General System Design

Document Review and Change Summary
Version/
Review Date / Brief Summary of Changes
From Prior Version / Document
Reviewers
Version 1.0
1/25/2006 /
  • Original
/ Interface Team
Version 1.1
2/27/2006
B. Palmer / Update section 2.2 - Removed erroneous reference to Vendor file and added physical server location.
Update section 2.4 - removed R_CUST_ACCT mappings and renumbered columns.
Update section 2.5 – Accounting fields are no longer used.
Update section 2.6 – Updated sample SQL query.
Update section 2.11 – Issues/Questions: DEP uses the Provider-Description.

MFASISCUSTOMER: Customer File Outbound Interface

Table of Contents

1.Overview

1.1.Document Definitions and Naming Conventions

1.2.Document Usage

1.3.Logical Document Component Structure

2.Publishable Specification

2.1.Document Prefixes

2.2.Customer File Repository

2.3.Customer File Availability

2.4.MFASIS 2.X Customer File mapped to Advantage 3.x Customer File

2.5.MFASIS 2.0 Customer File mapped to Advantage 3.x Vendor/Customer Files

2.6.Sample Query Extract

2.7.Selection Criteria

2.8.Build Approach

2.9.Unit Testing Approach

2.10.System Testing Approach

2.11.Issues/Questions

1.Overview

1.1.Document Definitions and Naming Conventions

This section describes/defines the DTD naming conventions applied in this document.

Transaction Layout Definitions

  • Attribute – The name of the data element in the database, as well as the name of the XML tag.
  • Caption – The label on the User Interface that defines the data element.
  • Description – A textual description of the data element.
  • R/C – Required/Conditional. Required specifies that the data element is required by the interface for the transaction to successful submit. Conditional means that specified conditions must be adhere to by the interface for the transaction to successful submit. No value specifies that the data element is optional by the interface for the transaction to submit.
  • Type – The data type of the data element.
  • VarChar – Variable Character text. Alphanumeric data.
  • Byte – Unsigned character.
  • Char – Character. Fixed length alphanumeric text, required to meet the exact specified size.
  • Date – Date Format YYYY-MM-DD
  • Decimal – Numeric value with a specified decimal position (9,3) = 999999999.999
  • Boolean –True/False. True conditions = “true”, False conditions = “false”.
  • Memo – Alphanumeric Text with a maximum of 1500 characters.
  • Currency – Numeric, with two decimal positions. Format 9999.99.
  • Integer – Numeric value, a whole number.
  • Long - Numeric value, a long whole number.
  • Size – The (numeric) size of the data element
  • MFASIS (ADV3.x) Notes – Notes specific to the Advantage 3.x (new MFASIS) data element.
  • CVL – Coded Value List, the value of the data element must come from the CVL table specified in the MFASIS (ADV3.x) Notes.
  • MFASIS (ADV2.x) Derivation Notes – Notes specific to translating MFASIS (Advantage 2.x) document layout data to Advantage 3.x.

1.2.Document Usage

Within the State of Maine financial system, departments and external agencies generatereceivables, originating in their own, independent, external systems. These systems currently interface RE documents to MFASIS.

Agencies which submit RE receivables through an interface will receive access to a Customer File in the Advantage 3.x system. This fixed lengthASCII file can be used to reconcile Customer data between Advantage 3.x and the external systems. The Customer file will be updated daily during the nightly batch cycle.

A list of agencies eligible to receiveaccess to the Customer File is specified below in the Document Prefixes section.

In the current MFASIS system, the Department of Environmental Protection receives a Customer File (EIFTPOP.MFASIS.CUSTOMER). It is a daily extract containing only agency 06A data. It extracts all data elements from the Customer (also referred to as Provider) table.

The new Customer file will contain only the same data elements found in the current Customer file.

1.3.Logical Document Component Structure

The Customer File consists of an ASCII text file generated from the Advantage 3.xVendor/Customer tables (collectively referred to as VCUST). The Vendor/Customer application is more robust than the current MFASIS Vendor and Customer tables. Multiple tables are used to store and relate vendor, customer, address, TIN, parent and master data. For the purposes of the Customer File extract, we will concentrate on the Vendor Customer, Vendor Contact and Vendor Account tables.

**NOTE** The Vendor Code will no longer be the Taxpayer Identification Number in the new system. New Vendor Codes will automatically be generated by Advantage 3.x. A Vendor Crosswalk is available for conversion from the old format to the new system. For more details, see the MFASIS UPGRADE section of the Office of the State Controller’s website.

