Introduction

This document is intended to capture the business requirements associated with the handling of Qbyte Financial to Agresso Interface. This document will be used as the basis for developing enhancements to the Qbyte Financial 11.4 application.

Database

Seed Data

New System Defaults 16 Miscellaneous Interfaces > Line Items to External Systems ACCT_PER_START_DATE. User must enter the accounting period in YY/MM format. VOUCHER_TYPES_TO_EXT_1. User must enter a valid voucher type code. Multiple vouchers can be added by adding a new default value and incrementing the extension

Table

New Table in QBYTE schema LINE_ITEM_EXPORTS with corresponding AUDIT_LINE_ITEM_EXPORTS in the QBYTE_AUDIT schema

COLUMN NAME	NULL	TYPE	DESCRIPTION

COLUMN NAME	NULL	TYPE	DESCRIPTION
LI_ID	N	NUMBER(10)	A unique, system-generated sequential number to identify the line item.
PROCESSED FLAG	N	VARCHAR2(1)	A flag indicating whether the line item has been interfaced to the ERP, successfully or unsuccessfully. Default value ‘N’. Valid values ‘N’,’Y’
CREATE_USER	N	VARCHAR2(30	User ID that created the record.
CREATE_DATE	N	DATE	Date the record was created.
LAST_UPDATE_USER	N	VARCHAR2(30)	User ID that last updated the record.
LAST_UPDATE_DATE	N	DATE
BATCH_NUM	Y	VARCHAR2(20)	The batch number of the interface to the ERP (optional)
EXPORT_DATE	Y	DATE	The date the line item was interfaced to the ERP
ERROR_CODE	Y	VARCHAR2(10)	The error code for the line item if it fails ERP validation
ERROR_DESCRIPTION	Y	VARCHAR2(100)	The error description for the line item if it fails ERP validation
LAST_UPDATE_AUDIT_ID	Y	NUMBER(10,0)	NUMBER(10,0) Last update audit ID

Views

LINE_ITEMS_FOR_EXPORT

View shows transactions based on the accounting period start date and voucher type defined in the system defaults where the processed flag is not “Y”. The view includes error codes that have been set in the line_items_export table by the external system using the API provided.

COLUMN NAME	SOURCE TABLE.COLUMN

COLUMN NAME	SOURCE TABLE.COLUMN
ORGANIZATION_ID	VOUCHERS.ORG_ID
VOUCHER_TYPE_CODE	VOUCHERS.VOUCHER_TYPE_CODE
VOUCHER_NUMBER	VOUCHERS.VOUCHER_NUM
ACCOUNTING_PERIOD	VOUCHERS.ACCT_PER_DATE
VOUCHER_CURRENCY	VOUCHERS.CURR_CODE
VOUCHER_DESCRIPTION	VOUCHERS.VOUCHER_REM
LINE_ITEM_ID	LINE_ITEMS.LI_ID
ACCOUNT_MAJOR_ID	LINE_ITEMS.MAJOR_ACCT
ACCOUNT_MINOR_ID	LINE_ITEMS.MINOR_ACCT
OPERATING_AMOUNT	LINE_ITEMS.LI_AMT
GST_AMOUNT	LINE_ITEMS.GST_AMT
VOLUME	LINE_ITEMS.LI_VOL
SOURCE_AMOUNT	LINE_ITEMS.REPORTING_CURR_AMT
REPORTING_AMOUNT	LINE_ITEMS.ORG_REP_CURR_AMT
GROSS_UP_AMOUNT	LINE_ITEMS.GROSS_UP_AMT
GROSS_UP_VOLUME	LINE_ITEMS.GROSS_UP_VOL
GROSS_UP_REPORTING_AMOUNT	LINE_ITEMS.ORG_REP_CURR_GROSS_UP_AMT
ACTIVITY_PERIOD	LINE_ITEMS.ACTVY_PER_DATE
AFE_ID	LINE_ITEMS.AFE_NUM
COST_CENTRE_ID	LINE_ITEMS.CC_NUM
LINE_ITEM_DESCRIPTION	LINE_ITEMS.LI_REM
INVOICE_BA_ID	INVOICES.CLIENT_ID
INVOICE_BA_NAME	BUSINESS_ASSOCIATES.NAME1
ALTERNATE_BA_NAME	BUSINESS_ASSOCIATES.ALTERNATE_ID
INVOICE_NUMBER	INVOICES.INVC_NUM
INVOICE_DATE	INVOICES.INVC_DATE
INVOICE_DUE_DATE	INVOICES.DUE_DATE
PAYMENT_NUMBER	CASH_TRANSACTIONS.CLIENT_ISSUED_NUM
PAYMENT_DATE	CASH_TRANSACTIONS.CLIENT_ISSUED_DATE
BANK_ACCOUNT_ID	CASH_TRANSACTIONS.BANK_ACCT_ID
ERROR_EXPORT_DATE	LINE_ITEM_EXPORTS.EXPORT_DATE
ERROR_CODE	LINE_ITEM_EXPORTS.ERROR_CODE
ERROR_DESCRIPTION	LINE_ITEM_EXPORTS.ERROR_DESCRIPTION

LINE_ITEMS_EXPORTED

View shows transactions based on the accounting period start date and voucher type defined in the system defaults where the processed flag is “Y” indicating successfully exported line items.

COLUMN NAME	SOURCE TABLE.COLUMN

COLUMN NAME	SOURCE TABLE.COLUMN
ORGANIZATION_ID	VOUCHERS.ORG_ID
VOUCHER_TYPE_CODE	VOUCHERS.VOUCHER_TYPE_CODE
VOUCHER_NUMBER	VOUCHERS.VOUCHER_NUM
ACCOUNTING_PERIOD	VOUCHERS.ACCT_PER_DATE
VOUCHER_CURRENCY	VOUCHERS.CURR_CODE
VOUCHER_DESCRIPTION	VOUCHERS.VOUCHER_REM
LINE_ITEM_ID	LINE_ITEMS.LI_ID
ACCOUNT_MAJOR_ID	LINE_ITEMS.MAJOR_ACCT
ACCOUNT_MINOR_ID	LINE_ITEMS.MINOR_ACCT
OPERATING_AMOUNT	LINE_ITEMS.LI_AMT
GST_AMOUNT	LINE_ITEMS.GST_AMT
VOLUME	LINE_ITEMS.LI_VOL
SOURCE_AMOUNT	LINE_ITEMS.REPORTING_CURR_AMT
REPORTING_AMOUNT	LINE_ITEMS.ORG_REP_CURR_AMT
GROSS_UP_AMOUNT	LINE_ITEMS.GROSS_UP_AMT
GROSS_UP_VOLUME	LINE_ITEMS.GROSS_UP_VOL
GROSS_UP_REPORTING_AMOUNT	LINE_ITEMS.ORG_REP_CURR_GROSS_UP_AMT
ACTIVITY_PERIOD	LINE_ITEMS.ACTVY_PER_DATE
AFE_ID	LINE_ITEMS.AFE_NUM
COST_CENTRE_ID	LINE_ITEMS.CC_NUM
LINE_ITEM_DESCRIPTION	LINE_ITEMS.LI_REM
INVOICE_BA_ID	INVOICES.CLIENT_ID
INVOICE_BA_NAME	BUSINESS_ASSOCIATES.NAME1
ALTERNATE_BA_ID	BUSINESS_ASSOCIATES.ALTERNATE_ID
INVOICE_NUMBER	INVOICES.INVC_NUM
INVOICE_DATE	INVOICES.INVC_DATE
INVOICE_DUE_DATE	INVOICES.DUE_DATE
PAYMENT_NUMBER	CASH_TRANSACTIONS.CLIENT_ISSUED_NUM
PAYMENT_DATE	CASH_TRANSACTIONS.CLIENT_ISSUED_DATE
BANK_ACCOUNT_ID	CASH_TRANSACTIONS.BANK_ACCT_ID
EXPORT_DATE	LINE_ITEM_EXPORTS.EXPORT_DATE

API

New API provided to update the LINE_ITEM_EXPORTS table with information from the external system to indicate error codes and when the line items have been processed. See the API Reference Guide Section 33 for details on this new Transaction Export API

Configuration

Menu Path: Go To Appl Utilities > Maintain Sub-Systems

1. Set Sub-System MISCELLANEOUS INTERFACES to Active.

2. Set Sub Sys Component 222 LINE ITEMS TO EXTERNAL SYSTEMS to Active.

3. Set Default Values for the accounting period start date.

ACCT_PER_START_DATE (YY/MM) is set to the initial accounting period used to select transactions to export.

4. Set the Default Value for the voucher types to include for the transactions included in the export.

IFS Qbyte Financial

Qbyte Financial Management Version 11.4.1.4 Enhancement Summary