Thursday, 20 June 2019

Query to Get AP Invoice "Approval Due On" Information

SELECT DISTINCT aih.invoice_id, aia.invoice_num
     , 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