REMARK
\
		--------------------------------------------------------------------------------------------------------------------------------------------------
		------------ --------------------------------  *** PAIE *** --------------------------------------------------------------------------------------
		------------              #4086 Création de grades (dans la GP ces mêmes code grades sont aussi créés plus bas cf. #3701)
		--------------------------------------------------------------------------------------------------------------------------------------------------
/

Delete From PAGRADE Where IND = '_Afi$';
Insert Into PAGRADE (IND, LIBELLE) Values ('_Afi$', 'Tempo AFI');

REMARK
\
		PAIE : Nouveau cadre d'emploi : ASSISTANTS TERRITORIAUX DE CONSERVATION DU PATRIMOINE ET DES BIBLIOTHEQUES
		Décret n° 2011-1642 du 23/11/2011 date d'effet 1er décembre 2011
		------------------------------------------------------------------------
		Grades :  			 'AC_0'  'Assistants conservation'
									   'AC_1'  'Assistants conserva. princ. 2eme classe'
										 'AC_2', 'Assistants conserva. princ. 1ere classe'
/
Insert Into PAGRADE (IND, LIBELLE)
       Select 'AC_0', 'Assistants conservation' From PAGRADE
       Where IND = '_Afi$' And NOT EXISTS (SELECT IND FROM PAGRADE WHERE IND = 'AC_0');
Insert Into PAGRADE (IND, LIBELLE)
       Select 'AC_1', 'Assistants conserva. princ. 2eme classe' From PAGRADE
       Where IND = '_Afi$' And NOT EXISTS (SELECT IND FROM PAGRADE WHERE IND = 'AC_1');
Insert Into PAGRADE (IND, LIBELLE)
       Select 'AC_2', 'Assistants conserva. princ. 1ere classe' From PAGRADE
       Where IND = '_Afi$' And NOT EXISTS (SELECT IND FROM PAGRADE WHERE IND = 'AC_2');

Delete From PAGRADE Where IND = '_Afi$';



REMARK
\
		--------------------------------------------------------------------------------------------------------------------------------------------------
		------------ --------------------------------  *** GP *** ----------------------------------------------------------------------------------------
		--------------------------------------------------------------------------------------------------------------------------------------------------
/

REMARK
\
-- ------------  #3701 Nouveau cadre emploi : ASSISTANTS TERRITORIAUX DE CONSERVATION DU PATRIMOINE ET DES BIBLIOTHEQUES -----------------------------
Décret n° 2011-1642 du 23/11/2011 date d'effet 1er décembre 2011

Reclassement des assistants qualifiés de conservation du patrimoine et des bibliothèques dans ce nouveau cadre d'emploi.

		Cadre d'emploi : 'AC_PB'   'Assistants conservation patrimoine et bibliotheques'
		Grades :  			 'AC_0'  'Assistants conservation'
									   'AC_1'  'Assistants conserva. princ. 2eme classe'
										 'AC_2', 'Assistants conserva. princ. 1ere classe'
		Grilles : 'AC_0', 'AC_1', 'AC_2'
  	Conditions d'avancement de grade : 'AC_1', 'AC_2', 'AC_3', 'AC_4'
-- ---------------------------------------------------------------------------------------------------------------------------------------------------
/

Delete From GPCADREMP Where CDCAEMP = '_Afi$';
Insert into GPCADREMP (CDCAEMP, CDFILIERE, LIBCAEMP, OBSERVATION) Values ('_Afi$', '3', 'Tempo AFI', NULL);

Insert into GPCADREMP (CDCAEMP, CDFILIERE, LIBCAEMP, OBSERVATION)
   Select 'AC_PB', '3', 'Assistants conservation patrimoine et bibliotheques', '2011-1642 du 23 novembre 2011'
   From GPCADREMP Where CDCAEMP = '_Afi$' And NOT EXISTS (SELECT CDCAEMP FROM GPCADREMP WHERE CDCAEMP = 'AC_PB');

Delete From GPCADREMP Where CDCAEMP = '_Afi$';


Delete From GPGRILLE Where ECHELLE = '_Afi$';
Insert Into GPGRILLE (ECHELLE, OBS) Values ('_Afi$', 'Tempo AFI');

