Sequential Numbers - SQL (2)

MERGE INTO ps_sf_acctg_ln
USING (SELECT ROW_NUMBER() OVER(PARTITION BY run_dt,seqnum ORDER BY run_dt,seqnum, sf_line_nbr) sf_line_nbr, ROWID rid FROM ps_sf_acctg_ln) SOURCE
ON (ps_sf_acctg_ln.ROWID = SOURCE.rid)
WHEN MATCHED THEN UPDATE SET ps_SF_ACCTG_LN.SF_LINE_NBR = SOURCE.sf_line_nbr;
commit;

Sequential Numbers - SQL

This will creating a running sequence number for each unique combination of RUN_DT and SEQNUM; NEW_SF_LINE_NBR

SELECT
run_dt,
seqnum,
ROW_NUMBER() OVER(PARTITION BY run_dt,seqnum ORDER BY run_dt, seqnum) new_sf_line_nbr
FROM ps_sf_acctg_ln

Using DateTime Fields in SQR - Gotcha #343

This will return unexpected results:

AND A.CLASS_PRICE_DTTM <= $RC_FromDate

This will return expected results:

AND A.CLASS_PRICE_DTTM <= to_date($RC_FromDate, 'DD-MON-YYYY')

Where $RC_FromDate is a variable populated from a Date field on your Run Control page.

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