VIEW: APPS.JAI_INV_INTERORG_AEL_V

Source

View Text - Preformatted

SELECT 7000 application_id, 'Inter org transfer' event_type, 'JAI INV INORGTRANSF' trx_class, 'JAI INV INTERORG TRANSFER' trx_class_name, jmt.transaction_temp_id trx_hdr_id, 'JAI_MTL_TRXS' trx_hdr_table, jmt.from_organization inventory_org_id, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date currency_conversion_date, gjh.currency_conversion_rate currency_conversion_rate, gjh.currency_conversion_type currency_conversion_type, glct.user_conversion_type currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, jmt.last_update_date LAST_UPDATE_DATE, jmt.last_updated_by LAST_UPDATED_BY, jmt.creation_date CREATION_DATE, jmt.created_by CREATED_BY, jmt.last_update_login LAST_UPDATE_LOGIN, jmt.inventory_item_id item_id, jmt.from_subinventory from_subinv, jmt.to_subinventory to_subinv, uom.unit_of_measure uom, jmt.quantity quantity, jmt.excise_invoice_no excise_invoice_no, jmt.vat_invoice_no vat_invoice_no, ood1.organization_name source_org, jmt.transaction_date transaction_date, ood2.organization_name to_org, mmt.shipment_number shipment_number FROM org_organization_definitions ood1, org_organization_definitions ood2, mtl_units_of_measure_vl uom, mtl_material_transactions mmt, jai_mtl_trxs jmt, gl_daily_conversion_types glct, gl_je_categories gjc, gl_je_sources gjs, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl WHERE gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND gjh.currency_conversion_type = glct.conversion_type AND ood1.organization_id = jmt.from_organization AND ood2.organization_id = jmt.to_organization AND jmt.transaction_header_id = mmt.transaction_set_id AND jmt.transaction_temp_id = mmt.original_transaction_temp_id AND jmt.transaction_uom = uom.uom_code(+) AND gjl.je_header_id = gjh.je_header_id AND gjr.je_header_id = gjl.je_header_id AND gjr.je_line_num = gjl.je_line_num AND gjr.je_batch_id = gjh.je_batch_id AND gjr.reference_2 = 'India Localization Entry' AND gjr.reference_3 = 'jai_mtl_trx_pkg.do_cenvat_Acctg' AND gjr.reference_4 = 'jai_mtl_trxs' AND gjr.reference_5 = to_char(jmt.transaction_temp_id) AND gjr.reference_6 = 'transaction_temp_id' AND gjr.reference_7 = to_char(mmt.organization_id) AND gjs.je_source_name = 'Register India' AND gjc.je_category_name IN ('Register India', 'VAT India', 'Inventory India') UNION ALL SELECT 7000 application_id, 'Inter org transfer' event_type, 'JAI INV INORGTRANSF' trx_class, 'JAI INV INTERORG TRANSFER' trx_class_name, jmt.transaction_temp_id trx_hdr_id, 'JAI_MTL_TRXS' trx_hdr_table, jmt.from_organization inventory_org_id, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date currency_conversion_date, gjh.currency_conversion_rate currency_conversion_rate, gjh.currency_conversion_type currency_conversion_type, glct.user_conversion_type currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, jmt.last_update_date LAST_UPDATE_DATE, jmt.last_updated_by LAST_UPDATED_BY, jmt.creation_date CREATION_DATE, jmt.created_by CREATED_BY, jmt.last_update_login LAST_UPDATE_LOGIN, jmt.inventory_item_id item_id, jmt.from_subinventory from_subinv, jmt.to_subinventory to_subinv, uom.unit_of_measure uom, jmt.quantity quantity, jmt.excise_invoice_no excise_invoice_no, jmt.vat_invoice_no vat_invoice_no, ood1.organization_name source_org, jmt.transaction_date transaction_date, ood2.organization_name to_org, mmt.shipment_number shipment_number FROM org_organization_definitions ood1, org_organization_definitions ood2, mtl_units_of_measure_vl uom, mtl_material_transactions mmt, jai_mtl_trxs jmt, gl_daily_conversion_types glct, gl_je_categories gjc, gl_je_sources gjs, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl WHERE gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND gjh.currency_conversion_type = glct.conversion_type AND ood1.organization_id = jmt.from_organization AND ood2.organization_id = jmt.to_organization AND jmt.transaction_header_id = mmt.transaction_set_id AND jmt.transaction_temp_id = mmt.original_transaction_temp_id AND jmt.transaction_uom = uom.uom_code(+) AND gjl.je_header_id = gjh.je_header_id AND gjr.je_header_id = gjl.je_header_id AND gjr.je_line_num = gjl.je_line_num AND gjr.je_batch_id = gjh.je_batch_id AND gjr.reference_2 = 'India Localization Entry' AND gjr.reference_3 = 'jai_mtl_trxs_pkg.std_cost_entry' AND gjr.reference_4 = 'jai_mtl_trxs' AND gjr.reference_5 = to_char(jmt.transaction_temp_id) AND gjr.reference_6 = 'transaction_id' AND gjr.reference_7 = to_char(mmt.organization_id) AND gjs.je_source_name = 'Purchasing India' AND gjc.je_category_name = 'MMT'

View Text - HTML Formatted

