Wednesday 28 March 2012

Function to get the final approver of the Purchase order(PO) by passing po_header_id as parameter

CREATE OR REPLACE FUNCTION getfinalapprover(p_header_id IN number) RETURN varchar2 AS
x_final_approver varchar2(1000);
BEGIN
SELECT   po_employees_sv.get_emp_name (pah.employee_id) final_approver
 INTO    x_final_approver
               FROM po_action_history pah
              WHERE object_type_code = 'PO'
                AND action_code = 'APPROVE'
                AND ROWID IN (
                       SELECT   MAX (ROWID)
                           FROM po_action_history
                          WHERE action_code = 'APPROVE'
                            AND object_type_code = 'PO'
                       GROUP BY object_id)
                and object_id = p_header_id;
RETURN x_final_approver;
EXCEPTION
WHEN no_data_found THEN
RETURN NULL;
WHEN others THEN
RETURN NULL;
END;
/

No comments:

Post a Comment