/* 
    Export des factures vers Talentia 
    - Spec 4936 - SOGEDO
    - Spec 5050 pour changement de destination des axes (SI devient CDC, CDC devient SCA, SCA devient SI)
    - Evol Dossier 305218 sur uk_numero_ordre

*/
/*<DELIMITEUR>;</DELIMITEUR>*/
/*<HEADER>0</HEADER>*/

/*
-- Uniquement en DEV
declare 
	@v_TYPETRAITEMENT	char(1)		= 'E', 
	@v_CODEOBJET		varchar(30) = 'FAC_ENT', 
	@v_CODEINTERFACE	varchar(30) = 'FACTURES', -- 'FACTURE' (RAenv8ou10), 'FACTURES' (base test 199)
	@v_NUM_JOUR			int			= 5           -- 4 (RAenv8), 5 (RAenv10, base test 199)
*/

/* Drop tmp tables */
if exists (select * from sysobjects where name='xxTABLExx' and xtype='U') drop table xxTABLExx
if exists (select * from sysobjects where name='xxTABLE2xx' and xtype='U') drop table xxTABLE2xx


/*<SEPARATEUR>*/
/* 
    REQ_1 : Liste des Lignes HT des factures, dans la table 1 (xxTABLExx) 
        - Cas des lignes hors immo (le SI n'est pas de type Invest, donc on a tous les axes)
*/

declare /* CS paramétrés sur les Sociétés */
    @v_NCS_SOC_1 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_SOCIETE_CODE_STE'), 0) /* Code société TALENTIA */

declare 
    @v_NCS_CDC_1 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_CODECOMP_ACRS'), 0), 
    @v_NCS_CDC_2 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_CODECOMP_REGION'), 0), 
    @v_NCS_CDC_3 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_CODECOMP_DEPARTEMENT'), 0), 
    @v_NCS_CDC_4 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_CODECOMP_CENTRE'), 0) 

declare 
    @v_NCS_TW_3 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_RUBRIQUE_ACRS'), 0), 
    @v_NCS_TW_5 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_RUBRIQUE_TYPE_CONTRAT'), 0)

/* Lecture N° du CS "Taux de récupération de TVA" paramétré sur la table des lignes de facture */
declare @v_NCS_TauxRecupTVA_1 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_FAC_LIG_TAUX_RECUP_TVA'), 0)

select distinct 

    1/*<NUMJOUR>*/      as lot_integration, -- N° du journal Oxalys
    MAX(ISNULL(cs_soc_1.VALEUR,'')) as ufk_societe,  
    replicate(' ', 30)  as ufk_origine,     -- Voir REQ_A (CS sur sous-famille)
    e.NUM_FACT          as uk_numero_piece, 
    1                   as fk_categorie_piece, 

    MAX(substring(e.DATE_PER,7,2)   +'/'+substring(e.DATE_PER,5,2)   +'/'+substring(e.DATE_PER,1,4))    as date_comptable,  -- DD/MM/YYYY
    MAX(substring(e.DATE_FAFO,7,2)  +'/'+substring(e.DATE_FAFO,5,2)  +'/'+substring(e.DATE_FAFO,1,4))   as date_piece,      -- DD/MM/YYYY 

    replicate(' ', 1)                                   as fk_classe_ecriture,  
    case when MAX(e.TYPE) = 'A' then '2' else '1' end   as fk_type_piece,  
    MAX(l.TYPE_TVA)                                     as fk_regime_tva, 
    0                                                   as uk_numero_ordre,     -- Voir REQ_J
    --l.CODE_SCA                                          as fk_compte, 
    --case when left(l.CODE_SCA,1) = '4' then MAX(e.CODE_FOUR) else l.CODE_SCA end                      as fk_compte, 
    case when left(l.CODE_SI,1) = '4' then MAX(e.CODE_FOUR) else l.CODE_SI end                          as fk_compte, 
    replicate(' ', 30)                                  as fk_type_tiers,       -- Voir REQ_B (CS sur fournisseur)
    replicate(' ', 30)                                  as fk_centralisateur,   -- Voir REQ_C (CS sur fournisseur, ou fk_compte)
    cast(MAX(e.CODE_FOUR) as varchar(30))               as libelle_ecriture,    -- Voir REQ_D (ajout de la RS sur 30 car max)
    right(rtrim(MAX(e.FACT_FOU)),20)                    as reference_lettrage,

    --cast( ABS(SUM(l.MONTANT * (case when e.TX_DEVISE <> 0 then e.TX_DEVISE else 1 end) )) as decimal(15,2) ) as montant_m1, -- En devise BDD  

    /* Si récup de TVA : on ajoute au HT la partie de TVA non récupérée */
    cast( ABS(SUM( 
            (
                l.MONTANT 
                + (case when (isnull(cs_tx_recup_tva.VALEUR,'') <> '' and isnumeric(isnull(cs_tx_recup_tva.VALEUR,'')) = 1) then (l.MT_TVA - l.MT_TVA * cast(cs_tx_recup_tva.VALEUR as decimal(4,2))/100 ) else 0 end) 
            )
            * (case when e.TX_DEVISE <> 0 then e.TX_DEVISE else 1 end) 
        )) as decimal(15,2) )                           as montant_m1, -- En devise BDD  

    case when MAX(e.TYPE) = 'A' then -1 else 1 end      as fk_sens,             -- 1 = Débit, -1 = Crédit 
    replicate(' ', 5)                                   as fk_mode_reglement, 
    
    --MAX(l.TYPE_TVA)                                     as fk_nature_tva,     -- Avant Modif. du 17/07/2025    
    MAX(case 
        when e.TOTAL_HT = e.TOTAL_TTC and pays.PAYSCEE = 0x54 and frs.CODE_PAYS <> 'FR' then '4'  
        when e.TOTAL_HT = e.TOTAL_TTC and frs.CODE_PAYS = 'FR'                          then '6'
        else '2' 
    end)                                                as fk_nature_tva,       -- Depuis Modif. du 17/07/2025 et du 07/08/2025  
    
    replicate(' ', 2)                                   as fk_code_tva,         -- Voir REQ_I Transcodification du taux de TVA
    replicate(' ', 1)                                   as date_specifique,             
    e.NUM_FACT                                          as identifiant_externe, 

    replicate(' ', 1)                                   as vide_24, 
    replicate(' ', 1)                                   as vide_25, 
    replicate(' ', 1)                                   as vide_26, 
    replicate(' ', 1)                                   as vide_27, 
    replicate(' ', 1)                                   as vide_28, 
    replicate(' ', 1)                                   as vide_29, 
    replicate(' ', 1)                                   as vide_30, 
    replicate(' ', 1)                                   as vide_31, 
    replicate(' ', 1)                                   as vide_32, 
    replicate(' ', 20)                                  as zone_specifique3,        -- Retour du 10/06 
    replicate(' ', 1)                                   as vide_34, 
    replicate(' ', 1)                                   as vide_35, 

    MAX(substring(e.DATE_FAFO,7,2)  +'/'+substring(e.DATE_FAFO,5,2)  +'/'+substring(e.DATE_FAFO,1,4))   as date_depart_echeance,    -- DD/MM/YYYY, idem date_piece
    MAX(substring(e.DTAE_ECH,7,2)   +'/'+substring(e.DTAE_ECH,5,2)   +'/'+substring(e.DTAE_ECH,1,4))    as date_echeance,           -- DD/MM/YYYY
    
    --right(rtrim(MAX(e.FACT_FOU)),20)                    as reference_tiers,         -- idem reference_lettrage
    cast(' ' as varchar(30))                            as reference_tiers,         -- idem libelle_ecriture (voir REQ_D2, depuis Dossier 305617)

    replicate(' ', 30)                                  as fk_type_de_tiers_regl,   -- Voir REQ_B (CS sur fournisseur) idem fk_type_tiers, et REQ_F (que si le chp suivant est renseigné )
    replicate(' ', 30)                                  as fk_tiers_reglement,      -- Voir REQ_E (CS sur en-tête)
    replicate(' ', 1)                                   as fk_fiche_tiers_reglement, -- Vide 
    --case when MAX(e.BON_A_PAY) = 0x54 then 'OK' else '' end as fk_code_bon_a_payer, 
    ''                                                  as fk_code_bon_a_payer, 

    replicate(' ', 1)                                   as vide_43, 
    replicate(' ', 1)                                   as vide_44, 
    replicate(' ', 1)                                   as vide_45, 
    replicate(' ', 1)                                   as vide_46, 
    replicate(' ', 1)                                   as vide_47, 
    replicate(' ', 1)                                   as vide_48, 
    replicate(' ', 1)                                   as vide_49, 
    replicate(' ', 1)                                   as vide_50, 
    replicate(' ', 1)                                   as vide_51, 
    replicate(' ', 1)                                   as vide_52, 
    replicate(' ', 1)                                   as vide_53, 

    MAX(ISNULL(si.CODE_DIR,''))                         as fk_rubrique,                 -- Pas d'analytique sur les comptes d'immo (donc si si.INVEST=0x54)
    l.CODE_SCA                                          as fk_axe0, 
    l.CODE_CDC                                          as fk_axe1, 
    l.CODE_SCA + MAX(ISNULL(si.CODE_DIR,''))            as fk_axe2,                     -- Cette concaténation permet aussi de récupérer la ligne souhaitée dans la table spécifique RUBRIQUE (ex-TW_TALENTIA) (utile pour certains CS plus bas)
    MAX(ISNULL(cs_tw_3.VALEUR,''))                      as fk_axe3,                     -- CS n°3 de la table RUBRIQUE  (ex-TW_TALENTIA)
    MAX(ISNULL(cs_cdc_1.VALEUR,''))                      as fk_axe4,                     -- CS n°1 de la table CDC
    replicate(' ', 60)                                  as fk_axe5,                     -- Voir REQ_G (fk_axe3 + fk_axe4, 60 car)
    MAX(ISNULL(cs_tw_5.VALEUR,''))                      as fk_axe6,                     -- CS n°5 de la table RUBRIQUE  (ex-TW_TALENTIA)
    MAX(ISNULL(cs_cdc_2.VALEUR,''))                      as fk_axe7,                     -- CS n°2 de la table CDC
    MAX(ISNULL(cs_cdc_3.VALEUR,''))                      as fk_axe8,                     -- CS n°3 de la table CDC
    MAX(ISNULL(cs_cdc_4.VALEUR,''))                      as fk_axe9,                     -- CS n°4 de la table CDC

    replicate(' ', 1)                                   as vide_65, 
    replicate(' ', 1)                                   as vide_66, 
    replicate(' ', 1)                                   as vide_67, 
    replicate(' ', 1)                                   as vide_68, 
    replicate(' ', 1)                                   as vide_69, 
    replicate(' ', 1)                                   as vide_70, 
    replicate(' ', 1)                                   as vide_71, 
    replicate(' ', 1)                                   as vide_72, 
    replicate(' ', 1)                                   as vide_73, 
    replicate(' ', 1)                                   as vide_74, 
    replicate(' ', 1)                                   as vide_75, 
    replicate(' ', 1)                                   as vide_76, 
    replicate(' ', 1)                                   as vide_77, 
    replicate(' ', 1)                                   as vide_78, 
    replicate(' ', 1)                                   as vide_79, 
    replicate(' ', 1)                                   as vide_80, 
    replicate(' ', 1)                                   as vide_81, 

    replicate(' ', 255)                                 as attachment1,                 -- Voir REQ_H (URL du pdf)

    replicate(' ', 1)                                   as vide_83, 
    replicate(' ', 1)                                   as vide_84, 
    replicate(' ', 1)                                   as vide_85, 
    replicate(' ', 1)                                   as vide_86, 
    replicate(' ', 1)                                   as vide_87, 
    replicate(' ', 1)                                   as vide_88, 
    replicate(' ', 1)                                   as vide_89, 
    replicate(' ', 1)                                   as vide_90, 
    replicate(' ', 1)                                   as vide_91, 

    'AT-OXA-' + e.CODE_STE + '-' + cast(e.NUM_FACT as varchar) as auditid,

    cast( ABS(SUM( 
            l.MONTANT 
            * (case when e.TX_DEVISE <> 0 then e.TX_DEVISE else 1 end) 
        )) as decimal(15,2) )                           as AZ32_montant_sans_tx_recup_tva, -- En devise BDD  

    /* Ordre selon le group BY. 
        - 1 pour le TTC en premier, 2 pour le HT, 3 pour la TVA
    */
    e.CODE_STE + '-' + cast(e.NUM_FACT as varchar) + '-' + '2' + '-' + l.CODE_SCA + '-' + l.CODE_CDC + '-' + l.CODE_SI  + '-' + l.TYPE_TVA + '-' + cast(l.TAUX_TVA as varchar) + replicate(' ', 40) as AZ32_ORDRE, 
    MAX(ISNULL(cs_soc_1.VALEUR,''))                                                                                     as AZ32_EXPORT_KEY, 
    'HT-HORS-IMMO' + replicate(' ', 20)                                                                                 as AZ32_TYPE_ENREG, 
    e.NUM_FACT                                                                                                          as AZ32_NUM_FACT,
    e.CODE_STE                                                                                                          as AZ32_CODE_STE,  
    MAX(e.CODE_FOUR)                                                                                                    as AZ32_CODE_FOUR, 
    MAX(l.CODE_SFAM)                                                                                                    as AZ32_CODE_SFAM,  
    l.TAUX_TVA                                                                                                          as AZ32_TAUX_TVA, 
    l.CODE_SI                                                                                                           as AZ32_CODE_SI,  
    cast('' as varchar(200))                                                                                            as AZ32_TRI_POUR_UK_NUMERO_ORDRE 

