Employee Payroll Balance - Extraction

 Extraction of employee’s balance amount by period or By Person.

Parameter :
1. All Employee or by Person_Id
2. For the period

  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;