View source | Discuss this page | Page history | Printable version   

IntraOrgBookingOfIS/Technical Specification

Contents

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

Technical Implementation

Final Approach

1. New check box Automatic Internal Settlement is to be provided in Document Type window.

Document type window.png

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.

Dbcolumn window.png

1.4 Add a new field Automatic Internal Settlement to the window Document Type.

Field window.png

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.

Element window.png

2. New Option Called Internal Settlement is to be added into Form of Payment list reference.

Paymentrule Refernce.png

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.

Approach 1

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

Approach 2

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.

Payment Status Mgmt 1.png


Payment Status Mgmt 2.png


Payment Status Mgmt 3.png

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.

Retrieved from "http://forge.openbravo.com/plugins/mwiki/index.php/IntraOrgBookingOfIS/Technical_Specification"

This page has been accessed 15,901 times. This page was last modified on 23 November 2009, at 07:21. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.