My Oracle Support Banner

CopyTo Rule Updates To Entities Are Causing Null Values To Be Passed To ODS Target In Error (Doc ID 2368201.1)

Last updated on FEBRUARY 21, 2019

Applies to:

Oracle Insurance Policy Administration Operational Data Store for Life and Annuity - Version and later
Information in this document applies to any platform.


On : version, ODS Data Sync

Client has noticed that some of the target fields are null. They expect these fields to have values and can see values in OIPA(source) and Stage. Did some analysis and found a policy with this problem. The null values are on the producer role field table.

Please see the attached spreadsheet.
Tab OIPA_Screen_Shows shows the producer role fields role for a policy. Notice the primary producer role field checkbox is checked and there is a value for the Producer ID. Scroll down to see the policy activities. Here the PolicyPage activity is expanded and has a timestamp of 01/29/2018 10:39:57 EST. This is after the last sync (29-JAN-18 AM ) as shown in tab Stage_ODSCode. Scroll down a little more and you see the ActivityXML for that activity. This transaction has a CopyToRoleFields attached. It is updating the SecondaryRoleStatus.
Tab StageAsTables shows role fields for the policy. The RoleGUID, LAST_MODIFIED_TIME and TEXTVALUE are highlighted for the role that was updated during the PolicyPage activity.
Tab StageAsTables_SQL shows the SQL.
Tab StageView shows the values the view selects. The sync hadn’t run yet. Notice that only the SECONDARYROLESTATUS has a value.
Tab StageView_SQL shows the SQL and the create DDL for the view.
Tab Stage_ODSCode shows the last sync time.
Tab Stage_ODSCode_SQL shows the SQL.
Tab Target_BEFORE_SYNC shows the target vales before the sync ran. Notice that the fields PRODUCERID, SECONDARYROLESTATUS, ROLESEQUENCENUMBER and ROLELEVELINDICATOR are populated for the role.
The sync ran and then I captured the Target again.
Tab Target_AFTER_SYNC shows the target vales after the sync ran. Notice that only the field SECONDARYROLESTATUS is populated for the role. Fields PRODUCERID, ROLESEQUENCENUMBER and ROLELEVELINDICATOR are null.
Tab Target_SQL shows the SQL.

I think the issue is in the View. It is looking for those dynamic fields where the last_modified_time is greater than or equal to the last sync and it is ignoring those other dynamic fields. Nationwide has noticed this on the PolicyFields table in addition to the RoleFields. I expect that it will happened on any dynamic table where update can able to individual rows and not all rows.

The issue can be reproduced at will with the following steps:
Use copyto rules to update entity fields. Attached details on the exact fields being nulled

The issue has the following business impact:
Due to this issue, Inconsistent data in the ODS target schema is causing significant impact to downstream systems extracting data


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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