Projects:Contract Management/Technical Specification
Contents |
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.