VIEW: APPS.PJM_PO_COMMITMENTS_BASIC_V

Source

View Text - Preformatted

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' ) , PJM_COMMITMENT_UTILS.PO_TYPE(POH.ORG_ID,POH.TYPE_LOOKUP_CODE) , 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 ) , POD.DELIVER_TO_PERSON_ID , PJM_COMMITMENT_UTILS.PEOPLE_NAME(POD.DELIVER_TO_PERSON_ID) , POH.AGENT_ID , PJM_COMMITMENT_UTILS.PEOPLE_NAME(POH.AGENT_ID) , PJM_COMMITMENT_UTILS.VENDOR_NAME(POH.VENDOR_ID) , POH.VENDOR_ID , POL.LINE_NUM , POL.ITEM_DESCRIPTION , PJM_COMMITMENT_UTILS.GET_UNIT(POL.UNIT_MEAS_LOOKUP_CODE, POD.DESTINATION_TYPE_CODE,POL.ITEM_ID,POD.DESTINATION_ORGANIZATION_ID) , PLL.PRICE_OVERRIDE * NVL( POD.RATE , 1 ) / NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE,POD.DESTINATION_TYPE_CODE, POL.ITEM_ID,POD.DESTINATION_ORGANIZATION_ID) , 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( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE,POD.DESTINATION_TYPE_CODE, POL.ITEM_ID,POD.DESTINATION_ORGANIZATION_ID) , 1 ) , ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED , 0 ) ) * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE , 1 ) ) , POD.QUANTITY_ORDERED * NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE,POD.DESTINATION_TYPE_CODE, POL.ITEM_ID,POD.DESTINATION_ORGANIZATION_ID) , 1 ) , POD.QUANTITY_ORDERED * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE , 1 ) ) , POD.QUANTITY_CANCELLED * NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE,POD.DESTINATION_TYPE_CODE, POL.ITEM_ID,POD.DESTINATION_ORGANIZATION_ID) , 1 ) , POD.QUANTITY_CANCELLED * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE , 1 ) ) , POD.QUANTITY_DELIVERED * NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE,POD.DESTINATION_TYPE_CODE, POL.ITEM_ID,POD.DESTINATION_ORGANIZATION_ID) , 1 ) , POD.QUANTITY_DELIVERED * ( PLL.PRICE_OVERRIDE ) , POD.QUANTITY_DELIVERED * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE , 1 )) , NVL( POD.QUANTITY_BILLED , 0 ) * NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE,POD.DESTINATION_TYPE_CODE, POL.ITEM_ID,POD.DESTINATION_ORGANIZATION_ID) , 1 ) , NVL( POD.AMOUNT_BILLED , 0 ) , ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED , 0 ) - NVL( POD.QUANTITY_DELIVERED , 0 ) ) * NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE,POD.DESTINATION_TYPE_CODE, POL.ITEM_ID,POD.DESTINATION_ORGANIZATION_ID) , 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( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE,POD.DESTINATION_TYPE_CODE, POL.ITEM_ID,POD.DESTINATION_ORGANIZATION_ID) , 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( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE,POD.DESTINATION_TYPE_CODE, POL.ITEM_ID,POD.DESTINATION_ORGANIZATION_ID) , 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 , PJM_COMMITMENT_UTILS.ITEM_NUMBER(POL.ITEM_ID, POD.DESTINATION_ORGANIZATION_ID) , POD.BOM_RESOURCE_ID , PJM_COMMITMENT_UTILS.BOM_RESOURCE(POD.BOM_RESOURCE_ID) , PJM_COMMITMENT_UTILS.GET_UOM_CODE(POL.UNIT_MEAS_LOOKUP_CODE, POD.DESTINATION_TYPE_CODE,POL.ITEM_ID,POD.DESTINATION_ORGANIZATION_ID) , PJM_COMMITMENT_UTILS.GET_UOM_TL(POL.UNIT_MEAS_LOOKUP_CODE, POD.DESTINATION_TYPE_CODE,POL.ITEM_ID,POD.DESTINATION_ORGANIZATION_ID) FROM PA_PROJECTS_ALL P , PA_TASKS T , PO_RELEASES_ALL POR , PO_HEADERS_ALL POH , PO_LINES_ALL POL , PO_LINE_LOCATIONS_ALL PLL , PO_DISTRIBUTIONS_ALL POD , GL_SETS_OF_BOOKS G , PA_EXPENDITURE_TYPES ET , HR_ALL_ORGANIZATION_UNITS_TL O WHERE POD.DESTINATION_TYPE_CODE in ('INVENTORY', 'SHOP FLOOR') AND POL.PO_LINE_ID = POD.PO_LINE_ID AND ( (NVL( POH.CLOSED_CODE , 'OPEN' ) NOT IN ( 'CLOSED' , 'FINALLY CLOSED' ) AND NVL( PLL.CLOSED_CODE , 'OPEN' ) NOT IN ( 'CLOSED' , 'FINALLY CLOSED' ) ) OR ( (NVL( POH.CLOSED_CODE , 'OPEN' ) = 'CLOSED' OR NVL( PLL.CLOSED_CODE , 'OPEN' ) = 'CLOSED') AND PLL.QUANTITY - NVL( PLL.QUANTITY_CANCELLED , 0 ) = NVL(PLL.QUANTITY_RECEIVED , 0) ) ) AND POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED , 0 ) > NVL( POD.QUANTITY_DELIVERED , 0 ) AND PLL.SHIPMENT_TYPE IN ( 'STANDARD' , 'BLANKET' , 'SCHEDULED' ) AND POH.TYPE_LOOKUP_CODE IN ( 'STANDARD' , 'BLANKET' , 'PLANNED' ) 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.PO_LINE_ID = PLL.PO_LINE_ID AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID AND P.PROJECT_ID = POD.PROJECT_ID AND T.TASK_ID = PJM_COMMITMENT_UTILS.PO_TASK_ID (POD.DESTINATION_ORGANIZATION_ID, P.PROJECT_ID, POD.destination_type_code, POL.ITEM_ID, POD.DESTINATION_SUBINVENTORY, POD.TASK_ID, POD.WIP_ENTITY_ID, POD.WIP_OPERATION_SEQ_NUM ) AND G.SET_OF_BOOKS_ID = POD.SET_OF_BOOKS_ID AND ET.EXPENDITURE_TYPE = PJM_COMMITMENT_UTILS.PO_EXP_TYPE (POD.DESTINATION_ORGANIZATION_ID , P.PROJECT_ID, POL.ITEM_ID, POD.BOM_RESOURCE_ID, POD.DESTINATION_TYPE_CODE) AND O.ORGANIZATION_ID = PJM_COMMITMENT_UTILS.PO_EXP_ORG (DESTINATION_ORGANIZATION_ID, POD.WIP_ENTITY_ID, POD.WIP_OPERATION_SEQ_NUM, POD.DESTINATION_TYPE_CODE) AND O.LANGUAGE = USERENV('LANG')

