Employee Payroll Balance

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.name grade,

           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;