Query Payroll to reconcile last Month with current Payroll before Payroll is run (From Element Entries)

SELECT person_id,employee_number, full_name,name_lang,SUM(CURR_AMOUNT) CURR_AMT, SUM(PRV_AMOUNT) PRV_AMT
FROM
(
SELECT distinct people.person_id,people.employee_number, people.full_name, peef.element_entry_id,petf.element_name name_eng,petftl.element_name name_lang,peef.element_type_id
--peef.effective_start_date,peef.effective_end_date,peef.entry_type,peef.creator_type,peef.*
,DECODE(
petf.classification_id
,107
,XXPY_HIRING_DATA.get_element_amount_r(assign.business_group_id, assign.payroll_id,assign.assignment_id,petf.element_name,LAST_DAY (TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR')))
,99 --Loans
,XXFUJ_PAYROLL_FUNCTIONS_II.GET_SCREEN_ENTRY_VALUE(assign.assignment_id, petf.element_name,'Amount',LAST_DAY (TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR')))
,100--Rents
,XXFUJ_PAYROLL_FUNCTIONS_II.GET_SCREEN_ENTRY_VALUE(assign.assignment_id, petf.element_name,'Rent Amount',LAST_DAY (TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR')))
,XXFUJ_PAYROLL_FUNCTIONS_II.GET_SCREEN_ENTRY_VALUE(assign.assignment_id, petf.element_name,'Override Amount',LAST_DAY (TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR')))
)CURR_AMOUNT
,0 PRV_AMOUNT
FROM             per_people_f people,
                 per_all_assignments_f assign,
                 pay_element_entries_f peef,
                 pay_element_types_f petf,
                 pay_element_types_f_tl petftl
                
--                 PAY_PAYWSMEE_ELEMENT_ENTRIES
WHERE
people.person_id = assign.person_id
AND people.person_id = NVL (:p_person_id, people.person_id)
and assign.payroll_id = :p_payroll_id
AND LAST_DAY (TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR')) BETWEEN people.effective_start_date  AND people.effective_end_date
AND LAST_DAY (TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR')) BETWEEN assign.effective_start_date  AND assign.effective_end_date
AND LAST_DAY (TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR')) BETWEEN peef.effective_start_date  AND peef.effective_end_date 
AND LAST_DAY (TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR')) BETWEEN petf.effective_start_date  AND petf.effective_end_date
AND PRIMARY_FLAG = 'Y'
and peef.assignment_Id = assign.assignment_id
and peef.element_type_id = petf.element_type_id
and petf.element_type_id = petftl.element_type_id
and petftl.language = userenv('LANG')
and petf.classification_id in (107,99,100) -- 107 Earnings, 88 Involuntary Ded, 100 Voluntary Ded
and ASSIGNMENT_STATUS_TYPE_ID <> 3 --terminated
UNION ALL
SELECT distinct people.person_id,people.employee_number, people.full_name, peef.element_entry_id,petf.element_name,petftl.element_name element_name,peef.element_type_id
--peef.effective_start_date,peef.effective_end_date,peef.entry_type,peef.creator_type,peef.*
,0 CURR_AMOUNT
,DECODE(
petf.classification_id
,107
,XXPY_HIRING_DATA.get_element_amount_r(assign.business_group_id, assign.payroll_id,assign.assignment_id,petf.element_name,LAST_DAY(ADD_MONTHS(TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR'),-1)))
,99 --Loans
,XXFUJ_PAYROLL_FUNCTIONS_II.GET_SCREEN_ENTRY_VALUE(assign.assignment_id, petf.element_name,'Amount',LAST_DAY(ADD_MONTHS(TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR'),-1)))
,100--Rents
,XXFUJ_PAYROLL_FUNCTIONS_II.GET_SCREEN_ENTRY_VALUE(assign.assignment_id, petf.element_name,'Rent Amount',LAST_DAY(ADD_MONTHS(TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR'),-1)))
,XXFUJ_PAYROLL_FUNCTIONS_II.GET_SCREEN_ENTRY_VALUE(assign.assignment_id, petf.element_name,'Override Amount',LAST_DAY(ADD_MONTHS(TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR'),-1)))
)PRV_AMOUNT
FROM             per_people_f people,
                 per_all_assignments_f assign,
                 pay_element_entries_f peef,
                 pay_element_types_f petf,
                 pay_element_types_f_tl petftl
--                 PAY_PAYWSMEE_ELEMENT_ENTRIES
WHERE
people.person_id = assign.person_id
AND people.person_id = NVL (:p_person_id, people.person_id)
and assign.payroll_id = :p_payroll_id
--AND LAST_DAY(ADD_MONTHS(TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR'),-1)) BETWEEN people.effective_start_date  AND people.effective_end_date
--AND LAST_DAY(ADD_MONTHS(TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR'),-1)) BETWEEN assign.effective_start_date  AND assign.effective_end_date
AND LAST_DAY (TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR')) BETWEEN people.effective_start_date  AND people.effective_end_date
AND LAST_DAY (TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR')) BETWEEN assign.effective_start_date  AND assign.effective_end_date
AND LAST_DAY(ADD_MONTHS(TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR'),-1)) BETWEEN peef.effective_start_date  AND peef.effective_end_date 
AND LAST_DAY(ADD_MONTHS(TO_DATE (:p_payroll_end_date, 'DD-MM-RRRR'),-1)) BETWEEN petf.effective_start_date  AND petf.effective_end_date
AND PRIMARY_FLAG = 'Y'
and ASSIGNMENT_STATUS_TYPE_ID <> 3
and peef.assignment_Id = assign.assignment_id
and peef.element_type_id = petf.element_type_id
and petf.element_type_id = petftl.element_type_id
and petftl.language = userenv('LANG')
and petf.classification_id in (107,99,100) -- 107 Earnings, 88 Involuntary Ded, 100 Voluntary Ded
)Q1
GROUP BY person_id,employee_number, full_name,name_lang

HAVING SUM(CURR_AMOUNT) <> SUM(PRV_AMOUNT)
ORDER BY 2

XXPY_HIRING_DATA.get_element_amount_r is our custom package where we can pass element to calculate manually.

Example: Housing Allowance is 10% of Basic, we did the same calculation inside the package.