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