Projects/AdvPaymentMngt/Technical Documentation

From InfiniteERP Wiki
Jump to: navigation, search

Contents

Advanced Payables and Receivables

Module Definition

A module must be created with the following parameters:

  • Name: Advanced Payables and Receivables
  • Version: 1.0.0.
  • Java package: org.openbravo.advpaymentmngt (Provisional)
  • Description: This module enhances the user experience in managing open invoices in Openbravo ERP. The scope of this project addresses workflows in Customer Payment (AR), Supplier Payment (AP) and Financial Account Management. The enhanced payment workflows have been simplified and include a full reconciliation process. Additionally, the accounting events driven by the workflow will be clearer, more easily configurable and transparent to end users.
  • Language: En_US
  • License: Commercial
  • License Text: Advanced Payables and Receivables is based on the Openbravo Commercial License that can be found here: http://www.openbravo.com/legal/OBCL10/BasicLicense.html
  • Author: Openbravo S.L.U.

In the dependency tab:

  • Dependant on core

In the prefix tab: Prefix=APRM (Advanced Payables and Receivables Management)

In the datapackage tab: org.openbravo.advpaymentmngt

Bulbgraph.png   This module is going to be included in QuickStart. Based in the Functional Documentation some core changes will also be needed. So during the development phase the module will be considered as a template and when it is completed all the changes of the template will be merged in the QuickStart template, setting back this module as a module.
Bulbgraph.png   All new tables needed on this module will be created in core with the new FIN prefix.

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.

Sales Invoice window

List of artifacts needed on this core window:

New tabs and fields

In this core window new fields are added in the Header tab and 2 new tabs are also added, Payment Plan and Payment Detail.

Header tab:

Bulbgraph.png   When the functional spec. were done the Payment monitor feature was not yet included in main, a review of the mockup is needed.

There are some inconsistencies in the proposed field names that are different on Edit view and Relation view mockups.

New fields included (descriptions are provisional):

  • Due date: Only visible in the Relation view mockup. Shows the date of the next due date. Same information is provided by current Days till due field.
  • Days Overdue/Overdue: Number of days of delay of the payment. Same information is provided by current Days till due field.
  • Overdue amount/Amount overdue: Amount not paid when there is an overdue.
  • Add Payment: Button that launches the Add Payment process.

Fields with name changes, most of them included in the Payment Monitor:

  • Total received payments/Received: Core's Total paid.
  • Amount pending/Pending: Core's Due Amount.
  • Gross Amount/Total Invoiced:Core's Grand Total Amount.

Fields shown in the Relation view are:

  1. Due date (Days till due)
  2. Invoice (Document No.)
  3. Business Partner
  4. Total Invoiced (Gross amount or Grand Total Amount)
  5. Received (Total Received Payments)
  6. Pending (Amount pending)
  7. Overdue (Days overdue)
  8. Overdue Amount (Amount overdue)

Payment Plan tab:

  • Name: Payment Plan (Provisional)
  • Table: FIN_PAYMENT_SCHED_INV_V
  • Child of the Header following the Tax tab.
  • UI Pattern: Read Only.

Check the mockup to get the relation of fields.

Payment Detail tab:

  • Name: Payment Detail.
  • Table: FIN_Payment_Detail_V
  • Child of the Payment Plan tab
  • UI Patter: Read only

Check the mockup to get the relation of fields.

Add Payment process

In the invoices window a new button is going to be set, this button will allow the user to add payments done by customer to the selected invoice. To do it this button will open a new Pop-up window to manage the selection of the different remaining invoices.

Process Definition:

  • Search key: AddPayment
  • Name: Add Payment
  • Data access level: Organization. (Provisional)
  • UI Pattern: Manual.

In Process Class tab:

  • Java Class: org.openbravo.advpaymentmngt.AddPayment.

File:Receive-Payments 0006 Add-Payments-Pop.png

Generate Payment Schedule Extension Points

New procedures APRM_GEN_PAYMENTSCHEDULE_INV and APRM_GEN_PAYMENTSCHEDULE_ORD, to be added to core's C_Invoice_Post - End process and C_Order_Post - End process extension points.

This procedure has to populate the FIN_PAYMENT_SCHEDULE table when the invoices or orders, both sales and purchase, are completed. The process has to be very similar to current c_debt_payment generation in the c_invoice_post procedure.

  • When invoices are completed: Populate FIN_Payment_Schedule table based on the payment terms of the invoice for the total amount of the invoice, if a related order has some payment done it has to be deducted to the amount.
  • When invoices are closed or reactivated: If there is some payment done the invoice cannot be reactivated, if not delete the related payment schedules and create the payment schedule of the order if necessary.
  • When orders are completed: Add one record to the FIN_Payment_Schedule table with the total amount.

Sales Order window

List of artifacts needed on this core window:

New tabs and fields

In this core window 2 new tabs are also added, Payment Plan and Payment Detail.

Payment Plan tab:

  • Name: Payment Plan (Provisional)
  • Table: FIN_PAYMENT_SCHED_ORD_V
  • Child of the Header following the Tax tab.
  • UI Pattern: Read Only.

Check the mockup to get the relation of fields.

Payment Detail tab:

  • Name: Payment Detail.
  • Table: FIN_Payment_Detail_V
  • Child of the Payment Plan tab
  • UI Patter: Read only

Check the mockup to get the relation of fields.

Receive Payment Window

New window, Receive Payment, created under Sales Management || Transactions. This window has 4 buttons that are shown / hidden depending on the status of the payment. These are:

  • Select Sales Orders or Sales Invoices manual window.
  • Process Payment / Reactivate process.
  • Reconcile manual window.
  • Posted accounting process.

Window definition

  • Description: Window to manage the payments done by customers (Provisional).
  • Help: Window to manage the payments done by customers (Provisional).

This new window has two tabs called Header and Lines.

Header tab:

  • Name: Header
  • Description: Add new customer payment into the system. (Provisional)
  • Table: FIN_PAYMENT
  • UI Pattern: Standard.
  • Notice that fields have a Read only logic depending of the payment status.

Check the mockup to get the relation of fields.

Lines tab:

  • Name: Lines
  • Description: Add new customer payment into the system.
  • Table: FIN_PAYMENT_DETAIL_V
  • UI Pattern: Read Only.

Check the mockup to get the relation of fields.

Add Order or Invoices process

In the commented new window named Receive Payment a new button is going to set in the header, this button will lunch a new process named Add Order or Invoices and will allow the user to select invoices or order to asociate to the created new payment. To manage this process a pop-up will be available to the user to select order or invoiced related to the business partner. Proccess Definition:

  • Search key: AddOrderInvoices
  • Name: Add Orders or Invoices
  • Data access level: Organization. (Provisional)
  • UI Pattern: Manual.

In Process Class tab:

  • Java Class: org.openbravo.advpaymentmngt.AddOrderInvoice

File:Receive-Payments 0017 Payment-select-invoices-POP.png

