SELECT    A.AGVOUTSTATION,A.AGVOUTTIME,F_GET_GROUPNAME(GA.GROUPID) AS GROUPNM,B.BINDTIME,B.SOWINGWALLNUM,B.ELECTRONICLABELID,A.SORTERNO2 AS SORTERNO,A.PALLETNO,A.GROUPNO,to_number(NVL(A.AGVPALLETFLAG,'0')) AS AGVPALLETFLAG,to_number(NVL(A.SPLITFLAG,'0')) AS SPLITFLAG,A.LENGTH,A.WIDTH,A.HEIGHT,A.WEIGHT,DO.SORTERID,to_char(substr(regexp_replace(XMLAGG(XMLPARSE(CONTENT ','||AA.LOADINGNOTICEID WELLFORMED) ORDER BY AA.LOADINGNOTICEID).GETCLOBVAL(),'([^,]+)(,\1)+','\1'),2)) AS LOADINGNOTICEID,B.STATUS, A.PICKINGTASKID,A.STOCKID, B.DETAILID, B.GOODSID,B.OWNERID,SS.PRODUCTDATE,SS.EXPIREDATE,A.ISJOIN,
        S.NAME GOODSNAME, S.MODEL, S.TRADEMARK,S.BIGUNIT,S.STANDUNIT,S.GOODSTOPCATEGORYID,B.GOODSBATCH, B.QUALITYFLG,
        D.OUTSTOCKTYPE,S.SCXKZHBAH,S.YLQXZCRBAR,
        O.COMPANYID,NVL(G.SNAME,'-') COMPANYNAME,A.RELATPICKINGTASKID, 
        B.INDIPICKBIGUNITNUM,B.INDIMIDUNITNUM, B.INDISTANDUNITNUM,B.PICKINGQTY,
        B.PICKAREAID, B.PICKPOSID,B.PREPICKPOSID,B.BUFFERPOSID,A.STAGINGZONEID,A.STAGINGLOCATIONID,
        B.OUTSTOCKINDICATIONID,D.ORDERDATE,M.TRANSFERTYPE,A.RELEASEGROUPID,D.CUSTID,F.SNAME CUSTNAME, F.CUSTTYPE,
        to_number(NVL(D.ISURGENCY,0)) ISURGENCY, to_number(NVL(D.MFLAG,0)) MFLAG,A.PICKWAVEID,B.PICKINGMAN,
        A.CREATTIME,B.PICKINGSTARTTIME,B.PICKINGENDTIME,
        B.CHECKMAN       AS CHECKMAN,
        B.CHECKENDTIME   AS CHECKENDTIME,
        B.MACHECKMAN     AS MACHECKMAN,
        B.MACHECKENDTIME AS MACHECKENDTIME,
        A.PACKMAN,A.PACKTIME,to_number(NVL(B.CHECKEXCEPTION,0)) CHECKEXCEPTION,B.EXCEPTIONREASONID,B.UPDATEDATE, 
        to_number(NVL(B.REPTASKFLG,0)) REPTASKFLG,A.OUTCONTAINERTYPE RELATIONTYPE,A.OUTCONTAINERTYPE,A.MULTCUSTBOXNO CONTAINERNO,W.LOADINGDOCID,
        A.STAGINGMAN,A.STAGINGTIME,a.LOADEDMAN LOADINGMAN,a.LOADEDTIME LOADINGTIME,NVL(A.PREPICKFLG,0)ISPREPICK, 
        W.SHIPPINGCONFIRMUSER,W.SHIPPINGCONFIRMTIME,A.RELAMAN,A.RELATIME,A.RELACONTAINERNO,
        O.CREATEDATE ORDERCREATEDATE,B.PRESTATUS,A.OUTLABELNO,B.BUSINESSTYPE,to_number(NVL(A.PREPICKFLG,0)) PREPICKFLG, 
        B.PREPICKMAN,B.PREPICKTIME,B.ACTUALPICKINGBIGUNITNUM,B.ACTUALPICKINGMIDUNITNUM,B.ACTUALPICKINGSTANDUNITNUM,B.ACTUALPICKINGNUM,B.BOXDETAILID, 
        GA.GOODSAREATYPE,M.DELIVERADDRESS,PREPICKNUM,GA.DPSFLG,
         to_number(nvl(A.BUFFERPOSFLG,0)) BUFFERPOSFLG,S.FORM,
        A.ROLLCONTAINERNO,A.ROLLCONTAINERMAN,A.ROLLCONTAINERTIME,a.COLDPACKFLG, 
        B.OUTSTOCKINFORMID,B.OUTSTOCKINDICATIONDTID,B.MID1,B.MID2,B.MID3,B.MID4,B.MID5,   
 CASE WHEN S.TEMPTYPEID='SYS_TEMPTYPESYS_TEMPTYPE_COOL' or S.TEMPTYPEID='SYS_TEMPTYPESYS_TEMPTYPE_FROZEN' THEN 1 ELSE 0 END ISCOLD, 
 round(( TO_DATE (SUBSTR('',1,16), 
                               'YYYY-MM-DD HH24:MI'  
                              )  -TO_DATE (SUBSTR (O.CREATEDATE, 1, 16),  
                               'YYYY-MM-DD HH24:MI'  
                              )  

                   ) *24,1) TIMECOUNT,
         to_number(nvl(A.FILLFLAG,0)) FILLFLAG,
        (B.PICKINGQTY*SS.STANDNETWEIGHT) AS TOTALWEIGHT,SS.STANDNETWEIGHT,S.STANDNETWEIGHT AS GOODSSTANDNETWEIGHT,
        to_number(NVL(B.CHECKFLG,0)) CHECKFLG,A.SECONDOUTLABELNO 
           ,A.PRIORITY AS PICKPRIORITY
           ,A.IP
          ,S.MAH,S.FACTORYNAME,S.FACTORYCODE,S.FACTORYTYPE,S.FACTORYLICENSE,S.ISMINSO   
          ,MAX(WCSA.PRIORITY) AS TASKPRIORITY
          ,MAX(WCSB.PRIORITY) AS PRIORITY
          ,CASE WHEN BB.MATERIALID IS NULL THEN  0  ELSE  1 END COLDPACK
   FROM OUTSTOCKPICKTBL A,
        OUTSTOCKPICKDTTBL B,
        OUTSTOCKINDICATIONTBL D,
        CUSTOMERMST F,
        CompanyMst G,
        GOODSMST S,
        GOODSBATCHMST SS,
        DELIVERTBL M,
        LOADINGDOCTBL W,
        OUTSTOCKINFORMTBL O,
        DELIVERSORTERTBL DO,
        GOODSAREAMST GA
       ,LOADINGNOTICEDETAILTBL AA,
   (SELECT MAX(A.MATERIALID) AS MATERIALID,A.OUTLABEL FROM MATERIALCONNECT A GROUP BY A.OUTLABEL) BB,
        WCS_STOCKOUT_TASK    WCSA,
        WCS_TASK_PRIORITY    WCSB

        WHERE 1=1 


     AND B.OUTLABELNO = 'OL8023082908788'
     AND B.OUTSTOCKINDICATIONID = AA.OUTSTOCKINDICATIONID(+)
     AND DO.DELIVERAREAID(+)=M.DELIVERAREAID
    AND A.PICKINGTASKID = B.PICKINGTASKID
    AND S.GOODSID = B.GOODSID
    AND SS.GOODSID(+) = B.GOODSID
    AND B.GOODSBATCH=SS.GOODSBATCH(+)
    AND B.OUTSTOCKINDICATIONID = D.OUTSTOCKINDICATIONID
    AND D.OUTSTOCKINFORMID = O.OUTSTOCKINFORMID
    AND D.OUTSTOCKINFORMID = M.OUTSTOCKINFORMID
    AND F.CUSTID = D.CUSTID
    AND G.COMPANYID(+) = O.COMPANYID
    AND a.LOADINGDOCID=W.LOADINGDOCID(+)
    AND B.PICKAREAID=GA.GOODSAREAID(+)
    AND A.PICKINGTASKID = WCSA.TASKID(+)
    AND A.PICKINGTASKID = WCSB.TASKID(+)
    AND A.OUTLABELNO = BB.OUTLABEL(+)
