View source | View content page | Page history | Printable version   

Projects:Contract Management/Technical Specification

Warning.png   This document is still a work in progress. It may contain inaccuracies or errors.

Contents

Overview

This article will explain technical infrastructure of Contract Management module.

Module definition

List of artifacts

New tables

The following tables will be created for this module:

The following core tables will have some fields added:


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.

Dataset

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

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

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.

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

Create Invoice Plan

Input parameters:

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:

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

Contract Management Class Diagram
Invoicing Process Class Diagram

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Contract_Management/Technical_Specification"

This page has been accessed 4,674 times. This page was last modified on 25 September 2012, at 15:42. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.