How to find blank/ tab character string oracle sql

Posted on October 3, 2018October 3, 2018 by JointLantic

How to find blank/ tab character string oracle SQL

How to find blank/ tab character string oracle SQL

SELECT UPPER(ADDRESS1)
FROM PS_ADDRESSES 
WHERE regexp_like(ltrim(UPPER(ADDRESS1)), '[[:space:]]$');

SELECT UPPER(ADDRESS1)

FROM PS_ADDRESSES

WHERE regexp_like(ltrim(UPPER(ADDRESS1)), '[[:space:]]$');

How to replace Line Feeds, Carriage Returns and tabs are the usual culprits and cannot be removed using the standard orLTrimRTrim Functions. You can remove them with the following:

LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(StringCharacter, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')))

If you encounter more white space characters that can’t be removed with the above then try one or all of the following:

--How to replace NULL
Replace([YourString],CHAR(0),'');
--How to replace Horizontal Tab
Replace([YourString],CHAR(9),'');
--How to replace Line Feed
Replace([YourString],CHAR(10),'');
--How to replace Vertical Tab
Replace([YourString],CHAR(11),'');
--How to replace Form Feed
Replace([YourString],CHAR(12),'');
--How to replace Carriage Return
Replace([YourString],CHAR(13),'');
--How to replace Column Break
Replace([YourString],CHAR(14),'');
--How to replace Non-breaking space
Replace([YourString],CHAR(160),'');

--How to replace NULL

Replace([YourString],CHAR(0),'');

--How to replace Horizontal Tab

Replace([YourString],CHAR(9),'');

--How to replace Line Feed

Replace([YourString],CHAR(10),'');

--How to replace Vertical Tab

Replace([YourString],CHAR(11),'');

--How to replace Form Feed

Replace([YourString],CHAR(12),'');

--How to replace Carriage Return

Replace([YourString],CHAR(13),'');

--How to replace Column Break

Replace([YourString],CHAR(14),'');

--How to replace Non-breaking space

Replace([YourString],CHAR(160),'');

References:
http://pplsoftlearner.blogspot.com/2016/
http://psacake.com/web/kn.asp

Difference between Search Record and Add Search Record in Component

Posted on October 3, 2018October 3, 2018 by JointLantic

Difference between Search Record and Add Search Record in Component

Difference between Search Record and Add Search Record in Component. Detail from Peoplebooks of PeopleSoft.

Search Record Name
Displays the component’s default search record when you access the component in update or display mode.

Note. You can assign an override search record to a component at the menu level. If the component uses an override search record, the search record displayed in the Override Search Record will be different from this one and you should search it instead.

Add Search Record
Displays the component’s search record when you access the component in add mode or when you want the fields that they users fill out to add a new key to be different from the fields for which they will be searching for the data.

If you use Add Search Record then data field in add mode using from add search record.

For example:

Search Record has keys field1, field2

Add Search Record has keys field1, field2, field3.

When search for the information on Component using from Search Record.
When the mode of Add a new value using Add Search Record that has to enter field1, field2, field3.

References:

http://pplsoftlearner.blogspot.com/2013/01/component-properties-search-record-and.html

https://it.toolbox.com/question/difference-between-search-record-and-add-search-record-061606

setcursorpos peoplecode peoplesoft

Posted on October 2, 2018October 2, 2018 by JointLantic

setcursorpos peoplecode peoplesoft

SetCursorPos PeopleCode PeopleSoft Using the SetCursorPos to place the focus in a specific field anywhere in the current component. To transfer to a page outside the current component, use Transfer.

SetCursorPos(Page.pagename, scrollpath, target_row, [recordname.]fieldname)

where scroll path is: [RECORD.level1_recname, level1_row, [RECORD.level2_recname, level2_row, ]] RECORD.target_recname

To prevent ambiguous references, you can use SCROLL. scrollname, where scrollname is the same as the scroll level’s primary record name.

peoplesoft setcursorpos level 1

This peoplecode for setcursorpos of field in scroll area or grid level 1

The page name is Page_name

The Record.field is TS_TBL.DESCR254_5

The row number of the row in which you want to place the cursor is 1.

SetCursorPos(Page.Page_name, TS_TBL.DESCR254_5, 1);

SetCursorPos(Page.Page_name, TS_TBL.DESCR254_5, currentrownumber());

More Example of peoplesoft setcursorpos level 1

setcursorpos peoplecode level 2

Page Name : TS_TRC_BOSSAPPV

Record Level 1 = TS_TRC_TRAN

Row of level 1 = 1

Record level 2 = TS_TRC_FILE

Row of level 2 = 1

SetCursorPos(Page.TS_TRC_BOSSAPPV, Record.TS_TRC_TRAN, 1, Record.TS_TRC_FILE, 1, TS_TRC_FILE.ATTACHUSERFILE);

PeopleSoft Correction Mode

Posted on October 2, 2018October 2, 2018 by JointLantic

PeopleSoft Correction Mode

PeopleSoft Correction Mode

Mode of Correction: Select to update any rows (history, current, and future) in an effective-dated record. Use only with effective-dated records. This information is translated to correct history at runtime.

You can check all %mode PeopleSoft by SQL

SELECT a.CLASSID, a.MENUNAME, a.BARNAME, a.BARITEMNAME, a.PNLITEMNAME, a.DISPLAYONLY
, a.AUTHORIZEDACTIONS 
,(SELECT b.PAGEACCESSDESCR FROM PSPGEACCESSDESC b WHERE b.AUTHORIZEDACTIONS= a.AUTHORIZEDACTIONS ) PAGEACCESSDESCR

FROM PSAUTHITEM a

ORDER BY CLASSID ,<span style="color: inherit; font-size: inherit;">MENUNAME</span>

SELECT a.CLASSID, a.MENUNAME, a.BARNAME, a.BARITEMNAME, a.PNLITEMNAME, a.DISPLAYONLY

, a.AUTHORIZEDACTIONS

,(SELECT b.PAGEACCESSDESCR FROM PSPGEACCESSDESC b WHERE b.AUTHORIZEDACTIONS= a.AUTHORIZEDACTIONS ) PAGEACCESSDESCR

FROM PSAUTHITEM a

ORDER BY CLASSID ,<span style="color: inherit; font-size: inherit;">MENUNAME</span>

How to attach BI Publisher Report to an email Peoplecode

Posted on October 1, 2018October 1, 2018 by JointLantic

How to attach BI Publisher Report to an email PeopleCode

How to attach BI Publisher Report to an email PeopleCode of PeopleSoft. Some requirement from user wants to send mail with attach file pdf, doc, and other from bi publisher report. You can use this code.

/*General code to print BI Publisher Report*/
import PSXP_RPTDEFNMANAGER:*;

/* get report definition object */
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn (&sRptDefn);
&oRptDefn.Get();
&oRptDefn.OutDestination = "/" | GetCwd(); /*We can change this path, if needed*/

/* fill query runtime prompt record */
&rcdQryPrompts = &oRptDefn.GetPSQueryPromptRecord();
If Not &rcdQryPrompts = Null Then
&oRptDefn.SetPSQueryPromptRecord(&rcdQryPrompts);
End-If;
&oRptDefn.ProcessInstance = &prcsInstId;

/*generate report*/
&oRptDefn.ProcessReport (&sTmpltID, &sLangCd, &AsOfDate, &sOutFormat);

/*Create URL ID before proceeding, use this link for the steps to URL ID */
&URL_ID = "record://RECORD_NAME";

/*We are changing the name of the report just make it possible to locate it*/
&oRptDefn.ReportFileName = "Report_Name";

/*Adding the extension of the file*/
&filename = "Report_Name"|"."|&sOutFormat;
&source_file = &oRptDefn.OutDestination | &filename;
&return = PutAttachment(&URL_ID, &filename, &source_file);

/*publish report */
&oRptDefn.Publish(&sPrcsServerName,"",&sFolder, &prcsInstId); 
/*&sPrcsServerName and &sFolder can be "" (i.e. blank) and &prcsInstId is process instance of your process*/
/*As now we have the path for the report (i.e. &source_file), 
we can now use this to add this report as an attachment in our email, as shown below code*/
&dest_file = &source_file;

/*SendMail() can directly access this file if application server and ftp server are at same location. 
If they are at different location, use GetAttachment() to get file from ftp server to application server 
and then mention the path of application server*/

&RET =SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC,&MAIL_SUBJECT, &MAIL_TEXT, &dest_file, &filename,&MAIL_FROM,&MAIL_SEP,&CONTTYPE,&REPLYTO, &SENDER);

