[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_CREATE_TERR_FROM_GEO
Source
1 PACKAGE BODY JTF_TTY_CREATE_TERR_FROM_GEO AS
2 /* $Header: jtfctfgb.pls 120.5 2006/09/12 02:22:49 spai noship $ */
3 -- Start of Comments
4 -- PURPOSE
5 -- For creating/updating equivalent territory for each geo territory created
6 -- or updated
7 --
8 -- HISTORY
9 -- 06/02/03 Vbghosh Initial Creation
10 -- End of Comments
11 ----
12
13 PROCEDURE CREATE_TERR (p_geo_terr_id IN NUMBER,
14 p_geo_parent_terr_id IN NUMBER,
15 p_geo_terr_name IN VARCHAR2)
16 IS
17 l_parent_terr_id NUMBER;
18 l_terr_id NUMBER;
19 l_qual_type_usg_id NUMBER;
20
21 l_terr_qtype_usg_id NUMBER;
22 l_terr_qual_id NUMBER;
23 l_terr_val_id NUMBER;
24
25 l_geo_terr_group_id NUMBER;
26 l_terr_rsc_id NUMBER;
27 l_terr_type_id NUMBER;
28
29 l_terr_rsc_access_id NUMBER;
30
31 l_start_date_active DATE;
32 l_end_date_active DATE;
33
34 l_access_type VARCHAR2(200);
35
36
37
38 l_org_id NUMBER;
39 l_rank NUMBER;
40
41
42
43 /* Cursor to get QType Usage from geo_terr_id. This value will be inserted in
44 table jtf_qtype_usgs_all */
45
46 CURSOR c_get_qtype_usgs(l_geo_terr_id NUMBER) IS
47 SELECT ra.access_type
48 FROM
49 JTF_TTY_GEO_TERR_RSC grsc
50 , jtf_tty_geo_terr gtr
51 , jtf_tty_terr_grp_roles tgr
52 , jtf_tty_role_access ra
53 WHERE grsc.GEO_TERRITORY_ID = l_geo_terr_id
54 AND gtr.geo_territory_id = grsc.geo_territory_id
55 AND grsc.rsc_role_code = tgr.role_code
56 AND tgr.terr_group_id = gtr.terr_group_id
57 AND ra.terr_group_role_id = tgr.terr_group_role_id;
58
59
60 /* Cursor to find Role Code */
61 CURSOR c_role_code(l_terr_group_id NUMBER) IS
62 SELECT b.role_code role_code
63 ,b.terr_group_id
64 FROM jtf_tty_terr_grp_roles b
65 WHERE
66 b.terr_group_id = l_terr_group_id
67 ORDER BY b.role_code;
68
69 /* Resource for territory */
70 CURSOR c_terr_resource (l_geo_territory_id NUMBER,l_role VARCHAR2) IS
71 SELECT DISTINCT a.resource_id
72 , a.rsc_group_id
73 , NVL(a.rsc_resource_type,'RS_EMPLOYEE') rsc_resource_type
74 FROM jtf_tty_geo_terr_rsc a
75 , jtf_tty_geo_terr b
76 WHERE a.geo_territory_id = b.geo_territory_id
77 AND b.geo_territory_id = l_geo_territory_id
78 AND a.rsc_role_code = l_role;
79
80
81 /* Access Types for a particular Role within a Territory Group */
82 CURSOR c_role_access( lp_terr_group_id NUMBER
83 , lp_role VARCHAR2) IS
84 SELECT DISTINCT a.access_type
85 FROM jtf_tty_role_access a
86 , jtf_tty_terr_grp_roles b
87 WHERE a.terr_group_role_id = b.terr_group_role_id
88 AND b.terr_group_id = lp_terr_group_id
89 AND b.role_code = lp_role;
90
91
92
93 BEGIN --top level begin
94
95
96 --dbms_output.put_line(' THis is Test');
97 --dbms_output.put_line(' p_geo_parent_terr_id ='||p_geo_parent_terr_id);
98
99 BEGIN
100 --dbms_output.put_line('p_geo_terr_id:' || p_geo_terr_id);
101
102 /* Check if call is for update or create
103 for the geo_terr_id if there is record present in terr_all
104 then call is for update.In this case ..*/
105 BEGIN
106 SELECT
107 terr_id
108 INTO l_terr_id
109 FROM jtf_terr_all
110 WHERE geo_territory_id = p_geo_terr_id;
111
112 EXCEPTION
113 when FND_API.G_EXC_ERROR then
114 -- Add proper error logging
115 --dbms_output.put_line(' exc err sqlerrm:' || sqlerrm);
116 NULL;
117 when FND_API.G_EXC_UNEXPECTED_ERROR then
118 -- Add proper error logging
119 --dbms_output.put_line('unexp err sqlerrm:' || sqlerrm);
120 NULL;
121 when no_data_found then
122 --dbms_output.put_line('no data found sqlerrm:' || sqlerrm);
123 NULL;
124 when others then
125 --dbms_output.put_line('other sqlerrm:' || sqlerrm);
126 -- Add proper error logging
127 NULL;
128
129 END;
130
131 --dbms_output.put_line('terr_id:' || l_terr_id);
132
133 /* If l_terr_id is not null then call is from update
134 So delete all the relevant records and then let continue the create
135 process
136 if create then create the terr_id using the sequence*/
137 IF l_terr_id IS NOT NULL THEN
138 /* Update case All the delete scripts here */
139
140 DELETE FROM jtf_terr_usgs_all where terr_id = l_terr_id;
141 DELETE FROM jtf_terr_qtype_usgs_all where terr_id = l_terr_id;
142
143 DELETE FROM jtf_terr_rsc_access_all
144 WHERE terr_rsc_id IN (SELECT terr_rsc_id FROM jtf_terr_rsc_all WHERE terr_id = l_terr_id);
145
146 DELETE FROM jtf_terr_rsc_all WHERE terr_id = l_terr_id;
147
148 DELETE FROM jtf_terr_all WHERE terr_id = l_terr_id;
149
150 ELSE
151 /*create the sequence only when call is from create otherwise use the existing ID */
152
153 SELECT JTF_TERR_S.nextval
154 INTO l_terr_id
155 FROM dual;
156
157 END IF;
158
159
160 EXCEPTION
161 WHEN OTHERS THEN
162 --dbms_output.put_line('SQL Error while gettting parent terr_id ' || sqlerrm);
163 RAISE;
164 END;
165
166
167 /* get the parent territory for geo's parent This will be inserted in parent_terr_id of terr_all */
168 /* get parent terr id and Org Id */
169
170 BEGIN
171 --dbms_output.put_line('p_geo_parent_terr_id:'||p_geo_parent_terr_id);
172 SELECT terr_id,
173 org_id,
174 rank,
175 start_Date_active,
176 end_date_active,
177 territory_type_id
178 INTO l_parent_terr_id,
179 l_org_id,
180 l_rank,
181 l_start_date_active,
182 l_end_date_active,
183 l_terr_type_id
184 FROM jtf_terr_all
185 WHERE geo_territory_id = p_geo_parent_terr_id;
186
187 --dbms_output.put_line(' Parent Territory Id is:'||l_parent_terr_id);
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 --dbms_output.put_line('SQL Error while gettting parent terr_id ' || sqlerrm);
192 RAISE;
193 END;
194
195 --dbms_output.put_line('l_parent_terr_id:'||l_parent_terr_id);
196
197 /* get terr group Id */
198
199 BEGIN
200 SELECT terr_group_id
201 INTO l_geo_terr_group_id
202 FROM jtf_tty_geo_terr
203 where geo_territory_id = p_geo_terr_id;
204 --dbms_output.put_line('terr_group_id:'||l_geo_terr_group_id);
205
206 EXCEPTION
207 when FND_API.G_EXC_ERROR then
208 -- Add proper error logging
209 NULL;
210 when FND_API.G_EXC_UNEXPECTED_ERROR then
211 -- Add proper error logging
212 NULL;
213 when others then
214 --dbms_output.put_line('SQL Error ' || sqlerrm);
215 RAISE;
216 END;
217
218 BEGIN -- insert into terr_all
219
220
221
222 --dbms_output.put_line('TERRITORY ID ' || l_terr_id);
223
224 INSERT INTO jtf_terr_all
225 ( TERR_ID
226 , NAME
227 ,LAST_UPDATE_DATE
228 ,LAST_UPDATED_BY
229 ,CREATION_DATE
230 ,CREATED_BY
231 ,LAST_UPDATE_LOGIN
232 ,APPLICATION_SHORT_NAME
233 , ENABLED_FLAG
234 , PARENT_TERRITORY_ID
235 , RANK
236 , TERRITORY_TYPE_ID
237 ,ORG_ID
238 ,OBJECT_VERSION_NUMBER
239 ,CATCH_ALL_FLAG
240 ,TERR_GROUP_FLAG
241 ,GEO_TERR_FLAG
242 ,GEO_TERRITORY_ID
243 ,TERR_GROUP_ID
244 ,START_DATE_ACTIVE
245 ,END_DATE_ACTIVE
246 )
247 SELECT l_terr_id
248 , p_geo_terr_name
249 , LAST_UPDATE_DATE
250 , LAST_UPDATED_BY
251 , SYSDATE
252 , CREATED_BY
253 , LAST_UPDATE_LOGIN
254 , 'JTF'
255 , 'Y'
256 , l_parent_terr_id
257 , l_rank--TODO Rank
258 , l_terr_type_id
259 , l_org_id --org id
260 , OBJECT_VERSION_NUMBER
261 , 'N'
262 , 'Y'
263 , 'Y'
264 , p_geo_terr_id
265 , l_geo_terr_group_id
266 , l_start_date_active --TODO END_DATE_ACTIVE
267 , l_end_date_active
268 FROM jtf_tty_geo_terr
269 where geo_territory_id = p_geo_terr_id;
270 --dbms_output.put_line(' After inserting jtf_terr_all');
271
272
273
274 EXCEPTION
275 WHEN NO_DATA_FOUND THEN
276 --dbms_output.put_line('Error 1 ' || sqlerrm);
277 NULL;
278 WHEN OTHERS THEN
279 --dbms_output.put_line('Error 2 ' || sqlerrm);
280 NULL;
281 END; -- insert into terr_all
282
283 BEGIN --insert into jtf_terr_usgs_all
284 --dbms_output.put_line('insert into jtf_terr_usgs_all');
285
286 /* insert into terr_usgs_all */
287
288 --dbms_output.put_line('Before inserting in terr usgs all Terr ID = ' || l_terr_id);
289 INSERT INTO jtf_terr_usgs_all
290 (TERR_USG_ID
291 , LAST_UPDATE_DATE
292 , LAST_UPDATED_BY
293 , CREATION_DATE
294 , CREATED_BY
295 , LAST_UPDATE_LOGIN
296 , TERR_ID
297 , SOURCE_ID
298 , ORG_ID
299 )
300 SELECT JTF_TERR_USGS_S.NEXTVAL
301 , LAST_UPDATE_DATE
302 , LAST_UPDATED_BY
303 , SYSDATE --CREATION_DATE
304 , CREATED_BY
305 , LAST_UPDATE_LOGIN
306 , l_terr_id
307 , -1001 -- FOR SALES ??
308 , l_org_id
309 from jtf_tty_geo_terr
310 where geo_territory_id = p_geo_terr_id;
311
312 --dbms_output.put_line(' After inserting jtf_terr_usgs_all');
313
314
315
316 EXCEPTION
317 WHEN NO_DATA_FOUND THEN
318 --dbms_output.put_line('Error 3 ' || sqlerrm);
319 RAISE;
320 WHEN OTHERS THEN
321 --dbms_output.put_line('Error 4 ' || sqlerrm);
322 RAISE;
323 END; --insert into jtf_terr_usgs_all
324
325 BEGIN -- insert into QType Usage
326
327 /* Open the cursor to get the Qtype */
328
329 FOR acctype IN c_get_qtype_usgs(p_geo_terr_id) LOOP
330
331 IF acctype.access_type='ACCOUNT' THEN
332 l_qual_type_usg_id := -1001;
333 END IF;
334
335 IF acctype.access_type='LEAD' THEN
336 l_qual_type_usg_id := -1002;
337 END IF;
338
339 IF acctype.access_type='OPPORTUNITY' THEN
340 l_qual_type_usg_id := -1003;
341 END IF;
342
343 IF acctype.access_type='PROPOSAL' THEN
344 l_qual_type_usg_id := -1106;
345 END IF;
346
347 IF acctype.access_type='QUOTE' THEN
348 l_qual_type_usg_id := -1105;
349 END IF;
350
351
352
353 /* get the sequence */
354 SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
355 INTO l_terr_qtype_usg_id
356 FROM DUAL;
357
358 /* Insert into table jtf_terr_qtype_all */
359
360 --dbms_output.put_line('insert into jtf_terr_qtype_usgs_all');
361 INSERT INTO jtf_terr_qtype_usgs_all
362 (TERR_QTYPE_USG_ID
363 , LAST_UPDATED_BY
364 , LAST_UPDATE_DATE
365 , CREATED_BY
366 , CREATION_DATE
367 , LAST_UPDATE_LOGIN
368 , TERR_ID
369 , QUAL_TYPE_USG_ID
370 , ORG_ID
371 )
372 SELECT l_terr_qtype_usg_id
373 , LAST_UPDATED_BY
374 , LAST_UPDATE_DATE
375 , CREATED_BY
376 , SYSDATE --CREATION_DATE
377 , LAST_UPDATE_LOGIN
378 , l_terr_id
379 , l_qual_type_usg_id
380 , l_org_id
381 FROM jtf_tty_geo_terr
382 WHERE geo_territory_id = p_geo_terr_id;
383
384 --dbms_output.put_line(' After inserting jtf_terr_qtype_usgs_all');
385
386
387
388
389 END LOOP;
390
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 --dbms_output.put_line('Error 6 ' || sqlerrm);
395 RAISE;
396 END; --insert into QType Usage
397
398
399 /* populate resource table */
400 BEGIN
401 --dbms_output.put_line('get c_role_code');
402 FOR tran_type IN c_role_code (l_geo_terr_group_id) LOOP
403
404 --dbms_output.put_line('role_code:'||tran_type.role_code||',terr_group_id:'||tran_type.terr_group_id);
405 FOR rsc IN c_terr_resource(p_geo_terr_id,tran_type.role_code) LOOP
406 --dbms_output.put_line('rs:'||rsc.resource_id||',rsc_group_id:'||rsc.rsc_group_id||',rsc_resource_type:'||rsc.rsc_resource_type);
407
408 /*insert in jtf_terr_rsc_all */
409
410
411 SELECT JTF_TERR_RSC_S.NEXTVAL
412 INTO l_terr_rsc_id
413 FROM DUAL;
414
415 INSERT INTO jtf_terr_rsc_all
416 ( TERR_RSC_ID
417 ,LAST_UPDATE_DATE
418 ,LAST_UPDATED_BY
419 ,CREATION_DATE
420 ,CREATED_BY
421 ,LAST_UPDATE_LOGIN
422 ,TERR_ID
423 ,RESOURCE_ID
424 ,RESOURCE_TYPE
425 ,GROUP_ID
426 ,ROLE
427 ,PRIMARY_CONTACT_FLAG
428 ,START_DATE_ACTIVE
429 ,END_DATE_ACTIVE
430 ,ORG_ID
431 ,OBJECT_VERSION_NUMBER )
432 SELECT l_terr_rsc_id
433 ,LAST_UPDATE_DATE
434 ,LAST_UPDATED_BY
435 ,SYSDATE --CREATION_DATE
436 ,CREATED_BY
437 ,LAST_UPDATE_LOGIN
438 , l_terr_id
439 , rsc.resource_id
440 , rsc.rsc_resource_type
441 , rsc.rsc_group_id
442 , tran_type.role_code
443 , 'N'
444 , l_start_date_active
445 , l_end_date_active
446 , l_org_id --org id
447 , 1
448 FROM jtf_tty_geo_terr
449 WHERE geo_territory_id = p_geo_terr_id;
450
451 --dbms_output.put_line(' After inserting jtf_terr_rsc_all');
452
453
454 /*insert in jtf_terr_rsc_access_all table */
455 FOR rsc_acc IN c_role_access(l_geo_terr_group_id, tran_type.role_code) LOOP
456 --dbms_output.put_line('acc_type:'||rsc_acc.access_type);
457
458 /* get the sequence */
459 SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
460 INTO l_terr_rsc_access_id
461 FROM DUAL;
462
463 /* commented this as all the types are found to be same
464 IF (rsc_acc.access_type= 'ACCOUNT') THEN
465 l_access_type :='ACCOUNT';
466 END IF;
467
468 IF (rsc_acc.access_type= 'OPPORTUNITY') THEN
469 l_access_type :='OPPOR';
470 END IF;
471
472 IF (rsc_acc.access_type= 'LEAD') THEN
473 l_access_type :='LEAD';
474 END IF;
475
476 IF (rsc_acc.access_type= 'PROPOSAL') THEN
477 l_access_type :='PROPOSAL';
478 END IF;
479
480 IF (rsc_acc.access_type = 'QUOTE') THEN
481 l_access_type :='QUOTE';
482 END IF;
483
484 */
485
486 -- SOLIN, BUG 5018824
487 /* insert into jft_Terr_rsc_Access_all */
488 INSERT INTO jtf_terr_rsc_access_all
489 ( TERR_RSC_ACCESS_ID
490 ,LAST_UPDATE_DATE
491 ,LAST_UPDATED_BY
492 ,CREATION_DATE
493 ,CREATED_BY
494 ,LAST_UPDATE_LOGIN
495 ,TERR_RSC_ID
496 ,ACCESS_TYPE
497 ,ORG_ID
498 ,OBJECT_VERSION_NUMBER
499 ,TRANS_ACCESS_CODE
500
501 )
502 SELECT
503 l_terr_rsc_access_id
504 ,SYSDATE
505 ,fnd_global.user_id
506 ,SYSDATE--CREATION_DATE
507 ,fnd_global.user_id
508 ,fnd_global.login_id
509 ,l_terr_rsc_id
510 --,l_access_type --commented out
511 , rsc_acc.access_type
515 FROM jtf_terr_rsc_all b
512 , l_org_id --org Id
513 , 1
514 , c.trans_access_code
516 , jtf_terr_rsc_access_all c
517 WHERE b.terr_id = l_parent_terr_id
518 AND b.role = tran_type.role_code
519 AND b.resource_type <> 'RS_EMPLOYEE'
520 AND b.terr_rsc_id = c.terr_rsc_id
521 AND c.access_type = rsc_acc.access_type;
522 -- SOLIN, BUG 5018824, end
523 --dbms_output.put_line(' After inserting jtf_terr_rsc_access_all');
524
525 END LOOP; --rsc_acc
526
527
528 END LOOP; --rsc
529
530 END LOOP; --tran_type
531
532 END; --populate resource table
533
534
535
536
537 IF (c_get_qtype_usgs%ISOPEN) THEN
538 CLOSE c_get_qtype_usgs;
539 END IF;
540
541 IF (c_role_code%ISOPEN) THEN
542 CLOSE c_role_code;
543 END IF;
544
545 IF (c_terr_resource%ISOPEN) THEN
546 CLOSE c_terr_resource;
547 END IF;
548
549 IF (c_role_access%ISOPEN) THEN
550 CLOSE c_role_access;
551 END IF;
552
553
554 COMMIT;
555
556 EXCEPTION
557 WHEN OTHERS THEN
558 RAISE;
559
560
561 END CREATE_TERR;
562
563
564
565 END JTF_TTY_CREATE_TERR_FROM_GEO;