Production Monitoring SQL for PeopleSoft

Below SQL is used to monitor the production jobs in your production environment, It lists the jobs based on the job statuses

1)Error for 4 days

2)Cancelled for 4 days

3)Deleted for 4 days

4)Queued for last 99 days

5)Success  for 4 days

 

 

SELECT
  /*+ index (PSPRCSRQST PSBPSPRCSRQST) */
DECODE(R.RUNSTATUS,16,'333',5,'333',4,'333', 10,'111',3,'111',17,'111',1,'111',8,'111',6,'222',7,'222',9,'444',2,'000','555')
||DECODE(R.RUNSTATUS,1,'Cancel',10,'NOT Successful',16,'Pending',17,'Warning',18,'Blocked'
,19,'Restart',2,'Delete',3,'Error',4,'Hold',5,'Queued',6,'Initiated',7,'Processing',8,'Cancelled',9,'Success','N/A') AS STATUS,SERVERNAMERUN
,R.RUNDTTM AS RUNDTTM
,(CASE WHEN R.RUNSTATUS IN (16,5,4) THEN
to_char(floor(((RUNDTTM-SYSDATE)*60*24/60)/24))||'d'|| to_char(MOD(floor( (RUNDTTM-SYSDATE)*60*24/60),24)) ||'h'||to_char(MOD(FLOOR((RUNDTTM-SYSDATE)*60*24),60))||'m'
WHEN R.RUNSTATUS IN (10,3,17,1,8) THEN '0'
WHEN R.RUNSTATUS IN (6,7) THEN to_char(CEIL((SYSDATE-BEGINDTTM)*60*24))
WHEN R.RUNSTATUS IN (9) THEN to_char(CEIL((ENDDTTM-BEGINDTTM)*60*24)) ELSE TO_CHAR(R.RUNDTTM,'mm-Mon-yyyy') END) AS TTE_ETA
,DECODE(R.PRCSTYPE,'Application Engine','AE','SQR Process','SQR P','SQR Report','SQR R',R.PRCSTYPE) AS  PRCSTYPE
,R.PRCSNAME,NVL(P.DESCR,J.DESCR) AS PRCS_DESCR
,R.RUNCNTLID AS RUNCNTLID
,R.RECURNAME AS RECURNAME
,R.PRCSINSTANCE AS PI
,R.RQSTDTTM AS RQSTDTTM
,SYSDATE AS CURDTTM
,ENDDTTM
FROM
PSPRCSRQST R LEFT OUTER JOIN PS_PRCSJOBDEFN J
ON R.MAINJOBNAME = J.PRCSJOBNAME LEFT OUTER JOIN  PS_PRCSDEFN P
ON R.PRCSTYPE = P.PRCSTYPE AND R.PRCSNAME = P.PRCSNAME
WHERE
R.OPRID IN ('TC0996')
AND R.RQSTDTTM BETWEEN SYSDATE-DECODE(R.RUNSTATUS,16,99,5,99,4) AND SYSDATE
AND R.PRCSTYPE LIKE '%'
AND R.PRCSJOBSEQ=0
AND R.RUNSTATUS IN (16,5,4,10,3,17,1,8,6,7,9,3)
ORDER BY 1 ASC,RUNDTTM ASC;

Code for calling the run control process selection page from the pushbutton - field change event

Below code is used to call the run control process selection page from the field change event,

This can be used instead of adding the run control page and using the RUN push button in the page

 

Declare Function LaunchProcessRequestDlg PeopleCode PRCSRQSTDLG_WRK.LOADPRCSRQSTDLGPB FieldFormula;

 

LaunchProcessRequestDlg(GetRowset().GetRow(1).GetRecord(1), GetRowset().GetRow(1).GetRecord(1).RUN_CNTL_ID.Value, " ", True);

SQL For creating select insert statement for - PeopleSoft

Below Oracle SQL can be used in peoplesoft for generating the select insert statement. Input for the SQL is peoplesoft query name. --------------------------------------------------------------------- SELECT 'INSERT INTO PS_'||RECNAME ||' ( ' ||RTRIM(XMLAGG (XMLELEMENT (b, FIELDNAME || ',')).EXTRACT ('//text()'), ',')||')'|| ' SELECT ' ||RTRIM(XMLAGG (XMLELEMENT (b, FIELDNAME || ',')).EXTRACT ('//text()'), ',')||' FROM PS_'||RECNAME || ' WHERE ' AS GENSQL FROM PSRECFIELDDB WHERE RECNAME = 'SCB_PCS_DIM_CFN' GROUP BY RECNAME ---------------------------------------------------------------------

SQL For converting delimitted string to individual rows

ORACLE SQL For converting delimitted string row into individual rows

