Scribd
Upload
49 views29 pages

GL Dump SQL

Oracle fusion GL Dump SQL

Uploaded by

mbajeesh007
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
49 views29 pages

GL Dump SQL

Oracle fusion GL Dump SQL

Uploaded by

mbajeesh007
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 29

SELECT

GL.NAME LEDGER_NAME,
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
GJH.NAME JV_NAME,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJB.NAME BATCH_NAME,
GJH.PERIOD_NAME,
(SELECT distinct PERIOD_YEAR from GL_PERIODS
where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_YEAR,
(SELECT distinct PERIOD_NUM from GL_PERIODS where
PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
1,
GCC.SEGMENT1),
1,
40) COMP_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2),
1,
40) CC_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
3,
GCC.SEGMENT3),
1,
40) ACCT_DESC,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
4,
GCC.SEGMENT4),
1,
40) INT_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
5,
GCC.SEGMENT5),
1,
40) FUT_DESC,
NVL(xal.ACCOUNTED_CR,0) GL_CR,
NVL(XAL.ACCOUNTED_DR,0) GL_DR,
NVL(XAL.ENTERED_CR,0) GL_EN_CR,
NVL(XAL.ENTERED_DR,0) GL_EN_DR,
---- GJL.CURRENCY_CONVERSION_RATE,
nvl((select
round(CONVERSION_RATE,5)
from GL_DAILY_RATES gd
where 1=1
--GD.TO_CURRENCY = 'USD'
--- and GD.FROM_CURRENCY ='GBP'
and GD.FROM_CURRENCY =GjL.CURRENCY_CODE
AND
GD.CONVERSION_DATE=GjL.CURRENCY_CONVERSION_DATE ),round(NVL(GJL.ACCOUNTED_DR,GJL.AC
COUNTED_CR)/Decode(gjl.ENTERED_DR,null,Decode(GJL.ENTERED_CR,null,GJL.ENTERED_CR,0,
1,GJL.ENTERED_CR),0,1,gjl.ENTERED_DR),3)) CURRENCY_CONVERSION_RATE,
GJL.CURRENCY_CONVERSION_DATE,
NVL(XAL.ACCOUNTED_DR,0)-NVL(XAL.ACCOUNTED_CR,0)
Net,
GJL.CURRENCY_CODE ENT_CUR, gcc.segment1||'-'||
gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 GL_STRING,
GJL.DESCRIPTION JV_LINE_DESCRIPTION,
(select distinct DESCRIPTION from
ra_customer_trx_lines_all where customer_trx_id =rta.customer_trx_id and
LINE_NUMBER = XAL.AE_LINE_NUM) line_desc,
XAH.EVENT_TYPE_CODE,
XAH.DESCRIPTION SLA_DESCRIPTION,
XAL.AE_LINE_NUM,
XAL.ACCOUNTING_DATE GL_DATE,
(SELECT AC.PARTY_NAME
FROM HZ_PARTIES AC,hz_cust_accounts hc WHERE
AC.PARTY_ID=hc.party_id
and
hc.cust_account_id=rta.bill_to_customer_id) CUSTOMER_SUPPLIER_NAME,
(SELECT hc.account_number
FROM HZ_PARTIES AC,hz_cust_accounts hc WHERE
AC.PARTY_ID=hc.party_id
and
hc.cust_account_id=rta.bill_to_customer_id) Supplier_number,
rta.trx_number
INVOICE_RECEIPT_PAYMENT_NUMBER,
/* (--select distinct f.segment1 from
pjb_invoice_headers a,PJB_inv_line_dists e, PJF_PROJECTs_all_b f
where e.linked_project_id=f.project_id
and a.invoice_id=e.invoice_id
and a.ra_invoice_number=rta.trx_number)*/
rta.INTERFACE_HEADER_ATTRIBUTE1 project_number,
null ASSET_NUMBER,
null PO_NUMBER,
null ITEM_NUMBER,
XAH.CREATION_DATE,
XAH.CREATED_BY,GJH.JE_HEADER_ID
header_id,GJL.je_LINE_num line_id, XAH.AE_HEADER_ID||XAL.AE_LINE_NUM id_num,
XAL.GL_SL_LINK_ID
,gjl.ATTRIBUTE7 Insurance_Claim_Number,
rta.trx_date Invoice_date
FROM GL_JE_BATCHES GJB,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_distribution_links xdl,
ra_customer_trx_all rta,
RA_CUST_TRX_LINE_GL_DIST_ALL rctd,
GL_LEDGERS GL
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJL.CODE_COMBINATION_ID =
GCC.CODE_COMBINATION_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
and xdl.ae_header_id=xal.ae_header_id
and
xdl.SOURCE_DISTRIBUTION_ID_NUM_1=rctd.cust_trx_line_gl_dist_id
and rctd.customer_trx_id=rta.customer_trx_id
and xdl.ae_line_num=xal.ae_line_num
--AND GJL.STATUS = 'P'
AND GJH.JE_SOURCE = 'Receivables'
and GJH.LEDGER_ID=GL.LEDGER_ID
AND gjh.je_category in ('Sales Invoices','Contract
Invoices','Credit Memos')
AND gl.ledger_id in (:P_Ledger)
and GJH.PERIOD_NAME in (:P_PERIOD_NAME)
and GCC.SEGMENT2 in NVL(:P_COST_CENTER,GCC.SEGMENT2)
and GCC.SEGMENT3 in NVL(:P_NAT_ACCOUNT,GCC.SEGMENT3)
and GCC.SEGMENT4 in NVL(:P_IC,GCC.SEGMENT4)
and GCC.SEGMENT5 in NVL(:P_FUT,GCC.SEGMENT5)

and (select user_je_source_name from gl_je_sources where


je_source_name=GJH.JE_SOURCE) in NVL((:P_JE_SOURCE),(select user_je_source_name
from gl_je_sources where je_source_name=GJH.JE_SOURCE))

UNION
SELECT
GL.NAME LEDGER_NAME,
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
GJH.NAME JV_NAME,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJB.NAME BATCH_NAME,
GJH.PERIOD_NAME,
(SELECT distinct PERIOD_YEAR from GL_PERIODS
where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_YEAR,
(SELECT distinct PERIOD_NUM from GL_PERIODS where
PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5, SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
1,
GCC.SEGMENT1),
1,
40) COMP_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2),
1,
40) CC_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
3,
GCC.SEGMENT3),
1,
40) ACCT_DESC,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
4,
GCC.SEGMENT4),
1,
40) INT_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
5,
GCC.SEGMENT5),
1,
40) FUT_DESC,
NVL(xal.ACCOUNTED_CR,0) GL_CR,
NVL(xal.ACCOUNTED_DR,0) GL_DR,
NVL(xal.ENTERED_CR,0) GL_EN_CR,
NVL(xal.ENTERED_DR,0) GL_EN_DR,
--- GJL.CURRENCY_CONVERSION_RATE,
nvl((select
round(CONVERSION_RATE,5)
from GL_DAILY_RATES gd
where 1=1
--GD.TO_CURRENCY = 'USD'
--- and GD.FROM_CURRENCY ='GBP'
and GD.FROM_CURRENCY =GjL.CURRENCY_CODE
AND
GD.CONVERSION_DATE=GjL.CURRENCY_CONVERSION_DATE ),round(NVL(GJL.ACCOUNTED_DR,GJL.AC
COUNTED_CR)/Decode(gjl.ENTERED_DR,null,Decode(GJL.ENTERED_CR,null,GJL.ENTERED_CR,0,
1,GJL.ENTERED_CR),0,1,gjl.ENTERED_DR),3)) CURRENCY_CONVERSION_RATE,
GJL.CURRENCY_CONVERSION_DATE,
nvl(xal.ACCOUNTED_DR,0)-NVL(xal.ACCOUNTED_CR,0) net,
GJL.CURRENCY_CODE ENT_CUR, gcc.segment1||'-'||
gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 GL_STRING,
GJL.DESCRIPTION
JV_LINE_DESCRIPTION,
null line_desc,
XAH.EVENT_TYPE_CODE,
XAH.DESCRIPTION SLA_DESCRIPTION,
XAL.AE_LINE_NUM,
XAL.ACCOUNTING_DATE GL_DATE,
(SELECT AC.PARTY_NAME
FROM HZ_PARTIES AC,hz_cust_accounts hc WHERE
AC.PARTY_ID=hc.party_id
and hc.cust_account_id=acr.PAY_FROM_CUSTOMER)
CUSTOMER_SUPPLIER_NAME,
(SELECT hc.account_number
FROM HZ_PARTIES AC,hz_cust_accounts hc WHERE
AC.PARTY_ID=hc.party_id
and hc.cust_account_id=acr.PAY_FROM_CUSTOMER)
Supplier_number,
acr.receipt_number
INVOICE_RECEIPT_PAYMENT_NUMBER,
null project_number,
null ASSET_NUMBER,
null PO_NUMBER,
null ITEM_NUMBER,
XAH.CREATION_DATE,
XAH.CREATED_BY,GJH.JE_HEADER_ID
header_id,GJL.je_LINE_num line_id, XAH.AE_HEADER_ID||XAL.AE_LINE_NUM id_num,
XAL.GL_SL_LINK_ID
,gjl.ATTRIBUTE7 Insurance_Claim_Number
,acr. Receipt_date INVOICE_DATE
FROM GL_JE_BATCHES GJB,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH
--XLA_distribution_links xdl,
-- ar_payment_schedules_all PS
--,ar_receivable_applications_all arr
,ar_cash_receipts_all acr
,xla_transaction_entities xte
--,ar_cash_receipt_history_all acrh
--,ar_distributions_all ard ,
,GL_LEDGERS GL
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJL.CODE_COMBINATION_ID =
GCC.CODE_COMBINATION_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
and gjl.je_line_num=gir.je_line_num
AND (GJH.JE_HEADER_ID=GIR.JE_HEADER_ID or
gjh.REVERSED_JE_HEADER_ID=GIR.JE_HEADER_ID)
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE

