Understanding the difference between SYNC and FULLSYNC integrations

If you are running two or more different PeopleSoft applications you have probably heard of the SYNC and FULLSYNC integrations. These are Integration Broker Services Operations that Oracle delivers to keep different database tables in sync. For example, if your organization owns Financials and HCM then you may have either the DEPT_SYNC and/or the DEPT_FULLSYNC setup to flow from Financials to HCM. When a new Department is added or updated in Finance, then you want that same update available in HCM. The DEPT_SYNC is a near real-time synchronization. When a update is made in Finance, then HCM knows about it in near real-time. The DEPT_FULLSYNC is generated with a batch process and pushes the full table to the subscribing system(s). The FULLSYNC can have potential down stream negative impacts in the subscribing HCM system which we will discuss in detail.

It is important to understand the difference between SYNC and FULL_SYNC integrations and the nuances between the two. Often times you need a combination of both. However, in some instances you can’t use both. We will explore SYNC and FULLSYNC operations in depth in this article.

Understanding SYNC Operations

Let’s first get an understanding of SYNC operations. You setup a SYNC service operation to publish from one PeopleSoft systems to another “subscribing” system. For the sake of this post, we are going to limit the discussion to a PeopleSoft to PeopleSoft integration. However, the subscribing system does not have to be a PeopleSoft system.

Oracle delivers many different SYNC operations for both setup tables and transactional tables.

Some examples of Setup table SYNCs are:

• BUS_UNIT_FS_SYNC
• CHARTFIELD1_SYNC
• COMPANY_SYNC
• DEPT_SYNC

Some examples of transactional SYNCs are:

• PERSON_ACCOMP_SYNC
• PERSON_BASIC_SYNC
• PERSON_DISABILITY_SYNC

You setup SYNC integrations people two systems when you need a near real-time integration of the data. For example, if you are integrating HCM 9.1 and Campus Solutions 9.0 then you will likely enable the PERSON_BASIC_SYNC from Campus Solutions to HCM. In this scenario, when a person is updated in Campus Solution, you want HCM to know about it immediately.

SYNC Publication Characteristics

Here are some general publication characteristics of SYNC operations:

• SYNCs are near real-time. They are “asynchronous” but are generally finished publishing in a few seconds.
• The code that publishes the SYNC service operation is generally in the SavePostChange event of the component.
• Since the publication happens during the PeopleSoft component save, you will often have a scenario where several messages are published for one “update” if the save is pressed several times.
  • For example, lets say that a user opens an employee record, updates the address, presses save, updates the birth country, presses save, then updates the name, then presses save. In this case, three different service operation for the PERS_BASIC_SYNC will be published because three different saves were performed.
• A SYNC service operation is published with a record called PSCAMA for each record in the message. The PSCAMA record has a very important field named AUDIT_ACTN which the subscribing system uses to figure out what to do with the row. The publishing system will set these automatically. Here are the values:

• SYNCs cannot always handle deletes and key changes.
  • Data between the subscribing system and publishing system can often get out of sync. We will cover exactly why when we look at the subscription characteristics, next.
• SQL Updates on the underlying SYNCed tables will not be replicated. For example, if you have the PERSON_BASIC_SYNC service operation publishing from a Campus Solutions (CS) database, any updates done via SQL will not trigger the SYNC service operation to the subscribing system. If you have some batch process that corrects addresses in the CS database via SQL then your subscribing systems will not know about it. This can cause all sorts of unforeseen problems.
  • You should always use Component Interface or the web browser to update tables that are being SYNCed. This will ensure the service operations are triggered.
• It is possible that bugs in the publishing code may not trigger a service operation. There are times where certain types of updates may not trigger a SYNC operation. These are typically bugs but they can happen.

SYNC Subscription Characteristics

Here are some general characteristics of SYNC subscription operations.

