你的位置:首页 > 数据库

[数据库]Daily Query


 

-- GI ReportSELECT 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 

GI Report
-- 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 FROMstrrsi 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. 

VNA Report
-- 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 

AP Productive Report
--Cancel Shipping Carton:-- inhouse.nike@yhglobal.comSELECT A.SHCASDTE "Shipping Date" , A.PLPKLNBR "PackList",    A.DNSCRCDE "Carrier Code", A.GNCTYCDE "City",b.inhldcde      "Shipping Carton" , B.PLCSQNBR "Seq Nbr" FROMshrplh 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" FROMshrplh 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.cnSELECT A.SHCASDTE "Shipping Date" , A.PLPKLNBR "PackList",    A.DNSCRCDE "Carrier Code", A.GNCTYCDE "City",b.inhldcde      "Shipping Carton" , B.PLCSQNBR "Seq Nbr" FROMshrplh 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 

Cancel Shipping Carton