Accruals Deferrals/Technical Documentation

Contents 1 Purpose of the Project 2 Introduction 3 Technical implementation 4 Required Files 4.1 Module Definition 5 New database objects 5.1 New columns in C_INVOICE table 5.2 New columns in C_ACCTSCHEMA_DEFAULT table 5.3 New table 5.4 New trigger 5.4.1 ACCDEF_Plan_Trg 5.4.2 ACCDEF_Invoice_Trg 6 Application Dictionary 6.1 Fields 6.1.1 Accruals and Deferrals Field Category 6.1.2 Purchase and Sales windows 6.1.3 Accounting Schema window 6.2 Reference 6.2.1 Accruals and Deferrals types 6.3 Validation Setup 6.3.1 Deferral types 6.4 Tab 6.5 Extension Point setup 6.6 Messages 7 Reference data 8 Processes 8.1 Accruals and Deferrals Plan generation 8.1.1 Summary 8.1.2 Structure of the Procedure 8.1.3 Beginning and parameter retrieval 8.1.4 End of the procedure 8.1.5 Defined date range correct check 8.1.6 Accruals and Deferrals type calculation 8.1.7 Plan lines not posted check 8.1.8 Existence check of previously created plans 8.1.9 Allocation amount calculation 8.1.10 Plan lines creation 8.2 Accruals and Deferrals Plan posting 8.2.1 Technical Requirements 8.2.2 Process 8.3 Accruals and Deferrals Plan unposting 8.3.1 Technical Requirement 8.3.2 Summary 8.3.3 Reactivate Funcnalities 9 Dependencies 10 To test 11 Open discussion items 12 Closed discussion items

Purpose of the Project

The purpose of this document is to describe the technical Specifications for an extension module to be developed in OB ERP version 2.50 called Accruals and Deferrals.

Introduction

This technical specifications document outlines the implementation of Accruals and Deferrals feature.

Technical implementation

This module will provide three processes:

1. to generate the Accruals and Deferrals Plan,
2. to generate the needed accounting entries and
3. to unpost the Acc. and Def. plan lines when its invoice is unposted.

It will also include new fields to configure the plan and a new tab to review it.

Required Files

A new .obx file will be provided containing the new fields and tab and the 2 processes properly configured.

Module Definition

• Name: Accruals and Deferrals
• Java Package: org.openbravo.accrualsanddeferrals
• Description/Help: Module to automatically generate Accruals and Deferrals plans and their posting.
• Dependency with core
• Data Package name: Accruals and Deferrals data package
• Data Package Description/Java Package: org.openbravo.accrualsanddeferrals
• DB Prefix: ACCDEF

New database objects

New columns in C_INVOICE table

• EM_ACCDEF_DATEFROM DATE
• EM_ACCDEF_DATETO DATE
• EM_ACCDEF_TYPE VARCHAR2(60)

EM_ACCDEF_IS_ACCDEF requires a check constraint as the isActive columns (EM_ACCDEF_IS_ACCDEF IN ('Y', 'N')).

PostgreSQL alter table sql:

ALTER TABLE C_INVOICE ADD CONSTRAINT accdef_cinvoice_isaccdef_check CHECK (EM_ACCDEF_IS_ACCDEF = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]));

The reference of EM_ACCDEF_TYPE has to be set to List, the Reference Search to Accruals and Deferrals types and the validation to Accruals or Deferrals.

New columns in C_ACCTSCHEMA_DEFAULT table

• EM_ACCDEF_PREPAID_EXPENSE VARCHAR(32), FOREIGN KEY with C_ValidCombination
• EM_ACCDEF_DEFERRED_INCOME VARCHAR(32), FOREIGN KEY with C_ValidCombination
• EM_ACCDEF_OTHER_RECEIVABLES VARCHAR(32), FOREIGN KEY with C_ValidCombination
• EM_ACCDEF_OTHER_LIABILITIES VARCHAR(32), FOREIGN KEY with C_ValidCombination
• EM_ACCDEF_OUT_PERIOD_EXPENSES VARCHAR(32), FOREIGN KEY with C_ValidCombination
• EM_ACCDEF_OUT_PERIOD_REVENUES VARCHAR(32), FOREIGN KEY with C_ValidCombination

New table

A new table is needed to store the Accruals and Deferrals Plans

