CAS AP Invoice - Invoices and Batches

[HRAGANBC]

User Story:
As a member of the SABC Finance team (Ministry User), I need to be able to review and approve a detailed inventory of the previous day’s PT and FT provincial grant disbursements so that SIMS can notify CAS about these disbursements and maintain up to date records of a student’s taxable income. This summary will need to have a sub total of each award and a total for the batch.
Each Invoice should relate to a single disbursement for an individual student. It will need to have two line items per award under the invoice.
Every Invoice will be included in a daily batch of invoices.
Once approved, the entire batch is sent to CAS. We need to record the outcome for each invoice, success or error

Acceptance Criteria:

Access/Roles

• Add a new role to business-administrator group for all AP invoicing activities (view/download batch and approve)

Invoices

• Every student who receives provincial grants (confirmed via disbursement receipt) will need an invoice PER doc/cert number. (This should normally be one invoice per disbursement receipt but could have odd edge cases where two doc numbers on a single disbursement receipt)
• Every invoice will need to contain two line items PER grant disbursed. (SBSD, BCAG-PT, BCAG-FT, BGPD)
  • Each award will need a Debit Line and a Credit Line
  • The Debit Line will need a Distribution Account unique to the Award
  • The Credit Line will need a Distribution Account unique to the Award
• Distribution Accounts per DR/CR per Award:
  • TEST/Staging Dist Accounts: Placeholders fine for now
  • PROD Dist Accounts
• Every invoice needs a unique invoice number: For now, "supplier number concatenated with record id" or similar.
• Full list of fields for the invoice and invoice lines: AP Invoice Web Service Field Requirements -General.xlsx
  • sims.disbursement_receipts.disburse_date should be sent as invoice date.
  • sims.disbursement_receipts.created_at should be sent as GL Date, converting it to PST/PDT.
  • sims.disbursement_receipts.batch_run_date should be sent as date invoice received.
• Invoices will need to have their status tracked individually as CAS calls/responses are done per invoice.
• Invoices that had an error may need manual intervention (Supplier Maintenance) before retrying through a CAS call, or may need to be entered manually (not done through CAS)

Batches

• Daily there will be a new "Batch Name" that must be unique: "SIMSBATCH"(constant) + "sequence number from sequence control".
• Batch should be generated automatically Mon-Fri AFTER disbursement receipt process and CAS process.
• Invoices are eligible to be added to the daily batch when:
  • We have received a disbursement receipt for it, AND
  • The student has a valid CAS supplier, AND
  • It has not already been added to a batch.
• Before SENDING the invoices attached to a batch to CAS, the batch must be approved by a ministry user.
• Allow users to download a report for audit and approval purposes for each batch.
  • Filename to include batch name, batch date
  • Each record should represent an invoice detail record (CR & DR on the same record line - important to have award type).
  • It should include the invoice number, student last name, student given name, supplier number, student sin, gl date, our document number, award type, value & distribution account for CR, value & distribution account for DR
  • From this report, we should be able to see a snapshot of what was approved and wouldn't be impacted by responses (error/success) from CAS for the invoices.

Business Context:

• No limit on how far back to check for disbursement receipts when creating a batch. Will likely revisit this to support invoicing in the correct tax year. Possible options include a report for disbursements without a batch for identification and creating a limit (30 days/60 days/current calendar year/etc)

Files for CAS: DEVS>CAS
For PT and FT student aid funding, this will only require $0 invoices to ensure T4As are generated based on provincial grants.
This may need to be revised in the future with inclusion of any invoicing for actual funds disbursed or for peripheral programs.
Although there are no set volume limitations, the service is designed for single processing in real time throughout the business day, typically up to 10,000 requests per day (distributed throughout the day) and up to 2,000 invoices (if sent one after the other)

AP Web Service Client Onboarding v02r02.pdf

AP Invoice Web Service Field Requirements -General.xlsx

Technical

Batches UI

• Create a new menu below Reports at the bottom left of the Ministry Portal.
• List all the available batches in descending order.
  • Columns
    • Batch Date
    • Batch Name
    • Approval Status
    • Approval Status Audit
    • Actions
      • Download
      • Approve (to be implemented)
      • Reject (to be implemented)
