批量导入po接口生成po的代码

来源：互联网发布：首都图书馆知乎编辑：程序博客网时间：2024/04/29 22:09

批量导入po接口生成po的代码：

create or replace package body XXOM_PO_IMPORT is
/* create by yong.li 20110610 */
FUNCTION XXGEM_split(pi_str Varchar2, pi_separator Varchar2)
RETURN t_Array IS

idx Number; --分隔符在字符串中的位置
currentindex Number := 1; --数组的索引
strs t_Array; --返回值
v_str varchar2(4000) := pi_str; --临时变量
BEGIN

IF pi_str IS NOT NULL AND pi_separator IS NOT NULL THEN
--存在分隔符
IF INSTR(v_str, pi_separator) <> 0 THEN
LOOP
EXIT WHEN v_str IS NULL;
EXIT WHEN INSTR(v_str, pi_separator) = 0;
idx := INSTR(v_str, pi_separator);
--截取字符串赋值给对应的数组
strs(currentindex) := SUBSTR(v_str, 0, idx - 1);
currentindex := currentindex + 1;
--更新字符串
v_str := SUBSTR(v_str, idx + 1);
END LOOP;
END IF;
END IF;
--如果分隔符为空，则直接返回字符串
IF v_str IS NOT NULL THEN
strs(currentindex) := v_str;
END IF;
Return strs;
END XXGEM_split;

procedure import_file(l_gfm_id number,
v_table varchar2,
v_fields varchar2,
v_col_separate varchar2,
v_row_separate varchar2,
p_organization_id number) is

v_row_sep varchar2(1);
v_col_sep varchar2(1);


my_field dbms_utility.uncl_array;
my_field_cnt binary_integer;



file_len NUMBER;
file_loc NUMBER; -- file location
temp_data varchar2(2000);

enter_loc NUMBER; -- enter_key location on temp buffer


sql_str1 varchar2(2000);
sql_temp varchar2(2000);
p_first_row_flag varchar2(1) := 'N';

p_remain_seg varchar2(200);
p_first_seg varchar2(200);
p_second_seg varchar2(200);
p_temp_seg varchar2(200);
v_date date;
ret t_array;
begin

-- delete XXWIP_BOM_IMPORT_TEMP;
-- commit;
p_first_row_flag := 'Y';

dbms_utility.comma_to_table(v_fields, my_field_cnt, my_field);
-- insert into XXGEM_LOG values('sql_str1'|| v_table || ' (' || v_fields ||' ) values (');
-- commit;

-- sql_str1 := ' Insert into ' || v_table || ' (' || v_fields || ') values (';





if upper(v_col_separate) = '<TAB>' then
v_col_sep := chr(09);
elsif upper(v_col_separate) <> 'NULL' then
v_col_sep := v_col_separate;
else
v_col_sep := chr(0);
end if;

-- Get Row Separate char
if upper(v_row_separate) = '<ENTER>' then
v_row_sep := chr(13);
elsif upper(v_row_separate) <> 'NULL' then
v_row_sep := v_row_separate;
else
v_row_sep := chr(0);
end if;
------------------------

-- Open File
-- l_gfm_id := open_file;

if l_gfm_id > 0 then

-- Get File Length
select dbms_lob.getlength(file_data)
into file_len
from fnd_lobs
where file_id = l_gfm_id;

-- Select Data


file_loc := 1;
enter_loc := 1;
Loop
-- Find Enter Key Location
select dbms_lob.instr(file_data,
utl_raw.cast_to_raw(v_row_sep),
file_loc,
1)
into enter_loc
from fnd_lobs
where file_id = l_gfm_id;

if enter_loc > 0 then
select utl_raw.cast_to_varchar2(dbms_lob.substr(file_data,
enter_loc -
file_loc,
file_loc))
into temp_data
from fnd_lobs
where file_id = l_gfm_id;

file_loc := enter_loc + 2;
enter_loc := enter_loc + 2;
else
select utl_raw.cast_to_varchar2(dbms_lob.substr(file_data,
1000,
file_loc))
into temp_data
from fnd_lobs
where file_id = l_gfm_id;

file_loc := file_len + 2;
enter_loc := file_len + 2;
end if;

-- the first field is customer name ,it may inculde seperate char,
-- so we must replace the seperate char
temp_data := convert(temp_data, 'UTF8', 'ZHS16GBK');


p_remain_seg := temp_data;
p_first_seg := null;
p_second_seg := null;
p_temp_seg := null;


temp_data := '''' || replace(temp_data, v_col_sep, ''',''') || ''')';

-- sql_str1:='Insert into xxgem.xxgem_bom_temp(ASSEMBLY_ITEM_NAME,common_item_name,UOM,Quantity,Date_From,Date_To,ALTERNATE_BOM_DESIGNATOR,ITEM_SEQ,OPERATION_SEQ,Planning,Comments,Yield,APPROVE_STATE,ORGANIZATION_CODE)';--,ASSEMBLY_ITEM_NAME)';
sql_str1:='Insert into xxgem.xxgem_so_update( cust_name,ordered_item,customer_po,so_price)';--,ASSEMBLY_ITEM_NAME)'; attribute15---batch id


sql_str1:=sql_str1 || ' values ( ';


begin
if p_first_row_flag = 'Y' then
p_first_row_flag := 'N'; -- the first row is title,no need to insert
else

--temp_data:=temp_data||','||ret(16)||'';
-- insert into XXGEM_LOG values(sql_str1||' temp_data data '||temp_data);


sql_temp := sql_str1||temp_data;

execute immediate sql_str1 || temp_data;
end if;

end;
Exit When file_loc >= file_len;
End Loop;

end if;
----------------
-- Delete temp data
if l_gfm_id >= 0 then
null;


end if;



commit;

end import_file;
--
/* create by yong.li 20110621 */
procedure SUBMIT_REQUEST(p_org_id number, P_update_PO VARCHAR2, P_update_price VARCHAR2 ,p_request_id out number) IS
v_request_id number;
v_ERRBUF VARCHAR2(2000);
v_RETCODE VARCHAR2(2000);


begin
-- FND_FILE.PUT_LINE(FND_FILE.LOG, P_EFFECTIVE||P_Comments);


v_request_id := fnd_request.submit_request( 'XXGEM', 'XXGEM_SO_UPDATE', '', Sysdate, false,
-- P_Comments, P_EFFECTIVE, CHR(0), '', '', '', '', '', '', '',
P_update_PO, P_update_price, CHR(0),'', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '' );


if v_request_id>0 then
p_request_id:=v_request_id;
end if;




end SUBMIT_REQUEST;



end XXOM_PO_IMPORT;

0 0