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.
632 lines
26 KiB
632 lines
26 KiB
return: text
|
|
lang: plpgsql
|
|
src: |
|
|
DECLARE
|
|
result TEXT;
|
|
_SOLDEAUTO_nb numeric;
|
|
BEGIN
|
|
|
|
|
|
-- Pour initialiser le paramétrage
|
|
INSERT INTO activite.t_divers (code, texte, valeur, description)
|
|
SELECT
|
|
'SOLDEAUTO',
|
|
'Nombre d''années pour solde automatique des factures',
|
|
'2',
|
|
'Tout séjour non soldé sorti avant sera soldé (-1 si pas de solde auto)'
|
|
WHERE 'SOLDEAUTO' NOT IN (SELECT code FROM activite.t_divers);
|
|
|
|
INSERT INTO activite.t_divers (code, texte, valeur, description)
|
|
SELECT
|
|
'SOLDEAUTOHON',
|
|
'Solde automatique des honoraires si solde etablissement à ',
|
|
'0',
|
|
'Si la partie clinique est soldée, il faut solder les honoaires'
|
|
WHERE 'SOLDEAUTOHON' NOT IN (SELECT code FROM activite.t_divers);
|
|
|
|
|
|
_SOLDEAUTO_nb = (SELECT base.cti_to_number(valeur)
|
|
FROM activite.t_divers
|
|
WHERE t_divers.code = 'SOLDEAUTO'
|
|
LIMIT 1)
|
|
;
|
|
|
|
-- Solde des parts patient si solde total = 0 et solde cli/hon <> 0
|
|
DROP TABLE IF EXISTS w_facture_solde_auto_0;
|
|
CREATE TEMP TABLE w_facture_solde_auto_0 AS
|
|
SELECT
|
|
no_facture,
|
|
date_fin,
|
|
date_vente AS date_solde
|
|
FROM activite.p_factures
|
|
WHERE
|
|
(
|
|
(
|
|
p_factures.montant_comptabilise_0_c + p_factures.montant_comptabilise_0_h
|
|
- p_factures.montant_regle_0_c - p_factures.montant_regle_0_h
|
|
) = 0
|
|
) AND
|
|
p_factures.montant_comptabilise_0_c <> p_factures.montant_regle_0_c
|
|
;
|
|
|
|
|
|
ANALYSE w_facture_solde_auto_0;
|
|
|
|
UPDATE w_facture_solde_auto_0
|
|
SET date_solde = subview.date_solde
|
|
FROM
|
|
(
|
|
SELECT p_factures_soldes_c.no_facture,
|
|
MAX(date_comptable) AS date_solde
|
|
FROM activite.p_factures_soldes_c
|
|
JOIN w_facture_solde_auto_0 ON w_facture_solde_auto_0.no_facture = p_factures_soldes_c.no_facture
|
|
WHERE montant_regle_0 <> 0
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE w_facture_solde_auto_0.no_facture = subview.no_facture AND
|
|
subview.date_solde > w_facture_solde_auto_0.date_solde
|
|
;
|
|
|
|
UPDATE w_facture_solde_auto_0
|
|
SET date_solde = subview.date_solde
|
|
FROM
|
|
(
|
|
SELECT p_factures_soldes_h.no_facture,
|
|
MAX(date_comptable) AS date_solde
|
|
FROM activite.p_factures_soldes_h
|
|
JOIN w_facture_solde_auto_0 ON w_facture_solde_auto_0.no_facture = p_factures_soldes_h.no_facture
|
|
WHERE montant_regle_0 <> 0
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE w_facture_solde_auto_0.no_facture = subview.no_facture AND
|
|
subview.date_solde > w_facture_solde_auto_0.date_solde
|
|
;
|
|
|
|
|
|
|
|
INSERT INTO activite.p_factures_soldes_c(
|
|
facture_id,
|
|
no_facture,
|
|
date_comptable,
|
|
prestation_id,
|
|
rubrique_comptabilisee_id,
|
|
montant_comptabilise,
|
|
montant_comptabilise_0,
|
|
montant_comptabilise_1,
|
|
montant_comptabilise_2,
|
|
montant_comptabilise_22,
|
|
montant_regle,
|
|
montant_regle_0,
|
|
montant_regle_1,
|
|
montant_regle_2,
|
|
montant_regle_22,
|
|
od_non_comptabilise
|
|
)
|
|
SELECT
|
|
facture_id,
|
|
p_factures_soldes_c.no_facture,
|
|
date_solde,
|
|
prestation_id,
|
|
rubrique_comptabilisee_id,
|
|
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,
|
|
|
|
SUM(montant_comptabilise_0-montant_regle_0) AS montant_regle,
|
|
SUM(montant_comptabilise_0-montant_regle_0) AS montant_regle_0,
|
|
0::numeric AS montant_regle_1,
|
|
0::numeric AS montant_regle_2,
|
|
0::numeric AS montant_regle_22,
|
|
'1' AS od_non_comptabilise
|
|
FROM activite.p_factures_soldes_c
|
|
JOIN w_facture_solde_auto_0 ON w_facture_solde_auto_0.no_facture = p_factures_soldes_c.no_facture
|
|
GROUP BY 1,2,3,4,5,6
|
|
HAVING SUM(montant_comptabilise_0-montant_regle_0) <> 0
|
|
;
|
|
|
|
INSERT INTO activite.p_factures_soldes_h(
|
|
facture_id,
|
|
no_facture,
|
|
date_comptable,
|
|
medecin_comptabilise_id,
|
|
prestation_id,
|
|
rubrique_comptabilisee_id,
|
|
montant_comptabilise,
|
|
montant_comptabilise_0,
|
|
montant_comptabilise_1,
|
|
montant_comptabilise_2,
|
|
montant_comptabilise_22,
|
|
montant_regle,
|
|
montant_regle_0,
|
|
montant_regle_1,
|
|
montant_regle_2,
|
|
montant_regle_22,
|
|
od_non_comptabilise
|
|
)
|
|
SELECT
|
|
facture_id,
|
|
p_factures_soldes_h.no_facture,
|
|
date_solde,
|
|
medecin_comptabilise_id,
|
|
prestation_id,
|
|
rubrique_comptabilisee_id,
|
|
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,
|
|
|
|
SUM(montant_comptabilise_0-montant_regle_0) AS montant_regle,
|
|
SUM(montant_comptabilise_0-montant_regle_0) AS montant_regle_0,
|
|
0::numeric AS montant_regle_1,
|
|
0::numeric AS montant_regle_2,
|
|
0::numeric AS montant_regle_22,
|
|
'1' AS od_non_comptabilise
|
|
FROM activite.p_factures_soldes_h
|
|
JOIN w_facture_solde_auto_0 ON w_facture_solde_auto_0.no_facture = p_factures_soldes_h.no_facture
|
|
GROUP BY 1,2,3,4,5,6,7
|
|
HAVING SUM(montant_comptabilise_0-montant_regle_0) <> 0
|
|
;
|
|
|
|
|
|
|
|
UPDATE activite.p_factures SET
|
|
montant_regle_c = montant_comptabilise_0_c + montant_regle_1_c + montant_regle_2_c + montant_regle_22_c,
|
|
montant_regle_0_c = montant_comptabilise_0_c,
|
|
montant_regle_h = montant_comptabilise_0_h + montant_regle_1_h + montant_regle_2_h + montant_regle_22_h,
|
|
montant_regle_0_h = montant_comptabilise_0_h
|
|
FROM w_facture_solde_auto_0
|
|
WHERE w_facture_solde_auto_0.no_facture = p_factures.no_facture
|
|
;
|
|
|
|
-- Solde automatique selon age solde
|
|
IF _SOLDEAUTO_nb >= 2 THEN
|
|
DROP TABLE IF EXISTS w_facture_solde_auto;
|
|
CREATE TEMP TABLE w_facture_solde_auto AS
|
|
SELECT
|
|
no_facture,date_fin,
|
|
date(date_trunc('year',now())-(t_divers.valeur||' years')::interval-interval '1 day') AS date_solde
|
|
FROM activite.p_factures
|
|
JOIN activite.t_divers ON t_divers.code = 'SOLDEAUTO'
|
|
WHERE date_fin < date(date_trunc('year',now())-(t_divers.valeur||' years')::interval)
|
|
;
|
|
|
|
|
|
|
|
INSERT INTO activite.p_factures_soldes_c(
|
|
facture_id,
|
|
no_facture,
|
|
date_comptable,
|
|
prestation_id,
|
|
rubrique_comptabilisee_id,
|
|
montant_comptabilise,
|
|
montant_comptabilise_0,
|
|
montant_comptabilise_1,
|
|
montant_comptabilise_2,
|
|
montant_comptabilise_22,
|
|
montant_regle,
|
|
montant_regle_0,
|
|
montant_regle_1,
|
|
montant_regle_2,
|
|
montant_regle_22,
|
|
od_non_comptabilise
|
|
)
|
|
SELECT
|
|
facture_id,
|
|
p_factures_soldes_c.no_facture,
|
|
MAX(GREATEST(date_comptable,date_solde)),
|
|
prestation_id,
|
|
rubrique_comptabilisee_id,
|
|
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,
|
|
|
|
SUM(montant_comptabilise_0-montant_regle_0)+
|
|
SUM(montant_comptabilise_1-montant_regle_1)+
|
|
SUM(montant_comptabilise_2-montant_regle_2)+
|
|
SUM(montant_comptabilise_22-montant_regle_22) AS montant_regle,
|
|
SUM(montant_comptabilise_0-montant_regle_0) AS montant_regle_0,
|
|
SUM(montant_comptabilise_1-montant_regle_1) AS montant_regle_1,
|
|
SUM(montant_comptabilise_2-montant_regle_2) AS montant_regle_2,
|
|
SUM(montant_comptabilise_22-montant_regle_22) AS montant_regle_22,
|
|
'1' AS od_non_comptabilise
|
|
FROM activite.p_factures_soldes_c
|
|
JOIN w_facture_solde_auto ON w_facture_solde_auto.no_facture = p_factures_soldes_c.no_facture
|
|
GROUP BY 1,2,4,5,6
|
|
HAVING SUM(montant_comptabilise-montant_regle) <> 0 OR
|
|
SUM(montant_comptabilise_1-montant_regle_1) <> 0 OR
|
|
SUM(montant_comptabilise_2-montant_regle_2) <> 0 OR
|
|
SUM(montant_comptabilise_22-montant_regle_22) <> 0 OR
|
|
SUM(montant_comptabilise_0-montant_regle_0) <> 0
|
|
;
|
|
|
|
INSERT INTO activite.p_factures_soldes_h(
|
|
facture_id,
|
|
no_facture,
|
|
date_comptable,
|
|
medecin_comptabilise_id,
|
|
prestation_id,
|
|
rubrique_comptabilisee_id,
|
|
montant_comptabilise,
|
|
montant_comptabilise_0,
|
|
montant_comptabilise_1,
|
|
montant_comptabilise_2,
|
|
montant_comptabilise_22,
|
|
montant_regle,
|
|
montant_regle_0,
|
|
montant_regle_1,
|
|
montant_regle_2,
|
|
montant_regle_22,
|
|
od_non_comptabilise
|
|
)
|
|
SELECT
|
|
facture_id,
|
|
p_factures_soldes_h.no_facture,
|
|
MAX(GREATEST(date_comptable,date_solde)),
|
|
medecin_comptabilise_id,
|
|
prestation_id,
|
|
rubrique_comptabilisee_id,
|
|
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,
|
|
|
|
SUM(montant_comptabilise_0-montant_regle_0)+
|
|
SUM(montant_comptabilise_1-montant_regle_1)+
|
|
SUM(montant_comptabilise_2-montant_regle_2) AS montant_regle,
|
|
SUM(montant_comptabilise_0-montant_regle_0) AS montant_regle_0,
|
|
SUM(montant_comptabilise_1-montant_regle_1) AS montant_regle_1,
|
|
SUM(montant_comptabilise_2-montant_regle_2) AS montant_regle_2,
|
|
SUM(montant_comptabilise_22-montant_regle_22) AS montant_regle_22,
|
|
'1' AS od_non_comptabilise
|
|
FROM activite.p_factures_soldes_h
|
|
JOIN w_facture_solde_auto ON w_facture_solde_auto.no_facture = p_factures_soldes_h.no_facture
|
|
GROUP BY 1,2,4,5,6,7
|
|
HAVING SUM(montant_comptabilise-montant_regle) <> 0 OR
|
|
SUM(montant_comptabilise_1-montant_regle_1) <> 0 OR
|
|
SUM(montant_comptabilise_2-montant_regle_2) <> 0 OR
|
|
SUM(montant_comptabilise_22-montant_regle_22) <> 0 OR
|
|
SUM(montant_comptabilise_0-montant_regle_0) <> 0
|
|
;
|
|
|
|
|
|
|
|
UPDATE activite.p_factures SET
|
|
montant_regle_c = montant_comptabilise_c,
|
|
montant_regle_0_c = montant_comptabilise_0_c,
|
|
montant_regle_1_c = montant_comptabilise_1_c,
|
|
montant_regle_2_c = montant_comptabilise_2_c,
|
|
montant_regle_22_c = montant_comptabilise_22_c,
|
|
montant_regle_h = montant_comptabilise_h,
|
|
montant_regle_0_h = montant_comptabilise_0_h,
|
|
montant_regle_1_h = montant_comptabilise_1_h,
|
|
montant_regle_2_h = montant_comptabilise_2_h,
|
|
montant_regle_22_h = montant_comptabilise_22_h
|
|
FROM w_facture_solde_auto
|
|
WHERE w_facture_solde_auto.no_facture = p_factures.no_facture AND
|
|
(
|
|
montant_regle_c <> montant_comptabilise_c OR
|
|
montant_regle_h <> montant_comptabilise_h
|
|
)
|
|
;
|
|
END IF;
|
|
|
|
|
|
-- Solde automatique honoraires si clinique soldé
|
|
IF (SELECT MAX(valeur) FROM activite.t_divers WHERE t_divers.code = 'SOLDEAUTOHON') = '1' THEN
|
|
DROP TABLE IF EXISTS w_facture_solde_auto_hon;
|
|
CREATE TEMP TABLE w_facture_solde_auto_hon AS
|
|
SELECT
|
|
no_facture,
|
|
'1'::text AS tiers_type,
|
|
SUM(montant_comptabilise_1) - SUM(montant_regle_1) AS montant_solde_h,
|
|
0::numeric AS montant_solde_c,
|
|
NULL::date AS date_solde_c
|
|
FROM activite.p_factures_soldes_h
|
|
GROUP BY 1
|
|
HAVING SUM(montant_comptabilise_1) <> SUM(montant_regle_1)
|
|
;
|
|
INSERT INTO w_facture_solde_auto_hon
|
|
SELECT
|
|
no_facture,
|
|
'0'::text AS tiers_type,
|
|
SUM(montant_comptabilise_0) - SUM(montant_regle_0) AS montant_solde_h,
|
|
0::numeric AS montant_solde_c,
|
|
NULL::date AS date_solde_c
|
|
FROM activite.p_factures_soldes_h
|
|
GROUP BY 1
|
|
HAVING SUM(montant_comptabilise_0) <> SUM(montant_regle_0)
|
|
;
|
|
INSERT INTO w_facture_solde_auto_hon
|
|
SELECT
|
|
no_facture,
|
|
'2'::text AS tiers_type,
|
|
SUM(montant_comptabilise_2) - SUM(montant_regle_2) AS montant_solde_h,
|
|
0::numeric AS montant_solde_c,
|
|
NULL::date AS date_solde_c
|
|
FROM activite.p_factures_soldes_h
|
|
GROUP BY 1
|
|
HAVING SUM(montant_comptabilise_2) <> SUM(montant_regle_2)
|
|
;
|
|
INSERT INTO w_facture_solde_auto_hon
|
|
SELECT
|
|
no_facture,
|
|
'22'::text AS tiers_type,
|
|
SUM(montant_comptabilise_22) - SUM(montant_regle_22) AS montant_solde_h,
|
|
0::numeric AS montant_solde_c,
|
|
NULL::date AS date_solde_c
|
|
FROM activite.p_factures_soldes_h
|
|
GROUP BY 1
|
|
HAVING SUM(montant_comptabilise_22) <> SUM(montant_regle_22)
|
|
;
|
|
|
|
ANALYSE w_facture_solde_auto_hon
|
|
;
|
|
|
|
UPDATE w_facture_solde_auto_hon SET
|
|
montant_solde_c = subview.montant_solde_c,
|
|
date_solde_c = subview.date_solde_c
|
|
FROM
|
|
(
|
|
SELECT w_facture_solde_auto_hon.no_facture,
|
|
w_facture_solde_auto_hon.tiers_type,
|
|
CASE WHEN SUM(montant_comptabilise_1) - SUM(montant_regle_1) = 0 THEN MAX(date_comptable) ELSE NULL END AS date_solde_c,
|
|
SUM(montant_comptabilise_1) - SUM(montant_regle_1) AS montant_solde_c
|
|
FROM activite.p_factures_soldes_c
|
|
JOIN w_facture_solde_auto_hon ON
|
|
activite.p_factures_soldes_c.no_facture = w_facture_solde_auto_hon.no_facture AND tiers_type = '1'
|
|
GROUP BY 1,2
|
|
HAVING SUM(montant_comptabilise_1) <> 0
|
|
) subview
|
|
WHERE w_facture_solde_auto_hon.no_facture = subview.no_facture AND
|
|
w_facture_solde_auto_hon.tiers_type = subview.tiers_type
|
|
;
|
|
|
|
UPDATE w_facture_solde_auto_hon SET
|
|
montant_solde_c = subview.montant_solde_c,
|
|
date_solde_c = subview.date_solde_c
|
|
FROM
|
|
(
|
|
SELECT w_facture_solde_auto_hon.no_facture,
|
|
w_facture_solde_auto_hon.tiers_type,
|
|
CASE WHEN SUM(montant_comptabilise_0) - SUM(montant_regle_0) = 0 THEN MAX(date_comptable) ELSE NULL END AS date_solde_c,
|
|
SUM(montant_comptabilise_0) - SUM(montant_regle_0) AS montant_solde_c
|
|
FROM activite.p_factures_soldes_c
|
|
JOIN w_facture_solde_auto_hon ON
|
|
activite.p_factures_soldes_c.no_facture = w_facture_solde_auto_hon.no_facture AND tiers_type = '0'
|
|
GROUP BY 1,2
|
|
HAVING SUM(montant_comptabilise_0) <> 0
|
|
) subview
|
|
WHERE w_facture_solde_auto_hon.no_facture = subview.no_facture AND
|
|
w_facture_solde_auto_hon.tiers_type = subview.tiers_type
|
|
;
|
|
|
|
UPDATE w_facture_solde_auto_hon SET
|
|
montant_solde_c = subview.montant_solde_c,
|
|
date_solde_c = subview.date_solde_c
|
|
FROM
|
|
(
|
|
SELECT w_facture_solde_auto_hon.no_facture,
|
|
w_facture_solde_auto_hon.tiers_type,
|
|
CASE WHEN SUM(montant_comptabilise_2) - SUM(montant_regle_2) = 0 THEN MAX(date_comptable) ELSE NULL END AS date_solde_c,
|
|
SUM(montant_comptabilise_2) - SUM(montant_regle_2) AS montant_solde_c
|
|
FROM activite.p_factures_soldes_c
|
|
JOIN w_facture_solde_auto_hon ON
|
|
activite.p_factures_soldes_c.no_facture = w_facture_solde_auto_hon.no_facture AND tiers_type = '2'
|
|
GROUP BY 1,2
|
|
HAVING SUM(montant_comptabilise_2) <> 0
|
|
) subview
|
|
WHERE w_facture_solde_auto_hon.no_facture = subview.no_facture AND
|
|
w_facture_solde_auto_hon.tiers_type = subview.tiers_type
|
|
;
|
|
|
|
UPDATE w_facture_solde_auto_hon SET
|
|
montant_solde_c = subview.montant_solde_c,
|
|
date_solde_c = subview.date_solde_c
|
|
FROM
|
|
(
|
|
SELECT w_facture_solde_auto_hon.no_facture,
|
|
w_facture_solde_auto_hon.tiers_type,
|
|
CASE WHEN SUM(montant_comptabilise_22) - SUM(montant_regle_22) = 0 THEN MAX(date_comptable) ELSE NULL END AS date_solde_c,
|
|
SUM(montant_comptabilise_22) - SUM(montant_regle_22) AS montant_solde_c
|
|
FROM activite.p_factures_soldes_c
|
|
JOIN w_facture_solde_auto_hon ON
|
|
activite.p_factures_soldes_c.no_facture = w_facture_solde_auto_hon.no_facture AND tiers_type = '22'
|
|
GROUP BY 1,2
|
|
HAVING SUM(montant_comptabilise_22) <> 0
|
|
) subview
|
|
WHERE w_facture_solde_auto_hon.no_facture = subview.no_facture AND
|
|
w_facture_solde_auto_hon.tiers_type = subview.tiers_type
|
|
;
|
|
|
|
|
|
INSERT INTO activite.p_factures_soldes_h(
|
|
facture_id,
|
|
no_facture,
|
|
date_comptable,
|
|
medecin_comptabilise_id,
|
|
prestation_id,
|
|
rubrique_comptabilisee_id,
|
|
montant_comptabilise,
|
|
montant_comptabilise_0,
|
|
montant_comptabilise_1,
|
|
montant_comptabilise_2,
|
|
montant_comptabilise_22,
|
|
montant_regle,
|
|
montant_regle_0,
|
|
montant_regle_1,
|
|
montant_regle_2,
|
|
montant_regle_22,
|
|
od_non_comptabilise
|
|
)
|
|
SELECT
|
|
facture_id,
|
|
p_factures_soldes_h.no_facture,
|
|
MAX(GREATEST(date_comptable,date_solde_c)),
|
|
medecin_comptabilise_id,
|
|
prestation_id,
|
|
rubrique_comptabilisee_id,
|
|
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,
|
|
|
|
SUM(CASE WHEN tiers_type = '0' THEN montant_comptabilise_0-montant_regle_0 ELSE 0 END)+
|
|
SUM(CASE WHEN tiers_type = '1' THEN montant_comptabilise_1-montant_regle_1 ELSE 0 END)+
|
|
SUM(CASE WHEN tiers_type = '2' THEN montant_comptabilise_2-montant_regle_2 ELSE 0 END)+
|
|
SUM(CASE WHEN tiers_type = '22' THEN montant_comptabilise_22-montant_regle_22 ELSE 0 END) AS montant_regle,
|
|
SUM(CASE WHEN tiers_type = '0' THEN montant_comptabilise_0-montant_regle_0 ELSE 0 END) AS montant_regle_0,
|
|
SUM(CASE WHEN tiers_type = '1' THEN montant_comptabilise_1-montant_regle_1 ELSE 0 END) AS montant_regle_1,
|
|
SUM(CASE WHEN tiers_type = '2' THEN montant_comptabilise_2-montant_regle_2 ELSE 0 END) AS montant_regle_2,
|
|
SUM(CASE WHEN tiers_type = '22' THEN montant_comptabilise_22-montant_regle_22 ELSE 0 END) AS montant_regle_22,
|
|
'1' AS od_non_comptabilise
|
|
FROM activite.p_factures_soldes_h
|
|
JOIN w_facture_solde_auto_hon ON w_facture_solde_auto_hon.no_facture = p_factures_soldes_h.no_facture
|
|
WHERE date_solde_c IS NOT NULL
|
|
GROUP BY 1,2,4,5,6,7
|
|
HAVING SUM(CASE WHEN tiers_type = '0' THEN montant_comptabilise_0-montant_regle_0 ELSE 0 END) <> 0 OR
|
|
SUM(CASE WHEN tiers_type = '1' THEN montant_comptabilise_1-montant_regle_1 ELSE 0 END) <> 0 OR
|
|
SUM(CASE WHEN tiers_type = '2' THEN montant_comptabilise_2-montant_regle_2 ELSE 0 END) <> 0 OR
|
|
SUM(CASE WHEN tiers_type = '22' THEN montant_comptabilise_22-montant_regle_22 ELSE 0 END) <> 0
|
|
;
|
|
|
|
|
|
|
|
UPDATE activite.p_factures SET
|
|
montant_regle_h = montant_regle_0_h + montant_comptabilise_1_h + montant_regle_2_h + montant_regle_22_h,
|
|
montant_regle_1_h = montant_comptabilise_1_h
|
|
FROM w_facture_solde_auto_hon
|
|
WHERE w_facture_solde_auto_hon.no_facture = p_factures.no_facture AND
|
|
w_facture_solde_auto_hon.tiers_type = '1' AND
|
|
w_facture_solde_auto_hon.date_solde_c IS NOT NULL
|
|
;
|
|
UPDATE activite.p_factures SET
|
|
montant_regle_h = montant_comptabilise_0_h + montant_regle_1_h + montant_regle_2_h + montant_regle_22_h,
|
|
montant_regle_0_h = montant_comptabilise_0_h
|
|
FROM w_facture_solde_auto_hon
|
|
WHERE w_facture_solde_auto_hon.no_facture = p_factures.no_facture AND
|
|
w_facture_solde_auto_hon.tiers_type = '0' AND
|
|
w_facture_solde_auto_hon.date_solde_c IS NOT NULL
|
|
;
|
|
|
|
UPDATE activite.p_factures SET
|
|
montant_regle_h = montant_regle_0_h + montant_regle_1_h + montant_comptabilise_2_h + montant_regle_22_h,
|
|
montant_regle_2_h = montant_comptabilise_2_h
|
|
FROM w_facture_solde_auto_hon
|
|
WHERE w_facture_solde_auto_hon.no_facture = p_factures.no_facture AND
|
|
w_facture_solde_auto_hon.tiers_type = '2' AND
|
|
w_facture_solde_auto_hon.date_solde_c IS NOT NULL
|
|
;
|
|
|
|
UPDATE activite.p_factures SET
|
|
montant_regle_h = montant_regle_0_h + montant_regle_1_h + montant_regle_2_h + montant_comptabilise_22_h,
|
|
montant_regle_22_h = montant_comptabilise_22_h
|
|
FROM w_facture_solde_auto_hon
|
|
WHERE w_facture_solde_auto_hon.no_facture = p_factures.no_facture AND
|
|
w_facture_solde_auto_hon.tiers_type = '22' AND
|
|
w_facture_solde_auto_hon.date_solde_c IS NOT NULL
|
|
;
|
|
|
|
|
|
|
|
|
|
END IF;
|
|
|
|
-- Ajustement des dates de solde après ces manipulations
|
|
DROP TABLE IF EXISTS w_correction_date_c;
|
|
CREATE TEMP TABLE w_correction_date_c AS
|
|
SELECT p_factures.no_facture, no_sejour,
|
|
MAX(p_factures.date_solde_0_c) AS date_solde_0_c_old,
|
|
MAX(p_factures.date_solde_1_c) AS date_solde_1_c_old,
|
|
MAX(p_factures.date_solde_2_c) AS date_solde_2_c_old,
|
|
MAX(p_factures.date_solde_22_c) AS date_solde_22_c_old,
|
|
MAX(CASE WHEN montant_comptabilise_0_c = 0 THEN '00010101' WHEN montant_comptabilise_0_c = montant_regle_0_c AND p_factures.date_solde_0_c = '20991231' AND montant_comptabilise_0_c <> 0 AND p_factures_soldes_c.montant_regle_0 <> 0 THEN date_comptable ELSE NULL END) AS date_solde_0_c_new,
|
|
MAX(CASE WHEN montant_comptabilise_1_c = 0 THEN '00010101' WHEN montant_comptabilise_1_c = montant_regle_1_c AND p_factures.date_solde_1_c = '20991231' AND montant_comptabilise_1_c <> 0 AND p_factures.date_solde_1_c = '20991231' AND p_factures_soldes_c.montant_regle_1 <> 0 THEN date_comptable ELSE NULL END) AS date_solde_1_c_new,
|
|
MAX(CASE WHEN montant_comptabilise_2_c = 0 THEN '00010101' WHEN montant_comptabilise_2_c = montant_regle_2_c AND p_factures.date_solde_2_c = '20991231' AND montant_comptabilise_2_c <> 0 AND p_factures.date_solde_2_c = '20991231' AND p_factures_soldes_c.montant_regle_2 <> 0 THEN date_comptable ELSE NULL END) AS date_solde_2_c_new,
|
|
MAX(CASE WHEN montant_comptabilise_22_c = 0 THEN '00010101' WHEN montant_comptabilise_22_c = montant_regle_22_c AND p_factures.date_solde_22_c = '20991231' AND montant_comptabilise_22_c <> 0 AND p_factures.date_solde_22_c = '20991231' AND p_factures_soldes_c.montant_regle_22 <> 0 THEN date_comptable ELSE NULL END) AS date_solde_22_c_new
|
|
FROM activite.p_factures
|
|
LEFT JOIN activite.p_factures_soldes_c ON p_factures.no_facture = p_factures_soldes_c.no_facture AND p_factures_soldes_c.montant_regle <> 0
|
|
GROUP BY 1,2
|
|
;
|
|
|
|
UPDATE activite.p_factures SET
|
|
date_solde_c = GREATEST(
|
|
COALESCE(date_solde_0_c_new,date_solde_0_c),
|
|
COALESCE(date_solde_1_c_new,date_solde_1_c),
|
|
COALESCE(date_solde_2_c_new,date_solde_2_c),
|
|
COALESCE(date_solde_22_c_new,date_solde_22_c)
|
|
),
|
|
date_solde_0_c = COALESCE(date_solde_0_c_new,date_solde_0_c),
|
|
date_solde_1_c = COALESCE(date_solde_1_c_new,date_solde_1_c),
|
|
date_solde_2_c = COALESCE(date_solde_2_c_new,date_solde_2_c),
|
|
date_solde_22_c = COALESCE(date_solde_22_c_new,date_solde_22_c)
|
|
FROM w_correction_date_c
|
|
WHERE p_factures.no_facture = w_correction_date_c.no_facture AND
|
|
(
|
|
date_solde_c <> GREATEST(
|
|
COALESCE(date_solde_0_c_new,date_solde_0_c),
|
|
COALESCE(date_solde_1_c_new,date_solde_1_c),
|
|
COALESCE(date_solde_2_c_new,date_solde_2_c),
|
|
COALESCE(date_solde_22_c_new,date_solde_22_c)
|
|
) OR
|
|
date_solde_0_c <> COALESCE(date_solde_0_c_new,date_solde_0_c) OR
|
|
date_solde_1_c <> COALESCE(date_solde_1_c_new,date_solde_1_c) OR
|
|
date_solde_2_c <> COALESCE(date_solde_2_c_new,date_solde_2_c) OR
|
|
date_solde_22_c <> COALESCE(date_solde_22_c_new,date_solde_22_c)
|
|
)
|
|
;
|
|
|
|
|
|
DROP TABLE IF EXISTS w_correction_date_h;
|
|
CREATE TEMP TABLE w_correction_date_h AS
|
|
SELECT p_factures.no_facture, no_sejour,
|
|
MAX(p_factures.date_solde_0_h) AS date_solde_0_h_old,
|
|
MAX(p_factures.date_solde_1_h) AS date_solde_1_h_old,
|
|
MAX(p_factures.date_solde_2_h) AS date_solde_2_h_old,
|
|
MAX(p_factures.date_solde_22_h) AS date_solde_22_h_old,
|
|
MAX(CASE WHEN montant_comptabilise_0_h = 0 THEN '00010101' WHEN montant_comptabilise_0_h = montant_regle_0_h AND p_factures.date_solde_0_h = '20991231' AND montant_comptabilise_0_h <> 0 AND p_factures_soldes_h.montant_regle_0 <> 0 THEN date_comptable ELSE NULL END) AS date_solde_0_h_new,
|
|
MAX(CASE WHEN montant_comptabilise_1_h = 0 THEN '00010101' WHEN montant_comptabilise_1_h = montant_regle_1_h AND p_factures.date_solde_1_h = '20991231' AND montant_comptabilise_1_h <> 0 AND p_factures.date_solde_1_h = '20991231' AND p_factures_soldes_h.montant_regle_1 <> 0 THEN date_comptable ELSE NULL END) AS date_solde_1_h_new,
|
|
MAX(CASE WHEN montant_comptabilise_2_h = 0 THEN '00010101' WHEN montant_comptabilise_2_h = montant_regle_2_h AND p_factures.date_solde_2_h = '20991231' AND montant_comptabilise_2_h <> 0 AND p_factures.date_solde_2_h = '20991231' AND p_factures_soldes_h.montant_regle_2 <> 0 THEN date_comptable ELSE NULL END) AS date_solde_2_h_new,
|
|
MAX(CASE WHEN montant_comptabilise_22_h = 0 THEN '00010101' WHEN montant_comptabilise_22_h = montant_regle_22_h AND p_factures.date_solde_22_h = '20991231' AND montant_comptabilise_22_h <> 0 AND p_factures.date_solde_22_h = '20991231' AND p_factures_soldes_h.montant_regle_22 <> 0 THEN date_comptable ELSE NULL END) AS date_solde_22_h_new
|
|
FROM activite.p_factures
|
|
LEFT JOIN activite.p_factures_soldes_h ON p_factures.no_facture = p_factures_soldes_h.no_facture AND p_factures_soldes_h.montant_regle <> 0
|
|
GROUP BY 1,2
|
|
;
|
|
|
|
UPDATE activite.p_factures SET
|
|
date_solde_h = GREATEST(
|
|
COALESCE(date_solde_0_h_new,date_solde_0_h),
|
|
COALESCE(date_solde_1_h_new,date_solde_1_h),
|
|
COALESCE(date_solde_2_h_new,date_solde_2_h),
|
|
COALESCE(date_solde_22_h_new,date_solde_22_h)
|
|
),
|
|
date_solde_0_h = COALESCE(date_solde_0_h_new,date_solde_0_h),
|
|
date_solde_1_h = COALESCE(date_solde_1_h_new,date_solde_1_h),
|
|
date_solde_2_h = COALESCE(date_solde_2_h_new,date_solde_2_h),
|
|
date_solde_22_h = COALESCE(date_solde_22_h_new,date_solde_22_h)
|
|
FROM w_correction_date_h
|
|
WHERE p_factures.no_facture = w_correction_date_h.no_facture AND
|
|
(
|
|
date_solde_h <> GREATEST(
|
|
COALESCE(date_solde_0_h_new,date_solde_0_h),
|
|
COALESCE(date_solde_1_h_new,date_solde_1_h),
|
|
COALESCE(date_solde_2_h_new,date_solde_2_h),
|
|
COALESCE(date_solde_22_h_new,date_solde_22_h)
|
|
) OR
|
|
date_solde_0_h <> COALESCE(date_solde_0_h_new,date_solde_0_h) OR
|
|
date_solde_1_h <> COALESCE(date_solde_1_h_new,date_solde_1_h) OR
|
|
date_solde_2_h <> COALESCE(date_solde_2_h_new,date_solde_2_h) OR
|
|
date_solde_22_h <> COALESCE(date_solde_22_h_new,date_solde_22_h)
|
|
)
|
|
;
|
|
|
|
|
|
|
|
RETURN 'OK';
|
|
END;
|