验收办理一览




 SELECT CT.NAME AS SUPPLIER,C.BIGPERNUM 件比,IF.INSTOCKBIGQTY*C.BIGPERNUM+IF.INSTOCKSMALLQTY AS 到货数量,AD.INSTOCKDATE 实际到货日期 ,BASE.SNAME AS FORM,NVL(C.LICENSENUMBER,B.LICENSENUMBER) LICENSENUMBER,
        CP.COMPANYID,CP.SNAME AS  分公司名称,AD.INSTOCKID 收货单号, AD.INSTOCKDTID, AD.INFORMNO, AD.INFORMDTID, AD.PURCHASENO,AD.INSTOCKMAN AS INSTOCKMANID, 
        AD.GOODSID 商品名称, IC.GOODSBATCH 批号, IC.QUALITYFLG 品质标记, AD.INSTOCKINFORMNUM, AD.MID2, AD.MID3 EXPRESSNO,IMG.ADDRESS, IMG.CONTACTOR, IMG.CUSTTELEPHONE, 
        to_number(NVL(IC.CHECKFLG,0)) 验收标志,NVL(IC.PRESSBIGNUM,0) PRESSBIGNUM,NVL(IC.PRESSSTANDNUM,0) PRESSSTANDNUM,
        IC.CHECKBIGUNITNUM 验收整数, IC.CHECKSTANDUNITNUM 验收零数, IC.STATEREPORT, IC.STATEPACKAGE, IC.STATELABEL, IC.STATEINSTRUCTIONS, IC.STATEEXTERIOR,IC.CHECKMAN AS CHECKMANID ,IC.CHECKMAN2 AS CHECKMAN2ID,
        B.MAH,B.FACTORYNAME,B.NAME AS 商品名称, B.MODEL, B.TRADEMARK,B.GOODSTOPCATEGORYID, B.GOODSCATEGORYID,C.PRODUCTDATE, C.EXPIREDATE,B.YLQXZCRBAR ,B.SCXKZHBAH,
        A.ASNNO 到货单号,IF.INSTOCKBIGQTY,IF.INSTOCKSMALLQTY,IF.INSTOCKBIGQTY*C.BIGPERNUM+IF.INSTOCKSMALLQTY INNUM,
        ID.INSTOCKINFORMNUM,U1.USERNM INSTOCKMAN,U2.USERNM CHECKMAN,IC.CHECKMAN2,U3.USERNM CHECKMAN2NM,IC.QUALITYREASONCODE,IC.UNUSUALREMARK,AZ.AZGOODSID,IC.REMARK, 
        A.STOCKID,ID.CREATETIME,
        f_get_basedata_name('SYS_JYFW',CASE WHEN LENGTH(INSTR(B.BUSINESSSCOPE, '1')) < 2 THEN '0'||TO_CHAR(INSTR(B.BUSINESSSCOPE, '1')) ELSE TO_CHAR(INSTR(B.BUSINESSSCOPE, '1')) END) AS BUSINESSSCOPE
   FROM INSTOCKTBL A, 
        INSTOCKDTTBL AD, 
        INSTOCKCHECKTBL IC, 
        (SELECT INSTOCKID,INSTOCKDTID,GOODSID,GOODSBATCH,QUALITYFLG,
          SUM(INSTOCKBIGQTY) INSTOCKBIGQTY,SUM(INSTOCKSMALLQTY) INSTOCKSMALLQTY FROM INSTOCKFACTDTTBL 
         WHERE NVL(UNUSUALFLG,'0')='0'
         GROUP BY INSTOCKID,INSTOCKDTID,GOODSID,GOODSBATCH,QUALITYFLG) IF, 
        GOODSMST B,
       VIEW_INSTOCK_SUPPLIERINFO IMG,
        GOODSBATCHMST C,
        INSTOCKINFORMDTTBL ID,
        USERMST U1,
        USERMST U2,
        USERMST U3,COMPANYMST CP,CUSTOMERMST  CT,(SELECT  A.CODEVALUE,A.SNAME FROM BASEDETAILMST A WHERE A.CODEID='SYS_GSPFORM') BASE
       ,AZGOODSMST AZ
  WHERE A.INSTOCKID = AD.INSTOCKID AND A.COMPANYID=CP.COMPANYID
    AND AD.INSTOCKID = IC.INSTOCKID AND AD.INSTOCKDTID=IC.INSTOCKDTID AND AD.GOODSID=IC.GOODSID
    AND IC.INSTOCKID = IF.INSTOCKID AND IC.INSTOCKDTID=IF.INSTOCKDTID AND IC.GOODSID=IF.GOODSID AND IC.GOODSBATCH=IF.GOODSBATCH AND IC.QUALITYFLG = IF.QUALITYFLG 
    AND AD.GOODSID = B.GOODSID  AND B.GOODSID=AZ.CQPGOODSID(+)
    AND IC.GOODSID = C.GOODSID(+) AND IC.GOODSBATCH = C.GOODSBATCH(+)
    AND A.ASNNO = ID.ASNNO AND AD.GOODSID=ID.GOODSID AND AD.GOODSBATCH=ID.GOODSBATCH AND NVL(AD.INFORMNO,'-')=NVL(ID.INFORMNO,'-')  AND NVL(AD.INFORMDTID, '-') = NVL(ID.INFORMDTID, '-')
    AND AD.INSTOCKMAN = U1.USERID(+)
    AND IC.CHECKMAN = U2.USERID(+)
    AND AD.INSTOCKID=IMG.INSTOCKID(+) AND IMG.GPS_LNO(+)=1
    AND IC.CHECKMAN2 = U3.USERID(+) AND CT.CUSTID(+)=A.SUPPLIERID AND LTRIM(RTRIM(B.FORM))=BASE.CODEVALUE(+)
    AND CASE WHEN A.STATUS='SYS_INSTOCKTATUSINSTOCKTATUS_END' THEN AD.INNUM ELSE 1 END > 0
    AND A.INSTOCKID='AN802023072600647-01'
 ORDER BY A.CREATETIME DESC

CQP



 SELECT CT.NAME AS SUPPLIER,C.BIGPERNUM 件比,IF.INSTOCKBIGQTY*C.BIGPERNUM+IF.INSTOCKSMALLQTY AS 到货数量,AD.INSTOCKDATE 实际到货日期 ,BASE.SNAME AS FORM,NVL(C.LICENSENUMBER,B.LICENSENUMBER) LICENSENUMBER,
        CP.COMPANYID,CP.SNAME AS  分公司名称,AD.INSTOCKID 收货单号, AD.INSTOCKDTID, AD.INFORMNO, AD.INFORMDTID, AD.PURCHASENO,AD.INSTOCKMAN AS INSTOCKMANID, 
        AD.GOODSID 商品名称, IC.GOODSBATCH 批号, IC.QUALITYFLG 品质标记, AD.INSTOCKINFORMNUM, AD.MID2, AD.MID3 EXPRESSNO,
        to_number(NVL(IC.CHECKFLG,0)) 验收标志,NVL(IC.PRESSBIGNUM,0) PRESSBIGNUM,NVL(IC.PRESSSTANDNUM,0) PRESSSTANDNUM,
        IC.CHECKBIGUNITNUM 验收整数, IC.CHECKSTANDUNITNUM 验收零数, IC.STATEREPORT, IC.STATEPACKAGE, IC.STATELABEL, IC.STATEINSTRUCTIONS, IC.STATEEXTERIOR,IC.CHECKMAN AS CHECKMANID ,IC.CHECKMAN2 AS CHECKMAN2ID,
        B.MAH,B.FACTORYNAME,B.NAME AS 商品名称, B.MODEL, B.TRADEMARK,B.GOODSTOPCATEGORYID, B.GOODSCATEGORYID,C.PRODUCTDATE, C.EXPIREDATE,B.YLQXZCRBAR ,B.SCXKZHBAH,
        A.ASNNO 到货单号,IF.INSTOCKBIGQTY,IF.INSTOCKSMALLQTY,IF.INSTOCKBIGQTY*C.BIGPERNUM+IF.INSTOCKSMALLQTY INNUM,
        ID.INSTOCKINFORMNUM,U1.USERNM INSTOCKMAN,U2.USERNM CHECKMAN,IC.CHECKMAN2,U3.USERNM CHECKMAN2NM,IC.QUALITYREASONCODE,IC.UNUSUALREMARK,IC.REMARK, 
        A.STOCKID,ID.CREATETIME,
        f_get_basedata_name('SYS_JYFW',CASE WHEN LENGTH(INSTR(B.BUSINESSSCOPE, '1')) < 2 THEN '0'||TO_CHAR(INSTR(B.BUSINESSSCOPE, '1')) ELSE TO_CHAR(INSTR(B.BUSINESSSCOPE, '1')) END) AS BUSINESSSCOPE
   FROM INSTOCKTBL A, 
        INSTOCKDTTBL AD, 
        INSTOCKCHECKTBL IC, 
        (SELECT INSTOCKID,INSTOCKDTID,GOODSID,GOODSBATCH,QUALITYFLG,
          SUM(INSTOCKBIGQTY) INSTOCKBIGQTY,SUM(INSTOCKSMALLQTY) INSTOCKSMALLQTY FROM INSTOCKFACTDTTBL 
         WHERE NVL(UNUSUALFLG,'0')='0'
         GROUP BY INSTOCKID,INSTOCKDTID,GOODSID,GOODSBATCH,QUALITYFLG) IF, 
        GOODSMST B,
        GOODSBATCHMST C,
        INSTOCKINFORMDTTBL ID,
        USERMST U1,
        USERMST U2,
        USERMST U3,COMPANYMST CP,CUSTOMERMST  CT,(SELECT  A.CODEVALUE,A.SNAME FROM BASEDETAILMST A WHERE A.CODEID='SYS_GSPFORM') BASE

  WHERE A.INSTOCKID = AD.INSTOCKID AND A.COMPANYID=CP.COMPANYID
    AND AD.INSTOCKID = IC.INSTOCKID AND AD.INSTOCKDTID=IC.INSTOCKDTID AND AD.GOODSID=IC.GOODSID
    AND IC.INSTOCKID = IF.INSTOCKID AND IC.INSTOCKDTID=IF.INSTOCKDTID AND IC.GOODSID=IF.GOODSID AND IC.GOODSBATCH=IF.GOODSBATCH AND IC.QUALITYFLG = IF.QUALITYFLG 
    AND AD.GOODSID = B.GOODSID  
    AND IC.GOODSID = C.GOODSID(+) AND IC.GOODSBATCH = C.GOODSBATCH(+)
    AND A.ASNNO = ID.ASNNO AND AD.GOODSID=ID.GOODSID AND AD.GOODSBATCH=ID.GOODSBATCH AND NVL(AD.INFORMNO,'-')=NVL(ID.INFORMNO,'-')  AND NVL(AD.INFORMDTID, '-') = NVL(ID.INFORMDTID, '-')
    AND AD.INSTOCKMAN = U1.USERID(+)
    AND IC.CHECKMAN = U2.USERID(+)
    AND IC.CHECKMAN2 = U3.USERID(+) AND CT.CUSTID(+)=A.SUPPLIERID AND LTRIM(RTRIM(B.FORM))=BASE.CODEVALUE(+)
    AND CASE WHEN A.STATUS='SYS_INSTOCKTATUSINSTOCKTATUS_END' THEN AD.INNUM ELSE 1 END > 0
    AND A.INSTOCKID='ANS52023122600020-01'
 ORDER BY A.CREATETIME DESC

