General System Design
MFASISVENDOR2: Vendor2 File Outbound Interface
Document Review and Change SummaryVersion/
Review Date / Brief Summary of Changes
From Prior Version / Document
Reviewers
Version 1.0
10/26/2007 / § Original (Copied from Vendor file specification) / Interface Team
Table of Contents
1. Overview 3
1.1. Document Definitions and Naming Conventions 3
1.2. Document Usage 4
1.3. Logical Document Component Structure 5
2. Publishable Specification 6
2.1. Document Prefixes 6
2.2. Vendor File Repository on FTP Server 7
2.3. Vendor File Availability 9
2.4. MFASIS 2.X Short Vendor File mapped to Advantage 3.x Vendor File 9
2.5. Map Vendor File from R_VEND_CUST and R_VEND_CNTAC Tables 11
2.6. Sample Query Extract 13
2.7. Selection Criteria 14
2.8. Build Approach 14
2.9. Unit Testing Approach 15
2.10. System Testing Approach 15
2.11. Issues/Questions 16
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.
o VarChar – Variable Character text. Alphanumeric data.
o Byte – Unsigned character.
o Char – Character. Fixed length alphanumeric text, required to meet the exact specified size.
o Date – Date Format YYYY-MM-DD
o Decimal – Numeric value with a specified decimal position (9,3) = 999999999.999
o Boolean –True/False. True conditions = “true”, False conditions = “false”.
o Memo – Alphanumeric Text with a maximum of 1500 characters.
o Currency – Numeric, with two decimal positions. Format 9999.99.
o Integer – Numeric value, a whole number.
o 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.
o 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 generate payments, originating in their own, independent, external systems. These systems currently interface PV (Payment Voucher) documents to MFASIS. The new Advantage 3.x system equivalent of PV documents are referred to as PRC and GAX documents.
Agencies which submit PRC and GAX payment documents through an interface will receive access to a Vendor File in the Advantage 3.x system. This fixed length ASCII file can be used to reconcile Vendor data between Advantage 3.x and the external systems. The Vendor file will be updated daily during the nightly batch cycle.
A list of agencies eligible to receive access to the Vendor File is specified below in the Document Prefixes section.
The MFASIS file most commonly used by external agencies was PWA.AFN1S.USER.VENDOR.SHORT (referred to as the “short Vendor file”. It contained 12 data elements from the Vendor database and held approximately 250,000 records. A few agency partners received complete extracts of the MFASIS vendor file, with every field for every record. For consistency as well as manageability, the new Vendor File will map to the “short Vendor file”.
1.3. Logical Document Component Structure
The Vendor File consists of an ASCII text file generated from the Advantage 3.x Vendor/Customer tables (collectively referred to as VCUST). The Vendor/Customer application is much 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 Vendor File extract, we will concentrate on the Vendor Customer and Vendor Contact 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.
Miscellaneous vendor codes that existed in MFASIS 2.x will be converted to the new Advantage 3.x system. Any miscellaneous vendors created in the new Advantage 3.x system will be assigned a random Vendor code.
The vendor file will be sequenced by Vendor Code. It is up to the interface partners to re-sequence the file to their specifications.
2. Publishable Specification
2.1. Document Prefixes
The following interface partners, which submit payment files, have been identified as having access to the Vendor File. In order to identify the interfaces by agency and type, the following codes have been assigned.
interface partner / Dept Code / INTERFACE DOC PREFIX / INTERFACE CREATOR ID / FTP USER ID (*=outside firewall) / EXTERNAL INTERFACE / TYPE OF MFASIS INTERFACE /COURTS / 40A / IND / INTFIND / FTPCOURT / IN / Indigent Defense / PV type 10
COURTS / 40A / WIT / INTFWIT / FTPCOURT / IN / Witness Fees / PV type 8
COURTS / 40A / JUR / INTFJUR / FTPCOURT / IN / Juror Payments / PV type Q
DFPS/CIMS / 18K / WCC / INTFWCC / FTPWCC / IN / WCC Workers Comp / PV type 11
DFPS/CIMS / 18K / WCM / INTFWCM / FTPWCC / IN / WCC Workers Comp Medical / PV type 12
DHHS/ACES / 10A / ASP / INTFASP / FTPASPIRE / IN / Aspire Payments / PV type F
DHHS/ACES / 10A / SSI / INTFSSI / FTPASPIRE / IN / SSI / Personal Needs / PV type R
DHHS/ACES / 10A / NWG / INTFNEWG / FTPASPIRE / IN / New Grants (Retro TANF) / PV type S
DHHS/ACES / 10A / TNF / INTFTNF / FTPASPIRE / IN / TANF (Public Assistance) / PV type U
DHHS/DDS / 10A / DDS / INTFDDS / FTPDDS / IN / DDS Development Disability Services / PV type 5
DHHS/MACWIS / 10A / CHW / INTFCHW / FTPDHHS2 / IN / Child Welfare Payments / PV type G
DHHS/MACWIS / 10A / MBL / INTFMBL / FTPDHHS2 / IN / Misc Bills and Christmas Payments / PV type I
DHHS/MACWIS / 10A / CON / INTFCON / FTPDHHS2 / IN / DHHS Contract Payments / PV type T
DHHS/MECMS / 10A / MCS / INTFMCS / FTPMECMS / IN / Maine Care (Medicaid) Payments / PV type P
DHHS/MECMS / 10A / MEP / INTFMEP / FTPMECMS / IN / Pharmacy Payments / PV type 13
DHHS/NECSES / 10A / NEC / INTFNEC / FTPNECSES / IN / DHHS Child Support (NECSES) / PV type J
EDUCATION / 05A / ECN / INTFECN / FTPEDUC / IN / Education Construction Payments. / PV type A
EDUCATION / 05A / SPE / INTFSPE / FTPEDUC / IN / Local Entitlement Special Ed. / PV type C
EDUCATION / 05A / ESB / INTFESB / FTPEDUC / IN / Education Subsidies. / PV type D
EDUCATION / 05A / ESL / INTFESL / FTPEDUC / IN / Education School Lunch Payments. / PV type E
LABOR / 12A / LRP / INTFLRP / FTPDOL / IN / Labor Rehab Payments / PV type W
LEGISLATURE / 30A / LEG / INTFLEG / FTPLEGIS* / IN / Legislature Payments / PV type 6
REVENUE SERVICES / 18F / ITR / INTFITR / FTPMRS / IN / INDIV. INCOME TAX REFUNDS / PV type B
REVENUE SERVICES / 18F / MTR / INTFMTR / FTPMRS / IN / Misc Tax Refunds / PV type H
REVENUE SERVICES / 18F / CTR / INTFCTR / FTPMRS / IN / CORP / FRAN / WH / INSUR REFUNDS (formerly Corp Tax Refunds) / PV type K
REVENUE SERVICES / 18F / GTR / INTFGTR / FTPMRS / IN / Gen'l Tax & Rent Refunds / PV type L
REVENUE SERVICES / 18F / BET / INTFBET / FTPMRS / IN / BETR Refunds / PV type M
REVENUE SERVICES / 18F / SUE / INTFSUE / FTPMRS / IN / Sales/Use/Excise Refunds / PV type N
REVENUE SERVICES / 18F / WTR / INTFWTR / FTPMRS / IN / FIDU / ESTATE REFUNDS (formerly Withholding Tax Refunds) / PV type V
TRANSPORTATION / 17A / DT1 / INTFDT1 / FTPDOT / IN / DOT Payments - commodity based (PRC) / PV type Y
TRANSPORTATION / 17A / DT2 / INTFDT2 / FTPDOT / IN / DOT Payments - non-commodity based (GAX) / PV type Y
TREASURY / 28A / UNP / INTFTUNP / FTPTREAS / IN / Unclaimed Property / PV type 14
TREASURY / 28A / RSH / INTFRSH / FTPTREAS / IN / Revenue Sharing / PV type O
2.2. Vendor File Repository on FTP Server
2.2.1 Data Repository
A central repository will exist for each agency partner’s files. Secure FTP software is now required to transfer files to the FTP server. (eg. WS-FTP Pro, Filezilla, Secure FTP by Glub Tech) WS_FTP will no longer work.
The server location is oit-isa1asmfdev.
The PRODUCTION HOST
for interface partners within the State of Maine firewall is:
Host: SOM-ISA1asMom02.SOM.W2k.state.me.us (nicknamed Mom2 or Momentum2)
Port: 21
Servertype: FTP over SSL
for interface partners outside the State of Maine firewall is:
Host: Mom01.secure.maine.gov (nicknamed Mom1 or Momentum1)
Port: 21
Servertype: FTP over SSL
For TESTING PURPOSES, use:
Host: momentum3.bis.state.me.us (nicknamed Mom3 or Momentum3)
Port: 21
Servertype: FTP over SSL
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 will have permission to access all folders.
Interface partners will PULL the Vendor Extract file once it is saved in the Vendor folder. The extract file name will contain a date (e.g. ALLVENDOR1.txt). A copy of the file is automatically archived for historical purposes.
The directory structure will look like this:
Inbout / \AccountCodesInbout / \Customer / \ALLCUSTOMER1.TXT
\ALLBILLPROFILE1.txt
Inbout / \Inbound / \INTFaaa / \AAAmmddXXX.xml
\AAAmmddXXX.inf
\INTFbbb / \BBBmmddXXX.xml
\BBBmmddXXX.inf
Inbout / \Outbound / \INTFyyy / \Extract file
\INTFzzz / \Extract file
Inbout / \Vendor / \ALLVENDOR2.TXT
\VendorXwalkforTestingmmddyyfixedLen.txt
**Agency Access and Read/Write Permissions
Inbout\Vendor \ ALLVENDOR2.txt
All interface partners submitting payment documents will have read only access to this extract.
2.3. Vendor File Availability
The Vendor File will be available at the end of the nightly batch cycle. The file will contain a complete refresh of the Vendor data as it existed at the end of the last business day.
2.4. MFASIS 2.X Short Vendor File mapped to Advantage 3.x Vendor File
The file most commonly used by agencies in MFASIS 2.0 is PWA.AFN1S.USER.VENDOR.SHORT. It contains approximately 250,000 records.
MFASIS 2.xFIELD NAME / CURRENT LENGTH / Advantage 3.x
COLUMN NAME / NEW LENGTH / COLUMNS /
VENDOR CODE / 11 / R_VEND_CUST.VEND_CUST_CD / 12 / 1 – 12
VENDOR NAME / 30 / R_VEND_CUST.LGL_NM / 60 / 13 - 72
VENDOR ADDRESS LINE1 / 30 / R_VEND_CNTAC.STR_1_NM / 75 / 73 – 147
VENDOR ADDRESS LINE2 / 30 / R_VEND_CNTAC.STR_2_NM / 75 / 148 – 222
VENDOR ADDRESS LINE3 / 30 / R_VEND_CNTAC.CITY_NM / 60 / 223 – 282
R_VEND_CNTAC.ST / 2 / 283 – 284
R_VEND_CNTAC.ZIP / 10 / 285 – 294
R_VEND_CNTAC.CTRY / 3 / 295 – 297
COMMENTS / 30 / R_VEND_CUST.HLD_REAS / 30 / 298 – 327
PAYMENT HOLD INDICATOR / 1 / R_VEND_CUST.HLD_PYMT_FL / 1 / 328 – 328
EFT STATUS / 1 / R_VEND_CUST.EFT_STA / 10 / 329 – 338
CONTACT NAME / 30 / R_VEND_CNTAC.CNTAC_NM / 60 / 339 – 398
PHONE / 12 / R_VEND_CNTAC.VOICE_PH_NO / 30 / 399 – 428
FEDERAL ID NUMBER / 14 / R_VEND_CUST.TIN / 9 / 429 – 437
LAST ACTION DATE / 08 / R_VEND_CUST.LAST_APRV_DT / 10 / 438 – 447
R_VEND_CUST.THRD_PTY_CD / 20 / 448 – 467
R_VEND_CUST.THRD_PTY_NM / 60 / 468 – 527
R_VEND_CUST.TIN_TYP / 1 / 528 – 528
R_VEND_CUST.ALIAS_NM / 60 / 529 – 588
2.5. Map Vendor File from R_VEND_CUST and R_VEND_CNTAC Tables
MFASIS 2.0 Attribute / Size / ADVANTAGE 3.6 Attribute / ADV 3.6 Type / ADV 3.6 Size / Advantage 3.6 Definition /VENDOR 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).
THIS ATTRIBUTE IS NO LONGER ASSOCIATED WITH THE TIN or SSN NUMBER
TRUNCATED TO 12 CHARACTERS
VENDOR NAME / 30 / R_VEND_CUST.LGL_NM / VarChar / 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.
VENDOR ADDRESS LINE1 / 30 / R_VEND_CNTAC.STR_1_NM / VarChar2 / 75 / The first line of the street address.
VENDOR ADDRESS LINE2 / 30 / R_VEND_CNTAC.STR_2_NM / VarChar2 / 75 / The second line of the street address.
VENDOR ADDRESS LINE3 / 30 / R_VEND_CNTAC.CITY_NM
R_VEND_CNTAC.ST
R_VEND_CNTAC.ZIP / VarChar2 / 60 + 2 + 10 / The city name associated with the address.
<appended to>
The state or province.
<appended to>
The zip code associated with the address.
R_VEND_CNTAC.CTRY / VarChar2 / 3 / The unique identification code associated with the country.
COMMENTS / 30 / R_VEND_CUST / VarChar2 / 1500 / The reason that payments are being held from a vendor/customer.
TRUNCATE TO 30 CHARACTERS
PAYMENT HOLD INDICATOR / 1 / R_VEND_CUST.HLD_PYMT_FL / Number / 3 / Indicates whether future payments should be held for this record. If the box is selected, payments to this record have been temporarily suspended. If the box is not selected, payments to this record are processed as normal. This allows payments to be suspended to a record without interrupting the processing of new orders.
Truncate to 1 Character. Value of 1 means payment is on hold.
EFT STATUS / 1 / R_VEND_CUST.EFT_STA / Number / 10 / The current status of the electronic funds transfer (EFT) processing.
Values:
0 / -N/A
1 / -Prenote Requested
2 / -Prenote Pending
3 / -Eligible for EFT
4 / -Prenote Rejected
5 / -Not Eligible for EFT
6 / -EFT Hold
CONTACT NAME / 30 / R_VEND_CNTAC.CNTAC_NM / VarChar2 / 60 / The default disbursement format to be used when generating payments to this record. Must be valid on the Disbursement Format page.
PHONE / 12 / R_VEND_CNTAC.VOICE_PH_NO / VarChar / 30 / The telephone number associated with the contact.
FEDERAL ID NUMBER / 14 / R_VEND_CUST.TIN / VarChar2 / 9 / The taxpayer identification number from the1099 Reporting Information page.
LAST ACTION DATE / 8 / R_VEND_CUST.LAST_APRV_DT / Date / 20 / Last date Approved.
Truncate to 10 characters: MM/DD/YYYY format
R_VEND_CUST.THRD_PTY_CD / VarChar / 20 / The vendor/customer identification code for the third party.
R_VEND_CUST.THRD_PTY-NM / VarChar / 60 / The legal name for the vendor/customer code displayed in the third party code field.
R_VEND_CUST.TIN_TYP / VarChar2 / 1 / The type associated with the taxpayer identification number. Valid values include: 2=SSN/ITIN/ATIN or 1=EIN.
R_VEND_CUST.ALIAS_NM / VarChar2 / 60 / The alternate name, alias or operating name (doing business as) used to identify the account.
2.6. Sample Query Extract
SELECT