eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: PJM_PO_COMMITMENTS_V

Product:	PJM - Project Manufacturing
Description:	Project-related unreceived purchase order distributions view
Implementation/DBA Data:	Not implemented in this database

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
, DECODE(LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POL.UNIT_MEAS_LOOKUP_CODE)
, DECODE(LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, TO_NUMBER(NULL)
, ( PLL.PRICE_OVERRIDE * NVL(POD.RATE
, 1) ) )
, POH.CURRENCY_CODE
, G.CURRENCY_CODE
, DECODE(NVL(POH.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, TO_DATE(NULL)
, POD.RATE_DATE)
, DECODE(NVL(POH.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, NULL
, POH.RATE_TYPE)
, DECODE(NVL(POH.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, TO_NUMBER(NULL)
, POD.RATE)
, ( POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) )
, ( POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) ) * ( PLL.PRICE_OVERRIDE * NVL(POD.RATE
, 1) )
, POD.QUANTITY_ORDERED
, POD.QUANTITY_ORDERED * ( PLL.PRICE_OVERRIDE * NVL(POD.RATE
, 1) )
, POD.QUANTITY_CANCELLED
, POD.QUANTITY_CANCELLED * ( PLL.PRICE_OVERRIDE * NVL(POD.RATE
, 1) )
, POD.QUANTITY_DELIVERED
, POD.QUANTITY_DELIVERED * ( PLL.PRICE_OVERRIDE )
, POD.QUANTITY_DELIVERED * ( PLL.PRICE_OVERRIDE * NVL(POD.RATE
, 1) )
, NVL(POD.QUANTITY_BILLED
, 0)
, NVL(POD.AMOUNT_BILLED
, 0)
, ( POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) - NVL(POD.QUANTITY_DELIVERED
, 0) )
, ( 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) ) )
, 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) ) )
, 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
FROM PO_HEADERS_ALL POH
, PO_RELEASES_ALL POR
, PO_DOCUMENT_TYPES PDT
, PO_VENDORS V
, PO_LINES_ALL POL
, PO_LINE_TYPES LT
, PO_LINE_LOCATIONS_ALL PLL
, PER_PEOPLE_F BUY
, PER_PEOPLE_F REQ
, HR_ORGANIZATION_UNITS O
, PA_EXPENDITURE_TYPES ET
, PA_TASKS T
, PO_DISTRIBUTIONS_ALL POD
, PA_PROJECTS P
, MTL_PARAMETERS MP
, GL_SETS_OF_BOOKS G
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
, TRUNC(SYSDATE))
AND NVL(REQ.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
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 POH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
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 P.PROJECT_ID = NVL(POD.PROJECT_ID
, PJM_COMMON_PROJ_PKG.GET_COMMON_PROJECT( POD.DESTINATION_ORGANIZATION_ID))
AND T.TASK_ID = NVL(POD.TASK_ID
, PJM_TASK_AUTO_ASSIGN.INV_TASK_WNPS( POD.DESTINATION_ORGANIZATION_ID
, P.PROJECT_ID
, POL.ITEM_ID
, POH.PO_HEADER_ID
, NULL
, POD.DESTINATION_SUBINVENTORY))
AND MP.ORGANIZATION_ID = O.ORGANIZATION_ID
AND PROJECT_MFG.MATL_SUBELEMENT( POL.ITEM_ID
, MP.AVG_RATES_COST_TYPE_ID
, MP.ORGANIZATION_ID) = ET.EXPENDITURE_TYPE
AND POD.DESTINATION_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND POD.DESTINATION_TYPE_CODE = ('INVENTORY')
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 UNION ALL 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
, DECODE(LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POL.UNIT_MEAS_LOOKUP_CODE)
, DECODE(LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, TO_NUMBER(NULL)
, ( PLL.PRICE_OVERRIDE * NVL(POD.RATE
, 1) ) )
, POH.CURRENCY_CODE
, G.CURRENCY_CODE
, DECODE(NVL(POH.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, TO_DATE(NULL)
, POD.RATE_DATE)
, DECODE(NVL(POH.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, NULL
, POH.RATE_TYPE)
, DECODE(NVL(POH.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, TO_NUMBER(NULL)
, POD.RATE)
, ( POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) )
, ( POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) ) * ( PLL.PRICE_OVERRIDE * NVL(POD.RATE
, 1) )
, POD.QUANTITY_ORDERED
, POD.QUANTITY_ORDERED * ( PLL.PRICE_OVERRIDE * NVL(POD.RATE
, 1) )
, POD.QUANTITY_CANCELLED
, POD.QUANTITY_CANCELLED * ( PLL.PRICE_OVERRIDE * NVL(POD.RATE
, 1) )
, POD.QUANTITY_DELIVERED
, POD.QUANTITY_DELIVERED * ( PLL.PRICE_OVERRIDE )
, POD.QUANTITY_DELIVERED * ( PLL.PRICE_OVERRIDE * NVL(POD.RATE
, 1) )
, NVL(POD.QUANTITY_BILLED
, 0)
, NVL(POD.AMOUNT_BILLED
, 0)
, ( POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) - NVL(POD.QUANTITY_DELIVERED
, 0) )
, ( 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) ) )
, 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) ) )
, 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
, BR.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
FROM PO_HEADERS_ALL POH
, PO_RELEASES_ALL POR
, PO_DOCUMENT_TYPES PDT
, PO_VENDORS V
, PO_LINES_ALL POL
, PO_LINE_TYPES LT
, PO_LINE_LOCATIONS_ALL PLL
, PER_PEOPLE_F BUY
, PER_PEOPLE_F REQ
, HR_ORGANIZATION_UNITS O
, PA_EXPENDITURE_TYPES ET
, PA_TASKS T
, PO_DISTRIBUTIONS_ALL POD
, PA_PROJECTS P
, WIP_DISCRETE_JOBS WDJ
, WIP_OPERATIONS WO
, BOM_RESOURCES BR
, BOM_DEPARTMENTS BD
, GL_SETS_OF_BOOKS G
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
, TRUNC(SYSDATE))
AND NVL(REQ.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
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 POH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
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 P.PROJECT_ID = NVL(POD.PROJECT_ID
, PJM_COMMON_PROJ_PKG.GET_COMMON_PROJECT( POD.DESTINATION_ORGANIZATION_ID))
AND T.TASK_ID = NVL(POD.TASK_ID
, PJM_TASK_AUTO_ASSIGN.WIP_TASK_WNPS( POD.DESTINATION_ORGANIZATION_ID
, P.PROJECT_ID
, WO.STANDARD_OPERATION_ID
, WDJ.WIP_ENTITY_ID
, WDJ.PRIMARY_ITEM_ID
, WO.DEPARTMENT_ID))
AND BD.PA_EXPENDITURE_ORG_ID = O.ORGANIZATION_ID
AND BR.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
AND POD.DESTINATION_TYPE_CODE = ('SHOP FLOOR')
AND POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) > NVL(POD.QUANTITY_DELIVERED
, 0)
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 BR.RESOURCE_ID = POD.BOM_RESOURCE_ID
AND G.SET_OF_BOOKS_ID = POD.SET_OF_BOOKS_ID