2.Publishable Specification

2.1.Document Prefixes

The following payment interface partners have been identified as having access to the Customer File.

In order to identify the interfaces by agency and type, the following codes have been assigned.

DFPS/CIMS / 18K / BIL / INTFBIL / FTPCIMS / CIMS Billing RE's / RE type 102
ENVIRONMENTAL PROTECTION / 06A / EPA / INTFEPA / FTPDEP / DEP RE'S / RE type 103
DFPS/CIMS / 18K / RFL / INTFRFL / FTPCIMS / CTRL Fleet Leasing RE's / RE type 104
DFPS/CIMS / 18K / RFR / INTFRFR / FTPCIMS / CTRL Fleet Rental RE's / RE type 105
DFPS/CIMS / 18K / CCP / INTFCCP / FTPCIMS / Credit Card RE's Procurement / RE type 106
DFPS/CIMS / 18K / RMR / INTFRMR / FTPCIMS / Risk Management RE's / RE type 107
DFPS/CIMS / 18K / CCR / INTFCCR / FTPCIMS / CopyCenter RE's / RE type 108
DFPS/CIMS / 18K / PCR / INTFPCR / FTPCIMS / PrintCenter RE's / RE type 109
DFPS/CIMS / 18K / ANS / INTFANS / FTPCIMS / Annual Statues RE's / RE type 111
DFPS/CIMS / 18K / CWR / INTFCWR / FTPCIMS / Central Warehouse RE's / RE type 112

2.2.Customer File Repository

A central repository will exist for each agency partner. The server locationis oit-isa1asmfdev.

Security permissions will be such that only the agency partner specified can access the file structure(s) assigned to them. Note: Advantage technical personnel and OIT systems personnel also have permissions to access all folders.

Just as the agency partner can push their receivable files to their Inbound folder, they can also PULL theCustomer File as soon as it is saved in the Outbound folder assigned to agencies with Customer File access. The Customer file name will contain a date (e.g. ALL0227CUSTOMER1.TXT). A copy of the file is automatically archived for historical purposes.

**Agency Access and Read/Write Permissions

******************************************************

\Interface\Inbound\<InterfaceCreatorID>

(grant write permission at Inbound directory level, grant access permission at the <InterfaceCreatorID> directory level to each respective agency)

\Interface\Outbound\<InterfaceCreatorID>

(grant read permission at Outbound directory level, grant access permission at the <InterfaceCreatorID> directory level to each respective agency)

\Interface\Outbound\CUSTOMER (grant read permission at Outbound directory level, grant access permission at the <InterfaceCreatorID> directory level to each respective agency)

2.3.Customer File Availability

The Customer File will be available at the end of the nightly batch cycle. The file will contain a complete refresh of the Customer data as it existed at the end of the last business day. The previous file will be archived.

2.4.MFASIS 2.X Customer File mapped to Advantage 3.x Customer File

MFASIS 2.x
FIELD NAME / CURRENT LENGTH / Advantage 3.x
COLUMN NAME / NEW LENGTH / COLUMNS
PROVIDER-CODE / 11 / R_VEND_CUST.VEND_CUST_CD / 12 / 1 – 12
PROVIDER-NAME / 30 / R_VEND_CUST.LGL_NM / 60 / 13 - 72
PROV-ADDRESS-LINE1 / 30 / R_VEND_CNTAC.STR_1_NM / 75 / 73 - 147
PROV-ADDRESS-LINE2 / 30 / R_VEND_CNTAC.STR_2_NM / 75 / 148 - 222
PROV-CITY / 28 / R_VEND_CNTAC.CITY_NM / 60 / 223 – 282
PROV-STATE / 2 / R_VEND_CNTAC.ST / 2 / 283 – 284
PROV-ZIP-CODE-1 and
PROV-ZIP-CODE-2 / 5 / R_VEND_CNTAC.ZIP / 10 / 285 – 294
(new field) / R_VEND_CNTAC.CTRY / 3 / 295 – 297
MISC-PROVIDER-IND / 1 / R_VEND_CUST.MISC_ACCT_FL / 1 / 298 – 298
(new field) / 1 / R_VEND_CUST.CA_FNDG_TYP / 1 / 299 – 299
CUST-PHONE-NUMBER / 15 / R_VEND_CNTAC.VOICE_PH_NO / 30 / 300 – 329
AP-CONTACT-NAME / 30 / R_VEND_CNTAC.CNTAC_NM / 60 / 330 – 389
AP-CONTACT-PHONE / 15 / R_AD.CNTAC_PH_NO / 30 / 390 – 419
RET-MAIL-IND / 1 / R_AD.MAIL_RET_FL / 1 / 420 – 420
ORG-TYPE / 1 / R_VEND_CUST.INT_ACCT_FL / 1 / 421 – 421
BILLING-CODE / 4 / R_VEND_CUST.DFLT_BPRO / 4 / 422 – 425
FAX-PHONE-NUMBER / 15 / R_VEND_CNTAC.FAX_PH_NO / 30 / 426 - 455
INDIV-CORP-NAME-FL / 1 / R_VEND_CUST.ORG_TYP / 1 / 456 - 456
THIRD-PARTY-CODE / 11 / R_VEND_CUST.THRD_PTY_CD / 12 / 457 - 468
(new field) / R_VEND_CUST.THRD_PTY_REAS / 30 / 469 - 498

