PublicationContractManager::ProcessError/RetryResponse(): ‘UserName not defined in database. (158,55)’.

ISSUE:

Sending Asynchronous messages from peoplesoft to peoplesoft gives out the error message as “PublicationContractManager::ProcessError/RetryResponse(): ‘UserName not defined in database. (158,55)’.”

PRE CHECK:

1.Check whether the basic  setup of the Integration Broker is good(ie pinging the node and gateway URL to success).

2.Check whether all the corresponding queues, handlers and routings are active.

SOLUTION:

Goto Main Menu->PeopleTools–>Itegration Broker–>Configuration–>Service Configuration

Goto –> Exclude PSFT Auth Token tab–>search for the required service–> check the Exclude Token –>Click Save

This applies to the Peopletools 8.5x.The issue was not addressed in the lower peopletools version.

How to determine if a query is a Public or a Private query?

Distinguising a Private query from a Public query can be accomplished by following these steps:

1) From any SQL tool, run statement:

a) SELECT * FROM PSQRYDEFN

2) Look at the Oprd column. If the column is blank, then the query is Public. For Private queries, this field is populated.

For example,

SELECT * FROM PSQRYDEFN WHERE OPRID <> ‘ ‘ will return the Private queries.

SELECT * FROM PSQRYDEFN WHERE OPRID = ‘ ‘ will return the Public queries.

How to make User ID to start as blank for each PIA logon

There is a system wide setting that you can change in the webprofile.

It is called ‘Days to Auto Fill User ID: ‘, default set to 7 days, to disable it, set it to 0.

Go to PIA > PeopleTools > Web profile > Web profile configuration > look up the web profile you use in the configuration.properties file > go to the Security tab

Restart the Application server and Web server.

Run SQR From command prompt

For Oracle Server:

{SQR Executable Path and name} {Report Path and name} {userid/password@database} {SQR Parameters}

For SQL Server:

{SQR Executable Path and name} {Report Path and name} {databasename/userid/password} {SQR Parameters}

Example:
C:\PS_HOME\bin\sqr\mss\binw\sqrw.exe C:\PS_HOME\SQR\aaaa.sqr databasename/userid/password -zifC:\PS_HOME\sqr\pssqr.ini -iC:\PS_HOME\sqr -oc:\temp\sqr.log -debugf

Miscellaneous PSADMIN Commands

 

 Command                                                        Description  

psadmin -h                                                       Displays psadmin help

psadmin -v                                                       Displays the current peopletools version with patch

psadmin -env                                                  Displays the whole environment including the Tuxedo installed directory

 

For running these commands goto command prompt , point out the PSHOME/appserv and type the above commands. 

 

How to unlock account in oracle

ALTER USER user_name ACCOUNT UNLOCK IDENTIFIED BY password

Change the user_name and password according to your requirement.

How to reset the password in oracle

ALTER USER user_name IDENTIFIED BY password

 Change the user_name and password according to your need.

SQL Commands

❑The DML commands are SELECT, INSERT, UPDATE, DELETE, and MERGE.

❑The DDL commands are CREATE, ALTER, DROP, RENAME, TRUNCATE,

and COMMENT.

❑The DCL commands are GRANT and REVOKE.

❑The TCL commands are COMMIT, ROLLBACK, and SAVEPOINT.

Alter Table by Re-creation

Alter Table by Re-creation

 

Most table alterations are done by building, populating, and renaming a copy of the original table.
The DDL models are used to create the temporary table and the indexes at the end of the script.
Alter table scripts cannot be run directly by the Application Designer; rather, they must be
run manually in SQL*Plus.

 

Example:

 

— Create temporary table
CREATE TABLE PS_YTABLE ***************

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

/
— Copy from source to temp table
INSERT INTO PS_YTABLE (
***************

**************)
SELECT
*******,

*******
FROM PS_TABLE

 /

— CAUTION: Drop Original Table
DROP TABLE PS_TABLE

 /

— Rename Table
RENAME PS_YTABLE TO PS_TABLE

 /
— Done

CREATE UNIQUE INDEX *************

/

 

Explanation:

Here it first creates a temporary table and then copies the values from the original table and
then it drops the original table. In the next step it will rename the temporary table to the
original table and then recreate the index for the new table created.

This script is not without risks. If the creation or population of the temporary table fails for any
reason, the script will continue and drop the original table, and the data will be lost.

Alter Table in Place

Alter Table in Place

The Application Designer can generate the ALTER statements to add columns to a table without
re-creating it, although it drops and re-creates the index. Only the CREATE INDEX command is
generated from a DDL model; the rest is hard-coded in the Application Designer

Example:

ALTER TABLE PS_TABLE  ADD COLUMN_NAME_NEW VARCHAR2(18)

UPDATE PS_TABLE SET COLUMN_NAME_NEW = ‘ ‘

ALTER TABLE PS_TABLE MODIFY COLUMN_NAME_NEW NOT NULL

DROP INDEX PS_TABLE

CREATE UNIQUE INDEX PS_TABLE

Explanation:

The above is the sample code where it addds a column DATA_KEY5 into the table PS_GFC_DATA_KEY5.
first it alters the target table directly and inserts a new column. If we specify any constraint to the
column it recreates the index only and not the table