REVENUE TRANSFER REPORT (FUSION)







Purpose of this report is to derive all projects expenditure and event details with cost information.

SELECT   revenue_type, TO_CHAR (customer_id) customer_id, customer_name,
         TO_CHAR (project_id) project_id, project_number, contract_number,
         project_name, project_organization, industry, state, city, country,
         company, ACCOUNT,                                     –account_type,
                          account_name, department, department_name, region,
         FUNCTION,
         ROUND (SUM (project_curr_revenue_amt), 2) project_curr_revenue_amt,
         bill_transaction_currency,
         ROUND
            (SUM (bill_transaction_currency_rev),
             2
            ) bill_transaction_currency_rev,
         contract_currency_code,
         ROUND (SUM (cont_curr_revenue_amt_sum), 2) cont_curr_revenue_amt_sum,
         revenue_currency_code, ledger_currency_code,
         ROUND (SUM (ledger_curr_revenue_amt), 2) ledger_curr_revenue_amt,
         ROUND (SUM (revenue_curr_amt), 2) revenue_curr_amt,
         ROUND (SUM (usd_revenue_amount), 2) usd_revenue_amount,
         project_currency_code,
         ROUND (SUM (project_revenue_amount), 2) project_revenue_amount,
         projfunc_currency_code,
         ROUND (SUM (projfunc_revenue_amount), 2) projfunc_revenue_amount,
         planning_week, gl_period, gl_set_of_books_name, gl_line_dff_prj_num,
                                                         –system_person_type,
         DECODE (project_organization,
                 ‘THG US’, ‘NA’,
                 ‘THG Germany’, ‘EU’,
                 ‘THG Netherlands’, ‘EU’,
                 ‘THG Switzerland’, ‘EU’,
                 ‘THG UK’, ‘EU’,
                 ‘THG France’, ‘EU’,
                 ‘THG Italy’, ‘EU’,
                 ‘THG Singapore’, ‘EU’,
                 ‘THG Spain’, ‘EU’,
                 ‘THG Australia’, ‘APAC’,
                 ‘THG India’, ‘IN’,
                 ‘THG Hungary’, ‘EU’,
                 ‘THG Uruguay’, ‘NA’,
                 ‘THG Canada’, ‘NA’,
                 ‘Missing Org Id’
                ) global_region,
         region_code, SOURCE, business_unit, ledger_name,
         DECODE
            (project_organization,
             ‘SAP AMS’, ‘SAP Application Managed Services’,
             ‘SAP Finance’, ‘SAP Application Services’,
             ‘SAP DS’, ‘SAP Application Services’,
             ‘SAP Business Intelligence’, ‘SAP Application Services’,
             ‘SAP Technology’, ‘SAP Application Services’,
             ‘SAP Supply Chain’, ‘SAP Application Services’,
             ‘SAP Management’, ‘SAP Application Services’,
             ‘SAP Reseller’, ‘SAP Reseller Software Services’,
             ‘EZ Commerce-India’, ‘EZ Commerce India-Bridge’,
             ‘EZ Commerce-India-DS’, ‘EZ Commerce India-Bridge’,
             ‘Business Application Support’, ‘Business Application Support Services’,
             ‘Hackett Institute’, ‘Hackett Institute Services’,
             ‘Hackett Institute DS’, ‘Hackett Institute Services’,
             ‘Transformation Workforce’, ‘Workforce Applications Services’,
             ‘Hackett DS’, ‘Hackett Admin’,
             ‘Hackett Performance Exchange’, ‘BM-HPE’,
             ‘Hackett Performance Exchange DS’, ‘BM-HPE’,
             ‘Technolab AMS’, ‘Technolab AMS Services’,
             ‘Technolab AMS DS’, ‘Technolab AMS Services’,
             ‘Strategic Technical Account Managers’, ‘Technolab AMS Services’,
             ‘REL’, ‘REL Services’,
             ‘REL DS’, ‘REL Services’,
             ‘Aecus’, ‘Aecus Limited Services’,
             ‘Aecus DS’, ‘Aecus Limited Services’,
             ‘Risk Management’, ‘Risk Management Services’,
             ‘Finance’, ‘Functional Services’,
             ‘Human Resources’, ‘Functional Services’,
             ‘Information Technology’, ‘Functional Services’,
             ‘Executive’, ‘Functional Services’,
             ‘Office Administration’, ‘Functional Services’,
             ‘Legal’, ‘Functional Services’,
             ‘Product Development’, ‘Functional Services’,
             ‘Corporate’, ‘Functional Services’,
             ‘REL Elimination’, ‘Functional Services’,
             ‘Transformation Finance’, DECODE
                                        (DECODE (project_organization,
                                                 ‘THG Canada’, ‘NA’,
                                                 ‘THG US’, ‘NA’,
                                                 ‘THG Uruguay’, ‘NA’,
                                                 ‘THG Switzerland’, ‘EU’,
                                                 ‘THG Germany’, ‘EU’,
                                                 ‘THG UK’, ‘EU’,
                                                 ‘THG Netherlands’, ‘EU’,
                                                 ‘THG France’, ‘EU’,
                                                 ‘THG Spain’, ‘EU’,
                                                 ‘Missing Org Id’
                                                ),
                                         ‘NA’, ‘Strategy & Business Transformation’,
                                         ‘EU’, ‘Transformation Europe’,
                                         ‘Transformation Services’
                                        ),
             ‘Transformation HR’, DECODE
                                        (DECODE (project_organization,
                                                 ‘THG Canada’, ‘NA’,
                                                 ‘THG US’, ‘NA’,
                                                 ‘THG Uruguay’, ‘NA’,
                                                 ‘THG Switzerland’, ‘EU’,
                                                 ‘THG Germany’, ‘EU’,
                                                 ‘THG UK’, ‘EU’,
                                                 ‘THG Netherlands’, ‘EU’,
                                                 ‘THG France’, ‘EU’,
                                                 ‘THG Spain’, ‘EU’,
                                                 ‘Missing Org Id’
                                                ),
                                         ‘NA’, ‘Strategy & Business Transformation’,
                                         ‘EU’, ‘Transformation Europe’,
                                         ‘Transformation Services’
                                        ),
             ‘Transformation IT’, DECODE
                                        (DECODE (project_organization,
                                                 ‘THG Canada’, ‘NA’,
                                                 ‘THG US’, ‘NA’,
                                                 ‘THG Uruguay’, ‘NA’,
                                                 ‘THG Switzerland’, ‘EU’,
                                                 ‘THG Germany’, ‘EU’,
                                                 ‘THG UK’, ‘EU’,
                                                 ‘THG Netherlands’, ‘EU’,
                                                 ‘THG France’, ‘EU’,
                                                 ‘THG Spain’, ‘EU’,
                                                 ‘Missing Org Id’
                                                ),
                                         ‘NA’, ‘Strategy & Business Transformation’,
                                         ‘EU’, ‘Transformation Europe’,
                                         ‘Transformation Services’
                                        ),
             ‘Transformation DS’, DECODE
                                        (DECODE (project_organization,
                                                 ‘THG Canada’, ‘NA’,
                                                 ‘THG US’, ‘NA’,
                                                 ‘THG Uruguay’, ‘NA’,
                                                 ‘THG Switzerland’, ‘EU’,
                                                 ‘THG Germany’, ‘EU’,
                                                 ‘THG UK’, ‘EU’,
                                                 ‘THG Netherlands’, ‘EU’,
                                                 ‘THG France’, ‘EU’,
                                                 ‘THG Spain’, ‘EU’,
                                                 ‘Missing Org Id’
                                                ),
                                         ‘NA’, ‘Strategy & Business Transformation’,
                                         ‘EU’, ‘Transformation Europe’,
                                         ‘Transformation Services’
                                        ),
             ‘ADP’, ‘ADP Services’,
             ‘Benchmark’, ‘BM-HPE’,          — Replacing’Benchmark Services’,
             ‘Benchmark DS’, ‘BM-HPE’,      — Replacing ‘Benchmark Services’,
             ‘Advisory’, ‘Advisory Services’,
             ‘Advisory-DS’, ‘Advisory Services’,
             ‘S&O Operations’, ‘Strategy & Business Transformation’,
             ‘S&O Procurement’, ‘Strategy & Business Transformation’,
             ‘S&O IT’, ‘Strategy & Business Transformation’,
             ‘S&O Enterprise Pool’, ‘Strategy & Business Transformation’,
             ‘S&O DS’, ‘Strategy & Business Transformation’,
             ‘EEA Transformation’, DECODE (DECODE (project_organization,
                                                   ‘THG Canada’, ‘NA’,
                                                   ‘THG US’, ‘NA’,
                                                   ‘THG Uruguay’, ‘NA’,
                                                   ‘THG Switzerland’, ‘EU’,
                                                   ‘THG Germany’, ‘EU’,
                                                   ‘THG UK’, ‘EU’,
                                                   ‘THG Netherlands’, ‘EU’,
                                                   ‘THG France’, ‘EU’,
                                                   ‘THG Spain’, ‘EU’,
                                                   ‘Missing Org Id’
                                                  ),
                                           ‘NA’, ‘EEA Solutions’,
                                           ‘EU’, ‘Transformation Europe’,
                                           ‘EEA Transformation Services’
                                          ),
             ‘GBS Europe’, ‘Transformation Europe’,
             ‘EPM Europe’, ‘Transformation Europe’,
             ‘Procurement Europe’, ‘Transformation Europe’,
             ‘EEA Jibe ERP’, ‘EEA Solutions’,
             ‘EEA Jibe HCM’, ‘EEA Solutions’,
             ‘EEA Jibe EPM Analytics’, ‘EEA Solutions’,
             ‘EEA Jibe DS’, ‘EEA Solutions’,
             ‘EEA Jibe Near Shore Applications’, ‘EEA Solutions’,
             ‘EEA Jibe NA Tech’, ‘EEA Solutions’,
             ‘EEA Jibe Offshore’, ‘EEA Solutions’,
             ‘EEA Jibe Financials’, ‘EEA Solutions’,
             ‘EEA Jibe AMS’, ‘EEA Solutions’,
             ‘EEA Business Intelligence/Advanced Analytics’, DECODE
                                               (DECODE (project_organization,
                                                        ‘THG Canada’, ‘NA’,
                                                        ‘THG US’, ‘NA’,
                                                        ‘THG Uruguay’, ‘NA’,
                                                        ‘Missing Org Id’
                                                       ),
                                                ‘NA’, ‘EEA Solutions’,
                                                ‘EEA Oracle Services’
                                               ),
             ‘EEA Oracle Consolidation’, DECODE
                                               (DECODE (project_organization,
                                                        ‘THG Canada’, ‘NA’,
                                                        ‘THG US’, ‘NA’,
                                                        ‘THG Uruguay’, ‘NA’,
                                                        ‘Missing Org Id’
                                                       ),
                                                ‘NA’, ‘EEA Solutions’,
                                                ‘EEA Oracle Services’
                                               ),
             ‘EEA Analytic Infrastructure’, DECODE
                                               (DECODE (project_organization,
                                                        ‘THG Canada’, ‘NA’,
                                                        ‘THG US’, ‘NA’,
                                                        ‘THG Uruguay’, ‘NA’,
                                                        ‘Missing Org Id’
                                                       ),
                                                ‘NA’, ‘EEA Solutions’,
                                                ‘EEA Oracle Services’
                                               ),
             ‘EEA Oracle Planning & Analytics’, DECODE
                                               (DECODE (project_organization,
                                                        ‘THG Canada’, ‘NA’,
                                                        ‘THG US’, ‘NA’,
                                                        ‘THG Uruguay’, ‘NA’,
                                                        ‘Missing Org Id’
                                                       ),
                                                ‘NA’, ‘EEA Solutions’,
                                                ‘EEA Oracle Services’
                                               ),
             ‘EEA Strategic’, DECODE (DECODE (project_organization,
                                              ‘THG Canada’, ‘NA’,
                                              ‘THG US’, ‘NA’,
                                              ‘THG Uruguay’, ‘NA’,
                                              ‘Missing Org Id’
                                             ),
                                      ‘NA’, ‘EEA Solutions’,
                                      ‘EEA Oracle Services’
                                     ),
             ‘EEA DS’, DECODE (DECODE (project_organization,
                                       ‘THG Canada’, ‘NA’,
                                       ‘THG US’, ‘NA’,
                                       ‘THG Uruguay’, ‘NA’,
                                       ‘Missing Org Id’
                                      ),
                               ‘NA’, ‘EEA Solutions’,
                               ‘EEA Oracle Services’
                              ),
             ‘EEA Near Shore Applications’, DECODE
                                               (DECODE (project_organization,
                                                        ‘THG Canada’, ‘NA’,
                                                        ‘THG US’, ‘NA’,
                                                        ‘THG Uruguay’, ‘NA’,
                                                        ‘Missing Org Id’
                                                       ),
                                                ‘NA’, ‘EEA Solutions’,
                                                ‘EEA Oracle Services’
                                               ),
             ‘EEA Oracle Cloud ERP’, DECODE (DECODE (project_organization,
                                                     ‘THG Canada’, ‘NA’,
                                                     ‘THG US’, ‘NA’,
                                                     ‘THG Uruguay’, ‘NA’,
                                                     ‘Missing Org Id’
                                                    ),
                                             ‘NA’, ‘EEA Solutions’,
                                             ‘EEA Oracle Services’
                                            ),
             ‘Not Defined’
            ) reporting_organization
    FROM ((SELECT   revenue_type, customer_id, customer_name, project_id,
                    project_number, contract_number, project_name,
                    project_organization, industry, state, city, country,
                    company, ACCOUNT, account_name, department,
                    department_name, region, FUNCTION,
                    ROUND
                       (SUM (project_curr_revenue_amt),
                        2
                       ) project_curr_revenue_amt,
                    bill_transaction_currency,
                    ROUND
                       (SUM (bill_transaction_currency_rev),
                        2
                       ) bill_transaction_currency_rev,
                    contract_currency_code,
                    ROUND
                       (SUM (cont_curr_revenue_amt_sum),
                        2
                       ) cont_curr_revenue_amt_sum,
                    revenue_currency_code, ledger_currency_code,
                    ROUND
                       (SUM (ledger_curr_revenue_amt),
                        2
                       ) ledger_curr_revenue_amt,
                    ROUND (SUM (revenue_curr_amt), 2) revenue_curr_amt,
                    ROUND (SUM (usd_revenue_amount), 2) usd_revenue_amount,
                    project_currency_code,
                    ROUND
                         (SUM (project_revenue_amount),
                          2
                         ) project_revenue_amount,
                    projfunc_currency_code,
                    ROUND
                       (SUM (projfunc_revenue_amount),
                        2
                       ) projfunc_revenue_amount,
                    planning_week, gl_period, gl_set_of_books_name,
                    gl_line_dff_prj_num, global_region, region_code,
                    business_unit, SOURCE, ledger_name, code_combination_id
               FROM (SELECT   ‘Expenditure’ revenue_type,
                              CAST
                                 (hca.cust_account_id AS CHARACTER (30)
                                 ) customer_id,
                              hp.party_name customer_name,
                              CAST
                                 (v225332622.project_id AS CHARACTER (30)
                                 ) 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,
                             
                              –gcc.account_type,
                              (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 THG’
                                  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 THG’
                                  –‘ANSR_GL_ACCOUNT’
                                  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.revenue_currency_code, NVL(v144634978.revenue_curr_amt,0),
                                        (NVL(v144634978.revenue_curr_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.set_of_books_id=gsb.set_of_books_id
AND s.currency_code= v144634978.revenue_currency_code
                            AND r.period_name =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,
                              pcdl.projfunc_currency_code
                                                       projfunc_currency_code,
                              pcdl.projfunc_raw_cost projfunc_revenue_amount,
                              (SELECT    ‘Q’
                                      || quarter_num
                                      || ‘-WK’
                                      || LPAD
                                            (TO_CHAR
                                                (DECODE
                                                    (period_num,
                                                     53, 14,
                                                     DECODE (MOD (period_num,
                                                                  13
                                                                 ),
                                                             0, 13,
                                                             MOD (period_num,
                                                                  13
                                                                 )
                                                            )
                                                    )
                                                ),
                                             2,
                                             ‘0’
                                            )
                                 FROM gl_periods gp
                                WHERE 1 = 1
                                  AND v144634978.gl_date BETWEEN start_date
                                                             AND end_date
                                  AND adjustment_period_flag = ‘N’
                                  AND gp.period_set_name = gl.period_set_name)
                                                                planning_week,
                              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 =
                                              v457612106.transaction_source_id
                                  AND b.document_id = v457612106.document_id)
                                                                       SOURCE,
                              v457612106.expenditure_item_id
                                                       AS expenditure_item_id,
                              v144634978.transaction_project_id
                                                    AS transaction_project_id,
                             
                              –v144634978.rev_distribution_id AS rev_distribution_id,
                              /*(Select meaning
                                 From fnd_lookup_values_tl
                                where lookup_type=’PJF_PERSON_TYPE’
                                  and lookup_code=v457612106.person_type) system_person_type,*/
                              gl.NAME ledger_name, xal.code_combination_id
                         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,
                              (SELECT expenditureitempeo.expenditure_item_id,
                                      expenditureitempeo.document_id,
                                      expenditureitempeo.project_id,
                                      expenditureitempeo.transaction_source_id,
                                      expenditureitempeo.person_type
                                                                  person_type,
                                      expenditureitempeo.original_dist_id
                                 FROM pjc_exp_items_all expenditureitempeo) v457612106,
                              pjc_cost_dist_lines_all pcdl,
                              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,
                              xla_distribution_links xda,
                              xla_ae_lines xal
                        WHERE v144634978.transaction_project_id =
                                                         v225332622.project_id
                          AND v225332622.project_id = v457612106.project_id
                          AND v144634978.expenditure_item_id = v457612106.expenditure_item_id(+)
                          AND (((v144634978.transaction_project_id > 0)))
                          AND pcdl.reversed_flag IS NULL
                          AND pcdl.line_num_reversed IS NULL
                          AND v144634978.reversed_flag IS NULL
                          AND v144634978.line_num_reversed IS NULL
                          AND pcdl.expenditure_item_id =
                                                v457612106.expenditure_item_id
                          AND pcdl.transfer_status_code IN (‘N’)
                          AND v144634978.contract_id = okha.ID
                          AND v144634978.major_version = okha.major_version
                          AND okha.bill_to_acct_id = hca.cust_account_id(+)
                          –AND v144634978.bill_transaction_type_code =’EI’
                          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 xda.source_distribution_id_num_1 =
                                                v144634978.rev_distribution_id
                          –AND PCDL.EXPENDITURE_ITEM_ID=XDA.SOURCE_DISTRIBUTION_ID_NUM_1(+)
                          AND xda.ae_line_num(+) = 2
                          AND xal.ae_header_id(+) = xda.ae_header_id
                          AND xal.ae_line_num(+) = xda.ae_line_num
                          AND gcc.code_combination_id =
                                                     –xal.code_combination_id
                                 NVL
                                    (xal.code_combination_id,
                                     (CASE
                                         WHEN pcdl.acct_source_code = ‘AP_INV’
                                            THEN (SELECT apd.dist_code_combination_id
                                                    FROM ap_invoice_distributions_all apd
                                                   WHERE apd.invoice_distribution_id =
                                                            v457612106.original_dist_id)
                                         ELSE NVL (pcdl.raw_cost_dr_ccid,
                                                   pcdl.raw_cost_cr_ccid
                                                  )
                                      END
                                     )
                                    )
                          AND xda.source_distribution_type =
                                               ‘Revenue – Expenditure Revenue’
                          AND xda.unrounded_accounted_cr IS NOT NULL
                          AND xal.ae_header_id = xda.ae_header_id
                          AND xal.ae_line_num = xda.ae_line_num
                          –AND gcc.code_combination_id =xal.code_combination_id
                          AND gcc.segment1 <> ‘AA’
                          AND revenue_curr_amt IS NOT NULL
                          /*AND (    gcc.segment2 BETWEEN ‘410000’ AND ‘549999’
                               AND gcc.segment2 NOT BETWEEN ‘500000’ AND ‘528509’
                               AND gcc.segment2 NOT BETWEEN ‘528511’ AND ‘529999’
                              )*/
                          AND v144634978.bill_transaction_type_code = ‘EI’
–AND v225332622.project_number=’TEST-GFY-DE’
–AND v457612106.expenditure_item_id=207232
                     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.account_type,
                              gcc.segment3,
                              gcc.segment4,
                              gcc.segment5,
                              v144634978.trns_curr_revenue_amt,
                              v144634978.gl_period,
                              gl.ledger_id,
                              v225332622.project_currency_code,
                              v144634978.project_curr_revenue_amt,
                              pcdl.projfunc_currency_code,
                              pcdl.projfunc_raw_cost,
                              v144634978.gl_date,
                              gl.period_set_name,
                              v144634978.gl_period,
                              gsb.NAME,
                              region_code,
                              v225332622.org_id,
                              v144634978.revenue_curr_amt,
                              v457612106.expenditure_item_id,
                              v144634978.transaction_project_id,
                              xal.code_combination_id,
                              v225332622.pm_product_code,
                              v457612106.transaction_source_id,
                              v457612106.document_id,
                              hp.country,
                              okha.contract_number,
                              gl.NAME,
                              v144634978.trns_currency_code,
                              v144634978.contract_currency_code,
                              v144634978.revenue_currency_code,
                              v144634978.ledger_currency_code)
              WHERE transaction_project_id IS NOT NULL
                AND expenditure_item_id IS NOT NULL
           GROUP BY revenue_type,
                    customer_id,
                    customer_name,
                    project_id,
                    project_number,
                    project_name,
                    project_organization,
                    industry,
                    state,
                    city,
                    country,
                    company,
                    ACCOUNT,
                    –account_type,
                    account_name,
                    department,
                    department_name,
                    region,
                    FUNCTION,
                    project_currency_code,
                    projfunc_currency_code,
                    planning_week,
                    gl_period,
                    gl_set_of_books_name,
                    gl_line_dff_prj_num,
                    global_region,
                    region_code,
                    business_unit,
                    SOURCE,
                    code_combination_id,
                    contract_number,
                    –system_person_type
                    ledger_name,
                    bill_transaction_currency,
                    contract_currency_code,
                    revenue_currency_code,
                    ledger_currency_code,
                    project_currency_code,
                    projfunc_currency_code)
          UNION ALL
          (SELECT   revenue_type, customer_id, customer_name, project_id,
                    project_number, contract_number, project_name,
                    project_organization, industry, state, city, country,
                    company, ACCOUNT, account_name, department,
                    department_name, region, FUNCTION,
                    ROUND
                       (SUM (project_curr_revenue_amt),
                        2
                       ) project_curr_revenue_amt,
                    bill_transaction_currency,
                    ROUND
                       (SUM (bill_transaction_currency_rev),
                        2
                       ) bill_transaction_currency_rev,
                    contract_currency_code,
                    ROUND
                       (SUM (cont_curr_revenue_amt_sum),
                        2
                       ) cont_curr_revenue_amt_sum,
                    revenue_currency_code, ledger_currency_code,
                    ROUND
                       (SUM (ledger_curr_revenue_amt),
                        2
                       ) ledger_curr_revenue_amt,
                    ROUND (SUM (revenue_curr_amt), 2) revenue_curr_amt,
                    ROUND (SUM (usd_revenue_amount), 2) usd_revenue_amount,
                    project_currency_code,
                    ROUND
                         (SUM (project_revenue_amount),
                          2
                         ) project_revenue_amount,
                    projfunc_currency_code,
                    ROUND
                       (SUM (projfunc_revenue_amount),
                        2
                       ) projfunc_revenue_amount,
                    planning_week, gl_period, gl_set_of_books_name,
                    gl_line_dff_prj_num, global_region, region_code,
                    business_unit, SOURCE, ledger_name, code_combination_id
               FROM (SELECT   ‘Event’ revenue_type,
                              CAST
                                 (hca.cust_account_id AS CHARACTER (30)
                                 ) customer_id,
                              hp.party_name customer_name,
                              CAST
                                 (ppa.project_id AS CHARACTER (30))
                                                                   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 THG’
                                  –‘ANSR_GL_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 THG’
                                  –‘ANSR_GL_ACCOUNT’
                                  AND fv.flex_value = gcc.segment3)
                                                              department_name,
                              gcc.segment4 region, gcc.segment5 FUNCTION,
                              SUM
                                 (pe.project_revenue_amt
                                 ) project_curr_revenue_amt,
                              pe.bill_trns_currency_code
                                                    bill_transaction_currency,
                              SUM
                                 (NVL (pe.bill_trns_amount, 0)
                                 ) bill_transaction_currency_rev,
                              pe.contract_curr_code contract_currency_code,
                              SUM
                                 (NVL (pe.contract_curr_amt, 0)
                                 ) cont_curr_revenue_amt_sum,
                              pe.revenue_currency_code,
                              pe.ledger_currency_code,
                              SUM
                                 (NVL (pe.ledger_revenue_amt, 0)
                                 ) ledger_curr_revenue_amt,
                              SUM (NVL (pe.revenue_amt, 0)) revenue_curr_amt,
                              SUM
                                 (DECODE
                                     (‘USD’,
                                      pe.revenue_currency_code, pe.revenue_amt,
                                      pe.ledger_currency_code, pe.ledger_revenue_amt,
                                      pe.contract_curr_code, pe.contract_curr_amt,
                                      pe.project_currency_code, pe.project_revenue_amt,
                                        pe.revenue_amt          –pcrdl.amount
                                      * NVL
                                           ((SELECT gdr.conversion_rate
                                               FROM gl_daily_rates gdr
                                              WHERE gdr.conversion_type =
                                                                   ‘Corporate’
                                                AND gdr.to_currency = ‘USD’
                                                AND gdr.from_currency =
                                                       pe.revenue_currency_code
                                                AND gdr.conversion_date =
                                                                   prd.gl_date),
                                            1
                                           )
                                     )
                                 ) usd_revenue_amount,
                              pe.project_currency_code project_currency_code,
                              SUM
                                 (pe.project_revenue_amt
                                 ) project_revenue_amount,
                              ppa.projfunc_currency_code
                                                       projfunc_currency_code,
                              NULL projfunc_revenue_amount,
                              (SELECT    ‘Q’
                                      || quarter_num
                                      || ‘-WK’
                                      || LPAD
                                            (TO_CHAR
                                                (DECODE
                                                    (period_num,
                                                     53, 14,
                                                     DECODE (MOD (period_num,
                                                                  13
                                                                 ),
                                                             0, 13,
                                                             MOD (period_num,
                                                                  13
                                                                 )
                                                            )
                                                    )
                                                ),
                                             2,
                                             ‘0’
                                            )
                                 FROM gl_periods gp
                                WHERE 1 = 1
                                  AND prd.gl_date BETWEEN start_date AND end_date
                                  AND adjustment_period_flag = ‘N’
                                  AND gp.period_set_name = gl.period_set_name)
                                                                planning_week,
                              prd.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 = ppa.project_id)
                                                                  region_code,
                              (SELECT NAME
                                 FROM hr_operating_units
                                WHERE organization_id =
                                                     ppa.org_id)
                                                                business_unit,
                              ‘PA’ SOURCE,
                                      –prd.revenue_curr_amt revenue_curr_amt,
                                          pe.event_id expenditure_item_id,
                              prd.transaction_project_id
                                                       transaction_project_id,
                              gl.NAME ledger_name,
                                                  –pe.event_desc,
                                                  xal.code_combination_id
                         FROM okc_k_headers_all_b okh,
                              — OKC_K_LINES_B okl,
                              pjf_projects_all_vl ppa,
                              pjf_project_types_vl ppt,
                              hr_all_organization_units hou,
                              hr_all_organization_units hou1,
                              pjb_billing_events pe,
                              pjf_tasks_v tsk,
                              pjf_event_types_tl etyp,
                              hz_cust_accounts hca,
                              hz_parties hp,
                              ((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,
                                       (CASE
                                           WHEN bill_transaction_type_code =
                                                                         ‘EVT’
                                              THEN transaction_id
                                        END
                                       ) AS event_id
                                  FROM pjb_rev_distributions revenuedistributionpeo
                                 WHERE 1 = 1)) prd,
                              gl_code_combinations gcc,
                              gl_ledgers gl,
                              gl_sets_of_books gsb,
                              fun_names_business_units_v fnbu,
                              xla_distribution_links xda,
                              xla_ae_lines xal,
                              pjc_exp_items_all pei
                        WHERE 1 = 1
                          AND ppa.project_type_id = ppt.project_type_id
                          AND hou.organization_id =
                                              ppa.carrying_out_organization_id
                          AND hou1.organization_id = pe.organization_id
                          AND pe.project_id = ppa.project_id
                          AND tsk.project_id = ppa.project_id
                          AND pe.task_id = tsk.task_id
                          AND pe.event_type_id = etyp.event_type_id
                          AND pe.event_id = prd.event_id(+)
                          AND okh.bill_to_acct_id = hca.cust_account_id(+)
                          AND NVL (prd.bill_transaction_type_code, ‘EVT’) =
                                                                         ‘EVT’
                          AND prd.reversed_flag IS NULL
                          AND prd.line_num_reversed IS NULL
                          AND hca.party_id = hp.party_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 pe.event_id=prd.event_id
                          AND okh.ID = pe.contract_id
                          AND NVL (xda.source_distribution_id_num_1(+),
                                   -99999) =
                                          NVL (prd.rev_distribution_id,
                                               -99999)
                          AND xal.ae_header_id = xda.ae_header_id
                          –AND xda.ae_line_num(+) <> 2
                          AND xal.ae_line_num(+) = xda.ae_line_num
                          AND gcc.code_combination_id =
                                                       xal.code_combination_id
                          –AND xda.unrounded_accounted_cr IS NOT NULL
                          AND xal.ae_header_id = xda.ae_header_id
                          AND xal.ae_line_num = xda.ae_line_num
                          –AND gcc.code_combination_id =xal.code_combination_id
                          AND gcc.segment1 <> ‘AA’
                          AND gcc.segment3 <>
                                          ‘0000’
                                                –Need to Check this condition
                          AND ppa.project_id = pei.project_id(+)
                          AND prd.transaction_id(+) = pei.expenditure_item_id
                     –AND ppa.segment1 in ‘10002’–(‘TEST-GFY-DE’)                             — 300000013862871
                     –AND okh.contract_number = ‘10002’
                     GROUP BY hca.cust_account_id,
                              hp.party_name,
                              ppa.project_id,
                              ppa.segment1,
                              okh.contract_number,
                              ppa.NAME,
                              hou.NAME,
                              hca.customer_class_code,
                              hp.state,
                              hp.city,
                              hp.country,
                              gcc.segment1,
                              gcc.segment2,
                              –gcc.account_type,
                              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,
                              gl.period_set_name,
                              prd.gl_period,
                              gsb.NAME,
                              prd.gl_date,
                              ppa.org_id,
                              –pei.person_type,
                              prd.revenue_curr_amt,
                              pe.event_id,
                              pe.event_desc,
                              prd.transaction_project_id,
                              xal.code_combination_id,
                              prd.rev_distribution_id,
                              pei.transaction_source_id,
                              gl.NAME,
                              –pei.document_id,
                              xda.source_distribution_id_num_1
                     ORDER BY okh.contract_number, pe.event_id)
           GROUP BY revenue_type,
                    customer_id,
                    customer_name,
                    project_id,
                    project_number,
                    project_name,
                    project_organization,
                    industry,
                    state,
                    city,
                    country,
                    company,
                    ACCOUNT,
                    –account_type,
                    account_name,
                    department,
                    department_name,
                    region,
                    FUNCTION,
                    project_currency_code,
                    projfunc_currency_code,
                    planning_week,
                    gl_period,
                    gl_set_of_books_name,
                    gl_line_dff_prj_num,
                    global_region,
                    region_code,
                    business_unit,
                    SOURCE,
                    code_combination_id,
                    contract_number,
                    –system_person_type
                    ledger_name,
                    bill_transaction_currency,
                    contract_currency_code,
                    revenue_currency_code,
                    ledger_currency_code,
                    project_currency_code,
                    projfunc_currency_code)
Union All
SELECT   ‘JE Adjustments’ revenue_type,
                    CAST (0 AS CHARACTER (30)) customer_id,
                    ‘JE Adjustments’ customer_name,
                    CAST (0 AS CHARACTER (30)) project_id, ‘0’ project_number,
‘0’ contract_number,                   
                    –js.user_je_source_name
                    ‘0’ project_name,
                                     –jh.description || ‘:’ || jl.description project_name,
                    fv.description project_organization,
                    jh.external_reference industry, NULL state, NULL city,
                    NULL country, cc.segment1 company, cc.segment2 ACCOUNT,
                    –cc.account_type,
                    (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 THG’
                        –‘ANSR_GL_ACCOUNT’
                        AND fv.flex_value = cc.segment2) account_name,
                    cc.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 THG’
                        –‘ANSR_GL_ACCOUNT’
                        AND fv.flex_value = cc.segment3) department_name,
                    cc.segment4 region, cc.segment5 FUNCTION,
                    SUM ((  NVL ((  NVL (jl.entered_dr, 0)
                                  – NVL (jl.entered_cr, 0)
                                 ),
                                 0
                                )
                          * -1
                         )
                        ) project_curr_revenue_amt,
