Friday, October 18, 2013

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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect