VIEW: APPS.PJM_REQ_COMMITMENTS_BASIC_V

Source

View Text - Preformatted

SELECT RH.SEGMENT1 , PJM_COMMITMENT_UTILS.REQ_TYPE(RH.ORG_ID,RH.TYPE_LOOKUP_CODE) , 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 , RL.TO_PERSON_ID , PJM_COMMITMENT_UTILS.PEOPLE_NAME(RL.TO_PERSON_ID) , 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 ) , PJM_COMMITMENT_UTILS.GET_UNIT(RL.UNIT_MEAS_LOOKUP_CODE, RL.DESTINATION_TYPE_CODE,RL.ITEM_ID,RL.DESTINATION_ORGANIZATION_ID) , RL.UNIT_PRICE / NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (RL.UNIT_MEAS_LOOKUP_CODE,RL.DESTINATION_TYPE_CODE, RL.ITEM_ID,RL.DESTINATION_ORGANIZATION_ID) , 1 ) , RD.REQ_LINE_QUANTITY * NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (RL.UNIT_MEAS_LOOKUP_CODE,RL.DESTINATION_TYPE_CODE, RL.ITEM_ID,RL.DESTINATION_ORGANIZATION_ID) , 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 , PJM_COMMITMENT_UTILS.ITEM_NUMBER(RL.ITEM_ID, RL.DESTINATION_ORGANIZATION_ID) , RL.BOM_RESOURCE_ID , PJM_COMMITMENT_UTILS.BOM_RESOURCE(RL.BOM_RESOURCE_ID) , PJM_COMMITMENT_UTILS.GET_UOM_CODE(RL.UNIT_MEAS_LOOKUP_CODE, RL.DESTINATION_TYPE_CODE,RL.ITEM_ID,RL.DESTINATION_ORGANIZATION_ID) , PJM_COMMITMENT_UTILS.GET_UOM_TL(RL.UNIT_MEAS_LOOKUP_CODE, RL.DESTINATION_TYPE_CODE,RL.ITEM_ID,RL.DESTINATION_ORGANIZATION_ID) FROM PO_REQUISITION_HEADERS_ALL RH , PO_REQUISITION_LINES_ALL RL , PO_REQ_DISTRIBUTIONS_ALL RD , GL_SETS_OF_BOOKS G , PA_PROJECTS_ALL P , PA_TASKS T , HR_ALL_ORGANIZATION_UNITS_TL O , PA_EXPENDITURE_TYPES ET WHERE RL.DESTINATION_TYPE_CODE in ('INVENTORY', 'SHOP FLOOR') AND RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID AND RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID AND RH.TYPE_LOOKUP_CODE = 'PURCHASE' 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 G.SET_OF_BOOKS_ID = RD.SET_OF_BOOKS_ID AND P.PROJECT_ID = RD.PROJECT_ID AND T.TASK_ID = PJM_COMMITMENT_UTILS.PO_TASK_ID (RL.DESTINATION_ORGANIZATION_ID, P.PROJECT_ID, RL.DESTINATION_TYPE_CODE, RL.ITEM_ID, RL.DESTINATION_SUBINVENTORY, RD.TASK_ID, RL.WIP_ENTITY_ID, RL.WIP_OPERATION_SEQ_NUM ) AND ET.EXPENDITURE_TYPE = PJM_COMMITMENT_UTILS.PO_EXP_TYPE (RL.DESTINATION_ORGANIZATION_ID , RD.PROJECT_ID, RL.ITEM_ID, RL.BOM_RESOURCE_ID, RL.DESTINATION_TYPE_CODE) AND O.ORGANIZATION_ID = PJM_COMMITMENT_UTILS.PO_EXP_ORG (RL.DESTINATION_ORGANIZATION_ID, RL.WIP_ENTITY_ID, RL.WIP_OPERATION_SEQ_NUM, RL.DESTINATION_TYPE_CODE) AND O.LANGUAGE = USERENV('LANG')

View Text - HTML Formatted

