Peoplecode Scroll, Rowset, Row, Fields

* OriginalValue vs PriorValue

Display table column followed by data

 declare

   sql_stmt varchar2(200);

   BU varchar2(5);

   col_name varchar2(30);

   col_val  varchar2(4000);

begin

 ------cursor

   for x in (select COLUMN_NAME from user_tab_columns where TABLE_NAME=upper('ps_voucher') order by COLUMN_ID ) loop

     cnt:=cnt+1;

     sql_stmt:='select '''||x.COLUMN_NAME||''', to_char('|| x.COLUMN_NAME|| ') from ps_voucher where business_unit=:1 and voucher_id = :2'; 

   --DBMS_OUTPUT.PUT_LINE('sql='||sql_stmt);

     EXECUTE IMMEDIATE sql_stmt into col_name, col_val  USING 'BU', 'VI'

     DBMS_OUTPUT.PUT_LINE(col_name||'='||col_val);

    end loop;

 exception

    WHEN OTHERS then

     declare

        errcd  NUMBER := SQLCODE;

        errmsg VARCHAR2(300) := SQLERRM;

     begin

        DBMS_OUTPUT.PUT_LINE('Error: rec#'|| cnt || ' rc='|| errcd ||','||errmsg);

     end;

 end;

/

AE Write Log

 Local File &Logfile;

&Logfile = GetFile(“Filename”,”W”,%FilePath_Absolute);

&Logfile.WriteLine(“Your messages”);

&Logfile.Close();

AE Trace Quick Look at Tables Modified

 

UPDATE

--- get all UPDATEs

awk '/^UPDATE /,/^\//' AE_AP_MATCH_999_0302143109.AET > xxx 

--- combine multiple lines into 1 line

sed -n 'H;:a /^\/$/{x;s/\n//g;p;n;h;b a}' xxx > yyy             

--- ignore temp tables

egrep -v '^UPDATE PS_.*_(T.*[0-9]|TAO) SET ' yyy                        

 

INSERT

grep ^INSERT AE_AP_MATCH_999_0302143109.AET| cut -d' ' -f3| egrep -v 'PS_.*_(T.*[0-9]|TAO)'

Open DB in RO

Read only oracle database tips

CREATE OR REPLACE TRIGGER
   manage_service
after startup on database
DECLARE
   role VARCHAR(30);
BEGIN
   SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
   IF role = 'PRIMARY' THEN
      DBMS_SERVICE.START_SERVICE('sales_rw');
   ELSE
      DBMS_SERVICE.START_SERVICE('sales_ro');
END IF;
END;

CONNECT BY Explained

 

 Source 

Result of each iteration is used as PRIOR in next iteration. 

Calculate Field Offset in a Record

Useful for generate load file:

select table_name, COLUMN_ID, COLUMN_NAME,DATA_TYPE, DATA_LENGTH, sum(DATA_LENGTH) 

over(PARTITION BY  table_name order by COLUMN_ID RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "Running Length"

from all_tab_columns where table_name ='PS_REQ_HDR'

Export Process Instance into test environment

-----------------------------------------------reset PI 

insert into psprcsrqst  select * from psprcsrqst@db where PRCSINSTANCE = 88888888

 

insert into PSPRCSPARMS select * from PSPRCSPARMS@db WHERE    PRCSINSTANCE  = 88888888

 

update PSPRCSPARMS set PARMLIST='...', ORIGPARMLIST='...' WHERE    PRCSINSTANCE  = 88888888

 

UPDATE PS_AERUNCONTROL SET ae_run_data = to_clob('INTFAPAM    MAIN    Notify  INTFAPAM    PrcNtfy Step05  ') where PROCESS_INSTANCE=88888888

 

UPDATE PS_AERUNCONTROL SET ae_run_data = to_clob('INTFAPAM    MAIN    Notify  INTFAPAM    PrcNtfy Step06  ') where PROCESS_INSTANCE=88888888

 

insert into PSPRCSQUE select * from PSPRCSQUE@db where PRCSINSTANCE =88888888

 

insert into PS_CDM_file_LIST select * from PS_CDM_file_LIST@db where PRCSINSTANCE =88888888

 

insert into ps_cdm_list select * from ps_cdm_list@db where PRCSINSTANCE =88888888

 

------------------------------------------------- reassign output folder/content ID

update PS_CDM_file_LIST set CONTENTID=4071614, filename='AP_CRDT_MEMO.pdf',  file_size=3570, CDM_FILE_TYPE='PDF'

where PRCSINSTANCE =88888888  and CDM_FILE_TYPE='LOG'

 

update ps_cdm_list set PRCSOUTPUTDIR='...',OUTPUTDIR='...' where PRCSINSTANCE =88888888

 

update psprcsrqst set CONTENTID=4071614 where PRCSINSTANCE =88888888

------------------------------------------------- stuck in Posting

update psprcsrqst set DISTSTATUS=4, CONTENTID=4144646 where PRCSINSTANCE = 88888888                                   

update PSPRCSQUE set DISTSTATUS=4 where PRCSINSTANCE = 88888888                                                        

insert into PS_CDM_file_list                                                                                          

select 88888888,CONTENTID,'AMPS1000_4648811.log',CDM_FILE_TYPE, 166249,sysdate-1                                      

from PS_CDM_file_list where PRCSINSTANCE =88888888                                                     

insert into PS_CDM_list                                                                                              

select 88888888,CONTENTID,PRCSNAME,PRCSTYPE,'XXXXXXXX/log_output/AE_AMPS1000_88888888',

CONTENT_DESCR,OUTDESTFORMAT,RQSTDTTM,ENDDTTM,EXPIRATION_DATE,4,DISTNODENAME,OUTPUTDIR,0,ADMIN_FILENAME,GENPRCSTYPE,  

0,FILENAME,PSRF_FOLDER_NAME,PRCSBURSTRPT,MSGNODENAME,CDM_APPROVAL_FLAG,QRYXFORMFILETYPE                               

from PS_CDM_list where PRCSINSTANCE = 4687995    

J



 

How many times a sql gets run?

select SQL_ID, CHILD_NUMBER, EXECUTIONS, FIRST_LOAD_TIME, ROWS_PROCESSED, LAST_LOAD_TIME, LAST_ACTIVE_TIME,module, action
from gv$sql where sql_text like 'select * from %'

CHILD_NUMBER: 0,1,2,...
EXECUTIONS: # of time a sql runs
ROWS_PROCESSED: # of rows fetched, can increase with more fetching
MODULE: name of process created the 1st sql, set by DBMS_APPLICATION_INFO.SET_MODULE
ACTION: name of process created the 1st sql, set by DBMS_APPLICATION_INFO.SET_ACTION

Source: V$SQL reference

Source: Why some sqls show up multiple times?  CHILD_NUMBER (unique, 0 ~...)

• caused by schema difference(owner), optimizer mode(ALL_ROWS,FIRST_ROWS), bind var(implicit conversion)...
• Why cursor IS_SHAREABLE = 'No' (reason: select * from v$SQL_SHARED_CURSOR where sql_id=...) 

Source: Different v$sql views

• v$sql the details -- if you have multiple copies of the query: "select * from T" in your shared pool, v$sql will have a row per query. This can happen if user U1 and user U2 both have a table T and both issue "select * from T". Those are entirely different queries with different plans and so on. v$sql will have 2 rows.
• v$sqlarea is a aggregate of v$sql. It selects out DISTINCT sql. "select * from T" will appear there.
• v$sqltext is simply a way to see the entire query. the v$sql and v$sqlarea views only show the first 1000 bytes. newlines and other control characters are replace with whitespace.
• v$sqltext_with_newlines is v$sqltext without the whitespace replacment.
• 
  

Compare:

select sql_id, CHILD_NUMBER,EXECUTIONS, FIRST_LOAD_TIME, ROWS_PROCESSED, LAST_LOAD_TIME, LAST_ACTIVE_TIME, module, action, sql_text from gv$sql where sql_text  ='....'

with:
select sql_id, EXECUTIONS, FIRST_LOAD_TIME, ROWS_PROCESSED,  LAST_LOAD_TIME, LAST_ACTIVE_TIME , module, action, sql_text  

from gv$sqlarea where sql_text ='..'

2nd cursor will aggregate info on all columns from 1st  cursor and sum up totals from  EXECUTIONS, ROWS_PROCESSED

Source: PS 8.50 uses DBMS_APPLICATION_INFO to Identify DB Sessions

For some components the action is set to ‘xyzzy’. This seems to be a default value set when the component is opened, but before any of the pages are processed.  Therefore, it refers to activity in the search dialogue, including processing of :

• look ups to obtain values for search criteria
• SQL issued during SearchSave PeopleCode to validate the search criteria.
• the query on the Component Search record

MODULE & ACTION are different in v$sql vs v$session.

Source: Histogram vs Stats

• improve skew condition - histograms are useful for improving cardinality estimates
• Histograms are created automatically when statistics are gathered using the SKEWONLY and AUTO options in METHOD_OPT: EXEC DBMS_STATS.GATHER_TABLE_STATS( … METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO' …)
• How does the Oracle Database know that a particular column is used in a query predicate or join? This information is gathered by the Oracle Optimizer at parse time and ultimately stored in the Oracle data dictionary in a table called SYS.COL_USAGE$. 

To manually flush monitoring info: dbms_stats.flush_database_monitoring_info()

Export / Import Oracle Table/Index Stats

Source - Why Export Import optimizer statistics: 

Importing and exporting statistics for the CBO and the systems stats (external system statistics for CPU, I/O. etc) and useful in a variety of areas:

• Export production into test to make test systems "look like" large systems for execution plan generation".
• Export/imports can be used to control execution plans by "freezing execution plans".
• Statistics are used as a backup before re-analyzing a schema.
• System stats can be moved to a smaller server to make it appear as if Oracle is executing on a large fast server.         

- System stats:  When migrating to a new server, you can export the old system statistics to ensure consistent execution plans until you are ready to use the "real" system stats.

- Systems reverse:  Conversely, you can migrate system stats from production to test to make a tiny server appear to be a larger server.  This will not improve SQL execution speed, but developers will see the same execution plans that they would see in production

- Backup stats:  Before making any production change to the CBO stats with dbms_stats, take a full schema backup and an backup of your dbms_stats system stats.  Remember, the primary reason for re-analyzing stats is to change SQL execution plans.

For example, here we export production table stats and backport them to the test database to make it appear to be a larger table

Source - Steps



Source - STATs stale?

EX: 
* when table was last analyzed: select table_name, to_char(last_analyzed,'MM-DD-YYYY HH24:MI'),stale_stats from all_tab_statistics where STALE_STATS='YES'

* what tables got modified recently(MONITORING attribute must be Yes): 
select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES, to_char(timestamp,'MM-DD-YYYY HH24:MI') from dba_tab_modifications where timestamp > trunc(sysdate) and TABLE_OWNER='SYSADM'  
(delayed flushing; for immediate flush:  
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;)

During the gather_*_stats, FLUSH_DATABASE_MONITORING_INFO would have flushed the information ; it could wipe out the entry from dba_tab_modifications  instead

Source - Rebuild TABLE stats include INDEX stats?

1. create INDEX....
2. select index_name , num_rows, last_analyzed from user_indexes where table_name ='A'

3. exec dbms_stats.gather_table_stats(....)

4. select index_name , num_rows, last_analyzed from user_indexes where table_name ='A'
union
select table_name,  num_rows, last_analyzed from user_tables where table_name ='A'

Source - Restore Previous Stats
dba_histograms  
dba_tab_stats_history

exec DBMS_STATS.DELETE_TABLE_STATS
exec dbms_stats.restore_table_stats(...)
exec dbms_stats.import_table_stats

PS Online Trace Analyzer

YATA - yet another Trace analyzer... this one for Online SQL trace


based on "COM Stmt" - 

* Elapsed Time : awk '{print $5}'  xxx.tracesql  |  awk 'BEGIN {tot=0}; {tot=tot+$1; next}; END{printf "%f / %d = %f\n", tot, NR,tot/NR}'

* Dur Time: awk -F' |Dur=' xxx | cut -d' ' -f1 | awk 'BEGIN {tot=0}; {tot=tot+$3; next}; END{printf "%f / %d = %f\n", tot, NR,tot/NR*100}'

Scan SQL history for reference to Object

Quick scan on DB for sqls used a certain objects:

set serveroutput on linesize 4000

declare 

   sqltxt clob;
   cnt integer;
   
begin

      cnt:=0;
      
      for x in (select sql_id, sql_fulltext from gv$sql where regexp_like (sql_fulltext ,'OBJECT_NAME','i') 
                and not regexp_like (sql_fulltext ,'^declare|sql_fulltext|sys.col','i')) 
      loop
      
      cnt:=cnt+1;
      
      DBMS_OUTPUT.PUT_LINE(lpad(cnt,5,0)||': '|| x.sql_id ||'-'||x.sql_fulltext );

      end loop;
   
exception
   
      WHEN OTHERS then
        declare
           errcd  NUMBER := SQLCODE;
           errmsg VARCHAR2(300) := SQLERRM;
        begin
           DBMS_OUTPUT.PUT_LINE('Error: rec#'|| cnt || ' rc='|| errcd ||','||errmsg);
        end;
   
end;
/

Combine 2 lines into 1 line

paste -d " " - -
sed 'N;s/\n/ /' yourFile
xargs -l2 
xargs -n2 -d'\n'
awk '{key=$0; getline; print key ", " $0;}'

Find session record locks

select d.module, d.action, a.SID, USERNAME,OSUSER, machine, LOCK_TYPE,MODE_HELD "Lock Mode", OBJECT_TYPE "Object",OBJECT_NAME "Name",a.PROCESS,c.process||'-'||a.blocking_session "Blocked By", to_char(to_date(SECONDS_IN_WAIT,'sssss'),'hh24:mi:ss') "Wait Time" from ( 
        Select se.sql_id, lk.SID, se.username, to_single_byte(se.OSUser) osuser, to_single_byte(se.Machine) as machine, 
               DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
               DECODE (lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.lmode)) mode_held,
               DECODE (lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.request)) mode_requested,
               ob.object_id, ob.object_type, ob.object_name, decode(lk.Block, 0, 'No', 1, 'Yes', 2, 'Global') block, se.lockwait, se.process, se.blocking_session
        FROM   gv$lock lk, dba_objects ob, gv$session se
        WHERE  lk.TYPE IN ('TX', 'TM', 'UL') AND    lk.SID = se.SID AND    lk.id1 = ob.object_id (+)) a, gv$session_wait b, gV$LOCKED_OBJECT c, gv$sql d  
        where a.lockwait is not null and a.sid = b.sid and SECONDS_IN_WAIT>0
        and a.object_id=c.object_id and blocking_session is not null and a.blocking_session=c.session_id
        and LOCK_TYPE<>'Transaction'
        and a.sql_id=d.sql_id(+)
        

Oracle Table Time Stamp

Original Link

CREATED = date of creation of the object

LAST_DDL_TIME = last ddl on object, would include CREATE OR REPLACE (example below)

TIMESTAMP = last time the external "view" or "specification" of the object changed -- will be between created and last_ddl_time.

it starts with the timestamp = created = last_ddl_time. 

Windows - Search file modified last hour

source - https://answers.microsoft.com/en-us/windows/forum/all/can-i-search-for-files-modified-in-the-last-hour/d7ae54c2-e271-418f-ad0b-f1b867ea5f81

Window Explorer: modified:2020-02-24 11:30..11:45

source - https://www.windows-commandline.com/find-files-based-on-modified-time/

forfiles /P directory /S /D +01/04/2015

forfiles /S /D -3 /C "cmd /c move @file D;\archiveDir"

Linux Date Snippets

source: https://unix.stackexchange.com/questions/24626/quickly-calculate-date-differences

* date --utc -d 'mar 28 1867 +72years +11months +2days'
* expr $(date '+%s' -d 'Aug 02, 2020') - $(date '+%s' -d 'Aug 01, 2020')

get recipient from maillog

source: https://superuser.com/questions/178171/gnu-sort-by-case-sensitive

awk -F"[<>]" '{print $2}' maillog | grep -i $recipient |  LC_COLLATE=C sort --ignore-case | uniq -i

PS BP Notes

A summary from various sources

https://docs.oracle.com/cd/F25059_01/fscm92pbr34/eng/fscm/fscc/task_PeopleSoftCommitmentControlReportsListAndGeneralDescription.html

• A successful Check Only budget entry will have a Budget Hdr Status of P to indicate a valid Budget Check Only. The value P is equivalent to N (not checked). Subsequently, after full processing, a successful budget check is indicated by the Budget Hdr Status V (valid), which indicates a successful budget check and posting to the Ledger_KK record.

• A Check Only that results in errors being logged updates the Budget Hdr Status to E (errors) and the applications links an access to the exception table functions as with normal budget checking and posting. Lines with errors are updated to an E (error) status. Those that are valid remain with an N (not checked) status.

• Note: If a budget transaction line is not subject to budget checking for any Commitment Control ledger groups assigned to the General Ledger business unit, the Budget processor sets the budget line status to B. If the budget transaction line is subject to budget checking, the budget line status has a V.

• Budget Type = Ledger Group

–AP = Appropriation (APPROP)

–OR = Organization (ORG)

–PR = Project /Grant (PROJ_GRT)

–RE = Revenue Estimate (REV_EST)

• Amount Field = Ledgers within Controlled Ledger Group

–Encumbered Amount = APPROP_ENC + Posted Total Amount

–Pre‐encumbered Amount = APPROP_PRE + Posted Total Amount

–Expended Amount = APPROP_EXP + Posted Total Amount

–Budget Amount = APPROP_BUD + Posted Total Amount

https://support.oracle.com/knowledge/PeopleSoft%20Enterprise/845460_1.html

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=226133918804235&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=845460.1&_afrWindowMode=0&_adf.ctrl-state=ublcblto3_4

• Budget Period

-Budget Period equals Appropriation Year

-Budget Date derives Budget Period

-Budget Period no longer specified on transactions

-Accounting Date derives Fiscal Year and Accounting Period

• Table Changes

• LEDGER_BUDG replaced with LEDGER_KK
• BUD_JRNL_HEADER & BUD_JRNL_LN replaced with KK_BUDGET_HDR and KK_BUDGET_LN

• Link to KK tables 

•KK_SOURCE_HDR

* Header information for source transactions to GL

* This table has the source transaction information such as Voucher ID, Journal ID, etc. Join KK_ACTIVITY_LOG to get the amounts and ChartFields
* SEQUENCE_NBR_9 shows seq # of Trans processed by specific  KK_PROC_INSTANCE
* FY & PRD are not populated

*Key fields include: KK_TRAN_ID and KK_TRAN_DT. Other fields include SOURCE_TRAN (AP_VOUCHER, GL_JOURNAL, POENC, PREENC,CM_TRNXTN) SEQUENCE_NBR, KK_PROCESS_STATUS, PO_ID, REQ_ID,VOUCHER_ID, JOURNAL_ID.

•KK_SOURCE_LN

Only transaction line that pass budget checking and impacts the ledger will be inserted into KK_SOURCE_LN table.

•KK_ACTIVITY_LOG

* Child table of KK_SOURCE_LN ;
* Detailed listing of each round of BP transactions posted against budget, identified by SEQUENCE_NBR 

* used to update LEDGER_KK when successful BP of a tran, both LEDGER_KK and KK_ACTIVITY_LOG table are updated at the same time  

* stores the details behind the LEDGER_KK balances

* Does not store the source transaction ID (i.e. voucher number, journal id, etc.).
* KK_SOURCE_HDR, KK_SOURCE_LN & ACTIVITY_LOG are populated by BP from the corresponding Header table (PO_HDR, REQ_HDR, VOUCHER, JRNL_HEADER). If the field BUDGET_HDR_STATUS is "V", then the data has been populated in the KK tables.

* join KK_SOURCE_HDR by the KK_TRAN_ID, KK_TRAN_DT, * AND KK_TRAN_LN
* stores actual FY + PRD values

* Key fields include: SEQNBR, KK_TRAN_ID, KK_TRAN_DT, KK_TRAN_LN,REFERENCED_BUDGET, BALANCING_LINE, LEDGER_GROUP, LEDGER (ORG_PR, ORG_EN, ORG_EX), FISCAL_YEAR, ACCOUNTING_PERIOD.

•KK_TRANS_LOG

Captures all transactions related to each pass of BP ;
1-1 match to JRNL_LN for budget_line_status <> ‘B’
CF at detailed level
capture documents changes between each passes of the BP, identified by SEQUENCE_NBR 
for PO & REQ, BU=XXXXX while BU=AGY_NO on SOURCE_HDR
entries removed when PO is deleted

•KK_BP_LOG

        Active BP instance, cleared when done, not cleared on abend.

•KK_LIQUIDATION

Details what/has been liquidated against encumbrances

KK_SOURCE_TRAN = PO_POENC / REQ_PREENC

KK_TRAN_ID = predecessor document transaction id (PO trans id that is referenced to PO Voucher)

KK_REFD_ID = referenced document

KK_POSTED_AMT = total source tran amt

MONETARY_AMOUNT = the remaining open balance.

•KK_REFERENCED

Stores the reference data between Requisition and Purchase Order or Purchase Order and Voucher

•KK_BUDGET_HDR

Stored the budget journal header information associated with commitment control budget journals.

Key fields include: BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE,UNPOST_SEQ

•KK_BUDGET_LN

Child record of the KK_BUDGET_HDR table and store the ChartField and Amount information associated with commitment control budget journals

Key fields include: BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE,UNPOST_SEQ, JOURNAL_LINE

•KK_EXCPTN_TBL

Details exceptions per trans

•KK_OVERRIDE_TBL

Captures any overrides made to the KK ledger(s)

•KK_TRAN_ID_TBL

Last KK_TRAN_ID

•LEDGER_KK

Budget ledgers that store balances from budget checking.

Updated along with KK_ACTIVITY_LOG

Key fields include: BUSINESS_UNIT, LEDGER, FISCAL_YEAR,ACCOUNTING_PERIOD, All ChartFields

•LEDGER_BUDG_KK

Captures budget by KK ledger

reconciled to the activity/transaction logs

Trans Rules:

* PO Vchr: 2 rows on Activity_Log: 

1. DETAIL_EX: REFERENCED_BUDGET='N'; KK_QUANTITY>0; ACTIVITY=0 
2. DETAIL_EN: REFERENCED_BUDGET='Y'; KK_QUANTITY=0;  ACTIVITY=MONETARY_AMOUNT; MONETARY_AMOUNT= offset of DETAIL_EX, 

*  update KK_PROCESS_STATUS field to 'I' on tables KK SOURCE HEADER, KK_SHDR_GLJRNL, add data to PS_KK_BP_LOG for the journals that are locked when trying to open (Header Unlock)  them 

 Archiving for Commitment Control

https://docs.oracle.com/cd/E39583_01/fscm92pbr0/eng/fscm/fscc/concept_UnderstandingArchivingforCommitmentControl-9f227d.html#DeliveredArchiveProceduresforCommitmentControl-9f227b__cm0152e22