03-Excel integration.pptx

1、Excel IntegrationQAD Enterprise Edition,Advanced FinancialsLearn how to use Excel integration for-Loading and updating static data-Loading transaction dataObjectivesMC-1.4-1-XLS-020Static dataGL Accounts(25.3.13.5)Business Relations(36.1.4.3.5)Transactional dataJournal entries(25.13.1.6)OverviewMC-1

2、.4-1-XLS-030Load-Load existing data to the Excel Integration gridExport to Excel for maintenance-Create Excel file(template)System View System Codes(36.24.3.3)-Display database field nameExcel Integration UsesMC-1.4-1-XLS-040GL Account Excel Integration(25.3.13.5)Right-click in grid-Load Accounts-Ex

3、port to Excel for maintenance-Import from Excel Chart of AccountsMC-1.4-1-XLS-050Business Relation Excel Integration(36.1.4.3.5)Right-click in grid-Load business relations-Export to Excel for maintenance-Import from Excel Business RelationsMC-1.4-1-XLS-060Customer Excel Integration(27.20.1.5)Right-c

4、lick in grid-Load Customers-Export to Excel for maintenance-Import from Excel Customer DataMC-1.4-1-XLS-070Supplier Excel Integration(28.20.1.5)Right-click in grid-Load Suppliers-Export to Excel for maintenance-Import from ExcelSupplier DataMC-1.4-1-XLS-080Up to QAD 2009.1 EE-Upload new bank numbers

5、 only-Unsuitable for mass updates(no Load Data option)From QAD 2010 EE-New Load Data option-Can be used for mass updatesBank NumbersMC-1.4-1-XLS-090MC-1.4-1-XLS-100Load Bank NumbersIndicate whether a modified record loaded using Bank Number Excel Integration-Updates an existing record-Creates a new

6、bank number recordFor loaded data,the field is selected if the bank number is not yet used in a payment-Otherwise,the field is blank Existing Record Field in Excel GridMC-1.4-1-XLS-110 For newly imported data,the field is blank,and the system creates a new bank numberExisting Record Field in Excel G

7、ridMC-1.4-1-XLS-120Export a template from Incoming Bank File Excel Integration(31.1.12)Complete the template with bank file dataImport to Incoming Bank File Excel IntegrationSave the file and process it in Process Incoming Bank Files(31.1.6)Incoming Bank FilesMC-1.4-1-XLS-121Customer(27.1.10)/Suppli

8、er(28.1.15)Transfer accountCalculate BC/SCInvoice type-Adjustment,Credit note,Credit note correction,Invoice,Invoice CorrectionCustomers:Ship-to/Ship-FromSuppliers:Invoice Status Code-Allocation status:any or allocatedOpening Balance CreateMC-1.4-1-XLS-130JE Excel Integration is divided into-Journal

9、 Entry Excel Integration-Journal Entry Cross-Company Excel Integration-Journal Entry Excel Integration RepairJournal EntriesMC-1.4-1-XLS-140Journal Entry Excel Integration(25.13.1.6)Right-click in grid-Export to Excel for maintenance-Import from Excel Journal Entry Excel IntegrationMC-1.4-1-XLS-150J

10、E Cross-Company Excel IntegrationMC-1.4-1-XLS-152Journal Entry Excel Cross Co Excel Integration(25.13.1.10)Right-click in grid-Export to Excel for maintenance-Export Unposted to Excel for Maintenance-Import from Excel MC-1.4-1-XLS-Entity-Level Child Posting Entity 10USACOLevel 3 -Posting LineAC 1000

11、0012DR 12,000Level 3 -Posting LineAC 100400CR 12,000Entity-Level Child Posting Entity 11CANCOLevel 3 -Posting LineAC 410001DR 12,000Level 3 -Posting LineAC 4100021CR 12,000Level 1 Overall Posting DetailsDesigned for use by system administrators and senior staffLets you bypass some of the validations

12、 of standard journal entryDesigned for repair activitiesUsage ExamplesManually adjusting rounding differences on inventory control accountsManually adjusting errors in initial data loadManually adjusting historical AR and AP balances at initial data loadJE Excel Integration RepairMC-1.4-1-XLS-160NOT

13、 for use inDay-to-day accounting activitiesCorrecting discrepancies between GL and AR/AP-This requires intervention by QAD Support!JE Excel Integration RepairMC-1.4-1-XLS-170Bulk Data Load-Import multiple csv files into the system at once-Useful when setting up an implementationBulk Data LoadMC-1.4-1-XLS-140Hands-on ExerciseMC-1.4-1-XLS-180