EBS获取采购订单审批记录的函数

  FUNCTION get_po_action_his(p_header_id  IN NUMBER,p_object_type IN VARCHAR2) RETURN VARCHAR2 IS
    l_return_his VARCHAR2(4000);
  BEGIN
    SELECT listagg(his.sequence_num || ' ' ||
                   to_char(his.action_date, 'YYYY-MM-DD HH24:MI:SS') || ' ' ||
                   his.action_dis || ' ' || his.last_name || ' ' ||
                   his.note, chr(13)) within GROUP(ORDER BY his.sequence_num)
    INTO   l_return_his
    FROM   (SELECT   
                   pah.sequence_num
                  ,pah.action_date
                  ,nvl(l.meaning, l2.meaning) action_dis
                  ,he.last_name
                  ,pah.note
            FROM   po_action_history    pah
                  ,hr_employees         he
                  ,fnd_lookup_values_vl l
                  ,fnd_lookup_values_vl l2
            WHERE  pah.employee_id = he.employee_id
						AND pah.OBJECT_ID = p_header_id
            AND    l.lookup_code(+) = pah.action_code
            AND    l.lookup_type(+) = 'APPROVER ACTIONS'
            AND    l2.lookup_type(+) = 'CONTROL ACTIONS'
            AND    l2.lookup_code(+) = pah.action_code
            AND    pah.object_type_code = p_object_type   ---- 'PO'  ---REQUISITION
            ORDER  BY 1
                     ,2) his;
    RETURN l_return_his;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END get_po_action_his;