DMS - Data Mover Scripts

-

-

DMS to Export PeopleSoft Project Message Catalog Entries

-

Export PSMSGCATDEFN Where MESSAGE_SET_NBR=14950 And MESSAGE_NBR IN (55, 56, 57, 58, 59, 60, 61, 64, 65, 66, 67);
Export PSMSGCATDEFN Where MESSAGE_SET_NBR=14951 And MESSAGE_NBR IN 138, 139, 27, 28, 29, 30, 63, 64);

We moved to following DMS to export all the message catalog entries inserted in the project definition. And it doesn’t have to change every time you create and insert new entries in the project definition.

Similar DMS is used to pick the master project catalog entries from bundle project, to which all the teams in campus solutions merge their project to.

EXPORT PSMSGCATDEFN
WHERE EXISTS
(
    SELECT 'X' FROM PSPROJECTITEM B
    WHERE B.PROJECTNAME = '<< insert your project name >>'
    AND OBJECTTYPE = 25
    AND OBJECTVALUE1 = MESSAGE_SET_NBR
    AND OBJECTVALUE2 = MESSAGE_NBR
);

-

-

DMS to remove past and future dated rows from a effective dated table

-

Following gave SQL Validator issues stating that table name alias can NOT be used in a delete / update SQL in a DMS script.

Following is the DMS scripts which leads to SQL Validator issue

-- Removing past and future dated rows from a effective dated table

DELETE FROM PS_TABLE_NAME TABLE_ALIAS1
WHERE EXISTS(
         SELECT 'X' FROM PS_TABLE_NAME TABLE_ALIAS2 
         WHERE TABLE_ALIAS1 .FIELD1 = TABLE_ALIAS2.FIELD1 
             AND TABLE_ALIAS1 .FIELD2 = TABLE_ALIAS2.FIELD2 
             AND %EffdtCheck(TABLE_NAME, TABLE_ALIAS2, %CurrentDateTimeIn)
             AND TABLE_ALIAS1.EFFDT <> TABLE_ALIAS2.EFFDT                       
);

 

And I used the following to resolve that issue, the crux is to hold the required data in a .dat file, delete the complete table and re-populate the table with the data in the .dat file. So both export and import happening in the same DMS import script we submit to bundle.

 

-- Removing past and future dated rows from a effective dated table

SET OUTPUT updXXXXXX_XX.dat;
EXPORT TABLE_NAME WHERE %EffdtCheck(TABLE_NAME, PS_TABLE_NAME, %CurrentDateIn);

DELETE FROM PS_SAE_CRSCTLG_EXT;

SET INPUT updXXXXXX_XX.dat;
IMPORT TABLE_NAME;

 

PeopleSoft Integration Broker

 

****************************************************************************************************************************************************************************

-

-

Gateway ID/Connector ID setup at Node vs Routing level

The ConnectorID is not required at the Node as long as there are no Routings defined (with that node as a Target Node) where these routings are not using Connector Override on the routing or within PeopleCode. 

Note that a gateway only needs to even be defined for outbound routings only (in other words if one had all inbound routings a gateway does not even need to be defined on the gateway page).

As long as this Node will always be used on routings that have its own connector override at the routing level then no Node connector needs to be defined.

-

-

****************************************************************************************************************************************************************************

-

-

Issue: Synchronous Service Operations : Multiple Active Routings 

-

While working on SAIP, we found an issue invoking a synchronous service operation having multiple active routings (i.e. multiple receiver nodes). At a time we wanted to invoke that service opetation for only one of the multiple active routings.

Initially we decided, to use a OnRoute Handler (as was suggested in PeopleBooks) to pick the correct routing (i.e. the receiver node), but we identified a small issue with that approach too and then moved to extending sendMessage() method in CS SOA framework. Wherein instead of using

&response_msg = %IntBroker.SyncRequest(&request_msg);

-

we planned to use either
-

&response_msg = %IntBroker.SyncRequest(&request_msg, Node.TargetNode1);

-

or

-

&responseMsgArray = %IntBroker.SyncRequest([&MsgArray, &NodeNames]) ;

but only after verifying if the given node was in the receiver node list of that service operation.

Example Service Operation

Service Operation: General Tab

Service Operation: Handler Tab

Service Operation: Routings Tab

OnRote handler (Impementing PS_PT:Integration:IRouter - OnRouteSend )PeopleCode

-

method OnRouteSend
/+ &_MSG as Message +/
/+ Returns Integer +/
/+ Extends/implements PS_PT:Integration:IRouter.OnRouteSend +/

