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