Payables

Accounts Payables

Transaction Throughput

===========================

-- Number of AP invoices created

select aia.source,to_char(aia.creation_date,'YYYY-MM-DD') creation_date,count(aia.invoice_num) count

from ap_invoices_all aia

where 1=1

and TO_CHAR(AIA.creation_date,'YYYY-MM-DD') >= nvl(:P_FROM_DATE, TO_CHAR(AIA.creation_date,'YYYY-MM-DD'))

and TO_CHAR(AIA.creation_date,'YYYY-MM-DD') <= nvl(:P_TO_DATE, TO_CHAR(AIA.creation_date,'YYYY-MM-DD'))

group by aia.source,creation_date

-- Number of AP Invoices paid

select aia.source,to_char(aia.creation_date,'YYYY-MM-DD') creation_date,count(aia.invoice_num) count

from ap_invoices_all aia

where 1=1

and TO_CHAR(AIA.creation_date,'YYYY-MM-DD') >= nvl(:P_FROM_DATE, TO_CHAR(AIA.creation_date,'YYYY-MM-DD'))

and TO_CHAR(AIA.creation_date,'YYYY-MM-DD') <= nvl(:P_TO_DATE, TO_CHAR(AIA.creation_date,'YYYY-MM-DD'))

and aia.AMOUNT_PAID > 0

group by aia.source,creation_date

-- detailed information for AP invoices created (Paid/Partially Paid/Not Paid)

select

aia.invoice_num Invoice_Number,

aila.line_number,

aila.line_type_lookup_code,

aila.description,

aia.invoice_date,

aia.creation_date,

aia.created_by,

aia.source,

PS.VENDOR_NAME,

PS.SEGMENT1 SUPPLIER_NUMBER,

pvs.vendor_site_code,

aia.INVOICE_AMOUNT,

aia.AMOUNT_PAID,

case

    when aia.INVOICE_AMOUNT = NVL(aia.AMOUNT_PAID,0) then 'Fully Paid'

    when NVL(aia.AMOUNT_PAID,0) = 0 then 'Not Paid'

    else 'Partially Paid'

end payment_status,

(select MAX(ipa.payment_date)

from ap_invoice_payments_all aipa,

ap_checks_all aca,

iby_payments_all ipa

where aipa.invoice_id = aia.invoice_id

and aipa.check_id = aca.check_id

and aca.payment_id = ipa.payment_id

and ipa.payment_status <> 'VOID') payment_date

from ap_invoices_all aia,

ap_invoice_lines_all aila,

POZ_SUPPLIERS_V ps,

poz_supplier_sites_all_m pvs

where aia.invoice_id = aila.invoice_id

and aia.vendor_id=pvs.vendor_id

and aia.vendor_site_id=pvs.vendor_site_id

and aia.vendor_id=ps.vendor_id

and TO_CHAR(AIA.creation_date,'YYYY-MM-DD') >= nvl(:P_FROM_DATE, TO_CHAR(AIA.creation_date,'YYYY-MM-DD'))

and TO_CHAR(AIA.creation_date,'YYYY-MM-DD') <= nvl(:P_TO_DATE, TO_CHAR(AIA.creation_date,'YYYY-MM-DD'))

order by aia.invoice_date,aia.invoice_num,aila.line_number

---------------------------------------------------------------------------------------------------------------------

Transaction Monitor

=======================

Number of stuck Lines in AP Interface

----------------------------------------------

-- count

SELECT count(*)

  FROM ap_interface_rejections apr,

       ap_invoices_interface inv

 WHERE apr.parent_id = inv.invoice_id

   AND inv.status='REJECTED'

-- query for details

SELECT inv.INVOICE_NUM, inv.INVOICE_DATE, inv.VENDOR_NAME, inv.VENDOR_SITE_CODE,inv.INVOICE_AMOUNT,inv.status, inv.source,apr.REJECT_LOOKUP_CODE,

