Scribd
Upload
824 views16 pages

Fusion CE

This query retrieves unreconciled transaction lines from bank statements, accounts payable, and accounts receivable for reconciliation. It first selects unreconciled lines from bank statements, joining statement lines to headers and lookups. It then unions this with a subquery selecting unreconciled payables journals, joining transactions tables to checks, journals, and code combinations. Finally, it unions this with a subquery selecting unreconciled receivables lines, joining receipts to journals and lookups. The results are consolidated to populate an interface for reconciliation.

Uploaded by

bhush
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
824 views16 pages

Fusion CE

This query retrieves unreconciled transaction lines from bank statements, accounts payable, and accounts receivable for reconciliation. It first selects unreconciled lines from bank statements, joining statement lines to headers and lookups. It then unions this with a subquery selecting unreconciled payables journals, joining transactions tables to checks, journals, and code combinations. Finally, it unions this with a subquery selecting unreconciled receivables lines, joining receipts to journals and lookups. The results are consolidated to populate an interface for reconciliation.

Uploaded by

bhush
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/ 16

with ce_cash_segments as (

select bank_account_id, regexp_substr(CASH_CCID_FIXED_SEGMENTS,'[^,]+', 1,


level) cash_segment
from (select bank_account_id, CASH_CCID_FIXED_SEGMENTS from
ce_bank_accounts where bank_account_id = :P_BANK_ACCOUNT_ID and
multi_cash_recon_enabled_flag = 'Y')
connect by regexp_substr(CASH_CCID_FIXED_SEGMENTS,'[^,]+', 1, level) is
not null)
SELECT KFS.PROMPT AS SEGMENT_CODE
, case
when ccs.CASH_SEGMENT = 'SEGMENT1' then glcc.SEGMENT1
when ccs.CASH_SEGMENT = 'SEGMENT2' then glcc.SEGMENT2
when ccs.CASH_SEGMENT = 'SEGMENT3' then glcc.SEGMENT3
when ccs.CASH_SEGMENT = 'SEGMENT4' then glcc.SEGMENT4
when ccs.CASH_SEGMENT = 'SEGMENT5' then glcc.SEGMENT5
when ccs.CASH_SEGMENT = 'SEGMENT6' then glcc.SEGMENT6
when ccs.CASH_SEGMENT = 'SEGMENT7' then glcc.SEGMENT7
when ccs.CASH_SEGMENT = 'SEGMENT8' then glcc.SEGMENT8
when ccs.CASH_SEGMENT = 'SEGMENT9' then glcc.SEGMENT9
when ccs.CASH_SEGMENT = 'SEGMENT10' then glcc.SEGMENT10
when ccs.CASH_SEGMENT = 'SEGMENT11' then glcc.SEGMENT11
when ccs.CASH_SEGMENT = 'SEGMENT12' then glcc.SEGMENT12
when ccs.CASH_SEGMENT = 'SEGMENT13' then glcc.SEGMENT13
when ccs.CASH_SEGMENT = 'SEGMENT14' then glcc.SEGMENT14
when ccs.CASH_SEGMENT = 'SEGMENT15' then glcc.SEGMENT15
when ccs.CASH_SEGMENT = 'SEGMENT16' then glcc.SEGMENT16
when ccs.CASH_SEGMENT = 'SEGMENT17' then glcc.SEGMENT17
when ccs.CASH_SEGMENT = 'SEGMENT18' then glcc.SEGMENT18
when ccs.CASH_SEGMENT = 'SEGMENT19' then glcc.SEGMENT19
when ccs.CASH_SEGMENT = 'SEGMENT20' then glcc.SEGMENT20
when ccs.CASH_SEGMENT = 'SEGMENT21' then glcc.SEGMENT21
when ccs.CASH_SEGMENT = 'SEGMENT22' then glcc.SEGMENT22
when ccs.CASH_SEGMENT = 'SEGMENT23' then glcc.SEGMENT23
when ccs.CASH_SEGMENT = 'SEGMENT24' then glcc.SEGMENT24
when ccs.CASH_SEGMENT = 'SEGMENT25' then glcc.SEGMENT25
when ccs.CASH_SEGMENT = 'SEGMENT26' then glcc.SEGMENT26
when ccs.CASH_SEGMENT = 'SEGMENT27' then glcc.SEGMENT27
when ccs.CASH_SEGMENT = 'SEGMENT28' then glcc.SEGMENT28
when ccs.CASH_SEGMENT = 'SEGMENT29' then glcc.SEGMENT29
when ccs.CASH_SEGMENT = 'SEGMENT30' then glcc.SEGMENT30
END as segment_value,
KFS.SEQUENCE_NUMBER as seq
FROM FND_KF_STR_INSTANCES_B KFSTRI
, FND_KF_SEGMENTS_VL KFS
, ce_bank_accounts cba
, gl_code_combinations glcc
, ce_cash_segments ccs
WHERE glcc.code_combination_id = cba.asset_code_combination_id
AND KFSTRI.APPLICATION_ID = 101
AND KFSTRI.STRUCTURE_INSTANCE_NUMBER = glcc.CHART_OF_ACCOUNTS_ID
AND KFSTRI.STRUCTURE_ID = KFS.STRUCTURE_ID
and KFS.COLUMN_NAME = ccs.CASH_SEGMENT
AND ccs.bank_account_id = CBA.BANK_ACCOUNT_ID
AND cba.bank_account_id = :P_BANK_ACCOUNT_ID
union all
select 'CASH_ACCOUNT' segment_code,
fnd_flex_ext.get_segs('GL','GL#',glcc.chart_of_accounts_id,glcc.code_combination_id
) segment_value, 1 as seq
from ce_bank_accounts cba, gl_code_combinations glcc
where cba.multi_cash_recon_enabled_flag = 'N'
and cba.asset_code_combination_id = glcc.code_combination_id
and cba.bank_account_id = :P_BANK_ACCOUNT_ID
order by seq

SELECT SUM(CLOSE_BAL_BOOK) CLOSE_BAL_BOOK FROM(SELECT


NVL(SUM((nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0))-
(nvl(glb.begin_balance_cr,0) + nvl(glb.period_net_cr,0))),0) CLOSE_BAL_BOOK
FROM gl_balances glb , ce_internal_bank_accts_v acct
WHERE acct.multi_cash_recon_enabled_flag = 'N'
AND glb.code_combination_id = acct.asset_code_combination_id
AND glb.period_name = :P_ACC_PERIOD_TO
AND glb.actual_flag = 'A'
AND glb.currency_code = decode(:gc_bank_type, 'FBANK', :gc_currency_code,
:gc_ledger_currency_code)
AND glb.ledger_id = :GC_SET_OF_BOOKS_ID
AND NVL(glb.TRANSLATED_FLAG,'R') = 'R'
AND ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID

UNION ALL

SELECT NVL(SUM((nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0))-


(nvl(glb.begin_balance_cr,0) + nvl(glb.period_net_cr,0))),0) CLOSE_BAL_BOOK
FROM gl_balances glb , ce_internal_bank_accts_v acct,
fusion.ce_bank_account_cash_ccids ce_ccid
WHERE acct.multi_cash_recon_enabled_flag = 'Y'
AND acct.bank_account_id = ce_ccid.bank_account_id
AND glb.code_combination_id = ce_ccid.cash_ccid
AND glb.period_name = :P_ACC_PERIOD_TO
AND glb.actual_flag = 'A'
AND glb.currency_code = decode(:gc_bank_type, 'FBANK', :gc_currency_code,
:gc_ledger_currency_code)
AND glb.ledger_id = :GC_SET_OF_BOOKS_ID
AND NVL(glb.TRANSLATED_FLAG,'R') = 'R'
AND ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID) CB_BAL

-- This Query populates Close Balance for Bank Transactions


SELECT balance_amount CLOSE_BAL_BANK from (
SELECT
FIRST_VALUE(csb1.balance_amount) OVER (order by balance_date desc,
csb1.creation_date desc) as balance_amount
FROM
fusion.ce_stmt_balances csb1,
fusion.ce_statement_headers csh
WHERE
csh.bank_account_id = :p_bank_account_id
AND csh.statement_header_id = csb1.statement_header_id
AND nvl(csh.intraday_flag,'N') <> 'Y'
AND trunc(csb1.balance_date) BETWEEN :gc_from_date AND :gc_to_date
AND csb1.balance_code = 'CLBD') where rownum = 1

--------------------------

TO_CHAR(SL.BOOKING_DATE,'YYYY-MM-DD') TRX_DATE,
0 BOOKS,
DECODE(SL.FLOW_INDICATOR, 'DBIT', -1 * SL.AMOUNT, SL.AMOUNT) BANK, /* based
in input from Hillary in bug 9498432*/
'Line ' || to_char(SL.LINE_NUMBER) || ',' || SH.statement_number
STATEMENT_NUMBER, -- bug#9950271 based on shaik/xin resp,
NULL JOURNAL_NAME,
NULL BATCH_NAME
FROM
CE_STATEMENT_LINES SL ,
CE_STATEMENT_HEADERS SH,
CE_LOOKUPS CEL ,/* ssumaith - bug 9498432*/
CE_LOOKUPS CEL1
WHERE SL.STATEMENT_HEADER_ID = SH.STATEMENT_HEADER_ID
AND RECON_STATUS = 'UNR'
AND CEL.lookup_type = 'CE_TRX_TYPE' /* ssumaith - bug 9498432*/
AND CEL.lookup_code = SL.TRX_TYPE /* ssumaith - bug 9498432*/
AND CEL1.lookup_type = 'CE_UNRECON_SOURCE' /* ssumaith - bug 9498432*/
AND CEL1.lookup_code = 'BS' /* ssumaith - bug 9498432*/
AND SH.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND TRUNC(SL.BOOKING_DATE) BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
and nvl(sh.intraday_flag, 'N') <> 'Y'

UNION ALL

-- payables non-reconciled journals


