Revenue Allocation Query

 WITH wc1_active_tree_version AS 

(

    SELECT /*+ MATERIALIZE */ ftv.tree_version_id

    FROM fnd_tree_version ftv

    WHERE LOWER(ftv.tree_structure_code) = 'gl_acct_flex'

      AND LOWER(ftv.tree_code) = 'xxxxxxxx'

      AND LOWER(ftv.status) = 'active'

      AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(ftv.effective_start_date), TRUNC(SYSDATE)) 

                              AND NVL(TRUNC(ftv.effective_end_date), TRUNC(SYSDATE))

),

GL_PERIOD_DATA AS 

(

  SELECT /*+ MATERIALIZE */ START_DATE, PERIOD_YEAR, PERIOD_NUM ,PERIOD_NAME

      FROM gl_periods

      WHERE 1=1

      AND period_set_name = 'xxxxxxxxxxx'

  and PERIOD_NUM = (SELECT CASE WHEN :p_period_name LIKE 'Jan%'

THEN 12

ELSE PERIOD_NUM-1

END    

                                                 FROM gl_periods 

                                                 where PERIOD_NAME = :p_period_name --'Feb-24'  

                                                 and period_set_name = 'xxxxxxxxxxxxxxx'

                    )

            and PERIOD_YEAR = (SELECT CASE WHEN :p_period_name LIKE 'Jan%'

                                                  then PERIOD_YEAR-1

                                                              else PERIOD_YEAR

                                                            end 

          FROM gl_periods 

  where PERIOD_NAME = :p_period_name 

  --and rownum <2

   and period_set_name = 'xxxxxxxxxxxxx')

   

)

select 

journalsource

,Area_Code

,Account

,SUM(NVL(CURR_PERIOD_BAL,0)) SUM_CUR_PERIOD_BAL

,SUM(NVL(PREV_PERIOD_BAL,0)) SUM_PREV_PERIOD_BAL

,(SUM(NVL(CURR_PERIOD_BAL,0))- SUM(NVL(PREV_PERIOD_BAL,0))) SUM_DIFF

from(

SELECT    

gjs.USER_JE_SOURCE_NAME AS journalsource,

        GSVHR.Ancestor_PK1_value || ' - ' || 

            (SELECT description

             FROM FND_FLEX_VALUES_VL ffv

             WHERE ffv.FLEX_VALUE = GSVHR.Ancestor_PK1_value  -- reference the correct field

               AND ffv.VALUE_CATEGORY = 'xxxxxxxxx') AS Area_Code,

        gcc.SEGMENT4 || ' - ' || gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 4, gcc.SEGMENT4) AS Account

,

        (SELECT sum(NVL(a.period_net_dr,0)-NVL(a.period_net_cr,0))

FROM gl_balances a

WHERE 1=1

and a.translated_flag is null

and a.PERIOD_NAME = :p_period_name

            and a.code_combination_id=gcc.code_combination_id

) AS CURR_PERIOD_BAL,

0 AS PREV_PERIOD_BAL

FROM GL_CODE_COMBINATIONS gcc, 

     GL_SEG_VAL_HIER_RF GSVHR,

GL_JE_HEADERS gjh, 

     GL_JE_LINES gjl, 

GL_JE_SOURCES GJS

    WHERE GCC.SEGMENT1 = GSVHR.PK1_VALUE

      AND GSVHR.DISTANCE = 3 

      AND GSVHR.TREE_CODE = 'xxxxxxxx'

  and gcc.code_combination_id=gjl.code_combination_id

  and gjh.JE_HEADER_ID = gjl.JE_HEADER_ID

      AND GJS.JE_SOURCE_NAME = gjh.je_source

      AND gjl.status = 'P'

  AND gjh.PERIOD_NAME = :p_period_name

      AND ((COALESCE(NULL, :p_journal_source) IS NULL) OR (GJS.USER_JE_SOURCE_NAME IN (:p_journal_source)))

      AND GJS.LANGUAGE = 'US' 

      AND GJL.Currency_Code = 'STAT'

      AND ((COALESCE(NULL, :p_account) IS NULL) OR (gcc.segment4 IN (:p_account)))

      AND ((COALESCE(NULL, :p_area) IS NULL) OR (gsvhr.ancestor_pk1_value IN (:p_area)))

      AND GSVHR.Ancestor_PK1_value LIKE 'K%'

      AND GSVHR.tree_version_id IN (SELECT tree_version_id FROM wc1_active_tree_version)

      AND gsvhr.tree_code = 'xxxxxxxx'

      AND gsvhr.tree_structure_code = 'GL_ACCT_FLEX'

  AND gcc.SEGMENT4 >= '900000' 

--and gd.PERIOD_NAME = :p_period_name 

UNION ALL

SELECT    

gjs.USER_JE_SOURCE_NAME AS journalsource,

        GSVHR.Ancestor_PK1_value || ' - ' || 

            (SELECT description

             FROM FND_FLEX_VALUES_VL ffv

             WHERE ffv.FLEX_VALUE = GSVHR.Ancestor_PK1_value  -- reference the correct field

               AND ffv.VALUE_CATEGORY = 'xxxxxxxxxx') AS Area_Code,

        gcc.SEGMENT4 || ' - ' || gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 4, gcc.SEGMENT4) AS Account

,

       0 AS CURR_PERIOD_BAL,

(

SELECT sum(NVL(a.period_net_dr,0)-NVL(a.period_net_cr,0))

FROM gl_balances a

WHERE 1=1

and a.translated_flag is null

and a.PERIOD_NUM = (SELECT CASE WHEN :p_period_name LIKE 'Jan%'

THEN 12

ELSE PERIOD_NUM-1

END    

                                                 FROM gl_periods 

                                                 where PERIOD_NAME = :p_period_name --'Feb-24'  

                                                 and period_set_name = 'xxxxxxxxx'

                    )

            and a.PERIOD_YEAR = (SELECT CASE WHEN :p_period_name LIKE 'Jan%'

                                                  then PERIOD_YEAR-1

                                                              else PERIOD_YEAR

                                                            end 

          FROM gl_periods 

  where PERIOD_NAME = :p_period_name 

 

   and period_set_name = 'xxxxxxxxx')

            and a.code_combination_id=gcc.code_combination_id

)  AS PREV_PERIOD_BAL

