As fast as we can design, map, and autogen target conversions in dspMigrate and have the ability to generate config check reports, we do not have a process to autogen preload and postload reports. At 2 of our clients I have used the the attached sql code to automate the creation of the following reports. The prerequisite is that each target table contains the following fields: zActive, zInSAP, zError, zLoaded. This will also help enforce a standard methodology across our validation efforts.
Detail Explanation of Attached File
Each target will need individual rules to mark any records inactive, mark the records that already exist in SAP by the primary key of the table, and a zError update where you could flag any records that will fail to load due to missing config, invalid values, etc. The zLoaded flag is a postload rule that will drive the delta load when its not flagged.
zActive - By Default it is set to 1, any rules to inactive records set this value to 0. When zActive = 0 this drives the "Exclusion" report.
zInSAP - Use the SAP primary keys to determine the records that already exist in SAP. This will drive the "Already Loaded" report
zError - When zInSAP = 0 and zActive = 1 flag any records that exist on any of the target error reports that would prevent the data from loading. This drives the "Error" report (Also called Exception in my slide, but i like "Error Report" name better.
When zActive = 1, zInSAP = 0, and zError = 0, then all records go to "Ready to Load" report
The reports should look like the following layout:
tv****_Preload_Exclusion_Sel - A list of all the Key and SAP fields on the target table where zActive = 0
tv****_Preload_Error_Sel - A list of the Key fields and SAP fields on the target table where zActive = 1 and zError = 1
tv****_Preload_Error_Detail_Sel - A list of the Key fields and a description column using the target reports description for where each record appears on the report. The same key record can appear multiple times for multiple different errors where zActive = 1 and zError = 1
tv****_Preload_AlreadyLoaded_Sel - Join the target table to sap table view in the database. List the Key information and then each SAP field Field1_Expected, Field1, Field2_Expected, Field2
-Field1 Expected is the target table value and the plain name is what is already in SAP. this allows the user to see mismatches between what conversion has and what the SAP environment has for each relevant field for conversion
tv****Preload_ReadyToLoad_Sel - Where zActive = 1, zInSAP = 0 and zError = 0 - Show all the target table Key and SAP fields.
After the load, zLoaded will be set to 1 where zActive = 1, zInSAP = 0
tv*****Postload_Success_Sel - Where zLoaded = 1 Show all fields similar to the "already loaded" where you have the Field1_Expected, Field1..etc
tv****Postload_NotLoaded_Sel - Where zActive = 1, zInSAP = 0, zError = 0, zLoaded = 0. This is everything in the preload ready to load that did not load. This gives us indication when we send out the records to load and then we get the results back what we thought should load but didn't. These we need to research for the failed cause and we should have error reports created to catch the reasons why these didn't load. We would always "expect" this report to have 0 records because all the records should have been caught in the tv****Preload_Error_Sel report.