SELECT DISTINCT CEL1.Meaning ORIGIN ,
CEL.MEANING TRX_TYPE,
to_char(AC.CHECK_NUMBER) DOC_NUMBER,
TO_CHAR(NVL(AC.CLEARED_DATE, AC.CHECK_DATE),'YYYY-MM-DD') TRX_DATE,
-- -1*CLEARED_AMOUNT BOOKS,
--NVL(GJL.ENTERED_DR,0)- NVL(GJL.ENTERED_cR ,0) BOOKS, --bug 14539897 replace
with ACCOUNTED_DR/CR
--NVL(GJL.ACCOUNTED_DR,0)- NVL(GJL.ACCOUNTED_cR ,0) BOOKS, --bug 15858278
replace GL with XLA,
--NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0) BOOKS, -- bug 22550369
decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,
0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) BOOKS,
0 BANK,
'Line ' || GJL.je_Line_num ||',' ||GJH.Name||','||GJB.NAME STATEMENT_NUMBER,
GJH.NAME JOURNAL_NAME,
GJB.NAME BATCH_NAME
FROM GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_JE_BATCHES GJB,
GL_LEDGERS GL,
XLA_AE_HEADERS XAH ,
XLA_AE_LINES XAL,
AP_CHECKS_ALL AC,
XLA_TRANSACTION_ENTITIES TRX,
XLA_EVENTS XE ,
CE_INTERNAL_BANK_ACCTS_V ACCT,
GL_IMPORT_REFERENCES GLIR,
CE_LOOKUPS CEL,
CE_LOOKUPS CEL1,
CE_TRX_TYPE_MAPPING CE_TRX,
CE_BANK_ACCT_USES_ALL CE_BAU
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.JE_SOURCE = 'Payables'
AND GJL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
--AND XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID -- ssumaith - bug9498432 added
following 3 joins
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
and xah.application_id = 200 -- bug 15858278 ADDED
and xah.application_id = xal.application_id
and xal.gl_sl_link_table = GLIR.GL_SL_LINK_table
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJB.STATUS = 'P' -- bug 15858278 ADDED
--AND TRX.SOURCE_ID_INT_1 = AC.CHECK_ID --bug 25027655
AND NVL(TRX.SOURCE_ID_INT_1, -99) = AC.CHECK_ID
AND XE.ENTITY_ID = TRX.ENTITY_ID
AND XAH.EVENT_ID = XE.EVENT_ID
AND TRX.APPLICATION_ID =200
AND AC.RECON_FLAG = 'N'
AND ac.status_lookup_code in ('NEGOTIABLE', 'CLEARED') -- bug 22499351 exclude
Voided trx
AND TRX.APPLICATION_ID = XE.APPLICATION_ID
AND TRX.ENTITY_CODE = 'AP_PAYMENTS'
AND XAL.GL_SL_LINK_TABLE IN ('XLAJEL')
--AND GJL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE -- bug 15968568
replace with XE.EVENT_DATE
AND XE.EVENT_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
and ACCT.BANK_ACCOUNT_ID = CE_BAU.BANK_ACCOUNT_ID
AND GJL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID
AND ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND CE_BAU.ORG_ID = AC.ORG_ID
AND CE_BAU.BANK_ACCT_USE_ID = AC.CE_BANK_ACCT_USE_ID
AND CE_BAU.AP_USE_ENABLE_FLAG = 'Y'
AND CEL1.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL1.lookup_code = 'AP'
AND CEL.lookup_type(+) = 'CE_TRX_TYPE'
and nvl(ce_trx.active_flag (+), 'Y') = 'Y'
AND CE_TRX.trx_type =CEL.LOOKUP_CODE(+)
AND CE_TRX.PMT_RCT_METHOD(+)=ac.payment_method_code

UNION ALL

-- GETTING UNRECONCILED LINES FROM RECEIVABLES


SELECT DISTINCT CEL1.Meaning ORIGIN ,
CEL.Meaning TRX_TYPE ,
to_char(CR.RECEIPT_NUMBER) DOC_NUMBER,
TO_CHAR(NVL(CR.DEPOSIT_DATE, CR.RECEIPT_DATE),'YYYY-MM-DD') TRX_DATE,
--CR.AMOUNT BOOKS,
--NVL(GJL.ENTERED_DR,0)- NVL(GJL.ENTERED_cR ,0) BOOKS,--bug 14539897 replace
with ACCOUNTED_DR/CR
--NVL(GJL.ACCOUNTED_DR,0)- NVL(GJL.ACCOUNTED_cR ,0) BOOKS, --bug 15858278
replace GL with XLA,
--NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0) BOOKS, -- bug 22550369
decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,
0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) BOOKS,
0 BANK,
'Line ' || GJL.je_Line_num ||',' ||GJH.Name||','||GJB.NAME
STATEMENT_NUMBER,
GJH.NAME JOURNAL_NAME,
GJB.NAME BATCH_NAME
FROM
GL_JE_LINES GJL
, GL_JE_HEADERS GJH
, GL_JE_BATCHES GJB
, AR_DISTRIBUTIONS_ALL ARD
, XLA_DISTRIBUTION_LINKS XDL
, XLA_AE_LINES XAL
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_CASH_RECEIPTS_ALL CR
, XLA_AE_HEADERS XAH
, CE_TRX_TYPE_MAPPING CTTM
, CE_INTERNAL_BANK_ACCTS_V ACCT
, GL_IMPORT_REFERENCES GLIR
, CE_LOOKUPS CEL
, CE_LOOKUPS CEL1
, GL_LEDGERS GL
, CE_BANK_ACCT_USES_ALL CE_BAU
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.JE_SOURCE = 'Receivables'
AND GJL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
--AND XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID -- ssumaith - bug9498432 added
following 3 joins
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
and xah.application_id = 222 -- bug 15858278 ADDED
and xah.application_id = xal.application_id
and xal.gl_sl_link_table = GLIR.GL_SL_LINK_table
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJB.STATUS = 'P' -- bug 15858278 ADDED
AND ARD.SOURCE_TABLE = 'CRH'
AND ARD.SOURCE_ID = CRH.CASH_RECEIPT_HISTORY_ID
AND ARD.LINE_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XDL.APPLICATION_ID = 222
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'
AND XAL.APPLICATION_ID = 222
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND CTTM.PMT_RCT_METHOD(+) = TO_CHAR(CR.RECEIPT_METHOD_ID)
AND CTTM.MAPPING_TYPE(+)='RECEIPT'
and nvl(CTTM.active_flag (+), 'Y') = 'Y'
AND crh.status in ('REMITTED', 'CLEARED', 'RISK_ELIMINATED') -- bug 22499351
exclude Voided trx
and not exists (select 1 from ar_cash_receipt_history_all crh1 where crh1.status =
'REVERSED' and crh1.current_record_flag = 'Y' and crh.cash_receipt_id =
crh1.cash_receipt_id)
AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CR.SET_OF_BOOKS_ID = XAH.LEDGER_ID
AND CRH.EVENT_ID IS NOT NULL
AND CR.RECON_FLAG = 'N'
AND GJL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID
and CE_BAU.ORG_ID = CR.ORG_ID
and CE_BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID
and CE_BAU.AR_USE_ENABLE_FLAG = 'Y'
and ACCT.BANK_ACCOUNT_ID = CE_BAU.BANK_ACCOUNT_ID
AND ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
--AND GJL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE --bug 15968568
replace with XAL.ACCOUNTING_DATE
AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND CEL.lookup_type (+)= 'CE_TRX_TYPE' /* ssumaith this join and next join -
bug 9498432*/
AND CEL.lookup_code (+)= CTTM.TRX_TYPE
AND CEL1.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL1.lookup_code = 'AR'

