service.java
Map<String, Object> params = new HashMap<String, Object> ();
params.put("bill_no", billNo);
checkListDao.synchronizeAc(params);
String rtn_code = params.get("rtn_code") == null?"" : params.get("rtn_code").toString();
if("S".equals(rtn_code)){
logger.info("================检验单审批通过,嵌入到流程中pp_ac_head pp_ac_detail============成功!!!");
}else{
String rtn_message = params.get("rtn_message") == null?"" : params.get("rtn_message").toString();
logger.error("================检验单审批通过,嵌入到流程中pp_ac_head pp_ac_detail============失败!!!"+rtn_message);
}
dao.java
public List<Map<String, Object>> synchronizeAc(Map<String, Object> params);
mapper.xml
<!-- ==========================嵌入到流程中pp_ac_head pp_ac_detail================================ -->
<select id="synchronizeAc" statementType="CALLABLE" parameterType="java.util.HashMap">
<![CDATA[
{CALL P_SYNCHRONIZE_AC(
#{rtn_code,mode=OUT,jdbcType=VARCHAR},
#{rtn_message,mode=OUT,jdbcType=VARCHAR},
#{bill_no,mode=IN,jdbcType=VARCHAR}
)
}
]]>
</select>
oracle 存储过程:
CREATE OR REPLACE PROCEDURE P_SYNCHRONIZE_AC(X_RTN_STATUS OUT VARCHAR2,
X_RTN_MESSAGE OUT VARCHAR2,
V_BILL_NO IN VARCHAR2) AS
--头表
cursor head_cur is
SELECT E.PO_ID AS po_head_id,
E.PO_NO AS po_head_no,
H.id,
H.bill_no,
H.status,
H.node_id,
H.form_type,
H.check_type,
H.submiter,
H.submit_date,
H.create_by,
H.create_date,
H.last_update_by,
H.last_update_date,
H.delivery_code,
H.first_approver,
H.second_approver,
H.third_approver,
H.notice_person,
H.enable_flag
FROM PP_MATERIEL_CHECK_HEAD H
LEFT JOIN PP_MATERIEL_CHECK_INFO O
ON O.PP_MATERIEL_CHECK_HEAD_ID = H.ID
LEFT JOIN APP_SRM.PP_PO_HEAD E
ON O.PO_NO = E.PO_NO
LEFT JOIN pp_form_approver V
ON V.FORM_ID = H.BILL_NO
AND V.REOUTE_ID = H.NODE_ID
WHERE H.enable_flag = '1'
and h.bill_no = V_BILL_NO;
--行表
cursor line_cur is
SELECT
o.id as AC_DETAIL_ID,
E.PO_ID as po_head_id ,
L.PO_DETAIL_ID as po_detail_id ,
O.SUPPLIY_NO as supplier_no ,
O.ORDER_QTY as po_qty ,
O.CHECK_QTY as ac_rec_qty ,
O.ORDER_QTY - O.CHECK_QTY AS AC_UNREC_QTY,
L.REQ_DATE,
O.CHECK_DATE as real_arrived_date ,
O.ARRIVAL_ADDRESS as arrive_address ,
O.CREATE_BY as created_by ,
O.CREATE_DATE as created_date ,
O.LAST_UPDATE_BY as last_updated_by ,
O.LAST_UPDATE_DATE as last_updated_date ,
O.ENABLE_FLAG as enabled_flag ,
O.ITEM_NO,
O.PP_MATERIEL_CHECK_HEAD_ID as ac_head_id ,
L.LOCAL_PRICE as use_money ,
L.ITEM_ID
FROM PP_MATERIEL_CHECK_HEAD H
LEFT JOIN PP_MATERIEL_CHECK_INFO O
ON O.PP_MATERIEL_CHECK_HEAD_ID = H.ID
LEFT JOIN APP_SRM.PP_PO_HEAD E
ON O.PO_NO = E.PO_NO
LEFT JOIN pp_form_approver V
ON V.FORM_ID = H.BILL_NO
AND V.REOUTE_ID = H.NODE_ID
LEFT JOIN PP_PO_DETAIL L
ON E.PO_ID = L.PO_ID
WHERE H.enable_flag = '1'
and o.enable_flag = '1'
and h.bill_no = V_BILL_NO;
--添加 送货单主表
BEGIN
--插入头信息
for h in head_cur loop
INSERT INTO PP_AC_HEAD
(ac_head_id,po_head_id,
created_by,dept_no,
created_date,
last_updated_by,
last_updated_date,
enabled_flag,content_summary,
verifypeople1,
verifypeople2,
verifypeople3,
notifier,
ac_head_no,
state,form_data_id,po_head_no,
ac_type,
source)
VALUES
(h.id,h.po_head_id,
h.create_by,h.create_by,
SYSDATE,
h.last_update_by,
SYSDATE,
1,h.bill_no,
h.first_approver,
h.second_approver,
h.third_approver,
h.notice_person,
h.bill_no,
30,h.bill_no,h.po_head_no,
h.check_type,
'CK');
end loop;
--插入行信息
for L in line_cur loop
INSERT INTO pp_ac_detail
( ac_detail_id,
po_head_id,
po_detail_id,
supplier_no,
po_qty,
ac_rec_qty,
ac_unrec_qty,
req_date,
real_arrived_date,
arrive_address,
created_by,
created_date,
last_updated_by,
last_updated_date,
enabled_flag,
item_no,
ac_head_id,
use_money,
item_id
)
VALUES
( L.ac_detail_id,
L.po_head_id,
L.po_detail_id,
L.supplier_no,
L.po_qty,
L.ac_rec_qty,
L.ac_unrec_qty,
L.req_date,
L.real_arrived_date,
L.arrive_address,
L.created_by,
L.created_date,
L.last_updated_by,
L.last_updated_date,
L.enabled_flag,
L.item_no,
L.ac_head_id,
L.use_money,
L.item_id
);
end loop;
COMMIT;
X_RTN_STATUS := 'S';
X_RTN_MESSAGE := 'success';
EXCEPTION
--向外界抛出过程执行不成功的异常
WHEN OTHERS THEN
ROLLBACK;
X_RTN_STATUS := 'E';
X_RTN_MESSAGE := SUBSTR(SQLERRM, 1, 3600);
--写执行失败信息
COMMIT;
END P_SYNCHRONIZE_AC;