VIEW: APPS.CST_PAC_WIP_TXN_V

Source

View Text - Preformatted

SELECT WT.ROWID ROW_ID , CCG.LEGAL_ENTITY , XFI.NAME , CCG.COST_GROUP_ID , CCG.COST_GROUP , CCG.ORGANIZATION_ID , WT.TRANSACTION_ID , WT.LAST_UPDATE_DATE , WT.LAST_UPDATED_BY , WT.CREATION_DATE , WT.CREATED_BY , WT.LAST_UPDATE_LOGIN , WT.ORGANIZATION_ID , OOD.ORGANIZATION_CODE , OOD.ORGANIZATION_NAME , WT.WIP_ENTITY_ID , WJAL.WIP_ENTITY_NAME , WT.PRIMARY_ITEM_ID , WT.ACCT_PERIOD_ID , WT.DEPARTMENT_ID , BD.DEPARTMENT_CODE , WT.TRANSACTION_TYPE , ML1.MEANING TRANSACTION_TYPE_MEANING , WT.TRANSACTION_DATE , WT.GROUP_ID , WT.LINE_ID , WJAL.LINE_CODE , WT.SOURCE_CODE , WT.SOURCE_LINE_ID , WT.OPERATION_SEQ_NUM , WT.RESOURCE_SEQ_NUM , WT.EMPLOYEE_ID , P.EMPLOYEE_NUMBER EMPLOYEE_NUM , WT.RESOURCE_ID , BR.RESOURCE_CODE , WT.AUTOCHARGE_TYPE , ML2.MEANING AUTOCHARGE_TYPE_MEANING , WT.STANDARD_RATE_FLAG , DECODE(WT.TRANSACTION_TYPE , 2, NULL, ML3.MEANING) STANDARD_RATE_MEANING , WT.USAGE_RATE_OR_AMOUNT , WT.BASIS_TYPE , ML4.MEANING BASIS_TYPE_MEANING , WT.TRANSACTION_QUANTITY , WT.TRANSACTION_UOM , WT.PRIMARY_QUANTITY , WT.PRIMARY_UOM , WT.ACTUAL_RESOURCE_RATE , WT.STANDARD_RESOURCE_RATE , WT.CURRENCY_CODE , WT.CURRENCY_CONVERSION_DATE , WT.CURRENCY_CONVERSION_TYPE , WT.CURRENCY_CONVERSION_RATE , WT.CURRENCY_ACTUAL_RESOURCE_RATE , WT.ACTIVITY_ID , CA.ACTIVITY , WT.REASON_ID , MTR.REASON_NAME , WT.REFERENCE , WT.MOVE_TRANSACTION_ID , WT.PO_HEADER_ID , POH.SEGMENT1 PO_NUMBER , WT.PROJECT_ID , PROJ.PROJECT_NUMBER , TASK.TASK_NUMBER , WT.TASK_ID , WT.ATTRIBUTE_CATEGORY , WT.ATTRIBUTE1 , WT.ATTRIBUTE2 , WT.ATTRIBUTE3 , WT.ATTRIBUTE4 , WT.ATTRIBUTE5 , WT.ATTRIBUTE6 , WT.ATTRIBUTE7 , WT.ATTRIBUTE8 , WT.ATTRIBUTE9 , WT.ATTRIBUTE10 , WT.ATTRIBUTE11 , WT.ATTRIBUTE12 , WT.ATTRIBUTE13 , WT.ATTRIBUTE14 , WT.ATTRIBUTE15 , WT.REQUEST_ID , WT.PROGRAM_APPLICATION_ID , WT.PROGRAM_ID , WT.PROGRAM_UPDATE_DATE FROM MFG_LOOKUPS ML1 , MFG_LOOKUPS ML2 , MFG_LOOKUPS ML3 , MFG_LOOKUPS ML4 , PO_HEADERS POH , MTL_PROJECT_V PROJ , MTL_TASK_V TASK , CST_ACTIVITIES CA , MTL_TRANSACTION_REASONS MTR , PER_PEOPLE_F P , BOM_RESOURCES BR , BOM_DEPARTMENTS BD , WIP_JOB_ASSY_LINE_V WJAL , WIP_TRANSACTIONS WT , CST_COST_GROUPS CCG , ORG_ORGANIZATION_DEFINITIONS OOD , XLE_FIRSTPARTY_INFORMATION_V XFI WHERE WT.WIP_ENTITY_ID = WJAL.WIP_ENTITY_ID AND WT.ORGANIZATION_ID = WJAL.ORGANIZATION_ID AND NVL(WT.LINE_ID, -1) = NVL(WJAL.LINE_ID, -1) AND BD.DEPARTMENT_ID(+) = WT.DEPARTMENT_ID AND P.PERSON_ID(+) = WT.EMPLOYEE_ID AND WT.TRANSACTION_DATE BETWEEN P.EFFECTIVE_START_DATE (+) AND P.EFFECTIVE_END_DATE (+) AND BR.RESOURCE_ID(+) = WT.RESOURCE_ID AND CA.ACTIVITY_ID(+) = WT.ACTIVITY_ID AND MTR.REASON_ID(+) = WT.REASON_ID AND POH.PO_HEADER_ID(+) = WT.PO_HEADER_ID AND PROJ.PROJECT_ID (+) = WT.PROJECT_ID AND TASK.PROJECT_ID (+) = WT.PROJECT_ID AND TASK.TASK_ID (+) = WT.TASK_ID AND ML1.LOOKUP_TYPE = 'WIP_TRANSACTION_TYPE' AND ML1.LOOKUP_CODE = WT.TRANSACTION_TYPE AND ML2.LOOKUP_TYPE(+) = 'BOM_AUTOCHARGE_TYPE' AND ML2.LOOKUP_CODE(+) = WT.AUTOCHARGE_TYPE AND ML3.LOOKUP_TYPE = 'SYS_YES_NO' AND ML3.LOOKUP_CODE = NVL(WT.STANDARD_RATE_FLAG, 1) AND ML4.LOOKUP_TYPE(+) = 'CST_BASIS' AND ML4.LOOKUP_CODE(+) = WT.BASIS_TYPE AND CCG.COST_GROUP_ID = ( SELECT CCGA.COST_GROUP_ID FROM CST_COST_GROUP_ASSIGNMENTS CCGA WHERE CCGA.ORGANIZATION_ID = WT.ORGANIZATION_ID) AND CCG.COST_GROUP_TYPE = 2 AND OOD.ORGANIZATION_ID = WT.ORGANIZATION_ID AND XFI.LEGAL_ENTITY_ID = CCG.LEGAL_ENTITY

