BOL_SHP_INV_VW (SQL View)	Index Back
BOL Demand Lines View View is used when construction the Shipping Bill of Lading. Demand lines are in states 20 thru 70.

BOL_SHP_INV_VW

(SQL View)

BOL Demand Lines View

View is used when construction the Shipping Bill of Lading. Demand lines are in states 20 thru 70.

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_sbr2,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) , ' ' , i.commodity_cd , i.harmonized_cd , i.inv_item_weight , i.inv_item_volume , i.unit_measure_wt , i.unit_measure_vol , m.setid , m.unit_measure_std , s.unit_of_measure , s.pro_number ,s.transfer_cost ,s.transfer_cost_dest ,s.export ,s.route_cd ,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_inv_items i , PS_MASTER_ITEM_TBL m , PS_SET_CNTRL_REC C WHERE s.in_fulfill_state BETWEEN '20' AND '70' AND a.business_unit = s.business_unit AND a.demand_source = s.demand_source AND a.source_bus_unit = s.source_bus_unit AND a.order_no = s.order_no AND a.order_int_line_no = s.order_int_line_no AND a.sched_line_nbr = s.sched_line_nbr AND a.inv_item_id = s.inv_item_id AND a.demand_line_no = s.demand_line_no AND C.RECNAME = 'MASTER_ITEM_TBL' AND S.BUSINESS_UNIT = C.SETCNTRLVALUE AND C.SETID = M.SETID 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')

# 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 SETID Character(5) VARCHAR2(5) NOT NULL SetID

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

67 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

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

69 TRANSFER_COST Signed Number(16,4) DECIMAL(14,4) NOT NULL Transfer Price

70 TRANSFER_COST_DEST Signed Number(16,4) DECIMAL(14,4) NOT NULL Transfer Price Dest

71 EXPORT Character(1) VARCHAR2(1) NOT NULL Export Analytic Instance
N=No
Y=Yes

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

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

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

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

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

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

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

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

80 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

81 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	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
66	UNIT_MEASURE_STD	Character(3)	VARCHAR2(3) NOT NULL	Standard Unit of Measure
67	UNIT_OF_MEASURE	Character(3)	VARCHAR2(3) NOT NULL	Used on an approval rule set. MHR=Muti Hourly PER=Percentage SQF=Square Footage
68	PRO_NUMBER	Character(40)	VARCHAR2(40) NOT NULL	Pro Number
69	TRANSFER_COST	Signed Number(16,4)	DECIMAL(14,4) NOT NULL	Transfer Price
70	TRANSFER_COST_DEST	Signed Number(16,4)	DECIMAL(14,4) NOT NULL	Transfer Price Dest
71	EXPORT	Character(1)	VARCHAR2(1) NOT NULL	Export Analytic Instance N=No Y=Yes
72	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.
73	INTERUNIT_FLG	Character(1)	VARCHAR2(1) NOT NULL	InterUnit N=No Y=Yes
74	PHONE	Character(24)	VARCHAR2(24) NOT NULL	Telephone
75	EXTENSION	Character(6)	VARCHAR2(6) NOT NULL	Phone Extension
76	CUST_CONTACT	Character(40)	VARCHAR2(40) NOT NULL	Customer Contact
77	EMAILID	Character(70)	VARCHAR2(70) NOT NULL	A user's E-mail address
78	COUNTRY_CODE	Character(3)	VARCHAR2(3) NOT NULL	Int'l Prefix
79	DEPLETE_INV_FLG	Character(1)	VARCHAR2(1) NOT NULL	Deplete Inventory Flag
80	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
81	DELIVERY_ID	Character(10)	VARCHAR2(10) NOT NULL	Delivery ID