[Home] [Help]
PACKAGE BODY: APPS.IGS_RE_CANDIDATURE_PKG
Source
1 package body IGS_RE_CANDIDATURE_PKG as
2 /* $Header: IGSRI01B.pls 120.1 2005/07/04 00:40:40 appldev ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_re_val_ca.genp_val_sdtt_sess
7 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
8 -------------------------------------------------------------------------------------------
9 l_rowid VARCHAR2(25);
10 old_references IGS_RE_CANDIDATURE_ALL%RowType;
11 new_references IGS_RE_CANDIDATURE_ALL%RowType;
12 PROCEDURE Set_Column_Values (
13 p_action IN VARCHAR2,
14 x_rowid IN VARCHAR2,
15 x_industry_links IN VARCHAR2 ,
16 x_person_id IN NUMBER ,
17 x_sequence_number IN NUMBER ,
18 x_sca_course_cd IN VARCHAR2 ,
19 x_acai_admission_appl_number IN NUMBER ,
20 x_acai_nominated_course_cd IN VARCHAR2 ,
21 x_acai_sequence_number IN NUMBER ,
22 x_attendance_percentage IN NUMBER ,
23 x_govt_type_of_activity_cd IN VARCHAR2 ,
24 x_max_submission_dt IN DATE ,
25 x_min_submission_dt IN DATE ,
26 x_research_topic IN VARCHAR2 ,
27 x_creation_date IN DATE ,
28 x_created_by IN NUMBER ,
29 x_last_update_date IN DATE ,
30 x_last_updated_by IN NUMBER ,
31 x_last_update_login IN NUMBER ,
32 x_org_id IN NUMBER
33 ) AS
34 CURSOR cur_old_ref_values IS
35 SELECT *
36 FROM IGS_RE_CANDIDATURE_ALL
37 WHERE rowid = x_rowid;
38 BEGIN
39 l_rowid := x_rowid;
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 Open cur_old_ref_values;
43 Fetch cur_old_ref_values INTO old_references;
44 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
45 Close cur_old_ref_values;
46 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
47 IGS_GE_MSG_STACK.ADD;
48 App_Exception.Raise_Exception;
49 Return;
50 END IF;
51 Close cur_old_ref_values;
52 -- Populate New Values.
53 new_references.industry_links := x_industry_links;
54 new_references.person_id := x_person_id;
55 new_references.sequence_number := x_sequence_number;
56 new_references.sca_course_cd := x_sca_course_cd;
57 new_references.acai_admission_appl_number := x_acai_admission_appl_number;
58 new_references.acai_nominated_course_cd := x_acai_nominated_course_cd;
59 new_references.acai_sequence_number := x_acai_sequence_number;
60 new_references.attendance_percentage := x_attendance_percentage;
61 new_references.govt_type_of_activity_cd := x_govt_type_of_activity_cd;
62 new_references.max_submission_dt := x_max_submission_dt;
63 new_references.min_submission_dt := x_min_submission_dt;
64 new_references.research_topic := x_research_topic;
65 new_references.org_id := x_org_id ;
66
67 IF (p_action = 'UPDATE') THEN
68 new_references.creation_date := old_references.creation_date;
69 new_references.created_by := old_references.created_by;
70 ELSE
71 new_references.creation_date := x_creation_date;
72 new_references.created_by := x_created_by;
73 END IF;
74 new_references.last_update_date := x_last_update_date;
75 new_references.last_updated_by := x_last_updated_by;
76 new_references.last_update_login := x_last_update_login;
77 END Set_Column_Values;
78
79 PROCEDURE BeforeRowInsertUpdate1(
80 p_inserting IN BOOLEAN,
81 p_updating IN BOOLEAN,
82 p_deleting IN BOOLEAN
83 ) AS
84 v_sequence_number NUMBER;
85 BEGIN
86 -- Log an entry in the IGS_PE_STD_TODO table, indicating that a fee re-assessment
87 -- is required.
88 IF p_updating THEN
89 -- Indicate fee assessment if attendance percentage has changed
90 -- and the IGS_RE_CANDIDATURE is linked to a student IGS_PS_COURSE attempt
91 IF (NVL(old_references.attendance_percentage,-1) <> NVL(new_references.attendance_percentage,-1))
92 AND
93 (new_references.sca_course_cd IS NOT NULL) THEN
94 v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(
95 new_references.person_id,
96 'FEE_RECALC',
97 SYSDATE,
98 'Y');
99 END IF;
100 END IF;
101 END BeforeRowInsertUpdate1;
102
103 PROCEDURE BeforeRowInsertUpdateDelete2(
104 p_inserting IN BOOLEAN,
105 p_updating IN BOOLEAN,
106 p_deleting IN BOOLEAN
107 ) AS
108 v_message_name VARCHAR2(30);
109 v_old_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
110 v_old_acai_admission_appl_num IGS_RE_CANDIDATURE.acai_admission_appl_number%TYPE;
111 v_old_acai_nominated_course_cd IGS_RE_CANDIDATURE.acai_nominated_course_cd%TYPE;
112 v_old_acai_sequence_number IGS_RE_CANDIDATURE.acai_sequence_number%TYPE;
113 BEGIN
114 IF p_inserting OR
115 p_updating THEN
116 IF p_inserting THEN
117 v_old_sca_course_cd := NULL;
118 v_old_acai_admission_appl_num := NULL;
119 v_old_acai_nominated_course_cd := NULL;
120 v_old_acai_sequence_number := NULL;
121 ELSE
122 v_old_sca_course_cd := old_references.sca_course_cd;
123 v_old_acai_admission_appl_num := old_references.acai_admission_appl_number;
124 v_old_acai_nominated_course_cd := old_references.acai_nominated_course_cd;
125 v_old_acai_sequence_number := old_references.acai_sequence_number;
126 END IF;
127 -- Validate that one of SCA or ACAI links exist, and that
128 -- the details match their parent(s)
129 IF p_inserting OR
130 (p_updating AND
131 (NVL(old_references.sca_course_cd,'NULL') <>
132 NVL(new_references.sca_course_cd,'NULL')) OR
133 (NVL(old_references.acai_admission_appl_number,0) <>
134 NVL(new_references.acai_admission_appl_number,0)) OR
135 (NVL(old_references.acai_nominated_course_cd,'NULL') <>
136 NVL(new_references.acai_nominated_course_cd,'NULL')) OR
137 (NVL(old_references.acai_sequence_number,0) <>
138 NVL(new_references.acai_sequence_number,0))) THEN
139 IF IGS_RE_VAL_CA.resp_val_ca_sca_acai (
140 new_references.person_id,
141 new_references.sca_course_cd,
142 new_references.acai_admission_appl_number,
143 new_references.acai_nominated_course_cd,
144 new_references.acai_sequence_number,
145 v_message_name) = FALSE THEN
146 Fnd_Message.Set_Name ('IGS', v_message_name);
147 IGS_GE_MSG_STACK.ADD;
148 App_Exception.Raise_Exception;
149 END IF;
150 END IF;
151 IF p_updating AND
152 new_references.sca_course_cd IS NOT NULL THEN
153 -- Validate that updates are allowed
154 IF IGS_RE_VAL_CA.resp_val_ca_upd(
155 new_references.person_id,
156 new_references.sca_course_cd,
157 v_message_name) = FALSE THEN
158 Fnd_Message.Set_Name ('IGS', v_message_name);
159 IGS_GE_MSG_STACK.ADD;
160 App_Exception.Raise_Exception;
161 END IF;
162 END IF;
163 -- Validation of submission dates can only be done in before row trigger
164 -- if p_inserting, or
165 -- p_updating and either:
166 -- attendance percentage has not been changed, or
167 -- both submission dates are set
168 -- If this is not the case, validation is in the after statement trigger.
169 IF p_inserting OR
170 (p_updating AND
171 (NVL(old_references.attendance_percentage,0) = NVL(new_references.attendance_percentage,0) OR
172 (new_references.min_submission_dt IS NOT NULL AND
173 new_references.max_submission_dt IS NOT NULL))) THEN
174 -- Validate minimum submission date
175 IF (p_inserting AND
176 new_references.min_submission_dt IS NOT NULL) OR
177 (p_updating AND
178 NVL(old_references.min_submission_dt,igs_ge_date.igsdate('1900/01/01')) <>
179 NVL(new_references.min_submission_dt,igs_ge_date.igsdate('1900/01/01')) ) THEN
180 IF IGS_RE_VAL_CA.resp_val_ca_minsbmsn (
181 new_references.person_id,
182 new_references.sca_course_cd,
183 new_references.acai_admission_appl_number,
184 new_references.acai_nominated_course_cd,
185 new_references.acai_sequence_number,
186 new_references.min_submission_dt,
187 new_references.max_submission_dt,
188 new_references.attendance_percentage,
189 NULL, -- commencement date should already be updated
190 v_message_name,
191 'N') = FALSE THEN
192 Fnd_Message.Set_Name ('IGS', v_message_name);
193 IGS_GE_MSG_STACK.ADD;
194 App_Exception.Raise_Exception;
195 END IF;
196 END IF;
197 -- Validate maximum submission date
198 IF (p_inserting AND
199 new_references.max_submission_dt IS NOT NULL) OR
200 (p_updating AND
201 NVL(old_references.max_submission_dt,igs_ge_date.igsdate('1900/01/01')) <>
202 NVL(new_references.max_submission_dt,igs_ge_date.igsdate('1900/01/01')) ) THEN
203 IF IGS_RE_VAL_CA.resp_val_ca_maxsbmsn (
204 new_references.person_id,
205 new_references.sca_course_cd,
206 new_references.acai_admission_appl_number,
207 new_references.acai_nominated_course_cd,
208 new_references.acai_sequence_number,
209 new_references.min_submission_dt,
210 new_references.max_submission_dt,
211 new_references.attendance_percentage,
212 NULL, -- commencement date should already be updated
213 v_message_name,
214 'N') = FALSE THEN
215 Fnd_Message.Set_Name ('IGS', v_message_name);
216 IGS_GE_MSG_STACK.ADD;
217 App_Exception.Raise_Exception;
218 END IF;
219 END IF;
220 END IF;
221 -- Validate govt type of activity code
222 IF (p_inserting AND
223 new_references.govt_type_of_activity_cd IS NOT NULL) OR
224 (p_updating AND
225 NVL(old_references.govt_type_of_activity_cd,'NULL') <>
226 NVL(new_references.govt_type_of_activity_cd,'NULL')) THEN
227 IF IGS_RE_VAL_CA.resp_val_gtcc_closed (
228 new_references.govt_type_of_activity_cd,
229 v_message_name) = FALSE THEN
230 Fnd_Message.Set_Name ('IGS', v_message_name);
231 IGS_GE_MSG_STACK.ADD;
232 App_Exception.Raise_Exception;
233 END IF;
234 END IF;
235 -- Validate research topic
236 IF p_inserting OR
237 (p_updating AND
238 NVL(old_references.research_topic,'NULL') <> NVL(new_references.research_topic,'NULL')) THEN
239 IF IGS_RE_VAL_CA.resp_val_ca_topic (
240 new_references.person_id,
241 new_references.sca_course_cd,
242 new_references.acai_admission_appl_number,
243 new_references.acai_nominated_course_cd,
244 new_references.acai_sequence_number,
245 new_references.research_topic,
246 v_message_name,
247 'N') = FALSE THEN
248 Fnd_Message.Set_Name ('IGS', v_message_name);
249 IGS_GE_MSG_STACK.ADD;
250 App_Exception.Raise_Exception;
251 END IF;
252 END IF;
253 END IF;
254
255 IF p_deleting THEN
256 -- Validate SCA link.
257 IF IGS_RE_VAL_CA.resp_val_ca_sca_del (
258 old_references.person_id,
259 old_references.sca_course_cd,
260 v_message_name) = FALSE THEN
261 Fnd_Message.Set_Name ('IGS', v_message_name);
262 IGS_GE_MSG_STACK.ADD;
263 App_Exception.Raise_Exception;
264 END IF;
265 -- Validate ACAI link.
266 IF IGS_RE_VAL_CA.resp_val_ca_acai_del (
267 old_references.person_id,
268 old_references.acai_admission_appl_number,
269 old_references.acai_nominated_course_cd,
270 old_references.acai_sequence_number,
271 v_message_name) = FALSE THEN
272 Fnd_Message.Set_Name ('IGS', v_message_name);
273 IGS_GE_MSG_STACK.ADD;
274 App_Exception.Raise_Exception;
275 END IF;
276 END IF;
277 END BeforeRowInsertUpdateDelete2;
278
279 PROCEDURE AfterRowInsertUpdate3(
280 p_inserting IN BOOLEAN,
281 p_updating IN BOOLEAN,
282 p_deleting IN BOOLEAN
283 ) AS
284 v_message_name VARCHAR2(30);
285 v_old_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
286 v_old_acai_admission_appl_num IGS_RE_CANDIDATURE.acai_admission_appl_number%TYPE;
287 v_old_acai_nominated_course_cd IGS_RE_CANDIDATURE.acai_nominated_course_cd%TYPE;
288 v_old_acai_sequence_number IGS_RE_CANDIDATURE.acai_sequence_number%TYPE;
289 BEGIN
290
291 IF p_inserting OR
292 p_updating THEN
293
294 IF p_inserting THEN
295 v_old_sca_course_cd := NULL;
296 v_old_acai_admission_appl_num := NULL;
297 v_old_acai_nominated_course_cd := NULL;
298 v_old_acai_sequence_number := NULL;
299 ELSE
300 v_old_sca_course_cd := old_references.sca_course_cd;
301 v_old_acai_admission_appl_num := old_references.acai_admission_appl_number;
302 v_old_acai_nominated_course_cd := old_references.acai_nominated_course_cd;
303 v_old_acai_sequence_number := old_references.acai_sequence_number;
304 END IF;
305
306 -- Validate that SCA and ACAI links
307 IF p_inserting OR
308 (p_updating AND
309 NVL(old_references.sca_course_cd,'NULL') <> NVL(new_references.sca_course_cd,'NULL')) OR
310 (p_updating AND
311 (NVL(old_references.acai_admission_appl_number,0) <>
312 NVL(new_references.acai_admission_appl_number,0)) OR
313 (NVL(old_references.acai_nominated_course_cd,'NULL') <>
314 NVL(new_references.acai_nominated_course_cd,'NULL')) OR
315 (NVL(old_references.acai_sequence_number,0) <>
316 NVL(new_references.acai_sequence_number,0))) THEN
317 NULL;
318 END IF;
319
320 -- Bug # 2829275 . UK Correspondence.The research topic event is raised when there is either a change or a research topic is created.
321
322 IF (p_inserting AND new_references.research_topic IS NOT NULL ) THEN
323
324 igs_re_workflow.retopic_event (
325 p_personid => new_references.person_id,
326 p_programcd => new_references.sca_course_cd,
327 p_restopic => new_references.research_topic
328 );
329
330 ELSIF ( p_updating AND ((new_references.research_topic IS NULL AND old_references.research_topic IS NOT NULL) OR
331 (new_references.research_topic IS NOT NULL AND old_references.research_topic IS NULL)
332 OR new_references.research_topic <> old_references.research_topic) ) THEN
333
334 igs_re_workflow.retopic_event (
335 p_personid => new_references.person_id,
336 p_programcd => new_references.sca_course_cd,
337 p_restopic => new_references.research_topic
338 );
339
340 END IF;
341
342 END IF;
343 END AfterRowInsertUpdate3;
344
345 PROCEDURE AfterRowUpdateDelete4(
346 p_inserting IN BOOLEAN,
347 p_updating IN BOOLEAN,
348 p_deleting IN BOOLEAN
349 ) AS
350 BEGIN
351 IF p_updating THEN
352 -- create a history
353 IGS_RE_GEN_002.RESP_INS_CA_HIST( old_references.person_id,
354 old_references.sequence_number,
355 old_references.sca_course_cd,
356 new_references.sca_course_cd,
357 old_references.acai_admission_appl_number,
358 new_references.acai_admission_appl_number,
359 old_references.acai_nominated_course_cd,
360 new_references.acai_nominated_course_cd,
361 old_references.acai_sequence_number,
362 new_references.acai_sequence_number,
363 old_references.attendance_percentage,
364 new_references.attendance_percentage,
365 old_references.govt_type_of_activity_cd,
366 new_references.govt_type_of_activity_cd,
367 old_references.max_submission_dt,
368 new_references.max_submission_dt,
369 old_references.min_submission_dt,
370 new_references.min_submission_dt,
371 old_references.research_topic,
372 new_references.research_topic,
373 old_references.industry_links,
374 new_references.industry_links,
375 old_references.last_updated_by,
376 new_references.last_updated_by,
377 old_references.last_update_date,
378 new_references.last_update_date);
379 END IF;
380 END AfterRowUpdateDelete4;
381
382 PROCEDURE AfterStmtInsertUpdate5(
383 p_inserting IN BOOLEAN,
384 p_updating IN BOOLEAN,
385 p_deleting IN BOOLEAN
386 ) AS
387 v_message_name VARCHAR2(30);
388
389 BEGIN
390 -- If trigger has not been disabled, perform required processing
391 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_RE_CANDIDATURE_ALL') THEN
392 -- Insert IGS_RE_CANDIDATURE attendance history from row ids saved
393 -- when IGS_RE_CANDIDATURE attendance percentage changed
394
395 -- Mutation logic pasted
396 -- Insert IGS_RE_CANDIDATURE attendance history
397 IF IGS_RE_GEN_002.RESP_INS_CA_CAH(
398 New_References.person_id,
399 New_References.sequence_number,
400 New_References.sca_course_cd,
401 New_References.attendance_percentage,
402 v_message_name) = FALSE THEN
403 Fnd_Message.Set_Name ('IGS', v_message_name);
404 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
405 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,'igs.plsql.igs_re_candidature_pkg.AfterStmtInsertUpdate5.ERR',FALSE);
406 END IF;
407 IGS_GE_MSG_STACK.ADD;
408 App_Exception.Raise_Exception;
409 END IF;
410 IF p_updating THEN
411 IF New_References.min_submission_dt IS NULL THEN
412 -- Validate derived minimum submission date
413 IF IGS_RE_VAL_CA.resp_val_ca_minsbmsn (
414 New_References.person_id,
415 New_References.sca_course_cd,
416 New_References.acai_admission_appl_number,
417 New_References.acai_nominated_course_cd,
418 New_References.acai_sequence_number,
419 New_References.min_submission_dt,
420 New_References.max_submission_dt,
421 New_References.attendance_percentage,
422 NULL, -- commencement date
423 v_message_name,
424 'N') = FALSE THEN
425 Fnd_Message.Set_Name ('IGS', v_message_name);
426 IGS_GE_MSG_STACK.ADD;
427 App_Exception.Raise_Exception;
428 END IF;
429 END IF;
430 IF New_References.max_submission_dt IS NULL THEN
431 -- Validate derived maximum submission date
432 IF IGS_RE_VAL_CA.resp_val_ca_maxsbmsn (
433 New_References.person_id,
434 New_References.sca_course_cd,
435 New_References.acai_admission_appl_number,
436 New_References.acai_nominated_course_cd,
437 New_References.acai_sequence_number,
438 New_References.min_submission_dt,
439 New_References.max_submission_dt,
440 New_References.attendance_percentage,
441 NULL, -- commencement date
442 v_message_name,
443 'N') = FALSE THEN
444 Fnd_Message.Set_Name ('IGS', v_message_name);
445 IGS_GE_MSG_STACK.ADD;
446 App_Exception.Raise_Exception;
447 END IF;
448 END IF;
449 END IF;
450 -- Validate SCA and ACAI links
451 -- Comment out NOCOPY for now until resolved
452
453 -- Mutation logic pasted
454 -- Validate SCA link
455 IF p_inserting OR
456 (p_updating AND
457 NVL(Old_References.sca_course_cd,'NULL') <>
458 NVL(New_References.sca_course_cd,'NULL')) THEN
459 IF IGS_RE_VAL_CA.resp_val_ca_sca(
460 New_References.person_id,
461 New_References.sequence_number,
462 Old_References.sca_course_cd,
463 New_References.sca_course_cd,
464 New_References.acai_admission_appl_number,
465 New_References.acai_nominated_course_cd,
466 New_References.acai_sequence_number,
467 v_message_name) = FALSE THEN
468 Fnd_Message.Set_Name ('IGS', v_message_name);
469 IGS_GE_MSG_STACK.ADD;
470 App_Exception.Raise_Exception;
471 END IF;
472 END IF;
473 -- Validate ACAI link
474 IF p_inserting OR
475 (p_updating AND
476 (NVL(Old_References.acai_admission_appl_number,0) <>
477 NVL(New_References.acai_admission_appl_number,0)) OR
478 (NVL(Old_References.acai_nominated_course_cd,'NULL') <>
479 NVL(New_References.acai_nominated_course_cd,'NULL')) OR
480 (NVL(Old_References.acai_sequence_number,0) <>
481 NVL(New_References.acai_sequence_number,0))) THEN
482 IF IGS_RE_VAL_CA.resp_val_ca_acai(
483 New_References.person_id,
484 New_References.sequence_number,
485 New_References.sca_course_cd,
486 Old_References.acai_admission_appl_number,
487 Old_References.acai_nominated_course_cd,
488 Old_References.acai_sequence_number,
489 New_References.acai_admission_appl_number,
490 New_References.acai_nominated_course_cd,
491 New_References.acai_sequence_number,
492 v_message_name) = FALSE THEN
493 Fnd_Message.Set_Name ('IGS', v_message_name);
494 IGS_GE_MSG_STACK.ADD;
495 App_Exception.Raise_Exception;
496 END IF;
497 END IF;
498 END IF;
499 END AfterStmtInsertUpdate5;
500
501 PROCEDURE Check_Constraints (
502 Column_Name IN VARCHAR2,
503 Column_Value IN VARCHAR2
504 ) AS
505 BEGIN
506 IF Column_Name is null then
507 NULL;
508 ELSIF upper(Column_name) = 'ACAI_NOMINATED_COURSE_CD' THEN
509 new_references.ACAI_NOMINATED_COURSE_CD := COLUMN_VALUE ;
510 ELSIF upper(Column_name) = 'GOVT_TYPE_OF_ACTIVITY_CD' THEN
511 new_references.GOVT_TYPE_OF_ACTIVITY_CD := COLUMN_VALUE ;
512 ELSIF upper(Column_name) = 'SCA_COURSE_CD' THEN
513 new_references.SCA_COURSE_CD := COLUMN_VALUE ;
514 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' THEN
515 new_references.SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
516 ELSIF upper(Column_name) = 'ACAI_SEQUENCE_NUMBER' THEN
517 new_references.ACAI_SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
518 ELSIF upper(Column_name) = 'ATTENDANCE_PERCENTAGE' THEN
519 new_references.ATTENDANCE_PERCENTAGE := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
520 END IF;
521
522 IF upper(column_name) = 'ACAI_NOMINATED_COURSE_CD' OR COLUMN_NAME IS NULL THEN
523 IF new_references.ACAI_NOMINATED_COURSE_CD <> upper(NEW_REFERENCES.ACAI_NOMINATED_COURSE_CD) then
524 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
525 IGS_GE_MSG_STACK.ADD;
526 App_Exception.Raise_Exception ;
527 END IF;
528 END IF;
529 IF upper(column_name) = 'GOVT_TYPE_OF_ACTIVITY_CD' OR COLUMN_NAME IS NULL THEN
530 IF new_references.GOVT_TYPE_OF_ACTIVITY_CD <> upper(NEW_REFERENCES.GOVT_TYPE_OF_ACTIVITY_CD) then
531 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
532 IGS_GE_MSG_STACK.ADD;
533 App_Exception.Raise_Exception ;
534 END IF;
535 END IF;
536 IF upper(column_name) = 'SCA_COURSE_CD' OR COLUMN_NAME IS NULL THEN
537 IF new_references.SCA_COURSE_CD <> upper(NEW_REFERENCES.SCA_COURSE_CD) then
538 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
539 IGS_GE_MSG_STACK.ADD;
540 App_Exception.Raise_Exception ;
541 END IF;
542 END IF;
543 IF upper(column_name) = 'SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
544 IF new_references.SEQUENCE_NUMBER < 1 OR new_references.SEQUENCE_NUMBER > 999999 then
545 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
546 IGS_GE_MSG_STACK.ADD;
547 App_Exception.Raise_Exception ;
548 END IF;
549 END IF;
550 IF upper(column_name) = 'ACAI_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
551 IF new_references.ACAI_SEQUENCE_NUMBER < 1 OR new_references.ACAI_SEQUENCE_NUMBER > 999999 then
552 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
553 IGS_GE_MSG_STACK.ADD;
554 App_Exception.Raise_Exception ;
555 END IF;
556 END IF;
557 IF upper(column_name) = 'ATTENDANCE_PERCENTAGE' OR COLUMN_NAME IS NULL THEN
558 IF new_references.ATTENDANCE_PERCENTAGE < 1 OR new_references.ATTENDANCE_PERCENTAGE > 100 then
559 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
560 IGS_GE_MSG_STACK.ADD;
561 App_Exception.Raise_Exception ;
562 END IF;
563 END IF;
564 END Check_Constraints ;
565
566
567 PROCEDURE Check_Parent_Existance AS
568 BEGIN
569 IF (((old_references.person_id = new_references.person_id) AND
570 (old_references.acai_admission_appl_number = new_references.acai_admission_appl_number) AND
571 (old_references.acai_nominated_course_cd = new_references.acai_nominated_course_cd) AND
572 (old_references.acai_sequence_number = new_references.acai_sequence_number)) OR
573 ((new_references.person_id IS NULL) OR
574 (new_references.acai_admission_appl_number IS NULL) OR
575 (new_references.acai_nominated_course_cd IS NULL) OR
576 (new_references.acai_sequence_number IS NULL))) THEN
577 NULL;
578 ELSE
579 IF NOT IGS_AD_PS_APPL_INST_PKG.Get_PK_For_Validation (
580 new_references.person_id,
581 new_references.acai_admission_appl_number,
582 new_references.acai_nominated_course_cd,
583 new_references.acai_sequence_number
584 ) THEN
585 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
586 IGS_GE_MSG_STACK.ADD;
587 App_Exception.Raise_Exception;
588 END IF;
589 END IF;
590
591 IF (((old_references.govt_type_of_activity_cd = new_references.govt_type_of_activity_cd)) OR
592 ((new_references.govt_type_of_activity_cd IS NULL))) THEN
593 NULL;
594 ELSE
595 IF NOT IGS_RE_GV_TOA_CLS_CD_PKG.Get_PK_For_Validation (
596 new_references.govt_type_of_activity_cd
597 ) THEN
598 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
599 IGS_GE_MSG_STACK.ADD;
600 App_Exception.Raise_Exception;
601 END IF;
602 END IF;
603 IF (((old_references.person_id = new_references.person_id)) OR
604 ((new_references.person_id IS NULL))) THEN
605 NULL;
606 ELSE
607 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
608 new_references.person_id
609 ) THEN
610 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
611 IGS_GE_MSG_STACK.ADD;
612 App_Exception.Raise_Exception;
613 END IF;
614 END IF;
615 IF (((old_references.person_id = new_references.person_id) AND
616 (old_references.sca_course_cd = new_references.sca_course_cd)) OR
617 ((new_references.person_id IS NULL) OR
618 (new_references.sca_course_cd IS NULL))) THEN
619 NULL;
620 ELSE
621 IF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
622 new_references.person_id,
623 new_references.sca_course_cd
624 ) THEN
625 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
626 IGS_GE_MSG_STACK.ADD;
627 App_Exception.Raise_Exception;
628 END IF;
629 END IF;
630 END Check_Parent_Existance;
631
632 PROCEDURE Check_Child_Existance AS
633 BEGIN
634 IGS_RE_CDT_ATT_HIST_PKG.GET_FK_IGS_RE_CANDIDATURE (
635 old_references.person_id,
636 old_references.sequence_number
637 );
638 IGS_RE_CDT_FLD_OF_SY_PKG.GET_FK_IGS_RE_CANDIDATURE (
639 old_references.person_id,
640 old_references.sequence_number
641 );
642 IGS_RE_CAND_SEO_CLS_PKG.GET_FK_IGS_RE_CANDIDATURE (
643 old_references.person_id,
644 old_references.sequence_number
645 );
646 IGS_PR_MILESTONE_PKG.GET_FK_IGS_RE_CANDIDATURE (
647 old_references.person_id,
648 old_references.sequence_number
649 );
650 IGS_RE_SPRVSR_PKG.GET_FK_IGS_RE_CANDIDATURE (
651 old_references.person_id,
652 old_references.sequence_number
653 );
654 IGS_RE_SCHOLARSHIP_PKG.GET_FK_IGS_RE_CANDIDATURE (
655 old_references.person_id,
656 old_references.sequence_number
657 );
658 IGS_RE_THESIS_PKG.GET_FK_IGS_RE_CANDIDATURE (
659 old_references.person_id,
660 old_references.sequence_number
661 );
662 END Check_Child_Existance;
663
664 FUNCTION Get_PK_For_Validation (
665 x_person_id IN NUMBER,
666 x_sequence_number IN NUMBER
667 )
668 RETURN BOOLEAN
669 AS
670 CURSOR cur_rowid IS
671 SELECT rowid
672 FROM IGS_RE_CANDIDATURE_ALL
673 WHERE person_id = x_person_id
674 AND sequence_number = x_sequence_number
675 FOR UPDATE NOWAIT;
676 lv_rowid cur_rowid%RowType;
677 BEGIN
678 Open cur_rowid;
679 Fetch cur_rowid INTO lv_rowid;
680 IF (cur_rowid%FOUND) THEN
681 Close cur_rowid;
682 RETURN(TRUE);
683 ELSE
684 Close cur_rowid;
685 RETURN(FALSE);
686 END IF;
687 END Get_PK_For_Validation;
688
689 PROCEDURE GET_FK_IGS_AD_PS_APPL_INST (
690 x_person_id IN NUMBER,
691 x_admission_appl_number IN NUMBER,
692 x_nominated_course_cd IN VARCHAR2,
693 x_sequence_number IN NUMBER
694 ) AS
695 CURSOR cur_rowid IS
696 SELECT rowid
697 FROM IGS_RE_CANDIDATURE_ALL
698 WHERE person_id = x_person_id
699 AND acai_admission_appl_number = x_admission_appl_number
700 AND acai_nominated_course_cd = x_nominated_course_cd
701 AND acai_sequence_number = x_sequence_number ;
702 lv_rowid cur_rowid%RowType;
703 BEGIN
704 Open cur_rowid;
705 Fetch cur_rowid INTO lv_rowid;
706 IF (cur_rowid%FOUND) THEN
707 Close cur_rowid;
708 Fnd_Message.Set_Name ('IGS', 'IGS_RE_CA_ACAI_FK');
709 IGS_GE_MSG_STACK.ADD;
710 App_Exception.Raise_Exception;
711 Return;
712 END IF;
713 Close cur_rowid;
714 END GET_FK_IGS_AD_PS_APPL_INST;
715
716 PROCEDURE GET_FK_IGS_RE_GV_TOA_CLS_CD (
717 x_govt_toa_class_cd IN VARCHAR2
718 ) AS
719 CURSOR cur_rowid IS
720 SELECT rowid
721 FROM IGS_RE_CANDIDATURE_ALL
722 WHERE govt_type_of_activity_cd = x_govt_toa_class_cd ;
723 lv_rowid cur_rowid%RowType;
724 BEGIN
725 Open cur_rowid;
726 Fetch cur_rowid INTO lv_rowid;
727 IF (cur_rowid%FOUND) THEN
728 Close cur_rowid;
729 Fnd_Message.Set_Name ('IGS', 'IGS_RE_CA_GTCC_FK');
730 IGS_GE_MSG_STACK.ADD;
731 App_Exception.Raise_Exception;
732 Return;
733 END IF;
734 Close cur_rowid;
735 END GET_FK_IGS_RE_GV_TOA_CLS_CD;
736 PROCEDURE GET_FK_IGS_PE_PERSON (
737 x_person_id IN NUMBER
738 ) AS
739 CURSOR cur_rowid IS
740 SELECT rowid
741 FROM IGS_RE_CANDIDATURE_ALL
742 WHERE person_id = x_person_id ;
743 lv_rowid cur_rowid%RowType;
744 BEGIN
745 Open cur_rowid;
746 Fetch cur_rowid INTO lv_rowid;
747 IF (cur_rowid%FOUND) THEN
748 Close cur_rowid;
749 Fnd_Message.Set_Name ('IGS', 'IGS_RE_CA_PE_FK');
750 IGS_GE_MSG_STACK.ADD;
751 App_Exception.Raise_Exception;
752 Return;
753 END IF;
754 Close cur_rowid;
755 END GET_FK_IGS_PE_PERSON;
756
757 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
758 x_person_id IN NUMBER,
759 x_course_cd IN VARCHAR2
760 ) AS
761 CURSOR cur_rowid IS
762 SELECT rowid
763 FROM IGS_RE_CANDIDATURE_ALL
764 WHERE person_id = x_person_id
765 AND sca_course_cd = x_course_cd ;
766 lv_rowid cur_rowid%RowType;
767 BEGIN
768 Open cur_rowid;
769 Fetch cur_rowid INTO lv_rowid;
770 IF (cur_rowid%FOUND) THEN
771 Close cur_rowid;
772 Fnd_Message.Set_Name ('IGS', 'IGS_RE_CA_SCA_FK');
773 IGS_GE_MSG_STACK.ADD;
774 App_Exception.Raise_Exception;
775 Return;
776 END IF;
777 Close cur_rowid;
778 END GET_FK_IGS_EN_STDNT_PS_ATT;
779
780 PROCEDURE Before_DML (
781 p_action IN VARCHAR2,
782 x_rowid IN VARCHAR2 ,
783 x_industry_links IN VARCHAR2 ,
784 x_person_id IN NUMBER ,
785 x_sequence_number IN NUMBER ,
786 x_sca_course_cd IN VARCHAR2 ,
787 x_acai_admission_appl_number IN NUMBER ,
788 x_acai_nominated_course_cd IN VARCHAR2 ,
789 x_acai_sequence_number IN NUMBER ,
790 x_attendance_percentage IN NUMBER ,
791 x_govt_type_of_activity_cd IN VARCHAR2 ,
792 x_max_submission_dt IN DATE ,
793 x_min_submission_dt IN DATE ,
794 x_research_topic IN VARCHAR2 ,
795 x_creation_date IN DATE ,
796 x_created_by IN NUMBER ,
797 x_last_update_date IN DATE ,
798 x_last_updated_by IN NUMBER ,
799 x_last_update_login IN NUMBER ,
800 x_org_id IN NUMBER
801 ) AS
802 BEGIN
803 Set_Column_Values (
804 p_action,
805 x_rowid,
806 x_industry_links,
807 x_person_id,
808 x_sequence_number,
809 x_sca_course_cd,
810 x_acai_admission_appl_number,
811 x_acai_nominated_course_cd,
812 x_acai_sequence_number,
813 x_attendance_percentage,
814 x_govt_type_of_activity_cd,
815 x_max_submission_dt,
816 x_min_submission_dt,
817 x_research_topic,
818 x_creation_date,
819 x_created_by,
820 x_last_update_date,
821 x_last_updated_by,
822 x_last_update_login ,
823 x_org_id
824 );
825 IF (p_action = 'INSERT') THEN
826 -- Call all the procedures related to Before Insert.
827 BeforeRowInsertUpdate1 ( p_inserting => TRUE,
828 p_updating => FALSE,
829 p_deleting => FALSE );
830 BeforeRowInsertUpdateDelete2 ( p_inserting => TRUE,
831 p_updating => FALSE,
832 p_deleting => FALSE );
833
834 IF Get_PK_For_Validation (
835 new_references.person_id,
836 new_references.sequence_number
837 ) THEN
838
839 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
840 IGS_GE_MSG_STACK.ADD;
841 App_Exception.Raise_Exception;
842 END IF;
843 Check_Constraints;
844 Check_Parent_Existance;
845
846 ELSIF (p_action = 'UPDATE') THEN
847 -- Call all the procedures related to Before Update.
848 BeforeRowInsertUpdate1 (p_inserting => FALSE,
849 p_updating => TRUE,
850 p_deleting => FALSE);
851 BeforeRowInsertUpdateDelete2 ( p_inserting => FALSE,
852 p_updating => TRUE,
853 p_deleting => FALSE);
854 Check_Constraints;
855 Check_Parent_Existance;
856 ELSIF (p_action = 'DELETE') THEN
857 -- Call all the procedures related to Before Delete.
858 BeforeRowInsertUpdateDelete2 ( p_inserting => FALSE,
859 p_updating => FALSE,
860 p_deleting => TRUE );
861 Check_Child_Existance;
862 ELSIF (p_action = 'VALIDATE_INSERT') THEN
863 IF Get_PK_For_Validation (
864 new_references.person_id,
865 new_references.sequence_number
866 ) THEN
867
868 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
869 IGS_GE_MSG_STACK.ADD;
870 App_Exception.Raise_Exception;
871 END IF;
872 Check_Constraints;
873 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
874 Check_Constraints;
875 ELSIF (p_action = 'VALIDATE_DELETE') THEN
876 Check_Child_Existance;
877 END IF;
878 END Before_DML;
879
880 PROCEDURE After_DML (
881 p_action IN VARCHAR2,
882 x_rowid IN VARCHAR2
883 ) AS
884 BEGIN
885 l_rowid := x_rowid;
886 IF (p_action = 'INSERT') THEN
887 -- Call all the procedures related to After Insert.
888 AfterRowInsertUpdate3 ( p_inserting => TRUE,
889 p_updating => FALSE,
890 p_deleting => FALSE );
891 AfterStmtInsertUpdate5 (p_inserting => TRUE,
892 p_updating => FALSE,
893 p_deleting => FALSE );
894 ELSIF (p_action = 'UPDATE') THEN
895 -- Call all the procedures related to After Update.
896 AfterRowInsertUpdate3 ( p_inserting => FALSE,
897 p_updating => TRUE,
898 p_deleting => FALSE);
899 AfterRowUpdateDelete4 ( p_inserting => FALSE,
900 p_updating => TRUE,
901 p_deleting => FALSE);
902 AfterStmtInsertUpdate5 (p_inserting => FALSE,
903 p_updating => TRUE,
904 p_deleting => FALSE);
905 ELSIF (p_action = 'DELETE') THEN
906 -- Call all the procedures related to After Delete.
907 AfterRowUpdateDelete4 ( p_inserting => FALSE,
908 p_updating => FALSE,
909 p_deleting => TRUE );
910 END IF;
911 END After_DML;
912 procedure INSERT_ROW (
913 X_ROWID in out NOCOPY VARCHAR2,
914 X_PERSON_ID in NUMBER,
915 X_SEQUENCE_NUMBER in NUMBER,
916 X_SCA_COURSE_CD in VARCHAR2,
917 X_ACAI_ADMISSION_APPL_NUMBER in NUMBER,
918 X_ACAI_NOMINATED_COURSE_CD in VARCHAR2,
919 X_ACAI_SEQUENCE_NUMBER in NUMBER,
920 X_ATTENDANCE_PERCENTAGE in NUMBER,
921 X_GOVT_TYPE_OF_ACTIVITY_CD in VARCHAR2,
922 X_MAX_SUBMISSION_DT in DATE,
923 X_MIN_SUBMISSION_DT in DATE,
924 X_RESEARCH_TOPIC in VARCHAR2,
925 X_INDUSTRY_LINKS in VARCHAR2,
926 X_MODE in VARCHAR2,
927 X_ORG_ID in NUMBER
928 ) as
929 cursor C is select ROWID from IGS_RE_CANDIDATURE_ALL
930 where PERSON_ID = X_PERSON_ID
931 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
932 X_LAST_UPDATE_DATE DATE;
933 X_LAST_UPDATED_BY NUMBER;
934 X_LAST_UPDATE_LOGIN NUMBER;
935 X_REQUEST_ID NUMBER;
936 X_PROGRAM_ID NUMBER;
937 X_PROGRAM_APPLICATION_ID NUMBER;
938 X_PROGRAM_UPDATE_DATE DATE;
939 begin
940 X_LAST_UPDATE_DATE := SYSDATE;
941 if(X_MODE = 'I') then
942 X_LAST_UPDATED_BY := 1;
943 X_LAST_UPDATE_LOGIN := 0;
944 elsif (X_MODE IN ('R', 'S')) then
945 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
946 if X_LAST_UPDATED_BY is NULL then
947 X_LAST_UPDATED_BY := -1;
948 end if;
949 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
950 if X_LAST_UPDATE_LOGIN is NULL then
951 X_LAST_UPDATE_LOGIN := -1;
952 end if;
953 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
954 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
955 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
956 if (X_REQUEST_ID = -1) then
957 X_REQUEST_ID := NULL;
958 X_PROGRAM_ID := NULL;
959 X_PROGRAM_APPLICATION_ID := NULL;
960 X_PROGRAM_UPDATE_DATE := NULL;
961 else
962 X_PROGRAM_UPDATE_DATE := SYSDATE;
963 end if;
964 else
965 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
966 IGS_GE_MSG_STACK.ADD;
967 app_exception.raise_exception;
968 end if;
969 Before_DML (
970 p_action => 'INSERT',
971 x_rowid => X_ROWID,
972 x_industry_links => X_INDUSTRY_LINKS,
973 x_person_id => X_PERSON_ID,
974 x_sequence_number => X_SEQUENCE_NUMBER,
975 x_sca_course_cd => X_SCA_COURSE_CD,
976 x_acai_admission_appl_number => X_ACAI_ADMISSION_APPL_NUMBER,
977 x_acai_nominated_course_cd => X_ACAI_NOMINATED_COURSE_CD,
978 x_acai_sequence_number => X_ACAI_SEQUENCE_NUMBER,
979 x_attendance_percentage => X_ATTENDANCE_PERCENTAGE,
980 x_govt_type_of_activity_cd => X_GOVT_TYPE_OF_ACTIVITY_CD,
981 x_max_submission_dt => X_MAX_SUBMISSION_DT,
982 x_min_submission_dt => X_MIN_SUBMISSION_DT,
983 x_research_topic => X_RESEARCH_TOPIC,
984 x_creation_date => X_LAST_UPDATE_DATE,
985 x_created_by => X_LAST_UPDATED_BY ,
986 x_last_update_date => X_LAST_UPDATE_DATE,
987 x_last_updated_by => X_LAST_UPDATED_BY,
988 x_last_update_login => X_LAST_UPDATE_LOGIN,
989 x_org_id => igs_ge_gen_003.get_org_id
990 ) ;
991 IF (x_mode = 'S') THEN
992 igs_sc_gen_001.set_ctx('R');
993 END IF;
994 insert into IGS_RE_CANDIDATURE_ALL (
995 PERSON_ID,
996 SEQUENCE_NUMBER,
997 SCA_COURSE_CD,
998 ACAI_ADMISSION_APPL_NUMBER,
999 ACAI_NOMINATED_COURSE_CD,
1000 ACAI_SEQUENCE_NUMBER,
1001 ATTENDANCE_PERCENTAGE,
1002 GOVT_TYPE_OF_ACTIVITY_CD,
1003 MAX_SUBMISSION_DT,
1004 MIN_SUBMISSION_DT,
1005 RESEARCH_TOPIC,
1006 INDUSTRY_LINKS,
1007 CREATION_DATE,
1008 CREATED_BY,
1009 LAST_UPDATE_DATE,
1010 LAST_UPDATED_BY,
1011 LAST_UPDATE_LOGIN,
1012 REQUEST_ID,
1013 PROGRAM_ID,
1014 PROGRAM_APPLICATION_ID,
1015 PROGRAM_UPDATE_DATE,
1016 ORG_ID
1017 ) values (
1018 NEW_REFERENCES.PERSON_ID,
1019 NEW_REFERENCES.SEQUENCE_NUMBER,
1020 NEW_REFERENCES.SCA_COURSE_CD,
1021 NEW_REFERENCES.ACAI_ADMISSION_APPL_NUMBER,
1022 NEW_REFERENCES.ACAI_NOMINATED_COURSE_CD,
1023 NEW_REFERENCES.ACAI_SEQUENCE_NUMBER,
1024 NEW_REFERENCES.ATTENDANCE_PERCENTAGE,
1025 NEW_REFERENCES.GOVT_TYPE_OF_ACTIVITY_CD,
1026 NEW_REFERENCES.MAX_SUBMISSION_DT,
1027 NEW_REFERENCES.MIN_SUBMISSION_DT,
1028 NEW_REFERENCES.RESEARCH_TOPIC,
1029 NEW_REFERENCES.INDUSTRY_LINKS,
1030 X_LAST_UPDATE_DATE,
1031 X_LAST_UPDATED_BY,
1032 X_LAST_UPDATE_DATE,
1033 X_LAST_UPDATED_BY,
1034 X_LAST_UPDATE_LOGIN,
1035 X_REQUEST_ID,
1036 X_PROGRAM_ID,
1037 X_PROGRAM_APPLICATION_ID,
1038 X_PROGRAM_UPDATE_DATE,
1039 NEW_REFERENCES.ORG_ID
1040 );
1041 IF (x_mode = 'S') THEN
1042 igs_sc_gen_001.unset_ctx('R');
1043 END IF;
1044
1045 open c;
1046 fetch c into X_ROWID;
1047 if (c%notfound) then
1048 close c;
1049 raise no_data_found;
1050 end if;
1051 close c;
1052 After_DML (
1053 p_action => 'INSERT',
1054 x_rowid => 'X_ROWID'
1055 );
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1059 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1060 fnd_message.set_token ('ERR_CD', SQLCODE);
1061 igs_ge_msg_stack.add;
1062 igs_sc_gen_001.unset_ctx('R');
1063 app_exception.raise_exception;
1064 ELSE
1065 igs_sc_gen_001.unset_ctx('R');
1066 RAISE;
1067 END IF;
1068
1069 end INSERT_ROW;
1070 procedure LOCK_ROW (
1071 X_ROWID in VARCHAR2,
1072 X_PERSON_ID in NUMBER,
1073 X_SEQUENCE_NUMBER in NUMBER,
1074 X_SCA_COURSE_CD in VARCHAR2,
1075 X_ACAI_ADMISSION_APPL_NUMBER in NUMBER,
1076 X_ACAI_NOMINATED_COURSE_CD in VARCHAR2,
1077 X_ACAI_SEQUENCE_NUMBER in NUMBER,
1078 X_ATTENDANCE_PERCENTAGE in NUMBER,
1079 X_GOVT_TYPE_OF_ACTIVITY_CD in VARCHAR2,
1080 X_MAX_SUBMISSION_DT in DATE,
1081 X_MIN_SUBMISSION_DT in DATE,
1082 X_RESEARCH_TOPIC in VARCHAR2,
1083 X_INDUSTRY_LINKS in VARCHAR2
1084 ) as
1085 cursor c1 is select
1086 SCA_COURSE_CD,
1087 ACAI_ADMISSION_APPL_NUMBER,
1088 ACAI_NOMINATED_COURSE_CD,
1089 ACAI_SEQUENCE_NUMBER,
1090 ATTENDANCE_PERCENTAGE,
1091 GOVT_TYPE_OF_ACTIVITY_CD,
1092 MAX_SUBMISSION_DT,
1093 MIN_SUBMISSION_DT,
1094 RESEARCH_TOPIC,
1095 INDUSTRY_LINKS
1096 from IGS_RE_CANDIDATURE_ALL
1097 where ROWID = X_ROWID
1098 for update nowait;
1099 tlinfo c1%rowtype;
1100 begin
1101 open c1;
1102 fetch c1 into tlinfo;
1103 if (c1%notfound) then
1104 close c1;
1105 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1106 app_exception.raise_exception;
1107 return;
1108 end if;
1109 close c1;
1110 if ( ((tlinfo.SCA_COURSE_CD = X_SCA_COURSE_CD)
1111 OR ((tlinfo.SCA_COURSE_CD is null)
1112 AND (X_SCA_COURSE_CD is null)))
1113 AND ((tlinfo.ACAI_ADMISSION_APPL_NUMBER = X_ACAI_ADMISSION_APPL_NUMBER)
1114 OR ((tlinfo.ACAI_ADMISSION_APPL_NUMBER is null)
1115 AND (X_ACAI_ADMISSION_APPL_NUMBER is null)))
1116 AND ((tlinfo.ACAI_NOMINATED_COURSE_CD = X_ACAI_NOMINATED_COURSE_CD)
1117 OR ((tlinfo.ACAI_NOMINATED_COURSE_CD is null)
1118 AND (X_ACAI_NOMINATED_COURSE_CD is null)))
1119 AND ((tlinfo.ACAI_SEQUENCE_NUMBER = X_ACAI_SEQUENCE_NUMBER)
1120 OR ((tlinfo.ACAI_SEQUENCE_NUMBER is null)
1121 AND (X_ACAI_SEQUENCE_NUMBER is null)))
1122 AND ((tlinfo.ATTENDANCE_PERCENTAGE = X_ATTENDANCE_PERCENTAGE)
1123 OR ((tlinfo.ATTENDANCE_PERCENTAGE is null)
1124 AND (X_ATTENDANCE_PERCENTAGE is null)))
1125 AND ((tlinfo.GOVT_TYPE_OF_ACTIVITY_CD = X_GOVT_TYPE_OF_ACTIVITY_CD)
1126 OR ((tlinfo.GOVT_TYPE_OF_ACTIVITY_CD is null)
1127 AND (X_GOVT_TYPE_OF_ACTIVITY_CD is null)))
1128 AND ((tlinfo.MAX_SUBMISSION_DT = X_MAX_SUBMISSION_DT)
1129 OR ((tlinfo.MAX_SUBMISSION_DT is null)
1130 AND (X_MAX_SUBMISSION_DT is null)))
1131 AND ((tlinfo.MIN_SUBMISSION_DT = X_MIN_SUBMISSION_DT)
1132 OR ((tlinfo.MIN_SUBMISSION_DT is null)
1133 AND (X_MIN_SUBMISSION_DT is null)))
1134 AND ((tlinfo.RESEARCH_TOPIC = X_RESEARCH_TOPIC)
1135 OR ((tlinfo.RESEARCH_TOPIC is null)
1136 AND (X_RESEARCH_TOPIC is null)))
1137 AND ((tlinfo.INDUSTRY_LINKS = X_INDUSTRY_LINKS)
1138 OR ((tlinfo.INDUSTRY_LINKS is null)
1139 AND (X_INDUSTRY_LINKS is null)))
1140 ) then
1141 null;
1142 else
1143 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1144 app_exception.raise_exception;
1145 end if;
1146 return;
1147 end LOCK_ROW;
1148 procedure UPDATE_ROW (
1149 X_ROWID in VARCHAR2,
1150 X_PERSON_ID in NUMBER,
1151 X_SEQUENCE_NUMBER in NUMBER,
1152 X_SCA_COURSE_CD in VARCHAR2,
1153 X_ACAI_ADMISSION_APPL_NUMBER in NUMBER,
1154 X_ACAI_NOMINATED_COURSE_CD in VARCHAR2,
1155 X_ACAI_SEQUENCE_NUMBER in NUMBER,
1156 X_ATTENDANCE_PERCENTAGE in NUMBER,
1157 X_GOVT_TYPE_OF_ACTIVITY_CD in VARCHAR2,
1158 X_MAX_SUBMISSION_DT in DATE,
1159 X_MIN_SUBMISSION_DT in DATE,
1160 X_RESEARCH_TOPIC in VARCHAR2,
1161 X_INDUSTRY_LINKS in VARCHAR2,
1162 X_MODE in VARCHAR2
1163 ) as
1164 X_LAST_UPDATE_DATE DATE;
1165 X_LAST_UPDATED_BY NUMBER;
1166 X_LAST_UPDATE_LOGIN NUMBER;
1167 X_REQUEST_ID NUMBER;
1168 X_PROGRAM_ID NUMBER;
1169 X_PROGRAM_APPLICATION_ID NUMBER;
1170 X_PROGRAM_UPDATE_DATE DATE;
1171 begin
1172 X_LAST_UPDATE_DATE := SYSDATE;
1173 if(X_MODE = 'I') then
1174 X_LAST_UPDATED_BY := 1;
1175 X_LAST_UPDATE_LOGIN := 0;
1176 elsif (X_MODE IN ('R', 'S')) then
1177 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1178 if X_LAST_UPDATED_BY is NULL then
1179 X_LAST_UPDATED_BY := -1;
1180 end if;
1181 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1182 if X_LAST_UPDATE_LOGIN is NULL then
1183 X_LAST_UPDATE_LOGIN := -1;
1184 end if;
1185 else
1186 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1187 IGS_GE_MSG_STACK.ADD;
1188 app_exception.raise_exception;
1189 end if;
1190 Before_DML (
1191 p_action => 'UPDATE',
1192 x_rowid => X_ROWID,
1193 x_industry_links => X_INDUSTRY_LINKS,
1194 x_person_id => X_PERSON_ID,
1195 x_sequence_number => X_SEQUENCE_NUMBER,
1196 x_sca_course_cd => X_SCA_COURSE_CD,
1197 x_acai_admission_appl_number => X_ACAI_ADMISSION_APPL_NUMBER,
1198 x_acai_nominated_course_cd => X_ACAI_NOMINATED_COURSE_CD,
1199 x_acai_sequence_number => X_ACAI_SEQUENCE_NUMBER,
1200 x_attendance_percentage => X_ATTENDANCE_PERCENTAGE,
1201 x_govt_type_of_activity_cd => X_GOVT_TYPE_OF_ACTIVITY_CD,
1202 x_max_submission_dt => X_MAX_SUBMISSION_DT,
1203 x_min_submission_dt => X_MIN_SUBMISSION_DT,
1204 x_research_topic => X_RESEARCH_TOPIC,
1205 x_creation_date => X_LAST_UPDATE_DATE,
1206 x_created_by => X_LAST_UPDATED_BY ,
1207 x_last_update_date => X_LAST_UPDATE_DATE,
1208 x_last_updated_by => X_LAST_UPDATED_BY,
1209 x_last_update_login => X_LAST_UPDATE_LOGIN
1210 ) ;
1211 if (X_MODE IN ('R', 'S')) then
1212 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1213 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1214 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1215 if (X_REQUEST_ID = -1) then
1216 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1217 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
1218 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1219 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1220 else
1221 X_PROGRAM_UPDATE_DATE := SYSDATE;
1222 end if;
1223 end if;
1224 IF (x_mode = 'S') THEN
1225 igs_sc_gen_001.set_ctx('R');
1226 END IF;
1227 update IGS_RE_CANDIDATURE_ALL set
1228 SCA_COURSE_CD = NEW_REFERENCES.SCA_COURSE_CD,
1229 ACAI_ADMISSION_APPL_NUMBER = NEW_REFERENCES.ACAI_ADMISSION_APPL_NUMBER,
1230 ACAI_NOMINATED_COURSE_CD = NEW_REFERENCES.ACAI_NOMINATED_COURSE_CD,
1231 ACAI_SEQUENCE_NUMBER = NEW_REFERENCES.ACAI_SEQUENCE_NUMBER,
1232 ATTENDANCE_PERCENTAGE = NEW_REFERENCES.ATTENDANCE_PERCENTAGE,
1233 GOVT_TYPE_OF_ACTIVITY_CD = NEW_REFERENCES.GOVT_TYPE_OF_ACTIVITY_CD,
1234 MAX_SUBMISSION_DT = NEW_REFERENCES.MAX_SUBMISSION_DT,
1235 MIN_SUBMISSION_DT = NEW_REFERENCES.MIN_SUBMISSION_DT,
1236 RESEARCH_TOPIC = NEW_REFERENCES.RESEARCH_TOPIC,
1237 INDUSTRY_LINKS = NEW_REFERENCES.INDUSTRY_LINKS,
1238 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1239 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1240 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1241 REQUEST_ID = X_REQUEST_ID,
1242 PROGRAM_ID = X_PROGRAM_ID,
1243 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1244 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1245 where ROWID = X_ROWID;
1246 if (sql%notfound) then
1247 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1248 igs_ge_msg_stack.add;
1249 igs_sc_gen_001.unset_ctx('R');
1250 app_exception.raise_exception;
1251 end if;
1252 IF (x_mode = 'S') THEN
1253 igs_sc_gen_001.unset_ctx('R');
1254 END IF;
1255
1256 After_DML (
1257 p_action => 'UPDATE',
1258 x_rowid => 'X_ROWID'
1259 );
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 IF (SQLCODE = (-28115)) THEN
1263 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1264 fnd_message.set_token ('ERR_CD', SQLCODE);
1265 igs_ge_msg_stack.add;
1266 igs_sc_gen_001.unset_ctx('R');
1267 app_exception.raise_exception;
1268 ELSE
1269 igs_sc_gen_001.unset_ctx('R');
1270 RAISE;
1271 END IF;
1272
1273 end UPDATE_ROW;
1274 procedure ADD_ROW (
1275 X_ROWID in out NOCOPY VARCHAR2,
1276 X_PERSON_ID in NUMBER,
1277 X_SEQUENCE_NUMBER in NUMBER,
1278 X_SCA_COURSE_CD in VARCHAR2,
1279 X_ACAI_ADMISSION_APPL_NUMBER in NUMBER,
1280 X_ACAI_NOMINATED_COURSE_CD in VARCHAR2,
1281 X_ACAI_SEQUENCE_NUMBER in NUMBER,
1282 X_ATTENDANCE_PERCENTAGE in NUMBER,
1283 X_GOVT_TYPE_OF_ACTIVITY_CD in VARCHAR2,
1284 X_MAX_SUBMISSION_DT in DATE,
1285 X_MIN_SUBMISSION_DT in DATE,
1286 X_RESEARCH_TOPIC in VARCHAR2,
1287 X_INDUSTRY_LINKS in VARCHAR2,
1288 X_MODE in VARCHAR2 ,
1289 X_ORG_ID in NUMBER
1290 ) as
1291 cursor c1 is select rowid from IGS_RE_CANDIDATURE_ALL
1292 where PERSON_ID = X_PERSON_ID
1293 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1294 ;
1295 begin
1296 open c1;
1297 fetch c1 into X_ROWID;
1298 if (c1%notfound) then
1299 close c1;
1300 INSERT_ROW (
1301 X_ROWID,
1302 X_PERSON_ID,
1303 X_SEQUENCE_NUMBER,
1304 X_SCA_COURSE_CD,
1305 X_ACAI_ADMISSION_APPL_NUMBER,
1306 X_ACAI_NOMINATED_COURSE_CD,
1307 X_ACAI_SEQUENCE_NUMBER,
1308 X_ATTENDANCE_PERCENTAGE,
1309 X_GOVT_TYPE_OF_ACTIVITY_CD,
1310 X_MAX_SUBMISSION_DT,
1311 X_MIN_SUBMISSION_DT,
1312 X_RESEARCH_TOPIC,
1313 X_INDUSTRY_LINKS,
1314 X_MODE,
1315 X_ORG_ID);
1316 return;
1317 end if;
1318 close c1;
1319 UPDATE_ROW (
1320 X_ROWID,
1321 X_PERSON_ID,
1322 X_SEQUENCE_NUMBER,
1323 X_SCA_COURSE_CD,
1324 X_ACAI_ADMISSION_APPL_NUMBER,
1325 X_ACAI_NOMINATED_COURSE_CD,
1326 X_ACAI_SEQUENCE_NUMBER,
1327 X_ATTENDANCE_PERCENTAGE,
1328 X_GOVT_TYPE_OF_ACTIVITY_CD,
1329 X_MAX_SUBMISSION_DT,
1330 X_MIN_SUBMISSION_DT,
1331 X_RESEARCH_TOPIC,
1332 X_INDUSTRY_LINKS,
1333 X_MODE );
1334 end ADD_ROW;
1335 procedure DELETE_ROW (
1336 X_ROWID in VARCHAR2,
1337 x_mode IN VARCHAR2
1338 ) as
1339 begin
1340 Before_DML (
1341 p_action => 'DELETE',
1342 x_rowid => X_ROWID
1343 );
1344 IF (x_mode = 'S') THEN
1345 igs_sc_gen_001.set_ctx('R');
1346 END IF;
1347 delete from IGS_RE_CANDIDATURE_ALL
1348 where ROWID = X_ROWID;
1349 if (sql%notfound) then
1350 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1351 igs_ge_msg_stack.add;
1352 igs_sc_gen_001.unset_ctx('R');
1353 app_exception.raise_exception;
1354 end if;
1355 IF (x_mode = 'S') THEN
1356 igs_sc_gen_001.unset_ctx('R');
1357 END IF;
1358
1359 After_DML (
1360 p_action => 'DELETE',
1361 x_rowid => X_ROWID
1362 );
1363 end DELETE_ROW;
1364 end IGS_RE_CANDIDATURE_PKG;