2.5.MFASIS 2.0 Customer File mapped to Advantage 3.x Vendor/Customer Files

In the current MFASIS system, the Department of Environmental Protection receives a Customer File (EIFTPOP.MFASIS.CUSTOMER). Approximately 13,000 records of which 12,000 are corporate and 1,000 are individual accounts. The MFASIS Customer file is a daily extract containing only agency 06A data. The new Advantage 3.x Customer file will contain data for all active customers, regardless of agency.

MFASIS 2.0 attribute / MFASIS Size / ADVANTAGE 3.6 Attribute / ADV 3.6 Type / ADV 3.6 Size / Advantage 3.6 Definition
PROVIDER-CODE / 11 / R_VEND_CUST.VEND_CUST_CD / VARCHAR / 20 / The unique identifier assigned to the vendor/customer. In ADVANTAGE Financial, a vendor can also be a customer, allowing you to enter information only one time when a particular contact is both a vendor (payable) and a customer (receivable). MAX FIELD LENGTH IS 12!
PROVIDER-NAME / 30 / R_VEND_CUST.LGL_NM / VARCHAR2 / 60 / The legally defined name of the company or individual represented by this record. The field is used for reporting if defined as not alias/DBA.
PROVIDER-DESCRIPTION / 30 / No longer used. See Issues/Questions section.
PROV-ADDRESS-LINE1 / 30 / R_VEND_CNTAC.STR_1_NM / VARCHAR / 75 / The first line of the street address from the billing contact info.
PROV-ADDRESS-LINE2 / 30 / R_VEND_CNTAC.STR_2_NM / VARCHAR / 75 / The second line of the street address from the billing contact info.
PROV-CITY / 28 / R_VEND_CNTAC.CITY_NM / VARCHAR / 60 / The city name associated with the address from the billing contact info.
PROV-STATE / 2 / R_VEND_CNTAC.ST / VARCHAR / 2 / The state or province from the billing contact info.
PROV-ZIP-CODE-1 / 5 / R_VEND_CNTAC.ZIP / VARCHAR / 10 / The zip code associated with the address from the billing contact info.
PROV-ZIP-CODE-2 / 4 / SEE ZIP / Zip+4 is part of ZIP (see previous attribute)
(new field) / R_VEND_CNTAC.CTRY / VARCHAR2 / 3 / The unique identification code associated with the country from the billing contact info.
MISC-PROVIDER-IND / 1 / R_VEND_CUST.MISC_ACCT_FL / NUMBER / 3 / Indicates this record is for miscellaneous use and does not represent a specific vendor or customer. Because of this, name and address information must be manually entered by users on accounting documents that use miscellaneous codes. The default for this flag on the Vendor/Customer table is unselected. If selected, the record cannot be referenced via the Web Vendor Self Service functionality. Additionally, the Internal, Third Party Only, Third Party Vendor, and Third Party Customer flags cannot be selected. The flag is often replicated down to documents to perform the edits for requiring name and address information.
Values: 0 = False, 1 = True
TRUNCATE TO 1 CHARACTER
HOLD-BILLING-IND / 1 / No longer used
PROJ-FUND-SRC-IND / 1 / No longer used
PROJ-FUND-SRC-TYPE / 1 / No longer used
(new field) / R_VEND_CUST.CA_FNDG_TYP / NUMBER / 10 / The type of cost accounting funding associated with the record. Valid values are: Federal (Non-CMIA) and Federal (CMIA)
TRUNCATE TO 1 CHARACTER
CUST-PHONE-NUMBER / 15 / R_VEND_CNTAC.VOICE_PH_NO / VARCHAR2 / 30 / The telephone number associated with the contact.
AP-CONTACT-NAME / 30 / R_VEND_CNTAC.CNTAC_NM / VARCHAR2 / 60 / Principal contact name of the person responsible for questions concerning an associated billing entity.
AP-CONTACT-PHONE / 15 / R_AD.CNTAC_PH_NO / VARCHAR2 / 30
RET-MAIL-IND / 1 / R_AD.MAIL_RET_FL / NUMBER / 3 / Indicates if mail sent to this address has been returned to sender.
Values: 0 = False, 1 = True (mail returned)
TRUNCATE TO 1 CHARACTER.
TAX-EXEMPT-IND / 1 / No longer used
CUSTOMER-STATUS / 1 / No longer used
DATE-OF-BIRTH-YR-CC / 2 / No longer used
DATE-OF-BIRTH-YR-YY / 2 / No longer used
DATE-OF-BIRTH-MNTH / 2 / No longer used
DATE-OF-BIRTH-DAY / 2 / No longer used
ENG-SPOKEN / 1 / No longer used
MARITAL-STATUS / 1 / No longer used
LIC-PERMIT-NUMB / 12 / No longer used
BANK-NAME / 30 / No longer used
BANK-PHONE / 15 / No longer used
ORG-TYPE / 1 / R_VEND_CUST.INT_ACCT_FL / NUMBER / 3 / Indicates if this account is an internal account (intergovernmental department). If selected, the miscellaneous, parent, third party vendor, third party customer or third party only flags cannot be selected.
Values: 0 = false, 1 = true (intergovernmental).
TRUNCATE TO 1 CHARACTER
SEX / 1 / No longer used
TEXT-IND / 1 / No longer used
CUST-SHORT-NAME / 12 / No longer used
ALT-CUSTOMER-CODE / 11 / No longer used
VENDOR CODE / 11 / No longer used
BILLING-CODE / 4 / R_VEND_CUST.DFLT_BPRO / VARCHAR2 / 5 / The default billing profile assigned to this record. NOTE - MULTIPLE BILLING CODES MAY EXIST. ONLY THE DEFAULT IS GIVEN HERE.
BILLED-TO-DATE / 14 / R_CUST_ACCT.TOT_ULQD_AM / NUMBER / (14,2) / No longer used
REC-TO-DATE / 14 / R_CUST_ACCT.TOT_COLL_AM / NUMBER / (14,2) / No longer used
TOT-AMT-WRITE-OFF / 14 / R_CUST_ACCT.TOT_WR_OFF / NUMBER / (14,2) / No longer used
PRIOR-YTD-BILLED / 14 / R_CUST_ACCT.PY_TOT_ULQD_AM / NUMBER / (14,2) / No longer used
PRIOR-YTD-REC / 14 / R_CUST_ACCT.PY_TOT_COLL_AM / NUMBER / (14,2) / No longer used
PRIOR-YTD-WRITE-OFF / 14 / R_CUST_ACCT.PY_TOT_AM_WR_OFF / NUMBER / (14,2) / No longer used
LAST-PAY-AMT / 14 / R_CUST_ACCT.LAST_PYMT_AM / NUMBER / (14,2) / No longer used
LAST-PAY-DATE-YR-CC / 2 / R_CUST_ACCT.LAST_PYMT_DT / DATE / 20 / No longer used
LAST-PAY-DATE-YR-YY / 2 / See LAST_PYMT_DT / No longer used
LAST-PAY-DATE-MNTH / 2 / See LAST_PYMT_DT / No longer used
LAST-PAY-DATE-DAY / 2 / See LAST_PYMT_DT / No longer used
LAST-REC-DATE-YR-CC / 2 / R_CUST_ACCT.LAST_RCVB_DT / DATE / 20 / No longer used
LAST-REC-DATE-YR-YY / 2 / See LAST_RCVB_DT / No longer used
LAST-REC-DATE-MNTH / 2 / See LAST_RCVB_DT / No longer used
LAST-REC-DATE-DAY / 2 / See LAST_RCVB_DT / No longer used
CURR-SHRT-PAY-AMT / 14 / R_CUST_ACCT.SPAY_WI_TOL / NUMBER / (14,2) / No longer used
CURR-OVER-PAY-AMT / 14 / R_CUST_ACCT.OPAY_WI_TOL / NUMBER / (14,2) / No longer used
PRIOR-SHRT-PAY-AMT / 14 / R_CUST_ACCT.PY_SPAY_WI_TOL / NUMBER / (14,2) / No longer used
PRIOR-OVER-PAY-AMT / 14 / R_CUST_ACCT.PY_OPAY_WI_TOL / NUMBER / (14,2) / No longer used
FAX-PHONE-NUMBER / 15 / R_VEND_CNTAC.FAX_PH_NO / VARCHAR2 / 30 / The fax extension number associated with a contact.
INDIV-CORP-NAME-FL / 1 / R_VEND_CUST.ORG_TYP / VARCHAR / 10 / The type of the organization. Valid values are 1 = Individual, 2 = Company.
TRUNCATE TO 1 CHARACTER
THIRD-PARTY-CODE / 11 / R_VEND_CUST.THRD_PTY_CD / VARCHAR / 20 / The vendor/customer identification code for the third party.
TRUNCATE TO 12 CHARACTERS
(new field) / R_VEND_CUST.THRD_PTY_REAS / VARCHAR2 / 1500 / The reason for the third party designation.
TRUNCATE TO 30 CHARACTERS
TOTAL 542

