Using the Effective Date Publish Utility

This chapter discusses how to:

Perform a full data publish of current effective data.
Publish incremental messages of current effective data.
Publish effective-dated rows from the delay table.

Understanding the Effective Date Publish Utility

The Effective Date Publish utility enables you to design processes to update external systems that process only current data and don't use or recognize effective dating.

When working with effective dating and effective date publishing, you need to understand the following terms:

Current Row	The current row is the first row of data with an effective date equal to or less than the system date. Only one row can be the current row.
Future Rows	Future rows have effective dates greater than the system date (usually the current date).
Historical Rows	Historical rows have effective dates less than the current row.
Effective Date	An effective date is when a table row becomes effective, or the date that an action begins. The PeopleSoft system supports the concept of effective-dated rows. Note. The EFFDT field is almost always a key. Specify the descending key attribute to display the row with the most recent effective date first.
Effective Dating	Automated effective dating saves changed data in a staging table for subsequent processing when the effective date becomes current. (Although data can be historical, current, or future, some third-party applications may support only current data. Thus, if a future-dated row is created within the PeopleSoft system, it must be delayed before transmission to the other system.)
Effective Sequence	An effective sequence serves two different purposes: If EFFSEQ is a required field, it enables the entry of more than one row with the same effective date when paired with EFFDT. The system assigns a unique sequence number to each row that has the same effective date. It also enables the first EFFSEQ to be zero. If EFFSEQ is not a required field, it is not paired with EFFDT, has no special function, and can be used as a simple sequencing field.
Effective Status	Effective status enables the system to select the appropriate effective-dated rows, when combined with the effective date field.
Full Data Publish	The full data publish process seeds, or initially populates or repopulates, a copy of an entire table onto a remote database or legacy system. The entire contents of the table are published to all systems that require a copy of the table. Generally, full data replication occurs with setup tables (relatively static, low-volume tables that are keyed by setID) and occurs in an asynchronous manner. When a full copy of the table exists on the external system, an incremental update provides a mechanism to keep the copy up-to-date with changes made on the master.
Incremental Publish	The incremental publish process sends a message that contains only the rows where the data has been modified, plus the corresponding anchoring parent and grandparent rows. When a particular transactional event occurs, an incremental update of the transaction data is sent to other systems to notify them of the changes.
Message Nodes	Each message node represents a publishing or subscribing system of a message. For example, the PeopleSoft Human Resources and PeopleSoft Financials databases are each defined as a message node even if they are both on the same server.
Message Channels	Message channels group messages and the nodes to which they are published, so that messages are published sequentially. Each message must belong to only one message channel. Channels control the ordering of messages and define timeout parameters and error thresholds. Assign message nodes to a message channel when you define the message channel.
Message Chunking	Chunking automatically breaks a message into several smaller messages based on the values in one or more of the fields in the level zero record. When publishing the entire contents of a table, you can use message chunking to publish only certain sets of data, or if a particular subscriber is interested in only a portion of the table.
Request ID	Use the request ID to specify multiple requirements within the same run control.
Run Control	You use run controls to produce full messages for objects at the same time. Run controls also associate publish rule definitions with the scheduled full publish process run. For example, you can set up a run control to publish both customer full messages and sales order full messages on a daily schedule.

Performing a Full Data Publish of Current Effective Data

This section discusses how to:

Create effective-dated messages.
Define the message node, message channel, and message definition.
Define chunking rules and ordering views.
Create publish rule definitions.
Create run controls for the Full Data Publish program.
(Optional) Define message routing.

For full data messages that are intended for vendors who do not handle effective dating, use the Effective Date Publish utility and a current full message to publish only those rows that are currently active. Any future-dated rows are written to the delay table.

This section discusses the process involved in a full data publish of current effective data. It uses the CUSTOMER_FULLSYNC_EFF message as an example, but the methods and procedures that are described here apply to creating any effective-dated message.

Pages Used for Full Data Publish of Current Effective Data

Page Name	Object Name	Navigation	Usage
Chunking Rule	EO_CHUNKRULE	Enterprise Components, Integration Definitions, Map Chunking Rules, Define Chunking Rules	Define the chunking rule description.
Full Data Publish	EO_FULLDATAPUB	Enterprise Components, Integration Definitions, Initiate Processes, Full Data Publish	Create the run control for the Full Data Publish utility. The run control associates publish rule definitions with the scheduled Full Publish process run. For example, you can set up a run control to publish both customers and sales orders full messages at the end of each day.

Page Name

Object Name

Navigation

Usage

Chunking Rule

EO_CHUNKRULE

Enterprise Components, Integration Definitions, Map Chunking Rules, Define Chunking Rules

Define the chunking rule description.

Full Data Publish

EO_FULLDATAPUB

Enterprise Components, Integration Definitions, Initiate Processes, Full Data Publish

