1 package body ENG_RELATED_ENTITY_PKG as
2 /*$Header: ENGRENTB.pls 120.15 2006/09/05 09:32:46 rnarveka noship $ */
3 -- Global constant holding the package name
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) :=
6 'ENG_RELATED_ENTITY_PKG' ;
7 -- For Debug
8 g_debug_file UTL_FILE.FILE_TYPE ;
9 g_debug_flag BOOLEAN := FALSE ; -- For TEST : FALSE ;
10 g_output_dir VARCHAR2(80) := NULL ;
11 g_debug_filename VARCHAR2(30) := 'eng.chgmt.relationship.log' ;
12 g_debug_errmesg VARCHAR2(240);
13
14
15 change_policy_defined EXCEPTION;
16 duplicate_related_doc EXCEPTION;
17
18 -- Seeded approval_status_type for change header
19 /********************************************************************
20 * Debug APIs : Open_Debug_Session, Close_Debug_Session,
21 * Write_Debug
22 * Parameters IN :
23 * Parameters OUT:
24 * Purpose : These procedures are for test and debug
25 *********************************************************************/
26 -- Open_Debug_Session
27 Procedure Open_Debug_Session
28 ( p_output_dir IN VARCHAR2 := NULL
29 , p_file_name IN VARCHAR2 := NULL
30 )
31 IS
32 l_found NUMBER := 0;
33 l_utl_file_dir VARCHAR2(2000);
34
35 BEGIN
36
37 IF p_output_dir IS NOT NULL THEN
38 g_output_dir := p_output_dir ;
39
40 END IF ;
41
42 IF p_file_name IS NOT NULL THEN
43 g_debug_filename := p_file_name ;
44 END IF ;
45
46 IF g_output_dir IS NULL
47 THEN
48
49 g_output_dir := FND_PROFILE.VALUE('ECX_UTL_LOG_DIR') ;
50
51 END IF;
52
53 select value
54 INTO l_utl_file_dir
55 FROM v$parameter
56 WHERE name = 'utl_file_dir';
57
58 l_found := INSTR(l_utl_file_dir, g_output_dir);
59
60 IF l_found = 0
61 THEN
62 RETURN;
63 END IF;
64
65 g_debug_file := utl_file.fopen( g_output_dir
66 , g_debug_filename
67 , 'w');
68 g_debug_flag := TRUE ;
69
70 EXCEPTION
71 WHEN OTHERS THEN
72 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
73 g_debug_flag := FALSE;
74
75 END Open_Debug_Session ;
76
77 -- Close Debug_Session
78 Procedure Close_Debug_Session
79 IS
80 BEGIN
81 IF utl_file.is_open(g_debug_file)
82 THEN
83 utl_file.fclose(g_debug_file);
84 END IF ;
85
86 EXCEPTION
87 WHEN OTHERS THEN
88 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
89 g_debug_flag := FALSE;
90
91 END Close_Debug_Session ;
92
93 -- Test Debug
94 Procedure Write_Debug
95 ( p_debug_message IN VARCHAR2 )
96 IS
97 BEGIN
98
99 IF utl_file.is_open(g_debug_file)
100 THEN
101 utl_file.put_line(g_debug_file, p_debug_message);
102 END IF ;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
107 g_debug_flag := FALSE;
108
109 END Write_Debug;
110 --added l_to_current _value as defination change from DOm side.
111 Procedure Implement_Relationship_Changes
112 (
113 p_api_version IN NUMBER --
114 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
115 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
116 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
117 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
118 ,p_output_dir IN VARCHAR2 := NULL --
119 ,p_debug_filename IN VARCHAR2 := 'ENGRENTB.Implement_Relationship_Changes.log'
120 ,x_return_status OUT NOCOPY VARCHAR2 --
121 ,x_msg_count OUT NOCOPY NUMBER --
122 ,x_msg_data OUT NOCOPY VARCHAR2 --
123 ,p_change_id IN NUMBER -- header's change_id
124 ,p_entity_id IN NUMBER -- ed item sequence id
125 )
126 IS
127 l_action_type VARCHAR2(30);
128 l_from_entity_name VARCHAR2(40);
129 l_from_pk1_value VARCHAR2(100);
130 l_from_pk2_value VARCHAR2(100);
131 l_from_pk3_value VARCHAR2(100);
132 l_from_pk4_value VARCHAR2(100);
133 l_from_pk5_value VARCHAR2(100);
134 l_to_entity_name VARCHAR2(40);
135 l_to_pk1_value VARCHAR2(100);
136 l_to_pk2_value VARCHAR2(100);
137 l_to_current_value VARCHAR2(100);
138 l_to_pk3_value VARCHAR2(100);
139 l_to_pk4_value VARCHAR2(100);
140 l_to_pk5_value VARCHAR2(100);
141 l_relationship_code VARCHAR2(30);
142 l_created_by NUMBER;
143 l_last_update_login NUMBER;
144
145 cursor C IS
146 select association_id,action, relationship_code, from_entity_name, from_pk1_value,
147 from_pk2_value, from_pk3_value, from_pk4_value, from_pk5_value,
148 to_entity_name, to_pk1_value, to_pk2_value, to_pk3_value,
149 to_pk4_value, to_pk5_value, created_by, last_update_login,to_current_value
150 from eng_relationship_changes
151 where change_id = p_change_id
152 and entity_id = p_entity_id;
153
154
155 l_api_name CONSTANT VARCHAR2(30) := 'Implement_Relationship_Changes';
156 l_api_version CONSTANT NUMBER := 1.0;
157 l_return_status VARCHAR2(1);
158 l_msg_count NUMBER;
159 l_msg_data VARCHAR2(2000);
160 l_message VARCHAR2(4000);
161
162 BEGIN
163 -- Standard Start of API savepoint
164 SAVEPOINT Implement_Relationship_Changes;
165
166 --If BIS_COLLECTION_UTILITIES.SETUP(p_object_name => 'ENI_OLTP_ITEM_STAR')=false then
167 --RAISE_APPLICATION_ERROR(-20000,errbuf);
168 --End if;
169
170
171 -- Standard call to check for call compatibility
172 IF NOT FND_API.Compatible_API_Call ( l_api_version
173 ,p_api_version
174 ,l_api_name
175 ,G_PKG_NAME )
176 THEN
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 END IF;
179
180 -- Initialize message list if p_init_msg_list is set to TRUE.
181 IF FND_API.to_Boolean( p_init_msg_list ) THEN
182 FND_MSG_PUB.initialize;
183 END IF ;
184 -- For Test/Debug
185 IF FND_API.to_Boolean( p_debug ) THEN
186 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
187 END IF ;
188
189 -- Write debug message if debug mode is on
190 IF g_debug_flag THEN
191 Write_Debug('ENG_RELATED_ENTITY_PKG.Implement_Relationship_Change_log');
192 Write_Debug('-----------------------------------------------------');
193 Write_Debug('p_change_id : ' || p_change_id );
194 Write_Debug('p_entity_id : ' || p_entity_id );
195 Write_Debug('-----------------------------------------------------');
196 Write_Debug('Initializing return status... ' );
197 END IF ;
198 -- Initialize API return status to success
199 x_return_status := FND_API.G_RET_STS_SUCCESS;
200
201
202 -- Real code starts here -----------------------------------------------
203
204 -- First check if there are floating versions in the CO to be implemented.
205 -- if there are flaoting version, check its change policy and ensure that
206 -- it is not under CO Required. If it is CO required, the implementation
207 -- should fail.
208
209 --BIS_COLLECTION_UTILITIES.log('Before Validation');
210 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Checking for floating revision');
211 Validate_floating_revision (
212 p_api_version => 1
213 ,p_change_id => p_change_id
214 ,p_rev_item_seq_id => p_entity_id
215 , x_return_status => l_return_status
216 , x_msg_count => l_msg_count
217 , x_msg_data => l_msg_data
218 );
219 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Completed validation');
220 --BIS_COLLECTION_UTILITIES.log('After Validation');
221 --In C cursor l_created_by was getting selected.that was causing un-expected error.
222 FOR REL_CHANGES IN C LOOP
223
224 -- Call DOM API with the action
225 -- if action = 'ADD' then the API will add the record in the DOM
226 -- relationship table
227 -- if action = 'DELETE' then the record will be deleted
228 -- If action = 'CHANGE_REVISION' then the revision id will be modified
229 -- for that related document
230 -- added assocaition id for dom changes.
231 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Creating record in DOM tables');
232 DOM_ASSOCIATIONS_UTIL.Implement_Pending_Association(
233 p_ASSOCIATION_ID => REL_CHANGES.association_id,
234 P_ACTION => REL_CHANGES.action,
235 P_FROM_ENTITY_NAME => REL_CHANGES.from_entity_name,
236 P_FROM_PK1_VALUE => REL_CHANGES.from_pk1_value,
237 P_FROM_PK2_VALUE => REL_CHANGES.from_pk2_value,
238 P_FROM_PK3_VALUE => REL_CHANGES.from_pk3_value,
239 P_FROM_PK4_VALUE => REL_CHANGES.from_pk4_value,
240 P_FROM_PK5_VALUE => REL_CHANGES.from_pk5_value,
241 P_TO_ENTITY_NAME => REL_CHANGES.to_entity_name,
242 P_TO_PK1_VALUE => REL_CHANGES.to_pk1_value,
243 P_TO_PK2_VALUE => REL_CHANGES.to_pk2_value,
244 P_TO_PK3_VALUE => REL_CHANGES.to_pk3_value,
245 P_TO_PK4_VALUE => REL_CHANGES.to_pk4_value,
246 P_TO_PK5_VALUE => REL_CHANGES.to_pk5_value,
247 P_RELATIONSHIP_CODE => REL_CHANGES.relationship_code,
248 P_CURRENT_VALUE => REL_CHANGES.to_current_value,
249 P_CREATED_BY => REL_CHANGES.created_by,
250 P_LAST_UPDATE_LOGIN => REL_CHANGES.last_update_login,
251 X_RETURN_STATUS => l_return_status,
252 X_MSG_COUNT => l_msg_count,
253 X_MSG_DATA => l_msg_data
254 );
255
256 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
258 EXIT;
259 END IF;
260
261 IF ( l_return_status = FND_API.G_RET_STS_ERROR) THEN
262 RAISE duplicate_related_doc;
263 EXIT;
264 END IF;
265 END LOOP;
266
267 -- Standard ending code ------------------------------------------------
268 FND_MSG_PUB.Count_And_Get
269 ( p_count => x_msg_count,
270 p_data => x_msg_data );
271
272 IF g_debug_flag THEN
273 Write_Debug('Finish. End Of Proc') ;
274 Close_Debug_Session ;
275 END IF ;
276
277 EXCEPTION
278 WHEN change_policy_defined THEN
279 ROLLBACK TO Implement_Relationship_Changes;
280 x_return_status := FND_API.G_RET_STS_ERROR;
281 x_msg_count := 1;
282 x_msg_data := 'Error: This Change Order has documents with floating revision that are under change required change policy. Such CO cannot be implemented';
283 -- BIS_COLLECTION_UTILITIES.log('In exception'|| x_msg_data);
284 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msg_data);
285 WHEN duplicate_related_doc THEN
286 ROLLBACK TO Implement_Relationship_Changes;
287 x_return_status := FND_API.G_RET_STS_ERROR;
288 x_msg_count := 1;
289 x_msg_data := 'Error: Duplicate operation on same related document of item . ';
290 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msg_data);
291 WHEN FND_API.G_EXC_ERROR THEN
292 ROLLBACK TO Implement_Relationship_Changes;
293 x_return_status := FND_API.G_RET_STS_ERROR;
294 FND_MSG_PUB.Count_And_Get
295 ( p_count => l_msg_count
296 ,p_data => l_msg_data );
297 --BIS_COLLECTION_UTILITIES.log('In exception'|| l_msg_data);
298 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msg_data);
299 IF g_debug_flag THEN
300 Write_Debug('Error Msg ' || l_msg_data);
301 Write_Debug('Rollback and Finish with expected error.') ;
302 Close_Debug_Session ;
303 END IF ;
304 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
305 ROLLBACK TO Implement_Relationship_Changes;
306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
307 FND_MSG_PUB.Count_And_Get
308 ( p_count => l_msg_count
309 ,p_data => l_msg_data );
310 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msg_data);
311 IF g_debug_flag THEN
312 Write_Debug('Error Msg ' || l_msg_data);
313 Write_Debug('Rollback and Finish with unexpected error.') ;
314 Close_Debug_Session ;
315 END IF ;
316 WHEN OTHERS THEN
317 ROLLBACK TO Implement_Relationship_Changes;
318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
319 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
320 THEN
321 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
322 END IF;
323 FND_MSG_PUB.Count_And_Get
324 ( p_count => l_msg_count
325 ,p_data => l_msg_data );
326 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msg_data);
327 IF g_debug_flag THEN
328 Write_Debug('Error Msg ' || l_msg_data);
329 Write_Debug('Rollback and Finish with other error.') ;
330 Close_Debug_Session ;
331 END IF ;
332
333 END Implement_Relationship_Changes;
334
335 -- Procedure to check if the floating revision is under change control
336 -- change policy.
337
338 Procedure Validate_floating_revision (
339 p_api_version IN NUMBER
340 ,p_change_id IN NUMBER
341 ,p_rev_item_seq_id IN NUMBER := NULL
342 ,x_return_status OUT NOCOPY VARCHAR2
343 ,x_msg_count OUT NOCOPY NUMBER
344 ,x_msg_data OUT NOCOPY VARCHAR2
345 )
346 IS
347 Cursor get_floating_revisions(l_change_id NUMBER,
348 l_revised_item_seq_id NUMBER) is
349 select change_id, entity_id, b.category_id,
350 from_pk1_value pk1_value, from_pk2_value pk2_value,
351 from_pk3_value pk3_value
352 from eng_relationship_changes a, dom_documents b
353 where a.change_id = l_change_id
354 and a.to_pk1_value = b.document_id
355 and to_pk2_value = -1 -- for floating revision documents
356 and action in ('ADD','CHANGE_REVISION')
357 and entity_id in (select decode(l_revised_item_seq_id,null, (select revised_item_sequence_id
358 from eng_revised_items
359 where change_id = a.change_id),
360 l_revised_item_seq_id) from dual);
361
362 l_change_id NUMBER;
363 l_revised_item_seq_id NUMBER;
364 l_change_policy VARCHAR2(100);
365 l_api_name VARCHAR2(100) := 'Validate_floating_revision';
366
367 BEGIN
368 SAVEPOINT Implement_Relationship_Changes;
369
370 -- Initialize API return status to success
371 x_return_status := FND_API.G_RET_STS_SUCCESS;
372
373 l_change_id := p_change_id;
374 l_revised_item_seq_id := p_rev_item_seq_id;
375
376 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Validating floating revision');
377
378 FOR c2 in get_floating_revisions(l_change_id, l_revised_item_seq_id)
379 LOOP
380
381 BEGIN
382
383 -- The foll. SQL checks if the category passed has changepolicy defined
384 -- on it or not
385 SELECT ecp.policy_char_value INTO l_change_policy
386 FROM
387 (select nvl(mirb.lifecycle_id, msi.lifecycle_id) as lifecycle_id,
388 nvl(mirb.current_phase_id , msi.current_phase_id) as phase_id,
389 msi.item_catalog_group_id item_catalog_group_id,
390 msi.inventory_item_id, msi.organization_id , mirb.revision_id
391 from mtl_item_revisions_b mirb,
392 MTL_SYSTEM_ITEMS msi
393 where mirb.INVENTORY_ITEM_ID(+) = msi.INVENTORY_ITEM_ID
394 and mirb.ORGANIZATION_ID(+)= msi.ORGANIZATION_ID
395 and mirb.revision_id(+) = c2.pk3_value
396 and msi.INVENTORY_ITEM_ID = c2.pk2_value
397 and msi.ORGANIZATION_ID = c2.pk1_value) ITEM_DTLS,
398 ENG_CHANGE_POLICIES_V ECP
399 WHERE
400 ecp.policy_object_pk1_value =
401 (SELECT TO_CHAR(ic.item_catalog_group_id)
402 FROM mtl_item_catalog_groups_b ic
403 WHERE EXISTS (SELECT olc.object_classification_code CatalogId
404 FROM EGO_OBJ_TYPE_LIFECYCLES olc
405 WHERE olc.object_id = (SELECT OBJECT_ID
406 FROM fnd_objects
407 WHERE obj_name = 'EGO_ITEM')
408 AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
409 AND olc.object_classification_code = ic.item_catalog_group_id
410 )
411 AND ROWNUM = 1
412 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
413 START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
414 AND ecp.policy_object_pk2_value = ITEM_DTLS.lifecycle_id
415 AND ecp.policy_object_pk3_value = ITEM_DTLS.phase_id
416 and ecp.policy_object_name = 'CATALOG_LIFECYCLE_PHASE'
417 and ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
418 and ecp.attribute_code = 'AML_RULE'
419 and ecp.attribute_number_value = 2;
420
421 IF l_change_policy = 'CHANGE_ORDER_REQUIRED' THEN
422 RAISE change_policy_defined;
423 ROLLBACK TO Implement_Relationship_Changes;
424 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Change policy required exception');
425 END IF;
426
427 EXCEPTION
428 WHEN NO_DATA_FOUND THEN -- no data found means there are no change
429 -- policy defined for the category
430 null;
431 END;
432
433 END LOOP;
434
435 END Validate_floating_revision;
436
437 END ENG_RELATED_ENTITY_PKG;
[Home] [Help]