View Text - HTML Formatted

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' )
, PJM_COMMITMENT_UTILS.PO_TYPE(POH.ORG_ID
, POH.TYPE_LOOKUP_CODE)
, 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 )
, POD.DELIVER_TO_PERSON_ID
, PJM_COMMITMENT_UTILS.PEOPLE_NAME(POD.DELIVER_TO_PERSON_ID)
, POH.AGENT_ID
, PJM_COMMITMENT_UTILS.PEOPLE_NAME(POH.AGENT_ID)
, PJM_COMMITMENT_UTILS.VENDOR_NAME(POH.VENDOR_ID)
, POH.VENDOR_ID
, POL.LINE_NUM
, POL.ITEM_DESCRIPTION
, PJM_COMMITMENT_UTILS.GET_UNIT(POL.UNIT_MEAS_LOOKUP_CODE
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
, PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ) / NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
, 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( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
, 1 )
, ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) ) * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ) )
, POD.QUANTITY_ORDERED * NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
, 1 )
, POD.QUANTITY_ORDERED * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ) )
, POD.QUANTITY_CANCELLED * NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
, 1 )
, POD.QUANTITY_CANCELLED * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ) )
, POD.QUANTITY_DELIVERED * NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
, 1 )
, POD.QUANTITY_DELIVERED * ( PLL.PRICE_OVERRIDE )
, POD.QUANTITY_DELIVERED * ( PLL.PRICE_OVERRIDE * NVL( POD.RATE
, 1 ))
, NVL( POD.QUANTITY_BILLED
, 0 ) * NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
, 1 )
, NVL( POD.AMOUNT_BILLED
, 0 )
, ( POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) - NVL( POD.QUANTITY_DELIVERED
, 0 ) ) * NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
, 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( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
, 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( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (POL.UNIT_MEAS_LOOKUP_CODE
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
, 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
, PJM_COMMITMENT_UTILS.ITEM_NUMBER(POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
, POD.BOM_RESOURCE_ID
, PJM_COMMITMENT_UTILS.BOM_RESOURCE(POD.BOM_RESOURCE_ID)
, PJM_COMMITMENT_UTILS.GET_UOM_CODE(POL.UNIT_MEAS_LOOKUP_CODE
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
, PJM_COMMITMENT_UTILS.GET_UOM_TL(POL.UNIT_MEAS_LOOKUP_CODE
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_ORGANIZATION_ID)
FROM PA_PROJECTS_ALL P
, PA_TASKS T
, PO_RELEASES_ALL POR
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, PO_DISTRIBUTIONS_ALL POD
, GL_SETS_OF_BOOKS G
, PA_EXPENDITURE_TYPES ET
, HR_ALL_ORGANIZATION_UNITS_TL O
WHERE POD.DESTINATION_TYPE_CODE IN ('INVENTORY'
, 'SHOP FLOOR')
AND POL.PO_LINE_ID = POD.PO_LINE_ID
AND ( (NVL( POH.CLOSED_CODE
, 'OPEN' ) NOT IN ( 'CLOSED'
, 'FINALLY CLOSED' )
AND NVL( PLL.CLOSED_CODE
, 'OPEN' ) NOT IN ( 'CLOSED'
, 'FINALLY CLOSED' ) ) OR ( (NVL( POH.CLOSED_CODE
, 'OPEN' ) = 'CLOSED' OR NVL( PLL.CLOSED_CODE
, 'OPEN' ) = 'CLOSED')
AND PLL.QUANTITY - NVL( PLL.QUANTITY_CANCELLED
, 0 ) = NVL(PLL.QUANTITY_RECEIVED
, 0) ) )
AND POD.QUANTITY_ORDERED - NVL( POD.QUANTITY_CANCELLED
, 0 ) > NVL( POD.QUANTITY_DELIVERED
, 0 )
AND PLL.SHIPMENT_TYPE IN ( 'STANDARD'
, 'BLANKET'
, 'SCHEDULED' )
AND POH.TYPE_LOOKUP_CODE IN ( 'STANDARD'
, 'BLANKET'
, 'PLANNED' )
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.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND P.PROJECT_ID = POD.PROJECT_ID
AND T.TASK_ID = PJM_COMMITMENT_UTILS.PO_TASK_ID (POD.DESTINATION_ORGANIZATION_ID
, P.PROJECT_ID
, POD.DESTINATION_TYPE_CODE
, POL.ITEM_ID
, POD.DESTINATION_SUBINVENTORY
, POD.TASK_ID
, POD.WIP_ENTITY_ID
, POD.WIP_OPERATION_SEQ_NUM )
AND G.SET_OF_BOOKS_ID = POD.SET_OF_BOOKS_ID
AND ET.EXPENDITURE_TYPE = PJM_COMMITMENT_UTILS.PO_EXP_TYPE (POD.DESTINATION_ORGANIZATION_ID
, P.PROJECT_ID
, POL.ITEM_ID
, POD.BOM_RESOURCE_ID
, POD.DESTINATION_TYPE_CODE)
AND O.ORGANIZATION_ID = PJM_COMMITMENT_UTILS.PO_EXP_ORG (DESTINATION_ORGANIZATION_ID
, POD.WIP_ENTITY_ID
, POD.WIP_OPERATION_SEQ_NUM
, POD.DESTINATION_TYPE_CODE)
AND O.LANGUAGE = USERENV('LANG')