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

Drill Down Query (AP ,AR ,GL ,Subscription ,Contracts ,Assets ,Depreciation)

 WITH GL_CMN_TBLS AS

(SELECT GL.name AS Ledger_name

,GJS.JE_SOURCE_NAME    AS JE_SOURCE_NAME

,GJH.JE_BATCH_ID      AS BATCH_ID

,GJB.NAME            AS BATCH_NAME

,GJH.PERIOD_NAME    AS PERIOD_NAME

,GCC.SEGMENT1                AS Code_comb_segment1

,GCC.SEGMENT2  AS Code_comb_segment2

    ,GCC.SEGMENT3    AS Code_comb_segment3

    ,GCC.SEGMENT4    AS Code_comb_segment4

,GCC.SEGMENT5    AS Code_comb_segment5

,GCC.SEGMENT6    AS Code_comb_segment6

,GCC.SEGMENT7    AS Code_comb_segment7

,GCC.SEGMENT8      AS Code_comb_segment8

    ,GJB.POSTED_DATE AS gl_posted_date

    ,DECODE(GJB.STATUS,'P',GJB.LAST_UPDATED_BY,NULL)        "POSTED_BY"   

    ,GL.DESCRIPTION 

    ,GJL.CURRENCY_CODE AS JOURNAL_CURRENCY

    ,GJL.ENTERED_DR AS ENTERED_DEBIT   

    ,GJL.ENTERED_CR AS ENTERED_CREDIT                                       

    ,GJL.ACCOUNTED_DR AS ACCOUNT_DEBIT                                       

    ,GJL.ACCOUNTED_CR   AS ACCOUNT_CREDIT                                    

    ,GJH.CURRENCY_CONVERSION_RATE                           

    ,GJH.CURRENCY_CONVERSION_DATE                           

    ,xal.entered_dr AS XLA_ENTERED_DEBIT                                         

    ,xal.entered_cr                     AS XLA_ENTERED_CREDIT                    

    ,xal.accounted_dr                   AS XLA_ACCOUNTED_ENTERED_DEBIT                    

    ,xal.accounted_cr                   AS XLA_ACCOUNTED_ENTERED_CREDIT                   

    ,xah.event_id                                           

    ,xev.EVENT_TYPE_CODE                                    

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,NULL                                                   "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,NULL                                                   "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

--,GJS.JE_SOURCE_NAME

,xev.entity_id

,gjh.je_category

,gjh.LEGAL_ENTITY_ID

FROM GL_JE_HEADERS GJH,

     GL_LEDGERS GL,

     GL_JE_BATCHES GJB,

     GL_JE_LINES GJL,

     GL_PERIODS GP,

     GL_CODE_COMBINATIONS GCC,

     GL_JE_SOURCES_B GJS,

     xla_events xev,

     xla_ae_headers xah,

     xla_ae_lines xal,

     gl_import_references gir

WHERE 1=1

  AND GJH.JE_HEADER_ID            = GJL.JE_HEADER_ID

  AND GJH.LEDGER_ID               = GL.LEDGER_ID

  AND GJH.PERIOD_NAME             = GP.PERIOD_NAME

  AND GJB.JE_BATCH_ID             = GJH.JE_BATCH_ID

  AND GJL.CODE_COMBINATION_ID     = GCC.CODE_COMBINATION_ID

  AND GP.PERIOD_SET_NAME          = 'xx_set'

  --AND GJS.JE_SOURCE_NAME         IN ('Assets','Manual','Revaluation','Cross-Charging','Payables','Payroll','Project Accounting','Receivables')

  AND gjh.status                 ='P'

  AND gjh.actual_flag            ='A'

  AND GJH.JE_SOURCE               = GJS.JE_SOURCE_NAME

  AND xah.event_id               = xev.event_id

  AND xah.ae_header_id           = xal.ae_header_id

  AND xah.gl_transfer_status_code= 'Y'

  AND xal.gl_sl_link_id          =gir.gl_sl_link_id

  AND gir.gl_sl_link_table       = xal.gl_sl_link_table

  AND gjh.je_header_id           =gir.je_header_id

  AND gjl.je_header_id           =gir.je_header_id

  AND gir.je_line_num            =gjl.je_line_num  

  AND gjh.posted_date BETWEEN NVL(:P_START_DATE,gjh.posted_date) AND NVL(:P_END_DATE,gjh.posted_date)

  --AND GL.name                    = NVL(:P_LEDGER,GL.name)

  AND (GL.name IN (:P_LEDGER) OR COALESCE (:P_LEDGER,NULL) IS NULL)

  --AND GJH.LEGAL_ENTITY_ID        =NVL(:P_LEGAL_ENTITY_ID,GJH.LEGAL_ENTITY_ID)

  --AND (GJH.PERIOD_NAME IN (:P_PERIOD_NAME) OR COALESCE (:P_PERIOD_NAME,NULL) IS NULL)

  --AND GJH.PERIOD_NAME             =NVL(:P_PERIOD_NAME,GJH.PERIOD_NAME)

  

  )

