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