BOL_SHP_OM_VW (SQL View)	Index Back
BOL Shipped Dom OM Orders This view is used to pick up Domestic order lines and schedules which are shipped or have address overrides. Date Initials Issue Description 052504 dms F-KNANN-V59N4 Replace sched_line_no

BOL_SHP_OM_VW

(SQL View)

BOL Shipped Dom OM Orders

This view is used to pick up Domestic order lines and schedules which are shipped or have address overrides. Date Initials Issue Description 052504 dms F-KNANN-V59N4 Replace sched_line_no

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 , %subrec(address_sbr,a) , 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 , m.setid , s.interunit_flg , a.phone , a.extension , a.cust_contact , a.emailid , a.country_code , s.deplete_inv_flg , s.in_fulfill_state , s.delivery_id FROM ps_in_demand s , ps_in_demand_addr a , ps_ord_line l , ps_ord_schedule c , ps_inv_items i , PS_MASTER_ITEM_TBL m WHERE 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.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 %EffdtCheck(inv_items i2, i, %CurrentDateIn) AND m.inv_item_id = s.inv_item_id AND m.itm_status_current IN ('1','2','3','4') AND %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 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
Prompt Table: COUNTRY_TBL

18 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1

19 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2

20 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3

21 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4

22 CITY Character(30) VARCHAR2(30) NOT NULL City

23 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1

24 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2

25 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer

26 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1

27 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2

28 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3

29 COUNTY Character(30) VARCHAR2(30) NOT NULL County

30 STATE Character(6) VARCHAR2(6) NOT NULL State
Prompt Table: STATE_TBL

31 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal

32 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code

33 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit
Y/N Table Edit

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

49 QTY_PICK_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Pick Base

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

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

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

53 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'.

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

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

56 SHIP_DATE Date(10) DATE Item Shipping Date

57 SCHED_DATE Date(10) DATE Schedule Date

58 CARRIER_ID_EXP Character(10) VARCHAR2(10) NOT NULL Export Carrier ID

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

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

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

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

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

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

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

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

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

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

69 INTERUNIT_FLG Character(1) VARCHAR2(1) NOT NULL InterUnit
N=No
Y=Yes

70 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone

71 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone Extension

72 CUST_CONTACT Character(40) VARCHAR2(40) NOT NULL Customer Contact

73 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address

74 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Int'l Prefix

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

76 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

77 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	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country Prompt Table: COUNTRY_TBL
18	ADDRESS1	Character(55)	VARCHAR2(55) NOT NULL	Address 1
19	ADDRESS2	Character(55)	VARCHAR2(55) NOT NULL	Address 2
20	ADDRESS3	Character(55)	VARCHAR2(55) NOT NULL	Address 3
21	ADDRESS4	Character(55)	VARCHAR2(55) NOT NULL	Address 4
22	CITY	Character(30)	VARCHAR2(30) NOT NULL	City
23	NUM1	Character(6)	VARCHAR2(6) NOT NULL	Number 1
24	NUM2	Character(6)	VARCHAR2(6) NOT NULL	Number 2
25	HOUSE_TYPE	Character(2)	VARCHAR2(2) NOT NULL	House Type AB=House Boat WW=Trailer
26	ADDR_FIELD1	Character(2)	VARCHAR2(2) NOT NULL	Address Field 1
27	ADDR_FIELD2	Character(4)	VARCHAR2(4) NOT NULL	Address Field 2
28	ADDR_FIELD3	Character(4)	VARCHAR2(4) NOT NULL	Address Field 3
29	COUNTY	Character(30)	VARCHAR2(30) NOT NULL	County
30	STATE	Character(6)	VARCHAR2(6) NOT NULL	State Prompt Table: STATE_TBL
31	POSTAL	Character(12)	VARCHAR2(12) NOT NULL	Postal
32	GEO_CODE	Character(11)	VARCHAR2(11) NOT NULL	Geo Code
33	IN_CITY_LIMIT	Character(1)	VARCHAR2(1) NOT NULL	In City Limit Y/N Table Edit
34	SHIP_ID	Character(10)	VARCHAR2(10) NOT NULL	Shipping ID
35	SHIP_LINE_NO	Number(10,2)	DECIMAL(9,2) NOT NULL	Ship Line No
36	LOAD_ID	Character(10)	VARCHAR2(10) NOT NULL	Load ID
37	CARRIER_ID	Character(10)	VARCHAR2(10) NOT NULL	Carrier ID
38	SHIP_TYPE_ID	Character(10)	VARCHAR2(10) NOT NULL	Ship Via Code
39	UNIT_MEASURE_SHIP	Character(3)	VARCHAR2(3) NOT NULL	Shipment Unit of Measure
40	QTY_SHIPPED_CONV	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Shipped
41	QTY_REQUESTED	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Requested
42	QTY_REQUESTED_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Requested Base
43	QTY_ALLOCATED	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Allocated quantity
44	QTY_ALLOC_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Quantity allocation
45	QTY_BACKORDER	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Backorder Qty
46	QTY_BACKORDER_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Backorder Base Qty
47	QTY_PROMISED_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Promised Base
48	QTY_PICKED	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Picked
49	QTY_PICK_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Pick Base
50	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.
51	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.
52	LAST_QTY_SHIP	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Quantity Last Shipped
53	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'.
54	IN_ZERO_PICKED_FLG	Character(1)	VARCHAR2(1) NOT NULL	Zero Picked
55	BILL_OF_LADING	Character(30)	VARCHAR2(30) NOT NULL	Specifies a bill of lading number associated with a Receivables item.
56	SHIP_DATE	Date(10)	DATE	Item Shipping Date
57	SCHED_DATE	Date(10)	DATE	Schedule Date
58	CARRIER_ID_EXP	Character(10)	VARCHAR2(10) NOT NULL	Export Carrier ID
59	COMMODITY_CD	Character(10)	VARCHAR2(10) NOT NULL	Commodity Code
60	HARMONIZED_CD	Character(14)	VARCHAR2(14) NOT NULL	Harmonized Code
61	INV_ITEM_WEIGHT	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Item Weight
62	INV_ITEM_VOLUME	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Volume
63	UNIT_MEASURE_WT	Character(3)	VARCHAR2(3) NOT NULL	Weight UOM
64	UNIT_MEASURE_VOL	Character(3)	VARCHAR2(3) NOT NULL	Volume UOM
65	UNIT_MEASURE_STD	Character(3)	VARCHAR2(3) NOT NULL	Standard Unit of Measure
66	PRO_NUMBER	Character(40)	VARCHAR2(40) NOT NULL	Pro Number
67	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.
68	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
69	INTERUNIT_FLG	Character(1)	VARCHAR2(1) NOT NULL	InterUnit N=No Y=Yes
70	PHONE	Character(24)	VARCHAR2(24) NOT NULL	Telephone
71	EXTENSION	Character(6)	VARCHAR2(6) NOT NULL	Phone Extension
72	CUST_CONTACT	Character(40)	VARCHAR2(40) NOT NULL	Customer Contact
73	EMAILID	Character(70)	VARCHAR2(70) NOT NULL	A user's E-mail address
74	COUNTRY_CODE	Character(3)	VARCHAR2(3) NOT NULL	Int'l Prefix
75	DEPLETE_INV_FLG	Character(1)	VARCHAR2(1) NOT NULL	Deplete Inventory Flag
76	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
77	DELIVERY_ID	Character(10)	VARCHAR2(10) NOT NULL	Delivery ID