SIL_RETAILSALESPROMOTIONFACT Batch Is Doubling the Values for Transactions Containing the Same Item More Than Once (Doc ID 1592361.1)

Last updated on AUGUST 16, 2022

Applies to:

Symptoms

 The Retail Analytics (RA) Sales Promotion batch (SIL_RETAILSALESPROMOTIONFACT, or slsprildsil.ksh) is doubling the values for transactions containing the same item more than once.

Steps to Recreate:

1. Create a Sales transaction in Retail Sales Audit (ReSA).  The transaction should contain two lines for the same item and there should be the same discount for both the lines.
2. Run the saexpdw batch to get the RDWT file. 
3. Run the resa2dw perl script to get the RDWT file in RA format. 
4. Run the SDE for Retail Sales (SDE_RETAIL_SALESTRANSACTIONFACT, or slsiltsde.ksh).  This will populate the Sales Promo Staging table W_RTL_SLSPR_TX_IT_LC_DY_FS and Sales Retail Staging table W_RTL_SLS_TRX_IT_LC_DY_FS.   Observe that data is not grouped at item, location, and day level. Hence there are two records each for this transaction in both the staging tables. 
5. Run the SIL for Promotion Fact (SIL_RETAILSALESPROMOTIONFACT, or slsprildsil.ksh).  Observe the records in the W_RTL_SLSPR_IT_LC_DY_TMP table. There are four records in this table. This is because there is a join between the Sales Retail Staging table W_RTL_SLS_TRX_IT_LC_DY_FS and Sales Promo Staging table W_RTL_SLSPR_TX_IT_LC_DY_FS on Transaction ID, Item, Day, and Location. This is resulting in multiple records for the same one transaction containing two lines for the same item. 
6. Observe the records in the fact table W_RTL_SLSPR_IT_LC_DY_F.  The values posted in the table are double the original value.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.