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.

VIEW: APPS.MRP_PO_SUPPLY_VIEW

Source

View Text - Preformatted


SELECT PO.ITEM_ID , ITEMS.COMPILE_DESIGNATOR , PO.MRP_TO_ORGANIZATION_ID , PO.TO_ORGANIZATION_ID , PO.PO_HEADER_ID , PO.VENDOR_ID , 1 , PO.MRP_EXPECTED_DELIVERY_DATE , PO.EXPECTED_DELIVERY_DATE , MRP_PRIMARY_QUANTITY , TO_ORG_PRIMARY_QUANTITY , MRP_PRIMARY_QUANTITY * DECODE(SIGN(ITEMS.SHRINKAGE_RATE), -1, 0, (NVL(ITEMS.SHRINKAGE_RATE, 0))) , TO_ORG_PRIMARY_QUANTITY* DECODE(SIGN(ITEMS.SHRINKAGE_RATE), -1, 0, (NVL(ITEMS.SHRINKAGE_RATE, 0))) , QUANTITY , PO.UNIT_PRICE , 1 , PO.LINE_NUM , DECODE(PO.FIRM_PLANNED_STATUS_FLAG,'Y', 1,2) , PO.EXPECTED_DOCK_DATE , PO.PO_NUMBER , PO.ITEM_REVISION , PO.PRINTED_DATE , UOM.UOM_CODE , PO_LINE_ID , PO.MRP_DESTINATION_TYPE_CODE , PO.DESTINATION_TYPE_CODE , PO.PROJECT_ID ,PO.TASK_ID , PO.END_ITEM_UNIT_NUMBER , PO.TO_SUBINVENTORY , PO.VENDOR_SITE_ID , to_number(NULL) FROM MTL_UNITS_OF_MEASURE UOM, MRP_SYSTEM_ITEMS ITEMS, PO_PO_SUPPLY_VIEW PO WHERE UOM.UNIT_OF_MEASURE = PO.PRIMARY_UOM AND ITEMS.INVENTORY_ITEM_ID = PO.ITEM_ID AND ITEMS.ORGANIZATION_ID = PO.ORGANIZATION_ID AND PO.PO_LINE_ID IS NOT NULL AND PO.ITEM_ID IS NOT NULL AND PO.LINE_NUM IS NOT NULL AND NOT EXISTS (SELECT 1 FROM oe_drop_ship_sources odss WHERE PO.po_line_location_id = odss.line_location_id ) UNION ALL SELECT SSV.ITEM_ID , ITEMS.COMPILE_DESIGNATOR , SSV.MRP_TO_ORGANIZATION_ID , SSV.TO_ORGANIZATION_ID , SSV.SHIPMENT_HEADER_ID , TO_NUMBER(NULL) , 11 , SSV.MRP_EXPECTED_DELIVERY_DATE , SSV.EXPECTED_DELIVERY_DATE , MRP_PRIMARY_QUANTITY , TO_ORG_PRIMARY_QUANTITY , MRP_PRIMARY_QUANTITY * DECODE(SIGN(ITEMS.SHRINKAGE_RATE), -1 ,0 ,(NVL(ITEMS.SHRINKAGE_RATE, 0))) , TO_ORG_PRIMARY_QUANTITY * DECODE(SIGN(ITEMS.SHRINKAGE_RATE), -1 , 0 ,(NVL(ITEMS.SHRINKAGE_RATE, 0))) , SSV.SHIPMENT_QUANTITY , TO_NUMBER(NULL) , 1 , SSV.SHIPMENT_LINE_NUM , 2 , SSV.DOCK_DATE , SSV.SHIPMENT_HEADER_NUM , TO_CHAR(NULL) , TO_DATE(NULL) , UOM.UOM_CODE , SSV.SHIPMENT_LINE_ID , SSV.MRP_DESTINATION_TYPE_CODE , SSV.DESTINATION_TYPE_CODE , SSV.PROJECT_ID , SSV.TASK_ID , SSV.END_ITEM_UNIT_NUMBER , SSV.TO_SUBINVENTORY , TO_NUMBER(NULL) , SSV.FROM_ORGANIZATION_ID FROM MTL_UNITS_OF_MEASURE UOM , MRP_SYSTEM_ITEMS ITEMS , PO_SHIP_SUPPLY_VIEW SSV WHERE UOM.UNIT_OF_MEASURE = SSV.PRIMARY_UOM AND ITEMS.INVENTORY_ITEM_ID = SSV.ITEM_ID AND ITEMS.ORGANIZATION_ID = SSV.ORGANIZATION_ID AND SSV.SHIPMENT_LINE_NUM IS NOT NULL AND SSV.SHIPMENT_LINE_ID IS NOT NULL AND SSV.ITEM_ID IS NOT NULL UNION ALL SELECT REQS.ITEM_ID , ITEMS.COMPILE_DESIGNATOR , REQS.MRP_TO_ORGANIZATION_ID , REQS.TO_ORGANIZATION_ID , REQS.REQUISITION_HEADER_ID , VENDOR.VENDOR_ID , 2 , REQS.MRP_EXPECTED_DELIVERY_DATE , REQS.EXPECTED_DELIVERY_DATE , REQS.MRP_PRIMARY_QUANTITY , REQS.TO_ORG_PRIMARY_QUANTITY , NVL(MRP_PRIMARY_QUANTITY,0) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE), -1 , 0 , (NVL(ITEMS.SHRINKAGE_RATE,0))) , NVL(REQS.TO_ORG_PRIMARY_QUANTITY ,0) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE) , -1 , 0 , (NVL(ITEMS.SHRINKAGE_RATE,0))) , REQS.QUANTITY , REQS.UNIT_PRICE , 1 , REQS.LINE_NUM , decode (fnd_profile.value_wnps ('MRP_FIRM_OE_XFERRED_INTERNAL_REQ'), 'Y',decode(reqs.transferred_to_oe_flag,'Y',1,2), 2) , REQS.EXPECTED_DOCK_DATE , REQS.REQUISITION_NUMBER , TO_CHAR(NULL) , TO_DATE(NULL) , UOM.UOM_CODE , REQS.REQ_LINE_ID , REQS.MRP_DESTINATION_TYPE_CODE , REQS.DESTINATION_TYPE_CODE , REQS.PROJECT_ID , REQS.TASK_ID , REQS.END_ITEM_UNIT_NUMBER , REQS.TO_SUBINVENTORY , SITES.VENDOR_SITE_ID , REQS.FROM_ORGANIZATION_ID FROM AP_SUPPLIER_SITES_ALL SITES , PO_VENDORS_VIEW VENDOR , MTL_UNITS_OF_MEASURE UOM , MRP_SYSTEM_ITEMS ITEMS , PO_REQ_SUPPLY_VIEW REQS WHERE VENDOR.VENDOR_NAME(+) = REQS.SUGGESTED_VENDOR_NAME AND (VENDOR.VENDOR_ID = SITES.VENDOR_ID OR VENDOR.VENDOR_ID IS NULL OR /* No Supplier */ SITES.VENDOR_ID IS NULL) /* No Supplier Site */ AND REQS.SUGGESTED_VENDOR_LOCATION = SITES.VENDOR_SITE_CODE (+) AND REQS.ORG_ID = SITES.ORG_ID (+) AND UOM.UNIT_OF_MEASURE = REQS.PRIMARY_UOM AND ITEMS.INVENTORY_ITEM_ID = REQS.ITEM_ID AND ITEMS.ORGANIZATION_ID = REQS.ORGANIZATION_ID AND REQS.LINE_NUM IS NOT NULL AND REQS.REQ_LINE_ID IS NOT NULL AND REQS.ITEM_ID IS NOT NULL AND NOT EXISTS (SELECT 1 FROM oe_drop_ship_sources odss WHERE REQS.req_line_id = odss.requisition_line_id ) UNION ALL SELECT RCV_SHIP.ITEM_ID , ITEMS.COMPILE_DESIGNATOR , RCV_SHIP.MRP_TO_ORGANIZATION_ID , RCV_SHIP.TO_ORGANIZATION_ID , RCV_SHIP.SHIPMENT_HEADER_ID , TO_NUMBER(NULL) , 12 , RCV_SHIP.MRP_EXPECTED_DELIVERY_DATE , RCV_SHIP.EXPECTED_DELIVERY_DATE , RCV_SHIP.MRP_PRIMARY_QUANTITY , RCV_SHIP.TO_ORG_PRIMARY_QUANTITY , TO_NUMBER(NULL) , TO_NUMBER(NULL) , RCV_SHIP.RCV_TRX_QUANTITY , TO_NUMBER(NULL) , 1 , RCV_SHIP.SHIPMENT_LINE_NUM , 1 , RCV_SHIP.DOCK_DATE , RCV_SHIP.SHIPMENT_NUM , TO_CHAR(NULL) , TO_DATE(NULL) , RCV_SHIP.RCV_TRX_UOM_CODE , RCV_SHIP.SHIPMENT_LINE_ID , RCV_SHIP.MRP_DESTINATION_TYPE_CODE , RCV_SHIP.DESTINATION_TYPE_CODE , RCV_SHIP.PROJECT_ID , RCV_SHIP.TASK_ID , RCV_SHIP.END_ITEM_UNIT_NUMBER , RCV_SHIP.TO_SUBINVENTORY , TO_NUMBER(NULL) , RCV_SHIP.FROM_ORGANIZATION_ID FROM MRP_SYSTEM_ITEMS ITEMS , PO_SHIP_RCV_SUPPLY_VIEW RCV_SHIP WHERE ITEMS.INVENTORY_ITEM_ID = RCV_SHIP.ITEM_ID AND ITEMS.ORGANIZATION_ID = RCV_SHIP.ORGANIZATION_ID AND RCV_SHIP.SHIPMENT_LINE_NUM IS NOT NULL AND RCV_SHIP.ITEM_ID IS NOT NULL UNION ALL SELECT PO.ITEM_ID , ITEMS.COMPILE_DESIGNATOR , PO.MRP_TO_ORGANIZATION_ID , PO.TO_ORGANIZATION_ID , PO.PO_HEADER_ID , PO.VENDOR_ID , 8 , PO.MRP_EXPECTED_DELIVERY_DATE , PO.EXPECTED_DELIVERY_DATE , MRP_PRIMARY_QUANTITY , PO.PRIMARY_UOM_QUANTITY , TO_NUMBER(NULL) , TO_NUMBER(NULL) , RCV_TRX_QUANTITY , PO.UNIT_PRICE , 1 , PO.LINE_NUM , 1 , PO.DOCK_DATE , PO.PO_NUMBER , ITEM_REVISION , TO_DATE(NULL) , RCV_TRX_UOM_CODE , PO_LINE_ID , PO.MRP_DESTINATION_TYPE_CODE , PO.DESTINATION_TYPE_CODE , PO.PROJECT_ID , PO.TASK_ID , PO.END_ITEM_UNIT_NUMBER , PO.TO_SUBINVENTORY , PO.VENDOR_SITE_ID , to_number(NULL) FROM MRP_SYSTEM_ITEMS ITEMS , PO_RCV_SUPPLY_VIEW PO WHERE ITEMS.INVENTORY_ITEM_ID = PO.ITEM_ID AND ITEMS.ORGANIZATION_ID = PO.ORGANIZATION_ID AND PO.LINE_NUM IS NOT NULL AND PO.ITEM_ID IS NOT NULL AND NOT EXISTS (SELECT 1 FROM oe_drop_ship_sources odss WHERE PO.po_header_id = odss.po_header_id AND PO.po_line_id = odss.po_line_id )

