View: PJM_REQ_COMMITMENTS_CMPRJ_V

Product:	PJM - Project Manufacturing
Description:	Project-related purchase requisition distributions view
Implementation/DBA Data:	APPS.PJM_REQ_COMMITMENTS_CMPRJ_V

View Text

SELECT RH.SEGMENT1
, PDT.TYPE_NAME
, DECODE( NVL( RH.AUTHORIZATION_STATUS
, 'NOT APPROVED' )
, 'APPROVED'
, 'Y'
, 'N' )
, RL.LINE_NUM
, RL.ITEM_DESCRIPTION
, RL.NEED_BY_DATE
, RL.CREATION_DATE
, RL.SUGGESTED_VENDOR_NAME
, RL.VENDOR_ID
, REQ.PERSON_ID
, REQ.FULL_NAME
, NVL( RL.CURRENCY_CODE
, G.CURRENCY_CODE )
, NVL( RL.CURRENCY_UNIT_PRICE
, RL.UNIT_PRICE ) * RD.REQ_LINE_QUANTITY
, G.CURRENCY_CODE
, DECODE( NVL( RL.CURRENCY_CODE
, G.CURRENCY_CODE )
, G.CURRENCY_CODE
, TO_DATE(NULL)
, RL.RATE_DATE )
, DECODE( NVL( RL.CURRENCY_CODE
, G.CURRENCY_CODE )
, G.CURRENCY_CODE
, NULL
, RL.RATE_TYPE )
, DECODE( NVL( RL.CURRENCY_CODE
, G.CURRENCY_CODE )
, G.CURRENCY_CODE
, TO_NUMBER(NULL)
, RL.RATE )
, R.UNIT_OF_MEASURE
, RL.UNIT_PRICE / NVL( R.UOM_CONVERSION_RATE
, 1 )
, RD.REQ_LINE_QUANTITY * NVL( R.UOM_CONVERSION_RATE
, 1 )
, RD.REQ_LINE_QUANTITY * RL.UNIT_PRICE
, P.SEGMENT1
, P.NAME
, T.TASK_NUMBER
, T.TASK_NAME
, ET.EXPENDITURE_TYPE
, ET.EXPENDITURE_CATEGORY
, ET.REVENUE_CATEGORY_CODE
, NVL(RL.NEED_BY_DATE
, RL.CREATION_DATE)
, O.NAME
, RH.REQUISITION_HEADER_ID
, RL.REQUISITION_LINE_ID
, RD.DISTRIBUTION_ID
, P.PROJECT_ID
, T.TASK_ID
, O.ORGANIZATION_ID
, RL.ITEM_ID
, ( SELECT ITEM_NUMBER
FROM MTL_ITEM_FLEXFIELDS
WHERE INVENTORY_ITEM_ID = RL.ITEM_ID
AND ORGANIZATION_ID = RL.DESTINATION_ORGANIZATION_ID )
, RL.BOM_RESOURCE_ID
, BR.RESOURCE_CODE
, R.UOM_CODE
, R.UNIT_OF_MEASURE_TL
FROM PER_ALL_PEOPLE_F REQ
, PO_DOCUMENT_TYPES_ALL_TL PDT
, PO_LINE_TYPES_B LT
, PO_REQUISITION_LINES_ALL RL
, PO_REQUISITION_HEADERS_ALL RH
, PA_TASKS T
, HR_ALL_ORGANIZATION_UNITS_TL O
, PA_EXPENDITURE_TYPES ET
, PO_REQ_DISTRIBUTIONS_ALL RD
, PA_PROJECTS P
, GL_SETS_OF_BOOKS G
, BOM_RESOURCES BR
, ( SELECT RL.ROWID RL_ROWID
, RD.ROWID RD_ROWID
, NVL( RD.PROJECT_ID
, POP.COMMON_PROJECT_ID ) PROJECT_ID
, NVL( RD.TASK_ID
, PJM_TASK_AUTO_ASSIGN.INV_TASK_WNPS ( RL.DESTINATION_ORGANIZATION_ID
, NVL( RD.PROJECT_ID
, POP.COMMON_PROJECT_ID )
, RL.ITEM_ID
, NULL
, NULL
, RL.DESTINATION_SUBINVENTORY ) ) TASK_ID
, PJM_COMMITMENT_UTILS.MTL_EXPENDITURE_TYPE ( RL.DESTINATION_ORGANIZATION_ID
, RL.ITEM_ID ) EXPENDITURE_TYPE
, RL.DESTINATION_ORGANIZATION_ID EXPENDITURE_ORG_ID
, MSI.PRIMARY_UOM_CODE UOM_CODE
, MSI.PRIMARY_UNIT_OF_MEASURE UNIT_OF_MEASURE
, UOM.UNIT_OF_MEASURE_TL
, DECODE( RL.UNIT_MEAS_LOOKUP_CODE
, MSI.PRIMARY_UNIT_OF_MEASURE
, 1
, INV_CONVERT.INV_UM_CONVERT ( RL.ITEM_ID
, 5
, 1
, NULL
, NULL
, RL.UNIT_MEAS_LOOKUP_CODE
, MSI.PRIMARY_UNIT_OF_MEASURE ) ) UOM_CONVERSION_RATE
FROM PO_REQ_DISTRIBUTIONS_ALL RD
, PO_REQUISITION_LINES_ALL RL
, PJM_ORG_PARAMETERS POP
, MTL_SYSTEM_ITEMS MSI
, MTL_UNITS_OF_MEASURE_VL UOM
WHERE RL.DESTINATION_TYPE_CODE = 'INVENTORY'
AND RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID
AND POP.ORGANIZATION_ID = RL.DESTINATION_ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = RL.DESTINATION_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = RL.ITEM_ID
AND UOM.UOM_CODE = MSI.PRIMARY_UOM_CODE UNION ALL SELECT RL.ROWID
, RD.ROWID
, NVL( RD.PROJECT_ID
, POP.COMMON_PROJECT_ID )
, NVL( RD.TASK_ID
, PJM_TASK_AUTO_ASSIGN.WIP_TASK_WNPS ( RL.DESTINATION_ORGANIZATION_ID
, NVL( RD.PROJECT_ID
, POP.COMMON_PROJECT_ID )
, WO.STANDARD_OPERATION_ID
, WDJ.WIP_ENTITY_ID
, WDJ.PRIMARY_ITEM_ID
, WO.DEPARTMENT_ID ) )
, PJM_COMMITMENT_UTILS.OSP_EXPENDITURE_TYPE ( RL.DESTINATION_ORGANIZATION_ID
, NVL( RD.PROJECT_ID
, POP.COMMON_PROJECT_ID )
, RL.BOM_RESOURCE_ID )
, BD.PA_EXPENDITURE_ORG_ID
, UOM.UOM_CODE
, UOM.UNIT_OF_MEASURE
, UOM.UNIT_OF_MEASURE_TL
, 1
FROM PO_REQ_DISTRIBUTIONS_ALL RD
, PO_REQUISITION_LINES_ALL RL
, PJM_ORG_PARAMETERS POP
, WIP_DISCRETE_JOBS WDJ
, WIP_OPERATIONS WO
, BOM_DEPARTMENTS BD
, MTL_UNITS_OF_MEASURE_VL UOM
WHERE RL.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
AND RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID
AND POP.ORGANIZATION_ID = RL.DESTINATION_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = RL.DESTINATION_ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = RL.WIP_ENTITY_ID
AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM = RL.WIP_OPERATION_SEQ_NUM
AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
AND UOM.UNIT_OF_MEASURE = RL.UNIT_MEAS_LOOKUP_CODE ) R
WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
AND RH.TYPE_LOOKUP_CODE = 'PURCHASE'
AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND PDT.DOCUMENT_SUBTYPE = RH.TYPE_LOOKUP_CODE
AND NVL( PDT.ORG_ID
, -99 ) = NVL( RH.ORG_ID
, -99 )
AND PDT.LANGUAGE = USERENV('LANG')
AND RL.LINE_LOCATION_ID IS NULL
AND NVL(RL.CLOSED_CODE
, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(RL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(RL.MODIFIED_BY_AGENT_FLAG
, 'N') = 'N'
AND RL.SOURCE_TYPE_CODE = 'VENDOR'
AND REQ.PERSON_ID = RL.TO_PERSON_ID
AND TRUNC(SYSDATE) BETWEEN REQ.EFFECTIVE_START_DATE
AND REQ.EFFECTIVE_END_DATE
AND RL.LINE_TYPE_ID = LT.LINE_TYPE_ID
AND RL.ROWID = R.RL_ROWID
AND RD.ROWID = R.RD_ROWID
AND P.PROJECT_ID = R.PROJECT_ID
AND T.TASK_ID = R.TASK_ID
AND O.ORGANIZATION_ID = R.EXPENDITURE_ORG_ID
AND O.LANGUAGE = USERENV('LANG')
AND ET.EXPENDITURE_TYPE = R.EXPENDITURE_TYPE
AND G.SET_OF_BOOKS_ID = RD.SET_OF_BOOKS_ID
AND BR.RESOURCE_ID (+) = RL.BOM_RESOURCE_ID

Columns