UNION ALL

-- UNRECONCILED PAYROLL TRX


SELECT DISTINCT CEL.Meaning ORIGIN ,
CEL.MEANING TRX_TYPE,
to_char(CE_PR.CHECK_NUMBER) DOC_NUMBER,
TO_CHAR(NVL(CE_PR.CLEARED_DATE, CE_PR.PAYMENT_DATE),'YYYY-MM-DD') TRX_DATE,
-- -1*AMOUNT BOOKS,
--NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0) BOOKS, --bug 22550369
decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,
0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) BOOKS,
0 BANK,
/* CE_PR.CHECK_NUMBER STATEMENT_NUMBER,
NULL JOURNAL_NAME,
NULL BATCH_NAME */
'Line ' || GJL.je_Line_num ||',' ||GJH.Name||','||GJB.NAME STATEMENT_NUMBER,
GJH.NAME JOURNAL_NAME,
GJB.NAME BATCH_NAME
FROM PAY_CE_TRANSACTIONS CE_PR,
CE_BANK_ACCOUNTS_PAY_V CE_BA,
CE_TRX_TYPE_MAPPING CTTM,
--CE_INTERNAL_BANK_ACCTS_V ACCT, --bug 15858278 removed
GL_IMPORT_REFERENCES GLIR,--bug 15858278 7 tables added
GL_JE_LINES GJL,
GL_JE_HEADERS GJH,
GL_JE_BATCHES GJB,
GL_LEDGERS GL,
xla_ae_headers xah,
XLA_AE_LINES XAL,
pay_xla_events pe,
CE_LOOKUPS CEL
WHERE CE_PR.RECON_FLAG = 'N'
AND CE_PR.PAYMENT_STATUS = 'PAID'
AND CE_BA.BANK_ACCOUNT_ID = CE_PR.PAYER_BANK_ACCOUNT_ID

--AND TRUNC(CE_PR.PAYMENT_DATE) BETWEEN :GC_FROM_DATE AND :GC_TO_DATE --


replaced with GJL.EFFECTIVE_DATE
--AND GJL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE --bug 15968568
replace with XAL.ACCOUNTING_DATE
AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND CTTM.PAY_PAYMENT_TYPE_ID(+) = CE_PR.PAYMENT_TYPE_ID
and nvl(CTTM.active_flag (+), 'Y') = 'Y'
AND CE_BA.ASSET_CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
--AND CE_BA.ASSET_CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID
--AND ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID -- replaced with
CE_BA.BANK_ACCOUNT_ID
AND CE_BA.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND CEL.lookup_type = 'CE_UNRECON_SOURCE' /* ssumaith this join and next join -
bug 9498432*/
AND CEL.lookup_code = 'PR'
and XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
and xah.application_id = 801
and xah.application_id = xal.application_id
and xal.gl_sl_link_table = GLIR.GL_SL_LINK_table
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM
and GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJB.STATUS = 'P'
AND GJL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
and xah.gl_transfer_status_code = 'Y'
and pe.event_id = xah.event_id
and exists
(select 1
from pay_payroll_rel_actions ra,
pay_action_interlocks int1,
pay_action_interlocks int2,
pay_action_interlocks int3
where ce_pr.pre_payment_id = ra.pre_payment_id
and int1.locked_action_id = ra.payroll_rel_action_id
and int2.locked_action_id = int1.locking_action_id
and int2.locking_action_id = pe.payroll_rel_action_id
and int3.locked_action_id = ra.payroll_rel_action_id
and ce_pr.payroll_rel_action_id = int3.locking_action_id
)

UNION ALL
-- CE_EXTERNAL_TRANSACTIONS
SELECT CEL.Meaning ORIGIN ,
CEL1.Meaning TRX_TYPE ,
TO_CHAR(EXT.REFERENCE_TEXT) DOC_NUMBER,
TO_CHAR(EXT.TRANSACTION_DATE,'YYYY-MM-DD') TRX_DATE ,
--NVL(-GJL.ENTERED_DR, GJL.ENTERED_CR) BOOKS ,
--NVL(GJL.ENTERED_DR,0)- NVL(GJL.ENTERED_cR ,0) BOOKS, --bug 14539897
replace with ACCOUNTED_DR/CR
--NVL(GJL.ACCOUNTED_DR,0)- NVL(GJL.ACCOUNTED_cR ,0) BOOKS,--bug 15858278
replace GL with XLA,
--NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0) BOOKS,-- bug 22550369
decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,
0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) BOOKS,
0 BANK ,
'Line ' || GJL.je_Line_num ||',' ||GJH.Name||','||GJB.NAME
STATEMENT_NUMBER,
GJH.NAME JOURNAL_NAME,
GJB.NAME BATCH_NAME
FROM GL_JE_HEADERS GJH ,
GL_JE_LINES GJL ,
GL_JE_BATCHES GJB ,
GL_LEDGERS GL,
XLA_AE_HEADERS XAH ,
XLA_AE_LINES XAL ,
CE_EXTERNAL_TRANSACTIONS EXT,
XLA_TRANSACTION_ENTITIES TRX,
XLA_EVENTS XE,
CE_INTERNAL_BANK_ACCTS_V ACCT,
GL_IMPORT_REFERENCES GLIR ,
CE_LOOKUPS CEL,
CE_LOOKUPS CEL1
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.JE_SOURCE = 'Cash Management'
AND GJL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
--AND XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID -- ssumaith - bug9498432 added
following 3 joins
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJB.STATUS = 'P' -- bug 15858278 ADDED
AND NVL(TRX.SOURCE_ID_INT_1, -99) = EXT.TRANSACTION_ID
AND GL.LEDGER_ID = TRX.LEDGER_ID
AND TRX.SOURCE_ID_INT_1 = EXT.TRANSACTION_ID
AND XE.ENTITY_ID = TRX.ENTITY_ID
AND XAH.EVENT_ID = XE.EVENT_ID
AND TRX.APPLICATION_ID =260
AND EXT.STATUS = 'UNR'
AND TRX.APPLICATION_ID = XE.APPLICATION_ID
AND TRX.APPLICATION_ID = XAL.APPLICATION_ID
AND TRX.APPLICATION_ID = XAH.APPLICATION_ID
AND TRX.ENTITY_CODE = 'CE_EXTERNAL'
AND CEL.lookup_type = 'CE_UNRECON_SOURCE' AND CEL.Lookup_code = 'XT'

