APPS.AHL_UMP_UTIL_PKG dependencies on AHL_APPLICABLE_MR_RELNS

Line 556: DELETE FROM AHL_APPLICABLE_MR_RELNS;

552: --
553: BEGIN
554:
555: -- Initialize temporary table.
556: DELETE FROM AHL_APPLICABLE_MR_RELNS;
557:
558: OPEN ahl_applicable_mrs_csr;
559: LOOP
560: FETCH ahl_applicable_mrs_csr INTO l_mr_header_id,

Line 743: FROM ahl_applicable_mr_relns

739: CURSOR ahl_appl_mrs_csr(p_mr_id IN NUMBER,
740: p_item_instance_id IN NUMBER) IS
741: SELECT level depth_level, mr_header_id, csi_item_instance_id,
742: related_mr_header_id, related_csi_item_instance_id
743: FROM ahl_applicable_mr_relns
744: --WHERE orig_mr_header_id = p_mr_id
745: --AND orig_csi_item_instance_id = p_item_instance_id
746: START WITH mr_header_id = p_mr_id
747: AND csi_item_instance_id = p_item_instance_id

Line 763: FROM ahl_applicable_mr_relns

759: p_related_item_instance_id IN NUMBER,
760: p_orig_mr_id IN NUMBER,
761: p_orig_item_instance_id IN NUMBER) IS
762: SELECT NVL(tree_depth_level, 0)
763: FROM ahl_applicable_mr_relns
764: WHERE orig_mr_header_id = p_orig_mr_id
765: AND orig_csi_item_instance_id = p_orig_item_instance_id
766: AND mr_header_id = p_mr_id
767: AND csi_item_instance_id = p_item_instance_id

Line 774: FROM ahl_applicable_mr_relns

770: --
771: CURSOR ahl_duplicate_relns_csr(p_mr_id IN NUMBER,
772: p_item_instance_id IN NUMBER) IS
773: SELECT related_mr_header_id, related_csi_item_instance_id
774: FROM ahl_applicable_mr_relns
775: WHERE orig_mr_header_id = p_mr_id
776: AND orig_csi_item_instance_id = p_item_instance_id
777: GROUP BY related_mr_header_id, related_csi_item_instance_id
778: HAVING COUNT(*)>1;

Line 785: FROM ahl_applicable_mr_relns

781: p_item_instance_id IN NUMBER,
782: p_orig_mr_id IN NUMBER,
783: p_orig_item_instance_id IN NUMBER) IS
784: SELECT mr_header_id, csi_item_instance_id
785: FROM ahl_applicable_mr_relns
786: WHERE tree_depth_level = (SELECT max(tree_depth_level)
787: from ahl_applicable_mr_relns
788: where orig_mr_header_id = p_orig_mr_id
789: AND orig_csi_item_instance_id = p_orig_item_instance_id

Line 787: from ahl_applicable_mr_relns

783: p_orig_item_instance_id IN NUMBER) IS
784: SELECT mr_header_id, csi_item_instance_id
785: FROM ahl_applicable_mr_relns
786: WHERE tree_depth_level = (SELECT max(tree_depth_level)
787: from ahl_applicable_mr_relns
788: where orig_mr_header_id = p_orig_mr_id
789: AND orig_csi_item_instance_id = p_orig_item_instance_id
790: AND related_mr_header_id = p_mr_id
791: AND related_csi_item_instance_id=p_item_instance_id)

Line 813: DELETE FROM AHL_APPLICABLE_MR_RELNS;

809: BEGIN
810:
811: -- Initialize temporary table.
812: IF (p_init_temp_table = 'Y') THEN
813: DELETE FROM AHL_APPLICABLE_MR_RELNS;
814: END IF;
815:
816: l_orig_ii_id := p_top_item_instance_id;
817: l_orig_mr_id := p_top_mr_id;

Line 822: --And populate the ahl_applicable_mr_relns table

818:
819:
820: --dbms_output.put_line(l_orig_mr_id||'::'||l_orig_ii_id);
821: --Now fetch all relations into l_mr_relns_tbl
822: --And populate the ahl_applicable_mr_relns table
823: OPEN ahl_fmp_relationships_csr(l_orig_mr_id);
824: LOOP
825: FETCH ahl_fmp_relationships_csr INTO l_mr_header_id,
826: l_related_mr_header_id,

Line 846: INSERT INTO AHL_APPLICABLE_MR_RELNS (

842: FETCH ahl_appl_child_mrs_csr INTO l_related_csi_ii_id;
843: EXIT WHEN ahl_appl_child_mrs_csr%NOTFOUND;
844:
845:
846: INSERT INTO AHL_APPLICABLE_MR_RELNS (
847: MR_HEADER_ID,
848: CSI_ITEM_INSTANCE_ID,
849: RELATED_MR_HEADER_ID,
850: RELATED_CSI_ITEM_INSTANCE_ID,

Line 890: UPDATE ahl_applicable_mr_relns

886:
887: IF (ahl_get_depth_level_csr%FOUND) THEN
888: --If depth is greater in rec, update to new depth
889: IF (l_depth_level < l_appl_mr_relns_rec.depth_level) THEN
890: UPDATE ahl_applicable_mr_relns
891: SET tree_depth_level = l_appl_mr_relns_rec.depth_level
892: WHERE orig_mr_header_id = l_orig_mr_id
893: AND orig_csi_item_instance_id = l_orig_ii_id
894: AND mr_header_id = l_appl_mr_relns_rec.mr_header_id

Line 905: DELETE FROM ahl_applicable_mr_relns

901: END LOOP;
902: CLOSE ahl_appl_mrs_csr;
903:
904: --Now delete all rows with null depth (unreachable)
905: DELETE FROM ahl_applicable_mr_relns
906: WHERE tree_depth_level IS NULL
907: AND orig_mr_header_id = l_orig_mr_id
908: AND orig_csi_item_instance_id = l_orig_ii_id;
909:

Line 923: DELETE FROM ahl_applicable_mr_relns

919: l_orig_ii_id);
920: FETCH ahl_max_depth_reln_csr INTO l_mr_header_id, l_csi_ii_id;
921: IF (ahl_max_depth_reln_csr%FOUND) THEN
922: --Delete all rows != edge with maximum depth
923: DELETE FROM ahl_applicable_mr_relns
924: WHERE (mr_header_id <> l_mr_header_id
925: OR csi_item_instance_id <> l_csi_ii_id)
926: AND orig_mr_header_id = l_orig_mr_id
927: AND orig_csi_item_instance_id = l_orig_ii_id