Projects/Invoicesregisterbook/TechnicalSpecifications

Contents 1 INVOICES REGISTER BOOK: Technical Specifications 1.1 PURPOSE OF THE PROJECT: 1.2 INTRODUCTION: 1.3 TECHNICAL IMPLEMENTATION : 1.3.1 MODULE DATABASE PREFIX 1.3.2 USER INTERFACE IMPLEMENTATION: 1.3.2.1 New windows and tabs: 1.3.2.1.1 [Financial Management / Accounting / Setup / Invoices Register Books]. 1.3.2.1.2 [Financial Management / Accounting / Analysis Tools / Invoices Register Books]. 1.3.2.2 Additional database changes 1.3.2.2.1 [Financial Management || Accounting || Setup || Tax Rate]. 1.3.2.2.2 [Sales/Purchase Invoices ]. 1.3.2.3 Reference Lists 1.3.2.3.1 OBIRB_RegisterBookType 1.3.3 DEPENDANT MODULES: Spain Taxes 1.3.3.1 XXXX_BookDeclaration Reference List 1.3.3.2 XXXX_OperationCode Reference List 1.3.3.3 New records 1.3.4 DEVELOPMENT CONSIDERATIONS

INVOICES REGISTER BOOK: Technical Specifications

PURPOSE OF THE PROJECT:

Spanish law requires that every company keeps a record of every single invoice received and issued. The Invoices Register Book module will enable users to meet this legal requirement. Two types of invoices register books will be available in this module:

• Issued invoices, which allows the end user to accurately determine the total VAT-fiscal debit “charged to its customers” for a given period and
• Received invoices, which allows the end user to accurately determine the total VAT-fiscal credit “charged by its vendors/suppliers” for a given period

The development will be thought to facilitate as much as possible the future development of the Spanish Tax Report called 340, where some information of these invoices register books will be used, such as the operation keys linked to each invoice line.

INTRODUCTION:

This development will provide new window/tabs to be able to do the setup and execution of this kind of register books. The application will create and store in the database the retrieved information from management to fill the Invoices Register Book, and it will allow the user to deactivate and to add new registers to the book (taking responsibility of these changes).

Once the register book is definitive, the user could mark it as final. This process will give a register number for each line and will allow the user to generate/print the final PDF file.

TECHNICAL IMPLEMENTATION :

MODULE DATABASE PREFIX

The database prefix used by this module is OBIRB, which is the acronym of OpenBravoInvoicesRegisterBooks.

USER INTERFACE IMPLEMENTATION:

New windows and tabs:

[Financial Management / Accounting / Setup / Invoices Register Books].

Invoices Register Books are defined in this window. An Invoices Register Book must have associated a list of VAT Tax Rates

• Tabs:
  • Invoices Register Books.
    • Related table:
      • OBIRB_InvBook_Setup
        • Columns:

Column	Mandatory	Data Type	Description	Notes
OBIRB_INVBOOK_SETUP_ID	Y	VARCHAR(32)	Identifier for Invoices Register Book Setup
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data
AD_ORG_ID	Y	VARCHAR(32)	Audit data
CREATED	Y	DATE	Audit data
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1 BYTE)	Audit data	Default ='Y'
TYPE	Y	VARCHAR(60)	List with the type of Invoices Register Book (E-Issued, R-Received)	OBIRB_RegisterBookType
NAME	Y	NVARCHAR2(60)	Operation Code name
DESCRIPTION	N	NVARCHAR2(255)	Brief explanation of the Operation Code.
STARTNO	Y	DECIMAL(10,0)	Sequence used for the generated lines of this Invoice Register Book

• • VAT Tax Rates.
    • Related table: OBIRB_InvBook_Setup
      • OBIRB_InvBookTax_Setup
        • Columns:

Column	Mandatory	Data Type	Description	Notes
OBIRB_INVBOOKTAX_SETUP_ID	Y	VARCHAR(32)	Identifier for VAT Tax Rates of the Invoices Register Books
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data
AD_ORG_ID	Y	VARCHAR(32)	Audit data
CREATED	Y	DATE	Audit data
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1 BYTE)	Audit data	Default ='Y'
OBIRB_INVBOOK_SETUP_ID	Y	VARCHAR(32)	Identifier for Invoices Register Book Setup
C_TAX_ID	Y	VARCHAR(32)	Tax Rate	Show only child purchase/sale tax rates declared in this type of book (purchase or sales)
C_Doctype_ID	Y	VARCHAR(32)	Document Type	Must take into account the type of book (purchase or sales)

[Financial Management / Accounting / Analysis Tools / Invoices Register Books].

Invoices Register Books are launched in this window.

• Tabs:
  • Invoices Register Books - Header.
    • Related table:
      • OBIRB_InvBook
        • Columns:

Column	Mandatory	Data Type	Description	Notes
OBIRB_INVBOOK_ID	Y	VARCHAR(32)	Identifier for Invoices Register Book
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data
AD_ORG_ID	Y	VARCHAR(32)	Audit data	Validation: Legal Entity with accounting
CREATED	Y	DATE	Audit data
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1 BYTE)	Audit data	Default ='Y'
OBIRB_INVBOOK_SETUP_ID	Y	VARCHAR(32)	Identifier for Invoices Register Book Setup
NAME	Y	NVARCHAR2(255)	Operation Code name
DESCRIPTION	N	NVARCHAR2(255)	Brief explanation of the Operation Code.
C_YEAR_ID	Y	VARCHAR(32)	Year
FROM_C_PERIOD_ID	Y	VARCHAR(32)	From Period.	Show the periods of the selected year
TO_C_PERIOD_ID	Y	VARCHAR(32)	To Period.	Show the periods of the selected year
ISCORRECTIVE	Y	CHAR(1)	Checkbox. If checked, only includes Credit Invoices	'Y', 'N'. Default='N'
ISCREATED	Y	CHAR(1)	Button for creating the lines of the book	'Y', 'N'. Default='N'
ISPROCESSED	Y	CHAR(1)	Button for generate the book and mark as final	'Y', 'N'
ISFINAL	Y	CHAR(1)	Checkbox Final. Readonly when 'N' is selected	'Y', 'N'

