1 PACKAGE BODY igs_ad_interface_ctl_pkg AS
2 /* $Header: IGSAIA8B.pls 115.15 2003/12/09 12:45:08 pbondugu ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_INTERFACE_CTL%RowType;
5 new_references IGS_AD_INTERFACE_CTL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_interface_run_id IN NUMBER,
11 x_source_type_id IN NUMBER ,
12 x_batch_id IN NUMBER ,
13 x_match_set_id IN NUMBER ,
14 x_status IN VARCHAR2 ,
15 x_creation_date IN DATE ,
16 x_created_by IN NUMBER ,
17 x_last_update_date IN DATE ,
18 x_last_updated_by IN NUMBER ,
19 x_last_update_login IN NUMBER
20 ) AS
21
22 /*************************************************************
23 Created By :
24 Date Created By :
25 Purpose :
26 Know limitations, enhancements or remarks
27 Change History
28 Who When What
29
30 (reverse chronological order - newest change first)
31 ***************************************************************/
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_AD_INTERFACE_CTL
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 Open cur_old_ref_values;
45 Fetch cur_old_ref_values INTO old_references;
46 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
47 Close cur_old_ref_values;
48 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
49 IGS_GE_MSG_STACK.ADD;
50 App_Exception.Raise_Exception;
51 Return;
52 END IF;
53 Close cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.interface_run_id := x_interface_run_id;
57 new_references.source_type_id := x_source_type_id;
58 new_references.batch_id := x_batch_id;
59 new_references.match_set_id := x_match_set_id;
60
61 IF x_status IS NOT NULL THEN
62 new_references.status := x_status;
63 ELSE
64 new_references.status := '2';
65 END IF;
66
67
68 IF (p_action = 'UPDATE') THEN
69 new_references.creation_date := old_references.creation_date;
70 new_references.created_by := old_references.created_by;
71 ELSE
72 new_references.creation_date := x_creation_date;
73 new_references.created_by := x_created_by;
74 END IF;
75
76 new_references.last_update_date := x_last_update_date;
77 new_references.last_updated_by := x_last_updated_by;
78 new_references.last_update_login := x_last_update_login;
79
80 END Set_Column_Values;
81
82 PROCEDURE Check_Constraints (
83 Column_Name IN VARCHAR2 ,
84 Column_Value IN VARCHAR2 ) AS
85 /*************************************************************
86 Created By :
87 Date Created By :
88 Purpose :
89 Know limitations, enhancements or remarks
90 Change History
91 Who When What
92 pkpatel 14-MAY-2002 Bug No: 2373399
93 Modified to check the Status value among 1,2,3,4 instead of E,P,C
94 (reverse chronological order - newest change first)
95 ***************************************************************/
96
97 BEGIN
98 IF column_name IS NULL THEN
99 NULL;
100 ELSIF UPPER(column_name) = 'STATUS' THEN
101 new_references.status := column_value;
102 NULL;
103 END IF;
104
105 -- The following code checks for check constraints on the Columns.
106 IF Upper(Column_Name) = 'STATUS' OR
107 Column_Name IS NULL THEN
108 IF NOT (new_references.status in ('1','2','3','4')) THEN
109 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
110 IGS_GE_MSG_STACK.ADD;
111 App_Exception.Raise_Exception;
112 END IF;
113 END IF;
114
115 END Check_Constraints;
116
117
118 PROCEDURE Check_Parent_Existance AS
119 /*************************************************************
120 Created By :
121 Date Created By :
122 Purpose :
123 Know limitations, enhancements or remarks
124 Change History
125 Who When What
126 rrengara 4-oct-2002 Removed the checks for admission cateogry, all calendars, funnel status and person types
127 (reverse chronological order - newest change first)
128 ***************************************************************/
129
130 BEGIN
131
132 IF (((old_references.source_type_id = new_references.source_type_id)) OR
133 ((new_references.source_type_id IS NULL))) THEN
134 NULL;
135 ELSIF NOT Igs_Pe_Src_Types_Pkg.Get_PK_For_Validation (
136 new_references.source_type_id
137 ) THEN
138 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
139 IGS_GE_MSG_STACK.ADD;
140 App_Exception.Raise_Exception;
141 END IF;
142
143 IF (((old_references.match_set_id = new_references.match_set_id)) OR
144 ((new_references.match_set_id IS NULL))) THEN
145 NULL;
146 ELSIF NOT Igs_Pe_Match_Sets_Pkg.Get_PK_For_Validation (
147 new_references.match_set_id
148 ) 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 Check_Parent_Existance;
154
155
156 FUNCTION Get_PK_For_Validation (
157 x_interface_run_id IN NUMBER
158 ) RETURN BOOLEAN AS
159
160 /*************************************************************
161 Created By :
162 Date Created By :
163 Purpose :
164 Know limitations, enhancements or remarks
165 Change History
166 Who When What
167
168 (reverse chronological order - newest change first)
169 ***************************************************************/
170
171 CURSOR cur_rowid IS
172 SELECT rowid
173 FROM IGS_AD_INTERFACE_CTL
174 WHERE interface_run_id = x_interface_run_id
175 FOR UPDATE NOWAIT;
176
177 lv_rowid cur_rowid%RowType;
178
179 BEGIN
180 Open cur_rowid;
181 Fetch cur_rowid INTO lv_rowid;
182 IF (cur_rowid%FOUND) THEN
183 Close cur_rowid;
184 Return(TRUE);
185 ELSE
186 Close cur_rowid;
187 Return(FALSE);
188 END IF;
189 END Get_PK_For_Validation;
190
191
192
193
194
195 PROCEDURE Get_FK_Igs_Pe_Src_Types (
196 x_source_type_id IN NUMBER
197 ) AS
198 /*************************************************************
199 Created By : samaresh
200 Date Created By : 15-May-2000
201 Purpose :
202 Know limitations, enhancements or remarks
203 Change History
204 Who When What
205
206 (reverse chronological order - newest change first)
207 ***************************************************************/
208
209 CURSOR cur_rowid IS
210 SELECT rowid
211 FROM IGS_AD_INTERFACE_CTL
212 WHERE source_type_id = x_source_type_id ;
213
214 lv_rowid cur_rowid%RowType;
215
216 BEGIN
217
218 Open cur_rowid;
219 Fetch cur_rowid INTO lv_rowid;
220 IF (cur_rowid%FOUND) THEN
221 Close cur_rowid;
222 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AIC_PST_FK');
223 IGS_GE_MSG_STACK.ADD;
224 App_Exception.Raise_Exception;
225 Return;
226 END IF;
227 Close cur_rowid;
228
229 END Get_FK_Igs_Pe_Src_Types;
230
231
232 PROCEDURE Get_FK_Igs_Pe_Match_Sets (
233 x_match_set_id IN NUMBER
234 ) AS
235 /*************************************************************
236 Created By : samaresh
237 Date Created By : 15-May-2000
238 Purpose :
239 Know limitations, enhancements or remarks
240 Change History
241 Who When What
242
243 (reverse chronological order - newest change first)
244 ***************************************************************/
245
246 CURSOR cur_rowid IS
247 SELECT rowid
248 FROM IGS_AD_INTERFACE_CTL
249 WHERE match_set_id = x_match_set_id ;
250
251 lv_rowid cur_rowid%RowType;
252
253 BEGIN
254
255 Open cur_rowid;
256 Fetch cur_rowid INTO lv_rowid;
257 IF (cur_rowid%FOUND) THEN
258 Close cur_rowid;
259 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AIC_PMS_FK');
260 IGS_GE_MSG_STACK.ADD;
261 App_Exception.Raise_Exception;
262 Return;
263 END IF;
264 Close cur_rowid;
265
266 END Get_FK_Igs_Pe_Match_Sets;
267
268 PROCEDURE Before_DML (
269 p_action IN VARCHAR2,
270 x_rowid IN VARCHAR2 ,
271 x_interface_run_id IN NUMBER ,
272 x_source_type_id IN NUMBER ,
273 x_batch_id IN NUMBER ,
274 x_match_set_id IN NUMBER ,
275 x_status IN VARCHAR2 ,
276 x_creation_date IN DATE ,
277 x_created_by IN NUMBER ,
278 x_last_update_date IN DATE ,
279 x_last_updated_by IN NUMBER ,
280 x_last_update_login IN NUMBER
281 ) AS
282 /*************************************************************
283 Created By :
284 Date Created By :
285 Purpose :
286 Know limitations, enhancements or remarks
287 Change History
288 Who When What
289
290 (reverse chronological order - newest change first)
291 ***************************************************************/
292
293 BEGIN
294
295 Set_Column_Values (
296 p_action,
297 x_rowid,
298 x_interface_run_id,
299 x_source_type_id,
300 x_batch_id,
301 x_match_set_id,
302 x_status,
303 x_creation_date,
304 x_created_by,
305 x_last_update_date,
306 x_last_updated_by,
307 x_last_update_login
308 );
309
310 IF (p_action = 'INSERT') THEN
311 -- Call all the procedures related to Before Insert.
312 Null;
313 IF Get_Pk_For_Validation(
314 new_references.interface_run_id) THEN
315 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
316 IGS_GE_MSG_STACK.ADD;
317 App_Exception.Raise_Exception;
318 END IF;
319 Check_Constraints;
320 Check_Parent_Existance;
321 ELSIF (p_action = 'UPDATE') THEN
322 -- Call all the procedures related to Before Update.
323 Null;
324 Check_Constraints;
325 Check_Parent_Existance;
326 ELSIF (p_action = 'DELETE') THEN
327 -- Call all the procedures related to Before Delete.
328 Null;
329 ELSIF (p_action = 'VALIDATE_INSERT') THEN
330 -- Call all the procedures related to Before Insert.
331 IF Get_PK_For_Validation (
332 new_references.interface_run_id) THEN
333 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
334 IGS_GE_MSG_STACK.ADD;
335 App_Exception.Raise_Exception;
336 END IF;
337 Check_Constraints;
338 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
339 Check_Constraints;
340 ELSIF (p_action = 'VALIDATE_DELETE') THEN
341 Null;
342 END IF;
343
344 END Before_DML;
345
346
347 PROCEDURE After_DML (
348 p_action IN VARCHAR2,
349 x_rowid IN VARCHAR2
350 ) IS
351 /*************************************************************
352 Created By :
353 Date Created By :
354 Purpose :
355 Know limitations, enhancements or remarks
356 Change History
357 Who When What
358
359 (reverse chronological order - newest change first)
360 ***************************************************************/
361
362 BEGIN
363
364 l_rowid := x_rowid;
365 IF (p_action = 'INSERT') THEN
366 -- Call all the procedures related to After Insert.
367 Null;
368 ELSIF (p_action = 'UPDATE') THEN
369 -- Call all the procedures related to After Update.
370 Null;
371 ELSIF (p_action = 'DELETE') THEN
372 -- Call all the procedures related to After Delete.
373 Null;
374 END IF;
375
376 END After_DML;
377
378 procedure INSERT_ROW (
379 X_ROWID in out NOCOPY VARCHAR2,
380 x_INTERFACE_RUN_ID IN OUT NOCOPY NUMBER,
381 x_SOURCE_TYPE_ID IN NUMBER,
382 x_BATCH_ID IN NUMBER,
383 x_MATCH_SET_ID IN NUMBER,
384 x_STATUS IN VARCHAR2,
385 X_MODE in VARCHAR2
386 ) AS
387 /*************************************************************
388 Created By :
389 Date Created By :
390 Purpose :
391 Know limitations, enhancements or remarks
392 Change History
393 Who When What
394 rrengara 4-OCT-2002 Passed null to admission category, calendar instance, person type and funnel status for the Build 2604395
395 (reverse chronological order - newest change first)
396 ***************************************************************/
397
398 cursor C is select ROWID from IGS_AD_INTERFACE_CTL
399 where INTERFACE_RUN_ID= X_INTERFACE_RUN_ID;
400
401 X_LAST_UPDATE_DATE DATE ;
402 X_LAST_UPDATED_BY NUMBER ;
403 X_LAST_UPDATE_LOGIN NUMBER ;
404 X_REQUEST_ID NUMBER;
405 X_PROGRAM_ID NUMBER;
406 X_PROGRAM_APPLICATION_ID NUMBER;
407 X_PROGRAM_UPDATE_DATE DATE;
408 begin
409 X_LAST_UPDATE_DATE := SYSDATE;
410 if(X_MODE = 'I') then
411 X_LAST_UPDATED_BY := 1;
412 X_LAST_UPDATE_LOGIN := 0;
413 elsif (X_MODE = 'R') then
414 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
415 if X_LAST_UPDATED_BY is NULL then
416 X_LAST_UPDATED_BY := -1;
417 end if;
418 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
419 if X_LAST_UPDATE_LOGIN is NULL then
420 X_LAST_UPDATE_LOGIN := -1;
421 end if;
422 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
423 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
424 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
425 if (X_REQUEST_ID = -1) then
426 X_REQUEST_ID := NULL;
427 X_PROGRAM_ID := NULL;
428 X_PROGRAM_APPLICATION_ID := NULL;
429 X_PROGRAM_UPDATE_DATE := NULL;
430 else
431 X_PROGRAM_UPDATE_DATE := SYSDATE;
432 end if;
433 else
434 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
435 IGS_GE_MSG_STACK.ADD;
436 app_exception.raise_exception;
437 end if;
438
439 X_INTERFACE_RUN_ID := -1;
440 Before_DML(
441 p_action=>'INSERT',
442 x_rowid=>X_ROWID,
443 x_interface_run_id=>X_INTERFACE_RUN_ID,
444 x_source_type_id=>X_SOURCE_TYPE_ID,
445 x_batch_id=>X_BATCH_ID,
446 x_match_set_id=>X_MATCH_SET_ID,
447 x_status=>X_STATUS,
448 x_creation_date=>X_LAST_UPDATE_DATE,
449 x_created_by=>X_LAST_UPDATED_BY,
450 x_last_update_date=>X_LAST_UPDATE_DATE,
451 x_last_updated_by=>X_LAST_UPDATED_BY,
452 x_last_update_login=>X_LAST_UPDATE_LOGIN
453 );
454
455 INSERT INTO IGS_AD_INTERFACE_CTL (
456 INTERFACE_RUN_ID
457 ,SOURCE_TYPE_ID
461 ,CREATION_DATE
458 ,BATCH_ID
459 ,MATCH_SET_ID
460 ,STATUS
462 ,CREATED_BY
463 ,LAST_UPDATE_DATE
464 ,LAST_UPDATED_BY
465 ,LAST_UPDATE_LOGIN
466 ,REQUEST_ID
467 ,PROGRAM_ID
468 ,PROGRAM_APPLICATION_ID
469 ,PROGRAM_UPDATE_DATE
470 ) values (
471 IGS_AD_INTERFACE_CTL_S.NEXTVAL
472 ,NEW_REFERENCES.SOURCE_TYPE_ID
473 ,NEW_REFERENCES.BATCH_ID
474 ,NEW_REFERENCES.MATCH_SET_ID
475 ,NEW_REFERENCES.STATUS
476 ,X_LAST_UPDATE_DATE
477 ,X_LAST_UPDATED_BY
478 ,X_LAST_UPDATE_DATE
479 ,X_LAST_UPDATED_BY
480 ,X_LAST_UPDATE_LOGIN
481 ,X_REQUEST_ID
482 ,X_PROGRAM_ID
483 ,X_PROGRAM_APPLICATION_ID
484 ,X_PROGRAM_UPDATE_DATE
485 )RETURNING INTERFACE_RUN_ID INTO X_INTERFACE_RUN_ID;
486
487 OPEN c;
488 FETCH c INTO X_ROWID;
489 IF (c%notfound) THEN
490 CLOSE c;
491 RAISE no_data_found;
492 END IF;
493 CLOSE C;
494
495 After_DML (
496 p_action => 'INSERT' ,
497 x_rowid => X_ROWID );
498 end INSERT_ROW;
499
500 procedure LOCK_ROW (
501 X_ROWID in VARCHAR2,
502 x_INTERFACE_RUN_ID IN NUMBER,
503 x_SOURCE_TYPE_ID IN NUMBER,
504 x_BATCH_ID IN NUMBER,
505 x_MATCH_SET_ID IN NUMBER,
506 x_STATUS IN VARCHAR2
507 ) AS
508 /*************************************************************
509 Created By :
510 Date Created By :
511 Purpose :
512 Know limitations, enhancements or remarks
513 Change History
514 Who When What
515 rrengara 4-OCT-2002 Removed the references to columns admission cat, calendar instance values and person type and funnel status
516 (reverse chronological order - newest change first)
517 ***************************************************************/
518
519 CURSOR c1 IS SELECT
520 SOURCE_TYPE_ID,
521 BATCH_ID,
522 MATCH_SET_ID,
523 STATUS
524 FROM IGS_AD_INTERFACE_CTL
525 WHERE ROWID = X_ROWID
526 for update nowait;
527
528 tlinfo c1%rowtype;
529
530 begin
531 OPEN c1;
532 FETCH c1 INTO tlinfo;
533 IF (c1%notfound) THEN
534 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
535 IGS_GE_MSG_STACK.ADD;
536 CLOSE c1;
537 APP_EXCEPTION.RAISE_EXCEPTION;
538 RETURN;
539 END IF;
540 CLOSE C1;
541
542 IF ( ( tlinfo.SOURCE_TYPE_ID = X_SOURCE_TYPE_ID)
543 AND (tlinfo.BATCH_ID = X_BATCH_ID)
544 AND (tlinfo.MATCH_SET_ID = X_MATCH_SET_ID)
545 AND ((tlinfo.STATUS = X_STATUS)
546 OR ((tlinfo.STATUS is null)
547 AND (X_STATUS is null)))
548 ) THEN
549 NULL;
550 ELSE
551 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
552 IGS_GE_MSG_STACK.ADD;
553 app_exception.raise_exception;
554 END IF;
555 RETURN;
556 end LOCK_ROW;
557
558
559 Procedure UPDATE_ROW (
560 X_ROWID in VARCHAR2,
561 x_INTERFACE_RUN_ID IN NUMBER,
562 x_SOURCE_TYPE_ID IN NUMBER,
563 x_BATCH_ID IN NUMBER,
564 x_MATCH_SET_ID IN NUMBER,
565 x_STATUS IN VARCHAR2,
566 X_MODE in VARCHAR2
567 ) AS
568 /*************************************************************
569 Created By :
570 Date Created By :
571 Purpose :
572 Know limitations, enhancements or remarks
573 Change History
574 Who When What
575 rrengara 4-OCT-2002 Passed null to admission category, calendar instance, person type and funnel status for the Build 2604395
576 (reverse chronological order - newest change first)
577 ***************************************************************/
578
579 X_LAST_UPDATE_DATE DATE ;
580 X_LAST_UPDATED_BY NUMBER ;
581 X_LAST_UPDATE_LOGIN NUMBER ;
582 X_REQUEST_ID NUMBER;
583 X_PROGRAM_ID NUMBER;
584 X_PROGRAM_APPLICATION_ID NUMBER;
585 X_PROGRAM_UPDATE_DATE DATE;
586 begin
587 X_LAST_UPDATE_DATE := SYSDATE;
588 if(X_MODE = 'I') then
589 X_LAST_UPDATED_BY := 1;
590 X_LAST_UPDATE_LOGIN := 0;
591 elsif (X_MODE = 'R') then
592 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
593 if X_LAST_UPDATED_BY is NULL then
594 X_LAST_UPDATED_BY := -1;
595 end if;
596 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
597 if X_LAST_UPDATE_LOGIN is NULL then
598 X_LAST_UPDATE_LOGIN := -1;
599 end if;
600 else
601 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
602 IGS_GE_MSG_STACK.ADD;
603 app_exception.raise_exception;
604 end if;
605
606 Before_DML(
607 p_action=>'UPDATE',
608 x_rowid=>X_ROWID,
609 x_interface_run_id=>X_INTERFACE_RUN_ID,
610 x_source_type_id=>X_SOURCE_TYPE_ID,
611 x_batch_id=>X_BATCH_ID,
615 x_created_by=>X_LAST_UPDATED_BY,
612 x_match_set_id=>X_MATCH_SET_ID,
613 x_status=>X_STATUS,
614 x_creation_date=>X_LAST_UPDATE_DATE,
616 x_last_update_date=>X_LAST_UPDATE_DATE,
617 x_last_updated_by=>X_LAST_UPDATED_BY,
618 x_last_update_login=>X_LAST_UPDATE_LOGIN
619 );
620
621 if (X_MODE = 'R') then
622 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
623 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
624 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
625 if (X_REQUEST_ID = -1) then
626 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
627 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
628 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
629 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
630 else
631 X_PROGRAM_UPDATE_DATE := SYSDATE;
632 end if;
633 end if;
634
635 update IGS_AD_INTERFACE_CTL set
636 SOURCE_TYPE_ID = NEW_REFERENCES.SOURCE_TYPE_ID,
637 BATCH_ID = NEW_REFERENCES.BATCH_ID,
638 MATCH_SET_ID = NEW_REFERENCES.MATCH_SET_ID,
639 STATUS = NEW_REFERENCES.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 REQUEST_ID = X_REQUEST_ID,
644 PROGRAM_ID = X_PROGRAM_ID,
645 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
646 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
647 where ROWID = X_ROWID;
648
649 if (sql%notfound) then
650 raise no_data_found;
651 end if;
652
653 After_DML (
654 p_action => 'UPDATE' ,
655 x_rowid => X_ROWID
656 );
657 end UPDATE_ROW;
658
659 procedure ADD_ROW (
660 X_ROWID in out NOCOPY VARCHAR2,
661 x_INTERFACE_RUN_ID IN OUT NOCOPY NUMBER,
662 x_SOURCE_TYPE_ID IN NUMBER,
663 x_BATCH_ID IN NUMBER,
664 x_MATCH_SET_ID IN NUMBER,
665 x_STATUS IN VARCHAR2,
666 X_MODE in VARCHAR2
667 ) AS
668 /*************************************************************
669 Created By :
670 Date Created By :
671 Purpose :
672 Know limitations, enhancements or remarks
673 Change History
674 Who When What
675
676 (reverse chronological order - newest change first)
677 ***************************************************************/
678
679 cursor c1 is select ROWID from IGS_AD_INTERFACE_CTL
680 where INTERFACE_RUN_ID= X_INTERFACE_RUN_ID;
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_INTERFACE_RUN_ID,
690 X_SOURCE_TYPE_ID,
691 X_BATCH_ID,
692 X_MATCH_SET_ID,
693 X_STATUS,
694 X_MODE );
695 return;
696 end if;
697 close c1;
698
699 UPDATE_ROW (
700 X_ROWID,
701 X_INTERFACE_RUN_ID,
702 X_SOURCE_TYPE_ID,
703 X_BATCH_ID,
704 X_MATCH_SET_ID,
705 X_STATUS,
706 X_MODE );
707
708 end ADD_ROW;
709
710 procedure DELETE_ROW (
711 X_ROWID in VARCHAR2
712 ) AS
713 /*************************************************************
714 Created By :
715 Date Created By :
716 Purpose :
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_AD_INTERFACE_CTL
730 where ROWID = X_ROWID;
731 if (sql%notfound) then
732 raise no_data_found;
733 end if;
734
735 After_DML (
736 p_action => 'DELETE',
737 x_rowid => X_ROWID
738 );
739
740 end DELETE_ROW;
741
742 END igs_ad_interface_ctl_pkg;
[Home] [Help]