View source | View content page | Page history | Printable version   

Projects:AdvPaymentMngt/Technical Documentation Student Debtors

Contents

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
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

Tabs

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
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

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

Parameter
Process Class

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

Retrieved from "http://wiki.openbravo.com/wiki/Projects:AdvPaymentMngt/Technical_Documentation_Student_Debtors"

This page has been accessed 1,164 times. This page was last modified on 8 June 2012, at 05:26. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.