Projects/Invoicesregisterbook/TechnicalSpecifications
Contents
|
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:
- OBIRB_InvBook_Setup
- Related table:
- Invoices Register Books.
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:
- OBIRB_InvBookTax_Setup
- Related table: OBIRB_InvBook_Setup
- VAT Tax Rates.
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:
- OBIRB_InvBook
- Related table:
- Invoices Register Books - Header.
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:
- OBIRB_InvBookLine (Read only)
- Related table: OBIRB_InvBook
- Invoices Register Books - Lines.
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:
- C_Tax (Already exists)
- Related table:
- Existing tab Header.
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:
- C_InvoiceTax (Already exists)
- Related table: C_Invoice
- Existing tab Tax.
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:
- C_Invoice (Already exists)
- Related table:
- Existing tab Tax.
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.