AND xal.code_combination_id = gcc.code_combination_id


AND xte.application_id = xal.application_id
AND xte.entity_id = xah.entity_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xte.ledger_id = gl.ledger_id
AND xte.entity_code = 'RECEIPTS'
AND NVL (xte.source_id_int_1, -99) = acr.cash_receipt_id
--AND GJL.STATUS = 'P'
AND GJH.JE_SOURCE = 'Receivables'
and GJH.LEDGER_ID=GL.LEDGER_ID
-- AND gl.ledger_id in NVL(:P_Ledger,gl.ledger_id)
and gjh.je_category in ('Receipts','Misc Receipts')
AND gl.ledger_id in (:P_Ledger)
and GJH.PERIOD_NAME in (:P_PERIOD_NAME)
and GCC.SEGMENT2 in NVL(:P_COST_CENTER,GCC.SEGMENT2)
and GCC.SEGMENT3 in NVL(:P_NAT_ACCOUNT,GCC.SEGMENT3)
and GCC.SEGMENT4 in NVL(:P_IC,GCC.SEGMENT4)
and GCC.SEGMENT5 in NVL(:P_FUT,GCC.SEGMENT5)

and (select user_je_source_name from gl_je_sources where


je_source_name=GJH.JE_SOURCE) in NVL((:P_JE_SOURCE),(select user_je_source_name
from gl_je_sources where je_source_name=GJH.JE_SOURCE))
UNION
SELECT
GL.NAME LEDGER_NAME,
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
GJH.NAME JV_NAME,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJB.NAME BATCH_NAME,
GJH.PERIOD_NAME,
(SELECT distinct PERIOD_YEAR from GL_PERIODS
where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_YEAR,
(SELECT distinct PERIOD_NUM from GL_PERIODS where
PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5, SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
1,
GCC.SEGMENT1),
1,
40) COMP_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2),
1,
40) CC_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
3,
GCC.SEGMENT3),
1,
40) ACCT_DESC,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
4,
GCC.SEGMENT4),
1,
40) INT_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
5,
GCC.SEGMENT5),
1,
40) FUT_DESC,
NVL(xal.ACCOUNTED_CR,0) GL_CR,
NVL(XAL.ACCOUNTED_DR,0) GL_DR,
NVL(XAL.ENTERED_CR,0) GL_EN_CR,
NVL(XAL.ENTERED_DR,0) GL_EN_DR,
---- GJL.CURRENCY_CONVERSION_RATE,
nvl((select
round(CONVERSION_RATE,5)
from GL_DAILY_RATES gd
where 1=1
--GD.TO_CURRENCY = 'USD'
--- and GD.FROM_CURRENCY ='GBP'
and GD.FROM_CURRENCY =GjL.CURRENCY_CODE
AND
GD.CONVERSION_DATE=GjL.CURRENCY_CONVERSION_DATE ),round(NVL(GJL.ACCOUNTED_DR,GJL.AC
COUNTED_CR)/Decode(gjl.ENTERED_DR,null,Decode(GJL.ENTERED_CR,null,GJL.ENTERED_CR,0,
1,GJL.ENTERED_CR),0,1,gjl.ENTERED_DR),3)) CURRENCY_CONVERSION_RATE,
GJL.CURRENCY_CONVERSION_DATE,
NVL(XAL.ACCOUNTED_DR,0)-NVL(XAL.ACCOUNTED_CR,0)
Net,
GJL.CURRENCY_CODE ENT_CUR, gcc.segment1||'-'||
gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 GL_STRING,
GJL.DESCRIPTION JV_LINE_DESCRIPTION,
null line_desc,
XAH.EVENT_TYPE_CODE,
XAH.DESCRIPTION SLA_DESCRIPTION,
XAL.AE_LINE_NUM,
XAL.ACCOUNTING_DATE GL_DATE,
(SELECT AC.PARTY_NAME
FROM HZ_PARTIES AC,hz_cust_accounts
hc,ra_customer_trx_all rta WHERE AC.PARTY_ID=hc.party_id
and
hc.cust_account_id=rta.bill_to_customer_id
and rta.customer_trx_id=aaa.customer_trx_id)
CUSTOMER_SUPPLIER_NAME,
(SELECT hc.account_number
FROM HZ_PARTIES AC,hz_cust_accounts
hc,ra_customer_trx_all rta WHERE AC.PARTY_ID=hc.party_id
and
hc.cust_account_id=rta.bill_to_customer_id
and rta.customer_trx_id=aaa.customer_trx_id)
Supplier_number,
aaa.ADJUSTMENT_NUMBER
INVOICE_RECEIPT_PAYMENT_NUMBER,
null project_number,
null ASSET_NUMBER,
null PO_NUMBER,
null ITEM_NUMBER,
XAH.CREATION_DATE,
XAH.CREATED_BY,GJH.JE_HEADER_ID
header_id,GJL.je_LINE_num line_id, XAH.AE_HEADER_ID||XAL.AE_LINE_NUM id_num,
XAL.GL_SL_LINK_ID
,gjl.ATTRIBUTE7 Insurance_Claim_Number,
aaa.APPLY_DATE Invoice_date
FROM GL_JE_BATCHES GJB,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
xla_events xe,
xla_transaction_entities xte,
ar_adjustments_all aaa,
GL_LEDGERS GL
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJL.CODE_COMBINATION_ID =
GCC.CODE_COMBINATION_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND xah.application_id = xe.application_id
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xe.application_id = xte.application_id
--AND xte.application_id = 222
AND xe.entity_id = xte.entity_id
AND xte.entity_code = 'ADJUSTMENTS'
AND xte.source_id_int_1 = aaa.adjustment_id
--AND GJL.STATUS = 'P'
AND GJH.JE_SOURCE = 'Receivables'
and GJH.LEDGER_ID=GL.LEDGER_ID
AND gjh.je_category in ('Adjustment')
AND gl.ledger_id in (:P_Ledger)
and GJH.PERIOD_NAME in (:P_PERIOD_NAME)
and GCC.SEGMENT2 in NVL(:P_COST_CENTER,GCC.SEGMENT2)
and GCC.SEGMENT3 in NVL(:P_NAT_ACCOUNT,GCC.SEGMENT3)
and GCC.SEGMENT4 in NVL(:P_IC,GCC.SEGMENT4)
and GCC.SEGMENT5 in NVL(:P_FUT,GCC.SEGMENT5)

