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 相关明细；
新建 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"