Process Receipt Payment and Reactivate process

Process that depending on the status of the payment processes or reactivate it.


Reconcile process

Manual popup window to reconcile the payments. These can be deposited, issued, received or withdrawn


Posted accounting process

Accounting process to post the payment when it is processed. The accounting can be configured when the payment is processed or when the payment is deposited in a financial account, so this button shall be hidden or shown depending on this configuration. Posting of invoices will have to be modified as well. New payment flow will be taken into account when it exists but default payment flow compatibility has to be preserved. Additional accounting configuration window has to be added to financial account to be able to define the accounting rules per a combination of financial account and payment method, and per accounting schema (TBD).

Objects: New java class creation for payment document. New java class creation for template (to be able to redefine the behavior through a module)

Purchase Invoice window

The changes of this window are the same than the changes of the Sales Invoice window.

New tabs and fields

In this core window new fields are added in the Header tab and 2 new tabs are also added, Payment Plan and Payment Detail.

Header tab:

Bulbgraph.png   When the functional spec. wad done the Payment monitor feature was not yet included in main, a review of the mockup is needed.

There are some inconsistencies in the proposed field names that are different on Edit view and Relation view mockups.

New fields included (descriptions are provisional):

  • Due date: Only visible in the Relation view mockup. Shows the date of the next due date. Same information is provided by current Days till due field.
  • Days Overdue/Overdue: Number of days of delay of the payment. Same information is provided by current Days till due field.
  • Overdue amount/Amount overdue: Amount not paid when there is an overdue.
  • Add Payment: Button that launches the Add Payment process, notice that in this document it is described the process based on a receipt transaction.

Fields with name changes, most of them included in the Payment Monitor:

  • Total received payments/Received: Core's Total paid.
  • Amount pending/Pending: Core's Due Amount.
  • Gross Amount/Total Invoiced:Core's Grand Total Amount.

Fields shown in the Relation view are:

  1. Due date (Days till due)
  2. Invoice (Document No.)
  3. Business Partner
  4. Total Invoiced (Gross amount or Grand Total Amount)
  5. Received (Total Received Payments)
  6. Pending (Amount pending)
  7. Overdue (Days overdue)
  8. Overdue Amount (Amount overdue)

Payment Plan tab:

  • Name: Payment Plan (Provisional)
  • Table: FIN_PAYMENT_SCHED_INV_V
  • Child of the Header following the Tax tab.
  • UI Pattern: Read Only.

Check the mockup to get the relation of fields.

Payment Detail tab:

  • Name: Payment Detail.
  • Table: FIN_Payment_Detail_V
  • Child of the Payment Plan tab
  • UI Patter: Read only

Check the mockup to get the relation of fields.

Purchase Order window

List of artifacts needed on this core window:

New tabs and fields

In this core window 2 new tabs are also added, Payment Plan and Payment Detail.

Payment Plan tab:

  • Name: Payment Plan (Provisional)
  • Table: FIN_PAYMENT_SCHED_ORD_V
  • Child of the Header following the Tax tab.
  • UI Pattern: Read Only.

Check the mockup to get the relation of fields.

Payment Detail tab:

  • Name: Payment Detail.
  • Table: FIN_Payment_Detail_V
  • Child of the Payment Plan tab
  • UI Patter: Read only

Check the mockup to get the relation of fields.

Payment Proposal window

New window, Payment Proposal, defined under Procurement Management || Transactions. This window adds 2 button:

  • Select Payments manual window.
  • Generate Payments, close, reactivate process.

Window definition

This window has 2 tabs Header and Lines.

Header tab:

  • Name: Header
  • Description: Add new vendor payment into the system. (Provisional)
  • Table: FIN_PAYMENT_PROPOSAL
  • UI Pattern: Standard
  • Notice that fields have a Read only logic depending on the payment status.

Check the mockup to get the relation of fields.

Lines tab:

  • Name: Lines
  • Description: Add new customer payment into the system.
  • Table: FIN_PAYMENT_PROPOSAL_DETAIL
  • UI Pattern: Read only

Check the mockup to get the relation of fields.

Select expected payments process

Manual popup window to select the pending scheduled payments of Purchase Orders and Invoices.


Process Proposal, Reactivate and Close process

Process that depending on the status and the chosen action, generate the payments, reactivates the proposal or closes it.


Payment Method Window

It is an auto generated new window, Payment Method, defined under Financial Management || Receivable & Payables || Setup. This window adds 2 tabs:

  • Payment Method
  • Accounts

Window definition

This new window has two tabs called Payment Method and Accounts.

Payment Method tab:

  • Name: Payment Method
  • Description: Add new payment method into the system.
  • Table: FIN_PaymentMethod
  • UI Pattern: Standard.

Accounts tab:

  • Name: Accounts
  • Description: Add new accounts into the Payment Method.
  • Table: FIN_FINACC_PAYMENTMETHOD
  • UI Pattern: Standard.
  • Child of the Payment Method tab.

Matching Algorithm Window

It is an auto generated new window, Matching Algorithm, defined under Financial Management || Receivable & Payables || Setup. This window has one tab:

  • Matching Algorithm

Window definition

This new window has one tab called Matching Algorithm

Matching Algorithm tab:

  • Name: Matching Algorithm
  • Description: Add new matching algorithm into the system.
  • Table: FIN_MATCHING_ALGORITHM
  • UI Pattern: Standard.

Bank File Format Window

It is an auto generated new window, Bank File Format, defined under Financial Management || Receivable & Payables || Setup. This window has one tab:

  • Bank File Format

Window definition

This new window has one tab called Bank File Format

Bank File Format tab:

  • Name: Bank File Format
  • Description: Add new bank file format into the system.
  • Table: FIN_BANKFILE_FORMAT
  • UI Pattern: Standard.


Financial Account Window

It is an auto generated new window, Financial Account, defined under Financial Management || Receivable & Payables || Transactions. This window has seven tabs. The transactions tab is manually developed tab.

  • Account
  • Transactions
  • Accounting history
  • Imported bank statements
  • Bank Statement Lines
  • Reconciliations
  • Reconciliations Lines

Window definition

Account tab:

  • Name  : Account
  • Description: Details of the account
  • Table  : FIN_FINANCIAL_ACCOUNT
  • UI Pattern : Standard.
  • Header tab of the Financial Account window.

Accounting history tab:

  • Name  : Accounting history
  • Description: Accounting history of the financial account
  • Table  : APRM_FINACC_TRANSACTION_ACCT_V
  • UI Pattern : ReadOnly.
  • Child of the Account following the Transactions tab.

Transactions tab:

  • Name  : Transactions
  • Description: Transactions associated with the financial account.
  • Table  : FIN_FINACC_TRANSACTION
  • UI Pattern : Standard.
  • Master Detail Form: AccountTransactions.
  • Child of the account tab.

