Search This Blog

Wednesday, August 15, 2012

Employee wise Costing Details Oracle Payroll Costing

SELECT   papf.employee_number, papf.full_name,
            ppa.payroll_action_id
         || ' : '
         || con.consolidation_set_name
         || ' => Run Date: '
         || ppa.creation_date payroll_action,
         pet.element_name, SUM (pc.costed_value) credit, TO_NUMBER ('0')
                                                                        debit,
         pcak.segment1, pcak.segment2, pcak.segment3 ACCOUNT,
         (SELECT description
            FROM fnd_flex_values_vl f
           WHERE flex_value_set_id = 1013546
             AND f.flex_value_meaning = pcak.segment3
             AND ROWNUM < 2) "Account Meaning",
         pcak.segment4, pcak.segment5, pcak.segment6,
         ppa.creation_date run_date, ppa.effective_date
    FROM per_all_people_f papf,
         per_all_assignments_f paf,
         pay_assignment_actions paa,
         pay_payroll_actions ppa,
         pay_payrolls_f ppay,
         pay_personal_payment_methods_f pppm,
         pay_external_accounts pea,
         pay_costs pc,
         pay_cost_allocation_keyflex pcak,
         pay_input_values_f piv,
         pay_element_types_f pet,
         pay_consolidation_sets con
   WHERE papf.person_id = paf.person_id
     AND paf.business_group_id = 81
     AND ppa.payroll_id = ppay.payroll_id
     AND paa.assignment_id = paf.assignment_id
     AND paa.payroll_action_id = ppa.payroll_action_id
     AND pc.assignment_action_id = paa.assignment_action_id
     AND pppm.assignment_id(+) = paf.assignment_id
     AND pppm.external_account_id = pea.external_account_id(+)
     AND pcak.cost_allocation_keyflex_id = pc.cost_allocation_keyflex_id
     AND pet.element_type_id = piv.element_type_id
     AND piv.input_value_id = pc.input_value_id
     AND con.consolidation_set_id = ppa.consolidation_set_id
     AND pet.business_group_id = paf.business_group_id
     AND papf.business_group_id = paf.business_group_id
     AND ppa.action_status = 'C'
     AND ppa.action_type = 'C'
     --   AND    Trunc(SYSDATE) = Trunc(Ppa.Creation_Date)
     AND TRUNC (SYSDATE) BETWEEN TRUNC (pet.effective_start_date)
                             AND TRUNC (pet.effective_end_date)
     AND LEAST
             (xx_hr_general_pkg.get_termination_date (paf.person_id,
                                                        paf.business_group_id
                                                       ),
              TRUNC (SYSDATE)
             ) BETWEEN TRUNC (paf.effective_start_date)
                   AND TRUNC (paf.effective_end_date)
     AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date)
                             AND TRUNC (papf.effective_end_date)
     AND LEAST
             (xx_hr_general_pkg.get_termination_date (paf.person_id,
                                                        paf.business_group_id
                                                       ),
              TRUNC (SYSDATE)
             ) BETWEEN NVL (TRUNC (pppm.effective_start_date),
                            TO_DATE ('01-JAN-1980')
                           )
                   AND NVL (TRUNC (pppm.effective_end_date),
                            TO_DATE ('31-DEC-4000')
                           )
     AND TRUNC (SYSDATE) BETWEEN TRUNC (ppay.effective_start_date)
                             AND TRUNC (ppay.effective_end_date)
     AND pc.debit_or_credit = 'C'
--      AND    Ppa.payroll_action_id = &P_Pay_Action_Id
--      AND    Paf.payroll_id        = :P_Payroll_Id
GROUP BY papf.employee_number,
         papf.full_name,
            ppa.payroll_action_id
         || ' : '
         || con.consolidation_set_name
         || ' => Run Date: '
         || ppa.creation_date,
         pet.element_name,
         pcak.segment1,
         pcak.segment2,
         pcak.segment3,
         pcak.segment4,
         pcak.segment5,
         pcak.segment6,
         ppa.creation_date,
         ppa.effective_date
