[Home] [Help]
PACKAGE BODY: APPS.CTO_UPDATE_ITEMS_PK
Source
1 package body CTO_UPDATE_ITEMS_PK as
2 /* $Header: CTOUITMB.pls 120.13.12020000.3 2012/07/26 11:38:45 abhissri ship $ */
3
4 /*============================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 | Oracle Manufacturing |
8 +=============================================================================+
9 |
10 | FILE NAME : CTOUPDIB.pls
11 | DESCRIPTION :
12 |
13 | HISTORY : Created On : 11-OCT-2003 Sajani Sheth
14 |
15 | Modified : 02-MAR-2004 Sushant Sawant
16 | Fixed Bug 3472654
17 | upgrades for matched config from CIB = 1 or 2 to 3 were not performed properly.
18 | data was not transformed to bcmo.
19 | perform_match check includes 'Y' and 'U'
20 |
21 |
22 | 03-15-2004 Kiran Konada
23 | 3504153
24 |
25 | Propagated fix 3340844 from CTOCITMB
26 | Populate value revision_label into mtl_item_revisions_b
27 | removed the join to distinct and join on bcso
28 |
29 | Propagated fix 3338108 from CTOCITMB.pls
30 | Populate revision_id by making a join to
31 | mtl_item_revisions_b
32 |
33 |
34 | 04-19-2004 Sushant Sawant
35 | Fixed bug 3576040
36 |
37 | 07-SEP-2004 Kiran Konada
38 | bugfix 3877097
39 | at lStmtNumber := 40,60,70,80
40 | during the insert into cst_item_costs and
41 | cst_item_cost_details
42 | mp1.cost_organization_id was being inserted
43 | BUT the NOT EXISTS condition was checking
44 | for mp1.organization_id.
45 | Fixed the above problem by using
46 | mp1.cost_organization_id in NOT EXISTS condition
47 | Code review has been done by Sushant
48 |
49 | Modified on 18-APR-2005 By Sushant Sawant
50 | Fixed Bug#4172300
51 | Cost in validation org is not copied properly from model to config item.
52 |
53 |
54 | Modified on 08-Aug-2005 by Kiran Konada
55 | bug# 4539578
56 | In R12, mtl_cross_references datamodel has been changed to
57 | mtl_cross_references_b and mtl_cross_references_tl
58 |
59 |
60 | Modified on 22-Sep-2005 Renga Kannan
61 | Made Code changes for ATG Performance
62 | Project
63 |
64 |
65 | Modified on 08-Nov-2005 Kiran Konada
66 | bug#4574899
67 | Insert default data into New R12 item attributes
68 |
69 |
70 | Modified on 03-Feb-2006 Kiran Konada
71 | FP bugfix 4861996
72 | added condition ic1.category_set_id = ic.category_set_id
73 *============================================================================*/
74
75 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
76
77 g_atp_flag varchar2(1) := 'N' ;
78 gUserId number := nvl(Fnd_Global.USER_ID, -1);
79 gLoginId number := nvl(Fnd_Global.LOGIN_ID, -1);
80
81 TYPE ATTRIB_NAME_TAB_TYPE is table of mtl_item_attributes.attribute_name%type index by binary_integer ;
82
83 TYPE CONTROL_LEVEL_TAB_TYPE is table of mtl_item_attributes.control_level%type index by binary_integer ;
84
85 g_attribute_name_tab ATTRIB_NAME_TAB_TYPE ;
86 g_control_level_tab CONTROL_LEVEL_TAB_TYPE ;
87
88 /***************************************************************************
89 This procedure is called by CTO_Update_Configs_PK.Update_Configs to update itemsand sourcing for configurations in bcol_upg.
90 It does the following:
91 1. Refreshes bcso/bcmo for all ato_line_ids in bcol_upg
92 2. Create ACC items
93 3. Create PC items (with special logic for attribute control)
94 4. Creates item data
95 --- Added by Renga Kannan on 01/23/04
96 5. A new parameter p_upgrade_mode is added to this procedure. This is to update the
97 atp attributes on the existing configs.
98 ***************************************************************************/
99 PROCEDURE Update_Items_And_Sourcing(
100 p_changed_src IN varchar2,
101 p_cat_id IN number,
102 p_upgrade_mode IN Number,
103 --Bugfix 10240482: Adding new parameter p_max_seq
104 p_max_seq IN number,
105 xReturnStatus OUT NOCOPY varchar2,
106 xMsgCount OUT NOCOPY number,
107 xMsgData OUT NOCOPY varchar2)
108 IS
109
110 CURSOR c_lines(l_seq number) IS
111 select /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
112 distinct ato_line_id, program_id -- , perform_match
113 from bom_cto_order_lines_upg
114 where sequence = l_seq
115 and status = 'UPG';
116
117 CURSOR c_configs(l_ato_line_id number) IS
118 select substrb(concatenated_segments,1,50) config_name
119 from bom_cto_order_lines_upg bcolu,
120 mtl_system_items_kfv msi
121 where bcolu.ato_line_id = l_ato_line_id
122 and bcolu.config_item_id is not null
123 and bcolu.config_item_id = msi.inventory_item_id
124 and bcolu.ship_from_org_id = msi.organization_id;
125
126 CURSOR c_src_lines(l_seq number) IS
127 select /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
128 distinct ato_line_id, program_id
129 from bom_cto_order_lines_upg
130 where sequence = l_seq
131 and status = 'ITEM';
132
133 CURSOR c_copy_src_rules(p_ato_line_id number) IS
134 select bcso.rcv_org_id,
135 bcso.organization_id,
136 bcolu.config_creation,
137 bcso.create_src_rules,
138 bcso.model_item_id,
139 bcso.config_item_id
140 from bom_cto_order_lines_upg bcolu,
141 bom_cto_src_orgs bcso
142 where bcolu.ato_line_id = p_ato_line_id
143 and bcolu.bom_item_type = '1'
144 and nvl(bcolu.wip_supply_type, 1) <> 6
145 and bcolu.option_specific = 'N'
146 and bcolu.line_id = bcso.line_id;
147
148 --Bugfix 1024082
149 --l_seq number := 0;
150 lStmtNum number;
151 l_exists number;
152 l_return_status varchar2(1);
153 l_msg_count number;
154 l_msg_data varchar2(2000);
155 l_status number;
156 l_stmt_num number := 0;
157 l_hold_result_out varchar2(30);
158 l_index number;
159 l_order_number number;
160
161 v_lines_perform_match varchar2(1);
162
163 --
164 -- bug 13051516
165 --
166 TYPE COPY_SRC_RULES_CACHE_TYP IS TABLE OF NUMBER INDEX BY VARCHAR2(32767);
167 COPY_SRC_RULES_CACHE COPY_SRC_RULES_CACHE_TYP ;
168
169 BEGIN
170
171 WriteToLog('Entering create_items_and_sourcing', 1);
172 xReturnStatus := FND_API.G_RET_STS_SUCCESS;
173
174 --
175 -- Call OSS processing
176 -- Populate / refresh bcso for all lines in bcol_upg
177 -- Create sourcing in CTO assignment set for these lines
178 --
179
180 --Bugfix 10240482: Changing the way sequence is used
181 --WHILE (TRUE) LOOP
182 for l_seq in 1..p_max_seq loop
183 --
184 -- Process all lines for each unique sequence
185 --
186
187 --Bugfix 10240482
188 --l_seq := l_seq + 1;
189 WriteToLog('Update_Items_And_Sourcing: l_seq:'|| l_seq, 1);
190
191 BEGIN
192 select 1
193 into l_exists
194 from bom_cto_order_lines_upg
195 where sequence = l_seq
196 and rownum = 1;
197
198 EXCEPTION
199 WHEN no_data_found THEN
200 --Bugfix 10240482
201 --exit;
202 WriteToLog('Update_Items_And_Sourcing: No_Data_Found for l_seq:'|| l_seq, 1);
203 goto end_loop1;
204
205 END;
206
207 FOR v_lines IN c_lines(l_seq) LOOP
208
209 --
210 -- Call OSS processing.
211 --
212
213 WriteToLog('Changed sourcing, processing OSS configs', 2);
214
215 CTO_OSS_SOURCE_PK.PROCESS_OSS_CONFIGURATIONS(
216 p_ato_line_id => v_lines.ato_line_id,
217 p_mode => 'UPG',
218 x_return_status => l_return_status,
219 x_msg_count => l_msg_count,
220 x_msg_data => l_msg_data);
221
222 -- Bugfix 13362916
223 -- IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
224 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
225 WriteToLog('ERROR: Process oss configurations returned with exp error',1);
226
227 --Bugfix 6376208: Not all upgrade cases will be present on SO lines. Handle the NDF
228 begin
229
230 select oeh.order_number
231 into l_order_number
232 from oe_order_lines_all oel,
233 oe_order_headers_all oeh
234 where oel.line_id = v_lines.ato_line_id
235 and oel.header_id = oeh.header_id;
236
237 exception
238 when no_data_found then
239 l_order_number := -99;
240 end;
241 --end Bugfix 6376208
242
243 WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
244 WriteToLog('ERROR: Order number '||l_order_number, 1);
245
246 FOR v_configs IN c_configs(v_lines.ato_line_id) LOOP
247 WriteToLog('ERROR: Configuration item '||v_configs.config_name, 1);
248 END LOOP;
249
250 lstmtnum := 20;
251 FOR l_index IN 1..nvl(l_msg_count,1) LOOP
252 lstmtnum := 20;
253 l_msg_data := fnd_msg_pub.get(
254 p_msg_index => l_index,
255 p_encoded => FND_API.G_FALSE);
256 lstmtnum := 30;
257 WriteToLog('Error : '||substr(l_msg_data,1,250), 1);
258 END LOOP;
259 lstmtnum := 40;
260 WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
261
262 update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
263 bom_cto_order_lines_upg
264 set status = 'ERROR'
265 where ato_line_id = v_lines.ato_line_id;
266
267 WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
268 GOTO SKIP;
269 --raise FND_API.G_EXC_ERROR;
270 /* Bugfix 13362916
271 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
272 WriteToLog('ERROR: Process_oss_configurations returned with unexp error',1);
273 raise FND_API.G_EXC_UNEXPECTED_ERROR;
274 */
275 END IF;
276
277 --
278 -- Refresh bcso, create src for each ato_line_id
279 -- Update status to 'BCSO'
280 -- Sourcing will always be refreshed, irrespective of the
281 -- parameter p_changed_src. This is to account for the new
282 -- way in which we populate bcso_b, bcmo.
283 --
284 WriteToLog('Refresh bcso:: l_seq:: '||to_char(l_seq), 4);
285 WriteToLog('Refresh bcso:: ato_line_id:: '||v_lines.ato_line_id, 4);
286
287 --
288 -- Delete from bcso_b
289 --
290 delete from bom_cto_src_orgs_b
291 where line_id in
292 (select /* INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 BOM_CTO_ORDER_LINES_UPG_N2 ) */
293 line_id
294 from bom_cto_order_lines_upg
295 where ato_line_id = v_lines.ato_line_id
296 and config_item_id is not null
297 and status = 'UPG');
298 WriteToLog('Lines deleted from bcso_b::'||sql%rowcount, 2);
299
300 --
301 -- delete from bcmo
302 --
303 delete from bom_cto_model_orgs
304 where config_item_id in
305 (select /* INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 BOM_CTO_ORDER_LINES_UPG_N2 ) */
306 distinct config_item_id
307 from bom_cto_order_lines_upg
308 where ato_line_id = v_lines.ato_line_id
309 and status = 'UPG');
310
311 WriteToLog('Lines deleted from bcmo::'||sql%rowcount, 2);
312
313 --
314 -- populating bcso_b and bcmo with changed sourcing
315 --
316 l_status := CTO_MSUTIL_PUB.populate_src_orgs_upg(
317 v_lines.ato_line_id,
318 l_return_status,
319 l_msg_count,
320 l_msg_data);
321
322 -- Bugfix 13362916
323 -- IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
324 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
325 WriteToLog('ERROR: Populate_src_orgs_upg returned with status:' || l_return_status, 1);
326
327 -- Bugfix 13362916
328 BEGIN
329 select oeh.order_number
330 into l_order_number
331 from oe_order_lines_all oel,
332 oe_order_headers_all oeh
333 where oel.line_id = v_lines.ato_line_id
334 and oel.header_id = oeh.header_id;
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 l_order_number := -99;
338 END;
339
340 WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
341 WriteToLog('ERROR: Order number '||l_order_number, 1);
342
343 FOR v_configs IN c_configs(v_lines.ato_line_id) LOOP
344 WriteToLog('ERROR: Configuration item '||v_configs.config_name, 1);
345 END LOOP;
346
347 FOR l_index IN 1..nvl(l_msg_count,1) LOOP
348 l_msg_data := fnd_msg_pub.get(
349 p_msg_index => l_index,
350 p_encoded => FND_API.G_FALSE);
351 WriteToLog('Error : '||substr(l_msg_data,1,250), 1);
352 END LOOP;
353 lstmtnum := 40;
354 WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
355
356 update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
357 bom_cto_order_lines_upg
358 set status = 'ERROR'
359 where ato_line_id = v_lines.ato_line_id;
360
361 WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
362 GOTO SKIP;
363 --raise FND_API.G_EXC_ERROR;
364 -- Bugfix 13362916
365 /*
366 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
367 WriteToLog('ERROR: Populate_src_orgs_upg returned with unexp error',1);
368 raise FND_API.G_EXC_UNEXPECTED_ERROR;
369 */
370 END IF;
371
372 WriteToLog('After populate_src_orgs_upg:: seq::'||to_char(l_seq), 4);
373
374
375 /*
376 begin
377 select perform_match into v_lines_perform_match
378 from bom_cto_order_lines_upg
379 where line_id = v_lines.ato_line_id ;
380
381 exception
382 when others then
383 v_lines_perform_match := 'N' ;
384 end ;
385 */
386
387
388 /* Bugfix 3472654 */
389 /*
390 if( v_lines_perform_match in( 'Y' , 'U') ) then
391
392 WriteToLog('populate_src_orgs_upg:: perform match Y, Should call update_bcso ' , 4);
393
394 */
395
396
397 --
398 -- Updating bcso_b for other order lines having same config
399 --
400 Update_Bcso(
401 v_lines.ato_line_id,
402 l_return_status,
403 l_msg_count,
404 l_msg_data);
405
406 -- Update_Bcso only returns unexp errors
407 IF (l_return_status = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
408 WriteToLog('Update_bcso returned with unexpected error', 1);
409 --Bugfix 13362916
410 WriteToLog('Skipping ato_line_id:' || v_lines.ato_line_id, 1);
411 --raise FND_API.G_EXC_UNEXPECTED_ERROR;
412 GOTO SKIP;
413 END IF;
414
415 /*
416 else
417
418 WriteToLog('populate_src_orgs_upg:: perform match N, No need to call update_bcso ' , 4);
419
420 end if ;
421 */
422
423
424
425 <<SKIP>>
426 EXIT WHEN c_lines%NOTFOUND;
427 END LOOP; /* c_lines */
428
429 update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
430 bom_cto_order_lines_upg
431 set status = 'BCSO'
432 where sequence = l_seq
433 and status = 'UPG';
434
435 WriteToLog('Lines updated to status BCSO:: '||sql%rowcount, 1);
436
437 <<end_loop1>>
438 null;
439
440 END LOOP;
441
442 WriteToLog('After refresh bcso', 4);
443
444
445 -- Update the atp attributes for the configuration in the existing orgs.
446 -- Added By Renga on 01/23/04
447
448 If p_upgrade_mode = 2 Then
449 WriteToLog('Updating ATP attributes for configs in existing orgs');
450
451 update mtl_system_items_b msic
452 set (msic.atp_components_flag,msic.atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(msim.atp_flag,
453 msim.atp_components_flag),CTO_CONFIG_ITEM_PK.get_atp_flag
454 from mtl_system_items_b msim
455 where msim.inventory_item_id = msic.base_item_id
456 and msim.organization_id = msic.organization_id)
457 where msic.inventory_item_id in
458 (
459 select distinct config_item_id
460 from bom_cto_order_lines_upg
461 where status = 'BCSO'
462 and config_item_id is not null
463 )
464 and exists (select 'x'
465 from mtl_system_items_b msim1
466 where msim1.inventory_item_id = msic.base_item_id
467 and msim1.organization_id = msic.organization_id);
468
469 WriteToLog('Number of Configs updated for ATP attributes ='||sql%rowcount);
470 End if;
471 -- End of addition by Renga
472
473 --
474 -- Create all auto-created items
475 -- Update status to 'ITEM'
476 --
477
478 Update_Acc_Items(
479 l_return_status,
480 l_msg_count,
481 l_msg_data);
482
483 -- Update_Acc_Items only returns unexp errors
484 IF (l_return_status = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
485 WriteToLog('Update_Acc_Items returned with unexpected error', 1);
486 raise FND_API.G_EXC_UNEXPECTED_ERROR;
487 END IF;
488
489
490 --
491 -- Create all pre-configured items
492 -- Update status to 'ITEM'
493 --
494
495 Update_Pc_Items(
496 l_return_status,
497 l_msg_count,
498 l_msg_data);
499
500 -- Update_Pc_Items only returns unexp errors
501 IF (l_return_status = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
502 WriteToLog('Update_Acc_Items returned with unexpected error', 1);
503 raise FND_API.G_EXC_UNEXPECTED_ERROR;
504 END IF;
505
506 --
507 -- create data for items in child tables
508 --
509 Update_Item_Data(
510 nvl(p_cat_id, -99),
511 l_return_status,
512 l_msg_count,
513 l_msg_data);
514
515 IF (l_return_status = fnd_api.G_RET_STS_ERROR) THEN
516 WriteToLog('Update_Item_Data returned with expected error', 1);
517 raise FND_API.G_EXC_ERROR;
518 ELSIF (l_return_status = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
519 WriteToLog('Update_Item_Data returned with unexpected error', 1);
520 raise FND_API.G_EXC_UNEXPECTED_ERROR;
521 END IF;
522
523 --
524 -- Update status to 'ITEM'
525 --
526 -- bug 14018684
527 -- The hint is not needed since this update would
528 -- impact all the records in the table and so an
529 -- index scan is not efficient
530 --
531 --update /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
532 update
533 bom_cto_order_lines_upg
534 set status = 'ITEM'
535 where status = 'BCSO';
536
537 WriteToLog('Items created. Status updated to ITEM for rows::'||sql%rowcount, 1);
538 --
539 -- Create sourcing rules in CTO assignment set
540 --
541
542 --Bugfix 10240482
543 --l_seq := 0;
544 --WHILE (TRUE) LOOP
545 for l_seq in 1..p_max_seq loop
546 --
547 -- Process all lines for each unique sequence
548 --
549
550 --Bugfix 10240482
551 --l_seq := l_seq + 1;
552 WriteToLog('Update_Items_And_Sourcing: l_seq:'|| l_seq, 1);
553
554 BEGIN
555 select 1
556 into l_exists
557 from bom_cto_order_lines_upg
558 where sequence = l_seq
559 and rownum = 1;
560
561 EXCEPTION
562 WHEN no_data_found THEN
563 --Bugfix 10240482
564 --exit;
565 WriteToLog('Update_Items_And_Sourcing: No_Data_Found for l_seq:'|| l_seq, 1);
566 goto end_loop2;
567
568 END;
569
570 FOR v_lines IN c_src_lines(l_seq) LOOP
571 --
572 -- Do not need to check for holds, as we are picking up
573 -- lines in status 'ITEM' only
574 --
575
576 WriteToLog('Changed sourcing, creating OSS rules', 2);
577
578 CTO_OSS_SOURCE_PK.create_oss_sourcing_rules
579 (p_ato_line_id => v_lines.ato_line_id,
580 p_mode => 'UPG',
581 x_return_status => l_return_status,
582 x_msg_count => l_msg_count,
583 x_msg_data => l_msg_data,
584 p_changed_src => p_changed_src );
585
586 -- bug 13362916
587 --
588 -- IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
589 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
590 WriteToLog('ERROR: Create_oss_sourcing_rules returned with status:' || l_return_status,1);
591
592 -- bug 13362916
593 BEGIN
594 select oeh.order_number
595 into l_order_number
596 from oe_order_lines_all oel,
597 oe_order_headers_all oeh
598 where oel.line_id = v_lines.ato_line_id
599 and oel.header_id = oeh.header_id;
600 EXCEPTION
601 WHEN NO_DATA_FOUND THEN
602 l_order_number := -99;
603 END;
604
605 WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
606 WriteToLog('ERROR: Order number '||l_order_number, 1);
607
608 FOR v_configs IN c_configs(v_lines.ato_line_id) LOOP
609 WriteToLog('ERROR: Configuration item '||v_configs.config_name, 1);
610 END LOOP;
611
612 FOR l_index IN 1..nvl(l_msg_count,1) LOOP
613 l_msg_data := fnd_msg_pub.get(
614 p_msg_index => l_index,
615 p_encoded => FND_API.G_FALSE);
616 WriteToLog('Error : '||substr(l_msg_data,1,250), 1);
617 END LOOP;
618 lstmtnum := 40;
619 WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
620
621 update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
622 bom_cto_order_lines_upg
623 set status = 'ERROR'
624 where ato_line_id = v_lines.ato_line_id;
625
626 WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
627 GOTO SKIP2;
628 --raise FND_API.G_EXC_ERROR;
629 -- 13362916
630 /*
631 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
632 WriteToLog('ERROR: Create_oss_sourcing_rules returned with unexp error',1);
633 raise FND_API.G_EXC_UNEXPECTED_ERROR;
634 */
635 END IF;
636
637 --debugging for bug 13029577
638 WriteToLog('++++++++++++++++++++++++++++++++++++++++++');
639 WriteToLog('printing bcso values');
640 FOR v_src_rule IN c_copy_src_rules(v_lines.ato_line_id) LOOP
641 WriteToLog('model::' || v_src_rule.model_item_id ||
642 '::config::' || v_src_rule.config_item_id ||
643 '::rcv_org::' || v_src_rule.rcv_org_id ||
644 '::org::' || v_src_rule.organization_id ||
645 '::cib::' || v_src_rule.config_creation ||
646 '::src::' || v_src_rule.create_src_rules);
647 END LOOP;
648 WriteToLog('++++++++++++++++++++++++++++++++++++++++++');
649 --end debugging for bug 13029577
650
651 --
652 -- bug 13029577
653 --
654 COPY_SRC_RULES_CACHE.delete;
655
656 FOR v_src_rule IN c_copy_src_rules(v_lines.ato_line_id) LOOP
657 --
658 -- bug 13051516
659 --
660 -- Bugfix 14270815
661 WriteToLog('Processing:Model:' || v_src_rule.model_item_id ||
662 ':Config:' || v_src_rule.config_item_id ||
663 ':rcv_org:' || v_src_rule.rcv_org_id ||
664 ':org:' || v_src_rule.organization_id ||
665 ':cib:' || v_src_rule.config_creation ||
666 ':src:' || v_src_rule.create_src_rules);
667
668 -- Bugfix 14270815: Concatinating organization_id value also. This is required
669 -- in a scenario where there is only a transfer from sourcing rule in the shipping
670 -- warehouse. In such cases, a transfer from same organization record is also
671 -- added in bcso. Hence, just concatinating model_id, config_id and rcv_org doesn't
672 -- work. The scenario is as follows:
673 -- In M1, tfr from M3. The records added in bcso for M1 would be:
674 -- rcv_org_id organization_id
675 -- M1 M1
676 -- M1 M3
677
678 IF COPY_SRC_RULES_CACHE.EXISTS(v_src_rule.model_item_id||'-'||
679 v_src_rule.config_item_id||'-'||
680 v_src_rule.rcv_org_id||'-'||
681 v_src_rule.organization_id) = FALSE THEN
682
683 COPY_SRC_RULES_CACHE(v_src_rule.model_item_id||'-'||
684 v_src_rule.config_item_id||'-'||
685 v_src_rule.rcv_org_id||'-'||
686 v_src_rule.organization_id) := 1;
687
688 --
689 -- Call API to copy sourcing rules from model item
690 -- to config item.
691 -- Copy sourcing rules only if sourcing has changed
692 --
693 IF (v_src_rule.create_src_rules='Y' AND v_src_rule.config_creation in (1, 2) AND p_changed_src = 'Y') THEN
694 WriteToLog ('Copying src rule for cfg item::'|| to_char(v_src_rule.config_item_id)||
695 ' in org::'|| to_char(v_src_rule.organization_id), 4);
696
697 CTO_MSUTIL_PUB.Create_Sourcing_Rules(
698 pModelItemId => v_src_rule.model_item_id,
699 pConfigId => v_src_rule.config_item_id,
700 pRcvOrgId => v_src_rule.rcv_org_id,
701 p_mode => 'UPGRADE',
702 x_return_status => l_return_status,
703 x_msg_count => l_msg_count,
704 x_msg_data => l_msg_data);
705
706 -- Bugfix 13362916
707 -- IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
708 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
709 WriteToLog('ERROR: Create_sourcing_rules returned with status:' || l_return_status,1);
710
711 -- bug 13362916
712 BEGIN
713 select oeh.order_number
714 into l_order_number
715 from oe_order_lines_all oel,
716 oe_order_headers_all oeh
717 where oel.line_id = v_lines.ato_line_id
718 and oel.header_id = oeh.header_id;
719 EXCEPTION
720 WHEN NO_DATA_FOUND THEN
721 l_order_number := -99;
722 END;
723
724 WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
725 WriteToLog('ERROR: Order number '||l_order_number, 1);
726
727 FOR v_configs IN c_configs(v_lines.ato_line_id) LOOP
728 WriteToLog('ERROR: Configuration item '||v_configs.config_name, 1);
729 END LOOP;
730
731 FOR l_index IN 1..nvl(l_msg_count,1) LOOP
732 l_msg_data := fnd_msg_pub.get(
733 p_msg_index => l_index,
734 p_encoded => FND_API.G_FALSE);
735 WriteToLog('Error : '||substr(l_msg_data,1,250), 1);
736 END LOOP;
737 WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
738
739 update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
740 bom_cto_order_lines_upg
741 set status = 'ERROR'
742 where ato_line_id = v_lines.ato_line_id;
743
744 WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
745 GOTO SKIP2;
746 -- Bug 13362916
747 /*
748 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
749 WriteToLog('ERROR: Create_sourcing_rules returned with unexp error',1);
750 raise FND_API.G_EXC_UNEXPECTED_ERROR;
751 */
752 END IF;
753 ELSIF (v_src_rule.config_creation = 3 ) THEN
754 --
755 -- Always copy sourcing rules if config_creation is 3
756 --
757 WriteToLog ('Copying src rule for cfg item:: '
758 ||to_char(v_src_rule.config_item_id)||' in org:: '||
759 to_char(v_src_rule.organization_id), 4);
760
761 CTO_MSUTIL_PUB.Create_TYPE3_Sourcing_Rules(
762 pModelItemId => v_src_rule.model_item_id,
763 pConfigId => v_src_rule.config_item_id,
764 pRcvOrgId => v_src_rule.organization_id,
765 p_mode => 'UPGRADE',
766 x_return_status => l_return_status,
767 x_msg_count => l_msg_count,
768 x_msg_data => l_msg_data);
769
770 -- bug 13362916
771 -- IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
772 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
773 WriteToLog('ERROR: Create_type3_sourcing_rules returned with status:' || l_return_status, 1);
774
775 -- bug 13362916
776 BEGIN
777 select oeh.order_number
778 into l_order_number
779 from oe_order_lines_all oel,
780 oe_order_headers_all oeh
781 where oel.line_id = v_lines.ato_line_id
782 and oel.header_id = oeh.header_id;
783 EXCEPTION
784 WHEN no_data_found then
785 l_order_number := -99;
786 END;
787
788 WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
789 WriteToLog('ERROR: Order number '||l_order_number, 1);
790
791 FOR v_configs IN c_configs(v_lines.ato_line_id) LOOP
792 WriteToLog('ERROR: Configuration item '||v_configs.config_name, 1);
793 END LOOP;
794
795 FOR l_index IN 1..nvl(l_msg_count,1) LOOP
796 l_msg_data := fnd_msg_pub.get(
797 p_msg_index => l_index,
798 p_encoded => FND_API.G_FALSE);
799 WriteToLog('Error : '||substr(l_msg_data,1,250), 1);
800 END LOOP;
801 WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
802
803 update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
804 bom_cto_order_lines_upg
805 set status = 'ERROR'
806 where ato_line_id = v_lines.ato_line_id;
807
808 WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
809 GOTO SKIP2;
810 -- Bug 13362916
811 /*
812 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
813 WriteToLog('ERROR: Create_type3_sourcing_rules returned with unexp error',1);
814 raise FND_API.G_EXC_UNEXPECTED_ERROR;
815 */
816 END IF;
817 END IF;
818 END IF;
819 END LOOP; /* c_copy_src_rules */
820 <<SKIP2>>
821 EXIT WHEN c_src_lines%NOTFOUND;
822 END LOOP; /* c_src_lines */
823
824 update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
825 bom_cto_order_lines_upg
826 set status = 'CTO_SRC'
827 where sequence = l_seq
828 and status = 'ITEM';
829
830 WriteToLog('Lines updated to status CTO_SRC:: '||sql%rowcount, 1);
831
832 <<end_loop2>>
833 null;
834
835 END LOOP;
836
837 WriteToLog('After create sourcing rules', 2);
838
839 EXCEPTION
840 when NO_DATA_FOUND then
841 WriteToLog('ERROR: NDF in Update_Items_and_Sourcing::lStmtNum::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
842 cto_msg_pub.count_and_get
843 ( p_msg_count => xMsgCount
844 , p_msg_data => xMsgData
845 );
846 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR;
847 --return(0);
848
849 when FND_API.G_EXC_ERROR then
850 WriteToLog('ERROR: Expected error in Update_Items_and_Sourcing::lStmtNum::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
851 cto_msg_pub.count_and_get
852 ( p_msg_count => xMsgCount
853 , p_msg_data => xMsgData
854 );
855 xReturnStatus := FND_API.G_RET_STS_ERROR;
856 --return(0);
857
858 when FND_API.G_EXC_UNEXPECTED_ERROR then
859 WriteToLog('ERROR: Unxpected error in Update_Items_and_Sourcing::lStmtNum::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
860 cto_msg_pub.count_and_get
861 ( p_msg_count => xMsgCount
862 , p_msg_data => xMsgData
863 );
864 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR;
865 --return(0);
866
867 when OTHERS then
868 WriteToLog('ERROR: Others error in Update_Items_and_Sourcing::lStmtNum::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
869 cto_msg_pub.count_and_get
870 ( p_msg_count => xMsgCount
871 , p_msg_data => xMsgData
872 );
873 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR;
874 --return(0);
875
876 END Update_Items_And_Sourcing;
877
878
879 PROCEDURE Update_Bcso(
880 p_ato_line_id IN number,
881 l_return_status OUT NOCOPY varchar2,
882 l_msg_count OUT NOCOPY number,
883 l_msg_data OUT NOCOPY varchar2)
884 IS
885
886 --new cursor for bulk fetch (bug 10307286)
887 CURSOR c_line_info IS
888 select distinct
889 bcmo.group_reference_id,
890 bcmo.model_item_id,
891 bcso.line_id,
892 bcso.top_model_line_id,
893 bcso.config_item_id
894 from bom_cto_model_orgs bcmo,
895 bom_cto_src_orgs_b bcso
896 where bcso.config_item_id = bcmo.config_item_id and
897 bcso.config_item_id in (select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 ) */
898 distinct config_item_id
899 from bom_cto_order_lines_upg
900 where ato_line_id = p_ato_line_id and
901 nvl(config_creation, 1) = 3 and
902 config_item_id is not null and
903 status = 'UPG' and
904 nvl(perform_match, 'N')IN ('Y','U'));
905
906
907 lStmtNum number;
908 v_group_reference_id number;
909 v_model_item_id number;
910
911 --bug 10307286
912 TYPE line_id_rec is RECORD (group_reference_id number,
913 model_item_id number,
914 line_id number,
915 top_model_line_id number,
916 config_item_id number
917 );
918
919 TYPE line_id_rec_tab_typ is TABLE OF line_id_rec INDEX BY BINARY_INTEGER;
920 line_id_rec_tab line_id_rec_tab_typ;
921
922 BEGIN
923
924 --
925 -- 1. Identify each config for this ato_line_id having attribute 3
926 -- 2. For each config, identify all order lines
927 -- 3. For each order line, delete sourcing from bcso for this config
928 -- and insert line referencing bcmo.
929 --
930 l_return_status := FND_API.G_RET_STS_SUCCESS;
931
932 lStmtNum := 10;
933 WriteToLog('Entering update_bcso', 3);
934
935 --new bulk fetch for bug 10307286 (begin)
936 OPEN c_line_info;
937 FETCH c_line_info BULK COLLECT INTO line_id_rec_tab;
938 CLOSE c_line_info;
939
940 lStmtNum := 20;
941 WriteToLog('Fecthed records from c_line_info count is :'||line_id_rec_tab.count, 3);
942
943 DELETE from bom_cto_src_orgs_b
944 where (line_id) IN (select distinct bcso.line_id
945 from bom_cto_src_orgs_b bcso
946 where bcso.config_item_id in (select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 ) */
947 distinct config_item_id
948 from bom_cto_order_lines_upg
949 where ato_line_id = p_ato_line_id
950 and nvl(config_creation, 1) = 3
951 and config_item_id is not null
952 and status = 'UPG'
953 and nvl(perform_match, 'N') IN ('Y','U')));
954
955 WriteToLog('Deleted data from bom_cto_src_orgs_b count is :'||sql%rowcount, 3);
956
957
958 lStmtNum := 30;
959 FORALL cntr in 1..line_id_rec_tab.COUNT
960 insert into bom_cto_src_orgs_b
961 (
962 top_model_line_id,
963 line_id,
964 group_reference_id,
965 model_item_id,
966 rcv_org_id,
967 organization_id,
968 create_bom,
969 cost_rollup,
970 organization_type, -- Used to store the source type
971 config_item_id,
972 create_src_rules,
973 rank,
974 creation_date,
975 created_by,
976 last_update_date,
977 last_updated_by,
978 last_update_login,
979 program_application_id,
980 program_id,
981 program_update_date
982 )
983 Values (
984 line_id_rec_tab(cntr).top_model_line_id, -- p_ato_line_id ,
985 line_id_rec_tab(cntr).line_id,
986 line_id_rec_tab(cntr).group_reference_id,
987 line_id_rec_tab(cntr).model_item_id,
988 null,
989 -1, -- organization_id is -1 for type3 matched
990 null, -- create_bom
991 null, -- cost_rollup
992 NULL , -- org_type is used to store the source type
993 line_id_rec_tab(cntr).config_item_id, -- config_item_id
994 NULL,
995 NULL, -- rank
996 sysdate, -- creation_date
997 gUserId, -- created_by
998 sysdate, -- last_update_date
999 gUserId, -- last_updated_by
1000 gLoginId, -- last_update_login
1001 null, -- program_application_id
1002 null, -- program_id
1003 sysdate); -- program_update_date
1004
1005 WriteToLog('After insert into bom_cto_src_orgs_b count is :'||sql%rowcount, 3);
1006
1007 lStmtNum := 40;
1008 update /* INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1009 bom_cto_order_lines_upg
1010 set status = 'BCSO'
1011 where line_id in ( select distinct line_id
1012 from bom_cto_src_orgs_b
1013 where config_item_id in( select /*+INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 ) */
1014 distinct config_item_id
1015 from bom_cto_order_lines_upg
1016 where ato_line_id = p_ato_line_id
1017 and nvl(config_creation, 1) = 3
1018 and config_item_id is not null
1019 and status = 'UPG'));
1020
1021
1022 WriteToLog('Updated rows to status BCSO::'||sql%rowcount, 4);
1023
1024 --END LOOP;
1025 -- End of changes for 10307286
1026
1027 lStmtNum := 50;
1028 WriteToLog('Exiting update_bcso', 2);
1029
1030 EXCEPTION
1031 when OTHERS then
1032 WriteToLog('Others error in Update_bcso::lStmtNum::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
1033 cto_msg_pub.count_and_get
1034 ( p_msg_count => l_msg_count
1035 , p_msg_data => l_msg_data
1036 );
1037 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1038
1039 END Update_Bcso;
1040
1041
1042 PROCEDURE Update_Item_Data(
1043 p_cat_id IN number,
1044 xReturnStatus OUT NOCOPY varchar2,
1045 xMsgCount OUT NOCOPY number,
1046 xMsgData OUT NOCOPY varchar2)
1047 IS
1048
1049 lStmtNumber number;
1050 lMsgCount number;
1051 lMsgData varchar2(240);
1052 l_cto_cost_type_id number;
1053 l_layer_id number;
1054 x_err_num number;
1055 x_msg_name varchar2(240);
1056 l_rev_id number;
1057
1058 CURSOR c_layer IS
1059 select distinct
1060 MP1.organization_id org_id,
1061 DECODE(bcso.ORGANIZATION_ID, bcolu.ship_from_org_id, get_cost_group(bcolu.ship_from_org_id, bcolu.line_id), 1) cost_group_id,
1062 bcolu.config_item_id config_item_id
1063 from
1064 cst_item_costs c,
1065 bom_cto_src_orgs bcso,
1066 bom_cto_order_lines_upg bcolu,
1067 mtl_parameters mp1
1068 where bcolu.config_item_id is not null
1069 and bcolu.status = 'BCSO'
1070 and c.organization_id = bcso.organization_id
1071 and c.inventory_item_id = bcolu.inventory_item_id
1072 and C.COST_TYPE_ID = 2 -- Average Costing
1073 and bcso.model_item_id = bcolu.inventory_item_id
1074 and bcso.line_id = bcolu.line_id
1075 and mp1.organization_id = bcso.organization_id
1076 and MP1.Primary_cost_method = 2 -- Create only in Avg costing org
1077 and NOT EXISTS
1078 (select NULL
1079 from cst_quantity_layers
1080 where inventory_item_id = bcolu.config_item_id
1081 and organization_id = bcso.organization_id);
1082
1083 CURSOR c_bcolu_cfg IS
1084 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
1085 distinct
1086 config_item_id,
1087 inventory_item_id,
1088 line_id
1089 from bom_cto_order_lines_upg bcolu
1090 where bcolu.config_item_id is not null
1091 and bcolu.status = 'BCSO';
1092
1093 CURSOR c_get_org_id IS
1094 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
1095 bcolu.config_item_id cfg_item_id,
1096 bcolu.inventory_item_id model_item_id,
1097 msi.organization_id src_org_id
1098 from mtl_system_items msi,
1099 bom_cto_order_lines_upg bcolu
1100 where bcolu.config_item_id is not null
1101 and bcolu.status = 'BCSO'
1102 and msi.inventory_item_id = bcolu.config_item_id
1103 and not exists
1104 (SELECT 'x'
1105 FROM FND_ATTACHED_DOCUMENTS
1106 WHERE pk1_value = to_char(msi.organization_id)
1107 AND pk2_value = to_char(msi.inventory_item_id)
1108 AND entity_name = 'MTL_SYSTEM_ITEMS');
1109
1110
1111
1112 Type number_tbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
1113
1114
1115 TYPE cicd_summary_rec_tab is record (
1116 config_item_id number_tbl_type,
1117 cost_organization_id number_tbl_type,
1118 cost_type_id number_tbl_type,
1119 material_cost number_tbl_type,
1120 material_overhead_cost number_tbl_type,
1121 resource_cost number_tbl_type,
1122 outside_processing_cost number_tbl_type,
1123 overhead_cost number_tbl_type,
1124 item_cost number_tbl_type ) ;
1125
1126
1127
1128
1129 l_rt_cicd_summary cicd_summary_rec_tab ;
1130
1131
1132 v_cto_cost_type_name cst_cost_types.cost_type%type;
1133
1134 --kkonada R12
1135 --for mtl_cross_references_b
1136 --bug# 4539578
1137
1138 TYPE cfg_item_id IS TABLE OF bom_cto_order_lines_upg.config_item_id%type;
1139 TYPE org_id IS TABLE OF mtl_cross_references_b.organization_id%type;
1140 TYPE cross_reference_type IS TABLE OF mtl_cross_references_b.cross_reference_type%type;
1141 TYPE cross_reference IS TABLE OF mtl_cross_references_b.cross_reference%type;
1142 TYPE org_independent_flag IS TABLE OF mtl_cross_references_b.org_independent_flag%type;
1143
1144 t_cfg_item_id cfg_item_id;
1145 t_organization_id org_id;
1146 t_cross_ref_type cross_reference_type;
1147 t_cross_ref cross_reference;
1148 t_org_independent_flag org_independent_flag;
1149
1150 BEGIN
1151
1152 WriteToLog('Entering Update_Item_Data', 3);
1153 lStmtNumber := 10;
1154 xReturnStatus := FND_API.G_RET_STS_SUCCESS;
1155
1156 insert into mtl_system_items_tl (
1157 inventory_item_id,
1158 organization_id,
1159 language,
1160 source_lang,
1161 description,
1162 last_update_date,
1163 last_updated_by,
1164 creation_date,
1165 created_by,
1166 last_update_login
1167 )
1168 select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1169 distinct
1170 bcolu.config_item_id,
1171 m.organization_id,
1172 l.language_code,
1173 userenv('LANG'),
1174 m.description,
1175 sysdate,
1176 gUserId, --last_updated_by
1177 sysdate,
1178 gUserId, --created_by
1179 gLoginId --last_update_login
1180 from
1181 mtl_system_items_tl m,
1182 bom_cto_src_orgs bcso,
1183 bom_cto_order_lines_upg bcolu,
1184 fnd_languages l
1185 where bcolu.config_item_id is not null
1186 and bcolu.status = 'BCSO'
1187 and m.inventory_item_id = bcolu.inventory_item_id
1188 and bcso.model_item_id = bcolu.inventory_item_id
1189 and bcso.line_id = bcolu.line_id
1190 and m.organization_id = bcso.organization_id
1191 and l.installed_flag In ('I', 'B')
1192 and l.language_code = m.language
1193 and NOT EXISTS
1194 (select NULL
1195 from mtl_system_items_tl t
1196 where t.inventory_item_id = bcolu.config_item_id
1197 and t.organization_id = bcso.organization_id
1198 and t.language = l.language_code );
1199
1200 WriteToLog('Inserted rows into mtl_system_items_tl:: '||sql%rowcount,2);
1201
1202 lStmtNumber := 20;
1203 insert into MTL_PENDING_ITEM_STATUS (
1204 inventory_item_id,
1205 organization_id,
1206 status_code,
1207 effective_date,
1208 pending_flag,
1209 last_update_date,
1210 last_updated_by,
1211 creation_date,
1212 created_by,
1213 last_update_login,
1214 program_application_id,
1215 program_id,
1216 program_update_date,
1217 request_id)
1218 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1219 distinct
1220 bcolu.config_item_id,
1221 m.organization_id,
1222 m.inventory_item_status_code,
1223 sysdate,
1224 'N',
1225 sysdate,
1226 gUserId,
1227 sysdate,
1228 gUserId,
1229 gLoginId,
1230 null,
1231 null,
1232 sysdate,
1233 null -- req_id
1234 from mtl_system_items m,
1235 bom_cto_src_orgs bcso,
1236 bom_cto_order_lines_upg bcolu
1237 where bcolu.config_item_id is not null
1238 and bcolu.status = 'BCSO'
1239 and m.inventory_item_id = bcolu.inventory_item_id
1240 and bcso.model_item_id = bcolu.inventory_item_id
1241 and bcso.line_id = bcolu.line_id
1242 and m.organization_id = bcso.organization_id
1243 and NOT EXISTS
1244 (select NULL
1245 from MTL_PENDING_ITEM_STATUS
1246 where inventory_item_id = bcolu.config_item_id
1247 and organization_id = bcso.organization_id);
1248
1249 WriteToLog('Inserted rows into mtl_pending_item_status:: '||sql%rowcount,2);
1250
1251 /*-------------------------------------------+
1252 Insert Item revision information
1253 Till Patchset I, this information was inserted into mtl_item_revisions table.
1254 In Patchset I, the table were changed to mtl_item_revisions_b and mtl_item_revisions_tl.
1255 In Patchset I, we were constructing insert stmts dymanically to avoid BOM/INV odf dependency.
1256 In Patchset J, we are inserting into the new tables. Dependency will not be an issue because we are maintaining a separate branch for J and I.
1257 Cannot do bulk processing here, since a different revision_id needs to be generated for each config
1258 +-------------------------------------------*/
1259 lStmtNumber := 30;
1260
1261 FOR v_bcolu_cfg IN c_bcolu_cfg LOOP
1262 --removed as part of bugfix 3340844
1263 /*select MTL_ITEM_REVISIONS_B_S.nextval
1264 into l_rev_id
1265 from dual;*/
1266
1267
1268 WriteToLog('Going to insert rows into mtl_item_revisions_b:: '|| v_bcolu_cfg.config_item_id,2);
1269 insert into mtl_item_revisions_b
1270 (inventory_item_id,
1271 organization_id,
1272 revision,
1273 last_update_date,
1274 last_updated_by,
1275 creation_date,
1276 created_by,
1277 last_update_login,
1278 implementation_date,
1279 effectivity_date,
1280 OBJECT_VERSION_NUMBER,
1281 REVISION_ID,
1282 REVISION_LABEL --3340844
1283 )
1284 select --distinct
1285 v_bcolu_cfg.config_item_id,
1286 m.organization_id,
1287 mp1.starting_revision,
1288 sysdate,
1289 gUserId, -- last_updated_by
1290 sysdate,
1291 gUserId, -- created_by
1292 gLoginId, -- last_update_login
1293 sysdate,
1294 sysdate,
1295 1, --would be 1 for initial creation of item
1296 MTL_ITEM_REVISIONS_B_S.nextval, -- 3338108 --l_rev_id, --revision_id is generated from sequence
1297 mp1.starting_revision --3340844
1298 from
1299 mtl_parameters mp1,
1300 mtl_system_items m
1301 where m.inventory_item_id = v_bcolu_cfg.config_item_id
1302 and m.organization_id = mp1.organization_id
1303 and NOT EXISTS
1304 (select NULL
1305 from MTL_ITEM_REVISIONS_B
1306 where inventory_item_id = v_bcolu_cfg.config_item_id
1307 and organization_id = mp1.organization_id);
1308
1309 WriteToLog('Inserted rows into mtl_item_revisions_b:: '||sql%rowcount,2);
1310 --insert into _tl table so that item is visible in revisions form
1311 --for multi-lingual support
1312
1313 insert into mtl_item_revisions_tl (
1314 inventory_item_id,
1315 organization_id,
1316 revision_id,
1317 language,
1318 source_lang,
1319 description,
1320 last_update_date,
1321 last_updated_by,
1322 creation_date,
1323 created_by,
1324 last_update_login
1325 )
1326 select distinct
1327 v_bcolu_cfg.config_item_id,
1328 m.organization_id,
1329 mr.revision_id, --3338108 --l_rev_id
1330 l.language_code,
1331 userenv('LANG'),
1332 m.description,
1333 sysdate,
1334 gUserId, --last_updated_by
1335 sysdate,
1336 gUserId, --created_by
1337 gLoginId --last_update_login
1338 from
1339 mtl_system_items_tl m,
1340 bom_cto_src_orgs bcso,
1341 fnd_languages l,
1342 mtl_item_revisions_b mr --3338108
1343 where m.inventory_item_id = v_bcolu_cfg.inventory_item_id
1344 and bcso.model_item_id = m.inventory_item_id
1345 and bcso.line_id = v_bcolu_cfg.line_id
1346 and m.organization_id = bcso.organization_id
1347 and l.installed_flag In ('I', 'B')
1348 and l.language_code = m.language
1349 and mr.inventory_item_id = v_bcolu_cfg.config_item_id --3338108
1350 and mr.organization_id = bcso.organization_id --3338108
1351 and NOT EXISTS
1352 (select NULL
1353 from mtl_item_revisions_tl t
1354 where t.inventory_item_id = v_bcolu_cfg.config_item_id
1355 and t.organization_id = bcso.organization_id
1356 and t.revision_id = mr.revision_id --3338108
1357 and t.language = l.language_code );
1358
1359 WriteToLog('Inserted rows into mtl_item_revisions_tl:: '||sql%rowcount,2);
1360
1361 END LOOP; /* c_bcolu_cfg */
1362
1363
1364 /*----------------------------------------------------------+
1365 Insert cost records for config items
1366 The cost organization id is either the organization id
1367 or the master organization id
1368 +----------------------------------------------------------*/
1369
1370
1371
1372 lStmtNumber := 33;
1373 /* FIX to avoid rolled up cost of model from being included during cost rollup */
1374 /* begin Fix for bug 4172300. cost in validation org is not copied properly from model to config item */
1375
1376 --performance bugfix4905887 shared memroy 1MB (sqlid :16104932 )
1377 --Removed comments to max possible
1378 --bsco is a mergable view now
1379 --removed un-necessary join on mtl_system_items
1380 --Removed a Distinct clause in IN sub-query
1381
1382 select bcolu.config_item_id,
1383 C.organization_id, -- bug 4172300
1384 C.cost_type_id,
1385 nvl(sum(decode( cicd.cost_element_id, 1 , nvl(cicd.item_cost, 0 ) )) , 0 ),
1386 nvl( sum(decode( cicd.cost_element_id,2 , nvl( cicd.item_cost, 0 ) )) , 0 ),
1387 nvl( sum(decode( cicd.cost_element_id,3 , nvl( cicd.item_cost, 0 ) )) , 0 ),
1388 nvl( sum(decode( cicd.cost_element_id,4 , nvl( cicd.item_cost, 0 ) )) , 0 ),
1389 nvl( sum(decode( cicd.cost_element_id,5 , nvl( cicd.item_cost, 0 ) )) , 0 )
1390 BULK COLLECT INTO
1391 l_rt_cicd_summary.config_item_id,
1392 l_rt_cicd_summary.cost_organization_id,
1393 l_rt_cicd_summary.cost_type_id,
1394 l_rt_cicd_summary.material_cost,
1395 l_rt_cicd_summary.material_overhead_cost,
1396 l_rt_cicd_summary.resource_cost,
1397 l_rt_cicd_summary.outside_processing_cost,
1398 l_rt_cicd_summary.overhead_cost
1399 from
1400 mtl_parameters MP1,
1401 cst_item_costs C,
1402 cst_item_cost_details CICD,
1403 bom_cto_order_lines_upg bcolu
1404 where
1405 C.organization_id = MP1.organization_id
1406 and C.inventory_item_id = bcolu.inventory_item_id -- pModelId
1407 and C.COST_TYPE_ID IN ( MP1.primary_cost_method, MP1.avg_rates_cost_type_id)
1408 and C.inventory_item_id = CICD.inventory_item_id(+)
1409 and C.organization_id = CICD.organization_id(+)
1410 and C.cost_type_id = CICD.cost_type_id(+)
1411 and CICD.rollup_source_type(+) = 1 -- User Defined
1412 --bug 4172300
1413 and ( bcolu.line_id , C.inventory_item_id, C.organization_id) in
1414 ( select
1415 bcolu.line_id,
1416 bcolu.inventory_item_id,
1417 MP2.cost_organization_id
1418 from mtl_parameters mp2,
1419 mtl_parameters mp3,
1420 bom_cto_src_orgs bcso,
1421 bom_cto_order_lines_upg bcolu
1422 where bcolu.config_item_id is not null
1423 and bcolu.status = 'BCSO'
1424 and bcso.model_item_id = bcolu.inventory_item_id
1425 and bcso.model_item_id = bcolu.inventory_item_id
1426 and bcso.line_id = bcolu.line_id
1427 and MP3.organization_id = bcso.organization_id
1428 and ((mp2.organization_id = bcso.organization_id) OR
1429 (mp2.organization_id = mp3.master_organization_id))
1430 )
1431 and NOT EXISTS
1432 (select NULL
1433 from CST_ITEM_COSTS
1434 where inventory_item_id = bcolu.config_item_id
1435 and organization_id = mp1.cost_organization_id
1436 and cost_type_id in (mp1.primary_cost_method, mp1.avg_rates_cost_type_id))
1437 group by bcolu.config_item_id, C.organization_id, C.cost_type_id;
1438
1439
1440
1441 /* end Fix for bug 4172300. cost in validation org is not copied properly from model to config item */
1442
1443 if( l_rt_cicd_summary.cost_organization_id.count > 0 ) then
1444 for i in l_rt_cicd_summary.cost_organization_id.first..l_rt_cicd_summary.cost_organization_id.last
1445 loop
1446
1447 oe_debug_pub.add( i || ') ' || 'Cost Header Info: ' ||
1448 ' CFG ID ' || l_rt_cicd_summary.config_item_id(i) ||
1449 ' cst org ' || l_rt_cicd_summary.cost_organization_id(i) ||
1450 ' cst id ' || l_rt_cicd_summary.cost_type_id(i) ||
1451 ' m cost ' || l_rt_cicd_summary.material_cost(i) ||
1452 ' moh cost ' || l_rt_cicd_summary.material_overhead_cost(i) ||
1453 ' rsc cost ' || l_rt_cicd_summary.resource_cost(i) ||
1454 ' osp cost ' || l_rt_cicd_summary.outside_processing_cost(i) ||
1455 ' ovh cost ' || l_rt_cicd_summary.overhead_cost(i) , 1 );
1456
1457
1458
1459 l_rt_cicd_summary.item_cost(i) := l_rt_cicd_summary.material_cost(i) + l_rt_cicd_summary.material_overhead_cost(i)
1460 + l_rt_cicd_summary.resource_cost(i) + l_rt_cicd_summary.outside_processing_cost(i)
1461 + l_rt_cicd_summary.overhead_cost(i) ;
1462
1463
1464
1465 oe_debug_pub.add( ' item cost ' || l_rt_cicd_summary.item_cost(i) , 1 );
1466
1467
1468 end loop ;
1469
1470
1471 else
1472
1473
1474 oe_debug_pub.add( ' no new item cost records for upgrade ' , 1 );
1475
1476 end if;
1477
1478
1479 /*-------------------------------------------------------+
1480 Insert a row into the cst_item_costs_table
1481 +------------------------------------------------------- */
1482
1483 lStmtNumber := 40;
1484
1485 insert into CST_ITEM_COSTS
1486 (inventory_item_id,
1487 organization_id,
1488 cost_type_id,
1489 last_update_date,
1490 last_updated_by,
1491 creation_date,
1492 created_by,
1493 last_update_login,
1494 inventory_asset_flag,
1495 lot_size,
1496 based_on_rollup_flag,
1497 shrinkage_rate,
1498 defaulted_flag,
1499 cost_update_id,
1500 pl_material,
1501 pl_material_overhead,
1502 pl_resource,
1503 pl_outside_processing,
1504 pl_overhead,
1505 tl_material,
1506 tl_material_overhead,
1507 tl_resource,
1508 tl_outside_processing,
1509 tl_overhead,
1510 material_cost,
1511 material_overhead_cost,
1512 resource_cost,
1513 outside_processing_cost ,
1514 overhead_cost,
1515 pl_item_cost,
1516 tl_item_cost,
1517 item_cost,
1518 unburdened_cost ,
1519 burden_cost,
1520 attribute_category,
1521 attribute1,
1522 attribute2,
1523 attribute3,
1524 attribute4,
1525 attribute5,
1526 attribute6,
1527 attribute7,
1528 attribute8,
1529 attribute9,
1530 attribute10,
1531 attribute11,
1532 attribute12,
1533 attribute13,
1534 attribute14,
1535 attribute15
1536 )
1537 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1538 distinct
1539 bcolu.config_item_id, -- INVENTORY_ITEM_ID
1540 mp1.cost_organization_id,
1541 c.cost_type_id,
1542 sysdate, -- last_update_date
1543 -1, -- last_updated_by
1544 sysdate, -- creation_date
1545 -1, -- created_by
1546 -1, -- last_update_login
1547 C.inventory_asset_flag,
1548 C.lot_size,
1549 C.based_on_rollup_flag,
1550 C.shrinkage_rate,
1551 C.defaulted_flag,
1552 NULL, -- cost_update_id
1553 C.pl_material,
1554 C.pl_material_overhead,
1555 C.pl_resource,
1556 C.pl_outside_processing,
1557 C.pl_overhead,
1558 C.tl_material,
1559 C.tl_material_overhead,
1560 C.tl_resource,
1561 C.tl_outside_processing,
1562 C.tl_overhead,
1563 C.material_cost,
1564 C.material_overhead_cost,
1565 C.resource_cost,
1566 C.outside_processing_cost ,
1567 C.overhead_cost,
1568 C.pl_item_cost,
1569 C.tl_item_cost,
1570 C.item_cost,
1571 C.unburdened_cost ,
1572 C.burden_cost,
1573 C.attribute_category,
1574 C.attribute1,
1575 C.attribute2,
1576 C.attribute3,
1577 C.attribute4,
1578 C.attribute5,
1579 C.attribute6,
1580 C.attribute7,
1581 C.attribute8,
1582 C.attribute9,
1583 C.attribute10,
1584 C.attribute11,
1585 C.ATTRIBUTE12,
1586 C.attribute13,
1587 C.attribute14,
1588 C.attribute15
1589 from
1590 mtl_parameters MP1,
1591 cst_item_costs C,
1592 mtl_system_items S,
1593 bom_cto_src_orgs bcso,
1594 bom_cto_order_lines_upg bcolu
1595 where bcolu.config_item_id is not null
1596 and bcolu.status = 'BCSO'
1597 and S.organization_id = C.organization_id
1598 and S.inventory_item_id = C.inventory_item_id
1599 and C.inventory_item_id = bcolu.inventory_item_id
1600 and C.inventory_item_id = S.inventory_item_id
1601 and bcso.model_item_id = bcolu.inventory_item_id
1602 and bcso.line_id = bcolu.line_id
1603 and C.cost_type_id in ( mp1.primary_cost_method, mp1.avg_rates_cost_type_id)
1604 and C.organization_id = mp1.organization_id
1605 and mp1.organization_id = bcso.organization_id
1606 and NOT EXISTS
1607 (select NULL
1608 from CST_ITEM_COSTS
1609 where inventory_item_id = bcolu.config_item_id
1610 and organization_id = mp1.cost_organization_id --bugfix 3877097
1611 and cost_type_id in (mp1.primary_cost_method, mp1.avg_rates_cost_type_id));
1612
1613 WriteToLog('Inserted rows into cst_item_costs:: '||sql%rowcount,2);
1614
1615
1616
1617
1618
1619
1620
1621 if( l_rt_cicd_summary.cost_type_id.count> 0 ) then
1622 FORALL j IN 1..l_rt_cicd_summary.cost_type_id.last
1623 UPDATE cst_item_costs
1624 set material_cost = l_rt_cicd_summary.material_cost(j),
1625 material_overhead_cost = l_rt_cicd_summary.material_overhead_cost(j),
1626 resource_cost = l_rt_cicd_summary.resource_cost(j),
1627 outside_processing_cost = l_rt_cicd_summary.outside_processing_cost(j),
1628 overhead_cost = l_rt_cicd_summary.overhead_cost(j),
1629 tl_material = l_rt_cicd_summary.material_cost(j),
1630 tl_material_overhead = l_rt_cicd_summary.material_overhead_cost(j),
1631 tl_resource = l_rt_cicd_summary.resource_cost(j),
1632 tl_outside_processing = l_rt_cicd_summary.outside_processing_cost(j),
1633 tl_overhead = l_rt_cicd_summary.overhead_cost(j),
1634 tl_item_cost = l_rt_cicd_summary.item_cost(j),
1635 item_cost = l_rt_cicd_summary.item_cost(j),
1636 burden_cost = l_rt_cicd_summary.material_overhead_cost(j)
1637 where inventory_item_id = l_rt_cicd_summary.config_item_id(j) -- pConfigId
1638 and organization_id = l_rt_cicd_summary.cost_organization_id(j)
1639 and cost_type_id = l_rt_cicd_summary.cost_type_id(j) ;
1640
1641
1642 IF PG_DEBUG <> 0 THEN
1643 oe_debug_pub.add('Create_Item: ' || 'after update:CST_ITEM_COSTS '|| to_char(sql%rowcount),2);
1644 END IF;
1645 else
1646
1647 oe_debug_pub.add( 'No update required to CST_ITEM_COSTS as no new records inserted ' , 1 ) ;
1648
1649 end if;
1650
1651
1652
1653
1654
1655
1656 /* For standard costing orgs, we will copy model's user-defined
1657 cost in Frozen to the config in CTO cost type. */
1658
1659 lStmtNumber := 50;
1660
1661
1662 /* begin bugfix 4057651, default CTO cost type id = 7 if it does not exist */
1663 begin
1664
1665 select cost_type_id into l_cto_cost_type_id
1666 from cst_cost_types
1667 where cost_type = 'CTO' ;
1668
1669 exception
1670 when no_data_found then
1671
1672 IF PG_DEBUG <> 0 THEN
1673 oe_debug_pub.add('Create_Item: ' || ' no_data_found error CTO cost type id does not exist',2);
1674 oe_debug_pub.add('Create_Item: ' || ' defaulting CTO cost type id = 7 ',2);
1675 END IF;
1676
1677 l_cto_cost_type_id := 7 ;
1678
1679 begin
1680 select cost_type into v_cto_cost_type_name
1681 from cst_cost_types
1682 where cost_type_id = l_cto_cost_type_id ;
1683
1684 IF PG_DEBUG <> 0 THEN
1685 oe_debug_pub.add('Create_Item: ' || ' cost type id = ' || l_cto_cost_type_id ||
1686 ' has cost_type = ' || v_cto_cost_type_name ,2);
1687 END IF;
1688 exception
1689 when no_data_found then
1690 IF PG_DEBUG <> 0 THEN
1691 oe_debug_pub.add('Create_Item: ' || ' no_data_found error for cost type id = 7 ',2);
1692 END IF;
1693 cto_msg_pub.cto_message('BOM','CTO_COST_NOT_FOUND');
1694 raise FND_API.G_EXC_ERROR;
1695 when others then
1696
1697 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1698 end ;
1699
1700 when others then
1701 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1702 end ;
1703 /* end bugfix 4057651, default CTO cost type id = 7 if it does not exist */
1704
1705
1706
1707 lStmtNumber := 60;
1708 insert into CST_ITEM_COSTS
1709 (inventory_item_id,
1710 organization_id,
1711 cost_type_id,
1712 last_update_date,
1713 last_updated_by,
1714 creation_date,
1715 created_by,
1716 last_update_login,
1717 inventory_asset_flag,
1718 lot_size,
1719 based_on_rollup_flag,
1720 shrinkage_rate,
1721 defaulted_flag,
1722 cost_update_id,
1723 pl_material,
1724 pl_material_overhead,
1725 pl_resource,
1726 pl_outside_processing,
1727 pl_overhead,
1728 tl_material,
1729 tl_material_overhead,
1730 tl_resource,
1731 tl_outside_processing,
1732 tl_overhead,
1733 material_cost,
1734 material_overhead_cost,
1735 resource_cost,
1736 outside_processing_cost ,
1737 overhead_cost,
1738 pl_item_cost,
1739 tl_item_cost,
1740 item_cost,
1741 unburdened_cost ,
1742 burden_cost,
1743 attribute_category,
1744 attribute1,
1745 attribute2,
1746 attribute3,
1747 attribute4,
1748 attribute5,
1749 attribute6,
1750 attribute7,
1751 attribute8,
1752 attribute9,
1753 attribute10,
1754 attribute11,
1755 attribute12,
1756 attribute13,
1757 attribute14,
1758 attribute15
1759 )
1760 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1761 distinct
1762 bcolu.config_item_id, -- INVENTORY_ITEM_ID
1763 mp1.cost_organization_id,
1764 l_cto_cost_type_id, -- CTO cost_type_id,
1765 sysdate, -- last_update_date
1766 -1, -- last_updated_by
1767 sysdate, -- creation_date
1768 -1, -- created_by
1769 -1, -- last_update_login
1770 C.inventory_asset_flag,
1771 C.lot_size,
1772 C.based_on_rollup_flag,
1773 C.shrinkage_rate,
1774 C.defaulted_flag,
1775 NULL, -- cost_update_id
1776 C.pl_material,
1777 C.pl_material_overhead,
1778 C.pl_resource,
1779 C.pl_outside_processing,
1780 C.pl_overhead,
1781 C.tl_material,
1782 C.tl_material_overhead,
1783 C.tl_resource,
1784 C.tl_outside_processing,
1785 C.tl_overhead,
1786 C.material_cost,
1787 C.material_overhead_cost,
1788 C.resource_cost,
1789 C.outside_processing_cost ,
1790 C.overhead_cost,
1791 C.pl_item_cost,
1792 C.tl_item_cost,
1793 C.item_cost,
1794 C.unburdened_cost ,
1795 C.burden_cost,
1796 C.attribute_category,
1797 C.attribute1,
1798 C.attribute2,
1799 C.attribute3,
1800 C.attribute4,
1801 C.attribute5,
1802 C.attribute6,
1803 C.attribute7,
1804 C.attribute8,
1805 C.attribute9,
1806 C.attribute10,
1807 C.attribute11,
1808 C.ATTRIBUTE12,
1809 C.attribute13,
1810 C.attribute14,
1811 C.attribute15
1812 from
1813 mtl_parameters MP1,
1814 cst_item_costs C,
1815 mtl_system_items S,
1816 bom_cto_src_orgs bcso,
1817 bom_cto_order_lines_upg bcolu
1818 where bcolu.config_item_id is not null
1819 and bcolu.status = 'BCSO'
1820 and S.organization_id = C.organization_id
1821 and S.inventory_item_id = C.inventory_item_id
1822 and C.inventory_item_id = bcolu.inventory_item_id
1823 and C.inventory_item_id = S.inventory_item_id
1824 and bcso.model_item_id = bcolu.inventory_item_id
1825 and bcso.line_id = bcolu.line_id
1826 and C.cost_type_id = mp1.primary_cost_method
1827 and C.cost_type_id = 1
1828 and C.organization_id = bcso.organization_id
1829 and mp1.organization_id = bcso.organization_id
1830 and NOT EXISTS
1831 (select NULL
1832 from CST_ITEM_COSTS
1833 where inventory_item_id = bcolu.config_item_id
1834 and organization_id = mp1.cost_organization_id --bugfix 3877097
1835 and cost_type_id = l_cto_cost_type_id);
1836
1837 WriteToLog('Inserted rows into cst_item_costs:: '||sql%rowcount,2);
1838
1839
1840
1841
1842
1843
1844
1845 if( l_rt_cicd_summary.cost_type_id.count > 0 ) then
1846 FORALL j IN 1..l_rt_cicd_summary.cost_type_id.last
1847 UPDATE cst_item_costs
1848 set material_cost = l_rt_cicd_summary.material_cost(j),
1849 material_overhead_cost = l_rt_cicd_summary.material_overhead_cost(j),
1850 resource_cost = l_rt_cicd_summary.resource_cost(j),
1851 outside_processing_cost = l_rt_cicd_summary.outside_processing_cost(j),
1852 overhead_cost = l_rt_cicd_summary.overhead_cost(j),
1853 tl_material = l_rt_cicd_summary.material_cost(j),
1854 tl_material_overhead = l_rt_cicd_summary.material_overhead_cost(j),
1855 tl_resource = l_rt_cicd_summary.resource_cost(j),
1856 tl_outside_processing = l_rt_cicd_summary.outside_processing_cost(j),
1857 tl_overhead = l_rt_cicd_summary.overhead_cost(j),
1858 tl_item_cost = l_rt_cicd_summary.item_cost(j),
1859 item_cost = l_rt_cicd_summary.item_cost(j),
1860 burden_cost = l_rt_cicd_summary.material_overhead_cost(j)
1861 where inventory_item_id = l_rt_cicd_summary.config_item_id(j) -- pConfigId
1862 and organization_id = l_rt_cicd_summary.cost_organization_id(j)
1863 and cost_type_id = l_cto_cost_type_id ;
1864
1865
1866 IF PG_DEBUG <> 0 THEN
1867 oe_debug_pub.add('Create_Item: ' || 'after update:cst_item_costs for CTO cost type '||to_char(sql%rowcount),2);
1868 END IF;
1869
1870 else
1871
1872 oe_debug_pub.add( 'No update required to CST_ITEM_COSTS for CTO cost type as no new records inserted ' , 1 ) ;
1873
1874 end if;
1875
1876
1877
1878
1879 /*------ ----------------------------------------------+
1880 Insert rows into the cst_item_cost_details table
1881 +-----------------------------------------------------*/
1882
1883 lStmtNumber := 70;
1884
1885 insert into cst_item_cost_details
1886 (inventory_item_id,
1887 cost_type_id,
1888 last_update_date,
1889 last_updated_by,
1890 creation_date,
1891 created_by,
1892 last_update_login,
1893 organization_id,
1894 operation_sequence_id,
1895 operation_seq_num,
1896 department_id,
1897 level_type,
1898 activity_id,
1899 resource_seq_num,
1900 resource_id,
1901 resource_rate,
1902 item_units,
1903 activity_units,
1904 usage_rate_or_amount,
1905 basis_type,
1906 basis_resource_id,
1907 basis_factor,
1908 net_yield_or_shrinkage_factor,
1909 item_cost,
1910 cost_element_id,
1911 rollup_source_type,
1912 activity_context,
1913 attribute_category,
1914 attribute1,
1915 attribute2,
1916 attribute3,
1917 attribute4,
1918 attribute5,
1919 attribute6,
1920 attribute7,
1921 attribute8,
1922 attribute9,
1923 attribute10,
1924 attribute11,
1925 attribute12,
1926 attribute13,
1927 attribute14,
1928 attribute15
1929 )
1930 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1931 distinct
1932 bcolu.config_item_id, -- inventory_item_id
1933 c.cost_type_id,
1934 sysdate, -- last_update_date
1935 -1, -- last_updated_by
1936 sysdate, -- creation_date
1937 -1, -- created_by
1938 -1, -- last_update_login
1939 mp1.cost_organization_id,
1940 c.operation_sequence_id,
1941 c.operation_seq_num,
1942 c.department_id,
1943 c.level_type,
1944 c.activity_id,
1945 c.resource_seq_num,
1946 c.resource_id,
1947 c.resource_rate,
1948 c.item_units,
1949 c.activity_units,
1950 c.usage_rate_or_amount,
1951 c.basis_type,
1952 c.basis_resource_id,
1953 c.basis_factor,
1954 c.net_yield_or_shrinkage_factor,
1955 c.item_cost,
1956 c.cost_element_id,
1957 C.rollup_source_type,
1958 C.activity_context,
1959 C.attribute_category,
1960 C.attribute1,
1961 C.attribute2,
1962 C.attribute3,
1963 C.attribute4,
1964 C.attribute5,
1965 C.attribute6,
1966 C.attribute7,
1967 C.attribute8,
1968 C.attribute9,
1969 C.attribute10,
1970 C.attribute11,
1971 C.attribute12,
1972 C.attribute13,
1973 C.attribute14,
1974 C.attribute15
1975 from
1976 mtl_parameters MP1,
1977 cst_item_cost_details C,
1978 mtl_system_items S,
1979 bom_cto_src_orgs bcso,
1980 bom_cto_order_lines_upg bcolu
1981 where bcolu.config_item_id is not null
1982 and bcolu.status = 'BCSO'
1983 and S.organization_id = C.organization_id
1984 and S.inventory_item_id = C.inventory_item_id
1985 and bcso.model_item_id = bcolu.inventory_item_id
1986 and bcso.line_id = bcolu.line_id
1987 and C.organization_id = MP1.organization_id
1988 and C.inventory_item_id = bcolu.inventory_item_id
1989 and C.inventory_item_id = S.inventory_item_id
1990 and C.rollup_source_type = 1 -- User Defined
1991 and C.COST_TYPE_ID IN ( MP1.primary_cost_method, MP1.avg_rates_cost_type_id)
1992 and mp1.organization_id = bcso.organization_id
1993 and NOT EXISTS
1994 (select NULL
1995 from cst_item_cost_details
1996 where inventory_item_id = bcolu.config_item_id
1997 and organization_id = mp1.cost_organization_id --bugfix 3877097
1998 and COST_TYPE_ID IN (MP1.primary_cost_method, MP1.avg_rates_cost_type_id));
1999
2000 WriteToLog('Inserted rows into cst_item_cost_details:: '||sql%rowcount,2);
2001
2002
2003 /* For standard costing orgs, we will copy model's user-defined
2004 cost in Frozen to the config in CTO cost type. */
2005
2006 lStmtNumber := 80;
2007 insert into cst_item_cost_details
2008 (inventory_item_id,
2009 cost_type_id,
2010 last_update_date,
2011 last_updated_by,
2012 creation_date,
2013 created_by,
2014 last_update_login,
2015 organization_id,
2016 operation_sequence_id,
2017 operation_seq_num,
2018 department_id,
2019 level_type,
2020 activity_id,
2021 resource_seq_num,
2022 resource_id,
2023 resource_rate,
2024 item_units,
2025 activity_units,
2026 usage_rate_or_amount,
2027 basis_type,
2028 basis_resource_id,
2029 basis_factor,
2030 net_yield_or_shrinkage_factor,
2031 item_cost,
2032 cost_element_id,
2033 rollup_source_type,
2034 activity_context,
2035 attribute_category,
2036 attribute1,
2037 attribute2,
2038 attribute3,
2039 attribute4,
2040 attribute5,
2041 attribute6,
2042 attribute7,
2043 attribute8,
2044 attribute9,
2045 attribute10,
2046 attribute11,
2047 attribute12,
2048 attribute13,
2049 attribute14,
2050 attribute15
2051 )
2052 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2053 distinct
2054 bcolu.config_item_id, -- inventory_item_id
2055 l_cto_cost_type_id, -- CTO cost_type_id,
2056 sysdate, -- last_update_date
2057 -1, -- last_updated_by
2058 sysdate, -- creation_date
2059 -1, -- created_by
2060 -1, -- last_update_login
2061 mp1.cost_organization_id,
2062 c.operation_sequence_id,
2063 c.operation_seq_num,
2064 c.department_id,
2065 c.level_type,
2066 c.activity_id,
2067 c.resource_seq_num,
2068 c.resource_id,
2069 c.resource_rate,
2070 c.item_units,
2071 c.activity_units,
2072 c.usage_rate_or_amount,
2073 c.basis_type,
2074 c.basis_resource_id,
2075 c.basis_factor,
2076 c.net_yield_or_shrinkage_factor,
2077 c.item_cost,
2078 c.cost_element_id,
2079 C.rollup_source_type,
2080 C.activity_context,
2081 C.attribute_category,
2082 C.attribute1,
2083 C.attribute2,
2084 C.attribute3,
2085 C.attribute4,
2086 C.attribute5,
2087 C.attribute6,
2088 C.attribute7,
2089 C.attribute8,
2090 C.attribute9,
2091 C.attribute10,
2092 C.attribute11,
2093 C.attribute12,
2094 C.attribute13,
2095 C.attribute14,
2096 C.attribute15
2097 from
2098 mtl_parameters MP1,
2099 cst_item_cost_details C,
2100 mtl_system_items S,
2101 bom_cto_src_orgs bcso,
2102 bom_cto_order_lines_upg bcolu
2103 where bcolu.config_item_id is not null
2104 and bcolu.status = 'BCSO'
2105 and S.organization_id = C.organization_id
2106 and S.inventory_item_id = C.inventory_item_id
2107 and bcso.model_item_id = bcolu.inventory_item_id
2108 and bcso.line_id = bcolu.line_id
2109 and C.organization_id = MP1.organization_id
2110 and C.inventory_item_id = bcolu.inventory_item_id
2111 and C.inventory_item_id = S.inventory_item_id
2112 and C.rollup_source_type = 1 -- User Defined
2113 and C.COST_TYPE_ID = MP1.primary_cost_method
2114 and C.cost_type_id = 1
2115 and mp1.organization_id = bcso.organization_id
2116 and NOT EXISTS
2117 (select NULL
2118 from cst_item_cost_details
2119 where inventory_item_id = bcolu.config_item_id
2120 and organization_id = mp1.cost_organization_id --bugfix 3877097
2121 and COST_TYPE_ID = l_cto_cost_type_id);
2122 WriteToLog('Inserted rows into cst_item_cost_details:: '||sql%rowcount,2);
2123
2124
2125 lStmtNumber := 90;
2126
2127 IF ( nvl(fnd_profile.value('CST_AVG_COSTING_OPTION'), '1') = '2' ) THEN
2128 FOR v_layer in c_layer
2129 LOOP
2130
2131 --
2132 -- This costing API will insert a row into cst_quantity_layers
2133 -- for a unique layer_id and the given parameters.
2134 -- It will return 0 if failed, layer_id if succeeded
2135 --
2136 l_layer_id := cstpaclm.create_layer (
2137 i_org_id => v_layer.org_id,
2138 i_item_id => v_layer.config_item_id, --pConfigId,
2139 i_cost_group_id => v_layer.cost_group_id,
2140 i_user_id => gUserId,
2141 i_request_id => NULL,
2142 i_prog_id => NULL,
2143 i_prog_appl_id => NULL,
2144 i_txn_id => -1,
2145 o_err_num => x_err_num,
2146 o_err_code => x_msg_name,
2147 o_err_msg => lMsgData
2148 );
2149
2150 IF (l_layer_id = 0) THEN
2151 WriteToLog('Create_Item: ' || 'CST function create_layer returned with error '||to_char(x_err_num)||', '||x_msg_name||', '||
2152 lMsgData||'for '||to_char(v_layer.org_id)||', '||to_char(v_layer.cost_group_id),1);
2153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2154 ELSE
2155 WriteToLog('Inserted row into cql for '||to_char(l_layer_id)||', '||to_char(v_layer.org_id)||', '|| to_char(v_layer.cost_group_id),1);
2156 END IF;
2157
2158 END LOOP;
2159 END IF;
2160
2161
2162 /*--------------------------------------------------------+
2163 Insert rows into the mtl_desc_elem_val_interface table
2164 Descriptive elements are not organization controlled
2165 Using ship_from org in bcol_upg to get values
2166 +---------------------------------------------------------*/
2167
2168 lStmtNumber := 100;
2169
2170 insert into MTL_DESCR_ELEMENT_VALUES
2171 (inventory_item_id,
2172 element_name,
2173 last_update_date,
2174 last_updated_by,
2175 last_update_login,
2176 creation_date,
2177 created_by,
2178 element_value,
2179 default_element_flag,
2180 element_sequence,
2181 program_application_id,
2182 program_id,
2183 program_update_date,
2184 request_id
2185 )
2186 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2187 distinct
2188 bcolu.config_item_id, -- Inventory_item_id
2189 E.element_name, -- element_name
2190 sysdate, -- last_update_date
2191 gUserId, -- last_updated_by
2192 gLoginId, -- last_update_login
2193 sysdate, -- creation_date
2194 gUserId, -- created_by
2195 D.element_value, -- element_value
2196 E.default_element_flag, -- default_element_flag
2197 E.element_sequence, -- element_sequence
2198 NULL, -- program_application_id
2199 NULL, -- program_id
2200 SYSDATE, -- program_update_date
2201 NULL -- request_id
2202 from mtl_system_items s,
2203 mtl_descr_element_values D,
2204 mtl_descriptive_elements E,
2205 bom_cto_order_lines_upg bcolu
2206 where bcolu.config_item_id is not null
2207 and bcolu.status = 'BCSO'
2208 and D.inventory_item_id = S.inventory_item_id
2209 and s.inventory_item_id = bcolu.inventory_item_id
2210 and s.organization_id = bcolu.ship_from_org_id
2211 and E.item_catalog_group_id = S.item_catalog_group_id
2212 and E.element_name = D.element_name
2213 and NOT EXISTS
2214 (select NULL
2215 from mtl_descr_element_values
2216 where inventory_item_id = bcolu.config_item_id
2217 and organization_id = bcolu.ship_from_org_id);
2218
2219 WriteToLog('Inserted rows into mtl_descr_element_values:: '||sql%rowcount,2);
2220
2221
2222 /*--------------------------------------+
2223 Insert into mtl_item_categories
2224 Do not insert into CTO category if passed
2225 +--------------------------------------*/
2226 lStmtNumber := 120;
2227
2228 WriteToLog('Category id is ::'||p_cat_id, 3);
2229
2230 IF p_cat_id = -99 THEN
2231
2232 lStmtNumber := 122;
2233
2234 --FP bugfix 4861996
2235 --added condition ic1.category_set_id = ic.category_set_id
2236
2237 -- bug 13362916
2238 -- Modified for performance
2239 --
2240 insert into MTL_ITEM_CATEGORIES
2241 (inventory_item_id,
2242 category_set_id,
2243 category_id,
2244 last_update_date,
2245 last_updated_by,
2246 creation_date,
2247 created_by,
2248 last_update_login,
2249 request_id,
2250 program_application_id,
2251 program_id,
2252 program_update_date,
2253 organization_id
2254 )
2255 -- select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2256 select /*+ leading( bcolu, ic1, mp1, bcso, mp2, ic) */
2257 distinct
2258 bcolu.config_item_id,
2259 ic.category_set_id,
2260 ic.category_id,
2261 sysdate, -- last_update_date
2262 gUserId, -- last_updated_by
2263 sysdate, --creation_date
2264 gUserId, -- created_by
2265 gLoginId, -- last_update_login
2266 NULL, -- request_id
2267 NULL, -- program_application_id
2268 NULL, -- program_id
2269 SYSDATE, -- program_update_date
2270 ic.organization_id
2271 from
2272 mtl_item_categories ic,
2273 bom_cto_src_orgs bcso,
2274 bom_cto_order_lines_upg bcolu
2275 where bcolu.config_item_id is not null
2276 and bcolu.status = 'BCSO'
2277 and ic.inventory_item_id = bcolu.inventory_item_id
2278 and ic.organization_id = bcso.organization_id
2279 and bcso.model_item_id = ic.inventory_item_id
2280 and bcso.line_id = bcolu.line_id
2281 and CTO_CUSTOM_CATEGORY_PK.Copy_Category (ic.category_set_id , ic.organization_id) = 1
2282 and NOT EXISTS
2283 (select /*+ NO_UNNEST PUSH_SUBQ */ NULL
2284 from MTL_ITEM_CATEGORIES ic1
2285 where ic1.inventory_item_id = bcolu.config_item_id
2286 and ic1.organization_id = bcso.organization_id
2287 and ic1.category_set_id = ic.category_set_id
2288 and rownum = 1 -- bug 13876670
2289 );
2290
2291 WriteToLog('Inserted rows into mtl_item_categories:: '||sql%rowcount,2);
2292 lStmtNumber := 124;
2293
2294 --
2295 -- bug 13362916
2296 -- modified hints
2297 --
2298 insert into MTL_ITEM_CATEGORIES
2299 (inventory_item_id,
2300 category_set_id,
2301 category_id,
2302 last_update_date,
2303 last_updated_by,
2304 creation_date,
2305 created_by,
2306 last_update_login,
2307 request_id,
2308 program_application_id,
2309 program_id,
2310 program_update_date,
2311 organization_id
2312 )
2313 -- select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2314 select /*+ leading( bcolu, ic1, mp1, bcso, mp2, ic) */
2315 distinct
2316 bcolu.config_item_id,
2317 mcsb.category_set_id,
2318 mcsb.default_category_id,
2319 sysdate, -- last_update_date
2320 gUserId, -- last_updated_by
2321 sysdate, --creation_date
2322 gUserId, -- created_by
2323 gLoginId, -- last_update_login
2324 NULL, -- request_id
2325 NULL, -- program_application_id
2326 NULL, -- program_id
2327 SYSDATE, -- program_update_date
2328 ic.organization_id
2329 from
2330 mtl_item_categories ic,
2331 mtl_category_sets_b mcsb,
2332 mtl_default_category_sets mdcs,
2333 bom_cto_src_orgs bcso,
2334 bom_cto_order_lines_upg bcolu
2335 where bcolu.config_item_id is not null
2336 and bcolu.status = 'BCSO'
2337 and bcolu.inventory_item_id = ic.inventory_item_id
2338 and ic.organization_id = bcso.organization_id
2339 and bcso.model_item_id = bcolu.inventory_item_id
2340 and bcso.line_id = bcolu.line_id
2341 and mcsb.category_set_id = mdcs.category_set_id
2342 and mdcs.functional_area_id = 2
2343 and NOT EXISTS
2344 ( select /*+ NO_UNNEST PUSH_SUBQ */ NULL
2345 from MTL_ITEM_CATEGORIES
2346 where inventory_item_id = bcolu.config_item_id
2347 and organization_id = bcso.organization_id
2348 and category_set_id = mcsb.category_set_id
2349 and rownum = 1 -- bug 13876670
2350 );
2351
2352 WriteToLog('Inserted rows into mtl_item_categories for default categories:: '||sql%rowcount,2);
2353
2354 ELSE /* p_cat_id is passed */
2355
2356 lStmtNumber := 126;
2357
2358 --FP bugfix 4861996
2359 --added condition ic1.category_set_id = ic.category_set_id
2360
2361 --
2362 -- bug 13362916
2363 -- modified hints
2364 --
2365 insert into MTL_ITEM_CATEGORIES
2366 (inventory_item_id,
2367 category_set_id,
2368 category_id,
2369 last_update_date,
2370 last_updated_by,
2371 creation_date,
2372 created_by,
2373 last_update_login,
2374 request_id,
2375 program_application_id,
2376 program_id,
2377 program_update_date,
2378 organization_id
2379 )
2380 -- select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2381 select /*+ leading( bcolu, ic1, mp1, bcso, mp2, ic) */
2382 distinct
2383 bcolu.config_item_id,
2384 ic.category_set_id,
2385 ic.category_id,
2386 sysdate, -- last_update_date
2387 gUserId, -- last_updated_by
2388 sysdate, --creation_date
2389 gUserId, -- created_by
2390 gLoginId, -- last_update_login
2391 NULL, -- request_id
2392 NULL, -- program_application_id
2393 NULL, -- program_id
2394 SYSDATE, -- program_update_date
2395 ic.organization_id
2396 from
2397 mtl_item_categories ic,
2398 bom_cto_src_orgs bcso,
2399 bom_cto_order_lines_upg bcolu
2400 where bcolu.config_item_id is not null
2401 and bcolu.status = 'BCSO'
2402 and ic.inventory_item_id = bcolu.inventory_item_id
2403 and ic.organization_id = bcso.organization_id
2404 and ic.category_id <> p_cat_id -- CTO category
2405 and bcso.model_item_id = ic.inventory_item_id
2406 and bcso.line_id = bcolu.line_id
2407 and CTO_CUSTOM_CATEGORY_PK.Copy_Category (ic.category_set_id , ic.organization_id) = 1
2408 and NOT EXISTS
2409 (select /*+ NO_UNNEST PUSH_SUBQ */ NULL
2410 from MTL_ITEM_CATEGORIES ic1
2411 where ic1.inventory_item_id = bcolu.config_item_id
2412 and ic1.organization_id = bcso.organization_id
2413 and ic1.category_set_id = ic.category_set_id
2414 and rownum = 1 -- bug 13876670
2415 );
2416
2417 WriteToLog('Inserted rows into mtl_item_categories:: '||sql%rowcount,2);
2418 lStmtNumber := 128;
2419
2420 --
2421 -- bug 13362916
2422 -- modified hints
2423 --
2424 insert into MTL_ITEM_CATEGORIES
2425 (inventory_item_id,
2426 category_set_id,
2427 category_id,
2428 last_update_date,
2429 last_updated_by,
2430 creation_date,
2431 created_by,
2432 last_update_login,
2433 request_id,
2434 program_application_id,
2435 program_id,
2436 program_update_date,
2437 organization_id
2438 )
2439 -- select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2440 select /*+ leading( bcolu, ic1, mp1, bcso, mp2, ic) */
2441 distinct
2442 bcolu.config_item_id,
2443 mcsb.category_set_id,
2444 mcsb.default_category_id,
2445 sysdate, -- last_update_date
2446 gUserId, -- last_updated_by
2447 sysdate, --creation_date
2448 gUserId, -- created_by
2449 gLoginId, -- last_update_login
2450 NULL, -- request_id
2451 NULL, -- program_application_id
2452 NULL, -- program_id
2453 SYSDATE, -- program_update_date
2454 ic.organization_id
2455 from
2456 mtl_item_categories ic,
2457 mtl_category_sets_b mcsb,
2458 mtl_default_category_sets mdcs,
2459 bom_cto_src_orgs bcso,
2460 bom_cto_order_lines_upg bcolu
2461 where bcolu.config_item_id is not null
2462 and bcolu.status = 'BCSO'
2463 and bcolu.inventory_item_id = ic.inventory_item_id
2464 and ic.organization_id = bcso.organization_id
2465 and ic.category_id <> p_cat_id -- CTO category
2466 and bcso.model_item_id = bcolu.inventory_item_id
2467 and bcso.line_id = bcolu.line_id
2468 and mcsb.category_set_id = mdcs.category_set_id
2469 and mdcs.functional_area_id = 2
2470 and NOT EXISTS
2471 ( select /*+ NO_UNNEST PUSH_SUBQ */ NULL
2472 from MTL_ITEM_CATEGORIES
2473 where inventory_item_id = bcolu.config_item_id
2474 and organization_id = bcso.organization_id
2475 and category_set_id = mcsb.category_set_id
2476 and rownum = 1 -- bug 13876670
2477 );
2478
2479 WriteToLog('Inserted rows into mtl_item_categories for default categories:: '||sql%rowcount,2);
2480
2481 END IF; /* p_cat_id = -99 */
2482
2483
2484 /*----------------------------------------------------+
2485 Copy related items into MTL_RELATED_ITEMS table
2486 +----------------------------------------------------*/
2487
2488 lStmtNumber := 140;
2489
2490 insert into MTL_RELATED_ITEMS
2491 (
2492 inventory_item_id,
2493 related_item_id,
2494 relationship_type_id,
2495 reciprocal_flag,
2496 last_update_date,
2497 last_updated_by,
2498 creation_date,
2499 created_by,
2500 last_update_login,
2501 request_id,
2502 program_application_id,
2503 program_id,
2504 program_update_date,
2505 organization_id
2506 )
2507 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2508 distinct
2509 bcolu.config_item_id,
2510 ri.related_item_id,
2511 ri.relationship_type_id,
2512 ri.reciprocal_flag,
2513 sysdate, -- last_update_date
2514 gUserId, -- last_updated_by
2515 sysdate, --creation_date
2516 gUserId, -- created_by
2517 gLoginId, -- last_update_login
2518 NULL, -- request_id
2519 NULL, -- program_application_id
2520 NULL, -- program_id
2521 SYSDATE, -- program_update_date
2522 ri.organization_id
2523 from mtl_related_items ri,
2524 bom_cto_src_orgs bcso,
2525 bom_cto_order_lines_upg bcolu
2526 where bcolu.config_item_id is not null
2527 and bcolu.status = 'BCSO'
2528 and ri.inventory_item_id = bcolu.inventory_item_id
2529 and bcso.model_item_id = bcolu.inventory_item_id
2530 and bcso.line_id = bcolu.line_id
2531 and ri.organization_id = bcso.organization_id
2532 and NOT EXISTS
2533 (select NULL
2534 from mtl_related_items
2535 where inventory_item_id = bcolu.config_item_id
2536 and organization_id = bcso.organization_id);
2537
2538 WriteToLog('Inserted rows into mtl_related_items:: '||sql%rowcount,2);
2539
2540 /*--------------------------------------------------+
2541 Copy substitute inventories
2542 +--------------------------------------------------*/
2543
2544 lStmtNumber := 150;
2545
2546 insert into mtl_item_sub_inventories
2547 (
2548 inventory_item_id,
2549 organization_id,
2550 secondary_inventory,
2551 last_update_date,
2552 last_updated_by,
2553 creation_date,
2554 created_by,
2555 last_update_login,
2556 primary_subinventory_flag ,
2557 picking_order,
2558 min_minmax_quantity,
2559 max_minmax_quantity,
2560 inventory_planning_code,
2561 fixed_lot_multiple,
2562 minimum_order_quantity,
2563 maximum_order_quantity,
2564 source_type,
2565 source_organization_id,
2566 source_subinventory,
2567 attribute_category,
2568 attribute1,
2569 attribute2,
2570 attribute3,
2571 attribute4,
2572 attribute5,
2573 attribute6,
2574 attribute7,
2575 attribute8,
2576 attribute9,
2577 attribute10,
2578 attribute11,
2579 attribute12,
2580 attribute13,
2581 attribute14,
2582 attribute15,
2583 request_id,
2584 program_application_id ,
2585 program_id,
2586 program_update_date,
2587 encumbrance_account
2588 )
2589 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2590 distinct
2591 bcolu.config_item_id,
2592 isi.ORGANIZATION_ID,
2593 isi.SECONDARY_INVENTORY,
2594 sysdate, -- last_update_date
2595 gUserId, -- last_updated_by
2596 sysdate, -- creation_date
2597 gUserId, -- created_by
2598 gLoginId, -- last_update_login
2599 isi.PRIMARY_SUBINVENTORY_FLAG ,
2600 isi.PICKING_ORDER,
2601 isi.MIN_MINMAX_QUANTITY,
2602 isi.MAX_MINMAX_QUANTITY,
2603 isi.INVENTORY_PLANNING_CODE,
2604 isi.FIXED_LOT_MULTIPLE,
2605 isi.MINIMUM_ORDER_QUANTITY,
2606 isi.MAXIMUM_ORDER_QUANTITY,
2607 isi.SOURCE_TYPE,
2608 isi.SOURCE_ORGANIZATION_ID,
2609 isi.SOURCE_SUBINVENTORY,
2610 isi.ATTRIBUTE_CATEGORY,
2611 isi.ATTRIBUTE1,
2612 isi.ATTRIBUTE2,
2613 isi.ATTRIBUTE3,
2614 isi.ATTRIBUTE4,
2615 isi.ATTRIBUTE5,
2616 isi.ATTRIBUTE6,
2617 isi.ATTRIBUTE7,
2618 isi.ATTRIBUTE8,
2619 isi.ATTRIBUTE9,
2620 isi.ATTRIBUTE10,
2621 isi.ATTRIBUTE11,
2622 isi.ATTRIBUTE12,
2623 isi.ATTRIBUTE13,
2624 isi.ATTRIBUTE14,
2625 isi.ATTRIBUTE15,
2626 NULL, -- request_id
2627 NULL, -- program_application_id
2628 NULL, -- program_id
2629 SYSDATE, -- program_update_date
2630 isi.ENCUMBRANCE_ACCOUNT
2631 from
2632 mtl_item_sub_inventories isi,
2633 bom_cto_src_orgs bcso,
2634 bom_cto_order_lines_upg bcolu
2635 where bcolu.config_item_id is not null
2636 and bcolu.status = 'BCSO'
2637 and isi.organization_id = bcso.organization_id
2638 and isi.inventory_item_id = bcolu.inventory_item_id
2639 and bcso.model_item_id = bcolu.inventory_item_id
2640 and bcso.line_id = bcolu.line_id
2641 and NOT EXISTS
2642 (select NULL
2643 from mtl_item_sub_inventories
2644 where inventory_item_id = bcolu.config_item_id
2645 and organization_id = bcso.organization_id);
2646
2647 WriteToLog('Inserted rows into mtl_item_sub_inventories:: '||sql%rowcount,2);
2648
2649
2650 /*--------------------------------------+
2651 Copy secondary locators
2652 +--------------------------------------*/
2653
2654 lStmtNumber := 160;
2655
2656 insert into mtl_secondary_locators
2657 (
2658 inventory_item_id,
2659 organization_id,
2660 secondary_locator,
2661 primary_locator_flag,
2662 picking_order,
2663 subinventory_code,
2664 last_update_date,
2665 last_updated_by,
2666 creation_date,
2667 created_by,
2668 last_update_login,
2669 request_id,
2670 program_application_id,
2671 program_id,
2672 program_update_date
2673 )
2674 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2675 distinct
2676 bcolu.config_item_id,
2677 sl.organization_id,
2678 sl.secondary_locator,
2679 sl.primary_locator_flag,
2680 sl.picking_order,
2681 sl.subinventory_code,
2682 sysdate, -- last_update_date
2683 gUserId, -- last_updated_by
2684 sysdate, -- creation_date
2685 gUserId, -- created_by
2686 gLoginId, -- last_update_login
2687 NULL, -- request_id
2688 NULL, -- program_application_id
2689 NULL, -- program_id
2690 SYSDATE -- program_update_date
2691 from
2692 mtl_secondary_locators sl,
2693 bom_cto_src_orgs bcso,
2694 bom_cto_order_lines_upg bcolu
2695 where bcolu.config_item_id is not null
2696 and bcolu.status = 'BCSO'
2697 and sl.organization_id = bcso.organization_id
2698 and bcolu.inventory_item_id = sl.inventory_item_id
2699 and bcso.model_item_id = bcolu.inventory_item_id
2700 and bcso.line_id = bcolu.line_id
2701 and NOT EXISTS
2702 (select NULL
2703 from mtl_secondary_locators
2704 where inventory_item_id = bcolu.config_item_id
2705 and organization_id = bcso.organization_id);
2706
2707 WriteToLog('Inserted rows into mtl_secondary_locators:: '||sql%rowcount,2);
2708
2709 /*----------------------------------------+
2710 Copy cross references
2711 +----------------------------------------*/
2712
2713 lStmtNumber := 170;
2714
2715 --start bugfix 4539578
2716 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2717 distinct
2718 bcolu.config_item_id,
2719 cr_b.organization_id,
2720 cr_b.cross_reference_type,
2721 cr_b.cross_reference,
2722 cr_b.org_independent_flag
2723 BULK COLLECT INTO
2724 t_cfg_item_id,
2725 t_organization_id,
2726 t_cross_ref_type,
2727 t_cross_ref,
2728 t_org_independent_flag
2729 from
2730 mtl_cross_references_b cr_b,
2731 bom_cto_src_orgs bcso,
2732 bom_cto_order_lines_upg bcolu
2733 where bcolu.config_item_id is not null
2734 and bcolu.status = 'BCSO'
2735 and (cr_b.organization_id = bcso.organization_id or
2736 cr_b.organization_id is NULL)
2737 and cr_b.inventory_item_id = bcolu.inventory_item_id
2738 and bcso.model_item_id = bcolu.inventory_item_id
2739 and bcso.line_id = bcolu.line_id
2740 and NOT EXISTS
2741 (select NULL
2742 from mtl_cross_references
2743 where inventory_item_id = bcolu.config_item_id
2744 and (organization_id = bcso.organization_id
2745 or organization_id is null)); -- bugfix 1960994: added OR condition
2746
2747
2748 IF t_cross_ref_type.count <> 0 THEN
2749
2750 FORALL i IN 1..t_cross_ref_type.count
2751 INSERT INTO MTL_CROSS_REFERENCES_B
2752 (
2753 INVENTORY_ITEM_ID
2754 ,ORGANIZATION_ID
2755 ,CROSS_REFERENCE_TYPE
2756 ,CROSS_REFERENCE
2757 ,ORG_INDEPENDENT_FLAG
2758 ,LAST_UPDATE_DATE
2759 ,LAST_UPDATED_BY
2760 ,CREATION_DATE
2761 ,CREATED_BY
2762 ,LAST_UPDATE_LOGIN
2763 ,REQUEST_ID
2764 ,PROGRAM_APPLICATION_ID
2765 ,PROGRAM_ID
2766 ,PROGRAM_UPDATE_DATE
2767 ,SOURCE_SYSTEM_ID
2768 ,OBJECT_VERSION_NUMBER
2769 ,UOM_CODE
2770 ,REVISION_ID
2771 ,CROSS_REFERENCE_ID
2772 ,EPC_GTIN_SERIAL
2773 ,ATTRIBUTE1
2774 ,ATTRIBUTE2
2775 ,ATTRIBUTE3
2776 ,ATTRIBUTE4
2777 ,ATTRIBUTE5
2778 ,ATTRIBUTE6
2779 ,ATTRIBUTE7
2780 ,ATTRIBUTE8
2781 ,ATTRIBUTE9
2782 ,ATTRIBUTE10
2783 ,ATTRIBUTE11
2784 ,ATTRIBUTE12
2785 ,ATTRIBUTE13
2786 ,ATTRIBUTE14
2787 ,ATTRIBUTE15
2788 ,ATTRIBUTE_CATEGORY
2789 )
2790 VALUES
2791 (
2792 t_cfg_item_id(i)
2793 ,t_organization_id(i)
2794 ,t_cross_ref_type(i)
2795 ,t_cross_ref(i)
2796 ,t_org_independent_flag(i)
2797 ,SYSDATE
2798 ,GUSERID
2799 ,SYSDATE
2800 ,GUSERID
2801 ,GLOGINID
2802 ,NULL --REQUEST_ID
2803 ,NULL --PROGRAM_APPLICATION_ID
2804 ,NULL --PROGRAM_ID
2805 ,SYSDATE --PROGRAM_UPDATE_DATE
2806 ,NULL --SOURCE_SYSTEM_ID
2807 ,1 --OBJECT_VERSION_NUMBER
2808 ,NULL --UOM_CODE due to ER#3215422. do not copy uom_code and revision_id attribute for mtl_cross_references
2809 ,NULL --REVISION_ID due to ER#3215422. do not copy uom_code and revision_id attribute for mtl_cross_references
2810 ,MTL_CROSS_REFERENCES_B_S.NEXTVAL --CROSS_REFERENCE_ID
2811 ,0 --EPC_GTIN_SERIAL
2812 ,NULL --ATTRIBUTE1
2813 ,NULL --ATTRIBUTE2
2814 ,NULL --ATTRIBUTE3
2815 ,NULL --ATTRIBUTE4
2816 ,NULL --ATTRIBUTE5
2817 ,NULL --ATTRIBUTE6
2818 ,NULL --ATTRIBUTE7
2819 ,NULL --ATTRIBUTE8
2820 ,NULL --ATTRIBUTE9
2821 ,NULL --ATTRIBUTE10
2822 ,NULL --ATTRIBUTE11
2823 ,NULL --ATTRIBUTE12
2824 ,NULL --ATTRIBUTE13
2825 ,NULL --ATTRIBUTE14
2826 ,NULL --ATTRIBUTE15
2827 ,NULL --ATTRIBUTE_CATEGORY
2828 );
2829
2830 WriteToLog('Inserted rows into mtl_cross_references_b:: '||sql%rowcount,2);
2831
2832 FORALL i IN 1..t_cfg_item_id.count
2833 INSERT INTO mtl_cross_references_tl (
2834 last_update_login
2835 ,description
2836 ,creation_date
2837 ,created_by
2838 ,last_update_date
2839 ,last_updated_by
2840 ,cross_reference_id
2841 ,language
2842 ,source_lang)
2843 SELECT
2844 gloginid,
2845 mtl.description,
2846 sysdate,
2847 guserid,
2848 sysdate,
2849 guserid,
2850 mtl_cross.cross_reference_id,
2851 l.language_code,
2852 userenv('lang')
2853 FROM fnd_languages l,
2854 mtl_cross_references_b mtl_cross,
2855 mtl_system_items_tl mtl
2856 WHERE mtl_cross.inventory_item_id = t_cfg_item_id(i)
2857 AND mtl_cross.inventory_item_id = mtl.inventory_item_id
2858 AND mtl_cross.organization_id = mtl.organization_id
2859 AND l.language_code = mtl.language
2860 AND l.installed_flag in ('I', 'B')
2861 AND NOT EXISTS (SELECT null
2862 FROM mtl_cross_references_tl t
2863 WHERE t.cross_reference_id = mtl_cross.cross_reference_id
2864 AND t.language = l.language_code);
2865
2866 WriteToLog('Inserted rows into mtl_cross_references_tl:: '||sql%rowcount,2);
2867
2868
2869 END IF; --t_cross_ref_type.count()
2870
2871 --end bugfix # 4539578
2872
2873
2874
2875 /*--------------------------------------+
2876 Copy Subinventory Defaults
2877 +--------------------------------------*/
2878
2879 lStmtNumber := 180;
2880
2881 insert into mtl_item_sub_defaults
2882 (
2883 inventory_item_id,
2884 organization_id,
2885 subinventory_code,
2886 default_type,
2887 last_update_date,
2888 last_updated_by,
2889 creation_date,
2890 created_by,
2891 last_update_login,
2892 request_id,
2893 program_application_id,
2894 program_id,
2895 program_update_date
2896 )
2897 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2898 distinct
2899 bcolu.config_item_id,
2900 sd.organization_id,
2901 sd.subinventory_code,
2902 sd.default_type,
2903 sysdate, -- last_update_date
2904 gUserId, -- last_updated_by
2905 sysdate, -- creation_date
2906 gUserId, -- created_by
2907 gLoginId, -- last_update_login
2908 NULL, -- request_id
2909 NULL, -- program_application_id
2910 NULL, -- program_id
2911 SYSDATE -- program_update_date
2912 from
2913 mtl_item_sub_defaults sd,
2914 bom_cto_src_orgs bcso,
2915 bom_cto_order_lines_upg bcolu
2916 where bcolu.config_item_id is not null
2917 and bcolu.status = 'BCSO'
2918 and sd.organization_id = bcso.organization_id
2919 and sd.inventory_item_id = bcolu.inventory_item_id
2920 and bcso.model_item_id = bcolu.inventory_item_id
2921 and bcso.line_id = bcolu.line_id
2922 and NOT EXISTS
2923 (select NULL
2924 from mtl_item_sub_defaults
2925 where inventory_item_id = bcolu.config_item_id
2926 and organization_id = bcso.organization_id);
2927
2928 WriteToLog('Inserted rows into mtl_item_sub_defaults:: '||sql%rowcount,2);
2929
2930
2931 /*--------------------------------------+
2932 Copy Locator Defaults
2933 +--------------------------------------*/
2934
2935 lStmtNumber := 190;
2936 insert into mtl_item_loc_defaults
2937 (
2938 inventory_item_id,
2939 organization_id,
2940 locator_id,
2941 default_type,
2942 subinventory_code,
2943 last_update_date,
2944 last_updated_by,
2945 creation_date,
2946 created_by,
2947 last_update_login,
2948 request_id,
2949 program_application_id,
2950 program_id,
2951 program_update_date
2952 )
2953 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2954 distinct
2955 bcolu.config_item_id,
2956 ld.organization_id,
2957 ld.locator_id,
2958 ld.default_type,
2959 ld.subinventory_code,
2960 sysdate, -- last_update_date
2961 gUserId, -- last_updated_by
2962 sysdate, -- creation_date
2963 gUserId, -- created_by
2964 gLoginId, -- last_update_login
2965 NULL, -- request_id
2966 NULL, -- program_application_id
2967 NULL, -- program_id
2968 SYSDATE -- program_update_date
2969 from
2970 mtl_item_loc_defaults ld,
2971 bom_cto_src_orgs bcso,
2972 bom_cto_order_lines_upg bcolu
2973 where bcolu.config_item_id is not null
2974 and bcolu.status = 'BCSO'
2975 and ld.organization_id = bcso.organization_id
2976 and ld.inventory_item_id = bcso.model_item_id
2977 and bcso.model_item_id = bcolu.inventory_item_id
2978 and bcso.line_id = bcolu.line_id
2979 and NOT EXISTS
2980 (select NULL
2981 from mtl_item_loc_defaults
2982 where inventory_item_id = bcolu.config_item_id
2983 and organization_id = ld.organization_id);
2984
2985
2986 WriteToLog('Inserted rows into mtl_item_loc_defaults:: '||sql%rowcount,2);
2987 --
2988 -- create item attachments in loop
2989 --
2990 lStmtNumber := 200;
2991 FOR v_get_org_id in c_get_org_id LOOP
2992
2993 fnd_attached_documents2_pkg.copy_attachments (
2994 X_from_entity_name => 'MTL_SYSTEM_ITEMS',
2995 X_from_pk1_value => v_get_org_id.src_org_id,
2996 X_from_pk2_value => v_get_org_id.model_item_id,
2997 X_from_pk3_value => NULL,
2998 X_from_pk4_value => NULL,
2999 X_from_pk5_value => NULL,
3000 X_to_entity_name => 'MTL_SYSTEM_ITEMS',
3001 X_to_pk1_value => v_get_org_id.src_org_id,
3002 X_to_pk2_value => v_get_org_id.cfg_item_id,
3003 X_to_pk3_value => NULL,
3004 X_to_pk4_value => NULL,
3005 X_to_pk5_value => NULL,
3006 X_created_by => fnd_global.USER_ID,
3007 X_last_update_login => fnd_global.USER_ID,
3008 X_program_application_id => fnd_global.PROG_APPL_ID,
3009 X_program_id => fnd_global.CONC_REQUEST_ID,
3010 X_request_id => fnd_global.USER_ID,
3011 X_automatically_added_flag => NULL
3012 );
3013
3014 WriteToLog('Done copy attachment for org id::'|| v_get_org_id.src_org_id,5);
3015 END LOOP;
3016
3017
3018 EXCEPTION
3019 WHEN NO_DATA_FOUND THEN
3020 WriteToLog ('ERROR:NDF in update_item_data'||to_char(lStmtNumber)||sqlerrm,1);
3021 CTO_MSG_PUB.Count_And_Get(
3022 p_msg_count => lMsgCount,
3023 p_msg_data => lMsgData
3024 );
3025 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR;
3026
3027 WHEN FND_API.G_EXC_ERROR THEN
3028 WriteToLog ('ERROR:Expected error in update_item_data'||to_char(lStmtNumber)||sqlerrm,1);
3029 CTO_MSG_PUB.Count_And_Get(
3030 p_msg_count => lMsgCount,
3031 p_msg_data => lMsgData
3032 );
3033 xReturnStatus := FND_API.G_RET_STS_ERROR;
3034
3035 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3036 WriteToLog ('ERROR:Unexpected error in update_item_data'||to_char(lStmtNumber)||sqlerrm,1);
3037 CTO_MSG_PUB.Count_And_Get(
3038 p_msg_count => lMsgCount,
3039 p_msg_data => lMsgData
3040 );
3041 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR;
3042
3043 WHEN OTHERS THEN
3044 WriteToLog ('ERROR:Others error in update_item_data'||to_char(lStmtNumber)||sqlerrm,1);
3045 CTO_MSG_PUB.Count_And_Get(
3046 p_msg_count => lMsgCount,
3047 p_msg_data => lMsgData
3048 );
3049 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR;
3050
3051 END Update_Item_Data;
3052
3053
3054 function get_cost_group( pOrgId in number,
3055 pLineID in number)
3056 return integer is
3057
3058 l_cst_grp number;
3059
3060 begin
3061
3062 /*--------------------------------------------+
3063 This is a function to get cost_group_id
3064 for using in insert to cst_quantity_layers
3065 +---------------------------------------------*/
3066
3067 select nvl(costing_group_id,1)
3068 into l_cst_grp
3069 from pjm_project_parameters ppp
3070 where ppp.project_id = ( select project_id
3071 from oe_order_lines_all ol
3072 where ol.line_id = pLineId )
3073 and ppp.organization_id = pOrgId;
3074
3075 if (l_cst_grp = 0) then
3076 l_cst_grp := 1;
3077 end if;
3078
3079 return(l_cst_grp);
3080
3081 exception
3082 when no_data_found then
3083 WriteToLog('get_cost_group: ' || 'ERROR: Could not fetch the cost_group_id from pjm_project_parameters (NDF)',1);
3084 return(0);
3085
3086 end get_cost_group;
3087
3088
3089 PROCEDURE Update_Acc_Items(
3090 xReturnStatus OUT NOCOPY varchar2,
3091 xMsgCount OUT NOCOPY number,
3092 xMsgData OUT NOCOPY varchar2)
3093
3094 IS
3095
3096 lStmtNumber number;
3097 lItemType varchar2(30);
3098
3099 BEGIN
3100
3101 WriteToLog('Entering Update_Acc_Items', 3);
3102
3103 lStmtNumber := 10;
3104 xReturnStatus := FND_API.G_RET_STS_SUCCESS;
3105
3106 lItemType := FND_PROFILE.Value('BOM:CONFIG_ITEM_TYPE');
3107
3108 --perf bugfix 4905887 (sql id 16105473)
3109 --Removed comments to extent possible
3110
3111 -- Bug 9223457.added additional attribute columns added in 12.1
3112 -- for mtl_sys_items.pdube
3113
3114 insert into mtl_system_items_b
3115 (inventory_item_id,
3116 organization_id,
3117 last_update_date,
3118 last_updated_by,
3119 creation_date,
3120 created_by,
3121 last_update_login,
3122 summary_flag,
3123 enabled_flag,
3124 start_date_active,
3125 end_date_active,
3126 description,
3127 buyer_id,
3128 accounting_rule_id,
3129 invoicing_rule_id,
3130 segment1,
3131 segment2,
3132 segment3,
3133 segment4,
3134 segment5,
3135 segment6,
3136 segment7,
3137 segment8,
3138 segment9,
3139 segment10,
3140 segment11,
3141 segment12,
3142 segment13,
3143 segment14,
3144 segment15,
3145 segment16,
3146 segment17,
3147 segment18,
3148 segment19,
3149 segment20,
3150 attribute_category,
3151 attribute1,
3152 attribute2,
3153 attribute3,
3154 attribute4,
3155 attribute5,
3156 attribute6,
3157 attribute7,
3158 attribute8,
3159 attribute9,
3160 attribute10,
3161 attribute11,
3162 attribute12,
3163 attribute13,
3164 attribute14,
3165 attribute15,
3166 attribute16, -- Bug 9223457
3167 attribute17,
3168 attribute18,
3169 attribute19,
3170 attribute20,
3171 attribute21,
3172 attribute22,
3173 attribute23,
3174 attribute24,
3175 attribute25,
3176 attribute26,
3177 attribute27,
3178 attribute28,
3179 attribute29,
3180 attribute30,
3181 purchasing_item_flag,
3182 shippable_item_flag,
3183 customer_order_flag,
3184 internal_order_flag,
3185 service_item_flag,
3186 inventory_item_flag,
3187 eng_item_flag,
3188 inventory_asset_flag,
3189 purchasing_enabled_flag,
3190 customer_order_enabled_flag,
3191 internal_order_enabled_flag,
3192 so_transactions_flag,
3193 mtl_transactions_enabled_flag,
3194 stock_enabled_flag,
3195 bom_enabled_flag,
3196 build_in_wip_flag,
3197 revision_qty_control_code,
3198 item_catalog_group_id,
3199 catalog_status_flag,
3200 returnable_flag,
3201 default_shipping_org,
3202 collateral_flag,
3203 taxable_flag,
3204 allow_item_desc_update_flag,
3205 inspection_required_flag,
3206 receipt_required_flag,
3207 market_price,
3208 hazard_class_id,
3209 rfq_required_flag,
3210 qty_rcv_tolerance,
3211 un_number_id,
3212 price_tolerance_percent,
3213 asset_category_id,
3214 rounding_factor,
3215 unit_of_issue,
3216 enforce_ship_to_location_code,
3217 allow_substitute_receipts_flag,
3218 allow_unordered_receipts_flag,
3219 allow_express_delivery_flag,
3220 days_early_receipt_allowed,
3221 days_late_receipt_allowed,
3222 receipt_days_exception_code,
3223 receiving_routing_id,
3224 invoice_close_tolerance,
3225 receive_close_tolerance,
3226 auto_lot_alpha_prefix,
3227 start_auto_lot_number,
3228 lot_control_code,
3229 shelf_life_code,
3230 shelf_life_days,
3231 serial_number_control_code,
3232 start_auto_serial_number,
3233 auto_serial_alpha_prefix,
3234 source_type,
3235 source_organization_id,
3236 source_subinventory,
3237 expense_account,
3238 encumbrance_account,
3239 restrict_subinventories_code,
3240 unit_weight,
3241 weight_uom_code,
3242 volume_uom_code,
3243 unit_volume,
3244 restrict_locators_code,
3245 location_control_code,
3246 shrinkage_rate,
3247 acceptable_early_days,
3248 planning_time_fence_code,
3249 demand_time_fence_code,
3250 lead_time_lot_size,
3251 std_lot_size,
3252 cum_manufacturing_lead_time,
3253 overrun_percentage,
3254 acceptable_rate_increase,
3255 acceptable_rate_decrease,
3256 cumulative_total_lead_time,
3257 planning_time_fence_days,
3258 demand_time_fence_days,
3259 end_assembly_pegging_flag,
3260 planning_exception_set,
3261 bom_item_type,
3262 pick_components_flag,
3263 replenish_to_order_flag,
3264 base_item_id,
3265 atp_components_flag,
3266 atp_flag,
3267 fixed_lead_time,
3268 variable_lead_time,
3269 wip_supply_locator_id,
3270 wip_supply_type,
3271 wip_supply_subinventory,
3272 primary_uom_code,
3273 primary_unit_of_measure,
3274 allowed_units_lookup_code,
3275 cost_of_sales_account,
3276 sales_account,
3277 default_include_in_rollup_flag,
3278 inventory_item_status_code,
3279 inventory_planning_code,
3280 planner_code,
3281 planning_make_buy_code,
3282 fixed_lot_multiplier,
3283 rounding_control_type,
3284 carrying_cost,
3285 postprocessing_lead_time,
3286 preprocessing_lead_time,
3287 full_lead_time,
3288 order_cost,
3289 mrp_safety_stock_percent,
3290 mrp_safety_stock_code,
3291 min_minmax_quantity,
3292 max_minmax_quantity,
3293 minimum_order_quantity,
3294 fixed_order_quantity,
3295 fixed_days_supply,
3296 maximum_order_quantity,
3297 atp_rule_id,
3298 picking_rule_id,
3299 reservable_type,
3300 positive_measurement_error,
3301 negative_measurement_error,
3302 engineering_ecn_code,
3303 engineering_item_id,
3304 engineering_date,
3305 service_starting_delay,
3306 vendor_warranty_flag,
3307 serviceable_component_flag,
3308 serviceable_product_flag,
3309 base_warranty_service_id,
3310 payment_terms_id,
3311 preventive_maintenance_flag,
3312 primary_specialist_id,
3313 secondary_specialist_id,
3314 serviceable_item_class_id,
3315 time_billable_flag,
3316 material_billable_flag,
3317 expense_billable_flag,
3318 prorate_service_flag,
3319 coverage_schedule_id,
3320 service_duration_period_code,
3321 service_duration,
3322 max_warranty_amount,
3323 response_time_period_code,
3324 response_time_value,
3325 new_revision_code,
3326 tax_code,
3327 must_use_approved_vendor_flag,
3328 safety_stock_bucket_days,
3329 auto_reduce_mps,
3330 costing_enabled_flag,
3331 invoiceable_item_flag,
3332 invoice_enabled_flag,
3333 outside_operation_flag,
3334 outside_operation_uom_type,
3335 auto_created_config_flag,
3336 cycle_count_enabled_flag,
3337 item_type,
3338 model_config_clause_name,
3339 ship_model_complete_flag,
3340 mrp_planning_code,
3341 repetitive_planning_flag,
3342 return_inspection_requirement,
3343 effectivity_control,
3344 request_id,
3345 program_application_id,
3346 program_id,
3347 program_update_date,
3348 comms_nl_trackable_flag,
3349 default_so_source_type,
3350 create_supply_flag,
3351 lot_status_enabled,
3352 default_lot_status_id,
3353 serial_status_enabled,
3354 default_serial_status_id,
3355 lot_split_enabled,
3356 lot_merge_enabled,
3357 bulk_picked_flag,
3358 FINANCING_ALLOWED_FLAG,
3359 EAM_ITEM_TYPE ,
3360 EAM_ACTIVITY_TYPE_CODE,
3361 EAM_ACTIVITY_CAUSE_CODE,
3362 EAM_ACT_NOTIFICATION_FLAG,
3363 EAM_ACT_SHUTDOWN_STATUS,
3364 SUBSTITUTION_WINDOW_CODE,
3365 SUBSTITUTION_WINDOW_DAYS,
3366 PRODUCT_FAMILY_ITEM_ID,
3367 CHECK_SHORTAGES_FLAG,
3368 PLANNED_INV_POINT_FLAG,
3369 OVER_SHIPMENT_TOLERANCE,
3370 UNDER_SHIPMENT_TOLERANCE,
3371 OVER_RETURN_TOLERANCE,
3372 UNDER_RETURN_TOLERANCE,
3373 PURCHASING_TAX_CODE,
3374 OVERCOMPLETION_TOLERANCE_TYPE,
3375 OVERCOMPLETION_TOLERANCE_VALUE,
3376 INVENTORY_CARRY_PENALTY,
3377 OPERATION_SLACK_PENALTY,
3378 UNIT_LENGTH,
3379 UNIT_WIDTH,
3380 UNIT_HEIGHT,
3381 LOT_TRANSLATE_ENABLED,
3382 CONTAINER_ITEM_FLAG,
3383 VEHICLE_ITEM_FLAG,
3384 DIMENSION_UOM_CODE,
3385 SECONDARY_UOM_CODE,
3386 MAXIMUM_LOAD_WEIGHT,
3387 MINIMUM_FILL_PERCENT,
3388 CONTAINER_TYPE_CODE,
3389 INTERNAL_VOLUME,
3390 EQUIPMENT_TYPE,
3391 INDIVISIBLE_FLAG,
3392 GLOBAL_ATTRIBUTE_CATEGORY,
3393 GLOBAL_ATTRIBUTE1,
3394 GLOBAL_ATTRIBUTE2,
3395 GLOBAL_ATTRIBUTE3,
3396 GLOBAL_ATTRIBUTE4,
3397 GLOBAL_ATTRIBUTE5,
3398 GLOBAL_ATTRIBUTE6,
3399 GLOBAL_ATTRIBUTE7,
3400 GLOBAL_ATTRIBUTE8,
3401 GLOBAL_ATTRIBUTE9,
3402 GLOBAL_ATTRIBUTE10,
3403 DUAL_UOM_CONTROL,
3404 DUAL_UOM_DEVIATION_HIGH,
3405 DUAL_UOM_DEVIATION_LOW,
3406 CONTRACT_ITEM_TYPE_CODE,
3407 SUBSCRIPTION_DEPEND_FLAG,
3408 SERV_REQ_ENABLED_CODE,
3409 SERV_BILLING_ENABLED_FLAG,
3410 RELEASE_TIME_FENCE_CODE,
3411 RELEASE_TIME_FENCE_DAYS,
3412 DEFECT_TRACKING_ON_FLAG,
3413 SERV_IMPORTANCE_LEVEL,
3414 WEB_STATUS,
3415 tracking_quantity_ind,
3416 ont_pricing_qty_source,
3417 approval_status,
3418 vmi_minimum_units,
3419 vmi_minimum_days,
3420 vmi_maximum_units,
3421 vmi_maximum_days,
3422 vmi_fixed_order_quantity,
3423 so_authorization_flag,
3424 consigned_flag,
3425 asn_autoexpire_flag,
3426 vmi_forecast_type,
3427 forecast_horizon,
3428 days_tgt_inv_supply,
3429 days_tgt_inv_window,
3430 days_max_inv_supply,
3431 days_max_inv_window,
3432 critical_component_flag,
3433 drp_planned_flag,
3434 exclude_from_budget_flag,
3435 convergence,
3436 continous_transfer,
3437 divergence,
3438 --r12,4574899
3439 lot_divisible_flag,
3440 grade_control_flag,
3441 child_lot_flag,
3442 child_lot_validation_flag,
3443 copy_lot_attribute_flag,
3444 parent_child_generation_flag, --Bugfix 8821149
3445 lot_substitution_enabled, --Bugfix 8821149
3446 recipe_enabled_flag,
3447 process_quality_enabled_flag,
3448 process_execution_enabled_flag,
3449 process_costing_enabled_flag,
3450 hazardous_material_flag,
3451 preposition_point,
3452 repair_program,
3453 outsourced_assembly
3454
3455 )
3456 select distinct
3457 bcolu.config_item_id,
3458 mp1.organization_id,
3459 sysdate,
3460 gUserId,
3461 sysdate,
3462 gUserId,
3463 gLoginId ,
3464 m.summary_flag,
3465 m.enabled_flag,
3466 m.start_date_active,
3467 m.end_date_active,
3468 m.description,
3469 m.buyer_id,
3470 m.accounting_rule_id,
3471 m.invoicing_rule_id,
3472 -- c. copy from config item
3473 c.segment1,
3474 c.segment2,
3475 c.segment3,
3476 c.segment4,
3477 c.segment5,
3478 c.segment6,
3479 c.segment7,
3480 c.segment8,
3481 c.segment9,
3482 c.segment10,
3483 c.segment11,
3484 c.segment12,
3485 c.segment13,
3486 c.segment14,
3487 c.segment15,
3488 c.segment16,
3489 c.segment17,
3490 c.segment18,
3491 c.segment19,
3492 c.segment20,
3493 m.attribute_category,
3494 m.attribute1,
3495 m.attribute2,
3496 m.attribute3,
3497 m.attribute4,
3498 m.attribute5,
3499 m.attribute6,
3500 m.attribute7,
3501 m.attribute8,
3502 m.attribute9,
3503 m.attribute10,
3504 m.attribute11,
3505 m.attribute12,
3506 m.attribute13,
3507 m.attribute14,
3508 m.attribute15,
3509 m.attribute16, -- Bug 9223457
3510 m.attribute17,
3511 m.attribute18,
3512 m.attribute19,
3513 m.attribute20,
3514 m.attribute21,
3515 m.attribute22,
3516 m.attribute23,
3517 m.attribute24,
3518 m.attribute25,
3519 m.attribute26,
3520 m.attribute27,
3521 m.attribute28,
3522 m.attribute29,
3523 m.attribute30,
3524 'Y',
3525 'Y',
3526 'Y',
3527 'Y',
3528 m.service_item_flag,
3529 'Y',
3530 m.eng_item_flag,
3531 m.inventory_asset_flag,
3532 'Y',
3533 'Y',
3534 'Y',
3535 'Y',
3536 'Y',
3537 'Y',
3538 'Y',
3539 'Y',
3540 m.revision_qty_control_code,
3541 m.item_catalog_group_id,
3542 m.catalog_status_flag,
3543 m.returnable_flag,
3544 m.default_shipping_org,
3545 m.collateral_flag,
3546 m.taxable_flag,
3547 m.allow_item_desc_update_flag,
3548 m.inspection_required_flag,
3549 m.receipt_required_flag,
3550 m.market_price,
3551 m.hazard_class_id,
3552 m.rfq_required_flag,
3553 m.qty_rcv_tolerance,
3554 m.un_number_id,
3555 m.price_tolerance_percent,
3556 m.asset_category_id,
3557 m.rounding_factor,
3558 m.unit_of_issue,
3559 m.enforce_ship_to_location_code,
3560 m.allow_substitute_receipts_flag,
3561 m.allow_unordered_receipts_flag,
3562 m.allow_express_delivery_flag,
3563 m.days_early_receipt_allowed,
3564 m.days_late_receipt_allowed,
3565 m.receipt_days_exception_code,
3566 m.receiving_routing_id,
3567 m.invoice_close_tolerance,
3568 m.receive_close_tolerance,
3569 m.auto_lot_alpha_prefix,
3570 m.start_auto_lot_number,
3571 m.lot_control_code,
3572 m.shelf_life_code,
3573 m.shelf_life_days,
3574 m.serial_number_control_code,
3575 m.start_auto_serial_number,
3576 m.auto_serial_alpha_prefix,
3577 m.source_type,
3578 m.source_organization_id,
3579 m.source_subinventory,
3580 m.expense_account,
3581 m.encumbrance_account,
3582 m.restrict_subinventories_code,
3583 -- c. copy from config item
3584 c.unit_weight,
3585 c.weight_uom_code,
3586 c.volume_uom_code,
3587 c.unit_volume,
3588 m.restrict_locators_code,
3589 m.location_control_code,
3590 m.shrinkage_rate,
3591 m.acceptable_early_days,
3592 m.planning_time_fence_code,
3593 m.demand_time_fence_code,
3594 m.lead_time_lot_size,
3595 m.std_lot_size,
3596 m.cum_manufacturing_lead_time,
3597 m.overrun_percentage,
3598 m.acceptable_rate_increase,
3599 m.acceptable_rate_decrease,
3600 m.cumulative_total_lead_time,
3601 m.planning_time_fence_days,
3602 m.demand_time_fence_days,
3603 m.end_assembly_pegging_flag,
3604 m.planning_exception_set,
3605 4, -- BOM_ITEM_TYPE:standard
3606 'N',
3607 'Y',
3608 m.inventory_item_id, -- Base Model ID
3609 CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(m.atp_flag, m.atp_components_flag),
3610 CTO_CONFIG_ITEM_PK.get_atp_flag,
3611 m.fixed_lead_time,
3612 m.variable_lead_time,
3613 m.wip_supply_locator_id,
3614 m.wip_supply_type,
3615 m.wip_supply_subinventory,
3616 m.primary_uom_code,
3617 m.primary_unit_of_measure,
3618 m.allowed_units_lookup_code,
3619 m.cost_of_sales_account,
3620 m.sales_account,
3621 'Y',
3622 m.inventory_item_status_code,
3623 m.inventory_planning_code,
3624 m.planner_code,
3625 m.planning_make_buy_code,
3626 m.fixed_lot_multiplier,
3627 m.rounding_control_type,
3628 m.carrying_cost,
3629 m.postprocessing_lead_time,
3630 m.preprocessing_lead_time,
3631 m.full_lead_time,
3632 m.order_cost,
3633 m.mrp_safety_stock_percent,
3634 m.mrp_safety_stock_code,
3635 m.min_minmax_quantity,
3636 m.max_minmax_quantity,
3637 m.minimum_order_quantity,
3638 m.fixed_order_quantity,
3639 m.fixed_days_supply,
3640 m.maximum_order_quantity,
3641 m.atp_rule_id,
3642 m.picking_rule_id,
3643 1, -- m.reservable_type
3644 m.positive_measurement_error,
3645 m.negative_measurement_error,
3646 m.engineering_ecn_code,
3647 m.engineering_item_id,
3648 m.engineering_date,
3649 m.service_starting_delay,
3650 m.vendor_warranty_flag,
3651 m.serviceable_component_flag,
3652 m.serviceable_product_flag,
3653 m.base_warranty_service_id,
3654 m.payment_terms_id,
3655 m.preventive_maintenance_flag,
3656 m.primary_specialist_id,
3657 m.secondary_specialist_id,
3658 m.serviceable_item_class_id,
3659 m.time_billable_flag,
3660 m.material_billable_flag,
3661 m.expense_billable_flag,
3662 m.prorate_service_flag,
3663 m.coverage_schedule_id,
3664 m.service_duration_period_code,
3665 m.service_duration,
3666 m.max_warranty_amount,
3667 m.response_time_period_code,
3668 m.response_time_value,
3669 m.new_revision_code,
3670 m.tax_code,
3671 m.must_use_approved_vendor_flag,
3672 m.safety_stock_bucket_days,
3673 m.auto_reduce_mps,
3674 m.costing_enabled_flag,
3675 m.invoiceable_item_flag,
3676 m.invoice_enabled_flag,
3677 m.outside_operation_flag,
3678 m.outside_operation_uom_type,
3679 'Y', -- auto created config flag
3680 m.cycle_count_enabled_flag,
3681 lItemType, --copy from profile
3682 m.model_config_clause_name,
3683 m.ship_model_complete_flag,
3684 m.mrp_planning_code,
3685 m.repetitive_planning_flag,
3686 m.return_inspection_requirement,
3687 nvl(m.effectivity_control, 1),
3688 null,
3689 null,
3690 99, -- prg_id (to identify orgs where item is created by this program)
3691 sysdate,
3692 m.comms_nl_trackable_flag,
3693 nvl(m.default_so_source_type,'INTERNAL'),
3694 nvl(m.create_supply_flag, 'Y'),
3695 m.lot_status_enabled,
3696 m.default_lot_status_id,
3697 m.serial_status_enabled,
3698 m.default_serial_status_id,
3699 m.lot_split_enabled,
3700 m.lot_merge_enabled,
3701 m.bulk_picked_flag,
3702 m.FINANCING_ALLOWED_FLAG,
3703 m.EAM_ITEM_TYPE ,
3704 m.EAM_ACTIVITY_TYPE_CODE,
3705 m.EAM_ACTIVITY_CAUSE_CODE,
3706 m.EAM_ACT_NOTIFICATION_FLAG,
3707 m.EAM_ACT_SHUTDOWN_STATUS,
3708 m.SUBSTITUTION_WINDOW_CODE,
3709 m.SUBSTITUTION_WINDOW_DAYS,
3710 null,--5385901 m.PRODUCT_FAMILY_ITEM_ID,
3711 m.CHECK_SHORTAGES_FLAG,
3712 m.PLANNED_INV_POINT_FLAG,
3713 m.OVER_SHIPMENT_TOLERANCE,
3714 m.UNDER_SHIPMENT_TOLERANCE,
3715 m.OVER_RETURN_TOLERANCE,
3716 m.UNDER_RETURN_TOLERANCE,
3717 m.PURCHASING_TAX_CODE,
3718 m.OVERCOMPLETION_TOLERANCE_TYPE,
3719 m.OVERCOMPLETION_TOLERANCE_VALUE,
3720 m.INVENTORY_CARRY_PENALTY,
3721 m.OPERATION_SLACK_PENALTY,
3722 m.UNIT_LENGTH,
3723 m.UNIT_WIDTH,
3724 m.UNIT_HEIGHT,
3725 m.LOT_TRANSLATE_ENABLED,
3726 m.CONTAINER_ITEM_FLAG,
3727 m.VEHICLE_ITEM_FLAG,
3728 m.DIMENSION_UOM_CODE,
3729 m.SECONDARY_UOM_CODE,
3730 m.MAXIMUM_LOAD_WEIGHT,
3731 m.MINIMUM_FILL_PERCENT,
3732 m.CONTAINER_TYPE_CODE,
3733 m.INTERNAL_VOLUME,
3734 m.EQUIPMENT_TYPE,
3735 m.INDIVISIBLE_FLAG,
3736 m.GLOBAL_ATTRIBUTE_CATEGORY,
3737 m.GLOBAL_ATTRIBUTE1,
3738 m.GLOBAL_ATTRIBUTE2,
3739 m.GLOBAL_ATTRIBUTE3,
3740 m.GLOBAL_ATTRIBUTE4,
3741 m.GLOBAL_ATTRIBUTE5,
3742 m.GLOBAL_ATTRIBUTE6,
3743 m.GLOBAL_ATTRIBUTE7,
3744 m.GLOBAL_ATTRIBUTE8,
3745 m.GLOBAL_ATTRIBUTE9,
3746 m.GLOBAL_ATTRIBUTE10,
3747 m.DUAL_UOM_CONTROL,
3748 m.DUAL_UOM_DEVIATION_HIGH,
3749 m.DUAL_UOM_DEVIATION_LOW,
3750 m.CONTRACT_ITEM_TYPE_CODE,
3751 m.SUBSCRIPTION_DEPEND_FLAG,
3752 m.SERV_REQ_ENABLED_CODE,
3753 m.SERV_BILLING_ENABLED_FLAG,
3754 m.RELEASE_TIME_FENCE_CODE,
3755 m.RELEASE_TIME_FENCE_DAYS,
3756 m.DEFECT_TRACKING_ON_FLAG,
3757 m.SERV_IMPORTANCE_LEVEL,
3758 m.web_status,
3759 m.tracking_quantity_ind,
3760 m.ont_pricing_qty_source,
3761 m.approval_status,
3762 m.vmi_minimum_units,
3763 m.vmi_minimum_days,
3764 m.vmi_maximum_units,
3765 m.vmi_maximum_days,
3766 m.vmi_fixed_order_quantity,
3767 m.so_authorization_flag,
3768 m.consigned_flag,
3769 m.asn_autoexpire_flag,
3770 m.vmi_forecast_type,
3771 m.forecast_horizon,
3772 m.days_tgt_inv_supply,
3773 m.days_tgt_inv_window,
3774 m.days_max_inv_supply,
3775 m.days_max_inv_window,
3776 m.critical_component_flag,
3777 m.drp_planned_flag,
3778 m.exclude_from_budget_flag,
3779 m.convergence,
3780 m.continous_transfer,
3781 m.divergence,
3782 --r12,4574899
3783 'N',
3784 'N',
3785 /* Bugfix 8821149: Will populate these values from model.
3786 'N',
3787 'N',
3788 'N',
3789 */
3790 m.child_lot_flag,
3791 m.child_lot_validation_flag,
3792 m.copy_lot_attribute_flag,
3793 m.parent_child_generation_flag,
3794 m.lot_substitution_enabled,
3795 -- End Bugfix 8821149
3796 'N',
3797 'N',
3798 'N',
3799 'N',
3800 'N',
3801 'N',
3802 3,
3803 2
3804
3805 from
3806 mtl_parameters mp1,
3807 mtl_system_items_b m, -- model
3808 mtl_system_items_b c, -- config
3809 bom_cto_src_orgs bcso,
3810 bom_cto_order_lines_upg bcolu
3811 where bcolu.config_item_id is not null
3812 and bcolu.status = 'BCSO'
3813 and m.inventory_item_id = bcolu.inventory_item_id
3814 -- get config item row for any one org
3815 and c.inventory_item_id = bcolu.config_item_id
3816 and c.organization_id = ( select organization_id from mtl_system_items
3817 where inventory_item_id = c.inventory_item_id and rownum = 1) /*BUGFIX 3576040 */
3818 -- config is not pc in any orgs
3819 and not exists
3820 (select 'pc'
3821 from mtl_system_items msi1
3822 where msi1.inventory_item_id = bcolu.config_item_id
3823 and nvl(msi1.auto_created_config_flag,'N') = 'N')
3824 and bcso.model_item_id = bcolu.inventory_item_id
3825 and bcso.line_id = bcolu.line_id
3826 and m.organization_id = mp1.organization_id
3827 and mp1.organization_id = bcso.organization_id
3828 and NOT EXISTS
3829 (select NULL
3830 from mtl_system_items_b
3831 where inventory_item_id = bcolu.config_item_id
3832 and organization_id = mp1.organization_id);
3833
3834 WriteToLog('Items created::'||sql%rowcount, 2);
3835
3836 EXCEPTION
3837 WHEN OTHERS THEN
3838 WriteToLog ('ERROR: Others error in Update_Acc_Items::'||to_char(lStmtNumber)||sqlerrm,1);
3839 CTO_MSG_PUB.Count_And_Get(
3840 p_msg_count => xMsgCount,
3841 p_msg_data => xMsgData
3842 );
3843 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR;
3844
3845 END Update_Acc_Items;
3846
3847
3848 FUNCTION get_attribute_control( p_attribute_name in varchar2)
3849 RETURN NUMBER
3850 IS
3851
3852 v_attribute_name varchar2(100);
3853
3854 BEGIN
3855 v_attribute_name := UPPER( p_attribute_name );
3856
3857 for i in 1..g_attribute_name_tab.count
3858 loop
3859 if( g_attribute_name_tab(i) = v_attribute_name ) then
3860 return g_control_level_tab(i);
3861 end if;
3862
3863 end loop ;
3864
3865 return 0;
3866
3867 END get_attribute_control;
3868
3869
3870 PROCEDURE Update_Pc_Items(
3871 xReturnStatus OUT NOCOPY varchar2,
3872 xMsgCount OUT NOCOPY number,
3873 xMsgData OUT NOCOPY varchar2)
3874 IS
3875
3876 lStmtNumber number;
3877 lItemType varchar2(30);
3878
3879 BEGIN
3880
3881 WriteToLog('Entering Update_Pc_Items', 3);
3882 lStmtNumber := 10;
3883 xReturnStatus := FND_API.G_RET_STS_SUCCESS;
3884
3885 lItemType := FND_PROFILE.Value('BOM:CONFIG_ITEM_TYPE');
3886
3887 select substr(attribute_name, instr(attribute_name, '.' )+ 1), control_level
3888 BULK COLLECT
3889 INTO g_attribute_name_tab, g_control_level_tab
3890 from mtl_item_attributes
3891 where control_level = 1 ;
3892
3893
3894 --perf bugfix 4905887 (sql id 16105473)
3895 --Removed comments to extent possible
3896
3897 lStmtNumber := 20;
3898 insert into mtl_system_items_b
3899 (inventory_item_id,
3900 organization_id,
3901 last_update_date,
3902 last_updated_by,
3903 creation_date,
3904 created_by,
3905 last_update_login,
3906 summary_flag,
3907 enabled_flag,
3908 start_date_active,
3909 end_date_active,
3910 description,
3911 buyer_id,
3912 accounting_rule_id,
3913 invoicing_rule_id,
3914 segment1,
3915 segment2,
3916 segment3,
3917 segment4,
3918 segment5,
3919 segment6,
3920 segment7,
3921 segment8,
3922 segment9,
3923 segment10,
3924 segment11,
3925 segment12,
3926 segment13,
3927 segment14,
3928 segment15,
3929 segment16,
3930 segment17,
3931 segment18,
3932 segment19,
3933 segment20,
3934 attribute_category,
3935 attribute1,
3936 attribute2,
3937 attribute3,
3938 attribute4,
3939 attribute5,
3940 attribute6,
3941 attribute7,
3942 attribute8,
3943 attribute9,
3944 attribute10,
3945 attribute11,
3946 attribute12,
3947 attribute13,
3948 attribute14,
3949 attribute15,
3950 attribute16, -- Bug 9223457
3951 attribute17,
3952 attribute18,
3953 attribute19,
3954 attribute20,
3955 attribute21,
3956 attribute22,
3957 attribute23,
3958 attribute24,
3959 attribute25,
3960 attribute26,
3961 attribute27,
3962 attribute28,
3963 attribute29,
3964 attribute30,
3965 purchasing_item_flag,
3966 shippable_item_flag,
3967 customer_order_flag,
3968 internal_order_flag,
3969 service_item_flag,
3970 inventory_item_flag,
3971 eng_item_flag,
3972 inventory_asset_flag,
3973 purchasing_enabled_flag,
3974 customer_order_enabled_flag,
3975 internal_order_enabled_flag,
3976 so_transactions_flag,
3977 mtl_transactions_enabled_flag,
3978 stock_enabled_flag,
3979 bom_enabled_flag,
3980 build_in_wip_flag,
3981 revision_qty_control_code,
3982 item_catalog_group_id,
3983 catalog_status_flag,
3984 returnable_flag,
3985 default_shipping_org,
3986 collateral_flag,
3987 taxable_flag,
3988 allow_item_desc_update_flag,
3989 inspection_required_flag,
3990 receipt_required_flag,
3991 market_price,
3992 hazard_class_id,
3993 rfq_required_flag,
3994 qty_rcv_tolerance,
3995 un_number_id,
3996 price_tolerance_percent,
3997 asset_category_id,
3998 rounding_factor,
3999 unit_of_issue,
4000 enforce_ship_to_location_code,
4001 allow_substitute_receipts_flag,
4002 allow_unordered_receipts_flag,
4003 allow_express_delivery_flag,
4004 days_early_receipt_allowed,
4005 days_late_receipt_allowed,
4006 receipt_days_exception_code,
4007 receiving_routing_id,
4008 invoice_close_tolerance,
4009 receive_close_tolerance,
4010 auto_lot_alpha_prefix,
4011 start_auto_lot_number,
4012 lot_control_code,
4013 shelf_life_code,
4014 shelf_life_days,
4015 serial_number_control_code,
4016 start_auto_serial_number,
4017 auto_serial_alpha_prefix,
4018 source_type,
4019 source_organization_id,
4020 source_subinventory,
4021 expense_account,
4022 encumbrance_account,
4023 restrict_subinventories_code,
4024 unit_weight,
4025 weight_uom_code,
4026 volume_uom_code,
4027 unit_volume,
4028 restrict_locators_code,
4029 location_control_code,
4030 shrinkage_rate,
4031 acceptable_early_days,
4032 planning_time_fence_code,
4033 demand_time_fence_code,
4034 lead_time_lot_size,
4035 std_lot_size,
4036 cum_manufacturing_lead_time,
4037 overrun_percentage,
4038 acceptable_rate_increase,
4039 acceptable_rate_decrease,
4040 cumulative_total_lead_time,
4041 planning_time_fence_days,
4042 demand_time_fence_days,
4043 end_assembly_pegging_flag,
4044 planning_exception_set,
4045 bom_item_type,
4046 pick_components_flag,
4047 replenish_to_order_flag,
4048 base_item_id,
4049 atp_components_flag,
4050 atp_flag,
4051 fixed_lead_time,
4052 variable_lead_time,
4053 wip_supply_locator_id,
4054 wip_supply_type,
4055 wip_supply_subinventory,
4056 primary_uom_code,
4057 primary_unit_of_measure,
4058 allowed_units_lookup_code,
4059 cost_of_sales_account,
4060 sales_account,
4061 default_include_in_rollup_flag,
4062 inventory_item_status_code,
4063 inventory_planning_code,
4064 planner_code,
4065 planning_make_buy_code,
4066 fixed_lot_multiplier,
4067 rounding_control_type,
4068 carrying_cost,
4069 postprocessing_lead_time,
4070 preprocessing_lead_time,
4071 full_lead_time,
4072 order_cost,
4073 mrp_safety_stock_percent,
4074 mrp_safety_stock_code,
4075 min_minmax_quantity,
4076 max_minmax_quantity,
4077 minimum_order_quantity,
4078 fixed_order_quantity,
4079 fixed_days_supply,
4080 maximum_order_quantity,
4081 atp_rule_id,
4082 picking_rule_id,
4083 reservable_type,
4084 positive_measurement_error,
4085 negative_measurement_error,
4086 engineering_ecn_code,
4087 engineering_item_id,
4088 engineering_date,
4089 service_starting_delay,
4090 vendor_warranty_flag,
4091 serviceable_component_flag,
4092 serviceable_product_flag,
4093 base_warranty_service_id,
4094 payment_terms_id,
4095 preventive_maintenance_flag,
4096 primary_specialist_id,
4097 secondary_specialist_id,
4098 serviceable_item_class_id,
4099 time_billable_flag,
4100 material_billable_flag,
4101 expense_billable_flag,
4102 prorate_service_flag,
4103 coverage_schedule_id,
4104 service_duration_period_code,
4105 service_duration,
4106 max_warranty_amount,
4107 response_time_period_code,
4108 response_time_value,
4109 new_revision_code,
4110 tax_code,
4111 must_use_approved_vendor_flag,
4112 safety_stock_bucket_days,
4113 auto_reduce_mps,
4114 costing_enabled_flag,
4115 invoiceable_item_flag,
4116 invoice_enabled_flag,
4117 outside_operation_flag,
4118 outside_operation_uom_type,
4119 auto_created_config_flag,
4120 cycle_count_enabled_flag,
4121 item_type,
4122 model_config_clause_name,
4123 ship_model_complete_flag,
4124 mrp_planning_code,
4125 repetitive_planning_flag,
4126 return_inspection_requirement,
4127 effectivity_control,
4128 request_id,
4129 program_application_id,
4130 program_id,
4131 program_update_date,
4132 comms_nl_trackable_flag,
4133 default_so_source_type,
4134 create_supply_flag,
4135 lot_status_enabled,
4136 default_lot_status_id,
4137 serial_status_enabled,
4138 default_serial_status_id,
4139 lot_split_enabled,
4140 lot_merge_enabled,
4141 bulk_picked_flag,
4142 FINANCING_ALLOWED_FLAG,
4143 EAM_ITEM_TYPE ,
4144 EAM_ACTIVITY_TYPE_CODE,
4145 EAM_ACTIVITY_CAUSE_CODE,
4146 EAM_ACT_NOTIFICATION_FLAG,
4147 EAM_ACT_SHUTDOWN_STATUS,
4148 SUBSTITUTION_WINDOW_CODE,
4149 SUBSTITUTION_WINDOW_DAYS,
4150 PRODUCT_FAMILY_ITEM_ID,
4151 CHECK_SHORTAGES_FLAG,
4152 PLANNED_INV_POINT_FLAG,
4153 OVER_SHIPMENT_TOLERANCE,
4154 UNDER_SHIPMENT_TOLERANCE,
4155 OVER_RETURN_TOLERANCE,
4156 UNDER_RETURN_TOLERANCE,
4157 PURCHASING_TAX_CODE,
4158 OVERCOMPLETION_TOLERANCE_TYPE,
4159 OVERCOMPLETION_TOLERANCE_VALUE,
4160 INVENTORY_CARRY_PENALTY,
4161 OPERATION_SLACK_PENALTY,
4162 UNIT_LENGTH,
4163 UNIT_WIDTH,
4164 UNIT_HEIGHT,
4165 LOT_TRANSLATE_ENABLED,
4166 CONTAINER_ITEM_FLAG,
4167 VEHICLE_ITEM_FLAG,
4168 DIMENSION_UOM_CODE,
4169 SECONDARY_UOM_CODE,
4170 MAXIMUM_LOAD_WEIGHT,
4171 MINIMUM_FILL_PERCENT,
4172 CONTAINER_TYPE_CODE,
4173 INTERNAL_VOLUME,
4174 EQUIPMENT_TYPE,
4175 INDIVISIBLE_FLAG,
4176 GLOBAL_ATTRIBUTE_CATEGORY,
4177 GLOBAL_ATTRIBUTE1,
4178 GLOBAL_ATTRIBUTE2,
4179 GLOBAL_ATTRIBUTE3,
4180 GLOBAL_ATTRIBUTE4,
4181 GLOBAL_ATTRIBUTE5,
4182 GLOBAL_ATTRIBUTE6,
4183 GLOBAL_ATTRIBUTE7,
4184 GLOBAL_ATTRIBUTE8,
4185 GLOBAL_ATTRIBUTE9,
4186 GLOBAL_ATTRIBUTE10,
4187 DUAL_UOM_CONTROL,
4188 DUAL_UOM_DEVIATION_HIGH,
4189 DUAL_UOM_DEVIATION_LOW,
4190 CONTRACT_ITEM_TYPE_CODE,
4191 SUBSCRIPTION_DEPEND_FLAG,
4192 SERV_REQ_ENABLED_CODE,
4193 SERV_BILLING_ENABLED_FLAG,
4194 RELEASE_TIME_FENCE_CODE,
4195 RELEASE_TIME_FENCE_DAYS,
4196 DEFECT_TRACKING_ON_FLAG,
4197 SERV_IMPORTANCE_LEVEL,
4198 WEB_STATUS,
4199 tracking_quantity_ind,
4200 ont_pricing_qty_source,
4201 approval_status,
4202 vmi_minimum_units,
4203 vmi_minimum_days,
4204 vmi_maximum_units,
4205 vmi_maximum_days,
4206 vmi_fixed_order_quantity,
4207 so_authorization_flag,
4208 consigned_flag,
4209 asn_autoexpire_flag,
4210 vmi_forecast_type,
4211 forecast_horizon,
4212 days_tgt_inv_supply,
4213 days_tgt_inv_window,
4214 days_max_inv_supply,
4215 days_max_inv_window,
4216 critical_component_flag,
4217 drp_planned_flag,
4218 exclude_from_budget_flag,
4219 convergence,
4220 continous_transfer,
4221 divergence,
4222 --r12,4574899
4223 lot_divisible_flag,
4224 grade_control_flag,
4225 child_lot_flag,
4226 child_lot_validation_flag,
4227 copy_lot_attribute_flag,
4228 parent_child_generation_flag, --Bugfix 8821149
4229 lot_substitution_enabled, --Bugfix 8821149
4230 recipe_enabled_flag,
4231 process_quality_enabled_flag,
4232 process_execution_enabled_flag,
4233 process_costing_enabled_flag,
4234 hazardous_material_flag,
4235 preposition_point,
4236 repair_program,
4237 outsourced_assembly
4238
4239
4240 )
4241 select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
4242 distinct
4243 bcolu.config_item_id,
4244 m.organization_id,
4245 sysdate,
4246 gUserId, -- last_updated_by
4247 sysdate,
4248 gUserId, -- created_by
4249 gLoginId , -- last_update_login
4250 decode( get_attribute_control( 'summary_flag') , 1 , config.summary_flag, m.summary_flag),
4251 decode( get_attribute_control( 'enabled_flag' ) , 1 , config.enabled_flag , m.enabled_flag),
4252 decode( get_attribute_control( 'start_date_active'), 1 , config.start_date_active, m.start_date_active) ,
4253 decode( get_attribute_control( 'end_date_active'), 1 , config.end_date_active, m.end_date_active) ,
4254 decode( get_attribute_control( 'description' ) , 1 , config.description, m.description) ,
4255 decode( get_attribute_control( 'buyer_id') , 1 , config.buyer_id, m.buyer_id) ,
4256 decode( get_attribute_control( 'accounting_rule_id' ) , 1 , config.accounting_rule_id, m.accounting_rule_id) ,
4257 decode( get_attribute_control( 'invoicing_rule_id' ) , 1 , config.invoicing_rule_id, m.invoicing_rule_id) ,
4258 config.segment1,
4259 config.segment2,
4260 config.segment3,
4261 config.segment4,
4262 config.segment5,
4263 config.segment6,
4264 config.segment7,
4265 config.segment8,
4266 config.segment9,
4267 config.segment10,
4268 config.segment11,
4269 config.segment12,
4270 config.segment13,
4271 config.segment14,
4272 config.segment15,
4273 config.segment16,
4274 config.segment17,
4275 config.segment18,
4276 config.segment19,
4277 config.segment20,
4278 decode(get_attribute_control( 'attribute_category'), 1 , config.attribute_category, m.attribute_category),
4279 m.attribute1,
4280 m.attribute2,
4281 m.attribute3,
4282 m.attribute4,
4283 m.attribute5,
4284 m.attribute6,
4285 m.attribute7,
4286 m.attribute8,
4287 m.attribute9,
4288 m.attribute10,
4289 m.attribute11,
4290 m.attribute12,
4291 m.attribute13,
4292 m.attribute14,
4293 m.attribute15,
4294 m.attribute16, -- Bug 9223457
4295 m.attribute17,
4296 m.attribute18,
4297 m.attribute19,
4298 m.attribute20,
4299 m.attribute21,
4300 m.attribute22,
4301 m.attribute23,
4302 m.attribute24,
4303 m.attribute25,
4304 m.attribute26,
4305 m.attribute27,
4306 m.attribute28,
4307 m.attribute29,
4308 m.attribute30,
4309 'Y',
4310 'Y',
4311 'Y',
4312 'Y',
4313 decode( get_attribute_control( 'service_item_flag' ), 1, config.service_item_flag , m.service_item_flag) ,
4314 'Y',
4315 decode( get_attribute_control( 'eng_item_flag' ) , 1 , config.eng_item_flag , m.eng_item_flag) ,
4316 decode( get_attribute_control( 'inventory_asset_flag' ) , 1 , config.inventory_asset_flag , m.inventory_asset_flag) ,
4317 'Y',
4318 'Y',
4319 'Y',
4320 'Y',
4321 'Y',
4322 'Y',
4323 'Y',
4324 'Y',
4325 decode( get_attribute_control( 'revision_qty_control_code' ) , 1 , config.revision_qty_control_code , m.revision_qty_control_code) ,
4326 decode( get_attribute_control( 'item_catalog_group_id' ) , 1 , config.item_catalog_group_id, m.item_catalog_group_id) ,
4327 decode( get_attribute_control( 'catalog_status_flag' ) , 1 , config.catalog_status_flag, m.catalog_status_flag) ,
4328 decode( get_attribute_control( 'returnable_flag' ) , 1 , config.returnable_flag, m.returnable_flag) ,
4329 decode( get_attribute_control( 'default_shipping_org' ) , 1, config.default_shipping_org, m.default_shipping_org),
4330 decode( get_attribute_control( 'collateral_flag') , 1 , config.collateral_flag , m.collateral_flag) ,
4331 decode( get_attribute_control( 'taxable_flag' ) , 1 , config.taxable_flag, m.taxable_flag) ,
4332 decode( get_attribute_control( 'allow_item_desc_update_flag' ) , 1, config.allow_item_desc_update_flag, m.allow_item_desc_update_flag),
4333 decode( get_attribute_control( 'inspection_required_flag' ), 1 , config.inspection_required_flag , m.inspection_required_flag),
4334 decode( get_attribute_control( 'receipt_required_flag' ), 1, config.receipt_required_flag, m.receipt_required_flag) ,
4335 decode( get_attribute_control( 'market_price' ) , 1 , config.market_price, m.market_price) ,
4336 decode( get_attribute_control( 'hazard_class_id' ), 1 , config.hazard_class_id, m.hazard_class_id),
4337 decode( get_attribute_control( 'rfq_required_flag'), 1 , config.rfq_required_flag, m.rfq_required_flag),
4338 decode( get_attribute_control( 'qty_rcv_tolerance'), 1, config.qty_rcv_tolerance, m.qty_rcv_tolerance),
4339 decode( get_attribute_control( 'un_number_id' ), 1 , config.un_number_id, m.un_number_id),
4340 decode( get_attribute_control( 'price_tolerance_percent'), 1 , config.price_tolerance_percent, m.price_tolerance_percent) ,
4341 decode( get_attribute_control( 'asset_category_id') , 1 , config.asset_category_id, m.asset_category_id) ,
4342 decode( get_attribute_control( 'rounding_factor' ) , 1 , config.rounding_factor, m.rounding_factor) ,
4343 decode( get_attribute_control( 'unit_of_issue') , 1 , config.unit_of_issue, m.unit_of_issue) ,
4344 decode( get_attribute_control( 'enforce_ship_to_location_code' ) , 1 , config.enforce_ship_to_location_code , m.enforce_ship_to_location_code),
4345 decode( get_attribute_control( 'allow_substitute_receipts_flag' ) , 1 , config.allow_substitute_receipts_flag, m.allow_substitute_receipts_flag) ,
4346 decode( get_attribute_control( 'allow_unordered_receipts_flag' ) , 1 , config.allow_unordered_receipts_flag, m.allow_unordered_receipts_flag) ,
4347 decode( get_attribute_control( 'allow_express_delivery_flag' ) ,1 , config.allow_express_delivery_flag, m.allow_express_delivery_flag) ,
4348 decode( get_attribute_control( 'days_early_receipt_allowed') , 1, config.days_early_receipt_allowed, m.days_early_receipt_allowed) ,
4349 decode( get_attribute_control( 'days_late_receipt_allowed' ) , 1 , config.days_late_receipt_allowed , m.days_late_receipt_allowed) ,
4350 decode( get_attribute_control( 'receipt_days_exception_code') , 1 , config.receipt_days_exception_code, m.receipt_days_exception_code) ,
4351 decode( get_attribute_control( 'receiving_routing_id' ) , 1 , config.receiving_routing_id, m.receiving_routing_id),
4352 decode( get_attribute_control( 'invoice_close_tolerance'), 1, config.invoice_close_tolerance, m.invoice_close_tolerance) ,
4353 decode( get_attribute_control( 'receive_close_tolerance') , 1 , config.receive_close_tolerance , m.receive_close_tolerance) ,
4354 decode( get_attribute_control( 'auto_lot_alpha_prefix') , 1, config.auto_lot_alpha_prefix, m.auto_lot_alpha_prefix) ,
4355 decode( get_attribute_control( 'start_auto_lot_number') , 1, config.start_auto_lot_number, m.start_auto_lot_number) ,
4356 decode( get_attribute_control( 'lot_control_code') ,1 , config.lot_control_code, m.lot_control_code) ,
4357 decode( get_attribute_control( 'shelf_life_code'), 1 , config.shelf_life_code, m.shelf_life_code) ,
4358 decode( get_attribute_control( 'shelf_life_days') , 1, config.shelf_life_days, m.shelf_life_days) ,
4359 decode( get_attribute_control( 'serial_number_control_code' ) ,1, config.serial_number_control_code, m.serial_number_control_code) ,
4360 decode( get_attribute_control( 'start_auto_serial_number' ) , 1 , config.start_auto_serial_number, m.start_auto_serial_number) ,
4361 decode( get_attribute_control( 'auto_serial_alpha_prefix') ,1 , config.auto_serial_alpha_prefix, m.auto_serial_alpha_prefix) ,
4362 decode( get_attribute_control( 'source_type' ) ,1 , config.source_type, m.source_type) ,
4363 decode( get_attribute_control( 'source_organization_id') , 1 , config.source_organization_id, m.source_organization_id) ,
4364 decode( get_attribute_control( 'source_subinventory') ,1 , config.source_subinventory, m.source_subinventory) ,
4365 decode( get_attribute_control( 'expense_account') , 1, config.expense_account, m.expense_account) ,
4366 decode( get_attribute_control( 'encumbrance_account') , 1 , config.encumbrance_account, m.encumbrance_account) ,
4367 decode( get_attribute_control( 'restrict_subinventories_code' ) , 1 , config.restrict_subinventories_code, m.restrict_subinventories_code) ,
4368 config.unit_weight,
4369 config.weight_uom_code,
4370 config.volume_uom_code,
4371 config.unit_volume,
4372 decode( get_attribute_control( 'restrict_locators_code'), 1, config.restrict_locators_code, m.restrict_locators_code) ,
4373 decode( get_attribute_control( 'location_control_code') , 1 , config.location_control_code, m.location_control_code) ,
4374 decode( get_attribute_control( 'shrinkage_rate' ) , 1, config.shrinkage_rate, m.shrinkage_rate) ,
4375 decode( get_attribute_control( 'acceptable_early_days') , 1 , config.acceptable_early_days, m.acceptable_early_days) ,
4376 decode( get_attribute_control( 'planning_time_fence_code' ) , 1 , config.planning_time_fence_code, m.planning_time_fence_code) ,
4377 decode( get_attribute_control( 'demand_time_fence_code') , 1 , config.demand_time_fence_code, m.demand_time_fence_code) ,
4378 decode( get_attribute_control( 'lead_time_lot_size') ,1, config.lead_time_lot_size, m.lead_time_lot_size) ,
4379 decode( get_attribute_control( 'std_lot_size' ) , 1, config.std_lot_size, m.std_lot_size) ,
4380 decode( get_attribute_control( 'cum_manufacturing_lead_time' ) , 1 , config.cum_manufacturing_lead_time, m.cum_manufacturing_lead_time) ,
4381 decode( get_attribute_control( 'overrun_percentage') , 1, config.overrun_percentage, m.overrun_percentage) ,
4382 decode( get_attribute_control( 'acceptable_rate_increase'), 1, config.acceptable_rate_increase, m.acceptable_rate_increase) ,
4383 decode( get_attribute_control( 'acceptable_rate_decrease') , 1 , config.acceptable_rate_decrease, m.acceptable_rate_decrease) ,
4384 decode( get_attribute_control( 'cumulative_total_lead_time' ) , 1 , config.cumulative_total_lead_time, m.cumulative_total_lead_time) ,
4385 decode( get_attribute_control( 'planning_time_fence_days' ) , 1, config.planning_time_fence_days, m.planning_time_fence_days) ,
4386 decode( get_attribute_control( 'demand_time_fence_days') , 1, config.demand_time_fence_days, m.demand_time_fence_days) ,
4387 decode( get_attribute_control( 'end_assembly_pegging_flag') ,1 , config.end_assembly_pegging_flag , m.end_assembly_pegging_flag) ,
4388 decode( get_attribute_control( 'planning_exception_set' ) , 1 , config.planning_exception_set, m.planning_exception_set) ,
4389 4, -- BOM_ITEM_TYPE : standard
4390 'N',
4391 'Y',
4392 bcolu.inventory_item_id, -- Base Model ID
4393 decode( get_attribute_control( 'atp_components_flag') , 1, config.atp_components_flag, CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(m.atp_flag, m.atp_components_flag)),
4394 decode( get_attribute_control( 'atp_flag') , 1, config.atp_flag, cto_config_item_pk.get_atp_flag) , -- ATP flag, set by evaluate_atp_attributes
4395 decode( get_attribute_control( 'fixed_lead_time') ,1 , config.fixed_lead_time, m.fixed_lead_time) ,
4396 decode( get_attribute_control( 'variable_lead_time') , 1 , config.variable_lead_time, m.variable_lead_time) ,
4397 decode( get_attribute_control( 'wip_supply_locator_id' ) , 1, config.wip_supply_locator_id, m.wip_supply_locator_id) ,
4398 decode( get_attribute_control( 'wip_supply_type' ) , 1 , config.wip_supply_type , m.wip_supply_type) ,
4399 decode( get_attribute_control( 'wip_supply_subinventory' ) , 1 , config.wip_supply_subinventory, m.wip_supply_subinventory) ,
4400 decode( get_attribute_control( 'primary_uom_code' ) , 1 , config.primary_uom_code, m.primary_uom_code) ,
4401 decode( get_attribute_control( 'primary_unit_of_measure' ) , 1 , config.primary_unit_of_measure, m.primary_unit_of_measure) ,
4402 decode( get_attribute_control( 'allowed_units_lookup_code' ) , 1 , config.allowed_units_lookup_code, m.allowed_units_lookup_code) ,
4403 decode( get_attribute_control( 'cost_of_sales_account' ) , 1 , config.cost_of_sales_account, m.cost_of_sales_account) ,
4404 decode( get_attribute_control( 'sales_account' ) , 1, config.sales_account, m.sales_account) ,
4405 'Y', -- DEFAULT_INCLUDE_IN_ROLLUP_FLAG
4406 decode( get_attribute_control( 'inventory_item_status_code' ) , 1 , config.inventory_item_status_code, m.inventory_item_status_code) ,
4407 decode( get_attribute_control( 'inventory_planning_code') , 1, config.inventory_planning_code, m.inventory_planning_code) ,
4408 decode( get_attribute_control( 'planner_code') , 1 , config.planner_code, m.planner_code) ,
4409 decode( get_attribute_control( 'planning_make_buy_code' ) , 1 , config.planning_make_buy_code, m.planning_make_buy_code) ,
4410 decode( get_attribute_control( 'fixed_lot_multiplier' ) , 1 , config.fixed_lot_multiplier, m.fixed_lot_multiplier) ,
4411 decode( get_attribute_control( 'rounding_control_type' ) , 1, config.rounding_control_type, m.rounding_control_type) ,
4412 decode( get_attribute_control( 'carrying_cost' ) ,1 , config.carrying_cost, m.carrying_cost) ,
4413 decode( get_attribute_control( 'postprocessing_lead_time') , 1, config.postprocessing_lead_time, m.postprocessing_lead_time) ,
4414 decode( get_attribute_control( 'preprocessing_lead_time' ) , 1 , config.preprocessing_lead_time, m.preprocessing_lead_time) ,
4415 decode( get_attribute_control( 'full_lead_time') , 1, config.full_lead_time, m.full_lead_time) ,
4416 decode( get_attribute_control( 'order_cost') , 1, config.order_cost, m.order_cost) ,
4417 decode( get_attribute_control( 'mrp_safety_stock_percent') , 1, config.mrp_safety_stock_percent, m.mrp_safety_stock_percent) ,
4418 decode( get_attribute_control( 'mrp_safety_stock_code' ) , 1, config.mrp_safety_stock_code, m.mrp_safety_stock_code) ,
4419 decode( get_attribute_control( 'min_minmax_quantity' ) , 1, config.min_minmax_quantity, m.min_minmax_quantity) ,
4420 decode( get_attribute_control( 'max_minmax_quantity' ) , 1 , config.max_minmax_quantity, m.max_minmax_quantity) ,
4421 decode( get_attribute_control( 'minimum_order_quantity' ) , 1 , config.minimum_order_quantity , m.minimum_order_quantity) ,
4422 decode( get_attribute_control( 'fixed_order_quantity' ) , 1 , config.fixed_order_quantity, m.fixed_order_quantity) ,
4423 decode( get_attribute_control( 'fixed_days_supply' ) , 1 , config.fixed_days_supply, m.fixed_days_supply) ,
4424 decode( get_attribute_control( 'maximum_order_quantity' ) , 1, config.maximum_order_quantity, m.maximum_order_quantity) ,
4425 decode( get_attribute_control( 'atp_rule_id' ) , 1, config.atp_rule_id, m.atp_rule_id) ,
4426 decode( get_attribute_control( 'picking_rule_id' ) , 1, config.picking_rule_id, m.picking_rule_id) ,
4427 1, -- m.reservable_type
4428 decode( get_attribute_control( 'positive_measurement_error' ) , 1, config.positive_measurement_error, m.positive_measurement_error) ,
4429 decode( get_attribute_control( 'negative_measurement_error' ) , 1, config.negative_measurement_error, m.negative_measurement_error) ,
4430 decode( get_attribute_control( 'engineering_ecn_code' ) , 1 , config.engineering_ecn_code, m.engineering_ecn_code) ,
4431 decode( get_attribute_control( 'engineering_item_id' ) , 1 , config.engineering_item_id, m.engineering_item_id) ,
4432 decode( get_attribute_control( 'engineering_date' ) , 1, config.engineering_date, m.engineering_date) ,
4433 decode( get_attribute_control( 'service_starting_delay') , 1 , config.service_starting_delay, m.service_starting_delay) ,
4434 decode( get_attribute_control( 'vendor_warranty_flag') , 1 , config.vendor_warranty_flag, m.vendor_warranty_flag) ,
4435 decode( get_attribute_control( 'serviceable_component_flag' ) , 1, config.serviceable_component_flag , m.serviceable_component_flag) ,
4436 decode( get_attribute_control( 'serviceable_product_flag' ) , 1, config.serviceable_product_flag , m.serviceable_product_flag) ,
4437 decode( get_attribute_control( 'base_warranty_service_id' ) ,1 , config.base_warranty_service_id, m.base_warranty_service_id) ,
4438 decode( get_attribute_control( 'payment_terms_id' ) , 1 , config.payment_terms_id, m.payment_terms_id) ,
4439 decode( get_attribute_control( 'preventive_maintenance_flag') , 1, config.preventive_maintenance_flag, m.preventive_maintenance_flag) ,
4440 decode( get_attribute_control( 'primary_specialist_id') , 1 , config.primary_specialist_id, m.primary_specialist_id),
4441 decode( get_attribute_control( 'secondary_specialist_id') , 1 , config.secondary_specialist_id, m.secondary_specialist_id) ,
4442 decode( get_attribute_control( 'serviceable_item_class_id') , 1, config.serviceable_item_class_id, m.serviceable_item_class_id) ,
4443 decode( get_attribute_control( 'time_billable_flag' ) , 1 , config.time_billable_flag, m.time_billable_flag) ,
4444 decode( get_attribute_control( 'material_billable_flag' ) , 1, config.material_billable_flag, m.material_billable_flag) ,
4445 decode( get_attribute_control( 'expense_billable_flag' ) , 1 , config.expense_billable_flag , m.expense_billable_flag) ,
4446 decode( get_attribute_control( 'prorate_service_flag' ) , 1, config.prorate_service_flag, m.prorate_service_flag) ,
4447 decode( get_attribute_control( 'coverage_schedule_id' ) , 1, config.coverage_schedule_id, m.coverage_schedule_id) ,
4448 decode( get_attribute_control( 'service_duration_period_code' ) , 1, config.service_duration_period_code, m.service_duration_period_code) ,
4449 decode( get_attribute_control( 'service_duration') , 1, config.service_duration, m.service_duration) ,
4450 decode( get_attribute_control( 'max_warranty_amount' ) , 1 , config.max_warranty_amount, m.max_warranty_amount) ,
4451 decode( get_attribute_control( 'response_time_period_code' ) , 1, config.response_time_period_code, m.response_time_period_code) ,
4452 decode( get_attribute_control( 'response_time_value') , 1, config.response_time_value, m.response_time_value) ,
4453 decode( get_attribute_control( 'new_revision_code' ) , 1 , config.new_revision_code, m.new_revision_code) ,
4454 decode( get_attribute_control( 'tax_code') , 1, config.tax_code, m.tax_code) ,
4455 decode( get_attribute_control( 'must_use_approved_vendor_flag' ) , 1, config.must_use_approved_vendor_flag, m.must_use_approved_vendor_flag) ,
4456 decode( get_attribute_control( 'safety_stock_bucket_days' ) , 1, config.safety_stock_bucket_days, m.safety_stock_bucket_days) ,
4457 decode( get_attribute_control( 'auto_reduce_mps') , 1, config.auto_reduce_mps, m.auto_reduce_mps) ,
4458 decode( get_attribute_control( 'costing_enabled_flag' ) , 1, config.costing_enabled_flag, m.costing_enabled_flag) ,
4459 decode( get_attribute_control( 'invoiceable_item_flag' ) , 1, config.invoiceable_item_flag, m.invoiceable_item_flag) , -- 'N' changed for international dropship
4460 decode( get_attribute_control( 'invoice_enabled_flag' ) , 1, config.invoice_enabled_flag, m.invoice_enabled_flag) , -- 'N' changed for international dropship
4461 decode( get_attribute_control( 'outside_operation_flag') , 1, config.outside_operation_flag, m.outside_operation_flag) ,
4462 decode( get_attribute_control( 'outside_operation_uom_type' ) , 1, config.outside_operation_uom_type, m.outside_operation_uom_type) ,
4463 'Y',
4464 decode( get_attribute_control( 'cycle_count_enabled_flag') , 1 , config.cycle_count_enabled_flag, m.cycle_count_enabled_flag) ,
4465 lItemType,
4466 decode( get_attribute_control( 'model_config_clause_name') ,1 , config.model_config_clause_name, m.model_config_clause_name) ,
4467 decode( get_attribute_control( 'ship_model_complete_flag') ,1 , config.ship_model_complete_flag, m.ship_model_complete_flag) ,
4468 decode( get_attribute_control( 'mrp_planning_code' ) , 1 , config.mrp_planning_code, m.mrp_planning_code) , -- earlier it was always from one org only
4469 decode( get_attribute_control( 'repetitive_planning_flag' ) , 1, config.repetitive_planning_flag, m.repetitive_planning_flag) , -- earlier it was always from one org only
4470 decode( get_attribute_control( 'return_inspection_requirement' ) , 1 , config.return_inspection_requirement, m.return_inspection_requirement) ,
4471 nvl( decode( get_attribute_control( 'effectivity_control') , 1, config.effectivity_control, m.effectivity_control) , 1),
4472 null, -- req_id
4473 null, -- prg_appid
4474 99, -- prg_id (to identify that item was created in this org by this program
4475 sysdate,
4476 decode( get_attribute_control( 'comms_nl_trackable_flag') , 1, config.comms_nl_trackable_flag, m.comms_nl_trackable_flag) ,
4477 nvl( decode( get_attribute_control( 'default_so_source_type') , 1 , config.default_so_source_type, m.default_so_source_type) ,'INTERNAL'),
4478 nvl( decode( get_attribute_control( 'create_supply_flag') , 1, config.create_supply_flag, m.create_supply_flag) , 'Y'),
4479 decode( get_attribute_control( 'lot_status_enabled') , 1, config.lot_status_enabled, m.lot_status_enabled) ,
4480 decode( get_attribute_control( 'default_lot_status_id' ) , 1, config.default_lot_status_id, m.default_lot_status_id) ,
4481 decode( get_attribute_control( 'serial_status_enabled') , 1, config.serial_status_enabled, m.serial_status_enabled) ,
4482 decode( get_attribute_control( 'default_serial_status_id') ,1 , config.default_serial_status_id, m.default_serial_status_id) ,
4483 decode( get_attribute_control( 'lot_split_enabled') , 1, config.lot_split_enabled, m.lot_split_enabled) ,
4484 decode( get_attribute_control( 'lot_merge_enabled') ,1 , config.lot_merge_enabled, m.lot_merge_enabled) ,
4485 decode( get_attribute_control( 'bulk_picked_flag' ) , 1 , config.bulk_picked_flag, m.bulk_picked_flag) ,
4486 decode( get_attribute_control( 'financing_allowed_flag') , 1, config.financing_allowed_flag, m.FINANCING_ALLOWED_FLAG) ,
4487 decode( get_attribute_control( 'eam_item_type') , 1 , config.eam_item_type, m.EAM_ITEM_TYPE ) ,
4488 decode( get_attribute_control( 'eam_activity_type_code') , 1 , config.eam_activity_type_code, m.EAM_ACTIVITY_TYPE_CODE) ,
4489 decode( get_attribute_control( 'eam_activity_cause_code') , 1, config.eam_activity_cause_code, m.EAM_ACTIVITY_CAUSE_CODE) ,
4490 decode( get_attribute_control( 'eam_act_notification_flag') , 1, config.eam_act_notification_flag, m.EAM_ACT_NOTIFICATION_FLAG) ,
4491 decode( get_attribute_control( 'eam_act_shutdown_status') , 1, config.eam_act_shutdown_status, m.EAM_ACT_SHUTDOWN_STATUS) ,
4492 decode( get_attribute_control( 'substitution_window_code') , 1, config.substitution_window_code, m.SUBSTITUTION_WINDOW_CODE) ,
4493 decode( get_attribute_control( 'substitution_window_days') , 1, config.substitution_window_days, m.SUBSTITUTION_WINDOW_DAYS) ,
4494 null, --5385901 decode( get_attribute_control( 'product_family_item_id') , 1, config.product_family_item_id, m.PRODUCT_FAMILY_ITEM_ID) ,
4495 decode( get_attribute_control( 'check_shortages_flag') , 1, config.check_shortages_flag, m.CHECK_SHORTAGES_FLAG) ,
4496 decode( get_attribute_control( 'planned_inv_point_flag') , 1, config.planned_inv_point_flag, m.PLANNED_INV_POINT_FLAG) ,
4497 decode( get_attribute_control( 'over_shipment_tolerance') , 1, config.over_shipment_tolerance, m.OVER_SHIPMENT_TOLERANCE) ,
4498 decode( get_attribute_control( 'under_shipment_tolerance') , 1, config.under_shipment_tolerance, m.UNDER_SHIPMENT_TOLERANCE) ,
4499 decode( get_attribute_control( 'over_return_tolerance') , 1, config.over_return_tolerance, m.OVER_RETURN_TOLERANCE) ,
4500 decode( get_attribute_control( 'under_return_tolerance') , 1, config.under_return_tolerance, m.UNDER_RETURN_TOLERANCE) ,
4501 decode( get_attribute_control( 'purchasing_tax_code') , 1, config.purchasing_tax_code, m.PURCHASING_TAX_CODE) ,
4502 decode( get_attribute_control( 'overcompletion_tolerance_type') , 1, config.overcompletion_tolerance_type, m.OVERCOMPLETION_TOLERANCE_TYPE) ,
4503 decode( get_attribute_control( 'overcompletion_tolerance_value') , 1, config.overcompletion_tolerance_value, m.OVERCOMPLETION_TOLERANCE_VALUE) ,
4504 decode( get_attribute_control( 'inventory_carry_penalty'), 1, config.inventory_carry_penalty, m.INVENTORY_CARRY_PENALTY) ,
4505 decode( get_attribute_control( 'operation_slack_penalty') ,1, config.operation_slack_penalty, m.OPERATION_SLACK_PENALTY) ,
4506 decode( get_attribute_control( 'unit_length') , 1, config.unit_length, m.UNIT_LENGTH) ,
4507 decode( get_attribute_control( 'unit_width' ) , 1, config.unit_width, m.UNIT_WIDTH) ,
4508 decode( get_attribute_control( 'unit_height') , 1, config.unit_height, m.UNIT_HEIGHT) ,
4509 decode( get_attribute_control( 'lot_translate_enabled') , 1, config.lot_translate_enabled, m.LOT_TRANSLATE_ENABLED) ,
4510 decode( get_attribute_control( 'container_item_flag') , 1, config.container_item_flag, m.CONTAINER_ITEM_FLAG) ,
4511 decode( get_attribute_control( 'vehicle_item_flag') , 1, config.vehicle_item_flag, m.VEHICLE_ITEM_FLAG) ,
4512 decode( get_attribute_control( 'dimension_uom_code') , 1, config.dimension_uom_code, m.DIMENSION_UOM_CODE) ,
4513 decode( get_attribute_control( 'secondary_uom_code') , 1, config.secondary_uom_code, m.SECONDARY_UOM_CODE) ,
4514 decode( get_attribute_control( 'maximum_load_weight') , 1, config.maximum_load_weight, m.MAXIMUM_LOAD_WEIGHT) ,
4515 decode( get_attribute_control( 'minimum_fill_percent') , 1, config.minimum_fill_percent, m.MINIMUM_FILL_PERCENT) ,
4516 decode( get_attribute_control( 'container_type_code') , 1, config.container_type_code, m.CONTAINER_TYPE_CODE) ,
4517 decode( get_attribute_control( 'internal_volume') , 1, config.internal_volume, m.INTERNAL_VOLUME) ,
4518 decode( get_attribute_control( 'equipment_type') , 1, config.equipment_type , m.EQUIPMENT_TYPE) ,
4519 decode( get_attribute_control( 'indivisible_flag') , 1, config.indivisible_flag, m.INDIVISIBLE_FLAG) ,
4520 decode( get_attribute_control( 'global_attribute_category'), 1, config.global_attribute_category, m.GLOBAL_ATTRIBUTE_CATEGORY) ,
4521 m.GLOBAL_ATTRIBUTE1,
4522 m.GLOBAL_ATTRIBUTE2,
4523 m.GLOBAL_ATTRIBUTE3,
4524 m.GLOBAL_ATTRIBUTE4,
4525 m.GLOBAL_ATTRIBUTE5,
4526 m.GLOBAL_ATTRIBUTE6,
4527 m.GLOBAL_ATTRIBUTE7,
4528 m.GLOBAL_ATTRIBUTE8,
4529 m.GLOBAL_ATTRIBUTE9,
4530 m.GLOBAL_ATTRIBUTE10,
4531 decode( get_attribute_control( 'dual_uom_control') , 1, config.dual_uom_control, m.DUAL_UOM_CONTROL) ,
4532 decode( get_attribute_control( 'dual_uom_deviation_high') , 1, config.dual_uom_deviation_high, m.DUAL_UOM_DEVIATION_HIGH) ,
4533 decode( get_attribute_control( 'dual_uom_deviation_low') , 1, config.dual_uom_deviation_low, m.DUAL_UOM_DEVIATION_LOW) ,
4534 decode( get_attribute_control( 'contract_item_type_code') , 1, config.contract_item_type_code, m.CONTRACT_ITEM_TYPE_CODE) ,
4535 decode( get_attribute_control( 'subscription_depend_flag') , 1 , config.subscription_depend_flag, m.SUBSCRIPTION_DEPEND_FLAG) ,
4536 decode( get_attribute_control( 'serv_req_enabled_code' ) , 1, config.serv_req_enabled_code, m.SERV_REQ_ENABLED_CODE) ,
4537 decode( get_attribute_control( 'serv_billing_enabled_flag') , 1, config.serv_billing_enabled_flag, m.SERV_BILLING_ENABLED_FLAG) ,
4538 decode( get_attribute_control( 'release_time_fence_code') , 1, config.release_time_fence_code, m.RELEASE_TIME_FENCE_CODE) ,
4539 decode( get_attribute_control( 'release_time_fence_days' ) ,1, config.release_time_fence_days, m.RELEASE_TIME_FENCE_DAYS) ,
4540 decode( get_attribute_control( 'defect_tracking_on_flag') , 1, config.defect_tracking_on_flag, m.DEFECT_TRACKING_ON_FLAG) ,
4541 decode( get_attribute_control( 'serv_importance_level'), 1, config.serv_importance_level, m.SERV_IMPORTANCE_LEVEL) ,
4542 decode( get_attribute_control( 'web_status') , 1, config.web_status, m.web_status),
4543 decode( get_attribute_control( 'tracking_quantity_ind') , 1, config.tracking_quantity_ind, m.tracking_quantity_ind),
4544 decode( get_attribute_control( 'ont_pricing_qty_source') , 1, config.ont_pricing_qty_source, m.ont_pricing_qty_source),
4545 decode( get_attribute_control( 'approval_status') , 1, config.approval_status, m.approval_status),
4546 --decode( get_attribute_control( 'default_control') , 1, config.tracking_quantity_ind, m.tracking_quantity_ind),
4547 decode( get_attribute_control( 'vmi_minimum_units') , 1, config.vmi_minimum_units, m.vmi_minimum_units),
4548 decode( get_attribute_control( 'vmi_minimum_days') , 1, config.vmi_minimum_days, m.vmi_minimum_days),
4549 decode( get_attribute_control( 'vmi_maximum_units') , 1, config.vmi_maximum_units, m.vmi_maximum_units),
4550 decode( get_attribute_control( 'vmi_maximum_days') , 1, config.vmi_maximum_days, m.vmi_maximum_days),
4551 decode( get_attribute_control( 'vmi_fixed_order_quantity') , 1, config.vmi_fixed_order_quantity, m.vmi_fixed_order_quantity),
4552 decode( get_attribute_control( 'so_authorization_flag') , 1, config.so_authorization_flag, m.so_authorization_flag),
4553 decode( get_attribute_control( 'consigned_flag') , 1, config.consigned_flag, m.consigned_flag),
4554 decode( get_attribute_control( 'asn_autoexpire_flag') , 1, config.asn_autoexpire_flag, m.asn_autoexpire_flag),
4555 decode( get_attribute_control( 'vmi_forecast_type') , 1, config.vmi_forecast_type, m.vmi_forecast_type),
4556 decode( get_attribute_control( 'forecast_horizon') , 1, config.forecast_horizon, m.forecast_horizon),
4557 decode( get_attribute_control( 'days_tgt_inv_supply') , 1, config.days_tgt_inv_supply, m.days_tgt_inv_supply),
4558 decode( get_attribute_control( 'days_tgt_inv_window') , 1, config.days_tgt_inv_window, m.days_tgt_inv_window),
4559 decode( get_attribute_control( 'days_max_inv_supply') , 1, config.days_max_inv_supply, m.days_max_inv_supply),
4560 decode( get_attribute_control( 'days_max_inv_window') , 1, config.days_max_inv_window, m.days_max_inv_window),
4561 decode( get_attribute_control( 'critical_component_flag') , 1, config.critical_component_flag, m.critical_component_flag),
4562 decode( get_attribute_control( 'drp_planned_flag') , 1, config.drp_planned_flag, m.drp_planned_flag),
4563 decode( get_attribute_control( 'exclude_from_budget_flag') , 1, config.exclude_from_budget_flag, m.exclude_from_budget_flag),
4564 decode( get_attribute_control( 'convergence') , 1, config.convergence, m.convergence),
4565 decode( get_attribute_control( 'continous_transfer') , 1, config.continous_transfer, m.continous_transfer),
4566 decode( get_attribute_control( 'divergence') , 1, config.divergence, m.divergence),
4567 --r12,4574899
4568 'N',
4569 'N',
4570 /* Bugfix 8821149: Will populate these values from model.
4571 'N',
4572 'N',
4573 'N',
4574 */
4575 decode( get_attribute_control( 'child_lot_flag' ) , 1 , config.child_lot_flag, m.child_lot_flag),
4576 decode( get_attribute_control( 'child_lot_validation_flag' ) , 1 , config.child_lot_validation_flag, m.child_lot_validation_flag),
4577 decode( get_attribute_control( 'copy_lot_attribute_flag' ) , 1 , config.copy_lot_attribute_flag, m.copy_lot_attribute_flag),
4578 decode( get_attribute_control( 'parent_child_generation_flag' ) , 1 , config.parent_child_generation_flag, m.parent_child_generation_flag),
4579 decode( get_attribute_control( 'lot_substitution_enabled' ) , 1 , config.lot_substitution_enabled, m.lot_substitution_enabled),
4580 -- End Bugfix 8821149
4581 'N',
4582 'N',
4583 'N',
4584 'N',
4585 'N',
4586 'N',
4587 3,
4588 2
4589
4590 from
4591 mtl_system_items_b m, -- model
4592 mtl_system_items_b config, -- config
4593 bom_cto_src_orgs bcso,
4594 bom_cto_order_lines_upg bcolu
4595 where bcolu.config_item_id is not null
4596 and bcolu.status = 'BCSO'
4597 and m.inventory_item_id = bcolu.inventory_item_id
4598 -- get config item row for any one org
4599 and config.inventory_item_id = bcolu.config_item_id
4600 and config.organization_id = bcolu.ship_from_org_id
4601 -- config is pc in atleast one orgs
4602 and exists
4603 (select 'pc'
4604 from mtl_system_items msi1
4605 where msi1.inventory_item_id = bcolu.config_item_id
4606 and nvl(msi1.auto_created_config_flag,'N') = 'N')
4607 and bcso.model_item_id = bcolu.inventory_item_id
4608 and bcso.line_id = bcolu.line_id
4609 and m.organization_id = bcso.organization_id
4610 and NOT EXISTS
4611 (select NULL
4612 from mtl_system_items_b
4613 where inventory_item_id = bcolu.config_item_id
4614 and organization_id = bcso.organization_id);
4615
4616 WriteToLog('PC Items created::'||sql%rowcount, 2);
4617
4618 EXCEPTION
4619 WHEN OTHERS THEN
4620 WriteToLog ('ERROR: Others error in Update_Pc_Items::'||to_char(lStmtNumber)||sqlerrm,1);
4621 CTO_MSG_PUB.Count_And_Get(
4622 p_msg_count => xMsgCount,
4623 p_msg_data => xMsgData
4624 );
4625 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR;
4626
4627 END Update_Pc_Items;
4628
4629
4630 PROCEDURE WriteToLog (p_message in varchar2 default null,
4631 p_level in number default 0) IS
4632 BEGIN
4633 IF gDebugLevel >= p_level THEN
4634 fnd_file.put_line (fnd_file.log, p_message);
4635 END IF;
4636 END WriteToLog;
4637
4638
4639 END CTO_UPDATE_ITEMS_PK;
4640