Running PowerShell Scripts from PeopleCode Using a Stored Procedure

 

Step1:  Execute the following 

GRANT CREATE JOB TO SYSADM;

GRANT MANAGE SCHEDULER TO SYSADM;

GRANT CREATE EXTERNAL JOB TO SYSADM;

Step2: create a delegated Active Directory service account

 BEGIN

  DBMS_SCHEDULER.CREATE_CREDENTIAL (

    credential_name => 'AD_SVC_CRED',

    username        => 'StudentManagement',

    password        => 'Mk#kfWi$5jDncD23$5kf'

  );

END;

credential_name => 'AD_SVC_CRED'

Step 3: Create the PL/SQL procedure to execute the PowerShell Script providing the required parameters

CREATE OR REPLACE PROCEDURE ad_enable_student_if_clear (

    p_identity IN VARCHAR2,   -- e.g., 'TESTSTD09'

    p_enable   IN VARCHAR2    -- '0' = disable, '1' = enable

) AS

  v_job VARCHAR2(64);

BEGIN

  -- Generate unique job name for tracking

  v_job := 'AD_ENABLE_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF3');

  -- Create the executable DBMS_SCHEDULER job

  DBMS_SCHEDULER.CREATE_JOB(

    job_name            => v_job,

    job_type            => 'EXECUTABLE',

    job_action          => 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe',

    number_of_arguments => 7,           -- 7 arguments now

    enabled             => FALSE,

    auto_drop           => TRUE,

    comments            => 'Enable/Disable AD account for student'

  );

  -- PowerShell parameters

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 1, '-NoProfile');

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 2, '-ExecutionPolicy');

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 3, 'Bypass');

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 4, '-File');

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 5, 'C:\account_lock\Student-Status1.ps1');

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 6, p_identity);  -- Username

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 7, p_enable);    -- 0 or 1

  -- Run job using AD credential

  DBMS_SCHEDULER.SET_ATTRIBUTE(v_job, 'credential_name', 'AD_SVC_CRED');

  -- Enable and start the job

  DBMS_SCHEDULER.ENABLE(v_job);

  -- Optional: log to DBMS output for confirmation

  DBMS_OUTPUT.PUT_LINE('Job ' || v_job || ' created and started successfully for ' ||

                       p_identity || ' with status flag ' || p_enable);

END;

/

Step4: Execute the procedure 

BEGIN

  ad_enable_student_if_clear('TESTSTD10', 0);  -- 1 = Enable, 0 = Disable

END;

Step5: Execute the below query to get the Job Status  ( Success , Failed )

SELECT job_name, status, additional_info

FROM dba_scheduler_job_run_details

WHERE job_name LIKE 'AD_ENABLE_%'

ORDER BY actual_start_date DESC;

Step 6 : Execute the procedure from Peoplecode 

 SQLExec("BEGIN ad_enable_student_if_clear(:1, :2); END;", &email_id, &Islock);

Following is PowerShell Script to Enable / Disable Accounts from Active Directory 

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

[CmdletBinding()]

param(

    [Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$true)]

    [string]$Username,

    [Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$true)]

    [string]$Action,

    [switch]$TriggerSync,

    [string]$LogPath = "E:\account_lock\Activitylog.log",

    [string]$Server

)

# ---------------- Robust logging (pre-create + mutex) ----------------

function Initialize-LogFile {

    try {

        $dir = Split-Path -Path $LogPath -Parent

        if ($dir -and -not (Test-Path -LiteralPath $dir)) {

            New-Item -ItemType Directory -Path $dir -Force | Out-Null

        }

        if (-not (Test-Path -LiteralPath $LogPath)) {

            New-Item -ItemType File -Path $LogPath -Force | Out-Null

        }

    } catch {

        Write-Warning "Init log failed: $($_.Exception.Message)"

    }

}

# Correlate lines from the same script instance (optional but helpful)

$script:RunId = ([guid]::NewGuid().ToString('N')).Substring(0,8)

