OM接口导出数据[@more@]

create or replace package cux_dz_interface as

procedure insert_delivery(Errbuf Out Varchar2,

Retcode Out Number,

p_delivery_id in number,

p_date_all in varchar2,

p_delivery_request in varchar2);

end cux_dz_interface;

/

create or replace package body cux_dz_interface as

procedure report_line(p_text varchar2) is

begin

dbms_output.put_line(p_text);

fnd_file.PUT_LINE(fnd_file.LOG, p_text);

fnd_file.PUT_LINE(fnd_file.OUTPUT, p_text);

end;

function cur_data(p_delivery_id in number) return varchar2 is

--校验以下字段是否为空

ls_order_number number;

ls_line_num number;

ls_long number;

ls_width number;

ls_high number;

ls_per_set number;

ls_box_set number;

ls_gross_weight number;

ls_net_weight number;

ls_front_mark varchar2(150);

ls_side_mark varchar2(150);

ls_receive_side varchar2(150);

ls_send_side varchar2(150);

ls_ship_to_port varchar2(150);

ls_ship_from_port varchar2(150);

ls_carrier_name varchar2(150);

report_line1 varchar2(100);

report_all varchar2(2000);

-- output_flag number := 0;

--flag varchar2(200);

--

cursor c_inffect is

select boh.order_number,

bol.line_num,

bol.measure_longth,

bol.measure_width,

bol.measure_high,

bol.gross_weight,

bol.net_weight,

bol.quantities_per_set,

bol.sets_per_box,

boh.front_mark,

boh.side_mark,

boh.ship_from_port,

boh.ship_to_port,

boh.receive_side,

boh.send_side,

boh.carrier_name

from bf_order_headers boh,

bf_order_lines bol,

wsh_delivery_assignments wda,

wsh_delivery_details wdd,

oe_order_headers_all ooh,

oe_order_lines_all ool

where ool.line_number = bol.line_num and

ooh.order_number = boh.order_number and

ooh.header_id = ool.header_id and

wdd.source_header_id = ool.header_id and

wdd.source_line_id = ool.line_id and

wdd.delivery_detail_id = wda.delivery_detail_id and

bol.header_id = boh.header_id and

wda.delivery_id = p_delivery_id;

--判断以下字段是否为空

begin

open c_inffect;

loop

fetch c_inffect

into ls_order_number, ls_line_num, ls_long, ls_width, ls_high, ls_gross_weight, ls_net_weight, ls_per_set, ls_box_set, ls_front_mark, ls_side_mark, ls_ship_from_port, ls_ship_to_port, ls_receive_side, ls_send_side, ls_carrier_name;

exit when c_inffect%notfound;

if ls_long is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number || '，' ||

'行号 = ' || ls_line_num || ' 字段 "长" 为空';

-- output_flag := output_flag + 1;

report_all := report_line1;

end if;

if ls_width is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number || '，' ||

'行号 = ' || ls_line_num || ' 字段 "宽" 为空';

-- output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

end if;

if ls_high is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number || '，' ||

'行号 = ' || ls_line_num || ' 字段 "高" 为空';

-- output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

end if;

if ls_gross_weight is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number || '，' ||

'行号 = ' || ls_line_num || ' 字段 "毛重" 为空';

-- output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

end if;

if ls_net_weight is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number || '，' ||

'行号 = ' || ls_line_num || ' 字段 "净重" 为空';

-- output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

end if;

if ls_per_set is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number || '，' ||

'行号 = ' || ls_line_num || ' 字段 "每套几支" 为空';

-- output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

end if;

if ls_box_set is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number || '，' ||

'行号 = ' || ls_line_num || ' 字段 "每箱几套" 为空';

-- output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

--return report_all;

end if;

if ls_front_mark is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number ||

' 字段 "正唛" 为空';

-- output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

end if;

if ls_side_mark is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number ||

' 字段 "侧唛" 为空';

-- output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

end if;

if ls_ship_from_port is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number ||

' 字段 "启运港" 为空';

