-- 1/3 : Création de la table temporaire DROP TABLE IF EXISTS w_dbsetup ; CREATE TEMP TABLE w_dbsetup AS SELECT t_expert_controle_rule.oid, t_expert_controle.code AS expert_controle_code, t_expert_controle_rule.data_type, t_indicateurs.code AS indicateur_code, COALESCE(t_indicateur_condition_1.external_code2,'') AS indicateur_condition_external_code_1, COALESCE(t_indicateur_condition_2.external_code2,'') AS indicateur_condition_external_code_2, COALESCE(t_indicateur_condition_3.external_code2,'') AS indicateur_condition_external_code_3, t_expert_controle_rule.formula, t_indicateurs_2.code AS indicateur_code_2, COALESCE(t_indicateur_condition_21.external_code2,'') AS indicateur_condition_external_code_21, COALESCE(t_indicateur_condition_22.external_code2,'') AS indicateur_condition_external_code_22, COALESCE(t_indicateur_condition_23.external_code2,'') AS indicateur_condition_external_code_23, t_expert_controle_rule.formula_2, t_indicateurs_3.code AS indicateur_code_3, COALESCE(t_indicateur_condition_31.external_code2,'') AS indicateur_condition_external_code_31, COALESCE(t_indicateur_condition_32.external_code2,'') AS indicateur_condition_external_code_32, COALESCE(t_indicateur_condition_33.external_code2,'') AS indicateur_condition_external_code_33, t_expert_controle_rule.formula_3, 0::bigint AS expert_controle_id, 0::bigint AS expert_controle_rule_id, 0::bigint AS indicateur_id, 0::bigint AS indicateur_condition_id_1, 0::bigint AS indicateur_condition_id_2, 0::bigint AS indicateur_condition_id_3, 0::bigint AS indicateur_2_id, 0::bigint AS indicateur_condition_id_21, 0::bigint AS indicateur_condition_id_22, 0::bigint AS indicateur_condition_id_23, 0::bigint AS indicateur_3_id, 0::bigint AS indicateur_condition_id_31, 0::bigint AS indicateur_condition_id_32, 0::bigint AS indicateur_condition_id_33 FROM rh.t_expert_controle_rule JOIN rh.t_expert_controle ON expert_controle_id = t_expert_controle.oid JOIN rh.t_indicateurs t_indicateurs ON indicateur_id = t_indicateurs.oid LEFT JOIN rh.t_indicateur_condition t_indicateur_condition_1 ON indicateur_condition_id[1] = t_indicateur_condition_1.oid LEFT JOIN rh.t_indicateur_condition t_indicateur_condition_2 ON indicateur_condition_id[2] = t_indicateur_condition_2.oid LEFT JOIN rh.t_indicateur_condition t_indicateur_condition_3 ON indicateur_condition_id[3] = t_indicateur_condition_3.oid LEFT JOIN rh.t_indicateurs t_indicateurs_2 ON indicateur_2_id = t_indicateurs_2.oid LEFT JOIN rh.t_indicateur_condition t_indicateur_condition_21 ON indicateur_condition_2_id[1] = t_indicateur_condition_21.oid LEFT JOIN rh.t_indicateur_condition t_indicateur_condition_22 ON indicateur_condition_2_id[2] = t_indicateur_condition_22.oid LEFT JOIN rh.t_indicateur_condition t_indicateur_condition_23 ON indicateur_condition_2_id[3] = t_indicateur_condition_23.oid LEFT JOIN rh.t_indicateurs t_indicateurs_3 ON indicateur_3_id = t_indicateurs_3.oid LEFT JOIN rh.t_indicateur_condition t_indicateur_condition_31 ON indicateur_condition_3_id[1] = t_indicateur_condition_31.oid LEFT JOIN rh.t_indicateur_condition t_indicateur_condition_32 ON indicateur_condition_3_id[2] = t_indicateur_condition_32.oid LEFT JOIN rh.t_indicateur_condition t_indicateur_condition_33 ON indicateur_condition_3_id[3] = t_indicateur_condition_33.oid WHERE t_expert_controle_rule.is_cti = '1' AND t_expert_controle_rule.indicateur_id > 0 AND t_expert_controle.is_cti = '1' ORDER BY t_expert_controle.code, t_indicateurs.code, t_indicateur_condition_1.code, t_indicateur_condition_2.code, t_indicateur_condition_3.code LIMIT 0 ; -- 2/3 : Peuplement de la table temporaire INSERT INTO w_dbsetup( oid, expert_controle_code, data_type, indicateur_code, indicateur_condition_external_code_1, indicateur_condition_external_code_2, indicateur_condition_external_code_3, formula, indicateur_code_2, indicateur_condition_external_code_21, indicateur_condition_external_code_22, indicateur_condition_external_code_23, formula_2, indicateur_code_3, indicateur_condition_external_code_31, indicateur_condition_external_code_32, indicateur_condition_external_code_33, formula_3, expert_controle_id, expert_controle_rule_id, indicateur_id, indicateur_condition_id_1, indicateur_condition_id_2, indicateur_condition_id_3, indicateur_2_id, indicateur_condition_id_21, indicateur_condition_id_22, indicateur_condition_id_23, indicateur_3_id, indicateur_condition_id_31, indicateur_condition_id_32, indicateur_condition_id_33 ) VALUES (1, 'CTI_NIV1_C000', 'salarie', 'CTI_C000', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_CAD_EMP_NR', 'salarie', 'CTI_CDM', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_CAT_STAT_NR', 'salarie', 'CTI_CST', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_CE_NR', 'salarie', 'CTI_CEM', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_COD_POST_NR', 'salarie', 'CTI_CPO', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_COM_PAR_NR', 'salarie', 'CTI_CPA', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_CSP_NR', 'salarie', 'CTI_CSP', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_CTRID_NULL', 'salarie', 'CTI_CTRID_NULL', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_DAT_NAIS_NR', 'salarie', 'CTI_DNA', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_DTEDEB_NR', 'salarie', 'CTI_DTEDEB_NR', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_DTEFIN_NR', 'salarie', 'CTI_DTEFIN_NR', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_FIL_NR', 'salarie', 'CTI_FIL', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_GRI_NR', 'salarie', 'CTI_GRI', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_GROGRI_NR', 'salarie', 'CTI_GGR', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_GRO_COT_NR', 'salarie', 'CTI_GCO', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_MOTDEB_NR', 'salarie', 'CTI_MDD', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_MOTFIN_NR', 'salarie', 'CTI_MDF', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_NAT_NR', 'salarie', 'CTI_NAT', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_QUAL_NR', 'salarie', 'CTI_QUA', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_SEX_NR', 'salarie', 'CTI_SEX', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_SPE_NR', 'salarie', 'CTI_SPE', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_SRV_NR', 'salarie', 'CTI_SER', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_STT_NR', 'salarie', 'CTI_STA', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_TC_NR', 'salarie', 'CTI_TCNT', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_TTT_NR', 'salarie', 'CTI_TTT', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV1_UNI_FONC_NR', 'salarie', 'CTI_UFO', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV2_C000_BRUT', 'salarie', 'CTI_C000_BRUT', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV2_C000_COT_PAT', 'salarie', 'CTI_C000_COT_PAT', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV2_C000_COT_SAL', 'salarie', 'CTI_C000_COT_SAL', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV2_C000_H_CONTRAT', 'salarie', 'CTI_C000_H_CONTRAT', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV2_C000_H_PAYEES', 'salarie', 'CTI_C000_H_PAYEES', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV2_C000_H_TRAVAILLEES', 'salarie', 'CTI_C000_H_TRAVAILLES', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV2_C000_MASSE_SALARIALE', 'salarie', 'CTI_C000_MASSE_SALARIALE', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV2_C000_NET_A_PAYE', 'salarie', 'CTI_C000_NET_A_PAYE', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV2_C000_NET_IMP', 'salarie', 'CTINETIMPOS', 'CTI_RUBRIQUE#DT#C000', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV3_PRESENCE_PAIE_PAS_PLANNING', 'salarie', 'CTI_PRESENCE_PAIE_PAS_PLANNING', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (1, 'CTI_NIV3_PRESENCE_PLANNING_PAS_PAIE', 'salarie', 'CTI_PRESENCE_PLANNING_PAS_PAIE', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) ; -- 3/3 : Màj de la table iCTI SELECT rh.cti_reorganize_indicateur_condition() ; UPDATE w_dbsetup SET expert_controle_id = t_expert_controle.oid FROM rh.t_expert_controle WHERE expert_controle_code = t_expert_controle.code ; UPDATE w_dbsetup SET indicateur_id = t_indicateurs.oid FROM rh.t_indicateurs WHERE indicateur_code = t_indicateurs.code ; UPDATE w_dbsetup SET indicateur_2_id = t_indicateurs.oid FROM rh.t_indicateurs WHERE indicateur_code_2 = t_indicateurs.code ; UPDATE w_dbsetup SET indicateur_3_id = t_indicateurs.oid FROM rh.t_indicateurs WHERE indicateur_code_3 = t_indicateurs.code ; UPDATE w_dbsetup SET indicateur_condition_id_1 = t_indicateur_condition.oid FROM rh.t_indicateur_condition WHERE indicateur_condition_external_code_1 = t_indicateur_condition.external_code2 ; UPDATE w_dbsetup SET indicateur_condition_id_2 = t_indicateur_condition.oid FROM rh.t_indicateur_condition WHERE indicateur_condition_external_code_2 = t_indicateur_condition.external_code2 ; UPDATE w_dbsetup SET indicateur_condition_id_3 = t_indicateur_condition.oid FROM rh.t_indicateur_condition WHERE indicateur_condition_external_code_3 = t_indicateur_condition.external_code2 ; UPDATE w_dbsetup SET indicateur_condition_id_21 = t_indicateur_condition.oid FROM rh.t_indicateur_condition WHERE indicateur_condition_external_code_21 = t_indicateur_condition.external_code2 ; UPDATE w_dbsetup SET indicateur_condition_id_22 = t_indicateur_condition.oid FROM rh.t_indicateur_condition WHERE indicateur_condition_external_code_22 = t_indicateur_condition.external_code2 ; UPDATE w_dbsetup SET indicateur_condition_id_23 = t_indicateur_condition.oid FROM rh.t_indicateur_condition WHERE indicateur_condition_external_code_23 = t_indicateur_condition.external_code2 ; UPDATE w_dbsetup SET indicateur_condition_id_31 = t_indicateur_condition.oid FROM rh.t_indicateur_condition WHERE indicateur_condition_external_code_31 = t_indicateur_condition.external_code2 ; UPDATE w_dbsetup SET indicateur_condition_id_32 = t_indicateur_condition.oid FROM rh.t_indicateur_condition WHERE indicateur_condition_external_code_32 = t_indicateur_condition.external_code2 ; UPDATE w_dbsetup SET indicateur_condition_id_33 = t_indicateur_condition.oid FROM rh.t_indicateur_condition WHERE indicateur_condition_external_code_33 = t_indicateur_condition.external_code2 ; UPDATE w_dbsetup SET expert_controle_rule_id = t_expert_controle_rule.oid FROM rh. t_expert_controle_rule WHERE w_dbsetup.expert_controle_id = t_expert_controle_rule.expert_controle_id AND w_dbsetup.indicateur_id = t_expert_controle_rule.indicateur_id AND w_dbsetup.indicateur_condition_id_1 = COALESCE(t_expert_controle_rule.indicateur_condition_id[1],0) AND w_dbsetup.indicateur_condition_id_2 = COALESCE(t_expert_controle_rule.indicateur_condition_id[2],0) AND w_dbsetup.indicateur_condition_id_3 = COALESCE(t_expert_controle_rule.indicateur_condition_id[3],0) AND w_dbsetup.indicateur_2_id = t_expert_controle_rule.indicateur_2_id AND w_dbsetup.indicateur_condition_id_21 = COALESCE(t_expert_controle_rule.indicateur_condition_2_id[1],0) AND w_dbsetup.indicateur_condition_id_22 = COALESCE(t_expert_controle_rule.indicateur_condition_2_id[2],0) AND w_dbsetup.indicateur_condition_id_23 = COALESCE(t_expert_controle_rule.indicateur_condition_2_id[3],0) AND w_dbsetup.indicateur_3_id = t_expert_controle_rule.indicateur_3_id AND w_dbsetup.indicateur_condition_id_31 = COALESCE(t_expert_controle_rule.indicateur_condition_3_id[1],0) AND w_dbsetup.indicateur_condition_id_32 = COALESCE(t_expert_controle_rule.indicateur_condition_3_id[2],0) AND w_dbsetup.indicateur_condition_id_33 = COALESCE(t_expert_controle_rule.indicateur_condition_3_id[3],0) AND w_dbsetup.data_type = t_expert_controle_rule.data_type AND w_dbsetup.formula = t_expert_controle_rule.formula ; DELETE FROM rh.t_expert_controle_rule WHERE expert_controle_id IN (SELECT oid FROM rh.t_expert_controle WHERE code IN (SELECT expert_controle_code FROM w_dbsetup)) AND is_cti = '1' AND oid NOT IN (SELECT expert_controle_rule_id FROM w_dbsetup) ; INSERT INTO rh.t_expert_controle_rule( expert_controle_id, data_type, indicateur_id, indicateur_condition_id, formula, indicateur_2_id, indicateur_condition_2_id, formula_2, indicateur_3_id, indicateur_condition_3_id, formula_3, is_cti) SELECT expert_controle_id, data_type, indicateur_id, ARRAY[w_dbsetup.indicateur_condition_id_1,w_dbsetup.indicateur_condition_id_2,w_dbsetup.indicateur_condition_id_3], formula, indicateur_2_id, ARRAY[w_dbsetup.indicateur_condition_id_21,w_dbsetup.indicateur_condition_id_22,w_dbsetup.indicateur_condition_id_23], formula_2, indicateur_3_id, ARRAY[w_dbsetup.indicateur_condition_id_31,w_dbsetup.indicateur_condition_id_32,w_dbsetup.indicateur_condition_id_33], formula_3, '1'::text AS is_cti FROM w_dbsetup WHERE expert_controle_rule_id = 0 AND indicateur_id > 0 ;