View: CST_PAC_AEL_GL_WIP_V

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

View Text

SELECT /*+ ORDERED */ CLCT.LEGAL_ENTITY
, CCGA.COST_GROUP_ID
, CCG.COST_GROUP
, CLCT.COST_TYPE_ID
, CCT.COST_TYPE
, CLCT.PRIMARY_COST_METHOD
, CCG.ORGANIZATION_ID
, 706
, CAH.SET_OF_BOOKS_ID
, OOD.ORGANIZATION_CODE
, WE.ORGANIZATION_ID
, LU4.MEANING
, 'WIP TRX'
, LU1.MEANING
, WT.TRANSACTION_TYPE
, TO_CHAR(WT.TRANSACTION_ID)
, WT.TRANSACTION_ID
, TRUNC(WT.TRANSACTION_DATE)
, WT.REFERENCE
, CAH.ACCT_EVENT_SOURCE_TABLE
, WT.TRANSACTION_ID
, LU2.MEANING
, TO_CHAR(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
, CAH.ACCOUNTING_DATE
, LU5.MEANING
, DECODE(NVL(CAL.GL_SL_LINK_ID
, -1)
, -1
, 'N'
, 'Y')
, CAH.GL_TRANSFER_RUN_ID
, CAL.SOURCE_TABLE
, CAL.SOURCE_ID
, 'CAL'
, CAL.DESCRIPTION
, RPAD(CAL.DESCRIPTION
, 132)
, WT.TRANSACTION_UOM
, WT.TRANSACTION_QUANTITY
, TO_CHAR(NVL(CAL.ACCOUNTED_DR
, CAL.ACCOUNTED_CR)/WT.PRIMARY_QUANTITY)
, WT.OPERATION_SEQ_NUM
, BD.DEPARTMENT_CODE
, BD.DESCRIPTION
, WT.RESOURCE_SEQ_NUM
, POH.SEGMENT1
, WE.WIP_ENTITY_ID
, DECODE(WT.LINE_ID
, NULL
, WE.WIP_ENTITY_NAME
, '')
, WT.LINE_ID
, WL.LINE_CODE
, MSIK.CONCATENATED_SEGMENTS
, LU3.MEANING
, BR.RESOURCE_ID
, BR.RESOURCE_CODE
, WFS.SCHEDULE_NUMBER
, MTR.REASON_NAME
, CAL.COST_ELEMENT_ID
, CCE.COST_ELEMENT
, 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_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
, WIP_TRANSACTIONS WT
, WIP_LINES WL
, WIP_ENTITIES WE
, BOM_RESOURCES BR
, WIP_FLOW_SCHEDULES WFS
, ORG_ORGANIZATION_DEFINITIONS OOD
, PO_HEADERS POH
, MTL_SYSTEM_ITEMS_KFV MSIK
, MTL_TRANSACTION_REASONS MTR
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MFG_LOOKUPS LU4
, MFG_LOOKUPS LU5
WHERE WE.WIP_ENTITY_ID = WT.WIP_ENTITY_ID
AND WL.LINE_ID ( + ) = WT.LINE_ID
AND WE.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND BD.DEPARTMENT_ID ( + ) = WT.DEPARTMENT_ID
AND MTR.REASON_ID ( + ) = WT.REASON_ID
AND POH.PO_HEADER_ID ( + ) = WT.PO_HEADER_ID
AND CCE.COST_ELEMENT_ID ( + ) = CAL.COST_ELEMENT_ID
AND BR.RESOURCE_ID ( + ) = CAL.RESOURCE_ID
AND WFS.WIP_ENTITY_ID ( + ) = WE.WIP_ENTITY_ID
AND LU1.LOOKUP_TYPE ( + ) = 'WIP_TRANSACTION_TYPE'
AND LU1.LOOKUP_CODE ( + ) = WT.TRANSACTION_TYPE
AND LU2.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU2.LOOKUP_CODE = TO_NUMBER ( CAL.AE_LINE_TYPE_CODE )
AND LU3.LOOKUP_CODE ( + ) = CAL.BASIS_TYPE
AND LU3.LOOKUP_TYPE ( + ) = 'CST_BASIS'
AND LU4.LOOKUP_CODE = 2
AND LU4.LOOKUP_TYPE = 'CST_VIEW_ACCOUNTING'
AND LU5.LOOKUP_TYPE = 'SYS_YES_NO'
AND LU5.LOOKUP_CODE = DECODE ( NVL ( CAH.GL_TRANSFER_RUN_ID
, -1 )
, -1
, 2
, 1 )
AND GPS.APPLICATION_ID = 401
AND GPS.PERIOD_NAME = JEH.PERIOD_NAME
AND WT.TRANSACTION_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE
AND WT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE ( + )
AND WT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND JEL.JE_HEADER_ID = JEH.JE_HEADER_ID
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND R.JE_LINE_NUM = JEL.JE_LINE_NUM
AND R.GL_SL_LINK_ID IS NOT NULL
AND R.REFERENCE_3 IS NOT NULL
AND CAH.GL_TRANSFER_RUN_ID = R.REFERENCE_1
AND CAL.REFERENCE3 = R.REFERENCE_3
AND CAL.CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID
AND CAL.GL_SL_LINK_ID = R.GL_SL_LINK_ID
AND JEH.JE_SOURCE = 'PERIODIC INVENTORY'
AND JEH.JE_CATEGORY = 'WIP'
AND CCGA.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND CCG.COST_GROUP_ID = CCGA.COST_GROUP_ID
AND CCG.COST_GROUP_TYPE = 2
AND CLCT.LEGAL_ENTITY = CCG.LEGAL_ENTITY
AND CAH.ACCOUNTING_EVENT_ID = WT.TRANSACTION_ID
AND CAH.ACCT_EVENT_SOURCE_TABLE = 'WT'
AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
AND CCT.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.COST_GROUP_ID = CCGA.COST_GROUP_ID
AND CAH.LEGAL_ENTITY_ID = CCG.LEGAL_ENTITY
AND MSIK.ORGANIZATION_ID(+) = WE.ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID(+) = WE.PRIMARY_ITEM_ID
AND GPS.SET_OF_BOOKS_ID = CLCT.SET_OF_BOOKS_ID
AND CLCT.SET_OF_BOOKS_ID = JEL.LEDGER_ID UNION ALL SELECT /*+ ORDERED */ CLCT.LEGAL_ENTITY
, CCGA.COST_GROUP_ID
, CCG.COST_GROUP
, CLCT.COST_TYPE_ID
, CCT.COST_TYPE
, CLCT.PRIMARY_COST_METHOD
, CCG.ORGANIZATION_ID
, 706
, CAH.SET_OF_BOOKS_ID
, OOD.ORGANIZATION_CODE
, CAH.ORGANIZATION_ID
, LU4.MEANING
, 'WIP TRX'
, LU1.MEANING
, WT.TRANSACTION_TYPE
, TO_CHAR ( WT.TRANSACTION_ID )
, WT.TRANSACTION_ID
, TRUNC ( WT.TRANSACTION_DATE )
, WT.REFERENCE
, CAH.ACCT_EVENT_SOURCE_TABLE
, WT.TRANSACTION_ID
, LU2.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
, CAH.ACCOUNTING_DATE
, LU5.MEANING
, DECODE ( NVL ( CAL.GL_SL_LINK_ID
, -1 )
, -1
, 'N'
, 'Y' )
, CAH.GL_TRANSFER_RUN_ID
, CAL.SOURCE_TABLE
, CAL.SOURCE_ID
, 'CAL'
, CAL.DESCRIPTION
, RPAD ( CAL.DESCRIPTION
, 132 )
, WT.TRANSACTION_UOM
, WT.TRANSACTION_QUANTITY
, TO_CHAR ( NVL ( CAL.ACCOUNTED_DR
, CAL.ACCOUNTED_CR) /WT.PRIMARY_QUANTITY )
, WT.OPERATION_SEQ_NUM
, BD.DEPARTMENT_CODE
, BD.DESCRIPTION
, WT.RESOURCE_SEQ_NUM
, POH.SEGMENT1
, WE.WIP_ENTITY_ID
, DECODE ( WT.LINE_ID
, NULL
, WE.WIP_ENTITY_NAME
, '' )
, WT.LINE_ID
, WL.LINE_CODE
, MSIK.CONCATENATED_SEGMENTS
, LU3.MEANING
, BR.RESOURCE_ID
, BR.RESOURCE_CODE
, WFS.SCHEDULE_NUMBER
, MTR.REASON_NAME
, CAL.COST_ELEMENT_ID
, CCE.COST_ELEMENT
, 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_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
, WIP_TRANSACTIONS WT
, WIP_LINES WL
, WIP_ENTITIES WE
, BOM_RESOURCES BR
, WIP_FLOW_SCHEDULES WFS
, ORG_ORGANIZATION_DEFINITIONS OOD
, PO_HEADERS POH
, MTL_SYSTEM_ITEMS_KFV MSIK
, MTL_TRANSACTION_REASONS MTR
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MFG_LOOKUPS LU4
, MFG_LOOKUPS LU5
WHERE WE.WIP_ENTITY_ID = WT.WIP_ENTITY_ID
AND WL.LINE_ID ( + ) = WT.LINE_ID
AND WE.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND BD.DEPARTMENT_ID ( + ) = WT.DEPARTMENT_ID
AND MTR.REASON_ID ( + ) = WT.REASON_ID
AND POH.PO_HEADER_ID ( + ) = WT.PO_HEADER_ID
AND CCE.COST_ELEMENT_ID ( + ) = CAL.COST_ELEMENT_ID
AND BR.RESOURCE_ID ( + ) = CAL.RESOURCE_ID
AND WFS.WIP_ENTITY_ID ( + ) = WE.WIP_ENTITY_ID
AND LU1.LOOKUP_TYPE ( + ) = 'WIP_TRANSACTION_TYPE'
AND LU1.LOOKUP_CODE ( + ) = WT.TRANSACTION_TYPE
AND LU2.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU2.LOOKUP_CODE = TO_NUMBER ( CAL.AE_LINE_TYPE_CODE )
AND LU3.LOOKUP_CODE ( + ) = CAL.BASIS_TYPE
AND LU3.LOOKUP_TYPE ( + ) = 'CST_BASIS'
AND LU4.LOOKUP_CODE = 2
AND LU4.LOOKUP_TYPE = 'CST_VIEW_ACCOUNTING'
AND LU5.LOOKUP_TYPE = 'SYS_YES_NO'
AND LU5.LOOKUP_CODE = DECODE ( NVL ( CAH.GL_TRANSFER_RUN_ID
, -1)
, -1
, 2
, 1 )
AND GPS.APPLICATION_ID = 401
AND GPS.PERIOD_NAME = JEH.PERIOD_NAME
AND WT.TRANSACTION_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE
AND WT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE ( + )
AND WT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND JEL.JE_HEADER_ID = JEH.JE_HEADER_ID
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND R.JE_LINE_NUM = JEL.JE_LINE_NUM
AND CAH.GL_TRANSFER_RUN_ID = R.REFERENCE_1
AND 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 JEH.JE_SOURCE = 'PERIODIC INVENTORY'
AND JEH.JE_CATEGORY = 'WIP'
AND CCGA.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND CCG.COST_GROUP_ID = CCGA.COST_GROUP_ID
AND CCG.COST_GROUP_TYPE = 2
AND CLCT.LEGAL_ENTITY = CCG.LEGAL_ENTITY
AND CCT.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.ACCOUNTING_EVENT_ID = WT.TRANSACTION_ID
AND CAH.ACCT_EVENT_SOURCE_TABLE = 'WT'
AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
AND CAH.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.COST_GROUP_ID = CCGA.COST_GROUP_ID
AND CAH.LEGAL_ENTITY_ID = CCG.LEGAL_ENTITY
AND MSIK.INVENTORY_ITEM_ID(+) = WE.PRIMARY_ITEM_ID
AND MSIK.ORGANIZATION_ID(+) = WE.ORGANIZATION_ID
AND GPS.SET_OF_BOOKS_ID = CLCT.SET_OF_BOOKS_ID
AND CLCT.SET_OF_BOOKS_ID = JEL.LEDGER_ID

Columns