SELECT /*+ MATERIALIZE */ main.person_id
,main.Employer_Contribution
,main.Member_contribution
,main.org_information6 as Nest_Employer_Reference_Number
,main.NATIONAL_IDENTIFIER_NUMBER as unique_record_identifier
,'Norfolk County Council' as Payment_source
,'Monthly' Frequency
,'0.00' as Pensionable_Earnings
, to_char(last_day(add_months(sysdate,-1)),'MM-DD-YYYY') as Earnings_period_end_date
FROM(
SELECT /*+ inline */
Bal_val.person_id,
SUM (CASE WHEN Balances.balance_name IN (xxxxxxxxx) Employer_Contribution,
SUM(CASE WHEN Balances.balance_name IN (xxxxxxxxxx) Member_contribution,
TRU.org_information6,
-- Bal_val.period_name,
-- Legal_Emp.name,
-- Bal_val.payroll_name,
-- flow.flow_name,
Bal_val.NATIONAL_IDENTIFIER_NUMBER
FROM
(
SELECT /*+ MATERIALIZE */
prb.balance_value,
prb.defined_balance_id,
prb.tax_unit_id,
ppa.effective_date,
ppa.earn_time_period_id,
Nvl(prg.assignment_id, ppaf.assignment_id) assignment_id,
pldg.legislation_code,
ppa.PAY_REQUEST_ID,
ppaf.legal_entity_id,
ppaf.person_id,
ptp.period_name,
papf.payroll_name,
pni.NATIONAL_IDENTIFIER_NUMBER
FROM
pay_run_balances prb,
pay_pay_relationships_dn pprd,
pay_payroll_rel_actions ppra,
pay_payroll_actions ppa,
per_all_assignments_f ppaf,
pay_rel_groups_dn PRG,
per_legislative_data_groups_vl PLDG,
pay_all_payrolls_f papf,
pay_time_periods ptp,
per_national_identifiers pni
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 pprd.person_id = ppaf.person_id
AND prb.effective_date = ppa.effective_date
AND ppa.action_type IN ('R', 'B', 'V', 'Q', 'I')
AND ppa.effective_date BETWEEN ppaf.effective_start_date
AND ppaf.effective_end_date
AND ppaf.primary_flag = 'Y'
AND pprd.legislative_data_group_id = pldg.legislative_data_group_id
AND ppaf.work_terms_assignment_id IS NOT NULL
AND ppaf.effective_latest_change = 'Y'
AND ppaf.assignment_type in ('E','C','N','P')
AND prg.relationship_group_id (+)= prb.payroll_assignment_id
AND ppra.retro_component_id IS NULL
AND ppa.payroll_id = papf.payroll_id(+)
--AND ppaf.person_id=300000004491016 ---200193
--AND ppaf.person_id=100000001628019 -----1915
AND prb.effective_date BETWEEN papf.effective_start_date(+) AND papf.effective_end_date(+)
AND ppa.earn_time_period_id = ptp.time_period_id
AND prb.effective_date BETWEEN ppaf.effective_start_date AND ppaf.effective_end_date
AND ppaf.person_id = pni.person_id
AND ((COALESCE(NULL, :p_payroll_name) IS NULL) OR (papf.payroll_name IN (:p_payroll_name)))
AND ptp.period_name = nvl(:p_period_name,ptp.period_name)
) Bal_val,
(
SELECT /*+ MATERIALIZE */
pduv.dimension_usage_id,
pduv.dimension_name,
pduv.legislation_code,
pdb.defined_balance_id
FROM
pay_dimension_usages_vl pduv,
pay_defined_balances pdb
WHERE
pduv.balance_dimension_id = pdb.balance_dimension_id
and pduv.dimension_name = 'Payroll Relationship, Run'
)Dimension,
(
SELECT /*+ MATERIALIZE */
houcf.org_unit_classification_id,
houcf.effective_start_date,
houcf.effective_end_date,
haouf.organization_id AS unit_eo_organization_id,
haouf.effective_start_date AS unit_eo_effective_start_date,
haouf.effective_end_date AS unit_eo_effective_end_date,
houft.organization_id AS unit_tleo_organization_id,
houft.effective_start_date AS unit_tleo_effective_start_date,
houft.effective_end_date AS unit_tleo_effective_end_date,
houft.language,
houft.NAME,
HOIF.ORG_INFORMATION6--nest ids
FROM
hr_all_organization_units_f haouf,
hr_org_unit_classifications_f houcf,
hr_organization_units_f_tl houft,
hr_organization_information_f HOIF
WHERE 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 userenv('LANG')= houft.language(+)
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 hoif.org_information_context LIKE 'LRU Additional Details'
AND hoif.organization_id = houft.organization_id
) TRU,
(
SELECT /*+ MATERIALIZE */
houcf.org_unit_classification_id,
houcf.effective_start_date,
houcf.effective_end_date,
haouf.organization_id AS unit_eo_organization_id,
haouf.effective_start_date AS unit_eo_effective_start_date,
haouf.effective_end_date AS unit_eo_effective_end_date,
houft.organization_id AS unit_tleo_organization_id,
houft.effective_start_date AS unit_tleo_effective_start_date,
houft.effective_end_date AS unit_tleo_effective_end_date,
houft.language,
houft.NAME
FROM
hr_all_organization_units_f haouf,
hr_org_unit_classifications_f houcf,
hr_organization_units_f_tl houft
WHERE
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 userenv('LANG') = houft.language(+)
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_LEMP'
AND ((COALESCE(NULL, :P_Legal_Entity) IS NULL) OR (houft.NAME IN (:P_Legal_Entity)))
) Legal_Emp,
(
SELECT /*+ MATERIALIZE */
pdb.balance_type_id,
pdb.defined_balance_id,
pbtv.balance_name
FROM
pay_defined_balances pdb,
pay_balance_types_vl pbtv
WHERE pbtv.balance_name in (xxxxxxx)
AND pdb.balance_type_id = pbtv.balance_type_id
) Balances,
(
SELECT /*+ MATERIALIZE */
pr.flow_task_instance_id,
pr.pay_request_id,
pfti.flow_instance_id,
pfti.flow_task_instance_id,
pft.flow_name
FROM
pay_requests pr,
pay_flow_task_instances pfti,
pay_flow_instances pfi,
pay_flows pf,
pay_flows_tl pft
WHERE pf.base_flow_id = pfi.base_flow_id (+)
AND pf.legislative_data_group_id = pfi.legislative_data_group_id
AND pft.flow_id = pf.flow_id
AND pft.language = userenv('LANG')
AND pfti.flow_instance_id = pfi.flow_instance_id(+)
AND pr.flow_task_instance_id = pfti.flow_task_instance_id(+)
AND ((COALESCE(NULL, :p_flow) IS NULL) OR (pfi.INSTANCE_NAME IN (:p_flow)))
) Flow
WHERE Bal_val.defined_balance_id = Dimension.defined_balance_id
AND Bal_val.legislation_code = Dimension.legislation_code
AND Bal_val.tax_unit_id = TRU.unit_eo_organization_id(+)
AND Bal_val.legal_entity_id = Legal_Emp.unit_eo_organization_id(+)
AND Bal_val.defined_balance_id = Balances.defined_balance_id
AND Bal_val.pay_request_id = Flow.pay_request_id(+)
AND Bal_val.effective_date BETWEEN TRU.effective_start_date(+) AND TRU.effective_end_date(+)
GROUP BY Bal_val.person_id,
TRU.ORG_INFORMATION6,
Bal_val.NATIONAL_IDENTIFIER_NUMBER
-- Bal_val.period_name,
-- Legal_Emp.name,
-- Bal_val.payroll_name,
-- flow.flow_name
) main
