Docstoc

SPECIFICATION FOR SUPPLIER IVOICE IMPORT INTO SYSPRO 6.0

Document Sample
SPECIFICATION FOR SUPPLIER IVOICE IMPORT INTO SYSPRO 6.0 Powered By Docstoc
					                   SPECIFICATION
                        FOR
               SUPPLIER IVOICE IMPORT
                  INTO SYSPRO 6.0




                        Prepared by:        Source IT
                        Specification no:   GB001
                        Revision level:     2
                        Date:               25 September 2003




Page 1 of 12
                                                       Specification: GB001/2
Supplier Invoice Import into SYSPRO 6.0                  25 September 2003



                                     CONTENTS


                                                Page

Overview                                         3


Details
   Creditor Invoice Import                       4
   Audit Report                                  8


File Layouts                                     10


Appendix B (Audit reports)                       12




Page 2 of 12
                                                                    Specification: GB001/2
Supplier Invoice Import into SYSPRO 6.0                               25 September 2003



                                     OVERVIEW

This document serves to outline the requirements for Supplier Invoice imports into
SYSPRO 6.0 for CLIENT

CLIENT need to import invoices into the Supplier module from a 3rd party product.
The relevant file will be in a .CSV format and will hold all the relevant information
required for the successful creation of a Supplier Invoice.
This program will create invoices for all supplier codes provided in the .CSV file
accordingly. The Supplier codes need to reside in SYSPRO and all data will be
validated prior to the import. The validation will also make sure that all mandatory
fields exist in the .CSV file and that any dates are in the correct format.
An audit of all successful/unsuccessful files will be created. The associated audit
report that will be date driven and may be printed at any time to display this
information.




Page 3 of 12
                                                                       Specification: GB001/2
Supplier Invoice Import into SYSPRO 6.0                                  25 September 2003


                                          DETAILS

1. Creditor Invoice Import

    The following sub directories need to exist under the SYSPRO DATA
    directory. If they do not exist, the program will automatically create them.
        IMPORT - The files waiting to be imported will reside here.
        DONE        - Files that were successfully imported will be moved here by
                        the program
        ERROR - Files with validation errors will be moved to this directory.

    The ASCII .CSV files should have the following naming convention:
       SUPIMP.SEQ
          Where:
          SUPIMP - Always the same name
          SEQ        - A unique sequence number assigned to each new file
                        starting at 001 and incrementing by 1.

    The program will store the last sequence number processed in an ASCII file in
    the IMPORT directory. If this file is missing the program will assume that it is
    processing file number 001. This file will be called GBFCTL. The user may not
    skip sequence numbers as the program will always look for a file with the next
    valid number. The System Administrator may modify the sequence number in
    this file to allow a file to be processed again or to skip a sequence number
    completely.
    It will be the System Administrator’s responsibility to clean up these files from
    the various sub directories.

    The .CSV must have the following fields separated by a comma in the
    sequence specified by the table below:

Field                                Type       Comment
Supplier code                        X(7)       Mandatory.
                                                Supplier code must exist in SYSPRO
Invoice date                         9(6)       Mandatory
                                                Date in the format of DDMMYY
Supplier Invoice number              X(20)      Mandatory.
                                                Must be unique for this Supplier.
                                                Waybill number on spreadsheet
Debtor Invoice number                X(6)       Mandatory
                                                This invoice number will be matched to the
                                                Accounts Receivable module to obtain the
                                                Debtor code for the GL account code.
Total invoice amount inclusive       9(12).99   Mandatory.
                                                Must include the decimal point even if the
                                                amount has no cents e.g. 582.00
VAT amount                           9(12).99   Mandatory.
                                                Must include the decimal point even if the
                                                amount has no cents e.g. 582.00

Page 4 of 12
                                                                          Specification: GB001/2
Supplier Invoice Import into SYSPRO 6.0                                     25 September 2003

Consignee                            X(40)       Notation, Invoice Ref, Journal notation

A user-defined menu option will be set up in SYSPRO 6.0. When the user selects
the Supplier Invoice Import option, a screen as per Figure 1.1 will be displayed to
the user.




Figure 1.1

When the user selects OK, the program will search for the specified file and any
subsequent files in a sequential order. Once a file is found, a validation will occur
for the entire file. If any errors are detected, no contents of the file will be imported
and it will be moved to the ERROR directory and a report as per Appendix A will
automatically print listing all the validation errors. The user will need to sort out
these errors, rename the file to the next sequence number and move it back to
the IMPORT directory for a new import. The validation process will perform the
following validation routine:
     1. Supplier code must exists in SYSPRO 6.0
     2. Date is in the format of DDMMYY
     3. There is an invoice number(Waybill) and it is unique for this supplier
     4. Invoice amount exists and is not zero
     5. VAT amount exists and is not zero.
     6. Tax code exists in SYSPRO 6.0
     7. Debtor Invoice number exists in the AR module’s invoice file. The debtor
        code will be picked up and the Branch code for the Debtor will be used to
        read the AR modules Branch file (SALBRN) to obtain the FREIGHT
        CHARGES General Ledger code (SALBRN-SFRT) that will be used as the
        Distribution GL code for the AP invoice. If this invoice number is blank the
        file will be rejected.

