MSC_AP_PO_REQ_SUPPLY

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
DELIVERY_PRICE FIRM_PLANNED_TYPE INVENTORY_ITEM_ID ITEM_NAME NEW_ORDER_PLACEMENT_DATE POSTPROCESSING_LEAD_TIME NEW_ORDER_QUANTITY NEW_SCHEDULE_DATE ORDER_NUMBER ORDER_TYPE ORGANIZATION_ID ORGANIZATION_CODE PO_LINE_ID PURCH_LINE_NUM TO_ORG_PRIMARY_QUANTITY SHRINKAGE_RATE SUBINVENTORY_CODE SUPPLIER_ID SUPPLIER_NAME SUPPLIER_SITE_ID SUPPLIER_SITE_CODE DESTINATION_TYPE_CODE DROP_SHIP_DEST_TYPE DROP_SHIP_CUST_ID DROP_SHIP_CUST_NAME DROP_SHIP_CUST_SITE_ID DROP_SHIP_CUST_SITE_CODE REQUESTED_SHIP_DATE CARRIER_ID CARRIER_NAME MODE_OF_TRANSPORT SERVICE_LEVEL SCHEDULE_LINE_NUM NEW_DOCK_DATE DISPOSITION_ID NEED_BY_DATE SUP_ROW_ID PRL_ROW_ID PRH_ROW_ID

Name

DELIVERY_PRICE

FIRM_PLANNED_TYPE

INVENTORY_ITEM_ID

ITEM_NAME

NEW_ORDER_PLACEMENT_DATE

POSTPROCESSING_LEAD_TIME

NEW_ORDER_QUANTITY

NEW_SCHEDULE_DATE

ORDER_NUMBER

ORDER_TYPE

ORGANIZATION_ID

ORGANIZATION_CODE

PO_LINE_ID

PURCH_LINE_NUM

TO_ORG_PRIMARY_QUANTITY

SHRINKAGE_RATE

SUBINVENTORY_CODE

SUPPLIER_ID

SUPPLIER_NAME

SUPPLIER_SITE_ID

SUPPLIER_SITE_CODE

DESTINATION_TYPE_CODE

DROP_SHIP_DEST_TYPE

DROP_SHIP_CUST_ID

DROP_SHIP_CUST_NAME

DROP_SHIP_CUST_SITE_ID

DROP_SHIP_CUST_SITE_CODE

REQUESTED_SHIP_DATE

CARRIER_ID

CARRIER_NAME

MODE_OF_TRANSPORT

SERVICE_LEVEL

SCHEDULE_LINE_NUM

NEW_DOCK_DATE

DISPOSITION_ID

NEED_BY_DATE

SUP_ROW_ID

PRL_ROW_ID

PRH_ROW_ID

Query

