CreditMngtDunning/Technical Documentation

Contents 1 Introduction 2 Modules 2.1 Dunning 3 UI-Specification 3.1 UI-Panels 3.2 UI Technical Specifications 3.2.1 UI-DUN-001 Dunning Key: View 3.2.2 UI-DUN-001 Dunning Key: Fields 3.2.3 UI-DUN-010 Dunning History: View 3.2.4 UI-DUN-010 Dunning History: Fields 3.2.5 UI-DUN-003 Base Interest Rates: View 3.2.6 UI-DUN-003 Base Interest Rates: Fields 3.2.7 UI-DUN-004 Dunning Run: View 3.2.8 UI-DUN-004 Dunning Run: Search Form 3.2.9 UI-DUN-004 Dunning Run: Process Form 3.2.10 UI-DUN-008 Customer Dunning Configuration: View 3.2.11 UI-DUN-008 Customer Dunning Configuration: Fields 3.2.12 UI-DUN-012 Dunning Invoice: View 3.2.13 UI-DUN-012 Invoice Header: Fields 3.2.14 UI-DUN-009 Setup Dunning Configuration: View 3.2.15 UI-DUN-009 Setup Dunning Configuration: Fields 3.3 Navigation Model 4 Service Specification 4.1 Services Catalog 4.2 Operations Catalog 5 Data Design 5.1 Tables Catalog 5.1.1 DUN_DUNNING_KEY 5.1.2 DUN_DUNNING_HISTORY 5.1.3 DUN_BASE_INTEREST 5.1.4 DUN_BASE_CONF 5.1.5 DUN_CUST_CONF 5.2 Triggers 5.3 STORED PROCEDURE

Introduction

Purpose of this document is to provide a detailed description of critical design information about the dunning module.

Modules

Dunning

Property	Value
Java Package	org.openbravo.creditmanagement.dunning
Type	Module
Translation Required	No
DB Prefix	DUN
Module Language	English
Dependencies	core 2.50.1482

UI-Specification

UI-Panels

Table of the key UI Panels of this Module.

Panel code	Panel Name	Existing	Type
UI-DUN-001	Dunning Key	No	Generated
UI-DUN-010	Dunning History	No	Generated
UI-DUN-003	Base Interest Rates	No	Generated
UI-DUN-004	Dunning Run	No	Manual
UI-DUN-009	Setup Dunning Configuration	No	Generated
UI-DUN-012	Dunning Invoice	No	Generated
UI-DUN-008	Customer Dunning Configuration	No	Generated

UI Technical Specifications

UI-DUN-001 Dunning Key: View

UI-DUN-001 Dunning Key: Fields

Name	Table.Column	Mandatory	Display Logic	Reference	Length	Seq. No.
Client	DUN_DUNNING_KEY.AD_CLIENT_ID	Yes		TableDir		10
Organization	DUN_DUNNING_KEY.AD_ORG_ID	Yes		TableDir		20
Name	DUN_DUNNING_KEY.NAME	Yes		String	30	30
Key	DUN_DUNNING_KEY.DKEY	Yes		String	2	40
Subsequent Key	DUN_DUNNING_KEY.SUB_DKEY	Yes		String	2	50
Coming into Effect	DUN_DUNNING_KEY.DAYS	Yes		Numeric	2	60
Dunning Fee	DUN_DUNNING_KEY.FEE	No		Amount		70
Dunning Cost	DUN_DUNNING_KEY.COST	No		Amount		80
Active	DUN_DUNNING_KEY.ACTIVE	Yes		YesNo		90

UI-DUN-010 Dunning History: View

UI-DUN-010 Dunning History: Fields

