Projects:Booking Control/Specification Technical
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
- New table: OBBO_BOOKING
- OBBO_Booking_ID VARCHAR2(32) NOT NULL,
- AD_Client_ID VARCHAR2(32) NOT NULL,
- AD_Org_ID VARCHAR2(32) NOT NULL,
- IsActive CHAR(1 BYTE) DEFAULT 'Y' NOT NULL,
- Created DATE DEFAULT SYSDATE NOT NULL,
- CreatedBy VARCHAR2(32) NOT NULL,
- Updated DATE DEFAULT SYSDATE NOT NULL,
- UpdatedBy VARCHAR2(32) NOT NULL,
- C_DocType_ID VARCHAR2(32) NOT NULL,
- DocumentNo NVARCHAR2(30) NOT NULL,
- Amount NUMBER NOT NULL,
- C_Period_ID VARCHAR2(32) NOT NULL,
- Description NVARCHAR2(255),
- C_AcctSchema_ID VARCHAR2(32) NOT NULL,
- AD_Table_ID VARCHAR2(32) NOT NULL,
- Record_ID VARCHAR2(32) NOT NULL,
- BrowseDocument CHAR(1 BYTE) DEFAULT 'N' NOT NULL, (Button to browse original document from Booking window)
- DocBaseType CHAR(3 BYTE) NOT NULL
(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 )
- New View: OBBO_Booking_Invoice_v
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'
- New View: OBBO_Booking_Order_v
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'
- New View: OBBO_Booking_Cash_v
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'
- New View: OBBO_Booking_BankStatement_v
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'
- New View: OBBO_Booking_InOut_v
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'
- New View: OBBO_Booking_Inventory_v
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'
- New View: OBBO_Booking_Production_v
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'
- New View: OBBO_Booking_Settlement_v
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'
- New View: OBBO_Booking_DPManagement_v
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'
- New View: OBBO_Booking_Movement_v
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'
- New View: OBBO_Amortization_v
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'
- New View: OBBO_GLJournal_v
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:
- Booking control: Financial Management || Accounting || Transactions || Booking control
- Header: based on the table OBBO_BOOKING
- Accounting: Based on Accounting information related to this particular booking slip. To do this probably a new view is needed. (Nice to have, not in first phase)
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
- Tab "Booking control" in window Purchase invoice: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Sales invoice: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Material Shipment: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Material Receipt: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Bank Statement: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Cash Journal: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Settlement: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window G/L Journal: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Debt Payment management: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Simple Production: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Amortization: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Physical Inventory: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Sales Order: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Purchase Order: This tab is no editable and should contain all the fields related to Booking slip
- Tab "Booking control" in window Material Movement: This tab is no editable and should contain all the fields related to Booking slip
Reference data
The module will contain some reference data as follows:
- New DocBaseType: New DocBaseType will be created to link document types related to booking control. This reference data will be at system level
- Value: 'BCS'
- Name: 'Booking control slip'
- New DocumentType: New DocumentType will be provided using previous DocBaseType. This reference data will be at Client/Organization level so that we can install it as many times as we need it. Remember we will be providing one DocumentType per each of the organizations.
- New Sequence: New Sequence will be included in the module to meet the requirement of having different sequence numbers fro each organization. this reference data will also be at Client/Organization level and will be linked to previous DocumentType.
- New process: New process in application dictionary that will be the one in charge of doing the booking control of the accounting transaction. This reference data will be at system level, this way the feature (process) will be available for all the clients)
- New assignment of this process to the table where the processes that will be launched in posting time (to be defined). This setup will be done manually and it is not needed to create a dataset for this info
Processes
- Browse Document: A new process will be added to browse the transactional document from the Booking control window.
- The existing class org.openbravo.erpCommon.ad_reports.ReportAcctRedirectUtility can be used for this purpose
- Create booking: This process will be in charge of creating the booking control entry and will be launched at the same time as posting occurs. The definition of this process will depend o the modification of core needed (See Dependencies section).
- It will need to implement an interface provided by the core product:
- Interface: org.openbravo.erpCommon.ad_forms.AcctProcessTemplate.
- And it will need to define the method:
- public abstract boolean execute(AcctServer acctServer, AcctSchema as, ConnectionProvider conn,Connection con, VariablesSecureApp vars) throws ServletException
- In that method the booking control slip will be created and info will be retrieved from acctServer object, which contains all document information
- It will need to implement an interface provided by the core product:
Menu Creation in Application Dictionary
- Login Openbravo ERP as System Administrator.
- Goto General Setup || Application || Menu || Menu
- Create a New Record "Menu"
- Name = Booking control
- Description = Booking control
- Active = Yes
- Action = "Window"
- Window = Booking control
- Save
- Click "Tree" icon in the tool bar (Pop up window will open).
- Drag & Drop newly created menu (Booking control) on the appropriated place [Financial Management >> Accounting >> Transactions].
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:
- 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.
- 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:
- Reset per Year (Y/N Flag. Default: No)
- First assigned number for the Year (Text)
* 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:
- Year (TableDir to C_YEAR)
- Next Assigned Number (Text)
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:
- 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.
- 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.
- 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:
- "Financial Management // Receivables & Payables // Transactions // Payment In // Booking Control"
- "Financial Management // Receivables & Payables // Transactions // Payment Out // Booking Control"
- "Financial Management // Receivables & Payables // Transactions // Financial Account // Reconciliation // Booking Control"
- "Financial Management // Receivables & Payables // Transactions // Financial Account // Booking Control for Transactions"
- "Financial Management // Receivables & Payables // Transactions // Financial Account // Bank Statements // Booking Control"
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:
- OBBO_FIN_PAYMENT_V
- OBBO_FIN_RECONCILIATION_V
- OBBO_FIN_TRANSACTION_V
- OBBO_FIN_BANKSTATEMENT_V
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.