/*truncate table*/
truncate table <PREFIXE_DW>AO_LIGNES_DETAIL
<SEPARATEUR>

/*Insert demandes AO inexistants x AO_FOUR*/
insert into <PREFIXE_DW>AO_LIGNES_DETAIL(AO_NUMERO_AO,AO_NUMERO_LIGNE_AO,AO_QUANTITE_AO,AO_DATE_LIVRAISON_AO,AO_TYPE_TRANSACTION,AO_CODE_FOURNISSEUR) 
select p.NO_AO,NO_PO,QTE,
CASE WHEN (DTE = ' ' OR DTE = '00000000' ) THEN NULL WHEN (DTE < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,DTE ,103) END,
'AO',f.CODE_FOU 
from <PREFIXE_HA>AO_PLANN p
cross join <PREFIXE_HA>AO_FOUR f
where p.NO_AO=f.NO_AO

<SEPARATEUR>
/*Insert AO Sans FOUR*/
insert into <PREFIXE_DW>AO_LIGNES_DETAIL(AO_NUMERO_AO,AO_NUMERO_LIGNE_AO,AO_QUANTITE_AO,AO_DATE_LIVRAISON_AO,AO_TYPE_TRANSACTION) 
select p.NO_AO,NO_PO,QTE,
CASE WHEN (DTE = ' ' OR DTE = '00000000' ) THEN NULL WHEN (DTE < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,DTE ,103) END,
'AO'
from <PREFIXE_HA>AO_PLANN p <WITHNOLOCK>
where p.NO_AO not in (select AO_NUMERO_AO from <PREFIXE_DW>AO_LIGNES_DETAIL)

<SEPARATEUR>
/*MAJ AO*/
update ao set 
AO_OBJET_AO = ent.OBJET_AO,
AO_DATE_CREATION_AO=CASE WHEN (ent.DTE_AO = ' ' OR ent.DTE_AO = '00000000' ) THEN NULL WHEN (ent.DTE_AO < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,ent.DTE_AO ,103) END,
AO_DATE_DELAI_REPONSE_AO=CASE WHEN (ent.DEL_REP = ' ' OR ent.DEL_REP = '00000000' ) THEN NULL WHEN (ent.DEL_REP < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,ent.DEL_REP ,103) END,
AO_DATE_VALIDATION_AO=CASE WHEN (ent.DTE_VALID_AO = ' ' OR ent.DTE_VALID_AO = '00000000' ) THEN NULL WHEN (ent.DTE_VALID_AO < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,ent.DTE_VALID_AO ,103) END,
AO_CODE_ARTICLE=lig.CODE_ART,
AO_TITRE_AO=substring( isnull( mt.CONTMEMO,' '),1,250)      ,
AO_COMMENTAIRE_AO=substring( isnull( mc.CONTMEMO,' '),1,250)       ,
AO_CODE_SOCIETE=ent.CODE_STE,
AO_CODE_ACHETEUR=ent.ACH,
AO_BUDGET_AO=ent.BUDG_ENT,
AO_CODE_DEVISE=ent.DEVISE,
AO_CODE_LANGUE=ent.COD_LNG,
AO_CODE_CENTRE_EMETTEUR=ent.EMETTEUR,
AO_PUBLIC_AO=CASE WHEN ent.AO_PUBLIC = 0x54 THEN 1 ELSE 0 END,
AO_NUMERO_DA=lig.NO_DA,
AO_NUMERO_LIGNE_DA=lig.NO_PO_DA,
AO_CHIFFRAGE_EXECUTION_DA=lig.CHIF_EXEC,
AO_DESIGNATION_ARTICLE=lig.DES,
AO_UNITE_AO=lig.UNITE,
AO_BUDGET_LIGNE_AO=lig.BUDGET,
AO_TYPE_DELAI_AO=ent.TYPEDEL,
AO_DELAI_AO=case when (ent.TYPEDEL='D') then lig.DELAI when (ent.TYPEDEL='T' ) then lig.DELAIT when (ent.TYPEDEL='J') then cast(lig.DELAIJ as varchar) else '' end, 
AO_DESIGNATION_COMP_LIGNE_AO=SUBSTRING( isnull( mdescomp.CONTMEMO,' ') , 1,100),
AO_CODE_FABRICANT=lig.COD_FAB,
AO_CODE_GAMME=lig.COD_GAM,
AO_CODE_REFERENCE_FABRICANT=lig.REF_FAB,
AO_PRIX_UNITAIRE_REFERENCE=lig.PRIX_REF,
AO_CENTRE_LIVRAISON=ent.CTR_LIV,
AO_ORIGINE_PRIX_REFERENCE=lig.ORI_PRIX_REF,
AO_CODE_PRESCRIPTEUR=ent.PRESCRIPTEUR,
AO_TYPE_CONSULTATION=ent.RFI_Q,
AO_CODE_FAMILLE=ent.CODE_FAM
from <PREFIXE_DW>AO_LIGNES_DETAIL ao <WITHNOLOCK>
join <PREFIXE_HA>AO_LIGNE lig <WITHNOLOCK> on ao.AO_NUMERO_AO= lig.NO_AO and ao.AO_NUMERO_LIGNE_AO=lig.NO_PO
join <PREFIXE_HA>AO_ENTET ent <WITHNOLOCK> on lig.NO_AO    = ent.NO_AO
left outer join <PREFIXE_HA>MEMOS mt <WITHNOLOCK> on ent.NM_TITRE=mt.NODEMEMO
left outer join <PREFIXE_HA>MEMOS mc <WITHNOLOCK> on ent.NM_COMENT=mc.NODEMEMO
left outer join <PREFIXE_HA>MEMOS mdescomp <WITHNOLOCK> on lig.NM_DCOMP=mdescomp.NODEMEMO