The program will always use TAX CODE A in any files requiring the tax code.
The DUE DATE will be calculated by program as follows: It will pick up the invoice
date and identify the month, it will add 1 to the month and the day will always be
the 25th (i.e. the 25th of next month).




Page 5 of 12
                                                              Specification: GB001/2
Supplier Invoice Import into SYSPRO 6.0                         25 September 2003

Once the validation has completed and no errors have been encountered, the
program will proceed to create the INVOICES in the AP module. The following
files will be updated:

    APSCTL (Supplier Control file)
    APSMST (Supplier Master file)
    APSINV (Supplier invoice detail file)
    APSJNC (Supplier journal control file)
    APSJND (Supplier journal detail file)
    APSJNS (Supplier journal summary file)
    APSTDD (Supplier Tax distribution detail file)
    APSTDS (Supplier Tax distribution summary file)

APSCTL(Supplier control file)
This file is used as follows:
   • Obtain current financial period in the AP module for the Invoice, tax
        distribution and journal files.
   • Obtain next journal number to use .
   • Increments the journal number by 1.
   • Adds the invoice amount to the month to date invoice value for the AP
        module

APSMST(Supplier master file)
This file is updated as follows:
   • Increments the Supplier’s current invoice balance with the invoice amount
        inclusive of VAT.
   • Increments the tax-month-to-date and tax-year-to-date amount with the
        VAT value
   • Increments the Supplier’s month-to-date other amount with the invoice
        amount exclusive of VAT.
   • Increments the Supplier’s year-to-date other amount with the invoice
        amount exclusive of VAT.

APSJNC(Journal control file)
This file is updated as follows:
   • A new record is created with the current AP period and Journal number as
        the key.
   • The invoice amount inclusive of VAT is stored.
   • The operator performing the import is recorded.

APSJND(Journal detail file)
This file is updated as follows:
   • A new record is created with the current AP period, Journal number,
        unique entry number and a sub entry number for each new invoice for a
        supplier.
   • The tax amount is recorded first using the General Ledger code assigned

Page 6 of 12
                                                                      Specification: GB001/2
Supplier Invoice Import into SYSPRO 6.0                                 25 September 2003

        to the Tax code and the reference which will be the consignee in the
        import file. This will be sub entry 1 for the entry.
    •   The invoice amount exclusive of VAT is recorded next using the General
        Ledger code assigned to the division code sent in the import file. The
        reference will also be the consignee in the import file. This will be sub entry
        2 for the entry.

APSJNS(Journal summary file)
  This file is updated as follows:
     • A new record is created with the current AP period, Journal number
          and unique entry number for each new invoice for a supplier.
     • The following fields are written to this record:
               o Supplier number
               o Invoice number
               o Reference will be the consignee field
               o Invoice date
               o Due date
               o Supplier branch code
               o Branch AP Control Ledger code
               o Amount inclusive of VAT
               o Supplier currency
               o Supplier name
               o Notation

APSINV(Supplier invoice detail)
This file is updated as follows:
        • A new record is created using the supplier code and invoice number as
             the key.
        • The following fields will now be written to this record:
                 o Journal date (today’s date)
                 o Journal number (From APSJNC file)
                 o Journal entry number (From APSJND file)
                 o Accounts payable branch code (From supplier master file)
                 o Invoice date (from .CSV file)
                 o Invoice due date (25th of following month using invoice date)
                 o Reference (Consignee field from .CSV file)
                 o Amount (Amount from .CSV file)
                 o Currency (From supplier master file)
                 o Bank code (From supplier file)
                 o Tax amount (From .CSV file)
                 o Posting year (Current year from AP control file)
                 o Posting month (Current month from AP control file)
                 o Tax code (Will always hardcode an “A” )



APSTDD(Tax distribution detail file)

Page 7 of 12
                                                                      Specification: GB001/2
Supplier Invoice Import into SYSPRO 6.0                                 25 September 2003

    This file is updated as follows:
       • A new record is created with the current AP period, Journal number
            and unique entry number for each new invoice for a supplier.
       • Transaction type of “I”
       • Supplier code
       • Invoice number
       • Reference
       • Invoice date
       • Amount inclusive of VAT
       • VAT amount
       • Amount exclusive of VAT