GROUP BY  A.AGVOUTSTATION,A.AGVOUTTIME,GA.GROUPID,B.BINDTIME,B.SOWINGWALLNUM,B.ELECTRONICLABELID,A.SORTERNO2,A.PALLETNO,A.GROUPNO,A.AGVPALLETFLAG,A.SPLITFLAG,A.LENGTH,A.WIDTH,A.HEIGHT,A.WEIGHT,DO.SORTERID,B.STATUS,A.PICKINGTASKID,A.STOCKID,B.DETAILID,B.GOODSID,
            B.OWNERID,SS.PRODUCTDATE,SS.EXPIREDATE,A.ISJOIN,S.NAME,S.MODEL,S.TRADEMARK,S.SCXKZHBAH,S.YLQXZCRBAR,
            S.BIGUNIT,S.STANDUNIT,S.GOODSTOPCATEGORYID,B.GOODSBATCH,B.QUALITYFLG,D.OUTSTOCKTYPE,
            O.COMPANYID,G.SNAME,A.RELATPICKINGTASKID,B.INDIPICKBIGUNITNUM,B.INDIMIDUNITNUM,
            B.INDISTANDUNITNUM,B.PICKINGQTY,B.PICKAREAID,B.PICKPOSID,B.PREPICKPOSID,
            B.BUFFERPOSID,A.STAGINGZONEID,A.STAGINGLOCATIONID,B.OUTSTOCKINDICATIONID,
            D.ORDERDATE,M.TRANSFERTYPE,A.RELEASEGROUPID,D.CUSTID,F.SNAME,F.CUSTTYPE,D.ISURGENCY,
            D.MFLAG,A.PICKWAVEID,B.PICKINGMAN,A.CREATTIME,B.PICKINGSTARTTIME,B.PICKINGENDTIME,
            A.ISJOIN,A.STAGINGMAN,B.CHECKMAN,A.STAGINGTIME,B.CHECKENDTIME,D.MFLAG,B.PICKINGMAN,
            B.MACHECKMAN,B.PICKINGENDTIME,B.MACHECKENDTIME,A.PACKMAN,A.PACKTIME,B.CHECKEXCEPTION,
            B.EXCEPTIONREASONID,B.UPDATEDATE,B.REPTASKFLG,A.OUTCONTAINERTYPE,A.OUTCONTAINERTYPE,
            A.MULTCUSTBOXNO,W.LOADINGDOCID,A.STAGINGMAN,A.STAGINGTIME,A.LOADEDMAN,A.LOADEDTIME,
            A.PREPICKFLG,W.SHIPPINGCONFIRMUSER,W.SHIPPINGCONFIRMTIME,A.RELAMAN,A.RELATIME,
            A.RELACONTAINERNO,O.CREATEDATE,B.PRESTATUS,A.OUTLABELNO,B.BUSINESSTYPE,A.PREPICKFLG,
            B.PREPICKMAN,B.PREPICKTIME,B.ACTUALPICKINGBIGUNITNUM,B.ACTUALPICKINGMIDUNITNUM,
            B.ACTUALPICKINGSTANDUNITNUM,B.ACTUALPICKINGNUM,B.BOXDETAILID,GA.GOODSAREATYPE,
            M.DELIVERADDRESS,PREPICKNUM,GA.DPSFLG,A.BUFFERPOSFLG,S.FORM,A.ROLLCONTAINERNO,
            A.ROLLCONTAINERMAN,A.ROLLCONTAINERTIME,A.COLDPACKFLG,B.OUTSTOCKINFORMID,B.OUTSTOCKINDICATIONDTID,
            B.MID1,B.MID2,B.MID3,B.MID4,B.MID5,S.TEMPTYPEID,O.CREATEDATE,A.FILLFLAG,B.PICKINGQTY,
            SS.STANDNETWEIGHT,S.STANDNETWEIGHT,B.CHECKFLG,A.SECONDOUTLABELNO,A.PRIORITY ,
            A.IP,S.MAH,S.FACTORYNAME,S.FACTORYCODE,S.FACTORYTYPE,S.FACTORYLICENSE,S.ISMINSO,BB.MATERIALID