Create the run control for the Full Data Publish utility.

The run control associates publish rule definitions with the scheduled Full Publish process run. For example, you can set up a run control to publish both customers and sales orders full messages at the end of each day.

Creating Effective-Dated Messages

The structure of the current full message must be a clone of the original FullSync message structure. However, you must map effective-dated records to a record view that selects only those rows that contain current data.

The current full message for customer data, CUSTOMER_FULLSYNC_EFF, uses the following views that are created as ordering view records.

Level	TARGET RECORDS	ORDERING VIEW RECORDS
Level 0	CUSTOMER	CUSTOMER
Level 1	CUST_ADDR_CNTCTC	CUST_ADDR_CNTCTC
Level 1	CUST_ADDR_SEQ	CUST_ADDR_SEQ
Level 2	CUST_ADDRESS (effective-dated)	CUST_ADDR_EF2VW (current effective-dated view)
Level 1	CUST_CNTCT_SEQ	CUST_CNTCT_SEQ
Level 2	CUST_CONTACT (effective-dated)	CUST_CNCT_EF2VW (current effective-dated view)
Level 3	CUST_CNTCT_CARD (effective-dated)	CUST_CARD_EF_VW (current effective-dated view)
Level 3	CUST_CNTCT_DOC (effective-dated)	CUST_DOC_EF_VW (current effective-dated view)
Level 3	CUST_CNTCT_PHN (effective-dated)	CUST_PHN_EF_VW (current effective-dated view)
Level 3	CUST_CNTCT_TYPE (effective-dated)	CUST_TYPE_EF_VW (current effective-dated view)

Defining the Message Node, Message Channel, and Message Definition

Create a full message definition that contains the necessary records in the publishing system. You can also set up message routing by using OnRouteTo PeopleCode.

Note. Remember to insert the message version first, otherwise you can’t add the tables that compose the message.

Begin by setting up the node and the transaction and connector details by using the Integration Profile setup function of PeopleSoft Integration Broker. To set up the node:

Create a node.
Set up the connector.

The default target is PSHTTP, but you can instead provide the HTTP address for another target.
Associate the transaction to the node.
Provide the message name.
Designate whether the message is synchronous or asynchronous.
Create the message definition for the effective-dated message.

Creating the Message Definition for the Message CUSTOMER_FULLSYNC_EFF

This example uses the message CUSTOMER_FULLSYNC.

To create the message definition for the message CUSTOMER_FULLSYNC_EFF:

Open PeopleSoft Application Designer.
Create the current full message definition by copying the CUSTOMER_FULLSYNC message and adding the suffix _EFF.

Note. Messages must have the _EFF suffix to be effective-dated.
Assign the message channel.
Set the status to Active.
Click Save and close PeopleSoft Application Designer.

Defining Chunking Rules and Ordering Views

In message chunking, all data within the message contains the same break field. (For example, if the break field is business unit, all transactions in the message are for the same business unit.)

The following message is chunked on setID by using the EO_SETID_EOC table:

To ensure that the message publishes when you use chunking rules:

Add subscribing nodes to the chunking node table.
In PeopleSoft Application Designer, add OnRouteSend PeopleCode to return a list of subscribing nodes.

Creating Publish Rule Definitions

For creating the current full message, the publish rule defines these options:

Message header and trailer creation.
Chunking rules.
Ordering views.

Create publish rule definitions for each current full message definition. Considerations are:

Specify only target records that are effective-dated.
Select only the current effective row to list the ordering view record that should be used as an override when publishing the message.
The Effective Date Publish utility makes a logic pass through the data for each publish rule definition.

You can use this logic to order and chunk the data differently for each subscriber.

Note. When chunking a message, you must provide an ordering view for each record that includes the chunking fields. The fields in this view must appear in the same order as the primary keys, followed by any other keys that are needed for that record. If you override the normal key structure of the message records, you must provide the ordering views for each record to guarantee that the message reconstructs with the correct chunking, parent, or child key relationships.

Creating Run Controls for the Full Data Publish Program

Access the Full Data Publish page.

Request ID

Enter request IDs to group the Description, Process Frequency, and Message Name parameters under one unique process request. A single run control ID can encompass multiple request IDs.

Parameters

Select the name of the message to publish.

The PeopleSoft system adds a run control for the currently effective FullSync message that is chunked by the setID CUSTOMER_FULLSYNC_EFF_SETID.

Note. If you insert a new row, the same run control component can publish more than one message, so you can produce both the full message and the current effective-dated full message from the same PeopleSoft Process Scheduler run.

Note. You must set up the run control parameters to start the Full Data Publish program.

Performing Full Table Replication

After you click Run on the Full Data Publish page, you can perform a table replication publish from the following screen:

To perform a full table replication, click the checkbox shown.

Defining Message Routing