----GL Query

  SELECT DISTINCT

gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                                    

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,NULL                                                   "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,NULL                                                   "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

    FROM GL_CMN_TBLS gct

WHERE gct.JE_SOURCE_NAME NOT IN ('Payables','Receivables','Cash Management','Assets','Project Accounting')

  ----END GL query

  UNION ALL

---AP Invoice query ---------  

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE

    ,NULL                                                   "TRADING_PARTNER"

    ,xte.source_id_int_1                                    "SOURCE_ID_INT_1"

    ,aps.SEGMENT1                                            "SUPPLIER_NUM"

    ,poss.vendor_site_code                                  "SUPPLIER_SITE"

    ,aia.INVOICE_DATE                                       "INVOICE_DATE"

    ,aia.INVOICE_NUM                                        "INVOICE_NUM"

    ,aia.INVOICE_CURRENCY_CODE                              "INVOICE_CURRENCY_CODE"

    ,aia.INVOICE_AMOUNT                                     "INVOICE_AMOUNT"

    ,aia.TOTAL_TAX_AMOUNT                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,NULL                                                   "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,(select psav.CITY 

      from POZ_SUPPLIER_ADDRESS_V psav

      where aps.vendor_id = psav.vendor_id

      AND poss.vendor_id  = psav.vendor_id  

  and rownum<2)                                         "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,ap_invoices_all aia

,poz_suppliers_v aps

,poz_supplier_sites_all_m poss

,xla_transaction_entities xte

WHERE 1=1

  AND aia.invoice_id              = xte.source_id_int_1

  AND aia.vendor_id               = aps.vendor_id(+)

  AND aia.vendor_site_id          = poss.vendor_site_id(+)

  AND aps.vendor_id               = poss.vendor_id(+)

  AND gct.entity_id               = xte.entity_id

  AND xte.entity_code   = 'AP_INVOICES'

  AND gct.JE_SOURCE_NAME         IN ('Payables')

  AND gct.je_category   = 'Purchase Invoices'

--- END AP Invoice --

UNION ALL

---AP Payments qry -------

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                      

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,to_char(aca.CHECK_NUMBER)                                     "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,(select DISTINCT psav.city 

     from POZ_SUPPLIER_ADDRESS_V psav 

     where aca.vendor_id = psav.vendor_id

and ROWNUM<2                        )                  "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

    ,ap_checks_all aca

    ,ap_invoice_payments_all aipa

    ,ap_payment_schedules_all aps

,xla_transaction_entities xte

WHERE 1=1

  AND aca.check_id              = xte.source_id_int_1

  and aipa.check_id             = aca.check_id(+)

  and aps.invoice_id  = aipa.invoice_id

  AND gct.entity_id             = xte.entity_id

  AND xte.entity_code = 'AP_PAYMENTS'

  AND gct.JE_SOURCE_NAME         IN ('Payables')

  AND gct.je_category = 'Payments'

---End AP Payments qry ----

UNION ALL