• Invoices filtering any disbursement receipt that:
  • Receipt values with some BCSG greater than 0 (zero).
  • For every receipt that has a BCSG, select all value_types BC Grant (but BCSG) records from the disbursement_values and consider only its effective_amount to be added to the invoices (only select records where the BC Grant is greater than zero).
  • For every BC Grantcreate an invoice details with all active distribution accounts from the new table cas_distribution_accounts associated with the grant value_code. Expected to have two (CR/DR) active distribution accounts for each grant code.
  • The invoice for the disbursement ID was never created.

CSV Report

• Invoice Number
• Given Names
• Last Name
• SIN
• Supplier Number
• Document Number
• GL Date
• Award Type
• CR Amount
• CR Account
• DR Amount
• DR Account
  Order by Award Code, Supplier Number, and Document number.

Tables Schemas

cas_invoice_batches

• id : serial;
• batch_name: varchar(50);
• batch_date: timestamp.
• approval_status: Pending, Approved, Rejected;
• approval_status_updated_on: timestamp;
• approval_status_updated_by: related to user_id.
  • Notes:
    • no notes are expected at this time.
    • add regular audit columns.

cas_invoices

• id: serial;
• cas_invoice_batch_id: related to cas_invoice_batches;
• disbursement_id: related to disbursements;
• cas_supplier_id: related to the active supplier ID at the time of the batch creation;
• invoice_number: varchar(40), generated during the time of the batch creation;
• invoice_status: Pending, Sent, Manual intervention
• errors: string array.
  • Notes:
    • no notes are expected at this time.
    • add regular audit columns.

cas_invoice_details

• id: serial;
• cas_invoice_id: related to cas_invoices;
• cas_distribution_account_id: related to cas_distribution_accounts.
• value_amount: numeric(8, 2)
  • Notes:
    • add regular audit columns.

cas_distribution_accounts

• ìd: serial;
• award_value_code: varchar(10);
• operation_code: CR or DR
• distribution_account: distribution account account to be reported to CAS.
• is_active: boolean
• Notes:
  • add regular audit columns.
  • create a unique index between award_value_code, operation_code, and is_active as true.

[andrewsignori-aot]

Analisys

Questions

• Should we restrict the maximum amount of records in a single batch? For instance 2000 as stated in the CAS docs?

Scheduler

• Create a new scheduler to be executed once a day (suggestion to be after all the receipts for a day were imported).
• Scheduler to create a batch and related invoices filtering any disbursement receipt that:
  • Receipt values with some BCSG greater than 0 (zero).
  • For every receipt that has a BCSG, select all value_types BC Grant (but BCSG) records from the disbursement_values and consider only its effective_amount to be added to the invoices (only select records where the BC Grant is greater than zero).
  • For every BC Grantcreate an invoice details with all active distribution accounts from the new table cas_distribution_accounts associated with the grant value_code. Expected to have two (CR/DR) active distribution accounts for each grant code.
  • The invoice for the disbursement ID was never created.
  • Invoice is created but is associated with an invoice status different than Pending or Sent or it is associated with a Rejected cas_invoice_batches record.
  • A new notes category can be created to have invoice status notes associated with the student and keep track of the status changes for invoices.
    • Suggested category: Invoices
    • Suggested message (user name and dates are always captured):
      • Invoice for document number 12345679, status changed to Removed from batch.
      • Invoice for document number 12345679, status changed to Manual intervention.

Batches UI

• Create a new menu below Reports at the bottom left of the Ministry Portal.
• List all the available batches in descending order.
  • Columns
    • Creation date
    • Name
    • Approval Status
    • Report

CSV Report

• Fiscal Year?
• Batch Name
• Batch Date
• Student Name
• Student SIN
• Supplier Number
• Supplier Site Number
• Document Number
• Invoice Number
• Invoice Amount
• GL Date
• Line Number
• Line Code
• Line Amount
• Distribution Account
  Can the report be one line per invoice detail?

Tables Schemas

cas_invoice_batches

• id : serial;
• batch_name: varchar(50);
• batch_date: timestamp.
• approval_status: Pending, Approved, Rejected;
• approval_status_updated_on: timestamp;
• approval_status_updated_by: related to user_id.
  • Notes:
    • no notes are expected at this time.
    • add regular audit columns.

cas_invoices

• id: serial;
• cas_invoice_batch_id: related to cas_invoice_batches;
• disbursement_id: related to disbursements;
• cas_supplier_id: related to the active supplier ID at the time of the batch creation;
• invoice_number: varchar(40), generated during the time of the batch creation;
• invoice_status: Pending, Sent, Manual intervention
• errors: string array.
  • Notes:
    • no notes are expected at this time.
    • add regular audit columns.