View Text - HTML Formatted

SELECT WT.ROWID ROW_ID
, CCG.LEGAL_ENTITY
, XFI.NAME
, CCG.COST_GROUP_ID
, CCG.COST_GROUP
, CCG.ORGANIZATION_ID
, WT.TRANSACTION_ID
, WT.LAST_UPDATE_DATE
, WT.LAST_UPDATED_BY
, WT.CREATION_DATE
, WT.CREATED_BY
, WT.LAST_UPDATE_LOGIN
, WT.ORGANIZATION_ID
, OOD.ORGANIZATION_CODE
, OOD.ORGANIZATION_NAME
, WT.WIP_ENTITY_ID
, WJAL.WIP_ENTITY_NAME
, WT.PRIMARY_ITEM_ID
, WT.ACCT_PERIOD_ID
, WT.DEPARTMENT_ID
, BD.DEPARTMENT_CODE
, WT.TRANSACTION_TYPE
, ML1.MEANING TRANSACTION_TYPE_MEANING
, WT.TRANSACTION_DATE
, WT.GROUP_ID
, WT.LINE_ID
, WJAL.LINE_CODE
, WT.SOURCE_CODE
, WT.SOURCE_LINE_ID
, WT.OPERATION_SEQ_NUM
, WT.RESOURCE_SEQ_NUM
, WT.EMPLOYEE_ID
, P.EMPLOYEE_NUMBER EMPLOYEE_NUM
, WT.RESOURCE_ID
, BR.RESOURCE_CODE
, WT.AUTOCHARGE_TYPE
, ML2.MEANING AUTOCHARGE_TYPE_MEANING
, WT.STANDARD_RATE_FLAG
, DECODE(WT.TRANSACTION_TYPE
, 2
, NULL
, ML3.MEANING) STANDARD_RATE_MEANING
, WT.USAGE_RATE_OR_AMOUNT
, WT.BASIS_TYPE
, ML4.MEANING BASIS_TYPE_MEANING
, WT.TRANSACTION_QUANTITY
, WT.TRANSACTION_UOM
, WT.PRIMARY_QUANTITY
, WT.PRIMARY_UOM
, WT.ACTUAL_RESOURCE_RATE
, WT.STANDARD_RESOURCE_RATE
, WT.CURRENCY_CODE
, WT.CURRENCY_CONVERSION_DATE
, WT.CURRENCY_CONVERSION_TYPE
, WT.CURRENCY_CONVERSION_RATE
, WT.CURRENCY_ACTUAL_RESOURCE_RATE
, WT.ACTIVITY_ID
, CA.ACTIVITY
, WT.REASON_ID
, MTR.REASON_NAME
, WT.REFERENCE
, WT.MOVE_TRANSACTION_ID
, WT.PO_HEADER_ID
, POH.SEGMENT1 PO_NUMBER
, WT.PROJECT_ID
, PROJ.PROJECT_NUMBER
, TASK.TASK_NUMBER
, WT.TASK_ID
, WT.ATTRIBUTE_CATEGORY
, WT.ATTRIBUTE1
, WT.ATTRIBUTE2
, WT.ATTRIBUTE3
, WT.ATTRIBUTE4
, WT.ATTRIBUTE5
, WT.ATTRIBUTE6
, WT.ATTRIBUTE7
, WT.ATTRIBUTE8
, WT.ATTRIBUTE9
, WT.ATTRIBUTE10
, WT.ATTRIBUTE11
, WT.ATTRIBUTE12
, WT.ATTRIBUTE13
, WT.ATTRIBUTE14
, WT.ATTRIBUTE15
, WT.REQUEST_ID
, WT.PROGRAM_APPLICATION_ID
, WT.PROGRAM_ID
, WT.PROGRAM_UPDATE_DATE
FROM MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MFG_LOOKUPS ML4
, PO_HEADERS POH
, MTL_PROJECT_V PROJ
, MTL_TASK_V TASK
, CST_ACTIVITIES CA
, MTL_TRANSACTION_REASONS MTR
, PER_PEOPLE_F P
, BOM_RESOURCES BR
, BOM_DEPARTMENTS BD
, WIP_JOB_ASSY_LINE_V WJAL
, WIP_TRANSACTIONS WT
, CST_COST_GROUPS CCG
, ORG_ORGANIZATION_DEFINITIONS OOD
, XLE_FIRSTPARTY_INFORMATION_V XFI
WHERE WT.WIP_ENTITY_ID = WJAL.WIP_ENTITY_ID
AND WT.ORGANIZATION_ID = WJAL.ORGANIZATION_ID
AND NVL(WT.LINE_ID
, -1) = NVL(WJAL.LINE_ID
, -1)
AND BD.DEPARTMENT_ID(+) = WT.DEPARTMENT_ID
AND P.PERSON_ID(+) = WT.EMPLOYEE_ID
AND WT.TRANSACTION_DATE BETWEEN P.EFFECTIVE_START_DATE (+)
AND P.EFFECTIVE_END_DATE (+)
AND BR.RESOURCE_ID(+) = WT.RESOURCE_ID
AND CA.ACTIVITY_ID(+) = WT.ACTIVITY_ID
AND MTR.REASON_ID(+) = WT.REASON_ID
AND POH.PO_HEADER_ID(+) = WT.PO_HEADER_ID
AND PROJ.PROJECT_ID (+) = WT.PROJECT_ID
AND TASK.PROJECT_ID (+) = WT.PROJECT_ID
AND TASK.TASK_ID (+) = WT.TASK_ID
AND ML1.LOOKUP_TYPE = 'WIP_TRANSACTION_TYPE'
AND ML1.LOOKUP_CODE = WT.TRANSACTION_TYPE
AND ML2.LOOKUP_TYPE(+) = 'BOM_AUTOCHARGE_TYPE'
AND ML2.LOOKUP_CODE(+) = WT.AUTOCHARGE_TYPE
AND ML3.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML3.LOOKUP_CODE = NVL(WT.STANDARD_RATE_FLAG
, 1)
AND ML4.LOOKUP_TYPE(+) = 'CST_BASIS'
AND ML4.LOOKUP_CODE(+) = WT.BASIS_TYPE
AND CCG.COST_GROUP_ID = ( SELECT CCGA.COST_GROUP_ID
FROM CST_COST_GROUP_ASSIGNMENTS CCGA
WHERE CCGA.ORGANIZATION_ID = WT.ORGANIZATION_ID)
AND CCG.COST_GROUP_TYPE = 2
AND OOD.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND XFI.LEGAL_ENTITY_ID = CCG.LEGAL_ENTITY