Untitled
unknown
plain_text
2 years ago
7.2 kB
7
Indexable
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
Editor is loading...
Leave a Comment