Actions
- Table of contents
- Batches
Batches¶
PurgeRP¶
Program Class : ECpurgeRP
Run Indicator :
0 - First Time run only, For earn, get from table MEMBER_ACCOUNT
1 - Calculate Earn and Utilize RP from table TRANSACTION
> 2 - Calculate Utilized RP and Deduct from ProcessDate Balance
3 - Purge expired RP
> 4 - Add missing RP by period
> -4 - Roll back (4)
-3 - Roll back (3)
> -2 - Roll back (2)
-1 - Roll back (1)
Verification SQL (Overall)¶
First Run¶
SELECT A.MEMBER_ID, A.RP_BALANCE, A.RP1_BALANCE, D.RP_EARN FROM MEMBER_ACCOUNT A, RP_PURGE D WHERE A.MEMBER_ID = D.MEMBER_ID AND A.MEMBER_ID = D.MEMBER_ID AND D.PROCESS_DATE = 202312 AND (A.RP_BALANCE + A.RP1_BALANCE) != D.RP_EARN
Transaction Table (Year 2023)¶
SELECT A.MEMBER_ID, A.SHOPPER_ID, A.TOTAL_RP_EARNED, A.TOTAL_RP1_EARNED, A.TOTAL_RP_UTILIZED, A.TOTAL_RP1_UTILIZED, B.RP_EARN, B.RP_UTILIZE FROM TRANSACTION_112023 A, RP_PURGE B, MEMBER_ACCOUNT C WHERE (A.SHOPPER_ID = B.MEMBER_ID OR A.MEMBER_ID = B.MEMBER_ID) AND B.MEMBER_ID = C.MEMBER_ID AND ((A.TOTAL_RP_EARNED + A.TOTAL_RP1_EARNED + C.RP_BALANCE + C.RP1_BALANCE) != B.RP_EARN OR (A.TOTAL_RP_UTILIZED + A.TOTAL_RP1_UTILIZED) != B.RP_UTILIZE) AND B.PROCESS_DATE = 202312
Transaction Table (After 2023)¶
SELECT A.MEMBER_ID, A.SHOPPER_ID, A.TOTAL_RP_EARNED, A.TOTAL_RP1_EARNED, A.TOTAL_RP_UTILIZED, A.TOTAL_RP1_UTILIZED, B.RP_EARN, B.RP_UTILIZE FROM TRANSACTION_112023 A, RP_PURGE B WHERE (A.SHOPPER_ID = B.MEMBER_ID OR A.MEMBER_ID = B.MEMBER_ID) AND ((A.TOTAL_RP_EARNED + A.TOTAL_RP1_EARNED) != B.RP_EARN OR (A.TOTAL_RP_UTILIZED + A.TOTAL_RP1_UTILIZED) != B.RP_UTILIZE) AND B.PROCESS_DATE = 202401
Daily Check¶
SELECT A.MEMBER_ID FROM MEMBER_ACCOUNT A WHERE A.RP_BALANCE + A.RP1_BALANCE != ( SELECT SUM(B.RP_TO_BE_EXPIRED) FROM RP_PURGE B WHERE B.MEMBER_ID = A.MEMBER_ID AND B.EXPIRY_DATE > CURRENT DATE )
Verification SQL (Individual)¶
select * from RP_PURGE_TRANSACTION select * from RP_PURGE where member_id = 'HK123456' select MEMBER_ID, RP_BALANCE, RP_BALANCE_AS_OF, RP1_BALANCE, RP1_BALANCE_AS_OF from member_account where member_id = 'HK123456' select TRX_REF_NO, member_id, shopper_id,TOTAL_RP_EARNED, TOTAL_RP_UTILIZED, TOTAL_RP1_EARNED, TOTAL_RP1_UTILIZED from transaction_122023 where (member_id = 'HK123456' or shopper_id = 'HK123456')
Report¶
Program Class : GenHKRPDailySteps :
- Call webservice (EcHkMemberController.getRPBalanceExpiryDateList) get HK & MC RP balance with expiry date.
- Write RP to file name : RPyymmdd.HK
Updated by Soh Keong about 1 year ago ยท 13 revisions