2021-10-03-Subscription-Fee-failed-for-some-users¶

Background¶

Some users didn't have an Account ID in SubscriptionFeeMessage payload because of a production issue caused by deploy. Transaction service was deployed before Fees & Promotions.

Steps To Fix¶

Fix data in Fees & Promotions DB

-- In Fees & Promotions DB
-- Collect missed Subscription Fee charges with empty AccountId

SELECT --TOP (1000) 
       [ChargeId]
      ,[Created]
      ,[Updated]
      ,[RowId]
      ,[UserId]
      ,[AccountId]
      ,[Type]
      ,[SchemeConfigurationId]
      ,[Amount]
      ,[Currency]
      ,[Status]
      ,[ChargedDate]
      ,[NextChargeDate]
      ,[ScheduledEventId]
      ,[ExternalReferenceId]
  FROM [dbo].[Charge]
  where Type = 1 
  AND AccountId = '00000000-0000-0000-0000-000000000000' 
  --AND UserId = 'f624892a-983f-42ce-8e9e-e622600afcc1'
  --AND SchemeConfigurationId = 17
  AND Status = 1
  AND NextChargeDate < GETUTCDATE()
  ORDER By NextChargeDate ASC

-- In Transactions DB
-- Collect AccountIDs for all affected users
SELECT 
      [AccountHolderId],
      [MajorityAccountId]
  FROM [dbo].[MajorityAccount]
  where AccountHolderId IN (
      '33144e14-6d0f-4667-8506-3efa36ded535'
      , ....
     )

-- in Fees & Promotions
-- Fix charges with missing Account IDs

CREATE TABLE #AccountIds
(
    UserId UNIQUEIDENTIFIER,
    AccountId UNIQUEIDENTIFIER
);

insert into #AccountIds
    (UserId,AccountId)
values
 ('4985e7bc-5025-43b4-b509-002b5ed0116a','9953c39b-62e7-4466-886d-84208818abf6')
-- all extracted data goes here

/*
UPDATE [dbo].[Charge]
   SET AccountId = a.AccountId
 FROM [dbo].[Charge] AS c
 INNER JOIN #AccountIds AS a
   ON c.UserId = a.UserId
 WHERE c.AccountId = '00000000-0000-0000-0000-000000000000'
*/

Call ExpireSubscriptionFee endpoint in Fees & Promotions Service to reschedule Subscription Fee Events. The data must be fixed first.

curl http://localhost:32123/users/33144e14-6d0f-4667-8506-3efa36ded535/expire -X POST -v -H "Content-Type: application/json" -d '{}'