/*<OXADWDB>*/
/*req only on OXADWDB DB*/
/* Date d'impression des Cdes : cr�ation si besoin */
if not exists (SELECT *   FROM INFORMATION_SCHEMA.COLUMNS    WHERE 'DOCUMENTS' = TABLE_NAME and COLUMN_NAME='DOC_DATE_IMP_CDE')
ALTER TABLE DOCUMENTS ADD DOC_DATE_IMP_CDE datetime NULL

<SEPARATEUR>
/* Date d'impression des Cdes : MAJ (09/2025) */
update doc set DOC_DATE_IMP_CDE=evt.EVE_DATE_CREATION
	from <PREFIXE_DW>DOCUMENTS doc, <PREFIXE_DW>EVENEMENTS evt ,<PREFIXE_HA>CA_ENTET ent 
	where doc.DOC_NUMERO_CDE=ent.NO_CDE and ent.EVE_IMP=evt.EVE_NUMERO_ORDRE and evt.EVE_TYPE_TRANSACTION='CA' and evt.EVE_NUMERO_DOCUMENT=doc.DOC_NUMERO_CDE
	and isnull(DOC_DATE_IMP_CDE,'')<>evt.EVE_DATE_CREATION
    
<SEPARATEUR>
/* Date d'impression des Cdes si IMP non trouv�*/
update <PREFIXE_DW>DOCUMENTS 
set
DOC_DATE_IMP_CDE=
(
select TOP 1 EVE_DATE_CREATION from EVENEMENTS E where 
EVE_TYPE_EVENEMENT='CDE' 
and EVE_NUMERO_DOCUMENT=DOCUMENTS.DOC_NUMERO_CDE
and (EVE_CODE_EVENEMENT = 'FAX'  )
and EVE_NUMERO_ORDRE>
(select TOP 1 EVE_NUMERO_ORDRE from <PREFIXE_DW>EVENEMENTS 
where
EVE_CODE_EVENEMENT in ('VAL')
and EVE_TYPE_EVENEMENT='CDE' 
and EVE_NUMERO_DOCUMENT=DOCUMENTS.DOC_NUMERO_CDE
order by EVE_NUMERO_ORDRE )
)
where DOC_TYPE_TRANSACTION='CA'
and DOC_DATEMODI>='<DATEMODIMIN>' and DOC_DATEMODI<='<DATEMODIMAX>'
and DOC_DATE_IMP_CDE is NULL

<SEPARATEUR>
/* Date d'impression des Cdes si IMP/FAX non trouv�*/
update DOCUMENTS 
set
DOC_DATE_IMP_CDE=
(
select TOP 1 EVE_DATE_CREATION from EVENEMENTS E where 
EVE_TYPE_EVENEMENT='CDE' 
and EVE_NUMERO_DOCUMENT=DOCUMENTS.DOC_NUMERO_CDE
and (EVE_CODE_EVENEMENT = 'MEL'  )
and EVE_NUMERO_ORDRE>
(select TOP 1 EVE_NUMERO_ORDRE from EVENEMENTS 
where
EVE_CODE_EVENEMENT in ('VAL')
and EVE_TYPE_EVENEMENT='CDE' 
and EVE_NUMERO_DOCUMENT=DOCUMENTS.DOC_NUMERO_CDE
order by EVE_NUMERO_ORDRE )
)
where DOC_TYPE_TRANSACTION='CA'
and DOC_DATEMODI>='<DATEMODIMIN>' and DOC_DATEMODI<='<DATEMODIMAX>'
and DOC_DATE_IMP_CDE is NULL

<SEPARATEUR>
/* Date de signature des DA : utilisation du Num EVT de DA_ENTET 04/2014 */
update DOCUMENTS set DOC_DATE_SIGNATURE_DA = EVE_DATE_CREATION
from DOCUMENTS doc, EVENEMENTS eve, <PREFIXE_HA>DA_ENTET e
where DOC_NUMERO_DA=NO_DA and DOC_NUMERO_DA=EVE_NUMERO_DOCUMENT and EVE_SIG=EVE_NUMERO_ORDRE and EVE_TYPE_EVENEMENT='DA'
and SIGNEE=0x54
and isnull(DOC_DATE_SIGNATURE_DA,'') <> isnull(EVE_DATE_CREATION,'')

