[Home] [Help]
PACKAGE BODY: APPS.CN_IMPORT_CLIENT_PVT
Source
1 PACKAGE BODY CN_IMPORT_CLIENT_PVT AS
2 /* $Header: cnvimpcb.pls 115.9 2002/11/21 21:13:39 hlchen ship $ */
3
4 -- Global variables and constants.
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_IMPORT_CLIENT_PVT';
6 G_DEBUG_FLAG VARCHAR2(1) := 'N';
7 G_COL_NUM NUMBER := 40;
8 G_ARC_IMPORT_HEADER CONSTANT VARCHAR2(30) := 'IMPH';
9
10 --
11 -- Private utility modules for this package
12 --
13 PROCEDURE Init_Col_Data (
14 p_data IN CN_IMPORT_PVT.char_data_set_type,
15 p_start_index IN NUMBER,
16 p_end_index IN NUMBER,
17 p_insert_flag IN VARCHAR2 := FND_API.G_FALSE, --TRUE for insertion
18 p_col_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000
19 );
20
21 PROCEDURE Append_More_Data (
22 p_str_col_names IN CN_IMPORT_PVT.char_data_set_type,
23 p_str_data IN CN_IMPORT_PVT.char_data_set_type,
24 p_num_col_names IN CN_IMPORT_PVT.char_data_set_type,
25 p_num_data IN CN_IMPORT_PVT.num_data_set_type,
26 p_col_clause IN OUT NOCOPY VARCHAR2,
27 p_val_clause IN OUT NOCOPY VARCHAR2
28 );
29
30 PROCEDURE Init_All_Col_Data (
31 p_start_index IN NUMBER,
32 p_col_count IN NUMBER,
33 p_row_count IN NUMBER,
34 p_data IN CN_IMPORT_PVT.char_data_set_type,
35 p_insert_flag IN VARCHAR2 := FND_API.G_FALSE, --TRUE for insertion
36 p_col1_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
37 p_col2_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
38 p_col3_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
39 p_col4_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
40 p_col5_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
41 p_col6_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
42 p_col7_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
43 p_col8_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
44 p_col9_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
45 p_col10_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
46
47 p_col11_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
48 p_col12_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
49 p_col13_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
50 p_col14_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
51 p_col15_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
52 p_col16_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
53 p_col17_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
54 p_col18_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
55 p_col19_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
56 p_col20_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
57
58 p_col21_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
59 p_col22_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
60 p_col23_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
61 p_col24_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
62 p_col25_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
63 p_col26_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
64 p_col27_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
65 p_col28_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
66 p_col29_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
67 p_col30_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
68
69 p_col31_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
70 p_col32_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
71 p_col33_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
72 p_col34_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
73 p_col35_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
74 p_col36_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
75 p_col37_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
76 p_col38_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
77 p_col39_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
78 p_col40_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
79
80 p_act_col_num OUT NOCOPY NUMBER
81
82 );
83
84 PROCEDURE Insert_To_DB (
85 p_prim_keys IN JTF_NUMBER_TABLE,
86 p_col_count IN NUMBER, --actual column count
87 p_row_count IN NUMBER,
88
89 p_tab_name_clause IN VARCHAR2,
90 p_col_clause IN VARCHAR2,
91 p_value_clause IN VARCHAR2,
92 p_col_names IN CN_IMPORT_PVT.char_data_set_type,
93 p_imp_header_id IN NUMBER,
94 p_import_type_code IN VARCHAR2,
95
96 p_col1_data IN JTF_VARCHAR2_TABLE_2000,
97 p_col2_data IN JTF_VARCHAR2_TABLE_2000,
98 p_col3_data IN JTF_VARCHAR2_TABLE_2000,
99 p_col4_data IN JTF_VARCHAR2_TABLE_2000,
100 p_col5_data IN JTF_VARCHAR2_TABLE_2000,
101 p_col6_data IN JTF_VARCHAR2_TABLE_2000,
102 p_col7_data IN JTF_VARCHAR2_TABLE_2000,
103 p_col8_data IN JTF_VARCHAR2_TABLE_2000,
104 p_col9_data IN JTF_VARCHAR2_TABLE_2000,
105 p_col10_data IN JTF_VARCHAR2_TABLE_2000,
106 p_col11_data IN JTF_VARCHAR2_TABLE_2000,
107 p_col12_data IN JTF_VARCHAR2_TABLE_2000,
108 p_col13_data IN JTF_VARCHAR2_TABLE_2000,
109 p_col14_data IN JTF_VARCHAR2_TABLE_2000,
110 p_col15_data IN JTF_VARCHAR2_TABLE_2000,
111 p_col16_data IN JTF_VARCHAR2_TABLE_2000,
112 p_col17_data IN JTF_VARCHAR2_TABLE_2000,
113 p_col18_data IN JTF_VARCHAR2_TABLE_2000,
114 p_col19_data IN JTF_VARCHAR2_TABLE_2000,
115 p_col20_data IN JTF_VARCHAR2_TABLE_2000,
116 p_col21_data IN JTF_VARCHAR2_TABLE_2000,
117 p_col22_data IN JTF_VARCHAR2_TABLE_2000,
118 p_col23_data IN JTF_VARCHAR2_TABLE_2000,
119 p_col24_data IN JTF_VARCHAR2_TABLE_2000,
120 p_col25_data IN JTF_VARCHAR2_TABLE_2000,
121 p_col26_data IN JTF_VARCHAR2_TABLE_2000,
122 p_col27_data IN JTF_VARCHAR2_TABLE_2000,
123 p_col28_data IN JTF_VARCHAR2_TABLE_2000,
124 p_col29_data IN JTF_VARCHAR2_TABLE_2000,
125 p_col30_data IN JTF_VARCHAR2_TABLE_2000,
126 p_col31_data IN JTF_VARCHAR2_TABLE_2000,
127 p_col32_data IN JTF_VARCHAR2_TABLE_2000,
128 p_col33_data IN JTF_VARCHAR2_TABLE_2000,
129 p_col34_data IN JTF_VARCHAR2_TABLE_2000,
130 p_col35_data IN JTF_VARCHAR2_TABLE_2000,
131 p_col36_data IN JTF_VARCHAR2_TABLE_2000,
132 p_col37_data IN JTF_VARCHAR2_TABLE_2000,
133 p_col38_data IN JTF_VARCHAR2_TABLE_2000,
134 p_col39_data IN JTF_VARCHAR2_TABLE_2000,
135 p_col40_data IN JTF_VARCHAR2_TABLE_2000,
136
137 x_return_status OUT NOCOPY VARCHAR2,
138 x_msg_count OUT NOCOPY NUMBER,
139 x_msg_data OUT NOCOPY VARCHAR2
140 );
141
142 PROCEDURE Update_DB (
143 p_prim_keys IN JTF_NUMBER_TABLE,
144 p_col_count IN NUMBER,
145 p_row_count IN NUMBER,
146
147 p_tab_name_name IN VARCHAR2,
148 p_col_names IN CN_IMPORT_PVT.char_data_set_type,
149 p_col_start_count IN NUMBER,
150
151 p_col1_data IN JTF_VARCHAR2_TABLE_2000,
152 p_col2_data IN JTF_VARCHAR2_TABLE_2000,
153 p_col3_data IN JTF_VARCHAR2_TABLE_2000,
154 p_col4_data IN JTF_VARCHAR2_TABLE_2000,
155 p_col5_data IN JTF_VARCHAR2_TABLE_2000,
156 p_col6_data IN JTF_VARCHAR2_TABLE_2000,
157 p_col7_data IN JTF_VARCHAR2_TABLE_2000,
158 p_col8_data IN JTF_VARCHAR2_TABLE_2000,
159 p_col9_data IN JTF_VARCHAR2_TABLE_2000,
160 p_col10_data IN JTF_VARCHAR2_TABLE_2000,
161 p_col11_data IN JTF_VARCHAR2_TABLE_2000,
162 p_col12_data IN JTF_VARCHAR2_TABLE_2000,
163 p_col13_data IN JTF_VARCHAR2_TABLE_2000,
164 p_col14_data IN JTF_VARCHAR2_TABLE_2000,
165 p_col15_data IN JTF_VARCHAR2_TABLE_2000,
166 p_col16_data IN JTF_VARCHAR2_TABLE_2000,
167 p_col17_data IN JTF_VARCHAR2_TABLE_2000,
168 p_col18_data IN JTF_VARCHAR2_TABLE_2000,
169 p_col19_data IN JTF_VARCHAR2_TABLE_2000,
170 p_col20_data IN JTF_VARCHAR2_TABLE_2000,
171 p_col21_data IN JTF_VARCHAR2_TABLE_2000,
172 p_col22_data IN JTF_VARCHAR2_TABLE_2000,
173 p_col23_data IN JTF_VARCHAR2_TABLE_2000,
174 p_col24_data IN JTF_VARCHAR2_TABLE_2000,
175 p_col25_data IN JTF_VARCHAR2_TABLE_2000,
176 p_col26_data IN JTF_VARCHAR2_TABLE_2000,
177 p_col27_data IN JTF_VARCHAR2_TABLE_2000,
178 p_col28_data IN JTF_VARCHAR2_TABLE_2000,
179 p_col29_data IN JTF_VARCHAR2_TABLE_2000,
180 p_col30_data IN JTF_VARCHAR2_TABLE_2000,
181 p_col31_data IN JTF_VARCHAR2_TABLE_2000,
182 p_col32_data IN JTF_VARCHAR2_TABLE_2000,
183 p_col33_data IN JTF_VARCHAR2_TABLE_2000,
184 p_col34_data IN JTF_VARCHAR2_TABLE_2000,
185 p_col35_data IN JTF_VARCHAR2_TABLE_2000,
186 p_col36_data IN JTF_VARCHAR2_TABLE_2000,
187 p_col37_data IN JTF_VARCHAR2_TABLE_2000,
188 p_col38_data IN JTF_VARCHAR2_TABLE_2000,
189 p_col39_data IN JTF_VARCHAR2_TABLE_2000,
190 p_col40_data IN JTF_VARCHAR2_TABLE_2000,
191
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2
195 );
196 --- End forward modules
197
198 -- PROCEDURE
199 -- Insert_Data
200 --
201 -- PURPOSE
202 -- The procedure insert a collection of data into a table whose name is
203 -- specified by the "p_table_name" field. It uses native dynamic SQL to
204 -- bulk insert records using binding tables.
205 -- p_table_name must be a view on top of cn_imp_lines
206 --
207 -- The "p_col_names" field contains a collection of the column names.
208 -- The "p_data" contains all data needed to be inserted, assuming all data
209 -- types are "VARCHAR2". For example, if the data to be inserted are the
210 -- followings:
211 --
212 -- Row Number 1 2 3 4
213 -- Column1 Frank Smith Scott Marry
214 -- Column2 Amos Anderson Baber Beier
215 -- Column3 75039 77002 23060 03062
216 --
217 -- The data is stored in the "p_data" as: "Frank", "Smith", "Scott", "Marry",
218 -- "Amos", "Anderson", "Baber", "Beier", "75039", "77002", "23060", "03062".
219 -- Both "p_col_names" and "p_data" are consecutive.
220 -- we need these fields or not. The "p_row_count" field is redundant since
221 -- we do not want to invoke the "COUNT" on "p_data" since this table is
222 -- supposed to be huge.
223 --
224 -- NOTES
225
226 PROCEDURE Insert_Data
227 (p_api_version IN NUMBER,
228 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
229 p_commit IN VARCHAR2 := FND_API.G_FALSE,
230 p_imp_header_id IN NUMBER,
231 p_import_type_code IN VARCHAR2,
232 p_table_name IN VARCHAR2,
233 p_col_names IN CN_IMPORT_PVT.char_data_set_type,
234 p_data IN CN_IMPORT_PVT.char_data_set_type,
235 p_row_count IN NUMBER,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2
239 ) IS
240
241 --
242 -- Standard API information constants.
243 --
244 L_API_VERSION CONSTANT NUMBER := 1.0;
245 L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Data';
246 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
247
248 --
249 -- "FORALL i IN 1 .. :row_count
250 -- INSERT INTO CN_IMP_LINES"
251 --
252 l_tab_name_clause VARCHAR2(8000) := 'BEGIN FORALL i IN 1 .. :row_count
253 INSERT INTO ';
254
255 --
256 -- "(IMP_LINE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY)"
257 --
258 l_col_clause VARCHAR2(8000) := ' (';
259
260 --
261 -- "VALUES ( :p_tab1(i), :p_tab2(i))
262 --
263 l_value_clause VARCHAR2(8000) := ' VALUES (';
264
265 l_col_count NUMBER;
266 l_orig_col_count NUMBER;
267 l_start_index NUMBER := 1;
268 l_act_col_count NUMBER; --actual col count for each initialization
269
270 l_prim_keys JTF_NUMBER_TABLE := JTF_NUMBER_TABLE(0);
271 l_curr_seq_num NUMBER;
272 l_start_seq_num NUMBER;
273
274 --
275 -- Just pick up data for 10 column data
276 --
277 l_col1_data JTF_VARCHAR2_TABLE_2000;
278 l_col2_data JTF_VARCHAR2_TABLE_2000;
279 l_col3_data JTF_VARCHAR2_TABLE_2000;
283 l_col7_data JTF_VARCHAR2_TABLE_2000;
280 l_col4_data JTF_VARCHAR2_TABLE_2000;
281 l_col5_data JTF_VARCHAR2_TABLE_2000;
282 l_col6_data JTF_VARCHAR2_TABLE_2000;
284 l_col8_data JTF_VARCHAR2_TABLE_2000;
285 l_col9_data JTF_VARCHAR2_TABLE_2000;
286 l_col10_data JTF_VARCHAR2_TABLE_2000;
287
288 l_col11_data JTF_VARCHAR2_TABLE_2000;
289 l_col12_data JTF_VARCHAR2_TABLE_2000;
290 l_col13_data JTF_VARCHAR2_TABLE_2000;
291 l_col14_data JTF_VARCHAR2_TABLE_2000;
292 l_col15_data JTF_VARCHAR2_TABLE_2000;
293 l_col16_data JTF_VARCHAR2_TABLE_2000;
294 l_col17_data JTF_VARCHAR2_TABLE_2000;
295 l_col18_data JTF_VARCHAR2_TABLE_2000;
296 l_col19_data JTF_VARCHAR2_TABLE_2000;
297 l_col20_data JTF_VARCHAR2_TABLE_2000;
298
299 l_col21_data JTF_VARCHAR2_TABLE_2000;
300 l_col22_data JTF_VARCHAR2_TABLE_2000;
301 l_col23_data JTF_VARCHAR2_TABLE_2000;
302 l_col24_data JTF_VARCHAR2_TABLE_2000;
303 l_col25_data JTF_VARCHAR2_TABLE_2000;
304 l_col26_data JTF_VARCHAR2_TABLE_2000;
305 l_col27_data JTF_VARCHAR2_TABLE_2000;
306 l_col28_data JTF_VARCHAR2_TABLE_2000;
307 l_col29_data JTF_VARCHAR2_TABLE_2000;
308 l_col30_data JTF_VARCHAR2_TABLE_2000;
309
310 l_col31_data JTF_VARCHAR2_TABLE_2000;
311 l_col32_data JTF_VARCHAR2_TABLE_2000;
312 l_col33_data JTF_VARCHAR2_TABLE_2000;
313 l_col34_data JTF_VARCHAR2_TABLE_2000;
314 l_col35_data JTF_VARCHAR2_TABLE_2000;
315 l_col36_data JTF_VARCHAR2_TABLE_2000;
316 l_col37_data JTF_VARCHAR2_TABLE_2000;
317 l_col38_data JTF_VARCHAR2_TABLE_2000;
318 l_col39_data JTF_VARCHAR2_TABLE_2000;
319 l_col40_data JTF_VARCHAR2_TABLE_2000;
320 BEGIN
321
322 -- Standard Start of API savepoint
323 SAVEPOINT Insert_Data;
324 -- Standard call to check for call compatibility.
325 IF NOT FND_API.compatible_api_call
326 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
327 THEN
328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
329 END IF;
330 -- Initialize message list if p_init_msg_list is set to TRUE.
331 IF FND_API.to_Boolean( p_init_msg_list ) THEN
332 FND_MSG_PUB.initialize;
333 END IF;
334 -- Initialize API return status to success
335 x_return_status := FND_API.G_RET_STS_SUCCESS;
336 -- API body
337 cn_message_pkg.debug('Insert Data : Start Inserting Data');
338
339 l_orig_col_count := p_col_names.COUNT;
340
341 IF l_orig_col_count < 1 THEN
342 cn_message_pkg.set_error(l_api_name,'Column count < 1');
343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
344 END IF;
345
346 cn_message_pkg.debug('Insert Data : Start Copy Data from p_data');
347
348 -- copy data from p_data into local variables l_col*_data
349 Init_All_Col_Data
350 (p_start_index => 1,
351 p_col_count => l_orig_col_count,
352 p_row_count => p_row_count,
353 p_data => p_data,
354 p_col1_data => l_col1_data,
355 p_col2_data => l_col2_data,
356 p_col3_data => l_col3_data,
357 p_col4_data => l_col4_data,
358 p_col5_data => l_col5_data,
359 p_col6_data => l_col6_data,
360 p_col7_data => l_col7_data,
361 p_col8_data => l_col8_data,
362 p_col9_data => l_col9_data,
363 p_col10_data => l_col10_data,
364 p_col11_data => l_col11_data,
365 p_col12_data => l_col12_data,
366 p_col13_data => l_col13_data,
367 p_col14_data => l_col14_data,
368 p_col15_data => l_col15_data,
369 p_col16_data => l_col16_data,
370 p_col17_data => l_col17_data,
371 p_col18_data => l_col18_data,
372 p_col19_data => l_col19_data,
373 p_col20_data => l_col20_data,
374 p_col21_data => l_col21_data,
375 p_col22_data => l_col22_data,
376 p_col23_data => l_col23_data,
377 p_col24_data => l_col24_data,
378 p_col25_data => l_col25_data,
379 p_col26_data => l_col26_data,
380 p_col27_data => l_col27_data,
381 p_col28_data => l_col28_data,
382 p_col29_data => l_col29_data,
383 p_col30_data => l_col30_data,
384 p_col31_data => l_col31_data,
385 p_col32_data => l_col32_data,
386 p_col33_data => l_col33_data,
387 p_col34_data => l_col34_data,
388 p_col35_data => l_col35_data,
389 p_col36_data => l_col36_data,
390 p_col37_data => l_col37_data,
391 p_col38_data => l_col38_data,
392 p_col39_data => l_col39_data,
396
393 p_col40_data => l_col40_data,
394 p_insert_flag => FND_API.G_TRUE,
395 p_act_col_num => l_act_col_count);
397 cn_message_pkg.debug('Insert Data : Finish Copy Data from p_data');
398
399 -- get table name
400 l_tab_name_clause := l_tab_name_clause || p_table_name;
401
402 -- primary key column
403 l_col_clause := l_col_clause || 'IMP_LINE_ID' || ',';
404 l_value_clause := l_value_clause || ':p_keys(i),';
405
406 -- WHO columns , use bind variable
407 l_col_clause := l_col_clause || 'LAST_UPDATE_DATE,';
408 l_value_clause := l_value_clause || ':l_last_update_date, ';
409
410 l_col_clause := l_col_clause || 'LAST_UPDATED_BY,';
411 l_value_clause := l_value_clause || ':l_last_updated_by, ';
412
413 l_col_clause := l_col_clause || ' CREATION_DATE,';
414 l_value_clause := l_value_clause || ':l_creation_date, ';
415
416 l_col_clause := l_col_clause || 'CREATED_BY,';
417 l_value_clause := l_value_clause || ':l_created_by, ';
418
419 l_col_clause := l_col_clause || 'LAST_UPDATE_LOGIN,';
420 l_value_clause := l_value_clause || ':l_last_update_login, ';
421
422 l_col_clause := l_col_clause || 'OBJECT_VERSION_NUMBER,';
423 l_value_clause := l_value_clause || ':l_obj_ver_num, ';
424
425 -- imp_header_id,import_type_code,status_code column
426 l_col_clause := l_col_clause || 'IMP_HEADER_ID,';
427 l_value_clause := l_value_clause || ':p_imp_header_id, ';
428
429 l_col_clause := l_col_clause || 'IMPORT_TYPE_CODE,';
430 l_value_clause := l_value_clause || ':p_import_type_code, ';
431
432 l_col_clause := l_col_clause || 'STATUS_CODE,';
433 l_value_clause := l_value_clause || ':p_status, ';
434
435 -- Build sequence collection
436 -- initialize "l_prim_keys" table
437 l_prim_keys.EXTEND(p_row_count, 1);
438 FOR i IN 1 .. p_row_count LOOP
439 SELECT CN_IMP_LINES_S.NEXTVAL
440 INTO l_prim_keys(i) FROM dual;
441 END LOOP;
442
443 cn_message_pkg.debug('Insert Data : Start insert into DB');
444
445 -- insert into DB
446 Insert_To_DB
447 (p_prim_keys => l_prim_keys,
448 p_col_count => l_act_col_count,
449 p_row_count => p_row_count,
450 p_tab_name_clause => l_tab_name_clause,
451 p_col_clause => l_col_clause,
452 p_value_clause => l_value_clause,
453 p_col_names => p_col_names,
454 p_imp_header_id => p_imp_header_id,
455 p_import_type_code => p_import_type_code,
456 p_col1_data => l_col1_data,
457 p_col2_data => l_col2_data,
458 p_col3_data => l_col3_data,
459 p_col4_data => l_col4_data,
460 p_col5_data => l_col5_data,
461 p_col6_data => l_col6_data,
462 p_col7_data => l_col7_data,
463 p_col8_data => l_col8_data,
464 p_col9_data => l_col9_data,
465 p_col10_data => l_col10_data,
466 p_col11_data => l_col11_data,
467 p_col12_data => l_col12_data,
468 p_col13_data => l_col13_data,
469 p_col14_data => l_col14_data,
470 p_col15_data => l_col15_data,
471 p_col16_data => l_col16_data,
472 p_col17_data => l_col17_data,
473 p_col18_data => l_col18_data,
474 p_col19_data => l_col19_data,
475 p_col20_data => l_col20_data,
476 p_col21_data => l_col21_data,
477 p_col22_data => l_col22_data,
478 p_col23_data => l_col23_data,
479 p_col24_data => l_col24_data,
480 p_col25_data => l_col25_data,
481 p_col26_data => l_col26_data,
482 p_col27_data => l_col27_data,
483 p_col28_data => l_col28_data,
484 p_col29_data => l_col29_data,
485 p_col30_data => l_col30_data,
486 p_col31_data => l_col31_data,
487 p_col32_data => l_col32_data,
488 p_col33_data => l_col33_data,
489 p_col34_data => l_col34_data,
490 p_col35_data => l_col35_data,
491 p_col36_data => l_col36_data,
492 p_col37_data => l_col37_data,
493 p_col38_data => l_col38_data,
494 p_col39_data => l_col39_data,
495 p_col40_data => l_col40_data,
496 x_return_status => x_return_status,
497 x_msg_count => x_msg_count,
498 x_msg_data => x_msg_data);
499
500 -- If any errors happen abort API.
501 IF x_return_status <> FND_API.g_ret_sts_success THEN
502 RAISE FND_API.G_EXC_ERROR;
503 END IF;
504
505 cn_message_pkg.debug('Insert Data : Finish insert into DB');
506
507 l_col_count := l_act_col_count;
508
509 -- Still have more columns
510 WHILE l_col_count < l_orig_col_count
511 LOOP
512 cn_message_pkg.debug('Insert Data : Copy more data from p_data.');
513
514 Init_All_Col_Data
515 (p_start_index => l_col_count * p_row_count + 1,
516 p_col_count => l_orig_col_count - l_col_count,
517 p_row_count => p_row_count,
518 p_data => p_data,
522 p_col4_data => l_col4_data,
519 p_col1_data => l_col1_data,
520 p_col2_data => l_col2_data,
521 p_col3_data => l_col3_data,
523 p_col5_data => l_col5_data,
524 p_col6_data => l_col6_data,
525 p_col7_data => l_col7_data,
526 p_col8_data => l_col8_data,
527 p_col9_data => l_col9_data,
528 p_col10_data => l_col10_data,
529 p_col11_data => l_col11_data,
530 p_col12_data => l_col12_data,
531 p_col13_data => l_col13_data,
532 p_col14_data => l_col14_data,
533 p_col15_data => l_col15_data,
534 p_col16_data => l_col16_data,
535 p_col17_data => l_col17_data,
536 p_col18_data => l_col18_data,
537 p_col19_data => l_col19_data,
538 p_col20_data => l_col20_data,
539 p_col21_data => l_col21_data,
540 p_col22_data => l_col22_data,
541 p_col23_data => l_col23_data,
542 p_col24_data => l_col24_data,
543 p_col25_data => l_col25_data,
544 p_col26_data => l_col26_data,
545 p_col27_data => l_col27_data,
546 p_col28_data => l_col28_data,
547 p_col29_data => l_col29_data,
548 p_col30_data => l_col30_data,
549 p_col31_data => l_col31_data,
550 p_col32_data => l_col32_data,
551 p_col33_data => l_col33_data,
552 p_col34_data => l_col34_data,
553 p_col35_data => l_col35_data,
554 p_col36_data => l_col36_data,
555 p_col37_data => l_col37_data,
556 p_col38_data => l_col38_data,
557 p_col39_data => l_col39_data,
558 p_col40_data => l_col40_data,
559 p_insert_flag => FND_API.G_FALSE,
560 p_act_col_num => l_act_col_count);
561
562 cn_message_pkg.debug('Insert Data : Update more data from p_data.');
563
564 Update_DB
565 (p_prim_keys => l_prim_keys,
566 p_col_count => l_act_col_count,
567 p_row_count => p_row_count,
568
569 p_tab_name_name => p_table_name,
570 p_col_names => p_col_names,
571 p_col_start_count => l_col_count,
572
573 p_col1_data => l_col1_data,
574 p_col2_data => l_col2_data,
575 p_col3_data => l_col3_data,
576 p_col4_data => l_col4_data,
577 p_col5_data => l_col5_data,
578 p_col6_data => l_col6_data,
579 p_col7_data => l_col7_data,
580 p_col8_data => l_col8_data,
581 p_col9_data => l_col9_data,
582 p_col10_data => l_col10_data,
583 p_col11_data => l_col11_data,
584 p_col12_data => l_col12_data,
585 p_col13_data => l_col13_data,
586 p_col14_data => l_col14_data,
587 p_col15_data => l_col15_data,
588 p_col16_data => l_col16_data,
589 p_col17_data => l_col17_data,
590 p_col18_data => l_col18_data,
591 p_col19_data => l_col19_data,
592 p_col20_data => l_col20_data,
593 p_col21_data => l_col21_data,
594 p_col22_data => l_col22_data,
595 p_col23_data => l_col23_data,
596 p_col24_data => l_col24_data,
597 p_col25_data => l_col25_data,
598 p_col26_data => l_col26_data,
599 p_col27_data => l_col27_data,
600 p_col28_data => l_col28_data,
601 p_col29_data => l_col29_data,
602 p_col30_data => l_col30_data,
603 p_col31_data => l_col31_data,
604 p_col32_data => l_col32_data,
605 p_col33_data => l_col33_data,
606 p_col34_data => l_col34_data,
607 p_col35_data => l_col35_data,
608 p_col36_data => l_col36_data,
609 p_col37_data => l_col37_data,
610 p_col38_data => l_col38_data,
611 p_col39_data => l_col39_data,
612 p_col40_data => l_col40_data,
613
614 x_return_status => x_return_status,
615 x_msg_count => x_msg_count,
616 x_msg_data => x_msg_data
617 );
618
619 -- If any errors happen abort API.
620 IF x_return_status <> FND_API.g_ret_sts_success THEN
621 RAISE FND_API.G_EXC_ERROR;
622 END IF;
623
624 l_col_count := l_col_count + l_act_col_count;
625
626 cn_message_pkg.debug('Insert Data : Finish update to DB.');
627
628 END LOOP;
629
630 -- End of API body.
631
632 -- Standard check of p_commit.
633 IF FND_API.To_Boolean( p_commit ) THEN
634 COMMIT WORK;
635 END IF;
639 p_data => x_msg_data ,
636 -- Standard call to get message count and if count is 1, get message info.
637 FND_MSG_PUB.count_and_get
638 (p_count => x_msg_count ,
640 p_encoded => FND_API.G_FALSE
641 );
642
643 EXCEPTION
644 WHEN FND_API.G_EXC_ERROR THEN
645 ROLLBACK TO Insert_Data;
646 x_return_status := FND_API.G_RET_STS_ERROR;
647 FND_MSG_PUB.count_and_get
648 (p_count => x_msg_count,
649 p_data => x_msg_data
650 );
651 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
652 ROLLBACK TO Insert_Data;
653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
654 FND_MSG_PUB.Count_And_Get
655 (p_count => x_msg_count,
656 p_data => x_msg_data
657 );
658 WHEN OTHERS THEN
659 ROLLBACK TO Insert_Data;
660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
661 cn_message_pkg.set_error(l_api_name,'Unexpected Error.');
662 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
663 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
664 END IF;
665 FND_MSG_PUB.Count_And_Get (
666 p_count => x_msg_count,
667 p_data => x_msg_data
668 );
669 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
670 END Insert_Data;
671
672
673 -- NAME
674 -- Append_More_Data
675 --
676 -- PURPOSE
677 -- NOTES
678 --
679 -- HISTORY
680 --
681 PROCEDURE Append_More_Data (
682 p_str_col_names IN CN_IMPORT_PVT.char_data_set_type,
683 p_str_data IN CN_IMPORT_PVT.char_data_set_type,
684 p_num_col_names IN CN_IMPORT_PVT.char_data_set_type,
685 p_num_data IN CN_IMPORT_PVT.num_data_set_type,
686 p_col_clause IN OUT NOCOPY VARCHAR2,
687 p_val_clause IN OUT NOCOPY VARCHAR2
688 )
689 IS
690 BEGIN
691 FOR i IN p_str_col_names.FIRST .. p_str_col_names.LAST
692 LOOP
693 p_col_clause := p_col_clause || p_str_col_names(i) || ', ';
694 p_val_clause := p_val_clause || '''' || p_str_data(i) || ''', ';
695 END LOOP;
696
697 FOR j IN p_num_col_names.FIRST .. p_num_col_names.LAST
698 LOOP
699 p_col_clause := p_col_clause || p_num_col_names(j) || ', ';
700 p_val_clause := p_val_clause || '''' || p_num_data(j) || ''', ';
701 END LOOP;
702 END Append_More_Data;
703
704
705 -- NAME
706 -- Init_Col_Data
707 --
708 -- PURPOSE
709 -- Copy data from "p_data" to "p_col_data"
710 -- NOTES
711 --
712 -- HISTORY
713 --
714 PROCEDURE Init_Col_Data (
715 p_data IN CN_IMPORT_PVT.char_data_set_type,
716 p_start_index IN NUMBER,
717 p_end_index IN NUMBER,
718 p_insert_flag IN VARCHAR2 := FND_API.G_FALSE, --TRUE for insertion
719 p_col_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000
720 )
721 IS
722 l_count NUMBER := 1;
723 BEGIN
724 IF p_insert_flag = FND_API.G_TRUE THEN
725 p_col_data := JTF_VARCHAR2_TABLE_2000 ('t');
726 p_col_data.EXTEND (p_end_index - p_start_index, 1);
727 END IF;
728 FOR i IN p_start_index .. p_end_index - 1 LOOP
729 p_col_data (l_count) := trim(p_data(i));
730 l_count := l_count + 1;
731 END LOOP;
732 END Init_Col_Data;
733
734 -- NAME
735 -- Init_All_Col_Data
736 --
737 -- PURPOSE
738 -- Initialize all columns
739 -- NOTES
740 --
741 -- HISTORY
742 --
743 PROCEDURE Init_All_Col_Data
744 (p_start_index IN NUMBER,
745 p_col_count IN NUMBER,
746 p_row_count IN NUMBER,
747 p_data IN CN_IMPORT_PVT.char_data_set_type,
748 p_insert_flag IN VARCHAR2 := FND_API.G_FALSE, --TRUE for insertion
749 p_col1_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
750 p_col2_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
751 p_col3_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
752 p_col4_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
753 p_col5_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
754 p_col6_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
755 p_col7_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
756 p_col8_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
757 p_col9_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
758 p_col10_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
759
760 p_col11_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
761 p_col12_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
762 p_col13_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
763 p_col14_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
764 p_col15_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
765 p_col16_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
766 p_col17_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
767 p_col18_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
768 p_col19_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
772 p_col22_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
769 p_col20_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
770
771 p_col21_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
773 p_col23_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
774 p_col24_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
775 p_col25_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
776 p_col26_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
777 p_col27_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
778 p_col28_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
779 p_col29_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
780 p_col30_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
781
782 p_col31_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
783 p_col32_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
784 p_col33_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
785 p_col34_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
786 p_col35_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
787 p_col36_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
788 p_col37_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
789 p_col38_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
790 p_col39_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
791 p_col40_data IN OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
792
793 p_act_col_num OUT NOCOPY NUMBER
794
795 )
796 IS
797 l_start_index NUMBER;
798 l_index NUMBER := 1;
799 BEGIN
800 l_start_index := p_start_index;
801
802 IF p_col_count >= G_COL_NUM THEN
803 p_act_col_num := G_COL_NUM;
804 ELSE
805 p_act_col_num := p_col_count;
806 END IF;
807
808 IF p_col_count > 0 THEN
809 Init_Col_Data
810 (p_data => p_data,
811 p_start_index => l_start_index,
812 p_end_index => l_start_index + p_row_count,
813 p_col_data => p_col1_data,
814 p_insert_flag => p_insert_flag);
815 l_start_index := l_start_index + p_row_count;
816 END IF;
817 IF p_col_count > 1 THEN
818 Init_Col_Data (
819 p_data => p_data,
820 p_start_index => l_start_index,
821 p_end_index => l_start_index + p_row_count,
822 p_col_data => p_col2_data,
823 p_insert_flag => p_insert_flag);
824 l_start_index := l_start_index + p_row_count;
825 END IF;
826 IF p_col_count > 2 THEN
827 Init_Col_Data (
828 p_data => p_data,
829 p_start_index => l_start_index,
830 p_end_index => l_start_index + p_row_count,
831 p_col_data => p_col3_data,
832 p_insert_flag => p_insert_flag);
833 l_start_index := l_start_index + p_row_count;
834 END IF;
835 IF p_col_count > 3 THEN
836 Init_Col_Data (
837 p_data => p_data,
838 p_start_index => l_start_index,
839 p_end_index => l_start_index + p_row_count,
840 p_col_data => p_col4_data,
841 p_insert_flag => p_insert_flag);
842 l_start_index := l_start_index + p_row_count;
843 END IF;
844 IF p_col_count > 4 THEN
845 Init_Col_Data (
846 p_data => p_data,
847 p_start_index => l_start_index,
848 p_end_index => l_start_index + p_row_count,
849 p_col_data => p_col5_data,
850 p_insert_flag => p_insert_flag);
851 l_start_index := l_start_index + p_row_count;
852 END IF;
853 IF p_col_count > 5 THEN
854 Init_Col_Data (
855 p_data => p_data,
856 p_start_index => l_start_index,
857 p_end_index => l_start_index + p_row_count,
858 p_col_data => p_col6_data,
859 p_insert_flag => p_insert_flag);
860 l_start_index := l_start_index + p_row_count;
861 END IF;
862 IF p_col_count > 6 THEN
863 Init_Col_Data (
864 p_data => p_data,
865 p_start_index => l_start_index,
866 p_end_index => l_start_index + p_row_count,
867 p_col_data => p_col7_data,
868 p_insert_flag => p_insert_flag);
869 l_start_index := l_start_index + p_row_count;
870 END IF;
871 IF p_col_count > 7 THEN
872 Init_Col_Data (
873 p_data => p_data,
874 p_start_index => l_start_index,
875 p_end_index => l_start_index + p_row_count,
876 p_col_data => p_col8_data,
877 p_insert_flag => p_insert_flag);
878 l_start_index := l_start_index + p_row_count;
879 END IF;
880 IF p_col_count > 8 THEN
881 Init_Col_Data (
882 p_data => p_data,
883 p_start_index => l_start_index,
887 l_start_index := l_start_index + p_row_count;
884 p_end_index => l_start_index + p_row_count,
885 p_col_data => p_col9_data,
886 p_insert_flag => p_insert_flag);
888 END IF;
889 IF p_col_count > 9 THEN
890 Init_Col_Data (
891 p_data => p_data,
892 p_start_index => l_start_index,
893 p_end_index => l_start_index + p_row_count,
894 p_col_data => p_col10_data,
895 p_insert_flag => p_insert_flag);
896 l_start_index := l_start_index + p_row_count;
897 END IF;
898
899 IF p_col_count > 10 THEN
900 Init_Col_Data (
901 p_data => p_data,
902 p_start_index => l_start_index,
903 p_end_index => l_start_index + p_row_count,
904 p_col_data => p_col11_data,
905 p_insert_flag => p_insert_flag);
906 l_start_index := l_start_index + p_row_count;
907 END IF;
908 IF p_col_count > 11 THEN
909 Init_Col_Data (
910 p_data => p_data,
911 p_start_index => l_start_index,
912 p_end_index => l_start_index + p_row_count,
913 p_col_data => p_col12_data,
914 p_insert_flag => p_insert_flag);
915 l_start_index := l_start_index + p_row_count;
916 END IF;
917 IF p_col_count > 12 THEN
918 Init_Col_Data (
919 p_data => p_data,
920 p_start_index => l_start_index,
921 p_end_index => l_start_index + p_row_count,
922 p_col_data => p_col13_data,
923 p_insert_flag => p_insert_flag);
924 l_start_index := l_start_index + p_row_count;
925 END IF;
926 IF p_col_count > 13 THEN
927 Init_Col_Data (
928 p_data => p_data,
929 p_start_index => l_start_index,
930 p_end_index => l_start_index + p_row_count,
931 p_col_data => p_col14_data,
932 p_insert_flag => p_insert_flag);
933 l_start_index := l_start_index + p_row_count;
934 END IF;
935 IF p_col_count > 14 THEN
936 Init_Col_Data (
937 p_data => p_data,
938 p_start_index => l_start_index,
939 p_end_index => l_start_index + p_row_count,
940 p_col_data => p_col15_data,
941 p_insert_flag => p_insert_flag);
942 l_start_index := l_start_index + p_row_count;
943 END IF;
944 IF p_col_count > 15 THEN
945 Init_Col_Data (
946 p_data => p_data,
947 p_start_index => l_start_index,
948 p_end_index => l_start_index + p_row_count,
949 p_col_data => p_col16_data,
950 p_insert_flag => p_insert_flag);
951 l_start_index := l_start_index + p_row_count;
952 END IF;
953 IF p_col_count > 16 THEN
954 Init_Col_Data (
955 p_data => p_data,
956 p_start_index => l_start_index,
957 p_end_index => l_start_index + p_row_count,
958 p_col_data => p_col17_data,
959 p_insert_flag => p_insert_flag);
960 l_start_index := l_start_index + p_row_count;
961 END IF;
962 IF p_col_count > 17 THEN
963 Init_Col_Data (
964 p_data => p_data,
965 p_start_index => l_start_index,
966 p_end_index => l_start_index + p_row_count,
967 p_col_data => p_col18_data,
968 p_insert_flag => p_insert_flag);
969 l_start_index := l_start_index + p_row_count;
970 END IF;
971 IF p_col_count > 18 THEN
972 Init_Col_Data (
973 p_data => p_data,
974 p_start_index => l_start_index,
975 p_end_index => l_start_index + p_row_count,
976 p_col_data => p_col19_data,
977 p_insert_flag => p_insert_flag);
978 l_start_index := l_start_index + p_row_count;
979 END IF;
980 IF p_col_count > 19 THEN
981 Init_Col_Data (
982 p_data => p_data,
983 p_start_index => l_start_index,
984 p_end_index => l_start_index + p_row_count,
985 p_col_data => p_col20_data,
986 p_insert_flag => p_insert_flag);
987 l_start_index := l_start_index + p_row_count;
988 END IF;
989
990 IF p_col_count > 20 THEN
991 Init_Col_Data (
992 p_data => p_data,
993 p_start_index => l_start_index,
994 p_end_index => l_start_index + p_row_count,
995 p_col_data => p_col21_data,
996 p_insert_flag => p_insert_flag);
997 l_start_index := l_start_index + p_row_count;
998 END IF;
999 IF p_col_count > 21 THEN
1000 Init_Col_Data (
1001 p_data => p_data,
1002 p_start_index => l_start_index,
1006 l_start_index := l_start_index + p_row_count;
1003 p_end_index => l_start_index + p_row_count,
1004 p_col_data => p_col22_data,
1005 p_insert_flag => p_insert_flag);
1007 END IF;
1008 IF p_col_count > 22 THEN
1009 Init_Col_Data (
1010 p_data => p_data,
1011 p_start_index => l_start_index,
1012 p_end_index => l_start_index + p_row_count,
1013 p_col_data => p_col23_data,
1014 p_insert_flag => p_insert_flag);
1015 l_start_index := l_start_index + p_row_count;
1016 END IF;
1017 IF p_col_count > 23 THEN
1018 Init_Col_Data (
1019 p_data => p_data,
1020 p_start_index => l_start_index,
1021 p_end_index => l_start_index + p_row_count,
1022 p_col_data => p_col24_data,
1023 p_insert_flag => p_insert_flag);
1024 l_start_index := l_start_index + p_row_count;
1025 END IF;
1026 IF p_col_count > 24 THEN
1027 Init_Col_Data (
1028 p_data => p_data,
1029 p_start_index => l_start_index,
1030 p_end_index => l_start_index + p_row_count,
1031 p_col_data => p_col25_data,
1032 p_insert_flag => p_insert_flag);
1033 l_start_index := l_start_index + p_row_count;
1034 END IF;
1035 IF p_col_count > 25 THEN
1036 Init_Col_Data (
1037 p_data => p_data,
1038 p_start_index => l_start_index,
1039 p_end_index => l_start_index + p_row_count,
1040 p_col_data => p_col26_data,
1041 p_insert_flag => p_insert_flag);
1042 l_start_index := l_start_index + p_row_count;
1043 END IF;
1044 IF p_col_count > 26 THEN
1045 Init_Col_Data (
1046 p_data => p_data,
1047 p_start_index => l_start_index,
1048 p_end_index => l_start_index + p_row_count,
1049 p_col_data => p_col27_data,
1050 p_insert_flag => p_insert_flag);
1051 l_start_index := l_start_index + p_row_count;
1052 END IF;
1053 IF p_col_count > 27 THEN
1054 Init_Col_Data (
1055 p_data => p_data,
1056 p_start_index => l_start_index,
1057 p_end_index => l_start_index + p_row_count,
1058 p_col_data => p_col28_data,
1059 p_insert_flag => p_insert_flag);
1060 l_start_index := l_start_index + p_row_count;
1061 END IF;
1062 IF p_col_count > 28 THEN
1063 Init_Col_Data (
1064 p_data => p_data,
1065 p_start_index => l_start_index,
1066 p_end_index => l_start_index + p_row_count,
1067 p_col_data => p_col29_data,
1068 p_insert_flag => p_insert_flag);
1069 l_start_index := l_start_index + p_row_count;
1070 END IF;
1071 IF p_col_count > 29 THEN
1072 Init_Col_Data (
1073 p_data => p_data,
1074 p_start_index => l_start_index,
1075 p_end_index => l_start_index + p_row_count,
1076 p_col_data => p_col30_data,
1077 p_insert_flag => p_insert_flag);
1078 l_start_index := l_start_index + p_row_count;
1079 END IF;
1080
1081 IF p_col_count > 30 THEN
1082 Init_Col_Data (
1083 p_data => p_data,
1084 p_start_index => l_start_index,
1085 p_end_index => l_start_index + p_row_count,
1086 p_col_data => p_col31_data,
1087 p_insert_flag => p_insert_flag);
1088 l_start_index := l_start_index + p_row_count;
1089 END IF;
1090 IF p_col_count > 31 THEN
1091 Init_Col_Data (
1092 p_data => p_data,
1093 p_start_index => l_start_index,
1094 p_end_index => l_start_index + p_row_count,
1095 p_col_data => p_col32_data,
1096 p_insert_flag => p_insert_flag);
1097 l_start_index := l_start_index + p_row_count;
1098 END IF;
1099 IF p_col_count > 32 THEN
1100 Init_Col_Data (
1101 p_data => p_data,
1102 p_start_index => l_start_index,
1103 p_end_index => l_start_index + p_row_count,
1104 p_col_data => p_col33_data,
1105 p_insert_flag => p_insert_flag);
1106 l_start_index := l_start_index + p_row_count;
1107 END IF;
1108 IF p_col_count > 33 THEN
1109 Init_Col_Data (
1110 p_data => p_data,
1111 p_start_index => l_start_index,
1112 p_end_index => l_start_index + p_row_count,
1113 p_col_data => p_col34_data,
1114 p_insert_flag => p_insert_flag);
1115 l_start_index := l_start_index + p_row_count;
1116 END IF;
1117 IF p_col_count > 34 THEN
1118 Init_Col_Data (
1119 p_data => p_data,
1123 p_insert_flag => p_insert_flag);
1120 p_start_index => l_start_index,
1121 p_end_index => l_start_index + p_row_count,
1122 p_col_data => p_col35_data,
1124 l_start_index := l_start_index + p_row_count;
1125 END IF;
1126 IF p_col_count > 35 THEN
1127 Init_Col_Data (
1128 p_data => p_data,
1129 p_start_index => l_start_index,
1130 p_end_index => l_start_index + p_row_count,
1131 p_col_data => p_col36_data,
1132 p_insert_flag => p_insert_flag);
1133 l_start_index := l_start_index + p_row_count;
1134 END IF;
1135 IF p_col_count > 36 THEN
1136 Init_Col_Data (
1137 p_data => p_data,
1138 p_start_index => l_start_index,
1139 p_end_index => l_start_index + p_row_count,
1140 p_col_data => p_col37_data,
1141 p_insert_flag => p_insert_flag);
1142 l_start_index := l_start_index + p_row_count;
1143 END IF;
1144 IF p_col_count > 37 THEN
1145 Init_Col_Data (
1146 p_data => p_data,
1147 p_start_index => l_start_index,
1148 p_end_index => l_start_index + p_row_count,
1149 p_col_data => p_col38_data,
1150 p_insert_flag => p_insert_flag);
1151 l_start_index := l_start_index + p_row_count;
1152 END IF;
1153 IF p_col_count > 38 THEN
1154 Init_Col_Data (
1155 p_data => p_data,
1156 p_start_index => l_start_index,
1157 p_end_index => l_start_index + p_row_count,
1158 p_col_data => p_col39_data,
1159 p_insert_flag => p_insert_flag);
1160 l_start_index := l_start_index + p_row_count;
1161 END IF;
1162 IF p_col_count > 39 THEN
1163 Init_Col_Data (
1164 p_data => p_data,
1165 p_start_index => l_start_index,
1166 p_end_index => l_start_index + p_row_count,
1167 p_col_data => p_col40_data,
1168 p_insert_flag => p_insert_flag);
1169 l_start_index := l_start_index + p_row_count;
1170 END IF;
1171 END Init_All_Col_Data;
1172
1173 -- NAME
1174 -- Insert_To_DB
1175 --
1176 -- PURPOSE
1177 -- Insert data to database
1178 -- NOTES
1179 --
1180 -- HISTORY
1181 --
1182 PROCEDURE Insert_To_DB (
1183 p_prim_keys IN JTF_NUMBER_TABLE,
1184 p_col_count IN NUMBER, --actual column count
1185 p_row_count IN NUMBER,
1186
1187 p_tab_name_clause IN VARCHAR2,
1188 p_col_clause IN VARCHAR2,
1189 p_value_clause IN VARCHAR2,
1190 p_col_names IN CN_IMPORT_PVT.char_data_set_type,
1191 p_imp_header_id IN NUMBER,
1192 p_import_type_code IN VARCHAR2,
1193
1194 p_col1_data IN JTF_VARCHAR2_TABLE_2000,
1195 p_col2_data IN JTF_VARCHAR2_TABLE_2000,
1196 p_col3_data IN JTF_VARCHAR2_TABLE_2000,
1197 p_col4_data IN JTF_VARCHAR2_TABLE_2000,
1198 p_col5_data IN JTF_VARCHAR2_TABLE_2000,
1199 p_col6_data IN JTF_VARCHAR2_TABLE_2000,
1200 p_col7_data IN JTF_VARCHAR2_TABLE_2000,
1201 p_col8_data IN JTF_VARCHAR2_TABLE_2000,
1202 p_col9_data IN JTF_VARCHAR2_TABLE_2000,
1203 p_col10_data IN JTF_VARCHAR2_TABLE_2000,
1204 p_col11_data IN JTF_VARCHAR2_TABLE_2000,
1205 p_col12_data IN JTF_VARCHAR2_TABLE_2000,
1206 p_col13_data IN JTF_VARCHAR2_TABLE_2000,
1207 p_col14_data IN JTF_VARCHAR2_TABLE_2000,
1208 p_col15_data IN JTF_VARCHAR2_TABLE_2000,
1209 p_col16_data IN JTF_VARCHAR2_TABLE_2000,
1210 p_col17_data IN JTF_VARCHAR2_TABLE_2000,
1211 p_col18_data IN JTF_VARCHAR2_TABLE_2000,
1212 p_col19_data IN JTF_VARCHAR2_TABLE_2000,
1213 p_col20_data IN JTF_VARCHAR2_TABLE_2000,
1214 p_col21_data IN JTF_VARCHAR2_TABLE_2000,
1215 p_col22_data IN JTF_VARCHAR2_TABLE_2000,
1216 p_col23_data IN JTF_VARCHAR2_TABLE_2000,
1217 p_col24_data IN JTF_VARCHAR2_TABLE_2000,
1218 p_col25_data IN JTF_VARCHAR2_TABLE_2000,
1219 p_col26_data IN JTF_VARCHAR2_TABLE_2000,
1220 p_col27_data IN JTF_VARCHAR2_TABLE_2000,
1221 p_col28_data IN JTF_VARCHAR2_TABLE_2000,
1222 p_col29_data IN JTF_VARCHAR2_TABLE_2000,
1223 p_col30_data IN JTF_VARCHAR2_TABLE_2000,
1224 p_col31_data IN JTF_VARCHAR2_TABLE_2000,
1225 p_col32_data IN JTF_VARCHAR2_TABLE_2000,
1226 p_col33_data IN JTF_VARCHAR2_TABLE_2000,
1227 p_col34_data IN JTF_VARCHAR2_TABLE_2000,
1231 p_col38_data IN JTF_VARCHAR2_TABLE_2000,
1228 p_col35_data IN JTF_VARCHAR2_TABLE_2000,
1229 p_col36_data IN JTF_VARCHAR2_TABLE_2000,
1230 p_col37_data IN JTF_VARCHAR2_TABLE_2000,
1232 p_col39_data IN JTF_VARCHAR2_TABLE_2000,
1233 p_col40_data IN JTF_VARCHAR2_TABLE_2000,
1234 x_return_status OUT NOCOPY VARCHAR2,
1235 x_msg_count OUT NOCOPY NUMBER,
1236 x_msg_data OUT NOCOPY VARCHAR2
1237 )
1238 IS
1239
1240 --
1241 -- Standard API information constants.
1242 --
1243 L_API_VERSION CONSTANT NUMBER := 1.0;
1244 L_API_NAME CONSTANT VARCHAR2(30) := 'INSERT_TO_DB';
1245 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
1246
1247 l_col_clause VARCHAR2(8000);
1248 l_value_clause VARCHAR2(8000);
1249 l_col_count NUMBER := p_col_count;
1250 l_index NUMBER := 1;
1251
1252 -- only calculate system date once
1253 l_date DATE := SYSDATE;
1254 l_user_id NUMBER := FND_GLOBAL.User_ID;
1255 l_login_id NUMBER := FND_GLOBAL.Login_ID;
1256 l_objvernum NUMBER := 1;
1257 l_status cn_imp_lines.status_code%TYPE := 'STAGE';
1258
1259 BEGIN
1260
1261 IF p_col_count >= G_COL_NUM THEN
1262 l_col_count := G_COL_NUM;
1263 END IF;
1264
1265 l_col_clause := p_col_clause;
1266 l_value_clause := p_value_clause;
1267
1268 WHILE l_index < l_col_count
1269 LOOP
1270 l_col_clause := l_col_clause || p_col_names(l_index) || ',';
1271 l_value_clause := l_value_clause || ':val' || l_index || '(i),';
1272 l_index := l_index + 1;
1273 END LOOP;
1274
1275 l_col_clause := l_col_clause || p_col_names( l_col_count) || ') ';
1276 l_value_clause := l_value_clause || ':val' || l_col_count || '(i)); END;';
1277
1278 cn_message_pkg.debug('Insert TO DB : ' || p_tab_name_clause );
1279 cn_message_pkg.debug('Insert TO DB : ' || l_col_clause);
1280 cn_message_pkg.debug('Insert TO DB : ' || l_value_clause);
1281
1282 --
1283 -- Done for all command
1284 --
1285 IF l_col_count = 1 THEN
1286 EXECUTE IMMEDIATE
1287 p_tab_name_clause || l_col_clause || l_value_clause
1288 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1289 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1290 p_col1_data;
1291 ELSIF l_col_count = 2 THEN
1292 EXECUTE IMMEDIATE
1293 p_tab_name_clause || l_col_clause || l_value_clause
1294 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1295 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1296 p_col1_data, p_col2_data;
1297 ELSIF l_col_count = 3 THEN
1298 EXECUTE IMMEDIATE
1299 p_tab_name_clause || l_col_clause || l_value_clause
1300 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1301 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1302 p_col1_data, p_col2_data, p_col3_data;
1303 ELSIF l_col_count = 4 THEN
1304 EXECUTE IMMEDIATE
1305 p_tab_name_clause || l_col_clause || l_value_clause
1306 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1307 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1308 p_col1_data, p_col2_data, p_col3_data,
1309 p_col4_data;
1310 ELSIF l_col_count = 5 THEN
1311 EXECUTE IMMEDIATE
1312 p_tab_name_clause || l_col_clause || l_value_clause
1313 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1314 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1315 p_col1_data, p_col2_data, p_col3_data,
1316 p_col4_data, p_col5_data;
1317 ELSIF l_col_count = 6 THEN
1318 EXECUTE IMMEDIATE
1319 p_tab_name_clause || l_col_clause || l_value_clause
1320 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1321 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1322 p_col1_data, p_col2_data, p_col3_data,
1323 p_col4_data, p_col5_data, p_col6_data;
1324 ELSIF l_col_count = 7 THEN
1325 EXECUTE IMMEDIATE
1326 p_tab_name_clause || l_col_clause || l_value_clause
1327 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1328 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1329 p_col1_data, p_col2_data, p_col3_data,
1330 p_col4_data, p_col5_data, p_col6_data, p_col7_data;
1331 ELSIF l_col_count = 8 THEN
1332 EXECUTE IMMEDIATE
1333 p_tab_name_clause || l_col_clause || l_value_clause
1334 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1335 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1336 p_col1_data, p_col2_data, p_col3_data,
1337 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data;
1338 ELSIF l_col_count = 9 THEN
1339 EXECUTE IMMEDIATE
1340 p_tab_name_clause || l_col_clause || l_value_clause
1341 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1342 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1343 p_col1_data, p_col2_data, p_col3_data,
1347 EXECUTE IMMEDIATE
1344 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1345 p_col9_data;
1346 ELSIF l_col_count = 10 THEN
1348 p_tab_name_clause || l_col_clause || l_value_clause
1349 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1350 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1351 p_col1_data, p_col2_data, p_col3_data,
1352 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1353 p_col9_data, p_col10_data;
1354 ELSIF l_col_count = 11 THEN
1355 EXECUTE IMMEDIATE
1356 p_tab_name_clause || l_col_clause || l_value_clause
1357 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1358 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1359 p_col1_data, p_col2_data, p_col3_data,
1360 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1361 p_col9_data, p_col10_data, p_col11_data;
1362 ELSIF l_col_count = 12 THEN
1363 EXECUTE IMMEDIATE
1364 p_tab_name_clause || l_col_clause || l_value_clause
1365 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1366 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1367 p_col1_data, p_col2_data, p_col3_data,
1368 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1369 p_col9_data, p_col10_data, p_col11_data, p_col12_data;
1370 ELSIF l_col_count = 13 THEN
1371 EXECUTE IMMEDIATE
1372 p_tab_name_clause || l_col_clause || l_value_clause
1373 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1374 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1375 p_col1_data, p_col2_data, p_col3_data,
1376 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1377 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data;
1378 ELSIF l_col_count = 14 THEN
1379 EXECUTE IMMEDIATE
1380 p_tab_name_clause || l_col_clause || l_value_clause
1381 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1382 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1383 p_col1_data, p_col2_data, p_col3_data,
1384 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1385 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1386 p_col14_data;
1387 ELSIF l_col_count = 15 THEN
1388 EXECUTE IMMEDIATE
1389 p_tab_name_clause || l_col_clause || l_value_clause
1390 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1391 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1392 p_col1_data, p_col2_data, p_col3_data,
1393 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1394 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1395 p_col14_data, p_col15_data;
1396 ELSIF l_col_count = 16 THEN
1397 EXECUTE IMMEDIATE
1398 p_tab_name_clause || l_col_clause || l_value_clause
1399 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1400 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1401 p_col1_data, p_col2_data, p_col3_data,
1402 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1403 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1404 p_col14_data, p_col15_data, p_col16_data;
1405 ELSIF l_col_count = 17 THEN
1406 EXECUTE IMMEDIATE
1407 p_tab_name_clause || l_col_clause || l_value_clause
1408 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1409 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1410 p_col1_data, p_col2_data, p_col3_data,
1411 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1412 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1413 p_col14_data, p_col15_data, p_col16_data, p_col17_data;
1414 ELSIF l_col_count = 18 THEN
1415 EXECUTE IMMEDIATE
1416 p_tab_name_clause || l_col_clause || l_value_clause
1417 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1418 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1419 p_col1_data, p_col2_data, p_col3_data,
1420 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1421 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1422 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data;
1423 ELSIF l_col_count = 19 THEN
1424 EXECUTE IMMEDIATE
1425 p_tab_name_clause || l_col_clause || l_value_clause
1426 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1427 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1428 p_col1_data, p_col2_data, p_col3_data,
1429 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1430 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1431 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1432 p_col19_data;
1433 ELSIF l_col_count = 20 THEN
1434 EXECUTE IMMEDIATE
1435 p_tab_name_clause || l_col_clause || l_value_clause
1436 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1437 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1438 p_col1_data, p_col2_data, p_col3_data,
1442 p_col19_data, p_col20_data;
1439 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1440 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1441 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1443 ELSIF l_col_count = 21 THEN
1444 EXECUTE IMMEDIATE
1445 p_tab_name_clause || l_col_clause || l_value_clause
1446 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1447 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1448 p_col1_data, p_col2_data, p_col3_data,
1449 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1450 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1451 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1452 p_col19_data, p_col20_data, p_col21_data;
1453 ELSIF l_col_count = 22 THEN
1454 EXECUTE IMMEDIATE
1455 p_tab_name_clause || l_col_clause || l_value_clause
1456 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1457 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1458 p_col1_data, p_col2_data, p_col3_data,
1459 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1460 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1461 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1462 p_col19_data, p_col20_data, p_col21_data, p_col22_data;
1463 ELSIF l_col_count = 23 THEN
1464 EXECUTE IMMEDIATE
1465 p_tab_name_clause || l_col_clause || l_value_clause
1466 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1467 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1468 p_col1_data, p_col2_data, p_col3_data,
1469 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1470 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1471 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1472 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data;
1473 ELSIF l_col_count = 24 THEN
1474 EXECUTE IMMEDIATE
1475 p_tab_name_clause || l_col_clause || l_value_clause
1476 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1477 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1478 p_col1_data, p_col2_data, p_col3_data,
1479 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1480 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1481 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1482 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1483 p_col24_data;
1484 ELSIF l_col_count = 25 THEN
1485 EXECUTE IMMEDIATE
1486 p_tab_name_clause || l_col_clause || l_value_clause
1487 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1488 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1489 p_col1_data, p_col2_data, p_col3_data,
1490 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1491 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1492 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1493 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1494 p_col24_data, p_col25_data;
1495 ELSIF l_col_count = 26 THEN
1496 EXECUTE IMMEDIATE
1497 p_tab_name_clause || l_col_clause || l_value_clause
1498 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1499 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1500 p_col1_data, p_col2_data, p_col3_data,
1501 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1502 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1503 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1504 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1505 p_col24_data, p_col25_data, p_col26_data;
1506 ELSIF l_col_count = 27 THEN
1507 EXECUTE IMMEDIATE
1508 p_tab_name_clause || l_col_clause || l_value_clause
1509 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1510 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1511 p_col1_data, p_col2_data, p_col3_data,
1512 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1513 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1514 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1515 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1516 p_col24_data, p_col25_data, p_col26_data, p_col27_data;
1517 ELSIF l_col_count = 28 THEN
1518 EXECUTE IMMEDIATE
1519 p_tab_name_clause || l_col_clause || l_value_clause
1520 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1521 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1522 p_col1_data, p_col2_data, p_col3_data,
1523 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1524 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1525 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1526 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1527 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data;
1531 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1528 ELSIF l_col_count = 29 THEN
1529 EXECUTE IMMEDIATE
1530 p_tab_name_clause || l_col_clause || l_value_clause
1532 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1533 p_col1_data, p_col2_data, p_col3_data,
1534 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1535 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1536 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1537 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1538 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data,
1539 p_col29_data;
1540 ELSIF l_col_count = 30 THEN
1541 EXECUTE IMMEDIATE
1542 p_tab_name_clause || l_col_clause || l_value_clause
1543 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1544 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1545 p_col1_data, p_col2_data, p_col3_data,
1546 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1547 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1548 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1549 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1550 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data,
1551 p_col29_data, p_col30_data;
1552 ELSIF l_col_count = 31 THEN
1553 EXECUTE IMMEDIATE
1554 p_tab_name_clause || l_col_clause || l_value_clause
1555 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1556 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1557 p_col1_data, p_col2_data, p_col3_data,
1558 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1559 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1560 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1561 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1562 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data,
1563 p_col29_data, p_col30_data, p_col31_data;
1564 ELSIF l_col_count = 32 THEN
1565 EXECUTE IMMEDIATE
1566 p_tab_name_clause || l_col_clause || l_value_clause
1567 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1568 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1569 p_col1_data, p_col2_data, p_col3_data,
1570 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1571 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1572 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1573 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1574 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data,
1575 p_col29_data, p_col30_data, p_col31_data, p_col32_data;
1576 ELSIF l_col_count = 33 THEN
1577 EXECUTE IMMEDIATE
1578 p_tab_name_clause || l_col_clause || l_value_clause
1579 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1580 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1581 p_col1_data, p_col2_data, p_col3_data,
1582 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1583 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1584 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1585 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1586 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data,
1587 p_col29_data, p_col30_data, p_col31_data, p_col32_data, p_col33_data;
1588 ELSIF l_col_count = 34 THEN
1589 EXECUTE IMMEDIATE
1590 p_tab_name_clause || l_col_clause || l_value_clause
1591 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1592 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1593 p_col1_data, p_col2_data, p_col3_data,
1594 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1595 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1596 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1597 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1598 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data,
1599 p_col29_data, p_col30_data, p_col31_data, p_col32_data, p_col33_data,
1600 p_col34_data;
1601 ELSIF l_col_count = 35 THEN
1602 EXECUTE IMMEDIATE
1603 p_tab_name_clause || l_col_clause || l_value_clause
1604 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1605 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1606 p_col1_data, p_col2_data, p_col3_data,
1607 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1608 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1609 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1610 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1611 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data,
1612 p_col29_data, p_col30_data, p_col31_data, p_col32_data, p_col33_data,
1613 p_col34_data, p_col35_data;
1614 ELSIF l_col_count = 36 THEN
1615 EXECUTE IMMEDIATE
1619 p_col1_data, p_col2_data, p_col3_data,
1616 p_tab_name_clause || l_col_clause || l_value_clause
1617 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1618 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1620 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1621 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1622 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1623 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1624 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data,
1625 p_col29_data, p_col30_data, p_col31_data, p_col32_data, p_col33_data,
1626 p_col34_data, p_col35_data, p_col36_data;
1627 ELSIF l_col_count = 37 THEN
1628 EXECUTE IMMEDIATE
1629 p_tab_name_clause || l_col_clause || l_value_clause
1630 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1631 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1632 p_col1_data, p_col2_data, p_col3_data,
1633 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1634 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1635 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1636 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1637 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data,
1638 p_col29_data, p_col30_data, p_col31_data, p_col32_data, p_col33_data,
1639 p_col34_data, p_col35_data, p_col36_data, p_col37_data;
1640 ELSIF l_col_count = 38 THEN
1641 EXECUTE IMMEDIATE
1642 p_tab_name_clause || l_col_clause || l_value_clause
1643 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1644 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1645 p_col1_data, p_col2_data, p_col3_data,
1646 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1647 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1648 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1649 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1650 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data,
1651 p_col29_data, p_col30_data, p_col31_data, p_col32_data, p_col33_data,
1652 p_col34_data, p_col35_data, p_col36_data, p_col37_data, p_col38_data;
1653 ELSIF l_col_count = 39 THEN
1654 EXECUTE IMMEDIATE
1655 p_tab_name_clause || l_col_clause || l_value_clause
1656 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1657 l_login_id, l_objvernum, p_imp_header_id, p_import_type_code, l_status,
1658 p_col1_data, p_col2_data, p_col3_data,
1659 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1660 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1661 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1662 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1663 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data,
1664 p_col29_data, p_col30_data, p_col31_data, p_col32_data, p_col33_data,
1665 p_col34_data, p_col35_data, p_col36_data, p_col37_data, p_col38_data,
1666 p_col39_data;
1667 ELSIF l_col_count = 40 THEN
1668 EXECUTE IMMEDIATE
1669 p_tab_name_clause || l_col_clause || l_value_clause
1670 USING p_row_count, p_prim_keys, l_date, l_user_id, l_date, l_user_id,
1671 l_login_id, l_objvernum , p_imp_header_id, p_import_type_code, l_status,
1672 p_col1_data, p_col2_data, p_col3_data,
1673 p_col4_data, p_col5_data, p_col6_data, p_col7_data, p_col8_data,
1674 p_col9_data, p_col10_data, p_col11_data, p_col12_data, p_col13_data,
1675 p_col14_data, p_col15_data, p_col16_data, p_col17_data, p_col18_data,
1676 p_col19_data, p_col20_data, p_col21_data, p_col22_data, p_col23_data,
1677 p_col24_data, p_col25_data, p_col26_data, p_col27_data, p_col28_data,
1678 p_col29_data, p_col30_data, p_col31_data, p_col32_data, p_col33_data,
1679 p_col34_data, p_col35_data, p_col36_data, p_col37_data, p_col38_data,
1680 p_col39_data, p_col40_data;
1681 END IF;
1682
1683
1684 -- Standard call to get message count and if count is 1, get message info.
1685 FND_MSG_PUB.Count_And_Get
1686 (p_count => x_msg_count,
1687 p_data => x_msg_data,
1688 p_encoded => FND_API.G_FALSE
1689 );
1690
1691 EXCEPTION
1692 WHEN OTHERS THEN
1693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1694 cn_message_pkg.set_error(l_api_name,'Unexpected Error.');
1695 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1696 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1697 END IF;
1698 FND_MSG_PUB.Count_And_Get (
1699 p_count => x_msg_count,
1700 p_data => x_msg_data
1701 );
1702 END Insert_To_DB;
1703
1704 -- NAME
1705 -- Update_DB
1706 --
1707 -- PURPOSE
1708 -- Inser more data to DB by updating the existing rows inserted
1709 -- NOTES
1710 --
1711 -- HISTORY
1712 --
1713 PROCEDURE Update_DB (
1714 p_prim_keys IN JTF_NUMBER_TABLE,
1715 p_col_count IN NUMBER,
1716 p_row_count IN NUMBER,
1717
1718 p_tab_name_name IN VARCHAR2,
1722 p_col1_data IN JTF_VARCHAR2_TABLE_2000,
1719 p_col_names IN CN_IMPORT_PVT.char_data_set_type,
1720 p_col_start_count IN NUMBER,
1721
1723 p_col2_data IN JTF_VARCHAR2_TABLE_2000,
1724 p_col3_data IN JTF_VARCHAR2_TABLE_2000,
1725 p_col4_data IN JTF_VARCHAR2_TABLE_2000,
1726 p_col5_data IN JTF_VARCHAR2_TABLE_2000,
1727 p_col6_data IN JTF_VARCHAR2_TABLE_2000,
1728 p_col7_data IN JTF_VARCHAR2_TABLE_2000,
1729 p_col8_data IN JTF_VARCHAR2_TABLE_2000,
1730 p_col9_data IN JTF_VARCHAR2_TABLE_2000,
1731 p_col10_data IN JTF_VARCHAR2_TABLE_2000,
1732 p_col11_data IN JTF_VARCHAR2_TABLE_2000,
1733 p_col12_data IN JTF_VARCHAR2_TABLE_2000,
1734 p_col13_data IN JTF_VARCHAR2_TABLE_2000,
1735 p_col14_data IN JTF_VARCHAR2_TABLE_2000,
1736 p_col15_data IN JTF_VARCHAR2_TABLE_2000,
1737 p_col16_data IN JTF_VARCHAR2_TABLE_2000,
1738 p_col17_data IN JTF_VARCHAR2_TABLE_2000,
1739 p_col18_data IN JTF_VARCHAR2_TABLE_2000,
1740 p_col19_data IN JTF_VARCHAR2_TABLE_2000,
1741 p_col20_data IN JTF_VARCHAR2_TABLE_2000,
1742 p_col21_data IN JTF_VARCHAR2_TABLE_2000,
1743 p_col22_data IN JTF_VARCHAR2_TABLE_2000,
1744 p_col23_data IN JTF_VARCHAR2_TABLE_2000,
1745 p_col24_data IN JTF_VARCHAR2_TABLE_2000,
1746 p_col25_data IN JTF_VARCHAR2_TABLE_2000,
1747 p_col26_data IN JTF_VARCHAR2_TABLE_2000,
1748 p_col27_data IN JTF_VARCHAR2_TABLE_2000,
1749 p_col28_data IN JTF_VARCHAR2_TABLE_2000,
1750 p_col29_data IN JTF_VARCHAR2_TABLE_2000,
1751 p_col30_data IN JTF_VARCHAR2_TABLE_2000,
1752 p_col31_data IN JTF_VARCHAR2_TABLE_2000,
1753 p_col32_data IN JTF_VARCHAR2_TABLE_2000,
1754 p_col33_data IN JTF_VARCHAR2_TABLE_2000,
1755 p_col34_data IN JTF_VARCHAR2_TABLE_2000,
1756 p_col35_data IN JTF_VARCHAR2_TABLE_2000,
1757 p_col36_data IN JTF_VARCHAR2_TABLE_2000,
1758 p_col37_data IN JTF_VARCHAR2_TABLE_2000,
1759 p_col38_data IN JTF_VARCHAR2_TABLE_2000,
1760 p_col39_data IN JTF_VARCHAR2_TABLE_2000,
1761 p_col40_data IN JTF_VARCHAR2_TABLE_2000,
1762
1763 x_return_status OUT NOCOPY VARCHAR2,
1764 x_msg_count OUT NOCOPY NUMBER,
1765 x_msg_data OUT NOCOPY VARCHAR2
1766 )
1767 IS
1768
1769 --
1770 -- Standard API information constants.
1771 --
1772 L_API_VERSION CONSTANT NUMBER := 1.0;
1773 L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_DB';
1774 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
1775
1776 l_update_sql VARCHAR2(8000) := 'BEGIN FORALL i IN 1 .. :row_count UPDATE ';
1777 l_col_count NUMBER;
1778 l_index NUMBER := 1;
1779 BEGIN
1780
1781 l_update_sql := l_update_sql || p_tab_name_name || ' SET ';
1782
1783 --
1784 -- max 40 columns
1785 --
1786 IF p_col_count <= G_COL_NUM THEN
1787 l_col_count := p_col_count;
1788 ELSE
1789 l_col_count := G_COL_NUM;
1790 END IF;
1791
1792 WHILE l_index < l_col_count LOOP
1793 l_update_sql := l_update_sql || p_col_names (p_col_start_count + l_index)
1794 || '=:val' || l_index || '(i), ';
1795 l_index := l_index + 1;
1796 END LOOP;
1797
1798 l_update_sql := l_update_sql || p_col_names (p_col_start_count + l_index)
1799 || '= :val' || l_index || '(i) ';
1800
1801 l_update_sql := l_update_sql || ' WHERE IMP_LINE_ID = :p_keys(i); END;';
1802
1803 cn_message_pkg.debug('Update TO DB : ' || l_update_sql );
1804
1805 --
1806 -- Done for all command
1807 --
1808 IF l_col_count = 1 THEN
1809 EXECUTE IMMEDIATE
1810 l_update_sql
1811 USING p_row_count, p_col1_data, p_prim_keys;
1812 ELSIF l_col_count = 2 THEN
1813 EXECUTE IMMEDIATE
1814 l_update_sql
1815 USING p_row_count, p_col1_data, p_col2_data, p_prim_keys;
1816 ELSIF l_col_count = 3 THEN
1817 EXECUTE IMMEDIATE
1818 l_update_sql
1819 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_prim_keys;
1820 ELSIF l_col_count = 4 THEN
1821 EXECUTE IMMEDIATE
1822 l_update_sql
1823 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1824 p_prim_keys;
1825 ELSIF l_col_count = 5 THEN
1826 EXECUTE IMMEDIATE
1827 l_update_sql
1828 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1829 p_col5_data, p_prim_keys;
1830 ELSIF l_col_count = 6 THEN
1831 EXECUTE IMMEDIATE
1832 l_update_sql
1833 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1834 p_col5_data, p_col6_data, p_prim_keys;
1835 ELSIF l_col_count = 7 THEN
1839 p_col5_data, p_col6_data, p_col7_data, p_prim_keys;
1836 EXECUTE IMMEDIATE
1837 l_update_sql
1838 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1840 ELSIF l_col_count = 8 THEN
1841 EXECUTE IMMEDIATE
1842 l_update_sql
1843 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1844 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_prim_keys;
1845 ELSIF l_col_count = 9 THEN
1846 EXECUTE IMMEDIATE
1847 l_update_sql
1848 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1849 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1850 p_prim_keys;
1851 ELSIF l_col_count = 10 THEN
1852 EXECUTE IMMEDIATE
1853 l_update_sql
1854 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1855 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1856 p_col10_data, p_prim_keys;
1857 ELSIF l_col_count = 11 THEN
1858 EXECUTE IMMEDIATE
1859 l_update_sql
1860 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1861 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1862 p_col10_data, p_col11_data, p_prim_keys;
1863 ELSIF l_col_count = 12 THEN
1864 EXECUTE IMMEDIATE
1865 l_update_sql
1866 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1867 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1868 p_col10_data, p_col11_data, p_col12_data, p_prim_keys;
1869 ELSIF l_col_count = 13 THEN
1870 EXECUTE IMMEDIATE
1871 l_update_sql
1872 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1873 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1874 p_col10_data, p_col11_data, p_col12_data, p_col13_data,p_prim_keys;
1875 ELSIF l_col_count = 14 THEN
1876 EXECUTE IMMEDIATE
1877 l_update_sql
1878 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1879 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1880 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1881 p_prim_keys;
1882 ELSIF l_col_count = 15 THEN
1883 EXECUTE IMMEDIATE
1884 l_update_sql
1885 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1886 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1887 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1888 p_col15_data, p_prim_keys;
1889 ELSIF l_col_count = 16 THEN
1890 EXECUTE IMMEDIATE
1891 l_update_sql
1892 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1893 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1894 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1895 p_col15_data, p_col16_data, p_prim_keys;
1896 ELSIF l_col_count = 17 THEN
1897 EXECUTE IMMEDIATE
1898 l_update_sql
1899 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1900 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1901 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1902 p_col15_data, p_col16_data, p_col17_data, p_prim_keys;
1903 ELSIF l_col_count = 18 THEN
1904 EXECUTE IMMEDIATE
1905 l_update_sql
1906 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1907 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1908 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1909 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_prim_keys;
1910 ELSIF l_col_count = 19 THEN
1911 EXECUTE IMMEDIATE
1912 l_update_sql
1913 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1914 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1915 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1916 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
1917 p_prim_keys;
1918 ELSIF l_col_count = 20 THEN
1919 EXECUTE IMMEDIATE
1920 l_update_sql
1921 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1922 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1923 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1924 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
1925 p_col20_data, p_prim_keys;
1926 ELSIF l_col_count = 21 THEN
1927 EXECUTE IMMEDIATE
1928 l_update_sql
1929 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1930 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1931 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1932 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
1933 p_col20_data, p_col21_data, p_prim_keys;
1934 ELSIF l_col_count = 22 THEN
1935 EXECUTE IMMEDIATE
1936 l_update_sql
1937 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1938 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1939 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1940 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
1944 l_update_sql
1941 p_col20_data, p_col21_data, p_col22_data, p_prim_keys;
1942 ELSIF l_col_count = 23 THEN
1943 EXECUTE IMMEDIATE
1945 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1946 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1947 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1948 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
1949 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_prim_keys;
1950 ELSIF l_col_count = 24 THEN
1951 EXECUTE IMMEDIATE
1952 l_update_sql
1953 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1954 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1955 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1956 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
1957 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
1958 p_prim_keys;
1959 ELSIF l_col_count = 25 THEN
1960 EXECUTE IMMEDIATE
1961 l_update_sql
1962 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1963 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1964 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1965 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
1966 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
1967 p_col25_data, p_prim_keys;
1968 ELSIF l_col_count = 26 THEN
1969 EXECUTE IMMEDIATE
1970 l_update_sql
1971 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1972 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1973 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1974 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
1975 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
1976 p_col25_data, p_col26_data, p_prim_keys;
1977 ELSIF l_col_count = 27 THEN
1978 EXECUTE IMMEDIATE
1979 l_update_sql
1980 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1981 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1982 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1983 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
1984 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
1985 p_col25_data, p_col26_data, p_col27_data, p_prim_keys;
1986 ELSIF l_col_count = 28 THEN
1987 EXECUTE IMMEDIATE
1988 l_update_sql
1989 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1990 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
1991 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
1992 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
1993 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
1994 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_prim_keys;
1995 ELSIF l_col_count = 29 THEN
1996 EXECUTE IMMEDIATE
1997 l_update_sql
1998 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
1999 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
2000 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
2001 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
2002 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
2003 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_col29_data,
2004 p_prim_keys;
2005 ELSIF l_col_count = 30 THEN
2006 EXECUTE IMMEDIATE
2007 l_update_sql
2008 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
2009 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
2010 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
2011 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
2012 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
2013 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_col29_data,
2014 p_col30_data, p_prim_keys;
2015 ELSIF l_col_count = 31 THEN
2016 EXECUTE IMMEDIATE
2017 l_update_sql
2018 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
2019 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
2020 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
2021 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
2022 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
2023 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_col29_data,
2024 p_col30_data, p_col31_data, p_prim_keys;
2025 ELSIF l_col_count = 32 THEN
2026 EXECUTE IMMEDIATE
2027 l_update_sql
2028 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
2029 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
2030 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
2031 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
2032 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
2033 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_col29_data,
2034 p_col30_data, p_col31_data, p_col32_data, p_prim_keys;
2038 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
2035 ELSIF l_col_count = 33 THEN
2036 EXECUTE IMMEDIATE
2037 l_update_sql
2039 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
2040 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
2041 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
2042 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
2043 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_col29_data,
2044 p_col30_data, p_col31_data, p_col32_data, p_col33_data, p_prim_keys;
2045 ELSIF l_col_count = 34 THEN
2046 EXECUTE IMMEDIATE
2047 l_update_sql
2048 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
2049 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
2050 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
2051 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
2052 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
2053 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_col29_data,
2054 p_col30_data, p_col31_data, p_col32_data, p_col33_data, p_col34_data,
2055 p_prim_keys;
2056 ELSIF l_col_count = 35 THEN
2057 EXECUTE IMMEDIATE
2058 l_update_sql
2059 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
2060 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
2061 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
2062 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
2063 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
2064 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_col29_data,
2065 p_col30_data, p_col31_data, p_col32_data, p_col33_data, p_col34_data,
2066 p_col35_data, p_prim_keys;
2067 ELSIF l_col_count = 36 THEN
2068 EXECUTE IMMEDIATE
2069 l_update_sql
2070 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
2071 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
2072 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
2073 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
2074 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
2075 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_col29_data,
2076 p_col30_data, p_col31_data, p_col32_data, p_col33_data, p_col34_data,
2077 p_col35_data, p_col36_data, p_prim_keys;
2078 ELSIF l_col_count = 37 THEN
2079 EXECUTE IMMEDIATE
2080 l_update_sql
2081 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
2082 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
2083 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
2084 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
2085 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
2086 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_col29_data,
2087 p_col30_data, p_col31_data, p_col32_data, p_col33_data, p_col34_data,
2088 p_col35_data, p_col36_data, p_col37_data, p_prim_keys;
2089 ELSIF l_col_count = 38 THEN
2090 EXECUTE IMMEDIATE
2091 l_update_sql
2092 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
2093 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
2094 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
2095 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
2096 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
2097 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_col29_data,
2098 p_col30_data, p_col31_data, p_col32_data, p_col33_data, p_col34_data,
2099 p_col35_data, p_col36_data, p_col37_data, p_col38_data, p_prim_keys;
2100 ELSIF l_col_count = 39 THEN
2101 EXECUTE IMMEDIATE
2102 l_update_sql
2103 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
2104 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
2105 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
2106 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
2107 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
2108 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_col29_data,
2109 p_col30_data, p_col31_data, p_col32_data, p_col33_data, p_col34_data,
2110 p_col35_data, p_col36_data, p_col37_data, p_col38_data, p_col39_data,
2111 p_prim_keys;
2112 ELSIF l_col_count = 40 THEN
2113 EXECUTE IMMEDIATE
2114 l_update_sql
2115 USING p_row_count, p_col1_data, p_col2_data, p_col3_data, p_col4_data,
2116 p_col5_data, p_col6_data, p_col7_data, p_col8_data, p_col9_data,
2117 p_col10_data, p_col11_data, p_col12_data, p_col13_data, p_col14_data,
2118 p_col15_data, p_col16_data, p_col17_data, p_col18_data, p_col19_data,
2119 p_col20_data, p_col21_data, p_col22_data, p_col23_data, p_col24_data,
2120 p_col25_data, p_col26_data, p_col27_data, p_col28_data, p_col29_data,
2121 p_col30_data, p_col31_data, p_col32_data, p_col33_data, p_col34_data,
2122 p_col35_data, p_col36_data, p_col37_data, p_col38_data, p_col39_data,
2123 p_col40_data, p_prim_keys;
2124 END IF;
2125
2126 -- Standard call to get message count and if count is 1, get message info.
2127 FND_MSG_PUB.Count_And_Get (
2128 p_count => x_msg_count,
2129 p_data => x_msg_data,
2130 p_encoded => FND_API.G_FALSE
2131 );
2132
2133 EXCEPTION
2134 WHEN OTHERS THEN
2135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2136 cn_message_pkg.set_error(l_api_name,'Unexpected Error.');
2137 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2138 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
2139 END IF;
2140 FND_MSG_PUB.Count_And_Get (
2141 p_count => x_msg_count,
2142 p_data => x_msg_data
2143 );
2144 END Update_DB;
2145
2146 END CN_IMPORT_CLIENT_PVT;