eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CST_AEL_GL_INV_V

Source

View Text - Preformatted


SELECT 401 , sob.set_of_books_id , mp.organization_code , mmt.organization_id , lu3.meaning , 'INV TRX' , mtt.transaction_type_name , mmt.transaction_type_id , to_char(mmt.transaction_id), mmt.transaction_id , mmt.transaction_date , mmt.transaction_reference , 'MMT' , mmt.transaction_id, lu1.meaning , to_char(mta.accounting_line_type) , mta.reference_account , nvl(mmt.currency_code, sob.currency_code) , DECODE(sign(mta.transaction_value), 1, mta.transaction_value, 0, decode(sign(mta.base_transaction_value), 1, 0, 0, decode(sign(mta.primary_quantity), 1, 0, 0, 0, NULL), NULL), NULL,decode(sign(mta.base_transaction_value), 1, mta.base_transaction_value, 0, decode(sign(mta.primary_quantity), 1, 0, 0, 0, NULL), NULL), NULL) , DECODE(sign(mta.transaction_value), 0,decode(sign(mta.base_transaction_value), -1,0, 0,decode(sign(mta.primary_quantity), -1, 0, null), null), -1, (-1*mta.transaction_value), NULL,decode(sign(mta.base_transaction_value), -1, (-1*mta.base_transaction_value), 0, decode(sign(mta.primary_quantity), -1, 0, null), null), NULL), DECODE(sign(mta.base_transaction_value), 0, decode(sign(mta.transaction_value), 0,decode(sign(mta.primary_quantity), -1, NULL, mta.base_transaction_value), -1,null, null, decode(sign(mta.primary_quantity), -1,NULL, mta.base_transaction_value), 0), 1,mta.base_transaction_value, null) , DECODE(sign(mta.base_transaction_value), 0,decode(sign(mta.transaction_value), -1, 0, 0, decode(sign(mta.primary_quantity), -1, 0, null) , null,decode(sign(mta.primary_quantity), -1,0, null), null), -1,(-1*mta.base_transaction_value), null) , mmt.currency_conversion_date , mmt.currency_conversion_type , glct.user_conversion_type , mmt.currency_conversion_rate , mmt.transaction_date , lu2.meaning , DECODE(mta.gl_batch_id, -1, 'N', 'Y') , DECODE(mta.gl_batch_id, -1, to_number(null), mta.gl_batch_id) , 'MMT' , mmt.transaction_id, 'MTA' , decode(cce.cost_element, NULL, lu1.meaning, lu1.meaning||', '||cce.cost_element) , rpad(decode(cce.cost_element, NULL, lu1.meaning, lu1.meaning||',  '||cce.cost_element), 132) /* ---------------------------Product Specific Columns ------------------------*/ , mmt.inventory_item_id , mmt.revision , msi.primary_uom_code , mta.primary_quantity , DECODE(mmt.transaction_type_id, 24, NULL, 80, NULL, DECODE(mta.primary_quantity, 0, NULL, NULL, NULL, ABS(mta.base_transaction_value/mta.primary_quantity))) , mmt.subinventory_code , mmt.locator_id , mil.description , mmt.operation_seq_num , mtst.transaction_source_type_name , mmt.transaction_source_type_id , mmt.transaction_source_name , mmt.transaction_source_id , mtr.reason_name , cce.cost_element , mta.cost_element_id , jel.je_header_id , jel.je_line_num , mta.last_update_date , mta.last_updated_by , mta.creation_date , mta.created_by , mta.last_update_login , mta.request_id , mta.program_application_id , mta.program_id , mta.program_update_date FROM gl_je_lines jel, gl_je_headers jeh, gl_import_references R, gl_period_statuses gps, gl_sets_of_books sob, gl_daily_conversion_types glct, mtl_transaction_accounts mta, cst_cost_elements cce, mtl_material_transactions mmt, mtl_transaction_types mtt, mtl_item_locations mil, mtl_transaction_reasons mtr, mtl_parameters mp, mtl_txn_source_types mtst, mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mtl_system_items_vl msi WHERE mmt.transaction_id = mta.transaction_id AND mmt.transaction_action_id NOT IN (2,3,5) AND mmt.inventory_item_id = mta.inventory_item_id AND mmt.organization_id = mp.organization_id AND mmt.organization_id = mil.organization_id(+) AND mmt.locator_id = mil.inventory_location_id(+) AND mmt.currency_conversion_type = glct.conversion_type(+) AND mtt.transaction_type_id = mmt.transaction_type_id AND mtst.transaction_source_type_id = mmt.transaction_source_type_id AND mtr.reason_id(+) = mmt.reason_id AND sob.set_of_books_id = gps.set_of_books_id AND gps.period_name = jel.period_name AND gps.application_id = 401 AND msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mmt.organization_id AND cce.cost_element_id(+) = mta.cost_element_id AND lu1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND lu1.lookup_code = mta.accounting_line_type AND lu2.lookup_type = 'SYS_YES_NO' AND lu2.lookup_code = decode(mta.gl_batch_id, -1, 2, 1) AND lu3.lookup_type = 'CST_VIEW_ACCOUNTING' AND lu3.lookup_code = 1 AND jeh.je_header_id = jel.je_header_id AND R.je_header_id = jeh.je_header_id AND R.je_line_num = jel.je_line_num AND jel.ledger_id = sob.set_of_books_id AND mta.gl_batch_id = R.reference_1 AND je_source = 'Inventory' AND je_category = 'MTL' AND ( ( R.gl_sl_link_id IS NOT NULL AND R.reference_3 IS NOT NULL AND mta.transaction_id = R.reference_3 AND mta.gl_sl_link_id = R.gl_sl_link_id ) OR ( R.gl_sl_link_id IS NULL AND R.reference_3 IS NULL AND mta.reference_account = jel.code_combination_id AND NVL(mta.currency_code,sob.currency_code) = jeh.currency_code AND DECODE(mta.encumbrance_type_id, NULL,'A','E') = jeh.actual_flag AND NVL(mta.encumbrance_type_id,-1) = NVL(jeh.encumbrance_type_id,-1) AND NVL(mmt.ussgl_transaction_code,'#ZZZ') = NVL(jeh.ussgl_transaction_code,'#ZZZ') ) ) UNION ALL SELECT 401 , sob.set_of_books_id , mp.organization_code , mmt.organization_id , lu3.meaning , 'INV TRX' , mtt.transaction_type_name , mmt.transaction_type_id , decode(mmt.transaction_action_id, 2, decode(sign(mmt.primary_quantity), -1,to_char(mmt.transaction_id), to_char(mmt.transfer_transaction_id)), 5,decode(sign(mmt.primary_quantity), -1, to_char(mmt.transaction_id), to_char(mmt.transfer_transaction_id)), to_char(mmt.transaction_id)) , decode(mmt.transaction_action_id, 2,decode(sign(mmt.primary_quantity), -1, mmt.transaction_id, mmt.transfer_transaction_id), 5,decode(sign(mmt.primary_quantity), -1,mmt.transaction_id, mmt.transfer_transaction_id), mmt.transaction_id), mmt.transaction_date , mmt.transaction_reference , 'MMT' , decode(mmt.transaction_action_id, 2,decode(sign(mmt.primary_quantity), -1,mmt.transaction_id, mmt.transfer_transaction_id), 5,decode(sign(mmt.primary_quantity), -1, mmt.transaction_id, mmt.transfer_transaction_id), mmt.transaction_id), lu1.meaning , to_char(mta.accounting_line_type) , mta.reference_account , nvl(mmt.currency_code, sob.currency_code) , DECODE(sign(mta.transaction_value), 1, mta.transaction_value, 0, decode(sign(mta.base_transaction_value), 1, 0, 0, decode(sign(mta.primary_quantity), 1, 0, 0, 0, NULL), NULL), NULL,decode(sign(mta.base_transaction_value), 1,mta.base_transaction_value, 0, decode(sign(mta.primary_quantity), 1, 0, 0, 0, NULL), NULL), NULL) , DECODE(sign(mta.transaction_value), 0, decode(sign(mta.base_transaction_value), -1, 0, 0, decode(sign(mta.primary_quantity), -1, 0, null), null), -1, (-1*mta.transaction_value), NULL, decode(sign(mta.base_transaction_value), -1, (-1*mta.base_transaction_value), 0, decode(sign(mta.primary_quantity), -1, 0, null), null), NULL) , DECODE(sign(mta.base_transaction_value), 0,decode(sign(mta.transaction_value), 0, decode(sign(mta.primary_quantity), -1, NULL, mta.base_transaction_value), -1, null, null, decode(sign(mta.primary_quantity), -1, NULL, mta.base_transaction_value), 0), 1, mta.base_transaction_value, null) , DECODE(sign(mta.base_transaction_value), 0, decode(sign(mta.transaction_value), -1, 0, 0, decode(sign(mta.primary_quantity), -1, 0, null) , null, decode(sign(mta.primary_quantity), -1, 0, null), null), -1, (-1*mta.base_transaction_value), null) , mmt.currency_conversion_date , mmt.currency_conversion_type , glct.user_conversion_type , mmt.currency_conversion_rate , mmt.transaction_date , lu2.meaning , DECODE(mta.gl_batch_id, -1, 'N', 'Y') , DECODE(mta.gl_batch_id, -1, to_number(null), mta.gl_batch_id) , 'MMT' , decode(mmt.transaction_action_id, 2, decode(sign(mmt.primary_quantity), -1, mmt.transaction_id, mmt.transfer_transaction_id), 5, decode(sign(mmt.primary_quantity), -1,mmt.transaction_id, mmt.transfer_transaction_id), mmt.transaction_id) , 'MTA' , decode(cce.cost_element, NULL, lu1.meaning, lu1.meaning||',  '||cce.cost_element) , rpad(decode(cce.cost_element, NULL, lu1.meaning, lu1.meaning||',  '||cce.cost_element), 132) /* ---------------------------Product Specific Columns ------------------------*/ , mmt.inventory_item_id , mmt.revision , msi.primary_uom_code , mta.primary_quantity , DECODE(mmt.transaction_type_id, 24, NULL, 80, NULL, DECODE(mta.primary_quantity, 0, NULL, NULL, NULL, ABS(mta.base_transaction_value/mta.primary_quantity))) , mmt.subinventory_code , mmt.locator_id , mil.description , mmt.operation_seq_num , mtst.transaction_source_type_name , mmt.transaction_source_type_id , mmt.transaction_source_name , mmt.transaction_source_id , mtr.reason_name , cce.cost_element , mta.cost_element_id , jel.je_header_id , jel.je_line_num , mta.last_update_date , mta.last_updated_by , mta.creation_date , mta.created_by , mta.last_update_login , mta.request_id , mta.program_application_id , mta.program_id , mta.program_update_date FROM gl_je_lines jel, gl_je_headers jeh, gl_import_references R, gl_period_statuses gps, gl_sets_of_books sob, gl_daily_conversion_types glct, mtl_transaction_accounts mta, cst_cost_elements cce, mtl_material_transactions mmt, mtl_transaction_types mtt, mtl_item_locations mil, mtl_transaction_reasons mtr, mtl_parameters mp, mtl_txn_source_types mtst, mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mtl_system_items_vl msi WHERE /* specifically transfer type transactions */ mmt.transfer_transaction_id = mta.transaction_id AND mmt.transaction_action_id IN (2, 5) AND mmt.inventory_item_id = mta.inventory_item_id AND mmt.organization_id = mp.organization_id AND mmt.organization_id = mil.organization_id(+) AND mmt.locator_id = mil.inventory_location_id(+) AND mmt.currency_conversion_type = glct.conversion_type(+) AND mtt.transaction_type_id = mmt.transaction_type_id AND mtst.transaction_source_type_id = mmt.transaction_source_type_id AND mtr.reason_id(+) = mmt.reason_id AND sob.set_of_books_id = gps.set_of_books_id AND gps.period_name = jel.period_name AND gps.application_id = 401 AND msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mmt.organization_id AND cce.cost_element_id(+) = mta.cost_element_id AND lu1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND lu1.lookup_code = mta.accounting_line_type AND lu2.lookup_type = 'SYS_YES_NO' AND lu2.lookup_code = decode(mta.gl_batch_id, -1, 2, 1) AND lu3.lookup_type = 'CST_VIEW_ACCOUNTING' AND lu3.lookup_code = 1 AND jeh.je_header_id = jel.je_header_id AND R.je_header_id = jeh.je_header_id AND R.je_line_num = jel.je_line_num AND jel.ledger_id = sob.set_of_books_id AND mta.gl_batch_id = R.reference_1 AND je_source = 'Inventory' AND je_category = 'MTL' AND ( ( R.gl_sl_link_id IS NOT NULL AND R.reference_3 IS NOT NULL AND mta.transaction_id = R.reference_3 AND mta.gl_sl_link_id = R.gl_sl_link_id ) OR ( R.gl_sl_link_id IS NULL AND R.reference_3 IS NULL AND mta.reference_account = jel.code_combination_id AND NVL(mta.currency_code,sob.currency_code) = jeh.currency_code AND DECODE(mta.encumbrance_type_id, NULL,'A','E') = jeh.actual_flag AND NVL(mta.encumbrance_type_id,-1) = NVL(jeh.encumbrance_type_id,-1) AND NVL(mmt.ussgl_transaction_code,'#ZZZ') = NVL(jeh.ussgl_transaction_code,'#ZZZ') ) ) UNION ALL SELECT 401 , sob.set_of_books_id , mp.organization_code , mmt.organization_id , lu3.meaning , 'INV TRX' , mtt.transaction_type_name , mmt.transaction_type_id , to_char(mmt.transaction_id), mmt.transaction_id , mmt.transaction_date , mmt.transaction_reference , 'MMT' , mmt.transaction_id, lu1.meaning , to_char(mta.accounting_line_type) , mta.reference_account , nvl(mmt.currency_code, sob.currency_code) , DECODE(sign(mta.transaction_value), 1, mta.transaction_value, 0, decode(sign(mta.base_transaction_value), 1, 0, 0, decode(sign(mta.primary_quantity), 1, 0, 0, 0, NULL), NULL), NULL, decode(sign(mta.base_transaction_value), 1, mta.base_transaction_value, 0, decode(sign(mta.primary_quantity), 1, 0, 0, 0, NULL), NULL), NULL) , DECODE(sign(mta.transaction_value), 0, decode(sign(mta.base_transaction_value), -1, 0, 0, decode(sign(mta.primary_quantity), -1, 0, null), null), -1, (-1*mta.transaction_value), NULL, decode(sign(mta.base_transaction_value), -1, (-1*mta.base_transaction_value), 0, decode(sign(mta.primary_quantity), -1, 0, null), null), NULL) , DECODE(sign(mta.base_transaction_value), 0,decode(sign(mta.transaction_value), 0,decode(sign(mta.primary_quantity), -1, NULL, mta.base_transaction_value), -1, null, null, decode(sign(mta.primary_quantity), -1, NULL, mta.base_transaction_value), 0), 1, mta.base_transaction_value, null) , DECODE(sign(mta.base_transaction_value), 0, decode(sign(mta.transaction_value), -1, 0, 0, decode(sign(mta.primary_quantity), -1, 0, null) , null,decode(sign(mta.primary_quantity), -1, 0, null) , null), -1, (-1*mta.base_transaction_value), null) , mmt.currency_conversion_date , mmt.currency_conversion_type , glct.user_conversion_type , mmt.currency_conversion_rate , mmt.transaction_date , lu2.meaning , DECODE(mta.gl_batch_id, -1, 'N', 'Y') , DECODE(mta.gl_batch_id, -1, to_number(null), mta.gl_batch_id) , 'MMT' , mmt.transaction_id, 'MTA' , decode(cce.cost_element, NULL, lu1.meaning, lu1.meaning||',     '||cce.cost_element) , rpad(decode(cce.cost_element, NULL, lu1.meaning, lu1.meaning||',     '||cce.cost_element), 132) /* ---------------------------Product Specific Columns ------------------------*/ , mmt.inventory_item_id , mmt.revision , msi.primary_uom_code , mta.primary_quantity , DECODE(mmt.transaction_type_id, 24, NULL, 80, NULL, DECODE(mta.primary_quantity, 0, NULL, NULL, NULL, ABS(mta.base_transaction_value/mta.primary_quantity))) , mmt.subinventory_code , mmt.locator_id , mil.description , mmt.operation_seq_num , mtst.transaction_source_type_name , mmt.transaction_source_type_id , mmt.transaction_source_name , mmt.transaction_source_id , mtr.reason_name , cce.cost_element , mta.cost_element_id , jel.je_header_id , jel.je_line_num , mta.last_update_date , mta.last_updated_by , mta.creation_date , mta.created_by , mta.last_update_login , mta.request_id , mta.program_application_id , mta.program_id , mta.program_update_date FROM gl_je_lines jel, gl_je_headers jeh, gl_import_references R, gl_period_statuses gps, gl_sets_of_books sob, gl_daily_conversion_types glct, mtl_transaction_accounts mta, cst_cost_elements cce, mtl_material_transactions mmt, mtl_transaction_types mtt, mtl_item_locations mil, mtl_transaction_reasons mtr, mtl_parameters mp, mtl_txn_source_types mtst, mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mtl_system_items_vl msi, mtl_parameters mp1 WHERE /* bug 4390678:specifically for DIT from std to std costing orgs */ ( mp.PRIMARY_COST_METHOD=1 AND mmt.organization_id=mp.organization_id and mp1.PRIMARY_COST_METHOD=1 and mp1.organization_id=mmt.transfer_organization_id ) AND ( ( mmt.transaction_id=mta.transaction_id AND mmt.primary_quantity < 0 ) OR ( mmt.transfer_transaction_id=mta.transaction_id AND mmt.primary_quantity > 0 ) ) AND mta.organization_id = mmt.organization_id AND mmt.transaction_action_id = 3 AND mmt.inventory_item_id = mta.inventory_item_id AND mmt.organization_id = mil.organization_id(+) AND mmt.locator_id = mil.inventory_location_id(+) AND mmt.currency_conversion_type = glct.conversion_type(+) AND mtt.transaction_type_id = mmt.transaction_type_id AND mtst.transaction_source_type_id = mmt.transaction_source_type_id AND mtr.reason_id(+) = mmt.reason_id AND sob.set_of_books_id = gps.set_of_books_id AND gps.period_name = jel.period_name AND gps.application_id = 401 AND msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mmt.organization_id AND cce.cost_element_id(+) = mta.cost_element_id AND lu1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND lu1.lookup_code = mta.accounting_line_type AND lu2.lookup_type = 'SYS_YES_NO' AND lu2.lookup_code = decode(mta.gl_batch_id, -1, 2, 1) AND lu3.lookup_type = 'CST_VIEW_ACCOUNTING' AND lu3.lookup_code = 1 AND jeh.je_header_id = jel.je_header_id AND R.je_header_id = jeh.je_header_id AND R.je_line_num = jel.je_line_num AND jel.ledger_id = sob.set_of_books_id AND mta.gl_batch_id = R.reference_1 AND je_source = 'Inventory' AND je_category = 'MTL' AND ( ( R.gl_sl_link_id IS NOT NULL AND R.reference_3 IS NOT NULL AND mta.transaction_id = R.reference_3 AND mta.gl_sl_link_id = R.gl_sl_link_id ) OR ( R.gl_sl_link_id IS NULL AND R.reference_3 IS NULL AND mta.reference_account = jel.code_combination_id AND NVL(mta.currency_code,sob.currency_code) = jeh.currency_code AND DECODE(mta.encumbrance_type_id, NULL,'A','E') = jeh.actual_flag AND NVL(mta.encumbrance_type_id,-1) = NVL(jeh.encumbrance_type_id,-1) AND NVL(mmt.ussgl_transaction_code,'#ZZZ') = NVL(jeh.ussgl_transaction_code,'#ZZZ') ) ) UNION ALL SELECT 401 , sob.set_of_books_id , mp.organization_code , mta.organization_id , lu3.meaning , 'INV TRX' , mtt.transaction_type_name , mmt.transaction_type_id , to_char(mta.transaction_id), mta.transaction_id , mmt.transaction_date , mmt.transaction_reference , 'MMT' , mta.transaction_id, lu1.meaning , to_char(mta.accounting_line_type) , mta.reference_account , nvl(mmt.currency_code, sob.currency_code) , DECODE(sign(mta.transaction_value), 1, mta.transaction_value, 0, decode(sign(mta.base_transaction_value), 1, 0, 0, decode(sign(mta.primary_quantity), 1, 0, 0, 0, NULL), NULL), NULL,decode(sign(mta.base_transaction_value), 1, mta.base_transaction_value, 0, decode(sign(mta.primary_quantity), 1, 0, 0, 0, NULL), NULL), NULL) , DECODE(sign(mta.transaction_value), 0,decode(sign(mta.base_transaction_value), -1, 0, 0, decode(sign(mta.primary_quantity), -1, 0, null), null), -1,(-1*mta.transaction_value), NULL, decode(sign(mta.base_transaction_value), -1, (-1*mta.base_transaction_value), 0, decode(sign(mta.primary_quantity), -1, 0, null), null), NULL) , DECODE(sign(mta.base_transaction_value), 0, decode(sign(mta.transaction_value), 0,decode(sign(mta.primary_quantity), -1, NULL, mta.base_transaction_value), -1, null, null, decode(sign(mta.primary_quantity), -1, NULL, mta.base_transaction_value), 0), 1,mta.base_transaction_value, null) , DECODE(sign(mta.base_transaction_value), 0, decode(sign(mta.transaction_value), -1, 0, 0, decode(sign(mta.primary_quantity), -1, 0, null) , null, decode(sign(mta.primary_quantity), -1, 0, null) , null), -1, (-1*mta.base_transaction_value), null) , mmt.currency_conversion_date , mmt.currency_conversion_type , glct.user_conversion_type , mmt.currency_conversion_rate , mmt.transaction_date , lu2.meaning , DECODE(mta.gl_batch_id, -1, 'N', 'Y') , DECODE(mta.gl_batch_id, -1, to_number(null), mta.gl_batch_id) , 'MMT' , mta.transaction_id, 'MTA' , decode(cce.cost_element, NULL, lu1.meaning, lu1.meaning||',     '||cce.cost_element) , rpad(decode(cce.cost_element, NULL, lu1.meaning, lu1.meaning||',     '||cce.cost_element), 132) /* ---------------------------Product Specific Columns ------------------------*/ , mmt.inventory_item_id , mmt.revision , msi.primary_uom_code , mta.primary_quantity , DECODE(mmt.transaction_type_id, 24, NULL, 80, NULL, DECODE(mta.primary_quantity, 0, NULL, NULL, NULL, ABS(mta.base_transaction_value/mta.primary_quantity))) , mmt.subinventory_code , mmt.locator_id , mil.description , mmt.operation_seq_num , mtst.transaction_source_type_name , mmt.transaction_source_type_id , mmt.transaction_source_name , mmt.transaction_source_id , mtr.reason_name , cce.cost_element , mta.cost_element_id , jel.je_header_id , jel.je_line_num , mta.last_update_date , mta.last_updated_by , mta.creation_date , mta.created_by , mta.last_update_login , mta.request_id , mta.program_application_id , mta.program_id , mta.program_update_date FROM gl_je_lines jel, gl_je_headers jeh, gl_import_references R, gl_period_statuses gps, gl_sets_of_books sob, gl_daily_conversion_types glct, mtl_transaction_accounts mta, cst_cost_elements cce, mtl_material_transactions mmt, mtl_transaction_types mtt, mtl_item_locations mil, mtl_transaction_reasons mtr, mtl_parameters mp, mtl_txn_source_types mtst, mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mtl_system_items_vl msi, mtl_parameters mp1 WHERE /* for DIT in all other costing org combinations */ ( ( mp.PRIMARY_COST_METHOD <> 1 AND mmt.organization_id = mp.organization_id AND mp1.organization_id = mmt.transfer_organization_id AND mp1.PRIMARY_COST_METHOD <> 1 ) OR ( mp.PRIMARY_COST_METHOD <> 1 AND mmt.organization_id = mp.organization_id AND mp1.organization_id = mmt.transfer_organization_id AND mp1.PRIMARY_COST_METHOD = 1 ) OR ( mp.PRIMARY_COST_METHOD = 1 AND mmt.organization_id = mp.organization_id AND mp1.organization_id = mmt.transfer_organization_id AND mp1.PRIMARY_COST_METHOD <> 1 ) ) AND mmt.transaction_id = mta.transaction_id AND mmt.transaction_action_id = 3 AND mmt.inventory_item_id = mta.inventory_item_id AND mmt.organization_id = mil.organization_id(+) AND mmt.locator_id = mil.inventory_location_id(+) AND mmt.currency_conversion_type = glct.conversion_type(+) AND mtt.transaction_type_id = mmt.transaction_type_id AND mtst.transaction_source_type_id = mmt.transaction_source_type_id AND mtr.reason_id(+) = mmt.reason_id AND sob.set_of_books_id = gps.set_of_books_id AND gps.period_name = jel.period_name AND gps.application_id = 401 AND msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mmt.organization_id AND cce.cost_element_id(+) = mta.cost_element_id AND lu1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND lu1.lookup_code = mta.accounting_line_type AND lu2.lookup_type = 'SYS_YES_NO' AND lu2.lookup_code = decode(mta.gl_batch_id, -1, 2, 1) AND lu3.lookup_type = 'CST_VIEW_ACCOUNTING' AND lu3.lookup_code = 1 AND jeh.je_header_id = jel.je_header_id AND R.je_header_id = jeh.je_header_id AND R.je_line_num = jel.je_line_num AND jel.ledger_id = sob.set_of_books_id AND mta.gl_batch_id = R.reference_1 AND je_source = 'Inventory' AND je_category = 'MTL' AND ( ( R.gl_sl_link_id IS NOT NULL AND R.reference_3 IS NOT NULL AND mta.transaction_id = R.reference_3 AND mta.gl_sl_link_id = R.gl_sl_link_id ) OR ( R.gl_sl_link_id IS NULL AND R.reference_3 IS NULL AND mta.reference_account = jel.code_combination_id AND NVL(mta.currency_code,sob.currency_code) = jeh.currency_code AND DECODE(mta.encumbrance_type_id, NULL,'A','E') = jeh.actual_flag AND NVL(mta.encumbrance_type_id,-1) = NVL(jeh.encumbrance_type_id,-1) AND NVL(mmt.ussgl_transaction_code,'#ZZZ') = NVL(jeh.ussgl_transaction_code,'#ZZZ') ) )

