Log in / create account
Openbravo.com
Issues
Forge
University
View source | View content page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Show collection (0 pages)
Collections help

Search

Projects:Remittances/Technical Documentation

Contents

Remittances infrastructure

Module Definition

A module must be created with the following parameters: 

               You may obtain a copy of the License at http://www.openbravo.com/legal/license.html or in the legal folder of the Openbravo ERP core distribution.

In the dependency tab:

In the prefix tab: Prefix=REM

In the datapackage tab: org.openbravo.module.remittance

Artifact List

The development of this module involves the creation of the several items in the application dictionary. Notice that new fields, tabs and window will require of the creation of the corresponding tables and columns in the application dictionary. The relation of these new database objects is described in the Database Structure Definition section. Below is the list of windows and processes that are new or might have some changes.


Window definition

Remittance Type window

This new window has three tabs called Remittance Type Accounting and Parameter.

Remittance Type tab:

Accounting tab:

Parameters tab:

Remittance window

Remittance tab: Fields included (descriptions are provisional):

Buttons:

Lines tab: Fields included (descriptions are provisional):

Canceled tab: Fields included (descriptions are provisional):

Buttons:

Returned tab: Fields included (descriptions are provisional):

Buttons:

Auxiliar Inputs

Auxiliar Input: IsSOTrx
Window: Remittance
Tab: Lines
Description: Used for proper navigation of payments (payment IN/OUT)
Code: @SQL=SELECT COALESCE(ISRECEIPT,'Y') AS ISRECEIPT FROM FIN_PAYMENT WHERE FIN_PAYMENT_ID = @FIN_PAYMENT_ID@

Auxiliar Input: IsSOTrx
Window: Remittance
Tab: Settled
Description: Used for proper navigation of payments (payment IN/OUT)
Code: @SQL=SELECT COALESCE(ISRECEIPT,'Y') AS ISRECEIPT FROM FIN_PAYMENT WHERE FIN_PAYMENT_ID = @FIN_PAYMENT_ID@

Auxiliar Input: IsSOTrx
Window: Remittance
Tab: Protested
Description: Used for proper navigation of payments (payment IN/OUT)
Code: @SQL=SELECT COALESCE(ISRECEIPT,'Y') AS ISRECEIPT FROM FIN_PAYMENT WHERE FIN_PAYMENT_ID = @FIN_PAYMENT_ID@

Auxiliar Input: isReceipt
Window: Remittance Type
Tab: Remittance Type
Description: Used for remit for discount field to calculate display logic of the field. It should be just visible when remittance allows payment IN.
Code: @SQL=SELECT FIN_PAYMENTMETHOD.PAYIN_ALLOW AS ISRECEIPT FROM REM_REMITTANCE_TYPE, FIN_PAYMENTMETHOD WHERE REM_REMITTANCE_TYPE.FIN_PAYMENTMETHOD_ID = FIN_PAYMENTMETHOD.FIN_PAYMENTMETHOD_ID AND REM_REMITTANCE_TYPE_ID = @REM_REMITTANCE_TYPE_ID@

Data Set

Data Set: Remittance Types
Description: Inserts standard remittance type with its payment method and execution process
Elements: Remittance Type ('REM_Remittance_Type'), Payment Method ('FIN_PaymentMethod'), Execution Process ('FIN_Pay_Exec_Process'), 
Execution Process Parameters ('FIN_Pay_Exec_Process_Para')

Data Set: Document Types
Description: Inserts Document Type and related Sequence
Elements: Document Type ('C_DocType'), Sequence ('AD_Sequence')

Module Script

Name: Accounting Configuration
Description: Adds Remittance, Remittance Cancel and Remittance Return to the list of Tables available for existing Accounting Schemas in the instance 
and populates Period Control entries for existing Periods in the system.

Entity Relationship diagram

[Not Yet Available]

Database Structure definition

Relation of tables and triggers that needs to be included in the module.


REM_Remittance_Type table

REM_Remittance_Type
Column Name Name Reference Length Description
AD_Client_ID Client TableDir 32 Client for this installation.
AD_Org_ID Organization TableDir 32 Organizational entity within client
C_Glitem_ID G/L Item TableDir 32 An alias for the Account Combination which can be commonly used in daily operations.
Classname Java Class Name String 1000 x not implemented
Consolidate Consolidate YesNo 1 Consolidate
Created Creation Date DateTime 19 The date that this record is completed.
Createdby Created By Search 32 User who created this records
FIN_Paymentmethod_ID Payment Method TableDir 32 It is the method by which payment is expected to be made or received.
FIN_PM_Consolidated_ID PM Consolidated Table 32
Isactive Active YesNo 1 A flag indicating whether this record is available for use or de-activated.
Name Name String 60 A non-unique identifier for a record/document often used as a search tool.
REM_Remittance_Type_ID Remittance Type ID 32
Updated Updated DateTime 19 x not implemented
Updatedby Updated By Search 32 User who updated this records