SELECT
STRTEXT
,SUBSTR
(STRTEXT,
DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1),
(CASE WHEN ((INSTR(STRTEXT,DELIMTR,1,ROWNUM)-1) - DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1)+1) <0 THEN
(LENGTH(STRTEXT) - DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1)+1)
ELSE
((INSTR(STRTEXT,DELIMTR,1,ROWNUM)-1) - DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1)+1)
END)
) AS SUBSTR
,DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1) AS STARTNUM
, (CASE WHEN ((INSTR(STRTEXT,DELIMTR,1,ROWNUM)-1) - DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1)+1) <0 THEN
(LENGTH(STRTEXT) - DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1)+1)
ELSE ((INSTR(STRTEXT,DELIMTR,1,ROWNUM)-1) - DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1)+1)
END) AS ENDNUM
FROM ALL_VIEWS
,
(
SELECT
'aWW,bDD,cDD,dFF,eAA,fCC' AS STRTEXT
, ',' AS DELIMTR
FROM PSCLOCK
)SUBQ
WHERE DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)) <> 0

PeopleSoft Excel Data Upload using java classes

Declare Function ConvertDate PeopleCode SCB_JUP_PAG_WRK.SUBMIT_BTN FieldFormula;


Local object &oWorkApp, &oWorkBook;
Local SQL &Jrnl_hdr_sql, &Jrnl_ln_sql;
Local Record &Jrnl_hdr_rec, &Jrnl_ln_rec;
Local number &Row_count, &Col_count, &Count, &i, &Jrnl_line_nbr, &Jrnl_total_lines;
Local number &Jrnl_ln_Tran_Amt, &Jrnl_ln_Base_Amt, &Jrnl_ln_Stat_Amt, &Jrnl_ln_Ex_Rate;
Local string &Jrnl_ln_Account, &Jrnl_ln_BU, &Jrnl_Hdr_Bu, &Jrnl_hdr_Journal_Date1;
Local date &Jrnl_hdr_Journal_Date, &Jrnl_hdr_Rev_Date;

Local string &Jrnl_hdr_Journal_ID, &Jrnl_hdr_Ledger_GRP, &Jrnl_hdr_Base_CURR, &Jrnl_hdr_Source;

/* Initializing Column and Row Positions for Reading Journal Header Details from Excel */

Local number &Col_Jrnl_ID = 0;
Local number &Col_Journal_Date = 1;
Local number &Col_Ledger_GRP = 2;
Local number &Col_Base_CURR = 3;
Local number &Col_Source = 4;
Local number &Col_Rev_Code = 5;
Local number &Col_Def_ex_rate = 6;
Local number &Col_Def_Rev_Date = 7;
Local number &Col_Rate_Type = 8;
Local number &Col_Verify_Rate = 10;
Local number &Col_Trans_Ref_Num = 12;
Local number &Row_Trans_Ref_Num = 0;
Local number &Col_Descr254 = 1;
Local number &Row_Descr254 = 3;


Local number &Row_Journal_Class = 5;
Local number &Row_Journal_Desc = 3;
Local number &Row_Header = 2;

/* Initializing Column and Row Positions for Reading Journal Line Details from Excel */

Local number &Col_BU = 0;
Local number &Col_Account = 1;
Local number &Col_Product = 2;
Local number &Col_Deptid = 3;
Local number &Col_Oper_Unit = 4;
Local number &Col_Class_Fld = 5;
Local number &Col_Project_ID = 6;
Local number &Col_Affiliate = 7;
Local number &Col_Trans_Curr = 8;
Local number &Col_Tran_Amt = 9;
Local number &Col_Ex_Rate = 10;
Local number &Col_Base_Amt = 11;
Local number &Col_VAT_App = 12;
Local number &Col_VAT_CD = 13;
Local number &Col_VAT_Trans_Type = 14;
Local number &Col_Ref = 15;
Local number &Col_Descr = 16;
Local number &Col_Open_Item = 17;
Local number &Col_Stat_Code = 18;
Local number &Col_Stat_Amt = 19;
Local number &Col_Cust_ID = 20;


Local string &SCB_FilePath = SCB_JUP_UPD_AET.ATTACHUSERFILE.Value;
Local JavaObject &SCB_JUP_Object = CreateJavaObject("jup");
Local JavaObject &SCB_WorkBook = &SCB_JUP_Object.initiateworkbook(&SCB_FilePath);
Local JavaObject &SCB_Sheet = &SCB_WorkBook.getSheet("Journal1");

&SCB_Excel_Ready = &SCB_Sheet.getCell(12, 5).getContents();
&SCB_Version = &SCB_Sheet.getCell(13, 1).getContents();
&SCB_Version = RTrim(LTrim(&SCB_Version));
&SCB_Row_Count = &SCB_Sheet.getCell(14, 1).getContents();

If &SCB_Excel_Ready <> "Ready" Then
&SCB_WorkBook.close();
Error MessageBox(0, "", 23000, 44, "The Excel trying to upload is not in Ready status. Please check and load again. Process Failed.");
Exit (1);
Else
&Jrnl_Hdr_Bu = " ";

/* Read Header row from Excel columns*/
&Jrnl_hdr_Journal_ID = &SCB_Sheet.getCell(&Col_Jrnl_ID, &Row_Header).getContents();

