Thursday, April 11, 2013

PeopleSoft Application Engine Drop Temp Tables & re-Create

Several time based on the requirements we have to remove and rebuild the temp tables which are specified in AE.

Below are the steps I followed to delete.

1. Find out the temp tables which are used by specific table using below query.

   select  table_name from all_tables where table_name like '%ABCTAO3%';
 
2. Using below sql generate the Drop Script.
 
     select 'DROP TABLE '||table_name||';' from all_tables where table_name like '%ABCTAO3%';



3. Execute the generated script in Database. I prefer to run these in DataMover.

Then build the table in Appdesigner.

Note:- Sometime after deleting temp tables from Database, I tried to build from AE the temp table again it doesnot create temp table instances. (How I know its created or not - look at the build script which generated). So I have to increase or decrease temp table instance count,save the AE and then change the instance count again back to original and save then Build the temp table.

for more information about temp table, you could follow the link too..

http://blog.psftdba.com/2009/01/managing-changes-to-number-of-instances.html

:-)





Saturday, March 9, 2013

JOLT_CAT:1626: ERROR: Jolt client (ip address) does not have proper application password

Trying to login PIA, 

   I got below error messages

   CHECK APPSERVER LOGS. THE SITE BOOTED WITH INTERNAL DEFAULT SETTINGS, BECAUSE OF: bea.jolt.ServiceException: Invalid Session 
      or
 application server down at this moment.

So I looked at the app server log &  below error message

So obviously I googled, I got information from below site managed by Nicolas:


So I checked web server configuration.properties for DomainConnectPswd parameter. Found the value its encrypted like {V1.1}qeurng=. 

& I checked in app server for the same parameter it has some value ENCRYPT. From Nicolas, he says both places this parameter should be same.

So what i did was manually opened psappsrv.cfg & changed DomainConnectPswd parameter to blank.

& Rebooted the app server. Whoa!! able to connect PIA.. Thanks Nicolas providing wonderful info.

Wednesday, February 27, 2013

PeopleTools 8.53/ PS 9.2 : PS Query :New Functionality Transformations Using XSLT


In PeopleTools 8.53, Oracle has added a new tab called Transformation. Hmm Transformation heard somewhere.. ya.. used in Integration broker in order to transform(or format) the input message. 

So what is this transformation do with PS/Query ?

PeopleBooks:
PeopleBooks Query Transformations

Basically If you want to transform the PS Query output to a different presentation format rather providing in standard table format.

for eg;  you want table header background with different color & you need some heading for report & want CSV output report readily...etc i..e User Defined Output

In Order to achieve all these kind of things need to have little about  XSLT, HTML and XPATH language.

 basics can be found http://www.w3schools.com/

So I have tried my self example :

Created query & In the Transformation tab & Click Add XSLT name you XSLT (Why naming XSLT coz you can have n number of XSLT formats I dont know the max limit ).


Click & click on Preview XSLT




Tuesday, February 26, 2013

Solution : TNS:listener does not currently know of SID given in connect descriptor

When em trying to connect db client with the problem TNS the db throws below error

Listener refused the connection with following error: ORA-12505, TNS: listener does not currently know of SID given in connect descriptor.

Checked the TNS everything looks fine.

Login to db server :

Checked ORACLE_SID & ORACLE_HOME environment variables are properly set or not. They looks fine.

Next in command line or putty

$lsnrctl status         (to the know the status of current listener)

the log indicated me  listener started with no services. Googled the to understand the issue found out I need to run below command inorder to link db with listener.

Logged as : sqlplus / as sysdba & excuted below command by changing the host & port

alter system set local_listener=' (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.local)(PORT=1521)))'


Then excuted the $lsnrctl status

the log indicated listener connected with db instance, and TNS entries may be the issue need to verify TNS entry at db client.

after that successfully connected to oracle db client

:-)

      

Solution : ORA-01017: invalid username/password; logon denied (Peoplesoft Application Designer)

Recently when em working with PeopleSoft VM template faced below problem while loging to Application designer.

SQL Access ManagerSQL error. Stmt #: 2 Error Position: 0 Return: 404 - ORA-01017: invalid username/password; logon denied 

Solution:

