Hi PeopleSoft Teams,

To let us know the feasibility of invoking a CI present in X instance from another instance Y either with or without any links. Also how to pass the parameters for the same.

Thanks for posting your question!

We cannot invoke CI of One peoplesoft instance from another peoplesoft instance directly. They will have to use Integration broker. Through the integration broker you can send XML message from one instance (Source) to another instance (Target). In the target instance you will have to get the xml, parse it and pass the value to the CI in this instance.

Thanks,

Sachin Deshmukh

Hi,

PS does not recommend the usage of SQLExec().

> What are the reasons for it?
> Please suggest how we can avoid its usage, with the alternatives.

Thanks,
Rama Naidu

Thanks for posting your question!

PS does not recommend sqlexec(), to avoid direct database calls. As the main reason for 3 tier architecture was to execute the business logic on the application server so that the application can be made database independent. This means if you want to change the database of any application from one vendor to another, you should be able to do it easily.

If you use sqlexec() calls, this purpose is not met as you may use sql which is very specific to the database. However if you use Peoplesoft way of creating a record, for example

RC_CASE is record with key as CASE_ID

Local record &lrec = createrecord(record.rc_case);

&lrec.case_id.value = 123;

Now if you want to select from RC_CASE, you would use sqlexec() as sqlexec(“select case_closed from ps_rc_case where case_id=:1”,123,&caseclos);

Instead you can also use the record instance as

&lrec.selectbykey();

The above will populate the rc_case record with all the values and you can use it. Also in this case Peoplesoft forms the sql based on the database, so if the organization using this application decides to change the database from Oracle to MS-SQL Server, you will not have to change the code for above.

Thanks,
Sachin Deshmukh

I have a requirement as a database cleanup to delete non required Workers from Database. Can you please advice on Worker related data and which peoplesoft tables should be hit. CRM 8.9

Thanks for posting your question!

In PeopleSoft Enterprise CRM, workers are represented as Person business objects with a role type of Worker. The Person table (RD_PERSON) contains a record for each worker, with Person ID as the key field. Attributes that are specific to the person’s role as a worker, such as employee status, job location, work function, and so forth, are stored in the Worker table (RB_WORKER).

Worker and related tables are as follows:

PS_RD_PERSON (Key: PERSON_ID)

PS_RB_WORKER (Key: PERSON_ID)

PS_BO (Key: BO_ID)

PS_BO_ROLE (Key: BO_ID, ROLE_TYPE_ID, ROLE_START_DT )

PS_BO_NAME (Key: BO_ID, SEQ_NBR)

PS_BO_REL (Key: REL_TYPE_ID, BO_ID_1, ROLE_TYPE_ID_1, BO_ID_2, ROLE_TYPE_ID_2, START_DT)

PS_BO_REL_TYPE (Key: REL_TYPE_ID)

PS_BO_TYPE (Key: BO_TYPE_ID)

PS_BO_NAME_TYPE (Key: BO_TYPE_ID, NAME_TYPE)

PS_BO_TYPE_ABE (Key: BO_TYPE_ID, ABE_SEQ)

PS_BO_ABE_DTL (Key: BO_TYPE_ID, ABE_SEQ, PROFILE_CM_SEQ)

PS_BO_ROLE_TYPE (Key: BO_TYPE_ID, ROLE_TYPE_ID)

PS_BO_CM (Key: BO_ID, PROFILE_CM_SEQ, BO_CM_START_DT)

PS_CM (Key: CM_ID, CM_TYPE_ID)

Regards,

Pratiksha Lokhande

Can u pls guide me how to write a AE program which loads data into the component.
This AE should load data into the time entry component. The pages contains the fields LOCATION,PAYGROUP and PAY_END_DT and all hours and amounts fields.
These LOCATION,PAYGROUP and PAY_END_DT three fields should be populated by the AE Program from the LOCATION_TBL,PAYGROUP_TBL,PAY_CALENDER.
Following is the logic that we need to follow for the interface to load data to the Bolt-on page.
1. Loop through all ACTIVE locations in the location table.
2. For each location, loop through the 3 paygroups – Weekly, Biweekly and RSR
3. for the combination derived select the min of pay-end-dt where paygroup obtained from above and pay_sheets_run = ‘Y’ and Pay_confirm_start = ‘n’
4. For the combination derived in the above steps, loop through the Employees (PS_EMPLOYEES) table and validate if the employee is present in
the PS_PAY_EARNINGS table. If it’s present, enter data for the Employee into the Bolt-on table. If not, write it out to the log output.
please guide thorugh the AE Program. I have problem with the implementation.

Thanks for posting your question!

We will give you a reference code which can be studied and a similar logic can be applied for you requirement. The table joins and sample code has assumed some key field and joins between the above mentioned tables. You may have to change them as per actual table structure. This is just an pseudo code, you may have to change it as per your requirement.

Steps:

Create a new application engine in app designer.
Add peoplecode section to the application engine
Below is the sample peoplecode that you can refer:

local &lrowsetlocation = createrowset(record.location);

