VIEW: APPS.AP_INVOICE_DISTS_INQUIRY_V

Source

View Text - Preformatted

SELECT AID.ROWID ROW_ID , AID.ACCOUNTING_DATE ACCOUNTING_DATE , AID.ACCRUAL_POSTED_FLAG ACCRUAL_POSTED_FLAG , 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.CASH_POSTED_FLAG CASH_POSTED_FLAG , 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 , 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 , AID.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE , AID.USSGL_TRX_CODE_CONTEXT USSGL_TRX_CODE_CONTEXT , TC.NAME VAT_CODE , 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 , PD.LINE_LOCATION_ID LINE_LOCATION_ID , PH.SEGMENT1 PO_NUMBER , PL.UNIT_MEAS_LOOKUP_CODE UNIT_MEAS_LOOKUP_CODE , TC.TAX_TYPE TAX_TYPE , AP_INVOICE_DISTRIBUTIONS_PKG.GET_UOM( AID.DIST_CODE_COMBINATION_ID, GSOB.CHART_OF_ACCOUNTS_ID) UNIT_OF_MEASURE , AID.TAX_RECOVERY_RATE , AID.TAX_RECOVERY_OVERRIDE_FLAG , AID.TAX_RECOVERABLE_FLAG , AID.TAX_CODE_OVERRIDE_FLAG , AI1.VENDOR_ID VENDOR_ID , AI1.VENDOR_SITE_ID VENDOR_SITE_ID , AI1.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , PV.VENDOR_NAME VENDOR_NAME , PV.SEGMENT1 VENDOR_NUMBER , PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE , AI1.INVOICE_AMOUNT INVOICE_TOTAL , AID.TAX_CODE_ID , 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 , AID.ASSET_CATEGORY_ID , AID.DISTRIBUTION_CLASS FROM AP_INVOICE_DISTRIBUTIONS AID, AP_INVOICES AI1, AP_INVOICES AI2, AP_AWT_GROUPS AWT, AP_BATCHES AB, AP_INCOME_TAX_REGIONS AITR, AP_LOOKUP_CODES ALC, AP_LOOKUP_CODES ALC2, AP_LOOKUP_CODES ALC3, AP_LOOKUP_CODES ALC4, AP_TAX_CODES TC, GL_SETS_OF_BOOKS GSOB, HR_ORGANIZATION_UNITS HOU, PA_PROJECTS_ALL PAP, PA_TASKS PAT, PO_DISTRIBUTIONS PD, PO_HEADERS PH, PO_LINES PL, PO_LINE_LOCATIONS PLL, PO_VENDORS PV, PO_VENDOR_SITES PVS 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 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 AID.AWT_GROUP_ID = AWT.GROUP_ID (+) AND AID.TAX_CODE_ID = TC.TAX_ID (+) AND PV.VENDOR_ID = AI1.VENDOR_ID AND AI1.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID

View Text - HTML Formatted

SELECT AID.ROWID ROW_ID
, AID.ACCOUNTING_DATE ACCOUNTING_DATE
, AID.ACCRUAL_POSTED_FLAG ACCRUAL_POSTED_FLAG
, 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.CASH_POSTED_FLAG CASH_POSTED_FLAG
, 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
, 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
, AID.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, AID.USSGL_TRX_CODE_CONTEXT USSGL_TRX_CODE_CONTEXT
, TC.NAME VAT_CODE
, 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
, PD.LINE_LOCATION_ID LINE_LOCATION_ID
, PH.SEGMENT1 PO_NUMBER
, PL.UNIT_MEAS_LOOKUP_CODE UNIT_MEAS_LOOKUP_CODE
, TC.TAX_TYPE TAX_TYPE
, AP_INVOICE_DISTRIBUTIONS_PKG.GET_UOM( AID.DIST_CODE_COMBINATION_ID
, GSOB.CHART_OF_ACCOUNTS_ID) UNIT_OF_MEASURE
, AID.TAX_RECOVERY_RATE
, AID.TAX_RECOVERY_OVERRIDE_FLAG
, AID.TAX_RECOVERABLE_FLAG
, AID.TAX_CODE_OVERRIDE_FLAG
, AI1.VENDOR_ID VENDOR_ID
, AI1.VENDOR_SITE_ID VENDOR_SITE_ID
, AI1.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PV.VENDOR_NAME VENDOR_NAME
, PV.SEGMENT1 VENDOR_NUMBER
, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, AI1.INVOICE_AMOUNT INVOICE_TOTAL
, AID.TAX_CODE_ID
, 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
, AID.ASSET_CATEGORY_ID
, AID.DISTRIBUTION_CLASS
FROM AP_INVOICE_DISTRIBUTIONS AID
, AP_INVOICES AI1
, AP_INVOICES AI2
, AP_AWT_GROUPS AWT
, AP_BATCHES AB
, AP_INCOME_TAX_REGIONS AITR
, AP_LOOKUP_CODES ALC
, AP_LOOKUP_CODES ALC2
, AP_LOOKUP_CODES ALC3
, AP_LOOKUP_CODES ALC4
, AP_TAX_CODES TC
, GL_SETS_OF_BOOKS GSOB
, HR_ORGANIZATION_UNITS HOU
, PA_PROJECTS_ALL PAP
, PA_TASKS PAT
, PO_DISTRIBUTIONS PD
, PO_HEADERS PH
, PO_LINES PL
, PO_LINE_LOCATIONS PLL
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
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 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 AID.AWT_GROUP_ID = AWT.GROUP_ID (+)
AND AID.TAX_CODE_ID = TC.TAX_ID (+)
AND PV.VENDOR_ID = AI1.VENDOR_ID
AND AI1.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID