[Home] [Help]
198:
199: BEGIN
200:
201: IF PG_DEBUG <> 0 THEN
202: oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::pTopAtoLineId::'||to_char(pTopAtoLineId),1);
203: END IF;
204:
205: --
206: -- For each model item in all possible receiving orgs, call
208: --
209:
210: lStmtNumber := 20;
211: IF PG_DEBUG <> 0 THEN
212: oe_debug_pub.add('populate_plan_level: ' || 'before loop',2);
213: END IF;
214:
215:
216: FOR v_model_lines IN c_model_lines LOOP
217:
218: if( v_model_lines.config_creation in ( 1, 2 ) ) then
219:
220: IF PG_DEBUG <> 0 THEN
221: oe_debug_pub.add('populate_plan_level: ' || 'loop::item::'||to_char(v_model_lines.inventory_item_id)||
222: '::line_id::'||to_char(v_model_lines.line_id),2);
223: END IF;
224: lStmtNumber := 30;
225:
224: lStmtNumber := 30;
225:
226: IF v_model_lines.ato_line_id = v_model_lines.line_id THEN
227: IF PG_DEBUG <> 0 THEN
228: oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id = line_id',2);
229: END IF;
230:
231:
232: lStmtNumber := 40;
236: where line_id = v_model_lines.line_id;
237:
238: lStmtNumber := 50;
239: IF PG_DEBUG <> 0 THEN
240: oe_debug_pub.add('populate_plan_level: ' || 'before calling GAIO',2);
241:
242: oe_debug_pub.add('populate_plan_level: ' || 'line_id::'||to_char(v_model_lines.line_id)||
243: '::inv_id::'||to_char(v_model_lines.inventory_item_id)||
244: '::ship_from_org::'||to_char(lShipFromOrgId),2);
238: lStmtNumber := 50;
239: IF PG_DEBUG <> 0 THEN
240: oe_debug_pub.add('populate_plan_level: ' || 'before calling GAIO',2);
241:
242: oe_debug_pub.add('populate_plan_level: ' || 'line_id::'||to_char(v_model_lines.line_id)||
243: '::inv_id::'||to_char(v_model_lines.inventory_item_id)||
244: '::ship_from_org::'||to_char(lShipFromOrgId),2);
245: END IF;
246:
253: x_msg_data);
254:
255: IF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
256: IF PG_DEBUG <> 0 THEN
257: oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with unexp error',1);
258: END IF;
259: raise FND_API.G_EXC_UNEXPECTED_ERROR;
260:
261: ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
259: raise FND_API.G_EXC_UNEXPECTED_ERROR;
260:
261: ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
262: IF PG_DEBUG <> 0 THEN
263: oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with exp error',1);
264: END IF;
265: raise FND_API.G_EXC_ERROR;
266: END IF;
267: IF PG_DEBUG <> 0 THEN
264: END IF;
265: raise FND_API.G_EXC_ERROR;
266: END IF;
267: IF PG_DEBUG <> 0 THEN
268: oe_debug_pub.add('populate_plan_level: ' || 'after calling GAIO::lStatus::'||to_char(lStatus),2);
269: END IF;
270: ELSE
271: IF PG_DEBUG <> 0 THEN
272: oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id <> line_id',2);
268: oe_debug_pub.add('populate_plan_level: ' || 'after calling GAIO::lStatus::'||to_char(lStatus),2);
269: END IF;
270: ELSE
271: IF PG_DEBUG <> 0 THEN
272: oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id <> line_id',2);
273: END IF;
274: lStmtNumber := 60;
275: lLineId := v_model_lines.line_id;
276: IF PG_DEBUG <> 0 THEN
273: END IF;
274: lStmtNumber := 60;
275: lLineId := v_model_lines.line_id;
276: IF PG_DEBUG <> 0 THEN
277: oe_debug_pub.add('populate_plan_level: ' || 'before PSO loop',2);
278: END IF;
279:
280: FOR v_parent_src_ogs IN c_parent_src_orgs LOOP
281: IF PG_DEBUG <> 0 THEN
278: END IF;
279:
280: FOR v_parent_src_ogs IN c_parent_src_orgs LOOP
281: IF PG_DEBUG <> 0 THEN
282: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::rcv org::'||
283: to_char(v_parent_src_ogs.organization_id),2);
284:
285: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::item id::'||
286: to_char(v_model_lines.inventory_item_id),2);
281: IF PG_DEBUG <> 0 THEN
282: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::rcv org::'||
283: to_char(v_parent_src_ogs.organization_id),2);
284:
285: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::item id::'||
286: to_char(v_model_lines.inventory_item_id),2);
287:
288: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::line id::'||
289: to_char(v_model_lines.line_id),2);
284:
285: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::item id::'||
286: to_char(v_model_lines.inventory_item_id),2);
287:
288: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::line id::'||
289: to_char(v_model_lines.line_id),2);
290: END IF;
291: lStmtNumber := 70;
292: lStatus := get_all_item_orgs(v_model_lines.line_id,
297: x_msg_data);
298:
299: IF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
300: IF PG_DEBUG <> 0 THEN
301: oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with unexp error',1);
302: END IF;
303: raise FND_API.G_EXC_UNEXPECTED_ERROR;
304:
305: ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
303: raise FND_API.G_EXC_UNEXPECTED_ERROR;
304:
305: ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
306: IF PG_DEBUG <> 0 THEN
307: oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with exp error',1);
308: END IF;
309: raise FND_API.G_EXC_ERROR;
310: END IF;
311:
314:
315:
316: else
317:
318: oe_debug_pub.add( 'config creation 3 not yet implemented ' , 1) ;
319:
320:
321: oe_debug_pub.add( '$$$$$$$$ TYPE 3 model line ' || v_model_lines.line_id , 1 ) ;
322:
317:
318: oe_debug_pub.add( 'config creation 3 not yet implemented ' , 1) ;
319:
320:
321: oe_debug_pub.add( '$$$$$$$$ TYPE 3 model line ' || v_model_lines.line_id , 1 ) ;
322:
323:
324: if( v_model_lines.perform_match = 'N' ) then
325:
323:
324: if( v_model_lines.perform_match = 'N' ) then
325:
326:
327: oe_debug_pub.add( '$$$$$$$$ GOING TO CALL insert_type3_bcso ' || v_model_lines.line_id , 1 ) ;
328:
329: CTO_MSUTIL_PUB.insert_type3_bcso( pTopAtoLineId
330: , v_model_lines.line_id
331: , v_model_lines.inventory_item_id ) ;
332:
333: elsif( v_model_lines.perform_match in ( 'Y' , 'C' ) ) then
334:
335:
336: oe_debug_pub.add( '$$$$$$$$ GOING TO CALL insert_type3_referenced_bcso ' || v_model_lines.line_id , 1 ) ;
337:
338: CTO_MSUTIL_PUB.insert_type3_referenced_bcso( pTopAtoLineId
339: , v_model_lines.line_id
340: , v_model_lines.inventory_item_id
345:
346: elsif( v_model_lines.perform_match = 'U' ) then
347:
348:
349: oe_debug_pub.add( '$$$$$$$$ GOING TO CALL insert_type3_bcmo_bcso ' || v_model_lines.line_id , 1 ) ;
350:
351: CTO_MSUTIL_PUB.insert_type3_bcmo_bcso( pTopAtoLineId
352: , v_model_lines.line_id
353: , v_model_lines.inventory_item_id ) ;
364:
365:
366:
367:
368: oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || to_char( v_model_lines.line_id ) , 1 ) ;
369: oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || v_model_lines.option_specific , 1 ) ;
370:
371: if( v_model_lines.option_specific in ( '1' , '2', '3' ) ) then /* do not execute this code as we will be changing it tomorrow */
372:
365:
366:
367:
368: oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || to_char( v_model_lines.line_id ) , 1 ) ;
369: oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || v_model_lines.option_specific , 1 ) ;
370:
371: if( v_model_lines.option_specific in ( '1' , '2', '3' ) ) then /* do not execute this code as we will be changing it tomorrow */
372:
373: oe_debug_pub.add( 'Going to Call GET OSS BOM ORGS for Create BOM Indication ' || to_char( v_model_lines.line_id) , 1 ) ;
369: oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || v_model_lines.option_specific , 1 ) ;
370:
371: if( v_model_lines.option_specific in ( '1' , '2', '3' ) ) then /* do not execute this code as we will be changing it tomorrow */
372:
373: oe_debug_pub.add( 'Going to Call GET OSS BOM ORGS for Create BOM Indication ' || to_char( v_model_lines.line_id) , 1 ) ;
374:
375:
376: cto_oss_source_pk.get_oss_bom_orgs( p_line_id => v_model_lines.line_id
377: ,x_orgs_list => v_orgs_list
386:
387: if x_return_status = FND_API.G_RET_STS_ERROR then
388:
389: IF PG_DEBUG <> 0 THEN
390: oe_debug_pub.add ('GET_ALL_ITEM_ORGS: ' ||
391: 'Failed in cto_oss_source_pk.get_oss_bom_orgs with expected error.', 1);
392: END IF;
393:
394: raise FND_API.G_EXC_ERROR;
395:
396: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
397:
398: IF PG_DEBUG <> 0 THEN
399: oe_debug_pub.add ('GET_ALL_ITEM_ORGS: ' ||
400: 'Failed in cto_oss_source_pk.get_oss_bom_orgs with unexpected error.', 1);
401: END IF;
402:
403: raise FND_API.G_EXC_UNEXPECTED_ERROR;
404:
405: end if;
406:
407: IF PG_DEBUG <> 0 THEN
408: oe_debug_pub.add('Create_And_Link_Item: ' || 'Ater Populate_Bcol', 5);
409: END IF;
410:
411:
412:
409: END IF;
410:
411:
412:
413: oe_debug_pub.add( 'OSS ORGS for Create BOM list size ' || v_orgs_list.count , 1 ) ;
414:
415: oe_debug_pub.add( 'OSS ORGS for Create BOM UPDATE ' , 1 ) ;
416:
417:
411:
412:
413: oe_debug_pub.add( 'OSS ORGS for Create BOM list size ' || v_orgs_list.count , 1 ) ;
414:
415: oe_debug_pub.add( 'OSS ORGS for Create BOM UPDATE ' , 1 ) ;
416:
417:
418: if( v_orgs_list.count > 0 ) then
419:
425:
426: update bom_cto_model_orgs set create_bom = 'N'
427: where group_reference_id = v_group_reference_id ;
428:
429: oe_debug_pub.add( 'UPDATED BCMO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
430: else
431:
432:
433: update bom_cto_src_orgs_b set create_bom = 'N'
432:
433: update bom_cto_src_orgs_b set create_bom = 'N'
434: where line_id = v_model_lines.line_id ;
435:
436: oe_debug_pub.add( 'UPDATED BCSO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
437:
438: end if;
439:
440:
441:
442: for i in 1..v_orgs_list.count
443: loop
444:
445: oe_debug_pub.add( 'OSS ORGS for Create BOM ' || v_orgs_list(i) , 1 ) ;
446:
447: if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
448:
449: select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
456: ( select 1 from bom_parameters
457: where organization_id = v_orgs_list(i)
458: and nvl(create_config_bom,'N') = 'Y' );
459:
460: oe_debug_pub.add( 'UPDATED BCMO create_bom = Y for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
461:
462: else
463:
464:
469: ( select 1 from bom_parameters
470: where organization_id = v_orgs_list(i)
471: and nvl(create_config_bom,'N') = 'Y' );
472:
473: oe_debug_pub.add( 'UPDATED BCSO create_bom = Y for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
474:
475: end if;
476:
477:
486:
487:
488:
489:
490: oe_debug_pub.add( '$$$$$$$$$$$$ DONE GET OSS BOM ORGS for Create BOM Indication ' || v_model_lines.line_id , 1 ) ;
491:
492:
493:
494:
496:
497: END LOOP;
498:
499: IF PG_DEBUG <> 0 THEN
500: oe_debug_pub.add('populate_plan_level: ' || 'end of loop',1);
501:
502: oe_debug_pub.add('populate_plan_level: ' || 'printing out bcso :', 2);
503:
504: oe_debug_pub.add('populate_plan_level: ' || 'line_id model_item_id rcv_org_id org_id create_bom create_src_rules organization_type group_reference_id ', 2);
498:
499: IF PG_DEBUG <> 0 THEN
500: oe_debug_pub.add('populate_plan_level: ' || 'end of loop',1);
501:
502: oe_debug_pub.add('populate_plan_level: ' || 'printing out bcso :', 2);
503:
504: oe_debug_pub.add('populate_plan_level: ' || 'line_id model_item_id rcv_org_id org_id create_bom create_src_rules organization_type group_reference_id ', 2);
505: END IF;
506:
500: oe_debug_pub.add('populate_plan_level: ' || 'end of loop',1);
501:
502: oe_debug_pub.add('populate_plan_level: ' || 'printing out bcso :', 2);
503:
504: oe_debug_pub.add('populate_plan_level: ' || 'line_id model_item_id rcv_org_id org_id create_bom create_src_rules organization_type group_reference_id ', 2);
505: END IF;
506:
507: FOR v_debug IN c_debug LOOP
508: IF PG_DEBUG <> 0 THEN
505: END IF;
506:
507: FOR v_debug IN c_debug LOOP
508: IF PG_DEBUG <> 0 THEN
509: oe_debug_pub.add('populate_plan_level: ' || to_char(v_debug.line_id)||' '||
510: to_char(v_debug.model_item_id)||' '||
511: nvl(to_char(v_debug.rcv_org_id),null)||' '||
512: to_char(v_debug.organization_id)||' '||
513: nvl(v_debug.create_bom, null)||' '||
520:
521:
522: /* make a call to get validation orgs and purchasing related orgs */
523:
524: oe_debug_pub.add( '$$$$$$$$ Additional Type 1 and 2 Processing ' , 1 ) ;
525:
526: open get_each_type1_model ;
527:
528: loop
530: fetch get_each_type1_model into v_current_model_line_id , v_current_model_item_id , v_config_creation ;
531:
532: exit when get_each_type1_model%notfound ;
533:
534: oe_debug_pub.add( '$$$$$$$$ calling ORg List for model line ' || v_current_model_line_id , 1 ) ;
535:
536: if( v_config_creation = 1) then
537:
538: oe_debug_pub.add( '$$$$$$$$ TYPE 1 model line ' || v_current_model_line_id , 1 ) ;
534: oe_debug_pub.add( '$$$$$$$$ calling ORg List for model line ' || v_current_model_line_id , 1 ) ;
535:
536: if( v_config_creation = 1) then
537:
538: oe_debug_pub.add( '$$$$$$$$ TYPE 1 model line ' || v_current_model_line_id , 1 ) ;
539:
540: CTO_MSUTIL_PUB.get_other_orgs( pmodellineid => v_current_model_line_id ,
541: xorglst => v_t_org_list ,
542: x_return_status => x_return_status ,
546: /* bugfix 4227127 fp for bug 4162642 :added return status check */
547: if x_return_status = FND_API.G_RET_STS_ERROR then
548:
549: if PG_DEBUG <> 0 then
550: oe_debug_pub.add( 'ERROR: get_other_orgs api return expected error' , 1 ) ;
551: end if;
552:
553: Raise FND_API.G_EXC_ERROR;
554:
554:
555: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
556:
557: if PG_DEBUG <> 0 then
558: oe_debug_pub.add( 'ERROR: get_other_orgs api return unexpected error' , 1 ) ;
559: end if;
560:
561: Raise FND_API.G_EXC_UNEXPECTED_ERROR;
562:
563: end if;
564:
565:
566:
567: oe_debug_pub.add( '$$$$$$$$ ORg List for model line ' || v_current_model_line_id , 1 ) ;
568:
569:
570: CTO_MSUTIL_PUB.insert_val_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id, v_t_org_list ) ;
571:
569:
570: CTO_MSUTIL_PUB.insert_val_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id, v_t_org_list ) ;
571:
572:
573: oe_debug_pub.add( '$$$$$$$$ ORg List DONE for model line ' || v_current_model_line_id , 1 ) ;
574:
575: -- Added by Renga Kannan on 15-Sep-2005
576: -- Added for ATG performance Project
577:
584:
585: if x_return_status = FND_API.G_RET_STS_ERROR then
586:
587: if PG_DEBUG <> 0 then
588: oe_debug_pub.add( 'ERROR: get_Master_orgs api return expected error' , 1 ) ;
589: end if;
590:
591: Raise FND_API.G_EXC_ERROR;
592:
592:
593: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
594:
595: if PG_DEBUG <> 0 then
596: oe_debug_pub.add( 'ERROR: get_Master_orgs api return unexpected error' , 1 ) ;
597: end if;
598:
599: Raise FND_API.G_EXC_UNEXPECTED_ERROR;
600:
607: End if;
608:
609: elsif( v_config_creation = 2 ) then
610:
611: oe_debug_pub.add( '$$$$$$$$ TYPE 2 model line ' || v_current_model_line_id , 1 ) ;
612:
613: CTO_MSUTIL_PUB.insert_all_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id ) ;
614:
615: end if;
621:
622:
623:
624:
625: oe_debug_pub.add( '$$$$$$$$ Going to update Create BOM flag for Shared Costing Organizations ' , 1 ) ;
626:
627: /*Update Create_BOM Flag for Shared Costing Organizations */
628: update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
629: where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
640: and bp.create_config_bom = 'Y' ) ;
641:
642:
643:
644: oe_debug_pub.add( '$$$$$$$$ Create BOM flag updated for Shared Costing Organizations ' || to_char(sql%rowcount) , 1 ) ;
645:
646:
647:
648: /* print debug output for Shared Cost update */
648: /* print debug output for Shared Cost update */
649: if( sql%rowcount > 0 ) then
650: FOR v_debug IN c_debug LOOP
651: IF PG_DEBUG <> 0 THEN
652: oe_debug_pub.add('populate_plan_level: ' || to_char(v_debug.line_id)||' '||
653: to_char(v_debug.model_item_id)||' '||
654: nvl(to_char(v_debug.rcv_org_id),null)||' '||
655: to_char(v_debug.organization_id)||' '||
656: nvl(v_debug.create_bom, null)||' '||
669: EXCEPTION
670:
671: when FND_API.G_EXC_UNEXPECTED_ERROR then
672: IF PG_DEBUG <> 0 THEN
673: oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::unexp error::'||lStmtNumber||sqlerrm,1);
674: END IF;
675: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
676: CTO_MSG_PUB.Count_And_Get
677: (p_msg_count => x_msg_count
680: return(0);
681:
682: when FND_API.G_EXC_ERROR then
683: IF PG_DEBUG <> 0 THEN
684: oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::exp error::'||lStmtNumber||sqlerrm,1);
685: END IF;
686: x_return_status := FND_API.G_RET_STS_ERROR;
687: CTO_MSG_PUB.Count_And_Get
688: (p_msg_count => x_msg_count
690: return(0);
691:
692: when others then
693: IF PG_DEBUG <> 0 THEN
694: oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::others::'||lStmtNumber||sqlerrm,1);
695: END IF;
696: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
698: FND_MSG_PUB.Add_Exc_Msg
786:
787: BEGIN
788:
789: IF PG_DEBUG <> 0 THEN
790: oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::pTopAtoLineId::'||to_char(pTopAtoLineId),1);
791: END IF;
792:
793: --
794: -- For each model item in all possible receiving orgs, call
796: --
797:
798: lStmtNumber := 20;
799: IF PG_DEBUG <> 0 THEN
800: oe_debug_pub.add('populate_plan_level: ' || 'before loop',2);
801: END IF;
802:
803:
804: FOR v_model_lines IN c_model_lines LOOP
805:
806: if( v_model_lines.config_creation in ( 1, 2 ) ) then
807:
808: IF PG_DEBUG <> 0 THEN
809: oe_debug_pub.add('populate_plan_level: ' || 'loop::item::'||to_char(v_model_lines.inventory_item_id)||
810: '::line_id::'||to_char(v_model_lines.line_id),2);
811: END IF;
812: lStmtNumber := 30;
813:
812: lStmtNumber := 30;
813:
814: IF v_model_lines.ato_line_id = v_model_lines.line_id THEN
815: IF PG_DEBUG <> 0 THEN
816: oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id = line_id',2);
817: END IF;
818:
819:
820: lStmtNumber := 40;
824: where line_id = v_model_lines.line_id;
825:
826: lStmtNumber := 50;
827: IF PG_DEBUG <> 0 THEN
828: oe_debug_pub.add('populate_plan_level: ' || 'before calling GAIO',2);
829:
830: oe_debug_pub.add('populate_plan_level: ' || 'line_id::'||to_char(v_model_lines.line_id)||
831: '::inv_id::'||to_char(v_model_lines.inventory_item_id)||
832: '::ship_from_org::'||to_char(lShipFromOrgId),2);
826: lStmtNumber := 50;
827: IF PG_DEBUG <> 0 THEN
828: oe_debug_pub.add('populate_plan_level: ' || 'before calling GAIO',2);
829:
830: oe_debug_pub.add('populate_plan_level: ' || 'line_id::'||to_char(v_model_lines.line_id)||
831: '::inv_id::'||to_char(v_model_lines.inventory_item_id)||
832: '::ship_from_org::'||to_char(lShipFromOrgId),2);
833: END IF;
834:
843: v_model_lines.config_item_id);
844:
845: IF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
846: IF PG_DEBUG <> 0 THEN
847: oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with unexp error',1);
848: END IF;
849: raise FND_API.G_EXC_UNEXPECTED_ERROR;
850:
851: ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
849: raise FND_API.G_EXC_UNEXPECTED_ERROR;
850:
851: ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
852: IF PG_DEBUG <> 0 THEN
853: oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with exp error',1);
854: END IF;
855: raise FND_API.G_EXC_ERROR;
856: END IF;
857: IF PG_DEBUG <> 0 THEN
854: END IF;
855: raise FND_API.G_EXC_ERROR;
856: END IF;
857: IF PG_DEBUG <> 0 THEN
858: oe_debug_pub.add('populate_plan_level: ' || 'after calling GAIO::lStatus::'||to_char(lStatus),2);
859: END IF;
860: ELSE
861: IF PG_DEBUG <> 0 THEN
862: oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id <> line_id',2);
858: oe_debug_pub.add('populate_plan_level: ' || 'after calling GAIO::lStatus::'||to_char(lStatus),2);
859: END IF;
860: ELSE
861: IF PG_DEBUG <> 0 THEN
862: oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id <> line_id',2);
863: END IF;
864: lStmtNumber := 60;
865: lLineId := v_model_lines.line_id;
866: IF PG_DEBUG <> 0 THEN
863: END IF;
864: lStmtNumber := 60;
865: lLineId := v_model_lines.line_id;
866: IF PG_DEBUG <> 0 THEN
867: oe_debug_pub.add('populate_plan_level: ' || 'before PSO loop',2);
868: END IF;
869:
870: FOR v_parent_src_ogs IN c_parent_src_orgs LOOP
871: IF PG_DEBUG <> 0 THEN
868: END IF;
869:
870: FOR v_parent_src_ogs IN c_parent_src_orgs LOOP
871: IF PG_DEBUG <> 0 THEN
872: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::rcv org::'||
873: to_char(v_parent_src_ogs.organization_id),2);
874:
875: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::item id::'||
876: to_char(v_model_lines.inventory_item_id),2);
871: IF PG_DEBUG <> 0 THEN
872: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::rcv org::'||
873: to_char(v_parent_src_ogs.organization_id),2);
874:
875: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::item id::'||
876: to_char(v_model_lines.inventory_item_id),2);
877:
878: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::line id::'||
879: to_char(v_model_lines.line_id),2);
874:
875: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::item id::'||
876: to_char(v_model_lines.inventory_item_id),2);
877:
878: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::line id::'||
879: to_char(v_model_lines.line_id),2);
880: END IF;
881: lStmtNumber := 70;
882: lStatus := get_all_item_orgs(v_model_lines.line_id,
889: v_model_lines.config_item_id);
890:
891: IF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
892: IF PG_DEBUG <> 0 THEN
893: oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with unexp error',1);
894: END IF;
895: raise FND_API.G_EXC_UNEXPECTED_ERROR;
896:
897: ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
895: raise FND_API.G_EXC_UNEXPECTED_ERROR;
896:
897: ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
898: IF PG_DEBUG <> 0 THEN
899: oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with exp error',1);
900: END IF;
901: raise FND_API.G_EXC_ERROR;
902: END IF;
903:
906:
907:
908: else
909:
910: oe_debug_pub.add( '$$$$$$$$ TYPE 3 model line ' || v_model_lines.line_id , 1 ) ;
911:
912: /* Fixed bug 3472654 */
913: if( v_model_lines.perform_match in ( 'Y' , 'U') ) then
914: CTO_MSUTIL_PUB.insert_type3_referenced_bcso( pTopAtoLineId
934:
935: end if ; /* config_creation check */
936:
937:
938: oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || to_char( v_model_lines.line_id ) , 1 ) ;
939: oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || v_model_lines.option_specific , 1 ) ;
940:
941: if( v_model_lines.option_specific in ( '1' , '2', '3' ) ) then
942:
935: end if ; /* config_creation check */
936:
937:
938: oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || to_char( v_model_lines.line_id ) , 1 ) ;
939: oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || v_model_lines.option_specific , 1 ) ;
940:
941: if( v_model_lines.option_specific in ( '1' , '2', '3' ) ) then
942:
943: oe_debug_pub.add( 'Going to Call GET OSS BOM ORGS for Create BOM Indication ' || to_char( v_model_lines.line_id) , 1 ) ;
939: oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || v_model_lines.option_specific , 1 ) ;
940:
941: if( v_model_lines.option_specific in ( '1' , '2', '3' ) ) then
942:
943: oe_debug_pub.add( 'Going to Call GET OSS BOM ORGS for Create BOM Indication ' || to_char( v_model_lines.line_id) , 1 ) ;
944:
945:
946: cto_oss_source_pk.get_oss_bom_orgs( p_line_id => v_model_lines.line_id
947: ,x_orgs_list => v_orgs_list
956:
957: if x_return_status = FND_API.G_RET_STS_ERROR then
958:
959: IF PG_DEBUG <> 0 THEN
960: oe_debug_pub.add ('GET_ALL_ITEM_ORGS: ' ||
961: 'Failed in cto_oss_source_pk.get_oss_bom_orgs with expected error.', 1);
962: END IF;
963:
964: raise FND_API.G_EXC_ERROR;
965:
966: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
967:
968: IF PG_DEBUG <> 0 THEN
969: oe_debug_pub.add ('GET_ALL_ITEM_ORGS: ' ||
970: 'Failed in cto_oss_source_pk.get_oss_bom_orgs with unexpected error.', 1);
971: END IF;
972:
973: raise FND_API.G_EXC_UNEXPECTED_ERROR;
974:
975: end if;
976:
977: IF PG_DEBUG <> 0 THEN
978: oe_debug_pub.add('Create_And_Link_Item: ' || 'Ater Populate_Bcol', 5);
979: END IF;
980:
981:
982:
979: END IF;
980:
981:
982:
983: oe_debug_pub.add( 'OSS ORGS for Create BOM list size ' || v_orgs_list.count , 1 ) ;
984:
985: oe_debug_pub.add( 'OSS ORGS for Create BOM UPDATE ' , 1 ) ;
986:
987:
981:
982:
983: oe_debug_pub.add( 'OSS ORGS for Create BOM list size ' || v_orgs_list.count , 1 ) ;
984:
985: oe_debug_pub.add( 'OSS ORGS for Create BOM UPDATE ' , 1 ) ;
986:
987:
988: if( v_orgs_list.count > 0 ) then
989:
995:
996: update bom_cto_model_orgs set create_bom = 'N'
997: where group_reference_id = v_group_reference_id ;
998:
999: oe_debug_pub.add( 'UPDATED BCMO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
1000: else
1001:
1002:
1003: update bom_cto_src_orgs_b set create_bom = 'N'
1002:
1003: update bom_cto_src_orgs_b set create_bom = 'N'
1004: where line_id = v_model_lines.line_id ;
1005:
1006: oe_debug_pub.add( 'UPDATED BCSO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
1007:
1008: end if;
1009:
1010:
1011:
1012: for i in 1..v_orgs_list.count
1013: loop
1014:
1015: oe_debug_pub.add( 'OSS ORGS for Create BOM ' || v_orgs_list(i) , 1 ) ;
1016:
1017: if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
1018:
1019: select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
1026: ( select 1 from bom_parameters
1027: where organization_id = v_orgs_list(i)
1028: and nvl(create_config_bom,'N') = 'Y' );
1029:
1030: oe_debug_pub.add( 'UPDATED BCMO create_bom = Y for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
1031:
1032: else
1033:
1034:
1039: ( select 1 from bom_parameters
1040: where organization_id = v_orgs_list(i)
1041: and nvl(create_config_bom,'N') = 'Y' );
1042:
1043: oe_debug_pub.add( 'UPDATED BCSO create_bom = Y for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
1044:
1045: end if;
1046:
1047:
1056:
1057:
1058:
1059:
1060: oe_debug_pub.add( '$$$$$$$$$$$$ DONE GET OSS BOM ORGS for Create BOM Indication ' || v_model_lines.line_id , 1 ) ;
1061:
1062:
1063:
1064: END LOOP;
1063:
1064: END LOOP;
1065:
1066: IF PG_DEBUG <> 0 THEN
1067: oe_debug_pub.add('populate_plan_level: ' || 'end of loop',1);
1068:
1069: oe_debug_pub.add('populate_plan_level: ' || 'printing out bcso :', 2);
1070:
1071: oe_debug_pub.add('populate_plan_level: ' || 'line_id model_item_id rcv_org_id org_id create_bom create_src_rules', 2);
1065:
1066: IF PG_DEBUG <> 0 THEN
1067: oe_debug_pub.add('populate_plan_level: ' || 'end of loop',1);
1068:
1069: oe_debug_pub.add('populate_plan_level: ' || 'printing out bcso :', 2);
1070:
1071: oe_debug_pub.add('populate_plan_level: ' || 'line_id model_item_id rcv_org_id org_id create_bom create_src_rules', 2);
1072: END IF;
1073:
1067: oe_debug_pub.add('populate_plan_level: ' || 'end of loop',1);
1068:
1069: oe_debug_pub.add('populate_plan_level: ' || 'printing out bcso :', 2);
1070:
1071: oe_debug_pub.add('populate_plan_level: ' || 'line_id model_item_id rcv_org_id org_id create_bom create_src_rules', 2);
1072: END IF;
1073:
1074: FOR v_debug IN c_debug LOOP
1075: IF PG_DEBUG <> 0 THEN
1072: END IF;
1073:
1074: FOR v_debug IN c_debug LOOP
1075: IF PG_DEBUG <> 0 THEN
1076: oe_debug_pub.add('populate_plan_level: ' || to_char(v_debug.line_id)||' '||
1077: to_char(v_debug.model_item_id)||' '||
1078: nvl(to_char(v_debug.rcv_org_id),null)||' '||
1079: to_char(v_debug.organization_id)||' '||
1080: nvl(v_debug.create_bom, null)||' '||
1085:
1086:
1087:
1088:
1089: oe_debug_pub.add( '$$$$$$$$ Going to update Create BOM flag for Shared Costing Organizations ' , 1 ) ;
1090:
1091: /*Update Create_BOM Flag for Shared Costing Organizations */
1092: update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
1093: where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
1104: and bp.create_config_bom = 'Y' ) ;
1105:
1106:
1107:
1108: oe_debug_pub.add( '$$$$$$$$ Create BOM flag updated for Shared Costing Organizations ' || to_char(sql%rowcount) , 1 ) ;
1109:
1110:
1111:
1112: /* print debug output for Shared Cost update */
1112: /* print debug output for Shared Cost update */
1113: if( sql%rowcount > 0 ) then
1114: FOR v_debug IN c_debug LOOP
1115: IF PG_DEBUG <> 0 THEN
1116: oe_debug_pub.add('populate_plan_level: ' || to_char(v_debug.line_id)||' '||
1117: to_char(v_debug.model_item_id)||' '||
1118: nvl(to_char(v_debug.rcv_org_id),null)||' '||
1119: to_char(v_debug.organization_id)||' '||
1120: nvl(v_debug.create_bom, null)||' '||
1127:
1128:
1129: /* make a call to get validation orgs and purchasing related orgs */
1130:
1131: oe_debug_pub.add( '$$$$$$$$ Additional Type 1 and 2 Processing ' , 1 ) ;
1132:
1133: open get_each_type1_model ;
1134:
1135: loop
1137: fetch get_each_type1_model into v_current_model_line_id , v_current_model_item_id , v_config_creation , v_current_config_item_id;
1138:
1139: exit when get_each_type1_model%notfound ;
1140:
1141: oe_debug_pub.add( '$$$$$$$$ calling ORg List for model line ' || v_current_model_line_id , 1 ) ;
1142:
1143: if( v_config_creation = 1) then
1144:
1145: oe_debug_pub.add( '$$$$$$$$ TYPE 1 model line ' || v_current_model_line_id , 1 ) ;
1141: oe_debug_pub.add( '$$$$$$$$ calling ORg List for model line ' || v_current_model_line_id , 1 ) ;
1142:
1143: if( v_config_creation = 1) then
1144:
1145: oe_debug_pub.add( '$$$$$$$$ TYPE 1 model line ' || v_current_model_line_id , 1 ) ;
1146:
1147: CTO_MSUTIL_PUB.get_other_orgs( pmodellineid => v_current_model_line_id ,
1148: p_mode => 'UPG',
1149: xorglst => v_t_org_list ,
1155: /* bugfix 4162642 :added return status check */
1156: if x_return_status = FND_API.G_RET_STS_ERROR then
1157:
1158: if PG_DEBUG <> 0 then
1159: oe_debug_pub.add( 'ERROR: get_other_orgs api return expected error' , 1 ) ;
1160: end if;
1161:
1162: Raise FND_API.G_EXC_ERROR;
1163:
1163:
1164: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1165:
1166: if PG_DEBUG <> 0 then
1167: oe_debug_pub.add( 'ERROR: get_other_orgs api return unexpected error' , 1 ) ;
1168: end if;
1169:
1170: Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1171:
1171:
1172: end if;
1173:
1174:
1175: oe_debug_pub.add( '$$$$$$$$ ORg List for model line ' || v_current_model_line_id , 1 ) ;
1176:
1177:
1178: CTO_MSUTIL_PUB.insert_val_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id, v_t_org_list , v_current_config_item_id) ;
1179:
1177:
1178: CTO_MSUTIL_PUB.insert_val_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id, v_t_org_list , v_current_config_item_id) ;
1179:
1180:
1181: oe_debug_pub.add( '$$$$$$$$ ORg List DONE for model line ' || v_current_model_line_id , 1 ) ;
1182: -- Added by Renga Kannan on 15-Sep-2005
1183: -- Added for ATG performance Project
1184:
1185: CTO_MSUTIL_PUB.Get_Master_orgs(
1191:
1192: if x_return_status = FND_API.G_RET_STS_ERROR then
1193:
1194: if PG_DEBUG <> 0 then
1195: oe_debug_pub.add( 'ERROR: get_Master_orgs api return expected error' , 1 ) ;
1196: end if;
1197:
1198: Raise FND_API.G_EXC_ERROR;
1199:
1199:
1200: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1201:
1202: if PG_DEBUG <> 0 then
1203: oe_debug_pub.add( 'ERROR: get_Master_orgs api return unexpected error' , 1 ) ;
1204: end if;
1205:
1206: Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1207:
1215:
1216:
1217: elsif( v_config_creation = 2 ) then
1218:
1219: oe_debug_pub.add( '$$$$$$$$ TYPE 2 model line ' || v_current_model_line_id , 1 ) ;
1220:
1221: CTO_MSUTIL_PUB.insert_all_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id , v_current_config_item_id) ;
1222:
1223: end if;
1232: EXCEPTION
1233:
1234: when FND_API.G_EXC_UNEXPECTED_ERROR then
1235: IF PG_DEBUG <> 0 THEN
1236: oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::unexp error::'||lStmtNumber||sqlerrm,1);
1237: END IF;
1238: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1239: CTO_MSG_PUB.Count_And_Get
1240: (p_msg_count => x_msg_count
1243: return(0);
1244:
1245: when FND_API.G_EXC_ERROR then
1246: IF PG_DEBUG <> 0 THEN
1247: oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::exp error::'||lStmtNumber||sqlerrm,1);
1248: END IF;
1249: x_return_status := FND_API.G_RET_STS_ERROR;
1250: CTO_MSG_PUB.Count_And_Get
1251: (p_msg_count => x_msg_count
1253: return(0);
1254:
1255: when others then
1256: IF PG_DEBUG <> 0 THEN
1257: oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::others::'||lStmtNumber||sqlerrm,1);
1258: END IF;
1259: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1260: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1261: FND_MSG_PUB.Add_Exc_Msg
1425: Null;
1426:
1427: when too_many_rows then
1428: IF PG_DEBUG <> 0 THEN
1429: oe_debug_pub.add('get_all_item_orgs: ' || 'too_many_rows happens when make and buy exist' ||
1430: to_char(l_parent_ato_line_id ),2);
1431:
1432: END IF;
1433:
1436:
1437: END;
1438:
1439: IF PG_DEBUG <> 0 THEN
1440: oe_debug_pub.add('populate_plan_level: ' || 'top ato line id::'||to_char(lTopAtoLineId),2);
1441:
1442: oe_debug_pub.add('populate_plan_level: ' || 'rcv org id::'||to_char(pRcvOrgId),2);
1443:
1444: oe_debug_pub.add('populate_plan_level: ' || 'model item id::'||to_char(pModelItemId),2);
1438:
1439: IF PG_DEBUG <> 0 THEN
1440: oe_debug_pub.add('populate_plan_level: ' || 'top ato line id::'||to_char(lTopAtoLineId),2);
1441:
1442: oe_debug_pub.add('populate_plan_level: ' || 'rcv org id::'||to_char(pRcvOrgId),2);
1443:
1444: oe_debug_pub.add('populate_plan_level: ' || 'model item id::'||to_char(pModelItemId),2);
1445: END IF;
1446:
1440: oe_debug_pub.add('populate_plan_level: ' || 'top ato line id::'||to_char(lTopAtoLineId),2);
1441:
1442: oe_debug_pub.add('populate_plan_level: ' || 'rcv org id::'||to_char(pRcvOrgId),2);
1443:
1444: oe_debug_pub.add('populate_plan_level: ' || 'model item id::'||to_char(pModelItemId),2);
1445: END IF;
1446:
1447: -- Added by Renga Kannan on 08/23/01 for procuring configuration
1448:
1446:
1447: -- Added by Renga Kannan on 08/23/01 for procuring configuration
1448:
1449: IF PG_DEBUG <> 0 THEN
1450: oe_debug_pub.add('populate_plan_level: ' || 'Parent ATO line id = '||l_parent_ato_line_id,1);
1451:
1452: oe_debug_pub.add('populate_plan_level: ' || 'Parent source type = '||l_source_type,1);
1453: END IF;
1454:
1448:
1449: IF PG_DEBUG <> 0 THEN
1450: oe_debug_pub.add('populate_plan_level: ' || 'Parent ATO line id = '||l_parent_ato_line_id,1);
1451:
1452: oe_debug_pub.add('populate_plan_level: ' || 'Parent source type = '||l_source_type,1);
1453: END IF;
1454:
1455:
1456:
1461:
1462: v_source_type_code := 'INTERNAL' ;
1463:
1464: IF PG_DEBUG <> 0 THEN
1465: oe_debug_pub.add('populate_plan_level: ' || ' pc bom source type code = '|| v_source_type_code ,1);
1466: END IF;
1467: else
1468:
1469: select source_type_code
1471: from oe_order_lines_all
1472: where line_id = pLineId ;
1473:
1474: IF PG_DEBUG <> 0 THEN
1475: oe_debug_pub.add('populate_plan_level: ' || ' non pc bom source type code = '|| v_source_type_code ,1);
1476: END IF;
1477:
1478: end if ;
1479:
1477:
1478: end if ;
1479:
1480: IF PG_DEBUG <> 0 THEN
1481: oe_debug_pub.add('populate_plan_level: ' || 'source type code = '|| v_source_type_code ,1);
1482: END IF;
1483:
1484:
1485: lStmtNumber := 30;
1501: -- will be copied
1502: lStmtNumber := 35;
1503: l_source_type := 4;
1504: IF PG_DEBUG <> 0 THEN
1505: oe_debug_pub.add('populate_plan_level: ' || ' This is part of Buy model... No need to look for sourcing...',1);
1506: END IF;
1507:
1508:
1509:
1566: and rcv_org_id = pRcvOrgId
1567: and model_item_id = pModelItemId);
1568:
1569: IF PG_DEBUG <> 0 THEN
1570: oe_debug_pub.add('Get_All_Item_Orgs: ' || 'Inserted in BCSO for procured child model same org id, rcv org id ' || SQL%rowcount
1571: || ' at stmt ' || to_char(lStmtNumber) ,2);
1572:
1573: oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || ' for model ' || to_char( pmodelitemid )
1574: || ' line ' || to_char(pLineId) ,2);
1569: IF PG_DEBUG <> 0 THEN
1570: oe_debug_pub.add('Get_All_Item_Orgs: ' || 'Inserted in BCSO for procured child model same org id, rcv org id ' || SQL%rowcount
1571: || ' at stmt ' || to_char(lStmtNumber) ,2);
1572:
1573: oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || ' for model ' || to_char( pmodelitemid )
1574: || ' line ' || to_char(pLineId) ,2);
1575: END IF;
1576:
1577:
1585: lStmtNumber := 40;
1586:
1587: IF lMrpAssignmentSet is null THEN
1588: IF PG_DEBUG <> 0 THEN
1589: oe_debug_pub.add('populate_plan_level: ' || 'Default assignment set is null',1);
1590: END IF;
1591:
1592:
1593: -- added by Renga Kannan on 08/21/01
1609: and organization_id = pRcvOrgId;
1610:
1611:
1612: IF PG_DEBUG <> 0 THEN
1613: oe_debug_pub.add('populate_plan_level: ' || 'Make buy code:: 1 means make 2 means buy',1);
1614:
1615: oe_debug_pub.add('populate_plan_level: ' || 'Planning make buy code for this item is ='||to_char(l_make_buy_code),1);
1616: END IF;
1617:
1611:
1612: IF PG_DEBUG <> 0 THEN
1613: oe_debug_pub.add('populate_plan_level: ' || 'Make buy code:: 1 means make 2 means buy',1);
1614:
1615: oe_debug_pub.add('populate_plan_level: ' || 'Planning make buy code for this item is ='||to_char(l_make_buy_code),1);
1616: END IF;
1617:
1618: IF l_make_buy_code = 2 then
1619: l_source_type := 3; ----- Buy Type
1698: and organization_id = pRcvOrgId
1699: and model_item_id = pModelItemId);
1700:
1701: IF PG_DEBUG <> 0 THEN
1702: oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || SQL%ROWCOUNT || ' at stmt ' || to_char(lStmtNumber) ,2);
1703:
1704: oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || ' for model ' || to_char( pmodelitemid )
1705: || ' line ' || to_char(plineid) , 2 );
1706: END IF;
1700:
1701: IF PG_DEBUG <> 0 THEN
1702: oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || SQL%ROWCOUNT || ' at stmt ' || to_char(lStmtNumber) ,2);
1703:
1704: oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || ' for model ' || to_char( pmodelitemid )
1705: || ' line ' || to_char(plineid) , 2 );
1706: END IF;
1707:
1708:
1711:
1712: lStmtNumber := 80;
1713:
1714: IF PG_DEBUG <> 0 THEN
1715: oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Default assignment set is '||to_char(lMrpAssignmentSet),2);
1716: END IF;
1717:
1718:
1719:
1736:
1737:
1738: IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1739: IF PG_DEBUG <> 0 THEN
1740: oe_debug_pub.add('get_all_item_orgs: ' || 'process_sourcing_chain returned with unexp error',1);
1741: END IF;
1742: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1743:
1744: ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1742: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1743:
1744: ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1745: IF PG_DEBUG <> 0 THEN
1746: oe_debug_pub.add('get_all_item_orgs: ' || 'process_sourcing_chain returned with exp error',1);
1747: END IF;
1748: raise FND_API.G_EXC_ERROR;
1749: END IF;
1750:
1749: END IF;
1750:
1751:
1752: IF PG_DEBUG <> 0 THEN
1753: oe_debug_pub.add('get_all_item_orgs: ' || 'after calling process_sourcing_chain::x_return_status::'||x_return_status,2);
1754: END IF;
1755:
1756:
1757:
1831: and rcv_org_id = pRcvOrgId
1832: and model_item_id = pModelItemId);
1833:
1834: IF PG_DEBUG <> 0 THEN
1835: oe_debug_pub.add('Get_All_Item_Orgs: ' || 'Inserted in BCSO for transfer same org id, rcv org id ' || SQL%rowcount
1836: || ' at stmt ' || to_char(lStmtNumber) ,2);
1837:
1838: oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || ' for model ' || to_char( pmodelitemid )
1839: || ' line ' || to_char(plineid) ,2);
1834: IF PG_DEBUG <> 0 THEN
1835: oe_debug_pub.add('Get_All_Item_Orgs: ' || 'Inserted in BCSO for transfer same org id, rcv org id ' || SQL%rowcount
1836: || ' at stmt ' || to_char(lStmtNumber) ,2);
1837:
1838: oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || ' for model ' || to_char( pmodelitemid )
1839: || ' line ' || to_char(plineid) ,2);
1840: END IF;
1841:
1842:
1843:
1844:
1845: lStmtNumber := 180;
1846: IF PG_DEBUG <> 0 THEN
1847: oe_debug_pub.add('get_all_item_orgs : ' || 'done recursive chain for model '||to_char(pmodelitemid ), 1);
1848: END IF;
1849:
1850:
1851:
1848: END IF;
1849:
1850:
1851:
1852: oe_debug_pub.add( ' procured_model_bcso_override mode ' || p_mode , 1);
1853:
1854: if( p_mode = 'AUTOCONFIG' ) then
1855: oe_debug_pub.add( ' going to call procured_model_bcso_override ' , 1);
1856:
1851:
1852: oe_debug_pub.add( ' procured_model_bcso_override mode ' || p_mode , 1);
1853:
1854: if( p_mode = 'AUTOCONFIG' ) then
1855: oe_debug_pub.add( ' going to call procured_model_bcso_override ' , 1);
1856:
1857: lStmtNumber := 200;
1858:
1859:
1889:
1890: EXCEPTION
1891: when FND_API.G_EXC_ERROR then
1892: IF PG_DEBUG <> 0 THEN
1893: oe_debug_pub.add('populate_plan_level: ' || 'Get_All_item_orgs::exp error::'||to_char(lStmtNumber)||'::'||sqlerrm,1);
1894: END IF;
1895: x_return_status := FND_API.G_RET_STS_ERROR;
1896: CTO_MSG_PUB.Count_And_Get
1897: (p_msg_count => x_msg_count
1900: return(0);
1901:
1902: when FND_API.G_EXC_UNEXPECTED_ERROR then
1903: IF PG_DEBUG <> 0 THEN
1904: oe_debug_pub.add('populate_plan_level: ' || 'Get_All_item_orgs::unexp error::'||to_char(lStmtNumber)||'::'||sqlerrm,1);
1905: END IF;
1906: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1907: CTO_MSG_PUB.Count_And_Get (
1908: p_msg_count => x_msg_count
1911: return(0);
1912:
1913: when OTHERS then
1914: IF PG_DEBUG <> 0 THEN
1915: oe_debug_pub.add('populate_plan_level: ' || 'Get_All_item_orgs::others::'||to_char(lStmtNumber)||'::'||sqlerrm,1);
1916: END IF;
1917: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1918: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1919: FND_MSG_PUB.Add_Exc_Msg
1988:
1989:
1990:
1991: IF PG_DEBUG <> 0 THEN
1992: oe_debug_pub.add( 'Entered process sourcing chain ' , 1 ) ;
1993: oe_debug_pub.add( 'Entered process sourcing chain line ' || p_line_id , 1 ) ;
1994: oe_debug_pub.add( 'Entered process sourcing chain org' || p_organization_id , 1 ) ;
1995: oe_debug_pub.add( 'Entered process sourcing chain model item ' || p_model_item_id , 1 ) ;
1996: END IF ;
1989:
1990:
1991: IF PG_DEBUG <> 0 THEN
1992: oe_debug_pub.add( 'Entered process sourcing chain ' , 1 ) ;
1993: oe_debug_pub.add( 'Entered process sourcing chain line ' || p_line_id , 1 ) ;
1994: oe_debug_pub.add( 'Entered process sourcing chain org' || p_organization_id , 1 ) ;
1995: oe_debug_pub.add( 'Entered process sourcing chain model item ' || p_model_item_id , 1 ) ;
1996: END IF ;
1997:
1990:
1991: IF PG_DEBUG <> 0 THEN
1992: oe_debug_pub.add( 'Entered process sourcing chain ' , 1 ) ;
1993: oe_debug_pub.add( 'Entered process sourcing chain line ' || p_line_id , 1 ) ;
1994: oe_debug_pub.add( 'Entered process sourcing chain org' || p_organization_id , 1 ) ;
1995: oe_debug_pub.add( 'Entered process sourcing chain model item ' || p_model_item_id , 1 ) ;
1996: END IF ;
1997:
1998:
1991: IF PG_DEBUG <> 0 THEN
1992: oe_debug_pub.add( 'Entered process sourcing chain ' , 1 ) ;
1993: oe_debug_pub.add( 'Entered process sourcing chain line ' || p_line_id , 1 ) ;
1994: oe_debug_pub.add( 'Entered process sourcing chain org' || p_organization_id , 1 ) ;
1995: oe_debug_pub.add( 'Entered process sourcing chain model item ' || p_model_item_id , 1 ) ;
1996: END IF ;
1997:
1998:
1999: if( to_char(p_organization_id ) = px_concat_org_id ) then
2007: v_buy_traversed := FALSE ;
2008:
2009:
2010: IF PG_DEBUG <> 0 THEN
2011: oe_debug_pub.add( 'calling query sourcing org ' , 1 ) ;
2012: END IF;
2013:
2014:
2015: if( p_mode = 'AUTOCONFIG' ) then
2083:
2084:
2085:
2086: IF PG_DEBUG <> 0 THEN
2087: oe_debug_pub.add( 'output query sourcing org rule ' || v_t_sourcing_info.sourcing_rule_id.count , 1 ) ;
2088: oe_debug_pub.add( 'output query sourcing org src org ' || v_t_sourcing_info.source_organization_id.count , 1 ) ;
2089: oe_debug_pub.add( 'output query sourcing org src type' || v_t_sourcing_info.source_type.count , 1 ) ;
2090: END IF;
2091:
2084:
2085:
2086: IF PG_DEBUG <> 0 THEN
2087: oe_debug_pub.add( 'output query sourcing org rule ' || v_t_sourcing_info.sourcing_rule_id.count , 1 ) ;
2088: oe_debug_pub.add( 'output query sourcing org src org ' || v_t_sourcing_info.source_organization_id.count , 1 ) ;
2089: oe_debug_pub.add( 'output query sourcing org src type' || v_t_sourcing_info.source_type.count , 1 ) ;
2090: END IF;
2091:
2092:
2085:
2086: IF PG_DEBUG <> 0 THEN
2087: oe_debug_pub.add( 'output query sourcing org rule ' || v_t_sourcing_info.sourcing_rule_id.count , 1 ) ;
2088: oe_debug_pub.add( 'output query sourcing org src org ' || v_t_sourcing_info.source_organization_id.count , 1 ) ;
2089: oe_debug_pub.add( 'output query sourcing org src type' || v_t_sourcing_info.source_type.count , 1 ) ;
2090: END IF;
2091:
2092:
2093:
2096: FOR i in 1..v_t_sourcing_info.source_type.count
2097: LOOP
2098:
2099: IF PG_DEBUG <> 0 THEN
2100: oe_debug_pub.add( 'output query sourcing org type ' || v_t_sourcing_info.source_type(i) , 1 ) ;
2101: END IF;
2102:
2103: /* Reinitialize variables */
2104: l_curr_src_org := null ;
2107:
2108: IF PG_DEBUG <> 0 THEN
2109: if( v_t_sourcing_info.source_type(i) = 1 ) then
2110:
2111: oe_debug_pub.add( 'output query sourcing org type 1 ' , 1) ;
2112:
2113: elsif ( v_t_sourcing_info.source_type(i) = 2 ) then
2114: oe_debug_pub.add( 'output query sourcing org type 2 ' , 1) ;
2115:
2110:
2111: oe_debug_pub.add( 'output query sourcing org type 1 ' , 1) ;
2112:
2113: elsif ( v_t_sourcing_info.source_type(i) = 2 ) then
2114: oe_debug_pub.add( 'output query sourcing org type 2 ' , 1) ;
2115:
2116:
2117: elsif ( v_t_sourcing_info.source_type(i) = 3 ) then
2118:
2115:
2116:
2117: elsif ( v_t_sourcing_info.source_type(i) = 3 ) then
2118:
2119: oe_debug_pub.add( 'output query sourcing org type 3 ' , 1) ;
2120:
2121:
2122: else
2123:
2120:
2121:
2122: else
2123:
2124: oe_debug_pub.add( 'output query sourcing org type else ' , 1) ;
2125:
2126: end if ;
2127: END IF;
2128:
2131: if( v_t_sourcing_info.source_type(i) in ( 1, 2 ) ) then
2132:
2133:
2134: IF PG_DEBUG <> 0 THEN
2135: oe_debug_pub.add( ' came into type 1,2 ' , 1 ) ;
2136: END IF;
2137:
2138:
2139: begin
2149: exception
2150: when others then
2151:
2152: IF PG_DEBUG <> 0 THEN
2153: oe_debug_pub.add( ' errored into type 1,2 at ' || lStmtNum || ' err ' || SQLERRM , 1 ) ;
2154: END IF;
2155: end ;
2156:
2157: IF PG_DEBUG <> 0 THEN
2154: END IF;
2155: end ;
2156:
2157: IF PG_DEBUG <> 0 THEN
2158: oe_debug_pub.add( ' value for l_curr_src_org ' || l_curr_src_org , 1 ) ;
2159: oe_debug_pub.add( ' value for l_source_type ' || l_source_type , 1 ) ;
2160: oe_debug_pub.add( ' value for l_curr_rank ' || l_curr_rank , 1 ) ;
2161: END IF;
2162:
2155: end ;
2156:
2157: IF PG_DEBUG <> 0 THEN
2158: oe_debug_pub.add( ' value for l_curr_src_org ' || l_curr_src_org , 1 ) ;
2159: oe_debug_pub.add( ' value for l_source_type ' || l_source_type , 1 ) ;
2160: oe_debug_pub.add( ' value for l_curr_rank ' || l_curr_rank , 1 ) ;
2161: END IF;
2162:
2163:
2156:
2157: IF PG_DEBUG <> 0 THEN
2158: oe_debug_pub.add( ' value for l_curr_src_org ' || l_curr_src_org , 1 ) ;
2159: oe_debug_pub.add( ' value for l_source_type ' || l_source_type , 1 ) ;
2160: oe_debug_pub.add( ' value for l_curr_rank ' || l_curr_rank , 1 ) ;
2161: END IF;
2162:
2163:
2164:
2166:
2167: if( l_source_type = 1 ) then
2168:
2169: IF PG_DEBUG <> 0 THEN
2170: oe_debug_pub.add( 'going to check for circular sourcing ' , 1 ) ;
2171: /* check for circular sourcing in bcso */
2172: oe_debug_pub.add( 'CIRCULAR SOURCE CHECK ' || px_concat_org_id ) ;
2173: END IF;
2174:
2168:
2169: IF PG_DEBUG <> 0 THEN
2170: oe_debug_pub.add( 'going to check for circular sourcing ' , 1 ) ;
2171: /* check for circular sourcing in bcso */
2172: oe_debug_pub.add( 'CIRCULAR SOURCE CHECK ' || px_concat_org_id ) ;
2173: END IF;
2174:
2175:
2176: v_org_check := to_char(l_curr_src_org) ;
2175:
2176: v_org_check := to_char(l_curr_src_org) ;
2177:
2178: IF PG_DEBUG <> 0 THEN
2179: oe_debug_pub.add( 'CIRCULAR SOURCE CHECK px_concat_org_id ' || px_concat_org_id || ' v_org_check ' || v_org_check ) ;
2180: END IF;
2181:
2182:
2183: if( instr( px_concat_org_id , v_org_check ) > 0 ) then
2182:
2183: if( instr( px_concat_org_id , v_org_check ) > 0 ) then
2184: v_circular_src_exists := 'Y' ;
2185: IF PG_DEBUG <> 0 THEN
2186: oe_debug_pub.add( 'CIRCULAR SOURCE DETECTED ' ) ;
2187: END IF;
2188:
2189: else
2190:
2198: if( v_circular_src_exists = 'Y' OR ( l_source_type = 1 and p_organization_id = l_curr_src_org) ) then
2199:
2200: lStmtNum := 5;
2201: IF PG_DEBUG <> 0 THEN
2202: oe_debug_pub.add('process_sourcing_chain: ' || 'Circular sourcing defined for model '
2203: || to_char(p_model_item_id)
2204: || ' in org '
2205: ||to_char(l_curr_src_org) || ' via org ' || to_char(p_organization_id ) , 1);
2206:
2204: || ' in org '
2205: ||to_char(l_curr_src_org) || ' via org ' || to_char(p_organization_id ) , 1);
2206:
2207:
2208: oe_debug_pub.add('process_sourcing_chain: ' || 'Circular sourcing additional info '
2209: || px_concat_org_id || '::' || to_char(l_curr_src_org) , 1) ;
2210:
2211: END IF;
2212:
2220: end if; /* l_source_type = 1 */
2221:
2222:
2223: IF PG_DEBUG <> 0 THEN
2224: oe_debug_pub.add( 'going to insert bcso for type 1,2 ' , 1 ) ;
2225: END IF;
2226:
2227: lStmtNum := 10 ;
2228: insert into bom_cto_src_orgs_b
2283:
2284:
2285:
2286: IF PG_DEBUG <> 0 THEN
2287: oe_debug_pub.add( 'inserted bcso for type 1,2 ' || SQL%rowcount , 1 ) ;
2288: oe_debug_pub.add( 'inserted bcso for type 1,2 rcv ' || p_organization_id || ' org ' || l_curr_src_org , 1 ) ;
2289: END IF;
2290:
2291:
2284:
2285:
2286: IF PG_DEBUG <> 0 THEN
2287: oe_debug_pub.add( 'inserted bcso for type 1,2 ' || SQL%rowcount , 1 ) ;
2288: oe_debug_pub.add( 'inserted bcso for type 1,2 rcv ' || p_organization_id || ' org ' || l_curr_src_org , 1 ) ;
2289: END IF;
2290:
2291:
2292:
2294: elsif( v_t_sourcing_info.source_type(i) = 3 and NOT v_buy_traversed ) then
2295:
2296: v_buy_traversed := TRUE ;
2297:
2298: oe_debug_pub.add( ' came into type 3 ' , 1 ) ;
2299:
2300: lStmtNum := 20 ;
2301:
2302: begin
2312: exception
2313: when others then
2314:
2315: IF PG_DEBUG <> 0 THEN
2316: oe_debug_pub.add( ' errored into type 3 at ' || lStmtNum || ' err ' || SQLERRM , 1 ) ;
2317: END IF;
2318:
2319: end ;
2320:
2380:
2381:
2382:
2383: IF PG_DEBUG <> 0 THEN
2384: oe_debug_pub.add( 'inserted bcso for type 3 ' || SQL%rowcount , 1 ) ;
2385: oe_debug_pub.add( 'inserted bcso for type 3 rcv ' || p_organization_id || ' org ' || l_curr_src_org , 1 ) ;
2386: END IF;
2387:
2388:
2381:
2382:
2383: IF PG_DEBUG <> 0 THEN
2384: oe_debug_pub.add( 'inserted bcso for type 3 ' || SQL%rowcount , 1 ) ;
2385: oe_debug_pub.add( 'inserted bcso for type 3 rcv ' || p_organization_id || ' org ' || l_curr_src_org , 1 ) ;
2386: END IF;
2387:
2388:
2389:
2395:
2396: if( v_t_sourcing_info.source_type(i) = 1 ) then
2397:
2398:
2399: oe_debug_pub.add( 'calling process sourcing chain recursive ' , 1 ) ;
2400:
2401: lStmtNum := 50 ;
2402:
2403: /* implemented using another variable as it is a multipath tree recursion */
2419:
2420:
2421: IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2422: IF PG_DEBUG <> 0 THEN
2423: oe_debug_pub.add('process_sourcing_chain: ' || 'process_sourcing_chain returned with unexp error',1);
2424: END IF;
2425: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2426:
2427: ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2425: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2426:
2427: ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2428: IF PG_DEBUG <> 0 THEN
2429: oe_debug_pub.add('process_sourcing_chain: ' || 'process_sourcing_chain returned with exp error',1);
2430: END IF;
2431: raise FND_API.G_EXC_ERROR;
2432: END IF;
2433:
2432: END IF;
2433:
2434:
2435: IF PG_DEBUG <> 0 THEN
2436: oe_debug_pub.add('process_sourcing_chain: ' || 'after calling process_sourcing_chain::x_return_status::'||x_return_status,2);
2437: END IF;
2438:
2439:
2440: end if;
2451: -- if( v_source_type_code = 'INTERNAL' ) then
2452:
2453:
2454: IF PG_DEBUG <> 0 THEN
2455: oe_debug_pub.add('process_sourcing_chain : ' || 'NDF::End of chain for model '||to_char(p_model_item_id), 1);
2456: oe_debug_pub.add('process_sourcing_chain : ' || 'NDF::End of chain in org '|| p_organization_id , 1);
2457: END IF;
2458:
2459:
2452:
2453:
2454: IF PG_DEBUG <> 0 THEN
2455: oe_debug_pub.add('process_sourcing_chain : ' || 'NDF::End of chain for model '||to_char(p_model_item_id), 1);
2456: oe_debug_pub.add('process_sourcing_chain : ' || 'NDF::End of chain in org '|| p_organization_id , 1);
2457: END IF;
2458:
2459:
2460: lStmtNumber := 70;
2473: EXCEPTION
2474: WHEN NO_DATA_FOUND THEN
2475:
2476: IF PG_DEBUG <> 0 THEN
2477: oe_debug_pub.add('process_sourcing_chain: ' || 'Inventory_item_id = '|| to_char(p_model_item_id ),1);
2478:
2479: oe_debug_pub.add('process_sourcing_chain: ' || 'Organization id = '|| to_char(p_organization_id),1);
2480:
2481: oe_debug_pub.add('process_sourcing_chain: ' || 'ERROR::The item is not defined in the sourcing org',1);
2475:
2476: IF PG_DEBUG <> 0 THEN
2477: oe_debug_pub.add('process_sourcing_chain: ' || 'Inventory_item_id = '|| to_char(p_model_item_id ),1);
2478:
2479: oe_debug_pub.add('process_sourcing_chain: ' || 'Organization id = '|| to_char(p_organization_id),1);
2480:
2481: oe_debug_pub.add('process_sourcing_chain: ' || 'ERROR::The item is not defined in the sourcing org',1);
2482: END IF;
2483:
2477: oe_debug_pub.add('process_sourcing_chain: ' || 'Inventory_item_id = '|| to_char(p_model_item_id ),1);
2478:
2479: oe_debug_pub.add('process_sourcing_chain: ' || 'Organization id = '|| to_char(p_organization_id),1);
2480:
2481: oe_debug_pub.add('process_sourcing_chain: ' || 'ERROR::The item is not defined in the sourcing org',1);
2482: END IF;
2483:
2484:
2485: -- The following message handling is modified by Renga Kannan
2579: and model_item_id = p_model_item_id ) ;
2580:
2581:
2582: IF PG_DEBUG <> 0 THEN
2583: oe_debug_pub.add( 'inserted bcso for end of chain ' || SQL%rowcount , 1 ) ;
2584: oe_debug_pub.add( 'inserted bcso for end of chain ' || p_organization_id ||
2585: ' org ' || p_organization_id
2586: , 1 ) ;
2587: END IF;
2580:
2581:
2582: IF PG_DEBUG <> 0 THEN
2583: oe_debug_pub.add( 'inserted bcso for end of chain ' || SQL%rowcount , 1 ) ;
2584: oe_debug_pub.add( 'inserted bcso for end of chain ' || p_organization_id ||
2585: ' org ' || p_organization_id
2586: , 1 ) ;
2587: END IF;
2588:
2592:
2593:
2594:
2595: IF PG_DEBUG <> 0 THEN
2596: oe_debug_pub.add('process_sourcing_chain: ' || 'end p_organization_id '||to_char(p_organization_id), 1);
2597: oe_debug_pub.add('process_sourcing_chain: ' || 'end px_concat_org_id '|| px_concat_org_id , 1);
2598: END IF;
2599:
2600:
2593:
2594:
2595: IF PG_DEBUG <> 0 THEN
2596: oe_debug_pub.add('process_sourcing_chain: ' || 'end p_organization_id '||to_char(p_organization_id), 1);
2597: oe_debug_pub.add('process_sourcing_chain: ' || 'end px_concat_org_id '|| px_concat_org_id , 1);
2598: END IF;
2599:
2600:
2601:
2604:
2605: EXCEPTION
2606: WHEN fnd_api.g_exc_error THEN
2607: IF PG_DEBUG <> 0 THEN
2608: oe_debug_pub.add('process_sourcing_chain: ' || 'Exception in stmt num: '
2609: || to_char(lStmtNum), 1);
2610: END IF;
2611: x_return_status := FND_API.G_RET_STS_ERROR;
2612: -- Get message count and data
2615: , p_msg_data => x_msg_data
2616: );
2617: WHEN fnd_api.g_exc_unexpected_error THEN
2618: IF PG_DEBUG <> 0 THEN
2619: oe_debug_pub.add('process_sourcing_chain: ' || ' Unexpected Exception in stmt num: '
2620: || to_char(lStmtNum), 1);
2621: END IF;
2622: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2623: -- Get message count and data
2627: );
2628: WHEN OTHERS then
2629: IF PG_DEBUG <> 0 THEN
2630:
2631: oe_debug_pub.add('process_sourcing_chain: ' || 'Others Exception in stmt num: '
2632: || to_char(lStmtNum), 1);
2633: oe_debug_pub.add('process_sourcing_chain: ' || 'errormsg='||sqlerrm, 1);
2634: END IF;
2635: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2629: IF PG_DEBUG <> 0 THEN
2630:
2631: oe_debug_pub.add('process_sourcing_chain: ' || 'Others Exception in stmt num: '
2632: || to_char(lStmtNum), 1);
2633: oe_debug_pub.add('process_sourcing_chain: ' || 'errormsg='||sqlerrm, 1);
2634: END IF;
2635: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2636: -- Get message count and data
2637: cto_msg_pub.count_and_get
2697: -- The following initialize_assignment_set is used to initialize the global variable
2698:
2699: IF gMrpAssignmentSet is null THEN
2700: IF PG_DEBUG <> 0 THEN
2701: oe_debug_pub.add('query_sourcing_org_ms: ' || 'Initializing the assignment set',5);
2702: END IF;
2703: initialize_assignment_set(x_return_status);
2704: if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2705: IF PG_DEBUG <> 0 THEN
2702: END IF;
2703: initialize_assignment_set(x_return_status);
2704: if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2705: IF PG_DEBUG <> 0 THEN
2706: oe_debug_pub.add('query_sourcing_org_ms: ' || 'Error in initializing assignment set',5);
2707: END IF;
2708: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2709: end if;
2710: End IF;
2782:
2783: close item_sources ;
2784:
2785:
2786: oe_debug_pub.add('query_sourcing_org_ms: ' || '****$$$$ count ' || p_t_sourcing_info.source_organization_id.count , 1 ) ;
2787:
2788:
2789: for i in 1..p_t_sourcing_info.sourcing_rule_id.count
2790: loop
2788:
2789: for i in 1..p_t_sourcing_info.sourcing_rule_id.count
2790: loop
2791:
2792: oe_debug_pub.add('query_sourcing_org_ms: ' || '****$$$$ org ' || p_t_sourcing_info.source_organization_id(i)
2793: || '****$$$$ rule ' || p_t_sourcing_info.sourcing_rule_id(i)
2794: || '****$$$$ type ' || p_t_sourcing_info.source_type(i)
2795: || '****$$$$ rank ' || p_t_sourcing_info.rank(i)
2796: || '****$$$$ assig id ' || p_t_sourcing_info.assignment_id(i) , 1 ) ;
2816:
2817: EXCEPTION
2818: WHEN NO_DATA_FOUND THEN
2819: IF PG_DEBUG <> 0 THEN
2820: oe_debug_pub.add('query_sourcing_org_ms: ' || ' came into no data when finding source ' || to_char(l_stmt_num ) , 1 );
2821: END IF;
2822: /* removed no sourcing flag as cascading of sourcing rules will
2823: ** be continued till no more sourcing rules can be cascaded
2824: */
2848:
2849:
2850: WHEN OTHERS THEN
2851: IF PG_DEBUG <> 0 THEN
2852: oe_debug_pub.add('query_sourcing_org_ms: ' || 'query_sourcing_org_ms::others:: ' ||
2853: to_char(l_stmt_num) || '::' ||
2854: ' came into others when finding source ' , 1 );
2855:
2856: oe_debug_pub.add('query_sourcing_org_ms: ' || ' SQLCODE ' || SQLCODE , 1 );
2852: oe_debug_pub.add('query_sourcing_org_ms: ' || 'query_sourcing_org_ms::others:: ' ||
2853: to_char(l_stmt_num) || '::' ||
2854: ' came into others when finding source ' , 1 );
2855:
2856: oe_debug_pub.add('query_sourcing_org_ms: ' || ' SQLCODE ' || SQLCODE , 1 );
2857:
2858: oe_debug_pub.add('query_sourcing_org_ms: ' || ' SQLERRM ' || SQLERRM , 1 );
2859:
2860: oe_debug_pub.add('query_sourcing_org_ms: ' || ' came into others when finding source ' , 1 );
2854: ' came into others when finding source ' , 1 );
2855:
2856: oe_debug_pub.add('query_sourcing_org_ms: ' || ' SQLCODE ' || SQLCODE , 1 );
2857:
2858: oe_debug_pub.add('query_sourcing_org_ms: ' || ' SQLERRM ' || SQLERRM , 1 );
2859:
2860: oe_debug_pub.add('query_sourcing_org_ms: ' || ' came into others when finding source ' , 1 );
2861: END IF;
2862:
2856: oe_debug_pub.add('query_sourcing_org_ms: ' || ' SQLCODE ' || SQLCODE , 1 );
2857:
2858: oe_debug_pub.add('query_sourcing_org_ms: ' || ' SQLERRM ' || SQLERRM , 1 );
2859:
2860: oe_debug_pub.add('query_sourcing_org_ms: ' || ' came into others when finding source ' , 1 );
2861: END IF;
2862:
2863: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2864:
2938:
2939:
2940: IF lMrpAssignmentSet is null THEN
2941: IF PG_DEBUG <> 0 THEN
2942: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Default assignment set is null, returning from create_sourcing_rules procedure',1);
2943: END IF;
2944: return;
2945: ELSE
2946: IF PG_DEBUG <> 0 THEN
2943: END IF;
2944: return;
2945: ELSE
2946: IF PG_DEBUG <> 0 THEN
2947: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Default assignment set is '||to_char(lMrpAssignmentSet),2);
2948: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Upgrade assignment set is '||to_char(lUPGAssignmentSet),2);
2949: END IF;
2950: END IF;
2951:
2944: return;
2945: ELSE
2946: IF PG_DEBUG <> 0 THEN
2947: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Default assignment set is '||to_char(lMrpAssignmentSet),2);
2948: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Upgrade assignment set is '||to_char(lUPGAssignmentSet),2);
2949: END IF;
2950: END IF;
2951:
2952: --
2995: EXCEPTION
2996: WHEN NO_DATA_FOUND THEN
2997:
2998: IF PG_DEBUG <> 0 THEN
2999: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'There is no sourcing rule defined ',1);
3000: END IF;
3001: raise no_sourcing_defined;
3002:
3003: END;
3003: END;
3004:
3005:
3006: IF PG_DEBUG <> 0 THEN
3007: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'lAssnType::'||to_char(lAssignmentType)||'::lAssnId::'||to_char(lAssignmentId),2);
3008: END IF;
3009:
3010: --
3011: -- copy assignment into lAssignmentRec
3101: FROM MRP_SR_ASSIGNMENTS
3102: WHERE ASSIGNMENT_ID = lAssignmentId;
3103:
3104: IF PG_DEBUG <> 0 THEN
3105: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'after query row',2);
3106: END IF;
3107:
3108: --
3109: -- check if this assignment already exists for config item
3111: lStmtNum := 35;
3112: BEGIN
3113:
3114: IF PG_DEBUG <> 0 THEN
3115: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment_set_id::'||to_char(lAssignmentRec.assignment_set_id),2);
3116:
3117: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
3118:
3119: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
3113:
3114: IF PG_DEBUG <> 0 THEN
3115: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment_set_id::'||to_char(lAssignmentRec.assignment_set_id),2);
3116:
3117: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
3118:
3119: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
3120:
3121: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
3115: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment_set_id::'||to_char(lAssignmentRec.assignment_set_id),2);
3116:
3117: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
3118:
3119: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
3120:
3121: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
3122:
3123: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
3117: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
3118:
3119: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
3120:
3121: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
3122:
3123: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
3124:
3125: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
3119: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
3120:
3121: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
3122:
3123: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
3124:
3125: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
3126:
3127: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
3121: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
3122:
3123: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
3124:
3125: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
3126:
3127: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
3128:
3129: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'category_id:: '||to_char(lAssignmentRec.category_id),2);
3123: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
3124:
3125: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
3126:
3127: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
3128:
3129: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'category_id:: '||to_char(lAssignmentRec.category_id),2);
3130: END IF;
3131:
3125: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
3126:
3127: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
3128:
3129: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'category_id:: '||to_char(lAssignmentRec.category_id),2);
3130: END IF;
3131:
3132: -- bug 6617686
3133: IF pConfigId IS NOT NULL THEN
3158: -- end: bug 6617686
3159:
3160: IF lAssignmentExists = 1 THEN
3161: IF PG_DEBUG <> 0 THEN
3162: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment exists already, do not recreate',2);
3163: END IF;
3164: return;
3165: END IF;
3166:
3166:
3167: EXCEPTION
3168: when NO_DATA_FOUND then
3169: IF PG_DEBUG <> 0 THEN
3170: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment does not exist, create it',2);
3171: END IF;
3172: when OTHERS then
3173: IF PG_DEBUG <> 0 THEN
3174: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'others exception while checking ifassignment exists, not handling, creating assignment:: '||sqlerrm,2);
3170: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment does not exist, create it',2);
3171: END IF;
3172: when OTHERS then
3173: IF PG_DEBUG <> 0 THEN
3174: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'others exception while checking ifassignment exists, not handling, creating assignment:: '||sqlerrm,2);
3175: END IF;
3176: END;
3177:
3178: --
3227: lAssignmentTbl(1).db_flag := NULL;
3228: lAssignmentTbl(1).operation := MRP_Globals.G_OPR_CREATE;
3229:
3230: IF PG_DEBUG <> 0 THEN
3231: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'after forming lAssignmentTbl',2);
3232: END IF;
3233:
3234: --
3235: -- form lAssignmentSetRec
3236: --
3237: lStmtNum := 50;
3238: lAssignmentSetRec.operation := MRP_Globals.G_OPR_NONE;
3239: IF PG_DEBUG <> 0 THEN
3240: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'after forming lAssignmentSetRec',2);
3241: END IF;
3242:
3243: --
3244: -- call mrp API to insert rec into assignment set
3244: -- call mrp API to insert rec into assignment set
3245: --
3246: lStmtNum := 60;
3247: IF PG_DEBUG <> 0 THEN
3248: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'before Process_Assignment',2);
3249: END IF;
3250:
3251: -- currently, not passing commented out parameters, need to
3252: -- confirm with raghu, confirmed with stupe
3270: );
3271:
3272: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3273: IF PG_DEBUG <> 0 THEN
3274: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'unexp error in process_assignment::'||sqlerrm,1);
3275: END IF;
3276: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3277:
3278: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3276: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3277:
3278: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3279: IF PG_DEBUG <> 0 THEN
3280: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'error in process_assignment::'||sqlerrm,1);
3281: END IF;
3282:
3283: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: count:'||l_msg_count , 1 );
3284:
3279: IF PG_DEBUG <> 0 THEN
3280: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'error in process_assignment::'||sqlerrm,1);
3281: END IF;
3282:
3283: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: count:'||l_msg_count , 1 );
3284:
3285: IF l_msg_count > 0 THEN
3286: FOR l_index IN 1..l_msg_count LOOP
3287: l_msg_data := fnd_msg_pub.get(
3287: l_msg_data := fnd_msg_pub.get(
3288: p_msg_index => l_index,
3289: p_encoded => FND_API.G_FALSE);
3290:
3291: oe_debug_pub.add( 'CTO_MSUTIL_PUB.create_sourcing_rule: ' || substr(l_msg_data,1,250) , 1 );
3292: END LOOP;
3293:
3294: oe_debug_pub.add(' CTO_MSUTIL_PUB.create_sourcing_rules: MSG:'|| xAssignmentSetRec.return_status);
3295: END IF;
3290:
3291: oe_debug_pub.add( 'CTO_MSUTIL_PUB.create_sourcing_rule: ' || substr(l_msg_data,1,250) , 1 );
3292: END LOOP;
3293:
3294: oe_debug_pub.add(' CTO_MSUTIL_PUB.create_sourcing_rules: MSG:'|| xAssignmentSetRec.return_status);
3295: END IF;
3296:
3297: oe_debug_pub.add('Failure!' , 1 );
3298:
3293:
3294: oe_debug_pub.add(' CTO_MSUTIL_PUB.create_sourcing_rules: MSG:'|| xAssignmentSetRec.return_status);
3295: END IF;
3296:
3297: oe_debug_pub.add('Failure!' , 1 );
3298:
3299:
3300: raise FND_API.G_EXC_ERROR;
3301:
3300: raise FND_API.G_EXC_ERROR;
3301:
3302: END IF;
3303: IF PG_DEBUG <> 0 THEN
3304: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'success in process_assignment',2);
3305: END IF;
3306:
3307: EXCEPTION
3308: When NO_sourcing_defined THEN
3309: null;
3310:
3311: when FND_API.G_EXC_ERROR then
3312: IF PG_DEBUG <> 0 THEN
3313: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Create_Src_Rules::exp error::'||to_char(lStmtNum)||'::'||sqlerrm,1);
3314: END IF;
3315: x_return_status := FND_API.G_RET_STS_ERROR;
3316: CTO_MSG_PUB.Count_And_Get
3317: (p_msg_count => x_msg_count
3319: );
3320:
3321: when FND_API.G_EXC_UNEXPECTED_ERROR then
3322: IF PG_DEBUG <> 0 THEN
3323: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Create_Src_Rules::unexp error::'||to_char(lStmtNum)||'::'||sqlerrm,1);
3324: END IF;
3325: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3326: CTO_MSG_PUB.Count_And_Get
3327: (p_msg_count => x_msg_count
3329: );
3330:
3331: when OTHERS then
3332: IF PG_DEBUG <> 0 THEN
3333: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Create_Src_Rules::others::'||to_char(lStmtNum)||'::'||sqlerrm,1);
3334: END IF;
3335: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3336: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3337: FND_MSG_PUB.Add_Exc_Msg
3444:
3445:
3446: IF lMrpAssignmentSet is null THEN
3447: IF PG_DEBUG <> 0 THEN
3448: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Default assignment set is null, returning from create_sourcing_rules procedure',1);
3449: END IF;
3450: return;
3451: ELSE
3452: IF PG_DEBUG <> 0 THEN
3449: END IF;
3450: return;
3451: ELSE
3452: IF PG_DEBUG <> 0 THEN
3453: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Default assignment set is '||to_char(lMrpAssignmentSet),2);
3454: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'UPG assignment set is '||to_char(lUPGAssignmentSet),2);
3455: END IF;
3456: END IF;
3457:
3450: return;
3451: ELSE
3452: IF PG_DEBUG <> 0 THEN
3453: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Default assignment set is '||to_char(lMrpAssignmentSet),2);
3454: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'UPG assignment set is '||to_char(lUPGAssignmentSet),2);
3455: END IF;
3456: END IF;
3457:
3458: --
3496: EXCEPTION
3497: WHEN NO_DATA_FOUND THEN
3498:
3499: IF PG_DEBUG <> 0 THEN
3500: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'There is no sourcing rule defined ',1);
3501: END IF;
3502: raise no_sourcing_defined;
3503:
3504: END;
3524: exit when c_type3_assignments%notfound ;
3525:
3526:
3527: IF PG_DEBUG <> 0 THEN
3528: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'lAssnType::'||to_char(lAssignmentType)||'::lAssnId::'||to_char(lAssignmentId),2);
3529: END IF;
3530:
3531: --
3532: -- copy assignment into lAssignmentRec
3622: FROM MRP_SR_ASSIGNMENTS
3623: WHERE ASSIGNMENT_ID = lAssignmentId;
3624:
3625: IF PG_DEBUG <> 0 THEN
3626: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'after query row',2);
3627: END IF;
3628:
3629: --
3630: -- check if this assignment already exists for config item
3632: lStmtNum := 35;
3633: BEGIN
3634:
3635: IF PG_DEBUG <> 0 THEN
3636: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment_set_id::'||to_char(lAssignmentRec.assignment_set_id),2);
3637:
3638: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Source assignment_set_id::'||to_char(lMrpAssignmentSet),2);
3639: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Destination assignment_set_id::'||to_char(lUPGAssignmentSet),2);
3640:
3634:
3635: IF PG_DEBUG <> 0 THEN
3636: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment_set_id::'||to_char(lAssignmentRec.assignment_set_id),2);
3637:
3638: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Source assignment_set_id::'||to_char(lMrpAssignmentSet),2);
3639: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Destination assignment_set_id::'||to_char(lUPGAssignmentSet),2);
3640:
3641: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
3642:
3635: IF PG_DEBUG <> 0 THEN
3636: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment_set_id::'||to_char(lAssignmentRec.assignment_set_id),2);
3637:
3638: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Source assignment_set_id::'||to_char(lMrpAssignmentSet),2);
3639: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Destination assignment_set_id::'||to_char(lUPGAssignmentSet),2);
3640:
3641: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
3642:
3643: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
3637:
3638: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Source assignment_set_id::'||to_char(lMrpAssignmentSet),2);
3639: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Destination assignment_set_id::'||to_char(lUPGAssignmentSet),2);
3640:
3641: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
3642:
3643: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
3644:
3645: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
3639: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Destination assignment_set_id::'||to_char(lUPGAssignmentSet),2);
3640:
3641: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
3642:
3643: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
3644:
3645: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
3646:
3647: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
3641: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
3642:
3643: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
3644:
3645: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
3646:
3647: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
3648:
3649: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
3643: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
3644:
3645: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
3646:
3647: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
3648:
3649: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
3650:
3651: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
3645: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
3646:
3647: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
3648:
3649: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
3650:
3651: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
3652:
3653: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'category_id:: '||to_char(lAssignmentRec.category_id),2);
3647: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
3648:
3649: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
3650:
3651: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
3652:
3653: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'category_id:: '||to_char(lAssignmentRec.category_id),2);
3654: END IF;
3655:
3649: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
3650:
3651: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
3652:
3653: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'category_id:: '||to_char(lAssignmentRec.category_id),2);
3654: END IF;
3655:
3656: -- bug 6617686
3657: IF pConfigId IS NOT NULL THEN
3682: -- end : bug 6617686
3683:
3684: IF lAssignmentExists = 1 THEN
3685: IF PG_DEBUG <> 0 THEN
3686: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment exists already, do not recreate',2);
3687: END IF;
3688:
3689:
3690:
3695:
3696: EXCEPTION
3697: when NO_DATA_FOUND then
3698: IF PG_DEBUG <> 0 THEN
3699: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment does not exist, create it',2);
3700: END IF;
3701: when OTHERS then
3702: IF PG_DEBUG <> 0 THEN
3703: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' ||
3699: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment does not exist, create it',2);
3700: END IF;
3701: when OTHERS then
3702: IF PG_DEBUG <> 0 THEN
3703: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' ||
3704: 'others exception while checking ifassignment exists, not handling, creating assignment:: '||sqlerrm,2);
3705: END IF;
3706: END;
3707:
3760: lAssignmentTbl(1).db_flag := NULL;
3761: lAssignmentTbl(1).operation := MRP_Globals.G_OPR_CREATE;
3762:
3763: IF PG_DEBUG <> 0 THEN
3764: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'after forming lAssignmentTbl',2);
3765: END IF;
3766:
3767: --
3768: -- form lAssignmentSetRec
3769: --
3770: lStmtNum := 50;
3771: lAssignmentSetRec.operation := MRP_Globals.G_OPR_NONE;
3772: IF PG_DEBUG <> 0 THEN
3773: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'after forming lAssignmentSetRec',2);
3774: END IF;
3775:
3776: --
3777: -- call mrp API to insert rec into assignment set
3777: -- call mrp API to insert rec into assignment set
3778: --
3779: lStmtNum := 60;
3780: IF PG_DEBUG <> 0 THEN
3781: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'before Process_Assignment',2);
3782: END IF;
3783:
3784: -- currently, not passing commented out parameters, need to
3785: -- confirm with raghu, confirmed with stupe
3803: );
3804:
3805: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3806: IF PG_DEBUG <> 0 THEN
3807: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'unexp error in process_assignment::'||sqlerrm,1);
3808: END IF;
3809: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3810:
3811: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3809: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3810:
3811: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3812: IF PG_DEBUG <> 0 THEN
3813: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'error in process_assignment::'||sqlerrm,1);
3814: END IF;
3815:
3816:
3817: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: count:'||l_msg_count , 1 );
3813: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'error in process_assignment::'||sqlerrm,1);
3814: END IF;
3815:
3816:
3817: oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: count:'||l_msg_count , 1 );
3818:
3819: IF l_msg_count > 0 THEN
3820: FOR l_index IN 1..l_msg_count LOOP
3821: l_msg_data := fnd_msg_pub.get(
3821: l_msg_data := fnd_msg_pub.get(
3822: p_msg_index => l_index,
3823: p_encoded => FND_API.G_FALSE);
3824:
3825: oe_debug_pub.add( 'CTO_MSUTIL_PUB.create_sourcing_rule: ' || substr(l_msg_data,1,250) , 1 );
3826: END LOOP;
3827:
3828: oe_debug_pub.add(' CTO_MSUTIL_PUB.create_sourcing_rules: MSG:'|| xAssignmentSetRec.return_status);
3829: END IF;
3824:
3825: oe_debug_pub.add( 'CTO_MSUTIL_PUB.create_sourcing_rule: ' || substr(l_msg_data,1,250) , 1 );
3826: END LOOP;
3827:
3828: oe_debug_pub.add(' CTO_MSUTIL_PUB.create_sourcing_rules: MSG:'|| xAssignmentSetRec.return_status);
3829: END IF;
3830:
3831: oe_debug_pub.add('Failure!' , 1 );
3832:
3827:
3828: oe_debug_pub.add(' CTO_MSUTIL_PUB.create_sourcing_rules: MSG:'|| xAssignmentSetRec.return_status);
3829: END IF;
3830:
3831: oe_debug_pub.add('Failure!' , 1 );
3832:
3833:
3834: raise FND_API.G_EXC_ERROR;
3835:
3834: raise FND_API.G_EXC_ERROR;
3835:
3836: END IF;
3837: IF PG_DEBUG <> 0 THEN
3838: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'success in process_assignment',2);
3839: END IF;
3840:
3841:
3842: lStmtNum := 70;
3840:
3841:
3842: lStmtNum := 70;
3843:
3844: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'before end_of_loop',2);
3845: <
3846: null ;
3847: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'after end_of_loop',2);
3848: END LOOP;
3843:
3844: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'before end_of_loop',2);
3845: <
3846: null ;
3847: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'after end_of_loop',2);
3848: END LOOP;
3849:
3850:
3851: if( c_type3_assignments%rowcount = 0 ) then
3849:
3850:
3851: if( c_type3_assignments%rowcount = 0 ) then
3852:
3853: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'no sourcing assignments ',2);
3854:
3855: end if;
3856:
3857: lStmtNum := 80;
3855: end if;
3856:
3857: lStmtNum := 80;
3858:
3859: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'before close c_type3_assignments ',2);
3860:
3861: close c_type3_assignments ;
3862:
3863:
3872: null;
3873:
3874: when FND_API.G_EXC_ERROR then
3875: IF PG_DEBUG <> 0 THEN
3876: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || '::exp error::'||to_char(lStmtNum)||'::'||sqlerrm,1);
3877: END IF;
3878: x_return_status := FND_API.G_RET_STS_ERROR;
3879: CTO_MSG_PUB.Count_And_Get
3880: (p_msg_count => x_msg_count
3882: );
3883:
3884: when FND_API.G_EXC_UNEXPECTED_ERROR then
3885: IF PG_DEBUG <> 0 THEN
3886: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || '::unexp error::'||to_char(lStmtNum)||'::'||sqlerrm,1);
3887: END IF;
3888: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3889: CTO_MSG_PUB.Count_And_Get
3890: (p_msg_count => x_msg_count
3892: );
3893:
3894: when OTHERS then
3895: IF PG_DEBUG <> 0 THEN
3896: oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || '::others::'||to_char(lStmtNum)||'::'||sqlerrm,1);
3897: END IF;
3898: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3899: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3900: FND_MSG_PUB.Add_Exc_Msg
3941:
3942: IF( gMrpAssignmentSet is null )
3943: THEN
3944: IF PG_DEBUG <> 0 THEN
3945: oe_debug_pub.add('initialize_assignment_set: ' || '**$$ Default assignment set is null', 1);
3946: END IF;
3947:
3948: ELSE
3949: IF PG_DEBUG <> 0 THEN
3946: END IF;
3947:
3948: ELSE
3949: IF PG_DEBUG <> 0 THEN
3950: oe_debug_pub.add('initialize_assignment_set: ' || 'Default assignment set is '||to_char(gMrpAssignmentSet),2);
3951: END IF;
3952:
3953: l_stmt_num := 10 ;
3954:
3961:
3962: exception
3963: when no_data_found then
3964: IF PG_DEBUG <> 0 THEN
3965: oe_debug_pub.add('initialize_assignment_set: ' || 'The assignment set pointed by the
3966: profile MRP_DEFAULT_ASSIGNMENT_SET
3967: does not exist in the database ' ,1);
3968: END IF;
3969:
3974: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3975: end ;
3976:
3977: IF PG_DEBUG <> 0 THEN
3978: oe_debug_pub.add('initialize_assignment_set: ' || 'Default assignment set name is '||
3979: assign_set_name ,2);
3980: END IF;
3981:
3982: END IF;
3986: when INVALID_MRP_ASSIGNMENT_SET then
3987: x_return_status := FND_API.G_RET_STS_ERROR;
3988:
3989: IF PG_DEBUG <> 0 THEN
3990: oe_debug_pub.add('initialize_assignment_set: ' || 'INITIALIZE_ASSIGNMENT_SET::INVALID ASSIGNMENT SET ::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
3991: END IF;
3992:
3993:
3994: when FND_API.G_EXC_UNEXPECTED_ERROR then
3993:
3994: when FND_API.G_EXC_UNEXPECTED_ERROR then
3995: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3996: IF PG_DEBUG <> 0 THEN
3997: oe_debug_pub.add('initialize_assignment_set: ' || 'INITIALIZE_ASSIGNMENT_SET::unexp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
3998: END IF;
3999:
4000:
4001: when OTHERS then
4000:
4001: when OTHERS then
4002: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4003: IF PG_DEBUG <> 0 THEN
4004: oe_debug_pub.add('initialize_assignment_set: ' || 'INITIALIZE_ASSIGNMENT_SET::others::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
4005: END IF;
4006:
4007:
4008: END initialize_assignment_set ;
4018: i number ;
4019: v_source_type_code varchar2(20) ;
4020: begin
4021:
4022: oe_debug_pub.add( '$$$ insert val into bcso ' , 1 ) ;
4023: oe_debug_pub.add( '$$$ VAL ORGS count ' || p_t_org_list.count , 1 ) ;
4024:
4025:
4026:
4019: v_source_type_code varchar2(20) ;
4020: begin
4021:
4022: oe_debug_pub.add( '$$$ insert val into bcso ' , 1 ) ;
4023: oe_debug_pub.add( '$$$ VAL ORGS count ' || p_t_org_list.count , 1 ) ;
4024:
4025:
4026:
4027: begin
4032: when others then
4033: v_source_type_code := 'INTERNAL' ;
4034: end ;
4035:
4036: oe_debug_pub.add( '$$$ source type code ' || v_source_type_code , 1 ) ;
4037:
4038:
4039:
4040: if( p_t_org_list.count > 0 ) then
4044:
4045: while i is not null
4046: loop
4047:
4048: oe_debug_pub.add( '$$$ VAL ORGS ' || p_t_org_list(i) , 1 ) ;
4049:
4050: insert into bom_cto_src_orgs_b
4051: (
4052: top_model_line_id,
4120: v_source_type_code varchar2(20) ;
4121:
4122: begin
4123:
4124: oe_debug_pub.add( '$$$ insert all into bcso ' , 1 ) ;
4125:
4126:
4127: begin
4128: select source_type_code into v_source_type_code
4132: when others then
4133: v_source_type_code := 'INTERNAL' ;
4134: end ;
4135:
4136: oe_debug_pub.add( '$$$ source type code ' || v_source_type_code , 1 ) ;
4137:
4138:
4139:
4140:
4194: and organization_id = msi.organization_id );
4195:
4196:
4197:
4198: oe_debug_pub.add( '$$$ insert all into bcso ' || SQL%rowcount , 1 ) ;
4199:
4200: end insert_all_into_bcso ;
4201:
4202:
4208: , p_config_item_id in NUMBER default null )
4209: is
4210: begin
4211:
4212: oe_debug_pub.add( '$$$ insert type3 bcso ' , 1 ) ;
4213:
4214:
4215: insert into bom_cto_src_orgs_b
4216: (
4271:
4272:
4273:
4274:
4275: oe_debug_pub.add( '$$$ insert type3 bcso ' || SQL%rowcount , 1 ) ;
4276:
4277: end insert_type3_bcso ;
4278:
4279:
4285: is
4286: v_group_reference_id number(10);
4287: begin
4288:
4289: oe_debug_pub.add( '$$$ insert type3 bcmo bcso ' , 1 ) ;
4290:
4291: select bom_cto_model_orgs_s1.nextval into v_group_reference_id from dual ;
4292:
4293:
4351: */
4352:
4353:
4354:
4355: oe_debug_pub.add( '$$$ insert type3 bcmo bcmo ' || SQL%rowcount , 1 ) ;
4356:
4357: insert into bom_cto_src_orgs_b
4358: (
4359: top_model_line_id,
4405: where line_id = p_model_line_id );
4406:
4407:
4408:
4409: oe_debug_pub.add( '$$$ insert type3 bcmo bcso ' || SQL%rowcount , 1 ) ;
4410:
4411:
4412:
4413:
4425: is
4426: v_group_reference_id number(10);
4427: begin
4428:
4429: oe_debug_pub.add( '$$$ insert type3 referenced bcso ' , 1 ) ;
4430:
4431: begin
4432: select group_reference_id into v_group_reference_id from bom_cto_model_orgs
4433: where config_item_id = p_config_item_id and rownum = 1 ; /* all records have the same group reference id */
4435:
4436: exception
4437: when no_data_found then
4438:
4439: oe_debug_pub.add( '$$$ short circuit for type3 referenced bcso ' , 1 ) ;
4440: select bom_cto_model_orgs_s1.nextval into v_group_reference_id from dual ;
4441:
4442:
4443:
4554: where line_id = p_model_line_id );
4555:
4556:
4557:
4558: oe_debug_pub.add( '$$$ insert type3 referenced bcso ' || SQL%rowcount , 1 ) ;
4559:
4560:
4561:
4562:
4705: if xOrgLst.count > 0 then
4706: for x1 in xOrgLst.FIRST..xOrgLst.LAST loop
4707:
4708: IF PG_DEBUG <> 0 THEN
4709: oe_debug_pub.add ('get_other_orgs:'||'OE and PO Validation Orgs ('||x1||') = '||xOrglst(x1),5);
4710: END IF;
4711:
4712: end loop;
4713: end if;
4766:
4767: while ( z is not null ) loop
4768:
4769: IF PG_DEBUG <> 0 THEN
4770: oe_debug_pub.add ('get_other_orgs:'||'Vendor ID '||l_model_vendors(z).vendor_id,5);
4771: oe_debug_pub.add ('get_other_orgs:'||'Vendor Site ID '||l_model_vendors(z).vendor_site_id,5);
4772: oe_debug_pub.add ('get_other_orgs:'||'ASL ID '||l_model_vendors(z).asl_id,5);
4773:
4774: END IF;
4767: while ( z is not null ) loop
4768:
4769: IF PG_DEBUG <> 0 THEN
4770: oe_debug_pub.add ('get_other_orgs:'||'Vendor ID '||l_model_vendors(z).vendor_id,5);
4771: oe_debug_pub.add ('get_other_orgs:'||'Vendor Site ID '||l_model_vendors(z).vendor_site_id,5);
4772: oe_debug_pub.add ('get_other_orgs:'||'ASL ID '||l_model_vendors(z).asl_id,5);
4773:
4774: END IF;
4775:
4768:
4769: IF PG_DEBUG <> 0 THEN
4770: oe_debug_pub.add ('get_other_orgs:'||'Vendor ID '||l_model_vendors(z).vendor_id,5);
4771: oe_debug_pub.add ('get_other_orgs:'||'Vendor Site ID '||l_model_vendors(z).vendor_site_id,5);
4772: oe_debug_pub.add ('get_other_orgs:'||'ASL ID '||l_model_vendors(z).asl_id,5);
4773:
4774: END IF;
4775:
4776: PO_AUTOSOURCE_SV.blanket_document_sourcing(
4798: x_msg_data => x_msg_data);
4799:
4800: IF PG_DEBUG <> 0 THEN
4801:
4802: oe_debug_pub.add ('get_other_orgs:'||'Doc Header ID '||l_doc_header_id,5);
4803:
4804: END IF;
4805:
4806: if l_doc_return = 'Y' then
4805:
4806: if l_doc_return = 'Y' then
4807:
4808: IF PG_DEBUG <> 0 THEN
4809: oe_debug_pub.add('get_other_orgs:'|| 'Valid Blanket found for config ..',5);
4810: END IF;
4811:
4812: else
4813: IF PG_DEBUG <> 0 THEN
4810: END IF;
4811:
4812: else
4813: IF PG_DEBUG <> 0 THEN
4814: oe_debug_pub.add('get_other_orgs:' || 'Valid Blanket not found for this config',5);
4815: END IF;
4816:
4817: exit;
4818:
4832:
4833:
4834: -- bugfix 3348635
4835: IF PG_DEBUG <> 0 THEN
4836: oe_debug_pub.add('get_other_orgs:' || 'Ga Flag: '||l_ga_flag||' :: Owning Org: '||l_own_org ,5);
4837: END IF;
4838:
4839:
4840:
4870:
4871:
4872: -- bugfix 3348635
4873: IF PG_DEBUG <> 0 THEN
4874: oe_debug_pub.add('get_other_orgs:' || 'Owning Op Unit: '||l_chk_own_oper_unit,5);
4875: END IF;
4876:
4877:
4878:
4893:
4894:
4895: -- bugfix 3348635
4896: IF PG_DEBUG <> 0 THEN
4897: oe_debug_pub.add('get_other_orgs:' || 'Owning POV Org: '||l_own_pov_org,5);
4898: END IF;
4899:
4900:
4901:
4926: if xOrgLst.count > 0 then
4927: for x2 in xOrgLst.FIRST..xOrgLst.LAST loop
4928:
4929: IF PG_DEBUG <> 0 THEN
4930: oe_debug_pub.add ('get_other_orgs:'||'Validation Orgs ('||x2||') = '||xOrglst(x2),5);
4931: END IF;
4932:
4933: end loop;
4934:
4952: if xOrgLst.exists(x4) then
4953: if xOrgLst(x4) = rget_bcso_orgs.bcso_org_id then
4954:
4955: IF PG_DEBUG <> 0 THEN
4956: oe_debug_pub.add ('get_other_orgs:'||'Deleting Org ('||x4||') = '||xOrglst(x4),5);
4957: END IF;
4958:
4959: xOrgLst.delete(x4);
4960:
4977: if( xOrgLst.count > 0 ) then
4978: for x6 in xOrgLst.First..xOrgLst.Last loop
4979:
4980: if xOrgLst.exists(x6) then
4981: oe_debug_pub.add ('get_other_orgs:'||'Copied Org ('||x6||') = '||xOrglst(x6),5);
4982: xOrgLst_copy(xOrgLst_copy.count + 1) := xOrgLst(x6) ;
4983:
4984: end if;
4985:
5005: if xOrgLst.exists(x5) then
5006:
5007:
5008: IF PG_DEBUG <> 0 THEN
5009: oe_debug_pub.add ('get_other_orgs:'||'Final Org List ('||x5||') = '||xOrglst(x5),5);
5010: END IF;
5011:
5012:
5013:
5020:
5021: exception /* added exception handling for expected and unexpected error as part of bug 4227127 (fp for bug 4162642) */
5022: when FND_API.G_EXC_UNEXPECTED_ERROR then
5023: IF PG_DEBUG <> 0 THEN
5024: oe_debug_pub.add('get_other_orgs: ' || 'GET_OTHER_ORGS::unexp error::'|| to_char(lstmt_num) ||sqlerrm,1);
5025: END IF;
5026: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5027: CTO_MSG_PUB.Count_And_Get
5028: (p_msg_count => x_msg_count
5030: );
5031:
5032: when FND_API.G_EXC_ERROR then
5033: IF PG_DEBUG <> 0 THEN
5034: oe_debug_pub.add('get_other_orgs: ' || 'GET_OTHER_ORGS::exp error::'|| to_char(lstmt_num) ||sqlerrm,1);
5035: END IF;
5036: x_return_status := FND_API.G_RET_STS_ERROR;
5037: CTO_MSG_PUB.Count_And_Get
5038: (p_msg_count => x_msg_count
5040:
5041:
5042: WHEN OTHERS THEN
5043: IF PG_DEBUG <> 0 THEN
5044: oe_debug_pub.add('get_other_orgs: ' || 'GET_OTHER_ORGS::unexp error:: '||to_char(lstmt_num)||'::'||sqlerrm,5);
5045: END IF;
5046:
5047: /* commented raise as the calling api will handle the error using x_return_status RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
5048:
5077: and model_item_id = p_model_item_id
5078: and nvl(organization_type , 2 ) in( '3' , '4' ) );
5079:
5080:
5081: oe_debug_pub.add( ' atleast one procured/dropship model/child model exist ' , 1);
5082:
5083:
5084: exception
5085: when others then
5082:
5083:
5084: exception
5085: when others then
5086: oe_debug_pub.add( ' No procured/dropship model/child model exist ' , 1);
5087: null ;
5088: end ;
5089:
5090:
5100: and model_item_id = p_model_item_id
5101: and create_bom = 'Y' );
5102:
5103:
5104: oe_debug_pub.add( ' atleast one org has bom flag = Y ' , 1);
5105:
5106:
5107: exception
5108: when others then
5126: from bom_cto_src_orgs
5127: where line_id = p_line_id and organization_id = rcv_org_id
5128: and organization_type in ( '3' , '4' ) and rownum = 1 ;
5129:
5130: oe_debug_pub.add( ' Org to be Updated ' || to_char(v_receiving_org) , 1);
5131:
5132: exception
5133: when others then
5134: null ;
5131:
5132: exception
5133: when others then
5134: null ;
5135: oe_debug_pub.add( ' Got Error ' || SQLERRM , 1);
5136: v_receiving_org := p_ship_org_id ;
5137: oe_debug_pub.add( ' Assigning Ship Org as Org to be Updated ' || to_char(v_receiving_org) , 1);
5138: end ;
5139:
5133: when others then
5134: null ;
5135: oe_debug_pub.add( ' Got Error ' || SQLERRM , 1);
5136: v_receiving_org := p_ship_org_id ;
5137: oe_debug_pub.add( ' Assigning Ship Org as Org to be Updated ' || to_char(v_receiving_org) , 1);
5138: end ;
5139:
5140:
5141:
5138: end ;
5139:
5140:
5141:
5142: oe_debug_pub.add( ' need to create bom in atleast one receiving org as bom flag = Y
5143: does not exist for any org ' , 1);
5144:
5145: lStmtNumber := 140;
5146: update bom_cto_src_orgs_b
5156: and bbom.assembly_item_id = p_model_item_id
5157: and bp.create_config_bom = 'Y' ) ;
5158:
5159:
5160: oe_debug_pub.add( ' updated for org ' || v_receiving_org || ' rcv org ' || v_receiving_org ) ;
5161: oe_debug_pub.add( ' Records updated ' || SQL%ROWCOUNT ) ;
5162:
5163:
5164: v_ship_org_bom_update := SQL%ROWCOUNT ;
5157: and bp.create_config_bom = 'Y' ) ;
5158:
5159:
5160: oe_debug_pub.add( ' updated for org ' || v_receiving_org || ' rcv org ' || v_receiving_org ) ;
5161: oe_debug_pub.add( ' Records updated ' || SQL%ROWCOUNT ) ;
5162:
5163:
5164: v_ship_org_bom_update := SQL%ROWCOUNT ;
5165:
5172:
5173:
5174:
5175:
5176: oe_debug_pub.add( ' need to create bom in any org as shipping org does not have model bom or bom param' , 1);
5177:
5178: lStmtNumber := 140;
5179: update bom_cto_src_orgs_b
5180: set create_bom = 'Y' /* , organization_type = l_source_type */
5188: and bp.create_config_bom = 'Y' )
5189: and rownum = 1 ;
5190:
5191:
5192: oe_debug_pub.add( ' updated in any shipping org Records updated ' || SQL%ROWCOUNT ) ;
5193:
5194:
5195: end if ; /* shipping_org no bom */
5196:
5226: i Number;
5227: Begin
5228:
5229: If PG_DEBUG <> 0 Then
5230: oe_debug_pub.add('Get_Master_orgs: Entering Get_Master_orgs API for Model Line id = '||p_model_line_id,3);
5231: End if;
5232:
5233: Begin
5234: Select distinct mp1.master_organization_id
5246: If PG_DEBUG <> 0 Then
5247: i := x_orgs_list.first;
5248: while (i is not null)
5249: Loop
5250: oe_debug_pub.add('Get_Master_Orgs: Master Org = '||x_orgs_list(i),5);
5251: i := x_orgs_list.next(i);
5252: End Loop;
5253: If i is null Then
5254: oe_debug_pub.add('Get_Master_Orgs: No master orgs insterted..',5);
5250: oe_debug_pub.add('Get_Master_Orgs: Master Org = '||x_orgs_list(i),5);
5251: i := x_orgs_list.next(i);
5252: End Loop;
5253: If i is null Then
5254: oe_debug_pub.add('Get_Master_Orgs: No master orgs insterted..',5);
5255: End if;
5256: End if;
5257: Exception when no_data_found then
5258: If PG_DEBUG <> 0 Then
5255: End if;
5256: End if;
5257: Exception when no_data_found then
5258: If PG_DEBUG <> 0 Then
5259: oe_debug_pub.add('Get_Master_Orgs: No new Masters orgs are added.... ',1);
5260: End if;
5261: End;
5262:
5263: x_return_status := FND_API.G_RET_STS_SUCCESS;
5264:
5265: Exception /* added exception handling for expected and unexpected error as part of bug 4227127 (fp for bug 4162642) */
5266: when FND_API.G_EXC_UNEXPECTED_ERROR then
5267: IF PG_DEBUG <> 0 THEN
5268: oe_debug_pub.add('Get_Master_orgs: ' || 'GET_OTHER_ORGS::unexp error::'|| to_char(lstmt_num) ||sqlerrm,1);
5269: END IF;
5270: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5271: CTO_MSG_PUB.Count_And_Get
5272: (p_msg_count => x_msg_count
5274: );
5275:
5276: when FND_API.G_EXC_ERROR then
5277: IF PG_DEBUG <> 0 THEN
5278: oe_debug_pub.add('Get_Master_orgs: ' || 'GET_OTHER_ORGS::exp error::'|| to_char(lstmt_num) ||sqlerrm,1);
5279: END IF;
5280: x_return_status := FND_API.G_RET_STS_ERROR;
5281: CTO_MSG_PUB.Count_And_Get
5282: (p_msg_count => x_msg_count
5284:
5285:
5286: WHEN OTHERS THEN
5287: IF PG_DEBUG <> 0 THEN
5288: oe_debug_pub.add('get_other_orgs: ' || 'Get_Master_Orgs::unexp error:: '||to_char(lstmt_num)||'::'||sqlerrm,5);
5289: END IF;
5290:
5291: /* commented raise as the calling api will handle the error using x_return_status RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
5292:
5307:
5308: bom_batch_id := Bom_Import_Pub.Get_BatchId;
5309:
5310: if bom_batch_id = 0 then
5311: oe_debug_pub.add('Get_bom_batch_id: batch_id is 0', 1);
5312: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5313: end if;
5314:
5315: IF PG_DEBUG <> 0 THEN
5312: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5313: end if;
5314:
5315: IF PG_DEBUG <> 0 THEN
5316: oe_debug_pub.add('Get_bom_batch_id: Batch_id = ' || bom_batch_id, 1);
5317: END IF;
5318:
5319: Exception
5320:
5319: Exception
5320:
5321: When FND_API.G_EXC_UNEXPECTED_ERROR then
5322: IF PG_DEBUG <> 0 THEN
5323: oe_debug_pub.add('Get_bom_batch_id: unexpected error: ' || sqlerrm, 1);
5324: END IF;
5325: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5326:
5327: When others then
5325: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5326:
5327: When others then
5328: IF PG_DEBUG <> 0 THEN
5329: oe_debug_pub.add('Get_bom_batch_id: unexpected error: ' || sqlerrm, 1);
5330: END IF;
5331: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5332:
5333: End set_bom_batch_id;
5362: x_current_mode := nvl(MO_GLOBAL.get_access_mode,'N');
5363: x_current_org := nvl(MO_GLOBAL.get_current_org_id,-99);
5364:
5365: If PG_DEBUG <> 0 Then
5366: oe_debug_pub.add('Switch_to_oe_Context : Order Line Org Id = '||to_char(p_oe_org_id),5);
5367: oe_debug_pub.add('Switch_to_oe_Context : Current Mode = '||x_current_mode,5);
5368: oe_debug_pub.add('Switch_to_oe_Context : Current Org = '||to_char(x_current_org),5);
5369: cto_wip_workflow_api_pk.cto_debug('Switch_to_oe_Context','Change_status_batch: Order Line Org Id ='||to_char(p_oe_org_id));
5370: cto_wip_workflow_api_pk.cto_debug('Switch_to_oe_Context','Change_Status_batch: Current Mode = '||x_current_mode);
5363: x_current_org := nvl(MO_GLOBAL.get_current_org_id,-99);
5364:
5365: If PG_DEBUG <> 0 Then
5366: oe_debug_pub.add('Switch_to_oe_Context : Order Line Org Id = '||to_char(p_oe_org_id),5);
5367: oe_debug_pub.add('Switch_to_oe_Context : Current Mode = '||x_current_mode,5);
5368: oe_debug_pub.add('Switch_to_oe_Context : Current Org = '||to_char(x_current_org),5);
5369: cto_wip_workflow_api_pk.cto_debug('Switch_to_oe_Context','Change_status_batch: Order Line Org Id ='||to_char(p_oe_org_id));
5370: cto_wip_workflow_api_pk.cto_debug('Switch_to_oe_Context','Change_Status_batch: Current Mode = '||x_current_mode);
5371: cto_wip_workflow_api_pk.cto_debug('Switch_to_oe_Context','Change_status_batch: Current org = '||to_char(x_current_org));
5364:
5365: If PG_DEBUG <> 0 Then
5366: oe_debug_pub.add('Switch_to_oe_Context : Order Line Org Id = '||to_char(p_oe_org_id),5);
5367: oe_debug_pub.add('Switch_to_oe_Context : Current Mode = '||x_current_mode,5);
5368: oe_debug_pub.add('Switch_to_oe_Context : Current Org = '||to_char(x_current_org),5);
5369: cto_wip_workflow_api_pk.cto_debug('Switch_to_oe_Context','Change_status_batch: Order Line Org Id ='||to_char(p_oe_org_id));
5370: cto_wip_workflow_api_pk.cto_debug('Switch_to_oe_Context','Change_Status_batch: Current Mode = '||x_current_mode);
5371: cto_wip_workflow_api_pk.cto_debug('Switch_to_oe_Context','Change_status_batch: Current org = '||to_char(x_current_org));
5372: end if;
5377: If x_current_mode <> 'N' then
5378: x_context_switch_flag := 'Y';
5379: End if;
5380: If PG_DEBUG <> 0 Then
5381: oe_debug_pub.add('Switch_to_oe_Context : Changing the operating unit context to Order Line context',5);
5382: End if;
5383: MO_GLOBAL.set_policy_context(p_access_mode => 'S',
5384: p_org_id => p_oe_org_id);
5385:
5384: p_org_id => p_oe_org_id);
5385:
5386: Else--5446723
5387: If PG_DEBUG <> 0 Then
5388: oe_debug_pub.add('Switch_to_oe_Context : UN-EXPECTED MOAC MODE',5);
5389: End if;
5390: End if;
5391:
5392: If PG_DEBUG <> 0 Then
5389: End if;
5390: End if;
5391:
5392: If PG_DEBUG <> 0 Then
5393: oe_debug_pub.add('SWITCH_TO_OE_CONTEXT : Done with Chaning the context to OE ',5);
5394: cto_wip_workflow_api_pk.cto_Debug('Switch_to_oe_Context:', 'Done with Chaning the context to OE');
5395: End if;
5396: End Switch_to_oe_context;
5397:
5415:
5416: end if; /* l_old_mode = 'S' */
5417:
5418: If PG_DEBUG <> 0 Then
5419: oe_debug_pub.add('Switch_context_back : Done with Chaning the context BAck',5);
5420: cto_wip_workflow_api_pk.cto_Debug('Switch_to_oe_Context:', 'Done with Chaning the context Back');
5421: End if;
5422:
5423: End Switch_context_back;