VIEW: APPS.ICX_AP_EXPENSE_LINES_V

Source

View Text - Preformatted

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 , FNDL1.MEANING RECEIPT_REQUIRED_FLAG , FNDL2.MEANING RECEIPT_MISSING_FLAG , FNDL3.MEANING 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_SSA_AWARDS_V GMS, FND_LOOKUPS FNDL1, FND_LOOKUPS FNDL2, FND_LOOKUPS FNDL3 WHERE FNDL1.LOOKUP_TYPE (+) = 'YES_NO' AND FNDL1.LOOKUP_CODE (+)= AID.RECEIPT_REQUIRED_FLAG AND FNDL2.LOOKUP_TYPE (+) = 'YES_NO' AND FNDL2.LOOKUP_CODE (+) = AID.RECEIPT_MISSING_FLAG AND FNDL3.LOOKUP_TYPE (+) = 'YES_NO' AND FNDL3.LOOKUP_CODE (+) = AID.RECEIPT_VERIFIED_FLAG AND 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 , FNDL1.MEANING RECEIPT_REQUIRED_FLAG , FNDL2.MEANING RECEIPT_MISSING_FLAG , FNDL3.MEANING 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, FND_LOOKUPS FNDL1, FND_LOOKUPS FNDL2, FND_LOOKUPS FNDL3 WHERE FNDL1.LOOKUP_TYPE (+) = 'YES_NO' AND FNDL1.LOOKUP_CODE (+)= AID.RECEIPT_REQUIRED_FLAG AND FNDL2.LOOKUP_TYPE (+) = 'YES_NO' AND FNDL2.LOOKUP_CODE (+) = AID.RECEIPT_MISSING_FLAG AND FNDL3.LOOKUP_TYPE (+) = 'YES_NO' AND FNDL3.LOOKUP_CODE (+) = AID.RECEIPT_VERIFIED_FLAG AND 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_SSA_AWARDS_V 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

View Text - HTML Formatted

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
, FNDL1.MEANING RECEIPT_REQUIRED_FLAG
, FNDL2.MEANING RECEIPT_MISSING_FLAG
, FNDL3.MEANING 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_SSA_AWARDS_V GMS
, FND_LOOKUPS FNDL1
, FND_LOOKUPS FNDL2
, FND_LOOKUPS FNDL3
WHERE FNDL1.LOOKUP_TYPE (+) = 'YES_NO'
AND FNDL1.LOOKUP_CODE (+)= AID.RECEIPT_REQUIRED_FLAG
AND FNDL2.LOOKUP_TYPE (+) = 'YES_NO'
AND FNDL2.LOOKUP_CODE (+) = AID.RECEIPT_MISSING_FLAG
AND FNDL3.LOOKUP_TYPE (+) = 'YES_NO'
AND FNDL3.LOOKUP_CODE (+) = AID.RECEIPT_VERIFIED_FLAG
AND 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
, FNDL1.MEANING RECEIPT_REQUIRED_FLAG
, FNDL2.MEANING RECEIPT_MISSING_FLAG
, FNDL3.MEANING 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
, FND_LOOKUPS FNDL1
, FND_LOOKUPS FNDL2
, FND_LOOKUPS FNDL3
WHERE FNDL1.LOOKUP_TYPE (+) = 'YES_NO'
AND FNDL1.LOOKUP_CODE (+)= AID.RECEIPT_REQUIRED_FLAG
AND FNDL2.LOOKUP_TYPE (+) = 'YES_NO'
AND FNDL2.LOOKUP_CODE (+) = AID.RECEIPT_MISSING_FLAG
AND FNDL3.LOOKUP_TYPE (+) = 'YES_NO'
AND FNDL3.LOOKUP_CODE (+) = AID.RECEIPT_VERIFIED_FLAG
AND 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_SSA_AWARDS_V 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