BOL_WDMD_OM_VW (SQL View)	Index Back
BOL Unshpd Dom OM Orders Wave This view is used to pick up Domestic OM order lines and schedules. Demand lines states are between 20 and 50.

BOL_WDMD_OM_VW

(SQL View)

BOL Unshpd Dom OM Orders Wave

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

SELECT 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) , l.carrier_id_exp , 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_ord_line l , ps_ord_schedule c , 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.source_bus_unit = l.business_unit AND s.order_no = l.order_no AND s.order_int_line_no = l.order_int_line_no AND s.source_bus_unit = c.business_unit AND s.order_no = c.order_no AND s.order_int_line_no = c.order_int_line_no AND s.sched_line_nbr = c.sched_line_nbr AND s.demand_source = 'OM' AND l.export = 'N' AND c.export = 'N' 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 CARRIER_ID_EXP Character(10) VARCHAR2(10) NOT NULL Export Carrier ID

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

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

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

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

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

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

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

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

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

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

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

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

54 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

55 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	CARRIER_ID_EXP	Character(10)	VARCHAR2(10) NOT NULL	Export Carrier ID
42	COMMODITY_CD	Character(10)	VARCHAR2(10) NOT NULL	Commodity Code
43	HARMONIZED_CD	Character(14)	VARCHAR2(14) NOT NULL	Harmonized Code
44	INV_ITEM_WEIGHT	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Item Weight
45	INV_ITEM_VOLUME	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Volume
46	UNIT_MEASURE_WT	Character(3)	VARCHAR2(3) NOT NULL	Weight UOM
47	UNIT_MEASURE_VOL	Character(3)	VARCHAR2(3) NOT NULL	Volume UOM
48	UNIT_MEASURE_STD	Character(3)	VARCHAR2(3) NOT NULL	Standard Unit of Measure
49	PRO_NUMBER	Character(40)	VARCHAR2(40) NOT NULL	Pro Number
50	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.
51	WAVE_CD	Character(6)	VARCHAR2(6) NOT NULL	A unique identifier used to group together one or more delivery route codes.
52	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
53	DEPLETE_INV_FLG	Character(1)	VARCHAR2(1) NOT NULL	Deplete Inventory Flag
54	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
55	DELIVERY_ID	Character(10)	VARCHAR2(10) NOT NULL	Delivery ID