1 PACKAGE BODY IEX_DEL_STATUSES_PKG AS
2 /* $Header: iextdlsb.pls 120.1 2005/07/12 20:35:40 jsanju noship $ */
3 /* Insert_Row procedure */
4 PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
5
6 PROCEDURE Insert_Row(x_rowid IN OUT NOCOPY VARCHAR2
7 ,p_DEL_STATUS_ID NUMBER
8 ,p_OBJECT_VERSION_NUMBER NUMBER
9 ,p_SCORE_VALUE_LOW NUMBER
10 ,p_SCORE_VALUE_HIGH NUMBER
11 ,p_DEL_STATUS VARCHAR2
12 ,p_SCORE_ID NUMBER
13 ,p_REQUEST_ID NUMBER DEFAULT NULL
14 ,p_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
15 ,p_PROGRAM_ID NUMBER DEFAULT NULL
16 ,p_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
17 ,p_CREATED_BY NUMBER
18 ,p_CREATION_DATE DATE
19 ,p_LAST_UPDATED_BY NUMBER
20 ,p_LAST_UPDATE_DATE DATE
21 ,p_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL
22 ) IS
23 CURSOR C IS SELECT ROWID FROM IEX_DEL_STATUSES
24 WHERE DEL_STATUS_ID = p_DEL_STATUS_ID;
25
26 BEGIN
27 INSERT INTO IEX_DEL_STATUSES
28 (
29 DEL_STATUS_ID
30 ,OBJECT_VERSION_NUMBER
31 ,SCORE_VALUE_LOW
32 ,SCORE_VALUE_HIGH
33 ,DEL_STATUS
34 ,SCORE_ID
35 ,REQUEST_ID
36 ,PROGRAM_APPLICATION_ID
37 ,PROGRAM_ID
38 ,PROGRAM_UPDATE_DATE
39 ,CREATED_BY
40 ,CREATION_DATE
41 ,LAST_UPDATED_BY
42 ,LAST_UPDATE_DATE
43 ,LAST_UPDATE_LOGIN
44 ) VALUES (
45 p_DEL_STATUS_ID
46 ,p_OBJECT_VERSION_NUMBER
47 ,p_SCORE_VALUE_LOW
48 ,p_SCORE_VALUE_HIGH
49 ,p_DEL_STATUS
50 ,p_SCORE_ID
51 ,p_REQUEST_ID
52 ,p_PROGRAM_APPLICATION_ID
53 ,p_PROGRAM_ID
54 ,p_PROGRAM_UPDATE_DATE
55 ,p_CREATED_BY
56 ,p_CREATION_DATE
57 ,p_LAST_UPDATED_BY
58 ,p_LAST_UPDATE_DATE
59 ,p_LAST_UPDATE_LOGIN
60 );
61
62 OPEN C;
63 FETCH C INTO x_rowid;
64 IF (C%NOTFOUND) THEN
65 CLOSE C;
66 RAISE NO_DATA_FOUND;
67 END IF;
68 CLOSE C;
69 END Insert_Row;
70
71
72
73 /* Insert_Row procedure */
74 PROCEDURE Insert_Row_With_Defaults
75 (x_rowid IN OUT NOCOPY VARCHAR2
76 ,p_SCORE_VALUE_LOW NUMBER
77 ,p_SCORE_VALUE_HIGH NUMBER
78 ,p_DEL_STATUS VARCHAR2
79 ,p_SCORE_ID NUMBER
80 ,Commit_flag VARCHAR2
81 ) IS
82 CURSOR C(p_del_status_id number)
83 IS SELECT ROWID FROM IEX_DEL_STATUSES
84 WHERE DEL_STATUS_ID = p_DEL_STATUS_ID;
85
86 l_del_status_id Number ;
87
88 BEGIN
89 -- Slightly modified since a lot of default values will be provided
90
91 Select IEX_DEL_STATUSES_S.nextval
92 into l_del_status_id
93 From dual ;
94
95 INSERT INTO IEX_DEL_STATUSES
96 (
97 DEL_STATUS_ID
98 ,OBJECT_VERSION_NUMBER
99 ,SCORE_VALUE_LOW
100 ,SCORE_VALUE_HIGH
101 ,DEL_STATUS
102 ,SCORE_ID
103 ,REQUEST_ID
104 ,PROGRAM_APPLICATION_ID
105 ,PROGRAM_ID
106 ,PROGRAM_UPDATE_DATE
107 ,CREATED_BY
108 ,CREATION_DATE
109 ,LAST_UPDATED_BY
110 ,LAST_UPDATE_DATE
111 ,LAST_UPDATE_LOGIN
112 ) VALUES (
113 l_del_status_id
114 ,1
115 ,p_SCORE_VALUE_LOW
116 ,p_SCORE_VALUE_HIGH
117 ,p_DEL_STATUS
118 ,p_SCORE_ID
119 ,NULL
120 ,NULL
121 ,NULL
122 ,NULL
123 ,-1
124 ,SYSDATE
125 ,-1
126 ,SYSDATE
127 ,-1
128 );
129
130 OPEN C(l_del_status_id) ;
131 FETCH C INTO x_rowid;
132 IF (C%NOTFOUND) THEN
133 CLOSE C;
134 ROLLBACK ;
135 RAISE NO_DATA_FOUND;
136 CLOSE C;
137 return ;
138 END IF;
139 close C ;
140
141 if commit_flag = 'Y' then
142 Commit ;
143 End If ;
144
145 END Insert_Row_With_Defaults;
146
147
148 /* Update_Row procedure */
149 PROCEDURE Update_Row(x_rowid VARCHAR2
150 ,p_DEL_STATUS_ID NUMBER
151 ,p_OBJECT_VERSION_NUMBER NUMBER
152 ,p_SCORE_VALUE_LOW NUMBER
153 ,p_SCORE_VALUE_HIGH NUMBER
154 ,p_DEL_STATUS VARCHAR2
155 ,p_SCORE_ID NUMBER
156 ,p_REQUEST_ID NUMBER DEFAULT NULL
157 ,p_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
158 ,p_PROGRAM_ID NUMBER DEFAULT NULL
159 ,p_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
160 ,p_CREATED_BY NUMBER
161 ,p_CREATION_DATE DATE
162 ,p_LAST_UPDATED_BY NUMBER
163 ,p_LAST_UPDATE_DATE DATE
164 ,p_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL
165 ) IS
166 BEGIN
167 UPDATE IEX_DEL_STATUSES SET
168 DEL_STATUS_ID = p_DEL_STATUS_ID
169 ,OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER
170 ,SCORE_VALUE_LOW = p_SCORE_VALUE_LOW
171 ,SCORE_VALUE_HIGH = p_SCORE_VALUE_HIGH
172 ,DEL_STATUS = p_DEL_STATUS
173 ,SCORE_ID = p_SCORE_ID
174 ,REQUEST_ID = p_REQUEST_ID
175 ,PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID
176 ,PROGRAM_ID = p_PROGRAM_ID
177 ,PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE
178 ,CREATED_BY = p_CREATED_BY
179 ,CREATION_DATE = p_CREATION_DATE
180 ,LAST_UPDATED_BY = p_LAST_UPDATED_BY
181 ,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
182 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
183 WHERE rowid = x_rowid;
184
185 if (sql%notfound) then
186 raise no_data_found;
187 end if;
188 END Update_Row;
189
190 /* Delete_Row procedure */
191 PROCEDURE Delete_Row(x_rowid VARCHAR2) IS
192 BEGIN
193 DELETE FROM IEX_DEL_STATUSES
194 WHERE rowid = x_rowid;
195
196 if (sql%notfound) then
197 -- No action required since this is range delete
198 null;
199 end if;
200 END Delete_Row;
201
202
203 /* Delete_Row procedure */
204 PROCEDURE Delete_del_config(p_scoreId NUMBER)
205 IS
206 BEGIN
207 DELETE FROM IEX_DEL_STATUSES
208 WHERE score_id = p_scoreId;
209
210 if (sql%notfound) then
211 -- No action required since this is range delete
212 null;
213 end if;
214 END Delete_del_config ;
215
216
217
218
219 /* Lock_Row procedure */
220 PROCEDURE Lock_Row(x_rowid VARCHAR2
221 ,p_DEL_STATUS_ID NUMBER
222 ,p_OBJECT_VERSION_NUMBER NUMBER
223 ,p_SCORE_VALUE_LOW NUMBER
224 ,p_SCORE_VALUE_HIGH NUMBER
225 ,p_DEL_STATUS VARCHAR2
226 ,p_SCORE_ID NUMBER
227 ,p_REQUEST_ID NUMBER DEFAULT NULL
228 ,p_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
229 ,p_PROGRAM_ID NUMBER DEFAULT NULL
230 ,p_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
231 ,p_CREATED_BY NUMBER
232 ,p_CREATION_DATE DATE
233 ,p_LAST_UPDATED_BY NUMBER
234 ,p_LAST_UPDATE_DATE DATE
235 ,p_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL
236 ) IS
237 CURSOR C IS SELECT * FROM IEX_DEL_STATUSES
238 WHERE rowid = x_rowid
239 FOR UPDATE of DEL_STATUS_ID NOWAIT;
240 Recinfo C%ROWTYPE;
241 BEGIN
242 OPEN C;
243 FETCH C INTO Recinfo;
244 IF (C%NOTFOUND) THEN
245 CLOSE C;
246 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
247 APP_EXCEPTION.Raise_Exception;
248 END IF;
249 CLOSE C;
250
251 IF (
252 (Recinfo.DEL_STATUS_ID = p_DEL_STATUS_ID)
253 AND ( (Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
254 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
255 AND (p_OBJECT_VERSION_NUMBER IS NULL)))
256 AND ( (Recinfo.SCORE_VALUE_LOW = p_SCORE_VALUE_LOW)
257 OR ( (Recinfo.SCORE_VALUE_LOW IS NULL)
258 AND (p_SCORE_VALUE_LOW IS NULL)))
259 AND ( (Recinfo.SCORE_VALUE_HIGH = p_SCORE_VALUE_HIGH)
260 OR ( (Recinfo.SCORE_VALUE_HIGH IS NULL)
261 AND (p_SCORE_VALUE_HIGH IS NULL)))
262 AND ( (Recinfo.DEL_STATUS = p_DEL_STATUS)
263 OR ( (Recinfo.DEL_STATUS IS NULL)
264 AND (p_DEL_STATUS IS NULL)))
265 AND ( (Recinfo.SCORE_ID = p_SCORE_ID)
266 OR ( (Recinfo.SCORE_ID IS NULL)
267 AND (p_SCORE_ID IS NULL)))
268 AND ( (Recinfo.REQUEST_ID = p_REQUEST_ID)
269 OR ( (Recinfo.REQUEST_ID IS NULL)
270 AND (p_REQUEST_ID IS NULL)))
271 AND ( (Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
272 OR ( (Recinfo.PROGRAM_APPLICATION_ID IS NULL)
273 AND (p_PROGRAM_APPLICATION_ID IS NULL)))
274 AND ( (Recinfo.PROGRAM_ID = p_PROGRAM_ID)
275 OR ( (Recinfo.PROGRAM_ID IS NULL)
276 AND (p_PROGRAM_ID IS NULL)))
277 AND ( (Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
278 OR ( (Recinfo.PROGRAM_UPDATE_DATE IS NULL)
279 AND (p_PROGRAM_UPDATE_DATE IS NULL)))
280 AND ( (Recinfo.CREATED_BY = p_CREATED_BY)
281 OR ( (Recinfo.CREATED_BY IS NULL)
282 AND (p_CREATED_BY IS NULL)))
283 AND ( (Recinfo.CREATION_DATE = p_CREATION_DATE)
284 OR ( (Recinfo.CREATION_DATE IS NULL)
285 AND (p_CREATION_DATE IS NULL)))
286 AND ( (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
287 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
288 AND (p_LAST_UPDATED_BY IS NULL)))
289 AND ( (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
290 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
291 AND (p_LAST_UPDATE_DATE IS NULL)))
292 AND ( (Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
293 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
294 AND (p_LAST_UPDATE_LOGIN IS NULL)))
295 ) THEN
296 return;
297 ELSE
298 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
299 APP_EXCEPTION.Raise_Exception;
300 END IF;
301 END Lock_Row;
302 END;
[Home] [Help]