需求：商务人员提出货通知单，纸质单送财务审核，然后财务系统中审核信用额度等信息方才批准出货。
但是晚上或节假日财务不上班，然后工厂急着出货就会导致出货延迟耽误出货，
故客制workflow，商务提单后通知财务，财务可用手机登入系统审核此出货通知单。
结果如上：
coding如下：
create or replace package body JW_SHIP_APPROVE_WF_PKG is
PROCEDURE fin_approve_result(itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2) IS
l_header_id NUMBER;
begin
IF (funcmode <> wf_engine.eng_run) THEN
resultout := wf_engine.eng_null;
RETURN;
END IF;
l_header_id := wf_engine.getitemattrnumber(itemtype => itemtype,
itemkey => itemkey,
aname => 'DELIVERY_HEADER_ID');
update JW_OM_DELIVERY_HEADERS h
set h.status_code = 3,
h.status = '已审核',
h.last_updated_by = fnd_profile.VALUE('USER_ID'),
h.last_update_date = sysdate
where h.delivery_header_id = l_header_id ;
resultout := 'COMPLETE';
NULL;
END;
PROCEDURE fin_reject_result(itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2) is
l_header_id NUMBER;
begin
IF (funcmode <> wf_engine.eng_run) THEN
resultout := wf_engine.eng_null;
RETURN;
END IF;
l_header_id := wf_engine.getitemattrnumber(itemtype => itemtype,
itemkey => itemkey,
aname => 'DELIVERY_HEADER_ID');
update JW_OM_DELIVERY_HEADERS h
set h.status_code = 4,
h.status = '已拒绝',
h.last_updated_by = fnd_profile.VALUE('USER_ID'),
h.last_update_date = sysdate
where h.delivery_header_id = l_header_id ;
resultout := 'COMPLETE';
null;
end;
procedure fin_approve_wf(v_header_id number,v_flag number) is
v_attribute3 varchar2(30);
begin
select h.attribute3
into v_attribute3
from JW_OM_DELIVERY_HEADERS h
where h.delivery_header_id = v_header_id;
if v_attribute3 is not null then
if v_flag = 1 then-------审批通过
wf_engine.completeactivity(itemtype => 'JW_SHIP',
itemkey => v_attribute3,
activity => 'JW_OM_PROCESS:JW_OM_NOTIFICATIONS',
RESULT => 'APPROVE');
else
wf_engine.completeactivity(itemtype => 'JW_SHIP',------审批拒绝
itemkey => v_attribute3,
activity => 'JW_OM_PROCESS:JW_OM_NOTIFICATIONS',
RESULT => 'REJECT');
end if;
else
if v_flag = 1 then
update JW_OM_DELIVERY_HEADERS jh
set jh.status_code = 3,jh.status = '已审核',
jh.last_updated_by = fnd_profile.VALUE('USER_ID'),
jh.last_update_date = sysdate
where jh.delivery_header_id = v_header_id;
else
update JW_OM_DELIVERY_HEADERS jh
set jh.status_code = 4,jh.status = '已拒绝',
jh.last_updated_by = fnd_profile.VALUE('USER_ID'),
jh.last_update_date = sysdate
where jh.delivery_header_id = v_header_id;
end if;
end if;
COMMIT;
null;
exception
when others then
null;
end;
procedure fin_cancel_wf(v_header_id number) is
l_item_key VARCHAR2(30);
begin
select h.attribute3
into l_item_key
from jw_om_delivery_headers h
where h.delivery_header_id = v_header_id;
wf_engine.abortprocess(itemtype => 'JW_SHIP', itemkey => l_item_key);
commit;
null;
exception
when others then
raise_application_error(-20001,'cancel error');
end;
PROCEDURE get_document_details(document_id IN VARCHAR2,
display_type IN VARCHAR2,
document IN OUT CLOB,
document_type IN OUT VARCHAR2) IS
l_item_type wf_items.item_type%TYPE;
l_item_key wf_items.item_key%TYPE;
l_document_id NUMBER;
l_document varchar2(32000) := '';
l_document_conent_0 varchar2(32000);
cursor c1(v_document_id number) is
select l.item_number,l.attribute1,l.notice_number,oola.unit_selling_price
from JW_OM_DELIVERY_LINES l,oe_order_lines_all oola
where l.order_line_id = oola.line_id
and l.delivery_header_id = v_document_id ;
begin
l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
l_item_key := substr(document_id,
instr(document_id, ':') + 1,
length(document_id) - 2);
l_document_id := wf_engine.getitemattrnumber(itemtype => l_item_type,
itemkey => l_item_key,
aname => 'DELIVERY_HEADER_ID');
/*l_document := '<table border=1>
<tr>
<td>
This table shows the details of the document. You can put anything here.
</td>
</tr>
</table>';*/
l_document_conent_0 := '<tr>
<td>物料</td>
<td>物料说明</td>
<td>通知数量</td>
<td>单价</td>
</tr>';
for v1 in c1(l_document_id) loop
l_document_conent_0 := l_document_conent_0 ||
'<tr>
<td> ' || v1.item_number || '</td>
<td> ' || v1.attribute1 || '</td>
<td> ' || v1.notice_number || '</td>
<td> ' || v1.unit_selling_price || '</td>
</tr>';
end loop;
l_document := '<table border=1>'
|| l_document_conent_0 ||
'</table>';
document := document || l_document;
null;
end;
procedure fin_submit_wf(v_header_id number) is
l_item_key VARCHAR2(30);
l_user_item_key VARCHAR2(30);
l_item_type VARCHAR2(30) := 'JW_SHIP';
l_process VARCHAR2(30) := 'JW_OM_PROCESS';
l_orig_system VARCHAR2(30) := 'PER';
l_seq_num number;
l_submit_id number;
l_submit_name wf_roles.NAME%TYPE;--提交人
l_submit_dsp_name wf_roles.display_name%TYPE;
l_approve_id number;
l_approve_name wf_roles.NAME%TYPE;--审批人
l_approve_dsp_name wf_roles.display_name%TYPE;
L_DELIVERY_HEADER JW_OM_DELIVERY_HEADERS%ROWTYPE;
l_init_credit number := 0;
l_userd_credit number := 0;
l_ship_credit number := 0;
l_remain_credit number := 0;
v_credit_used_1 NUMBER;
cursor c1(p_customer_id NUMBER) is
select distinct hca2.cust_account_id cust_account_id
from HZ_CUST_ACCOUNTS hca1,
HZ_CUST_ACCT_SITES_ALL c1,
HZ_CUST_ACCOUNTS hca2,
HZ_CUST_ACCT_SITES_ALL c2
where hca1.party_id = hca2.party_id
and hca1.cust_account_id = c1.cust_account_id
and hca1.cust_account_id = p_customer_id--1200
and hca2.cust_account_id = c2.cust_account_id
and c2.org_id = c1.org_id
and hca2.status = 'A'
AND C2.STATUS = 'A'
and hca1.status = 'A'
AND C1.STATUS = 'A'
and (hca2.created_by_module = 'CUST_INTERFACE' or hca2.cust_account_id = hca1.cust_account_id)
and c2.org_id = c1.org_id;
begin
SELECT * INTO L_DELIVERY_HEADER FROM JW_OM_DELIVERY_HEADERS J WHERE J.DELIVERY_HEADER_ID = v_header_id;
select JW_SHIP_APPROVE_WF_S.Nextval into l_seq_num from dual;
l_item_key := L_DELIVERY_HEADER.NOTICE_NUMBER || to_char(l_seq_num);
l_user_item_key := l_item_key;
SELECT WF.ORIG_SYSTEM_ID
into l_submit_id
FROM FND_USER FU,wf_roles WF
WHERE WF.ORIG_SYSTEM = 'PER'
AND FU.USER_NAME = WF.NAME
AND FU.USER_ID = L_DELIVERY_HEADER.created_by;---申请人
SELECT WF.ORIG_SYSTEM_ID
into l_approve_id
FROM FND_USER FU,wf_roles WF
WHERE WF.ORIG_SYSTEM = 'PER'
AND FU.USER_NAME = WF.NAME
AND FU.USER_ID = 1553;---财务审批人 1120：小谭
begin----得到信用额度
SELECT nvl(hcpa.overall_credit_limit
* JWARR002.GET_CURR_CODE_RATE(hcpa.currency_code,'RMB',L_DELIVERY_HEADER.NOTICE_DATE),0)
INTO l_init_credit
FROM hz_customer_profiles hcp
,hz_cust_profile_amts hcpa
WHERE hcp.cust_account_id = hcpa.cust_account_id
AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id
AND hcp.cust_account_id in--= p_customer_id;
(select distinct hca2.cust_account_id
from HZ_CUST_ACCOUNTS hca1,
HZ_CUST_ACCT_SITES_ALL c1,
HZ_CUST_ACCOUNTS hca2,
HZ_CUST_ACCT_SITES_ALL c2
where hca1.party_id = hca2.party_id
and hca1.cust_account_id = c1.cust_account_id
and hca1.cust_account_id = L_DELIVERY_HEADER.Customer_Id--1200
and hca2.cust_account_id = c2.cust_account_id
and c2.org_id = c1.org_id
and hca2.status = 'A'
AND C2.STATUS = 'A'
and hca1.status = 'A'
AND C1.STATUS = 'A'
and (hca2.created_by_module = 'CUST_INTERFACE' or hca2.cust_account_id = hca1.cust_account_id)
and c2.org_id = c1.org_id);
exception
when others then
l_init_credit := 0;
end;
for v1 in c1(L_DELIVERY_HEADER.Customer_Id) loop----已使用的信用额度
BEGIN
v_credit_used_1 := 0;
SELECT jwarr002.get_customer_invoice_amount1(null,
v1.cust_account_id,sysdate,L_DELIVERY_HEADER.NOTICE_DATE,
null) -
jwarr002.get_customer_receipt1(null, v1.cust_account_id,sysdate, L_DELIVERY_HEADER.NOTICE_DATE, null) pay_amount
into v_credit_used_1
FROM dual;
EXCEPTION
WHEN OTHERS THEN
v_credit_used_1 := 0;
END;
l_userd_credit := l_userd_credit + v_credit_used_1;
END LOOP;
begin-------出货的金额
SELECT SUM(amount)
INTO l_ship_credit
FROM jw_om_delivery_find_v
WHERE delivery_header_id = v_header_id;
exception
when others then
l_ship_credit := 0;
end ;
l_remain_credit := NVL(l_init_credit,0) - NVL(l_userd_credit,0) - NVL(l_ship_credit,0) ;---剩余额度
--1.创建wf
wf_engine.createprocess(itemtype => l_item_type,
itemkey => l_item_key,
process => l_process,
user_key => l_user_item_key);
--提交人
wf_directory.getusername(p_orig_system => l_orig_system,
p_orig_system_id => l_submit_id,--l_item_req_wf.created_by,
p_name => l_submit_name,
p_display_name => l_submit_dsp_name);
wf_engine.setitemattrnumber(itemtype => l_item_type,
itemkey => l_item_key,
aname => 'SUBMITER_ID',
avalue => l_submit_id);--l_item_req_wf.created_by);
wf_engine.setitemattrtext(itemtype => l_item_type,
itemkey => l_item_key,
aname => 'SUBMITER_NAME',
avalue => l_submit_name);
wf_engine.setitemattrtext(itemtype => l_item_type,
itemkey => l_item_key,
aname => 'SUBMITER_DSP_NAME',
avalue => l_submit_dsp_name);
--审批人
wf_directory.getusername(p_orig_system => l_orig_system,
p_orig_system_id => l_approve_id,--l_item_req_wf.buyer_id,
p_name => l_approve_name,
p_display_name => l_approve_dsp_name);
wf_engine.setitemattrnumber(itemtype => l_item_type,
itemkey => l_item_key,
aname => 'APPROVER_ID',
avalue => l_approve_id);--l_item_req_wf.buyer_id);
wf_engine.setitemattrtext(itemtype => l_item_type,
itemkey => l_item_key,
aname => 'APPROVER_NAME',
avalue => l_approve_name);
wf_engine.setitemattrtext(itemtype => l_item_type,
itemkey => l_item_key,
aname => 'APPROVER_DSP_NAME',
avalue => l_approve_dsp_name);
------------------------------资料属性set
wf_engine.setitemattrtext(itemtype => l_item_type,--单号 ID
itemkey => l_item_key,
aname => 'DELIVERY_HEADER_ID',
avalue => v_header_id);
wf_engine.setitemattrtext(itemtype => l_item_type,--customer
itemkey => l_item_key,
aname => 'CUSTOMER_NAME',
avalue => L_DELIVERY_HEADER.CUSTOMER_NAME);
wf_engine.setitemattrtext(itemtype => l_item_type,--出货通知单号
itemkey => l_item_key,
aname => 'DELIVERY_NO',
avalue => L_DELIVERY_HEADER.NOTICE_NUMBER);
wf_engine.setitemattrtext(itemtype => l_item_type,--信用额度
itemkey => l_item_key,
aname => 'INIT_CREDIT',
avalue => l_init_credit);
wf_engine.setitemattrtext(itemtype => l_item_type,--已用额度
itemkey => l_item_key,
aname => 'USERD_CREDIT',
avalue => l_userd_credit);
wf_engine.setitemattrtext(itemtype => l_item_type,--出货额度
itemkey => l_item_key,
aname => 'SHIP_CREDIT',
avalue => l_ship_credit);
wf_engine.setitemattrtext(itemtype => l_item_type,--剩余额度
itemkey => l_item_key,
aname => 'REMAIN_CREDIT',
avalue => l_remain_credit);
-------------------------document set
/*wf_engine.setitemattrtext(itemtype => l_item_type,
itemkey => l_item_key,
aname => 'OPEN_FORM_COMMAND',
avalue => 'FND_FNDSCAUS');*/
--设置Document类型Attribute
wf_engine.setitemattrtext(itemtype => l_item_type,
itemkey => l_item_key,
aname => 'DOCUMENT_DETAILS',
avalue => 'PLSQLCLOB:JW_SHIP_APPROVE_WF_PKG.GET_DOCUMENT_DETAILS/' ||
l_item_type || ':' || l_item_key);
--3.启动
wf_engine.startprocess(itemtype => l_item_type, itemkey => l_item_key);
commit;
update JW_OM_DELIVERY_HEADERS h
set h.attribute3 = l_item_key
where h.delivery_header_id = v_header_id ;
commit;
null;
end;
end JW_SHIP_APPROVE_WF_PKG;
学习workflow有一个很好的基础技术开发文档：深入浅出Oracle之Workflow实例详解.doc
可百度下载学习即可，我就是从这个文档中学习的
5441
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
