[Home] [Help]
PACKAGE BODY: APPS.JTY_TERR_TRIGGER_HANDLERS
Source
1 PACKAGE BODY JTY_TERR_TRIGGER_HANDLERS as
2 /* $Header: jtfyrhdb.pls 120.1 2006/03/30 17:39:44 achanda noship $ */
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 END IF;
65
66 ELSIF (Trigger_Mode = 'ON-UPDATE') THEN
67 IF (o_rank is null and n_rank is not null) OR
68 (o_rank is not null and n_rank is null) OR
69 (o_rank <> n_rank) THEN
70 SELECT count(*)
71 INTO l_no_of_records
72 FROM jty_changed_terrs
73 WHERE terr_id = p_terr_id
74 AND change_type = 'CREATE'
75 AND star_request_id IS NULL;
76
77 IF (l_no_of_records = 0) THEN
78 MERGE INTO jty_changed_terrs A
79 USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
80 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
81 WHEN MATCHED THEN
82 UPDATE SET
83 A.rank_calc_flag = 'Y'
84 WHEN NOT MATCHED THEN
85 INSERT (
86 A.CHANGED_TERRITORY_ID
87 ,A.OBJECT_VERSION_NUMBER
88 ,A.TERR_ID
89 ,A.SOURCE_ID
90 ,A.CHANGE_TYPE
91 ,A.RANK_CALC_FLAG
92 ,A.PROCESS_ATTR_VALUES_FLAG
93 ,A.MATCHING_SQL_FLAG
94 ,A.HIER_PROCESSING_FLAG)
95 VALUES (
96 jty_changed_terrs_s.nextval
97 ,0
98 ,S.terr_id
99 ,S.source_id
100 ,'UPDATE'
101 ,'Y'
102 ,'N'
103 ,'N'
104 ,'N');
105 END IF; /* end IF (l_no_of_records = 0) */
106 END IF; /* end IF (n_rank <> o_rank) */
107
108 IF (o_num_winners is null and n_num_winners is not null) OR
109 (o_num_winners is not null and n_num_winners is null) OR
110 (o_num_winners <> n_num_winners) THEN
111 SELECT count(*)
112 INTO l_no_of_records
113 FROM jty_changed_terrs
114 WHERE terr_id = p_terr_id
115 AND change_type = 'CREATE'
116 AND star_request_id IS NULL;
117
118 IF (l_no_of_records = 0) THEN
119 MERGE INTO jty_changed_terrs A
120 USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
121 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
122 WHEN MATCHED THEN
123 UPDATE SET
124 A.HIER_PROCESSING_FLAG = 'I'
125 WHEN NOT MATCHED THEN
126 INSERT (
127 A.CHANGED_TERRITORY_ID
128 ,A.OBJECT_VERSION_NUMBER
129 ,A.TERR_ID
130 ,A.SOURCE_ID
131 ,A.CHANGE_TYPE
132 ,A.RANK_CALC_FLAG
133 ,A.PROCESS_ATTR_VALUES_FLAG
134 ,A.MATCHING_SQL_FLAG
135 ,A.HIER_PROCESSING_FLAG)
136 VALUES (
137 jty_changed_terrs_s.nextval
138 ,0
139 ,S.terr_id
140 ,S.source_id
141 ,'UPDATE'
142 ,'N'
143 ,'N'
144 ,'N'
145 ,'I');
146 END IF; /* end IF (l_no_of_records = 0) */
147 END IF; /* end IF (n_num_winners <> o_num_winners) */
148
149 IF (n_parent_territory_id <> o_parent_territory_id) THEN
150
151 MERGE INTO jty_changed_terrs A
152 USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
153 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
154 WHEN MATCHED THEN
155 UPDATE SET
156 A.rank_calc_flag = 'Y'
157 ,A.process_attr_values_flag = 'I'
158 ,A.matching_sql_flag = 'Y'
159 ,A.hier_processing_flag = 'I'
160 WHEN NOT MATCHED THEN
161 INSERT (
162 A.CHANGED_TERRITORY_ID
163 ,A.OBJECT_VERSION_NUMBER
164 ,A.TERR_ID
165 ,A.SOURCE_ID
166 ,A.CHANGE_TYPE
167 ,A.RANK_CALC_FLAG
168 ,A.PROCESS_ATTR_VALUES_FLAG
169 ,A.MATCHING_SQL_FLAG
170 ,A.HIER_PROCESSING_FLAG)
171 VALUES (
172 jty_changed_terrs_s.nextval
173 ,0
174 ,S.terr_id
175 ,S.source_id
176 ,'UPDATE'
177 ,'Y'
178 ,'I'
179 ,'Y'
180 ,'I');
181 END IF; /* end IF (n_parent_territory_id <> o_parent_territory_id) */
182
186 /* future or inactive territory has become active as a result of the change */
183 IF ((o_end_date_active < sysdate and o_start_date_active < sysdate) OR
184 (o_end_date_active > sysdate and o_start_date_active > sysdate)) THEN
185 IF (n_start_date_active < sysdate and n_end_date_active > sysdate) THEN
187 MERGE INTO jty_changed_terrs A
188 USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
189 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
190 WHEN MATCHED THEN
191 UPDATE SET
192 A.rank_calc_flag = 'Y'
193 ,A.process_attr_values_flag = 'I'
194 ,A.matching_sql_flag = 'Y'
195 ,A.hier_processing_flag = 'I'
196 WHEN NOT MATCHED THEN
197 INSERT (
198 A.CHANGED_TERRITORY_ID
199 ,A.OBJECT_VERSION_NUMBER
200 ,A.TERR_ID
201 ,A.SOURCE_ID
202 ,A.CHANGE_TYPE
203 ,A.RANK_CALC_FLAG
204 ,A.PROCESS_ATTR_VALUES_FLAG
205 ,A.MATCHING_SQL_FLAG
206 ,A.HIER_PROCESSING_FLAG)
207 VALUES (
208 jty_changed_terrs_s.nextval
209 ,0
210 ,S.terr_id
211 ,S.source_id
212 ,'UPDATE'
213 ,'Y'
214 ,'I'
215 ,'Y'
216 ,'I');
217 END IF;
218 ELSIF (o_start_date_active < sysdate and o_end_date_active > sysdate) THEN
219 IF ((n_end_date_active < sysdate and n_start_date_active < sysdate) OR
220 (n_end_date_active > sysdate and n_start_date_active > sysdate)) THEN
221 /* active territory has become future or inactive as a resukt of the change */
222 MERGE INTO jty_changed_terrs A
223 USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
224 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
225 WHEN MATCHED THEN
226 UPDATE SET
227 A.rank_calc_flag = 'N'
228 ,A.process_attr_values_flag = 'D'
229 ,A.matching_sql_flag = 'Y'
230 ,A.hier_processing_flag = 'D'
231 WHEN NOT MATCHED THEN
232 INSERT (
233 A.CHANGED_TERRITORY_ID
234 ,A.OBJECT_VERSION_NUMBER
235 ,A.TERR_ID
236 ,A.SOURCE_ID
237 ,A.CHANGE_TYPE
238 ,A.RANK_CALC_FLAG
239 ,A.PROCESS_ATTR_VALUES_FLAG
240 ,A.MATCHING_SQL_FLAG
241 ,A.HIER_PROCESSING_FLAG)
242 VALUES (
243 jty_changed_terrs_s.nextval
244 ,0
245 ,S.terr_id
246 ,S.source_id
247 ,'UPDATE'
248 ,'N'
249 ,'D'
250 ,'Y'
251 ,'D');
252 END IF;
253 END IF;
254 ELSIF (Trigger_Mode = 'ON-DELETE') THEN
255
256 MERGE INTO jty_changed_terrs A
257 USING ( SELECT p_terr_id terr_id from dual ) S
258 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
259 WHEN MATCHED THEN
260 UPDATE SET
261 A.rank_calc_flag = 'N'
262 ,A.process_attr_values_flag = 'D'
263 ,A.matching_sql_flag = decode(o_named_acct_flag, 'Y', 'N', 'Y')
264 ,A.hier_processing_flag = 'D'
265 ,A.change_type = 'DELETE'
266 WHEN NOT MATCHED THEN
267 INSERT (
268 A.CHANGED_TERRITORY_ID
269 ,A.OBJECT_VERSION_NUMBER
270 ,A.TERR_ID
271 ,A.CHANGE_TYPE
272 ,A.RANK_CALC_FLAG
273 ,A.PROCESS_ATTR_VALUES_FLAG
274 ,A.MATCHING_SQL_FLAG
275 ,A.HIER_PROCESSING_FLAG)
276 VALUES (
277 jty_changed_terrs_s.nextval
278 ,0
279 ,S.terr_id
280 ,'DELETE'
281 ,'N'
282 ,'D'
283 ,decode(o_named_acct_flag, 'Y', 'N', 'Y')
284 ,'D');
285 END IF; /* end IF (Trigger_Mode = 'ON-UPDATE') */
286
287 EXCEPTION
288 When OTHERS then
289 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Problems: ' || sqlerrm);
290 RAISE;
291 END Territory_Trigger_Handler;
292
293 --**************************************************************
294 -- Terr_Values_Trigger_Handler
295 --**************************************************************
296 PROCEDURE Terr_Values_Trigger_Handler(
297 p_terr_qual_id IN NUMBER)
298 IS
299 l_terr_id NUMBER;
300 l_source_id NUMBER;
301 l_change_type VARCHAR2(80);
302 l_process_attr_values_flag VARCHAR2(1);
303 BEGIN
304
305 BEGIN
306 SELECT terr_id
307 INTO l_terr_id
308 FROM jtf_terr_qual
309 WHERE terr_qual_id = p_terr_qual_id;
310 EXCEPTION
311 WHEN NO_DATA_FOUND then
312 -- this should never happen since terr_qual_id req'd in jtf_terr_values
313 -- and terr_id terr_qual_id required in jtf_terr_qual
314 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'terr_id does not exist for terr_value_id');
315 RAISE;
316 WHEN OTHERS then
317 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Error while fetching terr_id from terr_value_id: ' || sqlerrm);
318 RAISE;
319 END;
320
321 BEGIN
322 SELECT source_id
323 INTO l_source_id
324 FROM jtf_terr_usgs_all
325 WHERE terr_id = l_terr_id;
326 EXCEPTION
327 WHEN OTHERS then
328 NULL;
329 END;
330
331 BEGIN
332 SELECT change_type,
333 process_attr_values_flag
334 INTO l_change_type,
335 l_process_attr_values_flag
336 FROM jty_changed_terrs
337 WHERE terr_id = l_terr_id
338 AND star_request_id IS NULL;
339
340 IF ((l_change_type = 'UPDATE') AND (l_process_attr_values_flag <> 'I')) THEN
341 UPDATE jty_changed_terrs
342 SET process_attr_values_flag = 'I'
343 WHERE terr_id = l_terr_id
344 AND star_request_id IS NULL;
345 END IF;
346 EXCEPTION
347 WHEN NO_DATA_FOUND THEN
348 INSERT INTO jty_changed_terrs (
349 CHANGED_TERRITORY_ID
350 ,OBJECT_VERSION_NUMBER
351 ,TERR_ID
352 ,SOURCE_ID
353 ,CHANGE_TYPE
354 ,RANK_CALC_FLAG
355 ,PROCESS_ATTR_VALUES_FLAG
356 ,MATCHING_SQL_FLAG
357 ,HIER_PROCESSING_FLAG)
358 VALUES (
359 jty_changed_terrs_s.nextval
360 ,0
361 ,l_terr_id
362 ,l_source_id
363 ,'UPDATE'
364 ,'N'
365 ,'I'
366 ,'N'
367 ,'N');
368
369 WHEN OTHERS THEN
370 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Error updating the record: ' || sqlerrm);
371 RAISE;
372 END;
373
377 RAISE;
374 EXCEPTION
375 When OTHERS then
376 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Problems: ' || sqlerrm);
378
379 END Terr_Values_Trigger_Handler;
380
381 --**************************************************************
382 -- Terr_Rsc_Trigger_Handler
383 --**************************************************************
384 PROCEDURE Terr_Rsc_Trigger_Handler(
385 p_TERR_ID IN NUMBER)
386 IS
387 l_no_of_records NUMBER;
388 l_source_id NUMBER;
389
390 BEGIN
391
392 BEGIN
393 SELECT source_id
394 INTO l_source_id
395 FROM jtf_terr_usgs_all
396 WHERE terr_id = p_terr_id;
397 EXCEPTION
398 WHEN OTHERS then
399 NULL;
400 END;
401
402 SELECT count(*)
403 INTO l_no_of_records
404 FROM jty_changed_terrs
405 WHERE terr_id = p_terr_id
406 AND tap_request_id IS NULL;
407
408 IF (l_no_of_records = 0) THEN
409 INSERT INTO jty_changed_terrs (
410 CHANGED_TERRITORY_ID
411 ,OBJECT_VERSION_NUMBER
412 ,TERR_ID
413 ,SOURCE_ID
414 ,CHANGE_TYPE
415 ,RANK_CALC_FLAG
416 ,PROCESS_ATTR_VALUES_FLAG
417 ,MATCHING_SQL_FLAG
418 ,HIER_PROCESSING_FLAG)
419 VALUES (
420 jty_changed_terrs_s.nextval
421 ,0
422 ,p_terr_id
423 ,l_source_id
424 ,'UPDATE'
425 ,'N'
426 ,'N'
427 ,'N'
428 ,'N');
429 END IF;
430
431 EXCEPTION
432 When OTHERS then
433 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_BIUD-Handler', 'Problems: ' || sqlerrm);
434 RAISE;
435
436 END Terr_Rsc_Trigger_Handler;
437
438 --**************************************************************
439 -- Terr_QType_Trigger_Handler
440 --**************************************************************
441
442 PROCEDURE Terr_QType_Trigger_Handler(
443 p_terr_id IN NUMBER)
444 IS
445 l_change_type VARCHAR2(80);
446 l_process_attr_values_flag VARCHAR2(1);
447 l_source_id NUMBER;
448 BEGIN
449
450 BEGIN
451 SELECT source_id
452 INTO l_source_id
453 FROM jtf_terr_usgs_all
454 WHERE terr_id = p_terr_id;
455 EXCEPTION
456 WHEN OTHERS then
457 NULL;
458 END;
459
460 BEGIN
461 SELECT change_type,
462 process_attr_values_flag
463 INTO l_change_type,
464 l_process_attr_values_flag
465 FROM jty_changed_terrs
466 WHERE terr_id = p_terr_id
467 AND star_request_id IS NULL;
468
469 IF ((l_change_type = 'UPDATE') AND (l_process_attr_values_flag <> 'I')) THEN
470 UPDATE jty_changed_terrs
471 SET process_attr_values_flag = 'I',
472 matching_sql_flag = 'Y'
473 WHERE terr_id = p_terr_id
474 AND star_request_id IS NULL;
475 END IF;
476 EXCEPTION
477 WHEN NO_DATA_FOUND THEN
478 INSERT INTO jty_changed_terrs (
479 CHANGED_TERRITORY_ID
480 ,OBJECT_VERSION_NUMBER
481 ,TERR_ID
482 ,SOURCE_ID
483 ,CHANGE_TYPE
484 ,RANK_CALC_FLAG
485 ,PROCESS_ATTR_VALUES_FLAG
486 ,MATCHING_SQL_FLAG
487 ,HIER_PROCESSING_FLAG)
488 VALUES (
489 jty_changed_terrs_s.nextval
490 ,0
491 ,p_terr_id
492 ,l_source_id
493 ,'UPDATE'
494 ,'N'
495 ,'I'
496 ,'Y'
497 ,'N');
498
499 WHEN OTHERS THEN
500 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QTYPE_BIUD-Handler', 'Error updating the record: ' || sqlerrm);
501 RAISE;
502 END;
503
504 EXCEPTION
505 When OTHERS then
506 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QTYPE_BIUD-Handler', 'Problems: ' || sqlerrm);
507 RAISE;
508
509 END Terr_QType_Trigger_Handler;
510
511 --**************************************************************
512 -- Terr_RscAccess_Trigger_Handler
513 --**************************************************************
514 PROCEDURE Terr_RscAccess_Trigger_Handler(
515 p_terr_rsc_id IN NUMBER)
516 IS
517 l_terr_id NUMBER;
518 l_no_of_records NUMBER;
519 l_source_id NUMBER;
520
521 BEGIN
522
523 BEGIN
524 Select terr_id
525 into l_terr_id
526 from jtf_terr_rsc_all
527 where terr_rsc_id = p_terr_rsc_id;
528 EXCEPTION
529 When OTHERS then
530 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_BIUD-Handler', 'Error getting terr_id: ' || sqlerrm);
531 RAISE;
532 End;
533
534 BEGIN
535 SELECT source_id
536 INTO l_source_id
537 FROM jtf_terr_usgs_all
538 WHERE terr_id = l_terr_id;
539 EXCEPTION
540 WHEN OTHERS then
541 NULL;
542 END;
543
544 SELECT count(*)
545 INTO l_no_of_records
546 FROM jty_changed_terrs
547 WHERE terr_id = l_terr_id
548 AND tap_request_id IS NULL;
549
550 IF (l_no_of_records = 0) THEN
551 INSERT INTO jty_changed_terrs (
552 CHANGED_TERRITORY_ID
553 ,OBJECT_VERSION_NUMBER
554 ,TERR_ID
555 ,SOURCE_ID
556 ,CHANGE_TYPE
557 ,RANK_CALC_FLAG
558 ,PROCESS_ATTR_VALUES_FLAG
559 ,MATCHING_SQL_FLAG
560 ,HIER_PROCESSING_FLAG)
561 VALUES (
562 jty_changed_terrs_s.nextval
563 ,0
564 ,l_terr_id
565 ,l_source_id
566 ,'UPDATE'
567 ,'N'
568 ,'N'
569 ,'N'
570 ,'N');
571 END IF;
572
573 EXCEPTION
574 When OTHERS then
575 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_ACCESS_BIUD-Handler', 'Problems: ' || sqlerrm);
576 RAISE;
577 END Terr_RscAccess_Trigger_Handler;
578
579
580 PROCEDURE Terr_Qual_Trigger_Handler(
581 p_terr_id IN NUMBER)
582 IS
583 l_change_type VARCHAR2(80);
584 l_matching_sql_flag VARCHAR2(1);
585 l_source_id NUMBER;
586 BEGIN
587
588 BEGIN
589 SELECT source_id
590 INTO l_source_id
591 FROM jtf_terr_usgs_all
592 WHERE terr_id = p_terr_id;
596 END;
593 EXCEPTION
594 WHEN OTHERS then
595 NULL;
597
598 BEGIN
599 SELECT change_type,
600 matching_sql_flag
601 INTO l_change_type,
602 l_matching_sql_flag
603 FROM jty_changed_terrs
604 WHERE terr_id = p_terr_id
605 AND star_request_id IS NULL;
606
607 IF ((l_change_type = 'UPDATE') AND (l_matching_sql_flag <> 'Y')) THEN
608 UPDATE jty_changed_terrs
609 SET matching_sql_flag = 'Y'
610 WHERE terr_id = p_terr_id
611 AND star_request_id IS NULL;
612 END IF;
613 EXCEPTION
614 WHEN NO_DATA_FOUND THEN
615 INSERT INTO jty_changed_terrs (
616 CHANGED_TERRITORY_ID
617 ,OBJECT_VERSION_NUMBER
618 ,TERR_ID
619 ,SOURCE_ID
620 ,CHANGE_TYPE
621 ,RANK_CALC_FLAG
622 ,PROCESS_ATTR_VALUES_FLAG
623 ,MATCHING_SQL_FLAG
624 ,HIER_PROCESSING_FLAG)
625 VALUES (
626 jty_changed_terrs_s.nextval
627 ,0
628 ,p_terr_id
629 ,l_source_id
630 ,'UPDATE'
631 ,'N'
632 ,'N'
633 ,'Y'
634 ,'N');
635
636 WHEN OTHERS THEN
637 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QUAL_BIUD-Handler', 'Error updating the record: ' || sqlerrm);
638 RAISE;
639 END;
640
641 EXCEPTION
642 When OTHERS then
643 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QUAL_BIUD-Handler', 'Problems: ' || sqlerrm);
644 RAISE;
645
646 END Terr_Qual_Trigger_Handler;
647
648
649 PROCEDURE Terr_Usgs_Trigger_Handler(
650 p_terr_id IN NUMBER,
651 p_source_id IN NUMBER,
652 triggering_mode IN VARCHAR2)
653 IS
654 BEGIN
655 IF (triggering_mode = 'ON-INSERT') THEN
656 MERGE INTO jty_changed_terrs A
657 USING ( SELECT p_terr_id terr_id, p_source_id source_id from dual ) S
658 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
659 WHEN MATCHED THEN
660 UPDATE SET
661 A.source_id = S.source_id
662 WHEN NOT MATCHED THEN
663 INSERT (
664 A.CHANGED_TERRITORY_ID
665 ,A.OBJECT_VERSION_NUMBER
666 ,A.TERR_ID
667 ,A.SOURCE_ID
668 ,A.CHANGE_TYPE
669 ,A.RANK_CALC_FLAG
670 ,A.PROCESS_ATTR_VALUES_FLAG
671 ,A.MATCHING_SQL_FLAG
672 ,A.HIER_PROCESSING_FLAG)
673 VALUES (
674 jty_changed_terrs_s.nextval
675 ,0
676 ,S.terr_id
677 ,S.source_id
678 ,'CREATE'
679 ,'Y'
680 ,'I'
681 ,'Y'
682 ,'I');
683 ELSIF (triggering_mode = 'ON-DELETE') THEN
684 MERGE INTO jty_changed_terrs A
685 USING ( SELECT p_terr_id terr_id, p_source_id source_id from dual ) S
686 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
687 WHEN MATCHED THEN
688 UPDATE SET
689 A.source_id = S.source_id
690 WHEN NOT MATCHED THEN
691 INSERT (
692 A.CHANGED_TERRITORY_ID
693 ,A.OBJECT_VERSION_NUMBER
694 ,A.TERR_ID
695 ,A.SOURCE_ID
696 ,A.CHANGE_TYPE
697 ,A.RANK_CALC_FLAG
698 ,A.PROCESS_ATTR_VALUES_FLAG
699 ,A.MATCHING_SQL_FLAG
700 ,A.HIER_PROCESSING_FLAG)
701 VALUES (
702 jty_changed_terrs_s.nextval
703 ,0
704 ,S.terr_id
705 ,S.source_id
706 ,'DELETE'
707 ,'N'
708 ,'D'
709 ,'Y'
710 ,'D');
711 END IF;
712
713 EXCEPTION
714 When OTHERS then
715 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_USGS_BIUD-Handler', 'Problems: ' || sqlerrm);
716 RAISE;
717
718 END Terr_Usgs_Trigger_Handler;
719
720 END JTY_TERR_TRIGGER_HANDLERS;