• In the subscribing PeopleSoft systems, SYNC operations are handled using an “OnNotify” subscription handler. This is just a piece of Application Package PeopleCode that you enable to run when a message comes in. Its job is to look at the message and determine what do with it. It can choose to ignore the message or do any sort of processing.
• The subscription handler could do straight SQL Inserts, thus bypassing the subscribing system’s component processor. Alternatively, it could call a Component Interface to update the system. It is just PeopleCode so anything you can do in PeopleCode can be done in the subscription handler.
  • Each service operation is different and unique and you have to look at the code that Oracle delivers with each one to determine the update method used as Oracle has not documented these very well.
• Several of the delivered subscription handlers are actually coded to error out if an effective date is changed or a row is deleted.

This last bullet is very important and it needs some further explanation. As an example, we will look at the Application Package LOCATION_SYNC.LocationSync and specifically focus in on the ProcessMsgUsingBusComp method. This is the called from the “OnNotify” call stack and will be responsible for invoking a component interface to update the system with what is in the message. We are going to abstract out some of the code so we do not get caught up in all the details. A common pattern you will see in many “SYNC” subscription handlers is some code that looks like this.

• Loop through each RowSet in the incoming message
  • For each row, look at the PSCAMA.AUDIT_ACTN value
    • When AUDIT_ACTN = A or N :=> Insert a new row
    • When AUDIT_ACTN = C :=> Update the existing values
    • When AUDIT_ACTN = D or K :=> Hard error

This last scenario listed for D and K AUDIT_ACTN values is a hard error. This makes the subscription service operation go to an error status. The subscription operations will need to be dealt with in the Asynchronous monitor manually and they cannot be ignored. If they are ignored they can actually stop other messages from flowing depending on the queue partitioning.

Why are these subscription handlers coded to error out on “D” (Row deletes) and “K” (Old value for key changes)? The hard coded error is basically forcing a human to look at the particular update that was made in the publishing system and determine the appropriate action in the subscribing systems. It is basically saying:

There are several scenarios that could have occurred in the publishing system. Some of them are dangerous. I am going to take the safe route and just force someone to look at this and deal with it manually so I don’t cause data corruption.

Let’s look at an example transaction in the publishing system.

• A new department is created in your Finance database with an effective date of January 1, 2010.
• The new department is SYNCed to your HCM system.
• New employees are hired into that Department starting on January 1, 2010, since it is a valid value as of that date.
• An analyst in the Financials system decides that the department was activated too early and goes into correction mode and changes the effective date from January 1, 2010 to July 1, 2010.
• The SYNC operation will actually send two rows in the message:
  • An “N” (new key value) with the July 1, 2010 row.
  • An “K” (OLD key value) with the January 1, 2010 row
• The HCM subscribing system will actually try to insert the July 1, 2012 “N” row. However, when it encounters the “K” January 1, 2010 row it will throw a hard error and the subscription process will stop and the message will not be processed.
• At this point someone will have to look at the error and manually sync the department table.
  • Generally, the administrator will cancel the error message and report it to the functional users. If the functional users do nothing, then the data will be out of sync. If the functional users do not accurately synchronize the data, then it will be out of sync.

In the example above, if the subscription code would have just made the change to the effective date (which is technically possible) then you would run into a scenario where all the employees hired between January 1, 2010 and June 30, 2010 would have an invalid department. This is data corruption. This is one reason why the subscription handler will error out in this scenario. The possibility of data corruption is very dependent on many factors that are unique to your installation and usage of the table. It is hard for Oracle to provide code to handle every situation. Of course, they could have provided a setup table to determine this behavior but they have not taken it there yet but there is nothing stopping you from writing this code in a custom subscription handler.

• I would look at the vanilla subscription handlers as a starting point. If they do not work exactly the way your organization needs then I would clone them into a custom application package and implement the exact behavior you need.

Understanding FULLSYNC Operations

