/* =========================================================================================== */
/* Transfert BR - Phase 2 - Update des lignes pr�sentes dans DW, � partir des valeurs de HA+   */
/* =========================================================================================== */
update doc
set 
/* En-t�te */
DOC_DATE_CREATION_BR         = CASE WHEN (ent.DATE_CRE = ' ' OR ent.DATE_CRE = '00000000' ) THEN NULL WHEN (ent.DATE_CRE < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,ent.DATE_CRE ,103) END,
DOC_DATE_PERIODE_BR          = CASE WHEN (ent.DATE_PER = ' ' OR ent.DATE_PER = '00000000' ) THEN NULL WHEN (ent.DATE_PER < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,ent.DATE_PER ,103) END,
DOC_DATE_PERIODE             = CASE WHEN (ent.DATE_PER = ' ' OR ent.DATE_PER = '00000000' ) THEN NULL WHEN (ent.DATE_PER < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,ent.DATE_PER ,103) END,
   /* DOC_ID_DATE : Date r�f�rence = Date p�riode */
DOC_ID_DATE                  = CASE WHEN (ent.DATE_PER = ' ' OR ent.DATE_PER = '00000000' ) THEN NULL WHEN (ent.DATE_PER < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,ent.DATE_PER ,103) END, 
DOC_DATE_VALIDATION_BR       = CASE WHEN (ent.DATE_VAL = ' ' OR ent.DATE_VAL = '00000000' ) THEN NULL WHEN (ent.DATE_VAL < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,ent.DATE_VAL ,103) END,
DOC_DATE_BON_LIVRAISON       = CASE WHEN (ent.DATE_BL  = ' ' OR ent.DATE_BL  = '00000000' OR isdate(DATE_BL)=0) THEN NULL WHEN (ent.DATE_BL  < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,ent.DATE_BL  ,103) END,
DOC_NUMERO_BON_LIVRAISON     = ent.BON_LIVR, 
DOC_CODE_SOCIETE             = ent.CODE_STE,
DOC_TAUX_DEVISE              = case when (ent.VAL_DEV<>0) then ent.VAL_DEV else 1 end, /* Taux devise du BR (DOC_DEVISE_BR) : celui de la Cde li�e, voir requ�te OXADW_DOC_UPDBLOC.SQL */
/* Lignes */
/*DOC_COMPTEUR_LIG             = convert(varchar(9),lig.NUM_BR) + '-' + convert(varchar(3),lig.NO_PO_BR) + '-' + '000',  Pour compteur lignes : P0?, encore utile? */
DOC_NUMERO_CDE               = lig.NUM_CDE, 
DOC_NUMERO_LIGNE_CDE         = lig.NO_PO_CDE,
DOC_DESIGNATION_ARTICLE_BR   = replace(replace(lig.DESIGN,char(10),' ') ,char(13),''),
DOC_VALIDITE_BON_LIVRAISON   = CASE WHEN lig.BR_VAL = 0x54 THEN 'OUI' ELSE 'NON' END, 
   /* Qt�s du BR, dans l'unit� de la ligne de Cde*/
DOC_QUANTITE_ACCEPTEE_BR     = lig.QTE_ACCEP, 
DOC_QUANTITE_RECUE_BR        = lig.QTE_RECUE, 
DOC_QUANTITE_COMPTEE_BR      = lig.QTE_COMPT, 
DOC_CODE_ARTICLE             = lig.CODE_ART,
DOC_RECEPTION_EN_MONTANT_BR  = CASE WHEN lig.EN_MNT = 0x54 THEN 1 ELSE 0 END,
DOC_MONTANT_LIGNE_BR         = CASE WHEN (lig.EN_MNT = 0x54 and ent.VAL_DEV<>0 and ent.TYP_BR='R') THEN lig.MNT_REC*ent.VAL_DEV when (lig.EN_MNT = 0x54 and ent.VAL_DEV=0 and ent.TYP_BR='R') THEN lig.MNT_REC when (lig.EN_MNT = 0x54 and ent.VAL_DEV=0 and ent.TYP_BR='A') THEN lig.MNT_RET when (lig.EN_MNT = 0x54 and ent.VAL_DEV<>0 and ent.TYP_BR='A') THEN lig.MNT_RET*ent.VAL_DEV else 0 END,
DOC_MONTANT_LIGNE_BR_DEV     = CASE WHEN (lig.EN_MNT = 0x54 and ent.TYP_BR='R') THEN lig.MNT_REC when (lig.EN_MNT = 0x54 and ent.TYP_BR='A') THEN lig.MNT_RET else 0 END,
DOC_TYPE_BR                  = ent.TYP_BR,
DOC_QUANTITE_RETOURNEE_BR    = lig.QTERETOUR,
DOC_DATE_LIVRAISON           = ent.DATE_CRE,
DOC_DATEMODI                 = lig.DATEMODI
/* Remise � blanc des CS, avant la m�j ult�rieure (en cas de cght du param�trage) */
/*DOC_CS01   = ' ',    
DOC_CS01_L = ' ',
DOC_CS02   = ' ',    
DOC_CS02_L = ' ',
DOC_CS03   = ' ',    
DOC_CS03_L = ' ',
DOC_CS04   = ' ',    
DOC_CS04_L = ' ',
DOC_CS05   = ' ',    
DOC_CS05_L = ' ',
DOC_CS06   = ' ',    
DOC_CS06_L = ' ',
DOC_CS07   = ' ',    
DOC_CS07_L = ' ',
DOC_CS08   = ' ',    
DOC_CS08_L = ' ',
DOC_CS09   = ' ',    
DOC_CS09_L = ' ',
DOC_CS10   = ' ',    
DOC_CS10_L = ' ' */
/* Tables */
from <PREFIXE_DW>DOCUMENTS doc,  
     <PREFIXE_HA>BR_LIGNE<SUFFIXE_HA> lig <WITHNOLOCK>, <PREFIXE_HA>BR_RECEP<SUFFIXE_HA> ent <WITHNOLOCK>
where 
/* Crit�res */
    doc.DOC_TYPE_TRANSACTION     = 'BR' 
and doc.DOC_NUMERO_DOC           = lig.NUM_BR
and doc.DOC_NUMERO_LIGNE_1       = lig.NO_PO_BR
and doc.DOC_NUMERO_LIGNE_2       = 0
/* Jointure interne lig/ent */
and lig.NUM_BR    = ent.NUM_BR
/* Diff�rentiel */
and lig.DATEMODI >= '<DATEMODIMIN>' and lig.DATEMODI <= '<DATEMODIMAX>'
