Invoices
The payables invoices resource is used to manage details about supplier invoices, that includes header, lines, distributions, installments, descriptive flexfields, global descriptive flexfields, and attachments. Note: You can only update certain attributes and the update applies only to those attributes. Related fields don't automatically get populated. In summary, the cascade defaulting doesn't automatically happen and you must review and update the dependent attributes. For example, when you update the invoice date in the Create or Edit Invoice pages, related fields, such as the terms date or due date, may be autopopulated based on the invoice date you enter. Using REST API, the invoice date is updated, but the related fields aren't autopopulated. Before updating a key attribute, consider the impact on the related attributes.
Table Specific Information
Select
The 本製品 uses the Oracle Fusion Cloud Financials API to process some of the filters having queryable=true in metadata. The 本製品 processes other filters within the 本製品.
For example, the following query is processed server side:
SELECT * FROM [Cdata].[Financials].[Invoices] WHERE InvoiceId = 10003
Insert
Create an invoice.
INSERT INTO [Cdata].[Financials].[Invoices] (InvoiceNumber,InvoiceCurrency,InvoiceAmount,InvoiceDate,BusinessUnit,Supplier,SupplierSite,Requester,InvoiceGroup,Description) VALUES ('AND_Unmatched_Invoice','USD',2212.75,'2019-02-01','Vision Operations','Advanced Network Devices','FRESNO','Johnson,Mary','01Feb2019','Office Supplies')
If you want to add child views/tables along with the parent table, you can add the child in following ways:
-
Using TEMP table:
INSERT into InvoicesinvoiceLinesinvoiceDistributions#TEMP(DistributionLineNumber,DistributionCombination,DistributionAmount,DistributionLineType,CUReferenceNumber) values (26,'101.10.60230.462.000.000',3.25,'Item',1) INSERT into InvoicesinvoiceLinesinvoiceDistributions#TEMP(DistributionLineNumber,DistributionCombination,DistributionAmount,DistributionLineType,CUReferenceNumber) values (27,'101.10.60230.462.000.000',83.25,'Item',2) INSERT into InvoicesinvoiceLines#TEMP(invoiceDistributions,LineNumber,DistributionCombination,LineAmount,CUReferenceNumber) values ('InvoicesinvoiceLinesinvoiceDistributions#TEMP',46,'101.10.60230.462',3.25,1) INSERT into InvoicesinvoiceLines#TEMP(invoiceDistributions,LineNumber,DistributionCombination,LineAmount,CUReferenceNumber) values ('InvoicesinvoiceLinesinvoiceDistributions#TEMP',47,'101.10.60230.462',36.25,2) INSERT INTO [Cdata].[Financials].[Invoices] (InvoiceNumber,InvoiceCurrency,InvoiceAmount,InvoiceDate,BusinessUnit,Supplier,SupplierSite,Requester,InvoiceGroup,Description,invoiceLines) VALUES ('AND_Unmatched_Invoice','USD',2212.75,'2019-02-01','Vision Operations','Advanced Network Devices','FRESNO','Johnson,Mary','01Feb2019','Office Supplies','InvoicesinvoiceLines#TEMP')
Note : you can use CUReferenceNumber to map the child views/tables to their parent. For example, invoiceLines having CUReferenceNumber 1, will have the child aggregates having CUReferenceNumber 1.
-
Directly providing the aggregate:
INSERT INTO [Cdata].[Financials].[Invoices] (InvoiceNumber,InvoiceCurrency,InvoiceAmount,InvoiceDate,BusinessUnit,Supplier,SupplierSite,Requester,InvoiceGroup,Description,invoiceLines) VALUES ('AND_Unmatched_Invoice','USD',2212.75,'2019-02-01','Vision Operations','Advanced Network Devices','FRESNO','Johnson,Mary','01Feb2019','Office Supplies','[ { "LineNumber": 3, "LineAmount": 54.25, "DistributionCombination": "101.10.60230.462" } ]')
Update
The Oracle Fusion Cloud Financials API uses InvoiceUniqId instead of InvoiceId as a path parameter in the URL to update the record.
If you want to update a record directly using the id (without any other filter), you can update the record in the following way:
Update [Cdata].[Financials].[Invoices] set Description='abcd' where InvoiceUniqId=454545454;
Note: This does not require any extra GET request to retrieve the UniqId.
Alternatively, if you want to apply any other filter. Please use InvoiceId instead of InvoiceUniqId. You can update the record in the following way:
Update [Cdata].[Financials].[Invoices] set Description='abcd' where InvoiceId=454545454 and Supplier='abc';
Note : Update on aggregates are not allowed by API, use the child tables to add/update/delete aggregates.
Delete
The Oracle Fusion Cloud Financials API uses InvoiceUniqId instead of InvoiceId as a path parameter in the URL to delete the record.
If you want to delete a record directly using the id (without any other filter), you can delete the record in the following way:
Delete from [Cdata].[Financials].[Invoices] where InvoiceUniqId=454545454;
Note: This does not require any extra GET request to retrieve the UniqId.
Alternatively, if you want to apply any other filter. Please use InvoiceId instead of InvoiceUniqId. You can delete the record in the following way:
Delete from [Cdata].[Financials].[Invoices] where InvoiceUniqId=454545454 and Supplier='abc';
Columns
Name | Type | ReadOnly | Description |
InvoiceId [KEY] | Long | True |
InvoiceId of Invoices |
InvoiceUniqId [KEY] | String | True |
Use this column's value in insert, update and delete operations, wherever applicable, instead of InvoiceId |
InvoiceNumber | String | False |
The unique number for supplier invoice. |
InvoiceCurrency | String | False |
The currency code used on the invoice. The value can either be provided while creating an invoice or populated by default from the Supplier Site or from the Manage Invoice Options for the invoicing business unit. |
PaymentCurrency | String | False |
The currency used to make a payment. The value can either be provided while creating an invoice or populated by default from the Supplier Site or from the Manage Invoice Options for the invoicing business unit. |
InvoiceAmount | Decimal | False |
The invoice amount in transaction currency. The value must be provided while creating an invoice. |
InvoiceDate | Date | False |
The date on the supplier invoice. The value can either be provided while creating an invoice or populated by default to the system date. |
BusinessUnit | String | False |
Indicates the business unit name for the invoice. The value can either be provided while creating an invoice or populated by default from the profile option DEFAULT_ORG_ID. It cannot be updated once the invoice is created. |
Supplier | String | False |
The supplier name on the invoice. The value must be provided while creating the invoice. It cannot be updated once the invoice is created. |
SupplierNumber | String | True |
SupplierNumber of Invoices |
ProcurementBU | String | False |
The procurement business unit that is associated to the supplier site on the invoice and helps to uniquely identify a supplier site. |
SupplierSite | String | False |
The name of the physical location of the supplier from where the goods and services are rendered. The value must be provided during creation action. It cannot be updated once the invoice is created. |
RequesterId | Long | False |
The unique requester identifier. |
Requester | String | False |
The name of the person who requested the goods or services. The requester is used by the Invoice Approval Workflow process to generate the list of approvers. |
InvoiceGroup | String | False |
The unique name of the invoice group assigned to a group of invoices used for reporting and payment purposes. It is a mandatory attribute, which is based on the Require invoice grouping option in the Manage Invoice Options for the invoicing business unit. |
ConversionRateType | String | False |
The source of currency conversion rate for an invoice. Examples include corporate, user-defined, and spot. It is a mandatory attribute for non functional currency invoices, which is based on the Require conversion rate entry option in the Manage Common Options for Payables and Procurement options for the invoicing business unit. The value can either be provided while creating an invoice or populated by default from the Conversion Rate Type from the Manage Common Options for Payables and Procurement options for the invoicing business unit. |
ConversionDate | Date | False |
The date when a conversion rate is used to convert an amount into another currency for an invoice. It is a mandatory attribute for non functional currency invoices. The value can either be provided while creating the invoice or populated by default from the accounting date of the invoice. |
ConversionRate | Decimal | False |
The conversion rate at which the principal unit of one currency is converted into another currency for an invoice. It is a mandatory attribute for non functional currency invoices, which is based on the Require conversion rate entry option in the Manage Common Options for Payables and Procurement options for the invoicing business unit. The value can either be provided while creating the invoice or populated by default from the Manage Daily Rates for the conversion rate type and date, or can be updated later using the Apply Missing Conversion Rates process. |
AccountingDate | Date | False |
The accounting date captured on the invoice that is finally used to default to invoice lines and distributions. The value can either be provided while creating the invoice or derived based on the Accounting Date Basis option on the Manage Invoice Options for the invoicing business unit. |
Description | String | False |
The statement that describes the invoice. |
DeliveryChannelCode | String | False |
The delivery channel code entered by the user that helps to uniquely identify a delivery channel. |
DeliveryChannel | String | False |
The text on electronic payment that instructs the bank about the execution of payment. For example, print a check and hold it for collection by the payee. The value can either be provided while creating the invoice or populated by default from the Supplier Site or Address or Profile level payment attributes. |
PayAloneFlag | Bool | False |
Indicates whether the invoice is to be paid with its own payment document without including other invoices for the supplier. The value can either be provided while creating an invoice or populated by default from the Supplier Site or Address or Profile level payment attributes. The default value is false. |
InvoiceSourceCode | String | True |
InvoiceSourceCode of Invoices |
InvoiceSource | String | False |
The source that indicates the feeder system from which an invoice is created. The valid values are Spreadsheet, External, or any user-defined source in the lookup type SOURCE and can be reviewed using the Setup and Maintenance work area, and the Manage Payables Lookups task. The value can either be provided while creating the invoice or populated by default to External. |
InvoiceType | String | False |
The type of the invoice. The valid invoice types are Standard, Prepayment, Credit Memo, Debit Memo. The value can either be provided while creating the invoice or derived based on the invoice amount. The positive invoice amount is considered as Standard invoice and negative values are considered as Credit Memo. A list of accepted values is defined in the lookup type INVOICE TYPE and can be reviewed using the Setup and Maintenance work area, and the Manage Payables Lookups task. |
PayGroup | String | False |
Groups a category of supplier invoices for a single pay run. Examples include employees, merchandise, nonmerchandise, government, domestic, and international. The value can either be provided while creating an invoice or populated by default from the Supplier Site or from the Manage Invoice Options for the invoicing business unit. You can also update the pay group on the invoice. A list of accepted values is defined in the lookup type PAY GROUP and can be reviewed using the Setup and Maintenance work area, and the Manage Payables Lookups task. |
InvoiceReceivedDate | Date | False |
The date when the invoice was received. This date can be used to calculate the invoice payment due date. The value can either be provided while creating the invoice or populated by default to the system date. |
PaymentReasonCode | String | False |
The user-entered payment reason code that helps to uniquely identify a payment reason. |
PaymentReason | String | False |
The codes provided by the government or central bank of a country. These codes provide the payment system or bank with additional details about the reason for the payment, and are used for regulatory reporting purposes. The value can either be provided while creating an invoice or populated by default from the Supplier Site or Address or Profile level payment attributes. |
PaymentReasonComments | String | False |
The comments that indicate the reason for creating the payment. The value can either be provided while creating an invoice or populated by default from the Supplier Site or Address or Profile level payment attributes. |
RemittanceMessageOne | String | False |
Remittance message provided for payment processing. You can provide the information during invoice creation or update it later. |
RemittanceMessageTwo | String | False |
Remittance message provided for payment processing. You can provide the information during invoice creation or update it later. |
RemittanceMessageThree | String | False |
Remittance message provided for payment processing. You can provide the information during invoice creation or update it later. |
PaymentTerms | String | False |
The payment terms used to calculate installments and to calculate due dates, discount dates, and discount amounts for each invoice. The value can either be provided while creating an invoice or populated by default from the Supplier Site or from the Manage Invoice Options for the invoicing business unit. |
TermsDate | Date | False |
The date used with payment terms to calculate installment along with due date and discount dates. The value can either be provided while creating an invoice or derived based on the Terms Date Basis option from the Supplier Site or from the Manage Invoice Options for the invoicing business unit. |
GoodsReceivedDate | Date | False |
The date when the goods were received. This date can be used to calculate the invoice payment due date. The value can either be provided while creating the invoice or populated by default to the system date. |
PaymentMethodCode | String | False |
The user-entered payment method code that helps to uniquely identify a payment method. |
PaymentMethod | String | False |
Indicates how the first party payer is going to make the payment for the invoice. Examples include check, cash, and credit. The value can either be provided while creating an invoice or populated by default based on the Payment Method Default Basis setup in the Manage Disbursement System Options. |
SupplierTaxRegistrationNumber | String | False |
The third party tax registration number. The value can either be provided while creating an invoice or populated by default from the supplier. |
FirstPartyTaxRegistrationId | Long | False |
The unique first party tax registration identifier. |
FirstPartyTaxRegistrationNumber | String | False |
The first party tax registration number. The value can either be provided while creating an invoice or populated by default from the first party legal establishment. You can also update the value for the first-party tax registration number. |
LegalEntity | String | False |
The name of the legal entity. The value can either be provided while creating an invoice or derived by the system. |
LegalEntityIdentifier | String | False |
The user-entered legal entity identifier that helps to uniquely identify a legal entity. |
LiabilityDistribution | String | False |
The account combination to which liability is recorded. Payables uses the liability account combination when accounting entries are created for invoices. The value can either be provided while creating an invoice or populated by default from the Supplier Site Assignment or from the Default Distributions from Manage Common Options for Payables and Procurement options for the invoicing business unit. |
DocumentCategory | String | False |
The sequential numbering document category. The attribute is mandatory, which is based on the Sequencing setup at Specify Ledger Options for the ledger associated to the invoicing business unit. |
DocumentSequence | Long | False |
The manual sequence number when document sequential numbering is enabled. The attribute is mandatory, which is based on the Sequencing setup at the Specify Ledger Options for the ledger associated to the invoicing business unit. |
VoucherNumber | String | False |
The manual voucher number when document sequential numbering is not used. |
ValidationStatus | String | False |
The status indicating the state of the invoice in the validation process. The default value is set to Not Validated. |
ApprovalStatus | String | True |
ApprovalStatus of Invoices |
PaidStatus | String | True |
PaidStatus of Invoices |
AccountingStatus | String | True |
AccountingStatus of Invoices |
ApplyAfterDate | Date | False |
The date associated with a prepayment invoice after which you can apply the prepayment against invoices. It is only used for temporary prepayments. The column is null for permanent prepayments and other invoice types. The value can either be provided while creating an invoice or derived based on the Prepayment Settlement Days setup in the Manage Invoice Options for the invoicing business unit. |
CanceledFlag | Bool | True |
CanceledFlag of Invoices |
AmountPaid | Decimal | True |
AmountPaid of Invoices |
BaseAmount | Decimal | True |
BaseAmount of Invoices |
PurchaseOrderNumber | String | False |
The purchase order document number that is matched to the invoice. |
Party | String | True |
Party of Invoices |
PartySite | String | True |
PartySite of Invoices |
ControlAmount | Decimal | False |
The user-enterable value to ensure that the calculated tax will be the same as on the physical document. |
DocumentFiscalClassificationCodePath | String | False |
The classification of transactions that require special documentation to accompany them as designated by the tax authority. For example, international transactions often require proof of export documentation to support the sale or transfer of goods. A document fiscal classification code can be created to confirm receipt of the export documents. When passing the attribute value in the payload, ensure that the document fiscal classification code path is passed. |
TaxationCountry | String | False |
The country where the transaction took place for taxation purposes. Oracle Fusion Tax sets the context of other tax drivers such as the Product Fiscal Classification based on the value of this field. The value can either be provided when creating an invoice or it's populated by default from the setup. |
RoutingAttribute1 | String | True |
RoutingAttribute1 of Invoices |
RoutingAttribute2 | String | False |
Captures additional attributes information that may have been used for routing payables invoice images. |
RoutingAttribute3 | String | False |
Captures additional attributes information that may have been used for routing payables invoice images. |
RoutingAttribute4 | String | False |
Captures additional attributes information that may have been used for routing payables invoice images. |
RoutingAttribute5 | String | True |
RoutingAttribute5 of Invoices |
AccountCodingStatus | String | True |
AccountCodingStatus of Invoices |
BudgetDate | Date | False |
Budgetary Calendar Period Date applicable for funds check. |
FundsStatus | String | True |
FundsStatus of Invoices |
CanceledDate | Date | True |
CanceledDate of Invoices |
CanceledBy | String | True |
CanceledBy of Invoices |
UniqueRemittanceIdentifier | String | False |
The unique identifier for a remittance. Contains reference information for a payment. |
UniqueRemittanceIdentifierCheckDigit | String | False |
A check digit used to validate the unique remittance identifier. |
CreationDate | Datetime | True |
CreationDate of Invoices |
CreatedBy | String | True |
CreatedBy of Invoices |
LastUpdatedBy | String | True |
LastUpdatedBy of Invoices |
LastUpdateDate | Datetime | True |
LastUpdateDate of Invoices |
LastUpdateLogin | String | True |
LastUpdateLogin of Invoices |
BankAccount | String | False |
The supplier bank account number to which the payment will be remitted. The value can either be provided while creating an invoice or populated by default from the Supplier Site, Address, or Profile-level payment attributes. |
SupplierIBAN | String | False |
The alphanumeric sequence that conforms to the ISO standard for uniquely identifying a bank account number internationally. The standard IBAN carries all the routing information needed to transfer a payment from one bank to another anywhere in the world. The value provided in the payload helps to uniquely identify a bank account. If you provide values for multiple bank account-related attributes, such as Bank Account, Supplier IBAN, or External Bank Account ID, then all the information provided will be validated. |
ExternalBankAccountId | Long | False |
The unique identifier for the supplier bank account. The value can either be provided while creating an invoice or populated by default from the Supplier Site, Address, or Profile-level payment attributes. If you provide values for multiple bank account-related attributes, such as Bank Account, Supplier IBAN, or External Bank Account ID, then all the information provided is validated. |
BankChargeBearer | String | False |
The bearer of bank charge cost. A list of accepted values is defined in the lookup type IBY_BANK_CHARGE_BEARER. Accepted values can be reviewed using the Setup and Maintenance work area and the Manage Standard Lookups task. |
SettlementPriority | String | False |
The priority with which the financial institution or payment system should settle the payment for this document. A list of accepted values is defined in the lookup type IBY_SETTLEMENT_PRIORITY. Accepted values can be reviewed using the Setup and Maintenance work area and the Manage Standard Lookups task. |
ReferenceKeyOne | String | True |
ReferenceKeyOne of Invoices |
ReferenceKeyTwo | String | True |
ReferenceKeyTwo of Invoices |
ReferenceKeyThree | String | True |
ReferenceKeyThree of Invoices |
ReferenceKeyFour | String | True |
ReferenceKeyFour of Invoices |
ReferenceKeyFive | String | True |
ReferenceKeyFive of Invoices |
ProductTable | String | True |
ProductTable of Invoices |
ImageDocumentNumber | String | True |
ImageDocumentNumber of Invoices |
invoiceLines | String | False |
This column can only be used for Insert. For update and delete, use child table's operations, if applicable. |
appliedPrepayments | String | False |
This column can only be used for Insert. For update and delete, use child table's operations, if applicable. |
availablePrepayments | String | False |
This column can only be used for Insert. For update and delete, use child table's operations, if applicable. |
invoiceGdf | String | False |
This column can only be used for Insert. For update and delete, use child table's operations, if applicable. |
invoiceInstallments | String | False |
This column can only be used for Insert. For update and delete, use child table's operations, if applicable. |
invoiceDff | String | False |
This column can only be used for Insert. For update and delete, use child table's operations, if applicable. |
Finder | String | True |
finder |
EffectiveDate | Date | True |
This query parameter is used to fetch resources which are effective dated as of the specified start date. |