Projects:AdvPaymentMngt/Technical Documentation Student Debtors

Contents 1 Student Debtors - Technical Documentation 1.1 Overview 1.2 Physical data model description 1.2.1 New tables 1.2.1.1 Payment Priority Table 1.2.2 Columns added to Core tables 1.2.2.1 Columns added to C_INVOICE table 1.2.2.2 Columns added to C_ORDER table 1.2.2.3 Columns added to FIN_PAYMENT_SCHEDULE table 1.3 List of artifacts 1.3.1 Windows 1.3.2 Tabs 1.3.2.1 Fields added to Core Tabs 1.3.3 Menu entries 1.3.4 Functions 1.3.5 Process 1.3.5.1 Parameter 1.3.5.2 Process Class 1.3.6 Java Processes 1.3.7 Views 1.4 v3 Roles dataset

Student Debtors - Technical Documentation

Overview

This article will explain how to proceed in order to provide the ability of distribution of payments in/out taking into consideration Payment Priority.

Physical data model description

New tables

Payment Priority Table

• Name: Fin_Payment_Priority
• DB Table Name: Fin_Payment_Priority
• Java Class Name: PaymentPriority
• Data Access Level: Client/Organization
• Window: Payment Priority
• Module: Core

FIN_Payment_Priority
Column Name	Type	Length	Comments
Fin_Payment_Priority_ID	CHAR	32	The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID	CHAR	32	Indicates which client (company) the record belongs to.
AD_ORG_ID	CHAR	32	Indicates which organization (city/department/location) within a client a record belongs to.
ISACTIVE	CHAR	1	Intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.
CREATED	DATE		Date/time of creation of a record.
CREATEDBY	CHAR	32	Foreign key to AD_USER indicating the user that created this record.
UPDATED	DATE		Date/time of last update of a record.
UPDATEDBY	CHAR	32	Foreign key to AD_USER indicating the user that last updated this record.
PRIORITY	NUMERIC	32	Used as Record identifier. Used as Filter Column. Default Value: @SQL=SELECT COALESCE(MAX(Priority),0)+1 AS DefaultValue FROM Fin_Payment_Priority WHERE AD_Client_ID=@AD_Client_ID@ A defined level of importance or precedence. Lower number means higher priority.
NAME	CHAR	60	Used as Record identifier. Used as Filter Column. Name of the priority.
DESCRIPTION	CHAR	255	A space to write additional related information.
ISDEFAULT	CHAR	1	Sets the priority as default for invoices and orders. If several priorities are selected as default the application will select as default the priority with higher priority (lower number).
COLOR	CHAR	60	Hexadecimal color code preceded by # symbol.

Columns added to Core tables

Columns added to C_INVOICE table