jl.currency_code bill_transaction_currency,
NULL bill_transaction_currency_rev,
Null contract_currency_code,
Null cont_curr_revenue_amt_sum,
Null revenue_currency_code,
                    gl.currency_code ledger_currency_code,
                    NULL ledger_curr_revenue_amt,
                              SUM (  NVL (NVL (jl.accounted_dr, jl.entered_dr), 0)
                              – (NVL (NVL (jl.accounted_cr, jl.entered_cr), 0)
                                )
                             ) revenue_curr_amt,
                    — Convert based on SOB Currency – Note: Accounted# are null when SOB Currency
                    — is the same as Journal Currency
                    SUM
                       (  (  (  NVL (NVL (jl.accounted_dr, jl.entered_dr), 0)
                              – (NVL (NVL (jl.accounted_cr, jl.entered_cr), 0)
                                )
                             )
                           * NVL ((SELECT   gper.avg_rate
                                       FROM gl_lookups lk,
                                            gl_translation_rates gper
                                      WHERE lk.lookup_type =
                                                        ‘TRANSLATION_BAL_TYPE’
                                        AND lk.lookup_code = gper.actual_flag
                                        AND gper.period_name = jl.period_name
                                        AND gper.set_of_books_id =
                                                           gsb.set_of_books_id
                                        –AND gper.functional_currency=gsb.currency_code
                                        AND gper.to_currency_code = ‘USD’
                                   GROUP BY gper.avg_rate,
                                            gper.period_name,
                                            gper.to_currency_code),
                                  1
                                 )
                                                         –xxansr_utils_pkg.get_period_avg_rate
                          —                                 (gsb.currency_code,
                            —                                jl.period_name,
                              —                              ‘USD’
                                —                           )
                          )
                        * 1
                       ) usd_revenue_amount,
                    jl.currency_code project_currency_code, 
    Null project_revenue_amount,
                    gsb.currency_code projfunc_currency_code,
                    SUM
                       (  (  NVL (NVL (jl.accounted_dr, jl.entered_dr), 0)
                           – (NVL (NVL (jl.accounted_cr, jl.entered_cr), 0))
                          )
                        * -1
                       ) projfunc_revenue_amount,
                    (SELECT    ‘Q’
                            || quarter_num
                            || ‘-WK’
                            || LPAD
                                  (TO_CHAR (DECODE (period_num,
                                                    53, 14,
                                                    DECODE (MOD (period_num,
                                                                 13
                                                                ),
                                                            0, 13,
                                                            MOD (period_num,
                                                                 13
                                                                )
                                                           )
                                                   )
                                           ),
                                   2,
                                   ‘0’
                                  )
                       FROM gl_periods
                      WHERE 1 = 1
                        AND period_set_name = ‘THG 4/4/5’
                                                         –period_type = ‘4_4_57130283831’
                        –’22’                         — PA Periods
                        AND TRUNC (jl.effective_date) BETWEEN start_date
                                                          AND end_date
                        AND adjustment_period_flag = ‘N’) planning_week,
                   
                    –‘JAN-05’ gl_period,
                    jl.period_name gl_period, gsb.NAME gl_set_of_books_name,
                    jl.attribute1 gl_line_dff_prj_num, NULL global_region,
                    ‘ ‘ region_code, gsb.NAME business_unit,
                    js.je_source_name source,
                    –NULL expenditure_item_id,
                    –NULL transaction_project_id,
                    gl.NAME ledger_name,
