Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

# Les données de livraison du vendeur sont transmises AWS Marketplace
<a name="data-feed-service"></a>

AWS Marketplace fournit des flux de données en tant que mécanisme permettant d'envoyer des informations structurées sur les up-to-date produits et les clients des AWS Marketplace systèmes aux compartiments Amazon S3 du vendeur pour un ETL (extraction, transformation et chargement) entre des outils de business intelligence appartenant au vendeur. Les flux de données collectent et transmettent des fichiers de valeurs séparées par des virgules (CSV) à un compartiment Amazon S3 chiffré que vous fournissez. Les flux de données sont générés en un jour et contiennent 24 heures de données de la veille. Les sections suivantes fournissent une vue d'ensemble des flux de données et expliquent comment y accéder et les utiliser. Les sections suivantes décrivent chaque flux de données. 

Les données transactionnelles sont fournies et ajoutées dans une structure bi-temporelle afin que les vendeurs puissent stocker et interroger les données selon deux chronologies, horodatées pour les deux
+ heure valide : date à laquelle un fait s'est produit dans le monde réel (« ce que vous saviez »)
+ heure du système : date à laquelle ce fait a été enregistré dans la base de données (« quand vous le saviez »).

Les flux de données sont fournis tous les jours à minuit UTC après une mise à jour de la veille contenant 24 heures de données de la veille. Une mise à jour peut être définie par l'abonnement d'un client, la facturation d'un client ou le AWS versement d'un paiement.