是是是

SELECT 
       AD.INSTOCKID,
       AD.PURCHASENO,
       AD.GOODSID,
       IC.GOODSBATCH,
       C.BIGPERNUM,
       IC.QUALITYFLG,
       A.ASNNO,
       IF.INSTOCKBIGQTY,
       IF.INSTOCKSMALLQTY,
       IF.INSTOCKBIGQTY * C.BIGPERNUM + IF.INSTOCKSMALLQTY INNUM
  FROM INSTOCKTBL A,
       INSTOCKDTTBL AD,
       INSTOCKCHECKTBL IC,
       (SELECT INSTOCKID,
               INSTOCKDTID,
               GOODSID,
               GOODSBATCH,
               QUALITYFLG,
               SUM(INSTOCKBIGQTY) INSTOCKBIGQTY,
               SUM(INSTOCKSMALLQTY) INSTOCKSMALLQTY
          FROM INSTOCKFACTDTTBL
         WHERE NVL(UNUSUALFLG, '0') = '0'
         GROUP BY INSTOCKID, INSTOCKDTID, GOODSID, GOODSBATCH, QUALITYFLG) IF,
       GOODSMST B,
       GOODSBATCHMST C,
       INSTOCKINFORMDTTBL ID,
       USERMST U1,
       USERMST U2,
       USERMST U3,
       COMPANYMST CP,
       CUSTOMERMST CT,
       (SELECT A.CODEVALUE, A.SNAME
          FROM BASEDETAILMST A
         WHERE A.CODEID = 'SYS_GSPFORM') BASE,
       AZGOODSMST AZ
 WHERE A.INSTOCKID = AD.INSTOCKID
   AND A.COMPANYID = CP.COMPANYID
   AND AD.INSTOCKID = IC.INSTOCKID
   AND AD.INSTOCKDTID = IC.INSTOCKDTID
   AND AD.GOODSID = IC.GOODSID
   AND IC.INSTOCKID = IF.INSTOCKID
   AND IC.INSTOCKDTID = IF.INSTOCKDTID
   AND IC.GOODSID = IF.GOODSID
   AND IC.GOODSBATCH = IF.GOODSBATCH
   AND IC.QUALITYFLG = IF.QUALITYFLG
   AND AD.GOODSID = B.GOODSID
   AND B.GOODSID = AZ.CQPGOODSID(+)
   AND IC.GOODSID = C.GOODSID(+)
   AND IC.GOODSBATCH = C.GOODSBATCH(+)
   AND A.ASNNO = ID.ASNNO
   AND AD.GOODSID = ID.GOODSID
   AND AD.GOODSBATCH = ID.GOODSBATCH
   AND NVL(AD.INFORMNO, '-') = NVL(ID.INFORMNO, '-')
   AND NVL(AD.INFORMDTID, '-') = NVL(ID.INFORMDTID, '-')
   AND AD.INSTOCKMAN = U1.USERID(+)
   AND IC.CHECKMAN = U2.USERID(+)
   AND IC.CHECKMAN2 = U3.USERID(+)
   AND CT.CUSTID(+) = A.SUPPLIERID
   AND LTRIM(RTRIM(B.FORM)) = BASE.CODEVALUE(+)
   AND CASE
         WHEN A.STATUS = 'SYS_INSTOCKTATUSINSTOCKTATUS_END' THEN
          AD.INNUM
         ELSE
          1
       END > 0
文档更新时间: 2023-12-28 16:09   作者:周骏