Tracking PeopleSoft usage with Google Analytics

One way is to place Google analytic code in HTML Object PT_HNAV_TEMPLATE.

Reference: http://www.erpassociates.com/peoplesoft-corner-weblog/peoplesoft/tracking-peoplesoft-usage-with-google-analytics.html

Question: What if PS is being accessed from Internet? Will reference to Web link not throw error?

Oracle SQL - Working with long columns (long datatype)

The LONG and LONG RAW datatypes have been deprecated in favour of LOBs for many Oracle versions, yet they still exist in the data dictionary and legacy systems.The Oracle documentation describes the LONG datatype's main restrictions as follows:

The use of LONG values is subject to these restrictions:

• A table can contain only one LONG column.
• You cannot create an object type with a LONG attribute.
• LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
• LONG columns cannot be indexed.
• LONG data cannot be specified in regular expressions.
• A stored function cannot return a LONG value.
• You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.
• Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
• LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
• If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

In addition, LONG columns cannot appear in these parts of SQL statements:

• GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
• The UNIQUE operator of a SELECT statement
• The column list of a CREATE CLUSTER statement
• The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
• SQL built-in functions, expressions, or conditions
• SELECT lists of queries containing GROUP BY clauses
• SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
• SELECT lists of CREATE TABLE ... AS SELECT statements
• ALTER TABLE ... MOVE statements
• SELECT lists in subqueries in INSERT statements

Despite the size of this list, we'll find that most of the time we are blocked by the two restrictions highlighted above.
--------------------

Issue simulation:
SELECT * FROM MYTABLE WHERE UPPER(MY_LONG_COLUMN) LIKE UPPER('%my_search_word%');

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Advice:
1) Convert the column to a CLOB

SQL> alter table MYTABLE modify (MY_LONG_COLUMN CLOB) ;

2) Write some PL/SQL to do your searching (limited upto 32K)

SQL> declare
    cursor text is select text from all_views where rownum<100;
    v varchar2(4000);
begin
    for c in text loop
        v:=c.text;
        if ( v like '%ZZ%') then
            dbms_output.put_line( 'text is '||substr(v,1,50));
        end if;
    end loop;
end;

How to identify bundles that have been applied?

Bundles Query:
SELECT A.UPDATE_ID,
  A.DESCR,
  A.DTTM_IMPORTED,
  A.FIXOPRID,
  A.APPLYOPRID,
  A.PRODUCT_LINE,
  A.RELEASEDTTM,
  A.RELEASELABEL,
  A.MAINTLOGTYPE,
  A.DESCRLONG
FROM PS_MAINTENANCE_LOG A
WHERE upper(A.DESCRLONG) LIKE '%BUNDLE%'
ORDER BY 2 DESC;

Tax Update Query:
SELECT A.UPDATE_ID,
  A.DESCR,
  A.DTTM_IMPORTED,
  A.FIXOPRID,
  A.APPLYOPRID,
  A.PRODUCT_LINE,
  A.RELEASEDTTM,
  A.RELEASELABEL,
  A.MAINTLOGTYPE,
  A.DESCRLONG
FROM PS_MAINTENANCE_LOG A
WHERE upper(A.DESCRLONG) LIKE '%TAX UPDATE%'
ORDER BY 2 DESC;

CI - Accessing a PSMessages Collection

Use the PSMessages collection to return all of the messages that occur during the session. The following example finds all the messages listed in the PSMessages collections when the Component Interface Save methods fails. The example assumes that &CI is the Component Interface object reference.

If Not &CI.Save() Then
   /* save didn't complete */
   &COLL = &MYSESSION.PSMessages;
   For &I = 1 to &COLL.Count
      &ERROR = &COLL.Item(&I);
      &TEXT = &ERROR.Text;

      /* do message processing */

   End-For;
&COLL.DeleteAll(); /* Delete all messages in queue */
End-if;

