/*<OXADWDB>*/
/*req only on OXADWDB DB*/

/*==============SPE091=============Spec 4003*/
if  exists (select * from sysobjects where name='INVENTAIRES' and xtype='U') drop table INVENTAIRES
<SEPARATEUR>
/*Ajout  inv*/
select i.NO_INV as INV_NUMERO_INVENTAIRE,SITE as INV_CODE_SITE,DTE_VAL as INV_DATE_VALIDITE,CODE_ART as INV_CODE_ARTICLE,ART_DESIGNATION_ARTICLE as INV_DESIGNATION_ARTICLE,0 as INV_NUMERO_COMMANDE,0 as INV_NUMERO_LIGNE,9999999999.99999 as INV_PRIX_UNITAIRE_CDE,9999999999.99999 as INV_PRIX_UNITAIRE_STOCK,9999999999.99999 as INV_PRIX_UNITAIRE_ART_FOUR,9999999999.99999 as INV_PRIX_UNITAIRE_ARTICLE,replicate(' ',15) as INV_CODE_FOURNISSEUR,replicate(' ',120) as INV_RS_FOURNISSEUR,CAST(NULL AS DATE) as INV_DATE_VALIDATION_COMMANDE 
into INVENTAIRES  from  <PREFIXE_HA>SCIINVLI i
left outer join ARTICLES a on a.ART_CODE_ARTICLE=i.CODE_ART
left outer join <PREFIXE_HA>SINVENTA e on e.NO_INV=i.NO_INV
--where NO_INV in (select top 1 NO_INV from <PREFIXE_HA>SINVENTA order by NO_INV desc)

<SEPARATEUR>
/*Calcul dernier N° de cde*/
select  INVENTAIRES.INV_CODE_ARTICLE, req2.*
into #tabletemp1
from INVENTAIRES,
(select CODE_ART,max(NO_CDE) as NO_CDE
from <PREFIXE_HA>CA_LIGNE
group by CODE_ART) req2
where INVENTAIRES.INV_CODE_ARTICLE=req2.CODE_ART 

update INVENTAIRES  set INV_NUMERO_COMMANDE=
(select top 1 t1.NO_CDE from #tabletemp1 t1  where INVENTAIRES.INV_CODE_ARTICLE = t1.CODE_ART ) 
where INV_CODE_ARTICLE in (select CODE_ART from #tabletemp1 )

<SEPARATEUR>
/*Calcul dernier N° ligne de cde*/
select  INVENTAIRES.INV_CODE_ARTICLE, req2.*
into #tabletemp2
from INVENTAIRES,
(select CODE_ART,max(NO_CDE) as NO_CDE,   max(NO_PO) as NO_PO
from <PREFIXE_HA>CA_LIGNE
group by CODE_ART,NO_CDE) req2
where INVENTAIRES.INV_CODE_ARTICLE=req2.CODE_ART and INV_NUMERO_COMMANDE=req2.NO_CDE

update INVENTAIRES  set INV_NUMERO_LIGNE=
(select top 1 t1.NO_PO from #tabletemp2 t1  where INVENTAIRES.INV_CODE_ARTICLE = t1.CODE_ART and INV_NUMERO_COMMANDE=t1.NO_CDE ) 
where INV_CODE_ARTICLE in (select CODE_ART from #tabletemp2 )

<SEPARATEUR>
/*Recherche Code fou*/
update t set t.[INV_CODE_FOURNISSEUR]=e.CODE_FOU,
INV_DATE_VALIDATION_COMMANDE=CASE WHEN (e.DTE_VALID = ' ' OR e.DTE_VALID = '00000000' ) THEN NULL WHEN (e.DTE_VALID < '17530101') THEN CONVERT (date,'19000101' ,103) ELSE CONVERT (date,e.DTE_VALID ,103) END
from INVENTAIRES t,<PREFIXE_HA>CA_ENTET e
where t.[INV_NUMERO_COMMANDE]=e.NO_CDE

<SEPARATEUR>
/*RAZ PU cde*/
update INVENTAIRES set [INV_PRIX_UNITAIRE_CDE]=0
update INVENTAIRES set [INV_PRIX_UNITAIRE_STOCK]=0
update INVENTAIRES set [INV_PRIX_UNITAIRE_ARTICLE]=0
update INVENTAIRES set [INV_PRIX_UNITAIRE_ART_FOUR]=0

<SEPARATEUR>
/*Recherche PU cde*/
update t set t.[INV_PRIX_UNITAIRE_CDE]=l.PU
from INVENTAIRES t,<PREFIXE_HA>CA_LIGNE l
where t.[INV_NUMERO_COMMANDE]=l.NO_CDE and t.[INV_NUMERO_LIGNE]=l.NO_PO

/*Recherche PU Stock local*/
update t set t.[INV_PRIX_UNITAIRE_STOCK]=l.STL_VALEUR
from INVENTAIRES t,STOCK_LOCAL l
where t.INV_CODE_ARTICLE=l.STL_CODE_ARTICLE and t.INV_CODE_SITE=l.STL_CODE_SITE

/*Recherche PU Article*/
update t set t.[INV_PRIX_UNITAIRE_ARTICLE]=l.ART_COUT_STD
from INVENTAIRES t,ARTICLES l
where t.INV_CODE_ARTICLE=l.ART_CODE_ARTICLE

/*Recherche PU Article fournisseur*/
update t set t.[INV_PRIX_UNITAIRE_ART_FOUR]=af.AFR_PRIX_UNITAIRE
from INVENTAIRES t,ARTICLES l,ARTICLES_FOURNISSEURS af
where t.INV_CODE_FOURNISSEUR=l.ART_S_FOURNISSEUR_1 and t.INV_CODE_ARTICLE=af.AFR_CODE_ARTICLE and l.ART_S_FOURNISSEUR_1=af.AFR_CODE_FOURNISSEUR

<SEPARATEUR>
/*Recherche RS fou*/
update t set t.[INV_RS_FOURNISSEUR]=f.RS
from INVENTAIRES t,<PREFIXE_HA>FOURNISS f
where t.[INV_CODE_FOURNISSEUR]=f.CODE