Insert Into GPGRILLE (ECHELLE,
   ECHELON1,BRUT1,MAJORE1,MINI1,MAXI1,
   ECHELON2,BRUT2,MAJORE2,MINI2,MAXI2,
   ECHELON3,BRUT3,MAJORE3,MINI3,MAXI3,
   ECHELON4,BRUT4,MAJORE4,MINI4,MAXI4,
   ECHELON5,BRUT5,MAJORE5,MINI5,MAXI5,
   ECHELON6,BRUT6,MAJORE6,MINI6,MAXI6,
   ECHELON7,BRUT7,MAJORE7,MINI7,MAXI7,
   ECHELON8,BRUT8,MAJORE8,MINI8,MAXI8,
   ECHELON9,BRUT9,MAJORE9,MINI9,MAXI9,
   ECHELON10,BRUT10,MAJORE10,MINI10,MAXI10,
   ECHELON11,BRUT11,MAJORE11,MINI11,MAXI11,
   ECHELON12,BRUT12,MAJORE12,MINI12,MAXI12,
   ECHELON13,BRUT13,MAJORE13,MINI13,MAXI13,
   ECHELON14,BRUT14,MAJORE14,MINI14,MAXI14,
   OBS)
   Select 'AC_0',
   		1,  325, NULL, 12, 12,
   		2,  333, NULL, 24, 24,
   		3,  347, NULL, 24, 24,
   		4,  359, NULL, 24, 24,
   		5,  374, NULL, 31, 36,
   		6,  393, NULL, 31, 36,
   		7,  418, NULL, 31, 36,
   		8,  436, NULL, 31, 36,
   		9,  457, NULL, 31, 36,
   		10, 486, NULL, 31, 36,
   		11, 516, NULL, 39, 48,
   		12, 548, NULL, 39, 48,
   		13, 576, NULL, 0,  0 ,
   		14,NULL,NULL,NULL,NULL,
   		'Décret 2011-1642 du 23/11/2011'
   From GPGRILLE Where ECHELLE = '_Afi$' And NOT EXISTS (SELECT ECHELLE FROM GPGRILLE WHERE ECHELLE = 'AC_0');

Insert Into GPGRILLE (ECHELLE,
   ECHELON1,BRUT1,MAJORE1,MINI1,MAXI1,
   ECHELON2,BRUT2,MAJORE2,MINI2,MAXI2,
   ECHELON3,BRUT3,MAJORE3,MINI3,MAXI3,
   ECHELON4,BRUT4,MAJORE4,MINI4,MAXI4,
   ECHELON5,BRUT5,MAJORE5,MINI5,MAXI5,
   ECHELON6,BRUT6,MAJORE6,MINI6,MAXI6,
   ECHELON7,BRUT7,MAJORE7,MINI7,MAXI7,
   ECHELON8,BRUT8,MAJORE8,MINI8,MAXI8,
   ECHELON9,BRUT9,MAJORE9,MINI9,MAXI9,
   ECHELON10,BRUT10,MAJORE10,MINI10,MAXI10,
   ECHELON11,BRUT11,MAJORE11,MINI11,MAXI11,
   ECHELON12,BRUT12,MAJORE12,MINI12,MAXI12,
   ECHELON13,BRUT13,MAJORE13,MINI13,MAXI13,
   ECHELON14,BRUT14,MAJORE14,MINI14,MAXI14,
   OBS)
   Select 'AC_1',
   		1,  350, NULL, 12, 12,
   		2,  357, NULL, 24, 24,
   		3,  367, NULL, 24, 24,
   		4,  378, NULL, 24, 24,
   		5,  397, NULL, 31, 36,
   		6,  422, NULL, 31, 36,
   		7,  444, NULL, 31, 36,
   		8,  463, NULL, 31, 36,
   		9,  493, NULL, 31, 36,
   		10, 518, NULL, 31, 36,
   		11, 551, NULL, 39, 48,
   		12, 581, NULL, 39, 48,
   		13, 614, NULL, 0,  0 ,
   		14,NULL,NULL,NULL,NULL,
   		'Décret 2011-1642 du 23/11/2011'
   From GPGRILLE Where ECHELLE = '_Afi$' And NOT EXISTS (SELECT ECHELLE FROM GPGRILLE WHERE ECHELLE = 'AC_1');

