Код:
update smdiscpers set cardtype=4, lastchange=sysdate where code in
(SELECT b.code
FROM
SUPERMAG.SMCLIENTINFO F
, SUPERMAG.SMDISCPERS B
, SUPERMAG.SMDOCDISCCARD H
WHERE
(B.CLIENT = F.ID (+) )
AND (B.CODE = H.CODE)
AND (substr(b.code,1,3)='294' or substr(b.code,1,3)='2135')
AND (B.CARDTYPE=3)
GROUP BY
B.CODE
having SUM(case when h.doctype='CS' then H.TOTALSUM else -H.TOTALSUM end)>=30000);
commit;
update smdiscpers set cardtype=5, lastchange=sysdate where code in
(SELECT b.code
FROM
SUPERMAG.SMCLIENTINFO F
, SUPERMAG.SMDISCPERS B
, SUPERMAG.SMDOCDISCCARD H
WHERE
(B.CLIENT = F.ID (+) )
AND (B.CODE = H.CODE)
AND (substr(b.code,1,3)='294' or substr(b.code,1,3)='2135')
AND (B.CARDTYPE=4)
GROUP BY
B.CODE
having SUM(case when h.doctype='CS' then H.TOTALSUM else -H.TOTALSUM end)>=50000);
commit;
update smdiscpers set cardtype=4, lastchange=sysdate where code in
(select code from smdiscpers where client in
(SELECT b.client from
SUPERMAG.SMDISCPERS B
, SUPERMAG.SMDOCDISCCARD H
WHERE
(B.CODE = H.CODE)
AND ((substr(b.code,1,3)='294') or (substr(b.code,1,3)='2135'))
AND (B.CARDTYPE IN (3,4,5))
GROUP BY
B.client
having SUM(case when h.doctype='CS' then H.TOTALSUM else -H.TOTALSUM end)>=30000)) and ((substr(code,1,3)='294') or (substr(code,1,3)='2135'));
commit;
update smdiscpers set cardtype=5, lastchange=sysdate where code in
(select code from smdiscpers where client in
(SELECT b.client from
SUPERMAG.SMDISCPERS B
, SUPERMAG.SMDOCDISCCARD H
WHERE
(B.CODE = H.CODE)
AND ((substr(b.code,1,3)='294') or (substr(b.code,1,3)='2135'))
AND (B.CARDTYPE IN (3,4,5))
GROUP BY
B.client
having SUM(case when h.doctype='CS' then H.TOTALSUM else -H.TOTALSUM end)>=50000)) and ((substr(code,1,3)='294') or (substr(code,1,3)='2135'));
commit;
insert into smpostqueue(enqtime,enqseq,target,objtype,objid,paramint,paramstr,transflags,virtpack,commentary) select sysdate,rownum,null,'DC', code , null, null, 0,null, null from smdiscpers where to_char(lastchange,'DDMMYYYY')=to_char(sysdate,'DDMMYYYY');
commit;