UNION
SELECT   papf.employee_number, papf.full_name,
            ppa.payroll_action_id
         || ' : '
         || con.consolidation_set_name
         || ' => Run Date: '
         || ppa.creation_date payroll_action,
         pet.element_name, TO_NUMBER ('0') credit, SUM (pc.costed_value)
                                                                        debit,
         pcak.segment1, pcak.segment2, pcak.segment3 ACCOUNT,
         (SELECT description
            FROM fnd_flex_values_vl f
           WHERE flex_value_set_id = 1013546
             AND f.flex_value_meaning = pcak.segment3
             AND ROWNUM < 2) "Account Meaning",
         pcak.segment4, pcak.segment5, pcak.segment6,
         ppa.creation_date run_date, ppa.effective_date
    FROM per_all_people_f papf,
         per_all_assignments_f paf,
         pay_assignment_actions paa,
         pay_payroll_actions ppa,
         pay_payrolls_f ppay,
         pay_personal_payment_methods_f pppm,
         pay_external_accounts pea,
         pay_costs pc,
         pay_cost_allocation_keyflex pcak,
         pay_input_values_f piv,
         pay_element_types_f pet,
         pay_consolidation_sets con
   WHERE papf.person_id = paf.person_id
     AND paf.business_group_id = 81
     AND ppa.payroll_id = ppay.payroll_id
     AND paa.assignment_id = paf.assignment_id
     AND paa.payroll_action_id = ppa.payroll_action_id
     AND pc.assignment_action_id = paa.assignment_action_id
     AND pppm.assignment_id(+) = paf.assignment_id
     AND pppm.external_account_id = pea.external_account_id(+)
     AND pcak.cost_allocation_keyflex_id = pc.cost_allocation_keyflex_id
     AND pet.element_type_id = piv.element_type_id
     AND piv.input_value_id = pc.input_value_id
     AND con.consolidation_set_id = ppa.consolidation_set_id
     AND pet.business_group_id = paf.business_group_id
     AND papf.business_group_id = paf.business_group_id
     AND ppa.action_status = 'C'
     AND ppa.action_type = 'C'
     AND TRUNC (SYSDATE) BETWEEN TRUNC (pet.effective_start_date)
                             AND TRUNC (pet.effective_end_date)
     AND LEAST
             (xx_hr_general_pkg.get_termination_date (paf.person_id,
                                                        paf.business_group_id
                                                       ),
              TRUNC (SYSDATE)
             ) BETWEEN TRUNC (paf.effective_start_date)
                   AND TRUNC (paf.effective_end_date)
     AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date)
                             AND TRUNC (papf.effective_end_date)
     AND LEAST
             (xx_hr_general_pkg.get_termination_date (paf.person_id,
                                                        paf.business_group_id
                                                       ),
              TRUNC (SYSDATE)
             ) BETWEEN NVL (TRUNC (pppm.effective_start_date),
                            TO_DATE ('01-JAN-1980')
                           )
                   AND NVL (TRUNC (pppm.effective_end_date),
                            TO_DATE ('31-DEC-4000')
                           )
     AND TRUNC (SYSDATE) BETWEEN TRUNC (ppay.effective_start_date)
                             AND TRUNC (ppay.effective_end_date)
     AND pc.debit_or_credit = 'D'
--      AND    Ppa.payroll_action_id = &P_Pay_Action_Id
-- AND    Paf.payroll_id        = :P_Payroll_Id
GROUP BY papf.employee_number,
         papf.full_name,
            ppa.payroll_action_id
         || ' : '
         || con.consolidation_set_name
         || ' => Run Date: '
         || ppa.creation_date,
         pet.element_name,
         pcak.segment1,
         pcak.segment2,
         pcak.segment3,
         pcak.segment4,
         pcak.segment5,
         pcak.segment6,
         ppa.creation_date,
         ppa.effective_date
ORDER BY 1, 2

No comments:

Post a Comment