BOL_ORDPRMT2_VW

(SQL View)
Index Back

BOL View of foreign orders

This view is used in the standalone sql used within the dynamic viewtext for BOL order no prompts (BOL_ORDPRMT2_DVW within sql BOL_ORDERNO2).

SELECT DISTINCT business_unit , demand_source , source_bus_unit , ship_to_cust_id , location , load_id , carrier_id , ship_id , order_no , in_fulfill_state , delivery_id FROM PS_IN_DEMAND WHERE in_fulfill_state BETWEEN '20' AND '70' AND bill_of_lading = ' ' AND ship_id <> ' ' AND ((EXPORT = 'Y' AND DEMAND_SOURCE <> 'OM') OR ORDER_NO IN ( SELECT DISTINCT S.ORDER_NO FROM PS_IN_DEMAND S , PS_ORD_LINE L , PS_ORD_SCHEDULE C WHERE S.DEMAND_SOURCE = 'OM' AND S.IN_FULFILL_STATE BETWEEN '20' AND '70' AND S.SOURCE_BUS_UNIT = L.BUSINESS_UNIT AND S.ORDER_NO = L.ORDER_NO AND S.SOURCE_BUS_UNIT = C.BUSINESS_UNIT AND S.ORDER_NO = C.ORDER_NO AND S.SHIP_TO_CUST_ID <> ' ' AND (L.EXPORT = 'Y' OR C.EXPORT = 'Y')))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 DEMAND_SOURCE Character(2) VARCHAR2(2) NOT NULL Demand Source
IN=Material Request
OM=Sales Order
PL=Planning Requisition
PO=Purchase Order
PR=Purchasing Requisition
RT=Material Return
SF=Production Request
WM=Work Order
3 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
4 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
5 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
6 LOAD_ID Character(10) VARCHAR2(10) NOT NULL Load ID
7 CARRIER_ID Character(10) VARCHAR2(10) NOT NULL Carrier ID
8 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID
9 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
10 IN_FULFILL_STATE Character(2) VARCHAR2(2) NOT NULL Inventory Fulfillment State
10=Pending
20=Unfulfilled
30=Releasable
40=Released
50=Confirmed
60=Shipped
70=Depleted
90=Canceled
11 DELIVERY_ID Character(10) VARCHAR2(10) NOT NULL Delivery ID