Issues in integrating PeopleSoft with Siebel UCM/HECH using XML web services

 

SOAPAction in double quotes

    SBL-EAI-08006: SOAPAction '<?>' is of invalid format: SOAPAction should be either rpc/operationName or document/operationName.

    Initially I had following in routing definition for a PeopleSoft service operation

    Property ID:             HEADER
    Property Name:        SOAPAction
    Value:                    document/http://siebel.com/marketing/contact:ContactCreateContact

    replaced it with

    Property ID:             HEADER
    Property Name:        SOAPAction
    Value:                    "document/http://siebel.com/marketing/contact:ContactCreateContact"

-

-

PeopleSoft Service Operation - Routing - Missing SOAP Body

    The PeopleSoft message was being carried over HTTP without SOAPBody, for an outbound service operation from PeopleSoft.
    I fixed it by incorporating following property in Service Operation - Routing. I persoanlly think that routing should be
    delivered as part of a PeopleSoft project and we must not rely on documentation and let customer do it as that could be error prone.

    Property ID:            HTTPPROPERTY 
    Property Name:        SOAPUpContent 
    Value:                    Y

PeopleSoft Troubleshooting

 

Error: Not Authorized

Eg.

From my experience, I have seen this "Not Authorized" error showing-up in component interfaces, anywhere PeopleCode tries to invoke a component interface. Just include the component interface to the required permission list

PeopleSoft Sever Administration

-

-

Creating Windows Services of PeopleSoft App,PRCS and PIA Servers.

Here's the way to create windows service out of your PeopleSoft single-user standalone instance's
app server, process scheduler, and PIA.

It's as easy as specifying the required domain name(s) in PSADMIN/"Configure a Service" and instantiating a
windows service out this configuration using PSADMIN/"Install a Service"

And for PIA, amazingly just invoke PS_HOME\webserv\<DOMAIN>\bin\installNTservicePIA.cmd, and you are good to go.

Once done, you can start the services from the Services utility in the Windows Control Panel or Run command "services.msc".

You can make the services to start automatically on machine reboot.

-

-

Details

Creating Windows NT Services out of PeopleSoft App Server and Process Scheduler

-

-

PSADMIN

Select “Service Setup”

-

Select “Configure a Service”

-

Select ‘Y’ for ‘Do you want to change any config values (y/n)?’

-

Enter the names of the application server domains and the Process Scheduler databases

that you want to include as part of the Microsoft Windows service.

To add multiple domains or databases, delimit each value with a comma and a space.

-

Select ‘Install a Service’ from the PeopleSoft Services Administration menu.

-

FYI….the psntsrv.exe file

-

Services utility in the Windows Control Panel or Run command "services.msc"

On the General tab of the service properties, select a startup type of Automatic.

On the Log On tab, the Log On As setting must match the Log On As setting that's defined for the

BEA ProcMGR V 9 service, which was created when you installed BEA Tuxedo.

Both services should either be configured to Log On As Local System Account, or to Log On As This Account (referring to the same account).

-

-

Troubleshooting

In case the service gives any error in starting you can view the details in Windows Logs.

            How to View Event Logs

                 In Windows XP, an event is any significant occurrence in the system or in a program

                 that requires users to be notified, or an entry added to a log. The Event Log Service

                 records application, security, and system events in Event Viewer. With the event logs in

                Event Viewer, you can obtain information about your hardware, software, and system

                components, and monitor security events on a local or remote computer. Event logs can

               help you identify and diagnose the source of current system problems, or help you

               predict potential system problems.

 

                             To open Event Viewer, follow these steps:

                                     1. Click Start, and then click Control Panel.

                                              a. Click Performance and Maintenance,

                                              b. then click Administrative Tools, and

                                              c. then double-click Computer Management. Or,

                                              d. open the MMC containing the Event Viewer snap-in.

                                     2. In the console tree, click Event Viewer.
                                               The Application, Security, and System logs are displayed in the Event Viewer window.

-

-

-

Creating Windows NT Services out of PeopleSoft Internet Architecture (PIA)

-

