Minnesota State Colleges and Universities
LADE-Accounting Interface Description
Process
LADE (local application development environment) accounting interface
Purpose
To support the processing of volumes of transactions originating at MnSCU institutions from local applications. To support the consistant application of MnSCU accounting system business rules to all transactions, whatever their source.
Description
MnSCU institutions maintain their own applications to support various business functions. Some of these functions have a need to interface with MnSCU accounting to maintain information integrity. The LADE-accounting interface provides support for the
processing of batches of financial transactions generated from local applications. This interface utilizes the standard ISRS LADE process. An institution may produce financial transactions in XML format and submit them for processing on any schedule they wish. Transactions processed thru LADE are loaded to a temporary database location on a regional server and held there until the batch posting job is run shortly before midnight each day. The temporary staging area for LADE transactions is UTF_BATCH which has public select privileges.
Institution responsibility
§ Work with MnSCU technical support and business analysts to determine the proper transaction type, commit grouping, and column population to accurately record accounting data for the business function being completed.
§ Successfully complete a proper test cycle to verify the accuracy of the process. This will include tests of the complete process in development/QC environmnents and a multiple-transaction test against the production DB.
§ Produce the transactions needed to record the business function.
§ Load transactions to the pre-defined location on their local server
§ Request the LADE download. This may be scheduled or on demand.
§ Verify that posting is completed successfully in MnSCU accounting. Errors encountered should be communicated to the MnSCU help desk
§ Remove transactions from the local server location
MnSCU technical support responsibility
§ Work with institutions to create the proper accounting transaction to complete the business function
§ Provide testing environment and support
Operations responsibility
§ Receive institution’s requests for LADE download of transactions
§ Schedule and monitor LADE download job
§ Schedule and monitor daily batch accounting jobs
Details of LADE transactions
Column definitions for XML load of financial transactions follow. More complete documentation on content and usage of transaction columns is available in ISRS system documentation.
The ISRS table UTF_BATCH serves as the staging area for transactions that originate on local institutions servers and are loaded via LADE. Transactions loaded to UTF_BATCH will remain in the table permanently. The following three columns act as the primary key for UTF_BATCH and must provide a unique identifier for the submitted transaction.
RC_ID CHAR(4) . report center ID
SOURCE_BATCH_NBR CHAR(12) . Source batch number, alphanumeric
TRAN_NBR INTEGER . Transaction number in batch
Following six columns further identify the transaction and provide a mechanism for specifying processing options.
GROUP_NBR INTEGER . Grouping number for commit
0 – commit single transaction
# - commit all same # as a group
PROCESS_STATUS_CODE CHAR(1) . Process status code
S – submit
P – processed
X – do not submit
E - errored
EFF_DATE CHAR(8) . Date transaction is to be posted
ADD_USER_ID CHAR(12) . User ID (system supplied)
ADD_TIMESTAMP DATE VMS . timestamp (system supplied)
PROCESS_TIMESTAMP DATE VMS . Tran processing timestamp (auto)
The following columns are a one-to-one mapping to the ISRS financial transaction, physically represented in the database as UTF_EVENT and UTF_DETAIL. Descriptions of various transaction types and their requirements are available in ISRS accounting system documentation.
OCCURANCE_DATE CHAR(8) . Occurrence date
TRAN_TYPE_CODE CHAR(2) . Transaction type code
TRAN_DESC VARCHAR(48) . Transaction description
PO_NBR CHAR(6) . Purchase order number (1-6 digits, right)
RECEIPT_NBR CHAR(8) . Receipt number
RECEIPT_FLAG CHAR(1) . Receipt flag
INVOICE_NBR CHAR(27) . Invoice number
CUSTOMER_ID CHAR(12) . Customer ID, (student/vendor)
CHECK_PROC_DATE CHAR(8) . Date check to be processed
CHECK_DATE CHAR(8) . Date to be printed on check
PAY_STATUS_CODE CHAR(1) . Pay status code (P/F)
PV_TYPE_CODE CHAR(1) . MAPS PV type code (1-6)
SING_CHECK_FLAG CHAR(1) . Do not combine with other PV/PG
BANK_ACCT_CODE CHAR(2) . Bank account code
CHECK_NBR CHAR(8) . Check/warrant number
LINK_SERIAL_NBR CHAR(9) . Transaction serial number of link
USER_ID CHAR(12) . User ID
SOURCE_MOD_ID CHAR(2) . ISRS source module ID
INPUT_REF_NBR CHAR(16) . reference number (free format)
WAIVER_CODE CHAR(2) . A/R Waiver code
WRITE_OFF_CODE CHAR(2) . A/R Write-off code
PR_WORK_AUTH_NBR INTEGER . Stu payroll work authorization number
E1_FY CHAR(4) . entry1 fiscal year
E1_ACCT_NBR CHAR(6) . entry1 account number (CC/GL)
E1_OBJECT_CODE CHAR(4) . entry1 object code
E1_SIGN CHAR(1) . entry1 Sign (D/C)
E2_FY CHAR(4) . entry2 fiscal year
E2_ACCT_NBR CHAR(6) . entry2 account number (CC/GL)
E2_OBJECT_CODE CHAR(4) . entry2 object code
E2_SIGN CHAR(1) . entry2 sign (D/C)
AMOUNT BIGINT(2) . transaction amount (positive amt only)
UBIT_CODE CHAR(1) . UBIT code (leave blank)
ADJUST_FLAG CHAR(1) . Adjusted transaction flag
BUSN_PROC_CODE CHAR(2) . Business process code
RECV_TYPE_CODE CHAR(2) . Receivable type code
BUSN_SUBPROC_CODE CHAR(2) . Business subprocess code
THIRD_PARTY_TECH_ID CHAR(8) . Third party payer tech ID
DB_CR_INDIC SMALLINT . Debit/credit indicator (1/-1)
FA_AWARD_ID CHAR(5) . FA award Id
FA_AWARD_DISB_NBR SMALLINT . FA award disbursement number
FA_AWARD_SEQ_NBR SMALLINT . FA award sequence number
INVC_NBR CHAR(8) . A/R invoice number
INVC_TEXT VARCHAR(120) . A/R invoice text
REVN_OBJECT_CODE CHAR(4) . Revenue object code
RATE_TECH_ID CHAR(8) . AR rate ID
YRTR CHAR(5) . year/term
SALES_TAX_CODE CHAR(1) . Sales tax code (1-4)
SALES_TAX_FLAG CHAR(1) . Sales tax flag
EXT_CHRG_ID CHAR(9) . Cost Alloc external charge Id
GIFT_DONOR_TECH_ID CHAR(8) . Gift donor tech ID
ISSUE_SEQ_NBR CHAR(8) . Consum inventory issue sequence number
COMMENTS VARCHAR(100) . Comment
POPULATE_SUBTABLE_FLAG CHAR(1) . Flag to populate utf_detl_ar (Y)
Format of XML transaction file
Following is the general format of the transaction xml file. Requirements for specific transaction types vary. See the MnSCU financial accounting system documentation for the requirements for specific transaction types.
<?xml version="1.0"?>
<LADE_00000060
<PROPERTIES>
<SOURCE>LADE</SOURCE>
<FUNCTION>ACB</FUNCTION>
<INST>9999</INST>
<INST_PIN>pin</INST_PIN>
<LADE_ID>00000060</LADE_ID>
<TARGET</TARGET>
<COMP</COMP>
<OPER</OPER>
<DATE>ccyymmdd</DATE>
</PROPERTIES>
<UTF_BATCH.
<RC_ID>9999</RC_ID>
<SOURCE_BATCH_NBR>xxxxxxxxxxxx</SOURCE_BATCH_NBR>
<TRAN_NBR>999999</TRAN_NBR>
<GROUP_NBR>0</GROUP_NBR>
<PROCESS_STATUS_CODE>x</PROCESS_STATUS_CODE
<EFF_DATE>mm/dd/ccyy</EFF_DATE>
<ADD_USER_ID</ADD_USER_ID> . do not include
ADD_TIMESTAMP</ADD_TIMESTAMP> . do not include
PROCESS_TIMESTAMP</PROCESS_TIMESTAMP> . do not include
OCCURANCE_DATEmm/dd/ccyy</OCCURANCE_DATE>
TRAN_TYPE_CODExx</TRAN_TYPE_CODE>
<TRAN_DESC>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx</TRAN_DESC>
<PO_NBR>999999</PO_NBR>
RECEIPT_NBR99999999</RECEIPT_NBR>
RECEIPT_FLAGx</RECEIPT_FLAG>
<INVOICE_NBR>xxxxxxxxxxxxxxxxxxxxxxxxxxx</INVOICE_NBR>
CUSTOMER_ID999999999999</CUSTOMER_ID>
CHECK_PROC_DATEccyymmdd</CHECK_PROC_DATE>
<CHECK_DATE>ccyymmdd</CHECK_DATE>
PAY_STATUS_CODEx</PAY_STATUS_CODE>
<PV_TYPE_CODE>9</PV_TYPE_CODE>
SING_CHECK_FLAGx</SING_CHECK_FLAG>
<BANK_ACCT_CODE>99</BANK_ACCT_CODE>
<CHECK_NBR>99999999</CHECK_NBR>
LINK_SERIAL_NBR999999999</LINK_SERIAL_NBR>
USER_IDxxxxxxxxxxxx</USER_ID>
SOURCE_MOD_ID>xx</SOURCE_MOD_ID>
INPUT_REF_NBRxxxxxxxxxxxxxxxx</INPUT_REF_NBR>
WAIVER_CODE99</WAIVER_CODE>
WRITE_OFF_CODE99</WRITE_OFF_CODE>
PR_WORK_AUTH_NBR99999999</PR_WORK_AUTH_NBR>
E1_FY9999</E1_FY>
E1_ACCT_NBR999999</E1_ACCT_NBR>
E1_OBJECT_CODE9999</E1_OBJECT_CODE>
E1_SIGNx</E1_SIGN>
E2_FY9999</E2_FY>
E2_ACCT_NBR999999</E2_ACCT_NBR>
E2_OBJECT_CODE9999</E2_OBJECT_CODE>
E2_SIGN>x</E2_SIGN>
AMOUNT999999999999999999</AMOUNT>
UBIT_CODE9</UBIT_CODE>
ADJUST_FLAGx</ADJUST_FLAG>
BUSN_PROC_CODExx</BUSN_PROC_CODE>
RECV_TYPE_CODExx</RECV_TYPE_CODE>
BUSN_SUBPROC_CODExx</BUSN_SUBPROC_CODE>
THIRD_PARTY_TECH_ID99999999</THIRD_PARTY_TECH_ID>
DB_CR_INDIC99</DB_CR_INDIC>
FA_AWARD_ID99999</FA_AWARD_ID>
FA_AWARD_DISB_NBR99999</FA_AWARD_DISB_NBR>
FA_AWARD_SEQ_NBR99999</FA_AWARD_SEQ_NBR>
INVC_NBR99999999</INVC_NBR>
INVC_TEXTxxxxxxxx(120)xxxxxxxx</INVC_TEXT>
REVN_OBJECT_CODE9999</REVN_OBJECT_CODE>
RATE_TECH_ID99999999</RATE_TECH_ID>
YRTR99999</YRTR>
SALES_TAX_CODE9</SALES_TAX_CODE>
SALES_TAX_FLAGx</SALES_TAX_FLAG>
EXT_CHRG_ID99999999</EXT_CHRG_ID>
GIFT_DONOR_TECH_ID99999999</GIFT_DONOR_TECH_ID>
ISSUE_SEQ_NBR99999999</ISSUE_SEQ_NBR>
COMMENTSxxxxxxxx(100)xxxxxxxx</COMMENTS>
POPULATE_SUBTABLE_FLAGx</POPULATE_SUBTABLE_FLAG>
</UTF_BATCH.
</LADE_00000060
The general layout of a multiple-transaction file is as follows:
<?xml version="1.0"?>
<LADE_00000060
<PROPERTIES>
- - header data (see above) - -
</PROPERTIES>
<UTF_BATCH.
- - transaction data (see above) - -
</UTF_BATCH.
<UTF_BATCH.
- - transaction data - -
</UTF_BATCH.
</LADE_00000060
Page 3 of 5