[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_UNT_PATRN_ITM_PKG
Source
1 package body IGS_AS_UNT_PATRN_ITM_PKG as
2 /* $Header: IGSDI32B.pls 120.0 2005/07/05 13:00:43 appldev noship $ */
3
4 --
5
6 l_rowid VARCHAR2(25);
7 old_references IGS_AS_UNT_PATRN_ITM%RowType;
8 new_references IGS_AS_UNT_PATRN_ITM%RowType;
9
10 PROCEDURE Set_Column_Values (
11 p_action IN VARCHAR2,
12 x_rowid IN VARCHAR2 DEFAULT NULL,
13 x_unit_cd IN VARCHAR2 DEFAULT NULL,
14 x_version_number IN NUMBER DEFAULT NULL,
15 x_cal_type IN VARCHAR2 DEFAULT NULL,
16 x_ci_sequence_number IN NUMBER DEFAULT NULL,
17 x_ass_pattern_id IN NUMBER DEFAULT NULL,
18 x_ass_id IN NUMBER DEFAULT NULL,
19 x_uai_sequence_number IN NUMBER DEFAULT NULL,
20 x_apportionment_percentage IN NUMBER DEFAULT NULL,
21 x_creation_date IN DATE DEFAULT NULL,
22 x_created_by IN NUMBER DEFAULT NULL,
23 x_last_update_date IN DATE DEFAULT NULL,
24 x_last_updated_by IN NUMBER DEFAULT NULL,
25 x_last_update_login IN NUMBER DEFAULT NULL
26 ) as
27
28 CURSOR cur_old_ref_values IS
29 SELECT *
30 FROM IGS_AS_UNT_PATRN_ITM
31 WHERE rowid = x_rowid;
32
33 BEGIN
34
35 l_rowid := x_rowid;
36
37 -- Code for setting the Old and New Reference Values.
38 -- Populate Old Values.
39 Open cur_old_ref_values;
40 Fetch cur_old_ref_values INTO old_references;
41 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
42 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
43 IGS_GE_MSG_STACK.ADD;
44 Close cur_old_ref_values;
45 App_Exception.Raise_Exception;
46 Return;
47 END IF;
48 Close cur_old_ref_values;
49
50 -- Populate New Values.
51 new_references.unit_cd := x_unit_cd;
52 new_references.version_number := x_version_number;
53 new_references.cal_type:= x_cal_type;
54 new_references.ci_sequence_number := x_ci_sequence_number;
55 new_references.ass_pattern_id := x_ass_pattern_id;
56 new_references.ass_id := x_ass_id;
57 new_references.uai_sequence_number := x_uai_sequence_number;
58 new_references.apportionment_percentage := x_apportionment_percentage;
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66 new_references.last_update_date := x_last_update_date;
67 new_references.last_updated_by := x_last_updated_by;
68 new_references.last_update_login := x_last_update_login;
69
70 END Set_Column_Values;
71
72 -- Trigger description :-
73 -- "OSS_TST".trg_uapi_br_id
74 -- BEFORE INSERT OR DELETE
75 -- ON IGS_AS_UNT_PATRN_ITM
76 -- FOR EACH ROW
77
78 PROCEDURE BeforeRowInsertDelete1(
79 p_inserting IN BOOLEAN DEFAULT FALSE,
80 p_updating IN BOOLEAN DEFAULT FALSE,
81 p_deleting IN BOOLEAN DEFAULT FALSE
82 ) as
83 v_message_name varchar2(30);
84 CURSOR c_uap (cp_unit_cd IGS_AS_UNTAS_PATTERN.unit_cd%TYPE,
85 cp_version_number IGS_AS_UNTAS_PATTERN.version_number%TYPE,
86 cp_cal_type IGS_AS_UNTAS_PATTERN.cal_type%TYPE,
87 cp_ci_sequence_number IGS_AS_UNTAS_PATTERN.ci_sequence_number%TYPE,
88 cp_ass_pattern_id IGS_AS_UNTAS_PATTERN.ass_pattern_id%TYPE) IS
89 SELECT uap.action_dt
90 FROM IGS_AS_UNTAS_PATTERN uap
91 WHERE uap.unit_cd = cp_unit_cd AND
92 uap.version_number = cp_version_number AND
93 uap.cal_type = cp_cal_type AND
94 uap.ci_sequence_number = cp_ci_sequence_number AND
95 uap.ass_pattern_id = cp_ass_pattern_id AND
96 uap.action_dt IS NULL
97 FOR UPDATE OF uap.action_dt NOWAIT;
98 BEGIN
99 -- Validate IGS_AD_LOCATION code, IGS_PS_UNIT class and IGS_PS_UNIT mode must match at the item and
100 -- pattern level.
101 IF p_inserting THEN
102 IF IGS_AS_VAL_UAPI.assp_val_uapi_uoo ( new_references.unit_cd,
103 new_references.version_number,
104 new_references.cal_type,
105 new_references.ci_sequence_number,
106 new_references.ass_pattern_id,
107 new_references.ass_id,
108 new_references.uai_sequence_number,
109 v_message_name) = FALSE THEN
110 Fnd_Message.Set_Name('IGS', v_message_name);
111 IGS_GE_MSG_STACK.ADD;
112 App_Exception.Raise_Exception;
113 END IF;
114 END IF;
115 IF p_inserting THEN
116 -- Update the action date of the IGS_AS_UNTAS_PATTERN table
117 FOR v_uap_rec IN c_uap( new_references.unit_cd,
118 new_references.version_number,
119 new_references.cal_type,
120 new_references.ci_sequence_number,
121 new_references.ass_pattern_id) LOOP
122 UPDATE IGS_AS_UNTAS_PATTERN uap
123 SET uap.action_dt = SYSDATE
124 WHERE CURRENT OF c_uap;
125 END LOOP;
126 END IF;
127 IF p_deleting THEN
128 -- Update the action date of the IGS_AS_UNTAS_PATTERN table
129 FOR v_uap_rec IN c_uap( old_references.unit_cd,
130 old_references.version_number,
131 old_references.cal_type,
132 old_references.ci_sequence_number,
133 old_references.ass_pattern_id) LOOP
134 UPDATE IGS_AS_UNTAS_PATTERN uap
135 SET uap.action_dt = SYSDATE
136 WHERE CURRENT OF c_uap;
137 END LOOP;
138 END IF;
139
140
141 END BeforeRowInsertDelete1;
142
143
144 PROCEDURE Check_Parent_Existance as
145 BEGIN
146
147 IF (((old_references.unit_cd = new_references.unit_cd) AND
148 (old_references.version_number = new_references.version_number) AND
149 (old_references.cal_type= new_references.cal_type) AND
150 (old_references.ci_sequence_number = new_references.ci_sequence_number) AND
151 (old_references.ass_id = new_references.ass_id) AND
152 (old_references.uai_sequence_number = new_references.uai_sequence_number)) OR
153 ((new_references.unit_cd IS NULL) OR
154 (new_references.version_number IS NULL) OR
155 (new_references.cal_type IS NULL) OR
156 (new_references.ci_sequence_number IS NULL) OR
157 (new_references.ass_id IS NULL) OR
158 (new_references.uai_sequence_number IS NULL))) THEN
159 NULL;
160 ELSE
161 IF IGS_AS_UNITASS_ITEM_PKG.Get_UK_For_Validation (
162 new_references.unit_cd,
163 new_references.version_number,
164 new_references.cal_type,
165 new_references.ci_sequence_number,
166 new_references.ass_id,
167 new_references.uai_sequence_number
168 )THEN
169 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
170 IGS_GE_MSG_STACK.ADD;
171 App_Exception.Raise_Exception;
172 END IF;
173 END IF;
174
175 IF (((old_references.unit_cd = new_references.unit_cd) AND
176 (old_references.version_number = new_references.version_number) AND
177 (old_references.cal_type= new_references.cal_type) AND
178 (old_references.ci_sequence_number = new_references.ci_sequence_number) AND
179 (old_references.ass_pattern_id = new_references.ass_pattern_id)) OR
180 ((new_references.unit_cd IS NULL) OR
181 (new_references.version_number IS NULL) OR
182 (new_references.cal_type IS NULL) OR
183 (new_references.ci_sequence_number IS NULL) OR
184 (new_references.ass_pattern_id IS NULL))) THEN
185 NULL;
186 ELSE
187 IF NOT(IGS_AS_UNTAS_PATTERN_PKG.Get_PK_For_Validation (
188 new_references.unit_cd,
189 new_references.version_number,
190 new_references.cal_type,
191 new_references.ci_sequence_number,
192 new_references.ass_pattern_id
193 ))THEN
194 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
195 IGS_GE_MSG_STACK.ADD;
196 App_Exception.Raise_Exception;
197 END IF;
198 END IF;
199
200 END Check_Parent_Existance;
201
202 PROCEDURE Check_Constraints (
203 Column_Name IN VARCHAR2 DEFAULT NULL,
204 Column_Value IN VARCHAR2 DEFAULT NULL
205 ) as
206 BEGIN
207
208 IF column_name is null then
209 NULL;
210 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
211 new_references.ci_sequence_number:= igs_ge_number.to_num(column_value);
212 ELSIF upper(Column_name) = 'APPORTIONMENT_PERCENTAGE' then
213 new_references.apportionment_percentage:= igs_ge_number.to_num(column_value);
214 ELSIF upper(Column_name) = 'UAI_SEQUENCE_NUMBER' then
215 new_references.uai_sequence_number:= igs_ge_number.to_num(column_value);
216 ELSIF upper(Column_name) = 'CAL_TYPE' then
217 new_references.cal_type:= column_value;
218 ELSIF upper(Column_name) = 'UNIT_CD' then
219 new_references.unit_cd:= column_value;
220 END IF;
221 IF upper(column_name) = 'CI_SEQUENCE_NUMBER' OR
222 column_name is null Then
223 IF new_references.ci_sequence_number < 1 AND new_references.ci_sequence_number > 999999 Then
224 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
225 IGS_GE_MSG_STACK.ADD;
226 App_Exception.Raise_Exception;
227 END IF;
228 END IF;
229
230 IF upper(column_name) = 'APPORTIONMENT_PERCENTAGE' OR
231 column_name is null Then
232 IF new_references.apportionment_percentage < 0 AND new_references.apportionment_percentage > 100 Then
233 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
234 IGS_GE_MSG_STACK.ADD;
235 App_Exception.Raise_Exception;
236 END IF;
237 END IF;
238 IF upper(column_name) = 'UAI_SEQUENCE_NUMBER' OR
239 column_name is null Then
240 IF new_references.uai_sequence_number < 1 AND new_references.uai_sequence_number > 999999 Then
241 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
242 IGS_GE_MSG_STACK.ADD;
243 App_Exception.Raise_Exception;
244 END IF;
245 END IF;
246 IF upper(column_name) = 'CAL_TYPE' OR
247 column_name is null Then
248 IF new_references.cal_type <> UPPER(new_references.cal_type) Then
249 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
250 IGS_GE_MSG_STACK.ADD;
251 App_Exception.Raise_Exception;
252 END IF;
253 END IF;
254 IF upper(column_name) = 'UNIT_CD' OR
255 column_name is null Then
256 IF new_references.unit_cd <> UPPER(new_references.unit_cd) Then
257 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
258 IGS_GE_MSG_STACK.ADD;
259 App_Exception.Raise_Exception;
260 END IF;
261 END IF;
262
263
264 END Check_Constraints;
265
266
267 FUNCTION Get_PK_For_Validation (
268 x_unit_cd IN VARCHAR2,
269 x_version_number IN NUMBER,
270 x_cal_type IN VARCHAR2,
271 x_ci_sequence_number IN NUMBER,
272 x_ass_pattern_id IN NUMBER,
273 x_ass_id IN NUMBER,
274 x_uai_sequence_number IN NUMBER
275 ) RETURN BOOLEAN AS
276 CURSOR cur_rowid IS
277 SELECT rowid
278 FROM IGS_AS_UNT_PATRN_ITM
279 WHERE unit_cd = x_unit_cd
280 AND version_number = x_version_number
281 AND cal_type= x_cal_type
282 AND ci_sequence_number = x_ci_sequence_number
283 AND ass_pattern_id = x_ass_pattern_id
284 AND ass_id = x_ass_id
285 AND uai_sequence_number = x_uai_sequence_number
286 FOR UPDATE NOWAIT;
287
288 lv_rowid cur_rowid%RowType;
289
290 BEGIN
291
292 Open cur_rowid;
293 Fetch cur_rowid INTO lv_rowid;
294 IF (cur_rowid%FOUND) THEN
295 Close cur_rowid;
296 Return (TRUE);
297 ELSE
298 Close cur_rowid;
299 Return (FALSE);
300 END IF;
301
302 END Get_PK_For_Validation;
303
304 PROCEDURE GET_FK_IGS_AS_UNITASS_ITEM (
305 x_unit_cd IN VARCHAR2,
306 x_version_number IN NUMBER,
307 x_cal_type IN VARCHAR2,
308 x_ci_sequence_number IN NUMBER,
309 x_ass_id IN NUMBER,
310 x_sequence_number IN NUMBER
311 ) as
312
313 CURSOR cur_rowid IS
314 SELECT rowid
315 FROM IGS_AS_UNT_PATRN_ITM
316 WHERE unit_cd = x_unit_cd
317 AND version_number = x_version_number
318 AND cal_type= x_cal_type
319 AND ci_sequence_number = x_ci_sequence_number
320 AND ass_id = x_ass_id
321 AND uai_sequence_number = x_sequence_number ;
322
323 lv_rowid cur_rowid%RowType;
324
325 BEGIN
326
327 Open cur_rowid;
328 Fetch cur_rowid INTO lv_rowid;
329 IF (cur_rowid%FOUND) THEN
330 Fnd_Message.Set_Name ('IGS', 'IGS_AS_UAPI_UAI_FK');
331 IGS_GE_MSG_STACK.ADD;
332 Close cur_rowid;
333 App_Exception.Raise_Exception;
334 Return;
335 END IF;
336 Close cur_rowid;
337
338 END GET_FK_IGS_AS_UNITASS_ITEM;
339
340 PROCEDURE GET_FK_IGS_AS_UNTAS_PATTERN (
341 x_unit_cd IN VARCHAR2,
342 x_version_number IN NUMBER,
343 x_cal_type IN VARCHAR2,
344 x_ci_sequence_number IN NUMBER,
345 x_ass_pattern_id IN NUMBER
346 ) as
347
348 CURSOR cur_rowid IS
349 SELECT rowid
350 FROM IGS_AS_UNT_PATRN_ITM
351 WHERE unit_cd = x_unit_cd
352 AND version_number = x_version_number
353 AND cal_type= x_cal_type
354 AND ci_sequence_number = x_ci_sequence_number
355 AND ass_pattern_id = x_ass_pattern_id ;
356
357 lv_rowid cur_rowid%RowType;
358
359 BEGIN
360
361 Open cur_rowid;
362 Fetch cur_rowid INTO lv_rowid;
363 IF (cur_rowid%FOUND) THEN
364 Fnd_Message.Set_Name ('IGS', 'IGS_AS_UAPI_UAP_FK');
365 IGS_GE_MSG_STACK.ADD;
366 Close cur_rowid;
367 App_Exception.Raise_Exception;
368 Return;
369 END IF;
370 Close cur_rowid;
371
372 END GET_FK_IGS_AS_UNTAS_PATTERN;
373
374 PROCEDURE Before_DML (
375 p_action IN VARCHAR2,
376 x_rowid IN VARCHAR2 DEFAULT NULL,
377 x_unit_cd IN VARCHAR2 DEFAULT NULL,
378 x_version_number IN NUMBER DEFAULT NULL,
379 x_cal_type IN VARCHAR2 DEFAULT NULL,
380 x_ci_sequence_number IN NUMBER DEFAULT NULL,
381 x_ass_pattern_id IN NUMBER DEFAULT NULL,
382 x_ass_id IN NUMBER DEFAULT NULL,
383 x_uai_sequence_number IN NUMBER DEFAULT NULL,
384 x_apportionment_percentage IN NUMBER DEFAULT NULL,
385 x_creation_date IN DATE DEFAULT NULL,
386 x_created_by IN NUMBER DEFAULT NULL,
387 x_last_update_date IN DATE DEFAULT NULL,
388 x_last_updated_by IN NUMBER DEFAULT NULL,
389 x_last_update_login IN NUMBER DEFAULT NULL
390 ) as
391 BEGIN
392
393 Set_Column_Values (
394 p_action,
395 x_rowid,
396 x_unit_cd,
397 x_version_number,
398 x_cal_type,
399 x_ci_sequence_number,
400 x_ass_pattern_id,
401 x_ass_id,
402 x_uai_sequence_number,
403 x_apportionment_percentage,
404 x_creation_date,
405 x_created_by,
406 x_last_update_date,
407 x_last_updated_by,
408 x_last_update_login
409 );
410
411 IF (p_action = 'INSERT') THEN
412 -- Call all the procedures related to Before Insert.
413 BeforeRowInsertDelete1 ( p_inserting => TRUE );
414 IF Get_PK_For_Validation (
415 new_references.unit_cd ,
416 new_references.version_number ,
417 new_references.cal_type,
418 new_references.ci_sequence_number,
419 new_references.ass_pattern_id ,
420 new_references.ass_id ,
421 new_references.uai_sequence_number
422 ) THEN
423 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
424 IGS_GE_MSG_STACK.ADD;
425 App_Exception.Raise_Exception;
426 END IF;
427 Check_Constraints;
428 Check_Parent_Existance;
429 ELSIF (p_action = 'UPDATE') THEN
430 -- Call all the procedures related to Before Update.
431 Null;
432 Check_Constraints;
433 Check_Parent_Existance;
434 ELSIF (p_action = 'DELETE') THEN
435 -- Call all the procedures related to Before Delete.
436 BeforeRowInsertDelete1 ( p_deleting => TRUE );
437 ELSIF (p_action = 'VALIDATE_INSERT') THEN
438 IF Get_PK_For_Validation (
439 new_references.unit_cd ,
440 new_references.version_number,
441 new_references.cal_type ,
442 new_references.ci_sequence_number,
443 new_references.ass_pattern_id ,
444 new_references.ass_id ,
445 new_references.uai_sequence_number
446 ) THEN
447 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
448 IGS_GE_MSG_STACK.ADD;
449 App_Exception.Raise_Exception;
450 END IF;
451 Check_Constraints;
452 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
453 Check_Constraints;
454 ELSIF (p_action = 'VALIDATE_DELETE') THEN
455 NULL;
456 END IF;
457
458 END Before_DML;
459
460 --
461 procedure INSERT_ROW (
462 X_ROWID in out NOCOPY VARCHAR2,
463 X_UNIT_CD in VARCHAR2,
464 X_VERSION_NUMBER in NUMBER,
465 X_CAL_TYPE in VARCHAR2,
466 X_CI_SEQUENCE_NUMBER in NUMBER,
467 X_ASS_PATTERN_ID in NUMBER,
468 X_ASS_ID in NUMBER,
469 X_UAI_SEQUENCE_NUMBER in NUMBER,
470 X_APPORTIONMENT_PERCENTAGE in NUMBER,
471 X_MODE in VARCHAR2 default 'R'
472 ) as
473 cursor C is select ROWID from IGS_AS_UNT_PATRN_ITM
474 where UNIT_CD = X_UNIT_CD
475 and VERSION_NUMBER = X_VERSION_NUMBER
476 and CAL_TYPE = X_CAL_TYPE
477 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
478 and ASS_PATTERN_ID = X_ASS_PATTERN_ID
479 and ASS_ID = X_ASS_ID
480 and UAI_SEQUENCE_NUMBER = X_UAI_SEQUENCE_NUMBER;
481 X_LAST_UPDATE_DATE DATE;
482 X_LAST_UPDATED_BY NUMBER;
483 X_LAST_UPDATE_LOGIN NUMBER;
484 X_REQUEST_ID NUMBER;
485 X_PROGRAM_ID NUMBER;
486 X_PROGRAM_APPLICATION_ID NUMBER;
487 X_PROGRAM_UPDATE_DATE DATE;
488
489 begin
490 X_LAST_UPDATE_DATE := SYSDATE;
491 if(X_MODE = 'I') then
492 X_LAST_UPDATED_BY := 1;
493 X_LAST_UPDATE_LOGIN := 0;
494 elsif (X_MODE = 'R') then
495 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
496 if X_LAST_UPDATED_BY is NULL then
497 X_LAST_UPDATED_BY := -1;
498 end if;
499 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
500 if X_LAST_UPDATE_LOGIN is NULL then
501 X_LAST_UPDATE_LOGIN := -1;
502 end if;
503 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
504 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
505
506 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
507 if (X_REQUEST_ID = -1) then
508 X_REQUEST_ID := NULL;
509 X_PROGRAM_ID := NULL;
510 X_PROGRAM_APPLICATION_ID := NULL;
511 X_PROGRAM_UPDATE_DATE := NULL;
512 else
513 X_PROGRAM_UPDATE_DATE := SYSDATE;
514 end if;
515 else
516 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
517 IGS_GE_MSG_STACK.ADD;
518 app_exception.raise_exception;
519 end if;
520 --
521 Before_DML(
522 p_action=>'INSERT',
523 x_rowid=>X_ROWID,
524 x_apportionment_percentage=>X_APPORTIONMENT_PERCENTAGE,
525 x_ass_id=>X_ASS_ID,
526 x_ass_pattern_id=>X_ASS_PATTERN_ID,
527 x_cal_type=>X_CAL_TYPE,
528 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
529 x_uai_sequence_number=>X_UAI_SEQUENCE_NUMBER,
530 x_unit_cd=>X_UNIT_CD,
531 x_version_number=>X_VERSION_NUMBER,
532 x_creation_date=>X_LAST_UPDATE_DATE,
533 x_created_by=>X_LAST_UPDATED_BY,
534 x_last_update_date=>X_LAST_UPDATE_DATE,
535 x_last_updated_by=>X_LAST_UPDATED_BY,
536 x_last_update_login=>X_LAST_UPDATE_LOGIN
537 );
538 --
539 insert into IGS_AS_UNT_PATRN_ITM (
540 UNIT_CD,
541 VERSION_NUMBER,
542 CAL_TYPE,
543 CI_SEQUENCE_NUMBER,
544 ASS_PATTERN_ID,
545 ASS_ID,
546 UAI_SEQUENCE_NUMBER,
547 APPORTIONMENT_PERCENTAGE,
548 CREATION_DATE,
549 CREATED_BY,
550 LAST_UPDATE_DATE,
551 LAST_UPDATED_BY,
552 LAST_UPDATE_LOGIN,
553 REQUEST_ID,
554 PROGRAM_ID,
555 PROGRAM_APPLICATION_ID,
556 PROGRAM_UPDATE_DATE
557
558 ) values (
559 NEW_REFERENCES.UNIT_CD,
560 NEW_REFERENCES.VERSION_NUMBER,
561 NEW_REFERENCES.CAL_TYPE,
562 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
563 NEW_REFERENCES.ASS_PATTERN_ID,
564 NEW_REFERENCES.ASS_ID,
565 NEW_REFERENCES.UAI_SEQUENCE_NUMBER,
566 NEW_REFERENCES.APPORTIONMENT_PERCENTAGE,
567 X_LAST_UPDATE_DATE,
568 X_LAST_UPDATED_BY,
569 X_LAST_UPDATE_DATE,
570 X_LAST_UPDATED_BY,
571 X_LAST_UPDATE_LOGIN,
572 X_REQUEST_ID,
573 X_PROGRAM_ID,
574 X_PROGRAM_APPLICATION_ID,
575 X_PROGRAM_UPDATE_DATE
576
577 );
578
579 open c;
580 fetch c into X_ROWID;
581 if (c%notfound) then
582 close c;
583 raise no_data_found;
584 end if;
585 close c;
586 --
587 --
588 end INSERT_ROW;
589
590 procedure LOCK_ROW (
591 X_ROWID in VARCHAR2,
592 X_UNIT_CD in VARCHAR2,
593 X_VERSION_NUMBER in NUMBER,
594 X_CAL_TYPE in VARCHAR2,
595 X_CI_SEQUENCE_NUMBER in NUMBER,
596 X_ASS_PATTERN_ID in NUMBER,
597 X_ASS_ID in NUMBER,
598 X_UAI_SEQUENCE_NUMBER in NUMBER,
599 X_APPORTIONMENT_PERCENTAGE in NUMBER
600 ) as
601 cursor c1 is select
602 APPORTIONMENT_PERCENTAGE
603 from IGS_AS_UNT_PATRN_ITM
604 where ROWID = X_ROWID for update nowait;
605 tlinfo c1%rowtype;
606
607 begin
608 open c1;
609 fetch c1 into tlinfo;
610 if (c1%notfound) then
611 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
612 IGS_GE_MSG_STACK.ADD;
613 close c1;
614 app_exception.raise_exception;
615 return;
616 end if;
617 close c1;
618
619 if ( ((tlinfo.APPORTIONMENT_PERCENTAGE = X_APPORTIONMENT_PERCENTAGE)
620 OR ((tlinfo.APPORTIONMENT_PERCENTAGE is null)
621 AND (X_APPORTIONMENT_PERCENTAGE is null)))
622 ) then
623 null;
624 else
625 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
626 IGS_GE_MSG_STACK.ADD;
627 app_exception.raise_exception;
628 end if;
629 return;
630 end LOCK_ROW;
631
632 procedure UPDATE_ROW (
633 X_ROWID in VARCHAR2,
634 X_UNIT_CD in VARCHAR2,
635 X_VERSION_NUMBER in NUMBER,
636 X_CAL_TYPE in VARCHAR2,
637 X_CI_SEQUENCE_NUMBER in NUMBER,
638 X_ASS_PATTERN_ID in NUMBER,
639 X_ASS_ID in NUMBER,
640 X_UAI_SEQUENCE_NUMBER in NUMBER,
641 X_APPORTIONMENT_PERCENTAGE in NUMBER,
642 X_MODE in VARCHAR2 default 'R'
643 ) as
644 X_LAST_UPDATE_DATE DATE;
645 X_LAST_UPDATED_BY NUMBER;
646 X_LAST_UPDATE_LOGIN NUMBER;
647 X_REQUEST_ID NUMBER;
648 X_PROGRAM_ID NUMBER;
649 X_PROGRAM_APPLICATION_ID NUMBER;
650 X_PROGRAM_UPDATE_DATE DATE;
651 begin
652 X_LAST_UPDATE_DATE := SYSDATE;
653 if(X_MODE = 'I') then
654 X_LAST_UPDATED_BY := 1;
655 X_LAST_UPDATE_LOGIN := 0;
656 elsif (X_MODE = 'R') then
657 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
658 if X_LAST_UPDATED_BY is NULL then
659 X_LAST_UPDATED_BY := -1;
660 end if;
661 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
662 if X_LAST_UPDATE_LOGIN is NULL then
663 X_LAST_UPDATE_LOGIN := -1;
664 end if;
665 else
666 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
667 IGS_GE_MSG_STACK.ADD;
668 app_exception.raise_exception;
669 end if;
670 Before_DML(
671 p_action=>'UPDATE',
672 x_rowid=>X_ROWID,
673 x_apportionment_percentage=>X_APPORTIONMENT_PERCENTAGE,
674 x_ass_id=>X_ASS_ID,
675 x_ass_pattern_id=>X_ASS_PATTERN_ID,
676 x_cal_type=>X_CAL_TYPE,
677 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
678 x_uai_sequence_number=>X_UAI_SEQUENCE_NUMBER,
679 x_unit_cd=>X_UNIT_CD,
680 x_version_number=>X_VERSION_NUMBER,
681 x_creation_date=>X_LAST_UPDATE_DATE,
682 x_created_by=>X_LAST_UPDATED_BY,
683 x_last_update_date=>X_LAST_UPDATE_DATE,
684 x_last_updated_by=>X_LAST_UPDATED_BY,
685 x_last_update_login=>X_LAST_UPDATE_LOGIN
686 );
687
688 if (X_MODE = 'R') then
689 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
690 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
691 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
692 if (X_REQUEST_ID = -1) then
693 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
694 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
695 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
696 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
697 else
698 X_PROGRAM_UPDATE_DATE := SYSDATE;
699 end if;
700 --
701
702 --
703 end if;
704 update IGS_AS_UNT_PATRN_ITM set
705 APPORTIONMENT_PERCENTAGE = NEW_REFERENCES.APPORTIONMENT_PERCENTAGE,
706 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
707 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
708 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
709 REQUEST_ID = X_REQUEST_ID,
710 PROGRAM_ID = X_PROGRAM_ID,
711 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
712 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
713 where ROWID = X_ROWID;
714 if (sql%notfound) then
715 raise no_data_found;
716 end if;
717 --
718 --
719 end UPDATE_ROW;
720
721 procedure ADD_ROW (
722 X_ROWID in out NOCOPY VARCHAR2,
723 X_UNIT_CD in VARCHAR2,
724 X_VERSION_NUMBER in NUMBER,
725 X_CAL_TYPE in VARCHAR2,
726 X_CI_SEQUENCE_NUMBER in NUMBER,
727 X_ASS_PATTERN_ID in NUMBER,
728 X_ASS_ID in NUMBER,
729 X_UAI_SEQUENCE_NUMBER in NUMBER,
730 X_APPORTIONMENT_PERCENTAGE in NUMBER,
731 X_MODE in VARCHAR2 default 'R'
732 ) as
733 cursor c1 is select rowid from IGS_AS_UNT_PATRN_ITM
734 where UNIT_CD = X_UNIT_CD
735 and VERSION_NUMBER = X_VERSION_NUMBER
736 and CAL_TYPE = X_CAL_TYPE
737 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
738 and ASS_PATTERN_ID = X_ASS_PATTERN_ID
739 and ASS_ID = X_ASS_ID
740 and UAI_SEQUENCE_NUMBER = X_UAI_SEQUENCE_NUMBER
741 ;
742 begin
743 open c1;
744 fetch c1 into X_ROWID;
745 if (c1%notfound) then
746 close c1;
747 INSERT_ROW (
748 X_ROWID,
749 X_UNIT_CD,
750 X_VERSION_NUMBER,
751 X_CAL_TYPE,
752 X_CI_SEQUENCE_NUMBER,
753 X_ASS_PATTERN_ID,
754 X_ASS_ID,
755 X_UAI_SEQUENCE_NUMBER,
756 X_APPORTIONMENT_PERCENTAGE,
757 X_MODE);
758 return;
759 end if;
760 close c1;
761 UPDATE_ROW (
762 X_ROWID,
763 X_UNIT_CD,
764 X_VERSION_NUMBER,
765 X_CAL_TYPE,
766 X_CI_SEQUENCE_NUMBER,
767 X_ASS_PATTERN_ID,
768 X_ASS_ID,
769 X_UAI_SEQUENCE_NUMBER,
770 X_APPORTIONMENT_PERCENTAGE,
771 X_MODE);
772 end ADD_ROW;
773
774 procedure DELETE_ROW (
775 X_ROWID in VARCHAR2) as
776 begin
777 --
778 Before_DML(
779 p_action => 'DELETE',
780 x_rowid => X_ROWID
781 );
782 --
783 delete from IGS_AS_UNT_PATRN_ITM
784 where ROWID = X_ROWID;
785 if (sql%notfound) then
786 raise no_data_found;
787 end if;
788 --
789 --
790 end DELETE_ROW;
791
792 end IGS_AS_UNT_PATRN_ITM_PKG;