eCosway » eCosway HK » RP_Purge

{{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 (Overall)

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 

</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. Verification SQL (Individual)

<pre>

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')

</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