and (select user_je_source_name from gl_je_sources where


je_source_name=GJH.JE_SOURCE) in NVL((:P_JE_SOURCE),(select user_je_source_name
from gl_je_sources where je_source_name=GJH.JE_SOURCE))
UNION
SELECT
GL.NAME LEDGER_NAME,
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
GJH.NAME JV_NAME,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJB.NAME BATCH_NAME,
GJH.PERIOD_NAME,
(SELECT distinct PERIOD_YEAR from
GL_PERIODS where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_YEAR,
(SELECT distinct PERIOD_NUM from GL_PERIODS
where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5, SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
1,
GCC.SEGMENT1),
1,
40) COMP_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2),
1,
40) CC_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
3,
GCC.SEGMENT3),
1,
40) ACCT_DESC,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
4,
GCC.SEGMENT4),
1,
40) INT_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
5,
GCC.SEGMENT5),
1,
40) FUT_DESC,
NVL(xal.ACCOUNTED_CR,0) GL_CR,
NVL(xal.ACCOUNTED_DR,0) GL_DR,
NVL(xal.ENTERED_CR,0) GL_EN_CR,
NVL(xal.ENTERED_DR,0) GL_EN_DR,
--- GJL.CURRENCY_CONVERSION_RATE,
nvl((select
round(CONVERSION_RATE,5)
from GL_DAILY_RATES gd
where 1=1
--GD.TO_CURRENCY = 'USD'
--- and GD.FROM_CURRENCY ='GBP'
and GD.FROM_CURRENCY =GjL.CURRENCY_CODE
AND
GD.CONVERSION_DATE=GjL.CURRENCY_CONVERSION_DATE ),round(NVL(GJL.ACCOUNTED_DR,GJL.AC
COUNTED_CR)/Decode(gjl.ENTERED_DR,null,Decode(GJL.ENTERED_CR,null,GJL.ENTERED_CR,0,
1,GJL.ENTERED_CR),0,1,gjl.ENTERED_DR),3)) CURRENCY_CONVERSION_RATE,
GJL.CURRENCY_CONVERSION_DATE,
NVL(XAL.ACCOUNTED_DR,0)-
NVL(XAL.ACCOUNTED_CR,0) Net,
GJL.CURRENCY_CODE ENT_CUR,
gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||
gcc.segment5 GL_STRING,
GJL.DESCRIPTION JV_LINE_DESCRIPTION,
(select DESCRIPTION from ap_invoice_lines_all
where invoice_id=aia.invoice_id and LINE_NUMBER =XAL.AE_LINE_NUM) line_desc,
XAH.EVENT_TYPE_CODE,
XAH.DESCRIPTION SLA_DESCRIPTION,
XAL.AE_LINE_NUM,
XAL.ACCOUNTING_DATE GL_DATE,
HP.PARTY_NAME CUSTOMER_SUPPLIER_NAME,
-- asup.segment1 Supplier_number,
(select segment1 from poz_Suppliers where
party_id=hp.party_id) Supplier_number,
to_char(aia.invoice_num)
INVOICE_RECEIPT_PAYMENT_NUMBER,
null project_number,
null ASSET_NUMBER,
(select segment1 from po_headers_all where
po_header_id=aia.po_header_id ) PO_NUMBER,
null ITEM_NUMBER,
XAH.CREATION_DATE,
XAH.CREATED_BY,GJH.JE_HEADER_ID
header_id,GJL.je_LINE_num line_id, XAH.AE_HEADER_ID||XAL.AE_LINE_NUM id_num,
XAL.GL_SL_LINK_ID
,gjl.ATTRIBUTE7 Insurance_Claim_Number,
AIA.INVOICE_DATE INVOICE_DATE
FROM
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH,
GL_CODE_COMBINATIONS GCC,
--- POZ_SUPPLIERS ASUP,
hz_parties hp,
GL_JE_BATCHES GJB,
GL_LEDGERS GL,
XLA_distribution_links xdl,
ap_invoice_distributions_all aid,
ap_invoices_all aia
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND
GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
GJL.JE_HEADER_ID=GJH.JE_HEADER_ID AND

GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
---AND ((AIA.VENDOR_ID=ASUP.VENDOR_ID AND
HP.PARTY_ID=ASUP.PARTY_ID) or (aia.PARTY_ID = HP.PARTY_ID ))
and aia.PARTY_ID = HP.PARTY_ID

