任务描述
暂无
需求描述

需求地址：M-15393 FMS：速卖通订单联盟及交易佣金公式调整（责任人：林很） · 钉钉文档 (dingtalk.com)


反馈人：王易婧

问题类型：202307012008146399570

系统0.71美金，后台实际收取0.31美金


[步骤]


[结果]联盟佣金同步错误


[期望]联盟佣金同步正确



验收标准
暂无
历史记录  
  1. 2023-07-13 10:29:31, 由 夏宇 创建。
  2. 2023-07-14 14:26:36, 由 夏宇 添加备注。

  3. 2023-07-17 14:15:42, 由 夏宇 启动。
    修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2023-07-17 14:15:37"。
    修改了 预计剩余，旧值为 "6"，新值为 "0.01"。
    修改了 任务状态，旧值为 "wait"，新值为 "doing"。
  4. 2023-07-17 14:21:14, 由 夏宇 添加备注。
    -- INSERT INTO [RB_AliExpress_OrderInfo] ( [CustomerID], [OrderSourceID], [AliExpressOrderID], [BuyerSignerFullName], [BuyerLoginID], [FrozenStatus], [FundStatus], [GmtCreate], [GmtModified], [GmtPaySuccess], [GmtTradeEnd], [OrderID], [IsPhone], [IssueStatus], [LoanStatus], [LogisticsStatus], [OrderStatus], [PaymentType], [SellerOperatorAliidloginID], [SellerOperatorLoginID], [SellerSignerFullName], [BuyerInfoCountry], [BuyerInfoFirstName], [BuyerInfoLastName], [BuyerInfoLoginID], [BuyerInfoEmail], [EscrowFeeAmount], [EscrowFeeCent], [EscrowFeeCurrencyCode], [InitOderAmountAmount], [InitOderAmountCent], [InitOderAmountCurrencyCode], [IssueModel], [ReasonCategoryId], [LoanInfoLoanTime], [LoanInfoAmountAmount], [LoanInfoAmountCent], [LoanInfoAmountCurrencyCode], [LogisticsAmountAmount], [LogisticsAmountCent], [LogisticsAmountCurrencyCode], [OrderAmountAmount], [OrderAmountCent], [OrderAmountCurrencyCode], [AddressAddress], [AddressAddress2], [AddressCity], [AddressContactPerson], [AddressCountry], [AddressDetailAddress], [AddressFaxArea], [AddressFaxCountry], [AddressFaxNumber], [AddressMobileNo], [AddressPhoneArea], [AddressPhoneCountry], [AddressPhoneNumber], [AddressProvince], [AddressZip], [RefundInfoRefundReason], [RefundInfoRefundStatus], [RefundInfoRefundType], [UpdateTime], [CheckStatusTime], [IsProcess], [ProcessTime], [PayAmountCurrencyCode], [PayAmountAmount], [PayAmountCent], [LatestShipDate], [LatestDeliveryDate], [DownloadDeliveryDateStatus], [PaymentReminderTotal], [PaymentReminderUpdateTime], [DeliveryMessageTotal], [DeliveryMessageDateTime], [RemindToCommentTotal], [RemindToCommentDateTime], [IssueGmtCreate], [IssueCmtModified], [ClientIssueStatus], [ClientIssueDateTime], [EvaluateDateTime], [RefundCashAmount], [RefundCashCurrencyCode], [RefundCouponAmount], [RefundCouponCurrencyCode], [RefundTime], [IsProcessRefund], [RefundCashAmountUSD], [RefundCouponAmountUSD], [CheckMessageTime], [Cod], [BatchCode], [IsFromTmcService], [OldOrderStatus], [SettlementCurrency], [PayAmountBySettlementCur], [PayAmountBySettlementCurValue], [SellerOrderAmountAmount], [SellerOrderAmountCurrencyCode], [FirstDeliveryDate], [Oaid]) VALUES ( 1, 7964, '8170727643800333', N'94b2a9431d58f6dfa44a2a1622b05a2c', 'at1042736333jgrae', 'NO_FROZEN', 'PAY_SUCCESS', '2023-07-01 19:49:23.000', '2023-07-13 18:06:07.000', '2023-07-01 19:49:30', NULL, '8170727643800333', '0', 'IN_ISSUE', 'loan_ok', 'SELLER_SEND_GOODS', 'WAIT_BUYER_ACCEPT_GOODS', 'MIXEDCARD', NULL, 'cn1526655617saxi', 'ROSENICE Healthy Well Store', N'AT', N'Spa***', N'Meliha', 'at1042736333jgrae', N'', .0000, 0, NULL, 28.4800, 2848, 'USD', NULL, NULL, '2023-07-04 05:41:24', 21.9300, 2193, 'USD', 16.0400, 1604, 'USD', 28.4800, 2848, 'USD', NULL, N'1434002c2658a5ff4dcb0de3f23864828d012693a4ba8179ab8eabf76e905988', N'Grieskirchen', N'd50e941446337207c25f24d54a1b0b93', N'AT', N'4c2d81d9401d9df29985e3ec6880b2f2', NULL, NULL, NULL, '735d18bd398dcb95bd278a38cff3d77e', NULL, N'+43', '', N'Upper Austria', N'4702', NULL, NULL, NULL, '2023-07-17 09:30:53.000', '2023-07-17 09:25:35.000', '1', '2023-07-17 09:51:18.637', 'USD', 28.4800, 0, '2023-07-08 20:50:35.000', '2023-07-18 19:06:08.000', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2023-07-17 08:32:39.457', '0', NULL, '0', N'WAIT_BUYER_ACCEPT_GOODS', NULL, N'28.48|23.74|USD', N'28.48', N'23.74', N'USD', '2023-07-03 13:58:15.627', 'a0Y0UKnjNaGSVnS5oIGZ0Q');
    --

    --INSERT INTO [RB_AliExpress_OrderChildOrderList] ( [CustomerID], [OrderSourceID], [AliExpressOrderID], [FrozenStatus], [FundStatus], [ChildID], [IssueStatus], [LotNum], [OrderStatus], [ProductAttributes], [ProductCount], [ProductID], [ProductName], [ProductStandard], [ProductUnit], [SkuCodes], [InitOrderAmtAmount], [InitOrderAmtCent], [InitOrderAmtCurrencyCode], [ProductPriceAmount], [ProductPriceCent], [ProductPriceCurrencyCode], [CanSubmitIssue], [DeliveryTime], [FreightCommitDay], [GoodsPrepareTime], [IssueMode], [LogisticsServiceName], [LogisticsType], [Memo], [MoneyBack3x], [ProductImgUrl], [ProductSnapUrl], [SendGoodsTime], [ProductUnitPriceAmount], [ProductUnitPriceCent], [ProductUnitPriceCurrencyCode], [TotalProductAmount], [TotalProductCent], [TotalProductCurrencyCode], [LogisticsAmount], [LogisticsCent], [LogisticsCurrencyCode], [SendGoodsOperator], [RefundCashAmount], [RefundCashCurrencyCode], [RefundCouponAmount], [RefundCouponCurrencyCode], [RefundTime], [RefundInfoRefundReason], [RefundInfoRefundStatus], [RefundInfoRefundType], [RefundCashAmountUSD], [RefundCouponAmountUSD], [LogisticsWarehouseType], [Tags], [AfflicateFeeRate], [EscrowFeeRate], [AlreadyTaxed], [OrderSortId]) VALUES ( 1, 1001020, '8170727643800333', 'NO_FROZEN', 'PAY_SUCCESS', '8170727643810333', 'IN_ISSUE', 1, 'WAIT_BUYER_ACCEPT_GOODS', N'{"sku":[{"skuImg":"Sd750ce4111b446048ba986fc3252f9ccm.jpg","selfDefineValue":"Red24.2cm","pName":"Color","pValueId":10,"pValue":"Red","pId":14,"order":1}]}', 1, '1005005549971711', N'100pcs Ear Wax Healthy Ear Wax Wax Cleaner for Relaxation Men', NULL, N'piece', N'E2GW212552SB4IV786', 12.6900, '1269', 'USD', 12.6900, 1269, 'USD', '0', NULL, NULL, '5', NULL, 'Cainiao Heavy Parcel Line', 'CAINIAO_STANDARD_HEAVY', NULL, '0', 'A407cb5959bf740f9a43e167458d8596ej.jpg', '//www.aliexpress.com/snapshot/null.html?orderId=8170727643810333', NULL, 12.6900, 1269, 'USD', 12.6900, 1269, 'USD', .0000, 1604, 'USD', N'SELLER_SEND_GOODS', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, .0300, .0800, N'I_TAXED', 1);

    -- 需要把RefundTime删掉
    SELECT * FROM RB_OrderList WHERE OrderCode='202307171051834151'
    SELECT  * FROM RB_AliExpress_OrderChildOrderList WHERE AliExpressOrderID='8170727643800333';

    -- //优惠金额=产品总价-税费收入+运费收入-总收入（不含税）
    -- decimal discountPrice = productTotalPrice - tex + transportPay - totalPrice;
    SELECT productTotalPrice,transportPay,totalPrice, * FROM RB_OrderInfo WHERE OrderCode='202307171051834151'
    SELECT OrderAmountAmount,sellerOrderAmountAmount, * FROM RB_AliExpress_OrderInfo WHERE AliExpressOrderID='8170727643800333'

    -- //优惠金额=产品总价-税费收入+运费收入-总收入（不含税）
    SELECT productTotalPrice,  transportPay,productTotalPrice,transportPay, * FROM RB_OrderInfo WHERE OrderCode='202307171051834151'


    -- //产品总价税费=税费收入-运费税费
    SELECT transportPay,productTotalPrice,transportPay,* FROM RB_OrderInfo WHERE OrderCode='202307171051834151'

    -- //税费收入
    -- decimal tex = aliExpressOrderInfo.OrderAmountAmount - sellerOrderAmountAmount;
    SELECT OrderAmountAmount,sellerOrderAmountAmount,* FROM RB_AliExpress_OrderInfo WHERE AliExpressOrderID='8170727643800333'


    -- 联盟佣金比例
    SELECT AfflicateFeeRate,* FROM RB_AliExpress_OrderChildOrderList WHERE AliExpressOrderID='8170727643800333'

    -- //联盟佣金=(产品总价-优惠金额-产品总价税费)*联盟佣金比例
    SELECT productTotalPrice,productTotalPrice,transportPay, * FROM RB_OrderInfo WHERE OrderCode='202307171051834151'

    -- //联盟佣金=(产品总价-优惠金额-产品总价税费)*联盟佣金比例
    SELECT EscrowFeeRate, * FROM RB_AliExpress_OrderChildOrderList WHERE AliExpressOrderID='8170727643800333'

    -- //交易佣金=(产品总价-优惠金额-产品总价税费)*交易佣金比例+(运费-运费税费)*运费佣金
    SELECT productTotalPrice,transportPay, * FROM RB_OrderInfo WHERE OrderCode='202307171051834151'

    SELECT ISNULL(oc.ProductPriceAmount,0)*oc.ProductCount,
    ISNULL(ao.OrderAmountAmount,0) - ISNULL(ao.SellerOrderAmountAmount,0),
    oi.TransportPay,
     ao.SellerOrderAmountAmount,
     oc.AfflicateFeeRate,
    oc.EscrowFeeRate
    FROM RB_AliExpress_OrderChildOrderList oc(NOLOCK) INNER JOIn RB_AliExpress_OrderInfo(NOLOCK) ao
    ON oc.CustomerID=ao.CustomerID AND oc.AliExpressOrderID=ao.AliExpressOrderID
    INNER JOIN RB_OrderInfo oi ON ao.CustomerID=oi.CustomerID AND ao.AliExpressOrderID=oi.ClientOrderCode
    WHERE oc.AliExpressOrderID='8151235343874884'

    SELECT * FROM RB_AliExpress_OrderChildOrderList  WHERE AliExpressOrderID='8170727643800333'


    DECLARE @ProductTotalPrice DECIMAL(18,2)=0
    DECLARE @Tex DECIMAL(18,2)=0
    DECLARE @TransportPay DECIMAL(18,2)=0
    DECLARE @SellerOrderAmountAmount DECIMAL(18,2)=0
    DECLARE @DiscountPrice DECIMAL(18,2)=0
    DECLARE @ShipTex DECIMAL(18,2)=0
    DECLARE @ProductTexPrice DECIMAL(18,2)=0
    DECLARE @AfflicateFeeRate DECIMAL(18,2)=0
    DECLARE @EscrowFeeRate DECIMAL(18,2)=0
    SELECT ISNULL(oc.ProductPriceAmount,0)*oc.ProductCount,
    ISNULL(ao.OrderAmountAmount,0) - ISNULL(ao.SellerOrderAmountAmount,0),
    oi.TransportPay,
     ao.SellerOrderAmountAmount,
     oc.AfflicateFeeRate,
     oc.EscrowFeeRate
    FROM RB_AliExpress_OrderChildOrderList oc(NOLOCK) INNER JOIn RB_AliExpress_OrderInfo(NOLOCK) ao
    ON oc.CustomerID=ao.CustomerID AND oc.AliExpressOrderID=ao.AliExpressOrderID
    INNER JOIN RB_OrderInfo oi ON ao.CustomerID=oi.CustomerID AND ao.AliExpressOrderID=oi.ClientOrderCode
    WHERE oc.AliExpressOrderID='8170727643800333'





  5. 2023-07-17 16:22:49, 由 夏宇 完成。
    修改了 总计消耗，旧值为 "0"，新值为 "0.01"。
    修改了 实际完成，旧值为 ""，新值为 "2023-07-17 14:21:47"。
    修改了 任务状态，旧值为 "doing"，新值为 "done"。
    修改了 预计剩余，旧值为 "0.01"，新值为 "0"。
    修改了 由谁完成，旧值为 ""，新值为 "xiayu"。

    修正上面的内容

  6. 2023-07-22 10:07:22, 由 夏宇 关闭。
    修改了 任务状态，旧值为 "done"，新值为 "closed"。
    修改了 指派给 ，旧值为 "xiayu"，新值为 "closed"。
    修改了 由谁关闭，旧值为 ""，新值为 "xiayu"。
    修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2023-07-22 10:07:22"。
    修改了 关闭原因，旧值为 ""，新值为 "done"。
返回
所属迭代 技术研发中心 7月第3版本（2023年）
所属模块 /
相关需求 FMS：速卖通订单联盟及交易佣金公式调整
指派给 Closed 于 2023-07-22 10:07:22
任务类型 测试
任务标签 初始
开发组 有品开发组
任务状态 已关闭
进度 100%
优先级 P1
关键词
抄送给
由谁创建 夏宇 于 2023-07-13 10:29:31
由谁完成 夏宇 于 2023-07-17 14:21:47
由谁取消 暂无
由谁关闭 夏宇 于 2023-07-22 10:07:22
关闭原因 已完成
最后编辑 夏宇 于 2023-07-22 10:07:22
团队 最初预计 总计消耗 预计剩余
工时信息
最初预计 6工时
总计消耗 0.01工时
预计剩余 0工时
预计开始 2023-07-13
实际开始 2023-07-17
截止日期 2023-07-20