[Home] [Help]
PACKAGE BODY: APPS.ENG_NEW_ITEM_REQ_UTIL
Source
1 PACKAGE BODY ENG_NEW_ITEM_REQ_UTIL AS
2 /* $Header: ENGUNIRB.pls 120.14 2007/07/09 09:51:28 sdarbha ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_NEW_ITEM_REQ_UTIL';
6 G_NIR_ICC_OPTION CONSTANT VARCHAR2(30) := 'C';
7 G_NIR_IND_ITEM_OPTION CONSTANT VARCHAR2(30) := 'I';
8
9
10 PROCEDURE Create_New_Item_Request
11 (
12 x_return_status OUT NOCOPY VARCHAR2,
13 change_number IN VARCHAR2, --10
14 change_name IN VARCHAR2, --240
15 change_type_code IN VARCHAR2, --80
16 item_number IN VARCHAR2, --240
17 organization_code IN VARCHAR2, --3
18 requestor_user_name IN VARCHAR2, --100
19 batch_id IN NUMBER := null
20 )
21 IS
22
23 l_change_id NUMBER ;
24
25 BEGIN
26
27 Create_New_Item_Request
28 ( x_return_status => x_return_status
29 , x_change_id => l_change_id
30 , change_number => change_number
31 , change_name => change_name
32 , change_type_code => change_type_code
33 , item_number => item_number
34 , organization_code => organization_code
35 , requestor_user_name => requestor_user_name
36 , batch_id => batch_id
37 ) ;
38
39 END Create_New_Item_Request ;
40
41
42 -- Added in R12
43 -- Item API will put returned NIR change id
44 -- to Item Open Interface table
45 PROCEDURE Create_New_Item_Request
46 (
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_change_id OUT NOCOPY NUMBER,
49 change_number IN VARCHAR2, --10
50 change_name IN VARCHAR2, --240
51 change_type_code IN VARCHAR2, --80
52 item_number IN VARCHAR2 ,--240
53 organization_code IN VARCHAR2, --3
54 requestor_user_name IN VARCHAR2, --100
55 batch_id IN NUMBER default null
56 )
57 IS
58 l_return_status varchar2(1);
59 l_msg_count number := 0;
60 l_eco_rec ENG_ECO_Pub.Eco_Rec_Type;
61 l_out_eco_rec ENG_Eco_PUB.Eco_Rec_Type := ENG_ECO_Pub.G_MISS_ECO_REC;
62 l_out_eco_rev_tbl ENG_Eco_PUB.Eco_Revision_Tbl_Type := ENG_ECO_Pub.G_MISS_ECO_REVISION_TBL;
63 l_out_rev_item_tbl ENG_Eco_PUB.Revised_Item_Tbl_Type := ENG_ECO_Pub.G_MISS_REVISED_ITEM_TBL;
64 l_out_rev_comp_tbl BOM_BO_PUB.Rev_Component_Tbl_Type := ENG_ECO_Pub.G_MISS_REV_COMPONENT_TBL;
65 l_out_ref_des_tbl BOM_BO_PUB.Ref_Designator_Tbl_Type := ENG_ECO_Pub.G_MISS_REF_DESIGNATOR_TBL;
66 l_out_sub_comp_tbl BOM_BO_PUB.Sub_Component_Tbl_Type := ENG_ECO_Pub.G_MISS_SUB_COMPONENT_TBL;
67 l_out_rev_operation_tbl Bom_Rtg_Pub.Rev_Operation_Tbl_Type := ENG_ECO_Pub.G_MISS_REV_OPERATION_TBL;
68 l_out_rev_op_resource_tbl Bom_Rtg_Pub.Rev_Op_Resource_Tbl_Type := ENG_ECO_Pub.G_MISS_REV_OP_RESOURCE_TBL;
69 l_out_rev_sub_resource_tbl Bom_Rtg_Pub.Rev_Sub_Resource_Tbl_Type := ENG_ECO_Pub.G_MISS_REV_SUB_RESOURCE_TBL;
70 l_message_text varchar2(2000);
71 l_entity_index number;
72 l_entity_id varchar2(10);
73 l_message_type varchar2(30);
74 change_mgmt_type_name varchar2(80);
75 l_change_order_type_id number;
76 stat_name varchar2(80);
77
78 l_type_id NUMBER;
79 l_auto_number_method eng_change_order_types.AUTO_NUMBERING_METHOD%TYPE;
80 l_change_number eng_engineering_changes.CHANGE_NOTICE%TYPE := NULL;
81
82 l_change_type_name eng_change_order_types_tl.type_name%TYPE := NULL;
83 l_hist_rec_row_id VARCHAR2(100);
84 l_change_mgmt_type_id eng_change_order_types_tl.change_order_type_id%TYPE;
85
86 CURSOR c_change_type_detail(l_change_order_type_id IN NUMBER)
87 IS
88 SELECT ecotv1.change_order_type_id change_mgmt_type_id,
89 ecotv2.change_order_type_id,
90 ecotv2.auto_numbering_method,
91 ecotv1.auto_numbering_method change_mgmt_method
92 FROM eng_change_order_types_vl ecotv1, eng_change_order_types_vl ecotv2
93 WHERE ecotv1.change_order_type_id = l_change_order_type_id
94 AND ecotv1.TYPE_CLASSIFICATION= 'CATEGORY'
95 AND ecotv1.change_mgmt_type_code = ecotv2.change_mgmt_type_code
96 AND ecotv2.type_name = change_type_code
97 AND ecotv2.TYPE_CLASSIFICATION = 'HEADER';
98
99
100
101 CURSOR c_change_id (p_change_notice IN VARCHAR2, p_org_code IN VARCHAR2)
102 IS
103 SELECT CHANGE_ID
104 FROM mtl_parameters org_param
105 , eng_engineering_changes eng_change
106 WHERE eng_change.change_notice = p_change_notice
107 AND eng_change.organization_id = org_param.organization_id
108 AND org_param.organization_code = p_org_code;
109
110 CURSOR c_first_status (cp_change_mgmt_type_id IN eng_change_order_types_tl.change_order_type_id%TYPE)
111 IS
112 SELECT a.status_name INTO stat_name
113 FROM eng_change_statuses_vl a, eng_lifecycle_statuses b
114 WHERE a.status_code = b.status_code
115 AND b.entity_name='ENG_CHANGE_TYPE'
116 AND b.entity_id1 = cp_change_mgmt_type_id
117 ORDER BY b.sequence_number;
118
119 L_TRANSACTION_TYPE varchar2(80) :='CREATE';
120
121 BEGIN
122
123 -- l_eco_rec.eco_name := change_number;
124 l_eco_rec.eco_name := '';
125 l_eco_rec.organization_code := organization_code;
126 l_eco_rec.description := change_name;
127 l_eco_rec.change_type_code := change_type_code;
128
129 -- Bug : 5140579 Removed the hardcoding and getting following values from database. Priority need not be hardcoded.
130 -- select status_name into stat_name FROM ENG_CHANGE_STATUSES_VL where status_code=1; -- 1 = Open
131
132 -- Bug : 5282713 We need to get the first status of this change type.
133 select change_order_type_id into l_change_mgmt_type_id from eng_change_order_types_tl where type_name = change_type_code and language = userenv('LANG');
134 -- Get the first phase
135
136 OPEN c_first_status(l_change_mgmt_type_id);
137 LOOP
138 FETCH c_first_status INTO stat_name; -- Get only the first record;
139 IF (c_first_status%NOTFOUND) THEN
140 stat_name := NULL;
141 ELSE
142 EXIT;
143 END IF;
144 END LOOP;
145 CLOSE c_first_status;
146
147 IF stat_name IS NULL THEN
148 select status_name into stat_name FROM ENG_CHANGE_STATUSES_VL where status_code=1; -- 1 = Open
149 END IF;
150
151
152 select change_order_type, change_order_type_id
153 into change_mgmt_type_name, l_change_order_type_id
154 FROM ENG_CHANGE_ORDER_TYPES_VL
155 WHERE change_mgmt_type_code='NEW_ITEM_REQUEST'
156 AND type_classification='CATEGORY';
157
158 l_eco_rec.change_management_type := change_mgmt_type_name;
159 l_eco_rec.status_name := stat_name;
160 l_eco_rec.priority_code := NULL;
161
162 l_eco_rec.reason_code := NULL;
163 l_eco_rec.approval_list_name := NULL;
164 l_eco_rec.eco_department_name := NULL;
165 l_eco_rec.cancellation_comments := NULL;
166 l_eco_rec.eng_implementation_cost := NULL;
167 l_eco_rec.mfg_implementation_cost := NULL;
168 l_eco_rec.requestor := requestor_user_name;
169 l_eco_rec.assignee := requestor_user_name;
170 l_eco_rec.organization_hierarchy := NULL;
171 l_eco_rec.return_status := NULL;
172 -- l_eco_rec.pk1_name := item_number;
173 -- l_eco_rec.pk2_name := organization_code;
174 l_eco_rec.plm_or_erp_change := 'PLM';
175 l_eco_rec.transaction_type := L_TRANSACTION_TYPE;
176
177 FOR CTD IN c_change_type_detail(l_change_order_type_id)
178 LOOP
179 IF (CTD.auto_numbering_method = 'INH_PAR')
180 THEN
181 l_type_id := CTD.change_mgmt_type_id;
182 l_auto_number_method := CTD.change_mgmt_method;
183 ELSE
184 l_type_id := CTD.change_order_type_id;
185 l_auto_number_method := CTD.auto_numbering_method;
186 END IF;
187
188 IF (l_auto_number_method = 'USR_ENT')
189 THEN
190 l_change_number := null;
191 ELSIF (l_auto_number_method = 'SEQ_GEN')
192 THEN
193
194 select alpha_prefix||next_available_number
195 INTO l_change_number
196 from eng_auto_number_ecn
197 where change_type_id = l_type_id;
198 -- Bug 5283630 Not necessary to update here. It is anyway done in Eng_Eco_Pub.Autogen_Change_Number() called from Process_Eco()
199 -- update eng_auto_number_ecn
200 -- set next_available_number = next_available_number+1
201 -- where change_type_id = l_type_id;
202 END IF;
203 END LOOP;
204
205 if l_change_number is not null
206 then
207
208 Eng_Eco_Pub.Process_Eco (
209 p_init_msg_list => TRUE,
210 x_return_status => l_return_status,
211 x_msg_count => l_msg_count,
212 p_ECO_rec => l_eco_rec,
213 p_eco_revision_tbl => l_out_eco_rev_tbl,
214 p_revised_item_tbl => l_out_rev_item_tbl,
215 p_rev_component_tbl => l_out_rev_comp_tbl,
216 p_ref_designator_tbl => l_out_ref_des_tbl,
217 p_sub_component_tbl => l_out_sub_comp_tbl,
218 p_rev_operation_tbl => l_out_rev_operation_tbl,
219 p_rev_op_resource_tbl => l_out_rev_op_resource_tbl,
220 p_rev_sub_resource_tbl => l_out_rev_sub_resource_tbl,
221 x_ECO_rec => l_out_eco_rec,
222 x_eco_revision_tbl => l_out_eco_rev_tbl,
223 x_revised_item_tbl => l_out_rev_item_tbl,
224 x_rev_component_tbl => l_out_rev_comp_tbl,
225 x_ref_designator_tbl => l_out_ref_des_tbl,
226 x_sub_component_tbl => l_out_sub_comp_tbl,
227 x_rev_operation_tbl => l_out_rev_operation_tbl,
228 x_rev_op_resource_tbl => l_out_rev_op_resource_tbl,
229 x_rev_sub_resource_tbl => l_out_rev_sub_resource_tbl
230 );
231
232 IF batch_id IS NOT NULL AND l_return_status = FND_API.G_RET_STS_SUCCESS
233 THEN
234
235 l_change_number := l_out_eco_rec.Eco_Name ;
236
237 FOR l_rec IN c_change_id( l_change_number, organization_code)
238 LOOP
239 x_change_id := l_rec.CHANGE_ID ;
240 END LOOP ;
241
242
243 IF x_change_id IS NOT NULL
244 THEN
245 ENG_CHANGE_IMPORT_UTIL.INSERT_IMPORT_CHANGE_ROW (
246 X_ROWID => l_hist_rec_row_id,
247 X_BATCH_ID => batch_id,
248 X_CHANGE_ID => x_change_id,
249 X_CREATION_DATE => SYSDATE,
250 X_CREATED_BY => FND_GLOBAL.user_id,
251 X_LAST_UPDATE_DATE => SYSDATE,
252 X_LAST_UPDATED_BY => FND_GLOBAL.user_id,
253 X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
254 ) ;
255 END IF ;
256
257 END IF ;
258
259 else
260 l_return_status := 'G';
261 end if;
262
263 x_return_status := l_return_status;
264
265
266 END Create_New_Item_Request ;
267
268 PROCEDURE CREATE_NEW_ITEM_REQUESTS(P_BATCH_ID IN NUMBER,
269 P_NIR_OPTION IN VARCHAR2,
270 x_return_status OUT NOCOPY VARCHAR2,
271 x_msg_data OUT NOCOPY VARCHAR2,
272 x_msg_count OUT NOCOPY NUMBER)
273 IS
274
275 TYPE ICC_TABLE IS TABLE of VARCHAR2(80);
276 TYPE CHANGE_TYPE_TABLE IS TABLE OF VARCHAR2(80);
277 TYPE ORGANIZATION_TABLE IS TABLE OF NUMBER;
278 TYPE NIR_COUNT is TABLE OF NUMBER;
279 TYPE NIR_CHANGE_TABLE IS TABLE OF NUMBER;
280 TYPE ITEMS_TABLE IS TABLE OF NUMBER;
281 TYPE SUB_DESC IS RECORD(SUBJECT_LEVEL NUMBER,
282 ENTITY_NAME VARCHAR2(80),
283 PARENT_ENTITY_NAME VARCHAR2(80)
284 );
285 TYPE SUB_DESC_TABLE IS TABLE OF SUB_DESC;
286 TYPE TRANSACTION_TABLE IS TABLE OF VARCHAR2(80);
287
288 l_icc_array ICC_TABLE;
289 l_change_type_array CHANGE_TYPE_TABLE;
290 l_nir_count NIR_COUNT;
291 l_org_array ORGANIZATION_TABLE;
292 l_change_ids_array NIR_CHANGE_TABLE;
293 l_item_ids_array ITEMS_TABLE;
294 l_sub_desc_array SUB_DESC_TABLE;
295 l_transaction_array TRANSACTION_TABLE;
296 l_nir_created NUMBER;
297
298 l_change_id NUMBER;
299 l_req_name VARCHAR2(30);
300 l_subject_level NUMBER;
301 l_process_flag NUMBER :=5;
302 L_TRANSACTION_TYPE varchar2(80) :='CREATE';
303 l_dynamic_sql varchar2(2000);
304 dumm_status number;
305 l_error_text VARCHAR2(80);
306 l_return_status VARCHAR2(1);
307 l_err_text VARCHAR2(240) :=null;
308 l_org_id NUMBER;
309 l_prev_org_id NUMBER;
310 l_org_code VARCHAR2(80);
311 --count number;
312 temp_change_line_id number;
313 l_nir_line_sequence ENG_CHANGE_LINES.SEQUENCE_NUMBER%TYPE;
314
315 CURSOR cur_change_lines IS
316 select
317 change_line_id,
318 change_id,
319 FND_GLOBAL.USER_ID created_by,
320 sysdate creation_date,
321 FND_GLOBAL.USER_ID last_updated_by,
322 sysdate last_update_date,
323 FND_GLOBAL.USER_ID last_update_login,
324 (select change_order_type_id from eng_change_order_types where TYPE_CLASSIFICATION='LINE'
325 AND CHANGE_MGMT_TYPE_CODE = 'NEW_ITEM_REQUEST' AND ROWNUM=1) change_type_id,
326 1 status_code,
327 1 APPROVAL_STATUS_TYPE
328 from mtl_system_items_interface msii
329 where msii.set_process_id = p_batch_id
330 and msii.process_flag = l_process_flag
331 -- and msii.transaction_type=L_TRANSACTION_TYPE
332 ;
333
334 BEGIN
335 x_return_status := 'S';
336
337 SAVEPOINT CREATE_NEW_ITEM_REQUESTS;
338 /*
339 SELECT DISTINCT MSII.ITEM_CATALOG_GROUP_ID,
340 eng_types.type_name,
341 msii.organization_id
342 bulk collect INTO l_icc_array ,
343 l_change_type_array,
344 l_org_array
345 FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
346 MTL_ITEM_CATALOG_GROUPS_VL micg,
347 ENG_CHANGE_ORDER_TYPES_VL eng_types,
348 MTL_PARAMETERS mp
349 WHERE msii.set_process_id=P_BATCH_ID
350 AND PROCESS_FLAG = l_process_flag
351 AND msii.ITEM_CATALOG_GROUP_ID = micg.ITEM_CATALOG_GROUP_ID
352 -- AND micg.NEW_ITEM_REQ_CHANGE_TYPE_ID = eng_types.change_order_type_id
353 AND
354 (SELECT
355 TO_NUMBER(SUBSTR(NEW_ITEM_REQ_CHANGE_TYPE_ID, INSTR(NEW_ITEM_REQ_CHANGE_TYPE_ID, '$$', 2)+2)) AS NEW_ITEM_REQ_CHANGE_TYPE_ID
356 FROM
357 (
358 SELECT
359 MIN( CASE WHEN micgb.NEW_ITEM_REQUEST_REQD = 'Y' AND ( PRIOR micgb.NEW_ITEM_REQUEST_REQD IS NULL OR PRIOR micgb.NEW_ITEM_REQUEST_REQD = 'I' )
360 THEN '$$'||LPad(LEVEL, 6, '0')||'$$'|| micgb.NEW_ITEM_REQ_CHANGE_TYPE_ID
361 ELSE NULL
362 END
363 ) NEW_ITEM_REQ_CHANGE_TYPE_ID
364
365 FROM MTL_ITEM_CATALOG_GROUPS_B micgb
366 CONNECT BY PRIOR micgb.PARENT_CATALOG_GROUP_ID = micgb.ITEM_CATALOG_GROUP_ID
367 START WITH micgb.ITEM_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
368 )) = eng_types.change_order_type_id
369 AND mp.organization_id = msii.organization_id
370 -- AND msii.TRANSACTION_TYPE = L_TRANSACTION_TYPE
371 order by organization_id;
372 */
373 --Bug 6162913
374 -- The above query was not working correctly because the ITEM_CATALOG_GROUP_ID taken is not from the MSII table.
375 -- Modified the following query for using the ego_item_cat_denorm_hier table
376 SELECT DISTINCT MSII.ITEM_CATALOG_GROUP_ID ,-- msii.segment1,
377 eng_types.type_name,
378 msii.organization_id
379 BULK COLLECT INTO l_icc_array ,
380 l_change_type_array,
381 l_org_array
382 FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
383 ENG_CHANGE_ORDER_TYPES_VL eng_types,
384 MTL_PARAMETERS mp,
385 ego_item_cat_denorm_hier heir,
386 mtl_item_catalog_groups_b micgb
387 WHERE msii.set_process_id = P_BATCH_ID
388 AND PROCESS_FLAG = l_process_flag
389 AND mp.organization_id = msii.organization_id
390 AND msii.item_catalog_group_id = heir.CHILD_CATALOG_GROUP_ID and
391 eng_types.change_order_type_id = micgb.NEW_ITEM_REQ_CHANGE_TYPE_ID and
392 micgb.item_catalog_group_id = heir.PARENT_CATALOG_GROUP_ID
393 ORDER BY organization_id;
394
395
396 select party_name into l_req_name from ego_user_v where user_name = FND_GLOBAL.USER_NAME;
397
398 if P_NIR_OPTION = G_NIR_ICC_OPTION
399 then
400 l_change_ids_array := NIR_CHANGE_TABLE();
401 for i in l_icc_array.FIRST .. l_icc_array.LAST
402 LOOP
403 l_org_id := l_org_array(i);
404 if l_prev_org_id IS NULL OR l_org_id <> l_prev_org_id
405 then
406 select organization_code
407 into l_org_code
408 from mtl_parameters mp
409 where mp.organization_id = l_org_id;
410 l_prev_org_id := l_org_id;
411 end if;
412
413 Create_New_Item_Request( x_return_status => l_return_status,
414 x_change_id => l_change_id,
415 change_number => l_icc_array(i),
416 change_name => l_icc_array(i),
417 change_type_code=> l_change_type_array(i),
418 item_number => null,
419 ORGANIZATION_CODE=>l_org_code,
420 requestor_user_name => l_req_name,
421 batch_id => p_batch_id) ;
422
423 if l_return_status = 'G'
424 then
425 update mtl_system_items_interface msii
426 set process_flag= 3
427 where msii.set_process_id = P_BATCH_ID
428 AND PROCESS_FLAG = l_process_flag
429 AND msii.ITEM_CATALOG_GROUP_ID = l_icc_array(i)
430 -- AND msii.TRANSACTION_TYPE = L_TRANSACTION_TYPE
431 ;
432
433 select transaction_id
434 bulk collect into l_transaction_array
435 from mtl_system_items_interface msii
436 where msii.set_process_id = P_BATCH_ID
437 AND PROCESS_FLAG = 3
438 AND msii.ITEM_CATALOG_GROUP_ID = l_icc_array(i)
439 -- AND msii.TRANSACTION_TYPE = L_TRANSACTION_TYPE
440 ;
441 for trans_i in l_transaction_array.FIRST .. l_transaction_array.LAST
442 LOOP
443 dumm_status := INVPUOPI.mtl_log_interface_err(
444 l_org_array(i), -- Row specific
445 FND_GLOBAL.USER_ID,
446 FND_GLOBAL.LOGIN_ID,
447 FND_GLOBAL.PROG_APPL_ID,
448 FND_GLOBAL.CONC_PROGRAM_ID,
449 FND_GLOBAL.CONC_REQUEST_ID
450 ,l_transaction_array(i) -- Row specific
451 ,l_err_text -- This is a dummy variable, if u want to pass message text and not name, assign the text to it
452 ,'ITEM_NUMBER' -- Column Name on which error occured
453 ,'MTL_SYSTEM_ITEMS_INTERFACE'
454 ,'INV_IOI_NO_AUTO_NIR' -- Message Name, If u want to specify the text directly, pass this as 'INV_IOI_ERR'
455 ,l_error_text);
456 END LOOP;
457 -- x_return_status := 'S';
458 else
459 --x_return_status := l_return_status;
460 l_change_ids_array.extend;
461 l_change_ids_array(l_change_ids_array.last):=l_change_id;
462
463 END IF;
464 END LOOP;
465 forall count IN l_change_ids_array.FIRST .. l_change_ids_array.LAST
466 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
467 set change_id = l_change_ids_array(count)
468 WHERE ITEM_CATALOG_GROUP_ID = l_icc_array(count)
469 AND ORGANIZATION_ID = l_org_array(count)
470 AND set_process_id = p_batch_id
471 AND PROCESS_FLAG = l_process_flag
472 -- AND TRANSACTION_TYPE =L_TRANSACTION_TYPE
473 ;
474 ELSE
475
476 for i in l_icc_array.FIRST .. l_icc_array.LAST
477 LOOP
478 l_org_id := l_org_array(i);
479
480 if l_prev_org_id is null OR l_org_id <> l_prev_org_id
481 then
482 select organization_code
483 into l_org_code
484 from mtl_parameters mp
485 where mp.organization_id = l_org_id;
486 l_prev_org_id := l_org_id;
487 end if;
488 l_change_ids_array := NIR_CHANGE_TABLE();
489 select inventory_item_id ,transaction_id
490 bulk collect into l_item_ids_array,l_transaction_array
491 FROM MTL_SYSTEM_ITEMS_INTERFACE
492 where ITEM_CATALOG_GROUP_ID = l_icc_array(i)
493 AND ORGANIZATION_ID = l_org_array(i)
494 AND set_process_id = p_batch_id
495 AND PROCESS_FLAG = l_process_flag
496 -- AND TRANSACTION_TYPE =L_TRANSACTION_TYPE
497 ;
498
499 for x in l_item_ids_array.FIRST .. l_item_ids_array.LAST
500 loop
501
502 Create_New_Item_Request( x_return_status => l_return_status,
503 x_change_id => l_change_id,
504 change_number => to_char(l_item_ids_array(x)),
505 change_name => 'Change '|| to_char(l_item_ids_array(x)),
506 change_type_code=> l_change_type_array(i),
507 item_number => null,
508 ORGANIZATION_CODE=>l_org_code,
509 requestor_user_name => l_req_name,
510 batch_id => p_batch_id) ;
511
512 if l_return_status = 'G'
513 then
514 dumm_status := INVPUOPI.mtl_log_interface_err(
515 l_org_array(i), -- Row specific
516 FND_GLOBAL.USER_ID,
517 FND_GLOBAL.LOGIN_ID,
518 FND_GLOBAL.PROG_APPL_ID,
519 FND_GLOBAL.CONC_PROGRAM_ID,
520 FND_GLOBAL.CONC_REQUEST_ID
521 ,l_transaction_array(i) -- Row specific
522 ,l_err_text -- This is a dummy variable, if u want to pass message text and not name, assign the text to it
523 ,'ITEM_NUMBER' -- Column Name on which error occured
524 ,'MTL_SYSTEM_ITEMS_INTERFACE'
525 ,'INV_IOI_NO_AUTO_NIR' -- Message Name, If u want to specify the text directly, pass this as 'INV_IOI_ERR'
526 ,l_error_text);
527
528 else
529 l_change_ids_array.extend;
530 l_change_ids_array(l_change_ids_array.last):=l_change_id;
531 end if;
532 END LOOP;
533
534 forall count in l_change_ids_array.FIRST .. l_change_ids_array.LAST
535 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
536 set change_id = l_change_ids_array(count)
537 WHERE ITEM_CATALOG_GROUP_ID = l_icc_array(i)
538 AND ORGANIZATION_ID = l_org_array(i)
539 AND set_process_id = p_batch_id
540 AND PROCESS_FLAG = l_process_flag
541 -- AND TRANSACTION_TYPE =L_TRANSACTION_TYPE
542 AND INVENTORY_ITEM_ID = l_item_ids_array(count);
543 END LOOP;
544
545 END IF;
546
547 update mtl_system_items_interface msii
548 set change_line_id = ENG_CHANGE_LINES_S.nextval
549 where msii.set_process_id = p_batch_id
550 and msii.process_flag= l_process_flag
551 -- and msii.transaction_type=L_TRANSACTION_TYPE
552 and change_id is not null;
553
554 /* -- Commented for bug 6189094 because sequence number is hardcoded, for multiple items in NIr sequence number should be differnent
555 insert into eng_change_lines
556 ( change_line_id ,
557 change_id ,
558 created_by ,
559 creation_date ,
560 last_updated_by ,
561 last_update_date ,
562 last_update_login ,
563 sequence_number ,
564 change_type_id ,
565 status_code ,
566 APPROVAL_STATUS_TYPE
567 )
568 (
569 select
570 msii.change_line_id,
571 msii.change_id,
572 FND_GLOBAL.USER_ID,
573 sysdate,
574 FND_GLOBAL.USER_ID,
575 sysdate,
576 FND_GLOBAL.USER_ID,
577 10,
578 (select change_order_type_id from eng_change_order_types where TYPE_CLASSIFICATION='LINE'
579 AND CHANGE_MGMT_TYPE_CODE = 'NEW_ITEM_REQUEST' AND ROWNUM=1) TYPE_ID,
580 1 ,
581 1
582 from mtl_system_items_interface msii
583 where msii.set_process_id = p_batch_id
584 and msii.process_flag = l_process_flag
585 -- and msii.transaction_type=L_TRANSACTION_TYPE
586 );
587 */
588 l_nir_line_sequence := 10;
589 FOR cur_chg_line IN cur_change_lines
590 LOOP
591 INSERT INTO eng_change_lines
592 ( change_line_id ,
593 change_id ,
594 created_by ,
595 creation_date ,
596 last_updated_by ,
597 last_update_date ,
598 last_update_login ,
599 sequence_number ,
600 change_type_id ,
601 status_code ,
602 APPROVAL_STATUS_TYPE
603 )
604 VALUES
605 (
606 cur_chg_line.change_line_id,
607 cur_chg_line.change_id,
608 cur_chg_line.created_by,
609 cur_chg_line.creation_date,
610 cur_chg_line.last_updated_by,
611 cur_chg_line.last_update_date,
612 cur_chg_line.last_update_login,
613 l_nir_line_sequence,
614 cur_chg_line.change_type_id,
615 cur_chg_line.status_code,
616 cur_chg_line.APPROVAL_STATUS_TYPE
617 );
618 l_nir_line_sequence := l_nir_line_sequence + 10;
619 END LOOP;
620
621 insert into eng_change_lines_tl
622 ( change_line_id ,
623 created_by ,
624 creation_date ,
625 last_updated_by ,
626 last_update_date ,
627 last_update_login ,
628 language ,
629 source_lang ,
630 name
631 )
632 (
633 select
634 msii.change_line_id,
635 FND_GLOBAL.USER_ID,
636 sysdate,
637 FND_GLOBAL.USER_ID,
638 sysdate,
639 FND_GLOBAL.USER_ID,
640 LANGS.LANGUAGE_CODE,
641 USERENV('LANG'),
642 msii.change_line_id
643 from mtl_system_items_interface msii,
644 FND_LANGUAGES LANGS
645 where msii.set_process_id = p_batch_id
646 and msii.process_flag = l_process_flag
647 -- and msii.transaction_type=L_TRANSACTION_TYPE
648 AND LANGS.installed_flag IN ('B','I'));
649
650 SELECT subject_level, entity_name, parent_entity_name
651 BULK COLLECT INTO l_sub_desc_array
652 FROM eng_subject_entities ese, eng_subjects_b esb
653 WHERE ese.subject_id=esb.subject_id
654 AND esb.subject_internal_name='EGO_NEW_ITEM'
655 ORDER BY subject_level ;
656
657 for sub_count in l_sub_desc_array.FIRST .. l_sub_desc_array.LAST
658 LOOP
659 l_dynamic_sql := 'insert into ENG_CHANGE_SUBJECTS '||
660 '(change_subject_id , change_id , change_line_id , entity_name,';
661 if l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_CATALOG_GROUP'
662 then
663 l_dynamic_sql := l_dynamic_sql || 'pk1_value, ';
664 elsif l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_ITEM'
665 THEN
666 l_dynamic_sql := l_dynamic_sql || 'pk1_value,pk2_value, ';
667 elsif l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_ITEM_REVISION'
668 then
669 l_dynamic_sql := l_dynamic_sql || 'pk1_value,pk2_value, pk3_value, ';
670 end if;
671
672 l_dynamic_sql := l_dynamic_sql || ' subject_level, created_by, creation_date ,' ||
673 ' last_updated_by, last_update_date, last_update_login)'
674 || ' (SELECT ENG_CHANGE_SUBJECTS_S.nextval , '||
675 ' MSII.CHANGE_ID, MSII.CHANGE_LINE_ID, ''' || l_sub_desc_array(sub_count).entity_name ||''',';
676 if l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_CATALOG_GROUP'
677 then
678 l_dynamic_sql := l_dynamic_sql || 'MSII.ITEM_CATALOG_GROUP_ID ';
679 elsif l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_ITEM'
680 THEN
681 l_dynamic_sql := l_dynamic_sql || 'MSII.INVENTORY_ITEM_ID,MSII.ORGANIZATION_ID ';
682 elsif l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_ITEM_REVISION'
683 then
684 l_dynamic_sql := l_dynamic_sql || 'MSII.INVENTORY_ITEM_ID,MSII.ORGANIZATION_ID,MIRI.REVISION_ID ';
685 end if;
686 l_dynamic_sql := l_dynamic_sql ||
687 ' , ' || l_sub_desc_array(sub_count).SUBJECT_LEVEL ||
688 ' , FND_GLOBAL.USER_ID, SYSDATE ' ||
689 ' , FND_GLOBAL.USER_ID, SYSDATE'||
690 ' , FND_GLOBAL.USER_ID FROM MTL_SYSTEM_ITEMS_INTERFACE MSII';
691
692 if l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_ITEM_REVISION'
693 then
694 l_dynamic_sql := l_dynamic_sql ||' ,MTL_ITEM_REVISIONS_INTERFACE MIRI '||
695 ' WHERE MSII.INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID ' ||
696 ' AND MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID ' ||
697 ' AND MSII.SET_PROCESS_ID = MIRI.SET_PROCESS_ID ' ;
698 ELSE
699 l_dynamic_sql := l_dynamic_sql || ' WHERE 1=1';
700 END IF;
701 l_dynamic_sql := l_dynamic_sql || ' AND msii.set_process_id = :1 AND msii.PROCESS_FLAG = :2 '
702 -- || ' AND msii.TRANSACTION_TYPE = :3 ' ||
703 || ')';
704 --EXECUTE IMMEDIATE l_dynamic_sql USING p_batch_id,l_process_flag,L_TRANSACTION_TYPE;
705
706 EXECUTE IMMEDIATE l_dynamic_sql USING p_batch_id,l_process_flag;
707
708 END LOOP;
709
710 -- Bug 6162913
711 -- Added the following because the items approval status is not getting updated in the NIR newly created from the excel import flow
712 -- The query in the ENG_NIR_UTIL_PKG.set_nir_item_approval_status() is returning no rows because change subjects are not yet created.
713 -- This is requuired because the lifecycle is created first and the subjects were getting created later
714 -- So we have to reset the Item approval status again after creating the NIR.
715
716 for l_nir_created IN l_change_ids_array.FIRST .. l_change_ids_array.LAST
717 LOOP
718
719 ENG_NIR_UTIL_PKG.set_nir_item_approval_status (l_change_ids_array(l_nir_created),
720 Eng_Workflow_Util.G_REQUESTED,
721 x_return_status => x_return_status,
722 x_msg_count => x_msg_count,
723 x_msg_data => x_msg_data);
724 END LOOP;
725
726 EXCEPTION
727 WHEN OTHERS THEN
728
729 x_return_status := 'U';
730 x_msg_count := 1;
731 x_msg_data := SQLERRM;
732 rollback to CREATE_NEW_ITEM_REQUESTS;
733
734 END CREATE_NEW_ITEM_REQUESTS;
735
736 END ENG_NEW_ITEM_REQ_UTIL;