and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num
and
xdl.SOURCE_DISTRIBUTION_ID_NUM_1=aid.invoice_distribution_id
and aid.invoice_id=aia.invoice_id
--AND GJH.STATUS='P'
AND
GJH.JE_SOURCE='Payables'
and xdl.event_class_code<>'PREPAYMENT
APPLICATIONS'
and GJH.LEDGER_ID=GL.LEDGER_ID
-- AND gl.ledger_id in
NVL(:P_Ledger,gl.ledger_id)
and gjh.je_category ='Purchase Invoices'
AND gl.ledger_id in (:P_Ledger)
and GJH.PERIOD_NAME in (:P_PERIOD_NAME)
and GCC.SEGMENT2 in NVL(:P_COST_CENTER,GCC.SEGMENT2)
and GCC.SEGMENT3 in NVL(:P_NAT_ACCOUNT,GCC.SEGMENT3)
and GCC.SEGMENT4 in NVL(:P_IC,GCC.SEGMENT4)
and GCC.SEGMENT5 in NVL(:P_FUT,GCC.SEGMENT5)

and (select user_je_source_name from gl_je_sources where


je_source_name=GJH.JE_SOURCE) in NVL((:P_JE_SOURCE),(select user_je_source_name
from gl_je_sources where je_source_name=GJH.JE_SOURCE))
union
SELECT
GL.NAME LEDGER_NAME,
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
GJH.NAME JV_NAME,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJB.NAME BATCH_NAME,
GJH.PERIOD_NAME,
(SELECT distinct PERIOD_YEAR from
GL_PERIODS where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_YEAR,
(SELECT distinct PERIOD_NUM from GL_PERIODS
where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5, SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
1,
GCC.SEGMENT1),
1,
40) COMP_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2),
1,
40) CC_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
3,
GCC.SEGMENT3),
1,
40) ACCT_DESC,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
4,
GCC.SEGMENT4),
1,
40) INT_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
5,
GCC.SEGMENT5),
1,
40) FUT_DESC,
NVL(xal.ACCOUNTED_CR,0) GL_CR,
NVL(xal.ACCOUNTED_DR,0) GL_DR,
NVL(xal.ENTERED_CR,0) GL_EN_CR,
NVL(xal.ENTERED_DR,0) GL_EN_DR,
--- GJL.CURRENCY_CONVERSION_RATE,
nvl((select
round(CONVERSION_RATE,5)
from GL_DAILY_RATES gd
where 1=1
--GD.TO_CURRENCY = 'USD'
--- and GD.FROM_CURRENCY ='GBP'
and GD.FROM_CURRENCY =GjL.CURRENCY_CODE
AND
GD.CONVERSION_DATE=GjL.CURRENCY_CONVERSION_DATE ),round(NVL(GJL.ACCOUNTED_DR,GJL.AC
COUNTED_CR)/Decode(gjl.ENTERED_DR,null,Decode(GJL.ENTERED_CR,null,GJL.ENTERED_CR,0,
1,GJL.ENTERED_CR),0,1,gjl.ENTERED_DR),3)) CURRENCY_CONVERSION_RATE,
GJL.CURRENCY_CONVERSION_DATE,
NVL(XAL.ACCOUNTED_DR,0)-
NVL(XAL.ACCOUNTED_CR,0) Net,
GJL.CURRENCY_CODE ENT_CUR,
gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||
gcc.segment5 GL_STRING,
GJL.DESCRIPTION
JV_LINE_DESCRIPTION,
(select distinct DESCRIPTION from
ap_invoice_lines_all where invoice_id=aia.invoice_id and LINE_NUMBER
=XAL.AE_LINE_NUM) line_desc,
XAH.EVENT_TYPE_CODE,
XAH.DESCRIPTION SLA_DESCRIPTION,
XAL.AE_LINE_NUM,
XAL.ACCOUNTING_DATE GL_DATE,
HP.PARTY_NAME CUSTOMER_SUPPLIER_NAME,
-- asup.segment1 Supplier_number,
(select segment1 from poz_Suppliers where
party_id=hp.party_id) Supplier_number,
to_char(aia.invoice_num)
INVOICE_RECEIPT_PAYMENT_NUMBER,
null project_number,
null ASSET_NUMBER,
(select segment1 from po_headers_all where
po_header_id=aia.po_header_id ) PO_NUMBER,
null ITEM_NUMBER,
XAH.CREATION_DATE,
XAH.CREATED_BY,GJH.JE_HEADER_ID
header_id,GJL.je_LINE_num line_id, XAH.AE_HEADER_ID||XAL.AE_LINE_NUM id_num,
XAL.GL_SL_LINK_ID
,gjl.ATTRIBUTE7 Insurance_Claim_Number,
AIA.INVOICE_DATE INVOICE_DATE
FROM
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH,
GL_CODE_COMBINATIONS GCC,
--- POZ_SUPPLIERS ASUP,
hz_parties hp,
GL_JE_BATCHES GJB,
GL_LEDGERS GL,
XLA_distribution_links xdl,
-- AP_PREPAY_APP_DISTS apd,
ap_invoice_distributions_all aid,
ap_invoices_all aia
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND
GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
GJL.JE_HEADER_ID=GJH.JE_HEADER_ID AND

gjl.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
---AND ((AIA.VENDOR_ID=ASUP.VENDOR_ID AND
HP.PARTY_ID=ASUP.PARTY_ID) or (aia.PARTY_ID = HP.PARTY_ID ))
and aia.PARTY_ID = HP.PARTY_ID

and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num
and xdl.APPLIED_TO_DIST_ID_NUM_1 =
aid.invoice_distribution_id
--and xdl.ae_line_num=xal.ae_line_num
-- and
aid.invoice_distribution_id=apd.prepay_app_distribution_id
and aid.invoice_id=aia.invoice_id
--AND GJH.STATUS='P'
AND GJH.JE_SOURCE='Payables'
and xdl.event_class_code='PREPAYMENT
APPLICATIONS'
and GJH.LEDGER_ID=GL.LEDGER_ID
--AND gl.ledger_id in
NVL(:P_Ledger,gl.ledger_id)
and gjh.je_category ='Purchase Invoices'
AND gl.ledger_id in (:P_Ledger)
and GJH.PERIOD_NAME in (:P_PERIOD_NAME)
and GCC.SEGMENT2 in NVL(:P_COST_CENTER,GCC.SEGMENT2)
and GCC.SEGMENT3 in NVL(:P_NAT_ACCOUNT,GCC.SEGMENT3)
and GCC.SEGMENT4 in NVL(:P_IC,GCC.SEGMENT4)
and GCC.SEGMENT5 in NVL(:P_FUT,GCC.SEGMENT5)

and (select user_je_source_name from gl_je_sources where


je_source_name=GJH.JE_SOURCE) in NVL((:P_JE_SOURCE),(select user_je_source_name
from gl_je_sources where je_source_name=GJH.JE_SOURCE))
union
SELECT

