SAP-ABAP-Statistical Report on ordering, production, inventory, and delivery of sales orders

Here is the view written by connecting to the SAP database on another ORACLE Server. zsdvbap_ex is a custom table that stores the text of the order line and characteristic values of configurable materials, the others are SAP standard tables.

Create or replace view v_salesorder_tracking_abcasselect ltrim (VK. kunnr, '0') kunnr, -- customer KV. klabc, -- ABC level ltrim (VP. vbeln, '0') vbeln, -- order ltrim (VP. posnr, '0') posnr, -- line ltrim (VP. matnr, '0') matnr, -- material Mt. maktx, -- material description ve. edatu, -- delivery VP. kwmeng, -- order quantity (ordering unit) VP. vrkme, -- ordering unit VP. umvkz * VP. kwmeng/VP. umvkn klmeng, -- order amount (Basic Unit) nvl (va. rfmng, 0) rfmng, -- shipped nvl (MK. kalab, 0) kalab, -- inventory quantity VP. umvkz * VP. kwmeng/VP. umvkn-nvl (MK. kalab, 0)-nvl (va. rfmng, 0) plmeng, -- number of goods owed VP. meins, -- Basic Unit Ma. matkl, -- "material group t23.wgbez, --" material group Ma. spart, -- "product group tat. vtext, -- "Product Group VP. erdat, -- create date ex. itmtxt1 | ex. itmtxt2 | ex. itmtxt3 itmtxt, -- Line Text ex. atwtb1, Ex. atwtb2, Ex. atw78, ex. atwtb4, -- feature pa. sname -- employee name from sapsr3.knvv @ sap kvleft 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. pernrinner join sapsr3.vbak @ sap VK on VK. kunnr = KV. kunnr and VK. vkorg = KV. vkorginner join sapsr3.vbap @ sap VP on VP. vbeln = VK. vbelninner 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. matnrleft 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 -- order stock from sapsr3.mska @ SAP group by vbeln, posnr) MK on MK. vbeln = VP. vbeln and MK. posnr = VP. posnr left join (select -- obtain the number of shipments of the order line vp2.vbeln, vp2.posnr, sum (va2.rfmng) rfmng from nation @ 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. posnrwhere loevm = ''-- and VP without deleting tags. abgru = ''order by VE. edatu, VP. erdat;