[Home] [Help]
320:
321: BEGIN
322:
323:
324: oe_debug_pub.add('=========================================================================',1);
325: oe_debug_pub.add(' ',1);
326: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
327: oe_debug_pub.add(' ',1);
328: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
321: BEGIN
322:
323:
324: oe_debug_pub.add('=========================================================================',1);
325: oe_debug_pub.add(' ',1);
326: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
327: oe_debug_pub.add(' ',1);
328: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
329: oe_debug_pub.add(' ',1);
322:
323:
324: oe_debug_pub.add('=========================================================================',1);
325: oe_debug_pub.add(' ',1);
326: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
327: oe_debug_pub.add(' ',1);
328: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
329: oe_debug_pub.add(' ',1);
330: oe_debug_pub.add('=========================================================================',1);
323:
324: oe_debug_pub.add('=========================================================================',1);
325: oe_debug_pub.add(' ',1);
326: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
327: oe_debug_pub.add(' ',1);
328: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
329: oe_debug_pub.add(' ',1);
330: oe_debug_pub.add('=========================================================================',1);
331:
324: oe_debug_pub.add('=========================================================================',1);
325: oe_debug_pub.add(' ',1);
326: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
327: oe_debug_pub.add(' ',1);
328: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
329: oe_debug_pub.add(' ',1);
330: oe_debug_pub.add('=========================================================================',1);
331:
332: g_pg_level := 3;
325: oe_debug_pub.add(' ',1);
326: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
327: oe_debug_pub.add(' ',1);
328: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
329: oe_debug_pub.add(' ',1);
330: oe_debug_pub.add('=========================================================================',1);
331:
332: g_pg_level := 3;
333: x_return_status := FND_API.G_RET_STS_SUCCESS;
326: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
327: oe_debug_pub.add(' ',1);
328: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
329: oe_debug_pub.add(' ',1);
330: oe_debug_pub.add('=========================================================================',1);
331:
332: g_pg_level := 3;
333: x_return_status := FND_API.G_RET_STS_SUCCESS;
334: l_Stmt_num := 10;
333: x_return_status := FND_API.G_RET_STS_SUCCESS;
334: l_Stmt_num := 10;
335:
336: If PG_DEBUG <> 0 Then
337: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGIRATIONS: Insise PROCESS_OSS_CONFIGURATION API',5);
338: End if;
339:
340: /* Get the default assignment set into a global variable.
341: The global variable will be used in all other modules later
344: l_stmt_num := 20;
345: G_def_assg_set := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
346:
347: IF PG_DEBUG <> 0 Then
348: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Default Assignment set = '
349: ||to_char(g_def_assg_set),5);
350: End if;
351:
352:
354: */
355:
356: If g_def_assg_set is null Then
357: IF PG_DEBUG <> 0 Then
358: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Default assignment set is null',5);
359: oe_debug_pub.add('=========================================================================',1);
360: oe_debug_pub.add(' ',1);
361: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
362: oe_debug_pub.add(' ',1);
355:
356: If g_def_assg_set is null Then
357: IF PG_DEBUG <> 0 Then
358: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Default assignment set is null',5);
359: oe_debug_pub.add('=========================================================================',1);
360: oe_debug_pub.add(' ',1);
361: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
362: oe_debug_pub.add(' ',1);
363: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
356: If g_def_assg_set is null Then
357: IF PG_DEBUG <> 0 Then
358: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Default assignment set is null',5);
359: oe_debug_pub.add('=========================================================================',1);
360: oe_debug_pub.add(' ',1);
361: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
362: oe_debug_pub.add(' ',1);
363: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
364: oe_debug_pub.add(' ',1);
357: IF PG_DEBUG <> 0 Then
358: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Default assignment set is null',5);
359: oe_debug_pub.add('=========================================================================',1);
360: oe_debug_pub.add(' ',1);
361: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
362: oe_debug_pub.add(' ',1);
363: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
364: oe_debug_pub.add(' ',1);
365: oe_debug_pub.add('=========================================================================',1);
358: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Default assignment set is null',5);
359: oe_debug_pub.add('=========================================================================',1);
360: oe_debug_pub.add(' ',1);
361: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
362: oe_debug_pub.add(' ',1);
363: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
364: oe_debug_pub.add(' ',1);
365: oe_debug_pub.add('=========================================================================',1);
366: End if;
359: oe_debug_pub.add('=========================================================================',1);
360: oe_debug_pub.add(' ',1);
361: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
362: oe_debug_pub.add(' ',1);
363: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
364: oe_debug_pub.add(' ',1);
365: oe_debug_pub.add('=========================================================================',1);
366: End if;
367: return;
360: oe_debug_pub.add(' ',1);
361: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
362: oe_debug_pub.add(' ',1);
363: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
364: oe_debug_pub.add(' ',1);
365: oe_debug_pub.add('=========================================================================',1);
366: End if;
367: return;
368: End if;
361: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
362: oe_debug_pub.add(' ',1);
363: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
364: oe_debug_pub.add(' ',1);
365: oe_debug_pub.add('=========================================================================',1);
366: End if;
367: return;
368: End if;
369:
382: l_oss_defined := 'N';
383: end;
384:
385: If l_oss_defined = 'N' Then
386: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: No Option Specific Soucing setup exists in the system',5);
387: return;
388: End if;
389:
390: /* Check to see if the whole configuration is matched or not.
398:
399:
400: l_stmt_num := 35;
401: If PG_DEBUG <> 0 Then
402: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIOS: CAlling mode = '||p_mode,5);
403: end if;
404:
405: If p_mode = 'UPG' then
406: copy_to_bcol_temp(
485: and nvl(rcv.disable_date,sysdate+1)>sysdate
486: and rcv.SR_RECEIPT_ID = src.sr_receipt_id);
487: End if;
488: Exception When no_data_found then
489: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Ship from org is not valid',1);
490: l_valid_ship_from_org := 'N';
491: CTO_MSG_PUB.cto_message('BOM','CTO_OSS_INVALID_SHIP_ORG');
492: raise FND_API.G_EXC_ERROR;
493: End;
541: and rcv.sr_receipt_id = src.sr_receipt_id
542: )
543: );
544: if PG_DEBUG <> 0 then
545: oe_debug_pub.add(lpad(' ',g_pg_level)||
546: 'PROCESS_OSS_CONFIGURATIONS: Preconfiguration org is a valid manufacturing or Procuring org',3);
547: End if;
548: Exception when no_data_found then
549: if PG_DEBUG <> 0 then
546: 'PROCESS_OSS_CONFIGURATIONS: Preconfiguration org is a valid manufacturing or Procuring org',3);
547: End if;
548: Exception when no_data_found then
549: if PG_DEBUG <> 0 then
550: oe_debug_pub.add(lpad(' ',g_pg_level)||
551: 'PROCESS_OSS_CONFIGURATIONS: Preconfiguration org is not a valid manufacturing or Procuring org',1);
552: CTO_MSG_PUB.cto_message('BOM','CTO_OSS_INVALID_PC_ORG');
553: raise FND_API.G_EXC_ERROR;
554: End if;
576: and ato_line_id = p_ato_line_id;
577: -- End of addition by Renga on 12/02/04 for bug 3896824
578:
579: IF PG_DEBUG <> 0 Then
580: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Config item is matched/re-used with attribute 3',5);
581: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Ending API with Success',5);
582: oe_debug_pub.add('=========================================================================',1);
583: oe_debug_pub.add(' ',1);
584: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
577: -- End of addition by Renga on 12/02/04 for bug 3896824
578:
579: IF PG_DEBUG <> 0 Then
580: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Config item is matched/re-used with attribute 3',5);
581: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Ending API with Success',5);
582: oe_debug_pub.add('=========================================================================',1);
583: oe_debug_pub.add(' ',1);
584: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
585: oe_debug_pub.add(' ',1);
578:
579: IF PG_DEBUG <> 0 Then
580: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Config item is matched/re-used with attribute 3',5);
581: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Ending API with Success',5);
582: oe_debug_pub.add('=========================================================================',1);
583: oe_debug_pub.add(' ',1);
584: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
585: oe_debug_pub.add(' ',1);
586: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
579: IF PG_DEBUG <> 0 Then
580: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Config item is matched/re-used with attribute 3',5);
581: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Ending API with Success',5);
582: oe_debug_pub.add('=========================================================================',1);
583: oe_debug_pub.add(' ',1);
584: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
585: oe_debug_pub.add(' ',1);
586: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
587: oe_debug_pub.add(' ',1);
580: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Config item is matched/re-used with attribute 3',5);
581: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Ending API with Success',5);
582: oe_debug_pub.add('=========================================================================',1);
583: oe_debug_pub.add(' ',1);
584: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
585: oe_debug_pub.add(' ',1);
586: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
587: oe_debug_pub.add(' ',1);
588: oe_debug_pub.add('=========================================================================',1);
581: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Ending API with Success',5);
582: oe_debug_pub.add('=========================================================================',1);
583: oe_debug_pub.add(' ',1);
584: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
585: oe_debug_pub.add(' ',1);
586: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
587: oe_debug_pub.add(' ',1);
588: oe_debug_pub.add('=========================================================================',1);
589: End if;
582: oe_debug_pub.add('=========================================================================',1);
583: oe_debug_pub.add(' ',1);
584: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
585: oe_debug_pub.add(' ',1);
586: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
587: oe_debug_pub.add(' ',1);
588: oe_debug_pub.add('=========================================================================',1);
589: End if;
590:
583: oe_debug_pub.add(' ',1);
584: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
585: oe_debug_pub.add(' ',1);
586: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
587: oe_debug_pub.add(' ',1);
588: oe_debug_pub.add('=========================================================================',1);
589: End if;
590:
591: return;
584: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
585: oe_debug_pub.add(' ',1);
586: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
587: oe_debug_pub.add(' ',1);
588: oe_debug_pub.add('=========================================================================',1);
589: End if;
590:
591: return;
592: end if;
610: x_msg_count => x_msg_count);
611:
612: If x_return_status = FND_API.G_RET_STS_ERROR Then
613: IF PG_DEBUG <> 0 Then
614: oe_debug_pub.add(lpad(' ',g_pg_level)||
615: 'GET_OSS_ORGS_LIST: Exepected error occurred in update_oss_in_bcol API',5);
616: End if;
617: raise FND_API.G_EXC_ERROR;
618: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
616: End if;
617: raise FND_API.G_EXC_ERROR;
618: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
619: IF PG_DEBUG <> 0 Then
620: oe_debug_pub.add(lpad(' ',g_pg_level)||
621: 'GET_OSS_ORGS_LIST: Un Exepected error occurred in update_oss_in_bcol API',5);
622: End if;
623: raise FND_API.G_EXC_UNEXPECTED_ERROR;
624: End if;
633: x_msg_count => x_msg_count,
634: x_msg_data => x_msg_data);
635: If x_return_status = FND_API.G_RET_STS_ERROR Then
636: IF PG_DEBUG <> 0 Then
637: oe_debug_pub.add(lpad(' ',g_pg_level)||
638: 'GET_OSS_ORGS_LIST: Exepected error occurred in update_oss_in_bcol API',5);
639: End if;
640: raise FND_API.G_EXC_ERROR;
641: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
639: End if;
640: raise FND_API.G_EXC_ERROR;
641: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
642: IF PG_DEBUG <> 0 Then
643: oe_debug_pub.add(lpad(' ',g_pg_level)||
644: 'GET_OSS_ORGS_LIST: Un Exepected error occurred in update_oss_in_bcol API',5);
645: End if;
646: raise FND_API.G_EXC_UNEXPECTED_ERROR;
647: End if;
656: x_msg_data => x_msg_data);
657:
658: If x_return_status = FND_API.G_RET_STS_ERROR Then
659: IF PG_DEBUG <> 0 Then
660: oe_debug_pub.add(lpad(' ',g_pg_level)||
661: 'GET_OSS_ORGS_LIST: Exepected error occurred in update_oss_in_bcol API',5);
662: End if;
663: raise FND_API.G_EXC_ERROR;
664: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
662: End if;
663: raise FND_API.G_EXC_ERROR;
664: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
665: IF PG_DEBUG <> 0 Then
666: oe_debug_pub.add(lpad(' ',g_pg_level)||
667: 'GET_OSS_ORGS_LIST: Un Exepected error occurred in update_oss_in_bcol API',5);
668: End if;
669: raise FND_API.G_EXC_UNEXPECTED_ERROR;
670: End if;
673:
674: l_stmt_num := 90;
675:
676: If PG_DEBUG <> 0 Then
677: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Before validating ship from org',5);
678: End if;
679:
680: /* Renga: Add Validation to ship from org check */
681:
714: where line_id = p_ato_line_id
715: and valid_flag = 'Y'));
716: Exception when no_data_found then
717: If PG_DEBUG <> 0 Then
718: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS: Ship from org is not valid',5);
719: end if;
720: CTO_MSG_PUB.cto_message('BOM','CTO_OSS_INVALID_SHIP_ORG');
721: raise FND_API.G_EXC_ERROR;
722: End;
767: and valid_flag in( 'P','Y')))
768: Where org_id = l_ship_from_org_id
769: and rownum = 1;
770: if PG_DEBUG<> 0 Then
771: oe_debug_pub.add(lpad(' ',g_pg_level)||
772: 'PROCESS_OSS_CONFIGURATIONS::The Preconfiguration org is valid manufacturing/procuring org',3);
773: end if;
774:
775: Exception when no_data_found then
773: end if;
774:
775: Exception when no_data_found then
776: if PG_DEBUG<> 0 Then
777: oe_debug_pub.add(lpad(' ',g_pg_level)||
778: 'PROCESS_OSS_CONFIGURATIONS::The Preconfiguration org is not a valid manufacturing/procuring org',3);
779: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS::Raising Expected error',1);
780: end if;
781: CTO_MSG_PUB.cto_message('BOM','CTO_OSS_INVALID_PC_ORG');
775: Exception when no_data_found then
776: if PG_DEBUG<> 0 Then
777: oe_debug_pub.add(lpad(' ',g_pg_level)||
778: 'PROCESS_OSS_CONFIGURATIONS::The Preconfiguration org is not a valid manufacturing/procuring org',3);
779: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS::Raising Expected error',1);
780: end if;
781: CTO_MSG_PUB.cto_message('BOM','CTO_OSS_INVALID_PC_ORG');
782: raise FND_API.G_EXC_ERROR;
783: End;
788: End if; /* x_oss_exists = 'Y' */
789:
790:
791: If PG_DEBUG <> 0 Then
792: oe_debug_pub.add('=========================================================================',1);
793: oe_debug_pub.add(' ',1);
794: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
795: oe_debug_pub.add(' ',1);
796: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
789:
790:
791: If PG_DEBUG <> 0 Then
792: oe_debug_pub.add('=========================================================================',1);
793: oe_debug_pub.add(' ',1);
794: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
795: oe_debug_pub.add(' ',1);
796: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
797: oe_debug_pub.add(' ',1);
790:
791: If PG_DEBUG <> 0 Then
792: oe_debug_pub.add('=========================================================================',1);
793: oe_debug_pub.add(' ',1);
794: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
795: oe_debug_pub.add(' ',1);
796: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
797: oe_debug_pub.add(' ',1);
798: oe_debug_pub.add('=========================================================================',1);
791: If PG_DEBUG <> 0 Then
792: oe_debug_pub.add('=========================================================================',1);
793: oe_debug_pub.add(' ',1);
794: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
795: oe_debug_pub.add(' ',1);
796: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
797: oe_debug_pub.add(' ',1);
798: oe_debug_pub.add('=========================================================================',1);
799: End if;
792: oe_debug_pub.add('=========================================================================',1);
793: oe_debug_pub.add(' ',1);
794: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
795: oe_debug_pub.add(' ',1);
796: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
797: oe_debug_pub.add(' ',1);
798: oe_debug_pub.add('=========================================================================',1);
799: End if;
800:
793: oe_debug_pub.add(' ',1);
794: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
795: oe_debug_pub.add(' ',1);
796: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
797: oe_debug_pub.add(' ',1);
798: oe_debug_pub.add('=========================================================================',1);
799: End if;
800:
801: Exception
794: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
795: oe_debug_pub.add(' ',1);
796: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
797: oe_debug_pub.add(' ',1);
798: oe_debug_pub.add('=========================================================================',1);
799: End if;
800:
801: Exception
802:
801: Exception
802:
803: WHEN FND_API.G_EXC_ERROR THEN
804: IF PG_DEBUG <> 0 THEN
805: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS::exp error::'
806: ||to_char(l_stmt_num)
807: ||'::'||sqlerrm,1);
808: END IF;
809: x_return_status := FND_API.G_RET_STS_ERROR;
813: p_msg_data => x_msg_data
814: );
815: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
816: IF PG_DEBUG <> 0 THEN
817: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS::exp error::'
818: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
819: END IF;
820: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
821: g_pg_level := g_pg_level - 3;
824: p_msg_data => x_msg_data
825: );
826: WHEN OTHERS THEN
827: IF PG_DEBUG <> 0 THEN
828: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_OSS_CONFIGURATIONS::exp error::'
829: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
830: END IF;
831: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
832: g_pg_level := g_pg_level - 3;
887: configuration has OSS definition
888: */
889:
890: If PG_DEBUG <> 0 Then
891: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Inside PRUNE_OSS_CONFIG API',5);
892: End if;
893:
894:
895: l_stmt_num := 10;
906: where ossl.oss_comp_seq_id = ossc.oss_comp_seq_id);
907:
908:
909: If PG_DEBUG <> 0 then
910: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: No of oss components for this model = '||l_comp_count,5);
911: End if;
912:
913: If l_comp_count > 0 then
914:
951:
952: l_org_count := sql%rowcount;
953:
954: If PG_DEBUG <> 0 Then
955: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: After first Insert',5);
956: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Number of of orgs inserted in temp table ='||l_org_count,5);
957: End if;
958:
959:
952: l_org_count := sql%rowcount;
953:
954: If PG_DEBUG <> 0 Then
955: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: After first Insert',5);
956: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Number of of orgs inserted in temp table ='||l_org_count,5);
957: End if;
958:
959:
960: /* We need to find out the intersection vendors from the sourcing setup list.
1000:
1001: l_vendor_count := sql%rowcount;
1002:
1003: If PG_DEBUG <> 0 Then
1004: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: After Second insert..',5);
1005: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Number of Vendors inserted into temp = '||l_vendor_count);
1006: End if;
1007:
1008: /* If there is no commong orgs, then CTO will raise an error and end the process
1001: l_vendor_count := sql%rowcount;
1002:
1003: If PG_DEBUG <> 0 Then
1004: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: After Second insert..',5);
1005: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Number of Vendors inserted into temp = '||l_vendor_count);
1006: End if;
1007:
1008: /* If there is no commong orgs, then CTO will raise an error and end the process
1009: Renga: Think about the case, where no orgs found but some valid vendors found.
1018:
1019: If l_vendor_count = 0 and l_org_count = 0 then
1020:
1021: If PG_DEBUG <> 0 then
1022: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: No Intersection org found ',1);
1023: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Model line id = '||p_model_line_id,1);
1024: end if;
1025: If p_calling_mode = 'ACC' Then
1026: cto_msg_pub.cto_message('BOM','BOM_CTO_OSS_NO_COMMON_ORGS');
1019: If l_vendor_count = 0 and l_org_count = 0 then
1020:
1021: If PG_DEBUG <> 0 then
1022: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: No Intersection org found ',1);
1023: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Model line id = '||p_model_line_id,1);
1024: end if;
1025: If p_calling_mode = 'ACC' Then
1026: cto_msg_pub.cto_message('BOM','BOM_CTO_OSS_NO_COMMON_ORGS');
1027: raise FND_API.G_EXC_ERROR;
1064: */
1065: /*
1066: for org_rec in org_cur
1067: Loop
1068: oe_debug_pub.add('Temp Org id = '||org_rec.organization_id,1);
1069: End Loop;
1070:
1071: for src_rec in src_cur
1072: loop
1069: End Loop;
1070:
1071: for src_rec in src_cur
1072: loop
1073: oe_debug_pub.add('Temp rcv org id = '||src_rec.rcv_org_id,1);
1074: oe_debug_pub.add('Temp Src org id = '||src_rec.source_org_id,1);
1075: oe_debug_pub.add('Temp Valid flag = '||src_rec.valid_flag,1);
1076: end loop;
1077: */
1070:
1071: for src_rec in src_cur
1072: loop
1073: oe_debug_pub.add('Temp rcv org id = '||src_rec.rcv_org_id,1);
1074: oe_debug_pub.add('Temp Src org id = '||src_rec.source_org_id,1);
1075: oe_debug_pub.add('Temp Valid flag = '||src_rec.valid_flag,1);
1076: end loop;
1077: */
1078:
1071: for src_rec in src_cur
1072: loop
1073: oe_debug_pub.add('Temp rcv org id = '||src_rec.rcv_org_id,1);
1074: oe_debug_pub.add('Temp Src org id = '||src_rec.source_org_id,1);
1075: oe_debug_pub.add('Temp Valid flag = '||src_rec.valid_flag,1);
1076: end loop;
1077: */
1078:
1079: l_stmt_num := 60;
1103: );
1104:
1105:
1106: If PG_DEBUG <> 0 Then
1107: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Number of valid nodes in the pruned tree ='
1108: ||sql%rowcount,5);
1109: End if;
1110:
1111:
1175:
1176: /* Impact : Valid flag condition should be removed */
1177:
1178: IF PG_DEBUG <> 0 Then
1179: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Number of 100% rules inserted ='
1180: ||sql%rowcount);
1181: End if;
1182:
1183:
1193: where valid_flag = 'Y'
1194: and line_id = p_model_line_id;
1195:
1196: IF PG_DEBUG <> 0 Then
1197: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Number of valid orgs = '
1198: ||l_valid_count);
1199: End if;
1200:
1201: If l_valid_count = 0 then
1200:
1201: If l_valid_count = 0 then
1202:
1203: If PG_DEBUG <> 0 then
1204: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Purning model tree results wiht no valid orgs',5);
1205: End if;
1206: If p_calling_mode = 'ACC' Then
1207: cto_msg_pub.cto_message('BOM','CTO_OSS_NO_VALID_TREE');
1208: raise FND_API.G_EXC_ERROR;
1241:
1242: /* Renga: Things about match and re-use case for parent configs
1243: */
1244: If PG_DEBUG <> 0 Then
1245: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Coming out of PRUNE_OSS_CONFIG API',5);
1246: End if;
1247:
1248:
1249: End if;
1253: Exception
1254:
1255: WHEN FND_API.G_EXC_ERROR THEN
1256: IF PG_DEBUG <> 0 THEN
1257: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG::exp error::'
1258: ||to_char(l_stmt_num)
1259: ||'::'||sqlerrm,1);
1260: END IF;
1261: x_return_status := FND_API.G_RET_STS_ERROR;
1265: p_msg_data => x_msg_data
1266: );
1267: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1268: IF PG_DEBUG <> 0 THEN
1269: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG::exp error::'
1270: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
1271: END IF;
1272: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1273: g_pg_level := g_pg_level - 3;
1276: p_msg_data => x_msg_data
1277: );
1278: WHEN OTHERS THEN
1279: IF PG_DEBUG <> 0 THEN
1280: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG::exp error::'
1281: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
1282: END IF;
1283: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1284: g_pg_level := g_pg_level - 3;
1331: set valid_flag = 'N'
1332: where line_id = p_model_line_id
1333: and valid_flag is null;
1334: If PG_DEBUG <> 0 then
1335: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Number of records updated in source table = '
1336: ||sql%rowcount,5);
1337: End if;
1338:
1339: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1342: where line_id = p_model_line_id
1343: and valid_flag = 'Y';
1344:
1345: If PG_DEBUG <> 0 then
1346: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Number of records updated in source table = '
1347: ||sql%rowcount,5);
1348: End if;
1349: end if;
1350:
1354: /* Get the no of child that are oss for this parent model
1355: */
1356:
1357: If PG_DEBUG <> 0 Then
1358: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Inside PRUNE_PARENT_OSS_CONFIG API',5);
1359: End if;
1360:
1361:
1362: select /*+ INDEX(bcol BOM_CTO_ORDER_LINES_GT_N3) */
1379: */
1380:
1381:
1382: If PG_DEBUG <> 0 Then
1383: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Number of oss child = '
1384: ||l_oss_child_count,5);
1385: End if;
1386: l_stmt_num := 20;
1387: If l_oss_child_count > 0 then
1441:
1442: group by organization_id
1443: having count(*) = l_oss_child_count;
1444: Else
1445: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: No oss child found...Updating in bcol',1);
1446: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
1447: bom_cto_order_lines_gt bcol
1448: set option_specific = null
1449: where line_id = p_model_line_id;
1446: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
1447: bom_cto_order_lines_gt bcol
1448: set option_specific = null
1449: where line_id = p_model_line_id;
1450: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Rows updated = '||sql%rowcount,1);
1451: return;
1452: End if;
1453:
1454: l_stmt_num := 50;
1452: End if;
1453:
1454: l_stmt_num := 50;
1455: If PG_DEBUG <> 0 Then
1456: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Number of intersection orgs = '
1457: ||sql%rowcount,5);
1458: End if;
1459: If sql%rowcount = 0 then
1460: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: No intersection orgs found',5);
1456: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Number of intersection orgs = '
1457: ||sql%rowcount,5);
1458: End if;
1459: If sql%rowcount = 0 then
1460: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: No intersection orgs found',5);
1461: x_exp_error_code := 350; /* No intersection orgs found */
1462: g_pg_level := g_pg_level - 3;
1463: return;
1464: end if;
1482: */
1483:
1484:
1485: If PG_DEBUG <> 0 Then
1486: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Item rule count = '||to_char(l_item_rule_count),5);
1487: end if;
1488:
1489: l_stmt_num := 70;
1490: If l_item_rule_count > 0 then
1498: x_msg_data => x_msg_data
1499: );
1500: If x_return_status = FND_API.G_RET_STS_ERROR Then
1501: IF PG_DEBUG <> 0 Then
1502: oe_debug_pub.add(lpad(' ',g_pg_level)||
1503: 'GET_OSS_ORGS_LIST: Exepected error occurred in update_oss_in_bcol API',5);
1504: End if;
1505: raise FND_API.G_EXC_ERROR;
1506: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
1504: End if;
1505: raise FND_API.G_EXC_ERROR;
1506: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
1507: IF PG_DEBUG <> 0 Then
1508: oe_debug_pub.add(lpad(' ',g_pg_level)||
1509: 'GET_OSS_ORGS_LIST: Un Exepected error occurred in update_oss_in_bcol API',5);
1510: End if;
1511: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1512: End if;
1510: End if;
1511: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1512: End if;
1513:
1514: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: '
1515: ||' Item Rule exists after pruning',1);
1516: Else
1517: l_rule_exists := 'N';
1518: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: '
1514: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: '
1515: ||' Item Rule exists after pruning',1);
1516: Else
1517: l_rule_exists := 'N';
1518: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: '
1519: ||' No Item Ruleafter pruning',1);
1520:
1521: End if;
1522: /* After pruing the item level sourcing, If still there are some
1570: Returning rcv_org_id,source_org_id Bulk collect into l_source_org_tbl,l_rcv_org_tbl;
1571:
1572:
1573: If PG_DEBUG <> 0 Then
1574: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Number of updated records ='||l_source_org_tbl.count,5);
1575: End if;
1576:
1577: If l_source_org_tbl.count <> 0 then
1578: For i in l_source_org_tbl.first..l_source_org_tbl.last
1584: x_msg_data => x_msg_data,
1585: x_msg_count => x_msg_count);
1586: If x_return_status = FND_API.G_RET_STS_ERROR Then
1587: IF PG_DEBUG <> 0 Then
1588: oe_debug_pub.add(lpad(' ',g_pg_level)||
1589: 'GET_OSS_ORGS_LIST: Exepected error occurred in update_oss_in_bcol API',5);
1590: End if;
1591: raise FND_API.G_EXC_ERROR;
1592: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
1590: End if;
1591: raise FND_API.G_EXC_ERROR;
1592: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
1593: IF PG_DEBUG <> 0 Then
1594: oe_debug_pub.add(lpad(' ',g_pg_level)||
1595: 'GET_OSS_ORGS_LIST: Un Exepected error occurred in update_oss_in_bcol API',5);
1596: End if;
1597: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1598: End if;
1625: -- any rows.
1626: -- Added the nvl clause in the subquery returun column
1627:
1628: If PG_DEBUG <> 0 Then
1629: oe_debug_pub.add(lpad(' ',g_pg_level)||
1630: 'PRUNE_PARENT_OSS: Before inserting 100% make at rules',5);
1631: End if;
1632: Insert into bom_cto_oss_source_gt
1633: (
1699: where line_id = p_model_line_id
1700: and valid_flag = 'Y';
1701:
1702: IF PG_DEBUG <> 0 Then
1703: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Number of valid nodes in the pruned tree ='
1704: ||l_valid_source_count,5);
1705: End if;
1706:
1707: If l_valid_source_count = 0 then
1705: End if;
1706:
1707: If l_valid_source_count = 0 then
1708: IF PG_DEBUG <> 0 Then
1709: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: After pruning there is no valid source node',4);
1710: End if;
1711: If p_calling_mode = 'ACC' Then
1712: CTO_MSG_PUB.cto_message('BOM','CTO_OSS_NO_VALID_TREE');
1713: raise FND_API.G_EXC_ERROR;
1721: Exception
1722:
1723: WHEN FND_API.G_EXC_ERROR THEN
1724: IF PG_DEBUG <> 0 THEN
1725: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG::exp error::'
1726: ||to_char(l_stmt_num)
1727: ||'::'||sqlerrm,1);
1728: END IF;
1729: x_return_status := FND_API.G_RET_STS_ERROR;
1733: p_msg_data => x_msg_data
1734: );
1735: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1736: IF PG_DEBUG <> 0 THEN
1737: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG::exp error::'
1738: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
1739: END IF;
1740: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1741: g_pg_level := g_pg_level - 3;
1744: p_msg_data => x_msg_data
1745: );
1746: WHEN OTHERS THEN
1747: IF PG_DEBUG <> 0 THEN
1748: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG::exp error::'
1749: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
1750: END IF;
1751: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1752: g_pg_level := g_pg_level - 3;
1977: g_pg_level := 3;
1978: x_return_status := FND_API.G_RET_STS_SUCCESS;
1979:
1980: If PG_DEBUG <> 0 Then
1981: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Inside Create OSS Sourcing Rule API',5);
1982: End if;
1983:
1984: l_stmt_num := 15;
1985: If p_mode = 'UPG' then
1992: For oss_model_lines_rec in oss_model_lines
1993: Loop
1994: l_stmt_num := 20;
1995: If PG_DEBUG <> 0 Then
1996: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Line id = '||oss_model_lines_rec.line_id,5);
1997: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Config item = '||oss_model_lines_rec.config_item_id,5);
1998: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Option specific = '||oss_model_lines_rec.option_specific,5);
1999: End if;
2000:
1993: Loop
1994: l_stmt_num := 20;
1995: If PG_DEBUG <> 0 Then
1996: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Line id = '||oss_model_lines_rec.line_id,5);
1997: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Config item = '||oss_model_lines_rec.config_item_id,5);
1998: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Option specific = '||oss_model_lines_rec.option_specific,5);
1999: End if;
2000:
2001: print_source_gt(oss_model_lines_rec.line_id);
1994: l_stmt_num := 20;
1995: If PG_DEBUG <> 0 Then
1996: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Line id = '||oss_model_lines_rec.line_id,5);
1997: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Config item = '||oss_model_lines_rec.config_item_id,5);
1998: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Option specific = '||oss_model_lines_rec.option_specific,5);
1999: End if;
2000:
2001: print_source_gt(oss_model_lines_rec.line_id);
2002: l_receiving_org_tbl.delete;
2010:
2011: If oss_model_lines_rec.config_creation = '3' and
2012: (oss_model_lines_rec.perform_match = 'Y' or oss_model_lines_rec.reuse_config = 'Y') Then
2013: If PG_DEBUG <> 0 Then
2014: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Config item is matched and item attribute is 3',5);
2015: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: No Need to create new sourcing rules',5);
2016: End if;
2017: l_stmt_num := 30;
2018: exit; /* Start processing next record */
2011: If oss_model_lines_rec.config_creation = '3' and
2012: (oss_model_lines_rec.perform_match = 'Y' or oss_model_lines_rec.reuse_config = 'Y') Then
2013: If PG_DEBUG <> 0 Then
2014: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Config item is matched and item attribute is 3',5);
2015: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: No Need to create new sourcing rules',5);
2016: End if;
2017: l_stmt_num := 30;
2018: exit; /* Start processing next record */
2019: End if;
2032: where line_id = oss_model_lines_rec.line_id
2033: and valid_flag = 'Y';
2034:
2035: IF PG_DEBUG <> 0 then
2036: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
2037: ||sql%rowcount,5);
2038: End if;
2039:
2040: Else
2051: from bom_cto_src_orgs
2052: where line_id = oss_model_lines_rec.line_id
2053: and organization_type is not null);
2054: IF PG_DEBUG <> 0 then
2055: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
2056: ||sql%rowcount,5);
2057: End if;
2058:
2059: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2072: where line_id = oss_model_lines_rec.line_id
2073: and valid_flag = 'P'));
2074:
2075: IF PG_DEBUG <> 0 then
2076: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
2077: ||sql%rowcount,5);
2078: End if;
2079: End if;
2080:
2096: )
2097: );
2098:
2099: If PG_DEBUG <> 0 Then
2100: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of records that are not re-used = '||
2101: sql%rowcount,5);
2102: End if;
2103:
2104: l_stmt_num := 70;
2112: l_cur_assg_id := null;
2113: End if;
2114:
2115: If PG_DEBUG <> 0 Then
2116: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Line id = '
2117: ||source_tree_rec.line_id,5);
2118: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Rcv Org Id = '
2119: ||source_tree_rec.rcv_org_id,5);
2120: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Source org id = '
2114:
2115: If PG_DEBUG <> 0 Then
2116: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Line id = '
2117: ||source_tree_rec.line_id,5);
2118: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Rcv Org Id = '
2119: ||source_tree_rec.rcv_org_id,5);
2120: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Source org id = '
2121: ||Source_tree_rec.source_org_id,5);
2122: oe_debug_pub.add(lpad(' ',g_pg_level)||'Old assignment set id = '||l_cur_assg_id,5);
2116: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Line id = '
2117: ||source_tree_rec.line_id,5);
2118: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Rcv Org Id = '
2119: ||source_tree_rec.rcv_org_id,5);
2120: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Source org id = '
2121: ||Source_tree_rec.source_org_id,5);
2122: oe_debug_pub.add(lpad(' ',g_pg_level)||'Old assignment set id = '||l_cur_assg_id,5);
2123: oe_debug_pub.add(lpad(' ',g_pg_level)||'New assignment set id = '||source_tree_rec.assignment_id,5);
2124: End if;
2118: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Rcv Org Id = '
2119: ||source_tree_rec.rcv_org_id,5);
2120: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Source org id = '
2121: ||Source_tree_rec.source_org_id,5);
2122: oe_debug_pub.add(lpad(' ',g_pg_level)||'Old assignment set id = '||l_cur_assg_id,5);
2123: oe_debug_pub.add(lpad(' ',g_pg_level)||'New assignment set id = '||source_tree_rec.assignment_id,5);
2124: End if;
2125: l_stmt_num := 90;
2126: if nvl(l_cur_assg_id,-1) <> source_tree_rec.assignment_id then
2119: ||source_tree_rec.rcv_org_id,5);
2120: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Source org id = '
2121: ||Source_tree_rec.source_org_id,5);
2122: oe_debug_pub.add(lpad(' ',g_pg_level)||'Old assignment set id = '||l_cur_assg_id,5);
2123: oe_debug_pub.add(lpad(' ',g_pg_level)||'New assignment set id = '||source_tree_rec.assignment_id,5);
2124: End if;
2125: l_stmt_num := 90;
2126: if nvl(l_cur_assg_id,-1) <> source_tree_rec.assignment_id then
2127: /* Now this is the time to process the sourcing rule creation.
2152: FOR l_index IN 1..x_msg_count LOOP
2153: l_msg_data := fnd_msg_pub.get(
2154: p_msg_index => l_index,
2155: p_encoded => FND_API.G_FALSE);
2156: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: error : '||substr(l_msg_data,1,250));
2157: END LOOP;
2158:
2159: If x_return_status = FND_API.G_RET_STS_ERROR Then
2160: IF PG_DEBUG <> 0 Then
2157: END LOOP;
2158:
2159: If x_return_status = FND_API.G_RET_STS_ERROR Then
2160: IF PG_DEBUG <> 0 Then
2161: oe_debug_pub.add(lpad(' ',g_pg_level)||
2162: 'CREATE_OSS_SOURCING_RULES: Exepected error occurred in update_oss_in_bcol API',5);
2163: End if;
2164: raise FND_API.G_EXC_ERROR;
2165: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
2163: End if;
2164: raise FND_API.G_EXC_ERROR;
2165: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
2166: IF PG_DEBUG <> 0 Then
2167: oe_debug_pub.add(lpad(' ',g_pg_level)||
2168: 'CREATE_OSS_SOURCING_RULES: Un Exepected error occurred in update_oss_in_bcol API',5);
2169: End if;
2170: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2171: End if;
2175: l_shipping_org_val_tbl.delete;
2176: l_stmt_num := 110;
2177: lAssignmentTbl(asg_count).sourcing_rule_id := x_sourcing_rule_rec.sourcing_rule_id;
2178: If PG_DEBUG <> 0 Then
2179: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: New Sourcing rule cretaed= ' ||x_sourcing_rule_rec.sourcing_rule_id,5);
2180: End if;
2181: Asg_count := asg_count + 1;
2182: l_old_rank := null;
2183: End if; /* l_cur_assg_id is not null */
2186:
2187: l_stmt_num := 120;
2188:
2189: If PG_DEBUG <> 0 Then
2190: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Loading the assignment into assignment record ',5);
2191: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES: assignment_type = '
2192: ||source_tree_rec.assignment_type,5);
2193: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: inventory item id = '
2194: ||oss_model_lines_rec.config_item_id,5);
2187: l_stmt_num := 120;
2188:
2189: If PG_DEBUG <> 0 Then
2190: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Loading the assignment into assignment record ',5);
2191: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES: assignment_type = '
2192: ||source_tree_rec.assignment_type,5);
2193: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: inventory item id = '
2194: ||oss_model_lines_rec.config_item_id,5);
2195: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: organization id = '
2189: If PG_DEBUG <> 0 Then
2190: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Loading the assignment into assignment record ',5);
2191: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES: assignment_type = '
2192: ||source_tree_rec.assignment_type,5);
2193: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: inventory item id = '
2194: ||oss_model_lines_rec.config_item_id,5);
2195: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: organization id = '
2196: ||source_tree_rec.organization_id,5);
2197:
2191: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES: assignment_type = '
2192: ||source_tree_rec.assignment_type,5);
2193: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: inventory item id = '
2194: ||oss_model_lines_rec.config_item_id,5);
2195: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: organization id = '
2196: ||source_tree_rec.organization_id,5);
2197:
2198: End if;
2199:
2300: where sourcing_rule_id = source_tree_rec.sourcing_rule_id;
2301:
2302:
2303: If PG_DEBUG <> 0 Then
2304: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Sourcing Rule name = '||
2305: l_sourcing_rule_rec.sourcing_rule_name,5);
2306: End if;
2307: l_sourcing_rule_rec.operation := 'CREATE';
2308:
2367:
2368: End if; /* nvl(l_curr_rcv_org,-1) <> source_tree_rec.sr_receipt_id */
2369:
2370: If PG_DEBUG <> 0 Then
2371: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Old Rank = '||l_old_rank);
2372: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: New Rank = '||source_tree_rec.rank);
2373: End if;
2374:
2375: l_stmt_num := 150;
2368: End if; /* nvl(l_curr_rcv_org,-1) <> source_tree_rec.sr_receipt_id */
2369:
2370: If PG_DEBUG <> 0 Then
2371: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Old Rank = '||l_old_rank);
2372: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: New Rank = '||source_tree_rec.rank);
2373: End if;
2374:
2375: l_stmt_num := 150;
2376:
2387: and rank = source_tree_rec.rank
2388: and valid_flag = 'P';
2389:
2390: If PG_DEBUG <> 0 Then
2391: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Rule id = '||source_tree_rec.sourcing_rule_id);
2392: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Rank = '||source_tree_rec.rank);
2393: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Rank Sum = '||l_rank_sum);
2394: End if;
2395:
2388: and valid_flag = 'P';
2389:
2390: If PG_DEBUG <> 0 Then
2391: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Rule id = '||source_tree_rec.sourcing_rule_id);
2392: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Rank = '||source_tree_rec.rank);
2393: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Rank Sum = '||l_rank_sum);
2394: End if;
2395:
2396: l_new_rank_seq := l_new_rank_seq + 1;
2389:
2390: If PG_DEBUG <> 0 Then
2391: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Rule id = '||source_tree_rec.sourcing_rule_id);
2392: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Rank = '||source_tree_rec.rank);
2393: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Rank Sum = '||l_rank_sum);
2394: End if;
2395:
2396: l_new_rank_seq := l_new_rank_seq + 1;
2397: l_old_rank := source_tree_rec.rank;
2401: l_stmt_num := 170;
2402:
2403: l_shipping_org_tbl(i).allocation_percent := source_tree_rec.allocation/l_rank_sum*100;
2404: If PG_DEBUG <> 0 Then
2405: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: New Allocation % = '
2406: ||l_shipping_org_tbl(i).allocation_percent);
2407: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Vendor id = '
2408: ||source_tree_rec.vendor_id,5);
2409: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: source org id = '
2403: l_shipping_org_tbl(i).allocation_percent := source_tree_rec.allocation/l_rank_sum*100;
2404: If PG_DEBUG <> 0 Then
2405: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: New Allocation % = '
2406: ||l_shipping_org_tbl(i).allocation_percent);
2407: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Vendor id = '
2408: ||source_tree_rec.vendor_id,5);
2409: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: source org id = '
2410: ||source_tree_rec.source_org_id);
2411: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING)RULE: Sr Source id ='
2405: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: New Allocation % = '
2406: ||l_shipping_org_tbl(i).allocation_percent);
2407: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Vendor id = '
2408: ||source_tree_rec.vendor_id,5);
2409: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: source org id = '
2410: ||source_tree_rec.source_org_id);
2411: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING)RULE: Sr Source id ='
2412: ||source_tree_rec.sr_source_id);
2413: End if;
2407: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Vendor id = '
2408: ||source_tree_rec.vendor_id,5);
2409: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: source org id = '
2410: ||source_tree_rec.source_org_id);
2411: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING)RULE: Sr Source id ='
2412: ||source_tree_rec.sr_source_id);
2413: End if;
2414:
2415: l_shipping_org_tbl(i).rank := l_new_rank_seq;
2468: l_shipping_org_tbl(i).vendor_site_id
2469: from mrp_sr_source_org
2470: where sr_source_id = source_tree_rec.sr_source_id;
2471:
2472: oe_debug_pub.add('Vendor site id inserted = '||l_shipping_org_tbl(i).vendor_site_id,5);
2473: i := i + 1;
2474:
2475: End Loop;
2476:
2506: FOR l_index IN 1..x_msg_count LOOP
2507: l_msg_data := fnd_msg_pub.get(
2508: p_msg_index => l_index,
2509: p_encoded => FND_API.G_FALSE);
2510: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: error : '||substr(l_msg_data,1,250));
2511: END LOOP;
2512:
2513: If x_return_status = FND_API.G_RET_STS_ERROR Then
2514: IF PG_DEBUG <> 0 Then
2511: END LOOP;
2512:
2513: If x_return_status = FND_API.G_RET_STS_ERROR Then
2514: IF PG_DEBUG <> 0 Then
2515: oe_debug_pub.add(lpad(' ',g_pg_level)||
2516: 'CREATE_OSS_SOURCING_RULES: Exepected error occurred in update_oss_in_bcol API',5);
2517: End if;
2518: raise FND_API.G_EXC_ERROR;
2519: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
2517: End if;
2518: raise FND_API.G_EXC_ERROR;
2519: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
2520: IF PG_DEBUG <> 0 Then
2521: oe_debug_pub.add(lpad(' ',g_pg_level)||
2522: 'CREATE_OSS_SOURCING_RULES: Un Exepected error occurred in update_oss_in_bcol API',5);
2523: End if;
2524: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2525: End if;
2524: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2525: End if;
2526:
2527: If PG_DEBUG <> 0 Then
2528: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: New sourcing Rule created = '
2529: ||x_sourcing_rule_rec.sourcing_rule_id,5);
2530: End if;
2531:
2532: lAssignmentTbl(asg_count).sourcing_rule_id := x_sourcing_rule_rec.sourcing_rule_id;
2552:
2553: l_stmt_num := 200;
2554:
2555: If PG_DEBUG <> 0 Then
2556: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: '||'Before make orgs loop ',5);
2557: End if;
2558:
2559: For oss_make_orgs in
2560: oss_make_orgs_cur(oss_model_lines_rec.line_id,oss_model_lines_rec.config_item_id)
2560: oss_make_orgs_cur(oss_model_lines_rec.line_id,oss_model_lines_rec.config_item_id)
2561: Loop
2562:
2563:
2564: oe_debug_pub.add('Inside make at rule loop ',5);
2565: l_stmt_num := 210;
2566:
2567: l_sourcing_rule_rec := MRP_SOURCING_RULE_PUB.G_MISS_SOURCING_RULE_REC;
2568: l_sourcing_rule_rec.organization_id := oss_make_orgs.rcv_org_id;
2629: x_msg_data => x_msg_data);
2630:
2631: If x_return_status = FND_API.G_RET_STS_ERROR Then
2632: IF PG_DEBUG <> 0 Then
2633: oe_debug_pub.add(lpad(' ',g_pg_level)||
2634: 'CREATE_OSS_SOURCING_RULES: Exepected error occurred in update_oss_in_bcol API',5);
2635: End if;
2636: raise FND_API.G_EXC_ERROR;
2637: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
2635: End if;
2636: raise FND_API.G_EXC_ERROR;
2637: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
2638: IF PG_DEBUG <> 0 Then
2639: oe_debug_pub.add(lpad(' ',g_pg_level)||
2640: 'CREATE_OSS_SOURCING_RULES: Un Exepected error occurred in update_oss_in_bcol API',5);
2641: End if;
2642: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2643: End if;
2643: End if;
2644:
2645:
2646: If PG_DEBUG <> 0 Then
2647: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Loading the assignment into assignment record ',5);
2648: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES: assignment_type = '
2649: ||'6',5);
2650: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: inventory item id = '
2651: ||oss_model_lines_rec.config_item_id,5);
2644:
2645:
2646: If PG_DEBUG <> 0 Then
2647: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Loading the assignment into assignment record ',5);
2648: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES: assignment_type = '
2649: ||'6',5);
2650: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: inventory item id = '
2651: ||oss_model_lines_rec.config_item_id,5);
2652: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: organization id = '
2646: If PG_DEBUG <> 0 Then
2647: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Loading the assignment into assignment record ',5);
2648: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES: assignment_type = '
2649: ||'6',5);
2650: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: inventory item id = '
2651: ||oss_model_lines_rec.config_item_id,5);
2652: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: organization id = '
2653: ||oss_make_orgs.rcv_org_id,5);
2654:
2648: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES: assignment_type = '
2649: ||'6',5);
2650: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: inventory item id = '
2651: ||oss_model_lines_rec.config_item_id,5);
2652: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: organization id = '
2653: ||oss_make_orgs.rcv_org_id,5);
2654:
2655:
2656: End if;
2672: l_stmt_num := 230;
2673:
2674:
2675: If PG_DEBUG <> 0 Then
2676: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: '||'Before reuse org loop ',5);
2677: End if;
2678:
2679: For oss_reused_assg_rec in
2680: oss_reused_assg(oss_model_lines_rec.line_id,oss_model_lines_rec.config_item_id)
2681: Loop
2682:
2683:
2684: If PG_DEBUG <> 0 Then
2685: oe_debug_pub.add(lpad(' ',g_pg_level)||'Inside Reuse assignments loop',5);
2686: oe_debug_pub.add(lpad(' ',g_pg_level)||'Assignment id = '||oss_reused_assg_rec.assignment_id,5);
2687: End if;
2688:
2689: l_Stmt_Num := 240;
2682:
2683:
2684: If PG_DEBUG <> 0 Then
2685: oe_debug_pub.add(lpad(' ',g_pg_level)||'Inside Reuse assignments loop',5);
2686: oe_debug_pub.add(lpad(' ',g_pg_level)||'Assignment id = '||oss_reused_assg_rec.assignment_id,5);
2687: End if;
2688:
2689: l_Stmt_Num := 240;
2690:
2777: FROM MRP_SR_ASSIGNMENTS
2778: WHERE ASSIGNMENT_ID = oss_reused_assg_rec.assignment_id;
2779:
2780: If PG_DEBUG <> 0 Then
2781: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Loading the assignment into assignment record ',5);
2782: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES: assignment_type = '
2783: ||lAssignmentRec.Assignment_Type,5);
2784: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: inventory item id = '
2785: ||oss_model_lines_rec.config_item_id,5);
2778: WHERE ASSIGNMENT_ID = oss_reused_assg_rec.assignment_id;
2779:
2780: If PG_DEBUG <> 0 Then
2781: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Loading the assignment into assignment record ',5);
2782: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES: assignment_type = '
2783: ||lAssignmentRec.Assignment_Type,5);
2784: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: inventory item id = '
2785: ||oss_model_lines_rec.config_item_id,5);
2786: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: organization id = '
2780: If PG_DEBUG <> 0 Then
2781: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: Loading the assignment into assignment record ',5);
2782: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES: assignment_type = '
2783: ||lAssignmentRec.Assignment_Type,5);
2784: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: inventory item id = '
2785: ||oss_model_lines_rec.config_item_id,5);
2786: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: organization id = '
2787: ||lAssignmentRec.Organization_Id,5);
2788: End if;
2782: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES: assignment_type = '
2783: ||lAssignmentRec.Assignment_Type,5);
2784: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: inventory item id = '
2785: ||oss_model_lines_rec.config_item_id,5);
2786: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: organization id = '
2787: ||lAssignmentRec.Organization_Id,5);
2788: End if;
2789:
2790: l_Stmt_Num := 245;
2842:
2843: l_stmt_num := 250;
2844:
2845: If pg_debug <> 0 then
2846: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: assignment count = '|| lAssignmentTbl.count,5);
2847: End if;
2848:
2849:
2850: If lAssignmentTbl.count <> 0 Then
2863: FOR l_index IN 1..x_msg_count LOOP
2864: l_msg_data := fnd_msg_pub.get(
2865: p_msg_index => l_index,
2866: p_encoded => FND_API.G_FALSE);
2867: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: error : '||substr(l_msg_data,1,250));
2868: END LOOP;
2869: If x_return_status = FND_API.G_RET_STS_ERROR Then
2870: IF PG_DEBUG <> 0 Then
2871: oe_debug_pub.add(lpad(' ',g_pg_level)||
2867: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES: error : '||substr(l_msg_data,1,250));
2868: END LOOP;
2869: If x_return_status = FND_API.G_RET_STS_ERROR Then
2870: IF PG_DEBUG <> 0 Then
2871: oe_debug_pub.add(lpad(' ',g_pg_level)||
2872: 'CREATE_OSS_SOURCING_RULES: Exepected error occurred in update_oss_in_bcol API',5);
2873: End if;
2874: raise FND_API.G_EXC_ERROR;
2875: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
2873: End if;
2874: raise FND_API.G_EXC_ERROR;
2875: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
2876: IF PG_DEBUG <> 0 Then
2877: oe_debug_pub.add(lpad(' ',g_pg_level)||
2878: 'CREATE_OSS_SOURCING_RULES: Un Exepected error occurred in update_oss_in_bcol API',5);
2879: End if;
2880: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2881: End if;
2915: Exception
2916:
2917: WHEN FND_API.G_EXC_ERROR THEN
2918: IF PG_DEBUG <> 0 THEN
2919: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCNIG_RULES::exp error::'
2920: ||to_char(l_stmt_num)
2921: ||'::'||sqlerrm,1);
2922: END IF;
2923: x_return_status := FND_API.G_RET_STS_ERROR;
2927: p_msg_data => x_msg_data
2928: );
2929: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2930: IF PG_DEBUG <> 0 THEN
2931: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES::exp error::'
2932: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
2933: END IF;
2934: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2935: g_pg_level := g_pg_level - 3;
2938: p_msg_data => x_msg_data
2939: );
2940: WHEN OTHERS THEN
2941: IF PG_DEBUG <> 0 THEN
2942: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULES::exp error::'
2943: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
2944: END IF;
2945: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2946: g_pg_level := g_pg_level - 3;
2972: l_stmt_num := 10;
2973: g_pg_level := g_pg_level + 3;
2974:
2975: If PG_DEBUG <> 0 Then
2976: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Inside Update Source Tree API',5);
2977: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Line id ='||p_line_id,5);
2978: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Org id ='||p_end_org,5);
2979: End if;
2980: If p_line_id is null and p_end_org is null then
2973: g_pg_level := g_pg_level + 3;
2974:
2975: If PG_DEBUG <> 0 Then
2976: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Inside Update Source Tree API',5);
2977: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Line id ='||p_line_id,5);
2978: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Org id ='||p_end_org,5);
2979: End if;
2980: If p_line_id is null and p_end_org is null then
2981: return;
2974:
2975: If PG_DEBUG <> 0 Then
2976: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Inside Update Source Tree API',5);
2977: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Line id ='||p_line_id,5);
2978: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Org id ='||p_end_org,5);
2979: End if;
2980: If p_line_id is null and p_end_org is null then
2981: return;
2982: end if;
2990: and nvl(leaf_node,'N') <> 'Y'
2991: returning rcv_org_id bulk collect into l_org_tbl;
2992:
2993: If PG_DEBUG <> 0 Then
2994: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Number parent orgs = '||l_org_tbl.count,5);
2995: End if;
2996:
2997: /* Need to work for Cutomer rules... */
2998:
3006: x_msg_count => x_msg_count
3007: );
3008: If x_return_status = FND_API.G_RET_STS_ERROR Then
3009: IF PG_DEBUG <> 0 Then
3010: oe_debug_pub.add(lpad(' ',g_pg_level)||
3011: 'UPDATE_SOURCE_TREE: Exepected error occurred in update_oss_in_bcol API',5);
3012: End if;
3013: raise FND_API.G_EXC_ERROR;
3014: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3012: End if;
3013: raise FND_API.G_EXC_ERROR;
3014: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3015: IF PG_DEBUG <> 0 Then
3016: oe_debug_pub.add(lpad(' ',g_pg_level)||
3017: 'UPDATE_SOURCE_TREE: Un Exepected error occurred in update_oss_in_bcol API',5);
3018: End if;
3019: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3020: End if;
3108: x_msg_count => x_msg_count,
3109: x_msg_data => x_msg_data);
3110: If x_return_status = FND_API.G_RET_STS_ERROR Then
3111: IF PG_DEBUG <> 0 Then
3112: oe_debug_pub.add(lpad(' ',g_pg_level)||
3113: 'PRUNE_ITEM_LEVEL_RULE: Exepected error occurred in update_oss_in_bcol API',5);
3114: End if;
3115: raise FND_API.G_EXC_ERROR;
3116: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3114: End if;
3115: raise FND_API.G_EXC_ERROR;
3116: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3117: IF PG_DEBUG <> 0 Then
3118: oe_debug_pub.add(lpad(' ',g_pg_level)||
3119: 'PRUNE_ITEM_LEVEL_RULE: Un Exepected error occurred in update_oss_in_bcol API',5);
3120: End if;
3121: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3122: End if;
3175: )
3176: returning rcv_org_id bulk collect into l_source_org_tbl;
3177:
3178:
3179: oe_debug_pub.add(lpad(' ',g_pg_level)||'Prune_item_level_rule Updated
3180: valid leaf nodes = '||l_source_org_tbl.count,1);
3181:
3182: /* For all the above leaf nodes, traverse the tree up
3183: and update all the parents as valid
3193: x_msg_count => x_msg_count,
3194: x_msg_data => x_msg_data);
3195: If x_return_status = FND_API.G_RET_STS_ERROR Then
3196: IF PG_DEBUG <> 0 Then
3197: oe_debug_pub.add(lpad(' ',g_pg_level)||
3198: 'PRUNE_ITEM_LEVEL_RULE: Exepected error occurred in update_oss_in_bcol API',5);
3199: End if;
3200: raise FND_API.G_EXC_ERROR;
3201: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3199: End if;
3200: raise FND_API.G_EXC_ERROR;
3201: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3202: IF PG_DEBUG <> 0 Then
3203: oe_debug_pub.add(lpad(' ',g_pg_level)||
3204: 'PRUNE_ITEM_LEVEL_RULE: Un Exepected error occurred in update_oss_in_bcol API',5);
3205: End if;
3206: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3207: End if;
3208: End if;
3209:
3210: End loop;
3211: Else /* X_rule_exists */
3212: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_ITEM_LEVEL_RULE: Updating bcol with option specific = 4',1);
3213:
3214: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
3215: bom_cto_order_lines_gt bcol
3216: set option_specific = 4
3243: Begin
3244: g_pg_level := nvl(g_pg_level,0)+3;
3245:
3246: If PG_DEBUG <> 0 Then
3247: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: Entering Find Leaf Node',5);
3248: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: P_source_org_id = '||p_source_org_id,5);
3249: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: P_rcv_org_id = '||p_rcv_org_id,5);
3250: End if;
3251:
3244: g_pg_level := nvl(g_pg_level,0)+3;
3245:
3246: If PG_DEBUG <> 0 Then
3247: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: Entering Find Leaf Node',5);
3248: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: P_source_org_id = '||p_source_org_id,5);
3249: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: P_rcv_org_id = '||p_rcv_org_id,5);
3250: End if;
3251:
3252: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3245:
3246: If PG_DEBUG <> 0 Then
3247: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: Entering Find Leaf Node',5);
3248: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: P_source_org_id = '||p_source_org_id,5);
3249: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: P_rcv_org_id = '||p_rcv_org_id,5);
3250: End if;
3251:
3252: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3253: source_org_id,
3260: and line_id = p_model_line_id
3261: and nvl(valid_flag,'Y') <> 'N';
3262:
3263: IF PG_DEBUG <> 0 Then
3264: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: Source Org count = '||l_source_org_id.count,5);
3265: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: Source Type count = '||l_source_type.count,5);
3266: End if;
3267:
3268: If l_source_type.count <> 0 then
3261: and nvl(valid_flag,'Y') <> 'N';
3262:
3263: IF PG_DEBUG <> 0 Then
3264: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: Source Org count = '||l_source_org_id.count,5);
3265: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: Source Type count = '||l_source_type.count,5);
3266: End if;
3267:
3268: If l_source_type.count <> 0 then
3269: For i in l_source_type.first..l_source_type.last
3278: x_msg_count => x_msg_count);
3279:
3280: If x_return_status = FND_API.G_RET_STS_ERROR Then
3281: IF PG_DEBUG <> 0 Then
3282: oe_debug_pub.add(lpad(' ',g_pg_level)||
3283: 'FIND_LEAF_NODE: Exepected error occurred in update_oss_in_bcol API',5);
3284: End if;
3285: raise FND_API.G_EXC_ERROR;
3286: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3284: End if;
3285: raise FND_API.G_EXC_ERROR;
3286: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3287: IF PG_DEBUG <> 0 Then
3288: oe_debug_pub.add(lpad(' ',g_pg_level)||
3289: 'FIND_LEAF_NODE: Un Exepected error occurred in update_oss_in_bcol API',5);
3290: End if;
3291: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3292: End if;
3315: x_msg_count => x_msg_count,
3316: x_msg_data => x_msg_data);
3317: If x_return_status = FND_API.G_RET_STS_ERROR Then
3318: IF PG_DEBUG <> 0 Then
3319: oe_debug_pub.add(lpad(' ',g_pg_level)||
3320: 'FIND_LEAF_NODE: Exepected error occurred in update_oss_in_bcol API',5);
3321: End if;
3322: raise FND_API.G_EXC_ERROR;
3323: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3321: End if;
3322: raise FND_API.G_EXC_ERROR;
3323: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3324: IF PG_DEBUG <> 0 Then
3325: oe_debug_pub.add(lpad(' ',g_pg_level)||
3326: 'FIND_LEAF_NODE: Un Exepected error occurred in update_oss_in_bcol API',5);
3327: End if;
3328: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3329: End if;
3351: x_msg_count => x_msg_count,
3352: x_msg_data => x_msg_data);
3353: If x_return_status = FND_API.G_RET_STS_ERROR Then
3354: IF PG_DEBUG <> 0 Then
3355: oe_debug_pub.add(lpad(' ',g_pg_level)||
3356: 'FIND_LEAF_NODE: Exepected error occurred in update_oss_in_bcol API',5);
3357: End if;
3358: raise FND_API.G_EXC_ERROR;
3359: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3357: End if;
3358: raise FND_API.G_EXC_ERROR;
3359: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3360: IF PG_DEBUG <> 0 Then
3361: oe_debug_pub.add(lpad(' ',g_pg_level)||
3362: 'FIND_LEAF_NODE: Un Exepected error occurred in update_oss_in_bcol API',5);
3363: End if;
3364: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3365: End if;
3385: Begin
3386: g_pg_level := g_pg_level + 3;
3387:
3388: If PG_DEBUG <> 0 Then
3389: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Entering Traverse up tree API',5);
3390: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Model Line id =
3391: '||p_model_line_id,1);
3392: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Source Org id =
3393: '||p_source_org_id,1);
3386: g_pg_level := g_pg_level + 3;
3387:
3388: If PG_DEBUG <> 0 Then
3389: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Entering Traverse up tree API',5);
3390: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Model Line id =
3391: '||p_model_line_id,1);
3392: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Source Org id =
3393: '||p_source_org_id,1);
3394:
3388: If PG_DEBUG <> 0 Then
3389: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Entering Traverse up tree API',5);
3390: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Model Line id =
3391: '||p_model_line_id,1);
3392: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Source Org id =
3393: '||p_source_org_id,1);
3394:
3395: End if;
3396:
3403: and source_type <> 2 /*Exclude make rules...*/
3404: returning source_org_id,rcv_org_id bulk collect into l_rcv_org_tbl, l_org_id_tbl;
3405:
3406: If PG_DEBUG <> 0 then
3407: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_TREE_UP: Number of parents updated = '||l_org_id_tbl.count,5);
3408: end if;
3409:
3410: If l_org_id_tbl.count <> 0 Then
3411: For i in l_org_id_tbl.first..l_org_id_tbl.last
3410: If l_org_id_tbl.count <> 0 Then
3411: For i in l_org_id_tbl.first..l_org_id_tbl.last
3412: Loop
3413:
3414: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Rcv org id = '||l_rcv_org_tbl(i),1);
3415: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Org id = '||l_org_id_tbl(i),1);
3416:
3417: If l_rcv_org_tbl(i) <> l_org_id_tbl(i)
3418: and l_rcv_org_tbl(i) is not null then
3411: For i in l_org_id_tbl.first..l_org_id_tbl.last
3412: Loop
3413:
3414: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Rcv org id = '||l_rcv_org_tbl(i),1);
3415: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_UP_TREE: Org id = '||l_org_id_tbl(i),1);
3416:
3417: If l_rcv_org_tbl(i) <> l_org_id_tbl(i)
3418: and l_rcv_org_tbl(i) is not null then
3419:
3425: x_msg_count => x_msg_count,
3426: x_msg_data => x_msg_data);
3427: if x_return_status = FND_API.G_RET_STS_ERROR Then
3428: IF PG_DEBUG <> 0 Then
3429: oe_debug_pub.add(lpad(' ',g_pg_level)||
3430: 'TRAVERSE_UP_TREE: Exepected error occurred in update_oss_in_bcol API',5);
3431: End if;
3432: raise FND_API.G_EXC_ERROR;
3433: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3431: End if;
3432: raise FND_API.G_EXC_ERROR;
3433: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3434: IF PG_DEBUG <> 0 Then
3435: oe_debug_pub.add(lpad(' ',g_pg_level)||
3436: 'TRAVERSE_UP_TREE: Un Exepected error occurred in update_oss_in_bcol API',5);
3437: End if;
3438: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3439: End if;
3484:
3485: x_return_status := FND_API.G_RET_STS_SUCCESS;
3486: l_stmt_num := 10;
3487:
3488: oe_debug_pub.add('=========================================================================',1);
3489: oe_debug_pub.add(' ',1);
3490: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
3491: oe_debug_pub.add(' ',1);
3492: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3485: x_return_status := FND_API.G_RET_STS_SUCCESS;
3486: l_stmt_num := 10;
3487:
3488: oe_debug_pub.add('=========================================================================',1);
3489: oe_debug_pub.add(' ',1);
3490: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
3491: oe_debug_pub.add(' ',1);
3492: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3493: oe_debug_pub.add(' ',1);
3486: l_stmt_num := 10;
3487:
3488: oe_debug_pub.add('=========================================================================',1);
3489: oe_debug_pub.add(' ',1);
3490: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
3491: oe_debug_pub.add(' ',1);
3492: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3493: oe_debug_pub.add(' ',1);
3494: oe_debug_pub.add('=========================================================================',1);
3487:
3488: oe_debug_pub.add('=========================================================================',1);
3489: oe_debug_pub.add(' ',1);
3490: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
3491: oe_debug_pub.add(' ',1);
3492: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3493: oe_debug_pub.add(' ',1);
3494: oe_debug_pub.add('=========================================================================',1);
3495:
3488: oe_debug_pub.add('=========================================================================',1);
3489: oe_debug_pub.add(' ',1);
3490: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
3491: oe_debug_pub.add(' ',1);
3492: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3493: oe_debug_pub.add(' ',1);
3494: oe_debug_pub.add('=========================================================================',1);
3495:
3496: If PG_DEBUG <> 0 Then
3489: oe_debug_pub.add(' ',1);
3490: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
3491: oe_debug_pub.add(' ',1);
3492: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3493: oe_debug_pub.add(' ',1);
3494: oe_debug_pub.add('=========================================================================',1);
3495:
3496: If PG_DEBUG <> 0 Then
3497: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Begin GET_OSS_ORGS_LIST API',5);
3490: oe_debug_pub.add(' START OPTION SPECIFIC SOURCE PROCESSING ',1);
3491: oe_debug_pub.add(' ',1);
3492: oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3493: oe_debug_pub.add(' ',1);
3494: oe_debug_pub.add('=========================================================================',1);
3495:
3496: If PG_DEBUG <> 0 Then
3497: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Begin GET_OSS_ORGS_LIST API',5);
3498: End if;
3493: oe_debug_pub.add(' ',1);
3494: oe_debug_pub.add('=========================================================================',1);
3495:
3496: If PG_DEBUG <> 0 Then
3497: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Begin GET_OSS_ORGS_LIST API',5);
3498: End if;
3499:
3500: delete from bom_cto_oss_source_gt ;
3501: If PG_DEBUG <> 0 Then
3498: End if;
3499:
3500: delete from bom_cto_oss_source_gt ;
3501: If PG_DEBUG <> 0 Then
3502: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Number of records delete in source_gt = '
3503: ||sql%rowcount,5);
3504: end if;
3505:
3506: delete from bom_cto_oss_orgslist_gt;
3505:
3506: delete from bom_cto_oss_orgslist_gt;
3507:
3508: If PG_DEBUG <> 0 Then
3509: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Number of records delete in source_gt = '
3510: ||sql%rowcount,5);
3511: end if;
3512:
3513: g_def_assg_set := to_number(
3519: */
3520:
3521:
3522: If PG_DEBUG <> 0 then
3523: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Default Assignment set id = '
3524: ||g_def_assg_set);
3525: End if;
3526:
3527: If g_def_assg_set is null then
3525: End if;
3526:
3527: If g_def_assg_set is null then
3528: If PG_DEBUG <> 0 Then
3529: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: There is no default assignment set Specified',4);
3530: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3531: oe_debug_pub.add('=========================================================================',1);
3532: oe_debug_pub.add(' ',1);
3533: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3526:
3527: If g_def_assg_set is null then
3528: If PG_DEBUG <> 0 Then
3529: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: There is no default assignment set Specified',4);
3530: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3531: oe_debug_pub.add('=========================================================================',1);
3532: oe_debug_pub.add(' ',1);
3533: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3534: oe_debug_pub.add(' ',1);
3527: If g_def_assg_set is null then
3528: If PG_DEBUG <> 0 Then
3529: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: There is no default assignment set Specified',4);
3530: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3531: oe_debug_pub.add('=========================================================================',1);
3532: oe_debug_pub.add(' ',1);
3533: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3534: oe_debug_pub.add(' ',1);
3535: oe_debug_pub.add(' ',1);
3528: If PG_DEBUG <> 0 Then
3529: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: There is no default assignment set Specified',4);
3530: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3531: oe_debug_pub.add('=========================================================================',1);
3532: oe_debug_pub.add(' ',1);
3533: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3534: oe_debug_pub.add(' ',1);
3535: oe_debug_pub.add(' ',1);
3536: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3529: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: There is no default assignment set Specified',4);
3530: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3531: oe_debug_pub.add('=========================================================================',1);
3532: oe_debug_pub.add(' ',1);
3533: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3534: oe_debug_pub.add(' ',1);
3535: oe_debug_pub.add(' ',1);
3536: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3537: oe_debug_pub.add(' ',1);
3530: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3531: oe_debug_pub.add('=========================================================================',1);
3532: oe_debug_pub.add(' ',1);
3533: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3534: oe_debug_pub.add(' ',1);
3535: oe_debug_pub.add(' ',1);
3536: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3537: oe_debug_pub.add(' ',1);
3538: oe_debug_pub.add('=========================================================================',1);
3531: oe_debug_pub.add('=========================================================================',1);
3532: oe_debug_pub.add(' ',1);
3533: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3534: oe_debug_pub.add(' ',1);
3535: oe_debug_pub.add(' ',1);
3536: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3537: oe_debug_pub.add(' ',1);
3538: oe_debug_pub.add('=========================================================================',1);
3539:
3532: oe_debug_pub.add(' ',1);
3533: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3534: oe_debug_pub.add(' ',1);
3535: oe_debug_pub.add(' ',1);
3536: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3537: oe_debug_pub.add(' ',1);
3538: oe_debug_pub.add('=========================================================================',1);
3539:
3540: End if;
3533: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3534: oe_debug_pub.add(' ',1);
3535: oe_debug_pub.add(' ',1);
3536: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3537: oe_debug_pub.add(' ',1);
3538: oe_debug_pub.add('=========================================================================',1);
3539:
3540: End if;
3541: return;
3534: oe_debug_pub.add(' ',1);
3535: oe_debug_pub.add(' ',1);
3536: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3537: oe_debug_pub.add(' ',1);
3538: oe_debug_pub.add('=========================================================================',1);
3539:
3540: End if;
3541: return;
3542: end if;
3560: where exists (select 'x' from bom_cto_oss_components);
3561:
3562: If l_check_flag = 'X' then
3563: If PG_DEBUG <> 0 Then
3564: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Some OSS Setup Exists',5);
3565: End if;
3566: end if;
3567: Exception when no_data_found then
3568: If PG_DEBUG <>0 then
3565: End if;
3566: end if;
3567: Exception when no_data_found then
3568: If PG_DEBUG <>0 then
3569: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: No OSS orgs defied in the system',4);
3570: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3571: oe_debug_pub.add('=========================================================================',1);
3572: oe_debug_pub.add(' ',1);
3573: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3566: end if;
3567: Exception when no_data_found then
3568: If PG_DEBUG <>0 then
3569: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: No OSS orgs defied in the system',4);
3570: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3571: oe_debug_pub.add('=========================================================================',1);
3572: oe_debug_pub.add(' ',1);
3573: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3574: oe_debug_pub.add(' ',1);
3567: Exception when no_data_found then
3568: If PG_DEBUG <>0 then
3569: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: No OSS orgs defied in the system',4);
3570: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3571: oe_debug_pub.add('=========================================================================',1);
3572: oe_debug_pub.add(' ',1);
3573: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3574: oe_debug_pub.add(' ',1);
3575: oe_debug_pub.add(' ',1);
3568: If PG_DEBUG <>0 then
3569: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: No OSS orgs defied in the system',4);
3570: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3571: oe_debug_pub.add('=========================================================================',1);
3572: oe_debug_pub.add(' ',1);
3573: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3574: oe_debug_pub.add(' ',1);
3575: oe_debug_pub.add(' ',1);
3576: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3569: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: No OSS orgs defied in the system',4);
3570: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3571: oe_debug_pub.add('=========================================================================',1);
3572: oe_debug_pub.add(' ',1);
3573: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3574: oe_debug_pub.add(' ',1);
3575: oe_debug_pub.add(' ',1);
3576: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3577: oe_debug_pub.add(' ',1);
3570: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3571: oe_debug_pub.add('=========================================================================',1);
3572: oe_debug_pub.add(' ',1);
3573: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3574: oe_debug_pub.add(' ',1);
3575: oe_debug_pub.add(' ',1);
3576: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3577: oe_debug_pub.add(' ',1);
3578: oe_debug_pub.add('=========================================================================',1);
3571: oe_debug_pub.add('=========================================================================',1);
3572: oe_debug_pub.add(' ',1);
3573: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3574: oe_debug_pub.add(' ',1);
3575: oe_debug_pub.add(' ',1);
3576: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3577: oe_debug_pub.add(' ',1);
3578: oe_debug_pub.add('=========================================================================',1);
3579:
3572: oe_debug_pub.add(' ',1);
3573: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3574: oe_debug_pub.add(' ',1);
3575: oe_debug_pub.add(' ',1);
3576: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3577: oe_debug_pub.add(' ',1);
3578: oe_debug_pub.add('=========================================================================',1);
3579:
3580: End if;
3573: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3574: oe_debug_pub.add(' ',1);
3575: oe_debug_pub.add(' ',1);
3576: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3577: oe_debug_pub.add(' ',1);
3578: oe_debug_pub.add('=========================================================================',1);
3579:
3580: End if;
3581:
3574: oe_debug_pub.add(' ',1);
3575: oe_debug_pub.add(' ',1);
3576: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3577: oe_debug_pub.add(' ',1);
3578: oe_debug_pub.add('=========================================================================',1);
3579:
3580: End if;
3581:
3582: return;
3594: );
3595:
3596: If x_return_status = FND_API.G_RET_STS_ERROR Then
3597: IF PG_DEBUG <> 0 Then
3598: oe_debug_pub.add(lpad(' ',g_pg_level)||
3599: 'GET_OSS_ORGS_LIST: Exepected error occurred in get_configurations_org API',5);
3600: End if;
3601: raise FND_API.G_EXC_ERROR;
3602: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3600: End if;
3601: raise FND_API.G_EXC_ERROR;
3602: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3603: IF PG_DEBUG <> 0 Then
3604: oe_debug_pub.add(lpad(' ',g_pg_level)||
3605: 'GET_OSS_ORGS_LIST: Un Exepected error occurred in get_configurations_org API',5);
3606: End if;
3607: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3608: End if;
3621: );
3622:
3623: If x_return_status = FND_API.G_RET_STS_ERROR Then
3624: IF PG_DEBUG <> 0 Then
3625: oe_debug_pub.add(lpad(' ',g_pg_level)||
3626: 'GET_OSS_ORGS_LIST: Exepected error occurred in get_configurations_org API',5);
3627: End if;
3628: raise FND_API.G_EXC_ERROR;
3629: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3627: End if;
3628: raise FND_API.G_EXC_ERROR;
3629: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3630: IF PG_DEBUG <> 0 Then
3631: oe_debug_pub.add(lpad(' ',g_pg_level)||
3632: 'GET_OSS_ORGS_LIST: Un Exepected error occurred in get_configurations_org API',5);
3633: End if;
3634: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3635: End if;
3659: If PG_DEBUG <> 0 Then
3660: If x_oss_orgs_list.line_id.count <> 0 Then
3661: For i in x_oss_orgs_list.line_id.first..x_oss_orgs_list.line_id.last
3662: Loop
3663: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Line id = '||x_oss_orgs_list.line_id(i),5);
3664: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Org id = '||x_oss_orgs_list.org_id(i),5);
3665: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Vendor id = '||x_oss_orgs_list.Vendor_id(i),5);
3666: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Vendor Site = '||x_oss_orgs_list.vendor_site(i),5);
3667: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Make Flag = '||x_oss_orgs_list.make_flag(i),5);
3660: If x_oss_orgs_list.line_id.count <> 0 Then
3661: For i in x_oss_orgs_list.line_id.first..x_oss_orgs_list.line_id.last
3662: Loop
3663: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Line id = '||x_oss_orgs_list.line_id(i),5);
3664: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Org id = '||x_oss_orgs_list.org_id(i),5);
3665: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Vendor id = '||x_oss_orgs_list.Vendor_id(i),5);
3666: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Vendor Site = '||x_oss_orgs_list.vendor_site(i),5);
3667: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Make Flag = '||x_oss_orgs_list.make_flag(i),5);
3668: End loop;
3661: For i in x_oss_orgs_list.line_id.first..x_oss_orgs_list.line_id.last
3662: Loop
3663: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Line id = '||x_oss_orgs_list.line_id(i),5);
3664: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Org id = '||x_oss_orgs_list.org_id(i),5);
3665: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Vendor id = '||x_oss_orgs_list.Vendor_id(i),5);
3666: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Vendor Site = '||x_oss_orgs_list.vendor_site(i),5);
3667: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Make Flag = '||x_oss_orgs_list.make_flag(i),5);
3668: End loop;
3669: End if;
3662: Loop
3663: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Line id = '||x_oss_orgs_list.line_id(i),5);
3664: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Org id = '||x_oss_orgs_list.org_id(i),5);
3665: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Vendor id = '||x_oss_orgs_list.Vendor_id(i),5);
3666: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Vendor Site = '||x_oss_orgs_list.vendor_site(i),5);
3667: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Make Flag = '||x_oss_orgs_list.make_flag(i),5);
3668: End loop;
3669: End if;
3670: End if;
3663: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Line id = '||x_oss_orgs_list.line_id(i),5);
3664: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Org id = '||x_oss_orgs_list.org_id(i),5);
3665: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Vendor id = '||x_oss_orgs_list.Vendor_id(i),5);
3666: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Vendor Site = '||x_oss_orgs_list.vendor_site(i),5);
3667: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Make Flag = '||x_oss_orgs_list.make_flag(i),5);
3668: End loop;
3669: End if;
3670: End if;
3671:
3669: End if;
3670: End if;
3671:
3672: If PG_DEBUG <> 0 Then
3673: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Number of records insert to output structure ='||sql%rowcount,4);
3674: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3675: End if;
3676:
3677:
3670: End if;
3671:
3672: If PG_DEBUG <> 0 Then
3673: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Number of records insert to output structure ='||sql%rowcount,4);
3674: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3675: End if;
3676:
3677:
3678: oe_debug_pub.add('=========================================================================',1);
3674: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Ending the call',4);
3675: End if;
3676:
3677:
3678: oe_debug_pub.add('=========================================================================',1);
3679: oe_debug_pub.add(' ',1);
3680: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3681: oe_debug_pub.add(' ',1);
3682: oe_debug_pub.add(' ',1);
3675: End if;
3676:
3677:
3678: oe_debug_pub.add('=========================================================================',1);
3679: oe_debug_pub.add(' ',1);
3680: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3681: oe_debug_pub.add(' ',1);
3682: oe_debug_pub.add(' ',1);
3683: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3676:
3677:
3678: oe_debug_pub.add('=========================================================================',1);
3679: oe_debug_pub.add(' ',1);
3680: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3681: oe_debug_pub.add(' ',1);
3682: oe_debug_pub.add(' ',1);
3683: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3684: oe_debug_pub.add(' ',1);
3677:
3678: oe_debug_pub.add('=========================================================================',1);
3679: oe_debug_pub.add(' ',1);
3680: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3681: oe_debug_pub.add(' ',1);
3682: oe_debug_pub.add(' ',1);
3683: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3684: oe_debug_pub.add(' ',1);
3685: oe_debug_pub.add('=========================================================================',1);
3678: oe_debug_pub.add('=========================================================================',1);
3679: oe_debug_pub.add(' ',1);
3680: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3681: oe_debug_pub.add(' ',1);
3682: oe_debug_pub.add(' ',1);
3683: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3684: oe_debug_pub.add(' ',1);
3685: oe_debug_pub.add('=========================================================================',1);
3686:
3679: oe_debug_pub.add(' ',1);
3680: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3681: oe_debug_pub.add(' ',1);
3682: oe_debug_pub.add(' ',1);
3683: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3684: oe_debug_pub.add(' ',1);
3685: oe_debug_pub.add('=========================================================================',1);
3686:
3687:
3680: oe_debug_pub.add(' END OPTION SPECIFIC SOURCE PROCESSING ',1);
3681: oe_debug_pub.add(' ',1);
3682: oe_debug_pub.add(' ',1);
3683: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3684: oe_debug_pub.add(' ',1);
3685: oe_debug_pub.add('=========================================================================',1);
3686:
3687:
3688: Exception
3681: oe_debug_pub.add(' ',1);
3682: oe_debug_pub.add(' ',1);
3683: oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',1);
3684: oe_debug_pub.add(' ',1);
3685: oe_debug_pub.add('=========================================================================',1);
3686:
3687:
3688: Exception
3689:
3688: Exception
3689:
3690: WHEN FND_API.G_EXC_ERROR THEN
3691: IF PG_DEBUG <> 0 THEN
3692: oe_debug_pub.add(lpad(' ',g_pg_level)||'Get_OSS_Orgs_list::exp error::'
3693: ||to_char(l_stmt_num)
3694: ||'::'||sqlerrm,1);
3695: END IF;
3696: x_return_status := FND_API.G_RET_STS_ERROR;
3699: p_msg_data => x_msg_data
3700: );
3701: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3702: IF PG_DEBUG <> 0 THEN
3703: oe_debug_pub.add(lpad(' ',g_pg_level)||'Get_OSS_Orgs_list::exp error::'
3704: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
3705: END IF;
3706: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3707: cto_msg_pub.count_and_get(
3709: p_msg_data => x_msg_data
3710: );
3711: WHEN OTHERS THEN
3712: IF PG_DEBUG <> 0 THEN
3713: oe_debug_pub.add(lpad(' ',g_pg_level)||'Get_OSS_Orgs_list::exp error::'
3714: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
3715: END IF;
3716: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3717: cto_msg_pub.count_and_get(
3780: x_return_status := FND_API.G_RET_STS_SUCCESS;
3781:
3782:
3783: If PG_DEBUG <> 0 Then
3784: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG : Entering Model Process',5);
3785: End if;
3786:
3787: /* Get the all models which has new config items created and the configuration
3788: has some oss orgs defined. The cursor definition resolves all the condition.
3792: FOR oss_top_model_rec in oss_top_models
3793: LOOP
3794:
3795: If PG_DEBUG <> 0 then
3796: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG: ATO Line id ='
3797: ||oss_top_model_rec.ato_line_id,5);
3798: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG: Processing top model line = '
3799: ||oss_top_model_rec.ato_line_id,5);
3800: End if;
3794:
3795: If PG_DEBUG <> 0 then
3796: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG: ATO Line id ='
3797: ||oss_top_model_rec.ato_line_id,5);
3798: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG: Processing top model line = '
3799: ||oss_top_model_rec.ato_line_id,5);
3800: End if;
3801:
3802:
3809: x_msg_data => x_msg_data,
3810: x_msg_count => x_msg_count);
3811: If x_return_status = FND_API.G_RET_STS_ERROR Then
3812: IF PG_DEBUG <> 0 Then
3813: oe_debug_pub.add(lpad(' ',g_pg_level)||
3814: 'GET_OSS_ORGS_LIST: Exepected error occurred in update_oss_in_bcol API',5);
3815: End if;
3816: raise FND_API.G_EXC_ERROR;
3817: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3815: End if;
3816: raise FND_API.G_EXC_ERROR;
3817: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3818: IF PG_DEBUG <> 0 Then
3819: oe_debug_pub.add(lpad(' ',g_pg_level)||
3820: 'GET_OSS_ORGS_LIST: Un Exepected error occurred in update_oss_in_bcol API',5);
3821: End if;
3822: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3823: End if;
3825:
3826: If X_oss_exists = 'Y' Then
3827:
3828: If PG_DEBUG <> 0 Then
3829: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG: This order line has some oss models',5);
3830: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG: Start processing OSS Model bottom-up',5);
3831: End if;
3832:
3833: get_sourcing_data(
3826: If X_oss_exists = 'Y' Then
3827:
3828: If PG_DEBUG <> 0 Then
3829: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG: This order line has some oss models',5);
3830: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG: Start processing OSS Model bottom-up',5);
3831: End if;
3832:
3833: get_sourcing_data(
3834: p_ato_line_id => oss_top_model_rec.ato_line_id,
3838:
3839: If x_return_status = FND_API.G_RET_STS_ERROR Then
3840:
3841: IF PG_DEBUG <> 0 Then
3842: oe_debug_pub.add(lpad(' ',g_pg_level)||
3843: 'GET_OSS_ORGS_LIST: Exepected error occurred in get_sourcing_data API',5);
3844: End if;
3845: raise FND_API.G_EXC_ERROR;
3846: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3844: End if;
3845: raise FND_API.G_EXC_ERROR;
3846: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3847: IF PG_DEBUG <> 0 Then
3848: oe_debug_pub.add(lpad(' ',g_pg_level)||
3849: 'GET_OSS_ORGS_LIST: Un Exepected error occurred in get_sourcing_data API',5);
3850: End if;
3851: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3852: End if; /* x_return_status = FND_API.G_RET_STS_ERROR */
3859: x_msg_data => x_msg_data);
3860:
3861: If x_return_status = FND_API.G_RET_STS_ERROR Then
3862: IF PG_DEBUG <> 0 Then
3863: oe_debug_pub.add(lpad(' ',g_pg_level)||
3864: 'GET_CONFIGURATIONS_ORG: Exepected error occurred in update_oss_in_bcol API',5);
3865: End if;
3866: raise FND_API.G_EXC_ERROR;
3867: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3865: End if;
3866: raise FND_API.G_EXC_ERROR;
3867: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
3868: IF PG_DEBUG <> 0 Then
3869: oe_debug_pub.add(lpad(' ',g_pg_level)||
3870: 'GET_CONFIGURATIONS_ORG: Un Exepected error occurred in update_oss_in_bcol API',5);
3871: End if;
3872: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3873: End if;
3874:
3875: Else
3876:
3877: IF PG_DEBUG <> 0 Then
3878: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG: This order line does not have any OSS configuration',5);
3879: End if;
3880: End if;
3881:
3882: END LOOP;
3985: Exception
3986:
3987: WHEN FND_API.G_EXC_ERROR THEN
3988: IF PG_DEBUG <> 0 THEN
3989: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG: get_configurations_org::exp error::'
3990: ||to_char(l_stmt_no)
3991: ||'::'||sqlerrm,1);
3992: END IF;
3993: g_pg_level := g_pg_level - 3;
3997: p_msg_data => x_msg_data
3998: );
3999: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4000: IF PG_DEBUG <> 0 THEN
4001: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG: get_configurations_org::exp error::'
4002: ||to_char(l_stmt_no)||'::'||sqlerrm,1);
4003: END IF;
4004: g_pg_level := g_pg_level - 3;
4005: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4008: p_msg_data => x_msg_data
4009: );
4010: WHEN OTHERS THEN
4011: IF PG_DEBUG <> 0 THEN
4012: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_CONFIGURATIONS_ORG: get_configurations_org::exp error::'
4013: ||to_char(l_stmt_no)||'::'||sqlerrm,1);
4014: END IF;
4015: g_pg_level := g_pg_level - 3;
4016: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4041:
4042: l_stmt_num := 10;
4043:
4044: If PG_DEBUG <> 0 Then
4045: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ATO_ITEM_ORGS: Assignment set id = '||p_assignment_id);
4046: End if;
4047:
4048: /* The following sql will insert all the orgs and vendors from sourcing assignments
4049: and sourcing rules for ato item and matched configuration item
4209: and msi.option_specific_sourced = 3);
4210:
4211:
4212: If PG_DEBUG <> 0 Then
4213: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ATO_ITEM_ORGS: Number of records inserted = '||sql%rowcount);
4214: End if;
4215:
4216: g_pg_level := g_pg_level - 3;
4217:
4218: Exception
4219:
4220: WHEN FND_API.G_EXC_ERROR THEN
4221: IF PG_DEBUG <> 0 THEN
4222: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ATO_ITEM_ORGS::exp error::'
4223: ||to_char(l_stmt_num)
4224: ||'::'||sqlerrm,1);
4225: END IF;
4226: g_pg_level := g_pg_level - 3;
4230: p_msg_data => x_msg_data
4231: );
4232: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4233: IF PG_DEBUG <> 0 THEN
4234: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ATO_ITEM_ORGS::exp error::'
4235: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
4236: END IF;
4237: g_pg_level := g_pg_level - 3;
4238: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4241: p_msg_data => x_msg_data
4242: );
4243: WHEN OTHERS THEN
4244: IF PG_DEBUG <> 0 THEN
4245: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ATO_ITEM_ORGS::exp error::'
4246: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
4247: END IF;
4248: g_pg_level := g_pg_level - 3;
4249: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4302: x_return_status := FND_API.G_RET_STS_SUCCESS;
4303: g_pg_level := g_pg_level + 3;
4304:
4305: IF PG_DEBUG <> 0 Then
4306: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: In UPDATE_OSS_BCOL API',5);
4307: End if;
4308: x_oss_exists :='Y';
4309:
4310: l_stmt_num := 10;
4328: returning parent_ato_line_id bulk collect into l_parent_ato_line_tbl;
4329:
4330:
4331: If PG_DEBUG <> 0 Then
4332: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: Number of OSS configuratinos = '
4333: ||sql%rowcount,5);
4334: End if;
4335: If sql%rowcount = 0 then
4336: IF PG_DEBUG <> 0 Then
4333: ||sql%rowcount,5);
4334: End if;
4335: If sql%rowcount = 0 then
4336: IF PG_DEBUG <> 0 Then
4337: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: No OSS configuration exists..',5);
4338: End if;
4339: x_oss_exists := 'N';
4340: g_pg_level := g_pg_level - 3;
4341: return;
4346: by line id
4347: */
4348:
4349: IF PG_DEBUG <> 0 Then
4350: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: Caching BCOL Data',5);
4351: End if;
4352:
4353: l_stmt_num := 20;
4354:
4373: /* For the top model no need to traverse */
4374:
4375:
4376: IF PG_DEBUG <> 0 Then
4377: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: Falgging OSS for parents',5);
4378: End if;
4379:
4380: l_stmt_num := 30;
4381:
4400: where line_id = g_parent_rec.line_id(i);
4401: end if;
4402:
4403: IF PG_DEBUG <> 0 Then
4404: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: Number of parent records updated = '
4405: ||g_parent_rec.line_id.count,5);
4406: End if;
4407:
4408:
4423: and bcol.config_creation = '3'
4424: and bcol.ato_line_id = p_ato_line_id;
4425:
4426: IF PG_DEBUG <> 0 Then
4427: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: Number of matched configs with attribute settting 3 ='
4428: ||sql%rowcount,5);
4429: End if;
4430:
4431: l_stmt_num := 60;
4437:
4438: Exception
4439: WHEN FND_API.G_EXC_ERROR THEN
4440: IF PG_DEBUG <> 0 THEN
4441: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL::exp error::'
4442: ||to_char(l_stmt_num)
4443: ||'::'||sqlerrm,1);
4444: END IF;
4445: g_pg_level := g_pg_level - 3;
4449: p_msg_data => x_msg_data
4450: );
4451: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4452: IF PG_DEBUG <> 0 THEN
4453: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL::exp error::'
4454: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
4455: END IF;
4456: g_pg_level := g_pg_level - 3;
4457: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4460: p_msg_data => x_msg_data
4461: );
4462: WHEN OTHERS THEN
4463: IF PG_DEBUG <> 0 THEN
4464: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL::exp error::'
4465: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
4466: END IF;
4467: g_pg_level := g_pg_level - 3;
4468: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4511:
4512:
4513:
4514: If PG_DEBUG <> 0 Then
4515: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_PARENT_OSS_LINE: Inside UPDATE_PARENT_OSS_LIEN API',5);
4516: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_PARENT_OSS_LINE: Line id = '||g_bcol_tbl(p_parent_ato_line_id).line_id,5);
4517: End if;
4518:
4519: l_stmt_num := 20;
4512:
4513:
4514: If PG_DEBUG <> 0 Then
4515: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_PARENT_OSS_LINE: Inside UPDATE_PARENT_OSS_LIEN API',5);
4516: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_PARENT_OSS_LINE: Line id = '||g_bcol_tbl(p_parent_ato_line_id).line_id,5);
4517: End if;
4518:
4519: l_stmt_num := 20;
4520: g_parent_rec.line_id.extend(g_tbl_index);
4582:
4583: Exception
4584: WHEN FND_API.G_EXC_ERROR THEN
4585: IF PG_DEBUG <> 0 THEN
4586: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_PARENT_OSS_LINE::exp error::'
4587: ||to_char(l_stmt_num)
4588: ||'::'||sqlerrm,1);
4589: END IF;
4590: g_pg_level := g_pg_level - 3;
4594: p_msg_data => x_msg_data
4595: );
4596: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4597: IF PG_DEBUG <> 0 THEN
4598: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_PARENT_OSS_LINE::exp error::'
4599: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
4600: END IF;
4601: g_pg_level := g_pg_level - 3;
4602: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4605: p_msg_data => x_msg_data
4606: );
4607: WHEN OTHERS THEN
4608: IF PG_DEBUG <> 0 THEN
4609: oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_PARENT_OSS_LINE::exp error::'
4610: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
4611: END IF;
4612: g_pg_level := g_pg_level - 3;
4613: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4631: x_return_status := FND_API.G_RET_STS_SUCCESS;
4632: l_stmt_num := 10;
4633:
4634: IF PG_DEBUG <> 0 Then
4635: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Inside GET_SOURCING_DATA API',5);
4636: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Assignment set id ='||g_def_assg_set,5);
4637: End if;
4638:
4639: Insert into bom_cto_oss_source_gt
4632: l_stmt_num := 10;
4633:
4634: IF PG_DEBUG <> 0 Then
4635: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Inside GET_SOURCING_DATA API',5);
4636: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Assignment set id ='||g_def_assg_set,5);
4637: End if;
4638:
4639: Insert into bom_cto_oss_source_gt
4640: (
4698: and src.vendor_site_id = vend.vendor_site_id(+);
4699:
4700:
4701: If PG_DEBUG <> 0 Then
4702: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Number of records inserted in 1st sql ='||sql%rowcount,5);
4703: End if;
4704: Insert into bom_cto_oss_source_gt
4705: (
4706: Inventory_item_id,
4764: and nvl(rcv.disable_date,sysdate+1)>sysdate
4765: and rcv.SR_RECEIPT_ID = src.sr_receipt_id
4766: and src.vendor_site_id = vend.vendor_site_id(+);
4767: If PG_DEBUG <> 0 Then
4768: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Number of records inserted in 2nd sql ='||sql%rowcount,5);
4769: End if;
4770:
4771: /*
4772:
4827: and rcv.SR_RECEIPT_ID = src.sr_receipt_id
4828: and src.vendor_site_id = vend.vendor_site_id(+);
4829:
4830: If PG_DEBUG <> 0 Then
4831: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Number of assignment records inserted = '
4832: ||sql%rowcount,5);
4833: End if;
4834:
4835: elsif p_config_creation in ('1','2') then
4881: l_stmt_num := 10;
4882: g_pg_level := g_pg_level + 3;
4883:
4884: If PG_DEBUG <> 0 Then
4885: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: Inside PROCESS_ORDER_FOR_OSS API',5);
4886: End if;
4887:
4888: l_stmt_num := 20;
4889:
4894: For oss_model_rec in oss_models
4895: Loop
4896:
4897: If PG_DEBUG <> 0 Then
4898: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: Inside PROCESS_ORDER_FOR_OSS API....',5);
4899: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: OSS process for line id = '
4900: ||oss_model_rec.line_id,5);
4901: End if;
4902:
4895: Loop
4896:
4897: If PG_DEBUG <> 0 Then
4898: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: Inside PROCESS_ORDER_FOR_OSS API....',5);
4899: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: OSS process for line id = '
4900: ||oss_model_rec.line_id,5);
4901: End if;
4902:
4903:
4911:
4912: if oss_model_rec.option_specific in ('1','2') then
4913:
4914: IF PG_DEBUG <> 0 Then
4915: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: Calling Prune OSS Config..',5);
4916: End if;
4917:
4918: l_stmt_num := 70;
4919: prune_oss_config(
4929: );
4930:
4931: If x_return_status = FND_API.G_RET_STS_ERROR Then
4932: IF PG_DEBUG <> 0 Then
4933: oe_debug_pub.add(lpad(' ',g_pg_level)||
4934: 'GET_OSS_ORGS_LIST: Exepected error occurred in prune_oss_config API',5);
4935: End if;
4936: raise FND_API.G_EXC_ERROR;
4937:
4937:
4938: Elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
4939:
4940: IF PG_DEBUG <> 0 Then
4941: oe_debug_pub.add(lpad(' ',g_pg_level)||
4942: 'GET_OSS_ORGS_LIST: Un Exepected error occurred in prune_oss_config API',5);
4943: End if;
4944: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4945:
4954: set oss_error_code = x_exp_error_code
4955: where line_id = oss_model_rec.line_id;
4956:
4957: If PG_DEBUG <> 0 Then
4958: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: Setting current model error code tp 350',5);
4959: End if;
4960:
4961: If oss_model_rec.line_id <> oss_model_rec.ato_line_id then
4962:
4968: where line_id = oss_model_rec.ato_line_id;
4969: exit;
4970:
4971: If PG_DEBUG <> 0 Then
4972: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: setting parent model error code to 360',5);
4973: End if;
4974: End if; /* oss_model_rec.line_id <> oss_model_rec.ato_line_id */
4975:
4976: End if; /* x_exp_error_code <> 0 */
4975:
4976: End if; /* x_exp_error_code <> 0 */
4977:
4978: If PG_DEBUG <> 0 Then
4979: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: After prune oss config.',5);
4980: End if;
4981:
4982: End if; /* oss_model_rec.option_specific in ('1','2') */
4983:
4981:
4982: End if; /* oss_model_rec.option_specific in ('1','2') */
4983:
4984: If PG_DEBUG <> 0 Then
4985: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: Option Specific = '
4986: ||oss_model_rec.option_specific,5);
4987: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: Exp Error Code = '
4988: || x_exp_error_code,5);
4989: End if;
4983:
4984: If PG_DEBUG <> 0 Then
4985: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: Option Specific = '
4986: ||oss_model_rec.option_specific,5);
4987: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: Exp Error Code = '
4988: || x_exp_error_code,5);
4989: End if;
4990:
4991: l_stmt_num := 100;
4992:
4993: If oss_model_rec.option_specific in ('2','3') and nvl(x_exp_error_code,0) = 0 then
4994:
4995: If PG_DEBUG <> 0 Then
4996: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: Before Prune Parent oss config API',5);
4997: End if;
4998: l_stmt_num := 110;
4999:
5000: Prune_parent_oss_config(
5009: );
5010:
5011: If x_return_status = FND_API.G_RET_STS_ERROR Then
5012: IF PG_DEBUG <> 0 Then
5013: oe_debug_pub.add(lpad(' ',g_pg_level)||
5014: 'PROCESS_ORDER_FOR_OSS: Exepected error occurred in prune_parent_oss_config API',5);
5015: End if;
5016: raise FND_API.G_EXC_ERROR;
5017: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
5015: End if;
5016: raise FND_API.G_EXC_ERROR;
5017: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
5018: IF PG_DEBUG <> 0 Then
5019: oe_debug_pub.add(lpad(' ',g_pg_level)||
5020: 'PROCESS_ORDER_FOR_OSS: Un Exepected error occurred in prune_parent_oss_config API',5);
5021: End if;
5022: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5023: End if; /* x_return_status = FND_API.G_RET_STS_ERROR */
5029: set oss_error_code = x_exp_error_code
5030: where line_id = oss_model_rec.line_id;
5031:
5032: If PG_DEBUG <> 0 Then
5033: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: Setting current model error code tp 350',5);
5034: End if;
5035:
5036: If oss_model_rec.line_id <> oss_model_rec.ato_line_id then
5037: l_stmt_num := 130;
5039: bom_cto_order_lines_gt bcol
5040: set oss_error_code = 360
5041: where line_id = oss_model_rec.ato_line_id;
5042: If PG_DEBUG <> 0 Then
5043: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: setting parent model error code to 360',5);
5044: End if;
5045: end if; /*oss_model_rec.line_id <> oss_model_rec.ato_line_id*/
5046: End if; /* x_exp_error_code */
5047:
5054:
5055: Exception
5056: WHEN FND_API.G_EXC_ERROR THEN
5057: IF PG_DEBUG <> 0 THEN
5058: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS::exp error::'
5059: ||to_char(l_stmt_num)
5060: ||'::'||sqlerrm,1);
5061: END IF;
5062: g_pg_level := g_pg_level - 3;
5066: p_msg_data => x_msg_data
5067: );
5068: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5069: IF PG_DEBUG <> 0 THEN
5070: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS::exp error::'
5071: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
5072: END IF;
5073: g_pg_level := g_pg_level - 3;
5074: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5077: p_msg_data => x_msg_data
5078: );
5079: WHEN OTHERS THEN
5080: IF PG_DEBUG <> 0 THEN
5081: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS::exp error::'
5082: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
5083: END IF;
5084: g_pg_level := g_pg_level - 3;
5085: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5102: x_return_status := FND_API.G_RET_STS_SUCCESS;
5103: l_stmt_num := 10;
5104:
5105: If pg_debug <> 0 Then
5106: oe_debug_pub.add(lpad(' ',g_pg_level)||'COPY_TO_BCOL_TEMP: Inside Copy to Bcol Temp API',5);
5107: end if;
5108:
5109: delete /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
5110: from bom_cto_order_lines_gt bcol
5166: where ato_line_id = p_ato_line_id;
5167:
5168:
5169: If PG_DEBUG <> 0 Then
5170: oe_debug_pub.add(lpad(' ',g_pg_level)||'COPY_TO_BCOL_TEMP: Number of lines inserted in to temp '
5171: || sql%rowcount,5);
5172: End if;
5173: g_pg_level := g_pg_level - 3;
5174: End COPY_TO_BCOL_TEMP;
5217: delete /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N1) */
5218: from bom_cto_oss_source_gt oss_src where ato_line_id = p_ato_line_id;
5219:
5220: If PG_DEBUG <> 0 Then
5221: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCNIG_DATA: Inside GET_ORDER_SOURCING_DATA API',5);
5222: End if;
5223:
5224: Insert
5225: into bom_cto_oss_orgslist_gt(
5238:
5239: For model_lines_rec in model_lines_cur
5240: Loop
5241: If PG_DEBUG <> 0 Then
5242: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCNIG_DATA: Processing model line = '||model_lines_rec.line_id,5);
5243: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCNIG_DATA: Processing model Item = '||model_lines_rec.inventory_item_id,5);
5244: End if;
5245:
5246: l_stmt_num := 30;
5239: For model_lines_rec in model_lines_cur
5240: Loop
5241: If PG_DEBUG <> 0 Then
5242: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCNIG_DATA: Processing model line = '||model_lines_rec.line_id,5);
5243: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCNIG_DATA: Processing model Item = '||model_lines_rec.inventory_item_id,5);
5244: End if;
5245:
5246: l_stmt_num := 30;
5247:
5253: g_assg_list.delete;
5254: For mfg_orgs_rec in mfg_orgs_cur(l_parent_line_id)
5255: Loop
5256: If PG_DEBUG <> 0 Then
5257: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCNIG_DATA: get sourcing chain from org = '||mfg_orgs_rec.organization_id,5);
5258: End if;
5259: l_stmt_num := 40;
5260: Traverse_sourcing_chain(
5261: p_item_id => model_lines_rec.inventory_item_id,
5270: End loop;
5271: End Loop;
5272:
5273: If PG_DEBUG <> 0 Then
5274: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCNIG_DATA: after the loop on crsr model_lines_cur',5);
5275: End if;
5276:
5277: l_stmt_num := 41;
5278: if x_assg_list.assignment_id.count <> 0 Then
5276:
5277: l_stmt_num := 41;
5278: if x_assg_list.assignment_id.count <> 0 Then
5279: if PG_DEBUG <> 0 Then
5280: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA: Before inserting the assignments into temp table',5);
5281: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA: Assignment count = '||x_assg_list.assignment_id.count,1);
5282: end if;
5283:
5284: l_stmt_num := 50;
5277: l_stmt_num := 41;
5278: if x_assg_list.assignment_id.count <> 0 Then
5279: if PG_DEBUG <> 0 Then
5280: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA: Before inserting the assignments into temp table',5);
5281: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA: Assignment count = '||x_assg_list.assignment_id.count,1);
5282: end if;
5283:
5284: l_stmt_num := 50;
5285: FORALL i in x_assg_list.assignment_id.first..x_assg_list.assignment_id.last
5343:
5344:
5345:
5346: IF PG_DEBUG <> 0 Then
5347: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA: Before inserting Model attribute 3 lines',5);
5348: End if;
5349:
5350: l_stmt_num := 60;
5351: Insert into bom_cto_oss_source_gt
5484:
5485: Exception
5486: WHEN FND_API.G_EXC_ERROR THEN
5487: IF PG_DEBUG <> 0 THEN
5488: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA::exp error::'
5489: ||to_char(l_stmt_num)
5490: ||'::'||sqlerrm,1);
5491: END IF;
5492: g_pg_level := g_pg_level - 3;
5496: p_msg_data => x_msg_data
5497: );
5498: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5499: IF PG_DEBUG <> 0 THEN
5500: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA::exp error::'
5501: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
5502: END IF;
5503: g_pg_level := g_pg_level - 3;
5504: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5507: p_msg_data => x_msg_data
5508: );
5509: WHEN OTHERS THEN
5510: IF PG_DEBUG <> 0 THEN
5511: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA::exp error::'
5512: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
5513: END IF;
5514: g_pg_level := g_pg_level - 3;
5515: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5561: g_pg_level := g_pg_level + 3;
5562: x_return_status := FND_API.G_RET_STS_SUCCESS;
5563:
5564: IF PG_DEBUG <> 0 Then
5565: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Inside Traverse_Souricng_Chain API',5);
5566: End if;
5567:
5568: --
5569: -- Added By Renga Kannan on 11/21/03
5571: --
5572:
5573: If G_source_org_stk.exists(p_org_id) then
5574: if PG_DEBUG <> 0 then
5575: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Circular sourcing deducted..',5);
5576: end if;
5577: cto_msg_pub.cto_message('BOM','CTO_INVALID_SOURCING');
5578: raise FND_API.G_EXC_ERROR;
5579: Else
5589: l_stmt_num := 20;
5590: For src_rec in src_cur
5591: Loop
5592: If PG_DEBUG <> 0 Then
5593: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Assignment Type = '||src_rec.assignment_type,1);
5594: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Assignment id = '||src_rec.assignment_id,1);
5595: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Source org id = '||src_rec.source_organization_id,1);
5596: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Rcv org id = '||src_rec.organization_id,1);
5597: End if;
5590: For src_rec in src_cur
5591: Loop
5592: If PG_DEBUG <> 0 Then
5593: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Assignment Type = '||src_rec.assignment_type,1);
5594: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Assignment id = '||src_rec.assignment_id,1);
5595: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Source org id = '||src_rec.source_organization_id,1);
5596: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Rcv org id = '||src_rec.organization_id,1);
5597: End if;
5598: l_stmt_num := 30;
5591: Loop
5592: If PG_DEBUG <> 0 Then
5593: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Assignment Type = '||src_rec.assignment_type,1);
5594: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Assignment id = '||src_rec.assignment_id,1);
5595: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Source org id = '||src_rec.source_organization_id,1);
5596: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Rcv org id = '||src_rec.organization_id,1);
5597: End if;
5598: l_stmt_num := 30;
5599: If src_rec.assignment_type in (3,6) Then
5592: If PG_DEBUG <> 0 Then
5593: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Assignment Type = '||src_rec.assignment_type,1);
5594: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Assignment id = '||src_rec.assignment_id,1);
5595: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Source org id = '||src_rec.source_organization_id,1);
5596: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Rcv org id = '||src_rec.organization_id,1);
5597: End if;
5598: l_stmt_num := 30;
5599: If src_rec.assignment_type in (3,6) Then
5600: If not g_assg_list.exists(src_rec.assignment_id) and p_option_specific is not null Then
5598: l_stmt_num := 30;
5599: If src_rec.assignment_type in (3,6) Then
5600: If not g_assg_list.exists(src_rec.assignment_id) and p_option_specific is not null Then
5601:
5602: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Registering the assignment id ',1);
5603: l_stmt_num := 40;
5604: x_assg_list.assignment_id.extend(x_assg_list.assignment_id.count+1);
5605: x_assg_list.assignment_id(x_assg_list.assignment_id.last) := src_rec.assignment_id;
5606: x_assg_list.line_id.extend(x_assg_list.line_id.count+1);
5605: x_assg_list.assignment_id(x_assg_list.assignment_id.last) := src_rec.assignment_id;
5606: x_assg_list.line_id.extend(x_assg_list.line_id.count+1);
5607: x_assg_list.line_id(x_assg_list.line_id.last) := p_line_id;
5608: g_assg_list(src_rec.assignment_id) := src_rec.assignment_id;
5609: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Line id = '||x_assg_list.line_id(x_assg_list.line_id.last),1);
5610: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Assg id = '||x_assg_list.assignment_id(x_assg_list.assignment_id.last),1);
5611: End if;
5612: l_assg_id := src_rec.assignment_id;
5613: If src_rec.source_type in (2,3) Then
5606: x_assg_list.line_id.extend(x_assg_list.line_id.count+1);
5607: x_assg_list.line_id(x_assg_list.line_id.last) := p_line_id;
5608: g_assg_list(src_rec.assignment_id) := src_rec.assignment_id;
5609: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Line id = '||x_assg_list.line_id(x_assg_list.line_id.last),1);
5610: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: Assg id = '||x_assg_list.assignment_id(x_assg_list.assignment_id.last),1);
5611: End if;
5612: l_assg_id := src_rec.assignment_id;
5613: If src_rec.source_type in (2,3) Then
5614: l_stmt_num := 50;
5611: End if;
5612: l_assg_id := src_rec.assignment_id;
5613: If src_rec.source_type in (2,3) Then
5614: l_stmt_num := 50;
5615: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: End org = '||src_rec.organization_id,1);
5616: insert into bom_cto_oss_orgslist_gt(
5617: line_id,
5618: organization_id,
5619: ato_line_id
5625: );
5626:
5627: Else
5628: If PG_DEBUG <> 0 Then
5629: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOOURCING_CHAIN: Before calling traverse sourcing chain recurrsive',5);
5630: End if;
5631: l_stmt_num := 60;
5632: Traverse_sourcing_chain(
5633: p_item_id => p_item_id,
5645: End if; /* src_rec.assignment_type in (3,6) */
5646: End Loop;
5647: If l_assg_id =0 Then
5648: If PG_DEBUG <> 0 Then
5649: oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_SOURCING_CHAIN: End of source chain',5);
5650: End if;
5651:
5652: insert into bom_cto_oss_orgslist_gt(
5653: line_id,
5709: x_return_status := FND_API.G_RET_STS_SUCCESS;
5710: i := 1;
5711:
5712: If PG_DEBUG <> 0 Then
5713: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: Inside Query OSS Sourcing Org API',5);
5714: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: p_line_id = '||p_line_id,5);
5715: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: p_org_id = '||p_organization_id,5);
5716: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: p_inventory_item_id = '||p_inventory_item_id,5);
5717: End if;
5710: i := 1;
5711:
5712: If PG_DEBUG <> 0 Then
5713: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: Inside Query OSS Sourcing Org API',5);
5714: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: p_line_id = '||p_line_id,5);
5715: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: p_org_id = '||p_organization_id,5);
5716: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: p_inventory_item_id = '||p_inventory_item_id,5);
5717: End if;
5718:
5711:
5712: If PG_DEBUG <> 0 Then
5713: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: Inside Query OSS Sourcing Org API',5);
5714: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: p_line_id = '||p_line_id,5);
5715: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: p_org_id = '||p_organization_id,5);
5716: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: p_inventory_item_id = '||p_inventory_item_id,5);
5717: End if;
5718:
5719: For Source_org_rule_rec in Source_org_rule_cur
5712: If PG_DEBUG <> 0 Then
5713: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: Inside Query OSS Sourcing Org API',5);
5714: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: p_line_id = '||p_line_id,5);
5715: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: p_org_id = '||p_organization_id,5);
5716: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: p_inventory_item_id = '||p_inventory_item_id,5);
5717: End if;
5718:
5719: For Source_org_rule_rec in Source_org_rule_cur
5720: Loop
5722: x_t_sourcing_info.source_organization_id(i) := Source_org_rule_rec.source_org_id;
5723: x_t_sourcing_info.source_type(i) := Source_org_rule_rec.source_type;
5724:
5725: If PG_DEBUG <> 0 Then
5726: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing org = '
5727: ||x_t_sourcing_info.source_organization_id(i),5);
5728: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing Type = '
5729: ||x_t_sourcing_info.source_type(i),5);
5730:
5724:
5725: If PG_DEBUG <> 0 Then
5726: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing org = '
5727: ||x_t_sourcing_info.source_organization_id(i),5);
5728: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing Type = '
5729: ||x_t_sourcing_info.source_type(i),5);
5730:
5731: End if;
5732: i := i +1 ;
5735: elsif source_org_rule_rec.source_type in (1,2) then
5736: x_t_sourcing_info.source_organization_id(i) := Source_org_rule_rec.source_org_id;
5737: x_t_sourcing_info.source_type(i) := Source_org_rule_rec.source_type;
5738: If PG_DEBUG <> 0 Then
5739: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing org = '
5740: ||x_t_sourcing_info.source_organization_id(i),5);
5741: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing Type = '
5742: ||x_t_sourcing_info.source_type(i),5);
5743:
5737: x_t_sourcing_info.source_type(i) := Source_org_rule_rec.source_type;
5738: If PG_DEBUG <> 0 Then
5739: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing org = '
5740: ||x_t_sourcing_info.source_organization_id(i),5);
5741: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing Type = '
5742: ||x_t_sourcing_info.source_type(i),5);
5743:
5744: End if;
5745:
5747: End if;
5748: End Loop;
5749:
5750: If PG_DEBUG <> 0 Then
5751: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: Number of orgs based on orgs rule ='
5752: ||x_t_sourcing_info.source_organization_id.count,5);
5753: End if;
5754:
5755: If x_t_sourcing_info.source_organization_id.count = 0 Then
5759: If Source_item_rule_rec.source_type = 3 and l_buy_type = 'N' Then
5760: x_t_sourcing_info.source_organization_id(i) := Source_item_rule_rec.source_org_id;
5761: x_t_sourcing_info.source_type(i) := Source_item_rule_rec.source_type;
5762: If PG_DEBUG <> 0 Then
5763: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing org = '
5764: ||x_t_sourcing_info.source_organization_id(i),5);
5765: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing Type = '
5766: ||x_t_sourcing_info.source_type(i),5);
5767:
5761: x_t_sourcing_info.source_type(i) := Source_item_rule_rec.source_type;
5762: If PG_DEBUG <> 0 Then
5763: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing org = '
5764: ||x_t_sourcing_info.source_organization_id(i),5);
5765: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing Type = '
5766: ||x_t_sourcing_info.source_type(i),5);
5767:
5768: End if;
5769:
5772: elsif Source_item_rule_rec.source_type in (1,2) then
5773: x_t_sourcing_info.source_organization_id(i) := Source_item_rule_rec.source_org_id;
5774: x_t_sourcing_info.source_type(i) := Source_item_rule_rec.source_type;
5775: If PG_DEBUG <> 0 Then
5776: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing org = '
5777: ||x_t_sourcing_info.source_organization_id(i),5);
5778: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing Type = '
5779: ||x_t_sourcing_info.source_type(i),5);
5780:
5774: x_t_sourcing_info.source_type(i) := Source_item_rule_rec.source_type;
5775: If PG_DEBUG <> 0 Then
5776: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing org = '
5777: ||x_t_sourcing_info.source_organization_id(i),5);
5778: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: sourcing Type = '
5779: ||x_t_sourcing_info.source_type(i),5);
5780:
5781: End if;
5782:
5784: End if;
5785: End Loop;
5786:
5787: If PG_DEBUG <> 0 Then
5788: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: Number of orgs based on item rule ='
5789: ||x_t_sourcing_info.source_organization_id.count,5);
5790: End if;
5791:
5792:
5799: from mtl_system_items
5800: where inventory_item_id = p_inventory_item_id
5801: and organization_id = p_organization_id;
5802: If PG_DEBUG <> 0 Then
5803: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: No sourcing rule exists',5);
5804: End if;
5805: else
5806: If PG_DEBUG <> 0 Then
5807: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: Number of sourcin orgs = '
5803: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: No sourcing rule exists',5);
5804: End if;
5805: else
5806: If PG_DEBUG <> 0 Then
5807: oe_debug_pub.add(lpad(' ',g_pg_level)||'QUERY_OSS_SOURCING_ORG: Number of sourcin orgs = '
5808: ||x_t_sourcing_info.source_organization_id.count,5);
5809: end if;
5810: x_sourcing_rule_exists := 'Y';
5811: End if;
5813: g_pg_level := g_pg_level - 3;
5814: Exception
5815: WHEN FND_API.G_EXC_ERROR THEN
5816: IF PG_DEBUG <> 0 THEN
5817: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA::exp error::'
5818: ||to_char(l_stmt_num)
5819: ||'::'||sqlerrm,1);
5820: END IF;
5821: g_pg_level := g_pg_level - 3;
5825: p_msg_data => x_msg_data
5826: );
5827: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5828: IF PG_DEBUG <> 0 THEN
5829: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA::exp error::'
5830: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
5831: END IF;
5832: g_pg_level := g_pg_level - 3;
5833: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5836: p_msg_data => x_msg_data
5837: );
5838: WHEN OTHERS THEN
5839: IF PG_DEBUG <> 0 THEN
5840: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA::exp error::'
5841: ||to_char(l_stmt_num)||'::'||sqlerrm,1);
5842: END IF;
5843: g_pg_level := g_pg_level - 3;
5844: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5867: g_pg_level := nvl(g_pg_level,0) + 3;
5868: x_return_status := FND_API.G_RET_STS_SUCCESS;
5869: l_stmt_num := 10;
5870:
5871: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_BOM_ORGS: Begin ',1);
5872:
5873: Select org_id
5874: bulk collect into x_orgs_list
5875: from
5891: from bom_cto_oss_source_gt oss_src
5892: where line_id = p_line_id
5893: and valid_flag in( 'P','Y')));
5894: If PG_DEBUG <> 0 Then
5895: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_BOM_ORGS: Number of orgs where bom should be created = '
5896: ||x_orgs_list.count,5);
5897: End if;
5898:
5899: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_BOM_ORGS: End ',1);
5895: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_BOM_ORGS: Number of orgs where bom should be created = '
5896: ||x_orgs_list.count,5);
5897: End if;
5898:
5899: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_BOM_ORGS: End ',1);
5900:
5901: End GET_OSS_BOM_ORGS;
5902:
5903:
5921: from bom_cto_oss_source_gt oss_src
5922: where line_id = p_line_id;
5923:
5924: Begin
5925: oe_debug_pub.add('================PRINTING BOM_CTO_OSS_SOURCE_GT==================',5);
5926: oe_debug_pub.add('================ Line id = '||p_line_id||'======================',5);
5927: oe_debug_pub.add('Item id --- Rcv org --- src org --- customer --- vendor --- vend site --- rank --- alloc% --- src type --- reuse -- valid --- leaf ---',5);
5928: for source_rec in source_cur
5929: loop
5922: where line_id = p_line_id;
5923:
5924: Begin
5925: oe_debug_pub.add('================PRINTING BOM_CTO_OSS_SOURCE_GT==================',5);
5926: oe_debug_pub.add('================ Line id = '||p_line_id||'======================',5);
5927: oe_debug_pub.add('Item id --- Rcv org --- src org --- customer --- vendor --- vend site --- rank --- alloc% --- src type --- reuse -- valid --- leaf ---',5);
5928: for source_rec in source_cur
5929: loop
5930: oe_debug_pub.add(source_rec.inventory_item_id||' --- '
5923:
5924: Begin
5925: oe_debug_pub.add('================PRINTING BOM_CTO_OSS_SOURCE_GT==================',5);
5926: oe_debug_pub.add('================ Line id = '||p_line_id||'======================',5);
5927: oe_debug_pub.add('Item id --- Rcv org --- src org --- customer --- vendor --- vend site --- rank --- alloc% --- src type --- reuse -- valid --- leaf ---',5);
5928: for source_rec in source_cur
5929: loop
5930: oe_debug_pub.add(source_rec.inventory_item_id||' --- '
5931: ||source_rec.rcv_org_id||' --- '||source_rec.source_org_id
5926: oe_debug_pub.add('================ Line id = '||p_line_id||'======================',5);
5927: oe_debug_pub.add('Item id --- Rcv org --- src org --- customer --- vendor --- vend site --- rank --- alloc% --- src type --- reuse -- valid --- leaf ---',5);
5928: for source_rec in source_cur
5929: loop
5930: oe_debug_pub.add(source_rec.inventory_item_id||' --- '
5931: ||source_rec.rcv_org_id||' --- '||source_rec.source_org_id
5932: ||' --- '||source_rec.customer_id||' --- '||source_rec.vendor_id
5933: ||' --- '||source_rec.vendor_site_code||' --- '||source_rec.rank
5934: ||' --- '||source_rec.allocation||' --- '||source_rec.source_type
5935: ||' --- '||source_rec.reuse_flag||' --- '||source_rec.valid_flag
5936: ||' --- '||source_rec.leaf_node,5);
5937: End Loop;
5938:
5939: oe_debug_pub.add('============== End printing ===============',5);
5940: End;
5941:
5942:
5943: Procedure Print_orglist_gt(p_line_id IN Number) is
5951: make_flag
5952: from bom_cto_oss_orgslist_gt
5953: where line_id = p_line_id;
5954: begin
5955: oe_debug_pub.add('================PRINTING BOM_CTO_ORGSLIST_GT==================',5);
5956: oe_debug_pub.add('================ Line id = '||p_line_id||'======================',5);
5957: oe_debug_pub.add('Line id --- Ato Line Id --- Item Id --- Org id --- vendor --- vend site ---Make Flag',5);
5958: for org_list_rec in org_list_cur
5959: Loop
5952: from bom_cto_oss_orgslist_gt
5953: where line_id = p_line_id;
5954: begin
5955: oe_debug_pub.add('================PRINTING BOM_CTO_ORGSLIST_GT==================',5);
5956: oe_debug_pub.add('================ Line id = '||p_line_id||'======================',5);
5957: oe_debug_pub.add('Line id --- Ato Line Id --- Item Id --- Org id --- vendor --- vend site ---Make Flag',5);
5958: for org_list_rec in org_list_cur
5959: Loop
5960: oe_debug_pub.add(org_list_rec.line_id||' --- '||org_list_rec.ato_line_id||' --- '||
5953: where line_id = p_line_id;
5954: begin
5955: oe_debug_pub.add('================PRINTING BOM_CTO_ORGSLIST_GT==================',5);
5956: oe_debug_pub.add('================ Line id = '||p_line_id||'======================',5);
5957: oe_debug_pub.add('Line id --- Ato Line Id --- Item Id --- Org id --- vendor --- vend site ---Make Flag',5);
5958: for org_list_rec in org_list_cur
5959: Loop
5960: oe_debug_pub.add(org_list_rec.line_id||' --- '||org_list_rec.ato_line_id||' --- '||
5961: org_list_rec.inventory_item_id||' --- '||org_list_rec.organization_id||' --- '||
5956: oe_debug_pub.add('================ Line id = '||p_line_id||'======================',5);
5957: oe_debug_pub.add('Line id --- Ato Line Id --- Item Id --- Org id --- vendor --- vend site ---Make Flag',5);
5958: for org_list_rec in org_list_cur
5959: Loop
5960: oe_debug_pub.add(org_list_rec.line_id||' --- '||org_list_rec.ato_line_id||' --- '||
5961: org_list_rec.inventory_item_id||' --- '||org_list_rec.organization_id||' --- '||
5962: org_list_rec.vendor_id||' --- '||org_list_rec.vendor_site_code||' --- '||
5963: org_list_rec.make_flag,5);
5964: End Loop;
5961: org_list_rec.inventory_item_id||' --- '||org_list_rec.organization_id||' --- '||
5962: org_list_rec.vendor_id||' --- '||org_list_rec.vendor_site_code||' --- '||
5963: org_list_rec.make_flag,5);
5964: End Loop;
5965: oe_debug_pub.add('============== End printing ===============',5);
5966: End;
5967: END CTO_OSS_SOURCE_PK;