code|name|oid|select_cmd|texte
ACTE|t_actes|1|"(SELECT t_actes.oid, t_actes.code, t_actes.texte, t_ccam_regroupements.code || CASE WHEN t_actes.classant = 'Y' THEN ' (classant)' ELSE '' END  as complement, 'O' as utilise 
FROM pmsi.t_actes, pmsi.t_ccam_regroupements
WHERE t_actes.oid > 0 AND nomenclature IN ('CCAM', 'CCAMDESC') AND ccam_regroupement_id_1 = t_ccam_regroupements.oid AND t_actes.oid in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'actes')  ) 
UNION 
(SELECT t_actes.oid, t_actes.code, t_actes.texte, t_ccam_regroupements.code || CASE WHEN t_actes.classant = 'Y' THEN ' (classant)' ELSE '' END as complement, 'N' as utilise 
FROM pmsi.t_actes, pmsi.t_ccam_regroupements
WHERE t_actes.oid > 0 AND nomenclature IN ('CCAM', 'CCAMDESC') AND ccam_regroupement_id_1 = t_ccam_regroupements.oid AND t_actes.oid not in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'actes')  ) 
ORDER BY code"|Actes
AGE|t_ages|1|SELECT oid, code, texte, ''::text as complement, 'O'::text as utilise FROM base.t_ages ORDER BY code|Ages
CODEPOST|t_codes_postaux|1|"(
SELECT t_codes_postaux.oid, t_codes_postaux.code, t_codes_postaux.texte, '' as complement, 'O' as utilise 
FROM base.t_codes_postaux
WHERE 
	t_codes_postaux.oid > 0 
	AND t_codes_postaux.oid in (SELECT oid FROM pmsi.p_oids WHERE code_table='c_postaux') 
