[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_TRIGGER_HANDLERS
Source
1 PACKAGE BODY JTF_TERR_TRIGGER_HANDLERS as
2 /* $Header: jtftrhdb.pls 115.20 2002/12/18 01:00:32 jdochert ship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TERR_TRIGGER_HANDLERS
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- Joint task force core territory manager private api's.
10 -- This package is defines Territory Trigger handlers.
11 -- Trigger handler API Spec for TABLES:
12 -- JTF_TERR, JTF_TERR_VALUES, JTF_TERR_RSC, JTF_TERR_RSC_ACCESS, JTF_TERR_QTYPE_USGS
13 -- CODE HAS BEEN OPTIMIZED
14 --
15 -- Procedures:
16 -- (see below for specification)
17 --
18 -- NOTES
19 -- This package is available for use
20 -- HISTORY
21 -- 04/23/00 EIHSU Created
22 -- in between EIHSU UPDATED to include JTF_TERR_RSC
23 -- in between EIHSU UPDATED to include JTF_TERR_RSC_ACCESS, JTF_TERR_QTYPE_USGS
24 -- 09/07/00 EIHSU Optimized
25 -- 09/21/00 EIHSU BUG 1410995, also commented out qual_type_usg_id in update proc
26 -- 09/25/00 EIHSU EHN BUG 1410995 - changed_terr records deleted upon record deletion
27 -- add request_id is null to all sql's
28 -- 09/26/00 EIHSU EHN BUG 1410995 - ON-DELETE, sometimes add delete record, sometimes delete ON-INSERT record.
29 -- 09/26/00 EIHSU EHN BUG 1410995 - when deleting a territory changed_terr record
30 -- also delete all records relating to that terr
31 -- 10/04/00 EIHSU ENH BUG 1423245, and BUG 1423718
32 -- 10/04/00 EIHSU Only transactions for sales will be recorded.
33 -- 10/06/00 EIHSU TERR_PTY transaction: Old values also recorded.
34 -- End of Comments
35
36 --**************************************************************
37 -- Territory_Trigger_Handler
38 --**************************************************************
39 PROCEDURE Territory_Trigger_Handler (
40 p_terr_id NUMBER,
41 p_org_id NUMBER,
42 o_parent_territory_id NUMBER,
43 o_last_update_date DATE,
44 o_last_updated_by NUMBER,
45 o_creation_date DATE,
46 o_created_by NUMBER,
47 o_last_update_login VARCHAR2,
48 o_start_date_active DATE,
49 o_end_date_active DATE,
50 o_rank VARCHAR2,
51 o_update_flag VARCHAR2,
52 o_num_winners NUMBER,
53 n_parent_territory_id NUMBER,
54 n_last_update_date DATE,
55 n_last_updated_by NUMBER,
56 n_creation_date DATE,
57 n_created_by NUMBER,
58 n_last_update_login VARCHAR2,
59 n_start_date_active DATE,
60 n_end_date_active DATE,
61 n_rank VARCHAR2,
62 n_update_flag VARCHAR2,
63 n_num_winners NUMBER,
64 Trigger_Mode VARCHAR2
65 )
66 IS
67
68 Changed_Terr_rec_Exist Varchar2(30);
69 exist_terr_id NUMBER;
70 l_terr_id NUMBER;
71 l_source_id NUMBER;
72 NOT_SALES_TERR_CHANGE exception;
73
74 BEGIN
75
76 Changed_Terr_rec_Exist := 'False';
77
78 l_terr_id := p_terr_id;
79
80 -- l_terr_value_id := terr_value_id;
81 --l_parent_territory_id := o_parent_territory_id;
82
83 -- check source of current territory
84 -- only add / modify records in jtf_changed_terr all
85 BEGIN
86 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_VAL) terr_id = ' || l_terr_id);
87 Select source_id into l_source_id
88 from jtf_terr_usgs
89 where terr_id = l_terr_id
90 and source_id = -1001;
91 EXCEPTION
92 When NO_DATA_FOUND then
93 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_VAL) NOT A SALES_TERR_CHANGE, raising exception ');
94 raise NOT_SALES_TERR_CHANGE;
95 End;
96
97 -- CHECK IF CHANGED_TERR record already exist
98 -- Terr_id header change record
99 BEGIN
100 Select terr_id into exist_terr_id
101 from JTF_CHANGED_TERR
102 where terr_id = l_terr_id
103 and terr_rsc_id is null
104 and terr_qtype_usg_id is null
105 and terr_rsc_access_id is null
106 and terr_value_id is null
107 and request_id is null;
108 Changed_Terr_rec_Exist := 'True';
109 EXCEPTION
110 When NO_DATA_FOUND then
111 Changed_Terr_rec_Exist := 'False';
112 --dbms_output.put_line('JTDT- no data found in table');
113 exist_terr_id := NULL;
114 When OTHERS then
115 --dbms_output.put_line('JTDT- error while looking for existing records.');
116 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Error checking for existing terr record(1): ' || sqlerrm);
117 null;
118 End;
119
120 -- Write to JTF_CHANGED_TERR table in appropriate fasion
121 If Trigger_Mode = 'ON-INSERT' then
122 --dbms_output.put_line('Terr_Exist: ' || Terr_Exist);
123
124 If Changed_Terr_rec_Exist = 'True' then
125 --dbms_output.put_line('JTDT- Updating record to JTF_CHANGED_TERR');
126 -- TEST results:
127 -- This should nver happen
128 BEGIN
129 UPDATE JTF_CHANGED_TERR
130 SET -- terr_id = terr_id,
131 Action = 'UPDATE_RECORD',
132 Trigger_mode = 'ON-INSERT',
133 NEW_last_update_date = n_last_update_date,
134 NEW_last_updated_by = n_last_updated_by,
135 NEW_creation_date = n_creation_date,
136 NEW_created_by = n_created_by,
137 NEW_last_update_login = n_last_update_login,
138 NEW_start_date_active = n_start_date_active,
139 NEW_end_date_active = n_end_date_active,
140 NEW_rank = n_rank,
141 NEW_update_flag = n_update_flag,
142 NEW_num_winners = n_num_winners,
143 NEW_parent_territory_id = n_parent_territory_id
144 WHERE terr_id = l_terr_id
145 and terr_rsc_id is null
146 and terr_qtype_usg_id is null
147 and terr_rsc_access_id is null
148 and terr_value_id is null
149 and request_id is null;
150
151 EXCEPTION
152 When OTHERS then
153 --dbms_output.put_line('ON-INSERT/UPDATE-TERR: error updating record');
154 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Error updating record for inserting terr' || sqlerrm);
155 End;
156 Else -- Terr_Exist = False -- New Territory Added
157 -- TEST results:
158 BEGIN
159 --dbms_output.put_line('JTDT- Inserting record to JTF_CHANGED_TERR');
160 INSERT INTO JTF_CHANGED_TERR
161 (terr_id, org_id, Action, Trigger_mode, NEW_last_update_date, NEW_last_updated_by,
162 NEW_creation_date, NEW_created_by, NEW_last_update_login, NEW_start_date_active,
163 NEW_end_date_active, NEW_rank, NEW_update_flag, NEW_num_winners, NEW_parent_territory_id)
164 values
165 (l_terr_id, p_org_id, 'NEW_RECORD', 'ON-INSERT', n_last_update_date, n_last_updated_by,
166 n_creation_date, n_created_by, n_last_update_login, n_start_date_active,
167 n_end_date_active, n_rank, n_update_flag, n_num_winners, n_parent_territory_id);
168 EXCEPTION
169 When OTHERS then
170 --dbms_output.put_line('ON-INSERT/INSERT-TERR: error updating record');
171 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Error inserting record for inserting terr' || sqlerrm);
172 End;
173 End if;
174 elsif Trigger_Mode = 'ON-UPDATE' then
175 -- (Territory Modified) items that may change:
176 -- start_date_active, end_date_active, rank, update_flag
177 -- Build update string based on what has actually been modified
178 -- If something didn't get modified, we'd still have it in the new field, so forget this dynamic SQL generation
179 -- Execute_SQL_STRING := 'UPDATE JTF_CHANGED_TERR SET ';
180 If Changed_Terr_rec_Exist = 'True' then
181 BEGIN
182 UPDATE JTF_CHANGED_TERR
183 SET Action = 'UPDATED_RECORD',
184 Trigger_mode = 'ON-UPDATE',
185 -- OLD_last_update_date = o_last_update_date,
186 -- OLD_last_updated_by = o_last_updated_by,
187 -- OLD_creation_date = o_creation_date,
188 -- OLD_created_by = o_created_by,
189 -- OLD_last_update_login = o_last_update_login,
190 -- OLD_start_date_active = o_start_date_active,
191 -- OLD_end_date_active = o_end_date_active,
192 -- OLD_rank = o_rank,
193 -- OLD_update_flag = o_update_flag,
194 -- OLD_num_winners = o_num_winners,
195 -- OLD_parent_territory_id = o_parent_territory_id,
196 NEW_last_update_date = n_last_update_date,
197 NEW_last_updated_by = n_last_updated_by,
198 NEW_creation_date = n_creation_date,
199 NEW_created_by = n_created_by,
200 NEW_last_update_login = n_last_update_login,
201 NEW_start_date_active = n_start_date_active,
202 NEW_end_date_active = n_end_date_active,
203 NEW_rank = n_rank,
204 NEW_update_flag = n_update_flag,
205 NEW_num_winners = n_num_winners,
206 NEW_parent_territory_id = n_parent_territory_id
207 WHERE terr_id = l_terr_id
208 and terr_rsc_id is null
209 and terr_qtype_usg_id is null
210 and terr_rsc_access_id is null
211 and terr_value_id is null
212 and request_id is null;
213
214 EXCEPTION
215 When OTHERS then
216 --dbms_output.put_line('ON-UPDATE/UPDATE-TERR: error updating record');
217 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Error updating record for updating terr' || sqlerrm);
218 End;
219
220 Else -- Changed_Terr_rec_Exist = 'False'
221 -- New Territory Added
222 -- TEST results:
223 BEGIN
224 INSERT INTO JTF_CHANGED_TERR
225 (terr_id, org_id, Action, Trigger_mode,
226 OLD_last_update_date, OLD_last_updated_by,
227 OLD_creation_date, OLD_created_by, OLD_last_update_login, OLD_start_date_active,
228 OLD_end_date_active, OLD_rank, OLD_update_flag, OLD_num_winners, OLD_parent_territory_id,
229 NEW_last_update_date, NEW_last_updated_by,
230 NEW_creation_date, NEW_created_by, NEW_last_update_login, NEW_start_date_active,
231 NEW_end_date_active, NEW_rank, NEW_update_flag, NEW_num_winners, NEW_parent_territory_id)
232 values
233 (l_terr_id, p_org_id, 'NEW_RECORD', 'ON-UPDATE',
234 o_last_update_date, o_last_updated_by,
235 o_creation_date, o_created_by, o_last_update_login, o_start_date_active,
236 o_end_date_active, o_rank, o_update_flag, o_num_winners, o_parent_territory_id,
237 n_last_update_date, n_last_updated_by,
238 n_creation_date, n_created_by, n_last_update_login, n_start_date_active,
239 n_end_date_active, n_rank, n_update_flag, n_num_winners, n_parent_territory_id);
240 EXCEPTION
241 When OTHERS then
242 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Error inserting record for updating terr' || sqlerrm);
243 null;
244 End;
245 End if;
246 else -- Trigger_mode = 'ON-DELETE'
247 -- check if we even have anything to delete. -- Territory Deleted
248 If Changed_Terr_rec_Exist = 'True' then
249 -- This means that there exists either a INSERT or MODIFY changed_terr record.
250 -- We delete this record of the INSERT or MODIFY, it is no longer needed
251 BEGIN
252 DELETE from JTF_CHANGED_TERR
253 WHERE terr_id = l_terr_id
254 /* -- because we want to kill all records relating to this deleted territory
255 and terr_rsc_id is null
256 and terr_qtype_usg_id is null
257 and terr_rsc_access_id is null
258 and terr_value_id is null*/
259 and request_id is null;
260 EXCEPTION
261 When OTHERS then
262 null;
263 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Error deleting record for deleting terr' || sqlerrm);
264 End;
265 Else -- Changed_Terr_rec_Exist = 'False'
266 -- Now insert the jtf_changed_terr record documenting the deletion of this territory.
267 BEGIN
268 INSERT INTO JTF_CHANGED_TERR
269 (terr_id, org_id, Action, Trigger_mode, OLD_last_update_date, OLD_last_updated_by,
270 OLD_creation_date, OLD_created_by, OLD_last_update_login, OLD_start_date_active,
271 OLD_end_date_active, OLD_rank, OLD_update_flag, OLD_num_winners, OLD_parent_territory_id)
272 values
273 (l_terr_id, p_org_id, 'NEW_RECORD', 'ON-DELETE', o_last_update_date, o_last_updated_by,
274 o_creation_date, o_created_by, o_last_update_login, o_start_date_active,
275 o_end_date_active, o_rank, o_update_flag, o_num_winners, o_parent_territory_id);
276 EXCEPTION
277 When OTHERS then
278 null;
279 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Error inserting record for deleting terr' || sqlerrm);
280 End;
281 End if;
282 End if;
283 EXCEPTION
284 When NOT_SALES_TERR_CHANGE then
285 null;
286 When OTHERS then
287 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Problems: ' || sqlerrm);
288 null;
289 END Territory_Trigger_Handler;
290
291 --**************************************************************
292 -- Terr_Values_Trigger_Handler
293 --**************************************************************
294 PROCEDURE Terr_Values_Trigger_Handler(
295 P_TERR_VALUE_ID NUMBER,
296 P_ORG_ID NUMBER,
297 o_LAST_UPDATED_BY NUMBER,
298 o_LAST_UPDATE_DATE DATE,
299 o_CREATED_BY NUMBER,
300 o_CREATION_DATE DATE,
301 o_LAST_UPDATE_LOGIN NUMBER,
302 o_TERR_QUAL_ID NUMBER,
303 o_INCLUDE_FLAG VARCHAR2,
304 o_COMPARISON_OPERATOR VARCHAR2,
305 o_ID_USED_FLAG VARCHAR2,
306 o_LOW_VALUE_CHAR_ID NUMBER,
307 o_LOW_VALUE_CHAR VARCHAR2,
308 o_HIGH_VALUE_CHAR VARCHAR2,
309 o_LOW_VALUE_NUMBER NUMBER,
310 o_HIGH_VALUE_NUMBER NUMBER,
311 o_VALUE_SET NUMBER,
312 o_INTEREST_TYPE_ID NUMBER,
313 o_PRIMARY_INTEREST_CODE_ID NUMBER,
314 o_SECONDARY_INTEREST_CODE_ID NUMBER,
315 o_CURRENCY_CODE VARCHAR2,
316 n_LAST_UPDATED_BY NUMBER,
317 n_LAST_UPDATE_DATE DATE,
318 n_CREATED_BY NUMBER,
319 n_CREATION_DATE DATE,
320 n_LAST_UPDATE_LOGIN NUMBER,
321 n_TERR_QUAL_ID NUMBER,
322 n_INCLUDE_FLAG VARCHAR2,
323 n_COMPARISON_OPERATOR VARCHAR2,
324 n_ID_USED_FLAG VARCHAR2,
325 n_LOW_VALUE_CHAR_ID NUMBER,
326 n_LOW_VALUE_CHAR VARCHAR2,
327 n_HIGH_VALUE_CHAR VARCHAR2,
328 n_LOW_VALUE_NUMBER NUMBER,
329 n_HIGH_VALUE_NUMBER NUMBER,
330 n_VALUE_SET NUMBER,
331 n_INTEREST_TYPE_ID NUMBER,
332 n_PRIMARY_INTEREST_CODE_ID NUMBER,
333 n_SECONDARY_INTEREST_CODE_ID NUMBER,
334 n_CURRENCY_CODE VARCHAR2,
335 TRIGGER_MODE VARCHAR2
336 )
337 IS
338 Changed_Terr_Value_Rec_Exists VARCHAR2(30);
339 l_terr_id NUMBER;
340 l_terr_qual_id NUMBER;
341 l_terr_value_id NUMBER;
342 l_seeded_qualifier_id NUMBER;
343 exist_terr_id NUMBER;
344 exist_terr_value_id NUMBER;
345 l_source_id NUMBER;
346 NOT_SALES_TERR_CHANGE exception;
347
348 BEGIN
349 l_terr_id := null;
350
351 l_terr_value_id := p_terr_value_id;
352 l_seeded_qualifier_id := l_seeded_qualifier_id;
353
354 -- extracted terr_id, needed for jtf_changed_terr table
355 If TRIGGER_MODE = 'ON-DELETE' then
356 l_terr_qual_id := o_TERR_QUAL_ID;
357 Else
358 l_terr_qual_id := n_TERR_QUAL_ID;
359 End if;
360
361 --dbms_output.put_line('JTDT- l_terr_qual_id = ' || l_terr_qual_id);
362 --Get the territory_id for the values record
363 BEGIN
364 Select terr_id into l_terr_id
365 from jtf_terr_qual where terr_qual_id = l_terr_qual_id;
366 EXCEPTION
367 WHEN NO_DATA_FOUND then
368 --dbms_output.put_line('JTDT- No data found error with extracting terr_qual_id or terr_id: ' || sqlerrm);
369 -- this should never happen since terr_qual_id req'd in jtf_terr_values
370 -- and terr_id terr_qual_id required in jtf_terr_qual
371 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'terr_id does not exist for terr_value_id');
372 WHEN OTHERS then
373 --dbms_output.put_line('JTDT- Terr_val_trigger error with extracting terr_qual_id or terr_id: ' || sqlerrm);
374 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Error while fetching terr_id from terr_value_id: ' || sqlerrm);
375
376 End;
377 -- check source of current territory
378 -- only add / modify records in jtf_changed_terr all
379 BEGIN
380 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_VAL) terr_id = ' || l_terr_id);
381 Select source_id into l_source_id
382 from jtf_terr_usgs
383 where terr_id = l_terr_id
384 and source_id = -1001;
385 EXCEPTION
386 When NO_DATA_FOUND then
387 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_VAL) NOT A SALES_TERR_CHANGE, raising exception ');
388 raise NOT_SALES_TERR_CHANGE;
389 End;
390 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_VAL) SALES_TERR_CHANGE, updating changed terr table');
391
392 --dbms_output.put_line('JTDT- l_terr_id = ' || l_terr_id);
393 -- Check if CHANGED_TERR record already exists
394 BEGIN
395 Select terr_value_id into exist_terr_value_id
396 from JTF_CHANGED_TERR
397 where terr_rsc_id is null
398 and terr_qtype_usg_id is null
399 and terr_rsc_access_id is null
400 and terr_id = l_terr_id
401 and terr_value_id = l_terr_value_id
402 and request_id is null;
403
404 Changed_Terr_Value_Rec_Exists := 'True';
405 EXCEPTION
406 When NO_DATA_FOUND then
407 exist_terr_value_id := NULL;
408 Changed_Terr_Value_Rec_Exists := 'False';
409 --dbms_output.put_line('JTDT- no data found in table: ' || sqlerrm);
410 When OTHERS then
411 null;
412 --dbms_output.put_line('JTDT- error while looking for existing records: ' || sqlerrm);
413 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Error testing for existing record: ' || sqlerrm);
414 End;
415
416 -- Extract the terr_id for this terr_value_id
417 If Trigger_Mode = 'ON-INSERT' then
418 If Changed_Terr_Value_Rec_Exists = 'True' then
419 -- Update only -- This should never happen.
420 null;
421 else -- Changed_Terr_Value_Rec_Exists = 'False'
422 -- Insert record
423 BEGIN
424 INSERT INTO JTF_CHANGED_TERR
425 (TERR_ID, TERR_VALUE_ID, ORG_ID, ACTION, TRIGGER_MODE,
426 OLD_LAST_UPDATED_BY, OLD_LAST_UPDATE_DATE, OLD_CREATED_BY, OLD_CREATION_DATE,
427 OLD_LAST_UPDATE_LOGIN, OLD_TERR_QUAL_ID, OLD_INCLUDE_FLAG, OLD_COMPARISON_OPERATOR,
428 OLD_ID_USED_FLAG, OLD_LOW_VALUE_CHAR_ID, OLD_LOW_VALUE_CHAR, OLD_HIGH_VALUE_CHAR,
429 OLD_LOW_VALUE_NUMBER, OLD_HIGH_VALUE_NUMBER, OLD_VALUE_SET, OLD_INTEREST_TYPE_ID,
430 OLD_PRIMARY_INTEREST_CODE_ID, OLD_SECONDARY_INTEREST_CODE_ID, OLD_CURRENCY_CODE,
431 NEW_LAST_UPDATED_BY, NEW_LAST_UPDATE_DATE, NEW_CREATED_BY, NEW_CREATION_DATE,
432 NEW_LAST_UPDATE_LOGIN, NEW_TERR_QUAL_ID, NEW_INCLUDE_FLAG, NEW_COMPARISON_OPERATOR,
433 NEW_ID_USED_FLAG, NEW_LOW_VALUE_CHAR_ID, NEW_LOW_VALUE_CHAR, NEW_HIGH_VALUE_CHAR,
434 NEW_LOW_VALUE_NUMBER, NEW_HIGH_VALUE_NUMBER, NEW_VALUE_SET, NEW_INTEREST_TYPE_ID,
435 NEW_PRIMARY_INTEREST_CODE_ID, NEW_SECONDARY_INTEREST_CODE_ID, NEW_CURRENCY_CODE)
436 VALUES
437 (l_terr_id, l_terr_value_id, p_ORG_id, 'NEW_RECORD', 'ON-INSERT',
438 o_LAST_UPDATED_BY, o_LAST_UPDATE_DATE, o_CREATED_BY, o_CREATION_DATE,
439 o_LAST_UPDATE_LOGIN, o_TERR_QUAL_ID, o_INCLUDE_FLAG, o_COMPARISON_OPERATOR,
440 o_ID_USED_FLAG, o_LOW_VALUE_CHAR_ID, o_LOW_VALUE_CHAR, o_HIGH_VALUE_CHAR,
441 o_LOW_VALUE_NUMBER, o_HIGH_VALUE_NUMBER, o_VALUE_SET, o_INTEREST_TYPE_ID,
442 o_PRIMARY_INTEREST_CODE_ID, o_SECONDARY_INTEREST_CODE_ID, o_CURRENCY_CODE,
443 n_LAST_UPDATED_BY, n_LAST_UPDATE_DATE, n_CREATED_BY, n_CREATION_DATE,
444 n_LAST_UPDATE_LOGIN, n_TERR_QUAL_ID, n_INCLUDE_FLAG, n_COMPARISON_OPERATOR,
445 n_ID_USED_FLAG, n_LOW_VALUE_CHAR_ID, n_LOW_VALUE_CHAR, n_HIGH_VALUE_CHAR,
446 n_LOW_VALUE_NUMBER, n_HIGH_VALUE_NUMBER, n_VALUE_SET, n_INTEREST_TYPE_ID,
447 n_PRIMARY_INTEREST_CODE_ID, n_SECONDARY_INTEREST_CODE_ID, n_CURRENCY_CODE);
448 EXCEPTION
449 WHEN OTHERS THEN
450 --dbms_output.put_line('Failed at ON-INSERT, NEW_RECORD'|| sqlerrm);
451 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Error inserting record for inserting terr val' || sqlerrm);
452 END;
453 End if;
454 elsif Trigger_mode = 'ON-UPDATE' then
455 If Changed_Terr_Value_Rec_Exists = 'True' then
456 -- Update only
457 BEGIN
458 UPDATE JTF_CHANGED_TERR
459 SET -- TERR_ID cannot be changed, as it is primary key
460 -- TERR_VALUE_ID = l_terr_value_id,
461 ACTION = 'UPDATED_RECORD',
462 TRIGGER_MODE = 'ON-UPDATE',
463 -- OLD_LAST_UPDATED_BY = o_LAST_UPDATED_BY,
464 -- OLD_LAST_UPDATE_DATE = o_LAST_UPDATE_DATE,
465 -- OLD_CREATED_BY = o_CREATED_BY,
466 -- OLD_CREATION_DATE = o_CREATION_DATE,
467 -- OLD_LAST_UPDATE_LOGIN = o_LAST_UPDATE_LOGIN,
468 -- OLD_TERR_QUAL_ID = o_TERR_QUAL_ID,
469 -- OLD_INCLUDE_FLAG = o_INCLUDE_FLAG,
470 -- OLD_COMPARISON_OPERATOR = o_COMPARISON_OPERATOR,
471 -- OLD_ID_USED_FLAG = o_ID_USED_FLAG,
472 -- OLD_LOW_VALUE_CHAR_ID = o_LOW_VALUE_CHAR_ID,
473 -- OLD_LOW_VALUE_CHAR = o_LOW_VALUE_CHAR,
474 -- OLD_HIGH_VALUE_CHAR = o_HIGH_VALUE_CHAR,
475 -- OLD_LOW_VALUE_NUMBER = o_LOW_VALUE_NUMBER,
476 -- OLD_HIGH_VALUE_NUMBER = o_HIGH_VALUE_NUMBER,
477 -- OLD_VALUE_SET = o_VALUE_SET,
478 -- OLD_INTEREST_TYPE_ID = o_INTEREST_TYPE_ID,
479 -- OLD_PRIMARY_INTEREST_CODE_ID = o_PRIMARY_INTEREST_CODE_ID,
480 -- OLD_SECONDARY_INTEREST_CODE_ID = o_SECONDARY_INTEREST_CODE_ID,
481 -- OLD_CURRENCY_CODE = o_CURRENCY_CODE,
482 NEW_LAST_UPDATED_BY = n_LAST_UPDATED_BY,
483 NEW_LAST_UPDATE_DATE = n_LAST_UPDATE_DATE,
484 NEW_CREATED_BY = n_CREATED_BY,
485 NEW_CREATION_DATE = n_CREATION_DATE,
486 NEW_LAST_UPDATE_LOGIN = n_LAST_UPDATE_LOGIN,
487 NEW_TERR_QUAL_ID = n_TERR_QUAL_ID,
488 NEW_INCLUDE_FLAG = n_INCLUDE_FLAG,
489 NEW_COMPARISON_OPERATOR = n_COMPARISON_OPERATOR,
490 NEW_ID_USED_FLAG = n_ID_USED_FLAG,
491 NEW_LOW_VALUE_CHAR_ID = n_LOW_VALUE_CHAR_ID,
492 NEW_LOW_VALUE_CHAR = n_LOW_VALUE_CHAR,
493 NEW_HIGH_VALUE_CHAR = n_HIGH_VALUE_CHAR,
494 NEW_LOW_VALUE_NUMBER = n_LOW_VALUE_NUMBER,
495 NEW_HIGH_VALUE_NUMBER = n_HIGH_VALUE_NUMBER,
496 NEW_VALUE_SET = n_VALUE_SET,
497 NEW_INTEREST_TYPE_ID = n_INTEREST_TYPE_ID,
498 NEW_PRIMARY_INTEREST_CODE_ID = n_PRIMARY_INTEREST_CODE_ID,
499 NEW_SECONDARY_INTEREST_CODE_ID = n_SECONDARY_INTEREST_CODE_ID,
500 NEW_CURRENCY_CODE = n_CURRENCY_CODE
501 WHERE terr_rsc_id is null
502 and terr_qtype_usg_id is null
503 and terr_rsc_access_id is null
504 and TERR_VALUE_ID = l_TERR_VALUE_ID
505 and TERR_ID = l_terr_id
506 and request_id is null;
507 EXCEPTION
508 WHEN OTHERS THEN
509 --dbms_output.put_line('Failed at ON-UPDATE, UPDATED_RECORD'|| sqlerrm);
510 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Error while updating record for updating terr val: ' || sqlerrm);
511 END;
512 else
513 -- Changed_Terr_Value_Rec_Exists = 'False'
514 -- Insert record
515 BEGIN
516 INSERT INTO JTF_CHANGED_TERR
517 (TERR_ID, TERR_VALUE_ID, ACTION, TRIGGER_MODE,
518 OLD_LAST_UPDATED_BY, OLD_LAST_UPDATE_DATE, OLD_CREATED_BY, OLD_CREATION_DATE,
519 OLD_LAST_UPDATE_LOGIN, OLD_TERR_QUAL_ID, OLD_INCLUDE_FLAG, OLD_COMPARISON_OPERATOR,
520 OLD_ID_USED_FLAG, OLD_LOW_VALUE_CHAR_ID, OLD_LOW_VALUE_CHAR, OLD_HIGH_VALUE_CHAR,
521 OLD_LOW_VALUE_NUMBER, OLD_HIGH_VALUE_NUMBER, OLD_VALUE_SET, OLD_INTEREST_TYPE_ID,
522 OLD_PRIMARY_INTEREST_CODE_ID, OLD_SECONDARY_INTEREST_CODE_ID, OLD_CURRENCY_CODE,
523 NEW_LAST_UPDATED_BY, NEW_LAST_UPDATE_DATE, NEW_CREATED_BY, NEW_CREATION_DATE,
524 NEW_LAST_UPDATE_LOGIN, NEW_TERR_QUAL_ID, NEW_INCLUDE_FLAG, NEW_COMPARISON_OPERATOR,
525 NEW_ID_USED_FLAG, NEW_LOW_VALUE_CHAR_ID, NEW_LOW_VALUE_CHAR, NEW_HIGH_VALUE_CHAR,
526 NEW_LOW_VALUE_NUMBER, NEW_HIGH_VALUE_NUMBER, NEW_VALUE_SET, NEW_INTEREST_TYPE_ID,
527 NEW_PRIMARY_INTEREST_CODE_ID, NEW_SECONDARY_INTEREST_CODE_ID, NEW_CURRENCY_CODE, ORG_ID)
528 VALUES
529 (l_terr_id, l_terr_value_id,
530 'NEW_RECORD', 'ON-UPDATE',
531 o_LAST_UPDATED_BY, o_LAST_UPDATE_DATE, o_CREATED_BY, o_CREATION_DATE,
532 o_LAST_UPDATE_LOGIN, o_TERR_QUAL_ID, o_INCLUDE_FLAG, o_COMPARISON_OPERATOR,
533 o_ID_USED_FLAG, o_LOW_VALUE_CHAR_ID, o_LOW_VALUE_CHAR, o_HIGH_VALUE_CHAR,
534 o_LOW_VALUE_NUMBER, o_HIGH_VALUE_NUMBER, o_VALUE_SET, o_INTEREST_TYPE_ID,
535 o_PRIMARY_INTEREST_CODE_ID, o_SECONDARY_INTEREST_CODE_ID, o_CURRENCY_CODE,
536 n_LAST_UPDATED_BY, n_LAST_UPDATE_DATE, n_CREATED_BY, n_CREATION_DATE,
537 n_LAST_UPDATE_LOGIN, n_TERR_QUAL_ID, n_INCLUDE_FLAG, n_COMPARISON_OPERATOR,
538 n_ID_USED_FLAG, n_LOW_VALUE_CHAR_ID, n_LOW_VALUE_CHAR, n_HIGH_VALUE_CHAR,
539 n_LOW_VALUE_NUMBER, n_HIGH_VALUE_NUMBER, n_VALUE_SET, n_INTEREST_TYPE_ID,
540 n_PRIMARY_INTEREST_CODE_ID, n_SECONDARY_INTEREST_CODE_ID, n_CURRENCY_CODE, P_ORG_ID);
541 EXCEPTION
542 WHEN OTHERS THEN
543 --dbms_output.put_line('Failed at ON-UPDATE, NEW_RECORD'|| sqlerrm);
544 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Error while inserting record for updating terr val' || sqlerrm);
545 END;
546 End if;
547 else
548 -- Trigger_mode = 'ON-DELETE'
549 If Changed_Terr_Value_Rec_Exists = 'True' then
550 -- This means that there exists either a INSERT or MODIFY changed_terr record.
551 -- We delete this record of the INSERT or MODIFY, it is no longer needed
552 BEGIN
553 DELETE from JTF_CHANGED_TERR
554 WHERE terr_id = l_terr_id
555 and TERR_VALUE_ID = l_TERR_VALUE_ID
556 and terr_rsc_id is null
557 and terr_qtype_usg_id is null
558 and terr_rsc_access_id is null
559 and request_id is null;
560 EXCEPTION
561 When OTHERS then
562 null;
563 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Error deleting record for deleting terr value' || sqlerrm);
564 End;
565 else -- Changed_Terr_Value_Rec_Exists = 'False'
566 -- Insert record
567 BEGIN
568 INSERT INTO JTF_CHANGED_TERR
569 (TERR_ID, TERR_VALUE_ID, ACTION, TRIGGER_MODE,
570 OLD_LAST_UPDATED_BY, OLD_LAST_UPDATE_DATE, OLD_CREATED_BY, OLD_CREATION_DATE,
571 OLD_LAST_UPDATE_LOGIN, OLD_TERR_QUAL_ID, OLD_INCLUDE_FLAG, OLD_COMPARISON_OPERATOR,
572 OLD_ID_USED_FLAG, OLD_LOW_VALUE_CHAR_ID, OLD_LOW_VALUE_CHAR, OLD_HIGH_VALUE_CHAR,
573 OLD_LOW_VALUE_NUMBER, OLD_HIGH_VALUE_NUMBER, OLD_VALUE_SET, OLD_INTEREST_TYPE_ID,
574 OLD_PRIMARY_INTEREST_CODE_ID, OLD_SECONDARY_INTEREST_CODE_ID, OLD_CURRENCY_CODE,
575 NEW_LAST_UPDATED_BY, NEW_LAST_UPDATE_DATE, NEW_CREATED_BY, NEW_CREATION_DATE,
576 NEW_LAST_UPDATE_LOGIN, NEW_TERR_QUAL_ID, NEW_INCLUDE_FLAG, NEW_COMPARISON_OPERATOR,
577 NEW_ID_USED_FLAG, NEW_LOW_VALUE_CHAR_ID, NEW_LOW_VALUE_CHAR, NEW_HIGH_VALUE_CHAR,
578 NEW_LOW_VALUE_NUMBER, NEW_HIGH_VALUE_NUMBER, NEW_VALUE_SET, NEW_INTEREST_TYPE_ID,
579 NEW_PRIMARY_INTEREST_CODE_ID, NEW_SECONDARY_INTEREST_CODE_ID, NEW_CURRENCY_CODE, ORG_ID)
580 VALUES
581 (l_terr_id, l_terr_value_id,
582 'NEW_RECORD', 'ON-DELETE',
583 o_LAST_UPDATED_BY, o_LAST_UPDATE_DATE, o_CREATED_BY, o_CREATION_DATE,
584 o_LAST_UPDATE_LOGIN, o_TERR_QUAL_ID, o_INCLUDE_FLAG, o_COMPARISON_OPERATOR,
585 o_ID_USED_FLAG, o_LOW_VALUE_CHAR_ID, o_LOW_VALUE_CHAR, o_HIGH_VALUE_CHAR,
586 o_LOW_VALUE_NUMBER, o_HIGH_VALUE_NUMBER, o_VALUE_SET, o_INTEREST_TYPE_ID,
587 o_PRIMARY_INTEREST_CODE_ID, o_SECONDARY_INTEREST_CODE_ID, o_CURRENCY_CODE,
588 n_LAST_UPDATED_BY, n_LAST_UPDATE_DATE, n_CREATED_BY, n_CREATION_DATE,
589 n_LAST_UPDATE_LOGIN, n_TERR_QUAL_ID, n_INCLUDE_FLAG, n_COMPARISON_OPERATOR,
590 n_ID_USED_FLAG, n_LOW_VALUE_CHAR_ID, n_LOW_VALUE_CHAR, n_HIGH_VALUE_CHAR,
591 n_LOW_VALUE_NUMBER, n_HIGH_VALUE_NUMBER, n_VALUE_SET, n_INTEREST_TYPE_ID,
592 n_PRIMARY_INTEREST_CODE_ID, n_SECONDARY_INTEREST_CODE_ID, n_CURRENCY_CODE, P_ORG_ID);
593 EXCEPTION
594 WHEN OTHERS THEN
595 --dbms_output.put_line('Failed at ON-DELETE, NEW_RECORD' || sqlerrm);
596 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Error while inserting record for deleting terr val ' || sqlerrm);
597 END;
598 End if;
599 End if;
600 EXCEPTION
601 When NOT_SALES_TERR_CHANGE then
602 null;
603 When OTHERS then
604 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_VALUES_BIUD-Handler', 'Problems: ' || sqlerrm);
605 null;
606
607 END Terr_Values_Trigger_Handler;
608
609 --**************************************************************
610 -- Terr_Rsc_Trigger_Handler
611 --**************************************************************
612 PROCEDURE Terr_Rsc_Trigger_Handler(
613 p_TERR_RSC_ID NUMBER,
614 p_TERR_ID NUMBER,
615 p_ORG_ID NUMBER,
616 o_LAST_UPDATE_DATE DATE,
617 o_LAST_UPDATED_BY NUMBER,
618 o_CREATION_DATE DATE,
619 o_CREATED_BY NUMBER,
620 o_LAST_UPDATE_LOGIN NUMBER,
621 o_RESOURCE_ID NUMBER,
622 o_GROUP_ID NUMBER,
623 o_RESOURCE_TYPE VARCHAR2,
624 o_ROLE VARCHAR2,
625 o_PRIMARY_CONTACT_FLAG VARCHAR2,
626 o_START_DATE_ACTIVE DATE,
627 o_END_DATE_ACTIVE DATE,
628 o_FULL_ACCESS_FLAG VARCHAR2,
629 n_LAST_UPDATE_DATE DATE,
630 n_LAST_UPDATED_BY NUMBER,
631 n_CREATION_DATE DATE,
632 n_CREATED_BY NUMBER,
633 n_LAST_UPDATE_LOGIN NUMBER,
634 n_RESOURCE_ID NUMBER,
635 n_GROUP_ID NUMBER,
636 n_RESOURCE_TYPE VARCHAR2,
637 n_ROLE VARCHAR2,
638 n_PRIMARY_CONTACT_FLAG VARCHAR2,
639 n_START_DATE_ACTIVE DATE,
640 n_END_DATE_ACTIVE DATE,
641 n_FULL_ACCESS_FLAG VARCHAR2,
642 Trigger_Mode VARCHAR2
643 )
644 IS
645 l_terr_id NUMBER;
646 l_terr_rsc_id NUMBER;
647 Terr_Rsc_Rec_Exists VARCHAR2(30);
648 exist_terr_rsc_id NUMBER;
649 l_source_id NUMBER;
650 NOT_SALES_TERR_CHANGE exception;
651
652
653 BEGIN
654 l_terr_id := p_terr_id;
655 l_terr_rsc_id := p_terr_rsc_id;
656
657 -- check source of current territory
658 -- only add / modify records in jtf_changed_terr all
659 BEGIN
660 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_RSC) terr_id = ' || l_terr_id);
661 Select source_id into l_source_id
662 from jtf_terr_usgs
663 where terr_id = l_terr_id
664 and source_id = -1001;
665 EXCEPTION
666 When NO_DATA_FOUND then
667 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_RSC) NOT A SALES_TERR_CHANGE, raising exception ');
668 raise NOT_SALES_TERR_CHANGE;
669 End;
670 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_RSC) SALES_TERR_CHANGE, updating changed terr table');
671
672 -- check if terr_rsc record exists
673 BEGIN
674 Select terr_rsc_id into exist_terr_rsc_id
675 from JTF_CHANGED_TERR
676 where terr_value_id is null
677 and terr_qtype_usg_id is null
678 and terr_rsc_access_id is null
679 and terr_rsc_id = l_terr_rsc_id
680 and Terr_id = l_terr_id
681 and request_id is null;
682 Terr_Rsc_Rec_Exists := 'True';
683 EXCEPTION
684 When NO_DATA_FOUND then
685 exist_terr_rsc_id := NULL;
686 Terr_Rsc_Rec_Exists := 'False';
687 When OTHERS then
688 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_BIUD-Handler', 'Error testing for existing record: ' || sqlerrm);
689 End;
690
691 If Trigger_Mode = 'ON-INSERT' then
692 If Terr_Rsc_Rec_Exists = 'True' then
693 -- Update only -- This should never happen.
694 null;
695 else -- Changed_Terr_Value_Rec_Exists = 'False'
696 -- Insert record
697 BEGIN
698 INSERT INTO JTF_CHANGED_TERR
699 (TERR_ID, TERR_RSC_ID, ORG_ID, ACTION, TRIGGER_MODE,
700 OLD_LAST_UPDATE_DATE, OLD_LAST_UPDATED_BY, OLD_CREATION_DATE, OLD_CREATED_BY,
701 OLD_LAST_UPDATE_LOGIN, OLD_RESOURCE_ID, OLD_GROUP_ID, OLD_RESOURCE_TYPE,
702 OLD_ROLE, OLD_PRIMARY_CONTACT_FLAG, OLD_START_DATE_ACTIVE, OLD_END_DATE_ACTIVE,
703 OLD_FULL_ACCESS_FLAG, NEW_LAST_UPDATE_DATE, NEW_LAST_UPDATED_BY, NEW_CREATION_DATE,
704 NEW_CREATED_BY, NEW_LAST_UPDATE_LOGIN, NEW_RESOURCE_ID, NEW_GROUP_ID,
705 NEW_RESOURCE_TYPE, NEW_ROLE, NEW_PRIMARY_CONTACT_FLAG, NEW_START_DATE_ACTIVE,
706 NEW_END_DATE_ACTIVE, NEW_FULL_ACCESS_FLAG)
707 VALUES
708 (l_terr_id, l_terr_rsc_id, p_ORG_ID, 'NEW_RECORD', 'ON-INSERT',
709 o_LAST_UPDATE_DATE, o_LAST_UPDATED_BY, o_CREATION_DATE, o_CREATED_BY,
710 o_LAST_UPDATE_LOGIN, o_RESOURCE_ID, o_RESOURCE_TYPE, o_GROUP_ID,
711 o_ROLE, o_PRIMARY_CONTACT_FLAG, o_START_DATE_ACTIVE, o_END_DATE_ACTIVE,
712 o_FULL_ACCESS_FLAG, n_LAST_UPDATE_DATE, n_LAST_UPDATED_BY, n_CREATION_DATE,
713 n_CREATED_BY, n_LAST_UPDATE_LOGIN, n_RESOURCE_ID, n_GROUP_ID, n_RESOURCE_TYPE,
714 n_ROLE, n_PRIMARY_CONTACT_FLAG, n_START_DATE_ACTIVE,
715 n_END_DATE_ACTIVE, n_FULL_ACCESS_FLAG);
716 --dbms_output.put_line('JTF_TERR_RSC_BIUD-Handler Success: ON-INSERT, NEW_RECORD'|| sqlerrm);
717 EXCEPTION
718 WHEN OTHERS THEN
719 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_BIUD-Handler', 'Error inserting record for inserting terr rsc' || sqlerrm);
720 END;
721 End if;
722 elsif Trigger_mode = 'ON-UPDATE' then
723 If Terr_Rsc_Rec_Exists = 'True' then
724 -- Update only
725 BEGIN
726 UPDATE JTF_CHANGED_TERR
727 SET
728 -- TERR_RSC_ID = l_terr_rsc_id,
729 ACTION = 'UPDATED_RECORD',
730 Trigger_Mode = 'ON-UPDATE',
731 -- OLD_LAST_UPDATE_DATE = o_LAST_UPDATE_DATE,
732 -- OLD_LAST_UPDATED_BY = o_LAST_UPDATED_BY,
733 -- OLD_CREATION_DATE = o_CREATION_DATE,
734 -- OLD_CREATED_BY = o_CREATED_BY,
735 -- OLD_LAST_UPDATE_LOGIN = o_LAST_UPDATE_LOGIN,
736 -- OLD_RESOURCE_ID = o_RESOURCE_ID,
737 -- OLD_GROUP_ID = o_GROUP_ID,
738 -- OLD_RESOURCE_TYPE = o_RESOURCE_TYPE,
739 -- OLD_ROLE = o_ROLE,
740 -- OLD_PRIMARY_CONTACT_FLAG = o_PRIMARY_CONTACT_FLAG,
741 -- OLD_START_DATE_ACTIVE = o_START_DATE_ACTIVE,
742 -- OLD_END_DATE_ACTIVE = o_END_DATE_ACTIVE,
743 -- OLD_FULL_ACCESS_FLAG = o_FULL_ACCESS_FLAG,
744 NEW_LAST_UPDATE_DATE = n_LAST_UPDATE_DATE,
745 NEW_LAST_UPDATED_BY = n_LAST_UPDATED_BY,
746 NEW_CREATION_DATE = n_CREATION_DATE,
747 NEW_CREATED_BY = n_CREATED_BY,
748 NEW_LAST_UPDATE_LOGIN = n_LAST_UPDATE_LOGIN,
749 NEW_RESOURCE_ID = n_RESOURCE_ID,
750 NEW_GROUP_ID = n_GROUP_ID,
751 NEW_RESOURCE_TYPE = n_RESOURCE_TYPE,
752 NEW_ROLE = n_ROLE,
753 NEW_PRIMARY_CONTACT_FLAG = n_PRIMARY_CONTACT_FLAG,
754 NEW_START_DATE_ACTIVE = n_START_DATE_ACTIVE,
755 NEW_END_DATE_ACTIVE = n_END_DATE_ACTIVE,
756 NEW_FULL_ACCESS_FLAG = n_FULL_ACCESS_FLAG
757 WHERE terr_qtype_usg_id is null
758 and terr_rsc_access_id is null
759 and terr_value_id is null
760 and TERR_RSC_ID = l_terr_rsc_id
761 and terr_id = l_terr_id
762 and request_id is null;
763 EXCEPTION
764 WHEN OTHERS THEN
765 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_BIUD-Handler', 'Error while updating record for updating terr rsc: ' || sqlerrm);
766 END;
767 else
768 -- Changed_Terr_Value_Rec_Exists = 'False'
769 -- Insert record
770 BEGIN
771 INSERT INTO JTF_CHANGED_TERR
772 (TERR_ID, TERR_RSC_ID, ORG_ID, ACTION, TRIGGER_MODE,
773 OLD_LAST_UPDATE_DATE, OLD_LAST_UPDATED_BY, OLD_CREATION_DATE, OLD_CREATED_BY,
774 OLD_LAST_UPDATE_LOGIN, OLD_RESOURCE_ID, OLD_GROUP_ID, OLD_RESOURCE_TYPE,
775 OLD_ROLE, OLD_PRIMARY_CONTACT_FLAG, OLD_START_DATE_ACTIVE, OLD_END_DATE_ACTIVE,
776 OLD_FULL_ACCESS_FLAG, NEW_LAST_UPDATE_DATE, NEW_LAST_UPDATED_BY, NEW_CREATION_DATE,
777 NEW_CREATED_BY, NEW_LAST_UPDATE_LOGIN, NEW_RESOURCE_ID, NEW_GROUP_ID,
778 NEW_RESOURCE_TYPE, NEW_ROLE, NEW_PRIMARY_CONTACT_FLAG, NEW_START_DATE_ACTIVE,
779 NEW_END_DATE_ACTIVE, NEW_FULL_ACCESS_FLAG)
780 VALUES
781 (l_terr_id, l_terr_rsc_id, p_ORG_ID, 'NEW_RECORD', 'ON-UPDATE',
782 o_LAST_UPDATE_DATE, o_LAST_UPDATED_BY, o_CREATION_DATE, o_CREATED_BY,
783 o_LAST_UPDATE_LOGIN, o_RESOURCE_ID, o_GROUP_ID, o_RESOURCE_TYPE, o_ROLE,
784 o_PRIMARY_CONTACT_FLAG, o_START_DATE_ACTIVE, o_END_DATE_ACTIVE, o_FULL_ACCESS_FLAG,
785 n_LAST_UPDATE_DATE, n_LAST_UPDATED_BY, n_CREATION_DATE, n_CREATED_BY,
786 n_LAST_UPDATE_LOGIN, n_RESOURCE_ID, n_GROUP_ID, n_RESOURCE_TYPE,
787 n_ROLE, n_PRIMARY_CONTACT_FLAG, n_START_DATE_ACTIVE, n_END_DATE_ACTIVE,
788 n_FULL_ACCESS_FLAG);
789 EXCEPTION
790 WHEN OTHERS THEN
791 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_BIUD-Handler', 'Error while inserting record for updating terr rsc' || sqlerrm);
792 END;
793 End if;
794 else -- Trigger_mode = 'ON-DELETE'
795 If Terr_Rsc_Rec_Exists = 'True' then
796 BEGIN
797 DELETE from JTF_CHANGED_TERR
798 WHERE terr_id = l_terr_id
799 and TERR_RSC_ID = l_terr_rsc_id
800 and TERR_VALUE_ID is null
801 and terr_qtype_usg_id is null
802 and terr_rsc_access_id is null
803 and request_id is null;
804 EXCEPTION
805 When OTHERS then
806 null;
807 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Error deleting record for deleting terr rsc' || sqlerrm);
808 End;
809 else -- Changed_Terr_Value_Rec_Exists = 'False'
810 BEGIN
811 INSERT INTO JTF_CHANGED_TERR
812 (TERR_ID, TERR_RSC_ID, ORG_ID, ACTION, TRIGGER_MODE,
813 OLD_LAST_UPDATE_DATE, OLD_LAST_UPDATED_BY, OLD_CREATION_DATE, OLD_CREATED_BY,
814 OLD_LAST_UPDATE_LOGIN, OLD_RESOURCE_ID, OLD_GROUP_ID, OLD_RESOURCE_TYPE,
815 OLD_ROLE, OLD_PRIMARY_CONTACT_FLAG, OLD_START_DATE_ACTIVE, OLD_END_DATE_ACTIVE,
816 OLD_FULL_ACCESS_FLAG, NEW_LAST_UPDATE_DATE, NEW_LAST_UPDATED_BY, NEW_CREATION_DATE,
817 NEW_CREATED_BY, NEW_LAST_UPDATE_LOGIN, NEW_RESOURCE_ID, NEW_GROUP_ID,
818 NEW_RESOURCE_TYPE, NEW_ROLE, NEW_PRIMARY_CONTACT_FLAG, NEW_START_DATE_ACTIVE,
819 NEW_END_DATE_ACTIVE, NEW_FULL_ACCESS_FLAG)
820 VALUES
821 (l_terr_id, l_terr_rsc_id, p_ORG_ID, 'NEW_RECORD', 'ON-DELETE',
822 o_LAST_UPDATE_DATE, o_LAST_UPDATED_BY, o_CREATION_DATE, o_CREATED_BY,
823 o_LAST_UPDATE_LOGIN, o_RESOURCE_ID, o_GROUP_ID, o_RESOURCE_TYPE,
824 o_ROLE, o_PRIMARY_CONTACT_FLAG, o_START_DATE_ACTIVE, o_END_DATE_ACTIVE,
825 o_FULL_ACCESS_FLAG, n_LAST_UPDATE_DATE, n_LAST_UPDATED_BY, n_CREATION_DATE,
826 n_CREATED_BY, n_LAST_UPDATE_LOGIN, n_RESOURCE_ID, n_GROUP_ID,
827 n_RESOURCE_TYPE, n_ROLE, n_PRIMARY_CONTACT_FLAG, n_START_DATE_ACTIVE,
828 n_END_DATE_ACTIVE, n_FULL_ACCESS_FLAG);
829 EXCEPTION
830 WHEN OTHERS THEN
831 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_BIUD-Handler', 'Error while inserting record for deleting terr rsc ' || sqlerrm);
832 END;
833 End if;
834
835 End if;
836 EXCEPTION
837 When NOT_SALES_TERR_CHANGE then
838 null;
839 When OTHERS then
840 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_BIUD-Handler', 'Problems: ' || sqlerrm);
841 null;
842
843 END Terr_Rsc_Trigger_Handler;
844
845 --**************************************************************
846 -- Terr_QType_Trigger_Handler
847 --**************************************************************
848
849 PROCEDURE Terr_QType_Trigger_Handler(
850 p_terr_qtype_usg_id NUMBER,
851 p_terr_id NUMBER,
852 p_org_id NUMBER,
853 o_LAST_UPDATED_BY NUMBER,
854 o_LAST_UPDATE_DATE DATE,
855 o_CREATED_BY NUMBER,
856 o_CREATION_DATE DATE,
857 o_LAST_UPDATE_LOGIN NUMBER,
858 n_LAST_UPDATED_BY NUMBER,
859 n_LAST_UPDATE_DATE DATE,
860 n_CREATED_BY NUMBER,
861 n_CREATION_DATE DATE,
862 n_LAST_UPDATE_LOGIN NUMBER,
863 o_qual_type_usg_id NUMBER,
864 n_qual_type_usg_id NUMBER,
865 Trigger_Mode VARCHAR2
866 )
867 IS
868 Changed_Terr_QType_Rec_Exists VARCHAR2(30);
869 l_terr_id NUMBER;
870 l_terr_qtype_usg_id NUMBER;
871 exist_terr_id NUMBER;
872 exist_terr_qtype_id NUMBER;
873 l_source_id NUMBER;
874 NOT_SALES_TERR_CHANGE exception;
875
876
877 BEGIN
878 --dbms_output.put_line('Terr_QType_Trigger_Handler: BEGIN');
879
880 l_terr_id := p_terr_id;
881 l_terr_qtype_usg_id := p_terr_qtype_usg_id;
882
883 -- check source of current territory
884 -- only add / modify records in jtf_changed_terr all
885 BEGIN
886 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_QTYPE) terr_id = ' || l_terr_id);
887 Select source_id into l_source_id
888 from jtf_terr_usgs
889 where terr_id = l_terr_id
890 and source_id = -1001;
891 EXCEPTION
892 When NO_DATA_FOUND then
893 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_QTYPE) NOT A SALES_TERR_CHANGE, raising exception ');
894 raise NOT_SALES_TERR_CHANGE;
895 End;
896 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_QTYPE) SALES_TERR_CHANGE, updating changed terr table');
897
898
899 -- Check if record already exists in changed_terr table
900 BEGIN
901 Select terr_qtype_usg_id into exist_terr_qtype_id
902 from JTF_CHANGED_TERR
903 where terr_value_id is null
904 and terr_rsc_id is null
905 and terr_rsc_access_id is null
906 and terr_qtype_usg_id = l_terr_qtype_usg_id
907 and terr_id = l_terr_id
908 and request_id is null;
909
910 Changed_Terr_QType_Rec_Exists := 'True';
911 EXCEPTION
912 When NO_DATA_FOUND then
913 exist_terr_qtype_id := NULL;
914 Changed_Terr_QType_Rec_Exists := 'False';
915 --dbms_output.put_line('JTDT- no data found in table: ' || sqlerrm);
916 When OTHERS then
917 null;
918 --dbms_output.put_line('JTDT- error while looking for existing records: ' || sqlerrm);
919 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QTYPE_USGS_BIUD-Handler', 'Error testing for existing record: ' || sqlerrm);
920 End;
921
922 -- Extract the terr_id for this terr_value_id
923 If Trigger_Mode = 'ON-INSERT' then
924 If Changed_Terr_QType_Rec_Exists = 'True' then
925 -- Update only -- This should never happen.
926 null;
927 else -- Changed_Terr_Value_Rec_Exists = 'False'
928 -- Insert record
929 BEGIN
930 INSERT INTO JTF_CHANGED_TERR
931 (TERR_ID, TERR_QTYPE_USG_ID, ORG_ID, ACTION, TRIGGER_MODE,
932 OLD_LAST_UPDATE_DATE, OLD_LAST_UPDATED_BY, OLD_CREATION_DATE, OLD_CREATED_BY, OLD_LAST_UPDATE_LOGIN,
933 NEW_LAST_UPDATE_DATE, NEW_LAST_UPDATED_BY, NEW_CREATION_DATE, NEW_CREATED_BY, NEW_LAST_UPDATE_LOGIN,
934 OLD_qual_type_usg_id, NEW_qual_type_usg_id)
935 VALUES
936 (l_terr_id, l_terr_qtype_usg_id, p_org_id, 'NEW_RECORD', 'ON-INSERT',
937 o_LAST_UPDATE_DATE, o_LAST_UPDATED_BY, o_CREATION_DATE, o_CREATED_BY, o_LAST_UPDATE_LOGIN,
938 n_LAST_UPDATE_DATE, n_LAST_UPDATED_BY, n_CREATION_DATE, n_CREATED_BY, n_LAST_UPDATE_LOGIN,
939 o_qual_type_usg_id, n_qual_type_usg_id);
940
941 EXCEPTION
942 WHEN OTHERS THEN
943 --dbms_output.put_line('Failed at ON-INSERT, NEW_RECORD'|| sqlerrm);
944 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QTYPE_USGS_BIUD-Handler', 'Error inserting record for inserting terr qtype' || sqlerrm);
945 END;
946 End if;
947 elsif Trigger_mode = 'ON-UPDATE' then
948 If Changed_Terr_QType_Rec_Exists = 'True' then
949 -- Update only
950 BEGIN
951 UPDATE JTF_CHANGED_TERR
952 SET -- TERR_ID cannot be changed, as it is primary key
953 --TERR_QTYPE_USG_ID = l_terr_qtype_usg_id,
954 ACTION = 'UPDATED_RECORD',
955 Trigger_Mode = 'ON-UPDATE',
956 -- OLD_LAST_UPDATE_DATE = o_LAST_UPDATE_DATE,
957 -- OLD_LAST_UPDATED_BY = o_LAST_UPDATED_BY,
958 -- OLD_CREATION_DATE = o_CREATION_DATE,
959 -- OLD_CREATED_BY = o_CREATED_BY,
960 -- OLD_LAST_UPDATE_LOGIN = o_LAST_UPDATE_LOGIN,
961 NEW_LAST_UPDATE_DATE = n_LAST_UPDATE_DATE,
962 NEW_LAST_UPDATED_BY = n_LAST_UPDATED_BY,
963 NEW_CREATION_DATE = n_CREATION_DATE,
964 NEW_CREATED_BY = n_CREATED_BY,
965 NEW_LAST_UPDATE_LOGIN = n_LAST_UPDATE_LOGIN,
966 OLD_qual_type_usg_id = o_qual_type_usg_id,
967 NEW_qual_type_usg_id = n_qual_type_usg_id
968 WHERE terr_rsc_id is null
969 and terr_rsc_access_id is null
970 and terr_value_id is null
971 and terr_qtype_usg_id = l_terr_qtype_usg_id
972 and terr_id = l_terr_id
973 and request_id is null;
974 EXCEPTION
975 WHEN OTHERS THEN
976 --dbms_output.put_line('Failed at ON-UPDATE, UPDATED_RECORD'|| sqlerrm);
977 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QTYPE_USGS_BIUD-Handler', 'Error while updating record for updating terr qtype: ' || sqlerrm);
978 END;
979 else
980 -- Changed_Terr_Value_Rec_Exists = 'False'
981 -- Insert record
982 BEGIN
983 INSERT INTO JTF_CHANGED_TERR
984 (TERR_ID, TERR_QTYPE_USG_ID, ORG_ID, ACTION, TRIGGER_MODE,
985 OLD_LAST_UPDATE_DATE, OLD_LAST_UPDATED_BY, OLD_CREATION_DATE, OLD_CREATED_BY, OLD_LAST_UPDATE_LOGIN,
986 NEW_LAST_UPDATE_DATE, NEW_LAST_UPDATED_BY, NEW_CREATION_DATE, NEW_CREATED_BY, NEW_LAST_UPDATE_LOGIN,
987 OLD_qual_type_usg_id, NEW_qual_type_usg_id)
988 VALUES
989 (l_terr_id, l_terr_qtype_usg_id, p_org_id, 'NEW_RECORD', 'ON-UPDATE',
990 o_LAST_UPDATE_DATE, o_LAST_UPDATED_BY, o_CREATION_DATE, o_CREATED_BY, o_LAST_UPDATE_LOGIN,
991 n_LAST_UPDATE_DATE, n_LAST_UPDATED_BY, n_CREATION_DATE, n_CREATED_BY, n_LAST_UPDATE_LOGIN,
992 o_qual_type_usg_id, n_qual_type_usg_id);
993 EXCEPTION
994 WHEN OTHERS THEN
995 --dbms_output.put_line('Failed at ON-UPDATE, NEW_RECORD'|| sqlerrm);
996 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QTYPE_USGS_BIUD-Handler', 'Error while inserting record for updating terr qtype: ' || sqlerrm);
997 END;
998 End if;
999 else
1000 -- Trigger_mode = 'ON-DELETE'
1001 If Changed_Terr_QType_Rec_Exists = 'True' then
1002 BEGIN
1003 DELETE from JTF_CHANGED_TERR
1004 WHERE terr_id = l_terr_id
1005 and terr_qtype_usg_id = l_terr_qtype_usg_id
1006 and TERR_RSC_ID is null
1007 and TERR_VALUE_ID is null
1008 and terr_rsc_access_id is null
1009 and terr_value_id is null
1010 and request_id is null;
1011 EXCEPTION
1012 When OTHERS then
1013 null;
1014 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Error deleting record for deleting terr rsc' || sqlerrm);
1015 End;
1016 else -- Changed_Terr_Value_Rec_Exists = 'False'
1017 BEGIN
1018 INSERT INTO JTF_CHANGED_TERR
1019 (TERR_ID, TERR_QTYPE_USG_ID, ORG_ID, ACTION, TRIGGER_MODE,
1020 OLD_LAST_UPDATE_DATE, OLD_LAST_UPDATED_BY, OLD_CREATION_DATE, OLD_CREATED_BY, OLD_LAST_UPDATE_LOGIN,
1021 NEW_LAST_UPDATE_DATE, NEW_LAST_UPDATED_BY, NEW_CREATION_DATE, NEW_CREATED_BY, NEW_LAST_UPDATE_LOGIN,
1022 OLD_qual_type_usg_id, NEW_qual_type_usg_id)
1023 VALUES
1024 (l_terr_id, l_terr_qtype_usg_id, p_org_id, 'NEW_RECORD', 'ON-DELETE',
1025 o_LAST_UPDATE_DATE, o_LAST_UPDATED_BY, o_CREATION_DATE, o_CREATED_BY, o_LAST_UPDATE_LOGIN,
1026 n_LAST_UPDATE_DATE, n_LAST_UPDATED_BY, n_CREATION_DATE, n_CREATED_BY, n_LAST_UPDATE_LOGIN,
1027 o_qual_type_usg_id, n_qual_type_usg_id);
1028 EXCEPTION
1029 WHEN OTHERS THEN
1030 --dbms_output.put_line('Failed at ON-DELETE, NEW_RECORD' || sqlerrm);
1031 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QTYPE_USGS_BIUD-Handler', 'Error while inserting record for deleting terr qtype: ' || sqlerrm);
1032 END;
1033 End if;
1034 End if;
1035 EXCEPTION
1036 When NOT_SALES_TERR_CHANGE then
1037 null;
1038 When OTHERS then
1039 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_QTYPE_BIUD-Handler', 'Problems: ' || sqlerrm);
1040 null;
1041
1042 END Terr_QType_Trigger_Handler;
1043
1044 --**************************************************************
1045 -- Terr_RscAccess_Trigger_Handler
1046 --**************************************************************
1047 PROCEDURE Terr_RscAccess_Trigger_Handler(
1048 p_terr_rsc_access_id NUMBER,
1049 p_terr_rsc_id NUMBER,
1050 p_org_id NUMBER,
1051 o_LAST_UPDATED_BY NUMBER,
1052 o_LAST_UPDATE_DATE DATE,
1053 o_CREATED_BY NUMBER,
1054 o_CREATION_DATE DATE,
1055 o_LAST_UPDATE_LOGIN NUMBER,
1056 n_LAST_UPDATED_BY NUMBER,
1057 n_LAST_UPDATE_DATE DATE,
1058 n_CREATED_BY NUMBER,
1059 n_CREATION_DATE DATE,
1060 n_LAST_UPDATE_LOGIN NUMBER,
1061 o_access_type VARCHAR2,
1062 n_access_type VARCHAR2,
1063 Trigger_Mode VARCHAR2
1064 )
1065 IS
1066 Changed_Terr_RscAcc_Rec_Exists VARCHAR2(30);
1067 l_terr_id NUMBER;
1068 l_terr_rsc_id NUMBER;
1069 l_terr_rsc_access_id NUMBER;
1070 exist_terr_id NUMBER;
1071 exist_terr_rscaccess_id NUMBER;
1072 l_source_id NUMBER;
1073 NOT_SALES_TERR_CHANGE exception;
1074
1075 BEGIN
1076 null;
1077
1078 l_terr_id := null;
1079 l_terr_rsc_id := p_terr_rsc_id;
1080 l_terr_rsc_access_id := p_terr_rsc_access_id;
1081
1082 --dbms_output.put_line('Terr_RscAccess_Trigger_Handler: BEGIN');
1083 --Get the territory_id for this record
1084 BEGIN
1085 Select terr_id into l_terr_id
1086 from jtf_terr_rsc
1087 where terr_rsc_id = l_terr_rsc_id;
1088 EXCEPTION
1089 WHEN NO_DATA_FOUND then
1090 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_ACCESS_BIUD-Handler', 'terr_id does not exist for terr_rsc_access_id');
1091 WHEN OTHERS then
1092 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_ACCESS_BIUD-Handler', 'Error while fetching terr_id from terr_rsc_access_id: ' || sqlerrm);
1093 End;
1094
1095 -- check source of current territory
1096 -- only add / modify records in jtf_changed_terr all
1097 BEGIN
1098 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_RSC_ACCESS) terr_id = ' || l_terr_id);
1099 Select source_id into l_source_id
1100 from jtf_terr_usgs
1101 where terr_id = l_terr_id
1102 and source_id = -1001;
1103 EXCEPTION
1104 When NO_DATA_FOUND then
1105 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_RSC_ACCESS) NOT A SALES_TERR_CHANGE, raising exception ');
1106 raise NOT_SALES_TERR_CHANGE;
1107 End;
1108 --dbms_output.put_line('JTF_TERR_TRIGGER_HANDLER: (TERR_RSC_ACCESS) SALES_TERR_CHANGE, updating changed terr table');
1109
1110 --dbms_output.put_line('JTDT- l_terr_id = ' || l_terr_id);
1111 -- Check if record already exists in changed_terr table
1112 BEGIN
1113 Select terr_rsc_access_id into l_terr_rsc_access_id
1114 from JTF_CHANGED_TERR
1115 where terr_value_id is null
1116 and terr_rsc_id is null
1117 and terr_qtype_usg_id is null
1118 and terr_rsc_access_id = l_terr_rsc_access_id
1119 and terr_id = l_terr_id
1120 and request_id is null;
1121
1122 Changed_Terr_RscAcc_Rec_Exists := 'True';
1123 EXCEPTION
1124 When NO_DATA_FOUND then
1125 exist_terr_rscaccess_id := NULL;
1126 Changed_Terr_RscAcc_Rec_Exists := 'False';
1127 --dbms_output.put_line('JTDT- no data found in table: ' || sqlerrm);
1128 When OTHERS then
1129 null;
1130 --dbms_output.put_line('JTDT- error while looking for existing records: ' || sqlerrm);
1131 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_ACCESS_BIUD-Handler', 'Error testing for existing record: ' || sqlerrm);
1132 End;
1133
1134 -- Extract the terr_id for this terr_value_id
1135 If Trigger_Mode = 'ON-INSERT' then
1136 If Changed_Terr_RscAcc_Rec_Exists = 'True' then
1137 -- Update only -- This should never happen.
1138 null;
1139 else -- Changed_Terr_RscAccess_Rec_Exists = 'False'
1140 -- Insert record
1141 BEGIN
1142 INSERT INTO JTF_CHANGED_TERR
1143 (TERR_ID, TERR_RSC_ACCESS_ID, ORG_ID, ACTION, TRIGGER_MODE,
1144 OLD_LAST_UPDATE_DATE, OLD_LAST_UPDATED_BY, OLD_CREATION_DATE, OLD_CREATED_BY, OLD_LAST_UPDATE_LOGIN,
1145 NEW_LAST_UPDATE_DATE, NEW_LAST_UPDATED_BY, NEW_CREATION_DATE, NEW_CREATED_BY, NEW_LAST_UPDATE_LOGIN,
1146 OLD_ACCESS_TYPE, NEW_ACCESS_TYPE)
1147 VALUES
1148 (l_terr_id, l_terr_rsc_access_id, p_org_id, 'NEW_RECORD', 'ON-INSERT',
1149 o_LAST_UPDATE_DATE, o_LAST_UPDATED_BY, o_CREATION_DATE, o_CREATED_BY, o_LAST_UPDATE_LOGIN,
1150 n_LAST_UPDATE_DATE, n_LAST_UPDATED_BY, n_CREATION_DATE, n_CREATED_BY, n_LAST_UPDATE_LOGIN,
1151 o_access_type, n_access_type);
1152
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 --dbms_output.put_line('Failed at ON-INSERT, NEW_RECORD'|| sqlerrm);
1156 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_ACCESS_BIUD-Handler', 'Error inserting record for inserting rsc access' || sqlerrm);
1157 END;
1158 End if;
1159 elsif Trigger_mode = 'ON-UPDATE' then
1160 If Changed_Terr_RscAcc_Rec_Exists = 'True' then
1161 -- Update only
1162 BEGIN
1163 UPDATE JTF_CHANGED_TERR
1164 SET -- TERR_ID cannot be changed, as it is primary key
1165 --TERR_RSC_ACCESS_ID = l_terr_rsc_access_id,
1166 ACTION = 'UPDATED_RECORD',
1167 Trigger_Mode = 'ON-UPDATE',
1168 -- OLD_LAST_UPDATE_DATE = o_LAST_UPDATE_DATE,
1169 -- OLD_LAST_UPDATED_BY = o_LAST_UPDATED_BY,
1170 -- OLD_CREATION_DATE = o_CREATION_DATE,
1171 -- OLD_CREATED_BY = o_CREATED_BY,
1172 -- OLD_LAST_UPDATE_LOGIN = o_LAST_UPDATE_LOGIN,
1173 NEW_LAST_UPDATE_DATE = n_LAST_UPDATE_DATE,
1174 NEW_LAST_UPDATED_BY = n_LAST_UPDATED_BY,
1175 NEW_CREATION_DATE = n_CREATION_DATE,
1176 NEW_CREATED_BY = n_CREATED_BY,
1177 NEW_LAST_UPDATE_LOGIN = n_LAST_UPDATE_LOGIN,
1178 OLD_ACCESS_TYPE = o_access_type,
1179 NEW_ACCESS_TYPE = n_access_type
1180 WHERE terr_rsc_id is null
1181 and terr_qtype_usg_id is null
1182 and terr_value_id is null
1183 and terr_rsc_access_id = l_terr_rsc_access_id
1184 and terr_id = l_terr_id
1185 and request_id is null;
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 --dbms_output.put_line('Failed at ON-UPDATE, UPDATED_RECORD'|| sqlerrm);
1189 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_ACCESS_BIUD-Handler', 'Error while updating record for updating rsc access: ' || sqlerrm);
1190 END;
1191 else
1192 -- Changed_Terr_RscAccess_Rec_Exists = 'False'
1193 -- Insert record
1194 BEGIN
1195 INSERT INTO JTF_CHANGED_TERR
1196 (TERR_ID, TERR_RSC_ACCESS_ID, ORG_ID, ACTION, TRIGGER_MODE,
1197 OLD_LAST_UPDATE_DATE, OLD_LAST_UPDATED_BY, OLD_CREATION_DATE, OLD_CREATED_BY, OLD_LAST_UPDATE_LOGIN,
1198 NEW_LAST_UPDATE_DATE, NEW_LAST_UPDATED_BY, NEW_CREATION_DATE, NEW_CREATED_BY, NEW_LAST_UPDATE_LOGIN,
1199 OLD_ACCESS_TYPE, NEW_ACCESS_TYPE)
1200 VALUES
1201 (l_terr_id, l_terr_rsc_access_id, p_org_id, 'NEW_RECORD', 'ON-UPDATE',
1202 o_LAST_UPDATE_DATE, o_LAST_UPDATED_BY, o_CREATION_DATE, o_CREATED_BY, o_LAST_UPDATE_LOGIN,
1203 n_LAST_UPDATE_DATE, n_LAST_UPDATED_BY, n_CREATION_DATE, n_CREATED_BY, n_LAST_UPDATE_LOGIN,
1204 o_access_type, n_access_type);
1205 EXCEPTION
1206 WHEN OTHERS THEN
1207 --dbms_output.put_line('Failed at ON-UPDATE, NEW_RECORD'|| sqlerrm);
1208 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_ACCESS_BIUD-Handler', 'Error while inserting record for updating rsc access' || sqlerrm);
1209 END;
1210 End if;
1211 else
1212 -- Trigger_mode = 'ON-DELETE'
1213 If Changed_Terr_RscAcc_Rec_Exists = 'True' then
1214 BEGIN
1215 DELETE from JTF_CHANGED_TERR
1216 WHERE terr_id = l_terr_id
1217 and TERR_RSC_ID is null
1218 and terr_rsc_access_id = l_terr_rsc_access_id
1219 and terr_qtype_usg_id is null
1220 and terr_value_id is null
1221 and request_id is null;
1222 EXCEPTION
1223 When OTHERS then
1224 null;
1225 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERRITORIES_BIUD-Handler', 'Error deleting record for deleting terr rsc' || sqlerrm);
1226 End;
1227 else -- Changed_Terr_RscAccess_Rec_Exists = 'False'
1228 BEGIN
1229 INSERT INTO JTF_CHANGED_TERR
1230 (TERR_ID, TERR_RSC_ACCESS_ID, ORG_ID, ACTION, TRIGGER_MODE,
1231 OLD_LAST_UPDATE_DATE, OLD_LAST_UPDATED_BY, OLD_CREATION_DATE, OLD_CREATED_BY, OLD_LAST_UPDATE_LOGIN,
1232 NEW_LAST_UPDATE_DATE, NEW_LAST_UPDATED_BY, NEW_CREATION_DATE, NEW_CREATED_BY, NEW_LAST_UPDATE_LOGIN,
1233 OLD_ACCESS_TYPE, NEW_ACCESS_TYPE)
1234 VALUES
1235 (l_terr_id, l_terr_rsc_access_id, p_org_id, 'NEW_RECORD', 'ON-DELETE',
1236 o_LAST_UPDATE_DATE, o_LAST_UPDATED_BY, o_CREATION_DATE, o_CREATED_BY, o_LAST_UPDATE_LOGIN,
1237 n_LAST_UPDATE_DATE, n_LAST_UPDATED_BY, n_CREATION_DATE, n_CREATED_BY, n_LAST_UPDATE_LOGIN,
1238 o_access_type, n_access_type);
1239 EXCEPTION
1240 WHEN OTHERS THEN
1241 --dbms_output.put_line('Failed at ON-DELETE, NEW_RECORD' || sqlerrm);
1242 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_ACCESS_BIUD-Handler', 'Error while inserting record for deleting rsc access ' || sqlerrm);
1243 END;
1244 End if;
1245 End if;
1246 EXCEPTION
1247 When NOT_SALES_TERR_CHANGE then
1248 null;
1249 When OTHERS then
1250 FND_MSG_PUB.Add_Exc_Msg( 'JTF_TERR_RSC_ACCESS_BIUD-Handler', 'Problems: ' || sqlerrm);
1251 null;
1252 END Terr_RscAccess_Trigger_Handler;
1253
1254
1255 END JTF_TERR_TRIGGER_HANDLERS;