As you evaluate each message, delete it from the PSMessages collection. Or, delete all the messages in the queue at once using DeleteAll.

If there are multiple errors when saving a Component Interface, all errors are logged to the PSMessages collection, not just the first occurrence of an error.

CI - General Considerations

This section discusses general considerations for component interface programs.

WinMessage Unavailable
You cannot use the WinMessage API in a component that will be used to build a component interface. Use the MsgGet() function instead.

Email from a Component Interface
To use a component interface to send email, use the TriggerBusinessEvent PeopleCode event, not SendMail.

Related Display
Related display fields are not available for use in a component interface because they are not held in the buffer context that the component interface uses.

Row Inserts
If row insert functionality has been disabled for a page, you must take care when calling inserts against the corresponding component interface. Any PeopleCode associated with buttons used on the page to add rows will not be invoked by the component interface when an insert is done.

Note.
1. If a component has logic that inserts rows on using the RowInsert event, the component interface
cannot identify the change and locate the rows that were inserted by the application code. Generic interfaces such as Excel to Component Interfaces utility and the WSDLToCI will not function correctly when using this type of dynamic insert.

2. The same field is in different scrolls but they do not have the same value
For example, at scroll 0 you might have the field EFFDT set to 01/01/2009. You want the EFFDT field to get the same value in scroll 1, so you write code to set it from the value in level 0 but it isn't working. If this is the case, check the field name in the component interface, you may find that PeopleSoft has automatically renamed it. For example, it might be EFFDT at level 0 but something like EFFDT_1 in level 1 of the component interface. 

CI - Create a user-defined method

Create a user-defined method
1. Right-click anywhere in the component interface view.
2. Select View PeopleCode from the menu.
3. Write the required PeopleCode functions.
4. Set permissions for the methods that you created.

Exporting User-Defined Methods
If you want a user-defined component interface to be exportable, meaning used by code that instantiates the
component interface, the method PeopleCode definition must include a Doc statement. It is in the form of:
Doc <documentation for method>
where <documentation for method> describes what the method does

Example
Function MyFooBar(int foo) returns boolean
Doc  'test'
if (foo >0) then
return True;
else
return False;
end-if;
end-function; 

PS Campus Solution Time table - 'Resource in Use' Error

"The resource you are trying to schedule is currently in use. Try your save again. If after 3 attempts you still cannot save, contact your system administrator."
There is a "Resource Queue" in PeopleSoft's class scheduling system. This Queue is meant to prevent users from double booking facilities. When two people attempt to schedule facilities at the same time, the data is locked in the Resource Queue by the first user. This lock should normally be resolved in a few minutes when the first user completes his work.
If the lock lingers more than five minutes, it is probably 'stuck.' To manually remove the lock:
    Log into ISIS (EAST agents have been authorized).
    Follow the links:
    Home
    -> Curriculum Management
    -> Schedule of Classes
    -> Resource Queue Cleanup
    Click on Search.
    If a lock is found and five or more minutes have elapsed from the time listed, click on Unlock Resource.

SQL to get a process historical run status

select
    PQ.SERVERNAMERUN,  
    PQ.PRCSINSTANCE,
    PQ.PRCSTYPE,
    PQ.PRCSJOBNAME,
    PQ.PRCSNAME,
    PD.DESCR,
    PQ.OPRID,
    PQ.RUNCNTLID,
    (select  XLATSHORTNAME
        from    PSXLATITEM
        where   FIELDNAME = 'RUNSTATUS'
        and     FIELDVALUE = PQ.RUNSTATUS
    ) as RUNSTATUS,
    PQ.RUNDTTM,
    PQ.RQSTDTTM,
    PQ.ENDDTTM,
    PQ.BEGINDTTM
from
    PSPRCSRQST PQ inner join PS_PRCSDEFN PD
    on PQ.PRCSNAME = PD.PRCSNAME
where
    PQ.PRCSNAME = 'SYSAUDIT'
order by PRCSINSTANCE desc;