Insert Into GPGRILLE (ECHELLE,
   ECHELON1,BRUT1,MAJORE1,MINI1,MAXI1,
   ECHELON2,BRUT2,MAJORE2,MINI2,MAXI2,
   ECHELON3,BRUT3,MAJORE3,MINI3,MAXI3,
   ECHELON4,BRUT4,MAJORE4,MINI4,MAXI4,
   ECHELON5,BRUT5,MAJORE5,MINI5,MAXI5,
   ECHELON6,BRUT6,MAJORE6,MINI6,MAXI6,
   ECHELON7,BRUT7,MAJORE7,MINI7,MAXI7,
   ECHELON8,BRUT8,MAJORE8,MINI8,MAXI8,
   ECHELON9,BRUT9,MAJORE9,MINI9,MAXI9,
   ECHELON10,BRUT10,MAJORE10,MINI10,MAXI10,
   ECHELON11,BRUT11,MAJORE11,MINI11,MAXI11,
   ECHELON12,BRUT12,MAJORE12,MINI12,MAXI12,
   ECHELON13,BRUT13,MAJORE13,MINI13,MAXI13,
   ECHELON14,BRUT14,MAJORE14,MINI14,MAXI14,
   OBS)
   Select 'AC_2',
   		1, 404, NULL, 12, 12,
   		2, 430, NULL, 20, 24,
   		3, 450, NULL, 20, 24,
   		4, 469, NULL, 20, 24,
   		5, 497, NULL, 20, 24,
   		6, 524, NULL, 20, 24,
   		7, 555, NULL, 29, 36,
   		8, 585, NULL, 29, 36,
   		9, 619, NULL, 29, 36,
   		10,646, NULL, 29, 36,
   		11,675, NULL, 0,  0 ,
   		12,NULL,NULL,NULL,NULL,
   		13,NULL,NULL,NULL,NULL,
   		14,NULL,NULL,NULL,NULL,
   		'Décret 2011-1642 du 23/11/2011'
   From GPGRILLE Where ECHELLE = '_Afi$' And NOT EXISTS (SELECT ECHELLE FROM GPGRILLE WHERE ECHELLE = 'AC_2');

Delete From GPGRILLE Where ECHELLE = '_Afi$';


Delete From GPGRADE Where CDGRADE = '_Afi$';
Insert Into GPGRADE (CDGRADE, CDCAEMP, LBGRADE, CDECHEIND, LBECHELLE, CATEG, POSIT) Values ('_Afi$', 'E_A', 'Tempo AFI', NULL, NULL, NULL, NULL);

Insert Into GPGRADE (CDGRADE, CDCAEMP, LBGRADE, CDECHEIND, LBECHELLE, CATEG, POSIT)
       Select 'AC_0', 'AC_PB', 'Assistants conservation', 'AC_0', NULL, 'B', 1 From GPGRADE
       Where CDGRADE = '_Afi$' And NOT EXISTS (SELECT CDGRADE FROM GPGRADE WHERE CDGRADE = 'AC_0');
Insert Into GPGRADE (CDGRADE, CDCAEMP, LBGRADE, CDECHEIND, LBECHELLE, CATEG, POSIT)
       Select 'AC_1', 'AC_PB', 'Assistants conserva. princ. 2eme classe', 'AC_1', NULL, 'B', 2 From GPGRADE
       Where CDGRADE = '_Afi$' And NOT EXISTS (SELECT CDGRADE FROM GPGRADE WHERE CDGRADE = 'AC_1');
Insert Into GPGRADE (CDGRADE, CDCAEMP, LBGRADE, CDECHEIND, LBECHELLE, CATEG, POSIT)
       Select 'AC_2', 'AC_PB', 'Assistants conserva. princ. 1ere classe', 'AC_2', NULL, 'B', 3 From GPGRADE
       Where CDGRADE = '_Afi$' And NOT EXISTS (SELECT CDGRADE FROM GPGRADE WHERE CDGRADE = 'AC_2');

Delete From GPGRADE Where CDGRADE = '_Afi$';


