• Table of contents
• Batches
  • PurgeRP
  • Verification SQL (Overall)
    • First Run
    • Transaction Table (Year 2023)
    • Transaction Table (After 2023)
    • Daily Check
  • Verification SQL (Individual)
  • Report

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 : GenHKRPDaily
Steps :

1. Call webservice (EcHkMemberController.getRPBalanceExpiryDateList) get HK & MC RP balance with expiry date.
2. Write RP to file name : RPyymmdd.HK