-- AR Invoice Query ----

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                              

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,rcta.CUSTOMER_TRX_ID                                   "CUSTOMER_TRX_ID"

    ,rcta.TRX_NUMBER                                        "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,NULL                                                   "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,ra_customer_trx_all rcta

,xla_transaction_entities xte

WHERE 1=1

  AND rcta.customer_trx_id        = xte.source_id_int_1

  AND gct.entity_id               = xte.entity_id

  AND xte.entity_code   = 'TRANSACTIONS'

  AND gct.JE_SOURCE_NAME         IN ('Receivables')

  AND gct.je_category   = 'Receiving'

--- End AR invoice --

UNION ALL

-----Assets Depreciation ----

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE               

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,fas.asset_id                                           "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,NULL                                                   "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,(FL.segment1||'-'||FL.segment2||'-'||FL.segment3||'-'||FL.segment4||'-'||FL.segment5||'-'||FL.segment6)          "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,fa_additions_b fas

,fa_additions_tl fasl

,xla_transaction_entities xte

,fa_distribution_history fdh

,fa_locations fl

WHERE 1=1

  AND fdh.location_id = fl.location_id 

  AND fas.asset_id = fdh.asset_id

  AND fas.asset_id        = xte.source_id_int_1

  AND fasl.asset_id         = fas.asset_id

  AND fasl.language         = USERENV('LANG')

  AND gct.entity_id         = xte.entity_id

  AND xte.entity_code     = 'DEPRECIATION'

  AND gct.JE_SOURCE_NAME    IN ('Assets')

  AND gct.je_category     = 'Depreciation'

----END --Assets Depreciation------

UNION ALL

--Assets Addition---

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                        

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,fas.asset_id                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,NULL                                                   "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,(FL.segment1||'-'||FL.segment2||'-'||FL.segment3||'-'||FL.segment4||'-'||FL.segment5||'-'||FL.segment6)       "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,fa_additions_b fas

,fa_additions_tl fasl

,fa_transaction_headers fth

,xla_transaction_entities xte

,fa_distribution_history fdh

,fa_locations fl

WHERE 1=1

  AND fdh.location_id = fl.location_id 

  AND fas.asset_id = fdh.asset_id 

  AND fth.transaction_header_id        = xte.source_id_int_1

  AND fasl.asset_id         = fas.asset_id

  AND fas.asset_id          = fth.asset_id

  AND fasl.language         = USERENV('LANG')

  AND gct.entity_id         = xte.entity_id

  --AND xte.entity_code     = 'DEPRECIATION'

  AND gct.JE_SOURCE_NAME    IN ('Assets')

  AND gct.je_category     IN ('Transfer', 'Retirement', 'Adjustment', 'CIP Adjustment', 'Addition', 'CIP Addition')

--END Assets Addition ---

--Project Accounting query-----

UNION ALL

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                              

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,NULL                                                   "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,oha.ATTRIBUTE3                                         "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM PJB_REV_DISTRIBUTIONS PJR

     ,okc_k_headers_all_b OHA

,xla_transaction_entities xte

     ,GL_CMN_TBLS gct

WHERE 1=1

AND PJR.CONTRACT_ID = OHA.id

AND xte.source_id_int_1 = pjr.REV_DISTRIBUTION_ID

AND xte.ENTITY_CODE = 'REVENUE'

AND xte.entity_id = gct.entity_id

--AND OHA.CONTRACT_NUMBER = 'Suite 10'

AND gct.JE_SOURCE_NAME = 'Project Accounting'

AND gct.je_category     IN('External Revenue')

--AND oha.UNDER_AMEND_VERSION_FLAG ='Y'

AND OHA.VERSION_TYPE = 'C'

--and pjr.REV_DISTRIBUTION_ID = 100000058663131

UNION ALL

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                              

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,rcta.CUSTOMER_TRX_ID                                   "CUSTOMER_TRX_ID"

    ,rcta.TRX_NUMBER                                        "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,okh.attribute3                                         "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,ra_customer_trx_all rcta

,xla_transaction_entities xte

,okc_k_headers_all_b okh

