[Home] [Help]
PACKAGE BODY: APPS.JTY_TERR_TRIGGER_HANDLERS
Source
1 PACKAGE BODY JTY_TERR_TRIGGER_HANDLERS as
2 /* $Header: jtfyrhdb.pls 120.4 2011/04/26 09:03:07 sseshaiy ship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTY_TERR_TRIGGER_HANDLERS
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- This package defines Territory Trigger handlers.
10 -- Trigger handler API Spec for TABLES:
11 -- JTF_TERR, JTF_TERR_VALUES, JTF_TERR_RSC, JTF_TERR_RSC_ACCESS, JTF_TERR_QTYPE_USGS, JTF_TERR_QUAL
12 --
13 -- Procedures:
14 -- (see below for specification)
15 --
16 -- HISTORY
17 -- 08/25/05 achanda Created
18 -- End of Comments
19
20 --**************************************************************
21 -- Territory_Trigger_Handler
22 --**************************************************************
23 PROCEDURE Territory_Trigger_Handler (
24 p_terr_id IN NUMBER,
25 o_parent_territory_id IN NUMBER,
26 o_start_date_active IN DATE,
27 o_end_date_active IN DATE,
28 o_rank IN NUMBER,
29 o_num_winners IN NUMBER,
30 o_named_acct_flag IN VARCHAR2,
31 n_parent_territory_id IN NUMBER,
32 n_start_date_active IN DATE,
33 n_end_date_active IN DATE,
34 n_rank IN NUMBER,
35 n_num_winners IN NUMBER,
36 n_named_acct_flag IN VARCHAR2,
37 Trigger_Mode IN VARCHAR2)
38 IS
39 l_no_of_records NUMBER;
40
41 BEGIN
42
43 IF (Trigger_Mode = 'ON-INSERT') THEN
44
45 IF ((n_start_date_active < sysdate) AND (n_end_date_active > sysdate)) THEN
46 INSERT INTO jty_changed_terrs (
47 CHANGED_TERRITORY_ID
48 ,OBJECT_VERSION_NUMBER
49 ,TERR_ID
50 ,CHANGE_TYPE
51 ,RANK_CALC_FLAG
52 ,PROCESS_ATTR_VALUES_FLAG
53 ,MATCHING_SQL_FLAG
54 ,HIER_PROCESSING_FLAG)
55 VALUES (
56 jty_changed_terrs_s.nextval
57 ,0
58 ,p_terr_id
59 ,'CREATE'
60 ,'Y'
61 ,'I'
62 ,decode(n_named_acct_flag, 'Y', 'N', 'Y')
63 ,'I');
64
65 INSERT INTO jty_changed_dea_terrs (
66 CHANGED_TERRITORY_ID
67 ,OBJECT_VERSION_NUMBER
68 ,TERR_ID
69 ,CHANGE_TYPE
70 ,RANK_CALC_FLAG
71 ,PROCESS_ATTR_VALUES_FLAG
72 ,MATCHING_SQL_FLAG
73 ,HIER_PROCESSING_FLAG)
74 VALUES (
75 jty_changed_dea_terrs_s.nextval
76 ,0
77 ,p_terr_id
78 ,'CREATE'
79 ,'Y'
80 ,'I'
81 ,decode(n_named_acct_flag, 'Y', 'N', 'Y')
82 ,'I');
83 END IF;
84
85 ELSIF (Trigger_Mode = 'ON-UPDATE') THEN
86 IF (o_rank is null and n_rank is not null) OR
87 (o_rank is not null and n_rank is null) OR
88 (o_rank <> n_rank) THEN
89 SELECT count(*)
90 INTO l_no_of_records
91 FROM jty_changed_terrs
92 WHERE terr_id = p_terr_id
93 AND change_type = 'CREATE'
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'
103 WHEN NOT MATCHED THEN
104 INSERT (
105 A.CHANGED_TERRITORY_ID
106 ,A.OBJECT_VERSION_NUMBER
107 ,A.TERR_ID
108 ,A.SOURCE_ID
109 ,A.CHANGE_TYPE
110 ,A.RANK_CALC_FLAG
111 ,A.PROCESS_ATTR_VALUES_FLAG
112 ,A.MATCHING_SQL_FLAG
113 ,A.HIER_PROCESSING_FLAG)
114 VALUES (
115 jty_changed_terrs_s.nextval
116 ,0
117 ,S.terr_id
118 ,S.source_id
119 ,'UPDATE'
120 ,'Y'
121 ,'N'
122 ,'N'
123 ,'N');
124 END IF; /* end IF (l_no_of_records = 0) */
125
126 SELECT count(*)
127 INTO l_no_of_records
128 FROM jty_changed_dea_terrs
129 WHERE terr_id = p_terr_id
130 AND change_type = 'CREATE'
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'
140 WHEN NOT MATCHED THEN
141 INSERT (
142 A.CHANGED_TERRITORY_ID
143 ,A.OBJECT_VERSION_NUMBER
144 ,A.TERR_ID
145 ,A.SOURCE_ID
146 ,A.CHANGE_TYPE
147 ,A.RANK_CALC_FLAG
148 ,A.PROCESS_ATTR_VALUES_FLAG
149 ,A.MATCHING_SQL_FLAG
150 ,A.HIER_PROCESSING_FLAG)
151 VALUES (
152 jty_changed_dea_terrs_s.nextval
153 ,0
154 ,S.terr_id
155 ,S.source_id
156 ,'UPDATE'
157 ,'Y'
158 ,'N'
159 ,'N'
160 ,'N');
161 END IF; /* end IF (l_no_of_records = 0) */
162 END IF; /* end IF (n_rank <> o_rank) */
163
164 IF (o_num_winners is null and n_num_winners is not null) OR
165 (o_num_winners is not null and n_num_winners is null) OR
166 (o_num_winners <> n_num_winners) THEN
167 SELECT count(*)
168 INTO l_no_of_records
169 FROM jty_changed_terrs
170 WHERE terr_id = p_terr_id
171 AND change_type = 'CREATE'
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'
181 WHEN NOT MATCHED THEN
182 INSERT (
183 A.CHANGED_TERRITORY_ID
184 ,A.OBJECT_VERSION_NUMBER
185 ,A.TERR_ID
186 ,A.SOURCE_ID
187 ,A.CHANGE_TYPE
188 ,A.RANK_CALC_FLAG
189 ,A.PROCESS_ATTR_VALUES_FLAG
190 ,A.MATCHING_SQL_FLAG
191 ,A.HIER_PROCESSING_FLAG)
192 VALUES (
193 jty_changed_terrs_s.nextval
194 ,0
195 ,S.terr_id
196 ,S.source_id
197 ,'UPDATE'
198 ,'N'
199 ,'N'
200 ,'N'
201 ,'I');
202 END IF; /* end IF (l_no_of_records = 0) */
203
204 SELECT count(*)
205 INTO l_no_of_records
206 FROM jty_changed_dea_terrs
207 WHERE terr_id = p_terr_id
208 AND change_type = 'CREATE'
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'
218 WHEN NOT MATCHED THEN
219 INSERT (
220 A.CHANGED_TERRITORY_ID
221 ,A.OBJECT_VERSION_NUMBER
222 ,A.TERR_ID
223 ,A.SOURCE_ID
224 ,A.CHANGE_TYPE
225 ,A.RANK_CALC_FLAG
226 ,A.PROCESS_ATTR_VALUES_FLAG
227 ,A.MATCHING_SQL_FLAG
228 ,A.HIER_PROCESSING_FLAG)
229 VALUES (
230 jty_changed_dea_terrs_s.nextval
231 ,0
232 ,S.terr_id
233 ,S.source_id
234 ,'UPDATE'
235 ,'N'
236 ,'N'
237 ,'N'
238 ,'I');
239 END IF; /* end IF (l_no_of_records = 0) */
240 END IF; /* end IF (n_num_winners <> o_num_winners) */
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'
250 ,A.process_attr_values_flag = 'I'
251 ,A.matching_sql_flag = 'Y'
252 ,A.hier_processing_flag = 'I'
253 WHEN NOT MATCHED THEN
254 INSERT (
255 A.CHANGED_TERRITORY_ID
256 ,A.OBJECT_VERSION_NUMBER
257 ,A.TERR_ID
258 ,A.SOURCE_ID
259 ,A.CHANGE_TYPE
260 ,A.RANK_CALC_FLAG
261 ,A.PROCESS_ATTR_VALUES_FLAG
262 ,A.MATCHING_SQL_FLAG
263 ,A.HIER_PROCESSING_FLAG)
264 VALUES (
265 jty_changed_terrs_s.nextval
266 ,0
267 ,S.terr_id
268 ,S.source_id
269 ,'UPDATE'
270 ,'Y'
271 ,'I'
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'
281 ,A.process_attr_values_flag = 'I'
282 ,A.matching_sql_flag = 'Y'
283 ,A.hier_processing_flag = 'I'
284 WHEN NOT MATCHED THEN
285 INSERT (
286 A.CHANGED_TERRITORY_ID
287 ,A.OBJECT_VERSION_NUMBER
288 ,A.TERR_ID
289 ,A.SOURCE_ID
290 ,A.CHANGE_TYPE
291 ,A.RANK_CALC_FLAG
292 ,A.PROCESS_ATTR_VALUES_FLAG
293 ,A.MATCHING_SQL_FLAG
294 ,A.HIER_PROCESSING_FLAG)
295 VALUES (
296 jty_changed_dea_terrs_s.nextval
297 ,0
298 ,S.terr_id
299 ,S.source_id
300 ,'UPDATE'
301 ,'Y'
302 ,'I'
303 ,'Y'
304 ,'I');
305 END IF; /* end IF (n_parent_territory_id <> o_parent_territory_id) */
306
307 IF ((o_end_date_active < sysdate and o_start_date_active < sysdate) OR
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'
317 ,A.process_attr_values_flag = 'I'
318 ,A.matching_sql_flag = 'Y'
319 ,A.hier_processing_flag = 'I'
320 WHEN NOT MATCHED THEN
321 INSERT (
322 A.CHANGED_TERRITORY_ID
323 ,A.OBJECT_VERSION_NUMBER
324 ,A.TERR_ID
325 ,A.SOURCE_ID
326 ,A.CHANGE_TYPE
327 ,A.RANK_CALC_FLAG
328 ,A.PROCESS_ATTR_VALUES_FLAG
329 ,A.MATCHING_SQL_FLAG
330 ,A.HIER_PROCESSING_FLAG)
331 VALUES (
332 jty_changed_terrs_s.nextval
333 ,0
334 ,S.terr_id
335 ,S.source_id
336 ,'UPDATE'
337 ,'Y'
338 ,'I'
339 ,'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'
349 ,A.process_attr_values_flag = 'I'
350 ,A.matching_sql_flag = 'Y'
351 ,A.hier_processing_flag = 'I'
352 WHEN NOT MATCHED THEN
353 INSERT (
354 A.CHANGED_TERRITORY_ID
355 ,A.OBJECT_VERSION_NUMBER
356 ,A.TERR_ID
357 ,A.SOURCE_ID
358 ,A.CHANGE_TYPE
359 ,A.RANK_CALC_FLAG
360 ,A.PROCESS_ATTR_VALUES_FLAG
361 ,A.MATCHING_SQL_FLAG
362 ,A.HIER_PROCESSING_FLAG)
363 VALUES (
364 jty_changed_dea_terrs_s.nextval
365 ,0
366 ,S.terr_id
367 ,S.source_id
368 ,'UPDATE'
369 ,'Y'
370 ,'I'
371 ,'Y'
372 ,'I');
373 END IF;
374 ELSIF (o_start_date_active < sysdate and o_end_date_active > sysdate) THEN
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'
384 ,A.process_attr_values_flag = 'D'
385 ,A.matching_sql_flag = 'Y'
386 ,A.hier_processing_flag = 'D'
387 WHEN NOT MATCHED THEN
388 INSERT (
389 A.CHANGED_TERRITORY_ID
390 ,A.OBJECT_VERSION_NUMBER
391 ,A.TERR_ID
392 ,A.SOURCE_ID
393 ,A.CHANGE_TYPE
394 ,A.RANK_CALC_FLAG
395 ,A.PROCESS_ATTR_VALUES_FLAG
396 ,A.MATCHING_SQL_FLAG
397 ,A.HIER_PROCESSING_FLAG)
398 VALUES (
399 jty_changed_terrs_s.nextval
400 ,0
401 ,S.terr_id
402 ,S.source_id
403 ,'UPDATE'
404 ,'N'
405 ,'D'
406 ,'Y'
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'
416 ,A.process_attr_values_flag = 'D'
417 ,A.matching_sql_flag = 'Y'
418 ,A.hier_processing_flag = 'D'
419 WHEN NOT MATCHED THEN
420 INSERT (
421 A.CHANGED_TERRITORY_ID
422 ,A.OBJECT_VERSION_NUMBER
423 ,A.TERR_ID
424 ,A.SOURCE_ID
425 ,A.CHANGE_TYPE
426 ,A.RANK_CALC_FLAG
427 ,A.PROCESS_ATTR_VALUES_FLAG
428 ,A.MATCHING_SQL_FLAG
429 ,A.HIER_PROCESSING_FLAG)
430 VALUES (
431 jty_changed_dea_terrs_s.nextval
432 ,0
433 ,S.terr_id
434 ,S.source_id
435 ,'UPDATE'
436 ,'N'
437 ,'D'
438 ,'Y'
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'
448 ,A.process_attr_values_flag = 'D'
449 ,A.matching_sql_flag = 'Y'
450 ,A.hier_processing_flag = 'D'
451 WHEN NOT MATCHED THEN
452 INSERT (
453 A.CHANGED_TERRITORY_ID
454 ,A.OBJECT_VERSION_NUMBER
455 ,A.TERR_ID
456 ,A.SOURCE_ID
457 ,A.CHANGE_TYPE
458 ,A.RANK_CALC_FLAG
459 ,A.PROCESS_ATTR_VALUES_FLAG
460 ,A.MATCHING_SQL_FLAG
461 ,A.HIER_PROCESSING_FLAG)
462 VALUES (
463 jty_changed_terrs_s.nextval
464 ,0
465 ,S.terr_id
466 ,S.source_id
467 ,'UPDATE'
468 ,'Y'
469 ,'I'
470 ,'Y'
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'
480 ,A.process_attr_values_flag = 'D'
481 ,A.matching_sql_flag = 'Y'
482 ,A.hier_processing_flag = 'D'
483 WHEN NOT MATCHED THEN
484 INSERT (
485 A.CHANGED_TERRITORY_ID
486 ,A.OBJECT_VERSION_NUMBER
487 ,A.TERR_ID
488 ,A.SOURCE_ID
489 ,A.CHANGE_TYPE
490 ,A.RANK_CALC_FLAG
491 ,A.PROCESS_ATTR_VALUES_FLAG
492 ,A.MATCHING_SQL_FLAG
493 ,A.HIER_PROCESSING_FLAG)
494 VALUES (
495 jty_changed_dea_terrs_s.nextval
496 ,0
497 ,S.terr_id
498 ,S.source_id
499 ,'UPDATE'
500 ,'Y'
501 ,'I'
502 ,'Y'
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'
512 ,A.process_attr_values_flag = 'D'
513 ,A.matching_sql_flag = 'Y'
514 ,A.hier_processing_flag = 'D'
515 WHEN NOT MATCHED THEN
516 INSERT (
517 A.CHANGED_TERRITORY_ID
518 ,A.OBJECT_VERSION_NUMBER
519 ,A.TERR_ID
520 ,A.SOURCE_ID
521 ,A.CHANGE_TYPE
522 ,A.RANK_CALC_FLAG
523 ,A.PROCESS_ATTR_VALUES_FLAG
524 ,A.MATCHING_SQL_FLAG
525 ,A.HIER_PROCESSING_FLAG)
526 VALUES (
527 jty_changed_dea_terrs_s.nextval
528 ,0
529 ,S.terr_id
530 ,S.source_id
531 ,'UPDATE'
532 ,'Y'
533 ,'I'
534 ,'Y'
535 ,'I');
536 END IF;
537 END IF;
538 ELSIF (Trigger_Mode = 'ON-DELETE') THEN
539
540 MERGE INTO jty_changed_terrs A
541 USING ( SELECT p_terr_id terr_id from dual ) S
542 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
543 WHEN MATCHED THEN
544 UPDATE SET
545 A.rank_calc_flag = 'N'
546 ,A.process_attr_values_flag = 'D'
547 ,A.matching_sql_flag = decode(o_named_acct_flag, 'Y', 'N', 'Y')
548 ,A.hier_processing_flag = 'D'
549 ,A.change_type = 'DELETE'
550 WHEN NOT MATCHED THEN
551 INSERT (
552 A.CHANGED_TERRITORY_ID
553 ,A.OBJECT_VERSION_NUMBER
554 ,A.TERR_ID
555 ,A.CHANGE_TYPE
556 ,A.RANK_CALC_FLAG
557 ,A.PROCESS_ATTR_VALUES_FLAG
558 ,A.MATCHING_SQL_FLAG
559 ,A.HIER_PROCESSING_FLAG)
560 VALUES (
561 jty_changed_terrs_s.nextval
562 ,0
563 ,S.terr_id
564 ,'DELETE'
565 ,'N'
566 ,'D'
567 ,decode(o_named_acct_flag, 'Y', 'N', 'Y')
568 ,'D');
569
570 MERGE INTO jty_changed_dea_terrs A
571 USING ( SELECT p_terr_id terr_id from dual ) S
572 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
573 WHEN MATCHED THEN
574 UPDATE SET
575 A.rank_calc_flag = 'N'
576 ,A.process_attr_values_flag = 'D'
577 ,A.matching_sql_flag = decode(o_named_acct_flag, 'Y', 'N', 'Y')
578 ,A.hier_processing_flag = 'D'
579 ,A.change_type = 'DELETE'
580 WHEN NOT MATCHED THEN
581 INSERT (
582 A.CHANGED_TERRITORY_ID
583 ,A.OBJECT_VERSION_NUMBER
584 ,A.TERR_ID
585 ,A.CHANGE_TYPE
586 ,A.RANK_CALC_FLAG
587 ,A.PROCESS_ATTR_VALUES_FLAG
588 ,A.MATCHING_SQL_FLAG
589 ,A.HIER_PROCESSING_FLAG)
590 VALUES (
591 jty_changed_dea_terrs_s.nextval
592 ,0
593 ,S.terr_id
594 ,'DELETE'
595 ,'N'
596 ,'D'
597 ,decode(o_named_acct_flag, 'Y', 'N', 'Y')
598 ,'D');
599 END IF; /* end IF (Trigger_Mode = 'ON-UPDATE') */
600
601 EXCEPTION
602 When OTHERS then
603 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Problems: ' || sqlerrm);
604 RAISE;
605 END Territory_Trigger_Handler;
606
607 --**************************************************************
608 -- Terr_Values_Trigger_Handler
609 --**************************************************************
610 PROCEDURE Terr_Values_Trigger_Handler(
611 p_terr_qual_id IN NUMBER)
612 IS
613 l_terr_id NUMBER;
614 l_source_id NUMBER;
615 l_change_type VARCHAR2(80);
616 l_process_attr_values_flag VARCHAR2(1);
617 BEGIN
618
619 BEGIN
620 SELECT terr_id
621 INTO l_terr_id
622 FROM jtf_terr_qual
623 WHERE terr_qual_id = p_terr_qual_id;
624 EXCEPTION
625 WHEN NO_DATA_FOUND then
626 -- this should never happen since terr_qual_id req'd in jtf_terr_values
627 -- and terr_id terr_qual_id required in jtf_terr_qual
628 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'terr_id does not exist for terr_value_id');
629 RAISE;
630 WHEN OTHERS then
631 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Error while fetching terr_id from terr_value_id: ' || sqlerrm);
632 RAISE;
633 END;
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;
643 END;
644
645 BEGIN
646 SELECT change_type,
647 process_attr_values_flag
648 INTO l_change_type,
649 l_process_attr_values_flag
650 FROM jty_changed_terrs
651 WHERE terr_id = l_terr_id
652 AND star_request_id IS NULL;
653
654 IF ((l_change_type = 'UPDATE') AND (l_process_attr_values_flag <> 'I')) THEN
655 UPDATE jty_changed_terrs
656 SET process_attr_values_flag = 'I'
657 WHERE terr_id = l_terr_id
658 AND star_request_id IS NULL;
659 END IF;
660 EXCEPTION
661 WHEN NO_DATA_FOUND THEN
662 INSERT INTO jty_changed_terrs (
663 CHANGED_TERRITORY_ID
664 ,OBJECT_VERSION_NUMBER
665 ,TERR_ID
666 ,SOURCE_ID
667 ,CHANGE_TYPE
668 ,RANK_CALC_FLAG
669 ,PROCESS_ATTR_VALUES_FLAG
670 ,MATCHING_SQL_FLAG
671 ,HIER_PROCESSING_FLAG)
672 VALUES (
673 jty_changed_terrs_s.nextval
674 ,0
675 ,l_terr_id
676 ,l_source_id
677 ,'UPDATE'
678 ,'N'
679 ,'I'
680 ,'N'
681 ,'N');
682
683 WHEN OTHERS THEN
684 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Error updating the record: ' || sqlerrm);
685 RAISE;
686 END;
687
688 BEGIN
689 SELECT change_type,
690 process_attr_values_flag
691 INTO l_change_type,
692 l_process_attr_values_flag
693 FROM jty_changed_dea_terrs
694 WHERE terr_id = l_terr_id
695 AND star_request_id IS NULL;
696
697 IF ((l_change_type = 'UPDATE') AND (l_process_attr_values_flag <> 'I')) THEN
698 UPDATE jty_changed_dea_terrs
699 SET process_attr_values_flag = 'I'
700 WHERE terr_id = l_terr_id
701 AND star_request_id IS NULL;
702 END IF;
703 EXCEPTION
704 WHEN NO_DATA_FOUND THEN
705 INSERT INTO jty_changed_dea_terrs (
706 CHANGED_TERRITORY_ID
707 ,OBJECT_VERSION_NUMBER
708 ,TERR_ID
709 ,SOURCE_ID
710 ,CHANGE_TYPE
711 ,RANK_CALC_FLAG
712 ,PROCESS_ATTR_VALUES_FLAG
713 ,MATCHING_SQL_FLAG
714 ,HIER_PROCESSING_FLAG)
715 VALUES (
716 jty_changed_dea_terrs_s.nextval
717 ,0
718 ,l_terr_id
719 ,l_source_id
720 ,'UPDATE'
721 ,'N'
722 ,'I'
723 ,'N'
724 ,'N');
725
726 WHEN OTHERS THEN
727 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Error updating the record: ' || sqlerrm);
728 RAISE;
729 END;
730
731
732 EXCEPTION
733 When OTHERS then
734 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Problems: ' || sqlerrm);
735 RAISE;
736
737 END Terr_Values_Trigger_Handler;
738
739 --**************************************************************
740 -- Terr_Rsc_Trigger_Handler
741 --**************************************************************
742 PROCEDURE Terr_Rsc_Trigger_Handler(
743 p_TERR_ID IN NUMBER)
744 IS
745 l_no_of_records NUMBER;
746 l_source_id NUMBER;
747
748 BEGIN
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;
758 END;
759
760 SELECT count(*)
761 INTO l_no_of_records
762 FROM jty_changed_terrs
763 WHERE terr_id = p_terr_id
764 AND tap_request_id IS NULL;
765
766 IF (l_no_of_records = 0) THEN
767 INSERT INTO jty_changed_terrs (
768 CHANGED_TERRITORY_ID
769 ,OBJECT_VERSION_NUMBER
770 ,TERR_ID
771 ,SOURCE_ID
772 ,CHANGE_TYPE
773 ,RANK_CALC_FLAG
774 ,PROCESS_ATTR_VALUES_FLAG
775 ,MATCHING_SQL_FLAG
776 ,HIER_PROCESSING_FLAG)
777 VALUES (
778 jty_changed_terrs_s.nextval
779 ,0
780 ,p_terr_id
781 ,l_source_id
782 ,'UPDATE'
783 ,'N'
784 ,'N'
785 ,'N'
786 ,'N');
787 END IF;
788
789 SELECT count(*)
790 INTO l_no_of_records
791 FROM jty_changed_dea_terrs
792 WHERE terr_id = p_terr_id
793 AND tap_request_id IS NULL;
794
795 IF (l_no_of_records = 0) THEN
796 INSERT INTO jty_changed_dea_terrs (
797 CHANGED_TERRITORY_ID
798 ,OBJECT_VERSION_NUMBER
799 ,TERR_ID
800 ,SOURCE_ID
801 ,CHANGE_TYPE
802 ,RANK_CALC_FLAG
803 ,PROCESS_ATTR_VALUES_FLAG
804 ,MATCHING_SQL_FLAG
805 ,HIER_PROCESSING_FLAG)
806 VALUES (
807 jty_changed_dea_terrs_s.nextval
808 ,0
809 ,p_terr_id
810 ,l_source_id
811 ,'UPDATE'
812 ,'N'
813 ,'N'
814 ,'N'
815 ,'N');
816 END IF;
817
818
819 EXCEPTION
820 When OTHERS then
821 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_BIUD-Handler', 'Problems: ' || sqlerrm);
822 RAISE;
823
824 END Terr_Rsc_Trigger_Handler;
825
826 --**************************************************************
827 -- Terr_QType_Trigger_Handler
828 --**************************************************************
829
830 PROCEDURE Terr_QType_Trigger_Handler(
831 p_terr_id IN NUMBER)
832 IS
833 l_change_type VARCHAR2(80);
834 l_process_attr_values_flag VARCHAR2(1);
835 l_source_id NUMBER;
836 BEGIN
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;
846 END;
847
848 BEGIN
849 SELECT change_type,
850 process_attr_values_flag
851 INTO l_change_type,
852 l_process_attr_values_flag
853 FROM jty_changed_terrs
854 WHERE terr_id = p_terr_id
855 AND star_request_id IS NULL;
856
857 IF ((l_change_type = 'UPDATE') AND (l_process_attr_values_flag <> 'I')) THEN
858 UPDATE jty_changed_terrs
859 SET process_attr_values_flag = 'I',
860 matching_sql_flag = 'Y'
861 WHERE terr_id = p_terr_id
862 AND star_request_id IS NULL;
863 END IF;
864 EXCEPTION
865 WHEN NO_DATA_FOUND THEN
866 INSERT INTO jty_changed_terrs (
867 CHANGED_TERRITORY_ID
868 ,OBJECT_VERSION_NUMBER
869 ,TERR_ID
870 ,SOURCE_ID
871 ,CHANGE_TYPE
872 ,RANK_CALC_FLAG
873 ,PROCESS_ATTR_VALUES_FLAG
874 ,MATCHING_SQL_FLAG
875 ,HIER_PROCESSING_FLAG)
876 VALUES (
877 jty_changed_terrs_s.nextval
878 ,0
879 ,p_terr_id
880 ,l_source_id
881 ,'UPDATE'
882 ,'N'
883 ,'I'
884 ,'Y'
885 ,'N');
886
887 WHEN OTHERS THEN
888 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QTYPE_BIUD-Handler', 'Error updating the record: ' || sqlerrm);
889 RAISE;
890 END;
891
892 BEGIN
893 SELECT change_type,
894 process_attr_values_flag
895 INTO l_change_type,
896 l_process_attr_values_flag
897 FROM jty_changed_dea_terrs
898 WHERE terr_id = p_terr_id
899 AND star_request_id IS NULL;
900
901 IF ((l_change_type = 'UPDATE') AND (l_process_attr_values_flag <> 'I')) THEN
902 UPDATE jty_changed_dea_terrs
903 SET process_attr_values_flag = 'I',
904 matching_sql_flag = 'Y'
905 WHERE terr_id = p_terr_id
906 AND star_request_id IS NULL;
907 END IF;
908 EXCEPTION
909 WHEN NO_DATA_FOUND THEN
910 INSERT INTO jty_changed_dea_terrs (
911 CHANGED_TERRITORY_ID
912 ,OBJECT_VERSION_NUMBER
913 ,TERR_ID
914 ,SOURCE_ID
915 ,CHANGE_TYPE
916 ,RANK_CALC_FLAG
917 ,PROCESS_ATTR_VALUES_FLAG
918 ,MATCHING_SQL_FLAG
919 ,HIER_PROCESSING_FLAG)
920 VALUES (
921 jty_changed_dea_terrs_s.nextval
922 ,0
923 ,p_terr_id
924 ,l_source_id
925 ,'UPDATE'
926 ,'N'
927 ,'I'
928 ,'Y'
929 ,'N');
930
931 WHEN OTHERS THEN
932 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QTYPE_BIUD-Handler', 'Error updating the record: ' || sqlerrm);
933 RAISE;
934 END;
935
936 EXCEPTION
937 When OTHERS then
938 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QTYPE_BIUD-Handler', 'Problems: ' || sqlerrm);
939 RAISE;
940
941 END Terr_QType_Trigger_Handler;
942
943 --**************************************************************
944 -- Terr_RscAccess_Trigger_Handler
945 --**************************************************************
946 PROCEDURE Terr_RscAccess_Trigger_Handler(
947 p_terr_rsc_id IN NUMBER)
948 IS
949 l_terr_id NUMBER;
950 l_no_of_records NUMBER;
951 l_source_id NUMBER;
952
953 BEGIN
954
955 BEGIN
956 Select terr_id
957 into l_terr_id
958 from jtf_terr_rsc_all
959 where terr_rsc_id = p_terr_rsc_id;
960 EXCEPTION
961 When OTHERS then
962 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_BIUD-Handler', 'Error getting terr_id: ' || sqlerrm);
963 RAISE;
964 End;
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;
974 END;
975
976 SELECT count(*)
977 INTO l_no_of_records
978 FROM jty_changed_terrs
979 WHERE terr_id = l_terr_id
980 AND tap_request_id IS NULL;
981
982 IF (l_no_of_records = 0) THEN
983 INSERT INTO jty_changed_terrs (
984 CHANGED_TERRITORY_ID
985 ,OBJECT_VERSION_NUMBER
986 ,TERR_ID
987 ,SOURCE_ID
988 ,CHANGE_TYPE
989 ,RANK_CALC_FLAG
990 ,PROCESS_ATTR_VALUES_FLAG
991 ,MATCHING_SQL_FLAG
992 ,HIER_PROCESSING_FLAG)
993 VALUES (
994 jty_changed_terrs_s.nextval
995 ,0
996 ,l_terr_id
997 ,l_source_id
998 ,'UPDATE'
999 ,'N'
1000 ,'N'
1001 ,'N'
1002 ,'N');
1003 END IF;
1004
1005 SELECT count(*)
1006 INTO l_no_of_records
1007 FROM jty_changed_dea_terrs
1008 WHERE terr_id = l_terr_id
1009 AND tap_request_id IS NULL;
1010
1011 IF (l_no_of_records = 0) THEN
1012 INSERT INTO jty_changed_dea_terrs (
1013 CHANGED_TERRITORY_ID
1014 ,OBJECT_VERSION_NUMBER
1015 ,TERR_ID
1016 ,SOURCE_ID
1017 ,CHANGE_TYPE
1018 ,RANK_CALC_FLAG
1019 ,PROCESS_ATTR_VALUES_FLAG
1020 ,MATCHING_SQL_FLAG
1021 ,HIER_PROCESSING_FLAG)
1022 VALUES (
1023 jty_changed_dea_terrs_s.nextval
1024 ,0
1025 ,l_terr_id
1026 ,l_source_id
1027 ,'UPDATE'
1028 ,'N'
1029 ,'N'
1030 ,'N'
1031 ,'N');
1032 END IF;
1033
1034
1035 EXCEPTION
1036 When OTHERS then
1037 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_ACCESS_BIUD-Handler', 'Problems: ' || sqlerrm);
1038 RAISE;
1039 END Terr_RscAccess_Trigger_Handler;
1040
1041
1042 PROCEDURE Terr_Qual_Trigger_Handler(
1043 p_terr_id IN NUMBER)
1044 IS
1045 l_change_type VARCHAR2(80);
1046 l_matching_sql_flag VARCHAR2(1);
1047 l_source_id NUMBER;
1048 BEGIN
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;
1058 END;
1059
1060 BEGIN
1061 SELECT change_type,
1062 matching_sql_flag
1063 INTO l_change_type,
1064 l_matching_sql_flag
1065 FROM jty_changed_terrs
1066 WHERE terr_id = p_terr_id
1067 AND star_request_id IS NULL;
1068
1069 IF ((l_change_type = 'UPDATE') AND (l_matching_sql_flag <> 'Y')) THEN
1070 UPDATE jty_changed_terrs
1071 SET matching_sql_flag = 'Y'
1072 WHERE terr_id = p_terr_id
1073 AND star_request_id IS NULL;
1074 END IF;
1075 EXCEPTION
1076 WHEN NO_DATA_FOUND THEN
1077 INSERT INTO jty_changed_terrs (
1078 CHANGED_TERRITORY_ID
1079 ,OBJECT_VERSION_NUMBER
1080 ,TERR_ID
1081 ,SOURCE_ID
1082 ,CHANGE_TYPE
1083 ,RANK_CALC_FLAG
1084 ,PROCESS_ATTR_VALUES_FLAG
1085 ,MATCHING_SQL_FLAG
1086 ,HIER_PROCESSING_FLAG)
1087 VALUES (
1088 jty_changed_terrs_s.nextval
1089 ,0
1090 ,p_terr_id
1091 ,l_source_id
1092 ,'UPDATE'
1093 ,'N'
1094 ,'N'
1095 ,'Y'
1096 ,'N');
1097
1098 WHEN OTHERS THEN
1099 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QUAL_BIUD-Handler', 'Error updating the record: ' || sqlerrm);
1100 RAISE;
1101 END;
1102
1103 BEGIN
1104 SELECT change_type,
1105 matching_sql_flag
1106 INTO l_change_type,
1107 l_matching_sql_flag
1108 FROM jty_changed_dea_terrs
1109 WHERE terr_id = p_terr_id
1110 AND star_request_id IS NULL;
1111
1112 IF ((l_change_type = 'UPDATE') AND (l_matching_sql_flag <> 'Y')) THEN
1113 UPDATE jty_changed_dea_terrs
1114 SET matching_sql_flag = 'Y'
1115 WHERE terr_id = p_terr_id
1116 AND star_request_id IS NULL;
1117 END IF;
1118 EXCEPTION
1119 WHEN NO_DATA_FOUND THEN
1120 INSERT INTO jty_changed_dea_terrs (
1121 CHANGED_TERRITORY_ID
1122 ,OBJECT_VERSION_NUMBER
1123 ,TERR_ID
1124 ,SOURCE_ID
1125 ,CHANGE_TYPE
1126 ,RANK_CALC_FLAG
1127 ,PROCESS_ATTR_VALUES_FLAG
1128 ,MATCHING_SQL_FLAG
1129 ,HIER_PROCESSING_FLAG)
1130 VALUES (
1131 jty_changed_dea_terrs_s.nextval
1132 ,0
1133 ,p_terr_id
1134 ,l_source_id
1135 ,'UPDATE'
1136 ,'N'
1137 ,'N'
1138 ,'Y'
1139 ,'N');
1140
1141 WHEN OTHERS THEN
1142 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QUAL_BIUD-Handler', 'Error updating the record: ' || sqlerrm);
1143 RAISE;
1144 END;
1145
1146 EXCEPTION
1147 When OTHERS then
1148 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QUAL_BIUD-Handler', 'Problems: ' || sqlerrm);
1149 RAISE;
1150
1151 END Terr_Qual_Trigger_Handler;
1152
1153
1154 PROCEDURE Terr_Usgs_Trigger_Handler(
1155 p_terr_id IN NUMBER,
1156 p_source_id IN NUMBER,
1157 triggering_mode IN VARCHAR2)
1158 IS
1159 BEGIN
1160 IF (triggering_mode = 'ON-INSERT') THEN
1161 MERGE INTO jty_changed_terrs A
1162 USING ( SELECT p_terr_id terr_id, p_source_id source_id from dual ) S
1163 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
1164 WHEN MATCHED THEN
1165 UPDATE SET
1166 A.source_id = S.source_id
1167 WHEN NOT MATCHED THEN
1168 INSERT (
1169 A.CHANGED_TERRITORY_ID
1170 ,A.OBJECT_VERSION_NUMBER
1171 ,A.TERR_ID
1172 ,A.SOURCE_ID
1173 ,A.CHANGE_TYPE
1174 ,A.RANK_CALC_FLAG
1175 ,A.PROCESS_ATTR_VALUES_FLAG
1176 ,A.MATCHING_SQL_FLAG
1177 ,A.HIER_PROCESSING_FLAG)
1178 VALUES (
1179 jty_changed_terrs_s.nextval
1180 ,0
1181 ,S.terr_id
1182 ,S.source_id
1183 ,'CREATE'
1184 ,'Y'
1185 ,'I'
1186 ,'Y'
1187 ,'I');
1188
1189 MERGE INTO jty_changed_dea_terrs A
1190 USING ( SELECT p_terr_id terr_id, p_source_id source_id from dual ) S
1191 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
1192 WHEN MATCHED THEN
1193 UPDATE SET
1194 A.source_id = S.source_id
1195 WHEN NOT MATCHED THEN
1196 INSERT (
1197 A.CHANGED_TERRITORY_ID
1198 ,A.OBJECT_VERSION_NUMBER
1199 ,A.TERR_ID
1200 ,A.SOURCE_ID
1201 ,A.CHANGE_TYPE
1202 ,A.RANK_CALC_FLAG
1203 ,A.PROCESS_ATTR_VALUES_FLAG
1204 ,A.MATCHING_SQL_FLAG
1205 ,A.HIER_PROCESSING_FLAG)
1206 VALUES (
1207 jty_changed_dea_terrs_s.nextval
1208 ,0
1209 ,S.terr_id
1210 ,S.source_id
1211 ,'CREATE'
1212 ,'Y'
1213 ,'I'
1214 ,'Y'
1215 ,'I');
1216 ELSIF (triggering_mode = 'ON-DELETE') THEN
1217 MERGE INTO jty_changed_terrs A
1218 USING ( SELECT p_terr_id terr_id, p_source_id source_id from dual ) S
1219 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
1220 WHEN MATCHED THEN
1221 UPDATE SET
1222 A.source_id = S.source_id
1223 WHEN NOT MATCHED THEN
1224 INSERT (
1225 A.CHANGED_TERRITORY_ID
1226 ,A.OBJECT_VERSION_NUMBER
1227 ,A.TERR_ID
1228 ,A.SOURCE_ID
1229 ,A.CHANGE_TYPE
1230 ,A.RANK_CALC_FLAG
1231 ,A.PROCESS_ATTR_VALUES_FLAG
1232 ,A.MATCHING_SQL_FLAG
1233 ,A.HIER_PROCESSING_FLAG)
1234 VALUES (
1235 jty_changed_terrs_s.nextval
1236 ,0
1237 ,S.terr_id
1238 ,S.source_id
1239 ,'DELETE'
1240 ,'N'
1241 ,'D'
1242 ,'Y'
1243 ,'D');
1244
1245 MERGE INTO jty_changed_dea_terrs A
1246 USING ( SELECT p_terr_id terr_id, p_source_id source_id from dual ) S
1247 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
1248 WHEN MATCHED THEN
1249 UPDATE SET
1250 A.source_id = S.source_id
1251 WHEN NOT MATCHED THEN
1252 INSERT (
1253 A.CHANGED_TERRITORY_ID
1254 ,A.OBJECT_VERSION_NUMBER
1255 ,A.TERR_ID
1256 ,A.SOURCE_ID
1257 ,A.CHANGE_TYPE
1258 ,A.RANK_CALC_FLAG
1259 ,A.PROCESS_ATTR_VALUES_FLAG
1260 ,A.MATCHING_SQL_FLAG
1261 ,A.HIER_PROCESSING_FLAG)
1262 VALUES (
1263 jty_changed_dea_terrs_s.nextval
1264 ,0
1265 ,S.terr_id
1266 ,S.source_id
1267 ,'DELETE'
1268 ,'N'
1269 ,'D'
1270 ,'Y'
1271 ,'D');
1272 END IF;
1273
1274 EXCEPTION
1275 When OTHERS then
1276 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_USGS_BIUD-Handler', 'Problems: ' || sqlerrm);
1277 RAISE;
1278
1279 END Terr_Usgs_Trigger_Handler;
1280
1281 END JTY_TERR_TRIGGER_HANDLERS;