cas_invoice_details

• id: serial;
• cas_invoice_id: related to cas_invoices;
• cas_distribution_account_id: related to cas_distribution_accounts.
• value_amount: numeric(8, 2)
  • Notes:
    • add regular audit columns.

cas_distribution_accounts

• ìd: serial;
• award_value_code: varchar(10);
• operation_code: CR or DR
• is_active: boolean
• Notes:
  • add regular audit columns.
  • create a unique index between award_value_code, operation_code, and is_active as true.

CAS API Invoice Payload Details

{
    "invoiceType": "Standard", // Fixed for now and not configurable, will be hard-coded while generating the CAS API payload.
    "supplierNumber": "3125635", // Retrieved from the cas_supplier_id associated during the batch creation.
    "supplierSiteNumber": "001", // Retrieved from the cas_supplier_id associated during the batch creation.
    "invoiceDate": "17-APR-2024", // Batch creation date (best assumption for now) or approval date.
    "invoiceNumber": "988855", // For now, "supplier number concatenated with record id" or similar.
    "invoiceAmount": 300.00, // Always 0(zero). Since SIMS will be generating the debt and credit records, the SUM should be always 0. This is not expected to be calculated at this moment to execute any sort of validation.
    "payGroup": "GEN GLP", // // Fixed for now and not configurable, will be hard-coded while generating the CAS API payload.
    "dateInvoiceReceived": "02-MAR-2024", // Disbursement receipt file date.
    "dateGoodsReceived": "01-MAR-2024", // Do not provide it.
    "remittanceCode": "01", // Fixed for now and not configurable, will be hard-coded while generating the CAS API payload.
    "specialHandling": "N", // Fixed for now and not configurable, will be hard-coded while generating the CAS API payload.
    "nameLine1": "", // Do not provide it. The docs mentioned it as conditional but not on which field. 
    "nameLine2": "", // Do not provide it. The docs mentioned it as conditional but not on which field. 
    "addressLine1": "", // Do not provide it. The assumption is that all fields address-related fields can be optional if "address line 1" is not provided.
    "addressLine2": "", // Do not provide it.
    "addressLine3": "", // Do not provide it.
    "city": "", // Do not provide it.
    "country": "", // Do not provide it.
    "province": "", // Do not provide it.
    "postalCode": "", // Do not provide it.
    "qualifiedReceiver": "", // Do not provide it.
    "terms": "Immediate", // Fixed for now and not configurable, will be hard-coded while generating the CAS API payload.
    "payAloneFlag": "Y", // Do not provide it.
    "paymentAdviceComments": "Test", // Do not provide it.
    "remittanceMessage1": "", // Send an empty string for now.
    "remittanceMessage2": "", // Send an empty string for now.
    "remittanceMessage3": "", // Do not provide.
    "glDate": "17-APR-2024", // Batch creation date for now.
    "invoiceBatchName": "CASAPWEB1", // "SIMSBATCH"(constant) + "sequence number from sequence control".
    "currencyCode": "CAD", // Fixed and not configurable, will be hard-coded while generating the CAS API payload.
    "invoiceLineDetails": [
        {
            "invoiceLineNumber": 1, // Mandatory, incremental number inside the invoiceLineDetails list.
            "invoiceLineType": "Item", // Fixed as per the docs.
            "lineCode": "DR", // Fixed as per the docs (DR or CR).
            "invoiceLineAmount": 150.00, // Award(grant) value.
            "defaultDistributionAccount": "105.15006.10120.5505.1500000.000000.0000", // Will be specific for an award(grant) plus the operation type (credit/debit).
            "description": "Test Line Description", // Do not provide it.
            "taxClassificationCode": "",  // Do not provide it.
            "distributionSupplier": "",  // Do not provide it.
            "info1": "",  // Do not provide it.
            "info2": "",  // Do not provide it.
            "info3": ""  // Do not provide it.
        },
        {
            "invoiceLineNumber": 2, // Mandatory, incremental number inside the invoiceLineDetails list.
            "invoiceLineType": "Item", // Fixed as per the docs.
            "lineCode": "CR",  // Fixed as per the docs (DR or CR).
            "invoiceLineAmount": 150.00, // Award(grant) value.
            "defaultDistributionAccount": "105.15006.10120.7701.1500000.000000.0000", // Will be specific for an award(grant) plus the operation type (credit/debit).
            "description": "Test Line 2",  // Do not provide it.
            "taxClassificationCode": "",  // Do not provide it.
            "distributionSupplier": "",  // Do not provide it.
            "info1": "",  // Do not provide it.
            "info2": "",  // Do not provide it.
            "info3": ""  // Do not provide it.
        }
    ]
}

