HCM : PAYROLL DEFINITIONS REPORT

 SELECT

PAPF.payroll_id,

(SELECT PLDG.name

 FROM   per_legislative_data_groups_vl PLDG

 WHERE  PLDG.legislative_data_group_id = PAPF.legislative_data_group_id)

LEGISLATIVE_DATA_GROUP_NAME,

PAPF.payroll_name                                            PAYROLL_CODE

,

To_char(PAPF.effective_start_date, 'yyyy/mm/dd')

effective_start_date,

To_char(PAPF.effective_end_date, 'yyyy/mm/dd')

effective_end_date,

PAPF.negative_pay_allowed_flag,

PAPF.base_offsets,

(SELECT PCS.consolidation_set_code

 FROM   pay_consolidation_sets PCS

 WHERE  PAPF.consolidation_set_id = PCS.consolidation_set_id)

CONSOLIDATION_GROUP_CODE,

PAPF.consolidation_set_id,

PAPF.cutoff_base_date,

PAPF.cutoff_base_wrt

CUTOFF_DAY_TYPE,

PAPF.cutoff_falls,

To_char(PAPF.cutoff_fixed_date, 'yyyy/mm/dd')

CUTOFF_FIXED_DATE,

PAPF.cutoff_offset,

PAPF.earn_base_date

DATE_EARNED_BASE_DATE,

PAPF.earn_base_wrt

DATE_EARNED_DAY_TYPE,

PAPF.earn_falls

DATE_EARNED_FALLS,

To_char(PAPF.earn_fixed_date, 'yyyy/mm/dd')

DATE_EARNED_FIXED_DATE,

PAPF.earn_offset

DATE_EARN_OFFSET,

PAPF.paydate_base_date

DATEPAID_BASE_DATE,

PAPF.paydate_base_wrt

DATEPAID_DATE_TYPE,

PAPF.paydate_falls

DATEPAID_FALLS,

To_char(PAPF.paydate_fixed_date, 'yyyy/mm/dd')               DATEPAID_FIXED_DATE,

PAPF.paydate_offset

DATEPAID_OFFSET,

PAPF.default_payment_method_id,

(SELECT POPMF.base_org_pay_method_name

 FROM   pay_org_pay_methods_f POPMF

 WHERE  PAPF.default_payment_method_id = POPMF.org_payment_method_id

        AND SYSDATE BETWEEN POPMF.effective_start_date AND

POPMF.effective_end_date)        DEFAULT_PAYMENT_METHOD_CODE,

To_char(PAPF.first_period_end_date, 'yyyy/mm/dd')            FIRST_PERIOD_END_DATE,

PAPF.fixed_date,

GL.name                                                      LEDGER_CODE,

GL.ledger_id,

(SELECT PLDG.legislative_data_group_id

 FROM   per_legislative_data_groups_vl PLDG

 WHERE  PLDG.legislative_data_group_id = PAPF.legislative_data_group_id)

LEGISLATIVE_DATA_GROUP_ID,

PAPF.number_of_years,

PAPF.process_base_date

PAYROLL_RUN_BASE_DATE,

PAPF.process_base_wrt

PAYROLL_RUN_DAY_TYPE,

PAPF.process_falls                                           PAYROLL_RUN_FALLS,

To_char(PAPF.process_fixed_date, 'yyyy/mm/dd')               PAYROLL_RUN_FIXED_DATE,

PAPF.process_offset                                          PAYROLL_RUN_OFFSET,

PAPF.payslip_base_date

PAYSLIP_AVAI_BASE_DATE,

PAPF.payslip_base_wrt

PAYSLIP_AVAI_DAY_TYPE,

PAPF.payslip_falls                                           PAYSLIP_AVAI_FALLS,

To_char(PAPF.payslip_fixed_date, 'yyyy/mm/dd')               PAYSLIP_AVAI_FIXED_DATE,

PAPF.payslip_offset                                          PAYSLIP_AVAI_OFFSET

,

PAPF.period_reset_years,

PAPF.period_type                                             PERIOD_TYPE_CODE,

PAPF.process_sub_base_date

PLANNED_SUBMISSION_BASE_DATE,

PAPF.process_sub_wrt

PLANNED_SUBMISSION_DAY_TYPE,

PAPF.process_sub_falls

PLANNED_SUBMISSION_FALLS,

To_char(PAPF.process_sub_fixed_date, 'YYYY/MM/DD HH24:MI:SS')PLANNED_SUBMISSION_FIXED_DATE,

PAPF.process_sub_offset

PLANNED_SUBMISSION_OFFSET,

PAPF.reporting_name,

PTD.short_name

PERIOD_TIME_DEFINITION_CODE,

PTD.period_time_definition_id,

HIKM.source_system_id,

HIKM.source_system_owner

FROM   pay_all_payrolls_f PAPF,

       hrc_integration_key_map HIKM,

       gl_ledgers GL,

       pay_time_definitions PTD

WHERE  1 = 1

       AND SYSDATE BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date

       AND PAPF.payroll_id = HIKM.surrogate_id

       AND PAPF.gl_set_of_books_id = GL.ledger_id(+)

       AND HIKM.object_name = 'PayrollDefinition'

       AND PAPF.time_definition_id = PTD.time_definition_id(+)