Name	Table.Column	Mandatory	Display Logic	Reference	Length	Seq. No.
Client	DUN_DUNNING_HISTORY.AD_CLIENT_ID	Yes		TableDir		10
Organization	DUN_DUNNING_HISTORY.AD_ORG_ID	Yes		TableDir		20
Invoice	DUN_DUNNING_HISTORY.C_INVOICE_ID	Yes		TableDir		30
Dunning Date 1	DUN_DUNNING_HISTORY.DATE1	Yes		Date		40
Dunning Key 1	DUN_DUNNING_HISTORY.DKEY1	Yes		Numeric		50
Dunning Date 2	DUN_DUNNING_HISTORY.DATE2	No		Date		60
Dunning Key 2	DUN_DUNNING_HISTORY.DKEY2	No		Numeric		70
Dunning Date 3	DUN_DUNNING_HISTORY.DATE3	No		Date		80
Dunning Key 3	DUN_DUNNING_HISTORY.DKEY3	No		Numeric		90
Dunning Date 4	DUN_DUNNING_HISTORY.DATE4	No		Date		100
Dunning Key 4	DUN_DUNNING_HISTORY.DKEY4	No		Numeric		110
Dunning Date 5	DUN_DUNNING_HISTORY.DATE5	No		Date		120
Dunning Key 5	DUN_DUNNING_HISTORY.DKEY5	No		Numeric		130

UI-DUN-003 Base Interest Rates: View

UI-DUN-003 Base Interest Rates: Fields

Name	Table.Column	Mandatory	Display Logic	Reference	Length	Seq. No.
Client	DUN_BASE_INTEREST.AD_CLIENT_ID	Yes		TableDir		10
Organization	DUN_BASE_INTEREST.AD_ORG_ID	Yes		TableDir		20
Date	DUN_ BASE_INTEREST.DATE	Yes		Date		30
Rate	DUN_ BASE_INTEREST.RATE	Yes		Numeric		40
Rate	DUN_ BASE_INTEREST.ACTIVE	Yes		YesNo		50

UI-DUN-004 Dunning Run: View

UI-DUN-004 Dunning Run: Search Form

Name	Mandatory	Description/Operation	Input/Output
Remainder Date	Yes	OP-DUN-001	Input
Type of Receivable	No	OP-DUN-001	Input
Level	No	OP-DUN-001	Input
Key	No	OP-DUN-001	Input

UI-DUN-004 Dunning Run: Process Form

Name	Mandatory	Description/Operation	Input/Output
Select	Yes	OP-DUN-002/ OP-DUN-001	Input/Output
Receivable		OP-DUN-001	Output
Type		OP-DUN-001	Output
Due Date		OP-DUN-001	Output
Dunning Date		OP-DUN-001	Output
Granted Deferral		OP-DUN-001	Output
Outstanding Amount		OP-DUN-001	Output

UI-DUN-008 Customer Dunning Configuration: View

UI-DUN-008 Customer Dunning Configuration: Fields

Name	Table.Column	Mandatory	Display Logic	Reference	Length	Seq. No.
Client	DUN_CUST_CONF.AD_CLIENT_ID	Yes		TableDir		10
Organization	DUN_CUST_CONF.AD_ORG_ID	Yes		TableDir		20
Private Person	DUN_CUST_CONF.private_person	Yes		Yes/No		30
Subject to private law	DUN_CUST_CONF.sto_priv_law	Yes		Yes/No		40
Active	DUN_CUST_CONF.ACTIVE	Yes		Yes/No		50
Override interest rate spread	DUN_CUST_CONF.overr_irs	Yes		Yes/No		60
Interest rate spread	DUN_CUST_CONF.inte_rate_spr	Yes		Numeric		70

UI-DUN-012 Dunning Invoice: View

UI-DUN-012 Invoice Header: Fields

