1 PACKAGE BODY IEX_STRATEGY_WORK_ITEMS_PKG as
2 /* $Header: iextswib.pls 120.0.12010000.2 2008/08/06 09:03:42 schekuri ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_STRATEGY_WORK_ITEMS_PKG';
4 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iextswib.pls';
5
6 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
7 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
8
9 PROCEDURE Insert_Row(
10 X_ROWID IN OUT NOCOPY VARCHAR2
11 ,x_WORK_ITEM_ID IN NUMBER
12 ,x_STRATEGY_ID IN NUMBER
13 ,x_work_item_template_id IN NUMBER
14 ,x_RESOURCE_ID IN NUMBER
15 ,x_STATUS_CODE IN VARCHAR2
16 ,x_execute_start IN DATE
17 ,x_execute_end IN DATE
18 ,x_LAST_UPDATE_LOGIN IN NUMBER
19 ,x_CREATION_DATE IN DATE
20 ,x_CREATED_BY IN NUMBER
21 ,x_LAST_UPDATE_DATE DATE
22 ,x_last_updated_by IN NUMBER
23 ,x_OBJECT_VERSION_NUMBER IN NUMBER
24 ,X_REQUEST_ID in NUMBER
25 ,X_PROGRAM_APPLICATION_ID in NUMBER
26 ,X_PROGRAM_ID in NUMBER
27 ,X_PROGRAM_UPDATE_DATE in DATE
28 ,x_schedule_start in DATE
29 ,x_schedule_end in DATE
30 ,x_strategy_temp_id in NUMBER
31 ,x_work_item_order in NUMBER
32 ,x_escalated_yn in CHAR
33 )
34
35
36 IS
37 cursor C is select ROWID from IEX_STRATEGY_WORK_ITEMS
38 where WORK_ITEM_ID = X_WORK_ITEM_ID ;
39
40 BEGIN
41 -- IF PG_DEBUG < 10 THEN
42 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
43 IEX_DEBUG_PUB.LogMessage ('********* Start of Procedure =>IEX_STRATEGY_WORK_ITEMS_PKG.INSERT_ROW ******** ');
44 END IF;
45
46 INSERT INTO IEX_STRATEGY_WORK_ITEMS(
47 WORK_ITEM_ID
48 ,STRATEGY_ID
49 ,RESOURCE_ID
50 ,STATUS_CODE
51 ,LAST_UPDATED_BY
52 ,LAST_UPDATE_LOGIN
53 ,CREATION_DATE
54 ,CREATED_BY
55 ,PROGRAM_ID
56 ,OBJECT_VERSION_NUMBER
57 ,REQUEST_ID
58 ,LAST_UPDATE_DATE
59 ,WORK_ITEM_TEMPLATE_ID
60 ,PROGRAM_APPLICATION_ID
61 ,PROGRAM_UPDATE_DATE
62 ,execute_start
63 ,execute_end
64 ,schedule_start
65 ,schedule_end
66 ,strategy_temp_id
67 ,work_item_order
68 ,escalated_yn
69 )
70 VALUES (
71 x_WORK_ITEM_ID
72 ,x_STRATEGY_ID
73 ,x_RESOURCE_ID
74 ,decode( x_STATUS_CODE, FND_API.G_MISS_CHAR, NULL, x_STATUS_CODE)
75 ,decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, x_LAST_UPDATED_BY)
76 ,decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, x_LAST_UPDATE_LOGIN)
77 ,decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), x_CREATION_DATE)
78 ,decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL, x_CREATED_BY)
79 ,decode( x_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, x_PROGRAM_ID)
80 ,decode( x_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, x_OBJECT_VERSION_NUMBER)
81 ,decode( x_REQUEST_ID, FND_API.G_MISS_NUM, NULL, x_REQUEST_ID)
82 ,decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), x_LAST_UPDATE_DATE)
83 ,decode( x_WORK_ITEM_TEMPLATE_ID, FND_API.G_MISS_NUM, NULL, x_WORK_ITEM_TEMPLATE_ID)
84 ,decode( x_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, x_PROGRAM_APPLICATION_ID)
85 ,decode( x_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, to_date(null), x_PROGRAM_UPDATE_DATE)
86 ,decode( x_execute_start, FND_API.G_MISS_DATE, to_date(null), x_execute_start)
87 ,decode( x_execute_end, FND_API.G_MISS_DATE, to_date(null), x_execute_end)
88 ,decode( x_schedule_start, FND_API.G_MISS_DATE, to_date(null), x_schedule_start )
89 ,decode( x_schedule_end , FND_API.G_MISS_DATE, to_date(null), x_schedule_end)
90 ,decode( x_strategy_temp_id, FND_API.G_MISS_NUM, NULL, x_strategy_temp_id)
91 ,decode( x_WORK_ITEM_ORDER, FND_API.G_MISS_NUM, NULL, x_WORK_ITEM_ORDER)
92 ,decode( x_escalated_yn, FND_API.G_MISS_CHAR, NULL, x_escalated_yn)
93 );
94 open c;
95 fetch c into X_ROWID;
96 -- IF PG_DEBUG < 10 THEN
97 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
98 IEX_DEBUG_PUB.LogMessage ('Insert_Row: ' || 'Value of ROWID = '||X_ROWID);
99 END IF;
100 if (c%notfound) then
101 close c;
102 raise no_data_found;
103 end if;
104 close c;
105
106 -- IF PG_DEBUG < 10 THEN
107 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
108 IEX_DEBUG_PUB.LogMessage ('********* end of Procedure =>IEX_STRATEGY_WORK_ITEMS_PKG.INSERT_ROW ******** ');
109 END IF;
110 End Insert_Row;
111
112 PROCEDURE Update_Row(
113 x_WORK_ITEM_ID IN NUMBER
114 ,x_STRATEGY_ID IN NUMBER
115 ,x_work_item_template_id IN NUMBER
116 ,x_RESOURCE_ID IN NUMBER
117 ,x_STATUS_CODE IN VARCHAR2
118 ,x_execute_start IN DATE
119 ,x_execute_end IN DATE
120 ,x_LAST_UPDATE_LOGIN IN NUMBER
121 ,x_LAST_UPDATE_DATE DATE
122 ,x_last_updated_by IN NUMBER
123 ,x_OBJECT_VERSION_NUMBER IN NUMBER
124 ,X_REQUEST_ID in NUMBER
125 ,X_PROGRAM_APPLICATION_ID in NUMBER
126 ,X_PROGRAM_ID in NUMBER
127 ,X_PROGRAM_UPDATE_DATE in DATE
128 ,x_schedule_start in DATE
129 ,x_schedule_end in DATE
130 ,x_strategy_temp_id in NUMBER
131 ,x_work_item_order in NUMBER
132 ,x_escalated_yn in CHAR
133 )
134 IS
135 BEGIN
136 -- IF PG_DEBUG < 10 THEN
137 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
138 IEX_DEBUG_PUB.LogMessage ('********* start of Procedure =>IEX_STRATEGY_WORK_ITEMS_PKG.UPDATE_ROW ******** ');
139 END IF;
140 Update IEX_STRATEGY_WORK_ITEMS
141 SET
142 STRATEGY_ID = decode( x_STRATEGY_ID, FND_API.G_MISS_NUM, STRATEGY_ID, x_STRATEGY_ID)
143 ,RESOURCE_ID = decode( x_RESOURCE_ID, FND_API.G_MISS_NUM, RESOURCE_ID, x_RESOURCE_ID)
144 ,STATUS_CODE = decode( x_STATUS_CODE, FND_API.G_MISS_CHAR, STATUS_CODE, x_STATUS_CODE)
145 ,LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, x_LAST_UPDATED_BY)
146 ,LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, x_LAST_UPDATE_LOGIN)
147 ,PROGRAM_ID = decode( x_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, x_PROGRAM_ID)
148 ,OBJECT_VERSION_NUMBER = decode( x_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, x_OBJECT_VERSION_NUMBER)
149 ,REQUEST_ID = decode( x_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, x_REQUEST_ID)
150 ,LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, x_LAST_UPDATE_DATE)
151 ,WORK_ITEM_TEMPLATE_ID = decode( x_WORK_ITEM_TEMPLATE_ID, FND_API.G_MISS_NUM, WORK_ITEM_TEMPLATE_ID, x_WORK_ITEM_TEMPLATE_ID)
152 ,PROGRAM_APPLICATION_ID = decode( x_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, x_PROGRAM_APPLICATION_ID)
153 ,PROGRAM_UPDATE_DATE = decode( x_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, x_PROGRAM_UPDATE_DATE)
154 ,execute_start = decode( x_execute_start, FND_API.G_MISS_DATE, execute_start, x_execute_start)
155 ,execute_end = decode( x_execute_end, FND_API.G_MISS_DATE, execute_end, x_execute_end)
156 ,schedule_start =decode( x_schedule_start, FND_API.G_MISS_DATE, schedule_start, x_schedule_start)
157 ,schedule_end =decode( x_schedule_end, FND_API.G_MISS_DATE, schedule_end, x_schedule_end)
158 ,strategy_temp_id = decode( x_strategy_temp_id , FND_API.G_MISS_NUM, strategy_temp_id , x_strategy_temp_id )
159 ,WORK_ITEM_ORDER = decode( x_WORK_ITEM_ORDER, FND_API.G_MISS_NUM, WORK_ITEM_ORDER, x_WORK_ITEM_ORDER)
160 ,escalated_yn = decode( x_escalated_yn, FND_API.G_MISS_CHAR, escalated_yn, x_escalated_yn)
161 where WORK_ITEM_ID = x_WORK_ITEM_ID;
162
163 If (SQL%NOTFOUND) then
164 RAISE NO_DATA_FOUND;
165 End If;
166 -- IF PG_DEBUG < 10 THEN
167 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
168 IEX_DEBUG_PUB.LogMessage ('********* end of Procedure =>IEX_STRATEGY_WORK_ITEMS_PKG.UPDATE_ROW ******** ');
169 END IF;
170
171 END Update_Row;
172
173 PROCEDURE Delete_Row(
174 x_WORK_ITEM_ID NUMBER)
175 IS
176 BEGIN
177 DELETE FROM IEX_STRATEGY_WORK_ITEMS
178 WHERE WORK_ITEM_ID = x_WORK_ITEM_ID;
179 If (SQL%NOTFOUND) then
180 RAISE NO_DATA_FOUND;
181 End If;
182 END Delete_Row;
183
184 /*
185 PROCEDURE Lock_Row(
186 x_WORK_ITEM_ID IN NUMBER
187 ,x_STRATEGY_ID IN NUMBER
188 ,x_work_item_template_id IN NUMBER
189 ,x_RESOURCE_ID IN NUMBER
190 ,x_STATUS_CODE IN VARCHAR2
191 ,x_execute_start IN DATE
192 ,x_execute_end IN DATE
193 ,x_LAST_UPDATE_LOGIN IN NUMBER
194 ,x_CREATION_DATE IN DATE
195 ,x_CREATED_BY IN NUMBER
196 ,x_LAST_UPDATE_DATE DATE
197 ,x_last_updated_by IN NUMBER
198 ,x_OBJECT_VERSION_NUMBER IN NUMBER
199 ,X_REQUEST_ID in NUMBER
200 ,X_PROGRAM_APPLICATION_ID in NUMBER
201 ,X_PROGRAM_ID in NUMBER
202 ,X_PROGRAM_UPDATE_DATE in DATE
203 ,x_schedule_start in DATE
204 ,x_schedule_end in DATE
205 ,x_strategy_temp_id in NUMBER
206 ,x_work_item_order in NUMBER
207 )
208
209 IS
210 CURSOR C IS
211 SELECT *
212 FROM IEX_STRATEGY_WORK_ITEMS
213 WHERE WORK_ITEM_ID = x_WORK_ITEM_ID
214 FOR UPDATE of WORK_ITEM_ID NOWAIT;
215 Recinfo C%ROWTYPE;
216 BEGIN
217 OPEN C;
218 FETCH C INTO Recinfo;
219 If (C%NOTFOUND) then
220 CLOSE C;
221 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
222 APP_EXCEPTION.RAISE_EXCEPTION;
223 End If;
224 CLOSE C;
225 if (
226 ( Recinfo.WORK_ITEM_ID = p_WORK_ITEM_ID)
227 AND ( ( Recinfo.STRATEGY_ID = p_STRATEGY_ID)
228 OR ( ( Recinfo.STRATEGY_ID IS NULL )
229 AND ( p_STRATEGY_ID IS NULL )))
230 AND ( ( Recinfo.COMPETENCE_ID = p_COMPETENCE_ID)
231 OR ( ( Recinfo.COMPETENCE_ID IS NULL )
232 AND ( p_COMPETENCE_ID IS NULL )))
233 AND ( ( Recinfo.CATEGORY_TYPE = p_CATEGORY_TYPE)
234 OR ( ( Recinfo.CATEGORY_TYPE IS NULL )
235 AND ( p_CATEGORY_TYPE IS NULL )))
236 AND ( ( Recinfo.RESOURCE_ID = p_RESOURCE_ID)
237 OR ( ( Recinfo.RESOURCE_ID IS NULL )
238 AND ( p_RESOURCE_ID IS NULL )))
239 AND ( ( Recinfo.REQUIRED_YN = p_REQUIRED_YN)
240 OR ( ( Recinfo.REQUIRED_YN IS NULL )
241 AND ( p_REQUIRED_YN IS NULL )))
242 AND ( ( Recinfo.STATUS_CODE = p_STATUS_CODE)
243 OR ( ( Recinfo.STATUS_CODE IS NULL )
244 AND ( p_STATUS_CODE IS NULL )))
245 AND ( ( Recinfo.PRIORITY_ID = p_PRIORITY_ID)
246 OR ( ( Recinfo.PRIORITY_ID IS NULL )
247 AND ( p_PRIORITY_ID IS NULL )))
248 AND ( ( Recinfo.PRE_EXECUTION_WAIT = p_PRE_EXECUTION_WAIT)
249 OR ( ( Recinfo.PRE_EXECUTION_WAIT IS NULL )
250 AND ( p_PRE_EXECUTION_WAIT IS NULL )))
251 AND ( ( Recinfo.POST_EXECUTION_WAIT = p_POST_EXECUTION_WAIT)
252 OR ( ( Recinfo.POST_EXECUTION_WAIT IS NULL )
253 AND ( p_POST_EXECUTION_WAIT IS NULL )))
254 AND ( ( Recinfo.CLOSURE_DATE_LIMIT = p_CLOSURE_DATE_LIMIT)
255 OR ( ( Recinfo.CLOSURE_DATE_LIMIT IS NULL )
256 AND ( p_CLOSURE_DATE_LIMIT IS NULL )))
257 AND ( ( Recinfo.EXECUTE_DATE_LIMIT = p_EXECUTE_DATE_LIMIT)
258 OR ( ( Recinfo.EXECUTE_DATE_LIMIT IS NULL )
259 AND ( p_EXECUTE_DATE_LIMIT IS NULL )))
260 AND ( ( Recinfo.SEEDED_WORKFLOW_YN = p_SEEDED_WORKFLOW_YN)
261 OR ( ( Recinfo.SEEDED_WORKFLOW_YN IS NULL )
262 AND ( p_SEEDED_WORKFLOW_YN IS NULL )))
263 AND ( ( Recinfo.WORKFLOW_ITEM_TYPE = p_WORKFLOW_ITEM_TYPE)
264 OR ( ( Recinfo.WORKFLOW_ITEM_TYPE IS NULL )
265 AND ( p_WORKFLOW_ITEM_TYPE IS NULL )))
266 AND ( ( Recinfo.WORKFLOW_PROCESS_NAME = p_WORKFLOW_PROCESS_NAME)
267 OR ( ( Recinfo.WORKFLOW_PROCESS_NAME IS NULL )
268 AND ( p_WORKFLOW_PROCESS_NAME IS NULL )))
269 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
270 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
271 AND ( p_LAST_UPDATED_BY IS NULL )))
272 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
273 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
274 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
275 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
276 OR ( ( Recinfo.CREATION_DATE IS NULL )
277 AND ( p_CREATION_DATE IS NULL )))
278 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
279 OR ( ( Recinfo.CREATED_BY IS NULL )
280 AND ( p_CREATED_BY IS NULL )))
281 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
282 OR ( ( Recinfo.PROGRAM_ID IS NULL )
283 AND ( p_PROGRAM_ID IS NULL )))
284 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
285 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
286 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
287 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
288 OR ( ( Recinfo.REQUEST_ID IS NULL )
289 AND ( p_REQUEST_ID IS NULL )))
290 AND ( ( Recinfo.WORK_TYPE = p_WORK_TYPE)
291 OR ( ( Recinfo.WORK_TYPE IS NULL )
292 AND ( p_WORK_TYPE IS NULL )))
293 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
294 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
295 AND ( p_LAST_UPDATE_DATE IS NULL )))
296 AND ( ( Recinfo.WORK_ITEM_TEMPLATE_ID = p_WORK_ITEM_TEMPLATE_ID)
297 OR ( ( Recinfo.WORK_ITEM_TEMPLATE_ID IS NULL )
298 AND ( p_WORK_ITEM_TEMPLATE_ID IS NULL )))
299 ) then
300 return;
301 else
302 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
303 APP_EXCEPTION.RAISE_EXCEPTION;
304 End If;
305 END Lock_Row;
306 */
307
308 procedure LOCK_ROW (
309 x_WORK_ITEM_ID in NUMBER,
310 X_OBJECT_VERSION_NUMBER in NUMBER)
311 is
312 cursor c is select OBJECT_VERSION_NUMBER
313 from IEX_STRATEGY_WORK_ITEMS
314 where WORK_ITEM_ID = X_WORK_ITEM_ID
315 and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
316 for update of WORK_ITEM_ID nowait;
317 recinfo c%rowtype;
318
319
320 begin
321 -- IF PG_DEBUG < 10 THEN
322 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
323 IEX_DEBUG_PUB.LogMessage ('********* Start of Procedure =>IEX_STRATEGY_WORK_ITEMS_PKG.LOCK_ROW ******** ');
324 END IF;
325 open c;
326 fetch c into recinfo;
327 if (c%notfound) then
328 close c;
329 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
330 app_exception.raise_exception;
331 end if;
332
333 close c;
334
335 if recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER then
336 null;
337 else
338 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
339 app_exception.raise_exception;
340 end if;
341 --IF PG_DEBUG < 10 THEN
342 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
343 IEX_DEBUG_PUB.LogMessage ('********* End of Procedure =>IEX_STRATEGY_WORK_ITEMS_PKG.LOCK_ROW ******** ');
344 END IF;
345 end LOCK_ROW;
346
347 End IEX_STRATEGY_WORK_ITEMS_PKG;
[Home] [Help]