GL Account analysis for PA Expenditure & Event query

                 /*  1. GL Account analysis for PA Expenditure */
                    SELECT   'Expenditure' revenue_type,
                              hca.cust_account_id  customer_id,
                              hp.party_name customer_name,
                              v225332622.project_id  project_id,
                              v225332622.project_number project_number,
                              okha.contract_number contract_number,
                              v225332622.project_name project_name,
                              haou.NAME project_organization,
                              hca.customer_class_code industry,
                              hp.state state, hp.city city,
                              hp.country country, gcc.segment1 company,
                              gcc.segment2 ACCOUNT,
                              (SELECT fv.description
                                 FROM fnd_flex_values_vl fv,
                                      fnd_flex_value_sets fvs
                                WHERE fv.flex_value_set_id =
                                                         fvs.flex_value_set_id
                                  AND fvs.flex_value_set_name = 'Account'
                                  AND fv.flex_value = gcc.segment2)
                                                                 account_name,
                              gcc.segment3 department,
                              (SELECT fv.description
                                 FROM fnd_flex_values_vl fv,
                                      fnd_flex_value_sets fvs
                                WHERE fv.flex_value_set_id =
                                                         fvs.flex_value_set_id
                                  AND fvs.flex_value_set_name = 'Department'
                                  AND fv.flex_value = gcc.segment3)department_name,
                              gcc.segment4 region, gcc.segment5 FUNCTION,
                              SUM
                                 (project_curr_revenue_amt
                                 ) project_curr_revenue_amt,
                              trns_currency_code bill_transaction_currency,
                              SUM
                                 (trns_curr_revenue_amt
                                 ) bill_transaction_currency_rev,
                              contract_currency_code,
                              SUM
                                 (NVL (cont_curr_revenue_amt, 0)
                                 ) cont_curr_revenue_amt_sum,
                              revenue_currency_code, ledger_currency_code,
                              SUM
                                 (ledger_curr_revenue_amt
                                 ) ledger_curr_revenue_amt,
                              SUM
                                 (v144634978.revenue_curr_amt
                                 ) revenue_curr_amt,
                              SUM
                                 (DECODE
                                     ('USD',
                                      v144634978.ledger_currency_code, NVL
                                          (v144634978.ledger_curr_revenue_amt,
                                           0
                                          ),
                                      (  NVL
                                            (v144634978.ledger_curr_revenue_amt,
                                             0
                                            )
                                       * (SELECT DISTINCT r.avg_rate
                                                     FROM gl_translation_rates r,
                                                          gl_sets_of_books s
                                                    WHERE s.set_of_books_id =
                                                             r.set_of_books_id
                                                      AND r.to_currency_code =
                                                                         'USD'
                                                      AND s.currency_code =
                                                             v144634978.ledger_currency_code
                                                      AND UPPER (r.period_name) =
                                                             UPPER
                                                                (v144634978.gl_period
                                                                )
                                                      AND ROWNUM = 1)
                                      )
                                     )
                                 ) usd_revenue_amount,
                              v225332622.project_currency_code
                                                        project_currency_code,
                              SUM
                                 (v144634978.project_curr_revenue_amt
                                 ) project_revenue_amount,
                              NVL
                                 (pei.projfunc_currency_code,
                                  v144634978.ledger_currency_code
                                 ) projfunc_currency_code,
                              SUM
                                 (NVL (v144634978.ledger_curr_revenue_amt, 0)
                                 ) projfunc_revenue_amount,
                              v144634978.gl_period gl_period,
                              gsb.NAME gl_set_of_books_name,
                              NULL gl_line_dff_prj_num, NULL global_region,
                              (SELECT class_code
                                 FROM pjf_project_classes prc,
                                      pjf_class_codes_tl pct
                                WHERE prc.class_code_id = pct.class_code_id
                                  AND prc.project_id = v225332622.project_id)region_code,
                              (SELECT NAME
                                 FROM hr_operating_units
                                WHERE organization_id =
                                              v225332622.org_id)business_unit,
                              (SELECT b.document_name
                                 FROM pjf_txn_sources_tl a,
                                      pjf_txn_document_tl b
                                WHERE a.transaction_source_id =
                                                     pei.transaction_source_id
                                  AND b.document_id = pei.document_id) SOURCE,
                              (SELECT ho1.NAME
                                 FROM pjf_projects_all_b pa,
                                      hr_all_organization_units ho1
                                WHERE ho1.organization_id =
                                                  carrying_out_organization_id
                                  AND pa.project_id = v225332622.project_id)project_organization1,
                              v225332622.project_name source_details,
                              pei.expenditure_item_id AS expenditure_item_id,
                              v144634978.transaction_project_id AS transaction_project_id,
                              gl.NAME ledger_name, xda.code_combination_id,
                              (SELECT hou1.NAME
                                 FROM hr_all_organization_units hou1
                                WHERE okha.owning_org_id =hou1.organization_id)contract_org,
                              (SELECT DISTINCT r.avg_rate
                                          FROM gl_translation_rates r,
                                               gl_sets_of_books s
                                         WHERE s.set_of_books_id =
                                                             r.set_of_books_id
                                           AND r.to_currency_code = 'USD'
                                           AND s.currency_code =
                                                  v144634978.ledger_currency_code
                                           AND UPPER (r.period_name) =
                                                  UPPER (v144634978.gl_period)
                                           AND ROWNUM = 1) period_avg_rate,
                              (SELECT NAME
                                 FROM per_jobs_f_vl pjb
                                WHERE pjb.job_id = pei.person_job_id
                                  AND SYSDATE
                                         BETWEEN NVL
                                                    (pjb.effective_start_date,
                                                     SYSDATE - 1
                                                    )
                                             AND NVL (pjb.effective_end_date,
                                                      SYSDATE + 1
                                                     )
                                  AND active_status = 'A') job_name
                         FROM
                         (sELECT revenuedistributionpeo.bill_transaction_type_code,
                                      revenuedistributionpeo.rev_distribution_id,
                                      revenuedistributionpeo.transaction_id,
                                      revenuedistributionpeo.transaction_project_id,
                                      revenuedistributionpeo.revenue_curr_amt,
                                      revenuedistributionpeo.trns_curr_revenue_amt,
                                      gl_period_name gl_period, contract_id,
                                      major_version, project_curr_revenue_amt,
                                      gl_date, reversed_flag,
                                      line_num_reversed,
                                      revenue_currency_code,
                                      ledger_currency_code,
                                      ledger_curr_revenue_amt,
                                      contract_currency_code,
                                      cont_curr_revenue_amt,
                                      project_currency_code,
                                      trns_currency_code,
                                      (CASE
                                          WHEN bill_transaction_type_code =
                                                                          'EI'
                                             THEN transaction_id
                                       END
                                      ) AS expenditure_item_id
                                 FROM pjb_rev_distributions revenuedistributionpeo
                                WHERE 1 = 1) v144634978,
                              (SELECT projectbasepeo.project_id,
                                      projectbasepeo.segment1
                                                            AS project_number,
                                      ppt.NAME project_name,
                                      projectbasepeo.org_id,
                                      project_currency_code,
                                      projectbasepeo.attribute10 region_code,
                                      projectbasepeo.pm_product_code
                                                              pm_product_code
                                 FROM pjf_projects_all_b projectbasepeo,
                                      pjf_projects_all_tl ppt
                                WHERE projectbasepeo.project_id =
                                                                ppt.project_id) v225332622,
                              pjc_exp_items_all pei,
                              okc_k_headers_all_b okha,
                              hz_cust_accounts hca,
                              hz_parties hp,
                              hr_all_organization_units haou,
                              fun_names_business_units_v fnbu,
                              gl_ledgers gl,
                              gl_sets_of_books gsb,
                              gl_code_combinations gcc,
                              (SELECT xda2.event_id,
                                      xda2.source_distribution_id_num_1,
                                      xda2.rounding_class_code,
                                      xda2.source_distribution_id_num_2,
                                      xda2.ae_line_num, xda2.ae_header_id,
                                      ref_ae_header_id, ref_ae_line_num,
                                      SIGN
                                         (NVL (  (-1)
                                               * (xda2.unrounded_entered_dr),
                                               xda2.unrounded_entered_cr
                                              )
                                         ) xla_amt,
                                      xal2.code_combination_id
                                 FROM xla_distribution_links xda2,
                                      xla_ae_lines xal2
                                WHERE 1 = 1 
                                 AND xda2.source_distribution_type ='Revenue - Expenditure Revenue'
                                  AND xal2.ae_header_id = xda2.ae_header_id
                                  AND xal2.ae_line_num = xda2.ae_line_num
                                  AND NOT EXISTS (
                                         SELECT 1
                                           FROM xla_distribution_links xda1,
                                                xla_ae_lines xla1
                                          WHERE xda1.ref_temp_line_num =
                                                            xda2.temp_line_num
                                            AND xda1.ref_ae_header_id =
                                                             xda2.ae_header_id
                                            AND xda1.ae_line_num =
                                                              xla1.ae_line_num
                                            AND xda1.ae_header_id =
                                                             xla1.ae_header_id
                                            AND xla1.override_reason IS NOT NULL)) xda
                        WHERE v144634978.transaction_project_id =
                                                         v225332622.project_id
                          AND v225332622.project_id = pei.project_id
                          AND v144634978.expenditure_item_id =
                                                       pei.expenditure_item_id
                          AND v144634978.contract_id = okha.ID
                          AND v144634978.major_version = okha.major_version
                          AND okha.bill_to_acct_id = hca.cust_account_id(+)
                          AND hca.party_id = hp.party_id(+)
                          AND haou.organization_id = v225332622.org_id
                          AND fnbu.bu_id = v225332622.org_id
                          AND fnbu.primary_ledger_id = gl.ledger_id
                          AND gl.ledger_id = gsb.set_of_books_id
                          AND v144634978.rev_distribution_id =
                                              xda.source_distribution_id_num_1
                          AND SIGN (xda.xla_amt) =SIGN (v144634978.revenue_curr_amt)
                          AND xda.code_combination_id =gcc.code_combination_id
                          AND v144634978.bill_transaction_type_code = 'EI'
                          AND  UPPER (v144634978.gl_period) IN (:p_gl_period)
             GROUP BY hca.cust_account_id,
                      hp.party_name,
                      v225332622.project_id,
                      v225332622.project_number,
                      v225332622.project_name,
                      haou.NAME,
                      hca.customer_class_code,
                      hp.state,
                      hp.city,
                      gcc.segment1,
                      gcc.segment2,
                      gcc.segment3,
                      gcc.segment4,
                      gcc.segment5,
                      v144634978.gl_period,
                      gl.ledger_id,
                      v225332622.project_currency_code,
                      NVL (pei.projfunc_currency_code,
                           v144634978.ledger_currency_code
                          ),
                      v144634978.gl_date,
                      gl.period_set_name,
                      v144634978.gl_period,
                      gsb.NAME,
                      region_code,
                      v225332622.org_id,
                      pei.expenditure_item_id,
                      v144634978.transaction_project_id,
                      xda.code_combination_id,
                      v225332622.pm_product_code,
                      pei.transaction_source_id,
                      pei.document_id,
                      hp.country,
                      okha.contract_number,
                      okha.owning_org_id,
                      gl.NAME,
                      v144634978.trns_currency_code,
                      v144634978.contract_currency_code,
                      v144634978.revenue_currency_code,
                      v144634978.ledger_currency_code,
                      pei.person_job_id

