1 PACKAGE BODY igs_ps_usec_cal_nums_pkg AS
2 /* $Header: IGSPI0UB.pls 120.0 2005/06/01 19:29:20 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_usec_cal_nums%RowType;
5 new_references igs_ps_usec_cal_nums%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_unit_section_call_number_id IN NUMBER DEFAULT NULL,
11 x_calender_type IN VARCHAR2 DEFAULT NULL,
12 x_ci_sequence_number IN NUMBER DEFAULT NULL,
13 x_call_number IN NUMBER DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20
21 /*************************************************************
22 Created By :schodava
23 Date Created By :2000/05/11
24 Purpose :
25 Know limitations, enhancements or remarks
26 Change History
27 Who When What
28
29 (reverse chronological order - newest change first)
30 ***************************************************************/
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM IGS_PS_USEC_CAL_NUMS
35 WHERE rowid = x_rowid;
36
37 BEGIN
38
39 l_rowid := x_rowid;
40
41 -- Code for setting the Old and New Reference Values.
42 -- Populate Old Values.
43 Open cur_old_ref_values;
44 Fetch cur_old_ref_values INTO old_references;
45 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
46 Close cur_old_ref_values;
47 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
48 IGS_GE_MSG_STACK.ADD;
49 App_Exception.Raise_Exception;
50 Return;
51 END IF;
52 Close cur_old_ref_values;
53
54 -- Populate New Values.
55 new_references.unit_section_call_number_id := x_unit_section_call_number_id;
56 new_references.calender_type := x_calender_type;
57 new_references.ci_sequence_number := x_ci_sequence_number;
58 new_references.call_number := x_call_number;
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66 new_references.last_update_date := x_last_update_date;
67 new_references.last_updated_by := x_last_updated_by;
68 new_references.last_update_login := x_last_update_login;
69
70 END Set_Column_Values;
71
72 PROCEDURE Check_Constraints (
73 Column_Name IN VARCHAR2 DEFAULT NULL,
74 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
75 /*************************************************************
76 Created By :schodava
77 Date Created By :2000/05/11
78 Purpose :
79 Know limitations, enhancements or remarks
80 Change History
81 Who When What
82
83 (reverse chronological order - newest change first)
84 ***************************************************************/
85
86 BEGIN
87
88 IF column_name IS NULL THEN
89 NULL;
90 NULL;
91 END IF;
92
93
94
95
96 END Check_Constraints;
97
98 PROCEDURE Check_Uniqueness AS
99 /*************************************************************
100 Created By :schodava
101 Date Created By :2000/05/11
102 Purpose :
103 Know limitations, enhancements or remarks
104 Change History
105 Who When What
106
107 (reverse chronological order - newest change first)
108 ***************************************************************/
109
110 begin
111 IF Get_Uk_For_Validation (
112 new_references.calender_type
113 ,new_references.ci_sequence_number
114 ) THEN
115 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
116 IGS_GE_MSG_STACK.ADD;
117 app_exception.raise_exception;
118 END IF;
119 END Check_Uniqueness ;
120 FUNCTION Get_PK_For_Validation (
121 x_unit_section_call_number_id IN NUMBER
122 ) RETURN BOOLEAN AS
123
124 /*************************************************************
125 Created By :schodava
126 Date Created By :2000/05/11
127 Purpose :
128 Know limitations, enhancements or remarks
129 Change History
130 Who When What
131
132 (reverse chronological order - newest change first)
133 ***************************************************************/
134
135 CURSOR cur_rowid IS
136 SELECT rowid
137 FROM igs_ps_usec_cal_nums
138 WHERE unit_section_call_number_id = x_unit_section_call_number_id
139 FOR UPDATE NOWAIT;
140
141 lv_rowid cur_rowid%RowType;
142
143 BEGIN
144
145 Open cur_rowid;
146 Fetch cur_rowid INTO lv_rowid;
147 IF (cur_rowid%FOUND) THEN
148 Close cur_rowid;
149 Return(TRUE);
150 ELSE
151 Close cur_rowid;
152 Return(FALSE);
153 END IF;
154 END Get_PK_For_Validation;
155
156 FUNCTION Get_UK_For_Validation (
157 x_calender_type IN VARCHAR2,
158 x_ci_sequence_number IN NUMBER
159 ) RETURN BOOLEAN AS
160
161 /*************************************************************
162 Created By :schodava
163 Date Created By :2000/05/11
164 Purpose :
165 Know limitations, enhancements or remarks
166 Change History
167 Who When What
168
169 (reverse chronological order - newest change first)
170 ***************************************************************/
171
172 CURSOR cur_rowid IS
173 SELECT rowid
174 FROM igs_ps_usec_cal_nums
175 WHERE calender_type = x_calender_type
176 AND ci_sequence_number = x_ci_sequence_number and ((l_rowid is null) or (rowid <> l_rowid))
177
178 ;
179 lv_rowid cur_rowid%RowType;
180
181 BEGIN
182
183 Open cur_rowid;
184 Fetch cur_rowid INTO lv_rowid;
185 IF (cur_rowid%FOUND) THEN
186 Close cur_rowid;
187 return (true);
188 ELSE
189 close cur_rowid;
190 return(false);
191 END IF;
192 END Get_UK_For_Validation ;
193 PROCEDURE Before_DML (
194 p_action IN VARCHAR2,
195 x_rowid IN VARCHAR2 DEFAULT NULL,
196 x_unit_section_call_number_id IN NUMBER DEFAULT NULL,
197 x_calender_type IN VARCHAR2 DEFAULT NULL,
198 x_ci_sequence_number IN NUMBER DEFAULT NULL,
199 x_call_number IN NUMBER DEFAULT NULL,
200 x_creation_date IN DATE DEFAULT NULL,
201 x_created_by IN NUMBER DEFAULT NULL,
202 x_last_update_date IN DATE DEFAULT NULL,
203 x_last_updated_by IN NUMBER DEFAULT NULL,
204 x_last_update_login IN NUMBER DEFAULT NULL
205 ) AS
206 /*************************************************************
207 Created By :schodava
208 Date Created By :2000/05/11
209 Purpose :
210 Know limitations, enhancements or remarks
211 Change History
212 Who When What
213
214 (reverse chronological order - newest change first)
215 ***************************************************************/
216
217 BEGIN
218
219 Set_Column_Values (
220 p_action,
221 x_rowid,
222 x_unit_section_call_number_id,
223 x_calender_type,
224 x_ci_sequence_number,
225 x_call_number,
226 x_creation_date,
227 x_created_by,
228 x_last_update_date,
229 x_last_updated_by,
230 x_last_update_login
231 );
232
233 IF (p_action = 'INSERT') THEN
234 -- Call all the procedures related to Before Insert.
235 Null;
236 IF Get_Pk_For_Validation(
237 new_references.unit_section_call_number_id) THEN
238 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
239 IGS_GE_MSG_STACK.ADD;
240 App_Exception.Raise_Exception;
241 END IF;
242 Check_Uniqueness;
243 Check_Constraints;
244 ELSIF (p_action = 'UPDATE') THEN
245 -- Call all the procedures related to Before Update.
246 Null;
247 Check_Uniqueness;
248 Check_Constraints;
249 ELSIF (p_action = 'DELETE') THEN
250 -- Call all the procedures related to Before Delete.
251 Null;
252 ELSIF (p_action = 'VALIDATE_INSERT') THEN
253 -- Call all the procedures related to Before Insert.
254 IF Get_PK_For_Validation (
255 new_references.unit_section_call_number_id) THEN
256 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
257 IGS_GE_MSG_STACK.ADD;
258 App_Exception.Raise_Exception;
259 END IF;
260 Check_Uniqueness;
261 Check_Constraints;
262 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
263 Check_Uniqueness;
264 Check_Constraints;
265 ELSIF (p_action = 'VALIDATE_DELETE') THEN
266 Null;
267 END IF;
268
269 END Before_DML;
270
271 PROCEDURE After_DML (
272 p_action IN VARCHAR2,
273 x_rowid IN VARCHAR2
274 ) IS
275 /*************************************************************
276 Created By :schodava
277 Date Created By :2000/05/11
278 Purpose :
279 Know limitations, enhancements or remarks
280 Change History
281 Who When What
282
283 (reverse chronological order - newest change first)
284 ***************************************************************/
285
286 BEGIN
287
288 l_rowid := x_rowid;
289
290 IF (p_action = 'INSERT') THEN
291 -- Call all the procedures related to After Insert.
292 Null;
293 ELSIF (p_action = 'UPDATE') THEN
294 -- Call all the procedures related to After Update.
295 Null;
296 ELSIF (p_action = 'DELETE') THEN
297 -- Call all the procedures related to After Delete.
298 Null;
299 END IF;
300 l_rowid := null;
301 END After_DML;
302
303 procedure INSERT_ROW (
304 X_ROWID in out NOCOPY VARCHAR2,
305 x_UNIT_SECTION_CALL_NUMBER_ID IN out NOCOPY NUMBER,
306 x_CALENDER_TYPE IN VARCHAR2,
307 x_CI_SEQUENCE_NUMBER IN NUMBER,
308 x_CALL_NUMBER IN NUMBER,
309 X_MODE in VARCHAR2
310 ) AS
311 /*************************************************************
312 Created By :schodava
313 Date Created By :2000/05/11
314 Purpose :
315 Know limitations, enhancements or remarks
316 Change History
317 Who When What
318
319 (reverse chronological order - newest change first)
320 ***************************************************************/
321
322 cursor C is select ROWID from IGS_PS_USEC_CAL_NUMS
323 where UNIT_SECTION_CALL_NUMBER_ID= X_UNIT_SECTION_CALL_NUMBER_ID
324 ;
325 X_LAST_UPDATE_DATE DATE ;
326 X_LAST_UPDATED_BY NUMBER ;
327 X_LAST_UPDATE_LOGIN NUMBER ;
328 begin
329 X_LAST_UPDATE_DATE := SYSDATE;
330 if(X_MODE = 'I') then
331 X_LAST_UPDATED_BY := 1;
332 X_LAST_UPDATE_LOGIN := 0;
333 elsif (X_MODE = 'R') then
334 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
335 if X_LAST_UPDATED_BY is NULL then
336 X_LAST_UPDATED_BY := -1;
337 end if;
338 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
339 if X_LAST_UPDATE_LOGIN is NULL then
340 X_LAST_UPDATE_LOGIN := -1;
341 end if;
342 else
343 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
344 IGS_GE_MSG_STACK.ADD;
345 app_exception.raise_exception;
346 end if;
347 SELECT
348 IGS_PS_USEC_CAL_NUMS_S.nextval
349 INTO
350 x_unit_section_call_number_id
351 FROM
352 dual;
353 Before_DML(
354 p_action=>'INSERT',
355 x_rowid=>X_ROWID,
356 x_unit_section_call_number_id=>X_UNIT_SECTION_CALL_NUMBER_ID,
357 x_calender_type=>X_CALENDER_TYPE,
358 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
359 x_call_number=>X_CALL_NUMBER,
360 x_creation_date=>X_LAST_UPDATE_DATE,
361 x_created_by=>X_LAST_UPDATED_BY,
362 x_last_update_date=>X_LAST_UPDATE_DATE,
363 x_last_updated_by=>X_LAST_UPDATED_BY,
364 x_last_update_login=>X_LAST_UPDATE_LOGIN);
365 insert into IGS_PS_USEC_CAL_NUMS (
366 UNIT_SECTION_CALL_NUMBER_ID
367 ,CALENDER_TYPE
368 ,CI_SEQUENCE_NUMBER
369 ,CALL_NUMBER
370 ,CREATION_DATE
371 ,CREATED_BY
372 ,LAST_UPDATE_DATE
373 ,LAST_UPDATED_BY
374 ,LAST_UPDATE_LOGIN
375 ) values (
376 NEW_REFERENCES.UNIT_SECTION_CALL_NUMBER_ID
377 ,NEW_REFERENCES.CALENDER_TYPE
378 ,NEW_REFERENCES.CI_SEQUENCE_NUMBER
379 ,NEW_REFERENCES.CALL_NUMBER
380 ,X_LAST_UPDATE_DATE
381 ,X_LAST_UPDATED_BY
382 ,X_LAST_UPDATE_DATE
383 ,X_LAST_UPDATED_BY
384 ,X_LAST_UPDATE_LOGIN
385 );
386 open c;
387 fetch c into X_ROWID;
388 if (c%notfound) then
389 close c;
390 raise no_data_found;
391 end if;
392 close c;
393 After_DML (
394 p_action => 'INSERT' ,
395 x_rowid => X_ROWID );
396 end INSERT_ROW;
397 procedure LOCK_ROW (
398 X_ROWID in VARCHAR2,
399 x_UNIT_SECTION_CALL_NUMBER_ID IN NUMBER,
400 x_CALENDER_TYPE IN VARCHAR2,
401 x_CI_SEQUENCE_NUMBER IN NUMBER,
402 x_CALL_NUMBER IN NUMBER ) AS
403 /*************************************************************
404 Created By :schodava
405 Date Created By :2000/05/11
406 Purpose :
407 Know limitations, enhancements or remarks
408 Change History
409 Who When What
410
411 (reverse chronological order - newest change first)
412 ***************************************************************/
413
414 cursor c1 is select
415 CALENDER_TYPE
416 , CI_SEQUENCE_NUMBER
417 , CALL_NUMBER
418 from IGS_PS_USEC_CAL_NUMS
419 where ROWID = X_ROWID
420 for update nowait;
421 tlinfo c1%rowtype;
422 begin
423 open c1;
424 fetch c1 into tlinfo;
425 if (c1%notfound) then
426 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
427 IGS_GE_MSG_STACK.ADD;
428 close c1;
429 app_exception.raise_exception;
430 return;
431 end if;
432 close c1;
433 if ( ( tlinfo.CALENDER_TYPE = X_CALENDER_TYPE)
434 AND (tlinfo.CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER)
435 AND (tlinfo.CALL_NUMBER = X_CALL_NUMBER)
436 ) then
437 null;
438 else
439 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
440 IGS_GE_MSG_STACK.ADD;
441 app_exception.raise_exception;
442 end if;
443 return;
444 end LOCK_ROW;
445 Procedure UPDATE_ROW (
446 X_ROWID in VARCHAR2,
447 x_UNIT_SECTION_CALL_NUMBER_ID IN NUMBER,
448 x_CALENDER_TYPE IN VARCHAR2,
449 x_CI_SEQUENCE_NUMBER IN NUMBER,
450 x_CALL_NUMBER IN NUMBER,
451 X_MODE in VARCHAR2
452 ) AS
453 /*************************************************************
454 Created By :schodava
455 Date Created By :2000/05/11
456 Purpose :
457 Know limitations, enhancements or remarks
458 Change History
459 Who When What
460
461 (reverse chronological order - newest change first)
462 ***************************************************************/
463
464 X_LAST_UPDATE_DATE DATE ;
465 X_LAST_UPDATED_BY NUMBER ;
466 X_LAST_UPDATE_LOGIN NUMBER ;
467 begin
468 X_LAST_UPDATE_DATE := SYSDATE;
469 if(X_MODE = 'I') then
470 X_LAST_UPDATED_BY := 1;
471 X_LAST_UPDATE_LOGIN := 0;
472 elsif (X_MODE = 'R') then
473 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
474 if X_LAST_UPDATED_BY is NULL then
475 X_LAST_UPDATED_BY := -1;
476 end if;
477 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
478 if X_LAST_UPDATE_LOGIN is NULL then
479 X_LAST_UPDATE_LOGIN := -1;
480 end if;
481 else
482 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
483 IGS_GE_MSG_STACK.ADD;
484 app_exception.raise_exception;
485 end if;
486 Before_DML(
487 p_action=>'UPDATE',
488 x_rowid=>X_ROWID,
489 x_unit_section_call_number_id=>X_UNIT_SECTION_CALL_NUMBER_ID,
490 x_calender_type=>X_CALENDER_TYPE,
491 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
492 x_call_number=>X_CALL_NUMBER,
493 x_creation_date=>X_LAST_UPDATE_DATE,
494 x_created_by=>X_LAST_UPDATED_BY,
495 x_last_update_date=>X_LAST_UPDATE_DATE,
496 x_last_updated_by=>X_LAST_UPDATED_BY,
497 x_last_update_login=>X_LAST_UPDATE_LOGIN);
498 update IGS_PS_USEC_CAL_NUMS set
499 CALENDER_TYPE = NEW_REFERENCES.CALENDER_TYPE,
500 CI_SEQUENCE_NUMBER = NEW_REFERENCES.CI_SEQUENCE_NUMBER,
501 CALL_NUMBER = NEW_REFERENCES.CALL_NUMBER,
502 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
503 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
504 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
505 where ROWID = X_ROWID;
506 if (sql%notfound) then
507 raise no_data_found;
508 end if;
509
510 After_DML (
511 p_action => 'UPDATE' ,
512 x_rowid => X_ROWID
513 );
514 end UPDATE_ROW;
515 procedure ADD_ROW (
516 X_ROWID in out NOCOPY VARCHAR2,
517 x_UNIT_SECTION_CALL_NUMBER_ID IN out NOCOPY NUMBER,
518 x_CALENDER_TYPE IN VARCHAR2,
519 x_CI_SEQUENCE_NUMBER IN NUMBER,
520 x_CALL_NUMBER IN NUMBER,
521 X_MODE in VARCHAR2
522 ) AS
523 /*************************************************************
524 Created By :schodava
525 Date Created By :2000/05/11
526 Purpose :
527 Know limitations, enhancements or remarks
528 Change History
529 Who When What
530
531 (reverse chronological order - newest change first)
532 ***************************************************************/
533
534 cursor c1 is select ROWID from IGS_PS_USEC_CAL_NUMS
535 where UNIT_SECTION_CALL_NUMBER_ID= X_UNIT_SECTION_CALL_NUMBER_ID
536 ;
537 begin
538 open c1;
539 fetch c1 into X_ROWID;
540 if (c1%notfound) then
541 close c1;
542 INSERT_ROW (
543 X_ROWID,
544 X_UNIT_SECTION_CALL_NUMBER_ID,
545 X_CALENDER_TYPE,
546 X_CI_SEQUENCE_NUMBER,
547 X_CALL_NUMBER,
548 X_MODE );
549 return;
550 end if;
551 close c1;
552 UPDATE_ROW (
553 X_ROWID,
554 X_UNIT_SECTION_CALL_NUMBER_ID,
555 X_CALENDER_TYPE,
556 X_CI_SEQUENCE_NUMBER,
557 X_CALL_NUMBER,
558 X_MODE );
559 end ADD_ROW;
560 procedure DELETE_ROW (
561 X_ROWID in VARCHAR2
562 ) AS
563 /*************************************************************
564 Created By :schodava
565 Date Created By :2000/05/11
566 Purpose :
567 Know limitations, enhancements or remarks
568 Change History
569 Who When What
570
571 (reverse chronological order - newest change first)
572 ***************************************************************/
573
574 begin
575 Before_DML (
576 p_action => 'DELETE',
577 x_rowid => X_ROWID
578 );
579 delete from IGS_PS_USEC_CAL_NUMS
580 where ROWID = X_ROWID;
581 if (sql%notfound) then
582 raise no_data_found;
583 end if;
584 After_DML (
585 p_action => 'DELETE',
586 x_rowid => X_ROWID
587 );
588 end DELETE_ROW;
589 END igs_ps_usec_cal_nums_pkg;
[Home] [Help]