[Home] [Help]
PACKAGE BODY: APPS.BIM_RESPONSE_IMPORT_PUB
Source
1 PACKAGE BODY BIM_Response_IMPORT_PUB AS
2 /* $Header: bimpmrib.pls 120.1 2005/06/14 15:28:06 appldev $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='BIM_Response_IMPORT_PUB';
5 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
6 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
7
8 TYPE local_resp_grade_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
9 TYPE local_resp_invalid_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
10
11 -- ===============================================================
12 -- Start of Comments
13 -- Package name
14 -- BIM_Response_IMPORT
15 -- Purpose
16 --
17 -- History
18 --
19 -- NOTE
20 --
21 -- End of Comments
22 -- ===============================================================
23
24
25 PROCEDURE VALIDATE_RESPONSES(
26 p_source_code IN VARCHAR2 ,
27 p_response_country IN VARCHAR2,
28 p_response_source IN VARCHAR2,
29 p_response_grade_table IN response_grade_table_type,
30 p_response_invalid_table IN response_invalid_table_type,
31 p_response_creation_date IN DATE,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2
35 )
36
37 IS
38
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_RESPONSES';
41 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
42 l_source_code Varchar2(30);
43 l_response_country Varchar2(30);
44 l_response_source Varchar2(30);
45 l_response_grade_table response_grade_table_type;
46 l_response_invalid_table response_invalid_table_type;
47 l_response_creation_date Date;
48 l_local_resp_grade_table local_resp_grade_table;
49 l_local_resp_invalid_table local_resp_invalid_table;
50 l_grade_lookup Varchar2(60) :='AMS_RESP_GRADE';
51 l_reason_lookup Varchar2(60) :='AMS_RESP_REJECT_REASON';
52 l_source_code_count Number := 0;
53 l_country_code_count Number;
54 l_resp_grade_count Number;
55 l_resp_reason_count Number;
56 k Varchar2(30);
57 l Varchar2(30);
58 l_dummy Varchar2(30);
59 l_resp_reason_count Number;
60 i Number := 1;
61 l_grade_flag Varchar2(30) := 'F';
62 l_reason_flag Varchar2(30) := 'F';
63 j Number := 1;
64 n Number := 1;
65
66
67 CURSOR c_grade_lookup IS
68 SELECT lookup_code
69 FROM AMS_LOOKUPS
70 WHERE lookup_type = l_grade_lookup;
71
72 CURSOR c_reason_lookup IS
73 SELECT lookup_code
74 FROM AMS_LOOKUPS
75 WHERE lookup_type = l_reason_lookup;
76
77 CURSOR c_grade(l_cur_lookup_code Varchar2) IS
78 SELECT lookup_code
79 FROM AMS_LOOKUPS
80 WHERE lookup_type = l_grade_lookup
81 AND lookup_code = l_cur_lookup_code;
82
83 CURSOR c_reason(l_cur_res_code Varchar2) IS
84 SELECT lookup_code
85 FROM AMS_LOOKUPS
86 WHERE lookup_type = l_reason_lookup
87 AND lookup_code = l_cur_res_code;
88
89 CURSOR c_source_code(l_cur_source_code Varchar2) IS
90 SELECT source_code
91 FROM AMS_SOURCE_CODES
92 WHERE source_code = l_cur_source_code ;
93
94 CURSOR c_country_code(l_cur_country_code Varchar2) IS
95 SELECT country_code
96 FROM JTF_LOC_HIERARCHIES_B
97 WHERE country_code = l_cur_country_code ;
98
99 CURSOR c_duplicate(l_source_code Varchar2 ,l_response_country Varchar2,l_response_creation_date Date,l_response_source Varchar2) IS
100 SELECT interface_header_id
101 FROM BIM_R_RESP_INT_HEADER
102 WHERE source_code = l_source_code
103 AND country = l_response_country
104 AND response_create_date = l_response_creation_date
105 AND response_source = l_response_source;
106
107 BEGIN
108
109 SAVEPOINT VALIDATE_RESPONSES;
110
111 --dbms_output.put_line('inside VALIDATE_RESPONSES');
112 l_source_code := p_source_code;
113 l_response_country := p_response_country;
114 l_response_source := p_response_source;
115 l_response_creation_date := p_response_creation_date;
116 l_response_grade_table := p_response_grade_table ;
117 l_response_invalid_table := p_response_invalid_table;
118
119 --dbms_output.put_line(' before source code validation'||l_source_code);
120
121 --Validation of source code passed
122
123 OPEN c_source_code(l_source_code);
124 FETCH c_source_code INTO l_dummy;
125 IF c_source_code%NOTFOUND THEN
126 CLOSE c_source_code;
127 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
128 -- FND_MESSAGE.set_name('AMS', 'AMS_INVALID_SOURCE_CODE');
129 FND_MESSAGE.set_name('BIM', 'BIM_INVALID_SOURCE_CODE');
130 FND_MSG_PUB.add;
131 END IF;
132 RAISE FND_API.g_exc_error;
133 END IF;
134 CLOSE c_source_code;
135
136 -- dbms_output.put_line('source code validated');
137
138 --Validation of response_country
139
140
141 OPEN c_country_code(l_response_country);
142 FETCH c_country_code INTO l_dummy;
143 IF c_country_code%NOTFOUND THEN
144 CLOSE c_country_code;
145 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
146 FND_MESSAGE.set_name('BIM', 'BIM_INVALID_COUNTRY_CODE');
147 FND_MSG_PUB.add;
148 END IF;
149 RAISE FND_API.g_exc_error;
150 END IF;
151 CLOSE c_country_code;
152
153 --dbms_output.put_line('country code validated');
154
155 --Validating duplicate entries
156
157 OPEN c_duplicate(l_source_code,l_response_country,l_response_creation_date,l_response_source);
158 FETCH c_duplicate INTO l_dummy;
159 IF c_duplicate%FOUND THEN
160 CLOSE c_duplicate;
161 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
162 FND_MESSAGE.set_name('BIM', 'BIM_RESP_DUPLICATE_RECORD');
163 FND_MSG_PUB.add;
164 END IF;
165 RAISE FND_API.g_exc_error;
166 END IF;
167 CLOSE c_duplicate;
168
169 --Cursor to fetch the lookup codes defined in Grade look up into local table
170
171 OPEN c_grade_lookup;
172 LOOP
173 FETCH c_grade_lookup INTO l_local_resp_grade_table(i);
174 EXIT WHEN c_grade_lookup%NOTFOUND;
175 --dbms_output.put_line('local grades'||l_local_resp_grade_table(i));
176 i := i + 1;
177 END LOOP;
178 CLOSE c_grade_lookup;
179
180 IF (l_response_grade_table.COUNT >0) THEN
181 FOR i IN 1.. l_response_grade_table.COUNT
182 LOOP
183
184 IF l_response_grade_table(i).response_grade IS NULL
185 THEN
186 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
187 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_GRADE');
188 FND_MSG_PUB.add;
189 END IF;
190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191 END IF;
192 --dbms_output.put_line('l_local_resp_grade_table.COUNT'|| l_local_resp_grade_table.COUNT);
193
194 FOR j IN 1.. l_local_resp_grade_table.COUNT
195 loop
196 --dbms_output.put_line('loop 1'|| l_response_grade_table(i).response_grade );
197 --dbms_output.put_line('loop2'|| l_local_resp_grade_table(j));
198 if ( l_response_grade_table(i).response_grade = l_local_resp_grade_table(j)) then
199 l_grade_flag := 'T';
200 --dbms_output.put_line('grade passed1'|| l_grade_flag);
201 end if;
202 end loop;
203 --dbms_output.put_line('grade passed'|| l_grade_flag);
204 IF (l_grade_flag<>'T') THEN
205 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
206 FND_MESSAGE.set_name('BIM', 'BIM_INVALID_GRADE');
207 FND_MSG_PUB.add;
208 END IF;
209 RAISE FND_API.g_exc_error;
210 END IF;
211
212 --dbms_output.put_line('response Grade is '||l_response_grade_table(i).response_grade);
213
214 END LOOP;
215
216 --dbms_output.put_line('grade validated'|| l_resp_grade_count);
217 END IF;
218 --dbms_output.put_line('l_response_invalid_table.COUNT'|| l_response_invalid_table.COUNT);
219
220 --Cursor to fetch the lookup codes defined in Reason look up into local table
221
222 OPEN c_reason_lookup;
223 LOOP
224 FETCH c_reason_lookup INTO l_local_resp_invalid_table(n);
225 EXIT WHEN c_reason_lookup%NOTFOUND;
226 --dbms_output.put_line('local grades'||l_local_resp_invalid_table(n));
227 n := n + 1;
228 END LOOP;
229 CLOSE c_reason_lookup;
230
231 IF(l_response_invalid_table.COUNT>0) THEN
232 j := 1;
233 FOR j IN 1..l_response_invalid_table.COUNT
234 LOOP
235 --dbms_output.put_line('response reason is '||l_response_invalid_table(j).invalid_reason);
236
237 IF l_response_invalid_table(j).invalid_reason IS NULL
238 THEN
239 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
240 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_REASON');
241 FND_MSG_PUB.add;
242 END IF;
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 END IF;
245
246 -- dbms_output.put_line('l_local_resp_invalid_table.COUNT'|| l_local_resp_invalid_table.COUNT);
247
248 FOR b IN 1.. l_local_resp_invalid_table.COUNT
249 loop
250 --dbms_output.put_line('loop 1'|| l_response_invalid_table(j).invalid_reason );
251 --dbms_output.put_line('loop2'|| l_local_resp_invalid_table(b));
252 if ( l_response_invalid_table(j).invalid_reason = l_local_resp_invalid_table(b)) then
253 l_reason_flag := 'T';
254 --dbms_output.put_line('reason passed1'|| l_reason_flag);
255 end if;
256 end loop;
257 --dbms_output.put_line('reason passed'|| l_reason_flag);
258 IF (l_reason_flag<>'T') THEN
259 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
260 FND_MESSAGE.set_name('BIM', 'BIM_INVALID_REJECT_REASON');
261 FND_MSG_PUB.add;
262 END IF;
263 RAISE FND_API.g_exc_error;
264 END IF;
265
266 END LOOP;
267
268 -- dbms_output.put_line('reason validated');
269 END IF;
270 EXCEPTION
271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
272 ROLLBACK TO VALIDATE_RESPONSES;
273 --dbms_output.put_line('in un excepted exception block');
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
275 -- Standard call to get message count and if count=1, get the message
276 FND_MSG_PUB.Count_And_Get (
277 p_encoded => FND_API.G_FALSE,
278 p_count => x_msg_count,
279 p_data => x_msg_data
280 );
281
282 WHEN FND_API.g_exc_error THEN
283 --dbms_output.put_line('in exception block');
284 --dbms_output.put_line('in exception block');
285 x_return_status := FND_API.g_ret_sts_error;
286 FND_MSG_PUB.count_and_get(
287 p_encoded => FND_API.g_false,
288 p_count => x_msg_count,
289 p_data => x_msg_data
290 );
291
292 WHEN OTHERS THEN
293 ROLLBACK TO VALIDATE_RESPONSES;
294 -- dbms_output.put_line('in others exception block');
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
297 THEN
298 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
299 END IF;
300 -- Standard call to get message count and if count=1, get the message
301 FND_MSG_PUB.Count_And_Get (
302 p_encoded => FND_API.G_FALSE,
303 p_count => x_msg_count,
304 p_data => x_msg_data
305 );
306
307 END VALIDATE_RESPONSES;
308
309 -- ==============================================================================
310 -- Start of Comments
311 -- ==============================================================================
312 -- API Name
313 -- Import_Responses
314 -- Type
315 -- Private
316 -- Pre-Req
317 --
318 -- Parameters
319 --
320 -- IN
321 -- p_api_version_number IN NUMBER Optional Default = 1.0
322 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
323 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
324 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
325 -- p_interface_header_id IN NUMBER
326 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
327 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
328 -- p_interface_header_id IN NUMBER
329 -- p_response_hdr_rec IN response_hdr_rec_type,
330 -- p_response_grade_table IN response_grade_table_type,
331 -- p_response_invalid_table IN response_invalid_table_type,
332
333
334
335 -- OUT
336 -- x_return_status OUT NOCOPYVARCHAR2
337 -- x_msg_count OUT NOCOPYNUMBER
338 -- x_msg_data OUT NOCOPYVARCHAR2
339
340 -- End of Comments
341 -- ==============================================================================
342 --
343
344 PROCEDURE IMPORT_RESPONSES(
345 p_api_version_number IN NUMBER := 1.0,
346 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
347 p_commit IN VARCHAR2 := FND_API.G_FALSE,
348 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
349 p_response_hdr_rec IN response_hdr_rec_type,
350 p_response_grade_table IN response_grade_table_type,
351 p_response_invalid_table IN response_invalid_table_type,
352 x_return_status OUT NOCOPY VARCHAR2,
353 x_msg_count OUT NOCOPY NUMBER,
354 x_msg_data OUT NOCOPY VARCHAR2)
355
356 IS
357
358 l_api_version CONSTANT NUMBER := 1.0;
359 l_api_name CONSTANT VARCHAR2(30) := 'IMPORT_RESPONSES';
360 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
361 l_source_code Varchar2(30);
362 l_response_country Varchar2(30);
363 l_region_code Varchar2(30);
364 l_response_source Varchar2(30);
365 l_response_grade_table response_grade_table_type;
366 l_response_invalid_table response_invalid_table_type;
367 l_response_creation_date Date;
368 l_local_resp_hdr_rec response_hdr_rec_type;
369 l_local_resp_grade_table local_resp_grade_table;
370 l_local_resp_invalid_table local_resp_invalid_table;
371 l_total_grades Number;
372 l_total_reasons Number;
373 l_landing_pad_hits Number;
374 l_survey_completed Number;
378 l_grade_id Number;
375 l_interface_header_id Number;
376 l_interface_grade_id Number;
377 l_interface_reason_id Number;
379 l_reason_id Number;
380 l_source_code_id Number;
381 l_object_id Number;
382 i Number;
383 j Number;
384 l_return_status VARCHAR2(1);
385 l_process_flag VARCHAR2(1) := 'N';
386 l_msg_data VARCHAR2(200);
387 l_obj_type VARCHAR2(30);
388
389 CURSOR c_header_id IS
390 SELECT bim_r_resp_int_header_s.NEXTVAL
391 FROM dual;
392
393 CURSOR c_grade_id IS
394 SELECT bim_r_resp_int_grades_s.NEXTVAL
395 FROM dual;
396
397 CURSOR c_reason_id IS
398 SELECT bim_r_resp_int_reason_s.NEXTVAL
399 FROM dual;
400
401 CURSOR c_source_code_ids (c_source_code IN Varchar2) IS
402 SELECT source_code_id ,source_code_for_id ,arc_source_code_for
403 FROM AMS_SOURCE_CODES
404 WHERE source_code = c_source_code;
405
406 CURSOR c_region_code (c_country_code IN Varchar2) IS
407 SELECT area2_code
408 FROM JTF_LOC_HIERARCHIES_B
409 WHERE country_code = c_country_code;
410
411 BEGIN
412
413 SAVEPOINT IMPORT_RESPONSES;
414
415 --dbms_output.put_line('Inside Import responses');
416 l_local_resp_hdr_rec := p_response_hdr_rec;
417 l_response_grade_table := p_response_grade_table ;
418 l_response_invalid_table := p_response_invalid_table;
419 l_source_code := l_local_resp_hdr_rec.source_code;
420 l_response_country := l_local_resp_hdr_rec.response_country;
421 l_response_source := l_local_resp_hdr_rec.response_source;
422 l_response_creation_date := l_local_resp_hdr_rec.response_create_date;
423
424 IF l_source_code IS NULL
425 THEN
426 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
427 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_SOURCE_CODE');
428 FND_MSG_PUB.add;
429 END IF;
430 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
431 END IF;
432
433 IF l_response_country IS NULL
434 THEN
435 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
436 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_COUNTRY_CODE');
437 FND_MSG_PUB.add;
438 END IF;
439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
440 END IF;
441
442 IF l_response_creation_date IS NULL
443 THEN
444 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
445 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_CREATE_DATE');
446 FND_MSG_PUB.add;
447 END IF;
448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
449 END IF;
450
451 IF l_response_source IS NULL
452 THEN
453 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
454 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_RESPONSE_SOURCE');
455 FND_MSG_PUB.add;
456 END IF;
457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
458 END IF;
459
460 -- dbms_output.put_line('Before validate');
461 VALIDATE_RESPONSES(
462 p_source_code => l_source_code,
463 p_response_country => l_response_country,
464 p_response_source => l_response_source,
465 p_response_creation_date => l_response_creation_date,
466 p_response_grade_table => l_response_grade_table,
467 p_response_invalid_table => l_response_invalid_table,
468 x_return_status => l_return_status,
469 x_msg_count => x_msg_count,
470 x_msg_data => x_msg_data
471 );
472
473 -- dbms_output.put_line('after validate');
474 IF l_return_status = FND_API.g_ret_sts_error THEN
475 RAISE FND_API.G_EXC_ERROR;
476 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
477 RAISE FND_API.g_exc_unexpected_error;
478 END IF;
479 -- dbms_output.put_line('validation ends..back to import');
480
481 -- Validation Ends
482
483 l_landing_pad_hits := l_local_resp_hdr_rec.landing_pad_hits;
484 l_survey_completed := l_local_resp_hdr_rec.survey_completed;
485
486 OPEN c_header_id;
487 FETCH c_header_id INTO l_interface_header_id;
488 CLOSE c_header_id;
489
490 OPEN c_source_code_ids(l_source_code);
491 FETCH c_source_code_ids INTO l_source_code_id , l_object_id ,l_obj_type;
492 CLOSE c_source_code_ids;
493
494 OPEN c_region_code(l_response_country);
495 FETCH c_region_code INTO l_region_code;
496 CLOSE c_region_code;
497
498
499 -- dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER '||l_interface_header_id );
500 -- dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER object_id '||l_object_id );
504 -- dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER region '||l_region_code );
501 -- dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER source_code'||l_source_code );
502 -- dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER source_code_id '||l_source_code_id );
503 -- dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER response_creation_date'||l_response_creation_date );
505 -- dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER landing pad hits'||l_landing_pad_hits );
506 -- dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER survey completed '||l_survey_completed );
507 -- dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER response source '||l_response_source );
508 -- dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER creation_date'||SYSDATE );
509 -- dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER created_by '||G_USER_ID );
510 -- dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER last_update_login '||G_LOGIN_ID );
511 --dbms_output.put_line('before inserting into BIM_R_RESP_INT_HEADER object_type '||l_obj_type );
512
513 IF l_object_id IS NULL
514 THEN
515 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
516 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_OBJECT_ID');
517 FND_MSG_PUB.add;
518 END IF;
519 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
520 END IF;
521
522 IF l_obj_type IS NULL
523 THEN
524 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
525 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_OBJECT_TYPE');
526 FND_MSG_PUB.add;
527 END IF;
528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
529 END IF;
530
531 IF l_region_code IS NULL
532 THEN
533 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
534 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_REGION_CODE');
535 FND_MSG_PUB.add;
536 END IF;
537 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
538 END IF;
539
540 IF l_source_code_id IS NULL
541 THEN
542 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
543 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_SOURCE_CODE_ID');
544 FND_MSG_PUB.add;
545 END IF;
546 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
547 END IF;
548
549 IF l_process_flag IS NULL
550 THEN
551 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
552 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_PROCESS_FLAG');
553 FND_MSG_PUB.add;
554 END IF;
555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
556 END IF;
557
558 IF l_interface_header_id IS NULL
559 THEN
560 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
561 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_HEADER_ID');
562 FND_MSG_PUB.add;
563 END IF;
564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
565 END IF;
566
567 INSERT INTO BIM_R_RESP_INT_HEADER
568 (
569 creation_date,
570 last_update_date,
571 created_by,
572 last_updated_by,
573 last_update_login,
574 interface_header_id,
575 object_id,
576 object_type,
577 country,
578 region,
579 source_code,
580 source_code_id,
581 response_create_date,
582 landing_pad_hits,
583 survey_completed,
584 process_flag,
585 response_source
586
587 ) VALUES
588
589 (
590 SYSDATE,
591 SYSDATE,
592 G_USER_ID,
593 G_USER_ID,
594 G_LOGIN_ID,
595 l_interface_header_id,
596 l_object_id,
597 l_obj_type,
598 l_response_country,
599 l_region_code,
600 l_source_code,
601 l_source_code_id,
602 l_response_creation_date,
603 l_landing_pad_hits,
604 l_survey_completed,
605 l_process_flag,
606 l_response_source
607
608 );
609
610 --dbms_output.put_line('after inserting into BIM_R_RESP_INT_HEADER '||l_interface_header_id );
611 --dbms_output.put_line('l_response_grade_table.COUNT '||l_response_grade_table.COUNT);
612 IF(l_response_grade_table.COUNT > 0) THEN
613 FOR i IN 1..l_response_grade_table.COUNT LOOP
614
615 OPEN c_grade_id;
616 FETCH c_grade_id INTO l_interface_grade_id;
617 CLOSE c_grade_id;
618 IF l_response_grade_table (i).response_grade_count IS NULL
619 THEN
620 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
621 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_GRADE_COUNT');
622 FND_MSG_PUB.add;
623 END IF;
624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
625 END IF;
626
627 IF l_interface_grade_id IS NULL
628 THEN
629 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
630 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_GRADE_ID');
631 FND_MSG_PUB.add;
632 END IF;
633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
634 END IF;
635
636 --dbms_output.put_line('before inserting into bim_r_resp_int_grades '||l_local_resp_grade_count_table(i) );
637 -- dbms_output.put_line('before inserting into bim_r_resp_int_grades interface header id '||l_interface_header_id );
638 -- dbms_output.put_line('before inserting into bim_r_resp_int_grades interface greade id '||l_interface_grade_id );
639 -- dbms_output.put_line('before inserting into bim_r_resp_int_grades response_grade'||l_local_resp_grade_table(i) );
640
641 INSERT INTO bim_r_resp_int_grades
642 (
643 creation_date,
644 last_update_date,
645 created_by,
646 last_updated_by,
647 last_update_login,
648 interface_grade_id,
649 interface_header_id,
650 response_grade,
651 response_grade_count
652
653
654 ) VALUES
655
656 (
657 SYSDATE,
658 SYSDATE,
659 G_USER_ID,
660 G_USER_ID,
661 G_LOGIN_ID,
662 l_interface_grade_id,
663 l_interface_header_id,
664 l_response_grade_table (i).response_grade,
665 l_response_grade_table (i).response_grade_count
666
667 );
668
669 END LOOP;
670 --dbms_output.put_line('after inserting into bim_r_resp_int_grades');
671 END IF;
672
673 IF (l_response_invalid_table.COUNT > 0) THEN
674 FOR j IN 1..l_response_invalid_table.COUNT LOOP
675
676 IF l_response_invalid_table (j).invalid_responses IS NULL
677 THEN
678 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
679 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_REASON_COUNT');
680 FND_MSG_PUB.add;
681 END IF;
682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
683 END IF;
684
685 OPEN c_reason_id;
686 FETCH c_reason_id INTO l_interface_reason_id;
687 CLOSE c_reason_id;
688
689 IF l_interface_reason_id IS NULL
690 THEN
691 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
692 FND_MESSAGE.set_name('BIM', 'BIM_RESP_NO_REASON_ID');
693 FND_MSG_PUB.add;
694 END IF;
695 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
696 END IF;
697
698 -- dbms_output.put_line('before inserting into bim_r_resp_int_reason '||l_interface_reason_id);
699 --dbms_output.put_line('before inserting into bim_r_resp_int_reason invalid reason'||l_local_resp_invalid_table(j) );
700 -- dbms_output.put_line('before inserting into bim_r_resp_int_reason invalid responses'||l_local_resp_inv_count_table(j) );
701
702 INSERT INTO bim_r_resp_int_reason
703 (
704 creation_date,
705 last_update_date,
706 created_by,
707 last_updated_by,
708 last_update_login,
709 interface_reason_id,
710 interface_header_id,
711 invalid_reason,
712 invalid_responses
713
714 ) VALUES
715
716 (
717 SYSDATE,
718 SYSDATE,
719 G_USER_ID,
720 G_USER_ID,
721 G_LOGIN_ID,
722 l_interface_reason_id,
723 l_interface_header_id,
724 l_response_invalid_table(j).invalid_reason ,
725 l_response_invalid_table(j).invalid_responses
726
727 );
728
729 END LOOP;
730
731 --dbms_output.put_line('end inserting into bim_r_resp_int_reason');
732 END IF;
733
734 -- Check for commit
735 IF FND_API.to_boolean(p_commit) THEN
736 COMMIT;
737 END IF;
738
739 EXCEPTION
740
741 WHEN FND_API.G_EXC_ERROR THEN
742 ROLLBACK TO IMPORT_RESPONSES;
743 x_return_status := FND_API.G_RET_STS_ERROR;
744 FND_MSG_PUB.count_and_get(
745 p_encoded => FND_API.g_false,
746 p_count => x_msg_count,
747 p_data => x_msg_data
748 );
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750 ROLLBACK TO IMPORT_RESPONSES;
751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
752 -- Standard call to get message count and if count=1, get the message
753 FND_MSG_PUB.Count_And_Get (
754 p_encoded => FND_API.G_FALSE,
755 p_count => x_msg_count,
756 p_data => x_msg_data
757 );
758 WHEN OTHERS THEN
759 ROLLBACK TO IMPORT_RESPONSES;
760 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
761 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
762 THEN
763 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
764 END IF;
765 -- Standard call to get message count and if count=1, get the message
766 FND_MSG_PUB.Count_And_Get (
767 p_encoded => FND_API.G_FALSE,
768 p_count => x_msg_count,
769 p_data => x_msg_data
770 );
771
772 END IMPORT_RESPONSES;
773
774 END BIM_Response_IMPORT_PUB;