View Text - HTML Formatted

SELECT 401
, SOB.SET_OF_BOOKS_ID
, MP.ORGANIZATION_CODE
, MMT.ORGANIZATION_ID
, LU3.MEANING
, 'INV TRX'
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_TYPE_ID
, TO_CHAR(MMT.TRANSACTION_ID)
, MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_REFERENCE
, 'MMT'
, MMT.TRANSACTION_ID
, LU1.MEANING
, TO_CHAR(MTA.ACCOUNTING_LINE_TYPE)
, MTA.REFERENCE_ACCOUNT
, NVL(MMT.CURRENCY_CODE
, SOB.CURRENCY_CODE)
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, 1
, MTA.TRANSACTION_VALUE
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, 1
, 0
, 0
, 0
, NULL)
, NULL)
, NULL
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 1
, MTA.BASE_TRANSACTION_VALUE
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, 1
, 0
, 0
, 0
, NULL)
, NULL)
, NULL)
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, -1
, (-1*MTA.TRANSACTION_VALUE)
, NULL
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, (-1*MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, NULL)
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, NULL
, MTA.BASE_TRANSACTION_VALUE)
, -1
, NULL
, NULL
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, NULL
, MTA.BASE_TRANSACTION_VALUE)
, 0)
, 1
, MTA.BASE_TRANSACTION_VALUE
, NULL)
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, -1
, (-1*MTA.BASE_TRANSACTION_VALUE)
, NULL)
, MMT.CURRENCY_CONVERSION_DATE
, MMT.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, MMT.CURRENCY_CONVERSION_RATE
, MMT.TRANSACTION_DATE
, LU2.MEANING
, DECODE(MTA.GL_BATCH_ID
, -1
, 'N'
, 'Y')
, DECODE(MTA.GL_BATCH_ID
, -1
, TO_NUMBER(NULL)
, MTA.GL_BATCH_ID)
, 'MMT'
, MMT.TRANSACTION_ID
, 'MTA'
, DECODE(CCE.COST_ELEMENT
, NULL
, LU1.MEANING
, LU1.MEANING||'
, '||CCE.COST_ELEMENT)
, RPAD(DECODE(CCE.COST_ELEMENT
, NULL
, LU1.MEANING
, LU1.MEANING||'
, '||CCE.COST_ELEMENT)
, 132) /* ---------------------------PRODUCT SPECIFIC COLUMNS ------------------------*/
, MMT.INVENTORY_ITEM_ID
, MMT.REVISION
, MSI.PRIMARY_UOM_CODE
, MTA.PRIMARY_QUANTITY
, DECODE(MMT.TRANSACTION_TYPE_ID
, 24
, NULL
, 80
, NULL
, DECODE(MTA.PRIMARY_QUANTITY
, 0
, NULL
, NULL
, NULL
, ABS(MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY)))
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MIL.DESCRIPTION
, MMT.OPERATION_SEQ_NUM
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.TRANSACTION_SOURCE_ID
, MTR.REASON_NAME
, CCE.COST_ELEMENT
, MTA.COST_ELEMENT_ID
, JEL.JE_HEADER_ID
, JEL.JE_LINE_NUM
, MTA.LAST_UPDATE_DATE
, MTA.LAST_UPDATED_BY
, MTA.CREATION_DATE
, MTA.CREATED_BY
, MTA.LAST_UPDATE_LOGIN
, MTA.REQUEST_ID
, MTA.PROGRAM_APPLICATION_ID
, MTA.PROGRAM_ID
, MTA.PROGRAM_UPDATE_DATE
FROM GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, GL_IMPORT_REFERENCES R
, GL_PERIOD_STATUSES GPS
, GL_SETS_OF_BOOKS SOB
, GL_DAILY_CONVERSION_TYPES GLCT
, MTL_TRANSACTION_ACCOUNTS MTA
, CST_COST_ELEMENTS CCE
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_TYPES MTT
, MTL_ITEM_LOCATIONS MIL
, MTL_TRANSACTION_REASONS MTR
, MTL_PARAMETERS MP
, MTL_TXN_SOURCE_TYPES MTST
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MTL_SYSTEM_ITEMS_VL MSI
WHERE MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.TRANSACTION_ACTION_ID NOT IN (2
, 3
, 5)
AND MMT.INVENTORY_ITEM_ID = MTA.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+)
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND MTR.REASON_ID(+) = MMT.REASON_ID
AND SOB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND GPS.PERIOD_NAME = JEL.PERIOD_NAME
AND GPS.APPLICATION_ID = 401
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND CCE.COST_ELEMENT_ID(+) = MTA.COST_ELEMENT_ID
AND LU1.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU1.LOOKUP_CODE = MTA.ACCOUNTING_LINE_TYPE
AND LU2.LOOKUP_TYPE = 'SYS_YES_NO'
AND LU2.LOOKUP_CODE = DECODE(MTA.GL_BATCH_ID
, -1
, 2
, 1)
AND LU3.LOOKUP_TYPE = 'CST_VIEW_ACCOUNTING'
AND LU3.LOOKUP_CODE = 1
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND R.JE_LINE_NUM = JEL.JE_LINE_NUM
AND JEL.LEDGER_ID = SOB.SET_OF_BOOKS_ID
AND MTA.GL_BATCH_ID = R.REFERENCE_1
AND JE_SOURCE = 'INVENTORY'
AND JE_CATEGORY = 'MTL'
AND ( ( R.GL_SL_LINK_ID IS NOT NULL
AND R.REFERENCE_3 IS NOT NULL
AND MTA.TRANSACTION_ID = R.REFERENCE_3
AND MTA.GL_SL_LINK_ID = R.GL_SL_LINK_ID ) OR ( R.GL_SL_LINK_ID IS NULL
AND R.REFERENCE_3 IS NULL
AND MTA.REFERENCE_ACCOUNT = JEL.CODE_COMBINATION_ID
AND NVL(MTA.CURRENCY_CODE
, SOB.CURRENCY_CODE) = JEH.CURRENCY_CODE
AND DECODE(MTA.ENCUMBRANCE_TYPE_ID
, NULL
, 'A'
, 'E') = JEH.ACTUAL_FLAG
AND NVL(MTA.ENCUMBRANCE_TYPE_ID
, -1) = NVL(JEH.ENCUMBRANCE_TYPE_ID
, -1)
AND NVL(MMT.USSGL_TRANSACTION_CODE
, '#ZZZ') = NVL(JEH.USSGL_TRANSACTION_CODE
, '#ZZZ') ) ) UNION ALL SELECT 401
, SOB.SET_OF_BOOKS_ID
, MP.ORGANIZATION_CODE
, MMT.ORGANIZATION_ID
, LU3.MEANING
, 'INV TRX'
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_TYPE_ID
, DECODE(MMT.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, -1
, TO_CHAR(MMT.TRANSACTION_ID)
, TO_CHAR(MMT.TRANSFER_TRANSACTION_ID))
, 5
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, -1
, TO_CHAR(MMT.TRANSACTION_ID)
, TO_CHAR(MMT.TRANSFER_TRANSACTION_ID))
, TO_CHAR(MMT.TRANSACTION_ID))
, DECODE(MMT.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, -1
, MMT.TRANSACTION_ID
, MMT.TRANSFER_TRANSACTION_ID)
, 5
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, -1
, MMT.TRANSACTION_ID
, MMT.TRANSFER_TRANSACTION_ID)
, MMT.TRANSACTION_ID)
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_REFERENCE
, 'MMT'
, DECODE(MMT.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, -1
, MMT.TRANSACTION_ID
, MMT.TRANSFER_TRANSACTION_ID)
, 5
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, -1
, MMT.TRANSACTION_ID
, MMT.TRANSFER_TRANSACTION_ID)
, MMT.TRANSACTION_ID)
, LU1.MEANING
, TO_CHAR(MTA.ACCOUNTING_LINE_TYPE)
, MTA.REFERENCE_ACCOUNT
, NVL(MMT.CURRENCY_CODE
, SOB.CURRENCY_CODE)
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, 1
, MTA.TRANSACTION_VALUE
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, 1
, 0
, 0
, 0
, NULL)
, NULL)
, NULL
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 1
, MTA.BASE_TRANSACTION_VALUE
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, 1
, 0
, 0
, 0
, NULL)
, NULL)
, NULL)
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, -1
, (-1*MTA.TRANSACTION_VALUE)
, NULL
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, (-1*MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, NULL)
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, NULL
, MTA.BASE_TRANSACTION_VALUE)
, -1
, NULL
, NULL
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, NULL
, MTA.BASE_TRANSACTION_VALUE)
, 0)
, 1
, MTA.BASE_TRANSACTION_VALUE
, NULL)
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, -1
, (-1*MTA.BASE_TRANSACTION_VALUE)
, NULL)
, MMT.CURRENCY_CONVERSION_DATE
, MMT.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, MMT.CURRENCY_CONVERSION_RATE
, MMT.TRANSACTION_DATE
, LU2.MEANING
, DECODE(MTA.GL_BATCH_ID
, -1
, 'N'
, 'Y')
, DECODE(MTA.GL_BATCH_ID
, -1
, TO_NUMBER(NULL)
, MTA.GL_BATCH_ID)
, 'MMT'
, DECODE(MMT.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, -1
, MMT.TRANSACTION_ID
, MMT.TRANSFER_TRANSACTION_ID)
, 5
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, -1
, MMT.TRANSACTION_ID
, MMT.TRANSFER_TRANSACTION_ID)
, MMT.TRANSACTION_ID)
, 'MTA'
, DECODE(CCE.COST_ELEMENT
, NULL
, LU1.MEANING
, LU1.MEANING||'
, '||CCE.COST_ELEMENT)
, RPAD(DECODE(CCE.COST_ELEMENT
, NULL
, LU1.MEANING
, LU1.MEANING||'
, '||CCE.COST_ELEMENT)
, 132) /* ---------------------------PRODUCT SPECIFIC COLUMNS ------------------------*/
, MMT.INVENTORY_ITEM_ID
, MMT.REVISION
, MSI.PRIMARY_UOM_CODE
, MTA.PRIMARY_QUANTITY
, DECODE(MMT.TRANSACTION_TYPE_ID
, 24
, NULL
, 80
, NULL
, DECODE(MTA.PRIMARY_QUANTITY
, 0
, NULL
, NULL
, NULL
, ABS(MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY)))
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MIL.DESCRIPTION
, MMT.OPERATION_SEQ_NUM
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.TRANSACTION_SOURCE_ID
, MTR.REASON_NAME
, CCE.COST_ELEMENT
, MTA.COST_ELEMENT_ID
, JEL.JE_HEADER_ID
, JEL.JE_LINE_NUM
, MTA.LAST_UPDATE_DATE
, MTA.LAST_UPDATED_BY
, MTA.CREATION_DATE
, MTA.CREATED_BY
, MTA.LAST_UPDATE_LOGIN
, MTA.REQUEST_ID
, MTA.PROGRAM_APPLICATION_ID
, MTA.PROGRAM_ID
, MTA.PROGRAM_UPDATE_DATE
FROM GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, GL_IMPORT_REFERENCES R
, GL_PERIOD_STATUSES GPS
, GL_SETS_OF_BOOKS SOB
, GL_DAILY_CONVERSION_TYPES GLCT
, MTL_TRANSACTION_ACCOUNTS MTA
, CST_COST_ELEMENTS CCE
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_TYPES MTT
, MTL_ITEM_LOCATIONS MIL
, MTL_TRANSACTION_REASONS MTR
, MTL_PARAMETERS MP
, MTL_TXN_SOURCE_TYPES MTST
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MTL_SYSTEM_ITEMS_VL MSI
WHERE /* SPECIFICALLY TRANSFER TYPE TRANSACTIONS */ MMT.TRANSFER_TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.TRANSACTION_ACTION_ID IN (2
, 5)
AND MMT.INVENTORY_ITEM_ID = MTA.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+)
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND MTR.REASON_ID(+) = MMT.REASON_ID
AND SOB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND GPS.PERIOD_NAME = JEL.PERIOD_NAME
AND GPS.APPLICATION_ID = 401
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND CCE.COST_ELEMENT_ID(+) = MTA.COST_ELEMENT_ID
AND LU1.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU1.LOOKUP_CODE = MTA.ACCOUNTING_LINE_TYPE
AND LU2.LOOKUP_TYPE = 'SYS_YES_NO'
AND LU2.LOOKUP_CODE = DECODE(MTA.GL_BATCH_ID
, -1
, 2
, 1)
AND LU3.LOOKUP_TYPE = 'CST_VIEW_ACCOUNTING'
AND LU3.LOOKUP_CODE = 1
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND R.JE_LINE_NUM = JEL.JE_LINE_NUM
AND JEL.LEDGER_ID = SOB.SET_OF_BOOKS_ID
AND MTA.GL_BATCH_ID = R.REFERENCE_1
AND JE_SOURCE = 'INVENTORY'
AND JE_CATEGORY = 'MTL'
AND ( ( R.GL_SL_LINK_ID IS NOT NULL
AND R.REFERENCE_3 IS NOT NULL
AND MTA.TRANSACTION_ID = R.REFERENCE_3
AND MTA.GL_SL_LINK_ID = R.GL_SL_LINK_ID ) OR ( R.GL_SL_LINK_ID IS NULL
AND R.REFERENCE_3 IS NULL
AND MTA.REFERENCE_ACCOUNT = JEL.CODE_COMBINATION_ID
AND NVL(MTA.CURRENCY_CODE
, SOB.CURRENCY_CODE) = JEH.CURRENCY_CODE
AND DECODE(MTA.ENCUMBRANCE_TYPE_ID
, NULL
, 'A'
, 'E') = JEH.ACTUAL_FLAG
AND NVL(MTA.ENCUMBRANCE_TYPE_ID
, -1) = NVL(JEH.ENCUMBRANCE_TYPE_ID
, -1)
AND NVL(MMT.USSGL_TRANSACTION_CODE
, '#ZZZ') = NVL(JEH.USSGL_TRANSACTION_CODE
, '#ZZZ') ) ) UNION ALL SELECT 401
, SOB.SET_OF_BOOKS_ID
, MP.ORGANIZATION_CODE
, MMT.ORGANIZATION_ID
, LU3.MEANING
, 'INV TRX'
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_TYPE_ID
, TO_CHAR(MMT.TRANSACTION_ID)
, MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_REFERENCE
, 'MMT'
, MMT.TRANSACTION_ID
, LU1.MEANING
, TO_CHAR(MTA.ACCOUNTING_LINE_TYPE)
, MTA.REFERENCE_ACCOUNT
, NVL(MMT.CURRENCY_CODE
, SOB.CURRENCY_CODE)
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, 1
, MTA.TRANSACTION_VALUE
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, 1
, 0
, 0
, 0
, NULL)
, NULL)
, NULL
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 1
, MTA.BASE_TRANSACTION_VALUE
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, 1
, 0
, 0
, 0
, NULL)
, NULL)
, NULL)
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, -1
, (-1*MTA.TRANSACTION_VALUE)
, NULL
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, (-1*MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, NULL)
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, NULL
, MTA.BASE_TRANSACTION_VALUE)
, -1
, NULL
, NULL
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, NULL
, MTA.BASE_TRANSACTION_VALUE)
, 0)
, 1
, MTA.BASE_TRANSACTION_VALUE
, NULL)
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, -1
, (-1*MTA.BASE_TRANSACTION_VALUE)
, NULL)
, MMT.CURRENCY_CONVERSION_DATE
, MMT.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, MMT.CURRENCY_CONVERSION_RATE
, MMT.TRANSACTION_DATE
, LU2.MEANING
, DECODE(MTA.GL_BATCH_ID
, -1
, 'N'
, 'Y')
, DECODE(MTA.GL_BATCH_ID
, -1
, TO_NUMBER(NULL)
, MTA.GL_BATCH_ID)
, 'MMT'
, MMT.TRANSACTION_ID
, 'MTA'
, DECODE(CCE.COST_ELEMENT
, NULL
, LU1.MEANING
, LU1.MEANING||'
, '||CCE.COST_ELEMENT)
, RPAD(DECODE(CCE.COST_ELEMENT
, NULL
, LU1.MEANING
, LU1.MEANING||'
, '||CCE.COST_ELEMENT)
, 132) /* ---------------------------PRODUCT SPECIFIC COLUMNS ------------------------*/
, MMT.INVENTORY_ITEM_ID
, MMT.REVISION
, MSI.PRIMARY_UOM_CODE
, MTA.PRIMARY_QUANTITY
, DECODE(MMT.TRANSACTION_TYPE_ID
, 24
, NULL
, 80
, NULL
, DECODE(MTA.PRIMARY_QUANTITY
, 0
, NULL
, NULL
, NULL
, ABS(MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY)))
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MIL.DESCRIPTION
, MMT.OPERATION_SEQ_NUM
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.TRANSACTION_SOURCE_ID
, MTR.REASON_NAME
, CCE.COST_ELEMENT
, MTA.COST_ELEMENT_ID
, JEL.JE_HEADER_ID
, JEL.JE_LINE_NUM
, MTA.LAST_UPDATE_DATE
, MTA.LAST_UPDATED_BY
, MTA.CREATION_DATE
, MTA.CREATED_BY
, MTA.LAST_UPDATE_LOGIN
, MTA.REQUEST_ID
, MTA.PROGRAM_APPLICATION_ID
, MTA.PROGRAM_ID
, MTA.PROGRAM_UPDATE_DATE
FROM GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, GL_IMPORT_REFERENCES R
, GL_PERIOD_STATUSES GPS
, GL_SETS_OF_BOOKS SOB
, GL_DAILY_CONVERSION_TYPES GLCT
, MTL_TRANSACTION_ACCOUNTS MTA
, CST_COST_ELEMENTS CCE
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_TYPES MTT
, MTL_ITEM_LOCATIONS MIL
, MTL_TRANSACTION_REASONS MTR
, MTL_PARAMETERS MP
, MTL_TXN_SOURCE_TYPES MTST
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MTL_SYSTEM_ITEMS_VL MSI
, MTL_PARAMETERS MP1
WHERE /* BUG 4390678:SPECIFICALLY FOR DIT
FROM STD TO STD COSTING ORGS */ ( MP.PRIMARY_COST_METHOD=1
AND MMT.ORGANIZATION_ID=MP.ORGANIZATION_ID
AND MP1.PRIMARY_COST_METHOD=1
AND MP1.ORGANIZATION_ID=MMT.TRANSFER_ORGANIZATION_ID )
AND ( ( MMT.TRANSACTION_ID=MTA.TRANSACTION_ID
AND MMT.PRIMARY_QUANTITY < 0 ) OR ( MMT.TRANSFER_TRANSACTION_ID=MTA.TRANSACTION_ID
AND MMT.PRIMARY_QUANTITY > 0 ) )
AND MTA.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MMT.TRANSACTION_ACTION_ID = 3
AND MMT.INVENTORY_ITEM_ID = MTA.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+)
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND MTR.REASON_ID(+) = MMT.REASON_ID
AND SOB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND GPS.PERIOD_NAME = JEL.PERIOD_NAME
AND GPS.APPLICATION_ID = 401
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND CCE.COST_ELEMENT_ID(+) = MTA.COST_ELEMENT_ID
AND LU1.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU1.LOOKUP_CODE = MTA.ACCOUNTING_LINE_TYPE
AND LU2.LOOKUP_TYPE = 'SYS_YES_NO'
AND LU2.LOOKUP_CODE = DECODE(MTA.GL_BATCH_ID
, -1
, 2
, 1)
AND LU3.LOOKUP_TYPE = 'CST_VIEW_ACCOUNTING'
AND LU3.LOOKUP_CODE = 1
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND R.JE_LINE_NUM = JEL.JE_LINE_NUM
AND JEL.LEDGER_ID = SOB.SET_OF_BOOKS_ID
AND MTA.GL_BATCH_ID = R.REFERENCE_1
AND JE_SOURCE = 'INVENTORY'
AND JE_CATEGORY = 'MTL'
AND ( ( R.GL_SL_LINK_ID IS NOT NULL
AND R.REFERENCE_3 IS NOT NULL
AND MTA.TRANSACTION_ID = R.REFERENCE_3
AND MTA.GL_SL_LINK_ID = R.GL_SL_LINK_ID ) OR ( R.GL_SL_LINK_ID IS NULL
AND R.REFERENCE_3 IS NULL
AND MTA.REFERENCE_ACCOUNT = JEL.CODE_COMBINATION_ID
AND NVL(MTA.CURRENCY_CODE
, SOB.CURRENCY_CODE) = JEH.CURRENCY_CODE
AND DECODE(MTA.ENCUMBRANCE_TYPE_ID
, NULL
, 'A'
, 'E') = JEH.ACTUAL_FLAG
AND NVL(MTA.ENCUMBRANCE_TYPE_ID
, -1) = NVL(JEH.ENCUMBRANCE_TYPE_ID
, -1)
AND NVL(MMT.USSGL_TRANSACTION_CODE
, '#ZZZ') = NVL(JEH.USSGL_TRANSACTION_CODE
, '#ZZZ') ) ) UNION ALL SELECT 401
, SOB.SET_OF_BOOKS_ID
, MP.ORGANIZATION_CODE
, MTA.ORGANIZATION_ID
, LU3.MEANING
, 'INV TRX'
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_TYPE_ID
, TO_CHAR(MTA.TRANSACTION_ID)
, MTA.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_REFERENCE
, 'MMT'
, MTA.TRANSACTION_ID
, LU1.MEANING
, TO_CHAR(MTA.ACCOUNTING_LINE_TYPE)
, MTA.REFERENCE_ACCOUNT
, NVL(MMT.CURRENCY_CODE
, SOB.CURRENCY_CODE)
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, 1
, MTA.TRANSACTION_VALUE
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, 1
, 0
, 0
, 0
, NULL)
, NULL)
, NULL
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 1
, MTA.BASE_TRANSACTION_VALUE
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, 1
, 0
, 0
, 0
, NULL)
, NULL)
, NULL)
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, -1
, (-1*MTA.TRANSACTION_VALUE)
, NULL
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, (-1*MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, NULL)
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, NULL
, MTA.BASE_TRANSACTION_VALUE)
, -1
, NULL
, NULL
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, NULL
, MTA.BASE_TRANSACTION_VALUE)
, 0)
, 1
, MTA.BASE_TRANSACTION_VALUE
, NULL)
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, -1
, (-1*MTA.BASE_TRANSACTION_VALUE)
, NULL)
, MMT.CURRENCY_CONVERSION_DATE
, MMT.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, MMT.CURRENCY_CONVERSION_RATE
, MMT.TRANSACTION_DATE
, LU2.MEANING
, DECODE(MTA.GL_BATCH_ID
, -1
, 'N'
, 'Y')
, DECODE(MTA.GL_BATCH_ID
, -1
, TO_NUMBER(NULL)
, MTA.GL_BATCH_ID)
, 'MMT'
, MTA.TRANSACTION_ID
, 'MTA'
, DECODE(CCE.COST_ELEMENT
, NULL
, LU1.MEANING
, LU1.MEANING||'
, '||CCE.COST_ELEMENT)
, RPAD(DECODE(CCE.COST_ELEMENT
, NULL
, LU1.MEANING
, LU1.MEANING||'
, '||CCE.COST_ELEMENT)
, 132) /* ---------------------------PRODUCT SPECIFIC COLUMNS ------------------------*/
, MMT.INVENTORY_ITEM_ID
, MMT.REVISION
, MSI.PRIMARY_UOM_CODE
, MTA.PRIMARY_QUANTITY
, DECODE(MMT.TRANSACTION_TYPE_ID
, 24
, NULL
, 80
, NULL
, DECODE(MTA.PRIMARY_QUANTITY
, 0
, NULL
, NULL
, NULL
, ABS(MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY)))
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MIL.DESCRIPTION
, MMT.OPERATION_SEQ_NUM
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.TRANSACTION_SOURCE_ID
, MTR.REASON_NAME
, CCE.COST_ELEMENT
, MTA.COST_ELEMENT_ID
, JEL.JE_HEADER_ID
, JEL.JE_LINE_NUM
, MTA.LAST_UPDATE_DATE
, MTA.LAST_UPDATED_BY
, MTA.CREATION_DATE
, MTA.CREATED_BY
, MTA.LAST_UPDATE_LOGIN
, MTA.REQUEST_ID
, MTA.PROGRAM_APPLICATION_ID
, MTA.PROGRAM_ID
, MTA.PROGRAM_UPDATE_DATE
FROM GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, GL_IMPORT_REFERENCES R
, GL_PERIOD_STATUSES GPS
, GL_SETS_OF_BOOKS SOB
, GL_DAILY_CONVERSION_TYPES GLCT
, MTL_TRANSACTION_ACCOUNTS MTA
, CST_COST_ELEMENTS CCE
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_TYPES MTT
, MTL_ITEM_LOCATIONS MIL
, MTL_TRANSACTION_REASONS MTR
, MTL_PARAMETERS MP
, MTL_TXN_SOURCE_TYPES MTST
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MTL_SYSTEM_ITEMS_VL MSI
, MTL_PARAMETERS MP1
WHERE /* FOR DIT IN ALL OTHER COSTING ORG COMBINATIONS */ ( ( MP.PRIMARY_COST_METHOD <> 1
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP1.ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID
AND MP1.PRIMARY_COST_METHOD <> 1 ) OR ( MP.PRIMARY_COST_METHOD <> 1
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP1.ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID
AND MP1.PRIMARY_COST_METHOD = 1 ) OR ( MP.PRIMARY_COST_METHOD = 1
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP1.ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID
AND MP1.PRIMARY_COST_METHOD <> 1 ) )
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.TRANSACTION_ACTION_ID = 3
AND MMT.INVENTORY_ITEM_ID = MTA.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+)
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND MTR.REASON_ID(+) = MMT.REASON_ID
AND SOB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND GPS.PERIOD_NAME = JEL.PERIOD_NAME
AND GPS.APPLICATION_ID = 401
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND CCE.COST_ELEMENT_ID(+) = MTA.COST_ELEMENT_ID
AND LU1.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU1.LOOKUP_CODE = MTA.ACCOUNTING_LINE_TYPE
AND LU2.LOOKUP_TYPE = 'SYS_YES_NO'
AND LU2.LOOKUP_CODE = DECODE(MTA.GL_BATCH_ID
, -1
, 2
, 1)
AND LU3.LOOKUP_TYPE = 'CST_VIEW_ACCOUNTING'
AND LU3.LOOKUP_CODE = 1
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND R.JE_LINE_NUM = JEL.JE_LINE_NUM
AND JEL.LEDGER_ID = SOB.SET_OF_BOOKS_ID
AND MTA.GL_BATCH_ID = R.REFERENCE_1
AND JE_SOURCE = 'INVENTORY'
AND JE_CATEGORY = 'MTL'
AND ( ( R.GL_SL_LINK_ID IS NOT NULL
AND R.REFERENCE_3 IS NOT NULL
AND MTA.TRANSACTION_ID = R.REFERENCE_3
AND MTA.GL_SL_LINK_ID = R.GL_SL_LINK_ID ) OR ( R.GL_SL_LINK_ID IS NULL
AND R.REFERENCE_3 IS NULL
AND MTA.REFERENCE_ACCOUNT = JEL.CODE_COMBINATION_ID
AND NVL(MTA.CURRENCY_CODE
, SOB.CURRENCY_CODE) = JEH.CURRENCY_CODE
AND DECODE(MTA.ENCUMBRANCE_TYPE_ID
, NULL
, 'A'
, 'E') = JEH.ACTUAL_FLAG
AND NVL(MTA.ENCUMBRANCE_TYPE_ID
, -1) = NVL(JEH.ENCUMBRANCE_TYPE_ID
, -1)
AND NVL(MMT.USSGL_TRANSACTION_CODE
, '#ZZZ') = NVL(JEH.USSGL_TRANSACTION_CODE
, '#ZZZ') ) )