CAS Integration 1 - Add CAS Supplier Info to Student Profile (Get Supplier Number)

[HRAGANBC]

User Story: SIMS must record and maintain a student's 'supplier' information as part of their profile, so that a student's SIMS profile can match with their corresponding account in the Corporate Accounting System (CAS), and SIMS can relay critical information about that student to CAS.

Acceptance Criteria:

Initiate 'Get Supplier' Process

• [x] After SIN is successfully validated, begin 'Get Supplier' Process (See Doc 1, Section: 11.1.6, page 13 for context)
• [x] Call CAS using the Last Name and SIN
• [x] Preserve a history of the following variables received during the response:
  • SupplierNumber
  • SupplierName
  • status
  • Supplier protected
  • lastupdated And from within Supplier Address
  • suppliersitecode
  • Addressline1
  • City
  • Province
  • country
  • postalcode
  • status
  • siteprotected
  • lastupdated
• [ ] If the response returns none of the above values, then see #3260 and #3258 for error handling
• [ ] API should be called only during business days (per #3327)

Technical

• [x] Follow a similar process from SIN validation.
  • [x] Create a new table to store the history of CAS supplier requests and allow Ministry manual entry, suggested table name sims.cas_suppliers. In the future, we will have a new table for invoices that we may call sims.cas_invoices.
  • [x] New table columns following in general the datatypes received from CAS.
    • id SERIAL PRIMARY KEY: Auto-generated sequential primary key column.
    • supplier_number VARCHAR(100) NULL: Supplier number received from CAS. null when no data was ever retrieved from CAS.
    • supplier_name VARCHAR(100) NULL: Supplier name received from CAS. null when no data was ever retrieved from CAS.
    • status VARCHAR(50) NULL: Supplier status received from CAS. null when no data was ever retrieved from CAS.
    • supplier_protected BOOLEAN NULL: Protected flag received from CAS which means the student profile was created by SFAS and therefore no system other than SFAS can change it. null when no data was ever retrieved from CAS.
    • last_updated TIMESTAMP WITH TIME ZONE NULL. Date and time of the last update. null when no data was ever retrieved from CAS.
    • supplier_address: JSONB. Supplier address from the CAS integrations. When multiple exists, only the active one should be saved. We will not be saving the array received at this moment, only a single entry from the received list should be persisted as JSONB. The JSONB should be mapped as below respecting the case to follow the SIMS pattern. Keep the datatypes as received from CAS.
    • supplierSiteCode
    • addressLine1
    • city
    • province
    • country
    • postalCode
    • status
    • siteProtected
    • last updated
    • supplier_status ENUM (Pending supplier verification, Pending address verification, Verified, Verified manually) NOT NULL. Indicates if the system should execute verification in the record calling some of the CAS integrations; if the record represents manual entry and no actions are needed; or if no further verifications are needed.
    • supplier_status_updated_on TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW. Date when the column supplier_status was updated.
    • is_valid BOOLEAN NOT NULL DEFAULT false: Indicates when the supplier is considered valid and an invoice can be generated using the information.
    • Add audit columns in this table similar to other tables
  • [x] Upon student creation, create a new entry in the sims.cas_suppliers and have it associated with a new column in sims.students.cas_supplier_id table. This would follow the same idea from sims.sin_validations and sims.students.sin_validation_id.
• [x] Create a new scheduler name cas-supplier-integration to execute the CAS requests to try to get the supplier.
  • [x] Only check for students with the SIN already validated (sims.sin_validations.valid_sin is true).
  • [x] Only check for supplier records where is_valid is not true and supplier_status is Pending supplier verification (other enum types are reserved for future use).
  • [x] Once the data is retrieved from CAS, set the record to Verified and update the supplier_status_updated_on.
  • [x] Execute it once daily as per the recommended configuration below.

       {
          "cron": "0 7 * * *",
          "retry": 3,
          "cleanUpPeriod": 2592000000,
          "retryInterval": 180000,
          "dashboardReadonly": false
        }

• [ ] Create a new CAS API Integration Service under backend/libs/integrations/cas.
  • [x] Enable authentication and call the "Get Supplier" using the last name and SIN.
  • [x] Use the endpoint as per doc 1 in the context https://<webserviceURL>:<port>/ords/cas/cfs/supplier/{lastname}/lastname/{sin}/sin.
  • [ ] Create new secrets to store the Client ID and Client Secret.
  • [x] In this first effort the authentication can be as simple as possible to allow the call to the "supplier" information. In an upcoming PR it can be improved to add, for instance, caching to allow multiple requests to reuse the same token.
• [x] E2E Tests
  • [x] E2E test for the new queue-consumers scheduler.
• [ ] Possible PR separation
  • [x] DB Migrations, SQL + entity models for new tables and the new student column.
  • [x] Student creation saving the new entry to the history table.
  • [x] Scheduler looking for records to call CAS get a supplier and persist the data retrieved.

Technical Context:

Question: Should the auth token be stored in Redis as we know how long the token is valid for?

Stephen or Jason to supply required connection information (.env)

Sample Code

  async authenticate(): Promise<any> {
    const url = `${this.baseUrl}/oauth/token`;
    const auth = Buffer.from(`${this.clientId}:${this.clientSecret}`).toString(
      'base64',
    );
    const headers = {
      'Content-Type': 'application/x-www-form-urlencoded',
      Authorization: `Basic ${auth}`,
    };
    const body = new URLSearchParams();
    body.append('grant_type', 'client_credentials');
    const config: AxiosRequestConfig = {
      headers,
    };
    const response = await firstValueFrom(
      this.httpService.post(url, body.toString(), config),
    );
    return response.data as AuthResult;
  }

  async getSupplier(token: string, sin: string, surname: string): Promise<any> {
    const url = `${this.baseUrl}/cfs/supplier/${surname}/lastname/${sin}/sin`;
    const headers = {
      Authorization: `Bearer ${token}`,
    };
    const config: AxiosRequestConfig = {
      headers,
    };
    const response = await firstValueFrom(this.httpService.get(url, config));
    return response.data as SupplierQueryResult;
  }

---

Mock-Ups

Fig 2 - Success Response Format for Get Supplier Number Process

Doc 1 Supplier Web Service RESTful Client Onboarding v02_01.pdf

Doc 2: Individual Supplier Web Service Field Requirements (All Requests) JAN2023.xlsx

[sslaws]

@HRAGANBC we should review this and then take it to grooming

[guru-aot]

@andrewsignori-aot

1. the understanding on Upon student creation, create a new entry in the sims.cas_suppliers and have it associated with a new column in sims.students.cas_supplier_id table. This would follow the same idea from sims.sin_validations and sims.students.sin_validation_id., why are we creating an entry if the student is not validated for sin and on creation?
2. On displaying the table cas_suppliers UI in the ministry portal, the line items for the suppliers for a particular student has to be fetched from this table cas_suppliers. Without the relation of student_id in the table how we could display muliple rows?
3. Reason for having Pending address verification status in the enum.
4. What will be the enum status if the CAS call is failure or no response is returned.
5. When a manual Edit Info is added for a particular student only SupplierNumber and SiteCode information is populated? What other information has to be updated in the sims.cas_suppliers table other than the supplier_status as Verified manually?
6. Efforts on configuration for cas api, like the URL endpoint, authentication parameters in our project is not captured. Do we need it initially as a first effort and from there create the table for cas_suppliers as subsequent efforts?

[andrewsignori-aot]

@andrewsignori-aot

1. the understanding on Upon student creation, create a new entry in the sims.cas_suppliers and have it associated with a new column in sims.students.cas_supplier_id table. This would follow the same idea from sims.sin_validations and sims.students.sin_validation_id., why are we creating an entry if the student is not validated for sin and on creation?
2. On displaying the table cas_suppliers UI in the ministry portal, the line items for the suppliers for a particular student has to be fetched from this table cas_suppliers. Without the relation of student_id in the table how we could display muliple rows?
3. Reason for having Pending address verification status in the enum.
4. What will be the enum status if the CAS call is failure or no response is returned.
5. When a manual Edit Info is added for a particular student only SupplierNumber and SiteCode information is populated? What other information has to be updated in the sims.cas_suppliers table other than the supplier_status as Verified manually?
6. Efforts on configuration for cas api, like the URL endpoint, authentication parameters in our project is not captured. Do we need it initially as a first effort and from there create the table for cas_suppliers as subsequent efforts?

1. Following the same approach from SIN we ensure there will always be a status associated with the student even if it is "pending".
2. Following the same approach from SIN we can have the student ID added.
3. Pending address verification is reserved for future use when students update their profiles and we need to communicate CAS. It is not part of this ticket and it can be added later if the Team is not willing to have it added now.
4. Failure to call CAS should keep the status as it is to a new attempt happen. 404 will be returned if not student was found and I believe that we can set the is_valid to false and set the record as validated.
5. Details for manual editing will be clarified in upcoming tickets.
6. The effort for the authentication is specified on "Create new secrets to store the Client ID and Client Secret." and we can just have the API URL added.

[andrepestana-aot]

@HRAGANBC @sslaws @JasonCTang

I have some questions I couldn't find in the documentation or ACs:

• Can a supplier response have more that one "item"? If so, should we ignore the rest?
• Is it possible that a supplier does not have an address? What to do if so?
• What's criteria to set isValid to true for a supplier? Should it just set isValid to true if there's a response for the request?

[HRAGANBC]

@andrepestana-aot

Can a supplier response have more that one "item"? If so, should we ignore the rest?

My understanding is that you can only have one item, but Nino has asked CAS for confirmation

Is it possible that a supplier does not have an address? What to do if so?

My understanding is that is not possible, but Nino has asked CAS for confirmation

What's criteria to set isValid to true for a supplier? Should it just set isValid to true if there's a response for the request?

My understanding is that is a variable we define ourselves to reflect whether or not SIMS has all the information it needs from CAS transactions. We set isValid = true when:

• we receive a successful response from CAS (i.e. the student was found in CAS), regardless of the values of the particular fields. (All potential errors will be handled in seperate ticket, and are still considered 'valid' from this perspective)
• A minstry user manually input the supplier information through the interface

@ninosamson @sslaws FYI

[ninosamson]

Hi @andrepestana-aot.

Confirming that all suppliers MUST have an address as per minimum requirement on creation.

[andrepestana-aot]

@ninosamson Could you confirm if the lastname for the request should be all in upper case, please?. In my tests I had different results with upper and lower cases.

[ninosamson]

From the documentation @andrepestana-aot