&Jrnl_hdr_Journal_Date1 = &SCB_Sheet.getCell(&Col_Journal_Date, &Row_Header).getContents();
&Jrnl_hdr_Ledger_GRP = &SCB_Sheet.getCell(&Col_Ledger_GRP, &Row_Header).getContents();
&Jrnl_hdr_Base_CURR = &SCB_Sheet.getCell(&Col_Base_CURR, &Row_Header).getContents();
&Jrnl_hdr_Source = &SCB_Sheet.getCell(&Col_Source, &Row_Header).getContents();
&Jrnl_hdr_Rev_Code = &SCB_Sheet.getCell(&Col_Rev_Code, &Row_Header).getContents();
&Jrnl_hdr_Def_ex_rate = &SCB_Sheet.getCell(&Col_Def_ex_rate, &Row_Header).getContents();
&Jrnl_hdr_Rev_Date1 = &SCB_Sheet.getCell(&Col_Def_Rev_Date, &Row_Header).getContents();
&Jrnl_hdr_Rate_Type = &SCB_Sheet.getCell(&Col_Rate_Type, &Row_Header).getContents();
&Jrnl_hdr_Verify_Rate = &SCB_Sheet.getCell(&Col_Verify_Rate, &Row_Header).getContents();
&Jrnl_hdr_Journal_Class = &SCB_Sheet.getCell(2, &Row_Journal_Class).getContents();
&Jrnl_hdr_Journal_Desc = &SCB_Sheet.getCell(1, &Row_Journal_Desc).getContents();
&Jrnl_hdr_Trans_Ref_Num = &SCB_Sheet.getCell(&Col_Trans_Ref_Num, &Row_Trans_Ref_Num).getContents();
&Jrnl_hdr_Descr254 = &SCB_Sheet.getCell(&Col_Descr254, &Row_Descr254).getContents();

<*SQLExec("SELECT to_char(to_date(:1, 'dd-mm-yyyy'),'ddmmyyyy') from PSCLOCK", &Jrnl_hdr_Journal_Date1, &Journal_Date);
rem SQLExec("SELECT %Dateout(:1) from PSCLOCK", &Jrnl_hdr_Journal_Date1, &Jrnl_hdr_Journal_Date);
SQLExec("SELECT to_char(to_date(:1, 'dd-mm-yyyy'),'ddmmyyyy') from PSCLOCK", &Jrnl_hdr_Rev_Date1, &Rev_Date);*>
MessageBox(0, "", 0, 0, "&Jrnl_hdr_Journal_Date1: " | &Jrnl_hdr_Journal_Date1);
&Journal_Date = ConvertDate(&Jrnl_hdr_Journal_Date1);
MessageBox(0, "", 0, 0, "&Journal_Date: " | &Journal_Date);
&Jrnl_hdr_Journal_Date = DateValue(&Journal_Date);
If All(&Jrnl_hdr_Rev_Date1) Then
MessageBox(0, "", 0, 0, "&Jrnl_hdr_Rev_Date1 " | &Jrnl_hdr_Rev_Date1);
&Rev_Date = ConvertDate(&Jrnl_hdr_Rev_Date1);
&Jrnl_hdr_Rev_Date = DateValue(&Rev_Date);
End-If;
MessageBox(0, "", 0, 0, "&Jrnl_hdr_Rev_Date: " | &Jrnl_hdr_Rev_Date);

&Jrnl_hdr_rec = CreateRecord(Record.SCB_JUP_HDR_TMP);
&Jrnl_hdr_sql = CreateSQL("%Insert (:1)");
&Jrnl_hdr_sql.BulkMode = True;

&Jrnl_ln_count = 0;
&Jrnl_ln_rec = CreateRecord(Record.SCB_JUP_LN_TMP);
&Jrnl_ln_sql = CreateSQL("%Insert (:1)");
&Jrnl_ln_sql.BulkMode = True;

For &i = 10 To &SCB_Row_Count - 1

