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.
116 lines
7.3 KiB
116 lines
7.3 KiB
return: text
|
|
lang: plpgsql
|
|
src: |
|
|
DECLARE
|
|
|
|
BEGIN
|
|
-- Ne faire que si un service est concerné
|
|
IF NOT EXISTS (SELECT * FROM activite.t_services_facturation WHERE COALESCE(prestations_presence,'') <> '') THEN
|
|
RETURN 'OK. Non concerné' ;
|
|
END IF;
|
|
RAISE NOTICE '%' , 'Initialisations. identification journees';
|
|
|
|
-- Identification des journées selon lignes facturées
|
|
DROP TABLE IF EXISTS w_mouvements_seances;
|
|
CREATE TEMP TABLE w_mouvements_seances AS
|
|
SELECT no_sejour, p_factures_lignes_c.date_debut
|
|
FROM activite.p_factures_lignes_c
|
|
JOIN activite.p_factures ON p_factures_lignes_c.no_facture = p_factures.no_facture
|
|
JOIN activite.t_lieux ON lieu_id = t_lieux.oid
|
|
JOIN activite.t_services_facturation ON service_facturation_id = t_services_facturation.oid
|
|
JOIN activite.t_prestations ON prestation_id = t_prestations.oid
|
|
WHERE
|
|
t_prestations.code <> '' AND
|
|
t_services_facturation.prestations_presence <> '' AND
|
|
t_prestations.code ILIKE ANY (string_to_array(translate(prestations_presence,'*,','% '),' '))
|
|
GROUP BY 1,2;
|
|
CREATE INDEX w_mouvements_seances_i1 ON w_mouvements_seances USING btree (no_sejour);
|
|
-- Identification des journées selon lignes non facturées
|
|
INSERT INTO w_mouvements_seances
|
|
SELECT p_factures.no_sejour, p_factures_lignes_c.date_debut
|
|
FROM activite.p_factures_lignes_non_facturees_c p_factures_lignes_c
|
|
JOIN activite.p_factures ON p_factures_lignes_c.no_facture = p_factures.no_facture
|
|
JOIN activite.t_lieux ON lieu_id = t_lieux.oid
|
|
JOIN activite.t_services_facturation ON service_facturation_id = t_services_facturation.oid
|
|
JOIN activite.t_prestations ON prestation_id = t_prestations.oid
|
|
LEFT JOIN w_mouvements_seances ON
|
|
w_mouvements_seances.no_sejour = p_factures.no_sejour AND
|
|
w_mouvements_seances.date_debut = p_factures_lignes_c.date_debut
|
|
WHERE
|
|
t_prestations.code <> '' AND
|
|
w_mouvements_seances.no_sejour IS NULL AND
|
|
t_services_facturation.prestations_presence <> '' AND
|
|
(t_prestations.code ILIKE ANY (string_to_array(translate(prestations_presence,'*,','% '),' ')))
|
|
GROUP BY 1,2;
|
|
|
|
-- Détection nouvelles journées pour les services concernés
|
|
RAISE NOTICE '%' , 'Initialisations. Preparation mouvements';
|
|
|
|
DROP TABLE IF EXISTS w_mouvements_nouveaux;
|
|
CREATE TEMP TABLE w_mouvements_nouveaux AS
|
|
SELECT
|
|
p_mouvements_sejour.no_sejour,
|
|
p_mouvements_sejour.date,
|
|
p_mouvements_sejour.heure_debut,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.nb_jours_js_non_inclus ELSE 0 END AS nb_jours_js_non_inclus,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.nb_jours_js_inclus ELSE 0 END AS nb_jours_js_inclus,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.est_jour_hospitalisation ELSE '0' END AS est_jour_hospitalisation,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.est_chambre_particuliere ELSE '0' END AS est_chambre_particuliere,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.est_chambre_particuliere_facturee ELSE '0' END AS est_chambre_particuliere_facturee,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.est_facture ELSE '0' END AS est_facture,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.nb_jours_d ELSE 0 END AS nb_jours_d,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.nb_jours_f ELSE 0 END AS nb_jours_f,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.nb_jours_12 ELSE 0 END AS nb_jours_12,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.nb_jours_prorata ELSE 0 END AS nb_jours_prorata,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.nb_jours_cp_d ELSE 0 END AS nb_jours_cp_d,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.nb_jours_cp_f ELSE 0 END AS nb_jours_cp_f,
|
|
CASE WHEN w_mouvements_seances.no_sejour IS NOT NULL THEN p_mouvements_sejour.nb_jours_cp_12 ELSE 0 END AS nb_jours_cp_12
|
|
FROM activite.p_mouvements_sejour
|
|
JOIN activite.t_lieux ON lieu_id = t_lieux.oid
|
|
JOIN activite.t_services_facturation ON service_facturation_id = t_services_facturation.oid
|
|
LEFT JOIN w_mouvements_seances ON p_mouvements_sejour.no_sejour = w_mouvements_seances.no_sejour AND
|
|
p_mouvements_sejour.date = w_mouvements_seances.date_debut
|
|
WHERE t_services_facturation.prestations_presence <> '' AND
|
|
w_mouvements_seances.no_sejour IS NULL;
|
|
CREATE INDEX w_mouvements_nouveaux_i1 ON w_mouvements_nouveaux USING btree (no_sejour);
|
|
|
|
-- Modification table des mouvements de séjour
|
|
RAISE NOTICE '%' , 'Initialisations. Modification mouvements';
|
|
UPDATE activite.p_mouvements_sejour SET
|
|
nb_jours_js_non_inclus = w_mouvements_nouveaux.nb_jours_js_non_inclus,
|
|
nb_jours_js_inclus = w_mouvements_nouveaux.nb_jours_js_inclus,
|
|
est_jour_hospitalisation = w_mouvements_nouveaux.est_jour_hospitalisation,
|
|
est_chambre_particuliere = w_mouvements_nouveaux.est_chambre_particuliere,
|
|
est_chambre_particuliere_facturee = w_mouvements_nouveaux.est_chambre_particuliere_facturee,
|
|
est_facture = w_mouvements_nouveaux.est_facture,
|
|
nb_jours_d = w_mouvements_nouveaux.nb_jours_d ,
|
|
nb_jours_f = w_mouvements_nouveaux.nb_jours_f ,
|
|
nb_jours_12 = w_mouvements_nouveaux.nb_jours_12,
|
|
nb_jours_prorata = w_mouvements_nouveaux.nb_jours_prorata,
|
|
nb_jours_cp_d = w_mouvements_nouveaux.nb_jours_cp_d,
|
|
nb_jours_cp_f = w_mouvements_nouveaux.nb_jours_cp_f,
|
|
nb_jours_cp_12 = w_mouvements_nouveaux.nb_jours_cp_12
|
|
FROM w_mouvements_nouveaux
|
|
WHERE p_mouvements_sejour.no_sejour = w_mouvements_nouveaux.no_sejour AND
|
|
p_mouvements_sejour.date = w_mouvements_nouveaux.date AND
|
|
p_mouvements_sejour.heure_debut = w_mouvements_nouveaux.heure_debut AND
|
|
(
|
|
p_mouvements_sejour.nb_jours_js_non_inclus IS DISTINCT FROM w_mouvements_nouveaux.nb_jours_js_non_inclus OR
|
|
p_mouvements_sejour.nb_jours_js_inclus IS DISTINCT FROM w_mouvements_nouveaux.nb_jours_js_inclus OR
|
|
p_mouvements_sejour.est_jour_hospitalisation IS DISTINCT FROM w_mouvements_nouveaux.est_jour_hospitalisation OR
|
|
p_mouvements_sejour.est_chambre_particuliere IS DISTINCT FROM w_mouvements_nouveaux.est_chambre_particuliere OR
|
|
p_mouvements_sejour.est_chambre_particuliere_facturee IS DISTINCT FROM w_mouvements_nouveaux.est_chambre_particuliere_facturee OR
|
|
p_mouvements_sejour.est_facture IS DISTINCT FROM w_mouvements_nouveaux.est_facture OR
|
|
p_mouvements_sejour.nb_jours_d IS DISTINCT FROM w_mouvements_nouveaux.nb_jours_d OR
|
|
p_mouvements_sejour.nb_jours_f IS DISTINCT FROM w_mouvements_nouveaux.nb_jours_f OR
|
|
p_mouvements_sejour.nb_jours_12 IS DISTINCT FROM w_mouvements_nouveaux.nb_jours_12 OR
|
|
p_mouvements_sejour.nb_jours_prorata IS DISTINCT FROM w_mouvements_nouveaux.nb_jours_prorata OR
|
|
p_mouvements_sejour.nb_jours_cp_d IS DISTINCT FROM w_mouvements_nouveaux.nb_jours_cp_d OR
|
|
p_mouvements_sejour.nb_jours_cp_f IS DISTINCT FROM w_mouvements_nouveaux.nb_jours_cp_f OR
|
|
p_mouvements_sejour.nb_jours_cp_12 IS DISTINCT FROM w_mouvements_nouveaux.nb_jours_cp_12
|
|
);
|
|
|
|
ANALYZE activite.p_mouvements_sejour;
|
|
|
|
RETURN 'OK' ;
|
|
END;
|