ORACLE TECH CENTER: ERP cloud

Showing posts with label ERP cloud. Show all posts

Thursday, 25 May 2023

Get the column or Table information in oracle fusion erp

select * from all_tab_cols where upper(column_name) like 'OFFSET_TAX_FLAG'

SELECT * FROM all_tab_modifications;

<?choose:?><?when: P_INVOICENUM=’’?>All<?end when?><?otherwise:?><?P_INVOICENUM?><?end otherwise?><?end choose?>

<fo:basic-link external-destination="{INVOICE_URL}" color="blue" text-decoration="underline">
<?INVOICE_NUMBER?>
</fo:basic-link>

Print Parameters In Otbi:

[b]Legal Employer               : @{PV_LE}[/b][br/]
[b]Department                      : @{PV_DEPT_NAME}[/b][br/]
[b]Employee Number            : @{PV_EMP_NUMBER}[/b][br/]
[b]Start Date                          : @{PV_ST_DT}[/b][br/]
[b]End Date                            : @{PV_END_DT}[/b][br/]
[b]Leave Type                           : @{PV_ABS_TYPE}[/b][br/]
[b]Absence Status                        : @{PV_ABS_STA}[/b][br/]

Convert String into Arabic xml tag for RTF:

<?xdoxslt:toCheckNumber('AR-KW',SALARY_AMOUNT,0)?>

IF condition in RTF xml Tag:

1) <?if:(GRADE='8 A') or

(GRADE='8 B') or (GRADE='9 A') or (GRADE='9 B') or

(GRADE='10 A') or (GRADE='10 B') or (GRADE='11 A') or

(GRADE='11 B') or (GRADE='12')?>

2)<?xdofx:if (GRADE ='8 A' or GRADE ='8 B')

Then '2000'

Else If (GRADE ='9 A' or GRADE ='9 B')

Then '2500'

Else If (GRADE ='10 A' or GRADE ='10 B')

Then '2800'

Else If (GRADE ='11 A' or GRADE ='11 B')

Then '3500'

Else If (GRADE ='12')

Then '5000'

END if?>

Call Subtemplate:

In Rtf Template:
<?call:Logo?>
<?import:xdoxsl:///Custom/Custom/Financials/Payables/Reports/Logo Template.xsb(Report Path)?>

In Fusion Sub Template:
<?template_Logo?> insert your Logo <?end template?>

Monday, 8 May 2023

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'