Project

General

Profile

Batch » History » Version 13

Soh Keong, 11/07/2023 05:17 PM

1 1 Soh Keong
{{toc}}
2
3
h1. Batches
4
5
h2. PurgeRP
6
7
Program Class : ECpurgeRP
8
Run Indicator : 
9 10 Soh Keong
> 0  - First Time run only, For earn, get from table MEMBER_ACCOUNT
10 3 Soh Keong
11 9 Soh Keong
> 1  - Calculate Earn and Utilize RP from table TRANSACTION
12 3 Soh Keong
13 11 Soh Keong
-> 2  - Calculate Utilized RP and Deduct from ProcessDate Balance-
14 3 Soh Keong
15 1 Soh Keong
> 3  - Purge expired RP
16 3 Soh Keong
17 11 Soh Keong
-> 4  - Add missing RP by period-
18 7 Soh Keong
19 11 Soh Keong
-> -4 - Roll back (4)-
20 7 Soh Keong
21 1 Soh Keong
> -3 - Roll back (3)
22
23 11 Soh Keong
-> -2 - Roll back (2)-
24 1 Soh Keong
25 11 Soh Keong
> -1 - Roll back (1)
26
27 13 Soh Keong
h2. Verification SQL (Overall)
28 11 Soh Keong
29
h3. First Run
30
31
<pre>
32
SELECT A.MEMBER_ID, A.RP_BALANCE, A.RP1_BALANCE, D.RP_EARN
33
FROM MEMBER_ACCOUNT A, RP_PURGE D
34
WHERE A.MEMBER_ID = D.MEMBER_ID AND 
35
A.MEMBER_ID = D.MEMBER_ID AND D.PROCESS_DATE = 202312 AND 
36
(A.RP_BALANCE + A.RP1_BALANCE) != D.RP_EARN
37
</pre>
38
39 12 Soh Keong
h3. Transaction Table (Year 2023)
40 11 Soh Keong
41
<pre>
42
SELECT A.MEMBER_ID, A.SHOPPER_ID, 
43
A.TOTAL_RP_EARNED, A.TOTAL_RP1_EARNED, 
44
A.TOTAL_RP_UTILIZED, A.TOTAL_RP1_UTILIZED, 
45 1 Soh Keong
B.RP_EARN, B.RP_UTILIZE
46 12 Soh Keong
FROM TRANSACTION_112023 A, RP_PURGE B, MEMBER_ACCOUNT C 
47
WHERE (A.SHOPPER_ID = B.MEMBER_ID OR A.MEMBER_ID = B.MEMBER_ID) AND 
48
B.MEMBER_ID = C.MEMBER_ID AND 
49
((A.TOTAL_RP_EARNED + A.TOTAL_RP1_EARNED + C.RP_BALANCE + C.RP1_BALANCE) != B.RP_EARN OR 
50
(A.TOTAL_RP_UTILIZED + A.TOTAL_RP1_UTILIZED) != B.RP_UTILIZE) AND 
51
B.PROCESS_DATE = 202312 
52
</pre>
53
54
h3. Transaction Table (After 2023)
55
56
<pre>
57
SELECT A.MEMBER_ID, A.SHOPPER_ID, 
58
A.TOTAL_RP_EARNED, A.TOTAL_RP1_EARNED, 
59
A.TOTAL_RP_UTILIZED, A.TOTAL_RP1_UTILIZED, 
60
B.RP_EARN, B.RP_UTILIZE
61 11 Soh Keong
FROM TRANSACTION_112023 A, RP_PURGE B 
62
WHERE (A.SHOPPER_ID = B.MEMBER_ID OR A.MEMBER_ID = B.MEMBER_ID) AND 
63 1 Soh Keong
((A.TOTAL_RP_EARNED + A.TOTAL_RP1_EARNED) != B.RP_EARN OR 
64 11 Soh Keong
(A.TOTAL_RP_UTILIZED + A.TOTAL_RP1_UTILIZED) != B.RP_UTILIZE) AND 
65 12 Soh Keong
B.PROCESS_DATE = 202401 
66 11 Soh Keong
</pre>
67
68
h3. Daily Check
69
70
<pre>
71
SELECT A.MEMBER_ID 
72
FROM MEMBER_ACCOUNT A
73
WHERE A.RP_BALANCE + A.RP1_BALANCE != (
74
	SELECT SUM(B.RP_TO_BE_EXPIRED) 
75
	FROM RP_PURGE B 
76
	WHERE B.MEMBER_ID = A.MEMBER_ID AND 
77
	B.EXPIRY_DATE > CURRENT DATE
78
)
79
</pre>
80 1 Soh Keong
81 13 Soh Keong
h2. Verification SQL (Individual)
82
83
<pre>
84
select * from RP_PURGE_TRANSACTION
85
86
select * from RP_PURGE where member_id = 'HK123456'
87
88
select MEMBER_ID, RP_BALANCE, RP_BALANCE_AS_OF, RP1_BALANCE, RP1_BALANCE_AS_OF  from member_account where member_id = 'HK123456'
89
90
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')
91
</pre>
92 2 Soh Keong
93
h2. Report
94
95
Program Class : GenHKRPDaily
96
Steps         :
97
# Call webservice (EcHkMemberController.getRPBalanceExpiryDateList) get HK & MC RP balance with expiry date.
98
# Write RP to file name : RPyymmdd.HK