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’)

               
   

  • January 2, 2019 | 16 views
  • Comments