UniFAST / ICT reference

UniFAST ICT Resources

Team assignments

9 staff · 5 project leads · 4 focal/support

Ai

TES Focal/Support
System responsibility
RC Portal - Validation and TDP Uploading
Report generation
TES Masterlist and other TES data requests

Anton

TES Focal/Support
System responsibility
RC Portal - Report Generation and Registry
Report generation
TES Masterlist and other TES data requests

Billee

TDP Focal/Support
System responsibility
HEI Portal and Proponents Portal
Report generation
TDP Masterlist and other TDP data requests

Fel

TDP Focal/Support
System responsibility
DMT Focal, Files and Comms
Report generation
TDP Masterlist and other TDP data requests

Zach

Project lead
System responsibility
RC Portal - Validation and TDP Uploading
Report generation
TES Masterlist and other TES data requests

Froi

Project lead
System responsibility
RC Portal - Report Generation and DMT
Report generation
TDP Masterlist and other TDP data requests

Kian

Project lead
System responsibility
HEI Portal and Proponents Portal
Report generation
TES Masterlist and other TES data requests

Vincent

Project lead
System responsibility
TES and TDP Admin Portal
Report generation
TDP Masterlist and other TDP data requests

Jojo

Project lead
System responsibility
Disbursement and Billing module, overall management of ICT infrastructure
Report generation
Not specified in the workbook

Request workflows

Use these checklists when receiving system concerns and data requests.

System / portal concerns

1. Document and route the issue

Alternate/Support: Document the reported issue and report it to the project lead to discuss a resolution.

Project lead: Document the reported issue and resolve it immediately if possible.

2. Always collect

  • Issue or concern, with a screen recording or screenshot
  • HEI name and UII
  • Award batch
  • Grant category: TES (4Ps, Listahanan, To be awarded, ESGPPA) or TDP
  • Region
  • TES or TDP focal involved
  • RC involved

3. Handle feature requests

Acknowledge the concern without promising new features. Bring the request to the project lead for discussion.

“I'll bring this up for discussion with the project lead.”

Report generation requests

1. Clarify the output

Ask whether the request is for a masterlist or figures only.

2. Always collect

  • Academic year
  • Semester
  • Award batch
  • Region

3. Collect when applicable

  • HEI name and/or UII, if necessary
  • For TDP only: proponent's name (TDP representative)
Query links pending. The workbook refers to separate queries for masterlists and figures, but contains no working URLs.

Workflow guidance appears in the Ai and Zach rows of the source workbook. Presented here by role; blank workflow cells are not treated as separate instructions.

Common Queries

Select an academic year to view its query references. Additional years will appear when uploaded.

These are source templates and clause examples. Complete the WHERE conditions and check table, semester and award batch references before use. SQL is shown as reference text.

MASTER LIST QUERY / FOR AY 2026-2027 1st Semester
-- FOR AY 2026-2027 1st Semester
SELECT
    `b`.`hei_region_nir` AS `Region`,
    `a`.`ac_year` AS `Academic Year`,
     CONCAT("'", `b`.`hei_uii`) AS `HEI UII`,
    `b`.`hei_name` AS `HEI name`,
    `a`.`app_id` AS `App ID`,
    `a`.`award_no` AS `Award Number`,
    `a`.`award_batch` AS `Award Batch`,
    `a`.`lname` AS `Last name`,
    `a`.`fname` AS `First name`,
    `a`.`mname` AS `Middle name`,
    `a`.`ext_name` AS `Ext. name`,
    `a`.`sex` AS `Sex`,
    `a`.`Birthdate` AS `Birthdate`,
    `a`.`course_enrolled` AS `Degree Program`,
    `a`.`yearlevel` AS `Year level`,
    `a`.`status_1st` AS `Status`,
    `a`.`tdp_rep_1st` AS `TDP Representative`,
    `a`.`contact` AS `Contact`,
    `a`.`email` AS `Email`
FROM
    `unifastgov_ufdb`.`tbl_tes_app_2026_2027` `a`  -- ALWAYS CHECK AY
JOIN `unifastgov_ufdb`.`tbl_heis` `b`
ON
    `a`.`hei_uii_1st` = `b`.`hei_uii`
WHERE -- [CONDITION or FILTER]
MASTER LIST QUERY / FOR AY 2026-2027 2nd Semester
-- FOR AY 2026-2027 2nd Semester

