DEMAND_PICK_VW (SQL View)	Index Back
Demand Order Picking View This view returns demand data for the Material Picking Feedback online panel. Whenever this view is modified, DEMAND_PICK_VW3 must also be modified to keep them in synch. 04/23/04 MEG CN#IN890 Data Model Changes - Replaced demand_inv with in_demand and cancel_flag with in_fulfill_state. Removed phone, extension, country_code. 03/08/06 MDS - Added DP_KIT_PARENT_QTY and DP_KIT_PARENT_UOM.

DEMAND_PICK_VW

(SQL View)

Demand Order Picking View

This view returns demand data for the Material Picking Feedback online panel. Whenever this view is modified, DEMAND_PICK_VW3 must also be modified to keep them in synch. 04/23/04 MEG CN#IN890 Data Model Changes - Replaced demand_inv with in_demand and cancel_flag with in_fulfill_state. Removed phone, extension, country_code. 03/08/06 MDS - Added DP_KIT_PARENT_QTY and DP_KIT_PARENT_UOM.

SELECT a.business_unit , a.demand_source , a.source_bus_unit , a.order_no , a.order_int_line_no , a.sched_line_nbr , a.inv_item_id , a.demand_line_no , a.load_id , a.carrier_id , a.ship_type_id , a.parent_prod_id , %DatePart(a.sched_dttm) , %TimePart(a.sched_dttm) , a.sched_arrv_dttm , a.interunit_flg , a.destin_bu , a.pick_batch_id , a.location , a.picklist_line_no , a.cust_id , a.cust_name , a.ship_to_cust_id , a.ship_cust_name1 , a.ship_cust_name2 , a.qty_requested , a.qty_requested_base , a.qty_allocated , a.qty_alloc_base , a.qty_picked , a.qty_pick_ovride , a.qty_pick_ovr_base , a.qty_backorder , a.qty_backorder_base , a.qty_promised_base , a.dp_kit_parent_qty , a.unit_of_measure , a.dp_kit_parent_uom , a.hard_alloc_flag , a.confirmed_flag , a.distrib_type , a.lot_alloc_flg , a.config_code , a.partial_qty_flag , a.picklist_printed , a.transfer_yield , a.route_cd , a.route_stop_nbr , a.pick_dttm , a.orig_inv_item_id , a.orig_uom , a.allow_sub_item_flg , a.export , a.allow_overpick_flg , a.max_pick_tolerance , a.ext_ref_nbr , a.ext_ref_ln_nbr , a.ship_id , a.ship_line_no , a.product_id , a.hold_flag , a.hold_cd , a.pick_release_id , a.in_fulfill_state , a.in_zero_picked_flg , a.peg_status , a.sup_peg_status , a.dt_timestamp , a.last_process_inst , a.delivery_id FROM PS_IN_DEMAND a WHERE a.in_fulfill_state BETWEEN '30' AND '40' AND a.fulfill_flag = 'Y'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_FS.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
Prompt Table: SP_BU_FS_NONVW

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
Prompt Table: BU_ITM_INV_VW2

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

9 LOAD_ID Character(10) VARCHAR2(10) NOT NULL Load ID
Prompt Table: LOAD_INV_OPN_VW

10 CARRIER_ID Character(10) VARCHAR2(10) NOT NULL Carrier ID
Prompt Table: CARRIER_ID

11 SHIP_TYPE_ID Character(10) VARCHAR2(10) NOT NULL Ship Via Code
Prompt Table: SHIP_METHOD

12 PARENT_PROD_ID Character(18) VARCHAR2(18) NOT NULL Parent Product Id

13 SCHED_DATE Date(10) DATE Schedule Date

14 SCHED_TIME Time(15) TIMESTAMP Schedule Time

15 SCHED_ARRV_DTTM DateTime(26) TIMESTAMP Schedule Arrival Dt/Tm

16 INTERUNIT_FLG Character(1) VARCHAR2(1) NOT NULL InterUnit
N=No
Y=Yes
Y/N Table Edit

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

18 PICK_BATCH_ID Number(10,0) DECIMAL(10) NOT NULL Pick Batch ID

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

20 PICKLIST_LINE_NO Number(7,0) INTEGER NOT NULL Pick List Sorting Line Number

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

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

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

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

25 SHIP_CUST_NAME2 Character(40) VARCHAR2(40) NOT NULL Ship To Name 2

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

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

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

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

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

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

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

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

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

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

36 DP_KIT_PARENT_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Demand Planning Kit Parent Qty

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

38 DP_KIT_PARENT_UOM Character(3) VARCHAR2(3) NOT NULL DP Kit Parent Unit of Measure

39 HARD_ALLOC_FLAG Character(1) VARCHAR2(1) NOT NULL Hard Allocate

40 CONFIRMED_FLAG Character(1) VARCHAR2(1) NOT NULL Confirm
Y/N Table Edit
Default Value: N