function Log-Message {

    param([string]$message)

    $timestamp  = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

    $line       = "$timestamp [$PID][$script:RunId] - $message"

    # Named mutex per-log-file to serialize writers across processes/sessions

    $mutexName = "Global\MBSC-SetAdUserStatus-" + ($LogPath -replace '[\\/:*?""<>|]', '_')

    $mutex = New-Object System.Threading.Mutex($false, $mutexName)

    $maxAttempts = 10

    for ($i=1; $i -le $maxAttempts; $i++) {

        $gotLock = $false

        try {

            $gotLock = $mutex.WaitOne(2000)   # wait up to 2s for the lock

            if (-not $gotLock) { throw "Timeout waiting for log mutex." }

            # Append using FileStream + StreamWriter (UTF8 no BOM), allow readers

            $fs = [System.IO.File]::Open($LogPath,

                                         [System.IO.FileMode]::Append,

                                         [System.IO.FileAccess]::Write,

                                         [System.IO.FileShare]::ReadWrite)

            try {

                $sw = New-Object System.IO.StreamWriter($fs, [System.Text.UTF8Encoding]::new($false))

                $sw.AutoFlush = $true

                $sw.WriteLine($line)

                $sw.Flush()

            } finally {

                $sw.Dispose()

                $fs.Dispose()

            }

            return

        } catch {

            if ($i -eq $maxAttempts) {

                Write-Warning "Log write failed after $maxAttempts attempts to '$LogPath': $($_.Exception.Message)"

                return

            }

            Start-Sleep -Milliseconds ([int][math]::Min(1600, 100 * [math]::Pow(2, $i-1)))

        } finally {

            if ($gotLock) { $mutex.ReleaseMutex() | Out-Null }

        }

    }

}

# ---------------------------------------------------------------------

# Ensure the log file exists before any writes

Initialize-LogFile

# Module load

try {

    if (-not (Get-Module -ListAvailable -Name ActiveDirectory)) {

        throw "ActiveDirectory module not found."

    }

    Import-Module ActiveDirectory -ErrorAction Stop

} catch {

    Log-Message "Failed to import ActiveDirectory module. $($_.Exception.Message)"

    Write-Error "Failed to import ActiveDirectory module. $_"

    exit 1

}

function Resolve-Action {

    param([Parameter(Mandatory)][string]$RawAction)

    switch -Regex ($RawAction.Trim().ToLower()) {

        '^(1|enable|enabled|true|on)$'      { return 'Enable' }

        '^(0|disable|disabled|false|off)$'  { return 'Disable' }

        default { return $null }

    }

}

function Set-UserAccountStatus {

    [CmdletBinding()]

    param(

        [Parameter(Mandatory)][string]$User,

        [Parameter(Mandatory)][ValidateSet('Enable','Disable')]$Desired,

        [string]$Server

    )

    $serverSplat = @{}

    if ($Server) { $serverSplat.Server = $Server }

    try {

        $adUser = Get-ADUser -Identity $User -Properties Enabled -ErrorAction Stop @serverSplat

        if ($Desired -eq 'Enable' -and $adUser.Enabled) {

            Log-Message "No change: $User already enabled."

            return

        }

        if ($Desired -eq 'Disable' -and -not $adUser.Enabled) {

            Log-Message "No change: $User already disabled."

            return

        }

        if ($Desired -eq 'Enable') {

            Enable-ADAccount -Identity $adUser.SamAccountName -ErrorAction Stop @serverSplat

            Log-Message "User $($adUser.SamAccountName) enabled."

        } else {

            Disable-ADAccount -Identity $adUser.SamAccountName -ErrorAction Stop @serverSplat

            Log-Message "User $($adUser.SamAccountName) disabled."

        }

    } catch {

        Log-Message "Failed to change status for user '$User'. Error: $($_.Exception.Message)"

        throw

    }

}

# Input validation (for param-based usage)

if ([string]::IsNullOrWhiteSpace($Username) -or [string]::IsNullOrWhiteSpace($Action)) {

    Write-Error "Username and Action are required."

    exit 1

}

$resolved = Resolve-Action -RawAction $Action

if (-not $resolved) {

    Log-Message "Invalid action '$Action' for user '$Username'. Use 1/enable/on/true or 0/disable/off/false."

    Write-Error "Invalid Action: $Action"

    exit 1

}

$processedAny = $false

try {

    Set-UserAccountStatus -User $Username -Desired $resolved -Server $Server

    $processedAny = $true

} catch {

    Write-Error $_

}