--output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

end if;

if ls_ship_to_port is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number ||

' 字段 "目的港" 为空';

--output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

end if;

if ls_receive_side is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number ||

' 字段 "收货人" 为空';

-- output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

end if;

if ls_send_side is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number ||

' 字段 "发运人" 为空';

--output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

end if;

if ls_carrier_name is null then

report_line1 := '警告! ' || '订单号 = ' || ls_order_number ||

' 字段 "货代名称" 为空';

--output_flag := output_flag + 1;

report_all := report_all || chr(10) || report_line1;

end if;

end loop;

close c_inffect;

report_all := trim(report_all);

return report_all;

end;

/*check function : -1:error; 0:OK 1:exist interface table*/

function check_delivery(p_delivery_id number) return number is

--

--l_error_code

l_flag number;

--

begin

select count(*)

into l_flag

from bf_dms_interface bd

where bd.delivery_id = p_delivery_id;

if l_flag > 0 then

report_line('delivery ID = ' || p_delivery_id || ' already exist!');

return(1);

else

return(0);

end if;

--return ();

end;

procedure insert_delivery(Errbuf Out Varchar2,

Retcode Out Number,

p_delivery_id in number,

p_date_all in varchar2,

p_delivery_request in varchar2) is

l_error_code number; --此交货号是否被导入标志

l_flag number;

report_all varchar2(2000);

--flag varchar2(300);

begin

report_all := cur_data(p_delivery_id);

-----------------------如有不全记录，返回

if report_all is not null then

report_line(report_all || chr(10) || '导入失败！');

return;

end if;

--

--report_line('aaaaasdgsgda');

--report_line('aaaa' || p_date_all);

l_error_code := check_delivery(p_delivery_id); --判断是否此已经交货号已经存在

--

if l_error_code = 0 then

insert into bf_dms_interface

select DISTINCT wda.created_by,

wda.creation_date,

wda.last_updated_by,

wda.last_update_date,

wda.last_update_login,

wdd.org_id,

wdd.organization_id,

wdd.delivery_detail_id,

to_number(decode(wdd.container_flag,

'N',

wdd.delivery_detail_id,

NULL)) delivery_line_id, --行号

wda.delivery_id, --交货号

wdd.source_header_id,

wdd.source_line_id,

boh.order_number,

wdd.source_line_number,

boh.currency_code,

boh.pay_term_id, --收款方式

rtv.name pay_term_name, --收款方式名称

boh.customer_id,

boh.cust_po_number, --客户号

acv.customer_name, --客户名字

boh.bill_to_site_id, --开单地点

rsu1.site_use_code, --开单地点,

boh.ship_to_site_id, --发运地点

rsu2.site_use_code, --发运地点

wdd.inventory_item_id, --商品号

msi.segment1,

mst1.description,

mst2.description,

bol.transaction_uom,

bol.ordered_quantity,

bol.unit_price,

bol.ordered_quantity * bol.unit_price amount, --金额

'', --订单金额

boh.schedule_finish_date, --预定交期

boh.salesrep_id,

raa.salesrep_number,

raa.name salesrep_name, --业务员

aav.territory_short_name,

aav.postal_code, --territory_short_code

boh.price_term_code, --价格条款

fnd1.meaning, -- price_term_name

boh.ship_from_port,

boh.ship_to_port,

boh.ship_method_code,

bol.measure_longth,

bol.measure_width,

bol.measure_high,

bol.gross_weight,

bol.net_weight,

bol.quantities_per_set,

bol.sets_per_box,

boh.commission_type,

boh.discount,

boh.front_mark,

boh.side_mark,

boh.front_mark_pic,

boh.side_mark_pic,

bol.mark_description,

bol.packing_pic,

boh.insure_benifit,

boh.insure_informer,

boh.send_side,

boh.receive_side,

boh.carrier_id,

boh.carrier_name,

boh.carrier_contact,

boh.carrier_phone,

boh.carrier_fax,

BII.ITEM_BYNAME,

'',

