WITH Employer_con AS
(
SELECT /*+ MATERIALIZE */
SUM(bal.balance_value) as amount,
pprd.person_id,
ppa.date_earned,
ptp.end_date,
ppa.payroll_action_id
FROM
per_legislative_data_groups_vl ldg,
pay_pay_relationships_dn pprd,
pay_payroll_rel_actions pra,
pay_payroll_actions ppa,
pay_balance_types_vl pbt,
TABLE ( pay_balance_view_pkg.get_balance_dimensions(p_balance_type_id => pbt.balance_type_id,p_payroll_rel_action_id => pra.payroll_rel_action_id
,p_payroll_term_id => NULL,p_payroll_assignment_id => NULL) ) bal,
pay_dimension_usages_vl pdu,
pay_time_periods ptp,
pay_requests req,
pay_flow_instances flow,
pay_all_payrolls_f paypf
WHERE
pprd.legislative_data_group_id = ldg.legislative_data_group_id
AND pra.payroll_relationship_id = pprd.payroll_relationship_id
AND EXISTS (
SELECT
1
FROM
pay_run_results prr
WHERE
prr.payroll_rel_action_id = pra.payroll_rel_action_id
)
AND ppa.payroll_action_id = pra.payroll_action_id
AND nvl(pbt.legislation_code,ldg.legislation_code) = ldg.legislation_code
AND nvl(pbt.legislative_data_group_id,ldg.legislative_data_group_id) = ldg.legislative_data_group_id
AND pdu.balance_dimension_id = bal.balance_dimension_id
AND pbt.balance_name IN ('xxxxxxxxxx')
AND pdu.dimension_name IN ('Payroll Relationship, Run')
AND nvl(pdu.legislation_code,ldg.legislation_code) = ldg.legislation_code
AND nvl(pdu.legislative_data_group_id,ldg.legislative_data_group_id) = ldg.legislative_data_group_id
AND trunc(ppa.date_earned) BETWEEN trunc(nvl(pprd.start_date(+),ppa.date_earned) ) AND trunc(nvl(pprd.end_date(+),ppa.date_earned) )
AND ptp.period_category = 'E'
AND ppa.earn_time_period_id=ptp.time_period_id
AND req.pay_request_id = ppa.pay_request_id
AND flow.flow_instance_id = req.flow_instance_id
and paypf.payroll_id = ptp.payroll_id
AND bal.balance_value <> 0
AND trunc(sysdate) BETWEEN paypf.effective_start_date AND paypf.effective_end_date
--AND ppa.payroll_id = :p_payroll_name
AND ((COALESCE(NULL, :p_payroll_name) IS NULL) OR (paypf.payroll_name IN (:p_payroll_name)))
AND ptp.period_name = nvl(:p_period_name,ptp.period_name)
--AND ppa.payroll_action_id in (:p_flow)
AND ((COALESCE(NULL, :p_flow) IS NULL) OR (flow.INSTANCE_NAME IN (:p_flow)))
GROUP BY
pprd.person_id,ppa.date_earned, ptp.end_date,ppa.payroll_action_id
),
MEMBER_CON AS
(
SELECT /*+ MATERIALIZE */
SUM(bal.balance_value) as amount,
pprd.person_id,
ppa.date_earned,
ptp.end_date,
ppa.payroll_action_id
FROM
per_legislative_data_groups_vl ldg,
pay_pay_relationships_dn pprd,
pay_payroll_rel_actions pra,
pay_payroll_actions ppa,
pay_balance_types_vl pbt,
TABLE ( pay_balance_view_pkg.get_balance_dimensions(p_balance_type_id => pbt.balance_type_id,p_payroll_rel_action_id => pra.payroll_rel_action_id
,p_payroll_term_id => NULL,p_payroll_assignment_id => NULL) ) bal,
pay_dimension_usages_vl pdu,
pay_time_periods ptp,
pay_requests req,
pay_flow_instances flow,
pay_all_payrolls_f paypf
WHERE
pprd.legislative_data_group_id = ldg.legislative_data_group_id
AND pra.payroll_relationship_id = pprd.payroll_relationship_id
AND EXISTS (
SELECT
1
FROM
pay_run_results prr
WHERE
prr.payroll_rel_action_id = pra.payroll_rel_action_id
)
AND ppa.payroll_action_id = pra.payroll_action_id
AND nvl(pbt.legislation_code,ldg.legislation_code) = ldg.legislation_code
AND nvl(pbt.legislative_data_group_id,ldg.legislative_data_group_id) = ldg.legislative_data_group_id
AND pdu.balance_dimension_id = bal.balance_dimension_id
AND pbt.balance_name IN ('xxxxxxxxxx')
AND pdu.dimension_name IN ('xxxxxxxxxxxxxx')
AND nvl(pdu.legislation_code,ldg.legislation_code) = ldg.legislation_code
AND nvl(pdu.legislative_data_group_id,ldg.legislative_data_group_id) = ldg.legislative_data_group_id
AND trunc(ppa.date_earned) BETWEEN trunc(nvl(pprd.start_date(+),ppa.date_earned) ) AND trunc(nvl(pprd.end_date(+),ppa.date_earned) )
AND ptp.period_category = 'E'
AND ppa.earn_time_period_id=ptp.time_period_id
AND req.pay_request_id = ppa.pay_request_id
AND flow.flow_instance_id = req.flow_instance_id
and paypf.payroll_id = ptp.payroll_id
AND bal.balance_value <> 0
AND trunc(sysdate) BETWEEN paypf.effective_start_date AND paypf.effective_end_date
--AND ppa.payroll_id = :p_payroll_name
AND ((COALESCE(NULL, :p_payroll_name) IS NULL) OR (paypf.payroll_name IN (:p_payroll_name)))
AND ptp.period_name = nvl(:p_period_name,ptp.period_name)
--AND ppa.payroll_action_id in (:p_flow)
AND ((COALESCE(NULL, :p_flow) IS NULL) OR (flow.INSTANCE_NAME IN (:p_flow)))
-- and paypf.payroll_name = 'DPSS PFS Payroll Monthly'
-- and ptp.period_name = '12 2023 Calendar Month'
-- and flow.INSTANCE_NAME = 'Calculate Payroll - 8th March 2024 - PFS'
--and pprd.person_id = 100000001631372
GROUP BY
pprd.person_id,ppa.date_earned, ptp.end_date,ppa.payroll_action_id
),
TRU_DETAILS as
(
SELECT /*+ MATERIALIZE */ DISTINCT
houft.NAME
,HOIF.ORG_INFORMATION_CONTEXT
,HOIF.ORG_INFORMATION1
,HOIF.ORG_INFORMATION2
,HOIF.ORG_INFORMATION3
,HOIF.ORG_INFORMATION_NUMBER1
,HOIF.ORG_INFORMATION4
,HOIF.ORG_INFORMATION6--nest ids
,HOIF.ORG_INFORMATION5
,pprd.person_id
FROM pay_run_balances prb,
pay_pay_relationships_dn pprd,
pay_payroll_rel_actions ppra,
pay_payroll_actions ppa,
pay_rel_groups_dn PRG,
per_legislative_data_groups_vl PLDG,
hr_all_organization_units_f haouf,
hr_org_unit_classifications_f houcf,
hr_organization_units_f_tl houft,
hr_organization_information_f HOIF
WHERE prb.payroll_rel_action_id=ppra.payroll_rel_action_id
AND ppra.payroll_action_id=ppa.payroll_action_id
AND prb.payroll_relationship_id= pprd.payroll_relationship_id (+)
AND prb.effective_date = ppa.effective_date
AND ppa.action_type IN ('R', 'B', 'V', 'Q', 'I')
AND pprd.legislative_data_group_id=pldg.legislative_data_group_id
AND prg.relationship_group_id (+)=prb.payroll_assignment_id
AND ppra.retro_component_id IS NULL
AND userenv('LANG') = houft.language(+)
AND haouf.organization_id = houcf.organization_id(+)
AND haouf.organization_id = houft.organization_id(+)
AND haouf.effective_start_date = houft.effective_start_date(+)
AND haouf.effective_end_date = houft.effective_end_date(+)
AND sysdate BETWEEN haouf.effective_start_date(+) AND haouf.effective_end_date(+)
AND sysdate BETWEEN houcf.effective_start_date(+)
AND houcf.effective_end_date(+)
AND sysdate BETWEEN houft.effective_start_date(+)
AND houft.effective_end_date(+)
AND houcf.classification_code(+) = 'HCM_TRU'
AND prb.tax_unit_id = haouf.organization_id (+)
AND ppa.effective_date >= houcf.effective_start_date(+)
AND ppa.effective_date <= houcf.effective_end_date(+)
AND hoif.org_information_context LIKE 'LRU Additional Details'
AND hoif.organization_id(+) = houft.organization_id
and HOIF.ORG_INFORMATION6 is not null
)
SELECT /*+ MATERIALIZE */
papf.person_number
,TD.ORG_INFORMATION6 as Nest_Employer_Reference_Number
,'Monthly' Frequency
, to_char(last_day(add_months(sysdate,-1)),'MM-DD-YYYY') as Earnings_period_end_date
,pni.NATIONAL_IDENTIFIER_NUMBER as unique_record_identifier
,0.00 as Pensionable_Earnings
,ec.amount Employer_Contribution
,mc.amount Member_contribution
FROM
per_all_people_f papf,
per_all_assignments_m paaf,
employer_con ec,
member_con mc,
tru_details td,
per_national_identifiers pni,
per_legal_employers ple
WHERE paaf.person_id = papf.person_id
AND papf.person_id = ec.person_id
AND papf.person_id = mc.person_id
AND paaf.primary_flag = 'Y'
AND paaf.effective_latest_change = 'Y'
AND PAAf.assignment_type IN('E','C')
AND td.person_id = paaf.person_id
AND papf.person_id = pni.person_id
AND ple.organization_id = paaf.legal_entity_id
AND sysdate between ple.EFFECTIVE_START_DATE and ple.EFFECTIVE_end_DATE
AND ((COALESCE(NULL, :P_Legal_Entity) IS NULL) OR (ple.name IN (:P_Legal_Entity)))
AND trunc(nvl(ec.date_earned,SYSDATE)) BETWEEN trunc(nvl(papf.effective_start_date,SYSDATE) ) AND trunc(nvl(papf.effective_end_date,SYSDATE) )
AND trunc(nvl(ec.date_earned,SYSDATE)) BETWEEN trunc(nvl(paaf.effective_start_date,SYSDATE) ) AND trunc(nvl(paaf.effective_end_date,SYSDATE) )
AND trunc(nvl(mc.date_earned,SYSDATE)) BETWEEN trunc(nvl(papf.effective_start_date,SYSDATE) ) AND trunc(nvl(papf.effective_end_date,SYSDATE) )
AND trunc(nvl(mc.date_earned,SYSDATE)) BETWEEN trunc(nvl(paaf.effective_start_date,SYSDATE) ) AND trunc(nvl(paaf.effective_end_date,SYSDATE) )
