AppEngine:%bind()_Dynamic Where Clause :Single Quote Problem

I've requirement to prepare dynamic where cluses based on setup.

In Setup: PLAN_TYPE values are stored like this as string : '10','11','15'.

This string  needs to inserted into AET ; In SQL Action, passed value using %bind().
eg: In AET
SR_TEST_AET.DESCR = '10','11','15'

SQL Action:
Select * from PS_SR_TEST_REC where plan_type in (%bind(DESC)).

Internally this sql is expanding differently as below : 

Select * from PS_SR_TEST_REC where plan_type in (''''10'',''11'',''15'''') -  due to this i faced lot of problem.

Solution: to insert dynamic clasue:                                                                                                                 

Select * from PS_SR_TEST_REC where plan_type in (%bind(DESC),NOQUOTES);
 it solves my problem & it expands as required

Select * from PS_SR_TEST_REC where plan_type in ( '10','11','15').


^_^

Peopletool 8.51: Peoplesoft Test Framework

with release of  8.51 peoplesoft comes with PS test framework (PTF) with record & playback.

PTF is located under /setup/PsTestFramework/setup.exe.

learn more follow the links:

1. Part 1-  http://www.youtube.com/watch?v=hY_q82hpcks

2. Part 2-  http://www.youtube.com/watch?v=kIK3QsPTPOU

3. Part 3-  http://www.youtube.com/watch?v=aRmpusoAN5E

 

 :-)

Benefit Administration

Primary Ben Admin Tables:-

•BAS_PARTIC - Stores information about a specific event.
•BAS_PARTIC_PLAN - Stores data about each plan type for an event.
•BAS_PARTIC_OPTN - Stores data about the options available.
•BAS_PARTIC_COST - Stores costs for each option.
•BAS_PARTIC_INVT  - Stores investment options by plan type
•BAS_PARTIC_DPND - Stores dependent information by plan type


To find the recent status of Open Enrollment
----------------------------------
SELECT BAS_PROCESS_STATUS,COUNT(*) FROM PS_BAS_PARTIC WHERE SCHED_ID = ‘XX’ GROUP BY BAS_PROCESS_STATUS

To identify who is eligible to make different plan (healh, saving, etc) for recent event maintainence.

SELECT A.EMPLID FROM PS_BAS_PARTIC A, PS_BAS_PARTIC_PLAN B WHERE A.SCHED_ID = B.SCHED_ID AND A.EMPLID = B.EMPLID AND A.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR AND A.EVENT_ID = B.EVENT_ID AND B.PLAN_TYPE = '20‘  /*different plans*/
AND B.ELECT_ALLOWED= 'Y‘ AND A.EVENT_DT > 'XX/XX/XXXX' /*event date*/.


Source : Web Source (OHUG).

Peoplesoft Base Benefit Tables

Peoplesoft Base Benefit Tables:

PS_BAS_PARTIC, PS_BAS_PARTIC_COST, PS_BAS_PARTIC_DPND, PS_BAS_PARTIC_INVT, PS_BAS_PARTIC_OPTN, PS_BAS_PARTIC_PLAN, PS_BENEF_COMMENT, PS_BEN_PROG_PARTIC, PS_DEPENDENT_BENEF, PS_DISABILITY_BEN, PS_FSA_BENEFIT, PS_FSA_PAYMENT, PS_HEALTH_BENEFIT, PS_HEALTH_DEPENDNT, PS_LEAVE_ACCRUAL, PS_LEAVE_PLAN, PS_LIFE_ADD_BEN, PS_LIFE_ADD_BENEFC, PS_PENSION_BENEFC, PS_PENSION_PLAN, PS_RTRMNT_PLAN, PS_SAVINGS_BENEFIC, PS_SAVINGS_INVEST, PS_SAVINGS_PLAN, PS_VACATION_BEN

Peoplesoft NA Payroll Tables

Peoplesoft NA Payroll Table List:

PS_STATE_TAX_DATA, PS_GENL_DEDUCTION, PS_BAL_ADJ_UI_CAN, PS_CAN_CHECK_YTD, PS_CAN_DED_BALANCE, PS_CAN_ERN_BALANCE, PS_CAN_TAX_BALANCE, PS_CAN_TAX_DATA, PS_CHECK_YTD, PS_DEDUCTION_BAL, PS_EARNINGS_BAL, PS_FED_TAX_DATA, PS_TAX_BALANCE, PS_LOCAL_TAX_DATA, PS_BAL_ADJ_ARR, PS_BAL_ADJ_CHK, PS_BAL_ADJ_CN_CHK, PS_BAL_ADJ_CN_DED, PS_BAL_ADJ_CN_ERN, PS_BAL_ADJ_CN_TAX, PS_BAL_ADJ_DED, PS_BAL_ADJ_ERN, PS_BAL_ADJ_GRN, PS_BAL_ADJ_TAX, PS_DED_ARREARS, PS_GARN_RULE, PS_GARN_SCHED, PS_GARN_SPEC, PS_GENL_DED_CD,PS_PAY_CHECK, PS_PAY_DEDUCTION

Peoplesoft HR/HCM Tables

List of Peoplesoft HCM Tables.

PS_EMPLOYEES, PS_JOB,PS_PERSONAL_DATA, PS_EMPLOYEES_LNG, PS_EMPLOYMENT, PS_EMPLOYMENT_LNG, PS_EMPLOYMENT_LNG1, PS_EMPLOYMENT_LNG2, PS_EMPLOYMENT_LNG3, PS_HS_EMPLOYMENT, PS_HS_EMPL_REL_CAN, PS_HS_EMPL_REL_LNG, PS_HS_EMPL_SUB_CAN, PS_HS_EMPL_SUB_LNG, PS_INJ_CLMADDR_CAN, PS_JOB, PS_PERSONL_DTA_LNG, PS_PERS_DATA_AET, PS_PERS_DATA_EFFDT, PS_PERS_DTAEFF_LNG, PS_PERS_NID, PS_JOB_EARNS_DIST, PS_JOB_APPROVALS, PS_BEN_PROG_PARTIC

Problem invoking WLST - java.lang.RuntimeException: Could not find the OffLine WLST class

When instlling Webserver on Tools 8.51 and Applicaion 9.1, the PIA  failed to configure by saying,

Problem invoking WLST - java.lang.RuntimeException: Could not find the OffLine WLST class 

I googled for the problem tried the all the solutions no luck!!.

Resolution:

The problem found while installing I dint Selected the Products
       Web 2.0 HTTP Pub Servers and Weblogic Server Clients.
So I reinstalled without uninstllaing existing weblogic by selecting only previously unselected options (as above mentioned).

Now webserver onfigruration installed properly..

-:)

Peopletools 8.51 on VM Ware

Today, a bit of struggle installed Peopletools 8.51 and HRCS 9.1 on VM Ware..

Configuration on VM ware:-
HardDisk: 50GB
RAM: 1GB (out of my 3GB)

Softwares
Windows XP SP3
Micrsoft SQL Server 2008
Weblogic 10.3.1
Tuxedo 10gR3PeopleTools 8.51 (3 Parts)
HRMS 9.1
Firefox 3.5x or IE7/8/9.


Overall the performance is good. the more performance I see after increasing my RAM 1GB to 1.5 GB.(2GB it is very good for to work)

Now the time to research...!!!!!!!!! :-)

PSAdmin: Failed to query Windows Registry for TUXDIR & NLSPATH

In PeopleTools 8.51
When I open appserver(psadmin.exe) to configure :
It Says:
ERROR: Failed to query Windows Registry for TUXDIR environment variable.
ERROR: Failed to query Windows Registry for NLSPATH environment variable.

To Resolve this, go to Regedit.exe

Create new registry keys(String) for TUXDIR & NLSPATH as shown below.

Peoplesoft Installation on VM Ware

Now a days lot of abuz going on  for virtualization of operating systems - like virtual machines, cloud computing.etc.