WHERE 1=1

  AND rcta.customer_trx_id        = xte.source_id_int_1

  AND gct.entity_id               = xte.entity_id

  AND xte.entity_code   = 'TRANSACTIONS'

  AND gct.JE_SOURCE_NAME         IN ('Receivables')

  AND gct.je_category   = 'Sales Invoices'

  AND rcta.INTERFACE_HEADER_ATTRIBUTE1 = okh.CONTRACT_NUMBER

  AND okh.VERSION_TYPE = 'C'

  

  UNION ALL

  

  SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                              

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,rcta.CUSTOMER_TRX_ID                                   "CUSTOMER_TRX_ID"

    ,os.SUBSCRIPTION_NUMBER                                 "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,os.ATTRIBUTE3                                          "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,ra_customer_trx_all rcta

,ra_customer_trx_lines_all rctla

,OSS_SUBSCRIPTIONS  os

,xla_transaction_entities xte

WHERE 1=1

  AND rcta.customer_trx_id         = xte.source_id_int_1

  AND gct.entity_id                = xte.entity_id

  AND rcta.CUSTOMER_TRX_ID         = rctla.CUSTOMER_TRX_ID

  AND rcta.INTERFACE_HEADER_ATTRIBUTE1 = os.SUBSCRIPTION_NUMBER

  AND xte.entity_code    = 'TRANSACTIONS'

  AND gct.JE_SOURCE_NAME          IN ('Receivables')

  AND rctla.LINE_TYPE              = 'LINE'

  and rcta.INTERFACE_HEADER_CONTEXT='Subscriptions Context'

  

  UNION ALL

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                              

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,rctla.CUSTOMER_TRX_ID                                  "CUSTOMER_TRX_ID"

    ,dha.ORDER_NUMBER                                       "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,dleb.ATTRIBUTE_CHAR1                                   "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,ra_customer_trx_all rcta

,ra_customer_trx_lines_all rctla

,doo_headers_all dha

,DOO_LINES_EFF_B dleb

,xla_transaction_entities xte

WHERE 1=1

  AND rcta.customer_trx_id         = xte.source_id_int_1

  AND gct.entity_id                = xte.entity_id

  AND rcta.CUSTOMER_TRX_ID         = rctla.CUSTOMER_TRX_ID

  AND dha.ORDER_NUMBER             = rctla.INTERFACE_LINE_ATTRIBUTE1

  AND dha.HEADER_ID                = dleb.HEADER_ID

  AND xte.entity_code    = 'TRANSACTIONS'

  AND gct.JE_SOURCE_NAME          IN ('Receivables')

  AND rctla.LINE_TYPE              = 'LINE'

  and rcta.INTERFACE_HEADER_CONTEXT='DOO' WITH GL_CMN_TBLS AS