cc.code_combination_id
               FROM gl_je_headers jh,
                    gl_je_lines jl,
                    gl_code_combinations cc,
                    fnd_flex_values_vl fv,
                    fnd_flex_value_sets fvs,
                    gl_je_sources js,
                    gl_sets_of_books gsb,
gl_ledgers gl
              WHERE jl.je_header_id = jh.je_header_id
                –AND jh.je_header_id = 7327
                AND jh.ledger_id = gsb.set_of_books_id
AND gl.ledger_id = gsb.set_of_books_id
                AND NVL (jh.status, ‘X’) = ‘P’
                AND jh.je_source = js.je_source_name
                AND fv.flex_value_set_id = fvs.flex_value_set_id
                AND fvs.flex_value_set_name = ‘Department THG’
                –‘ANSR_GL_DEPARTMENT’
                AND NVL (jh.je_category, ‘x’) != ‘1’
                AND fv.flex_value = cc.segment3
                AND jl.code_combination_id = cc.code_combination_id
                AND cc.account_type IN (‘R’, ‘E’)
                AND EXISTS (
                       SELECT ‘X’
                         FROM gl_code_combinations
                        WHERE 1 = 1
                          AND account_type IN (‘R’, ‘E’)
—                              AND (   segment2 BETWEEN ‘410500’ AND ‘433099’
                         —                              OR segment2 BETWEEN ‘532000’ AND ‘547099’
                         —                             )
                          AND segment1 != ‘AA’
                          AND code_combination_id = jl.code_combination_id)
                AND (    cc.segment2 BETWEEN ‘410000’ AND ‘549999’
                     AND cc.segment2 NOT BETWEEN ‘500000’ AND ‘528509’
                     AND cc.segment2 NOT BETWEEN ‘528511’ AND ‘529999’
                    )
           GROUP BY js.user_je_source_name,
                    jh.description,
                    jl.description,
                    fv.description,
                    jh.external_reference,
                    cc.segment1,
                    cc.segment2,
                    cc.account_type,
                    cc.segment3,
                    cc.segment4,
                    cc.segment5,
                    jl.period_name,
                    gsb.set_of_books_id,
                    jl.currency_code,
                    gsb.currency_code,
                    jl.effective_date,
                    jl.period_name,
                    gsb.name,
                    jl.attribute1,
                    cc.segment1,
                    js.je_source_name,
                    cc.code_combination_id,
                    jl.period_name,
gl.currency_code,
gl.name)
   WHERE gl_period IN (:p_gl_period)
—     AND  ROWNUM <= 75001
     /*AND (    ACCOUNT BETWEEN ‘410000’ AND ‘549999’
          AND ACCOUNT NOT BETWEEN ‘500000’ AND ‘528509’
          AND ACCOUNT NOT BETWEEN ‘528511’ AND ‘529999’
         )*/
GROUP BY revenue_type,
         customer_id,
         customer_name,
         project_id,
         project_number,
         contract_number,
         project_name,
         project_organization,
         industry,
         state,
         city,
         country,
         company,
         ACCOUNT,
         account_name,
         department,
         department_name,
         region,
         FUNCTION,
         bill_transaction_currency,
         contract_currency_code,
         revenue_currency_code,
         ledger_currency_code,
         project_currency_code,
         projfunc_currency_code,
         planning_week,
         gl_period,
         gl_set_of_books_name,
         gl_line_dff_prj_num,
         region_code,
         SOURCE,
         business_unit,
         ledger_name

  • September 22, 2018 | 23 views
  • Comments