[Home] [Help]
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
,NVL(PH.CLM_DOCUMENT_NUMBER, PH.SEGMENT1) PO_NUMBER /*9481666 - CLM*/
,AIL.PO_LINE_ID PO_LINE_ID
,NVL(PL.LINE_NUM_DISPLAY, TO_CHAR(PL.LINE_NUM)) PO_LINE_NUMBER /*9481666 - CLM*/
,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)
,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 /*bug 7439828*/
,AWT1.NAME PAY_AWT_GROUP_NAME /*bug 7439828*/
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_EXPEND_V PAT /*Bug 16391730*/
, FA_BOOK_CONTROLS FABC
, AP_AWT_GROUPS AWT
, AP_AWT_GROUPS AWT1 /*bug 7439828*/
, 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(+) /*bug 7439828*/
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(+)
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
, NVL(PH.CLM_DOCUMENT_NUMBER
, PH.SEGMENT1) PO_NUMBER /*9481666 - CLM*/
, AIL.PO_LINE_ID PO_LINE_ID
, NVL(PL.LINE_NUM_DISPLAY
, TO_CHAR(PL.LINE_NUM)) PO_LINE_NUMBER /*9481666 - CLM*/
, 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)
, 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 /*BUG 7439828*/
, AWT1.NAME PAY_AWT_GROUP_NAME /*BUG 7439828*/
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_EXPEND_V PAT /*BUG 16391730*/
, FA_BOOK_CONTROLS FABC
, AP_AWT_GROUPS AWT
, AP_AWT_GROUPS AWT1 /*BUG 7439828*/
, 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(+) /*BUG 7439828*/
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(+)
|
|
|
|