REM_Remittance_Type_Param table

REM_Remittance_Type_Param
Column Name Name Reference Length Description
AD_Client_ID Client TableDir 32 Client for this installation.
AD_Org_ID Organization TableDir 32 Organizational entity within client
Created Creation Date DateTime 19 The date that this record is completed.
Createdby Created By Search 32 User who created this records
Isactive Active YesNo 1 A flag indicating whether this record is available for use or de-activated.
Name Name String 60 A non-unique identifier for a record/document often used as a search tool.
REM_Remittance_Type_ID Remittance Type TableDir 32
REM_Remittance_Type_Param_ID REM_Remittance_Type_Param_ID ID 32 Parameter for the remittance type.
Updated Updated DateTime 19 x not implemented
Updatedby Updated By Search 32 User who updated this records
Value Search Key String 60 A fast method for finding a particular record.

REM_Remittance_Type_Acct table

REM_Remittance_Type_Acct
Column Name Name Reference Length Description
AD_Client_ID Client TableDir 32 Client for this installation.
AD_Org_ID Organization TableDir 32 Organizational entity within client
C_Acctschema_ID Accounting Schema TableDir 32 The structure used in accounting including costing methods, currencies and the calendar.
Created Creation Date DateTime 19 The date that this record is completed.
Createdby Created By Search 32 User who created this records
Isactive Active YesNo 1 A flag indicating whether this record is available for use or de-activated.
REM_Cancel_Acct Cancel Account Search 32
REM_Remittance_Type_Acct_ID REM_Remittance_Type_Acct_ID ID 32
REM_Remittance_Type_ID Remittance Type TableDir 32
REM_Sent_Acct Sent Account Search 32
Updated Updated DateTime 19 x not implemented
Updatedby Updated By Search 32 User who updated this records

REM_Remittance table

REM_Remittance
Column Name Name Reference Length Description
AD_Client_ID Client TableDir 32 Client for this installation.
AD_Org_ID Organization TableDir 32 Organizational entity within client
C_Doctype_ID Document Type TableDir 32 A value defining what sequence and process setup are used to handle this document.
Created Creation Date DateTime 19 The date that this record is completed.
Createdby Created By Search 32 User who created this records
Createfrom Create Lines From Button 1 An addition of statements from pre-existing documents.
Datetrx Transaction Date Date 19 The date that a specified transaction is entered into the application.
DocumentNo Document No. String 30 An often automatically generated identifier for all documents.
Duedate Due Date Date 19 The date when a specified request must be carried out by.
FIN_Financial_Account_ID Financial Account TableDir 32 Financial account used to deposit / withdrawal money such as bank accounts or petty cash
FIN_Payment_ID Payment TableDir 32 Payment event
Getfile Get file Button 1 Creates a File
Isactive Active YesNo 1 A flag indicating whether this record is available for use or de-activated.
Name Name String 60 A non-unique identifier for a record/document often used as a search tool.
Posted Posted Button 60 An accounting status button that indicates if the transaction has already been posted to the general ledger or not.
Processed Processed YesNo 1 A confirmation that the associated documents or requests are processed.
Processing Process Now YesNo 1 A request to process the respective document or task.
Process_Remittance Process Remittance Button 60
REM_Remittance_ID Remittance ID 32 Set of open items sent to the bank for their managing
REM_Remittance_Type_ID Remittance Type TableDir 32
Selectpayments Selectpayments Button 1 Select payments to be included in the remittance
Updated Updated DateTime 19 x not implemented
Updatedby Updated By Search 32 User who updated this records'


Trigger Definition:

REM_RemittanceLine table