1. Run the grant.sql script (available in /scripts folder)
       & modified the script - replace the with people
   

                  grant select on PSSTATUS to people;
                  grant select on PSOPRDEFN to people;
                  grant select on PSACCESSPRFL to people;

      Executed with sysadm/sysadm user.

2. Checked the PSACCESSPRFL table for accessid & password.

       select * from PSACCESSPRFL; 

  The accessid & accesspwd fields are encrypted so I dont know the existing existing userid & password so I  ran below script to modify the table.

      update PSACCESSPRFL set accessid = 'sysadm',accesspswd = 'sysadm', encrypted =0;

after all these I am able to login successfully to app designer.


Thursday, July 26, 2012

Things to remember while writing an AppEngine or Batch Process

When writing any batch process typically two operations will be performed on the database either Insert or Update in the tables. Most of the batch processes will fail on these update & insert statement.

 Typical batch process errors are : Can update value to Null, Unique constraint error ..etc


While inserting or updating a table we should remember couple (or more) of things.

While Updating:
 1. Check the row already existed  target table by using Exist or In operator.

 2. In some case we need to write sub-query to update the value, at sometime the subquery may  not result  a value & if the target field can't store blanks, the process will result in failure. Inorder to eliminate this kind issue use NVL or NVL2 functions outside sub-query

  for eg:- Update TBLA set TBL.FIELD1 = NVL((select TBL2.FIELD2 from TBL2),' ')

While Inserting:

1. Check any null values from Source table are inserting into a non-null value field in the target, then use NVL ,NVL2, case or Decode functions. Know the use these functions & use it in proper situation.

2. Check the row already existed in target table by using Exist or In operator.

3. Check any duplicate values exist in Source table itself  while inserting into target table. This one is most people will forget doing.

Monday, June 18, 2012

PSQuery Drill Down in PeopleSoft 9.1

Oracle has introduced new feature from PeopleTools 8.50 inorder to help users to navigate from PSQuery results to a particular PS Page , External page , Query  or attachment.

To Link PS Query results to particular component follow below steps:

1. Create a New Expression & Expression type as Drilldown URL


2. Click on the Component URL to select the particular component you want to link.
   
       1. Select component
       2. Click on search keys button to see the search key values for the component.
       3.  List of search keys for particular component
       4. Select the fields from ps query to map the component fields
       5. Select the fields which you want to provide a link in ps/query

 3. Final output PS Query result below. Click on the link below it will open a new window with the selected component.


    Security: If the user doesn't have security to the target component. it will end up showing not authorized message.

Similarly you can link query with antoher query or external URL and attachement.
:-)

Thursday, May 31, 2012

[Resovled:]Bugs in Portal Related Content Service PeopleTools 8.51

When working with Portal Related Content Service with PSQuery found couple of bugs.

1. If PS query has input parameters & when defining a service for Related Content click on Populate Parameters. Its populates the parameters but will not able to save page with below error. The is a known issue to Oracle & Oracle fixed this issue PeopleTools 8.51.15 but I am not aware of what's the fix is.

Fetching array element 2: index is not in range 1 to 1. (180,252) PTCSSERVICES.GBL.SavePostChange  PCPC:676  Statement:7 .

 Resolution1: We can manully enter parameters insteads of clicking the button.  
                                     or 
  
 Resolution2: With this change you can click populate parameter button  to puplated parameters

                  added the below code (in bold letters)  to existing peoplecode in PTCSSERVICES.savePostChange

 Local array of string &vals = Split(&params_del [&i], ":");
 Local boolean &found = False;

 

 If &vals.Len >= 2 Then             !Added inorder to fix delivered bug
                      .
                      .
                      .
         If Not &found Then
             SQLExec("DELETE FROM PS_PTCS_MAPFIELDS WHERE PTCS_SERVICEID = :1 AND  

                   PTCS_PARAMETERNAME = :2", &vals [1], &vals [2]);
         End-If;

 end-if;
        

2. When trying to link PS Query to component basically will map key values on search record to PS Query input values. But if component search record keys are in subrecord & we will not be able to map keys to PS/query. for example : Saving Plan page in HCM (Benefits > Enroll in Benefits >Saving Plan).

Resolution:Inorder to fix this issue we modified the peoplecode to look at psrecfieldall table instead of psrecfield table in Related Content.