/*truncate table*/
truncate table <PREFIXE_DW>DCC_LIGNES

<SEPARATEUR>
/*<VERSION_MINIMUM>10.32ai</VERSION_MINIMUM>*/
/*Insert DCC*/
insert into <PREFIXE_DW>DCC_LIGNES(DCC_NUMERO_DCC,DCC_CODE_ARTICLE,DCC_CODE_ACHETEUR,DCC_TYPE_SOCIETES,DCC_CODE_SOCIETE_GROUPE,DCC_LIBELLE,DCC_TITRE,DCC_DATE_CREATION,DCC_DATE_FERMETURE,DCC_CODE_FABRICANT,DCC_REFERENCE_FABRICANT,
DCC_CODE_FOURNISSEUR,DCC_DESIGNATION,DCC_DESIGNATION_COMPLEMENTAIRE,DCC_QUANTITE,DCC_UNITE,DCC_STATUT,DCC_COMMENTAIRE,DCC_STATUT_LIBELLE) 
select l.NO_DCC,l.CODE_ART,e.ACH,e.TYPSTE,e.CODE_GRSTE,e.LIBELLE,e.TITRE,
CASE WHEN (e.DTE_CREAT = ' ' OR e.DTE_CREAT = '00000000' ) THEN NULL WHEN (e.DTE_CREAT < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,e.DTE_CREAT ,103) END,
CASE WHEN (e.DTE_FERM = ' ' OR e.DTE_FERM = '00000000' ) THEN NULL WHEN (e.DTE_FERM < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,e.DTE_FERM ,103) END,
l.CODE_FAB,l.REF_FAB,l.CODE_FOU,l.DESIGN,l.DESCOMP,l.QTE,l.UNIT,l.STATUS,l.COMM,
case when (l.STATUS='') then 'Articles sans fournisseur' 
     when (l.STATUS='0') then 'Articles en Attente' 
	 when (l.STATUS='1') then 'Articles à traiter' 
	 when (l.STATUS='2') then 'Articles en AO'
	 when (l.STATUS='3') then 'Articles avec réponse fournisseur'
	 when (l.STATUS='4') then 'Articles avec fournisseur sélectionné'
	 when (l.STATUS='5') then 'Articles en accord cadre'
	 when (l.STATUS='6') then 'Articles en commande'
else '' end
from <PREFIXE_HA>DCC_ARTICLES l
left outer join <PREFIXE_HA>DCC_ENTET e on e.NO_DCC=l.NO_DCC


<SEPARATEUR>
/*<VERSION_MINIMUM>10.32ai</VERSION_MINIMUM>*/
/*IMAJ Documents*/
update doc set doc.DOC_NUMERO_DCC=l.NO_DCC
from   <PREFIXE_DW>DOCUMENTS doc,  <PREFIXE_HA>DCC_LIEN l
where DOC_NUMERO_DOC=cast(l.COD_ENR as int) and left(doc.DOC_TYPE_TRANSACTION,2)=left( l.TYPEOBJ,2) and l.TYPEOBJ<>'ACCCADRE'

<SEPARATEUR>
/*IMAJ AO*/
/*<VERSION_MINIMUM>10.32ai</VERSION_MINIMUM>*/
update doc set doc.AO_NUMERO_DCC=l.NO_DCC
from   <PREFIXE_DW>AO_LIGNES_DETAIL doc,  <PREFIXE_HA>DCC_LIEN l
where AO_NUMERO_AO=cast(l.COD_ENR as int) and left(doc.AO_TYPE_TRANSACTION,2)=left( l.TYPEOBJ,2) and l.TYPEOBJ<>'ACCCADRE'

<SEPARATEUR>
/*MAJ ACC*/
/*<VERSION_MINIMUM>10.32ai</VERSION_MINIMUM>*/
update doc set doc.ACC_NUMERO_DCC=l.NO_DCC
from   <PREFIXE_DW>ACCORD_CADRE doc,  <PREFIXE_HA>DCC_LIEN l
where ACC_CODE_ACCORD_CADRE=l.COD_ENR and  l.TYPEOBJ='ACCCADRE'

<SEPARATEUR>
/*<VERSION_MINIMUM>10.32ai</VERSION_MINIMUM>*/
/*Isert evenements Documents*/
insert into <PREFIXE_DW>EVENEMENTS (EVE_TYPE_EVENEMENT,EVE_TYPE_TRANSACTION,EVE_NUMERO_DOCUMENT,EVE_NUMERO_ORDRE,EVE_DATE_CREATION,EVE_HEURE_CREATION,EVE_CODE_EVENEMENT,EVE_UTILISATEUR,EVE_COMMENTAIRES,EVE_OBJET,EVE_LIEN_OBJET,EVE_LIBELLE_EVENEMENT)  
select distinct 'DCC','DCC',NO_DCC,NUM_ORD,
CASE WHEN (e.DTE = ' ' OR e.DTE = '00000000' ) THEN NULL WHEN (e.DTE < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,e.DTE ,103) END,
HEURE,CODE,UTIL,COMM,TYPEOBJ,COD_ENR,s.LIBELLE
from <PREFIXE_HA>DCC_EVENT e
left outer join <PREFIXE_HA>SUIDCCDAT s on e.CODE=s.CODE_SDCC
left outer join <PREFIXE_DW>EVENEMENTS evt on EVE_TYPE_EVENEMENT='DCC' and evt.EVE_TYPE_TRANSACTION='DCC' and   e.NO_DCC=evt.EVE_NUMERO_DOCUMENT and e.NUM_ORD=evt.EVE_NUMERO_ORDRE
where evt.EVE_NUMERO_DOCUMENT is null