(SELECT GL.name AS Ledger_name

,GJS.JE_SOURCE_NAME    AS JE_SOURCE_NAME

,GJH.JE_BATCH_ID      AS BATCH_ID

,GJB.NAME            AS BATCH_NAME

,GJH.PERIOD_NAME    AS PERIOD_NAME

,GCC.SEGMENT1                AS Code_comb_segment1

,GCC.SEGMENT2  AS Code_comb_segment2

    ,GCC.SEGMENT3    AS Code_comb_segment3

    ,GCC.SEGMENT4    AS Code_comb_segment4

,GCC.SEGMENT5    AS Code_comb_segment5

,GCC.SEGMENT6    AS Code_comb_segment6

,GCC.SEGMENT7    AS Code_comb_segment7

,GCC.SEGMENT8      AS Code_comb_segment8

    ,GJB.POSTED_DATE AS gl_posted_date

    ,DECODE(GJB.STATUS,'P',GJB.LAST_UPDATED_BY,NULL)        "POSTED_BY"   

    ,GL.DESCRIPTION 

    ,GJL.CURRENCY_CODE AS JOURNAL_CURRENCY

    ,GJL.ENTERED_DR AS ENTERED_DEBIT   

    ,GJL.ENTERED_CR AS ENTERED_CREDIT                                       

    ,GJL.ACCOUNTED_DR AS ACCOUNT_DEBIT                                       

    ,GJL.ACCOUNTED_CR   AS ACCOUNT_CREDIT                                    

    ,GJH.CURRENCY_CONVERSION_RATE                           

    ,GJH.CURRENCY_CONVERSION_DATE                           

    ,xal.entered_dr AS XLA_ENTERED_DEBIT                                         

    ,xal.entered_cr                     AS XLA_ENTERED_CREDIT                    

    ,xal.accounted_dr                   AS XLA_ACCOUNTED_ENTERED_DEBIT                    

    ,xal.accounted_cr                   AS XLA_ACCOUNTED_ENTERED_CREDIT                   

    ,xah.event_id                                           

    ,xev.EVENT_TYPE_CODE                                    

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,NULL                                                   "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,NULL                                                   "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

--,GJS.JE_SOURCE_NAME

,xev.entity_id

,gjh.je_category

,gjh.LEGAL_ENTITY_ID

FROM GL_JE_HEADERS GJH,

     GL_LEDGERS GL,

     GL_JE_BATCHES GJB,

     GL_JE_LINES GJL,

     GL_PERIODS GP,

     GL_CODE_COMBINATIONS GCC,

     GL_JE_SOURCES_B GJS,

     xla_events xev,

     xla_ae_headers xah,

     xla_ae_lines xal,

     gl_import_references gir

WHERE 1=1

  AND GJH.JE_HEADER_ID            = GJL.JE_HEADER_ID

  AND GJH.LEDGER_ID               = GL.LEDGER_ID

  AND GJH.PERIOD_NAME             = GP.PERIOD_NAME

  AND GJB.JE_BATCH_ID             = GJH.JE_BATCH_ID

  AND GJL.CODE_COMBINATION_ID     = GCC.CODE_COMBINATION_ID

  AND GP.PERIOD_SET_NAME          = 'xx_set'

  --AND GJS.JE_SOURCE_NAME         IN ('Assets','Manual','Revaluation','Cross-Charging','Payables','Payroll','Project Accounting','Receivables')

  AND gjh.status                 ='P'

  AND gjh.actual_flag            ='A'

  AND GJH.JE_SOURCE               = GJS.JE_SOURCE_NAME

  AND xah.event_id               = xev.event_id

  AND xah.ae_header_id           = xal.ae_header_id

  AND xah.gl_transfer_status_code= 'Y'

  AND xal.gl_sl_link_id          =gir.gl_sl_link_id

  AND gir.gl_sl_link_table       = xal.gl_sl_link_table

  AND gjh.je_header_id           =gir.je_header_id

  AND gjl.je_header_id           =gir.je_header_id

  AND gir.je_line_num            =gjl.je_line_num  

  AND gjh.posted_date BETWEEN NVL(:P_START_DATE,gjh.posted_date) AND NVL(:P_END_DATE,gjh.posted_date)

  --AND GL.name                    = NVL(:P_LEDGER,GL.name)

  AND (GL.name IN (:P_LEDGER) OR COALESCE (:P_LEDGER,NULL) IS NULL)

  --AND GJH.LEGAL_ENTITY_ID        =NVL(:P_LEGAL_ENTITY_ID,GJH.LEGAL_ENTITY_ID)

  --AND (GJH.PERIOD_NAME IN (:P_PERIOD_NAME) OR COALESCE (:P_PERIOD_NAME,NULL) IS NULL)

  --AND GJH.PERIOD_NAME             =NVL(:P_PERIOD_NAME,GJH.PERIOD_NAME)

  

  )

----GL Query

  SELECT DISTINCT

gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                                    

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,NULL                                                   "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,NULL                                                   "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

    FROM GL_CMN_TBLS gct

WHERE gct.JE_SOURCE_NAME NOT IN ('Payables','Receivables','Cash Management','Assets','Project Accounting')

  ----END GL query

  UNION ALL