SELECT RH.SEGMENT1
, PJM_COMMITMENT_UTILS.REQ_TYPE(RH.ORG_ID
, RH.TYPE_LOOKUP_CODE)
, 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
, RL.TO_PERSON_ID
, PJM_COMMITMENT_UTILS.PEOPLE_NAME(RL.TO_PERSON_ID)
, 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 )
, PJM_COMMITMENT_UTILS.GET_UNIT(RL.UNIT_MEAS_LOOKUP_CODE
, RL.DESTINATION_TYPE_CODE
, RL.ITEM_ID
, RL.DESTINATION_ORGANIZATION_ID)
, RL.UNIT_PRICE / NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (RL.UNIT_MEAS_LOOKUP_CODE
, RL.DESTINATION_TYPE_CODE
, RL.ITEM_ID
, RL.DESTINATION_ORGANIZATION_ID)
, 1 )
, RD.REQ_LINE_QUANTITY * NVL( PJM_COMMITMENT_UTILS.UOM_CONVERSION_RATE (RL.UNIT_MEAS_LOOKUP_CODE
, RL.DESTINATION_TYPE_CODE
, RL.ITEM_ID
, RL.DESTINATION_ORGANIZATION_ID)
, 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
, PJM_COMMITMENT_UTILS.ITEM_NUMBER(RL.ITEM_ID
, RL.DESTINATION_ORGANIZATION_ID)
, RL.BOM_RESOURCE_ID
, PJM_COMMITMENT_UTILS.BOM_RESOURCE(RL.BOM_RESOURCE_ID)
, PJM_COMMITMENT_UTILS.GET_UOM_CODE(RL.UNIT_MEAS_LOOKUP_CODE
, RL.DESTINATION_TYPE_CODE
, RL.ITEM_ID
, RL.DESTINATION_ORGANIZATION_ID)
, PJM_COMMITMENT_UTILS.GET_UOM_TL(RL.UNIT_MEAS_LOOKUP_CODE
, RL.DESTINATION_TYPE_CODE
, RL.ITEM_ID
, RL.DESTINATION_ORGANIZATION_ID)
FROM PO_REQUISITION_HEADERS_ALL RH
, PO_REQUISITION_LINES_ALL RL
, PO_REQ_DISTRIBUTIONS_ALL RD
, GL_SETS_OF_BOOKS G
, PA_PROJECTS_ALL P
, PA_TASKS T
, HR_ALL_ORGANIZATION_UNITS_TL O
, PA_EXPENDITURE_TYPES ET
WHERE RL.DESTINATION_TYPE_CODE IN ('INVENTORY'
, 'SHOP FLOOR')
AND RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
AND RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID
AND RH.TYPE_LOOKUP_CODE = 'PURCHASE'
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 G.SET_OF_BOOKS_ID = RD.SET_OF_BOOKS_ID
AND P.PROJECT_ID = RD.PROJECT_ID
AND T.TASK_ID = PJM_COMMITMENT_UTILS.PO_TASK_ID (RL.DESTINATION_ORGANIZATION_ID
, P.PROJECT_ID
, RL.DESTINATION_TYPE_CODE
, RL.ITEM_ID
, RL.DESTINATION_SUBINVENTORY
, RD.TASK_ID
, RL.WIP_ENTITY_ID
, RL.WIP_OPERATION_SEQ_NUM )
AND ET.EXPENDITURE_TYPE = PJM_COMMITMENT_UTILS.PO_EXP_TYPE (RL.DESTINATION_ORGANIZATION_ID
, RD.PROJECT_ID
, RL.ITEM_ID
, RL.BOM_RESOURCE_ID
, RL.DESTINATION_TYPE_CODE)
AND O.ORGANIZATION_ID = PJM_COMMITMENT_UTILS.PO_EXP_ORG (RL.DESTINATION_ORGANIZATION_ID
, RL.WIP_ENTITY_ID
, RL.WIP_OPERATION_SEQ_NUM
, RL.DESTINATION_TYPE_CODE)
AND O.LANGUAGE = USERENV('LANG')