1. GL to AR Receipts.
2. GL to AP Payments
3. GL to COGS AR/SO (Sales Order/AR Trx)
4. GL to Move Order Details.
5. GL to WIP(Job Number)
6. GL to Inventory(Material Txn)
7. GL to Receipts (Inv RCV)
GL_Balances table will have only Posted GL txns.
GL>Inquiry>Accounts> Jan-24 to Feb-24,
ex: Jan-24 10000 (Closing Balance)
Feb-24 50000 (Closing Balance)
Opening Balance for Jan-24 will be Closing Balance of ADJ-23 Period.
Opening Balance for Feb-24 will be Closing Balance of Jan-24 Period.
SELECT
SUM (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0))
beginning_bal,
SUM( NVL (gb.begin_balance_dr, 0)
- NVL (gb.begin_balance_cr, 0)
+ (NVL (gb.period_net_Dr, 0) - NVL (gb.period_net_cr, 0)))
end_bal,
SUM(
(NVL (gb.period_net_Dr, 0) - NVL (gb.period_net_cr, 0))
)PERIOD_TXN
,gb.currency_code
FROM gl_balances gb, gl_code_combinations_kfv gcc
WHERE gb.code_combination_id = gcc.code_combination_id
AND GCc.SEGMENT4 = :P_ACCOUNT_NUM
AND gb.ledger_id = 2021 --— Enter the Ledger
--AND gb.Actual_flag = 'E'--–Encumbrance
AND gb.period_name = 'JAN-24'-- –Enter the Period
--AND gb.currency_code = 'AED'
GROUP BY gb.currency_code
;
--Function
FUNCTION
F_GET_BEGIN_CLOSE_BAL
(P_LEDGER_ID NUMBER,
P_PERIOD_NUM VARCHAR2, --'20240001'
P_SEGMENT4 VARCHAR2, --'1101051001'
P_CURRENCY VARCHAR2, --'AED'
P_RET_TYPE VARCHAR2) --'BEGIN','END'
RETURN NUMBER AS
L_beginning_bal NUMBER;
L_end_bal NUMBER;
L_PERIOD_TXN NUMBER;
L_RETURN NUMBER;
BEGIN
SELECT
--gcc.segment4,
--gb.period_name,
SUM (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0))
beginning_bal,
SUM( NVL (gb.begin_balance_dr, 0)
- NVL (gb.begin_balance_cr, 0)
+ (NVL (gb.period_net_Dr, 0) - NVL (gb.period_net_cr, 0)))
end_bal,
SUM(
(NVL (gb.period_net_Dr, 0) - NVL (gb.period_net_cr, 0))
)PERIOD_TXN
--,gb.currency_code
INTO L_beginning_bal,L_end_bal,L_PERIOD_TXN
FROM gl_balances gb, gl_code_combinations_kfv gcc
,gl_period_statuses gps
WHERE gb.code_combination_id = gcc.code_combination_id
AND GCc.SEGMENT4 = P_SEGMENT4--'1101051001'--:P_ACCOUNT_NUM
AND gb.ledger_id = P_LEDGER_ID--2021 --— Enter the Ledger
--AND gb.Actual_flag = 'E'--–Encumbrance
--AND gb.period_name = 'JAN-24'-- –Enter the Period
AND gb.currency_code = P_CURRENCY--'AED'
AND GPS.PERIOD_NAME = GB.PERIOD_NAME
AND gps.LEDGER_ID = gb.LEDGER_ID
AND GPS.EFFECTIVE_PERIOD_NUM = P_PERIOD_NUM--'20240001'
AND NVL(gb.translated_flag,'X') = 'X'
AND gps.APPLICATION_ID = 101
GROUP BY gb.currency_code,gcc.segment4
,gb.period_name
;
CASE WHEN P_RET_TYPE = 'BEGIN' THEN
L_RETURN := L_beginning_bal;
WHEN P_RET_TYPE = 'END' THEN
L_RETURN := L_end_bal;
WHEN P_RET_TYPE = 'L_PERIOD_TXN' THEN
L_RETURN := L_PERIOD_TXN;
ELSE
L_RETURN := 0;
END CASE;
RETURN L_RETURN;
EXCEPTION WHEN OTHERS THEN RETURN 0;
END;
SELECT F_GET_BEGIN_CLOSE_BAL(2021,'20240001','1101051001','AED','BEGIN') FROM DUAL; --opening balance
SELECT F_GET_BEGIN_CLOSE_BAL(2021,'20240005','1101051001','AED','END') FROM DUAL; --closing balance
--for other queries
Scripts for Checking Closing to Opening Balances on the GL_BALANCES table (Doc ID 212082.1)
SELECT gcc.concatenated_segments ACCOUNTING_CODE_COMBINATION,
-- xla_report_utility_pkg.get_ccid_desc (glg.chart_of_accounts_id,
-- ael.code_combination_id)
-- CODE_COMBINATION_DESCRIPTION ,
xte.transaction_number TRANSACTION_NUMBER,
xle.transaction_date TRANSACTION_DATE,
ael.currency_code ENTERED_CURRENCY,
aeh.description HEADER_DESCRIPTION,
aeh.event_type_code EVENT_TYPE_CODE,
aeh.doc_sequence_value DOCUMENT_SEQUENCE_NUMBER,
gjh.DEFAULT_EFFECTIVE_DATE GL_DATE,
ael.entered_dr
ENTERED_DR,
ael.entered_cr
ENTERED_CR
,
ael.accounted_dr
ACCOUNTED_DR,
ael.accounted_cr
ACCOUNTED_CR,
CASE
WHEN gjh.STATUS = 'P' THEN 'Posted'
WHEN gjh.STATUS = 'U' THEN 'UnPosted'
ELSE gjh.STATUS
END POST_STATUS
FROM xla.xla_transaction_entities xte,
xla_ae_headers aeh,
xla_ae_lines ael,
gl.gl_import_references gir,
gl_je_lines gjl,
gl_je_headers gjh,
gl_je_batches gjb,
gl_code_combinations_kfv gcc,
xla.xla_events xle
WHERE 1 = 1
AND xle.application_id = aeh.application_id
AND xle.event_id = aeh.event_id
AND gjb.je_batch_id = gjh.je_batch_id
AND xte.entity_id = aeh.entity_id
AND aeh.ae_header_id = ael.ae_header_id
AND ael.gl_sl_link_id = gir.gl_sl_link_id
AND ael.gl_sl_link_table = gir.gl_sl_link_table
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjl.je_header_id = gjh.je_header_id
AND gcc.code_combination_id = gjl.code_combination_id
AND GCC.segment4 = '1101051001'
AND gjh.ledger_id = 2021
AND gjh.DEFAULT_EFFECTIVE_DATE BETWEEN TO_DATE ('01-JAN-2024')
AND TO_DATE ('31-JAN-2024');
Only GL Tables
SELECT B.*,
--:P_ORG1,
CASE WHEN AED_CR = 0 THEN NULL ELSE AED_CR END AED_CR,
CASE WHEN AED_DR = 0 THEN NULL ELSE AED_DR END AED_DR,
CASE WHEN RunningAgeTotal < 0 THEN ABS(RunningAgeTotal) ELSE NULL END CREDIT_BALANCE,
CASE WHEN RunningAgeTotal > 0 THEN RunningAgeTotal ELSE NULL END DEBIT_BALANCE
FROM (
SELECT A.*
,SUM (NVL(AED_DR,0) - NVL(AED_CR,0))OVER (ORDER BY JE_header_id,JE_LINE_NUM) AS RunningAgeTotal
FROM
(
SELECT GLV.CONCATENATED_SEGMENTS
,GLH.DEFAULT_EFFECTIVE_DATE TXN_DATE
,GLH.DOC_SEQUENCE_VALUE TXN_NUMBER
,GLH.CURRENCY_CODE
,NVL(GLL.ENTERED_DR,GLL.ENTERED_CR)ORIGINAL_AMOUNT
,GLH.DOC_SEQUENCE_VALUE
,GLH.DESCRIPTION
,GLH.DEFAULT_EFFECTIVE_DATE GL_DATE
,GLL.ACCOUNTED_DR AED_DR
,GLL.ACCOUNTED_CR AED_CR
,GLH.JE_HEADER_ID
,GLL.JE_LINE_NUM
,CASE
WHEN GLH.STATUS = 'P' THEN 'Posted'
WHEN GLH.STATUS = 'U' THEN 'UnPosted'
ELSE GLH.STATUS
END POST_STATUS
,GLH.JE_CATEGORY
,GLH.JE_SOURCE
FROM GL_JE_HEADERS GLH, GL_JE_LINES GLL, GL_CODE_COMBINATIONS_KFV GLV
,GL_PERIOD_STATUSES GLPS
WHERE GLH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GLL.CODE_COMBINATION_ID = GLV.CODE_COMBINATION_ID
AND GLH.LEDGER_ID = :P_LEDGER_ID--2021
AND GLV.SEGMENT4 BETWEEN :P_ACC_NUM_SEG4 AND :P_ACC_NUM_SEG4_2--'1101032002'--:P_ACC_NUM_SEG4
and GLH.period_name = GLPS.PERIOD_NAME--'FEB-24'
AND GLPS.LEDGER_ID = GLH.LEDGER_ID
AND GLPS.APPLICATION_ID = 101
AND GLPS.EFFECTIVE_PERIOD_NUM BETWEEN :P_FROM_PERIOD AND :P_TO_PERIOD
AND GLH.JE_SOURCE = NVL(:P_SOURCE,GLH.JE_SOURCE)
AND GLH.STATUS = 'P'
UNION ALL
SELECT NULL CONCATENATED_SEGMENTS
,NULL TXN_DATE
,NULL TXN_NUMBER
,NULL CURRENCY_CODE
,NULL ORIGINAL_AMOUNT
,NULL DOC_SEQUENCE_VALUE
,'Opening Balance' DESCRIPTION
,NULL GL_DATE
,CASE WHEN
GET_OPEN_GL_BALANCE_F(:P_LEDGER_ID,:P_ACC_NUM_SEG4, :P_FROM_PERIOD,:P_SOURCE) > 0 THEN
GET_OPEN_GL_BALANCE_F(:P_LEDGER_ID,:P_ACC_NUM_SEG4, :P_FROM_PERIOD,:P_SOURCE) ELSE NULL END DEBIT_BALANCE
,CASE WHEN
GET_OPEN_GL_BALANCE_F(:P_LEDGER_ID,:P_ACC_NUM_SEG4, :P_FROM_PERIOD,:P_SOURCE) < 0 THEN
ABS(GET_OPEN_GL_BALANCE_F(:P_LEDGER_ID,:P_ACC_NUM_SEG4, :P_FROM_PERIOD,:P_SOURCE)) ELSE NULL END CREDIT_BALANCE
,1 JE_HEADER_ID
,1 JE_LINE_NUM
,'P' POST_STATUS
,'Manual' JE_CATEGORY
,'Manual' JE_SOURCE
FROM DUAL
ORDER BY JE_header_id,JE_LINE_NUM
)A
)B
----------------------------GL To Receipts and GL to Payables link-----------------
FUNCTION F_GET_XLA_GL_DET(P_JE_HEADER_ID NUMBER, P_JE_SOURCE VARCHAR2,P_LINE_NUMBER NUMBER,P_TYPE VARCHAR2)
RETURN VARCHAR2
AS
L_RETURN VARCHAR2(200);
L_TRANSACTION_NUMBER VARCHAR2(200);
L_EVENT_TYPE_CODE VARCHAR2(200);
L_TRANSACTION_DATE VARCHAR2(200);
L_APPLICATION_ID NUMBER;
BEGIN
CASE WHEN P_JE_SOURCE = 'Manual' THEN
L_RETURN := 'Manual';
ELSE
SELECT APPLICATION_ID
INTO L_APPLICATION_ID
FROM FND_APPLICATION_TL
WHERE APPLICATION_NAME = P_JE_SOURCE
AND ROWNUM = 1
;
SELECT --JE_HEADER_ID,XLA.TRANSACTION_NUMBER,AEH.EVENT_TYPE_CODE, TO_CHAR(TRANSACTION_DATE,'DD/MM/YYYY')TRANSACTION_DATE, EVENT_NUMBER
XLA.TRANSACTION_NUMBER,AEH.EVENT_TYPE_CODE, TO_CHAR(TRANSACTION_DATE,'DD/MM/YYYY')TRANSACTION_DATE INTO
L_TRANSACTION_NUMBER,L_EVENT_TYPE_CODE,L_TRANSACTION_DATE
FROM XLA_EVENTS XLAE
,XLA.XLA_TRANSACTION_ENTITIES XLA
,XLA_AE_HEADERS AEH
,XLA_AE_LINES AEL
,GL_IMPORT_REFERENCES GLI
WHERE
XLA.ENTITY_ID = XLAE.ENTITY_ID--293729
AND AEH.APPLICATION_ID = L_APPLICATION_ID--222
AND AEH. AE_HEADER_ID = AEL. AE_HEADER_ID
-- AND EVENT_NUMBER = P_LINE_NUMBER AND AEL.AE_LINE_NUM = P_LINE_NUMBER -- ADD line_number here
AND EVENT_NUMBER = AEL.AE_LINE_NUM
AND AEH.EVENT_ID = XLAE.EVENT_ID
AND GLI.GL_SL_LINK_ID = AEL.GL_SL_LINK_ID
--AND SOURCE_ID_INT_1 = 104085
AND JE_HEADER_ID = P_JE_HEADER_ID;
CASE WHEN
P_TYPE = 'TXN_DATE' THEN
L_RETURN := L_TRANSACTION_DATE;
WHEN P_TYPE = 'EVENT_TYPE' THEN
L_RETURN := L_EVENT_TYPE_CODE;
WHEN P_TYPE = 'TXN_NUMBER' THEN
L_RETURN := L_TRANSACTION_NUMBER;
ELSE
L_RETURN := '';
END CASE;
END CASE; --MAIN
RETURN L_RETURN;
EXCEPTION WHEN OTHERS THEN RETURN NULL;
END;
F_GET_XLA_GL_DET(GLH.JE_HEADER_ID,GLH.JE_SOURCE,GLL.JE_LINE_NUM,'TXN_DATE')TXN_DATE
F_GET_XLA_GL_DET(GLH.JE_HEADER_ID,GLH.JE_SOURCE,GLL.JE_LINE_NUM,'TXN_NUMBER') TXN_NUMBER
.F_GET_XLA_GL_DET(GLH.JE_HEADER_ID,GLH.JE_SOURCE,GLL.JE_LINE_NUM,'EVENT_TYPE') EVENT_TYPE
1. GL to AR Receipts.
SELECT DISTINCT HZCA.ACCOUNT_NUMBER
FROM apps.ar_cash_receipts_all acra,
xla.xla_transaction_entities xte,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
apps.gl_code_combinations glcc,
apps.gl_import_references gir,
apps.gl_je_lines gjl,
apps.gl_je_headers gjh,
apps.gl_ledgers gl,
apps.gl_balances gb,
apps.ar_customers ac,
apps.gl_je_batches gjb,
APPS.HZ_CUST_ACCOUNTS_ALL HZCA
WHERE 1 = 1
AND HZCA.CUST_ACCOUNT_ID = ACRA.PAY_FROM_CUSTOMER
AND acra.customer_site_use_id = ac.customer_id(+)
AND xte.ledger_id = gl.ledger_id
AND xte.entity_code = 'RECEIPTS'
AND NVL (xte.source_id_int_1, -99) = acra.cash_receipt_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 xal.code_combination_id = glcc.code_combination_id
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xal.gl_sl_link_table = gir.gl_sl_link_table
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjl.je_header_id = gjh.je_header_id
AND gjh.ledger_id = gl.ledger_id
AND gb.code_combination_id = glcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = gl.currency_code
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_source = 'Receivables'
AND Gjh.je_header_id = P_JE_HEADER_ID
AND ROWNUM = 1;
2. GL to AP Payments
SELECT DISTINCT SEGMENT1
INTO L_RETURN
--,ACA.*
FROM ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_checks_all aca,
xla.xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl.gl_import_references gir,
gl_je_lines gjl,
gl_je_headers gjh,
AP_SUPPLIERS APS
WHERE 1 = 1
AND APS.VENDOR_ID = AIA.VENDOR_ID
AND aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND aca.check_id = NVL (SOURCE_ID_INT_1, (-99))
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.application_id = 200
AND xte.entity_id = xah.entity_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xal.gl_sl_link_table = gir.gl_sl_link_table
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjl.je_header_id = gjh.je_header_id
AND GJH.JE_HEADER_ID = P_JE_HEADER_ID
AND ROWNUM = 1
;
3. GL to COGS AR/SO (Sales Order/AR Trx)
SELECT DISTINCT HCAL.ACCOUNT_NUMBER
/*SELECT * RCTA.* (CHECK INTERFACE HEADER_ ATTRIBUTES FOR SALES ORDER DETAILS)
SELECT OEH.ORDER_NUMBER, rac.INTERFACE_LINE_ATTRIBUTE1||'-'||rac.INTERFACE_LINE_ATTRIBUTE2 ||'-'||INTERFACE_LINE_CONTEXT
,RAC.SALES_ORDER
FROM OE_ORDER_HEADERS_ALL OEH,
RA_CUSTOMER_TRX_LINES_ALL RAC
WHERE order_number = '20487' AND
TO_CHAR(OEH.order_number) = RAC.interface_line_attribute1;
*/
FROM RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTG,
XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA.XLA_DISTRIBUTION_LINKS XDL,
XLA.XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH,
HZ_CUST_ACCOUNTS_ALL HCAL
WHERE RCTA.CUSTOMER_TRX_ID = RCTG.CUSTOMER_TRX_ID
AND HCAL.CUST_ACCOUNT_ID = RCTA.BILL_TO_CUSTOMER_ID
AND RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1
AND XTE.ENTITY_ID = XE.ENTITY_ID
AND RCTG.CUST_TRX_LINE_GL_DIST_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
--AND XAH.AE_HEADER_ID = 423102
AND XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GIR.JE_HEADER_ID = GJH.JE_HEADER_ID
AND gjh.je_header_id = P_JE_HEADER_ID
AND ROWNUM = 1;
to be more breakdown.
Query 1: For complete Transaction
SELECT *
FROM
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTG,
XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XE
WHERE
RCTA.CUSTOMER_TRX_ID = RCTG.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1
AND XTE.ENTITY_ID = XE.ENTITY_ID
Query 2: After Create accounting Run
SELECT *
FROM
RA_CUST_TRX_LINE_GL_DIST_ALL RCTG,
XLA_DISTRIBUTION_LINKS XDL,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH
WHERE
RCTG.CUST_TRX_LINE_GL_DIST_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
Query 3 : After Run Transfer to GL
SELECT *
FROM
XLA_AE_LINES XAL,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH
WHERE
XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GIR.JE_HEADER_ID = GJH.JE_HEADER_ID
4. GL to Move Order Details.
select distinct mtrh.request_number ,MTL.SUBINVENTORY_CODE, MTL.TRANSFER_SUBINVENTORY FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl , MTL_MATERIAL_TRANSACTIONS MTL WHERE mtrh.header_id = mtrl.header_id AND mtrh.organization_id = mtrl.organization_id AND mtrl.line_id = MTL.move_order_line_id AND MTL.TRANSACTION_ID = :TXN_NUMBER;
5. GL to WIP(Job Number)
SELECT WIP_ENTITY_NAME INTO L_RETURN FROM MTL_MATERIAL_TRANSACTIONS MMT ,WIP_ENTITIES WIP WHERE WIP.WIP_ENTITY_ID = MMT.TRANSACTION_SOURCE_ID AND TRANSACTION_ID = :TXN_NUMBER AND ROWNUM =1;
SELECT WIP_ENTITY_NAME JOB_NUMBER FROM WIP_ENTITIES WIP WHERE WIP.WIP_ENTITY_ID IN (SELECT * FROM WIP_TRANSACTIONS WHERE TRANSACTION_ID IN (SELECT SOURCE_ID_INT_1 FROM XLA_TRANSACTION_ENTITIES_UPG WHERE ENTITY_ID IN ( SELECT ENTITY_ID FROM XLA_EVENTS WHERE EVENT_ID IN ( SELECT EVENT_ID FROM XLA_AE_HEADERS WHERE APPLICATION_ID=707 AND AE_HEADER_ID IN (SELECT AE_HEADER_ID FROM XLA_AE_LINES WHERE APPLICATION_ID=707 AND GL_SL_LINK_ID IN (SELECT GL_SL_LINK_ID FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID=&JE_HEADER_ID)))));
SELECT * FROM WIP_TRANSACTION_ACCOUNTS WHERE TRANSACTION_ID IN (SELECT SOURCE_ID_INT_1 FROM XLA_TRANSACTION_ENTITIES_UPG WHERE ENTITY_ID IN ( SELECT ENTITY_ID FROM XLA_EVENTS WHERE EVENT_ID IN ( SELECT EVENT_ID FROM XLA_AE_HEADERS WHERE APPLICATION_ID=707 AND AE_HEADER_ID IN (SELECT AE_HEADER_ID FROM XLA_AE_LINES WHERE APPLICATION_ID=707 AND GL_SL_LINK_ID IN (SELECT GL_SL_LINK_ID FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID=&JE_HEADER_ID)))))
6. GL to Inventory(Material Txn)
SELECT * FROM MTL_MATERIAL_TRANSACTIONS WHERE TRANSACTION_ID IN (SELECT SOURCE_ID_INT_1 FROM XLA.XLA_TRANSACTION_ENTITIES WHERE ENTITY_ID IN ( SELECT ENTITY_ID FROM XLA_EVENTS WHERE EVENT_ID IN ( SELECT EVENT_ID FROM XLA_AE_HEADERS WHERE APPLICATION_ID=707 AND AE_HEADER_ID IN (SELECT AE_HEADER_ID FROM XLA_AE_LINES WHERE APPLICATION_ID=707 AND GL_SL_LINK_ID IN (SELECT GL_SL_LINK_ID FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID=&JE_HEADER_ID)))))
SELECT * FROM MTL_TRANSACTION_ACCOUNTS WHERE TRANSACTION_ID IN (SELECT SOURCE_ID_INT_1 FROM XLA.XLA_TRANSACTION_ENTITIES WHERE ENTITY_ID IN ( SELECT ENTITY_ID FROM XLA_EVENTS WHERE EVENT_ID IN ( SELECT EVENT_ID FROM XLA_AE_HEADERS WHERE APPLICATION_ID=707 AND AE_HEADER_ID IN (SELECT AE_HEADER_ID FROM XLA_AE_LINES WHERE APPLICATION_ID=707 AND GL_SL_LINK_ID IN (SELECT GL_SL_LINK_ID FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID=&JE_HEADER_ID)))))
7. GL to Receipts (Inv RCV)
select * from rcv_transactions where transaction_id in(select source_id_int_1 from XLA_TRANSACTION_ENTITIES_UPG WHERE ENTITY_ID IN (SELECT ENTITY_ID FROM XLA_EVENTS WHERE EVENT_ID IN (SELECT EVENT_ID FROM XLA_AE_HEADERS WHERE APPLICATION_ID = 707 AND AE_HEADER_ID IN (SELECT AE_HEADER_ID FROM XLA_AE_LINES WHERE APPLICATION_ID = 707 AND GL_SL_LINK_ID IN (SELECT GL_SL_LINK_ID FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID = &JE_HEADER_ID)))))
select * from RCV_ACCOUNTING_EVENTS where RCV_TRANSACTION_ID in(select source_id_int_1 from XLA_TRANSACTION_ENTITIES_UPG WHERE ENTITY_ID IN (SELECT ENTITY_ID FROM XLA_EVENTS WHERE EVENT_ID IN (SELECT EVENT_ID FROM XLA_AE_HEADERS WHERE APPLICATION_ID = 707 AND AE_HEADER_ID IN (SELECT AE_HEADER_ID FROM XLA_AE_LINES WHERE APPLICATION_ID = 707 AND GL_SL_LINK_ID IN (SELECT GL_SL_LINK_ID FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID = &JE_HEADER_ID)))))
Subledger Accounting Diagnostic Scripts for Cost Management diagnose flow between Inventory/WIP/Receiving and GL through SLA (Doc ID 1372269.1)