SELECT
    `b`.`hei_region_nir` AS `Region`,
    `a`.`ac_year` AS `Academic Year`,
     CONCAT("'", `b`.`hei_uii`) AS `HEI UII`,
    `b`.`hei_name` AS `HEI name`,
    `a`.`app_id` AS `App ID`,
    `a`.`award_no` AS `Award Number`,
    `a`.`award_batch_2` AS `Award Batch`,
    `a`.`lname` AS `Last name`,
    `a`.`fname` AS `First name`,
    `a`.`mname` AS `Middle name`,
    `a`.`ext_name` AS `Ext. name`,
    `a`.`sex` AS `Sex`,
    `a`.`Birthdate` AS `Birthdate`,
    `a`.`course_enrolled` AS `Degree Program`,
    `a`.`yearlevel` AS `Year level`,
    `a`.`status_2nd` AS `Status`,
    `a`.`tdp_rep_2nd` AS `TDP Representative`,
    `a`.`contact` AS `Contact`,
    `a`.`email` AS `Email`
FROM
    `unifastgov_ufdb`.`tbl_tes_app_2026_2027` `a`  -- ALWAYS CHECK AY
JOIN `unifastgov_ufdb`.`tbl_heis` `b`
ON
    `a`.`hei_uii_2nd` = `b`.`hei_uii`
WHERE -- [CONDITION or FILTER]
COMMON SELECT CLAUSE / BY GRANT TYPE
-- BY GRANT TYPE
SUM(
        CASE WHEN a.status_1st = 'LISTAHANAN' AND award_batch IN('2','2.2','2.1') THEN 1 ELSE 0 -- CHECK AWARD BATCH
    END
) AS '4Ps',
SUM( 
    CASE WHEN a.status_1st = 'LISTAHANAN' AND award_batch NOT IN('2','2.2','2.1') THEN 1 ELSE 0 -- CHECK AWARD BATCH
END
) AS 'Listahanan',
SUM(
    CASE WHEN a.status_1st = 'TO BE AWARDED' THEN 1 ELSE 0
END
) AS 'PNSL',
SUM(
    CASE WHEN a.status_1st = 'ESGPPA' THEN 1 ELSE 0
END
) AS 'ESGPPA',
COMMON SELECT CLAUSE / HEI Type 1--
-- HEI Type 1--
SUM(CASE WHEN b.hei_it IN ('SUC','LUC') THEN 1 ELSE 0 END) AS `Public HEIs`, 
SUM(CASE WHEN b.hei_it = 'Private HEI' THEN 1 ELSE 0 END) AS `Private HEIs`
COMMON SELECT CLAUSE / HEI Type 2--
-- HEI Type 2--
SUM(
        CASE WHEN b.hei_it = 'LUC' THEN 1 ELSE NULL
    END
) AS 'LUC',
SUM(
        CASE WHEN b.hei_it = 'SUC' THEN 1 ELSE NULL
    END
) AS 'SUC',
SUM(
        CASE WHEN b.hei_it = 'Private HEI' AND b.hei_pnsl = '1' THEN 1 ELSE NULL
    END
) AS 'PNSL',
SUM(
        CASE WHEN b.hei_it = 'Private HEI' AND b.hei_pnsl <> '1' THEN 1 ELSE NULL
    END
) AS 'Regular Private HEI'
COMMON SELECT CLAUSE / BY SEX
-- BY SEX
SUM(
        CASE WHEN UPPER(a.sex) IN('MALE', 'M') THEN 1 ELSE 0
    END
) AS 'Male',
SUM(
    CASE WHEN UPPER(a.sex) IN('FEMALE', 'F') THEN 1 ELSE 0
END
) AS 'Female',
SUM(
    CASE WHEN UPPER(a.sex) NOT IN('FEMALE', 'F', 'MALE', 'M') THEN 1 ELSE 0
END
) AS 'Not Indicated'
COMMON SELECT CLAUSE / BY YEAR LEVEL
-- BY YEAR LEVEL
    SUM(CASE WHEN a.yearlevel = '1' THEN 1 ELSE 0 END) AS '1st Year',
    SUM(CASE WHEN a.yearlevel = '2' THEN 1 ELSE 0 END) AS '2nd Year',
    SUM(CASE WHEN a.yearlevel = '3' THEN 1 ELSE 0 END) AS '3rd Year',
    SUM(CASE WHEN a.yearlevel = '4' THEN 1 ELSE 0 END) AS '4th Year',
    SUM(CASE WHEN a.yearlevel = '5' THEN 1 ELSE 0 END) AS '5th Year',
    SUM(CASE WHEN a.yearlevel = '6' THEN 1 ELSE 0 END) AS '6th Year'
COMMON GROUP BY SCENARIO / BY REGION
-- BY REGION
SELECT
b.hei_region_nir,
COUNT(*)
FROM
    `unifastgov_ufdb`.`tbl_tes_app_2026_2027` `a`  -- ALWAYS CHECK AY
JOIN `unifastgov_ufdb`.`tbl_heis` `b`
ON
    `a`.`hei_uii_2nd` = `b`.`hei_uii`