FROM GL_CODE_COMBINATIONS gcc, 

     GL_SEG_VAL_HIER_RF GSVHR,

GL_JE_HEADERS gjh, 

     GL_JE_LINES gjl, 

GL_JE_SOURCES GJS

    WHERE GCC.SEGMENT1 = GSVHR.PK1_VALUE

      AND GSVHR.DISTANCE = 3 

      AND GSVHR.TREE_CODE = 'xxxxxxxx'

  and gcc.code_combination_id=gjl.code_combination_id

  and gjh.JE_HEADER_ID = gjl.JE_HEADER_ID

      AND GJS.JE_SOURCE_NAME = gjh.je_source

      AND gjl.status = 'P'

  AND gjh.PERIOD_NAME = (SELECT period_name FROM GL_PERIOD_DATA)

      AND ((COALESCE(NULL, :p_journal_source) IS NULL) OR (GJS.USER_JE_SOURCE_NAME IN (:p_journal_source)))

      AND GJS.LANGUAGE = 'US' 

      AND GJL.Currency_Code = 'STAT'

      AND ((COALESCE(NULL, :p_account) IS NULL) OR (gcc.segment4 IN (:p_account)))

      AND ((COALESCE(NULL, :p_area) IS NULL) OR (gsvhr.ancestor_pk1_value IN (:p_area)))

      AND GSVHR.Ancestor_PK1_value LIKE 'K%'

      AND GSVHR.tree_version_id IN (SELECT tree_version_id FROM wc1_active_tree_version)

      AND gsvhr.tree_code = 'xxxxxxxxx'

      AND gsvhr.tree_structure_code = 'GL_ACCT_FLEX'

  AND gcc.SEGMENT4 >= '900000' 

)

GROUP BY 

journalsource

,Area_Code

,Account

order by 

journalsource

,Area_Code

,Account

Fixed Assets To Payables Query

 SELECT ma.book_type_code AS book,

       ma.posting_status AS queue_name,

       TRIM(TO_CHAR(ma.Payables_cost, '99999999.00')) AS cost,

       ma.invoice_number,

       b.line_number AS invoice_line_number,

       CASE WHEN b.quantity_invoiced IS NOT NULL THEN b.quantity_invoiced ELSE 1 END AS units,

       b.description,

       gcc.segment4 AS acct,

       TO_CHAR(ma.create_batch_date, 'MM-DD-YYYY') AS ma_create_date ,

       ma.invoice_id,

       ma.feeder_system_name AS source, 

       SYSDATE AS run_dt,

       'X' KEY

  FROM fa_mass_additions ma,

       ap_invoices_all a,

       ap_invoice_lines_all b,

       ap_invoice_distributions_all c,

       gl_code_combinations gcc

 WHERE a.invoice_id = b.invoice_id

   AND b.invoice_id = c.invoice_id

   AND b.line_number = c.invoice_line_number

   AND gcc.code_combination_id = c.DIST_CODE_COMBINATION_ID

   AND b.invoice_id = ma.invoice_id

   AND b.line_number = ma.invoice_line_number

   AND ma.posting_status = 'NEW'

   AND ma.fixed_assets_cost != 0

   AND ma.feeder_system_name = 'ORACLE PAYABLES'

   AND gcc.segment4 = '164100'

ORDER BY  ma.book_type_code DESC, ma.invoice_number, b.line_number

Oracle Fixed Assets Integration Report

 

WITH hier_area AS (SELECT /*+ materialize */ DISTINCT dep29_pk1_value, dep1_pk1_value

                     FROM gl_seg_val_hier_cf WHERE tree_structure_code = 'GL_ACCT_FLEX'

                      AND tree_code = 'xxxxxxxxx'

                      AND tree_version_id = :P_TREE_V

                  ), 

