Learn Oracle Applications Tech-Stack: HCM Payroll Audit Report SQLs

1. Main data set

SELECT
person.person_id,
person.time_period_id,
payroll.earn_time_period_id,
payroll.effective_date,
payroll.payroll_action_id,
payroll.flow_name,
person.payroll_relationship_id,
person.payroll_id,
person.ptp_end_date,
person.employeeid,
person.employeename,
person.hiredate,
--PREVIOUS PERIOD END DATE
(select last_day(add_months(START_DATE,-1)) PRE_PERIOD_ENDDATE from PAY_TIME_PERIODS WHERE TIME_PERIOD_ID = :P_PERIOD_NAME ) M_PP_END_DATE,
(SELECT END_DATE FROM PAY_TIME_PERIODS WHERE TIME_PERIOD_ID = :P_PERIOD_NAME) M_CP_END_DATE
FROM
(
SELECT DISTINCT
papf.person_id,
paam.assignment_id,
ppr.payroll_relationship_id,
pay.payroll_id,
ptp.end_date ptp_end_date,
papf.person_number employeeid,
ppn.display_name employeename,
ppos.date_start hiredate,
ptp.time_period_id
FROM
per_all_people_f papf,
per_all_assignments_m paam,
per_person_names_f ppn,
per_periods_of_service ppos,
pay_all_payrolls_f pay,
pay_time_periods ptp,
pay_pay_relationships_dn ppr
WHERE
1 = 1
AND papf.person_id = paam.person_id
AND paam.primary_flag = 'Y'
AND paam.effective_latest_change = 'Y'
AND ppn.person_id = papf.person_id
AND ppn.name_type = 'GLOBAL'
AND ppos.person_id = papf.person_id
AND ppos.period_of_service_id = paam.period_of_service_id
AND ptp.payroll_id = pay.payroll_id
AND ptp.period_category = 'E'
AND ppr.person_id = papf.person_id
AND trunc(ptp.end_date) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND trunc(ptp.end_date) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND trunc(ptp.end_date) BETWEEN ppn.effective_start_date AND ppn.effective_end_date
AND trunc(ptp.end_date) BETWEEN pay.effective_start_date AND pay.effective_end_date
AND trunc(ptp.end_date) BETWEEN ppr.start_date AND ppr.end_date
AND paam.legal_entity_id =:p_le
AND ptp.legislative_data_group_id =:p_ldg
AND pay.legislative_data_group_id =:p_ldg
AND (
pay.payroll_id IN (
:p_payrollname
)
OR least(:p_payrollname) IS NULL
)
AND ptp.time_period_id =:p_period_name
AND paam.assignment_type = 'E' --as per discussion 17042019
AND (
papf.person_id IN (
:p_pn
)
OR least(:p_pn) IS NULL
)
ORDER BY
lpad(employeeid,10,'0') ) person,
(
SELECT distinct /* added for issue after commenting source_action_id multiple rows*/
ppra.payroll_action_id,
ppa.payroll_id,
ppa.EARN_TIME_PERIOD_ID,
(
SELECT DISTINCT
pfi.instance_name
FROM
pay_payroll_actions ppa1,
pay_requests prq,
pay_flow_instances pfi,
pay_flows_tl pft
WHERE
prq.pay_request_id (+) = ppa1.pay_request_id
AND prq.flow_instance_id = pfi.flow_instance_id (+)
AND pfi.base_flow_id = pft.flow_id
AND pft.source_lang = 'US'
AND ppa1.earn_time_period_id =:p_period_name
AND ppa1.payroll_id =:p_payrollname
AND pfi.legislative_data_group_id =:p_ldg
AND ppa1.legislative_data_group_id =:p_ldg
AND ppa1.payroll_action_id = ppa.payroll_action_id
) flow_name,
ppra.payroll_relationship_id,
--ppa.effective_date,
--last_day(nvl(ppa.effective_date,ptp.end_date)) effective_date,
ptp.end_date effective_date,
payrel.assignment_id
FROM
pay_rel_groups_dn payrel,
pay_payroll_actions ppa,
pay_payroll_rel_actions ppra,
pay_assigned_payrolls_dn pap,
pay_all_payrolls_f pay,
pay_time_periods ptp
WHERE
1 = 1
AND payrel.group_type = 'A'
AND payrel.parent_rel_group_id = pap.payroll_term_id
AND pay.payroll_id = pap.payroll_id
AND ptp.payroll_id = pay.payroll_id
AND ptp.period_category = 'E'
AND trunc(ptp.end_date) BETWEEN payrel.start_date AND payrel.end_date
AND trunc(ptp.end_date) BETWEEN pay.effective_start_date AND pay.effective_end_date
AND ptp.legislative_data_group_id =:p_ldg
AND pay.legislative_data_group_id =:p_ldg
AND (
pay.payroll_id IN (
:p_payrollname
)
OR least(:p_payrollname) IS NULL
)
AND ptp.time_period_id =:p_period_name
AND ppra.payroll_action_id = ppa.payroll_action_id
--AND ppra.source_action_id IS NOT NULL --issue in suspended
AND ppra.retro_component_id IS NULL
AND ppra.action_status = 'C'
AND ppa.legislative_data_group_id =:p_ldg
AND (
ppa.payroll_action_id IN (
:p_flowname
)
OR least(:p_flowname) IS NULL
)
AND (
ppa.action_type IN (
:p_action_type
)
OR least(:p_action_type) IS NULL
)
) payroll
WHERE
1 = 1
AND payroll.payroll_relationship_id (+) = person.payroll_relationship_id
AND payroll.payroll_id (+) = person.payroll_id
and payroll.effective_date (+) = person.ptp_end_date
and payroll.assignment_id(+) = person.assignment_id
and (
payroll.payroll_action_id IN (
:p_flowname
)
OR least(:p_flowname) IS NULL
)