GL.NAME LEDGER_NAME,
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
GJH.NAME JV_NAME,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJB.NAME BATCH_NAME,
GJH.PERIOD_NAME,
(SELECT distinct PERIOD_YEAR from GL_PERIODS
where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_YEAR,
(SELECT distinct PERIOD_NUM from GL_PERIODS where
PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5, SUBSTR (
GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
1,
GCC.SEGMENT1),
1,
40) COMP_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2),
1,
40) CC_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
3,
GCC.SEGMENT3),
1,
40) ACCT_DESC,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
4,
GCC.SEGMENT4),
1,
40) INT_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
5,
GCC.SEGMENT5),
1,
40) FUT_DESC,

NVL(XAL.ACCOUNTED_CR,0) GL_CR,
NVL(XAL.ACCOUNTED_DR,0) GL_DR,
NVL(XAL.ENTERED_CR,0) GL_EN_CR,
NVL(XAL.ENTERED_DR,0) GL_EN_DR,
--- GJL.CURRENCY_CONVERSION_RATE,
nvl((select
round(CONVERSION_RATE,5)
from GL_DAILY_RATES gd
where 1=1
--GD.TO_CURRENCY = 'USD'
--- and GD.FROM_CURRENCY ='GBP'
and GD.FROM_CURRENCY =GjL.CURRENCY_CODE
AND
GD.CONVERSION_DATE=GjL.CURRENCY_CONVERSION_DATE ),round(NVL(GJL.ACCOUNTED_DR,GJL.AC
COUNTED_CR)/Decode(gjl.ENTERED_DR,null,Decode(GJL.ENTERED_CR,null,GJL.ENTERED_CR,0,
1,GJL.ENTERED_CR),0,1,gjl.ENTERED_DR),3)) CURRENCY_CONVERSION_RATE,
GJL.CURRENCY_CONVERSION_DATE,
NVL(XAL.ACCOUNTED_DR,0)-NVL(XAL.ACCOUNTED_CR,0)
Net,
GJL.CURRENCY_CODE ENT_CUR, gcc.segment1||'-'||
gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 GL_STRING,
GJL.DESCRIPTION JV_LINE_DESCRIPTION,
null line_desc,
XAH.EVENT_TYPE_CODE,
XAH.DESCRIPTION SLA_DESCRIPTION,
XAL.AE_LINE_NUM,
XAL.ACCOUNTING_DATE GL_DATE,
aca.vendor_name CUSTOMER_SUPPLIER_name,
(select segment1 from poz_suppliers where
VENDOR_ID=aca.VENDOR_ID) Supplier_number,
to_char(aca.check_number)
INVOICE_RECEIPT_PAYMENT_NUMBER,
null project_number,
null ASSET_NUMBER,
null PO_NUMBER,
null ITEM_NUMBER,
XAH.CREATION_DATE,
XAH.CREATED_BY,GJH.JE_HEADER_ID
header_id,GJL.je_LINE_num line_id, XAH.AE_HEADER_ID||XAL.AE_LINE_NUM id_num,
XAL.GL_SL_LINK_ID
,gjl.ATTRIBUTE7 Insurance_Claim_Number
,aca.CHECK_DATE INVOICE_DATE
FROM
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH,
GL_CODE_COMBINATIONS GCC,
GL_JE_BATCHES GJB,
GL_LEDGERS GL,
XLA_distribution_links xdl,
ap_payment_hist_dists aphd,
ap_invoice_payments_all aipa,
ap_checks_all aca
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
GJL.JE_HEADER_ID=GJH.JE_HEADER_ID AND
GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
and xdl.ae_header_id=xal.ae_header_id
and
xdl.SOURCE_DISTRIBUTION_ID_NUM_1=aphd.payment_hist_dist_id
AND aipa.invoice_payment_id =
aphd.invoice_payment_id
AND aca.check_id = aipa.check_id
and xdl.ae_line_num=xdl.ae_line_num
--AND GJH.STATUS='P'
AND GJH.JE_SOURCE='Payables'
-- and GJH.LEDGER_ID=GL.LEDGER_ID
--AND gl.ledger_id in NVL(:P_Ledger,gl.ledger_id)
and gjh.je_category in ('Payments','Reconciled
Payments')
AND gl.ledger_id in (:P_Ledger)
and GJH.PERIOD_NAME in (:P_PERIOD_NAME)
and GCC.SEGMENT2 in NVL(:P_COST_CENTER,GCC.SEGMENT2)
and GCC.SEGMENT3 in NVL(:P_NAT_ACCOUNT,GCC.SEGMENT3)
and GCC.SEGMENT4 in NVL(:P_IC,GCC.SEGMENT4)
and GCC.SEGMENT5 in NVL(:P_FUT,GCC.SEGMENT5)

and (select user_je_source_name from gl_je_sources where


je_source_name=GJH.JE_SOURCE) in NVL((:P_JE_SOURCE),(select user_je_source_name
from gl_je_sources where je_source_name=GJH.JE_SOURCE))
UNION
SELECT GL.NAME LEDGER_NAME,
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
GJH.NAME JV_NAME,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJB.NAME BATCH_NAME,
GJH.PERIOD_NAME,
(SELECT distinct PERIOD_YEAR from GL_PERIODS
where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_YEAR,
(SELECT distinct PERIOD_NUM from GL_PERIODS where
PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5, SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
1,
GCC.SEGMENT1),
1,
40) COMP_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2),
1,
40) CC_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
3,
GCC.SEGMENT3),
1,
40) ACCT_DESC,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
4,
GCC.SEGMENT4),
1,
40) INT_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
5,
GCC.SEGMENT5),
1,
40) FUT_DESC,
NVL(xal.ACCOUNTED_CR,0) GL_CR,
NVL(XAL.ACCOUNTED_DR,0) GL_DR,
NVL(XAL.ENTERED_CR,0) GL_EN_CR,
NVL(XAL.ENTERED_DR,0) GL_EN_DR,
--- GJL.CURRENCY_CONVERSION_RATE,
nvl((select
round(CONVERSION_RATE,5)
from GL_DAILY_RATES gd
where 1=1
--GD.TO_CURRENCY = 'USD'
--- and GD.FROM_CURRENCY ='GBP'
and GD.FROM_CURRENCY =GjL.CURRENCY_CODE
AND
GD.CONVERSION_DATE=GjL.CURRENCY_CONVERSION_DATE ),round(NVL(GJL.ACCOUNTED_DR,GJL.AC
COUNTED_CR)/Decode(gjl.ENTERED_DR,null,Decode(GJL.ENTERED_CR,null,GJL.ENTERED_CR,0,
1,GJL.ENTERED_CR),0,1,gjl.ENTERED_DR),3)) CURRENCY_CONVERSION_RATE,
GJL.CURRENCY_CONVERSION_DATE,
NVL(XAL.ACCOUNTED_DR,0)-NVL(XAL.ACCOUNTED_CR,0)
Net,
GJL.CURRENCY_CODE ENT_CUR, gcc.segment1||'-'||
gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 GL_STRING,
GJL.DESCRIPTION JV_LINE_DESCRIPTION,
null line_desc,
XAH.EVENT_TYPE_CODE,
XAH.DESCRIPTION SLA_DESCRIPTION,
XAL.AE_LINE_NUM,
XAL.ACCOUNTING_DATE GL_DATE,
null CUSTOMER_SUPPLIER_NAME,
null supplier_number,
null INVOICE_RECEIPT_PAYMENT_NUMBER,
pro.segment1 project_number,
null ASSET_NUMBER,
null PO_NUMBER,
null ITEM_NUMBER,
NVL(XAH.CREATION_DATE,GJH.CREATION_DATE),
NVL(XAH.CREATED_BY,GJH.CREATED_BY),