Name	Table.Column	Mandatory	Display Logic	Reference	Length	Seq. No.
Client	DUN_PAYMENT_PLAN_DUNNING.AD_CLIENT_ID	Yes		TableDir		10
Organization	DUN_PAYMENT_PLAN_DUNNING.AD_ORG_ID	Yes		TableDir		20
Dunning Key	DUN_PAYMENT_PLAN_DUNNING.dun_dunning_key_id	No		TableDir		30
Next Dunning date	DUN_PAYMENT_PLAN_DUNNING.next_date	Yes		Date		40
Level	DUN_PAYMENT_PLAN_DUNNING.level	Yes		String		50
Granted Deferral	DUN_BASE_CONF.granted_deferral	Yes		Yes/No		60
Deferral Date	DUN_PAYMENT_PLAN_DUNNING.expected_deferral_date	Yes		Date		60

UI-DUN-009 Setup Dunning Configuration: View

UI-DUN-009 Setup Dunning Configuration: Fields

Name	Table.Column	Mandatory	Display Logic	Reference	Length	Seq. No.
Client	DUN_BASE_CONF.AD_CLIENT_ID	Yes		TableDir		10
Organization	DUN_BASE_CONF.AD_ORG_ID	Yes		TableDir		20
Private Person Percentage	DUN_BASE_CONF.priv_pers_perc	Yes		Numeric		30
Public Person Percentage	DUN_BASE_CONF.pub_pers_perc	Yes		YesNo		40
Min. Charge	DUN_BASE_CONF.min_charge	Yes		Yes/No		50
Max Charge	DUN_BASE_CONF.max_charge	Yes		YesNo		60
Fine Percentage	DUN_BASE_CONF.fine_perc	Yes		Numeric		60
Rounding	DUN_BASE_CONF.rounding	Yes		Numeric		70
Min. Def. Days	DUN_BASE_CONF.min_def_days	Yes		Numeric		80
Spread	DUN_BASE_CONF.spread	Yes		Numeric		90
Product for Fee	DUN_BASE_CONF.product_fee_id	Yes		Search - Product Complete Selector		100
Product for Cost	DUN_BASE_CONF.product_cost_id	Yes		Search - Product Complete Selector		110
Product for Deferral	DUN_BASE_CONF.product_deferr_id	Yes		Search - Product Complete Selector		120
Product for Fine	DUN_BASE_CONF.product_fine_id	Yes		Search - Product Complete Selector		130
Product for Arrears	DUN_BASE_CONF.product_arr_id	Yes		Search - Product Complete Selector		150
Tax Rate	DUN_BASE_CONF.c_tax_id	Yes		TableDir		160

Navigation Model

FROM View	TO View	Widget/Action
Financial Management - Receivables & Payables - Setup	UI-DUN-001 (Dunning Key)	Click on the specified menu Item
Financial Management - Receivables & Payables - Setup	UI-DUN-003 (Base Interest Rates)	Click on the specified menu Item
Financial Management - Receivables & Payables - Setup	UI-DUN-009 (Setup Dunning Configuration)	Click on the specified menu Item
Financial Management - Receivables & Payables - Transactions	UI-DUN-004 (Dunning Run)	Click on the specified menu Item
Financial Management - Receivables & Payables - Analysis Tools	UI-DUN-010 (Dunning History)	Click on the specified menu Item
Businesss Partner > Customer > Dunning Configuration	UI-DUN-008 (Customer Dunning Configuration)	Within BP tab click on Customer subtab, then click on the Dunning Configuration subtab

Service Specification

Services Catalog

SR Code	Class	Description
SR-DUN-001	org.openbravo.creditmanagement.dunning.biz	It exposes all the operations regarding Dunning Features

Operations Catalog

OP Code	SR Code	Operation	Description
OP-DUN-001	SR-DUN-001	+search(String orgId, Long level, String key, Date remDate ): List<DUNPaymentPlanDunning>	It loads all dunning receivables matching with form’s parameters
OP-DUN-002	SR-DUN-001	+process(String[] ids) : OBBusinessResult<Object>	It processes all selected 'dunnings', it also generates new receivables for the related Invoice and updates the related Dunning History items

Data Design

Tables Catalog

