View: CST_PAC_AEL_GL_INV_V

Product:	BOM - Bills of Material
Description:
Implementation/DBA Data:	APPS.CST_PAC_AEL_GL_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
, MP.ORGANIZATION_CODE
, MMT.ORGANIZATION_ID
, LU3.MEANING
, 'INV TRX'
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_TYPE_ID
, DECODE(TO_CHAR(MMT.TRANSACTION_ACTION_ID)
, 2
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, TO_CHAR(MMT.TRANSFER_TRANSACTION_ID)
, TO_CHAR(MMT.TRANSACTION_ID))
, 5
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, TO_CHAR(MMT.TRANSFER_TRANSACTION_ID)
, TO_CHAR(MMT.TRANSACTION_ID))
, 3
, TO_CHAR(CAH.ACCOUNTING_EVENT_ID)
, TO_CHAR(MMT.TRANSACTION_ID))
, DECODE(MMT.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID)
, 5
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID)
, 3
, CAH.ACCOUNTING_EVENT_ID
, MMT.TRANSACTION_ID)
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_REFERENCE
, 'MMT'
, DECODE(MMT.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID)
, 5
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID)
, 3
, CAH.ACCOUNTING_EVENT_ID
, 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
, MMT.CURRENCY_CONVERSION_DATE
, MMT.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, MMT.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'
, DECODE(MMT.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID)
, 5
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID)
, 3
, CAH.ACCOUNTING_EVENT_ID
, 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 ( CAL.RATE_OR_AMOUNT )
, 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
, JEL.JE_HEADER_ID
, JEL.JE_LINE_NUM
, 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 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
, CST_AE_HEADERS CAH
, CST_AE_LINES CAL
, CST_COST_ELEMENTS CCE
, CST_LE_COST_TYPES CLCT
, CST_COST_GROUPS CCG
, CST_COST_TYPES CCT
, CST_COST_GROUP_ASSIGNMENTS CCGA
, 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
WHERE ( (MMT.TRANSACTION_ACTION_ID IN (2
, 5)
AND ( (MMT.PRIMARY_QUANTITY < 0
AND CAH.ACCOUNTING_EVENT_ID = MMT.TRANSACTION_ID
AND CAL.ACCOUNTED_CR IS NOT NULL) OR (MMT.PRIMARY_QUANTITY > 0
AND CAH.ACCOUNTING_EVENT_ID = MMT.TRANSFER_TRANSACTION_ID
AND CAL.ACCOUNTED_DR IS NOT NULL) ) ) OR (MMT.TRANSACTION_ACTION_ID = 3
AND MMT.ORGANIZATION_ID = CAL.REFERENCE2
AND (CAH.ACCOUNTING_EVENT_ID = MMT.TRANSACTION_ID OR CAH.ACCOUNTING_EVENT_ID = MMT.TRANSFER_TRANSACTION_ID) ) OR (CAH.ACCOUNTING_EVENT_ID = MMT.TRANSACTION_ID
AND MMT.TRANSACTION_ACTION_ID NOT IN (2
, 5
, 3)) )
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 MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE ( + )
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID ( + )
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID ( + )
AND MTR.REASON_ID ( + ) = MMT.REASON_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_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 CCE.COST_ELEMENT_ID ( + ) = CAL.COST_ELEMENT_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 ( CAL.GL_SL_LINK_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 JE_SOURCE = 'PERIODIC INVENTORY'
AND JE_CATEGORY = 'MTL'
AND CAH.GL_TRANSFER_RUN_ID = R.REFERENCE_1
AND ( ( R.GL_SL_LINK_ID IS NOT NULL
AND R.REFERENCE_3 IS NOT NULL
AND CAH.ACCOUNTING_EVENT_ID = R.REFERENCE_3
AND CAL.GL_SL_LINK_ID = R.GL_SL_LINK_ID ) OR ( R.GL_SL_LINK_ID IS NULL
AND R.REFERENCE_3 IS NULL
AND CAL.CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID
AND CAL.CURRENCY_CODE = JEH.CURRENCY_CODE
AND 'A' = JEH.ACTUAL_FLAG
AND NVL ( MMT.USSGL_TRANSACTION_CODE
, '#ZZZ' ) = NVL ( JEH.USSGL_TRANSACTION_CODE
, '#ZZZ' ) ) )

Columns