首页 > 代码库 > Form_通过FND_FNDFLUPL标准功能上传CSV控件(案例)

Form_通过FND_FNDFLUPL标准功能上传CSV控件(案例)

2014-06-08 BaoXinjian

一、总结


1. 上传资料

   (1).通过调用function<FND_FNDFLUPL>,打开上传文件页面;

   (2).从表fnd_lob_access和fnd_lobs中取出文件名放在form画面上;   

2. 对dbms_lob中的二进制文件进行处理

   (1).通过dbms_lob.converttoclob将二进制文件转换成字符文件;

   (2).通过dbms_lob.instr和dbms_lob.substr在字符文件获取字符串记录;

3. 对获取资料放入到临时表,并显示在画面上

   (1).通过instr和substr获字符串记录中的字段;

   (2).并将字段赋值到集合或表中;

 

二、步骤


1. 上传资料

 1 DECLARE
 2 
 3     access_id NUMBER;
 4 
 5     l_server_url VARCHAR2(100);
 6 
 7     l_parameters VARCHAR2(100);
 8 
 9     button_choice INTEGER;
10 
11     l_file_id VARCHAR2(100);
12 
13     l_gfm_id INTEGER;
14 
15 BEGIN
16 
17     access_id := fnd_gfm.authorize(NULL);
18 
19     fnd_profile.get(APPS_WEB_AGENT, l_server_url);
20 
21     l_parameters := access_id= || access_id ||  l_server_url= || l_server_url;
22 
23     fnd_function.EXECUTE(function_name => FND_FNDFLUPL,
24 
25                                                                         open_flag => Y,
26 
27                                                                         session_flag => Y,
28 
29                                                                         other_params => l_parameters);
30 
31 -- Display a modal message for user to indicate file upload
32 
33 -- is completed.
34 
35     fnd_message.set_name(FND, ATCHMT-FILE-UPLOAD-COMPLETE);
36 
37     button_choice := fnd_message.question(button1 => YES,
38 
39                                                                    button2 => NULL,
40 
41                                                                    button3 => NO,
42 
43                                                                    default_btn => 1,
44 
45                                                                    cancel_btn => 3,
46 
47                                                                    icon => question);
48 
49     IF (button_choice = 3) THEN
50 
51         NULL;
52 
53     ELSIF (button_choice = 1) THEN
54 
55         l_file_id := ‘‘;
56 
57         l_gfm_id := fnd_gfm.get_file_id(access_id);
58 
59         :main.gfm_id := fnd_gfm.get_file_id(access_id);
60 
61         IF l_gfm_id IS NOT NULL THEN
62 
63             SELECT DECODE(INSTR(file_name, /),0,file_name,SUBSTR(file_name, INSTR(file_name, /) + 1))
64 
65                  INTO l_file_id
66 
67                 FROM fnd_lobs
68 
69               WHERE file_id = l_gfm_id;
70 
71             IF l_file_id IS NOT NULL THEN
72 
73                 :main.filename := l_file_id;
74 
75                 SELECT cux_ce_stmt_s.NEXTVAL INTO :main.working_id FROM DUAL;
76 
77             END IF;
78 
79         END IF;
80 
81     END IF;
82 
83 END IF;

 

2. 对dbms_lob中的二进制文件进行处

 1 DECLARE
 2 
 3     c_data CLOB;
 4 
 5     l_data BLOB;
 6 
 7     l_dest_offset INTEGER := 1;
 8 
 9     l_scr_offset INTEGER := 1;
10 
11     l_lang_context INTEGER := DBMS_LOB.default_lang_ctx;
12 
13     l_warn INTEGER;
14 
15     n_offset INTEGER;
16 
17     n_clob_size INTEGER;
18 
19     n_pos INTEGER;
20 
21     v_buf VARCHAR2(4000);
22 
23 BEGIN
24 
25     DBMS_LOB.createtemporary(c_data, FALSE, DBMS_LOB.SESSION);
26 
27     SELECT file_data INTO l_data FROM fnd_lobs WHERE file_id = 1501905;
28 
29     DBMS_LOB.converttoclob(dest_lob => c_data,
30 
31                                                 src_blob => l_data,
32 
33                                                 amount => DBMS_LOB.lobmaxsize,
34 
35                                                 dest_offset => l_dest_offset,
36 
37                                                 src_offset => l_scr_offset,
38 
39                                                 blob_csid => NLS_CHARSET_ID(UTF8),
40 
41                                                 lang_context => l_lang_context,
42 
43                                                 warning => l_warn);
44 
45     n_offset := 1;
46 
47     LOOP
48 
49         n_pos := DBMS_LOB.INSTR(lob_loc => c_data,
50 
51                                                      pattern => CHR(10),
52 
53                                                      offset => n_offset,
54 
55                                                      nth => 1);
56 
57         IF NVL(n_pos, 0) = 0 THEN
58 
59         n_pos := n_clob_size + 1;
60 
61     END IF;
62 
63     v_buf := DBMS_LOB.SUBSTR(lob_loc => c_data,
64 
65                                                     amount => n_pos - n_offset,
66 
67                                                     offset => n_offset);
68 
69      n_offset := n_pos + 1;
70 
71     EXIT WHEN n_pos > n_clob_size;
72 
73 END LOOP;
74 
75     IF dbms_lob.istemporary(l_data) > 0 THEN
76 
77         dbms_lob.freetemporary(l_data);
78 
79     END IF;
80 
81     IF dbms_lob.istemporary(c_data) > 0 THEN
82 
83         dbms_lob.freetemporary(c_data);
84 
85     END IF;
86 
87 END IF;

 

 

3. 对获取资料放入到临时表,并显示在画面上

 1 DECLARE
 2 
 3     v_buf VARCHAR2(1000);
 4 
 5     v_field VARCHAR2(1000);
 6 
 7     n_delimiter_pos INTEGER;
 8 
 9 BEGIN
10 
11     v_buf := Return Authorization Reference,Adjustment Type,Vendor Number,Site Code- Pay To,Site Code- Ship To,Part Number,Quantity,Subinventory;
12 
13     LOOP
14 
15         v_field := NULL;
16 
17         n_delimiter_pos := INSTR(v_buf, ,);
18 
19         IF n_delimiter_pos > 0 THEN
20 
21             v_field := LTRIM(RTRIM(SUBSTR(v_buf, 1, n_delimiter_pos - 1)));
22 
23             v_buf := SUBSTR(v_buf, n_delimiter_pos + 1);
24 
25         ELSIF LTRIM(RTRIM(v_buf)) IS NOT NULL THEN
26 
27             v_field := LTRIM(RTRIM(v_buf));
28 
29             v_buf := NULL;
30 
31         END IF;
32 
33         IF (v_buf IS NULL) THEN
34 
35             EXIT;
36 
37         END IF;
38 
39     END LOOP;
40 
41 END;

 

Thanks and Regards