View: MSC_PO_REQUISITIONS_V

Product:	MSC - Advanced Supply Chain Planning
Description:	This view contains all the information necessary to create approved or unapproved requisitions in Oracle Purchasing. This view is used in XML-outbound
Implementation/DBA Data:	APPS.MSC_PO_REQUISITIONS_V

View Text

SELECT MPR.SOURCE_LINE_ID
, MPR.ITEM_ID
, MSI.ITEM_NAME
, MPR.QUANTITY
, MPR.NEED_BY_DATE
, MPR.DELIVER_TO_LOCATION_ID
, MTP_DELIVER_SITES.LOCATION LOCATION_CODE
, MTP_DELIVER.PARTNER_NAME DELIVER_TO_PARTNER_NAME
, MPR.DELIVER_TO_REQUESTOR_ID
, NVL(PLN.PLANNER_CODE
, '-1') REQUESTOR_CODE
, MPR.AUTHORIZATION_STATUS
, DECODE(MPR.AUTHORIZATION_STATUS
, 'APPROVED'
, 1
, 0) AUTHORIZATION_STATUS_CODE
, MPR.UOM_CODE
, MPR.CHARGE_ACCOUNT_ID
, MPR.ITEM_REVISION
, MPR.DESTINATION_ORGANIZATION_ID
, SUBSTR(MTP_DEST.ORGANIZATION_CODE
, INSTR(MTP_DEST.ORGANIZATION_CODE
, ':') + 1) DEST_ORG_CODE
, MTP_DELIVER.PARTNER_NAME DEST_PARTNER_NAME
, MPR.SOURCE_ORGANIZATION_ID
, DECODE(MPR.SOURCE_ORGANIZATION_ID
, NULL
, NULL
, MSC_ST_UTIL.RET_ORG_CODE(MPR.SR_INSTANCE_ID
, MPR.SOURCE_ORGANIZATION_ID)) SOURCE_ORG_CODE
, MPR.SUGGESTED_VENDOR_ID
, MSC_ST_UTIL.RET_PARTNER_NAME(MPR.SR_INSTANCE_ID
, MPR.SUGGESTED_VENDOR_ID
, 1)PARTNER_NAME
, MPR.SUGGESTED_VENDOR_SITE
, NULL VENDOR_SITE_ADDRESS
, MPR.PROJECT_ID
, DECODE(MPR.PROJECT_ID
, NULL
, NULL
, MSC_ST_UTIL.RET_PROJECT_NUMBER(MPR.PROJECT_ID
, MPR.SR_INSTANCE_ID
, MPR.DESTINATION_ORGANIZATION_ID
, -1)) PROJECT_NUMBER
, MPR.TASK_ID
, DECODE(MPR.TASK_ID
, NULL
, NULL
, MSC_ST_UTIL.RET_TASK_NUMBER(MPR.PROJECT_ID
, MPR.TASK_ID
, MPR.SR_INSTANCE_ID
, MPR.DESTINATION_ORGANIZATION_ID
, -1)) TASK_NUMBER
, MPR.SR_INSTANCE_ID
, MAI.INSTANCE_CODE
FROM MSC_PO_REQUISITIONS_INTERFACE MPR
, MSC_APPS_INSTANCES MAI
, MSC_TRADING_PARTNERS MTP_DEST
, MSC_TRADING_PARTNERS MTP_DELIVER
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNER_SITES MTP_DELIVER_SITES
, MSC_PLANNERS PLN
WHERE MTP_DEST.SR_TP_ID = MPR.DESTINATION_ORGANIZATION_ID
AND MTP_DEST.PARTNER_TYPE = 3
AND MTP_DEST.SR_INSTANCE_ID = MPR.SR_INSTANCE_ID
AND MTP_DELIVER_SITES.SR_TP_SITE_ID(+) = MPR.DELIVER_TO_LOCATION_ID
AND MTP_DELIVER_SITES.SR_INSTANCE_ID(+) = MPR.SR_INSTANCE_ID
AND MTP_DELIVER.PARTNER_ID(+) = MTP_DELIVER_SITES.PARTNER_ID
AND MSI.SR_INVENTORY_ITEM_ID = MPR.ITEM_ID
AND MSI.PLAN_ID = -1
AND MSI.ORGANIZATION_ID = MPR.DESTINATION_ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID = MPR.SR_INSTANCE_ID
AND PLN.EMPLOYEE_ID(+)=MPR.DELIVER_TO_REQUESTOR_ID
AND PLN.SR_INSTANCE_ID(+)=MPR.SR_INSTANCE_ID
AND PLN.ORGANIZATION_ID(+)=MPR.DESTINATION_ORGANIZATION_ID
AND MAI.INSTANCE_ID = MPR.SR_INSTANCE_ID

Columns