本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

# 中的賣方交付資料摘要 AWS Marketplace
<a name="data-feed-service"></a>

AWS Marketplace 提供資料饋送作為一種機制，在賣方擁有的商業智慧工具之間，將結構化up-to-date產品和客戶資訊從 AWS Marketplace 系統傳送至賣方 Amazon S3 儲存貯體以進行 ETL （擷取、轉換和載入）。資料摘要會收集逗號分隔值 (CSV) 檔案，並將其交付至您提供的加密 Amazon S3 儲存貯體。資料饋送會在一天內產生，並包含前一天 24 小時的資料。下列各節提供資料摘要的概觀，並說明如何存取和使用。後續各節說明每個資料饋送。

交易資料會以雙時間結構交付並附加，因此賣方可以沿著兩個時間軸存放和查詢資料，同時包含兩個時間戳
+ 有效時間：當事實發生在真實世界時 （您所知）
+ 系統時間：當該事實記錄到資料庫時 (「當您知道時」)。

資料饋送會在前一天更新後每天午夜 UTC 交付，其中包含前一天 24 小時的資料。更新可由客戶訂閱、開立發票或 AWS 支付款項的客戶定義。

**Topics**
+ [AWS Marketplace 資料饋送的儲存和結構](data-feed-details.md)
+ [存取資料摘要](data-feed-accessing.md)
+ [使用資料饋送收集和分析資料](data-feed-using.md)
+ [資料饋送資料表概觀](data-feed-joining.md)
+ [資料饋送查詢範例](data-feed-full-examples.md)
+ [資料饋送](data-feeds.md)

# AWS Marketplace 資料饋送的儲存和結構
<a name="data-feed-details"></a>

AWS Marketplace 提供資料饋送作為一種機制，在賣方擁有的商業智慧工具之間，將結構化up-to-date產品和客戶資訊從 AWS Marketplace 系統傳送至賣方 Amazon S3 儲存貯體以進行 ETL （擷取、轉換和載入）。本主題提供有關資料饋送結構和儲存體的詳細資訊。