<SEPARATEUR>
/*<VERSION_MINIMUM>10.32ai</VERSION_MINIMUM>*/
/*Recherche dans les AO*/
select  AO_NUMERO_DCC,AO_CODE_FOURNISSEUR,AO_CODE_ARTICLE,max(AO_REPONSE_PRIX_UNITAIRE) as AO_REPONSE_PRIX_UNITAIRE ,max(AO_REPONSE_DATE) as AO_REPONSE_DATE
into #T
from <PREFIXE_DW>AO_LIGNES_DETAIL a
join <PREFIXE_DW>DCC_LIGNES d on a.AO_CODE_FOURNISSEUR=d.DCC_CODE_FOURNISSEUR and a.AO_CODE_ARTICLE=d.DCC_CODE_ARTICLE and a.AO_NUMERO_DCC=d.DCC_NUMERO_DCC
where AO_TYPE_TRANSACTION='AO_REPONSE' 
group by AO_NUMERO_DCC,AO_CODE_FOURNISSEUR,AO_CODE_ARTICLE
order by AO_CODE_FOURNISSEUR,AO_CODE_ARTICLE

update d set DCC_PRIX_FOURNISSEUR_RETENU_AO=t.AO_REPONSE_PRIX_UNITAIRE
from <PREFIXE_DW>DCC_LIGNES d,#T t 
where d.DCC_CODE_FOURNISSEUR=t.AO_CODE_FOURNISSEUR and d.DCC_CODE_ARTICLE=t.AO_CODE_ARTICLE and d.DCC_NUMERO_DCC=t.AO_NUMERO_DCC

<SEPARATEUR>
/*<VERSION_MINIMUM>10.32ai</VERSION_MINIMUM>*/
/*Recherche dans les cdes si non trouvé dans les AO*/
select  DOC_NUMERO_DCC,DOC_CODE_FOURNISSEUR,DOC_CODE_ARTICLE,max(DOC_PRIX_UNITAIRE_CDE) as DOC_PRIX_UNITAIRE ,max(DOC_DATE_CREATION_CDE) as DOC_DATE
into #T10
from <PREFIXE_DW>DOCUMENTS a
join <PREFIXE_DW>DCC_LIGNES d on a.DOC_CODE_FOURNISSEUR=d.DCC_CODE_FOURNISSEUR and a.DOC_CODE_ARTICLE=d.DCC_CODE_ARTICLE and a.DOC_NUMERO_DCC=d.DCC_NUMERO_DCC
where DOC_TYPE_TRANSACTION='CA' 
group by DOC_NUMERO_DCC,DOC_CODE_FOURNISSEUR,DOC_CODE_ARTICLE
order by DOC_CODE_FOURNISSEUR,DOC_CODE_ARTICLE

update d set DCC_PRIX_FOURNISSEUR_RETENU_CDE=t.DOC_PRIX_UNITAIRE
from <PREFIXE_DW>DCC_LIGNES d,#T10 t 
where d.DCC_CODE_FOURNISSEUR=t.DOC_CODE_FOURNISSEUR and d.DCC_CODE_ARTICLE=t.DOC_CODE_ARTICLE and d.DCC_NUMERO_DCC=t.DOC_NUMERO_DCC 

<SEPARATEUR>
/*<VERSION_MINIMUM>10.32ai</VERSION_MINIMUM>*/
/*DCC_PRIX_FOURNISSEUR_RETENU_ACC*/
update d set DCC_PRIX_FOURNISSEUR_RETENU_ACC=t.ACC_PRIX_UNITAIRE
from <PREFIXE_DW>DCC_LIGNES d,<PREFIXE_DW>ACCORD_CADRE t 
where d.DCC_CODE_FOURNISSEUR=t.ACC_CODE_FOURNISSEUR and d.DCC_CODE_ARTICLE=t.ACC_CODE_ARTICLE and d.DCC_NUMERO_DCC=t.ACC_NUMERO_DCC 

<SEPARATEUR>
/*DCC_NOMBRE_FOURNISSEURS_CONSULTES*/
select  a.AO_NUMERO_DCC,AO_CODE_ARTICLE ,count(distinct  AO_CODE_FOURNISSEUR) as  NB
into #T2
from <PREFIXE_DW>AO_LIGNES_DETAIL a
where AO_NUMERO_DCC>0  
group by a.AO_NUMERO_DCC,AO_CODE_ARTICLE
order by AO_NUMERO_DCC

update d set DCC_NOMBRE_FOURNISSEURS_CONSULTES=t.NB
from <PREFIXE_DW>DCC_LIGNES d,#T2 t 
where d.DCC_NUMERO_DCC=t.AO_NUMERO_DCC and d.DCC_CODE_ARTICLE=t.AO_CODE_ARTICLE

<SEPARATEUR>
select  a.AO_NUMERO_DCC,AO_CODE_ARTICLE ,count(distinct  AO_CODE_FOURNISSEUR) as  NB
into #T3
from <PREFIXE_DW>AO_LIGNES_DETAIL a
where AO_NUMERO_DCC>0  and AO_TYPE_TRANSACTION='AO_REPONSE'
group by a.AO_NUMERO_DCC,AO_CODE_ARTICLE
order by AO_NUMERO_DCC

update d set DCC_NOMBRE_REPONSES_FOURNISSEURS =t.NB
from <PREFIXE_DW>DCC_LIGNES d,#T3 t 
where d.DCC_NUMERO_DCC=t.AO_NUMERO_DCC and d.DCC_CODE_ARTICLE=t.AO_CODE_ARTICLE