Query For Payroll Run Balances In Oracle Fusion

 SELECT /*+ MATERIALIZE */ main.person_id

      ,main.Employer_Contribution

  ,main.Member_contribution

  ,main.org_information6 as Nest_Employer_Reference_Number

  ,main.NATIONAL_IDENTIFIER_NUMBER as unique_record_identifier

      ,'Norfolk County Council' as Payment_source

      ,'Monthly'  Frequency

      ,'0.00'  as  Pensionable_Earnings

  , to_char(last_day(add_months(sysdate,-1)),'MM-DD-YYYY') as  Earnings_period_end_date

FROM(

      SELECT /*+ inline */

        Bal_val.person_id, 

       SUM (CASE WHEN Balances.balance_name IN (xxxxxxxxx) Employer_Contribution,

       SUM(CASE WHEN Balances.balance_name IN (xxxxxxxxxx) Member_contribution,

TRU.org_information6,

-- Bal_val.period_name,

-- Legal_Emp.name,

-- Bal_val.payroll_name,

-- flow.flow_name,

Bal_val.NATIONAL_IDENTIFIER_NUMBER

      FROM 

        (

              SELECT /*+ MATERIALIZE */

                prb.balance_value, 

                prb.defined_balance_id, 

                prb.tax_unit_id,

                ppa.effective_date,

                ppa.earn_time_period_id,

                Nvl(prg.assignment_id, ppaf.assignment_id) assignment_id, 

                pldg.legislation_code,

ppa.PAY_REQUEST_ID,

ppaf.legal_entity_id,

ppaf.person_id,

ptp.period_name,

papf.payroll_name,

pni.NATIONAL_IDENTIFIER_NUMBER

              FROM 

                pay_run_balances prb, 

                pay_pay_relationships_dn pprd, 

                pay_payroll_rel_actions ppra, 

                pay_payroll_actions ppa, 

                per_all_assignments_f ppaf, 

                pay_rel_groups_dn PRG, 

                per_legislative_data_groups_vl PLDG,

    pay_all_payrolls_f papf,

    pay_time_periods ptp,

per_national_identifiers pni

              WHERE 

                prb.payroll_rel_action_id = ppra.payroll_rel_action_id 

                AND ppra.payroll_action_id = ppa.payroll_action_id 

                AND prb.payroll_relationship_id = pprd.payroll_relationship_id (+) 

                AND pprd.person_id = ppaf.person_id 

                AND prb.effective_date = ppa.effective_date 

                AND ppa.action_type IN ('R', 'B', 'V', 'Q', 'I') 

                AND ppa.effective_date BETWEEN ppaf.effective_start_date 

                AND ppaf.effective_end_date 

                AND ppaf.primary_flag = 'Y' 

                AND pprd.legislative_data_group_id = pldg.legislative_data_group_id 

                AND ppaf.work_terms_assignment_id IS NOT NULL 

AND ppaf.effective_latest_change = 'Y'

                AND ppaf.assignment_type in ('E','C','N','P')

                AND prg.relationship_group_id (+)= prb.payroll_assignment_id 

                AND ppra.retro_component_id IS NULL

AND ppa.payroll_id = papf.payroll_id(+) 

--AND ppaf.person_id=300000004491016 ---200193

--AND ppaf.person_id=100000001628019  -----1915

AND prb.effective_date BETWEEN papf.effective_start_date(+) AND papf.effective_end_date(+)

               AND ppa.earn_time_period_id = ptp.time_period_id 

   AND prb.effective_date BETWEEN ppaf.effective_start_date AND ppaf.effective_end_date

   AND ppaf.person_id = pni.person_id

   AND ((COALESCE(NULL, :p_payroll_name) IS NULL) OR (papf.payroll_name IN (:p_payroll_name)))

               AND   ptp.period_name = nvl(:p_period_name,ptp.period_name)

        ) Bal_val, 

        (

          SELECT /*+ MATERIALIZE */

            pduv.dimension_usage_id, 

            pduv.dimension_name, 

            pduv.legislation_code, 

            pdb.defined_balance_id 

          FROM 

            pay_dimension_usages_vl pduv, 

            pay_defined_balances pdb 

          WHERE 

            pduv.balance_dimension_id = pdb.balance_dimension_id

and pduv.dimension_name = 'Payroll Relationship, Run'

        )Dimension, 

        

      

        (

          SELECT /*+ MATERIALIZE */

            houcf.org_unit_classification_id, 

            houcf.effective_start_date, 

            houcf.effective_end_date, 

            haouf.organization_id AS unit_eo_organization_id, 

            haouf.effective_start_date AS unit_eo_effective_start_date, 

            haouf.effective_end_date AS unit_eo_effective_end_date, 

            houft.organization_id AS unit_tleo_organization_id, 

            houft.effective_start_date AS unit_tleo_effective_start_date, 

            houft.effective_end_date AS unit_tleo_effective_end_date, 

            houft.language, 

            houft.NAME,

HOIF.ORG_INFORMATION6--nest ids

          FROM 

            hr_all_organization_units_f haouf, 

            hr_org_unit_classifications_f houcf, 

            hr_organization_units_f_tl houft,

hr_organization_information_f HOIF

          WHERE  haouf.organization_id = houcf.organization_id(+) 

              AND haouf.organization_id = houft.organization_id(+) 

              AND haouf.effective_start_date = houft.effective_start_date(+) 

              AND haouf.effective_end_date = houft.effective_end_date(+) 

              AND userenv('LANG')= houft.language(+) 

              AND sysdate BETWEEN haouf.effective_start_date AND haouf.effective_end_date

              AND sysdate BETWEEN houcf.effective_start_date(+)AND houcf.effective_end_date(+)

              AND sysdate BETWEEN houft.effective_start_date(+) AND houft.effective_end_date(+)

              AND houcf.classification_code = 'HCM_TRU'

  AND hoif.org_information_context LIKE 'LRU Additional Details'

              AND hoif.organization_id = houft.organization_id

               

        ) TRU, 

         (

          SELECT /*+ MATERIALIZE */

            houcf.org_unit_classification_id, 

            houcf.effective_start_date, 

            houcf.effective_end_date, 

            haouf.organization_id AS unit_eo_organization_id, 

            haouf.effective_start_date AS unit_eo_effective_start_date, 

            haouf.effective_end_date AS unit_eo_effective_end_date, 

            houft.organization_id AS unit_tleo_organization_id, 

            houft.effective_start_date AS unit_tleo_effective_start_date, 

            houft.effective_end_date AS unit_tleo_effective_end_date, 

            houft.language, 

            houft.NAME 

          FROM 

            hr_all_organization_units_f haouf, 

            hr_org_unit_classifications_f houcf, 

            hr_organization_units_f_tl houft 

          WHERE 

            

              haouf.organization_id = houcf.organization_id(+) 

              AND haouf.organization_id = houft.organization_id(+) 

              AND haouf.effective_start_date = houft.effective_start_date(+) 

              AND haouf.effective_end_date = houft.effective_end_date(+) 

              AND userenv('LANG')  = houft.language(+) 

              AND sysdate BETWEEN haouf.effective_start_date AND haouf.effective_end_date

              AND sysdate BETWEEN houcf.effective_start_date(+) 

              AND houcf.effective_end_date(+)

              AND sysdate BETWEEN houft.effective_start_date(+) AND houft.effective_end_date(+)

              AND houcf.classification_code = 'HCM_LEMP'

  AND ((COALESCE(NULL, :P_Legal_Entity) IS NULL) OR (houft.NAME IN (:P_Legal_Entity)))

            

        ) Legal_Emp, 

        (

          SELECT /*+ MATERIALIZE */

            pdb.balance_type_id, 

            pdb.defined_balance_id,

pbtv.balance_name

          FROM 

            pay_defined_balances pdb,

pay_balance_types_vl pbtv

  WHERE pbtv.balance_name in (xxxxxxx)

      AND pdb.balance_type_id = pbtv.balance_type_id 

        ) Balances, 

        (

          SELECT /*+ MATERIALIZE */

            pr.flow_task_instance_id, 

            pr.pay_request_id,

pfti.flow_instance_id, 

            pfti.flow_task_instance_id,

    pft.flow_name

          FROM 

            pay_requests pr,

pay_flow_task_instances pfti,

pay_flow_instances pfi, 

            pay_flows pf, 

            pay_flows_tl pft 

   WHERE  pf.base_flow_id = pfi.base_flow_id (+) 

              AND pf.legislative_data_group_id = pfi.legislative_data_group_id 

              AND pft.flow_id = pf.flow_id 

              AND pft.language = userenv('LANG')

      AND pfti.flow_instance_id = pfi.flow_instance_id(+)

      AND pr.flow_task_instance_id = pfti.flow_task_instance_id(+) 

  AND ((COALESCE(NULL, :p_flow) IS NULL) OR (pfi.INSTANCE_NAME IN (:p_flow)))

        ) Flow

      

      WHERE   Bal_val.defined_balance_id = Dimension.defined_balance_id 

          AND Bal_val.legislation_code = Dimension.legislation_code 

          AND Bal_val.tax_unit_id = TRU.unit_eo_organization_id(+) 

          AND Bal_val.legal_entity_id = Legal_Emp.unit_eo_organization_id(+) 

          AND Bal_val.defined_balance_id = Balances.defined_balance_id 

          AND Bal_val.pay_request_id = Flow.pay_request_id(+) 

          AND Bal_val.effective_date BETWEEN TRU.effective_start_date(+) AND TRU.effective_end_date(+)

       

     GROUP BY  Bal_val.person_id,

           TRU.ORG_INFORMATION6,

   Bal_val.NATIONAL_IDENTIFIER_NUMBER

   -- Bal_val.period_name,

-- Legal_Emp.name,

-- Bal_val.payroll_name,

-- flow.flow_name

    

) main

Query for Calculation Cards in Oracle Fusion

 /* Formatted on 2019/11/04 16:35 (Formatter Plus v4.8.8) */

WITH tru AS

     (SELECT org_unit.organization_id, org_unit_tl.NAME

        FROM hr_org_unit_classifications_f org_unit_cls,

             hr_all_organization_units_f org_unit,

             hr_organization_units_f_tl org_unit_tl

       WHERE org_unit.organization_id = org_unit_cls.organization_id

         AND org_unit.organization_id = org_unit_tl.organization_id

         AND org_unit.effective_start_date

                BETWEEN org_unit_cls.effective_start_date

                    AND org_unit_cls.effective_end_date

         AND org_unit_tl.effective_start_date = org_unit.effective_start_date

         AND org_unit_tl.effective_end_date = org_unit.effective_end_date

         AND org_unit_tl.LANGUAGE = USERENV ('LANG')

         AND org_unit_cls.classification_code = 'HCM_TRU'),

     component_details AS

     (SELECT m_comp_dtl.source_system_owner, m_comp_dtl.source_system_id,

             comp_dtl.dir_comp_detail_id, comp_dtl.dir_card_comp_id,

             comp_dtl.dir_information_category

        FROM hrc_integration_key_map m_comp_dtl,

             pay_dir_comp_details_f comp_dtl

       WHERE m_comp_dtl.object_name = 'CalculationComponentDetails'

         AND m_comp_dtl.surrogate_id = comp_dtl.dir_comp_detail_id

         AND TRUNC (SYSDATE) BETWEEN comp_dtl.effective_start_date

                                 AND comp_dtl.effective_end_date)

SELECT DISTINCT p.person_number, pay_rel.payroll_relationship_number,

                rel_grp.assignment_number, term.assignment_number term_number,

                card_def.display_name, card_comp_def.component_name,

                ldg.NAME ldg, tru.NAME tru,

                m_card.source_system_owner card_source_system_owner,

                m_card.source_system_id card_source_system_id,

                TO_CHAR (card.dir_card_id) dir_card_id,

                TO_CHAR (card.effective_start_date,

                         'DD/MM/YYYY'

                        ) effective_start_date,

                TO_CHAR (card.effective_end_date,

                         'DD/MM/YYYY'

                        ) effective_end_date,

                m_card_comp.source_system_owner card_comp_source_system_owner,

                m_card_comp.source_system_id card_comp_source_system_id,

                TO_CHAR (card_comp.dir_card_comp_id) dir_card_comp_id,

                TO_CHAR

                   (card_comp.parent_dir_card_comp_id)

                                                      parent_dir_card_comp_id,

                dtl.source_system_owner comp_dtl_source_system_owner,

                dtl.source_system_id comp_dtl_source_system_id,

                TO_CHAR (dtl.dir_comp_detail_id) dir_comp_detail_id,

                dtl.dir_information_category, TO_CHAR(term.relationship_group_id) relationship_group_id

,rel_grp.end_date rel_grp_end_date

,pay_rel.end_date pay_rel_end_date

,term.end_date term_end_date,

pdcdf1.DIR_INFORMATION_CHAR1,

pdcdf1.DIR_INFORMATION_CHAR2,

pdcdf1.DIR_INFORMATION_CHAR3,

pdcdf1.DIR_INFORMATION_CHAR4,

pdcdf1.DIR_INFORMATION_CHAR5,

pdcdf1.DIR_INFORMATION_CHAR6,

pdcdf1.DIR_INFORMATION_CHAR7,

pdcdf1.DIR_INFORMATION_CHAR8,

pdcdf1.DIR_INFORMATION_CHAR9,

pdcdf1.DIR_INFORMATION_CHAR10,

pdcdf1.DIR_INFORMATION_CHAR11,

pdcdf1.DIR_INFORMATION_CHAR12,

pdcdf1.DIR_INFORMATION_CHAR13,

pdcdf1.DIR_INFORMATION_CHAR14,

pdcdf1.DIR_INFORMATION_CHAR15,

pdcdf1.DIR_INFORMATION_CHAR16,

pdcdf1.DIR_INFORMATION_NUMBER1,

pdcdf1.DIR_INFORMATION_NUMBER2,

pdcdf1.DIR_INFORMATION_NUMBER3,

pdcdf1.DIR_INFORMATION_NUMBER4,

pdcdf1.DIR_INFORMATION_NUMBER5,

pdcdf1.DIR_INFORMATION_NUMBER6,

pdcdf1.DIR_INFORMATION_NUMBER7,

pdcdf1.DIR_INFORMATION_DATE1,

pdcdf1.DIR_INFORMATION_DATE2,

pdcdf1.DIR_INFORMATION_DATE3,

pdcdf1.DIR_INFORMATION_DATE4,

pdcdf1.DIR_INFORMATION_DATE5,

pdcdf1.DIR_INFORMATION_DATE6,

pdcdf1.DIR_INFORMATION_DATE7,

pdcdf1.DIR_INFORMATION_DATE8,

pdcdf1.DIR_INFORMATION_DATE9,

pdcdf1.DIR_INFORMATION_DATE10,

pdcdf1.DIR_INFORMATION_DATE11,

pdcdf1.DIR_INFORMATION_DATE12,

pdcdf1.DIR_INFORMATION_DATE13,

pdcdf1.DIR_INFORMATION_DATE14,

pdcdf1.STATUS,

pdcdf1.EFFECTIVE_START_DATE as "Comp Details Start Date",

pdcdf1.EFFECTIVE_END_DATE as "Comp Details End Date"

           FROM pay_dir_cards_f card,

                pay_dir_card_definitions_tl card_def,

                per_legislative_data_groups_tl ldg,

                pay_dir_card_components_f card_comp,

                pay_dir_card_comp_defs_tl card_comp_def,

                hrc_integration_key_map m_card,

                hrc_integration_key_map m_card_comp,

                pay_pay_relationships_dn pay_rel,

                pay_rel_groups_dn rel_grp,

                pay_rel_groups_dn term,

                per_all_people_f p,

                pay_dir_rep_cards_f rep_card,

                tru,

                component_details dtl,

pay_dir_comp_details_f pdcdf1

          WHERE card_comp.dir_card_id = card.dir_card_id

            AND card_def.LANGUAGE = 'US'

            AND card.dir_card_definition_id = card_def.dir_card_definition_id

            AND card_comp_def.dir_card_comp_def_id =

                                                card_comp.dir_card_comp_def_id

            AND card_comp_def.LANGUAGE = 'US'

            AND card_def.display_name = :p_card_definition_name

                     -- ('Pensions Automatic Enrolment', 'Statutory Deductions')

            AND m_card.object_name = 'CalculationCard'

            AND m_card.surrogate_id = card.dir_card_id

            AND m_card_comp.object_name = 'CalculationCardComponents'

            AND m_card_comp.surrogate_id = card_comp.dir_card_comp_id

            AND ldg.legislative_data_group_id = card.legislative_data_group_id

            AND ldg.LANGUAGE = 'US'

            AND pay_rel.payroll_relationship_id = card.payroll_relationship_id

            AND TRUNC (SYSDATE) BETWEEN pay_rel.start_date AND pay_rel.end_date

            AND rel_grp.payroll_relationship_id =

                                               pay_rel.payroll_relationship_id

            AND rel_grp.group_type = 'A'

            AND TRUNC (SYSDATE) BETWEEN rel_grp.start_date AND rel_grp.end_date

            AND rel_grp.payroll_relationship_id = term.payroll_relationship_id

            AND term.group_type = 'T'

            AND rel_grp.term_id = term.term_id

            AND TRUNC (SYSDATE) BETWEEN term.start_date AND term.end_date

            AND TRUNC (SYSDATE) BETWEEN card.effective_start_date

                                    AND card.effective_end_date

            AND pay_rel.person_id = p.person_id

            AND p.object_version_number =

                   (SELECT MAX (p2.object_version_number)

                      FROM per_all_people_f p2

                     WHERE p.person_id = p2.person_id

                       AND p.effective_start_date = p2.effective_start_date)

            AND card_comp.dir_card_comp_id = dtl.dir_card_comp_id(+)

AND card_comp.dir_card_comp_id = pdcdf1.dir_card_comp_id(+)

            AND card.dir_card_id = rep_card.dir_card_id(+)

            AND rep_card.tax_unit_id = tru.organization_id(+)

            AND p.person_number = NVL (:p_person_number, p.person_number)

       ORDER BY p.person_number,

                rel_grp.assignment_number,

                card_def.display_name,

                card_comp_def.component_name