Daily Query
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 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.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--Cancel Shipping Carton: -- inhouse.nike@yhglobal.com 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 -- hujian@hercules-logistics.com 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 -- changfei.pan@runbow.com.cn 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