[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_UNIT_PKG
Source
1 package body IGS_PS_UNIT_PKG as
2 /* $Header: IGSPI73B.pls 115.5 2003/02/18 11:01:05 shtatiko ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PS_UNIT%RowType;
5 new_references IGS_PS_UNIT%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_unit_cd IN VARCHAR2 DEFAULT NULL,
11 x_creation_date IN DATE DEFAULT NULL,
12 x_created_by IN NUMBER DEFAULT NULL,
13 x_last_update_date IN DATE DEFAULT NULL,
14 x_last_updated_by IN NUMBER DEFAULT NULL,
15 x_last_update_login IN NUMBER DEFAULT NULL
16 ) AS
17
18 CURSOR cur_old_ref_values IS
19 SELECT *
20 FROM IGS_PS_UNIT
21 WHERE rowid = x_rowid;
22
23 BEGIN
24
25 l_rowid := x_rowid;
26
27 -- Code for setting the Old and New Reference Values.
28 -- Populate Old Values.
29 Open cur_old_ref_values;
30 Fetch cur_old_ref_values INTO old_references;
31 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
32 Close cur_old_ref_values;
33 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
34 IGS_GE_MSG_STACK.ADD;
35 App_Exception.Raise_Exception;
36 Return;
37 END IF;
38 Close cur_old_ref_values;
39
40 -- Populate New Values.
41 new_references.unit_cd := x_unit_cd;
42 IF (p_action = 'UPDATE') THEN
43 new_references.creation_date := old_references.creation_date;
44 new_references.created_by := old_references.created_by;
45 ELSE
46 new_references.creation_date := x_creation_date;
47 new_references.created_by := x_created_by;
48 END IF;
49 new_references.last_update_date := x_last_update_date;
50 new_references.last_updated_by := x_last_updated_by;
51 new_references.last_update_login := x_last_update_login;
52
53 END Set_Column_Values;
54
55 PROCEDURE Check_Child_Existance AS
56 BEGIN
57
58 IGS_PS_PAT_STUDY_UNT_PKG.GET_FK_IGS_PS_UNIT (
59 old_references.unit_cd
60 );
61
62 IGS_PE_PERS_UNT_EXCL_PKG.GET_FK_IGS_PS_UNIT (
63 old_references.unit_cd
64 );
65
66 IGS_PE_UNT_REQUIRMNT_PKG.GET_FK_IGS_PS_UNIT (
67 old_references.unit_cd
68 );
69
70 IGS_PR_OU_UNIT_PKG.GET_FK_IGS_PS_UNIT (
71 old_references.unit_cd
72 );
73
74 IGS_PR_STDNT_PR_UNIT_PKG.GET_FK_IGS_PS_UNIT (
75 old_references.unit_cd
76 );
77
78 IGS_EN_SU_ATTEMPT_PKG.GET_FK_IGS_PS_UNIT (
79 old_references.unit_cd
80 );
81
82 IGS_FI_UNIT_FEE_TRG_PKG.GET_FK_IGS_PS_UNIT (
83 old_references.unit_cd
84 );
85
86 IGS_PS_UNIT_RU_PKG.GET_FK_IGS_PS_UNIT (
87 old_references.unit_cd
88 );
89
90 IGS_PS_UNIT_VER_PKG.GET_FK_IGS_PS_UNIT (
91 old_references.unit_cd
92 );
93
94 END Check_Child_Existance;
95
96 FUNCTION Get_PK_For_Validation (
97 x_unit_cd IN VARCHAR2
98 ) RETURN BOOLEAN AS
99 /***************************************************************************************
100 Change History
101 WHO WHEN WHAT
102 shtatiko 18-FEB-2003 Enh# 2797116, Removed FOR UPDATE NOWAIT clause is removed
103 from the cursor, cur_rowid.
104 ***************************************************************************************/
105
106 CURSOR cur_rowid IS
107 SELECT rowid
108 FROM IGS_PS_UNIT
109 WHERE unit_cd = x_unit_cd;
110
111 lv_rowid cur_rowid%RowType;
112
113 BEGIN
114
115 Open cur_rowid;
116 Fetch cur_rowid INTO lv_rowid;
117 IF (cur_rowid%FOUND) THEN
118 Close cur_rowid;
119 Return(TRUE);
120 ELSE
121 Close cur_rowid;
122 Return(FALSE);
123 END IF;
124
125 END Get_PK_For_Validation;
126
127 PROCEDURE Check_Constraints(
128 Column_Name IN VARCHAR2 DEFAULT NULL,
129 Column_Value IN VARCHAR2 DEFAULT NULL)
130 AS
131 BEGIN
132
133 IF Column_Name IS NULL Then
134 NULL;
135 ELSIF Upper(Column_Name)='UNIT_CD' Then
136 New_References.Unit_Cd := Column_Value;
137 END IF;
138
139 IF Upper(Column_Name)='UNIT_CD' OR Column_Name IS NULL Then
140 IF New_References.Unit_Cd <> UPPER(New_References.Unit_Cd) Then
141 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
142 IGS_GE_MSG_STACK.ADD;
143 App_Exception.Raise_Exception;
144 END IF;
145 END IF;
146
147 END Check_Constraints;
148
149 PROCEDURE Before_DML (
150 p_action IN VARCHAR2,
151 x_rowid IN VARCHAR2 DEFAULT NULL,
152 x_unit_cd IN VARCHAR2 DEFAULT NULL,
153 x_creation_date IN DATE DEFAULT NULL,
154 x_created_by IN NUMBER DEFAULT NULL,
155 x_last_update_date IN DATE DEFAULT NULL,
156 x_last_updated_by IN NUMBER DEFAULT NULL,
157 x_last_update_login IN NUMBER DEFAULT NULL
158 ) AS
159 BEGIN
160
161 Set_Column_Values (
162 p_action,
163 x_rowid,
164 x_unit_cd,
165 x_creation_date,
166 x_created_by,
167 x_last_update_date,
168 x_last_updated_by,
169 x_last_update_login
170 );
171
172 IF (p_action = 'INSERT') THEN
173 -- Call all the procedures related to Before Insert.
174 IF Get_PK_For_Validation (New_References.unit_cd) THEN
175 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
176 IGS_GE_MSG_STACK.ADD;
177 App_Exception.Raise_Exception;
178 END IF;
179 Check_Constraints;
180 ELSIF (p_action = 'UPDATE') THEN
181 -- Call all the procedures related to Before Update.
182 Check_Constraints;
183 ELSIF (p_action = 'DELETE') THEN
184 -- Call all the procedures related to Before Delete.
185 Check_Child_Existance;
186 ELSIF (p_action = 'VALIDATE_INSERT') THEN
187 IF Get_PK_For_Validation (New_References.unit_cd) THEN
188 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
189 IGS_GE_MSG_STACK.ADD;
190 App_Exception.Raise_Exception;
191 END IF;
192 Check_Constraints;
193 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
194 Check_Constraints;
195 ELSIF (p_action = 'VALIDATE_DELETE') THEN
196 Check_Child_Existance;
197 END IF;
198
199 END Before_DML;
200
201 PROCEDURE After_DML (
202 p_action IN VARCHAR2,
203 x_rowid IN VARCHAR2
204 ) AS
205 BEGIN
206
207 l_rowid := x_rowid;
208
209
210 END After_DML;
211
212
213 procedure INSERT_ROW (
214 X_ROWID in out NOCOPY VARCHAR2,
215 X_UNIT_CD in VARCHAR2,
216 X_MODE in VARCHAR2 default 'R'
217 ) AS
218 cursor C is select ROWID from IGS_PS_UNIT
219 where UNIT_CD = X_UNIT_CD;
220 X_LAST_UPDATE_DATE DATE;
221 X_LAST_UPDATED_BY NUMBER;
222 X_LAST_UPDATE_LOGIN NUMBER;
223 begin
224 X_LAST_UPDATE_DATE := SYSDATE;
225 if(X_MODE = 'I') then
226 X_LAST_UPDATED_BY := 1;
227 X_LAST_UPDATE_LOGIN := 0;
228 elsif (X_MODE = 'R') then
229 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
230 if X_LAST_UPDATED_BY is NULL then
231 X_LAST_UPDATED_BY := -1;
232 end if;
233 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
234 if X_LAST_UPDATE_LOGIN is NULL then
235 X_LAST_UPDATE_LOGIN := -1;
236 end if;
237 else
238 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
239 IGS_GE_MSG_STACK.ADD;
240 app_exception.raise_exception;
241 end if;
242 Before_DML(
243 p_action => 'INSERT',
244 x_rowid => X_ROWID,
245 x_unit_cd => X_UNIT_CD,
246 x_creation_date => X_LAST_UPDATE_DATE,
247 x_created_by => X_LAST_UPDATED_BY,
248 x_last_update_date => X_LAST_UPDATE_DATE,
249 x_last_updated_by => X_LAST_UPDATED_BY,
250 x_last_update_login => X_LAST_UPDATE_LOGIN
251 );
252
253 insert into IGS_PS_UNIT (
254 UNIT_CD,
255 CREATION_DATE,
256 CREATED_BY,
257 LAST_UPDATE_DATE,
258 LAST_UPDATED_BY,
259 LAST_UPDATE_LOGIN
260 ) values (
261 NEW_REFERENCES.UNIT_CD,
262 X_LAST_UPDATE_DATE,
263 X_LAST_UPDATED_BY,
264 X_LAST_UPDATE_DATE,
265 X_LAST_UPDATED_BY,
266 X_LAST_UPDATE_LOGIN
267 );
268
269 open c;
270 fetch c into X_ROWID;
271 if (c%notfound) then
272 close c;
273 raise no_data_found;
274 end if;
275 close c;
276 After_DML (
277 p_action => 'INSERT',
278 x_rowid => X_ROWID
279 );
280
281 end INSERT_ROW;
282
283 procedure LOCK_ROW (
284 X_ROWID IN VARCHAR2,
285 X_UNIT_CD in VARCHAR2
286 ) AS
287 cursor c1 is select ROWID
288 from IGS_PS_UNIT
289 where ROWID = X_ROWID
290 for update nowait;
291 tlinfo c1%rowtype;
292
293 begin
294 open c1;
295 fetch c1 into tlinfo;
296 if (c1%notfound) then
297 close c1;
298 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
299 IGS_GE_MSG_STACK.ADD;
300 app_exception.raise_exception;
301 return;
302 end if;
303 close c1;
304
305 return;
306 end LOCK_ROW;
307
308 procedure DELETE_ROW (
309 X_ROWID in VARCHAR2
310 ) AS
311 begin
312 Before_DML(
313 p_action => 'DELETE',
314 x_rowid => X_ROWID
315 );
316 delete from IGS_PS_UNIT
317 where ROWID = X_ROWID;
318 if (sql%notfound) then
319 raise no_data_found;
320 end if;
321 After_DML(
322 p_action => 'DELETE',
323 x_rowid => X_ROWID
324 );
325 end DELETE_ROW;
326
327 end IGS_PS_UNIT_PKG;