CreditMngtDunning/Technical Documentation
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
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;