if ($TriggerSync -and $processedAny) {

    try {

        if (Get-Command -Name Start-ADSyncSyncCycle -ErrorAction SilentlyContinue) {

            Start-ADSyncSyncCycle -PolicyType Delta

            Log-Message "Started AAD Connect delta sync."

        } else {

            Log-Message "Start-ADSyncSyncCycle not found. Skipped sync."

        }

    } catch {

        Log-Message "Failed to start AAD Connect delta sync. Error: $($_.Exception.Message)"

        Write-Error "Failed to start AAD Connect delta sync. See log for details."

    }

}

Code to Matriculate students

 

/*Now add a matriculation row*/

Global File &LogFile;

Local string &sqlquery, &sqlquery1;

Function INSERT_ADM_APPL_PROG(&ADMIT_TERM As string, &ACAD_CAREER As string, &EMPLID As string, &INSTITUTION As string, &oprid As string)

   &sqlquery = "INSERT INTO PS_ADM_APPL_PROG (";

   &sqlquery = &sqlquery | "    EMPLID, ";

   &sqlquery = &sqlquery | "    ACAD_CAREER, ";

   &sqlquery = &sqlquery | "    STDNT_CAR_NBR, ";

   &sqlquery = &sqlquery | "    ADM_APPL_NBR, ";

   &sqlquery = &sqlquery | "    APPL_PROG_NBR, ";

   &sqlquery = &sqlquery | "    EFFDT, ";

   &sqlquery = &sqlquery | "    EFFSEQ, ";

   &sqlquery = &sqlquery | "    INSTITUTION, ";

   &sqlquery = &sqlquery | "    ACAD_PROG, ";

   &sqlquery = &sqlquery | "    PROG_STATUS, ";

   &sqlquery = &sqlquery | "    PROG_ACTION, ";

   &sqlquery = &sqlquery | "    ACTION_DT, ";

   &sqlquery = &sqlquery | "    PROG_REASON, ";

   &sqlquery = &sqlquery | "    ADMIT_TERM, ";

   &sqlquery = &sqlquery | "    EXP_GRAD_TERM, ";

   &sqlquery = &sqlquery | "    REQ_TERM, ";

   &sqlquery = &sqlquery | "    ACAD_LOAD_APPR, ";

   &sqlquery = &sqlquery | "    CAMPUS, ";

   &sqlquery = &sqlquery | "    ACAD_PROG_DUAL, ";

   &sqlquery = &sqlquery | "    JOINT_PROG_APPR, ";

   &sqlquery = &sqlquery | "    SSR_RS_CANDIT_NBR, ";

   &sqlquery = &sqlquery | "    SSR_APT_INSTANCE, ";

   &sqlquery = &sqlquery | "    SSR_YR_OF_PROG, ";

   &sqlquery = &sqlquery | "    SSR_SHIFT, ";

   &sqlquery = &sqlquery | "    SSR_COHORT_ID, ";

   &sqlquery = &sqlquery | "    SCC_ROW_ADD_OPRID, ";

   &sqlquery = &sqlquery | "    SCC_ROW_ADD_DTTM, ";

   &sqlquery = &sqlquery | "    SCC_ROW_UPD_OPRID, ";

   &sqlquery = &sqlquery | "    SCC_ROW_UPD_DTTM ";

   &sqlquery = &sqlquery | ") ";

   &sqlquery = &sqlquery | "SELECT ";

   &sqlquery = &sqlquery | "    EMPLID, ";

   &sqlquery = &sqlquery | "    ACAD_CAREER, ";

   &sqlquery = &sqlquery | "    STDNT_CAR_NBR, ";

   &sqlquery = &sqlquery | "    ADM_APPL_NBR, ";

   &sqlquery = &sqlquery | "    APPL_PROG_NBR, ";

   &sqlquery = &sqlquery | "    EFFDT + 1, ";

   &sqlquery = &sqlquery | "    EFFSEQ + 1, ";

   &sqlquery = &sqlquery | "    INSTITUTION, ";

   &sqlquery = &sqlquery | "    ACAD_PROG, ";

   &sqlquery = &sqlquery | "    'AC', ";

   &sqlquery = &sqlquery | "    'MATR', ";

   &sqlquery = &sqlquery | "    ACTION_DT, ";

   &sqlquery = &sqlquery | "    'MAT', ";

   &sqlquery = &sqlquery | "    ADMIT_TERM, ";

   &sqlquery = &sqlquery | "    EXP_GRAD_TERM, ";

   &sqlquery = &sqlquery | "    REQ_TERM, ";

   &sqlquery = &sqlquery | "    ACAD_LOAD_APPR, ";

   &sqlquery = &sqlquery | "    CAMPUS, ";

   &sqlquery = &sqlquery | "    ACAD_PROG_DUAL, ";

   &sqlquery = &sqlquery | "    JOINT_PROG_APPR, ";

   &sqlquery = &sqlquery | "    ' ', ";

   &sqlquery = &sqlquery | "    0, ";

   &sqlquery = &sqlquery | "    '01', ";

   &sqlquery = &sqlquery | "    ' ', ";

   &sqlquery = &sqlquery | "    ' ', ";

   &sqlquery = &sqlquery | "    '" | &oprid | "' , ";

   &sqlquery = &sqlquery | "    SYSDATE, ";

   &sqlquery = &sqlquery | "    '" | &oprid | "' , ";

   &sqlquery = &sqlquery | "    SYSDATE ";

   &sqlquery = &sqlquery | "FROM PS_ADM_APPL_PROG prog ";

   &sqlquery = &sqlquery | "WHERE prog.INSTITUTION = '" | &INSTITUTION | "' ";

   &sqlquery = &sqlquery | "AND prog.EMPLID = '" | &EMPLID | "' ";

   &sqlquery = &sqlquery | "AND prog.ACAD_CAREER = '" | &ACAD_CAREER | "' ";

   rem &sqlquery = &sqlquery | "AND prog.ACAD_PROG = '" | &ACAD_PROG | "' ";

   &sqlquery = &sqlquery | "AND prog.ADMIT_TERM = '" | &ADMIT_TERM | "'";

   SQLExec(&sqlquery);

   SQLExec("COMMIT");

