DEMAND_PICK_VW3

(SQL View)
 Index Back

Demand Order Picking View

Used to retrieve order lines on the Material Picking Feedback Panel when the wave code is entered as one of the selection criteria. This view, with the exception of WAVE_CD, must be kept in synch with DEMAND_PICK_VW at all times. 04/27/04 MEG CN#IN890 Data Model Changes - Replaced demand_inv with in_demand, sched_line_no with sched_line_nbr and cancel_flag with in_fulfill_state. Removed phone, extension, country_code.

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_backorder , a.qty_backorder_base , a.unit_of_measure , 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 , b.wave_cd , a.last_process_inst , a.delivery_id FROM PS_IN_DEMAND a , ps_wave_route_inv b WHERE a.in_fulfill_state IN ('30','40') AND a.fulfill_flag = 'Y' AND EXISTS ( SELECT 'X' FROM ps_wave_route_inv c WHERE a.business_unit = b.business_unit AND b.business_unit = c.business_unit AND b.wave_cd = c.wave_cd AND a.route_cd = b.route_cd AND b.route_cd = c.route_cd)

# 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: MST_ITM_PRPT_VW

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_BACKORDER Signed Number(17,4) DECIMAL(15,4) NOT NULL Backorder Qty
33 QTY_BACKORDER_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Backorder Base Qty
34 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
35 HARD_ALLOC_FLAG Character(1) VARCHAR2(1) NOT NULL Hard Allocate
36 CONFIRMED_FLAG Character(1) VARCHAR2(1) NOT NULL Confirm

Y/N Table Edit

Default Value: N

37 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type
38 LOT_ALLOC_FLG Character(1) VARCHAR2(1) NOT NULL Allow Lot Allocation
39 CONFIG_CODE Character(50) VARCHAR2(50) NOT NULL Product Configurator
40 PARTIAL_QTY_FLAG Character(1) VARCHAR2(1) NOT NULL Partial Qtys Can Ship
41 PICKLIST_PRINTED Character(1) VARCHAR2(1) NOT NULL Picklist Printed
42 TRANSFER_YIELD Number(5,1) DECIMAL(4,1) NOT NULL Transfer Yield
43 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.
44 ROUTE_STOP_NBR Number(5,0) INTEGER NOT NULL An identifier used to sequence customer addresses/location for a given route.
45 PICK_DTTM DateTime(26) TIMESTAMP Picked Date/Time
46 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.
47 ORIG_UOM Character(3) VARCHAR2(3) NOT NULL Original Unit of Measure
48 ALLOW_SUB_ITEM_FLG Character(1) VARCHAR2(1) NOT NULL Used to indicate whether item substitutions are allowed on the order schedule.
49 EXPORT Character(1) VARCHAR2(1) NOT NULL Export Analytic Instance
N=No
Y=Yes
50 ALLOW_OVERPICK_FLG Character(1) VARCHAR2(1) NOT NULL Used to indicate whether or not overpicking is allowed for a customer or business unit.
51 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.
52 EXT_REF_NBR Number(10,0) DECIMAL(10) NOT NULL External Reference Number
53 EXT_REF_LN_NBR Number(5,0) INTEGER NOT NULL External Reference Line Number
54 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID
55 SHIP_LINE_NO Number(10,2) DECIMAL(9,2) NOT NULL Ship Line No
56 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
57 HOLD_FLAG Character(1) VARCHAR2(1) NOT NULL Hold
N=Hold
Y=Hold
58 HOLD_CD Character(6) VARCHAR2(6) NOT NULL Hold Code
59 PICK_RELEASE_ID Number(10,0) DECIMAL(10) NOT NULL Pick Release ID
60 WAVE_CD Character(6) VARCHAR2(6) NOT NULL A unique identifier used to group together one or more delivery route codes.
61 LAST_PROCESS_INST Number(10,0) DECIMAL(10) NOT NULL Process Instance
62 DELIVERY_ID Character(10) VARCHAR2(10) NOT NULL Delivery ID