---AP Invoice query ---------  

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE

    ,NULL                                                   "TRADING_PARTNER"

    ,xte.source_id_int_1                                    "SOURCE_ID_INT_1"

    ,aps.SEGMENT1                                            "SUPPLIER_NUM"

    ,poss.vendor_site_code                                  "SUPPLIER_SITE"

    ,aia.INVOICE_DATE                                       "INVOICE_DATE"

    ,aia.INVOICE_NUM                                        "INVOICE_NUM"

    ,aia.INVOICE_CURRENCY_CODE                              "INVOICE_CURRENCY_CODE"

    ,aia.INVOICE_AMOUNT                                     "INVOICE_AMOUNT"

    ,aia.TOTAL_TAX_AMOUNT                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,NULL                                                   "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,(select psav.CITY 

      from POZ_SUPPLIER_ADDRESS_V psav

      where aps.vendor_id = psav.vendor_id

      AND poss.vendor_id  = psav.vendor_id  

  and rownum<2)                                         "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,ap_invoices_all aia

,poz_suppliers_v aps

,poz_supplier_sites_all_m poss

,xla_transaction_entities xte

WHERE 1=1

  AND aia.invoice_id              = xte.source_id_int_1

  AND aia.vendor_id               = aps.vendor_id(+)

  AND aia.vendor_site_id          = poss.vendor_site_id(+)

  AND aps.vendor_id               = poss.vendor_id(+)

  AND gct.entity_id               = xte.entity_id

  AND xte.entity_code   = 'AP_INVOICES'

  AND gct.JE_SOURCE_NAME         IN ('Payables')

  AND gct.je_category   = 'Purchase Invoices'

--- END AP Invoice --

UNION ALL

---AP Payments qry -------

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                      

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,to_char(aca.CHECK_NUMBER)                                     "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,(select DISTINCT psav.city 

     from POZ_SUPPLIER_ADDRESS_V psav 

     where aca.vendor_id = psav.vendor_id

and ROWNUM<2                        )                  "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

    ,ap_checks_all aca

    ,ap_invoice_payments_all aipa

    ,ap_payment_schedules_all aps

,xla_transaction_entities xte

WHERE 1=1

  AND aca.check_id              = xte.source_id_int_1

  and aipa.check_id             = aca.check_id(+)

  and aps.invoice_id  = aipa.invoice_id

  AND gct.entity_id             = xte.entity_id

  AND xte.entity_code = 'AP_PAYMENTS'

  AND gct.JE_SOURCE_NAME         IN ('Payables')

  AND gct.je_category = 'Payments'

---End AP Payments qry ----

UNION ALL

-- AR Invoice Query ----

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                              

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,rcta.CUSTOMER_TRX_ID                                   "CUSTOMER_TRX_ID"

    ,rcta.TRX_NUMBER                                        "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,NULL                                                   "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,ra_customer_trx_all rcta

,xla_transaction_entities xte

WHERE 1=1

  AND rcta.customer_trx_id        = xte.source_id_int_1

  AND gct.entity_id               = xte.entity_id

  AND xte.entity_code   = 'TRANSACTIONS'

  AND gct.JE_SOURCE_NAME         IN ('Receivables')

  AND gct.je_category   = 'Receiving'

--- End AR invoice --

UNION ALL

-----Assets Depreciation ----

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE               

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,fas.asset_id                                           "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,NULL                                                   "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,(FL.segment1||'-'||FL.segment2||'-'||FL.segment3||'-'||FL.segment4||'-'||FL.segment5||'-'||FL.segment6)          "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,fa_additions_b fas

,fa_additions_tl fasl

,xla_transaction_entities xte

,fa_distribution_history fdh

,fa_locations fl

WHERE 1=1

  AND fdh.location_id = fl.location_id 

  AND fas.asset_id = fdh.asset_id

  AND fas.asset_id        = xte.source_id_int_1

  AND fasl.asset_id         = fas.asset_id

  AND fasl.language         = USERENV('LANG')

  AND gct.entity_id         = xte.entity_id

  AND xte.entity_code     = 'DEPRECIATION'

  AND gct.JE_SOURCE_NAME    IN ('Assets')

  AND gct.je_category     = 'Depreciation'