[andrewsignori-aot]

@CarlyCotton are the distribution accounts different for the different offering intensities or grants like BCAG-PT/FT should be considered the same CR/DB distribution accounts?
Should we consider the SIMS grant names as the only identified for a CR/DR destruction account or should it be SIMS grant names + offering intensity?

[andrewsignori-aot]

@CarlyCotton are you aware if there is a cut date till when we should be checking if receipts have invoices generated?

[CarlyCotton]

@andrewsignori-aot
Re SIMS Grant Names & Distribution Accounts - pending response from our Finance user.

Invoices not generated - I think this can be accomplished by a report that users can run. Disbursements without an Invoice or something.

[andrewsignori-aot]

@CarlyCotton persisting the decisions from the last call, they are:

• Batches should be created on scheduled bases
  • Every weekday (Monday to Friday)
  • After CAS window for write operations to allow CAS updates, for instance, fixing a supplier.
  • After the disbursement receipts are received.
• Grants can have different distribution accounts for part-time/full-time and the offering intensity will be considered.
• The invoice date will be the disbuse_date from the sims.disbursement_receipts table till a different direction is given.

[andrewsignori-aot]

@CarlyCotton the AC states "an invoice PER disbursement date" but can we consider "one invoice per document number", which means "one invoice per disbursement receipt", right?

Every student who receives provincial grants (confirmed via disbursement receipt) will need an invoice PER disbursement date.

[andrewsignori-aot]

@CarlyCotton the "batch date" mentioned in some business AC does not seem part of the CAS invoice payload, right?
If it is right, should we assume that it will be used only by SIMS?
If used only by SIMS, would it represent the date when the batch was created and can it contain date+time?

[CarlyCotton]

@andrewsignori-aot
Confirming:
Batches Exactly. Created automatically on a schedule. Weekdays after current CAS scheduler and the disbursement receipt scheduler.
Grants Different distribution accounts for CR & DR on SBSD (combined FT/PT), BGPD, BCAG (PT), BCAG (FT)
Invoice Date will be disburse_date
PER disbursement date This can be 1:1 with document number or disbursement receipt. I want to avoid connecting based on disbursement receipt file.
Batch Date is for our own batch processes. Can include date/time.

New:
Date Invoice Received will be batch_run_date
GL Date will be SIMS receipt record

[andrewsignori-aot]

@CarlyCotton adding the draft from our latest chat to not lose it. Once your notes are in they will take precedence.

• sims.disbursement_receipts.dirsburse_date should be sent as invoice date.
• sims.disbursement_receipts.created_at should be sent as GL Date, converting it to PST/PDT.
• sims.disbursement_receipts.batch_run_date should be sent as date invoice received.
  The above data does not need to be persisted in the invoices tables and can be retrieved at the moment of the CAS API call since they are not supposed to be updated.

[andrewsignori-aot]

@CarlyCotton, question originated from a PR comment.
Regarding associating the active supplier to the invoice, the initial thought was to have the active supplier saved as part of the batch to allow the user to see it in the snapshot.
In the situation where the supplier is updated for some reason between the batch generation and CAS API call, should we action on it in any way?

Since the supplier being active is part of the criteria to create the invoice I would say that we should persist it when the batch is created and in the event it changes and CAS API call fails, it will generate a manual intervention. Please let me know if it makes sense.

[andrewsignori-aot]

@CarlyCotton do you see any need to store the student SIN currently associated with the student for history purposes?

[CarlyCotton]

@andrewsignori-aot

In the situation where the supplier is updated for some reason between the batch generation and CAS API call, should we action on it in any way?

Yes, I think we continue to use the cas supplier at the time of batch creation. Worst case we get a fail on the single invoice.

do you see any need to store the student SIN currently associated with the student for history purposes?

Nope, the supplier is the more important piece of information for the invoice. If the SIN doesn't match the supplier we end up in the same position with manual intervention being required.

[andrewsignori-aot]

