DBA Data[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;