----END --Assets Depreciation------

UNION ALL

--Assets Addition---

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                        

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,fas.asset_id                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,NULL                                                   "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,(FL.segment1||'-'||FL.segment2||'-'||FL.segment3||'-'||FL.segment4||'-'||FL.segment5||'-'||FL.segment6)       "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,fa_additions_b fas

,fa_additions_tl fasl

,fa_transaction_headers fth

,xla_transaction_entities xte

,fa_distribution_history fdh

,fa_locations fl

WHERE 1=1

  AND fdh.location_id = fl.location_id 

  AND fas.asset_id = fdh.asset_id 

  AND fth.transaction_header_id        = xte.source_id_int_1

  AND fasl.asset_id         = fas.asset_id

  AND fas.asset_id          = fth.asset_id

  AND fasl.language         = USERENV('LANG')

  AND gct.entity_id         = xte.entity_id

  --AND xte.entity_code     = 'DEPRECIATION'

  AND gct.JE_SOURCE_NAME    IN ('Assets')

  AND gct.je_category     IN ('Transfer', 'Retirement', 'Adjustment', 'CIP Adjustment', 'Addition', 'CIP Addition')

--END Assets Addition ---

--Project Accounting query-----

UNION ALL

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                              

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,NULL                                                   "CUSTOMER_TRX_ID"

    ,NULL                                                   "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,oha.ATTRIBUTE3                                         "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM PJB_REV_DISTRIBUTIONS PJR

     ,okc_k_headers_all_b OHA

,xla_transaction_entities xte

     ,GL_CMN_TBLS gct

WHERE 1=1

AND PJR.CONTRACT_ID = OHA.id

AND xte.source_id_int_1 = pjr.REV_DISTRIBUTION_ID

AND xte.ENTITY_CODE = 'REVENUE'

AND xte.entity_id = gct.entity_id

--AND OHA.CONTRACT_NUMBER = 'Suite 10'

AND gct.JE_SOURCE_NAME = 'Project Accounting'

AND gct.je_category     IN('External Revenue')

--AND oha.UNDER_AMEND_VERSION_FLAG ='Y'

AND OHA.VERSION_TYPE = 'C'

--and pjr.REV_DISTRIBUTION_ID = 100000058663131

UNION ALL

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                              

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,rcta.CUSTOMER_TRX_ID                                   "CUSTOMER_TRX_ID"

    ,rcta.TRX_NUMBER                                        "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,okh.attribute3                                         "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,ra_customer_trx_all rcta

,xla_transaction_entities xte

,okc_k_headers_all_b okh

WHERE 1=1

  AND rcta.customer_trx_id        = xte.source_id_int_1

  AND gct.entity_id               = xte.entity_id

  AND xte.entity_code   = 'TRANSACTIONS'

  AND gct.JE_SOURCE_NAME         IN ('Receivables')

  AND gct.je_category   = 'Sales Invoices'

  AND rcta.INTERFACE_HEADER_ATTRIBUTE1 = okh.CONTRACT_NUMBER

  AND okh.VERSION_TYPE = 'C'

  

  UNION ALL

  

  SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                              

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,rcta.CUSTOMER_TRX_ID                                   "CUSTOMER_TRX_ID"

    ,os.SUBSCRIPTION_NUMBER                                 "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,os.ATTRIBUTE3                                          "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,ra_customer_trx_all rcta

,ra_customer_trx_lines_all rctla

,OSS_SUBSCRIPTIONS  os

,xla_transaction_entities xte

WHERE 1=1

  AND rcta.customer_trx_id         = xte.source_id_int_1

  AND gct.entity_id                = xte.entity_id

  AND rcta.CUSTOMER_TRX_ID         = rctla.CUSTOMER_TRX_ID

  AND rcta.INTERFACE_HEADER_ATTRIBUTE1 = os.SUBSCRIPTION_NUMBER

  AND xte.entity_code    = 'TRANSACTIONS'

  AND gct.JE_SOURCE_NAME          IN ('Receivables')

  AND rctla.LINE_TYPE              = 'LINE'

  and rcta.INTERFACE_HEADER_CONTEXT='Subscriptions Context'

  

  UNION ALL

