[Home] [Help]
PACKAGE BODY: APPS.FND_SEED_STAGE_UTIL
Source
1 PACKAGE BODY FND_SEED_STAGE_UTIL as
2 /* $Header: fndpstub.pls 120.4 2005/10/10 16:11:38 quli noship $ */
3
4
5 G_MSG_TAB CHAR4K_TAB;
6 G_DISPLAY_MSG BOOLEAN := FALSE;
7 l_max_err PLS_INTEGER :=100;
8
9 PROCEDURE insert_msg( p_msg_str IN VARCHAR2)
10 IS
11 BEGIN
12 G_MSG_TAB(G_MSG_TAB.COUNT + 1) := p_msg_str;
13 END insert_msg;
14
15 PROCEDURE update_status( p_debug IN NUMBER,
16 p_seq IN NUMBER,
17 p_status IN NUMBER)
18 IS
19 type csr_type IS REF CURSOR;
20 upd_csr csr_type;
21 l_str VARCHAR2(4000);
22 BEGIN
23 if p_debug = 0 then
24 l_str := 'UPDATE fnd_seed_stage_entity
25 SET exec_status = :1
26 WHERE seq = :2';
27 else
28 l_str := 'UPDATE fnd_seed_stage_entity_debug
29 SET exec_status = :1
30 WHERE seq = :2';
31 end if;
32
33 EXECUTE IMMEDIATE l_str USING p_status, p_seq;
34
35 END update_status;
36
37 PROCEDURE get_messages(p_msg_to IN NUMBER,
38 x_msg_tab OUT NOCOPY CHAR4K_TAB)
39 IS
40 BEGIN
41 if G_MSG_TAB.COUNT > 0 then
42 FOR i IN l_max_err+1 .. p_msg_to
43 LOOP
44 x_msg_tab(i-l_max_err) := G_MSG_TAB(i);
45 END LOOP;
46 end if;
47 G_MSG_TAB.DELETE(l_max_err+1, p_msg_to);
48 END get_messages;
49
50 PROCEDURE get_messages(p_msg_from IN NUMBER,
51 p_msg_to IN NUMBER,
52 x_msg_tab OUT NOCOPY CHAR4K_TAB)
53 IS
54 BEGIN
55 if G_MSG_TAB.COUNT > 0 then
56 FOR i IN p_msg_from .. p_msg_to
57 LOOP
58 x_msg_tab(i) := G_MSG_TAB(i);
59 END LOOP;
60 end if;
61 G_MSG_TAB.DELETE(p_msg_from, p_msg_to);
62 END get_messages;
63
64 PROCEDURE UPLOAD (p_lct_file IN VARCHAR2,
65 p_proc_id IN NUMBER,
66 p_debug IN NUMBER,
67 x_abort OUT NOCOPY NUMBER,
68 x_warning OUT NOCOPY NUMBER,
69 x_err_count OUT NOCOPY NUMBER,
70 x_err_tab OUT NOCOPY CHAR4K_TAB)
71 IS
72 TYPE csr_type IS REF CURSOR;
73
74 config_csr csr_type;
75 l_upload_lob FND_SEED_STAGE_CONFIG.UPLOAD_STMT%TYPE;
76 l_stmt_len FND_SEED_STAGE_CONFIG.STMT_LEN%TYPE;
77
78 status_csr csr_type;
79 l_str VARCHAR2(4000);
80
81 l_upload_arr DBMS_SQL.VARCHAR2S;
82 l_upload_stmt VARCHAR2(32767);
83 l_amt BINARY_INTEGER;
84 l_offset INTEGER;
85 l_read_cnt PLS_INTEGER;
86 l_csr INTEGER;
87 l_msg VARCHAR2(4000);
88 l_ret INTEGER;
89 --l_max_err PLS_INTEGER;
90 l_err_count INTEGER;
91 l_status VARCHAR2(10);
92 l_buffer VARCHAR2(32767);
93
94 --ret INTEGER;
95 BEGIN
96
97 --ret := DBMS_PROFILER.START_PROFILER('FNDLOAD_ReuseLct'||to_char(sysdate, 'HH24:MI:SS'));
98
99 x_err_count := 0;
100 x_abort := 0;
101 x_warning := 0;
102 -- l_status := 0;
103 --l_max_err := 100;
104
105 --DBMS_OUTPUT.PUT_LINE('1111111');
106 G_MSG_TAB.delete;
107
108 if p_debug = 0 then
109 l_str := 'SELECT upload_stmt, stmt_len
110 FROM fnd_seed_stage_config
111 WHERE lct_file = :1
112 AND proc_id = :2
113 AND entity_name = ''PLSQL_WRAPPER_CODE''';
114 else
115 l_str := 'SELECT upload_stmt, stmt_len
116 FROM fnd_seed_stage_config_debug
117 WHERE lct_file = :1
118 AND proc_id = :2
119 AND entity_name = ''PLSQL_WRAPPER_CODE''';
120 end if;
121
122 OPEN config_csr FOR l_str USING p_lct_file, p_proc_id;
123 FETCH config_csr INTO l_upload_lob, l_stmt_len;
124 if config_csr%NOTFOUND then
125 l_msg := 'No record found in FND_SEED_STAGE_CONFIG with the plsql wrapper code';
126 x_abort := 1;
127 x_err_count := 1;
128 x_err_tab(1) := l_msg;
129 CLOSE config_csr;
130 RETURN;
131 end if;
132 CLOSE config_csr;
133
134 BEGIN
135 if l_stmt_len <= 32767 then
136 l_offset := 1;
137 BEGIN
138 LOOP
139 l_amt := 32767;
140 DBMS_LOB.READ(l_upload_lob, l_amt, l_offset, l_buffer);
141 l_upload_stmt := l_upload_stmt||l_buffer;
142 l_offset := l_offset + l_amt;
143 END LOOP;
144 EXCEPTION WHEN NO_DATA_FOUND THEN
145 NULL;
146 END;
147 EXECUTE IMMEDIATE l_upload_stmt;
148 else
149 l_offset := 1;
150 l_read_cnt := 1;
151 BEGIN
152 LOOP
153 l_amt := 256;
154 DBMS_LOB.READ(l_upload_lob, l_amt, l_offset, l_upload_arr(l_read_cnt));
155 l_offset := l_offset + l_amt;
156 l_read_cnt := l_read_cnt + 1;
157 END LOOP;
158 EXCEPTION WHEN NO_DATA_FOUND THEN
159 NULL;
160 END;
161
162 l_csr := DBMS_SQL.OPEN_CURSOR;
163
164 BEGIN
165 DBMS_SQL.PARSE(l_csr, l_upload_arr, 1, l_read_cnt-1, FALSE, DBMS_SQL.NATIVE);
166 EXCEPTION WHEN OTHERS THEN
167 DBMS_SQL.CLOSE_CURSOR(l_csr);
168 l_msg := SUBSTR('Error parsing the generated plsql wrapper statement '||sqlerrm(sqlcode), 1, 4000);
169 x_abort := 1;
170 x_err_count := 1;
171 x_err_tab(1) := l_msg;
172 RETURN;
173 END;
174
175 l_ret := DBMS_SQL.EXECUTE(l_csr);
176
177 DBMS_SQL.CLOSE_CURSOR(l_csr);
178
179 end if;
180 END;
181
182 l_err_count := G_MSG_TAB.COUNT;
183 x_err_count := l_err_count;
184 if l_err_count > l_max_err then
185 --x_err_count := l_max_err;
186 get_messages(1, l_max_err, x_err_tab);
187 else
188 --x_err_count := l_err_count;
189 x_err_tab := G_MSG_TAB;
190 end if;
191
192 if p_debug = 0 then
193 l_str := 'SELECT exec_status
194 FROM fnd_seed_stage_entity
195 WHERE seq = -1
196 AND config_id = -1';
197 else
198 l_str := 'SELECT exec_status
199 FROM fnd_seed_stage_entity_debug
200 WHERE seq = -1
201 AND config_id = -1';
202 end if;
203
204 OPEN status_csr FOR l_str;
205 FETCH status_csr INTO l_status;
206 CLOSE status_csr;
207 --DBMS_OUTPUT.PUT_LINE('status = '||l_status);
208 if l_status = '1' then
209 x_abort := 1;
210 elsif l_status = '2' then
211 x_warning := 1;
212 end if;
213
214 --ret := DBMS_PROFILER.STOP_PROFILER;
215
216 EXCEPTION WHEN OTHERS THEN
217 if config_csr%ISOPEN then
218 CLOSE config_csr;
219 end if;
220 if DBMS_SQL.IS_OPEN(l_csr) then
221 DBMS_SQL.CLOSE_CURSOR(l_csr);
222 end if;
223 if status_csr%ISOPEN then
224 CLOSE status_csr;
225 end if;
226 l_msg := SUBSTR('Error during uploading. '||sqlerrm, 1, 4000);
227 x_abort := 1;
228 x_err_count := 1;
229 x_err_tab(1) := l_msg;
230 END UPLOAD;
231
232 PROCEDURE create_temp_clob(p_temp_clob IN OUT NOCOPY CLOB)
233 is
234 begin
235 dbms_lob.createtemporary(p_temp_clob,true,dbms_lob.session);
236 end;
237
238 end FND_SEED_STAGE_UTIL;