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

CreditMngtDunning/Technical Documentation

Contents

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

Crdun UI Dunning Key.png

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

Crdun UI Dunning History.png

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

Crdun UI Base Interest Rate.png

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

Crdun DunningRun-forms.jpg

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

Crdun UI Customer Dunning Config.png

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

Crdun UI DunningInvoiceTab.png

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

Crdun DunningConfig.jpg

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;

Retrieved from "http://wiki.openbravo.com/wiki/CreditMngtDunning/Technical_Documentation"

This page has been accessed 953 times. This page was last modified on 26 March 2018, at 11:52. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.