create or replace package body pkg_bba_rcv_pre is
function lf_get_value(p_xml in xmltype, p_string in varchar2)
return varchar2 is
begin
return p_xml.extract(p_string).getstringval();
exception
when others then
return null;
end;
--收货预测处理类
procedure pre_route(p_xml_param in clob, --所有参数集合在xml中
p_out_cursor out sys_refcursor, --返回游标
p_out_number in out number, --返回数字型
p_out_string in out varchar2, --返回字符串
p_out_xml out clob, --返回xml数据
p_status out varchar2, --执行状态 OK/FAILE
p_message out varchar2 --执行信息
) is
l_xml_param xmltype;
l_function varchar2(500);
l_op_by varchar2(100);
p_pre_id varchar2(255); -- 预测ID
p_rcv_type varchar2(255); -- 收货类型:(Local / Import)
p_supplier_name varchar2(255); -- 供应商名称
p_po_number varchar2(255); -- 订单号
p_invoice varchar2(255); -- 发票
p_arrived_date varchar2(255); -- 到达时间
p_estimated_date varchar2(255); -- 预计车辆达到时间
p_item_process varchar2(1000);
p_boxes_qty varchar2(255); -- 箱子数量
p_trucks_qty varchar2(255); -- 车辆数量
p_state varchar2(255); -- 状态
p_remark varchar2(255); -- 对外型号
p_deliver_by varchar2(255); -- 送货人
p_deliver_date varchar2(255); -- 送货时间
p_estimated_date_begin varchar2(255); -- 时间
p_estimated_date_end varchar2(255); -- 时间
p_asn_no varchar2(255); -- ASN号
p_asn_state varchar2(255); -- ASN状态
p_reg_flag varchar2(255);
v_sql varchar2(2000);
v_condition varchar2(2000);
begin
p_status := 'FAIL';
p_message := 'Unknown error!';
p_out_number := -1;
p_out_string := 'X';
p_out_xml := 'X';
open p_out_cursor for
select null from dual where 1 = 0;
l_xml_param := xmltype.createxml(p_xml_param);
l_op_by := lf_get_value(l_xml_param, '/ROOT/OP_BY/text()');
l_function := lf_get_value(l_xml_param, '/ROOT/FUNCTION/text()');
case l_function
when 'PRE_SELECT_INFO' then
/*XML_PARAM 格式要求：
<ROOT>
<FUNCTION>PRE_SELECT_INFO</FUNCTION>
<OP_BY>实际用户名</OP_BY>
<PARAM>
<P_PRE_ID>P_PRE_ID</P_PRE_ID>
<P_INVOICE>P_INVOICE</P_INVOICE>
<P_SUPPLIER_NAME>P_SUPPLIER_NAME</P_SUPPLIER_NAME>
<P_RCV_TYPE>P_RCV_TYPE</P_RCV_TYPE>
<P_ESTIMATED_DATE_BEGIN>P_ESTIMATED_DATE_BEGIN</P_ESTIMATED_DATE_BEGIN>
<P_ESTIMATED_DATE_END>P_ESTIMATED_DATE_END</P_ESTIMATED_DATE_END>
<p_ASN_NO>10000<p_ASN_NO>
<p_ASN_STATE>1<p_ASN_STATE>
<P_REG_FLAG>Y<P_REG_FLAG>
</PARAM>
</ROOT>
*/
begin
p_pre_id := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_PRE_ID/text()');
p_invoice := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_INVOICE/text()');
p_supplier_name := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_SUPPLIER_NAME/text()');
p_rcv_type := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_RCV_TYPE/text()');
p_po_number := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_PO_NUMBER/text()');
p_estimated_date_begin := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_ESTIMATED_DATE_BEGIN/text()');
p_estimated_date_end := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_ESTIMATED_DATE_END/text()');
p_asn_no := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_ASN_NO/text()');
p_asn_state := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_ASN_STATE/text()');
p_reg_flag := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_REG_FLAG/text()');
v_sql := 'SELECT pre_id, rcv_type, supplier_name, po_number, invoice, estimated_date, boxes_qty,ITEM_PROCESS, trucks_qty, state, arrived_date, remark, create_by, create_date, last_update_by, last_update_date, deliver_by,asn_no,asn_state
,reg_flag,deliver_date FROM lmes_bba_rcv_reg_pre where 1=1 ';
if p_pre_id is not null then
v_condition := v_condition || ' and pre_id=' || p_pre_id;
end if;
if p_invoice is not null then
v_condition := v_condition || ' and invoice=''' || p_invoice || '''';
end if;
if p_supplier_name is not null then
v_condition := v_condition || ' and supplier_name like ''' ||
p_supplier_name || '''';
end if;
if p_rcv_type is not null then
v_condition := v_condition || ' and rcv_type=''' || p_rcv_type || '''';
end if;
if p_po_number is not null then
v_condition := v_condition || ' and po_number=''' ||
p_po_number || '''';
end if;
if p_estimated_date_begin is not null then
v_condition := v_condition ||
' and ESTIMATED_DATE >= to_date(''' ||
p_estimated_date_begin || ''',''mm/dd/yyyy'')';
end if;
if p_estimated_date_end is not null then
v_condition := v_condition ||
' and ESTIMATED_DATE <= to_date(''' ||
p_estimated_date_end || ''',''mm/dd/yyyy'') ';
end if;
if p_asn_no is not null then
v_condition := v_condition || ' and asn_no=' || p_asn_no;
end if;
if p_asn_state is not null then
v_condition := v_condition || ' and asn_state=' || p_asn_state;
end if;
if p_reg_flag is not null then
v_condition := v_condition || ' and reg_flag=''' || p_reg_flag || '''';
end if;
v_sql := v_sql || v_condition;
open p_out_cursor for v_sql;
p_status := 'OK';
p_message := 'Success';
exception
when others then
begin
p_status := 'FAIL';
p_message := substr(sqlcode || sqlerrm, 1, 200);
end;
end;
/*XML_PARAM 格式要求：
<ROOT>
<FUNCTION>PRE_INSERT_INFO</FUNCTION>
<OP_BY>实际用户名</OP_BY>
<PARAM>
<P_RCV_TYPE>P_RCV_TYPE</P_RCV_TYPE>
<P_SUPPLIER_NAME>P_SUPPLIER_NAME</P_SUPPLIER_NAME>
<P_PO_NUMBER>P_PO_NUMBER</P_PO_NUMBER>
<P_INVOICE>P_INVOICE</P_INVOICE>
<P_ESTIMATED_DATE>P_ESTIMATED_DATE</P_ESTIMATED_DATE>
<P_BOXES_QTY>P_BOXES_QTY</P_BOXES_QTY>
<P_TRUCKS_QTY>P_TRUCKS_QTY</P_TRUCKS_QTY>
<P_STATE>P_STATE</P_STATE>
<P_REMARK>P_REMARK</P_REMARK>
<P_DELIVER_BY>P_DELIVER_BY</P_DELIVER_BY>
<P_DELIVER_DATE>P_DELIVER_DATE</P_DELIVER_DATE>
</PARAM>
</ROOT>
*/
when 'PRE_INSERT_INFO' then
begin
p_rcv_type := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_RCV_TYPE/text()');
p_supplier_name := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_SUPPLIER_NAME/text()');
p_po_number := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_PO_NUMBER/text()');
p_invoice := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_INVOICE/text()');
p_estimated_date := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_ESTIMATED_DATE/text()');
p_boxes_qty := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_BOXES_QTY/text()');
p_trucks_qty := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_TRUCKS_QTY/text()');
p_state := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_STATE/text()');
p_remark := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_REMARK/text()');
p_deliver_by := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_DELIVER_BY/text()');
p_deliver_date := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_DELIVER_DATE/text()');
p_item_process := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_ITEM_PROCESS/text()');
insert into lmes_bba_rcv_reg_pre
(pre_id,
rcv_type,
supplier_name,
po_number,
invoice,
estimated_date,
boxes_qty,
item_process,
trucks_qty,
state,
arrived_date,
remark,
create_by,
create_date,
last_update_by,
last_update_date,
deliver_by,
deliver_date)
values
(lmes_bba_rcv_reg_pre_s.nextval,
p_rcv_type,
p_supplier_name,
p_po_number,
p_invoice,
to_date(p_estimated_date, 'mm/dd/yyyy hh24:mi:ss'),
p_boxes_qty,
p_item_process,
p_trucks_qty,
p_state,
null,
p_remark,
l_op_by,
sysdate,
'',
null,
p_deliver_by,
to_date(p_deliver_date, 'mm/dd/yyyy hh24:mi:ss'));
commit; --提交
p_status := 'OK';
p_message := 'Success';
exception
when others then
begin
rollback; --回滚
p_status := 'FAIL';
p_message := substr(sqlcode || sqlerrm, 1, 200);
end;
end;
when 'PRE_UPDATE_ARRIVED' then
/* --XML_PARAM 格式要求：
<ROOT>
<FUNCTION>PRE_UPDATE_ARRIVED</FUNCTION>
<OP_BY>实际用户名</OP_BY>
<PARAM>
<P_PRE_ID>P_PRE_ID</P_PRE_ID>
<P_STATE>P_STATE</P_STATE>
</PARAM>
</ROOT>
*/
begin
p_pre_id := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_PRE_ID/text()');
p_state := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_STATE/text()');
/*
UPDATE LMES_BBA_RCV_REG_PRE
SET STATE = P_STATE,
ARRIVED_DATE = SYSDATE,
LAST_UPDATE_BY = L_OP_BY,
LAST_UPDATE_DATE = SYSDATE
WHERE PRE_ID IN F_SPLIT(P_PRE_ID,',') ;*/
if p_pre_id is null then
p_status := 'FAIL';
p_message := 'PRE_ID can be not null';
return;
end if;
v_sql := ' UPDATE LMES_BBA_RCV_REG_PRE SET STATE = ''' || p_state ||
''', ARRIVED_DATE = SYSDATE, LAST_UPDATE_BY =''' ||
l_op_by ||
''', LAST_UPDATE_DATE = SYSDATE WHERE NVL(state,'' '')<> ''ARRIVED'' and PRE_ID IN (' ||
p_pre_id || ')';
execute immediate v_sql;
commit; --提交
p_status := 'OK';
p_message := 'Success';
exception
when others then
begin
rollback; --回滚
p_status := 'FAIL';
p_message := substr(sqlcode || sqlerrm, 1, 200);
end;
end;
when 'PRE_UPDATE_INFO' then
/* --XML_PARAM 格式要求：
<ROOT>
<FUNCTION>PRE_UPDATE_INFO</FUNCTION>
<OP_BY>实际用户名</OP_BY>
<PARAM>
<P_PRE_ID>P_PRE_ID</P_PRE_ID>
<P_RCV_TYPE>P_RCV_TYPE</P_RCV_TYPE>
<P_SUPPLIER_NAME>P_SUPPLIER_NAME</P_SUPPLIER_NAME>
<P_PO_NUMBER>P_PO_NUMBER</P_PO_NUMBER>
<P_INVOICE>P_INVOICE</P_INVOICE>
<P_ESTIMATED_DATE>P_ESTIMATED_DATE</P_ESTIMATED_DATE>
<P_BOXES_QTY>P_BOXES_QTY</P_BOXES_QTY>
<P_TRUCKS_QTY>P_TRUCKS_QTY</P_TRUCKS_QTY>
<P_ARRIVED_DATE>P_ARRIVED_DATE</P_ARRIVED_DATE>
<P_STATE>P_STATE</P_STATE>
<P_REMARK>P_REMARK</P_REMARK>
</PARAM>
</ROOT>
*/
begin
p_pre_id := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_PRE_ID/text()');
p_rcv_type := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_RCV_TYPE/text()');
p_supplier_name := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_SUPPLIER_NAME/text()');
p_po_number := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_PO_NUMBER/text()');
p_invoice := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_INVOICE/text()');
p_estimated_date := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_ESTIMATED_DATE/text()');
p_boxes_qty := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_BOXES_QTY/text()');
p_trucks_qty := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_TRUCKS_QTY/text()');
p_item_process := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_ITEM_PROCESS/text()');
/*P_ARRIVED_DATE := LF_GET_VALUE(L_XML_PARAM,
'/ROOT/PARAM/P_ARRIVED_DATE/text()');*/
p_state := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_STATE/text()');
p_remark := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_REMARK/text()');
update lmes_bba_rcv_reg_pre
set pre_id = p_pre_id,
rcv_type = p_rcv_type,
supplier_name = p_supplier_name,
po_number = p_po_number,
invoice = p_invoice,
estimated_date = to_date(p_estimated_date,
'mm/dd/yyyy hh24:mi:ss'),
boxes_qty = p_boxes_qty,
item_process = p_item_process,
trucks_qty = p_trucks_qty,
state = p_state,
--ARRIVED_DATE = P_ARRIVED_DATE,
remark = p_remark,
last_update_by = l_op_by,
last_update_date = sysdate
where pre_id = p_pre_id;
commit; --提交
p_status := 'OK';
p_message := 'Success';
exception
when others then
begin
rollback; --回滚
p_status := 'FAIL';
p_message := substr(sqlcode || sqlerrm, 1, 200);
end;
end;
when 'PRE_DELETE_INFO' then
/* --XML_PARAM 格式要求：
<ROOT>
<FUNCTION>PRE_DELETE_INFO</FUNCTION>
<OP_BY>实际用户名</OP_BY>
<PARAM>
<P_PRE_ID>P_PRE_ID</P_PRE_ID>
</PARAM>
</ROOT>
*/
begin
p_pre_id := lf_get_value(l_xml_param,
'/ROOT/PARAM/P_PRE_ID/text()');
delete lmes_bba_rcv_reg_pre where pre_id = p_pre_id;
commit; --提交
p_status := 'OK';
p_message := 'Success';
exception
when others then
begin
rollback; --回滚
p_status := 'FAIL';
p_message := substr(sqlcode || sqlerrm, 1, 200);
end;
end;
else
p_message := 'Undefined FUNCTION:' || l_function;
end case;
end;
end pkg_bba_rcv_pre;
浙公网安备 33010602011771号