使用workflow客制出货通知单
来源：互联网 发布：tdee计算器软件 编辑：程序博客网 时间：2024/06/10 12:04
需求：商务人员提出货通知单，纸质单送财务审核，然后财务系统中审核信用额度等信息方才批准出货。
但是晚上或节假日财务不上班，然后工厂急着出货就会导致出货延迟耽误出货，
故客制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
可百度下载学习即可，我就是从这个文档中学习的
0 0
- 使用workflow客制出货通知单
- 使用API创建AR 贷项通知单
- WorkFlow简单使用
- 通知单增强
- 使用SharePoint自带的Workflow(二) 使用workflow
- Workflow
- Workflow
- workflow
- workflow
- workflow
- workflow
- 使用SharePoint自带的Workflow(一) 开启workflow
- Oracle EBS Workflow简介和客制之后的感想
- 出货测试
- 借项通知单 & 贷项通知单
- 贷项通知单
- 门诊-入院通知单
- 职员试用通知单
- 杂
- struts2文件上传
- Swing批量重命名并导出excel
- 集群 和 分布式
- C++库汇总
- 使用workflow客制出货通知单
- 如何自学Android编程
- seandroid 如何添加被denied的权限
- linux调度器源码研究 - 概述（一）
- lightoj 1219 - Mafia 贪心
- linux用一键安装禅道
- Discuz! X中showmessage函数各参数用法解析
- 图的深度优先搜索
- Block的简单使用
