[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_UNIT_SET_PKG
Source
1 package body IGS_EN_UNIT_SET_PKG as
2 /* $Header: IGSEI01B.pls 120.1 2006/02/16 04:04:49 smaddali noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_EN_UNIT_SET_ALL%RowType;
6 new_references IGS_EN_UNIT_SET_ALL%RowType;
7
8 PROCEDURE beforerowdelete AS
9 ------------------------------------------------------------------
10 --Created by : smvk, Oracle India
11 --Date created: 03-Jan-2003
12 --
13 --Purpose: Only planned unit set status are allowed for deletion
14 --
15 --
16 --Known limitations/enhancements and/or remarks:
17 --
18 --Change History:
19 --Who When What
20 -------------------------------------------------------------------
21 CURSOR cur_delete (cp_c_unit_set_cd igs_en_unit_set_all.unit_set_cd%TYPE,
22 cp_n_version_number igs_en_unit_set_all.version_number%TYPE)
23 IS
24 SELECT 'x'
25 FROM igs_en_unit_set_all usv,
26 igs_en_unit_set_stat uss
27 WHERE usv.unit_set_status = uss.unit_set_status
28 AND uss.s_unit_set_status = 'PLANNED'
29 AND usv.unit_set_cd = cp_c_unit_set_cd
30 AND usv.version_number = cp_n_version_number;
31
32 l_check VARCHAR2(1);
33
34 BEGIN
35 -- Only planned unit status are allowed for deletion
36 OPEN cur_delete (old_references.unit_set_cd,old_references.version_number);
37 FETCH cur_delete INTO l_check;
38 IF cur_delete%NOTFOUND THEN
39 CLOSE cur_delete;
40 fnd_message.set_name('IGS','IGS_PS_UNIT_SET_NO_DEL_ALLOWED');
41 igs_ge_msg_stack.add;
42 app_exception.raise_exception;
43 END IF;
44 CLOSE cur_delete;
45 END beforerowdelete;
46
47 PROCEDURE beforerowupdate AS
48 ------------------------------------------------------------------
49 --Created by : smvk, Oracle India
50 --Date created: 03-Jan-2003
51 --
52 --Purpose: Active/Inactive Unit Set Status can not be changed to Planned Status
53 --
54 --
55 --Known limitations/enhancements and/or remarks:
56 --
57 --Change History:
58 --Who When What
59 -------------------------------------------------------------------
60 CURSOR cur_get_status (cp_c_unit_set_status igs_en_unit_set_stat.unit_set_status%TYPE)
61 IS
62 SELECT s_unit_set_status
63 FROM igs_en_unit_set_stat
64 WHERE unit_set_status = cp_c_unit_set_status;
65
66 l_c_sys_status igs_en_unit_set_stat.s_unit_set_status%TYPE;
67
68 CURSOR cur_check_update (cp_c_unit_set_cd igs_en_unit_set_all.unit_set_cd%TYPE,
69 cp_n_version_number igs_en_unit_set_all.version_number%TYPE)
70 IS
71 SELECT 'x'
72 FROM igs_en_unit_set_all usv,
73 igs_en_unit_set_stat uss
74 WHERE usv.unit_set_status=uss.unit_set_status
75 AND uss.s_unit_set_status <> 'PLANNED'
76 AND usv.unit_set_cd = cp_c_unit_set_cd
77 AND usv.version_number = cp_n_version_number;
78
79 l_check VARCHAR2(1);
80 BEGIN
81 -- Active/Inactive unit Status can not be changed to Planned Status
82 OPEN cur_get_status(new_references.unit_set_status);
83 FETCH cur_get_status INTO l_c_sys_status;
84 IF cur_get_status%FOUND THEN
85 CLOSE cur_get_status;
86 IF (l_c_sys_status = 'PLANNED') THEN
87 OPEN cur_check_update(old_references.unit_set_cd,old_references.version_number);
88 FETCH cur_check_update INTO l_check;
89 IF cur_check_update%FOUND THEN
90 CLOSE cur_check_update;
91 fnd_message.set_name('IGS','IGS_PS_UNIT_SET_STATUS_NOTALT');
92 igs_ge_msg_stack.add;
93 app_exception.raise_exception;
94 END IF;
95 CLOSE cur_check_update;
96 END IF;
97 ELSE
98 -- If the unit set status is not found then the record might have been deleted
99 CLOSE cur_get_status;
100 fnd_message.set_name('FND','FORM_RECORD_DELETED');
101 igs_ge_msg_stack.add;
102 app_exception.raise_exception;
103 END IF;
104 END beforerowupdate;
105
106 PROCEDURE Set_Column_Values (
107 p_action IN VARCHAR2,
108 x_rowid IN VARCHAR2 DEFAULT NULL,
109 x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
110 x_version_number IN NUMBER DEFAULT NULL,
111 x_unit_set_status IN VARCHAR2 DEFAULT NULL,
112 x_unit_set_cat IN VARCHAR2 DEFAULT NULL,
113 x_start_dt IN DATE DEFAULT NULL,
114 x_review_dt IN DATE DEFAULT NULL,
115 x_expiry_dt IN DATE DEFAULT NULL,
116 x_end_dt IN DATE DEFAULT NULL,
117 x_title IN VARCHAR2 DEFAULT NULL,
118 x_short_title IN VARCHAR2 DEFAULT NULL,
119 x_abbreviation IN VARCHAR2 DEFAULT NULL,
120 x_responsible_org_unit_cd IN VARCHAR2 DEFAULT NULL,
121 x_responsible_ou_start_dt IN DATE DEFAULT NULL,
122 x_administrative_ind IN VARCHAR2 DEFAULT NULL,
123 x_authorisation_rqrd_ind IN VARCHAR2 DEFAULT NULL,
124 x_attribute_category in VARCHAR2 DEFAULT NULL,
125 x_attribute1 in VARCHAR2 DEFAULT NULL,
126 x_attribute2 in VARCHAR2 DEFAULT NULL,
127 x_attribute3 in VARCHAR2 DEFAULT NULL,
128 x_attribute4 in VARCHAR2 DEFAULT NULL,
129 x_attribute5 in VARCHAR2 DEFAULT NULL,
130 x_attribute6 in VARCHAR2 DEFAULT NULL,
131 x_attribute7 in VARCHAR2 DEFAULT NULL,
132 x_attribute8 in VARCHAR2 DEFAULT NULL,
133 x_attribute9 in VARCHAR2 DEFAULT NULL,
134 x_attribute10 in VARCHAR2 DEFAULT NULL,
135 x_attribute11 in VARCHAR2 DEFAULT NULL,
136 x_attribute12 in VARCHAR2 DEFAULT NULL,
137 x_attribute13 in VARCHAR2 DEFAULT NULL,
138 x_attribute14 in VARCHAR2 DEFAULT NULL,
139 x_attribute15 in VARCHAR2 DEFAULT NULL,
140 x_attribute16 in VARCHAR2 DEFAULT NULL,
141 x_attribute17 in VARCHAR2 DEFAULT NULL,
142 x_attribute18 in VARCHAR2 DEFAULT NULL,
143 x_attribute19 in VARCHAR2 DEFAULT NULL,
144 x_attribute20 in VARCHAR2 DEFAULT NULL,
145 x_creation_date IN DATE DEFAULT NULL,
146 x_created_by IN NUMBER DEFAULT NULL,
147 x_last_update_date IN DATE DEFAULT NULL,
148 x_last_updated_by IN NUMBER DEFAULT NULL,
149 x_last_update_login IN NUMBER DEFAULT NULL,
150 x_org_id IN NUMBER DEFAULT NULL
151 ) as
152
153 CURSOR cur_old_ref_values IS
154 SELECT *
155 FROM IGS_EN_UNIT_SET_ALL
156 WHERE rowid = x_rowid;
157
158 BEGIN
159
160 l_rowid := x_rowid;
161
162 -- Code for setting the Old and New Reference Values.
163 -- Populate Old Values.
164 Open cur_old_ref_values;
165 Fetch cur_old_ref_values INTO old_references;
166 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
167 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
168 IGS_GE_MSG_STACK.ADD;
169 Close cur_old_ref_values;
170 App_Exception.Raise_Exception;
171 Return;
172 END IF;
173 Close cur_old_ref_values;
174
175 -- Populate New Values.
176 new_references.unit_set_cd := x_unit_set_cd;
177 new_references.version_number := x_version_number;
178 new_references.unit_set_status := x_unit_set_status;
179 new_references.unit_set_cat := x_unit_set_cat;
180 new_references.start_dt := x_start_dt;
181 new_references.review_dt := x_review_dt;
182 new_references.expiry_dt := x_expiry_dt;
183 new_references.end_dt := x_end_dt;
184 new_references.title := x_title;
185 new_references.short_title := x_short_title;
186 new_references.abbreviation := x_abbreviation;
187 new_references.responsible_org_unit_cd := x_responsible_org_unit_cd;
188 new_references.responsible_ou_start_dt := x_responsible_ou_start_dt;
189 new_references.administrative_ind := x_administrative_ind;
190 new_references.authorisation_rqrd_ind := x_authorisation_rqrd_ind;
191 new_references.attribute_category := x_attribute_category;
192 new_references.attribute1 := x_attribute1;
193 new_references.attribute2 := x_attribute2;
194 new_references.attribute3 := x_attribute3;
195 new_references.attribute4 := x_attribute4;
196 new_references.attribute5 := x_attribute5;
197 new_references.attribute6 := x_attribute6;
198 new_references.attribute7 := x_attribute7;
199 new_references.attribute8 := x_attribute8;
200 new_references.attribute9 := x_attribute9;
201 new_references.attribute10 := x_attribute10;
202 new_references.attribute11 := x_attribute11;
203 new_references.attribute12 := x_attribute12;
204 new_references.attribute13 := x_attribute13;
205 new_references.attribute14 := x_attribute14;
206 new_references.attribute15 := x_attribute15;
207 new_references.attribute16 := x_attribute16;
208 new_references.attribute17 := x_attribute17;
209 new_references.attribute18 := x_attribute18;
210 new_references.attribute19 := x_attribute19;
211 new_references.attribute20 := x_attribute20;
212 IF (p_action = 'UPDATE') THEN
213 new_references.creation_date := old_references.creation_date;
214 new_references.created_by := old_references.created_by;
215 ELSE
216 new_references.creation_date := x_creation_date;
217 new_references.created_by := x_created_by;
218 END IF;
219 new_references.last_update_date := x_last_update_date;
220 new_references.last_updated_by := x_last_updated_by;
221 new_references.last_update_login := x_last_update_login;
222 new_references.org_id := x_org_id;
223 END Set_Column_Values;
224
225
226 -- Trigger description :-
227 -- "OSS_TST".trg_us_br_iu
228 -- BEFORE INSERT OR UPDATE
229 -- ON IGS_EN_UNIT_SET_ALL
230 -- FOR EACH ROW
231
232 PROCEDURE BeforeRowInsertUpdate1(
233 p_inserting IN BOOLEAN DEFAULT FALSE,
234 p_updating IN BOOLEAN DEFAULT FALSE,
235 p_deleting IN BOOLEAN DEFAULT FALSE
236 ) as
237
238 v_message_name varchar2(30);
239
240 BEGIN
241 -- Validate that inserts/updates are allowed
242 IF p_inserting THEN
243 -- <us1>
244 -- Org UNIT inactive validation
245 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_US.crsp_val_ou_sys_sts
246 IF IGS_PS_VAL_CRV.crsp_val_ou_sys_sts (
247 new_references.responsible_org_unit_cd,
248 new_references.responsible_ou_start_dt,
249 v_message_name) = FALSE THEN
250 Fnd_Message.Set_Name('IGS', v_message_name);
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 END IF;
254 -- <us2>
255 -- UNIT set status closed validation
256 IF IGS_PS_VAL_US.crsp_val_uss_closed (
257 new_references.unit_set_status,
258 v_message_name) = FALSE THEN
259 Fnd_Message.Set_Name('IGS', v_message_name);
260 IGS_GE_MSG_STACK.ADD;
261 App_Exception.Raise_Exception;
262 END IF;
263 -- <us2a>
264 -- UNIT set category closed validation
265 IF IGS_PS_VAL_US.crsp_val_usc_closed (
266 new_references.UNIT_SET_CAT,
267 v_message_name) = FALSE THEN
268 Fnd_Message.Set_Name('IGS', v_message_name);
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception;
271 END IF;
272 END IF;
273 -- Validate that inserts/updates are allowed
274 IF p_inserting OR p_updating THEN
275 -- <us3>, <us4>, <us5>
276 -- Start/Expiry/End date validations
277 IF IGS_PS_VAL_US.crsp_val_ver_dt (
278 new_references.start_dt,
279 new_references.end_dt,
280 new_references.expiry_dt,
281 v_message_name,FALSE) = FALSE THEN
282 Fnd_Message.Set_Name('IGS', v_message_name);
283 IGS_GE_MSG_STACK.ADD;
284 App_Exception.Raise_Exception;
285 END IF;
286 -- <us7>
287 -- End date/UNIT set status cross-field validation
288 IF IGS_PS_VAL_US.crsp_val_us_end_sts (
289 new_references.end_dt,
290 new_references.unit_set_status,
291 v_message_name) = FALSE THEN
292 Fnd_Message.Set_Name('IGS', v_message_name);
293 IGS_GE_MSG_STACK.ADD;
294 App_Exception.Raise_Exception;
295 END IF;
296 -- <us8>
297 -- Validate end date/UNIT set status when there are active students
298 IF new_references.end_dt IS NOT NULL or
299 IGS_PS_GEN_006.CRSP_GET_US_SYS_STS (new_references.unit_set_status) = 'INACTIVE' THEN
300 IF IGS_PS_VAL_US.crsp_val_us_enr (
301 new_references.unit_set_cd,
302 new_references.version_number,
303 v_message_name) = FALSE THEN
304 Fnd_Message.Set_Name('IGS', v_message_name);
305 IGS_GE_MSG_STACK.ADD;
306 App_Exception.Raise_Exception;
307 END IF;
308 END IF;
309 -- <us9>
310 -- Validate UNIT set status changes
311 IF IGS_PS_VAL_US.crsp_val_us_status (
312 old_references.unit_set_status,
313 new_references.unit_set_status,
314 v_message_name) = FALSE THEN
315 Fnd_Message.Set_Name('IGS', v_message_name);
316 IGS_GE_MSG_STACK.ADD;
317 App_Exception.Raise_Exception;
318 END IF;
319 -- <us11a>
320 -- Validate details can not be altered when INACTIVE unless
321 -- changing back to ACTIVE
322 --smaddali changed the parameters passed to this call for bug#2182746
323 -- as the parameters were passed wrong
324 IF IGS_PS_VAL_COUSR.crsp_val_iud_us_dtl (
325 new_references.unit_set_cd,
326 new_references.version_number,
327 v_message_name) = FALSE THEN
328 Fnd_Message.Set_Name('IGS', v_message_name);
329 IGS_GE_MSG_STACK.ADD;
330 App_Exception.Raise_Exception;
331 END IF;
332 END IF;
333
334
335 END BeforeRowInsertUpdate1;
336
337 -- Trigger description :-
338 -- "OSS_TST".trg_us_ar_iu
339 -- AFTER INSERT OR UPDATE
340 -- ON IGS_EN_UNIT_SET_ALL
341 -- FOR EACH ROW
342
343 PROCEDURE AfterRowInsertUpdate2(
344 p_inserting IN BOOLEAN DEFAULT FALSE,
345 p_updating IN BOOLEAN DEFAULT FALSE,
346 p_deleting IN BOOLEAN DEFAULT FALSE
347 ) as
348 v_message_name varchar2(30);
349 BEGIN
350 IF IGS_PS_VAL_US.crsp_val_us_exp_sts (
351 New_References.unit_set_cd,
352 New_References.version_number,
353 New_References.unit_set_status,
354 New_References.expiry_dt,
355 v_message_name) = FALSE THEN
356 Fnd_Message.Set_Name('IGS', v_message_name);
357 IGS_GE_MSG_STACK.ADD;
358 App_Exception.Raise_Exception;
359 END IF;
360
361 END AfterRowInsertUpdate2;
362
363 -- Trigger description :-
364 -- "OSS_TST".trg_us_ar_u_hist
365 -- AFTER UPDATE
366 -- ON IGS_EN_UNIT_SET_ALL
367 -- FOR EACH ROW
368
369 PROCEDURE AfterRowUpdate3(
370 p_inserting IN BOOLEAN DEFAULT FALSE,
371 p_updating IN BOOLEAN DEFAULT FALSE,
372 p_deleting IN BOOLEAN DEFAULT FALSE
373 ) as
374 v_message_name varchar2(30);
375 BEGIN
376 IF p_updating THEN
377 -- Create IGS_EN_UNIT_SET_ALL history record.
378 IGS_PS_GEN_006.CRSP_INS_US_HIST (
379 new_references.unit_set_cd,
380 new_references.version_number,
381 new_references.unit_set_status,
382 old_references.unit_set_status,
383 new_references.unit_set_cat,
384 old_references.unit_set_cat,
385 new_references.start_dt,
386 old_references.start_dt,
387 new_references.review_dt,
388 old_references.review_dt,
389 new_references.expiry_dt,
390 old_references.expiry_dt,
391 new_references.end_dt,
392 old_references.end_dt,
393 new_references.title,
394 old_references.title,
395 new_references.short_title,
396 old_references.short_title,
397 new_references.abbreviation,
398 old_references.abbreviation,
399 new_references.responsible_org_unit_cd,
400 old_references.responsible_org_unit_cd,
401 new_references.responsible_ou_start_dt,
402 old_references.responsible_ou_start_dt,
403 new_references.administrative_ind,
404 old_references.administrative_ind,
405 new_references.authorisation_rqrd_ind,
406 old_references.authorisation_rqrd_ind,
407 new_references.last_updated_by,
408 old_references.last_updated_by,
409 new_references.last_update_date,
410 old_references.last_update_date);
411 END IF;
412
413
414 END AfterRowUpdate3;
415
416
417 PROCEDURE Check_Constraints (
418 Column_Name IN VARCHAR2 DEFAULT NULL,
419 Column_Value IN VARCHAR2 DEFAULT NULL
420 ) as
421
422 BEGIN
423
424 -- The following code checks for check constraints on the Columns.
425
426 IF column_name is NULL THEN
427 NULL;
428 ELSIF UPPER(column_name) = 'ADMINISTRATIVE_IND' THEN
429 new_references.administrative_ind := column_value;
430 ELSIF UPPER(column_name) = 'VERSION_NUMBER' THEN
431 new_references.version_number := IGS_GE_NUMBER.TO_NUM(column_value);
432 ELSIF UPPER(column_name) = 'AUTHORISATION_RQRD_IND' THEN
433 new_references.authorisation_rqrd_ind := column_value;
434 ELSIF UPPER(column_name) = 'ABBREVIATION' THEN
435 new_references.abbreviation := column_value;
436 ELSIF UPPER(column_name) = 'UNIT_SET_CAT' THEN
437 new_references.UNIT_SET_CAT := column_value;
438 ELSIF UPPER(column_name) = 'UNIT_SET_CD' THEN
439 new_references.unit_set_cd := column_value;
440 ELSIF UPPER(column_name) = 'UNIT_SET_STATUS' THEN
441 new_references.UNIT_SET_STATUS := column_value;
442 END IF;
443
444
445
446 IF ((UPPER (column_name) = 'ADMINISTRATIVE_IND') OR (column_name IS NULL)) THEN
447 IF new_references.administrative_ind NOT IN ('Y' , 'N' ) THEN
448 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
449 IGS_GE_MSG_STACK.ADD;
450 App_Exception.Raise_Exception;
451 END IF;
452 END IF;
453
454
455 IF ((UPPER (column_name) = 'VERSION_NUMBER') OR (column_name IS NULL)) THEN
456 IF new_references.version_number < 1 OR
457 new_references.version_number > 999 THEN
458 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
459 IGS_GE_MSG_STACK.ADD;
460 App_Exception.Raise_Exception;
461 END IF;
462 END IF;
463
464
465 IF ((UPPER (column_name) = 'AUTHORISATION_RQRD_IND') OR (column_name IS NULL)) THEN
466 IF (new_references.authorisation_rqrd_ind <> UPPER (new_references.authorisation_rqrd_ind)) THEN
467 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
468 IGS_GE_MSG_STACK.ADD;
469 App_Exception.Raise_Exception;
470 END IF;
471 END IF;
472
473
474 IF ((UPPER (column_name) = 'ABBREVIATION') OR (column_name IS NULL)) THEN
475 IF (new_references.abbreviation <> UPPER (new_references.abbreviation)) THEN
476 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
477 IGS_GE_MSG_STACK.ADD;
478 App_Exception.Raise_Exception;
479 END IF;
480 END IF;
481
482 IF ((UPPER (column_name) = 'UNIT_SET_CAT') OR (column_name IS NULL)) THEN
483 IF (new_references.UNIT_SET_CAT <> UPPER (new_references.UNIT_SET_CAT)) THEN
484 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
485 IGS_GE_MSG_STACK.ADD;
486 App_Exception.Raise_Exception;
487 END IF;
488 END IF;
489
490
491 IF ((UPPER (column_name) = 'UNIT_SET_CD') OR (column_name IS NULL)) THEN
492 IF (new_references.unit_set_cd <> UPPER (new_references.unit_set_cd)) THEN
493 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
494 IGS_GE_MSG_STACK.ADD;
495 App_Exception.Raise_Exception;
496 END IF;
497 END IF;
498
499 IF ((UPPER (column_name) = 'UNIT_SET_STATUS') OR (column_name IS NULL)) THEN
500 IF (new_references.UNIT_SET_STATUS <> UPPER (new_references.UNIT_SET_STATUS)) THEN
501 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
502 IGS_GE_MSG_STACK.ADD;
503 App_Exception.Raise_Exception;
504 END IF;
505 END IF;
506
507
508 END Check_Constraints;
509
510 PROCEDURE Check_Parent_Existance as
511 BEGIN
512
513 IF (((old_references.responsible_org_unit_cd = new_references.responsible_org_unit_cd) AND
514 (old_references.responsible_ou_start_dt = new_references.responsible_ou_start_dt)) OR
515 ((new_references.responsible_org_unit_cd IS NULL) OR
516 (new_references.responsible_ou_start_dt IS NULL))) THEN
517 NULL;
518 ELSE
519
520 IF NOT IGS_OR_UNIT_PKG.Get_PK_For_Validation (
521 new_references.responsible_org_unit_cd,
522 new_references.responsible_ou_start_dt
523 ) THEN
524
525 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
526 IGS_GE_MSG_STACK.ADD;
527 App_Exception.Raise_Exception;
528
529 END IF;
530
531 END IF;
532
533 IF (((old_references.UNIT_SET_CAT = new_references.UNIT_SET_CAT)) OR
534 ((new_references.UNIT_SET_CAT IS NULL))) THEN
535 NULL;
536 ELSE
537
538 IF NOT IGS_EN_UNIT_SET_CAT_PKG.Get_PK_For_Validation (
539 new_references.UNIT_SET_CAT
540 ) THEN
541
542 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
543 IGS_GE_MSG_STACK.ADD;
544 App_Exception.Raise_Exception;
545
546 END IF;
547
548 END IF;
549
550 IF (((old_references.UNIT_SET_STATUS = new_references.UNIT_SET_STATUS)) OR
551 ((new_references.UNIT_SET_STATUS IS NULL))) THEN
552 NULL;
553 ELSE
554 IF NOT IGS_EN_UNIT_SET_STAT_PKG.Get_PK_For_Validation (
555 new_references.UNIT_SET_STATUS
556 ) THEN
557
558 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
559 IGS_GE_MSG_STACK.ADD;
560 App_Exception.Raise_Exception;
561
562 END IF;
563 END IF;
564
565 END Check_Parent_Existance;
566
567 PROCEDURE Check_Child_Existance as
568
569 /* Who When What
570 pathipat 17-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
571 Added call to igs_fi_fee_as_rate_pkg.get_fk_igs_en_unit_set_all
572 and igs_fi_fee_as_items_pkg.get_fk_igs_en_unit_set_all
573 sbaliga 9-May-2002 Added 2 more calls to check for child existence
574 as part of #2330002
575 myoganat 06-Jun-2003 Added IGS_EN_UNIT_SET_MAP_PKG.get_fk_igs_en_unit_set
576 as part of #2829265
577 */
578
579 BEGIN
580
581 IGS_AD_UNIT_SETS_PKG.GET_FK_IGS_EN_UNIT_SET (
582 old_references.unit_set_cd,
583 old_references.version_number
584 );
585
586 IGS_AD_PS_APPL_INST_PKG.GET_FK_IGS_EN_UNIT_SET (
587 old_references.unit_set_cd,
588 old_references.version_number
589 );
590
591 IGS_GR_AWD_CRM_UT_ST_PKG.GET_FK_IGS_EN_UNIT_SET (
592 old_references.unit_set_cd,
593 old_references.version_number
594 );
595
596 IGS_PS_COO_AD_UNIT_S_PKG.GET_FK_IGS_EN_UNIT_SET (
597 old_references.unit_set_cd,
598 old_references.version_number
599 );
600
601 IGS_PS_ENT_PT_REF_CD_PKG.GET_FK_IGS_EN_UNIT_SET (
602 old_references.unit_set_cd,
603 old_references.version_number
604 );
605
606 IGS_PS_OFR_UNIT_SET_PKG.GET_FK_IGS_EN_UNIT_SET (
607 old_references.unit_set_cd,
608 old_references.version_number
609 );
610
611 IGS_PE_UNT_SET_EXCL_PKG.GET_FK_IGS_EN_UNIT_SET (
612 old_references.unit_set_cd,
613 old_references.version_number
614 );
615
616 IGS_PR_OU_UNIT_SET_PKG.GET_FK_IGS_EN_UNIT_SET (
617 old_references.unit_set_cd,
618 old_references.version_number
619 );
620
621 IGS_AD_SBM_PS_FNTRGT_PKG.GET_FK_IGS_EN_UNIT_SET (
622 old_references.unit_set_cd,
623 old_references.version_number
624 );
625
626 IGS_PR_SDT_PR_UNT_ST_PKG.GET_FK_IGS_EN_UNIT_SET (
627 old_references.unit_set_cd,
628 old_references.version_number
629 );
630
631 IGS_AS_SU_SETATMPT_PKG.GET_FK_IGS_EN_UNIT_SET (
632 old_references.unit_set_cd,
633 old_references.version_number
634 );
635
636 IGS_EN_UNITSETPSTYPE_PKG.GET_FK_IGS_EN_UNIT_SET (
637 old_references.unit_set_cd,
638 old_references.version_number
639 );
640
641 IGS_EN_UNITSETFEETRG_PKG.GET_FK_IGS_EN_UNIT_SET (
642 old_references.unit_set_cd,
643 old_references.version_number
644 );
645
646 IGS_EN_UNIT_SET_NOTE_PKG.GET_FK_IGS_EN_UNIT_SET (
647 old_references.unit_set_cd,
648 old_references.version_number
649 );
650
651 IGS_EN_UNIT_SET_RULE_PKG.GET_FK_IGS_EN_UNIT_SET (
652 old_references.unit_set_cd,
653 old_references.version_number
654 );
655 IGS_PS_RSV_ORGUN_PRF_PKG.GET_FK_IGS_EN_UNIT_SET_ALL(
656 old_references.unit_set_cd,
657 old_references.version_number);
658 IGS_PS_RSV_UOP_PRF_PKG.GET_FK_IGS_EN_UNIT_SET_ALL(
659 old_references.unit_set_cd,
660 old_references.version_number);
661 IGS_PS_RSV_USEC_PRF_PKG.GET_FK_IGS_EN_UNIT_SET_ALL(
662 old_references.unit_set_cd,
663 old_references.version_number);
664
665 IGS_AS_GPC_UNIT_SETS_PKG.GET_FK_IGS_EN_UNIT_SET(
666 old_references.unit_set_cd,
667 old_references.version_number);
668
669 --This call was added by sbaliga as part of #2330002
670 IGS_HE_POOUS_OU_ALL_PKG.GET_FK_IGS_EN_UNIT_SET_ALL(
671 old_references.unit_set_cd,
672 old_references.version_number
673 );
674
675
676 -- Added the following check chaild existance for the HESA requirment, pmarada
677 IGS_HE_POOUS_ALL_PKG.GET_FK_IGS_EN_UNIT_SET_ALL(
678 old_references.unit_set_cd,
679 old_references.version_number);
680
681 -- Enh bug#2833852
682 -- Added the following call for implementing the foreign key constraint.
683 IGS_PS_US_FLD_STUDY_PKG.GET_FK_IGS_EN_UNIT_SET (
684 old_references.unit_set_cd,
685 old_references.version_number
686 );
687
688 IGS_EN_UNIT_SET_MAP_PKG.get_fk_igs_en_unit_set (
689 old_references.unit_set_cd,
690 old_references.version_number
691 );
692
693 igs_fi_fee_as_rate_pkg.get_fk_igs_en_unit_set_all(
694 old_references.unit_set_cd,
695 old_references.version_number
696 );
697
698 igs_fi_fee_as_items_pkg.get_fk_igs_en_unit_set_all(
699 old_references.unit_set_cd,
700 old_references.version_number
701 );
702
703
704 END Check_Child_Existance;
705
706 FUNCTION Get_PK_For_Validation (
707 x_unit_set_cd IN VARCHAR2,
708 x_version_number IN NUMBER
709 ) RETURN BOOLEAN as
710
711 -- As a part of fixing the Bug # 2729917 to resolve locking issue.
712 -- Need to lock the Unit Set table only when the system status of the unit set status is 'PLANNED'
713 -- For other system statuses we are not allowing the user to delete the record.
714
715 CURSOR cur_get_status IS
716 SELECT uss.s_unit_set_status
717 FROM igs_en_unit_set_all usv,
718 igs_en_unit_set_stat uss
719 WHERE usv.unit_set_status = uss.unit_set_status
720 AND usv.unit_set_cd = x_unit_set_cd
721 AND usv.version_number = x_version_number;
722
723 l_c_unit_set_status igs_en_unit_set_stat.s_unit_set_status%TYPE;
724
725 CURSOR cur_rowid IS
726 SELECT rowid
727 FROM IGS_EN_UNIT_SET_ALL
728 WHERE unit_set_cd = x_unit_set_cd
729 AND version_number = x_version_number
730 FOR UPDATE NOWAIT;
731
732 lv_rowid cur_rowid%RowType;
733
734 BEGIN
735
736 OPEN cur_get_status;
737 FETCH cur_get_status INTO l_c_unit_set_status;
738 IF cur_get_status%FOUND THEN
739 CLOSE cur_get_status;
740 IF l_c_unit_set_status = 'PLANNED' THEN
741 OPEN cur_rowid;
742 FETCH cur_rowid INTO lv_rowid;
743 IF (cur_rowid%FOUND) THEN
744 CLOSE cur_rowid;
745 RETURN TRUE;
746 ELSE
747 CLOSE cur_rowid;
748 RETURN FALSE;
749 END IF;
750 ELSE
751 RETURN TRUE;
752 END IF;
753 ELSE
754 CLOSE cur_get_status;
755 RETURN FALSE;
756 END IF;
757
758 END Get_PK_For_Validation;
759
760 PROCEDURE GET_FK_IGS_OR_UNIT (
761 x_org_unit_cd IN VARCHAR2,
762 x_start_dt IN DATE
763 ) as
764
765 CURSOR cur_rowid IS
766 SELECT rowid
767 FROM IGS_EN_UNIT_SET_ALL
768 WHERE responsible_org_unit_cd = x_org_unit_cd
769 AND responsible_ou_start_dt = x_start_dt ;
770
771 lv_rowid cur_rowid%RowType;
772
773 BEGIN
774
775 Open cur_rowid;
776 Fetch cur_rowid INTO lv_rowid;
777 IF (cur_rowid%FOUND) THEN
778 Fnd_Message.Set_Name ('IGS', 'IGS_EN_US_OU_FK');
779 IGS_GE_MSG_STACK.ADD;
780 Close cur_rowid;
781 App_Exception.Raise_Exception;
782 Return;
783 END IF;
784 Close cur_rowid;
785
786 END GET_FK_IGS_OR_UNIT;
787
788 PROCEDURE GET_FK_IGS_EN_UNIT_SET_CAT (
789 x_unit_set_cat IN VARCHAR2
790 ) as
791
792 CURSOR cur_rowid IS
793 SELECT rowid
794 FROM IGS_EN_UNIT_SET_ALL
795 WHERE UNIT_SET_CAT = x_unit_set_cat ;
796
797 lv_rowid cur_rowid%RowType;
798
799 BEGIN
800
801 Open cur_rowid;
802 Fetch cur_rowid INTO lv_rowid;
803 IF (cur_rowid%FOUND) THEN
804 Fnd_Message.Set_Name ('IGS', 'IGS_EN_US_USC_FK');
805 IGS_GE_MSG_STACK.ADD;
806 Close cur_rowid;
807 App_Exception.Raise_Exception;
808 Return;
809 END IF;
810 Close cur_rowid;
811
812 END GET_FK_IGS_EN_UNIT_SET_CAT;
813
814 PROCEDURE GET_FK_IGS_EN_UNIT_SET_STAT (
815 x_unit_set_status IN VARCHAR2
816 ) as
817
818 CURSOR cur_rowid IS
819 SELECT rowid
820 FROM IGS_EN_UNIT_SET_ALL
821 WHERE UNIT_SET_STATUS = x_unit_set_status ;
822
823 lv_rowid cur_rowid%RowType;
824
825 BEGIN
826
827 Open cur_rowid;
828 Fetch cur_rowid INTO lv_rowid;
829 IF (cur_rowid%FOUND) THEN
830 Fnd_Message.Set_Name ('IGS', 'IGS_EN_US_USS_FK');
831 IGS_GE_MSG_STACK.ADD;
832 Close cur_rowid;
833 App_Exception.Raise_Exception;
834 Return;
835 END IF;
836 Close cur_rowid;
837 END GET_FK_IGS_EN_UNIT_SET_STAT;
838
839
840 PROCEDURE Before_DML (
841 p_action IN VARCHAR2,
842 x_rowid IN VARCHAR2 DEFAULT NULL,
843 x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
844 x_version_number IN NUMBER DEFAULT NULL,
845 x_unit_set_status IN VARCHAR2 DEFAULT NULL,
846 x_unit_set_cat IN VARCHAR2 DEFAULT NULL,
847 x_start_dt IN DATE DEFAULT NULL,
848 x_review_dt IN DATE DEFAULT NULL,
849 x_expiry_dt IN DATE DEFAULT NULL,
850 x_end_dt IN DATE DEFAULT NULL,
851 x_title IN VARCHAR2 DEFAULT NULL,
852 x_short_title IN VARCHAR2 DEFAULT NULL,
853 x_abbreviation IN VARCHAR2 DEFAULT NULL,
854 x_responsible_org_unit_cd IN VARCHAR2 DEFAULT NULL,
855 x_responsible_ou_start_dt IN DATE DEFAULT NULL,
856 x_administrative_ind IN VARCHAR2 DEFAULT NULL,
857 x_authorisation_rqrd_ind IN VARCHAR2 DEFAULT NULL,
858 x_attribute_category IN VARCHAR2 DEFAULT NULL,
859 x_attribute1 IN VARCHAR2 DEFAULT NULL,
860 x_attribute2 IN VARCHAR2 DEFAULT NULL,
861 x_attribute3 IN VARCHAR2 DEFAULT NULL,
862 x_attribute4 IN VARCHAR2 DEFAULT NULL,
863 x_attribute5 IN VARCHAR2 DEFAULT NULL,
864 x_attribute6 IN VARCHAR2 DEFAULT NULL,
865 x_attribute7 IN VARCHAR2 DEFAULT NULL,
866 x_attribute8 IN VARCHAR2 DEFAULT NULL,
867 x_attribute9 IN VARCHAR2 DEFAULT NULL,
868 x_attribute10 IN VARCHAR2 DEFAULT NULL,
869 x_attribute11 IN VARCHAR2 DEFAULT NULL,
870 x_attribute12 IN VARCHAR2 DEFAULT NULL,
871 x_attribute13 IN VARCHAR2 DEFAULT NULL,
872 x_attribute14 IN VARCHAR2 DEFAULT NULL,
873 x_attribute15 IN VARCHAR2 DEFAULT NULL,
874 x_attribute16 in VARCHAR2 DEFAULT NULL,
875 x_attribute17 in VARCHAR2 DEFAULT NULL,
876 x_attribute18 in VARCHAR2 DEFAULT NULL,
877 x_attribute19 in VARCHAR2 DEFAULT NULL,
878 x_attribute20 in VARCHAR2 DEFAULT NULL,
879 x_creation_date IN DATE DEFAULT NULL,
880 x_created_by IN NUMBER DEFAULT NULL,
881 x_last_update_date IN DATE DEFAULT NULL,
882 x_last_updated_by IN NUMBER DEFAULT NULL,
883 x_last_update_login IN NUMBER DEFAULT NULL,
884 x_org_id IN NUMBER
885 ) as
886 BEGIN
887
888 Set_Column_Values (
889 p_action,
890 x_rowid,
891 x_unit_set_cd,
892 x_version_number,
893 x_unit_set_status,
894 x_unit_set_cat,
895 x_start_dt,
896 x_review_dt,
897 x_expiry_dt,
898 x_end_dt,
899 x_title,
900 x_short_title,
901 x_abbreviation,
902 x_responsible_org_unit_cd,
903 x_responsible_ou_start_dt,
904 x_administrative_ind,
905 x_authorisation_rqrd_ind,
906 x_attribute_category ,
907 x_attribute1,
908 x_attribute2,
909 x_attribute3,
910 x_attribute4,
911 x_attribute5,
912 x_attribute6,
913 x_attribute7,
914 x_attribute8,
915 x_attribute9,
916 x_attribute10,
917 x_attribute11,
918 x_attribute12,
919 x_attribute13,
920 x_attribute14,
921 x_attribute15,
922 x_attribute16 ,
923 x_attribute17 ,
924 x_attribute18 ,
925 x_attribute19 ,
926 x_attribute20 ,
927 x_creation_date,
928 x_created_by,
929 x_last_update_date,
930 x_last_updated_by,
931 x_last_update_login,
932 x_org_id
933 );
934
935 IF (p_action = 'INSERT') THEN
936 -- Call all the procedures related to Before Insert.
937 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
938 IF Get_PK_For_Validation(
939 new_references.unit_set_cd,
940 new_references.version_number
941 ) THEN
942
943 Fnd_message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
944 IGS_GE_MSG_STACK.ADD;
945 App_Exception.Raise_Exception;
946
947 END IF;
948
949 Check_Constraints;
950 Check_Parent_Existance;
951 ELSIF (p_action = 'UPDATE') THEN
952 -- Call all the procedures related to Before Update.
953 -- Added the call to beforerowupdate as a part of Bug # 2729917
954 beforerowupdate;
955 BeforeRowInsertUpdate1 ( p_updating => TRUE );
956 Check_Constraints;
957 Check_Parent_Existance;
958 ELSIF (p_action = 'DELETE') THEN
959 -- Call all the procedures related to Before Delete.
960 -- Added the call to beforerowdelete as a part of Bug # 2729917
961 beforerowdelete;
962 Check_Child_Existance;
963 ELSIF (p_action = 'VALIDATE_INSERT') THEN
964 IF Get_PK_For_Validation (
965 new_references.unit_set_cd,
966 new_references.version_number
967 ) THEN
968 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
969 IGS_GE_MSG_STACK.ADD;
970 App_Exception.Raise_Exception;
971 END IF;
972 Check_Constraints;
973 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
974 Check_Constraints;
975 ELSIF (p_action = 'VALIDATE_DELETE') THEN
976 -- Added the call to beforerowdelete as a part of Bug # 2729917
977 beforerowdelete;
978 Check_Child_Existance;
979 END IF;
980
981 END Before_DML;
982
983 PROCEDURE After_DML (
984 p_action IN VARCHAR2,
985 x_rowid IN VARCHAR2
986 ) as
987 BEGIN
988
989 l_rowid := x_rowid;
990
991 IF (p_action = 'INSERT') THEN
992 -- Call all the procedures related to After Insert.
993 AfterRowInsertUpdate2 ( p_inserting => TRUE );
994 -- AfterStmtInsertUpdate4 ( p_inserting => TRUE );
995 ELSIF (p_action = 'UPDATE') THEN
996 -- Call all the procedures related to After Update.
997 AfterRowInsertUpdate2 ( p_updating => TRUE );
998 AfterRowUpdate3 ( p_updating => TRUE );
999 -- AfterStmtInsertUpdate4 ( p_updating => TRUE );
1000 ELSIF (p_action = 'DELETE') THEN
1001 -- Call all the procedures related to After Delete.
1002 Null;
1003 END IF;
1004
1005 END After_DML;
1006
1007
1008 procedure INSERT_ROW (
1009 X_ROWID in out NOCOPY VARCHAR2,
1010 X_UNIT_SET_CD in VARCHAR2,
1011 X_VERSION_NUMBER in NUMBER,
1012 X_UNIT_SET_STATUS in VARCHAR2,
1013 X_UNIT_SET_CAT in VARCHAR2,
1014 X_START_DT in DATE,
1015 X_REVIEW_DT in DATE,
1016 X_EXPIRY_DT in DATE,
1017 X_END_DT in DATE,
1018 X_TITLE in VARCHAR2,
1019 X_SHORT_TITLE in VARCHAR2,
1020 X_ABBREVIATION in VARCHAR2,
1021 X_RESPONSIBLE_ORG_UNIT_CD in VARCHAR2,
1022 X_RESPONSIBLE_OU_START_DT in DATE,
1023 X_ADMINISTRATIVE_IND in VARCHAR2,
1024 X_AUTHORISATION_RQRD_IND in VARCHAR2,
1025 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
1026 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
1027 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
1028 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
1029 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
1030 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
1031 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
1032 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
1033 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
1034 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
1035 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
1036 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
1037 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
1038 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
1039 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
1040 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
1041 X_ATTRIBUTE16 in VARCHAR2 DEFAULT NULL,
1042 X_ATTRIBUTE17 in VARCHAR2 DEFAULT NULL,
1043 X_ATTRIBUTE18 in VARCHAR2 DEFAULT NULL,
1044 X_ATTRIBUTE19 in VARCHAR2 DEFAULT NULL,
1045 X_ATTRIBUTE20 in VARCHAR2 DEFAULT NULL,
1046 X_MODE in VARCHAR2 default 'R',
1047 x_org_id IN NUMBER
1048 ) AS
1049 cursor C is select ROWID from IGS_EN_UNIT_SET_ALL
1050 where UNIT_SET_CD = X_UNIT_SET_CD
1051 and VERSION_NUMBER = X_VERSION_NUMBER;
1052 X_LAST_UPDATE_DATE DATE;
1053 X_LAST_UPDATED_BY NUMBER;
1054 X_LAST_UPDATE_LOGIN NUMBER;
1055 begin
1056 X_LAST_UPDATE_DATE := SYSDATE;
1057 if(X_MODE = 'I') then
1058 X_LAST_UPDATED_BY := 1;
1059 X_LAST_UPDATE_LOGIN := 0;
1060 elsif (X_MODE = 'R') then
1061 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1062 if X_LAST_UPDATED_BY is NULL then
1063 X_LAST_UPDATED_BY := -1;
1064 end if;
1065 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1066 if X_LAST_UPDATE_LOGIN is NULL then
1067 X_LAST_UPDATE_LOGIN := -1;
1068 end if;
1069 else
1070 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1071 IGS_GE_MSG_STACK.ADD;
1072 app_exception.raise_exception;
1073 end if;
1074
1075 Before_DML (
1076 p_action => 'INSERT',
1077 x_rowid => x_rowid ,
1078 x_unit_set_cd => x_unit_set_cd ,
1079 x_version_number => x_version_number ,
1080 x_unit_set_status => x_unit_set_status ,
1081 x_unit_set_cat => x_unit_set_cat ,
1082 x_start_dt => x_start_dt ,
1083 x_review_dt => x_review_dt ,
1084 x_expiry_dt => x_expiry_dt ,
1085 x_end_dt => x_end_dt ,
1086 x_title => x_title ,
1087 x_short_title => x_short_title ,
1088 x_abbreviation => x_abbreviation ,
1089 x_responsible_org_unit_cd => x_responsible_org_unit_cd ,
1090 x_responsible_ou_start_dt => x_responsible_ou_start_dt ,
1091 x_administrative_ind => NVL(X_ADMINISTRATIVE_IND,'N') ,
1092 x_authorisation_rqrd_ind=> NVL(X_AUTHORISATION_RQRD_IND,'N') ,
1093 x_attribute_category => x_attribute_category,
1094 x_attribute1 => x_attribute1,
1095 x_attribute2 => x_attribute2,
1096 x_attribute3 => x_attribute3,
1097 x_attribute4 => x_attribute4,
1098 x_attribute5 => x_attribute5,
1099 x_attribute6 => x_attribute6,
1100 x_attribute7 => x_attribute7,
1101 x_attribute8 => x_attribute8,
1102 x_attribute9 => x_attribute9,
1103 x_attribute10 => x_attribute10,
1104 x_attribute11 => x_attribute11,
1105 x_attribute12 => x_attribute12,
1106 x_attribute13 => x_attribute13,
1107 x_attribute14 => x_attribute14,
1108 x_attribute15 => x_attribute15,
1109 x_attribute16 => x_attribute16,
1110 x_attribute17 => x_attribute17,
1111 x_attribute18 => x_attribute18,
1112 x_attribute19 => x_attribute19,
1113 x_attribute20 => x_attribute20,
1114 x_creation_date => x_last_update_date ,
1115 x_created_by => x_last_updated_by,
1116 x_last_update_date => x_last_update_date,
1117 x_last_updated_by => x_last_updated_by,
1118 x_last_update_login => x_last_update_login ,
1119 x_org_id => igs_ge_gen_003.get_org_id
1120 );
1121
1122 insert into IGS_EN_UNIT_SET_ALL (
1123 UNIT_SET_CD,
1124 VERSION_NUMBER,
1125 UNIT_SET_STATUS,
1126 UNIT_SET_CAT,
1127 START_DT,
1128 REVIEW_DT,
1129 EXPIRY_DT,
1130 END_DT,
1131 TITLE,
1132 SHORT_TITLE,
1133 ABBREVIATION,
1134 RESPONSIBLE_ORG_UNIT_CD,
1135 RESPONSIBLE_OU_START_DT,
1136 ADMINISTRATIVE_IND,
1137 AUTHORISATION_RQRD_IND,
1138 ATTRIBUTE_CATEGORY,
1139 ATTRIBUTE1,
1140 ATTRIBUTE2,
1141 ATTRIBUTE3,
1142 ATTRIBUTE4,
1143 ATTRIBUTE5,
1144 ATTRIBUTE6,
1145 ATTRIBUTE7,
1146 ATTRIBUTE8,
1147 ATTRIBUTE9,
1148 ATTRIBUTE10,
1149 ATTRIBUTE11,
1150 ATTRIBUTE12,
1151 ATTRIBUTE13,
1152 ATTRIBUTE14,
1153 ATTRIBUTE15,
1154 ATTRIBUTE16,
1155 ATTRIBUTE17,
1156 ATTRIBUTE18,
1157 ATTRIBUTE19,
1158 ATTRIBUTE20,
1159 CREATION_DATE,
1160 CREATED_BY,
1161 LAST_UPDATE_DATE,
1162 LAST_UPDATED_BY,
1163 LAST_UPDATE_LOGIN,
1164 org_id
1165 ) values (
1166 NEW_REFERENCES.UNIT_SET_CD,
1167 NEW_REFERENCES.VERSION_NUMBER,
1168 NEW_REFERENCES.UNIT_SET_STATUS,
1169 NEW_REFERENCES.UNIT_SET_CAT,
1170 NEW_REFERENCES.START_DT,
1171 NEW_REFERENCES.REVIEW_DT,
1172 NEW_REFERENCES.EXPIRY_DT,
1173 NEW_REFERENCES.END_DT,
1174 NEW_REFERENCES.TITLE,
1175 NEW_REFERENCES.SHORT_TITLE,
1176 NEW_REFERENCES.ABBREVIATION,
1177 NEW_REFERENCES.RESPONSIBLE_ORG_UNIT_CD,
1178 NEW_REFERENCES.RESPONSIBLE_OU_START_DT,
1179 NEW_REFERENCES.ADMINISTRATIVE_IND,
1180 NEW_REFERENCES.AUTHORISATION_RQRD_IND,
1181 NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1182 NEW_REFERENCES.ATTRIBUTE1,
1183 NEW_REFERENCES.ATTRIBUTE2,
1184 NEW_REFERENCES.ATTRIBUTE3,
1185 NEW_REFERENCES.ATTRIBUTE4,
1186 NEW_REFERENCES.ATTRIBUTE5,
1187 NEW_REFERENCES.ATTRIBUTE6,
1188 NEW_REFERENCES.ATTRIBUTE7,
1189 NEW_REFERENCES.ATTRIBUTE8,
1190 NEW_REFERENCES.ATTRIBUTE9,
1191 NEW_REFERENCES.ATTRIBUTE10,
1192 NEW_REFERENCES.ATTRIBUTE11,
1193 NEW_REFERENCES.ATTRIBUTE12,
1194 NEW_REFERENCES.ATTRIBUTE13,
1195 NEW_REFERENCES.ATTRIBUTE14,
1196 NEW_REFERENCES.ATTRIBUTE15,
1197 NEW_REFERENCES.ATTRIBUTE16,
1198 NEW_REFERENCES.ATTRIBUTE17,
1199 NEW_REFERENCES.ATTRIBUTE18,
1200 NEW_REFERENCES.ATTRIBUTE19,
1201 NEW_REFERENCES.ATTRIBUTE20,
1202 X_LAST_UPDATE_DATE,
1203 X_LAST_UPDATED_BY,
1204 X_LAST_UPDATE_DATE,
1205 X_LAST_UPDATED_BY,
1206 X_LAST_UPDATE_LOGIN,
1207 NEW_REFERENCES.org_id
1208 );
1209
1210 open c;
1211 fetch c into X_ROWID;
1212 if (c%notfound) then
1213 close c;
1214 raise no_data_found;
1215 end if;
1216 close c;
1217
1218 After_DML(
1219 p_action => 'INSERT',
1220 x_rowid => X_ROWID
1221 );
1222 end INSERT_ROW;
1223
1224 procedure LOCK_ROW (
1225 X_ROWID IN VARCHAR2,
1226 X_UNIT_SET_CD in VARCHAR2,
1227 X_VERSION_NUMBER in NUMBER,
1228 X_UNIT_SET_STATUS in VARCHAR2,
1229 X_UNIT_SET_CAT in VARCHAR2,
1230 X_START_DT in DATE,
1231 X_REVIEW_DT in DATE,
1232 X_EXPIRY_DT in DATE,
1233 X_END_DT in DATE,
1234 X_TITLE in VARCHAR2,
1235 X_SHORT_TITLE in VARCHAR2,
1236 X_ABBREVIATION in VARCHAR2,
1237 X_RESPONSIBLE_ORG_UNIT_CD in VARCHAR2,
1238 X_RESPONSIBLE_OU_START_DT in DATE,
1239 X_ADMINISTRATIVE_IND in VARCHAR2,
1240 X_AUTHORISATION_RQRD_IND in VARCHAR2,
1241 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
1242 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
1243 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
1244 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
1245 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
1246 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
1247 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
1248 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
1249 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
1250 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
1251 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
1252 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
1253 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
1254 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
1255 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
1256 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
1257 X_ATTRIBUTE16 in VARCHAR2 DEFAULT NULL,
1258 X_ATTRIBUTE17 in VARCHAR2 DEFAULT NULL,
1259 X_ATTRIBUTE18 in VARCHAR2 DEFAULT NULL,
1260 X_ATTRIBUTE19 in VARCHAR2 DEFAULT NULL,
1261 X_ATTRIBUTE20 in VARCHAR2 DEFAULT NULL
1262
1263 ) as
1264 cursor c1 is select
1265 UNIT_SET_STATUS,
1266 UNIT_SET_CAT,
1267 START_DT,
1268 REVIEW_DT,
1269 EXPIRY_DT,
1270 END_DT,
1271 TITLE,
1272 SHORT_TITLE,
1273 ABBREVIATION,
1274 RESPONSIBLE_ORG_UNIT_CD,
1275 RESPONSIBLE_OU_START_DT,
1276 ADMINISTRATIVE_IND,
1277 AUTHORISATION_RQRD_IND,
1278 ATTRIBUTE_CATEGORY,
1279 ATTRIBUTE1,
1280 ATTRIBUTE2,
1281 ATTRIBUTE3,
1282 ATTRIBUTE4,
1283 ATTRIBUTE5,
1284 ATTRIBUTE6,
1285 ATTRIBUTE7,
1286 ATTRIBUTE8,
1287 ATTRIBUTE9,
1288 ATTRIBUTE10,
1289 ATTRIBUTE11,
1290 ATTRIBUTE12,
1291 ATTRIBUTE13,
1292 ATTRIBUTE14,
1293 ATTRIBUTE15,
1294 ATTRIBUTE16,
1295 ATTRIBUTE17,
1296 ATTRIBUTE18,
1297 ATTRIBUTE19,
1298 ATTRIBUTE20
1299 from IGS_EN_UNIT_SET_ALL
1300 where ROWID = X_ROWID
1301 for update nowait;
1302 tlinfo c1%rowtype;
1303
1304 begin
1305 open c1;
1306 fetch c1 into tlinfo;
1307 if (c1%notfound) then
1308 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1309 IGS_GE_MSG_STACK.ADD;
1310 close c1;
1311 app_exception.raise_exception;
1312 return;
1313 end if;
1314 close c1;
1315
1316 if ( (tlinfo.UNIT_SET_STATUS = X_UNIT_SET_STATUS)
1317 AND (tlinfo.UNIT_SET_CAT = X_UNIT_SET_CAT)
1318 AND (tlinfo.START_DT = X_START_DT)
1319 AND ((tlinfo.REVIEW_DT = X_REVIEW_DT)
1320 OR ((tlinfo.REVIEW_DT is null)
1321 AND (X_REVIEW_DT is null)))
1322 AND ((tlinfo.EXPIRY_DT = X_EXPIRY_DT)
1323 OR ((tlinfo.EXPIRY_DT is null)
1324 AND (X_EXPIRY_DT is null)))
1325 AND ((tlinfo.END_DT = X_END_DT)
1326 OR ((tlinfo.END_DT is null)
1327 AND (X_END_DT is null)))
1328 AND (tlinfo.TITLE = X_TITLE)
1329 AND (tlinfo.SHORT_TITLE = X_SHORT_TITLE)
1330 AND (tlinfo.ABBREVIATION = X_ABBREVIATION)
1331 AND ((tlinfo.RESPONSIBLE_ORG_UNIT_CD = X_RESPONSIBLE_ORG_UNIT_CD)
1332 OR ((tlinfo.RESPONSIBLE_ORG_UNIT_CD is null)
1333 AND (X_RESPONSIBLE_ORG_UNIT_CD is null)))
1334 AND ((tlinfo.RESPONSIBLE_OU_START_DT = X_RESPONSIBLE_OU_START_DT)
1335 OR ((tlinfo.RESPONSIBLE_OU_START_DT is null)
1336 AND (X_RESPONSIBLE_OU_START_DT is null)))
1337 AND (tlinfo.ADMINISTRATIVE_IND = X_ADMINISTRATIVE_IND)
1338 AND (tlinfo.AUTHORISATION_RQRD_IND = X_AUTHORISATION_RQRD_IND)
1339 AND ((tlinfo.attribute_category = X_ATTRIBUTE_CATEGORY) OR
1340 ((tlinfo.attribute_category IS NULL) AND (X_ATTRIBUTE_CATEGORY IS NULL)))
1341 AND ((tlinfo.attribute1 = X_ATTRIBUTE1) OR
1342 ((tlinfo.attribute1 IS NULL) AND (X_ATTRIBUTE1 IS NULL)))
1343 AND ((tlinfo.attribute2 = X_ATTRIBUTE2) OR
1344 ((tlinfo.attribute2 IS NULL) AND (X_ATTRIBUTE2 IS NULL)))
1345 AND ((tlinfo.attribute3 = X_ATTRIBUTE3) OR
1346 ((tlinfo.attribute3 IS NULL) AND (X_ATTRIBUTE3 IS NULL)))
1347 AND ((tlinfo.attribute4 = X_ATTRIBUTE4) OR
1348 ((tlinfo.attribute4 IS NULL) AND (X_ATTRIBUTE4 IS NULL)))
1349 AND ((tlinfo.attribute5 = X_ATTRIBUTE5) OR
1350 ((tlinfo.attribute5 IS NULL) AND (X_ATTRIBUTE5 IS NULL)))
1351 AND ((tlinfo.attribute6 = X_ATTRIBUTE6) OR
1352 ((tlinfo.attribute6 IS NULL) AND (X_ATTRIBUTE6 IS NULL)))
1353 AND ((tlinfo.attribute7 = X_ATTRIBUTE7) OR
1354 ((tlinfo.attribute7 IS NULL) AND (X_ATTRIBUTE7 IS NULL)))
1355 AND ((tlinfo.attribute8 = X_ATTRIBUTE8) OR
1356 ((tlinfo.attribute8 IS NULL) AND (X_ATTRIBUTE8 IS NULL)))
1357 AND ((tlinfo.attribute9 = X_ATTRIBUTE9) OR
1358 ((tlinfo.attribute9 IS NULL) AND (X_ATTRIBUTE9 IS NULL)))
1359 AND ((tlinfo.attribute10 = X_ATTRIBUTE10) OR
1360 ((tlinfo.attribute10 IS NULL) AND (X_ATTRIBUTE10 IS NULL)))
1361 AND ((tlinfo.attribute11 = X_ATTRIBUTE11) OR
1362 ((tlinfo.attribute11 IS NULL) AND (X_ATTRIBUTE11 IS NULL)))
1363 AND ((tlinfo.attribute12 = X_ATTRIBUTE12) OR
1364 ((tlinfo.attribute12 IS NULL) AND (X_ATTRIBUTE12 IS NULL)))
1365 AND ((tlinfo.attribute13 = X_ATTRIBUTE13) OR
1366 ((tlinfo.attribute13 IS NULL) AND (X_ATTRIBUTE13 IS NULL)))
1367 AND ((tlinfo.attribute14 = X_ATTRIBUTE14) OR
1368 ((tlinfo.attribute14 IS NULL) AND (X_ATTRIBUTE14 IS NULL)))
1369 AND ((tlinfo.attribute15 = X_ATTRIBUTE15) OR
1370 ((tlinfo.attribute15 IS NULL) AND (X_ATTRIBUTE15 IS NULL)))
1371 AND ((tlinfo.attribute16 = X_ATTRIBUTE16) OR
1372 ((tlinfo.attribute16 IS NULL) AND (X_ATTRIBUTE16 IS NULL)))
1373 AND ((tlinfo.attribute17 = X_ATTRIBUTE17) OR
1374 ((tlinfo.attribute17 IS NULL) AND (X_ATTRIBUTE17 IS NULL)))
1375 AND ((tlinfo.attribute18 = X_ATTRIBUTE18) OR
1376 ((tlinfo.attribute18 IS NULL) AND (X_ATTRIBUTE18 IS NULL)))
1377 AND ((tlinfo.attribute19 = X_ATTRIBUTE19) OR
1378 ((tlinfo.attribute19 IS NULL) AND (X_ATTRIBUTE19 IS NULL)))
1379 AND ((tlinfo.attribute20 = X_ATTRIBUTE20) OR
1380 ((tlinfo.attribute20 IS NULL) AND (X_ATTRIBUTE20 IS NULL)))
1381 ) then
1382 null;
1383 else
1384 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1385 IGS_GE_MSG_STACK.ADD;
1386 app_exception.raise_exception;
1387 end if;
1388 return;
1389 end LOCK_ROW;
1390
1391 procedure UPDATE_ROW (
1392 X_ROWID IN VARCHAR2,
1393 X_UNIT_SET_CD in VARCHAR2,
1394 X_VERSION_NUMBER in NUMBER,
1395 X_UNIT_SET_STATUS in VARCHAR2,
1396 X_UNIT_SET_CAT in VARCHAR2,
1397 X_START_DT in DATE,
1398 X_REVIEW_DT in DATE,
1399 X_EXPIRY_DT in DATE,
1400 X_END_DT in DATE,
1401 X_TITLE in VARCHAR2,
1402 X_SHORT_TITLE in VARCHAR2,
1403 X_ABBREVIATION in VARCHAR2,
1404 X_RESPONSIBLE_ORG_UNIT_CD in VARCHAR2,
1405 X_RESPONSIBLE_OU_START_DT in DATE,
1406 X_ADMINISTRATIVE_IND in VARCHAR2,
1407 X_AUTHORISATION_RQRD_IND in VARCHAR2,
1408 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
1409 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
1410 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
1411 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
1412 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
1413 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
1414 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
1415 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
1416 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
1417 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
1418 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
1419 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
1420 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
1421 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
1422 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
1423 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
1424 X_ATTRIBUTE16 in VARCHAR2 DEFAULT NULL,
1425 X_ATTRIBUTE17 in VARCHAR2 DEFAULT NULL,
1426 X_ATTRIBUTE18 in VARCHAR2 DEFAULT NULL,
1427 X_ATTRIBUTE19 in VARCHAR2 DEFAULT NULL,
1428 X_ATTRIBUTE20 in VARCHAR2 DEFAULT NULL,
1429 X_MODE in VARCHAR2 default 'R'
1430 ) as
1431 X_LAST_UPDATE_DATE DATE;
1432 X_LAST_UPDATED_BY NUMBER;
1433 X_LAST_UPDATE_LOGIN NUMBER;
1434 begin
1435 X_LAST_UPDATE_DATE := SYSDATE;
1436 if(X_MODE = 'I') then
1437 X_LAST_UPDATED_BY := 1;
1438 X_LAST_UPDATE_LOGIN := 0;
1439 elsif (X_MODE = 'R') then
1440 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1441 if X_LAST_UPDATED_BY is NULL then
1442 X_LAST_UPDATED_BY := -1;
1443 end if;
1444 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1445 if X_LAST_UPDATE_LOGIN is NULL then
1446 X_LAST_UPDATE_LOGIN := -1;
1447 end if;
1448 else
1449 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1450 IGS_GE_MSG_STACK.ADD;
1451 app_exception.raise_exception;
1452 end if;
1453
1454 Before_DML(
1455 p_action => 'UPDATE' ,
1456 x_rowid => x_rowid,
1457 x_unit_set_cd => x_unit_set_cd ,
1458 x_version_number => x_version_number ,
1459 x_unit_set_status => x_unit_set_status ,
1460 x_unit_set_cat => x_unit_set_cat ,
1461 x_start_dt => x_start_dt,
1462 x_review_dt => x_review_dt ,
1463 x_expiry_dt => x_expiry_dt ,
1464 x_end_dt => x_end_dt ,
1465 x_title => x_title ,
1466 x_short_title => x_short_title ,
1467 x_abbreviation => x_abbreviation ,
1468 x_responsible_org_unit_cd => x_responsible_org_unit_cd ,
1469 x_responsible_ou_start_dt => x_responsible_ou_start_dt ,
1470 x_administrative_ind => x_administrative_ind ,
1471 x_authorisation_rqrd_ind => x_authorisation_rqrd_ind ,
1472 x_attribute_category => x_attribute_category,
1473 x_attribute1 => x_attribute1,
1474 x_attribute2 => x_attribute2,
1475 x_attribute3 => x_attribute3,
1476 x_attribute4 => x_attribute4,
1477 x_attribute5 => x_attribute5,
1478 x_attribute6 => x_attribute6,
1479 x_attribute7 => x_attribute7,
1480 x_attribute8 => x_attribute8,
1481 x_attribute9 => x_attribute9,
1482 x_attribute10 => x_attribute10,
1483 x_attribute11 => x_attribute11,
1484 x_attribute12 => x_attribute12,
1485 x_attribute13 => x_attribute13,
1486 x_attribute14 => x_attribute14,
1487 x_attribute15 => x_attribute15,
1488 x_attribute16 => x_attribute16,
1489 x_attribute17 => x_attribute17,
1490 x_attribute18 => x_attribute18,
1491 x_attribute19 => x_attribute19,
1492 x_attribute20 => x_attribute20,
1493 x_creation_date => x_last_update_date ,
1494 x_created_by => x_last_updated_by ,
1495 x_last_update_date => x_last_update_date,
1496 x_last_updated_by => x_last_updated_by ,
1497 x_last_update_login => x_last_update_login
1498 );
1499
1500 update IGS_EN_UNIT_SET_ALL set
1501 UNIT_SET_STATUS = NEW_REFERENCES.UNIT_SET_STATUS,
1502 UNIT_SET_CAT = NEW_REFERENCES.UNIT_SET_CAT,
1503 START_DT = NEW_REFERENCES.START_DT,
1504 REVIEW_DT = NEW_REFERENCES.REVIEW_DT,
1505 EXPIRY_DT = NEW_REFERENCES.EXPIRY_DT,
1506 END_DT = NEW_REFERENCES.END_DT,
1507 TITLE = NEW_REFERENCES.TITLE,
1508 SHORT_TITLE = NEW_REFERENCES.SHORT_TITLE,
1509 ABBREVIATION = NEW_REFERENCES.ABBREVIATION,
1510 RESPONSIBLE_ORG_UNIT_CD = NEW_REFERENCES.RESPONSIBLE_ORG_UNIT_CD,
1511 RESPONSIBLE_OU_START_DT = NEW_REFERENCES.RESPONSIBLE_OU_START_DT,
1512 ADMINISTRATIVE_IND = NEW_REFERENCES.ADMINISTRATIVE_IND,
1513 AUTHORISATION_RQRD_IND = NEW_REFERENCES.AUTHORISATION_RQRD_IND,
1514 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1515 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1516 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1517 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1518 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1519 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1520 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1521 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1522 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1523 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1524 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1525 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1526 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1527 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1528 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1529 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1530 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1531 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1532 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1533 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1534 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1535 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1536 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1537 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1538
1539 where ROWID = X_ROWID
1540 ;
1541 if (sql%notfound) then
1542 raise no_data_found;
1543 end if;
1544
1545 After_DML(
1546 p_action => 'UPDATE',
1547 x_rowid => X_ROWID
1548 );
1549
1550 end UPDATE_ROW;
1551
1552 procedure ADD_ROW (
1553 X_ROWID in out NOCOPY VARCHAR2,
1554 X_UNIT_SET_CD in VARCHAR2,
1555 X_VERSION_NUMBER in NUMBER,
1556 X_UNIT_SET_STATUS in VARCHAR2,
1557 X_UNIT_SET_CAT in VARCHAR2,
1558 X_START_DT in DATE,
1559 X_REVIEW_DT in DATE,
1560 X_EXPIRY_DT in DATE,
1561 X_END_DT in DATE,
1562 X_TITLE in VARCHAR2,
1563 X_SHORT_TITLE in VARCHAR2,
1564 X_ABBREVIATION in VARCHAR2,
1565 X_RESPONSIBLE_ORG_UNIT_CD in VARCHAR2,
1566 X_RESPONSIBLE_OU_START_DT in DATE,
1567 X_ADMINISTRATIVE_IND in VARCHAR2,
1568 X_AUTHORISATION_RQRD_IND in VARCHAR2,
1569 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
1570 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
1571 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
1572 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
1573 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
1574 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
1575 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
1576 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
1577 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
1578 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
1579 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
1580 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
1581 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
1582 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
1583 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
1584 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
1585 X_ATTRIBUTE16 in VARCHAR2 DEFAULT NULL,
1586 X_ATTRIBUTE17 in VARCHAR2 DEFAULT NULL,
1587 X_ATTRIBUTE18 in VARCHAR2 DEFAULT NULL,
1588 X_ATTRIBUTE19 in VARCHAR2 DEFAULT NULL,
1589 X_ATTRIBUTE20 in VARCHAR2 DEFAULT NULL,
1590 X_MODE in VARCHAR2 default 'R',
1591 x_org_id IN NUMBER
1592 ) as
1593 cursor c1 is select rowid from IGS_EN_UNIT_SET_ALL
1594 where UNIT_SET_CD = X_UNIT_SET_CD
1595 and VERSION_NUMBER = X_VERSION_NUMBER;
1596
1597 begin
1598 open c1;
1599 fetch c1 into X_ROWID;
1600 if (c1%notfound) then
1601 close c1;
1602 INSERT_ROW (
1603 X_ROWID,
1604 X_UNIT_SET_CD,
1605 X_VERSION_NUMBER,
1606 X_UNIT_SET_STATUS,
1607 X_UNIT_SET_CAT,
1608 X_START_DT,
1609 X_REVIEW_DT,
1610 X_EXPIRY_DT,
1611 X_END_DT,
1612 X_TITLE,
1613 X_SHORT_TITLE,
1614 X_ABBREVIATION,
1615 X_RESPONSIBLE_ORG_UNIT_CD,
1616 X_RESPONSIBLE_OU_START_DT,
1617 X_ADMINISTRATIVE_IND,
1618 X_AUTHORISATION_RQRD_IND,
1619 X_ATTRIBUTE_CATEGORY ,
1620 X_ATTRIBUTE1 ,
1621 X_ATTRIBUTE2 ,
1622 X_ATTRIBUTE3 ,
1623 X_ATTRIBUTE4 ,
1624 X_ATTRIBUTE5 ,
1625 X_ATTRIBUTE6 ,
1626 X_ATTRIBUTE7 ,
1627 X_ATTRIBUTE8 ,
1628 X_ATTRIBUTE9 ,
1629 X_ATTRIBUTE10,
1630 X_ATTRIBUTE11 ,
1631 X_ATTRIBUTE12 ,
1632 X_ATTRIBUTE13 ,
1633 X_ATTRIBUTE14 ,
1634 X_ATTRIBUTE15 ,
1635 X_ATTRIBUTE16 ,
1636 X_ATTRIBUTE17 ,
1637 X_ATTRIBUTE18 ,
1638 X_ATTRIBUTE19 ,
1639 X_ATTRIBUTE20 ,
1640 X_MODE,
1641 x_org_id);
1642 return;
1643 end if;
1644 close c1;
1645 UPDATE_ROW (
1646 X_ROWID,
1647 X_UNIT_SET_CD,
1648 X_VERSION_NUMBER,
1649 X_UNIT_SET_STATUS,
1650 X_UNIT_SET_CAT,
1651 X_START_DT,
1652 X_REVIEW_DT,
1653 X_EXPIRY_DT,
1654 X_END_DT,
1655 X_TITLE,
1656 X_SHORT_TITLE,
1657 X_ABBREVIATION,
1658 X_RESPONSIBLE_ORG_UNIT_CD,
1659 X_RESPONSIBLE_OU_START_DT,
1660 X_ADMINISTRATIVE_IND,
1661 X_AUTHORISATION_RQRD_IND,
1662 X_ATTRIBUTE_CATEGORY ,
1663 X_ATTRIBUTE1 ,
1664 X_ATTRIBUTE2 ,
1665 X_ATTRIBUTE3 ,
1666 X_ATTRIBUTE4 ,
1667 X_ATTRIBUTE5 ,
1668 X_ATTRIBUTE6 ,
1669 X_ATTRIBUTE7 ,
1670 X_ATTRIBUTE8 ,
1671 X_ATTRIBUTE9 ,
1672 X_ATTRIBUTE10,
1673 X_ATTRIBUTE11 ,
1674 X_ATTRIBUTE12 ,
1675 X_ATTRIBUTE13 ,
1676 X_ATTRIBUTE14 ,
1677 X_ATTRIBUTE15 ,
1678 X_ATTRIBUTE16 ,
1679 X_ATTRIBUTE17 ,
1680 X_ATTRIBUTE18 ,
1681 X_ATTRIBUTE19 ,
1682 X_ATTRIBUTE20 ,
1683 X_MODE);
1684 end ADD_ROW;
1685
1686 procedure DELETE_ROW (
1687 X_ROWID IN VARCHAR2
1688 ) as
1689 begin
1690 Before_DML(
1691 p_action => 'DELETE',
1692 x_rowid => X_ROWID
1693 );
1694 delete from IGS_EN_UNIT_SET_ALL
1695 where ROWID = X_ROWID;
1696 if (sql%notfound) then
1697 raise no_data_found;
1698 end if;
1699
1700 After_DML(
1701 p_action => 'DELETE',
1702 x_rowid => X_ROWID
1703 );
1704 end DELETE_ROW;
1705
1706 end IGS_EN_UNIT_SET_PKG;