View Text - HTML Formatted

SELECT PO.ITEM_ID
, ITEMS.COMPILE_DESIGNATOR
, PO.MRP_TO_ORGANIZATION_ID
, PO.TO_ORGANIZATION_ID
, PO.PO_HEADER_ID
, PO.VENDOR_ID
, 1
, PO.MRP_EXPECTED_DELIVERY_DATE
, PO.EXPECTED_DELIVERY_DATE
, MRP_PRIMARY_QUANTITY
, TO_ORG_PRIMARY_QUANTITY
, MRP_PRIMARY_QUANTITY * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, TO_ORG_PRIMARY_QUANTITY* DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, QUANTITY
, PO.UNIT_PRICE
, 1
, PO.LINE_NUM
, DECODE(PO.FIRM_PLANNED_STATUS_FLAG
, 'Y'
, 1
, 2)
, PO.EXPECTED_DOCK_DATE
, PO.PO_NUMBER
, PO.ITEM_REVISION
, PO.PRINTED_DATE
, UOM.UOM_CODE
, PO_LINE_ID
, PO.MRP_DESTINATION_TYPE_CODE
, PO.DESTINATION_TYPE_CODE
, PO.PROJECT_ID
, PO.TASK_ID
, PO.END_ITEM_UNIT_NUMBER
, PO.TO_SUBINVENTORY
, PO.VENDOR_SITE_ID
, TO_NUMBER(NULL)
FROM MTL_UNITS_OF_MEASURE UOM
, MRP_SYSTEM_ITEMS ITEMS
, PO_PO_SUPPLY_VIEW PO
WHERE UOM.UNIT_OF_MEASURE = PO.PRIMARY_UOM
AND ITEMS.INVENTORY_ITEM_ID = PO.ITEM_ID
AND ITEMS.ORGANIZATION_ID = PO.ORGANIZATION_ID
AND PO.PO_LINE_ID IS NOT NULL
AND PO.ITEM_ID IS NOT NULL
AND PO.LINE_NUM IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM OE_DROP_SHIP_SOURCES ODSS
WHERE PO.PO_LINE_LOCATION_ID = ODSS.LINE_LOCATION_ID ) UNION ALL SELECT SSV.ITEM_ID
, ITEMS.COMPILE_DESIGNATOR
, SSV.MRP_TO_ORGANIZATION_ID
, SSV.TO_ORGANIZATION_ID
, SSV.SHIPMENT_HEADER_ID
, TO_NUMBER(NULL)
, 11
, SSV.MRP_EXPECTED_DELIVERY_DATE
, SSV.EXPECTED_DELIVERY_DATE
, MRP_PRIMARY_QUANTITY
, TO_ORG_PRIMARY_QUANTITY
, MRP_PRIMARY_QUANTITY * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, TO_ORG_PRIMARY_QUANTITY * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, SSV.SHIPMENT_QUANTITY
, TO_NUMBER(NULL)
, 1
, SSV.SHIPMENT_LINE_NUM
, 2
, SSV.DOCK_DATE
, SSV.SHIPMENT_HEADER_NUM
, TO_CHAR(NULL)
, TO_DATE(NULL)
, UOM.UOM_CODE
, SSV.SHIPMENT_LINE_ID
, SSV.MRP_DESTINATION_TYPE_CODE
, SSV.DESTINATION_TYPE_CODE
, SSV.PROJECT_ID
, SSV.TASK_ID
, SSV.END_ITEM_UNIT_NUMBER
, SSV.TO_SUBINVENTORY
, TO_NUMBER(NULL)
, SSV.FROM_ORGANIZATION_ID
FROM MTL_UNITS_OF_MEASURE UOM
, MRP_SYSTEM_ITEMS ITEMS
, PO_SHIP_SUPPLY_VIEW SSV
WHERE UOM.UNIT_OF_MEASURE = SSV.PRIMARY_UOM
AND ITEMS.INVENTORY_ITEM_ID = SSV.ITEM_ID
AND ITEMS.ORGANIZATION_ID = SSV.ORGANIZATION_ID
AND SSV.SHIPMENT_LINE_NUM IS NOT NULL
AND SSV.SHIPMENT_LINE_ID IS NOT NULL
AND SSV.ITEM_ID IS NOT NULL UNION ALL SELECT REQS.ITEM_ID
, ITEMS.COMPILE_DESIGNATOR
, REQS.MRP_TO_ORGANIZATION_ID
, REQS.TO_ORGANIZATION_ID
, REQS.REQUISITION_HEADER_ID
, VENDOR.VENDOR_ID
, 2
, REQS.MRP_EXPECTED_DELIVERY_DATE
, REQS.EXPECTED_DELIVERY_DATE
, REQS.MRP_PRIMARY_QUANTITY
, REQS.TO_ORG_PRIMARY_QUANTITY
, NVL(MRP_PRIMARY_QUANTITY
, 0) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, NVL(REQS.TO_ORG_PRIMARY_QUANTITY
, 0) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, REQS.QUANTITY
, REQS.UNIT_PRICE
, 1
, REQS.LINE_NUM
, DECODE (FND_PROFILE.VALUE_WNPS ('MRP_FIRM_OE_XFERRED_INTERNAL_REQ')
, 'Y'
, DECODE(REQS.TRANSFERRED_TO_OE_FLAG
, 'Y'
, 1
, 2)
, 2)
, REQS.EXPECTED_DOCK_DATE
, REQS.REQUISITION_NUMBER
, TO_CHAR(NULL)
, TO_DATE(NULL)
, UOM.UOM_CODE
, REQS.REQ_LINE_ID
, REQS.MRP_DESTINATION_TYPE_CODE
, REQS.DESTINATION_TYPE_CODE
, REQS.PROJECT_ID
, REQS.TASK_ID
, REQS.END_ITEM_UNIT_NUMBER
, REQS.TO_SUBINVENTORY
, SITES.VENDOR_SITE_ID
, REQS.FROM_ORGANIZATION_ID
FROM AP_SUPPLIER_SITES_ALL SITES
, PO_VENDORS_VIEW VENDOR
, MTL_UNITS_OF_MEASURE UOM
, MRP_SYSTEM_ITEMS ITEMS
, PO_REQ_SUPPLY_VIEW REQS
WHERE VENDOR.VENDOR_NAME(+) = REQS.SUGGESTED_VENDOR_NAME
AND (VENDOR.VENDOR_ID = SITES.VENDOR_ID OR VENDOR.VENDOR_ID IS NULL OR /* NO SUPPLIER */ SITES.VENDOR_ID IS NULL) /* NO SUPPLIER SITE */
AND REQS.SUGGESTED_VENDOR_LOCATION = SITES.VENDOR_SITE_CODE (+)
AND REQS.ORG_ID = SITES.ORG_ID (+)
AND UOM.UNIT_OF_MEASURE = REQS.PRIMARY_UOM
AND ITEMS.INVENTORY_ITEM_ID = REQS.ITEM_ID
AND ITEMS.ORGANIZATION_ID = REQS.ORGANIZATION_ID
AND REQS.LINE_NUM IS NOT NULL
AND REQS.REQ_LINE_ID IS NOT NULL
AND REQS.ITEM_ID IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM OE_DROP_SHIP_SOURCES ODSS
WHERE REQS.REQ_LINE_ID = ODSS.REQUISITION_LINE_ID ) UNION ALL SELECT RCV_SHIP.ITEM_ID
, ITEMS.COMPILE_DESIGNATOR
, RCV_SHIP.MRP_TO_ORGANIZATION_ID
, RCV_SHIP.TO_ORGANIZATION_ID
, RCV_SHIP.SHIPMENT_HEADER_ID
, TO_NUMBER(NULL)
, 12
, RCV_SHIP.MRP_EXPECTED_DELIVERY_DATE
, RCV_SHIP.EXPECTED_DELIVERY_DATE
, RCV_SHIP.MRP_PRIMARY_QUANTITY
, RCV_SHIP.TO_ORG_PRIMARY_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, RCV_SHIP.RCV_TRX_QUANTITY
, TO_NUMBER(NULL)
, 1
, RCV_SHIP.SHIPMENT_LINE_NUM
, 1
, RCV_SHIP.DOCK_DATE
, RCV_SHIP.SHIPMENT_NUM
, TO_CHAR(NULL)
, TO_DATE(NULL)
, RCV_SHIP.RCV_TRX_UOM_CODE
, RCV_SHIP.SHIPMENT_LINE_ID
, RCV_SHIP.MRP_DESTINATION_TYPE_CODE
, RCV_SHIP.DESTINATION_TYPE_CODE
, RCV_SHIP.PROJECT_ID
, RCV_SHIP.TASK_ID
, RCV_SHIP.END_ITEM_UNIT_NUMBER
, RCV_SHIP.TO_SUBINVENTORY
, TO_NUMBER(NULL)
, RCV_SHIP.FROM_ORGANIZATION_ID
FROM MRP_SYSTEM_ITEMS ITEMS
, PO_SHIP_RCV_SUPPLY_VIEW RCV_SHIP
WHERE ITEMS.INVENTORY_ITEM_ID = RCV_SHIP.ITEM_ID
AND ITEMS.ORGANIZATION_ID = RCV_SHIP.ORGANIZATION_ID
AND RCV_SHIP.SHIPMENT_LINE_NUM IS NOT NULL
AND RCV_SHIP.ITEM_ID IS NOT NULL UNION ALL SELECT PO.ITEM_ID
, ITEMS.COMPILE_DESIGNATOR
, PO.MRP_TO_ORGANIZATION_ID
, PO.TO_ORGANIZATION_ID
, PO.PO_HEADER_ID
, PO.VENDOR_ID
, 8
, PO.MRP_EXPECTED_DELIVERY_DATE
, PO.EXPECTED_DELIVERY_DATE
, MRP_PRIMARY_QUANTITY
, PO.PRIMARY_UOM_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, RCV_TRX_QUANTITY
, PO.UNIT_PRICE
, 1
, PO.LINE_NUM
, 1
, PO.DOCK_DATE
, PO.PO_NUMBER
, ITEM_REVISION
, TO_DATE(NULL)
, RCV_TRX_UOM_CODE
, PO_LINE_ID
, PO.MRP_DESTINATION_TYPE_CODE
, PO.DESTINATION_TYPE_CODE
, PO.PROJECT_ID
, PO.TASK_ID
, PO.END_ITEM_UNIT_NUMBER
, PO.TO_SUBINVENTORY
, PO.VENDOR_SITE_ID
, TO_NUMBER(NULL)
FROM MRP_SYSTEM_ITEMS ITEMS
, PO_RCV_SUPPLY_VIEW PO
WHERE ITEMS.INVENTORY_ITEM_ID = PO.ITEM_ID
AND ITEMS.ORGANIZATION_ID = PO.ORGANIZATION_ID
AND PO.LINE_NUM IS NOT NULL
AND PO.ITEM_ID IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM OE_DROP_SHIP_SOURCES ODSS
WHERE PO.PO_HEADER_ID = ODSS.PO_HEADER_ID
AND PO.PO_LINE_ID = ODSS.PO_LINE_ID )