[Home] [Help]
PACKAGE BODY: APPS.IGS_CA_DT_OF_MTH_PKG
Source
1 package body IGS_CA_DT_OF_MTH_PKG AS
2 /* $Header: IGSCI11B.pls 115.4 2002/11/28 23:02:13 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_CA_DT_OF_MTH%RowType;
5 new_references IGS_CA_DT_OF_MTH%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_dt_of_month IN DATE DEFAULT NULL,
11 x_current_user IN VARCHAR2 DEFAULT NULL,
12 x_creation_date IN DATE DEFAULT NULL,
13 x_created_by IN NUMBER DEFAULT NULL,
14 x_last_update_date IN DATE DEFAULT NULL,
15 x_last_updated_by IN NUMBER DEFAULT NULL,
16 x_last_update_login IN NUMBER DEFAULT NULL
17 ) AS
18
19 CURSOR cur_old_ref_values IS
20 SELECT *
21 FROM IGS_CA_DT_OF_MTH
22 WHERE rowid = x_rowid;
23
24 BEGIN
25
26 l_rowid := x_rowid;
27
28 -- Code for setting the Old and New Reference Values.
29 -- Populate Old Values.
30 Open cur_old_ref_values;
31 Fetch cur_old_ref_values INTO old_references;
32 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
33 Close cur_old_ref_values;
34 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
35 IGS_GE_MSG_STACK.ADD;
36 App_Exception.Raise_Exception;
37 Return;
38 END IF;
39 Close cur_old_ref_values;
40
41 -- Populate New Values.
42 new_references.dt_of_month := x_dt_of_month;
43 new_references.current_user := x_current_user;
44 IF (p_action = 'UPDATE') THEN
45 new_references.creation_date := old_references.creation_date;
46 new_references.created_by := old_references.created_by;
47 ELSE
48 new_references.creation_date := x_creation_date;
49 new_references.created_by := x_created_by;
50 END IF;
51 new_references.last_update_date := x_last_update_date;
52 new_references.last_updated_by := x_last_updated_by;
53 new_references.last_update_login := x_last_update_login;
54
55 END Set_Column_Values;
56
57 PROCEDURE Check_Constraints (
58 column_name IN VARCHAR2 DEFAULT NULL,
59 column_value IN VARCHAR2 DEFAULT NULL)
60 AS
61 BEGIN
62
63 IF column_name is null then
64 null;
65 ELSIF upper(column_name) = 'CURRENT_USER' Then
66 new_references.current_user := column_value;
67 End if;
68
69 If upper(Column_name)= 'CURRENT_USER' Or column_name is null then
70 If UPPER(new_references.current_user) <> new_references.current_user Then
71 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
72 IGS_GE_MSG_STACK.ADD;
73 App_Exception.Raise_Exception;
74 End if;
75 End if;
76 END Check_Constraints;
77
78 FUNCTION Get_PK_For_Validation (
79 x_dt_of_month IN DATE,
80 x_current_user IN VARCHAR2
81 ) RETURN BOOLEAN AS
82
83 CURSOR cur_rowid IS
84 SELECT rowid
85 FROM IGS_CA_DT_OF_MTH
86 WHERE dt_of_month = x_dt_of_month
87 AND current_user = x_current_user
88 FOR UPDATE NOWAIT;
89
90 lv_rowid cur_rowid%RowType;
91
92 BEGIN
93
94 Open cur_rowid;
95 Fetch cur_rowid INTO lv_rowid;
96 IF (cur_rowid%FOUND) THEN
97 Close cur_rowid;
98 Return(TRUE);
99 Else
100 Close cur_rowid;
101 Return(FALSE);
102 END IF;
103
104 END Get_PK_For_Validation;
105
106 PROCEDURE Before_DML (
107 p_action IN VARCHAR2,
108 x_rowid IN VARCHAR2 DEFAULT NULL,
109 x_dt_of_month IN DATE DEFAULT NULL,
110 x_current_user IN VARCHAR2 DEFAULT NULL,
111 x_creation_date IN DATE DEFAULT NULL,
112 x_created_by IN NUMBER DEFAULT NULL,
113 x_last_update_date IN DATE DEFAULT NULL,
114 x_last_updated_by IN NUMBER DEFAULT NULL,
115 x_last_update_login IN NUMBER DEFAULT NULL
116 ) AS
117 BEGIN
118
119 Set_Column_Values (
120 p_action,
121 x_rowid,
122 x_dt_of_month,
123 x_current_user,
124 x_creation_date,
125 x_created_by,
126 x_last_update_date,
127 x_last_updated_by,
128 x_last_update_login
129 );
130
131 IF (p_action = 'INSERT') THEN
132 -- Call all the procedures related to Before Insert.
133 if get_pk_for_validation(
134 x_dt_of_month,
135 x_current_user
136 ) Then
137 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
138 IGS_GE_MSG_STACK.ADD;
139 app_exception.raise_exception;
140 end if;
141 check_constraints;
142
143 ELSIF (p_action = 'UPDATE') THEN
144 -- Call all the procedures related to Before Update.
145 Null;
146 Check_Constraints;
147 ELSIF (p_action = 'DELETE') THEN
148 -- Call all the procedures related to Before Delete.
149 Null;
150
151 ELSIF (p_action = 'VALIDATE_INSERT') THEN
152 if get_pk_for_validation(
153 x_dt_of_month ,
154 x_current_user
155 ) Then
156 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
157 IGS_GE_MSG_STACK.ADD;
158 app_exception.raise_exception;
159 end if;
160 check_constraints;
161 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
162 Check_Constraints;
163 ELSIF (p_action = 'VALIDATE_DELETE') THEN
164 null;
165 END IF;
166
167 END Before_DML;
168
169 procedure INSERT_ROW (
170 X_ROWID in out NOCOPY VARCHAR2,
171 X_DT_OF_MONTH in DATE,
172 X_CURRENT_USER in VARCHAR2,
173 X_MODE in VARCHAR2 default 'R'
174 ) AS
175 cursor C is select ROWID from IGS_CA_DT_OF_MTH
176 where DT_OF_MONTH = X_DT_OF_MONTH
177 and CURRENT_USER = X_CURRENT_USER;
178 X_LAST_UPDATE_DATE DATE;
179 X_LAST_UPDATED_BY NUMBER;
180 X_LAST_UPDATE_LOGIN NUMBER;
181 begin
182 X_LAST_UPDATE_DATE := SYSDATE;
183 if(X_MODE = 'I') then
184 X_LAST_UPDATED_BY := 1;
185 X_LAST_UPDATE_LOGIN := 0;
186 elsif (X_MODE = 'R') then
187 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
188 if X_LAST_UPDATED_BY is NULL then
189 X_LAST_UPDATED_BY := -1;
190 end if;
191 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
192 if X_LAST_UPDATE_LOGIN is NULL then
193 X_LAST_UPDATE_LOGIN := -1;
194 end if;
195 else
196 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
197 IGS_GE_MSG_STACK.ADD;
198 app_exception.raise_exception;
199 end if;
200 Before_DML (
201 p_action =>'INSERT',
202 x_rowid =>X_ROWID,
203 x_dt_of_month =>X_DT_OF_MONTH,
204 x_current_user =>X_CURRENT_USER,
205 x_creation_date =>X_LAST_UPDATE_DATE,
206 x_created_by =>X_LAST_UPDATED_BY,
207 x_last_update_date =>X_LAST_UPDATE_DATE,
208 x_last_updated_by =>X_LAST_UPDATED_BY,
209 x_last_update_login =>X_LAST_UPDATE_LOGIN
210 );
211 insert into IGS_CA_DT_OF_MTH (
212 DT_OF_MONTH,
213 CURRENT_USER,
214 CREATION_DATE,
215 CREATED_BY,
216 LAST_UPDATE_DATE,
217 LAST_UPDATED_BY,
218 LAST_UPDATE_LOGIN
219 ) values (
220 NEW_REFERENCES.DT_OF_MONTH,
221 NEW_REFERENCES.CURRENT_USER,
222 X_LAST_UPDATE_DATE,
223 X_LAST_UPDATED_BY,
224 X_LAST_UPDATE_DATE,
225 X_LAST_UPDATED_BY,
226 X_LAST_UPDATE_LOGIN
227 );
228
229 open c;
230 fetch c into X_ROWID;
231 if (c%notfound) then
232 close c;
233 raise no_data_found;
234 end if;
235 close c;
236 end INSERT_ROW;
237
238 procedure LOCK_ROW (
239 X_ROWID in VARCHAR2,
240 X_DT_OF_MONTH in DATE,
241 X_CURRENT_USER in VARCHAR2
242 ) AS
243 cursor c1 is select ROWID
244 from IGS_CA_DT_OF_MTH
245 where ROWID=X_ROWID
246 for update nowait;
247 tlinfo c1%rowtype;
248 begin
249 open c1;
250 fetch c1 into tlinfo;
251 if (c1%notfound) then
252 close c1;
253 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
254 IGS_GE_MSG_STACK.ADD;
255 app_exception.raise_exception;
256 return;
257 end if;
258 close c1;
259 return;
260 end LOCK_ROW;
261
262 procedure DELETE_ROW (
263 X_ROWID in VARCHAR2
264 ) AS
265 begin
266 Before_DML (
267 p_action =>'DELETE',
268 x_rowid =>X_ROWID
269 );
270 delete from IGS_CA_DT_OF_MTH
271 where ROWID=X_ROWID;
272 if (sql%notfound) then
273 raise no_data_found;
274 end if;
275 end DELETE_ROW;
276
277 end IGS_CA_DT_OF_MTH_PKG;