VIEW: APPS.CST_PAC_AEL_SL_INV_V

Source

View Text - Preformatted

SELECT clct.legal_entity, ccga.cost_group_id, ccg.cost_group, clct.cost_type_id, cct.cost_type, ccg.organization_id, clct.primary_cost_method, 401, cah.set_of_books_id, ood.organization_code, ccga.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, cal.ae_line_type_code, cal.code_combination_id, cal.currency_code, cal.entered_dr, cal.entered_cr, cal.accounted_dr, cal.accounted_cr, cal.currency_conversion_date, cal.currency_conversion_type, glct.user_conversion_type, cal.currency_conversion_rate, mmt.transaction_date, lu2.meaning, DECODE(NVL(cal.gl_sl_link_id, -1), -1, 'N','Y') , cah.gl_transfer_run_id , 'MMT', mmt.transaction_id, 'CAL' , 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), mmt.inventory_item_id, mmt.revision, mmt.transaction_uom, mmt.transaction_quantity, to_char(decode(mmt.transaction_quantity, 0, 0, nvl(cal.accounted_dr,cal.accounted_cr) / abs(mmt.transaction_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, cal.cost_element_id , cal.last_update_date , cal.last_updated_by , cal.creation_date , cal.created_by , cal.last_update_login , cal.request_id , cal.program_application_id , cal.program_id , cal.program_update_date FROM mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mtl_transaction_reasons mtr, mtl_txn_source_types mtst, mtl_transaction_types mtt, gl_daily_conversion_types glct, cst_cost_elements cce, mtl_item_locations mil, mtl_material_transactions mmt, org_organization_definitions ood, cst_ae_headers cah, cst_ae_lines cal, cst_le_cost_types clct, cst_cost_groups ccg, cst_cost_types cct, cst_cost_group_assignments ccga WHERE ((mmt.transaction_action_id IN (2,5) and mmt.primary_quantity < 0 and cal.accounted_cr is not null) or (mmt.transaction_action_id = 3 and mmt.organization_id = cal.reference2) or (mmt.transaction_action_id not in (2,3,5) and mmt.transaction_type_id <> 26)) AND mmt.transaction_id=cah.accounting_event_id AND mmt.organization_id = mil.organization_id(+) AND mmt.locator_id = mil.inventory_location_id(+) AND cce.cost_element_id(+)=cal.cost_element_id 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 lu1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND lu1.lookup_code = cal.ae_line_type_code AND lu2.lookup_type = 'SYS_YES_NO' AND lu2.lookup_code = DECODE(NVL(cal.gl_sl_link_id, -1), -1, 2, 1) AND lu3.lookup_type = 'CST_VIEW_ACCOUNTING' AND lu3.lookup_code = 1 AND ccga.organization_id = ood.organization_id AND cah.set_of_books_id = clct.set_of_books_id AND mmt.currency_conversion_type = glct.conversion_type(+) AND ( (ccga.organization_id = mmt.organization_id) OR (ccga.organization_id = mmt.transfer_organization_id AND ccga.cost_group_id NOT IN (SELECT cost_group_id FROM cst_cost_group_assignments ccga2 WHERE ccga2.organization_id = mmt.organization_id) AND ( (mmt.transaction_action_id = 21 AND mmt.fob_point = 1) OR (mmt.transaction_action_id = 12 AND mmt.fob_point = 2) ) ) ) AND ccga.cost_group_id = ccg.cost_group_id AND clct.legal_entity = ccg.legal_entity AND ccg.cost_group_type= 2 AND cct.cost_type_id = clct.cost_type_id AND cah.ae_header_id = cal.ae_header_id AND cah.legal_entity_id=clct.legal_entity AND cah.cost_type_id = clct.cost_type_id AND cah.cost_group_id = ccg.cost_group_id AND cah.acct_event_source_table = 'MMT' UNION ALL SELECT clct.legal_entity, ccga.cost_group_id, ccg.cost_group, clct.cost_type_id, cct.cost_type, ccg.organization_id, clct.primary_cost_method, 401, cah.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, cal.ae_line_type_code, cal.code_combination_id, cal.currency_code, cal.entered_dr, cal.entered_cr, cal.accounted_dr, cal.accounted_cr, cal.currency_conversion_date, cal.currency_conversion_type, glct.user_conversion_type, cal.currency_conversion_rate, mmt.transaction_date, lu2.meaning, DECODE(NVL(cal.gl_sl_link_id, -1), -1, 'N','Y'), cah.gl_transfer_run_id , 'MMT', mmt.transfer_transaction_id, 'CAL' , 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), mmt.inventory_item_id, mmt.revision, mmt.transaction_uom, mmt.transaction_quantity, to_char(decode(mmt.transaction_quantity, 0, 0, nvl(cal.accounted_dr,cal.accounted_cr)/abs(mmt.transaction_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, cal.cost_element_id , cal.last_update_date , cal.last_updated_by , cal.creation_date , cal.created_by , cal.last_update_login , cal.request_id , cal.program_application_id, cal.program_id , cal.program_update_date FROM mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mtl_transaction_reasons mtr, mtl_txn_source_types mtst, mtl_transaction_types mtt, gl_daily_conversion_types glct, cst_cost_elements cce, mtl_item_locations mil, mtl_material_transactions mmt, org_organization_definitions ood, cst_ae_headers cah, cst_ae_lines cal, cst_le_cost_types clct, cst_cost_groups ccg, cst_cost_types cct, cst_cost_group_assignments ccga WHERE ((mmt.transaction_action_id IN (2,5) and mmt.primary_quantity > 0 and cal.accounted_dr is not null) or (mmt.transaction_action_id = 3 and mmt.organization_id = cal.reference2)) AND mmt.transfer_transaction_id=cah.accounting_event_id AND mmt.organization_id = mil.organization_id(+) AND mmt.locator_id = mil.inventory_location_id(+) AND cce.cost_element_id(+)=cal.cost_element_id 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 lu1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND lu1.lookup_code = cal.ae_line_type_code AND lu2.lookup_type = 'SYS_YES_NO' AND lu2.lookup_code = DECODE(NVL(cal.gl_sl_link_id, -1), -1, 2, 1) AND lu3.lookup_type = 'CST_VIEW_ACCOUNTING' AND lu3.lookup_code = 1 AND mmt.organization_id = ood.organization_id AND cah.set_of_books_id = clct.set_of_books_id AND mmt.currency_conversion_type = glct.conversion_type(+) and mmt.organization_id = ccga.organization_id and ccga.cost_group_id = ccg.cost_group_id and clct.legal_entity = ccg.legal_entity and ccg.cost_group_type= 2 and cct.cost_type_id = clct.cost_type_id and cah.ae_header_id = cal.ae_header_id and cah.legal_entity_id=clct.legal_entity and cah.cost_type_id = clct.cost_type_id and cah.cost_group_id = ccg.cost_group_id AND cah.acct_event_source_table = 'MMT' UNION ALL SELECT clct.legal_entity, ccg.cost_group_id, ccg.cost_group, clct.cost_type_id, cct.cost_type, ccg.organization_id, clct.primary_cost_method, 401, cah.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, cal.ae_line_type_code, cal.code_combination_id, cal.currency_code, cal.entered_dr, cal.entered_cr, cal.accounted_dr, cal.accounted_cr, cal.currency_conversion_date, cal.currency_conversion_type, glct.user_conversion_type, cal.currency_conversion_rate, mmt.transaction_date, lu2.meaning, DECODE(NVL(cal.gl_sl_link_id, -1), -1, 'N','Y'), cah.gl_transfer_run_id , 'MMT', mmt.transaction_id, 'CAL' , 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), mmt.inventory_item_id, mmt.revision, mmt.transaction_uom, mmt.transaction_quantity, to_char(decode(mmt.transaction_quantity, 0, 0, nvl(cal.accounted_dr,cal.accounted_cr)/abs(mmt.transaction_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, cal.cost_element_id , cal.last_update_date , cal.last_updated_by , cal.creation_date , cal.created_by , cal.last_update_login , cal.request_id , cal.program_application_id , cal.program_id , cal.program_update_date FROM mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mtl_transaction_reasons mtr, mtl_txn_source_types mtst, mtl_transaction_types mtt, gl_daily_conversion_types glct, cst_cost_elements cce, mtl_item_locations mil, mtl_material_transactions mmt, org_organization_definitions ood, cst_ae_headers cah, cst_ae_lines cal, cst_le_cost_types clct, cst_cost_groups ccg, cst_cost_types cct WHERE mmt.transaction_action_id <> 2 and mmt.transaction_action_id <> 5 and mmt.transaction_action_id <> 3 and mmt.transaction_type_id = 26 and mmt.transaction_id=cah.accounting_event_id AND mmt.organization_id = mil.organization_id(+) AND mmt.locator_id = mil.inventory_location_id(+) AND cce.cost_element_id(+)=cal.cost_element_id 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 lu1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND lu1.lookup_code = cal.ae_line_type_code AND lu2.lookup_type = 'SYS_YES_NO' AND lu2.lookup_code = DECODE(NVL(cal.gl_sl_link_id, -1), -1, 2, 1) AND lu3.lookup_type = 'CST_VIEW_ACCOUNTING' AND lu3.lookup_code = 1 AND mmt.organization_id = ood.organization_id /* For bug4765107 AND cah.set_of_books_id = ood.set_of_books_id */ AND mmt.currency_conversion_type = glct.conversion_type(+) and clct.legal_entity = ccg.legal_entity and ccg.cost_group_type= 2 and cct.cost_type_id = clct.cost_type_id and cah.ae_header_id = cal.ae_header_id and cah.legal_entity_id=clct.legal_entity and cah.cost_type_id = clct.cost_type_id and cah.cost_group_id = ccg.cost_group_id AND cah.acct_event_source_table = 'MMT'

View Text - HTML Formatted

SELECT CLCT.LEGAL_ENTITY
, CCGA.COST_GROUP_ID
, CCG.COST_GROUP
, CLCT.COST_TYPE_ID
, CCT.COST_TYPE
, CCG.ORGANIZATION_ID
, CLCT.PRIMARY_COST_METHOD
, 401
, CAH.SET_OF_BOOKS_ID
, OOD.ORGANIZATION_CODE
, CCGA.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
, CAL.AE_LINE_TYPE_CODE
, CAL.CODE_COMBINATION_ID
, CAL.CURRENCY_CODE
, CAL.ENTERED_DR
, CAL.ENTERED_CR
, CAL.ACCOUNTED_DR
, CAL.ACCOUNTED_CR
, CAL.CURRENCY_CONVERSION_DATE
, CAL.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, CAL.CURRENCY_CONVERSION_RATE
, MMT.TRANSACTION_DATE
, LU2.MEANING
, DECODE(NVL(CAL.GL_SL_LINK_ID
, -1)
, -1
, 'N'
, 'Y')
, CAH.GL_TRANSFER_RUN_ID
, 'MMT'
, MMT.TRANSACTION_ID
, 'CAL'
, 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)
, MMT.INVENTORY_ITEM_ID
, MMT.REVISION
, MMT.TRANSACTION_UOM
, MMT.TRANSACTION_QUANTITY
, TO_CHAR(DECODE(MMT.TRANSACTION_QUANTITY
, 0
, 0
, NVL(CAL.ACCOUNTED_DR
, CAL.ACCOUNTED_CR) / ABS(MMT.TRANSACTION_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
, CAL.COST_ELEMENT_ID
, CAL.LAST_UPDATE_DATE
, CAL.LAST_UPDATED_BY
, CAL.CREATION_DATE
, CAL.CREATED_BY
, CAL.LAST_UPDATE_LOGIN
, CAL.REQUEST_ID
, CAL.PROGRAM_APPLICATION_ID
, CAL.PROGRAM_ID
, CAL.PROGRAM_UPDATE_DATE
FROM MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MTL_TRANSACTION_REASONS MTR
, MTL_TXN_SOURCE_TYPES MTST
, MTL_TRANSACTION_TYPES MTT
, GL_DAILY_CONVERSION_TYPES GLCT
, CST_COST_ELEMENTS CCE
, MTL_ITEM_LOCATIONS MIL
, MTL_MATERIAL_TRANSACTIONS MMT
, ORG_ORGANIZATION_DEFINITIONS OOD
, CST_AE_HEADERS CAH
, CST_AE_LINES CAL
, CST_LE_COST_TYPES CLCT
, CST_COST_GROUPS CCG
, CST_COST_TYPES CCT
, CST_COST_GROUP_ASSIGNMENTS CCGA
WHERE ((MMT.TRANSACTION_ACTION_ID IN (2
, 5)
AND MMT.PRIMARY_QUANTITY < 0
AND CAL.ACCOUNTED_CR IS NOT NULL) OR (MMT.TRANSACTION_ACTION_ID = 3
AND MMT.ORGANIZATION_ID = CAL.REFERENCE2) OR (MMT.TRANSACTION_ACTION_ID NOT IN (2
, 3
, 5)
AND MMT.TRANSACTION_TYPE_ID <> 26))
AND MMT.TRANSACTION_ID=CAH.ACCOUNTING_EVENT_ID
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND CCE.COST_ELEMENT_ID(+)=CAL.COST_ELEMENT_ID
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 LU1.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU1.LOOKUP_CODE = CAL.AE_LINE_TYPE_CODE
AND LU2.LOOKUP_TYPE = 'SYS_YES_NO'
AND LU2.LOOKUP_CODE = DECODE(NVL(CAL.GL_SL_LINK_ID
, -1)
, -1
, 2
, 1)
AND LU3.LOOKUP_TYPE = 'CST_VIEW_ACCOUNTING'
AND LU3.LOOKUP_CODE = 1
AND CCGA.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND CAH.SET_OF_BOOKS_ID = CLCT.SET_OF_BOOKS_ID
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+)
AND ( (CCGA.ORGANIZATION_ID = MMT.ORGANIZATION_ID) OR (CCGA.ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID
AND CCGA.COST_GROUP_ID NOT IN (SELECT COST_GROUP_ID
FROM CST_COST_GROUP_ASSIGNMENTS CCGA2
WHERE CCGA2.ORGANIZATION_ID = MMT.ORGANIZATION_ID)
AND ( (MMT.TRANSACTION_ACTION_ID = 21
AND MMT.FOB_POINT = 1) OR (MMT.TRANSACTION_ACTION_ID = 12
AND MMT.FOB_POINT = 2) ) ) )
AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
AND CLCT.LEGAL_ENTITY = CCG.LEGAL_ENTITY
AND CCG.COST_GROUP_TYPE= 2
AND CCT.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.AE_HEADER_ID = CAL.AE_HEADER_ID
AND CAH.LEGAL_ENTITY_ID=CLCT.LEGAL_ENTITY
AND CAH.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.COST_GROUP_ID = CCG.COST_GROUP_ID
AND CAH.ACCT_EVENT_SOURCE_TABLE = 'MMT' UNION ALL SELECT CLCT.LEGAL_ENTITY
, CCGA.COST_GROUP_ID
, CCG.COST_GROUP
, CLCT.COST_TYPE_ID
, CCT.COST_TYPE
, CCG.ORGANIZATION_ID
, CLCT.PRIMARY_COST_METHOD
, 401
, CAH.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
, CAL.AE_LINE_TYPE_CODE
, CAL.CODE_COMBINATION_ID
, CAL.CURRENCY_CODE
, CAL.ENTERED_DR
, CAL.ENTERED_CR
, CAL.ACCOUNTED_DR
, CAL.ACCOUNTED_CR
, CAL.CURRENCY_CONVERSION_DATE
, CAL.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, CAL.CURRENCY_CONVERSION_RATE
, MMT.TRANSACTION_DATE
, LU2.MEANING
, DECODE(NVL(CAL.GL_SL_LINK_ID
, -1)
, -1
, 'N'
, 'Y')
, CAH.GL_TRANSFER_RUN_ID
, 'MMT'
, MMT.TRANSFER_TRANSACTION_ID
, 'CAL'
, 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)
, MMT.INVENTORY_ITEM_ID
, MMT.REVISION
, MMT.TRANSACTION_UOM
, MMT.TRANSACTION_QUANTITY
, TO_CHAR(DECODE(MMT.TRANSACTION_QUANTITY
, 0
, 0
, NVL(CAL.ACCOUNTED_DR
, CAL.ACCOUNTED_CR)/ABS(MMT.TRANSACTION_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
, CAL.COST_ELEMENT_ID
, CAL.LAST_UPDATE_DATE
, CAL.LAST_UPDATED_BY
, CAL.CREATION_DATE
, CAL.CREATED_BY
, CAL.LAST_UPDATE_LOGIN
, CAL.REQUEST_ID
, CAL.PROGRAM_APPLICATION_ID
, CAL.PROGRAM_ID
, CAL.PROGRAM_UPDATE_DATE
FROM MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MTL_TRANSACTION_REASONS MTR
, MTL_TXN_SOURCE_TYPES MTST
, MTL_TRANSACTION_TYPES MTT
, GL_DAILY_CONVERSION_TYPES GLCT
, CST_COST_ELEMENTS CCE
, MTL_ITEM_LOCATIONS MIL
, MTL_MATERIAL_TRANSACTIONS MMT
, ORG_ORGANIZATION_DEFINITIONS OOD
, CST_AE_HEADERS CAH
, CST_AE_LINES CAL
, CST_LE_COST_TYPES CLCT
, CST_COST_GROUPS CCG
, CST_COST_TYPES CCT
, CST_COST_GROUP_ASSIGNMENTS CCGA
WHERE ((MMT.TRANSACTION_ACTION_ID IN (2
, 5)
AND MMT.PRIMARY_QUANTITY > 0
AND CAL.ACCOUNTED_DR IS NOT NULL) OR (MMT.TRANSACTION_ACTION_ID = 3
AND MMT.ORGANIZATION_ID = CAL.REFERENCE2))
AND MMT.TRANSFER_TRANSACTION_ID=CAH.ACCOUNTING_EVENT_ID
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND CCE.COST_ELEMENT_ID(+)=CAL.COST_ELEMENT_ID
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 LU1.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU1.LOOKUP_CODE = CAL.AE_LINE_TYPE_CODE
AND LU2.LOOKUP_TYPE = 'SYS_YES_NO'
AND LU2.LOOKUP_CODE = DECODE(NVL(CAL.GL_SL_LINK_ID
, -1)
, -1
, 2
, 1)
AND LU3.LOOKUP_TYPE = 'CST_VIEW_ACCOUNTING'
AND LU3.LOOKUP_CODE = 1
AND MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND CAH.SET_OF_BOOKS_ID = CLCT.SET_OF_BOOKS_ID
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+)
AND MMT.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
AND CLCT.LEGAL_ENTITY = CCG.LEGAL_ENTITY
AND CCG.COST_GROUP_TYPE= 2
AND CCT.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.AE_HEADER_ID = CAL.AE_HEADER_ID
AND CAH.LEGAL_ENTITY_ID=CLCT.LEGAL_ENTITY
AND CAH.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.COST_GROUP_ID = CCG.COST_GROUP_ID
AND CAH.ACCT_EVENT_SOURCE_TABLE = 'MMT' UNION ALL SELECT CLCT.LEGAL_ENTITY
, CCG.COST_GROUP_ID
, CCG.COST_GROUP
, CLCT.COST_TYPE_ID
, CCT.COST_TYPE
, CCG.ORGANIZATION_ID
, CLCT.PRIMARY_COST_METHOD
, 401
, CAH.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
, CAL.AE_LINE_TYPE_CODE
, CAL.CODE_COMBINATION_ID
, CAL.CURRENCY_CODE
, CAL.ENTERED_DR
, CAL.ENTERED_CR
, CAL.ACCOUNTED_DR
, CAL.ACCOUNTED_CR
, CAL.CURRENCY_CONVERSION_DATE
, CAL.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, CAL.CURRENCY_CONVERSION_RATE
, MMT.TRANSACTION_DATE
, LU2.MEANING
, DECODE(NVL(CAL.GL_SL_LINK_ID
, -1)
, -1
, 'N'
, 'Y')
, CAH.GL_TRANSFER_RUN_ID
, 'MMT'
, MMT.TRANSACTION_ID
, 'CAL'
, 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)
, MMT.INVENTORY_ITEM_ID
, MMT.REVISION
, MMT.TRANSACTION_UOM
, MMT.TRANSACTION_QUANTITY
, TO_CHAR(DECODE(MMT.TRANSACTION_QUANTITY
, 0
, 0
, NVL(CAL.ACCOUNTED_DR
, CAL.ACCOUNTED_CR)/ABS(MMT.TRANSACTION_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
, CAL.COST_ELEMENT_ID
, CAL.LAST_UPDATE_DATE
, CAL.LAST_UPDATED_BY
, CAL.CREATION_DATE
, CAL.CREATED_BY
, CAL.LAST_UPDATE_LOGIN
, CAL.REQUEST_ID
, CAL.PROGRAM_APPLICATION_ID
, CAL.PROGRAM_ID
, CAL.PROGRAM_UPDATE_DATE
FROM MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MTL_TRANSACTION_REASONS MTR
, MTL_TXN_SOURCE_TYPES MTST
, MTL_TRANSACTION_TYPES MTT
, GL_DAILY_CONVERSION_TYPES GLCT
, CST_COST_ELEMENTS CCE
, MTL_ITEM_LOCATIONS MIL
, MTL_MATERIAL_TRANSACTIONS MMT
, ORG_ORGANIZATION_DEFINITIONS OOD
, CST_AE_HEADERS CAH
, CST_AE_LINES CAL
, CST_LE_COST_TYPES CLCT
, CST_COST_GROUPS CCG
, CST_COST_TYPES CCT
WHERE MMT.TRANSACTION_ACTION_ID <> 2
AND MMT.TRANSACTION_ACTION_ID <> 5
AND MMT.TRANSACTION_ACTION_ID <> 3
AND MMT.TRANSACTION_TYPE_ID = 26
AND MMT.TRANSACTION_ID=CAH.ACCOUNTING_EVENT_ID
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND CCE.COST_ELEMENT_ID(+)=CAL.COST_ELEMENT_ID
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 LU1.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU1.LOOKUP_CODE = CAL.AE_LINE_TYPE_CODE
AND LU2.LOOKUP_TYPE = 'SYS_YES_NO'
AND LU2.LOOKUP_CODE = DECODE(NVL(CAL.GL_SL_LINK_ID
, -1)
, -1
, 2
, 1)
AND LU3.LOOKUP_TYPE = 'CST_VIEW_ACCOUNTING'
AND LU3.LOOKUP_CODE = 1
AND MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID /* FOR BUG4765107
AND CAH.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID */
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+)
AND CLCT.LEGAL_ENTITY = CCG.LEGAL_ENTITY
AND CCG.COST_GROUP_TYPE= 2
AND CCT.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.AE_HEADER_ID = CAL.AE_HEADER_ID
AND CAH.LEGAL_ENTITY_ID=CLCT.LEGAL_ENTITY
AND CAH.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.COST_GROUP_ID = CCG.COST_GROUP_ID
AND CAH.ACCT_EVENT_SOURCE_TABLE = 'MMT'