View: PJM_REQ_COMMITMENTS_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
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, TO_DATE(NULL)
, RL.RATE_DATE)
, DECODE(NVL(RL.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, NULL
, RL.RATE_TYPE)
, DECODE(NVL(RL.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, TO_NUMBER(NULL)
, RL.RATE)
, DECODE(LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, RL.UNIT_MEAS_LOOKUP_CODE)
, TO_NUMBER(DECODE(LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, RL.UNIT_PRICE))
, RD.REQ_LINE_QUANTITY
, 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
FROM PER_PEOPLE_F REQ
, PO_DOCUMENT_TYPES PDT
, PO_LINE_TYPES LT
, PO_REQUISITION_LINES_ALL RL
, PO_REQUISITION_HEADERS_ALL RH
, PA_TASKS T
, HR_ORGANIZATION_UNITS O
, PA_EXPENDITURE_TYPES ET
, PO_REQ_DISTRIBUTIONS_ALL RD
, PA_PROJECTS P
, MTL_PARAMETERS MP
, GL_SETS_OF_BOOKS G
WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
AND RH.TYPE_LOOKUP_CODE = 'PURCHASE'
AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND RH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
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 NVL(REQ.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(REQ.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
AND RL.LINE_TYPE_ID = LT.LINE_TYPE_ID
AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
AND P.PROJECT_ID = NVL(RD.PROJECT_ID
, PJM_COMMON_PROJ_PKG.GET_COMMON_PROJECT( RL.DESTINATION_ORGANIZATION_ID))
AND T.TASK_ID = NVL(RD.TASK_ID
, PJM_TASK_AUTO_ASSIGN.INV_TASK_WNPS ( RL.DESTINATION_ORGANIZATION_ID
, P.PROJECT_ID
, RL.ITEM_ID
, NULL
, NULL
, RL.DESTINATION_SUBINVENTORY))
AND MP.ORGANIZATION_ID = O.ORGANIZATION_ID
AND PROJECT_MFG.MATL_SUBELEMENT(RL.ITEM_ID
, MP.AVG_RATES_COST_TYPE_ID
, MP.ORGANIZATION_ID) = ET.EXPENDITURE_TYPE
AND RL.DESTINATION_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND RL.DESTINATION_TYPE_CODE = ('INVENTORY')
AND G.SET_OF_BOOKS_ID = RD.SET_OF_BOOKS_ID UNION ALL 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
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, TO_DATE(NULL)
, RL.RATE_DATE)
, DECODE(NVL(RL.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, NULL
, RL.RATE_TYPE)
, DECODE(NVL(RL.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, TO_NUMBER(NULL)
, RL.RATE)
, DECODE(LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, RL.UNIT_MEAS_LOOKUP_CODE)
, DECODE(LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, TO_NUMBER(NULL)
, RL.UNIT_PRICE)
, RD.REQ_LINE_QUANTITY
, RD.REQ_LINE_QUANTITY * RL.UNIT_PRICE
, P.SEGMENT1
, P.NAME
, T.TASK_NUMBER
, T.TASK_NAME
, BR.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
FROM PER_PEOPLE_F REQ
, PO_DOCUMENT_TYPES PDT
, PO_LINE_TYPES LT
, PO_REQUISITION_LINES_ALL RL
, PO_REQUISITION_HEADERS_ALL RH
, PA_TASKS T
, HR_ORGANIZATION_UNITS O
, PA_EXPENDITURE_TYPES ET
, PO_REQ_DISTRIBUTIONS_ALL RD
, PA_PROJECTS P
, WIP_DISCRETE_JOBS WDJ
, WIP_OPERATIONS WO
, BOM_RESOURCES BR
, BOM_DEPARTMENTS BD
, GL_SETS_OF_BOOKS G
WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
AND RH.TYPE_LOOKUP_CODE = 'PURCHASE'
AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND RH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
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 NVL(REQ.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(REQ.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
AND RL.LINE_TYPE_ID = LT.LINE_TYPE_ID
AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
AND P.PROJECT_ID = NVL(RD.PROJECT_ID
, PJM_COMMON_PROJ_PKG.GET_COMMON_PROJECT( RL.DESTINATION_ORGANIZATION_ID))
AND T.TASK_ID = NVL(RD.TASK_ID
, PJM_TASK_AUTO_ASSIGN.WIP_TASK_WNPS ( RL.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 RL.DESTINATION_TYPE_CODE = ('SHOP FLOOR')
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 BR.RESOURCE_ID = RL.BOM_RESOURCE_ID
AND G.SET_OF_BOOKS_ID = RD.SET_OF_BOOKS_ID

Columns