/*truncate table non cat a.txt*/
--truncate table ACCORD_CADRE

/*ajout des ACART */
insert into <PREFIXE_DW>ACCORD_CADRE (ACC_CODE_ACCORD_CADRE,ACC_LIBELLE,ACC_CODE_ARTICLE,ACC_CODE_FOURNISSEUR,ACC_PRIX_UNITAIRE,ACC_QUANTITE_MAX,ACC_QUANTITE_CDE,ACC_RS_FOURNISSEUR,ACC_DATE_DEB,ACC_DATE_FIN,ACC_DESIGNATION_ARTICLE,ACC_NUMERO_DCC,ACC_NUMERO_AVENANT)
select l.CODE_ACD,e.LIBELLE,l.CODE_ART,e.CODE_FOUR,l.PU,l.QTEMAX,l.QTECDEE,f.RS,
CASE WHEN (DATE_DEB = ' ' OR DATE_DEB = '00000000' ) THEN NULL WHEN (DATE_DEB < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,DATE_DEB ,103) END,
CASE WHEN (DATE_FIN = ' ' OR DATE_FIN = '00000000' ) THEN NULL WHEN (DATE_FIN < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,DATE_FIN ,103) END,
isnull(a.DESIGN,' '),
isnull(dcc.NO_DCC,' '),
isnull(e.AVENANT,0)
from <PREFIXE_HA>ACART l
left outer join <PREFIXE_HA>ACCCADRE e on e.CODE_ACD=l.CODE_ACD
left outer join <PREFIXE_HA>FOURNISS f on e.CODE_FOUR=f.CODE
left outer join <PREFIXE_HA>ARTICLES a on l.CODE_ART=a.CODE_ART
left outer join <PREFIXE_HA>DCC_LIEN dcc on TYPEOBJ='ACCCADRE' and COD_ENR=e.CODE_ACD

<SEPARATEUR>
/*MAJ */
update d set ACC_NUMERO_AVENANT= isnull(s.AVENANT,0)
	from <PREFIXE_HA>ACCCADRE s, <PREFIXE_DW>ACCORD_CADRE d  where s.CODE_ACD=d.ACC_CODE_ACCORD_CADRE