AND CEL1.Lookup_type(+) = 'CE_TRX_TYPE' AND CEL1.lookup_code(+) =


EXT.TRANSACTION_TYPE
AND XAL.GL_SL_LINK_TABLE IN ('XLAJEL')
-- AND GJL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE --bug 15968568
replace with XE.EVENT_DATE
AND XE.EVENT_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND GJL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID
AND ACCT.BANK_ACCOUNT_ID = EXT.BANK_ACCOUNT_ID
AND ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID

UNION ALL
-- manual journal in GL single cash ccid
SELECT CEL.MEANING ORIGIN ,
NULL TRX_TYPE ,
GLH.NAME DOC_NUMBER,
TO_CHAR(GLL.EFFECTIVE_DATE,'YYYY-MM-DD') TRX_DATE ,
--NVL(GLL.ENTERED_DR,0)- NVL(GLL.ENTERED_cR ,0) BOOKS,--bug 14539897 replace
with ACCOUNTED_DR/CR
--NVL(GLL.ACCOUNTED_DR,0)- NVL(GLL.ACCOUNTED_cR ,0) BOOKS, -- bug 22550369
decode(:gc_bank_type, 'FBANK', (NVL(GLL.ENTERED_DR,0)- NVL(GLL.ENTERED_CR ,
0)), (NVL(GLL.ACCOUNTED_DR,0)- NVL(GLL.ACCOUNTED_CR ,0))) BOOKS,
0 BANK ,
'Line ' || GLL.je_Line_num ||',' ||GLH.Name||','||GJB.NAME
STATEMENT_NUMBER,
GLH.NAME JOURNAL_NAME,
GJB.NAME BATCH_NAME
FROM GL_JE_HEADERS GLH,
GL_JE_LINES GLL,
GL_JE_BATCHES GJB,
GL_LEDGERS GL,
CE_INTERNAL_BANK_ACCTS_V CE,
CE_LOOKUPS CEL
WHERE
GLL.CODE_COMBINATION_ID = CE.ASSET_CODE_COMBINATION_ID
AND CE.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND CE.MULTI_CASH_RECON_ENABLED_FLAG = 'N'
AND GLL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND GLH.JE_SOURCE NOT IN ('Cash
Management','Receivables','Payables','Revaluation','Payroll')
AND GJB.JE_BATCH_ID = GLH.JE_BATCH_ID
AND GLL.JE_HEADER_ID = GLH.JE_HEADER_ID
AND GLL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND CEL.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL.lookup_code = 'GL'
AND GJB.STATUS = 'P'
AND ((GLH.JE_FROM_SLA_FLAG is NULL) or (GLH.JE_FROM_SLA_FLAG='N'))
AND GLL.CURRENCY_CODE = decode(CE.CURRENCY_CODE, GL.CURRENCY_CODE,
GLL.CURRENCY_CODE, CE.CURRENCY_CODE) --bug 30231283 ADDED
/* bug 22635379, filtering out the journals which are reconciled. Journal
reconciliation is enabled by fin-514 project. */
AND NOT EXISTS
(
SELECT 1
FROM CE_RECON_HISTORY_ITEMS CRHI
WHERE CRHI.SOURCE_ID = GLL.JE_HEADER_ID
AND CRHI.SOURCE_LINE_ID = GLL.JE_LINE_NUM
AND CRHI.RECON_SOURCE = 'ORA_GL'
AND CRHI.CLEARED_DATE <= :GC_TO_DATE
)

UNION ALL
-- manual journal in XLA
SELECT CEL.MEANING ORIGIN ,
NULL TRX_TYPE,
XAH.DESCRIPTION DOC_NUMBER, -- GLH.NAME DOC_NUMBER,
TO_CHAR(XAL.ACCOUNTING_DATE,'YYYY-MM-DD') TRX_DATE,-- GLL.EFFECTIVE_DATE
TRX_DATE,
--NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR ,0) BOOKS,-- bug 22550369
decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,
0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) BOOKS,
0 BANK,
'Line ' || XAL.AE_LINE_NUM ||',' ||XAH.DESCRIPTION||','||GJB.NAME
STATEMENT_NUMBER,
XAH.DESCRIPTION JOURNAL_NAME,
GJB.NAME BATCH_NAME
FROM GL_JE_BATCHES GJB,
GL_IMPORT_REFERENCES GLIR,
XLA_AE_HEADERS XAH ,
XLA_AE_LINES XAL,
GL_LEDGERS GL,
CE_INTERNAL_BANK_ACCTS_V CE,
CE_LOOKUPS CEL
WHERE
xal.CODE_COMBINATION_ID = CE.ASSET_CODE_COMBINATION_ID
AND CE.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND CE.MULTI_CASH_RECON_ENABLED_FLAG = 'N'
AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
and xah.application_id = xal.application_id
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
and XAL.gl_sl_link_table = GLIR.GL_SL_LINK_table
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND CEL.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL.lookup_code = 'GL'
AND GJB.STATUS = 'P'
AND xah.event_type_code = 'MANUAL'

UNION ALL

-- manual journal in GL multi cash ccid