FULLSYNC service operations are a full table push of the data from the publishing system to a subscribing system. If your department table contains 1,000 rows then the DEPT_FULLSYNC will push over all of those rows to the subscribing system. This is something that can be done nightly, weekly, or monthly depending on the amount of table updates in the publishing system. Typically, the subscribing system does a full delete of the tables and then inserts all the new rows again. Lets look at both the publishing and subscribing pieces below.

FULLSYNC Publication Characteristics

• FULLSYNCs are published using a framework in the “Enterprise Components” module to setup the behavior of the FULLSYNC service operations.
  • Navigation: Enterprise Components > Integration Definitions > Full data publish rules
  • FULLSYNC service operations can be configured to push a “header” and “trailer” service operation to mark the beginning and end of the message stream. If a header record is sent, then typically the subscribing system will do a full truncation of the table on the subscription side. The trailer record is not always used on the subscribing system and is often not required. However, it does not hurt sending it.
• One common application engine is used to publish the data: EOP_PUBLISHT.
  • There is not a different application engine to publish the LOCATION_FULLSYNC and JOBCODE_FULLSYNC service operation. They are all handled with a common framework application engine. This allows you to easily setup and FULLSYNC custom tables.
  • Navigation: Enterprise Components > Integration Definitions > Initiate Process > Full Data Publish
• Of course the FULLSYNCs rely on a configured message, service, service operation and routings to be valid and active.
• If a header and trailer are configured, then you will typically have a minimum of 3 service operation sent.
  1. A header service operation to alert the subscribing system that a FULLSYNC message stream is coming. This will not have any data in the message other than the header marker.
  2. One or more “body” messages with the actual table contents. The full table contents may be broken up into several service operations depending on the size of the table and the application server settings.
  3. A trailer record to alert the subscribing system that all the messages are published and the transaction is completed and any final processing can be completed.

The publishing piece of FULLSYNCs are pretty straight forward.

1. First you setup the Service Operation and routings
2. Then you setup the FULLSYNC service operation in the “full data publish rules.”
3. Then you schedule the publication process.

You have to be careful with FULLSYNCs because the subscribing system will often truncate the target table and start from scratch. This can cause problems for the subscribing system if the target table has data from both the publishing system and from another system or values keyed by hand directly in the subscribing system. For example, Let’s imagine you have a large organization with many business units and different accounting packages like PeopleSoft and JD Edwards. Additionally, let’s imagine that you have one consolidated HR system. Your HCM system may have deparment values from both of those accounting systems. If the PeopleSoft accounting system sends a DEPT_FULLSYNC to your HCM system this can potentially delete the JD Edwards departments. Care has to be taken when sending FULLSYNCs.

FULLSYNC Subscription Characteristics

• In the subscribing PeopleSoft systems, FULLSYNC operations are handled using an “OnNotify” subscription handler. This is just a piece of Application Package PeopleCode that you enable to run when a message comes in. Its job is to look at the message and determine what do it.
• The subscription handler typically uses straight SQL inserts to insert the incoming data into the system.
• If a “header” row is sent along with the message, the target tables are truncated in preparation for the following “body” message(s).
  • The header record can also be used to prepare a temporary table for some FULLSYNC subscription handlers.
• Typically the trailer record is ignored. However, some FULLSYNC service operations use the trailer to move data from a staging table to the real tables or to finalize some data processing.
• I would look at the vanilla subscription handlers as a starting point. If they do not work exactly the way you want then I would clone them into a custom application package and implement the exact behavior you need.
• Keep in mind that the FULLSYNC service operations can also cause data corruption with effective dates just like the example we highlighted in the SYNC section where a department was setup with one effective date then employees were hired into that department. Afterward the date was moved forward in time and the employees were in an invalid department.

• It is good idea to make all security display only on the subscribing system’s setup page so user’s do not inadvertently add or update values that could be truncated.

Summary

The difference between SYNC and FULLSYNCs are subtle but important to understand. The technical and functional teams need to understand how your organization is using your system in order to determine what is the best integration for your organization.