GJH.JE_HEADER_ID header_id,GJL.je_LINE_num line_id,


gjh.je_HEADER_ID||gjl.je_LINE_NUM id_num, XAL.GL_SL_LINK_ID
,gjl.ATTRIBUTE7 Insurance_Claim_Number
,null invoice_date
FROM
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
gl_je_batches gjb,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
pjc_exp_items_all pje,
gl_code_combinations gcc,
pjf_projects_all_vl pro,
GL_LEDGERS GL,
xla_distribution_links xdl
WHERE
GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_LINE_num=GIR.JE_LINE_num
AND GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.application_id = xah.application_id
AND XAL.AE_HEADER_ID=XAH.AE_HEADER_ID
and GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
and GJH.LEDGER_ID=GL.LEDGER_ID
and GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
and xal.ae_header_id=xdl.ae_header_id
and xal.ae_line_num=xdl.ae_line_num
-- AND GJH.STATUS='P'
and
xdl.SOURCE_DISTRIBUTION_ID_NUM_1=pje.expenditure_item_id
--AND gl.ledger_id in NVL(:P_Ledger,gl.ledger_id)
and gjh.je_source='Project Accounting'
and pje.project_id=pro.project_id
AND gl.ledger_id in (:P_Ledger)
and GJH.PERIOD_NAME in (:P_PERIOD_NAME)
and GCC.SEGMENT2 in NVL(:P_COST_CENTER,GCC.SEGMENT2)
and GCC.SEGMENT3 in NVL(:P_NAT_ACCOUNT,GCC.SEGMENT3)
and GCC.SEGMENT4 in NVL(:P_IC,GCC.SEGMENT4)
and GCC.SEGMENT5 in NVL(:P_FUT,GCC.SEGMENT5)

and (select user_je_source_name from gl_je_sources where


je_source_name=GJH.JE_SOURCE) in NVL((:P_JE_SOURCE),(select user_je_source_name
from gl_je_sources where je_source_name=GJH.JE_SOURCE))
union
SELECT GL.NAME LEDGER_NAME,
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
GJH.NAME JV_NAME,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJB.NAME BATCH_NAME,
GJH.PERIOD_NAME,
(SELECT distinct PERIOD_YEAR from GL_PERIODS
where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_YEAR,
(SELECT distinct PERIOD_NUM from GL_PERIODS where
PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5, SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
1,
GCC.SEGMENT1),
1,
40) COMP_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2),
1,
40) CC_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
3,
GCC.SEGMENT3),
1,
40) ACCT_DESC,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
4,
GCC.SEGMENT4),
1,
40) INT_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
5,
GCC.SEGMENT5),
1,
40) FUT_DESC,
NVL(xal.ACCOUNTED_CR,0) GL_CR,
NVL(XAL.ACCOUNTED_DR,0) GL_DR,
NVL(XAL.ENTERED_CR,0) GL_EN_CR,
NVL(XAL.ENTERED_DR,0) GL_EN_DR,
--- GJL.CURRENCY_CONVERSION_RATE,
nvl((select
round(CONVERSION_RATE,5)
from GL_DAILY_RATES gd
where 1=1
--GD.TO_CURRENCY = 'USD'
--- and GD.FROM_CURRENCY ='GBP'
and GD.FROM_CURRENCY =GjL.CURRENCY_CODE
AND
GD.CONVERSION_DATE=GjL.CURRENCY_CONVERSION_DATE ),round(NVL(GJL.ACCOUNTED_DR,GJL.AC
COUNTED_CR)/Decode(gjl.ENTERED_DR,null,Decode(GJL.ENTERED_CR,null,GJL.ENTERED_CR,0,
1,GJL.ENTERED_CR),0,1,gjl.ENTERED_DR),3)) CURRENCY_CONVERSION_RATE,
GJL.CURRENCY_CONVERSION_DATE,
NVL(XAL.ACCOUNTED_DR,0)-NVL(XAL.ACCOUNTED_CR,0)
Net,
GJL.CURRENCY_CODE ENT_CUR, gcc.segment1||'-'||
gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 GL_STRING,
GJL.DESCRIPTION JV_LINE_DESCRIPTION,
null line_desc,
XAH.EVENT_TYPE_CODE,
XAH.DESCRIPTION SLA_DESCRIPTION,
XAL.AE_LINE_NUM,
XAL.ACCOUNTING_DATE GL_DATE,
null CUSTOMER_SUPPLIER_NAME,
null Supplier_number,
null INVOICE_RECEIPT_PAYMENT_NUMBER,
null project_number,
null ASSET_NUMBER,
null PO_NUMBER,
esi.item_number ITEM_NUMBER,
NVL(XAH.CREATION_DATE,GJH.CREATION_DATE),
NVL(XAH.CREATED_BY,GJH.CREATED_BY),

GJH.JE_HEADER_ID header_id,GJL.je_LINE_num line_id,


gjh.je_HEADER_ID||gjl.je_LINE_NUM id_num,XAL.GL_SL_LINK_ID
,gjl.ATTRIBUTE7 Insurance_Claim_Number
,imt.Transaction_date INVOICE_DATE
FROM
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
gl_je_batches gjb,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
inv_material_txns imt,
gl_code_combinations gcc,
GL_LEDGERS GL,
xla_distribution_links xdl,
egp_system_items_b esi
WHERE
GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_LINE_num=GIR.JE_LINE_num
AND GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.application_id = xah.application_id
AND XAL.AE_HEADER_ID=XAH.AE_HEADER_ID
and GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
and GJH.LEDGER_ID=GL.LEDGER_ID
and GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
and xal.ae_header_id=xdl.ae_header_id
and xal.ae_line_num=xdl.ae_line_num
and xdl.SOURCE_DISTRIBUTION_ID_NUM_1=imt.transaction_id
-- AND GJH.STATUS='P'
and gjh.je_source='Cost Accounting'
--AND gl.ledger_id in NVL(:P_Ledger,gl.ledger_id)
and imt.inventory_item_id=esi.inventory_item_id
AND gl.ledger_id in (:P_Ledger)
and GJH.PERIOD_NAME in (:P_PERIOD_NAME)
and GCC.SEGMENT2 in NVL(:P_COST_CENTER,GCC.SEGMENT2)
and GCC.SEGMENT3 in NVL(:P_NAT_ACCOUNT,GCC.SEGMENT3)
and GCC.SEGMENT4 in NVL(:P_IC,GCC.SEGMENT4)
and GCC.SEGMENT5 in NVL(:P_FUT,GCC.SEGMENT5)

