Script to extract Expenses SLA Details

SELECT aerh.invoice_num "Report_Number", papf.full_name "Employee_Name",
       papf.employee_number "Emp_Number", hou.NAME "Operating_Unit",
       aerh.total "Amount", aerh.default_currency_code "Currency",
       (SELECT meaning
          FROM apps.fnd_lookup_values
         WHERE lookup_type = 'EXPENSE REPORT STATUS'
           AND LANGUAGE = 'US'
           AND lookup_code = aerh.expense_status_code) "Report_status",
       (SELECT user_name
          FROM apps.fnd_user
         WHERE user_id = aerh.last_audited_by) "Last_Audited_By",
       DECODE (aerh.audit_code,
               'PAPERLESS_AUDIT', DECODE (aerh.workflow_approved_flag,
                                          'N', 'Y',
                                          'M', 'Y',
                                          NULL, 'Y',
                                          'N'
                                         ),
               'RECEIPT_BASED', DECODE (aerh.workflow_approved_flag,
                                        'N', 'Y',
                                        'M', 'Y',
                                        NULL, 'Y',
                                        'N'
                                       ),
               'N'
              ) "Requires_Audit",
       NVL (TO_CHAR (aerh.report_submitted_date, 'DD-MON-YYYY'),
            TO_CHAR (aerh.creation_date, 'DD-MON-YYYY')
           ) "Report_submit_date",
       (SELECT TO_CHAR (NVL (an.last_update_date, an.entered_date),
                        'DD-MON-YYYY'
                       )
          FROM apps.ap_notes an
         WHERE an.source_object_code = 'OIE_EXPENSE_REPORT'
           AND an.source_object_id = aerh.report_header_id
           AND TO_CHAR (SUBSTR (an.notes_detail, 1, 24)) =
                                                    'Approver Action: Approve'
           AND ROWNUM = 1) "Claim_Approved_Date",
       (CASE
           WHEN aerh.expense_status_code = 'RESOLUTN'
              THEN TO_CHAR (aerh.last_update_date, 'DD-MON-YYYY')
           ELSE NULL
        END
       ) "Pending_Your_Resolution_Date",
       (SELECT TO_CHAR (NVL (an.last_update_date, an.entered_date),
                        'DD-MON-YYYY'
                       )
          FROM apps.ap_notes an
         WHERE an.source_object_code = 'OIE_EXPENSE_REPORT'
           AND an.source_object_id = aerh.report_header_id
           AND TO_CHAR (SUBSTR (an.notes_detail, 1, 15)) = 'Complete Audit.'
           AND ROWNUM = 1) "Audit_Complete_Date"
  FROM apps.ap_expense_report_headers_all aerh,
       apps.per_all_people_f papf,
       apps.hr_operating_units hou
 WHERE papf.person_id = aerh.employee_id
   AND hou.organization_id = aerh.org_id
   AND papf.current_employee_flag = 'Y'
   AND TRUNC (SYSDATE) BETWEEN TRUNC (PAPF.EFFECTIVE_START_DATE) AND TRUNC (PAPF.EFFECTIVE_END_DATE) 
   AND aerh.org_id = FND_PROFILE.VALUE('ORG_ID')

               
   

Export employee data with personal expenses in i-expense and the expense is not claimed

SELECT pap.full_name, pap.email_address, pap.employee_number,

                aeh.invoice_num, aeh.total total_amount,

                aeh.amt_due_employee personal_amount, aeh.expense_status_code,

                aeh.report_header_id, aeh.employee_id, aeh.org_id, aeh.ROWID,

                round((trunc(sysdate)-trunc(aia.creation_date)))days,aia.creation_date

           FROM ap_expense_report_headers_all aeh, per_all_people_f pap,ap_invoices_all aia

          WHERE aeh.employee_id = pap.person_id

            AND aeh.org_id = p_organization_id

            AND AIA.INVOICE_NUM=aeh.invoice_num

            AND aeh.amt_due_employee < 0

            and aia.AMOUNT_PAID = 0;