• ACCDEF_PLAN
  • ACCDEF_Plan_ID VARCHAR(32) NOT NULL,
  • AD_Client_ID VARCHAR2(32) NOT NULL, FOREIGN KEY with AD_Client
  • AD_Org_ID VARCHAR2(32) NOT NULL, FOREIGN KEY with AD_Org
  • IsActive CHAR(1 BYTE) DEFAULT 'Y' NOT NULL, CHECK CONSTRAINT IsActive IN ('Y','N')
  • Created DATE DEFAULT SYSDATE NOT NULL,
  • CreatedBy VARCHAR2(32) NOT NULL,
  • Updated DATE DEFAULT SYSDATE NOT NULL,
  • UpdatedBy VARCHAR2(32) NOT NULL,
  • C_Invoice_ID VARCHAR(32) NOT NULL, FOREIGN KEY with C_Invoice, Link to Parent Column checked
  • Line NUMBER(10) NOT NULL
  • Amount NUMBER NOT NULL,
  • Acct_Date DATE NOT NULL,
  • Posted CHAR(1 BYTE) DEFAULT 'N' NOT NULL, Reference: Button, Reference Search Key: All Posted Status

Change the proposed default value of the Line column in the Application Dictionary to:

@SQL=SELECT COALESCE(MAX(Line),0)+10 AS DefaultValue FROM ACCDEF_Plan WHERE C_Invoice_ID=@C_Invoice_ID@

New trigger

2 new triggers are needed.

ACCDEF_Plan_Trg

New trigger called ACCDEF_Plan_Trg for ACCDEF_Plan table to not allow to modify the Line, Acct_Date and Amount columns when the Posted column equals 'Y'.

And also it does not allow to insert any new Acc.Def. Plan lines when Accruals and Deferrals section in Header is empty.

Take the C_Invoice_Trg as example.

