Ask Question
Help Others
Home

CompShack Blogs

List Dates in a month

Fri, 08/22/2008 - 8:35am — Lepa

Here is an SQL that will list all dates for a particular month.

SELECT       TRUNC (TO_DATE (:target_date, 'MM/DD/YYYY'),
                    'MM'
                   )
           + LEVEL
           - 1 AS days_in_month
      FROM DUAL
CONNECT BY LEVEL <=
              EXTRACT (DAY FROM LAST_DAY (TRUNC (TO_DATE (:target_date,
                                                          'MM/DD/YYYY'
                                                         ),
                                                 'MM'
                                                )

Call Unix Script from SQR

Wed, 08/20/2008 - 2:33pm — Lepa

Few posts back, I wrote about how to call a UNIX script from PeopleCode. In this post, I will provide a sample code to call a UNIX script from an SQR program.

 
!**********************************
! Procedure:    Run-Script                            *
! Description:  Calls a unix script from SQR *
!**********************************
begin-procedure Run-Script
#debug show 'Entering Run-Script'

! call script located in /ps_scripts folder

Find Navigation of Page from Component Name

Fri, 08/08/2008 - 8:03am — aryadav

Many times we came across situation where we have Page name but we dont know the navigation for this page in Portal.

Following SQL makes the job easy for us.
Execute the following SQL in Database.

SELECT REPLACE(navigation,'',' > ') "PIA Navigation"
, url
, MENU_NAME
, COMPONENT_NAME
, portal_objname
, portal_prntobjname
, portal_uri_seg3
,portal_label
FROM (SELECT SYS_CONNECT_BY_PATH (a.portal_label,'>>') navigation
, '/EMPLOYEE/ERP/c/' || a.portal_uri_seg1 || '.' || a.portal_uri_seg2 || '.' || a.portal_uri_seg3 url
,  a.portal_uri_seg1  MENU_NAME

Changing Date Format Using PeopleCode

Tue, 08/05/2008 - 5:29am — aryadav

Peoplesoft provides a function called as "DateTimeToLocalizedString" to change date from one format into another.

Following is syntax used to achiev this.

DateTimeToLocalizedString({datetime | date}, [Pattern])

Param1 : Variable of Date Datatype
Param2 : Date format which you want for example "dd/MM/yyyy".

If you have Param1 of type string then use function "DateValue" to convert that string to Date.

Component Interface Field Details

Mon, 08/04/2008 - 10:07am — simrose

This SQL returns details of the fields like DESCRIPTION, FIELD TYPE, FIELD LENGTH, KEY TYPE , REQUIRED, DEFAULT VALUE, EDIT , PROMPT TABLE etc.
in CI just replace ': COMPONENT INTERFACE NAME' with your Component Interface Name

SELECT
  c.bcname AS component_interface,
  a.recname AS record,
  a.fieldname AS FIELD,
  d.shortname AS description,
  CASE
WHEN b.fieldtype = 0 THEN
  'CHARACTER'
WHEN b.fieldtype = 1 THEN
  'LONG_CHARACTER'
WHEN b.fieldtype = 2 THEN
  'NUMBER'
WHEN b.fieldtype = 3 THEN
  'SIGNED_NBR'
WHEN b.fieldtype = 4 THEN

Work Flow Walkthrough

Thu, 07/31/2008 - 1:06pm — nitin_hexa

Hi all,

This is very basic Doc for Workflow. Even those who has not worked yet on workflow can go through and start making their own.

This helped me a lot.

I hope this is really goonne help.

Regards
Nitin Khanna

Dynamic Drop Down(Translate Values)

Thu, 07/31/2008 - 7:52am — nitin_hexa

This code i have got Ittool box.com.
It is very useful. we usually have requirement when we want to hide some translate values from translate field on page for some business reason.
In this case we can use AddDropDownItem(),ClearDropDownItem().
The fact is that we need to use ClearDropDownItem() function first. so whatever values it had will be cleared out and then we will have to manually add values using AddDropDownItem().

Here is an example on the page activate PeopleCode.

Local Field &fField;

&fField = Record.EX_APR_WRK.APPROVAL_STATUS;

Component Permission List Query

Wed, 07/23/2008 - 11:25am — Lepa

An SQL that will identify permission lists along with permission lists description assigned to a specific component.

SELECT   menu.menuname, compdfn.pnlgrpname, auth.classid permission_list,
         CLASS.classdefndesc permission_desc
    FROM psauthitem auth,
         psmenudefn menu,
         psmenuitem menuitm,
         pspnlgroup comp,
         pspnlgrpdefn compdfn,
         psclassdefn CLASS
   WHERE menu.menuname = menuitm.menuname
     AND menuitm.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname = comp.pnlgrpname

Get Current Time in a Different Time Zone

Mon, 07/21/2008 - 9:21am — Lepa

It is relatively easy to select current_timestamp from dual and get current date time in your time zone. But things get a little more complicated when you want to get date and time in a different time zone. Here is how you can accomplish that:

-- Get current date and time as well as current date and time in a different time zone (using eastern time zone).
SELECT CURRENT_TIMESTAMP currt_dt,
 CURRENT_TIMESTAMP AT TIME ZONE TZ_OFFSET ('EST') different_dt
  FROM DUAL;

CURRT_DT                            DIFFERENT_DT                      

Call Unix Script from PeopleCode

Fri, 07/18/2008 - 8:32am — Lepa

A function to call UNIX and/or shell script from PeopleCode.

/*call unix script from PeopleCode*/
Function CallScript;

   /*According to PeopleBooks, PS_HOME is always prefixed to the file location*/
   &exitCode = Exec("/path/to/script/scriptname ", True);

End-Function;

The Exec command has changed in PT8.4x so the above function will be:

Function CallScript;
 
   /*Use %Exec_Asynchronous if it is not important to wait for a response from the called script*/