[Home] [Help]
PACKAGE BODY: APPS.WIP_WIP_ENTITIES_UTIL
Source
1 PACKAGE BODY WIP_Wip_Entities_Util AS
2 /* $Header: WIPUWENB.pls 115.7 2002/12/01 18:13:29 simishra ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WIP_Wip_Entities_Util';
7
8
9 -- Function Complete_Record
10
11 FUNCTION Complete_Record
12 ( p_Wip_Entities_rec IN WIP_Work_Order_PUB.Wip_Entities_Rec_Type
13 , p_old_Wip_Entities_rec IN WIP_Work_Order_PUB.Wip_Entities_Rec_Type
14 ) RETURN WIP_Work_Order_PUB.Wip_Entities_Rec_Type
15 IS
16 l_Wip_Entities_rec WIP_Work_Order_PUB.Wip_Entities_Rec_Type := p_Wip_Entities_rec;
17 BEGIN
18
19 IF l_Wip_Entities_rec.created_by = FND_API.G_MISS_NUM THEN
20 l_Wip_Entities_rec.created_by := p_old_Wip_Entities_rec.created_by;
21 END IF;
22
23 IF l_Wip_Entities_rec.creation_date = FND_API.G_MISS_DATE THEN
24 l_Wip_Entities_rec.creation_date := p_old_Wip_Entities_rec.creation_date;
25 END IF;
26
27 IF l_Wip_Entities_rec.description = FND_API.G_MISS_CHAR THEN
28 l_Wip_Entities_rec.description := p_old_Wip_Entities_rec.description;
29 END IF;
30
31 IF l_Wip_Entities_rec.entity_type = FND_API.G_MISS_NUM THEN
32 l_Wip_Entities_rec.entity_type := p_old_Wip_Entities_rec.entity_type;
33 END IF;
34
35 IF l_Wip_Entities_rec.last_updated_by = FND_API.G_MISS_NUM THEN
36 l_Wip_Entities_rec.last_updated_by := p_old_Wip_Entities_rec.last_updated_by;
37 END IF;
38
39 IF l_Wip_Entities_rec.last_update_date = FND_API.G_MISS_DATE THEN
40 l_Wip_Entities_rec.last_update_date := p_old_Wip_Entities_rec.last_update_date;
41 END IF;
42
43 IF l_Wip_Entities_rec.last_update_login = FND_API.G_MISS_NUM THEN
44 l_Wip_Entities_rec.last_update_login := p_old_Wip_Entities_rec.last_update_login;
45 END IF;
46
47 IF l_Wip_Entities_rec.organization_id = FND_API.G_MISS_NUM THEN
48 l_Wip_Entities_rec.organization_id := p_old_Wip_Entities_rec.organization_id;
49 END IF;
50
51 IF l_Wip_Entities_rec.primary_item_id = FND_API.G_MISS_NUM THEN
52 l_Wip_Entities_rec.primary_item_id := p_old_Wip_Entities_rec.primary_item_id;
53 END IF;
54
55 IF l_Wip_Entities_rec.program_application_id = FND_API.G_MISS_NUM THEN
56 l_Wip_Entities_rec.program_application_id := p_old_Wip_Entities_rec.program_application_id;
57 END IF;
58
59 IF l_Wip_Entities_rec.program_id = FND_API.G_MISS_NUM THEN
60 l_Wip_Entities_rec.program_id := p_old_Wip_Entities_rec.program_id;
61 END IF;
62
63 IF l_Wip_Entities_rec.program_update_date = FND_API.G_MISS_DATE THEN
64 l_Wip_Entities_rec.program_update_date := p_old_Wip_Entities_rec.program_update_date;
65 END IF;
66
67 IF l_Wip_Entities_rec.request_id = FND_API.G_MISS_NUM THEN
68 l_Wip_Entities_rec.request_id := p_old_Wip_Entities_rec.request_id;
69 END IF;
70
71 IF l_Wip_Entities_rec.wip_entity_id = FND_API.G_MISS_NUM THEN
72 l_Wip_Entities_rec.wip_entity_id := p_old_Wip_Entities_rec.wip_entity_id;
73 END IF;
74
75 IF l_Wip_Entities_rec.wip_entity_name = FND_API.G_MISS_CHAR THEN
76 l_Wip_Entities_rec.wip_entity_name := p_old_Wip_Entities_rec.wip_entity_name;
77 END IF;
78
79 RETURN l_Wip_Entities_rec;
80
81 END Complete_Record;
82
83 -- Function Convert_Miss_To_Null
84
85 FUNCTION Convert_Miss_To_Null
86 ( p_Wip_Entities_rec IN WIP_Work_Order_PUB.Wip_Entities_Rec_Type
87 ) RETURN WIP_Work_Order_PUB.Wip_Entities_Rec_Type
88 IS
89 l_Wip_Entities_rec WIP_Work_Order_PUB.Wip_Entities_Rec_Type := p_Wip_Entities_rec;
90 BEGIN
91
92 IF l_Wip_Entities_rec.created_by = FND_API.G_MISS_NUM THEN
93 l_Wip_Entities_rec.created_by := NULL;
94 END IF;
95
96 IF l_Wip_Entities_rec.creation_date = FND_API.G_MISS_DATE THEN
97 l_Wip_Entities_rec.creation_date := NULL;
98 END IF;
99
100 IF l_Wip_Entities_rec.description = FND_API.G_MISS_CHAR THEN
101 l_Wip_Entities_rec.description := NULL;
102 END IF;
103
104 IF l_Wip_Entities_rec.entity_type = FND_API.G_MISS_NUM THEN
105 l_Wip_Entities_rec.entity_type := NULL;
106 END IF;
107
108 IF l_Wip_Entities_rec.last_updated_by = FND_API.G_MISS_NUM THEN
109 l_Wip_Entities_rec.last_updated_by := NULL;
110 END IF;
111
112 IF l_Wip_Entities_rec.last_update_date = FND_API.G_MISS_DATE THEN
113 l_Wip_Entities_rec.last_update_date := NULL;
114 END IF;
115
116 IF l_Wip_Entities_rec.last_update_login = FND_API.G_MISS_NUM THEN
117 l_Wip_Entities_rec.last_update_login := NULL;
118 END IF;
119
120 IF l_Wip_Entities_rec.organization_id = FND_API.G_MISS_NUM THEN
121 l_Wip_Entities_rec.organization_id := NULL;
122 END IF;
123
124 IF l_Wip_Entities_rec.primary_item_id = FND_API.G_MISS_NUM THEN
125 l_Wip_Entities_rec.primary_item_id := NULL;
126 END IF;
127
128 IF l_Wip_Entities_rec.program_application_id = FND_API.G_MISS_NUM THEN
129 l_Wip_Entities_rec.program_application_id := NULL;
130 END IF;
131
132 IF l_Wip_Entities_rec.program_id = FND_API.G_MISS_NUM THEN
133 l_Wip_Entities_rec.program_id := NULL;
134 END IF;
135
136 IF l_Wip_Entities_rec.program_update_date = FND_API.G_MISS_DATE THEN
137 l_Wip_Entities_rec.program_update_date := NULL;
138 END IF;
139
140 IF l_Wip_Entities_rec.request_id = FND_API.G_MISS_NUM THEN
141 l_Wip_Entities_rec.request_id := NULL;
142 END IF;
143
144 IF l_Wip_Entities_rec.wip_entity_id = FND_API.G_MISS_NUM THEN
145 l_Wip_Entities_rec.wip_entity_id := NULL;
146 END IF;
147
148 IF l_Wip_Entities_rec.wip_entity_name = FND_API.G_MISS_CHAR THEN
149 l_Wip_Entities_rec.wip_entity_name := NULL;
150 END IF;
151
152 RETURN l_Wip_Entities_rec;
153
154 END Convert_Miss_To_Null;
155
156 -- Procedure Update_Row
157
158 PROCEDURE Update_Row
159 ( p_Wip_Entities_rec IN WIP_Work_Order_PUB.Wip_Entities_Rec_Type
160 )
161 IS
162 BEGIN
163
164 UPDATE WIP_ENTITIES
165 SET CREATED_BY = p_Wip_Entities_rec.created_by
166 , CREATION_DATE = p_Wip_Entities_rec.creation_date
167 , DESCRIPTION = p_Wip_Entities_rec.description
168 , ENTITY_TYPE = p_Wip_Entities_rec.entity_type
169 , LAST_UPDATED_BY = p_Wip_Entities_rec.last_updated_by
170 , LAST_UPDATE_DATE = p_Wip_Entities_rec.last_update_date
171 , LAST_UPDATE_LOGIN = p_Wip_Entities_rec.last_update_login
172 , ORGANIZATION_ID = p_Wip_Entities_rec.organization_id
173 , PRIMARY_ITEM_ID = p_Wip_Entities_rec.primary_item_id
174 , PROGRAM_APPLICATION_ID = p_Wip_Entities_rec.program_application_id
175 , PROGRAM_ID = p_Wip_Entities_rec.program_id
176 , PROGRAM_UPDATE_DATE = p_Wip_Entities_rec.program_update_date
177 , REQUEST_ID = p_Wip_Entities_rec.request_id
178 , WIP_ENTITY_ID = p_Wip_Entities_rec.wip_entity_id
179 , WIP_ENTITY_NAME = p_Wip_Entities_rec.wip_entity_name
180 WHERE WIP_ENTITY_ID = p_Wip_Entities_rec.wip_entity_id
181 ;
182
183 EXCEPTION
184
185 WHEN OTHERS THEN
186
187 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
188 THEN
189 FND_MSG_PUB.Add_Exc_Msg
190 ( G_PKG_NAME
191 , 'Update_Row'
192 );
193 END IF;
194
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196
197 END Update_Row;
198
199 -- Procedure Insert_Row
200
201 PROCEDURE Insert_Row
202 ( p_Wip_Entities_rec IN WIP_Work_Order_PUB.Wip_Entities_Rec_Type
203 )
204 IS
205 BEGIN
206
207 INSERT INTO WIP_ENTITIES
208 ( CREATED_BY
209 , CREATION_DATE
210 , DESCRIPTION
211 , ENTITY_TYPE
212 , LAST_UPDATED_BY
213 , LAST_UPDATE_DATE
214 , LAST_UPDATE_LOGIN
215 , ORGANIZATION_ID
216 , PRIMARY_ITEM_ID
217 , PROGRAM_APPLICATION_ID
218 , PROGRAM_ID
219 , PROGRAM_UPDATE_DATE
220 , REQUEST_ID
221 , WIP_ENTITY_ID
222 , WIP_ENTITY_NAME
223 )
224 VALUES
225 ( p_Wip_Entities_rec.created_by
226 , p_Wip_Entities_rec.creation_date
227 , p_Wip_Entities_rec.description
228 , p_Wip_Entities_rec.entity_type
229 , p_Wip_Entities_rec.last_updated_by
230 , p_Wip_Entities_rec.last_update_date
231 , p_Wip_Entities_rec.last_update_login
232 , p_Wip_Entities_rec.organization_id
233 , p_Wip_Entities_rec.primary_item_id
234 , p_Wip_Entities_rec.program_application_id
235 , p_Wip_Entities_rec.program_id
236 , p_Wip_Entities_rec.program_update_date
237 , p_Wip_Entities_rec.request_id
238 , p_Wip_Entities_rec.wip_entity_id
239 , p_Wip_Entities_rec.wip_entity_name
240 );
241
242 EXCEPTION
243
244 WHEN OTHERS THEN
245
246 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
247 THEN
248 FND_MSG_PUB.Add_Exc_Msg
249 ( G_PKG_NAME
250 , 'Insert_Row'
251 );
252 END IF;
253
254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255
256 END Insert_Row;
257
258 -- Procedure Delete_Row
259
260 PROCEDURE Delete_Row
261 ( p_wip_entity_id IN NUMBER
262 )
263 IS
264 BEGIN
265
266 DELETE FROM WIP_ENTITIES
267 WHERE WIP_ENTITY_ID = p_wip_entity_id
268 ;
269
270 EXCEPTION
271
272 WHEN OTHERS THEN
273
274 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
275 THEN
276 FND_MSG_PUB.Add_Exc_Msg
277 ( G_PKG_NAME
278 , 'Delete_Row'
279 );
280 END IF;
281
282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
283
284 END Delete_Row;
285
286 -- Function Query_Row
287
288 FUNCTION Query_Row
289 ( p_wip_entity_id IN NUMBER
290 ) RETURN WIP_Work_Order_PUB.Wip_Entities_Rec_Type
291 IS
292 l_Wip_Entities_rec WIP_Work_Order_PUB.Wip_Entities_Rec_Type;
293 BEGIN
294
295 SELECT CREATED_BY
296 , CREATION_DATE
297 , DESCRIPTION
298 , ENTITY_TYPE
299 , LAST_UPDATED_BY
300 , LAST_UPDATE_DATE
301 , LAST_UPDATE_LOGIN
302 , ORGANIZATION_ID
303 , PRIMARY_ITEM_ID
304 , PROGRAM_APPLICATION_ID
305 , PROGRAM_ID
306 , PROGRAM_UPDATE_DATE
307 , REQUEST_ID
308 , WIP_ENTITY_ID
309 , WIP_ENTITY_NAME
310 INTO l_Wip_Entities_rec.created_by
311 , l_Wip_Entities_rec.creation_date
312 , l_Wip_Entities_rec.description
313 , l_Wip_Entities_rec.entity_type
314 , l_Wip_Entities_rec.last_updated_by
315 , l_Wip_Entities_rec.last_update_date
316 , l_Wip_Entities_rec.last_update_login
317 , l_Wip_Entities_rec.organization_id
318 , l_Wip_Entities_rec.primary_item_id
319 , l_Wip_Entities_rec.program_application_id
320 , l_Wip_Entities_rec.program_id
321 , l_Wip_Entities_rec.program_update_date
322 , l_Wip_Entities_rec.request_id
323 , l_Wip_Entities_rec.wip_entity_id
324 , l_Wip_Entities_rec.wip_entity_name
325 FROM WIP_ENTITIES
326 WHERE WIP_ENTITY_ID = p_wip_entity_id
327 ;
328
329 RETURN l_Wip_Entities_rec;
330
331 EXCEPTION
332
333 WHEN OTHERS THEN
334
335 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
336 THEN
337 FND_MSG_PUB.Add_Exc_Msg
338 ( G_PKG_NAME
339 , 'Query_Row'
340 );
341 END IF;
342
343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
344
345 END Query_Row;
346
347 -- Procedure lock_Row
348 --
349
350 PROCEDURE Lock_Row
351 ( x_return_status OUT NOCOPY VARCHAR2
352 , p_Wip_Entities_rec IN WIP_Work_Order_PUB.Wip_Entities_Rec_Type
353 , x_Wip_Entities_rec OUT NOCOPY WIP_Work_Order_PUB.Wip_Entities_Rec_Type
354 )
355 IS
356 l_Wip_Entities_rec WIP_Work_Order_PUB.Wip_Entities_Rec_Type;
357 BEGIN
358
359 SELECT CREATED_BY
360 , CREATION_DATE
361 , DESCRIPTION
362 , ENTITY_TYPE
363 , LAST_UPDATED_BY
364 , LAST_UPDATE_DATE
365 , LAST_UPDATE_LOGIN
366 , ORGANIZATION_ID
367 , PRIMARY_ITEM_ID
368 , PROGRAM_APPLICATION_ID
369 , PROGRAM_ID
370 , PROGRAM_UPDATE_DATE
371 , REQUEST_ID
372 , WIP_ENTITY_ID
373 , WIP_ENTITY_NAME
374 INTO l_Wip_Entities_rec.created_by
375 , l_Wip_Entities_rec.creation_date
376 , l_Wip_Entities_rec.description
377 , l_Wip_Entities_rec.entity_type
378 , l_Wip_Entities_rec.last_updated_by
379 , l_Wip_Entities_rec.last_update_date
380 , l_Wip_Entities_rec.last_update_login
381 , l_Wip_Entities_rec.organization_id
382 , l_Wip_Entities_rec.primary_item_id
383 , l_Wip_Entities_rec.program_application_id
384 , l_Wip_Entities_rec.program_id
385 , l_Wip_Entities_rec.program_update_date
386 , l_Wip_Entities_rec.request_id
387 , l_Wip_Entities_rec.wip_entity_id
388 , l_Wip_Entities_rec.wip_entity_name
389 FROM WIP_ENTITIES
390 WHERE WIP_ENTITY_ID = p_Wip_Entities_rec.wip_entity_id
391 FOR UPDATE NOWAIT;
392
393 -- Row locked. Compare IN attributes to DB attributes.
394
395 IF WIP_GLOBALS.Equal(p_Wip_Entities_rec.created_by,
396 l_Wip_Entities_rec.created_by)
397 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.creation_date,
398 l_Wip_Entities_rec.creation_date)
399 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.description,
400 l_Wip_Entities_rec.description)
401 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.entity_type,
402 l_Wip_Entities_rec.entity_type)
403 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.last_updated_by,
404 l_Wip_Entities_rec.last_updated_by)
405 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.last_update_date,
406 l_Wip_Entities_rec.last_update_date)
407 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.last_update_login,
408 l_Wip_Entities_rec.last_update_login)
409 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.organization_id,
410 l_Wip_Entities_rec.organization_id)
411 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.primary_item_id,
412 l_Wip_Entities_rec.primary_item_id)
413 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.program_application_id,
414 l_Wip_Entities_rec.program_application_id)
415 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.program_id,
416 l_Wip_Entities_rec.program_id)
417 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.program_update_date,
418 l_Wip_Entities_rec.program_update_date)
419 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.request_id,
420 l_Wip_Entities_rec.request_id)
421 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.wip_entity_id,
422 l_Wip_Entities_rec.wip_entity_id)
423 AND WIP_GLOBALS.Equal(p_Wip_Entities_rec.wip_entity_name,
424 l_Wip_Entities_rec.wip_entity_name)
425 THEN
426
427 -- Row has not changed. Set out parameter.
428
429 x_Wip_Entities_rec := l_Wip_Entities_rec;
430
431 -- Set return status
432
433 x_return_status := FND_API.G_RET_STS_SUCCESS;
434 x_Wip_Entities_rec.return_status := FND_API.G_RET_STS_SUCCESS;
435
436 ELSE
437
438 -- Row has changed by another user.
439
440 x_return_status := FND_API.G_RET_STS_ERROR;
441 x_Wip_Entities_rec.return_status := FND_API.G_RET_STS_ERROR;
442
443 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
444 THEN
445
446 FND_MESSAGE.SET_NAME('WIP','OE_LOCK_ROW_CHANGED');
447 FND_MSG_PUB.Add;
448
449 END IF;
450
451 END IF;
452 EXCEPTION
453
454 WHEN NO_DATA_FOUND THEN
455
456 x_return_status := FND_API.G_RET_STS_ERROR;
457 x_Wip_Entities_rec.return_status := FND_API.G_RET_STS_ERROR;
458
459 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
460 THEN
461
462 FND_MESSAGE.SET_NAME('WIP','OE_LOCK_ROW_DELETED');
463 FND_MSG_PUB.Add;
464
465 END IF;
466 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
467
468 x_return_status := FND_API.G_RET_STS_ERROR;
469 x_Wip_Entities_rec.return_status := FND_API.G_RET_STS_ERROR;
470
471 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
472 THEN
473
474 FND_MESSAGE.SET_NAME('WIP','OE_LOCK_ROW_ALREADY_LOCKED');
475 FND_MSG_PUB.Add;
476
477 END IF;
478 WHEN OTHERS THEN
479
480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
481 x_Wip_Entities_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
482
483 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
484 THEN
485 FND_MSG_PUB.Add_Exc_Msg
486 ( G_PKG_NAME
487 , 'Lock_Row'
488 );
489 END IF;
490
491 END Lock_Row;
492
493
494 END WIP_Wip_Entities_Util;