Integration – Invoice Cloud

Version 6.0

Overview

Governs’ Invoice Cloud integration provides an invoice presentation / payment solution using Invoice Cloud. With Invoice Cloud integration Govern users have 24/7 access to on-line bills and flexible payment options that include all major credit/debit cards and eChecks.
Invoice Cloud consists of 2 major areas. Batch Functions that can be scheduled to send Billing Information reports to Invoice Cloud and the AR & CC Cash Collection Service.

Requirement

Integration needed with invoice cloud. We need to make a new service that:
1. Allows Payments
2. Allows Payment Reversal
3. Update Paperless status for Mailing Index
4. GetArBalances
See AR/CC Web Service

The following actions are also needed:
1. Generate a Summary Account Balance and send it to an FTP
2. Generate a Detail Account Balance (bill) and send it to an FTP

Tax & UB Bill

Tax and Utility Billing PDF Bill

Account Balance Extract

Account Balance Extract is created and push to the FTP.
Work Order for Code: WALGOL1202
Crystal reports that show the balance for each NA_ID

Batch Processes

Consists of 2 batch process that generate reports in CSV format.

1) Batch process – Invoice Cloud Balance :
UI : ComboBox SubSystems (only RE, PP, UB)
Nb. Activity days Add to the report all entries in AR_Master having activities (all activities in AR_DETAIL except payment from InvoiceCloud) in the X last days of activity.

2) Batch Process – Invoice Cloud Invoice :
. UI : ComboBox SubSystems (only RE, PP, UB) ComboBox SubSystems (only RE, PP, UB)

a) subsystems : RE, PP :
UI : Year, Installment
File Name to generate : XXXXXX_RE_TimeStamp.csv or XXXXXX_PP_TimeStamp.csv

b) Subsystem : UB :
UI : ComboBox : Posting ID – Posting Date (From table UB_POST_MASTER)
File Name to generate : XXXXXX_UB_TimeStamp.csv

The Total Billed should be the Sum of Amounts from AR_DETAIL where type = ‘INV’ and having Creation_Date = Posting_Date

Supporting Documents

Click on the links below to view the .PDF document.

Right-click on the links below to down the view the .PDF document.

To download the Excel files below, either right click on the link and select “Save Link As…” from the menu, or click directly and select the option to open in Microsoft Excel.

To download the .CSV files below, right click on the link and select “Save Link As…” from the menu. The file may then be opened in an application like Microsoft Excel.

SQL for the INVOICE

SELECT "AR_MASTER"."SUB_SYSTEM", "AR_MASTER"."YEAR_ID", "AR_SUMMARY"."INSTALLMENT", "AR_SUMMARY"."AR_ID", "NA_MAILING_INDEX"."NA_ID", "NA_NAMES"."FREE_LINE_1", "NA_NAMES"."STREET", "NA_NAMES"."CIVIC", "NA_NAMES"."PRE_DIR", "NA_NAMES"."S_SUFFIX", "NA_NAMES"."POST_DIR", "NA_NAMES"."SEC_ADD_IND", "NA_NAMES"."UNIT", "NA_NAMES"."CITY", "NA_NAMES"."STATE", "NA_NAMES"."ADD_LINE_A", "NA_NAMES"."ZIP_POSTAL", "NA_NAMES"."EMAIL", "NA_NAMES"."NAME_2", "AR_MASTER"."SORT_KEY", "AR_SUMMARY"."PRINCIPAL", "NA_MAILING_INDEX"."SUB_SYSTEM", "NA_MAILING_INDEX"."PRIMARY_INDEX"
FROM (("Waltham"."dbo"."AR_MASTER" "AR_MASTER" INNER JOIN "Waltham"."dbo"."AR_SUMMARY" "AR_SUMMARY" ON "AR_MASTER"."AR_ID"="AR_SUMMARY"."AR_ID") LEFT OUTER JOIN "Waltham"."dbo"."NA_MAILING_INDEX" "NA_MAILING_INDEX" ON ("AR_MASTER"."P_ID"="NA_MAILING_INDEX"."REF_ID") AND ("AR_MASTER"."SUB_SYSTEM"="NA_MAILING_INDEX"."SUB_SYSTEM")) LEFT OUTER JOIN "Waltham"."dbo"."NA_NAMES" "NA_NAMES" ON "NA_MAILING_INDEX"."NA_ID"="NA_NAMES"."NA_ID"
WHERE "NA_MAILING_INDEX"."PRIMARY_INDEX"=-1 AND "NA_MAILING_INDEX"."SUB_SYSTEM"='UB'
ORDER BY "AR_SUMMARY"."AR_ID"

