STORLL4_PHY_VW3

(SQL View)
 Index Back

Storage Lev 4 Phys Prompt - Vw

05/2/13: Cloned STOR_L4_PHY_VW3 and then added fields INV_LOT_ID, QTY and UNIT_OF_MEASURE to create this record view for Mobile Inventory Express Issue to retrieve default storage location for lot items when BU allow negative inventory

SELECT DISTINCT business_unit , inv_lot_id , inv_item_id , storage_area , stor_level_1 , stor_level_2 , stor_level_3 , stor_level_4 , qty , unit_of_measure FROM ps_physical_inv WHERE (avail_status = '1' OR avail_status = '2') AND EXISTS ( SELECT 'x' FROM PS_STOR_LOC_INV WHERE ps_physical_inv.business_unit = PS_STOR_LOC_INV.business_unit AND ps_physical_inv.storage_area = PS_STOR_LOC_INV.storage_area AND ps_physical_inv.stor_level_1 = PS_STOR_LOC_INV.stor_level_1 AND ps_physical_inv.stor_level_2 = PS_STOR_LOC_INV.stor_level_2 AND ps_physical_inv.stor_level_3 = PS_STOR_LOC_INV.stor_level_3 AND ps_physical_inv.stor_level_4 = PS_STOR_LOC_INV.stor_level_4)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: STOR_LOC_INV

2 INV_LOT_ID Character(15) VARCHAR2(15) NOT NULL Lot ID
3 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
4 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area

Prompt Table: STOR_LOC_INV

5 STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
6 STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
7 STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
8 STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
9 QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity
10 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage