Example for Versioning

This example shows you the behavior of the system for the entries in the versioning fields for a business partner. For this simplified example, we assume that initially both current and history tables (sap.fsdm::BusinessPartner and sap.fsdm::BusinessPartner_Historical) are empty. Decimal digits are shortened.

Data Load

The following helper SQL procedure is used to load data for this example. In a productive environment, you use the write procedures of the interface generator:

Sample Code

-- Helper Procedure to write data
create procedure insert_bp_example(IN BusinessPartnerID NVARCHAR(128), BusinessValidFrom date, BusinessValidTo date, Name NVARCHAR(256))
	LANGUAGE SQLSCRIPT SQL SECURITY DEFINER AS
BEGIN
	declare tab_in_proc "sap.fsdm.tabletypes::BusinessPartnerTT";
	declare tab_input TABLE("BusinessPartnerID" NVARCHAR(128), "BusinessValidFrom" date, "BusinessValidTo" date, "Name" NVARCHAR(256));

	tab_input = SELECT BusinessPartnerID as "BusinessPartnerID", BusinessValidFrom as "BusinessValidFrom", BusinessValidTo as "BusinessValidTo", Name as "Name" from dummy;

	:tab_in_proc.("BusinessPartnerID", "BusinessValidFrom", "BusinessValidTo", "Name").insert(:tab_input);
	call "sap.fsdm.procedures::BusinessPartnerLoad"(:tab_in_proc);
end;

Initial loading with input write procedure: Initial version of the business partner John Doe (ID 4711), valid in the application dimension from January 01, 2000. This leads to an insert in the database. System time when calling the write procedure is May 4th, 2020.

Input Data
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	Name
4711	2000-01-01	9999-12-31	John Doe

Sample Code

call insert_bp_example('4711' , to_date('2000-01-01') , to_date('9999-12-31') , 'John Doe' );

Current Table "sap.fsdm::BusinessPartner"
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2000-01-01	9999-12-31	2020-05-04 00:00:00	9999-12-31 23:59:59	John Doe

The history table stays empty.

Correction of the business partner with the input write procedure: The name was wrong and has to be corrected. System time when calling the write procedure is May 6th, 2020.

Input Data
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	Name
4711	2000-01-01	9999-12-31	Richard Doe

Sample Code

call insert_bp_example('4711' , to_date('2000-01-01') , to_date('9999-12-31') , 'Richard Doe' );

Current Table "sap.fsdm::BusinessPartner"
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2000-01-01	9999-12-31	2020-05-06 00:0 0:00	9999-12-31 23:59:59	Richard Doe

History Table "sap.fsdm::BusinessPartner_Historical"
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2000-01-01	9999-12-31	2020-05-04 00:0 0:00	2020-05-06 00:00:00	John Doe

A new version comes from the source system with validity starting on May 10th, 2020 and a name change due to marriage. The system time when calling the write procedure is May 10th, 2020 00:00:00. The old record has to be delimited and a new version inserted.

Input Data
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	Name
4711	2020-05-10	9999-12-31	Richard Doe-Roe

Sample Code

call insert_bp_example('4711' , to_date('2020-05-10') , to_date('9999-12-31') , 'Richard Doe-Roe' );

Current Table "sap.fsdm::BusinessPartner"
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2000-01-01	2020-05-10	2020-05-10 00:00:00	9999-12-31 23:59:59	Richard Doe
4711	2020-05-10	9999-12-31	2020-05-10 00:00:00	9999-12-31 23:59:59	Richard Doe-Roe

History Table "sap.fsdm::BusinessPartner_Historical"
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2000-01-01	9999-12-31	2020-05-06 00:00:00	2020-05-10 00:00:00	Richard Doe
4711	2000-01-01	9999-12-31	2020-05-04 00:00:00	2020-05-06 00:00:00	John Doe

Backdated changes come from the source system with a validity starting on May 8th, 2020 because the name change to Richard Doe-Roe was in fact two days earlier than provided in the version before. The system date when calling the write procedure is May 12th, 2020.

Input Data
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	Name
4711	2020-05-08	9999-12-31	Richard Doe-Roe

Sample Code

call insert_bp_example('4711' , to_date('2020-05-08') , to_date('9999-12-31') , 'Richard Doe-Roe' );

Current Table "sap.fsdm::BusinessPartner"
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2000-01-01	2020-05-08	2020-05-12 00:00:00	9999-12-31 23:59:59	Richard Doe
4711	2020-05-08	9999-12-31	2020-05-12 00:00:00	9999-12-31 23:59:59	Richard Doe-Roe

