Begin "Tables Rows Usage tblnam char30 "TableName numrows int4 "NumRows avgrowlen int4 "AvgRowLen lastdt dats "LastDate contflag char1 "Type end select a.table_name, a.num_rows, a.avg_row_len, TO_CHAR(a.last_analyzed,'YYYYMMDD') As last_analyzed, NVL(s.contflag,'A') as contflag from USER_tables a, DD02L s where a.table_name = s.tabname (+) and s.contflag not in ('S','W') and a.num_rows is not NULL and a.num_rows > 0 order by num_rows desc, table_name ------------------------------------------------------------------------------------------ Begin "Native hdrkey char8 " SID werks werks_d " Site fkdat fkdat " Billing date for billing index and printout kbdnr w_kbdnr " Cashier number kasnr w_kasnr " POS number bonnr w_bonnr " Transaction number (till receipt) hdrtot w_umsvpo " Rtl price from sales as per recei paytot w_umsvpo " Payment Amount diff w_umsvpo " Diff Hdr & pay end select a.hdrkey, a.werks, a.fkdat, a.kbdnr, a.kasnr, a.bonnr, a.umsgvo as hdrtot, ( select sum( umsgvo ) from zpospay b where b.hdrkey = a.hdrkey ) as paytot, ( umsgvo - ( select sum( umsgvo ) from zpospay b where b.hdrkey = a.hdrkey ) ) as diff from zposhdr a where a.mandt = :sy-mandt and abs ( umsgvo - ( select sum( umsgvo ) from zpospay b where b.hdrkey = a.hdrkey ) ) > 0.50 order by werks, fkdat, kbdnr, kasnr, bonnr ------------------------------------------------------------------------------------------ begin netwr dmbtr mwsbp dmbtr prcnt dmbtr brtwr dmbtr end SELECT vbrp.netwr, vbrp.mwsbp, case when vbrp.netwr > 0 then round(vbrp.mwsbp * 100 / vbrp.netwr, 1) else 0 end as prcnt, vbrp.brtwr FROM ( vbrp INNER JOIN vbrk ON vbrp.vbeln = vbrk.vbeln ) WHERE vbrp.werks = 'H003' AND vbrk.fkdat = '20070501' AND vbrk.fksto <> 'X' AND ( vbrk.vbtyp = 'M' OR vbrk.vbtyp = 'O' OR vbrk.vbtyp = 'P' ) ------------------------------------------------------------------------------------------ Begin "Native hdrkey char8 " SID werks werks_d " Site fkdat fkdat " Billing date for billing index and printout kbdnr w_kbdnr " Cashier number kasnam w_kasnam " Cashier name kasnr w_kasnr " POS number bonnr w_bonnr " Transaction number (till receipt) hdrtot w_umsvpo " Rtl price from sales as per recei itmsrl char2 " Item srlno matnr matnr " Article itmtot w_umsvpo " Item Amont cndsrl char2 " Condition srlno cond v_kscha " Condition type, sales cndval kwert " Condition value prmsrl char2 " Promo srlno prom v_kscha " Promo type, sales prmval kwert " Promo value end select h.hdrkey, h.werks, h.fkdat, h.kbdnr, h.kasnam, h.kasnr, h.bonnr, h.umsgvo as hdrtot, i.itmsrl, i.matnr, i.umsgvo as itmtot, c.cndsrl, c.kschl as cond, c.kwert as cndval, p.prmsrl, p.kschl as prom, p.kwert as prmval from zposhdr h, zpositm i, zposcnd c, zposprm p where h.mandt = i.mandt and h.hdrkey = i.hdrkey and i.mandt = c.mandt and i.hdrkey = c.hdrkey and i.itmsrl = c.itmsrl and h.mandt = p.mandt (+) and h.hdrkey = p.hdrkey (+) and h.mandt = :sy-mandt and h.werks = 'X007' and h.hdrkey in ( select hdrkey from zposhdr a WHERE a.fkdat > '20070101' AND abs ( umsgvo - ( select sum( umsgvo ) from zpositm b where b.hdrkey = a.hdrkey ) ) > 0.50 ) order by werks, fkdat, kbdnr, kasnr, bonnr, itmsrl, cndsrl, prmsrl ------------------------------------------------------------------------------------------ Begin "Compare Header & Items Not Matching hdrkey char8 "SID werks werks_d fkdat fkdat "BillDate kbdnr w_kbdnr "Cashier kasnam w_kasnam "CshrName kasnr w_kasnr "POS-number bonnr w_bonnr "ReceiptNum hdrtot w_umsvpo "Payment itmsrl char2 "ItemSrlno matnr matnr itmtot w_umsvpo "ItemAmount end select h.hdrkey, h.werks, h.fkdat, h.kbdnr, h.kasnam, h.kasnr, h.bonnr, h.umsgvo as hdrtot, i.itmsrl, i.matnr, i.umsgvo as itmtot from zposhdr h, zpositm i where h.mandt = i.mandt and h.hdrkey = i.hdrkey and h.mandt = :sy-mandt and h.werks = 'H008' and h.hdrkey in ( select hdrkey from zposhdr a WHERE a.fkdat >= '20070505' AND a.werks = 'H008' AND abs ( umsgvo - ( select sum( umsgvo ) from zpositm b where b.hdrkey = a.hdrkey ) ) > 0.50 ) order by werks, fkdat, kbdnr, kasnr, bonnr, itmsrl ------------------------------------------------------------------------------------------ BEGIN budat budat shkzg shkzg dmbtr dmbtr menge menge_d END select budat, shkzg, sum(DMBTR), sum(MENGE) from bsim where bwkey = 'H008' group by budat, shkzg ------------------------------------------------------------------------------------------ Begin sptag sptag " Period to analyze - current date mzubb menge_d " Valuated stock receipts quantity mzukb menge_d " Consignment stock receipts quantity magbb menge_d " Quantity of valuated stock issues magkb menge_d " Quantity of consignment stock issued wzubb dmbtr " Valuated stock receipts value wagbb dmbtr " Value of valuated stock that has been issued end SELECT sptag, sum(mzubb), sum(mzukb), sum(magbb), sum(magkb), sum(wzubb), sum(wagbb) FROM s033 WHERE s033.ssour = ' ' AND s033.vrsio = '000' AND s033.spmon = '000000' AND s033.spwoc = '000000' AND s033.spbup = '000000' AND s033.werks = 'H008' GROUP BY sptag ------------------------------------------------------------------------------------------ BEGIN "500 Production IDOCs in Error sndprn edi_sndprn credat edi_ccrdat idoctp edi_idoctp docnum edi_docnum status edi_status descrp edi_text60 direct edi_direct rcvpor edi_rcvpor rcvprn edi_rcvprn END SELECT i.sndprn, i.credat, i.idoctp, i.docnum, i.status, t.descrp, i.direct, i.rcvpor, i.rcvprn FROM edidc i, teds2 t WHERE i.mandt = :sy-mandt AND i.status NOT IN ('53', '73', '03', '16') AND i.status = t.status AND t.langua = 'E' AND ( i.sndprn in ( select werks from t001w ) OR i.rcvprn in ( select werks from t001w ) ) ------------------------------------------------------------------------------------------ BEGIN "Stkvchr Analysis Ultimate! werks werks_d matnr maktx menge menge_d dmbtr END SELECT werks, a.matnr, maktx, (creditqty - debitqty), (creditval - debitval) FROM ( SELECT mseg.werks, mseg.matnr, SUM( CASE WHEN shkzg = 'H' THEN menge ELSE 0 END ) AS creditqty, SUM( CASE WHEN shkzg = 'S' THEN menge ELSE 0 END ) AS debitqty, SUM( CASE WHEN shkzg = 'H' THEN dmbtr ELSE 0 END ) AS creditval, SUM( CASE WHEN shkzg = 'S' THEN dmbtr ELSE 0 END ) AS debitval FROM mkpf INNER JOIN mseg ON mkpf.mandt = mseg.mandt AND mkpf.mblnr = mseg.mblnr AND mkpf.mjahr = mseg.mjahr AND mseg.mandt = :sy-mandt WHERE mseg.werks IN ('D070', 'D071') AND mkpf.budat <= '20070430' AND mkpf.budat >= '20070401' AND ( mseg.bwart = '551' OR mseg.bwart = '552' ) GROUP BY mseg.werks, mseg.matnr ) a, makt b WHERE a.matnr = b.matnr AND b.spras = :sy-langu AND b.mandt = :sy-mandt ORDER BY werks, a.matnr ------------------------------------------------------------------------------------------ BEGIN "StkVchr Analysis Dense Ultimate! werks werks_d matnr maktx menge menge_d dmbtr END SELECT werks, a.matnr, maktx, sum((creditqty - debitqty)), sum((creditval - debitval)) FROM ( SELECT mseg.werks, mseg.matnr, SUM( CASE WHEN shkzg = 'H' THEN menge ELSE 0 END ) AS creditqty, SUM( CASE WHEN shkzg = 'S' THEN menge ELSE 0 END ) AS debitqty, SUM( CASE WHEN shkzg = 'H' THEN dmbtr ELSE 0 END ) AS creditval, SUM( CASE WHEN shkzg = 'S' THEN dmbtr ELSE 0 END ) AS debitval FROM mkpf INNER JOIN mseg ON mkpf.mandt = mseg.mandt AND mkpf.mblnr = mseg.mblnr AND mkpf.mjahr = mseg.mjahr AND mseg.mandt = :sy-mandt WHERE mseg.werks IN ( 'D070', 'D071' , 'H008' ) AND mkpf.budat <= '20070430' AND mkpf.budat >= '20070401' AND ( mseg.bwart = '551' OR mseg.bwart = '552' ) GROUP BY mseg.werks, mseg.matnr UNION SELECT 'D070' as werks, mseg.matnr, 0 as creditqty, 0 as debitqty, 0 as creditval, 0 as debitval FROM mkpf INNER JOIN mseg ON mkpf.mandt = mseg.mandt AND mkpf.mblnr = mseg.mblnr AND mkpf.mjahr = mseg.mjahr AND mseg.mandt = :sy-mandt WHERE mseg.werks IN ( 'D070', 'D071' , 'H008' ) AND mkpf.budat <= '20070430' AND mkpf.budat >= '20070401' AND ( mseg.bwart = '551' OR mseg.bwart = '552' ) GROUP BY mseg.matnr UNION SELECT 'D071' as werks, mseg.matnr, 0 as creditqty, 0 as debitqty, 0 as creditval, 0 as debitval FROM mkpf INNER JOIN mseg ON mkpf.mandt = mseg.mandt AND mkpf.mblnr = mseg.mblnr AND mkpf.mjahr = mseg.mjahr AND mseg.mandt = :sy-mandt WHERE mseg.werks IN ( 'D070', 'D071' , 'H008' ) AND mkpf.budat <= '20070430' AND mkpf.budat >= '20070401' AND ( mseg.bwart = '551' OR mseg.bwart = '552' ) GROUP BY mseg.matnr UNION SELECT 'H008' as werks, mseg.matnr, 0 as creditqty, 0 as debitqty, 0 as creditval, 0 as debitval FROM mkpf INNER JOIN mseg ON mkpf.mandt = mseg.mandt AND mkpf.mblnr = mseg.mblnr AND mkpf.mjahr = mseg.mjahr AND mseg.mandt = :sy-mandt WHERE mseg.werks IN ( 'D070', 'D071' , 'H008' ) AND mkpf.budat <= '20070430' AND mkpf.budat >= '20070401' AND ( mseg.bwart = '551' OR mseg.bwart = '552' ) GROUP BY mseg.matnr ) a, makt b WHERE a.matnr = b.matnr AND b.spras = :sy-langu AND b.mandt = :sy-mandt GROUP BY werks, a.matnr, maktx ORDER BY werks, a.matnr, maktx ------------------------------------------------------------------------------------------ BEGIN "StkVchr Analysis Dense Ulimate & With Hierarchy! werks werks_d matnr maktx menge menge_d dmbtr mercat char200 END SELECT werks, ilvw.matnr, maktx, sum((creditqty - debitqty)), sum((creditval - debitval)), tree.mercat FROM ( SELECT mseg.werks, mseg.matnr, SUM( CASE WHEN shkzg = 'H' THEN menge ELSE 0 END ) AS creditqty, SUM( CASE WHEN shkzg = 'S' THEN menge ELSE 0 END ) AS debitqty, SUM( CASE WHEN shkzg = 'H' THEN dmbtr ELSE 0 END ) AS creditval, SUM( CASE WHEN shkzg = 'S' THEN dmbtr ELSE 0 END ) AS debitval FROM mkpf INNER JOIN mseg ON mkpf.mandt = mseg.mandt AND mkpf.mblnr = mseg.mblnr AND mkpf.mjahr = mseg.mjahr AND mseg.mandt = :sy-mandt WHERE mseg.werks IN ( 'D070', 'D071' , 'H008' ) AND mkpf.budat <= '20070430' AND mkpf.budat >= '20070401' AND ( mseg.bwart = '551' OR mseg.bwart = '552' ) GROUP BY mseg.werks, mseg.matnr UNION SELECT 'D070' as werks, mseg.matnr, 0 as creditqty, 0 as debitqty, 0 as creditval, 0 as debitval FROM mkpf INNER JOIN mseg ON mkpf.mandt = mseg.mandt AND mkpf.mblnr = mseg.mblnr AND mkpf.mjahr = mseg.mjahr AND mseg.mandt = :sy-mandt WHERE mseg.werks IN ( 'D070', 'D071' , 'H008' ) AND mkpf.budat <= '20070430' AND mkpf.budat >= '20070401' AND ( mseg.bwart = '551' OR mseg.bwart = '552' ) GROUP BY mseg.matnr UNION SELECT 'D071' as werks, mseg.matnr, 0 as creditqty, 0 as debitqty, 0 as creditval, 0 as debitval FROM mkpf INNER JOIN mseg ON mkpf.mandt = mseg.mandt AND mkpf.mblnr = mseg.mblnr AND mkpf.mjahr = mseg.mjahr AND mseg.mandt = :sy-mandt WHERE mseg.werks IN ( 'D070', 'D071' , 'H008' ) AND mkpf.budat <= '20070430' AND mkpf.budat >= '20070401' AND ( mseg.bwart = '551' OR mseg.bwart = '552' ) GROUP BY mseg.matnr UNION SELECT 'H008' as werks, mseg.matnr, 0 as creditqty, 0 as debitqty, 0 as creditval, 0 as debitval FROM mkpf INNER JOIN mseg ON mkpf.mandt = mseg.mandt AND mkpf.mblnr = mseg.mblnr AND mkpf.mjahr = mseg.mjahr AND mseg.mandt = :sy-mandt WHERE mseg.werks IN ( 'D070', 'D071' , 'H008' ) AND mkpf.budat <= '20070430' AND mkpf.budat >= '20070401' AND ( mseg.bwart = '551' OR mseg.bwart = '552' ) GROUP BY mseg.matnr ) ilvw, ( SELECT child.class, SYS_CONNECT_BY_PATH (swor.kschl, '<-') mercat FROM kssk, swor, klah child, klah parent WHERE kssk.objek = swor.clint AND kssk.mandt = swor.mandt AND swor.spras = :sy-langu AND kssk.objek = child.clint AND kssk.mandt = child.mandt AND kssk.clint = parent.clint AND kssk.mandt = parent.mandt AND kssk.mandt = :sy-mandt START with kssk.clint = ( SELECT clint FROM klah WHERE class = '100000000' ) CONNECT BY PRIOR kssk.objek = kssk.clint ) tree, makt, mara WHERE ilvw.matnr = makt.matnr AND makt.spras = :sy-langu AND makt.mandt = :sy-mandt AND makt.mandt = mara.mandt AND makt.matnr = mara.matnr AND mara.matkl = tree.class GROUP BY werks, ilvw.matnr, maktx, tree.class ORDER BY werks, ilvw.matnr, maktx ------------------------------------------------------------------------------------------ begin class KLASSE_D mercat char200 end SELECT child.class, SYS_CONNECT_BY_PATH (swor.kschl, '<-') mercat FROM kssk, swor, klah child, klah parent WHERE kssk.objek = swor.clint AND kssk.mandt = swor.mandt AND swor.spras = :sy-langu AND kssk.objek = child.clint AND kssk.mandt = child.mandt AND kssk.clint = parent.clint AND kssk.mandt = parent.mandt AND kssk.mandt = :sy-mandt START with kssk.clint = ( SELECT clint FROM klah WHERE class = '100000000' ) CONNECT BY PRIOR kssk.objek = kssk.clint ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ Begin "Compare Header & Items Not Matching hdrkey char8 "SID werks werks_d fkdat fkdat "BillDate kbdnr w_kbdnr "Cashier kasnam w_kasnam "CshrName kasnr w_kasnr "POS-number bonnr w_bonnr "ReceiptNum hdrtot w_umsvpo "Payment itmsrl char2 "ItemSrlno matnr matnr itmtot w_umsvpo "ItemAmount end select h.hdrkey , h.werks, h.fkdat, h.kbdnr, h.kasnam, h.kasnr, h.bonnr, h.umsgvo as hdrtot, i.itmsrl, i.matnr, i.umsgvo as itmtot from zposhdr h, zpositm i where h.mandt = i.mandt and h.hdrkey = i.hdrkey and h.mandt = :sy-mandt and h.werks = 'H008' and h.hdrkey in ( select hdrkey from zposhdr a WHERE a.fkdat >= '20070505' AND a.werks = 'H008' AND abs ( umsgvo - ( select sum( umsgvo ) from zpositm b where b.hdrkey = a.hdrkey ) ) > 0.50 ) order by werks, fkdat, kbdnr, kasnr, bonnr, itmsrl ------------------------------------------------------------------------------------------ BEGIN "Compare DTL Payments Items Aggregated and Finance werks werks_d fkdat fkdat paytot w_umsvpo "PaymentTot itmtot w_umsvpo "ItemTotal aggtot w_umsvpo "AggrgatSls fintot w_umsvpo "FinPayment payaggdiff w_umsvpo "PAY-AGGR payfindiff w_umsvpo "PAY-FIN END SELECT pay.werks, pay.fkdat, pay.total as paytot, itm.total as itmtot, agg.total as aggtot, fin.total as fintot, pay.total - agg.total as payaggdiff, pay.total - fin.total as payfindiff FROM ( SELECT zposhdr.werks, zposhdr.fkdat, sum( zposhdr.umsgvo ) as total FROM zposhdr WHERE zposhdr.mandt = :sy-mandt GROUP BY zposhdr.werks, zposhdr.fkdat ) pay, ( SELECT zposhdr.werks, zposhdr.fkdat , sum( zpositm.umsgvo ) as total FROM zposhdr INNER JOIN zpositm ON zposhdr.mandt = zpositm.mandt AND zposhdr.hdrkey = zpositm.hdrkey AND zpositm.mandt = :sy-mandt WHERE zposhdr.mandt = :sy-mandt GROUP By zposhdr.werks, zposhdr.fkdat ) itm, ( SELECT vbrp.werks, vbrk.fkdat, sum( vbrp.netwr + vbrp.mwsbp ) as total FROM vbrp INNER JOIN vbrk ON vbrp.mandt = vbrk.mandt AND vbrp.vbeln = vbrk.vbeln AND vbrk.mandt = :sy-mandt WHERE vbrp.mandt = :sy-mandt AND vbrk.fksto <> 'X' GROUP BY vbrp.werks, vbrk.fkdat ) agg, ( SELECT werks, fkdat, sum(total) as total FROM ( SELECT kunnr as werks, budat as fkdat, dmbtr as total FROM bsid WHERE xblnr IN ( select vbeln from vbrp where matnr = 'DUMMY' ) UNION SELECT kunnr as werks, budat as fkdat, dmbtr as total FROM bsad WHERE xblnr IN ( select vbeln from vbrp where matnr = 'DUMMY' ) ) GROUP BY werks, fkdat ) fin WHERE pay.werks = itm.werks AND pay.fkdat = itm.fkdat AND pay.werks = agg.werks AND pay.fkdat = agg.fkdat AND pay.werks = fin.werks (+) AND pay.fkdat = fin.fkdat (+) ------------------------------------------------------------------------------------------ Begin "Compare Header & Items Not Matching hdrkey char8 "SID werks werks_d fkdat fkdat "BillDate kbdnr w_kbdnr "Cashier kasnam w_kasnam "CshrName kasnr w_kasnr "POS-number bonnr w_bonnr "ReceiptNum hdrtot w_umsvpo "Payment itmsrl char2 "ItemSrlno matnr matnr itmtot w_umsvpo "ItemAmount end select h.hdrkey, h.werks, h.fkdat, h.kbdnr, h.kasnam, h.kasnr, h.bonnr, h.umsgvo, i.itmsrl, i.matnr, i.umsgvo from zposhdr h, zpositm i where h.mandt = i.mandt and h.hdrkey = i.hdrkey and h.mandt = :sy-mandt and h.werks = 'H008' and h.hdrkey in ( select hdrkey from zposhdr a WHERE a.fkdat = '20070507' AND a.werks = 'H008' AND abs ( umsgvo - ( select sum( umsgvo ) from zpositm b where b.hdrkey = a.hdrkey ) ) > 0.50 ) ------------------------------------------------------------------------------------------ BEGIN "500 Production IDOCs in Error sndprn edi_sndprn credat edi_ccrdat idoctp edi_idoctp docnum edi_docnum status edi_status descrp edi_text60 direct edi_direct rcvpor edi_rcvpor rcvprn edi_rcvprn END SELECT i.sndprn, i.credat, i.idoctp, i.docnum, i.status, t.descrp, i.direct, i.rcvpor, i.rcvprn FROM edidc i, teds2 t WHERE i.mandt = :sy-mandt AND i.status NOT IN ('53', '73', '03', '16') AND i.status = t.status AND t.langua = 'E' AND ( i.sndprn in ( select werks from t001w ) OR i.rcvprn in ( select werks from t001w ) )