CQP

 SELECT A.WEIGHT,DO.SORTERID,to_char(substr(regexp_replace(XMLAGG(XMLPARSE(CONTENT ','||AA.LOADINGNOTICEID WELLFORMED) ORDER BY AA.LOADINGNOTICEID).GETCLOBVAL(),'([^,]+)(,\1)+','\1'),2)) AS LOADINGNOTICEID,B.STATUS, A.PICKINGTASKID,A.STOCKID, B.DETAILID, B.GOODSID,B.OWNERID,SS.PRODUCTDATE,SS.EXPIREDATE,A.ISJOIN,
        S.NAME GOODSNAME, S.MODEL, S.TRADEMARK,S.BIGUNIT,S.STANDUNIT,S.GOODSTOPCATEGORYID,B.GOODSBATCH, B.QUALITYFLG,
        D.OUTSTOCKTYPE,S.SCXKZHBAH,S.YLQXZCRBAR,
        O.COMPANYID,NVL(G.SNAME,'-') COMPANYNAME,A.RELATPICKINGTASKID, 
        B.INDIPICKBIGUNITNUM,B.INDIMIDUNITNUM, B.INDISTANDUNITNUM,B.PICKINGQTY,
        B.PICKAREAID, B.PICKPOSID,B.PREPICKPOSID,B.BUFFERPOSID,A.STAGINGZONEID,A.STAGINGLOCATIONID,
        B.OUTSTOCKINDICATIONID,D.ORDERDATE,M.TRANSFERTYPE,A.RELEASEGROUPID,D.CUSTID,F.SNAME CUSTNAME, F.CUSTTYPE,
        to_number(NVL(D.ISURGENCY,0)) ISURGENCY, to_number(NVL(D.MFLAG,0)) MFLAG,A.PICKWAVEID,B.PICKINGMAN,
        A.CREATTIME,B.PICKINGSTARTTIME,B.PICKINGENDTIME,
        B.CHECKMAN       AS CHECKMAN,
        B.CHECKENDTIME   AS CHECKENDTIME,
        B.MACHECKMAN     AS MACHECKMAN,
        B.MACHECKENDTIME AS MACHECKENDTIME,
        A.PACKMAN,A.PACKTIME,to_number(NVL(B.CHECKEXCEPTION,0)) CHECKEXCEPTION,B.EXCEPTIONREASONID,B.UPDATEDATE, 
        to_number(NVL(B.REPTASKFLG,0)) REPTASKFLG,A.OUTCONTAINERTYPE RELATIONTYPE,A.OUTCONTAINERTYPE,A.MULTCUSTBOXNO CONTAINERNO,W.LOADINGDOCID,
        A.STAGINGMAN,A.STAGINGTIME,a.LOADEDMAN LOADINGMAN,a.LOADEDTIME LOADINGTIME,NVL(A.PREPICKFLG,0)ISPREPICK, 
        W.SHIPPINGCONFIRMUSER,W.SHIPPINGCONFIRMTIME,A.RELAMAN,A.RELATIME,A.RELACONTAINERNO,
        O.CREATEDATE ORDERCREATEDATE,B.PRESTATUS,A.OUTLABELNO,B.BUSINESSTYPE,to_number(NVL(A.PREPICKFLG,0)) PREPICKFLG, 
        B.PREPICKMAN,B.PREPICKTIME,B.ACTUALPICKINGBIGUNITNUM,B.ACTUALPICKINGMIDUNITNUM,B.ACTUALPICKINGSTANDUNITNUM,B.ACTUALPICKINGNUM,B.BOXDETAILID, 
        GA.GOODSAREATYPE,M.DELIVERADDRESS,PREPICKNUM,GA.DPSFLG,
         to_number(nvl(A.BUFFERPOSFLG,0)) BUFFERPOSFLG,S.FORM,
        A.ROLLCONTAINERNO,A.ROLLCONTAINERMAN,A.ROLLCONTAINERTIME,a.COLDPACKFLG, 
        B.OUTSTOCKINFORMID,B.OUTSTOCKINDICATIONDTID,B.MID1,B.MID2,B.MID3,B.MID4,B.MID5,   
 CASE WHEN S.TEMPTYPEID='SYS_TEMPTYPESYS_TEMPTYPE_COOL' or S.TEMPTYPEID='SYS_TEMPTYPESYS_TEMPTYPE_FROZEN' THEN 1 ELSE 0 END ISCOLD, 
 round(( TO_DATE (SUBSTR('',1,16), 
                               'YYYY-MM-DD HH24:MI'  
                              )  -TO_DATE (SUBSTR (O.CREATEDATE, 1, 16),  
                               'YYYY-MM-DD HH24:MI'  
                              )  

                   ) *24,1) TIMECOUNT,
         to_number(nvl(A.FILLFLAG,0)) FILLFLAG,
        (B.PICKINGQTY*SS.STANDNETWEIGHT) AS TOTALWEIGHT,SS.STANDNETWEIGHT,S.STANDNETWEIGHT AS GOODSSTANDNETWEIGHT,
        to_number(NVL(B.CHECKFLG,0)) CHECKFLG,A.SECONDOUTLABELNO 
           ,A.PRIORITY AS PICKPRIORITY
           ,A.IP
          ,S.MAH,S.FACTORYNAME,S.FACTORYCODE,S.FACTORYTYPE,S.FACTORYLICENSE,S.ISMINSO   
          ,MAX(WCSA.PRIORITY) AS TASKPRIORITY
          ,MAX(WCSB.PRIORITY) AS PRIORITY
          ,CASE WHEN BB.MATERIALID IS NULL THEN  0  ELSE  1 END COLDPACK
   FROM OUTSTOCKPICKTBL A,
        OUTSTOCKPICKDTTBL B,
        OUTSTOCKINDICATIONTBL D,
        CUSTOMERMST F,
        CompanyMst G,
        GOODSMST S,
        GOODSBATCHMST SS,
        DELIVERTBL M,
        LOADINGDOCTBL W,
        OUTSTOCKINFORMTBL O,
        DELIVERSORTERTBL DO,
        GOODSAREAMST GA
       ,LOADINGNOTICEDETAILTBL AA,
   (SELECT MAX(A.MATERIALID) AS MATERIALID,A.OUTLABEL FROM MATERIALCONNECT A GROUP BY A.OUTLABEL) BB,
        WCS_STOCKOUT_TASK    WCSA,
        WCS_TASK_PRIORITY    WCSB

        WHERE 1=1 


     AND B.OUTLABELNO = 'OLA923112700125'
     AND B.OUTSTOCKINDICATIONID = AA.OUTSTOCKINDICATIONID(+)
     AND DO.DELIVERAREAID(+)=M.DELIVERAREAID
    AND A.PICKINGTASKID = B.PICKINGTASKID
    AND S.GOODSID = B.GOODSID
    AND SS.GOODSID(+) = B.GOODSID
    AND B.GOODSBATCH=SS.GOODSBATCH(+)
    AND B.OUTSTOCKINDICATIONID = D.OUTSTOCKINDICATIONID
    AND D.OUTSTOCKINFORMID = O.OUTSTOCKINFORMID
    AND D.OUTSTOCKINFORMID = M.OUTSTOCKINFORMID
    AND F.CUSTID = D.CUSTID
    AND G.COMPANYID(+) = O.COMPANYID
    AND a.LOADINGDOCID=W.LOADINGDOCID(+)
    AND B.PICKAREAID=GA.GOODSAREAID(+)
    AND A.PICKINGTASKID = WCSA.TASKID(+)
    AND A.PICKINGTASKID = WCSB.TASKID(+)
    AND A.OUTLABELNO = BB.OUTLABEL(+)