report AS (

    SELECT 

        open_period,

        comp_code,

        facility_id,

        facility_desc,

        asset_id,

        description,

        conversion_id,

        parent_id,

        unit_number,

        vin,

        category_major,

        category_minor,

        quantity,

        SUM(cost)                        cost,

        SUM(accum_deprn)                 accum_deprn,

        ( SUM(cost) - SUM(accum_deprn) ) nbv,

        SUM(deprn)                       deprn,

        ( SUM(ytd_dpr) + SUM(ytd_pdp) )  ytd_dpr,

        revaluation_reserve,

        in_service_date,

        life,

        CASE

            WHEN rem_life_in_mon <= 0 THEN 0

            ELSE rem_life_in_mon

        END rem_life_in_mon,

        convention,

        dept,

        product,

        project,

        from_curr,

        wm_ownership,

        acquired_asset,

        fa_lcode,

        address,

        city,

        county,

        state,

        country,

        postal_code,

        concat_expns_acc,

        goodwill_ownership,

        run_date,

        area,

        book_type_code,

        'X' AS transaction_type_code

    FROM

        (

            SELECT /*+ materialize */

                gcc.segment1                                                                     comp_code,

                gcc.segment2                                                                     facility_id,

                gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 2, gcc.segment2) facility_desc,

                fab.asset_number                                                                 asset_id,

                fab.attribute1                                                                   conversion_id,

                fab.description,

                fcb.segment1                                                                     category_major,

                fcb.segment2                                                                     category_minor,

                fl.segment2                                                                      fa_lcode,

                hl.address_line_1                                                                address,

                hl.postal_code,

                hl.town_or_city                                                                  city,

                hl.region_1                                                                      county,

                hl.region_2                                                                      state,

                hl.country,

                fct.prorate_convention_code                                                      convention,

                fm.life_in_months                                                                life,

                to_char(fb.date_placed_in_service, 'MM/DD/YYYY', 'nls_date_language=American')   in_service_date,

                nvl(fdh.units_assigned, 0)                                                       quantity,

                gcc.segment3                                                                     dept,

                gcc.segment5                                                                     product,

                gcc.segment6                                                                     project,

                sob.currency_code                                                                from_curr,

                nvl(fdd.cost, 0)                                                                 cost,

                nvl(fdd.deprn_reserve, 0)                                                        accum_deprn,

                nvl(fdd.ytd_deprn, 0)                                                            ytd_dpr1,

                nvl((SELECT SUM(nvl(xl.accounted_cr, - 1 * xl.accounted_dr)) ytd_deprn

                       FROM fa_deprn_detail      fdd1,

                            xla_ae_headers       xh,

                            xla_ae_lines         xl,

                            gl_code_combinations gcc1

                      WHERE fdd1.asset_id = fab.asset_id

                        AND fdd1.book_type_code = fb.book_type_code

                        AND fdd1.distribution_id = fdd.distribution_id

                        AND fdd1.event_id = xh.event_id

                        AND xh.ae_header_id = xl.ae_header_id

and xl.application_id = 140

                        AND xl.accounting_class_code = 'DEPRECIATION_RESERVE'

                        AND gcc1.code_combination_id = xl.code_combination_id

                        AND gcc1.segment2 = gcc.segment2

                        AND fdd1.period_counter IN(SELECT MAX(fdpi.period_counter)

                                                     FROM fa_deprn_detail fdpi

                                                    WHERE fdpi.book_type_code = fb.book_type_code

                                                      AND fdpi.asset_id = fab.asset_id

                                                      AND fdpi.distribution_id = fdd.distribution_id)

                    ),

                  0)                                                                             deprn,

                nvl((

                    SELECT 

                        SUM(nvl(xla1.accounted_cr, - 1 * xla1.accounted_dr))

                    FROM

                        ( SELECT DISTINCT fdd1.event_id

                            FROM fa_deprn_detail fdd1

                           WHERE fdd1.asset_id = fab.asset_id

                             AND fdd1.book_type_code = fb.book_type_code

                             AND fdd1.deprn_source_code = 'D'

                             AND fdd1.period_counter IN( SELECT period_counter

                                                           FROM fa_deprn_periods fdp1

                                                          WHERE fdp1.book_type_code = fb.book_type_code -- fdd1.book_type_code commented by amruta

                                                            AND fdp1.fiscal_year = EXTRACT(YEAR FROM sysdate) )

                        )                    fdd1,

                        xla_ae_headers       xah1,

                        xla_ae_lines         xla1,

                        gl_code_combinations gcc1

                    WHERE 1=1

and fdd1.event_id = xah1.event_id

                        AND xah1.ae_header_id = xla1.ae_header_id

and xla1.application_id = 140

                        AND xla1.accounting_class_code = 'DEPRECIATION_RESERVE'

                        AND gcc1.code_combination_id = xla1.code_combination_id

                        AND gcc1.segment1 = gcc.segment1

                        AND gcc1.segment2 = gcc.segment2

                        AND EXTRACT(YEAR FROM xla1.accounting_date) = EXTRACT(YEAR FROM sysdate)

                ),

                    0)                                                                          ytd_dpr,

                nvl((

                    SELECT SUM(nvl(xl1.accounted_dr, - 1 * xl1.accounted_cr))

                    FROM fa_transaction_headers ftv1,

                        xla_ae_headers         xh1,

                        xla_ae_lines           xl1,

                        gl_code_combinations   gcc1,

                        fa_additions_vl        fab11

                    WHERE ftv1.asset_id = fab.asset_id

                        AND ftv1.book_type_code = fb.book_type_code

                        AND ftv1.event_id = xh1.event_id

                        AND xl1.ae_header_id = xh1.ae_header_id

                        AND xl1.code_combination_id = gcc1.code_combination_id

                        AND gcc1.segment1 = gcc.segment1

                        AND gcc1.segment2 = gcc.segment2

                        AND ftv1.asset_id = fab11.asset_id

                        AND EXTRACT(YEAR FROM xl1.accounting_date) = EXTRACT(YEAR FROM sysdate)

                        AND xl1.accounting_class_code IN('DEPRECIATION_EXPENSE', 'UNPLANNED_DEPRECIATION_EXPENSE')

                ),

                    0) AS ytd_pdp,

                decode(fds.reval_reserve, 0, '-', fds.reval_reserve)                             revaluation_reserve,

                fab.manufacturer_name                                                            unit_number,

                (CASE

                     WHEN (SELECT COUNT(asset_id) FROM fa_additions_vl WHERE parent_asset_id = fab.asset_id) > 0 THEN fab.asset_number

                     WHEN (SELECT COUNT(parent_asset_id) FROM fa_additions_vl WHERE asset_id = fab.asset_id) > 0 

                           THEN (SELECT asset_number FROM fa_additions_vl WHERE asset_id = fab.parent_asset_id)

                     WHEN ((SELECT COUNT(asset_id) FROM fa_additions_vl WHERE parent_asset_id = fab.asset_id) = 0 AND

                            (SELECT COUNT(parent_asset_id) FROM fa_additions_vl WHERE asset_id = fab.asset_id ) = 0 

                          ) THEN NULL

                     ELSE NULL

                 END)                                                                            parent_id,

                fab.serial_number                                                                vin,

                fab.attribute2                                                                   wm_ownership,

                fab.attribute4                                                                   goodwill_ownership,

                fab.attribute5                                                                   acquired_asset,

                (

                    SELECT

                        to_char(sysdate, 'dd')

                        || '-'

                        || upper(period_name)

                    FROM

                        gl_periods

                    WHERE

                            period_num = to_char(sysdate, 'mm')

                        AND period_set_name = 'xxxxxxxxx'

                        AND period_year = EXTRACT(YEAR FROM sysdate)

                )                                                                                run_date,

                ftn.dep29_pk1_value                                                              area,

                fb.book_type_code,

                ftv.transaction_type_code,

                (

                    SELECT

                        substr(period_name, 1, 3)

                        || '-'

                        || substr(period_name, 7, 8)

                    FROM

                        fa_deprn_periods

                    WHERE

                        period_close_date IS NULL

                        AND book_type_code = fb.book_type_code

                )                                                                                open_period,

                gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 

               ||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8      concat_expns_acc,

                ( ( fm.life_in_months ) - ( ( ( ( (

                    SELECT

                        fiscal_year

                    FROM

                        fa_deprn_periods

                    WHERE

                        period_close_date IS NULL

                        AND book_type_code = fb.book_type_code

                ) - EXTRACT(YEAR FROM fb.date_placed_in_service) ) * 12 ) + (

                    SELECT

                        period_num

                    FROM

                        fa_deprn_periods

                    WHERE

                        period_close_date IS NULL

                        AND book_type_code = fb.book_type_code

                ) ) - EXTRACT(MONTH FROM fb.date_placed_in_service) ) )                          rem_life_in_mon

            FROM

                fa_transaction_headers  ftv,

                fa_additions_vl         fab,

                fa_books                fb,

                fa_retirements          fr,

                fa_deprn_summary        fds,

                fa_deprn_detail         fdd,

                fa_distribution_history fdh,

                gl_code_combinations    gcc,

                hier_area               ftn,

                fa_deprn_periods        fdp,

                fa_methods              fm,

                fa_locations            fl,

                hr_locations_all        hl,

                fa_categories_b         fcb,

                fa_convention_types     fct,

                fa_book_controls        fbc,

                gl_sets_of_books        sob

            WHERE fab.asset_id NOT IN (SELECT DISTINCT fth1.asset_id

                                         FROM fa_transaction_headers fth1

                                        WHERE fth1.transaction_type_code = 'FULL RETIREMENT'

                                          AND fth1.date_effective = (SELECT MAX(date_effective) FROM fa_transaction_headers

                                                                      WHERE asset_id = fab.asset_id

                                                                        AND book_type_code = ftv.book_type_code)

                                          AND fth1.book_type_code = fb.book_type_code

                                      )

                AND fab.asset_category_id = fcb.category_id

                AND fab.asset_id = fb.asset_id

                AND fb.date_ineffective IS NULL

                AND fb.convention_type_id = fct.convention_type_id

                AND fab.asset_id = fr.asset_id (+)

                AND fr.retirement_id (+) = fb.retirement_id

                AND fbc.book_type_code = fb.book_type_code

                AND fbc.set_of_books_id = sob.set_of_books_id

                AND fds.deprn_source_code = 'DEPRN'

                AND fds.asset_id = fb.asset_id

                AND fds.book_type_code = fb.book_type_code

                AND fdp.period_counter = fds.period_counter

                AND fdp.book_type_code = fds.book_type_code

                AND fdd.asset_id = fb.asset_id

                AND fdd.asset_id = fds.asset_id

                AND fdd.book_type_code = fb.book_type_code

                AND fdd.period_counter = fds.period_counter

                AND fdd.period_counter = fdp.period_counter

                AND fdd.period_counter IN ( SELECT MAX(fdpi.period_counter) FROM fa_deprn_detail fdpi

                                             WHERE fdpi.book_type_code = fb.book_type_code

                                               AND fdpi.asset_id = fb.asset_id)

                AND fb.method_id = fm.method_id

                AND fdd.distribution_id = fdh.distribution_id

                AND fdh.asset_id = fab.asset_id

                AND fdh.book_type_code = fb.book_type_code

                AND fdh.code_combination_id = gcc.code_combination_id

                AND fdh.date_ineffective IS NULL

                AND ftn.dep1_pk1_value = gcc.segment1

                AND fbc.book_class = 'CORPORATE' -- p_book_class

                AND fb.date_effective IN (

                    SELECT

                        MAX(fb1.date_effective)

                    FROM

                        fa_books fb1

                    WHERE

                            fb1.asset_id = fab.asset_id

                        AND fb1.book_type_code = fb.book_type_code

                )

                AND fl.location_id = fdh.location_id

                AND fl.segment2 = hl.internal_location_code

                AND fdh.transaction_header_id_in = ftv.transaction_header_id

                AND fb.asset_id = ftv.asset_id

                AND fb.book_type_code = ftv.book_type_code

                 -- and ftv.event_id = xh.event_id

        )

    GROUP BY

        comp_code,

        facility_id,

        facility_desc,

        asset_id,

        conversion_id,

        description,

        category_major,

        category_minor,

        fa_lcode,

        address,

        postal_code,

        city,

        county,

        state,

        country,

        convention,

        life,

        in_service_date,

        quantity,

        dept,

        product,

        project,

        from_curr,

        revaluation_reserve,

        unit_number,

        parent_id,

        vin,

        wm_ownership,

        goodwill_ownership,

        acquired_asset,

        run_date,

        area,

        book_type_code,

        transaction_type_code,

        open_period,

        concat_expns_acc,

        rem_life_in_mon

    ORDER BY

        comp_code,

        facility_id,

        asset_id

)

