BOL_WDMD_OM2_VW (SQL View)	Index Back
BOL Unshpd Exp OM Orders - Wv This view is used to pick up Foreign order lines and schedules. Demand line states are between 20 and 50.

BOL_WDMD_OM2_VW

(SQL View)

BOL Unshpd Exp OM Orders - Wv

This view is used to pick up Foreign order lines and schedules. Demand line states are between 20 and 50.

SELECT DISTINCT s.business_unit , s.demand_source , s.source_bus_unit , s.order_no , s.order_int_line_no , s.sched_line_nbr , s.inv_item_id , s.demand_line_no , s.destin_bu , s.cust_id , s.cust_name , s.ship_cust_name1 , s.location , s.ship_to_cust_id , s.address_seq_num , s.addr_ovrd_level , s.ship_id , s.ship_line_no , s.load_id , s.carrier_id , s.ship_type_id , s.unit_measure_ship , s.qty_shipped_conv , s.qty_requested , s.qty_requested_base , s.qty_allocated , s.qty_alloc_base , s.qty_backorder , s.qty_backorder_base , s.qty_promised_base , s.qty_picked , s.qty_pick_base , s.qty_pick_ovride , s.qty_pick_ovr_base , s.last_qty_ship , s.last_qty_ship_base , s.in_zero_picked_flg , s.bill_of_lading , %DatePart(s.ship_dttm) , %DatePart(s.sched_dttm) , i.commodity_cd , i.harmonized_cd , i.inv_item_weight , i.inv_item_volume , i.unit_measure_wt , i.unit_measure_vol , m.unit_measure_std , s.pro_number , s.route_cd , w.wave_cd , m.setid , s.deplete_inv_flg , s.in_fulfill_state , s.delivery_id FROM ps_in_demand s , ps_inv_items i , PS_MASTER_ITEM_TBL m , ps_wave_route_inv w WHERE s.in_fulfill_state BETWEEN '20' AND '50' AND ((s.export = 'Y' AND s.demand_source <> 'OM') OR s.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.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'))) AND i.setid = m.setid AND i.inv_item_id = s.inv_item_id AND i.effdt = ( SELECT MAX(effdt) FROM ps_inv_items i2 WHERE i2.inv_item_id = i.inv_item_id AND i2.setid = i.setid AND i2.effdt <= %CurrentDateIn) AND m.inv_item_id = s.inv_item_id AND m.itm_status_current IN ('1','2','3','4') AND EXISTS( SELECT 'X' FROM ps_wave_route_inv z WHERE s.business_unit = w.business_unit AND w.business_unit = z.business_unit AND w.wave_cd = z.wave_cd AND s.route_cd = w.route_cd AND w.route_cd = z.route_cd) AND NOT EXISTS( SELECT 'X' FROM ps_in_demand_addr a WHERE %Join(COMMON_KEYS, in_demand s, in_demand_addr a))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: SP_BUIN_NONVW

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 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

5 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line

6 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number

7 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

8 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No

9 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit

10 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.

11 CUST_NAME Character(40) VARCHAR2(40) NOT NULL Name

12 SHIP_CUST_NAME1 Character(40) VARCHAR2(40) NOT NULL Ship To Name

13 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

14 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer

15 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number

16 ADDR_OVRD_LEVEL Character(1) VARCHAR2(1) NOT NULL Address Override Level
D=Demand Line
H=Header
L=Line
S=Schedule

17 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID

18 SHIP_LINE_NO Number(10,2) DECIMAL(9,2) NOT NULL Ship Line No

19 LOAD_ID Character(10) VARCHAR2(10) NOT NULL Load ID

20 CARRIER_ID Character(10) VARCHAR2(10) NOT NULL Carrier ID

21 SHIP_TYPE_ID Character(10) VARCHAR2(10) NOT NULL Ship Via Code

22 UNIT_MEASURE_SHIP Character(3) VARCHAR2(3) NOT NULL Shipment Unit of Measure

23 QTY_SHIPPED_CONV Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Shipped

24 QTY_REQUESTED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Requested

25 QTY_REQUESTED_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Requested Base

26 QTY_ALLOCATED Signed Number(17,4) DECIMAL(15,4) NOT NULL Allocated quantity

27 QTY_ALLOC_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity allocation

28 QTY_BACKORDER Signed Number(17,4) DECIMAL(15,4) NOT NULL Backorder Qty

29 QTY_BACKORDER_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Backorder Base Qty

30 QTY_PROMISED_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Promised Base

31 QTY_PICKED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Picked

32 QTY_PICKED_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Pick Base

33 QTY_PICK_OVRIDE Signed Number(17,4) DECIMAL(15,4) NOT NULL Field used to override the net requested quantity when printing the pick play for non-soft reservation items.

34 QTY_PICK_OVR_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Field used to override the net requested quantity in standard uom when printing the pick play for non-soft reservation items.

35 LAST_QTY_SHIP Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity Last Shipped

36 LAST_QTY_SHIP_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Used to save the last value of quantity shipped from the Shipping Issues Panel in Standard UOM at the DEMAND_PHYS_INV and SHIP_INF_INV level. Default to the quantity picked from the Express Issues Panel or for cases where auto ship equals 'Y'.

37 IN_ZERO_PICKED_FLG Character(1) VARCHAR2(1) NOT NULL Zero Picked

38 BILL_OF_LADING Character(30) VARCHAR2(30) NOT NULL Specifies a bill of lading number associated with a Receivables item.

39 SHIP_DATE Date(10) DATE Item Shipping Date

40 SCHED_DATE Date(10) DATE Schedule Date

41 COMMODITY_CD Character(10) VARCHAR2(10) NOT NULL Commodity Code

42 HARMONIZED_CD Character(14) VARCHAR2(14) NOT NULL Harmonized Code

43 INV_ITEM_WEIGHT Signed Number(17,4) DECIMAL(15,4) NOT NULL Item Weight

44 INV_ITEM_VOLUME Signed Number(17,4) DECIMAL(15,4) NOT NULL Volume

45 UNIT_MEASURE_WT Character(3) VARCHAR2(3) NOT NULL Weight UOM

46 UNIT_MEASURE_VOL Character(3) VARCHAR2(3) NOT NULL Volume UOM

47 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure

48 PRO_NUMBER Character(40) VARCHAR2(40) NOT NULL Pro Number

49 ROUTE_CD Character(6) VARCHAR2(6) NOT NULL A unique identifier assigned to a delivery route. Route codes can be associated with customer addresses for external orders or locations for internal orders. Route codes can be used to identify the appropriate delivery schedule for a customer/location if you are managing product shipments with your own vehicles.

50 WAVE_CD Character(6) VARCHAR2(6) NOT NULL A unique identifier used to group together one or more delivery route codes.

51 SETID Character(5) VARCHAR2(5) NOT NULL SetID

52 DEPLETE_INV_FLG Character(1) VARCHAR2(1) NOT NULL Deplete Inventory Flag

53 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

54 DELIVERY_ID Character(10) VARCHAR2(10) NOT NULL Delivery ID

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: SP_BUIN_NONVW
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	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
5	ORDER_INT_LINE_NO	Number(5,0)	INTEGER NOT NULL	Order Line
6	SCHED_LINE_NBR	Number(6,0)	INTEGER NOT NULL	Schedule Line Number
7	INV_ITEM_ID	Character(18)	VARCHAR2(18) NOT NULL	Item ID
8	DEMAND_LINE_NO	Number(4,0)	SMALLINT NOT NULL	Demand Line No
9	DESTIN_BU	Character(5)	VARCHAR2(5) NOT NULL	Destination Unit
10	CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
11	CUST_NAME	Character(40)	VARCHAR2(40) NOT NULL	Name
12	SHIP_CUST_NAME1	Character(40)	VARCHAR2(40) NOT NULL	Ship To Name
13	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
14	SHIP_TO_CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Ship To Customer
15	ADDRESS_SEQ_NUM	Number(5,0)	INTEGER NOT NULL	Address Sequence Number
16	ADDR_OVRD_LEVEL	Character(1)	VARCHAR2(1) NOT NULL	Address Override Level D=Demand Line H=Header L=Line S=Schedule
17	SHIP_ID	Character(10)	VARCHAR2(10) NOT NULL	Shipping ID
18	SHIP_LINE_NO	Number(10,2)	DECIMAL(9,2) NOT NULL	Ship Line No
19	LOAD_ID	Character(10)	VARCHAR2(10) NOT NULL	Load ID
20	CARRIER_ID	Character(10)	VARCHAR2(10) NOT NULL	Carrier ID
21	SHIP_TYPE_ID	Character(10)	VARCHAR2(10) NOT NULL	Ship Via Code
22	UNIT_MEASURE_SHIP	Character(3)	VARCHAR2(3) NOT NULL	Shipment Unit of Measure
23	QTY_SHIPPED_CONV	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Shipped
24	QTY_REQUESTED	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Requested
25	QTY_REQUESTED_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Requested Base
26	QTY_ALLOCATED	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Allocated quantity
27	QTY_ALLOC_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Quantity allocation
28	QTY_BACKORDER	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Backorder Qty
29	QTY_BACKORDER_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Backorder Base Qty
30	QTY_PROMISED_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Promised Base
31	QTY_PICKED	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Picked
32	QTY_PICKED_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Pick Base
33	QTY_PICK_OVRIDE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Field used to override the net requested quantity when printing the pick play for non-soft reservation items.
34	QTY_PICK_OVR_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Field used to override the net requested quantity in standard uom when printing the pick play for non-soft reservation items.
35	LAST_QTY_SHIP	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Quantity Last Shipped
36	LAST_QTY_SHIP_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Used to save the last value of quantity shipped from the Shipping Issues Panel in Standard UOM at the DEMAND_PHYS_INV and SHIP_INF_INV level. Default to the quantity picked from the Express Issues Panel or for cases where auto ship equals 'Y'.
37	IN_ZERO_PICKED_FLG	Character(1)	VARCHAR2(1) NOT NULL	Zero Picked
38	BILL_OF_LADING	Character(30)	VARCHAR2(30) NOT NULL	Specifies a bill of lading number associated with a Receivables item.
39	SHIP_DATE	Date(10)	DATE	Item Shipping Date
40	SCHED_DATE	Date(10)	DATE	Schedule Date
41	COMMODITY_CD	Character(10)	VARCHAR2(10) NOT NULL	Commodity Code
42	HARMONIZED_CD	Character(14)	VARCHAR2(14) NOT NULL	Harmonized Code
43	INV_ITEM_WEIGHT	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Item Weight
44	INV_ITEM_VOLUME	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Volume
45	UNIT_MEASURE_WT	Character(3)	VARCHAR2(3) NOT NULL	Weight UOM
46	UNIT_MEASURE_VOL	Character(3)	VARCHAR2(3) NOT NULL	Volume UOM
47	UNIT_MEASURE_STD	Character(3)	VARCHAR2(3) NOT NULL	Standard Unit of Measure
48	PRO_NUMBER	Character(40)	VARCHAR2(40) NOT NULL	Pro Number
49	ROUTE_CD	Character(6)	VARCHAR2(6) NOT NULL	A unique identifier assigned to a delivery route. Route codes can be associated with customer addresses for external orders or locations for internal orders. Route codes can be used to identify the appropriate delivery schedule for a customer/location if you are managing product shipments with your own vehicles.
50	WAVE_CD	Character(6)	VARCHAR2(6) NOT NULL	A unique identifier used to group together one or more delivery route codes.
51	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
52	DEPLETE_INV_FLG	Character(1)	VARCHAR2(1) NOT NULL	Deplete Inventory Flag
53	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
54	DELIVERY_ID	Character(10)	VARCHAR2(10) NOT NULL	Delivery ID