and (select user_je_source_name from gl_je_sources where


je_source_name=GJH.JE_SOURCE) in NVL((:P_JE_SOURCE),(select user_je_source_name
from gl_je_sources where je_source_name=GJH.JE_SOURCE))
UNION
SELECT GL.NAME LEDGER_NAME,
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
GJH.NAME JV_NAME,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJB.NAME BATCH_NAME,
GJH.PERIOD_NAME,
(SELECT distinct PERIOD_YEAR from GL_PERIODS
where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_YEAR,
(SELECT distinct PERIOD_NUM from GL_PERIODS where
PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5, SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
1,
GCC.SEGMENT1),
1,
40) COMP_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2),
1,
40) CC_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
3,
GCC.SEGMENT3),
1,
40) ACCT_DESC,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
4,
GCC.SEGMENT4),
1,
40) INT_DESC,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
5,
GCC.SEGMENT5),
1,
40) FUT_DESC,
NVL(xal.ACCOUNTED_CR,0) GL_CR,
NVL(xal.ACCOUNTED_DR,0) GL_DR,
NVL(xal.ENTERED_CR,0) GL_EN_CR,
NVL(xal.ENTERED_DR,0) GL_EN_DR,
-- GJL.CURRENCY_CONVERSION_RATE,
nvl((select
round(CONVERSION_RATE,5)
from GL_DAILY_RATES gd
where 1=1
--GD.TO_CURRENCY = 'USD'
--- and GD.FROM_CURRENCY ='GBP'
and GD.FROM_CURRENCY =GjL.CURRENCY_CODE
AND
GD.CONVERSION_DATE=GjL.CURRENCY_CONVERSION_DATE ),round(NVL(xal.ACCOUNTED_DR,xal.AC
COUNTED_CR)/Decode(gjl.ENTERED_DR,null,Decode(GJL.ENTERED_CR,null,GJL.ENTERED_CR,0,
1,GJL.ENTERED_CR),0,1,gjl.ENTERED_DR),3)) CURRENCY_CONVERSION_RATE,
GJL.CURRENCY_CONVERSION_DATE,
NVL(xal.ACCOUNTED_DR,0)-NVL(xal.ACCOUNTED_CR,0)
Net,
GJL.CURRENCY_CODE ENT_CUR, gcc.segment1||'-'||
gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 GL_STRING,
GJL.DESCRIPTION JV_LINE_DESCRIPTION,
null line_desc,
XAH.EVENT_TYPE_CODE,
XAH.DESCRIPTION SLA_DESCRIPTION,
XAL.AE_LINE_NUM,
XAL.ACCOUNTING_DATE GL_DATE,
null CUSTOMER_SUPPLIER_NAME,
null Supplier_number,
null INVOICE_RECEIPT_PAYMENT_NUMBER,
null project_number,
to_char(ce.ext_transaction_id) ASSET_NUMBER,
null PO_NUMBER,
null ITEM_NUMBER,
NVL(XAH.CREATION_DATE,GJH.CREATION_DATE),
NVL(XAH.CREATED_BY,GJH.CREATED_BY),
GJH.JE_HEADER_ID header_id,GJL.je_LINE_num line_id,
gjh.je_HEADER_ID||gjl.je_LINE_NUM id_num, XAL.GL_SL_LINK_ID
,gjl.ATTRIBUTE7 Insurance_Claim_Number
,CE.EXT_TRX_DATE INVOICE_DATE
FROM
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
gl_je_batches gjb,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
xla_distribution_links xdl,
gl_code_combinations gcc,
CE_XLA_EXT_EXTRACT_V ce,
GL_LEDGERS GL
WHERE
GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_LINE_num=GIR.JE_LINE_num
AND GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID=XAH.AE_HEADER_ID
and GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num
and ce.event_id=xah.event_id
and GJH.LEDGER_ID=GL.LEDGER_ID
and GJH.JE_SOURCE = 'Cash Management'
and GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
--AND GJH.STATUS='P'
AND gl.ledger_id in (:P_Ledger)
and GJH.PERIOD_NAME in (:P_PERIOD_NAME)
and GCC.SEGMENT2 in NVL(:P_COST_CENTER,GCC.SEGMENT2)
and GCC.SEGMENT3 in NVL(:P_NAT_ACCOUNT,GCC.SEGMENT3)
and GCC.SEGMENT4 in NVL(:P_IC,GCC.SEGMENT4)
and GCC.SEGMENT5 in NVL(:P_FUT,GCC.SEGMENT5)

and (select user_je_source_name from gl_je_sources where


je_source_name=GJH.JE_SOURCE) in NVL((:P_JE_SOURCE),(select user_je_source_name
from gl_je_sources where je_source_name=GJH.JE_SOURCE))

UNION
SELECT GL.NAME LEDGER_NAME,
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
GJH.NAME JV_NAME,
GJH.JE_CATEGORY,
(select user_je_source_name from
gl_je_sources where je_source_name=GJH.JE_SOURCE)JE_SOURCE,
GJB.NAME BATCH_NAME,
GJH.PERIOD_NAME,
(SELECT distinct PERIOD_YEAR from GL_PERIODS
where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_YEAR,
(SELECT distinct PERIOD_NUM from GL_PERIODS where
PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5, SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
1,
GCC.SEGMENT1),
1,
40) COMP_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2),
1,
40) CC_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
3,
GCC.SEGMENT3),
1,
40) ACCT_DESC,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
4,
GCC.SEGMENT4),
1,
40) INT_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
5,
GCC.SEGMENT5),
1,
40) FUT_DESC,
NVL(GJL.ACCOUNTED_CR,0) GL_CR,
NVL(GJL.ACCOUNTED_DR,0) GL_DR,
NVL(GJL.ENTERED_CR,0) GL_EN_CR,
NVL(GJL.ENTERED_DR,0) GL_EN_DR,
--- GJL.CURRENCY_CONVERSION_RATE,
nvl((select round(CONVERSION_RATE,5)
from GL_DAILY_RATES gd
where 1=1

--GD.TO_CURRENCY = 'USD'
--- and GD.FROM_CURRENCY ='GBP'
and GD.FROM_CURRENCY =GjL.CURRENCY_CODE
AND
GD.CONVERSION_DATE=GjL.CURRENCY_CONVERSION_DATE ),GJL.CURRENCY_CONVERSION_RATE)
CURRENCY_CONVERSION_RATE,
----
round(NVL(GJL.ACCOUNTED_DR,GJL.ACCOUNTED_CR)/NVL(gjl.ENTERED_DR,GJL.ENTERED_CR),3)C
URRENCY_CONVERSION_RATE
GJL.CURRENCY_CONVERSION_DATE,
NVL(GJL.ACCOUNTED_DR,0)-NVL(GJL.ACCOUNTED_CR,0)
Net,
GJL.CURRENCY_CODE ENT_CUR, gcc.segment1||'-'||
gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 GL_STRING,
GJL.DESCRIPTION JV_LINE_DESCRIPTION,
null line_desc,
null EVENT_TYPE_CODE,
null SLA_DESCRIPTION,
null AE_LINE_NUM,
GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
/*nvl((select distinct
vendor_name from poz_suppliers_v where segment1 in (gjl.attribute1)), */
null CUSTOMER_SUPPLIER_NAME,
null Supplier_number,
null INVOICE_RECEIPT_PAYMENT_NUMBER,
null project_number,
null ASSET_NUMBER,
null PO_NUMBER,
null ITEM_NUMBER,
NVL(GJH.CREATION_DATE,GJH.CREATION_DATE),
NVL(GJH.CREATED_BY,GJH.CREATED_BY),