SELECT

    *

FROM

    report

ORDER BY

    comp_code,

    facility_id,

    asset_id

Query to get Payables and Receivables Project Details (Project Cash Flow Report)

with gl_period_dates as ( 

       select period_name, 

              year_start_date, 

              add_months(year_start_date,12)-1 year_end_date, 

              quarter_start_date, 

              add_months(quarter_start_date,3)-1 quarter_end_date,

              start_date period_start_date, 

              end_date period_end_date

       from   gl_periods 

       where  period_set_name='Global Calendar'

       and    period_year=:p_year 

       and   (quarter_num IN (:p_quarter) OR coalesce(:p_quarter, NULL) IS NULL)

       and   (period_name IN (:p_month) OR coalesce(:p_month, NULL) IS NULL)),

receivables as (

select fnbuv.bu_id, 

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              pcpl.contract_id,

              fnbuv.bu_name,

              xlp.name le_name,

              ppavl.name, 

              ppavl.segment1,

OCH.contract_number,

              SUM(NVL(aps.amount_applied,0)) cash_in,

-- SUM(NVL(xal.ACCOUNTED_DR, 0)-NVL(xal.ACCOUNTED_CR, 0)) billing_amt

(SELECT sum(NVL(PROJECT_CURR_BILLED_AMT,0)) 

          FROM

          pjb_inv_line_dists idl

          

          WHERE 1=1

          and idl.CONTRACT_ID = och.id

          and  invoice_id in ( select invoice_id from pjb_invoice_headers 

  where

          contract_id = idl.contract_id  

  and invoice_status_code = 'ACCEPTED')

          ) billing_amt

FROM   ra_customer_trx_all rct,

okc_k_headers_all_b och,

xla_transaction_entities xlate,

xla_events xet,

xla_ae_headers xah,

xla_ae_lines xal,

ar_payment_schedules_all aps,

fun_names_business_units_v fnbuv,

xle_entity_profiles xlp,

gl_period_dates gpd,

              pjf_projects_all_vl ppavl,

              (select distinct project_id, 

                      contract_id, 

                      major_version

               from   pjb_cntrct_proj_links ) pcpl

WHERE to_char(rct.interface_header_attribute1) = to_char(och.contract_number)

AND   to_char(rct.interface_header_attribute2) = to_char(och.id)

AND   rct.set_of_books_id = xah.ledger_id

       and   pcpl.contract_id=och.id

       and   pcpl.project_id=ppavl.project_id

       --and   och.version_type='C'

AND   och.major_version = (

         SELECT max(och1.major_version)

         FROM okc_k_headers_all_b och1

         WHERE och1.id = OCH.id)

       AND   pcpl.major_version = och.major_version  

       AND   xet.entity_id = xlate.entity_id

       AND   xet.application_id = xlate.application_id

       AND   rct.customer_trx_id = xlate.source_id_int_1

       AND   xal.ae_header_id = xah.ae_header_id

       AND   xet.event_id = xah.event_id

       AND   xah.ledger_id = Xal.ledger_id

   AND   xlp.LEGAL_EMPLOYER_FLAG='Y'

       AND   xal.accounting_class_code = 'RECEIVABLE'

       AND   rct.customer_trx_id = aps.customer_trx_id

       AND   fnbuv.bu_id = rct.org_id

       AND   xlp.legal_entity_id = rct.legal_entity_id

       and   xal.period_name = gpd.period_name

   AND   xlate.entity_code='TRANSACTIONS'

   and   xlate.ledger_id=xah.ledger_id

       and   trunc(xal.accounting_date) between gpd.period_start_date and gpd.period_end_date

       /* --- Prompts --- */

       and  (ppavl.project_id IN (:p_project_id) OR COALESCE(:p_project_id, NULL) IS NULL)

       and  (ppavl.carrying_out_organization_id IN (:p_carrying_out_organization_id) OR COALESCE(:p_carrying_out_organization_id, NULL) IS NULL)

       and  (fnbuv.bu_id IN (:p_business_unit_id) OR COALESCE(:p_business_unit_id, NULL) IS NULL)

       and  (xlp.legal_entity_id IN (:p_legal_entity_id) OR COALESCE(:p_legal_entity_id, NULL) IS NULL)

       and  (och.id IN (:p_contract_id) OR COALESCE(:p_contract_id, NULL) IS NULL)

and exists ( 

              select 'Y'

              from   gl_seg_val_hier_cf cf,

                     fnd_tree_version_vl ftvvl,

                     xla_mapping_set_values xmsv

              where  cf.tree_structure_code='GL_ACCT_FLEX'

              and    ftvvl.tree_code=cf.tree_code

              and    xmsv.enabled_flag='Y'

              and    ftvvl.tree_version_id=cf.tree_version_id

              and    xmsv.value_constant=cf.dep0_pk1_value

              and    xmsv.input_value_constant1=TO_CHAR(ppavl.carrying_out_organization_id)

              and    exists (

                     select 'Y'

                     from   xla_mapping_set_flavors xmsf, 

                            fnd_id_flex_structures fifs

                     where  xmsf.stripe_id=fifs.id_flex_num

                     and    fifs.id_flex_code='GL#'

                     and    fifs.id_flex_structure_code='xxxx' 

                     and    xmsf.mapping_set_code=xmsv.mapping_set_code

                     and    xmsf.mapping_set_flavor_id=xmsv.mapping_set_flavor_id)

              and    sysdate between ftvvl.effective_start_date and ftvvl.effective_end_date

              and    sysdate between xmsv.effective_start_date and xmsv.effective_end_date

              /* --- Prompts --- */

              and   (cf.dep0_pk1_value in (:p_lob_profit_centre) or coalesce(:p_lob_profit_centre,null) is null)

              and   (cf.dep27_pk1_value in (:p_lob_sub_business_group) or coalesce(:p_lob_sub_business_group,null) is null)

              and   (cf.dep28_pk1_value in (:p_lob_business_group) or coalesce(:p_lob_business_group,null) is null)

              and   (cf.dep29_pk1_value in (:p_lob_service_bundle) or coalesce(:p_lob_service_bundle,null) is null)

              and   (cf.dep30_pk1_value in (:p_lob_division) or coalesce(:p_lob_division,null) is null)

              /* --- End Prompts --- */

              and    cf.tree_code='XXXXX'

              and    xmsv.mapping_set_code ='WDAP_LOB_POO'

              union all 

              select 'Y' 

              from   dual 

              where  coalesce(:p_lob_profit_centre,null) is null

              and    coalesce(:p_lob_sub_business_group,null) is null

              and    coalesce(:p_lob_business_group,null) is null

              and    coalesce(:p_lob_service_bundle,null) is null

              and    coalesce(:p_lob_division,null) is null)

       /* --- End Prompts --- */ 

       and    1=1

GROUP BY fnbuv.bu_id, 

          och.id,

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              pcpl.contract_id,

              fnbuv.bu_name,

              xlp.name,

              ppavl.name, 

              ppavl.segment1,

OCH.contract_number),