Imported Bank Statements tab:

  • Name  : Imported Bank Statements
  • Description: Matching bank statement with existing details.
  • Table  : FIN_BANKSTATEMENT
  • UI Pattern : Standard.
  • Child of the Account following the Accounting history tab.

Bank Statements Lines tab:

  • Name  : Bank Statements Lines
  • Description: Adding new lines to the bank statement.
  • Table  : FIN_BANKSTATEMENTLINE
  • UI Pattern : Standard.
  • Child of the Imported Bank Statements tab.

Reconciliations tab:

  • Name  : Reconciliations
  • Description: Details of reconciliation of the financial account.
  • Table  : APRM_RECONCILIATION_V
  • UI Pattern : Standard.
  • Child of the Account following the Imported Bank Statement tab.

Reconciliations Lines tab:

  • Name  : Reconciliations Lines
  • Description: Details of reconciliation Line of the financial account.
  • Table  : FIN_RECONCILIATIONLINE_V
  • UI Pattern : ReadOnly.
  • Child of the Reconciliations tab.

Entity Relationship diagram

File:APRM entityrelation.png
Advanced Payables and Receivables Entity Relation diagram

Database Structure definition

Relation of tables and views that needs to be included or modified. These tables and views are to be included in core except the columns related to the buttons that open the manual windows.

C_Invoice table

C_Invoice
Column Name Type Length Comments
EM_FIN_ADDPAYMENT CHAR 1 Relates to the button used to add payments to an existing invoice

FIN_Payment table

FIN_PAYMENT
Column Name Type Length Comments
FIN_PAYMENT_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
ISRECEIPT CHAR 1 Referred to A/R when positive and to A/P when negative.
DESCRIPTION VARCHAR 255 A space to write additional related information.
C_BPARTNER_ID VARCHAR 32 Foreign key to C_BPARTNER indicating the BPartner it relates to.
FIN_FINANCIAL_ACCOUNT_ID VARCHAR 32 Foreign key to FIN_FINANCIAL_ACCOUNT indicating the account from which the payment will be made.
PAYMENTDATE DATE Date of payment event.
C_CURRENCY_ID VARCHAR 32 Foreign key to C_CURRENCY indicating the currency used for the transaction.
AMOUNT DECIMAL Amount of the transaction.
WRITEOFFAMT DECIMAL Amount that has been wrote off
FIN_PAYMENTMETHOD_ID VARCHAR 32 Foreign key to FIN_PAYMENTMETHOD indicating the payment rule used in this transaction.
DOCUMENTNO VARCHAR 30 Document number for the payment.
REFERENCENO VARCHAR 40 Document number given by the customer for the payment.
STATUS VARCHAR 40 Status of the payment (Payment received, deposited not cleared, payment cleared).
PROCESSED CHAR 1 Flag indicating the event is ready to be accounted.
PROCESSING CHAR 1 Flag indicating the event is locked as is being used in a process.
POSTED CHAR 1 Flag indicating the event is already accounted.
C_DOCTYPE_ID VARCHAR 32 Foreign key to C_DOCTYPE indicating the document type used for the transaction.
C_PROJECT_ID VARCHAR 32 Foreign key to C_PROJECT indicating the project used for the transaction.
C_CAMPAIGN_ID VARCHAR 32 Foreign key to C_CAMPAIGN indicating the campaign used for the transaction.
C_ACTIVITY_ID VARCHAR 32 Foreign key to C_ACTIVITY indicating the activity used for the transaction.
USER1_ID VARCHAR 32 Indicating the first dimension
USER2_ID VARCHAR 32 Indicating the second dimension
EM_APRM_PROCESS_PAYMENT VARCHAR 60 Column for the Process button
EM_APRM_RECONCILE_PAYMENT CHAR 1 Column for the Reconcile button
EM_APRM_ADD_SCHEDULEDPAYMENTS CHAR 1 Relates to the button used to add schedule payments to an existing payment

FIN_Payment_Schedule table

FIN_PAYMENT_SCHEDULE
Column Name Type Length Comments
FIN_PAYMENT_SCHEDULE_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
C_INVOICE_ID VARCHAR 32 Foreign key to C_INVOICE indicating the invoice it relates to.
C_ORDER_ID VARCHAR 32 Foreign key to C_ORDER indicating the order it relates to.
DUEDATE DATE Date upon the payment is due.
FIN_PAYMENTMETHOD_ID VARCHAR 32 Foreign key to FIN_PAYMENTMETHOD indicating the payment rule used in this transaction.
C_CURRENCY_ID CHAR 32 Currency used in the transaction.
AMOUNT DECIMAL Amount of the transaction.
PAIDAMT DECIMAL Amount that has already been paid
OUTSTANDINGAMT DECIMAL The value of any unpaid part of a payment.

FIN_Payment_Detail Table

FIN_PAYMENT_DETAIL
Column Name Type Length Comments
FIN_PAYMENT_DETAIL_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
FIN_PAYMENT_ID VARCHAR 32 Foreign key to FIN_PAYMENT indicating the Payment it relates to.
AMOUNT DECIMAL Amount of the transaction.
REFUND CHAR 1 Used to indicate whether the amount is left as a credit for the customer or left to be refunded as a difference. Only applies when the amount do not match to any existing payment scheduling for a particular business partner.
WRITEOFFAMT DECIMAL Amount that has been wrote off
C_GLITEM_ID VARCHAR 32 Foreign key to C_GLITEM indicating the G/L item it relates to.
ISPREPAYMENT CHAR 1 Flag indicates that the payment is a prepayment

FIN_Payment_ScheduleDetail Table

FIN_PAYMENT_SCHEDULEDETAIL
Column Name Type Length Comments
FIN_PAYMENT_SCHEDULEDETAIL_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
FIN_PAYMENT_DETAIL_ID VARCHAR 32 Foreign key to FIN_PAYMENT indicating the Payment it relates to.
FIN_PAYMENT_SCHEDULE_INVOICE VARCHAR 32 Foreign key to FIN_PAYMENT_SCHEDULE indicating the invoice payment schedule it relates to.
FIN_PAYMENT_SCHEDULE_ORDER VARCHAR 32 Foreign key to FIN_PAYMENT_SCHEDULE indicating the order payment schedule it relates to.
AMOUNT DECIMAL Amount of the transaction.
WRITEOFFAMT DECIMAL Amount that has been wrote off
FIN_PAYMENT_PROP_DETAIL_ID VARCHAR 32 Foreign key to FIN_PAYMENT_PROP_DETAIL indicating the Proposal detail of the payment.

FIN_Payment_Proposal table

