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