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.
373 lines
24 KiB
373 lines
24 KiB
return: text
|
|
lang: plpgsql
|
|
parameters:
|
|
p0:
|
|
type: text
|
|
name: i_table
|
|
src: |
|
|
DECLARE
|
|
_taux_int1 numeric(5,2);
|
|
_taux_int0 numeric(5,2);
|
|
_sqlcmd text;
|
|
BEGIN
|
|
INSERT INTO activite.t_divers (code, texte, valeur, description, valeur2)
|
|
SELECT
|
|
'VENTGHS',
|
|
'Ventilation GHS',
|
|
'0',
|
|
'0=Prix moyen par jour, 1=Poids donné au jour de l''intervention',
|
|
''
|
|
WHERE 'VENTGHS' NOT IN (SELECT code FROM activite.t_divers);
|
|
INSERT INTO activite.t_divers (code, texte, valeur, description, valeur2)
|
|
SELECT
|
|
'VENTGHSPINT',
|
|
'Ventilation GHS. Poids jour intervention',
|
|
'000',
|
|
'Pourcentage',
|
|
''
|
|
WHERE 'VENTGHSPINT' NOT IN (SELECT code FROM activite.t_divers);
|
|
-- Spécialite selon specialite pmsi
|
|
IF NOT EXISTS (SELECT * FROM activite.t_divers WHERE code = 'VENTGHS' AND valeur = '1') THEN
|
|
return 'Pas de ventilation à faire';
|
|
END IF;
|
|
IF NOT EXISTS (SELECT * FROM activite.t_divers WHERE code = 'VENTGHSPINT') THEN
|
|
return 'Pas de taux paramétré';
|
|
END IF;
|
|
_taux_int1 = base.cti_to_number((SELECT valeur FROM activite.t_divers WHERE code = 'VENTGHSPINT')) / 100.00;
|
|
IF _taux_int1 <= 0 THEN
|
|
return 'Pas de taux paramétré';
|
|
END IF;
|
|
IF _taux_int1 > 1 THEN
|
|
return 'Taux incorrect';
|
|
END IF;
|
|
_taux_int0 = 1 - _taux_int1;
|
|
|
|
_sqlcmd = '
|
|
DROP TABLE IF EXISTS w_ventilation_inter';
|
|
|
|
EXECUTE _sqlcmd;
|
|
IF (i_table NOT LIKE '%encours%') THEN
|
|
_sqlcmd = '
|
|
CREATE TEMP TABLE w_ventilation_inter AS
|
|
SELECT
|
|
p_factures.sejour_id,
|
|
p_factures_lignes_c.no_facture,
|
|
p_factures_lignes_c.date_debut,
|
|
p_factures_lignes_c.nb_prestation,
|
|
montant_facture,
|
|
montant_facture_0,
|
|
montant_facture_1,
|
|
montant_facture_2,
|
|
montant_facture_22,
|
|
montant_comptabilise,
|
|
montant_comptabilise_0,
|
|
montant_comptabilise_1,
|
|
montant_comptabilise_2,
|
|
montant_comptabilise_22,
|
|
montant_encours,
|
|
montant_encours_0,
|
|
montant_encours_1,
|
|
montant_encours_2,
|
|
montant_encours_22,
|
|
''0''::text AS est_date_intervention,
|
|
p_factures_lignes_c.CTID AS from_ctid
|
|
FROM activite.p_factures_lignes_c p_factures_lignes_c
|
|
JOIN activite.p_factures ON p_factures_lignes_c.no_facture = p_factures.no_facture AND p_factures.date_fin > p_factures.date_debut
|
|
JOIN activite.t_prestations ON prestation_id = t_prestations.oid AND t_prestations.code = ''GHS''
|
|
WHERE sejour_id IN (SELECT sejour_id FROM activite.p_intervention)
|
|
ORDER BY 1,2,3;';
|
|
ELSE
|
|
_sqlcmd = '
|
|
CREATE TEMP TABLE w_ventilation_inter AS
|
|
SELECT
|
|
p_factures.sejour_id,
|
|
p_factures_lignes_c.no_facture,
|
|
p_factures_lignes_c.date_debut,
|
|
p_factures_lignes_c.nb_prestation,
|
|
0::numeric AS montant_facture,
|
|
0::numeric AS montant_facture_0,
|
|
0::numeric AS montant_facture_1,
|
|
0::numeric AS montant_facture_2,
|
|
0::numeric AS montant_facture_22,
|
|
0::numeric AS montant_comptabilise,
|
|
0::numeric AS montant_comptabilise_0,
|
|
0::numeric AS montant_comptabilise_1,
|
|
0::numeric AS montant_comptabilise_2,
|
|
0::numeric AS montant_comptabilise_22,
|
|
montant_encours,
|
|
montant_encours_0,
|
|
montant_encours_1,
|
|
montant_encours_2,
|
|
montant_encours_22,
|
|
''0''::text AS est_date_intervention,
|
|
p_factures_lignes_c.CTID AS from_ctid
|
|
FROM activite.p_factures_lignes_c p_factures_lignes_c
|
|
JOIN activite.p_factures ON p_factures_lignes_c.no_facture = p_factures.no_facture AND p_factures.date_fin > p_factures.date_debut
|
|
JOIN activite.t_prestations ON prestation_id = t_prestations.oid AND t_prestations.code = ''GHS''
|
|
WHERE sejour_id IN (SELECT sejour_id FROM activite.p_intervention)
|
|
ORDER BY 1,2,3;';
|
|
END IF;
|
|
|
|
_sqlcmd = replace(_sqlcmd,'activite.p_factures_lignes_c',i_table);
|
|
EXECUTE _sqlcmd;
|
|
_sqlcmd = '
|
|
CREATE INDEX w_ventilation_inter_i1 ON w_ventilation_inter USING btree (sejour_id);';
|
|
|
|
EXECUTE _sqlcmd;
|
|
|
|
_sqlcmd = '
|
|
UPDATE w_ventilation_inter
|
|
SET est_date_intervention = ''1''
|
|
FROM activite.p_intervention
|
|
WHERE p_intervention.sejour_id = w_ventilation_inter.sejour_id AND
|
|
p_intervention.date_debut = w_ventilation_inter.date_debut;';
|
|
|
|
EXECUTE _sqlcmd;
|
|
_sqlcmd = '
|
|
DROP TABLE IF EXISTS w_ventilation_total_inter;';
|
|
|
|
EXECUTE _sqlcmd;
|
|
|
|
_sqlcmd = '
|
|
CREATE TEMP TABLE w_ventilation_total_inter AS
|
|
SELECT no_facture,
|
|
MAX(CASE WHEN est_date_intervention = ''0'' AND nb_prestation <> 0 THEN from_ctid ELSE NULL END) AS last_ctid,
|
|
MAX(CASE WHEN est_date_intervention = ''0'' THEN from_ctid ELSE NULL END) AS last_ctid_2,
|
|
count(*) AS nb,
|
|
SUM(CASE WHEN est_date_intervention = ''0'' THEN 1 ELSE 0 END) AS nb_int0,
|
|
SUM(CASE WHEN est_date_intervention = ''1'' THEN 1 ELSE 0 END) AS nb_int1,
|
|
SUM(montant_facture) AS total_facture,
|
|
SUM(montant_facture_0) AS total_facture_0,
|
|
SUM(montant_facture_1) AS total_facture_1,
|
|
SUM(montant_facture_2) AS total_facture_2,
|
|
SUM(montant_facture_22) AS total_facture_22,
|
|
SUM(montant_comptabilise) AS total_comptabilise,
|
|
SUM(montant_comptabilise_0) AS total_comptabilise_0,
|
|
SUM(montant_comptabilise_1) AS total_comptabilise_1,
|
|
SUM(montant_comptabilise_2) AS total_comptabilise_2,
|
|
SUM(montant_comptabilise_22) AS total_comptabilise_22,
|
|
SUM(montant_encours) AS total_encours,
|
|
SUM(montant_encours_0) AS total_encours_0,
|
|
SUM(montant_encours_1) AS total_encours_1,
|
|
SUM(montant_encours_2) AS total_encours_2,
|
|
SUM(montant_encours_22) AS total_encours_22,
|
|
0::numeric(15,2) AS montant_facture_int0,
|
|
0::numeric(15,2) AS montant_facture_int0_last,
|
|
0::numeric(15,2) AS montant_facture_int1,
|
|
0::numeric(15,2) AS montant_facture_0_int0,
|
|
0::numeric(15,2) AS montant_facture_0_int0_last,
|
|
0::numeric(15,2) AS montant_facture_0_int1,
|
|
0::numeric(15,2) AS montant_facture_1_int0,
|
|
0::numeric(15,2) AS montant_facture_1_int0_last,
|
|
0::numeric(15,2) AS montant_facture_1_int1,
|
|
0::numeric(15,2) AS montant_facture_2_int0,
|
|
0::numeric(15,2) AS montant_facture_2_int0_last,
|
|
0::numeric(15,2) AS montant_facture_2_int1,
|
|
0::numeric(15,2) AS montant_facture_22_int0,
|
|
0::numeric(15,2) AS montant_facture_22_int0_last,
|
|
0::numeric(15,2) AS montant_facture_22_int1,
|
|
0::numeric(15,2) AS montant_comptabilise_int0,
|
|
0::numeric(15,2) AS montant_comptabilise_int0_last,
|
|
0::numeric(15,2) AS montant_comptabilise_int1,
|
|
0::numeric(15,2) AS montant_comptabilise_0_int0,
|
|
0::numeric(15,2) AS montant_comptabilise_0_int0_last,
|
|
0::numeric(15,2) AS montant_comptabilise_0_int1,
|
|
0::numeric(15,2) AS montant_comptabilise_1_int0,
|
|
0::numeric(15,2) AS montant_comptabilise_1_int0_last,
|
|
0::numeric(15,2) AS montant_comptabilise_1_int1,
|
|
0::numeric(15,2) AS montant_comptabilise_2_int0,
|
|
0::numeric(15,2) AS montant_comptabilise_2_int0_last,
|
|
0::numeric(15,2) AS montant_comptabilise_2_int1,
|
|
0::numeric(15,2) AS montant_comptabilise_22_int0,
|
|
0::numeric(15,2) AS montant_comptabilise_22_int0_last,
|
|
0::numeric(15,2) AS montant_comptabilise_22_int1,
|
|
0::numeric(15,2) AS montant_encours_int0,
|
|
0::numeric(15,2) AS montant_encours_int0_last,
|
|
0::numeric(15,2) AS montant_encours_int1,
|
|
0::numeric(15,2) AS montant_encours_0_int0,
|
|
0::numeric(15,2) AS montant_encours_0_int0_last,
|
|
0::numeric(15,2) AS montant_encours_0_int1,
|
|
0::numeric(15,2) AS montant_encours_1_int0,
|
|
0::numeric(15,2) AS montant_encours_1_int0_last,
|
|
0::numeric(15,2) AS montant_encours_1_int1,
|
|
0::numeric(15,2) AS montant_encours_2_int0,
|
|
0::numeric(15,2) AS montant_encours_2_int0_last,
|
|
0::numeric(15,2) AS montant_encours_2_int1,
|
|
0::numeric(15,2) AS montant_encours_22_int0,
|
|
0::numeric(15,2) AS montant_encours_22_int0_last,
|
|
0::numeric(15,2) AS montant_encours_22_int1
|
|
FROM w_ventilation_inter
|
|
GROUP BY 1
|
|
having count(*) > 1 AND
|
|
SUM(CASE WHEN est_date_intervention = ''1'' THEN 1 ELSE 0 END) > 0 AND
|
|
SUM(CASE WHEN est_date_intervention = ''0'' THEN 1 ELSE 0 END) > 0 AND
|
|
SUM(CASE WHEN nb_prestation <> 0 THEN 1 ELSE 0 END) = 1
|
|
ORDER BY 1;';
|
|
|
|
EXECUTE _sqlcmd;
|
|
|
|
_sqlcmd = '
|
|
UPDATE w_ventilation_total_inter SET
|
|
last_ctid = last_ctid_2
|
|
WHERE last_ctid IS NULL;';
|
|
|
|
EXECUTE _sqlcmd;
|
|
|
|
_sqlcmd = '
|
|
UPDATE w_ventilation_total_inter SET
|
|
montant_facture_int0 = round(total_facture * (0.4) / nb,2),
|
|
montant_facture_int1 = round(total_facture * (0.6) / nb_int1,2),
|
|
montant_facture_0_int0 = round(total_facture_0 * (0.4) / nb,2),
|
|
montant_facture_0_int1 = round(total_facture_0 * (0.6) / nb_int1,2),
|
|
montant_facture_1_int0 = round(total_facture_1 * (0.4) / nb,2),
|
|
montant_facture_1_int1 = round(total_facture_1 * (0.6) / nb_int1,2),
|
|
montant_facture_2_int0 = round(total_facture_2 * (0.4) / nb,2),
|
|
montant_facture_2_int1 = round(total_facture_2 * (0.6) / nb_int1,2),
|
|
montant_facture_22_int0 = round(total_facture_22 * (0.4) / nb,2),
|
|
montant_facture_22_int1 = round(total_facture_22 * (0.6) / nb_int1,2),
|
|
|
|
montant_comptabilise_int0 = round(total_comptabilise * (0.4) / nb,2),
|
|
montant_comptabilise_int1 = round(total_comptabilise * (0.6) / nb_int1,2),
|
|
montant_comptabilise_0_int0 = round(total_comptabilise_0 * (0.4) / nb,2),
|
|
montant_comptabilise_0_int1 = round(total_comptabilise_0 * (0.6) / nb_int1,2),
|
|
montant_comptabilise_1_int0 = round(total_comptabilise_1 * (0.4) / nb,2),
|
|
montant_comptabilise_1_int1 = round(total_comptabilise_1 * (0.6) / nb_int1,2),
|
|
montant_comptabilise_2_int0 = round(total_comptabilise_2 * (0.4) / nb,2),
|
|
montant_comptabilise_2_int1 = round(total_comptabilise_2 * (0.6) / nb_int1,2),
|
|
montant_comptabilise_22_int0 = round(total_comptabilise_22 * (0.4) / nb,2),
|
|
montant_comptabilise_22_int1 = round(total_comptabilise_22 * (0.6) / nb_int1,2),
|
|
|
|
montant_encours_int0 = round(total_encours * (0.4) / nb,2),
|
|
montant_encours_int1 = round(total_encours * (0.6) / nb_int1,2),
|
|
montant_encours_0_int0 = round(total_encours_0 * (0.4) / nb,2),
|
|
montant_encours_0_int1 = round(total_encours_0 * (0.6) / nb_int1,2),
|
|
montant_encours_1_int0 = round(total_encours_1 * (0.4) / nb,2),
|
|
montant_encours_1_int1 = round(total_encours_1 * (0.6) / nb_int1,2),
|
|
montant_encours_2_int0 = round(total_encours_2 * (0.4) / nb,2),
|
|
montant_encours_2_int1 = round(total_encours_2 * (0.6) / nb_int1,2),
|
|
montant_encours_22_int0 = round(total_encours_22 * (0.4) / nb,2),
|
|
montant_encours_22_int1 = round(total_encours_22 * (0.6) / nb_int1,2)
|
|
;';
|
|
|
|
_sqlcmd = replace(_sqlcmd,'(0.6)',_taux_int1);
|
|
_sqlcmd = replace(_sqlcmd,'(0.4)',_taux_int0);
|
|
EXECUTE _sqlcmd;
|
|
|
|
_sqlcmd = '
|
|
UPDATE w_ventilation_total_inter SET
|
|
montant_facture_int0_last = total_facture - ((nb-1) * montant_facture_int0) - (nb_int1 * montant_facture_int1),
|
|
montant_facture_0_int0_last = total_facture_0 - ((nb-1) * montant_facture_0_int0) - (nb_int1 * montant_facture_0_int1),
|
|
montant_facture_1_int0_last = total_facture_1 - ((nb-1) * montant_facture_1_int0) - (nb_int1 * montant_facture_1_int1),
|
|
montant_facture_2_int0_last = total_facture_2 - ((nb-1) * montant_facture_2_int0) - (nb_int1 * montant_facture_2_int1),
|
|
montant_facture_22_int0_last = total_facture_22 - ((nb-1) * montant_facture_22_int0) - (nb_int1 * montant_facture_22_int1),
|
|
montant_comptabilise_int0_last = total_comptabilise - ((nb-1) * montant_comptabilise_int0) - (nb_int1 * montant_comptabilise_int1),
|
|
montant_comptabilise_0_int0_last = total_comptabilise_0 - ((nb-1) * montant_comptabilise_0_int0) - (nb_int1 * montant_comptabilise_0_int1),
|
|
montant_comptabilise_1_int0_last = total_comptabilise_1 - ((nb-1) * montant_comptabilise_1_int0) - (nb_int1 * montant_comptabilise_1_int1),
|
|
montant_comptabilise_2_int0_last = total_comptabilise_2 - ((nb-1) * montant_comptabilise_2_int0) - (nb_int1 * montant_comptabilise_2_int1),
|
|
montant_comptabilise_22_int0_last = total_comptabilise_22 - ((nb-1) * montant_comptabilise_22_int0) - (nb_int1 * montant_comptabilise_22_int1),
|
|
montant_encours_int0_last = total_encours - ((nb-1) * montant_encours_int0) - (nb_int1 * montant_encours_int1),
|
|
montant_encours_0_int0_last = total_encours_0 - ((nb-1) * montant_encours_0_int0) - (nb_int1 * montant_encours_0_int1),
|
|
montant_encours_1_int0_last = total_encours_1 - ((nb-1) * montant_encours_1_int0) - (nb_int1 * montant_encours_1_int1),
|
|
montant_encours_2_int0_last = total_encours_2 - ((nb-1) * montant_encours_2_int0) - (nb_int1 * montant_encours_2_int1),
|
|
montant_encours_22_int0_last = total_encours_22 - ((nb-1) * montant_encours_22_int0) - (nb_int1 * montant_encours_22_int1),
|
|
montant_facture_int1 = montant_facture_int0 + montant_facture_int1,
|
|
montant_facture_0_int1 = montant_facture_0_int0 + montant_facture_0_int1,
|
|
montant_facture_1_int1 = montant_facture_1_int0 + montant_facture_1_int1,
|
|
montant_facture_2_int1 = montant_facture_2_int0 + montant_facture_2_int1,
|
|
montant_facture_22_int1 = montant_facture_22_int0 + montant_facture_22_int1,
|
|
montant_comptabilise_int1 = montant_comptabilise_int0 + montant_comptabilise_int1,
|
|
montant_comptabilise_0_int1 = montant_comptabilise_0_int0 + montant_comptabilise_0_int1,
|
|
montant_comptabilise_1_int1 = montant_comptabilise_1_int0 + montant_comptabilise_1_int1,
|
|
montant_comptabilise_2_int1 = montant_comptabilise_2_int0 + montant_comptabilise_2_int1,
|
|
montant_comptabilise_22_int1 = montant_comptabilise_22_int0 + montant_comptabilise_22_int1,
|
|
montant_encours_int1 = montant_encours_int0 + montant_encours_int1,
|
|
montant_encours_0_int1 = montant_encours_0_int0 + montant_encours_0_int1,
|
|
montant_encours_1_int1 = montant_encours_1_int0 + montant_encours_1_int1,
|
|
montant_encours_2_int1 = montant_encours_2_int0 + montant_encours_2_int1,
|
|
montant_encours_22_int1 = montant_encours_22_int0 + montant_encours_22_int1
|
|
;';
|
|
|
|
EXECUTE _sqlcmd;
|
|
|
|
_sqlcmd = '
|
|
CREATE INDEX w_ventilation_total_inter_i1 ON w_ventilation_total_inter USING btree (no_facture);';
|
|
|
|
EXECUTE _sqlcmd;
|
|
|
|
IF (i_table NOT LIKE '%encours%') THEN
|
|
_sqlcmd = '
|
|
UPDATE activite.p_factures_lignes_c p_factures_lignes_c SET
|
|
montant_facture = CASE WHEN est_date_intervention = ''1'' THEN montant_facture_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_facture_int0_last ELSE montant_facture_int0 END,
|
|
montant_facture_0 = CASE WHEN est_date_intervention = ''1'' THEN montant_facture_0_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_facture_0_int0_last ELSE montant_facture_0_int0 END,
|
|
montant_facture_1 = CASE WHEN est_date_intervention = ''1'' THEN montant_facture_1_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_facture_1_int0_last ELSE montant_facture_1_int0 END,
|
|
montant_facture_2 = CASE WHEN est_date_intervention = ''1'' THEN montant_facture_2_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_facture_2_int0_last ELSE montant_facture_2_int0 END,
|
|
montant_facture_22 = CASE WHEN est_date_intervention = ''1'' THEN montant_facture_22_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_facture_22_int0_last ELSE montant_facture_22_int0 END,
|
|
montant_comptabilise = CASE WHEN est_date_intervention = ''1'' THEN montant_comptabilise_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_comptabilise_int0_last ELSE montant_comptabilise_int0 END,
|
|
montant_comptabilise_0 = CASE WHEN est_date_intervention = ''1'' THEN montant_comptabilise_0_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_comptabilise_0_int0_last ELSE montant_comptabilise_0_int0 END,
|
|
montant_comptabilise_1 = CASE WHEN est_date_intervention = ''1'' THEN montant_comptabilise_1_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_comptabilise_1_int0_last ELSE montant_comptabilise_1_int0 END,
|
|
montant_comptabilise_2 = CASE WHEN est_date_intervention = ''1'' THEN montant_comptabilise_2_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_comptabilise_2_int0_last ELSE montant_comptabilise_2_int0 END,
|
|
montant_comptabilise_22 = CASE WHEN est_date_intervention = ''1'' THEN montant_comptabilise_22_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_comptabilise_22_int0_last ELSE montant_comptabilise_22_int0 END,
|
|
montant_encours = CASE WHEN est_date_intervention = ''1'' THEN montant_encours_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_int0_last ELSE montant_encours_int0 END,
|
|
montant_encours_0 = CASE WHEN est_date_intervention = ''1'' THEN montant_encours_0_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_0_int0_last ELSE montant_encours_0_int0 END,
|
|
montant_encours_1 = CASE WHEN est_date_intervention = ''1'' THEN montant_encours_1_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_1_int0_last ELSE montant_encours_1_int0 END,
|
|
montant_encours_2 = CASE WHEN est_date_intervention = ''1'' THEN montant_encours_2_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_2_int0_last ELSE montant_encours_2_int0 END,
|
|
montant_encours_22 = CASE WHEN est_date_intervention = ''1'' THEN montant_encours_22_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_22_int0_last ELSE montant_encours_22_int0 END
|
|
|
|
FROM w_ventilation_total_inter, w_ventilation_inter
|
|
WHERE w_ventilation_inter.from_ctid = p_factures_lignes_c.CTID AND
|
|
w_ventilation_total_inter.no_facture = p_factures_lignes_c.no_facture AND
|
|
(
|
|
p_factures_lignes_c.montant_facture IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_facture_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_facture_int0_last ELSE montant_facture_int0 END OR
|
|
p_factures_lignes_c.montant_facture_0 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_facture_0_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_facture_0_int0_last ELSE montant_facture_0_int0 END OR
|
|
p_factures_lignes_c.montant_facture_1 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_facture_1_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_facture_1_int0_last ELSE montant_facture_1_int0 END OR
|
|
p_factures_lignes_c.montant_facture_2 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_facture_2_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_facture_2_int0_last ELSE montant_facture_2_int0 END OR
|
|
p_factures_lignes_c.montant_facture_22 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_facture_22_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_facture_22_int0_last ELSE montant_facture_22_int0 END OR
|
|
p_factures_lignes_c.montant_comptabilise IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_comptabilise_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_comptabilise_int0_last ELSE montant_comptabilise_int0 END OR
|
|
p_factures_lignes_c.montant_comptabilise_0 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_comptabilise_0_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_comptabilise_0_int0_last ELSE montant_comptabilise_0_int0 END OR
|
|
p_factures_lignes_c.montant_comptabilise_1 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_comptabilise_1_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_comptabilise_1_int0_last ELSE montant_comptabilise_1_int0 END OR
|
|
p_factures_lignes_c.montant_comptabilise_2 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_comptabilise_2_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_comptabilise_2_int0_last ELSE montant_comptabilise_2_int0 END OR
|
|
p_factures_lignes_c.montant_comptabilise_22 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_comptabilise_22_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_comptabilise_22_int0_last ELSE montant_comptabilise_22_int0 END OR
|
|
p_factures_lignes_c.montant_encours IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_encours_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_int0_last ELSE montant_encours_int0 END OR
|
|
p_factures_lignes_c.montant_encours_0 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_encours_0_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_0_int0_last ELSE montant_encours_0_int0 END OR
|
|
p_factures_lignes_c.montant_encours_1 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_encours_1_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_1_int0_last ELSE montant_encours_1_int0 END OR
|
|
p_factures_lignes_c.montant_encours_2 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_encours_2_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_2_int0_last ELSE montant_encours_2_int0 END OR
|
|
p_factures_lignes_c.montant_encours_22 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_encours_22_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_22_int0_last ELSE montant_encours_22_int0 END
|
|
)
|
|
;';
|
|
ELSE
|
|
_sqlcmd = '
|
|
UPDATE activite.p_factures_lignes_c p_factures_lignes_c SET
|
|
montant_encours = CASE WHEN est_date_intervention = ''1'' THEN montant_encours_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_int0_last ELSE montant_encours_int0 END,
|
|
montant_encours_0 = CASE WHEN est_date_intervention = ''1'' THEN montant_encours_0_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_0_int0_last ELSE montant_encours_0_int0 END,
|
|
montant_encours_1 = CASE WHEN est_date_intervention = ''1'' THEN montant_encours_1_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_1_int0_last ELSE montant_encours_1_int0 END,
|
|
montant_encours_2 = CASE WHEN est_date_intervention = ''1'' THEN montant_encours_2_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_2_int0_last ELSE montant_encours_2_int0 END,
|
|
montant_encours_22 = CASE WHEN est_date_intervention = ''1'' THEN montant_encours_22_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_22_int0_last ELSE montant_encours_22_int0 END
|
|
|
|
FROM w_ventilation_total_inter, w_ventilation_inter
|
|
WHERE w_ventilation_inter.from_ctid = p_factures_lignes_c.CTID AND
|
|
w_ventilation_total_inter.no_facture = p_factures_lignes_c.no_facture AND
|
|
(
|
|
p_factures_lignes_c.montant_encours IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_encours_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_int0_last ELSE montant_encours_int0 END OR
|
|
p_factures_lignes_c.montant_encours_0 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_encours_0_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_0_int0_last ELSE montant_encours_0_int0 END OR
|
|
p_factures_lignes_c.montant_encours_1 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_encours_1_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_1_int0_last ELSE montant_encours_1_int0 END OR
|
|
p_factures_lignes_c.montant_encours_2 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_encours_2_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_2_int0_last ELSE montant_encours_2_int0 END OR
|
|
p_factures_lignes_c.montant_encours_22 IS DISTINCT FROM CASE WHEN est_date_intervention = ''1'' THEN montant_encours_22_int1 WHEN p_factures_lignes_c.CTID = last_ctid THEN montant_encours_22_int0_last ELSE montant_encours_22_int0 END
|
|
)
|
|
;';
|
|
END IF;
|
|
|
|
_sqlcmd = replace(_sqlcmd,'activite.p_factures_lignes_c',i_table);
|
|
EXECUTE _sqlcmd;
|
|
_sqlcmd = '
|
|
DROP TABLE IF EXISTS w_ventilation_inter';
|
|
|
|
|
|
EXECUTE _sqlcmd;
|
|
_sqlcmd = '
|
|
DROP TABLE IF EXISTS w_ventilation_total_inter;';
|
|
|
|
EXECUTE _sqlcmd;
|
|
|
|
|
|
RETURN 'OK' ;
|
|
END;
|