eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AP_INVOICE_DISTRIBUTIONS_V

Source

View Text - Preformatted


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(+)

View Text - HTML Formatted

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(+)