So I thought let me try the new technlogy of virtual maachine using VM Ware on my machine.
Started googling for VM Ware: found very interesting.

What is VM Ware (in Simple): Its virtualisation of software to create seperate OS in the existing OS(windows 7 or any other) by sharing the physical devices(ram,usb,hard disk..etc).

1. Downloaded the VM Ware 7.x (Evaluation version) it comes around apprx550MB.
2. Installed the VM Ware on windows 7.
3. In the VM ware I've created a new virtual machine. So creating the virtual machine(VM) means, blank hard disk with no operating system. The size of the Virtual machine set to 25GB.The VM shares the RAM size. Let say I've total 3GB of RAM and I've assinged 1.5 GB of RAM to virtual machine and 1.5 GB to Windows 7.

4. Started installing windows XP on the virtual machine. Installed successfully.
5.For testing purpose installed Peopletools 8.49 and HCM 9.0 with SQL Server 2005 db.

Out of 25 GB the whole installation took 21 GB space.

its got worked!!!!! able to have different versions of peoplesoft in single machine for better R&D .

The  best advantage I see is the backup is very portable & we can expand the size of VM whenever required and we can even compact the size of VM.


This experience gave me the boost to further research on VM ware with Oracle 11g..Next couple of days..

Let's see how it goes..

^-^

Oracle 11g - Pivot and UnPivot - Split rows into Multiple colums or vice versa

Transposing rows into columns (pivot) or columns into rows (unpivot) to generate results in crosstab format. Pivoting is a very useful technique, especially for reporting.



 I/P:

--------- Table1



Location    Month      Balances

------------------ -----------

A1               1                 100

A1               2                 200

A1               3                 300

A1               4                 400

A1               5                 500

A1               6                 600

A1               7                 800


 Output

----------------



Location         1           2           3          4        5        6        7

----------     ---        ----         ---       ---        ---     ----    -----
 A1             100       200         300      400     500      600     800



Sample Sql :
------------

 SELECT location,month,balances from table 1
  PIVOT (balances           -- pivot_clause
         FOR month         -- pivot_for_clause
         IN  (1,2,3,4,5,6,7)  -- pivot_in_clause
        );



 UNPIVOT:-
 --------

 Try it: same as pivot (instead of Pivot use Unpivot)

Oracle 11g-Listagg - Merge Rows into Single Columns

The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings.

Sample Sql: 

select listagg(''''||NAME||'''',',') within group (order by NAME) from PS_PERSON 

I/P

---

Name

------

A

B

C

D

E

The Output as

-----------------

'A','B','C','D','E'


:-)

Oracle's First Customer to Deploy PeopleSoft Enterprise 9.1

Ricoh Electronics is Oracle's First Customer to Deploy PeopleSoft Enterprise 9.1

 Read More :  http://www.oracle.com/us/corporate/press/065271 

 :-)

Oracle : Delete & Update Queries Using Joins

The SQL's are consutructed using dummy table.. below are the just framework. 

Modify according to your  requirement

Delete Query :-

---------------------

Delete from table1 A
where exists (select * from  table2 B
WHERE A.EMPLID = B.EMPLID AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND
B.Col5 = 6556 AND B.Col_status = 'S')

Update Query:-

-------------

update table2 A set A.STATUS = 'V'
where exists (Select * from table1 B
WHERE A.EMPLID = B.EMPLID AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND
B.col5 = 6556 AND B.col_status = 'S')

Thanks

Srinivas

To Get the Local/Current PC DateTime not the Server Time using Peoplecode

two ways we can achieve this

1. Using Personalization

%ClientDate:

%ClientDate returns the current date for the current user, adjusted for the user’s time zone. This is the date as specified with the current user's personalizations.You can use this system variable as the default constant for a date field.This is potentially one day different than the server date, which is returned with %Date.

Let's say, the Server is located in US, the business users spread across Global Other than US.

So, if we issue %Date (or any other Date variables) it will return server time.