to_date(p_date_all, 'YYYY-MM-DD HH24:MI:SS'),

p_delivery_request,

trunc(sysdate)

from wsh_delivery_assignments wda,

wsh_delivery_details wdd,

bf_order_headers boh,

bf_order_lines bol,

oe_order_headers_all ooh,

oe_order_lines_all ool,

ra_site_uses_all rsu1, --开单地点

ra_site_uses_all rsu2, --发运地点

ra_site_uses_all rsu3,

RA_TERMS_VL rtv, --收款条件

ar_customers_v acv, --客户

ra_salesreps_all raa, --业务员*/

mtl_system_items_b msi, --物料

mtl_system_items_tl mst1, --物料

mtl_system_items_tl mst2,

ar_addresses_v aav, --地址表

fnd_lookup_values fnd1,

fnd_lookup_types fnd2,

BF_INVENTORY_ITEMS BII

where boh.pay_term_id = rtv.term_id and --收款条件

acv.customer_id = boh.customer_id and --客户

rsu1.site_use_id = boh.bill_to_site_id and --开单地点

rsu2.site_use_id = boh.ship_to_site_id and --发运地点

raa.salesrep_id = boh.salesrep_id and --业务员

msi.organization_id = bol.ORGANIZATION_ID and

msi.inventory_item_id = bol.inventory_item_id and --物料

msi.organization_id = mst1.organization_id and

msi.inventory_item_id = mst1.inventory_item_id and

mst1.language = 'US' and --英文描述

msi.organization_id = mst2.organization_id and

msi.inventory_item_id = mst2.inventory_item_id and

mst2.language = 'ZHS' and --中文描述

boh.ship_to_site_id = rsu3.site_use_id and

rsu3.address_id = aav.address_id(+) and

fnd1.lookup_code = boh.price_term_code and --价格条款

fnd2.lookup_type = fnd1.lookup_type and

fnd1.view_application_id = fnd2.application_id and

fnd1.language = 'ZHS' and fnd2.application_id = 222 and --价格条款*/

bol.header_id = boh.header_id and

wdd.delivery_detail_id = wda.delivery_detail_id and

ooh.order_number = boh.order_number and

ooh.header_id = ool.header_id and

wdd.source_header_id = ool.header_id and

wdd.source_line_id = ool.line_id and

ool.line_number = bol.line_num and

ool.shipment_number = bol.shipment_number and

wda.delivery_id = p_delivery_id AND

BII.ORDER_LINE_ID(+)= BOL.LINE_ID;

commit;

select count(*)

into l_flag

from bf_dms_interface

where delivery_id = p_delivery_id;

if l_flag > 0 then

-----成功标志

report_line('delivery ID = ' || p_delivery_id ||

'is inputed successful!');

--------回写标志

update bf_order_headers

set transfered_to_dms = 'Y',

transfered_by = fnd_profile.value('USER_ID'),

transfer_date = sysdate

where order_number in

(select source_header_number

from bf_dms_interface

where delivery_id = p_delivery_id);

update bf_order_lines

set transfered_to_dms = 'Y',

transfered_by = fnd_profile.value('USER_ID'),

transfer_date = sysdate

where (line_num, shipment_number, header_id) in

(select distinct ool.line_number,

ool.shipment_number,

boh.header_id

from bf_dms_interface bdi,

bf_order_headers boh,

oe_order_lines_all ool,

oe_order_headers_all ooh

where bdi.source_header_number = boh.order_number and

ool.line_id = bdi.source_line_id and

ooh.order_number = boh.order_number and

delivery_id = p_delivery_id);

--写入队列表

insert into dms_delivery_stack

(load_id, delivery_id)

values

(cux.dms_delivery_stack_s.nextval, p_delivery_id);

commit;

/* if sqlca.sqlcode <> 100 then

report_line('数据提交失败！')

rollback;

end if*/

else

report_line('数据有误！导入失败！');

end if;

end if;

exception

when others then

report_line('Erro!');

return;

end;

end cux_dz_interface;

/