To define the message routing, you must insert PeopleCode. (Perform this step only if you're chunking a message.)

Example

You want to route the customer message to the nodes that are defined in the SetID/Nodes page within the Publish Setup component. Add the following PeopleCode to the OnRouteSend PeopleCode that is associated with the message:

Declare Function GETSETIDNODES PeopleCode FUNCLIB_EOEIP.PUBLISH_ROUTE_PC FieldFormula;
Local Message &MSG;
/* Call Function that looks at Setid of first transaction in the message and returns a list of subscribing nodes to route
the message */
&MSG = GetMessage();
GETSETIDNODES(&MSG, %Date);

Publishing Incremental Messages of Current Effective Data

This section discusses how to:

Create message definitions and assign the message channel in PeopleSoft Application Designer.
Create subscription processes that open the generic effective-dated delay function.

For incremental messages, use subscription PeopleCode to copy current effective rows to a current incremental message for immediate publication, to strip out historic data, and to store future effective rows in a delay table. A regularly scheduled Application Engine program uses the delay table data as a trigger to publish future data when that data becomes effective.

Creating Message Definitions and Assigning the Message Channel

You must create an incremental message definition that contains the necessary records in the publishing system. Specify the version first, otherwise you can’t add the tables that compose the message. When the system requests a message channel, enter the channel definition that you previously selected.

To create the message definition:

Open an instance of PeopleSoft Application Designer.
Create the current incremental message definition by copying the preferred message, such as the CUSTOMER_SYNC message, and adding the suffix _EFF.
Assign the message channel.
Set the status to Active.

Creating Subscription Processes That Open the Generic Effective-Dated Delay Function

The PeopleSoft system includes a function called PROCESS_EFFDT_MSG. This function reads through an incremental message and processes the past, current, future, or non-effective-dated information. PROCESS_EFFDT_MSG resides within the record FUNCLIB_EOEIP, in the EFFDT_MSG_PC field.

Within PeopleSoft Application Designer, open the standard incremental message—CUSTOMER_SYNC in this example—and right-click Message Subscription to insert a new subscription (usually the message name with a suffix of _EFF attached). In this example, add a subscription to CUSTOMER_SYNC_EFF.

When the Message Subscription Properties window appears:

Open the Process_Effdt_Msg generic function stored in record FUNCLIB_EOEIP.
Pass it the name of the current effective-dated incremental message.
Indicate whether only rows with Active effective status should be selected as the current effective-dated rows.

If the &ACTIVE_EFFSTATUS parameter passed in is set to False, the current effective-dated row (whether active or inactive) is selected.
Pass the parameter set to True if only active effective-dated rows should be sent to the other system.

Note. The standard setting for the &ACTIVE_EFFSTATUS parameter is False.

Publishing Effective-Dated Rows from the Delay Table

This section provides an overview of the publish of effective-dated rows from the delay table and discusses how to run the Effective Date Publish utility.

Understanding Effective-Dated Row Publishing

Publishing effective-dated rows from a delay table requires:

A future-dated entry in the delay table.
The process request page.
An Application Engine utility program that publishes future-dated information when it becomes current.

The Application Engine Effective Date Publish utility publishes effective-dated rows from a delay table by:

Retrieving from the delay table any entries that are effective within a date range.
Using the key strings from the delay table and record information for the current message to read the original application tables and retrieve the most current effective rows.
Publishing those rows to the current incremental message.

The third-party application subscribes to the current incremental messages.

The PeopleSoft system allows for an end date range on the Effective Date Publish utility if the utility was not run for one day. The end date range enables the program to run on the next date.

You can run the Effective Date Publish utility multiple times during the day, and it deletes the information from the delay queue when the future effective date data becomes current and the message for that data has been published. The Effective Date Publish utility retrieves only the latest delay table information since the prior run.

If the Effective Date Publish utility is invoked after not running for a period of time, it retrieves only the current row and publishes that as the active record. The presumption is that the subscribers want only the most current database information that is published.

Page Used to Run the Effective Date Publish Utility

Page Name	Object Name	Navigation	Usage
Effective Date Pub (effective date publish)	EO_EFFDATAPUB	Enterprise Components, Integration Definitions, Initiate Processes, Effective Date Publish	Run the Effective Date Publish utility.

Running the Effective Date Publish Utility

Access the Effective Date Pub page.

Message Name

Select the current incremental message to publish.

End Date

Select the highest effective date to process from the delay table.

Run

Click to run this request.

The Application Engine program uses the trigger records in the delay table and the end date parameter from the run control component to publish a current effective incremental message that contains all future-dated rows that are effective. This ensures that third-party systems that cannot manage future-dated records always receive currently active data on that data's effective date, even if that information was previously updated on the PeopleSoft system.

Request ID	Enter request IDs to group the Description, Process Frequency, and Message Name parameters under one unique process request. A single run control ID can encompass multiple request IDs.
Parameters	Select the name of the message to publish.