SQL_Statement
SELECT prl.unit_price DELIVERY_PRICE, DECODE(NVL(PRL.FIRM_FLAG,'N'),'Y',1,2) FIRM_PLANNED_TYPE, sup.item_id INVENTORY_ITEM_ID, esi.ITEM_NUMBER ITEM_NAME, PRH.CREATION_DATE NEW_ORDER_PLACEMENT_DATE , ESI.POSTPROCESSING_LEAD_TIME POSTPROCESSING_LEAD_TIME, NVL(SUP.TO_ORG_PRIMARY_QUANTITY ,0) NEW_ORDER_QUANTITY, NVL(SUP.EXPECTED_DELIVERY_DATE,PRL.need_by_date) NEW_SCHEDULE_DATE, prh.requisition_number ORDER_NUMBER, 2 ORDER_TYPE, sup.to_organization_id ORGANIZATION_ID, esi.ORGANIZATION_CODE, PRL.REQUISITION_LINE_ID PO_LINE_ID, prl.line_number PURCH_LINE_NUM, NVL(SUP.TO_ORG_PRIMARY_QUANTITY ,0) TO_ORG_PRIMARY_QUANTITY, esi.Shrinkage_Rate SHRINKAGE_RATE, PRL.destination_subinventory SUBINVENTORY_CODE, PRL.VENDOR_ID SUPPLIER_ID, supHZP.partner_name SUPPLIER_NAME, PRL.VENDOR_SITE_ID SUPPLIER_SITE_ID, pvsa.VENDOR_SITE_CODE SUPPLIER_SITE_CODE, PRL.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE, decode(upper(NVL(PRL.DESTINATION_TYPE_CODE,'-1')),'DROPSHIP','1','INVENTORY','2','-1') DROP_SHIP_DEST_TYPE, PRL.SHIP_TO_CUST_ID DROP_SHIP_CUST_ID, custHZP.PARTY_NUMBER DROP_SHIP_CUST_NAME, PRL.SHIP_TO_CUST_LOCATION_ID DROP_SHIP_CUST_SITE_ID, custsitesHPS.PARTY_SITE_NUMBER DROP_SHIP_CUST_SITE_CODE, PRL.REQUESTED_SHIP_DATE REQUESTED_SHIP_DATE, PRL.CARRIER_ID, HP.Partner_name CARRIER_NAME, FNLMOD.meaning MODE_OF_TRANSPORT, FNLSRL.meaning SERVICE_LEVEL, TO_CHAR(sup.SUPPLY_SOURCE_ID) SCHEDULE_LINE_NUM, sup.need_by_date NEW_DOCK_DATE, PRH.REQUISITION_HEADER_ID DISPOSITION_ID, PRL.need_by_date NEED_BY_DATE, sup.rowid sup_row_id, PRL.rowid prl_row_id, PRH.rowid prh_row_id FROM MSC_AP_SUPPLIERS_V supHzp, POZ_SUPPLIER_SITES_ALL_M pvsa, MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi, POR_REQUISITION_LINES_ALL PRL, POR_REQUISITION_HEADERS_ALL PRH, INV_SUPPLY SUP, MSC_AP_CARRIERS_V HP, HZ_PARTIES custHZP, HZ_PARTY_SITES custsitesHPS, FND_LOOKUP_VALUES_VL FNLMOD, FND_LOOKUP_VALUES_VL FNLSRL WHERE sup.supply_type_code = 'REQ' AND sup.REQ_LINE_ID IS NOT NULL AND esi.Organization_ID = sup.To_Organization_ID AND esi.Inventory_Item_ID = sup.Item_ID AND nvl(esi.OUTSIDE_PROCESS_SERVICE_FLAG, 2) <> 1 AND prl.requisition_line_id = sup.req_line_id AND prl.SOURCE_TYPE_CODE <> 'INTERNAL' AND prh.requisition_header_id = sup.req_header_id AND prl.quantity > 0 AND supHZP.sr_tp_id(+)=PRL.VENDOR_ID AND pvsa.vendor_site_id(+) = PRL.vendor_site_id AND prl.LINE_NUMBER IS NOT NULL AND NVL(prh.interface_source_code,'x') <> 'ORDER ENTRY' AND DECODE( DECODE( FND_PROFILE.VALUE('MRP_MPS_CONSUMPTION'),'Y',1,1,1,2) , 1, SUP.MRP_DESTINATION_TYPE_CODE , SUP.DESTINATION_TYPE_CODE ) = 'INVENTORY' AND HP.SR_TP_ID(+) = PRL.CARRIER_ID AND CUSTHZP.PARTY_ID(+) = PRL.SHIP_TO_CUST_ID AND CUSTSITESHPS.PARTY_SITE_ID(+) = PRL.SHIP_TO_CUST_LOCATION_ID AND FNLMOD.LOOKUP_CODE(+) = PRL.MODE_OF_TRANSPORT AND FNLMOD.LOOKUP_TYPE(+) = 'WSH_MODE_OF_TRANSPORT' AND FNLMOD.VIEW_APPLICATION_ID(+) = 0 AND FNLMOD.SET_ID(+) = 0 AND FNLSRL.LOOKUP_CODE(+) = PRL.SERVICE_LEVEL AND FNLSRL.LOOKUP_TYPE(+) = 'WSH_SERVICE_LEVELS' AND FNLSRL.VIEW_APPLICATION_ID(+) = 0 AND FNLSRL.SET_ID(+) = 0

SQL_Statement

SELECT prl.unit_price DELIVERY_PRICE,

DECODE(NVL(PRL.FIRM_FLAG,'N'),'Y',1,2) FIRM_PLANNED_TYPE,

sup.item_id INVENTORY_ITEM_ID,

esi.ITEM_NUMBER ITEM_NAME,

PRH.CREATION_DATE NEW_ORDER_PLACEMENT_DATE ,

ESI.POSTPROCESSING_LEAD_TIME POSTPROCESSING_LEAD_TIME,

NVL(SUP.TO_ORG_PRIMARY_QUANTITY ,0) NEW_ORDER_QUANTITY,

NVL(SUP.EXPECTED_DELIVERY_DATE,PRL.need_by_date) NEW_SCHEDULE_DATE,

prh.requisition_number ORDER_NUMBER,

2 ORDER_TYPE,

sup.to_organization_id ORGANIZATION_ID,

esi.ORGANIZATION_CODE,

