1 package body IGS_CA_INST_REL_PKG AS
2 /* $Header: IGSCI13B.pls 120.0 2005/06/02 03:52:17 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_CA_INST_REL%RowType;
5 new_references IGS_CA_INST_REL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_sub_cal_type IN VARCHAR2 DEFAULT NULL,
11 x_sub_ci_sequence_number IN NUMBER DEFAULT NULL,
12 x_sup_cal_type IN VARCHAR2 DEFAULT NULL,
13 x_sup_ci_sequence_number IN NUMBER DEFAULT NULL,
14 x_load_research_percentage IN NUMBER DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21
22 CURSOR cur_old_ref_values IS
23 SELECT *
24 FROM IGS_CA_INST_REL
25 WHERE rowid = x_rowid;
26
27 BEGIN
28
29 l_rowid := x_rowid;
30
31 -- Code for setting the Old and New Reference Values.
32 -- Populate Old Values.
33 Open cur_old_ref_values;
34 Fetch cur_old_ref_values INTO old_references;
35 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
36 Close cur_old_ref_values;
37 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39 App_Exception.Raise_Exception;
40 Return;
41 END IF;
42 Close cur_old_ref_values;
43
44 -- Populate New Values.
45 new_references.sub_cal_type := x_sub_cal_type;
46 new_references.sub_ci_sequence_number := x_sub_ci_sequence_number;
47 new_references.sup_cal_type := x_sup_cal_type;
48 new_references.sup_ci_sequence_number := x_sup_ci_sequence_number;
49 new_references.load_research_percentage := x_load_research_percentage;
50 IF (p_action = 'UPDATE') THEN
51 new_references.creation_date := old_references.creation_date;
52 new_references.created_by := old_references.created_by;
53 ELSE
54 new_references.creation_date := x_creation_date;
55 new_references.created_by := x_created_by;
56 END IF;
57 new_references.last_update_date := x_last_update_date;
58 new_references.last_updated_by := x_last_updated_by;
59 new_references.last_update_login := x_last_update_login;
60
61 END Set_Column_Values;
62
63 -- Trigger description :-
64 -- "OSS_TST".trg_cir_as_i
65 -- AFTER INSERT
66 -- ON IGS_CA_INST_REL
67
68 PROCEDURE BeforeRowInsertUpdateDelete(
69 p_inserting IN BOOLEAN DEFAULT FALSE,
70 p_updating IN BOOLEAN DEFAULT FALSE,
71 p_deleting IN BOOLEAN DEFAULT FALSE
72 ) AS
73 /******************************************************************
74 Created By : schodava
75 Date Created By : 22-Jan-2002
76 Purpose : Enh # 2187247
77 Validates a one-to-one relation only between
78 a Fee and Load calendar instance.
79 Prevents delete of a FCI-LCI relation
80 if used in FAM module, FTCI or FCCI
81 Known limitations,
82 enhancements,
83 remarks :
84 Change History
85 Who When What
86 smvk 05-Feb-2002 Added call to IGS_FI_CREDITS_PKG.GET_FK_IGS_CA_INST_2
87 This is as per new Application Hierarchicy Compliance DLD
88 Enhancement Bug No. 2191470
89 ******************************************************************/
90 cst_load CONSTANT VARCHAR2(10):= 'LOAD';
91 cst_fee CONSTANT VARCHAR2(10):= 'FEE';
92 l_c_sup_cat igs_ca_type.s_cal_cat%TYPE;
93
94 CURSOR c_cat(cp_cal_type IN igs_ca_type.cal_type%TYPE) IS
95 SELECT s_cal_cat
96 FROM igs_ca_type
97 WHERE cal_type = cp_cal_type;
98
99 CURSOR c_fci_lci(cp_sup_cal_type IN igs_ca_inst.cal_type%TYPE,
100 cp_sup_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
101 cp_sub_cal_type IN igs_ca_inst.cal_type%TYPE,
102 cp_sub_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE) IS
103 SELECT 'x'
104 FROM igs_ca_inst_rel cir,
105 igs_ca_type ct1,
106 igs_ca_type ct2
107 WHERE cir.sub_cal_type = ct1.cal_type
108 AND ct1.s_cal_cat = cst_load
109 AND cir.sup_cal_type = ct2.cal_type
110 AND ct2.s_cal_cat = cst_fee
111 AND ((cir.sup_cal_type = cp_sup_cal_type
112 AND cir.sup_ci_sequence_number = cp_sup_ci_sequence_number)
113 OR (cir.sub_cal_type = cp_sub_cal_type
114 AND cir.sub_ci_sequence_number = cp_sub_ci_sequence_number))
115 AND cir.rowid <> NVL(l_rowid,'0');
116
117 BEGIN
118
119 IF p_inserting or p_updating THEN
120 -- Allows only one to one relation between a Fee Cal Instance
121 -- and a Load Cal Instance
122 FOR l_c_cat IN c_cat(new_references.sup_cal_type) LOOP
123 l_c_sup_cat := l_c_cat.s_cal_cat;
124 END LOOP;
125 FOR l_c_cat IN c_cat(new_references.sub_cal_type) LOOP
126 IF l_c_sup_cat = cst_fee AND
127 l_c_cat.s_cal_cat = cst_load THEN
128 FOR l_c_fci_lci IN c_fci_lci(new_references.sup_cal_type,
129 new_references.sup_ci_sequence_number,
130 new_references.sub_cal_type,
131 new_references.sub_ci_sequence_number) LOOP
132 FND_MESSAGE.SET_NAME('IGS','IGS_FI_FCI_LCI_ONE_REL');
133 IGS_GE_MSG_STACK.ADD;
134 APP_EXCEPTION.RAISE_EXCEPTION;
135 END LOOP;
136 END IF;
137 END LOOP;
138 END IF;
139
140 IF p_deleting THEN
141
142 -- Prevents delete of a Fee Cal Instance relation if it is used in the FTCI table
143 IGS_FI_F_TYP_CA_INST_PKG.GET_FK_IGS_CA_INST (
144 old_references.sup_cal_type,
145 old_references.sup_ci_sequence_number
146 );
147
148 -- Prevents delete of a Fee Cal Instance relation if it is used in the FCCI table
149 IGS_FI_F_CAT_CA_INST_PKG.GET_FK_IGS_CA_INST (
150 old_references.sup_cal_type,
151 old_references.sup_ci_sequence_number
152 );
153
154 -- Prevents delete if Fee Cal Instance is used in igs_fi_credits_all table Bug No. 2191470
155 IGS_FI_CREDITS_PKG.GET_FK_IGS_CA_INST_2(
156 old_references.sup_cal_type,
157 old_references.sup_ci_sequence_number
158 );
159
160 END IF;
161
162 END BeforeRowInsertUpdateDelete;
163
164 PROCEDURE AfterStmtInsert2(
165 p_inserting IN BOOLEAN DEFAULT FALSE,
166 p_updating IN BOOLEAN DEFAULT FALSE,
167 p_deleting IN BOOLEAN DEFAULT FALSE
168 ) AS
169 v_message_name varchar2(30);
170 BEGIN
171 -- Validation routine calls.
172 IF p_inserting THEN
173 -- Validate superior/sub-ordinate calendar instance relationship
174 IF IGS_CA_VAL_CIR.calp_val_cir_ci (new_references.sub_cal_type,
175 new_references.sub_ci_sequence_number,
176 new_references.sup_cal_type,
177 new_references.sup_ci_sequence_number,
178 v_message_name) = FALSE
179 THEN
180 Fnd_Message.Set_Name('IGS',v_message_name);
181 IGS_GE_MSG_STACK.ADD;
182 APP_EXCEPTION.RAISE_EXCEPTION;
183 END IF;
184 END IF;
185 END AfterStmtInsert2;
186
187 FUNCTION Check_acad_adm_cal_rel(p_rowid IN ROWID )
188 RETURN BOOLEAN AS
189 CURSOR c_acad_adm_rel IS
190 SELECT 'X'
191 FROM igs_ca_inst_rel a ,
192 igs_ca_type b ,
193 igs_ca_type c ,
194 igs_ca_inst d ,
195 igs_ca_inst e ,
196 igs_ca_stat f,
197 igs_ca_stat g
198 WHERE a.rowid = p_rowid
199 AND a.sub_cal_type = b.cal_type
200 AND b.s_cal_cat = 'ADMISSION'
201 AND a.sup_cal_type = c.cal_type
202 AND c.s_cal_cat = 'ACADEMIC'
203 AND a.sub_cal_type = d.cal_type
204 AND a.sub_ci_sequence_number = d.sequence_number
205 AND f.s_cal_status = 'ACTIVE'
206 AND d.cal_status = f.cal_status
207 AND a.sup_cal_type = e.cal_type
208 AND a.sup_ci_sequence_number = e.sequence_number
209 AND g.s_cal_status = 'ACTIVE'
210 AND e.cal_status = g.cal_status;
211 l_c_acad_adm_rel VARCHAR2(1);
212 BEGIN
213 OPEN c_acad_adm_rel;
214 FETCH c_acad_adm_rel INTO l_c_acad_adm_rel;
215 IF c_acad_adm_rel%FOUND THEN
216 RETURN TRUE;
217 ELSE
218 RETURN FALSE;
219 END IF;
220 CLOSE c_acad_adm_rel;
221 END Check_acad_adm_cal_rel;
222
223 PROCEDURE Check_Constraints (
224 Column_Name IN VARCHAR2 DEFAULT NULL,
225 Column_Value IN VARCHAR2 DEFAULT NULL
226 ) AS
227 BEGIN
228 IF Column_Name is NULL THEN
229 NULL;
230 ELSIF upper(Column_Name) = 'LOAD_RESEARCH_PERCENTAGE' then
231 new_references.load_research_percentage := igs_ge_number.to_num(Column_Value);
232 ELSIF upper(Column_Name) = 'SUB_CAL_TYPE' then
233 new_references.sub_cal_type := Column_Value;
234 ELSIF upper(Column_Name) = 'SUP_CAL_TYPE' then
235 new_references.sup_cal_type := Column_Value;
236 END IF;
237
238 IF upper(Column_Name) = 'LOAD_RESEARCH_PERCENTAGE' OR
239 column_name is NULL THEN
240 IF new_references.load_research_percentage < 000.01 OR new_references.load_research_percentage > 100.00 THEN
241 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
242 IGS_GE_MSG_STACK.ADD;
243 App_Exception.Raise_Exception;
244 END IF;
245 END IF;
246 IF upper(Column_Name) = 'SUB_CAL_TYPE' OR
247 column_name is NULL THEN
248 IF new_references.sub_cal_type <> UPPER(new_references.sub_cal_type) THEN
249 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
250 IGS_GE_MSG_STACK.ADD;
251 App_Exception.Raise_Exception;
252 END IF;
253 END IF;
254 IF upper(Column_Name) = 'SUP_CAL_TYPE' OR
255 column_name is NULL THEN
256 IF new_references.sup_cal_type <> UPPER(new_references.sup_cal_type) THEN
257 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
258 IGS_GE_MSG_STACK.ADD;
259 App_Exception.Raise_Exception;
260 END IF;
261 END IF;
262 END Check_Constraints;
263
264 PROCEDURE Check_Parent_Existance AS
265 BEGIN
266
267 IF (((old_references.sub_cal_type = new_references.sub_cal_type) AND
268 (old_references.sub_ci_sequence_number = new_references.sub_ci_sequence_number)) OR
269 ((new_references.sub_cal_type IS NULL) OR
270 (new_references.sub_ci_sequence_number IS NULL))) THEN
271 NULL;
272 ELSIF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
273 new_references.sub_cal_type,
274 new_references.sub_ci_sequence_number
275 ) THEN
276 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
277 IGS_GE_MSG_STACK.ADD;
278 App_Exception.Raise_Exception;
279 END IF;
280
281 IF (((old_references.sup_cal_type = new_references.sup_cal_type) AND
282 (old_references.sup_ci_sequence_number = new_references.sup_ci_sequence_number)) OR
283 ((new_references.sup_cal_type IS NULL) OR
284 (new_references.sup_ci_sequence_number IS NULL))) THEN
285 NULL;
286 ELSIF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
287 new_references.sup_cal_type,
288 new_references.sup_ci_sequence_number
289 ) THEN
290 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
291 IGS_GE_MSG_STACK.ADD;
292 App_Exception.Raise_Exception;
293 END IF;
294
295 END Check_Parent_Existance;
296
297 PROCEDURE Check_Child_Existance AS
298 BEGIN
299
300 IGS_AD_APPL_PKG.GET_FK_IGS_CA_INST_REL (
301 old_references.sub_cal_type,
302 old_references.sub_ci_sequence_number,
303 old_references.sup_cal_type,
304 old_references.sup_ci_sequence_number
305 );
306
307 IF NVL(fnd_profile.value('IGS_RECRUITING_ENABLED'), 'N') = 'Y' THEN
308 EXECUTE IMMEDIATE
309 'begin IGR_I_APPL_PKG.GET_FK_IGS_CA_INST_REL ( :1, :2, :3, :4); end;'
310 USING old_references.sub_cal_type,
311 old_references.sub_ci_sequence_number,
312 old_references.sup_cal_type,
313 old_references.sup_ci_sequence_number;
314 END IF;
315
316 END Check_Child_Existance;
317
318 FUNCTION Get_PK_For_Validation (
319 x_sub_cal_type IN VARCHAR2,
320 x_sub_ci_sequence_number IN NUMBER,
321 x_sup_cal_type IN VARCHAR2,
322 x_sup_ci_sequence_number IN NUMBER
323 ) RETURN BOOLEAN AS
324
325 CURSOR cur_rowid IS
326 SELECT rowid
327 FROM IGS_CA_INST_REL
328 WHERE sub_cal_type = x_sub_cal_type
329 AND sub_ci_sequence_number = x_sub_ci_sequence_number
330 AND sup_cal_type = x_sup_cal_type
331 AND sup_ci_sequence_number = x_sup_ci_sequence_number;
332
333 lv_rowid cur_rowid%RowType;
334
335 BEGIN
336
337 Open cur_rowid;
338 Fetch cur_rowid INTO lv_rowid;
339 IF (cur_rowid%FOUND) THEN
340 Close cur_rowid;
341 Return (TRUE);
342 ELSE
343 Close cur_rowid;
344 Return (FALSE);
345 END IF;
346 END Get_PK_For_Validation;
347
348 PROCEDURE GET_FK_IGS_CA_INST (
349 x_cal_type IN VARCHAR2,
350 x_sequence_number IN NUMBER
351 ) AS
352
353 CURSOR cur_rowid IS
354 SELECT rowid
355 FROM IGS_CA_INST_REL
356 WHERE (sub_cal_type = x_cal_type
357 AND sub_ci_sequence_number = x_sequence_number)
358 OR (sup_cal_type = x_cal_type
359 AND sup_ci_sequence_number = x_sequence_number);
360 lv_rowid cur_rowid%RowType;
361
362 BEGIN
363
364 Open cur_rowid;
365 Fetch cur_rowid INTO lv_rowid;
366 IF (cur_rowid%FOUND) THEN
367 Close cur_rowid;
368 Fnd_Message.Set_Name ('IGS', 'IGS_CA_CIR_CI_FK');
369 IGS_GE_MSG_STACK.ADD;
370 App_Exception.Raise_Exception;
371 Return;
372 END IF;
373 Close cur_rowid;
374
375 END GET_FK_IGS_CA_INST;
376
377
378
379 PROCEDURE Before_DML (
380 p_action IN VARCHAR2,
381 x_rowid IN VARCHAR2 DEFAULT NULL,
382 x_sub_cal_type IN VARCHAR2 DEFAULT NULL,
383 x_sub_ci_sequence_number IN NUMBER DEFAULT NULL,
384 x_sup_cal_type IN VARCHAR2 DEFAULT NULL,
385 x_sup_ci_sequence_number IN NUMBER DEFAULT NULL,
386 x_load_research_percentage IN NUMBER DEFAULT NULL,
387 x_creation_date IN DATE DEFAULT NULL,
388 x_created_by IN NUMBER DEFAULT NULL,
389 x_last_update_date IN DATE DEFAULT NULL,
390 x_last_updated_by IN NUMBER DEFAULT NULL,
391 x_last_update_login IN NUMBER DEFAULT NULL
392 ) AS
393 /******************************************************************
394 Change History
395 Who When What
396 schodava 4-2-2002 Enh # 2187247
397 Added call to BeforeRowInsertUpdateDelete
398 kpadiyar 06-JAN-2002 Stop delete if SUP-CAL = Academic and SUB-CAL = Admission
399 and both calendars have active status.
400 ******************************************************************/
401 BEGIN
402
403 Set_Column_Values (
404 p_action,
405 x_rowid,
406 x_sub_cal_type,
407 x_sub_ci_sequence_number,
408 x_sup_cal_type,
409 x_sup_ci_sequence_number,
410 x_load_research_percentage,
411 x_creation_date,
412 x_created_by,
413 x_last_update_date,
414 x_last_updated_by,
415 x_last_update_login
416 );
417
418 IF (p_action = 'INSERT') THEN
419 -- Call all the procedures related to Before Insert.
420 IF Get_PK_For_Validation (
421 new_references.sub_cal_type,
422 new_references.sub_ci_sequence_number,
423 new_references.sup_cal_type,
424 new_references.sup_ci_sequence_number ) THEN
425 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
426 IGS_GE_MSG_STACK.ADD;
427 App_Exception.Raise_Exception;
428 END IF;
429 BeforeRowInsertUpdateDelete(p_inserting => TRUE);
430 Check_Constraints;
431 Check_Parent_Existance;
432 ELSIF (p_action = 'UPDATE') THEN
433 -- Call all the procedures related to Before Update.
434 BeforeRowInsertUpdateDelete(p_updating => TRUE);
435 Check_Constraints;
436 Check_Parent_Existance;
437 ELSIF (p_action = 'DELETE') THEN
438
439 -- Call all the procedures related to Before Delete.
440 BeforeRowInsertUpdateDelete(p_deleting => TRUE);
441 Check_Child_Existance;
442 ELSIF (p_action = 'VALIDATE_INSERT') THEN
443 -- Call all the procedures related to Before Insert.
444 IF Get_PK_For_Validation (
445 new_references.sub_cal_type,
446 new_references.sub_ci_sequence_number,
447 new_references.sup_cal_type,
448 new_references.sup_ci_sequence_number ) THEN
449 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
450 IGS_GE_MSG_STACK.ADD;
451 App_Exception.Raise_Exception;
452 END IF;
453 Check_Constraints;
454 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
455 Check_Constraints;
456 ELSIF (p_action = 'VALIDATE_DELETE') THEN
457 -- Stop delete if SUP-CAL = Academic and SUB-CAL = Admission and both calendars have active status.
458 IF Check_acad_adm_cal_rel (p_rowid => x_rowid) THEN
459 Fnd_Message.Set_Name ('IGS', 'IGS_CA_REL_DEL_NOT');
460 IGS_GE_MSG_STACK.ADD;
461 App_Exception.Raise_Exception;
462 END IF;
463 BeforeRowInsertUpdateDelete(p_deleting => TRUE);
464 Check_Child_Existance;
465 END IF;
466
467 END Before_DML;
468
469 PROCEDURE After_DML (
470 p_action IN VARCHAR2,
471 x_rowid IN VARCHAR2
472 ) AS
473 BEGIN
474
475 l_rowid := x_rowid;
476
477 IF (p_action = 'INSERT') THEN
478 -- Call all the procedures related to After Insert.
479 AfterStmtInsert2 ( p_inserting => TRUE );
480 ELSIF (p_action = 'UPDATE') THEN
481 -- Call all the procedures related to After Update.
482 Null;
483 ELSIF (p_action = 'DELETE') THEN
484 -- Call all the procedures related to After Delete.
485 Null;
486 END IF;
487
488 END After_DML;
489 procedure INSERT_ROW (
490 X_ROWID in out NOCOPY VARCHAR2,
491 X_SUB_CAL_TYPE in VARCHAR2,
492 X_SUB_CI_SEQUENCE_NUMBER in NUMBER,
493 X_SUP_CAL_TYPE in VARCHAR2,
494 X_SUP_CI_SEQUENCE_NUMBER in NUMBER,
495 X_LOAD_RESEARCH_PERCENTAGE in NUMBER,
496 X_MODE in VARCHAR2 default 'R'
497 ) AS
498 cursor C is select ROWID from IGS_CA_INST_REL
499 where SUB_CAL_TYPE = X_SUB_CAL_TYPE
500 and SUB_CI_SEQUENCE_NUMBER = X_SUB_CI_SEQUENCE_NUMBER
501 and SUP_CAL_TYPE = X_SUP_CAL_TYPE
502 and SUP_CI_SEQUENCE_NUMBER = X_SUP_CI_SEQUENCE_NUMBER;
503 X_LAST_UPDATE_DATE DATE;
504 X_LAST_UPDATED_BY NUMBER;
505 X_LAST_UPDATE_LOGIN NUMBER;
506 begin
507 X_LAST_UPDATE_DATE := SYSDATE;
508 if(X_MODE = 'I') then
509 X_LAST_UPDATED_BY := 1;
510 X_LAST_UPDATE_LOGIN := 0;
511 elsif (X_MODE = 'R') then
512 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
513 if X_LAST_UPDATED_BY is NULL then
514 X_LAST_UPDATED_BY := -1;
515 end if;
516 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
517 if X_LAST_UPDATE_LOGIN is NULL then
518 X_LAST_UPDATE_LOGIN := -1;
519 end if;
520 else
521 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
522 IGS_GE_MSG_STACK.ADD;
523 app_exception.raise_exception;
524 end if;
525 Before_DML (
526 p_action =>'INSERT',
527 x_rowid =>X_ROWID,
528 x_sub_cal_type =>X_SUB_CAL_TYPE,
529 x_sub_ci_sequence_number =>X_SUB_CI_SEQUENCE_NUMBER,
530 x_sup_cal_type =>X_SUP_CAL_TYPE,
531 x_sup_ci_sequence_number =>X_SUP_CI_SEQUENCE_NUMBER,
532 x_load_research_percentage =>X_LOAD_RESEARCH_PERCENTAGE,
533 x_creation_date =>X_LAST_UPDATE_DATE,
534 x_created_by =>X_LAST_UPDATED_BY,
535 x_last_update_date =>X_LAST_UPDATE_DATE,
536 x_last_updated_by =>X_LAST_UPDATED_BY,
537 x_last_update_login =>X_LAST_UPDATE_LOGIN
538 );
539 insert into IGS_CA_INST_REL (
540 SUB_CAL_TYPE,
541 SUB_CI_SEQUENCE_NUMBER,
542 SUP_CAL_TYPE,
543 SUP_CI_SEQUENCE_NUMBER,
544 LOAD_RESEARCH_PERCENTAGE,
545 CREATION_DATE,
546 CREATED_BY,
547 LAST_UPDATE_DATE,
548 LAST_UPDATED_BY,
549 LAST_UPDATE_LOGIN
550 ) values (
551 NEW_REFERENCES.SUB_CAL_TYPE,
552 NEW_REFERENCES.SUB_CI_SEQUENCE_NUMBER,
553 NEW_REFERENCES.SUP_CAL_TYPE,
554 NEW_REFERENCES.SUP_CI_SEQUENCE_NUMBER,
555 NEW_REFERENCES.LOAD_RESEARCH_PERCENTAGE,
556 X_LAST_UPDATE_DATE,
557 X_LAST_UPDATED_BY,
558 X_LAST_UPDATE_DATE,
559 X_LAST_UPDATED_BY,
560 X_LAST_UPDATE_LOGIN
561 );
562
563 open c;
564 fetch c into X_ROWID;
565 if (c%notfound) then
566 close c;
567 raise no_data_found;
568 end if;
569 close c;
570 After_DML (
571 p_action =>'INSERT',
572 x_rowid =>X_ROWID
573 );
574 end INSERT_ROW;
575
576 procedure LOCK_ROW (
577 X_ROWID in VARCHAR2,
578 X_SUB_CAL_TYPE in VARCHAR2,
579 X_SUB_CI_SEQUENCE_NUMBER in NUMBER,
580 X_SUP_CAL_TYPE in VARCHAR2,
581 X_SUP_CI_SEQUENCE_NUMBER in NUMBER,
582 X_LOAD_RESEARCH_PERCENTAGE in NUMBER
583 ) AS
584 cursor c1 is select
585 LOAD_RESEARCH_PERCENTAGE
586 from IGS_CA_INST_REL
587 where ROWID=X_ROWID
588 for update nowait;
589 tlinfo c1%rowtype;
590
591 begin
592 open c1;
593 fetch c1 into tlinfo;
594 if (c1%notfound) then
595 close c1;
596 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
597 IGS_GE_MSG_STACK.ADD;
598 app_exception.raise_exception;
599 return;
600 end if;
601 close c1;
602
603 if ( ((tlinfo.LOAD_RESEARCH_PERCENTAGE = X_LOAD_RESEARCH_PERCENTAGE)
604 OR ((tlinfo.LOAD_RESEARCH_PERCENTAGE is null)
605 AND (X_LOAD_RESEARCH_PERCENTAGE is null)))
606 ) then
607 null;
608 else
609 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
610 IGS_GE_MSG_STACK.ADD;
611 app_exception.raise_exception;
612 end if;
613 return;
614 end LOCK_ROW;
615
616 procedure UPDATE_ROW (
617 X_ROWID in VARCHAR2,
618 X_SUB_CAL_TYPE in VARCHAR2,
619 X_SUB_CI_SEQUENCE_NUMBER in NUMBER,
620 X_SUP_CAL_TYPE in VARCHAR2,
621 X_SUP_CI_SEQUENCE_NUMBER in NUMBER,
622 X_LOAD_RESEARCH_PERCENTAGE in NUMBER,
623 X_MODE in VARCHAR2 default 'R'
624 ) AS
625 X_LAST_UPDATE_DATE DATE;
626 X_LAST_UPDATED_BY NUMBER;
627 X_LAST_UPDATE_LOGIN NUMBER;
628 begin
629 X_LAST_UPDATE_DATE := SYSDATE;
630 if(X_MODE = 'I') then
631 X_LAST_UPDATED_BY := 1;
632 X_LAST_UPDATE_LOGIN := 0;
633 elsif (X_MODE = 'R') then
634 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
635 if X_LAST_UPDATED_BY is NULL then
636 X_LAST_UPDATED_BY := -1;
637 end if;
638 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
639 if X_LAST_UPDATE_LOGIN is NULL then
640 X_LAST_UPDATE_LOGIN := -1;
641 end if;
642 else
643 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
644 IGS_GE_MSG_STACK.ADD;
645 app_exception.raise_exception;
646 end if;
647 Before_DML (
648 p_action =>'UPDATE',
649 x_rowid =>X_ROWID,
650 x_sub_cal_type =>X_SUB_CAL_TYPE,
651 x_sub_ci_sequence_number =>X_SUB_CI_SEQUENCE_NUMBER,
652 x_sup_cal_type =>X_SUP_CAL_TYPE,
653 x_sup_ci_sequence_number =>X_SUP_CI_SEQUENCE_NUMBER,
654 x_load_research_percentage =>X_LOAD_RESEARCH_PERCENTAGE,
655 x_creation_date =>X_LAST_UPDATE_DATE,
656 x_created_by =>X_LAST_UPDATED_BY,
657 x_last_update_date =>X_LAST_UPDATE_DATE,
658 x_last_updated_by =>X_LAST_UPDATED_BY,
659 x_last_update_login =>X_LAST_UPDATE_LOGIN
660 );
661 update IGS_CA_INST_REL set
662 LOAD_RESEARCH_PERCENTAGE = NEW_REFERENCES.LOAD_RESEARCH_PERCENTAGE,
663 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
664 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
665 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
666 where ROWID=X_ROWID
667 ;
668 if (sql%notfound) then
669 raise no_data_found;
670 end if;
671 After_DML (
672 p_action =>'UPDATE',
673 x_rowid =>X_ROWID
674 );
675 end UPDATE_ROW;
676
677 procedure ADD_ROW (
678 X_ROWID in out NOCOPY VARCHAR2,
679 X_SUB_CAL_TYPE in VARCHAR2,
680 X_SUB_CI_SEQUENCE_NUMBER in NUMBER,
681 X_SUP_CAL_TYPE in VARCHAR2,
682 X_SUP_CI_SEQUENCE_NUMBER in NUMBER,
683 X_LOAD_RESEARCH_PERCENTAGE in NUMBER,
684 X_MODE in VARCHAR2 default 'R'
685 ) AS
686 cursor c1 is select rowid from IGS_CA_INST_REL
687 where SUB_CAL_TYPE = X_SUB_CAL_TYPE
688 and SUB_CI_SEQUENCE_NUMBER = X_SUB_CI_SEQUENCE_NUMBER
689 and SUP_CAL_TYPE = X_SUP_CAL_TYPE
690 and SUP_CI_SEQUENCE_NUMBER = X_SUP_CI_SEQUENCE_NUMBER
691 ;
692 begin
693 open c1;
694 fetch c1 into X_ROWID;
695 if (c1%notfound) then
696 close c1;
697 INSERT_ROW (
698 X_ROWID,
699 X_SUB_CAL_TYPE,
700 X_SUB_CI_SEQUENCE_NUMBER,
701 X_SUP_CAL_TYPE,
702 X_SUP_CI_SEQUENCE_NUMBER,
703 X_LOAD_RESEARCH_PERCENTAGE,
704 X_MODE);
705 return;
706 end if;
707 close c1;
708 UPDATE_ROW (
709 X_ROWID,
710 X_SUB_CAL_TYPE,
711 X_SUB_CI_SEQUENCE_NUMBER,
712 X_SUP_CAL_TYPE,
713 X_SUP_CI_SEQUENCE_NUMBER,
714 X_LOAD_RESEARCH_PERCENTAGE,
715 X_MODE);
716 end ADD_ROW;
717
718 procedure DELETE_ROW (
719 X_ROWID in VARCHAR2
720 ) AS
721 begin
722 Before_DML (
723 p_action =>'DELETE',
724 x_rowid =>X_ROWID
725 );
726 delete from IGS_CA_INST_REL
727 where ROWID=X_ROWID;
728 if (sql%notfound) then
729 raise no_data_found;
730 end if;
731 After_DML (
732 p_action =>'DELETE',
733 x_rowid =>X_ROWID
734 );
735 end DELETE_ROW;
736
737 end IGS_CA_INST_REL_PKG;
[Home] [Help]