/*General code to print BI Publisher Report*/

import PSXP_RPTDEFNMANAGER:*;

/* get report definition object */

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn (&sRptDefn);

&oRptDefn.Get();

&oRptDefn.OutDestination = "/" | GetCwd(); /*We can change this path, if needed*/

/* fill query runtime prompt record */

&rcdQryPrompts = &oRptDefn.GetPSQueryPromptRecord();

If Not &rcdQryPrompts = Null Then

&oRptDefn.SetPSQueryPromptRecord(&rcdQryPrompts);

End-If;

&oRptDefn.ProcessInstance = &prcsInstId;

/*generate report*/

&oRptDefn.ProcessReport (&sTmpltID, &sLangCd, &AsOfDate, &sOutFormat);

/*Create URL ID before proceeding, use this link for the steps to URL ID */

&URL_ID = "record://RECORD_NAME";

/*We are changing the name of the report just make it possible to locate it*/

&oRptDefn.ReportFileName = "Report_Name";

/*Adding the extension of the file*/

&filename = "Report_Name"|"."|&sOutFormat;

&source_file = &oRptDefn.OutDestination | &filename;

&return = PutAttachment(&URL_ID, &filename, &source_file);

/*publish report */

&oRptDefn.Publish(&sPrcsServerName,"",&sFolder, &prcsInstId);

/*&sPrcsServerName and &sFolder can be "" (i.e. blank) and &prcsInstId is process instance of your process*/

/*As now we have the path for the report (i.e. &source_file),

we can now use this to add this report as an attachment in our email, as shown below code*/

&dest_file = &source_file;

/*SendMail() can directly access this file if application server and ftp server are at same location.

If they are at different location, use GetAttachment() to get file from ftp server to application server

and then mention the path of application server*/

&RET =SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC,&MAIL_SUBJECT, &MAIL_TEXT, &dest_file, &filename,&MAIL_FROM,&MAIL_SEP,&CONTTYPE,&REPLYTO, &SENDER);

Reference : http://techatpeoplesoft.blogspot.com/2016/04/how-to-attach-bi-publisher-report-to.html