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.
128 lines
6.7 KiB
128 lines
6.7 KiB
return: text
|
|
lang: plpgsql
|
|
parameters:
|
|
p0:
|
|
type: text
|
|
name: i_table_code
|
|
src: |
|
|
DECLARE
|
|
|
|
|
|
|
|
result TEXT;
|
|
BEGIN
|
|
-- Si Toutes les tables, mettre à jour oid par codes
|
|
IF (i_table_code = '*ALL' OR i_table_code = 'CPT') THEN
|
|
PERFORM base.cti_initialize_classes_by_code('compta');
|
|
PERFORM base.cti_initialize_listes_by_code('compta');
|
|
END IF;
|
|
|
|
-- Tables par tables
|
|
IF (i_table_code = 'CPT' OR i_table_code = '*ALL') THEN
|
|
INSERT INTO compta.t_classes(code, texte, table_id, is_cti, sequence)
|
|
SELECT 'REGCPT', 'Regroupement de comptes', t_listes_tables.oid, '0', 9
|
|
FROM compta.t_listes_tables
|
|
WHERE t_listes_tables.code = 'CPT' AND 9 NOT IN (SELECT sequence FROM compta.t_classes WHERE table_id IN (SELECT oid FROM compta.t_listes_tables WHERE code ='CPT'));
|
|
INSERT INTO compta.t_classes_sections(code, texte, classe_id)
|
|
SELECT DISTINCT t_comptes_generaux.numero, t_comptes_generaux.texte, t_classes.oid
|
|
FROM compta.t_comptes JOIN compta.t_comptes t_comptes_generaux ON (t_comptes.compte_general_id = t_comptes_generaux.oid),
|
|
compta.t_classes JOIN compta.t_listes_tables ON (t_listes_tables.oid = t_classes.table_id)
|
|
WHERE t_listes_tables.code = 'CPT' AND t_classes.sequence = 9 AND t_classes.code = 'REGCPT'
|
|
AND t_comptes_generaux.oid IN
|
|
(SELECT oid FROM compta.p_oids WHERE code_table IN ('comptes_c', 'comptes_h'))
|
|
AND t_comptes_generaux.numero NOT IN
|
|
(SELECT t_classes_sections.code
|
|
FROM compta.t_classes
|
|
JOIN compta.t_listes_tables ON (t_listes_tables.oid = t_classes.table_id)
|
|
JOIN compta.t_classes_sections ON (t_classes_sections.classe_id = t_classes.oid)
|
|
WHERE t_listes_tables.code = 'CPT' AND t_classes.sequence = 9 AND t_classes_sections.classe_id = t_classes.oid
|
|
);
|
|
INSERT INTO compta.t_classes_sections_elements(section_id, to_id)
|
|
SELECT t_classes_sections.oid, t_comptes.oid
|
|
FROM compta.t_comptes
|
|
JOIN compta.t_comptes t_comptes_generaux ON (t_comptes.compte_general_id = t_comptes_generaux.oid),
|
|
compta.t_classes
|
|
JOIN compta.t_listes_tables ON (t_listes_tables.oid = t_classes.table_id)
|
|
JOIN compta.t_classes_sections ON (t_classes_sections.classe_id = t_classes.oid)
|
|
WHERE t_listes_tables.code = 'CPT' AND
|
|
t_classes.sequence = 9 AND t_classes.code = 'REGCPT' AND
|
|
t_comptes_generaux.numero = t_classes_sections.code AND
|
|
t_classes_sections.oid::text || '|' || t_comptes.oid::text NOT IN (SELECT section_id::text || '|' || to_id::text FROM compta.t_classes_sections_elements ) AND
|
|
t_comptes.oid IN (SELECT oid FROM compta.p_oids WHERE code_table IN ('comptes_c', 'comptes_h', 'comptes_extra_c', 'comptes_extra_h')) AND
|
|
t_comptes.oid NOT IN (SELECT to_id
|
|
FROM compta.t_classes
|
|
JOIN compta.t_classes_sections ON (t_classes_sections.classe_id = t_classes.oid)
|
|
JOIN compta.t_classes_sections_elements ON (t_classes_sections_elements.section_id = t_classes_sections.oid)
|
|
WHERE t_classes.sequence = 9 );
|
|
|
|
-- Si compte sans signe et pas compte signé dans classe, compte signé a la section de compte sans signe
|
|
DROP TABLE IF EXISTS w_comptes_section;
|
|
CREATE TEMP TABLE w_comptes_section AS
|
|
SELECT t_classes_sections.oid AS classe_id,
|
|
t_classes_sections.oid AS section_id,
|
|
t_comptes.numero AS compte_numero,
|
|
COALESCE(t_comptes_signes.numero, t_comptes.numero) AS compte_signe_numero,
|
|
t_comptes.oid AS compte_id, COALESCE(t_comptes_signes.oid,t_comptes.oid) AS compte_signe_id
|
|
FROM compta.t_classes_sections_elements
|
|
JOIN compta.t_classes_sections ON section_id = t_classes_sections.oid
|
|
JOIN compta.t_classes ON classe_id = t_classes.oid
|
|
JOIN compta.t_listes_tables ON table_id = t_listes_tables.oid AND
|
|
t_listes_tables.code = 'CPT'
|
|
JOIN compta.t_comptes ON to_id = t_comptes.oid
|
|
LEFT JOIN compta.t_comptes t_comptes_signes ON t_comptes_signes.compte_nonsigne_id = t_comptes.oid
|
|
;
|
|
ANALYSE w_comptes_section
|
|
;
|
|
|
|
INSERT INTO compta.t_classes_sections_elements (section_id, to_id)
|
|
SELECT w_comptes_section.section_id, w_comptes_section.compte_signe_id AS to_id
|
|
FROM w_comptes_section
|
|
LEFT JOIN w_comptes_section w_comptes_section_signe ON
|
|
w_comptes_section.classe_id = w_comptes_section_signe.classe_id AND
|
|
w_comptes_section.compte_signe_id = w_comptes_section_signe.compte_id
|
|
where w_comptes_section.compte_id <> w_comptes_section.compte_signe_id AND
|
|
w_comptes_section_signe.section_id IS NULL
|
|
GROUP BY 1,2
|
|
;
|
|
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_comptes_c'', ''CPT'','''');';
|
|
END IF;
|
|
IF (i_table_code = 'JRN' OR i_table_code = '*ALL') THEN
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_journaux'', ''JRN'','''');';
|
|
END IF;
|
|
IF (i_table_code = 'SIT' OR i_table_code = '*ALL') THEN
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_sites'', ''SIT'','''');';
|
|
END IF;
|
|
IF (i_table_code = 'PAR' OR i_table_code = '*ALL') THEN
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_partenaires'', ''PAR'','''');';
|
|
END IF;
|
|
IF (i_table_code = 'ANA' OR i_table_code = '*ALL') THEN
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_sections_analytiques'', ''ANA'','''');';
|
|
END IF;
|
|
IF (i_table_code = 'CSF' OR i_table_code = '*ALL') THEN
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_lieux_cout_patient_shs'', ''CSF'',''service_facturation_section'');';
|
|
END IF;
|
|
IF (i_table_code = 'CSA' OR i_table_code = '*ALL') THEN
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_lieux_cout_patient_shs'', ''CSA'',''activite_section'');';
|
|
END IF;
|
|
IF (i_table_code = 'CSE' OR i_table_code = '*ALL') THEN
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_lieux_cout_patient_shs'', ''CSE'',''etage_section'');';
|
|
END IF;
|
|
IF (i_table_code = 'CSM' OR i_table_code = '*ALL') THEN
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_medecins_cout_patient_shs'', ''CSM'','''');';
|
|
END IF;
|
|
IF (i_table_code = 'CSG' OR i_table_code = '*ALL') THEN
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_ghm_cout_patient_shs'', ''CSG'','''');';
|
|
END IF;
|
|
IF (i_table_code = 'CSH' OR i_table_code = '*ALL') THEN
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_ghs_cout_patient_shs'', ''CSH'','''');';
|
|
END IF;
|
|
IF (i_table_code = 'CSC' OR i_table_code = '*ALL') THEN
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_elements_cout_patient_shs'', ''CSC'','''');';
|
|
END IF;
|
|
IF (i_table_code = 'CSU' OR i_table_code = '*ALL') THEN
|
|
EXECUTE 'SELECT base.cti_update_classes(''compta'', ''t_unites_oeuvre_shs'', ''CSU'','''');';
|
|
END IF;
|
|
|
|
RETURN 'OK';
|
|
END;
|