IN_PTWY_R1_VW

(SQL View)
 Index Back

Directed Ptwy Rule 1

This view is used to get the default putaway locations for directed putaway.

SELECT DISTINCT A.BUSINESS_UNIT , A.INV_ITEM_ID , A.DEF_LOC_TYPE , A.STORAGE_AREA , A.STOR_LEVEL_1 , A.STOR_LEVEL_2 , A.STOR_LEVEL_3 , A.STOR_LEVEL_4 , B.CUBIC_CHK_FLG , B.CUR_LOC_VOLUME , %Round(%DecMult(B.LOC_HEIGHT, %DECMULT(B.LOC_LENGTH, B.LOC_WIDTH)),4) , B.WEIGHT_CHK_FLG , B.CUR_LOC_WEIGHT , B.WT_CAPACITY , B.NON_OWN_FLAG , B.INSPECT_FLG , B.INV_ITEM_ID , B.ITEM_SELECTION , B.NETTABLE_FLG , B.SUSPEND_FLG , B.AVAIL_STATUS , B.WIP_LOC_FLG , B.PHYS_CYC_INV_FLG FROM PS_DEFAULT_LOC_INV A , PS_STOR_LOC_INV B , PS_BU_ITEMS_INV C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INV_ITEM_ID = C.INV_ITEM_ID AND A.STORAGE_AREA = B.STORAGE_AREA AND A.STOR_LEVEL_1 = B.STOR_LEVEL_1 AND A.STOR_LEVEL_2 = B.STOR_LEVEL_2 AND A.STOR_LEVEL_3 = B.STOR_LEVEL_3 AND A.STOR_LEVEL_4 = B.STOR_LEVEL_4 AND B.DELETE_STORLOC_FLG = 'N' AND NOT EXISTS( SELECT 'Y' FROM PS_IN_ISOLATE_VW WHERE BUSINESS_UNIT = A.BUSINESS_UNIT AND INV_ITEM_ID <> A.INV_ITEM_ID AND STORAGE_AREA = A.STORAGE_AREA AND STOR_LEVEL_1 = A.STOR_LEVEL_1 AND STOR_LEVEL_2 = A.STOR_LEVEL_2 AND STOR_LEVEL_3 = A.STOR_LEVEL_3 AND STOR_LEVEL_4 = A.STOR_LEVEL_4) AND ((B.ITEM_SELECTION = 'M' AND C.ISOLATE_ITEM_FLG = 'N') OR (B.ITEM_SELECTION = 'S' AND B.INV_ITEM_ID <> ' ' AND A.INV_ITEM_ID = B.INV_ITEM_ID) OR (((B.ITEM_SELECTION = 'S' AND B.INV_ITEM_ID = ' ') OR C.ISOLATE_ITEM_FLG = 'Y') AND NOT EXISTS ( SELECT 'Y' FROM PS_PHYSICAL_INV WHERE BUSINESS_UNIT = A.BUSINESS_UNIT AND INV_ITEM_ID <> A.INV_ITEM_ID AND STORAGE_AREA = A.STORAGE_AREA AND STOR_LEVEL_1 = A.STOR_LEVEL_1 AND STOR_LEVEL_2 = A.STOR_LEVEL_2 AND STOR_LEVEL_3 = A.STOR_LEVEL_3 AND STOR_LEVEL_4 = A.STOR_LEVEL_4 AND (QTY_RESERVED <> 0 OR QTY <> 0)))) AND NOT EXISTS( SELECT 'Y' FROM PS_IN_ISOLATE_VW2 WHERE BUSINESS_UNIT = A.BUSINESS_UNIT AND INV_ITEM_ID <> A.INV_ITEM_ID AND STORAGE_AREA = A.STORAGE_AREA AND STOR_LEVEL_1 = A.STOR_LEVEL_1 AND STOR_LEVEL_2 = A.STOR_LEVEL_2 AND STOR_LEVEL_3 = A.STOR_LEVEL_3 AND STOR_LEVEL_4 = A.STOR_LEVEL_4) AND ((B.ITEM_SELECTION = 'M' AND C.ISOLATE_ITEM_FLG = 'N') OR (B.ITEM_SELECTION = 'S' AND B.INV_ITEM_ID <> ' ' AND A.INV_ITEM_ID = B.INV_ITEM_ID) OR (((B.ITEM_SELECTION = 'S' AND B.INV_ITEM_ID = ' ') OR C.ISOLATE_ITEM_FLG = 'Y') AND NOT EXISTS ( SELECT 'Y' FROM PS_STAGED_ITEM_INV WHERE BUSINESS_UNIT = A.BUSINESS_UNIT AND INV_ITEM_ID <> A.INV_ITEM_ID AND STORAGE_AREA = A.STORAGE_AREA AND STOR_LEVEL_1 = A.STOR_LEVEL_1 AND STOR_LEVEL_2 = A.STOR_LEVEL_2 AND STOR_LEVEL_3 = A.STOR_LEVEL_3 AND STOR_LEVEL_4 = A.STOR_LEVEL_4 AND PUTAWAY_STATUS = '1')))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
3 DEF_LOC_TYPE Character(1) VARCHAR2(1) NOT NULL Default Location Type
C=Owned Cross
D=Non-Owned Cross
I=Inspection
N=Non-Owned
O=Owned
P=Owned Peg
R=Non-Own Peg
4 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
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 CUBIC_CHK_FLG Character(1) VARCHAR2(1) NOT NULL 11/10/1999 whs - added
10 CUR_LOC_VOLUME Signed Number(17,4) DECIMAL(15,4) NOT NULL 11/1/1999 whs - added
11 VOL_CAPACITY Signed Number(17,4) DECIMAL(15,4) NOT NULL Maximum Volume Capacity
12 WEIGHT_CHK_FLG Character(1) VARCHAR2(1) NOT NULL 11/10/1999 whs - added
13 CUR_LOC_WEIGHT Signed Number(17,4) DECIMAL(15,4) NOT NULL 11/10/1999 whs - added
14 WT_CAPACITY Signed Number(17,4) DECIMAL(15,4) NOT NULL Maximum Weight Capacity
15 NON_OWN_FLAG Character(1) VARCHAR2(1) NOT NULL 07/30/1998 WHS CN#ENH8.0: Created Translate Values 'Y'es and 'N'o
N=No
Y=Yes
16 INSPECT_FLG Character(1) VARCHAR2(1) NOT NULL Inspection Location
17 INV_ITEM_ALIAS Character(18) VARCHAR2(18) NOT NULL Item Alias
18 ITEM_SELECTION Character(1) VARCHAR2(1) NOT NULL Item Selection
M=Multiple Items
S=Single Item
19 NETTABLE_FLG Character(1) VARCHAR2(1) NOT NULL Nettable
20 SUSPEND_FLG Character(1) VARCHAR2(1) NOT NULL Location Suspend Flag
21 AVAIL_STATUS Character(1) VARCHAR2(1) NOT NULL Availability Status
1=Open
2=Restricted
3=Hold
4=Rejected
22 WIP_LOC_FLG Character(1) VARCHAR2(1) NOT NULL WIP Location Flag
23 PHYS_CYC_INV_FLG Character(1) VARCHAR2(1) NOT NULL In Physical/Cyclic Inventory