[Home] [Help]
PACKAGE BODY: APPS.DOM_ASSOCIATIONS_UTIL
Source
1 package body DOM_ASSOCIATIONS_UTIL as
2 /*$Header: DOMPASUB.pls 120.5 2006/09/05 15:16:04 dedatta noship $ */
3 -- Global constant holding the package name
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DOM_ASSOCIATIONS_UTIL' ;
6
7 Procedure Insert_Row(
8 p_association_id IN NUMBER,
9 p_from_entity_name IN VARCHAR2,
10 p_from_pk1_value IN VARCHAR2,
11 p_from_pk2_value IN VARCHAR2,
12 p_from_pk3_value IN VARCHAR2,
13 p_from_pk4_value IN VARCHAR2,
14 p_from_pk5_value IN VARCHAR2,
15 p_to_entity_name IN VARCHAR2,
16 p_to_pk1_value IN VARCHAR2,
17 p_to_pk2_value IN VARCHAR2,
18 p_to_pk3_value IN VARCHAR2,
19 p_to_pk4_value IN VARCHAR2,
20 p_to_pk5_value IN VARCHAR2,
21 p_relationship_code IN VARCHAR2,
22 p_created_by IN NUMBER,
23 p_last_update_login IN NUMBER,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_count OUT NOCOPY NUMBER,
26 x_msg_data OUT NOCOPY VARCHAR2 )
27 IS
28 l_doc_number VARCHAR2(30);
29 l_message VARCHAR2(40);
30 BEGIN
31
32 l_doc_number := NULL;
33
34 BEGIN
35 SELECT doc_number INTO l_doc_number
36 FROM DOM_RELATIONSHIPS DR, DOM_DOCUMENTS_VL DD
37 WHERE
38 from_entity_name = p_from_entity_name
39 AND from_pk1_value = p_from_pk1_value
40 AND from_pk2_value = p_from_pk2_value
41 AND ((p_from_entity_name = 'EGO_ITEM_REVISION' AND from_pk3_value = p_from_pk3_value ) OR
42 (p_from_entity_name = 'DOM_DOCUMENT_REVISION' AND from_pk3_value IS NULL ) OR p_from_entity_name = 'EGO_ITEM' AND from_pk3_value IS NULL )
43 AND to_entity_name = p_to_entity_name
44 AND to_pk1_value = p_to_pk1_value
45 AND relationship_code= p_relationship_code
46 AND to_pk1_value = TO_CHAR(dd.document_id);
47 EXCEPTION
48 WHEN NO_DATA_FOUND THEN
49 NULL;
50 END;
51
52 IF l_doc_number IS NOT NULL THEN
53 l_message := 'DOM_ADD_IMP_ERROR';
54 FND_MESSAGE.Set_Name('DOM', l_message);
55 FND_MESSAGE.Set_Token('DOC_NUMBER', l_doc_number);
56 FND_MSG_PUB.Add;
57 x_return_status := FND_API.G_RET_STS_ERROR;
58 ELSE
59 INSERT INTO DOM_RELATIONSHIPS (
60 association_id ,
61 from_entity_name ,
62 from_pk1_value ,
63 from_pk2_value ,
64 from_pk3_value ,
65 from_pk4_value ,
66 from_pk5_value ,
67 to_entity_name ,
68 to_pk1_value ,
69 to_pk2_value ,
70 to_pk3_value ,
71 to_pk4_value ,
72 to_pk5_value ,
73 relationship_code ,
74 created_by ,
75 last_update_login ,
76 creation_date ,
77 last_update_date
78 )
79 VALUES
80 (
81 p_association_id ,
82 p_from_entity_name ,
83 p_from_pk1_value ,
84 p_from_pk2_value ,
85 p_from_pk3_value ,
86 p_from_pk4_value ,
87 p_from_pk5_value ,
88 p_to_entity_name ,
89 p_to_pk1_value ,
90 p_to_pk2_value ,
91 p_to_pk3_value ,
92 p_to_pk4_value ,
93 p_to_pk5_value ,
94 p_relationship_code ,
95 p_created_by ,
96 p_last_update_login ,
97 sysdate ,
98 sysdate
99 );
100 END IF;
101
102
103 END Insert_Row;
104
105 Procedure Delete_Row(
106 p_from_entity_name IN VARCHAR2,
107 p_from_pk1_value IN VARCHAR2,
108 p_from_pk2_value IN VARCHAR2,
109 p_from_pk3_value IN VARCHAR2,
110 p_from_pk4_value IN VARCHAR2,
111 p_from_pk5_value IN VARCHAR2,
112 p_to_entity_name IN VARCHAR2,
113 p_to_pk1_value IN VARCHAR2,
114 p_to_pk2_value IN VARCHAR2,
115 p_to_pk3_value IN VARCHAR2,
116 p_to_pk4_value IN VARCHAR2,
117 p_to_pk5_value IN VARCHAR2,
118 p_current_value IN VARCHAR2,
119 p_relationship_code IN VARCHAR2,
120 x_return_status OUT NOCOPY VARCHAR2,
121 x_msg_count OUT NOCOPY NUMBER,
122 x_msg_data OUT NOCOPY VARCHAR2 )
123 IS
124 l_doc_number VARCHAR2(30);
125 l_message VARCHAR2(40);
126 BEGIN
127
128 l_doc_number := NULL;
129
130 BEGIN
131 SELECT doc_number INTO l_doc_number
132 FROM DOM_RELATIONSHIPS DR, DOM_DOCUMENTS_VL DD
133 WHERE
134 from_entity_name = p_from_entity_name
135 AND from_pk1_value = p_from_pk1_value
136 AND from_pk2_value = p_from_pk2_value
137 AND ((p_from_entity_name = 'EGO_ITEM_REVISION' AND from_pk3_value = p_from_pk3_value ) OR
138 (p_from_entity_name = 'DOM_DOCUMENT_REVISION' AND from_pk3_value IS NULL ) OR (p_from_entity_name = 'EGO_ITEM' AND from_pk3_value IS NULL) )
139 AND to_entity_name = p_to_entity_name
140 AND to_pk1_value = p_to_pk1_value
141 AND relationship_code= p_relationship_code
142 AND to_pk1_value = TO_CHAR(dd.document_id);
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145 NULL;
146 END;
147
148
149
150
151 IF l_doc_number IS NULL THEN
152 l_message := 'DOM_DELETE_IMP_ERROR';
153 FND_MESSAGE.Set_Name('DOM', l_message);
154 FND_MESSAGE.Set_Token('DOC_NUMBER', l_doc_number);
155 FND_MSG_PUB.Add;
156 x_return_status := FND_API.G_RET_STS_ERROR;
157 ELSE
158
159
160 DELETE FROM DOM_RELATIONSHIPS
161 WHERE
162 from_entity_name = p_from_entity_name
163 AND from_pk1_value = p_from_pk1_value
164 AND from_pk2_value = p_from_pk2_value
165 AND ((p_from_entity_name = 'EGO_ITEM_REVISION' AND from_pk3_value = p_from_pk3_value ) OR
166 (p_from_entity_name = 'DOM_DOCUMENT_REVISION' AND from_pk3_value IS NULL ) OR
167 (p_from_entity_name = 'EGO_ITEM' AND from_pk3_value IS NULL ))
168 AND to_entity_name = p_to_entity_name
169 AND to_pk1_value = p_to_pk1_value
170 --AND to_pk2_value = p_current_value
171 AND relationship_code= p_relationship_code;
172 END IF;
173
174 END Delete_Row;
175
176 Procedure Change_Revision(
177 p_from_entity_name IN VARCHAR2,
178 p_from_pk1_value IN VARCHAR2,
179 p_from_pk2_value IN VARCHAR2,
180 p_from_pk3_value IN VARCHAR2,
181 p_from_pk4_value IN VARCHAR2,
182 p_from_pk5_value IN VARCHAR2,
183 p_to_entity_name IN VARCHAR2,
184 p_to_pk1_value IN VARCHAR2,
185 p_to_pk2_value IN VARCHAR2,
186 p_to_pk3_value IN VARCHAR2,
187 p_to_pk4_value IN VARCHAR2,
188 p_to_pk5_value IN VARCHAR2,
189 p_relationship_code IN VARCHAR2,
190 p_current_value IN VARCHAR2,
191 x_return_status OUT NOCOPY VARCHAR2,
192 x_msg_count OUT NOCOPY NUMBER,
193 x_msg_data OUT NOCOPY VARCHAR2 )
194 IS
195 l_doc_number VARCHAR2(30);
196 l_message VARCHAR2(40);
197 BEGIN
198
199 l_doc_number := NULL;
200
201 BEGIN
202 SELECT doc_number INTO l_doc_number
203 FROM DOM_RELATIONSHIPS DR, DOM_DOCUMENTS_VL DD
204 WHERE
205 from_entity_name = p_from_entity_name
206 AND from_pk1_value = p_from_pk1_value
207 AND from_pk2_value = p_from_pk2_value
208 AND ((p_from_entity_name = 'EGO_ITEM_REVISION' AND from_pk3_value = p_from_pk3_value ) OR
209 (p_from_entity_name = 'DOM_DOCUMENT_REVISION' AND from_pk3_value IS NULL ) OR (p_from_entity_name = 'EGO_ITEM' AND from_pk3_value IS NULL) )
210 AND to_entity_name = p_to_entity_name
211 AND to_pk1_value = p_to_pk1_value
212 AND relationship_code= p_relationship_code
213 AND to_pk1_value = TO_CHAR(dd.document_id);
214 EXCEPTION
215 WHEN NO_DATA_FOUND THEN
216 NULL;
217 END;
218
219
220
221
222
223
224
225 IF l_doc_number IS NULL THEN
226 l_message := 'DOM_CHANGE_REV_IMP_ERROR';
227 FND_MESSAGE.Set_Name('DOM', l_message);
228 FND_MESSAGE.Set_Token('DOC_NUMBER', l_doc_number);
229 FND_MSG_PUB.Add;
230 x_return_status := FND_API.G_RET_STS_ERROR;
231 ELSE
232
233 UPDATE DOM_RELATIONSHIPS
234 SET
235 to_pk2_value = p_to_pk2_value
236 WHERE
237 from_entity_name = decode(p_from_entity_name,'MTL_ITEM_REVISIONS','EGO_ITEM_REVISION',decode(p_from_entity_name,'MTL__SYSTEM_ITEMS','EGO_ITEM',p_from_entity_name))
238 AND from_pk1_value = p_from_pk1_value
239 AND from_pk2_value = p_from_pk2_value
240 AND ((decode(p_from_entity_name,'MTL_ITEM_REVISIONS','EGO_ITEM_REVISION',decode(p_from_entity_name,'MTL__SYSTEM_ITEMS','EGO_ITEM',p_from_entity_name)) = 'EGO_ITEM_REVISION' AND from_pk3_value = p_from_pk3_value ) OR
241 (p_from_entity_name = 'DOM_DOCUMENT_REVISION' AND from_pk3_value IS NULL ) OR
242 (p_from_entity_name = 'EGO_ITEM' AND from_pk3_value IS NULL ))
243 AND to_entity_name = p_to_entity_name
244 AND to_pk1_value = p_to_pk1_value
245 --AND to_pk2_value = p_current_value
246 AND relationship_code= p_relationship_code;
247 END IF;
248
249 END Change_Revision;
250
251 Procedure Implement_Pending_Association(
252 p_association_id IN NUMBER ,
253 p_action IN VARCHAR2,
254 p_from_entity_name IN VARCHAR2,
255 p_from_pk1_value IN VARCHAR2,
256 p_from_pk2_value IN VARCHAR2,
257 p_from_pk3_value IN VARCHAR2,
258 p_from_pk4_value IN VARCHAR2,
259 p_from_pk5_value IN VARCHAR2,
260 p_to_entity_name IN VARCHAR2,
261 p_to_pk1_value IN VARCHAR2,
262 p_to_pk2_value IN VARCHAR2,
263 p_to_pk3_value IN VARCHAR2,
264 p_to_pk4_value IN VARCHAR2,
265 p_to_pk5_value IN VARCHAR2,
266 p_relationship_code IN VARCHAR2,
267 p_current_value IN VARCHAR2,
268 p_created_by IN NUMBER,
269 p_last_update_login IN NUMBER,
270 x_return_status OUT NOCOPY VARCHAR2,
271 x_msg_count OUT NOCOPY NUMBER,
272 x_msg_data OUT NOCOPY VARCHAR2 )
273 IS
274
275 l_action VARCHAR2(20);
276
277
278 l_return_status VARCHAR2(1);
279 l_msg_count NUMBER;
280 l_msg_data VARCHAR2(2000);
281 l_api_name VARCHAR2(50) := 'Implement_Pending_Association';
282
283
284 BEGIN
285 -- Initialize API return status to success
286 x_return_status := FND_API.G_RET_STS_SUCCESS;
287
288 l_action := p_action;
289
290 IF l_action = 'ADD' THEN
291
292 Insert_Row ( p_association_id => p_association_id,
293 p_from_entity_name => p_from_entity_name,
294 p_from_pk1_value => p_from_pk1_value,
295 p_from_pk2_value => p_from_pk2_value,
296 p_from_pk3_value => p_from_pk3_value,
297 p_from_pk4_value => p_from_pk4_value,
298 p_from_pk5_value => p_from_pk5_value,
299 p_to_entity_name => p_to_entity_name,
300 p_to_pk1_value => p_to_pk1_value,
301 p_to_pk2_value => p_to_pk2_value,
302 p_to_pk3_value => p_to_pk3_value,
303 p_to_pk4_value => p_to_pk4_value,
304 p_to_pk5_value => p_to_pk5_value,
305 p_relationship_code => p_relationship_code,
306 p_created_by => p_created_by,
307 p_last_update_login => p_last_update_login,
308 x_return_status => x_return_status,
309 x_msg_count => x_msg_count,
310 x_msg_data => x_msg_data );
311
312 ELSIF l_action = 'DELETE' THEN
313
314 Delete_Row (
315 p_from_entity_name => p_from_entity_name,
316 p_from_pk1_value => p_from_pk1_value,
317 p_from_pk2_value => p_from_pk2_value,
318 p_from_pk3_value => p_from_pk3_value,
319 p_from_pk4_value => p_from_pk4_value,
320 p_from_pk5_value => p_from_pk5_value,
321 p_to_entity_name => p_to_entity_name,
322 p_to_pk1_value => p_to_pk1_value,
323 p_to_pk2_value => p_to_pk2_value,
324 p_to_pk3_value => p_to_pk3_value,
325 p_to_pk4_value => p_to_pk4_value,
326 p_to_pk5_value => p_to_pk5_value,
327 p_relationship_code => p_relationship_code,
328 p_current_value => p_current_value,
329 x_return_status => x_return_status,
330 x_msg_count => x_msg_count,
331 x_msg_data => x_msg_data );
332
333 ELSIF l_action = 'CHANGE_REVISION' THEN
334
335 Change_Revision (
336 p_from_entity_name => p_from_entity_name,
337 p_from_pk1_value => p_from_pk1_value,
338 p_from_pk2_value => p_from_pk2_value,
339 p_from_pk3_value => p_from_pk3_value,
340 p_from_pk4_value => p_from_pk4_value,
341 p_from_pk5_value => p_from_pk5_value,
342 p_to_entity_name => p_to_entity_name,
343 p_to_pk1_value => p_to_pk1_value,
344 p_to_pk2_value => p_to_pk2_value,
345 p_to_pk3_value => p_to_pk3_value,
346 p_to_pk4_value => p_to_pk4_value,
350 x_return_status => x_return_status,
347 p_to_pk5_value => p_to_pk5_value,
348 p_relationship_code => p_relationship_code,
349 p_current_value => p_current_value,
351 x_msg_count => x_msg_count,
352 x_msg_data => x_msg_data );
353 END IF;
354
355
356
357
358
359
360 -- Standard ending code ------------------------------------------------
361
362 FND_MSG_PUB.Count_And_Get
363 ( p_count => x_msg_count,
364 p_data => x_msg_data );
365
366 EXCEPTION
367
368 WHEN FND_API.G_EXC_ERROR THEN
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 FND_MSG_PUB.Count_And_Get
371 ( p_count => x_msg_count
372 ,p_data => x_msg_data );
373
374 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
376 FND_MSG_PUB.Count_And_Get
377 ( p_count => x_msg_count
378 ,p_data => x_msg_data );
379
380 WHEN OTHERS THEN
381
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
383 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
384 THEN
385 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
386 END IF;
387 FND_MSG_PUB.Count_And_Get
388 ( p_count => x_msg_count
389 ,p_data => x_msg_data );
390
391 END Implement_Pending_Association;
392
393
394 END DOM_ASSOCIATIONS_UTIL;