41 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type

42 LOT_ALLOC_FLG Character(1) VARCHAR2(1) NOT NULL Allow Lot Allocation

43 CONFIG_CODE Character(50) VARCHAR2(50) NOT NULL Product Configurator

44 PARTIAL_QTY_FLAG Character(1) VARCHAR2(1) NOT NULL Partial Qtys Can Ship

45 PICKLIST_PRINTED Character(1) VARCHAR2(1) NOT NULL Picklist Printed

46 TRANSFER_YIELD Number(5,1) DECIMAL(4,1) NOT NULL Transfer Yield

47 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.
Prompt Table: ROUTE_INV_VW

48 ROUTE_STOP_NBR Number(5,0) INTEGER NOT NULL An identifier used to sequence customer addresses/location for a given route.
Prompt Table: ROUTE_STOP_VW2

49 PICK_DTTM DateTime(26) TIMESTAMP Picked Date/Time

50 ORIG_INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Used to identify the original item ordered, in case an item substitution has taken place.

51 ORIG_UOM Character(3) VARCHAR2(3) NOT NULL Original Unit of Measure

52 ALLOW_SUB_ITEM_FLG Character(1) VARCHAR2(1) NOT NULL Used to indicate whether item substitutions are allowed on the order schedule.
Y/N Table Edit

53 EXPORT Character(1) VARCHAR2(1) NOT NULL Export Analytic Instance
N=No
Y=Yes
Y/N Table Edit

54 ALLOW_OVERPICK_FLG Character(1) VARCHAR2(1) NOT NULL Used to indicate whether or not overpicking is allowed for a customer or business unit.

55 MAX_PICK_TOLERANCE Number(6,2) DECIMAL(5,2) NOT NULL Used to indicate the allowable percentage of overshipment for a customer or business unit.

56 EXT_REF_NBR Number(10,0) DECIMAL(10) NOT NULL External Reference Number

57 EXT_REF_LN_NBR Number(5,0) INTEGER NOT NULL External Reference Line Number

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

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

60 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID

61 HOLD_FLAG Character(1) VARCHAR2(1) NOT NULL Hold
N=Hold
Y=Hold

62 HOLD_CD Character(6) VARCHAR2(6) NOT NULL Hold Code

63 PICK_RELEASE_ID Number(10,0) DECIMAL(10) NOT NULL Pick Release ID

64 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

65 IN_ZERO_PICKED_FLG Character(1) VARCHAR2(1) NOT NULL Zero Picked
Y/N Table Edit

66 PEG_STATUS Character(2) VARCHAR2(2) NOT NULL 07/21/04 EGS: Created
10=Open
20=Completed
30=Canceled
40=Not Pegged

67 SUP_PEG_STATUS Character(2) VARCHAR2(2) NOT NULL 07/21/04 EGS: Created
10=Open
20=Completed
30=Canceled
40=Unpegged

68 DT_TIMESTAMP DateTime(26) TIMESTAMP Date Timestamp

69 LAST_PROCESS_INST Number(10,0) DECIMAL(10) NOT NULL Process Instance