End-Function;

Function INSERT_ADM_APPL_PLAN(&ADMIT_TERM As string, &PROG_ACTION As string, &ACAD_CAREER As string, &EMPLID As string, &INSTITUTION As string, &oprid As string)

   

   &sqlquery1 = "INSERT INTO PS_ADM_APPL_PLAN (";

   &sqlquery1 = &sqlquery1 | "    EMPLID, ";

   &sqlquery1 = &sqlquery1 | "    ACAD_CAREER, ";

   &sqlquery1 = &sqlquery1 | "    STDNT_CAR_NBR, ";

   &sqlquery1 = &sqlquery1 | "    ADM_APPL_NBR, ";

   &sqlquery1 = &sqlquery1 | "    APPL_PROG_NBR, ";

   &sqlquery1 = &sqlquery1 | "    EFFDT, ";

   &sqlquery1 = &sqlquery1 | "    EFFSEQ, ";

   &sqlquery1 = &sqlquery1 | "    ACAD_PLAN, ";

   &sqlquery1 = &sqlquery1 | "    DECLARE_DT, ";

   &sqlquery1 = &sqlquery1 | "    PLAN_SEQUENCE, ";

   &sqlquery1 = &sqlquery1 | "    REQ_TERM ,";

   &sqlquery1 = &sqlquery1 | "    SSR_APT_INSTANCE, ";

   &sqlquery1 = &sqlquery1 | "    SSR_YR_OF_PROG, ";

   &sqlquery1 = &sqlquery1 | "    SCC_ROW_ADD_OPRID, ";

   &sqlquery1 = &sqlquery1 | "    SCC_ROW_ADD_DTTM, ";

   &sqlquery1 = &sqlquery1 | "    SCC_ROW_UPD_OPRID, ";

   &sqlquery1 = &sqlquery1 | "    SCC_ROW_UPD_DTTM ";

   

   &sqlquery1 = &sqlquery1 | ") SELECT ";

   &sqlquery1 = &sqlquery1 | "    EMPLID, ";

   &sqlquery1 = &sqlquery1 | "    ACAD_CAREER, ";

   &sqlquery1 = &sqlquery1 | "    STDNT_CAR_NBR, ";

   &sqlquery1 = &sqlquery1 | "    ADM_APPL_NBR, ";

   &sqlquery1 = &sqlquery1 | "    APPL_PROG_NBR, ";

   &sqlquery1 = &sqlquery1 | "    EFFDT + 1, ";

   &sqlquery1 = &sqlquery1 | "    EFFSEQ + 1, ";

   REM &sqlquery1 = &sqlquery1 | "    'UNDECLR', ";

   &sqlquery1 = &sqlquery1 | "    ACAD_PROG, ";

   

   &sqlquery1 = &sqlquery1 | "    EFFDT + 1, ";

   &sqlquery1 = &sqlquery1 | "    1, ";

   &sqlquery1 = &sqlquery1 | "    ADMIT_TERM ,";

   &sqlquery1 = &sqlquery1 | "    SSR_APT_INSTANCE, ";

   &sqlquery1 = &sqlquery1 | "    SSR_YR_OF_PROG, ";

   &sqlquery1 = &sqlquery1 | "    SCC_ROW_ADD_OPRID, ";

   &sqlquery1 = &sqlquery1 | "    SYSDATE, ";

   &sqlquery1 = &sqlquery1 | "    SCC_ROW_UPD_OPRID, ";

   &sqlquery1 = &sqlquery1 | "    SYSDATE ";

   

   &sqlquery1 = &sqlquery1 | "FROM PS_ADM_APPL_PROG prog ";

   &sqlquery1 = &sqlquery1 | "WHERE prog.INSTITUTION = '" | &INSTITUTION | "' ";

   &sqlquery1 = &sqlquery1 | "AND prog.EMPLID = '" | &EMPLID | "' ";

   &sqlquery1 = &sqlquery1 | "AND prog.ACAD_CAREER = '" | &ACAD_CAREER | "' ";

   rem &sqlquery1 = &sqlquery1 | "AND prog.ACAD_PROG = '" | &ACAD_PROG | "' ";

   &sqlquery1 = &sqlquery1 | "AND prog.PROG_ACTION = '" | &PROG_ACTION | "' ";

   &sqlquery1 = &sqlquery1 | "AND prog.ADMIT_TERM = '" | &ADMIT_TERM | "'";

   

   

   

   SQLExec(&sqlquery1);

   SQLExec("COMMIT");