<SEPARATEUR>
/*Ajout lignes réponses*/
insert into <PREFIXE_DW>AO_LIGNES_DETAIL(AO_NUMERO_AO,AO_NUMERO_LIGNE_AO,AO_TYPE_TRANSACTION,AO_CODE_FOURNISSEUR,AO_REPONSE_DATE,AO_REPONSE_REFERENCE_FOURNISSEUR,AO_REPONSE_CODE_DEVISE,AO_REPONSE_DATE_LIMITE,
AO_REPONSE_COMMENTAIRE,AO_REPONSE_QUANTITE,AO_REPONSE_PRIX_UNITAIRE,AO_REPONSE_UNITE,AO_REPONSE_DATE_DELAI,AO_CODE_ARTICLE,AO_DESIGNATION_ARTICLE,AO_REPONSE_DESIGNATION_COMP_LIGNE,AO_REPONSE_COMMENTAIRE_FOURNISSEUR,
AO_CODE_FABRICANT,AO_CODE_GAMME,AO_CODE_REFERENCE_FABRICANT,AO_REPONSE_CODE_PAYS_ORIGINE,AO_REPONSE_CODE_PAYS_PROVENANCE) 
select l.NO_AO,NO_PO_AO,'AO_REPONSE',isnull(e.CODE_FOU,''),
CASE WHEN (e.DTE_REP = ' ' OR e.DTE_REP = '00000000' ) THEN NULL WHEN (e.DTE_REP < '17530101') THEN TRY_CONVERT (datetime,'19000101' ,103) ELSE TRY_CONVERT (datetime,e.DTE_REP ,103) END,
isnull(e.REFRPFOU,''),isnull(e.DEVISE,''),
CASE WHEN (e.DTELIMIT = ' ' OR e.DTELIMIT = '00000000' ) THEN NULL WHEN (e.DTELIMIT < '17530101') THEN TRY_CONVERT (datetime,'19000101' ,103) ELSE TRY_CONVERT (datetime,e.DTELIMIT ,103) END,
SUBSTRING( isnull( mt.CONTMEMO,' ') , 1,249),
l.QTE,l.PU,l.UNITE,
CASE WHEN (l.DELAI = ' ' OR l.DELAI = '00000000' ) THEN NULL WHEN (l.DELAI < '17530101') THEN TRY_CONVERT (datetime,'19000101' ,103) ELSE TRY_CONVERT (datetime,l.DELAI ,103) END,
l.CODE_ART,l.DES_ART,isnull(mt2.CONTMEMO,' '),l.COMFOURN,
l.COD_FAB,l.COD_GAM,REF_FAB,PAY_ORI,PAY_PROV
from <PREFIXE_HA>AO_REP l <WITHNOLOCK>
left outer join <PREFIXE_HA>AO_DAREP e <WITHNOLOCK> on e.NO_AO=l.NO_AO and e.CODE_FOU=l.CODE_FOU
left outer join <PREFIXE_HA>MEMOS mt <WITHNOLOCK> on e.COMMENT1=mt.NODEMEMO
left outer join <PREFIXE_HA>MEMOS mt2 <WITHNOLOCK> on l.DESCOMPL=mt2.NODEMEMO

<SEPARATEUR>
update <PREFIXE_DW>AO_LIGNES_DETAIL set [AO_REPONSE_MONTANT_LIGNE]=[AO_REPONSE_QUANTITE] * [AO_REPONSE_PRIX_UNITAIRE]

<SEPARATEUR>
/*MAJ de FOURNISS*/
update ao set 
AO_RAISON_SOCIALE_FOURNISSEUR=f.RS
from <PREFIXE_DW>AO_LIGNES_DETAIL ao 
join <PREFIXE_HA>FOURNISS f <WITHNOLOCK> on ao.AO_CODE_FOURNISSEUR=f.CODE

