Projects:Contract Management/Technical Specification

Contents 1 Overview 2 Module definition 3 List of artifacts 3.1 New tables 3.1.1 Temporal Invoices Table 3.1.2 Temporal Invoice Lines Table 3.1.3 Invoice Plan Table 3.1.4 Invoicing Process Configuration Table 3.2 Modified Core tables 3.3 Window & Menu entries 3.4 Message 3.5 Reference 3.6 Process Definition 3.7 Dataset 3.7.1 Dataset Info 3.8 Stored Function 4 Java artifacts 4.1 Event Handler 4.2 Create Invoice Plan 5 JavaScript artifacts 5.1 View Implementation 5.1.1 Invoicing Process Filter window 6 Class Diagram

Overview

This article will explain technical infrastructure of Contract Management module.

Module definition

• Name: Contract Management
• Type: module
• Description: Standard contract management
• Java package: org.openbravo.contract
• Version: 1.0.0
• Language: en_US
• License: OBPL
• License Text: Licensed under the Openbravo Public License version 1.1. You may obtain a copy of the License at http://www.openbravo.com/legal/license.html or in the legal folder of the Openbravo ERP core distribution.
• Author/Licensor: Openbravo S.L.U.
• Translation required: Y
• Has reference data: Y
• Dependency:
  • core - first version: 3.0.17596
  • User Interface Application - first version: 2.1.17839
• DB Prefix: OBCNTR

List of artifacts

New tables

The following tables will be created for this module:

• OBCNTR_C_INVOICE_TEMP
• OBCNTR_C_INVOICELINE_TEMP
• OBCNTR_INVOICEPLAN
• OBCNTR_INVPRO_CONFIG

The following core tables will have some fields added:

• C_INVOICELINE
• C_PROJECT

Temporal Invoices Table

This table is (almost) a clone of C_INVOICE table. Deprecated columns or button columns have been ignored.

It will store the temporal invoices proposed by the billing process.

OBCNTR_C_INVOICE_TEMP
Column Name	Type	Length	Comments
OBCNTR_C_Invoice_Temp_ID	ID	32	Table primary key.
AD_Client_ID	TableDir	32	Client for this installation.
AD_Org_ID	TableDir	32	Organizational entity within client
Isactive	YesNo	1	A flag indicating whether this record is available for use or de-activated.
Created	DateTime	19	The date that this record is completed.
Createdby	Search	32	User who created this records
Updated	DateTime	19	The date that this record was last updated
Updatedby	Search	32	User who updated this records
Issotrx	YesNo	1	An indication that a transfer of goods and money between business partners is occurring.
DocumentNo	String	30	An often automatically generated identifier for all documents.
Docstatus	List	60	The Document Status indicates the status of a document at this time.
Docaction	List	60	A means of changing the transaction status of the document.
Processed	YesNo	1	A confirmation that the associated documents or requests are processed.
C_Doctype_ID	TableDir	32	A value defining what sequence and process setup are used to handle this document.
Description	Text	255	A space to write additional related information.
Salesrep_ID	Table	32	The person in charge of a document.
Dateinvoiced	Date	19	The time listed on the invoice.
Dateprinted	Date	19	Date the document was printed.
Dateacct	Date	19	The date this transaction is recorded for in the general ledger.
C_Bpartner_ID	Search	32	Anyone who takes part in daily business operations by acting as a customer, employee, etc.
C_Bpartner_Location_ID	TableDir	32	The location of the selected business partner.
Poreference	String	20	A reference or document order number as listed in business partner application.
Dateordered	Date	19	The time listed on the order.
C_Currency_ID	TableDir	32	An accepted medium of monetary exchange that may vary across countries.
C_Paymentterm_ID	TableDir	32	The setup and timing defined to complete a specified payment.
C_Charge_ID	Table	32	A cost or expense incurred during business activity.
Chargeamt	Amount	2	The amount of a cost or expense incurred during business activity.
Totallines	Amount	2	The final sum of all line totals made to a specified document or transaction (not including taxes).
Grandtotal	Amount	2	The final monetary amount (including taxes) charge listed in a document.
M_Pricelist_ID	TableDir	32	A catalog of selected items with prices defined generally or for a specific partner.
Istaxincluded	YesNo	1	Tax is included in the price
C_Campaign_ID	TableDir	32	An advertising effort aimed at increasing sales.
C_Project_ID	Search	32
C_Activity_ID	TableDir	32	A distinct activity defined and used in activity based management.
AD_User_ID	TableDir	32	An acquaintance to reach for information related to the business partner.
Isselfservice	YesNo	1	Self-Service allows users to enter data or update their data. The flag indicates, that this record was entered or created via Self-Service or that the user can change it via the Self-Service functionality.
User1_ID	String	32	A display of optional elements that are previously defined for this account combination.
User2_ID	String	32	A display of optional elements that are previously defined for this account combination.
Withholdingamount	Amount	2	Withholding amount
Taxdate	Date	19	Tax Date
C_Withholding_ID	TableDir	32	Withholding
Ispaid	YesNo	1	A confirmation stating whether the request has been closed through a monetary transaction.
Totalpaid	Amount	2	Total amount paid.
Outstandingamt	Amount	2	Outstanding Amount
Daystilldue	Integer	2	Days Till Next Due
Dueamt	Amount	2	Due Amount
Lastcalculatedondate	Date	19	Last Calculated On Date for payment monitor
FIN_Paymentmethod_ID	TableDir	32	It is the method by which payment is expected to be made or received.
Daysoutstanding	Integer	2
Percentageoverdue	Integer	2
Process_Instance_ID	String	32	Identifier that groups temporal invoices created in the same execution.
Create_Invoices	Button	1	Generate (real) invoices from temporal invoices.

