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.
337 lines
16 KiB
337 lines
16 KiB
src: |
|
|
SELECT p_handicap.oid AS handicap_id,
|
|
p_salaries.matricule AS handicap_code,
|
|
(p_salaries.nom::text || ' '::text) || p_salaries.prenom::text AS handicap_texte,
|
|
p_handicap.taux_incapacite,
|
|
p_handicap.date_effet,
|
|
p_handicap.type_handicap,
|
|
p_handicap.categorie_cotorep,
|
|
p_handicap.form_prof_500h,
|
|
p_handicap.annee_form_prof,
|
|
p_handicap.placement_anterieur,
|
|
p_handicap.taux_ipp,
|
|
p_handicap.date_echeance,
|
|
p_handicap.code_protection,
|
|
p_handicap.nature_contrat,
|
|
p_handicap.rqth,
|
|
p_handicap.beneficiaire_aah,
|
|
p_handicap.carte_invalidite,
|
|
p_handicap.maladie_p_at,
|
|
p_handicap.invalide_pensionne,
|
|
p_handicap.mutile_guerre,
|
|
p_handicap.assimile_mutile_guerre,
|
|
p_handicap.pompier_volontaire,
|
|
p_handicap.unite_age_26_51,
|
|
p_handicap.lourd_handicap,
|
|
p_handicap.emb_1er_handicap,
|
|
p_handicap.emb_chom_long,
|
|
p_handicap.emb_ea_cdtd_esat,
|
|
p_handicap.annee_embauche,
|
|
p_salaries.finess,
|
|
p_salaries.oid AS salarie_id,
|
|
p_salaries.matricule AS salarie_code,
|
|
(p_salaries.nom::text || ' '::text) || p_salaries.prenom::text AS salarie_texte,
|
|
p_salaries.nom,
|
|
p_salaries.nom_naissance,
|
|
p_salaries.prenom,
|
|
p_salaries.date_naissance,
|
|
CASE
|
|
WHEN p_salaries.sexe = 'F'::bpchar THEN 2
|
|
ELSE 1
|
|
END AS sexe_id,
|
|
p_salaries.sexe AS sexe_code,
|
|
CASE
|
|
WHEN p_salaries.sexe = 'F'::bpchar THEN 'Féminin'::text
|
|
ELSE 'Masculin'::text
|
|
END AS sexe_texte,
|
|
p_salaries.situation_famille_id,
|
|
t_situations_famille.code AS situation_famille_code,
|
|
t_situations_famille.texte_court AS situation_famille_texte,
|
|
p_salaries.code_original,
|
|
p_salaries.entreprise_id,
|
|
t_entreprises.code AS entreprise_code,
|
|
t_entreprises.texte_court AS entreprise_texte,
|
|
t_entreprises.section_id AS entreprise_section_id,
|
|
t_entreprises.section_code AS entreprise_section_code,
|
|
t_entreprises.section_texte AS entreprise_section_texte,
|
|
p_salaries.nationalite_id,
|
|
t_nationalites.code AS nationalite_code,
|
|
t_nationalites.texte_court AS nationalite_texte,
|
|
t_nationalites.section_id AS nationalite_section_id,
|
|
t_nationalites.section_code AS nationalite_section_code,
|
|
t_nationalites.section_texte AS nationalite_section_texte,
|
|
t_codes_postaux.departement_id,
|
|
t_codes_postaux.departement_code,
|
|
t_codes_postaux.departement_texte,
|
|
p_salaries.code_postal_id,
|
|
t_codes_postaux.code AS code_postal_code,
|
|
t_codes_postaux.texte_court AS code_postal_texte,
|
|
t_codes_postaux.section_id AS code_postal_section_id,
|
|
t_codes_postaux.section_code AS code_postal_section_code,
|
|
t_codes_postaux.section_texte AS code_postal_section_texte,
|
|
p_profils.oid,
|
|
p_profils.type_contrat_id,
|
|
p_profils.type_contrat_code_original,
|
|
p_profils.type_contrat_code,
|
|
p_profils.type_contrat_texte,
|
|
p_profils.type_contrat_section_id,
|
|
p_profils.type_contrat_section_code,
|
|
p_profils.type_contrat_section_texte,
|
|
p_profils.motif_debut_id,
|
|
p_profils.motif_debut_code_original,
|
|
p_profils.motif_debut_code,
|
|
p_profils.motif_debut_texte,
|
|
p_profils.motif_debut_section_id,
|
|
p_profils.motif_debut_section_code,
|
|
p_profils.motif_debut_section_texte,
|
|
p_profils.motif_fin_id,
|
|
p_profils.motif_fin_code_original,
|
|
p_profils.motif_fin_code,
|
|
p_profils.motif_fin_texte,
|
|
p_profils.motif_fin_section_id,
|
|
p_profils.motif_fin_section_code,
|
|
p_profils.motif_fin_section_texte,
|
|
p_profils.qualification_id,
|
|
p_profils.qualification_code_original,
|
|
p_profils.qualification_code,
|
|
p_profils.qualification_texte,
|
|
p_profils.qualification_section_id,
|
|
p_profils.qualification_section_code,
|
|
p_profils.qualification_section_texte,
|
|
p_profils.service_id,
|
|
p_profils.service_code_original,
|
|
p_profils.service_code,
|
|
p_profils.service_texte,
|
|
p_profils.service_section_id,
|
|
p_profils.service_section_code,
|
|
p_profils.service_section_texte,
|
|
p_profils.specialite_id,
|
|
p_profils.specialite_code_original,
|
|
p_profils.specialite_code,
|
|
p_profils.specialite_texte,
|
|
p_profils.specialite_section_id,
|
|
p_profils.specialite_section_code,
|
|
p_profils.specialite_section_texte,
|
|
p_profils.type_temps_travail_id,
|
|
p_profils.type_temps_travail_code_original,
|
|
p_profils.type_temps_travail_code,
|
|
p_profils.type_temps_travail_texte,
|
|
p_profils.type_temps_travail_section_id,
|
|
p_profils.type_temps_travail_section_code,
|
|
p_profils.type_temps_travail_section_texte,
|
|
p_profils.categorie_socio_professionnelle_id,
|
|
p_profils.categorie_socio_professionnelle_code_original,
|
|
p_profils.categorie_socio_professionnelle_code,
|
|
p_profils.categorie_socio_professionnelle_texte,
|
|
p_profils.categorie_socio_professionnelle_section_id,
|
|
p_profils.categorie_socio_professionnelle_section_code,
|
|
p_profils.categorie_socio_professionnelle_section_texte,
|
|
p_profils.statut_id,
|
|
p_profils.statut_code_original,
|
|
p_profils.statut_code,
|
|
p_profils.statut_texte,
|
|
p_profils.statut_section_id,
|
|
p_profils.statut_section_code,
|
|
p_profils.statut_section_texte,
|
|
p_profils.code_emploi_id,
|
|
p_profils.code_emploi_code_original,
|
|
p_profils.code_emploi_code,
|
|
p_profils.code_emploi_texte,
|
|
p_profils.code_emploi_section_id,
|
|
p_profils.code_emploi_section_code,
|
|
p_profils.code_emploi_section_texte,
|
|
p_profils.societe_interim_id,
|
|
p_profils.societe_interim_code_original,
|
|
p_profils.societe_interim_code,
|
|
p_profils.societe_interim_texte,
|
|
p_profils.societe_interim_section_id,
|
|
p_profils.societe_interim_section_code,
|
|
p_profils.societe_interim_section_texte,
|
|
p_profils.section_analytique_paie_id,
|
|
p_profils.section_analytique_paie_code_original,
|
|
p_profils.section_analytique_paie_code,
|
|
p_profils.section_analytique_paie_texte,
|
|
p_profils.section_analytique_paie_section_id,
|
|
p_profils.section_analytique_paie_section_code,
|
|
p_profils.section_analytique_paie_section_texte,
|
|
p_profils.section_analytique_id,
|
|
p_profils.section_analytique_code_original,
|
|
p_profils.section_analytique_code,
|
|
p_profils.section_analytique_texte,
|
|
p_profils.section_analytique_section_id,
|
|
p_profils.section_analytique_section_code,
|
|
p_profils.section_analytique_section_texte,
|
|
p_profils.grille_id,
|
|
p_profils.grille_code_original,
|
|
p_profils.grille_code,
|
|
p_profils.grille_texte,
|
|
p_profils.grille_section_id,
|
|
p_profils.grille_section_code,
|
|
p_profils.grille_section_texte,
|
|
p_profils.grille_groupe_id,
|
|
p_profils.grille_groupe_code_original,
|
|
p_profils.grille_groupe_code,
|
|
p_profils.grille_groupe_texte,
|
|
p_profils.grille_groupe_section_id,
|
|
p_profils.grille_groupe_section_code,
|
|
p_profils.grille_groupe_section_texte,
|
|
p_profils.type_horaire_id,
|
|
p_profils.type_horaire_code_original,
|
|
p_profils.type_horaire_code,
|
|
p_profils.type_horaire_texte,
|
|
p_profils.type_horaire_section_id,
|
|
p_profils.type_horaire_section_code,
|
|
p_profils.type_horaire_section_texte,
|
|
p_profils.groupe_cotisant_id,
|
|
p_profils.groupe_cotisant_code_original,
|
|
p_profils.groupe_cotisant_code,
|
|
p_profils.groupe_cotisant_texte,
|
|
p_profils.groupe_cotisant_section_id,
|
|
p_profils.groupe_cotisant_section_code,
|
|
p_profils.groupe_cotisant_section_texte,
|
|
t_anciennetes_annee.code AS anciennete_annee_code,
|
|
t_anciennetes_annee.texte AS anciennete_annee_texte,
|
|
t_anciennetes_annee.section_id AS anciennete_annee_section_id,
|
|
t_anciennetes_annee.section_code AS anciennete_annee_section_code,
|
|
t_anciennetes_annee.section_texte AS anciennete_annee_section_texte,
|
|
p_contrats_mois.anciennete_mois,
|
|
p_contrats_mois.anciennete_annee_id,
|
|
p_contrats_mois.age_id,
|
|
p_contrats_mois.age_jours,
|
|
p_contrats_mois.oid AS contrat_mois_id,
|
|
p_contrats_mois.mois_activite AS mois_activite_id,
|
|
p_contrats_mois.mois_activite AS mois_activite_code,
|
|
(
|
|
CASE
|
|
WHEN date_part('month'::text, p_contrats_mois.date_fin) = 1::double precision THEN 'Janvier'::text
|
|
WHEN date_part('month'::text, p_contrats_mois.date_fin) = 2::double precision THEN 'Février'::text
|
|
WHEN date_part('month'::text, p_contrats_mois.date_fin) = 3::double precision THEN 'Mars'::text
|
|
WHEN date_part('month'::text, p_contrats_mois.date_fin) = 4::double precision THEN 'Avril'::text
|
|
WHEN date_part('month'::text, p_contrats_mois.date_fin) = 5::double precision THEN 'Mai'::text
|
|
WHEN date_part('month'::text, p_contrats_mois.date_fin) = 6::double precision THEN 'Juin'::text
|
|
WHEN date_part('month'::text, p_contrats_mois.date_fin) = 7::double precision THEN 'Juillet'::text
|
|
WHEN date_part('month'::text, p_contrats_mois.date_fin) = 8::double precision THEN 'Aout'::text
|
|
WHEN date_part('month'::text, p_contrats_mois.date_fin) = 9::double precision THEN 'Septembre'::text
|
|
WHEN date_part('month'::text, p_contrats_mois.date_fin) = 10::double precision THEN 'Octobre'::text
|
|
WHEN date_part('month'::text, p_contrats_mois.date_fin) = 11::double precision THEN 'Novembre'::text
|
|
WHEN date_part('month'::text, p_contrats_mois.date_fin) = 12::double precision THEN 'Décembre'::text
|
|
ELSE date_part('month'::text, p_contrats_mois.date_fin)::text
|
|
END || ' '::text) || date_part('year'::text, p_contrats_mois.date_fin)::text AS mois_activite_texte,
|
|
date_part('year'::text, p_contrats_mois.date_fin) AS annee_activite_id,
|
|
date_part('year'::text, p_contrats_mois.date_fin) AS annee_activite_code,
|
|
date_part('year'::text, p_contrats_mois.date_fin) AS annee_activite_texte,
|
|
p_contrats_mois.date_debut AS date_debut_activite,
|
|
p_contrats_mois.date_fin AS date_fin_activite,
|
|
p_contrats.oid AS contrat_id,
|
|
p_contrats.numero_contrat AS contrat_code,
|
|
(p_salaries.nom::text || ' '::text) || p_salaries.prenom::text AS contrat_texte,
|
|
p_contrats.date_debut AS date_debut_contrat,
|
|
p_contrats.date_fin AS date_fin_contrat,
|
|
t_etablissements.code AS etablissement_code,
|
|
t_etablissements.texte_court AS etablissement_texte,
|
|
t_etablissements.section_id AS etablissement_section_id,
|
|
t_etablissements.section_code AS etablissement_section_code,
|
|
t_etablissements.section_texte AS etablissement_section_texte,
|
|
p_contrats_mois.age_id AS age_code,
|
|
p_contrats_mois.age_id::text || ' ans'::text AS age_texte,
|
|
p_profils.unite_fonctionnelle_id,
|
|
p_profils.unite_fonctionnelle_code_original,
|
|
p_profils.unite_fonctionnelle_code,
|
|
p_profils.unite_fonctionnelle_texte,
|
|
p_profils.unite_fonctionnelle_section_id,
|
|
p_profils.unite_fonctionnelle_section_code,
|
|
p_profils.unite_fonctionnelle_section_texte,
|
|
p_profils.lettre_budgetaire_id,
|
|
p_profils.lettre_budgetaire_code_original,
|
|
p_profils.lettre_budgetaire_code,
|
|
p_profils.lettre_budgetaire_texte,
|
|
p_profils.lettre_budgetaire_section_id,
|
|
p_profils.lettre_budgetaire_section_code,
|
|
p_profils.lettre_budgetaire_section_texte,
|
|
p_profils.commission_paritaire_id,
|
|
p_profils.commission_paritaire_code_original,
|
|
p_profils.commission_paritaire_code,
|
|
p_profils.commission_paritaire_texte,
|
|
p_profils.commission_paritaire_section_id,
|
|
p_profils.commission_paritaire_section_code,
|
|
p_profils.commission_paritaire_section_texte,
|
|
p_profils.compte_salarie_id AS compte_imputation_id,
|
|
p_profils.compte_salarie_code_original AS compte_imputation_code_original,
|
|
p_profils.compte_salarie_code AS compte_imputation_code,
|
|
p_profils.compte_salarie_texte AS compte_imputation_texte,
|
|
p_profils.compte_salarie_section_id AS compte_imputation_section_id,
|
|
p_profils.compte_salarie_section_code AS compte_imputation_section_code,
|
|
p_profils.compte_salarie_section_texte AS compte_imputation_section_texte,
|
|
p_profils.categorie_conge_id,
|
|
p_profils.categorie_conge_code_original,
|
|
p_profils.categorie_conge_code,
|
|
p_profils.categorie_conge_texte,
|
|
p_profils.categorie_conge_section_id,
|
|
p_profils.categorie_conge_section_code,
|
|
p_profils.categorie_conge_section_texte,
|
|
p_profils.filiere_id,
|
|
p_profils.filiere_code_original,
|
|
p_profils.filiere_code,
|
|
p_profils.filiere_texte,
|
|
p_profils.filiere_section_id,
|
|
p_profils.filiere_section_code,
|
|
p_profils.filiere_section_texte,
|
|
p_profils.cadre_emploi_id,
|
|
p_profils.cadre_emploi_code_original,
|
|
p_profils.cadre_emploi_code,
|
|
p_profils.cadre_emploi_texte,
|
|
p_profils.cadre_emploi_section_id,
|
|
p_profils.cadre_emploi_section_code,
|
|
p_profils.cadre_emploi_section_texte,
|
|
p_profils.categorie_statutaire_id,
|
|
p_profils.categorie_statutaire_code_original,
|
|
p_profils.categorie_statutaire_code,
|
|
p_profils.categorie_statutaire_texte,
|
|
p_profils.categorie_statutaire_section_id,
|
|
p_profils.categorie_statutaire_section_code,
|
|
p_profils.categorie_statutaire_section_texte,
|
|
p_salaries.date_entree_ets,
|
|
p_salaries.date_entree_fp,
|
|
p_salaries.date_entree_fph,
|
|
p_contrats_mois.anciennete_fp_mois,
|
|
p_contrats_mois.anciennete_fp_annee_id,
|
|
p_contrats_mois.anciennete_fph_mois,
|
|
p_contrats_mois.anciennete_fph_annee_id,
|
|
anciennete_fp.code AS anciennete_fp_code,
|
|
anciennete_fp.texte AS anciennete_fp_texte,
|
|
anciennete_fp.section_id AS anciennete_fp_section_id,
|
|
anciennete_fp.section_code AS anciennete_fp_section_code,
|
|
anciennete_fp.section_texte AS anciennete_fp_section_texte,
|
|
anciennete_fph.code AS anciennete_fph_code,
|
|
anciennete_fph.texte AS anciennete_fph_texte,
|
|
anciennete_fph.section_id AS anciennete_fph_section_id,
|
|
anciennete_fph.section_code AS anciennete_fph_section_code,
|
|
anciennete_fph.section_texte AS anciennete_fph_section_texte,
|
|
p_salaries.no_adeli,
|
|
p_salaries.matricule_retraite,
|
|
t_code_cotisation.oid AS code_cotisation_id,
|
|
t_code_cotisation.code_original AS code_cotisation_code_original,
|
|
t_code_cotisation.code AS code_cotisation_code,
|
|
t_code_cotisation.texte AS code_cotisation_texte,
|
|
t_code_cotisation.section_id AS code_cotisation_section_id,
|
|
t_code_cotisation.section_code AS code_cotisation_section_code,
|
|
t_code_cotisation.section_texte AS code_cotisation_section_texte,
|
|
p_salaries.section_id AS salarie_section_id,
|
|
p_salaries.section_code AS salarie_section_code,
|
|
p_salaries.section_texte AS salarie_section_texte,
|
|
p_profil_contrat_mois.pole_id AS rhp_pole_id
|
|
FROM rh.p_handicap
|
|
LEFT JOIN rh.p_salaries ON p_handicap.salarie_id = p_salaries.oid
|
|
LEFT JOIN rh.p_contrats ON p_contrats.salarie_id = p_salaries.oid
|
|
JOIN rh.p_contrats_mois ON p_contrats_mois.contrat_id = p_contrats.oid
|
|
JOIN rh.t_etablissements ON p_contrats_mois.etablissement_id = t_etablissements.oid
|
|
JOIN rh.t_entreprises ON p_salaries.entreprise_id = t_entreprises.oid
|
|
JOIN rh.t_codes_postaux ON p_salaries.code_postal_id = t_codes_postaux.oid
|
|
JOIN rh.t_nationalites ON p_salaries.nationalite_id = t_nationalites.oid
|
|
JOIN rh.t_situations_famille ON p_salaries.situation_famille_id = t_situations_famille.oid
|
|
JOIN rh.t_anciennetes_annee ON p_contrats_mois.anciennete_annee_id = t_anciennetes_annee.oid
|
|
JOIN rh.t_anciennetes_annee anciennete_fp ON p_contrats_mois.anciennete_annee_id = anciennete_fp.oid
|
|
JOIN rh.t_anciennetes_annee anciennete_fph ON p_contrats_mois.anciennete_annee_id = anciennete_fph.oid
|
|
JOIN rh.t_code_cotisation ON t_code_cotisation.oid = p_salaries.code_cotisation_id
|
|
JOIN rh.p_profil_contrat_mois ON p_profil_contrat_mois.contrat_mois_id = p_contrats_mois.oid
|
|
JOIN rh.p_profils ON p_profils.oid = p_profil_contrat_mois.profil_id;
|