OM_CP_QTYAVL_VW

(SQL View)
 Index Back

CP Config Item Qty Available


SELECT conv.setid , conv.inv_item_id , lt.config_code , conv.unit_of_measure , lt.business_unit , %Round(%DecMult(SUM(pi.qty_base - pi.qty_reserved_base), (%DecDiv(1,conv.conversion_rate))), 4) FROM ps_set_cntrl_rec sc , ps_physical_inv pi , PS_LOT_CONTROL_INV lt , ps_inv_item_uom conv WHERE sc.setcntrlvalue = lt.business_unit AND sc.recname = 'MASTER_ITEM_TBL' AND conv.setid = sc.setid AND lt.business_unit = pi.business_unit AND lt.inv_item_id = pi.inv_item_id AND lt.inv_item_id = conv.inv_item_id AND lt.inv_lot_id = pi.inv_lot_id AND EXISTS( SELECT 'X' FROM ps_stor_loc_inv lc WHERE %Join(COMMON_KEYS, STOR_LOC_INV LC, PHYSICAL_INV PI) AND lc.nettable_flg = 'Y' AND lc.suspend_flg = 'N' AND lc.phys_cyc_inv_flg = 'N' AND lc.wip_loc_flg = 'N') GROUP BY conv.setid, conv.inv_item_id, lt.config_code, conv.unit_of_measure, lt.business_unit, conv.conversion_rate

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
3 CONFIG_CODE Character(50) VARCHAR2(50) NOT NULL Product Configurator
4 UNIT_MEASURE_ORD Character(3) VARCHAR2(3) NOT NULL Ordering UOM
5 SHIP_FROM_BU Character(5) VARCHAR2(5) NOT NULL Ship from INV BU
6 QTY_AVAILABLE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Available