LD_SHP_INV2_VW

(SQL View)
Index Back

Picked/Shipped Orders View 2


SELECT d.business_unit , d.demand_source , d.source_bus_unit , d.order_no , d.order_int_line_no , d.sched_line_nbr , d.inv_item_id , d.demand_line_no , d.location , d.destin_bu , d.ship_to_cust_id , d.ship_id , d.load_id , d.carrier_id , d.ship_type_id , d.route_cd , m.descr , %DatePart(d.sched_dttm) , %TimePart(d.sched_dttm) , d.unit_of_measure , d.unit_measure_ship , d.qty_requested , d.qty_allocated , d.qty_picked , d.qty_shipped_conv , d.qty_ship_base , d.qty_pack_base , d.ship_dttm , i.inv_item_weight , i.inv_item_volume , i.unit_measure_wt , i.unit_measure_vol , m.unit_measure_std , d.pick_batch_id , d.hold_flag , d.qty_backorder , d.in_fulfill_state , d.single_ship_flag , d.parent_prod_id , '31' , d.optional_ship , d.lot_alloc_flg , d.in_defer_depletion , d.delivery_id FROM ps_in_demand d , PS_MASTER_ITEM_TBL m , ps_inv_items i WHERE (m.setid,d.business_unit) IN ( SELECT setid ,setcntrlvalue FROM ps_set_cntrl_rec sc WHERE recname = 'MASTER_ITEM_TBL') AND m.inv_item_id = d.inv_item_id AND d.in_fulfill_state BETWEEN '20' AND '70' AND (i.setid,d.business_unit) IN ( SELECT setid ,setcntrlvalue FROM ps_set_cntrl_rec sc WHERE recname = 'INV_ITEMS') AND i.inv_item_id = d.inv_item_id AND i.effdt = ( SELECT MAX(effdt) FROM ps_inv_items i2 WHERE i2.inv_item_id = i.inv_item_id AND i2.setid = i.setid AND i2.effdt <= %CurrentDateIn)

  • Related Language Record: LD_SHP_IN2_LVW
  • # 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 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    10 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit
    11 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
    12 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID
    13 LOAD_ID Character(10) VARCHAR2(10) NOT NULL Load ID
    14 CARRIER_ID Character(10) VARCHAR2(10) NOT NULL Carrier ID
    15 SHIP_TYPE_ID Character(10) VARCHAR2(10) NOT NULL Ship Via Code
    16 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.
    17 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    18 SCHED_DATE Date(10) DATE Schedule Date
    19 SCHED_TIME Time(15) TIMESTAMP Schedule Time
    20 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
    MHR=Muti Hourly
    PER=Percentage
    SQF=Square Footage
    21 UNIT_MEASURE_SHIP Character(3) VARCHAR2(3) NOT NULL Shipment Unit of Measure
    22 QTY_REQUESTED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Requested
    23 QTY_ALLOCATED Signed Number(17,4) DECIMAL(15,4) NOT NULL Allocated quantity
    24 QTY_PICKED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Picked
    25 QTY_SHIPPED_CONV Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Shipped
    26 QTY_SHIP_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Ship Base
    27 QTY_PACK_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity Packed
    28 SHIP_DTTM DateTime(26) TIMESTAMP Ship Date/Time
    29 INV_ITEM_WEIGHT Signed Number(17,4) DECIMAL(15,4) NOT NULL Item Weight
    30 INV_ITEM_VOLUME Signed Number(17,4) DECIMAL(15,4) NOT NULL Volume
    31 UNIT_MEASURE_WT Character(3) VARCHAR2(3) NOT NULL Weight UOM
    32 UNIT_MEASURE_VOL Character(3) VARCHAR2(3) NOT NULL Volume UOM
    33 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
    34 PICK_BATCH_ID Number(10,0) DECIMAL(10) NOT NULL Pick Batch ID
    35 HOLD_FLAG Character(1) VARCHAR2(1) NOT NULL Hold
    N=Hold
    Y=Hold
    36 QTY_BACKORDER Signed Number(17,4) DECIMAL(15,4) NOT NULL Backorder Qty
    37 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
    38 SINGLE_SHIP_FLAG Character(1) VARCHAR2(1) NOT NULL Single Ship Flag
    N=No
    Y=Yes
    39 PARENT_PROD_ID Character(18) VARCHAR2(18) NOT NULL Parent Product Id
    40 RESERVE_STATUS Character(2) VARCHAR2(2) NOT NULL Reservation Status
    10=Pending
    11=Unreserved
    12=Shortage, Rules Not Met
    15=Reserved, Rules Not Met
    21=Shortage, Line Rule Met
    22=Reserved, Line Rule Met
    31=Shortage, All Rules Met
    32=Reserved, All Rules Met
    40=Reserve Run
    99=Invalid
    41 OPTIONAL_SHIP Character(1) VARCHAR2(1) NOT NULL OK to Ship Without
    42 LOT_ALLOC_FLG Character(1) VARCHAR2(1) NOT NULL Allow Lot Allocation
    43 IN_DEFER_DEPLETION Character(1) VARCHAR2(1) NOT NULL Defer Depletions
    44 DELIVERY_ID Character(10) VARCHAR2(10) NOT NULL Delivery ID