Projects:Remittances/Technical Documentation
Remittances infrastructure
Module Definition
A module must be created with the following parameters:
- Name: Remittances infrastructure
- Version: 1.0.0.
- Java package: org.openbravo.module.remittance
- Description: This module enables creation, printing and posting of remittances.
- Language: En_US
- License: Openbravo Public License
- License Text: Licensed under the Openbravo Public License Version 1.1.
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.
- Author: Openbravo S.L.U.
In the dependency tab:
- Dependant on Advanced Payables and Receivables version 1.0.14
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
- Description: Create and edit remittance types with corresponding parameters according to your business needs.
- Help: Create and edit remittance types with corresponding parameters according to your business needs.
This new window has three tabs called Remittance Type Accounting and Parameter.
Remittance Type tab:
- Name: Remittance Type
- Description: Create and edit remittance types.
- Table: REM_REMITTANCE_TYPE
- UI Pattern: Standard.
Accounting tab:
- Name: Accounting
- Description: Accounting configuration associated to the given Remittance Type.
- Table: REM_REMITTANCE_TYPE_PARAM
- UI Pattern: Standard.
Parameters tab:
- Name: Parameters
- Description: Create and edit remittance type parameters.
- Table: REM_REMITTANCE_TYPE_PARAM
- UI Pattern: Standard.
Remittance window
- Description: Edit payments by using remittances to cancel or return them.
Remittance tab: Fields included (descriptions are provisional):
- Document Type: The Document Type determines document sequence and processing rules
- Document No.: An often automatically generated identifier for all documents.
- Remittance Type: Remittance Type
- Transaction Date: The date that a specified transaction is entered into the application.
- Due date: The date when a specified request must be carried out by.
- Name: A non-unique identifier for a record/document often used as a search tool.
- Financial Account: Financial account used to deposit / withdrawal money such as bank accounts or petty cash
- Payment: Payment done by bank when consolidated remittance.
- Processed: A confirmation that the associated documents or requests are processed.
Buttons:
- Select Payments: Select payments to be included in the remittance
- Select Orders or Invoices: Select Orders or Invoices and add them as lines for the remittance
- Processed: A confirmation that the associated documents or requests are processed.
- Create Remittance File: Create Remittance File to be sent to the bank. Just visible when remittance type provides a java class to implement such logic..
- Process Remittance: Process a remittance and group payments for same business partner is necessary.
- Posted: An accounting status button that indicates if the transaction has already been posted to the general ledger or not.
Lines tab: Fields included (descriptions are provisional):
- Remittance.: Refering parent remittance.
- Line No.: A line stating the position of this request in the document.
- Payment Schedule Detail.: Referinga payment schedule detail coming from an order or invoice to be collected/paid.
- Amount: Amount to be collected or paid
- Payment: Related Payment Event.
Canceled tab: Fields included (descriptions are provisional):
- Remittance.: Refering parent remittance.
- Accounting Date: Date used to book in the ledger.
- Line No.: A line stating the position of this request in the document.
- Payment: Payment event which has been fully collected or paid.
- Processed: A confirmation that the associated documents or requests are processed.
Buttons:
- Posted: An accounting status button that indicates if the transaction has already been posted to the general ledger or not.
Returned tab: Fields included (descriptions are provisional):
- Remittance.: Refering parent remittance.
- Accounting Date: Date used to book in the ledger.
- Line No.: A line stating the position of this request in the document.
- Payment: Payment event which has been returned by bank.
- Processed: A confirmation that the associated documents or requests are processed.
Buttons:
- Posted: An accounting status button that indicates if the transaction has already been posted to the general ledger or not.
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 ;