View: CST_PAC_AEL_SL_INV_V

Product:	BOM - Bills of Material
Description:
Implementation/DBA Data:	APPS.CST_PAC_AEL_SL_INV_V

View Text

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'

Columns