C_INVOICE
Column Name	Type	Length	Comments
Fin_Payment_Priority_ID	CHAR	32	Foreign key to Fin_Payment_Priority(Fin_PAYMENT_Priority_ID). Default Value: @SQL=SELECT Fin_Payment_Priority_ID FROM Fin_Payment_Priority WHERE AD_ISORGINCLUDED(@AD_Org_ID@, AD_Org_ID, @#AD_Client_ID@) <> -1 AND Isdefault = 'Y' ORDER BY Priority It belongs to Core.

Columns added to C_ORDER table

C_ORDER
Column Name	Type	Length	Comments
Fin_Payment_Priority_ID	CHAR	32	Foreign key to Fin_Payment_Priority(Fin_PAYMENT_Priority_ID) Default Value: @SQL=SELECT Fin_Payment_Priority_ID FROM Fin_Payment_Priority WHERE AD_ISORGINCLUDED(@AD_Org_ID@, AD_Org_ID, @#AD_Client_ID@) <> -1 AND Isdefault = 'Y' ORDER BY Priority It belongs to Core.

Columns added to FIN_PAYMENT_SCHEDULE table

FIN_PAYMENT_SCHEDULE
Column Name	Type	Length	Comments
Fin_Payment_Priority_ID	CHAR	32	Foreign key to Fin_Payment_Priority(Fin_Payment_Priority_ID)
Update_Payment_Plan	CHAR	1	Button that will show the popup where the payment plan can be updated. It belongs to Core.

List of artifacts

Windows

• Name: Payment Priority
• Description: Define priorities for payment plans created when processing invoices and orders.
• Help/Comment: Define priorities for payment plans created when processing invoices and orders. You can mark as default the priority you want to be displayed as selected in Invoice and Order windows. You can only mark as default one priority.
• Window Type: Maintain
• Sales Transaction: Yes
• Module: Core

Tabs

• Name: Payment Priority
• Description: Define priorities for payment plans created when processing invoices and orders.
• Help/Comment: Define priorities for payment plans created when processing invoices and orders. You can mark as default the priority you want to be displayed as selected in Invoice and Order windows. You can only mark as default one priority.
• Table: Fin_Payment_Priority
• Sequence Number: 10
• Tab Level: 0
• UI Pattern: Standard
• Module: Core

Payment Priority Tab
Field Name	Column Name	Length	Sequence	Show in Grid View	Comments
Organization	AD_Org_ID	32	10	No	Organizational entity within client.
Name	Name	40	20	Yes	A non-unique identifier for a record/document often used as a search tool.
Description	Description	255	30	Yes	A space to write additional related information.
Active	Isactive	1	40	Yes	A flag indicating whether this record is available for use or de-activated.
Priority	Priority	12	50	Yes	A defined level of importance or precedence.
Color	Color	40	60	Yes	HTML hexadecimal color code preceded by #.
Default	Isdefault	1	70	Yes	Indicates if this record will be used as a default value.

Fields added to Core Tabs

• Module: APRM

Payment Plan tab in Purchase/Sales Invoice/Order window
Field Name	Column Name	Length	Sequence	Show in Grid View	Comments
Payment Priority	Fin_Payment_Priority_ID	44	120	Yes	Sets the priority of the payment plans generated when processing the invoice or order.
Update Payment Plan	Update_Payment_Plan	20	130	Yes	Button for updating the payment plan. Display Logic: @Expected@!@Received@ Only show the button if the payment plan is not fully paid.

Menu entries

• Name: Payment Priority
• Description: Define priorities for payment plans created when processing invoices and orders.
• Action: Window
• Window: Payment Priority
• Summary Level: No
• Menu position: Financial Management || Receivables & Payables || Setup || Payment Priority
• Module: APRM

Functions

APRM_GEN_PAYMENTSCHEDULE_INV

Inherit em_aprm_payment_priority column value in the payment plan when processing the invoice.

@@ -57,6 +57,7 @@
 v_FIN_PAYMENT_DETAIL_ID FIN_PAYMENT_DETAIL.FIN_PAYMENT_DETAIL_ID%TYPE;
 v_FIN_FINACC_TRANSACTION_ID FIN_FINACC_TRANSACTION.FIN_FINACC_TRANSACTION_ID%TYPE;
 v_Line FIN_FINACC_TRANSACTION.LINE%TYPE;
+v_PaymentPriority VARCHAR2(32);


 TYPE RECORD IS REF CURSOR;
@@ -89,10 +90,12 @@
   v_ResultStr:='ReadingInvoice';
   SELECT ad_client_id, ad_org_id, c_doctypetarget_id, IsSOTrx,
       grandtotal, C_PaymentTerm_ID, PaymentRule, C_Currency_ID,
-      C_BPartner_ID, DateInvoiced, totallines, fin_paymentmethod_id
+      C_BPartner_ID, DateInvoiced, totallines, fin_paymentmethod_id,
+      fin_payment_priority_id
   INTO v_Client_ID, v_Org_ID, v_DocTypeTarget_ID, v_IsSOTrx,
       v_GrandTotal, v_PaymentTerm, v_PaymentRule, v_Currency_ID,
-      v_BPartner_ID, v_DateInvoiced, v_totallines, v_PaymentMethod
+      v_BPartner_ID, v_DateInvoiced, v_totallines, v_PaymentMethod,
+      v_PaymentPriority
   FROM c_invoice
   WHERE c_invoice_id=p_record_id;
 
@@ -211,14 +214,14 @@
           created, createdby, updated, updatedby,
           c_invoice_id, c_currency_id, c_order_id,
           fin_paymentmethod_id, amount, duedate,
-          outstandingamt
+          outstandingamt, fin_payment_priority_id
          ) VALUES (
           v_payment_schedule_invoice, v_client_id, v_org_id, 'Y',
           now(), p_user, now(), p_user,
           p_record_id, v_currency_id, null,
           v_PaymentMethod,
           v_scheduledamount, v_planneddate,
-          v_scheduledamount
+          v_scheduledamount, v_PaymentPriority
         );

APRM_GEN_PAYMENTSCHEDULE_ORD

Inherit em_aprm_payment_priority column value in the payment plan when processing the order.

@@ -37,6 +37,7 @@
 v_paymentmethod_id c_order.fin_paymentmethod_id%TYPE;
 v_bpartner_id c_order.c_bpartner_id%TYPE;
 v_docSubType c_doctype.docsubtypeso%TYPE;
+v_payment_priority VARCHAR2(32);
 
 TYPE RECORD IS REF CURSOR;
 Cur_Params RECORD;
@@ -65,10 +66,12 @@
   v_ResultStr:='ReadingOrder';
   SELECT ad_client_id, ad_org_id, 
          grandtotal, dateordered, c_currency_id,
-         fin_paymentmethod_id, c_bpartner_id, c_doctype_id
+         fin_paymentmethod_id, c_bpartner_id, c_doctype_id,
+         fin_payment_priority_id
     INTO v_client_id, v_org_id,
          v_grandtotal, v_orderdate, v_currency_id,
-         v_paymentmethod_id, v_bpartner_id, v_C_Doctype_ID
+         v_paymentmethod_id, v_bpartner_id, v_C_Doctype_ID,
+         v_payment_priority
   FROM c_order
   WHERE c_order_id = p_record_id;
 
@@ -106,7 +109,7 @@
         created, createdby, updated, updatedby,
         c_invoice_id, c_currency_id, c_order_id,
         fin_paymentmethod_id, amount, duedate,
-        outstandingamt
+        outstandingamt, fin_payment_priority_id
        )
       VALUES
       (
@@ -114,7 +117,7 @@
         now(), p_user, now(), p_user,
         null, v_currency_id, p_record_id,
         v_paymentmethod_id, v_grandtotal, v_orderdate,
-        v_grandtotal
+        v_grandtotal, v_payment_priority
       );