payables as (

SELECT fnbuv.bu_id, 

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              aia.invoice_id,              

              fnbuv.bu_name,

              xlp.name le_name,

              ppavl.name, 

              ppavl.segment1, 

SUM(NVL(aipa.INVOICE_BASE_AMOUNT, 0)) cash_out,

SUM(NVL(xal.ACCOUNTED_DR, 0)) liability_amt

FROM   ap_invoices_all aia,

ap_invoice_payments_all aipa,

fun_names_business_units_v fnbuv,

xla_transaction_entities xte,

xla_events xe,

xla_ae_headers xah,

xla_ae_lines xal,

xle_entity_profiles xlp,

gl_period_dates gpd,

              pjf_projects_all_vl ppavl

WHERE  1 = 1

AND   aipa.invoice_id = aia.invoice_id

       and   aia.project_id=ppavl.project_id

AND   fnbuv.bu_id = aia.org_id

AND   aia.invoice_id = xte.source_id_int_1

AND   aia.set_of_books_id = xah.ledger_id

AND   xe.entity_id = xah.entity_id

AND   xte.entity_id = xe.entity_id

AND   xal.ae_header_id = xah.ae_header_id

AND   xte.application_id = xah.application_id

and   xte.ledger_id=xah.ledger_id

    AND   xte.entity_code='TRANSACTIONS'

AND   xal.accounting_class_code = 'LIABILITY'

AND   xlp.legal_entity_id = aia.legal_entity_id

       and   xal.period_name = gpd.period_name

       and   trunc(xal.accounting_date) between gpd.period_start_date and gpd.period_end_date

       /* --- Prompts --- */

       and  (fnbuv.bu_id IN (:p_business_unit_id) OR COALESCE(:p_business_unit_id, NULL) IS NULL)

       and  (xlp.legal_entity_id IN (:p_legal_entity_id) OR COALESCE(:p_legal_entity_id, NULL) IS NULL)

       and  (ppavl.project_id IN (:p_project_id) OR COALESCE(:p_project_id, NULL) IS NULL)

       and  (ppavl.carrying_out_organization_id IN (:p_carrying_out_organization_id) OR COALESCE(:p_carrying_out_organization_id, NULL) IS NULL)

       and exists ( 

              select 'Y'

              from   gl_seg_val_hier_cf cf,

                     fnd_tree_version_vl ftvvl,

                     xla_mapping_set_values xmsv

              where  cf.tree_structure_code='GL_ACCT_FLEX'

              and    ftvvl.tree_code=cf.tree_code

              and    xmsv.enabled_flag='Y'

              and    ftvvl.tree_version_id=cf.tree_version_id

              and    xmsv.value_constant=cf.dep0_pk1_value

              and    xmsv.input_value_constant1=TO_CHAR(ppavl.carrying_out_organization_id) 

              and    exists (

                     select 'Y'

                     from   xla_mapping_set_flavors xmsf, 

                            fnd_id_flex_structures fifs

                     where  xmsf.stripe_id=fifs.id_flex_num

                     and    fifs.id_flex_code='GL#'

                     and    fifs.id_flex_structure_code='xxxxx' 

                     and    xmsf.mapping_set_code=xmsv.mapping_set_code

                     and    xmsf.mapping_set_flavor_id=xmsv.mapping_set_flavor_id)

              and    sysdate between ftvvl.effective_start_date and ftvvl.effective_end_date

              and    sysdate between xmsv.effective_start_date and xmsv.effective_end_date

              /* --- Prompts --- */

              and   (cf.dep0_pk1_value in (:p_lob_profit_centre) or coalesce(:p_lob_profit_centre,null) is null)

              and   (cf.dep27_pk1_value in (:p_lob_sub_business_group) or coalesce(:p_lob_sub_business_group,null) is null)

              and   (cf.dep28_pk1_value in (:p_lob_business_group) or coalesce(:p_lob_business_group,null) is null)

              and   (cf.dep29_pk1_value in (:p_lob_service_bundle) or coalesce(:p_lob_service_bundle,null) is null)

              and   (cf.dep30_pk1_value in (:p_lob_division) or coalesce(:p_lob_division,null) is null)

              /* --- End Prompts --- */

              and    cf.tree_code='XXXXX'

              and    xmsv.mapping_set_code ='WDAP_LOB_POO'

              union all 

              select 'Y' 

              from   dual 

              where  coalesce(:p_lob_profit_centre,null) is null

              and    coalesce(:p_lob_sub_business_group,null) is null

              and    coalesce(:p_lob_business_group,null) is null

              and    coalesce(:p_lob_service_bundle,null) is null

              and    coalesce(:p_lob_division,null) is null)

       /* --- End Prompts --- */ 

       and    1=1

GROUP BY fnbuv.bu_id, 

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              aia.invoice_id,              

              fnbuv.bu_name,

              xlp.name,

              ppavl.name, 

              ppavl.segment1)