DUN_DUNNING_KEY

Column Name	Type	Constraint
DUN_DUNNING_KEY_ID	Varchar(32)	Not Null, Primary Key
DKEY	Varchar(2)	Not Null
AD_CLIENT_ID	Varchar(32)	Not Null
AD_ORG_ID	Varchar(32)	Not Null
SUB_DKEY	Varchar(2)	Not Null
DAYS	Number	Not Null
ACTIVE	Boolean	Not Null

DUN_DUNNING_HISTORY

Column Name	Type	Constraint
DUN_DUNNING_HISTORY_ID	Varchar(32)	Not Null, Primary Key
AD_CLIENT_ID	Varchar(32)	Not Null
AD_ORG_ID	Varchar(32)	Not Null
C_INVOICE_ID	Varchar(32)	Not Null
DATE1	Date	Not Null
DKEY1	Varchar(32)	Not Null
DATE2	Date	Nullable
DKEY2	Varchar(32)	Nullable
DATE3	Date	Nullable
DKEY3	Varchar(32)	Nullable
DATE4	Date	Nullable
DKEY4	Varchar(32)	Nullable
DATE5	Date	Nullable
DKEY5	Varchar(32)	Nullable
ACTIVE	Boolean	Not Null

DUN_BASE_INTEREST

Column Name	Type	Constraint
DUN_BASE_INTEREST_ID	Varchar(32)	Not Null, Primary Key
AD_CLIENT_ID	Varchar(32)	Not Null
AD_ORG_ID	Varchar(32)	Not Null
RATE	Number(2)	Not Null
EMISSION_DATE	Date	Not Null
ACTIVE	Boolean	Not Null

DUN_BASE_CONF

Column Name	Type	Constraint
DUN_BASE_CONF_ID	Varchar(32)	Not Null, Primary Key
AD_CLIENT_ID	Varchar(32)	Not Null
AD_ORG_ID	Varchar(32)	Not Null
ACTIVE	Boolean	Not Null
priv_pers_perc	numeric(4,2)	Not Null
pub_pers_perc	numeric(4,2)	Not Null
base_perc	numeric(4,2)	Not Null
min_charge	numeric(13,2)	Not Null
max_charge	numeric(13,2)	Not Null
fine_perc	numeric(13,2)	Not Null
rounding	numeric(13,2)	Not Null
min_def_days	numeric	Not Null
spread	numeric(4,2)	Not Null
product_fee_id	varying(32)	Not Null
product_fine_id	varying(32)	Not Null
product_deferr_id	varying(32)	Not Null
product_arr_id	varying(32)	Not Null
product_cost_id	varying(32)	Not Null
c_tax_id	varying(32)	Not Null

DUN_CUST_CONF

Column Name	Type	Constraint
dun_cust_conf_id	Varchar(32)	Not Null, Primary Key
AD_CLIENT_ID	Varchar(32)	Not Null
AD_ORG_ID	Varchar(32)	Not Null
ACTIVE	Boolean	Not Null
c_bpartner_id	Varchar(32)	Not Null
dun_dunning_key_id	Varchar(32)	Not Null
private_person	character(1)	Not Null
sto_priv_law	character(1)	Not Null
overr_irs	character(1)	Not Null
inte_rate_spr	numeric(4,2)	Not Null

Triggers

This trigger executes an insert on the dun_payment_plan_dunning table as a row o n FIN_PAYMENT_SCHEDULE has been added.

CREATE TRIGGER dun_finpaymentsch_trg
  AFTER INSERT OR UPDATE
  ON fin_payment_schedule
  FOR EACH ROW
  EXECUTE PROCEDURE dun_finpaymentsch_trg();

CREATE OR REPLACE FUNCTION dun_finpaymentsch_trg()
  RETURNS trigger AS