2.6.Sample Query Extract

SELECT

A.VEND_CUST_CD,

A.LGL_NM ,

B.STR_1_NM,

B.STR_2_NM,

B.CITY_NM,

B.ST,

B.ZIP,

B.CTRY,

A.MISC_ACCT_FL,

A.CA_FNDG_TYP,

B.VOICE_PH_NO,

B.CNTAC_NM,

C.CNTAC_PH_NO,

C.MAIL_RET_FL,

A.INT_ACCT_FL,

A.DFLT_BPRO,

B.FAX_PH_NO,

A.ORG_TYP,

A.THRD_PTY_CD,

A.THRD_PTY_REAS

FROM

O_FINCONV.R_VEND_CUST A,

O_FINCONV.R_VEND_CNTAC B,

O_FINCONV.R_AD C

WHERE A.VEND_CUST_CD = B.PNT_ACCT_CD

AND B.PNT_ACCT_CD = C.VEND_CUST_CD

AND B.CNTAC_NO = C.CNTAC_NO

AND C.AD_TYP = 'BI'

AND A.CUST_ACT_STA = 2

ORDER BY A.VEND_CUST_CD

2.7.Selection Criteria

The current MFASIS system extracts the entire Customer table. With the implementation of a more robust VCUST database, we will include only Active customers so the file does not become too cumbersome.