GJH.JE_HEADER_ID header_id,GJL.je_LINE_num line_id,


gjh.je_HEADER_ID||gjl.je_LINE_NUM id_num, to_number(gjh.je_HEADER_ID||
gjl.je_LINE_NUM) GL_SL_LINK_ID
,gjl.ATTRIBUTE7 Insurance_Claim_Number
,NULL INVOICE_DATE
FROM
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
gl_je_batches gjb,
gl_code_combinations gcc,
GL_LEDGERS GL
WHERE
GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
and GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
and GJH.LEDGER_ID=GL.LEDGER_ID
--AND GJH.STATUS='P'
and GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
--and gjh.je_source in ('Manual','Spreadsheet','Receipt
Accounting','300000018954446','300000018954447','300000018454941','Assets','AutoCop
y','Revenue Management','Statistical','Tax','US Federal','Real
Estate','Allocations','AutoCopy','Balance Transfer','Revaluation','Other')
and GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND gl.ledger_id in (:P_Ledger)
and GJH.PERIOD_NAME in (:P_PERIOD_NAME)
and GCC.SEGMENT2 in NVL(:P_COST_CENTER,GCC.SEGMENT2)
and GCC.SEGMENT3 in NVL(:P_NAT_ACCOUNT,GCC.SEGMENT3)
and GCC.SEGMENT4 in NVL(:P_IC,GCC.SEGMENT4)
and GCC.SEGMENT5 in NVL(:P_FUT,GCC.SEGMENT5)

and (select user_je_source_name from gl_je_sources where


je_source_name=GJH.JE_SOURCE) in NVL((:P_JE_SOURCE),(select user_je_source_name
from gl_je_sources where je_source_name=GJH.JE_SOURCE))
UNION
SELECT GL.NAME LEDGER_NAME,
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
GJH.NAME JV_NAME,
GJH.JE_CATEGORY,
(select user_je_source_name from
gl_je_sources where je_source_name=GJH.JE_SOURCE)JE_SOURCE,
GJB.NAME BATCH_NAME,
GJH.PERIOD_NAME,
(SELECT distinct PERIOD_YEAR from GL_PERIODS
where PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_YEAR,
(SELECT distinct PERIOD_NUM from GL_PERIODS where
PERIOD_NAME=GJH.PERIOD_NAME) PERIOD_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5, SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
1,
GCC.SEGMENT1),
1,
40) COMP_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2),
1,
40) CC_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
3,
GCC.SEGMENT3),
1,
40) ACCT_DESC,
SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
4,
GCC.SEGMENT4),
1,
40) INT_DESC,

SUBSTR (

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
5,
GCC.SEGMENT5),
1,
40) FUT_DESC,
NVL(GJL.ACCOUNTED_CR,0) GL_CR,
NVL(GJL.ACCOUNTED_DR,0) GL_DR,
NVL(GJL.ENTERED_CR,0) GL_EN_CR,
NVL(GJL.ENTERED_DR,0) GL_EN_DR,
--- GJL.CURRENCY_CONVERSION_RATE,
nvl((select round(CONVERSION_RATE,5)
from GL_DAILY_RATES gd
where 1=1
--GD.TO_CURRENCY = 'USD'
--- and GD.FROM_CURRENCY ='GBP'
and GD.FROM_CURRENCY =GjL.CURRENCY_CODE
AND
GD.CONVERSION_DATE=GjL.CURRENCY_CONVERSION_DATE ),GJL.CURRENCY_CONVERSION_RATE)
CURRENCY_CONVERSION_RATE,
----
round(NVL(GJL.ACCOUNTED_DR,GJL.ACCOUNTED_CR)/NVL(gjl.ENTERED_DR,GJL.ENTERED_CR),3)C
URRENCY_CONVERSION_RATE
GJL.CURRENCY_CONVERSION_DATE,
NVL(GJL.ACCOUNTED_DR,0)-NVL(GJL.ACCOUNTED_CR,0)
Net,
GJL.CURRENCY_CODE ENT_CUR, gcc.segment1||'-'||
gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 GL_STRING,
GJL.DESCRIPTION JV_LINE_DESCRIPTION,
null line_desc,
null EVENT_TYPE_CODE,
null SLA_DESCRIPTION,
null AE_LINE_NUM,
GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,

null CUSTOMER_SUPPLIER_NAME,
null Supplier_number,
null INVOICE_RECEIPT_PAYMENT_NUMBER,
null project_number,
null ASSET_NUMBER,
null PO_NUMBER,
null ITEM_NUMBER,
NVL(GJH.CREATION_DATE,GJH.CREATION_DATE),
NVL(GJH.CREATED_BY,GJH.CREATED_BY),

GJH.JE_HEADER_ID header_id,GJL.je_LINE_num line_id,


gjh.je_HEADER_ID||gjl.je_LINE_NUM id_num, to_number(gjh.je_HEADER_ID||
gjl.je_LINE_NUM) GL_SL_LINK_ID
,gjl.ATTRIBUTE7 Insurance_Claim_Number
,NULL INVOICE_DATE
FROM
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
gl_je_batches gjb,
gl_code_combinations gcc,
GL_LEDGERS GL
WHERE
GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
and GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
and GJH.LEDGER_ID=GL.LEDGER_ID
-- AND GJH.STATUS='P'
and GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
and gjh.je_source in ('Payables','Receivables',
'Purchasing','Project Accounting','Cash Management','Cost
Accounting')
and NVL((select distinct 1 from gl_import_references a,
xla_ae_lines b where a.je_header_id=gjh.je_header_id and a.GL_SL_LINK_ID =
b.GL_SL_LINK_ID and je_header_id=gjh.je_header_id ),2)=2
and GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND gl.ledger_id in (:P_Ledger)
and GJH.PERIOD_NAME in (:P_PERIOD_NAME)
and GCC.SEGMENT2 in NVL(:P_COST_CENTER,GCC.SEGMENT2)
and GCC.SEGMENT3 in NVL(:P_NAT_ACCOUNT,GCC.SEGMENT3)
and GCC.SEGMENT4 in NVL(:P_IC,GCC.SEGMENT4)
and GCC.SEGMENT5 in NVL(:P_FUT,GCC.SEGMENT5)
and (select user_je_source_name from gl_je_sources where
je_source_name=GJH.JE_SOURCE) in NVL((:P_JE_SOURCE),(select user_je_source_name
from gl_je_sources where je_source_name=GJH.JE_SOURCE))

You might also like