indexes, Oracle Fusion, Table Structure

AP_INVOICE_LINES_ALL: Columns, Index & Salient Points

Mohit Saxena

AP_INVOICE_LINES_ALL is one of the most important table in P2P Cycle. The table AP_INVOICE_LINES_ALL used by Oracle Fusion to store the line level data of AP invoices. if you are looking to connect the AP invoice to any entity like PO/ Project etc, most probably this is your table like you can use PO_DISTRIBUTION_ID to connect AP invoice to PO Distribution. similarly Project and task id can be used to find the task info which has caused the invoice line to be created. So let’s see the table structure and the indexes:

(By the way, I was helped by this book a great deal, just in case you want to refer. No no.. I am neither the author nor the publisher of this book. )

AP_INVOICE_LINES_ALL: Table Structure

COLUMN_NAMEDATA_TYPENULLABLE
 INVOICE_IDNUMBERN 
 LINE_NUMBERNUMBERN 
 LINE_TYPE_LOOKUP_CODEVARCHAR2(100)N 
 REQUESTER_IDNUMBERY 
 DESCRIPTIONVARCHAR2(960)Y 
 LINE_SOURCEVARCHAR2(120)Y 
 ORG_IDNUMBERN 
 LINE_GROUP_NUMBERNUMBERY 
 INVENTORY_ITEM_IDNUMBERY 
 ITEM_DESCRIPTIONVARCHAR2(960)Y 
 SERIAL_NUMBERVARCHAR2(140)Y 
 MANUFACTURERVARCHAR2(120)Y 
 MODEL_NUMBERVARCHAR2(160)Y 
 WARRANTY_NUMBERVARCHAR2(60)Y 
 GENERATE_DISTSVARCHAR2(4)Y 
 MATCH_TYPEVARCHAR2(100)Y 
 DISTRIBUTION_SET_IDNUMBERY 
 ACCOUNT_SEGMENTVARCHAR2(100)Y 
 BALANCING_SEGMENTVARCHAR2(100)Y 
 COST_CENTER_SEGMENTVARCHAR2(100)Y 
 OVERLAY_DIST_CODE_CONCATVARCHAR2(1000)Y 
 DEFAULT_DIST_CCIDNUMBERY 
 PRORATE_ACROSS_ALL_ITEMSVARCHAR2(4)Y 
 ACCOUNTING_DATEDATEN 
 PERIOD_NAMEVARCHAR2(60)Y 
 DEFERRED_ACCTG_FLAGVARCHAR2(4)Y 
 DEF_ACCTG_START_DATEDATEY 
 DEF_ACCTG_END_DATEDATEY 
 DEF_ACCTG_NUMBER_OF_PERIODSNUMBERY 
 DEF_ACCTG_PERIOD_TYPEVARCHAR2(120)Y 
 SET_OF_BOOKS_IDNUMBERN 
 AMOUNTNUMBERN 
 BASE_AMOUNTNUMBERY 
 ROUNDING_AMTNUMBERY 
 QUANTITY_INVOICEDNUMBERY 
 UNIT_MEAS_LOOKUP_CODEVARCHAR2(100)Y 
 UNIT_PRICENUMBERY 
 WFAPPROVAL_STATUSVARCHAR2(120)N 
 USSGL_TRANSACTION_CODEVARCHAR2(120)Y 
 DISCARDED_FLAGVARCHAR2(4)Y 
 ORIGINAL_AMOUNTNUMBERY 
 ORIGINAL_BASE_AMOUNTNUMBERY 
 ORIGINAL_ROUNDING_AMTNUMBERY 
 CANCELLED_FLAGVARCHAR2(4)Y 
 INCOME_TAX_REGIONVARCHAR2(40)Y 
 TYPE_1099VARCHAR2(40)Y 
 STAT_AMOUNTNUMBERY 
 PREPAY_INVOICE_IDNUMBERY 
 PREPAY_LINE_NUMBERNUMBERY 
 INVOICE_INCLUDES_PREPAY_FLAGVARCHAR2(4)Y 
 CORRECTED_INV_IDNUMBERY 
 CORRECTED_LINE_NUMBERNUMBERY 
 PO_HEADER_IDNUMBERY 
 PO_LINE_IDNUMBERY 
 PO_RELEASE_IDNUMBERY 
 PO_LINE_LOCATION_IDNUMBERY 
 PO_DISTRIBUTION_IDNUMBERY 
 RCV_TRANSACTION_IDNUMBERY 
 FINAL_MATCH_FLAGVARCHAR2(4)Y 
 ASSETS_TRACKING_FLAGVARCHAR2(4)Y 
 ASSET_BOOK_TYPE_CODEVARCHAR2(120)Y 
 ASSET_CATEGORY_IDNUMBERY 
 PROJECT_IDNUMBERY 
 TASK_IDNUMBERY 
 EXPENDITURE_TYPEVARCHAR2(120)Y 
 EXPENDITURE_ITEM_DATEDATEY 
 EXPENDITURE_ORGANIZATION_IDNUMBERY 
 PA_QUANTITYNUMBERY 
 PA_CC_AR_INVOICE_IDNUMBERY 
 PA_CC_AR_INVOICE_LINE_NUMNUMBERY 
 PA_CC_PROCESSED_CODEVARCHAR2(4)Y 
 AWARD_IDNUMBERY 
 AWT_GROUP_IDNUMBERY 
 REFERENCE_1VARCHAR2(120)Y 
 REFERENCE_2VARCHAR2(120)Y 
 RECEIPT_VERIFIED_FLAGVARCHAR2(4)Y 
 RECEIPT_REQUIRED_FLAGVARCHAR2(4)Y 
 RECEIPT_MISSING_FLAGVARCHAR2(4)Y 
 JUSTIFICATIONVARCHAR2(960)Y 
 EXPENSE_GROUPVARCHAR2(320)Y 
 START_EXPENSE_DATEDATEY 
 END_EXPENSE_DATEDATEY 
 RECEIPT_CURRENCY_CODEVARCHAR2(60)Y 
 RECEIPT_CONVERSION_RATENUMBERY 
 RECEIPT_CURRENCY_AMOUNTNUMBERY 
 DAILY_AMOUNTNUMBERY 
 WEB_PARAMETER_IDNUMBERY 
 ADJUSTMENT_REASONVARCHAR2(960)Y 
 MERCHANT_DOCUMENT_NUMBERVARCHAR2(320)Y 
 MERCHANT_NAMEVARCHAR2(320)Y 
 MERCHANT_REFERENCEVARCHAR2(960)Y 
 MERCHANT_TAX_REG_NUMBERVARCHAR2(320)Y 
 MERCHANT_TAXPAYER_IDVARCHAR2(320)Y 
 COUNTRY_OF_SUPPLYVARCHAR2(20)Y 
 CREDIT_CARD_TRX_IDNUMBERY 
 COMPANY_PREPAID_INVOICE_IDNUMBERY 
 CC_REVERSAL_FLAGVARCHAR2(4)Y 
 CREATION_DATETIMESTAMP(6)N 
 CREATED_BYVARCHAR2(256)N 
 LAST_UPDATED_BYVARCHAR2(256)N 
 LAST_UPDATE_DATETIMESTAMP(6)N 
 LAST_UPDATE_LOGINVARCHAR2(128)Y 
 PROGRAM_APPLICATION_IDNUMBERY 
 PROGRAM_IDNUMBERY 
 PROGRAM_UPDATE_DATEDATEY 
 REQUEST_IDNUMBERY 
 ATTRIBUTE_CATEGORYVARCHAR2(600)Y 
 ATTRIBUTE1VARCHAR2(600)Y 
 ATTRIBUTE2VARCHAR2(600)Y 
 ATTRIBUTE3VARCHAR2(600)Y 
 ATTRIBUTE4VARCHAR2(600)Y 
 ATTRIBUTE5VARCHAR2(600)Y 
 ATTRIBUTE6VARCHAR2(600)Y 
 ATTRIBUTE7VARCHAR2(600)Y 
 ATTRIBUTE8VARCHAR2(600)Y 
 ATTRIBUTE9VARCHAR2(600)Y 
 ATTRIBUTE10VARCHAR2(600)Y 
 ATTRIBUTE11VARCHAR2(600)Y 
 ATTRIBUTE12VARCHAR2(600)Y 
 ATTRIBUTE13VARCHAR2(600)Y 
 ATTRIBUTE14VARCHAR2(600)Y 
 ATTRIBUTE15VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE_CATEGORYVARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE1VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE2VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE3VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE4VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE5VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE6VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE7VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE8VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE9VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE10VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE11VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE12VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE13VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE14VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE15VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE16VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE17VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE18VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE19VARCHAR2(600)Y 
 GLOBAL_ATTRIBUTE20VARCHAR2(600)Y 
 LINE_SELECTED_FOR_APPL_FLAGVARCHAR2(4)Y 
 PREPAY_APPL_REQUEST_IDNUMBERY 
 APPLICATION_IDNUMBERY 
 PRODUCT_TABLEVARCHAR2(120)Y 
 REFERENCE_KEY1VARCHAR2(600)Y 
 REFERENCE_KEY2VARCHAR2(600)Y 
 REFERENCE_KEY3VARCHAR2(600)Y 
 REFERENCE_KEY4VARCHAR2(600)Y 
 REFERENCE_KEY5VARCHAR2(600)Y 
 PURCHASING_CATEGORY_IDNUMBERY 
 COST_FACTOR_IDNUMBERY 
 CONTROL_AMOUNTNUMBERY 
 ASSESSABLE_VALUENUMBERY 
 TOTAL_REC_TAX_AMOUNTNUMBERY 
 TOTAL_NREC_TAX_AMOUNTNUMBERY 
 TOTAL_REC_TAX_AMT_FUNCL_CURRNUMBERY 
 TOTAL_NREC_TAX_AMT_FUNCL_CURRNUMBERY 
 INCLUDED_TAX_AMOUNTNUMBERY 
 PRIMARY_INTENDED_USEVARCHAR2(120)Y 
 INTENDED_USE_CLASSIF_IDNUMBERY 
 TAX_ALREADY_CALCULATED_FLAGVARCHAR2(4)Y 
 SHIP_TO_LOCATION_IDNUMBERY 
 PRODUCT_TYPEVARCHAR2(960)Y 
 PRODUCT_CATEGORYVARCHAR2(960)Y 
 PRODUCT_FISC_CLASSIFICATIONVARCHAR2(960)Y 
 PROD_FC_CATEG_IDNUMBERY 
 USER_DEFINED_FISC_CLASSVARCHAR2(960)Y 
 TRX_BUSINESS_CATEGORYVARCHAR2(960)Y 
 SUMMARY_TAX_LINE_IDNUMBERY 
 TAX_REGIME_CODEVARCHAR2(120)Y 
 TAXVARCHAR2(120)Y 
 TAX_JURISDICTION_CODEVARCHAR2(120)Y 
 TAX_STATUS_CODEVARCHAR2(120)Y 
 TAX_RATE_IDNUMBERY 
 TAX_RATE_CODEVARCHAR2(600)Y 
 TAX_RATENUMBERY 
 TAX_CODE_IDNUMBERY 
 HISTORICAL_FLAGVARCHAR2(4)Y 
 TAX_CLASSIFICATION_CODEVARCHAR2(120)Y 
 SOURCE_APPLICATION_IDNUMBERY 
 SOURCE_EVENT_CLASS_CODEVARCHAR2(120)Y 
 SOURCE_ENTITY_CODEVARCHAR2(120)Y 
 SOURCE_TRX_IDNUMBERY 
 SOURCE_LINE_IDNUMBERY 
 SOURCE_TRX_LEVEL_TYPEVARCHAR2(120)Y 
 RETAINED_AMOUNTNUMBERY 
 RETAINED_AMOUNT_REMAININGNUMBERY 
 RETAINED_INVOICE_IDNUMBERY 
 RETAINED_LINE_NUMBERNUMBERY 
 LINE_SELECTED_FOR_RELEASE_FLAGVARCHAR2(4)Y 
 LINE_OWNER_ROLEVARCHAR2(1280)Y 
 DISPUTABLE_FLAGVARCHAR2(4)Y 
 RCV_SHIPMENT_LINE_IDNUMBERY 
 PJC_CONTEXT_CATEGORYVARCHAR2(160)Y 
 PJC_PROJECT_IDNUMBERY 
 PJC_TASK_IDNUMBERY 
 PJC_EXPENDITURE_TYPE_IDNUMBERY 
 PJC_EXPENDITURE_ITEM_DATEDATEY 
 PJC_ORGANIZATION_IDNUMBERY 
 PJC_BILLABLE_FLAGVARCHAR2(4)Y 
 PJC_CAPITALIZABLE_FLAGVARCHAR2(4)Y 
 PJC_WORK_TYPE_IDNUMBERY 
 PJC_CONTRACT_IDNUMBERY 
 PJC_CONTRACT_LINE_IDNUMBERY 
 PJC_FUNDING_ALLOCATION_IDNUMBERY 
 PJC_RESERVED_ATTRIBUTE1VARCHAR2(600)Y 
 PJC_RESERVED_ATTRIBUTE2VARCHAR2(600)Y 
 PJC_RESERVED_ATTRIBUTE3VARCHAR2(600)Y 
 PJC_RESERVED_ATTRIBUTE4VARCHAR2(600)Y 
 PJC_RESERVED_ATTRIBUTE5VARCHAR2(600)Y 
 PJC_RESERVED_ATTRIBUTE6VARCHAR2(600)Y 
 PJC_RESERVED_ATTRIBUTE7VARCHAR2(600)Y 
 PJC_RESERVED_ATTRIBUTE8VARCHAR2(600)Y 
 PJC_RESERVED_ATTRIBUTE9VARCHAR2(600)Y 
 PJC_RESERVED_ATTRIBUTE10VARCHAR2(600)Y 
 PJC_USER_DEF_ATTRIBUTE1VARCHAR2(600)Y 
 PJC_USER_DEF_ATTRIBUTE2VARCHAR2(600)Y 
 PJC_USER_DEF_ATTRIBUTE3VARCHAR2(600)Y 
 PJC_USER_DEF_ATTRIBUTE4VARCHAR2(600)Y 
 PJC_USER_DEF_ATTRIBUTE5VARCHAR2(600)Y 
 PJC_USER_DEF_ATTRIBUTE6VARCHAR2(600)Y 
 PJC_USER_DEF_ATTRIBUTE7VARCHAR2(600)Y 
 PJC_USER_DEF_ATTRIBUTE8VARCHAR2(600)Y 
 PJC_USER_DEF_ATTRIBUTE9VARCHAR2(600)Y 
 PJC_USER_DEF_ATTRIBUTE10VARCHAR2(600)Y 
 ATTRIBUTE_NUMBER1NUMBERY 
 ATTRIBUTE_NUMBER2NUMBERY 
 ATTRIBUTE_NUMBER3NUMBERY 
 ATTRIBUTE_NUMBER4NUMBERY 
 ATTRIBUTE_NUMBER5NUMBERY 
 ATTRIBUTE_DATE1DATEY 
 ATTRIBUTE_DATE2DATEY 
 ATTRIBUTE_DATE3DATEY 
 ATTRIBUTE_DATE4DATEY 
 ATTRIBUTE_DATE5DATEY 
 GLOBAL_ATTRIBUTE_NUMBER1NUMBERY 
 GLOBAL_ATTRIBUTE_NUMBER2NUMBERY 
 GLOBAL_ATTRIBUTE_NUMBER3NUMBERY 
 GLOBAL_ATTRIBUTE_NUMBER4NUMBERY 
 GLOBAL_ATTRIBUTE_NUMBER5NUMBERY 
 GLOBAL_ATTRIBUTE_DATE1DATEY 
 GLOBAL_ATTRIBUTE_DATE2DATEY 
 GLOBAL_ATTRIBUTE_DATE3DATEY 
 GLOBAL_ATTRIBUTE_DATE4DATEY 
 GLOBAL_ATTRIBUTE_DATE5DATEY 
 OBJECT_VERSION_NUMBERNUMBERN 
 JOB_DEFINITION_NAMEVARCHAR2(400)Y 
 JOB_DEFINITION_PACKAGEVARCHAR2(3600)Y 
 BUDGET_DATEDATEY 
 FUNDS_STATUSVARCHAR2(120)Y 
 LCM_ENABLED_FLAGVARCHAR2(4)Y 
 CONSUMPTION_ADVICE_HEADER_IDNUMBERY 
 CONSUMPTION_ADVICE_LINE_IDNUMBERY 
 SHIP_FROM_LOCATION_IDNUMBERY 
 FINAL_DISCHARGE_LOCATION_IDNUMBERY 
 FOS_XFACE_FLAGVARCHAR2(4)Y 
 MATCHING_RULE_SET_IDNUMBERY 
 MATCHING_RULE_IDNUMBERY 
 FISCAL_CHARGE_TYPEVARCHAR2(120)Y 
 SHIP_TO_CUST_LOCATION_IDNUMBERY 
 DEF_ACCTG_ACCRUAL_CCIDNUMBERY 
