DBA Data[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;