订单一览

订单一览

查询条件:出库通知单号（OUTSTOCKINFORMID）

头单

 SELECT DISTINCT A.NOINDICATIONOUTID,
                 f_get_companysname(A.LENDCOMPANYID) AS LENDCOMPANYNAME,
                 A.SALESCD 销售员,
                 A.SALESNAME 销售员全称,
                 A.SALESMANPHONENO,
                 A.EXTERNALSOID,
                 A.INVOICENO 发票号,
                 A.OUTSTOCKINFORMID 出库通知单号,
                 A.HOLDINGREASON,
                 A.STATUS ,
                 I.SNAME 订单状态,
                 A.ORDERNO 订单号,
                 A.RELATEDORDERID,
                 A.OUTSTOCKTYPE,
                 J.SNAME 出库类型,
                 A.CREATEDATE,
                 to_number(NVL(A.ERPADDRESSFLAG, '0')) ERPADDRESSFLAG,
                 A.receivetime Ordergodate,
                 BB.ARRIVALDATE 要求到货时间,
                 A.OWNERID,
                 A.COMPANYID,
                 C.SNAME 分公司,
                 A.CUSTID 客户编号,
                 D.SNAME 客户全称,
                 D.CUSTTYPE,
                 K.SNAME 客户类型,
                 BB.SHIPPERID,
                 BB.SHIPPERNAME 承运单位,
                 BB.TRANSFERTYPE,
                 BB.TRANSFERTYPENAME 承运方式,
                 BB.ORDERTYPE,
                 BB.DELIVERAREAID ,
                 BB.DELIVERAREANAME 配送区域,
                 BB.DELIVERROUTEID,
                 BB.DELIVERROUTENAME 配送线路,
                 A.ISURGENCY,
                 A.MFLAG,
                 A.REMARK,
                 A.CREATOR 创建人,
                 H.SHORTNAME CREATORNAME,
                 A.CONFIRMMAN,
                 A.CONFIRMDATE,
                 A.STOCKID,
                 A.UPDATEMAN,
                 A.UPDATEDATE,
                 A.HOLDINGFLAG 拦截标志,
                 A.TREATOR,
                 A.ISCROSS,
                 A.ISBATCH,
                 A.QUALITYFLG,
                 A.ACTIONFLG,
                 A.ISINVOICENEED,
                 A.ISPAYNEED,
                 A.ISORDERMERGED,
                 A.CREATEDATE4WMS,
                 to_number(NVL(A.RELATIONFLAG, 0)) AS RELATIONFLAG
   FROM OUTSTOCKINFORMTBL A,
        COMPANYMST C,
        CUSTOMERMST D,
        USERMST H,
        OUTSTOCKINFORMDTTBL T,
        GOODSMST S,
        (SELECT CODEVALUE, SNAME
           FROM BASEDETAILMST
          WHERE CODEID = 'SYS_OUTSTCINFORMSTATUS') I,
        (SELECT CODEVALUE, SNAME
           FROM BASEDETAILMST
          WHERE CODEID = 'SYS_OUTSTCTYPE') J,
        (SELECT CODEVALUE, SNAME
           FROM BASEDETAILMST
          WHERE CODEID = 'SYS_CUSTOMERTYPE') K,
        (SELECT B.OUTSTOCKINFORMID,
                B.ARRIVALDATE,
                B.SHIPPERID,
                E.SNAME            SHIPPERNAME,
                B.TRANSFERTYPE,
                R.SNAME            TRANSFERTYPENAME,
                B.DELIVERAREAID,
                F.SNAME            DELIVERAREANAME,
                B.DELIVERROUTEID,
                G.SNAME            DELIVERROUTENAME,
                B.ORDERTYPE
           FROM DELIVERTBL B,
                SHIPPERMST E,
                DELIVERAREAMST F,
                DELIVERROUTEMST G,
                (SELECT CODEVALUE, SNAME
                   FROM BASEDETAILMST
                  WHERE CODEID = 'SYS_TRANCFERTYPE') R
          WHERE E.SHIPPERID = B.SHIPPERID
            AND F.DELIVERAREAID = B.DELIVERAREAID
            AND G.DELIVERROUTEID = B.DELIVERROUTEID
            AND G.DELIVERAREAID = B.DELIVERAREAID
            AND R.CODEVALUE = B.TRANSFERTYPE) BB

  WHERE 1 = 1
    AND (A.OUTSTOCKINFORMID LIKE '%01929056419%')
    AND A.OWNERID = C.OWNERID(+)
    AND A.COMPANYID = C.COMPANYID(+)
    AND D.CUSTID = A.CUSTID
    AND H.USERID(+) = A.CREATOR
    AND I.CODEVALUE = A.STATUS
    AND J.CODEVALUE = A.OUTSTOCKTYPE
    AND K.CODEVALUE(+) = D.CUSTTYPE
    AND A.OUTSTOCKINFORMID = T.OUTSTOCKINFORMID
    AND S.GOODSID = T.GOODSID
    AND A.OUTSTOCKINFORMID = BB.OUTSTOCKINFORMID(+)
  ORDER BY A.CREATEDATE DESC

订单明细

双击 查看 细单

SELECT F.LENDCOMPANYID,
       F_GET_COMPANYSNAME(F.LENDCOMPANYID) AS LENDCOMPANYNAME,
       TO_NUMBER(NVL(A.ISLPZ, 0)) AS 是否两票,
       B.NAME 商品名称,
       B.MODEL 规格,
       B.TRADEMARK 厂牌,
       C.PRODUCTDATE 生产日期,
       C.EXPIREDATE 过期日期,
       D.SNAME 分公司,
       B.GOODSTOPCATEGORYID 流转类型,
       CASE
         WHEN A.MID3 = 'BLC' THEN
          1
         ELSE
          0
       END BLOCKSTATUS,
       A.GOODSPOSID,
       F_GET_BASEDATA_NAME('SYS_GOODSCATEGORY', B.GOODSCATEGORYID) AS GOODSCATEGORYID， A.GOODSID 商品编码,
       A.GOODSBATCH 批号,
       (SELECT NAME
          FROM BASEDETAILMST BD
         WHERE BD.codeid = 'SYS_QUALITYFLAG'
           AND BD.CODEVALUE = A.Qualityflg) 品质标记,
       A.Applynum 订单数量， A.MID1,
       A.MID2,
       A.MID3,
       A.MID4,
       A.MID5,
       A.ME_LNO 药交所行号， A.OWNERID,
       A.COMPANYID

  FROM OUTSTOCKINFORMDTTBL A,
       GOODSMST            B,
       GOODSBATCHMST       C,
       COMPANYMST          D,
       OUTSTOCKINFORMTBL   F
 WHERE 1 = 1
   AND A.GOODSID = B.GOODSID
   AND A.GOODSID = C.GOODSID(+)
   AND A.GOODSBATCH = C.GOODSBATCH(+)
   AND A.COMPANYID = D.COMPANYID(+)
   AND A.OWNERID = D.OWNERID(+)
   AND A.OUTSTOCKINFORMID = F.OUTSTOCKINFORMID
   AND A.OUTSTOCKINFORMID = '01929056419'