APPS.AHL_COMPLEX_MX_RWSC_PVT dependencies on AHL_VISIT_IR_UC_DETAILS

Line 85: SELECT COUNT(1) from ahl_visit_ir_uc_details;

81: l_api_name CONSTANT VARCHAR2(30) := 'Get_visit_ir_uc_details';
82: l_api_version CONSTANT NUMBER := 1.0;
83:
84: CURSOR get_record_count IS
85: SELECT COUNT(1) from ahl_visit_ir_uc_details;
86:
87: CURSOR is_visit_present(c_visit_id IN NUMBER) IS
88: SELECT COUNT(1) FROM AHL_VISIT_IR_UC_DETAILS WHERE visit_id = c_visit_id;
89:

Line 88: SELECT COUNT(1) FROM AHL_VISIT_IR_UC_DETAILS WHERE visit_id = c_visit_id;

84: CURSOR get_record_count IS
85: SELECT COUNT(1) from ahl_visit_ir_uc_details;
86:
87: CURSOR is_visit_present(c_visit_id IN NUMBER) IS
88: SELECT COUNT(1) FROM AHL_VISIT_IR_UC_DETAILS WHERE visit_id = c_visit_id;
89:
90: CURSOR is_inst_present(c_instance_id IN NUMBER) IS
91: SELECT COUNT(1) FROM AHL_VISIT_IR_UC_DETAILS WHERE root_instance_id = c_instance_id;
92:

Line 91: SELECT COUNT(1) FROM AHL_VISIT_IR_UC_DETAILS WHERE root_instance_id = c_instance_id;

87: CURSOR is_visit_present(c_visit_id IN NUMBER) IS
88: SELECT COUNT(1) FROM AHL_VISIT_IR_UC_DETAILS WHERE visit_id = c_visit_id;
89:
90: CURSOR is_inst_present(c_instance_id IN NUMBER) IS
91: SELECT COUNT(1) FROM AHL_VISIT_IR_UC_DETAILS WHERE root_instance_id = c_instance_id;
92:
93: l_count NUMBER;
94: l_count_instance NUMBER;
95: l_count_visit NUMBER;

Line 136: 'Deleting all rows in ahl_visit_ir_uc_details');

132: IF l_count_visit = 0 THEN
133: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
134: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
135: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
136: 'Deleting all rows in ahl_visit_ir_uc_details');
137: END IF;
138:
139: -- even if table rows are zero it will not throw error.
140: DELETE FROM ahl_visit_ir_uc_details;

Line 140: DELETE FROM ahl_visit_ir_uc_details;

136: 'Deleting all rows in ahl_visit_ir_uc_details');
137: END IF;
138:
139: -- even if table rows are zero it will not throw error.
140: DELETE FROM ahl_visit_ir_uc_details;
141:
142: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
144: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,

Line 145: 'all rows in ahl_visit_ir_uc_details deleted');

141:
142: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
144: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
145: 'all rows in ahl_visit_ir_uc_details deleted');
146: END IF;
147:
148: END IF;
149:

Line 183: INSERT INTO ahl_visit_ir_uc_details

179: END IF;
180:
181: IF x_visit_ir_uc_details_tbl.FIRST IS NOT NULL THEN
182: FOR i IN x_visit_ir_uc_details_tbl.FIRST..x_visit_ir_uc_details_tbl.LAST LOOP
183: INSERT INTO ahl_visit_ir_uc_details
184: (
185: visit_ir_uc_id,
186: root_instance_id,
187: unit_header_id,

Line 541: FROM AHL_VISIT_IR_UC_DETAILS

537:
538: /*This query will return non null child instances, also it returns zero records for leaf nodes. */
539: CURSOR get_immediate_childern(c_instance_id IN NUMBER) IS
540: SELECT curr_instance_id
541: FROM AHL_VISIT_IR_UC_DETAILS
542: WHERE parent_instance_id = c_instance_id
543: AND curr_instance_id IS NOT NULL;
544:
545: CURSOR get_bg_instance(c_instance_id IN NUMBER) IS

Line 547: FROM AHL_VISIT_IR_UC_DETAILS

543: AND curr_instance_id IS NOT NULL;
544:
545: CURSOR get_bg_instance(c_instance_id IN NUMBER) IS
546: SELECT bg_instance, leaf_node_flag
547: FROM AHL_VISIT_IR_UC_DETAILS
548: WHERE curr_instance_id = c_instance_id;
549:
550: TYPE t_child_inst_tbl IS TABLE OF get_immediate_childern%ROWTYPE INDEX BY BINARY_INTEGER;
551:

Line 590: UPDATE AHL_VISIT_IR_UC_DETAILS

586: END LOOP;
587:
588: -- if this is not leaf node and bg_per value is not null, then set the bg_per value in table
589: IF l_child_bg_tbl.leaf_node_flag = 'N' AND bg_per IS NOT NULL THEN
590: UPDATE AHL_VISIT_IR_UC_DETAILS
591: SET bg_position = bg_per
592: WHERE curr_instance_id = p_current_instance_id;
593: END IF;
594:

Line 628: SELECT (NVL(MAX(visit_ir_uc_id),0) + 1) from ahl_visit_ir_uc_details;

624: l_unit_name VARCHAR2(80);
625: l_root_instance_id NUMBER;
626:
627: CURSOR get_max_pk_count IS
628: SELECT (NVL(MAX(visit_ir_uc_id),0) + 1) from ahl_visit_ir_uc_details;
629:
630: CURSOR uc_header_information(c_unit_header_id IN NUMBER) IS
631: SELECT name,csi_item_instance_id FROM AHL_UNIT_CONFIG_HEADERS
632: WHERE unit_config_header_id = c_unit_header_id;

Line 1014: SELECT (NVL(MAX(visit_ir_uc_id),0) + 1) from ahl_visit_ir_uc_details;

1010: l_msg_count NUMBER;
1011: l_msg_data VARCHAR2(2000);
1012:
1013: CURSOR get_max_pk_count IS
1014: SELECT (NVL(MAX(visit_ir_uc_id),0) + 1) from ahl_visit_ir_uc_details;
1015:
1016: CURSOR get_part_info(c_instance_id NUMBER) IS
1017: SELECT MTL.inventory_item_id,MTL.concatenated_segments,CSI.instance_number,CSI.serial_number, CSI.quantity
1018: FROM MTL_SYSTEM_ITEMS_VL MTL, CSI_ITEM_INSTANCES CSI