/*Correction heures dans OXALOG*/
update <PREFIXE_HA>OXALOG set HEU_LOG=rtrim(HEU_LOG) + '00'  where SUBSTRING(HEU_LOG,5,2)=''

<SEPARATEUR>
--truncate table <PREFIXE_DW>AUDITS

<SEPARATEUR>
/*Ajout OXALOG*/
insert into <PREFIXE_DW>AUDITS (AUD_ID,AUD_UTILISATEUR,AUD_APPLICATION,AUD_OPERATION,AUD_MESSAGE,AUD_OBJET,AUD_ID1_OBJET,AUD_ID2_OBJET,AUD_DATE,AUD_TABLE_SOURCE)
select MAGICKEY,USER_HA,upper(APPLI),OPE,TXT_MES,TYP_OBJ,ID1_OBJ,ID2_OBJ,cast( DAT_LOG +' '+substring(HEU_LOG,1,2)+':'+  substring(HEU_LOG,3,2)+':' +substring(HEU_LOG,5,2) as datetime),'O'
from <PREFIXE_HA>OXALOG o with (nolock)
left outer join <PREFIXE_DW>AUDITS a on o.MAGICKEY=a.AUD_ID and a.AUD_TABLE_SOURCE='O'
where a.AUD_ID is null

<SEPARATEUR>
/*delte AUDIT not found in OXALOG*/
delete a from <PREFIXE_DW>AUDITS a 
left outer join <PREFIXE_HA>OXALOG o on a.AUD_ID=o.MAGICKEY and a.AUD_TABLE_SOURCE='O' where o.DAT_LOG is null

<SEPARATEUR>
/*Ajout UTILHISTCNX*/
/*insert into AUDITS (AUD_UTILISATEUR,AUD_APPLICATION,AUD_OPERATION ,AUD_DATE,AUD_OBJET,AUD_ID1_OBJET,AUD_ID)
select CODE_USR,
case when ORIGINE='PROC' then 'OXAPROC' when ORIGINE='SRM' then 'OXASRM' else ' ' end,
'LOGIN',
cast( DATE_CNX +' '+substring(HEURE_CNX,1,2)+':'+  substring(HEURE_CNX,3,2)+':' +substring(HEURE_CNX,5,2) as datetime)  ,
'USER', CODE_USR,NOM_CTX
from IDEX_PROD_95.dbo.UTILHISTCNX u with (nolock)
left outer join AUDITS a on u.NOM_CTX=a.AUD_ID
where OK=0x54 and a.AUD_ID is null*/

