Getting the Excel output using peoplecode in peoplesoft(Without HTML)



Local Record &Rec;
Local Rowset &rowSet;
Local File &file1, &file2;
Local SQL &SQL1, &SQL2, &SQL3;
Local string &PresidentsHornor;


&CUR_DATE = DateTimeToLocalizedString(%Datetime, "MMddyyyy");
&OPRID = %UserId;
&PI = XXX_PRSDNT_AET.PROCESS_INSTANCE.Value;

/* Output File Processing */
&FILE_NAME = "President_List_Processing_" | &CUR_DATE | "_" | &PI | "_Success" | ".xls";
&ERROR_FILE = "President_List_Processing_" | &CUR_DATE | "_" | &PI | "_Error" | ".xls";

&file1 = GetFile(&FILE_NAME, "W", %FilePath_Relative);
&file2 = GetFile(&ERROR_FILE, "W", %FilePath_Relative);
&PresidentsHornor = "President’s Honors";

If &file1.IsOpen Then
   &rowSet = CreateRowset(Record.XXX_PRSDNT_TBL);
   &NUM_READ = &rowSet.Fill("WHERE PROCESS_INSTANCE= :1", XXX_PRSDNT_AET.PROCESS_INSTANCE.Value);
   &file1.WriteLine("EMPLID" | Char(9) | "HOME CAMPUS" | Char(9) | "DT_RECVD" | Char(9) | "STRM" | Char(9) | "AWARD CODE" | Char(9) | "DESCRFORMAL");

   &SQL1 = CreateSQL("SELECT EMPLID,CAMPUS,DATE_RECEIVED,XXX_RUNTERM,AWARD_CODE FROM %Table(XXX_PRSDNT_TBL) WHERE PROCESS_INSTANCE = :1 and TO_CHAR(DESCRLONG) = :2", &PI, "UPD");
   While &SQL1.Fetch(&Emplid, &Campus, &DtRecvd, &Runterm, &AwardCode)
      &file1.WriteLine(&Emplid | Char(9) | &Campus | Char(9) | &DtRecvd | Char(9) | &Runterm | Char(9) | "PRES" | Char(9) | &PresidentsHornor);
   End-While;
End-If;



If &file2.IsOpen Then
   &rowSet = CreateRowset(Record.XXX_PRSDNT_TBL);
   &NUM_READ = &rowSet.Fill("WHERE PROCESS_INSTANCE= :1", XXX_PRSDNT_AET.PROCESS_INSTANCE.Value);
   &file2.WriteLine("EMPLID" | Char(9) | "HOME CAMPUS" | Char(9) | "DT_RECVD" | Char(9) | "STRM" | Char(9) | "AWARD CODE" | Char(9) | "DESCRFORMAL" | Char(9) | "Error Message");

   &SQL2 = CreateSQL("SELECT EMPLID,CAMPUS,DATE_RECEIVED,XXX_RUNTERM,AWARD_CODE,DESCRLONG FROM %Table(XXX_PRSDNT_TBL) WHERE PROCESS_INSTANCE = :1 and TO_CHAR(DESCRLONG) <> %TEXTIN(:2) and TO_CHAR(DESCRLONG) <> ' '", &PI, "UPD");
   While &SQL2.Fetch(&Emplid, &Campus, &DtRecvd, &Runterm, &AwardCode, &ErrorMesge)
      &file2.WriteLine(&Emplid | Char(9) | &Campus | Char(9) | &DtRecvd | Char(9) | &Runterm | Char(9) | "PRES" | Char(9) | &PresidentsHornor | Char(9) | &ErrorMesge);
   End-While;
End-If;


&file1.Close();

&file2.Close();

Comments

Popular posts from this blog

BI Publisher: If Condition with sub-string in rtf template