1.1. 背景
背景：核对亚马逊结算明细和帐单差异
1.2. 验收目标
1.结算明细与结算总金额月差异小于 1W ，差异阀值在正负20 以内的合计少于 3W/ 月
2.找出原差异对比原因。
2. 功能设计
2.1. 业务流程
无
2.2.1 操作路径
1. 数据库中间表--RB_Amazon_ListFinancialEventGroups 对比 RB_Amazon_ListFinancialEvents
2.2.2 功能点清单
1. 核对亚马逊结算明细和帐单差异
2.2.3 核对亚马逊结算明细和帐单差异
2.2.3.1 功能点描述：
原设计：
当前差异阀值在正负50 ，每月合计约 3W/ 月 数据量无办法解释。
调整：
当前差异阀值在正负20 ，降至每月合计约 3W/ 月 数据量，并定位剩余差异原因。
参考SQL
DECLARE @EndTime datetime ='2022-06-30'
DECLARE @StartTime datetime ='2022-06-01'
DECLARE @CustomerID int =1
DECLARE @SholdValue float =50; --差异金额
with #Financial_Group as (
SELECT * FROM
RB_Amazon_ListFinancialEventGroups a WITH (NOLOCK)
WHERE
FinancialEventGroupStart <= @EndTime
AND FinancialEventGroupEnd >= @StartTime
AND CustomerID = @CustomerID
AND FundTransferStatus = 'Succeeded'
)
SELECT
*
FROM
(
SELECT
A.FinancialEventGroupId AS '报告ID',
C.ID AS '渠道ID',
C.OrderSourceName AS '渠道名称',
A.OriginalTotalAmount AS '划款金额',
A.OriginalTotaCurrency AS '划款币种',
B.CurrencyAmount AS '明细金额合计',
(ABS(A.OriginalTotalAmount) - ABS(B.CurrencyAmount)) * D.ExchangeRMB AS '差值',
'RMB' AS '差值币种',
A.FinancialEventGroupStart AS '报告周期开始时间',
A.FinancialEventGroupEnd AS '报告周期结束时间'
FROM
#Financial_Group A LEFT JOIN (
SELECT
A.CustomerID,
A.FinancesReportID,
SUM(ISNULL(A.CurrencyAmount, 0)) AS CurrencyAmount
FROM
RB_Amazon_ListFinancialEvents A WITH (
NOLOCK,
INDEX = IX_RB_Amazon_ListFinancialEvents_CustomerID_FinancesReportID_EventDetailType
)
INNER JOIN #Financial_Group B ON A.FinancesReportID=B.FinancialEventGroupId
WHERE
A.CustomerID = 1
AND A.FinancialEventType<>'DebtRecoveryEvents' and EventDetailType<>'TotalAmount'
GROUP BY
A.CustomerID,
A.FinancesReportID
) B ON A.CustomerID = B.CustomerID
AND A.FinancialEventGroupId = B.FinancesReportID
LEFT JOIN RB_OrderSource C(NOLOCK) ON C.ID = ISNULL(A.OrderSourceID, A.AddOrderSourceID)
LEFT JOIN dbo.E_Currency D(NOLOCK) ON A.OriginalTotaCurrency=D.Currency
WHERE
A.OriginalTotalAmount <> B.CurrencyAmount
AND (ABS(A.OriginalTotalAmount - B.CurrencyAmount)*D.ExchangeRMB) > @SholdValue
) T