Untitled

DECLARE START_DATE DATE DEFAULT "2023-09-01";
DECLARE END_DATE DATE DEFAULT "2023-12-31";
   
WITH PS_REGION_CD AS (SELECT * FROM `fr-finance-prd.datasharing_psoft_AR.ps_region_cd`  ) ,
ps_cust_regn_type AS (SELECT * FROM `fr-finance-prd.datasharing_psoft_AR.ps_cust_regn_type`  ),


CUST_ID_CODE_CAB AS (
SELECT
    a.cust_id,
    --a.setid,
    max(b.region_cd) as CODE_CABINET,
    --b.descr
FROM
    ps_cust_regn_type a,
    ps_region_cd b
WHERE
    b.region_cd_type = a.region_cd_type
    AND b.region_cd = a.region_cd
    AND b.setid = a.setid
    AND b.region_type_id = 'CDCAB'
    AND b.region_cd_type = 'RPT'
    AND b.effdt=(
    SELECT
    MAX(effdt)
    FROM
    ps_region_cd c
    WHERE
    b.setid=c.setid
    AND b.REGION_CD_TYPE=c.region_cd_type
    AND b.region_cd=c.region_cd
    AND b.region_cd!="BZZZZ"
    AND b.region_cd!="B999")
    GROUP BY CUST_ID
),


BASE_REFERENTIEL AS (


SELECT A.CUST_ID,
BP_SUCCURSALE,
CODE_CABINET,
NOM_CABINET_COMPTABLE,
VILLE,
NOM_CLIENT,
ADRESSE_EMAIL,
MODE_PAIEMENT




FROM CUST_ID_CODE_CAB A


INNER JOIN (
            SELECT CODE_CABINET_COMPTABLE,MAX(ADRESSE_EMAIL) as ADRESSE_EMAIL,MAX(NOM_CABINET_COMPTABLE) as NOM_CABINET_COMPTABLE
            FROM `vg1p-apps-dacdata-prd1-b9.o2c_referentiels.t_o2c_referentiel_code_cabinet_comptable_copie`
            GROUP BY CODE_CABINET_COMPTABLE
            ) B
            ON A.CODE_CABINET=B.CODE_CABINET_COMPTABLE


LEFT JOIN   (
            SELECT LEGACY_ID,MAX(CUST_ID) AS CUST_ID,MAX(BR.BRANCH_ID) AS BP_SUCCURSALE,MAX(TOWN) AS VILLE, MAX(CUST_NAME) AS NOM_CLIENT
            FROM `vg1p-apps-dacdata-prd1-b9.o2c_referentiels.t_o2c_referentiel_clients_b2b_sap`,UNNEST(BRANCHS) BR
            GROUP BY LEGACY_ID
            ) REF_SAP
            ON A.CUST_ID=REF_SAP.LEGACY_ID




INNER JOIN   (
            SELECT KUNNR,MAX(ZWELS) AS MODE_PAIEMENT
            FROM `fr-finance-prd.datasharing_sap.knb1` X
            GROUP BY KUNNR
            ) D ON D.KUNNR=REF_SAP.BP_SUCCURSALE


),




FACTURATION AS (


    SELECT  
    D.BUTXT ENTITE,
    A.KUNNR CODE_CLIENT,
    A.FILKD CODE_CLIENT_SAP,
    CASE
    WHEN H_BLART="DI" THEN KIDNO
    WHEN H_BLART!="DI" THEN XBLNR
    END
    AS PIECE,      
    CASE
    WHEN H_BLART="DI" AND XBLNR like 'FR%' THEN XBLNR
    WHEN H_BLART="DI" AND XBLNR not like 'FR%' THEN CONCAT(SUBSTR(B.XBLNR,3, 8),SUBSTR(B.XBLNR,15,2))
    WHEN H_BLART!="DI" THEN SGTXT
    END
    AS DOCUMENT,
    BLDAT AS DATE_PIECE,
    NETDT as DATE_ECHEANCE_PIECE,
    ' ' DATE_RELEVE,
    ' 'AS NUMERO_PRELEVEMENT,
    ' '  AS NUMERO_TRAITE,
    ' '   DATE_ECHEANCE_RELEVE,
    C.TEXT1 as METHODE_PAIEMENT,  
    CASE
    WHEN H_BLART="DB" THEN "TRANSFERT"
    WHEN H_BLART="DI" THEN "IMPAYE"
    WHEN H_BLART!="DB" AND A.SHKZG = 'H' THEN "AVOIR"
    WHEN H_BLART!="DB" AND A.SHKZG = 'S' THEN "FACTURE"
    END
    AS NATURE,
    CASE
    WHEN A.SHKZG = 'S' THEN A.DMBTR ELSE -1 * A.DMBTR
    END MONTANT
    FROM
        `fr-finance-prd.datasharing_sap.bseg` A


    # BKPF ; en-tête des pièces comptables, contient des informations supplémentaires sur les pièces
    LEFT JOIN
        `fr-finance-prd.datasharing_sap.bkpf` B
    ON
        A.BUKRS = B.BUKRS AND A.BELNR = B.BELNR AND A.GJAHR = B.GJAHR
   
    # Nom de la société facturante d'origine
    LEFT JOIN
        `fr-finance-prd.datasharing_sap.t001`D
    ON
        A.BUKRS = D.BUKRS
    LEFT JOIN
        (SELECT ZLSCH,TEXT1 FROM `fr-finance-prd.datasharing_sap.t042z` WHERE LAND1 = 'FR') C
    ON
        A.ZLSCH = C.ZLSCH    


    WHERE
    # Compte D = comptes clients
    KOART = 'D'
    # Ne pas reprendre les relevés
    AND TRIM(UMSKZ)=""
    AND BLART!="DV"
    # Exclure les codes fournisseurs et intra-groupe
    AND A.KUNNR LIKE "C%"
    AND BUDAT BETWEEN START_DATE AND END_DATE  




),