• • Invoices Register Books - Lines.
    • Related table: OBIRB_InvBook
      • OBIRB_InvBookLine (Read only)
        • Columns:

Column	Mandatory	Data Type	Description	Notes
OBIRB_INVBOOKLINE_ID	Y	VARCHAR(32)	Identifier for Invoices Register Book Line
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data
AD_ORG_ID	Y	VARCHAR(32)	Audit data
CREATED	Y	DATE	Audit data
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1 BYTE)	Audit data	Default ='Y'
ISMANUAL	Y	CHAR(1 BYTE)	'Y' in case of a manually introduced line	Default ='N'
OBIRB_INVBOOK_ID	Y	VARCHAR(32)	Identifier for Invoices Register Book
C_INVOICE_ID	N	VARCHAR(32)	Associated Invoiced	Always Readonly
SEQNO	N	NVARCHAR(128)	Register number (from sequence)	Filled when locking the book
DOCUMENTNO	Y	NVARCHAR(30)	Invoice Document number
DOCDATE	Y	DATE	Document date	Take the shipment/receipt date. If not available, take invoice date
C_BPARTNER_ID	Y	VARCHAR(32)	Business Partner
C_BPARTNER_TAXID	Y	VARCHAR(20)	Business Partner Tax ID	The process will fail is a business partner has no TAX ID
OPERATIONCODE	Y	VARCHAR(60)	Operation Code
TAXBASEAMT	Y	DECIMAL	Tax Base Amount
VATRATE	Y	DECIMAL	VAT rate as a %
VATAMT	Y	DECIMAL	VAT amount
ECRATE	N	DECIMAL	EC rate as a %
ECAMT	Y	DECIMAL	EC amount
DECTAXAMT	N	DECIMAL	Deductible tax amount	Just for received invoices register books
GRANDTOTAL	Y	DECIMAL	Invoice Amount

Additional database changes

[Financial Management || Accounting || Setup || Tax Rate].

• • Existing tab Header.
    • Related table:
      • C_Tax (Already exists)
        • New columns:

Column	Mandatory	Data Type	Description	Note
EM_XXXX_DECLARATION	Y	VARCHAR(60)	Declaration. Defines where this tax rate is defined, at: "Sales declaration" or "Purchase declaration".	List XXXX_BookDeclaration
EM_XXXX_ISREVERSECHARGE	Y	CHAR(1)	Reverse charge. If checked, the transaction code used for this Tax Rate will be 'I'	<onCreateDefault><![CDATA['N']]></onCreateDefault>.
EM_XXXX_ISEQUIVALENTCHARGE	Y	CHAR(1)	Equivalent charge. They're always linked to a parent tax rate (apply displaylogic)	<onCreateDefault><![CDATA['N']]></onCreateDefault>.

[Sales/Purchase Invoices ].

• • Existing tab Tax.
    • Related table: C_Invoice
      • C_InvoiceTax (Already exists)
        • New columns:

Column	Mandatory	Data Type	Description	Note
EM_OBIRB_OPERATIONCODE	N	VARCHAR(60)	Operation code	The operation code will be automatically added by the application when completing the invoice. This will be done using the new core functionality that allows to run code after a PL procedure or using a trigger in the C_Invoice table

• • Existing tab Tax.
    • Related table:
      • C_Invoice (Already exists)
        • New columns:

Column	Mandatory	Data Type	Description	Note
EM_OBIRB_DATEOPERATION	Y	DATE	Operation code	Operation date used by the invoices register book. By default take Invoice Date

Reference Lists

OBIRB_RegisterBookType

E: "E- Issued" R: "R- Received"

DEPENDANT MODULES: Spain Taxes

The Invoices Register Book module will depend on the [Spain Taxes Module ]. The following changes will be done in this tax module:

XXXX_BookDeclaration Reference List

S: "Sales declaration" P: "Purchase declaration"

XXXX_OperationCode Reference List

Note: "XXXX" is the dbprefix for the Spain Taxes Module

This list will contain: " ", "I", "C", "K", "D"

New records

The module will have the necessary tax rates already configured.

DEVELOPMENT CONSIDERATIONS

1- The invoice register books are launched at "Legal Entity with accounting" level, and they must also include the invoices of all the child organizations.

2- The module will include an alert that will check if the user has any active tax rate of type "Both".

3- The module will inform the user about the process of migration in case of existing tax rates.

4- Before launching the book, the system will check if there are invoices created before the installation of the module for the selected period. In this case it will show an info message, explaining that the accuracy of this report can be wrong.

5- The book logic will use an improvement in Core that allows us to run code after the execution of any PL code. In case this functionality is not available in the moment of the development of this module, we will use Triggers as a workaround.

6- The PDF will be generated using the Jasper Report Technology, as other PDF files already in the application.

7- The development of this module will use the new Data Access Layer based on Hibernate.