1 PACKAGE BODY ENG_NIR_UTIL_PKG AS
2 /* $Header: ENGNIRB.pls 120.20 2007/07/16 12:12:06 sdarbha ship $ */
3 PROCEDURE set_nir_item_approval_status (
4 p_change_id IN NUMBER,
5 p_approval_status IN NUMBER,
6 x_return_status OUT NOCOPY VARCHAR2,
7 x_msg_count OUT NOCOPY NUMBER,
8 x_msg_data OUT NOCOPY VARCHAR2) IS
9
10 type item_id_col is table of NUMBER;
11 type appr_status_col is table of VARCHAR2(1);
12 type org_id_col is table of NUMBER;
13
14 l_items_array item_id_col;
15 l_appr_statuses_array appr_status_col;
16 l_org_id_array org_id_col ;
17 l_approval_status VARCHAR2(1);
18 l_ret_item_app_st INTEGER;
19
20 BEGIN
21 x_return_status := FND_API.G_RET_STS_SUCCESS;
22 IF p_approval_status = 1 THEN
23 l_approval_status := 'N';
24 ELSIF p_approval_status = 3 THEN
25 l_approval_status := 'S';
26 ELSIF p_approval_status IN (4, 8) THEN
27 l_approval_status := 'R';
28 ELSIF p_approval_status IN (5) THEN
29 l_approval_status := 'A';
30 END IF;
31 --IF p_approval_status IN (1,3,4, 8) THEN
32
33 Select sub.pk1_value,
34 sub.pk2_value
35 BULK COLLECT into l_items_array,
36 l_org_id_array
37 from eng_change_subjects sub ,
38 eng_change_lines lines
39 where lines.change_id = p_change_id
40 and lines.change_id = sub.change_id
41 and lines.change_line_id = sub.change_line_id
42 and lines.STATUS_CODE not in (G_ENG_NEW_ITEM_REJECTED, G_ENG_NEW_ITEM_CANCELLED)
43 and entity_name='EGO_ITEM';
44 -- ELSIF p_approval_status = 5 THEN
45
46 /* Select sub.pk1_value,
47 sub.pk2_value
48 BULK COLLECT into l_items_array,
49 l_org_id_array
50 -- l_appr_statuses_array
51 from eng_change_subjects sub ,
52 eng_change_lines lines
53 where lines.change_id = p_change_id
54 and lines.change_id = sub.change_id
55 and lines.STATUS_CODE not in (5, 14)
56 and entity_name='EGO_ITEM';*/
57
58 -- END IF;
59 for i in l_items_array.FIRST .. l_items_array.LAST
60 LOOP
61 --IF p_approval_status IN (1,3,4, 8) then
62
63 -- l_ret_item_app_st := EGO_ITEM_PUB.UPDATE_ITEM_APPROVAL_STATUS(l_items_array(i),
64 EGO_ITEM_PUB.UPDATE_ITEM_APPROVAL_STATUS(l_items_array(i),
65 l_org_id_array(i),
66 l_approval_status,
67 p_change_id);
68 IF p_approval_status IN (4,8) THEN
69 UPDATE eng_change_lines
70 SET STATUS_CODE = G_ENG_NEW_ITEM_REJECTED
71 WHERE change_id = p_change_id
72 AND status_CODE not in(G_ENG_NEW_ITEM_REJECTED,G_ENG_NEW_ITEM_CANCELLED);
73 ELSIF p_approval_status = 3 THEN
74 UPDATE eng_change_lines
75 SET STATUS_CODE = G_ENG_NEW_ITEM_SFA
76 WHERE change_id = p_change_id
77 AND status_CODE not in(G_ENG_NEW_ITEM_REJECTED,G_ENG_NEW_ITEM_CANCELLED);
78 ELSIF p_approval_status IN (5) THEN
79 UPDATE eng_change_lines
80 SET STATUS_CODE = G_ENG_NEW_ITEM_APPROVED
81 WHERE change_id = p_change_id
82 AND status_CODE not in(G_ENG_NEW_ITEM_REJECTED,G_ENG_NEW_ITEM_CANCELLED);
83
84 END IF;
85
86 /* ELSIF p_approval_status = 5 THEN
87
88 UPDATE_ITEM_APPROVAL_STATUS(l_items_array(i),
89 l_org_id_array(i),
90 l_approval_status); */
91
92 -- END IF;
93 END LOOP;
94
95
96 EXCEPTION WHEN others THEN
97
98 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
99 /*FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
100 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EGO_ITEM_PUB.UPDATE_ITEM_APPROVAL_STATUS '||'itemId: '||l_item_id||' OrgId: '||l_organization_id|| ' approvalStstus: '||l_approval_status);
101 FND_MSG_PUB.Add;*/
102
103 END set_nir_item_approval_status;
104
105 PROCEDURE Cancel_NIR(
106 p_change_id IN NUMBER,
107 p_org_id IN NUMBER,
108 p_change_notice IN VARCHAR2,
109 p_auto_commit IN VARCHAR2,
110 -- p_item_action IN VARCHAR2 DEFAULT NULL,
111 p_wf_user_id IN NUMBER,
112 p_fnd_user_id IN NUMBER,
113 p_cancel_comments IN VARCHAR2,
114 p_check_security IN BOOLEAN DEFAULT TRUE,
115 x_nir_cancel_status OUT NOCOPY VARCHAR2
116 )
117 IS
118 l_wf_item_type VARCHAR2(8);
119 l_wf_item_key VARCHAR2(240);
120 l_wf_process_name VARCHAR2(30);
121 l_return_status VARCHAR2(1);
122 l_msg_count NUMBER ;
123 l_msg_data VARCHAR2(200);
124 l_action_id eng_change_actions.action_id%TYPE;
125 l_chk_fn_ret_val VARCHAR2(10);
126 l_party_id NUMBER;
127 l_inventory_item_id VARCHAR2(100); -- ENG_CHANGE_SUBJECTS.PK1_VALUE
128 l_organization_id VARCHAR2(100); -- ENG_CHANGE_SUBJECTS.PK2_VALUE
129
130 BEGIN
131
132 x_nir_cancel_status := 'TRUE';
133 -- First check whether user has edit privilege on this change or not.
134 SELECT PERSON_ID into l_party_id FROM EGO_PEOPLE_V WHERE USER_ID = p_wf_user_id;
135 /*
136 IF p_check_security = TRUE THEN
137 l_chk_fn_ret_val := EGO_DATA_SECURITY.check_function
138 (
139 p_api_version=>1.0,
140 p_function => 'ENG_EDIT_CHANGE',
141 p_object_name => 'ENG_CHANGE',
142 p_instance_pk1_value => p_change_id,
143 -- p_instance_pk2_value => p_instance_pk2_value,
144 -- p_instance_pk3_value => p_instance_pk3_value,
145 -- p_instance_pk4_value => p_instance_pk4_value,
146 -- p_instance_pk5_value => p_instance_pk5_value,
147 p_user_name => 'HZ_PARTY:' || l_party_id
148 );
149
150 -- If the User does not have direct roles for Editing this NIR, We have to check for Inherited roles.
151 IF NOT 'T' = l_chk_fn_ret_val THEN
152 -- Get the Inventory Item Id and Organization Id of the Item
153 SELECT PK1_VALUE, PK2_VALUE INTO l_inventory_item_id, l_organization_id
154 FROM ENG_CHANGE_SUBJECTS
155 WHERE ENTITY_NAME = 'EGO_ITEM' AND CHANGE_ID = p_change_id;
156
157 l_chk_fn_ret_val := EGO_DATA_SECURITY.check_inherited_function
158 (
159 p_api_version => 1.0,
160 p_function => 'ENG_EDIT_CHANGE',
161 p_object_name => 'ENG_CHANGE',
162 p_instance_pk1_value => p_change_id,
163 -- p_instance_pk2_value => p_org_id,
164 -- p_instance_pk3_value => p_instance_pk3_value,
165 -- p_instance_pk4_value => p_instance_pk4_value,
166 -- p_instance_pk5_value => p_instance_pk5_value,
167 p_user_name => 'HZ_PARTY:' || l_party_id,
168 p_object_type => 'NEW_ITEM_REQUEST',
169 p_parent_object_name => 'EGO_ITEM',
170 p_parent_instance_pk1_value => l_inventory_item_id,
171 p_parent_instance_pk2_value => l_organization_id
172 -- p_parent_instance_pk3_value => p_parent_instance_pk3_value,
173 -- p_parent_instance_pk4_value => p_parent_instance_pk4_value,
174 -- p_parent_instance_pk5_value => p_parent_instance_pk5_value
175 );
176 END IF;
177 ELSE
178 l_chk_fn_ret_val := 'T';
179 END IF;
180
181 IF NOT 'T' = l_chk_fn_ret_val THEN
182 x_nir_cancel_status := 'ACCESS_DENIED';
183 RETURN;
184 END IF;
185 */
186 BEGIN
187 -- First check whether any workflow is running for this NIR.
188 -- If running, then abort the workflow.
189 SELECT WF_ITEM_TYPE, WF_ITEM_KEY, WF_PROCESS_NAME
190 INTO l_wf_item_type, l_wf_item_key, l_wf_process_name
191 FROM ENG_CHANGE_ROUTES WHERE OBJECT_ID1 = p_change_id AND status_code = 'IN_PROGRESS';
192
193 IF SQL%FOUND THEN
194
195 Eng_Workflow_Util.AbortWorkflow
196 (
197 p_api_version => 1.0
198 , p_init_msg_list => FND_API.G_FALSE
199 , p_commit => p_auto_commit
200 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
201 , x_return_status => l_return_status
202 , x_msg_count => l_msg_count
203 , x_msg_data => l_msg_data
204 , p_item_type => l_wf_item_type
205 , p_item_key => l_wf_item_key
206 , p_process_name => l_wf_process_name
207 , p_wf_user_id => p_wf_user_id
208 , p_debug => FND_API.G_FALSE
209 , p_output_dir => NULL
210 , p_debug_filename => 'Eng_ChangeWF_Abort.log'
211 );
212
213 END IF;
214
215 EXCEPTION
216 WHEN NO_DATA_FOUND THEN
217 NULL;
218 END;
219 -- Cancel the NIR
220 ENG_CANCEL_ECO.Cancel_Eco
221 (
222 org_id => p_org_id,
223 change_order => p_change_notice,
224 user_id => p_wf_user_id,
225 login => p_fnd_user_id
226 );
227
228 -- We need to set the status of the NIR Manually. Even in the UI flow it is set manually.
229 UPDATE ENG_ENGINEERING_CHANGES
230 SET STATUS_TYPE = 5, STATUS_CODE = 5, CANCELLATION_DATE = sysdate, CANCELLATION_COMMENTS = p_cancel_comments
231 WHERE CHANGE_ID = p_change_id;
232
233 x_nir_cancel_status := 'TRUE';
234
235 -- Create action log entries for the Cancel action.
236 ENG_CHANGE_ACTIONS_UTIL.Create_Change_Action
237 (
238 p_api_version => 1.0,
239 p_init_msg_list => FND_API.G_TRUE,
240 p_commit => FND_API.G_FALSE,
241 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
242 p_debug => FND_API.G_FALSE,
243 p_output_dir => '',
244 p_debug_filename => '',
245 x_return_status => l_return_status,
246 x_msg_count => l_msg_count,
247 x_msg_data => l_msg_data,
248 p_object_id1 => p_change_id,
249 p_object_id2 => NULL,
250 p_object_name => 'ENG_CHANGE',
251 p_action_type => 'CANCEL',
252 p_parent_action_id => -1,
253 p_change_description => p_cancel_comments,
254 x_change_action_id => l_action_id
255 );
256
257 EXCEPTION
258 WHEN OTHERS THEN
259 x_nir_cancel_status := 'FALSE';
260
261 END Cancel_NIR;
262
263
264 PROCEDURE Cancel_NIR_FOR_ITEM(
265 p_item_id IN NUMBER,
266 p_org_id IN NUMBER,
267 -- p_item_number IN VARCHAR2,
268 p_auto_commit IN VARCHAR2,
269 -- p_mode IN VARCHAR2,
270 p_wf_user_id IN NUMBER,
271 p_fnd_user_id IN NUMBER,
272 p_cancel_comments IN VARCHAR2,
273 p_check_security IN BOOLEAN DEFAULT TRUE,
274 x_nir_cancel_status OUT NOCOPY VARCHAR2
275 )
276
277 IS
278 l_uncancelled_change_id NUMBER;
279 CURSOR c_nirs_for_this_item IS
280 SELECT A.CHANGE_ID, B.CHANGE_NOTICE
281 FROM ENG_CHANGE_SUBJECTS A,
282 ENG_ENGINEERING_CHANGES B,
283 ENG_CHANGE_LINES LINES
284 WHERE A.PK1_VALUE = p_item_id
285 AND A.PK2_VALUE = p_org_id
286 AND A.ENTITY_NAME = 'EGO_ITEM'
287 AND A.CHANGE_LINE_ID = LINES.CHANGE_LINE_ID
288 AND LINES.CHANGE_ID = B.CHANGE_ID
289 AND A.CHANGE_ID = B.CHANGE_ID
290 AND LINES.STATUS_CODE NOT IN (G_ENG_NEW_ITEM_CANCELLED, G_ENG_NEW_ITEM_REJECTED);
291
292 l_chk_fn_ret_val VARCHAR2(10);
293 l_party_id NUMBER;
294 l_cancel_comments VARCHAR2(32767);
295
296 BEGIN
297 -- p_check_security := TRUE;
298 SELECT PERSON_ID into l_party_id FROM EGO_PEOPLE_V WHERE USER_ID = p_wf_user_id;
299
300 -- Query all the NIRs for this Item in this Organization and Cancel them.
301 FOR nirs IN c_nirs_for_this_item
302 LOOP
303
304 -- First check whether the user has cancel/edit privilege to cancel the line or NIR
305 IF p_check_security = TRUE THEN
306 l_chk_fn_ret_val := EGO_DATA_SECURITY.check_function
307 (
308 p_api_version=>1.0,
309 p_function => 'ENG_EDIT_CHANGE',
310 p_object_name => 'ENG_CHANGE',
311 p_instance_pk1_value => nirs.change_id,
312 -- p_instance_pk2_value => p_instance_pk2_value,
313 -- p_instance_pk3_value => p_instance_pk3_value,
314 -- p_instance_pk4_value => p_instance_pk4_value,
315 -- p_instance_pk5_value => p_instance_pk5_value,
316 p_user_name => 'HZ_PARTY:' || l_party_id
317 );
318
319 -- If the User does not have direct roles for Editing this NIR, We have to check for Inherited roles.
320 IF NOT 'T' = l_chk_fn_ret_val THEN
321 -- Get the Inventory Item Id and Organization Id of the Item
322 l_chk_fn_ret_val := EGO_DATA_SECURITY.check_inherited_function
323 (
324 p_api_version => 1.0,
325 p_function => 'ENG_EDIT_CHANGE',
326 p_object_name => 'ENG_CHANGE',
327 p_instance_pk1_value => nirs.change_id,
328 -- p_instance_pk2_value => p_org_id,
329 -- p_instance_pk3_value => p_instance_pk3_value,
330 -- p_instance_pk4_value => p_instance_pk4_value,
331 -- p_instance_pk5_value => p_instance_pk5_value,
332 p_user_name => 'HZ_PARTY:' || l_party_id,
333 p_object_type => 'NEW_ITEM_REQUEST',
334 p_parent_object_name => 'EGO_ITEM',
335 p_parent_instance_pk1_value => p_item_id,
336 p_parent_instance_pk2_value => p_org_id
337 -- p_parent_instance_pk3_value => p_parent_instance_pk3_value,
338 -- p_parent_instance_pk4_value => p_parent_instance_pk4_value,
339 -- p_parent_instance_pk5_value => p_parent_instance_pk5_value
340 );
341 END IF;
342 ELSE
343 l_chk_fn_ret_val := 'T';
344 END IF;
345 IF NOT 'T' = l_chk_fn_ret_val THEN
346 x_nir_cancel_status := 'ACCESS_DENIED';
347 RETURN;
348 END IF;
349 Cancel_NIR_Line_Item( p_change_id => nirs.change_id,
350 p_item_id => p_item_id,
351 p_org_id => p_org_id,
352 -- p_mode => p_item_action, -- (DELETE/CHANGE_ICC)
353 p_wf_user_id => p_wf_user_id,
354 p_fnd_user_id => p_fnd_user_id,
355 p_cancel_comments => p_cancel_comments,
356 P_COMMIT => FND_API.G_FALSE,
357 x_return_status => x_nir_cancel_status
358 );
359 select count(change_id) into l_uncancelled_change_id
360 from eng_change_lines where change_id = nirs.change_id
361 and status_code <> 5;
362 if l_uncancelled_change_id = 0
363 then
364
365 FND_MESSAGE.SET_NAME('ENG', 'ENG_NIR_CANCELLED_COMMENT');
366 l_cancel_comments := FND_MESSAGE.GET;
367
368 Cancel_NIR(p_change_id => nirs.CHANGE_ID,
369 p_org_id => p_org_id,
370 p_change_notice => nirs.CHANGE_NOTICE,
371 p_auto_commit => p_auto_commit,
372 p_wf_user_id => p_wf_user_id,
373 p_fnd_user_id => p_fnd_user_id,
374 p_cancel_comments => l_cancel_comments,
375 -- p_check_security => p_check_security,
376 p_check_security => TRUE,
377 x_nir_cancel_status => x_nir_cancel_status
378 );
379 end if;
380 END LOOP;
381
382 END Cancel_NIR_FOR_ITEM;
383
384 PROCEDURE Delete_Child_Associations(
385 p_parent_icc_id IN NUMBER,
386 p_item_catalog_group_ids IN VARCHAR2,
387 p_route_people_id IN NUMBER DEFAULT NULL,
388 p_attribute_group_id IN NUMBER DEFAULT NULL,
389 p_commit IN VARCHAR2
390 )
391 IS
392 l_delete_assocs_stmt VARCHAR2(32767);
393 i NUMBER;
394 l_child_item_catalog_group_id VARCHAR2(80);
395 l_parent_item_catalog_group_id VARCHAR2(1000);
396 l_parent_assoc_creation_date VARCHAR2(1000);
397 l_parent_route_people_id NUMBER;
398 l_parent_attr_group_id NUMBER;
399
400 CURSOR cur_child_icc_assocs IS
401 SELECT assoc_obj_pk1_value, route_people_id, object_id1, To_Char(CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') creation_date
402 FROM ENG_CHANGE_ROUTE_ASSOCS WHERE ASSOC_OBJ_PK1_VALUE = p_parent_icc_id;
403
404 BEGIN
405 IF p_parent_icc_id IS NOT NULL THEN
406 -- Following code will be called when the AG association is deleted from the ICC directly
407 IF p_route_people_id IS NOT NULL AND p_attribute_group_id IS NOT NULL THEN
408 i := 1;
409 LOOP
410 l_child_item_catalog_group_id := Tokenize( p_item_catalog_group_ids, i , ',') ;
411 EXIT WHEN l_child_item_catalog_group_id IS NULL ;
412
413 -- We need to check whether the same AG is not associated for the Child ICC directly
414 -- If AG is associated directly to the Child ICC then the creation date will be different as parent's
415 -- else creation date will be same as parent's
416 SELECT To_Char(CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') INTO l_parent_assoc_creation_date FROM ENG_CHANGE_ROUTE_ASSOCS
417 WHERE ASSOC_OBJ_PK1_VALUE = p_parent_icc_id
418 AND ROUTE_PEOPLE_ID = p_route_people_id
419 AND OBJECT_ID1 = p_attribute_group_id;
420
421 DELETE FROM ENG_CHANGE_ROUTE_ASSOCS
422 WHERE ASSOC_OBJ_PK1_VALUE = l_child_item_catalog_group_id
423 AND ASSOC_OBJ_PK1_VALUE <> p_parent_icc_id
424 AND ROUTE_PEOPLE_ID = p_route_people_id
425 AND OBJECT_ID1 = p_attribute_group_id
426 AND To_Char(CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') = l_parent_assoc_creation_date; -- Delete only if AG assoc is inherited : if inherited then the creation date will be same
427 -- AND CREATION_DATE = l_parent_assoc_creation_date;
428 -- AND ADHOC_ASSOC_FLAG = 'N'; -- Child ICCs can add more associations, those should not be deleted
429
430 i := i + 1;
431 END LOOP;
432 -- EXECUTE IMMEDIATE l_delete_assocs_stmt USING p_item_catalog_group_ids, p_route_people_id, p_attribute_group_id;
433 ELSE
434 -- Following code will be called from API when the ICC NIR setp is changed
435 FOR rec IN cur_child_icc_assocs
436 LOOP
437 DELETE FROM ENG_CHANGE_ROUTE_ASSOCS WHERE ROUTE_ASSOCIATION_ID IN (
438 SELECT ROUTE_ASSOCIATION_ID FROM (
439 SELECT a.route_association_id,
440 b.item_catalog_group_id,
441 b.parent_catalog_group_id,
442 b.NEW_ITEM_REQUEST_REQD
443 FROM eng_change_route_assocs a, mtl_item_catalog_groups_v b
444 WHERE a.assoc_obj_pk1_value = b.item_catalog_group_id
445 AND a.object_id1= rec.object_id1
446 AND To_Char(a.CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') = rec.creation_date
447 ) CONNECT BY PRIOR item_catalog_group_id = parent_catalog_group_id AND NEW_ITEM_REQUEST_REQD = 'I'
448 START WITH item_catalog_group_id = rec.assoc_obj_pk1_value );
449 END LOOP;
450 END IF;
451 END IF;
452
453 -- IF p_commit = 'TRUE' THEN
454 -- commit;
455 -- END IF;
456
457 EXCEPTION
458 WHEN OTHERS THEN
459 NULL;
460
461 END Delete_Child_Associations;
462
463 -- Duplicate Validation still to be done while creating the associations like this. Done
464 /*
465 First Part of the Following API will be called when the ICC NIR setup is changed
466 This will do a mass creation of the AG Associations for all the Child ICCs given
467 Second Part of the Following API will be created when an attribute group association is created for an ICC
468 It will propagate the association to all the ICC's child ICCs.
469
470 */
471 PROCEDURE Create_Child_Associations(
472 p_source_item_catalog_group_id IN VARCHAR2,
473 p_parent_item_catalog_group_id IN VARCHAR2,
474 p_child_item_catalog_group_ids IN VARCHAR2,
475 -- following parameters will be used while calling only when the AG is associated to ICC directly
476 p_route_people_id IN NUMBER DEFAULT NULL,
477 p_attribute_group_id IN NUMBER DEFAULT NULL,
478 p_assoc_creation_date IN DATE DEFAULT NULL,
479 p_assoc_created_by IN NUMBER DEFAULT NULL,
480 p_assoc_last_update_date IN DATE DEFAULT NULL,
481 p_assoc_last_update_login IN NUMBER DEFAULT NULL,
482 p_assoc_last_updated_by IN NUMBER DEFAULT NULL,
483 p_commit IN VARCHAR2
484 )
485 IS
486 l_create_assocs_stmt VARCHAR2(32767);
487 l_child_item_catalog_group_id VARCHAR2(1000);
488 i NUMBER;
489 k NUMBER;
490 l_route_association_id ENG_CHANGE_ROUTE_ASSOCS.ROUTE_ASSOCIATION_ID%TYPE;
491 l_source_icc_id VARCHAR2(1000);
492 l_child_icc_ids VARCHAR2(32767);
493
494 CURSOR cur_parent_icc_ag_associations IS
495 SELECT
496 ROUTE_PEOPLE_ID, ASSOC_OBJECT_NAME, ASSOC_OBJ_PK1_VALUE, ASSOC_OBJ_PK2_VALUE, ASSOC_OBJ_PK3_VALUE, ASSOC_OBJ_PK4_VALUE,
497 ASSOC_OBJ_PK5_VALUE, ADHOC_ASSOC_FLAG, OBJECT_NAME, OBJECT_ID1, OBJECT_ID2, OBJECT_ID3, OBJECT_ID4, OBJECT_ID5, CREATION_DATE,
498 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID,
499 PROGRAM_UPDATE_DATE, ORIGINAL_SYSTEM_REFERENCE
500 FROM ENG_CHANGE_ROUTE_ASSOCS
501 WHERE ASSOC_OBJ_PK1_VALUE = l_source_icc_id;
502
503 BEGIN
504 IF p_source_item_catalog_group_id IS NOT NULL THEN
505 -- p_source_item_catalog_group_id This will not be null when an ICC NIR setup is set to Inherit From Parent and
506 -- it has a Parent ICC with some associations and some child ICCs. Then the Parent's associations should be copied
507 -- to it and its Children
508 l_source_icc_id := p_source_item_catalog_group_id;
509 l_child_icc_ids := p_child_item_catalog_group_ids;
510 -- Check if there are child ICCs for which the association to be propagated, if so append
511 /*
512 IF p_child_item_catalog_group_ids IS NULL OR p_child_item_catalog_group_ids = '' THEN
513 l_child_icc_ids := p_parent_item_catalog_group_id;
514 ELSE
515 l_child_icc_ids := p_child_item_catalog_group_ids || ',' || p_parent_item_catalog_group_id;
516 END IF;
517 */
518 ELSE
519 l_source_icc_id := p_parent_item_catalog_group_id;
520 l_child_icc_ids := p_child_item_catalog_group_ids;
521 END IF;
522 -- First Part : Mass Update
523 IF l_child_icc_ids IS NOT NULL THEN
524
525 i := 1;
526 -- Query all the Parent ICC Associations.
527 IF p_route_people_id IS NULL AND p_attribute_group_id IS NULL THEN
528 FOR ag_association IN cur_parent_icc_ag_associations LOOP
529 i := 1;
530 LOOP
531 -- For each Child ICC, create the attribute groups associations.
532 l_child_item_catalog_group_id := Tokenize( l_child_icc_ids, i , ',') ;
533 EXIT WHEN l_child_item_catalog_group_id IS NULL ;
534 IF ag_association.object_id1 IS NOT NULL THEN
535 -- First check if there is already an association for this AG at the Child ICC.
536 -- Association Unique combination (ASSOC_OBJ_PK1_VALUE, ROUTE_PEOPLE_ID, OBJECT_ID1)
537 SELECT count(route_association_id) INTO k
538 FROM ENG_CHANGE_ROUTE_ASSOCS
539 WHERE ASSOC_OBJ_PK1_VALUE = l_child_item_catalog_group_id
540 AND ROUTE_PEOPLE_ID = ag_association.route_people_id
541 AND OBJECT_ID1 = ag_association.object_id1;
542 IF k = 0 OR k IS NULL THEN -- Create association only if it already does not exist
543 -- Get the Association Id from Sequence.
544 SELECT ENG_CHANGE_ROUTE_ASSOCS_S.NEXTVAL INTO l_route_association_id FROM DUAL;
545 -- For each Parent ICC's association, create an association in the Child ICC.
546 INSERT INTO ENG_CHANGE_ROUTE_ASSOCS (ROUTE_ASSOCIATION_ID, ROUTE_PEOPLE_ID, ASSOC_OBJECT_NAME, ASSOC_OBJ_PK1_VALUE,
547 ASSOC_OBJ_PK2_VALUE, ASSOC_OBJ_PK3_VALUE, ASSOC_OBJ_PK4_VALUE, ASSOC_OBJ_PK5_VALUE, ADHOC_ASSOC_FLAG,
548 OBJECT_NAME, OBJECT_ID1, OBJECT_ID2, OBJECT_ID3, OBJECT_ID4, OBJECT_ID5, CREATION_DATE, CREATED_BY,
549 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID,
550 PROGRAM_UPDATE_DATE, ORIGINAL_SYSTEM_REFERENCE)
551 VALUES (l_route_association_id, ag_association.route_people_id, ag_association.assoc_object_name,
552 l_child_item_catalog_group_id, ag_association.assoc_obj_pk2_value, ag_association.assoc_obj_pk3_value,
553 ag_association.assoc_obj_pk4_value, ag_association.assoc_obj_pk5_value, 'N',
554 ag_association.object_name, ag_association.object_id1, ag_association.object_id2, ag_association.object_id3,
555 ag_association.object_id4, ag_association.object_id5, ag_association.creation_date, ag_association.created_by,
556 ag_association.last_update_date, ag_association.last_updated_by, ag_association.last_update_login,
557 ag_association.request_id, ag_association.program_id, ag_association.program_application_id,
558 ag_association.program_update_date, ag_association.original_system_reference);
559 END IF; -- if k = 0 then
560 END IF;
561 i := i + 1;
562 k := 0;
563 END LOOP; -- Child ICCs
564 END LOOP ; -- Parent's associations : For Cursor Loop
565 END IF;
566
567 -- Second Part : Single Attribute group creation propagation
568 -- Will be called when an attribute group is associated for the parent ICC
569 IF p_route_people_id IS NOT NULL AND p_attribute_group_id IS NOT NULL THEN
570 i := 1;
571 LOOP
572 l_child_item_catalog_group_id := Tokenize( l_child_icc_ids, i , ',') ;
573 EXIT WHEN l_child_item_catalog_group_id IS NULL ;
574 -- First check if there is already an association for this AG at the Child ICC.
575 -- Association Unique combination (ASSOC_OBJ_PK1_VALUE, ROUTE_PEOPLE_ID, OBJECT_ID1)
576 SELECT count(route_association_id) INTO k
577 FROM ENG_CHANGE_ROUTE_ASSOCS
578 WHERE ASSOC_OBJ_PK1_VALUE = l_child_item_catalog_group_id
579 AND ROUTE_PEOPLE_ID = p_route_people_id
580 AND OBJECT_ID1 = p_attribute_group_id;
581 IF (k = 0 OR k IS NULL) AND p_parent_item_catalog_group_id <> l_child_item_catalog_group_id THEN -- Create association only if it already does not exist
582 SELECT ENG_CHANGE_ROUTE_ASSOCS_S.NEXTVAL INTO l_route_association_id FROM DUAL;
583 -- For each Parent ICC's association, create an association in the Child ICC.
584 INSERT INTO ENG_CHANGE_ROUTE_ASSOCS (ROUTE_ASSOCIATION_ID, ROUTE_PEOPLE_ID, ASSOC_OBJECT_NAME, ASSOC_OBJ_PK1_VALUE,
585 ADHOC_ASSOC_FLAG, OBJECT_NAME, OBJECT_ID1, OBJECT_ID2, OBJECT_ID3, OBJECT_ID4, OBJECT_ID5,
586 CREATION_DATE , CREATED_BY , LAST_UPDATE_DATE,
587 LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
588 VALUES (l_route_association_id, p_route_people_id, 'EGO_CATALOG_GROUP', l_child_item_catalog_group_id, 'N',
589 'EGO_ITEM_ATTR_GROUP', p_attribute_group_id, 0,0,0,0, p_assoc_creation_date, p_assoc_created_by,
590 p_assoc_last_update_date, p_assoc_last_update_login, p_assoc_last_updated_by);
591 END IF;
592 i := i + 1;
593 k := 0;
594 END LOOP;
595 END IF;
596 END IF;
597 EXCEPTION
598 WHEN OTHERS THEN
599 NULL;
600 END Create_Child_Associations;
601
602 PROCEDURE Update_Child_Associations(
603 p_parent_item_catalog_group_id IN VARCHAR2,
604 p_child_item_catalog_group_ids IN VARCHAR2,
605 p_route_people_id IN NUMBER DEFAULT NULL,
606 p_attribute_group_id IN NUMBER DEFAULT NULL,
607 p_route_association_id IN NUMBER,
608 p_commit IN VARCHAR2
609 )
610 IS
611 l_old_attr_group_id NUMBER;
612 i NUMBER;
613 l_child_item_catalog_group_id VARCHAR2(1000);
614 BEGIN
615 -- Get the old attribute groups associated
616 SELECT object_id1 INTO l_old_attr_group_id FROM ENG_CHANGE_ROUTE_ASSOCS WHERE route_association_id = p_route_association_id;
617 i := 1;
618 LOOP
619 l_child_item_catalog_group_id := Tokenize( p_child_item_catalog_group_ids, i , ',') ;
620 EXIT WHEN l_child_item_catalog_group_id IS NULL ;
621
622 IF p_attribute_group_id IS NULL THEN -- When attribute group association lov field is cleared
623 DELETE FROM ENG_CHANGE_ROUTE_ASSOCS
624 WHERE ASSOC_OBJ_PK1_VALUE = l_child_item_catalog_group_id
625 AND ASSOC_OBJ_PK1_VALUE <> p_parent_item_catalog_group_id
626 AND ROUTE_PEOPLE_ID = p_route_people_id
627 AND OBJECT_ID1 = l_old_attr_group_id;
628 ELSE
629 UPDATE ENG_CHANGE_ROUTE_ASSOCS
630 SET OBJECT_ID1 = p_attribute_group_id
631 WHERE ASSOC_OBJ_PK1_VALUE = l_child_item_catalog_group_id
632 AND ASSOC_OBJ_PK1_VALUE <> p_parent_item_catalog_group_id
633 AND ROUTE_PEOPLE_ID = p_route_people_id;
634 END IF;
635
636 i := i + 1;
637 END LOOP;
638
639 END Update_Child_Associations;
640
641
642 FUNCTION Tokenize
643 (
644 p_string IN VARCHAR2, -- input string
645 p_start_position IN NUMBER, -- token number
646 p_seperator IN VARCHAR2 DEFAULT ',' -- separator character
647 )
648 RETURN VARCHAR2
649 IS
650 l_string VARCHAR2(32767) := p_seperator || p_string ;
651 l_position NUMBER ;
652 l_position2 NUMBER ;
653 BEGIN
654 l_position := INSTR( l_string, p_seperator, 1, p_start_position ) ;
655 IF l_position > 0 THEN
656 l_position2 := INSTR( l_string, p_seperator, 1, p_start_position + 1) ;
657 IF l_position2 = 0 THEN
658 l_position2 := LENGTH( l_string ) + 1 ;
659 END IF ;
660 RETURN( SUBSTR( l_string, l_position+1, l_position2 - l_position-1 ) ) ;
661 ELSE
662 RETURN NULL ;
663 END IF ;
664 END;
665
666
667 PROCEDURE Cancel_NIR_Line_Item(
668 p_change_id NUMBER,
669 p_item_id NUMBER,
670 p_org_id NUMBER,
671 -- p_mode VARCHAR2, -- (DELETE/CHANGE_ICC)
672 p_wf_user_id IN NUMBER,
673 p_fnd_user_id IN NUMBER,
674 p_cancel_comments IN VARCHAR2,
675 p_commit IN VARCHAR2 := FND_API.G_FALSE,
676 x_return_status OUT NOCOPY VARCHAR2
677 )
678 IS
679 l_change_line_id NUMBER;
680 l_lines_count NUMBER;
681 l_change_notice ENG_ENGINEERING_CHANGES.CHANGE_NOTICE%TYPE;
682 l_return_status VARCHAR2(1);
683 l_msg_count NUMBER ;
684 l_msg_data VARCHAR2(200);
685 l_action_id eng_change_actions.action_id%TYPE;
686 l_change_status_code eng_engineering_changes.status_code%TYPE;
687 BEGIN
688 -- First check whether the change(NIR) is in Draft status. If so the remove the line and subjects
689 SELECT status_code INTO l_change_status_code FROM eng_engineering_changes WHERE change_id = p_change_id;
690
691 IF l_change_status_code = 0 THEN -- If Draft then
692 -- Delete the line and subject in the NIR since it is in draft
693 SELECT change_line_id INTO l_change_line_id FROM eng_change_subjects
694 WHERE change_id = p_change_id
695 AND pk1_value = p_item_id
696 AND pk2_value = p_org_id
697 AND entity_name = 'EGO_ITEM';
698
699 DELETE FROM eng_change_subjects WHERE change_id = p_change_id ;
700 -- AND pk1_value = p_item_id
701 -- AND pk2_value = p_org_id
702 -- AND entity_name = 'EGO_ITEM';
703
704 DELETE FROM eng_change_lines WHERE change_id = p_change_id and change_line_id = l_change_line_id;
705 DELETE FROM eng_change_lines_tl WHERE change_line_id = l_change_line_id;
706 ELSE
707 -- Query change line id
708 SELECT change_line_id INTO l_change_line_id FROM eng_change_subjects
709 WHERE change_id = p_change_id
710 AND pk1_value = p_item_id
711 AND pk2_value = p_org_id
712 AND entity_name = 'EGO_ITEM';
713 -- Set change line status to Cancelled
714 UPDATE ENG_CHANGE_LINES SET status_code = 5 -- Cancelled
715 WHERE change_id = p_change_id AND change_line_id = l_change_line_id;
716 -- Update the Action Log of the NIR.
717 ENG_CHANGE_ACTIONS_UTIL.Create_Change_Action
718 (
719 p_api_version => 1.0,
720 p_init_msg_list => FND_API.G_TRUE,
721 p_commit => FND_API.G_FALSE,
722 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
723 p_debug => FND_API.G_FALSE,
724 p_output_dir => '',
725 p_debug_filename => '',
726 x_return_status => l_return_status,
727 x_msg_count => l_msg_count,
728 x_msg_data => l_msg_data,
729 p_object_id1 => p_change_id,
730 p_object_id2 => l_change_line_id,
731 p_object_name => 'ENG_NEW_ITEM_REQUEST_LINES',
732 p_action_type => 'CANCEL',
733 p_status_code => 5, -- Cancelled
734 p_parent_action_id => -1,
735 p_change_description => p_cancel_comments,
736 x_change_action_id => l_action_id
737 );
738 -- Check if this is the only Line in the NIR.
739 END IF;
740
741 x_return_status := 'TRUE';
742
743 EXCEPTION
744 WHEN OTHERS THEN
745 x_return_status := 'FALSE';
746
747 END Cancel_NIR_Line_Item;
748
749 /*
750 Added for R12C Enhancements
751 Now, since there can be more than one line items in an NIR, we have to query the item using change_line_id.
752 This method is called when the Line Item in an NIR is Rejected.
753 This method calls the Items API to change the Approval Status of the Item.
754 */
755 PROCEDURE Update_Item_Approval_Status (
756 p_change_id IN NUMBER,
757 p_change_line_id IN NUMBER,
758 p_approval_status IN NUMBER,
759 x_return_status OUT NOCOPY VARCHAR2)
760 IS
761 l_item_id NUMBER;
762 l_organization_id NUMBER;
763 l_approval_status VARCHAR2(1);
764 l_ret_item_app_st INTEGER;
765 BEGIN
766 x_return_status := FND_API.G_RET_STS_SUCCESS;
767 IF p_approval_status = 1 THEN
768 l_approval_status := 'N';
769 ELSIF p_approval_status = 3 THEN
770 l_approval_status := 'S';
771 ELSIF p_approval_status IN (4, 8, 14) THEN -- 14 = Rejected
772 l_approval_status := 'R';
773 ELSIF p_approval_status = 5 THEN
774 l_approval_status := 'A';
775 END IF;
776 /* if p_change_line_ids is not null
777 then
778 for i in p_change_line_ids.FIRST .. p_change_line_ids.LAST
779 LOOP
780 /* get item_id and organization_id */
781 SELECT to_number(pk1_value), to_number(pk2_value)
782 INTO l_item_id, l_organization_id
783 FROM eng_change_subjects
784 WHERE change_line_id = p_change_line_id
785 AND entity_name='EGO_ITEM';
786 /* call API to update approval status on the Item to approved*/
787
788 -- l_ret_item_app_st := EGO_ITEM_PUB.UPDATE_ITEM_APPROVAL_STATUS(l_item_id,l_organization_id, l_approval_status);
789 EGO_ITEM_PUB.UPDATE_ITEM_APPROVAL_STATUS(l_item_id,l_organization_id, l_approval_status, p_change_id);
790 --END LOOP;
791 -- end if;
792
793 EXCEPTION WHEN others THEN
794
795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
796 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
797 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EGO_ITEM_PUB.UPDATE_ITEM_APPROVAL_STATUS '||'itemId: '||l_item_id||' OrgId: '||l_organization_id|| ' approvalStstus: '||l_approval_status || ' changeId: ' || p_change_id);
798 FND_MSG_PUB.Add;
799
800 END Update_Item_Approval_Status;
801
802 FUNCTION checkNIRValidForApproval( p_change_id IN NUMBER)
803 return boolean
804 is
805 l_change_line_id number;
806 BEGIN
807 select change_line_id
808 into l_change_line_id
809 from eng_change_lines
810 where change_id = p_change_id
811 and status_code in( 1,3)
812 and rownum=1;
813
814 return ( l_change_line_id is not null);
815
816 EXCEPTION WHEN NO_DATA_FOUND THEN
817 return false;
818
819 END checkNIRValidForApproval;
820
821 /*
822 This method will be called from ENG_CHANGE_LIFECYCLE_UTIL.Update_Header_Appr_Status() procedure
823 This method should be called when the Workflow is aborted in the Approval phase and when the NIR is demoted to Approval phase
824 All the Lines and Line Items status and approval status respectively should be reset to Open and Submitted for Approval respectively.
825 This method will reset the Approval Status of Items and Status of Lines which are Rejected using the NIR.
826
827 Spl Test Case : If there is another NIR with one of the line items and submitted since it is rejected in previous NIR,
828 and if the previous NIR Workflow is restarted, then the Line Status and Item Approval status will not be reset because it has to
829 be tracked with the other NIR.
830 Test Case :
831 SNIR_277 (Line1 - Item1)
832 Line is Rejected
833 NIR is Approved
834 SNIR_279 (Line1 - Item1) Submitted, Item - SFA
835 SNIR_277 Wf is Restarted
836 Result : Should not update the Item App Status and Line Status in SNIR_277. SNIR_279 should be used to track the Item1.
837 */
838 PROCEDURE Update_Line_Items_App_St(
839 p_change_id IN NUMBER,
840 p_item_approval_status IN NUMBER,
841 x_sfa_line_items_exists OUT NOCOPY VARCHAR2
842 )
843 IS
844 CURSOR cur_line_items_in_nir IS
845 SELECT change_line_id, pk1_value, pk2_value, pk3_value FROM ENG_CHANGE_SUBJECTS WHERE change_id = p_change_id
846 AND ENTITY_NAME='EGO_ITEM';
847
848 l_ret_status VARCHAR2(10);
849 l_change_line_id ENG_CHANGE_SUBJECTS.change_line_id%TYPE;
850 l_pk1_value ENG_CHANGE_SUBJECTS.pk1_value%TYPE;
851 l_pk2_value ENG_CHANGE_SUBJECTS.pk2_value%TYPE;
852 l_pk3_value ENG_CHANGE_SUBJECTS.pk3_value%TYPE;
853 l_item_approval_status MTL_SYSTEM_ITEMS.approval_status%TYPE;
854 l_sfa_item VARCHAR2(1000);
855
856 BEGIN
857 x_sfa_line_items_exists := '';
858 -- Get all the Line Items in the NIR
859 FOR line_items IN cur_line_items_in_nir
860 LOOP
861 l_change_line_id := line_items.change_line_id;
862 l_pk1_value := line_items.pk1_value;
863 l_pk2_value := line_items.pk2_value;
864 l_pk3_value := line_items.pk3_value;
865
866 BEGIN
867 -- We need to reset the Line status and Item approval status only for the Lines rejected in the NIR.
868 SELECT approval_status INTO l_item_approval_status FROM MTL_SYSTEM_ITEMS WHERE inventory_item_id = l_pk1_value AND organization_id = l_pk2_value;
869 IF 'R' = l_item_approval_status THEN
870 -- Update Item Approval Status
871 Update_Item_Approval_Status(p_change_id, line_items.change_line_id, p_item_approval_status, l_ret_status);
872 -- Update Line Item Status
873 UPDATE eng_change_lines SET STATUS_CODE = 1 WHERE change_line_id = l_change_line_id;
874 ELSE
875 SELECT CONCATENATED_SEGMENTS INTO l_sfa_item
876 FROM MTL_SYSTEM_ITEMS_KFV
877 WHERE inventory_item_id = l_pk1_value
878 AND organization_id = l_pk2_value
879 AND LAST_SUBMITTED_NIR_ID <> p_change_id;
880
881 IF x_sfa_line_items_exists IS NOT NULL AND l_sfa_item IS NOT NULL THEN
882 x_sfa_line_items_exists := x_sfa_line_items_exists || ' , ';
883 END IF;
884 x_sfa_line_items_exists := x_sfa_line_items_exists || l_sfa_item;
885 END IF;
886
887 EXCEPTION
888 WHEN OTHERS THEN
889 NULL;
890 END;
891 END LOOP;
892
893 END Update_Line_Items_App_St;
894
895 END ENG_NIR_UTIL_PKG;
[Home] [Help]