SELECT ar.carrying_out_organization_id,

       ar.bu_name,  

       ar.le_name,        

ar.segment1, 

       ar.name project_name, 

       nvl(ar.cash_in,0) cash_in,

nvl(ar.billing_amt,0) billing_amt,

nvl(ap.cash_out,0) cash_out,

nvl(ap.liability_amt,0) liability_amt,

(nvl(ar.cash_in,0)-nvl(ap.cash_out,0)) cash_flow,

(nvl(ar.billing_amt,0)-nvl(ar.cash_in,0)) receivable_amt,

(nvl(ap.liability_amt,0)-nvl(ap.cash_out,0)) payable_amt

FROM   receivables ar, 

       payables ap 

WHERE  ar.bu_id=ap.bu_id(+)

AND    ar.legal_entity_id=ap.legal_entity_id(+)

AND    ar.project_id=ap.project_id (+)

order by ar.segment1,

       ar.bu_name, 

       ar.le_name

Query to get Invoice Line level Data

 select invoice_num,

       INVOICE_DATE,

   supplier_name,

   supplier_number,

   business_unit,

   inv_currency,

   country_code,

   INVOICE_AMOUNT,

   round(conversion_rate_gbp*INVOICE_AMOUNT,2) as inv_amt_gbp,

   AMOUNT_PAID ,

   round(conversion_rate_gbp*AMOUNT_PAID,2) inv_amt_paid_gbp ,

   inv_pay_status,

   PAYMENT_DATE,

   LINE_NUMBER,

   line_type,

   line_description,

   nominal,

   nominal_desc,

   bu_code,

   bu_desc,

   line_amount ,

   tax_amount,

   invoice_key,

   Inv_posted_date,

   PO_NUMBER,

   gl_code_combination,

   SUPPLIER_SITE,

   round(line_amount*conversion_rate_gbp,2) line_amt_gbp,

   round(tax_amount*conversion_rate_gbp,2) tax_amt_gbp

