Sunday, July 31, 2011

Oracle PO Approval Metrics

I was surpsised to learn that Oracle dont ship any reports which show how long a PO took to get approved so I wrote my own.
The top level SQL will show you one row per PO from approval start to approval end, including business hour /day columns.
==========================
select c.name "Organization",
b.segment1 "PO Number",
b.currency_code "Currency",
d.po_amount "Spend",
min(a.action_date) "Approval Start",
max(a.action_date) "Approval End",
business_hours(min(a.action_date),max(a.action_date)) "Elapsed Business Hours",
business_hours(min(a.action_date),max(a.action_date)) /8 "Elapsed Business Days",
(max(a.action_date)-min(a.action_date))*24 "Elapsed US Hours",
max(a.action_date)-min(a.action_date) "Elapsed US Days"
from po_action_history a,
po_headers_all b,
hr_all_organization_units c,
(select sum(unit_price) po_amount, po_header_id
from po_lines_all
group by po_header_id) d
where a.object_id = b.po_header_id
and b.APPROVED_FLAG = 'Y'
and c.organization_id = b.org_id
and d.po_header_id = b.po_header_id
group by c.name, b.segment1, b.currency_code, d.po_amount
order by c.name, min(a.action_date);

For information on the business_hours function, please see this post from Frank Kulash.

The next question will most likely be. Ok, so PO xxxx took 49 business days to approve! Who the hell was sitting on that PO?
For that, you will need an SQL that will delve further into the po_action_history table.
===========================
select d.name "Organization",
c.segment1 "PO Number",
b.FULL_NAME "Employee",
action_code "Approval Action",
nvl((select action_date
from po_action_history b
where a.object_id = b.object_id
and b.sequence_num = a.sequence_num-1),action_date) "Action Start",
action_date "Action End",
nvl(business_hours(nvl((select action_date
from po_action_history b
where a.object_id = b.object_id
and b.sequence_num = a.sequence_num-1),action_date),action_date),0)
"Elapsed Business Hours",
nvl(business_hours(nvl((select action_date
from po_action_history b
where a.object_id = b.object_id
and b.sequence_num = a.sequence_num-1),action_date),action_date)/8,0)
"Elapsed Business Days",
(a.action_date-(nvl((select action_date
from po_action_history b
where a.object_id = b.object_id
and b.sequence_num = a.sequence_num-1),action_date)))*24
"Elapsed US Hours",
a.action_date-(nvl((select action_date
from po_action_history b
where a.object_id = b.object_id
and b.sequence_num = a.sequence_num-1),action_date))
"Elapsed US Days"
from po_action_history a,
per_people_f b,
po_headers_all c,
hr_all_organization_units d
where 1=1
and a.object_id = c.po_header_id
and c.APPROVED_FLAG = 'Y'
and a.employee_id = b.person_id
and c.org_id = d.organization_id
and trunc(sysdate) between trunc(b.effective_start_date) and trunc(b.effective_end_date)
order by d.name, c.segment1, a.sequence_num;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect