IntraOrgBookingOfIS/Technical Specification

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.

Introduction

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:

1. New check box Automatic Internal Settlement is to be provided in Document Type window.
2. New Option Called Internal Settlement is to be added into Form of Payment list reference.
3. Validation to be done - Internal Settlement as Form of Payment, when Document Type is Inter-company.
4. Validation to be done - Exempt as default in Tax field in Lines tab, when Document Type is Inter-company
5. Create a new procedure that implements following behavior and call it from INTERCO_CREATE_INVOICE procedure.
   1. Automatic creation of Settlement should be done to cancel the Payments generated by both transactions.

Module Definition

• 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

Technical Implementation

Final Approach

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.

3. 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.

4. 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.

     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  ---

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

1. 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.