Temporal Invoice Lines Table

This table is (almost) a clone of C_INVOICELINE table. Deprecated columns or button columns have been ignored.

OBCNTR_C_INVOICELINE_TEMP
Column Name	Type	Length	Comments
OBCNTR_C_Invoiceline_Temp_ID	ID	32
AD_Client_ID	TableDir	32	Client for this installation.
AD_Org_ID	TableDir	32	Organizational entity within client
Isactive	YesNo	1	A flag indicating whether this record is available for use or de-activated.
Created	DateTime	19	The date that this record is completed.
Createdby	Search	32	User who created this records
Updated	DateTime	19	The date that this record was last updated
Updatedby	Search	32	User who updated this records
C_Orderline_ID	Search	32	A unique and often automatically generated identifier for a sales order line.
M_Inoutline_ID	Search	32	A statement displaying one item, charge, or movement in a shipment.
Line	Integer	12	A line stating the position of this request in the document.
Description	Text	2000	A space to write additional related information.
Financial_Invoice_Line	YesNo	1	This flag will decide whether we are going to book the invoice line on directly general ledger or not.
Account_ID	Table	32	The identification code used for accounting.
M_Product_ID	Search	32	An item produced by a process.
Qtyinvoiced	Quantity	2	The total number of a product included in an invoice to a business partner.
PriceList	Price	2	The official net price of a product in a specified currency.
PriceActual	Price	2	The price that will be paid for a specified item.
PriceLimit	Price	2	The lowest net price a specified item may be sold for.
LineNetAmt	Amount	2	The final amount of a specified line, based only on quantities and prices.
C_Charge_ID	TableDir	32	A cost or expense incurred during business activity.
Chargeamt	Amount	2	The amount of a cost or expense incurred during business activity.
C_Uom_ID	TableDir	32	A non monetary unit of measure.
C_Tax_ID	Table	32	The percentage of money requested by the government for this specified product or transaction.
S_Resourceassignment_ID	Assignment	32	The ID identifies a unique record
TaxAmt	Amount	2	The total sum of money requested by the government of the specified transaction.
M_Attributesetinstance_ID	PAttribute	32	An attribute associated with a product as part of an attribute set.
Isdescription	YesNo	1	if true, the line is just description and no transaction
QuantityOrder	Quantity	2	The number of a certain item involved in the transaction, shown in units which differ from the standard UOM.
M_Product_Uom_ID	Table	32	The unit of measure being used for the request.
C_Invoice_Discount_ID	Search	32
C_Projectline_ID	Search	32	Task or step in a project
M_Offer_ID	TableDir	32	The ability to raise or lower prices.
Pricestd	Price	2	The regular or normal price of a product in the respective price list.
Excludeforwithholding	YesNo	1	Exclude for Withholding
Iseditlinenetamt	YesNo	1	A flag used to allow the end-user to edit Line Net Amount field.
TaxBaseAmt	Amount	2	The total sum on which taxes are added.
Line_Gross_Amount	Amount	2	Gross Amount for a line
Gross_Unit_Price	Price	2	Price inclusive of taxes
Contract_InvoicePlan_ID	Search	32	Reference to the contract invoice plan.
OBCNTR_C_Invoice_Temp_ID	TableDir	32	Reference to the temporal invoice header.

