[Home] [Help]
PACKAGE BODY: APPS.IGS_ST_UNT_LOAD_APPO_PKG
Source
1 package body IGS_ST_UNT_LOAD_APPO_PKG as
2 /* $Header: IGSVI12B.pls 115.4 2002/11/29 04:33:50 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_ST_UNT_LOAD_APPO%RowType;
6 new_references IGS_ST_UNT_LOAD_APPO%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_dla_cal_type IN VARCHAR2 DEFAULT NULL,
12 x_ci_sequence_number IN NUMBER DEFAULT NULL,
13 x_unit_cd IN VARCHAR2 DEFAULT NULL,
14 x_version_number IN NUMBER DEFAULT NULL,
15 x_teach_cal_type IN VARCHAR2 DEFAULT NULL,
16 x_percentage IN NUMBER DEFAULT NULL,
17 x_second_percentage IN NUMBER DEFAULT NULL,
18 x_creation_date IN DATE DEFAULT NULL,
19 x_created_by IN NUMBER DEFAULT NULL,
20 x_last_update_date IN DATE DEFAULT NULL,
21 x_last_updated_by IN NUMBER DEFAULT NULL,
22 x_last_update_login IN NUMBER DEFAULT NULL
23 ) as
24
25 CURSOR cur_old_ref_values IS
26 SELECT *
27 FROM IGS_ST_UNT_LOAD_APPO
28 WHERE rowid = x_rowid;
29
30 BEGIN
31
32 l_rowid := x_rowid;
33
34 -- Code for setting the Old and New Reference Values.
35 -- Populate Old Values.
36 Open cur_old_ref_values;
37 Fetch cur_old_ref_values INTO old_references;
38 IF (cur_old_ref_values%NOTFOUND) AND p_action NOT IN ('INSERT','VALIDATE_INSERT') THEN
39 Close cur_old_ref_values;
40 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
41 IGS_GE_MSG_STACK.ADD;
42 App_Exception.Raise_Exception;
43 Return;
44 END IF;
45 Close cur_old_ref_values;
46
47 -- Populate New Values.
48 new_references.dla_cal_type := x_dla_cal_type;
49 new_references.ci_sequence_number := x_ci_sequence_number;
50 new_references.unit_cd := x_unit_cd;
51 new_references.version_number := x_version_number;
52 new_references.teach_cal_type := x_teach_cal_type;
53 new_references.percentage := x_percentage;
54 new_references.second_percentage := x_second_percentage;
55 IF (p_action = 'UPDATE') THEN
56 new_references.creation_date := old_references.creation_date;
57 new_references.created_by := old_references.created_by;
58 ELSE
59 new_references.creation_date := x_creation_date;
60 new_references.created_by := x_created_by;
61 END IF;
62 new_references.last_update_date := x_last_update_date;
63 new_references.last_updated_by := x_last_updated_by;
64 new_references.last_update_login := x_last_update_login;
65
66 END Set_Column_Values;
67
68 PROCEDURE BeforeRowInsertUpdateDelete1(
69 p_inserting IN BOOLEAN DEFAULT FALSE,
70 p_updating IN BOOLEAN DEFAULT FALSE,
71 p_deleting IN BOOLEAN DEFAULT FALSE
72 ) as
73 v_message_name VARCHAR2(30);
74 v_dla_cal_type IGS_ST_UNT_LOAD_APPO.dla_cal_type%TYPE;
75 v_ci_sequence_number IGS_ST_UNT_LOAD_APPO.ci_sequence_number%TYPE;
76 BEGIN
77 IF p_inserting OR p_updating THEN
78 v_dla_cal_type := new_references.dla_cal_type;
79 v_ci_sequence_number := new_references.ci_sequence_number;
80 ELSE
81 v_dla_cal_type := old_references.dla_cal_type;
82 v_ci_sequence_number := old_references.ci_sequence_number;
83 END IF;
84 -- Validate if insert, update or delete is allowed.
85 /*
86 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
87 || Changed the reference of "IGS_ST_VAL_ULA.STAP_VAL_CI_STATUS" to program unit "IGS_EN_VAL_DLA.STAP_VAL_CI_STATUS". -- kdande
88 */
89 IF IGS_EN_VAL_DLA.stap_val_ci_status (
90 v_dla_cal_type,
91 v_ci_sequence_number,
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 -- Validate the unit version status.
98 IF p_inserting OR
99 (p_updating AND
100 (old_references.unit_cd <> new_references.unit_cd AND
101 old_references.version_number <> new_references.version_number)) THEN
102 IF IGS_ST_VAL_ULA.stap_val_ula_uv_sts (
103 new_references.unit_cd,
104 new_references.version_number,
105 v_message_name) = FALSE THEN
106 Fnd_Message.Set_Name('IGS', v_message_name);
107 IGS_GE_MSG_STACK.ADD;
108 App_Exception.Raise_Exception;
109 END IF;
110 END IF;
111
112
113 END BeforeRowInsertUpdateDelete1;
114
115 procedure Check_Constraints (
116 Column_Name IN VARCHAR2 DEFAULT NULL,
117 Column_Value IN VARCHAR2 DEFAULT NULL
118 )
119 as
120 BEGIN
121
122 IF Column_Name is null then
123 NULL;
124 ELSIF upper(Column_Name) = 'CI_SEQUENCE_NUMBER' then
125 new_references.ci_sequence_number := IGS_GE_NUMBER.to_num(column_value);
126 ELSIF upper(Column_Name) = 'SECOND_PERCENTAGE' then
127 new_references.second_percentage := IGS_GE_NUMBER.to_num(column_value);
128 ELSIF upper(Column_Name) = 'DLA_CAL_TYPE' then
129 new_references.dla_cal_type := column_value;
130 ELSIF upper(Column_Name) = 'TEACH_CAL_TYPE' then
131 new_references.teach_cal_type := column_value;
132 ELSIF upper(Column_Name) = 'UNIT_CD' then
133 new_references.unit_cd := column_value;
134 ELSIF upper(Column_Name) = 'PERCENTAGE' then
135 new_references.percentage := IGS_GE_NUMBER.to_num(column_value);
136 END IF;
137
138 IF upper(Column_Name) = 'CI_SEQUENCE_NUMBER' OR Column_Name IS NULL THEN
139 IF new_references.ci_sequence_number < 1 OR new_references.ci_sequence_number > 999999 THEN
140 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
141 IGS_GE_MSG_STACK.ADD;
142 App_Exception.Raise_Exception;
143 END IF;
144 END IF;
145 IF upper(Column_Name) = 'SECOND_PERCENTAGE' OR Column_Name IS NULL THEN
146 IF new_references.second_percentage < 0 OR new_references.second_percentage > 100 THEN
147 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
148 IGS_GE_MSG_STACK.ADD;
149 App_Exception.Raise_Exception;
150 END IF;
151 END IF;
152 IF upper(Column_Name) = 'DLA_CAL_TYPE' OR Column_Name IS NULL THEN
153 IF new_references.dla_cal_type <> UPPER(new_references.dla_cal_type) THEN
154 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
155 IGS_GE_MSG_STACK.ADD;
156 App_Exception.Raise_Exception;
157 END IF;
158 END IF;
159 IF upper(Column_Name) = 'TEACH_CAL_TYPE' OR Column_Name IS NULL THEN
160 IF new_references.teach_cal_type <> UPPER(new_references.teach_cal_type) THEN
161 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
162 IGS_GE_MSG_STACK.ADD;
163 App_Exception.Raise_Exception;
164 END IF;
165 END IF;
166 IF upper(Column_Name) = 'UNIT_CD' OR Column_Name IS NULL THEN
167 IF new_references.unit_cd <> UPPER(new_references.unit_cd) THEN
168 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END IF;
172 END IF;
173 IF upper(Column_Name) = 'PERCENTAGE' OR Column_Name IS NULL THEN
174 IF new_references.percentage < 0 OR new_references.percentage > 100 THEN
175 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
176 IGS_GE_MSG_STACK.ADD;
177 App_Exception.Raise_Exception;
178 END IF;
179 END IF;
180
181 END Check_Constraints;
182
183 PROCEDURE Check_Parent_Existance as
184 BEGIN
185
186 IF (((old_references.dla_cal_type = new_references.dla_cal_type) AND
187 (old_references.ci_sequence_number = new_references.ci_sequence_number) AND
188 (old_references.teach_cal_type = new_references.teach_cal_type)) OR
189 ((new_references.dla_cal_type IS NULL) OR
190 (new_references.ci_sequence_number IS NULL) OR
191 (new_references.teach_cal_type IS NULL))) THEN
192 NULL;
193 ELSE
194 IF NOT IGS_ST_DFT_LOAD_APPO_PKG.Get_PK_For_Validation (
195 new_references.dla_cal_type,
196 new_references.ci_sequence_number,
197 new_references.teach_cal_type
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 END IF;
204
205 IF (((old_references.unit_cd = new_references.unit_cd) AND
206 (old_references.version_number = new_references.version_number) AND
207 (old_references.teach_cal_type = new_references.teach_cal_type)) OR
208 ((new_references.unit_cd IS NULL) OR
209 (new_references.version_number IS NULL) OR
210 (new_references.teach_cal_type IS NULL))) THEN
211 NULL;
212 ELSE
213 IF NOT IGS_PS_UNIT_OFR_PKG.Get_PK_For_Validation (
214 new_references.unit_cd,
215 new_references.version_number,
216 new_references.teach_cal_type
217 ) THEN
218 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
219 IGS_GE_MSG_STACK.ADD;
220 App_Exception.Raise_Exception;
221 END IF;
222 END IF;
223
224 END Check_Parent_Existance;
225
226 function Get_PK_For_Validation (
227 x_dla_cal_type IN VARCHAR2,
228 x_ci_sequence_number IN NUMBER,
229 x_unit_cd IN VARCHAR2,
230 x_version_number IN NUMBER,
231 x_teach_cal_type IN VARCHAR2
232 )return BOOLEAN as
233
234 CURSOR cur_rowid IS
235 SELECT rowid
236 FROM IGS_ST_UNT_LOAD_APPO
237 WHERE dla_cal_type = x_dla_cal_type
238 AND ci_sequence_number = x_ci_sequence_number
239 AND unit_cd = x_unit_cd
240 AND version_number = x_version_number
241 AND teach_cal_type = x_teach_cal_type
242 FOR UPDATE NOWAIT;
243
244 lv_rowid cur_rowid%RowType;
245
246 BEGIN
247
248 Open cur_rowid;
249 Fetch cur_rowid INTO lv_rowid;
250 IF (cur_rowid%FOUND) THEN
251 Close cur_rowid;
252 Return(TRUE);
253 ELSE
254 Close cur_rowid;
255 Return(FALSE);
256 END IF;
257
258 END Get_PK_For_Validation;
259
260 PROCEDURE get_fk_igs_st_dft_load_appo (
261 x_cal_type IN VARCHAR2,
262 x_ci_sequence_number IN NUMBER,
263 x_teach_cal_type IN VARCHAR2
264 ) as
265
266 CURSOR cur_rowid IS
267 SELECT rowid
268 FROM IGS_ST_UNT_LOAD_APPO
269 WHERE dla_cal_type = x_cal_type
270 AND ci_sequence_number = x_ci_sequence_number
271 AND teach_cal_type = x_teach_cal_type ;
272
273 lv_rowid cur_rowid%RowType;
274
275 BEGIN
276
277 Open cur_rowid;
278 Fetch cur_rowid INTO lv_rowid;
279 IF (cur_rowid%FOUND) THEN
280 Close cur_rowid;
281 Fnd_Message.Set_Name ('IGS', 'IGS_ST_ULA_DLA_FK');
282 IGS_GE_MSG_STACK.ADD;
283 App_Exception.Raise_Exception;
284 Return;
285 END IF;
286 Close cur_rowid;
287
288 END get_fk_igs_st_dft_load_appo;
289
290 PROCEDURE GET_FK_IGS_PS_UNIT_OFR (
291 x_unit_cd IN VARCHAR2,
292 x_version_number IN NUMBER,
293 x_cal_type IN VARCHAR2
294 ) as
295
296 CURSOR cur_rowid IS
297 SELECT rowid
298 FROM IGS_ST_UNT_LOAD_APPO
299 WHERE unit_cd = x_unit_cd
300 AND version_number = x_version_number
301 AND teach_cal_type = x_cal_type ;
302
303 lv_rowid cur_rowid%RowType;
304
305 BEGIN
306
307 Open cur_rowid;
308 Fetch cur_rowid INTO lv_rowid;
309 IF (cur_rowid%FOUND) THEN
310 Close cur_rowid;
311 Fnd_Message.Set_Name ('IGS', 'IGS_ST_ULA_UO_FK');
312 IGS_GE_MSG_STACK.ADD;
313 App_Exception.Raise_Exception;
314 Return;
315 END IF;
316 Close cur_rowid;
317
318 END GET_FK_IGS_PS_UNIT_OFR;
319
320 PROCEDURE Before_DML (
321 p_action IN VARCHAR2,
322 x_rowid IN VARCHAR2 DEFAULT NULL,
323 x_dla_cal_type IN VARCHAR2 DEFAULT NULL,
324 x_ci_sequence_number IN NUMBER DEFAULT NULL,
325 x_unit_cd IN VARCHAR2 DEFAULT NULL,
326 x_version_number IN NUMBER DEFAULT NULL,
327 x_teach_cal_type IN VARCHAR2 DEFAULT NULL,
328 x_percentage IN NUMBER DEFAULT NULL,
329 x_second_percentage IN NUMBER DEFAULT NULL,
330 x_creation_date IN DATE DEFAULT NULL,
331 x_created_by IN NUMBER DEFAULT NULL,
332 x_last_update_date IN DATE DEFAULT NULL,
333 x_last_updated_by IN NUMBER DEFAULT NULL,
334 x_last_update_login IN NUMBER DEFAULT NULL
335 ) as
336 BEGIN
337
338 Set_Column_Values (
339 p_action,
340 x_rowid,
341 x_dla_cal_type,
342 x_ci_sequence_number,
343 x_unit_cd,
344 x_version_number,
345 x_teach_cal_type,
346 x_percentage,
347 x_second_percentage,
348 x_creation_date,
349 x_created_by,
350 x_last_update_date,
351 x_last_updated_by,
352 x_last_update_login
353 );
354
355 IF (p_action = 'INSERT') THEN
356 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
357 IF Get_PK_For_Validation (
358 new_references.dla_cal_type,
359 new_references.ci_sequence_number,
360 new_references.unit_cd,
361 new_references.version_number,
362 new_references.teach_cal_type
363 ) THEN
364 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
365 IGS_GE_MSG_STACK.ADD;
366 App_Exception.Raise_Exception;
367 END IF;
368 Check_Constraints;
369 Check_Parent_Existance;
370 ELSIF (p_action = 'UPDATE') THEN
371 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
372 Check_Constraints;
373 Check_Parent_Existance;
374 ELSIF (p_action = 'DELETE') THEN
375 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
376 ELSIF (p_action = 'VALIDATE_INSERT') THEN
377 IF Get_PK_For_Validation (
378 new_references.dla_cal_type,
379 new_references.ci_sequence_number,
380 new_references.unit_cd,
381 new_references.version_number,
382 new_references.teach_cal_type
383 ) THEN
384 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
385 IGS_GE_MSG_STACK.ADD;
386 App_Exception.Raise_Exception;
387 END IF;
388 Check_Constraints;
389 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
390 Check_Constraints;
391 ELSIF (p_action = 'VALIDATE_DELETE') THEN
392 Null;
393 END IF;
394
395 END Before_DML;
396
397 PROCEDURE After_DML (
398 p_action IN VARCHAR2,
399 x_rowid IN VARCHAR2
400 ) as
401 BEGIN
402 l_rowid := x_rowid;
403 END After_DML;
404
405 procedure INSERT_ROW (
406 X_ROWID in out NOCOPY VARCHAR2,
407 X_DLA_CAL_TYPE in VARCHAR2,
408 X_CI_SEQUENCE_NUMBER in NUMBER,
409 X_UNIT_CD in VARCHAR2,
410 X_VERSION_NUMBER in NUMBER,
411 X_TEACH_CAL_TYPE in VARCHAR2,
412 X_PERCENTAGE in NUMBER,
413 X_SECOND_PERCENTAGE in NUMBER,
414 X_MODE in VARCHAR2 default 'R'
415 ) as
416 cursor C is select ROWID from IGS_ST_UNT_LOAD_APPO
417 where DLA_CAL_TYPE = X_DLA_CAL_TYPE
418 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
419 and UNIT_CD = X_UNIT_CD
420 and VERSION_NUMBER = X_VERSION_NUMBER
421 and TEACH_CAL_TYPE = X_TEACH_CAL_TYPE;
422 X_LAST_UPDATE_DATE DATE;
423 X_LAST_UPDATED_BY NUMBER;
424 X_LAST_UPDATE_LOGIN NUMBER;
425 begin
426 X_LAST_UPDATE_DATE := SYSDATE;
427 if(X_MODE = 'I') then
428 X_LAST_UPDATED_BY := 1;
429 X_LAST_UPDATE_LOGIN := 0;
430 elsif (X_MODE = 'R') then
431 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
432 if X_LAST_UPDATED_BY is NULL then
433 X_LAST_UPDATED_BY := -1;
434 end if;
435 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
436 if X_LAST_UPDATE_LOGIN is NULL then
437 X_LAST_UPDATE_LOGIN := -1;
438 end if;
439 else
440 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
441 IGS_GE_MSG_STACK.ADD;
442 app_exception.raise_exception;
443 end if;
444 Before_DML (
445 p_action => 'INSERT',
446 x_rowid => X_ROWID,
447 x_dla_cal_type => X_DLA_CAL_TYPE,
448 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
449 x_unit_cd => X_UNIT_CD,
450 x_version_number => X_VERSION_NUMBER,
451 x_teach_cal_type => X_TEACH_CAL_TYPE,
452 x_percentage => NVL(X_PERCENTAGE,100),
453 x_second_percentage => X_SECOND_PERCENTAGE,
454 x_creation_date => X_LAST_UPDATE_DATE,
455 x_created_by => X_LAST_UPDATED_BY,
456 x_last_update_date => X_LAST_UPDATE_DATE,
457 x_last_updated_by => X_LAST_UPDATED_BY,
458 x_last_update_login => X_LAST_UPDATE_LOGIN
459 );
460
461 insert into IGS_ST_UNT_LOAD_APPO (
462 DLA_CAL_TYPE,
463 CI_SEQUENCE_NUMBER,
464 UNIT_CD,
465 VERSION_NUMBER,
466 TEACH_CAL_TYPE,
467 PERCENTAGE,
468 SECOND_PERCENTAGE,
469 CREATION_DATE,
470 CREATED_BY,
471 LAST_UPDATE_DATE,
472 LAST_UPDATED_BY,
473 LAST_UPDATE_LOGIN
474 ) values (
475 NEW_REFERENCES.DLA_CAL_TYPE,
476 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
477 NEW_REFERENCES.UNIT_CD,
478 NEW_REFERENCES.VERSION_NUMBER,
479 NEW_REFERENCES.TEACH_CAL_TYPE,
480 NEW_REFERENCES.PERCENTAGE,
481 NEW_REFERENCES.SECOND_PERCENTAGE,
482 X_LAST_UPDATE_DATE,
483 X_LAST_UPDATED_BY,
484 X_LAST_UPDATE_DATE,
485 X_LAST_UPDATED_BY,
486 X_LAST_UPDATE_LOGIN
487 );
488
489 open c;
490 fetch c into X_ROWID;
491 if (c%notfound) then
492 close c;
493 raise no_data_found;
494 end if;
495 close c;
496
497 After_DML (
498 p_action => 'INSERT',
499 x_rowid => X_ROWID
500 );
501 end INSERT_ROW;
502
503 procedure LOCK_ROW (
504 X_ROWID in VARCHAR2,
505 X_DLA_CAL_TYPE in VARCHAR2,
506 X_CI_SEQUENCE_NUMBER in NUMBER,
507 X_UNIT_CD in VARCHAR2,
508 X_VERSION_NUMBER in NUMBER,
509 X_TEACH_CAL_TYPE in VARCHAR2,
510 X_PERCENTAGE in NUMBER,
511 X_SECOND_PERCENTAGE in NUMBER
512 ) as
513 cursor c1 is select
514 PERCENTAGE,
515 SECOND_PERCENTAGE
516 from IGS_ST_UNT_LOAD_APPO
517 where ROWID = X_ROWID for update nowait;
518 tlinfo c1%rowtype;
519
520 begin
521 open c1;
522 fetch c1 into tlinfo;
523 if (c1%notfound) then
524 close c1;
525 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
526 IGS_GE_MSG_STACK.ADD;
527 app_exception.raise_exception;
528 return;
529 end if;
530 close c1;
531
532 if ( (tlinfo.PERCENTAGE = X_PERCENTAGE)
533 AND ((tlinfo.SECOND_PERCENTAGE = X_SECOND_PERCENTAGE)
534 OR ((tlinfo.SECOND_PERCENTAGE is null)
535 AND (X_SECOND_PERCENTAGE is null)))
536 ) then
537 null;
538 else
539 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
540 IGS_GE_MSG_STACK.ADD;
541 app_exception.raise_exception;
542 end if;
543 return;
544 end LOCK_ROW;
545
546 procedure UPDATE_ROW (
547 X_ROWID in VARCHAR2,
548 X_DLA_CAL_TYPE in VARCHAR2,
549 X_CI_SEQUENCE_NUMBER in NUMBER,
550 X_UNIT_CD in VARCHAR2,
551 X_VERSION_NUMBER in NUMBER,
552 X_TEACH_CAL_TYPE in VARCHAR2,
553 X_PERCENTAGE in NUMBER,
554 X_SECOND_PERCENTAGE in NUMBER,
555 X_MODE in VARCHAR2 default 'R'
556 ) as
557 X_LAST_UPDATE_DATE DATE;
558 X_LAST_UPDATED_BY NUMBER;
559 X_LAST_UPDATE_LOGIN NUMBER;
560 begin
561 X_LAST_UPDATE_DATE := SYSDATE;
562 if(X_MODE = 'I') then
563 X_LAST_UPDATED_BY := 1;
564 X_LAST_UPDATE_LOGIN := 0;
565 elsif (X_MODE = 'R') then
566 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
567 if X_LAST_UPDATED_BY is NULL then
568 X_LAST_UPDATED_BY := -1;
569 end if;
570 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
571 if X_LAST_UPDATE_LOGIN is NULL then
572 X_LAST_UPDATE_LOGIN := -1;
573 end if;
574 else
575 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
576 IGS_GE_MSG_STACK.ADD;
577 app_exception.raise_exception;
578 end if;
579 Before_DML (
580 p_action => 'UPDATE',
581 x_rowid => X_ROWID,
582 x_dla_cal_type => X_DLA_CAL_TYPE,
583 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
584 x_unit_cd => X_UNIT_CD,
585 x_version_number => X_VERSION_NUMBER,
586 x_teach_cal_type => X_TEACH_CAL_TYPE,
587 x_percentage => X_PERCENTAGE,
588 x_second_percentage => X_SECOND_PERCENTAGE,
589 x_creation_date => X_LAST_UPDATE_DATE,
590 x_created_by => X_LAST_UPDATED_BY,
591 x_last_update_date => X_LAST_UPDATE_DATE,
592 x_last_updated_by => X_LAST_UPDATED_BY,
593 x_last_update_login => X_LAST_UPDATE_LOGIN
594 );
595 update IGS_ST_UNT_LOAD_APPO set
596 PERCENTAGE = NEW_REFERENCES.PERCENTAGE,
597 SECOND_PERCENTAGE = NEW_REFERENCES.SECOND_PERCENTAGE,
598 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
599 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
600 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
601 where ROWID = X_ROWID
602 ;
603 if (sql%notfound) then
604 raise no_data_found;
605 end if;
606 After_DML (
607 p_action => 'UPDATE',
608 x_rowid => X_ROWID
609 );
610 end UPDATE_ROW;
611
612 procedure ADD_ROW (
613 X_ROWID in out NOCOPY VARCHAR2,
614 X_DLA_CAL_TYPE in VARCHAR2,
615 X_CI_SEQUENCE_NUMBER in NUMBER,
616 X_UNIT_CD in VARCHAR2,
617 X_VERSION_NUMBER in NUMBER,
618 X_TEACH_CAL_TYPE in VARCHAR2,
619 X_PERCENTAGE in NUMBER,
620 X_SECOND_PERCENTAGE in NUMBER,
621 X_MODE in VARCHAR2 default 'R'
622 ) as
623 cursor c1 is select rowid from IGS_ST_UNT_LOAD_APPO
624 where DLA_CAL_TYPE = X_DLA_CAL_TYPE
625 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
626 and UNIT_CD = X_UNIT_CD
627 and VERSION_NUMBER = X_VERSION_NUMBER
628 and TEACH_CAL_TYPE = X_TEACH_CAL_TYPE
629 ;
630 begin
631 open c1;
632 fetch c1 into X_ROWID;
633 if (c1%notfound) then
634 close c1;
635 INSERT_ROW (
636 X_ROWID,
637 X_DLA_CAL_TYPE,
638 X_CI_SEQUENCE_NUMBER,
639 X_UNIT_CD,
640 X_VERSION_NUMBER,
641 X_TEACH_CAL_TYPE,
642 X_PERCENTAGE,
643 X_SECOND_PERCENTAGE,
644 X_MODE);
645 return;
646 end if;
647 close c1;
648 UPDATE_ROW (
649 X_ROWID,
650 X_DLA_CAL_TYPE,
651 X_CI_SEQUENCE_NUMBER,
652 X_UNIT_CD,
653 X_VERSION_NUMBER,
654 X_TEACH_CAL_TYPE,
655 X_PERCENTAGE,
656 X_SECOND_PERCENTAGE,
657 X_MODE);
658 end ADD_ROW;
659
660 procedure DELETE_ROW (
661 X_ROWID in VARCHAR2
662 ) as
663 begin
664 Before_DML (
665 p_action => 'DELETE',
666 x_rowid => X_ROWID
667 );
668 delete from IGS_ST_UNT_LOAD_APPO
669 where ROWID = X_ROWID;
670 if (sql%notfound) then
671 raise no_data_found;
672 end if;
673 After_DML (
674 p_action => 'DELETE',
675 x_rowid => X_ROWID
676 );
677 end DELETE_ROW;
678
679 end IGS_ST_UNT_LOAD_APPO_PKG;