If you are running two or more different PeopleSoft applications, you have probably heard of the SYNC and FULLSYNC integrations. These are Integration Broker Service Operations that Oracle delivers to keep different database tables in sync between PeopleSoft instances. It is important to understand the difference and the nuances between the two. Often you need a combination of both.
This page covers delivered Oracle
SYNC and
FULLSYNC data synchronization operations between PeopleSoft systems. For the general difference between synchronous and asynchronous interaction styles, see
Sync versus Async.
Understanding SYNC Operations
SYNC operations provide near real-time synchronization of data. When an update is made in the publishing system, the subscribing system knows about it within seconds.
Examples of delivered SYNC operations:
- Setup tables: BUS_UNIT_FS_SYNC, CHARTFIELD1_SYNC, COMPANY_SYNC, DEPT_SYNC
- Transactional tables: PERSON_ACCOMP_SYNC, PERSON_BASIC_SYNC, PERSON_DISABILITY_SYNC
SYNC Publication Characteristics
- SYNCs are near real-time asynchronous operations, generally finishing in a few seconds.
- The publication code is typically in the
SavePostChange event of the component.
- Multiple messages can be published for one “update” if save is pressed multiple times during one session.
- Each SYNC message includes a PSCAMA record with an
AUDIT_ACTN field:
| AUDIT_ACTN |
Meaning |
| A |
A row was added |
| C |
Correction mode update (non-key values changed, effective date NOT changed) |
| N |
A key value changed (typically the effective date was changed) |
| K |
“Sister” row accompanying an N row, containing the old key values |
| D |
A row was deleted |
| blank |
Nothing has changed |
- SYNCs cannot always handle deletes and key changes cleanly (see subscription characteristics below).
- SQL updates on SYNCed tables will NOT trigger service operations. Always use Component Interface or the web browser to update tables that are being SYNCed.
SYNC Subscription Characteristics
- Subscription handlers use an
OnNotify handler to process incoming messages.
- A common pattern in delivered subscription handlers:
- AUDIT_ACTN = A or N: Insert a new row
- AUDIT_ACTN = C: Update existing values
- AUDIT_ACTN = D or K: Hard error
The hard error on D and K values is intentional. It forces a human to evaluate the change because automatic processing could cause data corruption.
Example: Why D/K Causes a Hard Error
- A department is created in Finance with effective date January 1, 2010
- The department SYNCs to HCM
- Employees are hired into that department starting January 1, 2010
- An analyst changes the effective date from January 1 to July 1, 2010
- The SYNC sends an “N” row (July 1) and a “K” row (old January 1 values)
- The subscription handler errors on the “K” row
If the code had automatically changed the effective date, all employees hired between January 1 and June 30 would have an invalid department – that is data corruption. The hard error forces manual review.
Look at the vanilla subscription handlers as a starting point. If they do not work exactly the way your organization needs, clone them into a custom application package and implement the exact behavior you need.
Understanding FULLSYNC Operations
FULLSYNC operations push the full table from the publishing system to a subscribing system. If your department table has 1,000 rows, all 1,000 are sent. This typically runs nightly, weekly, or monthly.
FULLSYNC Publication Characteristics
- Published using the Enterprise Components framework:
- Navigation:
Enterprise Components > Integration Definitions > Full Data Publish Rules
- A common application engine (
EOP_PUBLISHT) publishes all FULLSYNCs
- Navigation:
Enterprise Components > Integration Definitions > Initiate Process > Full Data Publish
- FULLSYNCs can send a header and trailer service operation:
- Header - Alerts the subscriber that a FULLSYNC stream is coming (typically triggers table truncation)
- Body - One or more messages with the actual table data
- Trailer - Signals completion; may trigger final processing or staging table moves
For information on controlling which data goes to which subscriber, see FULLSYNC Chunking Rules.
FULLSYNC Subscription Characteristics
- Uses
OnNotify subscription handlers with straight SQL inserts
- If a header row is received, the target table is typically truncated
- The trailer record may be used to move data from staging tables to production tables
- FULLSYNCs can also cause effective date data corruption (same scenario as SYNC)
Make setup pages display-only on subscribing systems so users do not add or update values that could be truncated by the next FULLSYNC.
Data Ownership Risk
Be careful when the subscribing system has data from multiple sources. For example, if your HCM system has department values from both PeopleSoft Financials and JD Edwards, a DEPT_FULLSYNC from PeopleSoft Financials could potentially truncate and delete the JD Edwards departments.