FIN_PAYMENT_PROPOSAL
Column Name Type Length Comments
FIN_PAYMENT_PROPOSAL_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
ISRECEIPT CHAR 1 Referred to A/R when positive and to A/P when negative.
DESCRIPTION VARCHAR 255 A space to write additional related information.
C_BPARTNER_ID VARCHAR 32 Foreign key to C_BPARTNER indicating the BPartner it relates to.
FIN_FINANCIAL_ACCOUNT_ID VARCHAR 32 Foreign key to FIN_FINANCIAL_ACCOUNT indicating the account from which the payment will be made.
PAYMENTDATE DATE Date of payment event.
C_CURRENCY_ID VARCHAR 32 Foreign key to C_CURRENCY indicating the currency used for the transaction.
AMOUNT DECIMAL Amount of the transaction.
WRITEOFFAMT DECIMAL Amount that has been wrote off
FIN_PAYMENTMETHOD_ID VARCHAR 32 Foreign key to FIN_PAYMENTMETHOD indicating the payment rule used in this transaction.
DOCUMENTNO VARCHAR 40 Document number for the payment.
REFERENCENO VARCHAR 40 Document number given by the customer for the payment.
STATUS VARCHAR 40 Status of the payment (Payment received, deposited not cleared, payment cleared).
PROCESSED CHAR 1 Flag indicating the event is ready to be accounted.
PROCESSING CHAR 1 Flag indicating the event is locked as is being used in a process.
EM_APRM_PROCESS_PROPOSAL VARCHAR 60 Column for the Process button
EM_APRM_SEL_EXPECTEDPAYMENTS CHAR 1 Column for the button to select expected payments.
C_DOCTYPE_ID VARCHAR 32 Foreign key to C_DOCTYPE indicating the document used for the transaction.

FIN_Payment_Prop_Detail table

FIN_PAYMENT_PROP_DETAIL
Column Name Type Length Comments
FIN_PAYMENT_PROP_DETAIL_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
FIN_PAYMENT_PROPOSAL_ID VARCHAR 32 Foreign key to FIN_PAYMENT indicating the Payment it relates to.
FIN_PAYMENT_SCHEDULE_ID VARCHAR 32 Foreign key to FIN_PAYMENT_SCHEDULE indicating the payment schedule it relates to.
AMOUNT DECIMAL Amount of the transaction.
WRITEOFFAMT DECIMAL Amount that has been wrote off
C_GLITEM_ID VARCHAR 32 Foreign key to C_GLITEM indicating the G/L item it relates to.

FIN_Financial_Account table

FIN_FINANCIAL_ACCOUNT
Column Name Type Length Comments
FIN_FINANCIAL_ACCOUNT_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
C_CURRENCY_ID VARCHAR 32 Foreign key to C_CURRENCY indicating the currency it relates to.
NAME NVARCHAR 60 Name of the financial account.
DESCRIPTION NVARCHAR 255 Description added to the financial account.
TYPE VARCHAR 60 The Type indicates the type of validation that will occur
C_BPARTNER_ID VARCHAR 32 Foreign key to C_BPARTNER indicating the BPartner it relates to.
C_LOCATION_ID VARCHAR 32 Foreign key to C_LOCATION indicating the location it relates to.
ROUTINGNO VARCHAR 20 The Bank Routing Number identifies a legal Bank.
SWIFTNO VARCHAR 20 The SWIFT code is an identifier of a Bank.(Society of Worldwide Interbank Financial Telecommunications)
CODEBANK VARCHAR 4 First four numbers of the bank account number which identifies the bank uniquely.
CODEBRANCH VARCHAR 4 Second group of four numbers of the bank account which identifies the branch uniquely.
BANK_DIGITCONTROL VARCHAR 1 Tenth number of the bank account.
INE_NUMBER VARCHAR 9 Number provided by the bank to configure remittances.
ACCOUNT_DIGITCONTROL VARCHAR 1
CODEACCOUNT VARCHAR 10 Code of the account.Last ten numbers of the bank account.
ACCOUNTNO VARCHAR 20 The Account Number indicates the Number assigned to this account
CURRENTBALANCE NUMERIC The Current Balance field indicates the current balance in this account.
CREDITLIMIT NUMERIC The Credit Limit field indicates the credit limit for this account.
IBAN VARCHAR 34 International standard for identifying bank accounts across national borders
ISDEFAULT CHAR 1 The Default Checkbox indicates if this record will be used as a default value.

FIN_Finacc_Transaction table

FIN_FINACC_TRANSACTION
Column Name Type Length Comments
FIN_FINACC_TRANSACTION_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
C_CURRENCY_ID VARCHAR 32 Foreign key to C_CURRENCY indicating the currency it relates to.
FIN_FINANCIAL_ACCOUNT_ID VARCHAR 32 Foreign key to FIN_FINANCIAL_ACCOUNT indicating the financial account the transaction refers to.
FIN_PAYMENT_ID VARCHAR 32 Foreign key to FIN_PAYMENT indicating the payment the transaction refers to.
DATEACCT DATE Date in which the transaction will be booked.
C_GLITEM_ID VARCHAR 32 Foreign key to C_GLITEM indicating the G/L item the transaction refers to.
STATUS VARCHAR 40 Status of the transaction (not cleared, cleared, reconciled).
PAYMENTAMT DECIMAL Amount of the transaction to be paid.
DEPOSITAMT DECIMAL Amount of the transaction to be paid.
PROCESSED CHAR 1 Flag indicating the event is ready to be accounted.
PROCESSING CHAR 1 Flag indicating the event is locked as is being used in a process.
POSTED CHAR 1 Flag indicating the event is already accounted.
C_PROJECT_ID VARCHAR 32 Foreign key to C_PROJECT indicating the project used for the transaction.
C_CAMPAIGN_ID VARCHAR 32 Foreign key to C_CAMPAIGN indicating the campaign used for the transaction.
C_ACTIVITY_ID VARCHAR 32 Foreign key to C_ACTIVITY indicating the activity used for the transaction.
USER1_ID VARCHAR 32 Indicates the first dimension
USER2_ID VARCHAR 32 Indicates the second dimension
TRXTYPE VARCHAR 60 Indicates the transaction type.
STATEMENTDATE DATE Indicates the statement date.
DESCRIPTION VARCHAR 255 A space to write additional related information.
FIN_RECONCILIATION_ID VARCHAR 32 Foreign key to FIN_RECONCILIATION indicating the reconciliations its relates to.
LINE DECIMAL 10

FIN_PaymentMethod table

FIN_PaymentMethod
Column Name Type Length Comments
FIN_PAYMENTMETHOD_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
NAME VARCHAR 60 Name of the payment method
DESCRIPTION VARCHAR 255 A space to write additional related information.

FIN_Finacc_PaymentMethod table

FIN_Finacc_PaymentMethod
Column Name Type Length Comments
FIN_FINACC_PAYMENTMETHOD_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
FIN_PAYMENTMETHOD_ID VARCHAR 32 Foreign key to FIN_PAYMENTMETHOD indicating the payment rule used in this transaction.
FIN_FINANCIAL_ACCOUNT_ID VARCHAR 32 Foreign key to FIN_FINANCIAL_ACCOUNT indicating the account from which the payment will be made.
AUTOMATIC_RECEIPT CHAR 1 Flag is indicating automatically create the receipt when the invoice is completed.
AUTOMATIC_PAYMENT CHAR 1 Flag is indicating automatically create the payment when the invoice is completed.
AUTOMATIC_DEPOSIT CHAR 1 Flag is indicating automatically deposit the payment when it is processed.
AUTOMATIC_WITHDRAWN CHAR 1 Flag is indicating automatically withdrawn the payment when it is processed.

