[Home] [Help]
94: AND star_request_id IS NULL;
95:
96: IF (l_no_of_records = 0) THEN
97: MERGE INTO jty_changed_terrs A
98: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
99: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
100: WHEN MATCHED THEN
101: UPDATE SET
102: A.rank_calc_flag = 'Y'
131: AND star_request_id IS NULL;
132:
133: IF (l_no_of_records = 0) THEN
134: MERGE INTO jty_changed_dea_terrs A
135: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
136: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
137: WHEN MATCHED THEN
138: UPDATE SET
139: A.rank_calc_flag = 'Y'
172: AND star_request_id IS NULL;
173:
174: IF (l_no_of_records = 0) THEN
175: MERGE INTO jty_changed_terrs A
176: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
177: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
178: WHEN MATCHED THEN
179: UPDATE SET
180: A.HIER_PROCESSING_FLAG = 'I'
209: AND star_request_id IS NULL;
210:
211: IF (l_no_of_records = 0) THEN
212: MERGE INTO jty_changed_dea_terrs A
213: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
214: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
215: WHEN MATCHED THEN
216: UPDATE SET
217: A.HIER_PROCESSING_FLAG = 'I'
241:
242: IF (n_parent_territory_id <> o_parent_territory_id) THEN
243:
244: MERGE INTO jty_changed_terrs A
245: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
246: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
247: WHEN MATCHED THEN
248: UPDATE SET
249: A.rank_calc_flag = 'Y'
272: ,'Y'
273: ,'I');
274:
275: MERGE INTO jty_changed_dea_terrs A
276: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
277: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
278: WHEN MATCHED THEN
279: UPDATE SET
280: A.rank_calc_flag = 'Y'
308: (o_end_date_active > sysdate and o_start_date_active > sysdate)) THEN
309: IF (n_start_date_active < sysdate and n_end_date_active > sysdate) THEN
310: /* future or inactive territory has become active as a result of the change */
311: MERGE INTO jty_changed_terrs A
312: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
313: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
314: WHEN MATCHED THEN
315: UPDATE SET
316: A.rank_calc_flag = 'Y'
340: ,'I');
341:
342: /* future or inactive territory has become active as a result of the change */
343: MERGE INTO jty_changed_dea_terrs A
344: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
345: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
346: WHEN MATCHED THEN
347: UPDATE SET
348: A.rank_calc_flag = 'Y'
375: IF ((n_end_date_active < sysdate and n_start_date_active < sysdate) OR
376: (n_end_date_active > sysdate and n_start_date_active > sysdate)) THEN
377: /* active territory has become future or inactive as a resukt of the change */
378: MERGE INTO jty_changed_terrs A
379: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
380: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
381: WHEN MATCHED THEN
382: UPDATE SET
383: A.rank_calc_flag = 'N'
407: ,'D');
408:
409: /* active territory has become future or inactive as a resukt of the change */
410: MERGE INTO jty_changed_dea_terrs A
411: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
412: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
413: WHEN MATCHED THEN
414: UPDATE SET
415: A.rank_calc_flag = 'N'
439: ,'D');
440: ELSIF ((n_end_date_active > sysdate and n_end_date_active <> o_end_date_active)) THEN
441: /* active territory has become future or inactive as a resukt of the change */
442: MERGE INTO jty_changed_terrs A
443: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
444: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
445: WHEN MATCHED THEN
446: UPDATE SET
447: A.rank_calc_flag = 'N'
471: ,'I');
472:
473: /* active territory has become future or inactive as a resukt of the change */
474: MERGE INTO jty_changed_dea_terrs A
475: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
476: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
477: WHEN MATCHED THEN
478: UPDATE SET
479: A.rank_calc_flag = 'N'
503: ,'I');
504: ELSIF ((n_end_date_active >= sysdate and n_start_date_active <> o_start_date_active)) THEN
505: /* active territory has become future or inactive as a resukt of the change */
506: MERGE INTO jty_changed_dea_terrs A
507: USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
508: ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
509: WHEN MATCHED THEN
510: UPDATE SET
511: A.rank_calc_flag = 'N'
634:
635: BEGIN
636: SELECT source_id
637: INTO l_source_id
638: FROM jtf_terr_usgs_all
639: WHERE terr_id = l_terr_id;
640: EXCEPTION
641: WHEN OTHERS then
642: NULL;
749:
750: BEGIN
751: SELECT source_id
752: INTO l_source_id
753: FROM jtf_terr_usgs_all
754: WHERE terr_id = p_terr_id;
755: EXCEPTION
756: WHEN OTHERS then
757: NULL;
837:
838: BEGIN
839: SELECT source_id
840: INTO l_source_id
841: FROM jtf_terr_usgs_all
842: WHERE terr_id = p_terr_id;
843: EXCEPTION
844: WHEN OTHERS then
845: NULL;
965:
966: BEGIN
967: SELECT source_id
968: INTO l_source_id
969: FROM jtf_terr_usgs_all
970: WHERE terr_id = l_terr_id;
971: EXCEPTION
972: WHEN OTHERS then
973: NULL;
1049:
1050: BEGIN
1051: SELECT source_id
1052: INTO l_source_id
1053: FROM jtf_terr_usgs_all
1054: WHERE terr_id = p_terr_id;
1055: EXCEPTION
1056: WHEN OTHERS then
1057: NULL;