FROM (  

select distinct aia.invoice_num, --

       aia.INVOICE_AMOUNT,

   aia.invoice_id invoice_key,

   TO_CHAR(ail.ACCOUNTING_DATE ,'DD-MON-YY','nls_date_language=american')as Inv_posted_date,

   (SELECT pha.SEGMENT1 

    FROM po_headers_all pha

WHERE 1=1

and pha.vendor_id = supp.vendor_id

and pha.po_header_id = aia.po_header_id

)PO_NUMBER,

(gcc.SEGMENT1||'.'||

         gcc.SEGMENT2||'.'||

         gcc.SEGMENT3||'.'||

         gcc.SEGMENT4||'.'||

         gcc.SEGMENT5||'.'||

         gcc.SEGMENT6||'.'||

         gcc.SEGMENT7||'.'||

         gcc.SEGMENT8||'.'||

         gcc.SEGMENT9||'.'||

         gcc.SEGMENT10 ) gl_code_combination,

(SELECT PSSAM.VENDOR_SITE_CODE

          FROM POZ_SUPPLIER_SITES_ALL_M PSSAM

          WHERE PSSAM.vendor_id = supp.vendor_id AND ROWNUM < 2) AS SUPPLIER_SITE,

   aia.SOURCE,

   aia.AMOUNT_PAID,

   aia.INVOICE_CURRENCY_CODE inv_currency, --

   aia.INVOICE_TYPE_LOOKUP_CODE,

   to_char(aia.INVOICE_DATE, 'dd-Mon-yy') INVOICE_DATE, -- 

   aia.TAXATION_COUNTRY country,

   ail.LINE_NUMBER,

   ail.LINE_TYPE_LOOKUP_CODE,

   (select DESCRIPTION

    from fnd_lookup_values

where 1=1

and lookup_type = 'INVOICE LINE TYPE'

and LOOKUP_CODE = ail.LINE_TYPE_LOOKUP_CODE

and ENABLED_FLAG = 'Y'

   ) line_type,

   ail.DESCRIPTION as line_description,

   ail.LINE_SOURCE,

   ail.ITEM_DESCRIPTION,

   aida.AMOUNT as line_amount,

   (SELECT TAX_AMT from zx_lines zxl WHERE zxl.TRX_ID = ail.invoice_id AND zxl.TRX_LINE_NUMBER = ail.LINE_NUMBER AND zxl.entity_code='AP_INVOICES' AND rownum<2) as tax_amount,

   supp.vendor_name supplier_name, --

   supp.segment1 supplier_number, --

   hao.NAME business_unit, -- 

   att.NAME payment_terms,

   case 

when aia.INVOICE_CURRENCY_CODE = 'GBP'

  THEN 1

else 

  TRUNC((select conversion_rate

   from gl_daily_rates

   where 1=1

   and from_currency = aia.INVOICE_CURRENCY_CODE

   and to_currency = 'GBP'

   and trunc(conversion_date) = (SELECT MAX(conversion_date) 

                                 from gl_daily_rates 

where 1=1

and from_currency = aia.INVOICE_CURRENCY_CODE

and conversion_type = 'Spot'

and to_currency = 'GBP')

   and conversion_type = 'Spot'

   ),2)

end conversion_rate_gbp ,

        CASE 

      WHEN aia.PAYMENT_STATUS_FLAG = 'N'

         THEN 'Not Paid'

         WHEN aia.PAYMENT_STATUS_FLAG = 'P'  

THEN 'Partially Paid'

         ELSE 'Fully Paid'  

        END inv_pay_status,

to_char(ipa.PAYMENT_DATE, 'dd-Mon-yy') PAYMENT_DATE ,

gcc.segment1,

gcc.segment2 bu_code,

(SELECT GL_FLEXFIELDS_PKG.get_description_sql(GC.CHART_OF_ACCOUNTS_ID,2,

                                                 GC.segment2)   

                FROM GL_CODE_COMBINATIONS GC

                WHERE gc.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID ) bu_desc,

gcc.segment3,

gcc.segment4 nominal,

(SELECT GL_FLEXFIELDS_PKG.get_description_sql(GC.CHART_OF_ACCOUNTS_ID,4,

                                                 GC.segment4)   DESCRIPTION

                FROM GL_CODE_COMBINATIONS GC

                WHERE gc.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID ) nominal_desc,

gcc.segment5,

gcc.segment6,

gcc.segment7,

gcc.segment8,

gcc.segment9 country_code,

gcc.segment10

        /*inv_pay.amount inv_amt_paid,

        inv_pay.payment_num,

        inv_pay.period_name,

        inv_pay.accounting_date,

        inv_pay.posted_flag,

        inv_pay.creation_date */

from AP_INVOICES_ALL aia,

     AP_INVOICE_LINES_ALL ail,

zx_lines zxl,

AP_INVOICE_DISTRIBUTIONS_ALL aid,

POZ_SUPPLIERS_V supp,

AP_TERMS_TL att,

HR_ALL_ORGANIZATION_UNITS hao,

AP_INVOICE_PAYMENTS_ALL inv_pay,

ap_checks_all aca,

iby_payments_all ipa,

gl_code_combinations gcc,

AP_INVOICE_DISTRIBUTIONS_ALL aida

where 1=1

and aia.party_id = supp.party_id

and aia.org_id = hao.ORGANIZATION_ID

and aia.TERMS_ID = att.TERM_ID

and aia.invoice_id = ail.invoice_id

and aia.invoice_id = aida.invoice_id

and zxl.TRX_ID = ail.invoice_id 

and zxl.TRX_LINE_NUMBER = ail.LINE_NUMBER 

and zxl.entity_code='AP_INVOICES'

and ail.LINE_TYPE_LOOKUP_CODE = 'ITEM'

and ail.invoice_id = aid.invoice_id

and ail.line_number = aid.INVOICE_LINE_NUMBER

and aid.DIST_CODE_COMBINATION_ID = gcc.code_combination_id

and aida.DIST_CODE_COMBINATION_ID = gcc.code_combination_id

and aia.invoice_id = aid.invoice_id

and att.LANGUAGE = 'US'

and aia.PAYMENT_STATUS_FLAG != 'N'

--and aia.PAYMENT_STATUS_FLAG = 'P'  --

and inv_pay.invoice_id = aia.invoice_id

and aida.FINAL_MATCH_FLAG = 'N'

and aca.check_id = inv_pay.check_id

and aca.STATUS_LOOKUP_CODE = 'CLEARED'  --  temp to be tested

and ipa.payment_id = aca.payment_id

and ail.ACCOUNTING_DATE  between :p_start_date and :p_end_date

----and gcc.segment2=1340

--and gcc.segment2=1274

--and aida.AMOUNT = 120

--and aia.invoice_num='50462'

)  