FIN_Matching_Algorithm table

FIN_Matching_Algorithm
Column Name Type Length Comments
FIN_MATCHING_ALGORITHM_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
NAME VARCHAR 60 A non-unique identifier for a record/document often used as a search tool.
DESCRIPTION VARCHAR 255 A space to write additional related information.
CLASSNAME VARCHAR 1000 x not implemented

FIN_BankFile_Format table

FIN_BankFile_Format
Column Name Type Length Comments
FIN_BANKFILE_FORMAT_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
NAME VARCHAR 60 A non-unique identifier for a record/document often used as a search tool.
DESCRIPTION VARCHAR 255 A space to write additional related information.
CLASSNAME VARCHAR 1000 x not implemented

Aprm_Finacc_Transaction_Acct_V view

Aprm_Finacc_Transaction_Acct_V
Column Name Type Length Comments
FIN_FINACC_TRANSACTION_ACCT_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
C_ACCTSCHEMA_ID VARCHAR 32 The structure used in accounting including costing methods, currencies, and the calendar.
C_ELEMENTVALUE_ID VARCHAR 32 A identification code for an account type.
DATETRX DATE This field is indicates the date of the transaction.
DATEACCT DATE This field is indicates which accounting period within the fiscal year this transaction will be part of.
C_PERIOD_ID VARCHAR 32 This field indicates the exclusive range of dates for a calendar.
AD_TABLE_ID VARCHAR 32 This field indicates the table in which a field or fields reside.
FIN_FINACC_TRANSACTION_ID VARCHAR 32 Foreign key to FIN_FINACC_TRANSACTION indicating the financial transaction used in this transaction.
FIN_FINANCIAL_ACCOUNT_ID VARCHAR 32 Foreign key to FIN_FINANCIAL_ACCOUNT indicating the account from which the payment will be made.
GL_GATEGORY_ID VARCHAR 32 Indicates the category of general ledger.
C_TAX_ID VARCHAR 32 Indicates the type of tax for this document line.
M_LOCATOR_ID VARCHAR 32 Indicates where in a Warehouse a product is located.
POSTINGTYPE VARCHAR 60 Indicates the type of amount (Actual, Encumbrance, Budget) this journal updated.
C_CURRENCY_ID VARCHAR 32 Indicates the currency to be used when processing this document.
AMTSOURCEDR DECIMAL Indicates the credit amount for this line in the source currency.
AMTSOURCECR DECIMAL Indicates the credit amount for this line in the source currency.
AMTACCTDR DECIMAL Indicates the transaction amount converted to this organization's accounting currency
AMTACCTCR DECIMAL Indicates the transaction amount converted to this organization's accounting currency
C_UOM_ID VARCHAR 32 Indicates the unique non monetary unit of measure
QTY DECIMAL The Quantity indicates the number of a specific product or item for this document.
M_PRODUCT_ID VARCHAR 32 Identifies an item which is either purchased or sold in this organization.
C_BPARTNER_ID VARCHAR 32 Foreign key to C_BPARTNER indicating the BPartner it relates to.
AD_ORGTRX_ID VARCHAR 32 Indicates the organization transaction.
C_LOCFROM_ID VARCHAR 32 The Location From indicates the location that a product was moved from.
C_LOCTO_ID VARCHAR 32 This field is indicates the location that a product was moved to.
C_SALESREGION_ID VARCHAR 32 The Sales Region indicates a specific area of sales coverage.
C_PROJECT_ID VARCHAR 32 Identifier of a project defined within the Project & Service Management module.
C_CAMPAIGN_ID VARCHAR 32 The Campaign defines a unique marketing program.
C_ACTIVITY_ID VARCHAR 32 Activities indicate tasks that are performed and used to utilize Activity based Costing
USER1_ID VARCHAR 32 The user defined element displays the optional elements that have been defined for this account combination.
USER2_ID VARCHAR 32 The user defined element displays the optional elements that have been defined for this account combination.
DESCRIPTION VARCHAR 255 Indicates Additional related information.
A_ASSET_ID VARCHAR 32 Indicates An item which is owned and exchangeable for cash.
FACT_ACCT_GROUP_ID VARCHAR 32 Indicates an accounting entry group ID
SEQNO DECIMAL The Sequence indicates the order of records
FACTACCTTYPE CHAR 1 Type of entry (Normal, Closing, Opening, Regularization)
DOCBASETYPE VARCHAR 40 The Document Base Type identifies the base or starting point for a document
ACCTVALUE VARCHAR 40 Indicates the value of the account used in the entry.
ACCTDESCRIPTION VARCHAR 255 Description of the accounting entry
C_WITHHOLDING_ID VARCHAR 32 The Withholding indicates the type of withholding to be calculated.

View Definition: 

 CREATE OR REPLACE VIEW aprm_finacc_transaction_acct_v AS
 SELECT fact_acct.fact_acct_id AS fin_finacc_transaction_acct_id,
   fact_acct.ad_client_id,fact_acct.ad_org_id,fact_acct.isactive,
   fact_acct.created, fact_acct.createdby, fact_acct.updated,
   fact_acct.updatedby, fact_acct.c_acctschema_id, fact_acct.account_id AS   
   c_elementvalue_id, fact_acct.datetrx,fact_acct.dateacct, 
   fact_acct.c_period_id, fact_acct.ad_table_id, fact_acct.record_id AS 
   fin_finacc_transaction_id, fin_finacc_transaction.fin_financial_account_id, 
   fact_acct.gl_category_id, fact_acct.c_tax_id, fact_acct.m_locator_id, 
   fact_acct.postingtype, fact_acct.c_currency_id, fact_acct.amtsourcedr, 
   fact_acct.amtsourcecr, fact_acct.amtacctdr, fact_acct.amtacctcr, 
   fact_acct.c_uom_id, fact_acct.qty, fact_acct.m_product_id, 
   fact_acct.c_bpartner_id, fact_acct.ad_orgtrx_id, fact_acct.c_locfrom_id, 
   fact_acct.c_locto_id, fact_acct.c_salesregion_id, fact_acct.c_project_id, 
   fact_acct.c_campaign_id, fact_acct.c_activity_id, fact_acct.user1_id, 
   fact_acct.user2_id, fact_acct.description, fact_acct.a_asset_id, 
   fact_acct.fact_acct_group_id, fact_acct.seqno, fact_acct.factaccttype, 
   fact_acct.docbasetype, fact_acct.acctvalue, fact_acct.acctdescription, 
   fact_acct.c_withholding_id
 FROM
   fact_acct, fin_finacc_transaction
