--
-- maj_cnt_eve.sql:   maj contrats   =>   qte livrée qte commandée    (ENSEIGNE  ET PDL)
--                    maj cde        =>   N° contrats  dans numcnt  poste  pour contrats PDL
--                    maj liv        =>   N° contrats  dans numcnt  poste  pour contrats PDL  
--             --------------------------------------------
--
--              - Lecture de la table CNT/CNP
--
-- Ecrit le 02/12/2009
-- MODIF le 02/03/10 : ne plus mettre a jour contrat sur codpro(1,7) mais sur codpro stock
-- MODIF le 21/10/10 : ne plus tester qteini renseignée pour cas promo sans remontee de cde mais avec CDE
-- MODIF le 05/11/10 : optimisation requete lecture LIV et CDE
-- MODIF le 23/08/11 : réécriture requêtes pour diverses optimisation
-- MODIF le 30/08/11 : nouvelle conception : on traite les événements par lots et non ligne à ligne

set serveroutput on size 1000000;
spool q_maj_cntpromo;

declare
 	-- Variables
	V_CtrE          number := 0;
	V_Date          char(8) := ' ';
	V_num_cde       number := 0;
	V_num_liv       number := 0;
	V_CTR_maj       number := 0;
	V_num_cde_e     number := 0;
	V_num_liv_e     number := 0;
        V_start         date;
        V_end           date;
        V_qte           number := 0;
	
	V_codpro        char(9) := ' ';
	V_numens        varchar2(7) := ' ';

	-- Variables pour les execute immediate
	V_update_cnp    varchar2(4000);
	V_update_liv    varchar2(4000);
	V_update_cde    varchar2(4000);

	--  Variable des tables
	pru_coefuv      pru.coefuv%TYPE;                            

--     Mise a  jour du contrat enseigne
--     --------------------------------
procedure maj_cnt_ens
-- ---------------------
is
begin
	dbms_output.put_line('maj_cnt_ens(' || V_numens || ', ' || V_codpro || ')');

	-- recherche coefficient   contrat enseigne
	-- ----------------------------------------

        pru_coefuv := 1;
        begin
	        -- Optimisation 1 : Réécriture de la requête au format SQL-92
		select pru.coefuv
		into pru_coefuv
		from cnp
		inner join pru on pru.codsoc = cnp.codsoc and pru.codpro = cnp.codpro and pru.coduni = cnp.coduni
		inner join cnt on cnt.codsoc = cnp.codsoc and cnt.achvte = cnp.achvte and cnt.numcnt = cnp.numcnt and cnt.typeve = cnp.typeve and cnt.codzn4 ='ENS'
		where cnp.codsoc = 100
		and cnp.achvte = 'V'
		and cnp.typeve = 'CNT'
		and cnp.numcnt = V_numens
		and cnp.codpro = V_codpro
		and rownum = 1;
	exception
		when others then
			dbms_output.put_line('Err select pru produit ' || V_codpro);
	end;

        if pru_coefuv <> 0
	then         
		V_num_liv_e  := V_num_liv_e / pru_coefuv;            
		V_num_cde_e  := V_num_cde_e / pru_coefuv;
        end if;                                                      

	-- mise à jour  contrat enseigne
	-- -----------------------------

        begin
        	-- Optimisation 1 : Ajout de TQOI dans la requête : fait partie de la clé primaire
        	-- Optimisation 2 : Suppression de TYPEVE qui n'est pas dans l'index et inutile
        	-- Optimisation 3 : Utilisation de EXECUTE IMMEDIATE plutôt qu'une requête
		execute immediate V_update_cnp using V_date, V_num_cde_e, V_num_liv_e, V_numens, V_codpro;

		V_CTR_maj := V_CTR_maj + 1;

		if   V_CTR_maj > 100
		then
			commit;
			V_CTR_maj := 0;                          
		end if;                                                 
        exception
                when others then
                        dbms_output.put_line('Err update cnp ens ' || V_date || ', ' || V_num_cde_e || ', ' || V_num_liv_e || ', ' || V_numens || ', ' || V_codpro);
        end;                            