Invoice Plan Table

This table stores the invoicing plan for any contract.

OBCNTR_INVOICEPLAN
Column Name	Type	Length	Comments
OBCNTR_InvoicePlan_ID	ID	32
AD_Client_ID	TableDir	32	Client for this installation.
AD_Org_ID	TableDir	32	Organizational entity within client
Isactive	YesNo	1	A flag indicating whether this record is available for use or de-activated.
Created	DateTime	19	The date that this record is completed.
Createdby	Search	32	User who created this records
Updated	DateTime	19	The date that this record was last updated
Updatedby	Search	32	User who updated this records
C_Project_ID	Search	32	Identifier of a project defined within the Project & Service Management module.
Line	Integer	12	A line stating the position of this request in the document.
Description	Text	255	A space to write additional related information.
Startdate	Date	19	A parameter stating when a specified request will begin.
Enddate	Date	19	A parameter stating when a specified request will end.
Invoicedate	Date	19	Invoice Date
Netunitprice	Price	2	The price that will be paid for a specified item.
Quantity	Quantity	2	The number of a certain item.
Linenetamount	Amount	2	The final amount of a specified line, based only on quantities and prices.
Responsible_ID	Table	32	An employee that is responsible for the project.
Status	String	60	Status of the invoice plan item. The item can be: blocked, not invoiced, partially invoiced, fully invoiced and over invoiced.
Processed	YesNo	1	A confirmation that the associated documents or requests are processed.
Blockinvoice	YesNo	1	Indicates if the invoice plan item is blocking the billing. The invoicing process will show this item as blocked and it will not allow to generate the invoice.
InvoicedQuantity	Quantity	2	The total number of a product included in an invoice to a business partner.

Invoicing Process Configuration Table

This table allows to define the java class responsible of the invoicing process.

OBCNTR_INVOICEPLAN
Column Name	Type	Length	Comments
OBCNTR_InvPro_Config_ID	ID	32	Table primary key.
Client	TableDir	32	Client for this installation.
Organization	TableDir	32	Organizational entity within client
Creation Date	DateTime	19	The date that this record is completed.
Created By	Search	32	User who created this records
Updated	DateTime	19	The date that this record was last updated
Updated By	Search	32	User who updated this records
Active	YesNo	1	A flag indicating whether this record is available for use or de-activated.
Name	String	60	A non-unique identifier for a record/document often used as a search tool.
Description	Text	2000	A space to write additional related information.
Java Class Name	Text	1000	The classname is used when generating a representation of the table in java (the entity or business object). The classname is the simplename of the class (so without the package name). Often the AD_Table.name can be used here.

Modified Core tables

C_PROJECT
Column Name	Type	Length	Comments
EM_OBCNTR_CREATE_INVPLAN	CHAR	1	Button to create the invoice plan.
EM_OBCNTR_INVFREQUENCY	LIST {M, BW}	60	Invoicing frequency: monthly, bi-weekly.
EM_OBCNTR_PERIODDAY	INTEGER	N/A	Day of the period when the invoice should be generated. Default value = 1
EM_OBCNTR_AMOUNT_PER_PERIOD	AMOUNT	N/A	Amount to invoice in each period. Default value = 0
EM_OBCNTR_INVOICING_TYPE	LIST {FIX, VAR}	60	Invoicint type: fix, variable.