Suppose if the bussiness user located in India, he should get Date time in IST.

2.Using  DateTimeToTimeZone

 syntax

DateTimeToTimeZone(OldDateTime, SourceTimeZone, DestinationTimeZone)

Use the DateTimeToTimeZone function to convert datetimes from the datetime specified by SourceTimeZone to the datetime specified by DestinationTimeZone.

to know the client zone use the variable:- %ClientTimeZone

Eg:- DateTimeToTimeZone(%Datetime, "UST", "IST")
 this will convert US date time to Indian Std Date time.

Refer to:-

PeopleBooks: PeopleCode Language Reference 

:-)

Oralce Instant Client Instructions instead of Oracle Db Client (Using Toad/App Designer)

"To setup Toad with Oracle Client existed in Remote place:


Follow the steps:


1. Install Toad.

2. Download Oracle Instant Client from the following site (64-bit):

    http://www.oracle.com/technetwork/topics/winx64soft-089540.html


3. Place the instant client folder in some directory. Let's C:\Srinivas\InstantClient.

4.Create TNSNAMES.ORA file & place in C:\Srinivas\InstantClient folder.

  Sample TNSNAMES.ORA file:-

      your_dbname=
           (DESCRIPTION =
           (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = your_db_host_name)(PORT = your_db_port_no))  )
          (CONNECT_DATA =
          (SID = your_dbname)
          (SERVER = DEDICATED)
         )
        )            
5. Create Environment Variable

       

That's it!!!!!   "

:-)

PeopleTools 8.50 Connected Query with XML Publisher (Video)

http://download.oracle.com/peopletools/viewlets/peopletools850connectedquery.swf

:-)

Regular Expressions in Oracle 10g

In Oracle Database 10g, you can use both SQL and PL/SQL to implement regular expression support. Regular expressions are a method of describing both simple and complex patterns for searching and manipulating.

To implement regular expression support in either SQL or PL/SQL, you use a new set of functions. These functions are:

Function Name	Description
REGEXP_LIKE	Similar to the LIKE operator, but performs regular expression matching instead of simple pattern matching
REGEXP_INSTR	Searches for a given string for a regular expression pattern and returns the position were the match is found
REGEXP_REPLACE	Searches for a regular expression pattern and replaces it with a replacement string
REGEXP_SUBSTR	Searches for a regular expression pattern within a given string and returns the matched substring

POSIX Metacharacters in Oracle Database Regular Expressions

Metacharacters are special characters that have a special meaning, such as a wild card character, a repeating character, a nonmatching character, or a range of characters.

You can use several predefined metacharacter symbols in the pattern matching with the functions.

Symbol	Description
*	Matches zero or more occurrences
|	Alternation operator for specifying alternative matches
^/$	Matches the start of line and the end of line
[]	Bracket expression for a matching list matching any one of the expressions represented in the list
[^exp]	If the caret is inside the bracket, it negates the expression.
{m}	Matches exactly m times
{m,n}	Matches at least m times but no more than n times
[: :]	Specifies a character class and matches any character in that class
\	Can have four different meanings: (1) stand for itself; (2) quote the next character; (3) introduce an operator; (4) do nothing
+	Matches one or more occurrences
?	Matches zero or one occurrence
.	Matches any character in the supported character set (except NULL)
()	Grouping expression (treated as a single subexpression)
\n	Backreference expression
[==]	Specifies equivalence classes
[..]	Specifies one collation element (such as a multicharacter element)

More on

Oracle

:-)

PT8.49 Sending Mail using PT_MCF_MAIL doesn't validate the wrong mail addresses

PT_MCF_MAIL: send() function doesn't validate the wrong mail address'es

import PT_MCF_MAIL:*;

Local PT_MCF_MAIL:MCFOutboundEmail &email = create PT_MCF_MAIL:MCFOutboundEmail();
------------------------------------------
&email.Recipients = "srinivas@1234.com"; /*mail id syntax is correct but domain(1234.com) is a wrong domain*/
----------------------------------------------
&email.From = "";
&email.BCC = "";
&email.Subject = "Sample";
&email.ReplyTo = "";
&email.ContentType = "text/html";
&res = &email.Send();
WinMessage("Mail Sent " | &res , 0);

