1 PACKAGE BODY igs_pe_src_types_pkg AS
2 /* $Header: IGSNI65B.pls 120.0 2005/06/01 12:36:23 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_pe_src_types_all%RowType;
5 new_references igs_pe_src_types_all%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_source_type_id IN NUMBER DEFAULT NULL,
11 x_source_type IN VARCHAR2 DEFAULT NULL,
12 x_description IN VARCHAR2 DEFAULT NULL,
13 x_system_source_type IN VARCHAR2 DEFAULT NULL,
14 x_admission_cat IN VARCHAR2 DEFAULT NULL,
15 x_closed_ind IN VARCHAR2 DEFAULT NULL,
16 x_person_type_code IN VARCHAR2 DEFAULT NULL,
17 x_enquiry_source_type IN VARCHAR2 DEFAULT NULL,
18 x_funnel_status IN VARCHAR2 DEFAULT NULL,
19 x_inq_entry_stat_id IN NUMBER DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL ,
25 X_ORG_ID in NUMBER default NULL,
26 X_INQUIRY_TYPE_ID IN NUMBER DEFAULT NULL
27 ) AS
28 /*************************************************************
29 Created By :SVISWEAS
30 Date Created By :11-MAY-2000
31 Purpose :
32 Know limitations, enhancements or remarks
33 Change History
34 Who When What
35
36 (reverse chronological order - newest change first)
37 pbondugu 26-Feb-2003 new_references. admission_Cat is set to NULL
38 ***************************************************************/
39
40 CURSOR cur_old_ref_values IS
41 SELECT *
42 FROM igs_pe_src_types_all
43 WHERE rowid = x_rowid;
44
45 BEGIN
46
47 l_rowid := x_rowid;
48
49 -- Code for setting the Old and New Reference Values.
50 -- Populate Old Values.
51 Open cur_old_ref_values;
52 Fetch cur_old_ref_values INTO old_references;
53 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
54 Close cur_old_ref_values;
55 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
56 IGS_GE_MSG_STACK.ADD;
57 App_Exception.Raise_Exception;
58 Return;
59 END IF;
60 Close cur_old_ref_values;
61
62 -- Populate New Values.
63 new_references.source_type_id := x_source_type_id;
64 new_references.source_type := x_source_type;
65 new_references.description := x_description;
66 new_references.system_source_type := x_system_source_type;
67 new_references.admission_cat := NULL;
68 new_references.closed_ind := x_closed_ind;
69 new_references.person_type_code := x_person_type_code;
70 new_references.enquiry_source_type := x_enquiry_source_type;
71 new_references.funnel_status := x_funnel_status ;
72 new_references.inq_entry_stat_id := x_inq_entry_stat_id ;
73 new_references.org_id := x_org_id;
74 new_references.inquiry_type_id := X_INQUIRY_TYPE_ID;
75 IF (p_action = 'UPDATE') THEN
76 new_references.creation_date := old_references.creation_date;
77 new_references.created_by := old_references.created_by;
78 ELSE
79 new_references.creation_date := x_creation_date;
80 new_references.created_by := x_created_by;
81 END IF;
82 new_references.last_update_date := x_last_update_date;
83 new_references.last_updated_by := x_last_updated_by;
84 new_references.last_update_login := x_last_update_login;
85
86 END Set_Column_Values;
87
88 PROCEDURE Check_Constraints (
89 Column_Name IN VARCHAR2 DEFAULT NULL,
90 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
91 /*************************************************************
92 Created By :SVISWEAS
93 Date Created By :11-MAY-2000
94 Purpose :
95 Know limitations, enhancements or remarks
96 Change History
97 Who When What
98
99 (reverse chronological order - newest change first)
100 ***************************************************************/
101 BEGIN
102 IF column_name IS NULL THEN
103 NULL;
104 ELSIF UPPER(column_name) = 'CLOSED_IND' THEN
105 new_references.closed_ind := column_value;
106 NULL;
107 END IF;
108
109
110 -- The following code checks for check constraints on the Columns.
111 IF Upper(Column_Name) = 'CLOSED_IND' OR
112 Column_Name IS NULL THEN
113 IF NOT (new_references.closed_ind IN ('Y', 'N')) THEN
114 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
115 IGS_GE_MSG_STACK.ADD;
116 App_Exception.Raise_Exception;
117 END IF;
118 END IF;
119 END Check_Constraints;
120
121
122
123 FUNCTION Get_PK_For_Validation (
124 x_source_type_id IN NUMBER
125 ) RETURN BOOLEAN AS
126 /*************************************************************
127 Created By :SVISWEAS
128 Date Created By :11-MAY-2000
129 Purpose :
130 Know limitations, enhancements or remarks
131 Change History
132 Who When What
133
134 (reverse chronological order - newest change first)
135 ***************************************************************/
136
137 CURSOR cur_rowid IS
138 SELECT rowid
139 FROM igs_pe_src_types_all
140 WHERE source_type_id = x_source_type_id
141 FOR UPDATE NOWAIT;
142
143 lv_rowid cur_rowid%RowType;
144
145 BEGIN
146
147 Open cur_rowid;
148 Fetch cur_rowid INTO lv_rowid;
149 IF (cur_rowid%FOUND) THEN
150 Close cur_rowid;
151 Return(TRUE);
152 ELSE
153 Close cur_rowid;
154 Return(FALSE);
155 END IF;
156 END Get_PK_For_Validation;
157
158 PROCEDURE BEFORE_UPDATE_DELETE AS
159
160 /*************************************************************
161 Created By :amuthu
162 Date Created By :19-MAY-2000
163 Purpose : To delete the rows that where add during
164 insert through IGS_AD_SRC_CAT_INSERT.
165
166 Know limitations, enhancements or remarks
167 Change History
168
169 Who When What
170 rasingh 19-JUL-2001 DLD: Interface to Academic History:
171 'TRANSCRIPT' added to the list of system_source_type
172 rghosh 14-Feb-2003 removed the source type SS_ADM_APPL for bug #2422183
173 (reverse chronological order - newest change first)
174 ***************************************************************/
175 CURSOR c_asc IS
176 SELECT rowid
177 FROM IGS_AD_SOURCE_CAT
178 WHERE source_type_id = old_references.source_type_id;
179 BEGIN
180 IF old_references.system_source_type in ('APPLICATION',
181 'TEST_RESULTS',
182 'PROSPECT_LIST',
183 'PROSPECT_SS_WEB_INQUIRY',
184 'TRANSCRIPT') THEN -- removed the source type SS_ADM_APPL for bug #2422183 (rghosh)
185 FOR c_asc_rec IN c_asc
186 LOOP
187 IGS_AD_SOURCE_CAT_PKG.DELETE_ROW(
188 c_asc_rec.rowid
189 );
190 END LOOP;
191 END IF;
192
193 END BEFORE_UPDATE_DELETE;
194
195 PROCEDURE Check_Child_Existance AS
196 /*************************************************************
197 Created By :SVISWEAS
198 Date Created By :27-MAY-2000
199 Purpose : Check_Child_Existance
200 Know limitations, enhancements or remarks
201 Change History
202 Who When What
203 Aiyer 04-Feb-2003 Modified for the bug 2664699
204 Replaced call to IGS_AD_I_ENTRY_STATS_PKG.GET_FK_FOR_VALIDATION
205 with IGS_RC_I_ENT_STATS_PKG.GET_FK_FOR_VALIDATION
206 pkpatel 10-JUN-2003 Bug 2996726
207 Added the call igs_pe_match_sets_pkg.get_fk_igs_pe_src_types
208 (reverse chronological order - newest change first)
209
210 ***************************************************************/
211 BEGIN
212 igs_ad_interface_ctl_pkg.get_fk_igs_pe_src_types (
213 old_references.source_type_id
214 );
215
216 IF NVL(fnd_profile.value('IGS_RECRUITING_ENABLED'), 'N') = 'Y' THEN
217 EXECUTE IMMEDIATE
218 'begin igr_i_inquiry_types_pkg.get_fk_igs_pe_src_types ( :1 ); end;'
219 USING old_references.source_type_id;
220 END IF;
221
222 igs_pe_match_sets_pkg.get_fk_igs_pe_src_types (
223 old_references.source_type_id
224 );
225 END Check_Child_Existance;
226
227
228 PROCEDURE Check_Parent_Existance AS
229 /*************************************************************
230 Created By : sraj
231 Date Created By : 2000/05/13
232 Purpose : To check the master records exists before inserting.
233 Know limitations, enhancements or remarks
234 Change History
235 Who When What
236
237 (reverse chronological order - newest change first)
238 pbondugu 26_feb-2003 Nullified
239 ***************************************************************/
240
241 BEGIN
242 NULL;
243 /******* Commented as part of bug 2422183
244 IF (((old_references.admission_cat = new_references.admission_cat)) OR
245 ((new_references.admission_cat IS NULL))) THEN
246 NULL;
247 ELSIF NOT Igs_Ad_Cat_pkg.Get_PK_For_Validation (
248 new_references.admission_cat
249 ) THEN
250 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 END IF;
254 Commented as part of bug 2422183 *******/
255 END Check_Parent_Existance;
256
257
258 FUNCTION Get_UK_For_Validation (
259 x_source_type IN VARCHAR2
260 ) RETURN BOOLEAN AS
261 /*************************************************************
262 Created By :SVISWEAS
263 Date Created By :27-MAY-2000
264 Purpose : Get_UK_For_Validation
265 Know limitations, enhancements or remarks
266 Change History
267 Who When What
268
269 (reverse chronological order - newest change first)
270 ***************************************************************/
271 CURSOR cur_rowid IS
272 SELECT rowid
273 FROM igs_pe_src_types_all
274 WHERE source_type = x_source_type
275 and ((l_rowid is null) or (rowid <> l_rowid));
276
277 lv_rowid cur_rowid%RowType;
278
279 BEGIN
280
281 Open cur_rowid;
282 Fetch cur_rowid INTO lv_rowid;
283 IF (cur_rowid%FOUND) THEN
284 Close cur_rowid;
285 Return (TRUE);
286 ELSE
287 close cur_rowid;
288 return(false);
289 END IF;
290
291 END Get_UK_For_Validation ;
292
293
294 FUNCTION Get_Description (
295 x_inquiry_type_id IN NUMBER
296 ) RETURN VARCHAR2 AS
297 /*************************************************************
298 Created By :askapoor
299 Date Created By :11-March-2005
300 Purpose : Called from IGSPE021.pld to get the inquiry type code
301 Know limitations, enhancements or remarks
302 Change History
303 Who When What
304
305 (reverse chronological order - newest change first)
306 ***************************************************************/
307
308 TYPE inquiry_csr_type IS REF CURSOR;
309 l_inquiry_csr inquiry_csr_type;
310
311 l_query VARCHAR2(1000);
312 l_inq_type_dsp VARCHAR2(40);
313
314 BEGIN
315
316 l_query := 'select INQUIRY_TYPE_CD from igr_i_inquiry_types where INQUIRY_TYPE_ID = :1';
317
318 OPEN l_inquiry_csr FOR l_query USING x_inquiry_type_id;
319 LOOP
320 FETCH l_inquiry_csr INTO l_inq_type_dsp;
321 EXIT WHEN l_inquiry_csr%NOTFOUND;
322 END LOOP;
323 CLOSE l_inquiry_csr;
324
325 RETURN l_inq_type_dsp;
326
327
328 END Get_Description;
329
330 PROCEDURE Check_Uniqueness as
331 /*************************************************************
332 Created By :SVISWEAS
333 Date Created By :27-MAY-2000
334 Purpose : Check_Uniqueness
335 Know limitations, enhancements or remarks
336 Change History
337 Who When What
338
339 (reverse chronological order - newest change first)
340 ***************************************************************/
341 Begin
342 IF Get_Uk_For_Validation (
343 new_references.source_type
344 ) THEN
345 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
346 IGS_GE_MSG_STACK.ADD;
347 app_exception.raise_exception;
348 END IF;
349 END Check_Uniqueness ;
350
351
352 PROCEDURE Before_DML (
353 p_action IN VARCHAR2,
354 x_rowid IN VARCHAR2 DEFAULT NULL,
355 x_source_type_id IN NUMBER DEFAULT NULL,
356 x_source_type IN VARCHAR2 DEFAULT NULL,
357 x_description IN VARCHAR2 DEFAULT NULL,
358 x_system_source_type IN VARCHAR2 DEFAULT NULL,
359 x_admission_cat IN VARCHAR2 DEFAULT NULL,
360 x_closed_ind IN VARCHAR2 DEFAULT NULL,
361 x_person_type_code IN VARCHAR2 DEFAULT NULL,
362 x_enquiry_source_type IN VARCHAR2 DEFAULT NULL,
363 x_funnel_status IN VARCHAR2 DEFAULT NULL,
364 x_inq_entry_stat_id IN NUMBER DEFAULT NULL,
365 x_creation_date IN DATE DEFAULT NULL,
366 x_created_by IN NUMBER DEFAULT NULL,
367 x_last_update_date IN DATE DEFAULT NULL,
368 x_last_updated_by IN NUMBER DEFAULT NULL,
369 x_last_update_login IN NUMBER DEFAULT NULL,
370 X_ORG_ID in NUMBER default NULL,
371 X_INQUIRY_TYPE_ID IN NUMBER DEFAULT NULL
372 ) AS
373 /*************************************************************
374 Created By :SVISWEAS
375 Date Created By :11-MAY-2000
376 Purpose :
377 Know limitations, enhancements or remarks
378 Change History
379 Who When What
380
381 (reverse chronological order - newest change first)
382 | asbala 18-JUL-03 2885709, made l_rowid := null at the end of before_dml
383 ***************************************************************/
384 BEGIN
385
386 Set_Column_Values (
387 p_action,
388 x_rowid,
389 x_source_type_id,
390 x_source_type,
391 x_description,
392 x_system_source_type,
393 x_admission_cat,
394 x_closed_ind,
395 x_person_type_code ,
396 x_enquiry_source_type ,
397 x_funnel_status,
398 x_inq_entry_stat_id,
399 x_creation_date,
400 x_created_by,
401 x_last_update_date,
402 x_last_updated_by,
403 x_last_update_login ,
404 x_org_id,
405 X_INQUIRY_TYPE_ID
406 );
407
408 IF (p_action = 'INSERT') THEN
409 -- Call all the procedures related to Before Insert.
410 Null;
411 IF Get_Pk_For_Validation(
412 new_references.source_type_id) THEN
413 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
414 IGS_GE_MSG_STACK.ADD;
415 App_Exception.Raise_Exception;
416 END IF;
417 Check_Constraints;
418 Check_Uniqueness;
419 Check_Parent_Existance;
420 ELSIF (p_action = 'UPDATE') THEN
421 -- Call all the procedures related to Before Update.
422 IF old_references.system_source_type
423 <> new_references.system_source_type THEN
424 BEFORE_UPDATE_DELETE;
425 END IF;
426 Check_Constraints;
427 Check_Uniqueness;
428 Check_Parent_Existance;
429
430 ELSIF (p_action = 'DELETE') THEN
431 -- Call all the procedures related to Before Delete.
432 Check_Child_Existance;
433 BEFORE_UPDATE_DELETE;
434
435 ELSIF (p_action = 'VALIDATE_INSERT') THEN
436 -- Call all the procedures related to Before Insert.
437 IF Get_PK_For_Validation (
438 new_references.source_type_id) THEN
439 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
440 IGS_GE_MSG_STACK.ADD;
441 App_Exception.Raise_Exception;
442 END IF;
443 Check_Constraints;
444
445 Check_Parent_Existance;
446 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
447 Check_Constraints;
448
449 Check_Parent_Existance;
450
451 ELSIF (p_action = 'VALIDATE_DELETE') THEN
452 Check_Child_Existance;
453 END IF;
454 l_rowid:=null;
455 END Before_DML;
456
457
458 PROCEDURE AFTER_UPDATE_INSERT AS
459
460 /*************************************************************
461 Created By :amuthu
462 Date Created By :19-MAY-2000
463 Purpose : Insert records into the IGS_AD_SOURCE_CAT table
464 when the SYSTEM SOURCE TYPE of the inserted record
465 is IN ('TEST_RESULTS','PROSPECT_LIST','APPLICATION',
466 'PROSPECT_SS_WEB_INQUIRY')
467
468 Know limitations, enhancements or remarks
469
470 Change History
471
472 Who When What
473 pkpatel 21-JUN-2001 DLD:Modelling and Forecasting-SDQ
474 To make the default DISCREPANCY RULE as 'I'
475 i.e. 'Updating Existing Values With Imported Values'.
476 And include DETAIL_LEVEL_IND, AD_TAB_NAME and INT_TAB_NAME columns
477 in the call to Igs_Ad_Source_Cat_Pkg.Insert_Row.
478 rasingh 19-JUL-2001 DLD: Interface to Academic History.
479 Ssytem Source Type of Transcript added to the list of
480 system_source_types.
481 rghosh 14-Feb-2003 removed the source type SS_ADM_APPL for bug #2422183
482 (reverse chronological order - newest change first)
483 pbondugu 26-Feb-2003 Cursor c_sysc is change (condition for closed_ind is adde
484 ***************************************************************/
485 CURSOR c_sysc IS
486 SELECT *
487 FROM IGS_AD_SYSSRC_CAT
488 WHERE system_source_type = new_references.system_source_type
489 AND NVL(closed_ind,'N') = 'N';
490
491 lv_rowid VARCHAR2(25);
492 lv_src_cat_id NUMBER;
493 l_org_id NUMBER(15);
494 BEGIN
495 IF new_references.system_source_type in ('APPLICATION',
496 'TEST_RESULTS',
497 'PROSPECT_LIST',
498 'PROSPECT_SS_WEB_INQUIRY',
499 'TRANSCRIPT') THEN -- removed the source type SS_ADM_APPL for bug #2422183 (rghosh)
500 FOR c_sysc_rec in c_sysc
501 LOOP
502
503 l_org_id := igs_ge_gen_003.get_org_id;
504 IGS_AD_SOURCE_CAT_PKG.INSERT_ROW (
505 X_ROWID => lv_rowid,
506 x_SRC_CAT_ID => lv_src_cat_id,
507 x_SOURCE_TYPE_ID => new_references.source_type_id,
508 x_CATEGORY_NAME => c_sysc_rec.category_name,
509 x_MANDATORY_IND => c_sysc_rec.mandatory_ind,
510 x_INCLUDE_IND => c_sysc_rec.mandatory_ind,
511 x_SS_MANDATORY_IND => c_sysc_rec.mandatory_ind,
512 x_ORG_ID => l_org_id,
513 x_DISCREPANCY_RULE_CD => 'I', -- To make the default DISCREPANCY RULE as 'I'
514 -- i.e. 'Updating Existing Values With Imported Values'.
515 x_SS_IND => c_sysc_rec.ss_ind,
516 x_DISPLAY_SEQUENCE => c_sysc_rec.display_sequence,
517 x_DETAIL_LEVEL_IND => NULL,
518 x_AD_TAB_NAME => c_sysc_rec.ad_tab_name,
519 x_INT_TAB_NAME => c_sysc_rec.int_tab_name,
520 X_MODE => 'R'
521 );
522
523 END LOOP;
524 END IF;
525 END AFTER_UPDATE_INSERT;
526
527
528 PROCEDURE After_DML (
529 p_action IN VARCHAR2,
530 x_rowid IN VARCHAR2
531 ) IS
532 /*************************************************************
533 Created By :SVISWEAS
534 Date Created By :11-MAY-2000
535 Purpose :
536 Know limitations, enhancements or remarks
537 Change History
538 Who When What
539 asbala 21-JUL-03 2885709: made l_rowid:=null in the end
540 (reverse chronological order - newest change first)
541 ***************************************************************/
542 BEGIN
543
544 l_rowid := x_rowid;
545 IF (p_action = 'INSERT') THEN
546 -- Call all the procedures related to After Insert.
547 AFTER_UPDATE_INSERT;
548 ELSIF (p_action = 'UPDATE') THEN
549 -- Call all the procedures related to After Update.
550 IF old_references.system_source_type
551 <> new_references.system_source_type THEN
552 AFTER_UPDATE_INSERT;
553 END IF;
554 Null;
555 ELSIF (p_action = 'DELETE') THEN
556 -- Call all the procedures related to After Delete.
557 Null;
558 END IF;
559 l_rowid:=null;
560 END After_DML;
561
562 procedure INSERT_ROW (
563 X_ROWID in out NOCOPY VARCHAR2,
564 x_SOURCE_TYPE_ID IN OUT NOCOPY NUMBER,
565 x_SOURCE_TYPE IN VARCHAR2,
566 x_DESCRIPTION IN VARCHAR2,
567 x_SYSTEM_SOURCE_TYPE IN VARCHAR2,
568 x_ADMISSION_CAT IN VARCHAR2 ,
569 x_CLOSED_IND IN VARCHAR2,
570 x_PERSON_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
571 x_ENQUIRY_SOURCE_TYPE IN VARCHAR2 DEFAULT NULL,
572 x_FUNNEL_STATUS IN VARCHAR2 DEFAULT NULL,
573 x_INQ_ENTRY_STAT_ID IN NUMBER DEFAULT NULL,
574 X_MODE in VARCHAR2 default 'R' ,
575 X_ORG_ID in NUMBER,
576 X_INQUIRY_TYPE_ID IN NUMBER DEFAULT NULL
577 ) AS
578 /*************************************************************
579 Created By :SVISWEAS
580 Date Created By :11-MAY-2000
581 Purpose :
582 Know limitations, enhancements or remarks
583 Change History
584 Who When What
585 sbaliga 13-feb-2002 Assigned igs_ge_gen_003.get_org_id to x_org_id
586 in call to before_dml as part of SWCR006 build.
587 (reverse chronological order - newest change first)
588 pbondugu 26-Feb-2003 admission_Cat is assigned with Null
589 ***************************************************************/
590 cursor C is
591 select ROWID
592 from igs_pe_src_types_all
593 where SOURCE_TYPE_ID= X_SOURCE_TYPE_ID;
594
595 X_LAST_UPDATE_DATE DATE ;
596 X_LAST_UPDATED_BY NUMBER ;
597 X_LAST_UPDATE_LOGIN NUMBER ;
598 begin
599 X_LAST_UPDATE_DATE := SYSDATE;
600 IF(X_MODE = 'I') THEN
601 X_LAST_UPDATED_BY := 1;
602 X_LAST_UPDATE_LOGIN := 0;
603 ELSIF (X_MODE = 'R') THEN
604 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
605 IF X_LAST_UPDATED_BY is NULL THEN
606 X_LAST_UPDATED_BY := -1;
607 END IF;
608 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
609 if X_LAST_UPDATE_LOGIN is NULL THEN
610 X_LAST_UPDATE_LOGIN := -1;
611 END IF;
612 ELSE
613 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
614 IGS_GE_MSG_STACK.ADD;
615 app_exception.raise_exception;
616 END IF;
617
618 SELECT IGS_PE_SRC_TYPES_S.NEXTVAL
619 INTO X_SOURCE_TYPE_ID
620 FROM DUAL;
621
622 Before_DML(
623 p_action=>'INSERT',
624 x_rowid=>X_ROWID,
625 x_source_type_id=>X_SOURCE_TYPE_ID,
626 x_source_type=>X_SOURCE_TYPE,
627 x_description=>X_DESCRIPTION,
628 x_system_source_type=>X_SYSTEM_SOURCE_TYPE,
629 x_admission_cat=>NULL,
630 x_closed_ind=>X_CLOSED_IND,
631 x_person_type_code => X_PERSON_TYPE_CODE,
632 x_enquiry_source_type => X_ENQUIRY_SOURCE_TYPE,
633 x_funnel_status => X_FUNNEL_STATUS ,
634 x_inq_entry_stat_id => X_INQ_ENTRY_STAT_ID ,
635 x_creation_date=>X_LAST_UPDATE_DATE,
636 x_created_by=>X_LAST_UPDATED_BY,
637 x_last_update_date=>X_LAST_UPDATE_DATE,
638 x_last_updated_by=>X_LAST_UPDATED_BY,
639 x_last_update_login=>X_LAST_UPDATE_LOGIN,
640 x_org_id=>igs_ge_gen_003.get_org_id,
641 x_inquiry_type_id => X_INQUIRY_TYPE_ID
642 );
643
644 INSERT INTO igs_pe_src_types_all (
645 SOURCE_TYPE_ID
646 ,SOURCE_TYPE
647 ,DESCRIPTION
648 ,SYSTEM_SOURCE_TYPE
649 ,ADMISSION_CAT
650 ,CLOSED_IND
651 ,PERSON_TYPE_CODE
652 ,ENQUIRY_SOURCE_TYPE
653 ,FUNNEL_STATUS
654 ,INQ_ENTRY_STAT_ID
655 ,CREATION_DATE
656 ,CREATED_BY
657 ,LAST_UPDATE_DATE
658 ,LAST_UPDATED_BY
659 ,LAST_UPDATE_LOGIN
660 ,ORG_ID
661 ,INQUIRY_TYPE_ID
662 ) values (
663 NEW_REFERENCES.SOURCE_TYPE_ID
664 ,NEW_REFERENCES.SOURCE_TYPE
665 ,NEW_REFERENCES.DESCRIPTION
666 ,NEW_REFERENCES.SYSTEM_SOURCE_TYPE
667 ,NULL -- NEW_REFERENCES.ADMISSION_CAT
668 ,NEW_REFERENCES.CLOSED_IND
669 ,NEW_REFERENCES.PERSON_TYPE_CODE
670 ,NEW_REFERENCES.ENQUIRY_SOURCE_TYPE
671 ,NEW_REFERENCES.FUNNEL_STATUS
672 ,NEW_REFERENCES.INQ_ENTRY_STAT_ID
673 ,X_LAST_UPDATE_DATE
674 ,X_LAST_UPDATED_BY
675 ,X_LAST_UPDATE_DATE
676 ,X_LAST_UPDATED_BY
677 ,X_LAST_UPDATE_LOGIN,
678 NEW_REFERENCES.ORG_ID,
679 NEW_REFERENCES.INQUIRY_TYPE_ID
680 );
681 open c;
682 fetch c into X_ROWID;
683 if (c%notfound) then
684 close c;
685 raise no_data_found;
686 end if;
687 close c;
688
689 After_DML (
690 p_action => 'INSERT' ,
691 x_rowid => X_ROWID );
692 end INSERT_ROW;
693
694 procedure LOCK_ROW (
695 X_ROWID in VARCHAR2,
696 x_SOURCE_TYPE_ID IN NUMBER,
697 x_SOURCE_TYPE IN VARCHAR2,
698 x_DESCRIPTION IN VARCHAR2,
699 x_SYSTEM_SOURCE_TYPE IN VARCHAR2,
700 x_ADMISSION_CAT IN VARCHAR2,
701 x_CLOSED_IND IN VARCHAR2,
702 x_PERSON_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
703 x_ENQUIRY_SOURCE_TYPE IN VARCHAR2 DEFAULT NULL,
704 x_FUNNEL_STATUS IN VARCHAR2 DEFAULT NULL,
705 x_INQ_ENTRY_STAT_ID IN NUMBER DEFAULT NULL,
706 X_INQUIRY_TYPE_ID IN NUMBER DEFAULT NULL
707 ) AS
708 /*************************************************************
709 Created By :SVISWEAS
710 Date Created By :11-MAY-2000
711 Purpose :
712 Know limitations, enhancements or remarks
713 Change History
714 Who When What
715
716 (reverse chronological order - newest change first)
717 pbondugu 26-FEb-2003 Condition for admission_cat is removed
718 askapoor 14-Mar-2005 Removed reference of INQ_ENTRY_STAT_ID in the AND condition
719 ***************************************************************/
720 cursor c1 is select
721 SOURCE_TYPE
722 ,DESCRIPTION
723 ,SYSTEM_SOURCE_TYPE
724 ,ADMISSION_CAT
725 ,CLOSED_IND
726 ,PERSON_TYPE_CODE
727 ,ENQUIRY_SOURCE_TYPE
728 ,FUNNEL_STATUS
729 ,INQ_ENTRY_STAT_ID
730 ,INQUIRY_TYPE_ID
731 FROM igs_pe_src_types_all
732 WHERE ROWID = X_ROWID
733 for update nowait;
734
735 tlinfo c1%rowtype;
736 begin
737 open c1;
738 fetch c1 into tlinfo;
739 if (c1%notfound) then
740 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
741 IGS_GE_MSG_STACK.ADD;
742 close c1;
743 app_exception.raise_exception;
744 return;
745 end if;
746 close c1;
747
748 if ( ( tlinfo.SOURCE_TYPE = X_SOURCE_TYPE)
749 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
750 OR ((tlinfo.DESCRIPTION is null)
751 AND (X_DESCRIPTION is null)))
752 AND (tlinfo.SYSTEM_SOURCE_TYPE = X_SYSTEM_SOURCE_TYPE)
753 AND ((tlinfo.CLOSED_IND = X_CLOSED_IND)
754 OR ((tlinfo.CLOSED_IND is null)
755 AND (X_CLOSED_IND is null)))
756 AND ((tlinfo.PERSON_TYPE_CODE = X_PERSON_TYPE_CODE)
757 OR ((tlinfo.PERSON_TYPE_CODE is null)
758 AND (X_PERSON_TYPE_CODE is null)))
759 AND ((tlinfo.ENQUIRY_SOURCE_TYPE = X_ENQUIRY_SOURCE_TYPE)
760 OR ((tlinfo.ENQUIRY_SOURCE_TYPE is null)
761 AND (X_ENQUIRY_SOURCE_TYPE is null)))
762 AND ((tlinfo.FUNNEL_STATUS = X_FUNNEL_STATUS)
763 OR ((tlinfo.FUNNEL_STATUS is null)
764 AND (X_FUNNEL_STATUS is null)))
765 AND ((tlinfo.INQUIRY_TYPE_ID = X_INQUIRY_TYPE_ID)
766 OR ((tlinfo.INQUIRY_TYPE_ID is null)
767 AND (X_INQUIRY_TYPE_ID is null)))
768 ) then
769 null;
770 else
771 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
772 IGS_GE_MSG_STACK.ADD;
773 app_exception.raise_exception;
774 end if;
775 return;
776
777 end LOCK_ROW;
778
779 Procedure UPDATE_ROW (
780 X_ROWID in VARCHAR2,
781 x_SOURCE_TYPE_ID IN NUMBER,
782 x_SOURCE_TYPE IN VARCHAR2,
783 x_DESCRIPTION IN VARCHAR2,
784 x_SYSTEM_SOURCE_TYPE IN VARCHAR2,
785 x_ADMISSION_CAT IN VARCHAR2,
786 x_CLOSED_IND IN VARCHAR2,
787 x_PERSON_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
788 x_ENQUIRY_SOURCE_TYPE IN VARCHAR2 DEFAULT NULL,
789 x_FUNNEL_STATUS IN VARCHAR2 DEFAULT NULL,
790 x_INQ_ENTRY_STAT_ID IN NUMBER DEFAULT NULL,
791 X_MODE in VARCHAR2 default 'R' ,
792 X_INQUIRY_TYPE_ID IN NUMBER DEFAULT NULL
793 ) AS
794 /*************************************************************
795 Created By :SVISWEAS
796 Date Created By :11-MAY-2000
797 Purpose :
798 Know limitations, enhancements or remarks
799 Change History
800 Who When What
801
802 (reverse chronological order - newest change first)
803 pbondugu 26-FEb-2003 admission_Cat is made to null
804 ***************************************************************/
805 X_LAST_UPDATE_DATE DATE ;
806 X_LAST_UPDATED_BY NUMBER ;
807 X_LAST_UPDATE_LOGIN NUMBER ;
808 begin
809 X_LAST_UPDATE_DATE := SYSDATE;
810 if(X_MODE = 'I') then
811 X_LAST_UPDATED_BY := 1;
812 X_LAST_UPDATE_LOGIN := 0;
813 elsif (X_MODE = 'R') then
814 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
815 if X_LAST_UPDATED_BY is NULL then
816 X_LAST_UPDATED_BY := -1;
817 end if;
818 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
819 if X_LAST_UPDATE_LOGIN is NULL then
820 X_LAST_UPDATE_LOGIN := -1;
821 end if;
822 else
823 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
824 IGS_GE_MSG_STACK.ADD;
825 app_exception.raise_exception;
826 end if;
827
828 Before_DML(
829 p_action=>'UPDATE',
830 x_rowid=>X_ROWID,
831 x_source_type_id=>X_SOURCE_TYPE_ID,
832 x_source_type=>X_SOURCE_TYPE,
833 x_description=>X_DESCRIPTION,
834 x_system_source_type=>X_SYSTEM_SOURCE_TYPE,
835 x_admission_cat=>NULL,
836 x_closed_ind=>X_CLOSED_IND,
837 x_person_type_code => X_PERSON_TYPE_CODE,
838 x_enquiry_source_type => X_ENQUIRY_SOURCE_TYPE,
839 x_funnel_status => X_FUNNEL_STATUS ,
840 x_inq_entry_stat_id => X_INQ_ENTRY_STAT_ID ,
841 x_creation_date=>X_LAST_UPDATE_DATE,
842 x_created_by=>X_LAST_UPDATED_BY,
843 x_last_update_date=>X_LAST_UPDATE_DATE,
844 x_last_updated_by=>X_LAST_UPDATED_BY,
845 x_last_update_login=>X_LAST_UPDATE_LOGIN,
846 x_inquiry_type_id => X_INQUIRY_TYPE_ID
847 );
848
849 UPDATE igs_pe_src_types_all SET
850 SOURCE_TYPE = NEW_REFERENCES.SOURCE_TYPE,
851 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
852 SYSTEM_SOURCE_TYPE = NEW_REFERENCES.SYSTEM_SOURCE_TYPE,
853 ADMISSION_CAT = NULL, --NEW_REFERENCES.ADMISSION_CAT,
854 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
855 PERSON_TYPE_CODE = NEW_REFERENCES.PERSON_TYPE_CODE,
856 ENQUIRY_SOURCE_TYPE = NEW_REFERENCES.ENQUIRY_SOURCE_TYPE,
857 FUNNEL_STATUS = NEW_REFERENCES.FUNNEL_STATUS ,
858 INQ_ENTRY_STAT_ID = NEW_REFERENCES.INQ_ENTRY_STAT_ID ,
859 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
860 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
861 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
862 INQUIRY_TYPE_ID = X_INQUIRY_TYPE_ID
863 where ROWID = X_ROWID;
864
865 if (sql%notfound) then
866 raise no_data_found;
867 end if;
868
869 After_DML (
870 p_action => 'UPDATE' ,
871 x_rowid => X_ROWID
872 );
873
874 end UPDATE_ROW;
875
876 procedure ADD_ROW (
877 X_ROWID in out NOCOPY VARCHAR2,
878 x_SOURCE_TYPE_ID IN OUT NOCOPY NUMBER,
879 x_SOURCE_TYPE IN VARCHAR2,
880 x_DESCRIPTION IN VARCHAR2,
881 x_SYSTEM_SOURCE_TYPE IN VARCHAR2,
882 x_ADMISSION_CAT IN VARCHAR2,
883 x_CLOSED_IND IN VARCHAR2,
884 x_PERSON_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
885 x_ENQUIRY_SOURCE_TYPE IN VARCHAR2 DEFAULT NULL,
886 x_FUNNEL_STATUS IN VARCHAR2 DEFAULT NULL,
887 x_INQ_ENTRY_STAT_ID IN NUMBER DEFAULT NULL,
888 X_MODE in VARCHAR2 default 'R' ,
889 X_ORG_ID in NUMBER,
890 X_INQUIRY_TYPE_ID IN NUMBER DEFAULT NULL
891 ) AS
892 /*************************************************************
893 Created By :SVISWEAS
894 Date Created By :11-MAY-2000
895 Purpose :
896 Know limitations, enhancements or remarks
897 Change History
898 Who When What
899
900 (reverse chronological order - newest change first)
901 ***************************************************************/
902 CURSOR c1 is
903 SELECT ROWID
904 FROM igs_pe_src_types_all
905 WHERE SOURCE_TYPE_ID= X_SOURCE_TYPE_ID;
906
907 begin
908 open c1;
909 fetch c1 into X_ROWID;
910 if (c1%notfound) then
911 close c1;
912 INSERT_ROW (
913 X_ROWID,
914 X_SOURCE_TYPE_ID,
915 X_SOURCE_TYPE,
916 X_DESCRIPTION,
917 X_SYSTEM_SOURCE_TYPE,
918 X_ADMISSION_CAT,
919 X_CLOSED_IND,
920 X_PERSON_TYPE_CODE ,
921 X_ENQUIRY_SOURCE_TYPE ,
922 X_FUNNEL_STATUS,
923 X_INQ_ENTRY_STAT_ID,
924 X_MODE ,
925 x_org_id,
926 X_INQUIRY_TYPE_ID
927 );
928 return;
929 end if;
930 close c1;
931
932 UPDATE_ROW (
933 X_ROWID,
934 X_SOURCE_TYPE_ID,
935 X_SOURCE_TYPE,
936 X_DESCRIPTION,
937 X_SYSTEM_SOURCE_TYPE,
938 X_ADMISSION_CAT,
939 X_CLOSED_IND,
940 X_PERSON_TYPE_CODE ,
941 X_ENQUIRY_SOURCE_TYPE ,
942 X_FUNNEL_STATUS,
943 X_INQ_ENTRY_STAT_ID,
944 X_MODE,
945 X_INQUIRY_TYPE_ID
946 );
947
948 end ADD_ROW;
949
950 procedure DELETE_ROW (
951 X_ROWID in VARCHAR2
952 ) AS
953 /*************************************************************
954 Created By :SVISWEAS
955 Date Created By :11-MAY-2000
956 Purpose :
957 Know limitations, enhancements or remarks
958 Change History
959 Who When What
960
961 (reverse chronological order - newest change first)
962 ***************************************************************/
963 BEGIN
964 Before_DML (
965 p_action => 'DELETE',
966 x_rowid => X_ROWID
967 );
968
969 delete from igs_pe_src_types_all
970 where ROWID = X_ROWID;
971 if (sql%notfound) then
972 raise no_data_found;
973 end if;
974
975 After_DML (
976 p_action => 'DELETE',
977 x_rowid => X_ROWID
978 );
979
980 end DELETE_ROW;
981
982 END igs_pe_src_types_pkg;
[Home] [Help]