WHERE 
   fact_acct.record_id::text =      
   fin_finacc_transaction.fin_finacc_transaction_id::text AND 
   fact_acct.ad_table_id::text = '4D8C3B3C31D1410DA046140C9F024D17'::text;

FIN_Bankstatement table

FIN_Bankstatement
Column Name Type Length Comments
FIN_BANKSTATEMENT_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
FIN_FINANCIAL_ACCOUNT_ID VARCHAR 32 Foreign key to FIN_FINANCIAL_ACCOUNT indicating the account from which the payment will be made.
DOCUMENTNO VARCHAR 32 Document number for the payment.
NAME VARCHAR 60 A non-unique identifier for a record/document
NOTES VARCHAR 255 Indicates the additional information.
C_DOCTYPE_ID VARCHAR 32 Foreign key to C_DOCTYPE indicating the document type used for the transaction. .
FILENAME VARCHAR 255 Indicates the appropriate file name for the bank statement.
IMPORTDATE DATE Import date of the bank file
STATEMENTDATE DATE The Statement Date field defines the date of the statement being processed.
FIN_RECONCILIATION_ID VARCHAR 32 Reconciliation events related to the financial account
PROCESSING CHAR 1 Indicating a process is being performed on this record.
PROCESSED CHAR 1 This field is indicates that a document has been processed.

FIN_Bankstatementline table

FIN_Bankstatementline
Column Name Type Length Comments
FIN_BANKSTATEMENTLINE_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
FIN_BANKSTATEMENT_ID VARCHAR 32 Bank statements related to a financial account
LINE DECIMAL Indicates the unique line for a document.
BPARTNERNAME VARCHAR 60 Indicates the busines partner name.
C_BPARTNER_ID VARCHAR 32 Foreign key to C_BPARTNER indicating the BPartner it relates to.
DATETRX DATE The Transaction Date indicates the date of the transaction.
CRAMOUNT DECIMAL Indicates the credited amount.
DRAMOUNT DECIMAL Import the debited amount.
REFERENCENO VARCHAR 50 The number for a specific reference.
MATCHINGTYPE VARCHAR 60 Type of matching for the given line.
FIN_FINACC_TRANSACTION_ID VARCHAR 32 Foreign key to FIN_FINACC_TRANSACTION indicating the financial transaction used in this transaction.
EM_C43_DESCRIPTION VARCHAR 2000 C43 Description.

Aprm_Reconciliation_V view

Aprm_Reconciliation_V
Column Name Type Length Comments
APRM_RECONCILIATION_V_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
FIN_RECONCILIATION_ID VARCHAR 32 Reconciliation events related to the financial account
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
DOCUMENTNO VARCHAR 30 Indicates the document no for the financial account.
C_DOCTYPE_ID VARCHAR 32 The Document Type determines document sequence and processing rules
DATETO DATE The Date To indicates the end date of a range (inclusive)
FIN_FINANCIAL_ACCOUNT_ID VARCHAR 32 Financial account used to deposit / withdrawal money such as bank accounts or petty cash
STATEMENTDATE DATE The Statement Date field defines the date of the statement being processed.
ENDINGBALANCE DECIMAL The Ending Balance is the result of adjusting the Beginning Balance by any payments or disbursements.
STARTINGBALANCE DECIMAL Starting Balance of the financial account.
DOCSTATUS VARCHAR 60 The Document Status indicates the status of a document at this time.
PROCESSING CHAR 1 A request to process the respective document or task.
PROCESSED CHAR 1 The Processed field is indicates that a document has been processed.
POSTED VARCHAR 60 Indicates if the transaction has already been posted to the general ledger or not.
PRINTDETAILED CHAR 1 Indicates user want the detailed print.
PRINTSUMMARY CHAR 1 Print summary of an account.
ITEM_NO INTEGER Item no for the financial account.
ITEM_AMT DECIMAL Item amount for the financial account
UNREC_NO INTEGER Unreconcilied bank statement lines
UNREC_AMT DECIMAL Unreconcilied bank statement lines amount.
PAYMENT_NO INTEGER Indicates the outstanding payments.
PAYMENT_AMT DECIMAL Indicates the outstanding payments amount.
DEPOSIT_NO INTEGER Indicates the outstanding deposits.
DEPOSIT_AMT DECIMAL Indicates the outstanding deposits amount.
FORCED_TABLE_ID VARCHAR 32 Hidden column to set the source table id in views.

View Definition: 

CREATE OR REPLACE VIEW aprm_reconciliation_v AS 
SELECT fin_reconciliation.fin_reconciliation_id AS aprm_reconciliation_v_id, 
   fin_reconciliation.fin_reconciliation_id, fin_reconciliation.ad_client_id, 
   fin_reconciliation.ad_org_id, fin_reconciliation.created, 
   fin_reconciliation.createdby, fin_reconciliation.updated, 
   fin_reconciliation.updatedby, fin_reconciliation.isactive, 
   fin_reconciliation.fin_financial_account_id, fin_reconciliation.documentno, 
   fin_reconciliation.c_doctype_id, fin_reconciliation.dateto, 
   fin_reconciliation.statementdate, fin_reconciliation.endingbalance, 
   fin_reconciliation.startingbalance, fin_reconciliation.docstatus, 
   fin_reconciliation.processing, fin_reconciliation.processed, 
   fin_reconciliation.posted, fin_reconciliation.printdetailed, 
   fin_reconciliation.printsummary, item.item_no, item.item_amt, 
   unrec.unrec_no, unrec.unrec_amt, outstanding.payment_no, 
   outstanding.payment_amt, outstanding.deposit_no, outstanding.deposit_amt, ( 
   SELECT ad_table.ad_table_id
   FROM 
       ad_table
   WHERE 
       lower(ad_table.tablename::text) ~~ 'fin_reconciliation'::text) AS  
       forced_table_id
  FROM 
       fin_reconciliation
  LEFT JOIN ( SELECT fin_finacc_transaction.fin_reconciliation_id, count(*) AS 
       item_no, COALESCE(sum(fin_finacc_transaction.depositamt - 
       fin_finacc_transaction.paymentamt), 0::numeric) AS item_amt
       FROM 
           fin_finacc_transaction
       GROUP BY fin_finacc_transaction.fin_reconciliation_id) item ON 
       fin_reconciliation.fin_reconciliation_id::text = 
       item.fin_reconciliation_id::text
       LEFT JOIN ( SELECT fin_bankstatement.fin_reconciliation_id, count(*) AS 
       unrec_no, COALESCE(sum(fin_bankstatementline.cramount - 
       fin_bankstatementline.dramount), 0::numeric) AS unrec_amt
       FROM 
           fin_bankstatementline, fin_bankstatement
       WHERE 
           fin_bankstatementline.fin_finacc_transaction_id IS NULL AND 
           fin_bankstatementline.fin_bankstatement_id::text = 
           fin_bankstatement.fin_bankstatement_id::text
       GROUP BY fin_bankstatement.fin_reconciliation_id) unrec ON 
       fin_reconciliation.fin_reconciliation_id::text = 
       unrec.fin_reconciliation_id::text
   LEFT JOIN ( SELECT fin_reconciliation.fin_reconciliation_id, COALESCE(sum(
       CASE
           WHEN fin_finacc_transaction.paymentamt IS NOT NULL THEN 1
           ELSE 0
       END), 0::bigint) AS payment_no, 
   COALESCE(sum(fin_finacc_transaction.paymentamt), 0::numeric) AS 
        payment_amt, COALESCE(sum(
       CASE
           WHEN fin_finacc_transaction.depositamt IS NOT NULL THEN 1
           ELSE 0
       END), 0::bigint) AS deposit_no,   
   COALESCE(sum(fin_finacc_transaction.depositamt), 0::numeric) AS deposit_amt
       FROM 
           fin_finacc_transaction, fin_reconciliation
       WHERE 
           fin_finacc_transaction.fin_reconciliation_id IS NULL AND 
           fin_finacc_transaction.statementdate <= fin_reconciliation.dateto
           GROUP BY fin_reconciliation.fin_reconciliation_id) outstanding ON 
           fin_reconciliation.fin_reconciliation_id::text = 
           outstanding.fin_reconciliation_id::text;

