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'
