View: ICX_AP_EXPENSE_LINES_V

Product:	ICX - Oracle iProcurement
Description:	Employee Expense Reports Line View
Implementation/DBA Data:	APPS.ICX_AP_EXPENSE_LINES_V

View Text

SELECT AID.INVOICE_ID REPORT_FOREIGN_KEY
, AID.DISTRIBUTION_LINE_NUMBER DISTRIBUTION_LINE_NUMBER
, AID.EXPENSE_GROUP EXPENSE_GROUP
, AID.START_EXPENSE_DATE START_EXPENSE_DATE
, AID.END_EXPENSE_DATE END_EXPENSE_DATE
, NVL(AID.END_EXPENSE_DATE
, AID.START_EXPENSE_DATE) - AID.START_EXPENSE_DATE + 1 DAYS
, TO_CHAR(AID.DAILY_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) DAILY_AMOUNT
, TO_CHAR(AID.RECEIPT_CURRENCY_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) RECEIPT_CURRENCY_AMOUNT
, AID.RECEIPT_CURRENCY_CODE RECEIPT_CURRENCY_CODE
, AID.RECEIPT_CONVERSION_RATE RECEIPT_CONVERSION_RATE
, TO_CHAR(AID.AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) AMOUNT
, NVL(AERP.WEB_FRIENDLY_PROMPT
, AID.DESCRIPTION) EXPENSE_TYPE
, AID.JUSTIFICATION JUSTIFICATION
, AID.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, AID.RECEIPT_MISSING_FLAG RECEIPT_MISSING_FLAG
, AID.RECEIPT_VERIFIED_FLAG RECEIPT_VERIFIED_FLAG
, 'INVOICE' DOCUMENT_TYPE
, AID.ADJUSTMENT_REASON ADJUSTMENT_REASON
, PAPRJ.SEGMENT1
, PATSK.TASK_NUMBER
, AID.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AID.ATTRIBUTE1 ATTRIBUTE1
, 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.ATTRIBUTE10 ATTRIBUTE10
, AID.ATTRIBUTE11 ATTRIBUTE11
, AID.ATTRIBUTE12 ATTRIBUTE12
, AID.ATTRIBUTE13 ATTRIBUTE13
, AID.ATTRIBUTE14 ATTRIBUTE14
, AID.ATTRIBUTE15 ATTRIBUTE15
, GMS.AWARD_NUMBER
FROM AP_INVOICES AI
, AP_INVOICE_DISTRIBUTIONS AID
, AP_EXPENSE_REPORT_PARAMS AERP
, PA_PROJECTS_ALL PAPRJ
, PA_TASKS PATSK
, GMS_AWARDS GMS
WHERE AI.INVOICE_ID = AID.INVOICE_ID
AND AID.WEB_PARAMETER_ID = AERP.PARAMETER_ID (+) AND( AID.CC_REVERSAL_FLAG <> 'Y' OR AID.CC_REVERSAL_FLAG IS NULL)
AND AID.PROJECT_ID = PAPRJ.PROJECT_ID
AND AID.TASK_ID = PATSK.TASK_ID
AND GMS.AWARD_ID (+) = AID.AWARD_ID UNION SELECT AID.INVOICE_ID REPORT_FOREIGN_KEY
, AID.DISTRIBUTION_LINE_NUMBER DISTRIBUTION_LINE_NUMBER
, AID.EXPENSE_GROUP EXPENSE_GROUP
, AID.START_EXPENSE_DATE START_EXPENSE_DATE
, AID.END_EXPENSE_DATE END_EXPENSE_DATE
, NVL(AID.END_EXPENSE_DATE
, AID.START_EXPENSE_DATE) - AID.START_EXPENSE_DATE + 1 DAYS
, TO_CHAR(AID.DAILY_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) DAILY_AMOUNT
, TO_CHAR(AID.RECEIPT_CURRENCY_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) RECEIPT_CURRENCY_AMOUNT
, AID.RECEIPT_CURRENCY_CODE RECEIPT_CURRENCY_CODE
, AID.RECEIPT_CONVERSION_RATE RECEIPT_CONVERSION_RATE
, TO_CHAR(AID.AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) AMOUNT
, NVL(AERP.WEB_FRIENDLY_PROMPT
, AID.DESCRIPTION) EXPENSE_TYPE
, AID.JUSTIFICATION JUSTIFICATION
, AID.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, AID.RECEIPT_MISSING_FLAG RECEIPT_MISSING_FLAG
, AID.RECEIPT_VERIFIED_FLAG RECEIPT_VERIFIED_FLAG
, 'INVOICE' DOCUMENT_TYPE
, AID.ADJUSTMENT_REASON ADJUSTMENT_REASON
, NULL
, NULL
, AID.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AID.ATTRIBUTE1 ATTRIBUTE1
, 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.ATTRIBUTE10 ATTRIBUTE10
, AID.ATTRIBUTE11 ATTRIBUTE11
, AID.ATTRIBUTE12 ATTRIBUTE12
, AID.ATTRIBUTE13 ATTRIBUTE13
, AID.ATTRIBUTE14 ATTRIBUTE14
, AID.ATTRIBUTE15 ATTRIBUTE15
, NULL
FROM AP_INVOICES AI
, AP_INVOICE_DISTRIBUTIONS AID
, AP_EXPENSE_REPORT_PARAMS AERP
WHERE AI.INVOICE_ID = AID.INVOICE_ID
AND AID.WEB_PARAMETER_ID = AERP.PARAMETER_ID (+) AND( AID.CC_REVERSAL_FLAG <> 'Y' OR AID.CC_REVERSAL_FLAG IS NULL)
AND AID.PROJECT_ID IS NULL
AND AID.TASK_ID IS NULL
AND AID.AWARD_ID IS NULL UNION ALL SELECT AERL.REPORT_HEADER_ID
, AERL.DISTRIBUTION_LINE_NUMBER
, AERL.EXPENSE_GROUP
, AERL.START_EXPENSE_DATE
, AERL.END_EXPENSE_DATE
, NVL(AERL.END_EXPENSE_DATE
, AERL.START_EXPENSE_DATE) - AERL.START_EXPENSE_DATE + 1
, TO_CHAR(DECODE(AERL.DAILY_AMOUNT
, 0
, NULL
, AERL.DAILY_AMOUNT)
, FND_CURRENCY.GET_FORMAT_MASK( AERH.DEFAULT_CURRENCY_CODE
, 30))
, TO_CHAR(AERL.RECEIPT_CURRENCY_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(AERH.DEFAULT_CURRENCY_CODE
, 30))
, AERL.RECEIPT_CURRENCY_CODE
, TRUNC(AERL.RECEIPT_CONVERSION_RATE
, 8)
, TO_CHAR(NVL(AERL.AMOUNT
, 0)
, FND_CURRENCY.GET_FORMAT_MASK( AERH.DEFAULT_CURRENCY_CODE
, 30))
, NVL(AERP.WEB_FRIENDLY_PROMPT
, AERL.ITEM_DESCRIPTION)
, AERL.JUSTIFICATION
, FNDL1.MEANING
, FNDL2.MEANING
, FNDL3.MEANING
, 'EXPENSE REPORT'
, AERL.ADJUSTMENT_REASON
, PAPRJ.SEGMENT1
, PATSK.TASK_NUMBER
, AERL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AERL.ATTRIBUTE1 ATTRIBUTE1
, AERL.ATTRIBUTE2 ATTRIBUTE2
, AERL.ATTRIBUTE3 ATTRIBUTE3
, AERL.ATTRIBUTE4 ATTRIBUTE4
, AERL.ATTRIBUTE5 ATTRIBUTE5
, AERL.ATTRIBUTE6 ATTRIBUTE6
, AERL.ATTRIBUTE7 ATTRIBUTE7
, AERL.ATTRIBUTE8 ATTRIBUTE8
, AERL.ATTRIBUTE9 ATTRIBUTE9
, AERL.ATTRIBUTE10 ATTRIBUTE10
, AERL.ATTRIBUTE11 ATTRIBUTE11
, AERL.ATTRIBUTE12 ATTRIBUTE12
, AERL.ATTRIBUTE13 ATTRIBUTE13
, AERL.ATTRIBUTE14 ATTRIBUTE14
, AERL.ATTRIBUTE15 ATTRIBUTE15
, GMS.AWARD_NUMBER
FROM AP_EXPENSE_REPORT_LINES AERL
, AP_EXPENSE_REPORT_HEADERS AERH
, AP_EXPENSE_REPORT_PARAMS AERP
, FND_LOOKUPS FNDL1
, FND_LOOKUPS FNDL2
, FND_LOOKUPS FNDL3
, PA_PROJECTS_ALL PAPRJ
, PA_TASKS PATSK
, GMS_AWARDS GMS
WHERE FNDL1.LOOKUP_TYPE (+) = 'YES_NO'
AND FNDL1.LOOKUP_CODE (+)= AERL.RECEIPT_REQUIRED_FLAG
AND FNDL2.LOOKUP_TYPE (+) = 'YES_NO'
AND FNDL2.LOOKUP_CODE (+) = AERL.RECEIPT_MISSING_FLAG
AND FNDL3.LOOKUP_TYPE (+) = 'YES_NO'
AND FNDL3.LOOKUP_CODE (+) = AERL.RECEIPT_VERIFIED_FLAG
AND AERL.WEB_PARAMETER_ID = AERP.PARAMETER_ID (+)
AND AERH.VOUCHNO||'' = 0
AND AERL.REPORT_HEADER_ID = AERH.REPORT_HEADER_ID
AND AERL.PROJECT_ID = PAPRJ.PROJECT_ID
AND AERL.TASK_ID = PATSK.TASK_ID
AND AERL.AWARD_ID = GMS.AWARD_ID(+) UNION SELECT AERL.REPORT_HEADER_ID
, AERL.DISTRIBUTION_LINE_NUMBER
, AERL.EXPENSE_GROUP
, AERL.START_EXPENSE_DATE
, AERL.END_EXPENSE_DATE
, NVL(AERL.END_EXPENSE_DATE
, AERL.START_EXPENSE_DATE) - AERL.START_EXPENSE_DATE + 1
, TO_CHAR(DECODE(AERL.DAILY_AMOUNT
, 0
, NULL
, AERL.DAILY_AMOUNT)
, FND_CURRENCY.GET_FORMAT_MASK( AERH.DEFAULT_CURRENCY_CODE
, 30))
, TO_CHAR(AERL.RECEIPT_CURRENCY_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(AERH.DEFAULT_CURRENCY_CODE
, 30))
, AERL.RECEIPT_CURRENCY_CODE
, TRUNC(AERL.RECEIPT_CONVERSION_RATE
, 8)
, TO_CHAR(NVL(AERL.AMOUNT
, 0)
, FND_CURRENCY.GET_FORMAT_MASK( AERH.DEFAULT_CURRENCY_CODE
, 30))
, NVL(AERP.WEB_FRIENDLY_PROMPT
, AERL.ITEM_DESCRIPTION)
, AERL.JUSTIFICATION
, FNDL1.MEANING
, FNDL2.MEANING
, FNDL3.MEANING
, 'EXPENSE REPORT'
, AERL.ADJUSTMENT_REASON
, NULL
, NULL
, AERL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AERL.ATTRIBUTE1 ATTRIBUTE1
, AERL.ATTRIBUTE2 ATTRIBUTE2
, AERL.ATTRIBUTE3 ATTRIBUTE3
, AERL.ATTRIBUTE4 ATTRIBUTE4
, AERL.ATTRIBUTE5 ATTRIBUTE5
, AERL.ATTRIBUTE6 ATTRIBUTE6
, AERL.ATTRIBUTE7 ATTRIBUTE7
, AERL.ATTRIBUTE8 ATTRIBUTE8
, AERL.ATTRIBUTE9 ATTRIBUTE9
, AERL.ATTRIBUTE10 ATTRIBUTE10
, AERL.ATTRIBUTE11 ATTRIBUTE11
, AERL.ATTRIBUTE12 ATTRIBUTE12
, AERL.ATTRIBUTE13 ATTRIBUTE13
, AERL.ATTRIBUTE14 ATTRIBUTE14
, AERL.ATTRIBUTE15 ATTRIBUTE15
, NULL
FROM AP_EXPENSE_REPORT_LINES AERL
, AP_EXPENSE_REPORT_HEADERS AERH
, AP_EXPENSE_REPORT_PARAMS AERP
, FND_LOOKUPS FNDL1
, FND_LOOKUPS FNDL2
, FND_LOOKUPS FNDL3
WHERE FNDL1.LOOKUP_TYPE (+) = 'YES_NO'
AND FNDL1.LOOKUP_CODE (+)= AERL.RECEIPT_REQUIRED_FLAG
AND FNDL2.LOOKUP_TYPE (+) = 'YES_NO'
AND FNDL2.LOOKUP_CODE (+) = AERL.RECEIPT_MISSING_FLAG
AND FNDL3.LOOKUP_TYPE (+) = 'YES_NO'
AND FNDL3.LOOKUP_CODE (+) = AERL.RECEIPT_VERIFIED_FLAG
AND AERL.WEB_PARAMETER_ID = AERP.PARAMETER_ID (+)
AND AERH.VOUCHNO||'' = 0
AND AERL.REPORT_HEADER_ID = AERH.REPORT_HEADER_ID
AND AERL.PROJECT_ID IS NULL
AND AERL.TASK_ID IS NULL
AND AERL.AWARD_ID IS NULL

Columns