End-Function;

/*

&INSTITUTION = "MAU";

&ACAD_CAREER = "UGRD";

rem &ADMIT_TERM = "2520";

rem &ACAD_PROG = "NURS";

rem &EMPLID = "00169";

&app_nbr = "00025125";

&plan = "UNDECLR";

&PROG_ACTION = "APPL";

&oprid = %OperatorId;

*/

&oprid = %OperatorId;

&APPLIED_TERM = ITS_POSTPRC_AET.STRM.Value;

&std_list_sql = CreateSQL(SQL.ITS_GET_MATR_APPLICANT_DATA, &APPLIED_TERM);

While &std_list_sql.Fetch(&INSTITUTION, &EMPLID, &ADMIT_TERM, &app_nbr, &APPL_PROG_NBR, &ACAD_CAREER, &PROG_ACTION)

   

   

   

   

   

   

   

   INSERT_ADM_APPL_PROG(&ADMIT_TERM, &ACAD_CAREER, &EMPLID, &INSTITUTION, &oprid);

   INSERT_ADM_APPL_PLAN(&ADMIT_TERM, &PROG_ACTION, &ACAD_CAREER, &EMPLID, &INSTITUTION, &oprid);

   SQLExec("UPDATE PS_ADM_APP_CAR_SEQ SET CREATE_PROG_STATUS = 'R' Where EMPLID = :1 AND ACAD_CAREER = :2 AND       STDNT_CAR_NBR = :3 AND       ADM_APPL_NBR = :4 ", &EMPLID, &ACAD_CAREER, 0, &app_nbr);

   /* Ensure no premature commit before COBOL execution */

   CommitWork();

   

   /* Call COBOL Program */

   &RETCODE = 0;

   rem RemoteCall("PSRCCBL", "PSCOBOLPROG", "ADPCPRRC", "EMPLID", &EMPLID, "ACAD-CAREER", "UGRD", "STDNT-CAR-NBR", 0, "ADM-APPL-NBR", &app_nbr, "APPL-PROG-NBR", 0, "PROG-NBR-PASSED", "Y", "RC", &RETCODE);

   RemoteCall("PSRCCBL", "PSCOBOLPROG", "ADPCPRRC", "EMPLID", &EMPLID, "ACAD-CAREER", &ACAD_CAREER, "STDNT-CAR-NBR", 0, "ADM-APPL-NBR", &app_nbr, "APPL-PROG-NBR", &APPL_PROG_NBR, "PROG-NBR-PASSED", "Y", "STDNT-CAR-NBR-SR", 0, "RC", &RETCODE);

   

   /* Allow COBOL to handle commits internally */

   If &RETCODE = 0 Then

      

      SQLExec("Update PS_ITS_POSTING_LOG SET ITS_MATR_STATUS='P' WHERE EMPLID =:1", &EMPLID);

      

      

      /* COBOL executed successfully */

      CommitWork();

   Else

      /* Log error */

      &LogFile.WriteLine("The Application Activation Process did not complete for application: Error " | &RETCODE);

      

   End-If;

   

