深圳智汇创想科技有限公司

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