[Home] [Help]
PACKAGE BODY: APPS.IGI_IMP_IAC_IMPORT_PKG
Source
1 PACKAGE BODY igi_imp_iac_import_pkg AS
2 -- $Header: igiimipb.pls 120.11.12000000.1 2007/08/01 16:21:21 npandya ship $
3
4 --===========================FND_LOG.START=====================================
5
6 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
7 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
9 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
11 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12 g_path VARCHAR2(100) := 'IGI.PLSQL.igiimipb.igi_imp_iac_import_pkg.';
13
14 --===========================FND_LOG.END=====================================
15 --
16 -- Check current module name
17 --
18 FUNCTION Is_IGI_Program (p_program_name VARCHAR2) RETURN BOOLEAN
19 IS
20 -- Check program name
21 CURSOR c_check_program IS
22 SELECT COUNT(*)
23 FROM fnd_concurrent_programs
24 WHERE concurrent_program_name = p_program_name
25 AND concurrent_program_id = FND_GLOBAL.Conc_Program_Id
26 AND application_id = FND_GLOBAL.Prog_Appl_Id;
27
28 l_count NUMBER;
29 l_message VARCHAR2(1000);
30
31 BEGIN
32 OPEN c_check_program;
33 FETCH c_check_program INTO l_count;
34 CLOSE c_check_program;
35
36 IF l_count = 1 THEN
37 RETURN TRUE;
38 ELSE
39 RETURN FALSE;
40 END IF;
41
42 EXCEPTION
43 WHEN OTHERS THEN
44 RETURN FALSE;
45
46 END Is_IGI_Program;
47
48 --
49 -- Spawn an instance of the SQL*Loader program to load a file
50 --
51 PROCEDURE Spawn_Loader ( p_file_name IN VARCHAR2 ) IS
52
53 l_message VARCHAR2(1000);
54 l_file_name VARCHAR2(1000);
55 l_request_id NUMBER;
56 l_phase VARCHAR2(100);
57 l_status VARCHAR2(100);
58 l_dev_phase VARCHAR2(100);
59 l_dev_status VARCHAR2(100);
60
61 e_request_submit_error EXCEPTION;
62 e_request_wait_error EXCEPTION;
63 e_loader_failure EXCEPTION;
64 l_path_name VARCHAR2(150) := g_path||'spawn_loader';
65
66 BEGIN
67 l_file_name := p_file_name;
68
69 l_request_id := FND_REQUEST.SUBMIT_REQUEST
70 (APPLICATION => 'IGI',
71 PROGRAM => 'IGIIMPID',
72 DESCRIPTION => 'Inflation Accounting: Load Data from Data File',
73 START_TIME => NULL,
74 SUB_REQUEST => FALSE,
75 ARGUMENT1 => l_file_name,
76 ARGUMENT2 => CHR(0),
77 ARGUMENT3 => NULL, ARGUMENT4 => NULL, ARGUMENT5 => NULL,
78 ARGUMENT6 => NULL, ARGUMENT7 => NULL, ARGUMENT8 => NULL,
79 ARGUMENT9 => NULL, ARGUMENT10 => NULL, ARGUMENT11 => NULL,
80 ARGUMENT12 => NULL, ARGUMENT13 => NULL, ARGUMENT14 => NULL,
81 ARGUMENT15 => NULL, ARGUMENT16 => NULL, ARGUMENT17 => NULL,
82 ARGUMENT18 => NULL, ARGUMENT19 => NULL, ARGUMENT20 => NULL,
83 ARGUMENT21 => NULL, ARGUMENT22 => NULL, ARGUMENT23 => NULL,
84 ARGUMENT24 => NULL, ARGUMENT25 => NULL, ARGUMENT26 => NULL,
85 ARGUMENT27 => NULL, ARGUMENT28 => NULL, ARGUMENT29 => NULL,
86 ARGUMENT30 => NULL, ARGUMENT31 => NULL, ARGUMENT32 => NULL,
87 ARGUMENT33 => NULL, ARGUMENT34 => NULL, ARGUMENT35 => NULL,
88 ARGUMENT36 => NULL, ARGUMENT37 => NULL, ARGUMENT38 => NULL,
89 ARGUMENT39 => NULL, ARGUMENT40 => NULL, ARGUMENT41 => NULL,
90 ARGUMENT42 => NULL, ARGUMENT43 => NULL, ARGUMENT44 => NULL,
91 ARGUMENT45 => NULL, ARGUMENT46 => NULL, ARGUMENT47 => NULL,
92 ARGUMENT48 => NULL, ARGUMENT49 => NULL, ARGUMENT50 => NULL,
93 ARGUMENT51 => NULL, ARGUMENT52 => NULL, ARGUMENT53 => NULL,
94 ARGUMENT54 => NULL, ARGUMENT55 => NULL, ARGUMENT56 => NULL,
95 ARGUMENT57 => NULL, ARGUMENT58 => NULL, ARGUMENT59 => NULL,
96 ARGUMENT60 => NULL, ARGUMENT61 => NULL, ARGUMENT62 => NULL,
97 ARGUMENT63 => NULL, ARGUMENT64 => NULL, ARGUMENT65 => NULL,
98 ARGUMENT66 => NULL, ARGUMENT67 => NULL, ARGUMENT68 => NULL,
99 ARGUMENT69 => NULL, ARGUMENT70 => NULL, ARGUMENT71 => NULL,
100 ARGUMENT72 => NULL, ARGUMENT73 => NULL, ARGUMENT74 => NULL,
101 ARGUMENT75 => NULL, ARGUMENT76 => NULL, ARGUMENT77 => NULL,
102 ARGUMENT78 => NULL, ARGUMENT79 => NULL, ARGUMENT80 => NULL,
103 ARGUMENT81 => NULL, ARGUMENT82 => NULL, ARGUMENT83 => NULL,
104 ARGUMENT84 => NULL, ARGUMENT85 => NULL, ARGUMENT86 => NULL,
105 ARGUMENT87 => NULL, ARGUMENT88 => NULL, ARGUMENT89 => NULL,
106 ARGUMENT90 => NULL, ARGUMENT91 => NULL, ARGUMENT92 => NULL,
107 ARGUMENT93 => NULL, ARGUMENT94 => NULL, ARGUMENT95 => NULL,
108 ARGUMENT96 => NULL, ARGUMENT97 => NULL, ARGUMENT98 => NULL,
109 ARGUMENT99 => NULL, ARGUMENT100 => NULL);
110
111 IF l_request_id = 0 THEN
112 RAISE e_request_submit_error;
113 ELSE
114 COMMIT;
115 END IF;
116
117 -- Wait for request completion
118 IF NOT FND_CONCURRENT.Wait_For_Request (l_request_id,
119 10, -- interval seconds
120 0, -- max wait seconds
121 l_phase,
122 l_status,
123 l_dev_phase,
124 l_dev_status,
125 l_message) THEN
126 RAISE e_request_wait_error;
127 END IF;
128
129 -- Check request completion status
130 IF l_dev_phase <> 'COMPLETE' OR
131 l_dev_status <> 'NORMAL' THEN
132 RAISE e_loader_failure;
133 END IF;
134
135 -- Update concurrent process details to loaded records
136 UPDATE igi_imp_iac_intermediate
137 SET request_id = l_request_id
138 ,program_id = FND_GLOBAL.Conc_Program_Id
139 ,program_application_id = FND_GLOBAL.Prog_Appl_Id
140 ,program_update_date = SYSDATE
141 WHERE errored_flag IS NULL;
142
143 COMMIT;
144
145 EXCEPTION
146
147 WHEN e_request_submit_error THEN
148 FND_MESSAGE.Retrieve(l_message);
149 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
150 p_full_path => l_path_name,
151 p_remove_from_stack => FALSE);
152 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
153 RAISE;
154
155 WHEN e_request_wait_error THEN
156 FND_MESSAGE.Retrieve(l_message);
157 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
158 p_full_path => l_path_name,
159 p_remove_from_stack => FALSE);
160 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
161 RAISE;
162
163 WHEN e_loader_failure THEN
164 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_NOT_NORM_COMPLETE');
165 FND_MESSAGE.Set_Token('FILE_NAME',l_file_name);
166 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
167 p_full_path => l_path_name,
168 p_remove_from_stack => FALSE);
169 l_message := FND_MESSAGE.Get;
170 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
171 RAISE;
172
173 WHEN OTHERS THEN
174 FND_MESSAGE.Retrieve(l_message);
175 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
176 p_full_path => l_path_name,
177 p_remove_from_stack => FALSE);
178 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
179 RAISE;
180
181 END Spawn_Loader;
182
183 --
184 -- Validate and Update intermediate records to interface
185 --
186 PROCEDURE Validate_Update_IMP_Data ( p_file_name IN VARCHAR2
187 , p_book_type_code IN VARCHAR2
188 , p_category_id IN NUMBER
189 ) IS
190
191 -- Count records to process
192 CURSOR c_count_recs IS
193 SELECT COUNT(*)
194 FROM igi_imp_iac_intermediate
195 WHERE errored_flag IS NULL;
196
197 -- Get records to process
198 CURSOR c_recs_to_process IS
199 SELECT asset_id
200 ,asset_number
201 ,book_type_code
202 ,category_id
203 ,cost_mhca
204 ,ytd_mhca
205 ,accum_deprn_mhca
206 ,reval_reserve_mhca
207 ,backlog_mhca
208 ,general_fund_mhca
209 ,operating_account_cost
210 ,operating_account_backlog
211 ,request_id
212 ,program_application_id
213 ,program_id
214 ,program_update_date
215 FROM igi_imp_iac_intermediate
216 WHERE errored_flag IS NULL;
217
218 -- Validate the asset
219 CURSOR c_validate_asset (cp_asset_id igi_imp_iac_interface.asset_id%TYPE,
220 cp_asset_number igi_imp_iac_interface.asset_number%TYPE,
221 cp_book_type_code igi_imp_iac_interface.book_type_code%TYPE,
222 cp_category_id igi_imp_iac_interface.category_id%TYPE) IS
223 SELECT COUNT(*)
224 FROM igi_imp_iac_interface
225 WHERE asset_id = cp_asset_id
226 AND asset_number = cp_asset_number
227 AND book_type_code = cp_book_type_code
228 AND category_id = cp_category_id;
229
230 --
231 -- Bug 2499880 Start(1)
232 --
233 -- Check transfer to IAC
234 CURSOR c_chk_transfer_to_iac
235 (cp_book_type_code igi_imp_iac_interface.book_type_code%TYPE,
236 cp_category_id igi_imp_iac_interface.category_id%TYPE) IS
237 SELECT transfer_status
238 FROM igi_imp_iac_interface_ctrl
239 WHERE book_type_code = cp_book_type_code
240 AND category_id = cp_category_id;
241
242 Cursor C_book_class( cp_book_type_code igi_imp_iac_interface.book_type_code%TYPE) is
243 Select book_class
244 from fa_booK_controls
245 where book_type_code = cp_book_type_code;
246
247 cursor c_deprn_flag(cp_book_type_code igi_imp_iac_interface.book_type_code%TYPE,
248 cp_asset_id igi_imp_iac_interface.asset_id%TYPE ) is
249 select depreciate_flag
250 from fa_books
251 where book_type_code =cp_book_type_code
252 and asset_id =cp_asset_id
253 and transaction_header_id_out is null;
254
255 -- Bug 2499880 End(1)
256 --
257
258 l_book_type_code igi_imp_iac_interface.book_type_code%TYPE;
259 l_category_id igi_imp_iac_interface.category_id%TYPE;
260
261 --
262 -- Bug 2499880 Start(2)
263 --
264 l_transfer_status igi_imp_iac_interface_ctrl.transfer_status%TYPE;
265 --
266 -- Bug 2499880 End(2)
267 --
268 l_deprn_flag fa_books.depreciate_flag%type;
269 l_book_class fa_booK_controls.book_class%type;
270
271 l_count NUMBER;
272 l_message VARCHAR2(1000);
273 l_file_name VARCHAR2(1000);
274 l_igiimpip BOOLEAN;
275 l_valid BOOLEAN;
276 l_any_err_recs BOOLEAN := FALSE;
277 l_path_name VARCHAR2(150) := g_path||'validate_update_imp_data';
278
279 BEGIN
280
281 l_file_name := p_file_name;
282 l_book_type_code := p_book_type_code;
283 l_category_id := p_category_id;
284 l_igiimpip := is_igi_program('IGIIMPIP');
285
286 -- Log count of records to process
287 OPEN c_count_recs;
288 FETCH c_count_recs INTO l_count;
289 CLOSE c_count_recs;
290 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_RECORDS_TO_PROCESS');
291 FND_MESSAGE.Set_Token('RECORD_COUNT',l_count);
292 FND_MESSAGE.Set_Token('FILE_NAME',l_file_name);
293 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,
294 p_full_path => l_path_name,
295 p_remove_from_stack => FALSE);
296 l_message := FND_MESSAGE.Get;
297 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
298
299 -- Process records
300 FOR cv_recs_to_process IN c_recs_to_process LOOP
301 l_valid := TRUE;
302
303 -- If IGIIMPIP, validate the record's book type code
304 IF l_igiimpip THEN
305 IF l_book_type_code <> cv_recs_to_process.book_type_code THEN
306 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_WRONG_BOOK');
307 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',cv_recs_to_process.book_type_code);
308 FND_MESSAGE.Set_Token('ASSET_ID',cv_recs_to_process.asset_id);
309 FND_MESSAGE.Set_Token('ASSET_NUMBER',cv_recs_to_process.asset_number);
310 FND_MESSAGE.Set_Token('P_BOOK_TYPE_CODE',l_book_type_code);
311 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,
312 p_full_path => l_path_name,
313 p_remove_from_stack => FALSE);
314 l_message := FND_MESSAGE.Get;
315 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
316
317 l_valid := FALSE;
318 END IF;
319 END IF;
320
321 -- If IGIIMPIP, validate the record's category id
322 IF l_valid AND l_igiimpip THEN
323 IF l_category_id <> cv_recs_to_process.category_id THEN
324 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_WRONG_CATEGORY_ID');
325 FND_MESSAGE.Set_Token('CATEGORY_ID',cv_recs_to_process.category_id);
326 FND_MESSAGE.Set_Token('ASSET_ID',cv_recs_to_process.asset_id);
327 FND_MESSAGE.Set_Token('ASSET_NUMBER',cv_recs_to_process.asset_number);
328 FND_MESSAGE.Set_Token('P_CATEGORY_ID',l_category_id);
329 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,
330 p_full_path => l_path_name,
331 p_remove_from_stack => FALSE);
332 l_message := FND_MESSAGE.Get;
333 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
334
335 l_valid := FALSE;
336 END IF;
337 END IF;
338
339 IF l_valid THEN
340 -- Validate the asset
341 OPEN c_validate_asset(cv_recs_to_process.asset_id,
342 cv_recs_to_process.asset_number,
343 cv_recs_to_process.book_type_code,
344 cv_recs_to_process.category_id);
345 FETCH c_validate_asset INTO l_count;
346 CLOSE c_validate_asset;
347
348 IF l_count <> 1 THEN
349 -- Log the invalid asset
350 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_INTFACE_ASSET_ERR');
351 FND_MESSAGE.Set_Token('ASSET_ID',cv_recs_to_process.asset_id);
352 FND_MESSAGE.Set_Token('ASSET_NUMBER',cv_recs_to_process.asset_number);
353 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,
354 p_full_path => l_path_name,
355 p_remove_from_stack => FALSE);
356 l_message := FND_MESSAGE.Get;
357 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
358
359 l_valid := FALSE;
360 END IF; -- asset count
361 END IF; -- valid record
362
363 --
364 -- Bug 2499880 Start(3)
365 --
366 IF l_valid THEN
367 -- Check transfer to IAC
368 OPEN c_chk_transfer_to_iac(cv_recs_to_process.book_type_code,
369 cv_recs_to_process.category_id);
370 FETCH c_chk_transfer_to_iac INTO l_transfer_status;
371 CLOSE c_chk_transfer_to_iac;
372
373 IF l_transfer_status = 'C' THEN
374 -- Log the transfer already completed message.
375 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_TRNSFRD_TO_IAC');
376 FND_MESSAGE.Set_Token('ASSET_ID',cv_recs_to_process.asset_id);
377 FND_MESSAGE.Set_Token('ASSET_NUMBER',cv_recs_to_process.asset_number);
378 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',cv_recs_to_process.book_type_code);
379 FND_MESSAGE.Set_Token('CATEGORY_ID',cv_recs_to_process.category_id);
380 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,
381 p_full_path => l_path_name,
382 p_remove_from_stack => FALSE);
383 l_message := FND_MESSAGE.Get;
384 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
385
386 l_valid := FALSE;
387 END IF; -- transfer status
388 END IF; -- valid record
389 --
390 -- Bug 2499880 End(3)
391 --
392
393 IF l_valid THEN
394
395 l_deprn_flag := 'YES';
396 --open c_booK_class(cv_recs_to_process.book_type_code);
397 --Fetch c_booK_class into l_booK_class;
398 --IF l_book_class = 'CORPORATE' THEN
399 open c_deprn_flag(cv_recs_to_process.book_type_code,cv_recs_to_process.asset_id);
400 fetch c_deprn_flag into l_deprn_flag;
401 close c_deprn_flag;
402 -- END IF;
403 -- Close c_book_class;
404
405 IF l_deprn_flag = 'YES' THEN
406 -- Update the asset data to interface
407 UPDATE igi_imp_iac_interface
408 SET cost_mhca = cv_recs_to_process.cost_mhca
409 ,ytd_mhca = cv_recs_to_process.ytd_mhca
410 ,accum_deprn_mhca = cv_recs_to_process.accum_deprn_mhca
411 ,reval_reserve_mhca = cv_recs_to_process.reval_reserve_mhca
412 ,backlog_mhca = cv_recs_to_process.backlog_mhca
413 ,general_fund_mhca = cv_recs_to_process.general_fund_mhca
414 ,operating_account_cost = (-1) * cv_recs_to_process.operating_account_cost
415 ,operating_account_backlog = (-1) * cv_recs_to_process.operating_account_backlog
416 ,import_file = l_file_name
417 ,import_date = SYSDATE
418 ,request_id = cv_recs_to_process.request_id
419 ,program_id = cv_recs_to_process.program_id
420 ,program_application_id = cv_recs_to_process.program_application_id
421 ,program_update_date = cv_recs_to_process.program_update_date
422 ,valid_flag = 'N'
423 WHERE asset_id = cv_recs_to_process.asset_id
424 AND asset_number = cv_recs_to_process.asset_number
425 AND book_type_code = cv_recs_to_process.book_type_code
426 AND category_id = cv_recs_to_process.category_id;
427 ELSE
428
429 UPDATE igi_imp_iac_interface
430 SET cost_mhca = cv_recs_to_process.cost_mhca
431 ,reval_reserve_mhca = cv_recs_to_process.reval_reserve_mhca
432 ,operating_account_cost = (-1) * cv_recs_to_process.operating_account_cost
433 ,import_file = l_file_name
434 ,import_date = SYSDATE
435 ,request_id = cv_recs_to_process.request_id
436 ,program_id = cv_recs_to_process.program_id
437 ,program_application_id = cv_recs_to_process.program_application_id
438 ,program_update_date = cv_recs_to_process.program_update_date
439 ,valid_flag = 'N'
440 WHERE asset_id = cv_recs_to_process.asset_id
441 AND asset_number = cv_recs_to_process.asset_number
442 AND book_type_code = cv_recs_to_process.book_type_code
443 AND category_id = cv_recs_to_process.category_id;
444
445
446
447 END IF;
448 -- Delete the successful record from intermediate
449 DELETE FROM igi_imp_iac_intermediate
450 WHERE asset_id = cv_recs_to_process.asset_id
451 AND asset_number = cv_recs_to_process.asset_number
452 AND book_type_code = cv_recs_to_process.book_type_code
453 AND category_id = cv_recs_to_process.category_id;
454
455 ELSE -- not a valid record
456
457 -- Update the error status to intermediate
458 UPDATE igi_imp_iac_intermediate
459 SET errored_flag = 'Y'
460 WHERE asset_id = cv_recs_to_process.asset_id
461 AND asset_number = cv_recs_to_process.asset_number
462 AND book_type_code = cv_recs_to_process.book_type_code
463 AND category_id = cv_recs_to_process.category_id;
464
465 l_any_err_recs := TRUE;
466 END IF; -- not valid rec
467
468 END LOOP; -- loop through records to process
469
470 IF l_any_err_recs THEN
471 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_FAILED_RECS');
472 FND_MESSAGE.Set_Token('FILE_NAME',l_file_name);
473 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
474 p_full_path => l_path_name,
475 p_remove_from_stack => FALSE);
476 l_message := FND_MESSAGE.Get;
477 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
478 END IF;
479
480 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_FILE_COMPLETE');
481 FND_MESSAGE.Set_Token('FILE_NAME',l_file_name);
482 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,
483 p_full_path => l_path_name,
484 p_remove_from_stack => FALSE);
485 l_message := FND_MESSAGE.Get;
486 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
487
488 EXCEPTION
489
490 WHEN OTHERS THEN
491 FND_MESSAGE.Retrieve(l_message);
492 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,
493 p_full_path => l_path_name,
494 p_remove_from_stack => FALSE);
495 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
496 RAISE;
497
498 END Validate_Update_IMP_Data;
499
500 --
501 -- Implementation Import Data Process
502 --
503 PROCEDURE Import_IMP_Data_Process ( errbuf OUT NOCOPY VARCHAR2
504 , retcode OUT NOCOPY NUMBER
505 , p_book_type_code IN VARCHAR2
506 , p_category_id IN NUMBER
507 , p_category_name IN VARCHAR2
508 ) IS
509
510 -- Cursor to validate book type code
511 CURSOR c_book_type_code (cp_book_type_code
512 igi_imp_iac_controls.book_type_code%TYPE) IS
513 SELECT request_status
514 FROM igi_imp_iac_controls
515 WHERE book_type_code = cp_book_type_code;
516
517 -- Cursor to validate category id
518 CURSOR c_category_id (cp_book_type_code
519 igi_imp_iac_interface_ctrl.book_type_code%TYPE,
520 cp_category_id
521 igi_imp_iac_interface_ctrl.category_id%TYPE) IS
522 SELECT COUNT(*)
523 FROM igi_imp_iac_interface_ctrl
524 WHERE book_type_code = cp_book_type_code
525 AND category_id = cp_category_id;
526
527 -- Cursor for count of files to process
528 CURSOR c_group_count (cp_book_type_code igi_imp_iac_interface.book_type_code%TYPE,
529 cp_category_id igi_imp_iac_interface.category_id%TYPE) IS
530 SELECT COUNT(DISTINCT group_id)
531 FROM igi_imp_iac_interface
532 WHERE book_type_code = cp_book_type_code
533 AND category_id = cp_category_id
534 AND group_id IS NOT NULL;
535
536 -- Cursor to build file names to import
537 CURSOR c_group_id (cp_book_type_code igi_imp_iac_interface.book_type_code%TYPE,
538 cp_category_id igi_imp_iac_interface.category_id%TYPE) IS
539 SELECT DISTINCT group_id
540 FROM igi_imp_iac_interface
541 WHERE book_type_code = cp_book_type_code
542 AND category_id = cp_category_id
543 AND group_id IS NOT NULL;
544
545 l_book_type_code igi_imp_iac_interface.book_type_code%TYPE;
546 -- l_new_book_type_code igi_imp_iac_interface.book_type_code%TYPE; -- Bug No. 2843747 (Tpradhan) - Coommented since no longer in use
547 l_category_id igi_imp_iac_interface.category_id%TYPE;
548 l_category_name VARCHAR2(350);
549
550 l_request_status igi_imp_iac_controls.request_status%TYPE;
551 l_message VARCHAR2(1000);
552 l_file_path VARCHAR2(1000);
553 l_file_name VARCHAR2(1000);
554 l_count NUMBER := 0;
555
556 e_iac_not_enabled EXCEPTION;
557 e_not_null_params EXCEPTION;
558 e_invalid_book EXCEPTION;
559 e_incomplete_preparation EXCEPTION;
560 e_invalid_category_id EXCEPTION;
561 e_no_files EXCEPTION;
562 l_path_name VARCHAR2(150) := g_path||'import_imp_data_process';
563 BEGIN
564 l_book_type_code := p_book_type_code;
565 l_category_id := p_category_id;
566 l_category_name := p_category_name;
567
568 -- Check if IAC is switched on
569 IF NOT igi_gen.is_req_installed('IAC') THEN
570 RAISE e_iac_not_enabled;
571 END IF;
572
573 -- Check for mandatory values
574 IF TRIM(l_book_type_code) IS NULL OR
575 l_category_id IS NULL OR
576 TRIM(l_category_name) IS NULL THEN
577 RAISE e_not_null_params;
578 END IF;
579
580 -- Validate book type code
581 OPEN c_book_type_code(l_book_type_code);
582 FETCH c_book_type_code INTO l_request_status;
583 IF c_book_type_code%NOTFOUND THEN
584 CLOSE c_book_type_code;
585 RAISE e_invalid_book;
586 END IF;
587 CLOSE c_book_type_code;
588
589 -- Check preparation status
590 IF UPPER(l_request_status) <> 'C' THEN
591 RAISE e_incomplete_preparation;
592 END IF;
593
594 -- Validate the category id
595 OPEN c_category_id(l_book_type_code,l_category_id);
596 FETCH c_category_id INTO l_count;
597 CLOSE c_category_id;
598 IF l_count = 0 THEN
599 RAISE e_invalid_category_id;
600 END IF;
601
602 -- Delete old records from the intermediate table
603 DELETE FROM igi_imp_iac_intermediate;
604 COMMIT;
605
606 -- Strip book type code blank spaces
607 -- l_new_book_type_code := strip_blank_spaces(l_book_type_code); -- Commented since check is performed using the function igi_imp_iac_export_pkg.trim_invalid_chars
608
609 -- Check count of files to process
610 OPEN c_group_count(l_book_type_code,l_category_id);
611 FETCH c_group_count INTO l_count;
612 CLOSE c_group_count;
613 IF l_count = 0 THEN
614 RAISE e_no_files;
615 END IF;
616
617 -- Get file location
618 FND_PROFILE.Get('IGI_IMP_IAC_FILE_LOCN',l_file_path);
619
620 -- Build file names to be processed
621 FOR cv_group_id IN c_group_id(l_book_type_code,l_category_id) LOOP
622 l_file_name := l_file_path||igi_imp_iac_export_pkg.trim_invalid_chars(l_book_type_code)|| '_' ||
623 igi_imp_iac_export_pkg.trim_invalid_chars(l_category_name)|| '_' ||
624 TO_CHAR(cv_group_id.group_id)|| '_' ||
625 'in.csv';
626
627 -- Write file name to log file
628 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_FILE_LOG');
629 FND_MESSAGE.Set_Token('FILE_NAME',l_file_name);
630 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,
631 p_full_path => l_path_name,
632 p_remove_from_stack => FALSE);
633 l_message := FND_MESSAGE.Get;
634 --FND_FILE.Put_Line(FND_FILE.Log,l_message);
635
636 -- Invoking SQL*Loader to upload the file to the intermediate table.
637 spawn_loader( l_file_name
638 );
639
640 -- Invoke the validate and update PL/SQL Program
641 Validate_Update_IMP_Data( l_file_name
642 , l_book_type_code
643 , l_category_id
644 );
645
646 END LOOP; -- Group Ids
647
648 COMMIT;
649
650 EXCEPTION
651 WHEN e_iac_not_enabled THEN
652 FND_MESSAGE.Set_Name('IGI','IGI_IAC_NOT_INSTALLED');
653 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
654 p_full_path => l_path_name,
655 p_remove_from_stack => FALSE);
656 retcode := 2;
657 errbuf := FND_MESSAGE.Get;
658
659 WHEN e_not_null_params THEN
660 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_MANDATORY_PARAMS');
661 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',l_book_type_code);
662 FND_MESSAGE.Set_Token('CATEGORY_ID',l_category_id);
663 FND_MESSAGE.Set_Token('CATEGORY_NAME',l_category_name);
664 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
665 p_full_path => l_path_name,
666 p_remove_from_stack => FALSE);
667 retcode := 2;
668 errbuf := FND_MESSAGE.Get;
669
670 WHEN e_invalid_book THEN
671 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_INVALID_BOOK');
672 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',l_book_type_code);
673 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
674 p_full_path => l_path_name,
675 p_remove_from_stack => FALSE);
676 retcode := 2;
677 errbuf := FND_MESSAGE.Get;
678
679 WHEN e_incomplete_preparation THEN
680 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_PREP_NOT_COMPLETE');
681 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',l_book_type_code);
682 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
683 p_full_path => l_path_name,
684 p_remove_from_stack => FALSE);
685 retcode := 2;
686 errbuf := FND_MESSAGE.Get;
687
688 WHEN e_invalid_category_id THEN
689 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_INVALID_CAT_ID');
690 FND_MESSAGE.Set_Token('CATEGORY_ID',l_category_id);
691 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',l_book_type_code);
692 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
693 p_full_path => l_path_name,
694 p_remove_from_stack => FALSE);
695 retcode := 2;
696 errbuf := FND_MESSAGE.Get;
697
698 WHEN e_no_files THEN
699 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_NO_FILES_TO_IMPORT');
700 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',l_book_type_code);
701 FND_MESSAGE.Set_Token('CATEGORY_ID',l_category_id);
702 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
703 p_full_path => l_path_name,
704 p_remove_from_stack => FALSE);
705 retcode := 1;
706 errbuf := FND_MESSAGE.Get;
707
708 WHEN OTHERS THEN
709 FND_MESSAGE.Retrieve(l_message);
710 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
711 p_full_path => l_path_name,
712 p_remove_from_stack => FALSE);
713 retcode := 2;
714 errbuf := l_message;
715
716 END Import_IMP_Data_Process;
717
718 END igi_imp_iac_import_pkg;