End-While;

Add Student Name using CI in Peoplecode

 

Component Name : NAMES_OTHER 

Global string &FileName, &APPLIED_TERM;

Global string &PROCESS_INSTANCE;

Global string &RUN_CNTL_ID;

Global string &Type;

Local SQL &StudentList;

Local string &SQLquery;

Global ApiObject &oSession, &oNamesCi;

Global File &LogFile;

Function errorHandler()

   Local ApiObject &oPSMessageCollection, &oPSMessage;

   Local number &i;

   Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;

   

   &oPSMessageCollection = &oSession.PSMessages;

   For &i = 1 To &oPSMessageCollection.Count

      &oPSMessage = &oPSMessageCollection.Item(&i);

      &sErrMsgSetNum = &oPSMessage.MessageSetNumber;

      &sErrMsgNum = &oPSMessage.MessageNumber;

      &sErrMsgText = &oPSMessage.Text;

      MessageBox(0, "", 0, 0, (&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText));

   End-For;

   rem ***** Delete the Messages from the collection *****;

   &oPSMessageCollection.DeleteAll();

End-Function;

&APPLIED_TERM = ITS_POSTPRC_AET.STRM.Value;

&StudentList = CreateSQL(SQL.ITS_GET_ARA_NAMES_DATA, &APPLIED_TERM);

While &StudentList.Fetch(&EMPLID, &FIRST_NAME_ARB, &MIDDLE_NAME_ARB, &SECOND_LAST_NM_ARB, &LAST_NAME_ARB)

   

   try

      rem ***** Get current PeopleSoft Session *****;

      &oSession = %Session;

      

      rem ***** Get the Component Interface for Each Student *****;

      &oNamesCi = &oSession.GetCompIntfc(CompIntfc.NAMES_CI);

      If &oNamesCi = Null Then

         errorHandler();

         throw CreateException(0, 0, "GetCompIntfc failed for EMPLID: " | &EMPLID);

      End-If;

      

      rem ***** Set Component Interface Properties *****;

      &oNamesCi.InteractiveMode = True;

      &oNamesCi.GetHistoryItems = True;

      &oNamesCi.EditHistoryItems = True;

      

      rem ***** Set CI Keys *****;

      &oNamesCi.EMPLID = &EMPLID;

      

      rem ***** Execute Get *****;

      If Not &oNamesCi.Get() Then

         errorHandler();

         throw CreateException(0, 0, "Get failed for EMPLID: " | &EMPLID);

      End-If;

      

      rem ***** Process NAME_TYPE_VW Collection *****;

      &oNameTypeVwCollection = &oNamesCi.NAME_TYPE_VW;

      &oNameTypeVw = &oNameTypeVwCollection.Item(1); 

      

      rem ***** Process NAMES Collection *****;

      &oNamesCollection = &oNameTypeVw.NAMES;

      &oNames = &oNamesCollection.InsertItem(1);

      

      &oNames.NAME_TYPE = "OTH";

      &oNames.EFFDT = %Date;

      &oNames.EFF_STATUS_0 = "A";

      &oNames.COUNTRY_NM_FORMAT = "ARA";

      &oNames.LAST_NAME = &LAST_NAME_ARB;

      &oNames.FIRST_NAME = &FIRST_NAME_ARB;

      &oNames.MIDDLE_NAME = &MIDDLE_NAME_ARB;

      &oNames.SECOND_LAST_NAME = &SECOND_LAST_NM_ARB;

      

      rem ***** Execute Save *****;

      If Not &oNamesCi.Save() Then

         errorHandler();

         throw CreateException(0, 0, "Save failed for EMPLID: " | &EMPLID);

      Else

         &LogFile.WriteLine("Arabic name has been updated successfully for student " | &EMPLID);

         SQLExec("Update PS_ITS_POSTING_LOG SET ITS_ARA_NAMES='P' WHERE EMPLID =:1", &EMPLID);

         SQLExec("COMMIT");

         

      End-If;

      

      rem ***** Execute Cancel *****;

      If Not &oNamesCi.Cancel() Then

         errorHandler();

         throw CreateException(0, 0, "Cancel failed for EMPLID: " | &EMPLID);

      End-If;

      

      rem ***** Release Component Interface *****;

      &oNamesCi = Null;

      REM   End-If;

   catch Exception &ex

      rem Log the exception for debugging;

      MessageBox(0, "", 0, 0, "Error for EMPLID: " | &EMPLID | " - " | &ex.ToString());

      &LogFile.WriteLine("Error updating Arabic name for student " | &EMPLID | ": " | &ex.ToString());

   end-try;

   

