In the previous article Part 1, we completed to setup API Sync Settings.
Let's continue to setup Database view for below objects(shown in image)
The Sync to Commissions requires that the source data come from a database view created from within Producer Manager. The view should contain all the required fields. You can join different tables if more information is needed for sync.
For example, join the Producer and ProducerVer tables for a Producer view. The view should return the primary key value that is captured in the EntValue field of the Audit table.
For example, if a user makes a change to the producer's data in Producer Manager then an audit record will be created with Audit.EntName equal to "ProducerVer" and Audit.EntValue equal to the primary key value of the record modified.
In our example, EntValue would contain the value of ProducerVer.ProducerVerNo of the record modified. Also, for versioned Producer Manager entities like ProducerVer in our example, the view should also return the versioned table's parent entity's primary key.
In our example here that would be Producer.ProducerNo.
Below are the steps to create a database view in Producer Manager (Note: It is important to know what database back end is being used for your Producer Manager environment. The SQL syntax for creating the database views can vary between HANA and Oracle.😞
Navigate to the Database View Configuration screen via the Administratorportal and then the Entity Configuration tab menu option.
Under Database View screen click the Newbutton.
On the next screen give the view a name.
Next, paste your select query for the view into the ViewSelectSql box and click Save
Click the Activatebutton at the bottom
Navigate back to the Database View search screen and click the Sync DB button. On the next screen click the Run button. This will add your new view to the database to make it available to the sync configurations.
Note: You may want to use the SQL Workbench to get the query working first.
As this task needs to be handled manually, please configure as per user requirement to build SQL