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'