cti
/
activite-gitlab
12
0
Fork 0
Code Issues 0 Pull Requests 0 Projects 0 Releases 0 Wiki Activity
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.
5 Commits
1 Branch
6.8 MiB
 
 
Branch: master
Branches Tags
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
activite-gitlab/db/schema2/functions/cti_gen_dma_ssr.yaml

577 lines
23 KiB
Raw Permalink Blame History

return: text
lang: plpgsql
src: |
DECLARE
_module_pmsissr TEXT;
result TEXT;
BEGIN
IF NOT EXISTS (SELECT * FROM pg_tables WHERE schemaname = 'pmsissr' AND tablename = 'p_ssr') THEN
return 'Pas de SSR';
END IF;
IF NOT EXISTS (SELECT * FROM pg_tables WHERE schemaname = 'pmsissr' AND tablename = 'p_file_valo_ssr') THEN
return 'Pas de visual valo installé';
END IF;
-- Génération
-- Création de la prestation DMA
INSERT INTO activite.t_rubriques_facturation(code_original, code, texte, texte_court)
SELECT 'DMASSR', 'DMASSR', 'DMA SSR', 'DMA SSR'
WHERE 'DMASSR' NOT IN (SELECT code_original FROM activite.t_rubriques_facturation WHERE code_original IS NOT NULL);
INSERT INTO activite.t_prestations(code_original, code, texte, texte_court)
SELECT 'DMASSR', 'DMASSR', 'DMA SSR', 'DMA SSR'
WHERE 'DMASSR' NOT IN (SELECT code FROM activite.t_prestations WHERE code IS NOT NULL);
-- Création des listes
INSERT INTO activite.t_listes (code, texte, is_cti, contenu_non_replicable_ailleurs, utilisateur_createur, chapitre, table_id)
SELECT
'DMAP_'||subview.code AS code,
subview.texte,
'1' AS is_cti,
'1' AS contenu_non_replicable_ailleurs,
'cti'::text AS utilisateur_createur,
'DMA SSR' AS chapitre,
t_listes_tables.oid AS table_id
FROM
(
SELECT 'MFS'::text AS code, 'Prestations impactées par la minoration frais de séjour ' AS texte
UNION
SELECT 'MFSM'::text AS code, 'Prestations négatives de la minoration frais de séjour ' AS texte
) subview
JOIN activite.t_listes_tables ON t_listes_tables.code = 'PRESTA_C'
WHERE 'DMAP_'||subview.code NOT IN (SELECT code FROm activite.t_listes WHERE code IS NOT NULL)
;
INSERT INTO activite.t_listes_contenu (liste_id, to_id)
SELECT t_listes.oid, t_prestations.oid
FROM activite.t_prestations
JOIN activite.t_listes ON t_listes.code = 'DMAP_MFS'
LEFT JOIN activite.t_listes_contenu ON t_listes_contenu.liste_id = t_listes.oid
WHERE t_prestations.code IN ('PJ', 'FJ', 'ENT', 'SSM', 'PHJ', 'PMS', 'SHO', 'SNS', 'FS', 'TSG') AND
t_listes_contenu.liste_id IS NULL
;
INSERT INTO activite.t_listes_contenu (liste_id, to_id)
SELECT t_listes.oid, t_prestations.oid
FROM activite.t_prestations
JOIN activite.t_listes ON t_listes.code = 'DMAP_MFSM'
LEFT JOIN activite.t_listes_contenu ON t_listes_contenu.liste_id = t_listes.oid
WHERE (
t_prestations.code IN ('MPJ', 'MPJS', 'MFJ', 'MENT', 'MSSM', 'MPHJ', 'MPMS', 'MSHO', 'MSNS', 'MFS', 'MTSG') OR
t_prestations.code LIKE ('DMAM%')
) AND
t_listes_contenu.liste_id IS NULL
;
-- Forcer type de ventilation journalière dans le prochain import
UPDATE activite.t_prestations
SET type_ventilation_jour = '1'
FROM activite.t_listes_contenu
JOIN activite.t_listes ON liste_id = t_listes.oid
WHERE t_listes_contenu.to_id = t_prestations.oid AND
t_listes.code IN ('DMAP_MFS','DMAP_MFSM') AND
type_ventilation_jour IS DIStINCT FROM '1'
;
-- Suppression avant génération
DELETE FROM activite.p_factures_lignes_c WHERE no_facture LIKE '%.DMA';
DELETE FROM activite.p_factures WHERE no_facture LIKE '%.DMA';
PERFORM setval('activite.s_factures', COALESCE(MAX(oid),0) + 1) FROM activite.p_factures;
-- Cas de plusieurs SSR pour le même numéro de séjour, identification du bon pour l'année
DROP TABLE IF EXISTS w_ssr;
CREATE TEMP TABLE w_ssr AS
SELECT p_ssr.oid,
trim(num_admin_sejour) AS num_admin_sejour,
CASE WHEN trim(num_admin_sejour) IN (
SELECT trim(num_admin_sejour)
FROM pmsissr.p_ssr
JOIN pmsissr.p_rhs ON ssr_id = p_ssr.oid
WHERE est_version_actuelle = '1'
GROUP BY 1
HAVING count(DISTINCT ssr_id) > 1) THEN import_ref_year::numeric ELSE 0 END AS import_ref_year,
MAX(p_ssr.date_entree) AS date_entree,
MAX(p_ssr.date_sortie) AS date_sortie,
MAX(p_ssr.nb_jours) AS nb_jours
FROM pmsissr.p_ssr
JOIN pmsissr.p_rhs ON ssr_id = p_ssr.oid
JOIN pmsissr.p_imports ON import_id = p_imports.oid
WHERE import_ref_year >= 2017 AND
est_version_actuelle = '1'
GROUP BY 1,2,3
ORDER BY 2
;
ANALYSE w_ssr
;
-- Correction p_file_valo_ssr
UPDATE pmsissr.p_file_valo_ssr
SET numsemaine = subq.numsemaine
FROM
(
SELECT
(MAX(Array[p_rhs.date_fin::text,LPAD(p_rhs.num_semaine, 2, '0') || p_rhs.annee::text]))[2] as numsemaine,
p_rhs.ssr_id,
trim(p_ssr.num_admin_sejour)::text as numadmin
FROM pmsissr.p_ssr
JOIN pmsissr.p_rhs ON p_rhs.ssr_id = p_ssr.oid
JOIN pmsissr.p_file_valo_ssr ON numadmin = num_admin_sejour
WHERE p_file_valo_ssr.numsemaine = '522016' AND file_name_annee_import = 2017 AND hosp = 'C'
GROUP BY 2,3
) subq
WHERE 1=1 AND
trim(p_file_valo_ssr.numadmin)::text = subq.numadmin AND
p_file_valo_ssr.numsemaine = '522016' AND
file_name_annee_import = 2017 AND
subq.numsemaine <> '522016'
;
UPDATE pmsissr.p_file_valo_ssr
SET numsemaine = lpad(numsemaine,6,'0')
WHERE length(numsemaine) = 5
;
UPDATE pmsissr.p_file_valo_ssr
SET numadmin = trim(numadmin)
WHERE numadmin <> trim(numadmin)
;
DELETE FROM pmsissr.p_file_valo_ssr
USING
(
SELECT finess, date_part('year',file_name_date_import) AS year, MAX(file_name_date_import) AS file_name_date_import_ok
FROM pmsissr.p_file_valo_ssr
GROUP BY 1,2
) subview
WHERE p_file_valo_ssr.finess = subview.finess AND
date_part('year',file_name_date_import) = subview.year AND
file_name_date_import <> file_name_date_import_ok
;
-- Identification reprise. Ajout champs pour comptabililité
PERFORM base.cti_execute('ALTER TABLE pmsissr.p_file_valo_ssr ADD COLUMN ' || new_column_name || ' numeric DEFAULT 0',1)
FROM
(
SELECT 'avance_reprise_nbj'::text AS new_column_name
UNION
SELECT 'avance_reprise_dma_br' AS new_column_name
UNION
SELECT 'avance_reprise_dma_br_prud' AS new_column_name
UNION
SELECT 'avance_reprise_dma_am' AS new_column_name
) subview
LEFT JOIN information_schema.columns ON
table_schema = 'pmsissr' AND
table_name = 'p_file_valo_ssr' AND
column_name = new_column_name
WHERE column_name IS NULL
;
-- semaines en double car même semaine sur plusieurs imports
DROP TABLE IF EXISTS w_file_valo_ssr;
CREATE TEMP TABLE w_file_valo_ssr AS
SELECT
file_name_annee_import,
numadmin,
numsemaine,
hosp,
MAX(txremb) AS txremb,
SUM(nbjp-avance_reprise_nbj) AS nbjp,
SUM(dma_am-avance_reprise_dma_am) AS dma_am,
count(*)
FROM pmsissr.p_file_valo_ssr
WHERE dma_am <> 0
GROUP BY 1,2,3,4
;
ANALYSE w_file_valo_ssr
;
CREATE INDEX iw_file_valo_ssr_1 ON w_file_valo_ssr USING btree (numadmin)
;
-- Identification des factures de référence pour génération sur dernière
DROP TABLE IF EXISTS w_gen_dma_facture_reference;
CREATE TEMP TABLE w_gen_dma_facture_reference AS
SELECT subview.no_sejour,
subview.sejour_id,
subview.no_facture_reference,
COALESCE(p_factures.facture_reference_id,0) AS facture_reference_id,
COALESCE(p_factures.date_facture,subview.date_sortie) AS date_facture,
COALESCE(p_factures.code_vente,'0') AS code_vente,
COALESCE(p_factures.mois_vente,209912) AS mois_vente,
COALESCE(p_factures.date_vente,'20991231') AS date_vente,
COALESCE(p_factures.tiers_payant_0_id,subview.tiers_payant_0_id) AS tiers_payant_0_id,
COALESCE(p_factures.tiers_payant_1_id,subview.tiers_payant_1_id) AS tiers_payant_1_id,
COALESCE(p_factures.tiers_payant_2_id,subview.tiers_payant_2_id) AS tiers_payant_2_id,
COALESCE(p_factures.tiers_payant_22_id,subview.tiers_payant_22_id) AS tiers_payant_22_id
FROM
(
SELECT
p_sejours.no_sejour,
p_sejours.oid AS sejour_id,
MAX(p_sejours.date_sortie) AS date_sortie,
MAX(p_sejours.tiers_payant_0_id) AS tiers_payant_0_id,
MAX(p_sejours.tiers_payant_1_id) AS tiers_payant_1_id,
MAX(p_sejours.tiers_payant_2_id) AS tiers_payant_2_id,
MAX(p_sejours.tiers_payant_22_id) AS tiers_payant_22_id,
COALESCE((MAX(Array[p_factures_reference.date_facture::text,no_facture_reference]))[2],'NOSEJOUR'||MAX(p_sejours.no_sejour)) AS no_facture_reference
FROM activite.p_sejours
LEFT JOIN activite.p_factures_reference ON p_factures_reference.no_sejour = p_sejours.no_sejour
GROUP BY 1,2
) subview
LEFT JOIN activite.p_factures ON subview.no_facture_reference = p_factures.no_facture
;
-- Factures DMA à partir de valo SSR
DROP TABLE IF EXISTS w_gen_dma_facture;
CREATE TEMP TABLE w_gen_dma_facture AS
SELECT
p_sejour_pmsi.no_sejour,
p_sejour_pmsi.sejour_id,
w_ssr.oid AS ssr_id,
w_ssr.date_entree AS date_debut_dma,
file_name_annee_import,
hosp,
CASE WHEN w_ssr.nb_jours = nbjp AND w_ssr.date_sortie <> '2099-12-31' THEN w_ssr.date_sortie ELSE COALESCE(p_calendrier.date,'2099-12-31') END AS date_fin_dma,
NULL::date AS date_fin_facture,
CASE WHEN numsemaine <> '' THEN COALESCE((substr(numsemaine,3,4) || substr(numsemaine,1,2))::numeric,209912) ELSE 0 END AS semaine_valo,
GREATEST(nbjp,1) AS nbjp_source,
GREATEST(nbjp,1) AS nbjp,
'0'::text AS est_suite,
txremb,
dma_am,
round(dma_am / GREATEST(nbjp,1),2) AS dma_pu,
0::numeric AS dma_round,
''::text AS no_facture_reference,
''::text AS no_facture
FROM w_file_valo_ssr p_file_valo_ssr
JOIN w_ssr ON w_ssr.num_admin_sejour = p_file_valo_ssr.numadmin AND (w_ssr.import_ref_year = file_name_annee_import OR w_ssr.import_ref_year = 0)
JOIN activite.p_sejour_pmsi ON w_ssr.oid = ANY(p_sejour_pmsi.ssr_id_array)
JOIN w_gen_dma_facture_reference ON p_sejour_pmsi.sejour_id = w_gen_dma_facture_reference.sejour_id
LEFT JOIN base.p_calendrier ON CASE WHEN numsemaine <> '' THEN COALESCE((substr(numsemaine,3,4) || substr(numsemaine,1,2))::numeric,209912) ELSE 0 END = p_calendrier.semaine AND
p_calendrier.jour_semaine = '7'
WHERE dma_am <> 0
;
WITH w_set_date_fin_dma AS
(
SELECT
sum( jour_1 + jour_2 +jour_3 +jour_4 +jour_5 +jour_6 + jour_7) over ( partition by ssr_id order by p_rhs.date_fin, p_rhs.oid) as nb_jours_cum_rhs,
date_fin,
ssr_id,
p_rhs.date_sortie
FROM pmsissr.p_ssr
JOIN pmsissr.p_rhs ON p_ssr.oid = ssr_id
WHERE p_rhs.est_version_actuelle = '1' AND p_rhs.date_debut >= '20170102'
)
UPDATE w_gen_dma_facture
SET date_fin_dma = COALESCE(w_set_date_fin_dma.date_fin, w_set_date_fin_dma.date_sortie)
FROM w_set_date_fin_dma
WHERE true
AND w_gen_dma_facture.ssr_id = w_set_date_fin_dma.ssr_id
AND nb_jours_cum_rhs = nbjp
AND date_fin_dma = '2099-12-31'
;
UPDATE w_gen_dma_facture
SET date_fin_dma = subview.date_fin_dma
FROM
(
SELECT
w_gen_dma_facture.file_name_annee_import,
p_rhs.ssr_id,
w_gen_dma_facture.no_sejour,
w_gen_dma_facture.no_facture,
MAX(p_rhs.date_fin) AS date_fin_dma
FROM pmsissr.p_ssr
JOIN pmsissr.p_rhs ON p_ssr.oid = p_rhs.ssr_id
JOIN base.p_calendrier ON p_rhs.date_fin = p_calendrier.date
JOIN w_gen_dma_facture ON
w_gen_dma_facture.ssr_id = p_ssr.oid AND
w_gen_dma_facture.file_name_annee_import = substr(semaine::text,1,4)::numeric AND
(w_gen_dma_facture.date_fin_dma = '20991231' OR w_gen_dma_facture.hosp = 'C')
WHERE p_rhs.est_version_actuelle = '1'
GROUP BY 1,2,3,4
) subview
WHERE w_gen_dma_facture.ssr_id = subview.ssr_id AND
w_gen_dma_facture.file_name_annee_import = subview.file_name_annee_import AND
w_gen_dma_facture.no_sejour = subview.no_sejour AND
w_gen_dma_facture.no_facture = subview.no_facture AND
(w_gen_dma_facture.date_fin_dma = '20991231' OR w_gen_dma_facture.hosp = 'C')
;
UPDATE w_gen_dma_facture
SET date_debut_dma = w_gen_dma_facture2.date_fin_dma + interval '1 day', est_suite = '1'
FROM w_gen_dma_facture w_gen_dma_facture2
WHERE
w_gen_dma_facture.no_sejour = w_gen_dma_facture2.no_sejour AND
w_gen_dma_facture.file_name_annee_import = w_gen_dma_facture2.file_name_annee_import + 1
;
-- Sur hospit complète, le nombre de jours de nbjp n'est pas forcément égal aux rhs de l'année (cas des avances)
UPDATE w_gen_dma_facture
SET nbjp = subview.nb_jours_rhs, dma_pu = round(dma_am / subview.nb_jours_rhs,2)
FROM
(
SELECT
p_ssr.num_admin_sejour,
p_rhs.ssr_id,
file_name_annee_import,
sum( jour_1 + jour_2 +jour_3 +jour_4 +jour_5 +jour_6 + jour_7) as nb_jours_rhs
FROM pmsissr.p_ssr
JOIN pmsissr.p_rhs ON p_ssr.oid = ssr_id
JOIN w_gen_dma_facture ON
p_ssr.oid = w_gen_dma_facture.ssr_id AND
p_rhs.date_fin BETWEEN date_debut_dma AND date_fin_dma
WHERE hosp = 'C' AND
p_rhs.est_version_actuelle = '1' AND
p_rhs.date_debut >= '20170102' AND
est_suite = '1'
GROUP BY 1,2,3
HAVING sum( jour_1 + jour_2 +jour_3 +jour_4 +jour_5 +jour_6 + jour_7) <> MAX(nbjp_source) AND
sum( jour_1 + jour_2 +jour_3 +jour_4 +jour_5 +jour_6 + jour_7) > 0
ORDER BY 1,3
) subview
WHERE w_gen_dma_facture.ssr_id = subview.ssr_id AND
w_gen_dma_facture.file_name_annee_import = subview.file_name_annee_import
;
UPDATE w_gen_dma_facture
SET semaine_valo = p_calendrier.semaine
FROM base.p_calendrier
WHERE date_fin_dma = p_calendrier.date AND
semaine_valo = 0
;
UPDATE w_gen_dma_facture
SET dma_round = dma_am - (nbjp*dma_pu)
;
UPDATE w_gen_dma_facture SET
no_facture_reference = w_gen_dma_facture_reference.no_facture_reference,
no_facture = w_gen_dma_facture_reference.no_facture_reference || '.' || substr(file_name_annee_import::text,3,2) || '.DMA'
FROm w_gen_dma_facture_reference
WHERE w_gen_dma_facture.no_sejour = w_gen_dma_facture_reference.no_sejour
;
UPDATE w_gen_dma_facture
SET date_fin_facture =
CASE
WHEN date_fin_dma BETWEEN date_debut_ssr AND date_fin_ssr THEN date_fin_dma
WHEN date_fin_dma < date_debut_ssr THEN date_debut_ssr
WHEN date_fin_dma > date_fin_ssr THEN date_fin_ssr
ELSE NULL END
FROM
(
SELECT date_part('year',date) AS annee_ssr,
MIN(date) AS date_debut_ssr,
MAX(date) AS date_fin_ssr
FROM base.p_calendrier
GROUP BY 1
ORDER BY 1
) subview
WHERE w_gen_dma_facture.file_name_annee_import = annee_ssr
;
DROP TABLE IF EXISTS w_gen_dma_calendrier;
CREATE TEMP TABLE w_gen_dma_calendrier AS
SELECT no_sejour, no_facture, semaine_valo, p_calendrier.date, p_rhs.date_fin_presence, nbjp
FROM w_gen_dma_facture
JOIN pmsissr.p_rhs ON w_gen_dma_facture.ssr_id = p_rhs.ssr_id
JOIN base.p_calendrier ON
(
hosp = 'P' AND p_calendrier.semaine = semaine_valo OR
hosp = 'C' AND p_calendrier.semaine <= semaine_valo
)
WHERE
p_rhs.est_version_actuelle = '1' AND
(
p_rhs.date_debut = p_calendrier.date AND jour_1 = 1 OR
date(p_rhs.date_debut + interval '1 day') = p_calendrier.date AND jour_2 = 1 OR
date(p_rhs.date_debut + interval '2 day') = p_calendrier.date AND jour_3 = 1 OR
date(p_rhs.date_debut + interval '3 day') = p_calendrier.date AND jour_4 = 1 OR
date(p_rhs.date_debut + interval '4 day') = p_calendrier.date AND jour_5 = 1 OR
date(p_rhs.date_debut + interval '5 day') = p_calendrier.date AND jour_6 = 1 OR
date(p_rhs.date_debut + interval '6 day') = p_calendrier.date AND jour_7 = 1
)
;
INSERT INTO w_gen_dma_calendrier
SELECT no_sejour, no_facture, semaine_valo, p_calendrier.date, p_rhs.date_fin_presence, nbjp
FROM w_gen_dma_facture
JOIN pmsissr.p_rhs ON w_gen_dma_facture.ssr_id = p_rhs.ssr_id
JOIN base.p_calendrier ON
(
hosp = 'P' AND p_calendrier.semaine = semaine_valo OR
hosp = 'C' AND p_calendrier.semaine <= semaine_valo
)
WHERE
p_rhs.est_version_actuelle = '1' AND
hosp = 'C' AND
nbjp = 1 AND
p_rhs.date_debut = p_calendrier.date AND
jour_1 = 0 AND jour_2 = 0 AND jour_3 = 0 AND jour_4 = 0 AND jour_5 = 0 AND jour_6 = 0 AND jour_7 = 0
;
DELETE FROM w_gen_dma_calendrier
USING
(
SELECT *, row_number() OVER (PARTITION BY no_facture, semaine_valo ORDER BY date DESC) AS nbjf
FROM w_gen_dma_calendrier
) subview
WHERE w_gen_dma_calendrier.no_facture= subview.no_facture AND
w_gen_dma_calendrier.semaine_valo = subview.semaine_valo AND
w_gen_dma_calendrier.date = subview.date AND
subview.nbjf > subview.nbjp
;
INSERT INTO activite.p_factures (
sejour_id,
no_sejour,
no_facture,
no_facture_reference,
facture_reference_id,
date_debut,
date_fin,
type_facture,
code_facture,
date_facture,
code_vente,
mois_vente,
date_vente,
tiers_payant_0_id,
tiers_payant_1_id,
tiers_payant_2_id,
tiers_payant_22_id,
montant_facture_c,
montant_facture_1_c
)
SELECT
w_gen_dma_facture.sejour_id,
w_gen_dma_facture.no_sejour,
w_gen_dma_facture.no_facture,
MAX(w_gen_dma_facture.no_facture_reference) AS no_facture_reference,
MAX(w_gen_dma_facture_reference.facture_reference_id) AS facture_reference_id,
MIN(w_gen_dma_facture.date_debut_dma),
MAX(w_gen_dma_facture.date_fin_facture),
MAX('2'::text) AS type_facture,
MAX('1'::text) AS code_facture,
MAX(w_gen_dma_facture_reference.date_facture),
MAX(w_gen_dma_facture_reference.code_vente),
MAX(w_gen_dma_facture_reference.mois_vente),
MAX(w_gen_dma_facture_reference.date_vente),
MAX(w_gen_dma_facture_reference.tiers_payant_0_id),
MAX(w_gen_dma_facture_reference.tiers_payant_1_id),
MAX(w_gen_dma_facture_reference.tiers_payant_2_id),
MAX(w_gen_dma_facture_reference.tiers_payant_22_id),
SUM(dma_am) AS montant_facture_c,
SUM(dma_am) AS montant_facture_1_c
FROM w_gen_dma_facture
JOIN w_gen_dma_facture_reference ON w_gen_dma_facture.no_sejour = w_gen_dma_facture_reference.no_sejour
GROUP BY 1,2,3
;
INSERT INTO activite.p_factures_lignes_c (
facture_id,
no_facture,
date_debut,
date_fin,
nb_rubrique,
nb_prestation,
coefficient,
coefficient_mco,
rubrique_facturation_id,
rubrique_comptabilisation_id,
prestation_id,
montant_facture,
montant_facture_1,
taux_1,
prix_unitaire,
lieu_id
)
SELECT
p_factures.oid AS facture_id,
w_gen_dma_facture.no_facture,
w_gen_dma_calendrier.date,
w_gen_dma_calendrier.date,
1 AS nb_rubrique,
1 AS nb_prestation,
1 AS coefficient,
1 AS coefficient_mco,
t_rubriques_facturation.oid AS rubrique_facturation_id,
t_rubriques_facturation.oid AS rubrique_comptabilisation_id,
t_prestations.oid AS prestation_id,
dma_pu + CASE WHEN w_gen_dma_calendrier.date = date_fin_presence AND to_char(w_gen_dma_calendrier.date, 'YYYYIW') = w_gen_dma_facture.semaine_valo THEN dma_round ELSE 0 END AS montant_facture,
dma_pu + CASE WHEN w_gen_dma_calendrier.date = date_fin_presence AND to_char(w_gen_dma_calendrier.date, 'YYYYIW') = w_gen_dma_facture.semaine_valo THEN dma_round ELSE 0 END AS montant_facture_1,
txremb AS taux_1,
dma_pu AS prix_unitaire,
0::bigint AS lieu_id
FROM w_gen_dma_facture
JOIN activite.p_factures ON w_gen_dma_facture.no_facture = p_factures.no_facture
JOIN w_gen_dma_calendrier ON w_gen_dma_calendrier.no_facture = w_gen_dma_facture.no_facture AND w_gen_dma_calendrier.semaine_valo = w_gen_dma_facture.semaine_valo
JOIN activite.t_rubriques_facturation ON 'DMASSR' = t_rubriques_facturation.code_original
JOIN activite.t_prestations ON 'DMASSR' = t_prestations.code_original
;
PERFORM activite.cti_reorganize_rubrique_facture_c('activite.p_factures_lignes_c');
-- Lieu des lignes de factures (en tenant compte des absences)
DROP TABLE IF EXISTS w_mouvements_sejour_lieu_jour;
CREATE TEMP TABLE w_mouvements_sejour_lieu_jour AS
SELECT no_sejour,
date,
(MAX(CASE WHEN est_absence <> '1' THEN Array[heure_fin, lieu_id] ELSE NULL END))[2] AS lieu_id,
(MAX(Array[heure_fin, lieu_id]))[2] AS lieu_id_avec_absence
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
GROUP BY 1,2
;
UPDATE activite.p_factures_lignes_c
SET lieu_id = COALESCE(w_mouvements_sejour_lieu_jour.lieu_id,w_mouvements_sejour_lieu_jour.lieu_id_avec_absence)
FROM activite.p_factures, w_mouvements_sejour_lieu_jour
WHERE p_factures_lignes_c.no_facture = p_factures.no_facture AND
p_factures.no_sejour = w_mouvements_sejour_lieu_jour.no_sejour AND
p_factures_lignes_c.date_debut = w_mouvements_sejour_lieu_jour.date AND
p_factures_lignes_c.lieu_id <> COALESCE(w_mouvements_sejour_lieu_jour.lieu_id,w_mouvements_sejour_lieu_jour.lieu_id_avec_absence)
;
UPDATE activite.p_factures_lignes_c
SET lieu_id = p_sejours.lieu_sortie_id
FROM activite.p_factures
JOIN activite.p_sejours ON p_factures.no_sejour = p_sejours.no_sejour
WHERE p_factures_lignes_c.no_facture = p_factures.no_facture AND
p_factures_lignes_c.lieu_id = 0 AND
p_factures.no_facture LIKE '%DMA'
;
RETURN 'OK';
END;