Product: | BOM - Bills of Material |
---|---|
Description: | |
Implementation/DBA Data: | APPS.CST_AEL_GL_WIP_V |
SELECT /*+ ORDERED */ 706
, OOD.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
, 'WT'
, WT.TRANSACTION_ID
, LU2.MEANING
, TO_CHAR(WTA.ACCOUNTING_LINE_TYPE)
, WTA.REFERENCE_ACCOUNT
, NVL(WT.CURRENCY_CODE
, NVL(WTA.CURRENCY_CODE
, SOB.CURRENCY_CODE))
, DECODE(SIGN(NVL(WTA.TRANSACTION_VALUE
, WTA.BASE_TRANSACTION_VALUE))
, 1
, NVL(WTA.TRANSACTION_VALUE
, WTA.BASE_TRANSACTION_VALUE)
, 0
, 0
, NULL)
, DECODE(SIGN(NVL(WTA.TRANSACTION_VALUE
, WTA.BASE_TRANSACTION_VALUE))
, -1
, (-1*NVL(WTA.TRANSACTION_VALUE
, WTA.BASE_TRANSACTION_VALUE))
, NULL)
, DECODE(SIGN(WTA.BASE_TRANSACTION_VALUE)
, 1
, WTA.BASE_TRANSACTION_VALUE
, 0
, 0
, NULL)
, DECODE(SIGN(WTA.BASE_TRANSACTION_VALUE)
, -1
, (-1*WTA.BASE_TRANSACTION_VALUE)
, NULL)
, WT.CURRENCY_CONVERSION_DATE
, WT.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, WT.CURRENCY_CONVERSION_RATE
, TRUNC(WT.TRANSACTION_DATE)
, LU5.MEANING
, DECODE(WTA.GL_BATCH_ID
, -1
, 'N'
, 'Y')
, DECODE(WTA.GL_BATCH_ID
, -1
, TO_NUMBER(NULL)
, WTA.GL_BATCH_ID)
, 'WT'
, WT.TRANSACTION_ID
, 'WTA'
, DECODE(CCE.COST_ELEMENT
, NULL
, LU2.MEANING
, LU2.MEANING||'
, '||CCE.COST_ELEMENT)
, RPAD(DECODE(CCE.COST_ELEMENT
, NULL
, LU2.MEANING
, LU2.MEANING||'
, '||CCE.COST_ELEMENT)
, 132)
, WT.TRANSACTION_UOM
, WT.TRANSACTION_QUANTITY
, DECODE(WTA.PRIMARY_QUANTITY
, NULL
, NULL
, 0
, NULL
, ABS(WTA.BASE_TRANSACTION_VALUE/WTA.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
, WTA.COST_ELEMENT_ID
, CCE.COST_ELEMENT
, JEL.JE_HEADER_ID
, JEL.JE_LINE_NUM
, WTA.LAST_UPDATE_DATE
, WTA.LAST_UPDATED_BY
, WTA.CREATION_DATE
, WTA.CREATED_BY
, WTA.LAST_UPDATE_LOGIN
, WTA.REQUEST_ID
, WTA.PROGRAM_APPLICATION_ID
, WTA.PROGRAM_ID
, WTA.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
, WIP_TRANSACTION_ACCOUNTS WTA
, WIP_TRANSACTIONS WT
, WIP_ENTITIES WE
, WIP_LINES WL
, CST_COST_ELEMENTS CCE
, WIP_FLOW_SCHEDULES WFS
, PO_HEADERS POH
, BOM_RESOURCES BR
, MTL_TRANSACTION_REASONS MTR
, BOM_DEPARTMENTS BD
, ORG_ORGANIZATION_DEFINITIONS OOD
, MTL_SYSTEM_ITEMS_KFV MSIK
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MFG_LOOKUPS LU4
, MFG_LOOKUPS LU5
WHERE WTA.TRANSACTION_ID = WT.TRANSACTION_ID
AND 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(+) = WTA.COST_ELEMENT_ID
AND BR.RESOURCE_ID(+) = WTA.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 = WTA.ACCOUNTING_LINE_TYPE
AND LU3.LOOKUP_CODE(+) = WTA.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(WTA.GL_BATCH_ID
, -1
, 2
, 1)
AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND GPS.APPLICATION_ID = 401
AND GPS.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND GPS.PERIOD_NAME = JEH.PERIOD_NAME /*
AND WTA.TRANSACTION_DATE BETWEEN GPS.START_DATE
AND (TRUNC(GPS.END_DATE)+0.99999) */
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 SOB.SET_OF_BOOKS_ID = JEL.LEDGER_ID
AND R.GL_SL_LINK_ID IS NOT NULL
AND R.REFERENCE_3 IS NOT NULL
AND WTA.GL_BATCH_ID = R.REFERENCE_1
AND WTA.TRANSACTION_ID = R.REFERENCE_3
AND WTA.REFERENCE_ACCOUNT = JEL.CODE_COMBINATION_ID
AND WTA.GL_SL_LINK_ID = R.GL_SL_LINK_ID
AND JEH.JE_SOURCE = 'INVENTORY'
AND JEH.JE_CATEGORY = 'WIP'
AND MSIK.INVENTORY_ITEM_ID(+) = WE.PRIMARY_ITEM_ID
AND MSIK.ORGANIZATION_ID(+) = WE.ORGANIZATION_ID UNION ALL SELECT /*+ ORDERED */ 706
, OOD.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
, 'WT'
, WT.TRANSACTION_ID
, LU2.MEANING
, TO_CHAR(WTA.ACCOUNTING_LINE_TYPE)
, WTA.REFERENCE_ACCOUNT
, NVL(WT.CURRENCY_CODE
, NVL(WTA.CURRENCY_CODE
, SOB.CURRENCY_CODE))
, DECODE(SIGN(NVL(WTA.TRANSACTION_VALUE
, WTA.BASE_TRANSACTION_VALUE))
, 1
, NVL(WTA.TRANSACTION_VALUE
, WTA.BASE_TRANSACTION_VALUE)
, 0
, 0
, NULL)
, DECODE(SIGN(NVL(WTA.TRANSACTION_VALUE
, WTA.BASE_TRANSACTION_VALUE))
, -1
, (-1*NVL(WTA.TRANSACTION_VALUE
, WTA.BASE_TRANSACTION_VALUE))
, NULL)
, DECODE(SIGN(WTA.BASE_TRANSACTION_VALUE)
, 1
, WTA.BASE_TRANSACTION_VALUE
, 0
, 0
, NULL)
, DECODE(SIGN(WTA.BASE_TRANSACTION_VALUE)
, -1
, (-1*WTA.BASE_TRANSACTION_VALUE)
, NULL)
, WT.CURRENCY_CONVERSION_DATE
, WT.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, WT.CURRENCY_CONVERSION_RATE
, TRUNC(WT.TRANSACTION_DATE)
, LU5.MEANING
, DECODE(WTA.GL_BATCH_ID
, -1
, 'N'
, 'Y')
, DECODE(WTA.GL_BATCH_ID
, -1
, TO_NUMBER(NULL)
, WTA.GL_BATCH_ID)
, 'WT'
, WT.TRANSACTION_ID
, 'WTA'
, LU2.MEANING ||':'||CCE.COST_ELEMENT
, RPAD(CCE.COST_ELEMENT
, 80)
, /* ---------------------------PRODUCT SPECIFIC COLUMNS ------------------------*/ WT.TRANSACTION_UOM
, WT.TRANSACTION_QUANTITY
, DECODE(WTA.PRIMARY_QUANTITY
, NULL
, NULL
, 0
, NULL
, ABS(WTA.BASE_TRANSACTION_VALUE/WTA.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
, WTA.COST_ELEMENT_ID
, CCE.COST_ELEMENT
, JEL.JE_HEADER_ID
, JEL.JE_LINE_NUM
, WTA.LAST_UPDATE_DATE
, WTA.LAST_UPDATED_BY
, WTA.CREATION_DATE
, WTA.CREATED_BY
, WTA.LAST_UPDATE_LOGIN
, WTA.REQUEST_ID
, WTA.PROGRAM_APPLICATION_ID
, WTA.PROGRAM_ID
, WTA.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
, WIP_TRANSACTION_ACCOUNTS WTA
, WIP_TRANSACTIONS WT
, WIP_ENTITIES WE
, WIP_LINES WL
, CST_COST_ELEMENTS CCE
, WIP_FLOW_SCHEDULES WFS
, PO_HEADERS POH
, BOM_RESOURCES BR
, MTL_TRANSACTION_REASONS MTR
, BOM_DEPARTMENTS BD
, ORG_ORGANIZATION_DEFINITIONS OOD
, MTL_SYSTEM_ITEMS_KFV MSIK
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MFG_LOOKUPS LU4
, MFG_LOOKUPS LU5
WHERE WTA.TRANSACTION_ID = WT.TRANSACTION_ID
AND 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(+) = WTA.COST_ELEMENT_ID
AND BR.RESOURCE_ID(+) = WTA.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 = WTA.ACCOUNTING_LINE_TYPE
AND LU3.LOOKUP_CODE(+) = WTA.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(WTA.GL_BATCH_ID
, -1
, 2
, 1)
AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND GPS.APPLICATION_ID = 401
AND GPS.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND GPS.PERIOD_NAME = JEH.PERIOD_NAME /*
AND WTA.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 SOB.SET_OF_BOOKS_ID = JEL.LEDGER_ID
AND WTA.GL_BATCH_ID = R.REFERENCE_1
AND R.GL_SL_LINK_ID IS NULL
AND R.REFERENCE_3 IS NULL
AND WTA.REFERENCE_ACCOUNT+0 = JEL.CODE_COMBINATION_ID
AND NVL(WTA.CURRENCY_CODE
, SOB.CURRENCY_CODE) = JEH.CURRENCY_CODE
AND JEH.JE_SOURCE = 'INVENTORY'
AND JEH.JE_CATEGORY = 'WIP'
AND MSIK.INVENTORY_ITEM_ID(+) = WE.PRIMARY_ITEM_ID
AND MSIK.ORGANIZATION_ID(+) = WE.ORGANIZATION_ID