[Home] [Help]
PACKAGE BODY: APPS.CTO_BOM_RTG_PK
Source
1 package body CTO_BOM_RTG_PK as
2 /* $Header: CTOBMRTB.pls 120.8 2011/02/08 08:01:18 abhissri ship $ */
3
4 /*************************************************************************************
5 *
6 * Modified by : Sushant Sawant
7 * Modified on : 01/23/2001
8 * Desc : In Creating Bom If the model bill is not existing
9 * in the organization where we create the config bom
10 * the procedure needs to error out
11 *
12 * History : 06/18/01 sbhaskar
13 * bugfix 1835357
14 * Comment out all FND_FILE calls since we are using oe_debug_pub.
15 *
16 *
17 *
18 *
19 *
20 * Modified on 24-AUG-2001 by Sushant Sawant: BUG #1957336
21 * Added a new functionality for preconfigure bom.
22 * Modified on 08-MAR-2002 by Sushant Sawant: BUG#2234858
23 * Added a new functionality for Drop Ship
24 *
25 *
26 * Modified on 11-MAR-2002 By Renga Kannan Bug#2255396
27 * Attachment creation for multiple buy model
28 * under a top model was erroring out
29 * I have changed the implimentation of this to
30 * work for multiple buy models
31 *
32 * Modified on 27-MAR-2002 By Kiran Konada
33 * changed the signature in call to GENERATE_BOM_ATTACH_TEXT
34 * above changes have been made as part of patchset-H
35 *
36 * Modified on 09-JAN-2003 by Sushant Sawant:
37 * Added code for enhanced costing issues.
38 *
39 * Modified on 14-FEB-2003 By Kundan Sarkar
40 * Bugfix 2804321 : Propagating customer bugfix 2774570
41 * to main.
42 *
43 * Modified on 18-FEB-2003 by Sushant Sawant:
44 * Fixed bug 2808704.
45
46 * Modified on 20-FEB-2003 Sushant Sawant
47 * Fixed Bug 2810797
48 * Changed logic for overriding cost rollup.
49
50 * Modified on 28-FEB-2003 Sushant Sawant
51 * Fixed Bug 2828634
52 * Check whether item has been transacted in standard costing org.
53 *
54 *
55 * Modified on 02-JUL-2003 By Kundan Sarkar ( Bug 2986192) Customer bug 2929861
56 * Add warning for dropped items during match.
57 * Config item creation will now depend upon the
58 * value of profile BOM:CONFIG_EXCEPTION
59 *
60
61 * Modified on 09-JAN-2004 Sushant Sawant
62 * Fixed Bug 3349142
63 * Added token to error message CTO_NO_BOM_CREATED_IN_ANY_ORGS
64 *
65 | ssawant 15-JAN-04 Bugfix 3374548
66 | Added delete from from bom_inventory_comps_interface to avoid corrupt data.
67 |
68 Modified on 21-APR-2004 By Renga Kannan ( Bug 3543547)
69 * Autocreate config is dropping
70 * components from bill
71 * *
72 * since dropped component logic is based
73 * on bill
74 * *
75 * sequence id instead of common bill
76 * sequence id .
77 *
78 * Modified on 19-NOV-2004 Sushant Sawant
79 * Fixed Bug 3877317 front port for bug 3764447
80 * This bug has been front ported with some modifications
81 * to account for 11.5.10 features.
82 *
83 * BUG 3877317.
84 *
85 * old_behavior:
86 * Organizations where Cost rollup needs to be performed were determined using
87 * RCV_ORG_ID and ORGANIZATION_ID columns in bom_cto_src_orgs view.
88 *
89 * new behavior:
90 * Organizations where cost rollup needs to be performed will now be determined using
91 * only ORGANIZATION_ID column in bom_cto_src_orgs view.
92 *
93 * procedure CREATE_IN_SRC_ORGS has changed
94 *
95 * 1) change to cursor cSrcOrgs
96 * columns create_bom, cost_rollup and organization_type have been removed as they
97 * will now be queried in the cursor loop
98 * 2) added new variables v_create_bom, v_perform_cost_rollup
99 * 3) cost_rollup flag needs to be queried again as the flag can be updated in the loop
100 * 4) create_bom flag needs to be queried in the loop
101 *
102 * procedure OVERRIDE_BCSO_COST_ROLLUP has changed.
103 *
104 * 1) query fixed to get v_organization_type.
105 * 2) added new too_many_rows exception handler due to query in 1 above
106 * is now dependent only on organization_id
107 * 3) SQL added in too_many_rows exception handler declared in 2 above to check whether
108 * organization_id is make org. query is now based only on organization_id and hence
109 * needs to check whether it is manufacturing org using the following sql.
110 * The code to check whether cost rollup should not be performed needs to know whether
111 * the organization is make organization. In 11.5.9 this check was not required as
112 * create_bom flag was set only for manufacturing org.
113 *
114 * All changes are marked with bug 3877317.
115 *
116 *
117 *
118 ***************************************************************************************/
119
120 /*-------------------------------------------------------------+
121 Name : Create_all_boms_and_routings
122 This procedure loops through all the configuration
123 items in bom_cto_order_lines and calls create_in_src_orgs
124 for each item.
125 +-------------------------------------------------------------*/
126 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
127
128
129 procedure override_bcso_cost_rollup(
130 pLineId in number, -- Current Model Line ID
131 pModelId in number,
132 pConfigId in number,
133 p_cost_organization_id in number,
134 p_organization_id in number,
135 p_group_reference_id in number,
136 xReturnStatus out NOCOPY varchar2,
137 xMsgCount out NOCOPY number,
138 xMsgData out NOCOPY varchar2
139 );
140
141
142
143 FUNCTION is_item_transacted( p_inventory_item_id NUMBER
144 , p_organization_id NUMBER
145 , p_cost_type_id NUMBER )
146 Return BOOLEAN;
147
148
149
150 procedure create_all_boms_and_routings(
151 pAtoLineId in number, -- this is the top ato model line id
152 pFlowCalc in number,
153 xReturnStatus out NOCOPY varchar2,
154 xMsgCount out NOCOPY number,
155 xMsgData out NOCOPY varchar2
156 )
157
158 IS
159
160 cursor cAllConfigItems is
161 select bcol.line_id, bcol.inventory_item_id,
162 bcol.config_item_id
163 from bom_cto_order_lines bcol
164 --where bcol.top_model_line_id = pTopModelLineId
165 where bcol.ato_line_id = pAtoLineId
166 and bcol.bom_item_type = 1
167 and nvl(bcol.wip_supply_type,0) <> 6
168 and bcol.config_item_id is not null
169 and bcol.ato_line_id is not null
170 order by plan_level desc;
171
172 l_line_id oe_order_lines_all.line_id%type;
173 l_config_item_id oe_order_lines_all.inventory_item_id%type;
174 l_rcv_org_id oe_order_lines_all.ship_from_org_id%type;
175
176
177 v_bcol_count number:= 0 ;
178 BEGIN
179
180
181
182 xReturnStatus := FND_API.G_RET_STS_SUCCESS;
183
184
185
186 select count(*) into v_bcol_count from bom_cto_order_lines
187 where ato_line_id = pAtoLineId ;
188
189 oe_debug_pub.add(' CTOBMRTB bcol count ' || v_bcol_count || ' for ' || pAtoLineid , 1);
190
191
192 for lNextRec in cAllConfigItems loop
193
194 IF PG_DEBUG <> 0 THEN
195 oe_debug_pub.add('create_all_boms_and_routings: ' || 'Calling create_in_src_orgs with item ' ||
196 to_char(lNextRec.config_item_id) ||
197 ' and line ' || to_char(lNextRec.line_id), 1);
198 END IF;
199
200 create_in_src_orgs(
201 lNextRec.line_id, -- model line id
202 lNextRec.inventory_item_id, -- model item
203 lNextRec.config_item_id,
204 pFlowCalc,
205 xReturnStatus,
206 xMsgCount,
207 xMsgData);
208
209 IF PG_DEBUG <> 0 THEN
210 oe_debug_pub.add('create_all_boms_and_routings: ' || 'Returned from create_in_src_orgs with result '
211 || xReturnStatus, 1);
212 END IF;
213
214 /* BUG #1957336 Change for preconfigure bom by Sushant Sawant */
215 /* Sushant corrected this implementation */
216
217 if( xReturnStatus = FND_API.G_RET_STS_ERROR ) then
218 RAISE FND_API.G_EXC_ERROR ;
219
220 elsif( xReturnStatus = FND_API.G_RET_STS_UNEXP_ERROR ) then
221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
222
223 end if ;
224
225 end loop;
226
227 /* 2986192 */
228
229 CTO_MATCH_CONFIG.gMatch := 0;
230
231 IF PG_DEBUG <> 0 THEN
232 oe_debug_pub.add('Value of gMatch .. '||CTO_MATCH_CONFIG.gMatch,1);
233 END IF;
234
235
236 --- Added by Renga Kannan on 01/20/04 . Calling the new procedure to Create item attachments
237
238 oe_debug_pub.add('Create_all_boms_and_routings: Calling Create_item_attachments API',1);
239
240 CTO_UTILITY_PK.create_item_attachments(
241 p_ato_line_id => pAtoLineId,
242 x_return_status => xReturnStatus,
243 x_msg_count => xMsgCount,
244 x_msg_data => xMsgData);
245 oe_debug_pub.add('Create_all_boms_and_routings: After Create_item_attachments API',1);
246
247
248
249 -- Get message count and data
250 cto_msg_pub.count_and_get
251 ( p_msg_count => xMsgCount
252 , p_msg_data => xMsgData
253 );
254
255
256 EXCEPTION
257
258 WHEN fnd_api.g_exc_error THEN
259 xReturnStatus := fnd_api.g_ret_sts_error;
260 -- Get message count and data
261 if( xMsgData is null ) then
262 cto_msg_pub.count_and_get
263 ( p_msg_count => xMsgCount
264 , p_msg_data => xMsgData
265 );
266 end if ;
267
268 IF PG_DEBUG <> 0 THEN
269 oe_debug_pub.add('create_all_boms_and_routings: ' || xMsgData , 1);
270 END IF;
271
272 WHEN fnd_api.g_exc_unexpected_error THEN
273 xReturnStatus := fnd_api.g_ret_sts_unexp_error ;
274 -- Get message count and data
275 cto_msg_pub.count_and_get
276 ( p_msg_count => xMsgCount
277 , p_msg_data => xMsgData
278 );
279
280 WHEN OTHERS then
281 IF PG_DEBUG <> 0 THEN
282 oe_debug_pub.add('create_all_boms_and_routings: ' || 'create_all_boms_and_routings::others::'||'::'||sqlerrm, 1);
283 END IF;
284 xReturnStatus := fnd_api.g_ret_sts_unexp_error;
285 -- Get message count and data
286 cto_msg_pub.count_and_get
287 ( p_msg_count => xMsgCount
288 , p_msg_data => xMsgData
289 );
290
291 END create_all_boms_and_routings;
292
293
294
295 -- rkaza. bug 4315973. 08/25/2005. Helper function called from create_in_src
296 -- _orgs
297 -- Start of comments
298 -- API name : get_ato_line_id
299 -- Type : private
300 -- Pre-reqs : None.
301 -- Function : Given line_id, it gives ato_line_id and header_id
302 --
303 -- Parameters:
304 -- IN : p_line_id IN NUMBER Required.
305 -- Version :
306 -- End of comments
307
308 Procedure get_ato_line_id(p_line_id IN Number,
309 x_ato_line_id out NOCOPY number,
310 x_header_id out NOCOPY number,
311 x_return_status OUT NOCOPY varchar2) is
312
313 Begin
314
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316
317 select ato_line_id, header_id
318 into x_ato_line_id, x_header_id
319 from bom_cto_order_lines
320 where line_id = p_line_id ;
321
322 IF PG_DEBUG <> 0 THEN
323 oe_debug_pub.add('get_ato_line_id: ' || 'Queried ato_line_id from bcol for given line_id. exiting...', 5);
324 END IF;
325
326 Exception
327
328 when FND_API.G_EXC_ERROR THEN
329 IF PG_DEBUG <> 0 THEN
330 oe_debug_pub.add('get_ato_line_id: ' || 'expected error: ' || sqlerrm, 1);
331 END IF;
332 x_return_status := FND_API.G_RET_STS_ERROR;
333
334 when FND_API.G_EXC_UNEXPECTED_ERROR then
335 IF PG_DEBUG <> 0 THEN
336 oe_debug_pub.add('get_ato_line_id: ' || 'unexpected error: ' || sqlerrm, 1);
337 END IF;
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
339
340 when others then
341 IF PG_DEBUG <> 0 THEN
342 oe_debug_pub.add('get_ato_line_id: ' || 'When others exception ..' || sqlerrm, 1);
343 END IF;
344 x_return_status := fnd_api.g_ret_sts_unexp_error;
345
346 End get_ato_line_id;
347
348
349
350
351 /*-------------------------------------------------------------+
352 Name : create_in_src_orgs
353 This procedure creates a config item's bom and routing
354 in all of the proper sourcing orgs based on the base
355 model's sourcing rules.
356 +-------------------------------------------------------------*/
357 procedure create_in_src_orgs(
358 pLineId in number, -- Current Model Line ID
359 pModelId in number,
360 pConfigId in number,
361 pFlowCalc in number,
362 xReturnStatus out NOCOPY varchar2,
363 xMsgCount out NOCOPY number,
364 xMsgData out NOCOPY varchar2
365 )
366
367 IS
368
369 lStmtNum number;
370 lStatus number;
371 lItmBillId number;
372 lCfgBillId number;
373 lCfgRtgId number;
374 xBillId number;
375 lXErrorMessage varchar2(100);
376 lXMessageName varchar2(100);
377 lXTableName varchar2(100);
378
379 /* Report errors for single bom not created for non oss
380 bom not created in mfg org for oss
381 bom may not be created if create_config_bom = 'N' or model bom does not exist.
382 */
383 /* bug 3877317.
384 change to cursor cSrcOrgs
385 columns create_bom, cost_rollup and organization_type have been removed as they
386 will now be queried in the cursor loop
387 */
388 cursor cSrcOrgs is
389 select distinct bcso.organization_id,
390 mp.cost_organization_id,
391 bcol.perform_match,
392 bcol.option_specific,
393 -- bcso.create_bom bom_create, bug 3877317 column will be queried in the cursor
394 bcso.model_item_id,
395 bcso.config_item_id,
396 bcso.group_reference_id
397 -- bcso.cost_rollup, bug 3877317 column will be queried in the cursor
398 -- bcso.organization_type bug 3877317 column will be queried in the cursor
399 from bom_cto_src_orgs bcso, bom_cto_order_lines bcol, mtl_parameters mp
400 where bcso.line_id = pLineId
401 and bcso.model_item_id = pModelId
402 and bcso.config_item_id is not null
403 and bcso.line_id = bcol.line_id
404 and bcso.organization_id = mp.organization_id ;
405
406
407
408
409 v_primary_cost_method mtl_parameters.primary_cost_method%type := null ;
410 v_cto_cost cst_item_costs.item_cost%type := null ;
411 v_cto_cost_xudc cst_item_costs.item_cost%type := null ;
412 v_valuation_cost cst_item_costs.item_cost%type := null ;
413 v_buy_cost cst_item_costs.item_cost%type := null ;
414
415 v_cto_cost_type_id cst_item_costs.cost_type_id%type ;
416 v_buy_cost_type_id cst_item_costs.cost_type_id%type ;
417 v_rolledup_cost_count number ;
418 v_rolledup_cost number ;
419 lBuyCostType varchar2(30);
420
421 v_item_transacted boolean := FALSE ;
422
423 /* bugfix 2986192 Cursor to select dropped items during match */
424 /* Effectivity date bug fix : 4147224
425 Need to validate the dropped component for Estimated relase date
426 window. Added a xEstRelDate parameter to cursor and
427 added effectivity window condition for bom_inventory_comps_interface
428 Sql
429 */
430
431 cursor mismatched_items ( xlineid number,
432 xconfigbillid number,
433 xEstRelDate Date) is
434 select inventory_item_id
435 from bom_cto_order_lines
436 where parent_ato_line_id=xlineid
437 and parent_ato_line_id <> line_id /* to avoid selecting top model */
438 and NOT ( bom_item_type = 1 and wip_supply_type <> 6 and line_id <> xlineid ) /* to avoid selecting lower level models */
439 minus
440 select component_item_id
441 from bom_inventory_comps_interface
442 where bill_sequence_id = xconfigbillid
443 and greatest(sysdate, xEstRelDate ) >= effectivity_date
444 and (( disable_date is null ) or ( disable_date is not null and disable_date >= greatest(sysdate, xEstRelDate) )) ;
445
446 l_missed_item_id number;
447 v_missed_item varchar2(50);
448 l_config_item varchar2(50);
449 l_model varchar2(50);
450 -- l_missed_line_number varchar2(50);
451 v_order_number number := 0;
452 l_token CTO_MSG_PUB.token_tbl;
453 l_token1 CTO_MSG_PUB.token_tbl;
454 lcreate_item number;
455 lorg_code varchar2(3);
456
457
458 /* 2986192 End declaration */
459 lComItmBillId Number; -- 3543547
460
461 v_bom_created number := 0 ;
462 v_config_bom_exists number := 0 ;
463 v_bcol_count number := 0 ;
464
465 v_model_item_name varchar2(2000) ;
466
467 /* bug 3877317
468 added new variables v_create_bom, v_perform_cost_rollup
469 */
470 v_create_bom bom_cto_src_orgs.create_bom%type := null ; -- bug 3877317
471 v_perform_cost_rollup bom_cto_src_orgs.cost_rollup%type := null ; -- bug 3877317
472
473 l_ato_line_id number;
474 l_header_id number;
475 lEstRelDate Date;
476 lLeadTime Number;
477 -- Fix bug 5199775
478 v_program_id bom_cto_order_lines.program_id%type ;
479 v_option_num number := 0 ;
480 v_dropped_item_string varchar2(2000) ;
481 v_sub_dropped_item_string varchar2(2000) ;
482 v_ac_message_string varchar2(2000) ;
483 v_missed_line_number varchar2(50);
484 l_new_line varchar2(10) := fnd_global.local_chr(10);
485 v_problem_model varchar2(1000) ;
486 v_problem_config varchar2(1000) ;
487 v_problem_model_line_num varchar2(1000) ;
488 v_table_count number ;
489 v_error_org varchar2(1000) ;
490 v_recipient varchar2(100) ;
491 lplanner_code mtl_system_items_vl.planner_code%type;
492
493 --Bugfix 11056452
494 lCnt number;
495
496 BEGIN
497
498 xReturnStatus := fnd_api.g_ret_sts_success;
499
500 IF PG_DEBUG <> 0 THEN
501 oe_debug_pub.add(' entered create_in_src_orgs: model ' || pModelId
502 || ' Line ' || pLineId , 1);
503 END IF;
504
505 -- rkaza. bug 4315973.
506 get_ato_line_id(p_line_id => pLineId,
507 x_ato_line_id => l_ato_line_id,
508 x_header_id => l_header_id,
509 x_return_status => xReturnStatus);
510
511 if xReturnStatus <> fnd_api.g_ret_sts_success then
512 raise fnd_api.g_exc_unexpected_error;
513 end if;
514
515 --- Fixed bug 5485452
516 select program_id
517 into v_program_id
518 from bom_cto_order_lines
519 where line_id = pLineId;
520
521 select count(*) into v_bcol_count from bom_cto_order_lines
522 where ato_line_id = pLineId ;
523
524 oe_debug_pub.add(' bcol count ' || v_bcol_count , 1);
525
526 select count(*) into v_bcol_count from bom_cto_order_lines
527 where ato_line_id = pLineId and option_specific = 'N' ;
528
529 oe_debug_pub.add(' bcol count ' || v_bcol_count , 1);
530
531 lStmtNum := 10 ;
532
533
534 for lNextRec in cSrcOrgs loop
535
536 oe_debug_pub.add(' ****** entered cSrcOrgs loop ****************************' , 1);
537
538 lStmtNum := 20 ;
539
540
541
542 /* begin bug 3877317
543 cost_rollup flag needs to be queried again as the flag can be updated in the loop
544 */
545 v_perform_cost_rollup := 'N' ;
546
547 begin
548
549 select 'Y' into v_perform_cost_rollup from dual
550 where exists ( select * from bom_cto_src_orgs
551 where line_id = pLineId
552 and cost_rollup = 'Y'
553 and organization_id = lNextRec.cost_organization_id ) ;
554
555
556 exception
557 when others then
558 v_perform_cost_rollup := 'N' ;
559
560 end ;
561
562 /* end bug 3877317 */
563
564
565
566 /* begin bug 3877317
567 create_bom flag needs to be queried in the loop
568 */
569
570 v_create_bom := 'N' ;
571
572 begin
573
574 select 'Y' into v_create_bom from dual
575 where exists ( select * from bom_cto_src_orgs
576 where line_id = pLineId
577 and create_bom = 'Y'
578 and organization_id = lNextRec.organization_id ) ;
579
580
581 exception
582 when others then
583 v_create_bom := 'N' ;
584
585 end ;
586
587 /* end bug 3877317 */
588
589
590 oe_debug_pub.add(' entered cSRcOrgs model ' || lNextRec.model_item_id
591 || ' config ' || lNextRec.config_item_id
592 || ' org ' || lNextRec.organization_id , 1);
593
594 oe_debug_pub.add(' entered cSRcOrgs model bom ' || v_create_bom || ' cost ' || v_perform_cost_rollup || ' option ' || lNextRec.option_specific , 1 ) ;
595
596
597 if( v_perform_cost_rollup = 'Y' ) then -- bug 3877317 replaced variable
598 oe_debug_pub.add(' create_in_src_orgs: ' || ' Going to call override_bcso_cost_rollup ' , 1 ) ;
599
600 override_bcso_cost_rollup(
601 pLineId, -- Current Model Line ID
602 pModelId,
603 pConfigId,
604 lNextRec.cost_organization_id,
605 lNextRec.organization_id,
606 lNextRec.group_reference_id,
607 xReturnStatus,
608 xMsgCount,
609 xMsgData );
610
611 oe_debug_pub.add(' create_in_src_orgs: ' || ' Done override_bcso_cost_rollup ' , 1 ) ;
612
613 end if;
614
615
616 lStmtNum := 30 ;
617
618 if( v_create_bom = 'Y' ) then -- bug 3877317 replaced variable
619 -- check if model bom exists in src org
620
621 lStmtNum := 40;
622 IF PG_DEBUG <> 0 THEN
623 oe_debug_pub.add('create_in_src_orgs: ' || ' Going to check bom for . Item: ' ||
624 to_char(pConfigId) || '. Org ' ||
625 to_char(lNextRec.organization_id), 1);
626 END IF;
627
628
629 /* May not be required as model bom exists */
630 lStmtNum := 100;
631 lStatus := CTO_CONFIG_BOM_PK.check_bom(
632 pItemId => pModelId,
633 pOrgId => lNextRec.organization_id,
634 xBillId => lItmBillId);
635
636 IF PG_DEBUG <> 0 THEN
637 oe_debug_pub.add('create_in_src_orgs: '
638 || 'Returned from check_bom for model with result '
639 || to_char(lStatus), 1);
640 END IF;
641
642
643
644 if (lStatus = 1) then
645
646
647
648 lStmtNum := 110;
649 lStatus := CTO_CONFIG_BOM_PK.check_bom(
650 pItemId => pConfigId,
651 pOrgId => lNextRec.organization_id,
652 xBillId => lItmBillId);
653
654 IF PG_DEBUG <> 0 THEN
655 oe_debug_pub.add('create_in_src_orgs: '
656 || 'Returned from check_bom for config with result '
657 || to_char(lStatus), 1);
658 END IF;
659
660 if (lStatus = 1) then
661 v_config_bom_exists := v_config_bom_exists + 1 ;
662
663 IF PG_DEBUG <> 0 THEN
664 oe_debug_pub.add('create_in_src_orgs: ' || 'Config BOM ' || lItmBillId || '
665 already exists ' ,1);
666 END IF;
667
668 /*2986192*/
669
670 IF PG_DEBUG <> 0 THEN
671 oe_debug_pub.add('Checking for dropped items ... ' ,1);
672 oe_debug_pub.add('Config id '||pConfigId||' Org '||lNextRec.organization_id, 1);
673 END IF;
674
675 -- 3543547
676
677 select common_bill_sequence_id
678 into lComItmBillId
679 from bom_bill_of_materials
680 where bill_sequence_id = lItmBillId;
681
682 IF PG_DEBUG <> 0 THEN
683 oe_debug_pub.add('create_in_src_orgs: ' || 'Common Bill Id '
684 ||lComItmBillId,1);
685 END IF;
686
687 -- 3543547
688
689 lStmtNum := 111;
690
691 begin
692
693 IF PG_DEBUG <> 0 THEN
694 oe_debug_pub.add('Inserting into BICI ... ' ,1);
695 END IF;
696
697 -- rkaza. bug 4524248. 11/09/2005.
698 -- bom structure import enhancements. Added batch_id.
699
700 insert into bom_inventory_comps_interface(component_item_id,bill_sequence_id, batch_id,
701 effectivity_date,disable_date)
702 select component_item_id, bill_sequence_id,
703 cto_msutil_pub.bom_batch_id,effectivity_date,disable_date
704 from bom_inventory_components
705 where bill_sequence_id = lComItmBillId; -- 3543547 lItmBillId;
706
707 IF PG_DEBUG <> 0 THEN
708 oe_debug_pub.add('Value of gMatch '||CTO_MATCH_CONFIG.gMatch ,1);
709 oe_debug_pub.add('inserting into bici'|| SQL%ROWCOUNT || ' for bill ' || lItmBillId ,1);
710 END IF;
711
712
713
714
715
716
717 if CTO_MATCH_CONFIG.gMatch = 1 then
718
719
720
721
722 /*
723 if lNextRec.perform_match = 'Y' then
724 */
725
726 lStmtNum := 121;
727
728 IF PG_DEBUG <> 0 THEN
729 oe_debug_pub.add('Inserting child base model into BICI for matched cases... ' ,1);
730 END IF;
731
732 insert into bom_inventory_comps_interface(component_item_id,bill_sequence_id, batch_id,
733 effectivity_date,disable_date)
734 select distinct a.base_model_id, b.bill_sequence_id,
735 cto_msutil_pub.bom_batch_id,effectivity_date,disable_date
736 from bom_ato_configurations a,bom_inventory_components b
737 where a.config_item_id = b.component_item_id
738 and b.bill_sequence_id = lComItmBillId; -- 3543547 lItmBillId
739
740 end if;
741
742 exception
743 when others then
744 IF PG_DEBUG <> 0 THEN
745 oe_debug_pub.add('Failed to insert into bom_inventory_comps_interface with error '||sqlerrm);
746 END IF;
747 raise fnd_api.g_exc_error;
748 end ;
749
750 lStmtNum := 112;
751
752 begin
753
754 IF PG_DEBUG <> 0 THEN
755 oe_debug_pub.add ('Line_id '||pLineId,1);
756 END IF;
757
758 lcreate_item := nvl(FND_PROFILE.VALUE('CTO_CONFIG_EXCEPTION'), 1);
759
760 IF PG_DEBUG <> 0 THEN
761 oe_debug_pub.add ('Config exception profile '||lcreate_item);
762 END IF;
763
764 /* Added by Renga Kannan
765 Effectivity date bug fix : 4147224
766 The following part of the code is added
767 to get the lead time of config item */
768 lStmtNum := 113;
769 Begin
770 -- Fixed fp bug 5485452
771 If ( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
772 select (ceil(nvl(msi.fixed_lead_time,0)
773 + nvl(msi.variable_lead_time,0) * bcol.ordered_quantity))
774 into lLeadTime
775 from mtl_system_items msi,
776 bom_cto_order_lines bcol
777 where bcol.line_id = pLineId
778 and msi.inventory_item_id = bcol.inventory_item_id
779 and msi.organization_id = CTO_UTILITY_PK.PC_BOM_VALIDATION_ORG;
780
781 else
782 select (ceil(nvl(msi.fixed_lead_time,0)
783 + nvl(msi.variable_lead_time,0) * oel.ordered_quantity))
784 into lLeadTime
785 from mtl_system_items msi,
786 oe_order_lines_all oel
787 where oel.line_id = pLineId
788 and msi.inventory_item_id = oel.inventory_item_id
789 and msi.organization_id = oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id);
790 end if;
791 Exception when others then
792 IF PG_DEBUG <> 0 THEN
793 oe_debug_pub.add('create_in_src_orgs: ' || 'Failed in get_model_lead_time. ', 1);
794 END IF;
795 raise FND_API.G_EXC_ERROR;
796 End;
797
798 If PG_DEBUG <> 0 Then
799 oe_debug_pub.add('Create_in_src_orgs: '||' Config item lead time = '||to_char(lLeadTime),1);
800 oe_debug_pub.add('Create_in_src_orgs: Going to Calculate Estimated release date for the matched config item',1);
801 End if;
802 lStmtNum := 114;
803 begin
804 select CAL.CALENDAR_DATE
805 into lEstRelDate
806 from bom_calendar_dates cal,
807 mtl_system_items msi,
808 bom_cto_order_lines bcol,
809 mtl_parameters mp
810 where msi.organization_id = lNextRec.organization_id
811 and msi.inventory_item_id = pModelId
812 and bcol.line_id = pLineId
813 and bcol.inventory_item_id = msi.inventory_item_id
814 and mp.organization_id = msi.organization_id
815 and cal.calendar_code = mp.calendar_code
816 and cal.exception_set_id = mp.calendar_exception_set_id
817 and cal.seq_num =
818 (select cal2.prior_seq_num - lLeadTime
819 from bom_calendar_dates cal2
820 where cal2.calendar_code = mp.calendar_code
821 and cal2.exception_set_id = mp.calendar_exception_set_id
822 and cal2.calendar_date = trunc(bcol.schedule_ship_date));
823 exception
824 when no_data_found then
825 IF PG_DEBUG <> 0 THEN
826 oe_debug_pub.add('Create_in_src_orgs: ' || 'Unexpected error while computing estimated relase date',1);
827 END IF;
828 raise fnd_api.g_exc_unexpected_error;
829 end;
830
831 If PG_DEBUG <> 0 Then
832 oe_debug_pub.add('Create_in_src_orgs: '||' Estimated Release Date = '||to_char(lEstRelDate,'mm/dd/yy:hh:mi:ss'),1);
833 End if;
834 /* End of bug fix 4147224 */
835
836 Open mismatched_items(pLineId, lComItmBillId,lEstRelDate); -- 3543547 Replace lItmBillId with LComItmBillId
837
838 loop
839
840 fetch mismatched_items into l_missed_item_id;
841
842 IF PG_DEBUG <> 0 THEN
843 oe_debug_pub.add ('Missed item id '||l_missed_item_id,1);
844 END IF;
845
846 exit when mismatched_items%NOTFOUND;
847
848 v_option_num := v_option_num + 1 ;
849
850 lStmtNum := 113;
851
852 begin
853
854 IF PG_DEBUG <> 0 THEN
855 oe_debug_pub.add('Select missed component details.. ' ,1);
856 END IF;
857
858 -- Bug Fix 5199775
859 if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
860 IF PG_DEBUG <> 0 THEN
861 oe_debug_pub.add('Pre configured Item .. ' ,1);
862 END IF;
863 IF PG_DEBUG <> 0 THEN
864 oe_debug_pub.add('Pre configured Item .. ' ,1);
865 END IF;
866
867 select substrb(msi.concatenated_segments,1,50),
868 'Not Available' ,
869 -1
870 into v_missed_item,
871 v_missed_line_number,
872 v_order_number
873 from mtl_system_items_kfv msi,
874 bom_cto_order_lines bcol
875 where msi.organization_id = bcol.ship_from_org_id
876 and msi.inventory_item_id = bcol.inventory_item_id
877 and bcol.parent_ato_line_id = pLineId
878 and bcol.inventory_item_id = l_missed_item_id
879 and rownum = 1;
880
881 else
882
883 IF PG_DEBUG <> 0 THEN
884 oe_debug_pub.add('Auto configured Item .. ' ,1);
885 END IF;
886
887 select substrb(concatenated_segments,1,50),
888 to_char(oel.line_number)||'.'||to_char(oel.shipment_number) ||
889 decode(oel.option_number,NULL,NULL,'.'||to_char(option_number)),
890 oeh.order_number
891 into v_missed_item,
892 v_missed_line_number,
893 v_order_number
894 from mtl_system_items_kfv msi,
895 oe_order_lines_all oel,
896 oe_order_headers_all oeh
897 ,bom_cto_order_lines bcol
898 where msi.organization_id = oel.ship_from_org_id
899 and msi.inventory_item_id = oel.inventory_item_id
900 and oel.header_id = oeh.header_id
901 and oel.inventory_item_id = l_missed_item_id
902 and oel.line_id = bcol.line_id
903 and bcol.parent_ato_line_id = pLineId
904 and rownum =1;
905 End if;
906
907 /* fix oel.ato_line_id = pLineId to bcol.parent_ato_line_id = pLineId
908 pLineId is the line id of child model while ato_line_id is the line_id of parent model.
909 For multi-level model where option is missing for child model , join of
910 oel.ato_line_id = pLineId will fail
911 E.g
912 BILL Line id ATO line Id Parent ATO line id
913
914 M1 100 100 100
915 ....M2 200 100 100
916 ........OC1 300 100 200
917 .........OI1 400 100 200
918 .........OI2 500 100 200
919
920
921 So if OI2 is dropped , pLineId = 200 whereas ato_line_id = 100
922 The program will error out with NDF
923 */
924
925
926 lStmtNum := 114;
927 IF PG_DEBUG <> 0 THEN
928 oe_debug_pub.add('Select model.. ' ,1);
929 END IF;
930
931 select substrb(concatenated_segments,1,50)
932 into l_model
933 from mtl_system_items_kfv
934 where organization_id = lNextRec.organization_id
935 and inventory_item_id = pModelId ;
936
937
938 lStmtNum := 117;
939 IF PG_DEBUG <> 0 THEN
940 oe_debug_pub.add('Select Org.. ' ,1);
941 END IF;
942
943 select organization_code
944 into lOrg_code
945 from mtl_parameters
946 where organization_id = lNextRec.organization_id ;
947
948 -- Bug Fix 519975
949
950 if ( v_option_num = 1 ) then
951 v_dropped_item_string := 'Option ' || v_option_num || ': ' || v_missed_item || l_new_line ;
952 v_ac_message_string := ' Line ' || v_missed_line_number || ' ' || v_dropped_item_string ;
953 else
954 v_sub_dropped_item_string := 'Option ' || v_option_num || ': ' || v_missed_item || l_new_line ;
955 v_dropped_item_string := v_dropped_item_string || v_sub_dropped_item_string ;
956 v_ac_message_string := v_ac_message_string || ' Line ' || v_missed_line_number || ' ' || v_sub_dropped_item_string ;
957 end if ;
958
959
960 -- Bug Fix 519975
961
962 if ( lcreate_item = 1 ) then
963 IF PG_DEBUG <> 0 THEN
964 oe_debug_pub.add ('Warning: The component '||v_missed_item
965 || ' on Line Number '||v_missed_line_number
966 || ' in organization ' || lOrg_code
967 || ' was not included in the configured item''s bill. ',1);
968 oe_debug_pub.add ('Model Name : '||l_model,1);
969 oe_debug_pub.add ('Order Number : '||v_order_number,1);
970 END IF;
971 -- Bug fix 5199775. Commented the following error message
972 -- as we will be raising one message for all components
973 /*
974
975 l_token(1).token_name := 'OPTION_NAME';
976 l_token(1).token_value := l_missed_item;
977 l_token(2).token_name := 'LINE_ID';
978 l_token(2).token_value := l_missed_line_number;
979 l_token(3).token_name := 'ORG_CODE';
980 l_token(3).token_value := lOrg_code ;
981 l_token(4).token_name := 'MODEL_NAME';
982 l_token(4).token_value := l_model;
983 l_token(5).token_name := 'ORDER_NUMBER';
984 l_token(5).token_value := l_order_number;
985
986 cto_msg_pub.cto_message('BOM','CTO_DROP_ITEM_FROM_CONFIG',l_token);
987 */
988 else
989 IF PG_DEBUG <> 0 THEN
990 oe_debug_pub.add ('Warning: The configured item was not created because component '||v_missed_item
991 || ' on Line Number '||v_missed_line_number
992 || ' in organization ' || lOrg_code
993 || ' could not be included in the configured item''s bill. ',1);
994 oe_debug_pub.add ('Model Name : '||l_model,1);
995 oe_debug_pub.add ('Order Number : '||v_order_number,1);
996 END IF;
997
998 -- Bug Fix 5199775
999
1000 /*
1001 l_token(1).token_name := 'OPTION_NAME';
1002 l_token(1).token_value := l_missed_item;
1003 l_token(2).token_name := 'LINE_ID';
1004 l_token(2).token_value := l_missed_line_number;
1005 l_token(3).token_name := 'ORG_CODE';
1006 l_token(3).token_value := lOrg_code ;
1007 l_token(4).token_name := 'MODEL_NAME';
1008 l_token(4).token_value := l_model;
1009 l_token(5).token_name := 'ORDER_NUMBER';
1010 l_token(5).token_value := l_order_number;
1011
1012 cto_msg_pub.cto_message('BOM','CTO_DO_NOT_CREATE_ITEM',l_token);
1013 */
1014
1015 end if;
1016 -- end new message fix 2986192
1017
1018
1019 EXCEPTION /* exception for stmt 113 ,114 and 117*/
1020
1021 when others then
1022 IF PG_DEBUG <> 0 THEN
1023 oe_debug_pub.add('Others excepn from stmt '||lStmtNum ||':'||sqlerrm);
1024 END IF;
1025 raise fnd_api.g_exc_error;
1026 END ;
1027
1028 end loop;
1029 /* Fixed by Renga Kannan for bug 5199775
1030
1031 if mismatched_items%ROWCOUNT > 0 then
1032
1033 CTO_CONFIG_BOM_PK.gDropItem := 0;
1034
1035 lStmtNum := 115;
1036
1037
1038 if ( lcreate_item = 1) then
1039 IF PG_DEBUG <> 0 THEN
1040 oe_debug_pub.add ('Setting the global var gApplyHold to Y');
1041 END IF;
1042 CTO_CONFIG_BOM_PK.gApplyHold := 'Y';
1043
1044
1045
1046
1047
1048 else
1049 IF PG_DEBUG <> 0 THEN
1050 oe_debug_pub.add ('Not creating Item...');
1051 END IF;
1052
1053 -- rkaza. 08/25/2005. bug 4315973.
1054 -- Applying hold even for dropped item cases when config bom
1055 -- exists and profile set to not create item.
1056
1057 cto_utility_pk.apply_create_config_hold( l_ato_line_id, l_header_id, xReturnStatus, xMsgCount, xMsgData ) ;
1058
1059 -- pop up message that model is put on hold.
1060 cto_msg_pub.cto_message('BOM','CTO_MODEL_LINE_EXCPN_HOLD');
1061
1062 raise fnd_api.g_exc_error;
1063
1064 end if;
1065
1066 end if;
1067 */
1068
1069 if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
1070 IF PG_DEBUG <> 0 THEN
1071 oe_debug_pub.add ('Will not go through Hold Logic and Notification as Preconfigured Bom' , 1 );
1072 END IF;
1073 if mismatched_items%ROWCOUNT > 0 then
1074 if ( lcreate_item = 1 ) then
1075 IF PG_DEBUG <> 0 THEN
1076 oe_debug_pub.add ('Create Item profile set to Create and Link Item ' , 1 );
1077 END IF;
1078
1079 lxMessageName := 'CTO_DROP_ITEM_FROM_CONFIG';
1080
1081 select segment1
1082 into v_problem_model
1083 from mtl_system_items
1084 where inventory_item_id = pModelId
1085 and rownum = 1 ;
1086
1087 select segment1
1088 into v_problem_config
1089 from mtl_system_items
1090 where inventory_item_id = pConfigId
1091 and rownum = 1 ;
1092
1093 select organization_name
1094 into v_error_org
1095 from inv_organization_name_v
1096 where organization_id = lNextRec.organization_id ;
1097
1098 v_problem_model_line_num := ' -1 ' ;
1099
1100 v_table_count := CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE.count + 1 ;
1101 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROCESS := 'NOTIFY_OID_IC' ; /* ITEM CREATED */
1102 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).LINE_ID := pLineId ;
1103 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).SALES_ORDER_NUM := null ;
1104 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).ERROR_MESSAGE := v_dropped_item_string ;
1105 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_MODEL_NAME := null ;
1106 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_MODEL_LINE_NUM := null ;
1107 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_CONFIG_NAME := null ;
1108 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_CONFIG_LINE_NUM := null ;
1109 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROBLEM_MODEL := v_problem_model ;
1110 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROBLEM_MODEL_LINE_NUM := v_problem_model_line_num ;
1111 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROBLEM_CONFIG := v_problem_config ;
1112 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).ERROR_ORG := v_error_org ;
1113 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).ERROR_ORG_ID := lNextRec.organization_id ;
1114 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).MFG_REL_DATE := lEstRelDate ;
1115
1116 IF PG_DEBUG <> 0 THEN
1117 oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1118 END IF;
1119
1120 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1121
1122 else /* lcreate_item <> 1 */
1123
1124 IF PG_DEBUG <> 0 THEN
1125 oe_debug_pub.add ('Create Item profile set to Do Not Create Item ' , 1 );
1126 END IF;
1127
1128 lxMessageName := 'CTO_DO_NOT_CREATE_ITEM';
1129
1130 select segment1
1131 into v_problem_model
1132 from mtl_system_items
1133 where inventory_item_id = pModelId
1134 and rownum = 1 ;
1135
1136 select segment1
1137 into v_problem_config
1138 from mtl_system_items
1139 where inventory_item_id = pConfigId
1140 and rownum = 1 ;
1141
1142 select organization_name
1143 into v_error_org
1144 from inv_organization_name_v
1145 where organization_id = lNextRec.organization_id ;
1146
1147
1148 v_problem_model_line_num := ' -1 ' ;
1149
1150
1151 v_table_count := CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE.count + 1 ;
1152 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROCESS := 'NOTIFY_OID_INC' ; /* ITEM NOT CREATED */
1153 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).LINE_ID := pLineId ;
1154 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).SALES_ORDER_NUM := null ;
1155 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).ERROR_MESSAGE := v_dropped_item_string ;
1156 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_MODEL_NAME := null ;
1157 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_MODEL_LINE_NUM := null ;
1158 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_CONFIG_NAME := null ;
1159 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_CONFIG_LINE_NUM := null ;
1160 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROBLEM_MODEL := v_problem_model ;
1161 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROBLEM_MODEL_LINE_NUM := v_problem_model_line_num ;
1162 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROBLEM_CONFIG := v_problem_config ;
1163 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).ERROR_ORG := v_error_org ;
1164 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).ERROR_ORG_ID := lNextRec.organization_id ;
1165 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).MFG_REL_DATE := lEstRelDate ;
1166
1167 IF PG_DEBUG <> 0 THEN
1168 oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1169 END IF;
1170
1171 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1172
1173
1174
1175 raise fnd_api.g_exc_error;
1176
1177 end if; /* lcreate_item = 1 */
1178
1179 end if; /*mismatched_items%ROWCOUNT > 0 */
1180
1181 else /* v_program_id <> CTO_UTILITY_PK.PC_BOM_PROGRAM_ID */
1182 if mismatched_items%ROWCOUNT > 0 then
1183 CTO_CONFIG_BOM_PK.gDropItem := 0;
1184
1185 lStmtNum := 55;
1186 if ( lcreate_item = 1 ) then
1187 IF PG_DEBUG <> 0 THEN
1188 oe_debug_pub.add ('Setting the global var gApplyHold to Y');
1189 END IF;
1190
1191 CTO_CONFIG_BOM_PK.gApplyHold := 'Y';
1192 select segment1
1193 into v_problem_model
1194 from mtl_system_items
1195 where inventory_item_id = pModelId
1196 and rownum = 1 ;
1197
1198 select segment1
1199 into v_problem_config
1200 from mtl_system_items
1201 where inventory_item_id = pConfigId
1202 and rownum = 1 ;
1203
1204 select organization_name
1205 into v_error_org
1206 from inv_organization_name_v
1207 where organization_id = lNextRec.organization_id ;
1208
1209 if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
1210 v_problem_model_line_num := ' -1 ' ;
1211 else
1212 select oel.line_number || '.' || oel.shipment_number
1213 into v_problem_model_line_num
1214 from oe_order_lines_all oel
1215 where line_id = pLineId ;
1216 end if;
1217 oe_debug_pub.add( ' DROPPED ITEM INFO: ' ||
1218 ' Problem Model ' || v_problem_model ||
1219 ' Problem CONFIG ' || v_problem_config ||
1220 ' ERROR ORG ' || v_error_org ||
1221 ' PROBLEM MODEL LINE NUM ' || v_problem_model_line_num
1222 , 1 ) ;
1223
1224 v_table_count := CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE.count + 1 ;
1225 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROCESS := 'NOTIFY_OID_IC' ; /* ITEM CREATED */
1226 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).LINE_ID := pLineId ;
1227 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).SALES_ORDER_NUM := null ;
1228 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).ERROR_MESSAGE := v_dropped_item_string ;
1229 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_MODEL_NAME := null ;
1230 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_MODEL_LINE_NUM := null ;
1231 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_CONFIG_NAME := null ;
1232 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_CONFIG_LINE_NUM := null ;
1233 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROBLEM_MODEL := v_problem_model ;
1234 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROBLEM_MODEL_LINE_NUM := v_problem_model_line_num ;
1235 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROBLEM_CONFIG := v_problem_config ;
1236 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).ERROR_ORG := v_error_org ;
1237 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).ERROR_ORG_ID := lNextRec.organization_id ;
1238 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).MFG_REL_DATE := lEstRelDate ;
1239
1240 IF PG_DEBUG <> 0 THEN
1241 oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1242 END IF;
1243
1244 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).REQUEST_ID := to_char(fnd_global.conc_request_id) ;
1245
1246 IF PG_DEBUG <> 0 THEN
1247 oe_debug_pub.add('create_bom_ml: ' || 'Getting Custom Recipient..',3);
1248 END IF;
1249
1250 v_recipient := CTO_CUSTOM_NOTIFY_PK.get_recipient( p_error_type => CTO_UTILITY_PK.OPT_DROP_AND_ITEM_CREATED
1251 ,p_inventory_item_id => pModelId
1252 ,p_organization_id => lNextRec.organization_id
1253 ,p_line_id => pLineId );
1254
1255
1256
1257
1258 if( v_recipient is not null ) then
1259 IF PG_DEBUG <> 0 THEN
1260 oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK..' || v_recipient ,3);
1261 END IF;
1262
1263 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).NOTIFY_USER := v_recipient ; /* commented 'MFG' */
1264
1265 else
1266 IF PG_DEBUG <> 0 THEN
1267 oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK is null ..' , 3);
1268 oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
1269 END IF;
1270
1271 BEGIN
1272 SELECT u.user_name
1273 INTO lplanner_code
1274 FROM mtl_system_items_vl item
1275 ,mtl_planners p
1276 ,fnd_user u
1277 WHERE item.inventory_item_id = pModelId
1278 and item.organization_id = lNextRec.organization_id
1279 and p.organization_id = item.organization_id
1280 and p.planner_code = item.planner_code
1281 and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
1282 oe_debug_pub.add('create_bom_ml: ' || '****PLANNER CODE DATA' || lplanner_code ,2);
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 IF PG_DEBUG <> 0 THEN
1286 oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1287
1288 oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
1289
1290
1291 END IF;
1292 END;
1293
1294
1295
1296 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).NOTIFY_USER := lplanner_code ; /* commented 'MFG' */
1297
1298 end if; /* check custom recipient */
1299 l_token(1).token_name := 'ORDER_NUM';
1300 l_token(1).token_value := v_order_number;
1301 l_token(2).token_name := 'ORG';
1302 l_token(2).token_value := v_error_org;
1303 l_token(3).token_name := 'CONFIG_NAME';
1304 l_token(3).token_value := v_problem_config;
1305 l_token(4).token_name := 'ERROR_MESSAGE';
1306 l_token(4).token_value := v_ac_message_string ;
1307 cto_msg_pub.cto_message('BOM','CTO_DROP_ITEM_FROM_CONFIG',l_token);
1308
1309
1310 else
1311
1312 IF PG_DEBUG <> 0 THEN
1313 oe_debug_pub.add ('Not creating Item...');
1314 END IF;
1315 select segment1
1316 into v_problem_model
1317 from mtl_system_items
1318 where inventory_item_id = pModelId
1319 and rownum = 1 ;
1320
1321 select segment1
1322 into v_problem_config
1323 from mtl_system_items
1324 where inventory_item_id = pConfigId
1325 and rownum = 1 ;
1326 select organization_name
1327 into v_error_org
1328 from inv_organization_name_v
1329 where organization_id = lNextRec.organization_id ;
1330
1331
1332 select oel.line_number || '.' || oel.shipment_number
1333 into v_problem_model_line_num
1334 from oe_order_lines_all oel
1335 where line_id = pLineId ;
1336 v_table_count := CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE.count + 1 ;
1337 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROCESS := 'NOTIFY_OID_INC' ; /* ITEM NOT CREATED */
1338 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).LINE_ID := pLineId ;
1339 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).SALES_ORDER_NUM := null ;
1340 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).ERROR_MESSAGE := v_dropped_item_string ;
1341 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_MODEL_NAME := null ;
1342 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_MODEL_LINE_NUM := null ;
1343 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_CONFIG_NAME := null ;
1344 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).TOP_CONFIG_LINE_NUM := null ;
1345 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROBLEM_MODEL := v_problem_model ;
1346 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROBLEM_MODEL_LINE_NUM := v_problem_model_line_num ;
1347 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).PROBLEM_CONFIG := v_problem_config ;
1348 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).ERROR_ORG := v_error_org ;
1349 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).ERROR_ORG_ID := lNextRec.organization_id ;
1350 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).MFG_REL_DATE := lEstRelDate ;
1351
1352 IF PG_DEBUG <> 0 THEN
1353 oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1354 END IF;
1355
1356 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1357
1358 IF PG_DEBUG <> 0 THEN
1359 oe_debug_pub.add('create_bom_ml: ' || 'Getting Custom Recipient..',3);
1360 END IF;
1361
1362 v_recipient := CTO_CUSTOM_NOTIFY_PK.get_recipient( p_error_type => CTO_UTILITY_PK.OPT_DROP_AND_ITEM_NOT_CREATED
1363 ,p_inventory_item_id => pModelId
1364 ,p_organization_id => lNextRec.organization_id
1365 ,p_line_id => pLineId );
1366
1367
1368
1369
1370 if( v_recipient is not null ) then
1371 IF PG_DEBUG <> 0 THEN
1372 oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK..' || v_recipient ,3);
1373 END IF;
1374
1375 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).NOTIFY_USER := v_recipient ; /* commented 'MFG' */
1376
1377 else
1378
1379 IF PG_DEBUG <> 0 THEN
1380 oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK is null ..' , 3);
1381 oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
1382 END IF;
1383
1384 BEGIN
1385 SELECT u.user_name
1386 INTO lplanner_code
1387 FROM mtl_system_items_vl item
1388 ,mtl_planners p
1389 ,fnd_user u
1390 WHERE item.inventory_item_id = pModelId
1391 and item.organization_id = lNextRec.organization_id
1392 and p.organization_id = item.organization_id
1393 and p.planner_code = item.planner_code
1394 and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
1395
1396
1397 oe_debug_pub.add('create_bom_ml: ' || '****PLANNER CODE DATA' || lplanner_code ,2);
1398
1399
1400 EXCEPTION
1401 WHEN OTHERS THEN
1402 IF PG_DEBUG <> 0 THEN
1403 oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1404
1405 oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
1406
1407
1408 END IF;
1409 END;
1410
1411 CTO_CONFIG_BOM_PK.G_T_DROPPED_ITEM_TYPE(v_table_count).NOTIFY_USER := lplanner_code ; /* commented 'MFG' */
1412
1413 end if; /* check custom recipient */
1414
1415
1416
1417 -- rkaza. bug 4315973. 08/24/2005.
1418 -- Hold ato line for dropped items when profile is set to do not
1419 -- create item. Removed aps_version restriction.
1420
1421 oe_debug_pub.add('create_bom_ml: ' || 'fetching information for apply hold on lineid '|| to_char(pLineId) ,2);
1422 oe_debug_pub.add('create_bom_ml: ' || 'going to apply hold on lineid '|| to_char(pLineId) ,2);
1423
1424 cto_utility_pk.apply_create_config_hold( l_ato_line_id, l_header_id, xReturnStatus, xMsgCount, xMsgData ) ;
1425
1426
1427 l_token(1).token_name := 'ORDER_NUM';
1428 l_token(1).token_value := v_order_number;
1429 l_token(2).token_name := 'CONFIG_NAME';
1430 l_token(2).token_value := v_problem_config;
1431 l_token(3).token_name := 'ORG';
1432 l_token(3).token_value := v_error_org;
1433 l_token(4).token_name := 'ERROR_MESSAGE';
1434 l_token(4).token_value := v_ac_message_string ;
1435
1436 cto_msg_pub.cto_message('BOM','CTO_DO_NOT_CREATE_ITEM',l_token);
1437
1438 -- Bugfix 4084568: Adding message for model line on Hold.
1439
1440 cto_msg_pub.cto_message('BOM','CTO_MODEL_LINE_EXCPN_HOLD');
1441
1442
1443
1444 raise fnd_api.g_exc_error;
1445
1446 end if; /* create item profile condition */
1447
1448 end if; /* missed lines cursor condition */
1449
1450 end if; /* Preconfigure / Autoconfigure condition */
1451
1452 close mismatched_items;
1453
1454 lStmtNum := 116;
1455
1456 -- 3543547 Replace lItmBillId with lComItmBillId
1457 delete from bom_inventory_comps_interface
1458 where bill_sequence_id = lComItmBillId
1459 and batch_id = cto_msutil_pub.bom_batch_id;
1460
1461 lCnt := sql%rowcount;
1462 IF PG_DEBUG <> 0 THEN
1463 oe_debug_pub.add('Deleted from bici, rows::'||lCnt);
1464 END IF;
1465
1466 --Bugfix 11056452
1467 delete from bom_bill_of_mtls_interface
1468 where bill_sequence_id = lComItmBillId
1469 and batch_id = cto_msutil_pub.bom_batch_id;
1470
1471 lCnt := sql%rowcount;
1472 IF PG_DEBUG <> 0 THEN
1473 oe_debug_pub.add('Deleted from bmi, rows::'||lCnt);
1474 END IF;
1475
1476
1477 EXCEPTION /* exception for stmt 112 , 115 and 116 */
1478
1479 when others then
1480 IF PG_DEBUG <> 0 THEN
1481 oe_debug_pub.add ('Failed in stmt ' || lStmtNum || ' with error: '||sqlerrm);
1482 END IF;
1483 raise fnd_api.g_exc_error;
1484 END ;
1485
1486 /* Bugfix 2986192 ends here */
1487
1488
1489
1490 else
1491
1492
1493 lStmtNum := 125;
1494 lStatus := CTO_CONFIG_BOM_PK.create_bom_ml(
1495 pModelId => pModelId,
1496 pConfigId => pConfigId,
1497 pOrgId => lNextRec.organization_id,
1498 pLineId => pLineId,
1499 xBillId => lCfgBillId,
1500 xErrorMessage => lXErrorMessage,
1501 xMessageName => lXMessageName,
1502 xTableName => lXTableName);
1503
1504
1505
1506
1507
1508
1509 IF PG_DEBUG <> 0 THEN
1510 oe_debug_pub.add('create_in_src_orgs: '
1511 || 'Returned from Create_bom_ml with status: '
1512 || to_char(lStatus), 1);
1513 END IF;
1514
1515 if (lStatus <> 1) then
1516
1517 /*----------------------------+
1518 BOM Creation failed
1519 +----------------------------*/
1520 IF PG_DEBUG <> 0 THEN
1521 oe_debug_pub.add('create_in_src_orgs: ' || ' Failed in Create_bom.', 1);
1522 END IF;
1523
1524 /* Clean up bom_inventory_comps_interface */
1525 delete from bom_inventory_comps_interface
1526 where bill_sequence_id = lCfgBillId;
1527
1528 lCnt := sql%rowcount;
1529 IF PG_DEBUG <> 0 THEN
1530 oe_debug_pub.add('Deleted from bici, rows::'||lCnt);
1531 END IF;
1532
1533 --Bugfix 11056452
1534 delete from bom_bill_of_mtls_interface
1535 where bill_sequence_id = lCfgBillId;
1536
1537 lCnt := sql%rowcount;
1538 IF PG_DEBUG <> 0 THEN
1539 oe_debug_pub.add('Deleted from bmi, rows::'||lCnt);
1540 END IF;
1541
1542
1543 if( lStatus = -1) then /* add a message for unexpected errors(-1), expected errors(0) already have a message */
1544 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_BOM_ERROR');
1545 end if;
1546
1547 raise fnd_api.g_exc_error;
1548
1549 end if;
1550
1551
1552
1553
1554
1555 v_bom_created := v_bom_created + 1 ; /* increment bom created variable */
1556
1557
1558
1559 lStmtNum := 130;
1560 lStatus := CTO_CONFIG_ROUTING_PK.create_routing_ml(
1561 pModelId => pModelId ,
1562 pConfigId => pConfigId,
1563 pCfgBillId => lCfgBillId,
1564 pOrgId => lNextRec.organization_id,
1565 pLineId => pLineId,
1566 pFlowCalc => pFlowCalc,
1567 xRtgId => lCfgRtgId,
1568 xErrorMessage => lXErrorMessage,
1569 xMessageName => lXMessageName,
1570 xTableName => lXTableName );
1571
1572 IF PG_DEBUG <> 0 THEN
1573 oe_debug_pub.add('create_in_src_orgs: '
1574 || 'Returned from Create_routing_ml with status: '
1575 || to_char(lStatus), 1);
1576 END IF;
1577
1578
1579 if (lStatus <> 1) then
1580
1581 /*----------------------------+
1582 Routing Creation failed
1583 +----------------------------*/
1584 IF PG_DEBUG <> 0 THEN
1585 oe_debug_pub.add('create_in_src_orgs: ' || ' Failed in create_routing.');
1586 END IF;
1587
1588 /* Clean up bom_inventory_comps_interface */
1589 delete from bom_inventory_comps_interface
1590 where bill_sequence_id = lCfgBillId;
1591
1592 lCnt := sql%rowcount;
1593 IF PG_DEBUG <> 0 THEN
1594 oe_debug_pub.add('Deleted from bici, rows::'||lCnt);
1595 END IF;
1596
1597 --Bugfix 11056452
1598 delete from bom_bill_of_mtls_interface
1599 where bill_sequence_id = lCfgBillId;
1600
1601 lCnt := sql%rowcount;
1602 IF PG_DEBUG <> 0 THEN
1603 oe_debug_pub.add('Deleted from bmi, rows::'||lCnt);
1604 END IF;
1605
1606 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_ROUTING_ERROR');
1607 raise fnd_api.g_exc_error;
1608
1609 end if;
1610
1611
1612 if (lCfgBillId > 0) then
1613 lStmtNum := 135;
1614 lStatus := CTO_CONFIG_BOM_PK.create_bom_data_ml(
1615 pModelId,
1616 pConfigId,
1617 lNextRec.organization_id,
1618 lCfgBillId,
1619 lXErrorMessage,
1620 lXMessageName,
1621 lXTableName);
1622
1623 IF PG_DEBUG <> 0 THEN
1624 oe_debug_pub.add('create_in_src_orgs: '
1625 || 'Returned from Create_bom with status: '
1626 || to_char(lStatus), 1);
1627 END IF;
1628
1629 if (lStatus <> 1) then
1630
1631 IF PG_DEBUG <> 0 THEN
1632 oe_debug_pub.add('create_in_src_orgs: '
1633 || ' Failed in Create_bom_data', 1);
1634 END IF;
1635
1636 /* Clean up bom_inventory_comps_interface */
1637 delete from bom_inventory_comps_interface
1638 where bill_sequence_id = lCfgBillId;
1639
1640 lCnt := sql%rowcount;
1641 IF PG_DEBUG <> 0 THEN
1642 oe_debug_pub.add('Deleted from bici, rows::'||lCnt);
1643 END IF;
1644
1645 --Bugfix 11056452
1646 delete from bom_bill_of_mtls_interface
1647 where bill_sequence_id = lCfgBillId;
1648
1649 lCnt := sql%rowcount;
1650 IF PG_DEBUG <> 0 THEN
1651 oe_debug_pub.add('Deleted from bmi, rows::'||lCnt);
1652 END IF;
1653
1654 if( lXMessageName is not null ) then
1655 xMsgData := lXMessageName ;
1656 -- cto_msg_pub.cto_message('BOM', lXMessageName );
1657 else
1658 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_BOM_ERROR');
1659 end if;
1660
1661
1662
1663
1664 IF PG_DEBUG <> 0 THEN
1665 oe_debug_pub.add('added club_comp_error ' , 1) ;
1666 end if ;
1667
1668 raise fnd_api.g_exc_error;
1669
1670 end if;
1671
1672 end if; -- end lCfgBillId > 0
1673
1674
1675 end if; -- end check config bom
1676
1677 else
1678 -- Added by Renga Kannan to handle the exception
1679 IF PG_DEBUG <> 0 THEN
1680 oe_debug_pub.add('create_in_src_orgs: '
1681 || 'There is no bill for this model in this org',1);
1682
1683 oe_debug_pub.add('create_in_src_orgs: '
1684 || 'Model id :'||to_char(pModelId),1);
1685 oe_debug_pub.add('Org id ;'||to_char(lNextRec.organization_id),1);
1686 END IF;
1687
1688 /*
1689
1690
1691
1692 ** Warning **
1693
1694 ** Achtung **
1695
1696 ** Model BOM does not exist should not be treated as an error
1697 **
1698 ** Case: Specific Org
1699 ** BOM is created only in the end manufacturing org
1700 **
1701 ** Case: All Org
1702 ** BOM is created in all orgs where the model bom exists
1703 **
1704 ** In either case the error will be caught if the bom
1705 ** was not created even in a single org.
1706
1707
1708 cto_msg_pub.cto_message('BOM','CTO_BOM_NOT_DEFINED');
1709 -- bugfix 2294708: Replaced msg CTO_CREATE_BOM_ERROR with more specific
1710 -- error CTO_BOM_NOT_DEFINED.
1711
1712 raise fnd_api.g_exc_error;
1713
1714
1715
1716
1717
1718 */
1719
1720
1721
1722 end if; -- end check model bom
1723
1724
1725 else /* create_config_bom = 'N' */
1726
1727 IF PG_DEBUG <> 0 THEN
1728 oe_debug_pub.add('create_in_src_orgs: '
1729 || 'create_config_bom parameter is set to N in this org',1);
1730
1731 oe_debug_pub.add('create_in_src_orgs: '
1732 || 'Model id :'||to_char(pModelId),1);
1733 oe_debug_pub.add('Org id ;'||to_char(lNextRec.organization_id),1);
1734 END IF;
1735
1736 -- bugfix 2294708: Replaced msg CTO_CREATE_BOM_ERROR with more specific
1737 -- error CTO_BOM_NOT_DEFINED.
1738
1739
1740
1741
1742 end if ;
1743
1744
1745 end loop;
1746
1747
1748 if( v_bom_created = 0 and v_config_bom_exists = 0 ) then
1749
1750 select concatenated_segments into v_model_item_name
1751 from mtl_system_items_kfv
1752 where inventory_item_id = pModelId
1753 and rownum = 1 ;
1754
1755
1756 l_token1(1).token_name := 'MODEL_NAME';
1757 l_token1(1).token_value := v_model_item_name ;
1758
1759 cto_msg_pub.cto_message('BOM','CTO_NO_BOM_CREATED_IN_ANY_ORGS', l_token1 ); -- Bug 3349142
1760 raise fnd_api.g_exc_error;
1761
1762 end if ;
1763
1764
1765
1766
1767 cto_msg_pub.count_and_get
1768 ( p_msg_count => xMsgCount
1769 , p_msg_data => xMsgData
1770 );
1771
1772
1773 EXCEPTION
1774
1775 WHEN fnd_api.g_exc_error THEN
1776 IF PG_DEBUG <> 0 THEN
1777 oe_debug_pub.add('create_in_src_orgs: '
1778 || 'expected error::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
1779 END IF;
1780
1781 xReturnStatus := fnd_api.g_ret_sts_unexp_error;
1782 -- Get message count and data
1783 xReturnStatus := fnd_api.g_ret_sts_error;
1784 -- Get message count and data
1785
1786
1787 cto_msg_pub.count_and_get
1788 ( p_msg_count => xMsgCount
1789 , p_msg_data => xMsgData
1790 );
1791
1792 IF PG_DEBUG <> 0 THEN
1793 oe_debug_pub.add('create_in_src_orgs: ' || xMsgData , 1 ) ;
1794 oe_debug_pub.add('create_in_src_orgs: ' || xMsgCount , 1 ) ;
1795
1796 END IF;
1797
1798 WHEN fnd_api.g_exc_unexpected_error THEN
1799
1800
1801 xReturnStatus := fnd_api.g_ret_sts_unexp_error ;
1802 -- Get message count and data
1803
1804
1805 cto_msg_pub.count_and_get
1806 ( p_msg_count => xMsgCount
1807 , p_msg_data => xMsgData
1808 );
1809
1810 WHEN OTHERS then
1811 IF PG_DEBUG <> 0 THEN
1812 oe_debug_pub.add('create_in_src_orgs: '
1813 || 'create_in_src_orgs::others::'||to_char(lStmtNum)
1814 ||'::'||sqlerrm, 1);
1815 END IF;
1816
1817 xReturnStatus := fnd_api.g_ret_sts_unexp_error;
1818 -- Get message count and data
1819
1820 cto_msg_pub.count_and_get
1821 ( p_msg_count => xMsgCount
1822 , p_msg_data => xMsgData
1823 );
1824
1825
1826 END create_in_src_orgs;
1827
1828
1829
1830 /*------------------------------------------------+
1831 This function is to send new information to
1832 ATP after the config BOM has been created
1833 +------------------------------------------------*/
1834
1835 function update_atp( pLineId in number,
1836 xErrorMessage out NOCOPY varchar2,
1837 xMessageName out NOCOPY varchar2,
1838 xTableName out NOCOPY varchar2)
1839 return integer
1840 is
1841
1842 p_atp_table MRP_ATP_PUB.ATP_Rec_Typ;
1843 l_smc_table MRP_ATP_PUB.ATP_Rec_Typ;
1844 l_instance_id integer := -1;
1845 l_session_id number := 101;
1846 l_atp_table MRP_ATP_PUB.ATP_Rec_Typ;
1847 l_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
1848 l_atp_period MRP_ATP_PUB.ATP_Period_Typ;
1849 l_atp_details MRP_ATP_PUB.ATP_Details_Typ;
1850
1851 l_return_status VARCHAR2(1);
1852 l_msg_count number;
1853 l_msg_data varchar2(200);
1854
1855 atp_error exception;
1856
1857 lStatus varchar2(1);
1858 lStmt number;
1859 i number;
1860 temp number := null;
1861 temp1 date := null;
1862
1863 begin
1864 /*-----------------------------------------------------+
1865 Prepare initial input record for atp
1866 Copy model row information to p_atp_table
1867 Although, we are still using model and options info
1868 in oe_order_lines, call to get_bom_mandatory_comps
1869 will ensure that we get the latest picture of
1870 mandatory comps.
1871 This has changed to make a call to
1872 bom_mandatory_components instead.
1873 +-----------------------------------------------------*/
1874
1875 lStmt := 50;
1876 select oel.inventory_item_id,
1877 oel.ship_from_org_id,
1878 oel.line_id,
1879 oel.ordered_quantity,
1880 oel.order_quantity_uom,
1881 oel.request_date,
1882 oel.demand_class_code,
1883 temp, -- calling module
1884 temp, -- customer_id
1885 temp, -- customer_site_id
1886 temp, -- destination_time_zone
1887 oel.schedule_arrival_date,
1888 temp1, -- latest acceptable_date
1889 oel.delivery_lead_time, -- delivery lead time
1890 temp, -- Freight_Carrier
1891 temp, -- Ship_Method
1892 temp, --Ship_Set_Name
1893 temp, -- Arrival_Set_Name
1894 1, -- Override_Flag
1895 temp, -- Action
1896 temp1, -- Ship_date
1897 temp, -- available_quantity
1898 temp, -- requested_date_quantity
1899 temp1, -- group_ship_date
1900 temp1, -- group_arrival_date
1901 temp, -- vendor_id
1902 temp, -- vendor_site_id
1903 temp, -- insert_flag
1904 temp, -- error_code
1905 temp -- Message
1906 bulk collect into
1907 p_atp_table.Inventory_Item_Id ,
1908 p_atp_table.Source_Organization_Id ,
1909 p_atp_table.Identifier ,
1910 p_atp_table.Quantity_Ordered ,
1911 p_atp_table.Quantity_UOM ,
1912 p_atp_table.Requested_Ship_Date ,
1913 p_atp_table.Demand_Class ,
1914 p_atp_table.Calling_Module ,
1915 p_atp_table.Customer_Id ,
1916 p_atp_table.Customer_Site_Id ,
1917 p_atp_table.Destination_Time_Zone ,
1918 p_atp_table.Requested_Arrival_Date ,
1919 p_atp_table.Latest_Acceptable_Date ,
1920 p_atp_table.Delivery_Lead_Time ,
1921 p_atp_table.Freight_Carrier ,
1922 p_atp_table.Ship_Method ,
1923 p_atp_table.Ship_Set_Name ,
1924 p_atp_table.Arrival_Set_Name ,
1925 p_atp_table.Override_Flag ,
1926 p_atp_table.Action ,
1927 p_atp_table.Ship_Date ,
1928 p_atp_table.Available_Quantity ,
1929 p_atp_table.Requested_Date_Quantity ,
1930 p_atp_table.Group_Ship_Date ,
1931 p_atp_table.Group_Arrival_Date ,
1932 p_atp_table.Vendor_Id ,
1933 p_atp_table.Vendor_Site_Id ,
1934 p_atp_table.Insert_Flag ,
1935 p_atp_table.Error_Code ,
1936 p_atp_table.Message
1937 from oe_order_lines_all oel,
1938 oe_order_lines_all oel1,
1939 mtl_system_items msi
1940 where msi.inventory_item_id = oel.inventory_item_id
1941 and msi.organization_id = oel.ship_from_org_id
1942 and msi.bom_item_type = 1
1943 and oel.line_id = pLineId
1944 --and oel.item_type_code = 'MODEL'
1945 and oel1.item_type_code = 'CONFIG'
1946 --and oel1.top_model_line_id = pLineId
1947 and oel1.ato_line_id = pLineId
1948 and oel1.link_to_line_id = pLineId
1949 and oel1.ordered_quantity > 0 ;
1950
1951 IF PG_DEBUG <> 0 THEN
1952 oe_debug_pub.add('update_atp: ' || ' Line Id ' || p_atp_table.identifier(1));
1953
1954 oe_debug_pub.add('update_atp: ' || ' Inventory Id ' || p_atp_table.inventory_item_id(1));
1955
1956 oe_debug_pub.add('update_atp: ' || ' Req Date ' || p_atp_table.requested_ship_date(1));
1957
1958 oe_debug_pub.add('update_atp: ' || ' qty ' || p_atp_table.quantity_ordered(1));
1959 END IF;
1960
1961
1962 /*--------------------------------------+
1963 Get Mandatory components
1964 +--------------------------------------*/
1965
1966 /* lstatus := cto_config_item_pk.get_Bom_Mandatory_comps(
1967 p_atp_table ,
1968 l_smc_table ,
1969 xErrorMessage ,
1970 xMessageName ,
1971 xTableName );
1972 */
1973
1974 lstatus := cto_config_item_pk.Get_Mandatory_Components(
1975 p_atp_table, --p_ship_set in MRP_ATP_PUB.ATP_Rec_Typ
1976 null, --p_organization_id in number default null (passing null because OM)
1977 null, --p_inventory_item_id in number default null (passing null because OM)
1978 l_smc_table, --p_sm_rec out MRP_ATP_PUB.ATP_Rec_Typ
1979 xErrorMessage,
1980 xMessageName,
1981 xTableName );
1982
1983
1984 i := l_smc_table.inventory_item_id.FIRST;
1985
1986 if i is not null then
1987
1988 IF PG_DEBUG <> 0 THEN
1989 oe_debug_pub.add('update_atp: ' || 'From output record ---> ',1);
1990 END IF;
1991
1992 while i is not null
1993 loop
1994
1995 IF PG_DEBUG <> 0 THEN
1996 oe_debug_pub.add('update_atp: ' || ' Line Id '
1997 || l_smc_table.identifier(i));
1998
1999 oe_debug_pub.add('update_atp: ' || ' Inventory Id '
2000 || l_smc_table.inventory_item_id(i));
2001
2002 oe_debug_pub.add('update_atp: ' || ' Req Date '
2003 || l_smc_table.requested_ship_date(i));
2004
2005 oe_debug_pub.add('update_atp: ' || ' qty '
2006 || l_smc_table.quantity_ordered(i));
2007 END IF;
2008
2009 i := l_smc_table.inventory_item_id.NEXT(i);
2010 end loop;
2011
2012 IF PG_DEBUG <> 0 THEN
2013 oe_debug_pub.add('update_atp: ' || 'Calling ATP ---> ',1);
2014 END IF;
2015
2016 /*----------------------------+
2017 Call ATP
2018 +----------------------------*/
2019
2020 MRP_ATP_PUB.Call_ATP(
2021 l_session_id,
2022 l_smc_table,
2023 l_atp_table,
2024 l_atp_supply_demand,
2025 l_atp_period,
2026 l_atp_details,
2027 l_return_status,
2028 l_msg_data,
2029 l_msg_count);
2030
2031 IF PG_DEBUG <> 0 THEN
2032 oe_debug_pub.add('update_atp: ' || 'ATP returned ' || l_return_status);
2033 END IF;
2034
2035 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2036 or l_return_status = FND_API.G_RET_STS_ERROR ) then
2037 raise atp_error;
2038 END IF;
2039 else
2040 IF PG_DEBUG <> 0 THEN
2041 oe_debug_pub.add('update_atp: ' || 'No Mandatory components for ATP found' );
2042 END IF;
2043
2044 end if;
2045
2046 return (1);
2047
2048
2049 exception
2050
2051 when atp_error then
2052 xErrormessage := 'update_atp:'||to_char(lStmt)||':'||' ATP API returned Error';
2053 xMessageName := 'CTO_CREATE_BOM_ERROR';
2054 return(0);
2055
2056 when others then
2057 xErrorMessage := 'update_atp:'||to_char(lStmt)||':'||substrb(sqlerrm,1,150) ;
2058 xMessageName := 'CTO_CREATE_BOM_ERROR';
2059 return(0);
2060
2061 end update_atp;
2062
2063
2064
2065 FUNCTION is_item_transacted( p_inventory_item_id NUMBER
2066 , p_organization_id NUMBER
2067 , p_cost_type_id NUMBER )
2068 Return BOOLEAN IS
2069 Updateable VARCHAR2(10) := null ;
2070 RetVal BOOLEAN;
2071 intransit_count NUMBER;
2072
2073 Cursor Check_Updateable is
2074 Select 'YES'
2075 From MTL_MATERIAL_TRANSACTIONS t
2076 Where Inventory_Item_Id = p_inventory_item_id
2077 And Exists
2078 (Select 'all these org have the org as costing org'
2079 From MTL_PARAMETERS
2080 Where Cost_Organization_Id = p_organization_id
2081 AND Organization_Id = t.Organization_Id);
2082
2083 Cursor Check_Updateable_2 is
2084 Select 'YES'
2085 From MTL_MATERIAL_TRANSACTIONS_TEMP t
2086 Where Inventory_Item_Id = p_inventory_item_id
2087 And Exists
2088 (Select 'all these org have the org as costing org'
2089 From MTL_PARAMETERS
2090 Where Cost_Organization_Id = p_organization_id
2091 AND Organization_Id = t.Organization_Id);
2092
2093 BEGIN
2094 -- If we are dealing with a frozon cost type, it is only updateable when
2095 -- there does not exist any transactions.
2096
2097 IF ( p_cost_type_id = 1) THEN
2098 IF (Updateable is NULL) THEN
2099 Open Check_Updateable;
2100 Fetch Check_Updateable into Updateable;
2101 Close Check_Updateable;
2102
2103 IF (Updateable is Null) THEN
2104
2105 Open Check_Updateable_2;
2106 Fetch Check_Updateable_2 into Updateable;
2107 Close Check_Updateable_2;
2108 END IF;
2109
2110 IF (Updateable is NULL) THEN
2111
2112 select count(*)
2113 into intransit_count
2114 from mtl_supply m
2115 where m.item_id = p_inventory_item_id
2116 and m.intransit_owning_org_id = p_organization_id
2117 and m.to_organization_id = p_organization_id ;
2118 IF (intransit_count > 0) THEN
2119 Updateable := 'YES';
2120 END IF;
2121 END IF;
2122
2123 END IF;
2124 IF (Updateable = 'YES') THEN
2125 -- fnd_message.Set_Name('BOM', 'CST_ITEM_USED_IN_TXN');
2126 RetVal := TRUE;
2127 ELSE
2128 IF PG_DEBUG <> 0 THEN
2129 oe_debug_pub.add( ' is_item_transacted is null -> true ' ) ;
2130 END IF;
2131
2132 RetVal := FALSE ;
2133 END IF;
2134
2135 ELSE
2136 IF PG_DEBUG <> 0 THEN
2137 oe_debug_pub.add( ' cost type id not 1 ' ) ;
2138 END IF;
2139
2140 RetVal := FALSE ;
2141 END IF;
2142
2143 IF PG_DEBUG <> 0 THEN
2144
2145 if( RetVal = TRUE ) then
2146 oe_debug_pub.add( ' is_item_transacted is true ' ) ;
2147 elsif( RetVal = False ) then
2148 oe_debug_pub.add( ' is_item_transacted is false' ) ;
2149 elsif( RetVal is null ) then
2150 oe_debug_pub.add( ' is_item_transacted is null ' ) ;
2151 end if ;
2152 END IF;
2153
2154
2155
2156 Return RetVal;
2157
2158
2159
2160 END is_item_transacted ;
2161
2162
2163
2164
2165
2166
2167
2168 /*-------------------------------------------------------------+
2169 Name : override_bcso_cost_rollup_flag
2170 This procedure updates cost_rollup_flag in bcso to avoid cost rollup.
2171 +-------------------------------------------------------------*/
2172 procedure override_bcso_cost_rollup(
2173 pLineId in number, -- Current Model Line ID
2174 pModelId in number,
2175 pConfigId in number,
2176 p_cost_organization_id in number,
2177 p_organization_id in number,
2178 p_group_reference_id in number,
2179 xReturnStatus out NOCOPY varchar2,
2180 xMsgCount out NOCOPY number,
2181 xMsgData out NOCOPY varchar2
2182 )
2183
2184 IS
2185
2186 lStmtNum number;
2187 lStatus number;
2188 lItmBillId number;
2189 lCfgBillId number;
2190 lCfgRtgId number;
2191 xBillId number;
2192 lXErrorMessage varchar2(100);
2193 lXMessageName varchar2(100);
2194 lXTableName varchar2(100);
2195
2196 v_primary_cost_method mtl_parameters.primary_cost_method%type := null ;
2197 v_cto_cost cst_item_costs.item_cost%type := null ;
2198 v_cto_cost_xudc cst_item_costs.item_cost%type := null ;
2199 v_valuation_cost cst_item_costs.item_cost%type := null ;
2200 v_buy_cost cst_item_costs.item_cost%type := null ;
2201
2202 v_organization_type bom_cto_src_orgs.organization_type%type := null ;
2203
2204 v_cto_cost_type_id cst_item_costs.cost_type_id%type ;
2205 v_buy_cost_type_id cst_item_costs.cost_type_id%type ;
2206 v_rolledup_cost_count number ;
2207 v_rolledup_cost number ;
2208 lBuyCostType varchar2(30);
2209
2210 v_item_transacted boolean := FALSE ;
2211
2212 l_missed_item_id number;
2213 l_missed_item varchar2(50);
2214 l_config_item varchar2(50);
2215 l_model varchar2(50);
2216 l_missed_line_number varchar2(50);
2217 l_order_number number := 0;
2218 l_token CTO_MSG_PUB.token_tbl;
2219 lcreate_item number;
2220 lorg_code varchar2(3);
2221
2222
2223 /* 2986190 End declaration */
2224
2225 BEGIN
2226
2227 xReturnStatus := fnd_api.g_ret_sts_success;
2228
2229 -- check if model bom exists in src org
2230
2231 lStmtNum := 10;
2232 IF PG_DEBUG <> 0 THEN
2233 oe_debug_pub.add('create_in_src_orgs: ' || 'In create_in_src_orgs. Item: ' ||
2234 to_char(pConfigId) || '. Costing Org ' || -- 3116778
2235 to_char(p_cost_organization_id) || '. Source Org ' || -- 3116778
2236 to_char(p_organization_id), 1);
2237 END IF;
2238
2239
2240
2241
2242
2243 lStmtNum := 20;
2244
2245 v_primary_cost_method := null ;
2246 v_cto_cost := null ;
2247 v_cto_cost_xudc := null ;
2248 v_valuation_cost := null ;
2249 v_buy_cost := null ;
2250 v_organization_type := null ;
2251
2252
2253 lStmtNum := 25;
2254
2255 begin
2256
2257 select mp1.primary_cost_method into v_primary_cost_method
2258 from mtl_parameters mp1
2259 where mp1.organization_id = p_cost_organization_id ; -- 3116778
2260
2261 exception
2262
2263 when others then
2264 raise fnd_api.g_exc_error;
2265
2266 end ;
2267
2268 lStmtNum := 26;
2269 begin
2270
2271 select cost_type_id into v_cto_cost_type_id
2272 from cst_cost_types
2273 where cost_type = 'CTO' ;
2274
2275
2276
2277
2278 exception
2279 when no_data_found then
2280
2281 cto_msg_pub.cto_message('BOM','CTO_COST_NOT_FOUND');
2282 raise FND_API.G_EXC_ERROR;
2283
2284 when others then
2285
2286 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2287
2288
2289 end;
2290
2291
2292
2293 lStmtNum := 27;
2294
2295 lBuyCostType := FND_PROFILE.VALUE('CTO_BUY_COST_TYPE');
2296
2297
2298 if( lBuyCostType is not null ) then
2299 begin
2300 select cost_type_id into v_buy_cost_type_id
2301 from cst_cost_types
2302 where cost_type = lBuyCostType ;
2303
2304 IF PG_DEBUG <> 0 THEN
2305 oe_debug_pub.add('Buy Cost Type id ::'|| v_buy_cost_type_id , 2);
2306 END IF;
2307
2308 exception
2309 when no_data_found then
2310
2311 cto_msg_pub.cto_message('BOM','CTO_BUY_COST_NOT_FOUND');
2312 raise FND_API.G_EXC_ERROR;
2313
2314 when others then
2315
2316 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2317
2318
2319 end;
2320
2321 else
2322 v_buy_cost_type_id := v_cto_cost_type_id ;
2323
2324 IF PG_DEBUG <> 0 THEN
2325 oe_debug_pub.add('defaulting buy cost = cto cost ' , 2);
2326 END IF;
2327
2328 end if ;
2329
2330
2331 lStmtNum := 30;
2332 begin
2333
2334 select item_cost into v_cto_cost from cst_item_costs
2335 where inventory_item_id = pConfigId
2336 and organization_id = p_cost_organization_id -- 3116778
2337 and cost_type_id = v_cto_cost_type_id ;
2338
2339 lStmtNum := 32;
2340 select sum(item_cost) into v_cto_cost_xudc from cst_item_cost_details
2341 where inventory_item_id = pConfigId
2342 and organization_id = p_cost_organization_id -- 3116778
2343 and cost_type_id = v_cto_cost_type_id
2344 and rollup_source_type = 3 ; -- bugfix 2808704
2345
2346
2347 IF PG_DEBUG <> 0 THEN
2348 oe_debug_pub.add('cto cost ' || v_cto_cost ) ;
2349 oe_debug_pub.add('cto cost xudc ' || v_cto_cost_xudc ) ;
2350 END IF;
2351
2352
2353 exception
2354 when no_data_found then
2355 v_cto_cost := null ;
2356 v_cto_cost_xudc := null ;
2357
2358 when others then
2359
2360 raise fnd_api.g_exc_error;
2361
2362 end ;
2363
2364
2365
2366
2367
2368 lStmtNum := 35;
2369 begin
2370
2371 select item_cost into v_valuation_cost from cst_item_costs
2372 where inventory_item_id = pConfigId
2373 and organization_id = p_cost_organization_id -- 3116778
2374 and cost_type_id = v_primary_cost_method ;
2375
2376
2377 exception
2378 when no_data_found then
2379
2380 v_valuation_cost := null ;
2381
2382 when others then
2383
2384 raise fnd_api.g_exc_error;
2385
2386 end ;
2387
2388
2389 v_rolledup_cost_count := null ;
2390 v_rolledup_cost_count := null ;
2391
2392
2393 /* check whether rolledup cost exists in frozen cost in standard costing org */
2394 if( v_primary_cost_method = 1) then
2395 begin
2396 select count(*) , sum(item_cost) into v_rolledup_cost_count, v_rolledup_cost
2397 from cst_item_cost_details
2398 where inventory_item_id = pConfigId
2399 and organization_id = p_cost_organization_id -- 3116778l
2400 and cost_type_id = v_primary_cost_method
2401 and rollup_source_type = 3 ;
2402
2403 exception
2404 when others then
2405
2406 raise fnd_api.g_exc_error ;
2407
2408 end ;
2409 end if ;
2410
2411
2412
2413
2414
2415
2416
2417 IF PG_DEBUG <> 0 THEN
2418 oe_debug_pub.add('going for stmt 40 ' || pConfigId
2419 || ' org ' || p_organization_id
2420 || ' cost org ' || p_cost_organization_id -- 3116778
2421 || ' pri ' || v_primary_cost_method
2422 || ' buy ' || v_buy_cost_type_id
2423 || ' line ' || pLineId , 1);
2424 END IF;
2425
2426
2427
2428
2429 lStmtNum := 40;
2430
2431 if( lBuyCostType is not null ) then
2432 begin
2433 select item_cost into v_buy_cost from cst_item_costs
2434 where inventory_item_id = pConfigId
2435 and organization_id = p_cost_organization_id -- 3116778
2436 and cost_type_id = v_buy_cost_type_id ;
2437
2438
2439
2440
2441 IF PG_DEBUG <> 0 THEN
2442 oe_debug_pub.add('v_buy_cost ' || v_buy_cost , 1);
2443 oe_debug_pub.add('org ' || p_organization_id , 1);
2444 oe_debug_pub.add('cost org ' || p_cost_organization_id , 1); -- 3116778
2445 oe_debug_pub.add('cost id ' || v_buy_cost_type_id, 1);
2446 END IF;
2447
2448 exception
2449 when no_data_found then
2450
2451 v_buy_cost := null ;
2452
2453 IF PG_DEBUG <> 0 THEN
2454 oe_debug_pub.add('v_buy_cost null ' , 1);
2455 oe_debug_pub.add('org ' || p_organization_id , 1);
2456 oe_debug_pub.add('cost org ' || p_cost_organization_id , 1); -- 3116778
2457 END IF;
2458
2459 when others then
2460
2461 raise fnd_api.g_exc_error;
2462
2463 end ;
2464
2465
2466 else
2467
2468 IF PG_DEBUG <> 0 THEN
2469 oe_debug_pub.add('v_buy_cost null as buy cost profile is not set ' , 1);
2470 END IF;
2471
2472 v_buy_cost := null ;
2473
2474 end if;
2475
2476
2477
2478 IF PG_DEBUG <> 0 THEN
2479 oe_debug_pub.add('going for stmt 45 ' , 1);
2480 END IF;
2481
2482
2483 lStmtNum := 45;
2484
2485 IF PG_DEBUG <> 0 THEN
2486 oe_debug_pub.add('line ' || pLineId , 1);
2487 END IF;
2488
2489
2490
2491
2492 /*
2493 bug 3877317
2494 query fixed to get v_organization_type.
2495 added new too_many_rows exception handler due to query being dependent only on organization_id
2496 */
2497 begin
2498 select nvl( organization_type , 1 ) into v_organization_type
2499 from bom_cto_src_orgs
2500 where line_id = pLineId
2501 and cost_rollup = 'Y'
2502 and organization_id = p_organization_id ; -- added for bug 3877317 copied from fp.
2503 -- In 11.5.10 there could be multiple manufacturing orgs.
2504
2505 exception
2506 when too_many_rows then -- added for bug 3877317
2507 IF PG_DEBUG <> 0 THEN
2508 oe_debug_pub.add('others ' || SQLERRM , 1);
2509 oe_debug_pub.add('going to check whether make organization for too_many_rows ' , 1);
2510 END IF;
2511
2512 /*
2513 BUG 3877317
2514 SQL added in too_many_rows exception handler to check whether p_organization_id is make org.
2515 query is now based only on organization_id and hence needs to check whether it is manufacturing org
2516 using the following sql. The code to check whether cost rollup should not be performed needs to know
2517 whether the organization is make organization. In 11.5.9 this check was not required as create_bom flag
2518 was set only for manufacturing org.
2519 */
2520
2521 begin
2522 select organization_type into v_organization_type
2523 from bom_cto_src_orgs
2524 where line_id = pLineId
2525 and rcv_org_id = p_organization_id
2526 and organization_id = p_organization_id
2527 and organization_type = '2'
2528 and cost_rollup = 'Y' ;
2529
2530 IF PG_DEBUG <> 0 THEN
2531 oe_debug_pub.add( p_organization_id || ' is make organization ' , 1);
2532 END IF;
2533 exception
2534 when no_data_found then
2535 v_organization_type := 1 ;
2536
2537
2538 end ;
2539 -- end of bug 3877317 to check whether p_organization_id is make org.
2540
2541
2542 when others then
2543 IF PG_DEBUG <> 0 THEN
2544 oe_debug_pub.add('others ' || SQLERRM , 1);
2545 oe_debug_pub.add('defaulting organization type = 4 ' , 1);
2546 END IF;
2547
2548 v_organization_type := 4 ;
2549 /* cost rollup is 'N' for child models of drop ship or buy */
2550
2551
2552 -- raise fnd_api.g_exc_error;
2553
2554 end ;
2555
2556
2557
2558
2559 IF PG_DEBUG <> 0 THEN
2560 oe_debug_pub.add('organization_type ' || v_organization_type , 1);
2561
2562 oe_debug_pub.add('valuation cost ' || v_valuation_cost , 1);
2563 oe_debug_pub.add('primary cost method ' || v_primary_cost_method , 1);
2564 oe_debug_pub.add('cto cost ' || v_cto_cost ) ;
2565 oe_debug_pub.add('cto cost xudc ' || v_cto_cost_xudc ) ;
2566 oe_debug_pub.add('buy cost ' || v_buy_cost ) ;
2567
2568
2569 oe_debug_pub.add('going for stmt 50 ' , 1);
2570 END IF;
2571
2572
2573 lStmtNum := 50;
2574
2575
2576
2577
2578
2579 /* Standard or Average, Lifo, Fifo processing logic */
2580 if( v_primary_cost_method = 1 ) then
2581
2582
2583
2584 v_item_transacted := FALSE ;
2585
2586 v_item_transacted := is_item_transacted( pConfigId
2587 , p_cost_organization_id -- 3116778
2588 , 1 ) ;
2589
2590
2591 if( v_item_transacted ) then
2592
2593 IF PG_DEBUG <> 0 THEN
2594 oe_debug_pub.add( ' came into item transacted ' , 1 ) ;
2595 END IF;
2596
2597 if( v_valuation_cost <> v_cto_cost or v_cto_cost is null ) then
2598
2599 IF PG_DEBUG <> 0 THEN
2600 oe_debug_pub.add( ' going to copy cost valuation cost ' ||
2601 v_primary_cost_method , 1 ) ;
2602 oe_debug_pub.add( ' cto cost ' || v_cto_cost_type_id , 1 ) ;
2603 oe_debug_pub.add( ' config id ' || pConfigId , 1 ) ;
2604 oe_debug_pub.add( ' organization id ' || p_organization_id , 1 ) ;
2605 oe_debug_pub.add( 'cost organization id ' || p_cost_organization_id , 1 ) ; -- 3116778
2606
2607 END IF;
2608
2609
2610 /* copy_valuation_cost_to_cto_cost() ; */
2611 lStmtNum := 55;
2612 CTO_UTILITY_PK.copy_cost(v_primary_cost_method
2613 , v_cto_cost_type_id
2614 , pConfigId
2615 , p_cost_organization_id -- 3116778
2616 ) ;
2617
2618
2619 else
2620
2621 IF PG_DEBUG <> 0 THEN
2622 oe_debug_pub.add( ' cto cost is same as valuation cost no need to synch up ' , 1 ) ;
2623 END IF;
2624
2625 end if ;
2626
2627
2628 lStmtNum := 60;
2629
2630 if( p_group_reference_id is null ) then
2631 update bom_cto_src_orgs_b
2632 set cost_rollup = 'N'
2633 where line_id = pLineId
2634 and organization_id = p_organization_id ;
2635
2636
2637 IF PG_DEBUG <> 0 THEN
2638 oe_debug_pub.add( ' updated bcso cost rollup N for line ' || pLineId
2639 || ' org ' || p_organization_id
2640 || ' count ' || to_char(sql%rowcount)
2641 , 1 ) ;
2642 END IF;
2643
2644
2645 else
2646
2647 update bom_cto_model_orgs
2648 set cost_rollup = 'N'
2649 where group_reference_id = p_group_reference_id
2650 and organization_id = p_organization_id ;
2651
2652
2653 IF PG_DEBUG <> 0 THEN
2654 oe_debug_pub.add( ' updated bcmo cost rollup N for group_ref ' || p_group_reference_id
2655 || ' org ' || p_organization_id
2656 || ' count ' || to_char(sql%rowcount)
2657 , 1 ) ;
2658 END IF;
2659
2660
2661
2662 end if ;
2663
2664
2665 IF PG_DEBUG <> 0 THEN
2666 oe_debug_pub.add( ' going to indicate no cost rollup due to transacted condition 1 '
2667 , 1 ) ;
2668 END IF;
2669
2670
2671
2672
2673
2674
2675 else /* No transactions have taken place */
2676
2677
2678 /* Cost Rollup Override logic in Standard costing org. */
2679
2680 if( v_organization_type = '2' ) then /* make */
2681
2682 IF PG_DEBUG <> 0 THEN
2683 oe_debug_pub.add( ' came into make organization type ' , 1 ) ;
2684 END IF;
2685
2686 if( v_rolledup_cost_count > 0 and
2687 ( v_rolledup_cost <> v_buy_cost or v_buy_cost is null ) ) then
2688
2689 /* Synch up cto cost with valuation cost in case of average costing org. */
2690
2691
2692 if( v_valuation_cost <> v_cto_cost or v_cto_cost is null ) then
2693
2694 IF PG_DEBUG <> 0 THEN
2695 oe_debug_pub.add( ' going to copy cost valuation cost ' ||
2696 v_primary_cost_method , 1 ) ;
2697 oe_debug_pub.add( ' cto cost ' || v_cto_cost_type_id , 1 ) ;
2698 oe_debug_pub.add( ' config id ' || pConfigId , 1 ) ;
2699 oe_debug_pub.add( ' organization id ' || p_organization_id , 1 ) ;
2700 oe_debug_pub.add( ' cost organization id ' || p_cost_organization_id , 1 ) ; -- 3116778
2701 END IF;
2702
2703
2704 /* copy_valuation_cost_to_cto_cost() ; */
2705 lStmtNum := 55;
2706 CTO_UTILITY_PK.copy_cost(v_primary_cost_method
2707 , v_cto_cost_type_id
2708 , pConfigId
2709 , p_cost_organization_id -- 3116778
2710 ) ;
2711
2712
2713 else
2714
2715 IF PG_DEBUG <> 0 THEN
2716 oe_debug_pub.add( ' cto cost is same as valuation cost no need to synch up ' , 1 ) ;
2717 END IF;
2718
2719 end if ;
2720
2721 lStmtNum := 60;
2722
2723 if( p_group_reference_id is null ) then
2724 update bom_cto_src_orgs_b
2725 set cost_rollup = 'N'
2726 where line_id = pLineId
2727 and organization_id = p_organization_id ;
2728
2729 IF PG_DEBUG <> 0 THEN
2730 oe_debug_pub.add( ' updated bcso cost rollup N for line ' || pLineId
2731 || ' org ' || p_organization_id
2732 || ' count ' || to_char(sql%rowcount)
2733 , 1 ) ;
2734 END IF;
2735
2736 else
2737
2738 update bom_cto_model_orgs
2739 set cost_rollup = 'N'
2740 where group_reference_id = p_group_reference_id
2741 and organization_id = p_organization_id ;
2742
2743 IF PG_DEBUG <> 0 THEN
2744 oe_debug_pub.add( ' updated bcmo cost rollup N for group_ref ' || p_group_reference_id
2745 || ' org ' || p_organization_id
2746 || ' count ' || to_char(sql%rowcount)
2747 , 1 ) ;
2748
2749 END IF;
2750
2751
2752 end if ;
2753
2754 IF PG_DEBUG <> 0 THEN
2755 oe_debug_pub.add( ' going to indicate no cost rollup due to std make condition 1 '
2756 , 1 ) ;
2757 END IF;
2758
2759
2760
2761 end if; /* rolledup cost exists in Standard costing Org for make context */
2762
2763
2764
2765 elsif( v_organization_type in ( '3', '5')) then /* buy, dropship */
2766
2767 IF PG_DEBUG <> 0 THEN
2768 oe_debug_pub.add( ' came into buy organization type ' , 1 ) ;
2769 oe_debug_pub.add( ' cto ' || v_cto_cost , 1 ) ;
2770 oe_debug_pub.add( ' cto xudc ' || v_cto_cost_xudc , 1 ) ;
2771 oe_debug_pub.add( ' cto buy ' || v_buy_cost , 1 ) ;
2772 END IF ;
2773
2774
2775 end if ; /* costing for matched items logic */
2776
2777
2778
2779 end if ; /* item transacted */
2780
2781 else
2782
2783 /* Cost Rollup Override logic in Average, Lifo, Fifo Costing org. */
2784
2785
2786 if( v_valuation_cost <> 0 ) then
2787
2788 /* Synch up cto cost with valuation cost in case of average costing org. */
2789
2790
2791 if( v_valuation_cost <> v_cto_cost or v_cto_cost is null ) then
2792 IF PG_DEBUG <> 0 THEN
2793 oe_debug_pub.add( ' going to copy cost valuation cost ' ||
2794 v_primary_cost_method , 1 ) ;
2795 oe_debug_pub.add( ' cto cost ' || v_cto_cost_type_id , 1 ) ;
2796 oe_debug_pub.add( ' config id ' || pConfigId , 1 ) ;
2797 oe_debug_pub.add( ' organization id ' || p_organization_id , 1 ) ;
2798 oe_debug_pub.add( ' cost organization id ' || p_cost_organization_id , 1 ) ; -- 3116778
2799 END IF;
2800
2801
2802 /* copy_valuation_cost_to_cto_cost() ; */
2803 lStmtNum := 65;
2804 CTO_UTILITY_PK.copy_cost( v_primary_cost_method
2805 , v_cto_cost_type_id
2806 , pConfigId
2807 , p_cost_organization_id -- 3116778
2808 ) ;
2809
2810 else
2811
2812 IF PG_DEBUG <> 0 THEN
2813 oe_debug_pub.add( ' cto cost is same as valuation cost no need to synch up ' , 1 ) ;
2814 END IF;
2815
2816 end if ;
2817
2818
2819
2820
2821 lStmtNum := 70;
2822
2823
2824 if( p_group_reference_id is null ) then
2825 update bom_cto_src_orgs_b
2826 set cost_rollup = 'N'
2827 where line_id = pLineId
2828 and organization_id = p_organization_id ;
2829
2830 IF PG_DEBUG <> 0 THEN
2831 oe_debug_pub.add( ' updated bcso cost rollup N for line ' || pLineId
2832 || ' org ' || p_organization_id
2833 || ' count ' || to_char(sql%rowcount)
2834 , 1 ) ;
2835 END IF;
2836
2837 else
2838
2839 update bom_cto_model_orgs
2840 set cost_rollup = 'N'
2841 where group_reference_id = p_group_reference_id
2842 and organization_id = p_organization_id ;
2843
2844 IF PG_DEBUG <> 0 THEN
2845 oe_debug_pub.add( ' updated bcmo cost rollup N for group_ref ' || p_group_reference_id
2846 || ' org ' || p_organization_id
2847 || ' count ' || to_char(sql%rowcount)
2848 , 1 ) ;
2849
2850 END IF;
2851
2852
2853
2854 end if ;
2855
2856
2857 IF PG_DEBUG <> 0 THEN
2858 oe_debug_pub.add( ' going to indicate no cost rollup due to avg make condition 1 '
2859 , 1 ) ;
2860 END IF;
2861
2862
2863
2864 else
2865
2866 /* Logic for Make or Buy within Average/Lifo/Fifo Costing Org */
2867
2868 if( v_organization_type = '2' ) then /* make */
2869
2870
2871 if( v_cto_cost is not null and
2872 ( v_buy_cost is null or v_cto_cost_xudc <> v_buy_cost )) then
2873
2874
2875 lStmtNum := 75;
2876
2877
2878 if( p_group_reference_id is null ) then
2879 update bom_cto_src_orgs_b
2880 set cost_rollup = 'N'
2881 where line_id = pLineId
2882 and organization_id = p_organization_id ;
2883
2884 IF PG_DEBUG <> 0 THEN
2885 oe_debug_pub.add( ' updated bcso cost rollup N for line ' || pLineId
2886 || ' org ' || p_organization_id
2887 || ' count ' || to_char(sql%rowcount)
2888 , 1 ) ;
2889 END IF;
2890
2891 else
2892
2893 update bom_cto_model_orgs
2894 set cost_rollup = 'N'
2895 where group_reference_id = p_group_reference_id
2896 and organization_id = p_organization_id ;
2897
2898 IF PG_DEBUG <> 0 THEN
2899 oe_debug_pub.add( ' updated bcmo cost rollup N for group_ref ' || p_group_reference_id
2900 || ' org ' || p_organization_id
2901 || ' count ' || to_char(sql%rowcount)
2902 , 1 ) ;
2903
2904 END IF;
2905
2906
2907
2908 end if ;
2909
2910
2911 IF PG_DEBUG <> 0 THEN
2912 oe_debug_pub.add( ' going to indicate no cost rollup due to avg make condition 2 '
2913 , 1 ) ;
2914 END IF;
2915
2916
2917 end if ;
2918
2919
2920 elsif( v_organization_type in ( '3', '5')) then /* buy, dropship */
2921
2922 IF PG_DEBUG <> 0 THEN
2923 oe_debug_pub.add( ' came into buy organization type ' , 1 ) ;
2924 oe_debug_pub.add( ' cto ' || v_cto_cost , 1 ) ;
2925 oe_debug_pub.add( ' cto xudc ' || v_cto_cost_xudc , 1 ) ;
2926 oe_debug_pub.add( ' cto buy ' || v_buy_cost , 1 ) ;
2927 END IF ;
2928
2929
2930 end if ; /* costing for make or buy logic */
2931
2932
2933
2934
2935 end if; /* Valuation exists or not logic in Average/Lifo/Fifo costing org */
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945 end if ;
2946 /* Cost Rollup Override logic for Standard or Average, Lifo, Fifo processing logic */
2947
2948
2949
2950
2951
2952
2953
2954 Exception
2955 WHEN fnd_api.g_exc_error THEN
2956 IF PG_DEBUG <> 0 THEN
2957 oe_debug_pub.add('create_in_src_orgs: '
2958 || 'expected error::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
2959 END IF;
2960
2961 xReturnStatus := fnd_api.g_ret_sts_unexp_error;
2962 -- Get message count and data
2963 xReturnStatus := fnd_api.g_ret_sts_error;
2964 -- Get message count and data
2965
2966
2967 cto_msg_pub.count_and_get
2968 ( p_msg_count => xMsgCount
2969 , p_msg_data => xMsgData
2970 );
2971
2972 WHEN fnd_api.g_exc_unexpected_error THEN
2973
2974
2975 xReturnStatus := fnd_api.g_ret_sts_unexp_error ;
2976 -- Get message count and data
2977
2978
2979 cto_msg_pub.count_and_get
2980 ( p_msg_count => xMsgCount
2981 , p_msg_data => xMsgData
2982 );
2983
2984 WHEN OTHERS then
2985 IF PG_DEBUG <> 0 THEN
2986 oe_debug_pub.add('create_in_src_orgs: '
2987 || 'create_in_src_orgs::others::'||to_char(lStmtNum)
2988 ||'::'||sqlerrm, 1);
2989 END IF;
2990
2991 xReturnStatus := fnd_api.g_ret_sts_unexp_error;
2992 -- Get message count and data
2993
2994 cto_msg_pub.count_and_get
2995 ( p_msg_count => xMsgCount
2996 , p_msg_data => xMsgData
2997 );
2998
2999
3000 END override_bcso_cost_rollup;
3001
3002
3003
3004 END CTO_BOM_RTG_PK;