OAV_2113_script.sql

-- Les 2 tables a modifier
-- table ADH1H43PF
select * from WWADHESF.ADH1H43PF order by c_dateeffetpalier_avant;

-- table ADH1PACPF palier cotisation
select * from wwadhesf.adh1pacpf order by f_idpret, c_dateeffetpalier;


-- la zone a alimenter est la colonne C_TAUX dans la table ADH1PACPF
-- Table echeancier
select * from WWADHESF.P1ACRDPF;

-- La table des mouvements adhésion :  WWADHESF.P0AMADPF
                    
-- contient F_idpret, C_DateCreation    
select * from WWADHESF.P0AMADPF; --la table des mou

-- jointure des deux tables par rapport à la clé packro (F_IDPRET) = H43KMV(X_IDHISTOMOUVEMENT)
SELECT F_IDPRET, pactau, X_IDHISTOMOUVEMENT    
FROM wwadhesf.adh1pacpf
left JOIN WWADHESF.ADH1H43PF
ON wwadhesf.adh1pacpf.F_IDPRET = WWADHESF.ADH1H43PF.X_IDHISTOMOUVEMENT;


-- DATE DE REFERENCE TARIFAIRE
select * from WWADHESF.t4pprtpf;


-- La colonne garantie  
select pacggi from  wwadhesf.adh1pacpf order by f_idpret, c_dateeffetpalier;
-- table P_IDGARGARANTIEINCLUSE                     
select * from WWANNEXF.pa1xggipf;


-- Les infos prets
 SELECT * from WWADHESF.t4pprtpf; 
 
 --PRTBAN = f_BANCODE zoned(5:0)
 -- pacggi = F_IDGARGARANTIEINCLUSE; 
 -- prtcie = F_CIECODE; 
 -- prtdrt = C_DATEREFERENCETARIFAIRE,
 -- pactau = C_TAUX
 select pacggi, prtcie, prtdrt, pactau, prtban, packro
from WWADHESF.adh1pacpf 
inner join WWADHESF.t4pprtpf on packro=prtkro
inner join WWADHESF.t4papepf on prtnum=apenum and prtord=apeord;
where pactau = 0;

-- recupérer le pays
         select f_idPays_taxe
          into :widPaysTaxe
         from   p1abqepf
         where  f_bancode = :w_prtban;
         select f_idpays_taxe from WWADHESP.p1abqepf;
         
         --adh00006
         
-- banque retrouver         

-- MADKMV     CONDEN      11  0 = P_IDHISTOMOUVEMENT
-- H43KMV     CONDEN      11  0 = X_IDHISTOMOUVEMENT
-- H43GGI     CONDEN       5  0 = F_IDGARGARANTIEINCLUSE 
-- PRTBAN     CONDEN       5  0 = F_BANCODE
-- PRTCIE     CONDEN       3  0 = F_CIECODE
-- PRTDRT     DATE           10 = C_DATEREFERENCETARIFAIRE
select prtcie, prtban, h43ggi, prtdrt, h43tau, packro from wwadhesf.adh1h43pf
inner join WWADHESF.p0amadpf on h43kmv=madkmv
inner join WWADHESF.t4pprtpf on madkro=prtkro;
where h43tau is null;

-- Historique chgt palier cotisations
select * from WWADHESF.adh1h43pf;
-- Historique des mouvements adhesion
select * from WWADHESF.p0amadpf;
-- Prêts Adhésion Appel de Cotisations
select * from WWADHESF.t4pprtpf;

