/** Accounting of Inventory Transactions **/
SELECT 'INV1' BLOCK_NUMBER
, GJB.NAME AS JE_BATCH_NAME
, GJL.JE_HEADER_ID
, GJL.JE_LINE_NUM
, GJH.JE_SOURCE
, GJH.JE_CATEGORY
, GJH.NAME AS JE_NAME
, GJH.PERIOD_NAME
, TRUNC(GJL.EFFECTIVE_DATE) AS GL_DATE
, TO_CHAR(MMT.TRANSACTION_ID) AS DOC_NUMBER
, OOD.ORGANIZATION_NAME AS PARTY_NAME
, TO_CHAR(MMT.TRANSACTION_ID) AS TRANSACTION_NUMBER
, GJH.CURRENCY_CODE AS GL_CURRENCY_CODE
, NULL INVOICE_CURRENCY_CODE
, GCC.CONCATENATED_SEGMENTS
, MTT.TRANSACTION_TYPE_NAME AS NARATION
, XAH.EVENT_TYPE_CODE
, XAL.ENTERED_DR
, XAL.ENTERED_CR
, XAL.ACCOUNTED_DR
, XAL.ACCOUNTED_CR
, GIR.GL_SL_LINK_ID
, XAL.CODE_COMBINATION_ID
FROM APPS.GL_JE_LINES GJL
, APPS.GL_JE_HEADERS GJH
, APPS.GL_CODE_COMBINATIONS_KFV GCC
, APPS.GL_JE_BATCHES GJB
, APPS.GL_IMPORT_REFERENCES GIR
, APPS.XLA_AE_LINES XAL
, APPS.XLA_AE_HEADERS XAH
, APPS.XLA_TRANSACTION_ENTITIES_UPG UPG
, APPS.MTL_MATERIAL_TRANSACTIONS MMT
, APPS.MTL_TRANSACTION_TYPES MTT
, APPS.ORG_ORGANIZATION_DEFINITIONS OOD
WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM APPS.GL_CODE_COMBINATIONS_KFV
WHERE SEGMENT4 = :ACCT_CD)
AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID
AND GJL.STATUS = 'P'
AND GJH.ACTUAL_FLAG = 'A'
AND GJH.JE_SOURCE = 'Inventory'
AND GJH.JE_CATEGORY <>'Receiving'
AND TRUNC(GJL.EFFECTIVE_DATE) BETWEEN :P_START_DATE AND :P_END_DATE
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
--AND XAL.APPLICATION_ID in (707, 555)
AND XAL.APPLICATION_ID NOT IN (200,222,260)
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.EVENT_TYPE_CODE NOT IN ('RELE', 'CERT')
AND XAH.ENTITY_ID = UPG.ENTITY_ID
AND UPG.TRANSACTION_NUMBER = TO_CHAR(MMT.TRANSACTION_ID)
--AND UPG.ENTITY_CODE != 'RCV_ACCOUNTING_EVENTS'
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
SELECT 'INV1' BLOCK_NUMBER
, GJB.NAME AS JE_BATCH_NAME
, GJL.JE_HEADER_ID
, GJL.JE_LINE_NUM
, GJH.JE_SOURCE
, GJH.JE_CATEGORY
, GJH.NAME AS JE_NAME
, GJH.PERIOD_NAME
, TRUNC(GJL.EFFECTIVE_DATE) AS GL_DATE
, TO_CHAR(MMT.TRANSACTION_ID) AS DOC_NUMBER
, OOD.ORGANIZATION_NAME AS PARTY_NAME
, TO_CHAR(MMT.TRANSACTION_ID) AS TRANSACTION_NUMBER
, GJH.CURRENCY_CODE AS GL_CURRENCY_CODE
, NULL INVOICE_CURRENCY_CODE
, GCC.CONCATENATED_SEGMENTS
, MTT.TRANSACTION_TYPE_NAME AS NARATION
, XAH.EVENT_TYPE_CODE
, XAL.ENTERED_DR
, XAL.ENTERED_CR
, XAL.ACCOUNTED_DR
, XAL.ACCOUNTED_CR
, GIR.GL_SL_LINK_ID
, XAL.CODE_COMBINATION_ID
FROM APPS.GL_JE_LINES GJL
, APPS.GL_JE_HEADERS GJH
, APPS.GL_CODE_COMBINATIONS_KFV GCC
, APPS.GL_JE_BATCHES GJB
, APPS.GL_IMPORT_REFERENCES GIR
, APPS.XLA_AE_LINES XAL
, APPS.XLA_AE_HEADERS XAH
, APPS.XLA_TRANSACTION_ENTITIES_UPG UPG
, APPS.MTL_MATERIAL_TRANSACTIONS MMT
, APPS.MTL_TRANSACTION_TYPES MTT
, APPS.ORG_ORGANIZATION_DEFINITIONS OOD
WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM APPS.GL_CODE_COMBINATIONS_KFV
WHERE SEGMENT4 = :ACCT_CD)
AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID
AND GJL.STATUS = 'P'
AND GJH.ACTUAL_FLAG = 'A'
AND GJH.JE_SOURCE = 'Inventory'
AND GJH.JE_CATEGORY <>'Receiving'
AND TRUNC(GJL.EFFECTIVE_DATE) BETWEEN :P_START_DATE AND :P_END_DATE
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
--AND XAL.APPLICATION_ID in (707, 555)
AND XAL.APPLICATION_ID NOT IN (200,222,260)
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.EVENT_TYPE_CODE NOT IN ('RELE', 'CERT')
AND XAH.ENTITY_ID = UPG.ENTITY_ID
AND UPG.TRANSACTION_NUMBER = TO_CHAR(MMT.TRANSACTION_ID)
--AND UPG.ENTITY_CODE != 'RCV_ACCOUNTING_EVENTS'
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID