SAP Sales Rapid Mart v3.2
Data Servies 12.2.2
ECC 6.0
-We have been able to get an initial load run fine outside a single warning that we will troubleshoot later.
-However, on a DELTA run, we can’t seem to identify why we are getting PK contratint errors on WF_OntimeDeliveryFact.
-The problem resides specifically on the target table ONTIME_DELIVERY_HISTORY. This table is to be appended with new rows fed from dataflows invoving ONTIME_DELIVERY_HISTORY & ONTIME_DELIVERY_STAGE
-Logic resides in the script OntimeDelivTruncate to remove specific rows with CHANGE_STATUS_FLAGS set to ‘DELETE’. This is where we have yet to find how that status is getting changed so we can hopefully determine why this is happening.
If anyone has run into this issue with the Sales RM…would appreciate any feedback.
The Status “DELETE” is determined in data flow DF_OnTimeDelivStage3_SAP. On the first run of Ontime Delivery, all rows are assigned status “NEW”.
On a Delta load the ONTIME_DELIVERY_STAGE initially loaded with status of “NO CHANGE”. Then, ONTIME_DELIVERY_STAGE is compared (Table Comparison) to the last snapshot ONTIME_DELIVERY_FACT. This happens in DF_OnTimeDelivStage3_SAP. Rows that have been Added, Changed, or Deleted since the last run have status updated accordingly.
Things to watch out for in this process:
If your target database is SQL-Server make sure the database is Case-sensitive.
Make sure the primary key of ONTIME_DELIVERY_HISTORY includes the LOAD_DATE column. This will distinguish otherwise identical rows
Make sure the LOAD_DATE columns on all ONTIME tables include the timestamp.
Check that you’ve properly set the $G_ONTIME_DELIVERY_WINDOW variable for your need. Default is 15 days.
Remove the primary key on the ONTIME_DELIVERY_HISTORY table. Run the Delta job. It should now insert the duplicate rows without error.
Then do a group by query (see below) to find out which of the rows are being duplicated. This should help you identify the source of the issue
select
count(),
SALES_DOC_ID,
SALES_LINE_ITEM_ID,
SCHED_LINE_ITEM_ID,
DELIV_ID,
DELIV_LINE_ITEM_ID,
LOAD_DATE
from
ONTIME_DELIVERY_HISTORY
GROUP BY
SALES_DOC_ID,
SALES_LINE_ITEM_ID,
SCHED_LINE_ITEM_ID,
DELIV_ID,
DELIV_LINE_ITEM_ID,
LOAD_DATE
HAVING
COUNT() <> 1;
(BOB member since 2006-04-06)