Batch » History » Revision 11
Revision 10 (Soh Keong, 10/20/2023 11:50 AM) → Revision 11/13 (Soh Keong, 11/07/2023 04:23 PM)
{{toc}} h1. Batches h2. 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- Balance > 3 - Purge expired RP -> > 4 - Add missing RP by period- period -> > -4 - Roll back (4)- (4) > -3 - Roll back (3) -> > -2 - Roll back (2)- > -1 - Roll back (1) h2. Verification SQL h3. First Run <pre> 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 </pre> h3. Transaction Table <pre> 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 = 202312 </pre> h3. Daily Check <pre> 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 ) </pre> (2) h2. Report Program Class : GenHKRPDaily Steps : # Call webservice (EcHkMemberController.getRPBalanceExpiryDateList) get HK & MC RP balance with expiry date. # Write RP to file name : RPyymmdd.HK