&Jrnl_ln_BU = &SCB_Sheet.getCell(&Col_BU, &i).getContents();
If All(&Jrnl_ln_BU) Then
If &Jrnl_ln_BU <> &Jrnl_Hdr_Bu Then
&Jrnl_ln_count = 0;
&Jrnl_Hdr_Bu = &Jrnl_ln_BU;
SQLExec(SQL.SCB_JUP_GETVER_SQL, &Jrnl_Hdr_Bu, &Version);
If &Version <> &SCB_Version Then
&SCB_WorkBook.close();
Error MessageBox(0, "", 23000, 56, "The version of the excel uploaded is incorrect. Process Failed.");
Exit (1);
End-If;
&Jrnl_hdr_rec.PROCESS_INSTANCE.Value = SCB_JUP_UPD_AET.PROCESS_INSTANCE;
&Jrnl_hdr_rec.RUNDATE.Value = %Date;
&Jrnl_hdr_rec.EMPLID.Value = %OperatorId;
&Jrnl_hdr_rec.BUSINESS_UNIT.Value = &Jrnl_Hdr_Bu;
&Jrnl_hdr_rec.JOURNAL_ID.Value = &Jrnl_hdr_Journal_ID;
&Jrnl_hdr_rec.JOURNAL_DATE.Value = &Jrnl_hdr_Journal_Date;
rem &Jrnl_hdr_rec.JOURNAL_DATE.Value = %Date;
&Jrnl_hdr_rec.LEDGER_GROUP.Value = &Jrnl_hdr_Ledger_GRP;
&Jrnl_hdr_rec.DESCR254.Value = &Jrnl_hdr_Descr254;
&Jrnl_hdr_rec.CURRENCY_CD.Value = &Jrnl_hdr_Base_CURR;
&Jrnl_hdr_rec.SOURCE.Value = &Jrnl_hdr_Source;
&Jrnl_hdr_rec.FOREIGN_CURRENCY.Value = &Jrnl_hdr_Base_CURR;
&Jrnl_hdr_rec.REVERSAL_CD.Value = &Jrnl_hdr_Rev_Code;
&Jrnl_hdr_rec.RATE_DIV.Value = 1;
&Jrnl_hdr_rec.REVERSAL_DATE.Value = &Jrnl_hdr_Rev_Date;
&Jrnl_hdr_rec.RT_TYPE.Value = &Jrnl_hdr_Rate_Type;
&Jrnl_hdr_rec.SCB_JUP_RT_CHK.Value = &Jrnl_hdr_Verify_Rate;
&Jrnl_hdr_rec.TRANS_REF_NUM.Value = &Jrnl_hdr_Trans_Ref_Num;
&Jrnl_hdr_rec.JOURNAL_CLASS.Value = &Jrnl_hdr_Journal_Class;
&Jrnl_hdr_rec.DESCR200.Value = &Jrnl_hdr_Journal_Desc;
&Jrnl_hdr_rec.JRNL_TOTAL_DEBITS.Value = 0;
&Jrnl_hdr_rec.JRNL_TOT_CREDITS.Value = 0;
&Jrnl_hdr_sql.Execute(&Jrnl_hdr_rec);
End-If;

&Jrnl_ln_count = &Jrnl_ln_count + 1;
rem &Jrnl_ln_Ledger = &SCB_Sheet.getCell(&Col_Ledger, &i).getContents();
&Jrnl_ln_Account = &SCB_Sheet.getCell(&Col_Account, &i).getContents();
&Jrnl_ln_Product = &SCB_Sheet.getCell(&Col_Product, &i).getContents();
&Jrnl_ln_Deptid = &SCB_Sheet.getCell(&Col_Deptid, &i).getContents();
&Jrnl_ln_Oper_Unit = &SCB_Sheet.getCell(&Col_Oper_Unit, &i).getContents();
&Jrnl_ln_Class_Fld = &SCB_Sheet.getCell(&Col_Class_Fld, &i).getContents();
&Jrnl_ln_Project_ID = &SCB_Sheet.getCell(&Col_Project_ID, &i).getContents();
&Jrnl_ln_Affiliate = &SCB_Sheet.getCell(&Col_Affiliate, &i).getContents();
&Jrnl_ln_Trans_Curr = &SCB_Sheet.getCell(&Col_Trans_Curr, &i).getContents();
&Jrnl_ln_Tran_Amt = &SCB_Sheet.getCell(&Col_Tran_Amt, &i).getContents();
&Jrnl_ln_Ex_Rate = &SCB_Sheet.getCell(&Col_Ex_Rate, &i).getContents();
&Jrnl_ln_Base_Amt = &SCB_Sheet.getCell(&Col_Base_Amt, &i).getContents();
&Jrnl_ln_VAT_App = &SCB_Sheet.getCell(&Col_VAT_App, &i).getContents();
&Jrnl_ln_VAT_CD = &SCB_Sheet.getCell(&Col_VAT_CD, &i).getContents();
&Jrnl_ln_VAT_Trans_Type = &SCB_Sheet.getCell(&Col_VAT_Trans_Type, &i).getContents();
&Jrnl_ln_Ref = &SCB_Sheet.getCell(&Col_Ref, &i).getContents();
&Jrnl_ln_Descr = &SCB_Sheet.getCell(&Col_Descr, &i).getContents();
&Jrnl_ln_Open_Item = &SCB_Sheet.getCell(&Col_Open_Item, &i).getContents();
&Jrnl_ln_Stat_Code = &SCB_Sheet.getCell(&Col_Stat_Code, &i).getContents();
&Jrnl_ln_Stat_Amt = &SCB_Sheet.getCell(&Col_Stat_Amt, &i).getContents();
&Jrnl_ln_Cust_ID = &SCB_Sheet.getCell(&Col_Cust_ID, &i).getContents();



