[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_UNTAS_PATTERN_PKG
Source
1 package body IGS_AS_UNTAS_PATTERN_PKG as
2 /* $Header: IGSDI33B.pls 120.0 2005/07/05 12:26:53 appldev noship $ */
3
4 --
5 l_rowid VARCHAR2(25);
6 old_references IGS_AS_UNTAS_PATTERN_ALL%RowType;
7 new_references IGS_AS_UNTAS_PATTERN_ALL%RowType;
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_unit_cd IN VARCHAR2 DEFAULT NULL,
12 x_version_number IN NUMBER DEFAULT NULL,
13 x_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_ci_sequence_number IN NUMBER DEFAULT NULL,
15 x_ass_pattern_id IN NUMBER DEFAULT NULL,
16 x_ass_pattern_cd IN VARCHAR2 DEFAULT NULL,
17 x_description IN VARCHAR2 DEFAULT NULL,
18 x_location_cd IN VARCHAR2 DEFAULT NULL,
19 x_unit_class IN VARCHAR2 DEFAULT NULL,
20 x_unit_mode IN VARCHAR2 DEFAULT NULL,
21 x_dflt_pattern_ind IN VARCHAR2 DEFAULT NULL,
22 x_logical_delete_dt IN DATE DEFAULT NULL,
23 x_action_dt IN DATE DEFAULT NULL,
24 x_creation_date IN DATE DEFAULT NULL,
25 x_created_by IN NUMBER DEFAULT NULL,
26 x_last_update_date IN DATE DEFAULT NULL,
27 x_last_updated_by IN NUMBER DEFAULT NULL,
28 x_last_update_login IN NUMBER DEFAULT NULL,
29 x_org_id IN NUMBER DEFAULT NULL
30 ) as
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_AS_UNTAS_PATTERN_ALL
34 WHERE rowid = x_rowid;
35 BEGIN
36 l_rowid := x_rowid;
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 -- Populate New Values.
50 new_references.unit_cd := x_unit_cd;
51 new_references.version_number := x_version_number;
52 new_references.cal_type:= x_cal_type;
53 new_references.ci_sequence_number := x_ci_sequence_number;
54 new_references.ass_pattern_id := x_ass_pattern_id;
55 new_references.ass_pattern_cd := x_ass_pattern_cd;
56 new_references.description := x_description;
57 new_references.location_cd := x_location_cd;
58 new_references.unit_class:= x_unit_class;
59 new_references.unit_mode:= x_unit_mode;
60 new_references.dflt_pattern_ind := x_dflt_pattern_ind;
61 new_references.logical_delete_dt := x_logical_delete_dt;
62 new_references.action_dt := x_action_dt;
63 new_references.org_id := x_org_id;
64 IF (p_action = 'UPDATE') THEN
65 new_references.creation_date := old_references.creation_date;
66 new_references.created_by := old_references.created_by;
67 ELSE
68 new_references.creation_date := x_creation_date;
69 new_references.created_by := x_created_by;
70 END IF;
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74 END Set_Column_Values;
75 -- Trigger description :-
76 -- "OSS_TST".trg_uap_br_iu
77 -- BEFORE INSERT OR UPDATE
78 -- ON IGS_AS_UNTAS_PATTERN
79 -- FOR EACH ROW
80 PROCEDURE BeforeRowInsertUpdate1(
81 p_inserting IN BOOLEAN DEFAULT FALSE,
82 p_updating IN BOOLEAN DEFAULT FALSE,
83 p_deleting IN BOOLEAN DEFAULT FALSE
84 ) as
85 v_message_name varchar2(30);
86 BEGIN
87 IF p_inserting OR p_updating THEN
88 -- Validate IGS_AD_LOCATION closed indicator
89
90 -- As part of the bug# 1956374 changed to the below call from IGS_AS_VAL_UAP.crsp_val_loc_cd
91 IF IGS_PS_VAL_UOO.crsp_val_loc_cd (
92 new_references.location_cd,
93 v_message_name) = FALSE THEN
94 Fnd_Message.Set_Name('IGS', v_message_name);
95 IGS_GE_MSG_STACK.ADD;
96 App_Exception.Raise_Exception;
97 END IF;
98 -- Validate IGS_PS_UNIT mode closed indicator
99 -- As part of the bug# 1956374 changed to the below call from IGS_AS_VAL_UAP.crsp_val_um_closed
100 IF IGS_AS_VAL_UAI.crsp_val_um_closed (
101 new_references.unit_mode,
102 v_message_name) = FALSE THEN
103 Fnd_Message.Set_Name('IGS', v_message_name);
104 IGS_GE_MSG_STACK.ADD;
105 App_Exception.Raise_Exception;
106 END IF;
107 -- Validate IGS_PS_UNIT class indicator
108 -- As part of the bug# 1956374 changed to the below call from IGS_AS_VAL_UAP.crsp_val_ucl_closed
109 IF IGS_AS_VAL_UAI.crsp_val_ucl_closed (
110 new_references.unit_class,
111 v_message_name) = FALSE THEN
112 Fnd_Message.Set_Name('IGS', v_message_name);
113 IGS_GE_MSG_STACK.ADD;
114 App_Exception.Raise_Exception;
115 END IF;
116 -- If the IGS_PS_UNIT version status is inactive then prevent inserts, updates and
117 -- deletes. As deletes are logical, they are equiv to updates and delete
118 -- trigger is not required.
119 IF IGS_ps_val_unit.crsp_val_iud_uv_dtl (
120 new_references.unit_cd,
121 new_references.version_number,
122 v_message_name) = FALSE THEN
123 Fnd_Message.Set_Name('IGS', v_message_name);
124 IGS_GE_MSG_STACK.ADD;
125 App_Exception.Raise_Exception;
126 END IF;
127 -- If calendar instance is inactive, then prevent inserts, updates and
128 -- deletes. As deletes are logical, they are equiv to updates and delete
129 -- trigger is not required.
130 IF IGS_AS_VAL_UAI.crsp_val_crs_ci (
131 new_references.cal_type,
132 new_references.ci_sequence_number,
133 v_message_name) = FALSE THEN
134 Fnd_Message.Set_Name('IGS', v_message_name);
135 IGS_GE_MSG_STACK.ADD;
136 App_Exception.Raise_Exception;
137 END IF;
138 END IF;
139 IF p_inserting THEN
140 -- If calendar type is closed, then prevent inserts.
141 -- As part of the bug# 1956374 changed to the below call from IGS_AS_VAL_UAP.crsp_val_uo_cal_type
142 IF IGS_AS_VAL_UAI.crsp_val_uo_cal_type (
143 new_references.cal_type,
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 that IGS_PS_UNIT mode and IGS_PS_UNIT class cannot be set at the same time.
151 IF p_inserting OR
152 (p_updating AND
153 (NVL(new_references.unit_class,'NULL') <> NVL(old_references.unit_class,'NULL')) OR
154 (NVL(new_references.unit_mode,'NULL') <> NVL(old_references.unit_mode,'NULL')))THEN
155 IF IGS_AS_VAL_UAP.assp_val_uc_um (new_references.UNIT_MODE,
156 new_references.unit_class,
157 v_message_name) = FALSE THEN
158 Fnd_Message.Set_Name('IGS', v_message_name);
159 IGS_GE_MSG_STACK.ADD;
160 App_Exception.Raise_Exception;
161 END IF;
162 END IF;
163 -- Validate the IGS_AD_LOCATION, class and mode are not not in conflict with any of
164 -- the pattern items.
165 IF (p_updating AND
166 ((NVL(new_references.location_cd, 'NULL') <> NVL(old_references.location_cd, 'NULL')) OR
167 (NVL(new_references.unit_class,'NULL') <> NVL(old_references.unit_class,'NULL')) OR
168 (NVL(new_references.unit_mode, 'NULL') <> NVL(old_references.unit_mode,'NULL'))))THEN
169 IF IGS_AS_VAL_UAP.assp_val_uap_uoo_upd (new_references.unit_cd,
170 new_references.version_number,
171 new_references.cal_type, new_references.ci_sequence_number,
172 new_references.ass_pattern_id,
173 new_references.location_cd,
174 new_references.unit_class,
175 new_references.unit_mode,
176 v_message_name) = FALSE THEN
177 Fnd_Message.Set_Name('IGS', v_message_name);
178 IGS_GE_MSG_STACK.ADD;
179 App_Exception.Raise_Exception;
180 END IF;
181 IF IGS_AS_GEN_005.ASSP_UPD_UAP_UOO (new_references.unit_cd,
182 new_references.version_number,
183 new_references.cal_type, new_references.ci_sequence_number,
184 new_references.ass_pattern_id,
185 new_references.location_cd,
186 new_references.unit_class, new_references.unit_mode, 'Y',
187 v_message_name) = FALSE THEN
188 Fnd_Message.Set_Name('IGS', v_message_name);
189 IGS_GE_MSG_STACK.ADD;
190 App_Exception.Raise_Exception;
191 END IF;
192 END IF;
193 IF p_inserting OR p_deleting OR
194 (p_updating AND
195 ((NVL(new_references.location_cd, 'NULL') <> NVL(old_references.location_cd, 'NULL')) OR
196 (NVL(new_references.unit_class,'NULL') <> NVL(old_references.unit_class,'NULL')) OR
197 (new_references.dflt_pattern_ind <> old_references.dflt_pattern_ind) OR
198 (NVL(new_references.logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
199 NVL(old_references.logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
200 (NVL(new_references.unit_mode,'NULL') <> NVL(old_references.unit_mode,'NULL'))))THEN
201 IF NVL(new_references.action_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
202 = IGS_GE_DATE.IGSDATE('1900/01/01') THEN
203 new_references.action_dt := SYSDATE;
204 END IF;
205 END IF;
206 END BeforeRowInsertUpdate1;
207 -- Trigger description :-
208 -- "OSS_TST".trg_uap_ar_iu
209 -- AFTER INSERT OR UPDATE
210 -- ON IGS_AS_UNTAS_PATTERN
211 -- FOR EACH ROW
212 PROCEDURE AfterRowInsertUpdate2(
213 p_inserting IN BOOLEAN DEFAULT FALSE,
214 p_updating IN BOOLEAN DEFAULT FALSE,
215 p_deleting IN BOOLEAN DEFAULT FALSE
216 ) as
217 v_message_name varchar2(30);
218 BEGIN
219 IF p_inserting OR
220 ( p_updating AND
221 new_references.ass_pattern_cd <> old_references.ass_pattern_cd) THEN
222 IF IGS_AS_VAL_UAP.assp_val_uap_uniq_cd(
223 new_references.unit_cd,
224 new_references.version_number,
225 new_references.cal_type,
226 new_references.ci_sequence_number,
227 new_references.ass_pattern_id,
228 new_references.ass_pattern_cd,
229 v_message_name) = FALSE THEN
230 Fnd_Message.Set_Name('IGS', v_message_name);
231 IGS_GE_MSG_STACK.ADD;
232 App_Exception.Raise_Exception;
233 END IF;
234 -- Validate the assessment pattern code is unique within the IGS_PS_UNIT offering
235 -- pattern.
236 -- Cannot call assp_val_uap_uniq_cd because trigger will be mutating.
237 -- Save the rowid of the current row.
238 END IF;
239 END AfterRowInsertUpdate2;
240 -- Trigger description :-
241 -- "OSS_TST".trg_uap_as_iu
242 -- AFTER INSERT OR UPDATE
243 -- ON IGS_AS_UNTAS_PATTERN
244
245 PROCEDURE Check_Parent_Existance as
246 BEGIN
247 IF (((old_references.location_cd = new_references.location_cd)) OR
248 ((new_references.location_cd is NULL))) THEN
249 NULL;
250 ELSE
251 IF NOT(IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
252 new_references.location_cd ,
253 'N'
254 ))THEN
255 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
256 IGS_GE_MSG_STACK.ADD;
257 App_Exception.Raise_Exception;
258 END IF;
259 END IF;
260
261 IF (((old_references.unit_class= new_references.unit_class)) OR
262 ((new_references.unit_class IS NULL))) THEN
263 NULL;
264 ELSE
265 IF NOT(IGS_AS_UNIT_CLASS_PKG.Get_PK_For_Validation (
266 new_references.unit_class
267 ))THEN
268 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception;
271 END IF;
272 END IF;
273 IF (((old_references.unit_mode= new_references.unit_mode)) OR
274 ((new_references.unit_mode IS NULL))) THEN
275 NULL;
276 ELSE
277 IF NOT(IGS_AS_UNIT_MODE_PKG.Get_PK_For_Validation (
278 new_references.unit_mode
279 ))THEN
280 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
281 IGS_GE_MSG_STACK.ADD;
282 App_Exception.Raise_Exception;
283 END IF;
284 END IF;
285
286 IF (((old_references.unit_cd = new_references.unit_cd) AND
287 (old_references.version_number = new_references.version_number) AND
288 (old_references.cal_type= new_references.cal_type) AND
289 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
290 ((new_references.unit_cd IS NULL) OR
291 (new_references.version_number IS NULL) OR
292 (new_references.cal_type IS NULL) OR
293 (new_references.ci_sequence_number IS NULL))) THEN
294 NULL;
295 ELSE
296 IF NOT(IGS_PS_UNIT_OFR_PAT_PKG.Get_PK_For_Validation (
297 new_references.unit_cd,
298 new_references.version_number,
299 new_references.cal_type,
300 new_references.ci_sequence_number
301 ))THEN
302 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
303 IGS_GE_MSG_STACK.ADD;
304 App_Exception.Raise_Exception;
305 END IF;
306 END IF;
307
308 END Check_Parent_Existance;
309
310 PROCEDURE Check_Uniqueness AS
311 BEGIN
312 IF Get_UK_For_Validation (
313 new_references.ass_pattern_id
314 ) THEN
315 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
316 IGS_GE_MSG_STACK.ADD;
317 App_Exception.Raise_Exception;
318 END IF;
319
320 End Check_Uniqueness;
321
322 PROCEDURE Check_Constraints (
323 Column_Name IN VARCHAR2 DEFAULT NULL,
324 Column_Value IN VARCHAR2 DEFAULT NULL
325 ) as
326 BEGIN
327
328
329
330 IF column_name is null then
331 NULL;
332 ELSIF upper(Column_name) = 'ASS_PATTERN_ID' then
333 new_references.ass_pattern_id:= igs_ge_number.to_num(column_value);
334 ELSIF upper(Column_name) = 'ASS_PATTERN_CD' then
335 new_references.ass_pattern_cd:= column_value;
336 ELSIF upper(Column_name) = 'CAL_TYPE' then
337 new_references.cal_type:= column_value;
338 ELSIF upper(Column_name) = 'LOCATION_CD' then
339 new_references.location_cd:= column_value;
340 ELSIF upper(Column_name) = 'UNIT_MODE' then
341 new_references.unit_mode:= column_value;
342 ELSIF upper(Column_name) = 'UNIT_CLASS' then
343 new_references.unit_class:= column_value;
344 ELSIF upper(Column_name) = 'DFLT_PATTERN_IND' then
345 new_references.dflt_pattern_ind:= column_value;
346 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
347 new_references.ci_sequence_number:= igs_ge_number.to_num(column_value);
348
349 END IF;
350
351 IF upper(column_name) = 'ASS_PATTERN_CD' OR
352 column_name is null Then
353 IF new_references.ass_pattern_cd <> UPPER(new_references.ass_pattern_cd) Then
354 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
355 IGS_GE_MSG_STACK.ADD;
356 App_Exception.Raise_Exception;
357 END IF;
358 END IF;
359
360 IF upper(column_name) = 'ASS_PATTERN_ID' OR
361 column_name is null Then
362 IF new_references.ass_pattern_id < 1 AND new_references.ass_pattern_id > 999999 Then
363 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
364 IGS_GE_MSG_STACK.ADD;
365 App_Exception.Raise_Exception;
366 END IF;
367 END IF;
368 IF upper(column_name) = 'CAL_TYPE' OR
369 column_name is null Then
370 IF new_references.cal_type <> UPPER(new_references.cal_type) Then
371 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
372 IGS_GE_MSG_STACK.ADD;
373 App_Exception.Raise_Exception;
374 END IF;
375 END IF;
376 IF upper(column_name) = 'LOCATION_CD' OR
377 column_name is null Then
378 IF new_references.location_cd <> UPPER(new_references.location_cd) Then
379 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
380 IGS_GE_MSG_STACK.ADD;
381 App_Exception.Raise_Exception;
382 END IF;
383 END IF;
384 IF upper(column_name) = 'UNIT_MODE' OR
385 column_name is null Then
386 IF new_references.unit_mode <> UPPER(new_references.unit_mode) Then
387 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
388 IGS_GE_MSG_STACK.ADD;
389 App_Exception.Raise_Exception;
390 END IF;
391 END IF;
392 IF upper(column_name) = 'UNIT_CLASS' OR
393 column_name is null Then
394 IF new_references.unit_class <> UPPER(new_references.unit_class) Then
395 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
396 IGS_GE_MSG_STACK.ADD;
397 App_Exception.Raise_Exception;
398 END IF;
399 END IF;
400 IF upper(column_name) = 'DFLT_PATTERN_IND' OR
401 column_name is null Then
402 IF new_references.dflt_pattern_ind <> UPPER(new_references.dflt_pattern_ind) Then
403 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
404 IGS_GE_MSG_STACK.ADD;
405 App_Exception.Raise_Exception;
406 END IF;
407 END IF;
408
409
410 IF upper(column_name) = 'CI_SEQUENCE_NUMBER' OR
411 column_name is null Then
412 IF new_references.ci_sequence_number < 1 AND new_references.ci_sequence_number > 999999 Then
413 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
414 IGS_GE_MSG_STACK.ADD;
415 App_Exception.Raise_Exception;
416 END IF;
417 END IF;
418 END Check_Constraints;
419
420
421 PROCEDURE Check_Child_Existance as
422 BEGIN
423 IGS_AS_UNT_PATRN_ITM_PKG.GET_FK_IGS_AS_UNTAS_PATTERN (
424 OLD_references.unit_cd,
425 OLD_references.version_number,
426 OLD_references.cal_type,
427 OLD_references.ci_sequence_number,
428 OLD_references.ass_pattern_id
429 );
430 IGS_AS_SU_ATMPT_ITM_PKG.GET_UFK_IGS_AS_UNTAS_PATTERN (
431 OLD_references.ass_pattern_id
432 );
433 IGS_AS_SU_ATMPT_PAT_PKG.GET_UFK_IGS_AS_UNTAS_PATTERN (
434 OLD_references.ass_pattern_id
435 );
436 END Check_Child_Existance;
437 PROCEDURE Check_UK_Child_Existance as
438 BEGIN
439 IF ((old_references.ass_pattern_id = new_references.ass_pattern_id)
440 OR (old_references.ass_pattern_id IS NULL)) THEN
441 NULL;
442 ELSE
443 IGS_AS_SU_ATMPT_ITM_PKG.GET_UFK_IGS_AS_UNTAS_PATTERN(old_references.ass_pattern_id);
444 IGS_AS_SU_ATMPT_PAT_PKG.GET_UFK_IGS_AS_UNTAS_PATTERN(old_references.ass_pattern_id);
445 END IF;
446 END Check_UK_Child_Existance;
447 FUNCTION Get_PK_For_Validation (
448 x_unit_cd IN VARCHAR2,
449 x_version_number IN NUMBER,
450 x_cal_type IN VARCHAR2,
451 x_ci_sequence_number IN NUMBER,
452 x_ass_pattern_id IN NUMBER
453 ) RETURN BOOLEAN AS
454 CURSOR cur_rowid IS
455 SELECT rowid
456 FROM IGS_AS_UNTAS_PATTERN_ALL
457 WHERE unit_cd = x_unit_cd
458 AND version_number = x_version_number
459 AND cal_type= x_cal_type
460 AND ci_sequence_number = x_ci_sequence_number
461 AND ass_pattern_id = x_ass_pattern_id
462 FOR UPDATE NOWAIT;
463 lv_rowid cur_rowid%RowType;
464 BEGIN
465 Open cur_rowid;
466 Fetch cur_rowid INTO lv_rowid;
467 IF (cur_rowid%FOUND) THEN
468 Close cur_rowid;
469 Return (TRUE);
470 ELSE
471 Close cur_rowid;
472 Return (FALSE);
473 END IF;
474 END Get_PK_For_Validation;
475
476
477 FUNCTION Get_UK_For_Validation (
478 x_ass_pattern_id IN NUMBER
479 ) RETURN BOOLEAN AS
480 CURSOR cur_rowid IS
481 SELECT rowid
482 FROM IGS_AS_UNTAS_PATTERN_ALL
483 WHERE ass_pattern_id = x_ass_pattern_id
484 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
485
486 FOR UPDATE NOWAIT;
487 lv_rowid cur_rowid%RowType;
488 BEGIN
489 Open cur_rowid;
490 Fetch cur_rowid INTO lv_rowid;
491 IF (cur_rowid%FOUND) THEN
492 Close cur_rowid;
493 Return (TRUE);
494 ELSE
495 Close cur_rowid;
496 Return (FALSE);
497 END IF;
498 END Get_UK_For_Validation;
499
500 PROCEDURE GET_FK_IGS_AD_LOCATION (
501 x_location_cd IN VARCHAR2
502 ) as
503 CURSOR cur_rowid IS
504 SELECT rowid
505 FROM IGS_AS_UNTAS_PATTERN_ALL
506 WHERE location_cd = x_location_cd ;
507 lv_rowid cur_rowid%RowType;
508 BEGIN
509 Open cur_rowid;
510 Fetch cur_rowid INTO lv_rowid;
511 IF (cur_rowid%FOUND) THEN
512 Fnd_Message.Set_Name ('IGS', 'IGS_AS_UAP_LOC_FK');
513 IGS_GE_MSG_STACK.ADD;
514 Close cur_rowid;
515 App_Exception.Raise_Exception;
516 Return;
517 END IF;
518 Close cur_rowid;
519 END GET_FK_IGS_AD_LOCATION;
520 PROCEDURE GET_FK_IGS_AS_UNIT_CLASS (
521 x_unit_class IN VARCHAR2
522 ) as
523 CURSOR cur_rowid IS
524 SELECT rowid
525 FROM IGS_AS_UNTAS_PATTERN_ALL
526 WHERE unit_class= x_unit_class ;
527 lv_rowid cur_rowid%RowType;
528 BEGIN
529 Open cur_rowid;
530 Fetch cur_rowid INTO lv_rowid;
531 IF (cur_rowid%FOUND) THEN
532 Fnd_Message.Set_Name ('IGS', 'IGS_AS_UAP_UCL_FK');
533 IGS_GE_MSG_STACK.ADD;
534 Close cur_rowid;
535 App_Exception.Raise_Exception;
536 Return;
537 END IF;
538 Close cur_rowid;
539 END GET_FK_IGS_AS_UNIT_CLASS;
540 PROCEDURE GET_FK_IGS_AS_UNIT_MODE (
541 x_unit_mode IN VARCHAR2
542 ) as
543 CURSOR cur_rowid IS
544 SELECT rowid
545 FROM IGS_AS_UNTAS_PATTERN_ALL
546 WHERE unit_mode= x_unit_mode ;
547 lv_rowid cur_rowid%RowType;
548 BEGIN
549 Open cur_rowid;
550 Fetch cur_rowid INTO lv_rowid;
551 IF (cur_rowid%FOUND) THEN
552 Fnd_Message.Set_Name ('IGS', 'IGS_AS_UAP_UM_FK');
553 IGS_GE_MSG_STACK.ADD;
554 Close cur_rowid;
555 App_Exception.Raise_Exception;
556 Return;
557 END IF;
558 Close cur_rowid;
559 END GET_FK_IGS_AS_UNIT_MODE;
560 PROCEDURE GET_FK_IGS_PS_UNIT_OFR_PAT (
561 x_unit_cd IN VARCHAR2,
562 x_version_number IN NUMBER,
563 x_cal_type IN VARCHAR2,
564 x_ci_sequence_number IN NUMBER
565 ) as
566 CURSOR cur_rowid IS
567 SELECT rowid
568 FROM IGS_AS_UNTAS_PATTERN_ALL
569 WHERE unit_cd = x_unit_cd
570 AND version_number = x_version_number
571 AND cal_type= x_cal_type
572 AND ci_sequence_number = x_ci_sequence_number ;
573 lv_rowid cur_rowid%RowType;
574 BEGIN
575 Open cur_rowid;
576 Fetch cur_rowid INTO lv_rowid;
577 IF (cur_rowid%FOUND) THEN
578 Fnd_Message.Set_Name ('IGS', 'IGS_AS_UAP_UOP_FK');
579 IGS_GE_MSG_STACK.ADD;
580 Close cur_rowid;
581 App_Exception.Raise_Exception;
582 Return;
583 END IF;
584 Close cur_rowid;
585 END GET_FK_IGS_PS_UNIT_OFR_PAT;
586 PROCEDURE Before_DML (
587 p_action IN VARCHAR2,
588 x_rowid IN VARCHAR2 DEFAULT NULL ,
589 x_unit_cd IN VARCHAR2 DEFAULT NULL,
590 x_version_number IN NUMBER DEFAULT NULL,
591 x_cal_type IN VARCHAR2 DEFAULT NULL,
592 x_ci_sequence_number IN NUMBER DEFAULT NULL,
593 x_ass_pattern_id IN NUMBER DEFAULT NULL,
594 x_ass_pattern_cd IN VARCHAR2 DEFAULT NULL,
595 x_description IN VARCHAR2 DEFAULT NULL,
596 x_location_cd IN VARCHAR2 DEFAULT NULL,
597 x_unit_class IN VARCHAR2 DEFAULT NULL,
598 x_unit_mode IN VARCHAR2 DEFAULT NULL,
599 x_dflt_pattern_ind IN VARCHAR2 DEFAULT NULL,
600 x_logical_delete_dt IN DATE DEFAULT NULL,
601 x_action_dt IN DATE DEFAULT NULL,
602 x_creation_date IN DATE DEFAULT NULL ,
603 x_created_by IN NUMBER DEFAULT NULL ,
604 x_last_update_date IN DATE DEFAULT NULL ,
605 x_last_updated_by IN NUMBER DEFAULT NULL ,
606 x_last_update_login IN NUMBER DEFAULT NULL ,
607 x_org_id IN NUMBER DEFAULT NULL
608 ) as
609 BEGIN
610 Set_Column_Values (
611 p_action,
612 x_rowid,
613 x_unit_cd,
614 x_version_number,
615 x_cal_type,
616 x_ci_sequence_number,
617 x_ass_pattern_id,
618 x_ass_pattern_cd,
619 x_description,
620 x_location_cd,
621 x_unit_class,
622 x_unit_mode,
623 x_dflt_pattern_ind,
624 x_logical_delete_dt,
625 x_action_dt,
626 x_creation_date,
627 x_created_by,
628 x_last_update_date,
629 x_last_updated_by,
630 x_last_update_login,
631 x_org_id
632 );
633 IF (p_action = 'INSERT') THEN
634 -- Call all the procedures related to Before Insert.
635 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
636 IF Get_PK_For_Validation (
637 new_references.unit_cd ,
638 new_references.version_number ,
639 new_references.cal_type ,
640 new_references.ci_sequence_number,
641 new_references.ass_pattern_id
642 ) THEN
643 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
644 IGS_GE_MSG_STACK.ADD;
645 App_Exception.Raise_Exception;
646 END IF;
647
648 Check_Uniqueness;
649 Check_Constraints;
650 Check_Parent_Existance;
651 ELSIF (p_action = 'UPDATE') THEN
652 -- Call all the procedures related to Before Update.
653 BeforeRowInsertUpdate1 ( p_updating => TRUE );
654 Check_Uniqueness;
655 Check_Constraints;
656 Check_Parent_Existance;
657 Check_UK_Child_Existance;
658 ELSIF (p_action = 'DELETE') THEN
659 -- Call all the procedures related to Before Delete.
660 Null;
661 Check_Child_Existance;
662 ELSIF (p_action = 'VALIDATE_INSERT') THEN
663 IF Get_PK_For_Validation (
664 new_references.unit_cd,
665 new_references.version_number,
666 new_references.cal_type,
667 new_references.ci_sequence_number,
668 new_references.ass_pattern_id
669 ) THEN
670 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
671 IGS_GE_MSG_STACK.ADD;
672 App_Exception.Raise_Exception;
673 END IF;
674 Check_Uniqueness;
675 Check_Constraints;
676 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
677 Check_Uniqueness;
678 Check_Constraints;
679 Check_UK_Child_Existance;
680 ELSIF (p_action = 'VALIDATE_DELETE') THEN
681 Check_Child_Existance;
682 END IF;
683 END Before_DML;
684 PROCEDURE After_DML (
685 p_action IN VARCHAR2,
686 x_rowid IN VARCHAR2
687 ) as
688 BEGIN
689 l_rowid := x_rowid;
690 IF (p_action = 'INSERT') THEN
691 -- Call all the procedures related to After Insert.
692 AfterRowInsertUpdate2 ( p_inserting => TRUE );
693 ELSIF (p_action = 'UPDATE') THEN
694 -- Call all the procedures related to After Update.
695 AfterRowInsertUpdate2 ( p_updating => TRUE );
696 ELSIF (p_action = 'DELETE') THEN
697 -- Call all the procedures related to After Delete.
698 Null;
699 END IF;
700 l_rowid:=NULL;
701 END After_DML;
702 procedure INSERT_ROW (
703 X_ROWID in out NOCOPY VARCHAR2,
704 X_UNIT_CD in VARCHAR2,
705 X_VERSION_NUMBER in NUMBER,
706 X_CAL_TYPE in VARCHAR2,
707 X_CI_SEQUENCE_NUMBER in NUMBER,
708 X_ASS_PATTERN_ID in NUMBER,
709 X_ASS_PATTERN_CD in VARCHAR2,
710 X_DESCRIPTION in VARCHAR2,
711 X_LOCATION_CD in VARCHAR2,
712 X_UNIT_CLASS in VARCHAR2,
713 X_UNIT_MODE in VARCHAR2,
714 X_DFLT_PATTERN_IND in VARCHAR2,
715 X_LOGICAL_DELETE_DT in DATE,
716 X_ACTION_DT in DATE,
717 X_MODE in VARCHAR2 default 'R',
718 X_ORG_ID IN NUMBER
719 ) as
720 cursor C is select ROWID from IGS_AS_UNTAS_PATTERN_ALL
721 where UNIT_CD = X_UNIT_CD
722 and VERSION_NUMBER = X_VERSION_NUMBER
723 and CAL_TYPE = X_CAL_TYPE
724 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
725 and ASS_PATTERN_ID = X_ASS_PATTERN_ID;
726 X_LAST_UPDATE_DATE DATE;
727 X_LAST_UPDATED_BY NUMBER;
728 X_LAST_UPDATE_LOGIN NUMBER;
729 X_REQUEST_ID NUMBER;
730 X_PROGRAM_ID NUMBER;
731 X_PROGRAM_APPLICATION_ID NUMBER;
732 X_PROGRAM_UPDATE_DATE DATE;
733 begin
734 X_LAST_UPDATE_DATE := SYSDATE;
735 if(X_MODE = 'I') then
736 X_LAST_UPDATED_BY := 1;
737 X_LAST_UPDATE_LOGIN := 0;
738 elsif (X_MODE = 'R') then
739 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
740 if X_LAST_UPDATED_BY is NULL then
741 X_LAST_UPDATED_BY := -1;
742 end if;
743 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
744 if X_LAST_UPDATE_LOGIN is NULL then
745 X_LAST_UPDATE_LOGIN := -1;
746 end if;
747 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
748 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
749 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
750 if (X_REQUEST_ID = -1) then
751 X_REQUEST_ID := NULL;
752 X_PROGRAM_ID := NULL;
753 X_PROGRAM_APPLICATION_ID := NULL;
754 X_PROGRAM_UPDATE_DATE := NULL;
755 else
756 X_PROGRAM_UPDATE_DATE := SYSDATE;
757 end if;
758 else
759 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
760 IGS_GE_MSG_STACK.ADD;
761 app_exception.raise_exception;
762 end if;
763 --
764 Before_DML(
765 p_action=>'INSERT',
766 x_rowid=>X_ROWID,
767 x_action_dt=>X_ACTION_DT,
768 x_ass_pattern_cd=>X_ASS_PATTERN_CD,
769 x_ass_pattern_id=>X_ASS_PATTERN_ID,
770 x_cal_type=>X_CAL_TYPE,
771 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
772 x_description=>X_DESCRIPTION,
773 x_dflt_pattern_ind=>nvl(X_DFLT_PATTERN_IND,'N'),
774 x_location_cd=>X_LOCATION_CD,
775 x_logical_delete_dt=>X_LOGICAL_DELETE_DT,
776 x_unit_cd=>X_UNIT_CD,
777 x_unit_class=>X_UNIT_CLASS,
778 x_unit_mode=>X_UNIT_MODE,
779 x_version_number=>X_VERSION_NUMBER,
780 x_creation_date=>X_LAST_UPDATE_DATE,
781 x_created_by=>X_LAST_UPDATED_BY,
782 x_last_update_date=>X_LAST_UPDATE_DATE,
783 x_last_updated_by=>X_LAST_UPDATED_BY,
784 x_last_update_login=>X_LAST_UPDATE_LOGIN,
785 x_org_id => igs_ge_gen_003.get_org_id
786 );
787 --
788 insert into IGS_AS_UNTAS_PATTERN_ALL (
789 UNIT_CD,
790 VERSION_NUMBER,
791 CAL_TYPE,
792 CI_SEQUENCE_NUMBER,
793 ASS_PATTERN_ID,
794 ASS_PATTERN_CD,
795 DESCRIPTION,
796 LOCATION_CD,
797 UNIT_CLASS,
798 UNIT_MODE,
799 DFLT_PATTERN_IND,
800 LOGICAL_DELETE_DT,
801 ACTION_DT,
802 ORG_ID,
803 CREATION_DATE,
804 CREATED_BY,
805 LAST_UPDATE_DATE,
806 LAST_UPDATED_BY,
807 LAST_UPDATE_LOGIN,
808 REQUEST_ID,
809 PROGRAM_ID,
810 PROGRAM_APPLICATION_ID,
811 PROGRAM_UPDATE_DATE
812 ) values (
813 NEW_REFERENCES.UNIT_CD,
814 NEW_REFERENCES.VERSION_NUMBER,
815 NEW_REFERENCES.CAL_TYPE,
816 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
817 NEW_REFERENCES.ASS_PATTERN_ID,
818 NEW_REFERENCES.ASS_PATTERN_CD,
819 NEW_REFERENCES.DESCRIPTION,
820 NEW_REFERENCES.LOCATION_CD,
821 NEW_REFERENCES.UNIT_CLASS,
822 NEW_REFERENCES.UNIT_MODE,
823 NEW_REFERENCES.DFLT_PATTERN_IND,
824 NEW_REFERENCES.LOGICAL_DELETE_DT,
825 NEW_REFERENCES.ACTION_DT,
826 NEW_REFERENCES.ORG_ID,
827 X_LAST_UPDATE_DATE,
828 X_LAST_UPDATED_BY,
829 X_LAST_UPDATE_DATE,
830 X_LAST_UPDATED_BY,
831 X_LAST_UPDATE_LOGIN,
832 X_REQUEST_ID,
833 X_PROGRAM_ID,
834 X_PROGRAM_APPLICATION_ID,
835 X_PROGRAM_UPDATE_DATE
836 );
837
838 open c;
839 fetch c into X_ROWID;
840 if (c%notfound) then
841 close c;
842 raise no_data_found;
843 end if;
844 close c;
845 After_DML(
846 p_action => 'INSERT',
847 x_rowid => X_ROWID
848 );
849
850 end INSERT_ROW;
851 procedure LOCK_ROW (
852 X_ROWID in VARCHAR2,
853 X_UNIT_CD in VARCHAR2,
854 X_VERSION_NUMBER in NUMBER,
855 X_CAL_TYPE in VARCHAR2,
856 X_CI_SEQUENCE_NUMBER in NUMBER,
857 X_ASS_PATTERN_ID in NUMBER,
858 X_ASS_PATTERN_CD in VARCHAR2,
859 X_DESCRIPTION in VARCHAR2,
860 X_LOCATION_CD in VARCHAR2,
861 X_UNIT_CLASS in VARCHAR2,
862 X_UNIT_MODE in VARCHAR2,
863 X_DFLT_PATTERN_IND in VARCHAR2,
864 X_LOGICAL_DELETE_DT in DATE,
865 X_ACTION_DT in DATE
866 ) as
867 cursor c1 is select
868 ASS_PATTERN_CD,
869 DESCRIPTION,
870 LOCATION_CD,
871 UNIT_CLASS,
872 UNIT_MODE,
873 DFLT_PATTERN_IND,
874 LOGICAL_DELETE_DT,
875 ACTION_DT
876 from IGS_AS_UNTAS_PATTERN_ALL
877 where ROWID = X_ROWID for update nowait;
878 tlinfo c1%rowtype;
879 begin
880 open c1;
881 fetch c1 into tlinfo;
882 if (c1%notfound) then
883 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
884 IGS_GE_MSG_STACK.ADD;
885 close c1;
886 app_exception.raise_exception;
887 return;
888 end if;
889 close c1;
890 if ( (tlinfo.ASS_PATTERN_CD = X_ASS_PATTERN_CD)
891 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
892 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
893 OR ((tlinfo.LOCATION_CD is null)
894 AND (X_LOCATION_CD is null)))
895 AND ((tlinfo.UNIT_CLASS = X_UNIT_CLASS)
896 OR ((tlinfo.UNIT_CLASS is null)
897 AND (X_UNIT_CLASS is null)))
898 AND ((tlinfo.UNIT_MODE = X_UNIT_MODE)
899 OR ((tlinfo.UNIT_MODE is null)
900 AND (X_UNIT_MODE is null)))
901 AND (tlinfo.DFLT_PATTERN_IND = X_DFLT_PATTERN_IND)
902 AND ((trunc(tlinfo.LOGICAL_DELETE_DT) = trunc(X_LOGICAL_DELETE_DT))
903 OR ((tlinfo.LOGICAL_DELETE_DT is null)
904 AND (X_LOGICAL_DELETE_DT is null)))
905 AND ((trunc(tlinfo.ACTION_DT) = trunc(X_ACTION_DT))
906 OR ((tlinfo.ACTION_DT is null)
907 AND (X_ACTION_DT is null)))
908 ) then
909 null;
910 else
911 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
912 IGS_GE_MSG_STACK.ADD;
913 app_exception.raise_exception;
914 end if;
915 return;
916 end LOCK_ROW;
917 procedure UPDATE_ROW (
918 X_ROWID in VARCHAR2,
919 X_UNIT_CD in VARCHAR2,
920 X_VERSION_NUMBER in NUMBER,
921 X_CAL_TYPE in VARCHAR2,
922 X_CI_SEQUENCE_NUMBER in NUMBER,
923 X_ASS_PATTERN_ID in NUMBER,
924 X_ASS_PATTERN_CD in VARCHAR2,
925 X_DESCRIPTION in VARCHAR2,
926 X_LOCATION_CD in VARCHAR2,
927 X_UNIT_CLASS in VARCHAR2,
928 X_UNIT_MODE in VARCHAR2,
929 X_DFLT_PATTERN_IND in VARCHAR2,
930 X_LOGICAL_DELETE_DT in DATE,
931 X_ACTION_DT in DATE,
932 X_MODE in VARCHAR2 default 'R'
933 ) as
934 X_LAST_UPDATE_DATE DATE;
935 X_LAST_UPDATED_BY NUMBER;
936 X_LAST_UPDATE_LOGIN NUMBER;
937 X_REQUEST_ID NUMBER;
938 X_PROGRAM_ID NUMBER;
939 X_PROGRAM_APPLICATION_ID NUMBER;
940 X_PROGRAM_UPDATE_DATE DATE;
941 begin
942 X_LAST_UPDATE_DATE := SYSDATE;
943 if(X_MODE = 'I') then
944 X_LAST_UPDATED_BY := 1;
945 X_LAST_UPDATE_LOGIN := 0;
946 elsif (X_MODE = 'R') then
947 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
948 if X_LAST_UPDATED_BY is NULL then
949 X_LAST_UPDATED_BY := -1;
950 end if;
951 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
952 if X_LAST_UPDATE_LOGIN is NULL then
953 X_LAST_UPDATE_LOGIN := -1;
954 end if;
955 else
956 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
957 IGS_GE_MSG_STACK.ADD;
958 app_exception.raise_exception;
959 end if;
960 Before_DML(
961 p_action=>'UPDATE',
962 x_rowid=>X_ROWID,
963 x_action_dt=>X_ACTION_DT,
964 x_ass_pattern_cd=>X_ASS_PATTERN_CD,
965 x_ass_pattern_id=>X_ASS_PATTERN_ID,
966 x_cal_type=>X_CAL_TYPE,
967 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
968 x_description=>X_DESCRIPTION,
969 x_dflt_pattern_ind=>X_DFLT_PATTERN_IND,
970 x_location_cd=>X_LOCATION_CD,
971 x_logical_delete_dt=>X_LOGICAL_DELETE_DT,
972 x_unit_cd=>X_UNIT_CD,
973 x_unit_class=>X_UNIT_CLASS,
974 x_unit_mode=>X_UNIT_MODE,
975 x_version_number=>X_VERSION_NUMBER,
976 x_creation_date=>X_LAST_UPDATE_DATE,
977 x_created_by=>X_LAST_UPDATED_BY,
978 x_last_update_date=>X_LAST_UPDATE_DATE,
979 x_last_updated_by=>X_LAST_UPDATED_BY,
980 x_last_update_login=>X_LAST_UPDATE_LOGIN
981 );
982 if (X_MODE = 'R') then
983 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
984 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
985 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
986 if (X_REQUEST_ID = -1) then
987 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
988 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
989 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
990 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
991 else
992 X_PROGRAM_UPDATE_DATE := SYSDATE;
993 end if;
994 --
995 --
996 end if;
997 update IGS_AS_UNTAS_PATTERN_ALL set
998 ASS_PATTERN_CD = NEW_REFERENCES.ASS_PATTERN_CD,
999 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
1000 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
1001 UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
1002 UNIT_MODE = NEW_REFERENCES.UNIT_MODE,
1003 DFLT_PATTERN_IND = NEW_REFERENCES.DFLT_PATTERN_IND,
1004 LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
1005 ACTION_DT = NEW_REFERENCES.ACTION_DT,
1006 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1007 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1008 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1009 REQUEST_ID = X_REQUEST_ID,
1010 PROGRAM_ID = X_PROGRAM_ID,
1011 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1012 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1013 where ROWID = X_ROWID;
1014 if (sql%notfound) then
1015 raise no_data_found;
1016 end if;
1017 --
1018 After_DML(
1019 p_action => 'UPDATE',
1020 x_rowid => X_ROWID
1021 );
1022 --
1023 end UPDATE_ROW;
1024 procedure ADD_ROW (
1025 X_ROWID in out NOCOPY VARCHAR2,
1026 X_UNIT_CD in VARCHAR2,
1027 X_VERSION_NUMBER in NUMBER,
1028 X_CAL_TYPE in VARCHAR2,
1029 X_CI_SEQUENCE_NUMBER in NUMBER,
1030 X_ASS_PATTERN_ID in NUMBER,
1031 X_ASS_PATTERN_CD in VARCHAR2,
1032 X_DESCRIPTION in VARCHAR2,
1033 X_LOCATION_CD in VARCHAR2,
1034 X_UNIT_CLASS in VARCHAR2,
1035 X_UNIT_MODE in VARCHAR2,
1036 X_DFLT_PATTERN_IND in VARCHAR2,
1037 X_LOGICAL_DELETE_DT in DATE,
1038 X_ACTION_DT in DATE,
1039 X_MODE in VARCHAR2 default 'R',
1040 X_ORG_ID IN NUMBER
1041 ) as
1042 cursor c1 is select rowid from IGS_AS_UNTAS_PATTERN_ALL
1043 where UNIT_CD = X_UNIT_CD
1044 and VERSION_NUMBER = X_VERSION_NUMBER
1045 and CAL_TYPE = X_CAL_TYPE
1046 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
1047 and ASS_PATTERN_ID = X_ASS_PATTERN_ID
1048 ;
1049 begin
1050 open c1;
1051 fetch c1 into X_ROWID;
1052 if (c1%notfound) then
1053 close c1;
1054 INSERT_ROW (
1055 X_ROWID,
1056 X_UNIT_CD,
1057 X_VERSION_NUMBER,
1058 X_CAL_TYPE,
1059 X_CI_SEQUENCE_NUMBER,
1060 X_ASS_PATTERN_ID,
1061 X_ASS_PATTERN_CD,
1062 X_DESCRIPTION,
1063 X_LOCATION_CD,
1064 X_UNIT_CLASS,
1065 X_UNIT_MODE,
1066 X_DFLT_PATTERN_IND,
1067 X_LOGICAL_DELETE_DT,
1068 X_ACTION_DT,
1069 X_MODE,
1070 X_ORG_ID);
1071 return;
1072 end if;
1073 close c1;
1074 UPDATE_ROW (
1075 X_ROWID,
1076 X_UNIT_CD,
1077 X_VERSION_NUMBER,
1078 X_CAL_TYPE,
1079 X_CI_SEQUENCE_NUMBER,
1080 X_ASS_PATTERN_ID,
1081 X_ASS_PATTERN_CD,
1082 X_DESCRIPTION,
1083 X_LOCATION_CD,
1084 X_UNIT_CLASS,
1085 X_UNIT_MODE,
1086 X_DFLT_PATTERN_IND,
1087 X_LOGICAL_DELETE_DT,
1088 X_ACTION_DT,
1089 X_MODE);
1090 end ADD_ROW;
1091 procedure DELETE_ROW (
1092 X_ROWID in VARCHAR2) as
1093 begin
1094 --
1095 Before_DML(
1096 p_action => 'DELETE',
1097 x_rowid => X_ROWID
1098 );
1099 --
1100 delete from IGS_AS_UNTAS_PATTERN_ALL
1101 where ROWID = X_ROWID;
1102 if (sql%notfound) then
1103 raise no_data_found;
1104 end if;
1105 --
1106 After_DML(
1107 p_action => 'DELETE',
1108 x_rowid => X_ROWID
1109 );
1110 --
1111 end DELETE_ROW;
1112 end IGS_AS_UNTAS_PATTERN_PKG;