Process

• Search Key: UpdatePaymentPlan
• Name: Update Payment Plan
• Description: Update Payment Plan
• Data Access Level: Client/Organization
• UI Patern: Standard
• Module: Core

Parameter

• Name: Due Date
• Description: The date when a specified request must be carried out by.
• Help/Comment: Date when the payment is due without deductions or discount.
• Sequence Number: 10
• DB Column Name: Duedate
• Application Element: DueDate - Due Date
• Reference: Date
• Length: 10
• Mandatory: Yes
• Default Value: @Duedate@

• Name: Payment Priority
• Description: Sets the priority of the payment plans generated when processing the invoice or order.
• Help/Comment: Sets the priority of the payment plans generated when processing the invoice or order. The distribution algorithm will be driven by this priority. The pending payments for order or payments will be displayed ordered by the payment priority first and then by the due date.
• Sequence Number: 20
• DB Column Name: Fin_Payment_Priority_ID
• Application Element: Fin_Payment_Priority_ID - Payment Priority
• Reference: TableDir
• Length: 32
• Default Value: @Fin_Payment_Priority_ID@

Process Class

• Default: Yes
• Java Class Name: org.openbravo.erpCommon.ad_process.UpdatePaymentPlan

Java Processes

The following Java process will update the payment plan (due date and payment priority) depending on the user selection.

package org.openbravo.erpCommon.ad_process;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.log4j.Logger;
import org.openbravo.base.session.OBPropertiesProvider;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBDal;
import org.openbravo.database.ConnectionProvider;
import org.openbravo.erpCommon.utility.OBError;
import org.openbravo.erpCommon.utility.Utility;
import org.openbravo.model.financialmgmt.payment.FIN_PaymentSchedule;
import org.openbravo.model.financialmgmt.payment.PaymentPriority;
import org.openbravo.scheduling.ProcessBundle;
import org.openbravo.service.db.DalConnectionProvider;

