FND Design Data [Home] [Help]

View: CST_AEL_GL_WIP_V

Product: BOM - Bills of Material
Description:
Implementation/DBA Data: ViewAPPS.CST_AEL_GL_WIP_V
View Text

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

Columns

Name
APPLICATION_ID
SET_OF_BOOKS_ID
INVENTORY_ORGANIZATION_CODE
INVENTORY_ORGANIZATION_ID
TRX_CLASS_NAME
TRX_CLASS
TRX_TYPE_NAME
TRX_TYPE_N
TRX_NUMBER_DISPLAYED
TRX_NUMBER_N
TRX_DATE
COMMENTS
TRX_HDR_TABLE
TRX_HDR_ID
ACCT_LINE_TYPE_NAME
ACCT_LINE_TYPE
CODE_COMBINATION_ID
CURRENCY_CODE
ENTERED_DR
ENTERED_CR
ACCOUNTED_DR
ACCOUNTED_CR
CURRENCY_CONVERSION_DATE
CURRENCY_CONVERSION_TYPE
CURRENCY_USER_CONVERSION_TYPE
CURRENCY_CONVERSION_RATE
ACCOUNTING_DATE
GL_TRANSFER_STATUS_NAME
GL_TRANSFER_STATUS
GL_BATCH_ID
SOURCE_TABLE
SOURCE_ID
AEL_TABLE
AE_LINE_REFERENCE
AE_LINE_REFERENCE_INTERNAL
TRX_UOM
TRX_QUANTITY
UNIT_COST
MFG_OPERATION_SEQ_NUM
BOM_DEPARTMENT_CODE
BOM_DEPARTMENT_NAME
WIP_RESOURCE_SEQ_NUM
TRX_SOURCE_NAME
WIP_JOB_ID
WIP_JOB_NAME
WIP_LINE_ID
WIP_LINE_CODE
WIP_ASSEMBLY
WIP_BASIS
WIP_RESOURCE_ID
WIP_RESOURCE_NAME
WIP_FLOW_SCHEDULE_NUMBER
TRX_REASON_NAME
COST_ELEMENT_ID
COST_ELEMENT_NAME
JE_HEADER_ID
JE_LINE_NUM
LAST_UPDATE_DATE
LAST_UPDATED_BY
CREATION_DATE
CREATED_BY
LAST_UPDATE_LOGIN
REQUEST_ID
PROGRAM_APPLICATION_ID
PROGRAM_ID
PROGRAM_UPDATE_DATE