View: AP_INVOICE_LINES_V

Product:	AP - Payables
Description:
Implementation/DBA Data:	APPS.AP_INVOICE_LINES_V

View Text

SELECT AIL.ROWID ROW_ID
, AIL.INVOICE_ID INVOICE_ID
, AIL.LINE_NUMBER LINE_NUMBER
, AIL.LINE_TYPE_LOOKUP_CODE LINE_TYPE_LOOKUP_CODE
, ALC1.DISPLAYED_FIELD LINE_TYPE
, AIL.REQUESTER_ID REQUESTER_ID
, AIL.DESCRIPTION DESCRIPTION
, AIL.LINE_SOURCE LINE_SOURCE
, AIL.ORG_ID ORG_ID
, AIL.LINE_GROUP_NUMBER LINE_GROUP_NUMBER
, AIL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, AIL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, AIL.SERIAL_NUMBER SERIAL_NUMBER
, AIL.MANUFACTURER MANUFACTURER
, AIL.MODEL_NUMBER MODEL_NUMBER
, AIL.WARRANTY_NUMBER WARRANTY_NUMBER
, AIL.GENERATE_DISTS GENERATE_DISTRIBUTIONS
, AIL.MATCH_TYPE MATCH_TYPE
, AIL.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID
, APID.DISTRIBUTION_SET_NAME DISTRIBUTION_SET_NAME
, AIL.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AIL.BALANCING_SEGMENT BALANCING_SEGMENT
, AIL.COST_CENTER_SEGMENT COST_CENTER_SEGMENT
, AIL.OVERLAY_DIST_CODE_CONCAT OVERLAY_DIST_CODE_CONCAT
, AIL.DEFAULT_DIST_CCID DEFAULT_DIST_CCID
, AIL.PRORATE_ACROSS_ALL_ITEMS PRORATE_ACROSS_ALL_ITEMS
, AIL.ACCOUNTING_DATE GL_DATE
, AIL.PERIOD_NAME PERIOD_NAME
, AIL.DEFERRED_ACCTG_FLAG DEFERRED_ACCTG_FLAG
, AIL.DEF_ACCTG_START_DATE DEFERRED_START_DATE
, AIL.DEF_ACCTG_END_DATE DEFERRED_END_DATE
, AIL.DEF_ACCTG_NUMBER_OF_PERIODS DEFERRED_NUMBER_OF_PERIODS
, AIL.DEF_ACCTG_PERIOD_TYPE DEFERRED_PERIOD_TYPE
, AIL.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AIL.AMOUNT AMOUNT
, AIL.BASE_AMOUNT BASE_AMOUNT
, AIL.ROUNDING_AMT ROUNDING_AMOUNT
, AIL.QUANTITY_INVOICED QUANTITY_INVOICED
, AIL.UNIT_MEAS_LOOKUP_CODE UNIT_MEAS_LOOKUP_CODE
, AIL.UNIT_PRICE UNIT_PRICE
, AIL.WFAPPROVAL_STATUS WFAPPROVAL_STATUS_LOOKUP_CODE
, ALC2.DISPLAYED_FIELD WFAPPROVAL_STATUS_DISP
, AIL.DISCARDED_FLAG DISCARDED_FLAG
, AIL.ORIGINAL_AMOUNT ORIGINAL_AMOUNT
, AIL.ORIGINAL_BASE_AMOUNT ORIGINAL_BASE_AMOUNT
, AIL.ORIGINAL_ROUNDING_AMT ORIGINAL_ROUNDING_AMOUNT
, AIL.CANCELLED_FLAG CANCELLED_FLAG
, AIL.INCOME_TAX_REGION INCOME_TAX_REGION
, AIL.TYPE_1099 INCOME_TAX_TYPE
, AIL.STAT_AMOUNT STAT_AMOUNT
, AIL.PREPAY_INVOICE_ID PREPAY_INVOICE_ID
, AIP.INVOICE_NUM PREPAY_INVOICE_NUM
, AIL.PREPAY_LINE_NUMBER PREPAY_LINE_NUMBER
, AIL.INVOICE_INCLUDES_PREPAY_FLAG INVOICE_INCLUDES_PREPAY_FLAG
, AIL.CORRECTED_INV_ID CORRECTED_INV_ID
, AIC.INVOICE_NUM CORRECTED_INVOICE_NUM
, AIL.CORRECTED_LINE_NUMBER CORRECTED_LINE_NUMBER
, AIL.PO_HEADER_ID PO_HEADER_ID
, PH.SEGMENT1 PO_NUMBER
, AIL.PO_LINE_ID PO_LINE_ID
, PL.LINE_NUM PO_LINE_NUMBER
, AIL.PO_RELEASE_ID PO_RELEASE_ID
, PR.RELEASE_NUM PO_RELEASE_NUMBER
, AIL.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID
, PLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER
, ALC3.DISPLAYED_FIELD MATCHING_BASIS /* AMOUNT BASED MATCHING */
, PLL.MATCHING_BASIS MATCHING_BASIS_LOOKUP_CODE /* AMOUNT BASED MATCHING */
, AIL.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID
, PD.DISTRIBUTION_NUM PO_DISTRIBUTION_NUMBER
, AIL.RCV_TRANSACTION_ID RCV_TRANSACTION_ID
, RSH.RECEIPT_NUM RECEIPT_NUMBER
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, AIL.FINAL_MATCH_FLAG FINAL_MATCH_FLAG
, AIL.ASSETS_TRACKING_FLAG ASSETS_TRACKING_FLAG
, AIL.ASSET_BOOK_TYPE_CODE ASSET_BOOK_TYPE_CODE
, FABC.BOOK_TYPE_NAME ASSET_BOOK_NAME
, AIL.ASSET_CATEGORY_ID ASSET_CATEGORY_ID
, AIL.PROJECT_ID PROJECT_ID
, PAP.SEGMENT1 PROJECT
, AIL.TASK_ID TASK_ID
, PAT.TASK_NUMBER TASK
, AIL.EXPENDITURE_TYPE EXPENDITURE_TYPE
, AIL.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, AIL.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
, HOU.NAME EXPENDITURE_ORGANIZATION_NAME
, AIL.PA_QUANTITY PA_QUANTITY
, AIL.PA_CC_AR_INVOICE_ID PA_CC_AR_INVOICE_ID
, AIII.INVOICE_NUM INTERCOMPANY_INVOICE_NUM
, AIL.PA_CC_AR_INVOICE_LINE_NUM INTERCOMPANY_INVOICE_LINE_NUM
, AIL.PA_CC_PROCESSED_CODE PROCESSED_CODE
, AIL.AWARD_ID AWARD_ID
, AIL.AWT_GROUP_ID AWT_GROUP_ID
, AWT.NAME AWT_GROUP_NAME
, AIL.REFERENCE_1 REFERENCE_1
, AIL.REFERENCE_2 REFERENCE_2
, AIL.RECEIPT_VERIFIED_FLAG RECEIPT_VERIFIED_FLAG
, AIL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, AIL.RECEIPT_MISSING_FLAG RECEIPT_MISSING_FLAG
, AIL.JUSTIFICATION JUSTIFICATION
, AIL.EXPENSE_GROUP EXPENSE_GROUP
, AIL.START_EXPENSE_DATE START_EXPENSE_DATE
, AIL.END_EXPENSE_DATE END_EXPENSE_DATE
, AIL.RECEIPT_CURRENCY_CODE RECEIPT_CURRENCY_CODE
, AIL.RECEIPT_CONVERSION_RATE RECEIPT_CONVERSION_RATE
, AIL.RECEIPT_CURRENCY_AMOUNT RECEIPT_CURRENCY_AMOUNT
, AIL.DAILY_AMOUNT DAILY_AMOUNT
, AIL.WEB_PARAMETER_ID WEB_PARAMETER_ID
, AIL.ADJUSTMENT_REASON ADJUSTMENT_REASON
, AIL.MERCHANT_DOCUMENT_NUMBER MERCHANT_DOCUMENT_NUMBER
, AIL.MERCHANT_NAME MERCHANT_NAME
, AIL.MERCHANT_REFERENCE MERCHANT_REFERENCE
, AIL.MERCHANT_TAX_REG_NUMBER MERCHANT_TAX_REG_NUMBER
, AIL.MERCHANT_TAXPAYER_ID MERCHANT_TAXPAYER_ID
, AIL.COUNTRY_OF_SUPPLY COUNTRY_OF_SUPPLY
, AIL.CREDIT_CARD_TRX_ID CREDIT_CARD_TRX_ID
, AIL.COMPANY_PREPAID_INVOICE_ID COMPANY_PREPAID_INVOICE_ID
, AIL.CC_REVERSAL_FLAG CC_REVERSAL_FLAG
, AIL.CREATION_DATE CREATION_DATE
, AIL.CREATED_BY CREATED_BY
, AIL.LAST_UPDATED_BY LAST_UPDATED_BY
, AIL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AIL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AIL.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, AIL.PROGRAM_ID PROGRAM_ID
, AIL.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, AIL.REQUEST_ID REQUEST_ID
, AP_INVOICE_LINES_UTILITY_PKG.GET_PACKET_ID(AIL.INVOICE_ID
, AIL.LINE_NUMBER) QUERY_PACKET_ID
, NVL(AP_INVOICE_LINES_UTILITY_PKG.GET_APPROVAL_STATUS(AIL.INVOICE_ID
, AIL.LINE_NUMBER)
, 'NEVER APPROVED') VALIDATION_STATUS
, NVL(AP_INVOICE_LINES_UTILITY_PKG.GET_ENCUMBERED_FLAG(AIL.INVOICE_ID
, AIL.LINE_NUMBER)
, '') ENCUMBRANCE_FLAG
, NVL(AP_INVOICE_LINES_UTILITY_PKG.GET_POSTING_STATUS(AIL.INVOICE_ID
, AIL.LINE_NUMBER)
, 'N') ACCOUNTING_FLAG
, AIL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AIL.ATTRIBUTE1 ATTRIBUTE1
, AIL.ATTRIBUTE2 ATTRIBUTE2
, AIL.ATTRIBUTE3 ATTRIBUTE3
, AIL.ATTRIBUTE4 ATTRIBUTE4
, AIL.ATTRIBUTE5 ATTRIBUTE5
, AIL.ATTRIBUTE6 ATTRIBUTE6
, AIL.ATTRIBUTE7 ATTRIBUTE7
, AIL.ATTRIBUTE8 ATTRIBUTE8
, AIL.ATTRIBUTE9 ATTRIBUTE9
, AIL.ATTRIBUTE10 ATTRIBUTE10
, AIL.ATTRIBUTE11 ATTRIBUTE11
, AIL.ATTRIBUTE12 ATTRIBUTE12
, AIL.ATTRIBUTE13 ATTRIBUTE13
, AIL.ATTRIBUTE14 ATTRIBUTE14
, AIL.ATTRIBUTE15 ATTRIBUTE15
, AIL.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, AIL.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, AIL.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, AIL.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, AIL.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, AIL.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, AIL.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, AIL.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, AIL.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, AIL.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, AIL.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, AIL.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, AIL.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, AIL.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, AIL.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, AIL.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, AIL.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, AIL.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, AIL.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, AIL.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, AIL.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, AP_INVOICES_UTILITY_PKG.GET_MAX_DIST_LINE_NUM(AI.INVOICE_ID
, AIL.LINE_NUMBER) HIGHEST_DIST_LINE_NUM
, AIL.PRIMARY_INTENDED_USE
, AIL.SHIP_TO_LOCATION_ID
, HR.LOCATION_CODE SHIP_TO_LOCATION_CODE
, AIL.PRODUCT_FISC_CLASSIFICATION
, AIL.USER_DEFINED_FISC_CLASS
, AIL.TRX_BUSINESS_CATEGORY
, AIL.PRODUCT_TYPE
, AIL.PRODUCT_CATEGORY
, AIL.ASSESSABLE_VALUE
, AIL.CONTROL_AMOUNT
, AIL.INCLUDED_TAX_AMOUNT
, AIL.TOTAL_REC_TAX_AMOUNT
, AIL.TOTAL_NREC_TAX_AMOUNT
, AIL.TAX_REGIME_CODE
, AIL.TAX
, AIL.TAX_STATUS_CODE
, AIL.TAX_RATE_CODE
, AIL.TAX_RATE_ID
, AIL.TAX_RATE
, AIL.TAX_JURISDICTION_CODE
, AIL.SUMMARY_TAX_LINE_ID
, AIL.TAX_ALREADY_CALCULATED_FLAG
, AIL.TAX_CLASSIFICATION_CODE
, AIL.APPLICATION_ID
, AIL.PRODUCT_TABLE
, AIL.REFERENCE_KEY1
, AIL.REFERENCE_KEY2
, AIL.REFERENCE_KEY3
, AIL.REFERENCE_KEY4
, AIL.REFERENCE_KEY5
, DECODE(AIL.PO_HEADER_ID
, NULL
, AIL.PURCHASING_CATEGORY_ID
, PL.CATEGORY_ID)
, AIL.COST_FACTOR_ID
, PPET.NAME COST_FACTOR_NAME
, AP_MATCHING_UTILS_PKG.GET_INV_LINE_RECOUPED_AMOUNT(AIL.INVOICE_ID
, AIL.LINE_NUMBER) RECOUPED_AMOUNT
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, NULL
, PL.RECOUPMENT_RATE) RECOUPMENT_RATE
, AIL.RETAINED_AMOUNT RETAINED_AMOUNT
, AIL.RETAINED_AMOUNT_REMAINING RETAINED_AMOUNT_REMAINING
, AIL.RETAINED_INVOICE_ID RETAINED_INVOICE_ID
, AIR.INVOICE_NUM RETAINED_INVOICE_NUM
, AIL.RETAINED_LINE_NUMBER RETAINED_LINE_NUMBER
, AIL.LINE_SELECTED_FOR_RELEASE_FLAG LINE_SELECTED_FOR_RELEASE_FLAG
, AIL.RCV_SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID
, AIL.PAY_AWT_GROUP_ID PAY_AWT_GROUP_ID
, AWT1.NAME PAY_AWT_GROUP_NAME
FROM AP_INVOICE_LINES_ALL AIL
, AP_INVOICES_ALL AI
, GL_SETS_OF_BOOKS GSOB
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2
, AP_DISTRIBUTION_SETS_ALL APID
, PO_DISTRIBUTIONS_ALL PD
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, AP_LOOKUP_CODES ALC3 /* AMOUNT BASED MATCHING */
, PO_LINE_LOCATIONS_ALL PLL
, PO_RELEASES_ALL PR
, RCV_TRANSACTIONS RTXNS
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, HR_ORGANIZATION_UNITS HOU
, PA_PROJECTS_ALL PAP
, PA_TASKS PAT
, FA_BOOK_CONTROLS FABC
, AP_AWT_GROUPS AWT
, AP_AWT_GROUPS AWT1
, AP_INVOICES_ALL AIC /*FOR CORRECTED INVOICE NUMBER*/
, AP_INVOICES_ALL AIII /*FOR INTRECOMPANY INVOICE NUMBER*/
, AP_INVOICES_ALL AIP /*FOR PREPAYMENT INVOICE NUMBER*/
, AP_INVOICES_ALL AIR /*FOR RETAINED INVOICE NUMBER*/
, HR_LOCATIONS HR
, PON_PRICE_ELEMENT_TYPES_VL PPET
WHERE AIL.INVOICE_ID = AI.INVOICE_ID
AND AIL.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND ALC1.LOOKUP_TYPE (+) = 'INVOICE LINE TYPE'
AND ALC1.LOOKUP_CODE (+) = AIL.LINE_TYPE_LOOKUP_CODE
AND ALC2.LOOKUP_TYPE (+) = 'AP_WFAPPROVAL_STATUS'
AND ALC2.LOOKUP_CODE (+) = AIL.WFAPPROVAL_STATUS
AND AIL.DISTRIBUTION_SET_ID = APID.DISTRIBUTION_SET_ID(+)
AND AIL.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID(+)
AND AIL.PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND AIL.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+)
AND AIL.PO_LINE_ID = PL.PO_LINE_ID(+)
AND ALC3.LOOKUP_TYPE(+) = 'MATCHING BASIS' /* AMOUNT BASED MATCHING */
AND ALC3.LOOKUP_CODE(+) = PLL.MATCHING_BASIS /* AMOUNT BASED MATCHING */
AND AIL.PO_RELEASE_ID = PR.PO_RELEASE_ID(+)
AND AIL.RCV_TRANSACTION_ID = RTXNS.TRANSACTION_ID(+)
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)
AND RSL.SHIPMENT_LINE_ID(+) = AIL.RCV_SHIPMENT_LINE_ID
AND AIL.PROJECT_ID = PAP.PROJECT_ID(+)
AND AIL.TASK_ID = PAT.TASK_ID(+)
AND AIL.EXPENDITURE_ORGANIZATION_ID = HOU.ORGANIZATION_ID(+)
AND AIL.ASSET_BOOK_TYPE_CODE = FABC.BOOK_TYPE_CODE(+)
AND AIL.AWT_GROUP_ID = AWT.GROUP_ID(+)
AND AIL.PAY_AWT_GROUP_ID=AWT1.GROUP_ID(+)
AND AIL.CORRECTED_INV_ID = AIC.INVOICE_ID(+)
AND AIL.PA_CC_AR_INVOICE_ID = AIII.INVOICE_ID(+)
AND AIL.PREPAY_INVOICE_ID = AIP.INVOICE_ID(+)
AND AIL.RETAINED_INVOICE_ID = AIR.INVOICE_ID(+)
AND AIL.SHIP_TO_LOCATION_ID = HR.LOCATION_ID(+)
AND AIL.COST_FACTOR_ID = PPET.PRICE_ELEMENT_TYPE_ID(+)

Columns