TASK#33437 Ebay回款交易明细下载补全及增加原币-金蝶4期计划 ---测试执行 / 技术研发中心 12月分支版本（2022年）

任务描述

暂无

需求描述

1.1. 背景

背景：现有Ebay 回款交易明细，关联PayoutID为空导致划款金额与划款明细不一致，存在缺失情况。

1.2. 验收目标

1.在ERP新建数据表，下载PayoutID相关Ebay明细数据；并生成Payout明细报告。

2. 功能设计

2.1. 业务流程

无

2.2.1 操作路径

1. 报告中心---自定义报告下载

2.2.3 在ERP新建数据表，下载PayoutID相关Ebay明细数据；并生成Payout明细报告

2.2.3.1 功能点描述：

原设计：

无

调整：

通过RB_Payoutid 表，已经生成的ID , 进行接口请求Ebay后台，下载Payoutid 相关明细；

"href": "https://apiz.ebay.com/sell/finances/v1/transaction?filter=payoutId%3A%7B5664328576%7D&sort=transactionDate&limit=1000&offset=0",

新建 RB_ebay_payoutid_detail 表，存放明细，字段包含 payoutid 为主要关联；

需求逻辑：明细金额合计等于划款金额；

费用正负项类型：因为后台下载接口数据都是正号，需要根据不同的费用类型增加正负号关联。

SALE 正号；

NON_SALE_CHARGE 负号；

DISPUTE 负号；

CREDIT 负号；

其他费用：负号；

冻结：负号；

索赔：正号；

调整：正号；

标签：负号；

纠纷：负号；

转账：正号；

退款：正号；

发款：正号；

下面是样例：

5.55 SALE 10.7 SALE 11.21 SALE 12.82 SALE -79.44 NON_SALE_CHARGE 30.8 TRANSFER 5.27 SALE -0.51 NON_SALE_CHARGE 3.8 SALE 3.79 SALE -0.24 NON_SALE_CHARGE 4.13 SALE 4.37 SALE 12.56 DISPUTE -12.56 CREDIT -1.57 NON_SALE_CHARGE