SELECT CEL.MEANING ORIGIN ,
NULL TRX_TYPE ,
GLH.NAME DOC_NUMBER,
TO_CHAR(GLL.EFFECTIVE_DATE,'YYYY-MM-DD') TRX_DATE ,
--NVL(GLL.ENTERED_DR,0)- NVL(GLL.ENTERED_cR ,0) BOOKS,--bug 14539897 replace
with ACCOUNTED_DR/CR
--NVL(GLL.ACCOUNTED_DR,0)- NVL(GLL.ACCOUNTED_cR ,0) BOOKS, -- bug 22550369
decode(:gc_bank_type, 'FBANK', (NVL(GLL.ENTERED_DR,0)- NVL(GLL.ENTERED_CR ,
0)), (NVL(GLL.ACCOUNTED_DR,0)- NVL(GLL.ACCOUNTED_CR ,0))) BOOKS,
0 BANK ,
'Line ' || GLL.je_Line_num ||',' ||GLH.Name||','||GJB.NAME
STATEMENT_NUMBER,
GLH.NAME JOURNAL_NAME,
GJB.NAME BATCH_NAME
FROM GL_JE_HEADERS GLH,
GL_JE_LINES GLL,
GL_JE_BATCHES GJB,
GL_LEDGERS GL,
CE_INTERNAL_BANK_ACCTS_V CE,
CE_LOOKUPS CEL,
CE_BANK_ACCOUNT_CASH_CCIDS CE_CCID
WHERE
CE.BANK_ACCOUNT_ID = CE_CCID.BANK_ACCOUNT_ID
AND GLL.CODE_COMBINATION_ID = CE_CCID.CASH_CCID
AND CE.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND CE.MULTI_CASH_RECON_ENABLED_FLAG = 'Y'
AND GLL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND GLH.JE_SOURCE NOT IN ('Cash
Management','Receivables','Payables','Revaluation','Payroll')
AND GJB.JE_BATCH_ID = GLH.JE_BATCH_ID
AND GLL.JE_HEADER_ID = GLH.JE_HEADER_ID
AND GLL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND CEL.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL.lookup_code = 'GL'
AND GJB.STATUS = 'P'
AND ((GLH.JE_FROM_SLA_FLAG is NULL) or (GLH.JE_FROM_SLA_FLAG='N'))
AND GLL.CURRENCY_CODE = decode(CE.CURRENCY_CODE, GL.CURRENCY_CODE,
GLL.CURRENCY_CODE, CE.CURRENCY_CODE) --bug 30231283 ADDED
/* bug 22635379, filtering out the journals which are reconciled. Journal
reconciliation is enabled by fin-514 project. */
AND NOT EXISTS
(
SELECT 1
FROM CE_RECON_HISTORY_ITEMS CRHI
WHERE CRHI.SOURCE_ID = GLL.JE_HEADER_ID
AND CRHI.SOURCE_LINE_ID = GLL.JE_LINE_NUM
AND CRHI.RECON_SOURCE = 'ORA_GL'
AND CRHI.CLEARED_DATE <= :GC_TO_DATE
)

UNION ALL
-- manual journal in XLA with multi cash ccids
SELECT CEL.MEANING ORIGIN ,
NULL TRX_TYPE,
XAH.DESCRIPTION DOC_NUMBER, -- GLH.NAME DOC_NUMBER,
TO_CHAR(XAL.ACCOUNTING_DATE,'YYYY-MM-DD') TRX_DATE,-- GLL.EFFECTIVE_DATE
TRX_DATE,
--NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR ,0) BOOKS,-- bug 22550369
decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,
0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) BOOKS,
0 BANK,
'Line ' || XAL.AE_LINE_NUM ||',' ||XAH.DESCRIPTION||','||GJB.NAME
STATEMENT_NUMBER,
XAH.DESCRIPTION JOURNAL_NAME,
GJB.NAME BATCH_NAME
FROM GL_JE_BATCHES GJB,
GL_IMPORT_REFERENCES GLIR,
XLA_AE_HEADERS XAH ,
XLA_AE_LINES XAL,
GL_LEDGERS GL,
CE_INTERNAL_BANK_ACCTS_V CE,
CE_LOOKUPS CEL,
CE_BANK_ACCOUNT_CASH_CCIDS CE_CCID
WHERE
CE.BANK_ACCOUNT_ID = CE_CCID.BANK_ACCOUNT_ID
AND xal.CODE_COMBINATION_ID = CE_CCID.CASH_CCID
AND CE.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND CE.MULTI_CASH_RECON_ENABLED_FLAG = 'Y'
AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
and xah.application_id = xal.application_id
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
and XAL.gl_sl_link_table = GLIR.GL_SL_LINK_table
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND CEL.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL.lookup_code = 'GL'
AND GJB.STATUS = 'P'
AND xah.event_type_code = 'MANUAL'

-- This Query populates Detail Information of Unreconciled Bank and Book