into xxTABLExx 
from FAC_LIG l with (nolock) 
/* Lecture de l'en-tête de la facture */ 
left outer join FAC_ENT e with (nolock) on l.NUM_FACT = e.NUM_FACT 
/* Lecture du fournisseur */ 
left outer join FOURNISS frs with (nolock) on frs.CODE = e.CODE_FOUR 
/* Lecture du pays du fournisseur */ 
left outer join COUNTRY pays with (nolock) on pays.CODE_PAYS = frs.CODE_PAYS  
/* Lecture du SI de la ligne */ 
left outer join SECTIMPU si with (nolock) on si.CODE_SI = l.CODE_SI and si.CODE_STE = e.CODE_STE 
/* Lecture du SCA de la ligne */ 
left outer join SSCPTANA sca with (nolock) on sca.CODE_SCA = l.CODE_SCA and sca.CODE_STE = e.CODE_STE
/* Lecture des CS de la société */ 
left outer join CS_DON cs_soc_1 with (nolock) on cs_soc_1.COD_ENR = e.CODE_STE and cs_soc_1.COD_TAB = 'SOCIETE' and cs_soc_1.NUM_FIX = @v_NCS_SOC_1
/* Lecture des CS 1 à 4 du Code complémentaire (CDC) */ 
left outer join CS_DON cs_cdc_1 with (nolock) on cs_cdc_1.COD_ENR = cast(e.CODE_STE as char(7)) + l.CODE_CDC and cs_cdc_1.COD_TAB = 'CODECOMP' and cs_cdc_1.NUM_FIX = @v_NCS_CDC_1
left outer join CS_DON cs_cdc_2 with (nolock) on cs_cdc_2.COD_ENR = cast(e.CODE_STE as char(7)) + l.CODE_CDC and cs_cdc_2.COD_TAB = 'CODECOMP' and cs_cdc_2.NUM_FIX = @v_NCS_CDC_2
left outer join CS_DON cs_cdc_3 with (nolock) on cs_cdc_3.COD_ENR = cast(e.CODE_STE as char(7)) + l.CODE_CDC and cs_cdc_3.COD_TAB = 'CODECOMP' and cs_cdc_3.NUM_FIX = @v_NCS_CDC_3
left outer join CS_DON cs_cdc_4 with (nolock) on cs_cdc_4.COD_ENR = cast(e.CODE_STE as char(7)) + l.CODE_CDC and cs_cdc_4.COD_TAB = 'CODECOMP' and cs_cdc_4.NUM_FIX = @v_NCS_CDC_4
/* Lecture des CS 3 et 5 de la rubrique Talentia (rubrique = l.CODE_SCA + si.CODE_DIR, avant : l.CODE_CDC + sca.CODE_CTA) */ 
left outer join CS_DON cs_tw_3 with (nolock) on cs_tw_3.COD_ENR = cast(e.CODE_STE as char(7)) + l.CODE_SCA + si.CODE_DIR and cs_tw_3.COD_TAB = 'RUBRIQUE' and cs_tw_3.NUM_FIX = @v_NCS_TW_3
left outer join CS_DON cs_tw_5 with (nolock) on cs_tw_5.COD_ENR = cast(e.CODE_STE as char(7)) + l.CODE_SCA + si.CODE_DIR and cs_tw_5.COD_TAB = 'RUBRIQUE' and cs_tw_5.NUM_FIX = @v_NCS_TW_5 
/* Lecture du CS "Taux de récupération de TVA" de la ligne de facture */
left outer join CS_DON cs_tx_recup_tva 
    on (cs_tx_recup_tva.COD_TAB='FAC_LIG' 
        and cast(dbo.OXA_DecoupeChaineV2(cs_tx_recup_tva.COD_ENR,1,'|') as int) = l.NUM_FACT 
        and cast(dbo.OXA_DecoupeChaineV2(cs_tx_recup_tva.COD_ENR,2,'|') as int) = l.NUM_LIG 
        and cs_tx_recup_tva.NUM_FIX = @v_NCS_TauxRecupTVA_1) 

