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.
1480 lines
63 KiB
1480 lines
63 KiB
return: text
|
|
lang: plpgsql
|
|
src: |
|
|
DECLARE
|
|
result TEXT;
|
|
_module_pmsimco TEXT;
|
|
BEGIN
|
|
|
|
RAISE NOTICE '%' , 'Initialisation formes d''activite';
|
|
-- Contenu table
|
|
INSERT INTO activite.t_forme_activite (oid, code, texte, texte_court)
|
|
SELECT 0, '**', 'Non renseignée', 'Non renseignée'
|
|
WHERE 0 NOT IN (SELECT oid FROM activite.t_forme_activite);
|
|
|
|
|
|
-- MCO
|
|
INSERT INTO activite.t_forme_activite (code, texte, texte_court)
|
|
SELECT code, texte, texte
|
|
FROM (
|
|
SELECT '111' AS code, 'Hospitalisation complète MCO Médecine' AS texte
|
|
UNION
|
|
SELECT '112' AS code, 'Hospitalisation complète MCO Chirurgie' AS texte
|
|
UNION
|
|
SELECT '113' AS code, 'Hospitalisation complète MCO Obstétrique' AS texte
|
|
UNION
|
|
SELECT '114' AS code, 'Hospitalisation complète MCO Esthétique' AS texte
|
|
UNION
|
|
SELECT '117' AS code, 'Hospitalisation complète MCO A préciser' AS texte
|
|
UNION
|
|
SELECT '118' AS code, 'Hospitalisation complète MCO A préciser' AS texte
|
|
UNION
|
|
SELECT '119' AS code, 'Hospitalisation complète MCO Autre' AS texte
|
|
UNION
|
|
SELECT '121' AS code, 'Hospitalisation de jour MCO Médecine' AS texte
|
|
UNION
|
|
SELECT '122' AS code, 'Hospitalisation de jour MCO Chirurgie' AS texte
|
|
UNION
|
|
SELECT '123' AS code, 'Hospitalisation de jour MCO Obstétrique' AS texte
|
|
UNION
|
|
SELECT '124' AS code, 'Hospitalisation de jour MCO Esthétique' AS texte
|
|
UNION
|
|
SELECT '127' AS code, 'Hospitalisation de jour MCO A préciser' AS texte
|
|
UNION
|
|
SELECT '128' AS code, 'Hospitalisation de jour MCO A préciser' AS texte
|
|
UNION
|
|
SELECT '129' AS code, 'Hospitalisation de jour MCO Autre' AS texte
|
|
UNION
|
|
SELECT '131' AS code, 'Séances de dialyse' AS texte
|
|
UNION
|
|
SELECT '132' AS code, 'Séances de chimiothérapie' AS texte
|
|
UNION
|
|
SELECT '133' AS code, 'Séances de transfusion' AS texte
|
|
UNION
|
|
SELECT '139' AS code, 'Autres séances' AS texte
|
|
UNION
|
|
SELECT '211' AS code, 'Hospitalisation complète SSR Soins de Suite Médicalisés' AS texte
|
|
UNION
|
|
SELECT '212' AS code, 'Hospitalisation complète SSR Rééducation Fonctionnelle' AS texte
|
|
UNION
|
|
SELECT '217' AS code, 'Hospitalisation complète SSR A préciser' AS texte
|
|
UNION
|
|
SELECT '218' AS code, 'Hospitalisation complète SSR A préciser' AS texte
|
|
UNION
|
|
SELECT '219' AS code, 'Hospitalisation complète SSR Autre' AS texte
|
|
UNION
|
|
SELECT '221' AS code, 'Hospitalisation de jour SSR Soins de Suite Médicalisés' AS texte
|
|
UNION
|
|
SELECT '222' AS code, 'Hospitalisation de jour SSR Rééducation Fonctionnelle' AS texte
|
|
UNION
|
|
SELECT '227' AS code, 'Hospitalisation de jour SSR A préciser' AS texte
|
|
UNION
|
|
SELECT '228' AS code, 'Hospitalisation de jour SSR A préciser' AS texte
|
|
UNION
|
|
SELECT '229' AS code, 'Hospitalisation de jour SSR Autre' AS texte
|
|
UNION
|
|
SELECT '317' AS code, 'Hospitalisation complète PSY A préciser' AS texte
|
|
UNION
|
|
SELECT '318' AS code, 'Hospitalisation complète PSY A préciser' AS texte
|
|
UNION
|
|
SELECT '319' AS code, 'Hospitalisation complète PSY Autre' AS texte
|
|
UNION
|
|
SELECT '327' AS code, 'Hospitalisation de jour PSY A préciser' AS texte
|
|
UNION
|
|
SELECT '328' AS code, 'Hospitalisation de jour PSY A préciser' AS texte
|
|
UNION
|
|
SELECT '329' AS code, 'Hospitalisation de jour PSY Autre' AS texte
|
|
UNION
|
|
SELECT '401' AS code, 'Hospitalisation à Domicile' AS texte
|
|
UNION
|
|
SELECT '501' AS code, 'EHPAD' AS texte
|
|
UNION
|
|
SELECT '502' AS code, 'USLD' AS texte
|
|
UNION
|
|
SELECT '503' AS code, 'Foyer de vie' AS texte
|
|
UNION
|
|
SELECT '701' AS code, 'Activité SE' AS texte
|
|
UNION
|
|
SELECT '702' AS code, 'Urgences' AS texte
|
|
UNION
|
|
SELECT '709' AS code, 'Activité externe' AS texte
|
|
UNION
|
|
SELECT '999' AS code, 'Autres formes d''activité' AS texte
|
|
) subview
|
|
WHERE code NOT IN (SELECT code FROM activite.t_forme_activite WHERE code IS NOT NULL)
|
|
ORDER BY 1
|
|
;
|
|
|
|
-- Si consolidation, pas de règle
|
|
IF EXISTS (SELECT * FROM activite.t_forme_activite_rule WHERE code = '*NORULE_CONSO') THEN
|
|
return 'Pas de règle en consolidation';
|
|
END IF;
|
|
|
|
|
|
DELETE FROM activite.t_forme_activite_rule WHERE code LIKE 'CTI%' ;
|
|
DELETE FROM activite.t_forme_activite_rule WHERE COALESCE(forme_activite_id,0) = 0 OR
|
|
COALESCE(priorite,0) = 0;
|
|
|
|
RAISE NOTICE '%' , 'Generation regles CTI';
|
|
|
|
DROP SEQUENCE IF EXISTS w_priorite;
|
|
CREATE TEMP SEQUENCE w_priorite;
|
|
|
|
|
|
|
|
-- Associations possibles
|
|
|
|
-- Identification des codes utilisés
|
|
DROP TABLE IF EXISTS cti_rfar_w_forme_activite_used;
|
|
CREATE TEMP TABLE cti_rfar_w_forme_activite_used AS
|
|
SELECT service_facturation_id,
|
|
t_services_facturation.code AS service_facturation_code,
|
|
t_services_facturation.texte AS service_facturation_texte,
|
|
t_services_facturation.dmt_id,
|
|
t_services_facturation.mode_traitement_id,
|
|
t_services_facturation.type_t2a,
|
|
p_sejours.type_sejour,
|
|
''::text AS forme_activite_code
|
|
FROM activite.p_mouvements_sejour
|
|
JOIN activite.p_sejours ON p_mouvements_sejour.no_sejour = p_sejours.no_sejour
|
|
JOIN activite.t_lieux ON lieu_id = t_lieux.oid
|
|
JOIN activite.t_services_facturation ON t_lieux.service_facturation_id = t_services_facturation.oid
|
|
WHERE t_lieux.service_facturation_id <> 0
|
|
GROUP BY 1,2,3,4,5,6,7
|
|
;
|
|
|
|
INSERT INTO cti_rfar_w_forme_activite_used
|
|
SELECT 0::bigint AS service_facturation_id,
|
|
''::text AS service_facturation_code,
|
|
''::text AS service_facturation_texte,
|
|
0::bigint AS dmt_id,
|
|
0::bigint AS mode_traitement_id,
|
|
''::text AS type_t2a,
|
|
type_sejour,
|
|
''::text AS forme_activite_code
|
|
FROM cti_rfar_w_forme_activite_used
|
|
GROUP BY type_sejour
|
|
;
|
|
|
|
|
|
|
|
-- Valeurs par défaut par service
|
|
|
|
|
|
UPDATE cti_rfar_w_forme_activite_used
|
|
SET forme_activite_code =
|
|
CASE
|
|
WHEN service_facturation_id = 0 AND type_sejour = '1' THEN '119'
|
|
WHEN service_facturation_id = 0 AND type_sejour = '2' THEN '129'
|
|
WHEN service_facturation_id = 0 AND type_sejour = '3' THEN '709'
|
|
WHEN service_facturation_id = 0 AND type_sejour = '5' THEN '139'
|
|
WHEN service_facturation_id = 0 AND type_sejour = '6' THEN '113'
|
|
WHEN service_facturation_id = 0 AND type_sejour = '9' THEN '999'
|
|
|
|
WHEN service_facturation_texte iLIKE '%esthetique%' AND type_sejour = '2' THEN '124'
|
|
WHEN service_facturation_texte iLIKE '%esthetique%' THEN '114'
|
|
WHEN service_facturation_texte iLIKE '%esthétique%' AND type_sejour = '2' THEN '124'
|
|
WHEN service_facturation_texte iLIKE '%esthétique%' THEN '114'
|
|
|
|
WHEN t_dmt.code = '958' AND type_sejour = '3' THEN '701'
|
|
WHEN t_dmt.code = '924' THEN '501'
|
|
WHEN t_dmt.code = '657' THEN '501'
|
|
WHEN t_dmt.code = '273' THEN '502'
|
|
WHEN t_dmt.code = '936' THEN '503'
|
|
WHEN service_facturation_texte LIKE 'FSE%' AND type_sejour = '3' THEN '701'
|
|
WHEN service_facturation_texte LIKE 'SEH%' AND type_sejour = '3' THEN '701'
|
|
|
|
WHEN t_modes_traitement.code = '10' THEN '702'
|
|
|
|
WHEN t_dmt.code IN ('552','553','554','555','556','723','796','797','798') THEN '131'
|
|
WHEN t_modes_traitement.code = '19' AND t_dmt.code = '302' THEN '132'
|
|
|
|
WHEN t_modes_traitement.code = '03' AND type_t2a = '2' THEN '219'
|
|
WHEN t_modes_traitement.code = '03' AND t_dmt.texte ILIKE '%SSR%' THEN '219'
|
|
WHEN t_modes_traitement.code = '04' AND type_t2a = '2' THEN '229'
|
|
WHEN t_modes_traitement.code = '04' AND t_dmt.texte ILIKE '%SSR%' THEN '229'
|
|
WHEN t_modes_traitement.code = '19' AND type_t2a = '2' THEN '229'
|
|
WHEN t_modes_traitement.code = '19' AND t_dmt.texte ILIKE '%SSR%' THEN '229'
|
|
WHEN t_modes_traitement.code = '22' AND type_t2a = '2' THEN '229'
|
|
WHEN t_modes_traitement.code = '22' AND t_dmt.texte ILIKE '%SSR%' THEN '229'
|
|
WHEN t_modes_traitement.code = '03' AND type_t2a = '3' THEN '319'
|
|
WHEN t_modes_traitement.code = '03' AND t_dmt.texte ILIKE '%PSY%' THEN '319'
|
|
WHEN t_modes_traitement.code = '04' AND type_t2a = '3' THEN '329'
|
|
WHEN t_modes_traitement.code = '04' AND t_dmt.texte ILIKE '%PSY%' THEN '329'
|
|
WHEN t_modes_traitement.code = '19' AND type_t2a = '3' THEN '329'
|
|
WHEN t_modes_traitement.code = '19' AND t_dmt.texte ILIKE '%PSY%' THEN '329'
|
|
WHEN t_modes_traitement.code = '22' AND type_t2a = '3' THEN '329'
|
|
WHEN t_modes_traitement.code = '22' AND t_dmt.texte ILIKE '%PSY%' THEN '329'
|
|
WHEN type_t2a = '4' THEN '401'
|
|
WHEN service_facturation_texte iLIKE '%EHPAD%' THEN '501'
|
|
WHEN service_facturation_texte iLIKE '%USLD%' THEN '502'
|
|
WHEN service_facturation_texte iLIKE '%FOVAH%' THEN '503'
|
|
WHEN t_modes_traitement.code = '06' THEN '401'
|
|
|
|
ELSE ''
|
|
END
|
|
FROM base.t_modes_traitement,base.t_dmt
|
|
WHERE mode_traitement_id = t_modes_traitement.oid AND
|
|
dmt_id = t_dmt.oid
|
|
;
|
|
|
|
UPDATE cti_rfar_w_forme_activite_used
|
|
SET forme_activite_code = '212'
|
|
WHERE forme_activite_code = '219' AND
|
|
(
|
|
service_facturation_code LIKE 'REE%' OR
|
|
service_facturation_texte ILIKE '%rééducation%' OR
|
|
service_facturation_texte ILIKE '%reeducation%'
|
|
)
|
|
;
|
|
|
|
UPDATE cti_rfar_w_forme_activite_used
|
|
SET forme_activite_code = '222'
|
|
WHERE forme_activite_code = '229' AND
|
|
(
|
|
service_facturation_code LIKE 'REE%' OR
|
|
service_facturation_texte ILIKE '%rééducation%' OR
|
|
service_facturation_texte ILIKE '%reeducation%'
|
|
)
|
|
;
|
|
|
|
-- Les premiers 1000 oid sont réservés à CTI
|
|
PERFORM setval('activite.s_forme_activite_rule', (SELECT GREATEST(COALESCE(max(oid),0),1000) FROM activite.t_forme_activite_rule), true);
|
|
|
|
UPDATE activite.t_forme_activite_rule
|
|
SET oid = nextval('activite.s_forme_activite_rule'::regclass)
|
|
WHERE oid < 1000;
|
|
|
|
|
|
PERFORM setval('w_priorite', 70000, true);
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_service)
|
|
SELECT code,
|
|
texte,
|
|
forme_activite_id,
|
|
nextval('w_priorite'::regclass),
|
|
liste_type_sejour,
|
|
liste_service
|
|
FROM
|
|
(
|
|
SELECT 'CTI#' || service_facturation_code || '#' || type_sejour AS code,
|
|
'Valeur par défaut CTI ' ||
|
|
service_facturation_texte || '(' || service_facturation_code || ')' || ' - ' ||
|
|
CASE type_sejour
|
|
WHEN '1' THEN 'Hospitalisés'
|
|
WHEN '2' THEN 'Ambulatoires'
|
|
WHEN '3' THEn 'Externes'
|
|
WHEN '5' THEN 'Séances'
|
|
WHEN '6' THEN 'Bébés'
|
|
WHEN '9' THEN 'Séjours fictifs'
|
|
END AS texte
|
|
,
|
|
t_forme_activite.oid AS forme_activite_id,
|
|
type_sejour AS liste_type_sejour,
|
|
service_facturation_code AS liste_service
|
|
FROM cti_rfar_w_forme_activite_used
|
|
JOIN activite.t_forme_activite ON t_forme_activite.code = forme_activite_code AND forme_activite_code <> ''
|
|
WHERE service_facturation_id <> 0
|
|
ORDER BY 1
|
|
) subview
|
|
;
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_service_entree)
|
|
SELECT code,
|
|
texte,
|
|
forme_activite_id,
|
|
nextval('w_priorite'::regclass),
|
|
liste_type_sejour,
|
|
liste_service
|
|
FROM
|
|
(
|
|
SELECT 'CTI#' || service_facturation_code || '#' || type_sejour AS code,
|
|
'Valeur par défaut CTI ' ||
|
|
service_facturation_texte || '(' || service_facturation_code || ')' || ' - ' ||
|
|
CASE type_sejour
|
|
WHEN '1' THEN 'Hospitalisés'
|
|
WHEN '2' THEN 'Ambulatoires'
|
|
WHEN '3' THEn 'Externes'
|
|
WHEN '5' THEN 'Séances'
|
|
WHEN '6' THEN 'Bébés'
|
|
WHEN '9' THEN 'Séjours fictifs'
|
|
END AS texte
|
|
,
|
|
t_forme_activite.oid AS forme_activite_id,
|
|
type_sejour AS liste_type_sejour,
|
|
service_facturation_code AS liste_service
|
|
FROM cti_rfar_w_forme_activite_used
|
|
JOIN activite.t_forme_activite ON t_forme_activite.code = forme_activite_code AND forme_activite_code <> ''
|
|
WHERE service_facturation_id <> 0
|
|
ORDER BY 1
|
|
) subview
|
|
;
|
|
|
|
PERFORM setval('w_priorite', 99000, true);
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour)
|
|
SELECT code,
|
|
texte,
|
|
forme_activite_id,
|
|
nextval('w_priorite'::regclass),
|
|
liste_type_sejour
|
|
FROM
|
|
(
|
|
SELECT 'CTI#'||type_sejour AS code,
|
|
'Valeur par défaut CTI ' ||
|
|
CASE type_sejour
|
|
WHEN '1' THEN 'Hospitalisés'
|
|
WHEN '2' THEN 'Ambulatoires'
|
|
WHEN '3' THEn 'Externes'
|
|
WHEN '5' THEN 'Séances'
|
|
WHEN '6' THEN 'Bébés'
|
|
WHEN '9' THEN 'Séjours fictifs'
|
|
END AS texte
|
|
,
|
|
t_forme_activite.oid AS forme_activite_id,
|
|
type_sejour AS liste_type_sejour
|
|
FROM cti_rfar_w_forme_activite_used
|
|
JOIN activite.t_forme_activite ON t_forme_activite.code = forme_activite_code AND forme_activite_code <> ''
|
|
WHERE service_facturation_id = 0
|
|
ORDER BY 1
|
|
) subview
|
|
;
|
|
|
|
|
|
|
|
-- Spécifique selon les GHM
|
|
PERFORM setval('w_priorite', 10000, true);
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#GHM1415#1',
|
|
'Valeur par défaut CTI CMD 14 et 15 - Hospitalisés',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'1',
|
|
'14* 15*'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '113'
|
|
ORDER BY 1
|
|
;
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#GHM1415#2',
|
|
'Valeur par défaut CTI CMD 14 et 15 - Ambulatoires',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'2',
|
|
'14* 15*'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '123'
|
|
ORDER BY 1
|
|
;
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#GHM09Z02#1',
|
|
'Valeur par défaut CTI GHM 09Z02, 23Z03 - Hospitalisés',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'1',
|
|
'09Z02* 23Z03*'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '114'
|
|
ORDER BY 1
|
|
;
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#GHM09Z02#2',
|
|
'Valeur par défaut CTI GHM 09Z02, 23Z03 - Ambulatoires',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'2',
|
|
'09Z02* 23Z03*'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '124'
|
|
ORDER BY 1
|
|
;
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#GHM28DIA',
|
|
'Valeur par défaut CTI GHM Dialyse - Séances',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'5',
|
|
'28Z01Z 28Z02Z 28Z03Z 28Z04Z 28Z05Z 28Z06Z'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '131'
|
|
ORDER BY 1
|
|
;
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#GHM28CHM',
|
|
'Valeur par défaut CTI GHM Chimio - Séances',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'5',
|
|
'28Z07Z 28Z17Z'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '132'
|
|
ORDER BY 1
|
|
;
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#GHM28TRF',
|
|
'Valeur par défaut CTI GHM Dialyse - Séances',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'5',
|
|
'28Z14Z'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '133'
|
|
ORDER BY 1
|
|
;
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#GHM28AUT',
|
|
'Valeur par défaut CTI - Autres séances',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'5',
|
|
'28*'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '139'
|
|
ORDER BY 1
|
|
;
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#GHM28CHM1',
|
|
'Valeur par défaut CTI GHM Chimio - Hospit = Médecine',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'1',
|
|
'28*'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '111'
|
|
ORDER BY 1
|
|
;
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#GHM28CHM1',
|
|
'Valeur par défaut CTI GHM Chimio - Ambu = Médecine',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'2',
|
|
'28*'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '121'
|
|
ORDER BY 1
|
|
;
|
|
|
|
-- Spécifique selon les classes de GHM
|
|
PERFORM setval('w_priorite', 50100, true);
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#CASC#1',
|
|
'Valeur par défaut CTI CAS C - Hospitalisés',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'1',
|
|
'[CAS:C]'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '112'
|
|
ORDER BY 1
|
|
;
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#CASC#1',
|
|
'Valeur par défaut CTI CAS C - Ambulatoires',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'2',
|
|
'[CAS:C]'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '122'
|
|
ORDER BY 1
|
|
;
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#CASCKX#1',
|
|
'Valeur par défaut CTI CAS KX - Hospitalisés',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'1',
|
|
'[CAS:K] [CAS:X]'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '111'
|
|
ORDER BY 1
|
|
;
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_ghm)
|
|
SELECT 'CTI#CASKX#1',
|
|
'Valeur par défaut CTI CAS KX - Ambulatoires',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'2',
|
|
'[CAS:K] [CAS:X]'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '121'
|
|
ORDER BY 1
|
|
;
|
|
|
|
|
|
-- Spécifique selon les rubriques
|
|
PERFORM setval('w_priorite', 50200, true);
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_prestation)
|
|
SELECT 'CTI#SEH#3',
|
|
'Valeur par défaut CTI SEh - Externes',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'3',
|
|
'SE1 SE2 SE3 SE4 SE5 SE6 SE7'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '701'
|
|
ORDER BY 1
|
|
;
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_prestation)
|
|
SELECT 'CTI#GHS#1',
|
|
'Valeur par défaut CTI GHS - Hospitalisés',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'1',
|
|
'GHS'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '119'
|
|
ORDER BY 1
|
|
;
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_prestation)
|
|
SELECT 'CTI#GHS#2',
|
|
'Valeur par défaut CTI GHS - Ambulatoires',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'2',
|
|
'GHS'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '129'
|
|
ORDER BY 1
|
|
;
|
|
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite, liste_type_sejour, liste_prestation)
|
|
SELECT 'CTI#ATU#3',
|
|
'Valeur par défaut CTI ATU - Externes',
|
|
t_forme_activite.oid,
|
|
nextval('w_priorite'::regclass),
|
|
'3',
|
|
'ATU FPU FPV FPX FPL FPM'
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '702'
|
|
ORDER BY 1
|
|
;
|
|
|
|
-- Autres
|
|
INSERT INTO activite.t_forme_activite_rule (code, texte, forme_activite_id, priorite)
|
|
SELECT 'CTI#999',
|
|
'Autres formes',
|
|
t_forme_activite.oid,
|
|
99999
|
|
FROM activite.t_forme_activite
|
|
WHERE t_forme_activite.code = '999'
|
|
ORDER BY 1
|
|
;
|
|
|
|
|
|
PERFORM setval('activite.s_forme_activite_rule', (SELECT GREATEST(COALESCE(max(oid),0),1000) FROM activite.t_forme_activite_rule), true);
|
|
|
|
UPDATE activite.t_forme_activite_rule
|
|
SET oid = nextval('activite.s_forme_activite_rule'::regclass)
|
|
WHERE oid < 1000;
|
|
|
|
PERFORM setval('activite.s_forme_activite_rule', 1, true);
|
|
|
|
|
|
|
|
RAISE NOTICE '%' , 'Application des regles';
|
|
|
|
-- pmsi mco ?
|
|
IF EXISTS (SELECT * FROM pg_tables WHERE schemaname = 'pmsi' AND tablename = 'p_rss') THEN
|
|
_module_pmsimco = '1';
|
|
RAISE NOTICE '%' , 'Application des regles. Avec PMSI MCO';
|
|
ELSE
|
|
_module_pmsimco = '0';
|
|
END IF;
|
|
|
|
|
|
-- Sejours et RSS à Traiter
|
|
|
|
RAISE NOTICE '%' , 'Application des regles. Identification des sejours';
|
|
DROP SEQUENCE IF EXISTS w_sejours_rss_sequence;
|
|
CREATE SEQUENCE w_sejours_rss_sequence;
|
|
DROP TABLE IF EXISTS w_sejours_rss;
|
|
|
|
IF _module_pmsimco = '1' THEN
|
|
-- si pmsi mco
|
|
CREATE TEMP TABLE w_sejours_rss AS
|
|
SELECT
|
|
nextval('w_sejours_rss_sequence'::regclass) AS oid,
|
|
COALESCE(t_finess.oid,0) AS finess_id,
|
|
p_sejours.oid AS sejour_id,
|
|
p_sejours.no_sejour AS no_sejour,
|
|
COALESCE(p_rss.oid,0) AS rss_id,
|
|
diagnostic_principal_id,
|
|
diagnostic_relie_id,
|
|
NULL::bigint[] AS diagnostic_associe_liste_rule_id,
|
|
NULL::bigint[] AS acte_liste_rule_id,
|
|
NULL::bigint[] AS prestation_liste_rule_id,
|
|
NULL::bigint[] AS rubrique_liste_rule_id,
|
|
p_rss.ghm_id AS ghm_pmsi_id,
|
|
p_sejours.ghm_id AS ghm_activite_id,
|
|
COALESCE(medecin_rss_id,0) AS medecin_rss_id,
|
|
medecin_sejour_id,
|
|
COALESCE(t_medecins_pmsi.specialite_id,0) AS specialite_rss_id,
|
|
COALESCE(t_medecins_base.specialite_id,0) AS specialite_sejour_id,
|
|
p_sejours.type_sejour,
|
|
t_lieux.unite_fonctionnelle_id,
|
|
t_lieux.service_facturation_id,
|
|
t_lits.etage_id,
|
|
t_lieux.unite_fonctionnelle_id AS unite_fonctionnelle_entree_id,
|
|
t_lieux.service_facturation_id AS service_facturation_entree_id,
|
|
t_lits.etage_id AS etage_entree_id,
|
|
t_lieux.activite_id,
|
|
0::bigint AS forme_activite_id,
|
|
0::bigint AS forme_activite_priorite
|
|
FROM activite.p_sejours
|
|
JOIN activite.t_lieux ON lieu_sortie_id = t_lieux.oid
|
|
JOIN activite.t_lits ON t_lieux.lit_id = t_lits.oid
|
|
LEFT JOIN pmsi.p_rss ON no_sejour_administratif = no_sejour AND p_sejours.date_sortie = p_rss.date_sortie AND p_rss.etat = ''
|
|
LEFT JOIN pmsi.t_medecins t_medecins_pmsi ON medecin_rss_id = t_medecins_pmsi.oid
|
|
LEFT JOIN activite.t_medecins_administratifs ON medecin_sejour_id = t_medecins_administratifs.oid
|
|
LEFT JOIN base.t_medecins t_medecins_base ON t_medecins_administratifs.medecin_id = t_medecins_base.oid
|
|
LEFT JOIN base.t_finess ON p_sejours.finess = t_finess.code;
|
|
INSERT INTO w_sejours_rss
|
|
SELECT
|
|
nextval('w_sejours_rss_sequence'::regclass) AS oid,
|
|
COALESCE(t_finess.oid,0) AS finess_id,
|
|
0 AS sejour_id,
|
|
'' AS no_sejour,
|
|
p_rss.oid AS rss_id,
|
|
diagnostic_principal_id,
|
|
diagnostic_relie_id,
|
|
NULL::bigint[] AS diagnostic_associe_liste_rule_id,
|
|
NULL::bigint[] AS acte_liste_rule_id,
|
|
NULL::bigint[] AS prestation_liste_rule_id,
|
|
NULL::bigint[] AS rubrique_liste_rule_id,
|
|
p_rss.ghm_id AS ghm_pmsi_id,
|
|
0 AS ghm_activite_id,
|
|
medecin_rss_id,
|
|
0::bigint AS medecin_sejour_id,
|
|
COALESCE(t_medecins_pmsi.specialite_id,0) AS specialite_rss_id,
|
|
0::bigint AS specialite_sejour_id,
|
|
''::text AS type_sejour,
|
|
0::bigint AS unite_fonctionnelle_id,
|
|
0::bigint AS service_facturation_id,
|
|
0::bigint AS etage_id,
|
|
0::bigint AS unite_fonctionnelle_entree_id,
|
|
0::bigint AS service_facturation_entree_id,
|
|
0::bigint AS etage_entree_id,
|
|
0::bigint AS activite_id,
|
|
0::bigint AS forme_activite_id,
|
|
0::bigint AS forme_activite_priorite
|
|
FROM pmsi.p_rss
|
|
LEFT JOIN pmsi.t_medecins t_medecins_pmsi ON medecin_rss_id = t_medecins_pmsi.oid
|
|
LEFT JOIN base.t_finess ON p_rss.finess = t_finess.code
|
|
WHERE date_sortie >= '20100101' AND p_rss.oid NOT IN (SELECt rss_id FROM w_sejours_rss);
|
|
UPDATE w_sejours_rss SET
|
|
medecin_rss_id = t_medecins.oid,
|
|
specialite_rss_id = t_medecins.specialite_id
|
|
FROM activite.t_medecins_administratifs_c
|
|
JOIN pmsi.t_medecins ON t_medecins_administratifs_c.adm_code = t_medecins.code
|
|
WHERE medecin_sejour_id = t_medecins_administratifs_c.oid AND
|
|
t_medecins.oid <> 0 AND
|
|
(t_medecins.oid IS DISTINCT FROM medecin_rss_id OR
|
|
specialite_rss_id IS DISTINCT FROM t_medecins.specialite_id)
|
|
;
|
|
ELSE
|
|
-- si pas pmsi mco
|
|
CREATE TEMP TABLE w_sejours_rss AS
|
|
SELECT
|
|
nextval('w_sejours_rss_sequence'::regclass) AS oid,
|
|
COALESCE(t_finess.oid,0) AS finess_id,
|
|
p_sejours.oid AS sejour_id,
|
|
p_sejours.no_sejour AS no_sejour,
|
|
0 AS rss_id,
|
|
0::bigint AS diagnostic_principal_id,
|
|
0::bigint AS diagnostic_relie_id,
|
|
NULL::bigint[] AS diagnostic_associe_liste_rule_id,
|
|
NULL::bigint[] AS acte_liste_rule_id,
|
|
NULL::bigint[] AS prestation_liste_rule_id,
|
|
NULL::bigint[] AS rubrique_liste_rule_id,
|
|
0::bigint AS ghm_pmsi_id,
|
|
p_sejours.ghm_id AS ghm_activite_id,
|
|
0 AS medecin_rss_id,
|
|
medecin_sejour_id,
|
|
0 AS specialite_rss_id,
|
|
COALESCE(t_medecins_base.specialite_id,0) AS specialite_sejour_id,
|
|
p_sejours.type_sejour,
|
|
t_lieux.unite_fonctionnelle_id,
|
|
t_lieux.service_facturation_id,
|
|
t_lits.etage_id,
|
|
t_lieux.unite_fonctionnelle_id AS unite_fonctionnelle_entree_id,
|
|
t_lieux.service_facturation_id AS service_facturation_entree_id,
|
|
t_lits.etage_id AS etage_entree_id,
|
|
t_lieux.activite_id,
|
|
0::bigint AS forme_activite_id,
|
|
0::bigint AS forme_activite_priorite
|
|
FROM activite.p_sejours
|
|
JOIN activite.t_lieux ON lieu_sortie_id = t_lieux.oid
|
|
JOIN activite.t_lits ON t_lieux.lit_id = t_lits.oid
|
|
LEFT JOIN base.t_finess ON p_sejours.finess = t_finess.code
|
|
LEFT JOIN activite.t_medecins_administratifs ON medecin_sejour_id = t_medecins_administratifs.oid
|
|
LEFT JOIN base.t_medecins t_medecins_base ON t_medecins_administratifs.medecin_id = t_medecins_base.oid;
|
|
END IF;
|
|
|
|
-- Unites d'entrées
|
|
UPDATE w_sejours_rss SET
|
|
unite_fonctionnelle_entree_id = t_lieux.unite_fonctionnelle_id,
|
|
service_facturation_entree_id = t_lieux.service_facturation_id,
|
|
etage_entree_id = t_lits.etage_id
|
|
FROM activite.p_mouvements_sejour
|
|
JOIN activite.t_lieux ON p_mouvements_sejour.lieu_id = t_lieux.oid
|
|
JOIN activite.t_lits ON t_lieux.lit_id = t_lits.oid
|
|
WHERE w_sejours_rss.no_sejour = p_mouvements_sejour.no_sejour AND nb_entrees_directes = 1 AND
|
|
(
|
|
t_lieux.unite_fonctionnelle_id <> unite_fonctionnelle_entree_id OR
|
|
t_lieux.service_facturation_id <> service_facturation_entree_id OR
|
|
t_lits.etage_id <> etage_entree_id
|
|
)
|
|
;
|
|
|
|
|
|
-- GHM sur services non MCO
|
|
-- Verrouillé pour l'instant (on verra par la suite)
|
|
-- UPDATE w_sejours_rss SET
|
|
-- ghm_pmsi_id = 0,
|
|
-- ghm_activite_id = 0
|
|
-- FROM activite.t_services_facturation
|
|
-- WHERE service_facturation_id = t_services_facturation .oid AND
|
|
-- type_t2a <> '1' AND
|
|
-- service_facturation_id <> 0 AND
|
|
-- (ghm_pmsi_id > 0 OR
|
|
-- ghm_activite_id > 0)
|
|
-- ;
|
|
|
|
--
|
|
UPDATE w_sejours_rss SET
|
|
medecin_rss_id = 0,
|
|
specialite_rss_id = 0
|
|
WHERE medecin_sejour_id <> 0;
|
|
UPDATE w_sejours_rss
|
|
SET finess_id = (SELECT MAX(finess_id) FROM w_sejours_rss WHERE finess_id <> 0)
|
|
WHERE finess_id = 0 AND (SELECT MAX(finess_id) FROM w_sejours_rss WHERE finess_id <> 0) <> 0;
|
|
UPDATE w_sejours_rss
|
|
SET finess_id = (SELECT MAX(oid) FROM base.t_finess)
|
|
WHERE finess_id = 0;
|
|
CREATE INDEX w_sejours_rss_i0 ON w_sejours_rss USING btree (oid);
|
|
CREATE INDEX w_sejours_rss_i1 ON w_sejours_rss USING btree (rss_id);
|
|
CREATE INDEX w_sejours_rss_i2 ON w_sejours_rss USING btree (sejour_id);
|
|
CREATE INDEX w_sejours_rss_i3 ON w_sejours_rss USING btree (no_sejour);
|
|
|
|
-- Traduction des règles en oid
|
|
RAISE NOTICE '%' , 'Application des regles. Traduction en oid';
|
|
DROP TABLE IF EXISTS w_forme_activite_rule;
|
|
CREATE TEMP TABLE w_forme_activite_rule AS
|
|
SELECT
|
|
t_forme_activite_rule.oid,
|
|
forme_activite_id,
|
|
CASE WHEN priorite <> 0 THEN priorite WHEN liste_diagnostic_principal <> '' THEN 90000 ELSE 999999 END AS priorite,
|
|
CASE WHEN liste_finess <> '' THEN '1' ELSE '0' END AS a_liste_finess,
|
|
string_to_array(translate(liste_finess,'*,','% '),' ') AS liste_finess,
|
|
NULL::bigint[] AS liste_finess_id ,
|
|
CASE WHEN liste_diagnostic_principal <> '' THEN '1' ELSE '0' END AS a_liste_diagnostic_principal,
|
|
string_to_array(translate(liste_diagnostic_principal,'*,','% '),' ') AS liste_diagnostic_principal,
|
|
NULL::bigint[] AS liste_diagnostic_principal_id ,
|
|
CASE WHEN liste_diagnostic_relie <> '' THEN '1' ELSE '0' END AS a_liste_diagnostic_relie,
|
|
string_to_array(translate(liste_diagnostic_relie,'*,','% '),' ') AS liste_diagnostic_relie,
|
|
NULL::bigint[] AS liste_diagnostic_relie_id ,
|
|
CASE WHEN liste_diagnostic_associe <> '' THEN '1' ELSE '0' END AS a_liste_diagnostic_associe,
|
|
string_to_array(translate(liste_diagnostic_associe,'*,','% '),' ') AS liste_diagnostic_associe,
|
|
NULL::bigint[] AS liste_diagnostic_associe_id ,
|
|
CASE WHEN liste_acte <> '' THEN '1' ELSE '0' END AS a_liste_acte,
|
|
string_to_array(translate(liste_acte,'*,','% '),' ') AS liste_acte,
|
|
NULL::bigint[] AS liste_acte_pmsi_id ,
|
|
NULL::bigint[] AS liste_acte_base_id ,
|
|
CASE WHEN liste_ghm <> '' THEN '1' ELSE '0' END AS a_liste_ghm,
|
|
string_to_array(translate(liste_ghm,'*,','% '),' ') AS liste_ghm,
|
|
NULL::bigint[] AS liste_ghm_pmsi_id,
|
|
NULL::bigint[] AS liste_ghm_activite_id,
|
|
liste_ghm AS liste_ghm_origine,
|
|
CASE WHEN liste_sauf_ghm <> '' THEN '1' ELSE '0' END AS a_liste_sauf_ghm,
|
|
string_to_array(translate(liste_sauf_ghm,'*,','% '),' ') AS liste_sauf_ghm,
|
|
NULL::bigint[] AS liste_sauf_ghm_pmsi_id ,
|
|
NULL::bigint[] AS liste_sauf_ghm_activite_id ,
|
|
liste_sauf_ghm AS liste_sauf_ghm_origine,
|
|
CASE WHEN liste_type_sejour <> '' THEN '1' ELSE '0' END AS a_liste_type_sejour,
|
|
string_to_array(translate(liste_type_sejour,'*,','% '),' ') AS liste_type_sejour,
|
|
NULL::text[] AS liste_type_sejour_id ,
|
|
CASE WHEN liste_unite_fonctionnelle <> '' THEN '1' ELSE '0' END AS a_liste_unite_fonctionnelle,
|
|
string_to_array(translate(liste_unite_fonctionnelle,'*,','% '),' ') AS liste_unite_fonctionnelle,
|
|
NULL::bigint[] AS liste_unite_fonctionnelle_id ,
|
|
CASE WHEN liste_service <> '' THEN '1' ELSE '0' END AS a_liste_service,
|
|
string_to_array(translate(liste_service,'*,','% '),' ') AS liste_service,
|
|
NULL::bigint[] AS liste_service_id ,
|
|
CASE WHEN liste_etage <> '' THEN '1' ELSE '0' END AS a_liste_etage,
|
|
string_to_array(translate(liste_etage,'*,','% '),' ') AS liste_etage,
|
|
NULL::bigint[] AS liste_etage_id ,
|
|
CASE WHEN liste_unite_fonctionnelle_entree <> '' THEN '1' ELSE '0' END AS a_liste_unite_fonctionnelle_entree,
|
|
string_to_array(translate(liste_unite_fonctionnelle_entree,'*,','% '),' ') AS liste_unite_fonctionnelle_entree,
|
|
NULL::bigint[] AS liste_unite_fonctionnelle_entree_id ,
|
|
CASE WHEN liste_service_entree <> '' THEN '1' ELSE '0' END AS a_liste_service_entree,
|
|
string_to_array(translate(liste_service_entree,'*,','% '),' ') AS liste_service_entree,
|
|
NULL::bigint[] AS liste_service_entree_id ,
|
|
CASE WHEN liste_etage_entree <> '' THEN '1' ELSE '0' END AS a_liste_etage_entree,
|
|
string_to_array(translate(liste_etage_entree,'*,','% '),' ') AS liste_etage_entree,
|
|
NULL::bigint[] AS liste_etage_entree_id ,
|
|
NULL::bigint[] AS liste_activite_id ,
|
|
CASE WHEN liste_medecin <> '' THEN '1' ELSE '0' END AS a_liste_medecin,
|
|
string_to_array(CASE WHEN liste_medecin NOT LIKE '%**%' THEN translate(liste_medecin,'*,','% ') ELSE liste_medecin END,' ') AS liste_medecin,
|
|
NULL::bigint[] AS liste_medecin_pmsi_id ,
|
|
NULL::bigint[] AS liste_medecin_activite_id ,
|
|
CASE WHEN liste_specialite <> '' THEN '1' ELSE '0' END AS a_liste_specialite,
|
|
string_to_array(translate(liste_specialite,'*,','% '),' ') AS liste_specialite,
|
|
NULL::bigint[] AS liste_specialite_pmsi_id ,
|
|
NULL::bigint[] AS liste_specialite_activite_id ,
|
|
CASE WHEN liste_rubrique <> '' THEN '1' ELSE '0' END AS a_liste_rubrique,
|
|
string_to_array(translate(liste_rubrique,'*,','% '),' ') AS liste_rubrique,
|
|
NULL::bigint[] AS liste_rubrique_id,
|
|
CASE WHEN liste_prestation <> '' THEN '1' ELSE '0' END AS a_liste_prestation,
|
|
string_to_array(translate(liste_prestation,'*,','% '),' ') AS liste_prestation,
|
|
NULL::bigint[] AS liste_prestation_id
|
|
FROM activite.t_forme_activite_rule
|
|
JOIN activite.t_forme_activite ON forme_activite_id = t_forme_activite.oid
|
|
ORDER BY priorite, forme_activite_id;
|
|
|
|
|
|
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_finess_id = (SELECT base.cti_group_array3(oid) FROM base.t_finess WHERE code LIKE ANY (liste_finess))
|
|
WHERE a_liste_finess = '1';
|
|
|
|
IF _module_pmsimco = '1' THEN
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_diagnostic_principal_id = (SELECT base.cti_group_array3(oid) FROM pmsi.t_diagnostics WHERE code LIKE ANY (liste_diagnostic_principal))
|
|
WHERE a_liste_diagnostic_principal = '1';
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_diagnostic_relie_id = (SELECT base.cti_group_array3(oid) FROM pmsi.t_diagnostics WHERE code LIKE ANY (liste_diagnostic_relie))
|
|
WHERE a_liste_diagnostic_relie = '1';
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_diagnostic_associe_id = (SELECT base.cti_group_array3(oid) FROM pmsi.t_diagnostics WHERE code LIKE ANY (liste_diagnostic_associe))
|
|
WHERE a_liste_diagnostic_associe = '1';
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_acte_pmsi_id = (SELECT base.cti_group_array3(oid) FROM pmsi.t_actes WHERE code LIKE ANY (liste_acte))
|
|
WHERE a_liste_acte = '1';
|
|
DROP TABLE IF EXISTS w_forme_activite_rule_actes_pmsi;
|
|
CREATE TEMP TABLE w_forme_activite_rule_actes_pmsi AS
|
|
SELECT t_actes.oid AS rule_acte_id, w_forme_activite_rule.oid AS rule_id
|
|
FROM pmsi.t_actes
|
|
JOIN w_forme_activite_rule ON code LIKE ANY (liste_acte);
|
|
CREATE INDEX w_forme_activite_rule_actes_pmsi_i1 ON w_forme_activite_rule_actes_pmsi USING btree (rule_acte_id);
|
|
END IF;
|
|
|
|
|
|
DROP TABLE IF EXISTS w_forme_activite_rule_actes_base;
|
|
CREATE TEMP TABLE w_forme_activite_rule_actes_base AS
|
|
SELECT t_actes.oid AS rule_acte_id, w_forme_activite_rule.oid AS rule_id
|
|
FROM base.t_actes
|
|
JOIN w_forme_activite_rule ON code LIKE ANY (liste_acte);
|
|
CREATE INDEX w_forme_activite_rule_actes_base_i1 ON w_forme_activite_rule_actes_base USING btree (rule_acte_id);
|
|
|
|
DROP TABLE IF EXISTS w_forme_activite_rule_rubriques;
|
|
CREATE TEMP TABLE w_forme_activite_rule_rubriques AS
|
|
SELECT t_rubriques_facturation.oid AS rule_rubrique_id, w_forme_activite_rule.oid AS rule_id
|
|
FROM activite.t_rubriques_facturation
|
|
JOIN w_forme_activite_rule ON code LIKE ANY (liste_rubrique);
|
|
CREATE INDEX w_forme_activite_rule_rubriques_i1 ON w_forme_activite_rule_rubriques USING btree (rule_rubrique_id);
|
|
|
|
DROP TABLE IF EXISTS w_forme_activite_rule_prestations;
|
|
CREATE TEMP TABLE w_forme_activite_rule_prestations AS
|
|
SELECT t_prestations.oid AS rule_prestation_id, w_forme_activite_rule.oid AS rule_id
|
|
FROM activite.t_prestations
|
|
JOIN w_forme_activite_rule ON code LIKE ANY (liste_prestation);
|
|
CREATE INDEX w_forme_activite_rule_prestations_i1 ON w_forme_activite_rule_prestations USING btree (rule_prestation_id);
|
|
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_acte_base_id = (SELECT base.cti_group_array3(oid) FROM base.t_actes WHERE code LIKE ANY (liste_acte))
|
|
WHERE a_liste_acte = '1'
|
|
;
|
|
|
|
IF _module_pmsimco = '1' THEN
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_ghm_pmsi_id = (SELECT base.cti_group_array3(oid) FROM pmsi.t_ghm WHERE code LIKE ANY (liste_ghm))
|
|
WHERE a_liste_ghm = '1';
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_ghm_pmsi_id =
|
|
array_cat(liste_ghm_pmsi_id,
|
|
(SELECT base.cti_group_array3(t_ghm.oid) FROM pmsi.t_ghm JOIN base.t_cas ON cas_id = t_cas.oid WHERE '[CAS:'||t_cas.code||']' LIKE ANY (liste_ghm))
|
|
)
|
|
WHERE a_liste_ghm = '1' AND
|
|
liste_ghm_origine LIKE '%[CAS:%'
|
|
;
|
|
END IF;
|
|
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_ghm_activite_id = (SELECT base.cti_group_array3(oid) FROM base.t_ghm WHERE code LIKE ANY (liste_ghm))
|
|
WHERE a_liste_ghm = '1'
|
|
;
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_ghm_activite_id =
|
|
array_cat(liste_ghm_activite_id,
|
|
(SELECT base.cti_group_array3(t_ghm.oid) FROM base.t_ghm JOIN base.t_cas ON cas_id = t_cas.oid WHERE '[CAS:'||t_cas.code||']' LIKE ANY (liste_ghm))
|
|
)
|
|
WHERE a_liste_ghm = '1' AND
|
|
liste_ghm_origine LIKE '%[CAS:%'
|
|
;
|
|
IF _module_pmsimco = '1' THEN
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_sauf_ghm_pmsi_id = (SELECT base.cti_group_array3(oid) FROM pmsi.t_ghm WHERE code LIKE ANY (liste_sauf_ghm))
|
|
WHERE a_liste_sauf_ghm = '1';
|
|
END IF;
|
|
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_sauf_ghm_activite_id = (SELECT base.cti_group_array3(oid) FROM base.t_ghm WHERE code LIKE ANY (liste_sauf_ghm))
|
|
WHERE a_liste_sauf_ghm = '1'
|
|
;
|
|
IF _module_pmsimco = '1' THEN
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_medecin_pmsi_id = (
|
|
SELECT base.cti_group_array3(t_medecins_pmsi.oid)
|
|
FROM pmsi.t_medecins t_medecins_pmsi
|
|
JOIN base.t_medecins ON t_medecins_pmsi.medecin_reference_id = t_medecins.oid
|
|
WHERE t_medecins_pmsi.oid <> 0 AND t_medecins_pmsi.code <> '' AND t_medecins_pmsi.code LIKE ANY (liste_medecin) AND liste_medecin[1] <> '(R)' OR
|
|
t_medecins.oid <> 0 AND t_medecins.code <> '' AND t_medecins.code LIKE ANY (liste_medecin) AND liste_medecin[1] = '(R)'
|
|
)
|
|
WHERE a_liste_medecin = '1';
|
|
END IF;
|
|
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_medecin_activite_id = (
|
|
SELECT base.cti_group_array3(t_medecins_administratifs.oid)
|
|
FROM activite.t_medecins_administratifs
|
|
JOIN base.t_medecins ON t_medecins_administratifs.medecin_id = t_medecins.oid
|
|
WHERE t_medecins_administratifs.oid <> 0 AND t_medecins_administratifs.code <> '' AND t_medecins_administratifs.code LIKE ANY (liste_medecin) AND liste_medecin[1] <> '(R)' OR
|
|
t_medecins.oid <> 0 AND t_medecins.code <> '' AND t_medecins.code LIKE ANY (liste_medecin) AND liste_medecin[1] = '(R)'
|
|
)
|
|
WHERE a_liste_medecin = '1'
|
|
;
|
|
IF _module_pmsimco = '1' THEN
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_specialite_pmsi_id = (SELECT base.cti_group_array3(oid) FROM pmsi.t_specialites_medecin WHERE code LIKE ANY (liste_specialite))
|
|
WHERE a_liste_specialite = '1';
|
|
END IF;
|
|
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_specialite_activite_id = (SELECT base.cti_group_array3(oid) FROM base.t_specialites_medecin WHERE code LIKE ANY (liste_specialite))
|
|
WHERE a_liste_specialite = '1'
|
|
;
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_type_sejour_id = (SELECT base.cti_group_array3(code) FROM activite.t_type_sejour WHERE code <> '' AND code LIKE ANY (liste_type_sejour))
|
|
WHERE a_liste_type_sejour = '1'
|
|
;
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_unite_fonctionnelle_id = (SELECT base.cti_group_array3(oid) FROM activite.t_unites_fonctionnelles WHERE code LIKE ANY (liste_unite_fonctionnelle))
|
|
WHERE a_liste_unite_fonctionnelle = '1'
|
|
;
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_service_id = (SELECT base.cti_group_array3(oid) FROM activite.t_services_facturation WHERE code LIKE ANY (liste_service))
|
|
WHERE a_liste_service = '1'
|
|
;
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_etage_id = (SELECT base.cti_group_array3(oid) FROM activite.t_etages WHERE code LIKE ANY (liste_etage))
|
|
WHERE a_liste_etage = '1'
|
|
;
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_unite_fonctionnelle_entree_id = (SELECT base.cti_group_array3(oid) FROM activite.t_unites_fonctionnelles WHERE code LIKE ANY (liste_unite_fonctionnelle_entree))
|
|
WHERE a_liste_unite_fonctionnelle_entree = '1'
|
|
;
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_service_entree_id = (SELECT base.cti_group_array3(oid) FROM activite.t_services_facturation WHERE code LIKE ANY (liste_service_entree))
|
|
WHERE a_liste_service_entree = '1'
|
|
;
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_etage_entree_id = (SELECT base.cti_group_array3(oid) FROM activite.t_etages WHERE code LIKE ANY (liste_etage_entree))
|
|
WHERE a_liste_etage_entree = '1'
|
|
;
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_rubrique_id = (SELECT base.cti_group_array3(oid) FROM activite.t_rubriques_facturation WHERE code LIKE ANY (liste_rubrique))
|
|
WHERE a_liste_rubrique = '1'
|
|
;
|
|
UPDATE w_forme_activite_rule
|
|
SET liste_prestation_id = (SELECT base.cti_group_array3(oid) FROM activite.t_prestations WHERE code LIKE ANY (liste_prestation))
|
|
WHERE a_liste_prestation = '1'
|
|
;
|
|
|
|
|
|
-- Association cles aux rubriques
|
|
RAISE NOTICE '%' , 'Application des regles. Traitement des rubriques';
|
|
DROP TABLE IF EXISTS w_factures_rubriques;
|
|
CREATE TEMP TABLE w_factures_rubriques AS
|
|
SELECT
|
|
p_factures.no_sejour, rule_id
|
|
FROM activite.p_factures_lignes_c
|
|
JOIN activite.p_factures ON p_factures.no_facture = p_factures_lignes_c.no_facture
|
|
JOIN w_forme_activite_rule_rubriques ON p_factures_lignes_c.rubrique_facturation_id = rule_rubrique_id
|
|
WHERE p_factures_lignes_c.montant_facture <> 0
|
|
GROUP BY 1,2
|
|
HAVING SUM(p_factures_lignes_c.montant_facture) > 0
|
|
UNION
|
|
SELECT
|
|
p_factures.no_sejour, rule_id
|
|
FROM activite.p_factures_lignes_non_facturees_c
|
|
JOIN activite.p_factures ON p_factures.no_facture = p_factures_lignes_non_facturees_c.no_facture
|
|
JOIN w_forme_activite_rule_rubriques ON p_factures_lignes_non_facturees_c.rubrique_facturation_id = rule_rubrique_id
|
|
;
|
|
CREATE INDEX w_factures_rubriques_i1 ON w_factures_rubriques USING btree (no_sejour);
|
|
|
|
DROP TABLE IF EXISTS w_forme_activite_rubriques;
|
|
CREATE TEMP TABLE w_forme_activite_rubriques AS
|
|
|
|
SELECT oid, base.cti_group_array3(rubrique_rule_id) AS rubrique_liste_rule_id
|
|
FROM
|
|
(
|
|
SELECT
|
|
w_sejours_rss.oid, rule_id AS rubrique_rule_id
|
|
FROM w_factures_rubriques
|
|
JOIN w_sejours_rss ON w_factures_rubriques.no_sejour = w_sejours_rss.no_sejour
|
|
) subview
|
|
GROUP BY 1;
|
|
ALTER TABLE w_forme_activite_rubriques ADD CONSTRAINT w_forme_activite_rubriques_pkey PRIMARY KEY(oid);
|
|
UPDATE w_sejours_rss
|
|
SET rubrique_liste_rule_id = w_forme_activite_rubriques.rubrique_liste_rule_id
|
|
FROM w_forme_activite_rubriques
|
|
WHERE w_forme_activite_rubriques.oid = w_sejours_rss.oid;
|
|
|
|
|
|
|
|
-- Association cles aux prestations
|
|
RAISE NOTICE '%' , 'Application des regles. Traitement des prestations';
|
|
DROP TABLE IF EXISTS w_factures_prestations;
|
|
CREATE TEMP TABLE w_factures_prestations AS
|
|
SELECT
|
|
p_factures.no_sejour, rule_id
|
|
FROM activite.p_factures_lignes_c
|
|
JOIN activite.p_factures ON p_factures.no_facture = p_factures_lignes_c.no_facture
|
|
JOIN w_forme_activite_rule_prestations ON p_factures_lignes_c.prestation_id = rule_prestation_id
|
|
WHERE p_factures_lignes_c.montant_facture <> 0
|
|
GROUP BY 1,2
|
|
HAVING SUM(p_factures_lignes_c.montant_facture) > 0
|
|
UNION
|
|
SELECT
|
|
p_factures.no_sejour, rule_id
|
|
FROM activite.p_factures_lignes_non_facturees_c
|
|
JOIN activite.p_factures ON p_factures.no_facture = p_factures_lignes_non_facturees_c.no_facture
|
|
JOIN w_forme_activite_rule_prestations ON p_factures_lignes_non_facturees_c.prestation_id = rule_prestation_id
|
|
GROUP BY 1,2
|
|
;
|
|
CREATE INDEX w_factures_prestations_i1 ON w_factures_prestations USING btree (no_sejour);
|
|
|
|
DROP TABLE IF EXISTS w_forme_activite_prestations;
|
|
CREATE TEMP TABLE w_forme_activite_prestations AS
|
|
|
|
SELECT oid, base.cti_group_array3(prestation_rule_id) AS prestation_liste_rule_id
|
|
FROM
|
|
(
|
|
SELECT
|
|
w_sejours_rss.oid, rule_id AS prestation_rule_id
|
|
FROM w_factures_prestations
|
|
JOIN w_sejours_rss ON w_factures_prestations.no_sejour = w_sejours_rss.no_sejour
|
|
) subview
|
|
GROUP BY 1;
|
|
ALTER TABLE w_forme_activite_prestations ADD CONSTRAINT w_forme_activite_prestations_pkey PRIMARY KEY(oid);
|
|
UPDATE w_sejours_rss
|
|
SET prestation_liste_rule_id = w_forme_activite_prestations.prestation_liste_rule_id
|
|
FROM w_forme_activite_prestations
|
|
WHERE w_forme_activite_prestations.oid = w_sejours_rss.oid;
|
|
|
|
|
|
-- Association cles aux actes
|
|
RAISE NOTICE '%' , 'Application des regles. Traitement des actes';
|
|
DROP TABLE IF EXISTS w_factures_actes;
|
|
CREATE TEMP TABLE w_factures_actes AS
|
|
SELECT
|
|
no_facture, rule_id
|
|
FROM activite.p_factures_lignes_h
|
|
JOIN w_forme_activite_rule_actes_base ON p_factures_lignes_h.acte_id = rule_acte_id
|
|
WHERE activite_ccam <> '4'
|
|
UNION
|
|
SELECT
|
|
no_facture, rule_id
|
|
FROM activite.p_factures_lignes_non_facturees_h
|
|
JOIN w_forme_activite_rule_actes_base ON p_factures_lignes_non_facturees_h.acte_id = rule_acte_id
|
|
WHERE activite_ccam <> '4';
|
|
CREATE INDEX w_factures_actes_i1 ON w_factures_actes USING btree (no_facture);
|
|
|
|
DROP TABLE IF EXISTS w_forme_activite_actes;
|
|
IF _module_pmsimco = '1' THEN
|
|
CREATE TEMP TABLE w_forme_activite_actes AS
|
|
SELECT oid, base.cti_group_array3(acte_rule_id) AS acte_liste_rule_id
|
|
FROM
|
|
(
|
|
SELECT
|
|
w_sejours_rss.oid, rule_id AS acte_rule_id
|
|
FROM pmsi.p_rss_actes
|
|
JOIN w_sejours_rss ON p_rss_actes.rss_id = w_sejours_rss.rss_id
|
|
JOIN w_forme_activite_rule_actes_pmsi ON p_rss_actes.acte_id = rule_acte_id
|
|
UNION
|
|
SELECT
|
|
w_sejours_rss.oid, rule_id AS acte_rule_id
|
|
FROM w_factures_actes
|
|
JOIN activite.p_factures ON w_factures_actes.no_facture = p_factures.no_facture
|
|
JOIN w_sejours_rss ON p_factures.no_sejour = w_sejours_rss.no_sejour
|
|
|
|
) subview
|
|
GROUP BY 1;
|
|
ELSE
|
|
CREATE TEMP TABLE w_forme_activite_actes AS
|
|
SELECT oid, base.cti_group_array3(acte_rule_id) AS acte_liste_rule_id
|
|
FROM
|
|
(
|
|
SELECT
|
|
w_sejours_rss.oid, rule_id AS acte_rule_id
|
|
FROM w_factures_actes
|
|
JOIN activite.p_factures ON w_factures_actes.no_facture = p_factures.no_facture
|
|
JOIN w_sejours_rss ON p_factures.no_sejour = w_sejours_rss.no_sejour
|
|
|
|
) subview
|
|
GROUP BY 1;
|
|
END IF;
|
|
ALTER TABLE w_forme_activite_actes ADD CONSTRAINT w_forme_activite_actes_pkey PRIMARY KEY(oid);
|
|
UPDATE w_sejours_rss
|
|
SET acte_liste_rule_id = w_forme_activite_actes.acte_liste_rule_id
|
|
FROM w_forme_activite_actes
|
|
WHERE w_forme_activite_actes.oid = w_sejours_rss.oid;
|
|
|
|
-- Diagnostics
|
|
IF _module_pmsimco = '1' THEN
|
|
RAISE NOTICE '%' , 'Application des regles. Traitement des diagnostics';
|
|
DROP TABLE IF EXISTS w_forme_activite_diagnostics;
|
|
CREATE TEMP TABLE w_forme_activite_diagnostics AS
|
|
SELECT
|
|
w_sejours_rss.oid, base.cti_group_array3(w_forme_activite_rule.oid) AS diagnostic_associe_liste_rule_id
|
|
FROM pmsi.p_rss_diagnostics
|
|
JOIN w_forme_activite_rule ON type_diagnostic_rss IN ('DA', 'DS') AND diagnostic_id = ANY (liste_diagnostic_associe_id)
|
|
JOIN w_sejours_rss ON p_rss_diagnostics.rss_id = w_sejours_rss.rss_id
|
|
WHERE a_liste_diagnostic_associe <> '0'
|
|
GROUP BY 1;
|
|
ALTER TABLE w_forme_activite_diagnostics ADD CONSTRAINT w_forme_activite_diagnostics_pkey PRIMARY KEY(oid);
|
|
UPDATE w_sejours_rss
|
|
SET diagnostic_associe_liste_rule_id = w_forme_activite_diagnostics.diagnostic_associe_liste_rule_id
|
|
FROM w_forme_activite_diagnostics
|
|
WHERE w_forme_activite_diagnostics.oid = w_sejours_rss.oid;
|
|
END IF;
|
|
|
|
-- Mise à jour des cles venant des données du PMSI
|
|
RAISE NOTICE '%' , 'Application des regles. Attribution des formes activite venant du pmsi';
|
|
DROP TABLE IF EXISTS w_sejours_forme_activite;
|
|
CREATE TEMP TABLE w_sejours_forme_activite AS
|
|
SELECT w_sejours_rss.oid,
|
|
MIN(priorite)+1 AS forme_activite_priorite,
|
|
(MIN(ARRAY[priorite,w_forme_activite_rule.forme_activite_id]))[2] AS forme_activite_id
|
|
FROM w_sejours_rss
|
|
JOIN w_forme_activite_rule ON
|
|
(a_liste_finess = '0' OR finess_id = ANY (liste_finess_id)) AND
|
|
(a_liste_medecin = '0' OR medecin_rss_id = ANY (liste_medecin_pmsi_id)) AND
|
|
(a_liste_type_sejour = '0') AND
|
|
(a_liste_unite_fonctionnelle = '0') AND
|
|
(a_liste_service = '0') AND
|
|
(a_liste_etage = '0') AND
|
|
(a_liste_unite_fonctionnelle_entree = '0') AND
|
|
(a_liste_service_entree = '0') AND
|
|
(a_liste_etage_entree = '0') AND
|
|
(a_liste_specialite = '0' OR specialite_rss_id = ANY (liste_specialite_pmsi_id)) AND
|
|
(a_liste_diagnostic_principal = '0' OR diagnostic_principal_id = ANY (liste_diagnostic_principal_id)) AND
|
|
(a_liste_diagnostic_relie = '0' OR diagnostic_relie_id = ANY (liste_diagnostic_relie_id)) AND
|
|
(a_liste_diagnostic_associe = '0' OR w_forme_activite_rule.oid = ANY(diagnostic_associe_liste_rule_id)) AND
|
|
(a_liste_acte = '0' OR w_forme_activite_rule.oid = ANY(acte_liste_rule_id)) AND
|
|
(a_liste_ghm = '0' OR ghm_pmsi_id = ANY (liste_ghm_pmsi_id)) AND
|
|
(a_liste_sauf_ghm = '0' OR ghm_pmsi_id <> ALL (liste_sauf_ghm_pmsi_id)) AND
|
|
(a_liste_prestation = '0') AND
|
|
(a_liste_rubrique = '0')
|
|
WHERE w_sejours_rss.rss_id <> 0
|
|
GROUP BY 1
|
|
ORDER BY 1;
|
|
UPDATE w_sejours_rss SET
|
|
forme_activite_id = w_sejours_forme_activite.forme_activite_id,
|
|
forme_activite_priorite = w_sejours_forme_activite.forme_activite_priorite
|
|
FROM w_sejours_forme_activite
|
|
WHERE w_sejours_rss.oid = w_sejours_forme_activite.oid;
|
|
-- Mise à jour des cles venant des données d'activite
|
|
RAISE NOTICE '%' , 'Application des regles. Attribution des formes activite venant de la GAP';
|
|
DROP TABLE IF EXISTS w_sejours_forme_activite;
|
|
CREATE TEMP TABLE w_sejours_forme_activite AS
|
|
SELECT w_sejours_rss.oid,
|
|
MIN(priorite) AS forme_activite_priorite,
|
|
(MIN(ARRAY[priorite,w_forme_activite_rule.forme_activite_id]))[2] AS forme_activite_id
|
|
FROM w_sejours_rss
|
|
JOIN w_forme_activite_rule ON
|
|
(a_liste_finess = '0' OR finess_id = ANY (liste_finess_id)) AND
|
|
(a_liste_type_sejour = '0' OR type_sejour = ANY (liste_type_sejour_id)) AND
|
|
(a_liste_unite_fonctionnelle = '0' OR unite_fonctionnelle_id = ANY (liste_unite_fonctionnelle_id)) AND
|
|
(a_liste_service = '0' OR service_facturation_id = ANY (liste_service_id)) AND
|
|
(a_liste_etage = '0' OR etage_id = ANY (liste_etage_id)) AND
|
|
(a_liste_unite_fonctionnelle_entree = '0' OR unite_fonctionnelle_entree_id = ANY (liste_unite_fonctionnelle_entree_id)) AND
|
|
(a_liste_service_entree = '0' OR service_facturation_entree_id = ANY (liste_service_entree_id)) AND
|
|
(a_liste_etage_entree = '0' OR etage_entree_id = ANY (liste_etage_entree_id)) AND
|
|
(a_liste_medecin = '0' OR medecin_sejour_id = ANY (liste_medecin_activite_id)) AND
|
|
(a_liste_specialite = '0' OR specialite_sejour_id = ANY (liste_specialite_activite_id)) AND
|
|
(a_liste_diagnostic_principal = '0' OR diagnostic_principal_id = ANY (liste_diagnostic_principal_id)) AND
|
|
(a_liste_diagnostic_relie = '0' OR diagnostic_relie_id = ANY (liste_diagnostic_relie_id)) AND
|
|
(a_liste_diagnostic_associe = '0' OR w_forme_activite_rule.oid = ANY(diagnostic_associe_liste_rule_id)) AND
|
|
(a_liste_acte = '0' OR w_forme_activite_rule.oid = ANY(acte_liste_rule_id)) AND
|
|
(a_liste_rubrique = '0' OR w_forme_activite_rule.oid = ANY(rubrique_liste_rule_id)) AND
|
|
(a_liste_prestation = '0' OR w_forme_activite_rule.oid = ANY(prestation_liste_rule_id)) AND
|
|
(a_liste_ghm = '0' OR ghm_activite_id = ANY (liste_ghm_activite_id)) AND
|
|
(a_liste_sauf_ghm = '0' OR ghm_activite_id <> ALL (liste_sauf_ghm_activite_id))
|
|
WHERE w_sejours_rss.sejour_id <> 0
|
|
GROUP BY 1
|
|
ORDER BY 1;
|
|
UPDATE w_sejours_rss SET
|
|
forme_activite_id = w_sejours_forme_activite.forme_activite_id,
|
|
forme_activite_priorite = w_sejours_forme_activite.forme_activite_priorite
|
|
FROM w_sejours_forme_activite
|
|
WHERE w_sejours_rss.oid = w_sejours_forme_activite.oid AND
|
|
(w_sejours_rss.forme_activite_priorite > w_sejours_forme_activite.forme_activite_priorite OR w_sejours_rss.forme_activite_priorite = 0);
|
|
|
|
|
|
|
|
RAISE NOTICE '%' , 'Application des regles. Deductions depuis utilisation';
|
|
|
|
-- Valeurs par defaut des médecins
|
|
DROP TABLE IF EXISTS w_dft;
|
|
CREATE TEMP TABLE w_dft AS
|
|
SELECT medecin_sejour_id, type_sejour,
|
|
(MAX(ARRAY[to_char(nb,'FM000000000000'),forme_activite_id::text]))[2]::bigint AS forme_activite_id,
|
|
MAX(forme_activite_div_id) AS forme_activite_div_id,
|
|
(MAX(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) / SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END)) AS pct
|
|
FROM
|
|
(
|
|
SELECT medecin_sejour_id, type_sejour, forme_activite_id, count(*) AS nb,
|
|
MAX(CASE WHEN t_forme_activite.code LIKE '%9' THEN '1' ELSE '0' END) AS is_div,
|
|
MAX(CASE WHEN t_forme_activite.code LIKE '%9' THEN forme_activite_id ELSE 0 END) AS forme_activite_div_id
|
|
FROM w_sejours_rss
|
|
JOIN activite.t_forme_activite ON forme_activite_id = t_forme_activite.oid
|
|
WHERE type_sejour <> '9' AND medecin_sejour_id <> 0 AND sejour_id <> 0
|
|
GROUP BY 1,2,3
|
|
) subview
|
|
GROUP BY 1,2
|
|
HAVING MAX(is_div) = '1' AND
|
|
MIN(is_div) = '0' AND
|
|
(MAX(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) / SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END)) > 0.95 AND
|
|
SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) > 50 AND
|
|
(MAX(ARRAY[to_char(nb,'FM000000000000'),forme_activite_id::text]))[2]::bigint <> MAX(forme_activite_div_id)
|
|
;
|
|
|
|
DELETE FROM w_dft
|
|
USING
|
|
activite.t_forme_activite,
|
|
activite.t_forme_activite t_forme_activite_div
|
|
WHERE forme_activite_id = t_forme_activite.oid AND
|
|
forme_activite_div_id = t_forme_activite_div.oid AND
|
|
substr(t_forme_activite.code,1,2) <> substr(t_forme_activite_div.code,1,2)
|
|
;
|
|
|
|
UPDATE w_sejours_rss
|
|
SET forme_activite_id = w_dft.forme_activite_id
|
|
FROM w_dft
|
|
WHERE w_sejours_rss.medecin_sejour_id = w_dft.medecin_sejour_id AND
|
|
w_sejours_rss.type_sejour = w_dft.type_sejour AND
|
|
w_sejours_rss.forme_activite_id = w_dft.forme_activite_div_id
|
|
;
|
|
|
|
-- Defaut selon medecin et service de sortie
|
|
DROP TABLE IF EXISTS w_dft;
|
|
CREATE TEMP TABLE w_dft AS
|
|
SELECT service_facturation_id, medecin_sejour_id, type_sejour,
|
|
(MAX(ARRAY[to_char(nb,'FM000000000000'),forme_activite_id::text]))[2]::bigint AS forme_activite_id,
|
|
MAX(forme_activite_div_id) AS forme_activite_div_id,
|
|
(MAX(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) / SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END)) AS pct
|
|
FROM
|
|
(
|
|
SELECT service_facturation_id,medecin_sejour_id, type_sejour, forme_activite_id, count(*) AS nb,
|
|
MAX(CASE WHEN t_forme_activite.code LIKE '%9' THEN '1' ELSE '0' END) AS is_div,
|
|
MAX(CASE WHEN t_forme_activite.code LIKE '%9' THEN forme_activite_id ELSE 0 END) AS forme_activite_div_id
|
|
FROM w_sejours_rss
|
|
JOIN activite.t_forme_activite ON forme_activite_id = t_forme_activite.oid
|
|
WHERE type_sejour <> '9' AND medecin_sejour_id <> 0 AND service_facturation_id <> 0 AND sejour_id <> 0
|
|
GROUP BY 1,2,3,4
|
|
) subview
|
|
GROUP BY 1,2,3
|
|
HAVING MAX(is_div) = '1' AND
|
|
MIN(is_div) = '0' AND
|
|
(MAX(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) / SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END)) > 0.90 AND
|
|
SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) > 50 AND
|
|
(MAX(ARRAY[to_char(nb,'FM000000000000'),forme_activite_id::text]))[2]::bigint <> MAX(forme_activite_div_id)
|
|
;
|
|
|
|
DELETE FROM w_dft
|
|
USING
|
|
activite.t_forme_activite,
|
|
activite.t_forme_activite t_forme_activite_div
|
|
WHERE forme_activite_id = t_forme_activite.oid AND
|
|
forme_activite_div_id = t_forme_activite_div.oid AND
|
|
substr(t_forme_activite.code,1,2) <> substr(t_forme_activite_div.code,1,2)
|
|
;
|
|
|
|
UPDATE w_sejours_rss
|
|
SET forme_activite_id = w_dft.forme_activite_id
|
|
FROM w_dft
|
|
WHERE w_sejours_rss.medecin_sejour_id = w_dft.medecin_sejour_id AND
|
|
w_sejours_rss.type_sejour = w_dft.type_sejour AND
|
|
w_sejours_rss.service_facturation_id = w_dft.service_facturation_id AND
|
|
w_sejours_rss.forme_activite_id = w_dft.forme_activite_div_id
|
|
;
|
|
|
|
-- Defaut selon service sortie
|
|
DROP TABLE IF EXISTS w_dft;
|
|
CREATE TEMP TABLE w_dft AS
|
|
SELECT service_facturation_id, type_sejour,
|
|
(MAX(ARRAY[to_char(nb,'FM000000000000'),forme_activite_id::text]))[2]::bigint AS forme_activite_id,
|
|
MAX(forme_activite_div_id) AS forme_activite_div_id,
|
|
(MAX(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) / SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END)) AS pct
|
|
FROM
|
|
(
|
|
SELECT service_facturation_id,type_sejour, forme_activite_id, count(*) AS nb,
|
|
MAX(CASE WHEN t_forme_activite.code LIKE '%9' THEN '1' ELSE '0' END) AS is_div,
|
|
MAX(CASE WHEN t_forme_activite.code LIKE '%9' THEN forme_activite_id ELSE 0 END) AS forme_activite_div_id
|
|
FROM w_sejours_rss
|
|
JOIN activite.t_forme_activite ON forme_activite_id = t_forme_activite.oid
|
|
WHERE type_sejour <> '9' AND service_facturation_id <> 0 AND sejour_id <> 0
|
|
GROUP BY 1,2,3
|
|
) subview
|
|
GROUP BY 1,2
|
|
HAVING MAX(is_div) = '1' AND
|
|
MIN(is_div) = '0' AND
|
|
(MAX(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) / SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END)) > 0.95 AND
|
|
SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) > 50 AND
|
|
(MAX(ARRAY[to_char(nb,'FM000000000000'),forme_activite_id::text]))[2]::bigint <> MAX(forme_activite_div_id)
|
|
;
|
|
|
|
DELETE FROM w_dft
|
|
USING
|
|
activite.t_forme_activite,
|
|
activite.t_forme_activite t_forme_activite_div
|
|
WHERE forme_activite_id = t_forme_activite.oid AND
|
|
forme_activite_div_id = t_forme_activite_div.oid AND
|
|
substr(t_forme_activite.code,1,2) <> substr(t_forme_activite_div.code,1,2)
|
|
;
|
|
|
|
UPDATE w_sejours_rss
|
|
SET forme_activite_id = w_dft.forme_activite_id
|
|
FROM w_dft
|
|
WHERE w_sejours_rss.type_sejour = w_dft.type_sejour AND
|
|
w_sejours_rss.service_facturation_id = w_dft.service_facturation_id AND
|
|
w_sejours_rss.forme_activite_id = w_dft.forme_activite_div_id
|
|
;
|
|
|
|
-- Defaut selon medecin et service d'entree
|
|
DROP TABLE IF EXISTS w_dft;
|
|
CREATE TEMP TABLE w_dft AS
|
|
SELECT service_facturation_entree_id, medecin_sejour_id, type_sejour,
|
|
(MAX(ARRAY[to_char(nb,'FM000000000000'),forme_activite_id::text]))[2]::bigint AS forme_activite_id,
|
|
MAX(forme_activite_div_id) AS forme_activite_div_id,
|
|
(MAX(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) / SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END)) AS pct
|
|
FROM
|
|
(
|
|
SELECT service_facturation_entree_id,medecin_sejour_id, type_sejour, forme_activite_id, count(*) AS nb,
|
|
MAX(CASE WHEN t_forme_activite.code LIKE '%9' THEN '1' ELSE '0' END) AS is_div,
|
|
MAX(CASE WHEN t_forme_activite.code LIKE '%9' THEN forme_activite_id ELSE 0 END) AS forme_activite_div_id
|
|
FROM w_sejours_rss
|
|
JOIN activite.t_forme_activite ON forme_activite_id = t_forme_activite.oid
|
|
WHERE type_sejour <> '9' AND medecin_sejour_id <> 0 AND service_facturation_entree_id <> 0 AND sejour_id <> 0
|
|
GROUP BY 1,2,3,4
|
|
) subview
|
|
GROUP BY 1,2,3
|
|
HAVING MAX(is_div) = '1' AND
|
|
MIN(is_div) = '0' AND
|
|
(MAX(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) / SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END)) > 0.90 AND
|
|
SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) > 50 AND
|
|
(MAX(ARRAY[to_char(nb,'FM000000000000'),forme_activite_id::text]))[2]::bigint <> MAX(forme_activite_div_id)
|
|
;
|
|
|
|
DELETE FROM w_dft
|
|
USING
|
|
activite.t_forme_activite,
|
|
activite.t_forme_activite t_forme_activite_div
|
|
WHERE forme_activite_id = t_forme_activite.oid AND
|
|
forme_activite_div_id = t_forme_activite_div.oid AND
|
|
substr(t_forme_activite.code,1,2) <> substr(t_forme_activite_div.code,1,2)
|
|
;
|
|
|
|
UPDATE w_sejours_rss
|
|
SET forme_activite_id = w_dft.forme_activite_id
|
|
FROM w_dft
|
|
WHERE w_sejours_rss.medecin_sejour_id = w_dft.medecin_sejour_id AND
|
|
w_sejours_rss.type_sejour = w_dft.type_sejour AND
|
|
w_sejours_rss.service_facturation_entree_id = w_dft.service_facturation_entree_id AND
|
|
w_sejours_rss.forme_activite_id = w_dft.forme_activite_div_id
|
|
;
|
|
|
|
|
|
-- Defaut selon service entree
|
|
DROP TABLE IF EXISTS w_dft;
|
|
CREATE TEMP TABLE w_dft AS
|
|
SELECT service_facturation_entree_id, type_sejour,
|
|
(MAX(ARRAY[to_char(nb,'FM000000000000'),forme_activite_id::text]))[2]::bigint AS forme_activite_id,
|
|
MAX(forme_activite_div_id) AS forme_activite_div_id,
|
|
(MAX(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) / SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END)) AS pct
|
|
FROM
|
|
(
|
|
SELECT service_facturation_entree_id,type_sejour, forme_activite_id, count(*) AS nb,
|
|
MAX(CASE WHEN t_forme_activite.code LIKE '%9' THEN '1' ELSE '0' END) AS is_div,
|
|
MAX(CASE WHEN t_forme_activite.code LIKE '%9' THEN forme_activite_id ELSE 0 END) AS forme_activite_div_id
|
|
FROM w_sejours_rss
|
|
JOIN activite.t_forme_activite ON forme_activite_id = t_forme_activite.oid
|
|
WHERE type_sejour <> '9' AND service_facturation_entree_id <> 0 AND sejour_id <> 0
|
|
GROUP BY 1,2,3
|
|
) subview
|
|
GROUP BY 1,2
|
|
HAVING MAX(is_div) = '1' AND
|
|
MIN(is_div) = '0' AND
|
|
(MAX(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) / SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END)) > 0.95 AND
|
|
SUM(CASE WHEN is_div <> '1' THEN nb ELSE 0 END) > 50 AND
|
|
(MAX(ARRAY[to_char(nb,'FM000000000000'),forme_activite_id::text]))[2]::bigint <> MAX(forme_activite_div_id)
|
|
;
|
|
|
|
DELETE FROM w_dft
|
|
USING
|
|
activite.t_forme_activite,
|
|
activite.t_forme_activite t_forme_activite_div
|
|
WHERE forme_activite_id = t_forme_activite.oid AND
|
|
forme_activite_div_id = t_forme_activite_div.oid AND
|
|
substr(t_forme_activite.code,1,2) <> substr(t_forme_activite_div.code,1,2)
|
|
;
|
|
|
|
UPDATE w_sejours_rss
|
|
SET forme_activite_id = w_dft.forme_activite_id
|
|
FROM w_dft
|
|
WHERE w_sejours_rss.type_sejour = w_dft.type_sejour AND
|
|
w_sejours_rss.service_facturation_entree_id = w_dft.service_facturation_entree_id AND
|
|
w_sejours_rss.forme_activite_id = w_dft.forme_activite_div_id
|
|
;
|
|
|
|
|
|
|
|
|
|
RAISE NOTICE '%' , 'Application des regles. Validation des forme activite';
|
|
UPDATE activite.p_sejours
|
|
SET
|
|
forme_activite_id = w_sejours_rss.forme_activite_id
|
|
FROM w_sejours_rss
|
|
WHERE p_sejours.oid = w_sejours_rss.sejour_id AND
|
|
p_sejours.oid <> 0 AND
|
|
(
|
|
p_sejours.forme_activite_id IS DISTINCT FROM w_sejours_rss.forme_activite_id
|
|
);
|
|
|
|
ANALYSE activite.p_sejours
|
|
;
|
|
|
|
|
|
INSERT INTO activite.p_oids (code_table, oid)
|
|
SELECT 'forme_activite', forme_activite_id
|
|
FROM activite.p_sejours
|
|
WHERE forme_activite_id NOT IN (SELECT oid FROM activite.p_oids WHERE code_table = 'forme_activite')
|
|
GROUP BY 2;
|
|
|
|
REINDEX INDEX activite.i_sejours_forme_activite_id;
|
|
RETURN 'OK';
|
|
END;
|