Daily Query

-- GI Report
SELECT A.PLPKLNBR, D.DNDNHNBR, F.DNSAPCPO, C.PPPRODTE, A.GNUPDDTE  
GI_DATE, B.INHLDCDE, B.PLCSQNBR, PRSTYCDE, PRCOLCDE, INEXTSIZ,     
E.CODIVCDE, D.SHRPRQTY RL_DU, D.SHACPQTY GI_DU, 
( D.SHRPRQTY / CASE WHEN LLPRPFLG = 2 THEN MMDLUQTY                               
    WHEN LLPRPFLG <> 2 THEN 1 END ) RL_PU,  
(D.SHACPQTY / CASE WHEN LLPRPFLG = 2 THEN MMDLUQTY                               
    WHEN LLPRPFLG <> 2 THEN 1 END    ) GI_PU, B.FLFLTCDE          
FROM SHRPLH A, SHRPCA B, SHRPCI C, SHRPLI D, MMRPRD E , PPRSNH F   
WHERE A.PLPKLNBR = B.PLPKLNBR AND B.INHLDCDE = C.INHLDCDE          
AND   B.INHLDCDE = D.INHLDCDE 
AND TRIM(PRSTYCDE )||'-'||TRIM(PRCOLCDE) = E.MMPRDMAT      
AND   D.DNDNHNBR = F.DNDNHNBR                                      
AND   A.SHGISFLG = 'Y' AND A.GNUPDDTE = 20160127 

-- carton put away:
SELECT GNJOBUSR ,count(distinct INHLDCDE ) as Carton_PUT_AWAY FROM strrsi WHERE     
gnstscde ='060' and ACACTCDE = 'MCARST' and STPRELOC in (SELECT  
lolocsgt FROM lorloc WHERE LOBLDCDE = 'FW01') and GNUPDDTE =     
20140828 GROUP BY GNJOBUSR                                       

-- carton retrieved:
SELECT GNJOBUSR ,count(distinct INHLDCDE ) as Carton_retrivevd FROM 
strrsi WHERE gnstscde ='060' and ACACTCDE = 'MCARRE' and STPRELOC   
in (SELECT lolocsgt FROM lorloc WHERE LOBLDCDE = 'FW01') and        
GNUPDDTE = 20140828 GROUP BY GNJOBUSR                               
FILE DTL_904 IN NKXPRTEMP WAS CREATED.             

-- pallet put away:
SELECT GNJOBUSR ,count(distinct INHLDCDE ) as Pallet_Put_away     
FROM strrsi WHERE gnstscde ='060' and ACACTCDE =                
'MPALST' and STPRELOC in (SELECT lolocsgt FROM lorloc WHERE     
LOBLDCDE = 'FW01') and GNUPDDTE = 20140828                      
group by GNJOBUSR                                               
FILE DTL_905 IN NKXPRTEMP WAS CREATED.       

-- pallet retrieved:
SELECT GNJOBUSR ,count(distinct INHLDCDE ) as Pallet_retrieved FROM
strrsi WHERE gnstscde ='060' and ACACTCDE = 'MPALRE' and STPRELOC  
in (SELECT lolocsgt FROM lorloc WHERE LOBLDCDE = 'FW01') and       
GNUPDDTE = 20140828 and STTRKUID like 'TT%' GROUP BY GNJOBUSR      
FILE DTL_906 IN NKXPRTEMP WAS CREATED.                             

-- pallet ready:
SELECT GNJOBUSR ,count(distinct INHLDCDE ) as Pallet_to_P_D  
FROM strrsi WHERE gnstscde ='060' and ACACTCDE =             
'PALRDY' and STPRELOC in (SELECT lolocsgt FROM lorloc WHERE  
LOBLDCDE = 'FW01') and GNUPDDTE = 20140828                   
group by GNJOBUSR                                            
FILE DTL_907 IN NKXPRTEMP WAS CREATED.                       

-- to belt pick:
SELECT GNJOBUSR ,count(distinct INHLDCDE ) as Pallet_to_belt     
FROM strrsi WHERE gnstscde ='060' and ACACTCDE =                 
'PPICUP' and STPRELOC in (SELECT lolocsgt FROM lorloc WHERE      
LOBLDCDE = 'FW01') and GNUPDDTE = 20140828                       
group by GNJOBUSR                                                
FILE DTL_908 IN NKXPRTEMP WAS CREATED. 

-- AP Productive Report 