C_INVOICELINE
Column Name	Type	Length	Comments
EM_OBCNTR_INVOICEPLAN_ID	CHAR	32	Reference to the invoice plan (OBCNTR_INVOICEPLAN table).

Window & Menu entries

The following windows, with their corresponding tabs and fields will be implemented. Also, a menu entry will be created for each of them.

Windows
Name	Description
Sales Invoice Proposal	It contains all the information about the temporal invoices that potentially will become real invoices. Does not have any menu entry. This window popups in the middle of the invoicing process to preview the invoices suggested by this engine.
Invoice Process Configuration	Configure the Java class in charge of the invoicing process. Each module can extend the invoicing process.

Message

Messages
Name	Type	Message Text
OBCNTR_BlockedNoPossibleInvoice	Information	Some of the selected invoices are blocked. It is not allowed to invoice a blocked invoice.
OBCNTR_BusinessPartner	Information	Business Partner
OBCNTR_CreateInvoiceResult	Success	%0 invoice(s) created
OBCNTR_EndDate	Information	Ending Date
OBCNTR_InvalidDateRange	Error	Invalid date range.
OBCNTR_InvoicePlanCreateError	Error	Error creating the invoice plan. Please, review invoice plan configuration fields.
OBCNTR_MandatoryField	Error	This field is needed and it cannot be blank
OBCNTR_MoreThanOneInvoiceProcess	Error	You have more than one active invoice process defined for the same client.
OBCNTR_NoInvoiceProcessDefined	Error	No invoice process is defined. Please make sure you have applied the dataset.
OBCNTR_StartDate	Information	Starting Date
OBCNTR_ZeroAmount	Error	Zero is not a valid amount.

Reference

References
Name	Description	Parent Reference	Values
Contract Invoice Frequency	Contract Invoice Frequency	List	M (Monthly), BW (Bi-Weekly)
Contract Invoice Type	Contract Invoice Type	List	FIX (Fix), VAR (Variable)
Invoice Plan	Invoice Plan Search Reference	Search	Selector Reference - Table: OBCNTR_InvoicePlan - Column: OBCNTR_InvoicePlan_ID

Process Definition

Creates real invoices from a list of temporal invoices. It is not an standard process because we need multiple selection in the window.

• Search Key: OBCNTR_CreateInvoices
• Name: Create Invoices
• Data Access Level: Organization
• Handler: OB.OBCNTR.Process.createInvoices
• UI Pattern: Manual

Dataset

It populates the OBCNTR_InvPro_Config table with the Java class that will drive the invoicing process.

• Search Key: Standard Contract Invoice Process
• Name: Standard Contract Invoice Process
• Data Access level: System/Client
• Export allowed: Y

• Table: OBCNTR_InvPro_Config
• HQL Where clause: name = 'Standard Contract Invoice Process'
• Include All Columns: Y
• Exclude Audit Info: N
• IsBusinessObject: N

Dataset Info

<?xml version="1.0" encoding="UTF-8"?>
<ob:Openbravo xmlns:ob="http://www.openbravo.com"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <OBCNTR_InvPro_Config id="D261F05B04C04085A555BB6FDBDE6962" identifier="Standard Contract Invoice Process">
   <id>D261F05B04C04085A555BB6FDBDE6962</id>
   <creationDate transient="true">2012-09-21T09:12:36.624Z</creationDate>
   <createdBy transient="true" id="100" entity-name="ADUser" identifier="Openbravo"/>
   <updated transient="true">2012-09-21T09:12:36.624Z</updated>
   <updatedBy transient="true" id="100" entity-name="ADUser" identifier="Openbravo"/>
   <active>true</active>
   <name>Standard Contract Invoice Process</name>
   <description xsi:nil="true"/>
   <javaClassName>org.openbravo.contract.process.StandardContractInvoiceProcess</javaClassName>
 </OBCNTR_InvPro_Config>