CREATE TABLE milfort.ADH1H43PF(                                                     
		C_dateModification FOR H43DMO TIMESTAMP NOT NULL                              
            GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
		C_montantPalier_Avant FOR H43MPB DECIMAL(11, 2) NOT NULL,                     
		C_dateEffetPalier_Avant FOR H43DEB DATE NOT NULL,                             
		P_numeroLigne FOR H43NLI DECIMAL(3, 0) NOT NULL,                              
		X_idHistoMouvement FOR H43KMV DECIMAL(11, 0) NOT NULL,                        
		F_idGarGarantieIncluse FOR H43GGI DECIMAL(5, 0) NOT NULL,                     
                C_taux FOR H43TAU DECIMAL(5, 3),                                
		C_montantTaxe FOR H43MTX DECIMAL(11, 2),                                      
		C_montantHt FOR H43MHT DECIMAL(11, 2),                                        
		F_profil FOR H43PRO CHAR(10)                                                  
	);                                                                             
                                                                                
    LABEL ON TABLE milfort.ADH1H43PF IS                                                 
    'Historique chgt palier cotisation';                                        
                                                                                
    LABEL ON COLUMN milfort.ADH1H43PF.C_dateModification IS                             
    'Date de dernière modification';                                            
    LABEL ON COLUMN milfort.ADH1H43PF.C_dateModification TEXT IS                        
    'Date de dernière modification';	                                           
    LABEL ON COLUMN milfort.ADH1H43PF.C_montantPalier_Avant IS                          
    'Montant TTC du palier avant';                                              
    LABEL ON COLUMN milfort.ADH1H43PF.C_montantPalier_Avant TEXT IS                     
    'Montant TTC du palier avant';	                                             
    LABEL ON COLUMN milfort.ADH1H43PF.C_dateEffetPalier_Avant IS                        
    'Date d''effet du palier avant';                                            
    LABEL ON COLUMN milfort.ADH1H43PF.C_dateEffetPalier_Avant TEXT IS                   
    'Date d''effet du palier avant';	                                           
    LABEL ON COLUMN milfort.ADH1H43PF.P_numeroLigne IS                                  
    'Numéro de ligne';                                                          
    LABEL ON COLUMN milfort.ADH1H43PF.P_numeroLigne TEXT IS                             
    'Numéro de ligne';                                                          
    LABEL ON COLUMN milfort.ADH1H43PF.X_idHistoMouvement IS                             
    'Krono mouvement -> HistoMvtAdhesion (P0AMADPF)';                           
    LABEL ON COLUMN milfort.ADH1H43PF.X_idHistoMouvement TEXT IS                        
    'Krono mouvement -> HistoMvtAdhesion (P0AMADPF)';	                          
    LABEL ON COLUMN milfort.ADH1H43PF.F_idGarGarantieIncluse IS                         
    'Identifiant -> GarantieDetaillee (PA1XGGIPF)';                             
    LABEL ON COLUMN milfort.ADH1H43PF.F_idGarGarantieIncluse TEXT IS                    
    'Identifiant -> GarantieDetaillee (PA1XGGIPF)';                             
    LABEL ON COLUMN milfort.ADH1H43PF.C_taux IS                                         
    'Taux par garantie';                                                        
    LABEL ON COLUMN milfort.ADH1H43PF.C_taux TEXT IS                                    
    'Taux par garantie';	                                                       
    LABEL ON COLUMN milfort.ADH1H43PF.C_montantTaxe IS                                  
    'Montant de la taxe';                                                       
    LABEL ON COLUMN milfort.ADH1H43PF.C_montantTaxe TEXT IS                             
    'Montant de la taxe';	                                                      
    LABEL ON COLUMN milfort.ADH1H43PF.C_montantHt IS                                    
    'Montant HT du palier';                                                     
    LABEL ON COLUMN milfort.ADH1H43PF.C_montantHt TEXT IS                               
    'Montant HT du palier';	                                                    
    LABEL ON COLUMN milfort.ADH1H43PF.F_profil IS                                       
    'Id utilisateur -> UserProfil (T4PUSR52)';                                  
    LABEL ON COLUMN milfort.ADH1H43PF.F_profil TEXT IS                                  
    'Id utilisateur -> UserProfil (T4PUSR52)';

	ALTER TABLE milfort.ADH1H43PF ADD CONSTRAINT PK_ADH1H43PF                           
    PRIMARY KEY(P_numeroLigne,X_idHistoMouvement); 
    
    	ALTER TABLE milfort.ADH1H43PF                                                         
    FOREIGN KEY FK_ADH1H43PF02 (F_idGarGarantieIncluse)                         
    REFERENCES PA1XGGIPF(P_idGarGarantieIncluse);      
    
    --//---------------------------------------------------------
    
    CREATE TABLE milfort.ADH1PACPF(
	P_idPalierCotisation FOR PACPAC DECIMAL(11, 0) NOT NULL,
	C_montantPalier FOR PACMPA DECIMAL(11, 2),
	C_dateEffetPalier FOR PACDEP DATE NOT NULL,
    C_dateModification FOR PACDMO TIMESTAMP NOT NULL
        GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
	F_idPret FOR PACKRO DECIMAL(11, 0) NOT NULL,
	F_idGarGarantieIncluse FOR PACGGI DECIMAL(5, 0) NOT NULL,
        C_taux FOR PACTAU DECIMAL(5, 3),
	C_montantTaxe FOR PACMTX DECIMAL(11, 2),
	C_montantHt FOR PACMHT DECIMAL(11, 2),
	F_profil FOR PACPRO CHAR(10)
	);

    LABEL ON TABLE milfort.ADH1PACPF IS
    'Palier de cotisation';
    LABEL ON COLUMN milfort.ADH1PACPF.P_idPalierCotisation IS
    'Identifiant';
    LABEL ON COLUMN milfort.ADH1PACPF.P_idPalierCotisation TEXT IS
    'Identifiant';	
    LABEL ON COLUMN milfort.ADH1PACPF.C_montantPalier IS
    'Montant TTC du palier';
    LABEL ON COLUMN milfort.ADH1PACPF.C_montantPalier TEXT IS
    'Montant TTC du palier';	
    LABEL ON COLUMN milfort.ADH1PACPF.C_dateEffetPalier IS
    'Date d''effet du palier';
    LABEL ON COLUMN milfort.ADH1PACPF.C_dateEffetPalier TEXT IS
    'Date d''effet du palier';	
    LABEL ON COLUMN milfort.ADH1PACPF.C_dateModification IS
    'Date de dernière modification';
    LABEL ON COLUMN milfort.ADH1PACPF.C_dateModification TEXT IS
    'Date de dernière modification';	
    LABEL ON COLUMN milfort.ADH1PACPF.F_idPret IS
    'Identifiant -> PretAssure (T4PPRTPF)';
    LABEL ON COLUMN milfort.ADH1PACPF.F_idPret TEXT IS
    'Identifiant -> PretAssure (T4PPRTPF)';	
    LABEL ON COLUMN milfort.ADH1PACPF.F_idGarGarantieIncluse IS
    'Identifiant -> GarantieDetaillee (PA1XGGIPF)';
    LABEL ON COLUMN milfort.ADH1PACPF.F_idGarGarantieIncluse TEXT IS
    'Identifiant -> GarantieDetaillee (PA1XGGIPF)';	
    LABEL ON COLUMN milfort.ADH1PACPF.C_taux IS
    'Taux par garantie';
    LABEL ON COLUMN milfort.ADH1PACPF.C_taux TEXT IS
    'Taux par garantie';
    LABEL ON COLUMN milfort.ADH1PACPF.C_montantTaxe IS
    'Montant de la taxe';
    LABEL ON COLUMN milfort.ADH1PACPF.C_montantTaxe TEXT IS
    'Montant de la taxe';	
    LABEL ON COLUMN milfort.ADH1PACPF.C_montantHt IS
    'Montant HT du palier';
    LABEL ON COLUMN milfort.ADH1PACPF.C_montantHt TEXT IS
    'Montant HT du palier';
    LABEL ON COLUMN milfort.ADH1PACPF.F_profil IS
    'Id utilisateur -> UserProfil (T4PUSR52)';
    LABEL ON COLUMN milfort.ADH1PACPF.F_profil TEXT IS
    'Id utilisateur -> UserProfil (T4PUSR52)';

   	ALTER TABLE milfort.ADH1PACPF ADD CONSTRAINT PK_ADH1PACPF
    PRIMARY KEY(P_idPalierCotisation); 
    
    --cursor
    select h43ggi, h43kmv, h43tau