UNION 
SELECT t_codes_postaux.oid, t_codes_postaux.code, t_codes_postaux.texte, '' as complement, 'N' as utilise 
FROM base.t_codes_postaux
WHERE 
	t_codes_postaux.oid > 0 
	AND t_codes_postaux.oid not in (SELECT oid FROM pmsi.p_oids WHERE code_table='c_postaux')
) 
ORDER BY code"|Codes postaux
COMED|t_comites_medicaux|1|SELECT oid, code, texte, ''::text as complement, 'O'::text as utilise FROM pmsi.t_comites_medicaux ORDER BY code|Comités médicaux
DIAG|t_diagnostics|1|(SELECT oid, code, texte, CASE WHEN liste_cma = '1' THEN 'CMA' ELSE '' END as complement, 'O' as utilise FROM pmsi.t_diagnostics WHERE oid > 0 AND oid in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'diagnostics') UNION SELECT oid, code, texte, CASE WHEN liste_cma = '1' THEN 'CMA' ELSE '' END as complement , 'N' as utilise FROM pmsi.t_diagnostics WHERE oid > 0 AND oid not in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'diagnostics')) ORDER BY code|Diagnostics
DURSEJ|t_durees_sejour|1|SELECT oid, code, texte, ''::text as complement, 'O'::text as utilise FROM base.t_durees_sejour ORDER BY code|Durées de séjour
EXPERTCTL|t_expert_controles|1|"SELECT oid, code, texte, ''::text as complement, 'O'::text as utilise 
FROM pmsi.t_expert_controle WHERE oid > 0 
ORDER BY code"|Controles Expert
GHM|t_ghm|1|"(SELECT t_ghm.oid, t_ghm.code, t_ghm.texte, CASE WHEN t_ghm.mco = 'C' THEN 'Chirurgie' WHEN t_ghm.mco = 'M' THEN 'Médecine' WHEN t_ghm.mco = 'O' THEN 'Obstétrique' ELSE '' END || ' ' || t_lignes_oap.code || ' ' || t_lignes_oap.texte as complement, 'O' as utilise FROM pmsi.t_ghm, pmsi.t_lignes_oap WHERE t_ghm.oid > 0 AND ligne_oap_id = t_lignes_oap.oid AND t_ghm.oid in (SELECT oid FROM pmsi.p_oids WHERE code_table='ghm') UNION SELECT t_ghm.oid, t_ghm.code, t_ghm.texte, CASE WHEN t_ghm.mco = 'C' THEN 'Chirurgie' WHEN t_ghm.mco = 'M' THEN 'Médecine' WHEN t_ghm.mco = 'O' THEN 'Obstétrique' ELSE '' END || ' ' || t_lignes_oap.code || ' ' || t_lignes_oap.texte as complement, 'N' as utilise FROM pmsi.t_ghm, pmsi.t_lignes_oap WHERE t_ghm.oid > 0 AND ligne_oap_id = t_lignes_oap.oid AND t_ghm.oid not in (SELECT oid FROM pmsi.p_oids WHERE code_table='ghm')) ORDER BY code"|GHM
GHS|t_ghs|1|(SELECT t_ghs.oid, t_ghs.code, t_ghs.texte, ''::text as complement, 'O' as utilise FROM pmsi.t_ghs WHERE t_ghs.oid > 0 AND t_ghs.code > 0 AND t_ghs.oid in (SELECT oid FROM pmsi.p_oids WHERE code_table='ghs') UNION SELECT t_ghs.oid, t_ghs.code, t_ghs.texte, ''  as complement, 'N' as utilise FROM pmsi.t_ghs WHERE t_ghs.oid > 0 AND t_ghs.code > 0 AND t_ghs.oid not in (SELECT oid FROM pmsi.p_oids WHERE code_table='ghs')) ORDER BY code|GHS
INDICATIONUCD|t_indication|1|(SELECT oid,code,texte,'' as complement,'O' AS utilise FROM pmsi.t_indication WHERE oid > 0 AND oid IN ( SELECT oid FROM pmsi.p_oids WHERE code_table = 'indication ucd' ) UNION SELECT oid ,code ,texte ,'' as complement ,'N' AS utilise FROM pmsi.t_indication WHERE oid > 0 AND oid NOT IN ( SELECT oid FROM pmsi.p_oids WHERE code_table = 'indication ucd' ) ) ORDER BY code|Indication UCD
LPP|t_lpp|1|(SELECT oid, code, texte, ''::text as complement, 'O' as utilise FROM pmsi.t_lpp WHERE oid > 0 AND oid in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'lpp') UNION SELECT oid, code, texte, ''::text as complement, 'N' as utilise FROM pmsi.t_lpp WHERE oid > 0 AND oid not in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'lpp')) ORDER BY code|LPP
MEDECIN|t_medecins|1|"(SELECT t_medecins.oid, t_medecins.code, t_medecins.nom || ' ' || t_medecins.prenom as texte, t_specialites_medecin.code || ' ' || t_specialites_medecin.texte as complement, 'O' as utilise FROM pmsi.t_medecins, pmsi.t_specialites_medecin WHERE t_medecins.oid > 0 AND t_medecins.specialite_id = t_specialites_medecin.oid AND t_medecins.oid in (SELECT oid FROM pmsi.p_oids WHERE code_table IN ('medecins_rss', 'medecins_acte') ) UNION SELECT t_medecins.oid, t_medecins.code, t_medecins.nom || ' ' || t_medecins.prenom as texte, t_specialites_medecin.code || ' ' || t_specialites_medecin.texte as complement, 'N' as utilise FROM pmsi.t_medecins, pmsi.t_specialites_medecin WHERE t_medecins.oid > 0 AND t_medecins.specialite_id = t_specialites_medecin.oid AND (t_medecins.oid not in (SELECT oid FROM pmsi.p_oids WHERE code_table IN ('medecins_rss', 'medecins_acte')))) ORDER BY code"|Médecins
PRESTA|t_prestations|1|(SELECT oid, code, texte, ''::text as complement, 'O'::text as utilise FROM pmsi.t_prestations WHERE oid > 0 AND oid in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'prestations' OR code_table = 'prestations_h') UNION SELECT oid, code, texte, ''::text as complement, 'N'::text as utilise FROM pmsi.t_prestations WHERE oid > 0 AND oid not in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'prestations' OR code_table = 'prestations_h') ) ORDER BY code|Prestations
TYPEFACT|t_prestations_p|1|(SELECT oid, code, texte, ''::text as complement, 'O'::text as utilise FROM pmsi.t_prestations WHERE oid > 0 AND oid in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'prestations_p'))  UNION  (SELECT oid, code, texte, ''::text as complement, 'N'::text as utilise FROM pmsi.t_prestations WHERE oid > 0 AND oid not in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'prestations_p') ) ORDER BY 2|Types de facturation
UCD|t_ucd|1|(SELECT oid, code, texte, laboratoire_texte as complement, 'O' as utilise FROM pmsi.t_ucd WHERE oid > 0 AND oid in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'ucd') UNION SELECT oid, code, texte, laboratoire_texte as complement, 'N' as utilise FROM pmsi.t_ucd WHERE oid > 0 AND oid not in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'ucd')) ORDER BY code|UCD
UF|t_unites_fonctionnelles|1|"(SELECT oid, code, texte, ''::text as complement, 'O' as utilise FROM pmsi.t_unites_fonctionnelles WHERE oid > 0 AND oid = ANY (ARRAY(SELECT oid FROM pmsi.p_oids WHERE code_table = 'uf')::bigint[])
UNION 
SELECT oid, code, texte, ''::text as complement, 'N' as utilise FROM pmsi.t_unites_fonctionnelles WHERE oid > 0 AND oid != ALL(ARRAY(SELECT oid FROM pmsi.p_oids WHERE code_table = 'uf')::bigint[])) ORDER BY code"|Unités fonctionnelles
UM|t_unites_medicales|1|"(SELECT oid, code, texte, ''::text as complement, 'O' as utilise FROM pmsi.t_unites_medicales WHERE oid > 0 AND oid in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'um') 
UNION 
SELECT oid, code, texte, ''::text as complement, 'N' as utilise FROM pmsi.t_unites_medicales WHERE oid > 0 AND oid not in (SELECT oid FROM pmsi.p_oids WHERE code_table = 'um')) ORDER BY code"|Unités médicales
