Purpose of the Project
The purpose of this document is to describe the Technical Specifications required for the project called IntraOrg Booking of Internal Service. And these technical requirements are added into an already existing extension module called Intercompany Invoicing developed in OB ERP version 2.50. The version of this modified module is to be set to 2.0.0.
This Technical Specification document outlines the implementation of IntraOrg Booking of Internal Service feature.
High Level Functional Requirements
This module will provide the following processes:
- New check box Automatic Internal Settlement is to be provided in Document Type window.
- New Option Called Internal Settlement is to be added into Form of Payment list reference.
Validation to be done - Internal Settlement as Form of Payment, when Document Type is Inter-company. Validation to be done - Exempt as default in Tax field in Lines tab, when Document Type is Inter-company
- Create a new procedure that implements following behavior and call it from INTERCO_CREATE_INVOICE procedure.
- Automatic creation of Settlement should be done to cancel the Payments generated by both transactions.
- Name: Inter-company Documents
- Version: 2.0.0
- Java Package: org.openbravo.interco
- Description: This module provides the ability to manage inter-company documents (orders and invoices) and to automatically generate the matching document when an inter-company transaction is initiated.
- Dependency with: core
- Data Package name:The Interco Data Package
- Data Package Description/Java Package: org.openbravo.interco
- DB Prefix:INTERCO
- 1. New check box Automatic Internal Settlement is to be provided in Document Type window.
- 1.1 Create a new column EM_INTERCO_IS_INTLSETTLEMENT in C_DOCTYPE table.
ALTER TABLE c_doctype ADD em_interco_is_intlsettlement CHAR(1) DEFAULT 'N' NOT NULL ENABLE;
- 1.2 Go to the table window and do import of columns from database, this will import the new column into the Application Dictionary.
- 1.3 Correct/add column information if needed.
- 1.4 Add a new field Automatic Internal Settlement to the window Document Type.
- 1.5 To link the new column and field to a label (i.e. an Element) run the Synchronize Terminology process.
- After running this process there should be a new Element record called Automatic Internal Settlement.
- 2. New Option Called Internal Settlement is to be added into Form of Payment list reference.
Validation to be done - Internal Settlement as Form of Payment, when Document Type is Inter-company.
- RMO: This requirement can be removed. Form of Payment should be correctly (manually) configured as "Internal Settlement" when setting up the Business Partner.
Validation to be done - Exempt as default in Tax field in Lines tab, when Document Type is Inter-company
- RMO: This requirement can be removed. Tax status of "Tax Exempt" should be correctly (manually) configured when setting up the Business Partner.
- 5. New Procedure INTERCO_AUTO_SETTLEMENT is to be created and called from the procedure INTERCO_CREATE_INVOICE.
- Create a new custom procedure INTERCO_AUTO_SETTLEMENT which does the following tasks.
create or replace PROCEDURE INTERCO_AUTO_SETTLEMENT(p_invoice_id IN VARCHAR2, p_target_invoice_id IN VARCHAR2) AS TYPE RECORD IS REF CURSOR; Cur_Parameter RECORD; v_C_Debt_Payment_ID VARCHAR2(32); v_Paymentrule VARCHAR2(32); v_Description VARCHAR2(32); v_SettlementDocType_ID VARCHAR2(32) ; v_settlementID varchar2(32) ; v_DocumentNo VARCHAR2(50); v_AD_Org_ID VARCHAR2(32); v_AD_Client_ID VARCHAR2(32) ; v_isActive VARCHAR2(32); v_dateAcct DATE; ..... BEGIN .....
- 1. insert a new record in C_SETTLEMENT table
:Here Settlement Type is set as 'B' and document number is concatenated with '*INS*'(to indicate Internal Settlement)
INSERT INTO C_SETTLEMENT( C_SETTLEMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, DOCUMENTNO, DATETRX, DATEACCT, SETTLEMENTTYPE, C_DOCTYPE_ID, PROCESSING, PROCESSED, POSTED, C_CURRENCY_ID, C_PROJECT_ID, C_CAMPAIGN_ID, C_ACTIVITY_ID, USER1_ID, USER2_ID, CREATEFROM, ISGENERATED) VALUES( v_settlementID, v_AD_Client_ID, v_AD_Org_ID, v_IsActive, now(), v_CreatedBy, now(), v_UpdatedBy, '*INS*'|| v_DocumentNo, v_DateInvoiced, v_DateAcct, 'B', v_SettlementDocType_ID, 'N','N', 'N', v_currencyID, NULL, NULL, NULL, NULL, NULL,'N', 'Y');
- 2. update the table C_DEPT_PAYMENT.
FOR Cur_Parameter IN (SELECT * FROM c_invoice where c_invoice_id in (p_invoice_id, p_target_invoice_id)) LOOP UPDATE C_DEBT_PAYMENT SET C_Settlement_Cancel_Id=v_settlementID, updated=now(), IsPaid='Y', updatedby=v_updatedby WHERE c_invoice_id like Cur_Parameter.c_invoice_id ; END LOOP;
- 3. calls C_SETTLEMENT_POST() procedure along with necessary parameters.
::C_SETTLEMENT_POST(NULL, v_settlementID) ;
- After having discussion with Eduardo, since the following two approaches are not feasible, they are removed.
- In this approach, while completing invoice(either Purchase or Sales), we can stop creating payments. This can be done by removing the created payment records from C_DEPT_PAYMENT table, if it met certain condition (DocType is equal to Inter company), by using the concept of Extension Point.
Changes in C_INVOICE_POST procedure
- Completing the Purchase Invoice/ Sales Invoice will invoke the C_INVOICE_POST stored procedure.
- So, here we need to create an extension point that calls another custom procedure which removes the record (payment record) that is created by C_INVOICE_POST, if the document type is Inter Company.
v_pendingAmount:=v_pendingAmount - v_paymentAmount; Ad_Sequence_Next('C_Debt_Payment', v_Record_ID, v_debtPaymentID) ; INSERT INTO C_DEBT_PAYMENT ( C_DEBT_PAYMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, ISRECEIPT, C_SETTLEMENT_CANCEL_ID, C_SETTLEMENT_GENERATE_ID, DESCRIPTION, C_INVOICE_ID, C_BPARTNER_ID, C_CURRENCY_ID, C_CASHLINE_ID, C_BANKACCOUNT_ID, C_CASHBOOK_ID, PAYMENTRULE, ISPAID, AMOUNT, WRITEOFFAMT, DATEPLANNED, ISMANUAL, ISVALID, C_BANKSTATEMENTLINE_ID, CHANGESETTLEMENTCANCEL, CANCEL_PROCESSED, GENERATE_PROCESSED, c_project_id, status, status_initial, withholdingamount,C_WITHHOLDING_ID ) VALUES ( v_debtPaymentID, v_Client_ID, v_Org_ID, 'Y', now(), v_UpdatedBy, now(), 'v_UpdatedBy', v_IsSOTrx, NULL, NULL, '* ' || v_DocumentNo || ' * (' || COALESCE(TO_CHAR(v_BPartnerName) ,) ||(CASE WHEN v_POReference IS NULL THEN ELSE ' .Ref:'||TO_CHAR(v_POReference) END) || ' )', v_Record_ID, v_BPartner_ID, v_Currency_ID, NULL, v_bankAccount, v_cashBook, CUR_PAYMENTS.PaymentRule, 'N', C_Currency_Round((v_paymentamount *v_multiplierarc) -withholdamount *(v_paymentamount *v_multiplierarc / v_grandtotal), v_Currency_ID, NULL), 0, v_plannedDate, 'N', 'Y', NULL, 'N', 'N', 'N', v_C_Project_Id, 'DE', 'DE', C_Currency_Round(withholdamount *(v_paymentamount *v_multiplierarc / v_grandtotal), v_Currency_ID, NULL),cWithHoldId ) ; --- create an extension point that calls another stored procedure ---
- In this approach, After C_INVOICE_POST() completion, we could directly call the stored procedure invoked from the process button in Payment Status Management window, to change the status of the Payments created by Purchase/Sales Invoices.
- The Payment status is changed as Received.
Open Discussion Items
- 1. while inserting a new record in C_SETTLEMENT table, Settlement Type is set as B. Shall we keep it as it is or do we need to create a new Settlement Type?
Please Note: Currently, there are 3 Settlement Types existing (B, I & C).
- 2. while inserting a new record in C_SETTLEMENT table, Document number is concatenated with '*INS*'(to indicate Internal Settlement). Is it Valid?
Closed Discussion Items
How do we manage the Invoice Posting? Is there any functional behavior required for this?
- As per Eduardo comments, there is no need of development for Posting process.