SORTIE_PRE_TOTAUX AS (


    SELECT
        CODE_CABINET,
        ENTITE,
        NOM_CABINET_COMPTABLE,
        CUST_ID AS CODE_CLIENT,
        CODE_CLIENT_SAP,
        NOM_CLIENT,
        VILLE,
        PIECE,
        DOCUMENT,
        DATE_PIECE,
        DATE_ECHEANCE_PIECE,
        DATE_RELEVE,
        NUMERO_PRELEVEMENT,
        NUMERO_TRAITE,
        DATE_ECHEANCE_RELEVE,
        MODE_PAIEMENT AS METHODE_PAIEMENT,  
        NATURE,
        ADRESSE_EMAIL,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                CODE_CABINET,
                ENTITE,
                CODE_CLIENT_SAP
            ORDER BY
                CODE_CABINET ASC,
                ENTITE ASC,    
                CODE_CLIENT_SAP ASC,
                DATE_RELEVE ASC,
                NUMERO_PRELEVEMENT ASC,
                NUMERO_TRAITE ASC,    
                DATE_PIECE ASC,
                PIECE ASC,
                MONTANT ASC
        )
        AS ROW_NUM_CLIENT,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                CODE_CABINET,
                ENTITE
            ORDER BY
                CODE_CABINET ASC,
                ENTITE ASC,    
                CODE_CLIENT_SAP ASC,
                DATE_RELEVE ASC,
                NUMERO_PRELEVEMENT ASC,
                NUMERO_TRAITE ASC,    
                DATE_PIECE ASC,
                PIECE ASC,
                MONTANT ASC
        )
        AS ROW_NUM_ENTITE,    
        MONTANT
    FROM
        FACTURATION A
    INNER JOIN
        BASE_REFERENTIEL B
    ON
        A.CODE_CLIENT_SAP=B.BP_SUCCURSALE




    GROUP BY
        CODE_CABINET,  
        NOM_CABINET_COMPTABLE,
        ENTITE,
        CODE_CLIENT,
        CODE_CLIENT_SAP,
        NOM_CLIENT,
        VILLE,
        PIECE,
        DOCUMENT,
        DATE_PIECE,
        DATE_ECHEANCE_PIECE,
        DATE_RELEVE,
        NUMERO_PRELEVEMENT,
        NUMERO_TRAITE,
        DATE_ECHEANCE_RELEVE,
        MODE_PAIEMENT,  
        NATURE,
        ADRESSE_EMAIL,
        MONTANT




)


    SELECT
        CODE_CABINET,
        NOM_CABINET_COMPTABLE,
        ENTITE,
        CODE_CLIENT,
        CODE_CLIENT_SAP,
        NOM_CLIENT,
        VILLE,
        PIECE,
        DOCUMENT,
        DATE_PIECE,
        DATE_ECHEANCE_PIECE,
        DATE_RELEVE,
        NUMERO_PRELEVEMENT,
        NUMERO_TRAITE,
        DATE_ECHEANCE_RELEVE,
        METHODE_PAIEMENT,
        NATURE,
        ADRESSE_EMAIL,
        MONTANT,
        ' ' TOTAL,
        CASE
        WHEN row_num_client = 1 THEN SUM(MONTANT) OVER (PARTITION BY CODE_CABINET,ENTITE,CODE_CLIENT_SAP)
        ELSE NULL
        END AS
        TOTAL_CLIENT,
        CASE
        WHEN row_num_entite = 1 THEN SUM(MONTANT) OVER (PARTITION BY CODE_CABINET,ENTITE)
        ELSE NULL
        END AS
        TOTAL_ENTITE
    FROM
        SORTIE_PRE_TOTAUX WHERE CODE_CABINET = "B065"
    ORDER BY
        CODE_CABINET ASC,
        ENTITE ASC,    
        CODE_CLIENT_SAP ASC,
        DATE_RELEVE ASC,
        NUMERO_PRELEVEMENT ASC,
        NUMERO_TRAITE ASC,    
        DATE_PIECE ASC,
        PIECE ASC