REM_RemittanceLine
Column Name Name Reference Length Description
AD_Client_ID Client TableDir 32 Client for this installation.
AD_Org_ID Organization TableDir 32 Organizational entity within client
Amount Amount Amount 10
Created Creation Date DateTime 19 The date that this record is completed.
Createdby Created By Search 32 User who created this records
FIN_Payment_ID Payment TableDir 32 Payment event
FIN_Payment_Scheduledetail_ID Payment Schedule Detail TableDir 32
Isactive Active YesNo 1 A flag indicating whether this record is available for use or de-activated.
Line Line No. Integer 12 A line stating the position of this request in the document.
REM_Remittance_ID Remittance TableDir 32 Set of open items sent to the bank for their managing
REM_Remittanceline_ID Remittance Line ID 32 Each of the lines contained ina a remittance
Updated Updated DateTime 19 x not implemented
Updatedby Updated By Search 32 User who updated this records

REM_RemittanceLine_Cancel table

REM_RemittanceLine_Cancel
Column Name Name Reference Length Description
AD_Client_ID Client TableDir 32 Client for this installation.
AD_Org_ID Organization TableDir 32 Organizational entity within client
Created Creation Date DateTime 19 The date that this record is completed.
Createdby Created By Search 32 User who created this records
DateAcct Accounting Date Date 19 The date this transaction is recorded for in the general ledger.
FIN_Payment_ID Payment Search 32 Payment event
Isactive Active YesNo 1 A flag indicating whether this record is available for use or de-activated.
Line Line No. Integer 12 A line stating the position of this request in the document.
Posted Posted Button 60 An accounting status button that indicates if the transaction has already been posted to the general ledger or not.
Processed Processed YesNo 1 A confirmation that the associated documents or requests are processed.
Processing Process Now YesNo 1 A request to process the respective document or task.
REM_Remittance_ID Remittance Search 32 Set of open items sent to the bank for their managing
REM_Remittanceline_Cancel_ID Remittance Line Cancel ID 32
Updated Updated DateTime 19 x not implemented
Updatedby Updated By Search 32 User who updated this records

REM_RemittanceLine_Return table

REM_RemittanceLine_Return
Column Name Name Reference Length Description
AD_Client_ID Client TableDir 32 Client for this installation.
AD_Org_ID Organization TableDir 32 Organizational entity within client
Created Creation Date DateTime 19 The date that this record is completed.
Createdby Created By Search 32 User who created this records
DateAcct Accounting Date Date 19 The date this transaction is recorded for in the general ledger.
FIN_Payment_ID Payment TableDir 32 Payment event
Isactive Active YesNo 1 A flag indicating whether this record is available for use or de-activated.
Line Line No. Integer 12 A line stating the position of this request in the document.
Posted Posted Button 60 An accounting status button that indicates if the transaction has already been posted to the general ledger or not.
Processed Processed YesNo 1 A confirmation that the associated documents or requests are processed.
Processing Process Now YesNo 1 A request to process the respective document or task.
REM_Remittance_ID Remittance TableDir 32 Set of open items sent to the bank for their managing
REM_RemittanceLine_Return_ID Remittance Line Returned ID 32
Updated Updated DateTime 19 x not implemented
Updatedby Updated By Search 32 User who updated this records

REM_Remittance_Trg trigger

DECLARE 
/*************************************************************************
* The contents of this file are subject to the Openbravo  Public  License
* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
* Version 1.1  with a permitted attribution clause; you may not  use this
* file except in compliance with the License. You  may  obtain  a copy of
* the License at http://www.openbravo.com/legal/license.html
* Software distributed under the License  is  distributed  on  an "AS IS"
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
* License for the specific  language  governing  rights  and  limitations
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
* All portions are Copyright (C) 2011 Openbravo SLU
* All Rights Reserved.
* Contributor(s):  ______________________________________.
************************************************************************/
/*************************************************************************
* Title: Check in tg_op = 'DELETE', remittance not processed
************************************************************************/
v_DateNull TIMESTAMP := TO_DATE('31-12-9999','DD-MM-YYYY');   
BEGIN     
    IF AD_isTriggerEnabled()='N' THEN IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; 
    END IF;
  -- If remittance is processed, is not allowed to change
  IF (TG_OP = 'UPDATE') THEN
    IF (OLD.processed = 'Y' AND ((COALESCE (OLD.documentno, )<> COALESCE (NEW.documentno, ))
      OR (COALESCE (OLD.NAME, ) <> COALESCE (NEW.NAME, ))
      OR (COALESCE (OLD.datetrx, v_DateNull)<>COALESCE (NEW.datetrx, v_DateNull))
      OR (COALESCE (OLD.fin_financial_account_id, '0')<> COALESCE (NEW.fin_financial_account_id, '0'))
      OR (COALESCE (OLD.duedate, v_DateNull)<> COALESCE (NEW.duedate, v_DateNull))
      OR (COALESCE (OLD.rem_remittance_type_id,'0') <> COALESCE (NEW.rem_remittance_type_id,'0'))
      OR ((COALESCE (OLD.fin_payment_id, '0')<> COALESCE (NEW.fin_payment_id, '0')) AND NEW.processed!='N')
      OR ((COALESCE (OLD.c_doctype_id, '0') <> COALESCE (NEW.c_doctype_id, '0')) AND NEW.processed!='N')
      OR (COALESCE(old.AD_ORG_ID, '0') <> COALESCE(new.AD_ORG_ID, '0'))
      OR (COALESCE(old.AD_CLIENT_ID, '0') <> COALESCE(new.AD_CLIENT_ID, '0'))
     )
       ) THEN
      RAISE EXCEPTION '%', 'Document processed/posted'; --OBTG:-20501--
    END IF;
  END IF;
  IF (TG_OP = 'INSERT') THEN
    IF (NEW.processed = 'Y') THEN
      RAISE EXCEPTION '%', 'Document processed/posted'; --OBTG:-20501--
    END IF;
  END IF;
  IF (TG_OP = 'DELETE') THEN
    IF (OLD.processed = 'Y') THEN
      RAISE EXCEPTION '%', 'Document processed/posted'; --OBTG:-20501--
    END IF;
  END IF;
IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; 
END 
;

REM_RemittanceLine_Trg trigger

 DECLARE 

    /*************************************************************************
    * The contents of this file are subject to the Openbravo  Public  License
    * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
    * Version 1.1  with a permitted attribution clause; you may not  use this
    * file except in compliance with the License. You  may  obtain  a copy of
    * the License at http://www.openbravo.com/legal/license.html
    * Software distributed under the License  is  distributed  on  an "AS IS"
    * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    * License for the specific  language  governing  rights  and  limitations
    * under the License.
    * The Original Code is Openbravo ERP.
    * The Initial Developer of the Original Code is Openbravo SLU
    * All portions are Copyright (C) 2011 Openbravo SLU
    * All Rights Reserved.
    * Contributor(s):  ______________________________________.
    ************************************************************************/
  v_Processed VARCHAR(60) ;
  v_REM_Remittance_ID VARCHAR(32) ; --OBTG:VARCHAR2--
  v_Prec NUMERIC:=2;
  v_Currency        VARCHAR(32); --OBTG:VARCHAR2--
    
BEGIN
    
    IF AD_isTriggerEnabled()='N' THEN IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE  RETURN NEW; END IF; 
    END IF;


  IF TG_OP = 'INSERT' THEN
    v_REM_Remittance_ID:=NEW.REM_Remittance_ID;
  ELSE
    v_REM_Remittance_ID:=OLD.REM_Remittance_ID;
  END IF;
  SELECT PROCESSED
  INTO v_Processed
  FROM REM_Remittance
  WHERE REM_Remittance_ID=v_REM_Remittance_ID;
  IF TG_OP = 'UPDATE' THEN
    IF(v_Processed='Y' AND
         (
       (COALESCE(old.ISACTIVE, ) <> COALESCE(NEW.ISACTIVE, ))
       OR(COALESCE(old.LINE, 0) <> COALESCE(NEW.LINE, 0))
       OR(COALESCE(old.FIN_PAYMENT_ID, '0') <> COALESCE(NEW.FIN_PAYMENT_ID, '0'))
       OR(COALESCE(old.FIN_PAYMENT_SCHEDULEDETAIL_ID, '0') <>  COALESCE(NEW.FIN_PAYMENT_SCHEDULEDETAIL_ID, '0'))
       OR(COALESCE(old.AMOUNT, '0') <> COALESCE(new.AMOUNT, '0'))
       OR(COALESCE(old.AD_ORG_ID, '0') <> COALESCE(new.AD_ORG_ID, '0'))
       OR(COALESCE(old.AD_CLIENT_ID, '0') <> COALESCE(new.AD_CLIENT_ID, '0'))
       )) THEN
      RAISE EXCEPTION '%', 'Document processed/posted' ; --OBTG:-20501--
    END IF;
  END IF;
  IF((TG_OP = 'DELETE' OR TG_OP = 'INSERT') AND v_Processed='Y') THEN
    RAISE EXCEPTION '%', 'Document processed/posted' ; --OBTG:-20501--
  END IF;
IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; 

END 

;

REM_RemittanceLine_Cancel_Trg trigger

 DECLARE 

    /*************************************************************************
    * The contents of this file are subject to the Openbravo  Public  License
    * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
    * Version 1.1  with a permitted attribution clause; you may not  use this
    * file except in compliance with the License. You  may  obtain  a copy of
    * the License at http://www.openbravo.com/legal/license.html
    * Software distributed under the License  is  distributed  on  an "AS IS"
    * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    * License for the specific  language  governing  rights  and  limitations
    * under the License.
    * The Original Code is Openbravo ERP.
    * The Initial Developer of the Original Code is Openbravo SLU
    * All portions are Copyright (C) 2011 Openbravo SLU
    * All Rights Reserved.
    * Contributor(s):  ______________________________________.
    ************************************************************************/
    
BEGIN
    
    IF AD_isTriggerEnabled()='N' THEN IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE  RETURN NEW; END IF; 
    END IF;
  IF TG_OP = 'UPDATE' THEN
    IF(OLD.Processed='Y' AND
         (
       (COALESCE(old.ISACTIVE, ) <> COALESCE(NEW.ISACTIVE, ))
       OR(COALESCE(old.LINE, 0) <> COALESCE(NEW.LINE, 0))
       OR(COALESCE(old.FIN_PAYMENT_ID, '0') <> COALESCE(NEW.FIN_PAYMENT_ID, '0'))
       OR(COALESCE(old.AD_ORG_ID, '0') <> COALESCE(new.AD_ORG_ID, '0'))
       OR(COALESCE(old.AD_CLIENT_ID, '0') <> COALESCE(new.AD_CLIENT_ID, '0'))
       )) THEN
      RAISE EXCEPTION '%', 'Document processed/posted' ; --OBTG:-20501--
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    IF (NEW.processed = 'Y') THEN
      RAISE EXCEPTION '%', 'Document processed/posted'; --OBTG:-20501--
    END IF;
  END IF;

  IF (TG_OP = 'DELETE') THEN
    IF (OLD.processed = 'Y') THEN
      RAISE EXCEPTION '%', 'Document processed/posted'; --OBTG:-20501--
    END IF;
  END IF;
IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;  

END  

;

REM_RemittanceLine_Return_Trg trigger

 DECLARE 

    /*************************************************************************
    * The contents of this file are subject to the Openbravo  Public  License
    * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
    * Version 1.1  with a permitted attribution clause; you may not  use this
    * file except in compliance with the License. You  may  obtain  a copy of
    * the License at http://www.openbravo.com/legal/license.html
    * Software distributed under the License  is  distributed  on  an "AS IS"
    * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    * License for the specific  language  governing  rights  and  limitations
    * under the License.
    * The Original Code is Openbravo ERP.
    * The Initial Developer of the Original Code is Openbravo SLU
    * All portions are Copyright (C) 2011 Openbravo SLU
    * All Rights Reserved.
    * Contributor(s):  ______________________________________.
    ************************************************************************/
    
BEGIN
    
    IF AD_isTriggerEnabled()='N' THEN IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE     RETURN NEW; END IF; 
    END IF;
  IF TG_OP = 'UPDATE' THEN
    IF(OLD.Processed='Y' AND
         (
       (COALESCE(old.ISACTIVE, ) <> COALESCE(NEW.ISACTIVE, ))
       OR(COALESCE(old.LINE, 0) <> COALESCE(NEW.LINE, 0))
       OR(COALESCE(old.FIN_PAYMENT_ID, '0') <> COALESCE(NEW.FIN_PAYMENT_ID, '0'))
       OR(COALESCE(old.AD_ORG_ID, '0') <> COALESCE(new.AD_ORG_ID, '0'))
       OR(COALESCE(old.AD_CLIENT_ID, '0') <> COALESCE(new.AD_CLIENT_ID, '0'))
       )) THEN
      RAISE EXCEPTION '%', 'Document processed/posted' ; --OBTG:-20501--
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    IF (NEW.processed = 'Y') THEN
      RAISE EXCEPTION '%', 'Document processed/posted'; --OBTG:-20501--
    END IF;
  END IF;

  IF (TG_OP = 'DELETE') THEN
    IF (OLD.processed = 'Y') THEN
      RAISE EXCEPTION '%', 'Document processed/posted'; --OBTG:-20501--
    END IF;
  END IF;
IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; 

END 

;

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Remittances/Technical_Documentation"

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