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_SL_INV_V

Source

View Text - Preformatted


SELECT 401 , ood.set_of_books_id , ood.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), -1, (-1*mta.transaction_value), 0,decode(sign(mta.base_transaction_value), -1,0, 0, decode(sign(mta.primary_quantity), -1,0,NULL), NULL), 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 ,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_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, org_organization_definitions ood, mtl_txn_source_types mtst, mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mtl_system_items_vl msi WHERE mmt.transaction_action_id IN (2,5) AND mmt.primary_quantity < 0 AND mmt.transaction_id = mta.transaction_id AND mmt.primary_quantity = mta.primary_quantity AND mmt.organization_id = mil.organization_id(+) AND mmt.locator_id = mil.inventory_location_id(+) AND cce.cost_element_id(+) = mta.cost_element_id AND mtt.transaction_type_id = mmt.transaction_type_id AND mtst.transaction_source_type_id = mmt.transaction_source_type_id AND msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mmt.organization_id AND mtr.reason_id(+) = mmt.reason_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 mmt.organization_id = ood.organization_id AND sob.set_of_books_id = ood.set_of_books_id AND mmt.currency_conversion_type = glct.conversion_type(+) UNION SELECT 401 , ood.set_of_books_id , ood.organization_code , mmt.organization_id , lu3.meaning , 'INV TRX' , mtt.transaction_type_name , mmt.transaction_type_id , to_char(mmt.transfer_transaction_id) , mmt.transfer_transaction_id , mmt.transaction_date , mmt.transaction_reference , 'MMT' , mmt.transfer_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), -1, (-1*mta.transaction_value), 0,decode(sign(mta.base_transaction_value), -1,0, 0, decode(sign(mta.primary_quantity), -1,0,NULL), NULL), 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.transfer_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 ,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_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, org_organization_definitions ood, mtl_txn_source_types mtst, mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mtl_system_items_vl msi WHERE mmt.transaction_action_id IN (2,5) AND mmt.primary_quantity > 0 AND mmt.transfer_transaction_id = mta.transaction_id AND mmt.primary_quantity = mta.primary_quantity AND mmt.organization_id = mil.organization_id(+) AND mmt.locator_id = mil.inventory_location_id(+) AND cce.cost_element_id(+) = mta.cost_element_id AND mtt.transaction_type_id = mmt.transaction_type_id AND mtst.transaction_source_type_id = mmt.transaction_source_type_id AND msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mmt.organization_id AND mtr.reason_id(+) = mmt.reason_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 mmt.organization_id = ood.organization_id AND sob.set_of_books_id = ood.set_of_books_id AND mmt.currency_conversion_type = glct.conversion_type(+) UNION SELECT 401 , ood.set_of_books_id , ood.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), -1, (-1*mta.transaction_value), 0,decode(sign(mta.base_transaction_value), -1,0, 0, decode(sign(mta.primary_quantity), -1,0,NULL), NULL), 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 ,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_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, org_organization_definitions ood, mtl_txn_source_types mtst, mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mtl_system_items_vl msi WHERE mmt.transaction_action_id = 3 AND mmt.organization_id = mta.organization_id AND mmt.transaction_id = mta.transaction_id AND mmt.organization_id = mil.organization_id(+) AND mmt.locator_id = mil.inventory_location_id(+) AND cce.cost_element_id(+) = mta.cost_element_id AND mtt.transaction_type_id = mmt.transaction_type_id AND mtst.transaction_source_type_id = mmt.transaction_source_type_id AND msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mmt.organization_id AND mtr.reason_id(+) = mmt.reason_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 mmt.organization_id = ood.organization_id AND sob.set_of_books_id = ood.set_of_books_id AND mmt.currency_conversion_type = glct.conversion_type(+) UNION SELECT 401 , ood.set_of_books_id , ood.organization_code , mmt.organization_id , lu3.meaning , 'INV TRX' , mtt.transaction_type_name , mmt.transaction_type_id , to_char(mmt.transfer_transaction_id) , mmt.transfer_transaction_id , mmt.transaction_date , mmt.transaction_reference , 'MMT' , mmt.transfer_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), -1, (-1*mta.transaction_value), 0,decode(sign(mta.base_transaction_value), -1,0, 0, decode(sign(mta.primary_quantity), -1,0,NULL), NULL), 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.transfer_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 ,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_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, org_organization_definitions ood, mtl_txn_source_types mtst, mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mtl_system_items_vl msi WHERE mmt.transaction_action_id = 3 AND mmt.organization_id = mta.organization_id AND mmt.transfer_transaction_id = mta.transaction_id AND mmt.organization_id = mil.organization_id(+) AND mmt.locator_id = mil.inventory_location_id(+) AND cce.cost_element_id(+) = mta.cost_element_id AND mtt.transaction_type_id = mmt.transaction_type_id AND mtst.transaction_source_type_id = mmt.transaction_source_type_id AND msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mmt.organization_id AND mtr.reason_id(+) = mmt.reason_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 mmt.organization_id = ood.organization_id AND sob.set_of_books_id = ood.set_of_books_id AND mmt.currency_conversion_type = glct.conversion_type(+) UNION SELECT 401 , ood.set_of_books_id , ood.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,decode (mta.accounting_line_type, 1,0, 14,0, NULL), NULL), NULL), NULL,decode(sign(mta.base_transaction_value), 1,mta.base_transaction_value, 0, decode(sign(mta.primary_quantity), 1,0, 0,decode (mta.accounting_line_type, 1,0, 14,0, NULL), NULL), NULL), NULL) , DECODE(sign(mta.transaction_value), -1, (-1*mta.transaction_value), 0,decode(sign(mta.base_transaction_value), -1,0, 0, decode(sign(mta.primary_quantity), -1,0, 0,decode(mta.accounting_line_type, 2,0,NULL), NULL), NULL), NULL,decode(sign(mta.base_transaction_value), -1,(-1 * mta.base_transaction_value), 0, decode(sign(mta.primary_quantity), -1,0, 0,decode(mta.accounting_line_type, 2,0,NULL), NULL), NULL), NULL) ,DECODE(sign(mta.base_transaction_value), 0,decode(sign(mta.transaction_value), -1,NULL, 0,decode(sign(mta.primary_quantity), -1, NULL, 0, decode (mta.accounting_line_type, 1, 0, 14, 0, NULL), 0), NULL,decode(sign(mta.primary_quantity), -1, NULL, 0, decode (mta.accounting_line_type, 1, 0, 14, 0, NULL), 0), 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, 0, decode(mta.accounting_line_type, 2, 0, NULL), NULL), NULL,decode(sign(mta.primary_quantity), -1,0, 0, decode(mta.accounting_line_type, 2, 0, NULL), 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 ,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_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, org_organization_definitions ood, mtl_txn_source_types mtst, mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mtl_system_items_vl msi WHERE mmt.transaction_action_id <> 2 AND mmt.transaction_action_id <> 5 AND mmt.transaction_action_id <> 3 AND mmt.transaction_id = mta.transaction_id AND mmt.organization_id = mil.organization_id(+) AND mmt.locator_id = mil.inventory_location_id(+) AND cce.cost_element_id(+) = mta.cost_element_id AND mtt.transaction_type_id = mmt.transaction_type_id AND mtst.transaction_source_type_id = mmt.transaction_source_type_id AND msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mmt.organization_id AND mtr.reason_id(+) = mmt.reason_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 mmt.organization_id = ood.organization_id AND sob.set_of_books_id = ood.set_of_books_id AND mmt.currency_conversion_type = glct.conversion_type(+)

View Text - HTML Formatted

SELECT 401
, OOD.SET_OF_BOOKS_ID
, OOD.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)
, -1
, (-1*MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, 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
, 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_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
, ORG_ORGANIZATION_DEFINITIONS OOD
, MTL_TXN_SOURCE_TYPES MTST
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MTL_SYSTEM_ITEMS_VL MSI
WHERE MMT.TRANSACTION_ACTION_ID IN (2
, 5)
AND MMT.PRIMARY_QUANTITY < 0
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.PRIMARY_QUANTITY = MTA.PRIMARY_QUANTITY
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND CCE.COST_ELEMENT_ID(+) = MTA.COST_ELEMENT_ID
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MTR.REASON_ID(+) = MMT.REASON_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 MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+) UNION SELECT 401
, OOD.SET_OF_BOOKS_ID
, OOD.ORGANIZATION_CODE
, MMT.ORGANIZATION_ID
, LU3.MEANING
, 'INV TRX'
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_TYPE_ID
, TO_CHAR(MMT.TRANSFER_TRANSACTION_ID)
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_REFERENCE
, 'MMT'
, MMT.TRANSFER_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)
, -1
, (-1*MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, 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.TRANSFER_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
, 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_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
, ORG_ORGANIZATION_DEFINITIONS OOD
, MTL_TXN_SOURCE_TYPES MTST
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MTL_SYSTEM_ITEMS_VL MSI
WHERE MMT.TRANSACTION_ACTION_ID IN (2
, 5)
AND MMT.PRIMARY_QUANTITY > 0
AND MMT.TRANSFER_TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.PRIMARY_QUANTITY = MTA.PRIMARY_QUANTITY
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND CCE.COST_ELEMENT_ID(+) = MTA.COST_ELEMENT_ID
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MTR.REASON_ID(+) = MMT.REASON_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 MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+) UNION SELECT 401
, OOD.SET_OF_BOOKS_ID
, OOD.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)
, -1
, (-1*MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, 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
, 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_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
, ORG_ORGANIZATION_DEFINITIONS OOD
, MTL_TXN_SOURCE_TYPES MTST
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MTL_SYSTEM_ITEMS_VL MSI
WHERE MMT.TRANSACTION_ACTION_ID = 3
AND MMT.ORGANIZATION_ID = MTA.ORGANIZATION_ID
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND CCE.COST_ELEMENT_ID(+) = MTA.COST_ELEMENT_ID
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MTR.REASON_ID(+) = MMT.REASON_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 MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+) UNION SELECT 401
, OOD.SET_OF_BOOKS_ID
, OOD.ORGANIZATION_CODE
, MMT.ORGANIZATION_ID
, LU3.MEANING
, 'INV TRX'
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_TYPE_ID
, TO_CHAR(MMT.TRANSFER_TRANSACTION_ID)
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_REFERENCE
, 'MMT'
, MMT.TRANSFER_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)
, -1
, (-1*MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, NULL)
, NULL)
, 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.TRANSFER_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
, 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_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
, ORG_ORGANIZATION_DEFINITIONS OOD
, MTL_TXN_SOURCE_TYPES MTST
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MTL_SYSTEM_ITEMS_VL MSI
WHERE MMT.TRANSACTION_ACTION_ID = 3
AND MMT.ORGANIZATION_ID = MTA.ORGANIZATION_ID
AND MMT.TRANSFER_TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND CCE.COST_ELEMENT_ID(+) = MTA.COST_ELEMENT_ID
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MTR.REASON_ID(+) = MMT.REASON_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 MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+) UNION SELECT 401
, OOD.SET_OF_BOOKS_ID
, OOD.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
, DECODE (MTA.ACCOUNTING_LINE_TYPE
, 1
, 0
, 14
, 0
, NULL)
, NULL)
, NULL)
, NULL
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 1
, MTA.BASE_TRANSACTION_VALUE
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, 1
, 0
, 0
, DECODE (MTA.ACCOUNTING_LINE_TYPE
, 1
, 0
, 14
, 0
, NULL)
, NULL)
, NULL)
, NULL)
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, -1
, (-1*MTA.TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, 0
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, 0
, DECODE(MTA.ACCOUNTING_LINE_TYPE
, 2
, 0
, NULL)
, NULL)
, NULL)
, NULL
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, -1
, (-1 * MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, 0
, DECODE(MTA.ACCOUNTING_LINE_TYPE
, 2
, 0
, NULL)
, NULL)
, NULL)
, NULL)
, DECODE(SIGN(MTA.BASE_TRANSACTION_VALUE)
, 0
, DECODE(SIGN(MTA.TRANSACTION_VALUE)
, -1
, NULL
, 0
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, NULL
, 0
, DECODE (MTA.ACCOUNTING_LINE_TYPE
, 1
, 0
, 14
, 0
, NULL)
, 0)
, NULL
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, NULL
, 0
, DECODE (MTA.ACCOUNTING_LINE_TYPE
, 1
, 0
, 14
, 0
, NULL)
, 0)
, 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
, 0
, DECODE(MTA.ACCOUNTING_LINE_TYPE
, 2
, 0
, NULL)
, NULL)
, NULL
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, -1
, 0
, 0
, DECODE(MTA.ACCOUNTING_LINE_TYPE
, 2
, 0
, NULL)
, 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
, 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_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
, ORG_ORGANIZATION_DEFINITIONS OOD
, MTL_TXN_SOURCE_TYPES MTST
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MTL_SYSTEM_ITEMS_VL MSI
WHERE MMT.TRANSACTION_ACTION_ID <> 2
AND MMT.TRANSACTION_ACTION_ID <> 5
AND MMT.TRANSACTION_ACTION_ID <> 3
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND CCE.COST_ELEMENT_ID(+) = MTA.COST_ELEMENT_ID
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MTR.REASON_ID(+) = MMT.REASON_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 MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+)