<SEPARATEUR>
/* Date de signature des CA : utilisation du N� EVT de CA_ENTET 04/2014 */
update DOCUMENTS set DOC_DATE_SIGNATURE_CDE = EVE_DATE_CREATION
from DOCUMENTS doc, EVENEMENTS eve, <PREFIXE_HA>CA_ENTET e
where DOC_NUMERO_DOC= NO_CDE and DOC_NUMERO_DOC=EVE_NUMERO_DOCUMENT and EVE_SIG=EVE_NUMERO_ORDRE and EVE_TYPE_EVENEMENT='CDE'
and DOC_TYPE_TRANSACTION='CA' and SIGNEE=0x54
and isnull(DOC_DATE_SIGNATURE_CDE,'') <> isnull(EVE_DATE_CREATION,'')

<SEPARATEUR>
/* Date de signature des Factures ,  09/2025*/
with cte1 (NumFac,DateCreation) as  (select max(e.NUM_FACT) as NumFac,max(EVE_DATE_CREATION) as DateCreation  from EVENEMENTS,<PREFIXE_HA>FAC_ENT e where NUM_FACT=EVE_NUMERO_DOCUMENT and EVE_NUMERO_DOCUMENT=NUM_FACT and EVE_TYPE_EVENEMENT='FAC' and EVE_CODE_EVENEMENT='SIB' and BON_A_PAY=0x54 and ATT_SIG=0x46 group by EVE_NUMERO_DOCUMENT )
update doc set DOC_DATE_SIGNATURE_FAC=c.DateCreation
	from DOCUMENTS doc, cte1 c where DOC_NUMERO_FAC=c.NumFac

<SEPARATEUR>
/*DOC_DATE_TRAITEMENT_DA*/
update DOCUMENTS set DOC_DATE_TRAITEMENT_DA=
(select top 1 max(EVE_DATE_CREATION)  from EVENEMENTS where DOC_NUMERO_DA=EVE_NUMERO_DOCUMENT and EVE_TYPE_EVENEMENT='DA'  and EVE_CODE_EVENEMENT='DAT' and DOC_NUMERO_DA>0 group by EVE_NUMERO_DOCUMENT,EVE_DATE_CREATION order by EVE_DATE_CREATION desc )
from EVENEMENTS,DOCUMENTS  where DOC_NUMERO_DA=EVE_NUMERO_DOCUMENT and EVE_TYPE_EVENEMENT='DA' and EVE_CODE_EVENEMENT='DAT' and DOC_NUMERO_DA>0

<SEPARATEUR>
/* Re DATE_SOLDE_FINANCIER_CDE : MAJ 2015 */
update DOCUMENTS set DOC_DATE_SOLDE_FINANCIER_CDE=
(select max(EVE_DATE_CREATION)  from EVENEMENTS,<PREFIXE_HA>CA_ENTET where DOC_NUMERO_CDE=EVE_NUMERO_DOCUMENT and EVE_NUMERO_DOCUMENT=NO_CDE
and EVE_TYPE_EVENEMENT='CDE' and EVE_CODE_EVENEMENT in ('SFA', 'SFM') group by EVE_NUMERO_DOCUMENT )
from EVENEMENTS,DOCUMENTS  where DOC_NUMERO_CDE=EVE_NUMERO_DOCUMENT and DOC_ETAT_FINANCIER='F'

<SEPARATEUR>
/*09/2019 Spec 4252 Date du dernier MEL*/
WITH cte1 (TypeTrans,NumCde,DateEvt) AS (select max(EVE_TYPE_TRANSACTION),max(EVE_NUMERO_DOCUMENT) ,max(EVE_DATE_CREATION)  from <PREFIXE_DW>EVENEMENTS
where EVE_CODE_EVENEMENT='MEL' and EVE_TYPE_TRANSACTION='CA'
group by EVE_TYPE_TRANSACTION,EVE_NUMERO_DOCUMENT)

update doc set DOC_DATE_MEL_CDE= c.DateEvt
	from <PREFIXE_DW>DOCUMENTS doc, cte1 c where DOC_TYPE_TRANSACTION=TypeTrans and DOC_NUMERO_DOC=NumCde
	and  DOC_DATE_MEL_CDE<> DateEvt