Экспорт из СМ2000 данных о продажах по типам оплаты : Супермаг Плюс (Супермаг 2000)

 
SELECT SUM(round(totalsumnotax, 2)),SUM(round(totalvat, 2)),10 FROM Supermag.FFSpec WHERE docdate='03.01.2009' AND (ndoc IN (SELECT ndoc FROM Supermag.FFDocuments WHERE locationfrom=5)) AND DocType = 'CS' AND vatrate=10
UNION
SELECT SUM(round(totalsumnotax, 2)),SUM(round(totalvat, 2)),18 FROM Supermag.FFSpec WHERE docdate='03.01.2009' AND (ndoc IN (SELECT ndoc FROM Supermag.FFDocuments WHERE locationfrom=5)) AND DocType = 'CS' AND vatrate=18
UNION
SELECT SUM(round(totalsumnotax, 2)),SUM(round(totalvat, 2)),0 FROM Supermag.FFSpec WHERE docdate='03.01.2009' AND (ndoc IN (SELECT ndoc FROM Supermag.FFDocuments WHERE locationfrom=5)) AND DocType = 'CS' AND vatrate=0

 
SUM(ROUND(TOTALSUMNOTAX,2)) SUM(ROUND(TOTALVAT,2))         10
--------------------------- ---------------------- ----------
                         80                      0          0
                   30160,08                3015,92         10
                   32815,92                5906,87         18

 
SELECT SUM(salesum) AS saletotal,SUM(salenotax) AS salenotax,SUM(salesum-salenotax) AS tax,SUM(primecostnovat) AS primecostnotax,10 AS vatrate FROM Supermag.FFMapRep WHERE saledate='03.01.2009' AND salelocationfrom=5 AND saletype='CS' AND salevatrate=10 AND rectype=1
UNION
SELECT SUM(salesum) AS saletotal,SUM(salenotax) AS salenotax,SUM(salesum-salenotax) AS tax,SUM(primecostnovat) AS primecostnotax,18 AS vatrate FROM Supermag.FFMapRep WHERE saledate='03.01.2009' AND salelocationfrom=5 AND saletype='CS' AND salevatrate=18 AND rectype=1
UNION
SELECT SUM(salesum) AS saletotal,SUM(salenotax) AS salenotax,SUM(salesum-salenotax) AS tax,SUM(primecostnovat) AS primecostnotax,0 AS vatrate FROM Supermag.FFMapRep WHERE saledate='03.01.2009' AND salelocationfrom=5 AND saletype='CS' AND salevatrate=0 AND rectype=1

 
 SALETOTAL  SALENOTAX        TAX PRIMECOSTNOTAX    VATRATE
---------- ---------- ---------- -------------- ----------
        80         80          0             80          0
     33176   30160,08    3015,92       25129,43         10
  38722,79   32815,92    5906,87       25958,94         18

INSERT INTO supermag.tttovreport
  (doctype, opcode, useropcode, paycash, taxgroupid, taxid, taxsum)
  SELECT m.saletype,
         m.saleop,
         NVL(m.saleuserop, - :"SYS_B_00"),
         m.salepaycash,
         a.taxgroupid,
         t.taxid,
         SUM(DECODE(m.saleq,
                    :"SYS_B_01",
                    :"SYS_B_02",
                    t.taxsum * m.quantity / m.saleq))
    FROM supermag.fvmaprep m, supermag.smcardtax a, supermag.smspectax t
   WHERE TO_DATE(:"SYS_B_03", :"SYS_B_04") BETWEEN a.datefrom AND a.dateto
     AND a.article = m.article
     AND NVL(m.salelocationfrom, m.salelocationto) = :"SYS_B_05"
     AND m.saledate BETWEEN TO_DATE(:"SYS_B_06", :"SYS_B_07") AND
         TO_DATE(:"SYS_B_08", :"SYS_B_09")
     AND m.saletype IN (:"SYS_B_10", :"SYS_B_11")
     AND m.saleop IN (:"SYS_B_12", :"SYS_B_13")
     AND t.doctype = m.saletype
     AND t.docid = m.saleid
     AND t.specitem = m.salespecitem
   GROUP BY m.saletype,
            m.saleop,
            NVL(m.saleuserop, - :"SYS_B_14"),
            m.salepaycash,
            a.taxgroupid,
            t.taxid

SELECT a.taxgroupid,
       DECODE(o.expensetype, :"SYS_B_00", :"SYS_B_01", :"SYS_B_02") oper_type,
       o.ID operid,
       NVL(u.ID, - :"SYS_B_03") useroperid,
       o.NAME || :"SYS_B_04" || u.title opername,
       m.saletype,
       m.salepaycash,
       SUM(DECODE(m.saleq,
                  :"SYS_B_05",
                  :"SYS_B_06",
                  m.salesum * m.quantity / m.saleq)) sum_full,
       SUM(DECODE(m.incomeq,
                  :"SYS_B_07",
                  :"SYS_B_08",
                  m.incomenovat * m.quantity / m.incomeq)) cp,
       SUM(DECODE(m.incomeq,
                  :"SYS_B_09",
                  :"SYS_B_10",
                  m.incomesum * m.quantity / m.incomeq)) cp_full
  FROM supermag.fvmaprep    m,
       supermag.smcardtax   a,
       supermag.saoperation o,
       supermag.smuserop    u
 WHERE o.ID IN (:"SYS_B_11", :"SYS_B_12")
   AND m.saleop = o.ID
   AND m.saleuserop = u.ID(+)
   AND m.saletype IN (:"SYS_B_13", :"SYS_B_14", :"SYS_B_15", :"SYS_B_16",
        :"SYS_B_17", :"SYS_B_18")
   AND m.saleop IN (:"SYS_B_19", :"SYS_B_20")
   AND m.saledate BETWEEN TO_DATE(:"SYS_B_21", :"SYS_B_22") AND
       TO_DATE(:"SYS_B_23", :"SYS_B_24")
   AND NVL(m.salelocationfrom, m.salelocationto) = :"SYS_B_25"
   AND a.article = m.article
   AND TO_DATE(:"SYS_B_26", :"SYS_B_27") BETWEEN a.datefrom AND a.dateto
 GROUP BY a.taxgroupid,
          DECODE(o.expensetype, :"SYS_B_28", :"SYS_B_29", :"SYS_B_30"),
          o.ID,
          NVL(u.ID, - :"SYS_B_31"),
          o.NAME || :"SYS_B_32" || u.title,
          m.saletype,
          m.salepaycash
 ORDER BY :"SYS_B_33" ASC,
          :"SYS_B_34" ASC,
          :"SYS_B_35" ASC,
          :"SYS_B_36" ASC,
          :"SYS_B_37" ASC,
          :"SYS_B_38" ASC,
          :"SYS_B_39" ASC