-

          Starting BEA WebLogic on Microsoft Windows

            To run BEA WebLogic Server on Microsoft Windows, you can use a Windows service or a foreground process.

            Using the Windows Service

               Two benefits of running BEA WebLogic as a Windows service are:

                     1. BEA WebLogic can automatically start when the Windows server boots.

                     2. You can start and stop the service from a remote Windows machine.

                To install the service, open the command prompt, and enter the appropriate command from your WebLogic <domain>\bin directory: <PS_HOME>/

               Single server:

                          installNTservicePIA.cmd

               Multi server:

                         InstallNTservice.cmd weblogic_server_instance_name

               For example:

                        installNTservice.cmd PIA

              

                To start BEA WebLogic as a Windows service, use either of these methods:

                     Start the service named WebLogicdomain-servername (for example, peoplesoft-PIA) by using the

                      Services utility in the Windows Control Panel.

                    Start the service from a command prompt by entering the following command:

                        NET START peoplesoft-PIA

                 To uninstall the service, enter the following command:

                           UninstallNTservicePIA.cmd

              

 

 

 

PeopleSoft PeopleCode XMLDoc

Generating XML with namespace prefixes using PeopleCode XMLDoc object.

-

It may sound stupid but we do this by adding the xmlns as an attribute to the required element. Off course against the XML specs, which clearly says that we should never treat xmlns as an attribute.

-

   Required XML (sample):

        <?xml version="1.0"?>
        <q0:bibliograpphy xmlns:q0="http://example.org">
          <q0:book>
            <q0:title>TCP/IP Illustrated</q0:title>
            <q0:publisher>Addison-Wesley</q0:publisher>
            <price>65.95</price>
          </q0:book>
        </q0:bibliograpphy>

-

-

    PeopleCode

        Local XmlDoc &reqXML = CreateXmlDoc("");
        Local XmlNode &root = &reqXML.CreateDocumentElement("q0:bibliograpphy", "http://example.org");
       &root.AddAttribute("xmlns:q0", "http://example.org");
        Local XmlNode &book = &root.AddElement("q0:book");
        Local XmlNode &temp = &book.AddElement("q0:title").AddText("TCP/IP Illustrated");
        &temp = &book.AddElement("q0:publisher").AddText("Addison-Wesley");
        &temp = &book.AddElement("price").AddText("65.95");

        Local File &file = GetFile("<SOME_PATHTest>/XMLDoc.xml", "W", %FilePath_Absolute);
        &file.WriteLine(&reqXML.GenFormattedXmlString());

-

-

-

-

FTP using PeopleCode PutAttachment() inside App Engine

-