/* copy to the record */
&Jrnl_ln_rec.PROCESS_INSTANCE.Value = SCB_JUP_UPD_AET.PROCESS_INSTANCE;
&Jrnl_ln_rec.RUNDATE.Value = %Date;
&Jrnl_ln_rec.EMPLID.Value = %OperatorId;
&Jrnl_ln_rec.BUSINESS_UNIT.Value = &Jrnl_ln_BU;
&Jrnl_ln_rec.JOURNAL_ID.Value = &Jrnl_hdr_Journal_ID;
&Jrnl_ln_rec.JOURNAL_DATE.Value = &Jrnl_hdr_Journal_Date;
rem &Jrnl_ln_rec.JOURNAL_DATE.Value = %Date;
&Jrnl_ln_rec.JOURNAL_LINE.Value = &Jrnl_ln_count;
&Jrnl_ln_rec.LINE_NBR.Value = &i + 1;
&Jrnl_ln_rec.LEDGER.Value = &Jrnl_ln_Ledger;
&Jrnl_ln_rec.ACCOUNT.Value = &Jrnl_ln_Account;
&Jrnl_ln_rec.PRODUCT.Value = &Jrnl_ln_Product;
&Jrnl_ln_rec.DEPTID.Value = &Jrnl_ln_Deptid;
&Jrnl_ln_rec.OPERATING_UNIT.Value = &Jrnl_ln_Oper_Unit;
&Jrnl_ln_rec.CLASS_FLD.Value = &Jrnl_ln_Class_Fld;
&Jrnl_ln_rec.PROJECT_ID.Value = &Jrnl_ln_Project_ID;
&Jrnl_ln_rec.AFFILIATE.Value = &Jrnl_ln_Affiliate;
&Jrnl_ln_rec.FOREIGN_CURRENCY.Value = &Jrnl_ln_Trans_Curr;
&Jrnl_ln_rec.FOREIGN_AMOUNT.Value = &Jrnl_ln_Tran_Amt;
&Jrnl_ln_rec.CURRENCY_CD.Value = &Jrnl_hdr_Base_CURR;
&Jrnl_ln_rec.MONETARY_AMOUNT.Value = &Jrnl_ln_Base_Amt;
&Jrnl_ln_rec.RATE_MULT.Value = &Jrnl_ln_Ex_Rate;
&Jrnl_ln_rec.VAT_APPLICABILITY.Value = &Jrnl_ln_VAT_App;
&Jrnl_ln_rec.TAX_CD.Value = &Jrnl_ln_VAT_CD;
&Jrnl_ln_rec.VAT_TXN_TYPE_CD.Value = &Jrnl_ln_VAT_Trans_Type;
&Jrnl_ln_rec.JRNL_LN_REF.Value = &Jrnl_ln_Ref;
&Jrnl_ln_rec.LINE_DESCR.Value = &Jrnl_ln_Descr;
&Jrnl_ln_rec.OPEN_ITEM_KEY.Value = &Jrnl_ln_Open_Item;
&Jrnl_ln_rec.STATISTICS_CODE.Value = &Jrnl_ln_Stat_Code;
&Jrnl_ln_rec.STATISTIC_AMOUNT.Value = &Jrnl_ln_Stat_Amt;
&Jrnl_ln_rec.CUST_ID.Value = &Jrnl_ln_Cust_ID;
&Jrnl_ln_rec.JOURNAL_LINE_DATE.Value = &Jrnl_hdr_Journal_Date;
rem &Jrnl_ln_rec.JOURNAL_LINE_DATE.Value = %Date;
&Jrnl_ln_rec.RT_TYPE.Value = &Jrnl_hdr_Rate_Type;

&Jrnl_ln_sql.Execute(&Jrnl_ln_rec);

End-If;

End-For;

&Jrnl_hdr_sql.Close();
&Jrnl_ln_sql.Close();
End-If;
&SCB_WorkBook.close();


###############################################################
jup.java - Java Code & Related Jar details
this class is created as a wrapper class to avoid error
###############################################################

import java.io.*;
import jxl.*;
public class jup {
public static void main(String[] args){

}
public Workbook initiateworkbook(String Filename){
try {
Workbook ReadExcel = Workbook.getWorkbook(new File(Filename));
return ReadExcel;
}
catch (Exception i)
{

return null;
}
}
}


###############################################################
Jar details
###############################################################

Above referenced jars can be downloaded from the below
mentioned locaiton.
http://www.java2s.com/Code/Jar/j/Downloadjxljar.htm

Place the jup.java file in claases folder of appserver and reboot the appserver.



above code is in reference from Mr.Bhaskaran RajGanesh

PeopleSoft Application Engine Trace Settings

PeopleSoft Application Engine Trace Settings

-TRACE 135 -TOOLSTRACEPC 3596


SQL for formatting the number in oracle db
SELECT TO_CHAR(2354867423595,'99G99G99G99G99G99G999') FROM PSCLOCK

Group by aggregation of a string column using connect by and partition by - different approach

SELECT * FROM TEST001

Result:

RULE_ID SEQ_NUM EXPR
-----------------------------
GROUP_001 1 (
GROUP_001 2 GP
GROUP_001 3 +
GROUP_001 4 GM
GROUP_001 5 )
GROUP_002 1 (
GROUP_002 2 GP
GROUP_002 3 +
GROUP_002 4 GM
GROUP_002 5 )