$BODY$ DECLARE 
 
 DECLARE
    v_IsEmployee CHAR(1);
    v_NewDueDate TIMESTAMP;
    v_Days NUMERIC;
    v_Count NUMERIC;
    v_orgId Varchar;
 
 BEGIN
 
   IF AD_isTriggerEnabled()='N' THEN IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; 
   END IF;


   SELECT COUNT(*) 
   INTO v_Count
   FROM  C_Invoice c, dun_cust_conf dc
  WHERE c.c_invoice_id= new.c_invoice_id 
  and c.c_bpartner_id=dc.c_bpartner_id 
  and dc.ad_org_id=c.ad_org_id;
  
    
  IF v_Count=0 THEN
    v_orgId:='0';
  ELSE
    v_orgid:=new.ad_org_id;
  END IF;

  

  SELECT COALESCE(k.days,0)
   INTO v_Days
   FROM  C_Invoice c, dun_cust_conf dc, dun_dunning_key k
  WHERE c.c_invoice_id= new.c_invoice_id 
  and c.c_bpartner_id=dc.c_bpartner_id 
  and dc.ad_org_id=v_orgid
  and k.dun_dunning_key_id=dc.dun_dunning_key_id;
	
  
   
   IF TG_OP = 'INSERT' THEN	
	   INSERT INTO dun_payment_plan_dunning
	    (
		  dun_payment_plan_dunning_id,
		  ad_client_id,
		  ad_org_id,
		  isactive,
		  created,
		  createdby,
		  updated,
		  updatedby,
		  dun_dunning_key_id,
		  level,
		  fin_payment_sched_inv_v_id,
		  next_date
	    )
	  SELECT get_uuid(), 
		new.AD_Client_ID, 
		new.AD_Org_ID,
		new.IsActive, 
		new.Created, 
		new.CreatedBy,
		new.Updated, 
		new.updatedby,
		cc.DUN_DUNNING_KEY_ID, 
		0, 
		new.fin_payment_schedule_id,
		DUN_NEXT_BIZ_DATE(new.duedate,v_Days,new.AD_Org_ID)
	  FROM  C_Invoice  ci LEFT JOIN  dun_cust_conf cc ON ci.c_bpartner_id = cc.c_bpartner_id and cc.ad_org_id=v_orgId
	  WHERE ci.c_invoice_id= new.c_invoice_id;
  END IF; 
 
 
   IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; 

END 

; $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION dun_finpaymentsch_trg() OWNER TO tad;

STORED PROCEDURE

Dun_next_biz_date is a stored procedure that returns the closest valid business day for a Organization having p_adorgid such as key, starting from a p_docdate and adding p_days days.

CREATE OR REPLACE FUNCTION dun_next_biz_date(p_docdate timestamp without time zone, p_days numeric, p_adorgid character varying)
  RETURNS timestamp without time zone AS
$BODY$ DECLARE 

    v_DueDate TIMESTAMP;
    v_flag numeric;
    v_aux numeric;
    v_orgId character varying;
  BEGIN
	
	v_DueDate := p_DocDate + COALESCE(p_days, 0) ; 

	v_orgId:=ad_org_getcalendarowner(p_adorgid);
	
        v_flag:=1;

WHILE v_flag>0 LOOP
		select count(*), MOD(to_number(to_char(v_DueDate, 'J')), 7) + 1  into v_flag, v_Aux 
                from c_nonbusinessday nbd,ad_org o   
		where o.ad_org_id=v_orgId  
		and nbd.c_calendar_id=o.c_calendar_id 
		and nbd.date1=v_DueDate;

		IF v_flag >0 OR v_Aux IN('6','7') THEN
			v_DueDate:=v_DueDate+1;
		ELSE
                    v_flag:=0;
		END IF;
	
END LOOP;
        
 RETURN COALESCE(v_DueDate, TRUNC(p_DocDate)) ;
END ; $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION dun_next_biz_date(timestamp without time zone, numeric, character varying) OWNER TO tad;