where l.NUM_FACT in (select cast(ID_1 as int) from XT_LIG with(nolock) where TYPETR='<TYPETRAITEMENT>' and TYPEOBJ='<CODEOBJET>' and CODE_INT='<CODEINTERFACE>' and NUM_JOUR=1/*<NUMJOUR>*/) 
--where l.NUM_FACT in (select cast(ID_1 as int) from XT_LIG with(nolock) where TYPETR=@v_TYPETRAITEMENT and TYPEOBJ=@v_CODEOBJET and CODE_INT=@v_CODEINTERFACE and NUM_JOUR=@v_NUM_JOUR/*<NUMJOUR>*/)
and si.INVEST = 0x46 -- Cas des lignes hors immo (le SI n'est pas de type Invest)
group by e.CODE_STE, e.NUM_FACT, l.CODE_SCA, l.CODE_CDC, l.CODE_SI, l.TYPE_TVA, l.TAUX_TVA



/*<SEPARATEUR>*/
/* 
    REQ_2 : Liste des Lignes HT des factures, dans la table 1 (xxTABLExx) 
        - Cas des lignes immo (le SI est de type Invest, donc on n'a pas les axes)
        - Changements par rapport au cas Immo : 
            - fk_rubrique, et fk_axe0 à fk_axe9 : vides
            - AZ32_TYPE_ENREG, AZ32_ORDRE
            - le group by
*/

declare /* CS paramétrés sur les Sociétés */
    @v_NCS_SOC_1_b int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_SOCIETE_CODE_STE'), 0) /* Code société TALENTIA */

/* Lecture N° du CS "Taux de récupération de TVA" paramétré sur la table des lignes de facture */
declare @v_NCS_TauxRecupTVA_2 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_FAC_LIG_TAUX_RECUP_TVA'), 0)

insert into xxTABLExx 
select distinct 

    1/*<NUMJOUR>*/      as lot_integration, -- N° du journal Oxalys
    MAX(ISNULL(cs_soc_1.VALEUR,'')) as ufk_societe,  
    replicate(' ', 30)  as ufk_origine,     -- Voir REQ_A (CS sur sous-famille)
    e.NUM_FACT          as uk_numero_piece, 
    1                   as fk_categorie_piece, 

    MAX(substring(e.DATE_PER,7,2)   +'/'+substring(e.DATE_PER,5,2)   +'/'+substring(e.DATE_PER,1,4))    as date_comptable,  -- DD/MM/YYYY
    MAX(substring(e.DATE_FAFO,7,2)  +'/'+substring(e.DATE_FAFO,5,2)  +'/'+substring(e.DATE_FAFO,1,4))   as date_piece,      -- DD/MM/YYYY 

    replicate(' ', 1)                                   as fk_classe_ecriture,  
    case when MAX(e.TYPE) = 'A' then '2' else '1' end   as fk_type_piece,  
    MAX(l.TYPE_TVA)                                     as fk_regime_tva, 
    0                                                   as uk_numero_ordre,     -- Voir REQ_J
    --l.CODE_SCA                                          as fk_compte, 
    case when left(l.CODE_SI,1) = '4' then MAX(e.CODE_FOUR) else l.CODE_SI end                           as fk_compte, 
    replicate(' ', 30)                                  as fk_type_tiers,       -- Voir REQ_B (CS sur fournisseur)
    replicate(' ', 30)                                  as fk_centralisateur,   -- Voir REQ_C (CS sur fournisseur, ou fk_compte)
    cast(MAX(e.CODE_FOUR) as varchar(30))               as libelle_ecriture,    -- Voir REQ_D (ajout de la RS sur 30 car max)
    right(rtrim(MAX(e.FACT_FOU)),20)                    as reference_lettrage,

    /* Si récup de TVA : on ajoute au HT la partie de TVA non récupérée */
    cast( ABS(SUM( 
            (
                l.MONTANT 
                + (case when (isnull(cs_tx_recup_tva.VALEUR,'') <> '' and isnumeric(isnull(cs_tx_recup_tva.VALEUR,'')) = 1) then (l.MT_TVA - l.MT_TVA * cast(cs_tx_recup_tva.VALEUR as decimal(4,2))/100 ) else 0 end) 
            )
            * (case when e.TX_DEVISE <> 0 then e.TX_DEVISE else 1 end) 
        )) as decimal(15,2) )                           as montant_m1, -- En devise BDD  
    
    case when MAX(e.TYPE) = 'A' then -1 else 1 end      as fk_sens,             -- 1 = Débit, -1 = Crédit 
    replicate(' ', 5)                                   as fk_mode_reglement, 

    --MAX(l.TYPE_TVA)                                     as fk_nature_tva,     -- Avant Modif. du 17/07/2025     
    '3'                                                 as fk_nature_tva,       -- Depuis Modif. du 17/10/2025  

    replicate(' ', 2)                                   as fk_code_tva,         -- Voir REQ_I Transcodification du taux de TVA
    replicate(' ', 1)                                   as date_specifique,             
    e.NUM_FACT                                          as identifiant_externe, 

    replicate(' ', 1)                                   as vide_24, 
    replicate(' ', 1)                                   as vide_25, 
    replicate(' ', 1)                                   as vide_26, 
    replicate(' ', 1)                                   as vide_27, 
    replicate(' ', 1)                                   as vide_28, 
    replicate(' ', 1)                                   as vide_29, 
    replicate(' ', 1)                                   as vide_30, 
    replicate(' ', 1)                                   as vide_31, 
    replicate(' ', 1)                                   as vide_32, 
    l.CODE_CDC                                          as zone_specifique3,        -- Retour du 10/06
    replicate(' ', 1)                                   as vide_34, 
    replicate(' ', 1)                                   as vide_35, 

    MAX(substring(e.DATE_FAFO,7,2)  +'/'+substring(e.DATE_FAFO,5,2)  +'/'+substring(e.DATE_FAFO,1,4))   as date_depart_echeance,    -- DD/MM/YYYY, idem date_piece
    MAX(substring(e.DTAE_ECH,7,2)   +'/'+substring(e.DTAE_ECH,5,2)   +'/'+substring(e.DTAE_ECH,1,4))    as date_echeance,           -- DD/MM/YYYY
    
--    right(rtrim(MAX(e.FACT_FOU)),20)                    as reference_tiers,         -- idem reference_lettrage
    cast(' ' as varchar(30))                            as reference_tiers,         -- idem libelle_ecriture (voir REQ_D2, depuis Dossier 305617)

    replicate(' ', 30)                                  as fk_type_de_tiers_regl,   -- Voir REQ_B (CS sur fournisseur) idem fk_type_tiers, et REQ_F (que si le chp suivant est renseigné )
    replicate(' ', 30)                                  as fk_tiers_reglement,      -- Voir REQ_E (CS sur en-tête)
    replicate(' ', 1)                                   as fk_fiche_tiers_reglement, -- Vide 
    --case when MAX(e.BON_A_PAY) = 0x54 then 'OK' else '' end as fk_code_bon_a_payer, 
    ''                                                  as fk_code_bon_a_payer, 

    replicate(' ', 1)                                   as vide_43, 
    replicate(' ', 1)                                   as vide_44, 
    replicate(' ', 1)                                   as vide_45, 
    replicate(' ', 1)                                   as vide_46, 
    replicate(' ', 1)                                   as vide_47, 
    replicate(' ', 1)                                   as vide_48, 
    replicate(' ', 1)                                   as vide_49, 
    replicate(' ', 1)                                   as vide_50, 
    replicate(' ', 1)                                   as vide_51, 
    replicate(' ', 1)                                   as vide_52, 
    replicate(' ', 1)                                   as vide_53, 

    ''                                                  as fk_rubrique,                 -- Pas d'analytique sur les comptes d'immo (donc si si.INVEST=0x54)
    ''                                                  as fk_axe0, 
    ''                                                  as fk_axe1, 
    ''                                                  as fk_axe2,                     -- Cette concaténation permet aussi de récupérer la ligne souhaitée dans la table spécifique TW_TALENTIA (utile pour certains CS plus bas)
    ''                                                  as fk_axe3,                     -- CS n°3 de la table RUBRIQUE  (ex-TW_TALENTIA)
    ''                                                  as fk_axe4,                     -- CS n°1 de la table SI
    ''                                                  as fk_axe5,                     -- Voir REQ_G (fk_axe3 + fk_axe4, 60 car)
    ''                                                  as fk_axe6,                     -- CS n°5 de la table RUBRIQUE  (ex-TW_TALENTIA)
    ''                                                  as fk_axe7,                     -- CS n°2 de la table SI
    ''                                                  as fk_axe8,                     -- CS n°3 de la table SI
    ''                                                  as fk_axe9,                     -- CS n°4 de la table SI

    replicate(' ', 1)                                   as vide_65, 
    replicate(' ', 1)                                   as vide_66, 
    replicate(' ', 1)                                   as vide_67, 
    replicate(' ', 1)                                   as vide_68, 
    replicate(' ', 1)                                   as vide_69, 
    replicate(' ', 1)                                   as vide_70, 
    replicate(' ', 1)                                   as vide_71, 
    replicate(' ', 1)                                   as vide_72, 
    replicate(' ', 1)                                   as vide_73, 
    replicate(' ', 1)                                   as vide_74, 
    replicate(' ', 1)                                   as vide_75, 
    replicate(' ', 1)                                   as vide_76, 
    replicate(' ', 1)                                   as vide_77, 
    replicate(' ', 1)                                   as vide_78, 
    replicate(' ', 1)                                   as vide_79, 
    replicate(' ', 1)                                   as vide_80, 
    replicate(' ', 1)                                   as vide_81, 

    replicate(' ', 255)                                 as attachment1,                 -- Voir REQ_H (URL du pdf)

    replicate(' ', 1)                                   as vide_83, 
    replicate(' ', 1)                                   as vide_84, 
    replicate(' ', 1)                                   as vide_85, 
    replicate(' ', 1)                                   as vide_86, 
    replicate(' ', 1)                                   as vide_87, 
    replicate(' ', 1)                                   as vide_88, 
    replicate(' ', 1)                                   as vide_89, 
    replicate(' ', 1)                                   as vide_90, 
    replicate(' ', 1)                                   as vide_91, 

    'AT-OXA-' + e.CODE_STE + '-' + cast(e.NUM_FACT as varchar) as auditid,

    cast( ABS(SUM( 
            l.MONTANT 
            * (case when e.TX_DEVISE <> 0 then e.TX_DEVISE else 1 end) 
        )) as decimal(15,2) )                           as AZ32_montant_sans_tx_recup_tva, -- En devise BDD  

    /* Ordre selon le group BY. 
        - 1 pour le TTC en premier, 2 pour le HT, 3 pour la TVA
    */
    e.CODE_STE + '-' + cast(e.NUM_FACT as varchar) + '-' + '2' + '-' + l.CODE_CDC + '-' + l.CODE_SI + '- ' + '- ' + '-' + l.TYPE_TVA + '-' + cast(l.TAUX_TVA as varchar) + replicate(' ', 20) as AZ32_ORDRE, 
    MAX(ISNULL(cs_soc_1.VALEUR,''))                                                                                     as AZ32_EXPORT_KEY, 
    'HT-IMMO' + replicate(' ', 20)                                                                                      as AZ32_TYPE_ENREG, 
    e.NUM_FACT                                                                                                          as AZ32_NUM_FACT,
    e.CODE_STE                                                                                                          as AZ32_CODE_STE,  
    MAX(e.CODE_FOUR)                                                                                                    as AZ32_CODE_FOUR, 
    MAX(l.CODE_SFAM)                                                                                                    as AZ32_CODE_SFAM,  
    l.TAUX_TVA                                                                                                          as AZ32_TAUX_TVA, 
    l.CODE_SI                                                                                                           as AZ32_CODE_SI,  
    ''                                                                                                                  as AZ32_TRI_POUR_UK_NUMERO_ORDRE 

from FAC_LIG l with (nolock) 
/* Lecture de l'en-tête de la facture */ 
left outer join FAC_ENT e with (nolock) on l.NUM_FACT = e.NUM_FACT 
/* Lecture du fournisseur */ 
left outer join FOURNISS frs with (nolock) on frs.CODE = e.CODE_FOUR 
/* Lecture du pays du fournisseur */ 
left outer join COUNTRY pays with (nolock) on pays.CODE_PAYS = frs.CODE_PAYS  
/* Lecture du SI de la ligne */ 
left outer join SECTIMPU si with (nolock) on si.CODE_SI = l.CODE_SI and si.CODE_STE = e.CODE_STE 
/* Lecture des CS de la société */ 
left outer join CS_DON cs_soc_1 with (nolock) on cs_soc_1.COD_ENR = e.CODE_STE and cs_soc_1.COD_TAB = 'SOCIETE' and cs_soc_1.NUM_FIX = @v_NCS_SOC_1_b 
/* Lecture du CS "Taux de récupération de TVA" de la ligne de facture */
left outer join CS_DON cs_tx_recup_tva 
    on (cs_tx_recup_tva.COD_TAB='FAC_LIG' 
        and cast(dbo.OXA_DecoupeChaineV2(cs_tx_recup_tva.COD_ENR,1,'|') as int) = l.NUM_FACT 
        and cast(dbo.OXA_DecoupeChaineV2(cs_tx_recup_tva.COD_ENR,2,'|') as int) = l.NUM_LIG 
        and cs_tx_recup_tva.NUM_FIX = @v_NCS_TauxRecupTVA_2) 

where l.NUM_FACT in (select cast(ID_1 as int) from XT_LIG with(nolock) where TYPETR='<TYPETRAITEMENT>' and TYPEOBJ='<CODEOBJET>' and CODE_INT='<CODEINTERFACE>' and NUM_JOUR=1/*<NUMJOUR>*/) 
--where l.NUM_FACT in (select cast(ID_1 as int) from XT_LIG with(nolock) where TYPETR=@v_TYPETRAITEMENT and TYPEOBJ=@v_CODEOBJET and CODE_INT=@v_CODEINTERFACE and NUM_JOUR=@v_NUM_JOUR/*<NUMJOUR>*/)
and si.INVEST = 0x54 -- Cas des lignes immo (le SI est de type Invest)
group by e.CODE_STE, e.NUM_FACT, l.CODE_CDC, l.CODE_SI, l.TYPE_TVA, l.TAUX_TVA



/*<SEPARATEUR>*/
/* 
    REQ_3 : Liste des Lignes de TVA des factures, dans la table 1 (xxTABLExx) 
    - Pas d'autoliquididation
    - On exclut les factures sans TVA (cas TTC = HT)
*/

declare /* CS paramétrés sur les Sociétés */
    @v_NCS_SOC_1_c int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_SOCIETE_CODE_STE'), 0) /* Code société TALENTIA */

/* Lecture N° du CS "Compte comptable 1" paramétré sur la table des profils de TVA*/
declare @v_NCS_CpteTVA int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_PROFILS_TVA_COMPTE_TVA_1'), 0)

/* Lecture N° du CS "Taux de récupération de TVA" paramétré sur la table des lignes de facture */
declare @v_NCS_TauxRecupTVA_3 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_FAC_LIG_TAUX_RECUP_TVA'), 0)

insert into xxTABLExx 
select distinct 

    1/*<NUMJOUR>*/      as lot_integration, -- N° du journal Oxalys
    MAX(ISNULL(cs_soc_1.VALEUR,'')) as ufk_societe,  
    replicate(' ', 30)  as ufk_origine,     -- Voir REQ_A (CS sur sous-famille)
    e.NUM_FACT          as uk_numero_piece, 
    1                   as fk_categorie_piece, 

    MAX(substring(e.DATE_PER,7,2)   +'/'+substring(e.DATE_PER,5,2)   +'/'+substring(e.DATE_PER,1,4))    as date_comptable,  -- DD/MM/YYYY
    MAX(substring(e.DATE_FAFO,7,2)  +'/'+substring(e.DATE_FAFO,5,2)  +'/'+substring(e.DATE_FAFO,1,4))   as date_piece,      -- DD/MM/YYYY 

    replicate(' ', 1)                                   as fk_classe_ecriture,  
    case when MAX(e.TYPE) = 'A' then '2' else '1' end   as fk_type_piece,  
    MAX(l.TYPE_TVA)                                     as fk_regime_tva, 
    0                                                   as uk_numero_ordre,     -- Voir REQ_J
    isnull(cs.VALEUR,'')                                as fk_compte,           -- Compte de TVA issu du profil de TVA
    replicate(' ', 30)                                  as fk_type_tiers,       -- Voir REQ_B (CS sur fournisseur)
    replicate(' ', 30)                                  as fk_centralisateur,   -- Voir REQ_C (CS sur fournisseur, ou fk_compte)
    cast(MAX(e.CODE_FOUR) as varchar(30))               as libelle_ecriture,    -- Voir REQ_D (ajout de la RS sur 30 car max)
    right(rtrim(MAX(e.FACT_FOU)),20)                    as reference_lettrage,

    --cast( ABS(SUM( l.MONTANT * (l.TAUX_TVA / 100) * (case when e.TX_DEVISE <> 0 then e.TX_DEVISE else 1 end) * (case when (isnull(cs_tx_recup_tva.VALEUR,'') <> '' and isnumeric(isnull(cs_tx_recup_tva.VALEUR,'')) = 1) then cast(cs_tx_recup_tva.VALEUR as decimal(3,2) ) else 1 end)  )) as decimal(15,2) ) as montant_m1, -- Montant TVA, en devise BDD  

   /* Si récup de TVA : le montant de TVA est partiel (on applique le taux de récupération) */
    cast( ABS(SUM( 
            l.MT_TVA 
            * (case when e.TX_DEVISE <> 0 then e.TX_DEVISE else 1 end) 
            * (case when (isnull(cs_tx_recup_tva.VALEUR,'') <> '' and isnumeric(isnull(cs_tx_recup_tva.VALEUR,'')) = 1) then cast(cs_tx_recup_tva.VALEUR as decimal(4,2))/100 else 1 end)  
        )) as decimal(15,2) )                           as montant_m1, -- Montant TVA, en devise BDD  
    
    case when MAX(e.TYPE) = 'A' then -1 else 1 end      as fk_sens,             -- 1 = Débit, -1 = Crédit 
    replicate(' ', 5)                                   as fk_mode_reglement, 
   
    --MAX(l.TYPE_TVA)                                     as fk_nature_tva,     -- Avant Modif. du 17/07/2025 et du 07/08/2025    
    MAX(case 
        when si.INVEST = 0x54                                                           then '3' -- Depuis Modif du 17/10/2025  
        when e.TOTAL_HT = e.TOTAL_TTC and pays.PAYSCEE = 0x54 and frs.CODE_PAYS <> 'FR' then '4'  
        when e.TOTAL_HT = e.TOTAL_TTC and frs.CODE_PAYS = 'FR'                          then '6'
        else '2' 
    end)                                                as fk_nature_tva,       -- Depuis Modif. du 17/07/2025 et du 07/08/2025  

    replicate(' ', 2)                                   as fk_code_tva,         -- Voir REQ_I Transcodification du taux de TVA
    replicate(' ', 1)                                   as date_specifique,             
    e.NUM_FACT                                          as identifiant_externe, 

    replicate(' ', 1)                                   as vide_24, 
    replicate(' ', 1)                                   as vide_25, 
    replicate(' ', 1)                                   as vide_26, 
    replicate(' ', 1)                                   as vide_27, 
    replicate(' ', 1)                                   as vide_28, 
    replicate(' ', 1)                                   as vide_29, 
    replicate(' ', 1)                                   as vide_30, 
    replicate(' ', 1)                                   as vide_31, 
    replicate(' ', 1)                                   as vide_32, 
    replicate(' ', 20)                                  as zone_specifique3,        -- Retour du 10/06 
    replicate(' ', 1)                                   as vide_34, 
    replicate(' ', 1)                                   as vide_35, 

    MAX(substring(e.DATE_FAFO,7,2)  +'/'+substring(e.DATE_FAFO,5,2)  +'/'+substring(e.DATE_FAFO,1,4))   as date_depart_echeance,    -- DD/MM/YYYY, idem date_piece
    MAX(substring(e.DTAE_ECH,7,2)   +'/'+substring(e.DTAE_ECH,5,2)   +'/'+substring(e.DTAE_ECH,1,4))    as date_echeance,           -- DD/MM/YYYY
    
--    right(rtrim(MAX(e.FACT_FOU)),20)                    as reference_tiers,         -- idem reference_lettrage
    cast(' ' as varchar(30))                            as reference_tiers,         -- idem libelle_ecriture (voir REQ_D2, depuis Dossier 305617)

    replicate(' ', 30)                                  as fk_type_de_tiers_regl,   -- Voir REQ_B (CS sur fournisseur) idem fk_type_tiers, et REQ_F (que si le chp suivant est renseigné )
    replicate(' ', 30)                                  as fk_tiers_reglement,      -- Voir REQ_E (CS sur en-tête)
    replicate(' ', 1)                                   as fk_fiche_tiers_reglement, -- Vide 
    --case when MAX(e.BON_A_PAY) = 0x54 then 'OK' else '' end as fk_code_bon_a_payer, 
    ''                                                  as fk_code_bon_a_payer, 

    replicate(' ', 1)                                   as vide_43, 
    replicate(' ', 1)                                   as vide_44, 
    replicate(' ', 1)                                   as vide_45, 
    replicate(' ', 1)                                   as vide_46, 
    replicate(' ', 1)                                   as vide_47, 
    replicate(' ', 1)                                   as vide_48, 
    replicate(' ', 1)                                   as vide_49, 
    replicate(' ', 1)                                   as vide_50, 
    replicate(' ', 1)                                   as vide_51, 
    replicate(' ', 1)                                   as vide_52, 
    replicate(' ', 1)                                   as vide_53, 

    ''                                                  as fk_rubrique,                 -- Pas d'analytique sur les lignes de TVA
    ''                                                  as fk_axe0, 
    ''                                                  as fk_axe1, 
    ''                                                  as fk_axe2,                     
    ''                                                  as fk_axe3,                     
    ''                                                  as fk_axe4,                     
    ''                                                  as fk_axe5,                     
    ''                                                  as fk_axe6,                     
    ''                                                  as fk_axe7,                     
    ''                                                  as fk_axe8,                     
    ''                                                  as fk_axe9,                     

    replicate(' ', 1)                                   as vide_65, 
    replicate(' ', 1)                                   as vide_66, 
    replicate(' ', 1)                                   as vide_67, 
    replicate(' ', 1)                                   as vide_68, 
    replicate(' ', 1)                                   as vide_69, 
    replicate(' ', 1)                                   as vide_70, 
    replicate(' ', 1)                                   as vide_71, 
    replicate(' ', 1)                                   as vide_72, 
    replicate(' ', 1)                                   as vide_73, 
    replicate(' ', 1)                                   as vide_74, 
    replicate(' ', 1)                                   as vide_75, 
    replicate(' ', 1)                                   as vide_76, 
    replicate(' ', 1)                                   as vide_77, 
    replicate(' ', 1)                                   as vide_78, 
    replicate(' ', 1)                                   as vide_79, 
    replicate(' ', 1)                                   as vide_80, 
    replicate(' ', 1)                                   as vide_81, 

    replicate(' ', 255)                                 as attachment1,                 -- Voir REQ_H (URL du pdf)

    replicate(' ', 1)                                   as vide_83, 
    replicate(' ', 1)                                   as vide_84, 
    replicate(' ', 1)                                   as vide_85, 
    replicate(' ', 1)                                   as vide_86, 
    replicate(' ', 1)                                   as vide_87, 
    replicate(' ', 1)                                   as vide_88, 
    replicate(' ', 1)                                   as vide_89, 
    replicate(' ', 1)                                   as vide_90, 
    replicate(' ', 1)                                   as vide_91, 

    'AT-OXA-' + e.CODE_STE + '-' + cast(e.NUM_FACT as varchar) as auditid,

    cast( ABS(SUM( 
            l.MT_TVA 
            * (case when e.TX_DEVISE <> 0 then e.TX_DEVISE else 1 end) 
        )) as decimal(15,2) )                           as AZ32_montant_sans_tx_recup_tva, -- Montant TVA, en devise BDD  

    /* Ordre selon le group BY. 
        - 1 pour le TTC en premier, 2 pour le HT, 3 pour la TVA
    */
    e.CODE_STE + '-' + cast(e.NUM_FACT as varchar) + '-' + '3' + '-' + isnull(cs.VALEUR,'') + '-' + l.TYPE_TVA + '-' + cast(l.TAUX_TVA as varchar) + '-' + (case when si.INVEST = 0x54 then 'INVEST' else '' end) as AZ32_ORDRE, 
    MAX(ISNULL(cs_soc_1.VALEUR,''))                                                                                     as AZ32_EXPORT_KEY, 
    'TVA'                                                                                                               as AZ32_TYPE_ENREG, 
    e.NUM_FACT                                                                                                          as AZ32_NUM_FACT,
    e.CODE_STE                                                                                                          as AZ32_CODE_STE,  
    MAX(e.CODE_FOUR)                                                                                                    as AZ32_CODE_FOUR, 
    MAX(l.CODE_SFAM)                                                                                                    as AZ32_CODE_SFAM,  
    l.TAUX_TVA                                                                                                          as AZ32_TAUX_TVA, 
    ''                                                                                                                  as AZ32_CODE_SI, 
    ''                                                                                                                  as AZ32_TRI_POUR_UK_NUMERO_ORDRE 

from FAC_LIG l with (nolock) 
/* Lecture de l'en-tête de la facture */ 
left outer join FAC_ENT e with (nolock) on l.NUM_FACT = e.NUM_FACT 
/* Lecture du fournisseur */ 
left outer join FOURNISS frs with (nolock) on frs.CODE = e.CODE_FOUR 
/* Lecture du pays du fournisseur */ 
left outer join COUNTRY pays with (nolock) on pays.CODE_PAYS = frs.CODE_PAYS  
/* Lecture du SI de la ligne */ 
left outer join SECTIMPU si with (nolock) on si.CODE_SI = l.CODE_SI and si.CODE_STE = e.CODE_STE 
/* Lecture des CS de la société */ 
left outer join CS_DON cs_soc_1 with (nolock) on cs_soc_1.COD_ENR = e.CODE_STE and cs_soc_1.COD_TAB = 'SOCIETE' and cs_soc_1.NUM_FIX = @v_NCS_SOC_1_c 
/* Lecture du CS "Compte comptable 1" du profil de TVA */
left outer join CS_DON cs on cs.COD_TAB='PROFILS_TVA' and l.CODE_PROFIL_TVA = cs.COD_ENR and cs.NUM_FIX = @v_NCS_CpteTVA 
/* Lecture du CS "Taux de récupération de TVA" de la ligne de facture */
left outer join CS_DON cs_tx_recup_tva 
    on (cs_tx_recup_tva.COD_TAB='FAC_LIG' 
        and cast(dbo.OXA_DecoupeChaineV2(cs_tx_recup_tva.COD_ENR,1,'|') as int) = l.NUM_FACT 
        and cast(dbo.OXA_DecoupeChaineV2(cs_tx_recup_tva.COD_ENR,2,'|') as int) = l.NUM_LIG 
        and cs_tx_recup_tva.NUM_FIX = @v_NCS_TauxRecupTVA_3) 

where l.NUM_FACT in (select cast(ID_1 as int) from XT_LIG with(nolock) where TYPETR='<TYPETRAITEMENT>' and TYPEOBJ='<CODEOBJET>' and CODE_INT='<CODEINTERFACE>' and NUM_JOUR=1/*<NUMJOUR>*/) 
--where l.NUM_FACT in (select cast(ID_1 as int) from XT_LIG with(nolock) where TYPETR=@v_TYPETRAITEMENT and TYPEOBJ=@v_CODEOBJET and CODE_INT=@v_CODEINTERFACE and NUM_JOUR=@v_NUM_JOUR/*<NUMJOUR>*/)
/* On exclut les factures sans TVA (cas TTC = HT) */ 
and e.TOTAL_TTC <> e.TOTAL_HT 
/* On exclut les lignes de TVA à 0 (Retour 08/09/2025) */ 
and l.MT_TVA <> 0 

group by e.CODE_STE, e.NUM_FACT, cs.VALEUR, l.TYPE_TVA, l.TAUX_TVA, si.INVEST



/*<SEPARATEUR>*/
/* 
    REQ_4 : Liste des Lignes TTC des factures, dans la table 1 (xxTABLExx) 
*/

declare /* CS paramétrés sur les Sociétés */
    @v_NCS_SOC_1_d int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_SOCIETE_CODE_STE'), 0) /* Code société TALENTIA */

insert into xxTABLExx 
select distinct 

    1/*<NUMJOUR>*/      as lot_integration, -- N° du journal Oxalys
    MAX(ISNULL(cs_soc_1.VALEUR,'')) as ufk_societe,  
    replicate(' ', 30)  as ufk_origine,     -- Voir REQ_A (CS sur sous-famille)
    e.NUM_FACT          as uk_numero_piece, 
    1                   as fk_categorie_piece, 

    MAX(substring(e.DATE_PER,7,2)   +'/'+substring(e.DATE_PER,5,2)   +'/'+substring(e.DATE_PER,1,4))    as date_comptable,  -- DD/MM/YYYY
    MAX(substring(e.DATE_FAFO,7,2)  +'/'+substring(e.DATE_FAFO,5,2)  +'/'+substring(e.DATE_FAFO,1,4))   as date_piece,      -- DD/MM/YYYY 

    replicate(' ', 1)                                   as fk_classe_ecriture,  
    case when MAX(e.TYPE) = 'A' then '2' else '1' end   as fk_type_piece,  
    MAX(l.TYPE_TVA)                                     as fk_regime_tva, 
    0                                                   as uk_numero_ordre,     -- Voir REQ_J
    MAX(e.CODE_FOUR)                                    as fk_compte,           
    replicate(' ', 30)                                  as fk_type_tiers,       -- Voir REQ_B (CS sur fournisseur)
    replicate(' ', 30)                                  as fk_centralisateur,   -- Voir REQ_C (CS sur fournisseur, ou fk_compte)
    cast(MAX(e.CODE_FOUR) as varchar(30))               as libelle_ecriture,    -- Voir REQ_D (ajout de la RS sur 30 car max)
    right(rtrim(MAX(e.FACT_FOU)),20)                    as reference_lettrage,

    cast( ABS(MAX( e.TOTAL_TTC * (case when e.TX_DEVISE <> 0 then e.TX_DEVISE else 1 end) )) as decimal(15,2) ) as montant_m1, -- Montant TTC, en devise BDD  
     
    case when MAX(e.TYPE) = 'A' then 1 else -1 end      as fk_sens,             -- 1 = Débit, -1 = Crédit (Le TTC d'une Facture est au crédit, celui d'un avoir au débit)
    max(e.MODE_REG)                                     as fk_mode_reglement, 
    ''                                                  as fk_nature_tva,       
    ''                                                  as fk_code_tva,         -- Voir REQ_I Transcodification du taux de TVA
    replicate(' ', 1)                                   as date_specifique,             
    e.NUM_FACT                                          as identifiant_externe, 

    replicate(' ', 1)                                   as vide_24, 
    replicate(' ', 1)                                   as vide_25, 
    replicate(' ', 1)                                   as vide_26, 
    replicate(' ', 1)                                   as vide_27, 
    replicate(' ', 1)                                   as vide_28, 
    replicate(' ', 1)                                   as vide_29, 
    replicate(' ', 1)                                   as vide_30, 
    replicate(' ', 1)                                   as vide_31, 
    replicate(' ', 1)                                   as vide_32, 
    replicate(' ', 20)                                  as zone_specifique3,        -- Retour du 10/06 
    replicate(' ', 1)                                   as vide_34, 
    replicate(' ', 1)                                   as vide_35, 

    MAX(substring(e.DATE_FAFO,7,2)  +'/'+substring(e.DATE_FAFO,5,2)  +'/'+substring(e.DATE_FAFO,1,4))   as date_depart_echeance,    -- DD/MM/YYYY, idem date_piece
    MAX(substring(e.DTAE_ECH,7,2)   +'/'+substring(e.DTAE_ECH,5,2)   +'/'+substring(e.DTAE_ECH,1,4))    as date_echeance,           -- DD/MM/YYYY
    
--    right(rtrim(MAX(e.FACT_FOU)),20)                    as reference_tiers,         -- idem reference_lettrage
    cast(' ' as varchar(30))                            as reference_tiers,         -- idem libelle_ecriture (voir REQ_D2, depuis Dossier 305617)

    replicate(' ', 30)                                  as fk_type_de_tiers_regl,   -- Voir REQ_B (CS sur fournisseur) idem fk_type_tiers, et REQ_F (que si le chp suivant est renseigné )
    replicate(' ', 30)                                  as fk_tiers_reglement,      -- Voir REQ_E (CS sur en-tête)
    replicate(' ', 1)                                   as fk_fiche_tiers_reglement, -- Vide 
    --case when MAX(e.BON_A_PAY) = 0x54 then 'OK' else '' end as fk_code_bon_a_payer, 
    ''                                                  as fk_code_bon_a_payer, 

    replicate(' ', 1)                                   as vide_43, 
    replicate(' ', 1)                                   as vide_44, 
    replicate(' ', 1)                                   as vide_45, 
    replicate(' ', 1)                                   as vide_46, 
    replicate(' ', 1)                                   as vide_47, 
    replicate(' ', 1)                                   as vide_48, 
    replicate(' ', 1)                                   as vide_49, 
    replicate(' ', 1)                                   as vide_50, 
    replicate(' ', 1)                                   as vide_51, 
    replicate(' ', 1)                                   as vide_52, 
    replicate(' ', 1)                                   as vide_53, 

    ''                                                  as fk_rubrique,                 -- Pas d'analytique sur les lignes de TTC
    ''                                                  as fk_axe0, 
    ''                                                  as fk_axe1, 
    ''                                                  as fk_axe2,                     
    ''                                                  as fk_axe3,                     
    ''                                                  as fk_axe4,                     
    ''                                                  as fk_axe5,                     
    ''                                                  as fk_axe6,                     
    ''                                                  as fk_axe7,                     
    ''                                                  as fk_axe8,                     
    ''                                                  as fk_axe9,                     

    replicate(' ', 1)                                   as vide_65, 
    replicate(' ', 1)                                   as vide_66, 
    replicate(' ', 1)                                   as vide_67, 
    replicate(' ', 1)                                   as vide_68, 
    replicate(' ', 1)                                   as vide_69, 
    replicate(' ', 1)                                   as vide_70, 
    replicate(' ', 1)                                   as vide_71, 
    replicate(' ', 1)                                   as vide_72, 
    replicate(' ', 1)                                   as vide_73, 
    replicate(' ', 1)                                   as vide_74, 
    replicate(' ', 1)                                   as vide_75, 
    replicate(' ', 1)                                   as vide_76, 
    replicate(' ', 1)                                   as vide_77, 
    replicate(' ', 1)                                   as vide_78, 
    replicate(' ', 1)                                   as vide_79, 
    replicate(' ', 1)                                   as vide_80, 
    replicate(' ', 1)                                   as vide_81, 

    replicate(' ', 255)                                 as attachment1,                 -- Voir REQ_H (URL du pdf)

    replicate(' ', 1)                                   as vide_83, 
    replicate(' ', 1)                                   as vide_84, 
    replicate(' ', 1)                                   as vide_85, 
    replicate(' ', 1)                                   as vide_86, 
    replicate(' ', 1)                                   as vide_87, 
    replicate(' ', 1)                                   as vide_88, 
    replicate(' ', 1)                                   as vide_89, 
    replicate(' ', 1)                                   as vide_90, 
    replicate(' ', 1)                                   as vide_91, 

    'AT-OXA-' + e.CODE_STE + '-' + cast(e.NUM_FACT as varchar) as auditid,

    cast( ABS(MAX( e.TOTAL_TTC * (case when e.TX_DEVISE <> 0 then e.TX_DEVISE else 1 end) )) as decimal(15,2) ) as AZ32_montant_sans_tx_recup_tva, -- Montant TTC, en devise BDD  

    /* Ordre selon le group BY. 
        - 1 pour le TTC en premier, 2 pour le HT, 3 pour la TVA
    */
    e.CODE_STE + '-' + cast(e.NUM_FACT as varchar) + '-' + '1'                                                          as AZ32_ORDRE, 
    MAX(ISNULL(cs_soc_1.VALEUR,''))                                                                                     as AZ32_EXPORT_KEY, 
    'TTC'                                                                                                               as AZ32_TYPE_ENREG, 
    e.NUM_FACT                                                                                                          as AZ32_NUM_FACT,
    e.CODE_STE                                                                                                          as AZ32_CODE_STE,  
    MAX(e.CODE_FOUR)                                                                                                    as AZ32_CODE_FOUR, 
    MAX(l.CODE_SFAM)                                                                                                    as AZ32_CODE_SFAM,  
    0                                                                                                                   as AZ32_TAUX_TVA,
    ''                                                                                                                  as AZ32_CODE_SI, 
    ''                                                                                                                  as AZ32_TRI_POUR_UK_NUMERO_ORDRE 

from FAC_LIG l with (nolock) 
/* Lecture de l'en-tête de la facture */ 
left outer join FAC_ENT e with (nolock) on l.NUM_FACT = e.NUM_FACT 
/* Lecture du fournisseur */ 
left outer join FOURNISS frs with (nolock) on frs.CODE = e.CODE_FOUR 
/* Lecture des CS de la société */ 
left outer join CS_DON cs_soc_1 with (nolock) on cs_soc_1.COD_ENR = e.CODE_STE and cs_soc_1.COD_TAB = 'SOCIETE' and cs_soc_1.NUM_FIX = @v_NCS_SOC_1_d 

where l.NUM_FACT in (select cast(ID_1 as int) from XT_LIG with(nolock) where TYPETR='<TYPETRAITEMENT>' and TYPEOBJ='<CODEOBJET>' and CODE_INT='<CODEINTERFACE>' and NUM_JOUR=1/*<NUMJOUR>*/) 
--where l.NUM_FACT in (select cast(ID_1 as int) from XT_LIG with(nolock) where TYPETR=@v_TYPETRAITEMENT and TYPEOBJ=@v_CODEOBJET and CODE_INT=@v_CODEINTERFACE and NUM_JOUR=@v_NUM_JOUR/*<NUMJOUR>*/)
group by e.CODE_STE, e.NUM_FACT 



/*<SEPARATEUR>*/
/* REQ_A : CS "Code origine" de la sous-famille (elle devrait être la même dans les lignes d'un regroupement) */
declare @v_NCS_1 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_SSFAMILL_CODE_ORIGINE'), 0)
update t set ufk_origine = left(VALEUR, 30) 
    from xxTABLExx t, CS_DON cs 
    where t.AZ32_CODE_SFAM=cs.COD_ENR and cs.COD_TAB='SSFAMILL' and cs.NUM_FIX=@v_NCS_1 

/*<SEPARATEUR>*/
/* REQ_B : CS "Type Tiers" du fournisseur 
    - Seulement sur la ligne TTC, et sur les lignes dont le Code SI commence par 4 (consigne) (Modif 2 du 07/08/2024) (+ Modif Spec 5050)
*/
declare @v_NCS_2 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_FOURNISS_TYPE_TIERS'), 0)
update t set 
        fk_type_tiers           = left(VALEUR, 30), 
        fk_type_de_tiers_regl   = left(VALEUR, 30)
    from xxTABLExx t, CS_DON cs 
    where t.AZ32_CODE_FOUR=cs.COD_ENR and cs.COD_TAB='FOURNISS' and cs.NUM_FIX=@v_NCS_2 
    and (
            t.AZ32_TYPE_ENREG = 'TTC'
            or 
            (t.AZ32_TYPE_ENREG like 'HT%' and left(t.AZ32_CODE_SI,1) = '4') 
    ) 

/*<SEPARATEUR>*/
/* REQ_C1 : fk_centralisateur pour les lignes TTC (Retour Modif Spec du 28/09/2025)
    - Si aucune ligne d'immo sur la facture, c'est le CS "Centralisateur" du fournisseur
    - Sinon, c'est le CS "Centralisateur immo" sur la Société
*/
declare 
    @v_NCS_C1_FOU int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_FOURNISS_CENTRALISATEUR'), 0), 
    @v_NCS_C1_SOC int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_SOCIETE_CENTRALISATEUR_IMMO'), 0)

update t 
    set 
        fk_centralisateur = 
			(case 
				when not exists (select AZ32_NUM_FACT from xxTABLExx t2 where t2.AZ32_NUM_FACT = t.AZ32_NUM_FACT and t2.AZ32_TYPE_ENREG like 'HT-IMMO%') then left(isnull(cs_fou.VALEUR,''), 30) 
				else left(isnull(cs_soc.VALEUR,''), 30)
			end) 
    from xxTABLExx t 
        left outer join CS_DON cs_fou with(nolock) on (t.AZ32_CODE_FOUR=cs_fou.COD_ENR and cs_fou.COD_TAB='FOURNISS' and cs_fou.NUM_FIX=@v_NCS_C1_FOU)  
        left outer join CS_DON cs_soc with(nolock) on (t.AZ32_CODE_STE =cs_soc.COD_ENR and cs_soc.COD_TAB='SOCIETE'  and cs_soc.NUM_FIX=@v_NCS_C1_SOC)   
    where t.AZ32_TYPE_ENREG = 'TTC'   

/*<SEPARATEUR>*/
/* REQ_C2 : fk_centralisateur = fk_compte, pour les lignes hors TTC (et hors consigne, depuis Modif. du 17/07/2025) */
update xxTABLExx set fk_centralisateur = fk_compte 
    where AZ32_TYPE_ENREG <> 'TTC' -- hors TTC
    and not (AZ32_TYPE_ENREG like 'HT%' and left(AZ32_CODE_SI,1) = '4') -- et hors consigne

/*<SEPARATEUR>*/
/* REQ_C3 : fk_centralisateur = un CS de la Société, pour les lignes de consigne, depuis Modif. du 17/07/2025 */
declare @v_NCS_C3 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_SOCIETE_CENTRALISATEUR_CONSIGNE'), 0)
update t set fk_centralisateur = left(cs.VALEUR, 30)    
    from xxTABLExx t 
        inner join CS_DON cs with(nolock) on cs.COD_TAB='SOCIETE' and t.AZ32_CODE_STE = cs.COD_ENR and cs.NUM_FIX=@v_NCS_C3 
    where t.AZ32_TYPE_ENREG like 'HT%' and left(t.AZ32_CODE_SI,1) = '4' -- consigne

/*<SEPARATEUR>*/
/* REQ_C4 : on efface l'analytique, pour les lignes de consigne, depuis Modif. du 17/07/2025 */
update xxTABLExx set 
        fk_rubrique = '', 
        fk_axe0 = '',     
        fk_axe1 = '',     
        fk_axe2 = '',     
        fk_axe3 = '',     
        fk_axe4 = '',     
        fk_axe5 = '',     
        fk_axe6 = '',     
        fk_axe7 = '',     
        fk_axe8 = '',     
        fk_axe9 = ''      
    where AZ32_TYPE_ENREG like 'HT%' and left(AZ32_CODE_SI,1) = '4' -- consigne


/*<SEPARATEUR>*/
/* REQ_D : On ajoute la RS au début, sans dépasser 30 car */
update t set libelle_ecriture = left(f.RS, 30 - len(trim(libelle_ecriture)) -2) + ' ' + libelle_ecriture 
    from xxTABLExx t, FOURNISS f 
    where t.AZ32_CODE_FOUR=f.CODE and len(trim(libelle_ecriture)) < 30 

/*<SEPARATEUR>*/
/* REQ_D2 : reference_tiers doit être = à libelle_ecriture (Dossier 305617) */
update t set reference_tiers = libelle_ecriture
    from xxTABLExx t 

/*<SEPARATEUR>*/
/* REQ_E : CS "Tiers de règlement" sur l'en-tête */
declare @v_NCS_4 int = ISNULL((select cast(LIB01DICO as int) from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0166' and CODEDICO = 'NCS_FAC_ENT_TIERS_REGLEMENT'), 0)
update t set fk_tiers_reglement = left(VALEUR, 30) 
    from xxTABLExx t, CS_DON cs 
    where t.AZ32_NUM_FACT=cs.COD_ENR and cs.COD_TAB='FAC_ENT' and cs.NUM_FIX=@v_NCS_4 

/*<SEPARATEUR>*/
/* REQ_F : Si pas de tiers de règlement, on efface le type */
update xxTABLExx set fk_type_de_tiers_regl = '' 
    where trim(fk_tiers_reglement) = ''

/*<SEPARATEUR>*/
/* REQ_G : calcul de fk_axe5 */
update xxTABLExx set fk_axe5 = trim(fk_axe3) + trim(fk_axe4) 

/*<SEPARATEUR>*/
/* REQ_H : Calcul de l'URL du PDF de la facture */
update t 
set attachment1 = case when e.FIC_LAD<>'' then dbo.STD_UTI_URL_Charge_Document('<TAG_HTTP><TAG_DNS>/oxaproc<TAG_BROKER_NUM>','facture',e.FIC_LAD) else '' end 
    from xxTABLExx t, FAC_ENT e 
    where t.AZ32_NUM_FACT = e.NUM_FACT

/*<SEPARATEUR>*/
/* REQ_I : Transcodification du taux de TVA */
update t set fk_code_tva = d.LIB01DICO 
    from xxTABLExx t, DILISPEC d 
    where d.TYPEDICO = 'SOGEDO_TVA' and t.AZ32_TAUX_TVA = cast(d.CODEDICO as decimal(4,2)) 
    and t.AZ32_TYPE_ENREG <> 'TTC'


/*<SEPARATEUR>*/
/* REQ_J : Numérotation des lignes */

/*
declare 
    @v_AZ32_ORDRE       varchar(60) = '', 
    @v_AZ32_TYPE_ENREG  varchar(60) = '', 
    @v_Compteur         int         = 0 

declare CursorLignes cursor 
for 
select t.AZ32_ORDRE, t.AZ32_TYPE_ENREG from xxTABLExx t with(nolock) order by t.AZ32_ORDRE asc 

open CursorLignes
fetch next from CursorLignes into @v_AZ32_ORDRE, @v_AZ32_TYPE_ENREG
while (@@FETCH_STATUS = 0)
	begin
        /* Calcul du N° de ligne */
        if @v_AZ32_TYPE_ENREG = 'TTC' 
            begin 
                set @v_Compteur = 1
            end 
        else 
            begin 
                set @v_Compteur += 1
            end 
        /* Affection du N° de ligne */
        update xxTABLExx set uk_numero_ordre = @v_Compteur where AZ32_ORDRE = @v_AZ32_ORDRE
        /* Ligne suivante */
		fetch next from CursorLignes into @v_AZ32_ORDRE, @v_AZ32_TYPE_ENREG
	end
close CursorLignes 
deallocate CursorLignes
*/

/*<SEPARATEUR>*/
/* REQ_J_1 : Numérotation des lignes : calcul du critère de tri (Depuis évol. Dossier 305218) */
update xxTABLExx 
    set AZ32_TRI_POUR_UK_NUMERO_ORDRE = cast(AZ32_NUM_FACT as varchar)  
        + (
            case 
                when AZ32_TYPE_ENREG <> 'TTC' then  '-' + fk_compte + '-' + fk_code_tva 
                else '' -- On laisse à blanc : les lignes TTC seront donc en premier pour chaque facture (uk_numero_ordre à 1)
            end 
        )
   
/*<SEPARATEUR>*/
/* REQ_J_2 : Numérotation des lignes : pour chaque facture, selon le critère de tri (Depuis évol. Dossier 305218) */
/*
;with CTE_NUM as (
	select 
		AZ32_ORDRE, -- Unique
		AZ32_NUM_FACT, 
		AZ32_TYPE_ENREG, 
		AZ32_TRI_POUR_UK_NUMERO_ORDRE,  
		ROW_NUMBER() OVER(PARTITION BY AZ32_NUM_FACT ORDER BY AZ32_TRI_POUR_UK_NUMERO_ORDRE ASC) as uk_numero_ordre 
	from xxTABLExx with(nolock)
)
--select * from CTE_NUM order by AZ32_ORDRE
update t 
	set uk_numero_ordre = cte.uk_numero_ordre 
	from xxTABLExx t 
		inner join CTE_NUM cte on cte.AZ32_ORDRE = t.AZ32_ORDRE
*/

/*<SEPARATEUR>*/
/* REQ_J_2 : Numérotation des lignes : pour chaque facture, selon le critère de tri (Depuis évol. Dossier 305218) */

declare 
    @v_AZ32_ORDRE       varchar(60) = '', 
    @v_AZ32_TYPE_ENREG  varchar(60) = '', 
    @v_AZ32_TRI_POUR_UK_NUMERO_ORDRE  varchar(100) = '', 
    @v_AZ32_TRI_POUR_UK_NUMERO_ORDRE_PRECEDENT  varchar(100) = '', 
    @v_Compteur         int         = 0 

declare CursorLignes cursor 
for 
select t.AZ32_ORDRE, t.AZ32_TYPE_ENREG, t.AZ32_TRI_POUR_UK_NUMERO_ORDRE from xxTABLExx t with(nolock) order by t.AZ32_ORDRE asc 

open CursorLignes
fetch next from CursorLignes into @v_AZ32_ORDRE, @v_AZ32_TYPE_ENREG, @v_AZ32_TRI_POUR_UK_NUMERO_ORDRE 
while (@@FETCH_STATUS = 0)
	begin
        /* Calcul du N° de ligne */
        if @v_AZ32_TYPE_ENREG = 'TTC' 
            begin 
                set @v_Compteur = 1
            end 
        else 
            begin 
				-- On incrémente seulement si on change de valeur de rupture
				if @v_AZ32_TRI_POUR_UK_NUMERO_ORDRE_PRECEDENT <> @v_AZ32_TRI_POUR_UK_NUMERO_ORDRE
					begin 
						set @v_Compteur += 1
					end 
            end 
        /* Affection du N° de ligne */
        update xxTABLExx set uk_numero_ordre = @v_Compteur where AZ32_ORDRE = @v_AZ32_ORDRE
        /* Ligne suivante */
		set @v_AZ32_TRI_POUR_UK_NUMERO_ORDRE_PRECEDENT = @v_AZ32_TRI_POUR_UK_NUMERO_ORDRE
		fetch next from CursorLignes into @v_AZ32_ORDRE, @v_AZ32_TYPE_ENREG, @v_AZ32_TRI_POUR_UK_NUMERO_ORDRE
	end
close CursorLignes 
deallocate CursorLignes


-- TEST
-- select t.AZ32_ORDRE, t.AZ32_TYPE_ENREG, t.AZ32_TRI_POUR_UK_NUMERO_ORDRE, t.uk_numero_ordre, t.* from xxTABLExx t with(nolock) order by t.AZ32_ORDRE
