头

SELECT DISTINCT  OSI.CONFIRMMANNAME,OSI.CONFIRMMANIDCARE,OS.RETURNFLG,CP.SNAME COMPANYID , OS.STATUS, OS.OUTSTOCKINDICATIONID, OWD.PICKWAVEID,
       OS.THISNUM, O.SNAME OUTSTOCKTYPE, D.DELIVERAREAID, D.DELIVERROUTEID,
       D.DELIVERID, BC.SNAME CUSTOMERTYPE, pt.STAGINGZONEID,
       pt.STAGINGLOCATIONID, OS.ORDERDATE, OS.ARRIVALDATE, C.SNAME CUSTID,
       W.SNAME OWNERID, Z.SNAME SHIPPERID,  T.SNAME TRANSFERTYPE, OS.ISURGENCY, OS.MFLAG,
      CASE WHEN G.TEMPTYPEID='SYS_TEMPTYPESYS_TEMPTYPE_COOL' or G.TEMPTYPEID='SYS_TEMPTYPESYS_TEMPTYPE_FROZEN' THEN 1 ELSE 0 END ISCOLD, 
       OS.REMARK,S.SNAME STATUSNAME,USERMST.USERNM UPDATEMAN,OS.UPDATEDATE,OS.GETERID SignMan,OS.INVOICENO ARRREMARK,OS.ATTACHMENT  
 ,CASE 
                 CASE
                  (SELECT COUNT(1)
                     FROM (SELECT NVL(AR1.REPORT, '-') ARREPORT,
                                  OS1.Outstockindicationid
                             FROM OUTSTOCKINDICATIONTBL OS1,
                                  OUTSTOCKPICKDTTBL     OP1,
                                  LOADEDORDERTBL        LO1,
                                  ARRIVALAUTOINFOTBL    AR1
                            WHERE OS1.OUTSTOCKINDICATIONID =
                                  OP1.OUTSTOCKINDICATIONID
                              And OP1.Status <> '7'
                              AND OP1.OUTSTOCKINDICATIONID =
                                  LO1.OUTSTOCKINDICATIONID(+)
                              AND OP1.OUTSTOCKINDICATIONDTID =
                                  LO1.OUTSTOCKINDICATIONDTID(+)
                              AND OP1.MID1 = LO1.MID1(+)
                              AND OP1.MID2 = LO1.MID2(+)
                              AND OP1.MID3 = LO1.MID3(+)
                              AND OP1.MID4 = LO1.MID4(+)
                              AND OP1.MID5 = LO1.MID5(+)
                              AND OP1.GOODSBATCH = LO1.GOODSBATCH(+)
                              AND OP1.QUALITYFLG = LO1.QUALITYFLG(+)
                              AND LO1.LOADINGDOCID = AR1.LOADINGDOCID(+)
                              AND LO1.INNERCODE1 = AR1.INNERCODE1(+)
                              AND LO1.INNERCODE2 = AR1.INNERCODE2(+)
                              AND LO1.OUTSTOCKINDICATIONID =
                                  AR1.OUTSTOCKINDICATIONID(+)) MM
                    WHERE MM.ARREPORT = '-'
                      AND MM.OUTSTOCKINDICATIONID = OS.OUTSTOCKINDICATIONID)
                   WHEN 0 THEN
                    1
                   ELSE
                    0
                 END + CASE NVL(OS.ATTACHMENT, '-')
                   WHEN '-' THEN
                    0
                   ELSE
                    1
                 END
                  WHEN 2 THEN
                   1
                  ELSE
                   0
                END REPORTFLG
  FROM OUTSTOCKINDICATIONTBL OS,COMPANYMST  CP,OUTSTOCKINFORMTBL OSI,
       DELIVERTBL D,
       CUSTOMERMST C,
       OUTWAVEDETAILTBL OWD,
       (SELECT CODEVALUE, SNAME
          FROM BASEDETAILMST
         WHERE CODEID = 'SYS_OUTSTOCKINDI_STATUS') S,
       (SELECT CODEVALUE, SNAME
          FROM BASEDETAILMST
         WHERE CODEID = 'SYS_OUTSTCTYPE') O,
       (SELECT CODEVALUE, SNAME
          FROM BASEDETAILMST
         WHERE CODEID = 'SYS_TRANCFERTYPE') T,
       (SELECT CODEVALUE, SNAME
          FROM BASEDETAILMST
         WHERE CODEID = 'SYS_CUSTOMERTYPE') BC
     ,DELIVERAREAMST DA,DELIVERROUTEMST DR
     ,OWNERMST W
     ,SHIPPERMST Z
     ,OUTSTOCKINDICATIONSHIPDTTBL OSD,USERMST,OUTSTOCKINDICATIONDTTBL OSDT,GOODSMST G 
     ,OUTSTOCKPICKDTTBL DTPT,OUTSTOCKPICKTBL PT
  WHERE OS.OUTSTOCKINFORMID = D.OUTSTOCKINFORMID  AND OS.OUTSTOCKINFORMID = OSI.OUTSTOCKINFORMID
     And OS.OUTSTOCKINDICATIONID=DTPT.OUTSTOCKINDICATIONID
        And DTPT.PICKINGTASKID=PT.PICKINGTASKID 
   AND OS.CUSTID = C.CUSTID
   AND OS.COMPANYID=CP.COMPANYID
   AND D.SHIPPERID = Z.SHIPPERID(+) 
   AND OWD.OUTSTOCKINDICATIONID(+) = OS.OUTSTOCKINDICATIONID
   AND S.CODEVALUE = OS.STATUS
   AND O.CODEVALUE = OS.OUTSTOCKTYPE
   AND T.CODEVALUE = D.TRANSFERTYPE
   AND BC.CODEVALUE = C.CUSTTYPE
   AND DA.DELIVERAREAID = DR.DELIVERAREAID
   AND D.DELIVERAREAID = DR.DELIVERAREAID
   AND D.DELIVERROUTEID = DR.DELIVERROUTEID
   AND W.OWNERID = OS.OWNERID
   AND OS.OUTSTOCKINDICATIONID = OSD.OUTSTOCKINDICATIONID(+)
   AND OS.OUTSTOCKINDICATIONID = OSDT.OUTSTOCKINDICATIONID
   AND USERMST.USERID=OS.UPDATEMAN 
   AND OSDT.GOODSID=G.GOODSID 
   AND OS.OUTSTOCKINDICATIONID LIKE 'GA0GA0220231016000068%'