LOCAL DATE &LPAYENDDTw,&LPAYENDDTBIw,&LPAYENDDTRSR,&minenddate;

&lrowsetlocation.fill(“where status = A”);

Rem 1. Loop through all ACTIVE locations in the location table;

for &i= 1 to &lrowsetlocation.activerowcount

&locid = &lrowsetlocation.getrow(&i).getrecord(record.location).getfield(field.location_id).value;

Rem 2. For each location, loop through the 3 paygroups – Weekly, Biweekly and RSR;
rem – Loop through all 3 paygroups;

/* Code to check if the paygroup is available for the weekly */

SQLEXEC(“SELECT paygroup_id FROM PAYGROUP_TBL WHERE paygroup = ‘weekly’ and LOCATION_ID = :1″,&locid,&chkpaygrpw);

if ALL(&chkpaygrpw) then

SQLEXEC(“SELECT PAY_END_DT FROM PAY_CALENDAR WHERE pay_sheets_run=Y and pay_confirm_start = n and PAY_GROUP_ID = :1″&chkpaygrp,&LPAYENDDTw);

end-if;

/* Code to check if the paygroup is available for the biweekly */

SQLEXEC(“SELECT paygroup_id FROM PAYGROUP_TBL WHERE paygroup = ‘biweekly’ LOCATION_ID = :1″,&locid,&chkpaygrpbiw);

if ALL(&chkpaygrpbiw) then

SQLEXEC(“SELECT PAY_END_DT FROM PAY_CALENDAR WHERE pay_sheets_run=Y and pay_confirm_start = n and PAY_GROUP_ID = :1″&chkpaygrp,&LPAYENDDTBIw);

end-if;

/* Code to check if the paygroup is available for the rsr */

SQLEXEC(“SELECT paygroup_id FROM PAYGROUP_TBL WHERE paygroup = ‘rsr’ LOCATION_ID = :1″,&locid,&chkpaygrprsr);

if ALL(&chkpaygrprsr) then

SQLEXEC(“SELECT PAY_END_DT FROM PAY_CALENDAR WHERE pay_sheets_run=Y and pay_confirm_start = n and PAY_GROUP_ID = :1″&chkpaygrp,&LPAYENDDTRSR);

end-if;

rem – For the combination derived from above find the minimum pay end date

/* Code to return minimum end date out of the above 3 */

IF &LPAYENDDTw < &LPAYENDDTBIw THEN

IF &LPAYENDDTw < &LPAYENDDTRSR then

&minenddate = &LPAYENDDTw;

else

&minenddat = &LPAYENDDTRSR;

end-if;

ELSE

if &LPAYENDDTBIw < &LPAYENDDTRSR then

&minenddat = &LPAYENDDTBIw

else

&minenddat = &LPAYENDDTRSR

end-if;

END-IF;

If you like this post, spread the word around :

You might want to read these awesome related posts

{ 7 comments }

1 devwfb February 5, 2009 at 9:00 pm: Hi,

I was trying out CI based web services and found I was able to create/update child rows of a component using Update method. However, I couldn’t find a way of deleting child rows from it. Can you please guild me how to archive this? This web service is based on component interface USER_PROFILE and I was trying to delete a IDType.

Thanks & Regards,
2 Rama Naidu February 6, 2009 at 2:09 am: Hi thanks for the response,

&lrec.selectbykey() is fine with single table, but how should we avoid SQLExec involving two or more tables and lot of “where” clauses like:

SQLExec(“SELECT DISTINCT A.SUPERVISOR_ID, RTRIM(B.EMAILID),A.SAL_ADMIN_PLAN FROM PS_SY_JOB_VW A, PSOPRDEFN B WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_SY_JOB_VW A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= GETDATE()) AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_SY_JOB_VW A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) AND A.EMPL_STATUS IN (‘A’,'L’,'P’) AND A.SUPERVISOR_ID = B.EMPLID AND A.EMPLID = :1″, &Emplid, &Sup_id, &Sup_email_id, &SALARY_PLAN);

Please suggest an alternative..

Thanks,
Rama Naidu.
3 vartika July 12, 2010 at 5:44 am: how can we join two frame conditions in same table?
ex-

ex
4 vartika July 12, 2010 at 5:47 am: how can we use “above” and “vsides ” in same frame of any table?
5 vartika July 12, 2010 at 5:52 am: how can we use “above” and “vsides ” in same frame of any table?
6 Reethu July 22, 2010 at 9:34 am: Hi Team

This is in regard to the NVision reports.
Issue is:
The Nivsion report scheduled starts running few minutes after the requested start time mentioned .
why is this happening.

Used sql to check the timings:
elect RQSTDTTM,BEGINDTTM from psprcsrqst
where SERVERNAMERUN = ‘PSNTNVA’
and PRCSNAME = ‘NVSRUN’

Can you please help me in this regard.

Thank you.
7 Reethu July 22, 2010 at 9:56 am: Hi Team
Cna you please help me with the difference in coding or the exact diffrence /purpoes for the following :

saveprechange/savepostchange
prebuild/postbuild
%select/%selectinit
do select/do while