SQLExec

Sqlexec  Update

  SQLExec("Update PS_SF_ACCTG_LN set GL_DISTRIB_STATUS = 'Y', PROCESS_INSTANCE = :4 where RUN_DT = :1 and SEQNUM = :2 and SF_LINE_NBR = :3", &REC.RUN_DT.Value, &REC.SEQNUM.Value, &REC.SF_LINE_NBR.Value, &Process_instance);


----------------------------------------------------------------------------------------------------------

Comma Seprated String in Poeplecode

&UserRoles = CreateArray("");
Local array of string &roles = %Roles;
Local string &rolesStr = "";
If &roles.Len >= 1 Then
   &rolesStr = "'" | &UserRoles [1] | "'";
End-If;

For &i = 2 To &roles.Len
   &rolesStr = &rolesStr | ", ";
   &UserRoles [&i] = &roles [&i];
   &rolesStr = &rolesStr | "'" | &UserRoles [&i] | "'";
End-For;





If %Language = %Language_Base Then
  
  
   FORM_ADD_VW.FORM_TYPE.SqlText = "SELECT A.FORM_TYPE, A.PORTAL_LABEL FROM PS_KS_FORM_TYPE_VW A  WHERE A.ROLEUSER IN (" | &rolesStr | ") AND A.FORM_TYPE_STATUS ='A' AND A.PORTAL_NAME='" | %Portal | "' AND A.EFFDT_FROM <= %CurrentDateIn AND (A.EFFDT_TO is null or A.EFFDT_TO >= %CurrentDateIn)";
  
  
Else
  
   FORM_ADD_VW.FORM_TYPE.SqlText = "SELECT A.FORM_TYPE, B.PORTAL_LABEL FROM PS_KS_FORM_TYPE_VW A , PS_FORM_TYPE_LG B WHERE A.ROLEUSER IN (" | &rolesStr | ") AND  A.FORM_TYPE_STATUS ='A' AND A.PORTAL_NAME='" | %Portal | "' AND A.EFFDT_FROM <= %CurrentDateIn AND (A.EFFDT_TO is null or A.EFFDT_TO >= %CurrentDateIn) AND A.FORM_TYPE=B.FORM_TYPE AND B.LANGUAGE_CD='" | %Language | "'";
  
  
End-If;

Rowset Fill Method

 Simple Fill Method 

The Rowset method Fill is used to populate a stand-alone rowset from the database.
 It take a where clause as a parameter. The underlying record is used as a select record.

Local Rowset &GetNbr = CreateRowset(Record.IOBM_PHN_NO_WRK);
&GetNbr.Fill();

   For &i = 1 To &GetNbr.ActiveRowCount
     
     
      &getPhone = &GetNbr.GetRow(&i).GetRecord(Record.IOBM_PHN_NO_WRK).GetField(Field.PHONE).Value;
      &getemplid = &GetNbr.GetRow(&i).GetRecord(Record.IOBM_PHN_NO_WRK).GetField(Field.EMPLID).Value;

End-for ;

---------------------------------------------------------------------------------------------------------------------------
 1.  Using JOIN Keyword.

Some times the criteria for selection comes from a record other than the one your selecting from.

For example, let’s say you want to select records from the JOB table, but only those with a Job Code in a certain Grade.
Basically, you need JOB Record for the data and JOBCODE_TBL record  for the criteria.


Local Record &job = CreateRecord(Record.JOB);

