Oracle EBS: 获取PO审批人名字
作者:admin 日期:2010-06-07
PO审批的动作从数据库中去看有:
OPEN NO ACTION REJECT FORWARD APPROVE
SUBMIT CLOSE RETURN CANCEL IMPORT FINALLY CLOSE
通过查看po_action_history表就会发现很有意思的事情了
假设基本的PO审批架构为
跟单员->采购员1审核->采购员2审核->采购总监审核
那 Sequence_Num Action_Code 两列的数据就可以很清晰查看到整个审批流程了
而表中的object_id实际上跟po_header_all的po_header_id 及po_release_all 中的po_release_id也是有关联的.
有了以上这几个字段就可以串出来了,action_code实际上也可以做个变量,这样控制也就更灵活了。
这个应用在打印PO合同里面自动带出采购员中可以应用，省去手工签字的麻烦。
function GetChecker(p_object_id in number) return varchar2 as
v_user_name Varchar2(50);
begin
Select PO_EMPLOYEES_SV.GET_EMP_NAME(POAH.EMPLOYEE_ID)
into v_user_name
FROM PO_ACTION_HISTORY POAH,
PO_LOOKUP_CODES POLC1,
PO_LOOKUP_CODES POLC2
Where POLC1.LOOKUP_CODE(+) = POAH.ACTION_CODE
AND POLC1.LOOKUP_TYPE(+) = 'APPROVER ACTIONS'
AND POLC2.LOOKUP_CODE(+) = POAH.ACTION_CODE
AND POLC2.LOOKUP_TYPE(+) = 'CONTROL ACTIONS'
AND POAH.Sequence_Num = 2
AND POAH.Action_Code = 'FORWARD'
and poah.Object_Id = p_object_id
and rownum = 1;
return v_user_name;
end;
我原来是用这个的，但后来发现有问题
function GetChecker(p_object_id in number) return varchar2 as
v_user_name Varchar2(50);
begin
\* select fu.Description
into v_user_name
from Fnd_User fu
where fu.Employee_Id = p_agent_id;*\
select pahv.employee_name
into v_user_name
from PO_ACTION_HISTORY_V pahv
where pahv.object_id = p_object_id
and pahv.action_code = 'FORWARD'
and pahv.sequence_num = 2
and pahv.object_type_code = 'PO'
and rownum = 1; --转发
return v_user_name;
end;
OPEN NO ACTION REJECT FORWARD APPROVE
SUBMIT CLOSE RETURN CANCEL IMPORT FINALLY CLOSE
通过查看po_action_history表就会发现很有意思的事情了
假设基本的PO审批架构为
跟单员->采购员1审核->采购员2审核->采购总监审核
那 Sequence_Num Action_Code 两列的数据就可以很清晰查看到整个审批流程了
而表中的object_id实际上跟po_header_all的po_header_id 及po_release_all 中的po_release_id也是有关联的.
有了以上这几个字段就可以串出来了,action_code实际上也可以做个变量,这样控制也就更灵活了。
这个应用在打印PO合同里面自动带出采购员中可以应用，省去手工签字的麻烦。
复制内容到剪贴板
程序代码
程序代码function GetChecker(p_object_id in number) return varchar2 as
v_user_name Varchar2(50);
begin
Select PO_EMPLOYEES_SV.GET_EMP_NAME(POAH.EMPLOYEE_ID)
into v_user_name
FROM PO_ACTION_HISTORY POAH,
PO_LOOKUP_CODES POLC1,
PO_LOOKUP_CODES POLC2
Where POLC1.LOOKUP_CODE(+) = POAH.ACTION_CODE
AND POLC1.LOOKUP_TYPE(+) = 'APPROVER ACTIONS'
AND POLC2.LOOKUP_CODE(+) = POAH.ACTION_CODE
AND POLC2.LOOKUP_TYPE(+) = 'CONTROL ACTIONS'
AND POAH.Sequence_Num = 2
AND POAH.Action_Code = 'FORWARD'
and poah.Object_Id = p_object_id
and rownum = 1;
return v_user_name;
end;
我原来是用这个的，但后来发现有问题
复制内容到剪贴板 程序代码
程序代码function GetChecker(p_object_id in number) return varchar2 as
v_user_name Varchar2(50);
begin
\* select fu.Description
into v_user_name
from Fnd_User fu
where fu.Employee_Id = p_agent_id;*\
select pahv.employee_name
into v_user_name
from PO_ACTION_HISTORY_V pahv
where pahv.object_id = p_object_id
and pahv.action_code = 'FORWARD'
and pahv.sequence_num = 2
and pahv.object_type_code = 'PO'
and rownum = 1; --转发
return v_user_name;
end;