/*2. GL Account analysis for PA Events  */
SELECT   'Event' revenue_type,
          hca.cust_account_id  customer_id,
          hp.party_name customer_name,
          ppa.project_id project_id,
                              ppa.segment1 project_number,
                              okh.contract_number contract_number,
                              ppa.NAME project_name,
                              hou.NAME project_organization,
                              hca.customer_class_code industry,
                              hp.state state, hp.city city,
                              hp.country country, gcc.segment1 company,
                              gcc.segment2 ACCOUNT,
                              (SELECT fv.description
                                 FROM fnd_flex_values_vl fv,
                                      fnd_flex_value_sets fvs
                                WHERE fv.flex_value_set_id =
                                                         fvs.flex_value_set_id
                                  AND fvs.flex_value_set_name = 'Account'
                                  AND fv.flex_value = gcc.segment2)
                                                                 account_name,
                              gcc.segment3 department,
                              (SELECT fv.description
                                 FROM fnd_flex_values_vl fv,
                                      fnd_flex_value_sets fvs
                                WHERE fv.flex_value_set_id =
                                                         fvs.flex_value_set_id
                                  AND fvs.flex_value_set_name ='Department'
                                  AND fv.flex_value = gcc.segment3)
                                                              department_name,
                              gcc.segment4 region, gcc.segment5 FUNCTION,
                              SUM
                                 (prd.project_curr_revenue_amt
                                 ) project_curr_revenue_amt,
                              pe.bill_trns_currency_code
                                                    bill_transaction_currency,
                              SUM
                                 (NVL (prd.trns_curr_revenue_amt, 0)
                                 ) bill_transaction_currency_rev,
                              pe.contract_curr_code contract_currency_code,
                              SUM
                                 (NVL (prd.cont_curr_revenue_amt, 0)
                                 ) cont_curr_revenue_amt_sum,
                              pe.revenue_currency_code,
                              pe.ledger_currency_code,
                              SUM
                                 (NVL (prd.ledger_curr_revenue_amt, 0)
                                 ) ledger_curr_revenue_amt,
                              SUM
                                 (NVL (prd.revenue_curr_amt, 0)
                                 ) revenue_curr_amt,
                              SUM
                                 (DECODE
                                     ('USD',
                                      pe.ledger_currency_code, prd.ledger_curr_revenue_amt,
                                        prd.ledger_curr_revenue_amt
                                      * NVL
                                           ((SELECT DISTINCT r.avg_rate
                                                        FROM gl_translation_rates r,
                                                             gl_sets_of_books s
                                                       WHERE s.set_of_books_id =
                                                                r.set_of_books_id
                                                         AND r.to_currency_code =
                                                                         'USD'
                                                         AND s.currency_code =
                                                                pe.ledger_currency_code
                                                         AND UPPER
                                                                (r.period_name) =
                                                                UPPER
                                                                   (prd.gl_period_name
                                                                   )),
                                            1
                                           )
                                     )
                                 ) usd_revenue_amount,
                              pe.project_currency_code project_currency_code,
                              SUM(prd.project_curr_revenue_amt) project_revenue_amount,
                              ppa.projfunc_currency_code
                                                       projfunc_currency_code,
                              SUM(ledger_curr_revenue_amt) projfunc_revenue_amount,
                              prd.gl_period_name gl_period,
                              gsb.NAME gl_set_of_books_name,
                              NULL gl_line_dff_prj_num, NULL global_region,
                              (SELECT class_code
                                 FROM pjf_project_classes prc,
                                      pjf_class_codes_tl pct
                                WHERE prc.class_code_id = pct.class_code_id
                                  AND prc.project_id = ppa.project_id)
                                                                  region_code,
                              (SELECT NAME
                                 FROM hr_operating_units
                                WHERE organization_id =
                                                     ppa.org_id)
                                                                business_unit,
                              'PA' SOURCE,
                              (SELECT ho1.NAME
                                 FROM pjf_projects_all_b pa,
                                      hr_all_organization_units ho1
                                WHERE ho1.organization_id =
                                                  carrying_out_organization_id
                                  AND pa.project_id = ppa.project_id)
                                                        project_organization1,
                              ppa.segment1 source_details,
                              pe.event_id expenditure_item_id,
                              ppa.project_id transaction_project_id,
                              gl.NAME ledger_name,
                              xda.code_combination_id,
                              (SELECT hou1.NAME
                                 FROM hr_all_organization_units hou1
                                WHERE okh.owning_org_id = hou1.organization_id)
                                                                 contract_org,
                              (SELECT DISTINCT r.avg_rate
                                          FROM gl_translation_rates r,
                                               gl_sets_of_books s
                                         WHERE s.set_of_books_id =
                                                             r.set_of_books_id
                                           AND r.to_currency_code = 'USD'
                                           AND s.currency_code =
                                                       pe.ledger_currency_code
                                           AND UPPER (r.period_name) =
                                                    UPPER (prd.gl_period_name))
                                                              period_avg_rate
                         FROM pjf_projects_all_vl ppa,
                              pjf_project_types_vl ppt,
                              pjb_rev_distributions prd,
                              pjb_billing_events pe,
                              okc_k_headers_all_b okh,
                              hz_cust_accounts hca,
                              hz_parties hp,
                              hr_all_organization_units hou,
                              fun_names_business_units_v fnbu,
                              (SELECT xda2.event_id,
                                      xda2.source_distribution_id_num_1,
                                      xda2.rounding_class_code,
                                      xda2.source_distribution_id_num_2,
                                      xda2.ae_line_num, xda2.ae_header_id,
                                      ref_ae_header_id, ref_ae_line_num,
                                      SIGN
                                         (NVL (  (-1)
                                               * (xda2.unrounded_entered_dr),
                                               xda2.unrounded_entered_cr
                                              )
                                         ) xla_amt,
                                      xal2.code_combination_id
                                 FROM xla_distribution_links xda2,
                                      xla_ae_lines xal2
                                WHERE 1 = 1
                                  AND xda2.source_distribution_type =
                                                     'Revenue - Event Revenue'
                                  AND xal2.ae_header_id = xda2.ae_header_id
                                  AND xal2.ae_line_num = xda2.ae_line_num
                                  AND NOT EXISTS (
                                         SELECT 1
                                           FROM xla_distribution_links xda1,
                                                xla_ae_lines xla1
                                          WHERE xda1.ref_temp_line_num =
                                                            xda2.temp_line_num
                                            AND xda1.ref_ae_header_id =
                                                             xda2.ae_header_id
                                            AND xda1.ae_line_num =
                                                              xla1.ae_line_num
                                            AND xda1.ae_header_id =
                                                             xla1.ae_header_id
                                            AND xla1.override_reason IS NOT NULL)) xda,
                              gl_code_combinations gcc,
                              gl_ledgers gl,
                              gl_sets_of_books gsb,
                              (SELECT   pcl.project_id, pcl.contract_id
                                   FROM pjb_cntrct_proj_links pcl
                                  WHERE pcl.version_type = 'C'
                               GROUP BY pcl.project_id, pcl.contract_id) cont_link
                        WHERE 1 = 1
                          AND ppa.project_type_id = ppt.project_type_id
                          AND bill_transaction_type_code = 'EVT'
                          AND cont_link.project_id = ppa.project_id
                          AND prd.transaction_id = pe.event_id
                          AND pe.contract_id = cont_link.contract_id
                          AND okh.ID = pe.contract_id
                          AND okh.version_type = 'C'
                          AND okh.bill_to_acct_id = hca.cust_account_id(+)
                          AND hca.party_id = hp.party_id(+)
                          AND hou.organization_id =ppa.org_id
                          AND fnbu.bu_id = ppa.org_id
                          AND fnbu.primary_ledger_id = gl.ledger_id
                          AND gl.ledger_id = gsb.set_of_books_id
                          AND xda.source_distribution_id_num_1 =prd.rev_distribution_id
                          AND SIGN (xda.xla_amt) = SIGN (prd.revenue_curr_amt)
                          AND gcc.code_combination_id =xda.code_combination_id
                          AND  UPPER (prd.gl_period_name) IN (:p_gl_period)
                         GROUP BY hca.cust_account_id,
                              hp.party_name,
                              ppa.project_id,
                              ppa.segment1,
                              okh.contract_number,
                              okh.owning_org_id,
                              ppa.NAME,
                              hou.NAME,
                              hca.customer_class_code,
                              hp.state,
                              hp.city,
                              hp.country,
                              gcc.segment1,
                              gcc.segment2,
                              gcc.segment3,
                              gcc.segment4,
                              gcc.segment5,
                              pe.bill_trns_currency_code,
                              pe.contract_curr_code,
                              pe.revenue_currency_code,
                              pe.ledger_currency_code,
                              pe.project_currency_code,
                              ppa.projfunc_currency_code,
                              projfunc_currency_code,
                              prd.gl_date,
                              gl.period_set_name,
                              prd.gl_period_name,
                              gsb.NAME,
                              ppa.org_id,
                              pe.event_id,
                              ppa.project_id,
                              gl.NAME,
                              xda.code_combination_id