FIN_Reconciliationline_V view

FIN_Reconciliationline_V
Column Name Type Length Comments
FIN_RECONCILIATION_V_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
FIN_FINACC_TRANSACTION_ID VARCHAR 32 Foreign key to FIN_FINACC_TRANSACTION indicating the financial transaction used in this transaction.
FIN_PAYMENT_ID VARCHAR 32 Foreign key to FIN_PAYMENT indicating the Payment it relates to.
C_CURRENCY_ID VARCHAR 32 Indicates the currency to be used when processing this document.
FIN_FINANCIAL_ACCOUNT_ID VARCHAR 32 Foreign key to FIN_FINANCIAL_ACCOUNT indicating the account from which the payment will be made.
DATEACCT DATE The date this transaction is recorded for in the general ledger.
C_GLITEM_ID VARCHAR 32 Indicating the G/L item it relates to.
PAYMENTAMT DECIMAL Amount of the transaction to be paid.
DEPOSITAMT DECIMAL Indicates the deposit amount of the transaction.
C_PROJECT_ID VARCHAR 32 Identifier of a project defined within the Project & Service Management module.
C_CAMPAIGN_ID VARCHAR 32 The Campaign defines a unique marketing program.
C_ACTIVITY_ID VARCHAR 32 Activities indicate tasks that are performed and used to utilize Activity based Costing
USER1_ID VARCHAR 32 The user defined element displays the optional elements that have been defined for this account combination.
USER2_ID VARCHAR 32 The user defined element displays the optional elements that have been defined for this account combination.
TRXTYPE VARCHAR 60 The Transaction Type indicates the type of transaction to be submitted to the Credit Card Company.
STATEMENTDATE DATE The Statement Date field defines the date of the statement being processed.
DESCRIPTION VARCHAR 255 Indicates Additional related information.
FIN_RECONCILIATION_ID VARCHAR 32 Reconciliation events related to the financial account
FIN_BANKSTATEMENTLINE_ID VARCHAR 32 Line related to the bank statement.

View Definition: 

CREATE OR REPLACE VIEW fin_reconciliationline_v AS 
SELECT fin_finacc_transaction.fin_finacc_transaction_id AS 
   fin_reconciliationline_v_id, 
   fin_finacc_transaction.fin_finacc_transaction_id, 
   fin_finacc_transaction.ad_client_id, fin_finacc_transaction.ad_org_id, 
   fin_finacc_transaction.created, fin_finacc_transaction.createdby, 
   fin_finacc_transaction.updated, fin_finacc_transaction.updatedby, 
   fin_finacc_transaction.isactive, fin_finacc_transaction.fin_payment_id, 
   fin_finacc_transaction.c_currency_id, 
   fin_finacc_transaction.fin_financial_account_id, 
   fin_finacc_transaction.dateacct, fin_finacc_transaction.c_glitem_id, 
   fin_finacc_transaction.paymentamt, fin_finacc_transaction.depositamt,    
   fin_finacc_transaction.c_project_id, fin_finacc_transaction.c_campaign_id, 
   fin_finacc_transaction.c_activity_id, fin_finacc_transaction.user1_id, 
   fin_finacc_transaction.user2_id, fin_finacc_transaction.trxtype, 
   fin_finacc_transaction.statementdate, fin_finacc_transaction.description, 
   fin_finacc_transaction.fin_reconciliation_id, 
   fin_bankstatementline.fin_bankstatementline_id
FROM 
   fin_finacc_transaction
LEFT JOIN 
   fin_bankstatementline ON 
   fin_finacc_transaction.fin_finacc_transaction_id::text =  
   fin_bankstatementline.fin_finacc_transaction_id::text;

FIN_Payment_Sched_Inv_V view

FIN_PAYMENT_SCHED_INV_V
Column Name Type Length Comments
FIN_PAYMENT_SCHED_INV_V_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
C_INVOICE_ID VARCHAR 32 Foreign key to C_INVOICE indicating the invoice it relates to.
C_ORDER_ID VARCHAR 32 Foreign key to C_ORDER indicating the order it relates to.
DUEDATE DATE The Due Date of the oldest scheduled payment.
FIN_PAYMENTMETHOD_ID VARCHAR 40 Payment method used to clear the debt.
EXPECTED DECIMAL The gross amount of the scheduled payment.
RECEIVED DECIMAL Total amount received against the scheduled payment.
OUTSTANDING DECIMAL The gross amount outstanding against the scheduled payment.
LASTPAYMENT DATE Date of the last payment event related with the payment schedule event.
NUMBEROFPAYMENTS DECIMAL Number of paymets related with the payment schedule event.
C_CURRENCY_ID VARCHAR 32 Foreign key to C_CURRENCY indicating the currency used for the transaction.

View Definition: 

 CREATE OR REPLACE VIEW fin_payment_sched_inv_v AS 
SELECT ps.fin_payment_schedule_id AS fin_payment_sched_inv_v_id, 
     ps.ad_client_id, ps.ad_org_id, ps.isactive, ps.created, ps.createdby, 
     ps.updated, ps.updatedby, ps.c_invoice_id, ps.c_order_id, ps.duedate, 
     ps.fin_paymentmethod_id, ps.amount AS expected, ps.paidamt AS received, 
     ps.outstandingamt AS outstanding, ps.c_currency_id, 
     ( SELECT 
            max(p.paymentdate) AS max
       FROM fin_payment p
            LEFT JOIN fin_payment_detail pd ON pd.fin_payment_id::text = 
            p.fin_payment_id::text
            LEFT JOIN fin_payment_scheduledetail psd ON 
            pd.fin_payment_detail_id::text = psd.fin_payment_detail_id::text
       WHERE ps.c_invoice_id IS NOT NULL AND 
            psd.fin_payment_schedule_invoice::text = 
            ps.fin_payment_schedule_id::text) AS lastpayment, 
     ( SELECT count(*) AS count
       FROM fin_payment_detail pd, fin_payment_scheduledetail psd
       WHERE pd.fin_payment_detail_id::text = psd.fin_payment_detail_id::text 
            AND ps.c_invoice_id IS NOT NULL AND 
            psd.fin_payment_schedule_invoice::text = 
            ps.fin_payment_schedule_id::text) AS numberofpayments
