1 PACKAGE BODY igs_or_org_alt_ids_pkg AS
2 /* $Header: IGSOI22B.pls 120.1 2006/03/28 09:12:42 skpandey noship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_or_org_alt_ids%RowType;
5 new_references igs_or_org_alt_ids%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_structure_id IN VARCHAR2 DEFAULT NULL,
11 x_org_structure_type IN VARCHAR2 DEFAULT NULL,
12 x_org_alternate_id_type IN VARCHAR2 DEFAULT NULL,
13 x_org_alternate_id IN VARCHAR2 DEFAULT NULL,
14 x_start_date IN DATE DEFAULT NULL,
15 x_end_date IN DATE DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL
21 ) AS
22
23 /*************************************************************
24 Created By : sbonam@in
25 Date Created By : 2000/05/12
26 Purpose : Populating the new_reference columns to be used by other
27 Functions
28 Know limitations, enhancements or remarks
29 Change History
30 Who When What
31
32 (reverse chronological order - newest change first)
33 ***************************************************************/
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM IGS_OR_ORG_ALT_IDS
38 WHERE rowid = x_rowid;
39
40 BEGIN
41
42 l_rowid := x_rowid;
43
44 -- Code for setting the Old and New Reference Values.
45 -- Populate Old Values.
46 Open cur_old_ref_values;
47 Fetch cur_old_ref_values INTO old_references;
48 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
49 Close cur_old_ref_values;
50 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
51 IGS_GE_MSG_STACK.ADD;
52 App_Exception.Raise_Exception;
53 Return;
54 END IF;
55 Close cur_old_ref_values;
56
57 -- Populate New Values.
58 new_references.org_structure_id := x_org_structure_id;
59 new_references.org_structure_type := x_org_structure_type;
60 new_references.org_alternate_id_type := x_org_alternate_id_type;
61 new_references.org_alternate_id := x_org_alternate_id;
62 new_references.start_date := x_start_date;
63 new_references.end_date := x_end_date;
64 IF (p_action = 'UPDATE') THEN
65 new_references.creation_date := old_references.creation_date;
66 new_references.created_by := old_references.created_by;
67 ELSE
68 new_references.creation_date := x_creation_date;
69 new_references.created_by := x_created_by;
70 END IF;
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74
75 END Set_Column_Values;
76
77 PROCEDURE Check_Constraints (
78 Column_Name IN VARCHAR2 DEFAULT NULL,
79 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
80 /*************************************************************
81 Created By : sbonam@in
82 Date Created By : 2000/05/12
83 Purpose : To validate against any check constraints if any
84 Know limitations, enhancements or remarks
85 Change History
86 Who When What
87
88 (reverse chronological order - newest change first)
89 ***************************************************************/
90
91 BEGIN
92
93 IF column_name IS NULL THEN
94 NULL;
95 NULL;
96 END IF;
97
98
99
100
101 END Check_Constraints;
102
103 PROCEDURE Check_Parent_Existance AS
104 /*************************************************************
105 Created By : sbonam@in
106 Date Created By : 2000/05/12
107 Purpose : To check for Existence of Parent Values before
108 inserting into foreign key columns
109 Know limitations, enhancements or remarks
110 Change History
111 Who When What
112
113 (reverse chronological order - newest change first)
114 ***************************************************************/
115
116 BEGIN
117
118 IF (((old_references.org_alternate_id_type = new_references.org_alternate_id_type)) OR
119 ((new_references.org_alternate_id_type IS NULL))) THEN
120 NULL;
121 ELSIF NOT Igs_Or_Org_Alt_Idtyp_Pkg.Get_PK_For_Validation (
122 new_references.org_alternate_id_type
123 ) THEN
124 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
125 IGS_GE_MSG_STACK.ADD;
126 App_Exception.Raise_Exception;
127 END IF;
128 IF NOT Igs_Lookups_View_Pkg.Get_Pk_For_Validation('ORG_STRUCTURE_TYPE',new_references.org_structure_type) THEN
129 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
130 IGS_GE_MSG_STACK.ADD;
131 APP_EXCEPTION.RAISE_EXCEPTION;
132 END IF;
133
134 IF new_references.org_structure_type = 'INSTITUTE' THEN
135 IF NOT Igs_Or_Institution_Pkg.Get_Pk_For_Validation(new_references.org_structure_id) THEN
136 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
137 IGS_GE_MSG_STACK.ADD;
138 APP_EXCEPTION.RAISE_EXCEPTION;
139 END IF;
140 ELSIF new_references.org_structure_type = 'LOCATION' THEN
141 IF NOT Igs_Ad_Location_Pkg.Get_Pk_For_Validation(new_references.org_structure_id,
142 'N') THEN
143 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
144 IGS_GE_MSG_STACK.ADD;
145 APP_EXCEPTION.RAISE_EXCEPTION;
146 END IF;
147 ELSIF new_references.org_structure_type = 'ORG_UNIT' THEN
148 IF NOT Igs_Or_Unit_Pkg.Get_Pk_For_Str_Validation(new_references.org_structure_id) THEN
149 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
150 IGS_GE_MSG_STACK.ADD;
151 APP_EXCEPTION.RAISE_EXCEPTION;
152 END IF;
153 END IF;
154
155
156 END Check_Parent_Existance;
157
158 FUNCTION Get_PK_For_Validation (
159 x_org_alternate_id IN VARCHAR2,
160 x_org_alternate_id_type IN VARCHAR2,
161 x_org_structure_id IN VARCHAR2,
162 x_org_structure_type IN VARCHAR2,
163 x_start_date IN DATE
164 ) RETURN BOOLEAN AS
165
166 /*************************************************************
167 Created By : sbonam@in.
168 Date Created By : 2000/05/12
169 Purpose : This function is used by other table handler's in their
170 check_parent_existance to validate their foreign key
171 Know limitations, enhancements or remarks
172 Change History
173 Who When What
174
175 (reverse chronological order - newest change first)
176 ***************************************************************/
177
178 CURSOR cur_rowid IS
179 SELECT rowid
180 FROM igs_or_org_alt_ids
181 WHERE org_alternate_id = x_org_alternate_id
182 AND org_alternate_id_type = x_org_alternate_id_type
183 AND org_structure_id = x_org_structure_id
184 AND org_structure_type = x_org_structure_type
185 AND start_date = x_start_date
186 FOR UPDATE NOWAIT;
187
188 lv_rowid cur_rowid%RowType;
189
190 BEGIN
191
192 Open cur_rowid;
193 Fetch cur_rowid INTO lv_rowid;
194 IF (cur_rowid%FOUND) THEN
195 Close cur_rowid;
196 Return(TRUE);
197 ELSE
198 Close cur_rowid;
199 Return(FALSE);
200 END IF;
201 END Get_PK_For_Validation;
202
203 PROCEDURE Get_FK_Igs_Or_Org_Alt_Idtyp (
204 x_org_alternate_id_type IN VARCHAR2
205 ) AS
206
207 /*************************************************************
208 Created By : sbonam@in
209 Date Created By : 2000/05/12
210 Purpose : This procedure is called from other tbh's check_child_existence
211 to validate against the existence of records in the current table
212 which is actually a child of the master table whose name appears
213 in Get_Fk_<table_name>
214 Know limitations, enhancements or remarks
215 Change History
216 Who When What
217
218 (reverse chronological order - newest change first)
219 ***************************************************************/
220
221 CURSOR cur_rowid IS
222 SELECT rowid
223 FROM igs_or_org_alt_ids
224 WHERE org_alternate_id_type = x_org_alternate_id_type ;
225
226 lv_rowid cur_rowid%RowType;
227
228 BEGIN
229
230 Open cur_rowid;
231 Fetch cur_rowid INTO lv_rowid;
232 IF (cur_rowid%FOUND) THEN
233 Close cur_rowid;
234 Fnd_Message.Set_Name ('IGS', 'IGS_OR_OLI_OAIT_FK');
235 IGS_GE_MSG_STACK.ADD;
236 App_Exception.Raise_Exception;
237 Return;
238 END IF;
239 Close cur_rowid;
240
241 END Get_FK_Igs_Or_Org_Alt_Idtyp;
242
243 /*************************************************************
244 Created By : sbonam@in
245 Date Created By : 2000/05/23
246 Purpose : This procedure is called from other tbh's check_child_existence
247 to validate against the existence of records in the current table
248 which is actually a child of the master table whose name appears
249 in Get_Fk_<table_name>
250 Know limitations, enhancements or remarks
251 Change History
252 Who When What
253
254 (reverse chronological order - newest change first)
255 ***************************************************************/
256 PROCEDURE Get_Fk_Igs_Or_Institution(
257 x_institution_cd IN VARCHAR2
258 ) AS
259 CURSOR cur_rowid IS
260 SELECT ROWID
261 FROM IGS_OR_ORG_ALT_IDS
262 WHERE (
263 (org_structure_id = x_institution_cd) AND (org_structure_type = 'INSTITUTE')
264 );
265 lv_rowid cur_rowid%ROWTYPE;
266 BEGIN
267 OPEN cur_rowid;
268 FETCH cur_rowid INTO lv_rowid;
269 IF (cur_rowid%FOUND) THEN
270 CLOSE cur_rowid;
271 Fnd_Message.Set_Name ('IGS', 'IGS_OR_OLI_INS_FK');
272 IGS_GE_MSG_STACK.ADD;
273 App_Exception.Raise_Exception;
274 RETURN;
275 END IF;
276 CLOSE cur_rowid;
277 END Get_Fk_Igs_Or_Institution;
278
279 /*************************************************************
280 Created By : sbonam@in
281 Date Created By : 2000/05/23
282 Purpose : This procedure is called from other tbh's check_child_existence
283 to validate against the existence of records in the current table
284 which is actually a child of the master table whose name appears
285 in Get_Fk_<table_name>
286 Know limitations, enhancements or remarks
287 Change History
288 Who When What
289
290 (reverse chronological order - newest change first)
291 ***************************************************************/
292 PROCEDURE Get_Fk_Igs_Or_Unit (
293 x_org_unit_cd IN VARCHAR2
294 ) AS
295 CURSOR cur_rowid IS
296 SELECT ROWID
297 FROM IGS_OR_ORG_ALT_IDS
298 WHERE (
299 (org_structure_id = x_org_unit_cd) AND (org_structure_type = 'ORG_UNIT')
300 );
301
302 lv_rowid cur_rowid%ROWTYPE;
303 BEGIN
304 OPEN cur_rowid;
305 FETCH cur_rowid INTO lv_rowid;
306 IF (cur_rowid%FOUND) THEN
307 CLOSE cur_rowid;
308 Fnd_Message.Set_Name ('IGS', 'IGS_OR_OLI_OU_FK');
309 IGS_GE_MSG_STACK.ADD;
310 App_Exception.Raise_Exception;
311 RETURN;
312 END IF;
313 CLOSE cur_rowid;
314 END Get_Fk_Igs_Or_Unit;
315
316
317 PROCEDURE Before_DML (
318 p_action IN VARCHAR2,
319 x_rowid IN VARCHAR2 DEFAULT NULL,
320 x_org_structure_id IN VARCHAR2 DEFAULT NULL,
321 x_org_structure_type IN VARCHAR2 DEFAULT NULL,
322 x_org_alternate_id_type IN VARCHAR2 DEFAULT NULL,
323 x_org_alternate_id IN VARCHAR2 DEFAULT NULL,
324 x_start_date IN DATE DEFAULT NULL,
325 x_end_date IN DATE DEFAULT NULL,
326 x_creation_date IN DATE DEFAULT NULL,
327 x_created_by IN NUMBER DEFAULT NULL,
328 x_last_update_date IN DATE DEFAULT NULL,
329 x_last_updated_by IN NUMBER DEFAULT NULL,
330 x_last_update_login IN NUMBER DEFAULT NULL
331 ) AS
332 /*************************************************************
333 Created By : sbonam@in
334 Date Created By : 2000/05/12
335 Purpose : This procedure is called before any DML operation
336 with the DML operation as a parameter. This is
337 a function that is called from other functions only like
338 insert_row/add_row etc. This would not be called from Forms directly.
339 Know limitations, enhancements or remarks
340 Change History
341 Who When What
342
343 (reverse chronological order - newest change first)
344 ***************************************************************/
345
346 BEGIN
347
348 Set_Column_Values (
349 p_action,
350 x_rowid,
351 x_org_structure_id,
352 x_org_structure_type,
353 x_org_alternate_id_type,
354 x_org_alternate_id,
355 x_start_date,
356 x_end_date,
357 x_creation_date,
358 x_created_by,
359 x_last_update_date,
360 x_last_updated_by,
361 x_last_update_login
362 );
363
364 IF (p_action = 'INSERT') THEN
365 -- Call all the procedures related to Before Insert.
366 Null;
367 IF Get_Pk_For_Validation(
368 new_references.org_alternate_id,
369 new_references.org_alternate_id_type,
370 new_references.org_structure_id,
371 new_references.org_structure_type,
372 new_references.start_date) THEN
373 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
374 IGS_GE_MSG_STACK.ADD;
375 App_Exception.Raise_Exception;
376 END IF;
377 Check_Constraints;
378 Check_Parent_Existance;
379 ELSIF (p_action = 'UPDATE') THEN
380 -- Call all the procedures related to Before Update.
381 Null;
382 Check_Constraints;
383 Check_Parent_Existance;
384 ELSIF (p_action = 'DELETE') THEN
385 -- Call all the procedures related to Before Delete.
386 Null;
387 ELSIF (p_action = 'VALIDATE_INSERT') THEN
388 -- Call all the procedures related to Before Insert.
389 IF Get_PK_For_Validation (
390 new_references.org_alternate_id,
391 new_references.org_alternate_id_type,
392 new_references.org_structure_id,
393 new_references.org_structure_type,
394 new_references.start_date) THEN
395 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
396 IGS_GE_MSG_STACK.ADD;
397 App_Exception.Raise_Exception;
398 END IF;
399 Check_Constraints;
400 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
401 Check_Constraints;
402 ELSIF (p_action = 'VALIDATE_DELETE') THEN
403 Null;
404 END IF;
405
406 END Before_DML;
407
408 PROCEDURE After_DML (
409 p_action IN VARCHAR2,
410 x_rowid IN VARCHAR2
411 ) IS
412 /*************************************************************
413 Created By : sbonam@in
414 Date Created By : 2000/05/12
415 Purpose : This procedure is called after any DML operation
416 with the DML operation as a parameter. This is
417 a function that is called from other functions only like
418 insert_row/add_row etc. This would not be called from Forms directly.
419 Know limitations, enhancements or remarks
420 Change History
421 Who When What
422
423 (reverse chronological order - newest change first)
424 ***************************************************************/
425
426 BEGIN
427
428 l_rowid := x_rowid;
429
430 IF (p_action = 'INSERT') THEN
431 -- Call all the procedures related to After Insert.
432 Null;
433 ELSIF (p_action = 'UPDATE') THEN
434 -- Call all the procedures related to After Update.
435 Null;
436 ELSIF (p_action = 'DELETE') THEN
437 -- Call all the procedures related to After Delete.
438 Null;
439 END IF;
440
441 END After_DML;
442
443 procedure INSERT_ROW (
444 X_ROWID in out NOCOPY VARCHAR2,
445 x_ORG_STRUCTURE_ID IN VARCHAR2,
446 x_ORG_STRUCTURE_TYPE IN VARCHAR2,
447 x_ORG_ALTERNATE_ID_TYPE IN VARCHAR2,
448 x_ORG_ALTERNATE_ID IN VARCHAR2,
449 x_START_DATE IN DATE,
450 x_END_DATE IN DATE,
451 X_MODE in VARCHAR2 default 'R'
452 ) AS
453 /*************************************************************
454 Created By :
455 Date Created By : 2000/05/12
456 Purpose : This procedure is called from Forms during an
457 insert_row (ON_INSERT) operation. This in turn
458 calls Before_DML which inturn calls set_columns
459 and check_parent_existance Know limitations, enhancements or remarks
460 Change History
461 Who When What
462
463 (reverse chronological order - newest change first)
464 ***************************************************************/
465
466 cursor C is select ROWID from IGS_OR_ORG_ALT_IDS
467 where ORG_ALTERNATE_ID= X_ORG_ALTERNATE_ID
468 and ORG_ALTERNATE_ID_TYPE = X_ORG_ALTERNATE_ID_TYPE
469 and ORG_STRUCTURE_ID = X_ORG_STRUCTURE_ID
470 and ORG_STRUCTURE_TYPE = X_ORG_STRUCTURE_TYPE
471 and START_DATE = X_START_DATE
472 ;
473 X_LAST_UPDATE_DATE DATE ;
474 X_LAST_UPDATED_BY NUMBER ;
475 X_LAST_UPDATE_LOGIN NUMBER ;
476 begin
477 X_LAST_UPDATE_DATE := SYSDATE;
478 if(X_MODE = 'I') then
479 X_LAST_UPDATED_BY := 1;
480 X_LAST_UPDATE_LOGIN := 0;
481 elsif (X_MODE = 'R') then
482 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
483 if X_LAST_UPDATED_BY is NULL then
484 X_LAST_UPDATED_BY := -1;
485 end if;
486 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
487 if X_LAST_UPDATE_LOGIN is NULL then
488 X_LAST_UPDATE_LOGIN := -1;
489 end if;
490 else
491 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
492 IGS_GE_MSG_STACK.ADD;
493 app_exception.raise_exception;
494 end if;
495 Before_DML(
496 p_action=>'INSERT',
497 x_rowid=>X_ROWID,
498 x_org_structure_id=>X_ORG_STRUCTURE_ID,
499 x_org_structure_type=>X_ORG_STRUCTURE_TYPE,
500 x_org_alternate_id_type=>X_ORG_ALTERNATE_ID_TYPE,
501 x_org_alternate_id=>X_ORG_ALTERNATE_ID,
502 x_start_date=>X_START_DATE,
503 x_end_date=>X_END_DATE,
504 x_creation_date=>X_LAST_UPDATE_DATE,
505 x_created_by=>X_LAST_UPDATED_BY,
506 x_last_update_date=>X_LAST_UPDATE_DATE,
507 x_last_updated_by=>X_LAST_UPDATED_BY,
508 x_last_update_login=>X_LAST_UPDATE_LOGIN);
509 insert into IGS_OR_ORG_ALT_IDS (
510 ORG_STRUCTURE_ID
511 ,ORG_STRUCTURE_TYPE
512 ,ORG_ALTERNATE_ID_TYPE
513 ,ORG_ALTERNATE_ID
514 ,START_DATE
515 ,END_DATE
516 ,CREATION_DATE
517 ,CREATED_BY
518 ,LAST_UPDATE_DATE
519 ,LAST_UPDATED_BY
520 ,LAST_UPDATE_LOGIN
521 ) values (
522 NEW_REFERENCES.ORG_STRUCTURE_ID
523 ,NEW_REFERENCES.ORG_STRUCTURE_TYPE
524 ,NEW_REFERENCES.ORG_ALTERNATE_ID_TYPE
525 ,NEW_REFERENCES.ORG_ALTERNATE_ID
526 ,NEW_REFERENCES.START_DATE
527 ,NEW_REFERENCES.END_DATE
528 ,X_LAST_UPDATE_DATE
529 ,X_LAST_UPDATED_BY
530 ,X_LAST_UPDATE_DATE
531 ,X_LAST_UPDATED_BY
532 ,X_LAST_UPDATE_LOGIN
533 );
534 open c;
535 fetch c into X_ROWID;
536 if (c%notfound) then
537 close c;
538 raise no_data_found;
539 end if;
540 close c;
541 After_DML (
542 p_action => 'INSERT' ,
543 x_rowid => X_ROWID );
544 end INSERT_ROW;
545 procedure LOCK_ROW (
546 X_ROWID in VARCHAR2,
547 x_ORG_STRUCTURE_ID IN VARCHAR2,
548 x_ORG_STRUCTURE_TYPE IN VARCHAR2,
549 x_ORG_ALTERNATE_ID_TYPE IN VARCHAR2,
550 x_ORG_ALTERNATE_ID IN VARCHAR2,
551 x_START_DATE IN DATE,
552 x_END_DATE IN DATE ) AS
553 /*************************************************************
554 Created By : sbonam@in
555 Date Created By : 2000/05/12
556 Purpose : This procedure is called from Forms during an
557 lock_row (ON_LOCK) operation.
558 Know limitations, enhancements or remarks
559 Change History
560 Who When What
561
562 (reverse chronological order - newest change first)
563 ***************************************************************/
564
565 cursor c1 is select
566 END_DATE
567 from IGS_OR_ORG_ALT_IDS
568 where ROWID = X_ROWID
569 for update nowait;
570 tlinfo c1%rowtype;
571 begin
572 open c1;
573 fetch c1 into tlinfo;
574 if (c1%notfound) then
575 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
576 IGS_GE_MSG_STACK.ADD;
577 close c1;
578 app_exception.raise_exception;
579 return;
580 end if;
581 close c1;
582 if ( ( (tlinfo.END_DATE = X_END_DATE)
583 OR ((tlinfo.END_DATE is null)
584 AND (X_END_DATE is null)))
585 ) then
586 null;
587 else
588 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
589 IGS_GE_MSG_STACK.ADD;
590 app_exception.raise_exception;
591 end if;
592 return;
593 end LOCK_ROW;
594 Procedure UPDATE_ROW (
595 X_ROWID in VARCHAR2,
596 x_ORG_STRUCTURE_ID IN VARCHAR2,
597 x_ORG_STRUCTURE_TYPE IN VARCHAR2,
598 x_ORG_ALTERNATE_ID_TYPE IN VARCHAR2,
599 x_ORG_ALTERNATE_ID IN VARCHAR2,
600 x_START_DATE IN DATE,
601 x_END_DATE IN DATE,
602 X_MODE in VARCHAR2 default 'R'
603 ) AS
604 /*************************************************************
605 Created By : sbonam@in.
606 Date Created By : 2000/05/12
607 Purpose : This procedure is called from Forms during an
608 insert_row (ON_UPDATE) operation. This procedure
609 checks if there is a row for the given primary key and
610 if there isn't one then inserts it.
611 Know limitations, enhancements or remarks
612 Change History
613 Who When What
614
615 (reverse chronological order - newest change first)
616 ***************************************************************/
617
618 X_LAST_UPDATE_DATE DATE ;
619 X_LAST_UPDATED_BY NUMBER ;
620 X_LAST_UPDATE_LOGIN NUMBER ;
621 begin
622 X_LAST_UPDATE_DATE := SYSDATE;
623 if(X_MODE = 'I') then
624 X_LAST_UPDATED_BY := 1;
625 X_LAST_UPDATE_LOGIN := 0;
626 elsif (X_MODE = 'R') then
627 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
628 if X_LAST_UPDATED_BY is NULL then
629 X_LAST_UPDATED_BY := -1;
630 end if;
631 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
632 if X_LAST_UPDATE_LOGIN is NULL then
633 X_LAST_UPDATE_LOGIN := -1;
634 end if;
635 else
636 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
637 IGS_GE_MSG_STACK.ADD;
638 app_exception.raise_exception;
639 end if;
640 Before_DML(
641 p_action=>'UPDATE',
642 x_rowid=>X_ROWID,
643 x_org_structure_id=>X_ORG_STRUCTURE_ID,
644 x_org_structure_type=>X_ORG_STRUCTURE_TYPE,
645 x_org_alternate_id_type=>X_ORG_ALTERNATE_ID_TYPE,
646 x_org_alternate_id=>X_ORG_ALTERNATE_ID,
647 x_start_date=>X_START_DATE,
648 x_end_date=>X_END_DATE,
649 x_creation_date=>X_LAST_UPDATE_DATE,
650 x_created_by=>X_LAST_UPDATED_BY,
651 x_last_update_date=>X_LAST_UPDATE_DATE,
652 x_last_updated_by=>X_LAST_UPDATED_BY,
653 x_last_update_login=>X_LAST_UPDATE_LOGIN);
654 update IGS_OR_ORG_ALT_IDS set
655 END_DATE = NEW_REFERENCES.END_DATE,
656 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
657 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
658 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
659 where ROWID = X_ROWID;
660 if (sql%notfound) then
661 raise no_data_found;
662 end if;
663
664 After_DML (
665 p_action => 'UPDATE' ,
666 x_rowid => X_ROWID
667 );
668 end UPDATE_ROW;
669 procedure ADD_ROW (
670 X_ROWID in out NOCOPY VARCHAR2,
671 x_ORG_STRUCTURE_ID IN VARCHAR2,
672 x_ORG_STRUCTURE_TYPE IN VARCHAR2,
673 x_ORG_ALTERNATE_ID_TYPE IN VARCHAR2,
674 x_ORG_ALTERNATE_ID IN VARCHAR2,
675 x_START_DATE IN DATE,
676 x_END_DATE IN DATE,
677 X_MODE in VARCHAR2 default 'R'
678 ) AS
679 /*************************************************************
680 Created By : sbonam@in
681 Date Created By : 2000/05/12
682 Purpose : This procedure is called from Forms during an
683 insert_row (ON_INSERT) operation. This procedure
684 checks if there is a row for the given primary key and
685 if there isn't one then inserts it.
686 Know limitations, enhancements or remarks
687 Change History
688 Who When What
689
690 (reverse chronological order - newest change first)
691 ***************************************************************/
692
693 cursor c1 is select ROWID from IGS_OR_ORG_ALT_IDS
694 where ORG_ALTERNATE_ID= X_ORG_ALTERNATE_ID
695 and ORG_ALTERNATE_ID_TYPE = X_ORG_ALTERNATE_ID_TYPE
696 and ORG_STRUCTURE_ID = X_ORG_STRUCTURE_ID
697 and ORG_STRUCTURE_TYPE = X_ORG_STRUCTURE_TYPE
698 and START_DATE = X_START_DATE
699 ;
700 begin
701 open c1;
702 fetch c1 into X_ROWID;
703 if (c1%notfound) then
704 close c1;
705 INSERT_ROW (
706 X_ROWID,
707 X_ORG_STRUCTURE_ID,
708 X_ORG_STRUCTURE_TYPE,
709 X_ORG_ALTERNATE_ID_TYPE,
710 X_ORG_ALTERNATE_ID,
711 X_START_DATE,
712 X_END_DATE,
713 X_MODE );
714 return;
715 end if;
716 close c1;
717 UPDATE_ROW (
718 X_ROWID,
719 X_ORG_STRUCTURE_ID,
720 X_ORG_STRUCTURE_TYPE,
721 X_ORG_ALTERNATE_ID_TYPE,
722 X_ORG_ALTERNATE_ID,
723 X_START_DATE,
724 X_END_DATE,
725 X_MODE );
726 end ADD_ROW;
727
728
729 PROCEDURE DELETE_ROW (
730 X_ROWID in VARCHAR2
731 ) AS
732 /*************************************************************
733 Created By : sbonam@in
734 Date Created By : 2000/05/12
735 Purpose : This procedure is called from Forms during an
736 delete_row (ON_DELETE) operation.
737 Know limitations, enhancements or remarks
738 Change History
739 Who When What
740 skpandey 28-MAR-2006 Bug#:3634881, RECORD ALREADY EXISTS WHEN INSERTING A NEW ORG ALTERNATE ID, DUPLICATE AT INST
741 (reverse chronological order - newest change first)
742 ***************************************************************/
743 --Check Duplicate record and then delete
744 CURSOR cur_get_rowid(cp_org_alt_id_typ igs_or_cwlk_dtl.alt_id_type%TYPE, cp_alt_id_val igs_or_cwlk_dtl.alt_id_value%TYPE, cp_rowid ROWID) IS
745 SELECT rowid
746 FROM igs_or_cwlk_dtl
747 WHERE alt_id_type = cp_org_alt_id_typ
748 AND alt_id_value = cp_alt_id_val
749 AND NOT EXISTS ( SELECT 1
750 FROM igs_or_org_alt_ids
751 WHERE org_alternate_id_type = alt_id_type
752 AND org_alternate_id = alt_id_value
753 AND rowid <> cp_rowid);
754 l_row_id ROWID;
755
756 BEGIN
757 Before_DML (
758 p_action => 'DELETE',
759 x_rowid => X_ROWID
760 );
761
762 OPEN cur_get_rowid(old_references.org_alternate_id_type, old_references.org_alternate_id, X_ROWID );
763 FETCH cur_get_rowid INTO l_row_id;
764 CLOSE cur_get_rowid;
765
766 delete from IGS_OR_ORG_ALT_IDS
767 where ROWID = X_ROWID;
768 if (sql%notfound) then
769 raise no_data_found;
770 end if;
771
772 After_DML (
773 p_action => 'DELETE',
774 x_rowid => X_ROWID
775 );
776 IF l_row_id IS NOT NULL THEN
777 igs_or_cwlk_dtl_pkg.delete_row(x_rowid => l_row_id);
778 END IF;
779
780 END DELETE_ROW;
781
782 END igs_or_org_alt_ids_pkg;
[Home] [Help]