**Topics**
+ [Stockage et structure des flux de AWS Marketplace données](data-feed-details.md)
+ [Accès aux flux de données](data-feed-accessing.md)
+ [Collecte et analyse de données à l'aide de flux de données](data-feed-using.md)
+ [Vue d'ensemble des tableaux de flux de données](data-feed-joining.md)
+ [Exemples de requêtes de flux de données](data-feed-full-examples.md)
+ [Flux de données](data-feeds.md)

# Stockage et structure des flux de AWS Marketplace données
<a name="data-feed-details"></a>

AWS Marketplace fournit des flux de données en tant que mécanisme permettant d'envoyer des informations structurées sur les up-to-date produits et les clients des AWS Marketplace systèmes aux compartiments Amazon S3 du vendeur pour un ETL (extraction, transformation et chargement) entre des outils de business intelligence appartenant au vendeur. Cette rubrique fournit des informations supplémentaires sur la structure et le stockage des flux de données.

Les flux de données collectent et transmettent des fichiers de valeurs séparées par des virgules (CSV) à un compartiment Amazon S3 chiffré que vous fournissez. Les fichiers CSV présentent les caractéristiques suivantes :
+ Ils respectent les [normes 4180.](https://tools.ietf.org/html/rfc4180)
+ L'encodage des caractères est UTF-8 sans nomenclature.
+ Les virgules sont utilisées comme séparateurs entre les valeurs.
+ L'échappement des champs s'effectue à l'aide de guillemets doubles. 
+ `\n` est le caractère de saut de ligne.
+ Les dates sont indiquées dans le fuseau horaire UTC, sont au format de date et d'heure ISO 8601 et sont exactes à la seconde près.
+ Toutes les valeurs `*_period_start_date` et `*_period_end_date` sont inclusives, ce qui signifie que `23:59:59` est le dernier horodatage possible pour n'importe quel jour. 
+ Tous les champs monétaires sont précédés d'un champ de devise. 
+ Les champs monétaires utilisent un caractère point (`.`) comme séparateur décimal et n'utilisent pas de virgule (,) comme séparateur des milliers. 

Les flux de données sont générés et stockés comme suit :
+ Les flux de données sont générés en un jour et contiennent 24 heures de données de la veille. 
+ Dans le compartiment Amazon S3, les flux de données sont organisés par mois selon le format suivant :

  `bucket-name/data-feed-name_version/year=YYYY/month=MM/data.csv`
+ Au fur et à mesure que chaque flux de données quotidien est généré, il est ajouté au fichier CSV existant pour ce mois. Lorsqu'un nouveau mois démarre, un nouveau fichier CSV est généré pour chaque flux de données. 
+ Les informations contenues dans les flux de données sont remplies du 2010/01/01 au 2020/04/30 (inclus) et sont disponibles dans le [fichier CSV](#data-feed-details) du sous-dossier `year=2010/month=01`.

  Vous remarquerez peut-être des cas où le fichier du mois en cours pour un flux de données donné ne contient que des en-têtes de colonne et aucune donnée. Cela signifie qu'il n'y avait pas de nouvelles entrées pour ce mois pour le flux. Cela peut se produire avec des flux de données qui sont mis à jour moins fréquemment, comme le flux de produits. Dans ces cas, les données sont disponibles dans le dossier rempli. 
+ Dans Amazon S3, vous pouvez créer une [politique de cycle de vie Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/create-lifecycle.html) pour gérer la durée de conservation des fichiers dans le compartiment. 
+ Vous pouvez configurer Amazon SNS pour qu'il vous avertisse lorsque des données sont livrées à votre compartiment Amazon S3 chiffré. Pour plus d'informations sur la configuration des notifications, consultez [Getting started with Amazon SNS](https://docs.aws.amazon.com/sns/latest/dg/sns-getting-started.html) dans le manuel *Amazon Simple Notification Service Developer Guide*.

## Historisation des données
<a name="data-feed-historization"></a>

Chaque flux de données comprend des colonnes qui documentent l'historique des données. Sauf pour `valid_to`, ces colonnes sont communes à tous les flux de données. Elles sont incluses en tant que schéma d'historique commun et sont utiles pour interroger les données. 


| Nom de la colonne  | Description  | 
| --- | --- | 
| valid\$1from | Première date pour laquelle la valeur de la clé primaire est valide par rapport aux valeurs des autres champs. | 
| valid\$1to | Cette colonne est uniquement affichée dans le flux de données [Adresse](data-feed-address.md) et elle est toujours vide. | 
| insert\$1date | Date à laquelle un enregistrement a été inséré dans le flux de données. | 
| update\$1date | Date de la dernière mise à jour de l'enregistrement.  | 
| delete\$1date | Cette colonne est toujours vide. | 

Voici un exemple de ces colonnes. 


|  valid\$1from  |  valid\$1to  |  insert\$1date  |  update\$1date  |  delete\$1date  | 
| --- | --- | --- | --- | --- | 
|  2018-12-12T 02:00:00 Z  |   |  2018-12-12T 02:00:00 Z  |  2018-12-12T 02:00:00 Z  |   | 
|  2019-03-29T 03:00:00 Z  |   |  2019-03-29T 03:00:00 Z  |  2019-03-29T 03:00:00 Z  |   | 
|  2019-03-29T 03:00:00 Z  |   |  2019-03-29T 03:00:00 Z  |  2019-04-28 03:00:00 Z  |   | 

Les `update_date` champs `valid_from` et forment ensemble un *modèle de données bi-temporel*. Le `valid_from` champ, tel qu'il est nommé, vous indique à partir de quel moment l'article est valide. Si l'élément a été modifié, il peut contenir plusieurs enregistrements dans le flux, chacun portant une `valid_from` date différente `update_date` mais identique. Par exemple, pour trouver la valeur actuelle d'un élément, vous devez rechercher l'enregistrement le plus récent `update_date` dans la liste des enregistrements contenant la `valid_from` date la plus récente.

Dans l'exemple ci-dessus, l'enregistrement a été créé à l'origine le 12/2018. Elle a ensuite été modifiée le 29/03/2019 (par exemple, si l'adresse figurant dans le dossier a changé). Plus tard, le 28/04/2019, le changement d'adresse a été corrigé (il `valid_from` n'a donc pas changé, mais il l'`update_date`a fait). La correction de l'adresse (un événement rare) modifie rétroactivement l'enregistrement par rapport à la `valid_from` date d'origine, de sorte que le champ n'a pas changé. Une requête visant à trouver le plus récent `valid_from` renverrait deux enregistrements, celui contenant le plus récent `update_date` indique l'enregistrement actuel.

# Accès aux flux de données
<a name="data-feed-accessing"></a>

Vous pouvez utiliser les flux de données comme mécanisme pour envoyer des informations structurées sur les up-to-date produits et les clients à partir de AWS Marketplace systèmes et de compartiments Amazon S3 pour ETL (extraction, transformation et chargement) entre des outils de business intelligence appartenant au vendeur. AWS Marketplace Vous devez configurer votre environnement pour recevoir des flux de données vers un compartiment Amazon S3 chiffré. Cette rubrique explique comment accéder aux flux de données et s'en désabonner.

**Topics**
+ [Accédez à un flux de données](#data-feed-accessing-procedure)
+ [Politiques relatives aux flux de données](#data-feed-policies)
+ [Se désabonner des flux de données](#data-feed-unsubscribing)

## Accédez à un flux de données
<a name="data-feed-accessing-procedure"></a>

1. Désignez un ingénieur informatique ou un ingénieur des données doté d'une expérience SQL et ETL (extraction, transformation, chargement). Cette personne doit également avoir de l'expérience en configuration APIs.

1. Configurez un bucket Amazon Simple Storage Service et un abonnement aux flux de données. Utilisez l'identifiant AWS du compte vendeur associé à vos offres de produits Marketplace. Pour ce faire, vous pouvez [regarder cette YouTube vidéo](https://www.youtube.com/watch?v=heCsZdOT-hw) ou suivre les étapes ci-dessous.

   La vidéo et les étapes expliquent comment utiliser un [CloudFormation modèle](https://s3.amazonaws.com/aws-marketplace-reports-resources/DataFeedsResources.yaml) qui permet de simplifier la configuration.

   1. Ouvrez un navigateur Web et connectez-vous au [Portail de gestion AWS Marketplace](https://aws.amazon.com/marketplace/management/), puis accédez à [Configurer le stockage des données clients](https://aws.amazon.com/marketplace/management/reports/data-feed-configuration).

   1. Choisissez **Créer des ressources avec CloudFormation un modèle** pour ouvrir le modèle dans la CloudFormation console dans une autre fenêtre.

   1. Dans le modèle, spécifiez les éléments suivants, puis choisissez **Suivant** :
      + Nom de la pile : collection de ressources que vous créez pour permettre l'accès aux flux de données.
      + Nom du compartiment Amazon S3 : compartiment destiné au stockage des flux de données.
      + (Facultatif) Nom de la rubrique Amazon SNS : rubrique de réception des notifications lors d'un bucket Amazon Simple Storage Service.

   1. Sur la page **Révision**, validez vos entrées et choisissez **Créer une pile**. Cela ouvrira une nouvelle page avec le CloudFormation statut et les détails.

   1. Dans l'onglet **Resources**, copiez Amazon Resource Names (ARNs) pour les ressources suivantes depuis la CloudFormation page dans les champs de la page AWS Marketplace [Configurer le stockage des données clients](https://aws.amazon.com/marketplace/management/reports/data-feed-configuration) :
      + Compartiment Amazon S3 pour le stockage de flux de données
      + AWS KMS clé pour chiffrer le compartiment Amazon S3
      + (Facultatif) Rubrique Amazon SNS relative à la réception de notifications lors de l' AWS envoi de nouvelles données dans le compartiment Amazon S3

   1. Sur la page **Configurer le stockage des données client**, choisissez **Soumettre**.

   1. (Facultatif) Modifiez les politiques créées par le CloudFormation modèle. Pour plus d’informations, consultez [Politiques relatives aux flux de données](#data-feed-policies).

      Vous êtes désormais abonné aux flux de données. La prochaine fois que les flux de données seront générés, vous pourrez accéder aux données.

1. Utilisez une opération ETL (extraction, transformation, chargement) pour connecter les flux de données à votre entrepôt de données ou à votre base de données relationnelle.
**Note**  
Les outils de données ont des fonctionnalités différentes. Vous devez faire appel à un ingénieur en intelligence d'affaires ou à un ingénieur de données pour configurer l'intégration en fonction des capacités de votre outil.

1. Pour exécuter ou créer des requêtes SQL, configurez les flux de données pour appliquer les clés primaires et étrangères dans votre outil de données. Chaque flux de données représente une table unique, et vous devez configurer tous les flux de données dans le schéma de données avec les relations entre les entités. Pour plus d'informations sur les tables et les relations entre entités, consultez [Vue d'ensemble des tableaux de flux de données](data-feed-joining.md) ce guide.

1. Configurez Amazon Simple Notification Service pour actualiser automatiquement votre entrepôt de données ou votre base de données relationnelle. Vous pouvez configurer les notifications Amazon SNS pour envoyer des alertes lorsque les données de chaque flux unique sont transmises à un compartiment Amazon S3. Ces notifications peuvent être utilisées pour actualiser automatiquement l'entrepôt de données des vendeurs lorsque de nouvelles données sont reçues via des flux de données, si l'outil de données des vendeurs prend en charge cette fonctionnalité. Consultez [Getting started with Amazon SNS](https://docs.aws.amazon.com/sns/latest/dg/sns-getting-started.html) dans le guide du *développeur Amazon Simple Notification Service*.

   Exemple de notification :

   ```
   {
           "mainExecutionId": "1bc08b11-ab4b-47e1-866a-9c8f38423a98",
           "executionId": "52e862a9-42d2-41e0-8010-810af84d39b1",
           "subscriptionId": "27ae3961-b13a-44bc-a1a7-365b2dc181fd",
           "processedFiles": [],
           "executionStatus": "SKIPPED",
           "errors": [],
           "feedType": "[data feed name]"
           }
   ```

   Les notifications peuvent avoir les `executionStatus` états suivants :
   + `SKIPPED`— Le vendeur n'a aucune nouvelle donnée pour la journée.
   + `COMPLETED`— Nous avons livré le flux avec de nouvelles données.
   + `FAILED`— La livraison des aliments a un problème.

1. Validez la configuration en exécutant des requêtes SQL. Vous pouvez utiliser les [exemples de requêtes présentés dans ce guide](https://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-full-examples.html), ou les requêtes sur GitHub, à l'adresse [ https://github.com/aws-samples/aws-marketplace-api-samples/tree/main/seller-data-feeds/queries](https://github.com/aws-samples/aws-marketplace-api-samples/tree/main/seller-data-feeds/queries).
**Note**  
Les exemples de requêtes présentés dans ce guide ont été rédigés pour AWS Athéna. Vous devrez peut-être modifier les requêtes pour les utiliser avec vos outils.

1. Déterminez où les utilisateurs professionnels souhaitent utiliser les données. Par exemple, vous pouvez :
   + Exportez des données .csv depuis votre entrepôt de données ou votre base de données SQL.
   + Connectez vos données à un outil de visualisation tel que PowerBI ou Tableau.
   + Associez les données à votre CRM, à votre ERP ou à vos outils financiers, tels que Salesforce, Infor ou Netsuite.

Pour plus d'informations sur les CloudFormation modèles, voir [Utilisation des CloudFormation modèles](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/template-guide.html) dans le *Guide de AWS CloudFormation l'utilisateur*.

## Politiques relatives aux flux de données
<a name="data-feed-policies"></a>

Lorsque votre compartiment Amazon S3 est créé par le CloudFormation modèle, il crée des politiques d'accès associées à ce compartiment, à la AWS KMS clé et à la rubrique Amazon SNS. Les politiques permettent au service de AWS Marketplace rapports d'écrire dans votre bucket et dans votre rubrique SNS avec les informations du flux de données. Chaque politique comportera une section similaire à la suivante (cet exemple provient du compartiment Amazon S3).

```
        {
            "Sid": "AwsMarketplaceDataFeedsAccess",
            "Effect": "Allow",
            "Principal": {
                "Service": "reports.marketplace.amazonaws.com"
            },
            "Action": [
                "s3:ListBucket",
                "s3:GetObject",
                "s3:PutObject",
                "s3:GetEncryptionConfiguration",
                "s3:GetBucketAcl",
                "s3:PutObjectAcl"
            ],
            "Resource": [
                "arn:aws:s3:::amzn-s3-demo-bucket",
                "arn:aws:s3:::amzn-s3-demo-bucket/*"
            ]
        },
```

Dans cette politique, AWS Marketplace utilise le principal `reports.marketplace.amazonaws.com` de service pour transférer les données vers le compartiment Amazon S3. Vous l'avez spécifié *amzn-s3-demo-bucket* dans le CloudFormation modèle.

Lorsque le service de AWS Marketplace rapports appelle Amazon S3 ou Amazon SNS, il fournit l'ARN des données qu'il a l'intention d'écrire dans le compartiment. AWS KMS Pour vous assurer que les seules données écrites dans votre bucket sont des données écrites en votre nom, vous pouvez les spécifier `aws:SourceArn` dans les conditions de la politique. Dans l'exemple suivant, vous devez remplacer le *account-id* par l'identifiant de votre Compte AWS.

```
        {
           "Sid": "AwsMarketplaceDataFeedsAccess",
           "Effect": "Allow",
           "Principal": {
                "Service": "reports.marketplace.amazonaws.com"
            },
            "Action": [
                "s3:ListBucket",
                "s3:GetObject",
                "s3:PutObject",
                "s3:GetEncryptionConfiguration",
                "s3:GetBucketAcl",
                "s3:PutObjectAcl"
            ],
            "Resource": [
                "arn:aws:s3:::amzn-s3-demo-bucket",
                "arn:aws:s3:::amzn-s3-demo-bucket/*"
            ,
            "Condition": {
                "StringEquals": {
                        "aws:SourceAccount": "account-id",
                        "aws:SourceArn": ["arn:aws:marketplace::account-id:AWSMarketplace/SellerDataSubscription/DataFeeds_V1",
                        "arn:aws:marketplace::account-id:AWSMarketplace/SellerDataSubscription/Example-Report"]
                        }
                }
        },
```

## Se désabonner des flux de données
<a name="data-feed-unsubscribing"></a>

Ouvrez un navigateur Web et connectez-vous au [portail AWS Marketplace de gestion](https://aws.amazon.com/marketplace/management/). Accédez ensuite à la [page Contactez-nous](https://aws.amazon.com/marketplace/management/contact-us/) pour soumettre une demande de désinscription à l'équipe des opérations AWS Marketplace vendeurs. Le traitement de la demande de désinscription peut prendre jusqu'à 10 jours ouvrables.

# Collecte et analyse de données à l'aide de flux de données
<a name="data-feed-using"></a>

AWS Marketplace fournit des flux de données en tant que mécanisme permettant d'envoyer des informations structurées sur les up-to-date produits et les clients des AWS Marketplace systèmes aux compartiments Amazon S3 du vendeur pour un ETL (extraction, transformation et chargement) entre des outils de business intelligence appartenant au vendeur. Lorsque des données sont disponibles dans votre compartiment Amazon S3, vous pouvez utiliser les flux de données des manières suivantes :
+ Téléchargez les fichiers .CSV depuis le compartiment Amazon S3 que vous avez créé [Accès aux flux de données](data-feed-accessing.md) afin de pouvoir afficher les données dans une feuille de calcul.
+ Utilisez ETL (extraction, transformation et chargement), les requêtes SQL, les outils d'analyse métier pour collecter et analyser les données.

  Vous pouvez utiliser AWS des services pour collecter et analyser des données, ou tout autre outil tiers capable d'analyser des ensembles de données au format .CSV.

Pour plus d'informations sur les flux de données permettant de collecter et d'analyser des données, consultez l'exemple suivant.

## Exemple : utiliser AWS des services pour collecter et analyser des données
<a name="data-feed-using-example"></a>

La procédure suivante suppose que vous avez déjà configuré votre environnement pour recevoir des flux de données vers un compartiment Amazon S3 et que le compartiment contient des flux de données.

**Pour collecter et analyser des données à partir de flux de données**

1. Depuis la [AWS Glue console](https://console.aws.amazon.com/glue), [créez un robot d'exploration](https://docs.aws.amazon.com/glue/latest/dg/add-crawler.html) pour vous connecter au compartiment Amazon S3 qui stocke les flux de données, extrait les données souhaitées et crée des tables de métadonnées dans le AWS Glue Data Catalog.

   Pour plus d'informations à ce sujet AWS Glue, consultez le [https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html](https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html).

1. À partir de la [console Athena](https://console.aws.amazon.com/athena), [exécutez des requêtes SQL sur les données du](https://docs.aws.amazon.com/athena/latest/ug/querying-athena-tables.html). AWS Glue Data Catalog

   Pour plus d'informations sur Athena, consultez le guide de l'utilisateur d'[https://docs.aws.amazon.com/athena/latest/ug/what-is.html](https://docs.aws.amazon.com/athena/latest/ug/what-is.html). 

1. À partir de la [console Quick](http://quicksight.aws.amazon.com), [créez une analyse](https://docs.aws.amazon.com/quick/latest/userguide/creating-an-analysis.html), puis [créez un visuel](https://docs.aws.amazon.com/quick/latest/userguide/creating-a-visual.html) des données.

   Pour plus d'informations sur Quick, consultez le [https://docs.aws.amazon.com/quick/latest/userguide/welcome.html](https://docs.aws.amazon.com/quick/latest/userguide/welcome.html).

Pour un exemple détaillé d'une manière d'utiliser les AWS services pour collecter et analyser des données dans des flux de données, consultez [Utiliser le service de livraison de flux de données pour les vendeurs, Amazon Athena et Quick pour créer des rapports sur les vendeurs](https://aws.amazon.com/blogs/awsmarketplace/using-seller-data-feed-delivery-service-amazon-athena-and-amazon-quicksight-to-create-seller-reports/) sur le AWS Marketplace blog.

# Vue d'ensemble des tableaux de flux de données
<a name="data-feed-joining"></a>

AWS Marketplace fournit des flux de données sous la forme d'un ensemble de tables que vous pouvez joindre pour fournir plus de contexte à vos requêtes.

AWS Marketplace fournit les domaines généraux ou catégories d'intérêt suivants dans vos flux de données :
+ **Catalogue** — Inclut des informations sur les produits et les offres de votre compte.
+ **Comptes** : inclut des informations sur les comptes qui fournissent ou achètent des produits AWS Marketplace (vos propres comptes ou les comptes des parties avec lesquelles vous travaillez, tels que les partenaires de distribution ou les acheteurs).
+ **Recettes** — Comprend des informations sur la facturation, les décaissements et les taxes.
+ **Achats** : inclut des informations sur les accords relatifs aux offres de produits que vous avez créées en tant que vendeur officiel. 

Ce diagramme montre les tables des domaines Catalogue, Comptes et Recettes.

![\[Entity relationship diagram showing how data feeds relate to each other.\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/images/datafeeds-overview.png) 

## Tableaux relatifs au catalogue
<a name="data-feed-catalog-domain"></a>

Le schéma suivant montre les relations entre les tables du domaine Catalog, ainsi que les champs au sein des tables. 

![\[Relations entre les tables Product, Offer_Product, Offer, Offer_Target et Legacy_ID_Mapping dans le domaine Catalog.\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/images/datafeeds-catalog-details.png)


Les tables`Product`,`Offer_Product`, `Offer``Offer_Target`, et `Legacy_id_mapping` \$1tables se trouvent dans le domaine du catalogue.

La `Offer_Target` table inclut un champ de valeur pour `account_id` la cible, mais uniquement lorsque la `target_type` valeur est`account`.

Le `Legacy_id_mapping` tableau n'est pas utilisé pour les données actuelles.

**Note**  
Pour plus d'informations sur ces tables, notamment une description de chaque champ de la table et des jointures qui peuvent être créées, consultez les rubriques suivantes :  
[Flux de données produit](data-feed-product.md)
[Proposer un flux de données produit](data-feed-offer-product.md)
[Offre de flux de données](data-feed-offer.md)
[Offrir un flux de données cible](data-feed-offer-target.md)
[Flux de données de mappage hérité](data-feed-legacy-mapping.md)

## Tableaux relatifs aux comptes
<a name="data-feed-accounts-domain"></a>

Le schéma suivant montre les relations entre les `Address` tables `Account` et dans le domaine des comptes, ainsi que les champs des tables.

![\[Relation entre les tables de compte et d'adresses dans le domaine des comptes et les champs de chaque table.\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/images/datafeeds-accounts-details.png)


**Note**  
Pour plus d'informations sur ces tables, notamment une description de chaque champ de la table et des jointures que vous pouvez créer, consultez les rubriques suivantes :  
[Flux de données de compte](data-feed-account.md)
[Flux de données d'adresse](data-feed-address.md)

## Tableaux relatifs aux recettes
<a name="data-feed-revenue-domain"></a>

Le schéma suivant montre les relations entre les `Tax_Item` tables `Billing_Event` et dans le domaine des recettes, ainsi que les champs des tables. Le `Billing_Event` tableau contient des informations sur les décaissements, ainsi que sur les événements de facturation.

![\[Relations entre les tables Billing_Event et Tax_Item dans le domaine Revenue et les champs de chaque table.\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/images/datafeeds-revenue-details.png)


**Note**  
Pour plus d'informations sur ces tables, notamment une description de chaque champ de la table et des jointures que vous pouvez créer, consultez les rubriques suivantes :  
[Flux de données d'événement de facturation](data-feed-billing-event.md)
[Flux de données d'élément fiscal](data-feed-tax-item.md)

### Tableaux relatifs aux achats
<a name="procurement-tables"></a>

Le schéma suivant montre les champs de la table des accords dans le domaine des achats.

**Note**  
Pour plus d'informations sur ces tables, notamment une description de chaque champ de la table et des jointures qui peuvent être créées[Flux de données sur les accords](data-feed-agreements.md), consultez ce guide.

Les sections suivantes fournissent des diagrammes de *relations entre entités* (ER) pour chaque domaine. Chaque diagramme ER montre les tables et les champs de chaque table, ainsi que les champs que vous pouvez utiliser pour joindre les tables.

**Note**  
Les diagrammes ER présentés dans cette section n'incluent pas les champs communs à tous les flux de données. Pour plus d'informations sur les champs communs, consultez[Stockage et structure des flux de AWS Marketplace données](data-feed-details.md).

Le tableau suivant décrit les symboles utilisés dans les diagrammes ER.


| Symbol | Description | 
| --- | --- | 
|  ![\[Une image des lettres « PK » en tant que symbole.\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/images/datafeeds-primary-key.png)  |  **Clé primaire** : clé primaire pour la table. Lorsqu'il est utilisé avec les `update_date` champs `valid_from` et, il est unique. Pour plus de détails sur l'utilisation conjointe de ces champs, consultez[Historisation des données](data-feed-details.md#data-feed-historization). Si plusieurs champs sont marqués comme clé primaire, les champs forment ensemble la clé primaire.  | 
|  ![\[Une image des lettres « FK » en tant que symbole.\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/images/datafeeds-foreign-key.png)  |   **Clé étrangère** : champ qui représente une clé primaire dans une autre table. Pas nécessairement unique dans le tableau.   Dans certains cas, la clé étrangère peut être vide si l'enregistrement de la table en cours n'a pas d'enregistrement correspondant dans la table étrangère.   | 
|  ![\[Une image des lettres « AK » en tant que symbole.\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/images/datafeeds-alternate-key.png)  |   **Clé alternative** : clé qui peut être utilisée comme clé dans le tableau. Suit les mêmes règles d'unicité que la clé primaire.  | 
|  ![\[Image d'une ligne avec une croix à une extrémité et un cercle et une fourche à l'autre.\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/images/datafeeds-one-to-many.png)  |   **Connecteur** — Les lignes entre les champs représentent une connexion, c'est-à-dire deux champs qui peuvent être utilisés pour joindre des tables. Les extrémités de la ligne représentent le type de connexion. Cet exemple représente une one-to-many connexion.  | 

**Types de connecteurs**

Le tableau suivant indique les types d'extrémités que chaque connecteur peut avoir.


| Type de connecteur | Description | 
| --- | --- | 
|  ![\[Image d'une ligne avec une croix à une extrémité.\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/images/datafeeds-one-to-n.png)  |   **Un à n** — Un connecteur avec cette extrémité représente une jointure qui possède exactement une valeur de ce côté de la jointure.  | 
|  ![\[Image d'une ligne avec une croix et un cercle à une extrémité.\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/images/datafeeds-zero-or-one-to-n.png)  |   **Zéro ou un à n** : un connecteur avec cette extrémité représente une jointure dont la valeur est nulle ou une de ce côté de la jointure.  | 
|  ![\[Image d'une ligne avec un cercle et une fourche à une extrémité.\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/images/datafeeds-optional-many-to-n.png)  |   De **zéro ou plus à n** : un connecteur avec cette extrémité représente une jointure comportant zéro, une ou plusieurs valeurs de ce côté de la jointure.  | 
|  ![\[Image d'une ligne avec une croix et une fourche à une extrémité.\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/images/datafeeds-one-or-more-to-n.png)  |   **Un ou plusieurs vers n** — Un connecteur avec cette extrémité représente une jointure comportant une ou plusieurs valeurs de ce côté de la jointure.  | 

# Exemples de requêtes de flux de données
<a name="data-feed-full-examples"></a>

Cette section donne des exemples de requêtes complexes utilisant les flux de données fournis par AWS Marketplace. Ces exemples sont similaires à ceux [Tableaux de bord des vendeurs](dashboards.md) que vous obtenez du Portail de gestion AWS Marketplace. Vous pouvez personnaliser ces requêtes pour créer d'autres rapports dont vous avez besoin.

**Topics**
+ [Accords et renouvellements](#data-feed-example-agreements)
+ [Revenus facturés](#data-feed-example-billed-revenue)
+ [Factures non encaissées ou déboursées](#data-feed-example-collections)
+ [Factures taxées](#data-feed-example-tax)
+ [Déboursements par produit](#data-feed-example-disbursement-by-product)
+ [Rapport de rémunération des ventes](#data-feed-example-sales-compensation)

## Accords et renouvellements
<a name="data-feed-example-agreements"></a>

Pour trouver vos données d'accord et de renouvellement, vous pouvez exécuter un ensemble de requêtes comme dans l'exemple suivant. Les requêtes s'appuient les unes sur les autres pour créer le tableau de bord **des accords et des renouvellements**, section des données granulaires. Vous pouvez utiliser l'exemple tel qu'illustré ou le personnaliser en fonction de vos données et de vos cas d'utilisation.

Les commentaires contenus dans les requêtes expliquent à quoi servent les requêtes et comment les modifier.

```
      Query currently under development.
```

## Revenus facturés
<a name="data-feed-example-billed-revenue"></a>

Pour trouver les données de vos factures, vous pouvez exécuter un ensemble de requêtes comme dans l'exemple suivant. Les requêtes s'appuient les unes sur les autres pour créer le rapport sur les **recettes facturées**. Vous pouvez utiliser l'exemple tel qu'illustré ou le personnaliser en fonction de vos données et de vos cas d'utilisation.

Les commentaires contenus dans les requêtes expliquent à quoi servent les requêtes et comment les modifier.

```
-- Billed revenue report

-- General note: When executing this query we are assuming that the data ingested in the database uses 
-- two time axes (the valid_from column and the update_date column).
-- See documentation for more details: https://docs.aws.amazon.com/marketplace/latest/userguide/data-feed.html#data-feed-details

-- An account_id has several valid_from dates (each representing a separate revision of the data)
-- but because of bi-temporality, an account_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
with accounts_with_uni_temporal_data as (
  select
    account_id,
    aws_account_id,
    encrypted_account_id,
    mailing_address_id,
    tax_address_id,
    tax_legal_name,
    from_iso8601_timestamp(valid_from) as valid_from,
    tax_registration_number
  from
    (
      select
        account_id,
        aws_account_id,
        encrypted_account_id,
        mailing_address_id,
        tax_address_id,
        tax_legal_name,
        valid_from,
        delete_date,
        tax_registration_number,
        row_number() over (partition by account_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
      from
        accountfeed_v1
    )
    where
      -- keep latest ...
      row_num = 1
      -- ... and remove the soft-deleted one.
      and (delete_date is null or delete_date = '')
  ),

accounts_with_history as (
  with accounts_with_history_with_extended_valid_from as (
    select
      account_id,
      -- sometimes, this columns gets imported as a "bigint" and loses heading 0s -> casting to a char and re-adding heading 0s (if need be)
      substring('000000000000'||cast(aws_account_id as varchar),-12) as aws_account_id,
      encrypted_account_id,
      mailing_address_id,
      tax_address_id,
      tax_legal_name,
      -- The start time of account valid_from is extended to '1970-01-01 00:00:00', because:
      -- ... in tax report transformations, some tax line items with invoice_date cannot
      -- ... fall into the default valid time range of the associated account
      case
        when lag(valid_from) over (partition by account_id order by valid_from asc) is null
          then cast('1970-01-01 00:00:00' as timestamp)
        else valid_from
      end as valid_from
    from accounts_with_uni_temporal_data
    )
  select
    account_id,
    aws_account_id,
    encrypted_account_id,
    mailing_address_id,
    tax_address_id,
    tax_legal_name,
    valid_from,
    coalesce(
      lead(valid_from) over (partition by account_id order by valid_from asc),
      cast('2999-01-01 00:00:00' as timestamp)
    ) as valid_to
  from
    accounts_with_history_with_extended_valid_from
),

-- An address_id has several valid_from dates (each representing a separate revision of the data)
-- but because of bi-temporality, an account_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
address_with_uni_temporal_data as (
  select
    from_iso8601_timestamp(valid_from) as valid_from,
    address_id,
    company_name,
    email_domain,
    country_code,
    state_or_region,
    city,
    postal_code,
    row_num
  from
  (
    select
      valid_from,
      update_date,
      delete_date,
      address_id,
      company_name,
      email_domain,
      country_code,
      state_or_region,
      city,
      postal_code,
      row_number() over (partition by address_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
    from
      addressfeed_v1
  )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

-- We are only interested in the most recent tuple (BTW: a given address is not supposed to change over time but when bugs ;-) so this query mainly does nothing)
address_with_latest_revision as (
  select
    valid_from,
    address_id,
    company_name,
    email_domain,
    country_code,
    state_or_region,
    city,
    postal_code,
    row_num_latest_revision
  from
  (
    select
      valid_from,
      address_id,
      company_name,
      email_domain,
      country_code,
      state_or_region,
      city,
      postal_code,
      row_number() over (partition by address_id order by valid_from desc) as row_num_latest_revision
    from
      address_with_uni_temporal_data
  )
  where
    row_num_latest_revision = 1
),

accounts_with_history_with_company_name as (
  select
    awh.account_id,
    awh.aws_account_id,
    awh.encrypted_account_id,
    awh.mailing_address_id,
    awh.tax_address_id,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when address.company_name = '' then null else address.company_name end,
      awh.tax_legal_name) as mailing_company_name,
    address.email_domain,
    awh.valid_from,
    -- For BYOL, the agreement might be accepted (using some external non-AWS system or manual process) days before
    -- that BYOL agreement is entered into AWS Marketplace by the buyer. Therefore, the buyer is permitted to manually
    -- enter a backdated acceptance date, which might predate the point in time when the account was created.
    -- To work around this, we need to adjust the valid_from of the account to be
    -- earlier than the earliest possible backdated BYOL agreement acceptance date.
    case
      when lag(awh.valid_from) over (partition by aws_account_id order by awh.valid_from asc) is null
      then date_add('Day', -212, awh.valid_from)
      -- 212 is the longest delay between acceptance_date of the agreement and the account start_Date
      else awh.valid_from
    end as valid_from_adjusted,
    awh.valid_to
  from accounts_with_history as awh
  left join address_with_latest_revision as address on
    awh.mailing_address_id = address.address_id and awh.mailing_address_id is not null
),

-- An agreement_id has several valid_from dates (each representing an agreement revision)
-- but because of bi-temporality, an agreement_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
agreements_with_uni_temporal_data as (
  select
    agreement_id,
    origin_offer_id,
    proposer_account_id,
    acceptor_account_id,
    agreement_revision,
    from_iso8601_timestamp(valid_from) as valid_from,
    from_iso8601_timestamp(start_date) as start_date,
    from_iso8601_timestamp(end_date) as end_date,
    from_iso8601_timestamp(acceptance_date) as acceptance_date,
    agreement_type,
    previous_agreement_id,
    agreement_intent
  from
  (
    select
      --empty value in Athena shows as '', change all '' value to null
      case when agreement_id = '' then null else agreement_id end as agreement_id,
      origin_offer_id,
      proposer_account_id,
      acceptor_account_id,
      agreement_revision,
      valid_from,
      delete_date,
      start_date,
      end_date,
      acceptance_date,
      agreement_type,
      previous_agreement_id,
      agreement_intent,
      row_number() over (partition by agreement_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
    from
      -- TODO change to agreementfeed_v1 when Agreement Feed is GA'ed
      agreementfeed
  )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

agreements_with_history as (
  with agreements_with_window_functions as (
    select
      agreement_id,
      origin_offer_id as offer_id,
      proposer_account_id,
      acceptor_account_id,
      agreement_revision,
      start_date,
      end_date,
      acceptance_date,
      -- The start time of agreement valid_from is extended to '1970-01-01 00:00:00', because:
      -- ... in usage report transformations, some usage line items with usage_date cannot
      -- ... fall into the default valid time range of the associated agreement
      case
          when lag(valid_from) over (PARTITION BY agreement_id order by valid_from asc) is null
          then timestamp '1970-01-01 00:00:00'
          else valid_from
      end as valid_from,
      coalesce(
          lead(valid_from) over (partition by agreement_id order by valid_from asc),
          timestamp '2999-01-01 00:00:00'
      ) as valid_to,
      rank() over (partition by agreement_id order by valid_from asc) version,
      agreement_type,
      previous_agreement_id,
      agreement_intent
    from
      agreements_with_uni_temporal_data
  )
  select
    agreement_id,
    offer_id,
    proposer_account_id,
    acceptor_account_id,
    agreement_revision,
    start_date,
    end_date,
    acceptance_date,
    valid_from,
    case
        when version=1 and valid_from<timestamp '2023-03-03 06:16:08.743' then timestamp '1970-01-01'
        -- The following 60 minute adjustment is to handle special case where When Renewal happens for a contract
        when version=1 then date_add('minute',-60,valid_from)
        else valid_from
    end as valid_from_adjusted,
    valid_to,
    agreement_type,
    previous_agreement_id,
    agreement_intent
  from
    agreements_with_window_functions
),

-- An offer_id has several valid_from dates (each representing an offer revision)
-- but because of bi-temporality, an offer_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
offers_with_uni_temporal_data as (
  select
    from_iso8601_timestamp(valid_from) as valid_from,
    from_iso8601_timestamp(update_date) as update_date,
    from_iso8601_timestamp(delete_date) as delete_date,
    offer_id,
    offer_revision,
    name,
    expiration_date,
    opportunity_id,
    opportunity_name,
    opportunity_description,
    seller_account_id
  from
  (
    select
      valid_from,
      update_date,
      delete_date,
      offer_id,
      offer_revision,
      name,
      expiration_date,
      opportunity_id,
      opportunity_name,
      opportunity_description,
      seller_account_id,
      row_number() over (partition by offer_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
    from
      offerfeed_v1
  )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

-- Here, we build the validity time range (adding valid_to on top of valid_from) of each offer revision.
-- We will use it to get Offer name at invoice time.
-- NB: If you'd rather get "current" offer name, un-comment "offers_with_latest_revision"
offers_with_history as (
  select
    offer_id,
    offer_revision,
    name,
    opportunity_id,
    opportunity_name,
    opportunity_description,
    valid_from,
    -- When we try to look up an offer revision as at the acceptance date of a BYOL agreement, we run into a problem.
    -- For BYOL, the agreement might be accepted (using some external non-AWS system or manual process) days before
    -- that BYOL agreement is entered into AWS Marketplace by the buyer. Therefore, the buyer is permitted to manually
    -- enter a backdated acceptance date, which might predate the point in time when the first revision of the offer
    -- was created. To work around this, we need to adjust the valid_from on the first revision of the offer to be
    -- earlier than the earliest possible backdated BYOL agreement acceptance date.
    case
      when lag(valid_from) over (partition by offer_id order by valid_from asc) is null and valid_from<cast('2021-04-01' as timestamp)
      then date_add('Day', -3857, valid_from)
      -- 3857 is the longest delay between acceptance_date of an agreement and the first revision of the offer
      when lag(valid_from) over (partition by offer_id order by valid_from asc) is null and valid_from >= cast('2021-04-01' as timestamp)
      then date_add('Day', -1460, valid_from)
      --after 2021 for the two offers we need to adjust for 2 more years
      else valid_from
    end as valid_from_adjusted,
    coalesce(
      lead(valid_from) over (partition by offer_id order by valid_from asc),
      cast('2999-01-01 00:00:00' as timestamp))
    as valid_to
  from offers_with_uni_temporal_data
),
-- provided for reference only if you are interested into get "current" offer name
-- (ie. not used afterwards)
offers_with_latest_revision as (
  select
    offer_id,
    offer_revision,
    name,
    opportunity_name,
    opportunity_description,
    valid_from,
    null valid_to
  from
  (
    select
      offer_id,
      offer_revision,
      name,
      opportunity_name,
      opportunity_description,
      valid_from,
      null valid_to,
      row_number() over (partition by offer_id order by valid_from desc) as row_num_latest_revision
    from
      offers_with_uni_temporal_data
  )
  where
    row_num_latest_revision = 1
),

-- An offer_target_id has several valid_from dates (each representing an offer revision)
-- but because of bi-temporality, an offer_target_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
offer_targets_with_uni_temporal_data as (
  select
    from_iso8601_timestamp(valid_from) as valid_from,
    from_iso8601_timestamp(update_date) as update_date,
    from_iso8601_timestamp(delete_date) as delete_date,
    offer_target_id,
    offer_id,
    offer_revision,
    target_type,
    polarity,
    value
  from
  (
    select
      valid_from,
      update_date,
      delete_date,
      offer_target_id,
      offer_id,
      offer_revision,
      target_type,
      polarity,
      value,
      row_number() over (partition by offer_target_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
    from
      offertargetfeed_v1
  )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

offer_target_type as (
  select
    offer_id,
    offer_revision,
    substring(
      -- The first character indicates the priority (lower value means higher precedence):
      min(
        case
          when offer_target.target_type='BuyerAccounts' then '1Private'
          when offer_target.target_type='ParticipatingPrograms' then '2Program:'||cast(offer_target.value as varchar)
          when offer_target.target_type='CountryCodes' then '3GeoTargeted'
          -- well, there is no other case today, but rather be safe...
          else '4Other Targeting'
        end
      ),
      -- Remove the first character that was only used for th priority in the "min" aggregate function:
      2
    ) as offer_target
  from
    offer_targets_with_uni_temporal_data as offer_target
  group by
    offer_id,
    offer_revision
),

offers_with_history_with_target_type as (
  select
    offer.offer_id,
    offer.offer_revision,
    -- even though today it is not possible to combine several types of targeting in a single offer, let's ensure the query is still predictable if this gets possible in the future
    max(
      case
        when off_tgt.offer_target is null then 'Public'
        else off_tgt.offer_target
      end
    ) as offer_target,
    min(offer.name) as name,
    min(offer.opportunity_name) as opportunity_name,
    min(offer.opportunity_description) as opportunity_description,
    offer.valid_from,
    offer.valid_from_adjusted,
    offer.valid_to,
    offer.opportunity_id
  from
    offers_with_history as offer
  left join offer_target_type as off_tgt on
    offer.offer_id = off_tgt.offer_id
    and offer.offer_revision = off_tgt.offer_revision
  group by
    offer.offer_id,
    offer.offer_revision,
    offer.valid_from,
    offer.valid_from_adjusted,
    offer.valid_to,
    offer.opportunity_id
),

-- provided for reference only if you are interested into get "current" offer targets
-- (ie. not used afterwards)
offers_with_latest_revision_with_target_type as (
  select
    offer.offer_id,
    offer.offer_revision,
    -- even though today it is not possible to combine several types of targeting in a single offer, let's ensure the query is still predictable if this gets possible in the future
    max(
      distinct
      case
        when off_tgt.target_type is null then 'Public'
        when off_tgt.target_type='BuyerAccounts' then 'Private'
        when off_tgt.target_type='ParticipatingPrograms' then 'Program:'||cast(off_tgt.value as varchar)
        when off_tgt.target_type='CountryCodes' then 'GeoTargeted'
        -- well, there is no other case today, but rather be safe...
        else 'Other Targeting'
      end
    ) as offer_target,
    min(offer.name) as name,
    min(offer.opportunity_name) as opportunity_name,
    min(offer.opportunity_description) as opportunity_description,
    offer.valid_from,
    offer.valid_to
  from
    offers_with_latest_revision offer
    -- left joining because public offers don't have targets
    left join offer_targets_with_uni_temporal_data off_tgt on
      offer.offer_id=off_tgt.offer_id and offer.offer_revision=off_tgt.offer_revision
  group by
    offer.offer_id,
    offer.offer_revision,
    -- redundant with offer_revision, as each revision has a dedicated valid_from (but cleaner in the group by)
    offer.valid_from,
    offer.valid_to
),

-- A product_id has several valid_from dates (each representing a product revision),
-- but because of bi-temporality, each product_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
products_with_uni_temporal_data as (
  select
    from_iso8601_timestamp(valid_from) as valid_from,
    from_iso8601_timestamp(update_date) as update_date,
    from_iso8601_timestamp(delete_date) as delete_date,
    product_id,
    manufacturer_account_id,
    product_code,
    title
  from
  (
    select
      valid_from,
      update_date,
      delete_date,
      product_id,
      manufacturer_account_id,
      product_code,
      title,
      row_number() over (partition by product_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
    from
      productfeed_v1
  )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

products_with_history as (
  select
    product_id,
    title,
    valid_from,
    -- Offerv2 can have upto 50 years and Offerv3 is upto 5 years of past date
    case
      when lag(valid_from) over (partition by product_id order by valid_from asc) is null and valid_from<cast('2021-04-01' as timestamp)
        then date_add('Day', -3857, valid_from)
      -- 3827 is the longest delay between acceptance_date of an agreement and the product
      -- we are keeping 3857 as a consistency between the offers and products
      when lag(valid_from) over (partition by product_id order by valid_from asc) is null and valid_from >= cast('2021-04-01' as timestamp)
        then date_add('Day', -2190, valid_from)
      --after 2021 for the two offers we need to adjust for 2 more years
      else valid_from 
    end as valid_from_adjusted,
    coalesce(
      lead(valid_from) over (partition by product_id order by valid_from asc),
      cast('2999-01-01 00:00:00' as timestamp)
    ) as valid_to,
    product_code,
    manufacturer_account_id
  from
    products_with_uni_temporal_data
),

legacy_products as (
  select
    legacy_id,
    new_id
  from
    legacyidmappingfeed_v1
  where
    mapping_type='PRODUCT'
  group by
    legacy_id,
    new_id
),

-- A given billing_event_id represents an accounting event and thus has only one valid_from date,
-- but because of bi-temporality, a billing_event_id (+ its valid_from) can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
billing_events_with_uni_temporal_data as (
  select
    billing_event_id,
    valid_from,
    update_date,
    delete_date,
    invoice_date,
    transaction_type,
    transaction_reference_id,
    parent_billing_event_id,
    bank_trace_id,
    broker_id,
    product_id,
    disbursement_billing_event_id,
    action,
    from_account_id,
    to_account_id,
    end_user_account_id,
    billing_address_id,
    amount,
    currency,
    balance_impacting,
    --empty value in Athena shows as '', change all '' value to null
    case when agreement_id = '' then null else agreement_id end as agreement_id,
    invoice_id,
    payment_due_date,
    usage_period_start_date,
    usage_period_end_date,
    buyer_transaction_reference_id,
    row_num
  from
  (
    select
      billing_event_id,
      from_iso8601_timestamp(valid_from) as valid_from,
      from_iso8601_timestamp(update_date) as update_date,
      delete_date,
      from_iso8601_timestamp(invoice_date) as invoice_date,
      transaction_type,
      transaction_reference_id,
      parent_billing_event_id,
      -- casting in case data was imported as number
      cast(bank_trace_id as varchar) as bank_trace_id,
      broker_id,
      product_id,
      disbursement_billing_event_id,
      action,
      from_account_id,
      to_account_id,
      end_user_account_id,
      billing_address_id,
      -- casting in case data was imported as varchar
      cast(amount as decimal(38,6)) as amount,
      currency,
      balance_impacting,
      agreement_id,
      invoice_id,
      case when payment_due_date is null or payment_due_date = '' then null else from_iso8601_timestamp(payment_due_date) end as payment_due_date,
      from_iso8601_timestamp(usage_period_start_date) as usage_period_start_date,
      from_iso8601_timestamp(usage_period_end_date) as usage_period_end_date,
      buyer_transaction_reference_id,
      row_number() over (partition by billing_event_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
    from
      billingeventfeed_v1
  )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

-- Here we select the account_id of the current seller (We identify this by looking for the to_account_id related to revenue transactions).
-- We will use it later to distinguish own agreements from agreements generated by channel partners.
seller_account as (
  select
    from_account_id as seller_account_id
  from
    billing_events_with_uni_temporal_data bill
  where
    -- Assumption here is only seller will pay listing fee. As of 12/21/2021, there are cases that Channel partner have 0 listing fee for CPPO, so the amount could be 0.
    bill.transaction_type like 'AWS_REV_SHARE' and amount <= 0 and action = 'INVOICED'
  group by
    -- from_account_id is always the same for all those "listing fee" transactions == the seller of record himself.
    -- If this view returns more than 1 record, the overall query will fail (on purpose). Please contact AWS Marketplace if this happens.
    from_account_id
),

billing_event_with_business_flags as (
  select
    bl.billing_event_id,
    bl.end_user_account_id,
    bl.agreement_id,
    aggrement.proposer_account_id,
    aggrement.offer_id,
    aggrement.acceptor_account_id,
    case
      -- For AWS and BALANCE_ADJUSTMENT, the billing event feed will show the "AWS Marketplace" account as the
      -- receiver of the funds and the seller as the payer. We are not interested in this information here.
      -- Null values will be ignored by the `max` aggregation function.
      when bl.transaction_type like 'AWS%' then null
      -- For BALANCE_ADJUSTMENT, payer is seller themselves
      when bl.invoice_id is null then bl.to_account_id
      -- We get the payer of the invoice from *any* transaction type that is not AWS and not BALANCE_ADJUSTMENT (because they are the same for a given end user + agreement + product).
      else bl.from_account_id
    end as payer_account_id,
    bl.product_id,
    bl.action,
    bl.transaction_type,
    bl.parent_billing_event_id,
    bl.disbursement_billing_event_id,
    bl.amount,
    bl.currency,
    bl.balance_impacting,
    bl.invoice_date,
    bl.payment_due_date,
    bl.usage_period_start_date,
    bl.usage_period_end_date,
    bl.invoice_id,
    bl.billing_address_id,
    bl.transaction_reference_id,
    bl.buyer_transaction_reference_id,
    case when disbursement.bank_trace_id = 'EMEA_MP_TEST_TRACE_ID' then null else disbursement.bank_trace_id end as bank_trace_id,
    case when disbursement.bank_trace_id = 'EMEA_MP_TEST_TRACE_ID' then null else disbursement.invoice_date end as disbursement_date,
    disbursement.billing_event_id as disbursement_id,
    -- We will use disbursement_id_or_invoiced as part of the PK, so it cannot be null:
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when disbursement.billing_event_id = '' then null else disbursement.billing_event_id end,
      '<invoiced>') as disbursement_id_or_invoiced,
    bl.broker_id,
    case
      when bl.invoice_id is null /* transaction_type = 'BALANCE_ADJUSTMENT' */
        then (select seller_account_id from seller_account) ||':'|| cast(bl.invoice_date as varchar)
      else bl.buyer_transaction_reference_id
        ||'-'|| case when bl.agreement_id is null or bl.agreement_id = '' then ' ' else bl.agreement_id end
        ||'-'|| case when bl.end_user_account_id is null or bl.end_user_account_id = '' then ' ' else bl.end_user_account_id end
        ||'-'|| coalesce(cast(bl.usage_period_start_date as varchar),' ')
        ||'-'|| coalesce(cast(bl.usage_period_end_date as varchar),' ')
    end as internal_buyer_line_item_id,
    bl.buyer_transaction_reference_id <> bl.transaction_reference_id as is_seller_invoice,
    case when bl.transaction_type = 'SELLER_REV_SHARE' and (select seller_account_id from seller_account) <> bl.to_account_id then true else false end as is_cog,
    case when bl.transaction_type in('SELLER_REV_SHARE_CREDIT', 'SELLER_REV_SHARE_REFUND') and (select seller_account_id from seller_account) <> bl.to_account_id then true else false end as is_cog_refund,
    --TODO: replace below logic once we can create a logic the identify reseller/manufacturer without depending on agreement feed
    case when aggrement.proposer_account_id <> (select seller_account_id from seller_account) then true else false end as is_manufacturer_view_of_reseller
  from
    billing_events_with_uni_temporal_data as bl
    left join billing_events_with_uni_temporal_data as disbursement on
      disbursement.transaction_type like 'DISBURSEMENT%'
        and disbursement.action = 'DISBURSED'
        and disbursement.transaction_type IN ('DISBURSEMENT', 'DISBURSEMENT_FAILURE')
        and bl.disbursement_billing_event_id = disbursement.billing_event_id
    left join agreements_with_history as aggrement on
      bl.agreement_id = aggrement.agreement_id
        and bl.invoice_date >= aggrement.valid_from_adjusted
        and bl.invoice_date<aggrement.valid_to
    left join accounts_with_history awh on
      bl.to_account_id = awh.account_id
        and bl.invoice_date >= awh.valid_from
        and bl.invoice_date<awh.valid_to
  where
    bl.transaction_type not like 'DISBURSEMENT%' and
      (bl.agreement_id is null or bl.agreement_id = ''
      or aggrement.agreement_id is not null)
),

-- listagg function in athena does not support partitioning, grouping here and then joining to the main query
seller_invoice_list as (
  select
    internal_buyer_line_item_id,
    listagg(case when not is_seller_invoice then null else invoice_id end,',') within group (order by case when not is_seller_invoice then null else cast(invoice_date as varchar) end) as seller_invoice_id_or_null,
    listagg(case when not is_seller_invoice then null else cast(invoice_date as varchar) end,',') within group (order by case when not is_seller_invoice then null else cast(invoice_date as varchar) end) as seller_invoice_date_or_null
  from
    (
    -- listagg function in athena does not support ordering by another field when distinct is used,
    -- here we first select distinct invoices and then do the listagg order by invoice_date
    select distinct internal_buyer_line_item_id, is_seller_invoice, invoice_id, invoice_date
    from billing_event_with_business_flags) distinct_invoices
  group by internal_buyer_line_item_id
  order by internal_buyer_line_item_id
),

billing_event_with_categorized_transaction as (
-- Use the flags that were created in the previous transformation in more calculated columns:
-- NOTE: This transformation has no joins and no window functions
  select
    billing_event_id,
    end_user_account_id,
    agreement_id,
    proposer_account_id,
    offer_id,
    acceptor_account_id,
    case when is_cog or is_cog_refund then null else payer_account_id end as payer_account_id,
    product_id,
    action,
    transaction_type,
    parent_billing_event_id,
    disbursement_billing_event_id,
    amount,
    currency,
    balance_impacting,
    invoice_date,
    payment_due_date,
    usage_period_start_date,
    usage_period_end_date,
    invoice_id,
    billing_address_id,
    transaction_reference_id,
    buyer_transaction_reference_id,
    bank_trace_id,
    disbursement_date,
    disbursement_id,
    disbursement_id_or_invoiced,
    broker_id,
    bl.internal_buyer_line_item_id,
    is_seller_invoice,
    is_cog,
    is_cog_refund,
    is_manufacturer_view_of_reseller,

    -- Buyer/seller columns:
    case when is_seller_invoice then null else invoice_id end as buyer_invoice_id_or_null,
    seller_invoices.seller_invoice_id_or_null,
    case when is_seller_invoice then null else invoice_date end as buyer_invoice_date_or_null,
    seller_invoices.seller_invoice_date_or_null,

    -- Categorized amounts by transaction type:
    case when transaction_type =   'SELLER_REV_SHARE' and not is_cog then amount else 0 end as gross_revenue,
    case when transaction_type in ('SELLER_REV_SHARE_REFUND','SELLER_REV_SHARE_CREDIT') and not is_cog_refund then amount else 0 end as gross_refund,
    case when transaction_type =   'SELLER_REV_SHARE' and     is_cog then amount else 0 end as cogs,
    case when transaction_type in ('SELLER_REV_SHARE_REFUND','SELLER_REV_SHARE_CREDIT') and     is_cog_refund then amount else 0 end as cogs_refund,
    case when transaction_type =   'AWS_REV_SHARE' then amount else 0 end as aws_rev_share,
    case when transaction_type in ('AWS_REV_SHARE_REFUND','AWS_REV_SHARE_CREDIT') then amount else 0 end as aws_refund_share,
    case when transaction_type =   'AWS_TAX_SHARE' and not is_seller_invoice then amount else 0 end as aws_tax_share,             -- AWS tax share from _buyer_  invoice
    case when transaction_type =   'AWS_TAX_SHARE' and     is_seller_invoice then amount else 0 end as aws_tax_share_listing_fee, -- AWS tax share from _seller_ invoice
    case when transaction_type =   'AWS_TAX_SHARE_REFUND' and not is_seller_invoice then amount else 0 end as aws_tax_share_refund,
    case when transaction_type =   'AWS_TAX_SHARE_REFUND' and     is_seller_invoice then amount else 0 end as aws_tax_share_refund_listing_fee,
    case when transaction_type =   'SELLER_TAX_SHARE' then amount else 0 end as seller_tax_share,
    case when transaction_type =   'SELLER_TAX_SHARE_REFUND' then amount else 0 end as seller_tax_share_refund,
    case when transaction_type =   'BALANCE_ADJUSTMENT' then amount else 0 end as balance_adjustment,
    case when transaction_type =   'SELLER_REV_SHARE_CREDIT' then amount else 0 end as seller_rev_credit,
    case when transaction_type =   'AWS_REV_SHARE_CREDIT' then amount else 0 end as aws_ref_fee_credit
  from
    billing_event_with_business_flags as bl
    left join seller_invoice_list as seller_invoices
      on bl.internal_buyer_line_item_id = seller_invoices.internal_buyer_line_item_id
    ),

line_items_aggregated as (
-- This transformation has the only "group by" in all of these transformations.
-- NOTE: This transformation has no joins and no window functions
  select
    internal_buyer_line_item_id,
    disbursement_id,
    disbursement_id_or_invoiced,
    product_id,
    broker_id,
    currency,
    agreement_id,
    proposer_account_id,
    acceptor_account_id,
    max(payer_account_id) as payer_account_id,
    offer_id,
    end_user_account_id,
    usage_period_start_date,
    usage_period_end_date,
    max(payment_due_date) payment_due_date,
    buyer_transaction_reference_id,
    bank_trace_id,
    disbursement_date,
    max(billing_address_id) as billing_address_id,

    -- Buyer/seller columns:
    max(buyer_invoice_id_or_null) as buyer_invoice_id,
    max(seller_invoice_id_or_null) as seller_invoice_id,
    max(buyer_invoice_date_or_null) as buyer_invoice_date,
    max(seller_invoice_date_or_null) as seller_invoice_date,
  
    -- Categorized amounts by transaction type:
    -- When disbursement_id_or_invoiced = '<invoiced>',    these are invoiced amounts
    -- When disbursement_id_or_invoiced <> '<invoiced>' these are disbursed amounts for _this_ specific disbursement_id
    sum(gross_revenue) as gross_revenue_this_disbursement_id_or_invoiced,
    sum(gross_refund) as gross_refund_this_disbursement_id_or_invoiced,
    sum(cogs) as cogs_this_disbursement_id_or_invoiced,
    sum(cogs_refund) as cogs_refund_this_disbursement_id_or_invoiced,
    sum(aws_rev_share) as aws_rev_share_this_disbursement_id_or_invoiced,
    sum(aws_refund_share) as aws_refund_share_this_disbursement_id_or_invoiced,
    sum(aws_tax_share) as aws_tax_share_this_disbursement_id_or_invoiced,
    sum(aws_tax_share_listing_fee) as aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,
    sum(aws_tax_share_refund) as aws_tax_share_refund_this_disbursement_id_or_invoiced,
    sum(aws_tax_share_refund_listing_fee) as aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,
    sum(seller_tax_share) as seller_tax_share_this_disbursement_id_or_invoiced,
    sum(seller_tax_share_refund) as seller_tax_share_refund_this_disbursement_id_or_invoiced,
    sum(balance_adjustment) as balance_adjustment_this_disbursement_id_or_invoiced,
    sum(seller_rev_credit) as seller_rev_credit_this_disbursement_id_or_invoiced,
    sum(aws_ref_fee_credit) as aws_ref_fee_credit_this_disbursement_id_or_invoiced
  from
    billing_event_with_categorized_transaction as billing_categorized
  group by
    internal_buyer_line_item_id,
    disbursement_id,
    disbursement_id_or_invoiced,
    broker_id,
    -- The following columns are included the in group by but they are intentionally omitted from the PK.
    -- These columns should have the _same_ values for each record in the PK.
    product_id,
    currency,
    agreement_id,
    proposer_account_id,
    acceptor_account_id,
    offer_id,
    end_user_account_id,
    usage_period_start_date,
    usage_period_end_date,
    buyer_transaction_reference_id,
    bank_trace_id,
    disbursement_date
),

-- listagg function in athena does not support partitioning, grouping here and then joining to the main query
disbursement_list as (
  select
    internal_buyer_line_item_id,
    listagg(cast(disbursement_date as varchar),',') within group (order by cast(disbursement_date as varchar)) as disbursement_date_list,
    listagg(bank_trace_id,',') within group (order by cast(disbursement_date as varchar)) as disburse_bank_trace_id_list
    from (
    -- listagg function in athena does not support ordering by another field when distinct is used,
    -- here we first select distinct bank_trace_ids and then do the listagg order by disbursement_date
    select distinct internal_buyer_line_item_id, disbursement_date, bank_trace_id
  from billing_event_with_business_flags) distinct_disbursements
  group by internal_buyer_line_item_id
  order by internal_buyer_line_item_id
),

line_items_with_window_functions as (
--add flag next step compare gross_revenue and gross_revenue_disbursed or gross_refund and gross_refund_disbursed
  select
    line_item.internal_buyer_line_item_id,
    disbursement_id,
    disbursement_id_or_invoiced,
    product_id,
    broker_id,
    currency,
    agreement_id,
    proposer_account_id,
    acceptor_account_id,
    -- when there's aws_rev_Share adjustment/refund to a seller_rev_share invoice, it can happen that for the same aws_rev_share invoice_id, there are multiple disbursement events,
    -- using windows function to map payer_account_id of seller_rev_share to all corresponding aws_rev_Share
    max(payer_account_id) over (partition by line_item.internal_buyer_line_item_id) as payer_account_id,
    offer_id,
    end_user_account_id,
    usage_period_start_date,
    usage_period_end_date,
    payment_due_date,
    bank_trace_id,
    disbursement_date,
    billing_address_id,

    -- Buyer/seller columns:
    max(buyer_invoice_id) over (partition by line_item.internal_buyer_line_item_id) as buyer_invoice_id,
    seller_invoice_id,
    max(buyer_invoice_date) over (partition by line_item.internal_buyer_line_item_id) as buyer_invoice_date,
    seller_invoice_date,

    -- When disbursement_id_or_invoiced = '<invoiced>', these are actually invoiced amounts
    -- When disbursement_id_or_invoiced <> '<invoiced>' these are disbursed amounts for _this_ specific disbursement_id
    gross_revenue_this_disbursement_id_or_invoiced,
    gross_refund_this_disbursement_id_or_invoiced,
    cogs_this_disbursement_id_or_invoiced,
    cogs_refund_this_disbursement_id_or_invoiced,
    aws_rev_share_this_disbursement_id_or_invoiced,
    aws_refund_share_this_disbursement_id_or_invoiced,
    aws_tax_share_this_disbursement_id_or_invoiced,
    aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,
    seller_tax_share_this_disbursement_id_or_invoiced,
    seller_tax_share_refund_this_disbursement_id_or_invoiced,
    balance_adjustment_this_disbursement_id_or_invoiced,
    seller_rev_credit_this_disbursement_id_or_invoiced,
    aws_ref_fee_credit_this_disbursement_id_or_invoiced,
    -- IMPORTANT: All window functions partitioned by internal_buyer_line_item_id:

    -- Invoiced amounts, categorized by transaction type:
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then gross_revenue_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end)over (partition by line_item.internal_buyer_line_item_id) as gross_revenue_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then gross_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as gross_refund_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then cogs_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then cogs_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_refund_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then aws_rev_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_rev_share_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then aws_refund_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_refund_share_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then aws_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then aws_tax_share_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_listing_fee_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then aws_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_listing_fee_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then seller_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then seller_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_refund_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then balance_adjustment_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as balance_adjustment_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then seller_rev_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_rev_credit_invoiced,
    sum(case when disbursement_id_or_invoiced = '<invoiced>' then aws_ref_fee_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_ref_fee_credit_invoiced,

    -- Total disbursed amounts (for all disbursement_id values), categorized by transaction type:
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then gross_revenue_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as gross_revenue_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then gross_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as gross_refund_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then cogs_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then cogs_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_refund_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then aws_rev_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_rev_share_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then aws_refund_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_refund_share_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then aws_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then aws_tax_share_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_listing_fee_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then aws_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_listing_fee_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then seller_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then seller_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_refund_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then balance_adjustment_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as balance_adjustment_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then seller_rev_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_rev_credit_disbursed,
    sum(case when disbursement_id_or_invoiced <> '<invoiced>' then aws_ref_fee_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_ref_fee_credit_disbursed,

    -- aggregate multiple disbursement
    max(disbursement_date) over (partition by line_item.internal_buyer_line_item_id) as last_disbursement_date,
    first_value(case when disbursement_id_or_invoiced = '<invoiced>' then null else disbursement_id_or_invoiced end) over(partition by line_item.internal_buyer_line_item_id order by coalesce(disbursement_date,cast('1900-01-01' as timestamp)) desc rows between unbounded preceding and unbounded following) as last_disbursement_id,
    first_value(bank_trace_id) over (partition by line_item.internal_buyer_line_item_id order by coalesce(disbursement_date,cast('1900-01-01' as timestamp)) desc rows between unbounded preceding and unbounded following) as last_disburse_bank_trace_id,
    disb_list.disbursement_date_list,
    disb_list.disburse_bank_trace_id_list
  from
    line_items_aggregated as line_item
    left join disbursement_list disb_list
      on line_item.internal_buyer_line_item_id = disb_list.internal_buyer_line_item_id
),

cppo_offer_id as (
  select
    -- Channel partner offers do not exist in offertargetfeed_v1 table (as per legal requirement), causing cppo offer be defined as 'Public' in previous step, we will convert them back to 'Private' in next step
    offer_id
  from
    offers_with_uni_temporal_data
  where
    -- seller_account_id is null means the ISV owns the offer
    seller_account_id is not null
    and seller_account_id <>  (select seller_account_id from seller_account)
  group by
    offer_id
),

line_items_with_window_functions_enrich_offer_product_address as (
  select
    internal_buyer_line_item_id,
    disbursement_id,
    disbursement_id_or_invoiced,
    line.product_id,
    legacy_product.legacy_id as legacy_product_id,
    products.title as product_title,
    line.broker_id,
    line.currency,
    line.end_user_account_id,
    acc_enduser.encrypted_account_id as end_user_encrypted_account_id,
    acc_enduser.aws_account_id as end_user_aws_account_id,
    acc_payer.aws_account_id as payer_aws_account_id,
    acc_payer.encrypted_account_id payer_encrypted_account_id,
    line.agreement_id,
    agreement.agreement_revision,
    line.proposer_account_id,
    case when offer.offer_id like 'aiqoffer-%' then null else agreement.start_date end as Agreement_Start_Date,
    case when offer.offer_id like 'aiqoffer-%' then null else agreement.end_date end as Agreement_End_Date,
    case when offer.offer_id like 'aiqoffer-%' then null else agreement.acceptance_date end as Agreement_Acceptance_Date,
    case when offer.offer_id like 'aiqoffer-%' then null else agreement.valid_from end as agreement_updated_date,
    case when offer.offer_id like 'aiqoffer-%' then null else line.usage_period_start_date end as Usage_Period_Start_Date,
    case when offer.offer_id like 'aiqoffer-%' then null else line.usage_period_end_date end as Usage_Period_End_Date,

    line.acceptor_account_id,
    acc_subscriber.aws_account_id as subscriber_aws_account_id,
    acc_subscriber.encrypted_account_id as subscriber_encrypted_account_id,
    offer.offer_id,
    case
      when offer.offer_id in (
        select distinct offer_id
        from cppo_offer_id)
        then 'Private'
      else offer.offer_target
    end as offer_target,
    offer.name offer_name,
    offer.opportunity_name offer_opportunity_name,
    offer.opportunity_description offer_opportunity_description,
    offer.opportunity_id,
    payment_due_date,
    line.bank_trace_id,
    disbursement_date,
    billing_address_id,
    buyer_invoice_id,
    seller_invoice_id,
    buyer_invoice_date,
    seller_invoice_date,
    gross_revenue_this_disbursement_id_or_invoiced,
    gross_refund_this_disbursement_id_or_invoiced,
    cogs_this_disbursement_id_or_invoiced,
    cogs_refund_this_disbursement_id_or_invoiced,
    aws_rev_share_this_disbursement_id_or_invoiced,
    aws_refund_share_this_disbursement_id_or_invoiced,
    aws_tax_share_this_disbursement_id_or_invoiced,
    aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,
    seller_tax_share_this_disbursement_id_or_invoiced,
    seller_tax_share_refund_this_disbursement_id_or_invoiced,
    balance_adjustment_this_disbursement_id_or_invoiced,
    seller_rev_credit_this_disbursement_id_or_invoiced,
    aws_ref_fee_credit_this_disbursement_id_or_invoiced,
    gross_revenue_invoiced,
    gross_refund_invoiced,
    cogs_invoiced,
    cogs_refund_invoiced,
    aws_rev_share_invoiced,
    aws_refund_share_invoiced,
    aws_tax_share_invoiced,
    aws_tax_share_listing_fee_invoiced,
    aws_tax_share_refund_invoiced,
    aws_tax_share_refund_listing_fee_invoiced,
    seller_tax_share_invoiced,
    seller_tax_share_refund_invoiced,
    balance_adjustment_invoiced,
    seller_rev_credit_invoiced,
    aws_ref_fee_credit_invoiced,
    gross_revenue_disbursed,
    gross_refund_disbursed,
    cogs_disbursed,
    cogs_refund_disbursed,
    aws_rev_share_disbursed,
    aws_refund_share_disbursed,
    aws_tax_share_disbursed,
    aws_tax_share_listing_fee_disbursed,
    aws_tax_share_refund_disbursed,
    aws_tax_share_refund_listing_fee_disbursed,
    seller_tax_share_disbursed,
    seller_tax_share_refund_disbursed,
    balance_adjustment_disbursed,
    seller_rev_credit_disbursed,
    aws_ref_fee_credit_disbursed,
    last_disbursement_date,
    last_disbursement_id,
    last_disburse_bank_trace_id,
    disbursement_date_list,
    disburse_bank_trace_id_list,
    products.product_code,
    acc_products.aws_account_id as manufacturer_aws_account_id,
    products.manufacturer_account_id,
    --add subscriber and payer addressID, payer address preference order: tax address > billing address > mailing address,  subscriber address preference order: tax address >  mailing address
    coalesce (
      --empty value in Athena shows as '', change all '' value to null in order to follow the preference order logic above
      case when acc_subscriber.tax_address_id ='' then null else acc_subscriber.tax_address_id end,
      case when acc_subscriber.mailing_address_id = '' then null else acc_subscriber.mailing_address_id end) as subscriber_address_id,
    coalesce (
      case when acc_payer.tax_address_id = '' then null else acc_payer.tax_address_id end,
      case when line.billing_address_id = '' then null else line.billing_address_id end,
      case when acc_payer.mailing_address_id = '' then null else acc_payer.mailing_address_id end) as payer_address_id,
    coalesce (
      case when acc_enduser.tax_address_id = '' then null else acc_enduser.tax_address_id end,
      case when line.billing_address_id = '' then null else line.billing_address_id end,
      case when acc_enduser.mailing_address_id = '' then null else acc_enduser.mailing_address_id end) as end_user_address_id
  from
    line_items_with_window_functions as line
  left join agreements_with_history as agreement on
      line.agreement_id = agreement.agreement_id and line.buyer_invoice_date >= agreement.valid_from_adjusted and line.buyer_invoice_date<agreement.valid_to
  left join offers_with_history_with_target_type as offer on
        line.offer_id = offer.offer_id and line.buyer_invoice_date >= offer.valid_from and line.buyer_invoice_date<offer.valid_to
  left join products_with_history as products on
        line.product_id = products.product_id and line.buyer_invoice_date >= products.valid_from_adjusted and line.buyer_invoice_date<products.valid_to
  left join legacy_products as legacy_product on
        line.product_id = legacy_product.new_id
  left join accounts_with_history_with_company_name as acc_payer on
        line.payer_account_id = acc_payer.account_id and line.buyer_invoice_date >= acc_payer.valid_from and line.buyer_invoice_date<acc_payer.valid_to
  left join accounts_with_history_with_company_name as acc_enduser on
        line.end_user_account_id = acc_enduser.account_id and line.buyer_invoice_date >= acc_enduser.valid_from and line.buyer_invoice_date<acc_enduser.valid_to
  left join accounts_with_history_with_company_name as acc_subscriber on
        line.acceptor_account_id = acc_subscriber.account_id and line.buyer_invoice_date >= acc_subscriber.valid_from and line.buyer_invoice_date<acc_subscriber.valid_to
  left join accounts_with_history_with_company_name as acc_products on
        products.manufacturer_account_id = acc_products.account_id and line.buyer_invoice_date >= acc_products.valid_from and line.buyer_invoice_date<acc_products.valid_to

),

line_items_with_window_functions_enrich_offer_product_address_name as (
  select
    line.internal_buyer_line_item_id,
    disbursement_id,
    disbursement_id_or_invoiced,
    product_id,
    legacy_product_id,
    product_title,
    broker_id,
    currency,
    end_user_address_id,
    end_user_account_id,
    end_user_encrypted_account_id,
    end_user_aws_account_id,
    add_enduser.company_name end_user_company_name,
    add_enduser.email_domain end_user_email_domain,
    add_enduser.city end_user_city,
    add_enduser.state_or_region end_user_state,
    add_enduser.country_code end_user_country,
    add_enduser.postal_code end_user_postal_code,
    payer_aws_account_id,
    payer_encrypted_account_id,
    payer_address_id,
    add_payer.company_name payer_company_name,
    add_payer.email_domain payer_email_domain,
    add_payer.city payer_city,
    add_payer.state_or_region payer_state,
    add_payer.country_code payer_country,
    add_payer.postal_code payer_postal_code,
    agreement_id,
    agreement_revision,
    agreement_start_date,
    agreement_end_date,
    agreement_acceptance_date,
    agreement_updated_date,
    case when proposer_account_id = (select seller_account_id from seller_account) then null else acc_proposer.aws_account_id end as reseller_aws_account_id,
    case when proposer_account_id = (select seller_account_id from seller_account) then null else acc_proposer.mailing_company_name end as reseller_company_name,
    usage_period_start_date,
    usage_period_end_date,
    proposer_account_id,
    acc_proposer.aws_account_id as proposer_aws_account_id,
    acceptor_account_id,
    subscriber_aws_account_id,
    subscriber_encrypted_account_id,
    subscriber_address_id,
    add_subscriber.company_name subscriber_company_name,
    add_subscriber.email_domain subscriber_email_domain,
    add_subscriber.city subscriber_city,
    add_subscriber.state_or_region subscriber_state,
    add_subscriber.country_code subscriber_country,
    add_subscriber.postal_code subscriber_postal_code,
    offer_id,
    offer_target,
    offer_name,
    offer_opportunity_name,
    offer_opportunity_description,
    opportunity_id,
    payment_due_date,
    bank_trace_id,
    disbursement_date,
    billing_address_id,
    max(buyer_invoice_id)as buyer_invoice_id,
    max(seller_invoice_id)as seller_invoice_id,
    max(buyer_invoice_date)as buyer_invoice_date,
    max(seller_invoice_date)as seller_invoice_date,
    gross_revenue_this_disbursement_id_or_invoiced,
    gross_refund_this_disbursement_id_or_invoiced,
    cogs_this_disbursement_id_or_invoiced,
    cogs_refund_this_disbursement_id_or_invoiced,
    aws_rev_share_this_disbursement_id_or_invoiced,
    aws_refund_share_this_disbursement_id_or_invoiced,
    aws_tax_share_this_disbursement_id_or_invoiced,
    aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,
    seller_tax_share_this_disbursement_id_or_invoiced,
    seller_tax_share_refund_this_disbursement_id_or_invoiced,
    balance_adjustment_this_disbursement_id_or_invoiced,
    seller_rev_credit_this_disbursement_id_or_invoiced,
    aws_ref_fee_credit_this_disbursement_id_or_invoiced,
    (gross_revenue_this_disbursement_id_or_invoiced + gross_refund_this_disbursement_id_or_invoiced + aws_rev_share_this_disbursement_id_or_invoiced + aws_refund_share_this_disbursement_id_or_invoiced + seller_tax_share_this_disbursement_id_or_invoiced + seller_tax_share_refund_this_disbursement_id_or_invoiced
      + cogs_this_disbursement_id_or_invoiced + cogs_refund_this_disbursement_id_or_invoiced + aws_tax_share_listing_fee_this_disbursement_id_or_invoiced + aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced) as seller_net_revenue_this_disbursement_id_or_invoiced,
    gross_revenue_invoiced,
    gross_refund_invoiced,
    cogs_invoiced,
    cogs_refund_invoiced,
    aws_rev_share_invoiced,
    aws_refund_share_invoiced,
    aws_tax_share_invoiced,
    aws_tax_share_listing_fee_invoiced,
    aws_tax_share_refund_invoiced,
    aws_tax_share_refund_listing_fee_invoiced,
    seller_tax_share_invoiced,
    seller_tax_share_refund_invoiced,
    balance_adjustment_invoiced,
    seller_rev_credit_invoiced,
    aws_ref_fee_credit_invoiced,
    gross_revenue_disbursed,
    gross_refund_disbursed,
    cogs_disbursed,
    cogs_refund_disbursed,
    aws_rev_share_disbursed,
    aws_refund_share_disbursed,
    aws_tax_share_disbursed,
    aws_tax_share_listing_fee_disbursed,
    aws_tax_share_refund_disbursed,
    aws_tax_share_refund_listing_fee_disbursed,
    seller_tax_share_disbursed,
    seller_tax_share_refund_disbursed,
    balance_adjustment_disbursed,
    seller_rev_credit_disbursed,
    aws_ref_fee_credit_disbursed,
    (gross_revenue_invoiced + gross_revenue_disbursed) as uncollected_gross_revenue,
    -- net revenue = gross revenue - listing fee - tax - cogs
    (gross_revenue_invoiced + gross_refund_invoiced + aws_rev_share_invoiced + aws_refund_share_invoiced + seller_tax_share_invoiced + seller_tax_share_refund_invoiced + cogs_invoiced + cogs_refund_invoiced + aws_tax_share_listing_fee_invoiced + aws_tax_share_refund_listing_fee_invoiced) as seller_net_revenue,
    (gross_revenue_invoiced + gross_refund_invoiced + aws_rev_share_invoiced + aws_refund_share_invoiced + seller_tax_share_invoiced + seller_tax_share_refund_invoiced + cogs_invoiced + cogs_refund_invoiced + aws_tax_share_listing_fee_invoiced + aws_tax_share_refund_listing_fee_invoiced
      + gross_revenue_disbursed + gross_refund_disbursed + aws_rev_share_disbursed + aws_refund_share_disbursed + seller_tax_share_disbursed + seller_tax_share_refund_disbursed + cogs_disbursed + cogs_refund_disbursed + aws_tax_share_listing_fee_disbursed + aws_tax_share_refund_listing_fee_disbursed) as uncollected_seller_net_revenue,
    last_disbursement_date,
    last_disbursement_id,
    last_disburse_bank_trace_id,
    disbursement_date_list,
    disburse_bank_trace_id_list,
    product_code,
    manufacturer_aws_account_id,
    manufacturer_account_id,
    acc_manu.mailing_company_name as manufacturer_company_name,
    cast(null as varchar) as AR_Period,
    case
      when (
        (gross_revenue_invoiced <>0 and gross_revenue_invoiced = -1 * gross_revenue_disbursed)
        or (gross_refund_invoiced <> 0 and gross_refund_invoiced = -1 * gross_refund_disbursed)
        or (balance_adjustment_invoiced <> 0 and balance_adjustment_invoiced = -1 * balance_adjustment_disbursed)
        or (seller_tax_share_refund_invoiced <> 0 and seller_tax_share_refund_invoiced = -1 * seller_tax_share_refund_disbursed)
        or (gross_revenue_invoiced = 0 and gross_refund_invoiced = 0 and balance_adjustment_invoiced = 0 and seller_tax_share_refund_invoiced = 0 and last_disbursement_id is not null)) then 'Yes'
      when gross_revenue_disbursed = 0 and gross_refund_disbursed = 0 and balance_adjustment_disbursed = 0 and seller_tax_share_disbursed = 0 and seller_tax_share_refund_disbursed = 0 then 'No'
      else 'Partial'
    end as Disbursement_Flag
  from line_items_with_window_functions_enrich_offer_product_address as line
  left join accounts_with_history_with_company_name as acc_manu on
    line.manufacturer_account_id = acc_manu.account_id and line.buyer_invoice_date >= acc_manu.valid_from_adjusted and line.buyer_invoice_date <= acc_manu.valid_to
  left join accounts_with_history_with_company_name as acc_proposer on
    line.proposer_account_id = acc_proposer.account_id and line.buyer_invoice_date >= acc_proposer.valid_from and line.buyer_invoice_date<acc_proposer.valid_to
  left join address_with_latest_revision as add_payer on
    line.payer_address_id = add_payer.address_id
  left join address_with_latest_revision as add_subscriber on
    line.subscriber_address_id = add_subscriber.address_id
  left join address_with_latest_revision as add_enduser on
    line.end_user_address_id = add_enduser.address_id
  group by
    line.internal_buyer_line_item_id,
    disbursement_id,
    disbursement_id_or_invoiced,
    product_id,
    legacy_product_id,
    product_title,
    broker_id,
    currency,
    end_user_address_id,
    end_user_account_id,
    end_user_encrypted_account_id,
    end_user_aws_account_id,
    add_enduser.company_name,
    add_enduser.email_domain,
    add_enduser.city,
    add_enduser.state_or_region,
    add_enduser.country_code,
    add_enduser.postal_code,
    payer_aws_account_id,
    payer_encrypted_account_id,
    payer_address_id,
    add_payer.company_name,
    add_payer.email_domain,
    add_payer.city,
    add_payer.state_or_region,
    add_payer.country_code,
    add_payer.postal_code,
    agreement_id,
    agreement_revision,
    case when proposer_account_id = (select seller_account_id from seller_account) then null else acc_proposer.aws_account_id end,
    case when proposer_account_id = (select seller_account_id from seller_account) then null else acc_proposer.mailing_company_name end,
    agreement_start_date,
    agreement_end_date,
    agreement_acceptance_date,
    agreement_updated_date,
    usage_period_start_date,
    usage_period_end_date,
    acceptor_account_id,
    subscriber_aws_account_id,
    subscriber_encrypted_account_id,
    subscriber_address_id,
    add_subscriber.company_name,
    add_subscriber.email_domain,
    add_subscriber.city,
    add_subscriber.state_or_region,
    add_subscriber.country_code,
    add_subscriber.postal_code,
    offer_id,
    offer_target,
    offer_name,
    offer_opportunity_name,
    offer_opportunity_description,
    opportunity_id,
    payment_due_date,
    bank_trace_id,
    disbursement_date,
    billing_address_id,
    gross_revenue_this_disbursement_id_or_invoiced,
    gross_refund_this_disbursement_id_or_invoiced,
    cogs_this_disbursement_id_or_invoiced,
    cogs_refund_this_disbursement_id_or_invoiced,
    aws_rev_share_this_disbursement_id_or_invoiced,
    aws_refund_share_this_disbursement_id_or_invoiced,
    aws_tax_share_this_disbursement_id_or_invoiced,
    aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,
    seller_tax_share_this_disbursement_id_or_invoiced,
    seller_tax_share_refund_this_disbursement_id_or_invoiced,
    balance_adjustment_this_disbursement_id_or_invoiced,
    seller_rev_credit_this_disbursement_id_or_invoiced,
    aws_ref_fee_credit_this_disbursement_id_or_invoiced,
    gross_revenue_invoiced,
    gross_refund_invoiced,
    cogs_invoiced,
    cogs_refund_invoiced,
    aws_rev_share_invoiced,
    aws_refund_share_invoiced,
    aws_tax_share_invoiced,
    aws_tax_share_listing_fee_invoiced,
    aws_tax_share_refund_invoiced,
    aws_tax_share_refund_listing_fee_invoiced,
    seller_tax_share_invoiced,
    seller_tax_share_refund_invoiced,
    balance_adjustment_invoiced,
    seller_rev_credit_invoiced,
    aws_ref_fee_credit_invoiced,
    gross_revenue_disbursed,
    gross_refund_disbursed,
    cogs_disbursed,
    cogs_refund_disbursed,
    aws_rev_share_disbursed,
    aws_refund_share_disbursed,
    aws_tax_share_disbursed,
    aws_tax_share_listing_fee_disbursed,
    aws_tax_share_refund_disbursed,
    aws_tax_share_refund_listing_fee_disbursed,
    seller_tax_share_disbursed,
    seller_tax_share_refund_disbursed,
    balance_adjustment_disbursed,
    seller_rev_credit_disbursed,
    aws_ref_fee_credit_disbursed,
    last_disbursement_date,
    last_disbursement_id,
    last_disburse_bank_trace_id,
    disbursement_date_list,
    disburse_bank_trace_id_list,
    product_code,
    manufacturer_aws_account_id,
    manufacturer_account_id,
    acc_manu.mailing_company_name,
    proposer_account_id,
    acc_proposer.aws_account_id
),

billed_revenue as (
  select
    ------------------
    -- Invoice Info --
    ------------------
    buyer_invoice_date as Invoice_Date,
    Payment_Due_Date as Payment_Due_Date,
    concat(
      'Net ',
      case
        when abs(date_diff('Day', Payment_due_date, buyer_invoice_date))>180 then '180+'
        else cast(abs(date_diff('Day', Payment_due_date, buyer_invoice_date)) as varchar)
        end,
      ' days'
    ) as payment_terms,
    buyer_invoice_id as Invoice_ID,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when seller_invoice_id = '' then null else seller_invoice_id end,
      'Not applicable') as Listing_Fee_Invoice_ID,

    ---------------------------
    --End user Information --
    ---------------------------
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when End_User_Company_Name = '' then null else End_User_Company_Name end,
      'Not available') as End_User_Company_Name,
    End_User_AWS_Account_ID,
    End_User_Encrypted_Account_ID,
    End_User_Email_Domain,
    End_User_City,
    End_User_State as End_User_State_or_Region,
    End_User_Country,
    End_User_Postal_Code,
    End_User_Address_ID,

    ---------------------------
    --Subscriber Information --
    ---------------------------
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Company_Name is null or Subscriber_Company_Name = '' then 'Not provided'
      else Subscriber_Company_Name
      end as Subscriber_Company_Name,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      else Subscriber_AWS_Account_ID
      end as Subscriber_AWS_Account_ID,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      else Subscriber_Encrypted_Account_ID
      end as Subscriber_Encrypted_Account_ID,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Email_Domain is null or Subscriber_Email_Domain = '' then 'Not provided'
      else Subscriber_Email_Domain
      end as Subscriber_Email_Domain,
    case
      when Agreement_id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_City is null or Subscriber_City = '' then 'Not provided'
      else Subscriber_City
      end as Subscriber_City,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_State is null or Subscriber_State = '' then 'Not provided'
      else Subscriber_State
      end as Subscriber_State_or_Region,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Country is null or Subscriber_Country = '' then 'Not provided'
      else Subscriber_Country
      end as Subscriber_Country,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Postal_Code is null or Subscriber_Postal_Code = '' then 'Not provided'
      else Subscriber_Postal_Code
      end as Subscriber_Postal_Code,
    case
      when Agreement_ID is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Address_ID is null or Subscriber_Address_ID = '' then 'Not provided'
      else Subscriber_Address_ID
      end as Subscriber_Address_ID,

    ----------------------
    -- Procurement Info --
    ----------------------
     -- product title at time of invoice. It is possible that the title changes over time and therefore there may be multiple product titles mapped to a single product id.
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Product_Title = '' then null else Product_Title end,
      'Not provided') as Product_Title,
    -- offer name at time of invoice. It is possible that the name changes over time therefore there may be multiple offer names mapped to a single offer id.
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when (Offer_Name is null or Offer_Name = '') and Offer_Target = 'Public' then 'Not applicable'
      else Offer_Name
      end as Offer_Name,
    case
      when Agreement_Id is null or Agreement_ID = ''
      then 'Not available'
      else Offer_ID
      end as Offer_ID,
    -- offer target at time of invoice.,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      else Offer_Target
      end as Offer_Visibility,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Agreement_ID = '' then null else Agreement_ID end,
      'Not available') as Agreement_ID,
    Agreement_Start_Date,
    Agreement_Acceptance_Date,
    Agreement_End_Date,
    Usage_Period_Start_Date,
    Usage_Period_End_Date,

    -----------------------
    -- Disbursement Info --
    -----------------------
    case
      when Disbursement_Flag = 'Yes' then 'Disbursed'
      when Disbursement_Flag = 'No' then 'Not Disbursed'
      else 'Other'
      end as Disbursement_Status,
    last_disbursement_date as disbursement_date,
    case
      when Disbursement_Flag = 'No' then 'Not applicable'
      when disburse_bank_trace_id_list is null or disburse_bank_trace_id_list = '' then 'Not available'
      else disburse_bank_trace_id_list
    end as disburse_bank_trace_id,

    --------------
    -- Revenues --
    --------------
    -- We are rounding the sums using 2 decimal precision
    -- Note that the rounding method might differ between SQL implementations.
    -- The monthly revenue report is using RoundingMode.HALF_UP. This might create tiny discrepancies between this SQL output
    -- and the legacy report
    round(gross_revenue_invoiced,2) as Gross_Revenue,
    round(gross_refund_invoiced,2) as Gross_Refund,
    round(aws_rev_share_invoiced,2) as Listing_Fee,
    round(aws_refund_share_invoiced,2) as Listing_Fee_Refund,
    truncate(
      case
        when gross_revenue_invoiced != 0 then abs(aws_rev_share_invoiced/gross_revenue_invoiced)
        when gross_refund_invoiced != 0 then abs(aws_refund_share_invoiced/gross_refund_invoiced)
        else 0
      end
      ,4) as Listing_Fee_Percentage,
    round(seller_tax_share_invoiced,2) as Seller_Tax_Share,
    round(seller_tax_share_refund_invoiced,2) as Seller_Tax_Share_Refund,
    round(aws_tax_share_invoiced,2) as AWS_Tax_Share,
    round(aws_tax_share_refund_invoiced,2) as AWS_Tax_Share_Refund,
    round(aws_tax_share_listing_fee_invoiced,2) as AWS_Tax_Share_Listing_Fee,
    round(aws_tax_share_refund_listing_fee_invoiced,2) as AWS_Tax_Share_Refund_Listing_Fee,
    round(cogs_invoiced,2) as Wholesale_cost,
    round(cogs_refund_invoiced,2) as Wholesale_cost_Refund,
    round(seller_net_revenue,2) as Seller_Net_Revenue,
    currency as Currency,

    substring(internal_buyer_line_item_id,1,strpos(internal_buyer_line_item_id,'-')-1) as Transaction_Reference_ID,
    broker_id as AWS_seller_of_record,

    -----------------
    -- Resale info --
    -----------------
    case
      when Opportunity_Id is null or Opportunity_Id = '' then
        case
          when Offer_Target = 'Public' then 'Not applicable'
          when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable'
          else null
        end
      else Opportunity_Id
    end as Resale_authorization_ID,
    case
      when Offer_Opportunity_Name is null or Offer_Opportunity_Name = '' then
        case
          when Offer_Target = 'Public' then 'Not applicable'
          when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable'
          else null
        end
      else Offer_Opportunity_Name
    end as Resale_authorization_name,
    case
      when Offer_Opportunity_Description is null or Offer_Opportunity_Description = '' then
        case
         when Offer_Target = 'Public' then 'Not applicable'
         when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable'
         else null
        end
      else Offer_Opportunity_Name
    end as Resale_authorization_description,
    case
      when (Reseller_AWS_Account_ID is not null and Reseller_AWS_Account_ID != '')
        and (Reseller_Company_Name is null or Reseller_Company_Name = '') then 'Not available'
      when (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '')
        and (opportunity_id is null or opportunity_id = '') then 'Not applicable'
      when (select seller_account_id from seller_account) <> manufacturer_aws_account_id
        and (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') then 'Not applicable'
      else Reseller_Company_Name
    end as Reseller_Company_Name,
    case
      when (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '')
        and (Opportunity_Id is null or Opportunity_Id = '') then 'Not applicable'
      when (select seller_account_id from seller_account) <> manufacturer_aws_account_id
        and (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') then 'Not applicable'
      else Reseller_AWS_Account_ID
    end as Reseller_AWS_Account_ID,

    -----------------------
    -- Payer Information --
    -----------------------
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Payer_Company_Name = '' then null else Payer_Company_Name end,
      'Not available') as Payer_Company_Name,
    Payer_AWS_Account_ID,
    Payer_Encrypted_Account_ID,
    Payer_Email_Domain,
    Payer_City,
    Payer_State as Payer_State_or_Region,
    Payer_Country,
    Payer_Postal_Code,
    Payer_Address_ID,

    ---------------------
    -- ISV Information --
    ---------------------
    manufacturer_aws_account_id as ISV_Account_ID,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Manufacturer_Company_Name = '' then null else Manufacturer_Company_Name end,
      'Not available') as ISV_Company_Name,

    ---------------------
    -- Products info --
    ---------------------
    Legacy_Product_ID,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Product_ID = '' then null else Product_ID end,
      'Not provided') as Product_ID,
    Product_Code
  from
    line_items_with_window_functions_enrich_offer_product_address_name as line
  where disbursement_id_or_invoiced = '<invoiced>'

)

select *
from billed_revenue
where invoice_date >= date_add('DAY', -90, current_date)
--where invoice_date between cast('2023-01-01' as timestamp) and cast('2024-03-01' as timestamp)
```

## Factures non encaissées ou déboursées
<a name="data-feed-example-collections"></a>

Pour trouver vos factures non encaissées ou déboursées, vous pouvez exécuter un ensemble de requêtes comme dans l'exemple suivant. Les requêtes s'appuient les unes sur les autres pour créer le rapport sur les **recouvrements et les décaissements**. Vous pouvez utiliser l'exemple tel qu'illustré ou le personnaliser en fonction de vos données et de vos cas d'utilisation.

Les commentaires contenus dans les requêtes expliquent à quoi servent les requêtes et comment les modifier.

```
-- Collections and disbursements report

-- General note: When running this query, we assume that the data ingested in the database uses
-- two time axes (the valid_from column and the update_date column).
-- See documentation for more details: https://docs.aws.amazon.com/marketplace/latest/userguide/data-feed.html#data-feed-details

-- An account_id has several valid_from dates (each representing a separate revision of the data)
-- but because of bi-temporality, an account_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
with accounts_with_uni_temporal_data as (
  select
    account_id,
    aws_account_id,
    encrypted_account_id,
    mailing_address_id,
    tax_address_id,
    tax_legal_name,
    from_iso8601_timestamp(valid_from) as valid_from,
    tax_registration_number
  from
    (
      select
        account_id,
        aws_account_id,
        encrypted_account_id,
        mailing_address_id,
        tax_address_id,
        tax_legal_name,
        valid_from,
        delete_date,
        tax_registration_number,
        row_number() over (partition by account_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
      from
        accountfeed_v1
    )
    where
      -- keep latest ...
      row_num = 1
      -- ... and remove the soft-deleted one.
      and (delete_date is null or delete_date = '')
  ),

accounts_with_history as (
  with accounts_with_history_with_extended_valid_from as (
    select
      account_id,
      -- sometimes, this columns gets imported as a "bigint" and loses heading 0s -> casting to a char and re-adding heading 0s (if need be)
      substring('000000000000'||cast(aws_account_id as varchar),-12) as aws_account_id,
      encrypted_account_id,
      mailing_address_id,
      tax_address_id,
      tax_legal_name,
      -- The start time of account valid_from is extended to '1970-01-01 00:00:00', because:
      -- ... in tax report transformations, some tax line items with invoice_date cannot
      -- ... fall into the default valid time range of the associated account
      case
        when lag(valid_from) over (partition by account_id order by valid_from asc) is null
          then cast('1970-01-01 00:00:00' as timestamp)
        else valid_from
      end as valid_from
    from accounts_with_uni_temporal_data
    )
  select
    account_id,
    aws_account_id,
    encrypted_account_id,
    mailing_address_id,
    tax_address_id,
    tax_legal_name,
    valid_from,
    coalesce(
      lead(valid_from) over (partition by account_id order by valid_from asc),
      cast('2999-01-01 00:00:00' as timestamp)
    ) as valid_to
  from
    accounts_with_history_with_extended_valid_from
),

-- An address_id has several valid_from dates (each representing a separate revision of the data)
-- but because of bi-temporality, an account_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
address_with_uni_temporal_data as (
  select
    from_iso8601_timestamp(valid_from) as valid_from,
    address_id,
    company_name,
    email_domain,
    country_code,
    state_or_region,
    city,
    postal_code,
    row_num
  from
  (
    select
      valid_from,
      update_date,
      delete_date,
      address_id,
      company_name,
      email_domain,
      country_code,
      state_or_region,
      city,
      postal_code,
      row_number() over (partition by address_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
    from
      addressfeed_v1
  )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

-- We are only interested in the most recent tuple (BTW: a given address is not supposed to change over time but when bugs ;-) so this query mainly does nothing)
address_with_latest_revision as (
  select
    valid_from,
    address_id,
    company_name,
    email_domain,
    country_code,
    state_or_region,
    city,
    postal_code,
    row_num_latest_revision
  from
  (
    select
      valid_from,
      address_id,
      company_name,
      email_domain,
      country_code,
      state_or_region,
      city,
      postal_code,
      row_number() over (partition by address_id order by valid_from desc) as row_num_latest_revision
    from
      address_with_uni_temporal_data
  )
  where
    row_num_latest_revision = 1
),

accounts_with_history_with_company_name as (
  select
    awh.account_id,
    awh.aws_account_id,
    awh.encrypted_account_id,
    awh.mailing_address_id,
    awh.tax_address_id,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when address.company_name = '' then null else address.company_name end,
      awh.tax_legal_name) as mailing_company_name,
    address.email_domain,
    awh.valid_from,
    -- For BYOL, the agreement might be accepted (using some external non-AWS system or manual process) days before
    -- that BYOL agreement is entered into AWS Marketplace by the buyer. Therefore, the buyer is permitted to manually
    -- enter a backdated acceptance date, which might predate the point in time when the account was created.
    -- To work around this, we need to adjust the valid_from of the account to be
    -- earlier than the earliest possible backdated BYOL agreement acceptance date.
    case
      when lag(awh.valid_from) over (partition by aws_account_id order by awh.valid_from asc) is null
      then date_add('Day', -212, awh.valid_from)
      -- 212 is the longest delay between acceptance_date of the agreement and the account start_Date
      else awh.valid_from
    end as valid_from_adjusted,
    awh.valid_to
  from accounts_with_history as awh
  left join address_with_latest_revision as address on
    awh.mailing_address_id = address.address_id and awh.mailing_address_id is not null
),

-- An agreement_id has several valid_from dates (each representing an agreement revision)
-- but because of bi-temporality, an agreement_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
agreements_with_uni_temporal_data as (
  select
    agreement_id,
    origin_offer_id,
    proposer_account_id,
    acceptor_account_id,
    agreement_revision,
    from_iso8601_timestamp(valid_from) as valid_from,
    from_iso8601_timestamp(start_date) as start_date,
    from_iso8601_timestamp(end_date) as end_date,
    from_iso8601_timestamp(acceptance_date) as acceptance_date,
    agreement_type,
    previous_agreement_id,
    agreement_intent
  from
  (
    select
      --empty value in Athena shows as '', change all '' value to null
      case when agreement_id = '' then null else agreement_id end as agreement_id,
      origin_offer_id,
      proposer_account_id,
      acceptor_account_id,
      agreement_revision,
      valid_from,
      delete_date,
      start_date,
      end_date,
      acceptance_date,
      agreement_type,
      previous_agreement_id,
      agreement_intent,
      row_number() over (partition by agreement_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
    from
      -- TODO change to agreementfeed_v1 when Agreement Feed is GA'ed
      agreementfeed
  )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

agreements_with_history as (
  with agreements_with_window_functions as (
    select
      agreement_id,
      origin_offer_id as offer_id,
      proposer_account_id,
      acceptor_account_id,
      agreement_revision,
      start_date,
      end_date,
      acceptance_date,
      -- The start time of agreement valid_from is extended to '1970-01-01 00:00:00', because:
      -- ... in usage report transformations, some usage line items with usage_date cannot
      -- ... fall into the default valid time range of the associated agreement
      case
          when lag(valid_from) over (PARTITION BY agreement_id order by valid_from asc) is null
          then timestamp '1970-01-01 00:00:00'
          else valid_from
      end as valid_from,
      coalesce(
          lead(valid_from) over (partition by agreement_id order by valid_from asc),
          timestamp '2999-01-01 00:00:00'
      ) as valid_to,
      rank() over (partition by agreement_id order by valid_from asc) version,
      agreement_type,
      previous_agreement_id,
      agreement_intent
    from
      agreements_with_uni_temporal_data
  )
  select
    agreement_id,
    offer_id,
    proposer_account_id,
    acceptor_account_id,
    agreement_revision,
    start_date,
    end_date,
    acceptance_date,
    valid_from,
    case
        when version=1 and valid_from < timestamp '2023-03-03 06:16:08.743' then timestamp '1970-01-01'
        -- The following 60 minute adjustment is to handle special case where When Renewal happens for a contract
        when version=1 then date_add('minute',-60,valid_from)
        else valid_from
    end as valid_from_adjusted,
    valid_to,
    agreement_type,
    previous_agreement_id,
    agreement_intent
  from
    agreements_with_window_functions
),

-- An offer_id has several valid_from dates (each representing an offer revision)
-- but because of bi-temporality, an offer_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
offers_with_uni_temporal_data as (
  select
    from_iso8601_timestamp(valid_from) as valid_from,
    from_iso8601_timestamp(update_date) as update_date,
    from_iso8601_timestamp(delete_date) as delete_date,
    offer_id,
    offer_revision,
    name,
    expiration_date,
    opportunity_id,
    opportunity_name,
    opportunity_description,
    seller_account_id
  from
  (
    select
      valid_from,
      update_date,
      delete_date,
      offer_id,
      offer_revision,
      name,
      expiration_date,
      opportunity_id,
      opportunity_name,
      opportunity_description,
      seller_account_id,
      row_number() over (partition by offer_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
    from
      offerfeed_v1
  )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

-- Here, we build the validity time range (adding valid_to on top of valid_from) of each offer revision.
-- We will use it to get Offer name at invoice time.
-- NB: If you'd rather get "current" offer name, un-comment "offers_with_latest_revision"
offers_with_history as (
  select
    offer_id,
    offer_revision,
    name,
    opportunity_id,
    opportunity_name,
    opportunity_description,
    valid_from,
    -- When we try to look up an offer revision as at the acceptance date of a BYOL agreement, we run into a problem.
    -- For BYOL, the agreement might be accepted (using some external non-AWS system or manual process) days before
    -- that BYOL agreement is entered into AWS Marketplace by the buyer. Therefore, the buyer is permitted to manually
    -- enter a backdated acceptance date, which might predate the point in time when the first revision of the offer
    -- was created. To work around this, we need to adjust the valid_from on the first revision of the offer to be
    -- earlier than the earliest possible backdated BYOL agreement acceptance date.
    case
      when lag(valid_from) over (partition by offer_id order by valid_from asc) is null and valid_from < cast('2021-04-01' as timestamp)
      then date_add('Day', -3857, valid_from)
      -- 3857 is the longest delay between acceptance_date of an agreement and the first revision of the offer
      when lag(valid_from) over (partition by offer_id order by valid_from asc) is null and valid_from >= cast('2021-04-01' as timestamp)
      then date_add('Day', -1460, valid_from)
      --after 2021 for the two offers we need to adjust for 2 more years
      else valid_from
    end as valid_from_adjusted,
    coalesce(
      lead(valid_from) over (partition by offer_id order by valid_from asc),
      cast('2999-01-01 00:00:00' as timestamp))
    as valid_to
  from offers_with_uni_temporal_data
),
-- provided for reference only if you are interested into get "current" offer name
-- (ie. not used afterwards)
offers_with_latest_revision as (
  select
    offer_id,
    offer_revision,
    name,
    opportunity_name,
    opportunity_description,
    valid_from,
    null valid_to
  from
  (
    select
      offer_id,
      offer_revision,
      name,
      opportunity_name,
      opportunity_description,
      valid_from,
      null valid_to,
      row_number() over (partition by offer_id order by valid_from desc) as row_num_latest_revision
    from
      offers_with_uni_temporal_data
  )
  where
    row_num_latest_revision = 1
),

-- An offer_target_id has several valid_from dates (each representing an offer revision)
-- but because of bi-temporality, an offer_target_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
offer_targets_with_uni_temporal_data as (
  select
    from_iso8601_timestamp(valid_from) as valid_from,
    from_iso8601_timestamp(update_date) as update_date,
    from_iso8601_timestamp(delete_date) as delete_date,
    offer_target_id,
    offer_id,
    offer_revision,
    target_type,
    polarity,
    value
  from
  (
    select
      valid_from,
      update_date,
      delete_date,
      offer_target_id,
      offer_id,
      offer_revision,
      target_type,
      polarity,
      value,
      row_number() over (partition by offer_target_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
    from
      offertargetfeed_v1
  )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

offer_target_type as (
  select
    offer_id,
    offer_revision,
    substring(
      -- The first character indicates the priority (lower value means higher precedence):
      min(
        case
          when offer_target.target_type='BuyerAccounts' then '1Private'
          when offer_target.target_type='ParticipatingPrograms' then '2Program:'||cast(offer_target.value as varchar)
          when offer_target.target_type='CountryCodes' then '3GeoTargeted'
          -- well, there is no other case today, but rather be safe...
          else '4Other Targeting'
        end
      ),
      -- Remove the first character that was only used for th priority in the "min" aggregate function:
      2
    ) as offer_target
  from
    offer_targets_with_uni_temporal_data as offer_target
  group by
    offer_id,
    offer_revision
),

offers_with_history_with_target_type as (
  select
    offer.offer_id,
    offer.offer_revision,
    -- even though today it is not possible to combine several types of targeting in a single offer, let's ensure the query is still predictable if this gets possible in the future
    max(
      case
        when off_tgt.offer_target is null then 'Public'
        else off_tgt.offer_target
      end
    ) as offer_target,
    min(offer.name) as name,
    min(offer.opportunity_name) as opportunity_name,
    min(offer.opportunity_description) as opportunity_description,
    offer.valid_from,
    offer.valid_from_adjusted,
    offer.valid_to,
    offer.opportunity_id
  from
    offers_with_history as offer
  left join offer_target_type as off_tgt on
    offer.offer_id = off_tgt.offer_id
    and offer.offer_revision = off_tgt.offer_revision
  group by
    offer.offer_id,
    offer.offer_revision,
    offer.valid_from,
    offer.valid_from_adjusted,
    offer.valid_to,
    offer.opportunity_id
),

-- provided for reference only if you are interested into get "current" offer targets
-- (ie. not used afterwards)
offers_with_latest_revision_with_target_type as (
  select
    offer.offer_id,
    offer.offer_revision,
    -- even though today it is not possible to combine several types of targeting in a single offer, let's ensure the query is still predictable if this gets possible in the future
    max(
      distinct
      case
        when off_tgt.target_type is null then 'Public'
        when off_tgt.target_type='BuyerAccounts' then 'Private'
        when off_tgt.target_type='ParticipatingPrograms' then 'Program:'||cast(off_tgt.value as varchar)
        when off_tgt.target_type='CountryCodes' then 'GeoTargeted'
        -- well, there is no other case today, but rather be safe...
        else 'Other Targeting'
      end
    ) as offer_target,
    min(offer.name) as name,
    min(offer.opportunity_name) as opportunity_name,
    min(offer.opportunity_description) as opportunity_description,
    offer.valid_from,
    offer.valid_to
  from
    offers_with_latest_revision offer
    -- left joining because public offers don't have targets
    left join offer_targets_with_uni_temporal_data off_tgt on
      offer.offer_id=off_tgt.offer_id and offer.offer_revision=off_tgt.offer_revision
  group by
    offer.offer_id,
    offer.offer_revision,
    -- redundant with offer_revision, as each revision has a dedicated valid_from (but cleaner in the group by)
    offer.valid_from,
    offer.valid_to
),

-- A product_id has several valid_from dates (each representing a product revision),
-- but because of bi-temporality, each product_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
products_with_uni_temporal_data as (
  select
    from_iso8601_timestamp(valid_from) as valid_from,
    from_iso8601_timestamp(update_date) as update_date,
    from_iso8601_timestamp(delete_date) as delete_date,
    product_id,
    manufacturer_account_id,
    product_code,
    title
  from
  (
    select
      valid_from,
      update_date,
      delete_date,
      product_id,
      manufacturer_account_id,
      product_code,
      title,
      row_number() over (partition by product_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
    from
      productfeed_v1
  )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

products_with_history as (
  select
    product_id,
    title,
    valid_from,
    -- Offerv2 can have upto 50 years and Offerv3 is upto 5 years of past date
    case
      when lag(valid_from) over (partition by product_id order by valid_from asc) is null and valid_from < cast('2021-04-01' as timestamp)
        then date_add('Day', -3857, valid_from)
      -- 3827 is the longest delay between acceptance_date of an agreement and the product
      -- we are keeping 3857 as a consistency between the offers and products
      when lag(valid_from) over (partition by product_id order by valid_from asc) is null and valid_from >= cast('2021-04-01' as timestamp)
        then date_add('Day', -2190, valid_from)
      --after 2021 for the two offers we need to adjust for 2 more years
      else valid_from 
    end as valid_from_adjusted,
    coalesce(
      lead(valid_from) over (partition by product_id order by valid_from asc),
      cast('2999-01-01 00:00:00' as timestamp)
    ) as valid_to,
    product_code,
    manufacturer_account_id
  from
    products_with_uni_temporal_data
),

legacy_products as (
  select
    legacy_id,
    new_id
  from
    legacyidmappingfeed_v1
  where
    mapping_type='PRODUCT'
  group by
    legacy_id,
    new_id
),

-- A given billing_event_id represents an accounting event and thus has only one valid_from date,
-- but because of bi-temporality, a billing_event_id (+ its valid_from) can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
billing_events_with_uni_temporal_data as (
  select
    billing_event_id,
    valid_from,
    update_date,
    delete_date,
    invoice_date,
    transaction_type,
    transaction_reference_id,
    parent_billing_event_id,
    bank_trace_id,
    broker_id,
    product_id,
    disbursement_billing_event_id,
    action,
    from_account_id,
    to_account_id,
    end_user_account_id,
    billing_address_id,
    amount,
    currency,
    balance_impacting,
    --empty value in Athena shows as '', change all '' value to null
    case when agreement_id = '' then null else agreement_id end as agreement_id,
    invoice_id,
    payment_due_date,
    usage_period_start_date,
    usage_period_end_date,
    buyer_transaction_reference_id,
    row_num
  from
  (
    select
      billing_event_id,
      from_iso8601_timestamp(valid_from) as valid_from,
      from_iso8601_timestamp(update_date) as update_date,
      delete_date,
      from_iso8601_timestamp(invoice_date) as invoice_date,
      transaction_type,
      transaction_reference_id,
      parent_billing_event_id,
      -- casting in case data was imported as number
      cast(bank_trace_id as varchar) as bank_trace_id,
      broker_id,
      product_id,
      disbursement_billing_event_id,
      action,
      from_account_id,
      to_account_id,
      end_user_account_id,
      billing_address_id,
      -- casting in case data was imported as varchar
      cast(amount as decimal(38,6)) as amount,
      currency,
      balance_impacting,
      agreement_id,
      invoice_id,
      case when payment_due_date is null or payment_due_date = '' then null else from_iso8601_timestamp(payment_due_date) end as payment_due_date,
      from_iso8601_timestamp(usage_period_start_date) as usage_period_start_date,
      from_iso8601_timestamp(usage_period_end_date) as usage_period_end_date,
      buyer_transaction_reference_id,
      row_number() over (partition by billing_event_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
    from
      billingeventfeed_v1
  )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

-- Here we select the account_id of the current seller (We identify this by looking for the to_account_id related to revenue transactions).
-- We will use it later to distinguish own agreements from agreements generated by channel partners.
seller_account as (
  select
    from_account_id as seller_account_id
  from
    billing_events_with_uni_temporal_data bill
  where
    -- Assumption here is only seller will pay listing fee. As of 12/21/2021, there are cases that Channel partner have 0 listing fee for CPPO, so the amount could be 0.
    bill.transaction_type like 'AWS_REV_SHARE' and amount <= 0 and action = 'INVOICED'
  group by
    -- from_account_id is always the same for all those "listing fee" transactions == the seller of record himself.
    -- If this view returns more than 1 record, the overall query will fail (on purpose). Please contact AWS Marketplace if this happens.
    from_account_id
),

billing_event_with_business_flags as (
  select
    bl.billing_event_id,
    bl.end_user_account_id,
    bl.agreement_id,
    aggrement.proposer_account_id,
    aggrement.offer_id,
    aggrement.acceptor_account_id,
    case
      -- For AWS and BALANCE_ADJUSTMENT, the billing event feed will show the "AWS Marketplace" account as the
      -- receiver of the funds and the seller as the payer. We are not interested in this information here.
      -- Null values will be ignored by the `max` aggregation function.
      when bl.transaction_type like 'AWS%' then null
      -- For BALANCE_ADJUSTMENT, payer is seller themselves
      when bl.invoice_id is null then bl.to_account_id
      -- We get the payer of the invoice from *any* transaction type that is not AWS and not BALANCE_ADJUSTMENT (because they are the same for a given end user + agreement + product).
      else bl.from_account_id
    end as payer_account_id,
    bl.product_id,
    bl.action,
    bl.transaction_type,
    bl.parent_billing_event_id,
    bl.disbursement_billing_event_id,
    bl.amount,
    bl.currency,
    bl.balance_impacting,
    bl.invoice_date,
    bl.payment_due_date,
    bl.usage_period_start_date,
    bl.usage_period_end_date,
    bl.invoice_id,
    bl.billing_address_id,
    bl.transaction_reference_id,
    bl.buyer_transaction_reference_id,
    case when disbursement.bank_trace_id = 'EMEA_MP_TEST_TRACE_ID' then null else disbursement.bank_trace_id end as bank_trace_id,
    case when disbursement.bank_trace_id = 'EMEA_MP_TEST_TRACE_ID' then null else disbursement.invoice_date end as disbursement_date,
    disbursement.billing_event_id as disbursement_id,
    -- We will use disbursement_id_or_invoiced as part of the PK, so it cannot be null:
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when disbursement.billing_event_id = '' then null else disbursement.billing_event_id end,
      '<invoiced>') as disbursement_id_or_invoiced,
    bl.broker_id,
    case
      when bl.invoice_id is null /* transaction_type = 'BALANCE_ADJUSTMENT' */
        then (select seller_account_id from seller_account) ||':'|| cast(bl.invoice_date as varchar)
      else bl.buyer_transaction_reference_id
        ||'-'|| case when bl.agreement_id is null or bl.agreement_id = '' then ' ' else bl.agreement_id end
        ||'-'|| case when bl.end_user_account_id is null or bl.end_user_account_id = '' then ' ' else bl.end_user_account_id end
        ||'-'|| coalesce(cast(bl.usage_period_start_date as varchar),' ')
        ||'-'|| coalesce(cast(bl.usage_period_end_date as varchar),' ')
    end as internal_buyer_line_item_id,
    bl.buyer_transaction_reference_id <> bl.transaction_reference_id as is_seller_invoice,
    case when bl.transaction_type = 'SELLER_REV_SHARE' and (select seller_account_id from seller_account) <> bl.to_account_id then true else false end as is_cog,
    case when bl.transaction_type in('SELLER_REV_SHARE_CREDIT', 'SELLER_REV_SHARE_REFUND') and (select seller_account_id from seller_account) <> bl.to_account_id then true else false end as is_cog_refund,
    --TODO: replace below logic once we can create a logic the identify reseller/manufacturer without depending on agreement feed
    case when aggrement.proposer_account_id <> (select seller_account_id from seller_account) then true else false end as is_manufacturer_view_of_reseller
  from
    billing_events_with_uni_temporal_data as bl
    left join billing_events_with_uni_temporal_data as disbursement on
      disbursement.transaction_type like 'DISBURSEMENT%'
        and disbursement.action = 'DISBURSED'
        and disbursement.transaction_type IN ('DISBURSEMENT', 'DISBURSEMENT_FAILURE')
        and bl.disbursement_billing_event_id = disbursement.billing_event_id
    left join agreements_with_history as aggrement on
      bl.agreement_id = aggrement.agreement_id
        and bl.invoice_date >= aggrement.valid_from_adjusted
        and bl.invoice_date < aggrement.valid_to
    left join accounts_with_history awh on
      bl.to_account_id = awh.account_id
        and bl.invoice_date >= awh.valid_from
        and bl.invoice_date < awh.valid_to
  where
    bl.transaction_type not like 'DISBURSEMENT%' and
      (bl.agreement_id is null or bl.agreement_id = ''
      or aggrement.agreement_id is not null)
),

-- listagg function in athena does not support partitioning, grouping here and then joining to the main query
seller_invoice_list as (
  select
    internal_buyer_line_item_id,
    listagg(case when not is_seller_invoice then null else invoice_id end,',') within group (order by case when not is_seller_invoice then null else cast(invoice_date as varchar) end) as seller_invoice_id_or_null,
    listagg(case when not is_seller_invoice then null else cast(invoice_date as varchar) end,',') within group (order by case when not is_seller_invoice then null else cast(invoice_date as varchar) end) as seller_invoice_date_or_null
  from
    (
    -- listagg function in athena does not support ordering by another field when distinct is used,
    -- here we first select distinct invoices and then do the listagg order by invoice_date
    select distinct internal_buyer_line_item_id, is_seller_invoice, invoice_id, invoice_date
    from billing_event_with_business_flags) distinct_invoices
  group by internal_buyer_line_item_id
  order by internal_buyer_line_item_id
),

billing_event_with_categorized_transaction as (
-- Use the flags that were created in the previous transformation in more calculated columns:
-- NOTE: This transformation has no joins and no window functions
  select
    billing_event_id,
    end_user_account_id,
    agreement_id,
    proposer_account_id,
    offer_id,
    acceptor_account_id,
    case when is_cog or is_cog_refund then null else payer_account_id end as payer_account_id,
    product_id,
    action,
    transaction_type,
    parent_billing_event_id,
    disbursement_billing_event_id,
    amount,
    currency,
    balance_impacting,
    invoice_date,
    payment_due_date,
    usage_period_start_date,
    usage_period_end_date,
    invoice_id,
    billing_address_id,
    transaction_reference_id,
    buyer_transaction_reference_id,
    bank_trace_id,
    disbursement_date,
    disbursement_id,
    disbursement_id_or_invoiced,
    broker_id,
    bl.internal_buyer_line_item_id,
    is_seller_invoice,
    is_cog,
    is_cog_refund,
    is_manufacturer_view_of_reseller,

    -- Buyer/seller columns:
    case when is_seller_invoice then null else invoice_id end as buyer_invoice_id_or_null,
    seller_invoices.seller_invoice_id_or_null,
    case when is_seller_invoice then null else invoice_date end as buyer_invoice_date_or_null,
    seller_invoices.seller_invoice_date_or_null,

    -- Categorized amounts by transaction type:
    case when transaction_type =   'SELLER_REV_SHARE' and not is_cog then amount else 0 end as gross_revenue,
    case when transaction_type in ('SELLER_REV_SHARE_REFUND','SELLER_REV_SHARE_CREDIT') and not is_cog_refund then amount else 0 end as gross_refund,
    case when transaction_type =   'SELLER_REV_SHARE' and     is_cog then amount else 0 end as cogs,
    case when transaction_type in ('SELLER_REV_SHARE_REFUND','SELLER_REV_SHARE_CREDIT') and     is_cog_refund then amount else 0 end as cogs_refund,
    case when transaction_type =   'AWS_REV_SHARE' then amount else 0 end as aws_rev_share,
    case when transaction_type in ('AWS_REV_SHARE_REFUND','AWS_REV_SHARE_CREDIT') then amount else 0 end as aws_refund_share,
    case when transaction_type =   'AWS_TAX_SHARE' and not is_seller_invoice then amount else 0 end as aws_tax_share,             -- AWS tax share from _buyer_  invoice
    case when transaction_type =   'AWS_TAX_SHARE' and     is_seller_invoice then amount else 0 end as aws_tax_share_listing_fee, -- AWS tax share from _seller_ invoice
    case when transaction_type =   'AWS_TAX_SHARE_REFUND' and not is_seller_invoice then amount else 0 end as aws_tax_share_refund,
    case when transaction_type =   'AWS_TAX_SHARE_REFUND' and     is_seller_invoice then amount else 0 end as aws_tax_share_refund_listing_fee,
    case when transaction_type =   'SELLER_TAX_SHARE' then amount else 0 end as seller_tax_share,
    case when transaction_type =   'SELLER_TAX_SHARE_REFUND' then amount else 0 end as seller_tax_share_refund,
    case when transaction_type =   'BALANCE_ADJUSTMENT' then amount else 0 end as balance_adjustment,
    case when transaction_type =   'SELLER_REV_SHARE_CREDIT' then amount else 0 end as seller_rev_credit,
    case when transaction_type =   'AWS_REV_SHARE_CREDIT' then amount else 0 end as aws_ref_fee_credit
  from
    billing_event_with_business_flags as bl
    left join seller_invoice_list as seller_invoices
      on bl.internal_buyer_line_item_id = seller_invoices.internal_buyer_line_item_id
    ),

line_items_aggregated as (
-- This transformation has the only "group by" in all of these transformations.
-- NOTE: This transformation has no joins and no window functions
  select
    internal_buyer_line_item_id,
    disbursement_id,
    disbursement_id_or_invoiced,
    product_id,
    broker_id,
    currency,
    agreement_id,
    proposer_account_id,
    acceptor_account_id,
    max(payer_account_id) as payer_account_id,
    offer_id,
    end_user_account_id,
    usage_period_start_date,
    usage_period_end_date,
    max(payment_due_date) payment_due_date,
    buyer_transaction_reference_id,
    bank_trace_id,
    disbursement_date,
    max(billing_address_id) as billing_address_id,

    -- Buyer/seller columns:
    max(buyer_invoice_id_or_null) as buyer_invoice_id,
    max(seller_invoice_id_or_null) as seller_invoice_id,
    max(buyer_invoice_date_or_null) as buyer_invoice_date,
    max(seller_invoice_date_or_null) as seller_invoice_date,
  
    -- Categorized amounts by transaction type:
    -- When disbursement_id_or_invoiced = '<invoiced>',    these are invoiced amounts
    -- When disbursement_id_or_invoiced <> ''<invoiced>' these are disbursed amounts for _this_ specific disbursement_id
    sum(gross_revenue) as gross_revenue_this_disbursement_id_or_invoiced,
    sum(gross_refund) as gross_refund_this_disbursement_id_or_invoiced,
    sum(cogs) as cogs_this_disbursement_id_or_invoiced,
    sum(cogs_refund) as cogs_refund_this_disbursement_id_or_invoiced,
    sum(aws_rev_share) as aws_rev_share_this_disbursement_id_or_invoiced,
    sum(aws_refund_share) as aws_refund_share_this_disbursement_id_or_invoiced,
    sum(aws_tax_share) as aws_tax_share_this_disbursement_id_or_invoiced,
    sum(aws_tax_share_listing_fee) as aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,
    sum(aws_tax_share_refund) as aws_tax_share_refund_this_disbursement_id_or_invoiced,
    sum(aws_tax_share_refund_listing_fee) as aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,
    sum(seller_tax_share) as seller_tax_share_this_disbursement_id_or_invoiced,
    sum(seller_tax_share_refund) as seller_tax_share_refund_this_disbursement_id_or_invoiced,
    sum(balance_adjustment) as balance_adjustment_this_disbursement_id_or_invoiced,
    sum(seller_rev_credit) as seller_rev_credit_this_disbursement_id_or_invoiced,
    sum(aws_ref_fee_credit) as aws_ref_fee_credit_this_disbursement_id_or_invoiced
  from
    billing_event_with_categorized_transaction as billing_categorized
  group by
    internal_buyer_line_item_id,
    disbursement_id,
    disbursement_id_or_invoiced,
    broker_id,
    -- The following columns are included the in group by but they are intentionally omitted from the PK.
    -- These columns should have the _same_ values for each record in the PK.
    product_id,
    currency,
    agreement_id,
    proposer_account_id,
    acceptor_account_id,
    offer_id,
    end_user_account_id,
    usage_period_start_date,
    usage_period_end_date,
    buyer_transaction_reference_id,
    bank_trace_id,
    disbursement_date
),

-- listagg function in athena does not support partitioning, grouping here and then joining to the main query
disbursement_list as (
  select
    internal_buyer_line_item_id,
    listagg(cast(disbursement_date as varchar),',') within group (order by cast(disbursement_date as varchar)) as disbursement_date_list,
    listagg(bank_trace_id,',') within group (order by cast(disbursement_date as varchar)) as disburse_bank_trace_id_list
    from (
    -- listagg function in athena does not support ordering by another field when distinct is used,
    -- here we first select distinct bank_trace_ids and then do the listagg order by disbursement_date
    select distinct internal_buyer_line_item_id, disbursement_date, bank_trace_id
  from billing_event_with_business_flags) distinct_disbursements
  group by internal_buyer_line_item_id
  order by internal_buyer_line_item_id
),

line_items_with_window_functions as (
--add flag next step compare gross_revenue and gross_revenue_disbursed or gross_refund and gross_refund_disbursed
  select
    line_item.internal_buyer_line_item_id,
    disbursement_id,
    disbursement_id_or_invoiced,
    product_id,
    broker_id,
    currency,
    agreement_id,
    proposer_account_id,
    acceptor_account_id,
    -- when there's aws_rev_Share adjustment/refund to a seller_rev_share invoice, it can happen that for the same aws_rev_share invoice_id, there are multiple disbursement events,
    -- using windows function to map payer_account_id of seller_rev_share to all corresponding aws_rev_Share
    max(payer_account_id) over (partition by line_item.internal_buyer_line_item_id) as payer_account_id,
    offer_id,
    end_user_account_id,
    usage_period_start_date,
    usage_period_end_date,
    payment_due_date,
    bank_trace_id,
    disbursement_date,
    billing_address_id,

    -- Buyer/seller columns:
    max(buyer_invoice_id) over (partition by line_item.internal_buyer_line_item_id) as buyer_invoice_id,
    seller_invoice_id,
    max(buyer_invoice_date) over (partition by line_item.internal_buyer_line_item_id) as buyer_invoice_date,
    seller_invoice_date,

    -- When disbursement_id_or_invoiced = ''<invoiced>', these are actually invoiced amounts
    -- When disbursement_id_or_invoiced <> ''<invoiced>' these are disbursed amounts for _this_ specific disbursement_id
    gross_revenue_this_disbursement_id_or_invoiced,
    gross_refund_this_disbursement_id_or_invoiced,
    cogs_this_disbursement_id_or_invoiced,
    cogs_refund_this_disbursement_id_or_invoiced,
    aws_rev_share_this_disbursement_id_or_invoiced,
    aws_refund_share_this_disbursement_id_or_invoiced,
    aws_tax_share_this_disbursement_id_or_invoiced,
    aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,
    seller_tax_share_this_disbursement_id_or_invoiced,
    seller_tax_share_refund_this_disbursement_id_or_invoiced,
    balance_adjustment_this_disbursement_id_or_invoiced,
    seller_rev_credit_this_disbursement_id_or_invoiced,
    aws_ref_fee_credit_this_disbursement_id_or_invoiced,
    -- IMPORTANT: All window functions partitioned by internal_buyer_line_item_id:

    -- Invoiced amounts, categorized by transaction type:
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then gross_revenue_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end)over (partition by line_item.internal_buyer_line_item_id) as gross_revenue_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then gross_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as gross_refund_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then cogs_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then cogs_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_refund_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then aws_rev_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_rev_share_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then aws_refund_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_refund_share_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then aws_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then aws_tax_share_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_listing_fee_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then aws_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_listing_fee_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then seller_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then seller_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_refund_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then balance_adjustment_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as balance_adjustment_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then seller_rev_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_rev_credit_invoiced,
    sum(case when disbursement_id_or_invoiced = ''<invoiced>' then aws_ref_fee_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_ref_fee_credit_invoiced,

    -- Total disbursed amounts (for all disbursement_id values), categorized by transaction type:
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then gross_revenue_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as gross_revenue_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then gross_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as gross_refund_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then cogs_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then cogs_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_refund_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then aws_rev_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_rev_share_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then aws_refund_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_refund_share_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then aws_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then aws_tax_share_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_listing_fee_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then aws_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_listing_fee_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then seller_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then seller_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_refund_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then balance_adjustment_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as balance_adjustment_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then seller_rev_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_rev_credit_disbursed,
    sum(case when disbursement_id_or_invoiced '<> ''<invoiced>' then aws_ref_fee_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_ref_fee_credit_disbursed,

    -- aggregate multiple disbursement
    max(disbursement_date) over (partition by line_item.internal_buyer_line_item_id) as last_disbursement_date,
    first_value(case when disbursement_id_or_invoiced = ''<invoiced>' then null else disbursement_id_or_invoiced end) over(partition by line_item.internal_buyer_line_item_id order by coalesce(disbursement_date,cast('1900-01-01' as timestamp)) desc rows between unbounded preceding and unbounded following) as last_disbursement_id,
    first_value(bank_trace_id) over (partition by line_item.internal_buyer_line_item_id order by coalesce(disbursement_date,cast('1900-01-01' as timestamp)) desc rows between unbounded preceding and unbounded following) as last_disburse_bank_trace_id,
    disb_list.disbursement_date_list,
    disb_list.disburse_bank_trace_id_list
  from
    line_items_aggregated as line_item
    left join disbursement_list disb_list
      on line_item.internal_buyer_line_item_id = disb_list.internal_buyer_line_item_id
),

cppo_offer_id as (
  select
    -- Channel partner offers do not exist in offertargetfeed_v1 table (as per legal requirement), causing cppo offer be defined as 'Public' in previous step, we will convert them back to 'Private' in next step
    offer_id
  from
    offers_with_uni_temporal_data
  where
    -- seller_account_id is null means the ISV owns the offer
    seller_account_id is not null
    and seller_account_id '<>  (select seller_account_id from seller_account)
  group by
    offer_id
),

line_items_with_window_functions_enrich_offer_product_address as (
  select
    internal_buyer_line_item_id,
    disbursement_id,
    disbursement_id_or_invoiced,
    line.product_id,
    legacy_product.legacy_id as legacy_product_id,
    products.title as product_title,
    line.broker_id,
    line.currency,
    line.end_user_account_id,
    acc_enduser.encrypted_account_id as end_user_encrypted_account_id,
    acc_enduser.aws_account_id as end_user_aws_account_id,
    acc_payer.aws_account_id as payer_aws_account_id,
    acc_payer.encrypted_account_id payer_encrypted_account_id,
    line.agreement_id,
    agreement.agreement_revision,
    line.proposer_account_id,
    case when offer.offer_id like 'aiqoffer-%' then null else agreement.start_date end as Agreement_Start_Date,
    case when offer.offer_id like 'aiqoffer-%' then null else agreement.end_date end as Agreement_End_Date,
    case when offer.offer_id like 'aiqoffer-%' then null else agreement.acceptance_date end as Agreement_Acceptance_Date,
    case when offer.offer_id like 'aiqoffer-%' then null else agreement.valid_from end as agreement_updated_date,
    case when offer.offer_id like 'aiqoffer-%' then null else line.usage_period_start_date end as Usage_Period_Start_Date,
    case when offer.offer_id like 'aiqoffer-%' then null else line.usage_period_end_date end as Usage_Period_End_Date,

    line.acceptor_account_id,
    acc_subscriber.aws_account_id as subscriber_aws_account_id,
    acc_subscriber.encrypted_account_id as subscriber_encrypted_account_id,
    offer.offer_id,
    case
      when offer.offer_id in (
        select distinct offer_id
        from cppo_offer_id)
        then 'Private'
      else offer.offer_target
    end as offer_target,
    offer.name offer_name,
    offer.opportunity_name offer_opportunity_name,
    offer.opportunity_description offer_opportunity_description,
    offer.opportunity_id,
    payment_due_date,
    line.bank_trace_id,
    disbursement_date,
    billing_address_id,
    buyer_invoice_id,
    seller_invoice_id,
    buyer_invoice_date,
    seller_invoice_date,
    gross_revenue_this_disbursement_id_or_invoiced,
    gross_refund_this_disbursement_id_or_invoiced,
    cogs_this_disbursement_id_or_invoiced,
    cogs_refund_this_disbursement_id_or_invoiced,
    aws_rev_share_this_disbursement_id_or_invoiced,
    aws_refund_share_this_disbursement_id_or_invoiced,
    aws_tax_share_this_disbursement_id_or_invoiced,
    aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,
    seller_tax_share_this_disbursement_id_or_invoiced,
    seller_tax_share_refund_this_disbursement_id_or_invoiced,
    balance_adjustment_this_disbursement_id_or_invoiced,
    seller_rev_credit_this_disbursement_id_or_invoiced,
    aws_ref_fee_credit_this_disbursement_id_or_invoiced,
    gross_revenue_invoiced,
    gross_refund_invoiced,
    cogs_invoiced,
    cogs_refund_invoiced,
    aws_rev_share_invoiced,
    aws_refund_share_invoiced,
    aws_tax_share_invoiced,
    aws_tax_share_listing_fee_invoiced,
    aws_tax_share_refund_invoiced,
    aws_tax_share_refund_listing_fee_invoiced,
    seller_tax_share_invoiced,
    seller_tax_share_refund_invoiced,
    balance_adjustment_invoiced,
    seller_rev_credit_invoiced,
    aws_ref_fee_credit_invoiced,
    gross_revenue_disbursed,
    gross_refund_disbursed,
    cogs_disbursed,
    cogs_refund_disbursed,
    aws_rev_share_disbursed,
    aws_refund_share_disbursed,
    aws_tax_share_disbursed,
    aws_tax_share_listing_fee_disbursed,
    aws_tax_share_refund_disbursed,
    aws_tax_share_refund_listing_fee_disbursed,
    seller_tax_share_disbursed,
    seller_tax_share_refund_disbursed,
    balance_adjustment_disbursed,
    seller_rev_credit_disbursed,
    aws_ref_fee_credit_disbursed,
    last_disbursement_date,
    last_disbursement_id,
    last_disburse_bank_trace_id,
    disbursement_date_list,
    disburse_bank_trace_id_list,
    products.product_code,
    acc_products.aws_account_id as manufacturer_aws_account_id,
    products.manufacturer_account_id,
    --add subscriber and payer addressID, payer address preference order: tax address>billing address>mailing address,  subscriber address preference order: tax address> mailing address
    coalesce (
      --empty value in Athena shows as '', change all '' value to null in order to follow the preference order logic above
      case when acc_subscriber.tax_address_id ='' then null else acc_subscriber.tax_address_id end,
      case when acc_subscriber.mailing_address_id = '' then null else acc_subscriber.mailing_address_id end) as subscriber_address_id,
    coalesce (
      case when acc_payer.tax_address_id = '' then null else acc_payer.tax_address_id end,
      case when line.billing_address_id = '' then null else line.billing_address_id end,
      case when acc_payer.mailing_address_id = '' then null else acc_payer.mailing_address_id end) as payer_address_id,
    coalesce (
      case when acc_enduser.tax_address_id = '' then null else acc_enduser.tax_address_id end,
      case when line.billing_address_id = '' then null else line.billing_address_id end,
      case when acc_enduser.mailing_address_id = '' then null else acc_enduser.mailing_address_id end) as end_user_address_id
  from
    line_items_with_window_functions as line
  left join agreements_with_history as agreement on
      line.agreement_id = agreement.agreement_id and line.buyer_invoice_date >= agreement.valid_from_adjusted and line.buyer_invoice_date < agreement.valid_to
  left join offers_with_history_with_target_type as offer on
        line.offer_id = offer.offer_id and line.buyer_invoice_date >= offer.valid_from and line.buyer_invoice_date < offer.valid_to
  left join products_with_history as products on
        line.product_id = products.product_id and line.buyer_invoice_date >= products.valid_from_adjusted and line.buyer_invoice_date < products.valid_to
  left join legacy_products as legacy_product on
        line.product_id = legacy_product.new_id
  left join accounts_with_history_with_company_name as acc_payer on
        line.payer_account_id = acc_payer.account_id and line.buyer_invoice_date >= acc_payer.valid_from and line.buyer_invoice_date < acc_payer.valid_to
  left join accounts_with_history_with_company_name as acc_enduser on
        line.end_user_account_id = acc_enduser.account_id and line.buyer_invoice_date >= acc_enduser.valid_from and line.buyer_invoice_date < acc_enduser.valid_to
  left join accounts_with_history_with_company_name as acc_subscriber on
        line.acceptor_account_id = acc_subscriber.account_id and line.buyer_invoice_date >= acc_subscriber.valid_from and line.buyer_invoice_date < acc_subscriber.valid_to
  left join accounts_with_history_with_company_name as acc_products on
        products.manufacturer_account_id = acc_products.account_id and line.buyer_invoice_date >= acc_products.valid_from and line.buyer_invoice_date < acc_products.valid_to

),

line_items_with_window_functions_enrich_offer_product_address_name as (
  select
    line.internal_buyer_line_item_id,
    disbursement_id,
    disbursement_id_or_invoiced,
    product_id,
    legacy_product_id,
    product_title,
    broker_id,
    currency,
    end_user_address_id,
    end_user_account_id,
    end_user_encrypted_account_id,
    end_user_aws_account_id,
    add_enduser.company_name end_user_company_name,
    add_enduser.email_domain end_user_email_domain,
    add_enduser.city end_user_city,
    add_enduser.state_or_region end_user_state,
    add_enduser.country_code end_user_country,
    add_enduser.postal_code end_user_postal_code,
    payer_aws_account_id,
    payer_encrypted_account_id,
    payer_address_id,
    add_payer.company_name payer_company_name,
    add_payer.email_domain payer_email_domain,
    add_payer.city payer_city,
    add_payer.state_or_region payer_state,
    add_payer.country_code payer_country,
    add_payer.postal_code payer_postal_code,
    agreement_id,
    agreement_revision,
    agreement_start_date,
    agreement_end_date,
    agreement_acceptance_date,
    agreement_updated_date,
    case when proposer_account_id = (select seller_account_id from seller_account) then null else acc_proposer.aws_account_id end as reseller_aws_account_id,
    case when proposer_account_id = (select seller_account_id from seller_account) then null else acc_proposer.mailing_company_name end as reseller_company_name,
    usage_period_start_date,
    usage_period_end_date,
    proposer_account_id,
    acc_proposer.aws_account_id as proposer_aws_account_id,
    acceptor_account_id,
    subscriber_aws_account_id,
    subscriber_encrypted_account_id,
    subscriber_address_id,
    add_subscriber.company_name subscriber_company_name,
    add_subscriber.email_domain subscriber_email_domain,
    add_subscriber.city subscriber_city,
    add_subscriber.state_or_region subscriber_state,
    add_subscriber.country_code subscriber_country,
    add_subscriber.postal_code subscriber_postal_code,
    offer_id,
    offer_target,
    offer_name,
    offer_opportunity_name,
    offer_opportunity_description,
    opportunity_id,
    payment_due_date,
    bank_trace_id,
    disbursement_date,
    billing_address_id,
    max(buyer_invoice_id)as buyer_invoice_id,
    max(seller_invoice_id)as seller_invoice_id,
    max(buyer_invoice_date)as buyer_invoice_date,
    max(seller_invoice_date)as seller_invoice_date,
    gross_revenue_this_disbursement_id_or_invoiced,
    gross_refund_this_disbursement_id_or_invoiced,
    cogs_this_disbursement_id_or_invoiced,
    cogs_refund_this_disbursement_id_or_invoiced,
    aws_rev_share_this_disbursement_id_or_invoiced,
    aws_refund_share_this_disbursement_id_or_invoiced,
    aws_tax_share_this_disbursement_id_or_invoiced,
    aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,
    seller_tax_share_this_disbursement_id_or_invoiced,
    seller_tax_share_refund_this_disbursement_id_or_invoiced,
    balance_adjustment_this_disbursement_id_or_invoiced,
    seller_rev_credit_this_disbursement_id_or_invoiced,
    aws_ref_fee_credit_this_disbursement_id_or_invoiced,
    (gross_revenue_this_disbursement_id_or_invoiced + gross_refund_this_disbursement_id_or_invoiced + aws_rev_share_this_disbursement_id_or_invoiced + aws_refund_share_this_disbursement_id_or_invoiced + seller_tax_share_this_disbursement_id_or_invoiced + seller_tax_share_refund_this_disbursement_id_or_invoiced
      + cogs_this_disbursement_id_or_invoiced + cogs_refund_this_disbursement_id_or_invoiced + aws_tax_share_listing_fee_this_disbursement_id_or_invoiced + aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced) as seller_net_revenue_this_disbursement_id_or_invoiced,
    gross_revenue_invoiced,
    gross_refund_invoiced,
    cogs_invoiced,
    cogs_refund_invoiced,
    aws_rev_share_invoiced,
    aws_refund_share_invoiced,
    aws_tax_share_invoiced,
    aws_tax_share_listing_fee_invoiced,
    aws_tax_share_refund_invoiced,
    aws_tax_share_refund_listing_fee_invoiced,
    seller_tax_share_invoiced,
    seller_tax_share_refund_invoiced,
    balance_adjustment_invoiced,
    seller_rev_credit_invoiced,
    aws_ref_fee_credit_invoiced,
    gross_revenue_disbursed,
    gross_refund_disbursed,
    cogs_disbursed,
    cogs_refund_disbursed,
    aws_rev_share_disbursed,
    aws_refund_share_disbursed,
    aws_tax_share_disbursed,
    aws_tax_share_listing_fee_disbursed,
    aws_tax_share_refund_disbursed,
    aws_tax_share_refund_listing_fee_disbursed,
    seller_tax_share_disbursed,
    seller_tax_share_refund_disbursed,
    balance_adjustment_disbursed,
    seller_rev_credit_disbursed,
    aws_ref_fee_credit_disbursed,
    (gross_revenue_invoiced + gross_revenue_disbursed) as uncollected_gross_revenue,
    -- net revenue = gross revenue - listing fee - tax - cogs
    (gross_revenue_invoiced + gross_refund_invoiced + aws_rev_share_invoiced + aws_refund_share_invoiced + seller_tax_share_invoiced + seller_tax_share_refund_invoiced + cogs_invoiced + cogs_refund_invoiced + aws_tax_share_listing_fee_invoiced + aws_tax_share_refund_listing_fee_invoiced) as seller_net_revenue,
    (gross_revenue_invoiced + gross_refund_invoiced + aws_rev_share_invoiced + aws_refund_share_invoiced + seller_tax_share_invoiced + seller_tax_share_refund_invoiced + cogs_invoiced + cogs_refund_invoiced + aws_tax_share_listing_fee_invoiced + aws_tax_share_refund_listing_fee_invoiced
      + gross_revenue_disbursed + gross_refund_disbursed + aws_rev_share_disbursed + aws_refund_share_disbursed + seller_tax_share_disbursed + seller_tax_share_refund_disbursed + cogs_disbursed + cogs_refund_disbursed + aws_tax_share_listing_fee_disbursed + aws_tax_share_refund_listing_fee_disbursed) as uncollected_seller_net_revenue,
    last_disbursement_date,
    last_disbursement_id,
    last_disburse_bank_trace_id,
    disbursement_date_list,
    disburse_bank_trace_id_list,
    product_code,
    manufacturer_aws_account_id,
    manufacturer_account_id,
    acc_manu.mailing_company_name as manufacturer_company_name,
    cast(null as varchar) as AR_Period,
    case
      when (
        (gross_revenue_invoiced '<>0 and gross_revenue_invoiced = -1 * gross_revenue_disbursed)
        or (gross_refund_invoiced '<> 0 and gross_refund_invoiced = -1 * gross_refund_disbursed)
        or (balance_adjustment_invoiced '<> 0 and balance_adjustment_invoiced = -1 * balance_adjustment_disbursed)
        or (seller_tax_share_refund_invoiced '<> 0 and seller_tax_share_refund_invoiced = -1 * seller_tax_share_refund_disbursed)
        or (gross_revenue_invoiced = 0 and gross_refund_invoiced = 0 and balance_adjustment_invoiced = 0 and seller_tax_share_refund_invoiced = 0 and last_disbursement_id is not null)) then 'Yes'
      when gross_revenue_disbursed = 0 and gross_refund_disbursed = 0 and balance_adjustment_disbursed = 0 and seller_tax_share_disbursed = 0 and seller_tax_share_refund_disbursed = 0 then 'No'
      else 'Partial'
    end as Disbursement_Flag
  from line_items_with_window_functions_enrich_offer_product_address as line
  left join accounts_with_history_with_company_name as acc_manu on
    line.manufacturer_account_id = acc_manu.account_id and line.buyer_invoice_date >= acc_manu.valid_from_adjusted and line.buyer_invoice_date <= acc_manu.valid_to
  left join accounts_with_history_with_company_name as acc_proposer on
    line.proposer_account_id = acc_proposer.account_id and line.buyer_invoice_date >= acc_proposer.valid_from and line.buyer_invoice_date < acc_proposer.valid_to
  left join address_with_latest_revision as add_payer on
    line.payer_address_id = add_payer.address_id
  left join address_with_latest_revision as add_subscriber on
    line.subscriber_address_id = add_subscriber.address_id
  left join address_with_latest_revision as add_enduser on
    line.end_user_address_id = add_enduser.address_id
  group by
    line.internal_buyer_line_item_id,
    disbursement_id,
    disbursement_id_or_invoiced,
    product_id,
    legacy_product_id,
    product_title,
    broker_id,
    currency,
    end_user_address_id,
    end_user_account_id,
    end_user_encrypted_account_id,
    end_user_aws_account_id,
    add_enduser.company_name,
    add_enduser.email_domain,
    add_enduser.city,
    add_enduser.state_or_region,
    add_enduser.country_code,
    add_enduser.postal_code,
    payer_aws_account_id,
    payer_encrypted_account_id,
    payer_address_id,
    add_payer.company_name,
    add_payer.email_domain,
    add_payer.city,
    add_payer.state_or_region,
    add_payer.country_code,
    add_payer.postal_code,
    agreement_id,
    agreement_revision,
    case when proposer_account_id = (select seller_account_id from seller_account) then null else acc_proposer.aws_account_id end,
    case when proposer_account_id = (select seller_account_id from seller_account) then null else acc_proposer.mailing_company_name end,
    agreement_start_date,
    agreement_end_date,
    agreement_acceptance_date,
    agreement_updated_date,
    usage_period_start_date,
    usage_period_end_date,
    acceptor_account_id,
    subscriber_aws_account_id,
    subscriber_encrypted_account_id,
    subscriber_address_id,
    add_subscriber.company_name,
    add_subscriber.email_domain,
    add_subscriber.city,
    add_subscriber.state_or_region,
    add_subscriber.country_code,
    add_subscriber.postal_code,
    offer_id,
    offer_target,
    offer_name,
    offer_opportunity_name,
    offer_opportunity_description,
    opportunity_id,
    payment_due_date,
    bank_trace_id,
    disbursement_date,
    billing_address_id,
    gross_revenue_this_disbursement_id_or_invoiced,
    gross_refund_this_disbursement_id_or_invoiced,
    cogs_this_disbursement_id_or_invoiced,
    cogs_refund_this_disbursement_id_or_invoiced,
    aws_rev_share_this_disbursement_id_or_invoiced,
    aws_refund_share_this_disbursement_id_or_invoiced,
    aws_tax_share_this_disbursement_id_or_invoiced,
    aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_this_disbursement_id_or_invoiced,
    aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,
    seller_tax_share_this_disbursement_id_or_invoiced,
    seller_tax_share_refund_this_disbursement_id_or_invoiced,
    balance_adjustment_this_disbursement_id_or_invoiced,
    seller_rev_credit_this_disbursement_id_or_invoiced,
    aws_ref_fee_credit_this_disbursement_id_or_invoiced,
    gross_revenue_invoiced,
    gross_refund_invoiced,
    cogs_invoiced,
    cogs_refund_invoiced,
    aws_rev_share_invoiced,
    aws_refund_share_invoiced,
    aws_tax_share_invoiced,
    aws_tax_share_listing_fee_invoiced,
    aws_tax_share_refund_invoiced,
    aws_tax_share_refund_listing_fee_invoiced,
    seller_tax_share_invoiced,
    seller_tax_share_refund_invoiced,
    balance_adjustment_invoiced,
    seller_rev_credit_invoiced,
    aws_ref_fee_credit_invoiced,
    gross_revenue_disbursed,
    gross_refund_disbursed,
    cogs_disbursed,
    cogs_refund_disbursed,
    aws_rev_share_disbursed,
    aws_refund_share_disbursed,
    aws_tax_share_disbursed,
    aws_tax_share_listing_fee_disbursed,
    aws_tax_share_refund_disbursed,
    aws_tax_share_refund_listing_fee_disbursed,
    seller_tax_share_disbursed,
    seller_tax_share_refund_disbursed,
    balance_adjustment_disbursed,
    seller_rev_credit_disbursed,
    aws_ref_fee_credit_disbursed,
    last_disbursement_date,
    last_disbursement_id,
    last_disburse_bank_trace_id,
    disbursement_date_list,
    disburse_bank_trace_id_list,
    product_code,
    manufacturer_aws_account_id,
    manufacturer_account_id,
    acc_manu.mailing_company_name,
    proposer_account_id,
    acc_proposer.aws_account_id
),
invoiced_not_disbursed as(
  select
    --we will filter on rownum =1 in next step,
    -- means internal_buyer_line_item_id, there's only '<invoiced> record, no disbursement_id linked
    *,
    max(case when disbursement_id_or_invoiced = ''<invoiced>' then 1 else 2 end)
      over (partition by internal_buyer_line_item_id) rownum
  from line_items_with_window_functions_enrich_offer_product_address_name as line_items

),
collections_and_disbursements as (
  select
    ------------------
    -- Invoice Info --
    ------------------
    buyer_invoice_date as Invoice_Date,
    Payment_Due_Date as Payment_Due_Date,
    concat(
      'Net ',
      case
        when abs(date_diff('Day', Payment_due_date, buyer_invoice_date))>180 then '180+'
        else cast(abs(date_diff('Day', Payment_due_date, buyer_invoice_date)) as varchar)
        end,
      ' days'
    ) as payment_terms,
    buyer_invoice_id as Invoice_ID,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when seller_invoice_id = '' then null else seller_invoice_id end,
      'Not applicable') as Listing_Fee_Invoice_ID,

    ---------------------------
    --End user Information --
    ---------------------------
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when End_User_Company_Name = '' then null else End_User_Company_Name end,
      'Not available') as End_User_Company_Name,
    End_User_AWS_Account_ID,
    End_User_Encrypted_Account_ID,
    End_User_Email_Domain,
    End_User_City,
    End_User_State as End_User_State_or_Region,
    End_User_Country,
    End_User_Postal_Code,
    End_User_Address_ID,

    ---------------------------
    --Subscriber Information --
    ---------------------------
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Company_Name is null or Subscriber_Company_Name = '' then 'Not provided'
      else Subscriber_Company_Name
    end as Subscriber_Company_Name,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      else Subscriber_AWS_Account_ID
    end as Subscriber_AWS_Account_ID,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      else Subscriber_Encrypted_Account_ID
    end as Subscriber_Encrypted_Account_ID,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Email_Domain is null or Subscriber_Email_Domain = '' then 'Not provided'
      else Subscriber_Email_Domain
    end as Subscriber_Email_Domain,
    case
      when Agreement_id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_City is null or Subscriber_City = '' then 'Not provided'
      else Subscriber_City
    end as Subscriber_City,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_State is null or Subscriber_State = '' then 'Not provided'
      else Subscriber_State
    end as Subscriber_State_or_Region,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Country is null or Subscriber_Country = '' then 'Not provided'
      else Subscriber_Country
    end as Subscriber_Country,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Postal_Code is null or Subscriber_Postal_Code = '' then 'Not provided'
      else Subscriber_Postal_Code
    end as Subscriber_Postal_Code,
    case
      when Agreement_ID is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Address_ID is null or Subscriber_Address_ID = '' then 'Not provided'
      else Subscriber_Address_ID
    end as Subscriber_Address_ID,

    ----------------------
    -- Procurement Info --
    ----------------------
    -- product title at time of invoice. It is possible that the title changes over time and therefore there may be multiple product titles mapped to a single product id.
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Product_Title = '' then null else Product_Title end,
      'Not provided') as Product_Title,
    -- offer name at time of invoice. It is possible that the name changes over time therefore there may be multiple offer names mapped to a single offer id.
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when (Offer_Name is null or Offer_Name = '') and Offer_Target = 'Public' then 'Not applicable'
      else Offer_Name
    end as Offer_Name,
    case
      when Agreement_Id is null or Agreement_ID = ''
      then 'Not available'
      else Offer_ID
    end as Offer_ID,
    -- offer visibility at time of invoice.,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      else Offer_Target
    end as Offer_Visibility,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Agreement_ID = '' then null else Agreement_ID end,
      'Not available') as Agreement_ID,
    Agreement_Start_Date,
    Agreement_Acceptance_Date,
    Agreement_End_Date,

    Usage_Period_Start_Date,
    Usage_Period_End_Date,

    -----------------------
    -- Disbursement Info --
    -----------------------
    case
      when Disbursement_Flag = 'Yes' then 'Disbursed'
      when Disbursement_Flag = 'No' then 'Not Disbursed'
      else 'Other'
    end as Disbursement_Status,
    last_disbursement_date as disbursement_date,
    case
      when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date))
      else null
    end as Disbursement_Time,
    case
      when Disbursement_Flag = 'No' then 'Not applicable'
      when bank_trace_id is null or bank_trace_id = '' then 'Not available'
      else bank_trace_id
    end as disburse_bank_trace_id,

    --------------
    -- Revenues --
    --------------
    -- We are rounding the sums using 2 decimal precision
    -- Note that the rounding method might differ between SQL implementations.
    -- The monthly revenue report is using RoundingMode.HALF_UP. This might create tiny discrepancies between this SQL output
    -- and the legacy report
    round(-1 * gross_revenue_this_disbursement_id_or_invoiced,2) as Gross_Revenue,
    round(-1 * gross_refund_this_disbursement_id_or_invoiced,2) as Gross_Refund,
    round(-1 * aws_rev_share_this_disbursement_id_or_invoiced,2) as Listing_Fee,
    round(-1 * aws_refund_share_this_disbursement_id_or_invoiced,2) as Listing_Fee_Refund,
    truncate(
      case
        when gross_revenue_this_disbursement_id_or_invoiced != 0 then abs(aws_rev_share_this_disbursement_id_or_invoiced/gross_revenue_this_disbursement_id_or_invoiced)
        when gross_refund_this_disbursement_id_or_invoiced != 0 then abs(aws_refund_share_this_disbursement_id_or_invoiced/gross_refund_this_disbursement_id_or_invoiced)
        else 0
      end
      ,4) as Listing_Fee_Percentage,
    round(-1 * seller_tax_share_this_disbursement_id_or_invoiced,2) as Seller_Tax_Share,
    round(-1 * seller_tax_share_refund_this_disbursement_id_or_invoiced,2) as Seller_Tax_Share_Refund,
    round(-1 * aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,2) as AWS_Tax_Share_Listing_Fee,
    round(-1 * aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,2) as AWS_Tax_Share_Refund_Listing_Fee,
    round(-1 * cogs_this_disbursement_id_or_invoiced,2) as Wholesale_cost,
    round(-1 * cogs_refund_this_disbursement_id_or_invoiced,2) as Wholesale_cost_Refund,
    round(-1 * seller_net_revenue_this_disbursement_id_or_invoiced,2) as Seller_Net_Revenue,
    currency as Currency,

    substring(internal_buyer_line_item_id,1,strpos(internal_buyer_line_item_id,'-')-1) as Transaction_Reference_ID,
    broker_id as AWS_seller_of_record,

    -----------------
    -- Resale info --
    -----------------
    case
      when Opportunity_Id is null or Opportunity_Id = '' then
        case
          when Offer_Target = 'Public' then 'Not applicable'
          when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable'
          else null
        end
      else Opportunity_Id
    end as Resale_authorization_ID,
    case
      when Offer_Opportunity_Name is null or Offer_Opportunity_Name = '' then
        case
          when Offer_Target = 'Public' then 'Not applicable'
          when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable'
          else null
        end
      else Offer_Opportunity_Name
    end as Resale_authorization_name,
    case
      when Offer_Opportunity_Description is null or Offer_Opportunity_Description = '' then
        case
          when Offer_Target = 'Public' then 'Not applicable'
          when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable'
          else null
        end
      else Offer_Opportunity_Description
    end as Resale_authorization_description,
    case
      when (Reseller_AWS_Account_ID is not null and Reseller_AWS_Account_ID != '')
        and (Reseller_Company_Name is null or Reseller_Company_Name = '') then 'Not available'
      when (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '')
        and (opportunity_id is null or opportunity_id = '') then 'Not applicable'
      when (select seller_account_id from seller_account) '<> manufacturer_aws_account_id
        and (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') then 'Not applicable'
      else Reseller_Company_Name
    end as Reseller_Company_Name,
    case
      when (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '')
        and (Opportunity_Id is null or Opportunity_Id = '') then 'Not applicable'
      when (select seller_account_id from seller_account) '<> manufacturer_aws_account_id
        and (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') then 'Not applicable'
      else Reseller_AWS_Account_ID
    end as Reseller_AWS_Account_ID,

    -----------------------
    -- Payer Information --
    -----------------------
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Payer_Company_Name = '' then null else Payer_Company_Name end,
      'Not available') as Payer_Company_Name,
    Payer_AWS_Account_ID, -- "Customer AWS Account Number" in legacy report
    Payer_Encrypted_Account_ID,
    Payer_Email_Domain,
    Payer_City,
    Payer_State as Payer_State_or_Region,
    Payer_Country,
    Payer_Postal_Code,
    Payer_Address_ID,

    ---------------------
    -- ISV Information --
    ---------------------
    manufacturer_aws_account_id as ISV_Account_ID,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Manufacturer_Company_Name = '' then null else Manufacturer_Company_Name end,
      'Not available') as ISV_Company_Name,

    ---------------------
    -- Products info --
    ---------------------
    Legacy_Product_ID,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Product_ID = '' then null else Product_ID end,
      'Not provided') as Product_ID,
    -- this is to get the legacy product id https://sim.amazon.com/issues/MP-INSIGHTS-2561
    Product_Code,

    case when Disbursement_Flag = 'Yes' then round(-1 * seller_net_revenue_this_disbursement_id_or_invoiced,2) else 0 end as Disbursed_Net_Revenue,
    case when Disbursement_Flag = 'No' then round(-1 * seller_net_revenue_this_disbursement_id_or_invoiced,2) else 0 end as Undisbursed_Net_Revenue,
    case
      when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <= 0 then 'Not due'
      when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=30 then '1-30 days late'
      when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=60 then '31-60 days late'
      when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=90 then '61-90 days late'
      when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=120 then '91-120 days late'
      when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end >=121 then '121+ days late'
      else null
    end as Disbursement_Period
  from
    line_items_with_window_functions_enrich_offer_product_address_name as line
  where disbursement_id_or_invoiced != ''<invoiced>'

  union

  select
    ------------------
    -- Invoice Info --
    ------------------
    buyer_invoice_date as Invoice_Date,
    Payment_Due_Date as Payment_Due_Date,
    concat(
      'Net ',
      case
        when abs(date_diff('Day', Payment_due_date, buyer_invoice_date)) >180 then '180+'
        else cast(abs(date_diff('Day', Payment_due_date, buyer_invoice_date)) as varchar)
      end,
      ' days'
    ) as payment_terms,
    buyer_invoice_id as Invoice_ID,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when seller_invoice_id = '' then null else seller_invoice_id end,
      'Not applicable') as Listing_Fee_Invoice_ID,

    ---------------------------
    --End user Information --
    ---------------------------
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when End_User_Company_Name = '' then null else End_User_Company_Name end,
      'Not available') as End_User_Company_Name,
    End_User_AWS_Account_ID,
    End_User_Encrypted_Account_ID,
    End_User_Email_Domain,
    End_User_City,
    End_User_State as End_User_State_or_Region,
    End_User_Country,
    End_User_Postal_Code,
    End_User_Address_ID,

    ---------------------------
    --Subscriber Information --
    ---------------------------
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Company_Name is null or Subscriber_Company_Name = '' then 'Not provided'
      else Subscriber_Company_Name
    end as Subscriber_Company_Name,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      else Subscriber_AWS_Account_ID
    end as Subscriber_AWS_Account_ID,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      else Subscriber_Encrypted_Account_ID
    end as Subscriber_Encrypted_Account_ID,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Email_Domain is null or Subscriber_Email_Domain = '' then 'Not provided'
      else Subscriber_Email_Domain
    end as Subscriber_Email_Domain,
    case
      when Agreement_id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_City is null or Subscriber_City = '' then 'Not provided'
      else Subscriber_City
    end as Subscriber_City,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_State is null or Subscriber_State = '' then 'Not provided'
      else Subscriber_State
    end as Subscriber_State_or_Region,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Country is null or Subscriber_Country = '' then 'Not provided'
      else Subscriber_Country
    end as Subscriber_Country,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Postal_Code is null or Subscriber_Postal_Code = '' then 'Not provided'
      else Subscriber_Postal_Code
    end as Subscriber_Postal_Code,
    case
      when Agreement_ID is null or Agreement_ID = '' then 'Not available'
      when Subscriber_Address_ID is null or Subscriber_Address_ID = '' then 'Not provided'
      else Subscriber_Address_ID
    end as Subscriber_Address_ID,

    ----------------------
    -- Procurement Info --
    ----------------------
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Product_Title = '' then null else Product_Title end,
      'Not provided') as Product_Title,
    -- offer name at time of invoice. It is possible that the name changes over time therefore there may be multiple offer names mapped to a single offer id.
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      when (Offer_Name is null or Offer_Name = '') and Offer_Target = 'Public' then 'Not applicable'
      else Offer_Name
    end as Offer_Name,
    case
      when Agreement_Id is null or Agreement_ID = ''
      then 'Not available'
      else Offer_ID
    end as Offer_ID,
    -- offer visibility at time of invoice.,
    case
      when Agreement_Id is null or Agreement_ID = '' then 'Not available'
      else Offer_Target
    end as Offer_Visibility,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Agreement_ID = '' then null else Agreement_ID end,
      'Not available') as Agreement_ID,
    --case when Agreement_Id is null or Agreement_Id = '' then cast(null as timestamp) else Agreement_Start_Date end as Agreement_Start_Date,
    --case when Agreement_Id is null or Agreement_Id = '' then cast(null as timestamp) else Agreement_End_Date end as Agreement_End_Date,
    --case when Agreement_Id is null or Agreement_Id = '' then cast(null as timestamp) else Agreement_Acceptance_Date end as Agreement_Acceptance_Date,
    Agreement_Start_Date,
    Agreement_Acceptance_Date,
    Agreement_End_Date,

    Usage_Period_Start_Date,
    Usage_Period_End_Date,

    -----------------------
    -- Disbursement Info --
    -----------------------
    case
      when Disbursement_Flag = 'Yes' then 'Disbursed'
      when Disbursement_Flag = 'No' then 'Not Disbursed'
      else 'Other'
    end as Disbursement_Status,
    last_disbursement_date as disbursement_date,
    case
      when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date))
      else null
    end as Disbursement_Time,
    case
      when Disbursement_Flag = 'No' then 'Not applicable'
      when bank_trace_id is null or bank_trace_id = '' then 'Not available'
      else bank_trace_id
    end as disburse_bank_trace_id,

    --------------
    -- Revenues --
    --------------
    -- We are rounding the sums using 2 decimal precision
    -- Note that the rounding method might differ between SQL implementations.
    -- The monthly revenue report is using RoundingMode.HALF_UP. This might create tiny discrepancies between this SQL output
    -- and the legacy report
    round(gross_revenue_this_disbursement_id_or_invoiced,2) as Gross_Revenue,
    round(gross_refund_this_disbursement_id_or_invoiced,2) as Gross_Refund,
    round(aws_rev_share_this_disbursement_id_or_invoiced,2) as Listing_Fee,
    round(aws_refund_share_this_disbursement_id_or_invoiced,2) as Listing_Fee_Refund,
    truncate(
      case
        when gross_revenue_this_disbursement_id_or_invoiced != 0 then abs(aws_rev_share_this_disbursement_id_or_invoiced/gross_revenue_this_disbursement_id_or_invoiced)
        when gross_refund_this_disbursement_id_or_invoiced != 0 then abs(aws_refund_share_this_disbursement_id_or_invoiced/gross_refund_this_disbursement_id_or_invoiced)
        else 0
      end
      ,4) as Listing_Fee_Percentage,
    round(seller_tax_share_this_disbursement_id_or_invoiced,2) as Seller_Tax_Share,
    round(seller_tax_share_refund_this_disbursement_id_or_invoiced,2) as Seller_Tax_Share_Refund,
    round(aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,2) as AWS_Tax_Share_Listing_Fee,
    round(aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,2) as AWS_Tax_Share_Refund_Listing_Fee,
    round(cogs_this_disbursement_id_or_invoiced,2) as Wholesale_cost,
    round(cogs_refund_this_disbursement_id_or_invoiced,2) as Wholesale_cost_Refund,
    round(seller_net_revenue_this_disbursement_id_or_invoiced,2) as Seller_Net_Revenue,
    currency as Currency,

    substring(internal_buyer_line_item_id,1,strpos(internal_buyer_line_item_id,'-')-1) as Transaction_Reference_ID,
    broker_id as AWS_seller_of_record,

    -----------------
    -- Resale info --
    -----------------
    case
      when Opportunity_Id is null or Opportunity_Id = '' then
        case
          when Offer_Target = 'Public' then 'Not applicable'
          when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable'
          else null
        end
      else Opportunity_Id
    end as Resale_authorization_ID,
    case
      when Offer_Opportunity_Name is null or Offer_Opportunity_Name = '' then
        case
          when Offer_Target = 'Public' then 'Not applicable'
          when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable'
          else null
        end
      else Offer_Opportunity_Name
    end as Resale_authorization_name,
    case
      when Offer_Opportunity_Description is null or Offer_Opportunity_Description = '' then
        case
          when Offer_Target = 'Public' then 'Not applicable'
          when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable'
          else null
        end
      else Offer_Opportunity_Description
    end as Resale_authorization_description,
    case
      when (Reseller_AWS_Account_ID is not null and Reseller_AWS_Account_ID != '')
        and (Reseller_Company_Name is null or Reseller_Company_Name = '') then 'Not available'
      when (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '')
        and (opportunity_id is null or opportunity_id = '') then 'Not applicable'
      when (select seller_account_id from seller_account) '<> manufacturer_aws_account_id
        and (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') then 'Not applicable'
      else Reseller_Company_Name
    end as Reseller_Company_Name,
    case
      when (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '')
        and (Opportunity_Id is null or Opportunity_Id = '') then 'Not applicable'
      when (select seller_account_id from seller_account) '<> manufacturer_aws_account_id
        and (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') then 'Not applicable'
      else Reseller_AWS_Account_ID
    end as Reseller_AWS_Account_ID,

    -----------------------
    -- Payer Information --
    -----------------------
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Payer_Company_Name = '' then null else Payer_Company_Name end,
      'Not available') as Payer_Company_Name,
    Payer_AWS_Account_ID, -- "Customer AWS Account Number" in legacy report
    Payer_Encrypted_Account_ID,
    Payer_Email_Domain,
    Payer_City,
    Payer_State as Payer_State_or_Region,
    Payer_Country,
    Payer_Postal_Code,
    Payer_Address_ID,

    ---------------------
    -- ISV Information --
    ---------------------
    manufacturer_aws_account_id as ISV_Account_ID,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Manufacturer_Company_Name = '' then null else Manufacturer_Company_Name end,
      'Not available') as ISV_Company_Name,

    ---------------------
    -- Products info --
    ---------------------
    -- product title at time of invoice. It is possible that the title changes over time and therefore there may be multiple product titles mapped to a single product id.
    Legacy_Product_ID,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when Product_ID = '' then null else Product_ID end,
      'Not provided') as Product_ID,
    -- this is to get the legacy product id https://sim.amazon.com/issues/MP-INSIGHTS-2561
    Product_Code,

    case when Disbursement_Flag = 'Yes' then round(seller_net_revenue_this_disbursement_id_or_invoiced,2) else 0 end as Disbursed_Net_Revenue,
    case when Disbursement_Flag = 'No' then round(seller_net_revenue_this_disbursement_id_or_invoiced,2) else 0 end as Undisbursed_Net_Revenue,
    case
      when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <= 0 then 'Not due'
      when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=30 then '1-30 days late'
      when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=60 then '31-60 days late'
      when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=90 then '61-90 days late'
      when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=120 then '91-120 days late'
      when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end >=121 then '121+ days late'
      else null
    end as Disbursement_Period
  from
    invoiced_not_disbursed
  where rownum = 1

)

select *
from collections_and_disbursements
where payment_due_date >= date_add('DAY', -90, current_date)
--where payment_due_date between cast('2023-01-01' as timestamp) and cast('2024-12-31' as timestamp)

--where disbursement_date >= date_add('DAY', -90, current_date)
--where disbursement_date between cast('2023-01-01' as timestamp) and cast('2024-12-31' as timestamp)
```

## Factures taxées
<a name="data-feed-example-tax"></a>

Pour trouver vos factures taxées, vous pouvez exécuter un ensemble de requêtes comme dans l'exemple suivant. Les requêtes s'appuient les unes sur les autres pour créer le rapport **fiscal**. Vous pouvez utiliser l'exemple tel qu'illustré ou le personnaliser en fonction de vos données et de vos cas d'utilisation.

Les commentaires contenus dans les requêtes expliquent à quoi servent les requêtes et comment les modifier.

```
-- Taxation report

-- General note: When executing this query we are assuming that the data ingested in the database is using
-- two time axes (the valid_from column and the update_date column).
-- See documentation for more details: https://docs.aws.amazon.com/marketplace/latest/userguide/data-feed.html#data-feed-details

-- An account_id has several valid_from dates (each representing a separate revision of the data)
-- but because of bi-temporality, an account_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
with accounts_with_uni_temporal_data as (
  select
    account_id,
    aws_account_id,
    encrypted_account_id,
    mailing_address_id,
    tax_address_id,
    tax_legal_name,
    from_iso8601_timestamp(valid_from) as valid_from,
    tax_registration_number
  from
    (
      select
        account_id,
        aws_account_id,
        encrypted_account_id,
        mailing_address_id,
        tax_address_id,
        tax_legal_name,
        valid_from,
        delete_date,
        tax_registration_number,
        row_number() over (partition by account_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
      from
        accountfeed_v1
    )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

accounts_with_history as (
  with accounts_with_history_with_extended_valid_from as (
    select
      account_id,
      -- sometimes, this columns gets imported as a "bigint" and loses heading 0s -> casting to a char and re-adding heading 0s (if need be)
      substring('000000000000'||cast(aws_account_id as varchar),-12) as aws_account_id,
      encrypted_account_id,
      mailing_address_id,
      tax_address_id,
      tax_legal_name tax_legal_name,
      -- The start time of account valid_from is extended to '1970-01-01 00:00:00', because:
      -- ... in tax report transformations, some tax line items with invoice_date cannot
      -- ... fall into the default valid time range of the associated account
      CASE
        WHEN LAG(valid_from) OVER (PARTITION BY account_id ORDER BY valid_from ASC) IS NULL
            THEN CAST('1970-01-01 00:00:00' as timestamp)
        ELSE valid_from
      END AS valid_from
    from
      (select * from accounts_with_uni_temporal_data ) as account
  )
  select
    account_id,
    aws_account_id,
    encrypted_account_id,
    mailing_address_id,
    tax_address_id,
    tax_legal_name,
    valid_from,
    coalesce(
      lead(valid_from) over (partition by account_id order by valid_from asc),
      cast('2999-01-01 00:00:00' as timestamp)
    ) as valid_to
  from
    accounts_with_history_with_extended_valid_from
),

-- A product_id has several valid_from dates (each representing a product revision),
-- but because of bi-temporality, each product_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
products_with_uni_temporal_data as (
  select
    from_iso8601_timestamp(valid_from) as valid_from,
    from_iso8601_timestamp(update_date) as update_date,
    from_iso8601_timestamp(delete_date) as delete_date,
    product_id,
    manufacturer_account_id,
    product_code,
    title
  from
    (
      select
        valid_from,
        update_date,
        delete_date,
        product_id,
        manufacturer_account_id,
        product_code,
        title,
        row_number() over (partition by product_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
      from
       productfeed_v1
      )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

products_with_history as (
  select
    product_id,
    title,
    valid_from,    
    case
      when lag(valid_from) over (partition by product_id order by valid_from asc) is null and valid_from < cast('2021-04-01' as timestamp)
        then date_add('Day', -3857, valid_from)
      -- 3827 is the longest delay between acceptance_date of an agreement and the product
      -- we are keeping 3857 as a consistency between the offers and products
      when lag(valid_from) over (partition by product_id order by valid_from asc) is null and valid_from >= cast('2021-04-01' as timestamp)
        then date_add('Day', -2190, valid_from)
      --after 2021 for the two offers we need to adjust for 2 more years
      else valid_from end as valid_from_adjusted,
    coalesce(
      lead(valid_from) over (partition by product_id order by valid_from asc),
      cast('2999-01-01 00:00:00' as timestamp)
    ) as valid_to,
    product_code,
    manufacturer_account_id
  from
    products_with_uni_temporal_data
),

-- A tax_item_id has several valid_from dates (each representing a product revision),
-- but because of bi-temporality, each tax_item_id + valid_from tuple can appear multiple times with a different update_date.
-- We are only interested in the most recent tuple (ie, uni-temporal model)
tax_items_with_uni_temporal_data as (
  select
    from_iso8601_timestamp(valid_from) as valid_from,
    from_iso8601_timestamp(update_date) as update_date,
    delete_date,
    cast(tax_item_id as varchar) as tax_item_id,
    cast(invoice_id as varchar) as invoice_id,
    cast(line_item_id as varchar) as line_item_id,
    cast(customer_bill_id as varchar) as customer_bill_id,
    tax_liable_party,
    transaction_type_code,
    product_id,
    product_tax_code,
    from_iso8601_timestamp(invoice_date) as invoice_date,
    taxed_customer_account_id,
    taxed_customer_country,
    taxed_customer_state_or_region,
    taxed_customer_city,
    taxed_customer_postal_code,
    tax_location_code_taxed_jurisdiction,
    tax_type_code,
    jurisdiction_level,
    taxed_jurisdiction,
    display_price_taxability_type,
    tax_jurisdiction_rate,
    tax_amount,
    tax_currency,
    tax_calculation_reason_code,
    date_used_for_tax_calculation,
    customer_exemption_certificate_id,
    customer_exemption_certificate_id_domain,
    customer_exemption_certificate_level,
    customer_exemption_code,
    customer_exemption_domain,
    transaction_reference_id
  from
    (
      select
        valid_from,
        update_date,
        delete_date,
        tax_item_id,
        invoice_id,
        line_item_id,
        customer_bill_id,
        tax_liable_party,
        transaction_type_code,
        product_id,
        product_tax_code,
        invoice_date,
        taxed_customer_account_id,
        taxed_customer_country,
        taxed_customer_state_or_region,
        taxed_customer_city,
        taxed_customer_postal_code,
        tax_location_code_taxed_jurisdiction,
        tax_type_code,
        jurisdiction_level,
        taxed_jurisdiction,
        display_price_taxability_type,
        tax_jurisdiction_rate,
        tax_amount,
        tax_currency,
        tax_calculation_reason_code,
        date_used_for_tax_calculation,
        customer_exemption_certificate_id,
        customer_exemption_certificate_id_domain,
        customer_exemption_certificate_level,
        customer_exemption_code,
        customer_exemption_domain,
        transaction_reference_id,
        row_number() over (partition by tax_item_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num
      from
        taxitemfeed_v1
    )
  where
    -- keep latest ...
    row_num = 1
    -- ... and remove the soft-deleted one.
    and (delete_date is null or delete_date = '')
),

taxation as (
  select
    tax_items.invoice_id,
    tax_items.line_item_id,
    tax_items.customer_bill_id,
    tax_items.tax_liable_party,
    tax_items.transaction_type_code,
    tax_items.product_id,
    product_tax_item.title as product_title,
    tax_items.product_tax_code,
    tax_items.invoice_date,
    accounts_with_history.aws_account_id as taxed_customer_account_id,
    tax_items.taxed_customer_country,
    tax_items.taxed_customer_state_or_region,
    tax_items.taxed_customer_city,
    tax_items.taxed_customer_postal_code,
    tax_items.tax_type_code as tax_type,
    tax_items.jurisdiction_level,
    tax_items.taxed_jurisdiction,
    tax_items.display_price_taxability_type,
    tax_items.tax_jurisdiction_rate,
    tax_items.tax_amount,
    tax_items.tax_currency,
    tax_items.tax_calculation_reason_code,
    tax_items.date_used_for_tax_calculation,
    coalesce(
      --empty value in Athena shows as '', change all '' value to null
      case when tax_items.customer_exemption_certificate_id = '' then null else tax_items.customer_exemption_certificate_id end,
      'Not exempt') customer_exemption_certificate_id,
    coalesce(--empty value in Athena shows as '', change all '' value to null
      case when tax_items.customer_exemption_certificate_id_domain = '' then null else tax_items.customer_exemption_certificate_id_domain end,
      'Not exempt') customer_exemption_certificate_id_domain,
    coalesce(--empty value in Athena shows as '', change all '' value to null
      case when tax_items.customer_exemption_certificate_level = '' then null else tax_items.customer_exemption_certificate_level end,
      'Not exempt') customer_exemption_certificate_level,
    coalesce(--empty value in Athena shows as '', change all '' value to null
      case when tax_items.customer_exemption_code = '' then null else tax_items.customer_exemption_code end,
      'Not exempt') customer_exemption_code,
    tax_items.transaction_reference_id
  from
    tax_items_with_uni_temporal_data as tax_items
    left join products_with_history as product_tax_item on
      tax_items.product_id = product_tax_item.product_id and tax_items.invoice_date >= product_tax_item.valid_from_adjusted and tax_items.invoice_date < product_tax_item.valid_to
    left join accounts_with_history as accounts_with_history on
      tax_items.taxed_customer_account_id = accounts_with_history.account_id and tax_items.invoice_date >= accounts_with_history.valid_from and tax_items.invoice_date < accounts_with_history.valid_to

)

select *
from taxation
where invoice_date >= date_add('DAY', -90, current_date)
--where invoice_date between cast('2023-01-01' as timestamp) and cast('2024-12-31' as timestamp)
```

## Déboursements par produit
<a name="data-feed-example-disbursement-by-product"></a>

Pour connaître les montants déboursés par produit, vous pouvez exécuter une série de requêtes telles que les suivantes. 

Ces exemples de requêtes s'appuient les unes sur les autres pour créer la liste finale des détails du produit avec les décaissements. Il montre également comment obtenir les informations sur le produit à un moment précis. Vous pouvez utiliser cet exemple comme indiqué, ou le personnaliser en fonction de vos données et de vos cas d'utilisation.

Les commentaires contenus dans les requêtes expliquent à quoi servent les requêtes et comment les modifier.

**Note**  
Lors de l'exécution de cette requête, nous supposons que les données sont ingérées à l'aide de deux axes temporels, les `update` colonnes `valid_from` et. Pour plus d'informations sur les axes, consultez[Stockage et structure des flux de AWS Marketplace données](data-feed-details.md).

```
    -- Get all the products and keep the latest product_id, valid_from tuple
    with products_with_uni_temporal_data as (
      select
       *
      from
      (
        select
         *,
         ROW_NUMBER() OVER (PARTITION BY product_id, valid_from 
             ORDER BY from_iso8601_timestamp(update_date) desc) 
             as row_num
        from
         productfeed_v1
      )
      where
        -- A product_id can appear multiple times with the same 
        -- valid_from date but with a different update_date column,
        -- making it effectively bi-temporal. By only taking the most
        -- recent tuple, we are converting to a uni-temporal model.
        row_num = 1
    ),

    -- Gets the latest revision of a product
    -- A product can have multiple revisions where some of the 
    -- columns, like the title, can change.
    -- For the purpose of the disbursement report, we want 
    -- to get the latest revision of a product
    products_with_latest_version as (
     select
      *
     from
     (
      select
       *,
       ROW_NUMBER() OVER (PARTITION BY product_id 
           ORDER BY from_iso8601_timestamp(valid_from) desc) 
           as row_num_latest_version
      from
       products_with_uni_temporal_data
     )
     where
      row_num_latest_version = 1
   ),

    -- Get all the accounts and keep the latest account_id, valid_from tuple
    accounts_with_uni_temporal_data as (
      select
       *
      from
      (
        select
         *,
         ROW_NUMBER() OVER (PARTITION BY account_id, valid_from ORDER BY from_iso8601_timestamp(update_date) desc) as row_num
        from
         accountfeed_v1
      )
      where
        -- An account_id can appear multiple times with the same 
        -- valid_from date but with a different update_date column,
        -- making it effectively bi-temporal. By only taking the most
        -- recent tuple, we are converting to a uni-temporal model.
        row_num = 1
    ),

    -- Gets the latest revision of an account
    -- An account can have multiple revisions where some of the 
    -- columns, like the mailing_address_id, can change.
    -- For the purpose of the disbursement report, we want 
    -- to get the latest revision of a product
    accounts_with_latest_version as (
     select
      *
     from
     (
      select
       *,
       ROW_NUMBER() OVER (PARTITION BY account_id 
           ORDER BY from_iso8601_timestamp(valid_from) desc) 
           as row_num_latest_version
      from
       accounts_with_uni_temporal_data
     )
     where
      row_num_latest_version = 1
   ),

    -- Get all the billing events and keep the 
    -- latest billing_event_id, valid_from tuple:
    billing_events_with_uni_temporal_data as (
      select
       *
      from (
        select
          billing_event_id,
          from_iso8601_timestamp(valid_from) as valid_from,
          from_iso8601_timestamp(update_date) as update_date,
          from_iso8601_timestamp(invoice_date) as invoice_date,
          transaction_type,
          transaction_reference_id,
          product_id,
          disbursement_billing_event_id,
          action,
          from_account_id,
          to_account_id,
          end_user_account_id,
          CAST(amount as decimal(20, 10)) invoice_amount,
          bank_trace_id,
          ROW_NUMBER() OVER (PARTITION BY billing_event_id, valid_from 
              ORDER BY from_iso8601_timestamp(update_date) desc) 
              as row_num
        from
          billingeventfeed_v1
        )
      where row_num = 1
    ),

    -- Get all the disbursements
    -- The billing events data is immutable.
    -- It is not required to use time windows based on the 
    -- valid_from column to get the most recent billing event
    disbursement_events as (
      select
        billing_events_raw.billing_event_id as disbursement_id,
        billing_events_raw.invoice_date as disbursement_date,
        billing_events_raw.bank_trace_id
      from
        billing_events_with_uni_temporal_data billing_events_raw
      where
        -- Only interested in disbursements, so filter out
        -- non-disbursements by selecting transaction type 
        -- to be DISBURSEMENT:
        billing_events_raw.transaction_type = 'DISBURSEMENT'
        -- Select a time period, you can adjust the dates 
        -- below if need be. For billing events use the 
        -- invoice date as the point in time of the 
        -- disbursement being initiated:
        and billing_events_raw.invoice_date >= 
            from_iso8601_timestamp('2020-10-01T00:00:00Z')
        and billing_events_raw.invoice_date < 
            from_iso8601_timestamp('2020-11-01T00:00:00Z')
    ),

    -- Get the invoices along with the line items that 
    -- are part of the above filtered disbursements
    disbursed_line_items as (
      select
        line_items.transaction_reference_id,
        line_items.product_id,
        line_items.transaction_type,
        (case
           -- Get the payer of the invoice from any 
           -- transaction type that is not AWS and 
           -- not BALANCE_ADJUSTMENT.
           -- For AWS and BALANCE_ADJUSTMENT, the billing 
           -- event feed will show the "AWS Marketplace" 
           -- account as the receiver of the funds and the 
           -- seller as the payer. Filter those out.
           when line_items.transaction_type 
               not like '%AWS%' and transaction_type 
               not like 'BALANCE_ADJUSTMENT' 
               then line_items.from_account_id
        end) as payer_account_id,
        line_items.end_user_account_id,
        invoice_amount,
        disbursements.disbursement_date,
        disbursements.disbursement_id,
        disbursements.bank_trace_id
      from
        billing_events_with_uni_temporal_data line_items
        -- Each disbursed line item is linked to the parent 
        -- disbursement via the disbursement_billing_event_id
        join disbursement_events disbursements 
          on disbursements.disbursement_id 
          = line_items.disbursement_billing_event_id
      where
        -- we are interested only in the invoice line 
        -- items that are DISBURSED
        line_items.action = 'DISBURSED'
    ),

  -- An invoice can contain multiple line items
  -- Create a pivot table to calculate the different 
  -- amounts that are part of an invoice.
  -- The new row is aggregated at 
  -- transaction_reference_id - end_user_account_id level
  invoice_amounts_aggregated as (
    select
      transaction_reference_id,
      product_id,
      -- a given disbursement id should have the 
      -- same disbursement_date
      max(disbursement_date) as disbursement_date,
      -- Build a pivot table in order to provide all the
      -- data related to a transaction in a single row.
      -- Note that the amounts are negated. This is because 
      -- when an invoice is generated, we give you the 
      -- positive amounts and the disbursement event 
      -- negates the amounts
      sum(case when transaction_type = 'SELLER_REV_SHARE' 
          then -invoice_amount else 0 end) as seller_rev_share,
      sum(case when transaction_type = 'AWS_REV_SHARE'  
          then -invoice_amount else 0 end) as aws_rev_share,
      sum(case when transaction_type = 'SELLER_REV_SHARE_REFUND'  
          then -invoice_amount else 0 end) as seller_rev_refund,
      sum(case when transaction_type = 'AWS_REV_SHARE_REFUND'  
          then -invoice_amount else 0 end) as aws_rev_refund,
      sum(case when transaction_type = 'SELLER_REV_SHARE_CREDIT'  
          then -invoice_amount else 0 end) as seller_rev_credit,
      sum(case when transaction_type = 'AWS_REV_SHARE_CREDIT'  
          then -invoice_amount else 0 end) as aws_rev_credit,
      sum(case when transaction_type = 'SELLER_TAX_SHARE'  
          then -invoice_amount else 0 end) as seller_tax_share,
      sum(case when transaction_type = 'SELLER_TAX_SHARE_REFUND'  
          then -invoice_amount else 0 end) as seller_tax_refund,
      -- This is the account that pays the invoice:
      max(payer_account_id) as payer_account_id,
      -- This is the account that subscribed to the product:
      end_user_account_id as customer_account_id,
      bank_trace_id
    from
      disbursed_line_items
    group by
      transaction_reference_id,
      product_id,
      disbursement_id,
      -- There might be a different end-user for the same 
      -- transaction reference id. Distributed licenses 
      -- is an example
      end_user_account_id,
      bank_trace_id
),

disbursed_amount_by_product as (
  select
    products.title as ProductTitle,
    products.product_code as ProductCode,
    -- We are rounding the sums using 2 decimal precision
    -- Note that the rounding method might differ 
    -- between SQL implementations.
    -- The disbursement seller report is using 
    -- RoundingMode.HALF_UP. This might create 
    -- discrepancies between this SQL output
    -- and the disbursement seller report
    round(invoice_amounts.seller_rev_share, 2) as SellerRev,
    round(invoice_amounts.aws_rev_share, 2) as AWSRefFee,
    round(invoice_amounts.seller_rev_refund, 2) as SellerRevRefund,
    round(invoice_amounts.aws_rev_refund, 2) as AWSRefFeeRefund,
    round(invoice_amounts.seller_rev_credit, 2) as SellerRevCredit,
    round(invoice_amounts.aws_rev_credit, 2) as AWSRefFeeCredit,
    (
        round(invoice_amounts.seller_rev_share, 2) +
        round(invoice_amounts.aws_rev_share, 2) +
        round(invoice_amounts.seller_rev_refund, 2) +
        round(invoice_amounts.aws_rev_refund, 2) +
        round(invoice_amounts.seller_rev_credit, 2) +
        round(invoice_amounts.aws_rev_credit, 2)
    ) as NetAmount,
    invoice_amounts.transaction_reference_id  
          as TransactionReferenceID,
    round(invoice_amounts.seller_tax_share, 2)  
          as SellerSalesTax,
    round(invoice_amounts.seller_tax_refund, 2)  
          as SellerSalesTaxRefund,
    payer_info.aws_account_id  
          as PayerAwsAccountId,
    customer_info.aws_account_id  
          as EndCustomerAwsAccountId,
    invoice_amounts.disbursement_date  
          as DisbursementDate,
    invoice_amounts.bank_trace_id  
          as BankTraceId
  from
    invoice_amounts_aggregated invoice_amounts
    join products_with_latest_version products  
      on products.product_id = invoice_amounts.product_id
    left join accounts_with_latest_version payer_info  
      on payer_info.account_id = invoice_amounts.payer_account_id
    left join accounts_with_latest_version customer_info  
      on customer_info.account_id = invoice_amounts.customer_account_id
)

select * from disbursed_amount_by_product;
```

## Rapport de rémunération des ventes
<a name="data-feed-example-sales-compensation"></a>

Pour connaître le chiffre d'affaires facturé par client, vous pouvez exécuter un ensemble de requêtes telles que les suivantes. Ces exemples de requêtes s'appuient les unes sur les autres pour créer la liste finale des informations sur les clients avec le montant total facturé à chaque client pour l'utilisation de votre logiciel. Vous pouvez utiliser les requêtes comme indiqué, ou les personnaliser en fonction de vos données et de vos cas d'utilisation.

 Les commentaires contenus dans les requêtes expliquent à quoi servent les requêtes et comment les modifier.

**Note**  
Lorsque nous exécutons cette requête, nous supposons que les données ingérées utilisent deux axes temporels (les `update` colonnes `valid_from` et). Pour de plus amples informations, veuillez consulter [Stockage et structure des flux de AWS Marketplace données](data-feed-details.md).

```
    -- Gets all the products and keeps the latest product_id, 
    -- valid_from tuple.
    with products_with_uni_temporal_data as (
      select
       *
      from
      (
        select
         *,
         ROW_NUMBER() OVER (PARTITION BY product_id, valid_from 
                  ORDER BY from_iso8601_timestamp(update_date) desc) 
                  as row_num
        from
         productfeed_v1
      )
      where
        -- A product_id can appear multiple times with the same 
        -- valid_from date but with a different update_date column,
        -- making it effectively bi-temporal. By only taking the most
        -- recent tuple, we are converting to a uni-temporal model.
        row_num = 1
    ),

    -- Gets the latest revision of a product
    -- A product can have multiple revisions where some of the 
    -- columns, like the title, can change.
    -- For the purpose of the sales compensation report, we want 
    -- to get the latest revision of a product
    products_with_latest_revision as (
     select
      *
     from
     (
      select
       *,
       ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY from_iso8601_timestamp(valid_from) desc) as row_num_latest_revision
      from
       products_with_uni_temporal_data
     )
     where
      row_num_latest_revision = 1
   ),

     -- Gets all the addresses and keeps the latest address_id, 
     -- aws_account_id, and valid_from combination.
     -- We're transitioning from a bi-temporal data model to an 
     -- uni-temporal data_model
     piifeed_with_uni_temporal_data as (
       select
        *
       from
       (
         select
          *,
          ROW_NUMBER() OVER (
             PARTITION BY address_id, aws_account_id, valid_from 
             ORDER BY from_iso8601_timestamp(update_date) desc) 
             as row_num
         from
          piifeed
       )
       where
         -- An address_id can appear multiple times with the same
         -- valid_from date but with a different update_date column.
         -- We are only interested in the most recent.
         row_num = 1
     ),

    -- Gets the latest revision of an address.
    -- An address_id can have multiple revisions where some of 
    -- the columns can change.
    -- For the purpose of the sales compensation report, we want to
    -- get the latest revision of an address + account_id pair.
    pii_with_latest_revision as (
      select
       *
      from
      (
       select
        *,
        ROW_NUMBER() OVER (PARTITION BY address_id, aws_account_id 
              ORDER BY from_iso8601_timestamp(valid_from) desc) 
              as row_num_latest_revision
       from
        piifeed_with_uni_temporal_data
      )
      where
       row_num_latest_revision = 1
    ),

    -- Gets all the accounts and keeps the latest 
    -- account_id, valid_from tuple.
    -- We're transitioning from a bi-temporal data 
    -- model to an uni-temporal data_model.
    accounts_with_uni_temporal_data as (
      select
       *
      from
      (
        select
         *,
         ROW_NUMBER() OVER (PARTITION BY account_id, valid_from 
             ORDER BY from_iso8601_timestamp(update_date) desc) 
             as row_num
        from
         accountfeed_v1
      )
      where
        -- An account_id can appear multiple times with the same 
        -- valid_from date but with a different update_date column.
        -- We are only interested in the most recent tuple.
        row_num = 1
    ),

    -- Gets all the historical dates for an account
    -- An account can have multiple revisions where some of the 
    -- columns like the mailing_address_id can change.
    accounts_with_history as (
     select
      *,
      -- This interval's begin_date
      case
        when
        -- First record for a given account_id
          lag(valid_from, 1) over (partition by account_id 
             order by from_iso8601_timestamp(valid_from) asc) is null
        then
          -- 'force' begin_date a bit earlier because of different 
          -- data propagation times. We'll subtract one day as one
          -- hour is not sufficient
          from_iso8601_timestamp(valid_from) - INTERVAL '1' DAY
        else
          -- not the first line -> return the real date
          from_iso8601_timestamp(valid_from)
      end as begin_date,
      -- This interval's end date.
      COALESCE(
           LEAD(from_iso8601_timestamp(valid_from), 1) 
                OVER (partition by account_id 
                ORDER BY from_iso8601_timestamp(valid_from)),
           from_iso8601_timestamp('9999-01-01T00:00:00Z')
      ) as end_date
     from
       accounts_with_uni_temporal_data
   ),

    -- Gets all the billing events and keeps the latest 
    -- billing_event_id, valid_from tuple.
    -- We're transitioning from a bi-temporal data 
    -- model to an uni-temporal data_model.
    billing_events_with_uni_temporal_data as (
      select
       *
      from (
        select
          billing_event_id,
          from_iso8601_timestamp(valid_from) as valid_from,
          from_iso8601_timestamp(update_date) as update_date,
          from_iso8601_timestamp(invoice_date) as invoice_date,
          transaction_type,
          transaction_reference_id,
          product_id,
          disbursement_billing_event_id,
          action,
          currency,
          from_account_id,
          to_account_id,
          end_user_account_id,
          -- convert an empty billing address to null. This will 
          -- later be used in a COALESCE call
          case
           when billing_address_id <> '' then billing_address_id else null
          end as billing_address_id,
          CAST(amount as decimal(20, 10)) invoice_amount,
          ROW_NUMBER() OVER (PARTITION BY billing_event_id, valid_from 
              ORDER BY from_iso8601_timestamp(update_date) desc) 
              as row_num
        from
          billingeventfeed_v1
        where
          -- The Sales Compensation Report does not contain BALANCE 
          -- ADJUSTMENTS, so we filter them out here
          transaction_type <> 'BALANCE_ADJUSTMENT'
          -- Keep only the transactions that will affect any 
          -- future disbursed amounts.
          and balance_impacting = '1'
        )
      where row_num = 1
    ),

    -- Gets the billing address for all DISBURSED invoices. This 
    -- will be the address of the payer when the invoice was paid.
    -- NOTE: For legal reasons, for CPPO transactions, the 
    -- manufacturer will not see the payer's billing address id
    billing_addresses_for_disbursed_invoices as (
      select
        billing_events_raw.transaction_reference_id,
        billing_events_raw.billing_address_id,
        billing_events_raw.from_account_id
      from
        billing_events_with_uni_temporal_data billing_events_raw
      where
        -- the disbursed items will contain the billing address id
        billing_events_raw.action = 'DISBURSED'
        -- we only want to get the billing address id for the 
        -- transaction line items where the seller is the receiver
        -- of the amount
        and billing_events_raw.transaction_type like 'SELLER_%'
      group by
        billing_events_raw.transaction_reference_id,
        billing_events_raw.billing_address_id,
        billing_events_raw.from_account_id
    ),

  -- An invoice can contain multiple line items.
  -- We create a pivot table to calculate the different amounts 
  -- that are part of an invoice.
  -- The new row is aggregated at 
  -- transaction_reference_id - end_user_account_id level
  invoiced_and_forgiven_transactions as (
    select
      transaction_reference_id,
      product_id,
      -- A transaction will have the same invoice date for all 
      -- of its line items (transaction types)
      max(invoice_date) as invoice_date,
      -- A transaction will have the same billing_address_id 
      -- for all of its line items. Remember that the billing event
      -- is uni temporal and we retrieved only the latest valid_from item
      max(billing_address_id) as billing_address_id,
      --  A transaction will have the same currency for all 
      -- of its line items
      max(currency) as currency,
      -- We're building a pivot table in order to provide all the 
      -- data related to a transaction in a single row
      sum(case when transaction_type = 'SELLER_REV_SHARE' 
            then invoice_amount else 0 end) as seller_rev_share,
      sum(case when transaction_type = 'AWS_REV_SHARE' 
            then invoice_amount else 0 end) as aws_rev_share,
      sum(case when transaction_type = 'SELLER_REV_SHARE_REFUND' 
            then invoice_amount else 0 end) as seller_rev_refund,
      sum(case when transaction_type = 'AWS_REV_SHARE_REFUND' 
            then invoice_amount else 0 end) as aws_rev_refund,
      sum(case when transaction_type = 'SELLER_REV_SHARE_CREDIT' 
            then invoice_amount else 0 end) as seller_rev_credit,
      sum(case when transaction_type = 'AWS_REV_SHARE_CREDIT' 
            then invoice_amount else 0 end) as aws_rev_credit,
      sum(case when transaction_type = 'SELLER_TAX_SHARE' 
            then invoice_amount else 0 end) as seller_tax_share,
      sum(case when transaction_type = 'SELLER_TAX_SHARE_REFUND' 
            then invoice_amount else 0 end) as seller_tax_refund,
      -- this is the account that pays the invoice.
      max(case
        -- Get the payer of the invoice from any transaction type 
        -- that is not AWS and not BALANCE_ADJUSTMENT.
        -- For AWS and BALANCE_ADJUSTMENT, the billing event feed 
        -- will show the "AWS Marketplace" account as the
        -- receiver of the funds and the seller as the payer. We 
        -- are not interested in this information here.
        when
         transaction_type not like '%AWS%' 
           and transaction_type not like 'BALANCE_ADJUSTMENT' 
         then from_account_id
       end) as payer_account_id,
      -- this is the account that subscribed to your product
      end_user_account_id as customer_account_id
    from
      billing_events_with_uni_temporal_data
    where
      -- Get invoiced or forgiven items. Disbursements are 
      -- not part of the sales compensation report
      action in ('INVOICED', 'FORGIVEN')
    group by
      transaction_reference_id,
      product_id,
      -- There might be a different end-user for the same 
      -- transaction reference id. Distributed licenses 
      -- is an example.
      end_user_account_id
),

invoiced_items_with_product_and_billing_address as (
  select
    invoice_amounts.*,
    products.product_code,
    products.title,
    payer_info.aws_account_id as payer_aws_account_id,
    payer_info.account_id as payer_reference_id,
    customer_info.aws_account_id as end_user_aws_account_id,
    (
        invoice_amounts.seller_rev_share +
        invoice_amounts.aws_rev_share +
        invoice_amounts.seller_rev_refund +
        invoice_amounts.aws_rev_refund +
        invoice_amounts.seller_rev_credit +
        invoice_amounts.aws_rev_credit +
        invoice_amounts.seller_tax_share +
        invoice_amounts.seller_tax_refund
    ) as seller_net_revenue,
    -- Try to get the billing address from the DISBURSED event 
    -- (if any). If there is no DISBURSEMENT, get the billing 
    -- address from the INVOICED item. If still no billing address, 
    -- then default to getting the mailing address of the payer.
    coalesce(billing_add.billing_address_id, 
             invoice_amounts.billing_address_id, 
             payer_info.mailing_address_id) 
          as final_billing_address_id
  from
    invoiced_and_forgiven_transactions invoice_amounts
    join products_with_latest_revision products 
        on products.product_id = invoice_amounts.product_id
    left join accounts_with_history payer_info 
        on payer_info.account_id = invoice_amounts.payer_account_id
          -- Get the Payer Information at the time of invoice creation
          and payer_info.begin_date <= invoice_amounts.invoice_date 
          and invoice_amounts.invoice_date < payer_info.end_date
    left join accounts_with_history customer_info 
        on customer_info.account_id = invoice_amounts.customer_account_id
          -- Get the End User Information at the time of invoice creation
          and customer_info.begin_date <= invoice_amounts.invoice_date 
          and invoice_amounts.invoice_date < customer_info.end_date
    left join billing_addresses_for_disbursed_invoices billing_add 
        on billing_add.transaction_reference_id = 
           invoice_amounts.transaction_reference_id
        and billing_add.from_account_id = 
            invoice_amounts.payer_account_id
),

invoices_with_full_address as (
  select
    payer_aws_account_id as "Customer AWS Account Number",
    pii_data.country as "Country",
    pii_data.state_or_region as "State",
    pii_data.city as "City",
    pii_data.postal_code as "Zip Code",
    pii_data.email_domain as "Email Domain",
    product_code as "Product Code",
    title as "Product Title",
    seller_rev_share as "Gross Revenue",
    aws_rev_share as "AWS Revenue Share",
    seller_rev_refund as "Gross Refunds",
    aws_rev_refund as "AWS Refunds Share",
    seller_net_revenue as "Net Revenue",
    currency as "Currency",
    date_format(invoice_date, '%Y-%m')as "AR Period",
    transaction_reference_id as "Transaction Reference ID",
    payer_reference_id as "Payer Reference ID",
    end_user_aws_account_id as "End Customer AWS Account ID"
  from
    invoiced_items_with_product_and_billing_address invoice_amounts
    left join pii_with_latest_revision pii_data 
        on pii_data.aws_account_id = invoice_amounts.payer_aws_account_id
        and pii_data.address_id = invoice_amounts.final_billing_address_id
    -- Filter out FORGIVEN and Field Demonstration Pricing transactions
    where seller_net_revenue <> 0
)

select * from invoices_with_full_address;
```

# Flux de données
<a name="data-feeds"></a>

AWS Marketplace fournit un certain nombre de flux de données pour aider les vendeurs à collecter et à analyser les informations relatives aux ventes de vos produits. Les flux de données sont disponibles pour tous les AWS Marketplace vendeurs enregistrés. Étant donné que les flux de données sont générés en un jour, ils contiennent les données les plus récentes disponibles.

Les sections suivantes décrivent chaque flux de données.

**Topics**
+ [Flux de données de compte](data-feed-account.md)
+ [Flux de données d'adresse](data-feed-address.md)
+ [Flux de données sur les accords](data-feed-agreements.md)
+ [Flux de données sur les frais d'accord](data-feed-agreement-charge.md)
+ [Fil de données sur la durée de l'accord](data-feed-agreement-term.md)
+ [Flux de données d'événement de facturation](data-feed-billing-event.md)
+ [Flux de données d'utilisation quotidien](data-feed-daily-usage.md)
+ [Flux de données de mappage hérité](data-feed-legacy-mapping.md)
+ [Offre de flux de données](data-feed-offer.md)
+ [Proposer un flux de données produit](data-feed-offer-product.md)
+ [Offrir un flux de données cible](data-feed-offer-target.md)
+ [Flux de données produit](data-feed-product.md)
+ [Flux de données d'élément fiscal](data-feed-tax-item.md)

# Flux de données de compte
<a name="data-feed-account"></a>

Ce flux de données fournit des informations sur tous les comptes avec lesquels vous interagissez : les vôtres, les partenaires de distribution avec lesquels vous travaillez, les acheteurs, les payeurs et tous les comptes taxés. 

Les données du compte sont immuables et ne sont pas associées à un numéro de version. Les modifications apportées aux champs sont ajoutées, de sorte que ce flux de données peut avoir plusieurs lignes avec le même `account_id` et des valeurs `valid_from` différentes. Pour de plus amples informations sur les champs de l'historique des données, veuillez consulter [Historisation des données](data-feed-details.md#data-feed-historization).

Le flux de données du compte est actualisé toutes les 24 heures, de sorte que de nouvelles données sont disponibles quotidiennement.

Le tableau suivant explique les noms et les descriptions des colonnes du flux de données. 


| Nom de la colonne  | Description  | 
| --- | --- | 
| account\$1id  | L'identifiant unique global (GUID) du compte. Peut être utilisé pour joindre des champs dans les flux de `Tax_Item` données `Product` `Offer_Target``Billing_Event`,, et. Consultez ces flux de données pour obtenir des informations sur les champs qui peuvent être utilisés pour les jointures. | 
| aws\$1account\$1id  | Le numéro de AWS compte du AWS compte du vendeur, qui est unique par AWS partition.  | 
| encrypted\$1account\$1id | Identifiant unique et chiffré pour un acheteur individuel de votre application. La valeur pour encrypted\$1account\$1id est utilisée par AWS Marketplace le service de mesure, par exemple, car elle CustomerIdentifier est renvoyée par l'[https://docs.aws.amazon.com/marketplacemetering/latest/APIReference/API_ResolveCustomer.html](https://docs.aws.amazon.com/marketplacemetering/latest/APIReference/API_ResolveCustomer.html)action.  | 
| mailing\$1address\$1id | Référence de l'adresse postale de ce compte. | 
| tax\$1address\$1id | Référence de l'adresse fiscale de ce compte. | 
| tax\$1registration\$1number | Pour les comptes non-américains, le numéro d'enregistrement fiscal de ce compte.  | 
| tax\$1legal\$1name | Pour les comptes non-américains, le nom légal de la société. Il s'agit du nom utilisé sur les factures fiscales. | 

## Exemple de flux de données de compte
<a name="data-feed-account-sample-data"></a>

Voici un exemple de flux de données de compte. Pour plus de lisibilité, les colonnes de l'historique des données ne sont pas affichées. Pour de plus amples informations sur les champs de l'historique des données, veuillez consulter [Historisation des données](data-feed-details.md#data-feed-historization).


| account\$1id  | aws\$1account\$1id  | encrypted\$1account\$1id | mailing\$1address\$1id | tax\$1address\$1id | tax\$1registration\$1number | tax\$1legal\$1name | 
| --- | --- | --- | --- | --- | --- | --- | 
| xk0 CSmiAm6PQ4QqEog9iiaochIzuPlkMfba7a1oDlZ | 444456660000 | Zf 7 oMzhe GWpH | 25O3K46EN6 FiiQTXWx8E3 3 eViOf kaOiPal UiofjyFa |  |  |  | 
| 7 nyo5 jwTRo PLYx81Vx9JI04 O1Ff8 88W8 eEwTur biQi | 555567679999 | 373 vuQUqm Q8 V | 5OJ6 MRRf2GVH2VJ9HFQIM800M vTjSz uLEHmy FY5Lr42s8 | 5OJ6 MRRf2GVH2VJ9HFQIM800M vTjSz uLEHmy FY5Lr42s8 | SE823935083345 |  | 
| VIeGa2t9j3MuxioH9wc8lsndXXCgGCGUreeXriocM5 | 73739998888 | 8 SPxAYmi8MwX | NLUc5UeiMlGFTrDWCoftDPhDUF1oaSd8xgl5QM8Db7 | V5N Yogwy0 hBYBi WMhndGU4AfMggmuoTC2j7Pm8ZKKNNyT | DE469558025 |  | 

# Flux de données d'adresse
<a name="data-feed-address"></a>

**Important**  
AWS Marketplace supprimera le PiiFeed, qui est livré à l'aide du [service de flux de données de livraison des vendeurs](https://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-service.html), en décembre 2023. Utilisez le flux de données AddressFeed \$1V1 pour vos besoins en matière de données d'adresse. Si vous avez des questions ou si vous avez besoin d'assistance, contactez l'équipe [des opérations AWS Marketplace vendeurs](https://aws.amazon.com/marketplace/management/contact-us/).

Ce flux de données fournit les coordonnées de tous les comptes avec lesquels vous interagissez : les vôtres, les partenaires de distribution avec lesquels vous travaillez, les acheteurs, les payeurs et tous les comptes taxés. Chaque fois qu'une nouvelle transaction a lieu, l'adresse du client pour la transaction est analysée et si elle n'est pas dans votre flux de données, une nouvelle entrée est ajoutée à votre fichier de flux de données.

Les données d'adresse sont immuables. 

Le flux de données d'adresse est actualisé toutes les 24 heures, de sorte que de nouvelles données sont disponibles quotidiennement.

Le tableau suivant explique les noms et les descriptions des colonnes du flux de données. 


| Nom de la colonne  | Description  | 
| --- | --- | 
| address\$1id  | Clé unique de l'adresse. Peut être utilisé pour effectuer une connexion à partir du flux de `Billing_Event` données sur le `billing_address_id` terrain ou à partir du flux de `Account` données sur les `tax_address_id` champs `mailing_address_id` ou. | 
| email\$1domain  | Domaine de l'adresse e-mail enregistré pour ce compte.  | 
| company\$1name  | Nom de l'entreprise enregistré pour ce compte.  | 
| code\$1pays | Code de pays ISO 3166 alpha-2 enregistré pour cette adresse.  | 
| state\$1or\$1region  | État ou région enregistré pour cette adresse.  | 
| city  | Ville enregistrée pour cette adresse.  | 
| postal\$1code  | Code postal enregistré pour cette adresse.  | 
| address\$1line\$11  | Première ligne de l'adresse enregistrée pour cette adresse.  | 
| address\$1line\$12  | Deuxième ligne de l'adresse enregistrée pour cette adresse.  | 
| address\$1line\$13  | Troisième ligne de l'adresse enregistrée pour cette adresse.  | 

## Exemple de flux de données d'adresse
<a name="data-feed-address-sample-data"></a>

Voici un exemple de flux de données d'adresse. Dans le flux de données, ces informations sont présentées dans un seul tableau. Pour plus de lisibilité, les données sont affichées dans deux tableaux ici, et les colonnes de l'historique des données ne sont pas affichées. Pour de plus amples informations sur les champs de l'historique des données, veuillez consulter [Historisation des données](data-feed-details.md#data-feed-historization). 


| address\$1id  | email\$1domain  | company\$1name  | code\$1pays  | state\$1or\$1region  | city  | postal\$1code  | 
| --- | --- | --- | --- | --- | --- | --- | 
| V5NhPar 0WMhnDgu4 Tc2j7PM8ZKknNYT BiYogwy AfMggmuo | a.com | Société de Mateo Jackson | DE |  | Hambourg | 67568 | 
| G68xDBKZQDVVHZFBGW6YF5YOS0A6NiSVWhMH5 ViLjf | b.com | Société de Mary Major | ETATS-UNIS | OH | Dayton | 57684 | 
| nLUC5 GFTRDWCOFTDPHDUF1OASD8XGL5QM8DB7 UeiMl | c.com | Notre vendeur | ETATS-UNIS | NY | New York | 89475 | 




| address\$1line\$11  | address\$1line\$12  | address\$1line\$13  | 
| --- | --- | --- | 
|   |   |  | 
|  |   |  | 
|  | 19e étage |  | 



# Flux de données sur les accords
<a name="data-feed-agreements"></a>

Ce flux de données fournit des informations sur les accords, c'est-à-dire un contrat signé entre un proposant (vendeur officiel) et un accepteur (AWS acheteur) pour commencer à utiliser un produit. Ce flux de données fournit des informations sur les accords relatifs aux offres de produits que vous avez créées en tant que vendeur officiel.

Le flux de données de l'accord est actualisé toutes les 24 heures.

Le tableau suivant répertorie et décrit les éléments du flux de données.


| Colonne | Description | 
| --- | --- | 
| valid\$1from | Première date pour laquelle la valeur de la clé primaire est valide par rapport aux valeurs des autres champs. | 
| insert\$1date | Date à laquelle un enregistrement a été inséré dans le flux de données. | 
| update\$1date | Date de la dernière mise à jour de l'enregistrement. | 
| delete\$1date | Cette colonne est toujours vide. | 
| Statut |  État actuel de l'accord. Les statuts pris en charge incluent : [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-agreements.html)  | 
| valeur\$1de\$1accord\$1estimée | Le montant total connu que le client est susceptible de payer tout au long du cycle de vie du contrat. | 
| code\$1devise | Devise du montant total connu dans lequel le client est susceptible de payer tout au long du cycle de vie du contrat. | 
| identifiant\$1accord | L'identifiant unique de l'accord. | 
| identifiant\$1licences | Les identifiants de licence associés à l'accord, représentés sous forme de tableau. | 
| identifiant\$1compte\$1proposant | Le vendeur qui l'a proposé PurchaseAgreement, représenté par l'identifiant unique mondial (GUID) du compte du vendeur. Peut être utilisé pour rejoindre le flux de données du compte. | 
| identifiant\$1compte\$1accepteur | L'acheteur qui l'a accepté PurchaseAgreement, représenté par l'identifiant unique mondial (GUID) du compte de l'acheteur. Peut être utilisé pour rejoindre le flux de données du compte. | 
| révision de l'offre lors de son acceptation | L'identifiant convivial de l'offre correspondant à cet accord. Peut être utilisé pour rejoindre l'offre et les flux de données cibles de l'offre. | 
| offer\$1set\$1id | Identifiant de l'ensemble d'offres associé à l'offre. | 
| start\$1time | Date et heure de début de l'accord. | 
| end\$1time | Date et heure de fin de l'accord. Le champ est nul pour les pay-as-you-go accords qui n'ont pas de date de fin. | 
| temps\$1d'acceptation | Date et heure de l'acceptation de l'offre ou de la création de l'accord. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-agreements.html)  | 
| intent | L'intention de l'acheteur lors de la dernière modification du contrat. | 
| identifiant\$1accord précédent | L'identifiant de l'accord précédent. | 
| status\$1reason\$1code | La raison du changement de statut de l'accord. | 
| identifiant\$1compte\$1destinataire | Le compte du vendeur qui reçoit les données dans les flux. Peut être utilisé pour rejoindre le flux de `Account` données sur le `account_id` terrain. | 

## Exemple de flux de données sur les accords
<a name="agreements-feed-example"></a>


| valid\$1from | identifiant\$1accord | identifiant\$1compte\$1proposant | identifiant\$1compte\$1accepteur | offer\$1id | révision de l'offre lors de son acceptation | offer\$1set\$1id | start\$1time | end\$1time | acceptancet\$1time | intent | identifiant\$1accord précédent | status | status\$1reason\$1code | valeur\$1de\$1accord\$1estimée | code\$1devise | 
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | 
| 2024-06-21 20:58:00.0 | agmt-34g544dfgsd5678adsrgwe5t | 88a3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 88a3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | offre-krxxxxxxxxxxx | 1 | coffret d'offres - yricpu74oqox2 | 2024-06-21 20:58:00.0 | 2025-06-21 20:58:00.0 | 2024-06-21 20:58:00.0 | NOUVEAU |  | ACTIF |  | 1 000 | USD | 

# Flux de données sur les frais d'accord
<a name="data-feed-agreement-charge"></a>

Ce flux de données fournit un aperçu des frais associés à un contrat que vous avez créé en tant que vendeur officiel. Ce flux de données fournit actuellement uniquement des informations de facturation pour les accords associés à une durée de calendrier de paiement.

Le flux de données Agreement Charge est actualisé toutes les 24 heures.

Le tableau suivant répertorie et décrit les éléments du flux de données.


| Colonne | Description | 
| --- | --- | 
| valid\$1from | Première date pour laquelle la valeur de la clé primaire est valide par rapport aux valeurs des autres champs. | 
| insert\$1date | Date à laquelle un enregistrement a été inséré dans le flux de données. | 
| update\$1date | Date de la dernière mise à jour de l'enregistrement. | 
| delete\$1date | Cette colonne est toujours vide. | 
| montant | Le montant à facturer. | 
| code\$1devise | Devise de tarification du paiement. | 
| time | Date de facturation prévue pour le versement prévu. | 
| identifiant\$1accord | L'identifiant unique de l'accord. | 
| term\$1id | Identifiant unique du terme. | 

## Exemple de flux de données sur les frais d'accord
<a name="agreement-charge-feed-example"></a>


| valid\$1from | insert\$1date | update\$1date | delete\$1date | montant | code\$1devise | time | identifiant\$1accord | term\$1id | 
| --- | --- | --- | --- | --- | --- | --- | --- | --- | 
| 24/12-2024 11:31:47 ,0 | 2025-10-01 01:03:41,0 | 2025-10-01 01:03:41,0 | null | 28440,00 | USD | 06/11/2025 00:00:00.000 | AGMT-3KK39TBW3J6ID2VAKBP0xXXXXX | TERM-3986E2C7F73768ED4FF7CD8A97B41AC0AE2AA02ADA6B68DEB9349C8604CXXXXX | 

# Fil de données sur la durée de l'accord
<a name="data-feed-agreement-term"></a>

Ce flux de données fournit un aperçu de la durée du contrat associée à un accord que vous avez créé en tant que vendeur officiel.

Le flux de données sur la durée du contrat est actualisé toutes les 24 heures.

Le tableau suivant répertorie et décrit les éléments du flux de données.


| Colonne | Description | 
| --- | --- | 
| valid\$1from | Première date pour laquelle la valeur de la clé primaire est valide par rapport aux valeurs des autres champs. | 
| insert\$1date | Date à laquelle un enregistrement a été inséré dans le flux de données. | 
| update\$1date | Date de la dernière mise à jour de l'enregistrement. | 
| delete\$1date | Date à laquelle l'enregistrement a été supprimé. | 
| identifiant\$1accord | L'identifiant unique de l'accord. | 
| term\$1id | Identifiant unique du terme. | 
| type\$1terme | Type de terme associé à l'accord. Reportez-vous à https://docs.aws.amazon.com/marketplace/ latest/APIReference/API \$1Types\$1 AWS\$1M arketplace\$1Agreement\$1Service.html pour une liste complète des termes disponibles. | 
| term\$1configuration | Les paramètres supplémentaires spécifiés par l'accepteur lors de l'acceptation du terme. Cela s'applique à ConfigurableUpfrontPricingTerms et RenewalTerms. | 

## Exemple de flux de données sur les termes d'un accord
<a name="agreement-term-feed-example"></a>


| valid\$1from | insert\$1date | update\$1date | delete\$1date | identifiant\$1accord | term\$1id | type\$1terme | term\$1configuration | 
| --- | --- | --- | --- | --- | --- | --- | --- | 
| 24/12-2024 11:31:47 ,0 | 2025-10-01 01:03:41,0 | 2025-10-01 01:03:41,0 | null | AGMT-3KK39TBW3J6ID2VAKBP0xXXXXX | TERM-3986E2C7F73768ED4FF7CD8A97B41AC0AE2AA02ADA6B68DEB9349C8604CXXXXX | ConfigurableUpfrontPricingTerm | \$1« Valeur de sélection » « P36M », « dimensions » : [\$1"DimensionKey » « Applications », « DimensionValue » :1\$1]\$1 | 

# Flux de données d'événement de facturation
<a name="data-feed-billing-event"></a>

Ce flux de données fournit des informations sur les événements de facturation, y compris la facturation et les débours. 

Par exemple, vous pouvez utiliser ce flux de données pour savoir quand et pour quoi un acheteur est facturé. Vous pouvez également utiliser l'[exemple de requêtes SQL](#data-feeds-billing-event-query-examples) pour analyser les données de ce flux de données.

Ce flux de données contient des informations associées aux événements de facturation pour lesquels vous êtes le vendeur inscrit. Pour les accords conclus par l'intermédiaire de partenaires de distribution, ce flux de données contient des informations sur les événements de facturation enregistrés entre le fabricant et le vendeur.

Le flux de données d'événement de facturation est actualisé toutes les 24 heures, de sorte que de nouvelles données sont disponibles tous les jours.

Les données d'événement de facturation sont immuables. 

Le tableau suivant explique les noms et les descriptions des colonnes du flux de données. 


| Nom de la colonne  | Description  | 
| --- | --- | 
| billing\$1event\$1id | Identificateur d'un événement de facturation. Cet ID est unique dans l'environnement du vendeur.  | 
| from\$1account\$1id |  Compte qui a initié l'événement de facturation. Si `transaction_type` est `SELLER_REV_SHARE`, il s'agit du compte payeur de l'acheteur. Il s'agit d'une clé étrangère du flux de données du [compte](data-feed-account.md). Peut être utilisé pour rejoindre le flux de `Account` données sur le `account_id` terrain.  | 
| to\$1account\$1id | Compte qui reçoit le montant du mouvement pour le produit. Il s'agit d'une clé étrangère du flux de données du compte.Peut être utilisé pour rejoindre le flux de `Account` données sur le `account_id` terrain. | 
| end\$1user\$1account\$1id | Compte qui utilise le produit. Ce compte peut être différent des comptes acheteur et payeur.Peut être utilisé pour rejoindre le flux de `Account` données sur le `account_id` terrain. | 
| product\$1id | Identificateur de chemin du produit. Il s'agit d'une clé étrangère du flux de données du [produit](data-feed-product.md).Peut être utilisé pour rejoindre le flux de `Product` données sur le `product_id` terrain. | 
| action |  Type d'action pour cet événement. Les valeurs possibles sont les suivantes : [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-billing-event.html)  | 
| transaction\$1type |  Type de transaction. Pour obtenir des exemples, consultez [Scénarios de taxation](#data-feeds-billing-event-tax-examples). Les valeurs possibles sont les suivantes :  [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-billing-event.html)  | 
| parent\$1billing\$1event\$1id |  Lorsque la valeur de `broker_id` is`AWS_INC`, la valeur de `action` is `DISBURSED` ou`FORGIVEN`, et la valeur de `transaction_type` is`DISBURSEMENT`, `parent_billing_event_id` font référence à l'original à l'origine de `billing_event_id` cet événement de facturation. Si `action` a une autre valeur, ce champ est null.  Lorsque la valeur de `broker_id` est`AWS_EUROPE`, elle `parent_billing_event_id` fait référence à l'original `billing_event_id` à l'origine de cet événement de facturation dans les scénarios suivants :  [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-billing-event.html) Lorsque la valeur de `broker_id` est`AWS_EUROPE`, elle `parent_billing_event_id` fait référence à l'original `billing_event_id` du précédent événement de facturation des versements réussi dans le scénario suivant : [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-billing-event.html) Lorsque la valeur de `broker_id` est est`AWS_EUROPE`, ce champ est nul pour tous les scénarios restants.  | 
| disbursement\$1billing\$1event\$1id |  Le décaissement correspondant lorsque la valeur de `action` est `DISBURSED` et que l'une des conditions suivantes est vraie :  [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-billing-event.html) Dans tous les autres scénarios, cette valeur est nulle.  | 
| montant | Montant de l'événement de facturation.  | 
| currency | Le code de devise ISO 639. | 
| montant\$1décaissement | Le montant du déboursement. Pour les scénarios de double facturation, ce champ contient le montant du versement. Pour les scénarios de facturation unifiée, ce champ est nul. | 
| monnaie\$1de versement | Le code de devise ISO 639 pour le montant du versement. Pour les scénarios de double facturation, ce champ contient le code de devise du versement. Pour les scénarios de facturation unifiée, ce champ est nul. | 
| balance\$1impacting | Si le montant est pris en compte dans le calcul des débours du vendeur. Une valeur de 0 indique que le montant est mentionné à titre informatif et n'a aucun effet sur le solde. Une valeur de 1 indique que ce montant est pris en compte pour déterminer les débours du vendeur.  | 
| invoice\$1date | Date de création de la facture. | 
| payment\$1due\$1date |  Lorsque la valeur de `action` est`INVOICED`, la date d'échéance de la facture.  | 
| usage\$1period\$1start\$1date | Date de début de la période de l'enregistrement. | 
| usage\$1period\$1end\$1date | Date de fin de la période de l'enregistrement. | 
| invoice\$1id |  Le numéro AWS de facture.  | 
| billing\$1address\$1id | Référence de l'adresse de facturation du payeur dans le flux de données d'adresse.Peut être utilisé pour rejoindre le flux de `Address` données sur le `address_id` terrain. | 
| transaction\$1reference\$1id |  Identificateur qui vous permet de recouper les données des rapports suivants : [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-billing-event.html)  | 
| bank\$1trace\$1id |  Pour les transactions de versement (`transaction_type`=is `DISBURSEMENT` et action est`DISBURSED`), le numéro de suivi attribué par la banque. L'identifiant de trace peut être utilisé pour établir une corrélation avec les rapports fournis par la banque du vendeur.  | 
| identifiant\$1courtier |  Identifiant de l'entité commerciale qui a facilité la transaction. Les valeurs possibles sont les suivantes : [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-billing-event.html)  | 
| identifiant\$1référence\$1de\$1transaction de l'acheteur |  Identifiant qui regroupe tous les enregistrements associés du flux de facturation à l'aide `GROUP BY` de la structure des fonctions de fenêtre en SQL. Ces enregistrements connexes peuvent contenir la facture de l'acheteur, la facture du vendeur et les taxes sur la valeur ajoutée (VATs) sur les frais de mise en vente.  | 
| date\$1action |  Date à laquelle l'événement de transaction s'est produit. La date précise dépend du type d'action : [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-billing-event.html)  | 
| identifiant\$1compte\$1destinataire | Le compte du vendeur qui reçoit les données dans les flux. Peut être utilisé pour rejoindre le flux de `Account` données sur le `account_id` terrain. | 
| offer\$1id | L'identifiant convivial de l'offre associé à la ligne de facture de l'acheteur.Peut être utilisé pour rejoindre le flux de `Offer` données sur le `offer_id` terrain. | 
| date\$1de\$1facture de l'acheteur | Date de facturation de la ligne de facture de l'acheteur associée. | 
| line\$1item\$1id | L'identifiant de la ligne de facture, au moment de la granularité du versement. | 
| buyer\$1line\$1item\$1id | Numéro de ligne de facture de l'acheteur au moment de la granularité du versement. | 
| variante de charge | Indique ce qui est acheté ou vendu.  | 
| charge\$1side | Indique si le vendeur achète ou vend la variante de facturation.  | 

## Scénarios de taxation
<a name="data-feeds-billing-event-tax-examples"></a>

Le modèle de taxation qui est en place pour le pays et l'état de l'acheteur et du vendeur dicte la façon dont les taxes sont perçues et remises. Voici les scénarios possibles :
+ Les taxes sont collectées et versées par AWS. Dans ces cas, `transaction_type` est `AWS_TAX_SHARE`. 
+ Les taxes sont collectées par le vendeur AWS, versées au vendeur et remises par le vendeur aux autorités fiscales. Dans ces cas, `transaction_type` est `SELLER_TAX_SHARE`.
+ Les taxes ne sont pas collectées par AWS. Le vendeur doit calculer les taxes et les remettre aux autorités fiscales. Dans ces cas, AWS Marketplace n'effectue pas de calculs fiscaux et ne reçoit pas d'informations fiscales. Le vendeur paie les impôts sur la part des revenus.

## Exemples de flux de données d'événements de facturation
<a name="data-feed-billing-event-sample-scenario"></a>

Cette section présente des exemples de la période de données de l'événement de facturation au moment de la facturation et un mois plus tard. Notez ce qui suit pour tous les tableaux de cette section : 
+ Dans les flux de données, les valeurs `billing_event_id` sont des chaînes alphanumériques de 40 caractères. Elles sont affichées ici sous forme de chaînes à deux caractères pour la lisibilité. 
+ Dans le flux de données, ces informations sont présentées dans un seul tableau. Pour plus de lisibilité, les données sont présentées ici dans plusieurs tableaux et toutes les colonnes ne sont pas affichées. 

Pour les exemples présentés dans cette section, supposons ce qui suit : 
+ Arnav est l'acheteur.
  + Son ID de compte est `737399998888`.
  + Il est situé en France, où il est assujetti aux lois sur les facilitateurs du marché. Pour de plus amples informations, veuillez consulter l'[aide fiscale d'Amazon Web Service](https://aws.amazon.com/tax-help/).
  + Il a acheté `prod-o4grxfafcxxxx` et reçu une facture de 120,60 USD pour son utilisation mensuelle de ce produit.
  + Il a payé la facture au cours du mois.
+ Jane est le fabricant.
  + Son ID de compte est `111122223333`.
+ Paulo est le vendeur de disques.
  + Son ID de compte est `777788889999`. 
  + Il vit au Kansas, qui n'est pas soumis aux lois sur les facilitateurs du marché. 

### Flux de données d'événement de facturation pour le vendeur enregistré
<a name="billing-event-example-seller-of-record"></a>

En tant que vendeur officiel, Paulo facture l'acheteur, Arnav. 

Les tableaux suivants montrent les informations pertinentes dans le flux de données de Paulo lorsqu'il facture Arnav.


| billing\$1event\$1id  | from\$1account\$1id  | to\$1account\$1id  | end\$1user\$1account\$1id | product\$1id | action | transaction\$1type | 
| --- | --- | --- | --- | --- | --- | --- | 
| I0 | 737399998888 | 777788889999 | 737399998888 | prod-o4grxfafcxxxx | FACTURÉ | SELLER\$1REV\$1SHARE | 
| I1 | 737399998888 | AWS | 737399998888 | prod-o4grxfafcxxxx | FACTURÉ | AWS\$1TAX\$1PARTAGER | 
| I2 | 777788889999 | 111122223333 | 737399998888 | prod-o4grxfafcxxxx | FACTURÉ | SELLER\$1REV\$1SHARE | 
| I3 | 777788889999 | AWS | 737399998888 | prod-o4grxfafcxxxx | FACTURÉ | AWS\$1REV\$1PARTAGER | 




| parent\$1billing\$1event\$1id | disbursement\$1billing\$1event\$1id | amount | devise | invoice\$1date | invoice\$1id | 
| --- | --- | --- | --- | --- | --- | 
|  |  | 100 | USD | 2018-12-31T00:00:00Z | 781216640 | 
|  |  | 20,6 | USD | 2018-12-31T00:00:00Z | 781216640 | 
|  |  | -80 | USD | 2018-12-31T00:04:07Z | 788576665 | 
|  |  | -0,2 | USD | 2018-12-31T00:04:07Z | 788576665 | 

Les tableaux suivants présentent les informations pertinentes dans le flux de données de Paulo à la fin du mois, après le paiement de la facture par Arnav.


| billing\$1event\$1id  | from\$1account\$1id  | to\$1account\$1id  | end\$1user\$1account\$1id | product\$1id | action | transaction\$1type | 
| --- | --- | --- | --- | --- | --- | --- | 
| I10 | 737399998888 | 777788889999 | 737399998888 |  | DÉCAISSÉS | SELLER\$1REV\$1SHARE | 
| I12 | 777788889999 | 111122223333 | 737399998888 |  | DÉCAISSÉS | SELLER\$1REV\$1SHARE | 
| I13 | 777788889999 | AWS | 737399998888 | prod-o4grxfafcxxxx | DÉCAISSÉS | AWS\$1REV\$1PARTAGER | 
| I14 | AWS | 777788889999 |  |  | DÉCAISSÉS | DÉCAISSEMENT | 




| parent\$1billing\$1event\$1id | disbursement\$1billing\$1event\$1id | amount | devise | invoice\$1date | invoice\$1id | 
| --- | --- | --- | --- | --- | --- | 
| I0 | I14 | -100 | USD | 2018-12-31T00:00:00Z | 781216640 | 
| I2 | I14 | 80 | USD | 2018-12-31T00:04:07Z | 788576665 | 
| I3 | I14 | 0.2 | USD | 2018-12-31T00:04:07Z | 788576665 | 
|  |  | 19,8 | USD |  |  | 

### Flux de données d'événement de facturation pour le fabricant
<a name="billing-event-example-manufacturer"></a>

Les tableaux suivants présentent les informations pertinentes dans le flux de données de Jane lorsque Paulo facture Arnav.


| billing\$1event\$1id  | from\$1account\$1id  | to\$1account\$1id  | end\$1user\$1account\$1id | product\$1id | action | transaction\$1type | 
| --- | --- | --- | --- | --- | --- | --- | 
| I5 | 777788889999 | 111122223333 |  | prod-o4grxfafcxxxx | FACTURÉ | SELLER\$1REV\$1SHARE | 
| I6 | 777788889999 | 111122223333 |  | prod-o4grxfafcxxxx | FACTURÉ | SELLER\$1TAX\$1SHARE | 
| I7 | 111122223333 | AWS |  | prod-o4grxfafcxxxx | FACTURÉ | AWS\$1REV\$1PARTAGER | 




| parent\$1billing\$1event\$1id | disbursement\$1billing\$1event\$1id | amount | devise | invoice\$1date | invoice\$1id | 
| --- | --- | --- | --- | --- | --- | 
|  |  | 73,5 |  | 2018-12-31T00:04:07Z | 788576665 | 
|  |  | 6,5 |  | 2018-12-31T00:04:07Z | 788576665 | 
|  |  | -7,35 |  | 2018-12-31T00:04:07Z | 788576665 | 

Les tableaux suivants présentent les informations pertinentes dans le flux de données de Jane à la fin du mois, après le paiement de la facture.


| billing\$1event\$1id  | from\$1account\$1id  | to\$1account\$1id  | end\$1user\$1account\$1id | product\$1id | action | transaction\$1type | 
| --- | --- | --- | --- | --- | --- | --- | 
| I30 | 777788889999 | 111122223333 |  | prod-o4grxfafcxxxx | DÉCAISSÉS | SELLER\$1REV\$1SHARE | 
| I31 | 777788889999 | 111122223333 |  | prod-o4grxfafcxxxx | DÉCAISSÉS | SELLER\$1TAX\$1SHARE | 
| I32 | 111122223333 | AWS |  | prod-o4grxfafcxxxx | DÉCAISSÉS | AWS\$1REV\$1PARTAGER | 
| I33 | AWS | 111122223333 |  |  | DÉCAISSÉS | DÉCAISSEMENT | 




| parent\$1billing\$1event\$1id | disbursement\$1billing\$1event\$1id | amount | devise | invoice\$1date | invoice\$1id | 
| --- | --- | --- | --- | --- | --- | 
| I5 | I33 | -73,5 | USD |  |  | 
| I6 | I33 | -6,5 | USD |  |  | 
| I7 | I33 | 7,35 | USD |  |  | 
|  |  | 72,65 | USD |  |  | 

## Exemples de requêtes
<a name="data-feeds-billing-event-query-examples"></a>

Comme décrit dans[Collecte et analyse de données à l'aide de flux de données](data-feed-using.md), vous pouvez utiliser [Athena](https://docs.aws.amazon.com/athena/latest/ug/what-is.html) pour exécuter des requêtes sur les données collectées et stockées sous forme de flux de données dans votre compartiment Amazon S3 géré. Cette section fournit quelques exemples de façons courantes de le faire. Tous les exemples supposent qu'une devise unique est utilisée.

## Exemple 1 : Montant facturé, taxes comprises
<a name="data-feed-example-query-tax-invoice"></a>

Pour connaître le montant facturé aux acheteurs, taxes comprises, vous pouvez exécuter une requête comme indiqué dans l'exemple suivant. 

```
SELECT sum(amount) FROM billing_event 
WHERE 
  action = 'INVOICED'
  AND
  (
    (transaction_type in ('SELLER_REV_SHARE', 'SELLER_TAX_SHARE')
      -- to discard SELLER_REV_SHARE from Manufacturer to Channel Partner, aka cost of goods
      AND to_account_id='seller-account-id'
    )
  OR transaction_type= 'AWS_TAX_SHARE'
  );
```

## Exemple 2 : Montant facturé aux acheteurs pour le compte du vendeur
<a name="data-feed-example-query-invoice-for-seller"></a>

Pour savoir combien les acheteurs ont été facturés au nom d'un vendeur, vous pouvez exécuter une requête comme indiqué dans l'exemple suivant.

```
SELECT sum(amount) FROM billing_event 
WHERE
  action = 'INVOICED'
  AND transaction_type in ('SELLER_REV_SHARE', 'SELLER_TAX_SHARE')
  AND to_account_id='seller-account-id'
;
```

## Exemple 3 : montant AWS pouvant être collecté pour le compte du vendeur
<a name="data-feed-example-query-aws-collect"></a>

Pour connaître le montant que AWS vous pouvez collecter pour le compte d'un vendeur, déduction faite des remboursements, des crédits et des comptes annulés, vous pouvez exécuter une requête comme indiqué dans l'exemple suivant.

```
SELECT sum(amount) FROM billing_event 
WHERE
  -- what is invoiced on behalf of SELLER, incl. refunds/ credits and cost of goods
  transaction_type like 'SELLER_%' 
  -- FORGIVEN action records will "negate" related INVOICED
  and action in ('INVOICED','FORGIVEN') 
;
```

## Exemple 4 : Montant que le vendeur peut collecter
<a name="data-feed-example-query-seller-collect"></a>

Pour connaître le montant que les vendeurs peuvent collecter, vous pouvez exécuter une requête comme indiqué dans l'exemple suivant. Cet exemple supprime les frais de mise en vente et les AWS taxes perçus, et ajoute tout ajustement de solde exceptionnel. 

```
SELECT sum(amount) FROM billing_event
WHERE
  (transaction_type like 'SELLER_%' -- what is invoiced on behalf of SELLER
  or transaction_type like 'AWS_REV_%' -- what is owed to AWS
  or transaction_type = 'BALANCE_ADJUSTMENT' -- exceptionnal case
  )
  and action in ('INVOICED','FORGIVEN')
;
```

Vous pouvez également utiliser la requête suivante pour collecter les mêmes informations, comme indiqué dans l'exemple suivant.

```
SELECT sum(amount) FROM billing_event
WHERE
  balance_impacting = 1
  and action in ('INVOICED','FORGIVEN')
;
```

L'exemple suivant présente les mêmes informations, mais est limité aux transactions de 2018 et suppose que tous les acheteurs ont payé leurs factures. 

```
SELECT sum(amount) FROM billing_event
WHERE
  invoice_date between '2018-01-01' and '2018-12-31'
  and balance_impacting = 1
  and action in ('INVOICED','FORGIVEN')
;
```

## Exemple 5 : Montant des décaissements
<a name="data-feed-example-query-disbursements"></a>

Pour connaître le montant déboursé, vous pouvez exécuter une requête comme indiqué dans l'exemple suivant.

```
select sum(amount) FROM billing_event
WHERE
  action ='DISBURSED'
  and transaction_type like 'DISBURSEMENT%'
;
```

## Exemple 6 : Montant en attente de décaissement
<a name="data-feed-example-query-pending-disbursement"></a>

Pour connaître le montant en attente de versement, vous pouvez exécuter une requête comme indiqué dans l'exemple suivant. Cette requête supprime les montants déjà décaissés. 

```
SELECT sum(amount) FROM billing_event targeted 
WHERE
   (transaction_type like 'SELLER_%'  -- what is invoiced on behalf of SELLER
    or transaction_type like 'AWS_REV_%'  -- what is owed to AWS
    or transaction_type = 'BALANCE_ADJUSTMENT' -- exceptionnal case
   ) 
  -- DISBURSEMENT action records will "negate" 'INVOICED'
  -- but do not take into account failed disbursements
   AND 
    (not exists
      (select 1 
        from billing_event disbursement
          join billing_event failed_disbursement
           on disbursement.billing_event_id=failed_disbursement.parent_billing_event_id
        where
         disbursement.transaction_type='DISBURSEMENT'
         and failed_disbursement.transaction_type='DISBURSEMENT_FAILURE'
         and targeted.disbursement_billing_event_id=disbursement.billing_event_id
      )
    ) 
;
```

Une autre méthode pour obtenir les mêmes informations consiste à exécuter une requête pour obtenir le solde du vendeur, comme indiqué dans l'exemple suivant.

```
SELECT sum(amount) FROM billing_event
WHERE
 balance_impacting = 1
;
```

La requête suivante étend notre exemple. Il limite les résultats aux transactions de 2018 et renvoie des détails supplémentaires sur les transactions.

```
select sum(residual_amount_per_transaction)
from
 (SELECT
    max(billed_invoices.amount) invoiced_amount,
    sum(nvl(disbursed_invoices.amount,0)) disbursed_amount,
    -- Exercise left to the reader:
    -- use transaction_type to distinguish listing fee vs seller-owed money
    -- still pending collection
    max(transaction_type) transaction_type,
    max(billed_invoices.amount) 
      + sum(nvl(disbursed_invoices.amount,0)) residual_amount_per_transaction
  FROM billing_event billed_invoices
    -- find related disbursements
    left join billing_event disbursed_invoices
      on disbursed_invoices.action='DISBURSED'
      and disbursed_invoices.parent_billing_event_id=billed_invoices.billing_event_id
  WHERE
    billed_invoices.invoice_date between '2018-01-01' and '2018-12-31'
    and billed_invoices.transaction_type like 'SELLER_%' -- invoiced on behalf of SELLER
    and billed_invoices.action in ('INVOICED','FORGIVEN')
    -- do not take into account failed disbursements
    AND not exists
      (select 1 from billing_event failed_disbursement
       where disbursed_invoices.disbursement_billing_event_id = failed_disbursement.parent_billing_event_id
      )
   GROUP BY billed_invoices.billing_event_id
);
```

## Exemple 7 : Solde de l'ensemble de factures
<a name="data-feed-example-query-balance-invoice-set"></a>

Pour connaître la somme d'un ensemble de factures, vous pouvez exécuter une requête comme indiqué dans l'exemple suivant.

```
SELECT invoice_id, sum(amount) FROM billing_event targeted
WHERE
  -- invoice_id is only not null for invoiced records AND disbursed records 
  -- linking them to related disbursement -> no need to filter more precisely
  invoice_id in ('XXX','YYY') 
  -- filter out failed disbursements 
  AND not exists
      (select 1 
        from billing_event disbursement
          join billing_event failed_disbursement
           on disbursement.billing_event_id=failed_disbursement.parent_billing_event_id
        where
         disbursement.transaction_type='DISBURSEMENT'
         and failed_disbursement.transaction_type='DISBURSEMENT_FAILURE'
         and targeted.disbursement_billing_event_id=disbursement.billing_event_id
      ) 
  group by invoice_id;
```

# Flux de données d'utilisation quotidien
<a name="data-feed-daily-usage"></a>

Ce flux de données fournit des informations détaillées sur l'utilisation quotidienne de vos produits, notamment les indicateurs d'utilisation par les clients, les revenus estimés et les détails des prix. Les données vous aident à suivre la manière dont les clients utilisent vos produits et à calculer les revenus estimés en fonction des habitudes d'utilisation.

Le flux de données d'utilisation quotidienne est actualisé toutes les 24 heures.

Le tableau suivant répertorie et décrit les éléments du flux de données.


| Colonne | Description | 
| --- | --- | 
| valid\$1from | Première date pour laquelle la valeur de la clé primaire est valide par rapport aux valeurs des autres champs. | 
| insert\$1date | Date à laquelle un enregistrement a été inséré dans le flux de données. | 
| update\$1date | Date de la dernière mise à jour de l'enregistrement. | 
| delete\$1date | Date à laquelle l'enregistrement a été supprimé progressivement. | 
| utilisation\$1feed\$1id | Identifiant unique de l'enregistrement d'utilisation. Il s'agit d'un hachage salé par vendeur qui garantit la confidentialité des données tout en préservant l'unicité des records. | 
| date\$1d'utilisation | Date d'utilisation par le client, sans la composante horaire. L'heure est omise car l'utilisation est agrégée pour chaque jour. | 
| product\$1id | L'identifiant convivial du produit. Peut être utilisé pour joindre les `product_id` champs des flux de données Account, Billing\$1Event et Offer\$1Product. | 
| identifiant\$1accord | L'identifiant unique de l'accord. S'il est présent, cela commence toujours par`agmnt-`. Ce champ peut parfois être nul ou ne pas correspondre aux éléments de la ligne de facture correspondants. | 
| end\$1user\$1account\$1id | Le compte qui a réellement utilisé le produit, représenté par l'identifiant unique global (GUID) du compte de l'utilisateur final. Peut être utilisé pour rejoindre le flux de données du compte. Il s'agit d'un hachage salé par vendeur, et non de l'identifiant de compte AWS brut. | 
| ID de compte\$1payeur | Le compte censé payer pour l'utilisation du produit, représenté par l'identifiant unique mondial (GUID) du compte du payeur. Peut être utilisé pour rejoindre le flux de données du compte. Il s'agit d'un hachage salé par vendeur, et non de l'identifiant de compte AWS brut. | 
| region |  AWS Région dans laquelle l'acheteur a utilisé le produit. | 
| clé\$1dimensionnelle | La clé de dimension configurée par le vendeur lors de la publication de l'offre. | 
| unité\$1d'utilisation | Classification des unités d'utilisation, décrivant le type de mesure de l'utilisation. | 
| utilisation\$1quantité | La valeur d'utilisation pour le AWS service ou le type d'utilisation associé à l'enregistrement de facturation. La valeur est fournie avec un maximum de 2 décimales. | 
| monnaie\$1de tarification | Devise dans laquelle les recettes sont estimées. | 
| chiffre d'affaires estimatif dans la devise de tarification | Le chiffre d'affaires estimé calculé à l'aide du `usage_rate_per_unit` et`usage_quantity`. La valeur est fournie avec un maximum de 2 décimales. Pour que les ISV évaluent l'utilisation des offres des revendeurs, ce chiffre doit être multiplié par le pourcentage de part des revenus. | 
| identifiant\$1compte\$1destinataire | Le compte du vendeur qui reçoit les données dans les flux. | 
| offer\$1id | L'identifiant convivial de l'offre. Cela correspondra à l'offer\$1id du contrat d'achat lorsqu'il sera disponible. Peut être utilisé pour rejoindre l'offre et les flux de données cibles de l'offre. Pour des raisons de cohérence, ce champ n'est toujours pas nul. | 
| Taux d'utilisation par unité dans la devise de tarification | Le taux d'utilisation par unité qui peut être multiplié par le `usage_quantity` pour vérifier le`estimated_revenue_in_pricing_currency`. La valeur est fournie avec un maximum de 6 décimales. | 
| descriptif de l'article de charge | Description complète de l'élément de facturation, généralement au format offer\$1term\$1description\$1region\$1dimension\$1description pour les frais basés sur l'utilisation. | 

## Exemple de flux de données d'utilisation quotidienne
<a name="daily-usage-feed-example"></a>

Vous trouverez ci-dessous un exemple de flux de données d'utilisation quotidienne avec des colonnes clés. Pour des raisons de lisibilité, certaines colonnes ne sont pas affichées.


| date\$1d'utilisation | product\$1id | identifiant\$1accord | region | clé\$1dimensionnelle | unité\$1d'utilisation | utilisation\$1quantité | monnaie\$1de tarification | chiffre d'affaires estimatif dans la devise de tarification | offer\$1id | Taux d'utilisation par unité dans la devise de tarification | descriptif de l'article de charge | 
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | 
| 15-01-2025 | prod-abcd1234efgh5678 | agmnt-wxyz9876abcd5432 | us-east-1 | USE1\$1InputTokenCount | des unités | 24,00 | USD | 12,00 | offre-mnop5432qrst7890 | 0,500000 | Utilisation du logiciel AWS Marketplace \$1 US-East-1\$1Million de jetons d'entrée | 
| 15-01-2025 | prod-ijkl9876mnop1234 | agmnt-stuv5432wxyz9876 | us-west-2 | USE1\$1InputTokenCount | des unités | 1000,00 | USD | 5,50 | offre-abcd9876efgh5432 | 0,005500 | Utilisation du logiciel AWS Marketplace \$1 US-West-2\$1Appels d'API | 

# Flux de données de mappage hérité
<a name="data-feed-legacy-mapping"></a>

Ce flux de données indique comment les produits IDs et les offres IDs correspondent aux anciens identifiants uniques mondiaux (GUIDs). L'ancien a GUIDs été utilisé dans les anciens rapports, et les nouveaux IDs sont utilisés dans les flux de données et dans AWS Marketplace APIs.

Ce flux de données fournit des informations sur tous les produits que vous avez créés en tant que vendeur officiel et tous les produits que vous êtes autorisé à revendre.

Le flux de données de mappage hérité est actualisé toutes les 24 heures, de sorte que de nouvelles données sont disponibles quotidiennement.

Le tableau suivant explique les noms et les descriptions des colonnes du flux de données. 


| Nom de la colonne  | Description  | 
| --- | --- | 
| mapping\$1type | Qu'il s'agisse d'un ID de produit ou d'un ID d'offre.  | 
| legacy\$1id | ID hérité de ce produit ou de cette offre. | 
| new\$1id | ID convivial pour ce produit ou cette offre. Cet ID est utilisé comme clé primaire et avec toutes les actions d'API actuelles.  | 

## Exemple de flux de données de mappage hérité
<a name="data-feed-legacy-mapping-sample-data"></a>

Voici un exemple de flux de données de mappage hérité. Pour plus de lisibilité, les colonnes de l'historique des données ne sont pas affichées. Pour de plus amples informations sur les champs de l'historique des données, veuillez consulter [Historisation des données](data-feed-details.md#data-feed-historization).


| mapping\$1type | legacy\$1id  | new\$1id | 
| --- | --- | --- | 
| OFFRE | 8a806c74-dbd6-403e-9362-bb08f417ff37 | offer-dacpxznflfwin | 
| PRODUIT | 1368541d-890b-4b6c-9bb9-4a55306ab642 | prod-o4grxfafcxxxy | 
| OFFRE | 558d8382-6b3a-4c75-8345-a627b552f5f1 | offer-gszhmle5npzip | 



# Offre de flux de données
<a name="data-feed-offer"></a>

Le flux de données des offres fournit des informations sur toutes les offres que vous avez créées en tant que vendeur officiel. Si une seule offre comporte plusieurs révisions, toutes les révisions sont incluses dans le flux de données.

Lorsque vous modifiez une offre et que les données d'un champ exposé changent, un nouvel enregistrement est créé dans le flux de données pour la même clé primaire (`offer_id`plus`offer_revision`). Cependant, le `valid_from` champ possède une valeur différente. Pour de plus amples informations sur les colonnes de l'historique des flux de données, veuillez consulter [Historisation des données](data-feed-details.md#data-feed-historization).

Le flux de données de l'offre est actualisé toutes les 24 heures, de sorte que de nouvelles données sont disponibles quotidiennement.

Le tableau suivant fournit les noms et les descriptions des colonnes du flux de données. 


| Nom de la colonne  | Description  | 
| --- | --- | 
| offer\$1id | Identifiant convivial de l'offre.Peut être utilisé pour rejoindre le `offer_id` champ du flux de `Offer_Product` données. | 
| offer\$1revision | Révision de l'offre. Ce champ et le champ offer\$1id se combinent pour former la clé primaire.Avec`offer_id`, peut être utilisé pour joindre les `offer_revision` champs `offer_id` et du flux de `Target_Offer` données. | 
| name | Nom défini par le vendeur de l'offre.  | 
| expiration\$1date | Date et heure d'expiration de l'offre. | 
| opportunity\$1name | Toute donnée d'opportunité liée à cette offre. Si l'offre est liée à une opportunité AWS , ce champ est renseigné. | 
| opportunity\$1description | Toute information descriptive liée à cette offre. Si l'offre est liée à une opportunité AWS , ce champ est renseigné. | 
| seller\$1account\$1id | Identifiant global unique (GUID) du compte du vendeur. Peut être utilisé pour joindre le account\$1id champ dans le flux de données du compte. | 
| opportunity\$1id | L'identifiant de l'opportunité n'est renseigné que si un revendeur vend votre produit. Toutes les offres créées par différents partenaires de distribution (ou vendeurs) sont identiques opportunity\$1id si le produit est identique.  | 
| identifiant\$1compte\$1destinataire | Le compte du vendeur qui reçoit les données dans les flux. Peut être utilisé pour rejoindre le flux de `Account` données sur le `account_id` terrain. | 

## Exemple de flux de données d'offre
<a name="data-feed-offer-sample-data"></a>

Voici un exemple du flux de données de l'offre. Pour plus de lisibilité, les colonnes de l'historique des données ne sont pas affichées. Pour de plus amples informations sur les champs de l'historique des données, veuillez consulter [Historisation des données](data-feed-details.md#data-feed-historization).


| offer\$1id  | offer\$1revision | name | expiration\$1date | opportunity\$1name | opportunity\$1description | identifiant\$1compte\$1vendeur | identifiant\$1opportunité | 
| --- | --- | --- | --- | --- | --- | --- | --- | 
| offer-dacpxznflfwin | 1 | Offre du programme Contrat Entreprise | 9999-01-01T00:00:00Z |  |  |  |  | 
| offer-gszhmle5npzip | 1 | Offre privée créée par le vendeur | 2020-10-31T00:00:00Z |  |  |  |  | 
| offer-hmzhyle8nphlp | 1 | Offre du programme Contrat Entreprise | 9999-01-01T00:00:00Z |  |  |  |  | 

# Proposer un flux de données produit
<a name="data-feed-offer-product"></a>

Une offre peut avoir plusieurs produits, et un produit peut être inclus dans différentes offres. Ce flux de données répertorie des informations sur les relations entre les offres et les produits. 

Ce flux de données fournit des informations sur toutes les offres de produits que vous avez créées en tant que vendeur enregistré.

Lorsque vous ajoutez ou supprimez un produit d'une offre, vous créez une révision d'offre. 

Le flux de données produit de l'offre est actualisé toutes les 24 heures, de sorte que de nouvelles données sont disponibles tous les jours.

Le tableau suivant explique les noms et les descriptions des colonnes du flux de données. Pour de plus amples informations sur les colonnes de l'historique des flux de données, veuillez consulter [Historisation des données](data-feed-details.md#data-feed-historization).


| Nom de la colonne  | Description  | 
| --- | --- | 
| offer\$1id | Identifiant convivial de cette offre.Peut être utilisé pour rejoindre le `offer_id` champ du flux de `Offer` données. | 
| offer\$1revision | Se combine avec le champ offer\$1id pour former la clé étrangère de la révision de l'offre. | 
| product\$1id | L'identifiant convivial du produit, c'est la clé étrangère du produit que cette offre expose. Peut être utilisé pour rejoindre le `product_id` champ du flux de `Product` données. | 

## Exemple de flux de données produit d'une offre
<a name="data-feed-offer-product-sample-data"></a>

Voici un exemple de flux de données produit d'une offre. 


| offer\$1id  | offer\$1revision | product\$1id | 
| --- | --- | --- | 
| offer-dacpxznflfwin | 10 | prod-o4grxfafcxxxx | 
| offer-gszhmle5npzip | 24 | prod-o4grxfafcxxxy | 

# Offrir un flux de données cible
<a name="data-feed-offer-target"></a>

Ce flux de données répertorie les cibles de la révision d'une offre pour toutes les offres que vous avez créées en tant que vendeur enregistré. Si une seule offre comporte plusieurs révisions, toutes les révisions sont incluses dans le flux de données.

Lorsque vous effectuez une révision d'offre et que les données d'un champ exposé changent, un nouvel enregistrement est créé dans le flux de données pour la même clé primaire (`offer_id` plus `offer_revision`), mais avec une valeur différente pour le champ `valid_from`. 

Le flux de données cible de l'offre est actualisé toutes les 24 heures, de sorte que de nouvelles données sont disponibles quotidiennement.

Le tableau suivant explique les noms et les descriptions des colonnes du flux de données.


| Nom de la colonne  | Description  | 
| --- | --- | 
| offer\$1target\$1id | Clé primaire du flux. | 
| offer\$1id\$1offer\$1revision | Identifiant et révision de l'offre. Ces deux colonnes font référence à l'offre à laquelle cette cible se rapporte.Peut être utilisé pour joindre les `offer_revision` champs `offer_id` et du flux de `Target` données. | 
| target\$1type | Indique si le destinataire de l'offre est BuyerAccounts, ce qui indique une offre privée, ou ParticipatingPrograms. | 
| polarité | Indique si l'offre est destinée à être faite au `target_type`. Les valeurs possibles sont les suivantes : [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-offer-target.html)  | 
| value | Chaîne représentant la cible : soit un identifiant de AWS compte, soit un programme pouvant être utilisé avec une offre. Par exemple, [contrat standard pour AWS Marketplace (SCMP)](standardized-license-terms.md#standard-contracts) ou [programme de démonstration AWS Marketplace sur le terrain (FDP)](field-demonstration-program.md). | 

## Exemple de flux de données cible d'une offre
<a name="data-feed-offer-target-sample-data"></a>

Voici un exemple de flux de données cible d'une offre. Pour plus de lisibilité, les colonnes de l'historique des données ne sont pas affichées. Pour de plus amples informations sur les champs de l'historique des données, veuillez consulter [Historisation des données](data-feed-details.md#data-feed-historization). 


| offer\$1target\$1id  | offer\$1id  | offer\$1revision | target\$1type | polarité | value | 
| --- | --- | --- | --- | --- | --- | 
| 925ddc73f6a373b7d5544ea3210610803b600 | offer-dacpxznflfwin | 1 | ParticipatingPrograms | PositiveTargeting | EnterpriseContract | 
| 471ff22ae3165278f1fb960d3e14517bcd601 | offer-gszhmle5npzip | 1 | ParticipatingPrograms | PositiveTargeting | FieldDemonstration | 
| 511ff22adfj65278f1fb960d3e14517bcd6e602 | offer-gszhmle5npzip | 1 | ParticipatingPrograms  | PositiveTargeting | EnterpriseContract | 

# Flux de données produit
<a name="data-feed-product"></a>

Ce flux de données fournit des informations sur tous les produits que vous avez créés en tant que vendeur officiel et tous les produits que vous êtes autorisé à revendre.

Les données du produit sont mutables. Cela signifie que lorsque vous modifiez la valeur de l'un des champs suivants, un nouvel enregistrement est créé dans le flux de données avec une valeur différente pour le champ `valid_from`. Pour de plus amples informations sur les colonnes de l'historique des flux de données, veuillez consulter [Historisation des données](data-feed-details.md#data-feed-historization). 

Le flux de données produit est actualisé toutes les 24 heures, de sorte que de nouvelles données sont disponibles quotidiennement.

Le tableau suivant explique les noms et les descriptions des colonnes du flux de données. 


| Nom de la colonne  | Description  | 
| --- | --- | 
| product\$1id | Identifiant convivial du produit.Peut être utilisé pour joindre les `product_id` champs des flux de `Offer_Product` données `Account``Billing_Event`, et. | 
| manufacturer\$1account\$1id | Identifiant du propriétaire du produit. Il s'agit d'une clé étrangère du flux de données du [compte](data-feed-account.md).Peut être utilisé pour joindre le `account_id` champ du flux de `Account` données. | 
| product\$1code | Code de produit d'autorisation utilisé pour mesurer le produit. Cette valeur est également utilisée pour joindre des données à un rapport ou pour faire référence à ce qui est fourni dans AWS Marketplace Metering Service. | 
| title | Titre du produit.  | 

## Exemple du flux de données du produit
<a name="data-feed-product-sample-data"></a>

Voici un exemple de flux de données cible d'une offre. Pour plus de lisibilité, les colonnes de l'historique des données ne sont pas affichées. Pour de plus amples informations sur les champs de l'historique des données, veuillez consulter [Historisation des données](data-feed-details.md#data-feed-historization). 


| product\$1id  | manufacturer\$1account\$1id  | product\$1code | title | 
| --- | --- | --- | --- | 
| prod-o4grxfafcxxxx | 555568000000 | product\$1code\$11 | Product1 | 
| prod-t3grxfafcxxxy | 444457000000 | product\$1code\$12 | Product2 | 
| prod-x8faxxfafcxxy | 666678000000 | product\$1code\$13 | Product3 | 

# Flux de données d'élément fiscal
<a name="data-feed-tax-item"></a>

Ce flux de données fournit des informations sur le calcul des taxes pour une facture client.

Il peut y avoir plusieurs postes de facturation (`line_item_id`) pour un produit donné (`product_id`) sur une facture client donnée (`invoice_id`), un ou plusieurs pour chaque juridiction fiscale. Cela se produit, par exemple, pour les factures basées sur l'utilisation pour les clients qui appliquent des règles AWS régionales différentes selon les AWS entités (par exemple, les États-Unis et l'Irlande). Pour en savoir plus sur l'endroit où AWS collecte les taxes de vente, la TVA ou la TPS sur vos ventes et les reverse aux autorités fiscales locales, au nom d'AWS, Inc., consultez l'aide [fiscale d'Amazon Web Service](https://aws.amazon.com/tax-help/).

Le flux de données d'élément fiscal est actualisé toutes les 24 heures, de sorte que de nouvelles données sont disponibles quotidiennement.

Les données d'élément fiscal sont immuables. 

Le tableau suivant explique les noms et les descriptions des colonnes du flux de données. Pour de plus amples informations sur les colonnes de l'historique des données, veuillez consulter [Historisation des données](data-feed-details.md#data-feed-historization). 


| Nom de la colonne  | Description  | 
| --- | --- | 
| tax\$1item\$1id | Identifiant unique pour un enregistrement d'élément fiscal. | 
| invoice\$1id | Le numéro AWS de facture. Vous pouvez utiliser cette valeur avec la valeur de product\$1id pour rechercher des événements de facturation fiscale associés. | 
| line\$1item\$1id | Identifiant unique pour un poste de facture client. Les transactions de remboursement ont le même ID de poste que leurs transactions fiscales à terme. | 
| customer\$1bill\$1id | Identifiant unique de la facture client. Les acheteurs peuvent partager cet identifiant avec le vendeur pour aider à identifier et résoudre les questions relatives au calcul des taxes.  | 
| tax\$1liable\$1party | `AWS` ou `Seller`. Si le vendeur est assujetti à l'impôt, les taxes sont collectées. Si AWS c'est la partie assujettie à la taxe, la taxe de vente est collectée et versée par AWS. Pour plus d'informations, consultez la section [AWS Marketplace Vendeurs et recouvrement des taxes](https://aws.amazon.com/tax-help/marketplace). Si aucune taxe n'est collectée, aucune valeur n'est indiquée ici. Le vendeur étant responsable de la collecte des taxes, il doit déterminer si des taxes ont été collectées pour chaque facture.  | 
| transaction\$1type\$1code | Type de transaction. Les valeurs possibles sont les suivantes : [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-tax-item.html) Les transactions de remboursement partagent l'ID de poste avec les transactions à terme initiales. | 
| product\$1id | Clé étrangère du produit.Peut être utilisé pour rejoindre le flux de `Product` données sur le `product_id` terrain. | 
| product\$1tax\$1code | Code standard permettant d'identifier les propriétés fiscales d'un produit. Les vendeurs choisissent les propriétés lors de la création ou de la modification du produit. | 
| invoice\$1date | Date de création de la facture.  | 
| taxed\$1customer\$1account\$1id | Clé étrangère de l'entité de compte taxée.Peut être utilisé pour rejoindre le flux de `Account` données sur le `account_id` terrain. | 
| taxed\$1customer\$1country | Code pays ISO 3166 alpha 2 de l'adresse utilisée pour les calculs de taxes.  | 
| taxed\$1customer\$1state\$1or\$1region | État, région ou province utilisé pour les calculs de taxes. | 
| taxed\$1customer\$1city | Ville utilisée pour les calculs de taxes. | 
| taxed\$1customer\$1postal\$1code | Code postal utilisé pour les calculs de taxes. | 
| tax\$1location\$1code\$1taxed\$1jurisdiction | Géocode Vertex associé à l'emplacement taxé.  | 
| tax\$1type\$1code | Type de taxe appliqué à la transaction. Les valeurs possibles sont None, Sales et SellerUse. | 
| jurisdiction\$1level | Niveau de juridiction de l'adresse utilisée pour l'emplacement fiscal. Les valeurs possibles sont State, County, City et District. | 
| taxed\$1jurisdiction | Nom de la juridiction fiscale.  | 
| display\$1price\$1taxability\$1type | Détermine si le prix que les acheteurs voient inclut ou non les taxes. Toutes les AWS Marketplace offres sont hors taxes.  | 
| taxable\$1amount | Montant de la transaction qui est taxable, à ce niveau de juridiction. | 
| nontaxable\$1amount | Montant de la transaction qui n'est pas taxable, à ce niveau de juridiction. | 
| tax\$1jurisdiction\$1rate | Taux de taxe appliqué, à ce niveau de juridiction. | 
| tax\$1amount | Montant de la taxe facturée, à ce niveau de juridiction. | 
| tax\$1currency | Code de devise ISO 4217 alpha 3 pour les montants ci-dessus. | 
| tax\$1calculation\$1reason\$1code | Indique si la transaction est taxable, non taxable, exonérée ou détaxée, organisée selon le niveau de juridiction. | 
| date\$1used\$1for\$1tax\$1calculation | Date utilisée pour le calcul des taxes sur la transaction. | 
| customer\$1exemption\$1certificate\$1id | ID de certificat du certificat d'exonération. | 
| customer\$1exemption\$1certificate\$1id\$1domain | Emplacement où le certificat est stocké sur les systèmes Amazon.  | 
| customer\$1exemption\$1certificate\$1level | Niveau de juridiction qui a fourni l'exonération. | 
| customer\$1exemption\$1code | Code qui spécifie l'exonération : par exemple, RESALE. | 
| customer\$1exemption\$1domain | Système Amazon utilisé pour capturer les informations d'exonération du client, le cas échéant. | 
| transaction\$1reference\$1id | Identificateur qui vous permet de recouper les données des rapports suivants : [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/marketplace/latest/userguide/data-feed-tax-item.html)  | 
| montant\$1légal | Le montant de la taxe dans la devise légale pour la taxe à payer. | 
| monnaie\$1légale | Devise légale dans laquelle la taxe doit être payée. | 
| identifiant de facture fiscale | Identifiant de facture fiscale pour la ligne fiscale en cas de double facturation. Ce champ sera nul pour les scénarios de facturation unifiée. | 
| date\$1facture\$1fiscale | Date de la facture fiscale. Ce champ sera nul pour les scénarios de facturation unifiée. | 

**Note**  
À compter du 5 août 2021, les taxes internationales du Marketplace Facilitator relatives aux AWS Marketplace ventes figureront dans le flux de données des articles fiscaux. Cela signifie qu'à compter du 5 août 2021, chaque AWS\$1TAX\$1SHARE enregistrement SELLER\$1TAX\$1SHARE du flux de données des événements de facturation devrait avoir un enregistrement correspondant dans le flux de données des éléments fiscaux.

## Exemple de flux de données d'élément fiscal
<a name="data-feed-tax-item-sample-data"></a>

Voici un exemple de flux de données d'élément fiscal. Dans le flux de données, ces informations sont présentées dans un seul tableau. Pour plus de lisibilité, les données sont présentées ici dans plusieurs tableaux et toutes les colonnes ne sont pas affichées. 


| tax\$1item\$1id | invoice\$1id | line\$1item\$1id | customer\$1bill\$1id | 
| --- | --- | --- | --- | 
| 6p2ni6tu041xagvhbyanbgxl3xameha16txjoav\$10001 | 781216640 | 71000000000000000000 | 2210000000000000000 | 
| 6p2ni6tu041xagvhbyanbgxl3xameha16txjoav\$10002 | 781216640 | 53000000000000000000 | 2210000000000000000 | 
| flr4jobxjzww8czdsrq4noue2uxd56j39wxw0k7\$10001 | 250816266 | 76400000000000000000 | 5720000000000000000 | 
| gfkjjobxjzw56jgkrsrqgjtk52uxd56j39wgj567d\$10002 | 280336288 | 76400000000000000000 | 5724390000000000000 | 
| wwk1qpvb8ran3geiw8e3mp6dgs2qj7wpkuwhgk1\$10001 | 451431024 | 99300000000000000000 | 1230000000000000000 | 
| wwk1qpvb8ran3geiw8e3mp6dgs2qj7wpkuwhgk1\$10002 | 451431024 | 99300000000000000000 | 3120000000000000000 | 
| fnohdid8kwgqq9lvii2k30spn3ftgwihbe8h75x\$10001 | 229987654 | 92100000000000000000 | 6390000000000000000 | 




| tax\$1liable\$1party | transaction\$1type\$1code | product\$1id | product\$1tax\$1code | invoice\$1date | 
| --- | --- | --- | --- | --- | 
| Vendeur | AWS | prod-o4grxfafcxxxx | AWSMP\$1SOFTWARE\$1RA | 2018-12-31T00:00:00Z | 
| Vendeur | AWS | prod-o4grxfafcxxxx | AWSMP\$1SOFTWARE\$1RA | 2018-12-31T00:00:00Z | 
| Vendeur | AWS | prod-t3grxfafcxxxy | AWS\$1REMOTE\$1LOGICIEL\$1D'ACCÈS | 2018-08-31T00:00:00Z | 
| Vendeur | REMBOURSEMENT | prod-t3grxfafcxxxy | AWS\$1REMOTE\$1LOGICIEL\$1D'ACCÈS | 2018-08-31T00:00:00Z | 
| Vendeur | AWS | prod-x8faxxfafcxxy | AWS\$1REMOTE\$1LOGICIEL\$1D'ACCÈS | 2018-08-31T00:00:00Z | 
| Vendeur | TAXONLYREFUND | prod-x8faxxfafcxxy | AWS\$1REMOTE\$1LOGICIEL\$1D'ACCÈS | 2018-05-31T00:00:00Z | 
| AWS | AWS | prod-wghj8xfafrhgj | AWS\$1REMOTE\$1LOGICIEL\$1D'ACCÈS | 2019-07-31T00:00:00Z | 




| taxed\$1customer\$1account\$1id | taxed\$1customer\$1country | taxed\$1customer\$1state\$1or\$1region | taxed\$1customer\$1city | taxed\$1customer\$1postal\$1code | 
| --- | --- | --- | --- | --- | 
| VIeGA2T9J3MUXIOH9WC8LSND xRIOCM5 XXCg GCGUree | ETATS-UNIS | GA | MILTON | 48573-4839 | 
| VIeGA2T9J3MUXIOH9WC8LSND xRIOCM5 XXCg GCGUree | ETATS-UNIS | GA | MILTON | 48573-4839 | 
| TRo7nyo5JW PLyX81VX9Ji04 O1FF8BiQI88W8 eEwTur | ETATS-UNIS | NC | DURHAM | 27517-4834 | 
| TRo7nyo5JW PLyX81VX9Ji04 O1FF8BiQI88W8 eEwTur | ETATS-UNIS | NC | DURHAM | 27517-4834 | 
| TRo7nyo5JW PLyX81VX9Ji04 O1FF8BiQI88W8 eEwTur | ETATS-UNIS | TX | NON APPLICABLE | 75844-1235 | 
| TRo7nyo5JW PLyX81VX9Ji04 O1FF8BiQI88W8 eEwTur | ETATS-UNIS | TX | HOUSTON | 75844-1235 | 
| 192a0421313e41f069b52962ed7babf716291b688 | ETATS-UNIS | CT | NEW HAVEN | 06002-2948 | 




| tax\$1location\$1code\$1taxed\$1jurisdiction | tax\$1type\$1code | jurisdiction\$1level | taxed\$1jurisdiction | display\$1price\$1taxability\$1type | taxable\$1amount | nontaxable\$1amount | 
| --- | --- | --- | --- | --- | --- | --- | 
| 460473664 | Ventes | State | GA | Exclusive | 100 | 0 | 
| 66301164 | Ventes | County | FULTON | Exclusive | 0 | 100 | 
| 692938178 | SellerUse | State | NC | Exclusive | 58.1 | 523.8 | 
| 692938178 | SellerUse | State | NC | Exclusive | -58.1 | 523.8 | 
| 356794387 | Ventes | State | TX | Exclusive | 1105.14 | 0 | 
| 528887443 | Ventes | City | HOUSTON | Exclusive | -36 | 0 | 
| 171248162 | Ventes | State | CT | Exclusive | 0 | 114.55 | 




| tax\$1jurisdication\$1rate | tax\$1amount | tax\$1currency | tax\$1calculation\$1reason\$1code | date\$1used\$1for\$1tax\$1calculation | 
| --- | --- | --- | --- | --- | 
| 0.206 | 20,6 | USD | Taxable | 2018-10-31T00:00:00Z | 
| 0 | 0 | USD | NonTaxable | 2018-10-31T00:00:00Z | 
| 0.1 | 5.8 | USD | Taxable | 2018-07-31T00:00:00Z | 
| 0.1 | -5.8 | USD | Taxable | 2018-07-31T00:00:00Z | 
| 0.06 | 66.3 | USD | Taxable | 2018-07-31T00:00:00Z | 
| 0,01 | -0.36 | USD | NonTaxable | 2018-07-31T00:00:00Z | 
| 0 | 0 | USD | Exonération | 2019-06-30T00:00:00Z |