Transactions.
-- GETTING UNRECONCILED LINES FROM BANK STATEMENT
SELECT CEL.Meaning ORIGIN,
SL.TRX_TYPE TRX_TYPE,
SL.RECON_REFERENCE DOC_NUMBER,
TO_CHAR(SL.BOOKING_DATE,'YYYY-MM-DD') TRX_DATE,
0 ENTERED_BOOKS,
0 ACCOUNTED_BOOKS,
DECODE(SL.FLOW_INDICATOR, 'DBIT', -1 * SL.AMOUNT *
CE_CEXRECRE_XMLP_PKG.currency_conversion (:P_SET_OF_BOOKS_ID, sh.currency_code ,
sl.booking_date, :gc_conversion_type)
, SL.AMOUNT*
CE_CEXRECRE_XMLP_PKG.currency_conversion (:P_SET_OF_BOOKS_ID, sh.currency_code ,
sl.booking_date, :gc_conversion_type) ) BANK,
to_char(SL.LINE_NUMBER) STATEMENT_NUMBER
FROM
CE_STATEMENT_LINES SL ,
CE_STATEMENT_HEADERS SH,
CE_LOOKUPS CEL
WHERE SL.STATEMENT_HEADER_ID = SH.STATEMENT_HEADER_ID
AND RECON_STATUS = 'UNR'
AND SH.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND TRUNC(SL.BOOKING_DATE) BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND CEL.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL.lookup_code = 'BS'
UNION ALL
-- payables non-reconciled journals
SELECT DISTINCT CEL.Meaning ORIGIN ,
CTTM.TRX_TYPE TRX_TYPE,
to_char(AC.CHECK_ID) DOC_NUMBER,
TO_CHAR(NVL(AC.CLEARED_DATE, AC.CHECK_DATE),'YYYY-MM-DD') TRX_DATE,
-- GJL.ENTERED_CR ENTERED_BOOKS, --bug 15858278 replace GL with XLA,
-- -1*GJL.ACCOUNTED_CR ACCOUNTED_BOOKS,--bug 15858278 replace GL with XLA,
XAL.ENTERED_CR ENTERED_BOOKS,
-1*XAL.ACCOUNTED_CR ACCOUNTED_BOOKS,
0 BANK,
to_char(AC.CHECK_NUMBER) STATEMENT_NUMBER
FROM GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_JE_BATCHES GJB ,
GL_LEDGERS GL,
GL_IMPORT_REFERENCES GLIR,
XLA_AE_HEADERS XAH ,
XLA_AE_LINES XAL,
AP_CHECKS AC,
CE_TRX_TYPE_MAPPING CTTM ,
XLA_TRANSACTION_ENTITIES TRX,
XLA_EVENTS XE,
CE_LOOKUPS CEL
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.JE_SOURCE = 'Payables'
AND GJL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID -- bug 15858278 ADDED
AND GJB.STATUS = 'P' -- bug 15858278 ADDED
--AND GJL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE --bug 15968568
replace with XE.EVENT_DATE
AND XE.EVENT_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
and xah.application_id = 200 -- bug 15858278 ADDED
and xah.application_id = xal.application_id
and xal.gl_sl_link_table = GLIR.GL_SL_LINK_table
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND TRX.SOURCE_ID_INT_1 = AC.CHECK_ID
AND XE.ENTITY_ID = TRX.ENTITY_ID
AND XAH.EVENT_ID = XE.EVENT_ID
AND AC.PAYMENT_METHOD_LOOKUP_CODE = CTTM.PMT_RCT_METHOD
AND TRX.APPLICATION_ID =200
AND AC.RECON_FLAG = 'N'
AND TRX.APPLICATION_ID = XE.APPLICATION_ID
AND TRX.ENTITY_CODE = 'AP_PAYMENTS'
AND XAL.GL_SL_LINK_TABLE IN ('XLAJEL')
AND CEL.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL.lookup_code = 'AP'
--AND TRUNC(AC.CHECK_DATE) BETWEEN :GC_FROM_DATE AND :GC_TO_DATE -- bug 15858278
replaced with GJL.EFFECTIVE_DATE
AND gjl.&p_where
UNION ALL
-- GETTING UNRECONCILED LINES FROM RECEIVABLES
SELECT DISTINCT CEL.Meaning ORIGIN ,
CTTM.TRX_TYPE TRX_TYPE,
to_char(CR.CASH_RECEIPT_ID) DOC_NUMBER,
TO_CHAR(NVL(CR.DEPOSIT_DATE, CR.RECEIPT_DATE),'YYYY-MM-DD') TRX_DATE,
--GJL.ENTERED_DR ENTERED_BOOKS, --bug 15858278 replace GL with XLA,
--GJL.ACCOUNTED_DR ACCOUNTED_BOOKS, --bug 15858278 replace GL with XLA,
XAL.ENTERED_DR ENTERED_BOOKS,
XAL.ACCOUNTED_DR ACCOUNTED_BOOKS,
0 BANK,
RECEIPT_NUMBER STATEMENT_NUMBER
FROM
GL_JE_LINES GJL
, GL_JE_HEADERS GJH
, GL_JE_BATCHES GJB -- bug 15858278 ADDED
, GL_IMPORT_REFERENCES GLIR
, AR_DISTRIBUTIONS_ALL ARD
, XLA_DISTRIBUTION_LINKS XDL
, XLA_AE_LINES XAL
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_CASH_RECEIPTS_ALL CR
, XLA_AE_HEADERS XAH
, CE_TRX_TYPE_MAPPING CTTM
, CE_LOOKUPS CEL
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.JE_SOURCE = 'Receivables'
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID -- bug 15858278 ADDED
AND GJB.STATUS = 'P' -- bug 15858278 ADDED
--AND GJL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE --bug 15968568
replace with XAL.ACCOUNTING_DATE
AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
and xah.application_id = 222 -- bug 15858278 ADDED
and xah.application_id = xal.application_id
and xal.gl_sl_link_table = GLIR.GL_SL_LINK_table
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND ARD.SOURCE_TABLE = 'CRH'
AND ARD.SOURCE_ID = CRH.CASH_RECEIPT_HISTORY_ID
AND ARD.LINE_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XDL.APPLICATION_ID = 222
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'
AND XAL.APPLICATION_ID = 222
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CR.SET_OF_BOOKS_ID = XAH.LEDGER_ID
AND CRH.EVENT_ID IS NOT NULL
AND CR.RECON_FLAG = 'N'
AND CTTM.PMT_RCT_METHOD = TO_CHAR(CR.RECEIPT_METHOD_ID) --??? foreign key
column from AR table not known
AND CTTM.MAPPING_TYPE='RECEIPT'
AND CEL.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL.lookup_code = 'AR'
AND crh.status in ('REMITTED', 'CLEARED', 'RISK_ELIMINATED')
AND NOT EXISTS (SELECT 1 FROM AR_CASH_RECEIPT_HISTORY_ALL CRH1 WHERE
CRH1.STATUS = 'REVERSED' AND CRH1.CURRENT_RECORD_FLAG = 'Y' AND CRH.CASH_RECEIPT_ID
= CRH1.CASH_RECEIPT_ID)
AND gjl.&p_where
UNION ALL
-- UNRECONCILED PAYROLL TRX
SELECT DISTINCT CEL.Meaning ORIGIN ,
CTTM.TRX_TYPE TRX_TYPE,
to_char(CE_PR.PAYROLL_REL_ACTION_ID) DOC_NUMBER,
TO_CHAR(NVL(CE_PR.CLEARED_DATE, CE_PR.PAYMENT_DATE),'YYYY-MM-DD') TRX_DATE,
-- AMOUNT ENTERED_BOOKS ,
-- -1*AMOUNT*CE_CEXRECRE_XMLP_PKG.currency_conversion (:P_SET_OF_BOOKS_ID,
CE_BA.currency_code , CE_PR.PAYMENT_DATE, :gc_conversion_type) ACCOUNTED_BOOKS,
NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR,0) BOOKS,
NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0) ACCOUNTED_BOOKS,
0 BANK,
CE_PR.CHECK_NUMBER STATEMENT_NUMBER
FROM PAY_CE_TRANSACTIONS CE_PR,
CE_BANK_ACCOUNTS_PAY_V CE_BA,
GL_IMPORT_REFERENCES GLIR,--bug 15858278 7 tables added
GL_JE_LINES GJL,
GL_JE_HEADERS GJH,
GL_JE_BATCHES GJB,
GL_LEDGERS GL,
xla_ae_headers xah,
XLA_AE_LINES XAL,
pay_xla_events pe,
CE_TRX_TYPE_MAPPING CTTM,
CE_LOOKUPS CEL
WHERE CE_PR.RECON_FLAG = 'N'
AND CE_PR.PAYMENT_STATUS = 'PAID'
AND CE_BA.BANK_ACCOUNT_ID = CE_PR.PAYER_BANK_ACCOUNT_ID
--AND TRUNC(CE_PR.PAYMENT_DATE) BETWEEN :GC_FROM_DATE AND :GC_TO_DATE --
replaced with GJL.EFFECTIVE_DATE
--AND GJL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE --bug 15968568
replace with XAL.ACCOUNTING_DATE
AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
and XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
and xah.application_id = 801
and xah.application_id = xal.application_id
and xal.gl_sl_link_table = GLIR.GL_SL_LINK_table
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM
and GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJB.STATUS = 'P'
AND GJL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
and xah.gl_transfer_status_code = 'Y'
and pe.event_id = xah.event_id
and exists
(select 1
from pay_payroll_rel_actions ra,
pay_action_interlocks int1,
pay_action_interlocks int2,
pay_action_interlocks int3
where ce_pr.pre_payment_id = ra.pre_payment_id
and int1.locked_action_id = ra.payroll_rel_action_id
and int2.locked_action_id = int1.locking_action_id
and int2.locking_action_id = pe.payroll_rel_action_id
and int3.locked_action_id = ra.payroll_rel_action_id
and ce_pr.payroll_rel_action_id = int3.locking_action_id
)
AND CTTM.PAY_PAYMENT_TYPE_ID(+) = CE_PR.PAYMENT_TYPE_ID
AND CEL.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL.lookup_code = 'PR'
AND &p_where_pay
UNION ALL
SELECT CEL.Meaning ORIGIN ,
EXT.TRANSACTION_TYPE TRX_TYPE ,
TO_CHAR(EXT.EXTERNAL_TRANSACTION_ID) DOC_NUMBER,
TO_CHAR(EXT.TRANSACTION_DATE,'YYYY-MM-DD') TRX_DATE ,
--NVL(-GJL.ENTERED_DR, GJL.ENTERED_CR) BOOKS ,
--NVL(GJL.ENTERED_DR,0)- NVL(GJL.ENTERED_CR ,0) ENTERED_BOOKS, --bug
15858278 replace GL with XLA,
-- NVL(GJL.ACCOUNTED_DR,0)- NVL(GJL.ACCOUNTED_CR ,0) ACCOUNTED_BOOKS,
--bug 15858278 replace GL with XLA,
NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0) ENTERED_BOOKS,
NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR ,0) ACCOUNTED_BOOKS,
0 BANK ,
TO_CHAR(EXT.TRANSACTION_ID) STATEMENT_NUMBER
FROM GL_JE_HEADERS GJH ,
GL_JE_LINES GJL ,
GL_JE_BATCHES GJB , -- bug 15858278 ADDED
GL_LEDGERS GL,
XLA_AE_HEADERS XAH ,
XLA_AE_LINES XAL ,
CE_EXTERNAL_TRANSACTIONS EXT,
XLA_TRANSACTION_ENTITIES TRX,
XLA_EVENTS XE,
CE_LOOKUPS CEL
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.JE_SOURCE = 'Cash Management'
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID -- bug 15858278 ADDED
AND GJB.STATUS = 'P' -- bug 15858278 ADDED
AND NVL(TRX.SOURCE_ID_INT_1, -99) = EXT.TRANSACTION_ID
AND GL.LEDGER_ID = TRX.LEDGER_ID
AND GJL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
--AND GJL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE --bug 15968568
replace with XE.EVENT_DATE
AND XE.EVENT_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID
AND TRX.SOURCE_ID_INT_1 = EXT.TRANSACTION_ID
AND XE.ENTITY_ID = TRX.ENTITY_ID
AND XAH.EVENT_ID = XE.EVENT_ID
AND TRX.APPLICATION_ID =260
AND EXT.STATUS = 'UNR'
AND TRX.APPLICATION_ID = XE.APPLICATION_ID
AND TRX.ENTITY_CODE = 'CE_EXTERNAL'
AND XAL.GL_SL_LINK_TABLE IN ('XLAJEL')
AND CEL.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL.Lookup_code = 'XT'
AND TRX.APPLICATION_ID = XAL.APPLICATION_ID
AND TRX.APPLICATION_ID = XAH.APPLICATION_ID
--AND EXT.TRANSACTION_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE -- bug
15858278 removed, used GJL.EFFECTIVE_DATE
AND gjl.&p_where

You might also like