//
//
//
//
// TO DO : normaliser les noms des fichiers
// Exemple : 24 - SIGNALETIQUE GROUPE
// load the file and rename the field names
Signalitique:
LOAD @1 as [salarie_matricule],
@2 as [salarie_id_ss],
@3 as [salarie_ste],
@4 as [salarie_etablissement],
@5 as [salarie_localisation_etablissement ],
@6 as [salarie_nom],
@7 as [salarie_prenom],
@8 as [salarie_classification],
@9 as [salarie_college], // degree of classification ( ex : manager , ingénieur .. college 1 , technicien : collége 2 )
@10 as [salarie_salaire_base_mensuel],
@11 as [salarie_statut_salarie],
@12 as [salarie_statut_paie],
@13 as [salarie_lot_paie ],
@14 as [salarie_section],
@15 as [salarie_dernier_embauche],
@16 as [salarie_date_derniere_entree],
@17 as [salarie_ancienneté_ramenee ],
@18 as [salarie_derniere_date_de_sorti],
@19 as [salarie_motif_de_sortie],
@20 as [salarie_motif_absence],
@21 as [salarie_date_effet],
@23 as [salarie_satut],
@24 as [salarie_numero_rue ],
@25 as [salarie_numero_rue_2],
@26 as [salarie_type_adresse],
@27 as [salarie_libelle_rue],
@28 as [salarie_complementaire_adresse],
@29 as [salarie_bureau_distributeur],
@30 as [salarie_code_postale],
@31 as [salarie_commune],
@32 as [salarie_paye],
@33 as [salarie_date_de_nasissance],
@34 as [salarie_lieu_de_naissance],
@35 as [salarie_pays_naissance ],
@36 as [salarie_direction],
@37 as [salarie_code_banque],
@38 as [salarie_code_agence ],
@39 as [salarie_numero_compte],
@40 as [salarie_cle_controle], // 2 lastes numbers of a bank compte
@41 as [salarie_description_banque],
@42 as [salarie nom_titulaire_du_compte],
@43 as [salarie_type_contrat],
@44 as [salarie_profil paie],
@45 as [salarie_CD_remuneration],
@46 as [salarie_ETP_paye], /* This is the equivalent of the part-time,
if a person works 80% it will be a 0.8 FTE
if it is part-time so it'll be equal to 0.5.
100% => 1.
This helps us to calculate the quarter of the employee's remuneration.*/
@47 as [salarie_email],
@48 as [salarie_CD empl],
@49 as [salarie_emploi]
FROM
[$(FilePath)\fichier As\Signalitique PS\24 - SIGNALETIQUE GROUPE*.xls]
(biff, no labels);
// Laod a 2nd File : this file is sent by the bank( SOCIETE GENERALE) and it contains all the information about PEE
PEE:
LOAD @1 as [PEE code entreprise],
@2 as [PEE nom],
@3 as [PEE prenom],
@4 as [PEE num compte],
@5 as [PEE num INSEE],
@6 as [PEE matricule],
@7 as [PEE statut salarié], // outgoing or incomming
@8 as [PEE date embauche],
@9 as [PEE date depart salarie],
@10 as [PEE email],
@11 as [PEE abonnement a E-courier],
@12 as [PEE flag NPAI],
@13 as [PEE code NFI],
@14 as [PEE completement lieu],
@15 as [PEE completement adresse],
@16 as [PEE num et libellé voie],
@17 as [PEE lieu dit],
@18 as [PEE code postale],
@19 as [PEE ville],
@20 as [PEE code pays],
@21 as [PEE nb parts dispo],
@23 as [PEE PAG indispo 5],
@24 as [PEE nb parts dispo 7],
@25 as [PEE PEE 7 relais],
@26 as [PEE PEE 7 nb parts dispo],
@27 as [PEE PEE 7 nb parts indispo],
@28 as [PEE PEE 5 nb parts dispo],
@29 as [PEE PEE 5 nb parts indispo],
@30 as [PEE nb parts dispo 5],
@31 as [PEE PEE 5 relais],
@32 as [PEE arcancia 455 dispo],
@33 as [PEE arcancia 455 indispo],
@34 as [PEE arcancia 257 dispo],
@35 as [PEE arcancia 257 indispo],
@36 as [PEE arcancia 654 dispo],
@37 as [PEE arcancia 654 indipo],
@38 as [PEE arcancia 351 dispo],
@39 as [PEE arcancia 351 indispo],
@40 as [PEE PAG dispo],
@41 as [PEE PAG indispo 7],
@42 as [PEE ccb],
@43 as [PEE deblocage exceptionnel 2013 perm 7],
@44 as [PEE deblocage exceptionnel 2013 perm 5]
FROM
[$(FilePath)\fichier As\SG PEE\09 - PEE 02-10-13*.xls]
(biff, no labels);
Signalitique_ref:
LOAD
@1 as [salarie_matricule],
@2 as [salarie_id_ss],
@6 as [salarie_nom],
@7 as [salarie_prenom]
FROM [$(FilePath)\fichier As\Signalitique PS\24 - SIGNALETIQUE GROUPE*.xls]
(biff, no labels);
let ModificationTime ;
Let ThisExecTime = ReloadTime();
Let ThisExecTime = Now( );
SQL SELECT salarie_id_ss,salarie_matricule ,salarie_nom, salarie_prenom FROM Signalitique_ref
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(ThisExecTime)#;
Concatenate LOAD salarie_id_ss, salarie_nom, salarie_nom, salarie_matricule FROM $(FilePath)\Dev\Signalitique.qvd;
where not(Exists(salarie_id_ss));
Inner Join SQL SELECT salarie_id_ss FROM Signalitique_ref;
If ScriptErrorCount = 0 then
STORE Signalitique_ref INTO $(FilePath)\Dev\Signalitique_ref.QVD;
Let LastExecTime = ThisExecTime;
End If
SQL SELECT salarie_id_ss,salarie_matricule ,salarie_nom, salarie_prenom FROM Signalitique_ref
