AP_Invoices_All table is an integral part of P2P cycle in Oracle. In this post we shall talk about the AP invoice table structure in Oracle Fusion Finance. But before that a very basic question: why is Payable Invoice used for in the first place. Simple answer: To pay the suppliers for the items/ services you bought from them, most probably using a purchase order. Table name is AP_INVOICES_ALL & primary key is invoice_id. As you know an AP Invoice contains the invoices from the vendors who your company is supposed to pay. it means that every combination of supplier and invoice number will give you a unique row in this table . This post contains the information about:
1. Table Columns
2. Indexes
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
AP_Invoices_All has the unique key on invoice_id. Use it if possible to enhance the performance.
AP_INVOICES_ALL is the parent table of AP_INVOICE_LINES_ALL table which is used to store the Invoice Line Details.
There can be only one record for the combination of Supplier and the invoice number. To get the details of the supplier, click here .
Of course the end objective to create an AP Invoice is to make the payments, in case you are looking for the query to fetch the payment details, click here.
Want to know the accounting details of the Invoice, this post can help you.
Looking for account related info, most probably ap_invoice_distributions_all is your table.
Table Columns
| COLUMN NAME | DATA TYPE | DATA LENGTH | NULLABLE |
| INVOICE_ID | NUMBER | 22 | N |
| LAST_UPDATE_DATE | TIMESTAMP(6) | 11 | N |
| LAST_UPDATED_BY | VARCHAR2 | 256 | N |
| VENDOR_ID | NUMBER | 22 | Y |
| INVOICE_NUM | VARCHAR2 | 200 | N |
| SET_OF_BOOKS_ID | NUMBER | 22 | N |
| INVOICE_CURRENCY_CODE | VARCHAR2 | 60 | N |
| PAYMENT_CURRENCY_CODE | VARCHAR2 | 60 | N |
| PAYMENT_CROSS_RATE | NUMBER | 22 | N |
| INVOICE_AMOUNT | NUMBER | 22 | Y |
| VENDOR_SITE_ID | NUMBER | 22 | Y |
| AMOUNT_PAID | NUMBER | 22 | Y |
| DISCOUNT_AMOUNT_TAKEN | NUMBER | 22 | Y |
| INVOICE_DATE | DATE | 7 | Y |
| SOURCE | VARCHAR2 | 100 | Y |
| INVOICE_TYPE_LOOKUP_CODE | VARCHAR2 | 100 | Y |
| DESCRIPTION | VARCHAR2 | 960 | Y |
| BATCH_ID | NUMBER | 22 | Y |
| AMOUNT_APPLICABLE_TO_DISCOUNT | NUMBER | 22 | Y |
| TERMS_ID | NUMBER | 22 | Y |
| TERMS_DATE | DATE | 7 | Y |
| PAYMENT_METHOD_LOOKUP_CODE | VARCHAR2 | 100 | Y |
| PAY_GROUP_LOOKUP_CODE | VARCHAR2 | 100 | Y |
| ACCTS_PAY_CODE_COMBINATION_ID | NUMBER | 22 | Y |
| PAYMENT_STATUS_FLAG | VARCHAR2 | 4 | Y |
| CREATION_DATE | TIMESTAMP(6) | 11 | N |
| CREATED_BY | VARCHAR2 | 256 | N |
| BASE_AMOUNT | NUMBER | 22 | Y |
| LAST_UPDATE_LOGIN | VARCHAR2 | 128 | Y |
| EXCLUSIVE_PAYMENT_FLAG | VARCHAR2 | 4 | Y |
| PO_HEADER_ID | NUMBER | 22 | Y |
| FREIGHT_AMOUNT | NUMBER | 22 | Y |
| GOODS_RECEIVED_DATE | DATE | 7 | Y |
| INVOICE_RECEIVED_DATE | DATE | 7 | Y |
| VOUCHER_NUM | VARCHAR2 | 200 | Y |
| APPROVED_AMOUNT | NUMBER | 22 | Y |
| RECURRING_PAYMENT_ID | NUMBER | 22 | Y |
| EARLIEST_SETTLEMENT_DATE | DATE | 7 | Y |
| DOC_SEQUENCE_ID | NUMBER | 22 | Y |
| DOC_SEQUENCE_VALUE | NUMBER | 22 | Y |
| DOC_CATEGORY_CODE | VARCHAR2 | 120 | Y |
| ATTRIBUTE1 – ATTRIBUTE15 | VARCHAR2 | 600 | Y |
| ATTRIBUTE_CATEGORY | VARCHAR2 | 600 | Y |
| APPROVAL_STATUS | VARCHAR2 | 100 | Y |
| APPROVAL_DESCRIPTION | VARCHAR2 | 960 | Y |
| POSTING_STATUS | VARCHAR2 | 60 | Y |
| CANCELLED_DATE | DATE | 7 | Y |
| CANCELLED_BY | VARCHAR2 | 256 | Y |
| CANCELLED_AMOUNT | NUMBER | 22 | Y |
| TEMP_CANCELLED_AMOUNT | NUMBER | 22 | Y |
| USSGL_TRANSACTION_CODE | VARCHAR2 | 120 | Y |
| USSGL_TRX_CODE_CONTEXT | VARCHAR2 | 120 | Y |
| PROJECT_ID | NUMBER | 22 | Y |
| TASK_ID | NUMBER | 22 | Y |
| EXPENDITURE_TYPE | VARCHAR2 | 120 | Y |
| EXPENDITURE_ITEM_DATE | DATE | 7 | Y |
| PA_QUANTITY | NUMBER | 22 | Y |
| EXPENDITURE_ORGANIZATION_ID | NUMBER | 22 | Y |
| PA_DEFAULT_DIST_CCID | NUMBER | 22 | Y |
| PAYMENT_AMOUNT_TOTAL | NUMBER | 22 | Y |
| AWT_FLAG | VARCHAR2 | 4 | Y |
| AWT_GROUP_ID | NUMBER | 22 | Y |
| REFERENCE_1 | VARCHAR2 | 120 | Y |
| REFERENCE_2 | VARCHAR2 | 120 | Y |
| ORG_ID | NUMBER | 22 | N |
| PRE_WITHHOLDING_AMOUNT | NUMBER | 22 | Y |
| GLOBAL_ATTRIBUTE_CATEGORY | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE1 – GLOBAL_ATTRIBUTE20 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE_NUMBER1 – GLOBAL_ATTRIBUTE_NUMBER5 | NUMBER | 22 | Y |
| ATTRIBUTE_NUMBER1 – ATTRIBUTE_NUMBER5 | NUMBER | 22 | Y |
| GLOBAL_ATTRIBUTE_DATE1 – GLOBAL_ATTRIBUTE_DATE5 | DATE | 7 | Y |
| ATTRIBUTE_DATE1 – ATTRIBUTE_DATE5 | DATE | 7 | Y |
| PAYMENT_CROSS_RATE_TYPE | VARCHAR2 | 120 | Y |
| PAYMENT_CROSS_RATE_DATE | DATE | 7 | Y |
| PAY_CURR_INVOICE_AMOUNT | NUMBER | 22 | Y |
| MRC_BASE_AMOUNT | VARCHAR2 | 4000 | Y |
| MRC_EXCHANGE_RATE | VARCHAR2 | 4000 | Y |
| MRC_EXCHANGE_RATE_TYPE | VARCHAR2 | 4000 | Y |
| MRC_EXCHANGE_DATE | VARCHAR2 | 4000 | Y |
| MRC_POSTING_STATUS | VARCHAR2 | 4000 | Y |
| GL_DATE | DATE | 7 | N |
| AWARD_ID | NUMBER | 22 | Y |
| PAID_ON_BEHALF_EMPLOYEE_ID | NUMBER | 22 | Y |
| AMT_DUE_CCARD_COMPANY | NUMBER | 22 | Y |
| AMT_DUE_EMPLOYEE | NUMBER | 22 | Y |
| APPROVAL_READY_FLAG | VARCHAR2 | 4 | N |
| APPROVAL_ITERATION | NUMBER | 22 | Y |
| WFAPPROVAL_STATUS | VARCHAR2 | 200 | N |
| REQUESTER_ID | NUMBER | 22 | Y |
| VALIDATION_REQUEST_ID | NUMBER | 22 | Y |
| VALIDATED_TAX_AMOUNT | NUMBER | 22 | Y |
| QUICK_CREDIT | VARCHAR2 | 4 | Y |
| CREDITED_INVOICE_ID | NUMBER | 22 | Y |
| DISTRIBUTION_SET_ID | NUMBER | 22 | Y |
| APPLICATION_ID | NUMBER | 22 | Y |
| PRODUCT_TABLE | VARCHAR2 | 120 | Y |
| REFERENCE_KEY1 – REFERENCE_KEY5 | VARCHAR2 | 600 | Y |
| TOTAL_TAX_AMOUNT | NUMBER | 22 | Y |
| SELF_ASSESSED_TAX_AMOUNT | NUMBER | 22 | Y |
| TAX_RELATED_INVOICE_ID | NUMBER | 22 | Y |
| TRX_BUSINESS_CATEGORY | VARCHAR2 | 960 | Y |
| USER_DEFINED_FISC_CLASS | VARCHAR2 | 960 | Y |
| TAXATION_COUNTRY | VARCHAR2 | 120 | Y |
| DOCUMENT_SUB_TYPE | VARCHAR2 | 960 | Y |
| SUPPLIER_TAX_INVOICE_NUMBER | VARCHAR2 | 600 | Y |
| SUPPLIER_TAX_INVOICE_DATE | DATE | 7 | Y |
| SUPPLIER_TAX_EXCHANGE_RATE | NUMBER | 22 | Y |
| TAX_INVOICE_RECORDING_DATE | DATE | 7 | Y |
| TAX_INVOICE_INTERNAL_SEQ | VARCHAR2 | 600 | Y |
| LEGAL_ENTITY_ID | NUMBER | 22 | Y |
| HISTORICAL_FLAG | VARCHAR2 | 4 | Y |
| FORCE_REVALIDATION_FLAG | VARCHAR2 | 4 | Y |
| BANK_CHARGE_BEARER | VARCHAR2 | 120 | Y |
| REMITTANCE_MESSAGE1 | VARCHAR2 | 600 | Y |
| REMITTANCE_MESSAGE2 | VARCHAR2 | 600 | Y |
| REMITTANCE_MESSAGE3 | VARCHAR2 | 600 | Y |
| UNIQUE_REMITTANCE_IDENTIFIER | VARCHAR2 | 1024 | Y |
| URI_CHECK_DIGIT | VARCHAR2 | 8 | Y |
| SETTLEMENT_PRIORITY | VARCHAR2 | 120 | Y |
| PAYMENT_REASON_CODE | VARCHAR2 | 120 | Y |
| PAYMENT_REASON_COMMENTS | VARCHAR2 | 960 | Y |
| PAYMENT_METHOD_CODE | VARCHAR2 | 120 | Y |
| DELIVERY_CHANNEL_CODE | VARCHAR2 | 120 | Y |
| QUICK_PO_HEADER_ID | NUMBER | 22 | Y |
| NET_OF_RETAINAGE_FLAG | VARCHAR2 | 4 | Y |
| RELEASE_AMOUNT_NET_OF_TAX | NUMBER | 22 | Y |
| CONTROL_AMOUNT | NUMBER | 22 | Y |
| PARTY_ID | NUMBER | 22 | Y |
| PARTY_SITE_ID | NUMBER | 22 | Y |
| PAY_PROC_TRXN_TYPE_CODE | VARCHAR2 | 120 | Y |
| PAYMENT_FUNCTION | VARCHAR2 | 120 | Y |
| CUST_REGISTRATION_CODE | VARCHAR2 | 200 | Y |
| CUST_REGISTRATION_NUMBER | VARCHAR2 | 120 | Y |
| PORT_OF_ENTRY_CODE | VARCHAR2 | 120 | Y |
| EXTERNAL_BANK_ACCOUNT_ID | NUMBER | 22 | Y |
| VENDOR_CONTACT_ID | NUMBER | 22 | Y |
| INTERNAL_CONTACT_EMAIL | VARCHAR2 | 4000 | Y |
| DISC_IS_INV_LESS_TAX_FLAG | VARCHAR2 | 4 | Y |
| EXCLUDE_FREIGHT_FROM_DISCOUNT | VARCHAR2 | 4 | Y |
| OBJECT_VERSION_NUMBER | NUMBER | 22 | N |
| EXCHANGE_RATE_TYPE | VARCHAR2 | 120 | Y |
| EXCHANGE_RATE | NUMBER | 22 | Y |
| EXCHANGE_DATE | DATE | 7 | Y |
| EMPLOYEE_ADDRESS_CODE | VARCHAR2 | 60 | Y |
| INTERCOMPANY_FLAG | VARCHAR2 | 4 | Y |
| IMPORT_DOCUMENT_NUMBER | VARCHAR2 | 200 | Y |
| IMPORT_DOCUMENT_DATE | DATE | 7 | Y |
| CORRECTION_YEAR | NUMBER | 22 | Y |
| CORRECTION_PERIOD | VARCHAR2 | 8 | Y |
| CHECK_VAT_AMOUNT_PAID | VARCHAR2 | 4 | Y |
| REQUEST_ID | NUMBER | 22 | Y |
| JOB_DEFINITION_NAME | VARCHAR2 | 400 | Y |
| JOB_DEFINITION_PACKAGE | VARCHAR2 | 3600 | Y |
| TRANSACTION_DEADLINE | NUMBER | 22 | Y |
| MERGE_REQUEST_ID | NUMBER | 22 | Y |
| BUDGET_DATE | DATE | 7 | Y |
| DATA_SET_ID | NUMBER | 22 | Y |
| FUNDS_STATUS | VARCHAR2 | 120 | Y |
| FIRST_PARTY_REGISTRATION_ID | NUMBER | 22 | Y |
| THIRD_PARTY_REGISTRATION_ID | NUMBER | 22 | Y |
| ROUTING_ATTRIBUTE1 | VARCHAR2 | 1080 | Y |
| ROUTING_ATTRIBUTE2 | VARCHAR2 | 1080 | Y |
| ROUTING_ATTRIBUTE3 | VARCHAR2 | 1080 | Y |
| ROUTING_ATTRIBUTE4 | VARCHAR2 | 1080 | Y |
| ROUTING_ATTRIBUTE5 | VARCHAR2 | 1080 | Y |
| IMAGE_DOCUMENT_NUM | VARCHAR2 | 200 | Y |
| FISCAL_DOC_ACCESS_KEY | VARCHAR2 | 200 | Y |
| REMIT_TO_SUPPLIER_NAME | VARCHAR2 | 960 | Y |
| REMIT_TO_SUPPLIER_ID | NUMBER | 22 | Y |
| REMIT_TO_ADDRESS_NAME | VARCHAR2 | 960 | Y |
| REMIT_TO_ADDRESS_ID | NUMBER | 22 | Y |
| RELATIONSHIP_ID | NUMBER | 22 | Y |
| PO_MATCHED_FLAG | VARCHAR2 | 4 | Y |
| VALIDATION_WORKER_ID | NUMBER | 22 | Y |
| LOCKED_BY | VARCHAR2 | 256 | Y |
| LOCK_TIME | TIMESTAMP(6) | 11 | Y |
| ROUTING_STATUS_LOOKUP_CODE | VARCHAR2 | 120 | Y |
Indexes
| INDEX NAME | INDEX TYPE | UNIQUENESS | COLUMN NAME |
| AP_INVOICES_ALL_U1 | NORMAL | UNIQUE | INVOICE_ID |
| AP_INVOICES_ALL_U2 | NORMAL | UNIQUE | DOC_SEQUENCE_ID |
| AP_INVOICES_ALL_U2 | NORMAL | UNIQUE | DOC_SEQUENCE_VALUE |
| AP_INVOICES_ALL_U3 | NORMAL | UNIQUE | VENDOR_ID |
| AP_INVOICES_ALL_U3 | NORMAL | UNIQUE | INVOICE_NUM |
| AP_INVOICES_ALL_U3 | NORMAL | UNIQUE | ORG_ID |
| AP_INVOICES_ALL_U3 | NORMAL | UNIQUE | PARTY_ID |
| AP_INVOICES_N1 | NORMAL | NONUNIQUE | BATCH_ID |
| AP_INVOICES_N10 | NORMAL | NONUNIQUE | PO_HEADER_ID |
| AP_INVOICES_N11 | NORMAL | NONUNIQUE | PROJECT_ID |
| AP_INVOICES_N11 | NORMAL | NONUNIQUE | TASK_ID |
| AP_INVOICES_N12 | NORMAL | NONUNIQUE | VOUCHER_NUM |
| AP_INVOICES_N16 | NORMAL | NONUNIQUE | WFAPPROVAL_STATUS |
| AP_INVOICES_N17 | NORMAL | NONUNIQUE | VALIDATION_REQUEST_ID |
| AP_INVOICES_N2 | NORMAL | NONUNIQUE | VENDOR_ID |
| AP_INVOICES_N2 | NORMAL | NONUNIQUE | INVOICE_TYPE_LOOKUP_CODE |
| AP_INVOICES_N21 | FUNCTION-BASED NORMAL | NONUNIQUE | SYS_NC00231$ |
| AP_INVOICES_N21 | FUNCTION-BASED NORMAL | NONUNIQUE | INVOICE_TYPE_LOOKUP_CODE |
| AP_INVOICES_N22 | NORMAL | NONUNIQUE | PARTY_ID |
| AP_INVOICES_N23 | NORMAL | NONUNIQUE | SOURCE |
| AP_INVOICES_N23 | NORMAL | NONUNIQUE | ORG_ID |
| AP_INVOICES_N23 | NORMAL | NONUNIQUE | ROUTING_ATTRIBUTE5 |
| AP_INVOICES_N24 | NORMAL | NONUNIQUE | ORG_ID |
| AP_INVOICES_N24 | NORMAL | NONUNIQUE | APPROVAL_READY_FLAG |
| AP_INVOICES_N24 | NORMAL | NONUNIQUE | VALIDATION_REQUEST_ID |
| AP_INVOICES_N24 | NORMAL | NONUNIQUE | PAYMENT_STATUS_FLAG |
| AP_INVOICES_N24 | NORMAL | NONUNIQUE | HISTORICAL_FLAG |
| AP_INVOICES_N25 | NORMAL | NONUNIQUE | INVOICE_NUM |
| AP_INVOICES_N25 | NORMAL | NONUNIQUE | PARTY_ID |
| AP_INVOICES_N25 | NORMAL | NONUNIQUE | PAYMENT_STATUS_FLAG |
| AP_INVOICES_N27 | NORMAL | NONUNIQUE | DATA_SET_ID |
| AP_INVOICES_N28 | NORMAL | NONUNIQUE | LAST_UPDATE_DATE |
| AP_INVOICES_N28 | NORMAL | NONUNIQUE | ORG_ID |
| AP_INVOICES_N3 | NORMAL | NONUNIQUE | PAYMENT_STATUS_FLAG |
| AP_INVOICES_N3 | NORMAL | NONUNIQUE | PAY_GROUP_LOOKUP_CODE |
| AP_INVOICES_N3 | NORMAL | NONUNIQUE | ORG_ID |
| AP_INVOICES_N5 | NORMAL | NONUNIQUE | INVOICE_DATE |
| AP_INVOICES_N5 | NORMAL | NONUNIQUE | ORG_ID |
| AP_INVOICES_N7 | NORMAL | NONUNIQUE | VENDOR_SITE_ID |
| AP_INVOICES_N8 | NORMAL | NONUNIQUE | CREATION_DATE |
Related Posts:
How To Fetch (Query) The Payment Details In Fusion
Query to fetch the details of a Payment Process Request in Fusion
Accounting Detail Query for AP Invoice
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com