order by invoice_num, LINE_NUMBER

Query for Supplier Payment Comparison of that Period(Vendor Spend Query)

 SELECT POS.SEGMENT1

       , HP.PARTY_NAME

  ,cur_period.PAYMENT_AMOUNT  cur_pay_amt

  ,prev_period.PAYMENT_AMOUNT  prev_pay_amt

  ,ROUND((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100),1)||'%' increase_decrease

FROM

        (SELECT    AIA.VENDOR_ID              VENDOR_ID

          ,SUM(NVL(aipa.AMOUNT,0))    PAYMENT_AMOUNT

        FROM  AP_INVOICES_ALL AIA,

         ap_invoice_payments_all       aipa,

         gl_periods                    gl

        WHERE 1=1

        AND   AIA.INVOICE_ID                = AIPA.INVOICE_ID

        AND   aipa.period_name              = gl.period_name

        AND gl.period_set_name ='XX Period Name' 

AND gl.PERIOD_YEAR = :P_Fiscal_Year

AND gl.PERIOD_NAME = :P_Period

--AND   SYSDATE BETWEEN gl.start_date and gl.end_date

GROUP BY AIA.VENDOR_ID

        ) cur_period,

        (SELECT     AIA.VENDOR_ID     VENDOR_ID  

          ,SUM(NVL(aipa.AMOUNT,0))    PAYMENT_AMOUNT

        FROM  AP_INVOICES_ALL AIA,

         ap_invoice_payments_all       aipa,

         gl_periods                    gl

        WHERE 1=1

        AND   AIA.INVOICE_ID                = AIPA.INVOICE_ID

        AND   aipa.period_name              = gl.period_name

        AND gl.period_set_name ='XX Period Name'

AND gl.period_name = (

                      SELECT  gl.period_name

                              FROM  

                               gl_periods      gl,

                       gl_periods      gl2

                              WHERE 1=1

                      AND gl.period_set_name ='XX Period Name'

                      AND (

       ( 

           gl2.period_name = :P_Comp_Period

  AND gl2.PERIOD_YEAR = :P_Comp_Fis_Year

  AND trunc(gl2.start_date) between gl.start_date and gl.end_date

    )

  OR

  (

  ( trunc(gl2.start_date-1) between gl.start_date and gl.end_date ) 

   AND gl2.period_name = :P_Period

   AND gl2.PERIOD_YEAR = :P_Fiscal_Year

   AND :P_Comp_Period IS NULL

   AND :P_Comp_Fis_Year IS NULL

  )

  )

                      AND gl2.period_set_name ='XX Period Name'

  )   

        GROUP BY AIA.VENDOR_ID

        ) prev_period

        ,POZ_SUPPLIERS POS

        ,HZ_PARTIES     HP

WHERE POS.VENDOR_ID = cur_period.vendor_id(+)

AND   POS.VENDOR_ID  = Prev_period.vendor_id(+)

AND   POS.party_id  = hp.party_id

AND ((COALESCE(NULL, :P_Supplier) IS NULL)

       OR (HP.PARTY_NAME IN (:P_Supplier)))

and (NVL(cur_period.PAYMENT_AMOUNT ,0)<>0 OR NVl(prev_period.PAYMENT_AMOUNT ,0)<>0)

AND abs((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100)) > = NVL(:P_Change,abs((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100)))