Balance Extract by Employee

SELECT paf.assignment_number,
ppf.full_name,


gr.namegrade,
paygr.payroll_name payroll,
pbt.balance_name,
ppa.effective_date,
prv.result_value
FROM Pay_Element_Types_F PET,
Pay_Input_Values_F PIV,
Pay_Run_Result_Values PRV,
Pay_Run_Results PRR,
Pay_assignment_actions PAA,
Pay_payroll_actions PPA,
Pay_balance_types pbt,
Pay_balance_feeds_f pbff,
Per_people_f ppf,
Per_assignments_f paf,
Per_grades gr,
Pay_all_payrolls_f paygr
WHERE PRR.Element_Type_ID = PET.Element_Type_ID
AND PRR.STATUS IN (‘P’, ‘PA’)
AND PIV.Element_Type_ID = PET.Element_Type_ID
AND PRV.Input_Value_ID = PIV.Input_Value_ID
AND PRV.Run_Result_ID = PRR.Run_Result_ID
AND PRR.Assignment_Action_ID = PAA.Assignment_Action_ID
AND PAA.Payroll_Action_ID = PPA.Payroll_Action_ID
AND (PAF.Person_ID = ‘&&1’ OR ‘&&1’ IS NULL)
AND PBFF.balance_type_id = PBT.balance_type_id
AND PIV.input_value_id = PBFF.input_value_id
AND PIV.Name IN (‘Pay Value’)
AND PPA.EFFECTIVE_DATE BETWEEN ‘&&3’ AND ‘&&4’
AND PPF.PERSON_ID = PAF.PERSON_ID
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND paf.effective_start_date =
(SELECT MAX (effective_start_date)
FROM per_assignments_f paf1
WHERE paf.assignment_id = paf1.assignment_id)
AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND GR.GRADE_ID = PAF.GRADE_ID
AND PAYGR.PAYROLL_ID = PAF.PAYROLL_ID
AND SYSDATE BETWEEN PAYGR.EFFECTIVE_START_DATE
AND PAYGR.EFFECTIVE_END_DATE
ORDER BY paf.assignment_number,
ppf.full_name,
gr.name,
paygr.payroll_name,
pbt.balance_name,
ppa.effective_date;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s