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.
122 lines
5.2 KiB
122 lines
5.2 KiB
return: text
|
|
lang: plpgsql
|
|
src: |
|
|
DECLARE
|
|
result TEXT;
|
|
BEGIN
|
|
INSERT INTO rh.t_divers (code, texte, valeur, description)
|
|
SELECT
|
|
'ODETP_SALARIE',
|
|
'OD ETP. Liste des salaries',
|
|
''::text,
|
|
'Format MATRICULE:HEURES|MATRICULE:HEURES|MATRICULE:HEURES'
|
|
WHERE 'ODETP_SALARIE' NOT IN (SELECT code FROM rh.t_divers);
|
|
|
|
INSERT INTO rh.t_divers (code, texte, valeur, description)
|
|
SELECT
|
|
'ODETP_RUBRIQUE_BRUT',
|
|
'OD ETP. Rubrique identifiant la presence',
|
|
''::text,
|
|
'Code de la rubrique'
|
|
WHERE 'ODETP_RUBRIQUE_BRUT' NOT IN (SELECT code FROM rh.t_divers);
|
|
|
|
INSERT INTO rh.t_divers (code, texte, valeur, description)
|
|
SELECT
|
|
'ODETP_RUBRIQUE_HEURE',
|
|
'OD ETP. Rubrique d''heure pour generation OD',
|
|
''::text,
|
|
'Code de la rubrique'
|
|
WHERE 'ODETP_RUBRIQUE_HEURE' NOT IN (SELECT code FROM rh.t_divers);
|
|
|
|
DROP TABLE IF EXISTS w_divers_odetp;
|
|
CREATE TEMP TABLE w_divers_odetp AS
|
|
SELECT p_salaries.oid AS odetp_salarie_id,
|
|
CASE WHEN ((string_to_array((string_to_array(replace(t_divers_odetp_salaries.valeur,',','.'),'|'))[i],':'))[2])::text <> '*' THEN base.cti_to_number((string_to_array((string_to_array(replace(t_divers_odetp_salaries.valeur,',','.'),'|'))[i],':'))[2]::text)
|
|
ELSE -1 END AS odetp_nb_heures,
|
|
t_rubriques_brut.oid AS odetp_rubrique_brut_id,
|
|
t_rubriques_heure.oid AS odetp_rubrique_heure_id
|
|
FROM rh.p_salaries
|
|
JOIN rh.t_divers t_divers_odetp_salaries ON t_divers_odetp_salaries.code = 'ODETP_SALARIE'
|
|
JOIN generate_series(1,100) i ON (string_to_array(t_divers_odetp_salaries.valeur,'|'))[i] <> ''
|
|
JOIN rh.t_divers t_divers_odetp_rubrique_brut ON t_divers_odetp_rubrique_brut.code = 'ODETP_RUBRIQUE_BRUT'
|
|
JOIN rh.t_divers t_divers_odetp_rubrique_heure ON t_divers_odetp_rubrique_heure.code = 'ODETP_RUBRIQUE_HEURE'
|
|
JOIN rh.t_rubriques t_rubriques_brut ON t_divers_odetp_rubrique_brut.valeur = t_rubriques_brut.code
|
|
JOIN rh.t_rubriques t_rubriques_heure ON t_divers_odetp_rubrique_heure.valeur = t_rubriques_heure.code
|
|
WHERE matricule = (string_to_array((string_to_array(t_divers_odetp_salaries.valeur,'|'))[i],':'))[1]
|
|
;
|
|
DROP TABLE IF EXISTS w_odetp_mois;
|
|
CREATE TEMP TABLE w_odetp_mois AS
|
|
SELECT p_historique_paie.salarie_id,
|
|
p_historique_paie.contrat_mois_id,
|
|
mois_paie,
|
|
CASE WHEN MAX(odetp_nb_heures) >= 0 THEN MAX(odetp_nb_heures) ELSE 0 END AS odetp_heure_contrat,
|
|
CASE WHEN MAX(odetp_nb_heures) >= 0 THEN MAX(odetp_nb_heures) ELSE SUM(heure_contrat) END AS odetp_heure_payee,
|
|
MAX(odetp_rubrique_brut_id) AS odetp_rubrique_brut_id,
|
|
MAX(odetp_rubrique_heure_id) AS odetp_rubrique_heure_id
|
|
FROM rh.p_historique_paie
|
|
JOIN w_divers_odetp ON salarie_id = odetp_salarie_id
|
|
WHERE rubrique_id = odetp_rubrique_brut_id OR
|
|
rubrique_id = odetp_rubrique_heure_id OR
|
|
heure_contrat <> 0 AND odetp_nb_heures = -1
|
|
GROUP BY 1,2,3
|
|
HAVING MAX(CASE WHEN rubrique_id = odetp_rubrique_heure_id THEN rubrique_id ELSE 0 END) = 0 AND
|
|
MAX(CASE WHEN rubrique_id = odetp_rubrique_brut_id THEN rubrique_id ELSE 0 END) <> 0
|
|
;
|
|
DROP TABLE IF EXISTS w_odetp_historique;
|
|
CREATE TEMP TABLE w_odetp_historique AS
|
|
SELECT p_historique_paie.*
|
|
FROM rh.p_historique_paie
|
|
JOIN w_odetp_mois ON
|
|
p_historique_paie.salarie_id = w_odetp_mois.salarie_id AND
|
|
p_historique_paie.mois_paie = w_odetp_mois.mois_paie AND
|
|
p_historique_paie.contrat_mois_id = w_odetp_mois.contrat_mois_id AND
|
|
p_historique_paie.rubrique_id = odetp_rubrique_brut_id;
|
|
UPDATE w_odetp_historique SET
|
|
rubrique_id = odetp_rubrique_heure_id,
|
|
base = odetp_heure_payee,
|
|
heure_payee = odetp_heure_payee,
|
|
heure_travaillee = odetp_heure_payee,
|
|
heure_contrat = odetp_heure_contrat,
|
|
montant_brut = 0,
|
|
taux_cotisation_salarie = 0,
|
|
montant_cotisation_salarie = 0,
|
|
taux_cotisation_patronale = 0,
|
|
montant_cotisation_patronale = 0,
|
|
montant_net_imposable_salarie = 0,
|
|
montant_net_a_payer_salarie = 0,
|
|
montant_avantage_nature = 0,
|
|
montant_frais_imposables = 0,
|
|
montant_masse_salariale = 0,
|
|
montant_od_net_salarie = 0
|
|
FROM w_odetp_mois
|
|
WHERE w_odetp_mois.salarie_id = w_odetp_historique.salarie_id AND
|
|
w_odetp_mois.contrat_mois_id = w_odetp_historique.contrat_mois_id AND
|
|
w_odetp_mois.mois_paie = w_odetp_historique.mois_paie
|
|
;
|
|
INSERT INTO rh.p_historique_paie
|
|
SELECT *
|
|
FROM w_odetp_historique;
|
|
|
|
UPDATE rh.p_chiffrier_production
|
|
SET nombre_heures_od = 0
|
|
WHERE nombre_heures_od IS NULL;
|
|
|
|
UPDATE rh.p_chiffrier_production
|
|
SET nombre_heures_od = p_chiffrier_production.nombre_heures_od + subview.nombre_heures_od
|
|
FROM
|
|
(
|
|
SELECT t_etablissements.entreprise_id,
|
|
p_contrats.etablissement_id,
|
|
mois_paie,
|
|
SUM(base) AS nombre_heures_od
|
|
FROM w_odetp_historique
|
|
JOIN rh.p_contrats ON contrat_id = p_contrats.oid
|
|
JOIN rh.t_etablissements ON p_contrats.etablissement_id = t_etablissements.oid
|
|
GROUP BY 1,2,3
|
|
) subview
|
|
WHERE p_chiffrier_production.entreprise_id = subview.entreprise_id AND
|
|
p_chiffrier_production.etablissement_id = subview.etablissement_id AND
|
|
p_chiffrier_production.mois = subview.mois_paie;
|
|
|
|
RETURN 'OK';
|
|
END;
|