History Table "sap.fsdm::BusinessPartner_Historical"
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2000-01-01	2020-05-10	2020-05-10 00:00:00	2020-05-12 00:00:00	Richard Doe
4711	2020-05-10	9999-12-31	2020-05-10 00:00:00	2020-05-12 00:00:00	Richard Doe-Roe
4711	2000-01-01	9999-12-31	2020-05-06 00:0 0:00	2020-05-10 00:00:00	Richard Doe
4711	2000-01-01	9999-12-31	2020-05-04 00:0 0:00	2020-05-06 00:00:0	John Doe

The business partner is no longer valid as of May 18th, 2020 and has to be archived. The system date when calling the delete procedure is May 20th, 2020.

Input Data
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	Name
4711	2020-05-18	9999-12-31	Richard Doe-Roe

Sample Code

do begin 
declare tab_input "sap.fsdm.tabletypes::BusinessPartnerTT_Del"; 
    tab_input = SELECT '4711' as "BusinessPartnerID", to_date('2020-05-18') as "BusinessValidFrom", to_date('9999-12-31') as "BusinessValidTo" from dummy; 
    call "sap.fsdm.procedures::BusinessPartnerDelete"(:tab_input); 
end;

Current Table "sap.fsdm::BusinessPartner"
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2000-01-01	2020-05-08	2020-05-12 00:00:00	9999-12-31 23:59:59	Richard Doe
4711	2020-05-08	2020-05-18	2020-05-20 00:00:00	9999-12-31 23:59:59	Richard Doe-Roe

History Table "sap.fsdm::BusinessPartner_Historical"
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2020-05-08	9999-12-31	2020-05-12 00:00:00	2020-05-20 00:00:00	Richard Doe-Roe
4711	2000-01-01	2020-05-10	2020-05-10 00:00:00	2020-05-12 00:00:00	Richard Doe
4711	2020-05-10	9999-12-31	2020-05-10 00:00:00	2020-05-12 00:00:00	Richard Doe-Roe
4711	2000-01-01	9999-12-31	2020-05-06 00:00:00	2020-05-10 00:00:00	Richard Doe
4711	2000-01-01	9999-12-31	2020-05-04 00:00:00	2020-05-06 00:00:00	John Doe

Reporting

Based on the final data of May 20th, 2020, you can perform the following queries:

Select the currently valid version on May 25th, 2020:
Sample Code
```
SELECT * FROM "sap.fsdm.SQLViews::BusinessPartner_View" FOR APPLICATION_TIME AS OF current_date
```
Since the data was deleted starting from May 18th, 2020, the system will read no data.

Select a specific application time version for May 8th, 2020:

Sample Code

SELECT * FROM "sap.fsdm.SQLViews::BusinessPartner_View" FOR APPLICATION_TIME AS OF to_date('2020-05-08')

The system finds the following data record:

Result
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2020-05-08	2020-05-18	2020-05-20 00:00:00	9999-12-31 23:59:59	Richard Doe-Roe

Select a specific application time version (valid on May 8th, 2020) at May 09th, 2020 at 12:00 p.m. system time:

Sample Code

SELECT * FROM "sap.fsdm.SQLViews::BusinessPartner_View"  FOR SYSTEM_TIME AS OF '2020-05-09 12:00:00' FOR APPLICATION_TIME AS OF to_date('2020-05-08')

The system finds the following data record:

Result
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2000-01-01	9999-12-31	2020-05-06 00:00:00	2020-05-10 23:59:59	Richard Doe

Select a specific application time version that was valid in a range on the system time period:

Sample Code

SELECT * FROM "sap.fsdm.SQLViews::BusinessPartner_View"
	 FOR SYSTEM_TIME FROM '2020-05-03 12:00:00' To '2020-05-07 12:00:00' 
	FOR APPLICATION_TIME AS OF to_date('2020-05-01')

The system finds the following data records:

Result
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2000-01-01	9999-12-31	2020-05-06 00:00:00	2020-05-10 23:59:59	Richard Doe
4711	2000-01-01	9999-12-31	2020-05-04 00:00:00	2020-05-06 23:59:59	John Doe

Select a range of application times:

Sample Code

SELECT "Name", * FROM "sap.fsdm.SQLViews::BusinessPartner_View"
WHERE "BusinessValidFrom" < to_date( '2020-05-10' ) and 
       "BusinessValidTo" > to_date('2020-05-04');

The system finds the following data records:

Result
BusinessPartnerID	BusinessValidFrom	BusinessValidTo	SystemValidFrom	SystemValidTo	Name
4711	2000-01-01	2020-05-08	2020-05-12 00:00:00	9999-12-31 23:59:59	Richard Doe
4711	2020-05-08	2020-05-18	2020-05-20 00:00:00	9999-12-31 23:59:59	Richard Doe-Roe

More Information

For more information, see the related entities and their definitions in the data model represented in SAP PowerDesigner.