The return code (%Attachment_FileNotFound)  from PutAttachment() PeopleCode built-in function was incorrect.
Internally, it was failing as Write Access was revoked on the FTP server (ftp://username:password@hostname/AEI/SAKAI/)
This FTP server is running on some Windows Server 2008 machine and we need to contact the owner to get the write access.
Here's the ftp log generated from PutAttachment() PeopleCode.

verbose on
bin
200 Type set to I.
mkdir AEI
550 AEI: Cannot create a file when that file already exists.
cd AEI
250 CWD command successful.
mkdir SAKAI
550 SAKAI: Cannot create a file when that file already exists.
cd SAKAI
250 CWD command successful.
Local directory now C:\.
Local directory now C:\Documents and Settings.
Local directory now C:\Documents and Settings\BJETHWAN.PEOPLESOFT.
Local directory now C:\Documents and Settings\BJETHWAN.PEOPLESOFT\Local Settings.
Local directory now C:\Documents and Settings\BJETHWAN.PEOPLESOFT\Local Settings\Temp.
lcd C:\
lcd DOCUME~1
lcd BJETHW~1.PEO
lcd LOCALS~1
lcd Temp
put "bin.xml" "bin.xml"
200 PORT command successful.
550 bin.xml: Access is denied.

-

And here's the output from command line

-

C:\>bin.xml
C:\>ftp 10.138.125.144
Connected to 10.138.125.144.
220 Microsoft FTP Service
User (10.138.125.144:(none)): ftpuser1
331 Password required for ftpuser1.
Password:
230 User ftpuser1 logged in.
ftp> put bin.xml bin.xml
200 PORT command successful.
550 bin.xml: Access is denied.
ftp> cd AEI
250 CWD command successful.
ftp> cd SAKAI
250 CWD command successful.
ftp> delete READGROUPSFROMSAVEPOINT.xml
550 READGROUPSFROMSAVEPOINT.xml: Access is denied.
ftp> bye
C:\>

-

-

Basic FTP Commands

http://www.cs.colostate.edu/helpdocs/ftp.html

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;

-

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

PeopleSoft Effective Date

PeopleSoft HTTP Error Log

 

<PS_HOME>\webserv\<web-application>\applications\peoplesoft\PSIGW\errorLog.html

PeopleSoft Integration Broker and TCP Monitor (tcpmon v1.1)

 

PeopleSoft Integration Broker and TCP Monitor (tcpmon v1.1)

No one can refute the benefit of using tcpmon when testing a web-service. I was trying WS-Security stuff on PeopleSoft Campus solutions when I wanted to see the actual WS-Security xml elements in SOAP Header.

And I decided to use the TCPMonitor as no PeopleSoft component was showing me the complete SOAP message.

Following is a proof of concept where in I have implemented a small mock application using PeopleSoft Integration Broker and two PSFT nodes.

Since I wanted to use two PeopleSoft node only but still use HTTPTARGET connector (hope you know that if 2 PeopleSoft nodes communicate over PSFTTARGET connector target then you would rather see the cryptic JOLT string in tcpmon) I had to use build the web-service and WSDL in service provider node and in local ie service requester I used WSDL_NODE provided by PeopleSoft node with Gateway ID=LOCAL and Connector ID= “” and provided the Target URL in Routing.

Node

Requester Service Operation

Requester Routing

Primary URL: http://localhost:8080/PSIGW/PeopleSoftServiceListeningConnector

TCPMonitor

Request XML

Response XML

PeopleSoft Integration Broker – Message Schema PT8.49 Vs PT8.5

 

In PeopleTools 8.49 we used the DMS scripts to import export the message schemas. While in PeopleTools 8.5 used the new Message Schema definition in App Designer to place the message schema into the app designer project definition itself.

PT8.49 import/export scripts <PT_HOME>\scripts psibmsgschema_imp.dms and  psibmsgschema_exp.dms

Reverse Engineering: Database to ER diagrams using Microsoft Visio

 

SYS/SYSTEM password reset for SAMPLE database

1. Delete the passord file.
   C:\oracle\product\10.2.0\db_1\database\PWDsample.ora
2. Create the password file again. Using ORAPWD utilityWhen you invoke this password file creation utility without supplying any parameters, you receive a message indicating the proper use of the command as shown in the following sample output:> orapwdUsage: orapwd file= password= entries= force=wherefile - name of password file (mand),password - password for SYS (mand),entries - maximum number of distinct DBAs and OPERs (opt),force - whether to overwrite existing file (opt)There are no spaces around the equal-to (=) character.The following command creates a password file named acct.pwd that allows up to 30 privileged users with different passwords. In this example, the file is initially created with the password secret for users connecting as SYS.orapwd FILE=acct.pwd PASSWORD=secret ENTRIES=30The parameters in the ORAPWD utility are described in the sections that follow.FILE This parameter sets the name of the password file being created. You must specify the full path name for the file. The contents of this file are encrypted, and the file cannot be read directly. This parameter is mandatory.The types of filenames allowed for the password file are operating system specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file. For name and location information for the Unix and Linux operating systems, see Administrator's Reference for UNIX-Based Operating Systems. For Windows, see Platform Guide for Microsoft Windows. For other operating systems, see your operating system documentation.If you are running multiple instances of Oracle Database using Oracle Real Application Clusters, the environment variable for each instance should point to the same password file.Caution:It is critically important to the security of your system that you protect your password file and the environment variables that identify the location of the password file. Any user with access to these could potentially compromise the security of the connection.PASSWORD This parameter sets the password for user SYS. If you issue the ALTER USER statement to change the password for SYS after connecting to the database, both the password stored in the data dictionary and the password stored in the password file are updated. This parameter is mandatory.Note:You cannot change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so.ENTRIES This parameter specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.Entries can be reused as users are added to and removed from the password file. If you intend to specify REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this parameter is required.Caution:When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate a number of entries that is larger than you think you will ever need.FORCE This parameter, if set to Y, enables you to overwrite an existing password file. An error is returned if a password file of the same name already exists and this parameter is omitted or set to N.

Set *Correction* mode for a PeopleSoftComponent.

If component is already existing adn was registered using app designer then if you want to set the correction mode for a component, do it via PIA..

Push Button or Hyperlink

If it is set as PeopleCode Command then you are requirted to fill in a record and field name and FieldChange PeopleCode related at component record field or record field is executed.

Operating System Authentication Enabled at Installation

When you install Oracle Database, a special Windows local group called ORA_DBA is created (if it does not already exist from an earlier Oracle Database installation), and your Windows username is automatically added to it. Members of local group ORA_DBA automatically receive the SYSDBA privilege.
Membership in ORA_DBA enables you to:
Connect to local Oracle Database servers without a password with the command.

1. CONNECT / AS SYSDBA

Connect to remote Oracle Database servers without a password with the command

1. CONNECT /@net_service_name AS SYSDBA where net_service_name is the net service name of the remote Oracle Database server

Perform database administration procedures such as starting and shutting down local databases
Add additional Windows users to ORA_DBA, enabling them to have the SYSDBA privilege.

Date
SyntaxDate(date_num)
Description
The Date function takes a number in the form YYYYMMDD and returns a corresponding Date value. If the date is invalid, Date displays an error message.
Warning! Make sure that you pass a four-digit year in the year parameter of this function. Two-digit values are interpreted literally: 93, for example, represents the year 93 AD.
Returns
Returns a date equal to the date specified in date_num.
Example
Set the temporary variable &HIREDate to a date field containing the date July 1, 1997:&HIREDate = Date(19970701);

PeopleSoft Application

You can view the XML message for a service operation only when your login id has the required permissions. Here’s how we set it.

For testing I provide PTPT1000 as the permission list.

PeopleCode Snippets

Downcasting class variable

If the downcast operator is to be used in the context of further object expressions (such as references to other members using dot notation) then the downcast must be enclosed in parentheses to emphasize that the downcast happens before the member reference.

class Fruit



property number FruitCount;



end-class;







class Banana extends Fruit



property number BananaCount;



end-class;

In the following code example, first the variables are assigned. All the variable assignments are legal, for Banana is a subtype of Fruit.

local Banana &MyBanana = Create Banana();



local Fruit &MyFruit = &MyBanana;  /* okay, Banana is a subtype of Fruit */



local number &Num = &MyBanana.BananaCount;

The next two lines of code don't produce an error, as &MyFruit is currently a Banana at runtime.

&MyBanana = &MyFruit as Banana;  



&Num = (&MyFruit as Banana).BananaCount; /* same as &MyBanana.BananaCount */

In the next lines of code, we're reassigning &MyFruit to be of type Fruit, that is, of the superclass Fruit. When you try to reassign &MyFruit as Banana, &MyBanana is assigned as Null, because &MyFruit is no longer of type Banana.

&MyFruit = Create Fruit();







&MyBanana = &MyFruit as Banana; /* assigns Null - &MyFruit isn't a Banana */








 
Field.LongTranslateValue
You cannot access the &VALUE = &MYFIELD.LongTranslateValue; unless you have the following set as the record field properties. 
Without the edits being set the above LongTranslateValue property will return the small translate value.









 

PeopleTools Page Design

PeopleTools Page Design

Secondary Page

No save button either.

Level 0 and stuff not required.

Page title at the top of the page.

Secondary page gets activated by either a push button or a hyperlink.

It takes the level key fields by the placement of the push button.

Important SQLs

Important SQLs

• MicroSsoft SQL Server

Top few rows from a table, following will select
first 301 rows from a table.

SELECT
DISTINCT TOP 301 RUN_CNTL_ID,
LANGUAGE_CD
FROM
PS_PRCSRUNCNTL
WHERE
OPRID='PS'
AND
UPPER(RUN_CNTL_ID)
LIKE UPPER('%act')

• Oracle

Escape characters used in data.

Select * From STUDENTS Where STU_NAME Like '%\_%' Escape '\'

• Delete duplicate rows
  
  Delete From CLIENT_MASTER
  Where ROWID NOT IN
  ( Select min (ROWID)
  From CLIENT_MASTER
  Group By CLIENT_NO, NAME, BAL_DUE )
  

• In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.

Select ID, FIRST_NAME, LAST_NAME, NVL(PHONE, 'Unknown Phone Number') As Phone From CUSTOMER

Test Score Post in Campus Solutions

Test Id Security. Unless you set the test id security for a user you can not load or post the test scores from a flat file.

Here's the location to set that.

Navigation : Root --> Set Up SACR --> Security --> Secure Student Administration --> User ID --> Test ID Security.


Code extending the SOA framework to chunck a container based message when the message size exceeds Integration Broker max message size limit.

import SAD_CRM_INTEGRATION:SAD_TEST_POST_CRM:SAD_TEST_POST_CRM_AP;

Component Rowset &rsSadHeaderCrm, &rsSadApSus, &rsSccNameValue;
Component SAD_CRM_INTEGRATION:SAD_TEST_POST_CRM:SAD_TEST_POST_CRM_AP &oSadTestPostCRMAP;

Local number &i, &j;
Local Message &response;

&oSadTestPostCRMAP = create SAD_CRM_INTEGRATION:SAD_TEST_POST_CRM:SAD_TEST_POST_CRM_AP( Null, "SAD", "SAD_TEST_POST_CRM_AP");

&oSadTestPostCRMAP.rsSAD_HEADER_CRM = &rsSadHeaderCrm;
&oSadTestPostCRMAP.rsSAD_TEST_POST_AP_PART = &rsSadApSus;
&oSadTestPostCRMAP.rsSAD_TEST_POST_NAME_VAL_PART = &rsSccNameValue;
&response = &oSadTestPostCRMAP.execute();

If &oSadTestPostCRMAP.moduleError = Null Then
Exit (1); /* exit (1) to skip roll back action */
Else
MessageBox(%MsgStyle_OK, "", 99999, 999, "*** Post to CRM Integration Error ***");
MessageBox(%MsgStyle_OK, "", 99999, 999, &oSadTestPostCRMAP.moduleError.ToString( False));
If (&oSadTestPostCRMAP.moduleError.MessageSetNumber = 14099 And
&oSadTestPostCRMAP.moduleError.MessageNumber = 7) Then
MessageBox(%MsgStyle_OK, "", 99999, 999, "Attempting Secondary Burst of 100 transactions");


Local Rowset &rs100SadHeaderCrm, &rs100SadApSus, &rs100SadBioPrsSus, &rsSadBioPrsSus;
Local number &numberOfRowSet;

&rs100SadHeaderCrm = CreateRowset(&rsSadHeaderCrm);
&rs100SadApSus = CreateRowset(&rsSadApSus);

&rsSadHeaderCrm(1).CopyTo(&rs100SadHeaderCrm(1));

&rsSadBioPrsSus = &rsSadHeaderCrm(1).GetRowset(Scroll.SAD_BIO_PRS_SUS);
&rs100SadBioPrsSus = &rs100SadHeaderCrm(1).GetRowset(Scroll.SAD_BIO_PRS_SUS);


&i = 1;
While &i <= &rsSadBioPrsSus.ActiveRowCount
&j = 1;
&rs100SadBioPrsSus.Flush();
&rs100SadApSus.Flush();

For &j = 1 To 2

If &i > &rsSadBioPrsSus.ActiveRowCount Then
Break;
End-If;

If &j <> 1 Then
&rs100SadBioPrsSus.InsertRow(&rs100SadBioPrsSus.ActiveRowCount);
&rs100SadApSus.InsertRow(&rs100SadApSus.ActiveRowCount);
End-If;

&rsSadBioPrsSus(&i).CopyTo(&rs100SadBioPrsSus(&rs100SadBioPrsSus.ActiveRowCount));
&numberOfRowSet = 1;
While &numberOfRowSet <= &rsSadBioPrsSus(&i).ChildCount
&rsSadBioPrsSus(&i).GetRowset(&numberOfRowSet).CopyTo(&rs100SadBioPrsSus(&rs100SadBioPrsSus.ActiveRowCount).GetRowset(&numberOfRowSet));
&numberOfRowSet = &numberOfRowSet + 1;
End-While;

&rsSadApSus(&i).CopyTo(&rs100SadApSus(&rs100SadApSus.ActiveRowCount));
&numberOfRowSet = 1;
While &numberOfRowSet <= &rsSadApSus(&i).ChildCount
&rsSadApSus(&i).GetRowset(&numberOfRowSet).CopyTo(&rs100SadApSus(&rs100SadApSus.ActiveRowCount).GetRowset(&numberOfRowSet));
&numberOfRowSet = &numberOfRowSet + 1;
End-While;

&i = &i + 1;

End-For;

&oSadTestPostCRMAP.rsSAD_HEADER_CRM = &rs100SadHeaderCrm;
&oSadTestPostCRMAP.rsSAD_TEST_POST_AP_PART = &rs100SadApSus;
&oSadTestPostCRMAP.rsSAD_TEST_POST_NAME_VAL_PART = &rsSccNameValue;
&oSadTestPostCRMAP.moduleError = Null;
&response = &oSadTestPostCRMAP.execute();


If &oSadTestPostCRMAP.moduleError <> Null And
(&oSadTestPostCRMAP.moduleError.MessageSetNumber = 14099 And
&oSadTestPostCRMAP.moduleError.MessageNumber = 7) Then

MessageBox(%MsgStyle_OK, "", 99999, 999, "*** Secondary Burst too large ***");
Exit (0); /* Return 0 to call Rollback Action */

End-If;

End-While;
End-If;

End-If;