GROUP BY b.hei_region_nir
ORDER BY b.hei_region_nir
COMMON GROUP BY SCENARIO / BY REGION, HEI NAME, HEI UII
-- BY REGION, HEI NAME, HEI UII
SELECT
b.hei_region_nir, b.hei_name, b.hei_uii,
COUNT(*)
FROM
    `unifastgov_ufdb`.`tbl_tes_app_2026_2027` `a`  -- ALWAYS CHECK AY
JOIN `unifastgov_ufdb`.`tbl_heis` `b`
ON
    `a`.`hei_uii_2nd` = `b`.`hei_uii`
GROUP BY b.hei_region_nir, b.hei_name, b.hei_uii
ORDER BY b.hei_region_nir, b.hei_name, b.hei_uii
COMMON GROUP BY SCENARIO / BY REGION HEI NAME, HEI UII, HEY TYPE
-- BY REGION HEI NAME, HEI UII, HEY TYPE
SELECT
b.hei_region_nir, b.hei_name, b.hei_uii,
b.hei_name AS `HEI Name`,

    CASE
        WHEN b.hei_pnsl = '1'
             AND b.hei_it = 'Private HEI'
            THEN 'PNSL'

        WHEN b.hei_pnsl <> '1'
             AND b.hei_it = 'Private HEI'
            THEN 'Regular Private HEI'

        WHEN b.hei_it <> 'Private HEI'
            THEN b.hei_it

        ELSE b.hei_it
    END AS `HEI Type`
COUNT(*)
FROM
    `unifastgov_ufdb`.`tbl_tes_app_2026_2027` `a`  -- ALWAYS CHECK AY
JOIN `unifastgov_ufdb`.`tbl_heis` `b`
ON
    `a`.`hei_uii_2nd` = `b`.`hei_uii`
GROUP BY b.hei_region_nir, b.hei_name, b.hei_uii, 'HEI Type'
ORDER BY b.hei_region_nir, b.hei_name, b.hei_uii, 'HEI Type'

Data Dictionary

8 terms · Definitions and query examples from the supplied workbook.

Batch examples are stated for AY 2026–2027 in the source. Query examples are reference conditions, not complete executable queries.

Validated Grantee / Qualified Grantee / TES or TDP Grantee

Definition

TES or TDP Grantee tagged as Listahanan, To be awarded, 4Ps and CHED-TDP

Query example

To get TES: 
status_1st IN ('LISTAHANAN','TO BE AWARDED','4PS')

To get TDP: 
status_1st = 'CHED-TDP'
Unvalidated / Unvalidated applicant or grantee

Definition

TES or TDP Grantee with no validation status.

Query example

To get unvalidated TES: 
status_1st IS NULL AND award_batch IN ('1','2','10')

It is very important to include award_batch in the condition to ensure that it will only select batches under the grant category. If award_batch is not included the return will include TDP applicants and vice-versa.
Applicant

Definition

Mostly refer to TES applicants that did not matched with 4Ps list.
Can also mean TES or TDP with no validation status yet but are eligible for validation.

Query example

To get applicants:
TES
cert_no_3 IN ('TES-APPLICANT-PRIVATE','TES-APPLICANT-PUBLIC') 
if requests include new 4Ps qualified applicants:
(cert_no_3 IN ('TES-APPLICANT-PRIVATE','TES-APPLICANT-PUBLIC')  OR award_batch = '2.1')
TDP
cert_no_3 IN ('TDP-APPLICANT')
Delisted

Definition

Validation status used to terminate or disqualify TES or TDP Grantes. Can still be appealed (TES and TDP Focal initiative).
Does not show for 2nd Semester or next AY for validation.

Delisted status:
'AVAILING OTHER SCHOLARSHIPS',
'COURSE NOT IN THE REGISTRY',
'DID NOT MEET HEI RETENTION POLICY',
'DOUBLE ENTRY - APPLICANT',
'DOUBLE ENTRY - APPLICANTS',
'DOUBLE ENTRY - TDP CONTINUING',
'DOUBLE ENTRY - TES CONTINUING',
'DROPPED',
'EXCEEDED ELIGIBILITY OF TES/TDP PROGRAM',
'EXCEEDED HEI MAXIMUM RESIDENCY',
'GRAD AND POST GRAD STUDENT',
'GRADUATED',
'HEI NOT CHED-RECOGNIZED',
'INCOMPLETE DOCUMENT SUBMITTED',
'NO ALLOCATION',
'NO RECORDS FOUND IN THE HEI',
'NO SUBMISSION OF DOCUMENTARY REQUIREMENTS',
'NON-PNSL HEI',
'NON-RESIDENT',
'NOT ENROLLED',
'NOT ENROLLED FOR ONE ACADEMIC YEAR',
'NOT ENROLLED FOR THE CURRENT SEMESTER',
'OPTED OUT',
'SECOND COURSER',
'SECOND COURSER, GRAD AND POST GRAD STUDENT',
'SENIOR HIGH STUDENT',
'TRANSFERRED TO NON-PNSL HEI',
'TRANSFERRED WITHOUT NOTICE',
'NO IR',
'BARMM PRIVATE'

