There is a requirement to be able to show receipts attached for each expense line of an Oracle Internet Expenses submission.
I have created an interactive report with the following SQL query which returns the rows and the name of the file attached.
SELECT SUM(NVL((
CASE
WHEN ( IEXP.RESULT_PAY_VALUE ) = 'Entry Not Yet Processed'
THEN 0
ELSE TO_NUMBER(IEXP.RESULT_PAY_VALUE)
END ),0)) AS Pay_Value,
NVL(IEXP.REPORT_EXPENSE_TYPE,( 'Pay Run Element - '
||IEXP.RESULT_ELEMENT_NAME )) AS Element_Type,
IEXP.ASSIGNMENT_NUMBER AS Assignment_Number,
IEXP.DIRECTORATE AS Directorate,
IEXP.EMPLOYEE_NUMBER AS Employee_Number,
IEXP.FULL_NAME AS Full_Name,
IEXP.LOCATION_CODE AS Location,
IEXP.POSITION_NAME AS Position_Name,
IEXP.REPORT_EXP_STATUS_CODE_LOOKUP AS Expense_Status,
IEXP.REPORT_NUMBER AS Report_Number,
IEXP.REPORT_SUBMITTED_DATE AS Date_Submitted,
IEXP.REPORT_VEHICLE_REG AS Vehicle_Registration,
IEXP.RESULT_EFFECTIVE_DATE AS Effective_Date,
IEXP.SECTION AS Section,
IEXP.SERVICE AS Service,
IEXP.TEAM AS Team,
ATT.FILE_NAME AS File_Name,
ATT.FILE_ID AS ID,
-- ATT.FILE_DATA AS FILE_CONTENT,
SUM(IEXP.REPORT_TRIP_DISTANCE) AS Trip_Distance,
SUM(IEXP.REPORT_LINE_AMOUNT) AS Total_Amount
FROM
(SELECT NVL(APPS.AP_WEB_POLICY_UTILS.GET_LOOKUP_MEANING('EXPENSE REPORT STATUS',a.Report_Expense_Status_Code),'Status Null') Report_Exp_Status_Code_Lookup,
a.*
FROM XXOV.XXOV_IEXPENSES a
) IEXP,
(SELECT flob.FILE_ID,
flob.FILE_NAME,
FLOB.FILE_DATA,
FDOC.DOCUMENT_ID,
FDOC.MEDIA_ID,
FAD.CREATION_DATE,
FAD.PK1_VALUE,
fad.ENTITY_NAME
FROM fnd_lobs flob,
fnd_documents fdoc,
fnd_attached_documents fad
WHERE flob.file_id = fdoc.MEDIA_ID
AND fdoc.document_id = fad.document_id
) ATT
WHERE ( (IEXP.REPORT_LINE_ID = ATT.PK1_VALUE(+)))
AND (ATT.ENTITY_NAME(+) = 'OIE_LINE_ATTACHMENTS')
AND (IEXP.REPORT_SUBMITTED_DATE BETWEEN '01-MAY-2016' AND '06-MAY-2016')
GROUP BY NVL(IEXP.REPORT_EXPENSE_TYPE,( 'Pay Run Element - ' ||IEXP.RESULT_ELEMENT_NAME )), IEXP.ASSIGNMENT_NUMBER, IEXP.DIRECTORATE, IEXP.EMPLOYEE_NUMBER, IEXP.FULL_NAME,
IEXP.LOCATION_CODE, IEXP.REPORT_EXPENSE_TYPE, IEXP.POSITION_NAME, IEXP.REPORT_EXP_STATUS_CODE_LOOKUP,
IEXP.REPORT_NUMBER, IEXP.REPORT_SUBMITTED_DATE, IEXP.REPORT_VEHICLE_REG, IEXP.RESULT_EFFECTIVE_DATE, IEXP.SECTION,
IEXP.SERVICE, IEXP.TEAM, ATT.FILE_NAME,IEXP.REPORT_EXPENSE_TYPE, IEXP.POSITION_NAME, IEXP.REPORT_EXP_STATUS_CODE_LOOKUP, IEXP.REPORT_NUMBER,
IEXP.REPORT_SUBMITTED_DATE, IEXP.REPORT_VEHICLE_REG, IEXP.RESULT_EFFECTIVE_DATE, IEXP.SECTION, IEXP.SERVICE,
IEXP.TEAM, ATT.FILE_NAME, ATT.FILE_ID
Now I need to be able to download that attachment from FND_LOBS using the FILE_ID.
Please can anyone really spell it out for me? I am very new to APEX and am getting confused by the different methods available.
Thank you.