ail.LINE_TYPE_LOOKUP_CODE,ail.ITEM_DESCRIPTION,ail.AMOUNT line_amount,ail.PO_NUMBER,ail.PO_LINE_NUMBER,

ail.ATTRIBUTE1,ail.ATTRIBUTE2,ail.ATTRIBUTE3,ail.ATTRIBUTE4

  FROM ap_interface_rejections apr,

       ap_invoices_interface inv,

ap_invoice_lines_interface ail

 WHERE apr.parent_id = inv.invoice_id

and inv.invoice_id = ail.invoice_id

and inv.load_request_id = ail.load_request_id

   AND inv.status='REJECTED'

Number of AP Holds where workflow closed but hold still not released

-----------------------------------------------------------------------

-- count

select count(*)

from

ap_invoices_all    api,

ap_invoice_lines_all  apil,

AP_INV_APRVL_HIST_ALL  apia,

ap_holds_all  aph

where 1=1

and api.invoice_id = apil.invoice_id

and api.org_id = apil.org_id

and apil.invoice_id = apia.invoice_id

and apil.org_id = apia.org_id

and apia.response = 'RELEASE'

and apil.po_line_location_id = aph.line_location_id

and apia.invoice_id = aph.invoice_id

and apia.hold_id = aph.hold_id

and apia.org_id = aph.org_id

and aph.release_lookup_code is NULL

and aph.release_reason is null

-- query for details

select api.invoice_num, apil.line_number, aph.hold_lookup_code, apia.response, aph.release_reason, aph.release_lookup_code release_name

from

ap_invoices_all    api,

ap_invoice_lines_all  apil,

AP_INV_APRVL_HIST_ALL  apia,

ap_holds_all  aph

where 1=1

and api.invoice_id = apil.invoice_id

and api.org_id = apil.org_id

and apil.invoice_id = apia.invoice_id

and apil.org_id = apia.org_id

and apia.response = 'RELEASE'

and apil.po_line_location_id = aph.line_location_id

and apia.invoice_id = aph.invoice_id

and apia.hold_id = aph.hold_id

and apia.org_id = aph.org_id

and aph.release_lookup_code is NULL

and aph.release_reason is null

Number of AP lines with IPV entry

----------------------------------------

-- count

select 

count(aia.invoice_num)

from ap_invoices_all aia,

ap_invoice_lines_all aila,

po_headers_all pha,

po_lines_all pla,

ap_invoice_distributions_all aida

where aia.invoice_id = aila.invoice_id

and (aia.po_header_id = pha.po_header_id 

     or aila.po_header_id = pha.po_header_id)

and aila.po_line_id = pla.po_line_id

and pha.po_header_id = pla.po_header_id

and aia.org_id = aila.org_id

and aia.org_id = pha.prc_bu_id

and pha.prc_bu_id = pla.prc_bu_id

and aia.invoice_id = aida.invoice_id

and aila.line_number = aida.invoice_line_number

and aida.LINE_TYPE_LOOKUP_CODE = 'IPV'

-- query for details

select 

aia.invoice_num Invoice_Number,

aila.line_number invoice_line_number,

aila.unit_price invoice_line_unit_price,

pha.segment1 Order_Number,

pla.line_num PO_Line_Number,

pla.list_price PO_list_price,

(aila.unit_price-pla.list_price) variance

from ap_invoices_all aia,

ap_invoice_lines_all aila,

po_headers_all pha,

po_lines_all pla,

ap_invoice_distributions_all aida

where aia.invoice_id = aila.invoice_id

and (aia.po_header_id = pha.po_header_id 

     or aila.po_header_id = pha.po_header_id)

and aila.po_line_id = pla.po_line_id

and pha.po_header_id = pla.po_header_id

and aia.org_id = aila.org_id

and aia.org_id = pha.prc_bu_id

and pha.prc_bu_id = pla.prc_bu_id

and aia.invoice_id = aida.invoice_id

and aila.line_number = aida.invoice_line_number

and aida.LINE_TYPE_LOOKUP_CODE = 'IPV'

order by aia.invoice_num