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