IN_PTWY_R2Z_VW

(SQL View)
 Index Back

Directed Ptwy Rule 2/zones

This view is used to get the fixed picking locations for directed putaway when doing zone checking.

SELECT DISTINCT A.BUSINESS_UNIT , A.INV_ITEM_ID , A.PICKING_ORDER , A.UNIT_OF_MEASURE , 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 , C.ZONE_CD , Z.PRIORITY_NBR , B.NETTABLE_FLG , B.SUSPEND_FLG , B.AVAIL_STATUS , B.WIP_LOC_FLG , B.PHYS_CYC_INV_FLG FROM PS_FXD_BIN_LOC_INV A , PS_STOR_LOC_INV B , PS_STOR_ZONE_INV C , PS_BU_ITEMS_INV D , PS_PTWY_ZONE_INV Z WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND C.BUSINESS_UNIT = D.BUSINESS_UNIT AND C.BUSINESS_UNIT = Z.BUSINESS_UNIT AND C.ZONE_CD = Z.ZONE_CD AND Z.INV_ITEM_ID = A.INV_ITEM_ID AND A.INV_ITEM_ID = D.INV_ITEM_ID AND A.STORAGE_AREA = B.STORAGE_AREA AND B.STORAGE_AREA = C.STORAGE_AREA AND A.STOR_LEVEL_1 = B.STOR_LEVEL_1 AND B.STOR_LEVEL_1 = C.STOR_LEVEL_1 AND A.STOR_LEVEL_2 = B.STOR_LEVEL_2 AND B.STOR_LEVEL_2 = C.STOR_LEVEL_2 AND A.STOR_LEVEL_3 = B.STOR_LEVEL_3 AND B.STOR_LEVEL_3 = C.STOR_LEVEL_3 AND A.STOR_LEVEL_4 = B.STOR_LEVEL_4 AND B.STOR_LEVEL_4 = C.STOR_LEVEL_4 AND B.DELETE_STORLOC_FLG = 'N' AND C.PTWY_ZONE_FLG = 'Y' 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 D.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 D.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 D.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 D.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 PICKING_ORDER Number(3,0) SMALLINT NOT NULL Picking Order
4 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
5 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
6 STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
7 STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
8 STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
9 STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
10 CUBIC_CHK_FLG Character(1) VARCHAR2(1) NOT NULL 11/10/1999 whs - added
11 CUR_LOC_VOLUME Signed Number(17,4) DECIMAL(15,4) NOT NULL 11/1/1999 whs - added
12 VOL_CAPACITY Signed Number(17,4) DECIMAL(15,4) NOT NULL Maximum Volume Capacity
13 WEIGHT_CHK_FLG Character(1) VARCHAR2(1) NOT NULL 11/10/1999 whs - added
14 CUR_LOC_WEIGHT Signed Number(17,4) DECIMAL(15,4) NOT NULL 11/10/1999 whs - added
15 WT_CAPACITY Signed Number(17,4) DECIMAL(15,4) NOT NULL Maximum Weight Capacity
16 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
17 INSPECT_FLG Character(1) VARCHAR2(1) NOT NULL Inspection Location
18 INV_ITEM_ALIAS Character(18) VARCHAR2(18) NOT NULL Item Alias
19 ITEM_SELECTION Character(1) VARCHAR2(1) NOT NULL Item Selection
M=Multiple Items
S=Single Item
20 ZONE_CD Character(6) VARCHAR2(6) NOT NULL Zone code
21 PRIORITY_NBR Number(3,0) SMALLINT NOT NULL Priority
22 NETTABLE_FLG Character(1) VARCHAR2(1) NOT NULL Nettable
23 SUSPEND_FLG Character(1) VARCHAR2(1) NOT NULL Location Suspend Flag
24 AVAIL_STATUS Character(1) VARCHAR2(1) NOT NULL Availability Status
1=Open
2=Restricted
3=Hold
4=Rejected
25 WIP_LOC_FLG Character(1) VARCHAR2(1) NOT NULL WIP Location Flag
26 PHYS_CYC_INV_FLG Character(1) VARCHAR2(1) NOT NULL In Physical/Cyclic Inventory