IF UPDATING THEN
  IF(:OLD.Posted='Y'
    AND ((COALESCE(:OLD.LINE, '0') <> COALESCE(:NEW.LINE, '0'))
    AND --insert the other columns
  ) THEN
    RAISE_APPLICATION_ERROR(-20501, 'Document processed/posted') ;
  END IF;
END IF;
IF(DELETING) THEN
  IF(:OLD.POSTED='Y') THEN
    RAISE_APPLICATION_ERROR(-20501, 'Document processed/posted') ;
  END IF;
END IF;

IF (UPDATING OR INSERTING) THEN
 v_InvoiceID := :NEW.c_invoice_id;
ELSE
 v_InvoiceID := :OLD.c_invoice_id;
END IF;
BEGIN
 SELECT em_accdef_type, em_accdef_datefrom, em_accdef_dateto
  INTO v_AccDefType, v_AccDefDateFrom, v_AccDefDateTo
  FROM c_invoice
  WHERE c_invoice.c_invoice_id=v_InvoiceID;
EXCEPTION
WHEN others THEN
  v_AccDefType:=NULL;
  v_AccDefDateFrom:=NULL;
  v_AccDefDateTo:=NULL;
END;
IF (UPDATING OR INSERTING) THEN
 IF(v_AccDefType IS NULL OR v_AccDefDateFrom IS NULL OR v_AccDefDateTo IS NULL) THEN
  RAISE_APPLICATION_ERROR(-20000, '@ACCDEF_TypeNotEmpty@');
 END IF;
END IF;

ACCDEF_Invoice_Trg

New trigger called ACCDEF_Invoice_Trg for C_Invoice table to not allow to change the EM_ACCDEF_IS_ACCDEF column value when the invoice is completed.

Take the C_Invoice_Trg and previous one as example.

Application Dictionary

New objects should be registered in Application Dictionary as follows:

Fields

Accruals and Deferrals Field Category

Create a new Field Category called Accruals and Deferrals

Window Path: Application Dictionary || Setup || Field Category || Field Category

Purchase and Sales windows

Add the fields related to the columns added in the C_Invoice table in the Header tab of Purchase Invoice and Sales Invoice windows. All the fields belong to the newly defied Accruals and Deferrals field category.

Period From, Period To and Allocation Account must have a display logic so they are only visible when the Accruals and Deferrals flag is checked.

@EM_ACCDEF_IS_ACCDEF@='Y'

Check the mock-up of the Functional Specs to view how to setup the fields in the tab.

Accounting Schema window

Add the fields related to the new columns of the C_AcctSchema_Default table in the Defaults tab.

• EM_ACCDEF_PREPAID_EXPENSE -> Name: Prepaid expenses
• EM_ACCDEF_DEFERRED_INCOME -> Name: Deferred income
• EM_ACCDEF_OTHER_RECEIVABLES -> Name: Other receivables
• EM_ACCDEF_OTHER_LIABILITIES -> Name: Other liabilities
• EM_ACCDEF_OUT_PERIOD_EXPENSES -> Name: Out of period expenses
• EM_ACCDEF_OUT_PERIOD_REVENUES -> Name: Out of period revenues

Reference

Accruals and Deferrals types

Create a new List type reference called Accruals and Deferrals types

Values are:

• PE: Prepaid expenses
• DI: Deferred income
• OR: Other receivables
• OL: Other liabilities

Validation Setup

Deferral types

Create a new validation called Accruals or Deferrals of type SQL.

Validation code:

((@IsSOTrx@ = 'Y' AND AD_REF_LIST.Value IN ('DI', 'OR')) OR (@IsSOTrx@ = 'N' AND AD_REF_LIST.Value IN ('PE', 'OL')))

Tab

Create new tabs called Accruals and Deferrals Plan under the Header of the Purchase Invoice and Sales Invoice windows. Check the last proposed mock-up to know the order and design of the fields.

The tabs are based on the new table ACCDEF_Plan. Notice that the Allocation Account field is not needed.

Extension Point setup

In Application Dictionary || Setup || Extension Points || Extension Point window. Set a new Procedure for the C_Invoice_Post - Finish Process extension point. The procedure name is ACCDEF_GENERATE_PLAN.

Messages

New messages, used by the processes, are needed:

• ACCDEF_PostedPlanLines: There is an Accruals and Deferrals Plan with posted lines.
• ACCDEF_WrongPeriodRange: The period range is not properly configured; please check the Period From and Period To fields.
• ACCDEF_PeriodNotAvailableForDate: The period is not yet open for the given date.
• ACCDEF_TypeNotEmpty: Acc. and Def. section in Header can not be empty when creating Acc.Def. Plan Lines.

Reference data

Processes

Three processes are needed:

1. First one developed using PL/SQL called by an extension point,
2. second one to post the plan lines extending AcctServer class and
3. the last one also developed using PL/SQL called by an extension point.

Accruals and Deferrals Plan generation

Summary

This PL/SQL procedure, ACCDEF_GENERATE_PLAN, will be executed in the C_Invoice_Post - Finish Process extension point. This is, it will be executed each time that an Invoice is processed. To do so it has to be defined in the Extension Point window.

An invoice can be processed several times with different actions. It can be being completed (from draft status to completed CO docaction), reactivated, closed,... In this process, if the EM_ACCDEF_IS_ACCDEF flag is checked, it is only needed to take care of the completion CO docAction, to generate the plan, and the reactivation RE docAction, as it is only possible to reactivate it if the plan is not posted.

When the invoice is being completed it is possible to have several scenarios that have to be taken into account.

• Already exists lines in the plan tab. The user might have inserted the plan manually or it might be a reactivated invoice, in any case it is not necessary to generate it again. But it is necessary to check that there is one line for each period in the defined range and that all the accounting dates are set to the 1st of each month.
• It is already set the Accruals and Deferrals type. The user might have set it manually or it might be a reactivated invoice. It has to be checked that the defined date range is correctly set based on the type.
• The Date From date has to be updated to the 1st of the month.
• The Date To date has to be updated to the last day of the month.

When the plan is generated one line per period has to be created. The amount of the invoice has to be allocated following a linear distribution.

The next sections describe in more detail each part of the procedure.

Structure of the Procedure

BEGIN
  Retrieve parameters
  Retrieve necessary data from the C_Invoice using the p_Record_ID (SELECT INTO statement)
  IF (v_DateFrom IS NOT NULL AND v_DateTo IS NOT NULL AND v_AccDefType IS NOT NULL AND p_DocAction IN ('CO', 'RE')) THEN
    IF (v_DateFrom is null OR v_DateTo is null) THEN
      RAISE EXCEPTION @ACCDEF_WrongPeriodRange@
    END IF;
    IF (p_DocAction='CO') THEN
    
      IF (v_AccDefType IS NOT NULL) THEN
        Check that the defined date range is correct
      ELSE
        Calculate the Accruals and Deferrals type based on the period range
      END IF;

      Update the EM_AccDef_DateFrom (first day of the month), 
      EM_AccDef_DateTo (last day of the month) and 
      EM_AccDef_Type (with calculated type) columns of C_Invoice table.

      IF (not exists lines in ACCDEF_PLAN table for the v_Record_ID) THEN
        Calculate the allocation amount
        Insert the lines
      END IF;
    ELSIF (p_DocAction='RE') THEN
      Check that the plan lines are not posted, if there is one or more posted raise an exception.
    END IF;
  END IF;
  Update AD_EP_Instance_Para table.
END;

Beginning and parameter retrieval

As it is executed by the Extension Points functionality the procedure only has 1 parameter, p_ep_instance. This parameter is an uuid identifier that will allow us to retrieve the parameters that are available in this extension point (p_record_id, p_docaction, p_user, p_result and p_message). This parameters are stored in the AD_EP_Instance_Para table, there is a record for each parameter identified by the parameter name and the ep_instance_id.

Code example of the beginning of the procedure including the retrieval of the parameters:

create or replace
PROCEDURE ACCDEF_GENERATE_PLAN(p_ep_instance IN VARCHAR2) 

AS
/*************************************************************************
* The contents of this file are subject to the Openbravo  Public  License
...
************************************************************************/

  p_message VARCHAR2(2000);
  p_record_id VARCHAR2(60);
  p_docAction VARCHAR2(60);
  p_user VARCHAR2(60);
  p_result NUMBER;

  --Add more variables if needed
  --...
  
  -- Parameter
  TYPE RECORD IS REF CURSOR;
  Cur_Params RECORD;

BEGIN

  FOR Cur_Params IN (
    SELECT *
    FROM ad_ep_instance_para
    WHERE ad_ep_instance_id = p_ep_instance) LOOP
    IF (cur_params.parametername LIKE 'DocAction') THEN
      p_docaction := Cur_Params.p_string;
    ELSIF (cur_params.parametername LIKE 'Record_ID') THEN
      p_record_id := cur_params.p_string;
    ELSIF (cur_params.parametername LIKE 'User') THEN
      p_user := cur_params.p_string;
    ELSIF (cur_params.parametername LIKE 'Message') THEN
      p_message := cur_params.p_text;
    ELSIF (cur_params.parametername LIKE 'Result') THEN
      p_result := cur_params.p_number;
    END IF;
  END LOOP;

Parameter values:

• DocAction is the action of the process.
• Record_ID is the C_Invoice_ID of the invoice that it is being processed.
• User_ID is the AD_User_ID of the user that has launched the process, it is used to set the UpdatedBy and CreatedBy columns.
• Result is the integer to set the result of the process. If it is desired to change it at the end of the process is necessary to update the AD_EP_Instance_Para table.
• Message is the message that appears in the message box of the application. As the Result parameter it is necessary to update the AD_EP_Instance_Para table if it is desired to change it.

End of the procedure

All the exceptions that are Raised in the procedure has to be raised to the parent procedure in the exception block. Just before the final END of the procedure is updated the AD_EP_Instance_Para table with the modified values of p_Message and p_Result.

Code example of the end of the procedure including the updates to the AD_EP_Instance_Para table and the Exception block.

  UPDATE ad_ep_instance_para
  SET p_text = (CASE WHEN p_text IS NULL OR p_text= THEN p_message ELSE TO_CHAR(p_text) || '
' || p_message END)
  WHERE ad_ep_instance_id = p_ep_instance
  AND parametername LIKE 'Message';

  UPDATE ad_ep_instance_para
  SET p_number = p_result
  WHERE ad_ep_instance_id = p_ep_instance
  AND parametername LIKE 'Result';

EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('ACCDEF_GENERATE_PLAN exception') ;
  RAISE;
END ACCDEF_GENERATE_PLAN
;

Defined date range correct check

This check is done when the Accruals and Deferrals transaction type is already set in the invoice.

Rules that have to be fulfilled:

• DateFrom before than DateTo
• Transitory transactions (Prepaid Expenses and Deferred Income)
  • Month of DateFrom same or next than DateAcct
• Anticipatory transactions (Other Liabilities and Receivables)
  • Month of DateAcct same or next than DateTo
  • Year of DateAcct same or next than DateTo

If a rule is not fulfilled raise a ACCDEF_WrongPeriodRange exception.

RAISE_APPLICATION_ERROR(-20000, '@ACCDEF_WrongPeriodRange@');

Accruals and Deferrals type calculation

To generate the plan it is needed to know the type of Accruals and Deferrals of the document. It is also needed to ensure that the period definition is correct, see the Technical Requirements in the Functional Specs. There are 4 types of Accruals and Deferrals. To get the type is necessary to retrieve some column values from the C_Invoice table: IsSOTrx, DATEACCT, EM_ACCDEF_DATEFROM and EM_ACCDEF_DATETO.

If IsSOTrx='Y' it is a sales transaction, so it is a deferral, deferred income or other receivables. If IsSOTrx='N' it is a purchase transaction, so it is an accrual, prepaid expenses or other liabilities.

To check if it is transitory or anticipatory is needed to check the date range defined by EM_ACCDEF_DATEFROM and EM_ACCDEF_DATETO columns and compare it to the DATEACCT.

If the date range is defined before the DateAcct is Anticipatory, Other liabilities or Other receivables If the date range is defined after the DateAcct is Transitory, Deferred income or Prepaid expenses

There are also other rules that must be fulfilled as seen in the Defined date range correct check section.

To facilitate the checks is used auxiliary DateAcct and DateTo which are the same date moved to the first day of the month.

v_AuxDateAcct := TO_DATE('01-'||TO_CHAR(v_DateAcct, 'MM-YYYY'), 'DD-MM-YYYY');
v_AuxDateTo := TO_DATE('01-'||TO_CHAR(v_DateTo, 'MM-YYYY'), 'DD-MM-YYYY');

Check if it is transitory. The DateFrom date should be in the same period than DateAcct or in a period later. This is, DateFrom should be in the same month than AuxDateAcct or in the next month.

IF (v_DateFrom - v_AuxDateAcct <=31 AND v_DateFrom - v_AuxDateAcct >=0) THEN
  IF (v_IsSOTrx = 'Y') THEN
    v_AccDefType := 'DI'; --Deferred income
  ELSE
    v_AccDefType := 'PE'; --Prepaid expenses
END IF;

Check if it is anticipatory. The DateTo date should be in the same period than DateAcct or in a period before. This is, DateTo should be in the same month than DateAcct or in the month before. The PeriodFrom has to be in the same year of the DateAcct or in the previous year.

IF (v_AuxDateAcct - v_AuxDateTo <=31 AND v_AuxDateAcct - v_AuxDateTo >=0
    TO_CHAR(v_DateFrom,'YYYY')=TO_CHAR(v_DateAcct, 'YYYY') 
    AND TO_NUMBER(TO_CHAR(v_DateAcct,'YYYY'))-TO_NUMBER(TO_CHAR(v_DateFrom,'YYYY')) <=1) THEN
  IF (v_IsSOTrx = 'Y') THEN
    v_AccDefType := 'OR'; --Other receivables
  ELSE
    v_AccDefType := 'OL'; --Other liabilities
END IF;

If the date range defined doesn't fulfill any of the previous conditions raise an exception of wrong period range (@ACCDEF_WrongPeriodRange@).

Plan lines not posted check

Check that there isn't any plan line posted (ACCDEF_PLAN.POSTED='Y'). If exists raise an exception.

RAISE_APPLICATION_ERROR(-20000, '@ACCDEF_PostedPlanLines@');

Existence check of previously created plans

SELECT count(*) INTO v_count
FROM DUAL
WHERE EXISTS (SELECT 1 FROM ACCDEF_PLAN WHERE C_INVOICE_ID = p_Record_ID);

IF (v_count = 0) THEN
  ...
END IF;

Allocation amount calculation

First is needed to calculate the number of periods/months. Get the month number and year of DateFrom and DateTo. Then:

IF (v_DateFromYear=v_DateToYear) THEN
  v_TotalMonths := v_DateToMonth - v_DateFromMonth + 1;
ELSE 
  v_TotalMonths := (12 - v_DateFromMonth + 1) + v_DateToMonth + (v_DateToYear - v_DateFromYear - 1)*12;
END IF;

The total amount to allocate is the GrandTotal column of the C_Invoice table. To calculate it is divided the amount to allocate with the number of periods, the result is then rounded up. The number of decimals is taken from the standard precision of the invoice's currency (C_Currency.StdPrecision column, join the table with C_Invoice.C_Currency_ID column).

v_MonthAmt := ROUND((v_TotalAmt/v_TotalMonths)+(0.5/POWER(10,v_precision)),v_Precision);

Plan lines creation

It is needed a FOR loop for each month. As the monthly amount is rounded up the final period will have a smaller amount with the remaining amount to allocate. To calculate the accounting date for each plan line is used an auxiliary date variable that is updated on each iteration. Initialize the v_MonthDate date with the v_DateFrom date (that should be the first day of the month). Another auxiliary variable is used to calculate the line number of the plan line.

For each month that needs a plan line it has to be inserted a line in the ACCDEF_PLAN table. Use the same client and organization than the invoice header. For the updateby and createdby use the p_user_id parameter.

Before the insert 2 checks are needed:

• If it is an anticipatory transaction (v_AccDefType IN ('OL', 'OR')) check that the period is available for accounting. Copied below the code from the C_Invoice_Post procedure that does the same check for the accounting date of the invoice. Get the v_DocTypeTarget_ID from the Invoice header (C_Invoice.C_DocTypeTarget_ID). If it is not available raise an error. 'v_AD_Org_ID is the organization id of the invoice.
• If it is the last month (v_MonthNo = v_TotalMonths) calculate the remaining amount (v_TotalAmt-v_MonthAmt*(v_TotalMonths-1))

After the insert update the needed variables: v_LineNo and v_AuxMonthDate

Structure of this part of the code:

DECLARE
  v_MonthNo NUMBER;
  v_LastAmt NUMBER;
  v_MonthDate DATE := v_DateFrom;
  v_LineNo NUMBER := 10;
  v_org_bule_id VARCHAR2(32);
  v_isacctle CHAR(1);
  v_available_period NUMBER;
BEGIN
FOR v_MonthNo IN 1..v_TotalMonths LOOP

  -- Check the period control is opened (only if it is legal entity with accounting)
  -- Gets the BU or LE of the document
  SELECT AD_GET_DOC_LE_BU('C_INVOICE', p_Record_ID, 'C_INVOICE_ID', 'LE')
  INTO v_org_bule_id
  FROM DUAL;
         
  SELECT AD_OrgType.IsAcctLegalEntity
  INTO v_isacctle
  FROM AD_OrgType, AD_Org
  WHERE AD_Org.AD_OrgType_ID = AD_OrgType.AD_OrgType_ID
    AND AD_Org.AD_Org_ID=v_org_bule_id;
         
  IF (v_isacctle='Y' AND v_AccDefType IN('OL', 'OR')) THEN   		
    SELECT C_CHK_OPEN_PERIOD(v_AD_Org_ID, v_MonthDate, NULL, v_DocTypeTarget_ID) 
    INTO v_available_period
    FROM DUAL;
           
    IF (v_available_period<>1) THEN
      RAISE_APPLICATION_ERROR(-20000, '@ACCDEF_PeriodNotAvailableForDate@' || ' ' || TO_CHAR(v_MonthDate));
    END IF;
  END IF;

  --Check last month to calculate last amount
  IF (.....

  --Insert into ACCDEF_PLAN
  INSERT INTO....

  v_LineNo := v_LineNo +10;
  v_MonthDate := ADD_MONTHS(v_MonthDate, 1);

END;

Accruals and Deferrals Plan posting

Technical Requirements

Modularize AcctServer project has to be developed.

Process

A new class DocAccDefPlan extending org.openbravo.erpCommon.ad_forms.AcctServer has to be created.

This class is analogue to other Doc* classes of the org.openbravo.erpCommon.ad_forms package such as DocInvoice or DocMovement that can be taken as example.

A record of the ACCDEF_Plan table can only be posted if its related invoice is posted. It's period has also to be open.

Each plan line (record on ACCDEF_Plan table) is posted independently creating the necessary records in the Fact_Acct table.

For each invoice is needed a reversal accounting to set the proper amount in the corresponding accruals and deferrals account, this reversal accounting is generated when the first plan line is posted. So it has to be checked if that reversal accounting exists or not when a plan line is posted. In case it doesn't exist create it, using the C_Invoice table id as ad_table_id and the C_Invoice_ID as record_id.

To calculate the amount that has to be set in the reversal accounting sum the amounts of all the lines that are not in the same period of the invoice.

If a plan line's Acct_Date is in the same period than the invoice accounting date, set the plan line to posted but don't create any accounting in the fact_acct table for it.

Select the corresponding Acc. and Def. default account based on the Acc. and Def. type set in the invoice header.

Accruals and Deferrals Plan unposting

Technical Requirement

It is needed to add in core a new extension point in the FACT_ACCT_RESET procedure.

Summary

It is needed to create a new pl/sql procedure called ACCDEF_UnPost. This procedure is called in an extension point of the FACT_ACCT_RESET.

The procedure has to check if the process is unposting an invoice by the table_id given as a paremeter. If it is an invoice and has posted records in the ACCDEF_Plan table the procedure has to call back to the FACT_ACCT_RESET procedure. Before calling to the procedure a new AD_Pinstance has to be generated an the necessary parameters inserted in the AD_Pinstance_Para table.

If some record of ACCDEF_Plan table cannot be unpostes the invoice and the other records of the plan shouldn't be unposted.

Unpost funcnalities: When Click on unpost button in the Header tab of Invoice , It should check whether all Plan lines are posted or not in the Accruals and Deferrals tab.

• If all plan lines are posted (Posted='Y' in accdef_plan table), It should delete all accounting entry from invoice and also delete the accounting entry from all Plan lines belongs to this invoice in the table of FACT_ACCt.

• If any one of plan lines are not posted, (Posted='N' in accdef_plan table), It should not delete the accounting entry from invoice and also from all plan lines belongs to this invoice n the table of FACT_ACCT.

FOR Cur_Accdef IN  (SELECT i.accdef_plan_id FROM accdef_plan i WHERE c_invoice_id=v_Record_ID)
   LOOP
       DELETE 	FROM FACT_ACCT	WHERE Record_ID=Cur_Accdef.accdef_plan_id aND AD_Client_ID = v_AD_Client_ID;
       UPDATE accdef_plan SET posted ='N' WHERE accdef_plan_id=Cur_Accdef.accdef_plan_id;
   END LOOP;

Reactivate Funcnalities

All Plan lines should be removed from Accruals and Defferrals tab When reactivating the invoice from header.

IF (p_docAction='RE') THEN
     SELECT COUNT(*) INTO v_accdefcount FROM accdef_plan where C_INVOICE_ID=p_record_id;
     IF (v_accdefcount>0 ) THEN
         FOR Cur_Accdef_plan_Line IN 
           (SELECT i.accdef_plan_id
               FROM accdef_plan i 
                 WHERE c_invoice_id=p_record_id)
         LOOP
           DELETE
             FROM accdef_plan_line
             WHERE accdef_plan_id=Cur_Accdef_plan_Line.accdef_plan_id;
         END LOOP;
           DELETE FROM ACCDEF_PLAN WHERE C_INVOICE_ID=p_record_id;
     END IF;
END IF;

Dependencies

It is needed to use the Extension Points functionality, this development will be available in 2.50MP4, meanwhile it is possible to use the latest pi revision.

To test

Open discussion items

Query 1: Defined date range correct check

We have to check only based on the date instead of month. For example scenario account date 13/12/2009 type:prepaid expenses start from(from date)01/01/2010

here month of fromDate is less than month of account date. However it is valid.

So the below requirement may not be aligned: Transitory transactions (Prepaid Expenses and Deferred Income)

• Month of DateFrom same or next than DateAcct

I gave like below:

 IF(v_DateAcct>v_DateFrom) THEN      
  v_IsDateRange :=FALSE; //It means it is invalid date range
   END IF; 

Please give me your feedback.

Gorka, The below steps are to implement the Disabled or Hidden of the Unpost button for the HIS customer and other customers also. If anything wrong in the below steps , please let me know.

1. We need to create a column for flag setting in the database

2. In that column If flag is "yes", it will activate the unpost button for that customer otherwise It will be disabled.

3. This Configuration should be done in System Administrator because only he has the rights to decide whether the unpost button should be disabled or enabled for that particular customer.

If you agreed the above steps, Which table we have to create a column for flag setting and which screen we have to put this configuration in System Administrator login.

Please give me your feedback.

Closed discussion items