Points awarded/deducted scenarios
The major fact tables to refer to understand the use cases and possible scenarios of events related to points are as below:
EI tables for Points data | |
---|---|
Fact tables | Dimension tables |
points | points_awarded_type |
points_category | |
points_event_type | |
points_promotions | |
deduction_type | |
points_redemption_summary | Date , zone_till , time |
points_expiry_reminder_info | Date , communication_channel, time |
The dimension table for Point Fact which gives info regarding the points awarded scenario is points_awarded_type.
Values for awarded_type and their context:
POINTS_AWARDED:
- It refers to the regular points awarded to the user for making any transactions.
- Scenario:
- Customer made a txn of Rs.1000 on 1st Feb
- Customer is awarded 100 points
- Effect :
- Entry in the points fact on EI side with granularity at awarded_ref_id level.
- Each entry refers to a unique event of points awarded at transaction level.
- The column dim_points_awarded_type_id will be 1 referring to enum ‘POINT_AWARDED’.
- Allocated_points = 100 [populated from the points_exclusive column of the points_awarded table in the source ] against the bill_id.
POINTS_AWARDED_BILL_PROMOTION
-
Promotional points given on top of regular point at transaction level
-
Scenario:
- Customer made a txn of Rs.1000 on 1st Feb and got 100 points.
- Also got 50 bonus points through a promotion running.
-
Effect:
- Entry in the points fact on EI side with granularity at awarded_ref_id level.
- Each entry refers to a unique event of points awarded at transaction level.
- The column dim_points_awarded_type_id will be 2 referring to enum POINT_AWARDED_BILL_PROMOTION.
- Allocated_points = 100 [populated from the points_exclusive column of the points_awarded table in the
source ] against the bill_id in the points fact. - Allocated_points = 50[populated from the points_value column of points_awarded_bill_promotions table in
the source ] against the bill_id in the points fact
POINTS_AWARDED_LINEITEM
-
Regular points awarded for purchasing a specific product
-
Scenario:
- Customer made a txn of Rs. 1000, containing 3 items of Rs. 200, Rs. 350 and Rs. 450,
- He gets 20 points on item 1, 35 points on item 2 and 45 points on item 3.
- Also gets 40 bonus points on item 1 through a promotion running on that SKU
-
Effect:
- Entry in the points fact on EI side with granularity at awarded_ref_id level.
- Each entry refers to a unique event of points awarded at transaction level.
-The column dim_points_awarded_type_id will be 3 referring to enum POINT_AWARDED_LINEITEM.
-
On source side there would be an entry against each line item
- points_exclusive column of points_awarded_lineitems table will have 20 points against first bill_lineitem, (id
will be in bill_lineitem_id column) - points_exclusive column of points_awarded_lineitems table will have 35 points against second bill_lineitem,
(id will be in bill_lineitem_id column) - points_exclusive column will have 45 points against thrift bill_lineitem, (id will be in bill_lineitem_id column)
event_log_id in all 3 records with id generated in the event_log table
- points_exclusive column of points_awarded_lineitems table will have 20 points against first bill_lineitem, (id
-
In customer_points_summary table, against that point category,
- current_points will become x + 100 (x is the current_points before this event)
- cumulative_points will become x +100
- event_log_id with id generated in the event_log table
-
If Ledger is enabled, points_ledger table will have an
- entry of 100 points with entry_type as CREDIT
- cps_id which got affected in the above CPS
- event_log_id with id generated in the event_log table
POINTS_AWARDED_LINEITEM_PROMOTION
-
Promotional points given on top of regular point at line item level
-
Scenario:
- Customer made a txn of Rs. 1000, containing 3 items of Rs. 200, Rs. 350 and Rs. 450,
- He gets 20 points on item 1, 35 points on item 2 and 45 points on item 3.
- Also gets 40 bonus points on item 1 through a promotion running on that SKU
-
Effect:
- Entry in the points fact on EI side with granularity at awarded_ref_id level.
- Each entry refers to a unique event of points awarded at transaction/line item/promotion level.
- The column dim_points_awarded_type_id will be 4 referring to enum
POINTS_AWARDED_LINEITEM_PROMOTION.
-
In the event_log table, an event record will be inserted with event_type_id as 15.
-
The id generated here is event_log_id.
-
In points_awarded_lineitems table, there will be 3 entries
- points_exclusive column will have 20 points against first bill_lineitem (id will be in bill_lineitem_id column),
- points_exclusive column will have 35 points against second bill_lineitem (id will be in bill_lineitem_id
column), - points_exclusive column will have 45 points against thrift bill_lineitem (id will be in bill_lineitem_id column),
- event_log_id in all 3 records with id generated in the event_log table
-
In points_awarded_lineitem_promotion table, there will a single entry
- points_value column will have 40 points against the first bill_lineitem.
-
In customer_points_summary table, against that point category,
- current_points will become x + 140 (x is the current_points before this event)
- cumulative_points will become x +140
- event_log_id with id generated in the event_log table
-
If Ledger is enabled, points_ledger table will have an
- entry of 140 points with entry_type as CREDIT
- cps_id which got affected in the above CPS
- event_log_id with id generated in the event_log table
POINTS_AWARDED_CUSTOMER_PROMOTION
-
Promotional points given on top of regular points at customer level.
-
Scenario:
- Customer gets 100 bonus points while enrolling/registering in the program.
-
Effect:
- Entry in the points fact on EI side with granularity at awarded_ref_id level.
- Each entry refers to a unique event of points awarded at transaction/line item/promotion level.
- The column dim_points_awarded_type_id will be 4 referring to enum
POINTS_AWARDED_CUSTOMER_PROMOTION.
-
In the event_log table, an event record will be inserted with event_type_id as 2.
-
The id generated here is event_log_id
-
In points_awarded_customer_promotion table,
- points_value column will have 100 points,
- event_log_id with id generated in the event_log table
-
In customer_enrollment table, against that point category,
- a new entry will be created for the customer
- event_log_id with id generated in the event_log table
-
In customer_points_summary table, against that point category,
- a new entry will be created for the customer
- current_points will become 100
- cumulative_points will become 100
- event_log_id with id generated in the event_log table
-
If Ledger is enabled, points_ledger table will have an
- entry of 100 points with entry_type as CREDIT
- cps_id which got affected in the above CPS
- event_log_id with id generated in the event_log table
The dimension table for Point Fact which gives info regarding the points deducted scenario is deduction_type.
Values for deduction_type and their context:
EXPIRED
-
Points awarded to the user are expired and deducted.
-
Scenario:
- Customer made a transaction of Rs.1000 on 1st feb and was awarded 100 points.
- The above awarded points expired on 10th feb.
-
Effect:
- All points that expire will appear in the Point Fact at the granularity of the awarded_ref_id against which the
points have expired. - Points shall appear in the ‘deducted_points’ column. Effect is reduction in user’s point balance.
- The column dim_deduction_type_id will be 2 referring to enum EXPIRED.
- Till 9th feb, the flow will be usual like the above awarded scenarios, because the points will be in awarded
tables.
- All points that expire will appear in the Point Fact at the granularity of the awarded_ref_id against which the
-
On 10th feb, the following will happen :
- In event_log table new entry will be created with event_type_id as 2
-
In PA table,
- points_exclusive column will stay 100
- expired_exclusive_value column will become 100
- status column will change from AVAILABLE to EXPIRED
-
In CPS table
- current_points column will reduce by 100
- cumulative_points column will stay the same
- points_expired column will become x+100
-
If Ledger is Enabled,
- an entry_type column with DEBIT will be created
- points column will be 100
- event_log_id column will have the id generated in the above event_log table
-
In PD table,
- an entry will be created with deduction_type as EXPIRED
- points_deducted column will have 100.
- event_log_id will the id generated in the above event_log table
- points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
REDEEMED
-
User redeems the points allocated to him for any transaction
-
Scenario:
- Customer made a txn of Rs.1000 on 1st Feb and got 100 points.
- The points are redeemed by the customer on 8th Feb.
-
Effect:
- All points that expire will appear in the Point Fact at the granularity of the awarded_ref_id against which
the points have redeemed. - The column dim_deduction_type_id will be 4 referring to enum REDEEMED.
- All points that expire will appear in the Point Fact at the granularity of the awarded_ref_id against which
-
On 8th Feb, In event_log table
- new entry will be created with event_type_id as 3
-
In PA table,
- points_exclusive column will stay 100
- redeemed_exclusive_value column will become 100
- status column will change from AVAILABLE to REDEEMED
-
In CPS table,
- current_points column will reduce by 100
- cumulative_points column will stay the same
- points_redeemed column will become x+100
-
If Ledger is Enabled,
- an entry_type column with DEBIT will be created
- points column will be 100
- event_log_id column will have the id generated in the above event_log table
-
In PD table,
- an entry will be created with deduction_type as REDEEMED
- points_deducted column will have 100.
- event_log_id will the id generated in the above event_log table
- points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
-
In PRS table,
- an entry will be created with redemption_type column as REDEEMED
- points_redeemed column will have 100
- redemption_id column will have a unique id generated by loyalty
- bill_id / bill_number column will have the bill against which points were redeemed(if passed in the
payload) - event_log_id will the id generated in the above event_log table
RETURN
-
Points are allocated to the user on a transaction.
-
Before the points are redeemed or expired, the user returns the transaction.
-
The points awarded on these transaction are then deducted
-
Scenario:
- Customer made a txn 1 of Rs.1000 on 1st Feb and got 100 points.
- The expiry date of the points is 10th Feb.
- txn 1 is returned by the customer on 5th Feb.
-
Effect:
- All points that expire will appear in the Point Fact at the granularity of the awarded_ref_id against which the
points have been returned. - The column dim_deduction_type_id will be 5 referring to enum RETURN.
- All points that expire will appear in the Point Fact at the granularity of the awarded_ref_id against which the
-
On 5th Feb, In event_log table
- new entry will be created with event_type_id as 5
-
In PA table,
- points_exclusive column will stay 100
- returned_exclusive_value column will become 100
- status column will change from AVAILABLE to RETURNED
-
In CPS table,
- current_points column will reduce by 100
- cumulative_points column will stay the same
- points_returned column will become x+100
-
If Ledger is Enabled,
- an entry_type column with DEBIT will be created
- points column will be 100
- event_log_id column will have the id generated in the above event_log table
-
In PD table,
- an entry will be created with deduction_type as RETURN
- points_deducted column will have 100.
- event_log_id will the id generated in the above event_log table
- points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
REDEMPTION_REVERSAL
-
User redeems the points for a transaction ,
-
later he decides not to use those points and reverse the redemption
-
OR the transaction on which he redeemed the points is reverted.
-
Scenario:
- Customer made a txn 1 of Rs.1000 on 1st Feb and got 100 points.The expiry date of the points is 10th Feb.
- Customer makes another txn 2 of Rs 2000 on 3rd Feb and redeems 100 points.
- Customer returns txn 2 on 5th Feb
-
Effect:
- All points that expire will appear in the Point Fact at the granularity of the awarded_ref_id against which the
redeemed points have been reversed. - The column dim_deduction_type_id will be 7 referring to enum REDEMPTION_REVERSAL.
- All points that expire will appear in the Point Fact at the granularity of the awarded_ref_id against which the
-
On 3rd Feb, In event_log table
- first entry will be created with event_type_id as 3 for Redeem---- e1
- 2nd entry will be created with event_type_id as 15 for transaction/add------e2
-
In PA table,
- The PA created for the 1st transaction will be updated with
- redeemed_exclusive_value column will become x+100
- status column will become REDEEMED
-
In CPS Table,
- points_redeemed column will become x + 100
- current_points will reduce by 100 (x-100)
-
If Ledger is enabled, Points Ledger table will have,
- DEBIT entry of 100 will be created against e1 event_log_id
- cps_id will be the one that is affected above
-
In PD table,
- an entry will be created with deduction_type as REDEEMED
- points_deducted column will have 100.
- event_log_id will the id as e1
- points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
-
In PRS table,When redemption occurred
- an entry will be created with redemption_type column as REDEEMED
- points_redeemed column will have 100
- redemption_id column will have a unique id generated by loyalty
- billNumber / billId will be -1
- event_log_id will the id generated as e1
-
When 2nd transaction occurs, If unique redemption id is passed in the payload,
- billNumber / billId column will get updated to bill_number_2 / bill_id_2
-
After 5thFeb, when Txn2 is returned,
-
In event_log table,
- an entry will be created with event_type_id as 5 ----- e3
-
In PA Table,
- redeemed_exclusive_value will become x-100
- status column will change from REDEEMED to AVAILABLE
- points_exclusive stay as it is.
-
In CPS Table,
- points_redeemed column will become x - 100
- current_points will increase by 100 (x+100)
-
If Ledger is enabled, Points Ledger table will have,
- CREDIT entry of 100 will be created against e3 event_log_id
- cps_id will be the one that is affected above
-
In PD table,
- an entry will be created with deduction_type as REDEMPTION_REVERSAL
- points_deducted column will have 100.
- event_log_id will the id as e3
- points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
-
In PRS table, When redemption occurred
- an entry will be created with redemption_type column as REVERSAL_ON_RETURN
- points_redeemed column will have 100
- redemption_id column will have a unique id generated by loyalty
- billNumber / billId will be -1
- event_log_id will the id generated as e3
REDEMPTION_REVERTED
-
Points are allocated to the user on a transaction and he redeems these points.
-
Later, the transaction on which he earned these points is returned.
-
Scenario:
- Customer made a txn 1 of Rs.1000 on 1st Feb and got 100 points.The expiry date of the points is 10th Feb.
- Customer makes another txn 2 of Rs 2000 on 3rd Feb and redeems 100 points.
- Customer returns txn 1 on 5th Feb
-
Effect:
- All points that expire will appear in the Point Fact at the granularity of the awarded_ref_id against which the
redeemed points have been reverted. - The column dim_deduction_type_id will be 9 referring to enum REDEMPTION_REVERTED .
- All points that expire will appear in the Point Fact at the granularity of the awarded_ref_id against which the
-
On 5th Feb, when Txn2 is returned,
-
In event_log table,
- an entry will be created with event_type_id as 5 ----- e3
-
In PA Table,
- redeemed_exclusive_value will become x-100
- status column will change from REDEEMED to RETURNED
- points_exclusive stay as it is.
- returned_exclusive_value will become x+100
-
In PABP Table,New entry will be created with
- points_value column will be 0
- redeemed_value column will be x+100
- status column will be CONSUMED
- event_log_id will be e3
- promotion_id will be the id of the promotion mapped to ReturnBillPromotion
-
In CPS Table,Assuming x is the points before this event came
- points_redeemed column will stay as it is
- points_returned column will become x+100
- current_points will become x-100
- cumulative_points will become x-100
-
If Ledger is enabled, Points Ledger table will have,
- DEBIT entry of 100 will be created against e3 event_log_id
- cps_id will be the one that is affected above
-
In PD table,For the points that were awarded on txn1
- one entry will be created with deduction_type as RETURN and points_deducted column will have 100.
- one entry will be created with deduction_type as REDEMPTION_REVERTED and points_deducted column will
have 100 - points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
- event_log_id will the id as e3
-
For the PABP negative points that was created
- one entry will be created with deduction_type as REDEEMED and points_deducted column will have 100.
- points_awarded_ref_id will point to the PABP_id which got affected
- points_awarded_ref_type will be POINT_AWARDED_BILL_PROMOTION
- event_log_id will the id as e3
EXPIRY_REVERTED
-
The points allocated to the user for any transaction are expired .
-
After this the user returns the transaction on which he had earned these points
-
Scenario:
- Customer made a txn 1 of Rs.1000 on 1st Feb and got 100 points.
- The points expired on 10th Feb.
- Customer returns txn1 on 12th Feb
-
Effect:
- All points that expire will appear in the Point Fact at the granularity of the awarded_ref_id against which the
expired points have been reverted. - The column dim_deduction_type_id will be 8 referring to enum EXPIRY_REVERTED
- All points that expire will appear in the Point Fact at the granularity of the awarded_ref_id against which the
-
On 12th Feb ,In event_log table
- new entry will be created with event_type_id as 5 ---- e3.
-
In PA Table,
- expired_exclusive_value will become x-100
- returned_exclusive_value will become x+100
- status column will change from EXPIRED to RETURNED
- points_exclusive stay as it is.
-
In CPS Table,Assuming x is the points before this event came
- points_expired column will become x-100
- points_returned column will become x+100
- current_points will stay as it is
- cumulative_points will stay as it is
-
If Ledger is enabled, Points Ledger table will have,
- DEBIT entry of 100 will be created against e3 event_log_id
- cps_id will be the one that is affected above
-
In PD table,For the points that were awarded on txn1
- one entry will be created with deduction_type as RETURN and points_deducted column will have 100.
- one entry will be created with deduction_type as EXPIRY_REVERTED and points_deducted column will have
100 - points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
- event_log_id will the id as e3
REDEEMED_BY_TRANSFER
-
One user (sender) transfers the points to another user (receiver).
-
Points are deducted from the sender's entry
-
Scenario:
- Customer made a txn 1 of Rs.1000 on 1st Feb and got 100 points.
- Customer transfers 100 points to his friend on 5th Feb
-
Effect:
-
On 5th Feb,
-
In event_log table
- new entry will be created with event_type_id as 24 -->Sender is c1, Receiver is c2.
-
In PA table,
- points_exclusive column for c1 will stay 100
- redeemed_exclusive_value column for c1 will become 100
- status column for c1 will change from AVAILABLE to REDEEMED
-
In PACP table
- new entry will be created for c2
- points_value column will be 100
- status will be AVAILABLE
- promotion_id will be DefaultPointsTransferPromotion
- event_log_id column will have the id generated in above event_log table
-
In CPS table,
-
For c1,
- current_points column will reduce by 100
- cumulative_points column will stay the same
- points_redeemed column will become x+100
-
For c2
- current_points column will increase by 100
- cumulative_points will become x+100
-
If Ledger is Enabled,
-
For c1
- an entry_type column with DEBIT will be created
- points column will be 100
- event_log_id column will have the id generated in the above event_log table
-
For c2
- an entry_type column with CREDIT will be created
- points column will be 100
- event_log_id column will have the id generated in the above event_log table
-
In PD table,
-
For c1
- an entry will be created with deduction_type as REDEEMED_BY_TRANSFER
- points_deducted column will have 100.
- event_log_id will the id generated in the above event_log table
- points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
-
In PRS table,
-
For c1
- an entry will be created with redemption_type column as REDEEMED
- points_redeemed column will have 100
- redemption_id column will have a unique id generated by loyalty
- bill_id column will be -1 / bill_number column will be empty
- event_log_id will the id generated in the above event_log table
-
In PTS table, a new entry will be created
- from_customer_id column will be c1
- to_customer_id column will be c2
- points_deducted column will have 100
- points_credited column will have 100
- event_log_id will the id generated in the above event_log table
REDEEMED_BY_TRANSFER_REVERTED
-
This appears when there is a points transfer made from an account, following which the transaction where the points were awarded is returned.
-
Scenario:
- Customer made a txn 1 of Rs.1000 on 1st Feb and got 100 points.
- Customer transfers 100 points to his friend on 5th Feb
- Customer returns txn 1 on 7th Feb
-
Effect:
-
On 7th Feb,
-
In event_log table,
- an entry will be created with event_type_id as 5 ----- e3
-
In PA Table,
- redeemed_exclusive_value will become x-100
- status column will change from REDEEMED to RETURNED
- points_exclusive column will stay as its
- returned_exclusive column will become x+100
-
In CPS Table,
- current_points will become x-100
- points_returned wil become x+100
- cumulative_points will become x-100
-
If Ledger is enabled, Points Ledger table will have,
- DEBIT entry will be created with 100 against e3 event_log_id
- cps_id will be the one that is affected above
-
In PD table,
-
For the points that were transferred
- one entry will be created with deduction_type as RETURN and points_deducted column will have 100.
- one entry will be created with deduction_type as REDEEMED_BY_TRANSFER_REVERTED and points_deducted
column will have 100 - points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
- event_log_id will the id as e3
-
For the PABP negative points that was created
- one entry will be created with deduction_type as REDEEMED_BY_TRANSFER and points_deducted column will
have 100. - points_awarded_ref_id will point to the PABP_id which got affected
- points_awarded_ref_type will be POINT_AWARDED_BILL_PROMOTION
- event_log_id will the id as e3
- one entry will be created with deduction_type as REDEEMED_BY_TRANSFER and points_deducted column will
REDEEMED_BY_CONVERSION
-
This scenario is used when delayed accrual is enabled.
-
When the delay is over, a bulk job runs and converts promised points into regular points.
-
This is when a RBC entry is made in the deduction table pointing to the PA entry of those points, but with
points category Promised Points . -
After this, a new PA entry is made in the PA table but with points category Regular points.
-
Scenario:
- Customer made a txn 1 of Rs.1000 on 1st Feb which has a return period of 5 days and got 100 points in his
promised points account - Customer has not returned the txn till 6th Feb midnight
- 100 points get converted on 7th Feb morning
- Customer made a txn 1 of Rs.1000 on 1st Feb which has a return period of 5 days and got 100 points in his
-
Effect:
-
On 7th Feb,
-
In event_log table,
- an event record will be inserted with event_type_id as 16. The id generated here is event_log_id
-
In PA table,
For the existing record with DelayedAccrualPointCategory:- points_exclusive column will have 100 points,
- redeemed_exclusive_value column will become 100 points
- status column will be marked as CONSUMED_BY_CONVERSION
A new record will be created in PA for Main Point Category:
- points_exclusive column will have 100
- event_log_id with id generated in the event_log table above
- status will be AVAILABLE
- rest all columns will be copied from the existing record of DelayedAccrualPointCategory
-
In CPS table, For the DelayedAccrualPointCategory,
-
current_points will become x - 100 (x is the current_points before this event)
-
cumulative_points won't change
-
points_redeemed will become x + 100
For the Main Point Category,
-
current_points will become x + 100 (x is the current_points before this event)
-
cumulative_points column will become x + 100
-
-
If Ledger is enabled, PL table will have an
For DelayedAccrualPointCategory- entry of 100 points with entry_type as DEBIT
- cps_id which got affected in the above CPS
- event_log_id with id generated in the event_log table
For Main Point Category
- entry of 100 points with entry_type as CREDIT
- cps_id which got affected in the above CPS
- event_log_id with id generated in the event_log table
-
In PD table, an entry will be created with
- deduction_type column as REDEEMED_BY_CONVERSION
- points_deducted will be 100
- event_log_id with id generated in the event_log table
- points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
RETURN_HISTORICAL
-
This is used to adjust for partial returns that happen to promised points prior to conversion
-
Scenario:
- Customer made a txn 1 of Rs.1000 on 1st Feb which has a return period of 5 days and got 100 points in his
promised points account - Customer has returned Rs. 500 for the txn on 4th Feb
- 50 points get converted on 7th Feb morning
- 50 points get returned as RETURN_HISTORICAL
- Customer made a txn 1 of Rs.1000 on 1st Feb which has a return period of 5 days and got 100 points in his
-
Effect:
-
On 4th Feb,
-
In event_log table
- new entry will be created with event_type_id as 5, with id as e1
-
In PA table,
- points_exclusive column will stay 100
- returned_exclusive_value column will become 50
- status column will stay as AVAILABLE
-
In CPS table,
- current_points column will reduce by 50
- cumulative_points column will stay the same
- points_returned column will become x+50
-
If Ledger is Enabled,
- an entry_type column with DEBIT will be created
- points column will be 50
- event_log_id column will have e1
-
In PD table,
- an entry will be created with deduction_type as RETURN
- points_deducted column will have 50.
- event_log_id will the id as e1
- points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
-
On 7th Feb,
-
In event_log table
- new entry will be created with event_type_id as 5, with id as e2
-
In PA table, For the existing record with DelayedAccrualPointCategory:
- points_exclusive column will stay as 100,
- returned_exclusive_column will stay as 50
- redeemed_exclusive_value column will change to 50 points
- status column will be marked as CONSUMED_BY_CONVERSION
A new record will be created in PA for Main Point Category:
- points_exclusive column will have 100
- returned_exclusive_value column will have 50
- event_log_id with id as e2
- status will be AVAILABLE
- rest all columns will be copied from the existing record of DelayedAccrualPointCategory
-
In CPS table, For the DelayedAccrualPointCategory,
-
current_points will become x - 50 (x is the current_points before this event)
-
cumulative_points won't change
-
points_returned column will stay as it is
-
points_redeemed will become x + 50
For the Main Point Category,
-
current_points will become x + 50 (x is the current_points before this event)
-
cumulative_points column will become x + 100
-
points_returned column will become x + 50
-
-
If Ledger is enabled, PL table will have an
For DelayedAccrualPointCategory
- entry of 50 points with entry_type as DEBIT (mapped to delayed point category)
- cps_id which got affected in the above CPS
- event_log_id with id as e2
For Main Point Category
- entry of 50 points with entry_type as CREDIT
- cps_id which got affected in the above CPS (mapped to main point category)
- event_log_id with id as e2
-
In PD table, For the existing DelayedAccrualPointsCategory entry
- deduction_type column as REDEEMED_BY_CONVERSION
- points_deducted will be 50
- event_log_id with id as e2
- points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
For the Main Point Category
- deduction_type column as RETURN_HISTORICAL
- points_deducted will be 50
- event_log_id with id as e2
- points_awarded_ref_id will point to the PA_id which got affected
- points_awarded_ref_type will be POINTS_AWARDED (if points were on bill)
Updated 8 months ago