1 PACKAGE BODY igs_ps_generic_pvt AS
2 /* $Header: IGSPS90B.pls 120.2 2005/09/27 01:35:27 appldev noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'igs_ps_generic_pvt';
6
7 PROCEDURE psp_import (
8 p_api_version IN NUMBER,
9 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
10 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
11 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2,
15 p_calling_context IN VARCHAR2,
16 p_usec_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_tbl_type,
17 p_usec_gs_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_gs_tbl_type,
18 p_uso_tbl IN OUT NOCOPY igs_ps_generic_pub.uso_tbl_type,
19 p_unit_ref_tbl IN OUT NOCOPY igs_ps_generic_pub.unit_ref_tbl_type,
20 p_uso_ins_tbl IN OUT NOCOPY igs_ps_generic_pub.uso_ins_tbl_type,
21 p_usec_occurs_facility_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_occurs_facility_tbl_type,
22 p_usec_teach_resp_ovrd_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_teach_resp_ovrd_tbl_type,
23 p_usec_notes_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_notes_tbl_type,
24 p_usec_assmnt_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_assmnt_tbl_type,
25 p_usec_plus_hr_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_plus_hr_tbl_type,
26 p_usec_cat_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_cat_tbl_type,
27 p_usec_rule_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_rule_tbl_type,
28 p_usec_cross_group_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_cross_group_tbl_type,
29 p_usec_meet_with_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_meet_with_tbl_type,
30 p_usec_waitlist_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_waitlist_tbl_type,
31 p_usec_res_seat_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_res_seat_tbl_type,
32 p_usec_sp_fee_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_sp_fee_tbl_type,
33 p_usec_ret_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_ret_tbl_type,
34 p_usec_ret_dtl_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_ret_dtl_tbl_type,
35 p_usec_enr_dead_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_enr_dead_tbl_type,
36 p_usec_enr_dis_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_enr_dis_tbl_type,
37 p_usec_teach_resp_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_teach_resp_tbl_type,
38 p_usec_ass_item_grp_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_ass_item_grp_tbl_type,
39 p_usec_status OUT NOCOPY VARCHAR2,
40 p_usec_gs_status OUT NOCOPY VARCHAR2,
41 p_uso_status OUT NOCOPY VARCHAR2,
42 p_uso_ins_status OUT NOCOPY VARCHAR2,
43 p_uso_facility_status OUT NOCOPY VARCHAR2,
44 p_unit_ref_status OUT NOCOPY VARCHAR2,
45 p_usec_teach_resp_ovrd_status OUT NOCOPY VARCHAR2,
46 p_usec_notes_status OUT NOCOPY VARCHAR2,
47 p_usec_assmnt_status OUT NOCOPY VARCHAR2,
48 p_usec_plus_hr_status OUT NOCOPY VARCHAR2,
49 p_usec_cat_status OUT NOCOPY VARCHAR2,
50 p_usec_rule_status OUT NOCOPY VARCHAR2,
51 p_usec_cross_group_status OUT NOCOPY VARCHAR2,
52 p_usec_meet_with_status OUT NOCOPY VARCHAR2,
53 p_usec_waitlist_status OUT NOCOPY VARCHAR2,
54 p_usec_res_seat_status OUT NOCOPY VARCHAR2,
55 p_usec_sp_fee_status OUT NOCOPY VARCHAR2,
56 p_usec_ret_status OUT NOCOPY VARCHAR2,
57 p_usec_ret_dtl_status OUT NOCOPY VARCHAR2,
58 p_usec_enr_dead_status OUT NOCOPY VARCHAR2,
59 p_usec_enr_dis_status OUT NOCOPY VARCHAR2,
60 p_usec_teach_resp_status OUT NOCOPY VARCHAR2,
61 p_usec_ass_item_grp_status OUT NOCOPY VARCHAR2 ) AS
62 /***********************************************************************************************
63 Created By: Sanjeeb Rakshit
64 Date Created By: 25-May-2005
65 Purpose: This is a public API to import data from external system to OSS.
66 Known limitations,enhancements,remarks:
67
68 Change History
69
70 Who When What
71 sommukhe 27-SEP-2005 BUG #4632652.FND logging included.
72 ***********************************************************************************************/
73
74
75 l_api_name CONSTANT VARCHAR2(30) := 'psp_import';
76 l_api_version CONSTANT NUMBER := 1.0;
77 l_record_exists BOOLEAN := FALSE;
78 BEGIN
79
80 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
81 fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_generic_pvt.psp_import.start_logging_for',
82 'Data import from external Sysytem to OSS ');
83 END IF;
84
85 --Standard start of API savepoint
86 SAVEPOINT psp_import_PVT;
87
88 --Standard call to check for call compatibility
89 IF NOT FND_API.Compatible_API_Call(l_api_version ,
90 p_api_version ,
91 l_api_name ,
92 G_PKG_NAME) THEN
93 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
94 END IF;
95
96 --Initialize message list if p_init_msg_list is set to TRUE
97 IF FND_API.to_Boolean(p_init_msg_list) THEN
98 FND_MSG_PUB.initialize;
99 END IF;
100
101
102 --API body
103 IF p_calling_context NOT IN ('S','G') THEN
104 FND_MESSAGE.SET_NAME('IGS','IGS_PS_INVALID_VALUE_CONTEXT');
105 FND_MSG_PUB.ADD;
106 RAISE FND_API.G_EXC_ERROR;
107 END IF;
108
109 --Initialize API return status to success
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111
112
113 --API body
114 p_usec_status := 'S';
115 p_usec_gs_status := 'S';
116 p_uso_status := 'S';
117 p_uso_ins_status := 'S';
118 p_uso_facility_status := 'S';
119 p_unit_ref_status := 'S';
120 p_usec_teach_resp_ovrd_status := 'S';
121 p_usec_notes_status := 'S';
122 p_usec_assmnt_status := 'S';
123 p_usec_plus_hr_status := 'S';
124 p_usec_cat_status := 'S';
125 p_usec_rule_status := 'S';
126 p_usec_cross_group_status := 'S';
127 p_usec_meet_with_status := 'S';
128 p_usec_waitlist_status := 'S';
129 p_usec_res_seat_status := 'S';
130 p_usec_sp_fee_status := 'S';
131 p_usec_ret_status := 'S';
132 p_usec_ret_dtl_status := 'S';
133 p_usec_enr_dead_status := 'S';
134 p_usec_enr_dis_status := 'S';
135 p_usec_teach_resp_status := 'S';
136 p_usec_ass_item_grp_status := 'S';
137
138
139 --Similarly for all the other PL/SQL table status variable
140
141
142 --Call the Unit Section sub process
143 IF p_usec_tbl.COUNT > 0 THEN
144 SAVEPOINT create_unit_section;
145
146 igs_ps_unit_lgcy_pkg.create_unit_section( p_usec_tbl,p_usec_status,p_calling_context);
147 l_record_exists := TRUE;
148
149 IF p_usec_status = 'E' THEN
150 --Set the API status to 'E'
151 x_return_status := 'E';
152 --Set the record status to 'P' for the 'S' marked record
153 FOR I in 1..p_uso_tbl.LAST LOOP
154 IF p_usec_tbl.EXISTS(I) THEN
155 IF p_usec_tbl(I).status = 'S' THEN
156 p_usec_tbl(I).status := 'P';
157 END IF;
158 END IF;
159 END LOOP;
160 ROLLBACK TO create_unit_section;
161 END IF;
162 END IF;
163
164
165 --Call the Unit Section Grading schema sub process
166 IF p_usec_gs_tbl.COUNT > 0 THEN
167 SAVEPOINT create_usec_grd_sch;
168
169 igs_ps_unit_lgcy_pkg.create_usec_grd_sch( p_usec_gs_tbl,p_usec_gs_status,p_calling_context);
170 l_record_exists := TRUE;
171
172 IF p_usec_gs_status = 'E' THEN
173 --Set the API status to 'E'
174 x_return_status := 'E';
175 --Set the record status to 'P' for the 'S' marked record
176 FOR I in 1..p_usec_gs_tbl.LAST LOOP
177 IF p_usec_gs_tbl.EXISTS(I) THEN
178 IF p_usec_gs_tbl(I).status = 'S' THEN
179 p_usec_gs_tbl(I).status := 'P';
180 END IF;
181 END IF;
182 END LOOP;
183 ROLLBACK TO create_usec_grd_sch;
184 END IF;
185 END IF;
186
187
188 --Call the Unit Section Occurrence sub process
189
190 IF p_uso_tbl.COUNT > 0 THEN
191 SAVEPOINT create_usec_occur;
192
193 igs_ps_unit_lgcy_pkg.create_usec_occur( p_uso_tbl,p_uso_status,p_calling_context);
194 l_record_exists := TRUE;
195
196 IF p_uso_status = 'E' THEN
197 --Set the API status to 'E'
198 x_return_status := 'E';
199 --Set the record status to 'P' for the 'S' marked record
200 FOR I in 1..p_uso_tbl.LAST LOOP
201 IF p_uso_tbl.EXISTS(I) THEN
202 IF p_uso_tbl(I).status = 'S' THEN
203 p_uso_tbl(I).status := 'P';
204 END IF;
205 END IF;
206 END LOOP;
207 ROLLBACK TO create_usec_occur;
208 END IF;
209 END IF;
210
211
212 -- Call the Unit Section Occurrence Instructors sub process
213 IF p_uso_ins_tbl.COUNT > 0 THEN
214 SAVEPOINT create_uso_ins;
215
216 igs_ps_create_generic_pkg.create_uso_ins_ovrd(p_uso_ins_tbl,p_uso_ins_status,p_calling_context);
217
218 l_record_exists := TRUE;
219
220 IF p_uso_ins_status = 'E' THEN
221 --Set the API status to 'E'
222 x_return_status := 'E';
223 FOR I in 1..p_uso_ins_tbl.LAST LOOP
224 IF p_uso_ins_tbl.EXISTS(I) THEN
225 IF p_uso_ins_tbl(I).status = 'S' THEN
226 p_uso_ins_tbl(I).status := 'P';
227 END IF;
228 END IF;
229 END LOOP;
230 ROLLBACK TO create_uso_ins;
231 END IF;
232 END IF;
233
234
235 -- Call the Unit Section Occurrence Facilities sub process
236 IF p_usec_occurs_facility_tbl.COUNT > 0 THEN
237 SAVEPOINT create_uso_facility;
238
239 igs_ps_create_generic_pkg.create_uso_facility(p_usec_occurs_facility_tbl,p_uso_facility_status,p_calling_context);
240 l_record_exists := TRUE;
241
242 IF p_uso_facility_status = 'E' THEN
243 --Set the API status to 'E'
244 x_return_status := 'E';
245 FOR I in 1..p_usec_occurs_facility_tbl.LAST LOOP
246 IF p_usec_occurs_facility_tbl.EXISTS(I) THEN
247 IF p_usec_occurs_facility_tbl(I).status = 'S' THEN
248 p_usec_occurs_facility_tbl(I).status := 'P';
249 END IF;
250 END IF;
251 END LOOP;
252 ROLLBACK TO create_uso_facility;
253 END IF;
254 END IF;
255
256
257 -- Call the Unit Section/ Occurrence reference codes sub process
258 IF p_unit_ref_tbl.COUNT > 0 THEN
259 SAVEPOINT create_unit_ref_code;
260
261 igs_ps_unit_lgcy_pkg.create_unit_ref_code(p_unit_ref_tbl,p_unit_ref_status,p_calling_context);
262 l_record_exists := TRUE;
263
264 IF p_unit_ref_status = 'E' THEN
265 --Set the API status to 'E'
266 x_return_status := 'E';
267 FOR I in 1..p_unit_ref_tbl.LAST LOOP
268 IF p_unit_ref_tbl.EXISTS(I) THEN
269 IF p_unit_ref_tbl(I).status = 'S' THEN
270 p_unit_ref_tbl(I).status := 'P';
271 END IF;
272 END IF;
273 END LOOP;
274 ROLLBACK TO create_unit_ref_code;
275 END IF;
276 END IF;
277
278
279 -- Call the Unit Section teaching responsibility Override sub process
280 IF p_usec_teach_resp_ovrd_tbl.COUNT > 0 THEN
281 SAVEPOINT create_usec_teach_resp_ovrd;
282
283 igs_ps_create_generic_pkg.create_usec_teach_resp_ovrd(p_usec_teach_resp_ovrd_tbl,p_usec_teach_resp_ovrd_status,p_calling_context);
284 l_record_exists := TRUE;
285
286 IF p_usec_teach_resp_ovrd_status = 'E' THEN
287 --Set the API status to 'E'
288 x_return_status := 'E';
289 FOR I in 1..p_usec_teach_resp_ovrd_tbl.LAST LOOP
290 IF p_usec_teach_resp_ovrd_tbl.EXISTS(I) THEN
291 IF p_usec_teach_resp_ovrd_tbl(I).status = 'S' THEN
292 p_usec_teach_resp_ovrd_tbl(I).status := 'P';
293 END IF;
294 END IF;
295 END LOOP;
296 ROLLBACK TO create_usec_teach_resp_ovrd;
297 END IF;
298 END IF;
299
300
301 -- Call the Unit Section Notes sub process
302 IF p_usec_notes_tbl.COUNT > 0 THEN
303 SAVEPOINT create_usec_notes;
304
305 igs_ps_create_generic_pkg.create_usec_notes(p_usec_notes_tbl,p_usec_notes_status,p_calling_context);
306 l_record_exists := TRUE;
307
308 IF p_usec_notes_status = 'E' THEN
309 --Set the API status to 'E'
310 x_return_status := 'E';
311 FOR I in 1..p_usec_notes_tbl.LAST LOOP
312 IF p_usec_notes_tbl.EXISTS(I) THEN
313 IF p_usec_notes_tbl(I).status = 'S' THEN
314 p_usec_notes_tbl(I).status := 'P';
315 END IF;
316 END IF;
317 END LOOP;
318 ROLLBACK TO create_usec_notes;
319 END IF;
320 END IF;
321
322
323 -- Call the Unit Section Assessment sub process
324 IF p_usec_assmnt_tbl.COUNT > 0 THEN
325 SAVEPOINT create_usec_assmnt;
326
327 igs_ps_create_generic_pkg.create_usec_assmnt(p_usec_assmnt_tbl,p_usec_assmnt_status,p_calling_context);
328 l_record_exists := TRUE;
329
330 IF p_usec_assmnt_status = 'E' THEN
331 --Set the API status to 'E'
332 x_return_status := 'E';
336 p_usec_assmnt_tbl(I).status := 'P';
333 FOR I in 1..p_usec_assmnt_tbl.LAST LOOP
334 IF p_usec_assmnt_tbl.EXISTS(I) THEN
335 IF p_usec_assmnt_tbl(I).status = 'S' THEN
337 END IF;
338 END IF;
339 END LOOP;
340 ROLLBACK TO create_usec_assmnt;
341 END IF;
342 END IF;
343
344 -- Call the Unit Section Plus Hour sub process
345 IF p_usec_plus_hr_tbl.COUNT > 0 THEN
346 SAVEPOINT create_usec_plus_hr;
347
348 igs_ps_create_generic_pkg.create_usec_plus_hr(p_usec_plus_hr_tbl,p_usec_plus_hr_status,p_calling_context);
349 l_record_exists := TRUE;
350
351 IF p_usec_plus_hr_status = 'E' THEN
352 --Set the API status to 'E'
353 x_return_status := 'E';
354 FOR I in 1..p_usec_plus_hr_tbl.LAST LOOP
355 IF p_usec_plus_hr_tbl.EXISTS(I) THEN
356 IF p_usec_plus_hr_tbl(I).status = 'S' THEN
357 p_usec_plus_hr_tbl(I).status := 'P';
358 END IF;
359 END IF;
360 END LOOP;
361 ROLLBACK TO create_usec_plus_hr;
362 END IF;
363 END IF;
364
365 -- Call the Unit Section Categories sub process
366 IF p_usec_cat_tbl.COUNT > 0 THEN
367 SAVEPOINT create_usec_cat;
368
369 igs_ps_create_generic_pkg.create_usec_cat(p_usec_cat_tbl,p_usec_cat_status,p_calling_context);
370 l_record_exists := TRUE;
371
372 IF p_usec_cat_status = 'E' THEN
373 --Set the API status to 'E'
374 x_return_status := 'E';
375 FOR I in 1..p_usec_cat_tbl.LAST LOOP
376 IF p_usec_cat_tbl.EXISTS(I) THEN
377 IF p_usec_cat_tbl(I).status = 'S' THEN
378 p_usec_cat_tbl(I).status := 'P';
379 END IF;
380 END IF;
381 END LOOP;
382 ROLLBACK TO create_usec_cat;
383 END IF;
384 END IF;
385
386 -- Call the Unit Section Rule sub process
387 IF p_usec_rule_tbl.COUNT > 0 THEN
388 SAVEPOINT create_usec_rule;
389
390 igs_ps_create_generic_pkg.create_usec_rule(p_usec_rule_tbl,p_usec_rule_status,p_calling_context);
391 l_record_exists := TRUE;
392
393 IF p_usec_rule_status = 'E' THEN
394 --Set the API status to 'E'
395 x_return_status := 'E';
396 FOR I in 1..p_usec_rule_tbl.LAST LOOP
397 IF p_usec_rule_tbl.EXISTS(I) THEN
398 IF p_usec_rule_tbl(I).status = 'S' THEN
399 p_usec_rule_tbl(I).status := 'P';
400 END IF;
401 END IF;
402 END LOOP;
403 ROLLBACK TO create_usec_rule;
404 END IF;
405 END IF;
406
407 -- Call the Unit Section Cross Listed Group sub process
408 IF p_usec_cross_group_tbl.COUNT > 0 THEN
409 SAVEPOINT create_usec_cross_group;
410
411 igs_ps_create_generic_pkg.create_usec_cross_group(p_usec_cross_group_tbl,p_usec_cross_group_status,p_calling_context);
412 l_record_exists := TRUE;
413
414 IF p_usec_cross_group_status = 'E' THEN
415 --Set the API status to 'E'
416 x_return_status := 'E';
417 FOR I in 1..p_usec_cross_group_tbl.LAST LOOP
418 IF p_usec_cross_group_tbl.EXISTS(I) THEN
419 IF p_usec_cross_group_tbl(I).status = 'S' THEN
420 p_usec_cross_group_tbl(I).status := 'P';
421 END IF;
422 END IF;
423 END LOOP;
424 ROLLBACK TO create_usec_cross_group;
425 END IF;
426 END IF;
427
428
429 -- Call the Unit Section Meet With Group sub process
430 IF p_usec_meet_with_tbl.COUNT > 0 THEN
431 SAVEPOINT create_usec_meet_with;
432
433 igs_ps_create_generic_pkg.create_usec_meet_with(p_usec_meet_with_tbl,p_usec_meet_with_status,p_calling_context);
434 l_record_exists := TRUE;
435
436 IF p_usec_meet_with_status = 'E' THEN
437 --Set the API status to 'E'
438 x_return_status := 'E';
439 FOR I in 1..p_usec_meet_with_tbl.LAST LOOP
440 IF p_usec_meet_with_tbl.EXISTS(I) THEN
441 IF p_usec_meet_with_tbl(I).status = 'S' THEN
442 p_usec_meet_with_tbl(I).status := 'P';
443 END IF;
444 END IF;
445 END LOOP;
446 ROLLBACK TO create_usec_meet_with;
447 END IF;
448 END IF;
449
450 -- Call the Unit Section Waitlist sub process
451 IF p_usec_waitlist_tbl.COUNT > 0 THEN
452 SAVEPOINT create_usec_waitlist;
453
454 igs_ps_create_generic_pkg.create_usec_waitlist(p_usec_waitlist_tbl,p_usec_waitlist_status,p_calling_context);
455 l_record_exists := TRUE;
456
457 IF p_usec_waitlist_status = 'E' THEN
458 --Set the API status to 'E'
459 x_return_status := 'E';
460 FOR I in 1..p_usec_waitlist_tbl.LAST LOOP
461 IF p_usec_waitlist_tbl.EXISTS(I) THEN
462 IF p_usec_waitlist_tbl(I).status = 'S' THEN
463 p_usec_waitlist_tbl(I).status := 'P';
464 END IF;
465 END IF;
466 END LOOP;
467 ROLLBACK TO create_usec_waitlist;
468 END IF;
469 END IF;
470
471 -- Call the Unit Section Reserve Seating sub process
472 IF p_usec_res_seat_tbl.COUNT > 0 THEN
473 SAVEPOINT create_usec_res_seat;
474
475 igs_ps_create_generic_pkg.create_usec_res_seat(p_usec_res_seat_tbl,p_usec_res_seat_status,p_calling_context);
476 l_record_exists := TRUE;
477
478 IF p_usec_res_seat_status = 'E' THEN
479 --Set the API status to 'E'
480 x_return_status := 'E';
481 FOR I in 1..p_usec_res_seat_tbl.LAST LOOP
485 END IF;
482 IF p_usec_res_seat_tbl.EXISTS(I) THEN
483 IF p_usec_res_seat_tbl(I).status = 'S' THEN
484 p_usec_res_seat_tbl(I).status := 'P';
486 END IF;
487 END LOOP;
488 ROLLBACK TO create_usec_res_seat;
489 END IF;
490 END IF;
491
492 -- Call the Unit Section Special Fees sub process
493 IF p_usec_sp_fee_tbl.COUNT > 0 THEN
494 SAVEPOINT create_usec_sp_fee;
495
496 igs_ps_create_generic_pkg.create_usec_sp_fee(p_usec_sp_fee_tbl,p_usec_sp_fee_status,p_calling_context);
497 l_record_exists := TRUE;
498
499 IF p_usec_sp_fee_status = 'E' THEN
500 --Set the API status to 'E'
501 x_return_status := 'E';
502 FOR I in 1..p_usec_sp_fee_tbl.LAST LOOP
503 IF p_usec_sp_fee_tbl.EXISTS(I) THEN
504 IF p_usec_sp_fee_tbl(I).status = 'S' THEN
505 p_usec_sp_fee_tbl(I).status := 'P';
506 END IF;
507 END IF;
508 END LOOP;
509 ROLLBACK TO create_usec_sp_fee;
510 END IF;
511 END IF;
512
513 -- Call the Unit Section Retention sub process
514 IF p_usec_ret_tbl.COUNT > 0 THEN
515 SAVEPOINT create_usec_ret;
516
517 igs_ps_create_generic_pkg.create_usec_ret(p_usec_ret_tbl,p_usec_ret_status,p_calling_context);
518 l_record_exists := TRUE;
519
520 IF p_usec_ret_status = 'E' THEN
521 --Set the API status to 'E'
522 x_return_status := 'E';
523 FOR I in 1..p_usec_ret_tbl.LAST LOOP
524 IF p_usec_ret_tbl.EXISTS(I) THEN
525 IF p_usec_ret_tbl(I).status = 'S' THEN
526 p_usec_ret_tbl(I).status := 'P';
527 END IF;
528 END IF;
529 END LOOP;
530 ROLLBACK TO create_usec_ret;
531 END IF;
532 END IF;
533
534 -- Call the Unit Section Retention Details sub process
535 IF p_usec_ret_dtl_tbl.COUNT > 0 THEN
536 SAVEPOINT create_usec_ret_dtl;
537
538 igs_ps_create_generic_pkg.create_usec_ret_dtl(p_usec_ret_dtl_tbl,p_usec_ret_dtl_status,p_calling_context);
539 l_record_exists := TRUE;
540
541 IF p_usec_ret_dtl_status = 'E' THEN
542 --Set the API status to 'E'
543 x_return_status := 'E';
544 FOR I in 1..p_usec_ret_dtl_tbl.LAST LOOP
545 IF p_usec_ret_dtl_tbl.EXISTS(I) THEN
546 IF p_usec_ret_dtl_tbl(I).status = 'S' THEN
547 p_usec_ret_dtl_tbl(I).status := 'P';
548 END IF;
549 END IF;
550 END LOOP;
551 ROLLBACK TO create_usec_ret_dtl;
552 END IF;
553 END IF;
554
555 -- Call the Unit Section Enrollment Deadline sub process
556 IF p_usec_enr_dead_tbl.COUNT > 0 THEN
557 SAVEPOINT create_usec_enr_dead;
558
559 igs_ps_create_generic_pkg.create_usec_enr_dead(p_usec_enr_dead_tbl,p_usec_enr_dead_status,p_calling_context);
560 l_record_exists := TRUE;
561
562 IF p_usec_enr_dead_status = 'E' THEN
563 --Set the API status to 'E'
564 x_return_status := 'E';
565 FOR I in 1..p_usec_enr_dead_tbl.LAST LOOP
566 IF p_usec_enr_dead_tbl.EXISTS(I) THEN
567 IF p_usec_enr_dead_tbl(I).status = 'S' THEN
568 p_usec_enr_dead_tbl(I).status := 'P';
569 END IF;
570 END IF;
571 END LOOP;
572 ROLLBACK TO create_usec_enr_dead;
573 END IF;
574 END IF;
575
576 -- Call the Unit Section Enrollment Discontinuation sub process
577 IF p_usec_enr_dis_tbl.COUNT > 0 THEN
578 SAVEPOINT create_usec_enr_dis;
579
580 igs_ps_create_generic_pkg.create_usec_enr_dis(p_usec_enr_dis_tbl,p_usec_enr_dis_status,p_calling_context);
581 l_record_exists := TRUE;
582
583 IF p_usec_enr_dis_status = 'E' THEN
584 --Set the API status to 'E'
585 x_return_status := 'E';
586 FOR I in 1..p_usec_enr_dis_tbl.LAST LOOP
587 IF p_usec_enr_dis_tbl.EXISTS(I) THEN
588 IF p_usec_enr_dis_tbl(I).status = 'S' THEN
589 p_usec_enr_dis_tbl(I).status := 'P';
590 END IF;
591 END IF;
592 END LOOP;
593 ROLLBACK TO create_usec_enr_dis;
594 END IF;
595 END IF;
596
597 -- Call the Unit Section Teaching Responsibility (for Update only) sub process
598 IF p_usec_teach_resp_tbl.COUNT > 0 THEN
599 SAVEPOINT create_usec_teach_resp;
600
601 igs_ps_create_generic_pkg.create_usec_teach_resp(p_usec_teach_resp_tbl,p_usec_teach_resp_status,p_calling_context);
602 l_record_exists := TRUE;
603
604 IF p_usec_teach_resp_status = 'E' THEN
605 --Set the API status to 'E'
606 x_return_status := 'E';
607 FOR I in 1..p_usec_teach_resp_tbl.LAST LOOP
608 IF p_usec_teach_resp_tbl.EXISTS(I) THEN
609 IF p_usec_teach_resp_tbl(I).status = 'S' THEN
610 p_usec_teach_resp_tbl(I).status := 'P';
611 END IF;
612 END IF;
613 END LOOP;
614 ROLLBACK TO create_usec_teach_resp;
615 END IF;
616 END IF;
617
618 -- Call the Unit Section Assessment Item Group sub process
619 IF p_usec_ass_item_grp_tbl.COUNT > 0 THEN
620 SAVEPOINT create_usec_ass_item_grp;
621
622 igs_ps_create_generic_pkg.create_usec_ass_item_grp(p_usec_ass_item_grp_tbl,p_usec_ass_item_grp_status,p_calling_context);
623 l_record_exists := TRUE;
624
625 IF p_usec_ass_item_grp_status = 'E' THEN
626 --Set the API status to 'E'
627 x_return_status := 'E';
628 FOR I in 1..p_usec_ass_item_grp_tbl.LAST LOOP
629 IF p_usec_ass_item_grp_tbl.EXISTS(I) THEN
630 IF p_usec_ass_item_grp_tbl(I).status = 'S' THEN
631 p_usec_ass_item_grp_tbl(I).status := 'P';
632 END IF;
633 END IF;
634 END LOOP;
635 ROLLBACK TO create_usec_ass_item_grp;
636 END IF;
637 END IF;
638
639 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
640 fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_generic_pvt.psp_import.end_of_logging_for',
641 'Data import from external Sysytem to OSS ');
642 END IF;
643
644 --If none of the PL/SQL data has been passed then raise error
645 IF NOT l_record_exists THEN
646 FND_MESSAGE.SET_NAME ('IGS','IGS_PS_LGCY_DATA_NOT_PASSED');
647 FND_MSG_PUB.ADD;
648 RAISE FND_API.G_EXC_ERROR;
649 END IF;
650
651 --End of API body
652
653 --Standard check of p_commit
654 IF FND_API.TO_Boolean( p_commit) THEN
655 COMMIT WORK;
656 END IF;
657
658 --Standard call to get message count and if count is 1, get message info.
659 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
660 p_data => x_msg_data);
661
662
663 EXCEPTION
664 WHEN FND_API.G_EXC_ERROR THEN
665 ROLLBACK TO psp_import_PVT;
666 x_return_status := FND_API.G_RET_STS_ERROR;
667 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
668 p_data => x_msg_data );
669
670 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
671 ROLLBACK TO psp_import_PVT;
672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
673 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
674 p_data => x_msg_data );
675
676 WHEN OTHERS THEN
677 ROLLBACK TO psp_import_PVT;
678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
679 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
680 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
681 l_api_name);
682 END IF;
683 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
684 p_data => x_msg_data );
685 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
686 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_generic_pvt.psp_import.in_exception_section_OTHERS.err_msg',
687 SUBSTRB(SQLERRM,1,4000));
688 END IF;
689
690 END psp_import;
691
692 END igs_ps_generic_pvt;
[Home] [Help]