GROUP BY  A.WEIGHT,DO.SORTERID,B.STATUS,A.PICKINGTASKID,A.STOCKID,B.DETAILID,B.GOODSID,
            B.OWNERID,SS.PRODUCTDATE,SS.EXPIREDATE,A.ISJOIN,S.NAME,S.MODEL,S.TRADEMARK,S.SCXKZHBAH,S.YLQXZCRBAR,
            S.BIGUNIT,S.STANDUNIT,S.GOODSTOPCATEGORYID,B.GOODSBATCH,B.QUALITYFLG,D.OUTSTOCKTYPE,
            O.COMPANYID,G.SNAME,A.RELATPICKINGTASKID,B.INDIPICKBIGUNITNUM,B.INDIMIDUNITNUM,
            B.INDISTANDUNITNUM,B.PICKINGQTY,B.PICKAREAID,B.PICKPOSID,B.PREPICKPOSID,
            B.BUFFERPOSID,A.STAGINGZONEID,A.STAGINGLOCATIONID,B.OUTSTOCKINDICATIONID,
            D.ORDERDATE,M.TRANSFERTYPE,A.RELEASEGROUPID,D.CUSTID,F.SNAME,F.CUSTTYPE,D.ISURGENCY,
            D.MFLAG,A.PICKWAVEID,B.PICKINGMAN,A.CREATTIME,B.PICKINGSTARTTIME,B.PICKINGENDTIME,
            A.ISJOIN,A.STAGINGMAN,B.CHECKMAN,A.STAGINGTIME,B.CHECKENDTIME,D.MFLAG,B.PICKINGMAN,
            B.MACHECKMAN,B.PICKINGENDTIME,B.MACHECKENDTIME,A.PACKMAN,A.PACKTIME,B.CHECKEXCEPTION,
            B.EXCEPTIONREASONID,B.UPDATEDATE,B.REPTASKFLG,A.OUTCONTAINERTYPE,A.OUTCONTAINERTYPE,
            A.MULTCUSTBOXNO,W.LOADINGDOCID,A.STAGINGMAN,A.STAGINGTIME,A.LOADEDMAN,A.LOADEDTIME,
            A.PREPICKFLG,W.SHIPPINGCONFIRMUSER,W.SHIPPINGCONFIRMTIME,A.RELAMAN,A.RELATIME,
            A.RELACONTAINERNO,O.CREATEDATE,B.PRESTATUS,A.OUTLABELNO,B.BUSINESSTYPE,A.PREPICKFLG,
            B.PREPICKMAN,B.PREPICKTIME,B.ACTUALPICKINGBIGUNITNUM,B.ACTUALPICKINGMIDUNITNUM,
            B.ACTUALPICKINGSTANDUNITNUM,B.ACTUALPICKINGNUM,B.BOXDETAILID,GA.GOODSAREATYPE,
            M.DELIVERADDRESS,PREPICKNUM,GA.DPSFLG,A.BUFFERPOSFLG,S.FORM,A.ROLLCONTAINERNO,
            A.ROLLCONTAINERMAN,A.ROLLCONTAINERTIME,A.COLDPACKFLG,B.OUTSTOCKINFORMID,B.OUTSTOCKINDICATIONDTID,
            B.MID1,B.MID2,B.MID3,B.MID4,B.MID5,S.TEMPTYPEID,O.CREATEDATE,A.FILLFLAG,B.PICKINGQTY,
            SS.STANDNETWEIGHT,S.STANDNETWEIGHT,B.CHECKFLG,A.SECONDOUTLABELNO,A.PRIORITY ,
            A.IP,S.MAH,S.FACTORYNAME,S.FACTORYCODE,S.FACTORYTYPE,S.FACTORYLICENSE,S.ISMINSO,BB.MATERIALID