</ob:Openbravo>

Stored Function

This function will return the first day of the period for the given frequency. Supported frequencies are: 'M' monthly, 'Q' quarter, 'W' weekly, BW' bi-weekly

• Name: OBCNTR_GET_PERIOD_FIRST_DAY
• IN parameters: (Date p_date, VARCHAR frequency)
• OUT: first day for the given frequency.

Examples
ID	(IN) Date	(IN) Frequency	Output
Example 1	25/05/2012	Monthly	01/05/2012
Example 2	25/05/2012	bi-weekly	16/05/2012
Example 3	25/05/2012	quarter	01/04/2012

 v_StartDate DATE;
 v_EndDate DATE;
 v_month_aux NUMBER;
 v_day_aux NUMBER;
 v_february NUMBER:= 0;
 
BEGIN --BODY
 
 IF(p_date IS NULL) THEN
  v_StartDate := now() ;
 END IF;
 
 IF (frequency='Q') THEN -- Quarter
   v_StartDate:=TRUNC(p_date, 'Q') ;
   v_EndDate:=TRUNC(v_StartDate+93, 'Q') - 1;
   
 ELSIF (frequency='W') THEN -- Weekly
   v_StartDate:=TRUNC(p_date, 'DAY') ;
   v_EndDate:=TRUNC(v_StartDate+7, 'DAY') - 1;
  
 ELSIF (frequency='BW') THEN -- Bi Weekly
   SELECT to_number(to_char(p_date, 'DD')) INTO v_day_aux FROM dual;
   SELECT to_number(to_char(p_date, 'MM')) INTO v_month_aux FROM dual;

   IF (v_month_aux = 2) THEN -- February
     v_february := 1;
   END IF;
   
   IF (v_day_aux < (16 - v_february)) THEN
     v_StartDate:=TRUNC(p_date, 'MM');
     v_EndDate:=TRUNC(v_StartDate, 'MM') + (14 - v_february);
   ELSE
     v_StartDate:=TRUNC(p_date, 'MM') + (15 - v_february);
     v_EndDate:=TRUNC(v_StartDate+20, 'MM') - 1;
   END IF;
   
 ELSIF (frequency='M') THEN -- Month
   v_StartDate:=TRUNC(p_date, 'MM');
   v_EndDate:=TRUNC(v_StartDate+32, 'MM') - 1;
   
 ELSE
   RAISE NO_DATA_FOUND;
 END IF;
   
 RETURN v_StartDate;
 
EXCEPTION
WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('obcntr_GET_PERIOD_FIRST_DAY exception');
 RAISE;
END OBCNTR_GET_PERIOD_FIRST_DAY

Java artifacts

Event Handler

OBCNTR_C_InvoiceLine_Temp table will have an event handler assigned.

• Java Class: org.openbravo.contract.eventhandler.InvoiceLineTempEventHandler

It will handle any change in the temporal invoice lines updating the parent total amounts.

• onSave: update total net amount of the invoice temp header.
• onUpdate: update total net amount of the invoice temp header.

Create Invoice Plan

• Java Class Name: org.openbravo.contract.process.CreateInvoicePlan

Input parameters:

• Date from
• Date to
• Frequency: Monthly or bi-weekly
• Day of the period: invoice date
• Amount per period: amount to invoice in each period

Output: it will populate the OBCNTR_Invoice_Plan table. Based on the frequency this process splits the date range in periods and assigns to each period the proportional amount.

JavaScript artifacts

View Implementation

Invoicing Process Filter window

It should be possible to select which contracts to select for billing by specifying the following selection criteria:

• Business Partner
• Organization
• Date From
• Date To
• Contract
• Product: contract/orders including these products.
• Previous: include invoices prior to the starting date.
• Current: include invoices in the date range selected.
• Blocked: show also blocked invoice plan items.
• From Order: invoices coming from orders.
• From Contract: invoices coming from invoice plans.

Only date from - date to parameters are mandatory. The selection should return a list of potential invoices; users should be able to pick them all or some and process them.

Class Diagram

Category: WorkInProgress