Columns

Name
PO_NUMBER
PO_REVISION
RELEASE_NUMBER
RELEASE_REVISION
APPROVED_FLAG
EVER_APPROVED_FLAG
DOCUMENT_TYPE
CREATION_DATE
APPROVED_DATE
PRINTED_DATE
REQUESTOR_PERSON_ID
REQUESTOR_NAME
BUYER_PERSON_ID
BUYER_NAME
VENDOR_NAME
VENDOR_ID
PO_LINE
ITEM_DESCRIPTION
UNIT
UNIT_PRICE
DENOM_CURRENCY_CODE
ACCT_CURRENCY_CODE
ACCT_RATE_DATE
ACCT_RATE_TYPE
ACCT_EXCHANGE_RATE
QUANTITY_ORDERED
AMOUNT_ORDERED
ORIGINAL_QUANTITY_ORDERED
ORIGINAL_AMOUNT_ORDERED
QUANTITY_CANCELLED
AMOUNT_CANCELLED
QUANTITY_DELIVERED
DENOM_AMOUNT_DELIVERED
AMOUNT_DELIVERED
QUANTITY_INVOICED
AMOUNT_INVOICED
QUANTITY_OUTSTANDING_DELIVERY
DENOM_AMT_OUTSTANDING_DELIVERY
AMOUNT_OUTSTANDING_DELIVERY
QUANTITY_OUTSTANDING_INVOICE
DENOM_AMT_OUTSTANDING_INVOICE
AMOUNT_OUTSTANDING_INVOICE
QUANTITY_OVERBILLED
AMOUNT_OVERBILLED
PROJECT_NUMBER
PROJECT_NAME
TASK_NUMBER
TASK_NAME
EXPENDITURE_TYPE
EXPENDITURE_CATEGORY
REVENUE_CATEGORY
EXPENDITURE_ITEM_DATE
EXPENDITURE_ORGANIZATION
PO_HEADER_ID
PO_RELEASE_ID
PO_LINE_ID
PO_LINE_LOCATION_ID
PO_DISTRIBUTION_ID
PROJECT_ID
TASK_ID
EXPENDITURE_ORGANIZATION_ID
PROMISED_DATE
NEED_BY_DATE