FROM fin_payment_schedule ps;

FIN_Payment_Sched_Ord_V view

FIN_PAYMENT_SCHED_ORD_V
Column Name Type Length Comments
FIN_PAYMENT_SCHED_ORD_V_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
C_INVOICE_ID VARCHAR 32 Foreign key to C_INVOICE indicating the invoice it relates to.
C_ORDER_ID VARCHAR 32 Foreign key to C_ORDER indicating the order it relates to.
DUEDATE DATE The Due Date of the oldest scheduled payment.
FIN_PAYMENTMETHOD_ID VARCHAR 40 Payment method used to clear the debt.
EXPECTED DECIMAL The gross amount of the scheduled payment.
RECEIVED DECIMAL Total amount received against the scheduled payment.
OUTSTANDING DECIMAL The gross amount outstanding against the scheduled payment.
LASTPAYMENT DATE Date of the last payment event related with the payment schedule event.
NUMBEROFPAYMENTS DECIMAL Number of paymets related with the payment schedule event.
C_CURRENCY_ID VARCHAR 32 Foreign key to C_CURRENCY indicating the currency used for the transaction.

View Definition: 

 CREATE OR REPLACE VIEW fin_payment_sched_ord_v AS 
SELECT ps.fin_payment_schedule_id AS fin_payment_sched_ord_v_id, 
    ps.ad_client_id, ps.ad_org_id, ps.isactive, ps.created, ps.createdby, 
    ps.updated, ps.updatedby, ps.c_invoice_id, ps.c_order_id, ps.duedate, 
    ps.fin_paymentmethod_id, ps.amount AS expected, ps.paidamt AS received, 
    ps.outstandingamt AS outstanding, ps.c_currency_id, 
    ( SELECT 
          max(p.paymentdate) AS max
      FROM fin_payment p
          LEFT JOIN fin_payment_detail pd ON pd.fin_payment_id::text =
          p.fin_payment_id::text
          LEFT JOIN fin_payment_scheduledetail psd ON     
          pd.fin_payment_detail_id::text = psd.fin_payment_detail_id::text
      WHERE ps.c_order_id IS NOT NULL AND 
          psd.fin_payment_schedule_order::text = 
          ps.fin_payment_schedule_id::text) AS lastpayment, 
    ( SELECT count(*) 
          AS count FROM fin_payment_detail pd, fin_payment_scheduledetail psd
      WHERE pd.fin_payment_detail_id::text = 
          psd.fin_payment_detail_id::text AND ps.c_order_id IS NOT NULL AND 
          psd.fin_payment_schedule_order::text = 
          ps.fin_payment_schedule_id::text) AS numberofpayments
  FROM fin_payment_schedule ps;

FIN_Payment_Detail_V view

FIN_PAYMENT_SCHEDULE_V
Column Name Type Length Comments
FIN_PAYMENT_DETAIL_V_ID VARCHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID VARCHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID VARCHAR 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 VARCHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY VARCHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
FIN_PAYMENT_SCHED_INV_V_ID VARCHAR 32 Indicating the payment scheduled invoice used for the transaction.
FIN_PAYMENT_SCHED_ORD_V_ID VARCHAR 32 Indicating the payment scheduled order used for the transaction.
INVOICENO VARCHAR 30 Indicating the invoice number of the transaction.
ORDERNO VARCHAR 30 Indicating the order number of the transaction.
PAYMENTNO VARCHAR 30 Indicating the payment number of the transaction.
FIN_PAYMENT_ID VARCHAR 32 Foreign key to FIN_PAYMENT indicating the user that last updated this record.
DUEDATE DATE Date upon the payment is due.
INVOICEDAMT NUMERIC Indicating the invoiced amount of the transaction.
EXPECTED NUMERIC The gross amount of the scheduled payment.
PAIDAMT NUMERIC Amount that has already been paid.
C_BPARTNER_ID VARCHAR 32 Foreign key to C_BPARTNER indicating the BPartner it relates to.
PAYMENTDATE DATE Date of payment event.
FIN_PAYMENTMETHOD_ID VARCHAR 40 Payment method used to clear the debt.
FIN_FINANCIAL_ACCOUNT_ID VARCHAR 32 Foreign key to FIN_FINANCIAL_ACCOUNT indicating the account from which the payment will be made.
C_CURRENCY_ID VARCHAR 32 Foreign key to C_CURRENCY indicating the currency used in this transaction.

View Definition: 

 CREATE OR REPLACE VIEW fin_payment_detail_v AS SELECT psd.fin_payment_scheduledetail_id AS fin_payment_detail_v_id,psd.ad_client_id,
 psd.ad_org_id, psd.isactive, psd.created, psd.createdby, psd.updated, 
 psd.updatedby, psi.fin_payment_sched_inv_v_id, 
 pso.fin_payment_sched_ord_v_id, i.documentno AS invoiceno, o.documentno AS  
 orderno, p.documentno AS paymentno, pd.fin_payment_id, COALESCE(psi.duedate, 
 pso.duedate) AS duedate, COALESCE(i.grandtotal, o.grandtotal) AS invoicedamt, 
 COALESCE(psi.expected, pso.expected) AS expected, psd.amount AS paidamt, 
 p.c_bpartner_id,p.fin_paymentmethod_id, p.fin_financial_account_id, 
 p.c_currency_id, p.paymentdate
FROM fin_payment p, fin_payment_detail pd, fin_payment_scheduledetail psd
LEFT JOIN fin_payment_sched_inv_v psi ON  
psd.fin_payment_schedule_invoice::text = psi.fin_payment_sched_inv_v_id::text
  LEFT JOIN c_invoice i ON psi.c_invoice_id::text = i.c_invoice_id::text
  LEFT JOIN fin_payment_sched_ord_v pso ON psd.fin_payment_schedule_order::text = pso.fin_payment_sched_ord_v_id::text
  LEFT JOIN c_order o ON pso.c_order_id::text = o.c_order_id::text
 WHERE p.fin_payment_id::text = pd.fin_payment_id::text AND pd.fin_payment_detail_id::text = psd.fin_payment_detail_id::text;

link title

Retrieved from "https://wiki.infinite-erp.co.id/index.php?title=Projects/AdvPaymentMngt/Technical_Documentation&oldid=2737"