PRL.REQUISITION_LINE_ID PO_LINE_ID,

prl.line_number PURCH_LINE_NUM,

NVL(SUP.TO_ORG_PRIMARY_QUANTITY ,0) TO_ORG_PRIMARY_QUANTITY,

esi.Shrinkage_Rate SHRINKAGE_RATE,

PRL.destination_subinventory SUBINVENTORY_CODE,

PRL.VENDOR_ID SUPPLIER_ID,

supHZP.partner_name SUPPLIER_NAME,

PRL.VENDOR_SITE_ID SUPPLIER_SITE_ID,

pvsa.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,

PRL.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE,

decode(upper(NVL(PRL.DESTINATION_TYPE_CODE,'-1')),'DROPSHIP','1','INVENTORY','2','-1') DROP_SHIP_DEST_TYPE,

PRL.SHIP_TO_CUST_ID DROP_SHIP_CUST_ID,

custHZP.PARTY_NUMBER DROP_SHIP_CUST_NAME,

PRL.SHIP_TO_CUST_LOCATION_ID DROP_SHIP_CUST_SITE_ID,

custsitesHPS.PARTY_SITE_NUMBER DROP_SHIP_CUST_SITE_CODE,

PRL.REQUESTED_SHIP_DATE REQUESTED_SHIP_DATE,

PRL.CARRIER_ID,

HP.Partner_name CARRIER_NAME,

FNLMOD.meaning MODE_OF_TRANSPORT,

FNLSRL.meaning SERVICE_LEVEL,

TO_CHAR(sup.SUPPLY_SOURCE_ID) SCHEDULE_LINE_NUM,

sup.need_by_date NEW_DOCK_DATE,

PRH.REQUISITION_HEADER_ID DISPOSITION_ID,

PRL.need_by_date NEED_BY_DATE,

sup.rowid sup_row_id,

PRL.rowid prl_row_id,

PRH.rowid prh_row_id

FROM

MSC_AP_SUPPLIERS_V supHzp,

POZ_SUPPLIER_SITES_ALL_M pvsa,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi,

POR_REQUISITION_LINES_ALL PRL,

POR_REQUISITION_HEADERS_ALL PRH,

INV_SUPPLY SUP,

MSC_AP_CARRIERS_V HP,

HZ_PARTIES custHZP,

HZ_PARTY_SITES custsitesHPS,

FND_LOOKUP_VALUES_VL FNLMOD,

FND_LOOKUP_VALUES_VL FNLSRL

WHERE sup.supply_type_code = 'REQ'

AND sup.REQ_LINE_ID IS NOT NULL

AND esi.Organization_ID = sup.To_Organization_ID

AND esi.Inventory_Item_ID = sup.Item_ID

AND nvl(esi.OUTSIDE_PROCESS_SERVICE_FLAG, 2) <> 1

AND prl.requisition_line_id = sup.req_line_id

AND prl.SOURCE_TYPE_CODE <> 'INTERNAL'

AND prh.requisition_header_id = sup.req_header_id

AND prl.quantity > 0

AND supHZP.sr_tp_id(+)=PRL.VENDOR_ID

AND pvsa.vendor_site_id(+) = PRL.vendor_site_id

AND prl.LINE_NUMBER IS NOT NULL

AND NVL(prh.interface_source_code,'x') <> 'ORDER ENTRY'

AND DECODE( DECODE( FND_PROFILE.VALUE('MRP_MPS_CONSUMPTION'),'Y',1,1,1,2) , 1, SUP.MRP_DESTINATION_TYPE_CODE , SUP.DESTINATION_TYPE_CODE ) = 'INVENTORY'

AND HP.SR_TP_ID(+) = PRL.CARRIER_ID

AND CUSTHZP.PARTY_ID(+) = PRL.SHIP_TO_CUST_ID

AND CUSTSITESHPS.PARTY_SITE_ID(+) = PRL.SHIP_TO_CUST_LOCATION_ID

AND FNLMOD.LOOKUP_CODE(+) = PRL.MODE_OF_TRANSPORT

AND FNLMOD.LOOKUP_TYPE(+) = 'WSH_MODE_OF_TRANSPORT'

AND FNLMOD.VIEW_APPLICATION_ID(+) = 0

AND FNLMOD.SET_ID(+) = 0

AND FNLSRL.LOOKUP_CODE(+) = PRL.SERVICE_LEVEL

AND FNLSRL.LOOKUP_TYPE(+) = 'WSH_SERVICE_LEVELS'

AND FNLSRL.VIEW_APPLICATION_ID(+) = 0

AND FNLSRL.SET_ID(+) = 0