from wwadhesf.adh1h43pf
where h43tau is not null
order by h43kmv; 
-- select h43ggi, h43kmv
-- from adh1h43pf
-- where h43tau is null
-- order by hk3kmv 



 --- visualisation fichier log
 
SELECT * FROM wwadhesf.adh1logpf WHERE date(logdhe)='2020-05-28' ORDER BY
logdhe desc;
-- fichier a1ilog
SELECT * FROM BIBCOMMU.a1ilogpf WHERE date(logdhe)='2020-05-27' ORDER BY
logdhe desc;                                                       

-- tester la première ligne
update WWADHESF.adh1h43pf set c_taux = null where c_taux is not null;
commit;

--tester pour pac
update WWADHESF.adh1pacpf set c_taux = null where c_taux is not null;
commit;

-- verifier taux de taxe en sortie de m_gettauxtaxe en fonction du code pays, paysban et la garantie
select * from wwannexf.t4ptaxpf
where x_ciecode = 25
and f_idpays = 71
and taxgar = 15;

-- curseur h43
select h43ggi, h43kmv
from WWADHESF.adh1h43pf
where h43tau is null
order by h43kmv
for update with nc;

-- curseur pac
select pacggi, packro
from WWADHESF.adh1pacpf
where pactau is null
order by packro
for update with nc;

select prtban
            from  WWADHESF.p0amadpf
            inner join WWADHESF.t4pprtpf on madkro=prtkro
            where madkmv = packro;
            
            select prtban
            from  WWADHESF.p0amadpf
            inner join WWADHESF.t4pprtpf on madkro=prtkro
            where madkmv = h43kmv;
            
            -- recupérer prtcie prtdrt
            select prtcie, prtdrt, prtban
            from  WWADHESF.p0amadpf
            inner join WWADHESF.t4pprtpf on madkro=prtkro
            where madkmv = 4867337;
            
            --recherche prtban
            select prtban
            from  WWADHESF.p0amadpf
            inner join WWADHESF.t4pprtpf on madkro=prtkro
            where madkmv = 4295351;  
            
            
            select * from WWADHESF.adh1h43pf;
            select * from WWADHESF.adh1pacpf;
            
            --  error pret prtcie, prtdrt et prtban vides
            select prtcie, prtdrt, prtban
            from  WWADHESF.p0amadpf
            inner join WWADHESF.t4pprtpf on madkro=prtkro
            where madkmv = 36730484 ;
            -- erreur OAV - Taxe - QPADEV000L - MOUH43 - 000279 - (liste Programme :/MOUH43) : Erreur - Pret   -wh43kmv = 36730484                          
            
            -- zones à vides
            --OAV - Archivage - QPADEV000L - MOUPAC - 000286 - (liste Programme :/MOUPAC) : Erreur - Pret   -wpackro = 0  
             select prtcie, prtdrt, prtban
            from  WWADHESF.p0amadpf
            inner join WWADHESF.t4pprtpf on madkro=prtkro
            where madkmv = 0;                                                                     
           
           select count(*) from WWADHESF.adh1pacpf;