View: AP_INVOICE_DISTRIBUTIONS_V

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

View Text

SELECT AID.ROWID ROW_ID
, AID.ACCOUNTING_EVENT_ID ACCOUNTING_EVENT_ID /*BUG 4926356*/
, AID.ACCOUNTING_DATE ACCOUNTING_DATE
, AID.ACCTS_PAY_CODE_COMBINATION_ID ACCTS_PAY_CODE_COMBINATION_ID
, AID.AMOUNT AMOUNT
, AID.ASSETS_ADDITION_FLAG ASSETS_ADDITION_FLAG
, AID.ASSETS_TRACKING_FLAG ASSETS_TRACKING_FLAG
, AID.ATTRIBUTE1 ATTRIBUTE1
, AID.ATTRIBUTE10 ATTRIBUTE10
, AID.ATTRIBUTE11 ATTRIBUTE11
, AID.ATTRIBUTE12 ATTRIBUTE12
, AID.ATTRIBUTE13 ATTRIBUTE13
, AID.ATTRIBUTE14 ATTRIBUTE14
, AID.ATTRIBUTE15 ATTRIBUTE15
, AID.ATTRIBUTE2 ATTRIBUTE2
, AID.ATTRIBUTE3 ATTRIBUTE3
, AID.ATTRIBUTE4 ATTRIBUTE4
, AID.ATTRIBUTE5 ATTRIBUTE5
, AID.ATTRIBUTE6 ATTRIBUTE6
, AID.ATTRIBUTE7 ATTRIBUTE7
, AID.ATTRIBUTE8 ATTRIBUTE8
, AID.ATTRIBUTE9 ATTRIBUTE9
, AID.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AID.AWT_FLAG AWT_FLAG
, AID.AWT_GROSS_AMOUNT AWT_GROSS_AMOUNT
, AID.AWT_GROUP_ID AWT_GROUP_ID
, AID.AWT_INVOICE_ID AWT_INVOICE_ID
, AID.AWT_ORIGIN_GROUP_ID AWT_ORIGIN_GROUP_ID
, AID.AWT_TAX_RATE_ID AWT_TAX_RATE_ID
, AID.BASE_AMOUNT BASE_AMOUNT
, AID.BASE_QUANTITY_VARIANCE BASE_QUANTITY_VARIANCE
, AID.BATCH_ID BATCH_ID
, AID.CREATED_BY CREATED_BY
, AID.CREATION_DATE CREATION_DATE
, AID.DESCRIPTION DESCRIPTION
, AID.DISTRIBUTION_LINE_NUMBER DISTRIBUTION_LINE_NUMBER
, AID.DIST_CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID
, AID.ENCUMBERED_FLAG ENCUMBERED_FLAG
, AID.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, AID.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
, AID.EXPENDITURE_TYPE EXPENDITURE_TYPE
, AID.FINAL_MATCH_FLAG FINAL_MATCH_FLAG
, AID.INCOME_TAX_REGION INCOME_TAX_REGION
, AID.INVOICE_ID INVOICE_ID
, AID.LAST_UPDATED_BY LAST_UPDATED_BY
, AID.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AID.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AID.LINE_TYPE_LOOKUP_CODE LINE_TYPE_LOOKUP_CODE
, AID.MATCH_STATUS_FLAG MATCH_STATUS_FLAG
, AID.ORG_ID ORG_ID
, AID.OTHER_INVOICE_ID OTHER_INVOICE_ID
, AID.PACKET_ID PACKET_ID
, AID.PARENT_INVOICE_ID PARENT_INVOICE_ID
, AID.PA_ADDITION_FLAG PA_ADDITION_FLAG
, AID.PA_QUANTITY PA_QUANTITY
, AID.PERIOD_NAME PERIOD_NAME
, AID.POSTED_AMOUNT POSTED_AMOUNT
, AID.POSTED_BASE_AMOUNT POSTED_BASE_AMOUNT
, AID.POSTED_FLAG POSTED_FLAG
, AID.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID
, PD.DISTRIBUTION_NUM PO_DISTRIBUTION_NUMBER
, NVL( AID.PREPAY_AMOUNT_REMAINING
, DECODE( AI1.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, DECODE( AI1.PAYMENT_STATUS_FLAG
, 'Y'
, DECODE( AID.LINE_TYPE_LOOKUP_CODE
, 'ITEM'
, AID.AMOUNT
, AID.PREPAY_AMOUNT_REMAINING )
, AID.PREPAY_AMOUNT_REMAINING )
, AID.PREPAY_AMOUNT_REMAINING ) ) PREPAY_AMOUNT_REMAINING
, AID.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, AID.PROGRAM_ID PROGRAM_ID
, AID.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, AID.PROJECT_ID PROJECT_ID
, AID.QUANTITY_INVOICED QUANTITY_INVOICED
, AID.QUANTITY_VARIANCE QUANTITY_VARIANCE
, AID.REFERENCE_1 REFERENCE_1
, AID.REFERENCE_2 REFERENCE_2
, AID.REQUEST_ID REQUEST_ID
, AID.REVERSAL_FLAG REVERSAL_FLAG
, AID.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AID.STAT_AMOUNT STAT_AMOUNT
, AID.TASK_ID TASK_ID
, AID.TYPE_1099 TYPE_1099
, AID.UNIT_PRICE UNIT_PRICE
, AI1.INVOICE_NUM INVOICE_NUM
, AI2.INVOICE_NUM PARENT_INVOICE_NUM
, AB.BATCH_NAME BATCH_NAME
, AITR.REGION_LONG_NAME INCOME_TAX_REGION_NAME
, AID.POSTED_FLAG POSTED_STATUS
, ALC.DISPLAYED_FIELD LINE_TYPE
, ALC2.DISPLAYED_FIELD STATUS
, ALC3.DISPLAYED_FIELD AWT_CREATION_METHOD
, ALC4.DISPLAYED_FIELD POSTED_STATUS_DISP
, AWT.NAME AWT_GROUP_NAME
, GSOB.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID
, GSOB.NAME SET_OF_BOOKS_NAME
, GSOB.SHORT_NAME SET_OF_BOOKS_SHORT_NAME
, PAP.SEGMENT1 PROJECT
, PAT.TASK_NUMBER TASK
, HOU.NAME EXPENDITURE_ORGANIZATION_NAME
, PD.ACCRUE_ON_RECEIPT_FLAG PO_ACCRUE_ON_RECEIPT_FLAG
, PD.PO_HEADER_ID PO_HEADER_ID
, PD.PO_LINE_ID PO_LINE_ID
, NVL(PL.LINE_NUM_DISPLAY
, TO_CHAR(PL.LINE_NUM)) PO_LINE_NUMBER /*9481666 - CLM*/
, PD.LINE_LOCATION_ID LINE_LOCATION_ID
, PLL.SHIPMENT_NUM PO_LINE_LOCATION_NUMBER
, NVL(PH.CLM_DOCUMENT_NUMBER
, PH.SEGMENT1) PO_NUMBER /*9481666 - CLM*/
, PL.UNIT_MEAS_LOOKUP_CODE UNIT_MEAS_LOOKUP_CODE
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_UOM( AID.DIST_CODE_COMBINATION_ID
, GSOB.CHART_OF_ACCOUNTS_ID) UNIT_OF_MEASURE
, PD.CODE_COMBINATION_ID PO_CODE_COMBINATION_ID
, AID.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, AID.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, AID.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, AID.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, AID.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, AID.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, AID.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, AID.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, AID.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, AID.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, AID.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, AID.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, AID.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, AID.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, AID.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, AID.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, AID.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, AID.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, AID.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, AID.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, AID.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, PLL.PO_RELEASE_ID
, PRL.RELEASE_NUM
, PD.DESTINATION_TYPE_CODE
, AID.INVOICE_DISTRIBUTION_ID
, AID.MATCHED_UOM_LOOKUP_CODE
, RTXNS.TRANSACTION_DATE
, AID.RCV_TRANSACTION_ID
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_HEADER_ID
, RSL.LINE_NUM
, RSL.SHIPMENT_LINE_ID
, AID.AWARD_ID
, AID.GMS_BURDENABLE_RAW_COST
, AID.PREPAY_DISTRIBUTION_ID
, AP_INVOICES_UTILITY_PKG.GET_PREPAY_NUMBER(AID.PREPAY_DISTRIBUTION_ID)
, AP_INVOICES_UTILITY_PKG.GET_PREPAY_DIST_NUMBER(AID.PREPAY_DISTRIBUTION_ID)
, AID.START_EXPENSE_DATE
, AID.MERCHANT_DOCUMENT_NUMBER
, AID.MERCHANT_NAME
, AID.MERCHANT_REFERENCE
, AID.MERCHANT_TAX_REG_NUMBER
, AID.MERCHANT_TAXPAYER_ID
, AID.COUNTRY_OF_SUPPLY
, AID.PARENT_REVERSAL_ID
, DECODE(AID.PARENT_REVERSAL_ID
, NULL
, NULL
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_DIST_LINE_NUM( AID.PARENT_REVERSAL_ID)) REVERSED_DISTRIBUTION_NUM
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_REVERSAL_RELATED_ID( AID.INVOICE_DISTRIBUTION_ID) REVERSING_DISTRIBUTION_ID
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_REVERSING_DIST_NUM( AID.INVOICE_DISTRIBUTION_ID) REVERSING_DISTRIBUTION_NUM
, PLL.CLOSED_CODE
, AID.INVOICE_LINE_NUMBER
, DECODE(AID.PREPAY_DISTRIBUTION_ID
, NULL
, NULL
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_INV_LINE_NUM(AID.PREPAY_DISTRIBUTION_ID)) PREPAY_LINE_NUMBER
, AID.ROUNDING_AMT
, AID.CHARGE_APPLICABLE_TO_DIST_ID
, DECODE(AID.CHARGE_APPLICABLE_TO_DIST_ID
, NULL
, NULL
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_INV_LINE_NUM( AID.CHARGE_APPLICABLE_TO_DIST_ID) ) CHARGE_APPLICABLE_TO_LINE_NUM
, DECODE(AID.CHARGE_APPLICABLE_TO_DIST_ID
, NULL
, NULL
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_DIST_LINE_NUM( AID.CHARGE_APPLICABLE_TO_DIST_ID) ) CHARGE_APPLICABLE_TO_DIST_NUM
, AID.CORRECTED_INVOICE_DIST_ID
, DECODE(AID.CORRECTED_INVOICE_DIST_ID
, NULL
, NULL
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_INVOICE_NUM( AID.CORRECTED_INVOICE_DIST_ID)) CORRECTED_INVOICE_NUM
, DECODE(AID.CORRECTED_INVOICE_DIST_ID
, NULL
, NULL
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_INV_LINE_NUM( AID.CORRECTED_INVOICE_DIST_ID) ) CORRECTED_INVOICE_LINE_NUM
, DECODE(AID.CORRECTED_INVOICE_DIST_ID
, NULL
, NULL
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_DIST_LINE_NUM( AID.CORRECTED_INVOICE_DIST_ID) ) CORRECTED_INVOICE_DIST_NUM
, AID.CORRECTED_QUANTITY
, AID.RELATED_ID
, DECODE(AID.RELATED_ID
, NULL
, NULL
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'IPV'
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_DIST_LINE_NUM(AID.RELATED_ID)
, 'ERV'
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_DIST_LINE_NUM(AID.RELATED_ID)
, AID.DISTRIBUTION_LINE_NUMBER) ) RELATED_DIST_NUM
, AID.ASSET_BOOK_TYPE_CODE
, FABC.BOOK_TYPE_NAME ASSET_BOOK_TYPE_NAME
, AID.ASSET_CATEGORY_ID
, AID.DISTRIBUTION_CLASS
, ALC5.DISPLAYED_FIELD
, AID.INTENDED_USE
, AID.DETAIL_TAX_DIST_ID
, AID.REC_NREC_RATE
, AID.RECOVERY_RATE_ID
, AID.RECOVERY_RATE_NAME
, AID.RECOVERY_TYPE_CODE
, AID.WITHHOLDING_TAX_CODE_ID
, TC.NAME WITHHOLDING_TAX_CODE
, AID.TAX_RECOVERABLE_FLAG
, AID.DIST_MATCH_TYPE
, ALC6.DISPLAYED_FIELD
, AID.CANCELLATION_FLAG
, AI1.CANCELLED_DATE
, AP_INVOICE_DISTRIBUTIONS_PKG.ASSOCIATED_CHARGES( AI1.INVOICE_ID
, AID.INVOICE_DISTRIBUTION_ID) ASSOCIATED_CHARGES
, AID.CASH_POSTED_FLAG
, AID.ACCRUAL_POSTED_FLAG
, AID.RETAINED_AMOUNT_REMAINING
, DECODE(AID.RELATED_RETAINAGE_DIST_ID
, NULL
, NULL
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_DIST_LINE_NUM( AID.RELATED_RETAINAGE_DIST_ID) ) RELATED_RETAINAGE_INV_DIST_NUM
, DECODE(AID.RETAINED_INVOICE_DIST_ID
, NULL
, NULL
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_DIST_LINE_NUM( AID.RETAINED_INVOICE_DIST_ID) ) RETAINED_INVOICE_DIST_NUM
, AID.WEB_PARAMETER_ID
, AID.PAY_AWT_GROUP_ID PAY_AWT_GROUP_ID /*BUG 7439828*/
, AWT1.NAME PAY_AWT_GROUP_NAME /*BUG 7439828*/ FROM AP_INVOICE_DISTRIBUTIONS AID
, AP_INVOICES_ALL AI1
, AP_INVOICES_ALL AI2
, AP_AWT_GROUPS AWT
, AP_AWT_GROUPS AWT1
, /*BUG 7439828*/ AP_BATCHES_ALL AB
, AP_INCOME_TAX_REGIONS AITR
, AP_LOOKUP_CODES ALC
, AP_LOOKUP_CODES ALC2
, AP_LOOKUP_CODES ALC3
, AP_LOOKUP_CODES ALC4
, AP_LOOKUP_CODES ALC5
, AP_LOOKUP_CODES ALC6
, AP_TAX_CODES_ALL TC
, GL_SETS_OF_BOOKS GSOB
, HR_ORGANIZATION_UNITS HOU
, PA_PROJECTS_ALL PAP
, PA_TASKS_EXPEND_V PAT
, /*BUG 16391730*/ PO_DISTRIBUTIONS_ALL PD
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, PO_RELEASES_ALL PRL
, PO_LINE_LOCATIONS_ALL PLL
, RCV_TRANSACTIONS RTXNS
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, FA_BOOK_CONTROLS FABC WHERE AID.INVOICE_ID = AI1.INVOICE_ID
AND AID.PARENT_INVOICE_ID = AI2.INVOICE_ID (+)
AND AID.BATCH_ID = AB.BATCH_ID (+)
AND AID.INCOME_TAX_REGION = AITR.REGION_SHORT_NAME (+)
AND ALC.LOOKUP_TYPE (+) = 'INVOICE DISTRIBUTION TYPE'
AND ALC.LOOKUP_CODE (+) = AID.LINE_TYPE_LOOKUP_CODE
AND ALC2.LOOKUP_TYPE (+) = 'NLS TRANSLATION'
AND ALC2.LOOKUP_CODE (+) = DECODE(AID.MATCH_STATUS_FLAG
, NULL
, 'NEVER APPROVED'
, 'N'
, 'NEVER APPROVED'
, 'T'
, 'NEEDS REAPPROVAL'
, 'A'
, 'APPROVED'
, 'S'
, 'NEVER APPROVED')
AND ALC3.LOOKUP_TYPE (+) = 'AWT FLAG'
AND ALC3.LOOKUP_CODE (+) = AID.AWT_FLAG
AND ALC4.LOOKUP_TYPE (+) = 'POSTING STATUS'
AND ALC4.LOOKUP_CODE (+) = AID.POSTED_FLAG
AND ALC5.LOOKUP_TYPE (+) = 'DISTRIBUTION CLASS'
AND ALC5.LOOKUP_CODE (+) = AID.DISTRIBUTION_CLASS
AND ALC6.LOOKUP_TYPE (+) = 'MATCH_STATUS'
AND ALC6.LOOKUP_CODE (+) = AID.DIST_MATCH_TYPE
AND AID.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND AID.PROJECT_ID = PAP.PROJECT_ID (+)
AND AID.TASK_ID = PAT.TASK_ID (+)
AND AID.EXPENDITURE_ORGANIZATION_ID = HOU.ORGANIZATION_ID (+)
AND AID.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID (+)
AND PD.PO_HEADER_ID = PH.PO_HEADER_ID (+)
AND PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID (+)
AND PLL.PO_LINE_ID = PL.PO_LINE_ID (+)
AND PLL.PO_RELEASE_ID = PRL.PO_RELEASE_ID(+)
AND AID.AWT_GROUP_ID = AWT.GROUP_ID (+)
AND AID.PAY_AWT_GROUP_ID = AWT1.GROUP_ID (+) /*BUG 7439828*/
AND AID.WITHHOLDING_TAX_CODE_ID = TC.TAX_ID (+)
AND AID.RCV_TRANSACTION_ID = RTXNS.TRANSACTION_ID (+)
AND RTXNS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID (+)
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND AID.ASSET_BOOK_TYPE_CODE = FABC.BOOK_TYPE_CODE(+)

Columns