View: PJM_PO_COMMITMENTS_BASIC_V

Product:	PJM - Project Manufacturing
Description:	Project-related unreceived purchase order distributions view
Implementation/DBA Data:	APPS.PJM_PO_COMMITMENTS_BASIC_V

View Text

SELECT POH.SEGMENT1
, POH.REVISION_NUM
, POR.RELEASE_NUM
, POR.REVISION_NUM
, DECODE( POR.RELEASE_NUM
, NULL
, DECODE( POH.AUTHORIZATION_STATUS
, 'APPROVED'
, 'Y'
, 'N' )
, DECODE( POR.AUTHORIZATION_STATUS
, 'APPROVED'
, 'Y'
, 'N' ) )
, DECODE( POD.REQ_DISTRIBUTION_ID
, NULL
, DECODE( POR.RELEASE_NUM
, NULL
, DECODE( POH.APPROVED_DATE
, NULL
, 'N'
, 'Y' )
, DECODE( POR.APPROVED_DATE
, NULL
, 'N'
, 'Y' ) )
, 'Y' )
, PDT.TYPE_NAME
, DECODE( POR.RELEASE_NUM
, NULL
, POH.CREATION_DATE
, POR.CREATION_DATE )
, DECODE( POR.RELEASE_NUM
, NULL
, POH.APPROVED_DATE
, POR.APPROVED_DATE )
, DECODE( POR.RELEASE_NUM
, NULL
, POH.PRINTED_DATE
, POR.PRINTED_DATE )
, REQ.PERSON_ID
, REQ.FULL_NAME
, BUY.PERSON_ID
, BUY.FULL_NAME
, V.VENDOR_NAME
, V.VENDOR_ID
, POL.LINE_NUM
, POL.ITEM_DESCRIPTION
, D.UNIT_OF_MEASURE
, PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ) / NVL( D.UOM_CONVERSION_RATE
, 1 )
, POH.CURRENCY_CODE
, G.CURRENCY_CODE
, DECODE( NVL( POH.CURRENCY_CODE
, G.CURRENCY_CODE )
, G.CURRENCY_CODE
, TO_DATE(NULL)
, POD.RATE_DATE )
, DECODE( NVL( POH.CURRENCY_CODE
, G.CURRENCY_CODE )
, G.CURRENCY_CODE
, NULL
, POH.RATE_TYPE )
, DECODE( NVL( POH.CURRENCY_CODE
, G.CURRENCY_CODE )
, G.CURRENCY_CODE
, TO_NUMBER(NULL)
, POD.RATE )
, ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) ) * NVL( D.UOM_CONVERSION_RATE
, 1 )
, ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) ) * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ) )
, POD.QUANTITY_ORDERED * NVL( D.UOM_CONVERSION_RATE
, 1 )
, POD.QUANTITY_ORDERED * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ) )
, POD.QUANTITY_CANCELLED * NVL( D.UOM_CONVERSION_RATE
, 1 )
, POD.QUANTITY_CANCELLED * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ) )
, POD.QUANTITY_DELIVERED * NVL( D.UOM_CONVERSION_RATE
, 1 )
, POD.QUANTITY_DELIVERED * ( PLL.PRICE_OVERRIDE )
, POD.QUANTITY_DELIVERED * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ) )
, NVL( POD.QUANTITY_BILLED
, 0 ) * NVL( D.UOM_CONVERSION_RATE
, 1 )
, NVL( POD.AMOUNT_BILLED
, 0 )
, ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) - NVL( POD.QUANTITY_DELIVERED
, 0 ) ) * NVL( D.UOM_CONVERSION_RATE
, 1 )
, ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) - NVL( POD.QUANTITY_DELIVERED
, 0 ) ) * ( PLL.PRICE_OVERRIDE )
, ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) - NVL( POD.QUANTITY_DELIVERED
, 0 ) ) * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ) )
, GREATEST(0
, ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) - NVL( POD.QUANTITY_BILLED
, 0 ) ) ) * NVL( D.UOM_CONVERSION_RATE
, 1 )
, GREATEST(0
, ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) - NVL( POD.QUANTITY_BILLED
, 0 ) ) ) * ( PLL.PRICE_OVERRIDE )
, GREATEST(0
, ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) - NVL( POD.QUANTITY_BILLED
, 0 ) ) ) * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ) )
, GREATEST(0
, (-1 ) * ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) - NVL( POD.QUANTITY_BILLED
, 0 ) ) ) * NVL( D.UOM_CONVERSION_RATE
, 1 )
, GREATEST(0
, (-1 ) * ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) - NVL( POD.QUANTITY_BILLED
, 0 ) ) ) * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ) )
, P.SEGMENT1
, P.NAME
, T.TASK_NUMBER
, T.TASK_NAME
, ET.EXPENDITURE_TYPE
, ET.EXPENDITURE_CATEGORY
, ET.REVENUE_CATEGORY_CODE
, NVL( PLL.NEED_BY_DATE
, NVL( PLL.PROMISED_DATE
, PLL.CREATION_DATE ) )
, O.NAME
, POH.PO_HEADER_ID
, POR.PO_RELEASE_ID
, POL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, POD.PO_DISTRIBUTION_ID
, P.PROJECT_ID
, T.TASK_ID
, O.ORGANIZATION_ID
, PLL.PROMISED_DATE
, PLL.NEED_BY_DATE
, POL.ITEM_ID
, ( SELECT ITEM_NUMBER
FROM MTL_ITEM_FLEXFIELDS
WHERE INVENTORY_ITEM_ID = POL.ITEM_ID
AND ORGANIZATION_ID = POD.DESTINATION_ORGANIZATION_ID )
, POD.BOM_RESOURCE_ID
, BR.RESOURCE_CODE
, D.UOM_CODE
, D.UNIT_OF_MEASURE_TL
FROM PO_HEADERS_ALL POH
, PO_RELEASES_ALL POR
, PO_DOCUMENT_TYPES_ALL_TL PDT
, PO_VENDORS V
, PO_LINES_ALL POL
, PO_LINE_TYPES_B LT
, PO_LINE_LOCATIONS_ALL PLL
, PER_ALL_PEOPLE_F BUY
, PER_ALL_PEOPLE_F REQ
, HR_ALL_ORGANIZATION_UNITS_TL O
, PA_EXPENDITURE_TYPES ET
, PA_TASKS T
, PO_DISTRIBUTIONS_ALL POD
, PA_PROJECTS P
, GL_SETS_OF_BOOKS G
, BOM_RESOURCES BR
, ( SELECT POD.ROWID POD_ROWID
, POD.PROJECT_ID
, NVL( POD.TASK_ID
, PJM_TASK_AUTO_ASSIGN.INV_TASK_WNPS ( POD.DESTINATION_ORGANIZATION_ID
, POD.PROJECT_ID
, POL.ITEM_ID
, NULL
, NULL
, POD.DESTINATION_SUBINVENTORY ) ) TASK_ID
, PJM_COMMITMENT_UTILS.MTL_EXPENDITURE_TYPE ( POD.DESTINATION_ORGANIZATION_ID
, POL.ITEM_ID ) EXPENDITURE_TYPE
, POD.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( POL.UNIT_MEAS_LOOKUP_CODE
, MSI.PRIMARY_UNIT_OF_MEASURE
, 1
, INV_CONVERT.INV_UM_CONVERT ( POL.ITEM_ID
, 5
, 1
, NULL
, NULL
, POL.UNIT_MEAS_LOOKUP_CODE
, MSI.PRIMARY_UNIT_OF_MEASURE ) ) UOM_CONVERSION_RATE
FROM PO_DISTRIBUTIONS_ALL POD
, PO_LINES_ALL POL
, MTL_SYSTEM_ITEMS MSI
, MTL_UNITS_OF_MEASURE_VL UOM
WHERE POD.DESTINATION_TYPE_CODE = 'INVENTORY'
AND POL.PO_LINE_ID = POD.PO_LINE_ID
AND MSI.ORGANIZATION_ID = POD.DESTINATION_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID
AND UOM.UOM_CODE = MSI.PRIMARY_UOM_CODE UNION ALL SELECT POD.ROWID
, POD.PROJECT_ID
, NVL( POD.TASK_ID
, PJM_TASK_AUTO_ASSIGN.WIP_TASK_WNPS ( POD.DESTINATION_ORGANIZATION_ID
, POD.PROJECT_ID
, WO.STANDARD_OPERATION_ID
, WDJ.WIP_ENTITY_ID
, WDJ.PRIMARY_ITEM_ID
, WO.DEPARTMENT_ID ) )
, PJM_COMMITMENT_UTILS.OSP_EXPENDITURE_TYPE ( POD.DESTINATION_ORGANIZATION_ID
, POD.PROJECT_ID
, POD.BOM_RESOURCE_ID )
, BD.PA_EXPENDITURE_ORG_ID
, UOM.UOM_CODE
, UOM.UNIT_OF_MEASURE
, UOM.UNIT_OF_MEASURE_TL
, 1
FROM PO_DISTRIBUTIONS_ALL POD
, PO_LINES_ALL POL
, WIP_DISCRETE_JOBS WDJ
, WIP_OPERATIONS WO
, BOM_DEPARTMENTS BD
, MTL_UNITS_OF_MEASURE_VL UOM
WHERE POD.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
AND POL.PO_LINE_ID = POD.PO_LINE_ID
AND WDJ.ORGANIZATION_ID = POD.DESTINATION_ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = POD.WIP_ENTITY_ID
AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM = POD.WIP_OPERATION_SEQ_NUM
AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
AND UOM.UNIT_OF_MEASURE = POL.UNIT_MEAS_LOOKUP_CODE ) D
WHERE POH.VENDOR_ID = V.VENDOR_ID
AND POH.AGENT_ID = BUY.PERSON_ID
AND TRUNC(SYSDATE ) BETWEEN BUY.EFFECTIVE_START_DATE
AND BUY.EFFECTIVE_END_DATE
AND POD.DELIVER_TO_PERSON_ID = REQ.PERSON_ID (+ )
AND TRUNC(SYSDATE ) BETWEEN NVL( REQ.EFFECTIVE_START_DATE
, SYSDATE - 1 )
AND NVL( REQ.EFFECTIVE_END_DATE
, SYSDATE + 1 )
AND NVL( POH.CLOSED_CODE
, 'OPEN' ) NOT IN ( 'CLOSED'
, 'FINALLY CLOSED' )
AND NVL( PLL.CLOSED_CODE
, 'OPEN' ) NOT IN ( 'CLOSED'
, 'FINALLY CLOSED' )
AND PLL.SHIPMENT_TYPE IN ( 'STANDARD'
, 'BLANKET'
, 'SCHEDULED' )
AND POH.TYPE_LOOKUP_CODE IN ( 'STANDARD'
, 'BLANKET'
, 'PLANNED' )
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDT.DOCUMENT_TYPE_CODE IN ( 'PO'
, 'PA' )
AND NVL( PDT.ORG_ID
, -99 ) = NVL( POH.ORG_ID
, -99 )
AND PDT.LANGUAGE = USERENV('LANG')
AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
AND NVL( POH.CANCEL_FLAG
, 'N' ) = 'N'
AND DECODE( POR.RELEASE_NUM
, NULL
, 'OPEN'
, NVL( POR.CLOSED_CODE
, 'OPEN' ) ) NOT IN ( 'CLOSED'
, 'FINALLY CLOSED' )
AND DECODE( POR.RELEASE_NUM
, NULL
, 'N'
, NVL( POR.CANCEL_FLAG
, 'N' ) ) = 'N'
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND POL.LINE_TYPE_ID = LT.LINE_TYPE_ID
AND POL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POD.ROWID = D.POD_ROWID
AND P.PROJECT_ID = D.PROJECT_ID
AND T.TASK_ID = D.TASK_ID
AND O.ORGANIZATION_ID = D.EXPENDITURE_ORG_ID
AND O.LANGUAGE = USERENV('LANG')
AND ET.EXPENDITURE_TYPE = D.EXPENDITURE_TYPE
AND POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) > NVL( POD.QUANTITY_DELIVERED
, 0 )
AND G.SET_OF_BOOKS_ID = POD.SET_OF_BOOKS_ID
AND BR.RESOURCE_ID (+) = POD.BOM_RESOURCE_ID

Columns