End-While;

Assig Student groups to student using CI in Peoplecode

 

Local File &fileLog;

Local ApiObject &oSession, &oUodaStdntGroupsPersCi;

Local ApiObject &oPersnlFerpaVwCollection, &oPersnlFerpaVw;

Local ApiObject &oSrvcIndSelVwCollection, &oSrvcIndSelVw;

Local ApiObject &oStdntGrpsCollection, &oStdntGrps;

Local ApiObject &oStdntGrpsHistCollection, &oStdntGrpsHist;

Function errorHandler()

   Local ApiObject &oPSMessageCollection, &oPSMessage;

   Local number &i;

   Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;

   

   &oPSMessageCollection = &oSession.PSMessages;

   For &i = 1 To &oPSMessageCollection.Count

      &oPSMessage = &oPSMessageCollection.Item(&i);

      &sErrMsgSetNum = &oPSMessage.MessageSetNumber;

      &sErrMsgNum = &oPSMessage.MessageNumber;

      &sErrMsgText = &oPSMessage.Text;

      rem &fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);

   End-For;

   rem ***** Delete the Messages from the collection *****;

   &oPSMessageCollection.DeleteAll();

End-Function;

try

   &oSession = %Session;

   

   &oSession.PSMessagesMode = 1;

   

   rem ***** Get the Component Interface *****;

   &oUodaStdntGroupsPersCi = &oSession.GetCompIntfc(CompIntfc.X_STDNT_GROUPS_PERS_CI);

   If &oUodaStdntGroupsPersCi = Null Then

      errorHandler();

      throw CreateException(0, 0, "GetCompIntfc failed");

   End-If;

   

   rem ***** Set the Component Interface Mode *****;

   &oUodaStdntGroupsPersCi.InteractiveMode = False;

   &oUodaStdntGroupsPersCi.GetHistoryItems = True;

   &oUodaStdntGroupsPersCi.EditHistoryItems = False;

   

   rem ***** Set Component Interface Get/Create Keys *****;

   &oUodaStdntGroupsPersCi.EMPLID = "2250030300";

   

   rem ***** Execute Get *****;

   If Not &oUodaStdntGroupsPersCi.Get() Then

      rem ***** No rows exist for the specified keys.*****;

      errorHandler();

      throw CreateException(0, 0, "Get failed");

   End-If;

   

   

   rem ***** Begin: Get/Set Component Interface Properties *****;

   

   

   

   rem ***** Set/Get STDNT_GRPS Collection Field Properties -- Parent: PS_ROOT Collection *****;

   &oStdntGrpsCollection = &oUodaStdntGroupsPersCi.STDNT_GRPS;

   Local integer &i121;

   rem For &i121 = 1 To &oStdntGrpsCollection.Count;

   &oStdntGrps = &oStdntGrpsCollection.insertItem(1);

   

   &oStdntGrps.INSTITUTION = "UABC";

   &oStdntGrps.STDNT_GROUP = "W001";

   

   rem ***** Set STDNT_GRPS_HIST Collection Field Properties -- Parent: STDNT_GRPS Collection *****;

   &oStdntGrpsHistCollection = &oStdntGrps.STDNT_GRPS_HIST;

   Local integer &i224;

   rem  For &i224 = 1 To &oStdntGrpsHistCollection.Count;

   &oStdntGrpsHist = &oStdntGrpsHistCollection.Item(1);

   

   &oStdntGrpsHist.EFFDT = %Date;

   &oStdntGrpsHist.EFF_STATUS = "A";

   rem    End-For;

   rem   End-For;

   rem ***** End: Get/Set Component Interface Properties *****;

   

   rem ***** Execute Save *****;

   If Not &oUodaStdntGroupsPersCi.Save() Then;

      throw CreateException(0, 0, "Save failed");

   End-If;

   

   rem ***** Execute Cancel *****;

   If Not &oUodaStdntGroupsPersCi.Cancel() Then;

      errorHandler();

      throw CreateException(0, 0, "Cancel failed");

   End-If;

   