Replace the bracketed reason with the applicable status value. The source example uses TES award batches; confirm the correct batches when working with TDP.

Query example

To get delisted:
status_1st IN ('[delisted reason]') AND award_batch IN ('1','2','10')

It is very important to include award_batch in the condition to ensure that it will only select batches under the grant category. If award_batch is not included the return will include TDP delisted and vice-versa.
On-Hold

Definition

Validation status used to indicate that grantee failed to enroll due to accepted reasons. Can still be appealed (TES and TDP Focal initiative)

ON-HOLD STATUS
'CLOSURE OF HEI',
'DELISTED CHED-RECOGNIZED LUC',
'NOT ENROLLED - 1 YEAR LEAVE MDSD AND MARITIME',
'NOT ENROLLED - CANDIDATE FOR GRADUATION/COMPLETED ACADEMIC REQUIREMENTS',
'NOT ENROLLED - NO SUBJECT AVAILABLE',
'NOT ENROLLED WITH LOA',
'Wrong tagging of HEI',
'NO ALLOCATION',
'AVAILING ONE TIME GRANT',
'MARINA COURSE/PROGRAM'

Replace the bracketed reason with the applicable status value. The source example uses TES award batches; confirm the correct batches when working with TDP.

Query example

To get on-hold:
status_1st IN ('[delisted reason]') AND award_batch IN ('1','2','10')

It is very important to include award_batch in the condition to ensure that it will only select batches under the grant category. If award_batch is not included the return will include TDP on-hold and vice-versa.
New and Continuing

Definition

NEW - Newly uploaded TES or TDP Applicants that are eligible for validation and was uploaded in the current AY. Can be identified by batch number with a .1. New grantees will be absorbed to continuing batch after 2 semester
Example:
TES New: 2.1
TDP New: 26.1

CONTINUING - Previously validated grantee and have been uploaded in previous AY. To be considered as a CONTINUING Grantee applicant should have been validated as grantee from the previous AY or in TDP case have been a grantee for 2 semester. Can be identified with whole number as batch.
Example:
TES Continuing: 1, 2, 10
TDP Continuing: 3

Query example

To get NEW TES or TDP for AY 2026-2027:
status_1st  = 'LISTAHANAN' AND award_batch = '2.1'
status_1st = 'CHED-TDP' AND award_batch = '26.1'

To get CONTINUING TES or TDP for AY 2026-2027:
status_1st = 'LISTAHANAN' AND award_batch = '1'
status_1st = 'LISTAHANAN' AND award_batch = '2'
status_1st = 'CHED-TDP' AND award_batch = '3'
Award batch

Definition

Batch number to identify which billing batch the grantee belong. Example for AY 2026-2027
TES Batches (continuing):
4Ps = 2
Listahanan = 1
To be awarded (PNSL) = 10
TDP Batches (continuing):
3
TES Appeal batch (continuing)
4Ps = 2.2
Listahanan = 1.2
To be awarded (PNSL) = 10.2
TDP Appeal batch (continuing)
3.2
TES new batch:
4Ps = 2.1

Query example

TES AND TDP CONTINUING
- 4PS: status_1st = 'LISTAHANAN' AND award_batch IN ('2')
- LISTAHANAN: status_1st = 'LISTAHANAN' AND award_batch IN ('1')
- PNSL: status_1st = 'TO BE AWARDED' AND award_batch IN ('10')
- CHED-TDP: status_1st = 'CHED-TDP' AND award_batch IN ('3')

TES AND TDP NEW
- 4PS: status_1st = 'LISTAHANAN' AND award_batch IN ('2.1')
- CHED-TDP: status_1st = 'CHED-TDP' AND award_batch IN ('26.1')
Grant Type

Definition

TES
LISTAHANAN
- Grantee matched with Listahanan List
- award_batch = '1' -- or '1.2' for appeal
- status_1st = 'LISTAHANAN'
PNSL
- Grantee enrolled in areas where no SUCs/LUCs
- Tagged as 'TO BE AWARDED'
- award_batch = '10' -- or '10.2' for appeal
-status_1st = 'TO BE AWARDED'
4PS
- Grantee matched with 4Ps List
- Tagged as 'LISTAHANAN' and can be differentiate using award batch
- award_batch = '2' -- or '2.2' for appeal or '2.1' for new
CHED-TDP
- Grantee endorsed by TDP Proponents
- Tagged as 'CHED-TDP'
- award_batch = '3' -- or '3.2' for appeal or '26.1' for new

No query example supplied.