99: l_no_of_records := 0;
100:
101: SELECT count(*)
102: INTO l_no_of_records
103: FROM jty_terr_values_idx_header
104: WHERE source_id = p_source_id
105: AND qual_usg_id = p_qual_usg_id_tbl(i);
106:
107: IF (l_no_of_records = 0) THEN
105: AND qual_usg_id = p_qual_usg_id_tbl(i);
106:
107: IF (l_no_of_records = 0) THEN
108:
109: SELECT jty_terr_values_idx_header_s.nextval
110: INTO l_header_seq
111: FROM dual;
112:
113: SELECT 'JTY_DNM_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_RN' ||
112:
113: SELECT 'JTY_DNM_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_RN' ||
114: (nvl(max(to_number(substr(index_name, instr(index_name, '_RN')+3))), 0) + 1)
115: INTO l_index_name
116: FROM jty_terr_values_idx_header
117: WHERE index_name like 'JTY_DNM_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_RN%';
118:
119: INSERT INTO jty_terr_values_idx_header (
120: terr_values_idx_header_id
115: INTO l_index_name
116: FROM jty_terr_values_idx_header
117: WHERE index_name like 'JTY_DNM_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_RN%';
118:
119: INSERT INTO jty_terr_values_idx_header (
120: terr_values_idx_header_id
121: ,source_id
122: ,last_update_date
123: ,last_updated_by
520:
521: /* in incremental mode , if the qualifier is alreday present */
522: /* then mark it as being used by active territory */
523: IF (p_mode = 'INCREMENTAL') THEN
524: UPDATE jty_terr_values_idx_header
525: SET delete_flag = 'N'
526: WHERE source_id = p_source_id
527: AND qual_usg_id = p_qual_usg_id_tbl(i);
528: END IF; -- END IF (p_mode = 'INCREMENTAL')