SELECT DISTINCT gct.Ledger_name

,gct.JE_SOURCE_NAME

,gct.BATCH_ID

,gct.BATCH_NAME

,gct.PERIOD_NAME

,gct.Code_comb_segment1  

,gct.Code_comb_segment2    

,gct.Code_comb_segment3

,gct.Code_comb_segment4

    ,gct.Code_comb_segment5

    ,gct.Code_comb_segment6

    ,gct.Code_comb_segment7

    ,gct.Code_comb_segment8

,gct.gl_posted_date

    ,gct.POSTED_BY

    ,gct.DESCRIPTION 

    ,gct.JOURNAL_CURRENCY

    ,gct.ENTERED_DEBIT   

    ,gct.ENTERED_CREDIT                                       

    ,gct.ACCOUNT_DEBIT                                       

    ,gct.ACCOUNT_CREDIT                                                                       

    ,gct.CURRENCY_CONVERSION_RATE                           

    ,gct.CURRENCY_CONVERSION_DATE                           

    ,gct.XLA_ENTERED_DEBIT                                         

    ,gct.XLA_ENTERED_CREDIT                                         

    ,gct.XLA_ACCOUNTED_ENTERED_DEBIT                                       

    ,gct.XLA_ACCOUNTED_ENTERED_CREDIT                                       

    ,gct.event_id                                           

    ,gct.EVENT_TYPE_CODE                              

    ,NULL                                                   "TRADING_PARTNER"

    ,NULL                                                   "SOURCE_ID_INT_1"

    ,NULL                                                   "SUPPLIER_NUM"

    ,NULL                                                   "SUPPLIER_SITE"

    ,NULL                                                   "INVOICE_DATE"

    ,NULL                                                   "INVOICE_NUM"

    ,NULL                                                   "INVOICE_CURRENCY_CODE"

    ,NULL                                                   "INVOICE_AMOUNT"

    ,NULL                                                   "TOTAL_TAX_AMOUNT"

    ,NULL                                                   "DISTRIBUTIONS_LINE_TYPE"

    ,NULL                                                   "DISTRIBUTIONS_DESCRIPTION"

    ,NULL                                                   "VENDOR_ID"

    ,NULL                                                   "PARTY_NAME"

    ,NULL                                                   "ASSET_ID"

    ,NULL                                                   "PROJECT_NUMBER"

    ,rctla.CUSTOMER_TRX_ID                                  "CUSTOMER_TRX_ID"

    ,dha.ORDER_NUMBER                                       "TRX_NUMBER"

    ,NULL                                                   "AR_BATCH_NUMBER"

    ,NULL                                                   "AR_RECEIPT_NUMBER"

    ,NULL                                                   "AR_APPLIED_TRANS_NUMBER"

    ,NULL                                                   "RECEIVABLE_APPLICATION_ID"

    ,dleb.ATTRIBUTE_CHAR1                                   "LOCATION"

    ,NULL                                                   "LOCATION_SOURCE"

FROM GL_CMN_TBLS gct

,ra_customer_trx_all rcta

,ra_customer_trx_lines_all rctla

,doo_headers_all dha

,DOO_LINES_EFF_B dleb

,xla_transaction_entities xte

WHERE 1=1

  AND rcta.customer_trx_id         = xte.source_id_int_1

  AND gct.entity_id                = xte.entity_id

  AND rcta.CUSTOMER_TRX_ID         = rctla.CUSTOMER_TRX_ID

  AND dha.ORDER_NUMBER             = rctla.INTERFACE_LINE_ATTRIBUTE1

  AND dha.HEADER_ID                = dleb.HEADER_ID

  AND xte.entity_code    = 'TRANSACTIONS'

  AND gct.JE_SOURCE_NAME          IN ('Receivables')

  AND rctla.LINE_TYPE              = 'LINE'

  and rcta.INTERFACE_HEADER_CONTEXT='DOO'