APSTDS(Tax distribution summary file)
  This file is updated as follows:
  • A new record is created with the current AP period, Journal number and
     unique entry number for each new invoice for a supplier.
  • Journal date
  • Amount inclusive of VAT
  • VAT amount

An audit file will be created displaying all transactions that were successfully
created in SYSPRO. If a file was rejected, the audit report will indicate that the file
had validation errors and was rejected. Please see the FILE LAYOUTS section
for details on the audit file.




Page 8 of 12
                                                                     Specification: GB001/2
Supplier Invoice Import into SYSPRO 6.0                                25 September 2003

2. Audit Report

    An audit report may be printed at any time by date. It will display all files that
    were successfully/unsuccessfully imported for that date or range of dates. A
    screen as per Figure 2.1 will be displayed to the user allowing them to select a
    date, range of dates or all dates. Please see Appendix B for the layout of the
    report.




    Figure 2.1




Page 9 of 12
                                                                       Specification: GB001/2
Supplier Invoice Import into SYSPRO 6.0                                  25 September 2003


    FILE LAYOUTS
1. Audit File – ?GBFAUD
   Audit records for all invoices created by the interface
   This file will reside the SYSPRO data directory.

    FIELD                                     SIZE           DESCRIPTION (A)
*   GBFAUD-DATE                               9(8)           Date
*   GBFAUD-TIME                               9(8)           Time
*   GBFAUD-SEQ                                9(3)           Unique sequence no.
    GBFAUD-FILE                               X(10)          File name
    GBFAUD-USER                               X(6)           User doing the import
    GBFAUD-IND                                X              Success indicator
                                                             Y = Yes
                                                             N = No
    GBFAUD-SUPP                               X(7)           Supplier number
    GPFAUD-INV                                X(20)          Invoice number
    GPFAUD-AMT                                9(12).99       Amount inclusive of VAT
    GPFAUD-CONSIGNEE                          X(40)          Consignee
    GPFAUD-DATE                               9(8)           Invoice date
    GPFAUD-GL                                 X(15)          GL code used for
                                                             distribution
    GPFAUD-IND                                X              Indicator if default GL code
                                                             was used
                                                             Y = Default used
                                                             “ “ = From AR branch file
    FILLER                                    X(59)          Filler for future

.




Page 10 of 12
                                                               APPENDIX A
Prepared:7/09/2003 09:22                            Industrial Urethanes               Page :   1
Version : 6.0.001                                   Validation errors on import file

Date: 9999/99/99      File name: SUPIMP.999   User: XXXXXX

Error Message                                                   Field

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX       XXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX       XXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX       XXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX       XXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX       XXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX       XXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX       XXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX       XXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX       XXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX       XXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX       XXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX       XXXXXXXXXXXXXXXXXXXX

** End of Report **




Page 11 of 12
                                                                 APPENDIX B
For a successful import

Prepared:7/09/2003 09:22                            Industrial Urethanes                                          Page :       1
Version : 6.0.001                                   Audit Report for Supplier Invoice import

Date: 9999/99/99      File name: SUPIMP.999   User: XXXXXX   Status: Successful

Supplier   Invoice                   Amount(Inclusive)   Consignee                              Invoice date   GL Code         Default

XXXXXXX    XXXXXXXXXXXXXXXXXXXX        999999999999.99   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX   9999/99/99   XXXXXXXXXXXXXXX       X
XXXXXXX    XXXXXXXXXXXXXXXXXXXX        999999999999.99   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX   9999/99/99   XXXXXXXXXXXXXXX       X
XXXXXXX    XXXXXXXXXXXXXXXXXXXX        999999999999.99   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX   9999/99/99   XXXXXXXXXXXXXXX       X
XXXXXXX    XXXXXXXXXXXXXXXXXXXX        999999999999.99   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX   9999/99/99   XXXXXXXXXXXXXXX       X
XXXXXXX    XXXXXXXXXXXXXXXXXXXX        999999999999.99   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX   9999/99/99   XXXXXXXXXXXXXXX       X


** End of Report **



For an unsuccessful import

Prepared:7/09/2003 09:22                            Industrial Urethanes                                          Page :       1
Version : 6.0.001                                   Audit Report for Supplier Invoice import

Date: 9999/99/99      File name: SUPIMP.999   User: XXXXXX   Status: Unsuccessful

Supplier   Invoice                   Amount(Inclusive)   Consignee                              Invoice date

There were records in this file with validation errors, please refer to the error report for more details.
This file was moved to the ERROR directory


** End of Report **




Page 12 of 12

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:27
posted:3/7/2011
language:English
pages:12