Batch » History » Revision 12
Revision 11 (Soh Keong, 11/07/2023 04:23 PM) → Revision 12/13 (Soh Keong, 11/07/2023 05:13 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-
> 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)
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 (Year 2023)
<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, 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
</pre>
h3. Transaction Table (After 2023)
<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 = 202401 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>
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