2. Get Data for a Balance Name for Current/Previous Period

select
current_tp.person_id,
current_tp.payroll_action_id,
current_tp.Totalpay c_Totalpay,
previous_tp.Totalpay p_Totalpay,
case when (nvl(previous_tp.Totalpay,0) = nvl(current_tp.Totalpay,0)) then 'N' else 'Y' end diff_tp
from
(SELECT papf1.person_id,
ppa.payroll_action_id,
NVL(sum(bal.balance_value),'0') Totalpay
from pay_pay_relationships_dn pprd
,pay_payroll_rel_actions pra
,pay_payroll_actions ppa
,pay_action_classes pac
,pay_balance_types_vl pbt
,per_all_people_f papf1
,per_people_legislative_f ppl
,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
where PAYROLL_RELATIONSHIP_NUMBER = papf1.person_number
and papf1.person_id = ppl.person_id
and ppa.effective_date BETWEEN ptp.start_date and ptp.end_date
--and pdu.dimension_name = 'Assignment Run'
and pdu.dimension_name = 'Relationship Run'
and pdu.LEGISLATION_CODE = ppl.LEGISLATION_CODE
AND ptp.time_period_id = ppa.earn_time_period_id
and trunc(ptp.end_date) BETWEEN PTP.START_DATE AND PTP.END_DATE
AND trunc(ptp.end_date) BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE
AND trunc(ptp.end_date) BETWEEN PPL.EFFECTIVE_START_DATE AND PPL.EFFECTIVE_END_DATE
and pra.payroll_relationship_id = pprd.payroll_relationship_id
and pra.retro_component_id is null
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 ( pra.payroll_action_id IN (:P_FLOWNAME))
and pac.action_type = ppa.action_type
and pac.classification_name = 'SEQUENCED'
and Upper(pbt.balance_name) = 'Total Salary'
and nvl(bal.balance_value,0) != 0
and pdu.balance_dimension_id = bal.balance_dimension_id
AND ppa.earn_time_period_id =:P_PERIOD_NAME
group by papf1.person_id, ppa.payroll_action_id) current_tp,
(SELECT papf1.person_id,
ppa.payroll_action_id,
NVL(sum(bal.balance_value),'0') Totalpay
from pay_pay_relationships_dn pprd
,pay_payroll_rel_actions pra
,pay_payroll_actions ppa
,pay_action_classes pac
,pay_balance_types_vl pbt
,per_all_people_f papf1
,per_people_legislative_f ppl
,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
where PAYROLL_RELATIONSHIP_NUMBER = papf1.person_number
and papf1.person_id = ppl.person_id
and ppa.effective_date BETWEEN ptp.start_date and ptp.end_date
--and pdu.dimension_name = 'Assignment Run'
and pdu.dimension_name = 'Relationship Run'
and pdu.LEGISLATION_CODE = ppl.LEGISLATION_CODE
AND ptp.time_period_id = ppa.earn_time_period_id
and trunc(ptp.end_date) BETWEEN PTP.START_DATE AND PTP.END_DATE
AND trunc(ptp.end_date) BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE
AND trunc(ptp.end_date) BETWEEN PPL.EFFECTIVE_START_DATE AND PPL.EFFECTIVE_END_DATE
and pra.payroll_relationship_id = pprd.payroll_relationship_id
and pra.retro_component_id is null
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 ( pra.payroll_action_id IN (:P_PRE_FLOW_NAME))
and pac.action_type = ppa.action_type
and pac.classification_name = 'SEQUENCED'
and Upper(pbt.balance_name) = 'Total Salary'
and nvl(bal.balance_value,0) != 0
and pdu.balance_dimension_id = bal.balance_dimension_id
AND ppa.earn_time_period_id =:P_PRE_PERIOD_NAME
group by papf1.person_id, ppa.payroll_action_id) previous_tp
where 1=1
and current_tp.person_id = previous_tp.person_id(+)

