, aia.invoice_date, hou.name circle
, fu.employee_id, ppf.full_name
, wf.recipient_role recipient_aprover_name
, wf.notification_id
, wf.begin_date notification_start_date
, wf.due_date notification_due_date
, wf.item_key
FROM apps.ap_inv_aprvl_hist_all aih
, apps.hr_operating_units hou
, apps.ap_invoices_all aia
, apps.wf_notifications wf
, apps.fnd_user fu
, apps.per_all_people_f ppf
WHERE 1 = 1
AND aih.org_id = hou.organization_id
AND aih.org_id = aia.org_id
AND aih.invoice_id = aia.invoice_id
AND aih.invoice_id IN( SELECT invoice_id
FROM apps.ap_inv_aprvl_hist_all
WHERE response = 'TIMEOUT')
AND substr( wf.item_key, 1, 8 ) = TO_CHAR( aih.invoice_id )
AND wf.recipient_role = fu.user_name
AND fu.employee_id = ppf.person_id
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND aih.creation_date BETWEEN '01-Mar-2019'
AND '15-Jun-2019'
AND wf.message_type = 'APINVAPR'
AND wf.status = 'OPEN'
AND wf.due_date >= SYSDATE
ORDER BY 10, 3, 2;
No comments:
Post a Comment