[Home] [Help]
1: PACKAGE BODY wsh_opsm_asn_item_genealogy AS
2: /* $Header: wshopsmasnigb.pls 120.9 2011/02/02 16:43:44 skaradib noship $ */
3:
4:
5: ---------------------------------------------------------------------------------------
428: l_stmt_no := 0;
429: FND_LOG.String(
430: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
431: MESSAGE => l_stmt_no ||': Entering get_genealogy',
432: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
433: FND_LOG.String(
434: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
435: MESSAGE => ' p_gen_object_id= '|| p_gen_object_id,
436: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
432: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
433: FND_LOG.String(
434: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
435: MESSAGE => ' p_gen_object_id= '|| p_gen_object_id,
436: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
437:
438: FND_LOG.String(
439: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
440: MESSAGE => ' p_delivery_detail_id= '|| p_delivery_detail_id,
437:
438: FND_LOG.String(
439: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
440: MESSAGE => ' p_delivery_detail_id= '|| p_delivery_detail_id,
441: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
442:
443: FND_LOG.String(
444: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
445: MESSAGE => ' p_delivery_id= '|| p_delivery_id,
442:
443: FND_LOG.String(
444: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
445: MESSAGE => ' p_delivery_id= '|| p_delivery_id,
446: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
447:
448: FND_LOG.String(
449: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
450: MESSAGE => ' p_organization_id= '|| p_organization_id,
447:
448: FND_LOG.String(
449: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
450: MESSAGE => ' p_organization_id= '|| p_organization_id,
451: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
452:
453: FND_LOG.String(
454: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
455: MESSAGE => ' p_frm_serial= '|| p_frm_serial,
452:
453: FND_LOG.String(
454: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
455: MESSAGE => ' p_frm_serial= '|| p_frm_serial,
456: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
457:
458: FND_LOG.String(
459: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
460: MESSAGE => ' p_to_serial= '|| p_to_serial,
457:
458: FND_LOG.String(
459: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
460: MESSAGE => ' p_to_serial= '|| p_to_serial,
461: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
462:
463: END IF;
464:
465: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
521: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
522: FND_LOG.String(
523: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
524: MESSAGE => l_stmt_no ||': OPEN c_main_sql(p_delivery_detail_id, p_organization_id)= ',
525: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
526: FND_LOG.String(
527: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
528: MESSAGE => l_stmt_no ||': p_delivery_detail_id= '|| p_delivery_detail_id,
529: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
525: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
526: FND_LOG.String(
527: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
528: MESSAGE => l_stmt_no ||': p_delivery_detail_id= '|| p_delivery_detail_id,
529: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
530:
531: FND_LOG.String(
532: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
533: MESSAGE => l_stmt_no ||': p_organization_id= '|| p_organization_id,
530:
531: FND_LOG.String(
532: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
533: MESSAGE => l_stmt_no ||': p_organization_id= '|| p_organization_id,
534: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
535:
536: END IF;
537: ----dbms_output.put_line(p_delivery_detail_id);
538: OPEN c_main_sql(p_delivery_detail_id, p_organization_id);
548: FOR i3 IN dbg_genealogy_tab.FIRST .. dbg_genealogy_tab.LAST LOOP
549: FND_LOG.String(
550: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
551: MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).item_nbr= '|| dbg_genealogy_tab(i3).item_nbr,
552: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
553:
554: FND_LOG.String(
555: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
556: MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).serial_number= '|| dbg_genealogy_tab(i3).serial_number,
553:
554: FND_LOG.String(
555: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
556: MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).serial_number= '|| dbg_genealogy_tab(i3).serial_number,
557: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
558:
559: FND_LOG.String(
560: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
561: MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).job_name= '|| dbg_genealogy_tab(i3).job_name,
558:
559: FND_LOG.String(
560: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
561: MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).job_name= '|| dbg_genealogy_tab(i3).job_name,
562: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
563: END LOOP;
564: END IF;
565: END IF;
566:
573: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
574: FND_LOG.String(
575: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
576: MESSAGE => l_stmt_no ||': IF genealogy_tab.COUNT = 0 THEN',
577: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
578: FND_LOG.String(
579: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
580: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_sub_sql BULK COLLECT INTO genealogy_tab USING p_gen_object_id;',
581: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
577: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
578: FND_LOG.String(
579: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
580: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_sub_sql BULK COLLECT INTO genealogy_tab USING p_gen_object_id;',
581: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
582:
583: END IF;
584: ----dbms_output.put_line(12);
585: EXECUTE IMMEDIATE g_sub_sql
592: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
593: FND_LOG.String(
594: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
595: MESSAGE => l_stmt_no ||': FOR I IN 1..genealogy_tab.COUNT',
596: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
597:
598: END IF;
599:
600: FOR I IN 1..genealogy_tab.COUNT
607: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
608: FND_LOG.String(
609: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
610: MESSAGE => l_stmt_no ||': genealogy_tab(i).item_nbr= '|| genealogy_tab(i).item_nbr,
611: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
612:
613: FND_LOG.String(
614: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
615: MESSAGE => l_stmt_no ||': genealogy_tab(i).serial_number= '|| genealogy_tab(i).serial_number,
612:
613: FND_LOG.String(
614: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
615: MESSAGE => l_stmt_no ||': genealogy_tab(i).serial_number= '|| genealogy_tab(i).serial_number,
616: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
617: END IF;
618:
619: v_level := 1;
620: genealogy_rec.item_nbr := genealogy_tab(i).item_nbr;
641: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
642: FND_LOG.String(
643: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
644: MESSAGE => l_stmt_no ||': B4 IF NOT occ.EXISTS(genealogy_rec.item_nbr',
645: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
646: END IF;
647: IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
648: genealogy_rec.lot_number || '.' ||
649: genealogy_rec.serial_number || '.' ||
656: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
657: FND_LOG.String(
658: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
659: MESSAGE => l_stmt_no ||': After IF NOT occ.EXISTS(genealogy_rec.item_nbr',
660: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
661: END IF;
662: occ(genealogy_rec.item_nbr || '.' ||
663: genealogy_rec.lot_number || '.' ||
664: genealogy_rec.serial_number || '.' ||
676: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
677: FND_LOG.String(
678: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
679: MESSAGE => l_stmt_no ||': IF v_wms_enabled <> 1 THEN',
680: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
681: END IF;
682:
683: genealogy_rec.DELIVERY_DETAIL_ID := p_delivery_detail_id;
684: genealogy_rec.DELIVERY_ID := p_delivery_id;
695: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
696: FND_LOG.String(
697: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
698: MESSAGE => l_stmt_no ||': IF v_wms_enabled = 0 AND v_make_items = "Y"',
699: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
700: END IF;
701: IF v_wms_enabled = 0 AND v_make_items = 'Y' THEN
702: l_stmt_no := 90;
703: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
704:
705: FND_LOG.String(
706: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
707: MESSAGE => l_stmt_no ||': genealogy_tab(i).item_nbr= '|| genealogy_tab(i).item_nbr,
708: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
709:
710: FND_LOG.String(
711: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
712: MESSAGE => l_stmt_no ||': genealogy_tab(i).serial_number= '|| genealogy_tab(i).serial_number,
709:
710: FND_LOG.String(
711: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
712: MESSAGE => l_stmt_no ||': genealogy_tab(i).serial_number= '|| genealogy_tab(i).serial_number,
713: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
714:
715: FND_LOG.String(
716: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
717: MESSAGE => l_stmt_no ||': genealogy_tab(i).inventory_item_id= '|| genealogy_tab(i).inventory_item_id,
714:
715: FND_LOG.String(
716: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
717: MESSAGE => l_stmt_no ||': genealogy_tab(i).inventory_item_id= '|| genealogy_tab(i).inventory_item_id,
718: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
719: FND_LOG.String(
720: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
721: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO sgenealogy_tab',
722: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
718: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
719: FND_LOG.String(
720: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
721: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO sgenealogy_tab',
722: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
723: END IF;
724:
725: ----dbms_output.put_line(15);
726: EXECUTE IMMEDIATE g_gen_sql
735: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
736: FND_LOG.String(
737: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
738: MESSAGE => l_stmt_no ||': IF sgenealogy_tab.COUNT <> 0 THEN ',
739: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
740:
741: END IF;
742: IF sgenealogy_tab.COUNT <> 0 THEN
743: l_stmt_no := 110;
747: FOR i4 IN dbg_sgenealogy_tab.FIRST .. dbg_sgenealogy_tab.LAST LOOP
748: FND_LOG.String(
749: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
750: MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).item_nbr= '|| dbg_sgenealogy_tab(i4).item_nbr,
751: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
752:
753: FND_LOG.String(
754: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
755: MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).serial_number= '|| dbg_sgenealogy_tab(i4).serial_number,
752:
753: FND_LOG.String(
754: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
755: MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).serial_number= '|| dbg_sgenealogy_tab(i4).serial_number,
756: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
757:
758: FND_LOG.String(
759: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
760: MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).job_name= '|| dbg_sgenealogy_tab(i4).job_name,
757:
758: FND_LOG.String(
759: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
760: MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).job_name= '|| dbg_sgenealogy_tab(i4).job_name,
761: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
762:
763: END LOOP;
764: END IF;
765: END IF;
768: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
769: FND_LOG.String(
770: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
771: MESSAGE => l_stmt_no ||': FOR j IN 1..sgenealogy_tab.COUNT',
772: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
773:
774: END IF;
775: FOR j IN 1..sgenealogy_tab.COUNT
776: LOOP
782: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
783: FND_LOG.String(
784: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
785: MESSAGE => l_stmt_no ||': sgenealogy_tab(i).item_nbr= '|| sgenealogy_tab(i).item_nbr,
786: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
787:
788: FND_LOG.String(
789: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
790: MESSAGE => l_stmt_no ||': sgenealogy_tab(i).serial_number= '|| sgenealogy_tab(i).serial_number,
787:
788: FND_LOG.String(
789: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
790: MESSAGE => l_stmt_no ||': sgenealogy_tab(i).serial_number= '|| sgenealogy_tab(i).serial_number,
791: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
792: END IF;
793: v_level := 2;
794: genealogy_rec.item_nbr := sgenealogy_tab(j).item_nbr;
795: genealogy_rec.primary_uom_code := sgenealogy_tab(j).primary_uom_code;
814: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
815: FND_LOG.String(
816: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
817: MESSAGE => l_stmt_no ||': B4 IF NOT occ.EXISTS(genealogy_rec.item_nbr',
818: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
819: END IF;
820: IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
821: genealogy_rec.lot_number || '.' ||
822: genealogy_rec.serial_number || '.' ||
829: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
830: FND_LOG.String(
831: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
832: MESSAGE => l_stmt_no ||': After IF NOT occ.EXISTS(genealogy_rec.item_nbr',
833: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
834: END IF;
835: occ(genealogy_rec.item_nbr || '.' ||
836: genealogy_rec.lot_number || '.' ||
837: genealogy_rec.serial_number || '.' ||
847: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
848: FND_LOG.String(
849: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
850: MESSAGE => l_stmt_no ||': -- PIPE ROW(genealogy_rec);',
851: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
852:
853: END IF;
854: genealogy_rec.DELIVERY_DETAIL_ID := p_delivery_detail_id;
855: genealogy_rec.DELIVERY_ID := p_delivery_id;
873: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
874: FND_LOG.String(
875: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
876: MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
877: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
878:
879: FND_LOG.String(
880: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
881: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
878:
879: FND_LOG.String(
880: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
881: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
882: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
883:
884: FND_LOG.String(
885: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
886: MESSAGE => l_stmt_no ||': vr_main.lot_number= '|| vr_main.lot_number,
883:
884: FND_LOG.String(
885: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
886: MESSAGE => l_stmt_no ||': vr_main.lot_number= '|| vr_main.lot_number,
887: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
888:
889: END IF;
890: l_stmt_no := 180;
891: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
891: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
892: FND_LOG.String(
893: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
894: MESSAGE => l_stmt_no ||': LOOP',
895: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
896:
897: END IF;
898:
899: << REPEAT_LOOP>>
902: l_stmt_no := 185;
903: FND_LOG.String(
904: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
905: MESSAGE => l_stmt_no ||': OPEN c_main',
906: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
907: FND_LOG.String(
908: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
909: MESSAGE => l_stmt_no ||': genealogy_rec.item_nbr= '|| genealogy_rec.item_nbr,
910: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
906: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
907: FND_LOG.String(
908: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
909: MESSAGE => l_stmt_no ||': genealogy_rec.item_nbr= '|| genealogy_rec.item_nbr,
910: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
911:
912: FND_LOG.String(
913: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
914: MESSAGE => l_stmt_no ||': genealogy_rec.inventory_item_id= '|| genealogy_rec.inventory_item_id,
911:
912: FND_LOG.String(
913: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
914: MESSAGE => l_stmt_no ||': genealogy_rec.inventory_item_id= '|| genealogy_rec.inventory_item_id,
915: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
916:
917: FND_LOG.String(
918: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
919: MESSAGE => l_stmt_no ||': genealogy_rec.serial_number= '|| genealogy_rec.serial_number,
916:
917: FND_LOG.String(
918: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
919: MESSAGE => l_stmt_no ||': genealogy_rec.serial_number= '|| genealogy_rec.serial_number,
920: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
921:
922: END IF;
923: OPEN c_main(genealogy_rec.serial_number ,
924: genealogy_rec.inventory_item_id,
932: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
933: FND_LOG.String(
934: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
935: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
936: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
937:
938: FND_LOG.String(
939: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
940: MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
937:
938: FND_LOG.String(
939: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
940: MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
941: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
942:
943: FND_LOG.String(
944: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
945: MESSAGE => l_stmt_no ||': b4 IF c_main%NOTFOUND THEN',
942:
943: FND_LOG.String(
944: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
945: MESSAGE => l_stmt_no ||': b4 IF c_main%NOTFOUND THEN',
946: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
947:
948: END IF;
949: IF c_main%NOTFOUND THEN
950: l_stmt_no := 200;
951: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
952: FND_LOG.String(
953: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
954: MESSAGE => l_stmt_no ||': after IF c_main%NOTFOUND THEN',
955: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
956:
957: END IF;
958: ----dbms_output.put_line(21);
959: l_stmt_no := 210;
960: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
961: FND_LOG.String(
962: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
963: MESSAGE => l_stmt_no ||': b4 IF v_tab_nodes.COUNT > 0 THEN ',
964: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
965:
966: END IF;
967: IF v_tab_nodes.COUNT > 0 THEN
968: l_stmt_no := 220;
969: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
970: FND_LOG.String(
971: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
972: MESSAGE => l_stmt_no ||': after IF v_tab_nodes.COUNT > 0 THEN ',
973: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
974: dbg_v_tab_nodes := v_tab_nodes;
975: IF dbg_v_tab_nodes.COUNT > 0 THEN
976: l_stmt_no := 220.1;
977: --FOR i7 IN dbg_v_tab_nodes.FIRST .. dbg_v_tab_nodes.LAST LOOP
979: FOR i7 IN 1 .. dbg_v_tab_nodes.COUNT LOOP
980: FND_LOG.String(
981: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
982: MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).inventory_item_id= '|| dbg_v_tab_nodes(i7).inventory_item_id,
983: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
984:
985: FND_LOG.String(
986: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
987: MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).lot_number= '|| dbg_v_tab_nodes(i7).lot_number,
984:
985: FND_LOG.String(
986: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
987: MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).lot_number= '|| dbg_v_tab_nodes(i7).lot_number,
988: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
989:
990: FND_LOG.String(
991: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
992: MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).serial_number= '|| dbg_v_tab_nodes(i7).serial_number,
989:
990: FND_LOG.String(
991: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
992: MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).serial_number= '|| dbg_v_tab_nodes(i7).serial_number,
993: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
994:
995: END LOOP;
996: ********************/
997: END IF;
997: END IF;
998: FND_LOG.String(
999: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1000: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).item_nbr= '|| s2genealogy_tab(v_tab_nodes.LAST).item_nbr,
1001: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1002:
1003: FND_LOG.String(
1004: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1005: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).serial_number= '|| s2genealogy_tab(v_tab_nodes.LAST).serial_number,
1002:
1003: FND_LOG.String(
1004: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1005: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).serial_number= '|| s2genealogy_tab(v_tab_nodes.LAST).serial_number,
1006: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1007:
1008: END IF;
1009: ----dbms_output.put_line(22);
1010: ----dbms_output.put_line(s2genealogy_tab(v_tab_nodes.LAST).item_nbr);
1045: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1046: FND_LOG.String(
1047: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1048: MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
1049: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1050:
1051: END IF;
1052:
1053: occ(genealogy_rec.item_nbr || '.' ||
1064: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1065: FND_LOG.String(
1066: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1067: MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
1068: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1069:
1070: END IF;
1071: genealogy_rec.DELIVERY_DETAIL_ID := p_delivery_detail_id;
1072: genealogy_rec.DELIVERY_ID := p_delivery_id;
1086: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1087: FND_LOG.String(
1088: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1089: MESSAGE => l_stmt_no ||': CLOSE c_main; EXIT;',
1090: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1091:
1092: END IF;
1093: CLOSE c_main;
1094: EXIT;
1098: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1099: FND_LOG.String(
1100: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1101: MESSAGE => l_stmt_no ||': CLOSE c_main;',
1102: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1103: FND_LOG.String(
1104: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1105: MESSAGE => l_stmt_no ||': vr_main.serial_number = '|| vr_main.serial_number ,
1106: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1102: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1103: FND_LOG.String(
1104: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1105: MESSAGE => l_stmt_no ||': vr_main.serial_number = '|| vr_main.serial_number ,
1106: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1107:
1108: FND_LOG.String(
1109: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1110: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
1107:
1108: FND_LOG.String(
1109: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1110: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
1111: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1112:
1113: END IF;
1114: CLOSE c_main;
1115: ----dbms_output.put_line(24);
1126: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1127: FND_LOG.String(
1128: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1129: MESSAGE => l_stmt_no ||':EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s1genealogy_tab USING p_organization_id ,vr_main.serial_number , vr_main.inventory_item_id ,p_organization_id;',
1130: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1131:
1132: FND_LOG.String(
1133: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1134: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
1131:
1132: FND_LOG.String(
1133: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1134: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
1135: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1136: dbg_s1genealogy_tab := s1genealogy_tab;
1137: IF dbg_s1genealogy_tab.COUNT > 0 THEN
1138: FOR i1 IN dbg_s1genealogy_tab.FIRST .. dbg_s1genealogy_tab.LAST LOOP
1139: FND_LOG.String(
1138: FOR i1 IN dbg_s1genealogy_tab.FIRST .. dbg_s1genealogy_tab.LAST LOOP
1139: FND_LOG.String(
1140: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1141: MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).item_nbr= '|| dbg_s1genealogy_tab(i1).item_nbr,
1142: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1143:
1144: FND_LOG.String(
1145: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1146: MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).serial_number= '|| dbg_s1genealogy_tab(i1).serial_number,
1143:
1144: FND_LOG.String(
1145: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1146: MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).serial_number= '|| dbg_s1genealogy_tab(i1).serial_number,
1147: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1148: FND_LOG.String(
1149: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1150: MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).serial_number= '|| dbg_s1genealogy_tab(i1).job_name,
1151: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1147: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1148: FND_LOG.String(
1149: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1150: MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).serial_number= '|| dbg_s1genealogy_tab(i1).job_name,
1151: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1152:
1153: END LOOP;
1154: END IF;
1155: END IF;
1159: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1160: FND_LOG.String(
1161: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1162: MESSAGE => l_stmt_no ||': IF s1genealogy_tab.COUNT <> 0',
1163: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1164:
1165: END IF;
1166: ----dbms_output.put_line(27);
1167: ----dbms_output.put_line(vr_main.serial_number);
1175: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1176: FND_LOG.String(
1177: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1178: MESSAGE => l_stmt_no ||': IF vr_main.inventory_item_id <> sgenealogy_tab(j).inventory_item_id THEN',
1179: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1180: FND_LOG.String(
1181: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1182: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
1183: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1179: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1180: FND_LOG.String(
1181: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1182: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
1183: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1184:
1185: FND_LOG.String(
1186: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1187: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).inventory_item_id= '|| sgenealogy_tab(j).inventory_item_id,
1184:
1185: FND_LOG.String(
1186: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1187: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).inventory_item_id= '|| sgenealogy_tab(j).inventory_item_id,
1188: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1189:
1190: FND_LOG.String(
1191: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1192: MESSAGE => l_stmt_no ||': Y= '|| Y,
1189:
1190: FND_LOG.String(
1191: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1192: MESSAGE => l_stmt_no ||': Y= '|| Y,
1193: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1194:
1195: FND_LOG.String(
1196: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1197: MESSAGE => l_stmt_no ||': Z= '|| Z,
1194:
1195: FND_LOG.String(
1196: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1197: MESSAGE => l_stmt_no ||': Z= '|| Z,
1198: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1199:
1200: END IF;
1201: IF s1genealogy_tab.EXISTS(Z) THEN
1202: ----dbms_output.put_line(28);
1205: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1206: FND_LOG.String(
1207: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1208: MESSAGE => l_stmt_no ||': IF s1genealogy_tab.EXISTS(Z)',
1209: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1210: FND_LOG.String(
1211: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1212: MESSAGE => l_stmt_no ||': FOR l IN Z..s1genealogy_tab.COUNT',
1213: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1209: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1210: FND_LOG.String(
1211: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1212: MESSAGE => l_stmt_no ||': FOR l IN Z..s1genealogy_tab.COUNT',
1213: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1214: FND_LOG.String(
1215: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1216: MESSAGE => 's1genealogy_tab.COUNT= '|| s1genealogy_tab.COUNT,
1217: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1213: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1214: FND_LOG.String(
1215: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1216: MESSAGE => 's1genealogy_tab.COUNT= '|| s1genealogy_tab.COUNT,
1217: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1218:
1219: END IF;
1220: FOR l IN Z..s1genealogy_tab.COUNT
1221: LOOP
1223: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1224: FND_LOG.String(
1225: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1226: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).item_nbr= '|| s1genealogy_tab(l).item_nbr,
1227: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1228:
1229: FND_LOG.String(
1230: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1231: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).serial_number= '|| s1genealogy_tab(l).serial_number,
1228:
1229: FND_LOG.String(
1230: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1231: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).serial_number= '|| s1genealogy_tab(l).serial_number,
1232: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1233: FND_LOG.String(
1234: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1235: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).transaction_quantity= '|| s1genealogy_tab(l).transaction_quantity,
1236: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1232: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1233: FND_LOG.String(
1234: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1235: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).transaction_quantity= '|| s1genealogy_tab(l).transaction_quantity,
1236: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1237:
1238: END IF;
1239: ----dbms_output.put_line(29);
1240: ----dbms_output.put_line(s1genealogy_tab(l).item_nbr);
1271: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1272: FND_LOG.String(
1273: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1274: MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
1275: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1276:
1277: END IF;
1278:
1279: occ(genealogy_rec.item_nbr || '.' ||
1291: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1292: FND_LOG.String(
1293: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1294: MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
1295: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1296:
1297: END IF;
1298: genealogy_rec.DELIVERY_DETAIL_ID := p_delivery_detail_id;
1299: genealogy_rec.DELIVERY_ID := p_delivery_id;
1311: l_stmt_no := 340;
1312: FND_LOG.String(
1313: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1314: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).transaction_quantity= '|| sgenealogy_tab(j).transaction_quantity,
1315: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1316:
1317: FND_LOG.String(
1318: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1319: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
1316:
1317: FND_LOG.String(
1318: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1319: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
1320: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1321:
1322: END IF;
1323: IF sgenealogy_tab(j).transaction_quantity >= v_ctr THEN
1324: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1325: l_stmt_no := 341;
1326: FND_LOG.String(
1327: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1328: MESSAGE => l_stmt_no ||': IF sgenealogy_tab(j).transaction_quantity >= v_ctr THEN',
1329: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1330:
1331: END IF;
1332:
1333: genealogy_rec.serial_number := sgenealogy_tab(j).serial_number;
1342: l_stmt_no := 342;
1343: FND_LOG.String(
1344: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1345: MESSAGE => l_stmt_no ||': ELSIF v_ctr < sgenealogy_tab(j).transaction_quantity THEN',
1346: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1347:
1348: END IF;
1349:
1350: ----dbms_output.put_line(32);
1360: l_stmt_no := 350;
1361: FND_LOG.String(
1362: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1363: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
1364: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1365:
1366: FND_LOG.String(
1367: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1368: MESSAGE => l_stmt_no ||': v_level= '|| v_level,
1365:
1366: FND_LOG.String(
1367: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1368: MESSAGE => l_stmt_no ||': v_level= '|| v_level,
1369: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1370:
1371: END IF;
1372:
1373: ELSIF s1genealogy_tab(l).transaction_quantity = v_ctr THEN
1375: l_stmt_no := 360;
1376: FND_LOG.String(
1377: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1378: MESSAGE => l_stmt_no ||': ELSIF s1genealogy_tab(l).transaction_quantity = v_ctr THEN',
1379: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1380:
1381: FND_LOG.String(
1382: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1383: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).transaction_quantity= '|| s1genealogy_tab(l).transaction_quantity,
1380:
1381: FND_LOG.String(
1382: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1383: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).transaction_quantity= '|| s1genealogy_tab(l).transaction_quantity,
1384: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1385:
1386: FND_LOG.String(
1387: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1388: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
1385:
1386: FND_LOG.String(
1387: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1388: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
1389: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1390:
1391: END IF;
1392: ----dbms_output.put_line(35);
1393: IF s1genealogy_tab.EXISTS(l + 1) THEN
1395: l_stmt_no := 370;
1396: FND_LOG.String(
1397: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1398: MESSAGE => l_stmt_no ||': IF s1genealogy_tab.EXISTS(l+1)',
1399: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1400: FND_LOG.String(
1401: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1402: MESSAGE => l_stmt_no ||': FOR m IN l+1..s1genealogy_tab.COUNT',
1403: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1399: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1400: FND_LOG.String(
1401: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1402: MESSAGE => l_stmt_no ||': FOR m IN l+1..s1genealogy_tab.COUNT',
1403: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1404:
1405: END IF;
1406:
1407: ----dbms_output.put_line(36);
1417: l_stmt_no := 380;
1418: FND_LOG.String(
1419: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1420: MESSAGE => l_stmt_no ||': s1genealogy_tab(m).item_nbr= '|| s1genealogy_tab(m).item_nbr,
1421: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1422:
1423: FND_LOG.String(
1424: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1425: MESSAGE => l_stmt_no ||': s1genealogy_tab(m).serial_number= '|| s1genealogy_tab(m).serial_number,
1422:
1423: FND_LOG.String(
1424: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1425: MESSAGE => l_stmt_no ||': s1genealogy_tab(m).serial_number= '|| s1genealogy_tab(m).serial_number,
1426: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1427:
1428: FND_LOG.String(
1429: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1430: MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
1427:
1428: FND_LOG.String(
1429: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1430: MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
1431: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1432:
1433: FND_LOG.String(
1434: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1435: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
1432:
1433: FND_LOG.String(
1434: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1435: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
1436: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1437:
1438: END IF;
1439:
1440: v_tab_nodes(m).serial_number := s1genealogy_tab(m).serial_number;
1465: l_stmt_no := 390;
1466: FND_LOG.String(
1467: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1468: MESSAGE => l_stmt_no ||': OPEN c_main(v_tab_nodes(m).serial_number',
1469: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1470: FND_LOG.String(
1471: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1472: MESSAGE => l_stmt_no ||': v_tab_nodes(m).serial_number= '|| v_tab_nodes(m).serial_number,
1473: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1469: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1470: FND_LOG.String(
1471: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1472: MESSAGE => l_stmt_no ||': v_tab_nodes(m).serial_number= '|| v_tab_nodes(m).serial_number,
1473: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1474:
1475: FND_LOG.String(
1476: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1477: MESSAGE => l_stmt_no ||': v_tab_nodes(m).inventory_item_id= '|| v_tab_nodes(m).inventory_item_id,
1474:
1475: FND_LOG.String(
1476: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1477: MESSAGE => l_stmt_no ||': v_tab_nodes(m).inventory_item_id= '|| v_tab_nodes(m).inventory_item_id,
1478: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1479:
1480: END IF;
1481:
1482: OPEN c_main(v_tab_nodes(m).serial_number ,
1489: l_stmt_no := 400;
1490: FND_LOG.String(
1491: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1492: MESSAGE => l_stmt_no ||': IF c_main%NOTFOUND',
1493: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1494:
1495: END IF;
1496:
1497: ----dbms_output.put_line(61);
1500: l_stmt_no := 410;
1501: FND_LOG.String(
1502: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1503: MESSAGE => l_stmt_no ||': v_tab_nodes.COUNT > 0',
1504: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1505: FND_LOG.String(
1506: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1507: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).item_nbr= '|| s2genealogy_tab(v_tab_nodes.LAST).item_nbr,
1508: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1504: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1505: FND_LOG.String(
1506: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1507: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).item_nbr= '|| s2genealogy_tab(v_tab_nodes.LAST).item_nbr,
1508: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1509:
1510: FND_LOG.String(
1511: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1512: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).serial_number= '|| s2genealogy_tab(v_tab_nodes.LAST).serial_number,
1509:
1510: FND_LOG.String(
1511: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1512: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).serial_number= '|| s2genealogy_tab(v_tab_nodes.LAST).serial_number,
1513: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1514:
1515:
1516: END IF;
1517: ----dbms_output.put_line(62);
1550: l_stmt_no := 420;
1551: FND_LOG.String(
1552: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1553: MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
1554: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1555: FND_LOG.String(
1556: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1557: MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
1558: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1554: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1555: FND_LOG.String(
1556: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1557: MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
1558: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1559: END IF;
1560: occ(genealogy_rec.item_nbr || '.' ||
1561: genealogy_rec.lot_number || '.' ||
1562: genealogy_rec.serial_number || '.' ||
1588: l_stmt_no := 430;
1589: FND_LOG.String(
1590: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1591: MESSAGE => l_stmt_no ||': genealogy_rec.item_nbr= '|| genealogy_rec.item_nbr,
1592: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1593:
1594: FND_LOG.String(
1595: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1596: MESSAGE => l_stmt_no ||': genealogy_rec.serial_number= '|| genealogy_rec.serial_number,
1593:
1594: FND_LOG.String(
1595: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1596: MESSAGE => l_stmt_no ||': genealogy_rec.serial_number= '|| genealogy_rec.serial_number,
1597: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1598:
1599: FND_LOG.String(
1600: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1601: MESSAGE => l_stmt_no ||': genealogy_rec.inventory_item_id= '|| genealogy_rec.inventory_item_id,
1598:
1599: FND_LOG.String(
1600: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1601: MESSAGE => l_stmt_no ||': genealogy_rec.inventory_item_id= '|| genealogy_rec.inventory_item_id,
1602: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1603:
1604: END IF;
1605: ELSE--IF v_tab_nodes.COUNT > 0 THEN
1606: ----dbms_output.put_line(64);
1609: l_stmt_no := 440;
1610: FND_LOG.String(
1611: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1612: MESSAGE => l_stmt_no ||': CLOSE c_main;',
1613: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1614: FND_LOG.String(
1615: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1616: MESSAGE => l_stmt_no ||': END IF;--IF v_tab_nodes.COUNT > 0 THEN ',
1617: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1613: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1614: FND_LOG.String(
1615: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1616: MESSAGE => l_stmt_no ||': END IF;--IF v_tab_nodes.COUNT > 0 THEN ',
1617: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1618:
1619: END IF;
1620: END IF;--IF v_tab_nodes.COUNT > 0 THEN
1621: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1621: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1622: FND_LOG.String(
1623: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1624: MESSAGE => l_stmt_no ||': END IF;--IF c_main%NOTFOUND THEN ',
1625: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1626:
1627: END IF;
1628:
1629: END IF;--IF c_main%NOTFOUND THEN
1633: l_stmt_no := 451;
1634: FND_LOG.String(
1635: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1636: MESSAGE => l_stmt_no ||': CLOSE c_main;',
1637: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1638:
1639: FND_LOG.String(
1640: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1641: MESSAGE => l_stmt_no ||': END LOOP;--FOR m IN l + 1..s1genealogy_tab.COUNT ',
1638:
1639: FND_LOG.String(
1640: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1641: MESSAGE => l_stmt_no ||': END LOOP;--FOR m IN l + 1..s1genealogy_tab.COUNT ',
1642: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1643:
1644: END IF;
1645:
1646: END LOOP;--FOR m IN l + 1..s1genealogy_tab.COUNT
1648: l_stmt_no := 452;
1649: FND_LOG.String(
1650: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1651: MESSAGE => l_stmt_no ||': END IF;--IF s1genealogy_tab.EXISTS(l + 1) THEN',
1652: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1653:
1654: END IF;
1655:
1656: END IF;--IF s1genealogy_tab.EXISTS(l + 1) THEN
1660: l_stmt_no := 460;
1661: FND_LOG.String(
1662: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1663: MESSAGE => l_stmt_no ||': v_level= '|| v_level,
1664: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1665: FND_LOG.String(
1666: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1667: MESSAGE => l_stmt_no ||': EXIT;',
1668: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1664: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1665: FND_LOG.String(
1666: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1667: MESSAGE => l_stmt_no ||': EXIT;',
1668: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1669: END IF;
1670: EXIT;
1671: ----dbms_output.put_line(39);
1672: ELSIF s1genealogy_tab(l).transaction_quantity > v_ctr THEN
1674: l_stmt_no := 470;
1675: FND_LOG.String(
1676: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1677: MESSAGE => l_stmt_no ||': ELSIF s1genealogy_tab(l).transaction_quantity > v_ctr',
1678: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1679:
1680: END IF;
1681:
1682: ----dbms_output.put_line(80);
1684: l_stmt_no := 480;
1685: FND_LOG.String(
1686: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1687: MESSAGE => l_stmt_no ||': OPEN c_main(s1genealogy_tab(l).serial_number',
1688: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1689:
1690: END IF;
1691:
1692: OPEN c_main(s1genealogy_tab(l).serial_number ,
1698: l_stmt_no := 490;
1699: FND_LOG.String(
1700: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1701: MESSAGE => l_stmt_no ||': IF c_main%FOUND',
1702: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1703:
1704:
1705:
1706: END IF;
1715: l_stmt_no := 500;
1716: FND_LOG.String(
1717: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1718: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).serial_number= '|| s1genealogy_tab(l).serial_number,
1719: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1720:
1721: FND_LOG.String(
1722: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1723: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).inventory_item_id= '|| s1genealogy_tab(l).inventory_item_id,
1720:
1721: FND_LOG.String(
1722: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1723: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).inventory_item_id= '|| s1genealogy_tab(l).inventory_item_id,
1724: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1725:
1726: FND_LOG.String(
1727: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1728: MESSAGE => l_stmt_no ||': v_level= '|| v_level,
1725:
1726: FND_LOG.String(
1727: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1728: MESSAGE => l_stmt_no ||': v_level= '|| v_level,
1729: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1730:
1731: FND_LOG.String(
1732: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1733: MESSAGE => l_stmt_no ||': CLOSE c_main',
1730:
1731: FND_LOG.String(
1732: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1733: MESSAGE => l_stmt_no ||': CLOSE c_main',
1734: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1735:
1736: FND_LOG.String(
1737: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1738: MESSAGE => l_stmt_no ||': EXIT',
1735:
1736: FND_LOG.String(
1737: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1738: MESSAGE => l_stmt_no ||': EXIT',
1739: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1740:
1741: END IF;
1742:
1743: EXIT;
1747: l_stmt_no := 510;
1748: FND_LOG.String(
1749: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1750: MESSAGE => l_stmt_no ||': CLOSE c_main',
1751: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1752: FND_LOG.String(
1753: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1754: MESSAGE => l_stmt_no ||': END IF;--IF c_main%FOUND THEN ',
1755: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1751: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1752: FND_LOG.String(
1753: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1754: MESSAGE => l_stmt_no ||': END IF;--IF c_main%FOUND THEN ',
1755: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1756:
1757:
1758: END IF;
1759:
1767: l_stmt_no := 511;
1768: FND_LOG.String(
1769: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1770: MESSAGE => l_stmt_no ||': END LOOP;--FOR l IN Z..s1genealogy_tab.COUNT',
1771: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1772:
1773: FND_LOG.String(
1774: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1775: MESSAGE => l_stmt_no ||': END IF;--IF s1genealogy_tab.EXISTS(Z) THEN',
1772:
1773: FND_LOG.String(
1774: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1775: MESSAGE => l_stmt_no ||': END IF;--IF s1genealogy_tab.EXISTS(Z) THEN',
1776: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1777:
1778: END IF;
1779:
1780: END IF;--IF s1genealogy_tab.EXISTS(Z) THEN
1784: l_stmt_no := 512;
1785: FND_LOG.String(
1786: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1787: MESSAGE => l_stmt_no ||': ELSE--IF s1genealogy_tab.COUNT <> 0 THEN',
1788: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1789: FND_LOG.String(
1790: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1791: MESSAGE => l_stmt_no ||': EXIT; ',
1792: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1788: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1789: FND_LOG.String(
1790: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1791: MESSAGE => l_stmt_no ||': EXIT; ',
1792: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1793:
1794: END IF;
1795:
1796: ----dbms_output.put_line(43);
1802: l_stmt_no := 513;
1803: FND_LOG.String(
1804: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1805: MESSAGE => l_stmt_no ||': END LOOP;--c_main',
1806: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1807:
1808: END IF;
1809: ----dbms_output.put_line(45);
1810: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1811: l_stmt_no := 520;
1812: FND_LOG.String(
1813: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1814: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1815: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1816:
1817: FND_LOG.String(
1818: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1819: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).serial_number = '|| sgenealogy_tab(j).serial_number ,
1816:
1817: FND_LOG.String(
1818: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1819: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).serial_number = '|| sgenealogy_tab(j).serial_number ,
1820: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1821:
1822: FND_LOG.String(
1823: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1824: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).inventory_item_id= '|| sgenealogy_tab(j).inventory_item_id,
1821:
1822: FND_LOG.String(
1823: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1824: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).inventory_item_id= '|| sgenealogy_tab(j).inventory_item_id,
1825: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1826:
1827: END IF;
1828:
1829: EXECUTE IMMEDIATE g_gen_sql
1837: l_stmt_no := 521;
1838: FND_LOG.String(
1839: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1840: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1841: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1842: END IF;
1843:
1844: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1845: l_stmt_no := 530;
1848: l_stmt_no := 522;
1849: FND_LOG.String(
1850: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1851: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1852: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1853: END IF;
1854: IF dbg_s3genealogy_tab.COUNT > 0 THEN
1855: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1856: l_stmt_no := 523;
1856: l_stmt_no := 523;
1857: FND_LOG.String(
1858: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1859: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1860: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1861: END IF;
1862: FOR i6 IN dbg_s3genealogy_tab.FIRST .. dbg_s3genealogy_tab.LAST LOOP
1863: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1864: l_stmt_no := 524;
1864: l_stmt_no := 524;
1865: FND_LOG.String(
1866: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1867: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1868: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1869: END IF;
1870: FND_LOG.String(
1871: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1872: MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).item_nbr= '|| dbg_s3genealogy_tab(i6).item_nbr,
1869: END IF;
1870: FND_LOG.String(
1871: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1872: MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).item_nbr= '|| dbg_s3genealogy_tab(i6).item_nbr,
1873: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1874: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1875: l_stmt_no := 525;
1876: FND_LOG.String(
1877: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1875: l_stmt_no := 525;
1876: FND_LOG.String(
1877: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1878: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1879: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1880: END IF;
1881: FND_LOG.String(
1882: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1883: MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).serial_number= '|| dbg_s3genealogy_tab(i6).serial_number,
1880: END IF;
1881: FND_LOG.String(
1882: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1883: MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).serial_number= '|| dbg_s3genealogy_tab(i6).serial_number,
1884: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1885: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1886: l_stmt_no := 526;
1887: FND_LOG.String(
1888: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1886: l_stmt_no := 526;
1887: FND_LOG.String(
1888: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1889: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1890: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1891: END IF;
1892: FND_LOG.String(
1893: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1894: MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).job_name= '|| dbg_s3genealogy_tab(i6).job_name,
1891: END IF;
1892: FND_LOG.String(
1893: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1894: MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).job_name= '|| dbg_s3genealogy_tab(i6).job_name,
1895: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1896: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1897: l_stmt_no := 527;
1898: FND_LOG.String(
1899: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1897: l_stmt_no := 527;
1898: FND_LOG.String(
1899: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1900: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1901: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1902: END IF;
1903:
1904: END LOOP;
1905: END IF;
1908: l_stmt_no := 528;
1909: FND_LOG.String(
1910: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1911: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1912: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1913: END IF;
1914: IF s3genealogy_tab.COUNT <> 0 THEN
1915: ----dbms_output.put_line(90);
1916: Z := Y + 1;
1919: l_stmt_no := 540;
1920: FND_LOG.String(
1921: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1922: MESSAGE => l_stmt_no ||': Y= '|| Y,
1923: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1924:
1925: FND_LOG.String(
1926: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1927: MESSAGE => l_stmt_no ||': Z= '|| Z,
1924:
1925: FND_LOG.String(
1926: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1927: MESSAGE => l_stmt_no ||': Z= '|| Z,
1928: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1929:
1930: END IF;
1931:
1932: IF s3genealogy_tab.EXISTS(Z) THEN
1934: l_stmt_no := 550;
1935: FND_LOG.String(
1936: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1937: MESSAGE => l_stmt_no ||': IF s3genealogy_tab.EXISTS(Z)',
1938: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1939: FND_LOG.String(
1940: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1941: MESSAGE => l_stmt_no ||': FOR l IN Z..s3genealogy_tab.COUNT',
1942: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1938: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1939: FND_LOG.String(
1940: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1941: MESSAGE => l_stmt_no ||': FOR l IN Z..s3genealogy_tab.COUNT',
1942: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1943:
1944: END IF;
1945:
1946: FOR l IN Z..s3genealogy_tab.COUNT
1951: l_stmt_no := 560;
1952: FND_LOG.String(
1953: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1954: MESSAGE => l_stmt_no ||': s3genealogy_tab(l).item_nbr= '|| s3genealogy_tab(l).item_nbr,
1955: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1956:
1957: FND_LOG.String(
1958: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1959: MESSAGE => l_stmt_no ||': s3genealogy_tab(l).serial_number= '|| s3genealogy_tab(l).serial_number,
1956:
1957: FND_LOG.String(
1958: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1959: MESSAGE => l_stmt_no ||': s3genealogy_tab(l).serial_number= '|| s3genealogy_tab(l).serial_number,
1960: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1961:
1962: END IF;
1963:
1964: genealogy_rec.item_nbr := s3genealogy_tab(l).item_nbr;
1992: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1993: FND_LOG.String(
1994: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1995: MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
1996: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1997:
1998: END IF;
1999: occ(genealogy_rec.item_nbr || '.' ||
2000: genealogy_rec.lot_number || '.' ||
2010: l_stmt_no := 570;
2011: FND_LOG.String(
2012: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2013: MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
2014: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2015: FND_LOG.String(
2016: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2017: MESSAGE => l_stmt_no ||': GOTO REPEAT_LOOP',
2018: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2014: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2015: FND_LOG.String(
2016: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2017: MESSAGE => l_stmt_no ||': GOTO REPEAT_LOOP',
2018: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2019: END IF;
2020: genealogy_rec.DELIVERY_DETAIL_ID := p_delivery_detail_id;
2021: genealogy_rec.DELIVERY_ID := p_delivery_id;
2022: genealogy_rec.GENEALOGY_OBJECT_ID := p_gen_object_id;
2031: l_stmt_no := 571;
2032: FND_LOG.String(
2033: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2034: MESSAGE => l_stmt_no ||': -- PIPE ROW(genealogy_rec); ',
2035: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2036: FND_LOG.String(
2037: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2038: MESSAGE => l_stmt_no ||': GOTO REPEAT_LOOP;',
2039: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2035: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2036: FND_LOG.String(
2037: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2038: MESSAGE => l_stmt_no ||': GOTO REPEAT_LOOP;',
2039: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2040:
2041: FND_LOG.String(
2042: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2043: MESSAGE => l_stmt_no ||': END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr || "." ||',
2040:
2041: FND_LOG.String(
2042: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2043: MESSAGE => l_stmt_no ||': END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr || "." ||',
2044: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2045:
2046: END IF;
2047: END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
2048: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2049: l_stmt_no := 572;
2050: FND_LOG.String(
2051: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2052: MESSAGE => l_stmt_no ||': END LOOP;--FOR l IN Z..s3genealogy_tab.COUNT',
2053: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2054:
2055: END IF;
2056:
2057: END LOOP;--FOR l IN Z..s3genealogy_tab.COUNT
2059: l_stmt_no := 573;
2060: FND_LOG.String(
2061: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2062: MESSAGE => l_stmt_no ||': END IF;--s3genealogy_tab.EXISTS(Z) ',
2063: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2064:
2065: END IF;
2066:
2067: END IF;--s3genealogy_tab.EXISTS(Z)
2069: l_stmt_no := 574;
2070: FND_LOG.String(
2071: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2072: MESSAGE => l_stmt_no ||': END IF;--s3genealogy_tab.COUNT <> 0 ',
2073: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2074:
2075: END IF;
2076:
2077: END IF;--s3genealogy_tab.COUNT <> 0
2079: l_stmt_no := 575;
2080: FND_LOG.String(
2081: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2082: MESSAGE => l_stmt_no ||': END LOOP;--FOR j IN 1..sgenealogy_tab.COUNT',
2083: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2084:
2085: END IF;
2086:
2087: END LOOP;--FOR j IN 1..sgenealogy_tab.COUNT
2090: l_stmt_no := 576;
2091: FND_LOG.String(
2092: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2093: MESSAGE => l_stmt_no ||': END IF;--sgenealogy_tab.COUNT <> 0',
2094: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2095:
2096: END IF;
2097:
2098: END IF;--sgenealogy_tab.COUNT <> 0
2101: l_stmt_no := 577;
2102: FND_LOG.String(
2103: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2104: MESSAGE => l_stmt_no ||': END IF;--v_wms_enabled = 0 AND v_make_items = "Y"',
2105: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2106:
2107: END IF;
2108:
2109: END IF;--v_wms_enabled = 0 AND v_make_items = 'Y'
2112: l_stmt_no := 578;
2113: FND_LOG.String(
2114: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2115: MESSAGE => l_stmt_no ||': END LOOP;--I IN 1..genealogy_tab.COUNT',
2116: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2117:
2118: END IF;
2119: END LOOP;--I IN 1..genealogy_tab.COUNT
2120:
2340: l_stmt_no := 590;
2341: FND_LOG.String(
2342: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2343: MESSAGE => l_stmt_no ||': Exiting get_genealogy',
2344: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2345: END IF;
2346: RETURN;
2347: END;
2348:
2760: l_stmt_no := 0;
2761: FND_LOG.String(
2762: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2763: MESSAGE => l_stmt_no ||': Entering get_genealogy',
2764: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2765: FND_LOG.String(
2766: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2767: MESSAGE => ' p_gen_object_id= '|| p_gen_object_id,
2768: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2764: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2765: FND_LOG.String(
2766: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2767: MESSAGE => ' p_gen_object_id= '|| p_gen_object_id,
2768: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2769:
2770: FND_LOG.String(
2771: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2772: MESSAGE => ' p_delivery_detail_id= '|| p_delivery_detail_id,
2769:
2770: FND_LOG.String(
2771: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2772: MESSAGE => ' p_delivery_detail_id= '|| p_delivery_detail_id,
2773: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2774:
2775: FND_LOG.String(
2776: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2777: MESSAGE => ' p_delivery_id= '|| p_delivery_id,
2774:
2775: FND_LOG.String(
2776: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2777: MESSAGE => ' p_delivery_id= '|| p_delivery_id,
2778: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2779:
2780: FND_LOG.String(
2781: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2782: MESSAGE => ' p_organization_id= '|| p_organization_id,
2779:
2780: FND_LOG.String(
2781: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2782: MESSAGE => ' p_organization_id= '|| p_organization_id,
2783: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2784:
2785: FND_LOG.String(
2786: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2787: MESSAGE => ' p_frm_serial= '|| p_frm_serial,
2784:
2785: FND_LOG.String(
2786: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2787: MESSAGE => ' p_frm_serial= '|| p_frm_serial,
2788: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2789:
2790: FND_LOG.String(
2791: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2792: MESSAGE => ' p_to_serial= '|| p_to_serial,
2789:
2790: FND_LOG.String(
2791: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2792: MESSAGE => ' p_to_serial= '|| p_to_serial,
2793: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2794:
2795: END IF;
2796:
2797: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2853: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2854: FND_LOG.String(
2855: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2856: MESSAGE => l_stmt_no ||': OPEN c_main_sql(p_delivery_detail_id, p_organization_id)= ',
2857: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2858: FND_LOG.String(
2859: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2860: MESSAGE => l_stmt_no ||': p_delivery_detail_id= '|| p_delivery_detail_id,
2861: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2857: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2858: FND_LOG.String(
2859: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2860: MESSAGE => l_stmt_no ||': p_delivery_detail_id= '|| p_delivery_detail_id,
2861: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2862:
2863: FND_LOG.String(
2864: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2865: MESSAGE => l_stmt_no ||': p_organization_id= '|| p_organization_id,
2862:
2863: FND_LOG.String(
2864: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2865: MESSAGE => l_stmt_no ||': p_organization_id= '|| p_organization_id,
2866: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2867:
2868: END IF;
2869: ----dbms_output.put_line(p_delivery_detail_id);
2870: OPEN c_main_sql(p_delivery_detail_id, p_organization_id);
2880: FOR i3 IN dbg_genealogy_tab.FIRST .. dbg_genealogy_tab.LAST LOOP
2881: FND_LOG.String(
2882: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2883: MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).item_nbr= '|| dbg_genealogy_tab(i3).item_nbr,
2884: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2885:
2886: FND_LOG.String(
2887: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2888: MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).serial_number= '|| dbg_genealogy_tab(i3).serial_number,
2885:
2886: FND_LOG.String(
2887: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2888: MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).serial_number= '|| dbg_genealogy_tab(i3).serial_number,
2889: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2890:
2891: FND_LOG.String(
2892: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2893: MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).job_name= '|| dbg_genealogy_tab(i3).job_name,
2890:
2891: FND_LOG.String(
2892: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2893: MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).job_name= '|| dbg_genealogy_tab(i3).job_name,
2894: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2895: END LOOP;
2896: END IF;
2897: END IF;
2898:
2905: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2906: FND_LOG.String(
2907: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2908: MESSAGE => l_stmt_no ||': IF genealogy_tab.COUNT = 0 THEN',
2909: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2910: FND_LOG.String(
2911: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2912: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_sub_sql BULK COLLECT INTO genealogy_tab USING p_gen_object_id;',
2913: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2909: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2910: FND_LOG.String(
2911: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2912: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_sub_sql BULK COLLECT INTO genealogy_tab USING p_gen_object_id;',
2913: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2914:
2915: END IF;
2916: ----dbms_output.put_line(12);
2917: EXECUTE IMMEDIATE g_sub_sql
2924: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2925: FND_LOG.String(
2926: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2927: MESSAGE => l_stmt_no ||': FOR I IN 1..genealogy_tab.COUNT',
2928: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2929:
2930: END IF;
2931:
2932: FOR I IN 1..genealogy_tab.COUNT
2939: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2940: FND_LOG.String(
2941: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2942: MESSAGE => l_stmt_no ||': genealogy_tab(i).item_nbr= '|| genealogy_tab(i).item_nbr,
2943: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2944:
2945: FND_LOG.String(
2946: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2947: MESSAGE => l_stmt_no ||': genealogy_tab(i).serial_number= '|| genealogy_tab(i).serial_number,
2944:
2945: FND_LOG.String(
2946: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2947: MESSAGE => l_stmt_no ||': genealogy_tab(i).serial_number= '|| genealogy_tab(i).serial_number,
2948: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2949: END IF;
2950:
2951: v_level := 1;
2952: genealogy_rec.item_nbr := genealogy_tab(i).item_nbr;
2973: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2974: FND_LOG.String(
2975: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2976: MESSAGE => l_stmt_no ||': B4 IF NOT occ.EXISTS(genealogy_rec.item_nbr',
2977: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2978: END IF;
2979: IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
2980: genealogy_rec.lot_number || '.' ||
2981: genealogy_rec.serial_number || '.' ||
2988: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2989: FND_LOG.String(
2990: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2991: MESSAGE => l_stmt_no ||': After IF NOT occ.EXISTS(genealogy_rec.item_nbr',
2992: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2993: END IF;
2994: occ(genealogy_rec.item_nbr || '.' ||
2995: genealogy_rec.lot_number || '.' ||
2996: genealogy_rec.serial_number || '.' ||
3008: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3009: FND_LOG.String(
3010: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3011: MESSAGE => l_stmt_no ||': IF v_wms_enabled <> 1 THEN',
3012: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3013: END IF;
3014: PIPE ROW(genealogy_rec);
3015: END IF;--v_wms_enabled <> 1
3016: END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr
3018: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3019: FND_LOG.String(
3020: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3021: MESSAGE => l_stmt_no ||': IF v_wms_enabled = 0 AND v_make_items = "Y"',
3022: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3023: END IF;
3024: IF v_wms_enabled = 0 AND v_make_items = 'Y' THEN
3025: l_stmt_no := 90;
3026: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3027:
3028: FND_LOG.String(
3029: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3030: MESSAGE => l_stmt_no ||': genealogy_tab(i).item_nbr= '|| genealogy_tab(i).item_nbr,
3031: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3032:
3033: FND_LOG.String(
3034: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3035: MESSAGE => l_stmt_no ||': genealogy_tab(i).serial_number= '|| genealogy_tab(i).serial_number,
3032:
3033: FND_LOG.String(
3034: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3035: MESSAGE => l_stmt_no ||': genealogy_tab(i).serial_number= '|| genealogy_tab(i).serial_number,
3036: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3037:
3038: FND_LOG.String(
3039: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3040: MESSAGE => l_stmt_no ||': genealogy_tab(i).inventory_item_id= '|| genealogy_tab(i).inventory_item_id,
3037:
3038: FND_LOG.String(
3039: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3040: MESSAGE => l_stmt_no ||': genealogy_tab(i).inventory_item_id= '|| genealogy_tab(i).inventory_item_id,
3041: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3042: FND_LOG.String(
3043: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3044: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO sgenealogy_tab',
3045: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3041: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3042: FND_LOG.String(
3043: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3044: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO sgenealogy_tab',
3045: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3046: END IF;
3047:
3048: ----dbms_output.put_line(15);
3049: EXECUTE IMMEDIATE g_gen_sql
3058: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3059: FND_LOG.String(
3060: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3061: MESSAGE => l_stmt_no ||': IF sgenealogy_tab.COUNT <> 0 THEN ',
3062: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3063:
3064: END IF;
3065: IF sgenealogy_tab.COUNT <> 0 THEN
3066: l_stmt_no := 110;
3070: FOR i4 IN dbg_sgenealogy_tab.FIRST .. dbg_sgenealogy_tab.LAST LOOP
3071: FND_LOG.String(
3072: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3073: MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).item_nbr= '|| dbg_sgenealogy_tab(i4).item_nbr,
3074: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3075:
3076: FND_LOG.String(
3077: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3078: MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).serial_number= '|| dbg_sgenealogy_tab(i4).serial_number,
3075:
3076: FND_LOG.String(
3077: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3078: MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).serial_number= '|| dbg_sgenealogy_tab(i4).serial_number,
3079: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3080:
3081: FND_LOG.String(
3082: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3083: MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).job_name= '|| dbg_sgenealogy_tab(i4).job_name,
3080:
3081: FND_LOG.String(
3082: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3083: MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).job_name= '|| dbg_sgenealogy_tab(i4).job_name,
3084: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3085:
3086: END LOOP;
3087: END IF;
3088: END IF;
3091: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3092: FND_LOG.String(
3093: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3094: MESSAGE => l_stmt_no ||': FOR j IN 1..sgenealogy_tab.COUNT',
3095: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3096:
3097: END IF;
3098: FOR j IN 1..sgenealogy_tab.COUNT
3099: LOOP
3105: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3106: FND_LOG.String(
3107: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3108: MESSAGE => l_stmt_no ||': sgenealogy_tab(i).item_nbr= '|| sgenealogy_tab(i).item_nbr,
3109: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3110:
3111: FND_LOG.String(
3112: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3113: MESSAGE => l_stmt_no ||': sgenealogy_tab(i).serial_number= '|| sgenealogy_tab(i).serial_number,
3110:
3111: FND_LOG.String(
3112: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3113: MESSAGE => l_stmt_no ||': sgenealogy_tab(i).serial_number= '|| sgenealogy_tab(i).serial_number,
3114: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3115: END IF;
3116: v_level := 2;
3117: genealogy_rec.item_nbr := sgenealogy_tab(j).item_nbr;
3118: genealogy_rec.primary_uom_code := sgenealogy_tab(j).primary_uom_code;
3137: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3138: FND_LOG.String(
3139: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3140: MESSAGE => l_stmt_no ||': B4 IF NOT occ.EXISTS(genealogy_rec.item_nbr',
3141: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3142: END IF;
3143: IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
3144: genealogy_rec.lot_number || '.' ||
3145: genealogy_rec.serial_number || '.' ||
3152: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3153: FND_LOG.String(
3154: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3155: MESSAGE => l_stmt_no ||': After IF NOT occ.EXISTS(genealogy_rec.item_nbr',
3156: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3157: END IF;
3158: occ(genealogy_rec.item_nbr || '.' ||
3159: genealogy_rec.lot_number || '.' ||
3160: genealogy_rec.serial_number || '.' ||
3170: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3171: FND_LOG.String(
3172: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3173: MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec);',
3174: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3175:
3176: END IF;
3177: PIPE ROW(genealogy_rec);
3178: v_level := v_level + 1;
3188: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3189: FND_LOG.String(
3190: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3191: MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
3192: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3193:
3194: FND_LOG.String(
3195: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3196: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3193:
3194: FND_LOG.String(
3195: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3196: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3197: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3198:
3199: FND_LOG.String(
3200: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3201: MESSAGE => l_stmt_no ||': vr_main.lot_number= '|| vr_main.lot_number,
3198:
3199: FND_LOG.String(
3200: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3201: MESSAGE => l_stmt_no ||': vr_main.lot_number= '|| vr_main.lot_number,
3202: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3203:
3204: END IF;
3205: l_stmt_no := 180;
3206: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3206: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3207: FND_LOG.String(
3208: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3209: MESSAGE => l_stmt_no ||': LOOP',
3210: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3211:
3212: END IF;
3213:
3214: << REPEAT_LOOP>>
3217: l_stmt_no := 185;
3218: FND_LOG.String(
3219: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3220: MESSAGE => l_stmt_no ||': OPEN c_main',
3221: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3222: FND_LOG.String(
3223: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3224: MESSAGE => l_stmt_no ||': genealogy_rec.item_nbr= '|| genealogy_rec.item_nbr,
3225: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3221: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3222: FND_LOG.String(
3223: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3224: MESSAGE => l_stmt_no ||': genealogy_rec.item_nbr= '|| genealogy_rec.item_nbr,
3225: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3226:
3227: FND_LOG.String(
3228: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3229: MESSAGE => l_stmt_no ||': genealogy_rec.inventory_item_id= '|| genealogy_rec.inventory_item_id,
3226:
3227: FND_LOG.String(
3228: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3229: MESSAGE => l_stmt_no ||': genealogy_rec.inventory_item_id= '|| genealogy_rec.inventory_item_id,
3230: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3231:
3232: FND_LOG.String(
3233: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3234: MESSAGE => l_stmt_no ||': genealogy_rec.serial_number= '|| genealogy_rec.serial_number,
3231:
3232: FND_LOG.String(
3233: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3234: MESSAGE => l_stmt_no ||': genealogy_rec.serial_number= '|| genealogy_rec.serial_number,
3235: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3236:
3237: END IF;
3238: OPEN c_main(genealogy_rec.serial_number ,
3239: genealogy_rec.inventory_item_id,
3247: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3248: FND_LOG.String(
3249: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3250: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3251: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3252:
3253: FND_LOG.String(
3254: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3255: MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
3252:
3253: FND_LOG.String(
3254: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3255: MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
3256: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3257:
3258: FND_LOG.String(
3259: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3260: MESSAGE => l_stmt_no ||': b4 IF c_main%NOTFOUND THEN',
3257:
3258: FND_LOG.String(
3259: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3260: MESSAGE => l_stmt_no ||': b4 IF c_main%NOTFOUND THEN',
3261: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3262:
3263: END IF;
3264: IF c_main%NOTFOUND THEN
3265: l_stmt_no := 200;
3266: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3267: FND_LOG.String(
3268: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3269: MESSAGE => l_stmt_no ||': after IF c_main%NOTFOUND THEN',
3270: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3271:
3272: END IF;
3273: ----dbms_output.put_line(21);
3274: l_stmt_no := 210;
3275: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3276: FND_LOG.String(
3277: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3278: MESSAGE => l_stmt_no ||': b4 IF v_tab_nodes.COUNT > 0 THEN ',
3279: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3280:
3281: END IF;
3282: IF v_tab_nodes.COUNT > 0 THEN
3283: l_stmt_no := 220;
3284: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3285: FND_LOG.String(
3286: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3287: MESSAGE => l_stmt_no ||': after IF v_tab_nodes.COUNT > 0 THEN ',
3288: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3289: dbg_v_tab_nodes := v_tab_nodes;
3290: IF dbg_v_tab_nodes.COUNT > 0 THEN
3291: FOR i7 IN dbg_v_tab_nodes.FIRST .. dbg_v_tab_nodes.LAST LOOP
3292: FND_LOG.String(
3291: FOR i7 IN dbg_v_tab_nodes.FIRST .. dbg_v_tab_nodes.LAST LOOP
3292: FND_LOG.String(
3293: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3294: MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).inventory_item_id= '|| dbg_v_tab_nodes(i7).inventory_item_id,
3295: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3296:
3297: FND_LOG.String(
3298: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3299: MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).lot_number= '|| dbg_v_tab_nodes(i7).lot_number,
3296:
3297: FND_LOG.String(
3298: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3299: MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).lot_number= '|| dbg_v_tab_nodes(i7).lot_number,
3300: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3301:
3302: FND_LOG.String(
3303: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3304: MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).serial_number= '|| dbg_v_tab_nodes(i7).serial_number,
3301:
3302: FND_LOG.String(
3303: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3304: MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).serial_number= '|| dbg_v_tab_nodes(i7).serial_number,
3305: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3306:
3307: END LOOP;
3308: END IF;
3309: FND_LOG.String(
3308: END IF;
3309: FND_LOG.String(
3310: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3311: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).item_nbr= '|| s2genealogy_tab(v_tab_nodes.LAST).item_nbr,
3312: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3313:
3314: FND_LOG.String(
3315: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3316: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).serial_number= '|| s2genealogy_tab(v_tab_nodes.LAST).serial_number,
3313:
3314: FND_LOG.String(
3315: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3316: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).serial_number= '|| s2genealogy_tab(v_tab_nodes.LAST).serial_number,
3317: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3318:
3319: END IF;
3320: ----dbms_output.put_line(22);
3321: ----dbms_output.put_line(s2genealogy_tab(v_tab_nodes.LAST).item_nbr);
3356: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3357: FND_LOG.String(
3358: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3359: MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
3360: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3361:
3362: END IF;
3363:
3364: occ(genealogy_rec.item_nbr || '.' ||
3375: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3376: FND_LOG.String(
3377: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3378: MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
3379: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3380:
3381: END IF;
3382:
3383: PIPE ROW(genealogy_rec);
3390: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3391: FND_LOG.String(
3392: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3393: MESSAGE => l_stmt_no ||': CLOSE c_main; EXIT;',
3394: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3395:
3396: END IF;
3397: CLOSE c_main;
3398: EXIT;
3402: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3403: FND_LOG.String(
3404: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3405: MESSAGE => l_stmt_no ||': CLOSE c_main;',
3406: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3407: FND_LOG.String(
3408: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3409: MESSAGE => l_stmt_no ||': vr_main.serial_number = '|| vr_main.serial_number ,
3410: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3406: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3407: FND_LOG.String(
3408: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3409: MESSAGE => l_stmt_no ||': vr_main.serial_number = '|| vr_main.serial_number ,
3410: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3411:
3412: FND_LOG.String(
3413: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3414: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3411:
3412: FND_LOG.String(
3413: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3414: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3415: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3416:
3417: END IF;
3418: CLOSE c_main;
3419: ----dbms_output.put_line(24);
3430: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3431: FND_LOG.String(
3432: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3433: MESSAGE => l_stmt_no ||':EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s1genealogy_tab USING p_organization_id ,vr_main.serial_number , vr_main.inventory_item_id ,p_organization_id;',
3434: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
3435:
3436: FND_LOG.String(
3437: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3438: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
3435:
3436: FND_LOG.String(
3437: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3438: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
3439: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3440: dbg_s1genealogy_tab := s1genealogy_tab;
3441: IF dbg_s1genealogy_tab.COUNT > 0 THEN
3442: FOR i1 IN dbg_s1genealogy_tab.FIRST .. dbg_s1genealogy_tab.LAST LOOP
3443: FND_LOG.String(
3442: FOR i1 IN dbg_s1genealogy_tab.FIRST .. dbg_s1genealogy_tab.LAST LOOP
3443: FND_LOG.String(
3444: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3445: MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).item_nbr= '|| dbg_s1genealogy_tab(i1).item_nbr,
3446: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3447:
3448: FND_LOG.String(
3449: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3450: MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).serial_number= '|| dbg_s1genealogy_tab(i1).serial_number,
3447:
3448: FND_LOG.String(
3449: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3450: MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).serial_number= '|| dbg_s1genealogy_tab(i1).serial_number,
3451: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3452: FND_LOG.String(
3453: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3454: MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).serial_number= '|| dbg_s1genealogy_tab(i1).job_name,
3455: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3451: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3452: FND_LOG.String(
3453: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3454: MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).serial_number= '|| dbg_s1genealogy_tab(i1).job_name,
3455: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3456:
3457: END LOOP;
3458: END IF;
3459: END IF;
3463: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3464: FND_LOG.String(
3465: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3466: MESSAGE => l_stmt_no ||': IF s1genealogy_tab.COUNT <> 0',
3467: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3468:
3469: END IF;
3470: ----dbms_output.put_line(27);
3471: ----dbms_output.put_line(vr_main.serial_number);
3479: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3480: FND_LOG.String(
3481: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3482: MESSAGE => l_stmt_no ||': IF vr_main.inventory_item_id <> sgenealogy_tab(j).inventory_item_id THEN',
3483: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
3484: FND_LOG.String(
3485: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3486: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3487: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3483: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
3484: FND_LOG.String(
3485: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3486: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3487: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3488:
3489: FND_LOG.String(
3490: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3491: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).inventory_item_id= '|| sgenealogy_tab(j).inventory_item_id,
3488:
3489: FND_LOG.String(
3490: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3491: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).inventory_item_id= '|| sgenealogy_tab(j).inventory_item_id,
3492: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3493:
3494: FND_LOG.String(
3495: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3496: MESSAGE => l_stmt_no ||': Y= '|| Y,
3493:
3494: FND_LOG.String(
3495: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3496: MESSAGE => l_stmt_no ||': Y= '|| Y,
3497: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3498:
3499: FND_LOG.String(
3500: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3501: MESSAGE => l_stmt_no ||': Z= '|| Z,
3498:
3499: FND_LOG.String(
3500: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3501: MESSAGE => l_stmt_no ||': Z= '|| Z,
3502: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3503:
3504: END IF;
3505: IF s1genealogy_tab.EXISTS(Z) THEN
3506: ----dbms_output.put_line(28);
3509: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3510: FND_LOG.String(
3511: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3512: MESSAGE => l_stmt_no ||': IF s1genealogy_tab.EXISTS(Z)',
3513: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3514: FND_LOG.String(
3515: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3516: MESSAGE => l_stmt_no ||': FOR l IN Z..s1genealogy_tab.COUNT',
3517: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3513: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3514: FND_LOG.String(
3515: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3516: MESSAGE => l_stmt_no ||': FOR l IN Z..s1genealogy_tab.COUNT',
3517: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3518: FND_LOG.String(
3519: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3520: MESSAGE => 's1genealogy_tab.COUNT= '|| s1genealogy_tab.COUNT,
3521: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3517: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3518: FND_LOG.String(
3519: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3520: MESSAGE => 's1genealogy_tab.COUNT= '|| s1genealogy_tab.COUNT,
3521: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3522:
3523: END IF;
3524: FOR l IN Z..s1genealogy_tab.COUNT
3525: LOOP
3527: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3528: FND_LOG.String(
3529: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3530: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).item_nbr= '|| s1genealogy_tab(l).item_nbr,
3531: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3532:
3533: FND_LOG.String(
3534: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3535: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).serial_number= '|| s1genealogy_tab(l).serial_number,
3532:
3533: FND_LOG.String(
3534: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3535: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).serial_number= '|| s1genealogy_tab(l).serial_number,
3536: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3537: FND_LOG.String(
3538: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3539: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).transaction_quantity= '|| s1genealogy_tab(l).transaction_quantity,
3540: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3536: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3537: FND_LOG.String(
3538: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3539: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).transaction_quantity= '|| s1genealogy_tab(l).transaction_quantity,
3540: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3541:
3542: END IF;
3543: ----dbms_output.put_line(29);
3544: ----dbms_output.put_line(s1genealogy_tab(l).item_nbr);
3575: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3576: FND_LOG.String(
3577: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3578: MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
3579: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3580:
3581: END IF;
3582:
3583: occ(genealogy_rec.item_nbr || '.' ||
3595: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3596: FND_LOG.String(
3597: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3598: MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
3599: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3600:
3601: END IF;
3602: PIPE ROW(genealogy_rec);
3603: ----dbms_output.put_line(50);
3607: l_stmt_no := 340;
3608: FND_LOG.String(
3609: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3610: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).transaction_quantity= '|| sgenealogy_tab(j).transaction_quantity,
3611: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3612:
3613: FND_LOG.String(
3614: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3615: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
3612:
3613: FND_LOG.String(
3614: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3615: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
3616: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3617:
3618: END IF;
3619: IF sgenealogy_tab(j).transaction_quantity >= v_ctr THEN
3620: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3621: l_stmt_no := 341;
3622: FND_LOG.String(
3623: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3624: MESSAGE => l_stmt_no ||': IF sgenealogy_tab(j).transaction_quantity >= v_ctr THEN',
3625: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
3626:
3627: END IF;
3628:
3629: genealogy_rec.serial_number := sgenealogy_tab(j).serial_number;
3638: l_stmt_no := 342;
3639: FND_LOG.String(
3640: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3641: MESSAGE => l_stmt_no ||': ELSIF v_ctr < sgenealogy_tab(j).transaction_quantity THEN',
3642: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
3643:
3644: END IF;
3645:
3646: ----dbms_output.put_line(32);
3656: l_stmt_no := 350;
3657: FND_LOG.String(
3658: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3659: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
3660: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3661:
3662: FND_LOG.String(
3663: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3664: MESSAGE => l_stmt_no ||': v_level= '|| v_level,
3661:
3662: FND_LOG.String(
3663: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3664: MESSAGE => l_stmt_no ||': v_level= '|| v_level,
3665: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3666:
3667: END IF;
3668:
3669: ELSIF s1genealogy_tab(l).transaction_quantity = v_ctr THEN
3671: l_stmt_no := 360;
3672: FND_LOG.String(
3673: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3674: MESSAGE => l_stmt_no ||': ELSIF s1genealogy_tab(l).transaction_quantity = v_ctr THEN',
3675: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
3676:
3677: FND_LOG.String(
3678: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3679: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).transaction_quantity= '|| s1genealogy_tab(l).transaction_quantity,
3676:
3677: FND_LOG.String(
3678: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3679: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).transaction_quantity= '|| s1genealogy_tab(l).transaction_quantity,
3680: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3681:
3682: FND_LOG.String(
3683: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3684: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
3681:
3682: FND_LOG.String(
3683: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3684: MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
3685: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3686:
3687: END IF;
3688: ----dbms_output.put_line(35);
3689: IF s1genealogy_tab.EXISTS(l + 1) THEN
3691: l_stmt_no := 370;
3692: FND_LOG.String(
3693: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3694: MESSAGE => l_stmt_no ||': IF s1genealogy_tab.EXISTS(l+1)',
3695: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3696: FND_LOG.String(
3697: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3698: MESSAGE => l_stmt_no ||': FOR m IN l+1..s1genealogy_tab.COUNT',
3699: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3695: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3696: FND_LOG.String(
3697: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3698: MESSAGE => l_stmt_no ||': FOR m IN l+1..s1genealogy_tab.COUNT',
3699: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3700:
3701: END IF;
3702:
3703: ----dbms_output.put_line(36);
3713: l_stmt_no := 380;
3714: FND_LOG.String(
3715: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3716: MESSAGE => l_stmt_no ||': s1genealogy_tab(m).item_nbr= '|| s1genealogy_tab(m).item_nbr,
3717: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3718:
3719: FND_LOG.String(
3720: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3721: MESSAGE => l_stmt_no ||': s1genealogy_tab(m).serial_number= '|| s1genealogy_tab(m).serial_number,
3718:
3719: FND_LOG.String(
3720: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3721: MESSAGE => l_stmt_no ||': s1genealogy_tab(m).serial_number= '|| s1genealogy_tab(m).serial_number,
3722: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3723:
3724: FND_LOG.String(
3725: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3726: MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
3723:
3724: FND_LOG.String(
3725: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3726: MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
3727: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3728:
3729: FND_LOG.String(
3730: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3731: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3728:
3729: FND_LOG.String(
3730: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3731: MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3732: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3733:
3734: END IF;
3735:
3736: v_tab_nodes(m).serial_number := s1genealogy_tab(m).serial_number;
3761: l_stmt_no := 390;
3762: FND_LOG.String(
3763: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3764: MESSAGE => l_stmt_no ||': OPEN c_main(v_tab_nodes(m).serial_number',
3765: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3766: FND_LOG.String(
3767: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3768: MESSAGE => l_stmt_no ||': v_tab_nodes(m).serial_number= '|| v_tab_nodes(m).serial_number,
3769: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3765: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3766: FND_LOG.String(
3767: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3768: MESSAGE => l_stmt_no ||': v_tab_nodes(m).serial_number= '|| v_tab_nodes(m).serial_number,
3769: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3770:
3771: FND_LOG.String(
3772: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3773: MESSAGE => l_stmt_no ||': v_tab_nodes(m).inventory_item_id= '|| v_tab_nodes(m).inventory_item_id,
3770:
3771: FND_LOG.String(
3772: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3773: MESSAGE => l_stmt_no ||': v_tab_nodes(m).inventory_item_id= '|| v_tab_nodes(m).inventory_item_id,
3774: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3775:
3776: END IF;
3777:
3778: OPEN c_main(v_tab_nodes(m).serial_number ,
3785: l_stmt_no := 400;
3786: FND_LOG.String(
3787: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3788: MESSAGE => l_stmt_no ||': IF c_main%NOTFOUND',
3789: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3790:
3791: END IF;
3792:
3793: ----dbms_output.put_line(61);
3796: l_stmt_no := 410;
3797: FND_LOG.String(
3798: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3799: MESSAGE => l_stmt_no ||': v_tab_nodes.COUNT > 0',
3800: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3801: FND_LOG.String(
3802: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3803: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).item_nbr= '|| s2genealogy_tab(v_tab_nodes.LAST).item_nbr,
3804: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3800: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3801: FND_LOG.String(
3802: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3803: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).item_nbr= '|| s2genealogy_tab(v_tab_nodes.LAST).item_nbr,
3804: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3805:
3806: FND_LOG.String(
3807: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3808: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).serial_number= '|| s2genealogy_tab(v_tab_nodes.LAST).serial_number,
3805:
3806: FND_LOG.String(
3807: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3808: MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).serial_number= '|| s2genealogy_tab(v_tab_nodes.LAST).serial_number,
3809: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3810:
3811:
3812: END IF;
3813: ----dbms_output.put_line(62);
3846: l_stmt_no := 420;
3847: FND_LOG.String(
3848: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3849: MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
3850: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3851: FND_LOG.String(
3852: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3853: MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
3854: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3850: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3851: FND_LOG.String(
3852: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3853: MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
3854: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3855: END IF;
3856: occ(genealogy_rec.item_nbr || '.' ||
3857: genealogy_rec.lot_number || '.' ||
3858: genealogy_rec.serial_number || '.' ||
3876: l_stmt_no := 430;
3877: FND_LOG.String(
3878: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3879: MESSAGE => l_stmt_no ||': genealogy_rec.item_nbr= '|| genealogy_rec.item_nbr,
3880: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3881:
3882: FND_LOG.String(
3883: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3884: MESSAGE => l_stmt_no ||': genealogy_rec.serial_number= '|| genealogy_rec.serial_number,
3881:
3882: FND_LOG.String(
3883: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3884: MESSAGE => l_stmt_no ||': genealogy_rec.serial_number= '|| genealogy_rec.serial_number,
3885: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3886:
3887: FND_LOG.String(
3888: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3889: MESSAGE => l_stmt_no ||': genealogy_rec.inventory_item_id= '|| genealogy_rec.inventory_item_id,
3886:
3887: FND_LOG.String(
3888: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3889: MESSAGE => l_stmt_no ||': genealogy_rec.inventory_item_id= '|| genealogy_rec.inventory_item_id,
3890: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3891:
3892: END IF;
3893: ELSE--IF v_tab_nodes.COUNT > 0 THEN
3894: ----dbms_output.put_line(64);
3897: l_stmt_no := 440;
3898: FND_LOG.String(
3899: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3900: MESSAGE => l_stmt_no ||': CLOSE c_main;',
3901: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3902: FND_LOG.String(
3903: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3904: MESSAGE => l_stmt_no ||': END IF;--IF v_tab_nodes.COUNT > 0 THEN ',
3905: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3901: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3902: FND_LOG.String(
3903: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3904: MESSAGE => l_stmt_no ||': END IF;--IF v_tab_nodes.COUNT > 0 THEN ',
3905: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3906:
3907: END IF;
3908: END IF;--IF v_tab_nodes.COUNT > 0 THEN
3909: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3909: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3910: FND_LOG.String(
3911: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3912: MESSAGE => l_stmt_no ||': END IF;--IF c_main%NOTFOUND THEN ',
3913: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3914:
3915: END IF;
3916:
3917: END IF;--IF c_main%NOTFOUND THEN
3921: l_stmt_no := 451;
3922: FND_LOG.String(
3923: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3924: MESSAGE => l_stmt_no ||': CLOSE c_main;',
3925: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3926:
3927: FND_LOG.String(
3928: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3929: MESSAGE => l_stmt_no ||': END LOOP;--FOR m IN l + 1..s1genealogy_tab.COUNT ',
3926:
3927: FND_LOG.String(
3928: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3929: MESSAGE => l_stmt_no ||': END LOOP;--FOR m IN l + 1..s1genealogy_tab.COUNT ',
3930: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3931:
3932: END IF;
3933:
3934: END LOOP;--FOR m IN l + 1..s1genealogy_tab.COUNT
3936: l_stmt_no := 452;
3937: FND_LOG.String(
3938: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3939: MESSAGE => l_stmt_no ||': END IF;--IF s1genealogy_tab.EXISTS(l + 1) THEN',
3940: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3941:
3942: END IF;
3943:
3944: END IF;--IF s1genealogy_tab.EXISTS(l + 1) THEN
3948: l_stmt_no := 460;
3949: FND_LOG.String(
3950: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3951: MESSAGE => l_stmt_no ||': v_level= '|| v_level,
3952: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3953: FND_LOG.String(
3954: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3955: MESSAGE => l_stmt_no ||': EXIT;',
3956: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3952: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3953: FND_LOG.String(
3954: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3955: MESSAGE => l_stmt_no ||': EXIT;',
3956: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3957: END IF;
3958: EXIT;
3959: ----dbms_output.put_line(39);
3960: ELSIF s1genealogy_tab(l).transaction_quantity > v_ctr THEN
3962: l_stmt_no := 470;
3963: FND_LOG.String(
3964: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3965: MESSAGE => l_stmt_no ||': ELSIF s1genealogy_tab(l).transaction_quantity > v_ctr',
3966: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3967:
3968: END IF;
3969:
3970: ----dbms_output.put_line(80);
3972: l_stmt_no := 480;
3973: FND_LOG.String(
3974: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3975: MESSAGE => l_stmt_no ||': OPEN c_main(s1genealogy_tab(l).serial_number',
3976: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3977:
3978: END IF;
3979:
3980: OPEN c_main(s1genealogy_tab(l).serial_number ,
3986: l_stmt_no := 490;
3987: FND_LOG.String(
3988: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3989: MESSAGE => l_stmt_no ||': IF c_main%FOUND',
3990: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3991:
3992:
3993:
3994: END IF;
4003: l_stmt_no := 500;
4004: FND_LOG.String(
4005: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4006: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).serial_number= '|| s1genealogy_tab(l).serial_number,
4007: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4008:
4009: FND_LOG.String(
4010: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4011: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).inventory_item_id= '|| s1genealogy_tab(l).inventory_item_id,
4008:
4009: FND_LOG.String(
4010: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4011: MESSAGE => l_stmt_no ||': s1genealogy_tab(l).inventory_item_id= '|| s1genealogy_tab(l).inventory_item_id,
4012: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4013:
4014: FND_LOG.String(
4015: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4016: MESSAGE => l_stmt_no ||': v_level= '|| v_level,
4013:
4014: FND_LOG.String(
4015: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4016: MESSAGE => l_stmt_no ||': v_level= '|| v_level,
4017: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4018:
4019: FND_LOG.String(
4020: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4021: MESSAGE => l_stmt_no ||': CLOSE c_main',
4018:
4019: FND_LOG.String(
4020: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4021: MESSAGE => l_stmt_no ||': CLOSE c_main',
4022: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4023:
4024: FND_LOG.String(
4025: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4026: MESSAGE => l_stmt_no ||': EXIT',
4023:
4024: FND_LOG.String(
4025: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4026: MESSAGE => l_stmt_no ||': EXIT',
4027: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4028:
4029: END IF;
4030:
4031: EXIT;
4035: l_stmt_no := 510;
4036: FND_LOG.String(
4037: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4038: MESSAGE => l_stmt_no ||': CLOSE c_main',
4039: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4040: FND_LOG.String(
4041: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4042: MESSAGE => l_stmt_no ||': END IF;--IF c_main%FOUND THEN ',
4043: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4039: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4040: FND_LOG.String(
4041: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4042: MESSAGE => l_stmt_no ||': END IF;--IF c_main%FOUND THEN ',
4043: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4044:
4045:
4046: END IF;
4047:
4055: l_stmt_no := 511;
4056: FND_LOG.String(
4057: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4058: MESSAGE => l_stmt_no ||': END LOOP;--FOR l IN Z..s1genealogy_tab.COUNT',
4059: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4060:
4061: FND_LOG.String(
4062: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4063: MESSAGE => l_stmt_no ||': END IF;--IF s1genealogy_tab.EXISTS(Z) THEN',
4060:
4061: FND_LOG.String(
4062: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4063: MESSAGE => l_stmt_no ||': END IF;--IF s1genealogy_tab.EXISTS(Z) THEN',
4064: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4065:
4066: END IF;
4067:
4068: END IF;--IF s1genealogy_tab.EXISTS(Z) THEN
4072: l_stmt_no := 512;
4073: FND_LOG.String(
4074: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4075: MESSAGE => l_stmt_no ||': ELSE--IF s1genealogy_tab.COUNT <> 0 THEN',
4076: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4077: FND_LOG.String(
4078: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4079: MESSAGE => l_stmt_no ||': EXIT; ',
4080: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4076: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4077: FND_LOG.String(
4078: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4079: MESSAGE => l_stmt_no ||': EXIT; ',
4080: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4081:
4082: END IF;
4083:
4084: ----dbms_output.put_line(43);
4090: l_stmt_no := 513;
4091: FND_LOG.String(
4092: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4093: MESSAGE => l_stmt_no ||': END LOOP;--c_main',
4094: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4095:
4096: END IF;
4097: ----dbms_output.put_line(45);
4098: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4099: l_stmt_no := 520;
4100: FND_LOG.String(
4101: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4102: MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
4103: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4104:
4105: FND_LOG.String(
4106: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4107: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).serial_number = '|| sgenealogy_tab(j).serial_number ,
4104:
4105: FND_LOG.String(
4106: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4107: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).serial_number = '|| sgenealogy_tab(j).serial_number ,
4108: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4109:
4110: FND_LOG.String(
4111: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4112: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).inventory_item_id= '|| sgenealogy_tab(j).inventory_item_id,
4109:
4110: FND_LOG.String(
4111: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4112: MESSAGE => l_stmt_no ||': sgenealogy_tab(j).inventory_item_id= '|| sgenealogy_tab(j).inventory_item_id,
4113: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4114:
4115: END IF;
4116:
4117: EXECUTE IMMEDIATE g_gen_sql
4128: FOR i6 IN dbg_s3genealogy_tab.FIRST .. dbg_s3genealogy_tab.LAST LOOP
4129: FND_LOG.String(
4130: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4131: MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).item_nbr= '|| dbg_s3genealogy_tab(i6).item_nbr,
4132: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4133:
4134: FND_LOG.String(
4135: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4136: MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).serial_number= '|| dbg_s3genealogy_tab(i6).serial_number,
4133:
4134: FND_LOG.String(
4135: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4136: MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).serial_number= '|| dbg_s3genealogy_tab(i6).serial_number,
4137: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4138:
4139: FND_LOG.String(
4140: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4141: MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).job_name= '|| dbg_s3genealogy_tab(i6).job_name,
4138:
4139: FND_LOG.String(
4140: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4141: MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).job_name= '|| dbg_s3genealogy_tab(i6).job_name,
4142: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4143:
4144: END LOOP;
4145: END IF;
4146: END IF;
4153: l_stmt_no := 540;
4154: FND_LOG.String(
4155: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4156: MESSAGE => l_stmt_no ||': Y= '|| Y,
4157: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4158:
4159: FND_LOG.String(
4160: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4161: MESSAGE => l_stmt_no ||': Z= '|| Z,
4158:
4159: FND_LOG.String(
4160: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4161: MESSAGE => l_stmt_no ||': Z= '|| Z,
4162: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4163:
4164: END IF;
4165:
4166: IF s3genealogy_tab.EXISTS(Z) THEN
4168: l_stmt_no := 550;
4169: FND_LOG.String(
4170: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4171: MESSAGE => l_stmt_no ||': IF s3genealogy_tab.EXISTS(Z)',
4172: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4173: FND_LOG.String(
4174: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4175: MESSAGE => l_stmt_no ||': FOR l IN Z..s3genealogy_tab.COUNT',
4176: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
4172: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4173: FND_LOG.String(
4174: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4175: MESSAGE => l_stmt_no ||': FOR l IN Z..s3genealogy_tab.COUNT',
4176: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
4177:
4178: END IF;
4179:
4180: FOR l IN Z..s3genealogy_tab.COUNT
4185: l_stmt_no := 560;
4186: FND_LOG.String(
4187: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4188: MESSAGE => l_stmt_no ||': s3genealogy_tab(l).item_nbr= '|| s3genealogy_tab(l).item_nbr,
4189: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4190:
4191: FND_LOG.String(
4192: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4193: MESSAGE => l_stmt_no ||': s3genealogy_tab(l).serial_number= '|| s3genealogy_tab(l).serial_number,
4190:
4191: FND_LOG.String(
4192: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4193: MESSAGE => l_stmt_no ||': s3genealogy_tab(l).serial_number= '|| s3genealogy_tab(l).serial_number,
4194: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4195:
4196: END IF;
4197:
4198: genealogy_rec.item_nbr := s3genealogy_tab(l).item_nbr;
4226: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4227: FND_LOG.String(
4228: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4229: MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
4230: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4231:
4232: END IF;
4233: occ(genealogy_rec.item_nbr || '.' ||
4234: genealogy_rec.lot_number || '.' ||
4244: l_stmt_no := 570;
4245: FND_LOG.String(
4246: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4247: MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
4248: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4249: FND_LOG.String(
4250: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4251: MESSAGE => l_stmt_no ||': GOTO REPEAT_LOOP',
4252: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4248: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4249: FND_LOG.String(
4250: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4251: MESSAGE => l_stmt_no ||': GOTO REPEAT_LOOP',
4252: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4253: END IF;
4254: PIPE ROW(genealogy_rec);
4255: GOTO REPEAT_LOOP;
4256: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4257: l_stmt_no := 571;
4258: FND_LOG.String(
4259: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4260: MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec); ',
4261: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4262: FND_LOG.String(
4263: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4264: MESSAGE => l_stmt_no ||': GOTO REPEAT_LOOP;',
4265: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4261: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4262: FND_LOG.String(
4263: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4264: MESSAGE => l_stmt_no ||': GOTO REPEAT_LOOP;',
4265: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4266:
4267: FND_LOG.String(
4268: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4269: MESSAGE => l_stmt_no ||': END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr || "." ||',
4266:
4267: FND_LOG.String(
4268: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4269: MESSAGE => l_stmt_no ||': END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr || "." ||',
4270: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4271:
4272: END IF;
4273: END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
4274: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4275: l_stmt_no := 572;
4276: FND_LOG.String(
4277: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4278: MESSAGE => l_stmt_no ||': END LOOP;--FOR l IN Z..s3genealogy_tab.COUNT',
4279: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4280:
4281: END IF;
4282:
4283: END LOOP;--FOR l IN Z..s3genealogy_tab.COUNT
4285: l_stmt_no := 573;
4286: FND_LOG.String(
4287: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4288: MESSAGE => l_stmt_no ||': END IF;--s3genealogy_tab.EXISTS(Z) ',
4289: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4290:
4291: END IF;
4292:
4293: END IF;--s3genealogy_tab.EXISTS(Z)
4295: l_stmt_no := 574;
4296: FND_LOG.String(
4297: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4298: MESSAGE => l_stmt_no ||': END IF;--s3genealogy_tab.COUNT <> 0 ',
4299: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4300:
4301: END IF;
4302:
4303: END IF;--s3genealogy_tab.COUNT <> 0
4305: l_stmt_no := 575;
4306: FND_LOG.String(
4307: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4308: MESSAGE => l_stmt_no ||': END LOOP;--FOR j IN 1..sgenealogy_tab.COUNT',
4309: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4310:
4311: END IF;
4312:
4313: END LOOP;--FOR j IN 1..sgenealogy_tab.COUNT
4316: l_stmt_no := 576;
4317: FND_LOG.String(
4318: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4319: MESSAGE => l_stmt_no ||': END IF;--sgenealogy_tab.COUNT <> 0',
4320: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4321:
4322: END IF;
4323:
4324: END IF;--sgenealogy_tab.COUNT <> 0
4327: l_stmt_no := 577;
4328: FND_LOG.String(
4329: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4330: MESSAGE => l_stmt_no ||': END IF;--v_wms_enabled = 0 AND v_make_items = "Y"',
4331: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4332:
4333: END IF;
4334:
4335: END IF;--v_wms_enabled = 0 AND v_make_items = 'Y'
4338: l_stmt_no := 578;
4339: FND_LOG.String(
4340: LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4341: MESSAGE => l_stmt_no ||': END LOOP;--I IN 1..genealogy_tab.COUNT',
4342: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4343:
4344: END IF;
4345: END LOOP;--I IN 1..genealogy_tab.COUNT
4346:
4558: l_stmt_no := 590;
4559: FND_LOG.String(
4560: LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
4561: MESSAGE => l_stmt_no ||': Exiting get_genealogy',
4562: MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4563: END IF;
4564: RETURN;
4565: END;
4566: ---------------------------------------------------------------------------------------
4611: NULL;
4612: WHEN INVALID_NUMBER THEN
4613: NULL;
4614: END;
4615: RETURN SUBSTR(p_char, 1, instr(p_char, TO_CHAR(wsh_opsm_asn_item_genealogy.get_digits(p_char)), 1) - 1);
4616: END;
4617: ---------------------------------------------------------------------------------------
4618: --
4619: -- Function: check_for_itemgenealogy
4664: END;
4665: END LOOP;
4666: RETURN (v_serial_number);
4667: END;
4668: END wsh_opsm_asn_item_genealogy;