BOL_DMD_INV_VW

(SQL View)
 Index Back

BOL Unshipped Demand lines

View is used when construction the Shipping Bill of Lading. Demand lines are in states 20 thru 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) , ' ' , 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 , s.deplete_inv_flg , s.in_fulfill_state , s.delivery_id FROM ps_in_demand s , ps_inv_items i , PS_MASTER_ITEM_TBL m , PS_SET_CNTRL_REC C WHERE s.in_fulfill_state BETWEEN '20' AND '50' 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') 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_PICK_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 SETID Character(5) VARCHAR2(5) NOT NULL SetID
49 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
50 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
51 PRO_NUMBER Character(40) VARCHAR2(40) NOT NULL Pro Number
52 TRANSFER_COST Signed Number(16,4) DECIMAL(14,4) NOT NULL Transfer Price
53 TRANSFER_COST_DEST Signed Number(16,4) DECIMAL(14,4) NOT NULL Transfer Price Dest
54 EXPORT Character(1) VARCHAR2(1) NOT NULL Export Analytic Instance
N=No
Y=Yes
55 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.
56 INTERUNIT_FLG Character(1) VARCHAR2(1) NOT NULL InterUnit
N=No
Y=Yes
57 DEPLETE_INV_FLG Character(1) VARCHAR2(1) NOT NULL Deplete Inventory Flag
58 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
59 DELIVERY_ID Character(10) VARCHAR2(10) NOT NULL Delivery ID