CREATE OR REPLACE PROCEDURE COUNT_TABLE IS
 
code_S varchar2(20);
libelle_S varchar2(30);
code_M varchar2(20);
libelle_M varchar2(30);
code_C varchar2(20);
libelle_C varchar2(30); 
le_sql VARCHAR2(5000);
extract_S varchar2(30);
extract_M varchar2(30);
extract_C varchar2(30);
comptage number;
BEGIN
-- on vide la table
execute immediate'TRUNCATE TABLE TABLE_COUNT';
 
SELECT cocod, colib1 INTO code_S, libelle_S
FROM code 
WHERE cocod='S';
 
SELECT nom INTO extract_S
FROM ext 
WHERE exnum ='RS';
 
 
SELECT cocod, colib1 INTO code_M, libelle_M
FROM code 
WHERE cocod='W';

SELECT nom INTO extract_M
FROM ext 
WHERE exnum ='RE';

SELECT cocod, colib1 INTO code_C, libelle_C
FROM code 
WHERE cocod='C';

SELECT nom INTO extract_C
FROM ext 
WHERE exnum ='NP';

-- Boucle sur les magasins
FOR c IN(SELECT decode(length(shop_id),2,'100'||shop_id,3,'10'||shop_id,1,'1000'||shop_id,4,'1'||shop_id) shop FROMcrm_shop WHERE country_id ='FR')
LOOP
 
BEGIN
le_sql:='select count(pers) from (select pers from idet, cart, part where cadad<>''21001231'' and camot= '' '' and fdpart.patvo like ''06%''and fdpart.pacl1=''Y'' and pers = capor and idpor = papor and idagr= '':1'' minus select expor from '||extract_S||')';
EXECUTE IMMEDIATE le_sql INTO comptage USING c.shop;
INSERT INTO TABLE_COUNT(shop_id, action_type, action_type_lab, people_number)VALUES(c.shop, code_S,libelle_S, comptage); 
COMMIT;
END;
 
BEGIN
le_sql:='select count(pers) from (select pers from idet, cart, part where cadad<>''21001231'' and camot= '' '' and FDPART.PAAEM<>'' '' and FDPART.PACL2 IN(''Y'', ''A'', ''T'') and pers = capor and idpor = papor and idagr= '':1'' minus select expor from '||extract_M||')';
EXECUTE IMMEDIATE le_sql INTO comptage USING c.shop;
INSERT INTO TABLE_COUNT(shop_id, action_type, action_type_lab, people_number)VALUES(c.shop, code_M,libelle_M, comptage); 
COMMIT;
END;

 
BEGIN
le_sql:='select count(pers) from (select pers from idet, cart where cadad<>''21001231'' and camot= '' '' and pers= capor and idagr= '':1'' minus select expor from '||extract_C||')';
EXECUTE IMMEDIATE le_sql INTO comptage USING c.shop;
INSERT INTO TABLE_COUNT(shop_id, action_type, action_type_lab, people_number)VALUES(c.shop, code_C,libelle_C, comptage); 
COMMIT;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/