SELECT 7000 APPLICATION_ID
, 'INTER ORG TRANSFER' EVENT_TYPE
, 'JAI INV INORGTRANSF' TRX_CLASS
, 'JAI INV INTERORG TRANSFER' TRX_CLASS_NAME
, JMT.TRANSACTION_TEMP_ID TRX_HDR_ID
, 'JAI_MTL_TRXS' TRX_HDR_TABLE
, JMT.FROM_ORGANIZATION INVENTORY_ORG_ID
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, JMT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JMT.LAST_UPDATED_BY LAST_UPDATED_BY
, JMT.CREATION_DATE CREATION_DATE
, JMT.CREATED_BY CREATED_BY
, JMT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, JMT.INVENTORY_ITEM_ID ITEM_ID
, JMT.FROM_SUBINVENTORY FROM_SUBINV
, JMT.TO_SUBINVENTORY TO_SUBINV
, UOM.UNIT_OF_MEASURE UOM
, JMT.QUANTITY QUANTITY
, JMT.EXCISE_INVOICE_NO EXCISE_INVOICE_NO
, JMT.VAT_INVOICE_NO VAT_INVOICE_NO
, OOD1.ORGANIZATION_NAME SOURCE_ORG
, JMT.TRANSACTION_DATE TRANSACTION_DATE
, OOD2.ORGANIZATION_NAME TO_ORG
, MMT.SHIPMENT_NUMBER SHIPMENT_NUMBER
FROM ORG_ORGANIZATION_DEFINITIONS OOD1
, ORG_ORGANIZATION_DEFINITIONS OOD2
, MTL_UNITS_OF_MEASURE_VL UOM
, MTL_MATERIAL_TRANSACTIONS MMT
, JAI_MTL_TRXS JMT
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND OOD1.ORGANIZATION_ID = JMT.FROM_ORGANIZATION
AND OOD2.ORGANIZATION_ID = JMT.TO_ORGANIZATION
AND JMT.TRANSACTION_HEADER_ID = MMT.TRANSACTION_SET_ID
AND JMT.TRANSACTION_TEMP_ID = MMT.ORIGINAL_TRANSACTION_TEMP_ID
AND JMT.TRANSACTION_UOM = UOM.UOM_CODE(+)
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'JAI_MTL_TRX_PKG.DO_CENVAT_ACCTG'
AND GJR.REFERENCE_4 = 'JAI_MTL_TRXS'
AND GJR.REFERENCE_5 = TO_CHAR(JMT.TRANSACTION_TEMP_ID)
AND GJR.REFERENCE_6 = 'TRANSACTION_TEMP_ID'
AND GJR.REFERENCE_7 = TO_CHAR(MMT.ORGANIZATION_ID)
AND GJS.JE_SOURCE_NAME = 'REGISTER INDIA'
AND GJC.JE_CATEGORY_NAME IN ('REGISTER INDIA'
, 'VAT INDIA'
, 'INVENTORY INDIA') UNION ALL SELECT 7000 APPLICATION_ID
, 'INTER ORG TRANSFER' EVENT_TYPE
, 'JAI INV INORGTRANSF' TRX_CLASS
, 'JAI INV INTERORG TRANSFER' TRX_CLASS_NAME
, JMT.TRANSACTION_TEMP_ID TRX_HDR_ID
, 'JAI_MTL_TRXS' TRX_HDR_TABLE
, JMT.FROM_ORGANIZATION INVENTORY_ORG_ID
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, JMT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JMT.LAST_UPDATED_BY LAST_UPDATED_BY
, JMT.CREATION_DATE CREATION_DATE
, JMT.CREATED_BY CREATED_BY
, JMT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, JMT.INVENTORY_ITEM_ID ITEM_ID
, JMT.FROM_SUBINVENTORY FROM_SUBINV
, JMT.TO_SUBINVENTORY TO_SUBINV
, UOM.UNIT_OF_MEASURE UOM
, JMT.QUANTITY QUANTITY
, JMT.EXCISE_INVOICE_NO EXCISE_INVOICE_NO
, JMT.VAT_INVOICE_NO VAT_INVOICE_NO
, OOD1.ORGANIZATION_NAME SOURCE_ORG
, JMT.TRANSACTION_DATE TRANSACTION_DATE
, OOD2.ORGANIZATION_NAME TO_ORG
, MMT.SHIPMENT_NUMBER SHIPMENT_NUMBER
FROM ORG_ORGANIZATION_DEFINITIONS OOD1
, ORG_ORGANIZATION_DEFINITIONS OOD2
, MTL_UNITS_OF_MEASURE_VL UOM
, MTL_MATERIAL_TRANSACTIONS MMT
, JAI_MTL_TRXS JMT
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND OOD1.ORGANIZATION_ID = JMT.FROM_ORGANIZATION
AND OOD2.ORGANIZATION_ID = JMT.TO_ORGANIZATION
AND JMT.TRANSACTION_HEADER_ID = MMT.TRANSACTION_SET_ID
AND JMT.TRANSACTION_TEMP_ID = MMT.ORIGINAL_TRANSACTION_TEMP_ID
AND JMT.TRANSACTION_UOM = UOM.UOM_CODE(+)
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'JAI_MTL_TRXS_PKG.STD_COST_ENTRY'
AND GJR.REFERENCE_4 = 'JAI_MTL_TRXS'
AND GJR.REFERENCE_5 = TO_CHAR(JMT.TRANSACTION_TEMP_ID)
AND GJR.REFERENCE_6 = 'TRANSACTION_ID'
AND GJR.REFERENCE_7 = TO_CHAR(MMT.ORGANIZATION_ID)
AND GJS.JE_SOURCE_NAME = 'PURCHASING INDIA'
AND GJC.JE_CATEGORY_NAME = 'MMT'