3. Old and New Bank Details

SELECT

current_b.bankname c_bank_name,

current_b.bank_account_num c_bank_account_num,

current_b.iban c_iban,

previous_b.bankname p_bank_name,

previous_b.bank_account_num p_bank_account_num,

previous_b.iban p_iban,

current_b.payroll_relationship_id,

case when ( nvl(current_b.bankname,-1) != nvl(previous_b.bankname,-1) ) then 'Y' else 'N' end bank_diff,

case when ( nvl(current_b.bank_account_num,-1) != nvl(previous_b.bank_account_num,-1) ) then 'Y' else 'N' end bankacct_diff,

case when ( nvl(current_b.iban,-1) != nvl(previous_b.iban,-1) ) then 'Y' else 'N' end bankiban_diff

from

(SELECT DISTINCT

ieb.bank_name bankname,

TO_CHAR(iba.bank_account_num) bank_account_num,

iba.iban,

ppp.payroll_relationship_id

FROM

pay_person_pay_methods_f ppp,

iby_ext_bank_accounts_v ieb,

iby_ext_bank_accounts iba

WHERE

1 = 1

AND PPP.LEGISLATIVE_DATA_GROUP_ID =:P_LDG

AND ppp.payroll_relationship_id (+) = :M_payroll_relationship_id

AND ieb.bank_account_id (+) = ppp.bank_account_id

AND iba.ext_bank_account_id (+) = ieb.ext_bank_account_id

AND trunc(:M_CP_END_DATE) BETWEEN ppp.effective_start_date (+) AND ppp.effective_end_date (+)

) current_b,

(SELECT DISTINCT

ieb.bank_name bankname,

TO_CHAR(iba.bank_account_num) bank_account_num,

iba.iban,

ppp.payroll_relationship_id

FROM

pay_person_pay_methods_f ppp,

iby_ext_bank_accounts_v ieb,

iby_ext_bank_accounts iba

WHERE

1 = 1

AND PPP.LEGISLATIVE_DATA_GROUP_ID =:P_LDG

AND ppp.payroll_relationship_id (+) = :M_payroll_relationship_id

AND ieb.bank_account_id (+) = ppp.bank_account_id

AND iba.ext_bank_account_id (+) = ieb.ext_bank_account_id

AND trunc(:M_PP_END_DATE) BETWEEN ppp.effective_start_date (+) AND ppp.effective_end_date (+)

) previous_b

where current_b.payroll_relationship_id = previous_b.payroll_relationship_id(+)

4. Earnings List

select

curr.person_id

,curr.earn_time_period_id earn_time_period_id_c

,curr.payroll_action_id payroll_action_id_c

,curr.element_type_id element_type_id_c

,curr.base_element_name base_element_name_c

