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