/* Formatted on 2019/11/04 16:35 (Formatter Plus v4.8.8) */
WITH tru AS
(SELECT org_unit.organization_id, org_unit_tl.NAME
FROM hr_org_unit_classifications_f org_unit_cls,
hr_all_organization_units_f org_unit,
hr_organization_units_f_tl org_unit_tl
WHERE org_unit.organization_id = org_unit_cls.organization_id
AND org_unit.organization_id = org_unit_tl.organization_id
AND org_unit.effective_start_date
BETWEEN org_unit_cls.effective_start_date
AND org_unit_cls.effective_end_date
AND org_unit_tl.effective_start_date = org_unit.effective_start_date
AND org_unit_tl.effective_end_date = org_unit.effective_end_date
AND org_unit_tl.LANGUAGE = USERENV ('LANG')
AND org_unit_cls.classification_code = 'HCM_TRU'),
component_details AS
(SELECT m_comp_dtl.source_system_owner, m_comp_dtl.source_system_id,
comp_dtl.dir_comp_detail_id, comp_dtl.dir_card_comp_id,
comp_dtl.dir_information_category
FROM hrc_integration_key_map m_comp_dtl,
pay_dir_comp_details_f comp_dtl
WHERE m_comp_dtl.object_name = 'CalculationComponentDetails'
AND m_comp_dtl.surrogate_id = comp_dtl.dir_comp_detail_id
AND TRUNC (SYSDATE) BETWEEN comp_dtl.effective_start_date
AND comp_dtl.effective_end_date)
SELECT DISTINCT p.person_number, pay_rel.payroll_relationship_number,
rel_grp.assignment_number, term.assignment_number term_number,
card_def.display_name, card_comp_def.component_name,
ldg.NAME ldg, tru.NAME tru,
m_card.source_system_owner card_source_system_owner,
m_card.source_system_id card_source_system_id,
TO_CHAR (card.dir_card_id) dir_card_id,
TO_CHAR (card.effective_start_date,
'DD/MM/YYYY'
) effective_start_date,
TO_CHAR (card.effective_end_date,
'DD/MM/YYYY'
) effective_end_date,
m_card_comp.source_system_owner card_comp_source_system_owner,
m_card_comp.source_system_id card_comp_source_system_id,
TO_CHAR (card_comp.dir_card_comp_id) dir_card_comp_id,
TO_CHAR
(card_comp.parent_dir_card_comp_id)
parent_dir_card_comp_id,
dtl.source_system_owner comp_dtl_source_system_owner,
dtl.source_system_id comp_dtl_source_system_id,
TO_CHAR (dtl.dir_comp_detail_id) dir_comp_detail_id,
dtl.dir_information_category, TO_CHAR(term.relationship_group_id) relationship_group_id
,rel_grp.end_date rel_grp_end_date
,pay_rel.end_date pay_rel_end_date
,term.end_date term_end_date,
pdcdf1.DIR_INFORMATION_CHAR1,
pdcdf1.DIR_INFORMATION_CHAR2,
pdcdf1.DIR_INFORMATION_CHAR3,
pdcdf1.DIR_INFORMATION_CHAR4,
pdcdf1.DIR_INFORMATION_CHAR5,
pdcdf1.DIR_INFORMATION_CHAR6,
pdcdf1.DIR_INFORMATION_CHAR7,
pdcdf1.DIR_INFORMATION_CHAR8,
pdcdf1.DIR_INFORMATION_CHAR9,
pdcdf1.DIR_INFORMATION_CHAR10,
pdcdf1.DIR_INFORMATION_CHAR11,
pdcdf1.DIR_INFORMATION_CHAR12,
pdcdf1.DIR_INFORMATION_CHAR13,
pdcdf1.DIR_INFORMATION_CHAR14,
pdcdf1.DIR_INFORMATION_CHAR15,
pdcdf1.DIR_INFORMATION_CHAR16,
pdcdf1.DIR_INFORMATION_NUMBER1,
pdcdf1.DIR_INFORMATION_NUMBER2,
pdcdf1.DIR_INFORMATION_NUMBER3,
pdcdf1.DIR_INFORMATION_NUMBER4,
pdcdf1.DIR_INFORMATION_NUMBER5,
pdcdf1.DIR_INFORMATION_NUMBER6,
pdcdf1.DIR_INFORMATION_NUMBER7,
pdcdf1.DIR_INFORMATION_DATE1,
pdcdf1.DIR_INFORMATION_DATE2,
pdcdf1.DIR_INFORMATION_DATE3,
pdcdf1.DIR_INFORMATION_DATE4,
pdcdf1.DIR_INFORMATION_DATE5,
pdcdf1.DIR_INFORMATION_DATE6,
pdcdf1.DIR_INFORMATION_DATE7,
pdcdf1.DIR_INFORMATION_DATE8,
pdcdf1.DIR_INFORMATION_DATE9,
pdcdf1.DIR_INFORMATION_DATE10,
pdcdf1.DIR_INFORMATION_DATE11,
pdcdf1.DIR_INFORMATION_DATE12,
pdcdf1.DIR_INFORMATION_DATE13,
pdcdf1.DIR_INFORMATION_DATE14,
pdcdf1.STATUS,
pdcdf1.EFFECTIVE_START_DATE as "Comp Details Start Date",
pdcdf1.EFFECTIVE_END_DATE as "Comp Details End Date"
FROM pay_dir_cards_f card,
pay_dir_card_definitions_tl card_def,
per_legislative_data_groups_tl ldg,
pay_dir_card_components_f card_comp,
pay_dir_card_comp_defs_tl card_comp_def,
hrc_integration_key_map m_card,
hrc_integration_key_map m_card_comp,
pay_pay_relationships_dn pay_rel,
pay_rel_groups_dn rel_grp,
pay_rel_groups_dn term,
per_all_people_f p,
pay_dir_rep_cards_f rep_card,
tru,
component_details dtl,
pay_dir_comp_details_f pdcdf1
WHERE card_comp.dir_card_id = card.dir_card_id
AND card_def.LANGUAGE = 'US'
AND card.dir_card_definition_id = card_def.dir_card_definition_id
AND card_comp_def.dir_card_comp_def_id =
card_comp.dir_card_comp_def_id
AND card_comp_def.LANGUAGE = 'US'
AND card_def.display_name = :p_card_definition_name
-- ('Pensions Automatic Enrolment', 'Statutory Deductions')
AND m_card.object_name = 'CalculationCard'
AND m_card.surrogate_id = card.dir_card_id
AND m_card_comp.object_name = 'CalculationCardComponents'
AND m_card_comp.surrogate_id = card_comp.dir_card_comp_id
AND ldg.legislative_data_group_id = card.legislative_data_group_id
AND ldg.LANGUAGE = 'US'
AND pay_rel.payroll_relationship_id = card.payroll_relationship_id
AND TRUNC (SYSDATE) BETWEEN pay_rel.start_date AND pay_rel.end_date
AND rel_grp.payroll_relationship_id =
pay_rel.payroll_relationship_id
AND rel_grp.group_type = 'A'
AND TRUNC (SYSDATE) BETWEEN rel_grp.start_date AND rel_grp.end_date
AND rel_grp.payroll_relationship_id = term.payroll_relationship_id
AND term.group_type = 'T'
AND rel_grp.term_id = term.term_id
AND TRUNC (SYSDATE) BETWEEN term.start_date AND term.end_date
AND TRUNC (SYSDATE) BETWEEN card.effective_start_date
AND card.effective_end_date
AND pay_rel.person_id = p.person_id
AND p.object_version_number =
(SELECT MAX (p2.object_version_number)
FROM per_all_people_f p2
WHERE p.person_id = p2.person_id
AND p.effective_start_date = p2.effective_start_date)
AND card_comp.dir_card_comp_id = dtl.dir_card_comp_id(+)
AND card_comp.dir_card_comp_id = pdcdf1.dir_card_comp_id(+)
AND card.dir_card_id = rep_card.dir_card_id(+)
AND rep_card.tax_unit_id = tru.organization_id(+)
AND p.person_number = NVL (:p_person_number, p.person_number)
ORDER BY p.person_number,
rel_grp.assignment_number,
card_def.display_name,
card_comp_def.component_name
No comments:
Post a Comment