DATETIMESTAMP Field - Inserting into in SQR or SQL

UPDATE TABKLE_X
set LAST_UPDATE_DTTM = to_timestamp($SysDateTime, 'DD-MON-YYYY_HH:MI:SS.FF6_AM')

...something about version 9 :S

Last Process Instance Number in PeopleSoft

Lifted from Praj's most excellent Wiki:
http://peoplesoft.wikidot.com

The table PS_PRCSSEQUENCE stores the last sequence numbers for the Process Scheduler and Report Manager. In PeopleTools 8.48.15 there are 5 process sequence keys (PRCSSEQKEY) which you can view yourself by looking at the translates on this field:

0 = Process Instance
1 = Report Instance
2 = Transfer Instance
3 = Report ID
4 = Folder ID

It's probably a good health check to ensure that the numbers in PS_PRCSSEQUENCE match those in the relevant tables:

select max(PRCSINSTANCE) as PRCSSEQKEY0 from PSPRCSRQST;
select max(CONTENTID) PRCSSEQKEY1 from PS_CDM_AUTH;
select max(TRANSFERINSTANCE) PRCSSEQKEY2 from PS_CDM_LIST;
select max(PSRF_REPORT_ID) PRCSSEQKEY3 from PSRF_RINFO_TBL;
select max(PSRF_FOLDER_ID) PRCSSEQKEY4 from PSRF_FINFO_TBL;

Especially if you happen to be clearing out data from these tables manually. I guess if you do clear out processes/reports you'll want to update the PS_PRCSSEQUENCE accordingly.

Reports and/or log file not posting. N/A status.

You've tried everything. New run control ids, deleting rows from proc sched, holding your tongue the other way, mucking around with server reboots and caches, etc. etc.

Yet if you log in as somebody else, it works.

Delete your user. Start again.

SQL to Create Insert Script; Replace DMS

Instead using DMS to move data around you could run the following to insert a zillion rows. PL/SQL creates the SQL file for you.

CREATE OR REPLACE FUNCTION GET_INSERT_SCRIPT(V_TABLE_NAME VARCHAR2)
RETURN VARCHAR2 AS
B_FOUND BOOLEAN := FALSE;
V_TEMPA VARCHAR2 (8000);
V_TEMPB VARCHAR2 (8000);
V_TEMPC VARCHAR2 (255);
BEGIN
    FOR TAB_REC IN (SELECT TABLE_NAME
                    FROM ALL_TABLES
                    WHERE TABLE_NAME = UPPER (V_TABLE_NAME)) LOOP
        B_FOUND := TRUE;
        V_TEMPA := 'select ''insert into ' || TAB_REC.TABLE_NAME || ' (';
        FOR COL_REC IN (SELECT *
                        FROM ALL_TAB_COLUMNS
                        WHERE TABLE_NAME = TAB_REC.TABLE_NAME
                        ORDER BY COLUMN_ID) LOOP
            IF COL_REC.COLUMN_ID = 1 THEN
                V_TEMPA := V_TEMPA || '''||chr(10)||''';
            ELSE
                V_TEMPA := V_TEMPA || ',''||chr(10)||''';
                V_TEMPB := V_TEMPB || ',''||chr(10)||''';
            END IF;

            V_TEMPA := V_TEMPA || COL_REC.COLUMN_NAME;
            IF INSTR (COL_REC.DATA_TYPE, 'CHAR') > 0 THEN
                V_TEMPC := '''''''''||' || COL_REC.COLUMN_NAME || '||''''''''';
            ELSIF INSTR (COL_REC.DATA_TYPE, 'DATE') > 0 THEN
                V_TEMPC := '''to_date(''''''||to_char('
                        || COL_REC.COLUMN_NAME
                        || ',''mm/dd/yyyy hh24:mi'')||'''''',''''mm/dd/yyyy hh24:mi'''')''';
            ELSE
                V_TEMPC := COL_REC.COLUMN_NAME;
            END IF;

            V_TEMPB := V_TEMPB
                    || '''||decode('
                    || COL_REC.COLUMN_NAME
                    || ',Null,''Null'','
                    || V_TEMPC
                    || ')||''';
        END LOOP;

        V_TEMPA := V_TEMPA
                || ') values ('
                || V_TEMPB
                || ');'' from '
                || TAB_REC.TABLE_NAME
                || ';';
    END LOOP;

    IF NOT B_FOUND THEN
        V_TEMPA := '-â?? Table ' || V_TABLE_NAME || ' not found';
    ELSE
        V_TEMPA := V_TEMPA || CHR (10) || 'select ''-- commit;'' from dual;';
    END IF;

    RETURN V_TEMPA;
END;
/
SHOW ERRORS

And the Code to run it:

set head off
set pages 0
set trims on
set lines 2000
set feed off
set echo off
spool c:\Temp\genInsertForMyTable.sql
SELECT get_insert_script('PS_SAD_TAC_MAP_BOA') FROM DUAL;  -- change table here
spool off

XML Publisher: How To Guide

Summary of the 1,000,000 XML Publisher posts below. Quick little guide on how to create a basic Report Definition in v9 using Rowsets and Queries. You could modify to include Connected Queries (put data into rowset first).

XML Publisher: How to Guide

The Class referenced in the Guide (code)

Menu Navigation Path to a Component

Find your way like this:

(Also in 8.5 version of Tools you can enter the Component Name under Menu > Search Menu. Easier)

DEFINE COMPONENT_NAME = "SSS_STDNCTR_OPT";
SELECT
  A.PORTAL_URI_SEG1 AS Menu
, A.PORTAL_URI_SEG2 AS Component
, D.PNLNAME AS Page
, C.PORTAL_LABEL || '-> ' || B.PORTAL_LABEL || '-> ' || A.PORTAL_LABEL AS Portal_Path
FROM
  PSPRSMDEFN C
, PSPRSMDEFN A
    LEFT OUTER JOIN PSPRSMDEFN B
      ON A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME , PSPNLGROUP D WHERE C.PORTAL_OBJNAME = B.PORTAL_PRNTOBJNAME
  AND D.PNLGRPNAME = A.PORTAL_URI_SEG2
  AND C.PORTAL_NAME = A.PORTAL_NAME
  AND A.PORTAL_URI_SEG2 LIKE '&COMPONENT_NAME'
  ORDER BY 1,2,3,4;

XML Publisher to Window

To open directly to a pop-up window, not using the Process Scheduler:

/********************************************************************************
FRS576 CR000222 Michael Nitschke 01/06/2011
Trigger the Job that will produce the XMLP Report.
********************************************************************************/
/* FRS576: Begin */
import PSXP_RPTDEFNMANAGER:*;

Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
Local string &Report, &TemplateId, &LanguageCD, &OutputFormat;
Local date &AsOfDate;
Local Record &parms;

&Report = "AMSW3_INVCEQ";
&TemplateId = "AMSW3_INVCEQ_1";
&LanguageCD = "";
&AsOfDate = %Date;
&OutputFormat = "";

/* Create Report Definition. */
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&Report);
&oRptDefn.Get();

/* Setup Query Parameters. */
&parms = &oRptDefn.GetPSQueryPromptRecord();
&parms.INVOICE_ID.Value = AMSW3_STDNT_INV.INVOICE_ID.Value;
&oRptDefn.SetPSQueryPromptRecord(&parms);

/* Run Report. */
&oRptDefn.ProcessReport(&TemplateId, &LanguageCD, &AsOfDate, &OutputFormat);
CommitWork(); /* must do this */;

/* Display report in separate window. */
&oRptDefn.DisplayOutput();

/* FRS576 End */