[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_ITEM_ASSESSOR_PKG
Source
1 package body IGS_AS_ITEM_ASSESSOR_PKG AS
2 /* $Header: IGSDI02B.pls 120.0 2005/07/05 12:14:17 appldev noship $ */
3 --msrinivi 24-AUG-2001 Bug No. 1956374. Repointed genp_val_prsn_id
4 l_rowid VARCHAR2(25);
5 old_references IGS_AS_ITEM_ASSESSOR%RowType;
6 new_references IGS_AS_ITEM_ASSESSOR%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_sequence_number IN NUMBER DEFAULT NULL,
12 x_ass_assessor_type IN VARCHAR2 DEFAULT NULL,
13 x_primary_assessor_ind IN VARCHAR2 DEFAULT NULL,
14 x_item_limit IN NUMBER DEFAULT NULL,
15 x_location_cd IN VARCHAR2 DEFAULT NULL,
16 x_unit_mode IN VARCHAR2 DEFAULT NULL,
17 x_unit_class IN VARCHAR2 DEFAULT NULL,
18 x_comments IN VARCHAR2 DEFAULT NULL,
19 x_ass_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 ) AS
26 CURSOR cur_old_ref_values IS
27 SELECT *
28 FROM IGS_AS_ITEM_ASSESSOR
29 WHERE rowid = x_rowid;
30 BEGIN
31 l_rowid := x_rowid;
32 -- Code for setting the Old and New Reference Values.
33 -- Populate Old Values.
34 Open cur_old_ref_values;
35 Fetch cur_old_ref_values INTO old_references;
36 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
37 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39 Close cur_old_ref_values;
40 APP_EXCEPTION.RAISE_EXCEPTION;
41
42 Return;
43 END IF;
44 Close cur_old_ref_values;
45 -- Populate New Values.
46 new_references.person_id := x_person_id;
47 new_references.sequence_number := x_sequence_number;
48 new_references.ass_assessor_type := x_ass_assessor_type;
49 new_references.primary_assessor_ind := x_primary_assessor_ind;
50 new_references.item_limit := x_item_limit;
51 new_references.location_cd := x_location_cd;
52 new_references.unit_mode := x_unit_mode;
53 new_references.unit_class := x_unit_class;
54 new_references.comments := x_comments;
55 new_references.ass_id := x_ass_id;
56 IF (p_action = 'UPDATE') THEN
57 new_references.creation_date := old_references.creation_date;
58 new_references.created_by := old_references.created_by;
59 ELSE
60 new_references.creation_date := x_creation_date;
61 new_references.created_by := x_created_by;
62 END IF;
63 new_references.last_update_date := x_last_update_date;
64 new_references.last_updated_by := x_last_updated_by;
65 new_references.last_update_login := x_last_update_login;
66 END Set_Column_Values;
67 -- Trigger description :-
68 -- "OSS_TST".trg_aia_br_iu
69 -- BEFORE INSERT OR UPDATE
70 -- ON IGS_AS_ITEM_ASSESSOR
71 -- FOR EACH ROW
72 PROCEDURE BeforeRowInsertUpdate1(
73 p_inserting IN BOOLEAN DEFAULT FALSE,
74 p_updating IN BOOLEAN DEFAULT FALSE,
75 p_deleting IN BOOLEAN DEFAULT FALSE
76 ) AS
77 v_message_name VARCHAR2(30);
78 BEGIN
79 -- Validate that inserts/updates are allowed
80 IF p_inserting OR p_updating THEN
81 -- <aia1>
82 -- Validate IGS_PE_PERSON exists
83 IF IGS_CO_VAL_OC.genp_val_prsn_id(new_references.person_id,
84 v_message_name) = FALSE THEN
85 FND_MESSAGE.SET_NAME('IGS',v_message_name);
86 IGS_GE_MSG_STACK.ADD;
87 APP_EXCEPTION.RAISE_EXCEPTION;
88 END IF;
89 -- <aia2>
90 -- Validate assessment assessor type closed indicator
91 IF IGS_AS_VAL_AIA.assp_val_asst_closed(new_references.ass_assessor_type,
92 v_message_name) = FALSE THEN
93 FND_MESSAGE.SET_NAME('IGS',v_message_name);
94 IGS_GE_MSG_STACK.ADD;
95 APP_EXCEPTION.RAISE_EXCEPTION;
96 END IF;
97 -- <aia3>
98 -- Validate IGS_AD_LOCATION closed indicator
99 -- As part of the bug# 1956374 changed to the below call from IGS_AS_VAL_AIA.crsp_val_loc_cd
100 IF IGS_PS_VAL_UOO.crsp_val_loc_cd(new_references.location_cd,
101 v_message_name) = FALSE THEN
102 FND_MESSAGE.SET_NAME('IGS',v_message_name);
103 IGS_GE_MSG_STACK.ADD;
104 APP_EXCEPTION.RAISE_EXCEPTION;
105 END IF;
106 -- <aia4>
107 -- Validate IGS_PS_UNIT mode closed indicator
108 -- As part of the bug# 1956374 changed to the below call from IGS_AS_VAL_AIA.crsp_val_um_closed
109 IF IGS_AS_VAL_UAI.crsp_val_um_closed(new_references.unit_mode,
110 v_message_name) = FALSE THEN
111 FND_MESSAGE.SET_NAME('IGS',v_message_name);
112 IGS_GE_MSG_STACK.ADD;
113 APP_EXCEPTION.RAISE_EXCEPTION;
114 END IF;
115 -- <aia5>
116 -- Validate IGS_PS_UNIT class indicator
117 -- As part of the bug# 1956374 changed to the below call from IGS_AS_VAL_AIA.crsp_val_ucl_closed
118 IF IGS_AS_VAL_UAI.crsp_val_ucl_closed(new_references.unit_class,
119 v_message_name) = FALSE THEN
120 FND_MESSAGE.SET_NAME('IGS',v_message_name);
121 IGS_GE_MSG_STACK.ADD;
122 APP_EXCEPTION.RAISE_EXCEPTION;
123 END IF;
124 END IF;
125 END BeforeRowInsertUpdate1;
126 -- Trigger description :-
127 -- "OSS_TST".trg_aia_ar_iu
128 -- AFTER INSERT OR UPDATE
129 -- ON IGS_AS_ITEM_ASSESSOR
130 -- FOR EACH ROW
131 PROCEDURE AfterRowInsertUpdate2(
132 p_inserting IN BOOLEAN DEFAULT FALSE,
133 p_updating IN BOOLEAN DEFAULT FALSE,
134 p_deleting IN BOOLEAN DEFAULT FALSE
135 ) AS
136 v_message_name VARCHAR2(30);
137 BEGIN
138 IF p_inserting OR p_updating THEN
139 IF new_references.primary_assessor_ind = 'Y' THEN
140 IF IGS_AS_VAL_AIA.assp_val_aia_primary (
141 new_references.ass_id,
142 new_references.person_id,
143 new_references.sequence_number,
144 v_message_name) = FALSE THEN
145 FND_MESSAGE.SET_NAME('IGS',v_message_name);
146 IGS_GE_MSG_STACK.ADD;
147 APP_EXCEPTION.RAISE_EXCEPTION;
148 END IF;
149 END IF;
150 -- Validate assessor links for invalid combinations
151 IF IGS_AS_VAL_AIA.assp_val_aia_links (
152 new_references.ass_id,
153 new_references.person_id,
154 new_references.sequence_number,
155 new_references.location_cd,
156 new_references.unit_mode,
157 new_references.unit_class,
158 new_references.ass_assessor_type ,
159 v_message_name) = FALSE THEN
160 FND_MESSAGE.SET_NAME('IGS',v_message_name);
161 IGS_GE_MSG_STACK.ADD;
162 APP_EXCEPTION.RAISE_EXCEPTION;
163 END IF;
164 -- Save the rowid of the current row.
165 END IF;
166 END AfterRowInsertUpdate2;
167 -- Trigger description :-
168 -- "OSS_TST".trg_aia_as_iu
169 -- AFTER INSERT OR UPDATE
170 -- ON IGS_AS_ITEM_ASSESSOR
171
172 PROCEDURE Check_Parent_Existance AS
173 BEGIN
174 IF (((old_references.ass_id = new_references.ass_id)) OR
175 ((new_references.ass_id IS NULL))) THEN
176 NULL;
177 ELSIF NOT IGS_AS_ASSESSMNT_ITM_PKG.Get_PK_For_Validation (
178 new_references.ass_id ) THEN
179 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
180 IGS_GE_MSG_STACK.ADD;
181 APP_EXCEPTION.RAISE_EXCEPTION;
182 END IF;
183 IF (((old_references.ass_assessor_type = new_references.ass_assessor_type)) OR
184 ((new_references.ass_assessor_type IS NULL))) THEN
185 NULL;
186 ELSIF NOT IGS_AS_ASSESSOR_TYPE_PKG.Get_PK_For_Validation (
187 new_references.ass_assessor_type )THEN
188 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
189 IGS_GE_MSG_STACK.ADD;
190 APP_EXCEPTION.RAISE_EXCEPTION;
191
192 END IF;
193 IF (((old_references.location_cd = new_references.location_cd)) OR
194 ((new_references.location_cd IS NULL))) THEN
195 NULL;
196 ELSIF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation ( new_references.location_cd,
197 'N'
198 ) THEN
199 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
200 IGS_GE_MSG_STACK.ADD;
201 APP_EXCEPTION.RAISE_EXCEPTION;
202 END IF;
203 IF (((old_references.person_id = new_references.person_id)) OR
204 ((new_references.person_id IS NULL))) THEN
205 NULL;
206 ELSIF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
207 new_references.person_id
208 ) THEN
209 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
210 IGS_GE_MSG_STACK.ADD;
211 APP_EXCEPTION.RAISE_EXCEPTION;
212 END IF;
213 IF (((old_references.unit_class = new_references.unit_class)) OR
214 ((new_references.unit_class IS NULL))) THEN
215 NULL;
216 ELSIF NOT IGS_AS_UNIT_CLASS_PKG.Get_PK_For_Validation (
217 new_references.unit_class
218 ) THEN
219 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
220 IGS_GE_MSG_STACK.ADD;
221 APP_EXCEPTION.RAISE_EXCEPTION;
222 END IF;
223 IF (((old_references.unit_mode = new_references.unit_mode)) OR
224 ((new_references.unit_mode IS NULL))) THEN
225 NULL;
226 ELSIF NOT IGS_AS_UNIT_MODE_PKG.Get_PK_For_Validation (
227 new_references.unit_mode
228 ) THEN
229 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
230 IGS_GE_MSG_STACK.ADD;
231 APP_EXCEPTION.RAISE_EXCEPTION;
232 END IF;
233 END Check_Parent_Existance;
234
235 FUNCTION Get_PK_For_Validation (
236 x_ass_id IN NUMBER,
237 x_person_id IN NUMBER,
238 x_sequence_number IN NUMBER
239 ) RETURN BOOLEAN AS
240 CURSOR cur_rowid IS
241 SELECT rowid
242 FROM IGS_AS_ITEM_ASSESSOR
243 WHERE ass_id = x_ass_id
244 AND person_id = x_person_id
245 AND sequence_number = x_sequence_number
246 FOR UPDATE NOWAIT;
247 lv_rowid cur_rowid%RowType;
248 BEGIN
249 Open cur_rowid;
250 Fetch cur_rowid INTO lv_rowid;
251 IF (cur_rowid%FOUND) THEN
252 Close cur_rowid;
253 Return (TRUE);
254 ELSE
255 Close cur_rowid;
256 Return (FALSE);
257 END IF;
258 END Get_PK_For_Validation;
259
260 PROCEDURE GET_FK_IGS_AS_ASSESSMNT_ITM (
261 x_ass_id IN NUMBER
262 ) AS
263 CURSOR cur_rowid IS
264 SELECT rowid
265 FROM IGS_AS_ITEM_ASSESSOR
266 WHERE ass_id = x_ass_id ;
267 lv_rowid cur_rowid%RowType;
268 BEGIN
269 Open cur_rowid;
270 Fetch cur_rowid INTO lv_rowid;
271 IF (cur_rowid%FOUND) THEN
272 Fnd_Message.Set_Name ('IGS', 'IGS_AS_AIA_AI_FK');
273 IGS_GE_MSG_STACK.ADD;
274 Close cur_rowid;
275 APP_EXCEPTION.RAISE_EXCEPTION;
276
277 Return;
278 END IF;
279 Close cur_rowid;
280 END GET_FK_IGS_AS_ASSESSMNT_ITM;
281 PROCEDURE GET_FK_IGS_AS_ASSESSOR_TYPE (
282 x_ass_assessor_type IN VARCHAR2
283 ) AS
284 CURSOR cur_rowid IS
285 SELECT rowid
286 FROM IGS_AS_ITEM_ASSESSOR
287 WHERE ass_assessor_type = x_ass_assessor_type ;
288 lv_rowid cur_rowid%RowType;
289 BEGIN
290 Open cur_rowid;
291 Fetch cur_rowid INTO lv_rowid;
292 IF (cur_rowid%FOUND) THEN
293 Fnd_Message.Set_Name ('IGS', 'IGS_AS_AIA_ASST_FK');
294 IGS_GE_MSG_STACK.ADD;
295 Close cur_rowid;
296 APP_EXCEPTION.RAISE_EXCEPTION;
297
298 Return;
299 END IF;
300 Close cur_rowid;
301 END GET_FK_IGS_AS_ASSESSOR_TYPE;
302 PROCEDURE GET_FK_IGS_AD_LOCATION (
303 x_location_cd IN VARCHAR2
304 ) AS
305 CURSOR cur_rowid IS
306 SELECT rowid
307 FROM IGS_AS_ITEM_ASSESSOR
308 WHERE location_cd = x_location_cd ;
309 lv_rowid cur_rowid%RowType;
310 BEGIN
311 Open cur_rowid;
312 Fetch cur_rowid INTO lv_rowid;
313 IF (cur_rowid%FOUND) THEN
314 Fnd_Message.Set_Name ('IGS', 'IGS_AS_AIA_LOC_FK');
315 IGS_GE_MSG_STACK.ADD;
316 Close cur_rowid;
317 APP_EXCEPTION.RAISE_EXCEPTION;
318
319 Return;
320 END IF;
321 Close cur_rowid;
322 END GET_FK_IGS_AD_LOCATION;
323 PROCEDURE GET_FK_IGS_PE_PERSON (
324 x_person_id IN NUMBER
325 ) AS
326 CURSOR cur_rowid IS
327 SELECT rowid
328 FROM IGS_AS_ITEM_ASSESSOR
329 WHERE person_id = x_person_id ;
330 lv_rowid cur_rowid%RowType;
331 BEGIN
332 Open cur_rowid;
333 Fetch cur_rowid INTO lv_rowid;
334 IF (cur_rowid%FOUND) THEN
335 Fnd_Message.Set_Name ('IGS', 'IGS_AS_AIA_PE_FK');
336 IGS_GE_MSG_STACK.ADD;
337 Close cur_rowid;
338 APP_EXCEPTION.RAISE_EXCEPTION;
339
340 Return;
341 END IF;
342 Close cur_rowid;
343 END GET_FK_IGS_PE_PERSON;
344
345 PROCEDURE GET_FK_IGS_AS_UNIT_MODE (
346 x_unit_mode IN VARCHAR2
347 ) AS
348 CURSOR cur_rowid IS
349 SELECT rowid
350 FROM IGS_AS_ITEM_ASSESSOR
351 WHERE unit_mode = x_unit_mode ;
352 lv_rowid cur_rowid%RowType;
353 BEGIN
354 Open cur_rowid;
355 Fetch cur_rowid INTO lv_rowid;
356 IF (cur_rowid%FOUND) THEN
357 Fnd_Message.Set_Name ('IGS', 'IGS_AS_AIA_UM_FK');
358 IGS_GE_MSG_STACK.ADD;
359 Close cur_rowid;
360 APP_EXCEPTION.RAISE_EXCEPTION;
361
362 Return;
363 END IF;
364 Close cur_rowid;
365 END GET_FK_IGS_AS_UNIT_MODE;
366 PROCEDURE Before_DML (
367 p_action IN VARCHAR2,
368 x_rowid IN VARCHAR2 DEFAULT NULL,
369 x_person_id IN NUMBER DEFAULT NULL,
370 x_sequence_number IN NUMBER DEFAULT NULL,
371 x_ass_assessor_type IN VARCHAR2 DEFAULT NULL,
372 x_primary_assessor_ind IN VARCHAR2 DEFAULT NULL,
373 x_item_limit IN NUMBER DEFAULT NULL,
374 x_location_cd IN VARCHAR2 DEFAULT NULL,
375 x_unit_mode IN VARCHAR2 DEFAULT NULL,
376 x_unit_class IN VARCHAR2 DEFAULT NULL,
377 x_comments IN VARCHAR2 DEFAULT NULL,
378 x_ass_id IN NUMBER DEFAULT NULL,
379 x_creation_date IN DATE DEFAULT NULL,
380 x_created_by IN NUMBER DEFAULT NULL,
381 x_last_update_date IN DATE DEFAULT NULL,
382 x_last_updated_by IN NUMBER DEFAULT NULL,
383 x_last_update_login IN NUMBER DEFAULT NULL
384 ) AS
385 BEGIN
386 Set_Column_Values (
387 p_action,
388 x_rowid,
389 x_person_id,
390 x_sequence_number,
391 x_ass_assessor_type,
392 x_primary_assessor_ind,
393 x_item_limit,
394 x_location_cd,
395 x_unit_mode,
396 x_unit_class,
397 x_comments,
398 x_ass_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 );
405 IF (p_action = 'INSERT') THEN
406 -- Call all the procedures related to Before Insert.
407 BeforeRowInsertUpdate1 ( p_inserting => TRUE ) ;
408 IF Get_PK_For_Validation ( new_references.ass_id ,
409 new_references.person_id ,
410 new_references.sequence_number ) THEN
411 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
412 IGS_GE_MSG_STACK.ADD;
413 APP_EXCEPTION.RAISE_EXCEPTION;
414 END IF;
415
416 Check_Constraints;
417 Check_Parent_Existance;
418 ELSIF (p_action = 'UPDATE') THEN
419 -- Call all the procedures related to Before Update.
420 BeforeRowInsertUpdate1 ( p_updating => TRUE );
421 Check_Constraints;
422 Check_Parent_Existance;
423
424 ELSIF (p_action = 'VALIDATE_INSERT') THEN
425 IF Get_PK_For_Validation ( new_references.ass_id ,
426 new_references.person_id ,
427 new_references.sequence_number ) THEN
428 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
429 IGS_GE_MSG_STACK.ADD;
430 APP_EXCEPTION.RAISE_EXCEPTION;
431 END IF;
432 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
433
434 Check_Constraints;
435 END IF;
436 END Before_DML;
437 PROCEDURE After_DML (
438 p_action IN VARCHAR2,
439 x_rowid IN VARCHAR2
440 ) AS
441 BEGIN
442 l_rowid := x_rowid;
443 IF (p_action = 'INSERT') THEN
444 -- Call all the procedures related to After Insert.
445 AfterRowInsertUpdate2 ( p_inserting => TRUE );
446 ELSIF (p_action = 'UPDATE') THEN
447 -- Call all the procedures related to After Update.
448 AfterRowInsertUpdate2 ( p_updating => TRUE );
449
450 END IF;
451 END After_DML;
452 procedure INSERT_ROW (
453 X_ROWID in out NOCOPY VARCHAR2,
454 X_ASS_ID in NUMBER,
455 X_PERSON_ID in NUMBER,
456 X_SEQUENCE_NUMBER in NUMBER,
457 X_ASS_ASSESSOR_TYPE in VARCHAR2,
458 X_PRIMARY_ASSESSOR_IND in VARCHAR2,
459 X_ITEM_LIMIT in NUMBER,
460 X_LOCATION_CD in VARCHAR2,
461 X_UNIT_MODE in VARCHAR2,
462 X_UNIT_CLASS in VARCHAR2,
463 X_COMMENTS in VARCHAR2,
464 X_MODE in VARCHAR2 default 'R'
465 ) AS
466 cursor C is select ROWID from IGS_AS_ITEM_ASSESSOR
467 where ASS_ID = X_ASS_ID
468 and PERSON_ID = X_PERSON_ID
469 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
470 X_LAST_UPDATE_DATE DATE;
471 X_LAST_UPDATED_BY NUMBER;
472 X_LAST_UPDATE_LOGIN NUMBER;
473 gv_other_detail VARCHAR2(255);
474 begin
475 X_LAST_UPDATE_DATE := SYSDATE;
476 if(X_MODE = 'I') then
477 X_LAST_UPDATED_BY := 1;
478 X_LAST_UPDATE_LOGIN := 0;
479 elsif (X_MODE = 'R') then
480 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
481 if X_LAST_UPDATED_BY is NULL then
482 X_LAST_UPDATED_BY := -1;
483 end if;
484 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
485 if X_LAST_UPDATE_LOGIN is NULL then
486 X_LAST_UPDATE_LOGIN := -1;
487 end if;
488 else
489 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
490 IGS_GE_MSG_STACK.ADD;
491 APP_EXCEPTION.RAISE_EXCEPTION;
492 end if;
493 Before_DML(
494 p_action=>'INSERT',
495 x_rowid=>X_ROWID,
496 x_ass_assessor_type=>X_ASS_ASSESSOR_TYPE,
497 x_ass_id=>X_ASS_ID,
498 x_comments=>X_COMMENTS,
499 x_item_limit=>X_ITEM_LIMIT,
500 x_location_cd=>X_LOCATION_CD,
501 x_person_id=>X_PERSON_ID,
502 x_primary_assessor_ind=> NVL(X_PRIMARY_ASSESSOR_IND,'Y'),
503 x_sequence_number=>X_SEQUENCE_NUMBER,
504 x_unit_class=>X_UNIT_CLASS,
505 x_unit_mode=>X_UNIT_MODE,
506 x_creation_date=>X_LAST_UPDATE_DATE,
507 x_created_by=>X_LAST_UPDATED_BY,
508 x_last_update_date=>X_LAST_UPDATE_DATE,
509 x_last_updated_by=>X_LAST_UPDATED_BY,
510 x_last_update_login=>X_LAST_UPDATE_LOGIN
511 );
512 insert into IGS_AS_ITEM_ASSESSOR (
513 ASS_ID,
514 PERSON_ID,
515 SEQUENCE_NUMBER,
516 ASS_ASSESSOR_TYPE,
517 PRIMARY_ASSESSOR_IND,
518 ITEM_LIMIT,
519 LOCATION_CD,
520 UNIT_MODE,
521 UNIT_CLASS,
522 COMMENTS,
523 CREATION_DATE,
524 CREATED_BY,
525 LAST_UPDATE_DATE,
526 LAST_UPDATED_BY,
527 LAST_UPDATE_LOGIN
528 ) values (
529 NEW_REFERENCES.ASS_ID,
530 NEW_REFERENCES.PERSON_ID,
531 NEW_REFERENCES.SEQUENCE_NUMBER,
532 NEW_REFERENCES.ASS_ASSESSOR_TYPE,
533 NEW_REFERENCES.PRIMARY_ASSESSOR_IND,
534 NEW_REFERENCES.ITEM_LIMIT,
535 NEW_REFERENCES.LOCATION_CD,
536 NEW_REFERENCES.UNIT_MODE,
537 NEW_REFERENCES.UNIT_CLASS,
538 NEW_REFERENCES.COMMENTS,
539 X_LAST_UPDATE_DATE,
540 X_LAST_UPDATED_BY,
541 X_LAST_UPDATE_DATE,
542 X_LAST_UPDATED_BY,
543 X_LAST_UPDATE_LOGIN
544 );
545 open c;
546 fetch c into X_ROWID;
547 if (c%notfound) then
548 close c;
549 raise no_data_found;
550 end if;
551 close c;
552 After_DML(
553 p_action => 'INSERT',
554 x_rowid => X_ROWID
555 );
556 end INSERT_ROW;
557 procedure LOCK_ROW (
558 X_ROWID in VARCHAR2,
559 X_ASS_ID in NUMBER,
560 X_PERSON_ID in NUMBER,
561 X_SEQUENCE_NUMBER in NUMBER,
562 X_ASS_ASSESSOR_TYPE in VARCHAR2,
563 X_PRIMARY_ASSESSOR_IND in VARCHAR2,
564 X_ITEM_LIMIT in NUMBER,
565 X_LOCATION_CD in VARCHAR2,
566 X_UNIT_MODE in VARCHAR2,
567 X_UNIT_CLASS in VARCHAR2,
568 X_COMMENTS in VARCHAR2
569 ) AS
570 cursor c1 is select
571 ASS_ASSESSOR_TYPE,
572 PRIMARY_ASSESSOR_IND,
573 ITEM_LIMIT,
574 LOCATION_CD,
575 UNIT_MODE,
576 UNIT_CLASS,
577 COMMENTS
578 from IGS_AS_ITEM_ASSESSOR
579 where ROWID = X_ROWID for update nowait;
580 tlinfo c1%rowtype;
581 begin
582 open c1;
583 fetch c1 into tlinfo;
584 if (c1%notfound) then
585 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
586 IGS_GE_MSG_STACK.ADD;
587 APP_EXCEPTION.RAISE_EXCEPTION;
588 close c1;
589 return;
590 end if;
591 close c1;
592 if ( (tlinfo.ASS_ASSESSOR_TYPE = X_ASS_ASSESSOR_TYPE)
593 AND (tlinfo.PRIMARY_ASSESSOR_IND = X_PRIMARY_ASSESSOR_IND)
594 AND ((tlinfo.ITEM_LIMIT = X_ITEM_LIMIT)
595 OR ((tlinfo.ITEM_LIMIT is null)
596 AND (X_ITEM_LIMIT is null)))
597 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
598 OR ((tlinfo.LOCATION_CD is null)
599 AND (X_LOCATION_CD is null)))
600 AND ((tlinfo.UNIT_MODE = X_UNIT_MODE)
601 OR ((tlinfo.UNIT_MODE is null)
602 AND (X_UNIT_MODE is null)))
603 AND ((tlinfo.UNIT_CLASS = X_UNIT_CLASS)
604 OR ((tlinfo.UNIT_CLASS is null)
605 AND (X_UNIT_CLASS is null)))
606 AND ((tlinfo.COMMENTS = X_COMMENTS)
607 OR ((tlinfo.COMMENTS is null)
608 AND (X_COMMENTS is null)))
609 ) then
610 null;
611 else
612 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
613 IGS_GE_MSG_STACK.ADD;
614 APP_EXCEPTION.RAISE_EXCEPTION;
615 end if;
616 return;
617 end LOCK_ROW;
618 procedure UPDATE_ROW (
619 X_ROWID in VARCHAR2,
620 X_ASS_ID in NUMBER,
621 X_PERSON_ID in NUMBER,
622 X_SEQUENCE_NUMBER in NUMBER,
623 X_ASS_ASSESSOR_TYPE in VARCHAR2,
624 X_PRIMARY_ASSESSOR_IND in VARCHAR2,
625 X_ITEM_LIMIT in NUMBER,
626 X_LOCATION_CD in VARCHAR2,
627 X_UNIT_MODE in VARCHAR2,
628 X_UNIT_CLASS in VARCHAR2,
629 X_COMMENTS in VARCHAR2,
630 X_MODE in VARCHAR2 default 'R'
631 ) AS
632 X_LAST_UPDATE_DATE DATE;
633 X_LAST_UPDATED_BY NUMBER;
634 X_LAST_UPDATE_LOGIN NUMBER;
635 begin
636 X_LAST_UPDATE_DATE := SYSDATE;
637 if(X_MODE = 'I') then
638 X_LAST_UPDATED_BY := 1;
639 X_LAST_UPDATE_LOGIN := 0;
640 elsif (X_MODE = 'R') then
641 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
642 if X_LAST_UPDATED_BY is NULL then
643 X_LAST_UPDATED_BY := -1;
644 end if;
645 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
646 if X_LAST_UPDATE_LOGIN is NULL then
647 X_LAST_UPDATE_LOGIN := -1;
648 end if;
649 else
650 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
651 IGS_GE_MSG_STACK.ADD;
652 APP_EXCEPTION.RAISE_EXCEPTION;
653 end if;
654 Before_DML(
655 p_action=>'UPDATE',
656 x_rowid=>X_ROWID,
657 x_ass_assessor_type=>X_ASS_ASSESSOR_TYPE,
658 x_ass_id=>X_ASS_ID,
659 x_comments=>X_COMMENTS,
660 x_item_limit=>X_ITEM_LIMIT,
661 x_location_cd=>X_LOCATION_CD,
662 x_person_id=>X_PERSON_ID,
663 x_primary_assessor_ind=>X_PRIMARY_ASSESSOR_IND,
664 x_sequence_number=>X_SEQUENCE_NUMBER,
665 x_unit_class=>X_UNIT_CLASS,
666 x_unit_mode=>X_UNIT_MODE,
667 x_creation_date=>X_LAST_UPDATE_DATE,
668 x_created_by=>X_LAST_UPDATED_BY,
669 x_last_update_date=>X_LAST_UPDATE_DATE,
670 x_last_updated_by=>X_LAST_UPDATED_BY,
671 x_last_update_login=>X_LAST_UPDATE_LOGIN
672 );
673 update IGS_AS_ITEM_ASSESSOR set
674 ASS_ASSESSOR_TYPE = NEW_REFERENCES.ASS_ASSESSOR_TYPE,
675 PRIMARY_ASSESSOR_IND = NEW_REFERENCES.PRIMARY_ASSESSOR_IND,
676 ITEM_LIMIT = NEW_REFERENCES.ITEM_LIMIT,
677 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
678 UNIT_MODE = NEW_REFERENCES.UNIT_MODE,
679 UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
680 COMMENTS = NEW_REFERENCES.COMMENTS,
681 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
682 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
683 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
684 where ROWID = X_ROWID;
685 if (sql%notfound) then
686 raise no_data_found;
687 end if;
688 After_DML(
689 p_action => 'UPDATE',
690 x_rowid => X_ROWID
691 );
692 end UPDATE_ROW;
693 procedure ADD_ROW (
694 X_ROWID in out NOCOPY VARCHAR2,
695 X_ASS_ID in NUMBER,
696 X_PERSON_ID in NUMBER,
697 X_SEQUENCE_NUMBER in NUMBER,
698 X_ASS_ASSESSOR_TYPE in VARCHAR2,
699 X_PRIMARY_ASSESSOR_IND in VARCHAR2,
700 X_ITEM_LIMIT in NUMBER,
701 X_LOCATION_CD in VARCHAR2,
702 X_UNIT_MODE in VARCHAR2,
703 X_UNIT_CLASS in VARCHAR2,
704 X_COMMENTS in VARCHAR2,
705 X_MODE in VARCHAR2 default 'R'
706 ) AS
707 cursor c1 is select rowid from IGS_AS_ITEM_ASSESSOR
708 where ASS_ID = X_ASS_ID
709 and PERSON_ID = X_PERSON_ID
710 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
711 ;
712 begin
713 open c1;
714 fetch c1 into X_ROWID;
715 if (c1%notfound) then
716 close c1;
717 INSERT_ROW (
718 X_ROWID,
719 X_ASS_ID,
720 X_PERSON_ID,
721 X_SEQUENCE_NUMBER,
722 X_ASS_ASSESSOR_TYPE,
723 X_PRIMARY_ASSESSOR_IND,
724 X_ITEM_LIMIT,
725 X_LOCATION_CD,
726 X_UNIT_MODE,
727 X_UNIT_CLASS,
728 X_COMMENTS,
729 X_MODE);
730 return;
731 end if;
732 close c1;
733 UPDATE_ROW (
734 X_ROWID,
735 X_ASS_ID,
736 X_PERSON_ID,
737 X_SEQUENCE_NUMBER,
738 X_ASS_ASSESSOR_TYPE,
739 X_PRIMARY_ASSESSOR_IND,
740 X_ITEM_LIMIT,
741 X_LOCATION_CD,
742 X_UNIT_MODE,
743 X_UNIT_CLASS,
744 X_COMMENTS,
745 X_MODE);
746 end ADD_ROW;
747 procedure DELETE_ROW (
748 X_ROWID in VARCHAR2) AS
749 begin
750 Before_DML(
751 p_action => 'DELETE',
752 x_rowid => X_ROWID
753 );
754 delete from IGS_AS_ITEM_ASSESSOR
755 where ROWID = X_ROWID;
756 After_DML(
757 p_action => 'DELETE',
758 x_rowid => X_ROWID
759 );
760 if (sql%notfound) then
761 raise no_data_found;
762 end if;
763 end DELETE_ROW;
764
765 PROCEDURE Check_Constraints (
766 Column_Name IN VARCHAR2 DEFAULT NULL,
767 Column_Value IN VARCHAR2 DEFAULT NULL
768 )
769 AS
770 BEGIN
771 IF column_name is null then
772 NULL;
773 ELSIF upper(Column_name) = 'PRIMARY_ASSESSOR_IND' then
774 new_references.primary_assessor_ind := column_value;
775 ELSIF upper(Column_name) = 'ASS_ASSESSOR_TYPE' then
776 new_references.ass_assessor_type := column_value;
777 ELSIF upper(Column_name) = 'LOCATION_CD' then
778 new_references.location_cd := column_value;
779 ELSIF upper(Column_name) = 'UNIT_CLASS' then
780 new_references.unit_class := column_value;
781 ELSIF upper(Column_name) = 'UNIT_MODE' then
782 new_references.unit_mode := column_value;
783 ELSIF upper(Column_name) = 'ITEM_LIMIT' then
784 new_references.item_limit := igs_ge_number.to_num(column_value);
785 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' then
786 new_references.item_limit := igs_ge_number.to_num(column_value);
787 END IF;
788
789
790 IF upper(column_name) = 'PRIMARY_ASSESSOR_IND' OR
791 column_name is null Then
792 IF new_references.primary_assessor_ind NOT IN ('Y' , 'N') Then
793 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
794 IGS_GE_MSG_STACK.ADD;
795 APP_EXCEPTION.RAISE_EXCEPTION;
796 END IF;
797 END IF;
798
799
800 IF upper(column_name) = 'ASS_ASSESSOR_TYPE' OR
801 column_name is null Then
802 IF new_references.ass_assessor_type <> UPPER(new_references.ass_assessor_type) Then
803 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
804 IGS_GE_MSG_STACK.ADD;
805 APP_EXCEPTION.RAISE_EXCEPTION;
806 END IF;
807 END IF;
808
809 IF upper(column_name) = 'LOCATION_CD' OR
810 column_name is null Then
811 IF new_references.location_cd <>
812 UPPER(new_references.location_cd) Then
813 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
814 IGS_GE_MSG_STACK.ADD;
815 APP_EXCEPTION.RAISE_EXCEPTION;
816 END IF;
817 END IF;
818
819 IF upper(column_name) = 'PRIMARY_ASSESSOR_IND' OR
820 column_name is null Then
821 IF new_references.primary_assessor_ind <>
822 UPPER(new_references.primary_assessor_ind) Then
823 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
824 IGS_GE_MSG_STACK.ADD;
825 APP_EXCEPTION.RAISE_EXCEPTION;
826 END IF;
827 END IF;
828 IF upper(column_name) = 'UNIT_CLASS' OR
829 column_name is null Then
830 IF new_references.unit_class <>
831 UPPER(new_references.unit_class) Then
832 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
833 IGS_GE_MSG_STACK.ADD;
834 APP_EXCEPTION.RAISE_EXCEPTION;
835 END IF;
836 END IF;
837 IF upper(column_name) = 'UNIT_MODE' OR
838 column_name is null Then
839 IF new_references.unit_mode <>
840 UPPER(new_references.unit_mode) Then
841 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
842 IGS_GE_MSG_STACK.ADD;
843 APP_EXCEPTION.RAISE_EXCEPTION;
844 END IF;
845 END IF;
846
847 IF upper(column_name) = 'ITEM_LIMIT' OR
848 column_name is null Then
849 IF new_references.item_limit < 0 OR new_references.item_limit > 99999 Then
850 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
851 IGS_GE_MSG_STACK.ADD;
852 APP_EXCEPTION.RAISE_EXCEPTION;
853 END IF;
854 END IF;
855
856
857 IF upper(column_name) = 'SEQUENCE_NUMBER' OR
858 column_name is null Then
859 IF new_references.sequence_number < 1 OR new_references.sequence_number > 999999 Then
860 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
861 IGS_GE_MSG_STACK.ADD;
862 APP_EXCEPTION.RAISE_EXCEPTION;
863 END IF;
864 END IF;
865
866
867 END Check_Constraints;
868 end IGS_AS_ITEM_ASSESSOR_PKG;