You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
803 lines
29 KiB
803 lines
29 KiB
return: text
|
|
lang: plpgsql
|
|
src: |
|
|
DECLARE
|
|
result TEXT;
|
|
_module_activite TEXT;
|
|
_module_eco TEXT;
|
|
_module_rh TEXT;
|
|
BEGIN
|
|
|
|
RAISE NOTICE '%' , 'Initialisation poles';
|
|
|
|
IF EXISTS (SELECT * FROM pg_tables WHERE schemaname = 'activite' AND tablename = 't_pole') THEN
|
|
_module_activite = '1';
|
|
ELSE
|
|
_module_activite = '0';
|
|
END IF;
|
|
IF EXISTS (SELECT * FROM pg_tables WHERE schemaname = 'eco' AND tablename = 't_pole') THEN
|
|
_module_eco = '1';
|
|
ELSE
|
|
_module_eco = '0';
|
|
END IF;
|
|
IF EXISTS (SELECT * FROM pg_tables WHERE schemaname = 'rh' AND tablename = 't_pole') THEN
|
|
_module_rh = '1';
|
|
ELSE
|
|
_module_rh = '0';
|
|
END IF;
|
|
|
|
|
|
-- Contenu table
|
|
INSERT INTO base.t_pole (oid, code, texte, texte_court)
|
|
SELECT 0, '**', 'Non renseigné', 'Non renseigné'
|
|
WHERE 0 NOT IN (SELECT oid FROM base.t_pole);
|
|
|
|
-- replication dans compta
|
|
TRUNCATE compta.t_pole;
|
|
INSERT INTO compta.t_pole (oid, code, texte, texte_court, section_id, section_code, section_texte)
|
|
SELECT oid, code, texte, texte_court, section_id, section_code, section_texte
|
|
FROM base.t_pole
|
|
;
|
|
|
|
-- Pole par défaut
|
|
INSERT INTO compta.t_pole_rule(
|
|
code,
|
|
texte,
|
|
pole_id,
|
|
priorite,
|
|
liste_compte,
|
|
liste_sauf_compte,
|
|
liste_journal,
|
|
liste_sauf_journal,
|
|
liste_section_analytique,
|
|
liste_sauf_section_analytique,
|
|
liste_finess,
|
|
liste_site,
|
|
liste_exercice,
|
|
liste_mois_comptable,
|
|
special_calc,
|
|
special_calc_from,
|
|
from_rule_id
|
|
)
|
|
SELECT
|
|
'*DFT'::text AS code,
|
|
'Défaut'::text AS texte,
|
|
0::bigint AS pole_id,
|
|
99999::numeric AS priorite,
|
|
''::text AS liste_compte,
|
|
''::text AS liste_sauf_compte,
|
|
''::text AS liste_journal,
|
|
''::text AS liste_sauf_journal,
|
|
''::text AS liste_section_analytique,
|
|
''::text AS liste_sauf_section_analytique,
|
|
''::text AS liste_finess,
|
|
''::text AS liste_site,
|
|
''::text AS liste_exercice,
|
|
''::text AS liste_mois_comptable,
|
|
''::text AS special_calc,
|
|
''::text AS special_calc_from,
|
|
0::bigint AS from_rule_id
|
|
WHERE '*DFT' NOT IN (SELECT code FROM compta.t_pole_rule WHERE code IS NOT NULL)
|
|
;
|
|
|
|
DELETE FROM compta.t_pole_rule_pole
|
|
WHERE pole_rule_id <> 0 AND
|
|
(
|
|
pole_rule_id NOT IN (SELECT oid FROM compta.t_pole_rule) OR
|
|
pole_rule_id IS NULL
|
|
)
|
|
|
|
;
|
|
|
|
INSERT INTO compta.t_pole_rule_pole (pole_rule_id, pole_id, taux)
|
|
SELECT oid, pole_id, 1
|
|
FROM compta.t_pole_rule
|
|
WHERE oid NOT IN (SELECT pole_rule_id FROM compta.t_pole_rule_pole WHERE pole_rule_id IS NOT NULL)
|
|
;
|
|
|
|
|
|
|
|
|
|
-- Traduction des règles en oid
|
|
RAISE NOTICE '%' , 'Application des regles. Traduction en oid';
|
|
DROP TABLE IF EXISTS w_pole_rule;
|
|
CREATE TEMP TABLE w_pole_rule AS
|
|
SELECT
|
|
t_pole_rule.oid,
|
|
pole_id AS default_pole_id,
|
|
special_calc,
|
|
priorite,
|
|
CASE WHEN liste_finess <> '' THEN '1' ELSE '0' END AS a_liste_finess,
|
|
string_to_array(translate(liste_finess,'*,','% '),' ') AS liste_finess,
|
|
NULL::bigint[] AS liste_finess_id ,
|
|
|
|
CASE WHEN liste_site <> '' THEN '1' ELSE '0' END AS a_liste_site,
|
|
string_to_array(translate(liste_site,'*,','% '),' ') AS liste_site,
|
|
NULL::bigint[] AS liste_site_id ,
|
|
|
|
CASE WHEN liste_exercice <> '' THEN '1' ELSE '0' END AS a_liste_exercice,
|
|
string_to_array(translate(liste_exercice,'*,','% '),' ') AS liste_exercice,
|
|
NULL::bigint[] AS liste_exercice_id ,
|
|
|
|
CASE WHEN liste_mois_comptable <> '' THEN '1' ELSE '0' END AS a_liste_mois_comptable,
|
|
string_to_array(translate(liste_mois_comptable,'*,','% '),' ') AS liste_mois_comptable,
|
|
NULL::bigint[] AS liste_mois_comptable_id ,
|
|
|
|
CASE WHEN liste_compte <> '' THEN '1' ELSE '0' END AS a_liste_compte,
|
|
string_to_array(translate(liste_compte,'*,','% '),' ') AS liste_compte,
|
|
NULL::bigint[] AS liste_compte_id ,
|
|
|
|
CASE WHEN liste_sauf_compte <> '' THEN '1' ELSE '0' END AS a_liste_sauf_compte,
|
|
string_to_array(translate(liste_sauf_compte,'*,','% '),' ') AS liste_sauf_compte,
|
|
NULL::bigint[] AS liste_sauf_compte_id,
|
|
|
|
CASE WHEN liste_journal <> '' THEN '1' ELSE '0' END AS a_liste_journal,
|
|
string_to_array(translate(liste_journal,'*,','% '),' ') AS liste_journal,
|
|
NULL::bigint[] AS liste_journal_id,
|
|
|
|
CASE WHEN liste_sauf_journal <> '' THEN '1' ELSE '0' END AS a_liste_sauf_journal,
|
|
string_to_array(translate(liste_sauf_journal,'*,','% '),' ') AS liste_sauf_journal,
|
|
NULL::bigint[] AS liste_sauf_journal_id,
|
|
|
|
CASE WHEN liste_section_analytique <> '' THEN '1' ELSE '0' END AS a_liste_section_analytique,
|
|
string_to_array(translate(liste_section_analytique,'*,','% '),' ') AS liste_section_analytique,
|
|
NULL::bigint[] AS liste_section_analytique_id,
|
|
|
|
CASE WHEN liste_sauf_section_analytique <> '' THEN '1' ELSE '0' END AS a_liste_sauf_section_analytique,
|
|
string_to_array(translate(liste_sauf_section_analytique,'*,','% '),' ') AS liste_sauf_section_analytique,
|
|
NULL::bigint[] AS liste_sauf_section_analytique_id
|
|
|
|
|
|
FROM compta.t_pole_rule
|
|
JOIN compta.t_pole ON pole_id = t_pole.oid
|
|
WHERE special_calc_from = ''
|
|
ORDER BY priorite, pole_id;
|
|
|
|
ANALYSE w_pole_rule
|
|
;
|
|
|
|
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_finess_id = (SELECT base.cti_group_array3(oid) FROM base.t_finess WHERE oid <> 0 AND code LIKE ANY (liste_finess))
|
|
WHERE a_liste_finess = '1';
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_site_id = (SELECT base.cti_group_array3(oid) FROM compta.t_sites WHERE oid <> 0 AND code LIKE ANY (liste_site))
|
|
WHERE a_liste_site = '1';
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_exercice_id = subview.liste_exercice_id
|
|
FROM
|
|
(
|
|
SELECT oid, base.cti_array_accum(exercice) AS liste_exercice_id
|
|
FROM
|
|
(
|
|
SELECT oid, base.cti_to_number(exercice)::bigint AS exercice
|
|
FROM
|
|
(
|
|
SELECT oid, unnest(liste_exercice) AS exercice
|
|
FROM w_pole_rule
|
|
WHERE a_liste_exercice = '1'
|
|
) subview
|
|
WHERE exercice > 0
|
|
) subview
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE w_pole_rule.oid = subview.oid
|
|
;
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_mois_comptable_id = subview.liste_mois_comptable_id
|
|
FROM
|
|
(
|
|
SELECT oid, base.cti_array_accum(mois_comptable) AS liste_mois_comptable_id
|
|
FROM
|
|
(
|
|
SELECT oid, base.cti_to_number(mois_comptable)::bigint AS mois_comptable
|
|
FROM
|
|
(
|
|
SELECT oid, unnest(liste_mois_comptable) AS mois_comptable
|
|
FROM w_pole_rule
|
|
WHERE a_liste_mois_comptable = '1'
|
|
) subview
|
|
WHERE mois_comptable > 0
|
|
) subview
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE w_pole_rule.oid = subview.oid
|
|
;
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_compte_id = (SELECT base.cti_group_array3(oid) FROM compta.t_comptes WHERE oid <> 0 AND numero LIKE ANY (liste_compte))
|
|
WHERE a_liste_compte = '1';
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_sauf_compte_id = (SELECT base.cti_group_array3(oid) FROM compta.t_comptes WHERE oid <> 0 AND numero LIKE ANY (liste_sauf_compte))
|
|
WHERE a_liste_sauf_compte = '1';
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_journal_id = (SELECT base.cti_group_array3(oid) FROM compta.t_journaux WHERE oid <> 0 AND code LIKE ANY (liste_journal))
|
|
WHERE a_liste_journal = '1';
|
|
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_sauf_journal_id = (SELECT base.cti_group_array3(oid) FROM compta.t_journaux WHERE oid <> 0 AND code LIKE ANY (liste_sauf_journal))
|
|
WHERE a_liste_sauf_journal = '1';
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_section_analytique_id = (SELECT base.cti_group_array3(oid) FROM compta.t_sections_analytiques WHERE oid <> 0 AND code LIKE ANY (liste_section_analytique))
|
|
WHERE a_liste_section_analytique = '1';
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_sauf_section_analytique_id = (SELECT base.cti_group_array3(oid) FROM compta.t_sections_analytiques WHERE oid <> 0 AND code LIKE ANY (liste_sauf_section_analytique))
|
|
WHERE a_liste_sauf_section_analytique = '1';
|
|
|
|
-- oids selon listes
|
|
PERFORM base.cti_execute('
|
|
UPDATE w_pole_rule
|
|
SET liste_'||item||'_id = array_cat(
|
|
liste_'||item||'_id
|
|
,
|
|
(
|
|
SELECT base.cti_group_array3(to_id)
|
|
FROM compta.t_listes
|
|
JOIN compta.t_listes_contenu ON liste_id = t_listes.oid
|
|
WHERE (''[LISTE:''||t_listes.code||'']'') LIKE ANY (liste_'||item||')
|
|
)
|
|
)
|
|
WHERE a_liste_'||item||' = ''1'' AND
|
|
array_to_string(liste_'||item||','','') LIKE ''%[LISTE:%''
|
|
',1)
|
|
FROM
|
|
(
|
|
SELECT 'compte'::text AS item
|
|
UNION
|
|
SELECT 'sauf_compte'::text AS item
|
|
UNION
|
|
SELECT 'journal'::text AS item
|
|
UNION
|
|
SELECT 'sauf_journal'::text AS item
|
|
UNION
|
|
SELECT 'section_analytique'::text AS item
|
|
UNION
|
|
SELECT 'sauf_section_analytique'::text AS item
|
|
) subview
|
|
;
|
|
|
|
-- oids selon classes
|
|
PERFORM base.cti_execute('
|
|
UPDATE w_pole_rule
|
|
SET liste_'||item||'_id = array_cat(
|
|
liste_'||item||'_id
|
|
,
|
|
(
|
|
SELECT base.cti_group_array3(to_id)
|
|
FROM compta.t_classes
|
|
JOIN compta.t_classes_sections ON classe_id = t_classes.oid
|
|
JOIN compta.t_classes_sections_elements ON section_id = t_classes_sections.oid
|
|
WHERE (''[CLASSE:''||t_classes.code||'':''||t_classes_sections.code||'']'') LIKE ANY (liste_'||item||')
|
|
)
|
|
)
|
|
WHERE a_liste_'||item||' = ''1'' AND
|
|
array_to_string(liste_'||item||','','') LIKE ''%[CLASSE:%''
|
|
',1)
|
|
FROM
|
|
(
|
|
SELECT 'compte'::text AS item
|
|
UNION
|
|
SELECT 'sauf_compte'::text AS item
|
|
UNION
|
|
SELECT 'journal'::text AS item
|
|
UNION
|
|
SELECT 'sauf_journal'::text AS item
|
|
UNION
|
|
SELECT 'section_analytique'::text AS item
|
|
UNION
|
|
SELECT 'sauf_section_analytique'::text AS item
|
|
) subview
|
|
;
|
|
|
|
|
|
-- Génération automatique des ventilations automatiques
|
|
DELETE FROM compta.t_pole_rule_pole
|
|
WHERE pole_rule_id IN
|
|
(SELECT oid
|
|
FROM compta.t_pole_rule
|
|
WHERE special_calc_from <> ''
|
|
)
|
|
;
|
|
|
|
DELETE FROM compta.t_pole_rule
|
|
WHERE special_calc_from <> ''
|
|
;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS w_pole_rule_pole;
|
|
CREATE TEMP TABLE w_pole_rule_pole(
|
|
pole_rule_id bigint,
|
|
exercice_comptable text,
|
|
mois_comptable text,
|
|
compte_code text,
|
|
pole_id bigint,
|
|
taux numeric,
|
|
subrule_index numeric
|
|
)
|
|
;
|
|
|
|
-- Journal des ventes
|
|
IF _module_activite = '1' THEN
|
|
RAISE NOTICE '%' , 'Ventilations automatiques JVT.';
|
|
INSERT INTO w_pole_rule_pole
|
|
SELECT pole_rule_id,
|
|
exercice_comptable,
|
|
mois_comptable,
|
|
compte_code,
|
|
pole_id,
|
|
base.cti_division(total_comptabilise,SUM(total_comptabilise) OVER (PARTITION BY pole_rule_id, exercice_comptable, mois_comptable, compte_code)) AS taux,
|
|
rank() OVER (PARTITION BY pole_rule_id ORDER BY exercice_comptable, mois_comptable, compte_code) AS subrule_index
|
|
FROM
|
|
(
|
|
SELECT w_pole_rule.oid AS pole_rule_id,
|
|
to_char(date_vente,'YYYY') AS exercice_comptable,
|
|
CASE WHEN special_calc = 'JVTCPTMM' THEN to_char(date_vente,'YYYYMM') ELSE '' END AS mois_comptable,
|
|
CASE WHEN special_calc <> 'JVTALLEX' THEN t_compte.code ELSE '' END AS compte_code,
|
|
CASE WHEN p_factures_lignes_c.pole_id > 0 THEN p_factures_lignes_c.pole_id ELSE default_pole_id END AS pole_id,
|
|
SUM(montant_comptabilise) AS total_comptabilise
|
|
FROM activite.p_factures_lignes_c
|
|
JOIN activite.p_factures ON p_factures_lignes_c.no_facture = p_factures.no_facture
|
|
JOIN activite.t_compte ON compte_produit_id = t_compte.oid
|
|
JOIN w_pole_rule ON
|
|
(
|
|
special_calc = 'JVTCPTMM' OR
|
|
special_calc = 'JVTCPTEX' OR
|
|
special_calc = 'JVTALLEX'
|
|
)
|
|
WHERE date_vente >= '20180101' AND
|
|
montant_comptabilise <> 0 AND
|
|
(a_liste_compte = '0' OR t_compte.code <> '' AND t_compte.code LIKE ANY (liste_compte)) AND
|
|
(a_liste_exercice = '0' OR date_part('year',date_vente) = ANY (liste_exercice))
|
|
GROUP BY 1,2,3,4,5
|
|
HAVING SUM(montant_comptabilise) > 0
|
|
) subview
|
|
;
|
|
END IF ;
|
|
|
|
|
|
IF _module_eco = '1' THEN
|
|
RAISE NOTICE '%' , 'Ventilations automatiques Conso Eco.';
|
|
INSERT INTO w_pole_rule_pole
|
|
SELECT pole_rule_id,
|
|
exercice_comptable,
|
|
mois_comptable,
|
|
compte_code,
|
|
CASE WHEN taux < 0.005 THEN default_pole_id ELSE pole_id END AS pole_id,
|
|
SUM(taux),
|
|
MIN(subrule_index)
|
|
FROM
|
|
(
|
|
SELECT pole_rule_id,
|
|
exercice_comptable,
|
|
mois_comptable,
|
|
compte_code,
|
|
pole_id,
|
|
default_pole_id,
|
|
base.cti_division(total_sortie_montant_ht,SUM(total_sortie_montant_ht) OVER (PARTITION BY pole_rule_id, exercice_comptable, mois_comptable, compte_code)) AS taux,
|
|
rank() OVER (PARTITION BY pole_rule_id ORDER BY exercice_comptable, mois_comptable, compte_code) AS subrule_index
|
|
FROM
|
|
(
|
|
SELECT w_pole_rule.oid AS pole_rule_id,
|
|
to_char(p_mouvements_articles.date,'YYYY') AS exercice_comptable,
|
|
CASE WHEN special_calc = 'ECOCPTMM' THEN to_char(p_mouvements_articles.date,'YYYYMM') ELSE '' END AS mois_comptable,
|
|
CASE WHEN special_calc <> 'ECOALLEX' THEN t_compte.code ELSE '' END AS compte_code,
|
|
CASE WHEN p_mouvements_articles.pole_id > 0 THEN p_mouvements_articles.pole_id ELSE default_pole_id END AS pole_id,
|
|
default_pole_id,
|
|
SUM(sortie_montant_ht) AS total_sortie_montant_ht
|
|
FROM eco.p_mouvements_articles
|
|
JOIN eco.t_compte ON p_mouvements_articles.compte_id = t_compte.oid
|
|
JOIN w_pole_rule ON
|
|
(
|
|
special_calc = 'ECOCPTEX' OR
|
|
special_calc = 'ECOALLEX'
|
|
)
|
|
WHERE p_mouvements_articles.date >= '20180101' AND
|
|
sortie_montant_ht <> 0 AND
|
|
p_mouvements_articles.unite_fonctionnelle_id <> 0 AND
|
|
(a_liste_compte = '0' OR t_compte.code <> '' AND t_compte.code LIKE ANY (liste_compte)) AND
|
|
(a_liste_exercice = '0' OR date_part('year',p_mouvements_articles.date) = ANY (liste_exercice))
|
|
GROUP BY 1,2,3,4,5,6
|
|
HAVING SUM(sortie_montant_ht) > 0
|
|
) subview
|
|
) subview
|
|
GROUP BY 1,2,3,4,5
|
|
;
|
|
END IF;
|
|
|
|
|
|
|
|
|
|
IF _module_rh = '1' THEN
|
|
RAISE NOTICE '%' , 'Ventilations automatiques PAIE';
|
|
INSERT INTO w_pole_rule_pole
|
|
SELECT pole_rule_id,
|
|
exercice_comptable,
|
|
mois_comptable,
|
|
compte_code,
|
|
pole_id,
|
|
base.cti_division(total_montant_masse_salariale,SUM(total_montant_masse_salariale) OVER (PARTITION BY pole_rule_id, exercice_comptable, mois_comptable, compte_code)) AS taux,
|
|
rank() OVER (PARTITION BY pole_rule_id ORDER BY exercice_comptable, mois_comptable, compte_code) AS subrule_index
|
|
FROM
|
|
(
|
|
SELECT w_pole_rule.oid AS pole_rule_id,
|
|
to_char(p_historique_paie.date_paie,'YYYY') AS exercice_comptable,
|
|
CASE WHEN special_calc = 'PAICPTMM' THEN to_char(p_historique_paie.date_paie,'YYYYMM') ELSE '' END AS mois_comptable,
|
|
CASE WHEN special_calc <> 'PAIALLEX' THEN t_compte.code ELSE '' END AS compte_code,
|
|
CASE WHEN p_profil_contrat_mois.pole_id > 0 THEN p_profil_contrat_mois.pole_id ELSE default_pole_id END AS pole_id,
|
|
SUM(montant_masse_salariale) AS total_montant_masse_salariale
|
|
FROM rh.p_historique_paie
|
|
JOIN rh.p_profil_contrat_mois ON p_profil_contrat_mois.contrat_mois_id = p_historique_paie.contrat_mois_id
|
|
JOIN rh.t_compte ON p_historique_paie.compte_id = t_compte.oid
|
|
JOIN w_pole_rule ON
|
|
(
|
|
special_calc = 'PAICPTEX' OR
|
|
special_calc = 'PAIALLEX'
|
|
)
|
|
WHERE p_historique_paie.date_paie >= '20180101' AND
|
|
montant_masse_salariale <> 0 AND
|
|
(a_liste_compte = '0' OR t_compte.code <> '' AND t_compte.code LIKE ANY (liste_compte)) AND
|
|
(a_liste_exercice = '0' OR date_part('year',p_historique_paie.date_paie) = ANY (liste_exercice))
|
|
GROUP BY 1,2,3,4,5
|
|
HAVING SUM(montant_masse_salariale) > 0
|
|
) subview
|
|
;
|
|
END IF;
|
|
|
|
|
|
-- Génération des sous regles
|
|
UPDATE w_pole_rule_pole SET
|
|
exercice_comptable = '',
|
|
mois_comptable = ''
|
|
FROM
|
|
(
|
|
SELECT pole_rule_id,
|
|
compte_code
|
|
FROM w_pole_rule_pole
|
|
GROUP BY 1,2
|
|
HAVING count(DISTINCT pole_id) = 1
|
|
) subview
|
|
WHERE w_pole_rule_pole.pole_rule_id = subview.pole_rule_id AND
|
|
w_pole_rule_pole.compte_code = subview.compte_code
|
|
;
|
|
|
|
UPDATE w_pole_rule_pole SET
|
|
mois_comptable = ''
|
|
FROM
|
|
(
|
|
SELECT pole_rule_id,
|
|
compte_code,
|
|
exercice_comptable
|
|
FROM w_pole_rule_pole
|
|
WHERE exercice_comptable <> ''
|
|
GROUP BY 1,2,3
|
|
HAVING count(DISTINCT pole_id) = 1
|
|
) subview
|
|
WHERE w_pole_rule_pole.pole_rule_id = subview.pole_rule_id AND
|
|
w_pole_rule_pole.compte_code = subview.compte_code AND
|
|
w_pole_rule_pole.exercice_comptable = subview.exercice_comptable
|
|
;
|
|
|
|
DELETE FROM w_pole_rule_pole
|
|
USING
|
|
(
|
|
SELECT pole_rule_id,
|
|
compte_code,
|
|
exercice_comptable,
|
|
mois_comptable,
|
|
pole_id,
|
|
MIN(w_pole_rule_pole.CTID) AS CTID_keep
|
|
FROM w_pole_rule_pole
|
|
GROUP BY 1,2,3,4,5
|
|
HAVING count(*) > 1
|
|
) subview
|
|
WHERE w_pole_rule_pole.pole_rule_id = subview.pole_rule_id AND
|
|
w_pole_rule_pole.compte_code = subview.compte_code AND
|
|
w_pole_rule_pole.exercice_comptable = subview.exercice_comptable AND
|
|
w_pole_rule_pole.mois_comptable = subview.mois_comptable AND
|
|
w_pole_rule_pole.pole_id = subview.pole_id AND
|
|
w_pole_rule_pole.CTID <> CTID_keep
|
|
;
|
|
|
|
UPDATE w_pole_rule_pole
|
|
SET compte_code = liste_compte
|
|
FROM compta.t_pole_rule
|
|
WHERE w_pole_rule_pole.pole_rule_id = t_pole_rule.oid AND
|
|
w_pole_rule_pole.compte_code = ''
|
|
;
|
|
|
|
|
|
INSERT INTO compta.t_pole_rule(
|
|
oid,
|
|
code,
|
|
texte,
|
|
priorite,
|
|
liste_finess,
|
|
liste_site,
|
|
liste_exercice,
|
|
liste_mois_comptable,
|
|
liste_compte,
|
|
liste_sauf_compte,
|
|
liste_journal,
|
|
liste_sauf_journal,
|
|
liste_section_analytique,
|
|
liste_sauf_section_analytique,
|
|
special_calc,
|
|
special_calc_from,
|
|
from_rule_id
|
|
)
|
|
SELECT
|
|
0 - (pole_rule_id * 1000000 + subrule_index) AS oid,
|
|
MAX(t_pole_rule.code || compte_code || w_pole_rule_pole.mois_comptable),
|
|
MAX(t_pole_rule.texte || ' ' || compte_code || ' ' || w_pole_rule_pole.mois_comptable),
|
|
MAX(t_pole_rule.priorite),
|
|
MAX(t_pole_rule.liste_finess),
|
|
MAX(t_pole_rule.liste_site),
|
|
MAX(CASE WHEN w_pole_rule_pole.mois_comptable = '' THEN w_pole_rule_pole.exercice_comptable ELSE '' END) AS liste_exercice,
|
|
MAX(w_pole_rule_pole.mois_comptable) AS liste_mois_comptable,
|
|
MAX(w_pole_rule_pole.compte_code) AS liste_compte,
|
|
''::text AS liste_sauf_compte,
|
|
MAX(t_pole_rule.liste_journal),
|
|
MAX(t_pole_rule.liste_sauf_journal),
|
|
MAX(t_pole_rule.liste_section_analytique),
|
|
MAX(t_pole_rule.liste_sauf_section_analytique),
|
|
''::text AS special_calc,
|
|
MAX(t_pole_rule.special_calc) AS special_calc_from,
|
|
MAX(pole_rule_id)
|
|
FROM w_pole_rule_pole
|
|
JOIN compta.t_pole_rule ON pole_rule_id = t_pole_rule.oid
|
|
GROUP BY 1
|
|
;
|
|
|
|
INSERT INTO compta.t_pole_rule_pole (pole_rule_id, pole_id, taux)
|
|
SELECT t_pole_rule.oid AS pole_rule_id, w_pole_rule_pole.pole_id, w_pole_rule_pole.taux
|
|
FROM w_pole_rule_pole
|
|
JOIN compta.t_pole_rule ON
|
|
t_pole_rule.from_rule_id = w_pole_rule_pole.pole_rule_id AND
|
|
(
|
|
t_pole_rule.special_calc_from = 'JVTCPTMM' OR
|
|
t_pole_rule.special_calc_from = 'JVTCPTEX' OR
|
|
t_pole_rule.special_calc_from = 'JVTALLEX' OR
|
|
t_pole_rule.special_calc_from = 'ECOCPTEX' OR
|
|
t_pole_rule.special_calc_from = 'ECOALLEX' OR
|
|
t_pole_rule.special_calc_from = 'PAICPTEX' OR
|
|
t_pole_rule.special_calc_from = 'PAIALLEX'
|
|
) AND
|
|
t_pole_rule.liste_compte = w_pole_rule_pole.compte_code AND
|
|
t_pole_rule.liste_exercice = CASE WHEN w_pole_rule_pole.mois_comptable = '' THEN w_pole_rule_pole.exercice_comptable ELSE '' END AND
|
|
t_pole_rule.liste_mois_comptable = w_pole_rule_pole.mois_comptable
|
|
;
|
|
|
|
|
|
INSERT INTO w_pole_rule
|
|
SELECT t_pole_rule.oid,
|
|
w_pole_rule.default_pole_id,
|
|
t_pole_rule.special_calc,
|
|
w_pole_rule.priorite,
|
|
w_pole_rule.a_liste_finess,
|
|
w_pole_rule.liste_finess,
|
|
w_pole_rule.liste_finess_id,
|
|
w_pole_rule.a_liste_site,
|
|
w_pole_rule.liste_site,
|
|
w_pole_rule.liste_site_id,
|
|
CASE WHEN t_pole_rule.liste_exercice <> '' THEN '1' ELSE '0' END AS a_liste_exercice,
|
|
string_to_array(translate(t_pole_rule.liste_exercice,'*,','% '),' ') AS liste_exercice,
|
|
NULL::bigint[] AS liste_exercice_id ,
|
|
CASE WHEN t_pole_rule.liste_mois_comptable <> '' THEN '1' ELSE '0' END AS a_liste_mois_comptable,
|
|
string_to_array(translate(t_pole_rule.liste_mois_comptable,'*,','% '),' ') AS liste_mois_comptable,
|
|
NULL::bigint[] AS liste_mois_comptable_id ,
|
|
CASE WHEN t_pole_rule.liste_compte <> '' THEN '1' ELSE '0' END AS a_liste_compte,
|
|
string_to_array(translate(t_pole_rule.liste_compte,'*,','% '),' ') AS liste_compte,
|
|
NULL::bigint[] AS liste_compte_id ,
|
|
'0'::text AS a_liste_sauf_compte,
|
|
NULL::text[] AS liste_sauf_compte,
|
|
NULL::bigint[] AS liste_sauf_compte_id,
|
|
w_pole_rule.a_liste_journal,
|
|
w_pole_rule.liste_journal,
|
|
w_pole_rule.liste_journal_id,
|
|
w_pole_rule.a_liste_sauf_journal,
|
|
w_pole_rule.liste_sauf_journal,
|
|
w_pole_rule.liste_sauf_journal_id,
|
|
w_pole_rule.a_liste_section_analytique,
|
|
w_pole_rule.liste_section_analytique,
|
|
w_pole_rule.liste_section_analytique_id,
|
|
a_liste_sauf_section_analytique,
|
|
w_pole_rule.liste_sauf_section_analytique,
|
|
w_pole_rule.liste_sauf_section_analytique_id
|
|
FROM w_pole_rule
|
|
JOIN compta.t_pole_rule ON
|
|
w_pole_rule.special_calc <> '' AND
|
|
w_pole_rule.special_calc = t_pole_rule.special_calc_from AND
|
|
w_pole_rule.oid = t_pole_rule.from_rule_id
|
|
;
|
|
|
|
ANALYSE w_pole_rule
|
|
;
|
|
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_exercice_id = subview.liste_exercice_id
|
|
FROM
|
|
(
|
|
SELECT oid, base.cti_array_accum(exercice) AS liste_exercice_id
|
|
FROM
|
|
(
|
|
SELECT oid, base.cti_to_number(exercice)::bigint AS exercice
|
|
FROM
|
|
(
|
|
SELECT oid, unnest(liste_exercice) AS exercice
|
|
FROM w_pole_rule
|
|
WHERE a_liste_exercice = '1'
|
|
) subview
|
|
WHERE exercice > 0
|
|
) subview
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE w_pole_rule.oid = subview.oid
|
|
;
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_mois_comptable_id = subview.liste_mois_comptable_id
|
|
FROM
|
|
(
|
|
SELECT oid, base.cti_array_accum(mois_comptable) AS liste_mois_comptable_id
|
|
FROM
|
|
(
|
|
SELECT oid, base.cti_to_number(mois_comptable)::bigint AS mois_comptable
|
|
FROM
|
|
(
|
|
SELECT oid, unnest(liste_mois_comptable) AS mois_comptable
|
|
FROM w_pole_rule
|
|
WHERE a_liste_mois_comptable = '1'
|
|
) subview
|
|
WHERE mois_comptable > 0
|
|
) subview
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE w_pole_rule.oid = subview.oid
|
|
;
|
|
|
|
UPDATE w_pole_rule
|
|
SET liste_compte_id = (SELECT base.cti_group_array3(oid) FROM compta.t_comptes WHERE oid <> 0 AND numero LIKE ANY (liste_compte))
|
|
WHERE a_liste_compte = '1';
|
|
|
|
|
|
|
|
|
|
RAISE NOTICE '%' , 'Application des regles.';
|
|
|
|
|
|
PERFORM base.cti_execute('
|
|
DROP TABLE IF EXISTS w_historique_ecritures_total;
|
|
CREATE TEMP TABLE w_historique_ecritures_total AS
|
|
SELECT p_historique_ecritures.CTID AS from_CTID,
|
|
COALESCE((MIN(ARRAY[priorite,w_pole_rule.oid]))[2],0) AS pole_rule_id
|
|
FROM compta.p_historique_ecritures_total p_historique_ecritures
|
|
' || CASE WHEN a_liste_finess = '1' THEN 'JOIN compta.t_sites ON t_sites.oid = site_id' ELSE '' END || '
|
|
LEFT JOIN w_pole_rule ON
|
|
special_calc = ''''
|
|
' || CASE WHEN a_liste_finess = '1' THEN 'AND (a_liste_finess = ''0'' OR finess_id = ANY (liste_finess_id))' ELSE '' END || '
|
|
' || CASE WHEN a_liste_site = '1' THEN 'AND (a_liste_site = ''0'' OR site_id = ANY (liste_site_id))' ELSE '' END || '
|
|
' || CASE WHEN a_liste_exercice = '1' THEN 'AND (a_liste_exercice = ''0'' OR exercice_comptable = ANY (liste_exercice))' ELSE '' END || '
|
|
' || CASE WHEN a_liste_mois_comptable = '1' THEN 'AND (a_liste_mois_comptable = ''0'' OR mois_comptable = ANY (liste_mois_comptable))' ELSE '' END || '
|
|
' || CASE WHEN a_liste_compte = '1' THEN 'AND (a_liste_compte = ''0'' OR compte_id = ANY (liste_compte_id))' ELSE '' END || '
|
|
' || CASE WHEN a_liste_sauf_compte = '1' THEN 'AND (a_liste_sauf_compte = ''0'' OR compte_id <> ALL (liste_sauf_compte_id))' ELSE '' END || '
|
|
' || CASE WHEN a_liste_journal = '1' THEN 'AND (a_liste_journal = ''0'' OR journal_id = ANY (liste_journal_id))' ELSE '' END || '
|
|
' || CASE WHEN a_liste_sauf_journal = '1' THEN 'AND ((a_liste_sauf_journal = ''0'' OR compte_id <> ALL (liste_sauf_journal_id))' ELSE '' END || '
|
|
' || CASE WHEN a_liste_section_analytique = '1' THEN 'AND (a_liste_section_analytique = ''0'' OR section_analytique_id = ANY (liste_section_analytique_id))' ELSE '' END || '
|
|
' || CASE WHEN a_liste_sauf_section_analytique = '1' THEN 'AND (a_liste_sauf_section_analytique = ''0'' OR compte_id <> ALL (liste_sauf_section_analytique_id)) ' ELSE '' END || '
|
|
WHERE mois_comptable >= 201801 AND
|
|
clinique_honoraire <> ''H''
|
|
GROUP BY 1
|
|
HAVING COALESCE((MIN(ARRAY[priorite,w_pole_rule.oid]))[2],0) IS DISTINCT FROM MAX(COALESCE(p_historique_ecritures.pole_rule_id,0))
|
|
',1)
|
|
FROM (
|
|
SELECT
|
|
MAX(a_liste_finess) AS a_liste_finess,
|
|
MAX(a_liste_site) AS a_liste_site,
|
|
MAX(a_liste_exercice) AS a_liste_exercice,
|
|
MAX(a_liste_mois_comptable) AS a_liste_mois_comptable,
|
|
MAX(a_liste_compte) AS a_liste_compte,
|
|
MAX(a_liste_sauf_compte) AS a_liste_sauf_compte,
|
|
MAX(a_liste_journal) AS a_liste_journal,
|
|
MAX(a_liste_sauf_journal) AS a_liste_sauf_journal,
|
|
MAX(a_liste_section_analytique) AS a_liste_section_analytique,
|
|
MAX(a_liste_sauf_section_analytique) AS a_liste_sauf_section_analytique
|
|
FROM w_pole_rule
|
|
) subview
|
|
;
|
|
|
|
|
|
|
|
|
|
ANALYSE w_historique_ecritures_total
|
|
;
|
|
|
|
|
|
|
|
RAISE NOTICE '%' , 'Application des regles. Validation des poles';
|
|
|
|
UPDATE compta.p_historique_ecritures_total p_historique_ecritures SET
|
|
pole_rule_id = w_historique_ecritures.pole_rule_id
|
|
FROM w_historique_ecritures_total w_historique_ecritures
|
|
WHERE
|
|
p_historique_ecritures.CTID = w_historique_ecritures.from_CTID AND
|
|
p_historique_ecritures.pole_rule_id IS DISTINCT FROM w_historique_ecritures.pole_rule_id
|
|
;
|
|
|
|
ANALYSE compta.p_historique_ecritures_total
|
|
;
|
|
|
|
-- Réplication sur détail
|
|
DROP TABLE IF EXISTS w_ecriture_pole;
|
|
CREATE TEMP TABLE w_ecriture_pole AS
|
|
SELECT site_id, mois_comptable, compte_id, journal_id, section_analytique_id, MAX(pole_rule_id) AS pole_rule_id
|
|
FROM compta.p_historique_ecritures_total
|
|
WHERE mois_comptable >= 201801 AND
|
|
clinique_honoraire <> 'H'
|
|
GROUP BY 1,2,3,4,5
|
|
;
|
|
|
|
ANALYSE w_ecriture_pole;
|
|
|
|
CREATE INDEX iw_ecriture_pole_2 ON w_ecriture_pole USING btree (compte_id);
|
|
|
|
UPDATE compta.p_historique_ecritures
|
|
SET pole_rule_id = w_ecriture_pole.pole_rule_id
|
|
FROM w_ecriture_pole
|
|
WHERE p_historique_ecritures.compte_id = w_ecriture_pole.compte_id AND
|
|
p_historique_ecritures.mois_comptable >= '201801' AND
|
|
clinique_honoraire <> 'H' AND
|
|
p_historique_ecritures.site_id = w_ecriture_pole.site_id AND
|
|
p_historique_ecritures.mois_comptable = w_ecriture_pole.mois_comptable AND
|
|
p_historique_ecritures.journal_id = w_ecriture_pole.journal_id AND
|
|
p_historique_ecritures.section_analytique_id = w_ecriture_pole.section_analytique_id AND
|
|
p_historique_ecritures.pole_rule_id IS DISTINCT FROM w_ecriture_pole.pole_rule_id
|
|
;
|
|
|
|
|
|
|
|
|
|
|
|
INSERT INTO compta.t_pole_rule_pole(pole_rule_id, pole_id, taux)
|
|
SELECT 0,0,1
|
|
WHERE 0 NOT IN (SELECT pole_rule_id FROM compta.t_pole_rule_pole)
|
|
;
|
|
|
|
|
|
UPDATE compta.t_pole_rule_pole SET
|
|
pole_code = t_pole.code,
|
|
pole_texte = t_pole.texte,
|
|
pole_section_id = t_pole.section_id,
|
|
pole_section_code = t_pole.section_code,
|
|
pole_section_texte = t_pole.section_texte
|
|
FROM compta.t_pole
|
|
WHERE t_pole_rule_pole.pole_id = t_pole.oid AND
|
|
(
|
|
t_pole_rule_pole.pole_code IS DISTINCT FROM t_pole.code OR
|
|
t_pole_rule_pole.pole_texte IS DISTINCT FROM t_pole.texte OR
|
|
t_pole_rule_pole.pole_section_id IS DISTINCT FROM t_pole.section_id OR
|
|
t_pole_rule_pole.pole_section_code IS DISTINCT FROM t_pole.section_code OR
|
|
t_pole_rule_pole.pole_section_texte IS DISTINCT FROM t_pole.section_texte
|
|
)
|
|
;
|
|
|
|
INSERT INTO compta.p_oids (code_table, oid)
|
|
SELECT 'pole', t_pole_rule_pole.pole_id
|
|
FROM compta.p_historique_ecritures_total
|
|
JOIN compta.t_pole_rule_pole ON t_pole_rule_pole.pole_rule_id = p_historique_ecritures_total.pole_rule_id
|
|
WHERE t_pole_rule_pole.pole_id NOT IN (SELECT oid FROM compta.p_oids WHERE code_table = 'pole')
|
|
GROUP BY 2;
|
|
|
|
|
|
RETURN 'OK';
|
|
END;
|