,curr.base_name base_name_c

,curr.earnings earnings_c

,curr.TOTAL_EARNINGS TOTAL_EARNINGS_c

-- curr.person_id

,previous.earn_time_period_id earn_time_period_id_p

,previous.payroll_action_id payroll_action_id_p

,previous.element_type_id element_type_id_p

,previous.base_element_name base_element_name_p

,previous.base_name base_name_p

,previous.earnings earnings_p

,previous.TOTAL_EARNINGS TOTAL_EARNINGS_p

,(nvl(curr.earnings,0) - nvl(previous.earnings,0)) DIFF_EARNINGS

,(nvl(curr.TOTAL_EARNINGS,0) - nvl(previous.TOTAL_EARNINGS,0)) DIFF_TOTAL_EARNINGS

from

(SELECT distinct papf.person_id

,ppa.earn_time_period_id

,ppa.payroll_action_id

,petf.element_type_id

,petf_tl.element_name base_element_name

,pivf.base_name

,sum(nvl(prrv.result_value,0)) OVER (PARTITION BY papf.person_id,ppa.earn_time_period_id,ppa.payroll_action_id,petf_tl.element_name) earnings

, SUM(nvl(prrv.result_value,0)) OVER (PARTITION BY papf.person_id) TOTAL_EARNINGS

FROM pay_run_result_values prrv

,pay_input_values_f pivf

,pay_run_results prr

,per_all_people_f papf

,hr_locations hl

,per_all_assignments_m paam

,pay_rel_groups_dn prgd

,pay_payroll_rel_actions ppra

,pay_flow_instances pfi

,pay_requests pr

,pay_payroll_actions ppa

,pay_element_types_f petf

,PAY_ELEMENT_TYPES_TL petf_tl

,pay_ele_classifications pec

,pay_time_periods ptp

WHERE 1=1

AND prrv.input_value_id = pivf.input_value_id

AND prrv.run_result_id = prr.run_result_id

AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date

--AND pivf.base_name in ('Pay Value')

AND NVL(PIVF.reserved_input_value,'X') = 'SPECIAL_OUTPUT'

AND pivf.element_type_id = petf.element_type_id

AND petf.element_type_id = prr.element_type_id

AND petf.classification_id=pec.classification_id

and petf_tl.element_type_id = petf.element_type_id

and petf_tl.element_type_id = prr.element_type_id

and petf_tl.language='US'

and petf_tl.source_lang='US'

AND prr.payroll_rel_action_id = ppra.payroll_rel_action_id

AND ppa.effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date

AND papf.person_id = paam.person_id

AND hl.location_id(+) = paam.location_id

AND ppa.effective_date BETWEEN paam.effective_start_date AND paam.effective_end_date

AND paam.effective_latest_change = 'Y'

AND paam.assignment_id = prgd.assignment_id

AND prgd.group_type = 'A'

AND ppra.source_id IS NULL

--AND prr.payroll_term_id = prgd.parent_rel_group_id

and prr.status='P' -- 06052019

AND prgd.payroll_relationship_id = ppra.payroll_relationship_id

AND ppra.action_status = 'C'

AND ppra.payroll_action_id = ppa.payroll_action_id

AND pfi.flow_instance_id = pr.flow_instance_id

AND pr.pay_request_id = ppa.pay_request_id

--AND ppa.action_type IN ('Q', 'R','B')

and (ppa.action_type in (:p_action_type) or least(:p_action_type) is null)

AND ppa.payroll_id=ptp.payroll_id

and ptp.time_period_id=ppa.earn_time_period_id

and ptp.period_category = 'E'

AND pec.base_classification_name in ('Standard Earnings','Supplemental Earnings','Direct Payments')

AND ppa.earn_time_period_id =:P_PERIOD_NAME

and (ppa.payroll_action_id in (:P_FLOWNAME ) OR LEAST(:P_FLOWNAME ) IS NULL)

--AND ppa.payroll_action_id = :p_flowname

AND trunc(ptp.end_date) between petf.effective_start_date and petf.effective_end_date

and paam.legal_entity_id=:P_LE

and (papf.person_id in (:P_PN) OR LEAST(:P_PN) IS NULL)

) curr,