public class UpdatePaymentPlan implements org.openbravo.scheduling.Process {

 private static final Logger log = Logger.getLogger(UpdatePaymentPlan.class);

 @Override
 public void execute(ProcessBundle bundle) throws Exception {
   OBContext.setAdminMode(true);
   try {
     final String strPaymentScheduleInvId = (String) bundle.getParams().get(
         "Fin_Payment_Sched_Inv_V_ID");
     final String strPaymentScheduleOrdId = (String) bundle.getParams().get(
         "Fin_Payment_Sched_Ord_V_ID");
     final String strPaymentPriority = (String) bundle.getParams().get("finPaymentPriorityId");
     final String strDueDate = (String) bundle.getParams().get("duedate");

     final String strPaymentScheduleId = (strPaymentScheduleOrdId == null) ? strPaymentScheduleInvId
         : strPaymentScheduleOrdId;

     FIN_PaymentSchedule ps = OBDal.getInstance().get(FIN_PaymentSchedule.class,
         strPaymentScheduleId);
     ps.setDueDate(getDate(strDueDate));

     ps.setFINPaymentPriority(OBDal.getInstance().get(PaymentPriority.class, strPaymentPriority));
     OBDal.getInstance().save(ps);
     OBDal.getInstance().flush();

     final OBError msg = new OBError();
     ConnectionProvider conn = new DalConnectionProvider(false);
     msg.setTitle(Utility.messageBD(conn, "Success", OBContext.getOBContext().getLanguage()
         .getLanguage()));
     msg.setType("Success");
     bundle.setResult(msg);

   } catch (final Exception e) {
     log.error(e.getMessage(), e);
     final OBError msg = new OBError();
     msg.setType("Error");
     msg.setMessage(e.getMessage());
     msg.setTitle("Done with Errors");
     bundle.setResult(msg);
   } finally {
     OBContext.restorePreviousMode();
   }

 }

 /**
  * Parses the string to a date using the dateFormat.java property.
  * 
  * @param strDate
  *          String containing the date
  * @return the date
  */
 private static Date getDate(String strDate) {
   if (strDate.equals(""))
     return null;
   try {
     String dateFormat = OBPropertiesProvider.getInstance().getOpenbravoProperties().getProperty(
         "dateFormat.java");
     SimpleDateFormat outputFormat = new SimpleDateFormat(dateFormat);
     return (outputFormat.parse(strDate));
   } catch (ParseException e) {
     log.error(e.getMessage(), e);
     return null;
   }
 }

}

Views

Include Update_Payment_Plan and Fin_Payment_Priority_ID columns in FIN_PAYMENT_SCHED_ORD_V and FIN_PAYMENT_SCHED_INV_V views.

FIN_PAYMENT_SCHED_INV_V

@@ -6,6 +6,6 @@
-WHERE pd.fin_payment_detail_id = psd.fin_payment_detail_id
       AND ps.c_invoice_id IS NOT NULL
       AND psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id) AS numberofpayments
+WHERE pd.fin_payment_detail_id = psd.fin_payment_detail_id
       AND ps.c_invoice_id IS NOT NULL
       AND psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id) AS numberofpayments
       ,ps.update_payment_plan, ps.fin_payment_priority_id

FIN_PAYMENT_SCHED_ORD_V

@@ -6,6 +6,6 @@
-WHERE pd.fin_payment_detail_id = psd.fin_payment_detail_id
       AND ps.c_order_id IS NOT NULL
       AND psd.fin_payment_schedule_order = ps.fin_payment_schedule_id) AS numberofpayments
+WHERE pd.fin_payment_detail_id = psd.fin_payment_detail_id
       AND ps.c_order_id IS NOT NULL
       AND psd.fin_payment_schedule_order = ps.fin_payment_schedule_id) AS numberofpayments
       ,ps.update_payment_plan, ps.fin_payment_priority_id

v3 Roles dataset

• Employee
• Purchase Management
• Finance: grant access to Payment Priority window.
• Sales
• Company Admin: grant access to Payment Priority window.
• Company Executive
• Material Management