CREATE OR REPLACE PROCEDURE EXP_PROC (period VARCHAR2, s_gp VARCHAR2)
IS
query1 VARCHAR2(1700);
TYPE uslage IS REF CURSOR;
cur_strong uslage;
idsluch ut.id_sluch % TYPE;
idpacs ut.id_pac % TYPE;
mcod ut.lpu_1 % TYPE;
summv ut.SUMV_USL % TYPE;
idserv ut.idserv % TYPE;
BEGIN
query1 := 'SELECT distinct(u.id_sluch) as id_sluch, u.id_pac, u.lpu_1,
SUM(u.SUMV_USL) AS summv, MAX(u.idserv) AS idserv
FROM XML_USL PARTITION (u'||period||') u
INNER JOIN (SELECT ID_PAC, GLPU, DR
FROM XML_PACIENT PARTITION (p'||period||')
WHERE GLPU = ' || '''' || s_gp || ''' AND novor = ''' || 0 || ''') ps
ON u.id_pac = ps.id_pac AND u.LPU = ps.glpu
WHERE
SUBSTR(TRIM(u.CODE_USL), 1, 1) <> ''' || 5 || ''' AND
((SUBSTR(TRIM(u.CODE_USL), -1, 1) = ''' || 1 || ''' AND
months_between(u.DATE_IN, ps.DR) / 12 < 17.4)
OR
(SUBSTR(TRIM(u.CODE_USL), -1, 1) = ''' || 2 || ''' AND
months_between(u.DATE_IN, ps.DR) / 12 > 18.3)) AND
NOT EXISTS (SELECT s.ID_PAC, s.GLPU
FROM SANKC s
WHERE s.glpu = ' || '''' || s_gp || ''' AND s.period = ' || '''' || period || ''' AND
ps.id_pac = s.ID_PAC AND ps.GLPU = s.GLPU)
GROUP BY u.id_sluch, u.id_pac, u.lpu_1';
OPEN cur_strong FOR query1;
LOOP
FETCH cur_strong INTO idsluch, idpacs, mcod, summv, idserv;
EXIT WHEN cur_strong%NOTFOUND;
INSERT INTO SANKC (glpu, idpvd, flag, ID_PAC, id_sluch, idserv, DATE_EXP, period, comments, summ)
VALUES (s_gp, '514', 4, idpacs, idsluch, idserv, SYSDATE, period_date, 'Неправильный возраст', summv);
END LOOP;
CLOSE cur_strong;
COMMIT;
END;
/