catch Exception &ex

   rem Handle the exception;

   rem &fileLog.WriteLine(&ex.ToString());

   MessageBox(0, "", 0, 0, "" | &ex.ToString());

end-try;

rem &fileLog.WriteLine("End");

SQL to get Long description of Translate Field

 

SELECT A.X_APP_TYPE   

 , B.XLATLONGNAME   

 , A.X_HP_ENTITYID   

 ,A.X_HP_BEARER   

  FROM PS_X_SIS_APP_KEYS A   

  , PSXLATITEM B   

 WHERE B.FIELDNAME = 'X_APP_TYPE'   

   AND B.FIELDVALUE = A.X_APP_TYPE   

   AND B.EFF_STATUS = 'A'   

   AND ( B.EFFDT = (  

 SELECT MAX(A_ED.EFFDT)   

  FROM PSXLATITEM A_ED   

 WHERE B.FIELDNAME = A_ED.FIELDNAME   

   AND B.FIELDVALUE = A_ED.FIELDVALUE   

   AND A_ED.EFFDT <= SYSDATE))

Radio button-like behavior using checkboxes in a grid

 Following PeopleCode  enforces radio button-like behavior using checkboxes in a grid. When the user checks a checkbox in one row of the grid, it will automatically uncheck all others.

/* Place this code in the FieldChange event of X_SELECT */

Local Rowset &rs;

Local Row &row;

Local integer &rowCount;

/* Get the grid rowset */

&rs = GetLevel0().GetRow(1).GetRowset(Scroll.X_STD_INSTLMNTS);

For &i = 1 To &rs.ActiveRowCount

  

   /* Uncheck all other checkboxes */

   If &i <> CurrentRowNumber() Then

      

      &rs(&i).X_STD_INSTLMNTS.X_SELECT.Value = "N";

   Else

      /* Ensure the clicked row is checked */

      &rs(&i).X_STD_INSTLMNTS.X_SELECT.Value = "Y";

      

   End-If;

End-For;

Sql.parm Miscount Error in Equations with Callable SQL

 The following occurs when executing an equation engine that uses a callable SQL (with a custom function), or callable SQL contains a custom view 

In Equation Engine, you need to have call parms such that you have 2 more than the number of binds and selected fields - so if you have no binds and
are only returning one field in the select - you would need 3 call parms (1 for sql return code, one for sql row count, one stem to catch the returning
field in your sql). However, if in your select statement in your SQL you use something like a substring function - then the equation will fail when you
test it with an error returned that you don't have the correct number of calls parms.

So, if your sql statement was

SELECT substr(A.STRM,1,2)
FROM ps_session_table


or 

SELECT ITEM_AMT FROM PS_CUSTOM_VW WHERE   EMPLID=:1 AND ADMIT_TERM=:2

You would expect to need 3 bind variables - 2 for sql return codes/row, and a stem to catch the select. However, the equation engine will error your
equation because it is counting each comma in your select as needing a new stem.

The available workaround is to create a TABLE that can be used to simplify the logic of the equation SQL. Insert the data in it and then reference that table instead - using a simpler SQL.