I am passing wrong Recipients mail id. when we run this still its &email.Send(); returns 1 (success).. and doesn't capture the %ObEmail_NotDelivered exception.

in Peopletools 8.50 there is a function isDomainNameValid needs to check how this function works..@not at the moment..

from Metalink
---------------
In PT8.50 we have a new function called isdomainavailable to check if the email address's domain is valid or not. It pings DNS server to validate email addresses. Other than that, there is no other ways. Development has stated that Java mail on the outgoing server does not check for invalid address of TO address while sending an email. It just checks for the correct format of email address.
If you consider to upgrade to PT8.50, this issue can be resolved.

:-(

SendMail() vs MCFOutBoundEmail() Emailing Functions

1. Is the MCFOutBoundEmail() function better than SendMail() function?

As of PT 8.49.13, PeopleSoft began using javamail via Multi Channel Framwork (MCF) to send emails, as javamail is fully SMTP Protocol compliant to RFC 821/822. The MCFOutBoundEmail() function is fully compliant, whereas SendMail() function uses PeopleSoft's basesmtp.cpp, and has been found to not be fully compliant with SMTP Protocol RFC 821/822. Therefore, the recommendation is to replace use of SendMail, with MCFOutBoundEmail function.


2. How does this function send email? is it SMTP? Yes, MCFOutBoundEmail sends email fully complant to Simple Mail Transfer Protocol (SMTP)

3. Where is SMTP Server & Port configured? MCFOutBoundEmail() function uses the SMTP section of the Application Server's psappsrv.cfg file, and Process Scheduler's SMTP section of the psprcs.cfg file, just as the SendMail() function does. No special configuration is required for the MCFOutboundEmail() function to work.

--
More on
Oracle Metalink

Restore Deleted User profile : PS

while installing peoplesoft  i issued a command by mistake..

Drop user PS cascade;

its deleted all the chain records..

to restore PS user again,

for eg SYSADM/SYSADM

run the script @/script/nt/dbowner.sql;


login to datamover as SYSADM/SYSADM run the following script

INSERT INTO PS.PSDBOWNER VALUES('HRDEMO', 'SYSADM');
UPDATE PSSTATUS SET OWNERID = 'SYSADM';
UPDATE PSOPRDEFN SET SYMBOLICID = 'SYSADM1', OPERPSWD = OPRID, ENCRYPTED = 0;
UPDATE PSACCESSPRFL SET ACCESSID = 'SYSADM', SYMBOLICID = 'SYSADM1', ACCESSPSWD = 'SYSADM', VERSION = 0, ENCRYPTED = 0;


ENCRYPT_PASSWORD *;


TON!!!!

Oracle SQL Developer 2.1

To Download, Follow

http://www.oracle.com/technology/software/products/sql/index.html

New Feaures in 2.1, Follow

http://www.oracle.com/technology/products/database/sql_developer/files/newfeaturelist21.htm


T on!!

True User Centered Design Process

Follow

T on!!

PeopleTools 8.40 to 8.49 Cumulative Features

Follow the link to find the changes in PeopleTools 8.40 to 8.49.


http://www.oracle.com/applications/peoplesoft/tools_tech/ent/ptools/peopletools-840-849-cumulative-feature-overview.pdf


T on!!!!!

Copying bin folder will not work peopletools 8.50 in Client side

pTo work peopletools on client side before 8.49 we used to copy bin folder and will configure the environment.

But in PeopleTools 8.50 if you copy bin folder, Peopletools will not work. it will give error as
"Environment is not configured".

So, in PeopleTools we need to run first the ps_home/setup/vrcdist/vcredist_x86.exe.
 (This exe contains Visual C++ runtime files)

Follow:-

http://forums.oracle.com


T On!!!