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;