Delete From GPAVGRADE Where CODE = '_Afi$';
Insert Into GPAVGRADE (CODE, GRADE_N, GRADE_A, ECHELON, ANC_GRADE, ANC_ECH, ANC_CADRE, AGE_MINI, AGE_MAXI, RECLA, PROMOTION, EXAMEN)
       Values ('_Afi$', '_Afi$', '_Afi$', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

Insert Into GPAVGRADE (CODE, GRADE_N, GRADE_A, ECHELON, ANC_GRADE, ANC_ECH, ANC_CADRE, AGE_MINI, AGE_MAXI, RECLA, PROMOTION, EXAMEN)
       Select 'AC_1', 'AC_1', 'AC_0', 4, 0, 12, 36, 0, 0, 0, 0, 1 From GPAVGRADE
       Where CODE = '_Afi$' And NOT EXISTS (SELECT CODE FROM GPAVGRADE WHERE CODE = 'AC_1');

Insert Into GPAVGRADE (CODE, GRADE_N, GRADE_A, ECHELON, ANC_GRADE, ANC_ECH, ANC_CADRE, AGE_MINI, AGE_MAXI, RECLA, PROMOTION, EXAMEN)
       Select 'AC_2', 'AC_1', 'AC_0', 6, 0, 12, 60, 0, 0, 0, 0, 0 From GPAVGRADE
       Where CODE = '_Afi$' And NOT EXISTS (SELECT CODE FROM GPAVGRADE WHERE CODE = 'AC_2');

       
Insert Into GPAVGRADE (CODE, GRADE_N, GRADE_A, ECHELON, ANC_GRADE, ANC_ECH, ANC_CADRE, AGE_MINI, AGE_MAXI, RECLA, PROMOTION, EXAMEN)
       Select 'AC_3', 'AC_2', 'AC_1', 5, 0, 24, 36, 0, 0, 0, 0, 1 From GPAVGRADE
       Where CODE = '_Afi$' And NOT EXISTS (SELECT CODE FROM GPAVGRADE WHERE CODE = 'AC_3');

Insert Into GPAVGRADE (CODE, GRADE_N, GRADE_A, ECHELON, ANC_GRADE, ANC_ECH, ANC_CADRE, AGE_MINI, AGE_MAXI, RECLA, PROMOTION, EXAMEN)
       Select 'AC_4', 'AC_2', 'AC_1', 6, 0, 12, 60, 0, 0, 0, 0, 0 From GPAVGRADE
       Where CODE = '_Afi$' And NOT EXISTS (SELECT CODE FROM GPAVGRADE WHERE CODE = 'AC_4');

Delete From GPAVGRADE Where CODE = '_Afi$';



REMARK
\
-- ------------------------ #4087 Réaffectation BRUT MAJORE DANS LES GRILLES pour les 2 derniers échelons (10 et 11) ---------------------------------
-- Pour échelon 10 : Anciennes valeurs Brut 640  Majoré 535    Nouvelles valeurs Brut 646  Majoré 540
-- Pour échelon 11 : Anciennes valeurs Brut 660  Majoré 551		 Nouvelles valeurs Brut 675  Majoré 562
-- ---------------------------------------------------------------------------------------------------------------------------------------------------
/
UPDATE GPGRILLE SET BRUT10 = 646 WHERE ECHELLE IN ( 'TT_T1', 'E_A2', 'AN_T2', 'CP_M2') AND BRUT10 = 640;
UPDATE GPGRILLE SET BRUT11 = 675 WHERE ECHELLE IN ( 'TT_T1', 'E_A2', 'AN_T2', 'CP_M2') AND BRUT11 = 660;

COMMIT;



REMARK
\
	BUT: Actualisation automatique de l'indice majoré des grilles de la GP à partir de la table de référence en cours INDBRUTMAJ
/
Update GPGRILLE Set MAJORE1 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT1  );
Update GPGRILLE Set MAJORE2 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT2 );
Update GPGRILLE Set MAJORE3 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT3 );
Update GPGRILLE Set MAJORE4 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT4 );
Update GPGRILLE Set MAJORE5 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT5 );
Update GPGRILLE Set MAJORE6 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT6 );
Update GPGRILLE Set MAJORE7 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT7 );
Update GPGRILLE Set MAJORE8 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT8 );
Update GPGRILLE Set MAJORE9 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT9 );
Update GPGRILLE Set MAJORE10 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT10 );
Update GPGRILLE Set MAJORE11 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT11 );
Update GPGRILLE Set MAJORE12 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT12 );
Update GPGRILLE Set MAJORE13 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT13 );
Update GPGRILLE Set MAJORE14 = ( Select MAJ From INDBRUTMAJ Where  INDBRUTMAJ.BRUT = GPGRILLE.BRUT14 );

COMMIT;