70 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 Default Value: OPR_DEF_TBL_FS.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 Prompt Table: SP_BU_FS_NONVW
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 Prompt Table: BU_ITM_INV_VW2
8	DEMAND_LINE_NO	Number(4,0)	SMALLINT NOT NULL	Demand Line No
9	LOAD_ID	Character(10)	VARCHAR2(10) NOT NULL	Load ID Prompt Table: LOAD_INV_OPN_VW
10	CARRIER_ID	Character(10)	VARCHAR2(10) NOT NULL	Carrier ID Prompt Table: CARRIER_ID
11	SHIP_TYPE_ID	Character(10)	VARCHAR2(10) NOT NULL	Ship Via Code Prompt Table: SHIP_METHOD
12	PARENT_PROD_ID	Character(18)	VARCHAR2(18) NOT NULL	Parent Product Id
13	SCHED_DATE	Date(10)	DATE	Schedule Date
14	SCHED_TIME	Time(15)	TIMESTAMP	Schedule Time
15	SCHED_ARRV_DTTM	DateTime(26)	TIMESTAMP	Schedule Arrival Dt/Tm
16	INTERUNIT_FLG	Character(1)	VARCHAR2(1) NOT NULL	InterUnit N=No Y=Yes Y/N Table Edit
17	DESTIN_BU	Character(5)	VARCHAR2(5) NOT NULL	Destination Unit
18	PICK_BATCH_ID	Number(10,0)	DECIMAL(10) NOT NULL	Pick Batch ID
19	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
20	PICKLIST_LINE_NO	Number(7,0)	INTEGER NOT NULL	Pick List Sorting Line Number
21	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.
22	CUST_NAME	Character(40)	VARCHAR2(40) NOT NULL	Name
23	SHIP_TO_CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Ship To Customer
24	SHIP_CUST_NAME1	Character(40)	VARCHAR2(40) NOT NULL	Ship To Name
25	SHIP_CUST_NAME2	Character(40)	VARCHAR2(40) NOT NULL	Ship To Name 2
26	QTY_REQUESTED	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Requested
27	QTY_REQUESTED_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Requested Base
28	QTY_ALLOCATED	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Allocated quantity
29	QTY_ALLOC_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Quantity allocation
30	QTY_PICKED	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Picked
31	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.
32	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.
33	QTY_BACKORDER	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Backorder Qty
34	QTY_BACKORDER_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Backorder Base Qty
35	QTY_PROMISED_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Qty Promised Base
36	DP_KIT_PARENT_QTY	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Demand Planning Kit Parent Qty
37	UNIT_OF_MEASURE	Character(3)	VARCHAR2(3) NOT NULL	Used on an approval rule set. MHR=Muti Hourly PER=Percentage SQF=Square Footage Prompt Table: INV_ORD3_UOM_VW
38	DP_KIT_PARENT_UOM	Character(3)	VARCHAR2(3) NOT NULL	DP Kit Parent Unit of Measure
39	HARD_ALLOC_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Hard Allocate
40	CONFIRMED_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Confirm Y/N Table Edit Default Value: N
41	DISTRIB_TYPE	Character(10)	VARCHAR2(10) NOT NULL	Distrib. Type
42	LOT_ALLOC_FLG	Character(1)	VARCHAR2(1) NOT NULL	Allow Lot Allocation
43	CONFIG_CODE	Character(50)	VARCHAR2(50) NOT NULL	Product Configurator
44	PARTIAL_QTY_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Partial Qtys Can Ship
45	PICKLIST_PRINTED	Character(1)	VARCHAR2(1) NOT NULL	Picklist Printed
46	TRANSFER_YIELD	Number(5,1)	DECIMAL(4,1) NOT NULL	Transfer Yield
47	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. Prompt Table: ROUTE_INV_VW
48	ROUTE_STOP_NBR	Number(5,0)	INTEGER NOT NULL	An identifier used to sequence customer addresses/location for a given route. Prompt Table: ROUTE_STOP_VW2
49	PICK_DTTM	DateTime(26)	TIMESTAMP	Picked Date/Time
50	ORIG_INV_ITEM_ID	Character(18)	VARCHAR2(18) NOT NULL	Used to identify the original item ordered, in case an item substitution has taken place.
51	ORIG_UOM	Character(3)	VARCHAR2(3) NOT NULL	Original Unit of Measure
52	ALLOW_SUB_ITEM_FLG	Character(1)	VARCHAR2(1) NOT NULL	Used to indicate whether item substitutions are allowed on the order schedule. Y/N Table Edit
53	EXPORT	Character(1)	VARCHAR2(1) NOT NULL	Export Analytic Instance N=No Y=Yes Y/N Table Edit
54	ALLOW_OVERPICK_FLG	Character(1)	VARCHAR2(1) NOT NULL	Used to indicate whether or not overpicking is allowed for a customer or business unit.
55	MAX_PICK_TOLERANCE	Number(6,2)	DECIMAL(5,2) NOT NULL	Used to indicate the allowable percentage of overshipment for a customer or business unit.
56	EXT_REF_NBR	Number(10,0)	DECIMAL(10) NOT NULL	External Reference Number
57	EXT_REF_LN_NBR	Number(5,0)	INTEGER NOT NULL	External Reference Line Number
58	SHIP_ID	Character(10)	VARCHAR2(10) NOT NULL	Shipping ID
59	SHIP_LINE_NO	Number(10,2)	DECIMAL(9,2) NOT NULL	Ship Line No
60	PRODUCT_ID	Character(18)	VARCHAR2(18) NOT NULL	Product ID
61	HOLD_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Hold N=Hold Y=Hold
62	HOLD_CD	Character(6)	VARCHAR2(6) NOT NULL	Hold Code
63	PICK_RELEASE_ID	Number(10,0)	DECIMAL(10) NOT NULL	Pick Release ID
64	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
65	IN_ZERO_PICKED_FLG	Character(1)	VARCHAR2(1) NOT NULL	Zero Picked Y/N Table Edit
66	PEG_STATUS	Character(2)	VARCHAR2(2) NOT NULL	07/21/04 EGS: Created 10=Open 20=Completed 30=Canceled 40=Not Pegged
67	SUP_PEG_STATUS	Character(2)	VARCHAR2(2) NOT NULL	07/21/04 EGS: Created 10=Open 20=Completed 30=Canceled 40=Unpegged
68	DT_TIMESTAMP	DateTime(26)	TIMESTAMP	Date Timestamp
69	LAST_PROCESS_INST	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance
70	DELIVERY_ID	Character(10)	VARCHAR2(10) NOT NULL	Delivery ID