Figure 1 - Organisational Structure for Accounts Payable: Business Entities and Suppliers
15th March 2019
Amended: 30th March 2024
Accounts payable is an accounting entry that represents obligations to pay off short-term debts owed to creditors or suppliers. These must be paid off within a given period to avoid default. For example, at the corporate level, accounts payable may refer to commercial invoices submitted by suppliers from whom the business has purchased goods or services. The payable is essentially a short-term "I owe you" (IOU) from the business entity to another organisation or person.
To record accounts payable, the business entity credits the accounts payable liability account when the commercial invoice is received, and debits one or more expense or asset accounts depending upon the nature and usage of the goods or services which were supplied. For example, purchases of depreciable assets, inventory or revenue-generating services provided by subcontractors would typically debit asset accounts, whereas rentals, or purchases of consumables or services for maintenance, general or administrative purposes, would typically debit expense accounts.
Subsequently, when the invoice is paid, the business entity debits accounts payable and credits the cash account. Payments may be made in many different forms including cash, cheques, electronic funds transfers and credit or debit card transactions. Those payments may be made on the business entity's premises, delivered directly to suppliers by mail or courier, or may be handled by third parties such as agents or banks acting on the entity's behalf. When making payments, the business entity may also need to issue a remittance advice or withholding tax certificate.
Additional business processes include tracking accounts payable to determine if they are overdue, and responding to supplier queries. A business entity's finance department would use an ageing report to track and list unpaid supplier invoices and the length of time each invoice has remained unpaid, usually in increments of 30, 60 and 90 days, or more. An ageing report is a way to determine if a business entity is paying off its obligations to suppliers and reducing accounts payable efficiently.
The PARTY, ORGANISATION and PERSON tables contain entries for every organisation or person which is required to be known to the GM-X application.
At least one of these parties is an organisation which is the "client" and it is the one for which the application is being run. This organisation is known as the functional unit and can be identified by adding it to the PARTY and ORGANISATION tables using a single party_id. This data provides the following:
It is also possible for a GM-X installation to handle the business for a group of organisations which do business under separate names, possibly using different functional currencies. Those organisations can be added as functional units to the PARTY and ORGANISATION tables. Each of them is identified in those tables by its own party_id.
The GM-X application can maintain separate general ledgers for these organisations. In this case each of those organisations must be added as a business entity to the FUNCTIONAL_UNIT table using its party_id, in addition to the PARTY and ORGANISATION tables.
When managing accounts payable, each of those functional units has relationships with external parties which are defined on the AP_PARTY_RELATIONSHIP table for various role types including:
When the GM-X installation handles business for a group of related functional units, it is possible for any of those functional units to be suppliers or agents of the others, and these intercompany relationships may also be defined on the AP_PARTY_RELATIONSHIP and PARTY_RELATIONSHIP tables. In these situations, accounts payable owed by related parties are usually carried separately on each functional unit's accounts in order to segregate the cumulative effects of business transactions attributable to internal parties from those of external parties.
This is an important consideration when drawing up consolidated financial statements, because intercompany business transactions must offset one another during the consolidation process. For example, an amount owed to one functional unit (the creditor) by a related functional unit (the debtor) would be treated as an asset by the creditor and an offsetting liability by the debtor which eliminates the amount from the group's consolidated balance sheet. Similarly, revenues derived by one functional unit from supplying goods and services to a related functional unit would be treated as revenues by the supplier and as offsetting expenses by the customer which eliminate the revenues from the group's consolidated income statement. The accounts reserved exclusively for intercompany transactions are known as "elimination accounts" and may be identified as such in the GM-X General Ledger subsystem.
The parties in internal as well as external trading relationships may utilise the same or different functional currencies. When the functional currencies are different, both parties must agree to use a single currency for invoicing and payment purposes whenever goods and services are sold. When the agreed currency is also the functional currency of one party, the opposite party in the relationship bears the entire foreign exchange risk; but when the agreed currency is neither party's functional currency, both parties bear the risk. Unless those risks are hedged through forward future contract instruments, each risk-bearing party must periodically revalue its accounts payable which are not denominated in its functional currency to account for the effects of exchange rate fluctuations, and the revaluation gains or losses must be accumulated in a book foreign exchange gains or losses account. When the customer makes a payment, cumulative revaluation gains or losses attributable to the payment (if any) must be reversed from the book gains or losses account, and the entire realised gain or loss attributable to the payment must be written off to crystallised foreign exchange gains or losses in order to fully clear accounts payable.
In addition to the trading relationships described above, personnel who carry out accounts payable processes for or on behalf of each functional unit, either as employees or as contractors to whom the work is outsourced, have relationships with the functional units which may be defined on the PARTY_RELATIONSHIP table for various role types and subtypes such as data enterer, payment processor, approver, controller and chief financial officer.
Figure 1 - Organisational Structure for Accounts Payable: Business Entities and Suppliers
This extends the existing PARTY_RELATIONSHIP table of the PARTY subsystem.
New tasks will be available to users of the FINANCE-AP subsystem to create, enquire, update and delete AP_PARTY_RELATIONSHIP records for suppliers. These will perform the same functions as the existing tasks available to users of the PARTY subsystem to create, enquire, update and delete PARTY_RELATIONSHIP records, and in addition will allow the extra fields to be maintained. The new tasks, together with the existing PARTY subsystem tasks for creating, enquiring, updating and deleting PERSON and ORGANISATION records, will be functionally equivalent to the vendor master data management functions of traditional ERP systems. This serves the purpose of allowing master data maintenance tasks performed on supplier relationships to be completed in a single step, rather than two.
Although the order in which two parties are specified in a PARTY_RELATIONSHIP does not matter, in this instance the functional unit (customer) will always be party_id_1 and the supplier will always be party_id_2. Values to be used in role_type_id_1, role_subtype_id_1, role_type_id_2 and role_subtype_id_2 will be defined within the application.
| Field | Type | Description |
|---|---|---|
| party_id_1 | number | This identifies an organisation which is a functional unit for which a balanced set of books is kept in the GL_ACCOUNT_PERIOD_BALANCE table. Links to an entry on the FUNCTIONAL_UNIT table. |
| party_id_2 | number | This identifies the party (person or organisation) which is a supplier. Links to an entry on the PARTY table. |
| seq_no | number | Unique number generated by the system. This starts at 1 for each customer/supplier relationship. It is possible for a supplier to have different sets of details for different circumstances, so this acts as the unique identifier. |
| relationship_name | string | Optional. If more than one relationship exists for a combination of party_id_1 and party_id_2 (with a different value for seq_no) this optional description can be used to differentiate one relationship with another. |
| identity_type_id | string | The value for this column is set to "ALTERNATIVE". Together with party_id_2 this links to an entry on the PARTY_IDENTIFICATION table in order to provide, in the id_value column, the supplier account number assigned by the functional unit which is entirely different from the internally generated party_id. This will allow the record on the PARTY_IDENTIFICATION table to be accessible via the FINANCE-AP screens without having to use the separate task in the PARTY subsystem. |
| payment_days_average | number | Optional. This is automatically calculated and updated by the system whenever payments from party_id_1 are posted to party_id_2. |
| party_id_trading_organisation | number | Optional. This identifies the purchasing organisation responsible for managing the relationship with party_id_2. Links to an entry on the PARTY table. This entry would ordinarily be needed only when party_id_1 has two or more purchasing organisations which manage relationships with different groups of suppliers. |
| party_id_bursar | number | Optional. This identifies the financial institution or agency, if any, responsible for disbursing funds to party_id_2 on behalf of party_id_1. Links to an entry on the PARTY table. |
| is_remittance_advice_issuer | boolean | A YES/NO switch with an initial value of NO. It indicates if the party_id_bursar is responsible for preparing and issuing remittance advices or withholding tax certificate documents to party_id_2, or not. |
| task_id_remittance_advice | string | Optional. The identity of the OUTPUT3 task which prepares the remittance advice document in PDF format. |
| task_id_withholding_cert | string | Optional. The identity of the OUTPUT3 task which prepares the withholding tax certificate document (e.g. 1099 form in the United States, Thailand Withholding Tax Certificate), either as a file or in PDF format. |
| financial_account_id_remit_from | number | This is the identity of the bank account, credit card account, payment service provider account, crypto-currency wallet or cash vault which is the default source of funds for paying party_id_2. Links to an entry on the FINANCIAL_ACCOUNT table.
The value for party_id_owner on the linked FINANCIAL_ACCOUNT entry must be the same as party_id_1 (i.e. the account owner must the functional unit in this relationship). If the payment_method is 6 (Credit card), 7 (Debit card), 8 (Payment service provider) or 9 (Mobile wallet) then allow_card must be YES on the linked FINANCIAL_ACCOUNT entry. If the payment_method is 4 (Direct deposit/ACH) or 5 (Wire transfer) then allow_eft must be YES on the linked FINANCIAL_ACCOUNT entry. |
| financial_account_id_remit_to | number | Optional. This is the identity of the bank account, credit account, payment service provider account or crypto-currency wallet to which funds are remitted by default when party_id_2 is paid. Links to an entry on the FINANCIAL_ACCOUNT table.
The value for party_id_owner on the linked FINANCIAL_ACCOUNT entry must be the same as party_id_2 (i.e. the account owner must the supplier in this relationship). Required if the payment_method is 4 (Direct deposit/ACH), 5 (Wire transfer) or 10 (Crypto-currency). Must be blank if the payment_method is 11 (Cash). |
| payment_method | number | This is the default method for paying party_id_2 which can be one of the following:
|
| fee_responsibility | number | This indicates who is responsible for paying any fees or commissions charged by the financial intermediary for payments from party_id_1 to party_id_2, which can be one of the following:
|
| payment_file_format | number | Optional. This indicates the file format specification to be used when the system prepares files for uploading payment instructions to a financial institution, which can be one of the following:
This entry is valid only when the payment_method is 4 (Direct deposit/ACH) or 5 (Wire transfer); otherwise it must be blank. |
| is_stp | boolean | A YES/NO switch with an initial value of NO. This indicates if straight-through processing (STP) is required for funds transfers. Its effect is to tighten the validation rules for financial account information to support STP.
An entry of YES is valid only when the payment_method is 4 (Direct deposit/ACH) or 5 (Wire transfer); otherwise it must be NO. |
| je_template_id_invoice | number | Optional. This is the subsystem journal entry template number to be used as the account distribution template for new customer invoices issued by party_id_1 to party_id_2. Links to an entry on the SUBSYSTEM_JE_TEMPLATE_HEADER table. |
| je_template_id_credit_note | number | Optional. This is the identity of the account distribution template to be used for new customer credit notes issued by party_id_1 to party_id_2. Links to an entry on the SUBSYSTEM_JE_TEMPLATE_HEADER table. |
| je_template_id_debit_note | number | Optional. This is the identity of the account distribution template to be used for new customer debit notes issued by party_id_1 to party_id_2. Links to an entry on the SUBSYSTEM_JE_TEMPLATE_HEADER table. |
| je_template_id_payment | number | Optional. This is the identity of the account distribution template to be used for payments received by party_id_1 to party_id_2. Links to an entry on the SUBSYSTEM_JE_TEMPLATE_HEADER table. |
| party_id_tax_authority | number | Optional. This identifies the tax authority, an organisation which collects sales, use, value-added tax (VAT) and/or withholding taxes on transactions between party_id_1 and party_id_2. Links to an entry on the PARTY table.
If multiple tax authorities have the power to impose such taxes on the same transactions, they must be separately identified as the party_id_transacting on two or more of the SUBSYSTEM_JE_TEMPLATE_ITEM records belonging to the following:
|
| is_tax_due_on_accrual | boolean | A YES/NO switch with an initial value of NO. It indicates if the sales, use and/or value-added tax (VAT) becomes due and payable to the party_id_tax_authority upon issuance of the commercial invoice, credit note or debit note, or not. If not, such taxes (if any) become due and payable upon payment or settlement of the commercial invoice, credit note or debit note. |
| is_withholding_tax_applicable | boolean | A YES/NO switch with an initial value of NO. It indicates if party_id_1 is required to withhold corporate or personal income tax from payments to party_id_2, or not. |
Traditional ERP systems were designed years ago when the overwhelming majority of businesses paid their debts using paper cheques. Payment processing consists of selecting invoices, credit notes and debit notes to be settled during a particular cheque run, modifying the selection or payment amounts as required, and printing the cheques.
With respect to cheque printing, the main focus of these systems was to provide and overprint information on pre-printed voucher cheque forms. In addition to a payment amount using words as well as numeric format, this information includes the payment date, payee (beneficiary) name and a listing of the invoices settled by the payment identifying each invoice, credit note or debit note settled as well as its date, amount settled and voucher number. The listing is overprinted on the voucher (remittance advice) section of the form. Perforations make the cheque and voucher sections easy to separate. There was no need for the ERP system to provide the payee's bank account details because these would be provided by the payee when presenting the cheque. Neither did any need exist to provide the remitter's bank account details because these were already pre-printed on the cheque forms.
With the rise of electronic banking, the number of organisations making business-to-business (B2B) payments by cheque has shrunk more than 40% since 2004. Although many organisations still need to print paper cheques the traditional way, they increasingly need to initiate payment transactions such as:
For e-Cheques, the ERP system must be capable of providing the payee name (taken from the GM-X Party Name of the payee) together with the remitter's bank account details as they would be shown on a pre-printed cheque:
For EFT and telegraphic transfer payments, the ERP system must be capable of providing the payee's bank account details because those transactions will credit the payee's account directly and automatically. Those details may include:
Alternatively, for payments by credit card, debit card and mobile wallet, and for some payments initiated with payment service providers payments, the ERP system must be capable of providing a different set of financial account details for the remitter. These are the data required from the remitter to initiate one-time or repetitive payments and typically include:
Lastly, for crypto-currency payments, the ERP system must be capable of providing wallet addresses for the remitter and payee.
For EFT or telegraphic transfer payments, and many payments initiated with payment service providers, the ERP system must also be capable of generating files for bulk upload of payment details to the bank or agent. Although no globally universal standard file format exists, the most prominent emerging standard is the SWIFT MT101 message type.
Figure 2 - Organisational Structure for Accounts Payable: Financial Intermediaries
This is a new FINANCE-AP subsystem table which provides details for individual bank accounts, credit card accounts, payment service provider accounts and crypto-currency wallets.
| Field | Type | Description |
|---|---|---|
| financial_account_id | number | Identity number which is generated automatically by the system. |
| financial_account_type | number | This indicates the type of financial account which can be one of the following:
|
| financial_account_desc | string | The description of this financial account. |
| currency_code | string | This is the currency in which this financial account is denominated. Links to an entry on the CURRENCY_CODE table. |
| party_id_owner | number | This identifies the party who owns this financial account (for current/checking, savings and crypto-currency accounts), or who is liable for repaying the credit issuer (credit accounts). Links to an entry on the PARTY table. |
| party_id_custodian | number | This identifies the party who maintains or services this financial account, such as a bank, payment service provider or other financial institution. Links to an entry on the PARTY table.
When the financial_account_type is 5 (Cash vault) then the party_id_custodian must be the same as the party_id_owner. |
| allow_card | boolean | A YES/NO switch with an initial value of NO. It indicates if this financial account allows the owner to use a credit or debit card when initiating payments which debit the account, or not. |
| allow_eft | boolean | A YES/NO switch with an initial value of NO. It indicates if this financial account allows the owner to initiate electronic funds transfers (direct deposit/ACH payment method) which debit the account, or not. |
| requires_intermediary | boolean | A YES/NO switch with an initial value of NO. It indicates if this financial account receives credits by way of an intermediary bank, or not. |
| clearing_type | string | Optional. This identifies the clearing system used for the financial account, if any, which can be one of the following:
Refer Note 1(a) and Note 1(b), below, for additional validation details. |
| routing_number | number | Optional. This is the bank routing number or sort code for the financial account, if any. Required when allow_eft is YES and the swift_code is blank and the party_id_owner links to an entry on the FUNCTIONAL_UNIT table.
Refer Note 2, below, for additional validation rules. |
| swift_code | string | Optional. This is the SWIFT code for the financial account, if any.
If an entry is made, it must have exactly 8 or 11 alphanumeric characters. The first 6 characters may contain only the uppercase Latin letters 'A' through 'Z', inclusive. The remaining 2 or 5 characters may contain only the uppercase Latin letters 'A' through 'Z', inclusive; or the numeric digits '0' through '9', inclusive. |
| account_number | string | Optional. This is the account identity, such as a bank account number or International Bank Account Number (IBAN), assigned by the party_id_custodian.
If an entry is made, its length may not exceed 34 alphanumeric characters. If the financial_account_type is 4 (Crypto-currency), the entry may contain only the uppercase Latin letters 'A' through 'Z', inclusive; lowercase Latin letters 'a' through 'z', inclusive; or the numeric digits '0' through '9', inclusive. |
| clearing_type_intermediary | string | Optional. This identifies the clearing system used for the intermediary financial institution, if any. The option list is the same as for the clearing_type entry.
This entry is required when requires_intermediary is YES and allow_eft is YES and the swift_code_intermediary is blank and the party_id_owner links to an entry on the FUNCTIONAL_UNIT table. If the clearing_type entry is not blank and is not 'LO' or 'IS' then then clearing_type_intermediary cannot be the same as clearing_type. Refer Note 1(b), below, for additional validation details. |
| routing_number_intermediary | number | Optional. This is the bank routing number or sort code for the intermediary financial institution, if any. Required when requires_intermediary is YES and allow_eft is YES and the swift_code_intermediary is blank and the party_id_owner links to an entry on the FUNCTIONAL_UNIT table.
Refer Note 2, below, for additional validation rules. |
| swift_code_intermediary | string | Optional. This is the SWIFT code for the intermediary financial institution, if any.
If an entry is made, it must have exactly 8 or 11 alphanumeric characters. The first 6 characters may contain only the uppercase Latin letters 'A' through 'Z', inclusive. The remaining 2 or 5 characters may contain only the uppercase Latin letters 'A' through 'Z', inclusive; or the numeric digits '0' through '9', inclusive. |
| cardholder_name_enc | string | Optional. This is the holder's name embossed on the credit or debit card. Required when allow_card is YES.
The value in the database is encrypted for additional security. |
| payment_card_number_enc | string | Optional. This is the payment card number or Payment Account Number (PAN) embossed on the credit or debit card. Required when allow_card is YES.
If an entry is made, it must have exactly 16 of the numeric digits '0' through '9', inclusive. The value in the database is encrypted for additional security. |
| expiry_date_enc | string | Optional. This is the expiry date of the credit of debit card. Required when allow_card is YES.
The value in the database is encrypted for additional security. |
| verification_value_enc | string | Optional. This is the Card Verification Value (CVV) printed on the front or the back of the credit or debit card. Required when allow_card is YES.
If an entry is made, it must have 3 or 4 of the numeric digits '0' through '9', inclusive. The value in the database is encrypted for additional security. |
| contact_mech_id_billing | number | Optional. This identifies the postal address which is the billing address for the credit or debit card. Links to an entry on the CONTACT_MECHANISM table which must also exist on the PARTY_CONTACT_MECHANISM table for party_id_owner.
Always blank when allow_card is NO. |
| chart_id | number | Optional. This identifies the chart of accounts. Links to an entry on the GL_ACCOUNT table.
An entry is allowed only if the party_id_owner links to an entry on the FUNCTIONAL_UNIT table, and in this case its value must be the same as the chart_id on that FUNCTIONAL_UNIT entry. |
| account_id | string | Optional. This identifies the general ledger asset or liability account to be credited by payments made from this financial account. Links to an entry on the GL_ACCOUNT table.
An entry is allowed only if the party_id_owner links to an entry on the FUNCTIONAL_UNIT table. If the financial_account_type is 3 (Credit) then the account_type of the linked GL_ACCOUNT entry must be 'LI' (liability); otherwise the account_type of the linked GL_ACCOUNT entry must be 'AS' (asset). |
Note 1 - validation of clearing_type
1(a) This entry is required when allow_eft is YES and the swift_code is blank and the party_id_owner links to an entry on the FUNCTIONAL_UNIT table.
1(b) In addition, the following entries are not valid when the currency_code is:
Note 2 - validation of routing_number
This entry always consists of the numeric digits '0' through '9', inclusive, with left zero-fill; however the allowable number of digits vary as shown below:
Although supplier invoices, credit notes and debit notes are held in the same physical tables as customer invoices, credit notes and debit notes, they may refer to certain master data which is different for supplier invoicing and accounts payable processes that it is for customer invoicing and accounts receivable processes:
To accommodate these differences, the GM-X Accounts Payable subsystem contains three master data tables which are used exclusively for supplier invoices, credit notes and debit notes.
An organisation may require extra fields to be held for each supplier invoice, credit note or debit note. There is a NAMES table which holds the names of any such extra fields, with their validation types, and a VALUES table which holds a corresponding value for each invoice, credit note or debit note.
Although these values can be held in the database they cannot be considered when processing any business rules as their names and meanings are not part of the standard software. They can, however, be dealt with in any custom plugins.
This identifies the names of extra fields which can be held for each supplier invoice, supplier credit note or supplier debit note.
| Field | Type | Description |
|---|---|---|
| extra_id | string | Identity |
| extra_name | string | Short Name |
| extra_desc | string | Optional. Long Description |
| extra_type | string | Validation type. Allowable values are:
|
This identifies the names of extra fields which can be held for each item within a supplier invoice, supplier credit note or supplier debit note.
| Field | Type | Description |
|---|---|---|
| extra_id | string | Identity |
| extra_name | string | Short Name |
| extra_desc | string | Optional. Long Description |
| extra_type | string | Validation type. Allowable values are:
|
As well as the bill_to and bill_from details which are held on the INVOICE_HEADER, there may be a need to identify other parties who may be involved with a supplier invoice, credit note or debit note.
This identifies the types of role with which parties can be related to an invoice.
| Field | Type | Description |
|---|---|---|
| invoice_role_type_id | string | Identity |
| invoice_role_type_desc | string | Description |
Here are some examples:
A supplier invoice, credit note or debit note will be in different states at different points in time, and instead of just recording the current state it may be more useful to maintain a history of each change in state. This table identifies all the possible status values while the SUPPLIER_INVOICE_STATUS_HIST table maintains a history of all changes.
| Field | Type | Description |
|---|---|---|
| invoice_status_type_id | string | Identity |
| invoice_status_type_name | string | Short name |
| invoice_status_type_desc | string | Optional. Long description |
| sort_seq | numeric | Used for sorting the entries in the dropdown list. |
Here are the values used by the system which cannot be altered:
| Id | Description | Sequence |
|---|---|---|
| PEND | Pending. | 1 |
| COMP | Complete. | 2 |
| REL | Released for Approval. | 3 |
| APRI | Approved for immediate payment (or next payment cycle). | 4 |
| APRL | Approved for payment at a later time. Cannot be selected for payment until changed to 'APRI'. | 5 |
| REJ | Rejected. | 7 |
| HOLD | On Hold. | 8 |
| PAID | Settled in full (through any combination of payments, supplier credit notes and/or supplier debit notes). | 9 |
This identifies all the possible states through which a payment may go.
| Field | Type | Description |
|---|---|---|
| payment_status_type_id | string | Identity |
| payment_status_type_name | string | Short name. |
| payment_status_type_desc | string | Long description. |
| sort_seq | number | Used for sorting the entries in the dropdown list. |
Here are the values used by the system which cannot be altered:
| Id | Description | Sequence |
|---|---|---|
| PEND | Pending, not yet complete. The payment amount does not match the document balances. | 1 |
| COMP | Complete. The payment amount matches the document balances (see below). The balances are ready to be updated. | 2 |
| REL | Released for approval. | 4 |
| APPR | Approved for payment | 5 |
| PREP | Payment raised. | 6 |
| SIGN | Payment signed. | 7 |
| DISP | Payment dispatched. A payment must have this status before it can be posted to the General Ledger. | 8 |
| WD | Financial account debited. The funds have been withdrawn from this account and submitted to the vendor.
This is also the point at which the settled/applied amount for each invoice component in a payment is subtracted from the component's balance_tx and balance columns to give the outstanding balance. If the component is settled in full the outstanding balance will be reduced to zero. |
9 |
| REC | Payment reconciled.
When payment_method is blank the payment status automatically advances from 'APPR' (Approved) to 'REC' (Reconciled) thus stepping over the 'WD' (Financial Account Debited) status as there is no transfer of funds. At this point the settled/applied amount for each invoice component must be subtracted from the component's balance_tx and balance columns to give the outstanding balance. |
11 |
| VOID | Void. The effects of this settlement have been reversed and the document balances reset to what they were. | 12 |
| REJ | Rejected (payment not approved). | 13 |
The document balance is calculated as (payment_value_out_tx - payment_value_in_tx) on the PAYMENT_HEADER and must match the sum of all the settlement_value_tx values on all associated PAYMENT_ITEM_SETTLED entries. Note that this identifies the total amount being settled which will be different from the amount being paid if any credit notes are being applied, as indicated by the sum of all the applied_value_tx values on all associated PAYMENT_ITEM_SETTLED entries.
The amount being paid to the vendor is therefore calculated as (sum(settlement_value_tx) - sum(applied_value_tx)). Note that one of these two values will be negative, so both are made positive before one is subtracted from the other so that the payment amount can always be expressed as a positive number.
The following status changes are allowed:
NOTE: If the payment is being created as part of a PAYMENT_BATCH, which is signified by a non-zero value in payment_batch_id, then the payment status is automatically advanced from COMP to APPR without the need for any manual approval regardless of any setting in IS_PAYMENT_ALERT in the Accounts Payable Control data. This is because payment batches have a separate approval process which covers all payments within the batch.
This identifies all the possible states through which a payment batch may go.
| Field | Type | Description |
|---|---|---|
| payment_batch_status_type_id | string | Identity |
| payment_batch_status_type_name | string | Short name. |
| payment_batch_status_type_desc | string | Long description |
| sort_seq | numeric | Used for sorting the entries in the dropdown list. |
Here are the values used by the system which cannot be altered:
| Id | Description | Sequence |
|---|---|---|
| PEND | Pending release. | 1 |
| APPR | Approved for document preparation. | 4 |
| UNIF | Unissued form numbers entered. | 6 |
| PREP | All documents prepared. | 5 |
| REJ | Rejected (not approved). | 3 |
| REL | Released for approval. | 2 |
Supplier invoices are usually created directly from purchase orders. A function is available to do this automatically so that the order details do not have to be transposed manually. It is possible to amend the resulting invoice, such as to add in any delivery charges, and it is also possible to create a supplier invoice, credit note or debit note from scratch, with or without a purchase order reference.
After any of these documents has been completed and approved it can be posted to the General Ledger using the logic described in Posting invoices to the General Ledger.
Figure 3 - Invoices, Invoice Items and Item Features
This is an existing table in the INVOICE database which holds information about the invoice, credit note or debit note as a whole. The link to a related purchase order is optional.
An invoice can either be created manually or generated from an existing purchase order which will copy across all the items and adjustment details exactly. The rules for amending invoice details, and for creating invoices from scratch, are exactly the same as for creating and amending purchase orders.
A credit note or debit note can either be created manually or generated from an existing invoice. The tax date of the credit note or debit note should be the same as the date of the Invoice to which it applies so that any values which are time sensitive can be taken from the correct time period. For example, if the tax rate changes after the invoice is created but before the credit note is produced it will be necessary to specify the invoice date on the credit note so that the rate of tax which is applied to the credit note is the same rate of tax which was applied to the invoice.
An alert can optionally be created for each supplier invoice, credit note or debit note in the ALERT database. This will allow electronic copies of invoice, credit note, debit note and supporting documents submitted by suppliers (for e.g. PDF files submitted by suppliers by email, or scan copies of paper documents mailed by suppliers) to be attached to an INVOICE_ACCOUNT_HEADER entry using an ALERT entry and, optionally, one or more ALERT_DETAIL entries that are linked to the INVOICE_ACCOUNT_HEADER entry. It will also allow approvals of the invoice, credit note or debit note to be delegated to specific personnel and route any document attachments to those personnel for review, using the GM-X Workflow subsystem.
If YES, the IS_INVOICE_ALERT parameter in Accounts Payable Control Data will cause an alert to be automatically created whenever a new INVOICE_HEADER entry having an invoice_type of 'V' (supplier invoice), 'U' (supplier credit note) or 'R' (supplier debit note) is inserted into the database. It is also possible to create the alert manually via a pre-selectable navigation button on the List Vendor Invoices, List Vendor Credit Notes and List Vendor Debit Notes screens.
The status of a supplier invoice, credit note or debit note can only be promoted from 'Pending' to 'Complete' when all of these conditions are satisfied.
NOTE: This table resides in the INVOICE database, but has the following differences in meaning:
| Field | Type | Description |
|---|---|---|
| invoice_type | string | This identifies the document type, which can be one of the following:
Each of these document types will have their own numbering sequence. |
| invoice_id_external | string | Optional. This is the identity of the invoice assigned by the party_id_bill_from.
Note: although this field is added to the INVOICE_HEADER table, it is used only within the Accounts Payable subsystem (invoice_types 'V', 'U' and 'R'), and is always null for all other invoice_types. |
| party_id_bill_from | numeric | Links to an entry on the PARTY table. This identifies the party to whom this invoice should be paid. If order_id is not empty it will be taken from the party_id for that order. |
| party_id_bill_to | numeric | Links to an entry on the PARTY table. This identifies the party who is due to pay this invoice. If order_id is not empty it will be taken from the party_id_internal for that order, otherwise the Business Entity chosen by the user will be used instead. |
| The following fields are used to deal with transactions which are carried out in a currency which is different from that of the functional unit. Please see FAQ24 for details. | ||
| currency_code_tx | string | Display only. The transaction (TX) currency. Links to an entry on the CURRENCY_CODE table. Obtained from the PARTY table for party_id_bill_from when the invoice is created. |
| currency_code_fn | string | Display only. The functional (FN) currency. Links to an entry on the CURRENCY_CODE table. Obtained from the PARTY table for party_id_bill_to when the invoice is created. |
The Accounts Payable subsystem uses the existing INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE tables in the INVOICE database to store further details for each supplier invoice, credit note or debit note. Each of these tables has an invoice_type which links to an entry on the INVOICE_HEADER table, and will contain the values 'V', 'U' and 'R' denoting supplier invoice, supplier credit note or supplier debit note respectively.
This shares the table with the same name in the INVOICE subsystem.
This shares the table with the same name in the INVOICE subsystem.
As well as the price of individual products an invoice may contain a variety of discounts or surcharges which affect its total value. Some of these adjustments may apply to individual items, such as quantity breaks, while others apply to the whole invoice, such as sales tax.
Figure 4 - Invoice Adjustments
This shares the table with the same name in the INVOICE subsystem.
This shares the table with the same name in the INVOICE subsystem.
This database may be used by other systems, such as a web front end, which may have a requirement for extra fields to be held for each invoice. There is a NAMES table which holds the names of any such extra fields, with their validation types, and a VALUES table which holds a corresponding value for each invoice.
Although these values can be held in the database they cannot be considered when processing any business rules as their names and meanings are not part of the standard software. They can, however, be dealt with in any custom plugins.
Figure 5 - Invoice Extra Values
This holds the values for any extra fields for each invoice.
| Field | Type | Description |
|---|---|---|
| invoice_type | string | Links to an entry on the INVOICE_HEADER table. |
| invoice_id | numeric | Links to an entry on the INVOICE_HEADER table. |
| extra_id | string | Links to an entry on the SUPPLIER_INVOICE_EXTRA_NAMES table. |
| extra_value | string | This holds the value for this Extra Name with this Invoice. |
This database may be used by other systems, such as a web front end, which may have a requirement for extra fields to be held for each invoice item. There is a NAMES table which holds the names of any such extra fields, with their validation types, and a VALUES table which holds a corresponding value for each invoice item.
Although these values can be held in the database they cannot be considered when processing any business rules as their names and meanings are not part of the standard software. They can, however, be dealt with in any custom plugins.
Figure 6 - Invoice Item Extra Values
This holds the values for any extra fields for each invoice item.
| Field | Type | Description |
|---|---|---|
| invoice_type | string | Links to an entry on the INVOICE_ITEM table. |
| invoice_id | numeric | Links to an entry on the INVOICE_ITEM table. |
| invoice_item_seq_no | numeric | Links to an entry on the INVOICE_ITEM table. |
| extra_id | string | Links to an entry on the SUPPLIER_INVOICE_ITEM_EXTRA_NAMES table. |
| extra_value | string | This holds the value for this Extra Name with this Invoice Item. |
An invoice will be in different states at different points in time, and instead of just recording the current state it may be more useful to maintain a history of each change in state.
Figure 7 - Invoice Status History
This keeps a history of all changes in status for an invoice. The entry with the highest sequence number is the latest.
| Field | Type | Description |
|---|---|---|
| invoice_type | string | Links to an entry on the INVOICE_HEADER table. |
| invoice_id | numeric | Links to an entry on the INVOICE_HEADER table. |
| seq_no | numeric | Sequence number which is generated by the system. |
| invoice_status_type_id | string | Links to an entry on the SUPPLIER_INVOICE_STATUS_TYPE table. |
| status_date | date | The date on which this entry was created. |
As well as the bill_to and bill_from details which are held on the INVOICE_HEADER, there may be a need to identify other parties who may be involved with a supplier invoice, supplier credit note or supplier debit note.
Figure 8 - Invoice Roles
This identifies the roles that various parties have with an invoice.
| Field | Type | Description |
|---|---|---|
| invoice_type | string | Links to an entry on the INVOICE_HEADER table. |
| invoice_id | numeric | Links to an entry on the INVOICE_HEADER table. |
| invoice_role_type_id | string | Links to an entry on the SUPPLIER_INVOICE_ROLE_TYPE table. |
| party_id | numeric | Links to an entry on the PARTY table. |
| percent_contribution | numeric | Optional. The percentage contribution made by this party. This could be useful, for example, if the commission on an order needs to be shared among several people. |
The parties involved in an invoice may agree on many arrangements or terms, such as payment terms. Each order may have one or more terms which are categorised by TERM_TYPE. Any entries which have been added to the PARTY_TERMS table for this vendor will automatically be added to this table.
Figure 9 - Invoice Terms
Supplier invoices are usually created directly from purchase orders. A function is available to do this automatically so that the order details do not have to be transposed manually. It is possible to amend the resulting invoice, such as to add in any delivery charges, and it is also possible to create a supplier invoice, credit note or debit note from scratch, with or without a purchase order reference.
As data for each vendor invoice, credit note and debit note is being input the corresponding data on the INVOICE_ACCOUNT_HEADER and INVOICE_ACCOUNT_ITEM tables will also be updated.
Figure 10 - Account Distribution
This is a new FINANCE-AP subsystem table which holds information for each supplier invoice, supplier credit note or supplier debit note which is common to all entries in the INVOICE_ACCOUNT_ITEM table. The system automatically creates exactly one entry on this table for each supplier invoice, supplier credit note or supplier debit note entry in the INVOICE_HEADER table.
| Field | Type | Description |
|---|---|---|
| invoice_type | string | This identifies the document type. Links to an entry on the INVOICE_HEADER table. |
| invoice_id | numeric | This identifies the document number. Links to an entry on the INVOICE_HEADER table. |
| fiscal_calendar_id | numeric | This identifies the fiscal calendar. Links to an entry on the FISCAL_CALENDAR_PERIOD table. Automatically determined by the system based on the party_id_bill_to on the related INVOICE_HEADER entry. |
| fiscal_year | numeric | This identifies the Gregorian year when the end of the fiscal year occurs. Automatically calculated by the system based on the posting_date.
Links to an entry on the FISCAL_CALENDAR_PERIOD table. |
| fiscal_period | numeric | This identifies the fiscal period. Automatically calculated by the system based on the posting_date.
Links to an entry on the FISCAL_CALENDAR_PERIOD table. |
| posting_date | date | This is the date which determines the fiscal period to which this journal entry was (or will be) posted. The default value is the invoice_date on the related INVOICE_HEADER entry. |
| journal_desc | string | Long description of the journal entry created from this supplier invoice, debit note or credit note. This is also the default description for each of that journal entry's items.
The initial value is pre-populated from the journal_desc field of any entry on the SUBSYSTEM_JE_TEMPLATE_HEADER table which is linked to one of the following fields on the entry on the AP_PARTY_RELATIONSHIP table that is linked to the party_id_bill_from and party_id_bill_to on the related INVOICE_HEADER entry:
If no such entry exists on the SUBSYSTEM_JE_TEMPLATE_HEADER table then the initial value is pre-populated from Accounts Payable Control Data, journal_desc_invoice value. |
| uom_id_statistic | string | Optional. The unit of measure for the journal line item statistic created from this supplier invoice, debit note or credit note, if any. Links to an entry on the UNIT_OF_MEASURE table.
This is the default statistics unit of measure for each of that journal entry's items. The initial value is pre-populated from the uom_id_statistic field of any entry on the SUBSYSTEM_JE_TEMPLATE_HEADER table which is linked one of the following fields on the entry on the AP_PARTY_RELATIONSHIP table that is linked to the party_id_bill_from and party_id_bill_to on the related INVOICE_HEADER entry:
|
This is a new FINANCE-AP subsystem table which holds the general ledger account distribution for each INVOICE_HEADER entry representing a supplier invoice, supplier credit note or supplier debit note. Additional entries may exist in the INVOICE_ACCOUNT_ITEM table for specific INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE entries which may exist for an INVOICE_HEADER entry.
| Field | Type | Description |
|---|---|---|
| invoice_type | string | This identifies the document type. Links to an entry on the INVOICE_ACCOUNT_HEADER table. |
| invoice_id | numeric | This identifies the document number. Links to an entry on the INVOICE_ACCOUNT_HEADER table. |
| invoice_account_item_seq_no | numeric | This uniquely identifies the INVOICE_ACCOUNT_ITEM entry for a particular entry on the INVOICE_ACCOUNT_HEADER table. Item numbers start at 1 for the first item entered, and are incremented by 1 for each subsequent item. |
| journal_item_desc | string | Defaults to the description on the associated invoice item. |
| chart_id | numeric | This identifies the chart of accounts. Links to an entry on the GL_ACCOUNT table. Read-only.
The initial value may be pre-populated from the chart_id field of any entry on one of the entries, if any, on the SUBSYSTEM_JE_TEMPLATE_ITEM table which is linked, by way of the SUBSYSTEM_JE_TEMPLATE_HEADER table, to one of the following fields on the entry on the AP_PARTY_RELATIONSHIP table that is linked to the party_id_bill_from and party_id_bill_to on the related INVOICE_HEADER entry:
|
| account_id | string | This identifies the general ledger account. Links to an entry on the GL_ACCOUNT table.
The initial value may be pre-populated from the chart_id field of one of the entries, if any, on the SUBSYSTEM_JE_TEMPLATE_ITEM table which is linked, by way of the SUBSYSTEM_JE_TEMPLATE_HEADER table, to one of the following fields on the entry on the AP_PARTY_RELATIONSHIP table that is linked to the party_id_bill_from and party_id_bill_to on the related INVOICE_HEADER entry:
|
| debit_functional | numeric | Optional. The debit amount in the currency_code_fn of the related INVOICE_HEADER entry. Never negative. Must be blank if credit_functional is greater than zero. |
| credit_functional | numeric | Optional. The debit amount in the currency_code_fn of the related INVOICE_HEADER entry. Never negative. Must be blank if debit_functional is greater than zero. |
| debit_tx | numeric | Optional. The debit amount in the currency_code_tx of the related INVOICE_HEADER entry. Never negative. Must be blank if credit_tx is greater than zero. |
| credit_tx | numeric | Optional. The debit amount in the currency_code_tx of the related INVOICE_HEADER entry. Never negative. Must be blank if debit_tx is greater than zero. |
| statistic | numeric | Optional. The statistic amount in the uom_id_statistic. |
| uom_id_statistic | string | Optional. The unit of measure for the statistic, if any. Links to an entry on the UNIT_OF_MEASURE table. If blank then the uom_id_statistic from the related INVOICE_ACCOUNT_HEADER entry is used instead.
The initial value may be pre-populated from the uom_id_statistic field on one of the entries, if any, on the SUBSYSTEM_JE_TEMPLATE_ITEM table which is linked, by way of the SUBSYSTEM_JE_TEMPLATE_HEADER table, to one of the following fields on the entry on the AP_PARTY_RELATIONSHIP table that is linked to the party_id_bill_from and party_id_bill_to on the related INVOICE_HEADER entry:
|
| invoice_adjustment_seq_no | numeric | Optional. Links to an entry on the INVOICE_ADJUSTMENT table. |
| invoice_item_seq_no | numeric | Optional. Links to an entry on the INVOICE_ITEM table. |
| invoice_item_prod_feature_id | numeric | Optional. Links to an entry on the INVOICE_ITEM_FEATURE table. |
| invoice_item_adjustment_seq_no | numeric | Optional. Links to an entry on the INVOICE_ITEM_ADJUSTMENT table. |
| facility_id | numeric | Optional. The facility associated with this INVOICE_ACCOUNT_ITEM. Links to an entry on the FACILITY table. Required for the purpose of including the statistic value (for e.g., quantity of electrical energy consumption) in the estimate of Scope 2 greenhouse gas (GHG) emissions attributable to that facility. |
Each document, whether it be a supplier invoice, supplier credit note or supplier debit note, will have one or more components, each of which is represented as an entry on one of the following database tables:
Note that the INVOICE_ADJUSTMENT table has different rules for tax and non-tax entries.
When an entry on any of these four database tables is added or updated this action will require one or more entries to be written to the INVOICE_ACCOUNT_ITEM table. Every input will require an output for the account_id which has been associated with the usage 'trade_payables' as a first side entry, plus one or more additional usages which can either be provided by:
If only one additional usage is identified then the values used in the first side entry can be reversed and used in this single contra side entry. If more than one additional usage is identified then, unless some logic is defined to calculate its values, the record written out to the INVOICE_ACCOUNT_ITEM table will contain empty values.
Once the INVOICE_ACCOUNT_ITEM entries have been assembled for one of the five supplier invoice components some post-processing logic will be executed to see if some empty values can be filled in.
The identity of the records to be read is held on the AP_PARTY_RELATIONSHIP table for the party_id_bill_from and relationship_seq_no values of the INVOICE_HEADER and depends on the invoice_type of the input record being processed.
Note that even though a value for je_template_id may be provided, there may or may not be any eligible records on the SUBSYSTEM_JE_TEMPLATE_ITEM table.
Each valid record read from the SUBSYSTEM_JE_TEMPLATE_ITEM table will result in a record being written to the INVOICE_ACCOUNT_ITEM table for each document component.
All the available records must be read and processed one at a time. Note that any record with an account_usage which does not appear in Segment 1 in the following table is to be ignored.
| Segment | Option | First Side Column Usage | Contra Column Usage |
|---|---|---|---|
| 1 | a. Unaccrued Direct Cost,
Revenue and Work in Progress |
trade_payables | cogs
direct_labour staff_direct_reimb staff_direct_mileage staff_direct_air staff_direct_hotel staff_direct_meal_own staff_direct_entertain staff_direct_other direct_cost_other staff_indirect_reimb |
| sales_use
sales_supply sales_service exp_recovery |
wip | ||
| b. Inventory Purchases | trade_payables | accrued expense
ppv |
|
| c. Unaccrued Expense and
Acquisition of Other Assets |
trade_payables | vendor_deposit
ffe commission_expense commissions_fees pension_expense staff_indirect_mileage staff_indirect_air staff_indirect_hotel staff_indirect_meal_own staff_indirect_entertain staff_indirect_other interest indirect_expense_other |
|
| d. Accrued Expense and
Accrued Work in Process |
trade_payables | accrued_expense | |
| 2 | Sales Tax | trade_payables | sales_tax_payable
- OR - sales_tax_liability (see Note 3 below) |
Note 3: The account to use depends on the value of the is_tax_due_on_accrual field in the AP_PARTY_RELATIONSHIP table. If the value of this field is YES then a 'sales_tax_payable' account is used; otherwise a 'sales_tax_liability' account is used.
Note that some records may contain a non-blank value for account_id and either a blank or non-blank value for account_usage, in which case the record will be output with that account_id, with empty values, and the account_usage must not be checked for being a duplicate.
As each record with an account_usage (with or without an account_id) is processed the account_usage should be stored in a temporary variable so that any additional records with the same account_usage can be recognised as duplicates and ignored (with the exception noted in the preceding paragraph).
After all the available SUBSYSTEM_JE_TEMPLATE_ITEM records have been processed for a document component it is necessary to examine the contents of the temporary variable to see if any records for particular account_usage values are missing, in which case the system must create them.
While some of these records may initially be created with empty values the processing described in Post Processing INVOICE_ACCOUNT_ITEM records may provide some values.
This procedure is used to convert an account_usage into an account_id. This value may be provided on any one of a number of records which must be searched in the following sequence:
Note that some of these tables can only be read if the input record from the INVOICE subsystem contains the primary key for that table.
As soon as the first non-blank value for account_id is found then the account determination process will be terminated. If at the end there is no value for account_id then the value for account_usage will be used instead. This record will have to be corrected by the user manually in order to balance the debits and credits for the document before it can be marked as 'complete'.
Note that the PRICE_COMPONENT table can only be used for percentage rates (such as tax rates) and sale prices, it cannot be used to provide purchase prices as they are always entered manually.
The "first side" refers to the side (debit side, or credit side) of an INVOICE_ACCOUNT_ITEM entry which identifies the trade payables liability account affected by the document component. The proposed credit_tx or debit_tx value comes from:
Monetary amounts on the INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE entries for a supplier invoice or supplier debit note are positive numbers for prices and surcharges, or negative numbers for discounts. This means:
Monetary amounts on the INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE entries for a supplier credit note are negative numbers for prices and surcharges, or positive numbers for discounts. This means:
Relative to the first side entry for a document component, the "contra side" (or "opposite side") refers to the side of its mandatory and opposite entry. Each contra side entry must affect a different account than its corresponding first side entry. Therefore:
In some situations (for e.g. when calculating a purchase price variance), the first side entry may require two or more entries on the contra side. In such situations one of the contra side entries is mandatory and the other contra side entries are optional. If the absolute value of the mandatory contra side entry is unequal to the absolute value of its corresponding first side entry, then at least one of the optional entries is required and those entries can occupy either the first side or contra side. In that case the sum of the debits and credits for all contra side entries must equal the absolute value of the corresponding first side entry, before the INVOICE_ACCOUNT_ITEM entries for a document can be converted into a journal entry.
For sales taxes, or other taxes such as duties or use taxes, an INVOICE_ADJUSTMENT linked to an ORDER_ADJUSTMENT_TYPE having an order_adjustment_type_id that begins with 'TAX' is required. In this situation a Segment 2 must be constructed from each such INVOICE_ADJUSTMENT, but only for input value-added tax (VAT) that is recoverable from the tax authority having jurisdiction over the supply of goods or services included on the supplier invoice, credit note or debit note. It also excludes those entries where both adjustment and adjustment_tx are zero.
Note that this does NOT read the contents of the SUBSYSTEM_JE_TEMPLATE_ITEM table.
This includes all INVOICE_ADJUSTMENT entries except those linked to an ORDER_ADJUSTMENT_TYPE entry having an order_adjustment_type_id that begins with 'TAX', and those linked to a PRICE_COMPONENT entry having a price_type of 'C' (Commission), 'E' (Early Payment Discount) or 'W' (Withholding Tax). It also excludes any entry where product_id_free is non-blank as that product will have its own entry in the INVOICE_ITEM table, and those entries where both adjustment and adjustment_tx are zero.
Note that unless specific logic is defined which identifies what amount can be associated with what account_usage the output record must contain blank values. The user must then update this data manually in order to make the entries balance.
After traversing the SUBSYSTEM_JE_TEMPLATE_ITEM table perform the following checks by referencing the temporary variable:
If NO then determine the account_id for this usage and output it with empty values.
After these checks have been performed then execute the logic described in Post Processing INVOICE_ACCOUNT_ITEM records.
This includes all INVOICE_ITEM entries.
Note that the product_id on each record may have multiple entries in the PROD_CAT_CLASS table that may have either primary_flag, second_flag or third_flag set to 'Y'. These are to be examined in that order with only the first value found having an entry on the FUNC_UNIT_PROD_CAT table and a nonblank value for the relevant general ledger account to be processed further.
Note that unless specific logic is defined which identifies what amount can be associated with what account_usage the output record must contain blank values. The user must then update this data manually in order to make the entries balance.
After traversing the SUBSYSTEM_JE_TEMPLATE_ITEM table perform the following checks by referencing the temporary variable:
If NO then determine the account_id for that usage and output a record with empty values.
After these checks have been performed and any values have been calculated for 'accrued_expense' and 'ppv' then execute the logic described in Post Processing INVOICE_ACCOUNT_ITEM records.
This includes all INVOICE_ITEM entries except those linked to a PRICE_COMPONENT entry having a price_type of 'C' (Commission), 'E' (Early Payment Discount) or 'W' (Withholding Tax). It also excludes any entry where product_id_free is non-blank as that product will have its own entry in the INVOICE_ITEM table.
Note that the product_id on the associated INVOICE_ITEM record may have multiple entries in the PROD_CAT_CLASS table that may have either primary_flag, second_flag or third_flag set to 'Y'. These are to be examined in that order with only the first value found to be processed further.
Note that unless specific logic is defined which identifies what amount can be associated with what account_usage the output record must contain blank values. The user must then update this data manually in order to make the entries balance.
After traversing the SUBSYSTEM_JE_TEMPLATE_ITEM table perform the following checks by referencing the temporary variable:
If NO then determine the account_id for that usage and output a record with empty values.
After these checks have been performed and any value has been calculated for 'ppv' then execute the logic described in Post Processing INVOICE_ACCOUNT_ITEM records.
This will exclude any entry where both feature_price and feature_price_tx are zero.
Note that the product_id on the associated INVOICE_ITEM record may have multiple entries in the PROD_CAT_CLASS table that may have either primary_flag, second_flag or third_flag set to 'Y'. These are to be examined in that order with only the first value found to be processed further.
Note that unless specific logic is defined which identifies what amount can be associated with what account_usage the output record must contain blank values. The user must then update this data manually in order to make the entries balance.
After traversing the SUBSYSTEM_JE_TEMPLATE_ITEM table perform the following checks by referencing the temporary variable:
If NO then determine the account_id for that usage and output a record with empty values.
After these checks have been performed and any value has been calculated for 'ppv' then execute the logic described in Post Processing INVOICE_ACCOUNT_ITEM records.
This is to be performed after any other logic for each invoice component.
Traverse the INVOICE_ACCOUNT_ITEM records for the document component. Exclude:
Examine each of the remaining records one at a time and:
Vendor invoices, credit notes and debit notes are posted to the General Ledger using the logic described below in Constructing Journal Entries for Vendor Invoices. Invoices may be posted either before or after they have been paid or settled.
If the currency_code_tx in an INVOICE_HEADER is different from the currency_code_fn then all those values which were input in transaction currency would have been converted to functional currency using the exchange rate which was in operation when that document was created. However, before the document balance_tx is cleared it is possible for the exchange rate to change, which means that the balance in functional currency will be different between the two dates. In such a case the document balance must be revalued and the difference posted to the general ledger as either a gain or a loss. For full details please refer to Invoice Revaluations. Note also that a final revaluation can occur when a payment is posted to the General Ledger.
The fap_invoice_header(upd4)post and fap_invoice_header(batch)post tasks will create JOURNAL_ENTRY_HEADER from the INVOICE_ACCOUNT_HEADER, and JOURNAL_ENTRY_ITEM entries from the INVOICE_ACCOUNT_ITEM entries related to each selected or qualified INVOICE_HEADER entry, as described in the is_batch_post Property section.
An invoice cannot be posted to the General Ledger unless the INVOICE_HEADER entry has the following values:
Note that an invoice, credit note or debit note can be posted to the General Ledger either before or after the corresponding payment is posted. Neither one is dependent on the other.
After the invoice has been posted the is_posted column is set to 'Y' so that it cannot be posted again.
The following table shows the field mappings for constructing JOURNAL_ENTRY_HEADER entries.
| JOURNAL_ENTRY_HEADER | Document Component or Constant |
|---|---|
| party_id_functional | Refer Note 1, below |
| fiscal_calendar_id | fiscal_calendar_id |
| fiscal_year | fiscal_year |
| fiscal_period | fiscal_period |
| journal_number | Refer Note 2, below |
| posting_date | posting_date. Refer Note 3, below |
| transaction_date | transaction_date. Refer Note 3, below |
| journal_desc | journal_desc |
| uom_id_statistic | uom_id_statistic |
| recurring_je_number | NULL |
| processing_seq | NULL |
| repeat_count | NULL |
| je_status_type_id | 'PEND' |
| is_posted | 'N' |
| subsys_id | 'FINANCE_AP' |
| je_template_id | Refer Note 4, below |
The following table shows the field mappings for constructing JOURNAL_ENTRY_ITEM entries.
| JOURNAL_ENTRY_ITEM | Document Component or Constant |
|---|---|
| party_id_functional | Refer Note 1, above |
| fiscal_calendar_id | fiscal_calendar_id |
| fiscal_year | fiscal_year |
| fiscal_period | fiscal_period |
| journal_number | journal_number. Refer Note 2, above |
| journal_line_item | invoice_account_item |
| journal_item_desc | journal_item_desc |
| party_id_transacting | Refer Note 5, below |
| document_reference | Refer Note 6, below |
| chart_id | chart_id |
| account_id | account_id |
| debit_functional | debit_functional. Refer Note 7, below |
| credit_functional | credit_functional. Refer Note 7, below |
| statistic | statistic. Refer Note 7, below |
| uom_id_statistic | uom_id_statistic |
| invoice_type | invoice_type |
| invoice_id | invoice_id |
| invoice_adjustment_seq_no | invoice_adjustment_seq_no |
| invoice_item_seq_no | invoice_item_seq_no |
| invoice_item_prod_feature_id | invoice_item_prod_feature_id |
| invoice_item_adjustment_seq_no | invoice_item_adjustment_seq_no |
| order_type | order_type. Refer Note 8, below |
| order_id | order_id. Refer Note 8, below |
| <all other fields>
(except created_date and created_user) |
NULL |
After each group of INVOICE_ACCOUNT_ITEM records has been written to the database for one of the invoice components it will be necessary to perform some additional checks which, if passed, will allow the status of the invoice/credit note/debit note to be promoted from 'Pending' to 'Complete'. These checks are:
If all these checks pass then the status of the supplier invoice document can be promoted from 'Pending' to 'Complete'. If any check fails then the invoice status must be demoted back to 'Pending'. An invoice can only be selected for approval once it has reached the 'Complete' status. After it has been approved no further amendments will be permitted.
Thereafter, the INVOICE_HEADER and its related INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT, INVOICE_ITEM_FEATURE, INVOICE_ACCOUNT_HEADER and INVOICE_ACCOUNT_ITEM cannot be deleted, no new INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT, INVOICE_ITEM_FEATURE or INVOICE_ACCOUNT_ITEM entries can be created, and no changes may be made to any fields within those tables which are otherwise editable by users when the INVOICE_HEADER is in 'Pending' status. The only way to change the values in these fields is to demote the document's status from 'Rejected' to 'Pending'.
Although the ultimate aim is to provide entries on the INVOICE_ACCOUNT_ITEM table where the sum of the debit amounts matches the sum of the credit amounts, it may not be possible for the software to produce an accurate distribution with the information available, so all it can do is make a series of suggestions which the user may have to modify manually in order to achieve the desired result. It is only after the sum of the debits and credits are matched that the status of the invoice can be marked as 'complete' which will then allow, after an approval process, for the values to be posted into the General Ledger.
Delegation to specific personnel for approval using the GM-X Workflow subsystem will occur when the document's status is successfully promoted from 'Complete' to 'Released for Approval'. If the adjusted_value of the INVOICE_HEADER is greater than or equal to the INVOICE_MIN parameter in Accounts Payable Control Data, after the adjusted_value is converted from the currency_code_tx of the INVOICE_HEADER to the CURRENCY_CODE_MIN parameter in Accounts Payable Control Data at the current spot rate of exchange, and the IS_INVOICE_ALERT parameter in Accounts Payable Control Data is TRUE, then an approval workflow process will be triggered. Otherwise, the document's status will automatically be promoted from 'Released for Approval' to 'Approved for immediate payment'.
These checks are performed after all other processing for each INVOICE_ITEM component. It is not necessary to check INVOICE_ITEM_ADJUSTMENT or INVOICE_ITEM_FEATURE components separately as they will be automatically included in the INVOICE_ITEM data.
If any test fails then a warning message must be output and all further tests can be skipped. In addition if the current invoice_status_type_id is 'Complete' it must be reset to 'Pending'. This cannot be an error message otherwise it would cause all the database changes to be rolled back.
There are several quite complex database lookups which are needed in several places, so they are defined here so that they can be referenced by name.
For inventory purchases, deliveries of goods must have been accepted and received into inventory, but not yet successfully invoiced, before any supplier invoice for those goods can be promoted from 'Pending' to 'Complete'. This rule applies when the INVOICE_QTY_VAR_MIN tolerance parameter in Accounts Payable Control data is neither zero nor blank. A supplier invoice is deemed to be an invoice for an inventory purchase when at least one purchase order item sent by the bill-to party exists for that invoice, and at least one inventory receipt exists for that purchase order item which was owned by the bill-to party (see Note 4). In this case the invoice can only be promoted from 'Pending' to 'Complete' when, for each of its INVOICE_ITEM entries, the product_id exists on one or more of the purchase order items for that invoice, and the quantity is less than or equal to the quantity of that product_id already received but not previously invoiced, adjusted upward by the INVOICE_QTY_VAR_MIN tolerance parameter; otherwise the system will throw an error message such as 'Unable to match all invoiced quantities to inventory receipts'.
Note 4 - If a supplier invoice is submitted which references a purchase order that was sent by the bill-to party, but no inventory receipts exist for any of that purchase order's items, it is not necessarily an invoice for an inventory purchase so the system will not treat it as such. In this case it will be possible for users to promote the invoice from 'Pending' to 'Complete' even though all invoice quantities exceed quantities received.
For a given invoice_id, an SQL query such as the following will identify an invoice for an inventory purchase when quantity_received is greater than zero:
SELECT COALESCE(SUM(item_receipt.quantity_received), 0) AS quantity_received
FROM invoice_header
RIGHT JOIN item_receipt ON (item_receipt.order_type = invoice_header.order_type
AND item_receipt.order_id = invoice_header.order_id
AND item_receipt.party_id = invoice_header.party_id_bill_to)
LEFT JOIN order_item ON (order_item.order_type = item_receipt.order_type
AND order_item.order_id = item_receipt.order_id
AND order_item.order_item_seq_no = item_receipt.order_item_seq_no
AND order_item.product_id = item_receipt.product_id)
LEFT JOIN order_header ON (order_header.order_type = order_item.order_type
AND order_header.order_id = order_item.order_id
AND order_header.party_id_internal = invoice_header.party_id_bill_to)
WHERE invoice_header.invoice_type = 'V'
AND invoice_header.invoice_id = '[invoice_id]'
AND invoice_header.order_type = 'P'
AND (order_header.order_status_type_id = 'SUP2' OR order_header.order_status_type_id = 'SUP3')
Because quantity values are always positive, to determine the quantity previously invoiced for a particular product_id and purchase order, the quantities found on INVOICE_ITEM entries for the supplier invoices and any debit notes related to that purchase order must be combined, and then reduced by the the sum of quantities found on any credit notes related to the purchase order. All supplier invoices, debit notes and credit notes must be included in this calculation except those having the status 'Pending' or 'Rejected'.
For a given product_id, purchase order_id and party_id_bill_to, an SQL query such as the following will return the quantity for an INVOICE_ITEM which is already received, after INVOICE_QTY_VAR_MIN tolerance parameter adjustment, but not previously invoiced:
SELECT
(
(COALESCE(SUM(item_receipt.quantity_received), 0) * ([INVOICE_QTY_VAR_MIN] / 100))
-
(
(SELECT COALESCE(SUM(d1.quantity), 0)
FROM invoice_item AS d1
LEFT JOIN invoice_header AS d2 ON (d2.invoice_type = d1.invoice_type
AND d2.invoice_id = d1.invoice_id)
RIGHT JOIN item_receipt AS d3 ON (d3.order_type = d2.order_type
AND d3.order_id = d2.order_id
AND d3.party_id = d2.party_id_bill_to
AND d3.product_id = d1.product_id)
LEFT JOIN order_item AS d4 ON (d4.order_type = d3.order_type
AND d4.order_id = d3.order_id
AND d4.order_item_seq_no = d3.order_item_seq_no
AND d4.product_id = d3.product_id)
LEFT JOIN order_header AS d5 ON (d5.order_type = d4.order_type
AND d5.order_id = d4.order_id)
WHERE (d2.invoice_type = 'V' OR d2.invoice_type = 'R')
AND d4.product_id = '[product_id]'
AND d5.order_type = 'P'
AND d5.order_id = '[order_id]'
AND (d5.order_status_type_id = 'SUP2' OR d5.order_status_type_id = 'SUP3')
AND d2.invoice_status_type_id <> 'PEND'
AND d2.invoice_status_type_id <> 'REJ'
)
-
(SELECT COALESCE(SUM(c1.quantity), 0)
FROM invoice_item AS c1
LEFT JOIN invoice_header AS c2 ON (c2.invoice_type = c1.invoice_type
AND c2.invoice_id = c1.invoice_id)
RIGHT JOIN item_receipt AS c3 ON (c3.order_type = c2.order_type
AND c3.order_id = c2.order_id
AND c3.party_id = c2.party_id_bill_to
AND c3.product_id = c1.product_id)
LEFT JOIN order_item AS c4 ON (c4.order_type = c3.order_type
AND c4.order_id = c3.order_id
AND c4.order_item_seq_no = c3.order_item_seq_no
AND c4.product_id = c3.product_id)
LEFT JOIN order_header AS c5 ON (c5.order_type = c4.order_type
AND c5.order_id = c4.order_id)
WHERE (c2.invoice_type = 'U')
AND c4.product_id = '[product_id]'
AND c5.order_type = 'P'
AND c5.order_id = '[order_id]'
AND (c5.order_status_type_id = 'SUP2' OR c5.order_status_type_id = 'SUP3')
AND c2.invoice_status_type_id <> 'PEND'
AND c2.invoice_status_type_id <> 'REJ'
)
)
) AS quantity_not_invoiced
FROM item_receipt
LEFT JOIN order_item ON (order_item.order_type = item_receipt.order_type
AND order_item.order_id = item_receipt.order_id
AND order_item.order_item_seq_no = item_receipt.order_item_seq_no
AND order_item.product_id = item_receipt.product_id)
LEFT JOIN order_header ON (order_header.order_type = order_item.order_type
AND order_header.order_id = order_item.order_id)
WHERE item_receipt.product_id = '[product_id]'
AND item_receipt.order_type = 'P'
AND order_item.order_id = '[order_id]'
AND order_header.party_id_internal = '[party_id_bill_to]'
AND (order_header.order_status_type_id = 'SUP2' OR order_header.order_status_type_id = 'SUP3')
For supplier invoices (other than inventory purchases) linked to a purchase order, the quantity on each of its INVOICE_ITEM entries must be less than or equal to the total ordered quantity on any related purchase order items before any supplier invoice can be promoted from 'Pending' to 'Complete'. This rule applies when the INVOICE_QTY_VAR_MIN tolerance parameter in Accounts Payable Control Data is neither zero nor blank. For a given invoice_type, invoice_id and invoice_item_seq_no, a SQL query such as the following will return the total ordered quantity for an INVOICE_ITEM (or NULL if no related purchase order items exist), after INVOICE_QTY_VAR_MIN tolerance parameter adjustment:
SELECT ((COALESCE(SUM(order_item.quantity), 0) * ([INVOICE_QTY_VAR_MIN] / 100))
AS quantity_ordered
FROM invoice_item
LEFT JOIN invoice_header ON (invoice_header.invoice_type = invoice_item.invoice_type
AND invoice_header.invoice_id = invoice_item.invoice_id)
LEFT JOIN order_header ON (order_header.order_type = invoice_header.order_type
AND order_header.order_id = invoice_header.order_id
AND order_header.party_id_internal = invoice_header.party_id_bill_to)
RIGHT JOIN order_item ON (order_item.order_type = order_header.order_type
AND order_item.order_id = order_header.order_id
AND (order_item.product_id = invoice_item.product_id OR order_item.order_item_desc LIKE invoice_item.invoice_item_desc)
WHERE invoice_item.invoice_type = '[invoice_type]'
AND invoice_item.invoice_id = '[invoice_id]'
AND invoice_item.invoice_item_seq_no = '[invoice_item_seq_no]'
AND invoice_header.order_type = 'P'
AND (order_header.order_status_type_id = 'SUP2' OR order_header.order_status_type_id = 'SUP3')
For supplier invoices linked to a purchase order, the adjusted_price_tx on each of its INVOICE_ITEM entries must be less than or equal to the highest adjusted_price_tx on any related purchase order items before any supplier invoice can be promoted from 'Pending' to 'Complete'. This rule applies when the PRICE_VAR_MIN tolerance parameter in Accounts Payable Control Data is neither zero nor blank. For a given invoice_type, invoice_id and invoice_item_seq_no, a SQL query such as the following will return the highest price for an INVOICE_ITEM (or NULL if no related purchase order items exist), after PRICE_VAR_MIN tolerance parameter adjustment:
SELECT (COALESCE (MAX(order_item.adjusted_price_tx, 0) * ([PRICE_VAR_MIN] / 100)) AS highest_adjusted_price_tx
FROM invoice_item
LEFT JOIN invoice_header ON (invoice_header.invoice_type = invoice_item.invoice_type
AND invoice_header.invoice_id = invoice_item.invoice_id
LEFT JOIN order_header ON (order_header.order_type = invoice_header.order_type
AND order_header.order_id = invoice_header.order_id
AND order_header.party_id_internal = invoice_header.party_id_bill_to)
RIGHT JOIN order_item ON (order_item.order_type = order_header.order_type
AND order_item.order_id = order_header.order_id
AND (order_item.product_id = invoice_item.product_id OR order_item.order_item_desc LIKE invoice_item.invoice_item_desc)
WHERE invoice_item.invoice_type = '[invoice_type]'
AND invoice_item.invoice_id = '[invoice_id]'
AND invoice_item.invoice_item_seq_no = '[invoice_item_seq_no]'
AND invoice_header.order_type = 'P'
AND (order_header.order_status_type_id = 'SUP2' OR order_header.order_status_type_id = 'SUP3')
This uses values for VALUATION_TYPE, COST_ELEMENT_ID and PRODUCT_BOM_TYPE which are defined in Accounts Payable Control Data.
Lookup on the PRODUCT_COST table using the following:
Payment of supplier invoices, and other settlements of open supplier invoices and supplier debit notes using supplier credit notes, are recorded on the PAYMENT_HEADER, PAYMENT_ITEM_SETTLED and PAYMENT_ITEM_APPLIED tables. The PAYMENT_ITEM_SETTLED table holds an entry for each invoice/credit note/debit note component which is to be settled with this payment. The PAYMENT_ITEM_APPLIED table holds an entry whenever a settlement amount is reduced or replaced by an amount which is applied from a credit note. If the amount being applied matches the amount being settled then there is no payment.
Once the details of each payment have been entered, completed and marked as approved they can be posted to the General Ledger using the logic described in Constructing Account Distribution for Payments and Constructing Journal Entries for Payments.
The following settlement options are possible:
The first screen in this process (payment_item(add7)) will allow one or more documents to be selected for settlement in the PAYMENT_ITEM_SETTLED table. After pressing the SUBMIT button a second screen (payment_item(multi4)) will show all the component parts (adjustments, items, item adjustments and item features) instead of the document headers. If the PAYMENT_HEADER indicates that a credit or debit note is being applied then the relevant data will automatically be entered into the PAYMENT_ITEM_APPLIED table.
If an invoice component is being settled from a payment it is also possible to settle all or part of that component's value by applying one or more credit notes by manually creating an entry on the PAYMENT_ITEM_APPLIED table. Once a matching credit note component is selected the applied_value_tx amount will be used to reduce the payment amount signified in the settlement_value_tx column. If a credit note amount is to be incorporated into a settlement amount the following rules apply:
The amount of each settlement which is to be provided by a payment to that vendor/supplier can therefore be calculated as (abs(settlement_value_tx) - abs(applied_value_tx)).
Note also that when settling an invoice from a payment it is possible to specify a settlement_value_tx which is less than the balance on that invoice item. When the payment status is promoted to 'WD' (Financial account debited) the settled/applied amount will be subtracted from the current invoice balance to provide an outstanding balance which can be settled with another payment.
When settling invoices with a single payment and the payment amount is less than the total invoice balance there are two options which can be used:
Crypto-currency transactions involve BOTH the spending of one or more transaction outputs that are manually keyed into the PAYMENT_OUTPUT table, AND the receipt of "change" (payment_value_in_tx field). So:
An alert can optionally be created for each payment or settlement in the ALERT database. This will allow electronic copies of remittance advice documents (for e.g. PDF files generated by the task_id_remittance_advice entries on the AP_PARTY_RELATIONSHIP table) to be attached to a PAYMENT_HEADER entry using an ALERT entry and, optionally, one or more ALERT_DETAIL entries that are linked to the PAYMENT_HEADER entry. It will also allow approvals of the payment or settlement to be delegated to specific personnel and route any document attachments to those personnel for review, using the GM-X Workflow subsystem.
If a payment is created manually and not by the batch payment process (i.e. when payment_batch_id is empty) then any attachments for the documents (invoice, credit note or debit note) within that payment should be copied to the ALERT_DETAIL table for that payment. This will allow the user to view those attachments without having to drill down into each individual document.
If the IS_PAYMENT_ALERT parameter in Accounts Payable Control data is YES, and payment_batch_id is empty (i.e. the payment is being created manually and not automatically with a batch), an alert will automatically be created whenever the payment_value_net_ac (or payment_value_out_tx if it is zero) of the PAYMENT_HEADER is greater than or equal to the PAYMENT_MIN parameter in Accounts Payable Control data, after the payment_value_net_ac (or payment_value_out_tx if it is zero) is converted from the currency_code_tx of the PAYMENT_HEADER to the CURRENCY_CODE_MIN parameter in Accounts Payable Control data at the current spot rate of exchange.
Should the need arise to void a payment (for e.g. in the event a paper cheque is lost in the mail) then a separate PAYMENT_HEADER and one or more PAYMENT_ITEM_SETTLED records must be recorded which reverse the effects of the original payment.
Each payment has the following possible states:
Unless they are future dated, most payments other than paper cheque or e-cheque will instantaneously debit the financial account when the payment is dispatched.
After approval for payment occurs, the payment amounts and affected accounts must not be changed. Instead, if corrections are needed, two new payments should be entered and posted, one to reverse the payment (same as voiding a payment) and the other consisting of the corrected supplier invoices, supplier debit notes, supplier credit notes and amounts.
Figure 11 - Payments and Settlements
This holds information about each of the supplier payments, credit notes and/or debit notes used to settle a supplier invoice or debit note.
| Field | Type | Description |
|---|---|---|
| party_id_functional | number | This identifies an organisation which is a functional unit for which a balanced set of books is kept in the GL_ACCOUNT_PERIOD_BALANCE table. Links to an entry on the FUNCTIONAL_UNIT table. |
| settlement_number | number | This is the settlement number which is generated by the system. Each functional unit will have its own numbering sequence. |
| posting_date | date | This is the date which determines the fiscal period to which the journal entry created from this payment was (or will be) posted. Today's date by default. |
| transaction_date | date | This is the date when the transaction underlying this journal entry occurred (or will occur). Today's date by default. |
| payment_desc | string | Long description of the payment or settlement. This is also the default description for each of this payment's items. |
| financial_account_id_out | number | Optional. Required if invoice_id_applied is blank. This is the identity of the financial account from which funds for this payment are disbursed. Links to an entry on the FINANCIAL_ACCOUNT table. This account may be in a currency which is different from the organisation's functional currency.
The default value is the financial_account_id_remit_from for the AP_PARTY_RELATIONSHIP entry having party_id_functional as party_id_1 and the party_id_bill_from (from any of the INVOICE_HEADER entries for this payment) as party_id_2. The party_id_owner of this financial account must be the same as that of the party_id_functional. |
| financial_account_id_in | number | Optional. This is the identity of the financial account receiving the "change" from this payment. Required if the financial_account_type of the financial_account_id_out is 4 (crypto-currency) and in this case the currency in which those accounts are denominated must be the same; otherwise must be blank. Links to an entry on the FINANCIAL_ACCOUNT table which must have a financial_account_type value of 4 (crypto-currency).
The party_id_owner of this financial account must be the same as that of the party_id_functional. |
| payment_method | string | Optional. Required if invoice_id_applied is blank. This is the method of payment, which can be one of the following:
|
| financial_account_id_payee | number | Optional. Required if payment_method is 4 (Direct deposit/ACH), 5 (Wire transfer) or 10 (Crypto-currency). This is the identity of the financial account to which funds for this payment are sent. Links to an entry on the FINANCIAL_ACCOUNT table.
The default value is the financial_account_id_remit_to for the AP_PARTY_RELATIONSHIP entry having party_id_functional as the 'customer' role_type_id and the party_id_bill_from (from any of the INVOICE_HEADER entries for this payment) as the 'supplier' role_type_id. The party_id_owner of this financial account must be the same as that of the party_id_bill_from from any of the INVOICE_HEADER entries for this payment. |
| payment_reference | string | Optional. Required if invoice_id_applied is blank. Must be blank if invoice_id_applied is non-blank.
This is the identity of the payment instrument which may include, but is not necessarily limited to, any of the following:
|
| invoice_type_applied | string | Optional. This identifies the type of document, if any, which was applied by this settlement, which can be one of the following:
Links to an entry on the INVOICE_HEADER table. This entry can be made only if a single credit note was applied to settle an entire invoice or debit note, or a single debit note was applied to settle an entire credit note. In this case invoice_id_applied must be non-blank, financial_account_id_out and payment_reference must be blank, and the absolute adjusted_value of the INVOICE_HEADER record for the credit or debit note which was applied must equal the absolute adjusted_value of the INVOICE_HEADER record for the invoice, debit note or credit note which was settled. Otherwise, this entry must be blank. |
| invoice_id_applied | number | Optional. This is the identity number of the credit note or debit note which was applied by this settlement, in whole or in part. Links to an entry on the INVOICE_HEADER table.
This entry can be made only if a single credit note was applied to settle an entire invoice or debit note, or a single debit note was applied to settle an entire credit note. In this case invoice_id_applied must be non-blank, financial_account_id_out and payment_reference must be blank, and the absolute adjusted_value of the INVOICE_HEADER record for the credit or debit note which was applied must equal the absolute adjusted_value of the INVOICE_HEADER record for the invoice, debit note or credit note which was settled. Otherwise, this entry must be blank. |
| party_id_bill_from | number | This identifies the supplier/vendor who is the subject of this payment/settlement. Links to the party_id_2 column on an entry on the AP_PARTY_RELATIONSHIP table. |
| relationship_seq_no | number | Links to the seq_no column on an entry on the AP_PARTY_RELATIONSHIP table. |
| currency_code_tx | string | This is the currency in which the payment or invoice_id_applied is denominated. Links to an entry on the CURRENCY_CODE table. The default is the currency of the financial_account_id_out. |
| payment_value_out_tx | number | This is the total value of the outgoing payment in the transaction currency, which must be the same as the currency_code_tx of all settled invoices, credit notes and debit notes. Never negative. If the financial_account_type of the financial_account_id_out is 4 (crypto-currency) this is read-only and calculated as the sum of payment_value_out_tx values for all related PAYMENT_OUTPUT entries; otherwise a non-zero entry must be input.
If invoice_type_applied is a debit note or credit note then payment_value_out_tx will be set to the adjusted_value_tx value for that debit/credit note and cannot be changed. Note that this value excludes any withholding_tax_tx amount because those values are NEVER remitted to the payee. Instead they are remitted later to a tax authority on the payee's behalf. |
| payment_value_out_ac | number | Read only. This is used only when batch_payment_id is not empty. This is the value of payment_value_out_tx after it has been converted into the currency of the financial_account_id_out using the current exchange rate.
This is the value which is accumulated into the cash_requirement_amount of the associated PAYMENT_BATCH record. |
| payment_value_net_fn | number | This is the total value of the payment in the functional currency of the party_id_functional. Never negative. Read only. If the currency_code_tx is the same as the functional currency of the party_id_functional then this value is the same as payment_value_net_ac when the payment is first created. Otherwise this value is initially zero when the payment is first created; when the payment status is promoted from 'Payment dispatched' to 'Financial account debited', the system sets this value to the value of payment_value_net_ac if exchange_rate_fn is blank, or automatically calculates this value as payment_value_net_ac * exchange_rate_fn, rounded to the functional currency's least significant digit. |
| payment_value_net_ac | number | This is the total value of the payment in the currency of the financial_account_id_out. Never negative. If the currency_code_tx is the same as the currency of the financial_account_id_out then this value is calculated automatically by the system as (payment_value_out_tx - payment_value_in_tx) when the payment is first created. Otherwise this value is initially zero when the payment is first created, and must be entered before the payment status can be promoted from 'Payment dispatched' to 'Financial account debited'. |
| payment_value_in_tx | number | This is the total value of the "change" from this payment. Never negative. An entry of zero or more is required if the financial_account_type of the financial_account_id_out is 4 (crypto-currency) in which case the entry must be less than the payment_value_out_tx; otherwise the entry must be zero. |
| exchange_rate_actual | number | Optional. This is the actual rate of exchange at which the difference between the payment_value_out_tx and payment_value_in_tx amounts was purchased using the payment_value_net_ac amount, before the addition of financial intermediary fees and currency conversion commissions. Read-only. Blank if the currency_code_tx is the same as the currency of the financial_account_id_out; otherwise calculated by the system as payment_value_net_ac / (payment_value_out_tx - payment_value_in_tx) rounded to 7 decimal places, or blank if payment_value_net_ac is zero. |
| exchange_rate_fn | number | Optional. This is the recognition rate of exchange which converts payment_value_net_ac, excluding financial intermediary fees and currency conversion commissions, to the functional currency of the party_id_functional. Must be blank if the currency of the financial_account_id_out is the same as the functional currency of the party_id_functional or if payment_value_net_ac is zero. Otherwise, if payment_value_net_ac is greater than zero, must be entered before the payment status can be promoted from 'Payment dispatched' to 'Financial account debited'. |
| forex_contract_id | string | Optional. This is the identifier of the forward future currency exchange contract, if any, governing purchase of the payment_value_out_tx from the financial intermediary. |
| commission_fee_ac | number | This is the total value of financial intermediary or bursar fees and currency conversion commissions in the currency of the financial_account_id_out, which were added to the payment_value_net_ac before the financial account was debited. Read-only after the payment status is promoted from 'Payment dispatched' to 'Financial account debited'. |
| commission_fee_fn | number | This is the total commission_fee_ac value in the functional currency of the party_id_functional. Read-only. If the currency of the financial_account_id_out is the same as the functional currency of the party_id_functional then this value is the same as commission_fee_ac. Otherwise this value is initially zero when the payment is first created, and is automatically calculated by the system as (commission_fee_ac * exchange_rate_fn), rounded to the functional currency's least significant digit, when the payment status is promoted from 'Payment dispatched' to 'Financial account debited'. |
| withholding_tax_tx | number | Read only. This is the amount of Withholding Tax, if any, which will be deducted from the payment made to the supplier, but will be included in the amount taken from the organisation's bank account. |
| withholding_tax_fn | number | Read only. This is the same as withholding_tax_tx but in functional currency. |
| fiscal_calendar_id | number | Optional. This identifies the fiscal calendar of the journal entry which was created for this payment. Links to an entry on the JOURNAL_ENTRY_HEADER table. |
| fiscal_year | number | Optional. This identifies the fiscal year of the journal entry which was created for this payment. Links to an entry on the JOURNAL_ENTRY_HEADER table. |
| fiscal_period | number | Optional. This identifies the fiscal period of the journal entry which was created for this payment. Links to an entry on the JOURNAL_ENTRY_HEADER table. |
| journal_number | number | Optional. This is the identity of the journal entry which was created for this payment. Links to an entry on the JOURNAL_ENTRY_HEADER table. |
| payment_status_type_id | string | Optional. This is the current payment status. Links to an entry on the PAYMENT_STATUS_TYPE table. |
| payment_batch_id | number | Optional. This is the payment batch in which this payment is included. Links to an entry on the PAYMENT_BATCH table. |
| is_approved | boolean | A YES/NO switch with an initial value of NO. Indicates if the payment has been approved, or not. Once is_approved is YES for a payment, the payment cannot be modified or deleted. |
| is_posted | boolean | A YES/NO switch with an initial value of NO. Indicates if the payment has been posted by creating a journal entry for the general ledger, or not. Once is_posted is YES for a payment, the payment cannot be posted again. |
| is_void_posted | boolean | A YES/NO switch with an initial value of NO. Indicates if voiding the payment has been posted by reversing the original journal entry for the general ledger, or not. Once is_void_posted is YES for a payment, the payment cannot be voided again. |
This holds information on each of the invoice, credit note or debit note items which will be settled by this payment. Each record must point to one and only one of the 4 invoice components. Any associated entries on the PAYMENT_ITEM_APPLIED table will reduce the payment to a lesser (including zero) amount.
| Field | Type | Description |
|---|---|---|
| party_id_functional | number | This identifies the functional unit. Links to an entry on the PAYMENT_HEADER table. |
| settlement_number | number | This is the settlement number. Links to an entry on the PAYMENT_HEADER table. |
| settlement_line_item | number | This is the settlement line item number which is generated by the system. Settlement line item numbers start at 1 for the first line item entered, and are incremented by 1 for each subsequent line item. |
| settlement_item_desc | string | Optional. Long description. If blank then the default description from the payment header is displayed instead. |
| invoice_type | string | This identifies the type of document which was settled by this item. Links to an entry on the INVOICE_HEADER table. It will be one of the following:
The value allowed depends on the contents of invoice_type_applied on the PAYMENT_HEADER table:
|
| invoice_id | number | This is the identity number of the document which was settled by this item, in whole or in part. Links to an entry on the INVOICE_HEADER table. |
| invoice_adjustment_seq_no | number | Optional. This is the identity of the invoice adjustment which was settled by this item, if any, in whole or in part. Links to an entry on the INVOICE_ADJUSTMENT table. |
| invoice_item_seq_no | number | Optional. This is the identity of the invoice item which was settled by this item, if any, in whole or in part. Links to an entry on the INVOICE_ITEM table. |
| invoice_item_prod_feature_id | number | Optional. This is the identity of the invoice item product feature which settled by this item, if any, in whole or in part. Links to an entry on the INVOICE_ITEM_FEATURE table. |
| invoice_item_adjustment_seq_no | number | Optional. This is the identity of the invoice item adjustment which was settled by this item, if any, in whole or in part. Links to an entry on the INVOICE_ITEM_ADJUSTMENT table. |
| settlement_value_tx | number | Required. This is the value of this settlement item in transaction currency which is initially the same as balance_tx. If invoice_type is 'V' then this value can be reduced to signify a part-payment, but can never be greater than balance_tx. |
| settlement_value_fn | number | Read-only. This is the value of this settlement item in the functional currency which is initially the same as balance_fn. Should the value of settlement_value_tx be reduced from its original then the ratio of the reduction is calculated as settlement_value_tx / balance_tx, then the current contents of settlement_value_fn is multiplied by this ratio. |
| balance_tx | number | Read-only. This is the outstanding balance, in transaction currency, of this invoice component which is due to be settled.
It is calculated using the following steps:
|
| balance_fn | number | Read-only. This is the outstanding balance, in functional currency, of this invoice component which is due to be settled.
It is calculated using the following steps:
|
| applied_value_tx | number | Read-only. This is the sum of the applied_value_tx field from all associated PAYMENT_ITEM_APPLIED entries. This value can never be more than settlement_value_tx when both values are compared as positives. |
| applied_value_fn | number | Read-only. This is the sum of the applied_value_fn field from all associated PAYMENT_ITEM_APPLIED entries. This value can never be more than settlement_value_fn when both values are compared as positives. |
| realised_gain_loss | number | Read-only. This is the difference, if any, between the original value of this part of the invoice in the functional currency of the party_id_functional, and the settlement_value_fn. Calculated by the system. Refer to Payment Revaluations for details. |
| days_to_settle | number | Read-only. This is the number of days it took to settle this part of the invoice. Calculated by the system based on the invoice_date of the settled INVOICE_HEADER record. |
This identifies when all or part of a settlement amount comes from either a debit note or credit note. Each record must point to one and only one of the 4 invoice components which must also be the same type of component on the parent PAYMENT_ITEM_SETTLED record. Each applied_value_tx will reduce the amount which will be paid to the vendor/supplier.
Note that the values for applied_value_tx and applied_value_fn will be accumulated into the corresponding columns on the parent PAYMENT_ITEM_SETTLED table.
| Field | Type | Description |
|---|---|---|
| party_id_functional | number | Links to an entry on the PAYMENT_ITEM_SETTLED table. |
| settlement_number | number | Links to an entry on the PAYMENT_ITEM_SETTLED table. |
| settlement_line_item | number | Links to an entry on the PAYMENT_ITEM_SETTLED table. |
| applied_line_item | number | This is the line item number which is generated by the system. Line item numbers start at 1 for the first line item entered, and are incremented by 1 for each subsequent line item. |
| invoice_type | string | This identifies the type of document which is being applied to this settlement item. It must be one of the following:
|
| invoice_id | number | This is the identity number of the document which was applied to this settlement item.
Links to an entry on the INVOICE_HEADER table. |
| invoice_adjustment_seq_no | number | Optional. This is the identity of the invoice adjustment which was applied to this settlement item.
Links to an entry on the INVOICE_ADJUSTMENT table. |
| invoice_item_seq_no | number | Optional. This is the identity of the invoice item which was applied to this settlement item.
Links to an entry on the INVOICE_ITEM table. |
| invoice_item_prod_feature_id | number | Optional. This is the identity of the invoice item feature which was applied to this settlement item.
Links to an entry on the INVOICE_ITEM_FEATURE table. |
| invoice_item_adjustment_seq_no | number | Optional. This is the identity of the invoice item adjustment which was applied to this settlement item.
Links to an entry on the INVOICE_ITEM_ADJUSTMENT table. |
| applied_value_tx | number | Read-only. This is the amount, in transaction currency, of this invoice component which will be applied to this PAYMENT_ITEM_SETTLED. |
| applied_value_fn | number | Read-only. This is the amount, in functional currency, of this invoice component which will be applied to this PAYMENT_ITEM_SETTLED. |
| balance_tx | number | Read-only. This is the outstanding balance, in transaction currency, of this invoice component which is due to be settled.
It is calculated using the following steps:
|
| balance_fn | number | Read-only. This is the outstanding balance, in functional currency, of this invoice component which is due to be settled.
It is calculated using the following steps:
|
| realised_gain_loss | number | Read-only. This is the difference, if any, between the original value of this part of the invoice in the functional currency of the party_id_functional, and the applied_value_fn. Calculated by the system. Refer to Payment Revaluations for details. |
For payments made using crypto-currency, it is necessary to consume one or more transaction outputs which the remitter owns or controls. Those outputs cannot be split, and their total value must be greater than or equal to the payment amount. Any part of the total value which exceeds the payment amount is returned to the remitter as "change" which will credit the financial_account_id_in on a related PAYMENT_HEADER entry. This may be an existing financial account, but can be a completely new financial account to assure the remitter's anonymity on the crypto-currency's blockchain.
This allows one or more transaction outputs to be defined as the source of funds for a payment.
| Field | Type | Description |
|---|---|---|
| party_id_functional | number | This identifies an organisation which is a functional unit for which a balanced set of books is kept in the GL_ACCOUNT_PERIOD_BALANCE table. Links to an entry on the PAYMENT_HEADER table. |
| settlement_number | number | This is the settlement number. Links to an entry on the PAYMENT_HEADER table. |
| financial_account_id_out | numeric | This is the identity of the financial account from which funds for this payment are disbursed. Links to an entry on the FINANCIAL_ACCOUNT table.
The party_id_owner of this financial account must be the same as that of the party_id_functional on the PAYMENT_HEADER entry having this party_id_functional and settlement_number. |
| payment_value_out_tx | numeric | This is the value of the payment output in the transaction currency, which must be the same as the currency_code_tx of all settled invoices, credit notes and debit notes. Never negative. |
An alert can optionally be created for each payment or settlement in the ALERT database. This is controlled by the following options in the Accounts Payable Control Data:
The design calls for two procedures for initiating payments: individually, or by batch. Either procedure may be used at any time. For example:
If that business wants to push its batches through an approval workflow process then only one workflow case is required per batch, no matter how many payments are in the batch. Conversely if the business wants to push individual payments through an approval workflow process then one workflow case is required per payment (if the net payment value exceeds the PAYMENT_MIN parameter in Accounts Payable Control data). For this reason it is NOT possible to push BOTH individual payments AND batches through the workflow process.
This is why the IS_PAYMENT_ALERT parameter is ignored if payment_batch_id is not empty (i.e. the payment is being created as part of a batch). The only way to launch a workflow case for an individual payment using the ALERT subsystem is if workflow cases are NEVER launched using the ALERT subsystem for payment batches.
If you look at the 13 possible states for individual payments you will see that approval is given (status 'APPR') before the payment (for e.g. instrument such as a paper cheque or wire transfer request form) is raised (status 'PREP').
For payment batches, there are only 6 possible states and approval is given (status 'APPR') before the payment documents are prepared (status 'PREP'). At Step 4 in Payment Batches you will see that a separate PAYMENT_BATCH entry is created for each payment method.
In other words, for both individual payments and batches, the "approval" concept means "approved for payment instrument preparation" - NOT "approved for release to payee."
Why is this? Because other mechanisms always exist OUTSIDE the GM-X system for controlling the release of payments to payees:
For individual payments, 8 times out of 10 someone will prepare and release the payment first, using one of the above mechanisms, and then record the payment in the GM-X system after-the-fact. In these cases it would be completely redundant to enforce payment approval prior to document preparation in the GM-X system, because the documents have already been prepared. In other words the horse is already out of the barn and running wildly free.
For those 1 time out of 10 situations where the documents for individual payments haven't been prepared, and it is desired to enforce approval in GM-X prior to document preparation, and the business entity also processes batch payments, the user can simply create a payment batch consisting of ONE payment, and then create an approval workflow case for it. In this situation IS_BATCH_ALERT = YES and the IS_PAYMENT_ALERT setting is irrelevant.
For the remaining 1 time out of 10 situations where the documents for individual payments haven't been prepared, and it is desired to enforce payment approval in GM-X prior to document preparation, and the business entity does NOT process batch payments, the user can create an approval workflow case for the payment. In this situation IS_BATCH_ALERT = NO and the IS_PAYMENT_ALERT = YES.
A payment will be in different states at different points in time, and instead of just recording the current state it may be more useful to maintain a history of each change in state.
Figure 12 - Payment Status History
This keeps a history of all changes in status for a payment. The entry with the highest sequence number is the latest.
| Field | Type | Description |
|---|---|---|
| party_id_functional | number | Links to an entry on the PAYMENT_HEADER table. |
| settlement_number | number | Links to an entry on the PAYMENT_HEADER table. |
| seq_no | numeric | Sequence number which is generated by the system. |
| payment_status_type_id | string | Links to an entry on the PAYMENT_STATUS_TYPE table. |
| status_date | date | The date on which this entry was created. |
Many organisations prefer to raise payments to their suppliers and other parties in recurring cycles; for e.g. weekly, bi-weekly or monthly. The actions performed by each business entity during a cycle may include the following:
An alert can optionally be created for each batch in the ALERT database. This will allow approvals of the batch to be delegated to specific personnel using the GM-X Workflow subsystem. It creates an ALERT entry for the batch and automatically includes at least one or more ALERT_DETAIL entries which may exist for the PAYMENT_HEADER entries linked to the batch.
If YES, the IS_BATCH_ALERT parameter in Accounts Payable Control data will cause an alert to be automatically created whenever a new batch is created.
When a payment batch is created then any attachments for the documents (invoice, credit note or debit note) for any payment within that batch should be copied to the ALERT_DETAIL table for that batch. This will allow the user to view those attachments without having to drill down into each individual document.
When settling invoices within a single batch, which may contain several payments to different suppliers, if the cash_required_amount is greater than the cash_available_amount there are two options which can be used:
This identifies which payments are to be processed in which batch.
| Field | Type | Description |
|---|---|---|
| payment_batch_id | number | This is the payment batch number assigned by the system. |
| party_id_functional | number | This identifies an organisation which is a functional unit for which a balanced set of books is kept in the GL_ACCOUNT_PERIOD_BALANCE table. Links to an entry on the FUNCTIONAL_UNIT table. |
| payment_batch_status_type_id | string | This is the current status of this payment batch. Links to an entry on the PAYMENT_BATCH_STATUS_TYPE table. |
| party_id_bursar | number | Optional. This identifies the financial institution or agency, if any, responsible for disbursing funds to the party having the 'supplier' role_type_id on behalf of the party having the 'functional unit' role_type_id. Links to an entry on the PARTY table. |
| financial_account_id | number | This is the identity of the financial account from which funds will be paid for the batch. Links to an entry on the FINANCIAL_ACCOUNT table. The party_id_owner on the linked FINANCIAL_ACCOUNT entry must be the same as party_id_functional.
If the payment_method is 6 (Credit card), 7 (Debit card), 8 (Payment service provider) or 9 (Mobile wallet) then allow_card must be YES on the linked FINANCIAL_ACCOUNT entry. If the payment_method is 4 (Direct deposit/ACH) or 5 (Wire transfer) then allow_eft must be YES on the linked FINANCIAL_ACCOUNT entry. |
| payment_method | number | This is the payment method for the batch, which can be one of the following:
|
| payment_file_format | number | Optional. This indicates the file format specification to be used when the system prepares files for uploading payment instructions to a financial institution, which can be one of the following:
|
| cash_requirement_amount | number | Read-only. This is the estimated cash requirement for the batch in the currency_code of the financial_account_id. Calculated by the system. Never negative.
It is accumulated from the values for payment_value_out_ac for all those PAYMENT_HEADER records which are in this batch. |
| cash_available_amount | number | This is the amount of cash that is available to settle this batch of invoices. Never negative or greater than cash_requirement_amount. If less than cash_requirement_amount it signifies that there are not enough funds to settle all the invoices in full, in which case either the FIFO or Pro-Rata option must be used to identify how the settlement should be applied. |
| discount_earned_amount | number | Read-only. This is the total estimated financial discount earned for the batch. Calculated by the system. Never negative. |
| is_discount_taken | boolean | A YES/NO switch with an initial value of NO. Indicates if all financial discounts offered on all supplier invoices and supplier debit notes included in the batch are to be taken, regardless of the date_due. Changing this field will cause the system to automatically recalculate the cash_requirement_amount and discount_earned_amount by adding or subtracting the amount of any Early Settlement Discounts which are affected. |
| date_due | datetime | This is the latest date and time when the batch is due for completion. The initial value is the payment cycle date which was specified when the payment batch was initiated. The time portion defaults to 15:30 hours in the user's local time zone (converted to the server time zone in the database) and is intended as the financial institution's or bursar's cut-off time for receiving payment documents. Changing the due date will cause the system to automatically recalculate the cash_requirement_amount and discount_earned_amount due to the fact that some Early Settlement Discounts may become eligible or ineligible.
Changing this date has no effect if is_discount_taken is set to YES. |
| date_scheduled_approval | datetime | Optional. This is the deadline for approving the batch. Any entry must be earlier than the date_scheduled_prep. The date portion (in the user's local time zone) must fall on a working day (in the user's local time zone) if the party_id_functional is linked to a CALENDAR_HEADER entry. |
| date_scheduled_prep | datetime | Optional. This is the deadline for generating all the payment documents for the batch. Any entry must be earlier than the date_scheduled_dispatch. The date portion (in the user's local time zone) must fall on a working day (in the user's local time zone) if the party_id_functional is linked to a CALENDAR_HEADER entry. |
| date_scheduled_dispatch | datetime | Optional. This is the deadline for dispatching all the payment documents for the batch to the payee or bursar. Any entry must fall on or before the date_due. The date portion (in the user's local time zone) must fall on a working day (in the user's local time zone) if the party_id_functional is linked to a CALENDAR_HEADER entry. |
Note: 'dispatch' means to delivery of paper documents; sending electronic (i.e. PDF) documents by email or other means; or re-entry of payment details and/or uploading a payment file to an online service provided by the financial institution, payment service provider or crypto-currency exchange.
This keeps a history of all changes in status for a payment batch. The entry with the highest sequence number is the latest.
| Field | Type | Description |
|---|---|---|
| payment_batch_id | numeric | Links to an entry on the PAYMENT_BATCH table. |
| seq_no | numeric | Sequence number which is generated by the system. |
| payment_batch_status_type_id | string | Links to an entry on the PAYMENT_BATCH_STATUS_TYPE table. |
| status_date | date | The date on which this entry was created. |
This holds information about forms which were consumed but not issued for a payment batch.
| Field | Type | Description |
|---|---|---|
| payment_batch_id | number | This is the payment batch number. Links to an entry on the PAYMENT_BATCH table. |
| seq_no | numeric | Sequence number which is generated by the system. |
| form_number_low | string | This is an individual unissued form number, or the lowest number within a range of unissued form numbers in alphanumeric sequence. |
| form_number_high | string | Optional. This is the highest number within a range of unissued form numbers in alphanumeric sequence. Blank for an individual unissued form number. If an entry exists, it must be greater than form_number_low in alphanumeric sequence. |
Note: form_number_low and form_number_high entries must not exist as payment_reference values on any PAYMENT_HEADER entry belonging to the payment_batch_id, nor may any payment_reference exist within the range between form_number_low and form_number_high.
This procedure applies when a payment has been completed and is posted to the General Ledger. Each amount on each invoice component has to be linked with a General Ledger Account before it can be added to an entry on the JOURNAL_ENTRY_ITEM table.
Each payment or settlement will have a PAYMENT_HEADER entry and may have one or more related PAYMENT_ITEM_SETTLED entries. Additionally, each of those PAYMENT_ITEM_SETTLED entries (if any) may have one or more related PAYMENT_ITEM_APPLIED entries.
Each of those PAYMENT_ITEM_SETTLED and PAYMENT_ITEM_APPLIED entries (if any) will refer to a component of a document which is a supplier invoice, supplier credit note or supplier debit note. Each of those components is represented as an entry on one of the following database tables:
Note that the INVOICE_ADJUSTMENT table has different rules for tax and non-tax entries.
Note also that INVOICE_ADJUSTMENT entries linked to a PRICE_COMPONENT having a price_type of 'C' (Commission) are not applicable for Accounts Payable payments and do *NOT* have entries on the PAYMENT_ITEM_SETTLED and PAYMENT_ITEM_APPLIED tables.
When an entry on any of these four database tables is settled, either by payment of money or by applying another entry from one of these four database tables, this action will require two or more entries to be written to the JOURNAL_ENTRY_ITEM table. Every input will require an output for the account_id which has been associated with the usage 'trade_payables' as a contra side entry, plus one or more additional usages which are identified as required because of relevant data which exists in other database tables.
There are five situations which may cause additional entries to be written to the JOURNAL_ENTRY_ITEM table:
In situations involving payment of money every input will require an output for the account_id which has been associated with the usage 'cash_in_bank' as a first side entry, plus one or more additional usages which can be provided by the logic described in Traversing the SUBSYSTEM_JE_TEMPLATE table (payments).
In all of the above cases, because only one additional usage is ever identified for a particular monetary value then the value used in the contra side entry can be reversed and used in a single first side entry.
When dealing with vendor invoices where the transaction currency is different from the functional currency the exchange rate used when the invoice was created may be different from that which applies when the invoice is paid. This results in a different functional currency amount, and this difference must be recorded in the ledger as either a gain or a loss. This revaluation may occur in two different places:
This procedure takes place whenever the value for exchange_rate_fn is changed, or a different value for exchange_rate_actual has been calculated due to a value being entered for payment_value_net_ac, on the PAYMENT_HEADER table. It involves cycling through the associated PAYMENT_ITEM_SETTLED and PAYMENT_ITEM_APPLIED entries each of which is linked to one of the four invoice components - INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE. Each of these entries must be processed EXCEPT the following entries which are *NEVER* revalued and must ALWAYS be skipped:
The calculation is slightly different for each payment component. Note that the PAYMENT_ITEM_APPLIED entries must be processed before their parent PAYMENT_ITEM_SETTLED entries as the latter will require the results produced by the former.
Note also that it is possible for a payment to deal with three different currencies:
The PAYMENT_HEADER record stores only two exchange rates:
If all three currencies are different then the value for exchange_rate_actual must be temporarily calculated as payment_value_net_fn / payment_value_out_tx.
After the each of the PAYMENT_ITEM_APPLIED and PAYMENT_ITEM_SETTLED entries have been updated the value of payment_value_net_fn must be calculated as the sum of the payment_item_settled values minus the sum of all payment_item_applied values on all associated PAYMENT_ITEM_SETTLED entries.
The identity of the records to be read is held in je_template_id_payment on the AP_PARTY_RELATIONSHIP table for the party_id_bill_from and relationship_seq_no values of the PAYMENT_HEADER.
Note that even though a value for je_template_id may be provided, there may or may not be any eligible records on the SUBSYSTEM_JE_TEMPLATE_ITEM table.
Each valid record read from the SUBSYSTEM_JE_TEMPLATE_ITEM table will result in a record being written to the JOURNAL_ENTRY_ITEM table for each document component.
All the available records must be read and processed one at a time. Note that any record with an account_usage which does not appear in Segment 1 in the following table is to be ignored.
| Segment | Option | First Side Column Usage | Contra Column Usage |
|---|---|---|---|
| 1 | Supplier Invoice, Credit Note or Debit Note Paid, Settled or Applied | book_gain_loss | crystallised_gain_loss |
| cash_in_bank | trade_payables | ||
| 2 | Financial Discount Offered | creditor_fin_dsc_taken | trade_payables |
| 3 | Unapplied Cash | cash_in_bank | vendor_deposit |
| 4 | Sales Tax (see Note 5) | sales_tax_liability | sales_tax_payable |
| 5 | Withholding Tax (see Note 6) | tax_wh_payable | cash_in_bank |
| 6 | Bank Commissions and Fees | cash_in_bank | commissions_fees |
Note 5 - Whether or not Segment 4 is required depends on the value of the is_tax_due_on_accrual field in the AP_PARTY_RELATIONSHIP table. If the value of this field is NO then Segment 4 is required; otherwise Segment 4 is NOT required..
Note 6 - Whether or not Segment 5 is required depends on the value of the is_withholding_tax_applicable field in the AP_PARTY_RELATIONSHIP table. If the value of this field is YES for BOTH the party_id_bill_to and party_id_bill_from then Segment 5 is required; otherwise Segment 5 is NOT required.
Examination of this table will stop when it finds a record containing a non-blank account_id for the selected account_usage.
This procedure is used to convert an account_usage into an account_id. This value may be provided on any one of a number of records which must be searched in the following sequence:
Note that some of these tables can only be read if the input record from the INVOICE subsystem contains the primary key for that table.
As soon as the first non-blank value for account_id is found then the account determination process will be terminated. If at the end there is no value for account_id then the value for account_usage will be used instead. This record will have to be corrected by the user manually in order to balance the debits and credits for the document before it can be marked as 'complete'.
Note that the PRICE_COMPONENT table can only be used for percentage rates (such as tax rates) and sale prices, it cannot be used to provide purchase prices as they are always entered manually.
The "contra side" (or "opposite side") refers to the side (debit side, or credit side) of a JOURNAL_ENTRY_ITEM entry which identifies the trade payables liability account affected by the document component. The proposed credit_functional or debit_functional value comes from:
Monetary amounts for the four invoice components, the INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE entries for a supplier invoice or supplier debit note are positive numbers for prices and surcharges, or negative numbers for discounts. This means:
Monetary amounts on the INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE entries for a supplier credit note are negative numbers for prices and surcharges, or positive numbers for discounts. This means:
Relative to the contra side entry for a document component, the "first side" refers to the side of its mandatory and opposite entry. Each first side entry must affect a different account than its corresponding contra side entry. Therefore:
This is for sales taxes, other taxes such as duties or use taxes. Only applies when the settlement_value_fn (for PAYMENT_ITEM_SETTLED entries) or applied_value_fn (for PAYMENT_ITEM_APPLIED entries) are non-zero.
Note that for INVOICE_ADJUSTMENT records (tax) the procedure begins with Segment 5 construction (if required) because each Segment 5 eliminates the posting of values to the 'trade_payables' and 'cash_in_bank' accounts for Segment 1 constructed from the same INVOICE_ADJUSTMENT record (tax). For this purpose it will be necessary to initialise the temporary variable is_segment_5_posted to FALSE for each INVOICE_ADJUSTMENT record (tax).
Note that in the following paragraphs the settlement_value_fn column is taken from the PAYMENT_ITEM_SETTLED table and the applied_value_fn column is taken from the PAYMENT_ITEM_APPLIED table when the INVOICE_ADJUSTMENT was found on that table.
To construct each Segment 1:
To construct each Segment 4:
This includes all INVOICE_ADJUSTMENT entries except those linked to an ORDER_ADJUSTMENT_TYPE entry having an order_adjustment_type_id that begins with 'TAX', and those linked to a PRICE_COMPONENT entry having a price_type of 'C' (Commission). It also excludes any entry where product_id_free is non-blank as that product will have its own entry in the INVOICE_ITEM table, and where the settlement_value_fn (for PAYMENT_ITEM_SETTLED entries) or applied_value_fn (for PAYMENT_ITEM_APPLIED entries) are zero.
For each INVOICE_ADJUSTMENT record meeting the criteria described in the preceding paragraph:
Note 7 - This design supports only the "gross method" accounting treatment for financial discounts, also known as "cash discounts" or "early payment discounts". Under the gross method, invoices, credit notes and debit notes which are open and outstanding are carried on the books at full face value until paid or settled, and financial discounts are recognised as business income only when customers take the financial discounts offered. Market research indicates that nearly all businesses use gross method instead of the "net method", under which open invoices, credit notes and debit notes are carried on the books at net value after the financial discount offered.
Note that for INVOICE_ADJUSTMENT records (non-tax) the construction of a Segment 2 (if required) is performed before a Segment 1 because each Segment 2 eliminates the posting of values to the 'trade_payables' and 'cash_in_bank' accounts for Segment 1 constructed from the same INVOICE_ADJUSTMENT record (non-tax). For this purpose it will be necessary to initialise the temporary variable is_segment_2_posted to FALSE for each INVOICE_ADJUSTMENT record (non-tax).
To construct each Segment 2:
To construct each Segment 1:
To construct each Segment 5:
This section includes all INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE components which are attached to an entry on the PAYMENT_ITEM_APPLIED or PAYMENT_ITEM_SETTLED table, except those INVOICE_ITEM_ADJUSTMENT entries linked to a PRICE_COMPONENT entry having a price_type of 'C' (Commission).
To construct each Segment 1:
This includes all PAYMENT_HEADER entries having a non-zero commission_fee_fn value.
A Segment 6 must be constructed for any such entry:
This includes all PAYMENT_HEADER entries for which the payment_value_net_fn value on the PAYMENT_HEADER entry is not equal to the sum of all settlement_value_fn values on the related PAYMENT_ITEM_SETTLED entries, less the sum of all applied_value_fn values on their related PAYMENT_ITEM_APPLIED entries.
A Segment 3 must be constructed for any such entry:
The fap_payment_header(upd4)post and fap_payment_header(batch)post tasks will create JOURNAL_ENTRY_HEADER and JOURNAL_ENTRY_ITEM entries from the INVOICE_ADJUSTMENT, INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and/or INVOICE_ITEM_FEATURE (i.e. document component) entries related to each selected or qualified PAYMENT_HEADER entry, via its associated PAYMENT_ITEM_SETTLED and PAYMENT_ITEM_APPLIED entries.
A payment cannot be posted to the General Ledger unless the PAYMENT_HEADER entry has the following values:
or
The following table shows the field mappings for constructing JOURNAL_ENTRY_HEADER entries.
| JOURNAL_ENTRY_HEADER | Document Component or Constant |
|---|---|
| party_id_functional | Refer Note 1, below |
| fiscal_calendar_id | Refer Note 2, below |
| fiscal_year | Refer Note 4, below |
| fiscal_period | Refer Note 4, below |
| journal_number | Refer Note 3, below |
| posting_date | Refer Note 4, below |
| transaction_date | Refer Note 4, below |
| journal_desc | Refer Note 5, below |
| uom_id_statistic | Refer Note 6, below |
| recurring_je_number | NULL |
| processing_seq | NULL |
| repeat_count | NULL |
| je_status_type_id | 'PEND' |
| is_posted | 'N' |
| subsys_id | 'FINANCE_AP' |
| je_template_id | Refer Note 7, below |
The following table shows the field mappings for constructing JOURNAL_ENTRY_ITEM entries which are constructed from each invoice component which appears on any associated PAYMENT_ITEM_SETTLED and PAYMENT_ITEM_APPLIED entries. Some values are obtained using the logic described in Constructing the Account Distribution for Payments and Settlements.
| JOURNAL_ENTRY_ITEM | Document Component or Constant |
|---|---|
| party_id_functional | Refer Note 1, above |
| fiscal_calendar_id | Refer Note 2, above |
| fiscal_year | Refer Note 4, above |
| fiscal_period | Refer Note 4, above |
| journal_number | Refer Note 3, above |
| journal_line_item | Refer Note 3, above |
| journal_item_desc | NULL |
| party_id_transacting | Refer Note 8, below |
| document_reference | Refer Note 9, below |
| chart_id | Refer Note 2, above |
| account_id | Refer to Traversing the SUBSYSTEM_JE_TEMPLATE table and Determine the account_id from an account_usage value sections, above |
| debit_functional | Refer Debits and Credits for First Side and Contra Side Entries section, above; and Note 10, below. |
| credit_functional | Refer Debits and Credits for First Side and Contra Side Entries section, above; and Note 10, below. |
| statistic | NULL |
| uom_id_statistic | NULL |
| invoice_type | invoice_type |
| invoice_id | invoice_id |
| invoice_adjustment_seq_no | adjustment_seq_no (for INVOICE_ADJUSTMENT entries only); otherwise NULL |
| invoice_item_seq_no | invoice_item_seq_no (for INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE entries only); otherwise NULL |
| invoice_item_prod_feature_id | prod_feature_id (for INVOICE_ITEM_FEATURE entries only); otherwise NULL |
| invoice_item_adjustment_seq_no | adjustment_seq_no (for INVOICE_ITEM_ADJUSTMENT entries only); otherwise NULL |
| payment_id | Refer Note 11, below |
| payment_line_no | Refer Note 11, below |
| payment_applied_line_no | Refer Note 12, below |
| order_type | Refer Note 13, below |
| order_id | Refer Note 13, below |
| <all other fields>
(except created_date and created_user) |
NULL |
This information exists within a shared table in the MENU database and not a separate table within the FINANCE_AP database. It holds values which can be varied to suit that particular installation. The current values are as follows:
| Id | Description | Value (example) |
|---|---|---|
| JOURNAL_DESC_INVOICE | To be used as the default Journal Entry Description for Documents. | |
| JOURNAL_DESC_PAYMENT | To be used as the default Journal Entry Description for payments and settlements. | |
| INVOICE_QTY_VAR_MIN | The maximum acceptable invoiced quantity as a percentage of received quantity. Minimum possible value is 100. If zero or blank, any invoiced quantity is acceptable. | 105 |
| PRICE_VAR_MIN | The maximum acceptable invoiced price as a percentage of purchase order price. Minimum possible value is 100. If zero or blank, any invoiced price is acceptable. Applicable only for supplier invoices which reference a purchase order. | 105 |
| CURRENCY_CODE_MIN | The currency code in which the minimum Document and Payment values are denominated. Must exist in the UNIT_OF_MEASURE table. | USD |
| IS_INVOICE_ALERT | Indicates if a new ALERT entry is automatically created for each new INVOICE_HEADER entry, or not.
This will require the ALERT to be signed before the associated document can have its status changed to "Approved". |
'Y' or 'N' |
| INVOICE_MIN | The minimum adjusted_value requiring approval. Unsigned number with 2 decimal places. Zero/Blank means no approvals are required. A non-zero value indicates that any document with a value equal to or greater than this will require approval. A value of '0.01' indicates that all documents will require approval. | 100.00 |
| IS_PAYMENT_ALERT | Indicates if a new ALERT entry is automatically created for each new PAYMENT_HEADER entry, or not. This is ignored when the payment is created as part of a batch.
This will require the ALERT to be signed before the associated document can have its status changed to "Approved". |
'Y' or 'N' |
| PAYMENT_MIN | The minimum payment_value requiring approval. Unsigned number with 2 decimal places. Zero/Blank means no approvals are required. A non-zero value indicates that any document with a value equal to or greater than this will require approval. A value of '0.01' indicates that all documents will require approval. | 100.00 |
| IS_BATCH_ALERT | Indicates if a new ALERT entry is automatically created for each new PAYMENT_BATCH entry, or not.
This will require the ALERT to be signed before the associated batch of documents can have its status changed to "Approved". |
'Y' or 'N' |
| CHQ_FOLDER_PATH | The path name to the directory where voucher cheque print files will be copied after being created. | |
| ACH_FOLDER_PATH | The path name to the directory where ACH payment files will be placed after being generated. | |
| SWFT_FOLDER_PATH | The path name to the directory where SWIFT MT101 payment files will be placed after being generated. | |
| US1099_FOLDER_PATH | The path name to the directory where U.S. 1099 report files will be placed after being generated. |
Date created: 15th March 2019
| 30 Mar 2024 | Updated the INVOICE_ACCOUNT_ITEM table to include the facility_id column. |
| 21st Nov 2023 | Updated Steps 3a and 3b and added Step 3c of INVOICE_ADJUSTMENT records (tax), updated Steps 3a and 3b and added Step 3c of INVOICE_ADJUSTMENT records (non-tax), and updated Steps 3 and 4 and added Step 5 of INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE records (payments) to correct the data sources for the contra side and first side entries. |
| 4th Sep 2023 | Updated INVOICE_ADJUSTMENT records (tax), INVOICE_ADJUSTMENT records (non-tax), and INVOICE_ITEM, INVOICE_ITEM_ADJUSTMENT and INVOICE_ITEM_FEATURE records (payments) to correct the account usage for first-side realised_gain_loss entries, and the value for contra-side trade_payables entries. |
| 23rd Aug 2023 | Updated Allowable payment status changes to correct the rules for automatic attainment of 'Approved' status. |
| 6th Mar 2023 | Updated Promoting document status from 'Pending' to 'Complete' to change the rules for automatic promotion from 'Complete' to 'Released for Approval'. |
| 20th Feb 2022 | Updated the Control Data to drop VALUATION_TYPE, COST_ELEMENT_ID and PRODUCT_BOM_TYPE which have been moved to Product Control data.
Updated the Lookup PRODUCT_COST section to look for a revision_id on the PRODUCT_REVISION table whose effective_date and discontinue_date include the posting_date (not the order_date). |
| 16th Feb 2022 | Updated the PAYMENT_HEADER table to include the withholding_tax_tx and withholding_tax_fn columns. |
| 6th Nov 2021 | Updated the PAYMENT_HEADER table to include the payment_value_out_ac column. |
| 20th Dec 2020 | Updated the AP_PARTY_RELATIONSHIP table to include the relationship_name column.
Updated the PAYMENT_HEADER table to include the relationship_seq_no column. |
| 30th Sep 2020 | Updated the PAYMENT_ITEM_APPLIED table to include the balance_tx and balance_fn columns. |
| 10th Sep 2020 | Added Constructing Journal Entries for Vendor Invoices |
| 31st Aug 2020 | Updated the PAYMENT_ITEM_APPLIED table to include the realised_gain_loss column. |
| 28th Jul 2020 | Updated the PAYMENT_BATCH table to include the cash_available_amount column. |
| 14th Jul 2020 | Added the PAYMENT_ITEM_SETTLED table.
Added the PAYMENT_ITEM_APPLIED table. Removed the PAYMENT_ITEM table. |
| 2nd Jul 2020 | Updated the PAYMENT_HEADER table to remove the is_payment column.
Updated the PAYMENT_HEADER table to add the party_id_bill_from column. Updated the PAYMENT_HEADER table to rename the currency_code column to currency_code_tx. Updated the PAYMENT_HEADER table to rename the payment_value_net column to payment_value_net_ac. Updated the PAYMENT_HEADER table to rename the commission_fee column to commission_fee_ac. |
| 14th Oct 2019 | Added the PAYMENT_HEADER table.
Added the PAYMENT_ITEM table. Added the PAYMENT_OUTPUT table. Added the PAYMENT_STATUS_TYPE table. Added the PAYMENT_STATUS_HIST table. Added the PAYMENT_BATCH table. Added the PAYMENT_BATCH_STATUS_TYPE table. Added the PAYMENT_BATCH_STATUS_HIST table. Added the UNISSUED_FORMS table. |
| 4th Jul 2019 | Added the Constructing the Account Distribution for Invoices, Credit Notes and Debit Notes section. |
| 24th Apr 2019 | Added the INVOICE_ACCOUNT_HEADER and INVOICE_ACCOUNT_ITEM tables. |
| 11th Apr 2019 | Added the AP_PARTY_RELATIONSHIP and FINANCIAL_ACCOUNT tables. |
Copyright © 1999-2024 by Geoprise Technologies Licensing, All Rights Reserved.