Excel Add-In for Oracle Fusion Cloud Financials

Build 24.0.9111

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.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9111