SQL for the BALANCE

SELECT "AR_MASTER"."SUB_SYSTEM", "AR_MASTER"."YEAR_ID", "AR_SUMMARY"."INSTALLMENT", "AR_MASTER"."AR_ID", "AR_SUMMARY"."AR_ID", "NA_MAILING_INDEX"."NA_ID", "NA_MAILING_INDEX_1"."NA_ID", "AR_SUMMARY"."BALANCE", "NA_MAILING_INDEX"."SUB_SYSTEM"
FROM (("Waltham"."dbo"."AR_MASTER" "AR_MASTER" INNER JOIN "Waltham"."dbo"."AR_SUMMARY" "AR_SUMMARY" ON "AR_MASTER"."AR_ID"="AR_SUMMARY"."AR_ID") LEFT OUTER JOIN "Waltham"."dbo"."NA_MAILING_INDEX" "NA_MAILING_INDEX" ON ("AR_MASTER"."P_ID"="NA_MAILING_INDEX"."REF_ID") AND ("AR_MASTER"."SUB_SYSTEM"="NA_MAILING_INDEX"."SUB_SYSTEM")) LEFT OUTER JOIN "Waltham"."dbo"."NA_MAILING_INDEX" "NA_MAILING_INDEX_1" ON ("AR_MASTER"."UB_ID"="NA_MAILING_INDEX_1"."REF_ID") AND ("AR_MASTER"."SUB_SYSTEM"="NA_MAILING_INDEX_1"."SUB_SYSTEM")
WHERE "NA_MAILING_INDEX"."SUB_SYSTEM"='RE' AND "AR_MASTER"."YEAR_ID"=2012 ORDER BY "AR_MASTER"."AR_ID", "AR_SUMMARY"."INSTALLMENT"

Installation of Web Services

Invoice Cloud Web Service (Release 5.1)

To install the following are the installation steps for the Invoice Cloud Web Service:

Deploy the Release 5.0.9 of the Resources Files.
Install and configure the WCF Accounts Receivable API [Filename: WCFAccountsReceivableAPI.zip].
Modify the web.config file to use the GoverNetConfig.xml of the deployment [Location: In the root of the specific deployment folder]
When installing Release 5.1 of the web service, administrators will need to manually create the following table in (Table: NA_NAMES: WEBPROFILEACCESSRT (Smallint, Allows Null). This field was added to provide support for the eGov and is used in the business rules of the Names.

Verification of the Service

The WcfTestClient.exe application is used to test the web service. This application can be obtained on the Govern FTP site or from Govern Technical Support: WCF Test Application.zip. For additional details regarding testing Govern Web Services

What’s New!

Release 6.0
The two (2) Govern batch processes that are associated with the Invoice Cloud process for Real Property and Personal Property modules have been modified to allow them to identify required InvoiceTypeID’s that match with AR Cycle codes for Real Property (RE) and Personal Property (PP). – Release 1703.06

Prior to release 6.0 of the Govern Web service, manual changes were required to be made during the installation of the Invoice Cloud Web Service:

Modify the web.config to use the GovernNetConfig.xml file located in the deployment root directory.
Create (Table: NA_NAMES.WEBPROFILEACCESSRT), with properties set to (smallint, allow NULLS).

The above steps are no longer required, they are automatically created when the Database Verification process is run in the Govern New Administration (GNA).