<SEPARATEUR>
/*MAJ de ARTICLES*/
update ao set 
AO_CODE_SOUS_FAMILLE=a.SSFAM
from <PREFIXE_DW>AO_LIGNES_DETAIL ao 
join <PREFIXE_HA>ARTICLES a <WITHNOLOCK> on ao.AO_CODE_ARTICLE=a.CODE_ART

<SEPARATEUR>
/*MAJ  from ENT*/
update ao set 
AO_DATE_CHOIX_FOURNISSEUR=CASE WHEN (e.VALCHXFOU = ' ' OR e.VALCHXFOU = '00000000' ) THEN NULL WHEN (e.VALCHXFOU < '17530101') THEN TRY_CONVERT (datetime,'19000101' ,103) ELSE TRY_CONVERT (datetime,e.VALCHXFOU ,103) END,
AO_RF_TYPE=e.RFI_Q,
AO_DATE_HEURE_LANCEMENT=case when HEURE_LANCEMENT<>'' then TRY_CONVERT(datetime, DTE_LANCEMENT+' '+   STUFF(STUFF(HEURE_LANCEMENT, 5, 0, ':'), 3, 0, ':') ) when HEURE_LANCEMENT=''       then TRY_CONVERT(datetime, DTE_LANCEMENT+' '+   '00:00:00' ) end,  
AO_DATE_HEURE_CLOTURE  = case when HEURE_CLOTURE  <>'' then TRY_CONVERT(datetime, DEL_REP+' '+   STUFF(STUFF(HEURE_CLOTURE, 5, 0, ':'), 3, 0, ':') )   when HEURE_CLOTURE  =''             then TRY_CONVERT(datetime, DEL_REP+' '+   '00:00:00' ) end,
AO_DATE_DELAI_REPONSE_AO=case when HEURE_CLOTURE  <>'' then TRY_CONVERT(datetime, DEL_REP+' '+   STUFF(STUFF(HEURE_CLOTURE, 5, 0, ':'), 3, 0, ':') )   when HEURE_CLOTURE  =''             then TRY_CONVERT(datetime, DEL_REP+' '+   '00:00:00' ) end,
AO_DATE_VALIDATION_AO=CASE WHEN (e.DTE_VALID_AO = ' ' OR e.DTE_VALID_AO = '00000000' ) THEN NULL WHEN (e.DTE_VALID_AO < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,e.DTE_VALID_AO ,103) END,
AO_DATE_CREATION_AO     =CASE WHEN (e.DTE_AO = ' ' OR e.DTE_AO = '00000000' ) THEN NULL WHEN (e.DTE_AO < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,e.DTE_AO ,103) END
from <PREFIXE_DW>AO_LIGNES_DETAIL ao 
join <PREFIXE_HA>AO_ENTET e <WITHNOLOCK> on ao.AO_NUMERO_AO=e.NO_AO

<SEPARATEUR>
/*MAJ de ENT*/
update ao set AO_TITRE_AO=left(CONTMEMO,100)
    from    <PREFIXE_DW>AO_LIGNES_DETAIL ao, <PREFIXE_HA>MEMOS m,<PREFIXE_HA>AO_ENTET a  where a.NM_TITRE=m.NODEMEMO and a.NO_AO=ao.AO_NUMERO_AO

<SEPARATEUR>
/*MAJ  from AO_PLANN*/
update ao set ao.AO_DATE_LIVRAISON_AO= CASE WHEN (p.DTE = ' ' OR p.DTE = '00000000' ) THEN NULL WHEN (p.DTE < '17530101') THEN null ELSE CONVERT (datetime,p.DTE ,103) END
	from <PREFIXE_DW>AO_LIGNES_DETAIL ao, <PREFIXE_HA>AO_PLANN p where ao.AO_NUMERO_AO=p.NO_AO and ao.AO_NUMERO_LIGNE_AO=p.NO_PO

<SEPARATEUR>
/*Special AGRICA 51*/
if exists (select * from <PREFIXE_HA>PARAMOXA where NO_SITCL=51)
	begin
		if not exists (select * from <OXADWDB_DATABASE>.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='AO_LIGNES_DETAIL' and COLUMN_NAME='AO_TYPE_DELAIS_AO')  alter table <PREFIXE_DW>AO_LIGNES_DETAIL add AO_TYPE_DELAIS_AO varchar(30) default '' null
	end

<SEPARATEUR>
DECLARE @sql NVARCHAR(255);
set @sql='update <PREFIXE_DW>AO_LIGNES_DETAIL set AO_TYPE_DELAIS_AO=AO_TYPE_DELAI_AO'
if exists (select * from <PREFIXE_HA>PARAMOXA where NO_SITCL=51) EXEC sp_executesql  @sql