学步园

这里是在另一台oracle服务器连接 sap 的数据库写的视图，zsdvbap_ex 是个自定义表，保存了订单行的文本及可配置物料的特性值,其它均为 sap 标准表.

create or replace view V_SALESORDER_TRACKING_ABC
as
select 
  ltrim(vk.kunnr,'0') kunnr,--客户
  kv.klabc,--ABC等级
  ltrim(vp.vbeln,'0') vbeln,--订单
  ltrim(vp.posnr,'0') posnr,--行
  ltrim(vp.matnr,'0') matnr,--物料
  mt.MAKTX,--物料描述
  ve.EDATU,--交期
  
  vp.KWMENG,--订货量(订货单位)
  vp.VRKME,--订货单位
  
  vp.UMVKZ * vp.KWMENG / vp.UMVKN KLMENG, --订货量(基本单位)
  nvl(va.RFMNG,0) RFMNG,--已出货数量
  nvl(mk.kalab,0) kalab, --库存数量
  vp.UMVKZ * vp.KWMENG / vp.UMVKN  - nvl(mk.kalab,0) - nvl(va.RFMNG,0) PLMENG, --欠货数量
  vp.MEINS, --基本单位
  
  ma.MATKL, --"物料组
  t23.WGBEZ, --"物料组描叙
  ma.SPART,   --"产品组
  tat.VTEXT, --"产品组描叙
  vp.ERDAT, --创建日期
  
  ex.ITMTXT1
  || ex.ITMTXT2
  || ex.ITMTXT3 ITMTXT,--行文本
  ex.ATWTB1,
  ex.ATWTB2,
  ex.ATWTB3,
  ex.ATWTB4,--特性
  
  pa.SNAME -- 雇员名称
  


from sapsr3.knvv@sap kv
LEFT  JOIN  sapsr3.KNVP@sap kp ON kp.KUNNR = kv.KUNNR AND kp.VKORG = kv.VKORG AND kp.PARVW = 'ZM' 
INNER  JOIN  sapsr3.PA0001@sap pa ON pa.PERNR = kp.PERNR
inner join sapsr3.vbak@sap vk on vk.kunnr=kv.kunnr and vk.vkorg=kv.vkorg
inner join sapsr3.vbap@sap vp on vp.vbeln=vk.vbeln
INNER JOIN sapsr3.makt@sap mt  ON  mt.matnr = vp.matnr  AND mt.spras = '1'
INNER JOIN sapsr3.VBEP@sap ve ON ve.VBELN = vp.VBELN AND ve.POSNR = vp.POSNR AND ve.ETENR = 1 
INNER JOIN sapsr3.MARA@sap ma  ON ma.MATNR = vp.MATNR
left  Join sapsr3.T023T@sap t23 On t23.MATKL = ma.MATKL and t23.SpRAS = '1'
Left  Join sapsr3.TSPAT@sap tat on tat.SPART = ma.SPART and tat.spRAs = '1'
LEFT JOIN
    (SELECT VBELN,
      POSNR,
      SUM( KALAB ) kalab  --订单库存
     FROM sapsr3.MSKA@sap
     group by VBELN, POSNR
    ) mk   ON mk.vbeln  = vp.vbeln   AND mk.posnr = vp.posnr
    
LEFT JOIN
    (select  --取订单行的已发货数量
        vp2.vbeln,
        vp2.posnr,
        sum(va2.RFMNG)rfmng 
      from sapsr3.vbap@sap vp2
      inner join sapsr3.vbfa@sap va2  ON va2.VBELV  = vp2.vbeln   AND va2.POSNV = vp2.posnr and ( va2.VBTYP_N = 'T' or va2.VBTYP_N = 'J' )
      inner join sapsr3.vbup@sap vu  on vu.vbeln = va2.VBELN   and vu.posnr = va2.POSNN and vu.WBSTA = 'C'
      group by vp2.vbeln, vp2.posnr
     ) va   ON va.vbeln  = vp.vbeln   AND va.posnr = vp.posnr     
  LEFT JOIN sapsr3.ZSDVBAP_EX@sap ex
  ON ex.vbeln  = vp.vbeln
  AND ex.posnr = vp.posnr

where  loevm=' '   --没有删除标记的
  and vp.ABGRU=' '
order by ve.edatu,vp.ERDAT;