Log in / create account
Openbravo.com
Issues
Forge
University
View source | View content page | Page history | Printable version   

Projects:Booking Control/Specification Technical

Contents

TECHNICAL SPECIFICATION OF BOOKING CONTROL

PURPOSE OF THE PROJECT

The purpose of this document is to describe the technical Specifications for an extension module to be developed in OB ERP version 2.50 called Booking Control.

INTRODUCTION

This technical specifications document outlines the implementation of Booking control feature.

TECHNICAL IMPLEMENTATION :

This module will provide system of a tab showing the information related to the booking control for all the transactional windows related to accounting. As well a new window for the booking slip will be provided.

REQUIRED FILES

A new .obx file will be provided containing the new window, the tabs to display booking slip information and a dataset containing the new document type and sequence. It is also needed a change in core to trigger the creation of this new document at the same time the document is posted.

(Here all the information related to the module creation is pending)

New database objects

(Here all the information related to data types etc is pending to be added)


BOOKING TABLE SCRIPT FOR POSTGRES

CREATE TABLE obbo_booking

( obbo_booking_id character varying(32) NOT NULL, ad_client_id character varying(32) NOT NULL, ad_org_id character varying(32) NOT NULL, c_acctschema_id character varying(32) NOT NULL, c_doctype_id character varying(32) NOT NULL, c_period_id character varying(32) NOT NULL, ad_table_id character varying(32) NOT NULL, record_id character varying(32) NOT NULL, isactive character(1) NOT NULL DEFAULT 'N'::bpchar, description character varying(255), docbasetype character(3) NOT NULL, browsedocument character(1) NOT NULL DEFAULT 'N'::bpchar, documentno character varying(32) NOT NULL, amount numeric NOT NULL DEFAULT 0, created timestamp without time zone NOT NULL DEFAULT now(), createdby character varying(32) NOT NULL, updated timestamp without time zone NOT NULL DEFAULT now(), updatedby character varying(32) NOT NULL, CONSTRAINT obbo_booking_key PRIMARY KEY(obbo_booking_id), CONSTRAINT adclient_obbo_booking FOREIGN KEY (ad_client_id) REFERENCES ad_client(ad_client_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT adorg_obbo_booking FOREIGN KEY(ad_org_id) REFERENCES ad_org(ad_org_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT c_period_obbo_booking FOREIGN KEY(c_period_id) REFERENCES c_period(c_period_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT c_acctschema_obbo_booking FOREIGN KEY(c_acctschema_id) REFERENCES c_acctschema(c_acctschema_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT adtable_obbo_booking FOREIGN KEY(ad_table_id) REFERENCES ad_table(ad_table_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE, CONSTRAINT c_doctype_obbo_booking FOREIGN KEY(c_doctype_id) REFERENCES c_doctype(c_doctype_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE )

SELECT OBBO_BOOKING_ID AS OBBO_BOOKING_INVOICE_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID, C_PERIOD_ID, RECORD_ID AS C_INVOICE_ID, BROWSEDOCUMENT, AMOUNT, C_DOCTYPE_ID, VALUE, DESCRIPTION FROM OBBO_BOOKING WHERE AD_TABLE_ID = '318'

SELECT OBBO_BOOKING_ID AS OBBO_BOOKING_ORDER_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID, C_PERIOD_ID, RECORD_ID AS C_ORDER_ID, BROWSEDOCUMENT, AMOUNT, C_DOCTYPE_ID, VALUE, DESCRIPTION FROM OBBO_BOOKING WHERE AD_TABLE_ID = '259'

SELECT OBBO_BOOKING_ID AS OBBO_BOOKING_CASH_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID, C_PERIOD_ID, RECORD_ID AS C_CASH_ID, BROWSEDOCUMENT, AMOUNT, C_DOCTYPE_ID, VALUE, DESCRIPTION FROM OBBO_BOOKING WHERE AD_TABLE_ID = '407'

SELECT OBBO_BOOKING_ID AS OBBO_BOOKING_BANKSTATEMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID, C_PERIOD_ID, RECORD_ID AS C_BANKSTATEMENT_ID, BROWSEDOCUMENT, AMOUNT, C_DOCTYPE_ID, VALUE, DESCRIPTION FROM OBBO_BOOKING WHERE AD_TABLE_ID = '392'

SELECT OBBO_BOOKING_ID AS OBBO_BOOKING_INOUT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID, C_PERIOD_ID, RECORD_ID AS M_INOUT_ID, BROWSEDOCUMENT, AMOUNT, C_DOCTYPE_ID, VALUE, DESCRIPTION FROM OBBO_BOOKING WHERE AD_TABLE_ID = '319'

SELECT OBBO_BOOKING_ID AS OBBO_BOOKING_INVENTORY_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID, C_PERIOD_ID, RECORD_ID AS M_INVENTORY_ID, BROWSEDOCUMENT, AMOUNT, C_DOCTYPE_ID, VALUE, DESCRIPTION FROM OBBO_BOOKING WHERE AD_TABLE_ID = '321'

SELECT OBBO_BOOKING_ID AS OBBO_BOOKING_PRODUCTION_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID, C_PERIOD_ID, RECORD_ID AS M_PRODUCTION_ID, BROWSEDOCUMENT, AMOUNT, C_DOCTYPE_ID, VALUE, DESCRIPTION FROM OBBO_BOOKING WHERE AD_TABLE_ID = '325'

SELECT OBBO_BOOKING_ID AS OBBO_BOOKING_SETTLEMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID, C_PERIOD_ID, RECORD_ID AS C_SETTLEMENT_ID, BROWSEDOCUMENT, AMOUNT, C_DOCTYPE_ID, VALUE, DESCRIPTION FROM OBBO_BOOKING WHERE AD_TABLE_ID = '800019'

SELECT OBBO_BOOKING_ID AS OBBO_BOOKING_DP_MANAGEMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID, C_PERIOD_ID, RECORD_ID AS C_DP_MANAGEMENT_ID, BROWSEDOCUMENT, AMOUNT, C_DOCTYPE_ID, VALUE, DESCRIPTION FROM OBBO_BOOKING WHERE AD_TABLE_ID = '800176'

SELECT OBBO_BOOKING_ID AS OBBO_BOOKING_MOVEMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID, C_PERIOD_ID, RECORD_ID AS M_MOVEMENT_ID, BROWSEDOCUMENT, AMOUNT, C_DOCTYPE_ID, VALUE, DESCRIPTION FROM OBBO_BOOKING WHERE AD_TABLE_ID = '323'

SELECT OBBO_BOOKING_ID AS OBBO_BOOKING_AMORTIZATION_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID, C_PERIOD_ID, RECORD_ID AS A_AMORTIZATION_ID, BROWSEDOCUMENT, AMOUNT, C_DOCTYPE_ID, VALUE, DESCRIPTION FROM OBBO_BOOKING WHERE AD_TABLE_ID = '800060'


SELECT OBBO_BOOKING_ID AS OBBO_BOOKING_GL_JOURNAL_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID, C_PERIOD_ID, RECORD_ID AS GL_JOURNAL_ID, BROWSEDOCUMENT, AMOUNT, C_DOCTYPE_ID, VALUE, DESCRIPTION FROM OBBO_BOOKING WHERE AD_TABLE_ID = '224'

Application Dictionary

New objects should be registered in Application Dictionary as follows:

New window:

New tabs fro existing windows: All of the following tabs will have the same field structure and will be based on the columns and table for the booking control, views created from the table OBBO_BOOKING

Reference data

The module will contain some reference data as follows:

Processes

Menu Creation in Application Dictionary

Dependencies

A new enhancement in core is needed to be able to launch one or more processes at posting time. This is a prerequisite for this project in order to be able to deliver a process (Booking control process) that will be launched at posting time.

To test

OPEN DISCUSSION ITEMS

CLOSED DISCUSSION ITEMS

Advanced Sequence Enhancement

Booking control modules is going to be enhanced as described in the Feature Request 13863: sequence numbers for booking control modules will implement a logic so:

  1. Prefixes and suffixes will include a logic, so the string 'YYYY' will be replaced by the fiscal year (four digits) of the booking control document.
  2. The sequence associated to booking control document, will have the possibility to maintain one independent sequence per each fiscal year, of each calendar. This way, system will be ale to reset the sequence numbers per each fiscal year.

Database and Application Dictionary

Sequence window needs to be modified: New section: Periodicity New fields under Periodicity section:

* A display logic will make that in case "Reset per Year" is active, the "Next Assigned Number" field will be hidden, and the "First assigned number for the Year" one will be shown.

New tab under Sequence one, named "Year Sequence", with these fields:

Code

The accounting process, implemented at src/org/openbravo/obbo/process/BookingControlProcess.java, has got the following piece of code: 

Sequence sequence = getSequence(OBDal.getInstance().get(Organization.class,
    acctServer.AD_Org_ID), OBDal.getInstance().get(
    org.openbravo.model.financialmgmt.accounting.coa.AcctSchema.class, as.m_C_AcctSchema_ID));
if (sequence == null)
  sequence = docType.getDocumentSequence();
StringBuffer sequenceBuffer = new StringBuffer();
if (sequence.getPrefix() != null) {
  sequenceBuffer.append(sequence.getPrefix());
}
sequenceBuffer.append(sequence.getNextAssignedNumber().toString());
if (sequence.getSuffix() != null) {
  sequenceBuffer.append(sequence.getSuffix());
}
bcs.setDocumentNo(sequenceBuffer.toString());
sequence.setNextAssignedNumber(sequence.getNextAssignedNumber() + sequence.getIncrementBy());
OBDal.getInstance().save(sequence);
OBDal.getInstance().flush();

This is the code in charged of building the sequence number, whenever a new booking control document is posted. In order to implement the requested functionality, some more functionality needs to be implemented:

  1. Once the sequence is retrieved, system will check the flag "Reset per Year". In case it is active, will look in the "Year Sequence" sub-tab. In this tab, there are a set of entries that matches a year (C_Year_ID) with a "Next Assigned Number". According to the booking control accounting date, and the organization, the C_Year_ID that corresponds to the booking control document is retrieved.
    1. In case it exists an entry in the "Year Sequence" sub-tab, the "Next Assigned Number" value is retrieved, and incremented according to "Increment By" value in the Sequence header.
    2. In case it doesn't exist, a new record is created with that C_Year_ID value, and as "Next Assigned Number", the value in the "First assigned number for the Year" field of the sequence header. Then, the "Next Assigned Number" value is retrieved, and incremented according to "Increment By" value in the Sequence header.


Advanced Payables and Receivables compatibility

Introduction

From version 1.1.0, the Booking Control module is compatible with the new Advanced Payables and Receivables functionality, so some new tabs have been created in order to display the booking control information related with the APR accounting:

Apart from these new tabs, the "Accounting History" tab has been moved just behind the new "Booking Control for Transactions", displaying the Accounting History for a concrete booking control transaction.

Development

The main development consists of creating new database views that are used to build the new tabs. The new views are:

These views just filter the Booking control information by the table id that corresponds with the tab. Example: 

SELECT obbo_booking.obbo_booking_id AS obbo_fin_payment_v_id, obbo_booking.ad_client_id, obbo_booking.ad_org_id, obbo_booking.isactive, obbo_booking.created, obbo_booking.createdby,
obbo_booking.updated, obbo_booking.updatedby, obbo_booking.c_acctschema_id, obbo_booking.c_period_id, obbo_booking.record_id AS fin_payment_id, obbo_booking.browsedocument,
obbo_booking.amount, obbo_booking.c_doctype_id, obbo_booking.documentno, obbo_booking.description
FROM obbo_booking
WHERE obbo_booking.ad_table_id = 'D1A97202E832470285C9B1EB026D54E2'


The "Accounting History" has a special consideration. This tab does not belong to the Booking Control module, so we can't modify it; that's why it's necessary to duplicate it in this module. So, a new view called OBBO_FINACC_TRANSACTION_ACCT_V will be created with the same structure as the APRM_FINACC_TRANSACTION_ACCT_V. However, it will have one important modification: when defining its columns into the Application Dictionary, the FIN_Finacc_Transaction_ID column must be marked as "Link to Parent Column", because in this tab we just want to display the Accounting history for the selected transaction (and not for all transactions)


Dependencies

Posting of Imported Bank Statements will be available from Openbravo 3.0 RC4, so it's important to make this module dependant of the Advanced Payables and Receivable module that corresponds to 3.0 RC4.

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Booking_Control/Specification_Technical"

This page has been accessed 1,167 times. This page was last modified on 19 January 2018, at 10:10. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.