SELECT
RULE_ID,
REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(EXPR, ' '),2),' ','') EXPR
FROM
(
SELECT
EXPR,
RULE_ID,
COUNT(*) OVER ( PARTITION BY RULE_ID ) CNT,
ROW_NUMBER () OVER ( PARTITION BY RULE_ID ORDER BY SEQ_NUM) seq
FROM
TEST001
WHERE
RULE_ID IS NOT NULL
)
WHERE
SEQ=CNT
START WITH
SEQ=1
CONNECT BY PRIOR
SEQ+1=SEQ
and prior
RULE_ID=RULE_ID;

Result:
RULE_ID EXPR
-----------------------
GROUP_001 (GP+GM)
GROUP_002 (GP+GM)

XML Aggregator function in ORACLE - Sample Use

Below is the XMLAGG xml aggregator function used for a different purpose,i.e for aggregating the strings.


SELECT * FROM TEST001

Result:

RULE_ID SEQ_NUM EXPR
----------------------------
GROUP_001 1 (
GROUP_001 2 GP
GROUP_001 3 +
GROUP_001 4 GM
GROUP_001 5 )
GROUP_002 1 (
GROUP_002 2 GP
GROUP_002 3 +
GROUP_002 4 GM
GROUP_002 5 )


SELECT
RULE_ID,
RTRIM(XMLAGG (XMLELEMENT (b, EXPR || '')).EXTRACT ('//text()'), ',') EXPR
FROM
TEST001
GROUP BY RULE_ID

Result:

RULE_ID EXPR
------------------------
GROUP_001 (GP+)GM
GROUP_002 (GP+)GM

DateTime Diff Query

Sample query for finding out the difference between two ORACLE time stamp fields