AP_INVOICE_LINES_ALL Table Structure

Indexes

INDEX_NAMEINDEX_TYPEUNIQUENESSCOLUMN NAME
 AP_INVOICE_LINES_N1NORMALNONUNIQUEACCOUNTING_DATE
 AP_INVOICE_LINES_N10NORMALNONUNIQUELINE_SELECTED_FOR_APPL_FLAG
 AP_INVOICE_LINES_N12NORMALNONUNIQUELINE_TYPE_LOOKUP_CODE
 AP_INVOICE_LINES_N13NORMALNONUNIQUESET_OF_BOOKS_ID
 AP_INVOICE_LINES_N13NORMALNONUNIQUEORG_ID
 AP_INVOICE_LINES_N13NORMALNONUNIQUEACCOUNTING_DATE
 AP_INVOICE_LINES_N13NORMALNONUNIQUEAMOUNT
 AP_INVOICE_LINES_N13NORMALNONUNIQUEDISCARDED_FLAG
 AP_INVOICE_LINES_N14NORMALNONUNIQUEINVOICE_ID
 AP_INVOICE_LINES_N14NORMALNONUNIQUESUMMARY_TAX_LINE_ID
 AP_INVOICE_LINES_N2NORMALNONUNIQUERCV_TRANSACTION_ID
 AP_INVOICE_LINES_N3NORMALNONUNIQUEPO_LINE_LOCATION_ID
 AP_INVOICE_LINES_N4NORMALNONUNIQUEPJC_PROJECT_ID
 AP_INVOICE_LINES_N4NORMALNONUNIQUEPJC_TASK_ID
 AP_INVOICE_LINES_N6NORMALNONUNIQUECORRECTED_INV_ID
 AP_INVOICE_LINES_N8NORMALNONUNIQUEPREPAY_INVOICE_ID
 AP_INVOICE_LINES_N8NORMALNONUNIQUEPREPAY_LINE_NUMBER
 AP_INVOICE_LINES_N9NORMALNONUNIQUEPO_HEADER_ID
 AP_INVOICE_LINES_U1NORMALUNIQUEINVOICE_ID
 AP_INVOICE_LINES_U1NORMALUNIQUELINE_NUMBER
AP_INVOICE_LINES_ALL Indexes

Related Posts

AP_INVOICES_ALL
AP Payment Query

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

From: Mohit Saxena
Author of “Oracle Fusion: Unconventional Ways to Enhance the Performance of BI Reports“

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

1 thought on “AP_INVOICE_LINES_ALL: Columns, Index & Salient Points”

  1. Pingback: AP_INVOICE_DISTRIBUTIONS_ALL: Table Details - Oracle Bytes

Comments are closed.