2.8.Build Approach

Perform a single extract from the Advantage Customertables for all active customers. Use Pervasive Data Integrator (PDI) or an SQL extract to create the file.

2.9.Unit Testing Approach

  1. The Advantage Interface team will manually run the PDI script or SQL extract to create the Customer File.
  2. The Advantage Interface team will manually FTP the Customer File to the CUSTOMER OUTBOUND folder.
  3. The Advantage Interface team will notify the interface partners that the latest CustomerFile has been created. The file can be FTP’d or emailed to the interface partners for review.

2.10.System Testing Approach

System Testing will validate that the Advantage Interface infrastructure is in place to automatically process all Inbound and Outbound interfaces.

The following must be in place:

  1. The Inbound and Outbound directory structure exists.
  2. Scripts to run the batch cycle are written.
  3. Espresso (Cybermation) Job Scheduler jobs have been defined and built.
  4. Security is in place so that interface partners can only access their specified folders.

System Testing will entail:

  1. The Job Scheduler will automatically run the nightly batch cycle jobs.
  2. The outbound Customer file will be automatically moved to the Customer Outbound folder.

2.11.Issues/Questions

  1. DEP uses the Provider-Description. It is printed on DEP invoices. This field (Division on 2.x CUST screen, also referred to as Cust Name 2) is not slated for conversion from 2.x to 3.x . DEP feels this is a showstopper.

Per Kim Hall on 2/28/06:

After doing some research, we found that the Division field from the CUST table in MFASIS does not print on the Invoices and Statements as it does today. If you would like to have the information in the Division field print on the Invoices and Statements, you will need to modify your customer information in the CUST table in MFASIS.

The Division field from MFASIS will not be converted into the new system.

The CUST fields that will print on the Invoice and Statement include: Corporate Name or Individual Last Name & First Name, Address Street 1, Address Street 2, City, State and Zip.

I am attaching an extract of your Customer data as of 01/12/06. The Division Field is listed as PROVIDER-DESCRIPTION on the spreadsheet.

This is also the perfect time for you to cleanse your Customer data.

MFASIS-CUSTOMER-GSD-02-27-2006.DOCPage 1 of 15