[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.18.12020000.5 2012/10/15 11:01:31 swuppala 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
135
136 --Bug 8242706(base bug7587290): If NIR type and Change Order Type has got same name, Creation of NIR is failing.
137 -- validaton on NIR type included in the select query.
138
139 SELECT ECOT.change_order_type_id
140 INTO l_change_mgmt_type_id
141 FROM eng_change_order_types_tl ECOT,
142 ENG_CHANGE_ORDER_TYPES ECO
143 WHERE ECOT.CHANGE_ORDER_TYPE_ID=ECO.CHANGE_ORDER_TYPE_ID
144 AND ECO.CHANGE_MGMT_TYPE_CODE= 'NEW_ITEM_REQUEST'
145 AND ECOT.type_name = change_type_code
146 AND ECOT.language = userenv('LANG');
147
148 -- Get the first phase
149 OPEN c_first_status(l_change_mgmt_type_id);
150 LOOP
151 FETCH c_first_status INTO stat_name; -- Get only the first record;
152 IF (c_first_status%NOTFOUND) THEN
153 stat_name := NULL;
154 ELSE
155 EXIT;
156 END IF;
157 END LOOP;
158 CLOSE c_first_status;
159
160 IF stat_name IS NULL THEN
161 select status_name into stat_name FROM ENG_CHANGE_STATUSES_VL where status_code=1; -- 1 = Open
162 END IF;
163
164
165 select change_order_type, change_order_type_id
166 into change_mgmt_type_name, l_change_order_type_id
167 FROM ENG_CHANGE_ORDER_TYPES_VL
168 WHERE change_mgmt_type_code='NEW_ITEM_REQUEST'
169 AND type_classification='CATEGORY';
170
171 l_eco_rec.change_management_type := change_mgmt_type_name;
172 l_eco_rec.status_name := stat_name;
173 l_eco_rec.priority_code := NULL;
174
175 l_eco_rec.reason_code := NULL;
176 l_eco_rec.approval_list_name := NULL;
177 l_eco_rec.eco_department_name := NULL;
178 l_eco_rec.cancellation_comments := NULL;
179 l_eco_rec.eng_implementation_cost := NULL;
180 l_eco_rec.mfg_implementation_cost := NULL;
181 l_eco_rec.requestor := requestor_user_name;
182 /*Commented for bug 13721297,the default value for assignee will be handle in Eng_Default_ECO.Attribute_Defaulting*/
183 --l_eco_rec.assignee := requestor_user_name;
184 l_eco_rec.organization_hierarchy := NULL;
185 l_eco_rec.return_status := NULL;
186 -- l_eco_rec.pk1_name := item_number;
187 -- l_eco_rec.pk2_name := organization_code;
188 l_eco_rec.plm_or_erp_change := 'PLM';
189 l_eco_rec.transaction_type := L_TRANSACTION_TYPE;
190
191 FOR CTD IN c_change_type_detail(l_change_order_type_id)
192 LOOP
193 IF (CTD.auto_numbering_method = 'INH_PAR')
194 THEN
195 l_type_id := CTD.change_mgmt_type_id;
196 l_auto_number_method := CTD.change_mgmt_method;
197 ELSE
198 l_type_id := CTD.change_order_type_id;
199 l_auto_number_method := CTD.auto_numbering_method;
200 END IF;
201
202 IF (l_auto_number_method = 'USR_ENT')
203 THEN
204 l_change_number := null;
205 ELSIF (l_auto_number_method = 'SEQ_GEN')
206 THEN
207
208 select alpha_prefix||next_available_number
209 INTO l_change_number
210 from eng_auto_number_ecn
211 where change_type_id = l_type_id;
212 -- Bug 5283630 Not necessary to update here. It is anyway done in Eng_Eco_Pub.Autogen_Change_Number() called from Process_Eco()
213 -- update eng_auto_number_ecn
214 -- set next_available_number = next_available_number+1
215 -- where change_type_id = l_type_id;
216 END IF;
217 END LOOP;
218
219 if l_change_number is not null
220 then
221
222 Eng_Eco_Pub.Process_Eco (
223 p_init_msg_list => TRUE,
224 x_return_status => l_return_status,
225 x_msg_count => l_msg_count,
226 p_ECO_rec => l_eco_rec,
227 p_eco_revision_tbl => l_out_eco_rev_tbl,
228 p_revised_item_tbl => l_out_rev_item_tbl,
229 p_rev_component_tbl => l_out_rev_comp_tbl,
230 p_ref_designator_tbl => l_out_ref_des_tbl,
231 p_sub_component_tbl => l_out_sub_comp_tbl,
232 p_rev_operation_tbl => l_out_rev_operation_tbl,
233 p_rev_op_resource_tbl => l_out_rev_op_resource_tbl,
234 p_rev_sub_resource_tbl => l_out_rev_sub_resource_tbl,
235 x_ECO_rec => l_out_eco_rec,
236 x_eco_revision_tbl => l_out_eco_rev_tbl,
237 x_revised_item_tbl => l_out_rev_item_tbl,
238 x_rev_component_tbl => l_out_rev_comp_tbl,
239 x_ref_designator_tbl => l_out_ref_des_tbl,
240 x_sub_component_tbl => l_out_sub_comp_tbl,
241 x_rev_operation_tbl => l_out_rev_operation_tbl,
242 x_rev_op_resource_tbl => l_out_rev_op_resource_tbl,
243 x_rev_sub_resource_tbl => l_out_rev_sub_resource_tbl
244 );
245
246 IF batch_id IS NOT NULL AND l_return_status = FND_API.G_RET_STS_SUCCESS
247 THEN
248
249 l_change_number := l_out_eco_rec.Eco_Name ;
250
251 FOR l_rec IN c_change_id( l_change_number, organization_code)
252 LOOP
253 x_change_id := l_rec.CHANGE_ID ;
254 END LOOP ;
255
256
257 IF x_change_id IS NOT NULL
258 THEN
259 ENG_CHANGE_IMPORT_UTIL.INSERT_IMPORT_CHANGE_ROW (
260 X_ROWID => l_hist_rec_row_id,
261 X_BATCH_ID => batch_id,
262 X_CHANGE_ID => x_change_id,
263 X_CREATION_DATE => SYSDATE,
264 X_CREATED_BY => FND_GLOBAL.user_id,
265 X_LAST_UPDATE_DATE => SYSDATE,
266 X_LAST_UPDATED_BY => FND_GLOBAL.user_id,
267 X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
268 ) ;
269 END IF ;
270
271 END IF ;
272
273 else
274 l_return_status := 'G';
275 end if;
276
277 x_return_status := l_return_status;
278
279
280 END Create_New_Item_Request ;
281
282 PROCEDURE CREATE_NEW_ITEM_REQUESTS(P_BATCH_ID IN NUMBER,
283 P_NIR_OPTION IN VARCHAR2,
284 x_return_status OUT NOCOPY VARCHAR2,
285 x_msg_data OUT NOCOPY VARCHAR2,
286 x_msg_count OUT NOCOPY NUMBER)
287 IS
288
289 TYPE ICC_TABLE IS TABLE of VARCHAR2(80);
290 TYPE CHANGE_TYPE_TABLE IS TABLE OF VARCHAR2(80);
291 TYPE ORGANIZATION_TABLE IS TABLE OF NUMBER;
292 TYPE NIR_COUNT is TABLE OF NUMBER;
293 TYPE NIR_CHANGE_TABLE IS TABLE OF NUMBER;
294 TYPE ITEMS_TABLE IS TABLE OF NUMBER;
295 TYPE SUB_DESC IS RECORD(SUBJECT_LEVEL NUMBER,
296 ENTITY_NAME VARCHAR2(80),
297 PARENT_ENTITY_NAME VARCHAR2(80)
298 );
299 TYPE SUB_DESC_TABLE IS TABLE OF SUB_DESC;
300 TYPE TRANSACTION_TABLE IS TABLE OF VARCHAR2(80);
301
302 l_icc_array ICC_TABLE;
303 l_change_type_array CHANGE_TYPE_TABLE;
304 l_nir_count NIR_COUNT;
305 l_org_array ORGANIZATION_TABLE;
306 l_change_ids_array NIR_CHANGE_TABLE;
307 l_change_ids_array_all NIR_CHANGE_TABLE; -- bug 14376801
308 l_item_ids_array ITEMS_TABLE;
309 l_sub_desc_array SUB_DESC_TABLE;
310 l_transaction_array TRANSACTION_TABLE;
311 l_nir_created NUMBER;
312 l_change_type_code_num NUMBER;
313 l_change_type_code VARCHAR2(80);
314
315 l_change_id NUMBER;
316 l_req_name VARCHAR2(30);
317 l_subject_level NUMBER;
318 l_process_flag NUMBER :=5;
319 L_TRANSACTION_TYPE varchar2(80) :='CREATE';
320 l_dynamic_sql varchar2(2000);
321 dumm_status number;
322 l_error_text VARCHAR2(80);
323 l_return_status VARCHAR2(1);
324 l_err_text VARCHAR2(240) :=null;
325 l_org_id NUMBER;
326 l_prev_org_id NUMBER;
327 l_org_code VARCHAR2(80);
328 --count number;
329 temp_change_line_id number;
330 l_nir_line_sequence ENG_CHANGE_LINES.SEQUENCE_NUMBER%TYPE;
331
332 CURSOR cur_change_lines IS
333 select
334 change_line_id,
335 change_id,
336 FND_GLOBAL.USER_ID created_by,
337 sysdate creation_date,
338 FND_GLOBAL.USER_ID last_updated_by,
339 sysdate last_update_date,
340 FND_GLOBAL.USER_ID last_update_login,
341 (select change_order_type_id from eng_change_order_types where TYPE_CLASSIFICATION='LINE'
342 AND CHANGE_MGMT_TYPE_CODE = 'NEW_ITEM_REQUEST' AND ROWNUM=1) change_type_id,
343 1 status_code,
344 1 APPROVAL_STATUS_TYPE
345 from mtl_system_items_interface msii
346 where msii.set_process_id = p_batch_id
347 and msii.process_flag = l_process_flag
348 -- and msii.transaction_type=L_TRANSACTION_TYPE
349 ;
350
351 BEGIN
352 x_return_status := 'S';
353
354 SAVEPOINT CREATE_NEW_ITEM_REQUESTS;
355 /*
356 SELECT DISTINCT MSII.ITEM_CATALOG_GROUP_ID,
357 eng_types.type_name,
358 msii.organization_id
359 bulk collect INTO l_icc_array ,
360 l_change_type_array,
361 l_org_array
362 FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
363 MTL_ITEM_CATALOG_GROUPS_VL micg,
364 ENG_CHANGE_ORDER_TYPES_VL eng_types,
365 MTL_PARAMETERS mp
366 WHERE msii.set_process_id=P_BATCH_ID
367 AND PROCESS_FLAG = l_process_flag
368 AND msii.ITEM_CATALOG_GROUP_ID = micg.ITEM_CATALOG_GROUP_ID
369 -- AND micg.NEW_ITEM_REQ_CHANGE_TYPE_ID = eng_types.change_order_type_id
370 AND
371 (SELECT
372 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
373 FROM
374 (
375 SELECT
376 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' )
377 THEN '$$'||LPad(LEVEL, 6, '0')||'$$'|| micgb.NEW_ITEM_REQ_CHANGE_TYPE_ID
378 ELSE NULL
379 END
380 ) NEW_ITEM_REQ_CHANGE_TYPE_ID
381
382 FROM MTL_ITEM_CATALOG_GROUPS_B micgb
383 CONNECT BY PRIOR micgb.PARENT_CATALOG_GROUP_ID = micgb.ITEM_CATALOG_GROUP_ID
384 START WITH micgb.ITEM_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
385 )) = eng_types.change_order_type_id
386 AND mp.organization_id = msii.organization_id
387 -- AND msii.TRANSACTION_TYPE = L_TRANSACTION_TYPE
388 order by organization_id;
389 */
390 --Bug 6162913
391 -- The above query was not working correctly because the ITEM_CATALOG_GROUP_ID taken is not from the MSII table.
392 -- Modified the following query for using the ego_item_cat_denorm_hier table
393 /*
394 SELECT DISTINCT MSII.ITEM_CATALOG_GROUP_ID ,-- msii.segment1,
395 eng_types.type_name,
396 msii.organization_id
397 BULK COLLECT INTO l_icc_array ,
398 l_change_type_array,
399 l_org_array
400 FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
401 ENG_CHANGE_ORDER_TYPES_VL eng_types,
402 MTL_PARAMETERS mp,
403 ego_item_cat_denorm_hier heir,
404 mtl_item_catalog_groups_b micgb
405 WHERE msii.set_process_id = P_BATCH_ID
406 AND PROCESS_FLAG = l_process_flag
407 AND mp.organization_id = msii.organization_id
408 AND msii.item_catalog_group_id = heir.CHILD_CATALOG_GROUP_ID and
409 eng_types.change_order_type_id = micgb.NEW_ITEM_REQ_CHANGE_TYPE_ID and
410 micgb.item_catalog_group_id = heir.PARENT_CATALOG_GROUP_ID
411 ORDER BY organization_id;
412 */
413
414 SELECT DISTINCT MSII.ITEM_CATALOG_GROUP_ID,
415 (
416 SELECT
417 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' )
418 THEN '$$'||LPad(LEVEL, 6, '0')||'$$'|| micgb.NEW_ITEM_REQ_CHANGE_TYPE_ID
419 ELSE NULL
420 END
421 ) NEW_ITEM_REQ_CHANGE_TYPE_ID
422
423 FROM MTL_ITEM_CATALOG_GROUPS_B micgb
424 CONNECT BY PRIOR micgb.PARENT_CATALOG_GROUP_ID = micgb.ITEM_CATALOG_GROUP_ID
425 START WITH micgb.ITEM_CATALOG_GROUP_ID = msii.ITEM_CATALOG_GROUP_ID
426 ) AS type_name,
427 msii.organization_id
428 bulk collect INTO l_icc_array ,
429 l_change_type_array,
430 l_org_array
431 FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
432 -- ENG_CHANGE_ORDER_TYPES eng_types,
433 MTL_PARAMETERS mp
434 WHERE msii.set_process_id=P_BATCH_ID
435 AND PROCESS_FLAG = l_process_flag
436 AND mp.organization_id = msii.organization_id
437 order by organization_id;
438
439
440
441
442 -- Bug 10412328, change the l_req_name initialization part from party_name to user_name
443 --select party_name into l_req_name from ego_user_v where user_name = FND_GLOBAL.USER_NAME;
444 l_req_name := FND_GLOBAL.USER_NAME;
445
446 if P_NIR_OPTION = G_NIR_ICC_OPTION
447 then
448 l_change_ids_array := NIR_CHANGE_TABLE();
449 for i in l_icc_array.FIRST .. l_icc_array.LAST
450 LOOP
451 l_org_id := l_org_array(i);
452 if l_prev_org_id IS NULL OR l_org_id <> l_prev_org_id
453 then
454 select organization_code
455 into l_org_code
456 from mtl_parameters mp
457 where mp.organization_id = l_org_id;
458 l_prev_org_id := l_org_id;
459 end if;
460
461 l_change_type_code_num := TO_NUMBER(SUBSTR(l_change_type_array(i), INSTR(l_change_type_array(i), '$$', 2)+2));
462 SELECT eng_types.type_name into l_change_type_code FROM ENG_CHANGE_ORDER_TYPES_VL eng_types WHERE eng_types.change_order_type_id = l_change_type_code_num;
463
464 Create_New_Item_Request( x_return_status => l_return_status,
465 x_change_id => l_change_id,
466 change_number => l_icc_array(i),
467 change_name => l_icc_array(i),
468 change_type_code=> l_change_type_code,
469 item_number => null,
470 ORGANIZATION_CODE=>l_org_code,
471 requestor_user_name => l_req_name,
472 batch_id => p_batch_id) ;
473
474 if l_return_status = 'G'
475 then
476 update mtl_system_items_interface msii
477 set process_flag= 3
478 where msii.set_process_id = P_BATCH_ID
479 AND PROCESS_FLAG = l_process_flag
480 AND msii.ITEM_CATALOG_GROUP_ID = l_icc_array(i)
481 -- AND msii.TRANSACTION_TYPE = L_TRANSACTION_TYPE
482 ;
483
484 select transaction_id
485 bulk collect into l_transaction_array
486 from mtl_system_items_interface msii
487 where msii.set_process_id = P_BATCH_ID
488 AND PROCESS_FLAG = 3
489 AND msii.ITEM_CATALOG_GROUP_ID = l_icc_array(i)
490 -- AND msii.TRANSACTION_TYPE = L_TRANSACTION_TYPE
491 ;
492 for trans_i in l_transaction_array.FIRST .. l_transaction_array.LAST
493 LOOP
494 dumm_status := INVPUOPI.mtl_log_interface_err(
495 l_org_array(i), -- Row specific
496 FND_GLOBAL.USER_ID,
497 FND_GLOBAL.LOGIN_ID,
498 FND_GLOBAL.PROG_APPL_ID,
499 FND_GLOBAL.CONC_PROGRAM_ID,
500 FND_GLOBAL.CONC_REQUEST_ID
501 ,l_transaction_array(i) -- Row specific
502 ,l_err_text -- This is a dummy variable, if u want to pass message text and not name, assign the text to it
503 ,'ITEM_NUMBER' -- Column Name on which error occured
504 ,'MTL_SYSTEM_ITEMS_INTERFACE'
505 ,'INV_IOI_NO_AUTO_NIR' -- Message Name, If u want to specify the text directly, pass this as 'INV_IOI_ERR'
506 ,l_error_text);
507 END LOOP;
508 -- x_return_status := 'S';
509 else
510 --x_return_status := l_return_status;
511 l_change_ids_array.extend;
512 l_change_ids_array(l_change_ids_array.last):=l_change_id;
513
514 END IF;
515 END LOOP;
516 forall count IN l_change_ids_array.FIRST .. l_change_ids_array.LAST
517 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
518 set change_id = l_change_ids_array(count)
519 WHERE ITEM_CATALOG_GROUP_ID = l_icc_array(count)
520 AND ORGANIZATION_ID = l_org_array(count)
521 AND set_process_id = p_batch_id
522 AND PROCESS_FLAG = l_process_flag
523 -- AND TRANSACTION_TYPE =L_TRANSACTION_TYPE
524 ;
525 ELSE
526
527 l_change_ids_array_all := NIR_CHANGE_TABLE(); --bug 14376801
528 for i in l_icc_array.FIRST .. l_icc_array.LAST
529 LOOP
530 l_org_id := l_org_array(i);
531
532 if l_prev_org_id is null OR l_org_id <> l_prev_org_id
533 then
534 select organization_code
535 into l_org_code
536 from mtl_parameters mp
537 where mp.organization_id = l_org_id;
538 l_prev_org_id := l_org_id;
539 end if;
540 l_change_ids_array := NIR_CHANGE_TABLE();
541 select inventory_item_id ,transaction_id
542 bulk collect into l_item_ids_array,l_transaction_array
543 FROM MTL_SYSTEM_ITEMS_INTERFACE
544 where ITEM_CATALOG_GROUP_ID = l_icc_array(i)
545 AND ORGANIZATION_ID = l_org_array(i)
546 AND set_process_id = p_batch_id
547 AND PROCESS_FLAG = l_process_flag
548 -- AND TRANSACTION_TYPE =L_TRANSACTION_TYPE
549 ;
550 l_change_type_code_num := TO_NUMBER(SUBSTR(l_change_type_array(i), INSTR(l_change_type_array(i), '$$', 2)+2));
551 SELECT eng_types.type_name into l_change_type_code FROM ENG_CHANGE_ORDER_TYPES_VL eng_types WHERE eng_types.change_order_type_id = l_change_type_code_num;
552 for x in l_item_ids_array.FIRST .. l_item_ids_array.LAST
553 loop
554
555
556
557 Create_New_Item_Request( x_return_status => l_return_status,
558 x_change_id => l_change_id,
559 change_number => to_char(l_item_ids_array(x)),
560 change_name => 'Change '|| to_char(l_item_ids_array(x)),
561 change_type_code=> l_change_type_code,
562 item_number => null,
563 ORGANIZATION_CODE=>l_org_code,
564 requestor_user_name => l_req_name,
565 batch_id => p_batch_id) ;
566
567 if l_return_status = 'G'
568 then
569 dumm_status := INVPUOPI.mtl_log_interface_err(
570 l_org_array(i), -- Row specific
571 FND_GLOBAL.USER_ID,
572 FND_GLOBAL.LOGIN_ID,
573 FND_GLOBAL.PROG_APPL_ID,
574 FND_GLOBAL.CONC_PROGRAM_ID,
575 FND_GLOBAL.CONC_REQUEST_ID
576 ,l_transaction_array(i) -- Row specific
577 ,l_err_text -- This is a dummy variable, if u want to pass message text and not name, assign the text to it
578 ,'ITEM_NUMBER' -- Column Name on which error occured
579 ,'MTL_SYSTEM_ITEMS_INTERFACE'
580 ,'INV_IOI_NO_AUTO_NIR' -- Message Name, If u want to specify the text directly, pass this as 'INV_IOI_ERR'
581 ,l_error_text);
582
583 else
584 l_change_ids_array.extend;
585 l_change_ids_array(l_change_ids_array.last):=l_change_id;
586
587 l_change_ids_array_all.extend; --bug 14376801
588 l_change_ids_array_all(l_change_ids_array_all.last):=l_change_id; --bug 14376801
589 end if;
590 END LOOP;
591
592 forall count in l_change_ids_array.FIRST .. l_change_ids_array.LAST
593 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
594 set change_id = l_change_ids_array(count)
595 WHERE ITEM_CATALOG_GROUP_ID = l_icc_array(i)
596 AND ORGANIZATION_ID = l_org_array(i)
597 AND set_process_id = p_batch_id
598 AND PROCESS_FLAG = l_process_flag
599 -- AND TRANSACTION_TYPE =L_TRANSACTION_TYPE
600 AND INVENTORY_ITEM_ID = l_item_ids_array(count);
601 END LOOP;
602
603 END IF;
604
605 update mtl_system_items_interface msii
606 set change_line_id = ENG_CHANGE_LINES_S.nextval
607 where msii.set_process_id = p_batch_id
608 and msii.process_flag= l_process_flag
609 -- and msii.transaction_type=L_TRANSACTION_TYPE
610 and change_id is not null;
611
612 /* -- Commented for bug 6189094 because sequence number is hardcoded, for multiple items in NIr sequence number should be differnent
613 insert into eng_change_lines
614 ( change_line_id ,
615 change_id ,
616 created_by ,
617 creation_date ,
618 last_updated_by ,
619 last_update_date ,
620 last_update_login ,
621 sequence_number ,
622 change_type_id ,
623 status_code ,
624 APPROVAL_STATUS_TYPE
625 )
626 (
627 select
628 msii.change_line_id,
629 msii.change_id,
630 FND_GLOBAL.USER_ID,
631 sysdate,
632 FND_GLOBAL.USER_ID,
633 sysdate,
634 FND_GLOBAL.USER_ID,
635 10,
636 (select change_order_type_id from eng_change_order_types where TYPE_CLASSIFICATION='LINE'
637 AND CHANGE_MGMT_TYPE_CODE = 'NEW_ITEM_REQUEST' AND ROWNUM=1) TYPE_ID,
638 1 ,
639 1
640 from mtl_system_items_interface msii
641 where msii.set_process_id = p_batch_id
642 and msii.process_flag = l_process_flag
643 -- and msii.transaction_type=L_TRANSACTION_TYPE
644 );
645 */
646 l_nir_line_sequence := 10;
647 FOR cur_chg_line IN cur_change_lines
648 LOOP
649 INSERT INTO eng_change_lines
650 ( change_line_id ,
651 change_id ,
652 created_by ,
653 creation_date ,
654 last_updated_by ,
655 last_update_date ,
656 last_update_login ,
657 sequence_number ,
658 change_type_id ,
659 status_code ,
660 APPROVAL_STATUS_TYPE
661 )
662 VALUES
663 (
664 cur_chg_line.change_line_id,
665 cur_chg_line.change_id,
666 cur_chg_line.created_by,
667 cur_chg_line.creation_date,
668 cur_chg_line.last_updated_by,
669 cur_chg_line.last_update_date,
670 cur_chg_line.last_update_login,
671 l_nir_line_sequence,
672 cur_chg_line.change_type_id,
673 cur_chg_line.status_code,
674 cur_chg_line.APPROVAL_STATUS_TYPE
675 );
676 l_nir_line_sequence := l_nir_line_sequence + 10;
677 END LOOP;
678
679 insert into eng_change_lines_tl
680 ( change_line_id ,
681 created_by ,
682 creation_date ,
683 last_updated_by ,
684 last_update_date ,
685 last_update_login ,
686 language ,
687 source_lang ,
688 name
689 )
690 (
691 select
692 msii.change_line_id,
693 FND_GLOBAL.USER_ID,
694 sysdate,
695 FND_GLOBAL.USER_ID,
696 sysdate,
697 FND_GLOBAL.USER_ID,
698 LANGS.LANGUAGE_CODE,
699 USERENV('LANG'),
700 msii.change_line_id
701 from mtl_system_items_interface msii,
702 FND_LANGUAGES LANGS
703 where msii.set_process_id = p_batch_id
704 and msii.process_flag = l_process_flag
705 -- and msii.transaction_type=L_TRANSACTION_TYPE
706 AND LANGS.installed_flag IN ('B','I'));
707
708 SELECT subject_level, entity_name, parent_entity_name
709 BULK COLLECT INTO l_sub_desc_array
710 FROM eng_subject_entities ese, eng_subjects_b esb
711 WHERE ese.subject_id=esb.subject_id
712 AND esb.subject_internal_name='EGO_NEW_ITEM'
713 ORDER BY subject_level ;
714
715 for sub_count in l_sub_desc_array.FIRST .. l_sub_desc_array.LAST
716 LOOP
717 l_dynamic_sql := 'insert into ENG_CHANGE_SUBJECTS '||
718 '(change_subject_id , change_id , change_line_id , entity_name,';
719 if l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_CATALOG_GROUP'
720 then
721 l_dynamic_sql := l_dynamic_sql || 'pk1_value, ';
722 elsif l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_ITEM'
723 THEN
724 l_dynamic_sql := l_dynamic_sql || 'pk1_value,pk2_value, ';
725 elsif l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_ITEM_REVISION'
726 then
727 l_dynamic_sql := l_dynamic_sql || 'pk1_value,pk2_value, pk3_value, ';
728 end if;
729
730 l_dynamic_sql := l_dynamic_sql || ' subject_level, created_by, creation_date ,' ||
731 ' last_updated_by, last_update_date, last_update_login)'
732 || ' (SELECT ENG_CHANGE_SUBJECTS_S.nextval , '||
733 ' MSII.CHANGE_ID, MSII.CHANGE_LINE_ID, ''' || l_sub_desc_array(sub_count).entity_name ||''',';
734 if l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_CATALOG_GROUP'
735 then
736 l_dynamic_sql := l_dynamic_sql || 'MSII.ITEM_CATALOG_GROUP_ID ';
737 elsif l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_ITEM'
738 THEN
739 l_dynamic_sql := l_dynamic_sql || 'MSII.INVENTORY_ITEM_ID,MSII.ORGANIZATION_ID ';
740 elsif l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_ITEM_REVISION'
741 then
742 l_dynamic_sql := l_dynamic_sql || 'MSII.INVENTORY_ITEM_ID,MSII.ORGANIZATION_ID,MIRI.REVISION_ID ';
743 end if;
744 l_dynamic_sql := l_dynamic_sql ||
745 ' , ' || l_sub_desc_array(sub_count).SUBJECT_LEVEL ||
746 ' , FND_GLOBAL.USER_ID, SYSDATE ' ||
747 ' , FND_GLOBAL.USER_ID, SYSDATE'||
748 ' , FND_GLOBAL.USER_ID FROM MTL_SYSTEM_ITEMS_INTERFACE MSII';
749
750 if l_sub_desc_array(sub_count).ENTITY_NAME = 'EGO_ITEM_REVISION'
751 then
752 l_dynamic_sql := l_dynamic_sql ||' ,MTL_ITEM_REVISIONS_INTERFACE MIRI '||
753 ' WHERE MSII.INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID ' ||
754 ' AND MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID ' ||
755 ' AND MSII.SET_PROCESS_ID = MIRI.SET_PROCESS_ID ' ;
756 ELSE
757 l_dynamic_sql := l_dynamic_sql || ' WHERE 1=1';
758 END IF;
759 l_dynamic_sql := l_dynamic_sql || ' AND msii.set_process_id = :1 AND msii.PROCESS_FLAG = :2 '
760 -- || ' AND msii.TRANSACTION_TYPE = :3 ' ||
761 || ')';
762 --EXECUTE IMMEDIATE l_dynamic_sql USING p_batch_id,l_process_flag,L_TRANSACTION_TYPE;
763
764 EXECUTE IMMEDIATE l_dynamic_sql USING p_batch_id,l_process_flag;
765
766 END LOOP;
767
768 -- Bug 6162913
769 -- Added the following because the items approval status is not getting updated in the NIR newly created from the excel import flow
770 -- The query in the ENG_NIR_UTIL_PKG.set_nir_item_approval_status() is returning no rows because change subjects are not yet created.
771 -- This is requuired because the lifecycle is created first and the subjects were getting created later
772 -- So we have to reset the Item approval status again after creating the NIR.
773
774 --bug 14376801 begin
775 if P_NIR_OPTION = G_NIR_ICC_OPTION
776 then
777 for l_nir_created IN l_change_ids_array.FIRST .. l_change_ids_array.LAST
778 LOOP
779
780 ENG_NIR_UTIL_PKG.set_nir_item_approval_status (l_change_ids_array(l_nir_created),
781 Eng_Workflow_Util.G_REQUESTED,
782 x_return_status => x_return_status,
783 x_msg_count => x_msg_count,
784 x_msg_data => x_msg_data);
785 END LOOP;
786 else --bug 14376801 [for P_NIR_OPTION is I, use l_change_ids_array_all]
787 for l_nir_created IN l_change_ids_array_all.FIRST .. l_change_ids_array_all.LAST
788 LOOP
789 ENG_NIR_UTIL_PKG.set_nir_item_approval_status (l_change_ids_array_all(l_nir_created),
790 Eng_Workflow_Util.G_REQUESTED,
791 x_return_status => x_return_status,
792 x_msg_count => x_msg_count,
793 x_msg_data => x_msg_data);
794 END LOOP;
795 end if;
796 --bug 14376801 end
797
798 EXCEPTION
799 WHEN OTHERS THEN
800
801 x_return_status := 'U';
802 x_msg_count := 1;
803 x_msg_data := SQLERRM;
804 rollback to CREATE_NEW_ITEM_REQUESTS;
805
806 END CREATE_NEW_ITEM_REQUESTS;
807
808 END ENG_NEW_ITEM_REQ_UTIL;