資料饋送會收集逗號分隔值 (CSV) 檔案，並將其交付至您提供的加密 Amazon S3 儲存貯體。CSV 檔案具有下列特性：
+ 它們遵循 [4180 標準](https://tools.ietf.org/html/rfc4180)。
+ 字元編碼是不含 BOM 的 UTF-8。
+ 逗號用作值之間的分隔符號。
+ 欄位會被雙引號逸出。
+ `\n` 是換行字元。
+ 日期會以 UTC 時區報告、以 ISO 8601 日期和時間格式報告，且在 1 秒內準確。
+ 所有 `*_period_start_date`和 `*_period_end_date`值都包含在內，這表示 `23:59:59`是任何一天的最後一個可能時間戳記。
+ 所有貨幣欄位前面都有貨幣欄位。
+ 貨幣欄位使用句點 (`.`) 字元做為十進位分隔符號，而不使用逗號 (，) 做為千位分隔符號。

資料饋送的產生和存放方式如下：
+ 資料饋送會在一天內產生，並包含前一天的 24 小時資料。
+ 在 Amazon S3 儲存貯體中，資料饋送是使用下列格式按月組織：

  `bucket-name/data-feed-name_version/year=YYYY/month=MM/data.csv`
+ 產生每日資料饋送時，它會附加到該月的現有 CSV 檔案。當新的月份開始時，會為每個資料饋送產生新的 CSV 檔案。
+ 資料摘要中的資訊會從 2010/01/01 回填至 2020/04/30 （包含），並可在 `year=2010/month=01`子資料夾中的 [CSV 檔案中](#data-feed-details)取得。

  您可能會注意到指定資料饋送的當月檔案僅包含資料欄標頭，且沒有資料的情況。這表示該月沒有饋送的新項目。這可能會在更新頻率較低的資料饋送中發生，例如產品饋送。在這些情況下，資料可在回填資料夾中取得。
+ 在 Amazon S3 中，您可以建立 [Amazon S3 生命週期政策](https://docs.aws.amazon.com/AmazonS3/latest/userguide/create-lifecycle.html)，以管理檔案在儲存貯體中保留多久。
+ 您可以設定 Amazon SNS 在資料交付到加密的 Amazon S3 儲存貯體時通知您。如需如何設定通知的資訊，請參閱《[Amazon Simple Notification Service 開發人員指南》中的 Amazon SNS 入門](https://docs.aws.amazon.com/sns/latest/dg/sns-getting-started.html)。 **

## 資料的歷史化
<a name="data-feed-historization"></a>

每個資料饋送都包含記錄資料歷史記錄的資料欄。除了 之外`valid_to`，這些資料欄適用於所有資料饋送。它們被納入為常見的歷史記錄結構描述，有助於查詢資料。


| 資料欄名稱  | 描述  | 
| --- | --- | 
| valid\$1from | 相對於其他欄位的值，主索引鍵值對 有效的第一個日期。 | 
| valid\$1to | 此欄僅顯示在[地址](data-feed-address.md)資料饋送上，且一律為空白。 | 
| insert\$1date | 將記錄插入資料饋送的日期。 | 
| update\$1date | 記錄上次更新的日期。 | 
| delete\$1date | 此欄一律為空白。 | 

以下顯示這些資料欄的範例。


|  valid\$1from  |  valid\$1to  |  insert\$1date  |  update\$1date  |  delete\$1date  | 
| --- | --- | --- | --- | --- | 
|  2018-12-12T02：00：00Z  |   |  2018-12-12T02：00：00Z  |  2018-12-12T02：00：00Z  |   | 
|  2019-03-29T03：00：00Z  |   |  2019-03-29T03：00：00Z  |  2019-03-29T03：00：00Z  |   | 
|  2019-03-29T03：00：00Z  |   |  2019-03-29T03：00：00Z  |  2019-04-28T03：00：00Z  |   | 

`valid_from` 和 `update_date` 欄位一起形成*雙時間資料模型*。`valid_from` 欄位命名為 時， 會告訴您項目的有效期。如果項目已編輯，它可以在摘要中具有多個記錄，每個記錄都有不同的 `update_date`，但`valid_from`日期相同。例如，若要尋找項目的目前值，您可以從具有最新`valid_from`日期的記錄`update_date`清單中找到具有最新 的記錄。

在上述範例中，記錄最初是在 2018-12-12 建立。然後，它在 2019-03-29 變更 （例如，如果記錄中的地址變更）。稍後於 2019-04-28 更正地址變更 （因此 `valid_from` 不會變更，但 `update_date`會變更）。更正地址 （罕見事件） 會追溯變更原始`valid_from`日期的記錄，因此欄位不會變更。尋找最新記錄的查詢`valid_from`會傳回兩個記錄，具有最新記錄的 `update_date`會為您提供實際的目前記錄。

# 存取資料摘要
<a name="data-feed-accessing"></a>

透過 AWS Marketplace，您可以使用資料饋送作為一種機制，在賣方擁有的商業智慧工具之間，從 Amazon S3 儲存貯體 ETL （擷取、轉換和載入） AWS Marketplace 的系統傳送結構化、up-to-date產品和客戶資訊。您需要將環境設定為接收加密 Amazon S3 儲存貯體的資料饋送。本主題說明如何存取和取消訂閱資料摘要。

**Topics**
+ [存取資料饋送](#data-feed-accessing-procedure)
+ [資料饋送政策](#data-feed-policies)
+ [取消訂閱資料摘要](#data-feed-unsubscribing)

## 存取資料饋送
<a name="data-feed-accessing-procedure"></a>

1. 使用 SQL 和 ETL （擷取、轉換、載入） 體驗來配置商業智慧或資料工程師。此人員也需要設定 APIs的經驗。

1. 設定 Amazon Simple Storage Service 儲存貯體和資料摘要的訂閱。使用與您的 Marketplace 產品清單相關聯的 AWS 賣方帳戶 ID。若要這麼做，您可以[觀看此 YouTube 影片](https://www.youtube.com/watch?v=heCsZdOT-hw)或遵循下列步驟。

   影片和步驟說明如何使用有助於簡化組態的[CloudFormation 範本](https://s3.amazonaws.com/aws-marketplace-reports-resources/DataFeedsResources.yaml)。

   1. 開啟 Web 瀏覽器並登入 [AWS Marketplace 管理入口網站](https://aws.amazon.com/marketplace/management/)，然後前往[設定客戶資料儲存](https://aws.amazon.com/marketplace/management/reports/data-feed-configuration)。

   1. 選擇**使用 CloudFormation 範本建立資源**，在另一個視窗中開啟 CloudFormation 主控台中的範本。

   1. 在範本中，指定下列項目，然後選擇**下一步**：
      + 堆疊名稱 – 您要建立以啟用資料饋送存取的資源集合。
      + Amazon S3 儲存貯體名稱 – 儲存資料摘要的儲存貯體。
      + （選用） Amazon SNS 主題名稱 – Amazon Simple Storage Service 儲存貯體時接收通知的主題。

   1. 在**檢閱**頁面上，確認您的項目，然後選擇**建立堆疊**。這會開啟具有 CloudFormation 狀態和詳細資訊的新頁面。

   1. 從**資源**索引標籤中，將下列資源的 Amazon Resource Name (ARNs) 從 CloudFormation 頁面複製到 AWS Marketplace [設定客戶資料儲存](https://aws.amazon.com/marketplace/management/reports/data-feed-configuration)頁面上的欄位：
      + 用於儲存資料饋送的 Amazon S3 儲存貯體
      + AWS KMS 用於加密 Amazon S3 儲存貯體的金鑰
      + （選用） 當 將新資料 AWS 交付至 Amazon S3 儲存貯體時，用於接收通知的 Amazon SNS 主題

   1. 在**設定客戶資料儲存**頁面上，選擇**提交**。

   1. （選用） 編輯 CloudFormation 範本建立的政策。如需詳細資訊，請參閱[資料饋送政策](#data-feed-policies)。

      您現在已訂閱 資料摘要。下次產生資料摘要時，您可以存取資料。

1. 使用 ETL （擷取、轉換、載入） 操作將資料饋送連接到您的資料倉儲或關聯式資料庫。
**注意**  
資料工具具有不同的功能。您必須讓商業智慧工程師或資料工程師參與，才能設定整合以符合工具的功能。

1. 若要執行或建立 SQL 查詢，請設定資料饋送，以在資料工具中強制執行主要和外部金鑰。每個資料饋送都代表唯一的資料表，而且您必須使用實體關係來設定資料結構描述中的所有資料饋送。如需資料表和實體關係的詳細資訊，請參閱本指南[資料饋送資料表概觀](data-feed-joining.md)中的 。

1. 設定 Amazon Simple Notification Service 以自動重新整理您的資料倉儲或關聯式資料庫。您可以設定 Amazon SNS 通知，在將來自每個唯一摘要的資料交付至 Amazon S3 儲存貯體時傳送提醒。如果賣方資料工具支援此功能，則可以利用這些通知，在透過資料饋送接收新資料時自動重新整理賣方資料倉儲。請參閱《[Amazon Simple Notification Service 開發人員指南》中的 Amazon SNS 入門](https://docs.aws.amazon.com/sns/latest/dg/sns-getting-started.html)。 **

   通知範例：

   ```
   {
           "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]"
           }
   ```

   通知可以有下列`executionStatus`狀態：
   + `SKIPPED` – 賣方當天沒有新資料。
   + `COMPLETED` – 我們交付了包含新資料的摘要。
   + `FAILED` – 饋送交付發生問題。

1. 執行 SQL 查詢來驗證設定。您可以使用[本指南中的範例查詢](https://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-full-examples.html)，或 GitHub 上的查詢，網址為 [ https：//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)。
**注意**  
本指南中的範例查詢是針對 AWS Athena 撰寫的。您可能需要修改查詢，以便與工具搭配使用。

1. 決定商業使用者想要在何處使用資料。例如，您可以：
   + 從資料倉儲或 SQL 資料庫匯出 .csv 資料。
   + 將您的資料連接到視覺化工具，例如 PowerBI 或 Tableau。
   + 將資料映射到您的 CRM、ERP 或財務工具，例如 Salesforce、Infor 或 Netsuite。

如需 CloudFormation 範本的詳細資訊，請參閱*AWS CloudFormation 《 使用者指南*》中的[使用 CloudFormation 範本](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/template-guide.html)。

## 資料饋送政策
<a name="data-feed-policies"></a>

當 CloudFormation 範本建立您的 Amazon S3 儲存貯體時，它會建立連接到該儲存貯體、 AWS KMS 金鑰和 Amazon SNS 主題的存取政策。這些政策允許 AWS Marketplace 報告服務使用資料饋送資訊寫入您的儲存貯體和 SNS 主題。每個政策都有如下所示的區段 （此範例來自 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/*"
            ]
        },
```

在此政策中， AWS Marketplace 會使用 `reports.marketplace.amazonaws.com`服務主體將資料推送至 Amazon S3 儲存貯體。您已在 CloudFormation 範本中指定 *amzn-s3-demo-bucket*。

當 AWS Marketplace 報告服務呼叫 Amazon S3 AWS KMS或 Amazon SNS 時，它會提供想要寫入儲存貯體的資料 ARN。若要確保寫入儲存貯體的唯一資料是代表您寫入的資料，您可以在 政策的條件`aws:SourceArn`中指定 。在下列範例中，您必須將 *account-id* 取代為 的 ID 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"]
                        }
                }
        },
```

## 取消訂閱資料摘要
<a name="data-feed-unsubscribing"></a>

開啟 Web 瀏覽器並登入 [AWS Marketplace 管理入口網站](https://aws.amazon.com/marketplace/management/)。然後，前往[聯絡我們頁面](https://aws.amazon.com/marketplace/management/contact-us/)，向 AWS Marketplace 賣方營運團隊提交取消訂閱請求。取消訂閱請求最多可能需要 10 個工作天才能處理。

# 使用資料饋送收集和分析資料
<a name="data-feed-using"></a>

AWS Marketplace 提供資料饋送作為一種機制，在賣方擁有的商業智慧工具之間，將結構化up-to-date產品和客戶資訊從 AWS Marketplace 系統傳送至賣方 Amazon S3 儲存貯體以進行 ETL （擷取、轉換和載入）。當 Amazon S3 儲存貯體中有資料可用時，您可以透過下列方式使用資料饋送：
+ 從您在 中建立的 Amazon S3 儲存貯體下載 .CSV 檔案[存取資料摘要](data-feed-accessing.md)，以便您可以在試算表中檢視資料。
+ 使用 ETL （擷取、轉換和載入）、SQL 查詢、商業分析工具來收集和分析資料。

  您可以使用 AWS 服務來收集和分析資料，或可執行 .CSV 型資料集分析的任何第三方工具。

如需收集和分析資料之資料摘要的詳細資訊，請參閱下列範例。

## 範例：使用 AWS 服務來收集和分析資料
<a name="data-feed-using-example"></a>

下列程序假設您已將環境設定為接收資料摘要至 Amazon S3 儲存貯體，且儲存貯體包含資料摘要。

**從資料饋送收集和分析資料**

1. 從[AWS Glue 主控台](https://console.aws.amazon.com/glue)[建立爬蟲程式](https://docs.aws.amazon.com/glue/latest/dg/add-crawler.html)，以連線至存放資料饋送的 Amazon S3 儲存貯體、擷取您想要的資料，並在 中建立中繼資料表 AWS Glue Data Catalog。

   如需 的詳細資訊 AWS Glue，請參閱 [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. 從 [Athena 主控台](https://console.aws.amazon.com/athena)，[對 中的資料執行 SQL 查詢 AWS Glue Data Catalog](https://docs.aws.amazon.com/athena/latest/ug/querying-athena-tables.html)。

   如需 Athena 的詳細資訊，請參閱[https://docs.aws.amazon.com/athena/latest/ug/what-is.html](https://docs.aws.amazon.com/athena/latest/ug/what-is.html)。

1. 從[快速主控台](http://quicksight.aws.amazon.com)[建立分析](https://docs.aws.amazon.com/quick/latest/userguide/creating-an-analysis.html)，然後[建立資料的視覺](https://docs.aws.amazon.com/quick/latest/userguide/creating-a-visual.html)效果。

   如需 Quick 的詳細資訊，請參閱《[https://docs.aws.amazon.com/quick/latest/userguide/welcome.html](https://docs.aws.amazon.com/quick/latest/userguide/welcome.html)》。

如需使用 AWS 服務在資料饋送中收集和分析資料的詳細範例，請參閱 AWS Marketplace 部落格中的[使用賣方資料饋送交付服務、Amazon Athena 和 Quick 建立賣方報告](https://aws.amazon.com/blogs/awsmarketplace/using-seller-data-feed-delivery-service-amazon-athena-and-amazon-quicksight-to-create-seller-reports/)。

# 資料饋送資料表概觀
<a name="data-feed-joining"></a>

AWS Marketplace 以一組資料表的形式提供資料饋送，您可以合併為查詢提供更多內容。

AWS Marketplace 會在您的資料饋送中提供下列一般網域或感興趣的類別：
+ **目錄** – 包含您帳戶中產品和優惠的相關資訊。
+ **帳戶** – 包括提供或購買產品的帳戶相關資訊 AWS Marketplace （您自己的帳戶或您合作對象的帳戶，例如管道合作夥伴或買方）。
+ **收入** – 包括有關帳單、支出和稅金的資訊。
+ **採購** – 包含您建立做為記錄賣方的產品優惠協議相關資訊。

此圖表顯示目錄、帳戶和營收網域中的資料表。

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

## 目錄相關資料表
<a name="data-feed-catalog-domain"></a>

下圖顯示目錄網域中資料表之間的關係，以及資料表中的欄位。

![\[目錄網域中產品、Profession_Product、Profess、Profession_Target 和 Legacy_id_mapping 資料表之間的關係。\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/images/datafeeds-catalog-details.png)


`Product`、`Offer_Product`、`Offer_Target`、 `Offer`和 `Legacy_id_mapping`\$1tables 位於目錄網域中。

`Offer_Target` 資料表包含目標 `account_id` 的值欄位，但僅限於`target_type`值為 時`account`。

`Legacy_id_mapping` 資料表不會用於目前的資料。

**注意**  
如需這些資料表的詳細資訊，包括資料表中每個欄位的描述和可建立的聯結，請參閱下列主題：  
[產品資料饋送](data-feed-product.md)
[提供產品資料饋送](data-feed-offer-product.md)
[提供資料饋送](data-feed-offer.md)
[提供目標資料饋送](data-feed-offer-target.md)
[舊版映射資料饋送](data-feed-legacy-mapping.md)

## 帳戶相關資料表
<a name="data-feed-accounts-domain"></a>

下圖顯示帳戶網域中 `Account`和 `Address` 資料表之間的關係，以及資料表中的欄位。

![\[帳戶網域中的帳戶和地址資料表與每個資料表中的欄位之間的關係。\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/images/datafeeds-accounts-details.png)


**注意**  
如需這些資料表的詳細資訊，包括資料表中每個欄位的描述，以及您可以建立的聯結，請參閱下列主題：  
[帳戶資料饋送](data-feed-account.md)
[位址資料饋送](data-feed-address.md)

## 營收相關資料表
<a name="data-feed-revenue-domain"></a>

下圖顯示營收網域中 `Billing_Event` 和 `Tax_Item` 資料表之間的關係，以及資料表中的欄位。`Billing_Event` 資料表包含有關支出以及帳單事件的資訊。

![\[Billing_Event 與 Revenue 網域中的 Tax_Item 資料表，以及每個資料表中的欄位之間的關係。\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/images/datafeeds-revenue-details.png)


**注意**  
如需這些資料表的詳細資訊，包括資料表中每個欄位的描述，以及您可以建立的聯結，請參閱下列主題：  
[帳單事件資料摘要](data-feed-billing-event.md)
[稅務項目資料饋送](data-feed-tax-item.md)

### 採購相關資料表
<a name="procurement-tables"></a>

下圖顯示採購網域中協議資料表中的欄位。

**注意**  
如需這些資料表的詳細資訊，包括資料表中每個欄位的描述，以及可建立的聯結，請參閱本指南[協議資料饋送](data-feed-agreements.md)中的 。

以下各節提供每個網域的*實體關係* (ER) 圖表。每個 ER 圖表會顯示資料表和每個資料表中的欄位，以及您可以用來聯結資料表的欄位。

**注意**  
本節中的 ER 圖表不包含所有資料饋送的常用欄位。如需常見欄位的詳細資訊，請參閱[AWS Marketplace 資料饋送的儲存和結構](data-feed-details.md)。

下表說明 ER 圖表中使用的符號。


| 符號 | 描述 | 
| --- | --- | 
|  ![\[字母 "PK" 做為符號的影像。\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/images/datafeeds-primary-key.png)  |  **主索引**鍵 – 資料表的主索引鍵。與 `valid_from`和 `update_date` 欄位搭配使用時，它是唯一的。如需將這些欄位一起使用的詳細資訊，請參閱 [資料的歷史化](data-feed-details.md#data-feed-historization)。如果多個欄位標記為主索引鍵，則欄位會一起形成主索引鍵。  | 
|  ![\[字母 "FK" 做為符號的影像。\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/images/datafeeds-foreign-key.png)  |   **外部索引鍵** – 代表不同資料表中主索引鍵的欄位。在資料表中不一定是唯一的。   在某些情況下，如果目前資料表中的記錄在外部資料表中沒有對應的記錄，則外部索引鍵可以為空白。   | 
|  ![\[字母 "AK" 做為符號的影像。\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/images/datafeeds-alternate-key.png)  |   **備用索引**鍵 – 可用來做為資料表中索引鍵的索引鍵。遵循與主索引鍵相同的唯一性規則。  | 
|  ![\[一端有十字的線條影像，另一端有圓圈和叉。\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/images/datafeeds-one-to-many.png)  |   **連接器** – 欄位之間的行代表連線，這是兩個可用來聯結資料表的欄位。該行的尾端代表連線類型。此範例代表one-to-many連線。  | 

**連接器類型**

下表顯示每個連接器可以擁有的端點類型。


| 連接器類型 | 描述 | 
| --- | --- | 
|  ![\[一端有十字的線條影像。\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/images/datafeeds-one-to-n.png)  |   **1 到 n** – 具有此端點的連接器代表聯結，該聯結在這端只有一個值。  | 
|  ![\[一端有十字和圓圈的線條影像。\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/images/datafeeds-zero-or-one-to-n.png)  |   **零或一到 n** – 具有此端點的連接器表示聯結，聯結的此端具有零或一個值。  | 
|  ![\[一端有圓圈和叉的線條影像。\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/images/datafeeds-optional-many-to-n.png)  |   **零或大於 n** – 具有此端點的連接器代表聯結，此端具有零、一或多個值。  | 
|  ![\[一端有十字叉的線條影像。\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/images/datafeeds-one-or-more-to-n.png)  |   **一或多個 n** - 具有此端的連接器代表聯結，而聯結的此端具有一或多個值。  | 

# 資料饋送查詢範例
<a name="data-feed-full-examples"></a>

本節提供使用 提供之資料饋送的複雜查詢範例 AWS Marketplace。這些範例類似於[賣方儀表板](dashboards.md)您從 取得的 AWS Marketplace 管理入口網站。您可以自訂這些查詢，以建立您需要的其他報告。

**Topics**
+ [協議和續約](#data-feed-example-agreements)
+ [帳單收入](#data-feed-example-billed-revenue)
+ [未收集或已分配的發票](#data-feed-example-collections)
+ [已稅發票](#data-feed-example-tax)
+ [依產品分配](#data-feed-example-disbursement-by-product)
+ [銷售補償報告](#data-feed-example-sales-compensation)

## 協議和續約
<a name="data-feed-example-agreements"></a>

若要尋找您的協議和續約資料，您可以執行一組查詢，如下列範例所示。查詢會彼此建置，以建立**協議和續約**儀表板、精細資料區段。您可以使用範例，如下所示，或針對您的資料和使用案例進行自訂。

查詢中的註解說明查詢的功能，以及如何修改它們。

```
      Query currently under development.
```

## 帳單收入
<a name="data-feed-example-billed-revenue"></a>

若要尋找發票資料，您可以執行一組查詢，如下列範例所示。查詢會彼此建置，以建立**計費營收**報告。您可以使用範例，如下所示，或針對您的資料和使用案例進行自訂。

查詢中的註解說明查詢的功能，以及如何修改它們。

```
-- 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)
```

## 未收集或已分配的發票
<a name="data-feed-example-collections"></a>

若要尋找未收集或已分配的發票，您可以執行一組查詢，如下列範例所示。查詢會彼此建置，以建立**集合和支出**報告。您可以使用範例，如下所示，或針對您的資料和使用案例進行自訂。

查詢中的註解說明查詢的功能，以及如何修改它們。

```
-- 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)
```

## 已稅發票
<a name="data-feed-example-tax"></a>

若要尋找您的稅金發票，您可以執行一組查詢，如下列範例所示。這些查詢會彼此建置，以建立**稅務**報告。您可以使用範例，如下所示，或針對您的資料和使用案例進行自訂。

查詢中的註解說明查詢的功能，以及如何修改它們。

```
-- 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)
```

## 依產品分配
<a name="data-feed-example-disbursement-by-product"></a>

若要尋找產品分配的金額，您可以執行一組如下所示的查詢。

這些範例查詢會彼此建置，以建立具有支出的產品詳細資訊最終清單。它也會說明如何在特定時間點取得產品資訊。您可以使用此範例，如下所示，或針對您的資料和使用案例進行自訂。

查詢中的註解說明查詢的功能，以及如何修改它們。

**注意**  
執行此查詢時，我們假設使用兩個時間軸 `valid_from`和 `update`資料欄來擷取資料。如需軸的詳細資訊，請參閱 [AWS Marketplace 資料饋送的儲存和結構](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;
```

## 銷售補償報告
<a name="data-feed-example-sales-compensation"></a>

若要依客戶尋找計費收入，您可以執行一組類似以下的查詢。這些範例查詢會彼此建置，以建立客戶詳細資訊的最終清單，其中包含針對軟體用量向每位客戶計費的總金額。您可以使用如下所示的查詢，或為您的資料和使用案例自訂查詢。

 查詢中的註解說明查詢的功能，以及如何修改它們。

**注意**  
執行此查詢時，我們假設擷取的資料使用兩個時間軸 ( `valid_from`和 `update`欄）。如需詳細資訊，請參閱[AWS Marketplace 資料饋送的儲存和結構](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;
```

# 資料饋送
<a name="data-feeds"></a>

AWS Marketplace 提供多種資料摘要，以協助賣方收集和分析產品銷售的相關資訊。資料摘要可供所有已註冊的 AWS Marketplace 賣方使用。由於資料饋送是在一天內產生，因此它們包含最新的可用資料。

下列各節說明每個資料饋送。

**Topics**
+ [帳戶資料饋送](data-feed-account.md)
+ [位址資料饋送](data-feed-address.md)
+ [協議資料饋送](data-feed-agreements.md)
+ [協議費用資料饋送](data-feed-agreement-charge.md)
+ [協議期限資料饋送](data-feed-agreement-term.md)
+ [帳單事件資料摘要](data-feed-billing-event.md)
+ [每日用量資料摘要](data-feed-daily-usage.md)
+ [舊版映射資料饋送](data-feed-legacy-mapping.md)
+ [提供資料饋送](data-feed-offer.md)
+ [提供產品資料饋送](data-feed-offer-product.md)
+ [提供目標資料饋送](data-feed-offer-target.md)
+ [產品資料饋送](data-feed-product.md)
+ [稅務項目資料饋送](data-feed-tax-item.md)

# 帳戶資料饋送
<a name="data-feed-account"></a>

此資料饋送提供您與之互動之所有帳戶的相關資訊：您自己、與您合作的任何通路合作夥伴、買家、付款人和所有稅務帳戶。

帳戶資料是不可變的，而且與版本號碼沒有關聯。系統會附加對欄位的變更，因此此資料饋送可能有數個具有相同值`account_id`和不同`valid_from`值的列。如需資料歷程記錄欄位的資訊，請參閱[資料的歷史化](data-feed-details.md#data-feed-historization)。

帳戶資料摘要每 24 小時會重新整理一次，因此每天都有新資料可用。

下表說明資料饋送欄的名稱和說明。


| 資料欄名稱  | 描述  | 
| --- | --- | 
| account\$1id  | 帳戶的全域唯一識別碼 (GUID)。可用於聯結至`Product`、`Offer_Target`、`Billing_Event`和`Tax_Item`資料饋送中的欄位。如需可用於聯結之欄位的相關資訊，請參閱這些資料摘要。 | 
| aws\$1account\$1id  | 賣家 AWS 帳戶的帳 AWS 戶號碼，在 AWS 分區中是唯一的。 | 
| 加密帳號 | 您應用程式個別購買者的唯一加密 ID。的值encrypted\$1account\$1id是由 AWS Marketplace 計量服務使用，例如，作為CustomerIdentifier[https://docs.aws.amazon.com/marketplacemetering/latest/APIReference/API_ResolveCustomer.html](https://docs.aws.amazon.com/marketplacemetering/latest/APIReference/API_ResolveCustomer.html)動作所傳回的值。 | 
| 郵件地址 ID | 此帳戶的郵寄地址參照。 | 
| 地址識別碼 | 此帳戶的稅捐地址參考。 | 
| 稅務登記號 | 對於非美國帳戶，則為此帳戶的稅務登記號碼。 | 
| 稅務法律名稱 | 對於非美國帳戶，則為合法公司名稱。這是稅捐商業發票上使用的名稱。 | 

## 帳戶資料饋送範例
<a name="data-feed-account-sample-data"></a>

以下顯示帳戶資料饋送的範例。為了便於閱讀，不會顯示數據歷史記錄列。如需資料歷程記錄欄位的資訊，請參閱[資料的歷史化](data-feed-details.md#data-feed-historization)。


| account\$1id  | aws\$1account\$1id  | 加密帳號 | 郵件地址 ID | 地址識別碼 | 稅務登記號 | 稅務法律名稱 | 
| --- | --- | --- | --- | --- | --- | --- | 
| XK0 CSmiAm6PQ4QqEog9iiaochIzuPlkMfba7a1oDlZ | 444456660000 | ZF7 oMzhe GWpH | 25O3K46EN6 斐濟克斯 8E3 eViOf kaOiPal UiofjyFa |  |  |  | 
| 7 年 5 聚合物 81Vx9ji04 O1FF8 88W8 jwTRo eEwTur biQi | 555567679999 | 373 Q8V vuQUqm | 5OJ6 RF2GVH2VJ9HFQIM800M vTjSz uLEHmy FY5Lr42s8 | 5OJ6 RF2GVH2VJ9HFQIM800M vTjSz uLEHmy FY5Lr42s8 | SE823935083345 |  | 
| VIeGa2t9j3MuxioH9wc8lsndXXCgGCGUreeXriocM5 | 73739998888 | 8 SPxAYmi8MwX | NLUc5UeiMlGFTrDWCoftDPhDUF1oaSd8xgl5QM8Db7 | \$1\$1\$1 瑜格威 0 hBYBi WMhndGU4AfMggmuoTC2j7Pm8ZKKNNyT | DE469558025 |  | 

# 位址資料饋送
<a name="data-feed-address"></a>

**重要**  
AWS Marketplace 將於 2023 年 12 月終止使用[賣家交付資料饋送服務交付的 PiFe](https://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-service.html) ed。使用 AddressFeed \$1V1 資料饋送來滿足您的地址資料需求。如果您有任何問題或需要支援，請聯絡[AWS Marketplace 賣家營運](https://aws.amazon.com/marketplace/management/contact-us/)團隊。

此資料饋送會為您與之互動的所有帳戶提供聯絡資訊：您自己、與您合作的任何通路合作夥伴、買家、付款人和所有稅務帳戶。每次發生新交易時，都會掃描交易的客戶地址，如果該地址不在您的資料饋送中，則會在資料饋送檔案中新增一個新項目。

地址數據是不可變的。

地址資料饋送會每 24 小時重新整理一次，因此每天都有新資料可用。

下表說明資料饋送欄的名稱和說明。


| 資料欄名稱  | 描述  | 
| --- | --- | 
| 位址識別碼  | 地址的唯一密鑰。可用於從欄位上的`Billing_Event`資料饋送或從`mailing_address_id`或`tax_address_id`欄位上的`Account`資料饋送聯結。`billing_address_id` | 
| 電子郵件網域  | 此帳戶所記錄之電子郵件地址的網域。 | 
| company\$1name  | 此帳戶登記的公司名稱。 | 
| 國家代碼 | 這個地址的 ISO 3166 英文字母 2 國家/地區代碼。 | 
| 州或地區  | 此地址登記的州或地區。 | 
| 城市  | 這個地址登記的城市。 | 
| 郵政編碼  | 此地址的檔案中的郵遞區號。 | 
| 地址行 \$11  | 這個地址的第一行地址。 | 
| 地址行 2  | 此地址登記的第二行地址。 | 
| 地址列 \$13  | 這個地址登記的地址的第三行。 | 

## 位址資料饋送範例
<a name="data-feed-address-sample-data"></a>

以下顯示位址資料饋送的範例。在資料摘要中，此資訊會顯示在單一表格中。為了便於閱讀，這裡的數據顯示在兩個表中，並且不顯示數據歷史記錄列。如需資料歷程記錄欄位的資訊，請參閱[資料的歷史化](data-feed-details.md#data-feed-historization)。


| 位址識別碼  | 電子郵件網域  | company\$1name  | 國家代碼  | 州或地區  | 城市  | 郵政編碼  | 
| --- | --- | --- | --- | --- | --- | --- | 
| V5nHby 0WMHNDGU4 TC2J7 PM 8 克里尼 BiYogwy AfMggmuo | 一個 .com | 马特奥·杰克逊公司 | DE |  | 漢堡 | 67568 | 
| G68xDB 頻率 DVHZF WGW6YF5 YOS0A6 尼斯夫 Wh5 ViLjf | B.com | 瑪麗大公司 | 美國 | OH | 代頓 | 57684 | 
| NLUC5 GFTRW 福特夫特波多夫 1OASD8xgl5QM8db7 UeiMl | C.com | 我們的賣家 | 美國 | NY | 紐約 | 89475 | 




| 地址行 \$11  | 地址行 2  | 地址列 \$13  | 
| --- | --- | --- | 
|   |   |  | 
|  |   |  | 
|  | 十九樓 |  | 



# 協議資料饋送
<a name="data-feed-agreements"></a>

此資料摘要提供協議的相關資訊，這是提案者 （記錄賣方） 與接受者 (AWS 買方） 之間簽訂的合約，以開始使用產品。此資料摘要提供您所建立做為記錄賣方之產品優惠協議的相關資訊。

協議資料饋送每 24 小時重新整理一次。

下表列出並說明資料饋送中的項目。


| 資料行 | Description | 
| --- | --- | 
| valid\$1from | 相對於其他欄位的值，主索引鍵值對 有效的第一個日期。 | 
| insert\$1date | 將記錄插入資料饋送的日期。 | 
| update\$1date | 記錄上次更新的日期。 | 
| delete\$1date | 此欄一律為空白。 | 
| 狀態 |  協議的目前狀態。支援的狀態包括： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-agreements.html)  | 
| estimated\$1agreement\$1value | 客戶在協議生命週期內可能支付的已知金額總計。 | 
| currency\$1code | 客戶可能在協議生命週期內支付的總已知金額貨幣。 | 
| agreement\$1id | 協議的唯一識別符。 | 
| license\$1ids | 與協議相關聯的授權識別符，以陣列表示。 | 
| 提案者\$1帳戶\$1id | 提議此 PurchaseAgreement 的賣方，以賣方帳戶的全域唯一識別碼 (GUID) 表示。可用於加入帳戶資料饋送。 | 
| acceptor\$1account\$1id | 接受此 PurchaseAgreement 的買方，以買方帳戶的全域唯一識別碼 (GUID) 表示。可用於加入帳戶資料饋送。 | 
| offer\$1revision\$1at\$1acceptance | 對應至本合約之優惠的易記 ID。可用於加入優惠和優惠目標資料饋送。 | 
| offer\$1set\$1id | 與優惠相關聯的優惠集識別符。 | 
| start\$1time | 協議開始的日期和時間。 | 
| end\$1time | 協議結束的日期和時間。pay-as-you-go協議的 欄位為 null，沒有結束日期。 | 
| acceptance\$1time | 接受優惠或建立協議的日期和時間。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-agreements.html)  | 
| intent | 上次修改協議時的買方意圖。 | 
| preceding\$1agreement\$1id | 先前協議的協議 ID。 | 
| status\$1reason\$1code | 協議狀態變更的原因。 | 
| recipient\$1account\$1id | 接收摘要中資料的賣方帳戶。可用於加入 `account_id` 欄位上的`Account`資料饋送。 | 

## 協議資料饋送範例
<a name="agreements-feed-example"></a>


| valid\$1from | agreement\$1id | 提案者\$1帳戶\$1id | acceptor\$1account\$1id | offer\$1id | offer\$1revision\$1at\$1acceptance | offer\$1set\$1id | start\$1time | end\$1time | acceptancet\$1time | intent | preceding\$1agreement\$1id | status | status\$1reason\$1code | estimated\$1agreement\$1value | currency\$1code | 
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | 
| 2024-06-21 20：58：00.0 | agmt-34g544dfgsd5678adsrgwe5t | 88a3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 88a3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | offer-krxxxxxxxxxxx | 1 | offeret-yricpu74oqox2 | 2024-06-21 20：58：00.0 | 2025-06-21 20：58：00.0 | 2024-06-21 20：58：00.0 | 新 |  | ACTIVE |  | 1,000 | 美元 | 

# 協議費用資料饋送
<a name="data-feed-agreement-charge"></a>

此資料摘要提供與您以記錄賣方身分建立之協議相關聯的費用概觀。此資料饋送目前僅提供與付款排程期間相關聯的協議費用資訊。

協議費用資料饋送每 24 小時重新整理一次。

下表列出並說明資料饋送中的項目。


| 資料行 | Description | 
| --- | --- | 
| valid\$1from | 相對於其他欄位的值，主索引鍵值對 有效的第一個日期。 | 
| insert\$1date | 將記錄插入資料饋送的日期。 | 
| update\$1date | 記錄上次更新的日期。 | 
| delete\$1date | 此欄一律為空白。 | 
| amount | 要收取的金額。 | 
| currency\$1code | 付款的定價貨幣。 | 
| time | 計劃分期的排程收費日期。 | 
| agreement\$1id | 協議的唯一識別符。 | 
| term\$1id | 該字詞的唯一識別符。 | 

## 協議費用資料饋送範例
<a name="agreement-charge-feed-example"></a>


| valid\$1from | insert\$1date | update\$1date | delete\$1date | amount | currency\$1code | time | agreement\$1id | term\$1id | 
| --- | --- | --- | --- | --- | --- | --- | --- | --- | 
| 2024-12-24 11：31：47.0 | 2025-10-01 01：03：41.0 | 2025-10-01 01：03：41.0 | null | 28440.00 | 美元 | 2025-11-06 00：00：00.00000 | agmt-3kk39tbw3j6id2vakbp0XXXXX | term-3986e2c7f73768ed4ff7cd8a97b41ac0ae2aa02ada6b68deb9349c8604cXXXXX | 

# 協議期限資料饋送
<a name="data-feed-agreement-term"></a>

此資料摘要提供與您以記錄賣方身分建立之協議相關聯的協議期限概觀。

協議期限資料饋送每 24 小時重新整理一次。

下表列出並說明資料饋送中的項目。


| 資料行 | Description | 
| --- | --- | 
| valid\$1from | 相對於其他欄位的值，主索引鍵值對 有效的第一個日期。 | 
| insert\$1date | 將記錄插入資料饋送的日期。 | 
| update\$1date | 記錄上次更新的日期。 | 
| delete\$1date | 記錄刪除的日期。 | 
| agreement\$1id | 協議的唯一識別符。 | 
| term\$1id | 字詞的唯一識別符。 | 
| term\$1type | 與協議相關聯的詞彙類型。如需可用詞彙的完整清單，請參閱 https://docs.aws.amazon.com/marketplace/latest/APIReference/API\$1Types\$1AWS\$1Marketplace\$1Agreement\$1Service.html：//。 | 
| term\$1configuration | 接受字詞時接受者指定的其他參數。這適用於 ConfigurableUpfrontPricingTerms 和 RenewalTerms。 | 

## 協議期限資料饋送範例
<a name="agreement-term-feed-example"></a>


| valid\$1from | insert\$1date | update\$1date | delete\$1date | agreement\$1id | term\$1id | term\$1type | term\$1configuration | 
| --- | --- | --- | --- | --- | --- | --- | --- | 
| 2024-12-24 11：31：47.0 | 2025-10-01 01：03：41.0 | 2025-10-01 01：03：41.0 | null | agmt-3kk39tbw3j6id2vakbp0XXXXX | term-3986e2c7f73768ed4ff7cd8a97b41ac0ae2aa02ada6b68deb9349c8604cXXXXX | ConfigurableUpfrontPricingTerm | \$1"selectorValue"："P36M"，"dimensions"：【\$1"dimensionKey"："Applications"，"dimensionValue"：1\$1】\$1 | 

# 帳單事件資料摘要
<a name="data-feed-billing-event"></a>

此資料摘要提供帳單事件的相關資訊，包括開立發票和支出。

例如，您可以使用此資料摘要來了解買方開立發票的時間和項目。您也可以使用[範例 SQL 查詢](#data-feeds-billing-event-query-examples)來分析來自此資料饋送的資料。

此資料摘要包含與您是記錄賣方的帳單事件相關的資訊。對於透過管道合作夥伴所簽訂的協議，此資料饋送包含製造商與記錄賣方之間帳單事件的相關資訊。

帳單事件資料饋送每 24 小時重新整理一次，因此新資料每天可用。

帳單事件資料不可變。

下表說明資料饋送資料欄的名稱和描述。


| 欄名稱  | Description  | 
| --- | --- | 
| billing\$1event\$1id | 帳單事件的識別符。此 ID 在賣方環境中是唯一的。 | 
| from\$1account\$1id |  啟動帳單事件的帳戶。如果 `transaction_type`是 `SELLER_REV_SHARE`，則它是買方的付款人帳戶。這是[帳戶](data-feed-account.md)資料饋送的外部金鑰。 可用於加入 `account_id` 欄位上的`Account`資料饋送。  | 
| to\$1account\$1id | 接收產品交易金額的帳戶。這是帳戶資料饋送的外部金鑰。可用於加入 `account_id` 欄位上的`Account`資料饋送。 | 
| end\$1user\$1account\$1id | 使用產品的 帳戶。此帳戶可能與買方和付款人帳戶不同。可用於加入 `account_id` 欄位上的`Account`資料饋送。 | 
| product\$1id | 產品的識別符。這是[產品](data-feed-product.md)資料饋送的外部金鑰。可用於加入 `product_id` 欄位上的`Product`資料饋送。 | 
| 動作 |  此事件的動作類型。可能的值如下： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-billing-event.html)  | 
| transaction\$1type |  交易的類型。如需範例，請參閱 [稅務案例](#data-feeds-billing-event-tax-examples)。可能的值如下： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-billing-event.html)  | 
| parent\$1billing\$1event\$1id |  當 的值`broker_id`為 時`AWS_INC`， 的值`action`為 `DISBURSED`或 `FORGIVEN`，而 的值`transaction_type`為 時`DISBURSEMENT`， `parent_billing_event_id`是指起始此帳單事件`billing_event_id`的原始 。如果 `action`有另一個值，則此欄位為 null。 當 的值`broker_id`為 時`AWS_EUROPE`， `parent_billing_event_id`是指在下列情況下`billing_event_id`啟動此帳單事件的原始 ： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-billing-event.html) 當 的值`broker_id`為 時`AWS_EUROPE`， `parent_billing_event_id`是指下列情況下先前成功支付帳單事件`billing_event_id`的原始 ： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-billing-event.html) 當 的值`broker_id`為 時`AWS_EUROPE`，此欄位在所有剩餘的案例中皆為 null。  | 
| disbursement\$1billing\$1event\$1id |  當 的值`action`為 `DISBURSED`且下列其中一項為 true 時，相關的支出： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-billing-event.html) 在所有其他情況下，此值為 null。  | 
| amount | 帳單事件金額。 | 
| currency | ISO 639 貨幣代碼。 | 
| disbursement\$1amount | 支出金額。對於雙發票案例，此欄位包含支出金額。對於統一發票案例，此欄位為 null。 | 
| disbursement\$1currency | 支出金額的 ISO 639 貨幣代碼。對於雙發票案例，此欄位包含支出貨幣代碼。對於統一發票案例，此欄位為 null。 | 
| balance\$1impacting | 在計算賣方支出時，是否會考慮金額。值 0 表示金額僅供參考，對餘額沒有影響。值 1 表示此金額在決定賣方支出時已納入考量。 | 
| invoice\$1date | 發票建立的日期。 | 
| payment\$1due\$1date |  當 的值`action`為 時`INVOICED`，發票的到期日。  | 
| usage\$1period\$1start\$1date | 記錄中期間的開始日期。 | 
| usage\$1period\$1end\$1date | 記錄中期間的結束日期。 | 
| invoice\$1id |   AWS 發票 ID。  | 
| billing\$1address\$1id | 地址資料摘要中的付款人帳單地址參考。可用於加入 `address_id` 欄位上的`Address`資料饋送。 | 
| transaction\$1reference\$1id |  可讓您從下列報告交叉參考資料的識別符： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-billing-event.html)  | 
| bank\$1trace\$1id |  對於支出交易 (`transaction_type` =is `DISBURSEMENT`且動作為 `DISBURSED`)，由銀行指派的追蹤 ID。追蹤 ID 可用來與賣方銀行提供的報告建立關聯。  | 
| broker\$1id |  促進交易之商業實體的識別符。可能的值如下： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-billing-event.html)  | 
| buyer\$1transaction\$1reference\$1id |  使用 `GROUP BY`或 SQL 中的視窗函數建構，將帳單摘要中所有相關記錄分組在一起的識別符。這些相關記錄可包含買方發票、賣方發票，以及列出費用的增值稅 VATs)。  | 
| action\$1date |  交易事件發生的日期。特定日期取決於動作類型： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-billing-event.html)  | 
| recipient\$1account\$1id | 接收摘要中資料的賣方帳戶。可用於加入 `account_id` 欄位上的`Account`資料饋送。 | 
| offer\$1id | 與買方發票明細項目相關聯的優惠的易記 ID。可用於加入 `offer_id` 欄位上的`Offer`資料饋送。 | 
| buyer\$1invoice\$1date | 相關聯買方發票明細項目的發票日期。 | 
| line\$1item\$1id | 發票明細項目 ID，以支付精細程度表示。 | 
| buyer\$1line\$1item\$1id | 支付精細度的買方發票明細項目 ID。 | 
| charge\$1variant | 指出正在購買或銷售的項目。 | 
| charge\$1side | 指出賣方是購買還是銷售費用變體。 | 

## 稅務案例
<a name="data-feeds-billing-event-tax-examples"></a>

適用於買方和賣方國家和州的稅務模型會決定如何收集和匯出稅金。以下是可能的情況：
+ 稅金由 收取和傳送 AWS。在這些情況下， `transaction_type`是 `AWS_TAX_SHARE`。
+ 稅務由賣方收取 AWS、支付給賣方，並由賣方向稅務機關提交。在這些情況下， `transaction_type`是 `SELLER_TAX_SHARE`。
+ 稅金不會由 收取 AWS。賣方必須計算稅金並將其匯給稅務機關。在這些情況下， AWS Marketplace 不會執行稅務計算或接收稅務資訊。賣方會支付收入份額的稅金。

## 帳單事件資料饋送的範例
<a name="data-feed-billing-event-sample-scenario"></a>

本節顯示開立發票時和一個月後帳單事件資料期間的範例。請注意本節中所有資料表的下列項目：
+ 在資料饋送中，`billing_event_id`值為 40 個字元的英數字串。此處顯示為兩個字元字串，以供讀取。
+ 在資料饋送中，此資訊會以單一資料表顯示。為了便於閱讀，資料會顯示在此處的多個資料表中，而且不會顯示所有資料欄。

如需本節中的範例，請假設下列事項：
+ Arnav 是買方。
  + 他的帳戶 ID 是 `737399998888`。
  + 他位於法國，受到市場協調人員法律的約束。如需詳細資訊，請參閱 [Amazon Web Service Tax Help](https://aws.amazon.com/tax-help/)。
  + 他購買`prod-o4grxfafcxxxx`該產品，並按每月用量開立 120.60 美元的發票。
  + 他在該月內支付了發票。
+ Jane 是製造商。
  + 她的帳戶 ID 是 `111122223333`。
+ Paulo 是記錄的賣方。
  + 他的帳戶 ID 是 `777788889999`。
  + 他住在堪薩斯，不受市場促進者法律的約束。

### 記錄賣方的帳單事件資料摘要
<a name="billing-event-example-seller-of-record"></a>

身為記錄賣方，Paulo 會向買方 Arnav 開立發票。

下表顯示 Paulo 在開立 Arnav 發票時，資料摘要中的相關資訊。


| billing\$1event\$1id  | from\$1account\$1id  | to\$1account\$1id  | end\$1user\$1account\$1id | product\$1id | 動作 | transaction\$1type | 
| --- | --- | --- | --- | --- | --- | --- | 
| I0 | 737399998888 | 777788889999 | 737399998888 | prod-o4grxfafcxxxx | 已開立發票 | SELLER\$1REV\$1SHARE | 
| I1 | 737399998888 | AWS | 737399998888 | prod-o4grxfafcxxxx | 已開立發票 | AWS\$1TAX\$1SHARE | 
| I2 | 777788889999 | 111122223333 | 737399998888 | prod-o4grxfafcxxxx | 已開立發票 | SELLER\$1REV\$1SHARE | 
| I3 | 777788889999 | AWS | 737399998888 | prod-o4grxfafcxxxx | 已開立發票 | AWS\$1REV\$1SHARE | 




| parent\$1billing\$1event\$1id | disbursement\$1billing\$1event\$1id | amount | currency | invoice\$1date | invoice\$1id | 
| --- | --- | --- | --- | --- | --- | 
|  |  | 100 | 美元 | 2018-12-31T00：00：00Z | 781216640 | 
|  |  | 20.6 | 美元 | 2018-12-31T00：00：00Z | 781216640 | 
|  |  | -80 | 美元 | 2018-12-31T00：04：07Z | 788576665 | 
|  |  | -0.2 | 美元 | 2018-12-31T00：04：07Z | 788576665 | 

下表顯示在 Arnav 支付發票之後，月底 Paulo 資料饋送中的相關資訊。


| billing\$1event\$1id  | from\$1account\$1id  | to\$1account\$1id  | end\$1user\$1account\$1id | product\$1id | 動作 | transaction\$1type | 
| --- | --- | --- | --- | --- | --- | --- | 
| I10 | 737399998888 | 777788889999 | 737399998888 |  | 分發 | SELLER\$1REV\$1SHARE | 
| I12 | 777788889999 | 111122223333 | 737399998888 |  | 分發 | SELLER\$1REV\$1SHARE | 
| I13 | 777788889999 | AWS | 737399998888 | prod-o4grxfafcxxxx | 分發 | AWS\$1REV\$1SHARE | 
| I14 | AWS | 777788889999 |  |  | 分發 | 支出 | 




| parent\$1billing\$1event\$1id | disbursement\$1billing\$1event\$1id | amount | currency | invoice\$1date | invoice\$1id | 
| --- | --- | --- | --- | --- | --- | 
| I0 | I14 | -100 | 美元 | 2018-12-31T00：00：00Z | 781216640 | 
| I2 | I14 | 80 | 美元 | 2018-12-31T00：04：07Z | 788576665 | 
| I3 | I14 | 0.2 | 美元 | 2018-12-31T00：04：07Z | 788576665 | 
|  |  | 19.8 | 美元 |  |  | 

### 製造商的帳單事件資料摘要
<a name="billing-event-example-manufacturer"></a>

下表顯示 Paulo 發票 Arnav 時 Jane 資料饋送中的相關資訊。


| billing\$1event\$1id  | from\$1account\$1id  | to\$1account\$1id  | end\$1user\$1account\$1id | product\$1id | 動作 | transaction\$1type | 
| --- | --- | --- | --- | --- | --- | --- | 
| I5 | 777788889999 | 111122223333 |  | prod-o4grxfafcxxxx | 已開立發票 | SELLER\$1REV\$1SHARE | 
| I6 | 777788889999 | 111122223333 |  | prod-o4grxfafcxxxx | 已開立發票 | SELLER\$1TAX\$1SHARE | 
| I7 | 111122223333 | AWS |  | prod-o4grxfafcxxxx | 已開立發票 | AWS\$1REV\$1SHARE | 




| parent\$1billing\$1event\$1id | disbursement\$1billing\$1event\$1id | amount | currency | 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 | 

下表顯示在支付發票後，月底 Jane 資料饋送中的相關資訊。


| billing\$1event\$1id  | from\$1account\$1id  | to\$1account\$1id  | end\$1user\$1account\$1id | product\$1id | 動作 | transaction\$1type | 
| --- | --- | --- | --- | --- | --- | --- | 
| I30 | 777788889999 | 111122223333 |  | prod-o4grxfafcxxxx | 分發 | SELLER\$1REV\$1SHARE | 
| I31 | 777788889999 | 111122223333 |  | prod-o4grxfafcxxxx | 分發 | SELLER\$1TAX\$1SHARE | 
| I32 | 111122223333 | AWS |  | prod-o4grxfafcxxxx | 分發 | AWS\$1REV\$1SHARE | 
| I33 | AWS | 111122223333 |  |  | 分發 | 支出 | 




| parent\$1billing\$1event\$1id | disbursement\$1billing\$1event\$1id | amount | currency | invoice\$1date | invoice\$1id | 
| --- | --- | --- | --- | --- | --- | 
| I5 | I33 | -73.5 | 美元 |  |  | 
| I6 | I33 | -6.5 | 美元 |  |  | 
| I7 | I33 | 7.35 | 美元 |  |  | 
|  |  | 72.65 | 美元 |  |  | 

## 查詢範例
<a name="data-feeds-billing-event-query-examples"></a>

如 中所述[使用資料饋送收集和分析資料](data-feed-using.md)，您可以使用 [ Athena](https://docs.aws.amazon.com/athena/latest/ug/what-is.html) 對在受管 Amazon S3 儲存貯體中收集並儲存為資料饋送的資料執行查詢。本節提供一些您可能這麼做的常見方式範例。所有範例都假設使用單一貨幣。

## 範例 1：發票金額，包括稅金
<a name="data-feed-example-query-tax-invoice"></a>

若要了解已開立多少買方發票，包括稅金，您可以執行查詢，如下列範例所示。

```
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'
  );
```

## 範例 2：代表賣方向買方開立發票的金額
<a name="data-feed-example-query-invoice-for-seller"></a>

若要了解代表賣方開立多少買方發票，您可以執行查詢，如下列範例所示。

```
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'
;
```

## 範例 3： AWS 可以代表賣方收取的金額
<a name="data-feed-example-query-aws-collect"></a>

若要了解 AWS 可以代表賣方收取多少金額，減去任何退款、抵用金和寬容帳戶，您可以執行查詢，如下列範例所示。

```
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') 
;
```

## 範例 4：賣方可以收集的金額
<a name="data-feed-example-query-seller-collect"></a>

若要了解賣方可以收集多少，您可以執行查詢，如下列範例所示。此範例會移除 AWS 收集的清單費用和稅金，並新增任何例外餘額調整。

```
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')
;
```

您也可以使用下列查詢來收集相同的資訊，如下列範例所示。

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

下列範例顯示相同的資訊，但僅限於 2018 年交易，並假設所有買方都已支付其發票。

```
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')
;
```

## 範例 5：支出金額
<a name="data-feed-example-query-disbursements"></a>

若要了解已分配的金額，您可以執行查詢，如下列範例所示。

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

## 範例 6：待支付的金額
<a name="data-feed-example-query-pending-disbursement"></a>

若要了解待支付的金額，您可以執行查詢，如下列範例所示。此查詢會移除已分配的金額。

```
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
      )
    ) 
;
```

取得相同資訊的另一個方法是執行查詢以取得賣方的餘額，如下列範例所示。

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

下列查詢延伸了我們的範例。它會將結果限制為 2018 年交易，並傳回交易的其他詳細資訊。

```
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
);
```

## 範例 7：發票集的餘額
<a name="data-feed-example-query-balance-invoice-set"></a>

若要了解一組發票的總和，您可以執行查詢，如下列範例所示。

```
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;
```

# 每日用量資料摘要
<a name="data-feed-daily-usage"></a>

此資料摘要為您的產品提供詳細的每日用量資訊，包括客戶用量指標、預估收入和定價詳細資訊。資料可協助您追蹤客戶如何使用您的產品，並根據用量模式計算預估收入。

每日用量資料饋送每 24 小時重新整理一次。

下表列出並說明資料饋送中的項目。


| 資料行 | Description | 
| --- | --- | 
| valid\$1from | 相對於其他欄位的值，主索引鍵值對 有效的第一個日期。 | 
| insert\$1date | 將記錄插入資料饋送的日期。 | 
| update\$1date | 記錄上次更新的日期。 | 
| delete\$1date | 記錄軟刪除的日期。 | 
| usage\$1feed\$1id | 用量記錄的唯一識別符。這是每個賣方的加鹽雜湊，可確保資料隱私權，同時保持記錄唯一性。 | 
| usage\$1date | 客戶使用日期，不含時間元件。因為每天都會彙總用量，所以省略時間。 | 
| product\$1id | 產品的易記 ID。可用於加入帳戶、Billing\$1Event 和 Offer\$1Product 資料摘要`product_id`的欄位。 | 
| agreement\$1id | 協議的唯一識別符。如果存在，一律以 開頭`agmnt-`。此欄位有時可能為 null 或不符合對應的發票明細項目。 | 
| end\$1user\$1account\$1id | 實際使用產品的帳戶，以最終使用者帳戶的全域唯一識別碼 (GUID) 表示。可用於加入帳戶資料饋送。這是每個賣方的加鹽雜湊，而不是原始 AWS 帳戶 ID。 | 
| payer\$1account\$1id | 預期支付產品用量的帳戶，以付款人帳戶的全域唯一識別碼 (GUID) 表示。可用於加入帳戶資料饋送。這是每個賣方的加鹽雜湊，而不是原始 AWS 帳戶 ID。 | 
| region | 買方使用發生 AWS 的區域。 | 
| dimension\$1key | 賣方在發佈優惠時設定的維度索引鍵。 | 
| usage\$1unit | 用量單位的分類，描述用量測量的類型。 | 
| usage\$1quantity | 與帳單記錄相關聯的 AWS 服務或用量類型的用量值。此值提供最多 2 個小數位數。 | 
| pricing\$1currency | 預估收入的貨幣。 | 
| estimated\$1revenue\$1in\$1pricing\$1currency | 使用 `usage_rate_per_unit`和 計算的預估收入`usage_quantity`。此值提供最多 2 個小數位數。對於經銷商提供的用量 ISV 檢視，這需要乘以收入共享百分比。 | 
| recipient\$1account\$1id | 接收摘要中資料的賣方帳戶。 | 
| offer\$1id | 優惠的易記 ID。如果可用，這將與購買協議的 offer\$1id 相符。可用於加入優惠和優惠目標資料摘要。為了一致性，此欄位一律為非空。 | 
| usage\$1rate\$1per\$1unit\$1in\$1pricing\$1currency | 每單位的使用率，可乘以 `usage_quantity`來驗證 `estimated_revenue_in_pricing_currency`。此值提供最多 6 個小數位數。 | 
| charge\$1item\$1description | 完整的費用項目描述，通常格式化為以用量為基礎的費用的 offer\$1term\$1description\$1region\$1dimension\$1description。 | 

## 每日用量資料饋送範例
<a name="daily-usage-feed-example"></a>

以下顯示具有索引鍵資料欄的每日用量資料饋送範例。為了便於閱讀，不會顯示某些資料欄。


| usage\$1date | product\$1id | agreement\$1id | region | dimension\$1key | usage\$1unit | usage\$1quantity | pricing\$1currency | estimated\$1revenue\$1in\$1pricing\$1currency | offer\$1id | usage\$1rate\$1per\$1unit\$1in\$1pricing\$1currency | charge\$1item\$1description | 
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | 
| 2025-01-15 | prod-abcd1234efgh5678 | agmnt-wxyz9876abcd5432 | us-east-1 | USE1\$1InputTokenCount | 單位 | 24.00 | 美元 | 12.00 | offer-mnop5432qrst7890 | 0.500000 | AWS Marketplace 軟體用量\$1us-east-1\$1Million Input Token | 
| 2025-01-15 | prod-ijkl9876mnop1234 | agmnt-stuv5432wxyz9876 | us-west-2 | USE1\$1InputTokenCount | 單位 | 1000.00 | 美元 | 5.50 | offer-abcd9876efgh5432 | 0.005500 | AWS Marketplace 軟體用量\$1us-west-2\$1API 呼叫 | 

# 舊版映射資料饋送
<a name="data-feed-legacy-mapping"></a>

此資料摘要會列出產品 IDs和提供 IDs如何對應至舊版全域唯一識別碼 (GUIDs)。舊版 GUIDs 用於較舊的報告，而新 IDs用於資料饋送和 AWS Marketplace APIs。

此資料摘要提供您所建立做為記錄賣方的所有產品，以及您授權轉售的所有產品的相關資訊。

舊版映射資料饋送每 24 小時重新整理一次，因此新的資料每天可用。

下表說明資料饋送資料欄的名稱和描述。


| 欄名稱  | Description  | 
| --- | --- | 
| mapping\$1type | 這是產品 ID 或優惠 ID。 | 
| legacy\$1id | 此產品或優惠的舊版 ID。 | 
| new\$1id | 此產品或優惠的易記 ID。此 ID 會用作主索引鍵，並搭配所有目前的 API 動作使用。 | 

## 舊版映射資料饋送的範例
<a name="data-feed-legacy-mapping-sample-data"></a>

以下顯示舊版映射資料饋送的範例。為了便於閱讀，不會顯示資料歷史記錄資料欄。如需資料歷史記錄欄位的資訊，請參閱 [資料的歷史化](data-feed-details.md#data-feed-historization)。


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



# 提供資料饋送
<a name="data-feed-offer"></a>

優惠資料摘要提供您已建立做為記錄賣方之所有優惠的相關資訊。如果單一優惠有多個修訂，則所有修訂都會包含在資料饋送中。

當您進行優惠修訂，且公開欄位中的資料變更時，會在相同主索引鍵的資料饋送中建立新的記錄 (`offer_id`加上 `offer_revision`)。不過， `valid_from` 欄位具有不同的值。如需資料饋送歷史記錄資料欄的詳細資訊，請參閱 [資料的歷史化](data-feed-details.md#data-feed-historization)。

優惠資料饋送每 24 小時重新整理一次，因此新資料每天可用。

下表提供資料饋送資料欄的名稱和描述。


| 欄名稱  | Description  | 
| --- | --- | 
| offer\$1id | 優惠的易記識別符。可用來加入`Offer_Product`資料饋送的 `offer_id` 欄位。 | 
| offer\$1revision | 優惠修訂。此欄位和 offer\$1id 欄位結合以形成主索引鍵。透過 `offer_id`， 可用來加入`Target_Offer`資料饋送的 `offer_id`和 `offer_revision` 欄位。 | 
| name | 優惠的賣方定義名稱。 | 
| expiration\$1date | 優惠到期的日期和時間。 | 
| opportunity\$1name | 連結至本優惠的任何機會資料。如果優惠繫結至機會 AWS ，則會填入此欄位。 | 
| opportunity\$1description | 連結至本優惠的任何描述性資訊。如果優惠繫結至機會 AWS ，則會填入此欄位。 | 
| seller\$1account\$1id | 賣方帳戶的全域唯一識別符 (GUID)。可用於與帳戶資料饋送中的 account\$1id 欄位聯結。 | 
| opportunity\$1id | 只有在經銷商銷售您的產品時，才會填入機會的識別符。opportunity\$1id 如果產品相同，則由不同通路合作夥伴 （或賣方） 建立的所有優惠都相同。 | 
| recipient\$1account\$1id | 接收摘要中資料的賣方帳戶。可用於加入 `account_id` 欄位上的`Account`資料饋送。 | 

## 優惠資料饋送範例
<a name="data-feed-offer-sample-data"></a>

以下顯示優惠資料饋送的範例。為了便於閱讀，不會顯示資料歷史記錄資料欄。如需資料歷史記錄欄位的資訊，請參閱 [資料的歷史化](data-feed-details.md#data-feed-historization)。


| offer\$1id  | offer\$1revision | name | expiration\$1date | opportunity\$1name | opportunity\$1description | seller\$1account\$1id | opportunity\$1id | 
| --- | --- | --- | --- | --- | --- | --- | --- | 
| offer-dacpxznflfwin | 1 | 企業合約計劃優惠 | 9999-01-01T00：00：00Z |  |  |  |  | 
| offer-gszhmle5npzip | 1 | 賣方建立的私有優惠 | 2020-10-31T00：00：00Z |  |  |  |  | 
| offer-hmzhyle8nphlp | 1 | 企業合約計劃優惠 | 9999-01-01T00：00：00Z |  |  |  |  | 

# 提供產品資料饋送
<a name="data-feed-offer-product"></a>

一個優惠可以有幾個產品，一個產品可以包含在不同的優惠中。此資料饋送會列出選件與產品之間關係的相關資訊。

此資料饋送會提供您作為記錄賣方建立之所有產品優惠的相關資訊。

當您在報價中新增或移除產品時，您可以建立選件版本。

選件產品資料饋送會每 24 小時重新整理一次，因此每天都有新資料可用。

下表說明資料饋送欄的名稱和說明。如需有關資料饋送歷史記錄欄的資訊，請參閱[資料的歷史化](data-feed-details.md#data-feed-historization)。


| 資料欄名稱  | 描述  | 
| --- | --- | 
| 報價識別碼 | 此優惠的易記識別碼。可用於聯結至資`Offer`料饋送的`offer_id`欄位。 | 
| 優惠修訂 | 與offer\$1id欄位結合以形成選件修訂的外部索引鍵。 | 
| product\$1id | 產品的易記識別碼，這是此優惠公開之產品的外部索引鍵。可用於聯結至資`Product`料饋送的`product_id`欄位。 | 

## 提供產品資料饋送範例
<a name="data-feed-offer-product-sample-data"></a>

以下顯示「提供產品資料饋送」的範例。


| 報價識別碼  | 優惠修訂 | product\$1id | 
| --- | --- | --- | 
| 優惠-達克斯恩弗溫 | 10 | 产品-o4 格拉法夫 XXXX | 
| 提供-吉斯姆勒 5 倍壓縮 | 24 | 項目-o4 格拉法克 XXXY | 

# 提供目標資料饋送
<a name="data-feed-offer-target"></a>

此資料饋送會針對您建立為記錄賣方的所有選件，列出選件修訂的目標。如果單一選件具有多個修訂，則所有修訂都會包含在資料饋送中。

當您進行選件修訂且公開欄位中的資料發生變更時，會在相同主索引鍵 (`offer_id`加號`offer_revision`) 的資料饋送中建立新記錄，但`valid_from`欄位的值不同。

選件目標資料饋送會每 24 小時重新整理一次，因此每天都有新資料可用。

下表說明資料饋送欄的名稱和說明。


| 資料欄名稱  | 描述  | 
| --- | --- | 
| 提供目標 ID | 摘要的主索引鍵。 | 
| 優惠 ID \$1 優惠修訂 | 選件的識別碼和修訂。這兩個資料欄會參考此目標相關的選件。可用來聯結至資`Target`料饋送的`offer_id`和`offer_revision`欄位。 | 
| 目標類型 | 指出選件收件者是否為BuyerAccounts，表示非公開選件或ParticipatingPrograms。 | 
| 極性 | 指出選件是否要對`target_type`。可接受的值如下： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-offer-target.html)  | 
| value | 代表目標的字串： AWS 帳戶 ID 或可與選件搭配使用的程式。例如，[AWS Marketplace （SCMP）的標準合約](standardized-license-terms.md#standard-contracts)，或[AWS Marketplace 現場演示程序（FDP）](field-demonstration-program.md)。 | 

## 選件目標資料饋送範例
<a name="data-feed-offer-target-sample-data"></a>

以下顯示選件目標資料饋送的範例。為了便於閱讀，不會顯示數據歷史記錄列。如需資料歷程記錄欄位的資訊，請參閱[資料的歷史化](data-feed-details.md#data-feed-historization)。


| 提供目標 ID  | 報價識別碼  | 優惠修訂 | 目標類型 | 極性 | value | 
| --- | --- | --- | --- | --- | --- | 
| 925ddc73f6a373b7d5544E3210610803b600 | 優惠-達克斯恩弗溫 | 1 | ParticipatingPrograms | PositiveTargeting | EnterpriseContract | 
| 471ff22ae3165278f1fb960d3e14517bcd601 | 提供-吉斯姆勒 5 倍壓縮 | 1 | ParticipatingPrograms | PositiveTargeting | FieldDemonstration | 
| 511ff22adfj65278f1fb960d3e14517bcd6e602 | 提供-吉斯姆勒 5 倍壓縮 | 1 | ParticipatingPrograms  | PositiveTargeting | EnterpriseContract | 

# 產品資料饋送
<a name="data-feed-product"></a>

此資料摘要提供您所建立做為記錄賣方的所有產品，以及您授權轉售之所有產品的相關資訊。

產品資料是可變的。這表示當您變更下列其中一個欄位的值時，會在資料饋送中為 `valid_from` 欄位建立具有不同值的新記錄。如需資料饋送歷史記錄資料欄的詳細資訊，請參閱 [資料的歷史化](data-feed-details.md#data-feed-historization)。

產品資料饋送每 24 小時重新整理一次，因此新的資料每天可用。

下表說明資料饋送資料欄的名稱和描述。


| 資料欄名稱  | 說明  | 
| --- | --- | 
| product\$1id | 產品的易記識別符。可用來加入 `Account`、 `Billing_Event`和 `Offer_Product` 資料饋送`product_id`的欄位。 | 
| manufacturer\$1account\$1id | 產品擁有者的識別符。這是[帳戶](data-feed-account.md)資料饋送的外部金鑰。可用來加入`Account`資料饋送的 `account_id` 欄位。 | 
| product\$1code | 用來計量產品的現有權利產品代碼。此值也用於將資料與報告聯結，或參考 AWS Marketplace Metering Service 中提供的內容。 | 
| 標題 | 產品的標題。 | 

## 產品資料饋送範例
<a name="data-feed-product-sample-data"></a>

以下顯示優惠目標資料饋送的範例。為了便於閱讀，不會顯示資料歷史記錄資料欄。如需資料歷史記錄欄位的資訊，請參閱 [資料的歷史化](data-feed-details.md#data-feed-historization)。


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

# 稅務項目資料饋送
<a name="data-feed-tax-item"></a>

此資料饋送提供客戶發票的稅務計算資訊。

特定客戶發票 (`line_item_id`) 的特定產品 () 可能會有多個明細項目 (`product_id`)`invoice_id`，每個稅務司法管轄區可能會有一或多個明細項目。例如，針對不同 AWS 實體 （例如，美國和愛爾蘭） 使用不同 AWS 區域規則的客戶，會發生這種情況。若要進一步了解 向哪些 AWS 收取銷售稅、增值稅或 GST，並將此類稅金匯給當地稅務機關，請參閱 AWS， Inc. 的 [Amazon Web Service Tax Help](https://aws.amazon.com/tax-help/)。

稅務項目資料饋送每 24 小時重新整理一次，因此新資料每天可用。

稅務項目資料不可變。

下表說明資料饋送資料欄的名稱和描述。如需資料歷史記錄資料欄的資訊，請參閱 [資料的歷史化](data-feed-details.md#data-feed-historization)。


| 欄名稱  | Description  | 
| --- | --- | 
| tax\$1item\$1id | 稅務項目記錄的唯一識別符。 | 
| invoice\$1id |  AWS 發票 ID。您可以使用此值搭配 值product\$1id來尋找相關的稅務帳單事件。 | 
| line\$1item\$1id | 客戶帳單明細項目的唯一識別符。退款交易具有與其轉送稅務交易相同的明細項目 ID。 | 
| customer\$1bill\$1id | 客戶帳單的唯一識別符。買方可以與賣方共用此識別符，以協助識別和解決稅務計算問題。 | 
| tax\$1liable\$1party | `AWS` 或 `Seller`。如果賣方是納稅方，則會收取稅金。如果 AWS 是稅務責任方，則會收取銷售稅並由 傳送 AWS。如需詳細資訊，請參閱[AWS Marketplace 賣方與稅務收集](https://aws.amazon.com/tax-help/marketplace)。 如果未收取稅金，此處不會顯示任何值。賣方需要判斷是否為每個發票收取某些稅金，因為賣方需負責收取稅金。 | 
| transaction\$1type\$1code | 交易的類型。可能的值如下： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-tax-item.html) 退款交易會與其原始轉送交易共用明細項目 ID。 | 
| product\$1id | 產品的外部金鑰。可用於加入 `product_id` 欄位上的`Product`資料饋送。 | 
| product\$1tax\$1code | 用來識別產品稅務屬性的標準程式碼。賣方在建立或修改產品時選擇屬性。 | 
| invoice\$1date | 發票建立的日期。 | 
| taxed\$1customer\$1account\$1id | 稅務帳戶實體的外部金鑰。可用於加入 `account_id` 欄位上的`Account`資料饋送。 | 
| taxed\$1customer\$1country | 用於稅務計算的地址的 ISO 3166 alpha 2 國家代碼。 | 
| taxed\$1customer\$1state\$1or\$1region | 用於稅務計算的州、區域或省。 | 
| taxed\$1customer\$1city | 用於稅務計算的城市。 | 
| taxed\$1customer\$1postal\$1code | 用於稅務計算的郵遞區號。 | 
| tax\$1location\$1code\$1taxed\$1jurisdiction | 與已課稅位置相關聯的頂點地理碼。 | 
| tax\$1type\$1code | 套用至交易的稅金類型。可能的值為 None、Sales 和 SellerUse。 | 
| jurisdiction\$1level | 用於稅務位置的地址的司法管轄區層級。可能的值為 State、County、City 和 District。 | 
| taxed\$1jurisdiction | 稅務司法管轄區的名稱。 | 
| display\$1price\$1taxability\$1type | 買方看到的價格是否包含或不含稅金。所有 AWS Marketplace 優惠皆不含稅金。 | 
| taxable\$1amount | 在此司法管轄區層級，應稅的交易金額。 | 
| nontaxable\$1amount | 在此司法管轄區層級，不可課稅的交易金額。 | 
| tax\$1jurisdiction\$1rate | 在此司法管轄區層級套用的稅金。 | 
| tax\$1amount | 在此司法管轄區層級收取的稅金。 | 
| tax\$1currency | 上述金額的 ISO 4217 Alpha 3 貨幣代碼。 | 
| tax\$1calculation\$1reason\$1code | 交易是應稅交易，而不是應稅交易、豁免交易，還是零評分交易，由司法管轄區層級組織。 | 
| date\$1used\$1for\$1tax\$1calculation | 用於計算交易稅的日期。 | 
| customer\$1exemption\$1certificate\$1id | 豁免憑證的憑證 ID。 | 
| customer\$1exemption\$1certificate\$1id\$1domain | 憑證存放在 Amazon 系統上的位置。 | 
| customer\$1exemption\$1certificate\$1level | 提供豁免的司法管轄區層級。 | 
| customer\$1exemption\$1code | 指定豁免的程式碼，例如 RESALE。 | 
| customer\$1exemption\$1domain | 如果可用，用於擷取客戶豁免資訊的 Amazon 系統。 | 
| transaction\$1reference\$1id | 可讓您從下列報告交叉參考資料的識別符： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/marketplace/latest/userguide/data-feed-tax-item.html)  | 
| legal\$1amount | 要支付稅金之法定貨幣的稅金金額。 | 
| legal\$1currency | 要支付稅金的法定貨幣。 | 
| tax\$1invoice\$1id | 在雙開發票的情況下，稅務明細行的稅務發票識別符。統一發票案例的此欄位將為 null。 | 
| tax\$1invoice\$1date | 稅務發票的日期。統一發票案例的此欄位將為 null。 | 

**注意**  
從 2021 年 8 月 5 日開始，國際市場協調人員 AWS Marketplace 銷售稅將在稅務項目資料饋送中包含項目。這表示自 2021 年 8 月 5 日起，帳單事件資料饋送中的每個 AWS\$1TAX\$1SHARE 和 SELLER\$1TAX\$1SHARE 記錄預期在稅務項目資料饋送中都有對應的記錄。

## 稅務項目資料饋送範例
<a name="data-feed-tax-item-sample-data"></a>

以下顯示稅務項目資料饋送的範例。在資料饋送中，此資訊會以單一資料表顯示。為了便於閱讀，資料會顯示在此處的多個資料表中，而且不會顯示所有資料欄。


| 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 | 
| --- | --- | --- | --- | --- | 
| 賣方 | AWS | prod-o4grxfafcxxxx | AWSMP\$1SOFTWARE\$1RA | 2018-12-31T00：00：00Z | 
| 賣方 | AWS | prod-o4grxfafcxxxx | AWSMP\$1SOFTWARE\$1RA | 2018-12-31T00：00：00Z | 
| 賣方 | AWS | prod-t3grxfafcxxxy | AWS\$1REMOTE\$1ACCESS\$1SOFTWARE | 2018-08-31T00：00：00Z | 
| 賣方 | 退款 | prod-t3grxfafcxxxy | AWS\$1REMOTE\$1ACCESS\$1SOFTWARE | 2018-08-31T00：00：00Z | 
| 賣方 | AWS | prod-x8faxxfafcxxy | AWS\$1REMOTE\$1ACCESS\$1SOFTWARE | 2018-08-31T00：00：00Z | 
| 賣方 | TAXONLYREFUND | prod-x8faxxfafcxxy | AWS\$1REMOTE\$1ACCESS\$1SOFTWARE | 2018-05-31T00：00：00Z | 
| AWS | AWS | prod-wghj8xfafrhgj | AWS\$1REMOTE\$1ACCESS\$1SOFTWARE | 2019-07-31T00：00：00Z | 




| taxed\$1customer\$1account\$1id | taxed\$1customer\$1country | taxed\$1customer\$1state\$1or\$1region | taxed\$1customer\$1city | taxed\$1customer\$1postal\$1code | 
| --- | --- | --- | --- | --- | 
| VIeGa2t9j3MuxioH9wc8lsndXXCgGCGUreeXriocM5 | 美國 | GA | 密爾頓 | 48573-4839 | 
| VIeGa2t9j3MuxioH9wc8lsndXXCgGCGUreeXriocM5 | 美國 | GA | 密爾頓 | 48573-4839 | 
| 7nyo5jwTRoPlyX81vx9ji04eEwTurO1Ff8biQi88W8 | 美國 | NC | 杜拉姆 | 27517-4834 | 
| 7nyo5jwTRoPlyX81vx9ji04eEwTurO1Ff8biQi88W8 | 美國 | NC | 杜拉姆 | 27517-4834 | 
| 7nyo5jwTRoPlyX81vx9ji04eEwTurO1Ff8biQi88W8 | 美國 | TX | 不適用 | 75844-1235 | 
| 7nyo5jwTRoPlyX81vx9ji04eEwTurO1Ff8biQi88W8 | 美國 | TX | 休士頓 | 75844-1235 | 
| 192a0421313e41f069b52962ed7babf716291b688 | 美國 | CT | 新 HAVEN | 06002-2948 | 




| tax\$1location\$1code\$1taxed\$1jurisdiction | tax\$1type\$1code | jurisdiction\$1level | taxed\$1jurisdiction | display\$1price\$1taxability\$1type | taxable\$1amount | nontaxable\$1amount | 
| --- | --- | --- | --- | --- | --- | --- | 
| 460473664 | 銷售 | State | GA | 獨家 | 100 | 0 | 
| 66301164 | 銷售 | County | 富爾頓 | 獨家 | 0 | 100 | 
| 692938178 | SellerUse | State | NC | 獨家 | 58.1 | 523.8 | 
| 692938178 | SellerUse | State | NC | 獨家 | -58.1 | 523.8 | 
| 356794387 | 銷售 | State | TX | 獨家 | 1105.14 | 0 | 
| 528887443 | 銷售 | City | 休士頓 | 獨家 | -36 | 0 | 
| 171248162 | 銷售 | State | CT | 獨家 | 0 | 114.55 | 




| tax\$1jurisdication\$1rate | tax\$1amount | tax\$1currency | tax\$1calculation\$1reason\$1code | date\$1used\$1for\$1tax\$1calculation | 
| --- | --- | --- | --- | --- | 
| 0.206 | 20.6 | 美元 | 應稅 | 2018-10-31T00：00：00Z | 
| 0 | 0 | 美元 | NonTaxable | 2018-10-31T00：00：00Z | 
| 0.1 | 5.8 | 美元 | 應稅 | 2018-07-31T00：00：00Z | 
| 0.1 | -5.8 | 美元 | 應稅 | 2018-07-31T00：00：00Z | 
| 0.06 | 66.3 | 美元 | 應稅 | 2018-07-31T00：00：00Z | 
| 0.01 | -0.36 | 美元 | NonTaxable | 2018-07-31T00：00：00Z | 
| 0 | 0 | 美元 | 豁免 | 2019-06-30T00：00：00Z |