SELECT PROCESS_INSTANCE, EMPLID, BUSINESS_UNIT,
WF_FROM_DTTM, WF_TO_DTTM,
FLOOR(((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60)/3600)
|| ' HOURS ' ||
FLOOR((((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60) -
FLOOR(((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60)/3600)*3600)/60)
|| ' MINS ' ||
ROUND((((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60) -
FLOOR(((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60)/3600)*3600 -
(FLOOR((((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60) -
FLOOR(((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' AS "RUN TIME"
FROM PS_SCB_JUP_REQ_ACT WHERE SCB_JUP_APR_TYPE = 'CFP'

Below PeopleCode reference is used to send the HTML mail format.

&ContentType = "Content-type: text/html; charset=UTF-8";
SendMail(0, &strEmailTo, &strEmailCC, &strEmailBCC, &Subject, &Message, &strFileNames, &strFileTitles, &tempEmailReplyTo, ";", &ContentType);

Below SQL code reference is used to specify the thousands seperator automatically at database level.
SELECT TO_CHAR(2354867423595,'99G99G99G99G99G99G999') FROM PSCLOCK

Find file in the shell directory hierarchy

below reference shell command will find the file named ROLM.DAT in the directory tree specified.
find ./ -name 'ROLM.DAT' -exec ls -ltr '{}' \;

PepleSoft SQR Two- Tier Running Parameter reference

SQR Two- Tier Running Parameter reference

-iE:\psoft\SQR\ -xmb -xcb -ZIFE:\psoft\SQR\pssqr.ini -S -DEBUGX -PRINTER:PD
-EH_PDF -fE:\temp\genpdf1.pdf -oE:\psoft\setdbnam.log


-iC:\PSHOME\pcfls\ps89_pt84707\sqr\ -ZIFC:\PSHOME\pcfls\ps89_pt84707\sqr\pssqr.ini -PRINTER:PD C:\TEMP\

PeopleSoft Tech Spec SQLs

----------RECNAME AND RECFIELD LEVEL SPECS GENERATION
SELECT RECNAME,FIELDNAME,
(CASE
WHEN USEEDIT=8388609 THEN 'K'
WHEN USEEDIT=8407329 OR USEEDIT=8390945 THEN 'K/S'
WHEN USEEDIT=8388673 THEN 'K/D' ELSE ' '
END) AS KSA
,(CASE WHEN USEEDIT=8407329 OR USEEDIT=8390945 THEN 'L' ELSE ' ' END) AS LISTBOX
,(CASE WHEN USEEDIT=8407329 OR USEEDIT=8390945 OR USEEDIT=8405248 THEN 'R' ELSE ' ' END) AS REQUIRED
,(CASE WHEN DEFRECNAME=' ' THEN DEFFIELDNAME ELSE DEFRECNAME||'.'||DEFFIELDNAME END) AS DEFAULTV,EDITTABLE FROM PSRECFIELDDB WHERE RECNAME IN
(
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM
WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTID1 = 1 AND OBJECTTYPE=0
)
ORDER BY RECNAME,FIELDNUM

----------RECNAME LEVEL SPECS
SELECT DISTINCT RECNAME,DECODE(RECTYPE,0,'Table',7,'Temp Table','DEF') AS RECTYPE,'A','New Record' FROM PSRECDEFN WHERE RECNAME IN
(
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM
WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTID1 = 1 AND OBJECTTYPE=0
)
ORDER BY RECNAME;
COMMIT;
-----------------------FIELD DETAILS - PROIDED THE PROJECT NAME

SELECT
F.FIELDNAME
,( CASE WHEN F.FIELDTYPE = 3 THEN 'Signed Number'
WHEN F.FIELDTYPE = 2 THEN 'Number'
WHEN F.FIELDTYPE = 0 THEN 'Character' END) AS FIELDTYPE
,( CASE WHEN F.FIELDTYPE = 3 THEN TO_CHAR(F.LENGTH-F.DECIMALPOS-1)||','||TO_CHAR(F.DECIMALPOS)
WHEN F.FIELDTYPE = 2 THEN TO_CHAR(F.LENGTH-F.DECIMALPOS)||','||TO_CHAR(F.DECIMALPOS)
WHEN F.FIELDTYPE = 0 THEN TO_CHAR(F.LENGTH) END) AS LENGTH
,L.LONGNAME
,L.SHORTNAME
,'A'
,'NEW FIELD'
FROM PSDBFIELD F,PSDBFLDLABL L WHERE F.FIELDNAME IN (
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM P WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTID1 = 6 AND OBJECTTYPE=2
)
AND F.FIELDNAME = L.FIELDNAME
AND L.DEFAULT_LABEL = 1
AND F.FIELDNAME LIKE 'SCB%'

------------------------------------------------XLAT VALUES BASED ON THE PRJ NAME-------------------
SELECT FIELDNAME
,FIELDVALUE AS XLATVALUE
,XLATLONGNAME AS LONGNAME
,XLATSHORTNAME AS SHORTNAME
,'A'
,'NEW XLAT'
FROM PSXLATITEM WHERE FIELDNAME IN (
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM P WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTID1 = 6 AND OBJECTTYPE=2 )
--------------------------------------------------PAGE NAMES----------------------------------------------------

SELECT * FROM PSPROJECTITEM P WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTTYPE=8 AND OBJECTID1 = 1
SELECT MESSAGE_SET_NBR,MESSAGE_NBR,'A',MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 22300
------------------------------------------------------------------------------------

PeopleSoft Reference SQLs

UPDATE PSRECTBLSPC SET DDLSPACENAME = 'GLAPP' WHERE RECNAME IN
(
SELECT DISTINCT RECNAME FROM PSRECDEFN WHERE RECNAME IN
(
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM
WHERE PROJECTNAME = 'SCBCSRTE186' AND OBJECTID1 = 1 AND OBJECTTYPE=0
) AND RECTYPE = 0
)
AND
DDLSPACENAME <> 'GLAPP'
;COMMIT;



SELECT DISTINCT R.ROLENAME,P.CLASSID
FROM PSROLECLASS R,PSAUTHITEM P,PSROLEUSER U
WHERE R.CLASSID = P.CLASSID
AND U.ROLENAME = R.ROLENAME
AND U.ROLEUSER = '1354466'
AND P.MENUNAME='PROCESSMONITOR'

SELECT * FROM PSAUTHITEM WHERE MENUNAME='PROCESSMONITOR'

SELECT * FROM PSROLECLASS WHERE CLASSID='STPPTPT1200'


SELECT DISTINCT C.ROLEUSER,CMP.AUTHORIZEDACTIONS,CMP.DISPLAYONLY
FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C ,PSAUTHITEM CMP
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND CMP.CLASSID = A.CLASSID
AND CMP.PNLITEMNAME = 'RUN_BIBSCMON'
AND A.CLASSID = 'XXX_BILLING_ALL'


SELECT * FROM PSAUTHITEM WHERE PNLITEMNAME = 'RUN_BIBSCMON'

----VENDOR RELATED TABLES

UPDATE PS_VENDOR_ADDR SET EMAILID = 'a@a.a';
COMMIT;

UPDATE PS_VENDOR_ADDR SET NAME1 =
TRANSLATE(LOWER(REVERSE(NAME1)),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');
COMMIT;

UPDATE PS_VENDOR_ADDR SET NAME2 =
TRANSLATE(LOWER(REVERSE(NAME2)),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR SET ADDRESS1 =
TRANSLATE(LOWER(REVERSE(ADDRESS1)),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR SET ADDRESS2 =
TRANSLATE(LOWER(REVERSE(ADDRESS2)),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR SET ADDRESS3 =
TRANSLATE(LOWER(REVERSE(ADDRESS3)),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR SET ADDRESS4 =
TRANSLATE(REVERSE(ADDRESS4),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR SET COUNTRY =
TRANSLATE(REVERSE(COUNTRY),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR SET POSTAL =
TRANSLATE(REVERSE(POSTAL),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR_PHN SET PHONE =
TRANSLATE(REVERSE(PHONE),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR SET VENDOR_NAME_SHORT =
TRANSLATE(REVERSE(VENDOR_NAME_SHORT),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR SET VNDR_NAME_SHRT_USR =
TRANSLATE(REVERSE(VNDR_NAME_SHRT_USR),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR SET NAME1 =
TRANSLATE(REVERSE(NAME1),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');


-----------PS_PERSONAL_DATA

UPDATE PS_PERSONAL_DATA SET NAME =
TRANSLATE(REVERSE(NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');
COMMIT;

UPDATE PS_PERSONAL_DATA SET
LAST_NAME_SRCH=TRANSLATE(REVERSE(LAST_NAME_SRCH),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
,FIRST_NAME_SRCH=TRANSLATE(REVERSE(FIRST_NAME_SRCH),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');
COMMIT;

UPDATE PS_PERSONAL_DATA SET LAST_NAME=TRANSLATE(REVERSE(LAST_NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
,FIRST_NAME=TRANSLATE(REVERSE(FIRST_NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
,ADDRESS1=TRANSLATE(REVERSE(ADDRESS1),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS2=TRANSLATE(REVERSE(ADDRESS2),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS3=TRANSLATE(REVERSE(ADDRESS3),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS1_OTHER=TRANSLATE(REVERSE(ADDRESS1_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS2_OTHER=TRANSLATE(REVERSE(ADDRESS2_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS3_OTHER=TRANSLATE(REVERSE(ADDRESS3_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS4_OTHER=TRANSLATE(REVERSE(ADDRESS4_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
;
COMMIT;

----------------PS_PERS_DATA_EFFDT

UPDATE PS_PERS_DATA_EFFDT SET NAME=TRANSLATE(REVERSE(NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
,LAST_NAME_SRCH=TRANSLATE(REVERSE(LAST_NAME_SRCH),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
,FIRST_NAME_SRCH=TRANSLATE(REVERSE(FIRST_NAME_SRCH),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, LAST_NAME=TRANSLATE(REVERSE(LAST_NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, FIRST_NAME=TRANSLATE(REVERSE(FIRST_NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS1=TRANSLATE(REVERSE(ADDRESS1),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS2=TRANSLATE(REVERSE(ADDRESS2),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS3=TRANSLATE(REVERSE(ADDRESS3),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS1_OTHER=TRANSLATE(REVERSE(ADDRESS1_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS2_OTHER=TRANSLATE(REVERSE(ADDRESS2_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS3_OTHER=TRANSLATE(REVERSE(ADDRESS3_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS4_OTHER=TRANSLATE(REVERSE(ADDRESS4_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
;
COMMIT;

--------------BANK ACCOUNT DETAILS

UPDATE PS_BANK_ACCT_DEFN SET BANK_ACCOUNT_NUM =
TRANSLATE(REVERSE(BANK_ACCOUNT_NUM),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_BANK_ACCT_DEFN SET BANK_ACCT_KEY =
TRANSLATE(REVERSE(BANK_ACCT_KEY),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_EX_EE_PYMNT_DTL SET BANK_ACCOUNT_NUM =
TRANSLATE(REVERSE(BANK_ACCOUNT_NUM),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');
COMMIT;

------------SECURITY

UPDATE PSOPRDEFN SET OPRDEFNDESC =
TRANSLATE(REVERSE(OPRDEFNDESC),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_ROLEXLATOPR SET DESCR =
TRANSLATE(REVERSE(DESCR),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');
COMMIT;

------------------------END---------

Bribery in india

General analysis about bribery in india.

Today, morning i came across the news that poor people in india pay money for their basic needs in the society, since i lived in india and i know that it is happening now. I have put some thinking on the matter and put forward my thinking in the topic (why bribery happens and its consequences).

• first I like to undermine why that bribery is happening?

Even though the public services employees are being paid by the government, then why do they take bribes for the daily work they are supposed to do and provide to tax payers or people. Two things that i think that may move them to demand bribes for doing their regular task is, one peer pressure from their relatives and two they think people are getting free service in public hospitals (Public hospitals are for free serives, but they do have cost, tax money).

Lets examine why the peer pressure from relatives are pressuring them to accept bribes, lets see how an indian family works, since the indian families are closed families, means people communicate with their family members a lot, i mean family as people who also related to your mother,father,brothers,sisters and their respective relatives.
When there is some one who earns more in the family circle, then if i am in the family and work in a public service facility, I like to make money and to show off to the guy who has more money in the family(family in the sense i maent the outer cirlce).

When a guy with no education or less education works in the public facility, all he knows is the world around him in that he thinks the service offered here in the public facility is free and he can make money by asking for bribery.

• next I like to speak about the general consequences of the bribery?

Consequence is that when somebody is taking bribe that they need to understand that they are not making money neither for them nor family nor for the nation, why, since if every body starts taking bribe then every body needs to pay bribe, thats it, it means null effect , such that you see more money in the wallet but it goes waste as un accounted again for the bribes that you need to pay for other services, so the bribe money cretaes a current of unaccounted money flow from the regular accounted money flow. so what happens when there is a whole lot of accounted money flow in the economy is that it basically wastes peoples tax money indirectly as the one who takes bribe also pays for the tax.

• ways to avoid bribery in india

First thing is people need start thinking at macro economic level not at the level they are, for example, he needs to think how nice it will be if people stop taking bribes, that means every citizen in the nation will begetting free service in the hospitals, so that even though somebody is not earning a lot thinks that he can get good health care, so thats the way to create heaven in earth. To create heaven in india is wholly vested on the people in india.