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.
356 lines
14 KiB
356 lines
14 KiB
return: text
|
|
lang: plpgsql
|
|
src: |
|
|
DECLARE
|
|
result TEXT;
|
|
BEGIN
|
|
-- Alimentation des nouveaux champs si pas remplis
|
|
IF EXISTS (
|
|
SELECT no_facture
|
|
FROM activite.p_factures_encours
|
|
WHERE p_factures_encours.date_entree IS NULL OR
|
|
p_factures_encours.heure_entree IS NULL OR
|
|
p_factures_encours.date_sortie IS NULL OR
|
|
p_factures_encours.heure_sortie IS NULL OR
|
|
p_factures_encours.type_sejour IS NULL OR
|
|
p_factures_encours.date_groupage IS NULL OR
|
|
p_factures_encours.provider_id IS NULL OR
|
|
p_factures_encours.ghm_id IS NULL
|
|
) THEN
|
|
RAISE NOTICE '%' , 'Initialisation nouveaux champs';
|
|
|
|
UPDATE activite.p_factures_encours SET
|
|
date_entree = COALESCE(p_factures_encours.date_entree,p_sejours.date_entree),
|
|
heure_entree = COALESCE(p_factures_encours.heure_entree,p_sejours.heure_entree),
|
|
date_sortie = COALESCE(p_factures_encours.date_sortie,p_sejours.date_sortie),
|
|
heure_sortie = COALESCE(p_factures_encours.heure_sortie,p_sejours.heure_sortie),
|
|
type_sejour = COALESCE(p_factures_encours.type_sejour,p_sejours.type_sejour),
|
|
date_groupage = COALESCE(p_factures_encours.date_groupage,p_sejours.date_groupage),
|
|
provider_id = COALESCE(p_factures_encours.provider_id,p_sejours.provider_id),
|
|
ghm_id = COALESCE(p_factures_encours.ghm_id,p_sejours.ghm_id)
|
|
FROM activite.p_sejours
|
|
WHERE p_factures_encours.code_origine = 'C' AND
|
|
p_factures_encours.no_sejour = p_sejours.no_sejour AND
|
|
(
|
|
p_factures_encours.date_entree IS NULL OR
|
|
p_factures_encours.heure_entree IS NULL OR
|
|
p_factures_encours.date_sortie IS NULL OR
|
|
p_factures_encours.heure_sortie IS NULL OR
|
|
p_factures_encours.type_sejour IS NULL OR
|
|
p_factures_encours.date_groupage IS NULL OR
|
|
p_factures_encours.provider_id IS NULL OR
|
|
p_factures_encours.ghm_id IS NULL
|
|
)
|
|
;
|
|
-- Alimentation des nouveaux champs si pas remplis et séjour non trouvé
|
|
UPDATE activite.p_factures_encours SET
|
|
date_entree = COALESCE(p_factures_encours.date_entree,p_factures_encours.date_debut),
|
|
heure_entree = COALESCE(p_factures_encours.heure_entree,9),
|
|
date_sortie = COALESCE(p_factures_encours.date_sortie,p_factures_encours.date_fin),
|
|
heure_sortie = COALESCE(p_factures_encours.heure_sortie,1300),
|
|
type_sejour = COALESCE(p_factures_encours.type_sejour),
|
|
date_groupage = COALESCE(p_factures_encours.date_groupage,'20991231'),
|
|
provider_id = COALESCE(p_factures_encours.provider_id),
|
|
ghm_id = COALESCE(p_factures_encours.ghm_id,0)
|
|
WHERE p_factures_encours.code_origine = 'C' AND
|
|
(
|
|
p_factures_encours.date_entree IS NULL OR
|
|
p_factures_encours.heure_entree IS NULL OR
|
|
p_factures_encours.date_sortie IS NULL OR
|
|
p_factures_encours.heure_sortie IS NULL OR
|
|
p_factures_encours.type_sejour IS NULL OR
|
|
p_factures_encours.date_groupage IS NULL OR
|
|
p_factures_encours.provider_id IS NULL OR
|
|
p_factures_encours.ghm_id IS NULL
|
|
)
|
|
;
|
|
-- Alimentation du nouveau champ type de séjour si séjour non trouvé selon prestations
|
|
UPDATE activite.p_factures_encours
|
|
SET type_sejour = '1'
|
|
WHERE type_sejour IS NULL AND
|
|
oid IN (
|
|
SELECT p_factures_encours_lignes_c.facture_id
|
|
FROM activite.p_factures_encours_lignes_c
|
|
JOIN activite.p_factures_encours ON p_factures_encours.oid = p_factures_encours_lignes_c.facture_id
|
|
JOIN activite.t_prestations ON prestation_id = t_prestations.oid
|
|
WHERE p_factures_encours.code_origine = 'C' AND
|
|
p_factures_encours.type_sejour IS NULL AND
|
|
t_prestations.code IN ('GHS', 'PJ') AND
|
|
(p_factures_encours.date_debut <> p_factures_encours.date_fin OR
|
|
code_sorti <> '1'
|
|
)
|
|
)
|
|
;
|
|
UPDATE activite.p_factures_encours
|
|
SET type_sejour = '2'
|
|
WHERE type_sejour IS NULL AND
|
|
oid IN (
|
|
SELECT p_factures_encours_lignes_c.facture_id
|
|
FROM activite.p_factures_encours_lignes_c
|
|
JOIN activite.p_factures_encours ON p_factures_encours.oid = p_factures_encours_lignes_c.facture_id
|
|
JOIN activite.t_prestations ON prestation_id = t_prestations.oid
|
|
WHERE p_factures_encours.code_origine = 'C' AND
|
|
p_factures_encours.type_sejour IS NULL AND
|
|
t_prestations.code IN ('GHS', 'IGM', 'IGM', 'IGM', 'IMD', 'IMI') AND
|
|
p_factures_encours.date_debut = p_factures_encours.date_fin AND
|
|
p_factures_encours.code_sorti = '1'
|
|
)
|
|
;
|
|
UPDATE activite.p_factures_encours
|
|
SET type_sejour = '5'
|
|
WHERE type_sejour IS NULL AND
|
|
oid IN (
|
|
SELECT p_factures_encours_lignes_c.facture_id
|
|
FROM activite.p_factures_encours_lignes_c
|
|
JOIN activite.p_factures_encours ON p_factures_encours.oid = p_factures_encours_lignes_c.facture_id
|
|
JOIN activite.t_prestations ON prestation_id = t_prestations.oid
|
|
WHERE p_factures_encours.code_origine = 'C' AND
|
|
p_factures_encours.type_sejour IS NULL AND
|
|
t_prestations.code IN ('D09','D13')
|
|
)
|
|
;
|
|
UPDATE activite.p_factures_encours
|
|
SET type_sejour = '3'
|
|
WHERE type_sejour IS NULL AND
|
|
oid IN (
|
|
SELECT p_factures_encours_lignes_c.facture_id
|
|
FROM activite.p_factures_encours_lignes_c
|
|
JOIN activite.p_factures_encours ON p_factures_encours.oid = p_factures_encours_lignes_c.facture_id
|
|
JOIN activite.t_prestations ON prestation_id = t_prestations.oid
|
|
WHERE p_factures_encours.code_origine = 'C' AND
|
|
p_factures_encours.type_sejour IS NULL AND
|
|
t_prestations.code IN ('SE1', 'SE2', 'SE3', 'SE4', 'ATU', 'FPU', 'FFM')
|
|
)
|
|
;
|
|
UPDATE activite.p_factures_encours
|
|
SET type_sejour = '1'
|
|
WHERE type_sejour IS NULL AND
|
|
oid IN (
|
|
SELECT p_factures_encours_lignes_c.facture_id
|
|
FROM activite.p_factures_encours_lignes_c
|
|
JOIN activite.p_factures_encours ON p_factures_encours.oid = p_factures_encours_lignes_c.facture_id
|
|
JOIN activite.t_prestations ON prestation_id = t_prestations.oid
|
|
WHERE p_factures_encours.code_origine = 'C' AND
|
|
p_factures_encours.type_sejour IS NULL AND
|
|
t_prestations.code IN ('GHS', 'PJ') AND
|
|
p_factures_encours.date_debut <> p_factures_encours.date_fin AND
|
|
code_sorti = '1'
|
|
);
|
|
UPDATE activite.p_factures_encours
|
|
SET type_sejour = '9'
|
|
WHERE p_factures_encours.code_origine = 'C' AND
|
|
type_sejour IS NULL;
|
|
|
|
-- Cas où les GHM sont à null
|
|
UPDATE activite.p_factures_encours
|
|
SET ghm_id = 0
|
|
WHERE ghm_id IS NULL;
|
|
END IF;
|
|
RAISE NOTICE '%' , 'Maintenance lieu des lignes encours';
|
|
|
|
-- Actualisation du lieu selon nouveaux mouvements
|
|
UPDATE activite.p_factures_encours_lignes_c
|
|
SET lieu_id = 0
|
|
WHERE lieu_id NOT IN
|
|
(SELECT oid FROM activite.t_lieux)
|
|
;
|
|
UPDATE activite.p_factures_encours_lignes_c
|
|
SET lieu_id = p_mouvements_sejour.lieu_id
|
|
FROM activite.p_factures_encours
|
|
JOIN activite.p_mouvements_sejour ON p_factures_encours.no_sejour = p_mouvements_sejour.no_sejour
|
|
JOIN activite.p_sejours ON p_sejours.no_sejour = p_factures_encours.no_sejour
|
|
WHERE p_factures_encours_lignes_c.facture_id = p_factures_encours.oid AND
|
|
activite.p_factures_encours_lignes_c.date_debut = p_mouvements_sejour.date AND
|
|
(
|
|
p_mouvements_sejour.heure_fin = 240000 OR
|
|
p_mouvements_sejour.nb_sorties_directes = 1 OR
|
|
p_mouvements_sejour.nb_seances = 1 AND p_sejours.type_sejour = '5' OR
|
|
p_mouvements_sejour.nb_externes = 1 OR
|
|
p_mouvements_sejour.nb_ambulatoires = 1
|
|
) AND
|
|
p_factures_encours_lignes_c.lieu_id <> p_mouvements_sejour.lieu_id;
|
|
|
|
-- Actualisation du lieu de sortie selon nouveaux mouvements
|
|
RAISE NOTICE '%' , 'Maintenance lieu de sortie facture encours';
|
|
|
|
UPDATE activite.p_factures_encours
|
|
SET lieu_sortie_id = subview.lieu_sortie_id
|
|
FROM (
|
|
SELECT p_factures_encours_lignes_c.facture_id,
|
|
(MAX(array[p_factures_encours_lignes_c.date_fin::text, lieu_id::text]))[2]::bigint AS lieu_sortie_id
|
|
FROM activite.p_factures_encours_lignes_c
|
|
JOIN activite.p_factures_encours ON p_factures_encours.oid = p_factures_encours_lignes_c.facture_id
|
|
WHERE p_factures_encours.lieu_sortie_id IS NULL
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE p_factures_encours.oid = subview.facture_id AND
|
|
p_factures_encours.lieu_sortie_id <> subview.lieu_sortie_id;
|
|
-- Création de séjours fictifs pour séjours supprimés depuis création en cours
|
|
RAISE NOTICE '%' , 'Creation sejours fictifs si supprimes depuis creation en cours';
|
|
PERFORM setval('activite.s_sejours'::regclass,
|
|
(SELECT COALESCE(MAX(substr(to_char(oid, 'FM00000000000000000000'),11,10))::bigint,0::bigint)+1 FROm activite.p_sejours));
|
|
|
|
DROP TABLE IF EXISTS w_fac_enc_sej;
|
|
CREATE TEMP TABLE w_fac_enc_sej AS
|
|
SELECT p_factures_encours.no_sejour,
|
|
(MAX(ARRAY[date_encours::text,p_factures_encours.oid::text]))[2] AS oid
|
|
FROM activite.p_factures_encours
|
|
LEFT JOIN activite.p_sejours ON p_factures_encours.no_sejour = p_sejours.no_sejour
|
|
WHERE p_sejours.no_sejour IS NULL
|
|
GROUP BY 1;
|
|
|
|
ANALYSE w_fac_enc_sej
|
|
;
|
|
|
|
INSERT INTO activite.p_sejours (
|
|
oid,
|
|
finess,
|
|
no_sejour,
|
|
code_original,
|
|
no_patient,
|
|
date_entree,
|
|
heure_entree,
|
|
date_sortie,
|
|
heure_sortie,
|
|
code_sexe,
|
|
age,
|
|
code_sorti,
|
|
code_prevu,
|
|
type_sejour,
|
|
lieu_sortie_id,
|
|
medecin_sejour_id,
|
|
ghs_id,
|
|
ghm_id,
|
|
ghs_bebe1_id,
|
|
ghs_bebe2_id,
|
|
ghs_bebe3_id,
|
|
date_groupage,
|
|
code_cp_demandee,
|
|
mode_traitement_id,
|
|
mode_entree,
|
|
provenance,
|
|
mode_sortie,
|
|
destination,
|
|
tiers_payant_0_id,
|
|
tiers_payant_1_id,
|
|
tiers_payant_2_id,
|
|
tiers_payant_22_id,
|
|
est_budget_global,
|
|
code_postal_id
|
|
)
|
|
SELECT
|
|
(substr(to_char(p_factures_encours.oid, 'FM00000000000000000000'),1,10) || to_char(nextval('activite.s_sejours'::regclass),'FM0000000000'))::bigint AS oid,
|
|
p_factures_encours.finess,
|
|
p_factures_encours.no_sejour,
|
|
'*DEL*'||p_factures_encours.no_sejour AS code_original,
|
|
p_factures_encours.no_sejour no_patient,
|
|
p_factures_encours.date_entree,
|
|
p_factures_encours.heure_entree,
|
|
p_factures_encours.date_sortie,
|
|
p_factures_encours.heure_sortie,
|
|
'1'::text AS code_sexe,
|
|
0::numeric AS age,
|
|
base.cti_to_number(p_factures_encours.code_sorti),
|
|
0::numeric AS code_prevu,
|
|
'9'::text AS type_sejour,
|
|
0::bigint AS lieu_sortie_id,
|
|
0::bigint AS medecin_sejour_id,
|
|
p_factures_encours.ghs_id,
|
|
p_factures_encours.ghm_id,
|
|
0::bigint AS ghs_bebe1_id,
|
|
0::bigint AS ghs_bebe2_id,
|
|
0::bigint AS ghs_bebe3_id,
|
|
p_factures_encours.date_groupage,
|
|
p_factures_encours.code_cp_demandee,
|
|
0::bigint AS mode_traitement_id,
|
|
'8'::text AS mode_entree,
|
|
''::text AS provenance,
|
|
'8'::text AS mode_sortie,
|
|
''::text AS destination,
|
|
p_factures_encours.tiers_payant_0_id,
|
|
p_factures_encours.tiers_payant_1_id,
|
|
p_factures_encours.tiers_payant_2_id,
|
|
p_factures_encours.tiers_payant_22_id,
|
|
0::numeric AS est_budget_global,
|
|
0::bigint AS code_postal_id
|
|
FROM activite.p_factures_encours
|
|
JOIN w_fac_enc_sej ON p_factures_encours.oid = w_fac_enc_sej.oid
|
|
;
|
|
|
|
UPDATE activite.p_sejours
|
|
SET type_sejour = '9', etat = ''
|
|
FROM activite.p_factures_encours
|
|
WHERE p_factures_encours.no_sejour = p_sejours.no_sejour AND
|
|
p_sejours.etat = 'I'
|
|
;
|
|
|
|
-- Avec patients correspondant
|
|
INSERT INTO activite.p_patients(finess, no_patient, nom, prenom, date_naissance, code_sexe)
|
|
SELECT '', p_sejours.no_patient, 'Séjour supprimé : ' || p_sejours.no_sejour, '', NULL, '1'
|
|
FROM activite.p_sejours
|
|
LEFT JOIN activite.p_patients ON (p_sejours.no_patient = p_patients.no_patient)
|
|
WHERE p_sejours.code_original LIKE '*DEL%' AND
|
|
p_patients.no_patient IS NULL;
|
|
|
|
-- Mise à jour des factures encours avec ces sejours
|
|
UPDATE activite.p_factures_encours
|
|
SET sejour_id = p_sejours.oid
|
|
FROM activite.p_sejours
|
|
WHERE p_factures_encours.no_sejour = p_sejours.no_sejour AND
|
|
sejour_id IS DISTINCT FROM p_sejours.oid;
|
|
|
|
|
|
|
|
-- Correction des totaux encours
|
|
RAISE NOTICE '%' , 'Correction totaux encours';
|
|
|
|
UPDATE activite.p_factures_encours SET
|
|
montant_encours_c = subview.montant_encours,
|
|
montant_encours_0_c = subview.montant_encours_0,
|
|
montant_encours_1_c = subview.montant_encours_1,
|
|
montant_encours_2_c = subview.montant_encours_2,
|
|
montant_encours_22_c = subview.montant_encours_22
|
|
FROM (
|
|
SELECT facture_id,
|
|
SUM(montant_encours) AS montant_encours,
|
|
SUM(montant_encours_0) AS montant_encours_0,
|
|
SUM(montant_encours_1) AS montant_encours_1,
|
|
SUM(montant_encours_2) AS montant_encours_2,
|
|
SUM(montant_encours_22) AS montant_encours_22
|
|
FROM activite.p_factures_encours_lignes_c
|
|
JOIN activite.p_factures_encours ON facture_id = oid
|
|
GROUP BY 1
|
|
HAVING SUM(montant_encours) <> MAX(montant_encours_c)
|
|
) subview
|
|
WHERE p_factures_encours.oid = subview.facture_id
|
|
;
|
|
-- Correction facture reference
|
|
RAISE NOTICE '%' , 'Correction facture reference';
|
|
UPDATE activite.p_factures_encours
|
|
SET no_facture_reference = p_factures.no_facture
|
|
FROM activite.p_factures
|
|
WHERE p_factures_encours.no_sejour = p_factures.no_sejour AND
|
|
p_factures_encours.date_debut BETWEEN p_factures.date_debut AND p_factures.date_fin AND
|
|
p_factures.no_facture = p_factures.no_facture_reference AND
|
|
p_factures_encours.no_facture_reference <> p_factures.no_facture;
|
|
|
|
|
|
UPDATE activite.p_factures_reference
|
|
SET
|
|
montant_encours = p_factures.montant_encours_c + p_factures.montant_encours_h,
|
|
montant_encours_c = p_factures.montant_encours_c,
|
|
montant_encours_h = p_factures.montant_encours_h,
|
|
montant_encours_0 = p_factures.montant_encours_0_c + p_factures.montant_encours_0_h,
|
|
montant_encours_0_c = p_factures.montant_encours_0_c,
|
|
montant_encours_0_h = p_factures.montant_encours_0_h,
|
|
montant_encours_1 = p_factures.montant_encours_1_c + p_factures.montant_encours_1_h,
|
|
montant_encours_1_c = p_factures.montant_encours_1_c,
|
|
montant_encours_1_h = p_factures.montant_encours_1_h,
|
|
montant_encours_2 = p_factures.montant_encours_2_c + p_factures.montant_encours_2_h,
|
|
montant_encours_2_c = p_factures.montant_encours_2_c,
|
|
montant_encours_2_h = p_factures.montant_encours_2_h
|
|
FROM activite.p_factures
|
|
WHERE p_factures_reference.no_facture_reference = p_factures.no_facture;
|
|
|
|
RETURN 'OK';
|
|
END;
|