R12 – GL-Receiving India Drill Down Query

Here is the query for Link between General Ledger and Receiving India data’s.

SELECT msib.segment1 item,
msib.description,
rsh.receipt_num,
mp.organization_code,
gjl.accounted_dr,
gjl.accounted_cr,
gjh.je_source,
gjh.je_category
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_code_combinations_kfv gcc,
apps.gl_import_references gir,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl,
apps.gl_je_batches gjb,
apps.mtl_parameters mp,
apps.mtl_system_items_b msib
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 GJH.JE_SOURCE      = ‘Purchasing India’
AND gjh.period_name    =’Nov-14′
AND gjl.reference_2 = ‘India Localization Entry’
AND gjl.code_combination_id = gcc.code_combination_id
AND rt.organization_id      = mp.organization_id
AND rt.transaction_id       = gjl.reference_5
AND rt.shipment_header_id   = rsh.shipment_header_id
AND rsl.shipment_header_id  = rsh.shipment_header_id
AND rsl.shipment_line_id    = rt.shipment_line_id
AND msib.inventory_item_id  = rsl.item_id
AND msib.organization_id    = rt.organization_id
AND gjl.period_name        IN
(SELECT DISTINCT period_name
FROM APPS.GL_PERIODS
WHERE START_DATE BETWEEN :P_FROM_PERIOD AND :P_TO_PERIOD
);

Note: This one was not linked with India Localization SLA Tables, its a direct link between GL and Localization Info’s