&job.Fill("JOIN PS_JOBCODE_TBL JC ON FILL.SETID = JC.SETID
AND FILL.JOBCODE = JC.JOBCODE WHERE JC.GRADE = :1", &paramGrade);


----------------------------------------------------------------------------------------------------------------------


2. Using Exist Keyword 



Local Rowset &rs = CreateRowset(Record.RF_INST_PROD);

&rs.Fill("WHERE RBTACCTID = :1 AND EXISTS (SELECT 1 FROM PS_RF_INST_PROD_ST WHERE SETID = FILL.SETID AND INST_PROD_ID = FILL.INST_PROD_ID AND INST_PROD_STATUS = 'INS')", &acctid);



------------------------------------------------------------------------------------------------------------------------- 

3 . Using UNION Keyword.
Local Rowset &rs = Createrowset(Record.PERSON);

&rs.Fill("WHERE 1=0 UNION SELECT EMPLID, %DateOut(BIRTHDATE),BIRTHPLACE,BIRTHCOUNTRY,BIRTHSTATE,%DateOut(DT_OF_DEATH),%DateTimeOut(LAST_CHILD_UPDDTM) FROM PS_PERSON WHERE EMPLID = :1", &emplid);

 
 

Peoplesoft Meta Variables

%DateIn


When a date literal or a date bind variable is used within the WHERE clause of SELECT or UPDATE statements,
it can be specified within the %DateIn meta-SQL variable to get the platform-specific SQL syntax for the date.


%Datein  resolves to to_date('2013-06-10','YYYY-MM-DD')   

In short it converts the date literal / bind variable into the format which the DB uses.

INSERT INTO PS_SGK_REC (EMPLID, NAME, JOIN_DT, DEPTID)
VALUES (’001′, ‘John’, %DateIn(:1), ‘EAS’);

-------------------------------------------------------------------------------------------


%DateOut  

%DateOut on the other hand converts a date column in the SELECT clause to the format that PeopleSoft uses.

SELECT EMPLID, NAME, %DateOut(JOIN_DT)
FROM PS_SGK_REC WHERE DEPTID = ‘EAS’;

%Dateout resolves to to_char(date,'YYYY-MM-DD')

Local Rowset &rs = Createrowset(Record.PERSON);

&rs.Fill("WHERE 1=0 UNION SELECT EMPLID, %DateOut(BIRTHDATE),BIRTHPLACE,BIRTHCOUNTRY,

BIRTHSTATE,%DateOut(DT_OF_DEATH),%DateTimeOut(LAST_CHILD_UPDDTM)

 FROM PS_PERSON WHERE EMPLID = :1", &emplid);

------------------------------------------------------------------------------------------------

%SELECTALL

%SelectAll uses the underlying record definition to select the fields - so it will always return all fields from the underlying record, even if that record definition changes.

Local Record &REC ;
&REC = CreateRecord(Record.SF_ACCTG_LN);


&SQL = CreateSQL("%SELECTALL(:1) WHERE GL_DISTRIB_STATUS =:2 AND  ACCOUNTING_DT <=TO_DATE( :3 ,'YYYY-MM-DD') ", &REC, "N", IBT_GL_RUN_AET.END_DATE);
While &SQL.Fetch(&REC)
  

&Currency_cd =  &REC.CURRENCY_CD.Value ;
&account = &REC.ACCOUNT.Value


End-While

Manipulating Data on a Grid: Scroll Select

 

Sometimes you want to pull information from multiple records and display in a grid on a page.
you create a view to get data from one or more records and associate with grid as Primary record .
Consider that you want to populate the grid dynamically passing parameters to the view.
Since, static views cannot accept parameters , how do you handle this kind of situation.

 Lets say we have a Grid on a page and the main record on the grid is called IBT_CMAT_STDLST_VW . Well we would first want to define a Rowset object to manipulate. The code for this would look as follows:


   Local Rowset &Level0, &Att_Tmplt;
   Local Row &Level0_Row;

  &Level0 = GetLevel0();
   &Level0_Row = &Level0(1);
 
   &LC = IBT_CMAT_WRK.IBT_UPOU_LC.Value;
   &ACTION = IBT_CMAT_WRK.IBT_ACTION.Value;
 
   &APPR_STATUS = IBT_CMAT_WRK.IBT_APPROVL_STATUS.Value;
 
   &EMPLID = IBT_CMAT_WRK.EMPLID.Value;
 
   &APPL_DT = IBT_CMAT_WRK.ACTION_DT.Value;
 
   &NAME = IBT_CMAT_WRK.NAME.Value;
 
 
 
 
   &where = "  1=1";
   If All(&LC) Then
      &where = &where | "AND  IBT_UPOU_LC  = " | Quote(&LC);
    
   End-If;
   If All(&ACTION) Then
    
      &where = &where | "AND  IBT_ACTION  = " | Quote(&ACTION);
    
   End-If;

   If All(&APPL_DT) Then
    
      &where = &where | "AND  ACTION_DT  = " | Quote(&APPL_DT);
    
   End-If;

   If All(&APPR_STATUS) Then
      &where = &where | " AND IBT_APPROVL_STATUS = " | Quote(&APPR_STATUS);
   End-If;
 
   If All(&EMPLID) Then
      &where = &where | " AND EMPLID = " | Quote(&EMPLID);
   End-If;
 
   If All(&NAME) Then
      &where = &where | " AND NAME LIKE " | Quote(&NAME);
   End-If;
 
rem    MessageBox(0, "", 0, 0, "" | &where);
 
   

  

Next we will want to instantiate the object. Instantiate just means to represent by an instance. If you notice, I am not creating a new Rowset. I am getting the Rowset from the current grid called IBT_CMAT_STDLST_VW , using the GetRowSet function. And, I am defining the Grid object as a Rowset.
 
 
   &Att_Tmplt = &Level0_Row.GetRowset(Scroll.IBT_CMAT_STDLST_VW);



Once that is complete, I want to remove all rows of data from the Grid. To do that I simply use the Flush method.


   &Att_Tmplt.Flush();

Now lets fill the grid with new data that we really want.  


   &Att_Tmplt.Select(Record.IBT_CMAT_STDLST_VW, "WHERE " | &where | "");
 
 




 -----------------------------------------------------------------------------------------------------------------



    Local Rowset &Level0, &Att_Tmplt;
    Local Row &Level0_Row;
    Local Rowset &Level0, &Att_Tmplt;
   Local Row &Level0_Row;

  &Level0 = GetLevel0();
   &Level0_Row = &Level0(1);

   &LC = IBT_CMAT_WRK.IBT_UPOU_LC.Value;
   &ACTION = IBT_CMAT_WRK.IBT_ACTION.Value;
 
   &APPR_STATUS = IBT_CMAT_WRK.IBT_APPROVL_STATUS.Value;
 
   &EMPLID = IBT_CMAT_WRK.EMPLID.Value;
 
   &APPL_DT = IBT_CMAT_WRK.ACTION_DT.Value;
 
   &NAME = IBT_CMAT_WRK.NAME.Value;
 
 
 
 
   &where = "  1=1";
   If All(&LC) Then
      &where = &where | "AND  IBT_UPOU_LC  = " | Quote(&LC);
    
   End-If;
   If All(&ACTION) Then
    
      &where = &where | "AND  IBT_ACTION  = " | Quote(&ACTION);
    
   End-If;
   If All(&APPL_DT) Then
    
      &where = &where | "AND  ACTION_DT  = " | Quote(&APPL_DT);
    
   End-If;
   If All(&APPR_STATUS) Then
      &where = &where | " AND IBT_APPROVL_STATUS = " | Quote(&APPR_STATUS);
   End-If;
 
   If All(&EMPLID) Then
      &where = &where | " AND EMPLID = " | Quote(&EMPLID);
   End-If;
 
   If All(&NAME) Then
      &where = &where | " AND NAME LIKE " | Quote(&NAME);
   End-If;

 

 
 
 
   &Att_Tmplt = &Level0_Row.GetRowset(Scroll.IBT_CMAT_STDLST);
   &Att_Tmplt.Flush();
 
   &Att_Tmplt.Select(Record.IBT_CMAT_STDLST, "WHERE " | &where | "");
 
 

Read Data from Grid using Peoplecode

&rsLvl0 = GetLevel0();

/ Get Level 1 Rowset /
&rsLvl1 = GetLevel0()(1).GetRowset(Scroll.IOBM_APTST_SMS1);

For &i = 1 To &rsLvl1.ActiveRowCount
  
   &getPhone = &rsLvl1.GetRow(&i).GetRecord(Record.IOBM_APTST_SMS1).GetField(Field.IOBM_A_MOBILE_NO).VALUE;
 
 &FIRST_NAME = &rsLvl1.GetRow(&i).GetRecord(Record.IOBM_APTST_SMS1).GetField(Field.FIRST_NAME).VALUE;

  &LAST_NAME = &rsLvl1.GetRow(&i).GetRecord(Record.IOBM_APTST_SMS1).GetField(Field.LAST_NAME).VALUE;

   &MIDDLE_NAME = &rsLvl1.GetRow(&i).GetRecord(Record.IOBM_APTST_SMS1).GetField(Field.MIDDLE_NAME).VALUE;

End-for;

Load Data in a Grid using Peoplecode

Local SQL &GRD_SQL;
Local Rowset &GRD_RS;

&GRD_RS = GetLevel0()(1).GetRowset(Scroll.IOBM_APTST_SMS1);

&APT_ARR = CreateArrayAny("");

&strm = IOBM_SMS_WRK.STRM.Value;
If All(&strm) Then

   &GRD_SQL = CreateSQL("select distinct IOBM_REF_NO , ACAD_CAREER , ACAD_PROG , ADMIT_TERM ,IOBM_A_MOBILE_NO , FIRST_NAME ,MIDDLE_NAME ,LAST_NAME  from PS_IOBM_APT_SMS_VW  WHERE ADMIT_TERM =:1", &strm);
  
  
  
   &GRD_RS.Flush();
  
   While &GRD_SQL.Fetch(&APT_ARR)
     
      &GRD_RS(1).IOBM_APTST_SMS1.IOBM_REF_NO.Value = &APT_ARR [1];
     
      &GRD_RS(1).IOBM_APTST_SMS1.ACAD_CAREER.Value = &APT_ARR [2];
     
      &GRD_RS(1).IOBM_APTST_SMS1.ACAD_PROG.Value = &APT_ARR [3];
      &GRD_RS(1).IOBM_APTST_SMS1.ADMIT_TERM.Value = &APT_ARR [4];
      &GRD_RS(1).IOBM_APTST_SMS1.IOBM_A_MOBILE_NO.Value = &APT_ARR [5];
      &GRD_RS(1).IOBM_APTST_SMS1.FIRST_NAME.Value = &APT_ARR [6];
     
      &GRD_RS(1).IOBM_APTST_SMS1.MIDDLE_NAME.Value = &APT_ARR [7];
      &GRD_RS(1).IOBM_APTST_SMS1.LAST_NAME.Value = &APT_ARR [8];
     
      &GRD_RS.InsertRow(0);
   End-While;
   &GRD_RS.DeleteRow(1);
   &GRD_SQL.Close();
End-If;