2

明细

   SELECT   OS.RETURNFLG, OP.GOODSID, G.NAME, G.MODEL, G.TRADEMARK, O.SNAME MID1, 
         OP.MID2, OP.MID3, OP.MID4, OP.MID5, OP.GOODSBATCH, NVL(CP.SNAME,'-') AS COMPANYID, 
         SUM (OP.INDIPICKBIGUNITNUM) INDIPICKBIGUNITNUM, 
         SUM (OP.INDIMIDUNITNUM) INDIMIDUNITNUM, 
         SUM (OP.INDISTANDUNITNUM) INDISTANDUNITNUM, 
         SUM (OP.PICKINGQTY) PICKINGQTY, B.SNAME QUALITYFLG, 
        OSD.INNERCODE1, OSD.INNERCODE2 
        --LO.INNERCODE1, LO.INNERCODE2 
       ,decode(AR.report,null,null,'http://yjbg.cq-p.com.cn:11000/CQP_SHTXD/'||replace(AR.report,',',';http://yjbg.cq-p.com.cn:11000/CQP_SHTXD/'))  REPORT
    FROM OUTSTOCKPICKDTTBL OP,COMPANYMST CP, 
         GOODSMST G, 
         OUTSTOCKINDICATIONTBL OS, 
         (SELECT CODEVALUE, SNAME 
            FROM BASEDETAILMST 
           WHERE CODEID = 'SYS_QUALITYFLAG') B, 
         OWNERMST O, 
         OUTSTOCKINDICATIONSHIPDTTBL OSD 
         ，LOADEDORDERTBL LO  
      ,  ARRIVALAUTOINFOTBL AR
   WHERE OP.GOODSID = G.GOODSID 
     AND OS.OUTSTOCKINDICATIONID = OP.OUTSTOCKINDICATIONID 
     AND OP.OUTSTOCKINDICATIONID = OSD.OUTSTOCKINDICATIONID(+) 
     AND OP.OUTSTOCKINDICATIONDTID = OSD.OUTSTOCKINDICATIONDTID(+) 
     AND OP.OUTSTOCKINDICATIONID = LO.OUTSTOCKINDICATIONID(+) 
     AND OP.OUTSTOCKINDICATIONDTID = LO.OUTSTOCKINDICATIONDTID(+) 
     AND OP.GOODSID = OSD.GOODSID(+)
     AND OP.GOODSID = LO.GOODSID(+)
     AND OP.COMPANYID=CP.COMPANYID(+)
     AND OP.MID1 = OSD.MID1(+)
     AND OP.MID2 = OSD.MID2(+)
     AND OP.MID3 = OSD.MID3(+)
     AND OP.MID4 = OSD.MID4(+)
     AND OP.MID5 = OSD.MID5(+)
     AND OP.GOODSBATCH = OSD.GOODSBATCH(+)
     AND OP.QUALITYFLG = OSD.QUALITYFLG(+)
     AND OP.MID1 = LO.MID1(+)
     AND OP.MID2 = LO.MID2(+)
     AND OP.MID3 = LO.MID3(+)
     AND OP.MID4 = LO.MID4(+)
     AND OP.MID5 = LO.MID5(+)
     AND OP.GOODSBATCH = LO.GOODSBATCH(+)
     AND OP.QUALITYFLG = LO.QUALITYFLG(+)
     AND OP.OUTSTOCKINDICATIONID = '' 
     AND B.CODEVALUE = OP.QUALITYFLG 
     AND O.OWNERID(+) = OP.OWNERID 
     AND LO.LOADINGDOCID=AR.LOADINGDOCID(+) 
     AND LO.INNERCODE1=AR.INNERCODE1(+) 
     AND LO.INNERCODE2=AR.INNERCODE2(+) 
     --AND LO.OUTSTOCKINDICATIONID=AR.OUTSTOCKINDICATIONID(+)
     And OP.Status <> 'SYS_PICKSTATUSPICKSTATUS_PICKFORCEFINISH'

    --AND OS.OUTSTOCKINDICATIONID LIKE 'GA0GA02202310160000%'
 GROUP BY OS.RETURNFLG, 
         OP.GOODSID, 
         G.NAME, 
         G.MODEL, 
         G.TRADEMARK, 
         O.SNAME, 
         OP.MID2, 
         OP.MID3, 
         OP.MID4, 
         OP.MID5, 
         OP.GOODSBATCH, 
         B.SNAME, 
         CP.SNAME, 
         OSD.INNERCODE1, 
         OSD.INNERCODE2
         --，LO.INNERCODE1, 
        --LO.INNERCODE2，
       ，decode(AR.report,null,null,'http://yjbg.cq-p.com.cn:11000/CQP_SHTXD/'||replace(AR.report,',',';http://yjbg.cq-p.com.cn:11000/CQP_SHTXD/'))