/* Variable Declaration */
Local any &aNodeList;
Local any &rootNode;
Local Rowset &TargetRowset;
Local string &TargetNode;

/* The node name is in the header part of the message */

&destinationNodes = CreateArray();
&TargetRowset = &_MSG.GetPartRowset(1);
&TargetNode = &TargetRowset(1).GetRecord(Record.SAE_EVENT_INFO).SAE_TARGET_NODE.Value;
&destinationNodes.Push(&TargetNode);

Return (%IntBroker_ROUTE_SOME);

end-method;

-

Issue

-

Here we had a issue wherein if we populate the &destinationNodes string array with a node name which is not not specified as a receiver node in the list of service operation routings, PeopleCode throws an exception and shows that service operation as “ERROR” in “Synchronous Services - Monitor”

-

Resolution

-

Then we realized that we can handle it in another way rather then using OnRote handler (Impementing PS_PT:Integration:IRouter - OnRouteSend ) to pick the correct routing. We decided on using &response = %IntBroker.SyncRequest(&p_msg, &TargetNode); .

However, before even executing this SyncRequest, we planned to verifyif that node is in the receiver node list of that service operation all this using a IB provided component interface (IB_ROUTINGDEFN_CI) for IB routing component.

We overridden the sendMessage() method in CS SOA framework

method sendMessage
/+ &p_msg as Message +/
/+ Returns Message +/
/+ Extends/implements SCC_SERVICE:AbstractServiceOperation.sendMessage +/

Local Message &response;
Local Rowset &TargetRowset;
Local string &TargetNode, &TargetServiceOperation;
Local SAE_FRAMEWORK:UTIL:GeneralUtil &objGeneralUtil = create SAE_FRAMEWORK:UTIL:GeneralUtil();

%This.log.logInfo(MsgGetText(14099, 8, ""));
&TargetRowset = &p_msg.GetPartRowset(1);
&TargetNode = &TargetRowset(1).GetRecord(Record.SAE_EVENT_INFO).SAE_TARGET_NODE.Value;
&TargetServiceOperation = &TargetRowset(1).GetRecord(Record.SAE_EVENT_INFO).SAE_TRGT_OPERATION.Value;

If (&objGeneralUtil.existsActiveRouting(&TargetServiceOperation, &TargetNode)) Then
    &response = %IntBroker.SyncRequest(&p_msg, @("Node." | &TargetNode));
End-If;

Return &response;
end-method

-;

Here’s the magic code to find if a given node exists in the receiver node list of the specified service operation

-.

method existsActiveRouting
   /+ &pServiceOperationName as String, +/
   /+ &pNodeName as String +/
   /+ Returns Boolean +/

 
   Local number &indexServiceOperationRoutings;
   Local ApiObject &oSession, &oSaeIbRoutingdefnCi, &oSaeIbRoutingdefnCiRet, &oSaeIbRoutingdefnCiCollection;

- 
   &oSession = %Session;
   &oSession.PSMessagesMode = 1;

-
   &oSaeIbRoutingdefnCi = &oSession.GetCompIntfc(CompIntfc.IB_ROUTINGDEFN_CI); 
-  

   If &oSaeIbRoutingdefnCi = Null Then
      rem "Unable to retrieve CI for routings.";
   End-If;

- 
   &oSaeIbRoutingdefnCi.InteractiveMode = False;
   &oSaeIbRoutingdefnCi.GetHistoryItems = True;
   &oSaeIbRoutingdefnCi.EditHistoryItems = True;

- 
   &oSaeIbRoutingdefnCi.IB_OPERATIONNAME = &pServiceOperationName;
   &oSaeIbRoutingdefnCiCollection = &oSaeIbRoutingdefnCi.find();

-
   For &indexServiceOperationRoutings = 1 To &oSaeIbRoutingdefnCiCollection.Count;
      &oSaeIbRoutingdefnCiRet = &oSaeIbRoutingdefnCiCollection.Item(&indexServiceOperationRoutings);
      &oSaeIbRoutingdefnCiRet.Get();
      If (&oSaeIbRoutingdefnCiRet.IB_OPERATIONNAME = &pServiceOperationName And
            &oSaeIbRoutingdefnCiRet.EFF_STATUS = "A" And
            &oSaeIbRoutingdefnCiRet.RECEIVERNODENAME = &pNodeName) Then
         Return True;
      End-If;
   End-For;
  Return False;
end-method;

-

****************************************************************************************************************************************************************************