Absence Queries

 Absence



SELECT 'METADATA'

|| CHR (124) || 'PersonAbsenceEntry'

|| CHR (124) || 'PerAbsenceEntryId'

|| CHR (124) || 'AbsenceType'

|| CHR (124) || 'AbsenceTypeId'

|| CHR (124) || 'AbsenceStatus'

|| CHR (124) || 'ApprovalStatus'

|| CHR (124) || 'AssignmentNumber'

|| CHR (124) || 'AssignmentId'

|| CHR (124) || 'EmployerId'

|| CHR (124) || 'PersonNumber'

|| CHR (124) || 'PersonId'

|| CHR (124) || 'StartDate'

|| CHR (124) || 'StartTime'

|| CHR (124) || 'StartDateDuration'

|| CHR (124) || 'EndDate'

|| CHR (124) || 'EndTime'

|| CHR (124) || 'EndDateDuration'

|| CHR (124) || 'AbsenceReasonId'

|| CHR (124) || 'SubmittedDate'

|| CHR (124) || 'PlannedEndDate'

|| CHR (124) || 'NotificationDate'

|| CHR (124) || 'ConfirmedDate'

|| CHR (124) || 'SourceSystemId'

|| CHR (124) || 'SourceSystemOwner' AS DATA_ROW

FROM DUAL

UNION all

select 'MERGE'

|| CHR (124) || 'PersonAbsenceEntry'

|| CHR (124) || apae.per_absence_entry_id

|| CHR (124) || aatft.name

|| CHR (124) || apae.absence_type_id

|| CHR (124) || apae.absence_status_cd

|| CHR (124) || apae.approval_status_cd

|| CHR (124) || paam.assignment_number

|| CHR (124) || paam.assignment_id

|| CHR (124) || apae.legal_entity_id

|| CHR (124) || papf.person_number

|| CHR (124) || apae.person_id

|| CHR (124) || to_char(apae.start_date,'RRRR/MM/DD')

|| CHR (124) || apae.start_time

|| CHR (124) || apae.start_date_duration

|| CHR (124) || to_char(apae.end_date,'RRRR/MM/DD')

|| CHR (124) || apae.end_time

|| CHR (124) || apae.end_date_duration

|| CHR (124) || apae.absence_type_reason_id

|| CHR (124) || to_char(apae.submitted_date,'RRRR/MM/DD')

|| CHR (124) || to_char(apae.planned_end_date,'RRRR/MM/DD')

|| CHR (124) || to_char(apae.notification_date,'RRRR/MM/DD')

|| CHR (124) || to_char(apae.confirmed_date,'RRRR/MM/DD')

|| CHR (124) || hikm.source_system_id

|| CHR (124) || hikm.source_system_owner

FROM ANC_PER_ABS_ENTRIES apae,

ANC_ABSENCE_TYPES_F_TL aatft,

PER_ALL_ASSIGNMENTS_M paam,

PER_ALL_PEOPLE_F papf,

HRC_INTEGRATION_KEY_MAP hikm

WHERE hikm.surrogate_id = apae.per_absence_entry_id

AND apae.absence_type_id = aatft.absence_type_id

AND apae.assignment_id = paam.assignment_id

AND apae.person_id = papf.person_id

AND paam.person_id = papf.person_id

AND aatft.language = 'US'

AND aatft.name LIKE 'Annual%'

AND TRUNC(SYSDATE) BETWEEN aatft.effective_start_date AND aatft.effective_end_date

AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

AND apae.start_date BETWEEN paam.effective_start_date AND paam.effective_end_date

)


Child or Parental Record



Comments

Post a Comment

Popular posts from this blog

Payroll Balance Initialization Errors

Query   SELECT pbh.batch_name ,pbh.batch_status ,pbl.LINE_SEQUENCE ,pbl.PAYROLL_RELATIONSHIP_NUMBER ,pbl.ASSIGNMENT_NUMBER ,pbl.BALANCE_NAME ,pbl.DIMENSION_NAME ,pbl.batch_line_status ,pbl.value ,LISTAGG(pml.line_text, '; ') WITHIN GROUP ( ORDER BY pbl.LINE_SEQUENCE ) "Error_list" FROM pay_bal_batch_headers pbh ,pay_bal_batch_lines pbl ,pay_message_lines pml WHERE pbh.batch_name = nvl(:Batch_Name, pbh.Batch_Name) AND pbl.batch_id = pbh.batch_id AND pbl.batch_line_status = 'E' AND pml.source_type = 'L' AND pml.source_id = pbl.batch_line_id GROUP BY pbh.batch_name ,pbh.batch_status ,pbl.LINE_SEQUENCE ,pbl.PAYROLL_RELATIONSHIP_NUMBER ,pbl.ASSIGNMENT_NUMBER ,pbl.BALANCE_NAME ,pbl.DIMENSION_NAME ,pbl.batch_line_status ,pbl.value
Read more

HCM Extracts

  Common Entities used in HCM Extracts Person - PER_EXT_SEC_PERSON_UE Worker Payroll - PER_EXT_PAY_EMPLOYEES_UE Extract Assignment Basic Information - PER_EXT_SEC_ASSIGNMENT_BASIC_UE Assignments Range - PER_EXT_SEC_ASSIGNMENT_RANGE_UE Extract Current and Future Persons - PER_EXT_SEC_PERSON_NOW_FUTURE_UE Extract Current and Future Assignments - PER_EXT_SEC_ASSIGNMENT_NOW_FUTURE_UE
Read more

HCM Data Loader

 Configure HCM Data Loader Parameters Setup and Maintenance > Configure HCM Data Loader > Open task SELECT * FROM hrc_dl_parameters Role for HCM Data Loader Human Resource Integration Specialist List of all Business Objects supported by HDL Under View Business Objects in Data Exchange Types of Keys supported by HDL GUID Oracle Fusion Global Unique ID Hexadecimal Can NOT use while creating new records Source Keys Combination of two fields Source System Owner HRC_SOURCE_SYSTEM_OWNER lookup under Setup and Maintenance > Common Lookups Source System ID  Alphanumeric value Can be used while creating new records Surrogate Keys Can NOT use while creating new records System generated Unique Numeric value for that record User Keys Can be used while creating new records Part of the Business Object that they belong to e.g. JobCode|SetCode Table which stores Source, Surrogate and GUIDs HRC_INTEGRATION_KEY_MAP Update Source Keys once set Yes, using SourceKey.dat Purge HDL Staging ...
Read more