@CarlyCotton I am planning to have the scheduler create the batches running at 8 PM PST from Monday to Friday considering that:

• disbursement-receipts-file-integration runs every day at 6 AM PST.
• cas-supplier-integration runs on weekdays at 12 PM
• This service does not need to obey CAS window because operations happen only on SIMS.

[andrewsignori-aot]

@CarlyCotton are you aware if there is a cut date till when we should be checking if receipts have invoices generated?

@CarlyCotton I was not able to find an answer for this one. Did we have a decision about a possible date to stop to look for a pending receipt?

For instance, "only check disbursement receipts with disbursed dates in the last month".

[andrewsignori-aot]

@CarlyCotton is there a plan to add some error handling to the batch generation?
For instance, if there is an award but no distribution accounts configured or if the distribution accounts do not have CR/DR records?
Right now there are no checks to ensure distribution accounts are present. Besides that, any error in any invoice will cause the job to fail and the batch generation will be aborted.

[CarlyCotton]

@andrewsignori-aot

At this point I don't have a limit on how far back to search for receipt disbursements. This may change in the future as this new daily batch process stabilizes.

Didn't have a plan for error handling on the batch generation. Is this going to be run through a job on the dashboard? Logs and a sysdig alert like the other processes would work.

[andrewsignori-aot]

Demo on DEV

Using existing applications on DEV with a receipt already in place.

Last name: JACK UPDATED
Application Number: 2023000516
https://dev.sims.studentaidbc.ca/ministry/student/205/application/118156/assessment-award-details/109378

Last name: Boni Signori_1
Application Number: 2024000011
https://dev.sims.studentaidbc.ca/ministry/student/264/application/118110/assessment-award-details/109318

Notes

• Make one supplier invalid.
• Generate another batch after fixing the supplier for JACK.
• Show new role aest-cas-invoicing.
• Explain the distribution accounts and how they will be added to the release instructions.
• Show table filters and sorting.

Tables Reset

delete from sims.cas_invoice_batches 
delete from sims.cas_invoices 
delete from sims.cas_invoice_details 

Fake Distribution Accounts to be inserted in non-prod

INSERT into
	sims.cas_distribution_accounts (award_value_code,"offering_intensity",operation_code,distribution_account,is_active,created_at,updated_at,creator,modifier)
VALUES
	 ('BGPD','Full Time','CR','BGPD.CR.FULL-TIME.0000000000000000000000',true,'2025-01-29 09:07:18.025','2025-01-29 09:07:18.025',634,NULL),
	 ('BGPD','Full Time','DR','BGPD.DR.FULL-TIME.0000000000000000000000',true,'2025-01-29 09:07:18.025','2025-01-29 09:07:18.025',634,NULL),
	 ('SBSD','Full Time','CR','SBSD.CR.FULL-TIME.0000000000000000000000',true,'2025-01-29 09:07:18.025','2025-01-29 09:07:18.025',634,NULL),
	 ('SBSD','Full Time','DR','SBSD.DR.FULL-TIME.0000000000000000000000',true,'2025-01-29 09:07:18.025','2025-01-29 09:07:18.025',634,NULL),
	 ('BCAG','Part Time','CR','BCAG.CR.PART-TIME.0000000000000000000000',true,'2025-01-29 09:07:18.025','2025-01-29 09:07:18.025',634,NULL),
	 ('BCAG','Part Time','DR','BCAG.DR.PART-TIME.0000000000000000000000',true,'2025-01-29 09:07:18.025','2025-01-29 09:07:18.025',634,NULL),
	 ('SBSD','Part Time','CR','SBSD.CR.PART-TIME.0000000000000000000000',true,'2025-01-29 09:07:18.025','2025-01-29 09:07:18.025',634,NULL),
	 ('SBSD','Part Time','DR','SBSD.DR.PART-TIME.0000000000000000000000',true,'2025-01-29 09:07:18.025','2025-01-29 09:07:18.025',634,NULL),
	 ('BCAG','Full Time','CR','BCAG.CR.FULL-TIME.0000000000000000000000',true,'2025-01-29 09:07:18.025','2025-02-05 18:14:21.193',634,NULL),
	 ('BCAG','Full Time','DR','BCAG.DR.FULL-TIME.0000000000000000000000',true,'2025-01-29 09:07:18.025','2025-02-05 18:14:21.193',634,NULL);

System user from each environment must be changed.

select * from sims.users where last_name = 'system-user'