[Home] [Help]
PACKAGE BODY: APPS.FND_SEED_STAGE_UTIL
Source
1 PACKAGE BODY FND_SEED_STAGE_UTIL as
2 /* $Header: fndpstub.pls 120.6 2011/01/19 14:39:49 smadhapp ship $ */
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 get_messages(x_msg_tab OUT NOCOPY CHAR4K_TAB)
65 IS
66 counter number := 0;
67 BEGIN
68 if G_MSG_TAB.COUNT > 0 then
69 FOR i IN G_MSG_TAB.FIRST .. G_MSG_TAB.LAST
70 LOOP
71 x_msg_tab(i) := G_MSG_TAB(i);
72 counter := counter + 1;
73 exit when counter > 100;
74 END LOOP;
75 end if;
76 G_MSG_TAB.DELETE;
77 END get_messages;
78
79 PROCEDURE UPLOAD (p_lct_file IN VARCHAR2,
80 p_proc_id IN NUMBER,
81 p_debug IN NUMBER,
82 x_abort OUT NOCOPY NUMBER,
83 x_warning OUT NOCOPY NUMBER,
84 x_err_count OUT NOCOPY NUMBER,
85 x_err_tab OUT NOCOPY CHAR4K_TAB)
86 IS
87 TYPE csr_type IS REF CURSOR;
88
89 config_csr csr_type;
90 l_upload_lob FND_SEED_STAGE_CONFIG.UPLOAD_STMT%TYPE;
91 l_stmt_len FND_SEED_STAGE_CONFIG.STMT_LEN%TYPE;
92
93 status_csr csr_type;
94 l_str VARCHAR2(4000);
95
96 l_upload_arr DBMS_SQL.VARCHAR2S;
97 l_upload_stmt VARCHAR2(32767);
98 l_amt BINARY_INTEGER;
99 l_offset INTEGER;
100 l_read_cnt PLS_INTEGER;
101 l_csr INTEGER;
102 l_msg VARCHAR2(4000);
103 l_ret INTEGER;
104 --l_max_err PLS_INTEGER;
105 l_err_count INTEGER;
106 l_status VARCHAR2(10);
107 l_buffer VARCHAR2(32767);
108
109 --ret INTEGER;
110 BEGIN
111
112 --ret := DBMS_PROFILER.START_PROFILER('FNDLOAD_ReuseLct'||to_char(sysdate, 'HH24:MI:SS'));
113
114 x_err_count := 0;
115 x_abort := 0;
116 x_warning := 0;
117 -- l_status := 0;
118 --l_max_err := 100;
119
120 --DBMS_OUTPUT.PUT_LINE('1111111');
121 G_MSG_TAB.delete;
122
123 if p_debug = 0 then
124 l_str := 'SELECT upload_stmt, stmt_len
125 FROM fnd_seed_stage_config
126 WHERE lct_file = :1
127 AND proc_id = :2
128 AND entity_name = ''PLSQL_WRAPPER_CODE''';
129 else
130 l_str := 'SELECT upload_stmt, stmt_len
131 FROM fnd_seed_stage_config_debug
132 WHERE lct_file = :1
133 AND proc_id = :2
134 AND entity_name = ''PLSQL_WRAPPER_CODE''';
135 end if;
136
137 OPEN config_csr FOR l_str USING p_lct_file, p_proc_id;
138 FETCH config_csr INTO l_upload_lob, l_stmt_len;
139 if config_csr%NOTFOUND then
140 l_msg := 'No record found in FND_SEED_STAGE_CONFIG with the plsql wrapper code';
141 x_abort := 1;
142 x_err_count := 1;
143 x_err_tab(1) := l_msg;
144 CLOSE config_csr;
145 RETURN;
146 end if;
147 CLOSE config_csr;
148
149 BEGIN
150 if l_stmt_len <= 32767 then
151 l_offset := 1;
152 BEGIN
153 LOOP
154 l_amt := 32767;
155 DBMS_LOB.READ(l_upload_lob, l_amt, l_offset, l_buffer);
156 l_upload_stmt := l_upload_stmt||l_buffer;
157 l_offset := l_offset + l_amt;
158 END LOOP;
159 EXCEPTION WHEN NO_DATA_FOUND THEN
160 NULL;
161 END;
162 EXECUTE IMMEDIATE l_upload_stmt;
163 else
164 l_offset := 1;
165 l_read_cnt := 1;
166 BEGIN
167 LOOP
168 l_amt := 256;
169 DBMS_LOB.READ(l_upload_lob, l_amt, l_offset, l_upload_arr(l_read_cnt));
170 l_offset := l_offset + l_amt;
171 l_read_cnt := l_read_cnt + 1;
172 END LOOP;
173 EXCEPTION WHEN NO_DATA_FOUND THEN
174 NULL;
175 END;
176
177 l_csr := DBMS_SQL.OPEN_CURSOR;
178
179 BEGIN
180 DBMS_SQL.PARSE(l_csr, l_upload_arr, 1, l_read_cnt-1, FALSE, DBMS_SQL.NATIVE);
181 EXCEPTION WHEN OTHERS THEN
182 DBMS_SQL.CLOSE_CURSOR(l_csr);
183 l_msg := SUBSTR('Error parsing the generated plsql wrapper statement '||sqlerrm(sqlcode), 1, 4000);
184 x_abort := 1;
185 x_err_count := 1;
186 x_err_tab(1) := l_msg;
187 RETURN;
188 END;
189
190 l_ret := DBMS_SQL.EXECUTE(l_csr);
191
192 DBMS_SQL.CLOSE_CURSOR(l_csr);
193
194 end if;
195 END;
196
197 l_err_count := G_MSG_TAB.COUNT;
198 x_err_count := l_err_count;
199 if l_err_count > l_max_err then
200 --x_err_count := l_max_err;
201 get_messages(1, l_max_err, x_err_tab);
202 else
203 --x_err_count := l_err_count;
204 x_err_tab := G_MSG_TAB;
205 end if;
206
207 if p_debug = 0 then
208 l_str := 'SELECT exec_status
209 FROM fnd_seed_stage_entity
210 WHERE seq = -1
211 AND config_id = -1';
212 else
213 l_str := 'SELECT exec_status
214 FROM fnd_seed_stage_entity_debug
215 WHERE seq = -1
216 AND config_id = -1';
217 end if;
218
219 OPEN status_csr FOR l_str;
220 FETCH status_csr INTO l_status;
221 CLOSE status_csr;
222 --DBMS_OUTPUT.PUT_LINE('status = '||l_status);
223 if l_status = '1' then
224 x_abort := 1;
225 elsif l_status = '2' then
226 x_warning := 1;
227 end if;
228
229 --ret := DBMS_PROFILER.STOP_PROFILER;
230
231 EXCEPTION WHEN OTHERS THEN
232 if config_csr%ISOPEN then
233 CLOSE config_csr;
234 end if;
235 if DBMS_SQL.IS_OPEN(l_csr) then
236 DBMS_SQL.CLOSE_CURSOR(l_csr);
237 end if;
238 if status_csr%ISOPEN then
239 CLOSE status_csr;
240 end if;
241 l_msg := SUBSTR('Error during uploading. '||sqlerrm, 1, 4000);
242 x_abort := 1;
243 x_err_count := 1;
244 x_err_tab(1) := l_msg;
245 END UPLOAD;
246
247 PROCEDURE create_temp_clob(p_temp_clob IN OUT NOCOPY CLOB)
248 is
249 begin
250 dbms_lob.createtemporary(p_temp_clob,true,dbms_lob.session);
251 end;
252
253 end FND_SEED_STAGE_UTIL;