end;  

--    Traitement PRINCIPAL 
--    --------------------
        
begin
        select sysdate into V_start from dual;
        dbms_output.put_line('Début de la mise à jour à ' || to_char(V_start, 'HH24:MI:SS'));
	
	V_update_cnp := 'update cnp set utimod = ''GNC'', datmod = :1, qtecde = :2, qteliv = :3 where codsoc = 100 and achvte = ''V'' and tqoi = ''501'' and numcnt = :4 and codpro = :5';
	V_update_cde := 'update evt set codzn15 = :1, utimod = ''GNC'', datmod = :2 where (codsoc, achvte, typeve, numeve, numpos, numlig, numblo) in (select evp.codsoc, evp.achvte, evp.typeve, evp.numeve, evp.numpos, 0, 0 from eve inner join evp on evp.codsoc = eve.codsoc and evp.achvte = eve.achvte and evp.typeve = eve.typeve and evp.numeve = eve.numeve inner join pro on pro.codsoc = evp.codsoc and pro.codpro = evp.codpro where eve.codsoc = 100 and eve.achvte = ''V'' and eve.typeve = ''LIV'' and eve.typtie = ''CLI'' and eve.sigtie = :3 and eve.codeta in (''V'',''F'',''B'') and eve.dateve between :6 and :7 and pro.codzn15 = :8)';
	V_update_liv := 'update evt set codzn15 = :1, utimod = ''GNC'', datmod = :2 where (codsoc, achvte, typeve, numeve, numpos, numlig, numblo) in (select evp.codsoc, evp.achvte, evp.typeve, evp.numeve, evp.numpos, 0, 0 from eve inner join evp on evp.codsoc = eve.codsoc and evp.achvte = eve.achvte and evp.typeve = eve.typeve and evp.numeve = eve.numeve inner join pro on pro.codsoc = evp.codsoc and pro.codpro = evp.codpro where eve.codsoc = 100 and eve.achvte = ''V'' and eve.typeve = ''CDE'' and eve.typtie = ''CLI'' and eve.sigtie = :3 and eve.codeta != ''A'' and eve.datliv between :4 and :5 and pro.codzn15 = :6)';
	
	V_Date := TO_CHAR(SYSDATE,'YYYYMMDD');

	-- Recherche des contrats  +  coefficient de conversion produit cnp 
	-- ----------------------------------------------------------------

	For Row_cnt in (
		-- Optimisation 1 : Réécriture de la requête au format SQL-92
		-- Optimisation 2 : Utilisation un "in" à la place d'un "exists", car "in" est plus performant
		-- Optimisation 3 : On ne fait pas de cast sur cnt.datech (n fois) mais sur V_date (1 fois)
		-- Optimisation 4 : Ajout de cnp.tqoi dans la requête : index primaire sur CNP
		-- Optimisation 5 : Remplacement du EXISTS par une jointure INNER JOIN
		-- Optimisation 6 : Suppression de champs inutilement retournés par la requête
		-- Optimisations possibles :
		-- 1/ Création d'un index sur CNT (CODSOC, ACHVTE, TYPEVE, [CODZN4, CODZN5, DATREV])
		select c1.codsoc, c1.sigtie, c1.datapp, c1.datech, c1.numcnt, cnp.codpro, c1.codzn5, pru.coefuv
		from cnt c1
		inner join cnt c2 on c2.codsoc = c1.codsoc and c2.achvte = c1.achvte and c2.numcnt = c1.codzn5 and c2.codzn4 = 'ENS'
		inner join cnp on cnp.codsoc = c1.codsoc and cnp.achvte = c1.achvte and cnp.numcnt = c1.numcnt and cnp.tqoi = '501'
		inner join pru on pru.codsoc = cnp.codsoc and pru.codpro = cnp.codpro and pru.coduni = cnp.coduni
		where c1.codsoc = 100
		and c1.achvte = 'V'
		and c1.typeve = 'CNT'
		and c1.codzn4 = 'PDL'
		and c1.datrev <= V_Date
		and c1.datech >= to_char(to_date(V_Date, 'YYYYMMDD') - 15, 'YYYYMMDD')
		order by c1.codzn5, cnp.codpro
	)
	Loop        
		V_CtrE := V_CtrE + 1;

		-- test rupture num contrat enseigne-produit pour mise à jour contrat enseigne
		If V_numens != Row_cnt.codzn5 or V_codpro != Row_cnt.codpro
		Then
           		If V_numens != ' '
			Then
				maj_cnt_ens;
			End if;

	           	V_num_cde_e := 0;
			V_num_liv_e := 0;
			V_codpro    := Row_cnt.codpro;
			V_numens    := Row_cnt.codzn5;
		End if;

		V_num_cde    :=0;
		V_num_liv    :=0;           

		-- Recherche livraisons +  coefficient de conversion produit evp
		-- -------------------------------------------------------------  

		-- Optimisation 1 : Réécriture de la requête au format SQL-92
		-- Optimisation 2 : Sélection d'un SUM plutôt que ligne à ligne
		-- Optimisations possibles :
		-- 1/ Création d'un index sur EVE (CODSOC, ACHVTE, TYPEVE, TYPTIE, SIGTIE, CODETA, DATLIV)
		select sum(evp.qtecde * pru.coefuv)
		into V_qte
		from eve
		inner join evp on evp.codsoc = eve.codsoc and evp.achvte = eve.achvte and evp.typeve = eve.typeve and evp.numeve = eve.numeve
		inner join pro on pro.codsoc = evp.codsoc and pro.codpro = evp.codpro
		inner join pru on pru.codsoc = evp.codsoc and pru.codpro = pro.codpro and pru.coduni = evp.coduni
		where eve.codsoc = Row_cnt.codsoc
		and eve.achvte = 'V'
		and eve.typeve = 'LIV'
		and eve.typtie = 'CLI'
		and eve.sigtie = Row_cnt.sigtie
		and eve.codeta in ('V','F','B')
		and eve.dateve between Row_cnt.datapp and Row_cnt.datech
		and pro.codzn15 = Row_cnt.codpro;

		-- cumul qte livrée
		V_num_liv    := V_num_liv   +  V_qte;
		V_num_liv_e  := V_num_liv_e +  V_qte;

		-- maj evp  livraisons
		-- -------------------
		Begin
			dbms_output.put_line('maj_liv');
			-- Optimisation 1 : Utilisation de EXECUTE IMMEDIATE plutôt qu'une requête
			-- Optimisation 2 : Mise à jour de l'ensemble des lignes portant sur le contrat
			execute immediate V_update_liv using Row_cnt.numcnt, V_date, Row_cnt.sigtie, Row_cnt.datapp, Row_cnt.datech, Row_cnt.codpro;

			V_CTR_maj := V_CTR_maj + 1;
			
			if   V_CTR_maj > 100
			then
				commit;
				V_CTR_maj := 0;                          
			end if;                                                                 

			Exception
				When others Then
					dbms_output.put_line('Err update evt liv ' || Row_cnt.numcnt || ', ' || V_date || ', ' || Row_cnt.sigtie || ', ' || Row_cnt.datapp || ', ' || Row_cnt.datech || ', ' || Row_cnt.codpro);
		End;

		--     Recherche commandes
		--     -------------------

		-- Optimisation 1 : Réécriture de la requête au format SQL-92
		-- Optimisation 2 : Sélection d'un SUM plutôt que ligne à ligne
		-- Optimisations possibles :
		-- 1/ Création d'un index sur EVE (CODSOC, ACHVTE, TYPEVE, TYPTIE, SIGTIE, CODETA, DATLIV)
		select nvl(sum(decode(evt.codzn5, ' ', evp.qtecde, to_number(evt.codzn5)) * pru.coefuv), 0)
		into V_qte
		from eve
		inner join evp on evp.codsoc = eve.codsoc and evp.achvte = eve.achvte and evp.typeve = eve.typeve and evp.numeve = eve.numeve
		inner join evt on evt.codsoc = evp.codsoc and evt.achvte = evp.achvte and evt.typeve = evp.typeve and evt.numeve = evp.numeve and evt.numpos = evp.numpos and evt.numlig = 0
		inner join pro on pro.codsoc = evp.codsoc and pro.codpro = evp.codpro
		inner join pru on pru.codsoc = evp.codsoc and pru.codpro = pro.codpro and pru.coduni = evp.coduni
		where eve.codsoc = Row_cnt.codsoc
		and eve.achvte = 'V'
		and eve.typeve = 'CDE'
		and eve.typtie = 'CLI'
		and eve.sigtie = Row_cnt.sigtie
		and eve.codeta != 'A'
		and eve.datliv between Row_cnt.datapp and Row_cnt.datech
		and pro.codzn15 = Row_cnt.codpro;
		
		-- cumul qte commandée initiale
		V_num_cde    := V_num_cde   + V_qte;
		V_num_cde_e  := V_num_cde_e + V_qte;

		-- maj evp  commande
		-- -----------------
		
		Begin
			dbms_output.put_line('maj_cde');
			-- Optimisation 1 : Utilisation de EXECUTE IMMEDIATE plutôt qu'une requête
			-- Optimisation 2 : Mise à jour de l'ensemble des lignes portant sur le contrat
			execute immediate V_update_cde using Row_cnt.numcnt, V_date, Row_cnt.sigtie, Row_cnt.datapp, Row_cnt.datech, Row_cnt.codpro;

			V_CTR_maj := V_CTR_maj + 1;

			if   V_CTR_maj > 100
			then
				commit;
				V_CTR_maj := 0;                          
			end if;                                 
	
		Exception
			When others Then
					dbms_output.put_line('Err update evt cde ' || Row_cnt.numcnt || ', ' || V_date || ', ' || Row_cnt.sigtie || ', ' || Row_cnt.datapp || ', ' || Row_cnt.datech || ', ' || Row_cnt.codpro);
		End; 
		
		-- maj contrat
		-- -----------
		-- convertir quantité litres en uv du contrat

		if Row_cnt.coefuv != 0
		then
			V_num_cde := V_num_cde / Row_cnt.coefuv;
			V_num_liv := V_num_liv / Row_cnt.coefuv;
		end if; 

		Begin
			dbms_output.put_line('maj_cnt_pdl(' || Row_cnt.numcnt || ', ' || Row_cnt.codpro || ')');
			-- Optimisation 1 : On filtre sur tqoi (utilisation de la clé unique)
			-- Optimisation 2 : Utilisation de EXECUTE IMMEDIATE plutôt qu'une requête
			execute immediate V_update_cnp using V_date, V_num_cde, V_num_liv, Row_cnt.numcnt, Row_cnt.codpro;

			V_CTR_maj := V_CTR_maj + 1;

			if   V_CTR_maj > 100
			then
				commit;
				V_CTR_maj := 0;                          
			end if;                                                 

		Exception
			When others Then
				dbms_output.put_line('Err update CNP PDL ' || V_date || ', ' || V_num_cde || ', ' || V_num_liv || ', ' || Row_cnt.numcnt || ', ' || Row_cnt.codpro);
		End;                            
	End Loop;

	if V_numens != ' '
	then
		maj_cnt_ens;
	end if;
	
	commit;
	                                        
	dbms_output.put_line('Nombre d''entrées traitées : ' || V_CtrE);
        
        select sysdate into V_end from dual;
        dbms_output.put_line('Fin de la mise à jour à ' || to_char(V_end, 'HH24:MI:SS'));
        dbms_output.put_line('Durée du traitement (secondes) ' || (to_number(to_char(V_end, 'SSSSS')) - to_number(to_char(V_start, 'SSSSS'))));
	
EXCEPTION
	when INVALID_CURSOR then
		dbms_output.put_line('Erreur : ' || sqlerrm(sqlcode));
		raise_application_error(-20000, 'Erreur 4');
	when OTHERS then
		dbms_output.put_line('Erreur : ' || sqlerrm(sqlcode));
		raise_application_error(-20000, 'Erreur fqsdf');
END;
/
--exit