{

"href": "https://apiz.ebay.com/sell/finances/v1/transaction?filter=payoutId%3A%7B5664328576%7D&sort=transactionDate&limit=1000&offset=0",

"limit": 1000,

"offset": 0,

"transactions": [

{

"transactionId": "21-09277-18180",

"orderId": "21-09277-18180",

"payoutId": "5664328576",

"salesRecordReference": "0",

"buyer": {

"username": "hccar_6epjv34"

},

"transactionType": "SALE",

"amount": {

"value": "5.55",

"currency": "USD",

"convertedFromValue": "4.9",

"convertedFromCurrency": "GBP",

"exchangeRate": "1.13218"

},

"totalFeeBasisAmount": {

"value": "6.2",

"currency": "GBP"

},

"totalFeeAmount": {

"value": "1.3",

"currency": "GBP"

},

"orderLineItems": [

{

"lineItemId": "10055030136221",

"feeBasisAmount": {

"value": "7.44",

"currency": "GBP"

},

"marketplaceFees": [

{

"feeType": "INTERNATIONAL_FEE",

"amount": {

"value": "0.11",

"currency": "GBP"

},

"feeMemo": "因運送地址在大英聯合王國.而收取的費用。最終金額：GBP 7.44"

},

{

"feeType": "FINAL_VALUE_FEE",

"amount": {

"value": "0.89",

"currency": "GBP"

}

},

{

"feeType": "FINAL_VALUE_FEE_FIXED_PER_ORDER",

"amount": {

"value": "0.3",

"currency": "GBP"

}

}

]

}

],

"bookingEntry": "CREDIT",

"transactionDate": "2022-10-31T07:23:37.000Z",

"transactionStatus": "PAYOUT",

"paymentsEntity": "EBAY_COMMERCE_UK_LTD",

"ebayCollectedTaxAmount": {

"value": "1.24",

"currency": "GBP"

}

},

{

"transactionId": "03-09286-30887",

"orderId": "03-09286-30887",

"payoutId": "5664328576",

"salesRecordReference": "0",

"buyer": {

"username": "211589anniken"

},

"transactionType": "SALE",

"amount": {

"value": "10.7",

"currency": "USD",

"convertedFromValue": "9.45",

"convertedFromCurrency": "GBP",

"exchangeRate": "1.13218"

},

"totalFeeBasisAmount": {

"value": "11.96",

"currency": "GBP"

},

"totalFeeAmount": {

"value": "2.51",

"currency": "GBP"

},

"orderLineItems": [

{

"lineItemId": "10050628253303",

"feeBasisAmount": {

"value": "14.96",

"currency": "GBP"

},

"marketplaceFees": [

{

"feeType": "INTERNATIONAL_FEE",

"amount": {

"value": "0.28",

"currency": "GBP"

},

"feeMemo": "因運送地址在挪威.而收取的費用。最終金額：GBP 14.96"

},

{

"feeType": "FINAL_VALUE_FEE",

"amount": {

"value": "1.93",

"currency": "GBP"

}

},

{

"feeType": "FINAL_VALUE_FEE_FIXED_PER_ORDER",

"amount": {

"value": "0.3",

"currency": "GBP"

}

}

]

}

],

"bookingEntry": "CREDIT",

"transactionDate": "2022-10-31T19:25:42.000Z",

"transactionStatus": "PAYOUT",

"paymentsEntity": "EBAY_COMMERCE_UK_LTD",

"ebayCollectedTaxAmount": {

"value": "3.0",

"currency": "GBP"

}

},

{

"transactionId": "06-09288-42618",

"orderId": "06-09288-42618",

"payoutId": "5664328576",

"salesRecordReference": "0",

"buyer": {

"username": "esg36"

},

"transactionType": "SALE",

"amount": {

"value": "11.21",

"currency": "USD",

"convertedFromValue": "9.97",

"convertedFromCurrency": "GBP",

"exchangeRate": "1.12445"

},

"totalFeeBasisAmount": {

"value": "12.06",

"currency": "GBP"

},

"totalFeeAmount": {

"value": "2.09",

"currency": "GBP"

},

"orderLineItems": [

{

"lineItemId": "10050685314406",

"feeBasisAmount": {

"value": "14.47",

"currency": "GBP"

},

"marketplaceFees": [

{

"feeType": "INTERNATIONAL_FEE",

"amount": {

"value": "0.21",

"currency": "GBP"

},

"feeMemo": "因運送地址在大英聯合王國.而收取的費用。最終金額：GBP 14.47"

},

{

"feeType": "FINAL_VALUE_FEE_FIXED_PER_ORDER",

"amount": {

"value": "0.3",

"currency": "GBP"

}

},

{

"feeType": "FINAL_VALUE_FEE",

"amount": {

"value": "1.58",

"currency": "GBP"

}

}

]

}

],

"bookingEntry": "CREDIT",

"transactionDate": "2022-11-01T15:04:31.000Z",

"transactionStatus": "PAYOUT",

"paymentsEntity": "EBAY_COMMERCE_UK_LTD",

"ebayCollectedTaxAmount": {

"value": "2.41",

"currency": "GBP"

}

},

{

"transactionId": "19-09284-92973",

"orderId": "19-09284-92973",

"payoutId": "5664328576",

"salesRecordReference": "0",

"buyer": {

"username": "rebecchunte15"

},

"transactionType": "SALE",

"amount": {

"value": "12.82",

"currency": "USD",

"convertedFromValue": "11.4",

"convertedFromCurrency": "GBP",

"exchangeRate": "1.12445"

},

"totalFeeBasisAmount": {

"value": "14.14",

"currency": "GBP"

},

"totalFeeAmount": {

"value": "2.74",

"currency": "GBP"

},

"orderLineItems": [

{

"lineItemId": "10049787977619",

"feeBasisAmount": {

"value": "16.97",

"currency": "GBP"

},

"marketplaceFees": [

{

"feeType": "FINAL_VALUE_FEE_FIXED_PER_ORDER",

"amount": {

"value": "0.3",

"currency": "GBP"

}

},

{

"feeType": "FINAL_VALUE_FEE",

"amount": {

"value": "2.19",

"currency": "GBP"

}

},

{

"feeType": "INTERNATIONAL_FEE",

"amount": {

"value": "0.25",

"currency": "GBP"

},

"feeMemo": "因運送地址在大英聯合王國.而收取的費用。最終金額：GBP 16.97"

}

]

}

],

"bookingEntry": "CREDIT",

"transactionDate": "2022-11-01T20:24:25.000Z",

"transactionStatus": "PAYOUT",

"paymentsEntity": "EBAY_COMMERCE_UK_LTD",

"ebayCollectedTaxAmount": {

"value": "2.83",

"currency": "GBP"

}

},

{

"transactionId": "INVOICE-2-245784389-61-14",

"payoutId": "5664328576",

"salesRecordReference": "0",

"transactionType": "NON_SALE_CHARGE",

"amount": {

"value": "79.44",

"currency": "USD"

},

"bookingEntry": "DEBIT",

"transactionDate": "2022-11-03T18:24:12.000Z",

"transactionStatus": "PAYOUT",

"transactionMemo": "2022-11-01 - 2022-11-30",

"feeType": "OTHER_FEES"

},

{

"transactionId": "12333011608",

"payoutId": "5664328576",

"salesRecordReference": "0",

"transactionType": "TRANSFER",

"amount": {

"value": "30.8",

"currency": "USD"

},

"bookingEntry": "CREDIT",

"transactionDate": "2022-11-03T19:28:33.000Z",

"transactionStatus": "PAYOUT",

"transactionMemo": "TRANSFER_FROM"

},

{

"transactionId": "06-09300-11736",

"orderId": "06-09300-11736",

"payoutId": "5664328576",

"salesRecordReference": "0",

"buyer": {

"username": "pshtewan1980"

},

"transactionType": "SALE",

"amount": {

"value": "5.27",

"currency": "USD",

"convertedFromValue": "4.8",

"convertedFromCurrency": "GBP",

"exchangeRate": "1.09837"

},

"totalFeeBasisAmount": {

"value": "6.16",

"currency": "GBP"

},

"totalFeeAmount": {

"value": "1.36",

"currency": "GBP"

},

"orderLineItems": [

{

"lineItemId": "10050753998806",

"feeBasisAmount": {

"value": "7.39",

"currency": "GBP"

},

"marketplaceFees": [

{

"feeType": "FINAL_VALUE_FEE",

"amount": {

"value": "0.95",

"currency": "GBP"

}

},

{

"feeType": "FINAL_VALUE_FEE_FIXED_PER_ORDER",

"amount": {

"value": "0.3",

"currency": "GBP"

}

},

{

"feeType": "INTERNATIONAL_FEE",

"amount": {

"value": "0.11",

"currency": "GBP"

},

"feeMemo": "因運送地址在大英聯合王國.而收取的費用。最終金額：GBP 7.39"

}

]

}

],

"bookingEntry": "CREDIT",

"transactionDate": "2022-11-04T11:32:32.000Z",

"transactionStatus": "PAYOUT",

"paymentsEntity": "EBAY_COMMERCE_UK_LTD",

"ebayCollectedTaxAmount": {

"value": "1.23",

"currency": "GBP"

}

},

{

"transactionId": "FEE-4919950535816",

"payoutId": "5664328576",

"salesRecordReference": "0",

"transactionType": "NON_SALE_CHARGE",

"amount": {

"value": "0.51",

"currency": "USD"

},

"bookingEntry": "DEBIT",

"transactionDate": "2022-11-04T11:41:04.000Z",

"transactionStatus": "PAYOUT",

"paymentsEntity": "EBAY_COMMERCE_UK_LTD",

"references": [

{

"referenceId": "06-09300-11736",

"referenceType": "ORDER_ID"

},

{

"referenceId": "394025175696",

"referenceType": "ITEM_ID"

}

],

"feeType": "AD_FEE"

},

{

"transactionId": "27-09292-94048",

"orderId": "27-09292-94048",

"payoutId": "5664328576",

"salesRecordReference": "0",

"buyer": {

"username": "gil_gbu_kzf09kn"

},

"transactionType": "SALE",

"amount": {

"value": "3.8",

"currency": "USD",

"convertedFromValue": "3.46",

"convertedFromCurrency": "GBP",

"exchangeRate": "1.09837"

},

"totalFeeBasisAmount": {

"value": "4.48",

"currency": "GBP"

},

"totalFeeAmount": {

"value": "1.02",

"currency": "GBP"

},

"orderLineItems": [

{

"lineItemId": "10054187612527",

"feeBasisAmount": {

"value": "5.38",

"currency": "GBP"

},

"marketplaceFees": [

{

"feeType": "FINAL_VALUE_FEE_FIXED_PER_ORDER",

"amount": {

"value": "0.3",

"currency": "GBP"

}

},

{

"feeType": "INTERNATIONAL_FEE",

"amount": {

"value": "0.08",

"currency": "GBP"

},

"feeMemo": "因運送地址在大英聯合王國.而收取的費用。最終金額：GBP 5.38"

},

{

"feeType": "FINAL_VALUE_FEE",

"amount": {

"value": "0.64",

"currency": "GBP"

}

}

]

}

],

"bookingEntry": "CREDIT",

"transactionDate": "2022-11-04T13:52:03.000Z",

"transactionStatus": "PAYOUT",

"paymentsEntity": "EBAY_COMMERCE_UK_LTD",

"ebayCollectedTaxAmount": {

"value": "0.9",

"currency": "GBP"

}

},

{

"transactionId": "02-09309-60626",

"orderId": "02-09309-60626",

"payoutId": "5664328576",

"salesRecordReference": "0",

"buyer": {

"username": "5883sharon"

},

"transactionType": "SALE",

"amount": {

"value": "3.79",

"currency": "USD",

"convertedFromValue": "3.42",

"convertedFromCurrency": "GBP",

"exchangeRate": "1.10902"

},

"totalFeeBasisAmount": {

"value": "4.37",

"currency": "GBP"

},

"totalFeeAmount": {

"value": "0.95",

"currency": "GBP"

},

"orderLineItems": [

{

"lineItemId": "10050155338202",

"feeBasisAmount": {

"value": "5.24",

"currency": "GBP"

},

"marketplaceFees": [

{

"feeType": "FINAL_VALUE_FEE_FIXED_PER_ORDER",

"amount": {

"value": "0.3",

"currency": "GBP"

}

},

{

"feeType": "INTERNATIONAL_FEE",

"amount": {

"value": "0.08",

"currency": "GBP"

},

"feeMemo": "因運送地址在大英聯合王國.而收取的費用。最終金額：GBP 5.24"

},

{

"feeType": "FINAL_VALUE_FEE",

"amount": {

"value": "0.57",

"currency": "GBP"

}

}

]

}

],

"bookingEntry": "CREDIT",

"transactionDate": "2022-11-06T16:00:33.000Z",

"transactionStatus": "PAYOUT",

"paymentsEntity": "EBAY_COMMERCE_UK_LTD",

"ebayCollectedTaxAmount": {

"value": "0.87",

"currency": "GBP"

}

},

{

"transactionId": "FEE-4923887702616",

"payoutId": "5664328576",

"salesRecordReference": "0",

"transactionType": "NON_SALE_CHARGE",

"amount": {

"value": "0.24",

"currency": "USD"

},

"bookingEntry": "DEBIT",

"transactionDate": "2022-11-06T16:07:25.000Z",

"transactionStatus": "PAYOUT",

"paymentsEntity": "EBAY_COMMERCE_UK_LTD",

"references": [

{

"referenceId": "02-09309-60626",

"referenceType": "ORDER_ID"

},

{

"referenceId": "393707652761",

"referenceType": "ITEM_ID"

}

],

"feeType": "AD_FEE"

},

{

"transactionId": "12-09308-06659",

"orderId": "12-09308-06659",

"payoutId": "5664328576",

"salesRecordReference": "0",

"buyer": {

"username": "susant2270"

},

"transactionType": "SALE",

"amount": {

"value": "4.13",

"currency": "USD",

"convertedFromValue": "3.72",

"convertedFromCurrency": "GBP",

"exchangeRate": "1.10902"

},

"totalFeeBasisAmount": {

"value": "4.78",

"currency": "GBP"

},

"totalFeeAmount": {

"value": "1.06",

"currency": "GBP"

},

"orderLineItems": [

{

"lineItemId": "10049951374212",

"feeBasisAmount": {

"value": "5.74",

"currency": "GBP"

},

"marketplaceFees": [

{

"feeType": "FINAL_VALUE_FEE_FIXED_PER_ORDER",

"amount": {

"value": "0.3",

"currency": "GBP"

}

},

{

"feeType": "INTERNATIONAL_FEE",

"amount": {

"value": "0.08",

"currency": "GBP"

},

"feeMemo": "因運送地址在大英聯合王國.而收取的費用。最終金額：GBP 5.74"

},

{

"feeType": "FINAL_VALUE_FEE",

"amount": {

"value": "0.68",

"currency": "GBP"

}

}

]

}

],

"bookingEntry": "CREDIT",

"transactionDate": "2022-11-07T00:41:55.000Z",

"transactionStatus": "PAYOUT",

"paymentsEntity": "EBAY_COMMERCE_UK_LTD",

"ebayCollectedTaxAmount": {

"value": "0.96",

"currency": "GBP"

}

},

{

"transactionId": "12-09308-13189",

"orderId": "12-09308-13189",

"payoutId": "5664328576",

"salesRecordReference": "0",

"buyer": {

"username": "girlgonegaming"

},

"transactionType": "SALE",

"amount": {

"value": "4.37",

"currency": "USD",

"convertedFromValue": "3.94",

"convertedFromCurrency": "GBP",

"exchangeRate": "1.10902"

},

"totalFeeBasisAmount": {

"value": "4.99",

"currency": "GBP"

},

"totalFeeAmount": {

"value": "1.05",

"currency": "GBP"

},

"orderLineItems": [

{

"lineItemId": "10049951787112",

"feeBasisAmount": {

"value": "5.3",

"currency": "GBP"

验收标准

暂无

历史记录

2022-12-05 10:33:26, 由 林鸿贵 创建。
2022-12-06 15:25:55, 由 zhengqiao 取消。
修改了 任务状态，旧值为 "wait"，新值为 "cancel"。
修改了 实际完成，旧值为 ""，新值为 "0000-00-00"。
修改了 由谁取消，旧值为 ""，新值为 "zhengqiao"。
修改了 取消时间，旧值为 ""，新值为 "2022-12-06 15:25:55"。
2022-12-06 15:27:01, 由 zhengqiao 编辑。
修改了 所属迭代，旧值为 "665"，新值为 "666"。
修改了 任务状态，旧值为 "cancel"，新值为 "wait"。
修改了 由谁取消，旧值为 "zhengqiao"，新值为 ""。
2022-12-06 17:02:56, 由 林鸿贵 编辑。
修改了 所属迭代，旧值为 "666"，新值为 "665"。

测试环境：http://10.158.158.22:8080/Manager/index.aspx

验证场景：

一、执行服务，查看对应表是否有新增数据
2022-12-06 17:19:27, 由 林鸿贵 编辑。
修改了 相关需求，旧值为 "12693"，新值为 "0"。

https://alidocs.dingtalk.com/i/nodes/xdqZp24KneBJzK296qp28vyb7RA0Nr1E?iframeQuery=mode%3D2%26sheetId%3Dsheet1
2022-12-08 14:09:55, 由 林鸿贵 编辑。
修改了 所属迭代，旧值为 "665"，新值为 "666"。
修改了 相关需求，旧值为 "0"，新值为 "12693"。
2022-12-08 16:22:53, 由 林鸿贵 完成。
修改了 总计消耗，旧值为 "0"，新值为 "2"。
修改了 实际完成，旧值为 ""，新值为 "2022-12-08 16:22:39"。
修改了 任务状态，旧值为 "wait"，新值为 "done"。
修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2022-12-08 16:22:52"。
修改了 预计剩余，旧值为 "14"，新值为 "0"。
修改了 由谁完成，旧值为 ""，新值为 "linhonggui"。

select * from RB_OrderSource_API where ordersourceid in (SELECT id from RB_OrderSource where ordersourcetype = 2)

SELECT * from RB_Ebay_Payout WHERE AddTime>'2022-12-08'
select TransactionType,ConvertedAmount,* from RB_Ebay_Payout_Detail WHERE AddTime>'2022-12-08'

需要EBAY正式授权
2022-12-08 16:23:33, 由 林鸿贵 添加备注。

区分正负和自定义报告，产品开发说2022/12/13再补需求调整
2022-12-08 16:23:35, 由 林鸿贵 关闭。
修改了 任务状态，旧值为 "done"，新值为 "closed"。
修改了 指派给 ，旧值为 "linhonggui"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "linhonggui"。
修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2022-12-08 16:23:35"。
修改了 关闭原因，旧值为 ""，新值为 "done"。

基本信息
任务的一生

所属迭代	技术研发中心 12月分支版本（2022年）
所属模块	/
相关需求	Ebay回款交易明细下载补全及增加原币-金蝶4期计划
指派给	Closed 于 2022-12-08 16:23:35
任务类型	测试
任务标签	初始
开发组	百货开发组
任务状态	已关闭
进度	100%
优先级	P2
关键词
抄送给

由谁创建	林鸿贵于 2022-12-05 10:33:26
由谁完成	林鸿贵于 2022-12-08 16:22:39
由谁取消	暂无
由谁关闭	林鸿贵于 2022-12-08 16:23:35
关闭原因	已完成
最后编辑	林鸿贵于 2022-12-08 16:23:35

团队	最初预计	总计消耗	预计剩余

工时信息

最初预计	14工时
总计消耗	2工时
预计剩余	0工时
预计开始	0000-00-00
实际开始	2022-12-08
截止日期	2022-12-15