SELECT USR , STYLE, COL, SIZE, UOM, QUA, ISGE, SUM(OP_QTY) OP_QTY,  
SUM( BP_QTY ) BP_QTY, SUM(PA_QTY) PA_QTY FROM ( 
/* Batch Picking Unit */               
SELECT BPUSRPRO USR, BPSTYCDE STYLE,                                
BPCOLCDE COL, BPSIZCDE SIZE, BPUOMCDE UOM, BPPQUCDE QUA,            
BPISGCDE ISGE, 0 AS OP_QTY, BPPIDQTY BP_QTY, 0 PA_QTY               
FROM BPRINS  WHERE BPINSSTS = '060' AND GNUPDDTE = 20151223         
UNION ALL   
/* Order picking Unit */                                                  
SELECT LPUSRPRO USR, PRSTYCDE STYLE, PRCOLCDE COL, INEXTSIZ SIZE,   
MMUOMCDE UOM, COPQUCDE QUA, MMISGCDE ISGE, OPAPKQTY AS OP_QTY, 0 AS 
BP_QTY, 0 PA_QTY FROM OPRPHD WHERE OPPHDSTS = '060' AND GNUPDDTE =  
20151223 AND OPCLUSGT IN (SELECT OPCLUSGT FROM OPRCLU WHERE         
PPRUNNBR IN (SELECT PPRUNNBR FROM PPRPRU WHERE PPRUNTYP IN          
('AP_FLS' , 'NORM' , 'RUSH' , 'SAME' , 'SAMEFS' ) ))                
UNION ALL 
       
/* Packing Unit  */                                                 
SELECT PAUSRCDE USR, PRSTYCDE STYLE, PRCOLCDE COL, INEXTSIZ SIZE,   
MMUOMCDE UOM, COPQUCDE QUA, MMISGCDE ISGE, 0 OP_QTY, 0 BP_QTY,      
SHACPQTY PA_QTY FROM PARSHI WHERE GNUPDDTE = 20151223 AND PASHHSGT  
IN (SELECT PASHHSGT FROM PARSHH WHERE PASHHSTS = '060' AND PPRUNNBR 
IN (SELECT PPRUNNBR FROM PPRPRU WHERE                               
PPRUNTYP IN ('AP_FLS' , 'NORM' , 'RUSH' , 'SAME' , 'SAMEFS' )))     
    ) A  WHERE USR IN (SELECT LPUSRPRO FROM LPSUSR )                
GROUP BY USR , STYLE, COL, SIZE, UOM, QUA, ISGE                     
ORDER BY USR , STYLE, COL, SIZE, UOM, QUA, ISGE 

--Cancel Shipping Carton:

-- [email protected]

SELECT A.SHCASDTE "Shipping Date" , A.PLPKLNBR "PackList",        
A.DNSCRCDE "Carrier Code", A.GNCTYCDE "City",b.inhldcde           
"Shipping Carton" , B.PLCSQNBR "Seq Nbr" FROM
shrplh a, shrpca b WHERE A.SHGISFLG = 'Y' and A.PLPKLNBR =        
B.PLPKLNBR and B.GNSTSCDE = '090' and A.SHCASDTE IN (20151225) and      
A.DNSCRCDE in ('YUHA') AND A.SHSTPFLG <> 'Y' ORDER BY B.PLPKLNBR, 
B.PLCSQNBR 
   

-- [email protected]     
                                                    
SELECT A.SHCASDTE "Shipping Date" , A.PLPKLNBR "PackList",        
A.DNSCRCDE "Carrier Code", A.GNCTYCDE "City",b.inhldcde           
"Shipping Carton" , B.PLCSQNBR "Seq Nbr" FROM
shrplh a, shrpca b WHERE A.SHGISFLG = 'Y' and A.PLPKLNBR =        
B.PLPKLNBR and B.GNSTSCDE = '090' and A.SHCASDTE IN (20151225) and      
A.DNSCRCDE in ('HERC') AND A.SHSTPFLG <> 'Y' ORDER BY B.PLPKLNBR, 
B.PLCSQNBR 

-- [email protected]

SELECT A.SHCASDTE "Shipping Date" , A.PLPKLNBR "PackList",        
A.DNSCRCDE "Carrier Code", A.GNCTYCDE "City",b.inhldcde           
"Shipping Carton" , B.PLCSQNBR "Seq Nbr" FROM
shrplh a, shrpca b WHERE A.SHGISFLG = 'Y' and A.PLPKLNBR =        
B.PLPKLNBR and B.GNSTSCDE = '090' and A.SHCASDTE IN (20151225) and      
A.DNSCRCDE in ('RUBO', 'RUBE') AND A.SHSTPFLG <> 'Y' ORDER BY B.PLPKLNBR, 
B.PLCSQNBR