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