GL -- XLA Joins
SELECT gjh.je_header_id,
gjh.je_category,
gjh.je_source,
gjh.period_name,
gjh.name journal_name,
gjl.je_line_num,
gjl.entered_cr gl_line_credit,
gjl.entered_dr gl_line_debit,
gir.reference_10 ref_accounted_cr
--mta.base_transaction_value
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_je_batches gjb,
apps.gl_import_references gir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_transaction_entities xte
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_header_id = gir.je_header_id
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjb.je_batch_id = gir.je_batch_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.entity_id = xte.entity_id
-- AND xal.application_id = '222'
-- AND xah.application_id = '222'
-- AND xte.application_id = '222'
-- AND gir.reference_10 = 157
AND gir.je_header_id = 120700
-- AND gjh.je_category = 'Sales Invoices'
-- AND gjh.je_source = 'Receivables'
-- AND gjh.period_name = 'Sep-15'
AND gjh.status = 'P'
AND gjl.status = 'P'
AND gjh.period_name = 'Sep-15'
==================================================================
GL -- XLA -- MMT Joins
SELECT msib.segment1,
mp.ORGANIZATION_CODE,
mmt.TRANSACTION_QUANTITY TRANSACTION_QUANTITY,
ACTUAL_COST,
round(ACTUAL_COST * mmt.TRANSACTION_QUANTITY,2) Cost ,
gjb.NAME batch_name,
gjl.accounted_dr,
gjl.accounted_cr,
gjh.NAME journal_name,
xal.accounting_class_code,
xal.accounted_dr accounted_dr,
xal.accounted_cr accounted_cr,
xal.CURRENCY_CODE --, gjh.je_source, gjh.je_category
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_je_batches gjb,
apps.gl_import_references gir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_transaction_entities xte,
apps.mtl_material_transactions mmt,
apps.mtl_system_items_b msib,
apps.gl_code_combinations_kfv gcc,
apps.mtl_parameters mp,
apps.mtl_transaction_types mtt --, GL_JE_BATCHES_HEADERS_V gjv
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_header_id = gir.je_header_id
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjb.je_batch_id = gir.je_batch_id
-- AND gjl.period_name = 'AUG-12-13'
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = '707'
AND xah.application_id = '707'
AND xte.application_id = '707'
AND xah.entity_id = xte.entity_id
AND xte.source_id_int_1 = mmt.transaction_id
-- AND xte.entity_code = 'MTL_ACCOUNTING_EVENTS'
AND mtt.transaction_type_id = mmt.transaction_type_id
AND mmt.inventory_item_id = msib.inventory_item_id
AND mmt.organization_id = msib.organization_id
AND mp.organization_id = mmt.organization_id
-- AND gjl.period_name in (select distinct period_name from apps.gl_periods where start_date between :p_from_per and :p_to_per)
--AND gcc.segment4 in (3021000, 3021100)--, 3021700, 3023000, 3023100)
AND gcc.segment3 IN (100510)
--AND msib.segment1 = '1411MIK-MK30S3TCYS2B-200'
-- AND gcc.segment2 not in (102)
--AND TRUNC (xal.accounting_date) BETWEEN '01-JAN-2015' AND '31-JAN-2015'
AND gjl.code_combination_id = gcc.code_combination_id
-- AND gcc.segment4 = '3021000'
AND TRUNC (mmt.TRANSACTION_DATE) <= '31-AUG-2015'
--and gjb.NAME = 'JORDAN_ON-HAND_CONVESION Cost Management A 4004 3339387'
AND gjh.NAME like 'Sep-15%AED'
-- and ABS(( mmt.TRANSACTION_QUANTITY * round(ACTUAL_COST,2))) <> xal.accounted_dr
ORDER BY msib.segment1, mp.ORGANIZATION_CODE
========================================================
GL -- XLA -- RA -- OM -- MMT Joins (Report Query)
SELECT *
FROM (SELECT gjh.je_header_id,
gjh.je_category,
gjh.je_source,
gjh.period_name,
gjh.name journal_name,
TO_CHAR (gjh.posted_date, 'DD-MON-YYYY') gl_posted_date,
gjh.currency_code gl_curr,
gjl.je_line_num,
gjl.entered_cr gl_line_credit,
gjl.entered_dr gl_line_debit,
gir.reference_10 ref_acctd_10,
gir.reference_9 ref_acctd_9,
rct.trx_number inv_numr,
TO_CHAR (rct.trx_date, 'DD-MON-YYYY') trx_date,
rct.invoice_currency_code inv_cur,
NVL (rctl.GROSS_EXTENDED_AMOUNT, rctl.EXTENDED_AMOUNT)
ra_line_amount, -- rctdl.amount ra_line_amount,
TO_CHAR (rctdl.gl_date, 'DD-MON-YYYY') ra_gl_date,
TO_CHAR (rctdl.gl_posted_date, 'DD-MON-YYYY')
ra_gl_posted_date,
ooh.order_number,
ooh.transactional_curr_code SO_CURR,
ool.line_number order_line_num,
(DECODE (ool.line_category_code, 'RETURN', -1, 1)
* ool.ordered_quantity)
ordered_quantity,
ool.ordered_item,
ool.unit_selling_price So_line_sprice,
ool.unit_list_price so_line_lprice,
mta.base_transaction_value
* (CASE
WHEN OOH.ORDER_CATEGORY_CODE = 'ORDER' THEN 1
ELSE -1
END)
base_transaction_value,
gcc.segment3,
rctt.TYPE,
rctt.NAME trx_type,
rctl.INTERFACE_LINE_ATTRIBUTE6,
rctl.INTERFACE_LINE_ATTRIBUTE1,
rctl.sales_order_line,
APPS.gl_currency_api.get_rate (gjh.currency_code,
'USD',
SYSDATE,
'Corporate')
EXCHANGE_RATE,
ROUND (NVL (rctl.GROSS_EXTENDED_AMOUNT, rctl.EXTENDED_AMOUNT)
* APPS.gl_currency_api.get_rate (gjh.currency_code,
'USD',
SYSDATE,
'Corporate'), 2)
AR_Value_in_USD,
ROUND (mta.base_transaction_value
* APPS.gl_currency_api.
get_rate (
(CASE
WHEN gjh.currency_code = 'BHD'
OR gjh.currency_code = 'OMR'
OR gjh.currency_code = 'KWD'
OR gjh.currency_code = 'QAR'
THEN
'AED'
ELSE
gjh.currency_code
END),
'USD',
SYSDATE,
'Corporate'), 2)
* (CASE
WHEN OOH.ORDER_CATEGORY_CODE = 'ORDER' THEN 1
ELSE -1
END)
Base_Value_in_USD
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_je_batches gjb,
apps.gl_import_references gir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_transaction_entities xte,
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rctdl,
oe_order_headers_all ooh,
oe_order_lines_all ool,
mtl_material_transactions mmt,
mtl_system_items_b mis,
mtl_transaction_accounts mta,
xla_distribution_links xdl,
gl_code_combinations_kfv gcc,
ra_cust_trx_types_all rctt
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_header_id = gir.je_header_id
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjb.je_batch_id = gir.je_batch_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.entity_id = xte.entity_id
AND xal.application_id = '222'
AND xah.application_id = '222'
AND xte.application_id = '222'
AND gjh.status = 'P'
AND gjl.status = 'P'
AND xte.source_id_int_1 = rct.customer_trx_id
AND rctl.customer_trx_id = rct.customer_trx_id
AND rctdl.customer_trx_id = rct.customer_trx_id
AND rctdl.account_class = 'REV'
--AND rctdl.cogs_request_id > 0
AND rct.interface_header_attribute1 = ooh.order_number
AND ooh.header_id = ool.header_id
--AND rctl.sales_order_line = ool.line_number
--AND rctl.sales_order = ooh.order_number
AND rctl.INTERFACE_LINE_ATTRIBUTE6 = ool.line_id
AND rctl.INTERFACE_LINE_ATTRIBUTE1 = ooh.order_number
AND rctl.customer_trx_line_id = rctdl.customer_trx_line_id
--AND mmt.transaction_source_name = ooh.order_number
AND mmt.TRX_SOURCE_LINE_ID = ool.line_id
AND mmt.transaction_type_id IN (10008, 15) -- COGS Recognition,RMA Receipt
AND mis.inventory_item_id = mmt.inventory_item_id
AND mis.organization_id = 103
AND xte.entity_code = 'TRANSACTIONS'
AND mis.segment1 = ool.ordered_item
AND mta.transaction_id = mmt.transaction_id
AND mta.accounting_line_type = 36 --Cost of Goods Sold mfg look up 'CST_ACCOUNTING_LINE_TYPE'
--AND gir.reference_10 = 157
-- AND gir.je_header_id in ( 123814 ,120700)
AND gjh.je_category IN ('Sales Invoices', 'Credit Memos')
AND gjh.je_source = 'Receivables'
AND gjh.period_name = :P_PERIOD_NAME --'Sep-15'
--AND gir.reference_10 = rctdl.amount
AND xdl.SOURCE_DISTRIBUTION_ID_NUM_1 =
rctdl.CUST_TRX_LINE_GL_DIST_ID
AND xah.AE_HEADER_ID = xdl.AE_HEADER_ID
AND xal.AE_LINE_NUM = xdl.AE_LINE_NUM
AND gjl.code_combination_id = gcc.code_combination_id
AND rctt.CUST_TRX_TYPE_ID = rct.CUST_TRX_TYPE_ID
AND gcc.segment3 IN
('410050',
'410010',
'410014',
'410012',
'410030',
'410130',
'100545')
AND gjh.LEDGER_ID IN (2045, 2047, 2049, 2042)
-- AND TRX_NUMBER =138905
-- AND ROWNUM < 50
-- AND ooh.order_number = 92015219228
-- AND ooh.order_number NOT LIKE '9%'
UNION
SELECT gjh.je_header_id,
gjh.je_category,
gjh.je_source,
gjh.period_name,
gjh.name journal_name,
TO_CHAR (gjh.posted_date, 'DD-MON-YYYY') gl_posted_date,
gjh.currency_code gl_currency_code,
gjl.je_line_num,
gjl.entered_cr gl_line_credit,
gjl.entered_dr gl_line_debit,
gir.reference_10 ref_accounted_10,
gir.reference_9 ref_accounted_9,
rct.trx_number invoice_number,
TO_CHAR (rct.trx_date, 'DD-MON-YYYY') trx_date,
rct.invoice_currency_code,
NVL (rctl.GROSS_EXTENDED_AMOUNT, rctl.EXTENDED_AMOUNT)
ra_line_amount, --rctdl.amount ra_line_amount,
TO_CHAR (rctdl.gl_date, 'DD-MON-YYYY') ra_gl_date,
TO_CHAR (rctdl.gl_posted_date, 'DD-MON-YYYY')
ra_gl_posted_date,
ooh.order_number,
ooh.transactional_curr_code,
NULL order_line_num,
NULL ordered_quantity,
NULL ordered_item,
ADJUSTED_AMOUNT So_line_selling_price,
NULL so_line_list_price,
NULL base_transaction_value,
gcc.segment3,
rctt.TYPE,
rctt.NAME trx_type,
rctl.INTERFACE_LINE_ATTRIBUTE6,
rctl.INTERFACE_LINE_ATTRIBUTE1,
rctl.sales_order_line,
APPS.gl_currency_api.get_rate (gjh.currency_code,
'USD',
SYSDATE,
'Corporate')
EXCHANGE_RATE,
ROUND (NVL (rctl.GROSS_EXTENDED_AMOUNT, rctl.EXTENDED_AMOUNT)
* APPS.gl_currency_api.get_rate (gjh.currency_code,
'USD',
SYSDATE,
'Corporate'), 2)
AR_Value_in_USD,
NULL Base_Value_in_USD
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_je_batches gjb,
apps.gl_import_references gir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_transaction_entities xte,
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rctdl,
oe_order_headers_all ooh,
xla_distribution_links xdl,
gl_code_combinations_kfv gcc,
ra_cust_trx_types_all rctt,
OE_PRICE_ADJUSTMENTS op
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_header_id = gir.je_header_id
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjb.je_batch_id = gir.je_batch_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.entity_id = xte.entity_id
AND xal.application_id = '222'
AND xah.application_id = '222'
AND xte.application_id = '222'
AND gjh.status = 'P'
AND gjl.status = 'P'
AND xte.source_id_int_1 = rct.customer_trx_id
AND rctl.customer_trx_id = rct.customer_trx_id
AND rctdl.customer_trx_id = rct.customer_trx_id
AND rctdl.account_class = 'REV'
--AND rctdl.cogs_request_id > 0
AND rct.interface_header_attribute1 = ooh.order_number
AND ooh.header_id = op.header_id
AND rctl.INTERFACE_LINE_ATTRIBUTE6 = op.PRICE_ADJUSTMENT_ID
AND rctl.INTERFACE_LINE_ATTRIBUTE1 = ooh.order_number
AND rctl.customer_trx_line_id = rctdl.customer_trx_line_id
AND xte.entity_code = 'TRANSACTIONS'
--AND gir.reference_10 = 157
-- AND gir.je_header_id in ( 123814 ,120700)
AND gjh.je_category IN ('Sales Invoices', 'Credit Memos')
AND gjh.je_source = 'Receivables'
AND gjh.period_name = :P_PERIOD_NAME -- 'Sep-15'
--AND gir.reference_10 = rctdl.amount
AND xdl.SOURCE_DISTRIBUTION_ID_NUM_1 =
rctdl.CUST_TRX_LINE_GL_DIST_ID
AND xah.AE_HEADER_ID = xdl.AE_HEADER_ID
AND xal.AE_LINE_NUM = xdl.AE_LINE_NUM
AND gjl.code_combination_id = gcc.code_combination_id
AND rctt.CUST_TRX_TYPE_ID = rct.CUST_TRX_TYPE_ID
AND gcc.segment3 IN
('410050',
'410010',
'410014',
'410012',
'410030',
'410130',
'100545')
AND gjh.LEDGER_ID IN (2045, 2047, 2049, 2042)
AND ooh.header_id = op.header_id -- AND ROWNUM < 50
-- AND TRX_NUMBER =138905
-- AND ooh.order_number NOT LIKE '9%' -- 20151181867
)
ORDER BY order_number
==================================================================
Important columns affected:
After a transaction is performed in forms:
mtl_material_transactions.costed_flag = 'N'
After the Cost Manager Picks up the data and processes it:
mtl_material_transactions.costed_flag is Null
xla_events.event_status_code = 'U'
xla_events.process_status_code = 'U'
After the Create Accounting - Cost Management is run:
xla_events.event_status_code = 'P'
xla_events.process_status_code = 'P'
xla_ae_headers.gl_transfer_status_code = 'N'
xla_ae_headers.gl_transfer_date is Null
After the Transfer To GL is run:
xla_ae_headers.gl_transfer_status_code = 'Y'
xla_ae_headers.gl_transfer_date is Not Null
Queries involved:
SELECT *
FROM mtl_material_transactions
WHERE transaction_id = '&transaction_id'
SELECT *
FROM mtl_transaction_accounts
WHERE transaction_id = '&transaction_id'
SELECT *
FROM XLA_TRANSACTION_ENTITIES_upg
WHERE source_id_int_1 = '&transaction_id'
SELECT *
FROM xla_events
WHERE entity_id IN (SELECT entity_id
FROM XLA_TRANSACTION_ENTITIES_upg
WHERE source_id_int_1 = '&transaction_id')
SELECT *
FROM xla_distribution_links
WHERE source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
AND source_distribution_id_num_1 IN (SELECT inv_sub_ledger_id
FROM mtl_transaction_accounts
WHERE transaction_id = '&txnid')
SELECT *
FROM gl_import_references
WHERE gl_sl_link_table = 'XLAJEL' AND gl_sl_link_id IN ('')
SELECT *
FROM gl_je_lines
WHERE je_header_id IN (123) AND je_line_num IN ('')
SELECT *
FROM xla_accounting_errors
WHERE event_id IN
(SELECT event_id
FROM xla_events
WHERE entity_id IN (SELECT entity_id
FROM XLA_TRANSACTION_ENTITIES_upg
WHERE source_id_int_1 = '&transaction_id'))
===========================================
