[Home] [Help]
PACKAGE BODY: APPS.JTY_WEBADI_OTH_TERR_DWNL_PKG
Source
1 PACKAGE BODY jty_webadi_oth_terr_dwnl_pkg AS
2 /* $Header: jtfowdpb.pls 120.44.12010000.11 2009/01/27 11:52:55 gmarwah ship $ */
3 -- +===========================================================================+
4 -- | Copyright (c) 1999 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +===========================================================================+
8
9 -- Start of Comments
10 -- ---------------------------------------------------
11 -- PACKAGE NAME: JTY_OTH_WEBADI_PKG
12 -- ---------------------------------------------------
13 --
14 -- PURPOSE
15 --
16 -- WebAdi Other Territory Upload package.
17 --
18 --
19 -- Procedures:
20 -- (see below for specification)
21 --
22 -- NOTES
23 -- This package is publicly available for use
24 --
25 -- HISTORY
26 -- 08/18/2005 mhtran created
27
28 -- ===============================================================
29 -- End of Comments
30 --
31
32 PROCEDURE get_qual_header(p_usage_id IN NUMBER, p_org_id IN NUMBER, p_user_sequence IN NUMBER, x_row_count OUT nocopy NUMBER) IS
33
34 BEGIN
35
36 DELETE FROM jty_webadi_qual_header
37 WHERE user_sequence = p_user_sequence;
38
39 INSERT
40 INTO jty_webadi_qual_header(qualifier_num, user_sequence, qual_usg_id, qualifier_name, display_type,
41 operator_type, qual_cond_col_name, qual_val1_col_name, qual_val2_col_name, qual_val3_col_name,
42 html_lov_sql1, html_lov_sql2, html_lov_sql3, display_sql1, display_sql2, display_sql3,
43 convert_to_id_flag, comparison_operator)
44 SELECT rownum,
45 sub.*
46 FROM
47 (SELECT p_user_sequence,
48 qual.qual_usg_id,
49 --rownum,
50 qual.seeded_qual_name,
51 qual.display_type,
52 qual.hierarchy_type operator_type,
53 qual.seeded_qual_name || '.Condition',
54 qual.seeded_qual_name || '.Value1',
55 qual.seeded_qual_name || '.Value2',
56 qual.seeded_qual_name || '.Value3',
57 qual.html_lov_sql1,
58 decode(qual.display_type, 'CURRENCY', 'SELECT f.name col1_value, f.currency_code col2_value ' || 'FROM fnd_currencies_vl f ' || 'WHERE f.enabled_flag = ''Y'' ' || 'ORDER BY 1', qual.html_lov_sql2) html_lov_sql2,
59 qual.html_lov_sql3,
60 qual.display_sql1,
61 decode(qual.display_type, 'CURRENCY', 'SELECT f.name col1_value ' || 'FROM fnd_currencies_vl f ' || 'WHERE f.enabled_flag = ''Y'' ' || 'AND f.currency_code = ', qual.display_sql2) display_sql2,
62 qual.display_sql3,
63 convert_to_id_flag,
64 (
65 CASE
66 WHEN qual.equal_flag = 'Y' THEN
67 CASE
68 WHEN qual.like_flag = 'Y' THEN
69 CASE
70 WHEN qual.between_flag = 'Y' THEN '=,LIKE,BETWEEN'
71 ELSE '=,LIKE'
72 END
73 ELSE
74 CASE
75 WHEN qual.between_flag = 'Y' THEN '=,BETWEEN'
76 ELSE '='
77 END
78 END
79 ELSE
80 CASE
81 WHEN qual.like_flag = 'Y' THEN
82 CASE
83 WHEN qual.between_flag = 'Y' THEN 'LIKE,BETWEEN'
84 ELSE 'LIKE'
85 END
86 ELSE
87 CASE
88 WHEN qual.between_flag = 'Y' THEN ',BETWEEN'
89 ELSE ''
90 END
91 END
92 END) comparison_operator
93 FROM jtf_seeded_qual_usgs_v qual
94 WHERE qual.org_id = p_org_id
95 AND qual.source_id = p_usage_id
96 AND qual.enabled_flag = 'Y'
97 ORDER BY qual.html_lov_sql3,
98 qual.html_lov_sql2,
99 operator_type DESC)
100 sub;
101
102 x_row_count := SQL % rowcount;
103 COMMIT;
104
105 END get_qual_header;
106
107 PROCEDURE get_qual_type_header(p_usage_id IN INTEGER, p_user_sequence IN INTEGER) IS
108
109 BEGIN
110
111 DELETE FROM jty_webadi_qual_type_header
112 WHERE user_sequence = p_user_sequence;
113
114 INSERT
115 INTO jty_webadi_qual_type_header(qual_type_id, qual_type_num, qual_type_name, qual_type_descr, user_sequence)
116 (SELECT jqtu.qual_type_usg_id qual_type_id,
117 rownum,
118 jqt.name qual_type_name,
119 jqt.description qual_type_descr,
120 p_user_sequence
121 FROM jtf_qual_type_usgs_all jqtu,
122 jtf_qual_types_all jqt
123 WHERE jqtu.qual_type_id = jqt.qual_type_id
124 AND jqtu.source_id = p_usage_id)
125 ;
126
127 COMMIT;
128 END get_qual_type_header;
129
130 PROCEDURE dl_unassign_geography(p_org_id IN NUMBER, p_usage_id IN NUMBER, p_user_id IN NUMBER,
131 p_user_sequence IN NUMBER, p_interface_type IN VARCHAR2, p_terr_id IN NUMBER, p_geo_type IN NUMBER,
132 x_retcode OUT nocopy VARCHAR2, x_errbuf OUT nocopy VARCHAR2) IS
133
134 l_query VARCHAR2(30000);
135 l_qual_name VARCHAR2(150);
136 l_comp_oper VARCHAR2(30);
137 l_qual_val1 VARCHAR2(150);
138 l_qual_val2 VARCHAR2(150);
139 l_qual_num NUMBER;
140 l_loc_seg_id NUMBER;
141
142 BEGIN
143
144 -- get the start geo location value
145 SELECT qgt.qualifier_num,
146 REPLACE(LTRIM(LTRIM(UPPER(qualifier_name))), ' ', '_') qual_name
147 INTO l_qual_num,
148 l_qual_name
149 FROM jty_webadi_qual_header qgt
150 WHERE qgt.operator_type = 'GEOGRAPHY'
151 AND qgt.user_sequence = p_user_sequence
152 AND qgt.qual_usg_id = p_geo_type;
153
154 --dbms_output.put_line('l_qual_num, l_qual_name: '|| l_qual_num ||', '|| l_qual_name);
155 CASE l_qual_name
156 WHEN 'COUNTRY' THEN
157 x_retcode := fnd_api.g_ret_sts_error;
158 fnd_message.clear;
159 fnd_message.set_name('JTF', 'JTY_OTH_TERR_GEO_TYPE');
160 x_errbuf := fnd_message.GET();
161 ELSE
162 INSERT
163 INTO jty_webadi_oth_terr_intf(interface_type, org_id, usage_id, user_id, user_sequence,
164 qual1_value1, qual2_value1, qual3_value1, qual4_value1, qual5_value1, qual6_value1,
165 qual7_value1, qual8_value1, qual9_value1, qual10_value1, qual11_value1, qual12_value1,
166 qual13_value1, qual14_value1, qual15_value1, qual16_value1, qual17_value1, qual18_value1,
167 qual19_value1, qual20_value1, qual21_value1, qual22_value1, qual23_value1, qual24_value1,
168 qual25_value1)
169 SELECT p_interface_type,
170 p_org_id,
171 p_usage_id,
172 p_user_id,
173 p_user_sequence,
174 decode(l_qual_num, 1, geography_name, NULL) qual1_value1,
175 decode(l_qual_num, 2, geography_name, NULL) qual2_value1,
176 decode(l_qual_num, 3, geography_name, NULL) qual3_value1,
177 decode(l_qual_num, 4, geography_name, NULL) qual4_value1,
178 decode(l_qual_num, 5, geography_name, NULL) qual5_value1,
179 decode(l_qual_num, 6, geography_name, NULL) qual6_value1,
180 decode(l_qual_num, 7, geography_name, NULL) qual7_value1,
181 decode(l_qual_num, 8, geography_name, NULL) qual8_value1,
182 decode(l_qual_num, 9, geography_name, NULL) qual9_value1,
183 decode(l_qual_num, 10, geography_name, NULL) qual10_value1,
184 decode(l_qual_num, 11, geography_name, NULL) qual11_value1,
185 decode(l_qual_num, 12, geography_name, NULL) qual12_value1,
186 decode(l_qual_num, 13, geography_name, NULL) qual13_value1,
187 decode(l_qual_num, 14, geography_name, NULL) qual14_value1,
188 decode(l_qual_num, 15, geography_name, NULL) qual15_value1,
189 decode(l_qual_num, 16, geography_name, NULL) qual16_value1,
190 decode(l_qual_num, 17, geography_name, NULL) qual17_value1,
191 decode(l_qual_num, 18, geography_name, NULL) qual18_value1,
192 decode(l_qual_num, 19, geography_name, NULL) qual19_value1,
193 decode(l_qual_num, 20, geography_name, NULL) qual20_value1,
194 decode(l_qual_num, 21, geography_name, NULL) qual21_value1,
195 decode(l_qual_num, 22, geography_name, NULL) qual22_value1,
196 decode(l_qual_num, 23, geography_name, NULL) qual23_value1,
197 decode(l_qual_num, 24, geography_name, NULL) qual24_value1,
198 decode(l_qual_num, 25, geography_name, NULL) qual25_value1
199 FROM hz_geographies hzg,
200 jtf_terr_values_all qv,
201 jtf_terr_qual_all jtq
202 WHERE hzg.geography_type = l_qual_name
203 AND jtq.terr_qual_id = qv.terr_qual_id
204 AND qv.low_value_char IN(geography_element1_code, geography_element2_code, geography_element3_code, geography_element4_code, geography_element5_code)
205 AND jtq.org_id = p_org_id
206 AND jtq.terr_id = p_terr_id
207 AND NOT EXISTS
208 (SELECT 1
209 FROM jtf_terr_values_all qv,
210 jtf_terr_qual_all jtq,
211 jtf_terr_all terr
212 WHERE hzg.geography_name = qv.low_value_char
213 AND jtq.terr_qual_id = qv.terr_qual_id
214 AND jtq.org_id = p_org_id
215 AND jtq.terr_id = terr.terr_id
216 AND nvl(terr.terr_group_flag, 'N') = 'N'
217 AND terr.enabled_flag = 'Y'
218 AND nvl(terr.enable_self_service, 'N') = 'N'
219 AND terr.parent_territory_id = p_terr_id
220 AND terr.org_id = p_org_id)
221 ;
222 END
223 CASE;
224
225 x_retcode := fnd_api.g_ret_sts_success;
226 x_errbuf := 'Success';
227
228 COMMIT;
229
230 EXCEPTION
231 WHEN no_data_found THEN
232 x_retcode := fnd_api.g_ret_sts_error;
233 fnd_message.clear;
234 fnd_message.set_name('JTF', 'JTY_OTH_TERR_GEO_TYPE');
235 x_errbuf := fnd_message.GET();
236 --raise_application_error(-20000, 'No geography defined for this territory');
237 WHEN others THEN
238 x_retcode := fnd_api.g_ret_sts_error;
239 fnd_message.clear;
240 fnd_message.set_name('JTF', 'JTY_OTH_TERR_GEO_TYPE');
241 fnd_message.set_token('POSITION', sqlerrm);
242 x_errbuf := fnd_message.GET();
243
244 END dl_unassign_geography;
245
246 FUNCTION get_resource_name(p_resource_type VARCHAR2, p_resource_id NUMBER, p_group_id NUMBER, p_role_id VARCHAR2, p_role VARCHAR2) RETURN VARCHAR IS
247 CURSOR c_grp_name IS
248 SELECT group_name
249 FROM jtf_rs_groups_tl
250 WHERE group_id = p_group_id
251 AND LANGUAGE = userenv('LANG');
252
253 CURSOR c_team_name IS
254 SELECT team_name
255 FROM jtf_rs_teams_tl
256 WHERE team_id = p_resource_id
257 AND LANGUAGE = userenv('LANG');
258
259 CURSOR c_role_name IS
260 SELECT jrrt.role_name
261 FROM jtf_rs_roles_tl jrrt,
262 jtf_rs_roles_b jrrb
263 WHERE(p_role_id IS NULL OR jrrt.role_id = p_role_id)
264 AND jrrb.role_code(+) = p_role
265 AND jrrb.role_id = jrrt.role_id(+)
266 AND jrrt.LANGUAGE = userenv('LANG');
267
268 CURSOR c_resource_name IS
269 SELECT resource_name
270 FROM jtf_rs_resource_extns_tl
271 WHERE resource_id = p_resource_id
272 AND LANGUAGE = userenv('LANG');
273 l_name VARCHAR2(250) := NULL;
274 BEGIN
275
276 IF p_resource_type = 'RS_GROUP' THEN
277
278 OPEN c_grp_name;
279 FETCH c_grp_name
280 INTO l_name;
281 CLOSE c_grp_name;
282 ELSIF p_resource_type = 'RS_TEAM' THEN
283
284 OPEN c_team_name;
285 FETCH c_team_name
286 INTO l_name;
287 CLOSE c_team_name;
288 ELSIF p_resource_type = 'RS_ROLE' THEN
289
290 OPEN c_role_name;
291 FETCH c_role_name
292 INTO l_name;
293 CLOSE c_role_name;
294 ELSE
295
296 OPEN c_resource_name;
297 FETCH c_resource_name
298 INTO l_name;
299 CLOSE c_resource_name;
300 END IF;
301
302 RETURN l_name;
303 END get_resource_name;
304
305 FUNCTION get_group_name(p_resource_type VARCHAR, p_group_id NUMBER, p_resource_id NUMBER) RETURN VARCHAR IS
306 CURSOR c_ind_grp_name IS
307 SELECT group_name
308 FROM jtf_rs_groups_tl
309 WHERE group_id = p_group_id
310 AND LANGUAGE = userenv('LANG');
311
312 CURSOR c_grp_grp_name IS
313 select JRGV.GROUP_NAME from
314 JTF_RS_GROUPS_VL JRGV, JTF_RS_GROUP_MEMBERS_VL jrgmv
315 where jrgv.group_ID = jrgmv.group_ID
316 AND jrgmv.RESOURCE_ID = p_resource_id;
317
318 l_group_name VARCHAR2(250) := NULL;
319 BEGIN
320
321 IF p_resource_type = 'RS_GROUP' THEN
322 OPEN c_grp_grp_name;
323 FETCH c_grp_grp_name
324 INTO l_group_name;
325 CLOSE c_grp_grp_name;
326 RETURN l_group_name;
327 ELSIF p_resource_type = 'RS_TEAM' THEN
328 RETURN NULL;
329 ELSIF p_resource_type = 'RS_ROLE' THEN
330 RETURN NULL;
331 ELSE
332 OPEN c_ind_grp_name;
333 FETCH c_ind_grp_name
334 INTO l_group_name;
335 CLOSE c_ind_grp_name;
336 RETURN l_group_name;
337 END IF;
338
339 END get_group_name;
340
341 FUNCTION get_role_name(p_resource_type VARCHAR, p_role VARCHAR2) RETURN VARCHAR IS CURSOR c_role_name IS
342 SELECT jrrt.role_name
343 FROM jtf_rs_roles_tl jrrt,
344 jtf_rs_roles_b jrrb
345 WHERE jrrt.role_id = jrrb.role_id
346 AND language = 'US'
347 AND jrrb.role_code = p_role;
348
349 l_role_name VARCHAR2(250) := NULL;
350 BEGIN
351 IF p_resource_type = 'RS_TEAM' THEN
352 RETURN NULL;
353 ELSIF p_resource_type = 'RS_ROLE' THEN
354 RETURN NULL;
355 ELSE
359 CLOSE c_role_name;
356 OPEN c_role_name;
357 FETCH c_role_name
358 INTO l_role_name;
360 RETURN l_role_name;
361 END IF;
362
363 END get_role_name;
364
365 FUNCTION get_email(p_resource_type VARCHAR, p_resource_id NUMBER) RETURN VARCHAR IS
366 CURSOR c_email IS
367 SELECT source_email
368 FROM jtf_rs_resource_extns
369 WHERE resource_id = p_resource_id;
370 CURSOR c_grp_email IS
371 SELECT EMAIL FROM jtf_rs_resources_vl
372 WHERE RESOURCE_ID = p_resource_id and resource_type = p_resource_type;
373
374 l_email VARCHAR2(2000);
375 BEGIN
376 IF p_resource_type = 'RS_GROUP' THEN
377 OPEN c_grp_email;
378 FETCH c_grp_email
379 INTO l_email;
380 CLOSE c_grp_email;
381 RETURN l_email;
382 ELSE
383 OPEN c_email;
384 FETCH c_email
385 INTO l_email;
386 CLOSE c_email;
387 RETURN l_email;
388 END IF;
389 END get_email;
390
391 PROCEDURE dl_all_territories(p_user_sequence IN NUMBER, p_user_id IN NUMBER, p_org_id IN NUMBER,
392 p_usage_id IN NUMBER, p_interface_type IN VARCHAR2, p_terr_rec IN terr_rec_type,
393 x_retcode OUT nocopy VARCHAR2, x_errbuf OUT nocopy VARCHAR2) IS
394
395 l_header VARCHAR2(15);
396 l_today_date DATE := sysdate;
397 l_sql VARCHAR2(31000);
398 l_action_flag VARCHAR2(1) := 'U';
399 l_row_count NUMBER;
400
401 BEGIN
402
403 -- get access details information
404
405 IF p_terr_rec.terr_id.COUNT > 0 THEN
406 l_header := 'TERR';
407 forall i IN p_terr_rec.terr_id.FIRST .. p_terr_rec.terr_id.LAST INSERT
408 INTO jty_webadi_oth_terr_intf(interface_type, action_flag, header, user_sequence, user_id, org_id,
409 org_name, usage_id, terr_id, terr_name, terr_type_name, terr_type_id, rank, num_winners,
410 terr_start_date, terr_end_date, parent_terr_id, hierarchy, attribute_category, attribute1,
411 attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8,
412 attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15,
413 creation_date, created_by, last_update_date, last_updated_by, last_update_login)
414 VALUES(p_interface_type, l_action_flag, l_header, p_user_sequence, p_user_id, p_org_id,
415 p_terr_rec.org_name(i), p_usage_id, p_terr_rec.terr_id(i), p_terr_rec.terr_name(i),
416 p_terr_rec.terr_type_name(i), p_terr_rec.terr_type_id(i), p_terr_rec.rank(i),
417 p_terr_rec.num_winners(i), p_terr_rec.start_date(i), p_terr_rec.end_date(i),
418 p_terr_rec.parent_terr_id(i), p_terr_rec.hierarchy(i), p_terr_rec.attribute_category(i),
419 p_terr_rec.attribute1(i), p_terr_rec.attribute2(i), p_terr_rec.attribute3(i),
420 p_terr_rec.attribute4(i), p_terr_rec.attribute5(i), p_terr_rec.attribute6(i),
421 p_terr_rec.attribute7(i), p_terr_rec.attribute8(i), p_terr_rec.attribute9(i),
422 p_terr_rec.attribute10(i), p_terr_rec.attribute11(i), p_terr_rec.attribute12(i),
423 p_terr_rec.attribute13(i), p_terr_rec.attribute14(i), p_terr_rec.attribute15(i),
424 l_today_date, p_user_id, l_today_date, p_user_id, p_user_id);
425
426 l_header := 'QUAL';
427
428 forall i IN p_terr_rec.terr_id.FIRST .. p_terr_rec.terr_id.LAST INSERT
429 INTO jty_webadi_oth_terr_intf(interface_type, action_flag, header, user_sequence, user_id,
430 org_id, org_name, usage_id, terr_id, terr_name, terr_type_name, terr_type_id, rank,
431 num_winners, terr_start_date, terr_end_date, parent_terr_id, hierarchy, creation_date,
432 created_by, last_update_date, last_updated_by, last_update_login, row_id, qual1_value_id,
433 qual1_value1, qual1_value2, qual1_value3, qual2_value_id, qual2_value1, qual2_value2,
434 qual2_value3, qual3_value_id, qual3_value1, qual3_value2, qual3_value3, qual4_value_id,
435 qual4_value1, qual4_value2, qual4_value3, qual5_value_id, qual5_value1, qual5_value2,
436 qual5_value3, qual6_value_id, qual6_value1, qual6_value2, qual6_value3, qual7_value_id,
437 qual7_value1, qual7_value2, qual7_value3, qual8_value_id, qual8_value1, qual8_value2,
438 qual8_value3, qual9_value_id, qual9_value1, qual9_value2, qual9_value3, qual10_value_id,
439 qual10_value1, qual10_value2, qual10_value3, qual11_value_id, qual11_value1, qual11_value2,
440 qual11_value3, qual12_value_id, qual12_value1, qual12_value2, qual12_value3, qual13_value_id,
441 qual13_value1, qual13_value2, qual13_value3, qual14_value_id, qual14_value1, qual14_value2,
442 qual14_value3, qual15_value_id, qual15_value1, qual15_value2, qual15_value3, qual16_value_id,
443 qual16_value1, qual16_value2, qual16_value3, qual17_value_id, qual17_value1, qual17_value2,
444 qual17_value3, qual18_value_id, qual18_value1, qual18_value2, qual18_value3, qual19_value_id,
445 qual19_value1, qual19_value2, qual19_value3, qual20_value_id, qual20_value1, qual20_value2,
446 qual20_value3, qual21_value_id, qual21_value1, qual21_value2, qual21_value3, qual22_value_id,
447 qual22_value1, qual22_value2, qual22_value3, qual23_value_id, qual23_value1, qual23_value2,
448 qual23_value3, qual24_value_id, qual24_value1, qual24_value2, qual24_value3, qual25_value_id,
449 qual25_value1, qual25_value2, qual25_value3)
450 SELECT p_interface_type,
451 l_action_flag,
452 l_header,
453 p_user_sequence,
454 p_user_id,
458 p_terr_rec.terr_id(i),
455 p_org_id,
456 p_terr_rec.org_name(i),
457 p_usage_id,
459 p_terr_rec.terr_name(i),
460 p_terr_rec.terr_type_name(i),
461 p_terr_rec.terr_type_id(i),
462 p_terr_rec.rank(i),
463 p_terr_rec.num_winners(i),
464 p_terr_rec.start_date(i),
465 p_terr_rec.end_date(i),
466 p_terr_rec.parent_terr_id(i),
467 p_terr_rec.hierarchy(i),
468 l_today_date,
469 p_user_id,
470 l_today_date,
471 p_user_id,
472 p_user_id,
473 row_id,
474 qual1_value_id,
475 qual1_value1,
476 qual1_value2,
477 qual1_value3,
478 qual2_value_id,
479 qual2_value1,
480 qual2_value2,
481 qual2_value3,
482 qual3_value_id,
483 qual3_value1,
484 qual3_value2,
485 qual3_value3,
486 qual4_value_id,
487 qual4_value1,
488 qual4_value2,
489 qual4_value3,
490 qual5_value_id,
491 qual5_value1,
492 qual5_value2,
493 qual5_value3,
494 qual6_value_id,
495 qual6_value1,
496 qual6_value2,
497 qual6_value3,
498 qual7_value_id,
499 qual7_value1,
500 qual7_value2,
501 qual7_value3,
502 qual8_value_id,
503 qual8_value1,
504 qual8_value2,
505 qual8_value3,
506 qual9_value_id,
507 qual9_value1,
508 qual9_value2,
509 qual9_value3,
510 qual10_value_id,
511 qual10_value1,
512 qual10_value2,
513 qual10_value3,
514 qual11_value_id,
515 qual11_value1,
516 qual11_value2,
517 qual11_value3,
518 qual12_value_id,
519 qual12_value1,
520 qual12_value2,
521 qual12_value3,
522 qual13_value_id,
523 qual13_value1,
524 qual13_value2,
525 qual13_value3,
526 qual14_value_id,
527 qual14_value1,
528 qual14_value2,
529 qual14_value3,
530 qual15_value_id,
531 qual15_value1,
532 qual15_value2,
533 qual15_value3,
534 qual16_value_id,
535 qual16_value1,
536 qual16_value2,
537 qual16_value3,
538 qual17_value_id,
539 qual17_value1,
540 qual17_value2,
541 qual17_value3,
542 qual18_value_id,
543 qual18_value1,
544 qual18_value2,
545 qual18_value3,
546 qual19_value_id,
547 qual19_value1,
548 qual19_value2,
549 qual19_value3,
550 qual20_value_id,
551 qual20_value1,
552 qual20_value2,
553 qual20_value3,
554 qual21_value_id,
555 qual21_value1,
556 qual21_value2,
557 qual21_value3,
558 qual22_value_id,
559 qual22_value1,
560 qual22_value2,
561 qual22_value3,
562 qual23_value_id,
563 qual23_value1,
564 qual23_value2,
565 qual23_value3,
566 qual24_value_id,
567 qual24_value1,
568 qual24_value2,
569 qual24_value3,
570 qual25_value_id,
571 qual25_value1,
572 qual25_value2,
573 qual25_value3
574 FROM
575 (SELECT row_id,
576 MAX(decode(qualifier_num, 1, terr_value_id)) qual1_value_id,
577 MAX(decode(qualifier_num, 1, qual_value1)) qual1_value1,
578 MAX(decode(qualifier_num, 1, qual_value2)) qual1_value2,
579 MAX(decode(qualifier_num, 1, qual_value3)) qual1_value3,
580 MAX(decode(qualifier_num, 2, terr_value_id)) qual2_value_id,
581 MAX(decode(qualifier_num, 2, qual_value1)) qual2_value1,
582 MAX(decode(qualifier_num, 2, qual_value2)) qual2_value2,
583 MAX(decode(qualifier_num, 2, qual_value3)) qual2_value3,
584 MAX(decode(qualifier_num, 3, terr_value_id)) qual3_value_id,
585 MAX(decode(qualifier_num, 3, qual_value1)) qual3_value1,
586 MAX(decode(qualifier_num, 3, qual_value2)) qual3_value2,
587 MAX(decode(qualifier_num, 3, qual_value3)) qual3_value3,
588 MAX(decode(qualifier_num, 4, terr_value_id)) qual4_value_id,
589 MAX(decode(qualifier_num, 4, qual_value1)) qual4_value1,
590 MAX(decode(qualifier_num, 4, qual_value2)) qual4_value2,
591 MAX(decode(qualifier_num, 4, qual_value3)) qual4_value3,
592 MAX(decode(qualifier_num, 5, terr_value_id)) qual5_value_id,
593 MAX(decode(qualifier_num, 5, qual_value1)) qual5_value1,
594 MAX(decode(qualifier_num, 5, qual_value2)) qual5_value2,
595 MAX(decode(qualifier_num, 5, qual_value3)) qual5_value3,
596 MAX(decode(qualifier_num, 6, terr_value_id)) qual6_value_id,
597 MAX(decode(qualifier_num, 6, qual_value1)) qual6_value1,
598 MAX(decode(qualifier_num, 6, qual_value2)) qual6_value2,
599 MAX(decode(qualifier_num, 6, qual_value3)) qual6_value3,
600 MAX(decode(qualifier_num, 7, terr_value_id)) qual7_value_id,
601 MAX(decode(qualifier_num, 7, qual_value1)) qual7_value1,
602 MAX(decode(qualifier_num, 7, qual_value2)) qual7_value2,
603 MAX(decode(qualifier_num, 7, qual_value3)) qual7_value3,
607 MAX(decode(qualifier_num, 8, qual_value3)) qual8_value3,
604 MAX(decode(qualifier_num, 8, terr_value_id)) qual8_value_id,
605 MAX(decode(qualifier_num, 8, qual_value1)) qual8_value1,
606 MAX(decode(qualifier_num, 8, qual_value2)) qual8_value2,
608 MAX(decode(qualifier_num, 9, terr_value_id)) qual9_value_id,
609 MAX(decode(qualifier_num, 9, qual_value1)) qual9_value1,
610 MAX(decode(qualifier_num, 9, qual_value2)) qual9_value2,
611 MAX(decode(qualifier_num, 9, qual_value3)) qual9_value3,
612 MAX(decode(qualifier_num, 10, terr_value_id)) qual10_value_id,
613 MAX(decode(qualifier_num, 10, qual_value1)) qual10_value1,
614 MAX(decode(qualifier_num, 10, qual_value2)) qual10_value2,
615 MAX(decode(qualifier_num, 10, qual_value3)) qual10_value3,
616 MAX(decode(qualifier_num, 11, terr_value_id)) qual11_value_id,
617 MAX(decode(qualifier_num, 11, qual_value1)) qual11_value1,
618 MAX(decode(qualifier_num, 11, qual_value2)) qual11_value2,
619 MAX(decode(qualifier_num, 11, qual_value3)) qual11_value3,
620 MAX(decode(qualifier_num, 12, terr_value_id)) qual12_value_id,
621 MAX(decode(qualifier_num, 12, qual_value1)) qual12_value1,
622 MAX(decode(qualifier_num, 12, qual_value2)) qual12_value2,
623 MAX(decode(qualifier_num, 12, qual_value3)) qual12_value3,
624 MAX(decode(qualifier_num, 13, terr_value_id)) qual13_value_id,
625 MAX(decode(qualifier_num, 13, qual_value1)) qual13_value1,
626 MAX(decode(qualifier_num, 13, qual_value2)) qual13_value2,
627 MAX(decode(qualifier_num, 13, qual_value3)) qual13_value3,
628 MAX(decode(qualifier_num, 14, terr_value_id)) qual14_value_id,
629 MAX(decode(qualifier_num, 14, qual_value1)) qual14_value1,
630 MAX(decode(qualifier_num, 14, qual_value2)) qual14_value2,
631 MAX(decode(qualifier_num, 14, qual_value3)) qual14_value3,
632 MAX(decode(qualifier_num, 15, terr_value_id)) qual15_value_id,
633 MAX(decode(qualifier_num, 15, qual_value1)) qual15_value1,
634 MAX(decode(qualifier_num, 15, qual_value2)) qual15_value2,
635 MAX(decode(qualifier_num, 15, qual_value3)) qual15_value3,
636 MAX(decode(qualifier_num, 16, terr_value_id)) qual16_value_id,
637 MAX(decode(qualifier_num, 16, qual_value1)) qual16_value1,
638 MAX(decode(qualifier_num, 16, qual_value2)) qual16_value2,
639 MAX(decode(qualifier_num, 16, qual_value3)) qual16_value3,
640 MAX(decode(qualifier_num, 17, terr_value_id)) qual17_value_id,
641 MAX(decode(qualifier_num, 17, qual_value1)) qual17_value1,
642 MAX(decode(qualifier_num, 17, qual_value2)) qual17_value2,
643 MAX(decode(qualifier_num, 17, qual_value3)) qual17_value3,
644 MAX(decode(qualifier_num, 18, terr_value_id)) qual18_value_id,
645 MAX(decode(qualifier_num, 18, qual_value1)) qual18_value1,
646 MAX(decode(qualifier_num, 18, qual_value2)) qual18_value2,
647 MAX(decode(qualifier_num, 18, qual_value3)) qual18_value3,
648 MAX(decode(qualifier_num, 19, terr_value_id)) qual19_value_id,
649 MAX(decode(qualifier_num, 19, qual_value1)) qual19_value1,
650 MAX(decode(qualifier_num, 19, qual_value2)) qual19_value2,
651 MAX(decode(qualifier_num, 19, qual_value3)) qual19_value3,
652 MAX(decode(qualifier_num, 20, terr_value_id)) qual20_value_id,
653 MAX(decode(qualifier_num, 20, qual_value1)) qual20_value1,
654 MAX(decode(qualifier_num, 20, qual_value2)) qual20_value2,
655 MAX(decode(qualifier_num, 20, qual_value3)) qual20_value3,
656 MAX(decode(qualifier_num, 21, terr_value_id)) qual21_value_id,
657 MAX(decode(qualifier_num, 21, qual_value1)) qual21_value1,
658 MAX(decode(qualifier_num, 21, qual_value2)) qual21_value2,
659 MAX(decode(qualifier_num, 21, qual_value3)) qual21_value3,
660 MAX(decode(qualifier_num, 22, terr_value_id)) qual22_value_id,
661 MAX(decode(qualifier_num, 22, qual_value1)) qual22_value1,
662 MAX(decode(qualifier_num, 22, qual_value2)) qual22_value2,
663 MAX(decode(qualifier_num, 22, qual_value3)) qual22_value3,
664 MAX(decode(qualifier_num, 23, terr_value_id)) qual23_value_id,
665 MAX(decode(qualifier_num, 23, qual_value1)) qual23_value1,
666 MAX(decode(qualifier_num, 23, qual_value2)) qual23_value2,
667 MAX(decode(qualifier_num, 23, qual_value3)) qual23_value3,
668 MAX(decode(qualifier_num, 24, terr_value_id)) qual24_value_id,
669 MAX(decode(qualifier_num, 24, qual_value1)) qual24_value1,
670 MAX(decode(qualifier_num, 24, qual_value2)) qual24_value2,
671 MAX(decode(qualifier_num, 24, qual_value3)) qual24_value3,
672 MAX(decode(qualifier_num, 25, terr_value_id)) qual25_value_id,
673 MAX(decode(qualifier_num, 25, qual_value1)) qual25_value1,
674 MAX(decode(qualifier_num, 25, qual_value2)) qual25_value2,
675 MAX(decode(qualifier_num, 25, qual_value3)) qual25_value3
676 FROM
677 (SELECT rank() over(PARTITION BY jtq.terr_id, qgt.qual_usg_id
678 ORDER BY rownum) row_id,
679 jtva.terr_value_id,
680 qgt.qualifier_num,
684 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
681 decode(qgt.display_type, 'CHAR', decode(qgt.convert_to_id_flag, 'Y',
682 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
683 qgt.display_sql1, jtva.low_value_char_id, NULL), 'N',
685 qgt.display_sql1, jtva.low_value_char, NULL), NULL), 'CHAR_2IDS',
686 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
687 qgt.display_sql1, jtva.value1_id, jtva.value2_id), 'DEP_2FIELDS',
688 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
689 qgt.display_sql1, jtva.value1_id, -9999), 'DEP_2FIELDS_1CHAR_1ID',
690 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
691 qgt.display_sql1, jtva.low_value_char, NULL), 'DEP_2FIELDS_CHAR_2IDS',
692 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
693 qgt.display_sql1, jtva.value1_id, -9999), 'DEP_3FIELDS',
694 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
695 qgt.display_sql1, jtva.value1_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',
696 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
697 qgt.display_sql1, jtva.value1_id, -9999), 'INTEREST_TYPE',
698 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
699 qgt.display_sql1, jtva.interest_type_id, NULL), 'NUMERIC', jtva.low_value_number, 'CURRENCY',
700 jtva.low_value_number, NULL) qual_value1,
701 decode(qgt.display_type, 'CHAR', jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag,
702 qgt.display_type, NULL, qgt.display_sql2, jtva.high_value_char, NULL), 'CHAR_2IDS', NULL,
703 'DEP_2FIELDS', jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
704 qgt.display_sql2, jtva.value2_id, -9999), 'DEP_2FIELDS_1CHAR_1ID',
705 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
706 qgt.display_sql2, jtva.low_value_char_id, NULL), 'DEP_2FIELDS_CHAR_2IDS',
707 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
708 qgt.display_sql2, jtva.value2_id, -9999), 'DEP_3FIELDS',
709 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
710 qgt.display_sql2, jtva.value2_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',
711 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
712 qgt.display_sql2, jtva.value2_id, -9999), 'INTEREST_TYPE',
713 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
717 qgt.display_type, NULL, qgt.display_sql3, jtva.value3_id, NULL), 'DEP_3FIELDS_CHAR_3IDS',
714 qgt.display_sql2, jtva.primary_interest_code_id, NULL), 'NUMERIC', jtva.high_value_number,
715 'CURRENCY', jtva.high_value_number, NULL) qual_value2,
716 decode(qgt.display_type, 'DEP_3FIELDS', jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag,
718 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
719 qgt.display_sql3, jtva.value3_id, jtva.value4_id), 'INTEREST_TYPE',
720 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
721 qgt.display_sql3, jtva.secondary_interest_code_id, NULL), 'CURRENCY',
722 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
723 qgt.display_sql2, jtva.currency_code, NULL), NULL) qual_value3,
724 NULL qual_value4
725 FROM jty_webadi_qual_header qgt,
726 jtf_terr_values_all jtva,
727 jtf_terr_qual_all jtq
728 WHERE qgt.qual_usg_id = jtq.qual_usg_id
729 AND jtq.terr_qual_id = jtva.terr_qual_id --AND qgt.qualifier_num = 1
730 AND jtq.org_id = p_org_id
731 AND qgt.user_sequence = p_user_sequence
732 AND jtq.terr_id = p_terr_rec.terr_id(i))
733 GROUP BY row_id)
734 ;
735
736 l_header := 'RSC';
737
738 forall i IN p_terr_rec.terr_id.FIRST .. p_terr_rec.terr_id.LAST
739 INSERT INTO jty_webadi_resources(user_sequence, interface_type, header, terr_id, terr_rsc_id,
740 resource_name, resource_group, resource_role, resource_id, group_id, role_code,
741 resource_type, res_start_date, res_end_date, email, attribute_category, attribute1,
742 attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8,
743 attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15,
744 trans_access_code1, terr_rsc_access_id1, trans_access_code2, terr_rsc_access_id2,
745 trans_access_code3, terr_rsc_access_id3, trans_access_code4, terr_rsc_access_id4,
746 trans_access_code5, terr_rsc_access_id5, trans_access_code6, terr_rsc_access_id6,
747 trans_access_code7, terr_rsc_access_id7, trans_access_code8, terr_rsc_access_id8,
748 trans_access_code9, terr_rsc_access_id9, trans_access_code10, terr_rsc_access_id10)
749 SELECT terr_rsc.user_sequence,
750 p_interface_type,
751 l_header,
752 terr_rsc.terr_id,
753 terr_rsc.terr_rsc_id,
754 get_resource_name(terr_rsc.resource_type, terr_rsc.resource_id, terr_rsc.group_id, terr_rsc.role_id, terr_rsc.role) resource_name,
755 get_group_name(terr_rsc.resource_type, terr_rsc.group_id, terr_rsc.resource_id) resource_group,
756 get_role_name(terr_rsc.resource_type, terr_rsc.role) resource_role,
757 terr_rsc.resource_id resource_id,
758 terr_rsc.group_id group_id,
759 terr_rsc.role role_code,
760 decode(terr_rsc.resource_type, 'RS_GROUP', 1, 'RS_TEAM', 2, 'RS_ROLE', 3, 0) resource_type,
761 terr_rsc.start_date,
762 terr_rsc.end_date,
763 get_email(terr_rsc.resource_type, terr_rsc.resource_id),
764 terr_rsc.attribute_category,
765 terr_rsc.attribute1,
766 terr_rsc.attribute2,
767 terr_rsc.attribute3,
768 terr_rsc.attribute4,
769 terr_rsc.attribute5,
770 terr_rsc.attribute6,
771 terr_rsc.attribute7,
772 terr_rsc.attribute8,
773 terr_rsc.attribute9,
774 terr_rsc.attribute10,
775 terr_rsc.attribute11,
776 terr_rsc.attribute12,
777 terr_rsc.attribute13,
778 terr_rsc.attribute14,
779 terr_rsc.attribute15,
780 terr_rsc.qual_type1_val,
781 terr_rsc.terr_rsc_access_id1,
782 terr_rsc.qual_type2_val,
783 terr_rsc.terr_rsc_access_id2,
784 terr_rsc.qual_type3_val,
785 terr_rsc.terr_rsc_access_id3,
786 terr_rsc.qual_type4_val,
787 terr_rsc.terr_rsc_access_id4,
788 terr_rsc.qual_type5_val,
789 terr_rsc.terr_rsc_access_id5,
790 terr_rsc.qual_type6_val,
791 terr_rsc.terr_rsc_access_id6,
792 terr_rsc.qual_type7_val,
793 terr_rsc.terr_rsc_access_id7,
794 terr_rsc.qual_type8_val,
795 terr_rsc.terr_rsc_access_id8,
796 terr_rsc.qual_type9_val,
797 terr_rsc.terr_rsc_access_id9,
798 terr_rsc.qual_type10_val,
799 terr_rsc.terr_rsc_access_id10
800 FROM
801 (SELECT jqth.user_sequence,
802 jtr.terr_id,
803 jtr.resource_type,
804 jtr.terr_rsc_id,
805 jtr.resource_id,
806 decode(jtr.resource_type, 'RS_GROUP', jtr.resource_id, jtr.group_id) group_id,
807 decode(jtr.resource_type, 'RS_ROLE', jtr.resource_id, NULL) role_id,
808 jtr.role,
809 jtr.start_date_active start_date,
810 jtr.end_date_active end_date,
811 jtr.attribute_category,
812 jtr.attribute1,
813 jtr.attribute2,
814 jtr.attribute3,
815 jtr.attribute4,
816 jtr.attribute5,
817 jtr.attribute6,
818 jtr.attribute7,
819 jtr.attribute8,
820 jtr.attribute9,
821 jtr.attribute10,
822 jtr.attribute11,
823 jtr.attribute12,
824 jtr.attribute13,
825 jtr.attribute14,
826 jtr.attribute15,
830 MAX(decode(jqth.qual_type_num, 2, fnd.description, NULL)) qual_type2_val,
827 MAX(decode(jqth.qual_type_num, 1, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id1,
828 MAX(decode(jqth.qual_type_num, 1, fnd.description, NULL)) qual_type1_val,
829 MAX(decode(jqth.qual_type_num, 2, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id2,
831 MAX(decode(jqth.qual_type_num, 3, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id3,
832 MAX(decode(jqth.qual_type_num, 3, fnd.description, NULL)) qual_type3_val,
833 MAX(decode(jqth.qual_type_num, 4, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id4,
834 MAX(decode(jqth.qual_type_num, 4, fnd.description, NULL)) qual_type4_val,
835 MAX(decode(jqth.qual_type_num, 5, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id5,
836 MAX(decode(jqth.qual_type_num, 5, fnd.description, NULL)) qual_type5_val,
837 MAX(decode(jqth.qual_type_num, 6, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id6,
838 MAX(decode(jqth.qual_type_num, 6, fnd.description, NULL)) qual_type6_val,
839 MAX(decode(jqth.qual_type_num, 7, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id7,
840 MAX(decode(jqth.qual_type_num, 7, fnd.description, NULL)) qual_type7_val,
841 MAX(decode(jqth.qual_type_num, 8, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id8,
842 MAX(decode(jqth.qual_type_num, 8, fnd.description, NULL)) qual_type8_val,
843 MAX(decode(jqth.qual_type_num, 9, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id9,
844 MAX(decode(jqth.qual_type_num, 9, fnd.description, NULL)) qual_type9_val,
845 MAX(decode(jqth.qual_type_num, 10, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id10,
846 MAX(decode(jqth.qual_type_num, 10, fnd.description, NULL)) qual_type10_val
847 FROM jtf_sources_all jsa,
848 jtf_terr_rsc_access_all jtra,
849 jty_webadi_qual_type_header jqth,
850 jtf_terr_rsc_all jtr,
851 fnd_lookups fnd
852 WHERE jsa.rsc_access_lkup = fnd.lookup_type
853 AND jsa.source_id = p_usage_id
854 AND jtra.trans_access_code = fnd.lookup_code
855 AND jtra.access_type(+) = jqth.qual_type_name
856 AND jqth.user_sequence = p_user_sequence
857 AND jtr.terr_id = p_terr_rec.terr_id(i)
858 AND jtra.terr_rsc_id = jtr.terr_rsc_id
859 GROUP BY jtr.terr_id,
860 jqth.user_sequence,
861 jtr.terr_rsc_id,
862 jtr.resource_id,
863 jtr.group_id,
864 jtr.role,
865 jtr.resource_type,
866 jtr.start_date_active,
867 jtr.end_date_active,
868 jtr.attribute_category,
869 jtr.attribute1,
870 jtr.attribute2,
871 jtr.attribute3,
872 jtr.attribute4,
873 jtr.attribute5,
874 jtr.attribute6,
875 jtr.attribute7,
876 jtr.attribute8,
877 jtr.attribute9,
878 jtr.attribute10,
879 jtr.attribute11,
880 jtr.attribute12,
881 jtr.attribute13,
882 jtr.attribute14,
883 jtr.attribute15)
884 terr_rsc;
885
886 forall i IN p_terr_rec.terr_id.FIRST .. p_terr_rec.terr_id.LAST
887 INSERT INTO jty_webadi_oth_terr_intf(interface_type, user_sequence, terr_rsc_id, action_flag,
888 header, user_id, org_id, org_name, usage_id, parent_terr_id, terr_id, terr_name,
889 hierarchy, creation_date, created_by, last_update_date, last_updated_by, last_update_login)
890 SELECT jwr.interface_type,
891 jwr.user_sequence,
892 jwr.terr_rsc_id,
893 l_action_flag,
894 l_header,
895 p_user_id,
896 p_org_id,
897 p_terr_rec.org_name(i),
898 p_usage_id,
899 p_terr_rec.parent_terr_id(i),
900 p_terr_rec.terr_id(i),
901 p_terr_rec.terr_name(i),
902 p_terr_rec.hierarchy(i),
903 l_today_date,
904 p_user_id,
905 l_today_date,
906 p_user_id,
907 p_user_id
908 FROM jty_webadi_resources jwr
909 WHERE jwr.user_sequence = p_user_sequence
910 AND jwr.interface_type = p_interface_type
911 AND jwr.header = l_header
912 AND jwr.terr_id = p_terr_rec.terr_id(i);
913
914 END IF;
915
916 x_retcode := fnd_api.g_ret_sts_success;
917 x_errbuf := 'Success';
918
919 COMMIT;
920
921 EXCEPTION
922 WHEN others THEN
923 x_retcode := fnd_api.g_ret_sts_error;
924 x_errbuf := 'Other errors in download territory definition: ' || SQLCODE || ': ' || sqlerrm;
925
926 END dl_all_territories;
927
928 PROCEDURE populate_webadi_interface(p_usage_id IN NUMBER, p_user_id IN NUMBER, p_terr_id IN NUMBER,
929 p_org_id IN NUMBER, p_type_id IN NUMBER, p_mode IN VARCHAR2 DEFAULT 'NODE',
930 p_view IN VARCHAR2 DEFAULT 'TERR', p_geo_type IN NUMBER, p_active IN DATE,
931 p_terr_id_array IN VARCHAR2 DEFAULT NULL, x_seq OUT nocopy VARCHAR2, x_retcode OUT nocopy VARCHAR2,
932 x_errbuf OUT nocopy VARCHAR2) IS
933
934 l_seq NUMBER;
935 l_intf_type VARCHAR2(1) := 'D';
936 l_mode VARCHAR2(15);
937 l_view VARCHAR2(15);
938 l_active VARCHAR2(1);
939 l_string VARCHAR2(5000);
940 l_cnt NUMBER;
941 l_value NUMBER;
942 l_no_of_qualifiers NUMBER;
943
944 CURSOR get_single_terr(v_terr_id NUMBER, v_org_id NUMBER) IS
945 SELECT terr.terr_id,
949 terr.start_date_active start_date,
946 decode(terr.terr_id, 1, hr.name, terr.name) terr_name,
947 terr.rank,
948 terr.num_winners,
950 terr.end_date_active end_date,
951 terr_type.name terr_type_name,
952 terr_type.terr_type_id,
953 terr.parent_territory_id parent_terr_id,
954 hr.name org_name,
955 --decode(reverse(substr(sub.hierarchy,2)),terr.name,null,
956 -- replace(reverse(substr(sub.hierarchy,2)),'/','->')) hierachy,
957 LTRIM(RTRIM(RTRIM(REPLACE(REVERSE(SUBSTR(sub.hierarchy, 2)), '/', '->'), terr.name), '->'), '->') hierachy,
958 terr.attribute_category,
959 terr.attribute1,
960 terr.attribute2,
961 terr.attribute3,
962 terr.attribute4,
963 terr.attribute5,
964 terr.attribute6,
965 terr.attribute7,
966 terr.attribute8,
967 terr.attribute9,
968 terr.attribute10,
969 terr.attribute11,
970 terr.attribute12,
971 terr.attribute13,
972 terr.attribute14,
973 terr.attribute15
974 FROM hr_operating_units hr,
975 jtf_terr_types_all terr_type,
976 jtf_terr_all terr,
977 (SELECT MAX(sys_connect_by_path(REVERSE(terr.name), '/')) hierarchy
978 FROM jtf_terr_all terr
979 WHERE terr.org_id = v_org_id START WITH terr.terr_id = v_terr_id CONNECT BY PRIOR terr.parent_territory_id = terr.terr_id
980 AND terr.terr_id <> 1)
981 sub
982 WHERE terr.org_id = hr.organization_id
983 AND terr.territory_type_id = terr_type.terr_type_id
984 AND terr.terr_id = v_terr_id
985 AND terr.org_id = v_org_id;
986
987 CURSOR get_all_terr(v_terr_id NUMBER, v_org_id NUMBER, v_active DATE, v_parent_terr_hierarchy VARCHAR2) IS
988 SELECT sub.terr_id,
989 decode(sub.terr_id, 1, hr.name, sub.terr_name) terr_name,
990 sub.rank,
991 sub.num_winners,
992 sub.start_date,
993 sub.end_date,
994 terr_type.name terr_type_name,
995 terr_type.terr_type_id,
996 sub.parent_terr_id,
997 hr.name org_name,
998 RTRIM(v_parent_terr_hierarchy||LTRIM(RTRIM(REPLACE(sub.hierarchy, sub.terr_name, ''), '->'), '->'), '->') hierarchy,
999 sub.attribute_category,
1000 sub.attribute1,
1001 sub.attribute2,
1002 sub.attribute3,
1003 sub.attribute4,
1004 sub.attribute5,
1005 sub.attribute6,
1006 sub.attribute7,
1007 sub.attribute8,
1008 sub.attribute9,
1009 sub.attribute10,
1010 sub.attribute11,
1011 sub.attribute12,
1012 sub.attribute13,
1013 sub.attribute14,
1014 sub.attribute15
1015 FROM hr_operating_units hr,
1016 jtf_terr_types_all terr_type,
1017 (SELECT terr.terr_id terr_id,
1018 terr.name terr_name,
1019 sys_connect_by_path(terr.name, '->') hierarchy,
1020 terr.parent_territory_id parent_terr_id,
1021 terr.rank rank,
1022 terr.num_winners num_winners,
1023 terr.start_date_active start_date,
1024 terr.end_date_active end_date,
1025 terr.territory_type_id terr_type_id,
1026 terr.org_id,
1027 terr.attribute_category,
1028 terr.attribute1,
1029 terr.attribute2,
1030 terr.attribute3,
1031 terr.attribute4,
1032 terr.attribute5,
1033 terr.attribute6,
1034 terr.attribute7,
1035 terr.attribute8,
1036 terr.attribute9,
1037 terr.attribute10,
1038 terr.attribute11,
1039 terr.attribute12,
1040 terr.attribute13,
1041 terr.attribute14,
1042 terr.attribute15
1043 FROM jtf_terr_all terr
1044 WHERE terr.org_id = v_org_id
1045 AND nvl(terr.terr_group_flag, 'N') = 'N'
1046 AND nvl(terr.enable_self_service, 'N') = 'N'
1047 AND(v_active BETWEEN terr.start_date_active
1048 AND terr.end_date_active OR v_active IS NULL) CONNECT BY terr.parent_territory_id = PRIOR terr.terr_id
1049 AND terr.terr_id <> 1 START WITH terr.terr_id = v_terr_id
1050 ORDER siblings BY terr.terr_id)
1051 sub
1055 CURSOR get_imm_children(v_terr_id NUMBER, v_org_id NUMBER, v_active DATE, v_parent_terr_hierarchy VARCHAR2) IS
1052 WHERE sub.org_id = hr.organization_id
1053 AND sub.terr_type_id = terr_type.terr_type_id;
1054
1056 SELECT sub.terr_id,
1057 decode(sub.terr_id, 1, hr.name, sub.terr_name) terr_name,
1058 sub.rank,
1059 sub.num_winners,
1060 sub.start_date,
1061 sub.end_date,
1062 terr_type.name terr_type_name,
1063 terr_type.terr_type_id,
1064 sub.parent_terr_id,
1065 hr.name org_name,
1066 RTRIM(v_parent_terr_hierarchy||LTRIM(RTRIM(REPLACE(sub.hierarchy, sub.terr_name, ''), '->'), '->'), '->') hierarchy,
1067 sub.attribute_category,
1068 sub.attribute1,
1069 sub.attribute2,
1070 sub.attribute3,
1071 sub.attribute4,
1072 sub.attribute5,
1073 sub.attribute6,
1074 sub.attribute7,
1075 sub.attribute8,
1076 sub.attribute9,
1077 sub.attribute10,
1078 sub.attribute11,
1079 sub.attribute12,
1080 sub.attribute13,
1081 sub.attribute14,
1082 sub.attribute15
1083 FROM hr_operating_units hr,
1084 jtf_terr_types_all terr_type,
1085 (SELECT terr.terr_id terr_id,
1086 terr.name terr_name,
1087 sys_connect_by_path(terr.name, '->') hierarchy,
1088 terr.parent_territory_id parent_terr_id,
1089 terr.rank rank,
1090 terr.num_winners num_winners,
1091 terr.start_date_active start_date,
1092 terr.end_date_active end_date,
1093 terr.territory_type_id terr_type_id,
1094 terr.org_id,
1095 terr.attribute_category,
1096 terr.attribute1,
1097 terr.attribute2,
1098 terr.attribute3,
1099 terr.attribute4,
1100 terr.attribute5,
1101 terr.attribute6,
1102 terr.attribute7,
1103 terr.attribute8,
1104 terr.attribute9,
1105 terr.attribute10,
1106 terr.attribute11,
1107 terr.attribute12,
1108 terr.attribute13,
1109 terr.attribute14,
1110 terr.attribute15
1111 FROM jtf_terr_all terr
1112 WHERE terr.org_id = v_org_id
1113 AND LEVEL < 3
1114 AND nvl(terr.terr_group_flag, 'N') = 'N'
1115 AND nvl(terr.enable_self_service, 'N') = 'N'
1116 AND(v_active BETWEEN terr.start_date_active
1117 AND terr.end_date_active OR v_active IS NULL) CONNECT BY terr.parent_territory_id = PRIOR terr.terr_id
1118 AND terr.terr_id <> 1 START WITH terr.terr_id = v_terr_id
1119 ORDER siblings BY terr.terr_id)
1120 sub
1121 WHERE sub.org_id = hr.organization_id
1122 AND sub.terr_type_id = terr_type.terr_type_id;
1123
1124 CURSOR get_search_csr(v_org_id NUMBER, v_active DATE) IS
1125 SELECT sub.terr_id,
1126 decode(sub.terr_id, 1, hr.name, sub.terr_name) terr_name,
1127 sub.rank,
1128 sub.num_winners,
1129 sub.start_date,
1130 sub.end_date,
1131 terr_type.name terr_type_name,
1132 terr_type.terr_type_id,
1133 sub.parent_terr_id,
1134 hr.name org_name,
1135 LTRIM(RTRIM(REPLACE(sub.hierarchy, sub.terr_name, ''), '->'), '->') hierarchy,
1136 sub.attribute_category,
1137 sub.attribute1,
1138 sub.attribute2,
1139 sub.attribute3,
1140 sub.attribute4,
1141 sub.attribute5,
1142 sub.attribute6,
1143 sub.attribute7,
1144 sub.attribute8,
1145 sub.attribute9,
1146 sub.attribute10,
1147 sub.attribute11,
1148 sub.attribute12,
1149 sub.attribute13,
1150 sub.attribute14,
1151 sub.attribute15
1152 FROM hr_operating_units hr,
1153 jtf_terr_types_all terr_type,
1154 (SELECT terr.terr_id terr_id,
1155 terr.name terr_name,
1156 sys_connect_by_path(terr.name, '->') hierarchy,
1157 terr.parent_territory_id parent_terr_id,
1158 terr.rank rank,
1159 terr.num_winners num_winners,
1160 terr.start_date_active start_date,
1161 terr.end_date_active end_date,
1162 terr.territory_type_id terr_type_id,
1163 terr.org_id,
1164 terr.attribute_category,
1165 terr.attribute1,
1166 terr.attribute2,
1167 terr.attribute3,
1168 terr.attribute4,
1169 terr.attribute5,
1170 terr.attribute6,
1171 terr.attribute7,
1172 terr.attribute8,
1173 terr.attribute9,
1174 terr.attribute10,
1175 terr.attribute11,
1176 terr.attribute12,
1177 terr.attribute13,
1178 terr.attribute14,
1179 terr.attribute15
1180 FROM jtf_terr_all terr
1181 WHERE terr.org_id = v_org_id
1182 AND nvl(terr.terr_group_flag, 'N') = 'N'
1183 AND nvl(terr.enable_self_service, 'N') = 'N'
1184 AND(v_active BETWEEN terr.start_date_active
1185 AND terr.end_date_active OR v_active IS NULL) CONNECT BY terr.parent_territory_id = PRIOR terr.terr_id
1186 AND terr.terr_id <> 1 START WITH terr.terr_id IN
1187 (SELECT num_col
1188 FROM jty_str_to_table_gt)
1189 ORDER siblings BY terr.terr_id)
1190 sub
1191 WHERE sub.org_id = hr.organization_id
1192 AND sub.terr_type_id = terr_type.terr_type_id;
1193
1194 l_terr_rec terr_rec_type;
1195 --l_all_terr_rec_tbl terr_rec_tbl_type;
1196 l_parent_terr_hierarchy VARCHAR2(2000);
1197 BEGIN
1198 mo_global.set_org_context(p_org_id, NULL, 'JTF');
1199
1200 x_retcode := 'S';
1201 x_errbuf := 'Success';
1202 l_mode := nvl(p_mode, 'NODE');
1206 SELECT jty_webadi_oth_terr_intf_s.nextval
1203 l_view := nvl(p_view, 'TERR');
1204
1205 BEGIN
1207 INTO l_seq
1208 FROM dual;
1209 -- remove existing old data for this userid which is older than 3 days
1210
1211 DELETE FROM jty_webadi_oth_terr_intf
1212 WHERE user_id = p_user_id
1213 AND creation_date <= sysdate -3;
1214
1215 DELETE FROM jty_webadi_resources jwr
1216 WHERE NOT EXISTS
1217 (SELECT 1
1218 FROM jty_webadi_oth_terr_intf jwot
1219 WHERE jwot.user_sequence = jwr.user_sequence)
1220 ;
1221
1222 COMMIT;
1223
1224 EXCEPTION
1225 WHEN others THEN
1226 NULL;
1227 END;
1228
1229 --Added for bug 8200357
1230 IF l_mode <> 'SEARCH'
1231 THEN
1232 --Added for bug 7639213
1233 SELECT RTRIM(REPLACE(sub.hierarchy,'/','->'),terr.name) hierarchy
1234 INTO l_parent_terr_hierarchy
1235 FROM jtf_terr_all terr,
1236 (SELECT REVERSE(SUBSTR(MAX(sys_connect_by_path(REVERSE(terr.name),'/')),2)) hierarchy
1237 FROM jtf_terr_all terr
1238 WHERE terr.org_id = p_org_id START WITH terr.terr_id = p_terr_id CONNECT BY PRIOR terr.parent_territory_id = terr.terr_id
1239 AND terr.terr_id <> 1
1240 ) sub
1241 WHERE terr.org_id = p_org_id
1242 AND terr.terr_id = p_terr_id ;
1243
1244 END IF;
1245
1246
1247 -- Populate a global table with qualfiers details that
1248 -- needs to be displayed
1249 --get qualifier header information
1250 get_qual_header(p_usage_id => p_usage_id, p_org_id => p_org_id, p_user_sequence => l_seq, x_row_count => l_no_of_qualifiers);
1251
1252 -- get qual type header information
1253 get_qual_type_header(p_usage_id => p_usage_id, p_user_sequence => l_seq);
1254
1255 --dbms_output.put_line('get_qual_details: Returns l_no_of_qualifiers ' || l_no_of_qualifiers);
1256
1257 IF(l_no_of_qualifiers > 25) THEN
1258 fnd_message.clear;
1259 fnd_message.set_name('JTF', 'JTY_OTH_TERR_TOO_MANY_QUAL');
1260 fnd_message.set_token('POSITION', l_no_of_qualifiers);
1261 x_retcode := fnd_api.g_ret_sts_error;
1262 x_errbuf := fnd_message.GET();
1263
1264 --APP_EXCEPTION.RAISE_EXCEPTION;
1265 ELSIF(l_no_of_qualifiers = 0) THEN
1266 fnd_message.clear;
1267 fnd_message.set_name('JTF', 'JTY_OTH_TERR_NO_QUAL_ENABLED');
1268 x_retcode := fnd_api.g_ret_sts_error;
1269 x_errbuf := fnd_message.GET();
1270
1271 --APP_EXCEPTION.RAISE_EXCEPTION;
1272 ELSE
1273 -- start populating terr detail
1274 CASE l_mode
1275 WHEN 'NODE' THEN
1276
1277 OPEN get_single_terr(p_terr_id, p_org_id);
1278 FETCH get_single_terr bulk collect
1279 INTO l_terr_rec.terr_id,
1280 l_terr_rec.terr_name,
1281 l_terr_rec.rank,
1282 l_terr_rec.num_winners,
1283 l_terr_rec.start_date,
1284 l_terr_rec.end_date,
1285 l_terr_rec.terr_type_name,
1286 l_terr_rec.terr_type_id,
1287 l_terr_rec.parent_terr_id,
1288 l_terr_rec.org_name,
1289 l_terr_rec.hierarchy,
1290 l_terr_rec.attribute_category,
1291 l_terr_rec.attribute1,
1292 l_terr_rec.attribute2,
1293 l_terr_rec.attribute3,
1294 l_terr_rec.attribute4,
1295 l_terr_rec.attribute5,
1296 l_terr_rec.attribute6,
1297 l_terr_rec.attribute7,
1298 l_terr_rec.attribute8,
1302 l_terr_rec.attribute12,
1299 l_terr_rec.attribute9,
1300 l_terr_rec.attribute10,
1301 l_terr_rec.attribute11,
1303 l_terr_rec.attribute13,
1304 l_terr_rec.attribute14,
1305 l_terr_rec.attribute15;
1306 CLOSE get_single_terr;
1307
1308 -- call download single terr procedure
1309 dl_all_territories(p_user_sequence => l_seq, p_user_id => p_user_id, p_org_id => p_org_id, p_usage_id => p_usage_id, p_interface_type => l_intf_type, p_terr_rec => l_terr_rec, x_retcode => x_retcode, x_errbuf => x_errbuf);
1310
1314 CASE l_view
1311 COMMIT;
1312 -- process immediate children
1313 WHEN 'IMM' THEN
1315 WHEN 'TERR' THEN
1316
1317 OPEN get_imm_children(p_terr_id, p_org_id, p_active, l_parent_terr_hierarchy);
1318 FETCH get_imm_children bulk collect
1319 INTO l_terr_rec.terr_id,
1320 l_terr_rec.terr_name,
1321 l_terr_rec.rank,
1322 l_terr_rec.num_winners,
1323 l_terr_rec.start_date,
1324 l_terr_rec.end_date,
1325 l_terr_rec.terr_type_name,
1326 l_terr_rec.terr_type_id,
1327 l_terr_rec.parent_terr_id,
1328 l_terr_rec.org_name,
1329 l_terr_rec.hierarchy,
1330 l_terr_rec.attribute_category,
1331 l_terr_rec.attribute1,
1332 l_terr_rec.attribute2,
1333 l_terr_rec.attribute3,
1334 l_terr_rec.attribute4,
1335 l_terr_rec.attribute5,
1336 l_terr_rec.attribute6,
1337 l_terr_rec.attribute7,
1338 l_terr_rec.attribute8,
1339 l_terr_rec.attribute9,
1340 l_terr_rec.attribute10,
1341 l_terr_rec.attribute11,
1342 l_terr_rec.attribute12,
1343 l_terr_rec.attribute13,
1344 l_terr_rec.attribute14,
1345 l_terr_rec.attribute15;
1346 CLOSE get_imm_children;
1347
1348 --dbms_output.put_line('before passing to process' || l_terr_rec_tbl.count);
1349 dl_all_territories(p_user_sequence => l_seq, p_user_id => p_user_id, p_org_id => p_org_id, p_usage_id => p_usage_id, p_interface_type => l_intf_type, p_terr_rec => l_terr_rec, x_retcode => x_retcode, x_errbuf => x_errbuf);
1350 COMMIT;
1351 WHEN 'UNASS' THEN
1352 -- process unassign geography
1353 dl_unassign_geography(p_org_id => p_org_id, p_usage_id => p_usage_id, p_user_id => p_user_id,
1354 p_user_sequence => l_seq, p_interface_type => l_intf_type, p_terr_id => p_terr_id,
1355 p_geo_type => p_geo_type, x_retcode => x_retcode, x_errbuf => x_errbuf);
1356 COMMIT;
1357 WHEN 'BOTH' THEN
1358
1359 OPEN get_imm_children(p_terr_id, p_org_id, p_active, l_parent_terr_hierarchy);
1360 FETCH get_imm_children bulk collect
1361 INTO l_terr_rec.terr_id,
1362 l_terr_rec.terr_name,
1363 l_terr_rec.rank,
1364 l_terr_rec.num_winners,
1365 l_terr_rec.start_date,
1366 l_terr_rec.end_date,
1367 l_terr_rec.terr_type_name,
1368 l_terr_rec.terr_type_id,
1369 l_terr_rec.parent_terr_id,
1370 l_terr_rec.org_name,
1371 l_terr_rec.hierarchy,
1372 l_terr_rec.attribute_category,
1373 l_terr_rec.attribute1,
1374 l_terr_rec.attribute2,
1375 l_terr_rec.attribute3,
1376 l_terr_rec.attribute4,
1377 l_terr_rec.attribute5,
1378 l_terr_rec.attribute6,
1379 l_terr_rec.attribute7,
1380 l_terr_rec.attribute8,
1381 l_terr_rec.attribute9,
1382 l_terr_rec.attribute10,
1383 l_terr_rec.attribute11,
1384 l_terr_rec.attribute12,
1385 l_terr_rec.attribute13,
1386 l_terr_rec.attribute14,
1387 l_terr_rec.attribute15;
1388 CLOSE get_imm_children;
1389
1390 -- process assigned territories
1391 dl_all_territories(p_user_sequence => l_seq, p_user_id => p_user_id, p_org_id => p_org_id, p_usage_id => p_usage_id, p_interface_type => l_intf_type, p_terr_rec => l_terr_rec, x_retcode => x_retcode, x_errbuf => x_errbuf);
1392
1393 -- process unassign geography
1394 dl_unassign_geography(p_org_id => p_org_id, p_usage_id => p_usage_id, p_user_id => p_user_id,
1395 p_user_sequence => l_seq, p_interface_type => l_intf_type, p_terr_id => p_terr_id,
1396 p_geo_type => p_geo_type, x_retcode => x_retcode, x_errbuf => x_errbuf);
1397 COMMIT;
1398 ELSE
1399 NULL;
1400 END
1401 CASE;
1402 --l_view
1403 WHEN 'ALL' THEN
1404
1405 OPEN get_all_terr(p_terr_id, p_org_id, p_active, l_parent_terr_hierarchy);
1406 FETCH get_all_terr bulk collect
1407 INTO l_terr_rec.terr_id,
1408 l_terr_rec.terr_name,
1409 l_terr_rec.rank,
1410 l_terr_rec.num_winners,
1411 l_terr_rec.start_date,
1412 l_terr_rec.end_date,
1413 l_terr_rec.terr_type_name,
1414 l_terr_rec.terr_type_id,
1415 l_terr_rec.parent_terr_id,
1416 l_terr_rec.org_name,
1417 l_terr_rec.hierarchy,
1418 l_terr_rec.attribute_category,
1419 l_terr_rec.attribute1,
1420 l_terr_rec.attribute2,
1421 l_terr_rec.attribute3,
1422 l_terr_rec.attribute4,
1423 l_terr_rec.attribute5,
1424 l_terr_rec.attribute6,
1425 l_terr_rec.attribute7,
1426 l_terr_rec.attribute8,
1427 l_terr_rec.attribute9,
1428 l_terr_rec.attribute10,
1429 l_terr_rec.attribute11,
1430 l_terr_rec.attribute12,
1431 l_terr_rec.attribute13,
1432 l_terr_rec.attribute14,
1433 l_terr_rec.attribute15;
1434 CLOSE get_all_terr;
1435
1436 dl_all_territories(p_user_sequence => l_seq, p_user_id => p_user_id, p_org_id => p_org_id, p_usage_id => p_usage_id, p_interface_type => l_intf_type, p_terr_rec => l_terr_rec, x_retcode => x_retcode, x_errbuf => x_errbuf);
1437 COMMIT;
1438 WHEN 'SEARCH' THEN
1439 --p( l_cur_query );
1440
1441 IF p_terr_id_array IS NOT NULL THEN
1442 BEGIN
1443 --EXECUTE IMMEDIATE ('TRUNCATE TABLE JTY_STR_TO_TABLE_GT');
1444
1445 DELETE FROM jty_str_to_table_gt;
1446 l_string := p_terr_id_array || ',';
1447 LOOP
1448 EXIT
1449 WHEN l_string IS NULL;
1450 l_cnt := instr(l_string, ',');
1451 l_value := SUBSTR(l_string, 1, l_cnt -1);
1452
1456 VALUES(l_value);
1453 IF l_value IS NOT NULL THEN
1454 INSERT
1455 INTO jty_str_to_table_gt(num_col)
1457 END IF;
1458
1459 l_string := SUBSTR(l_string, l_cnt + 1);
1460
1461 END LOOP;
1462 END;
1463
1464 OPEN get_search_csr(p_org_id, p_active);
1465 FETCH get_search_csr bulk collect
1466 INTO l_terr_rec.terr_id,
1467 l_terr_rec.terr_name,
1468 l_terr_rec.rank,
1469 l_terr_rec.num_winners,
1470 l_terr_rec.start_date,
1471 l_terr_rec.end_date,
1472 l_terr_rec.terr_type_name,
1473 l_terr_rec.terr_type_id,
1474 l_terr_rec.parent_terr_id,
1475 l_terr_rec.org_name,
1476 l_terr_rec.hierarchy,
1477 l_terr_rec.attribute_category,
1478 l_terr_rec.attribute1,
1479 l_terr_rec.attribute2,
1480 l_terr_rec.attribute3,
1481 l_terr_rec.attribute4,
1482 l_terr_rec.attribute5,
1483 l_terr_rec.attribute6,
1484 l_terr_rec.attribute7,
1485 l_terr_rec.attribute8,
1486 l_terr_rec.attribute9,
1487 l_terr_rec.attribute10,
1488 l_terr_rec.attribute11,
1489 l_terr_rec.attribute12,
1490 l_terr_rec.attribute13,
1491 l_terr_rec.attribute14,
1492 l_terr_rec.attribute15;
1493 CLOSE get_search_csr;
1494
1495 dl_all_territories(p_user_sequence => l_seq, p_user_id => p_user_id, p_org_id => p_org_id, p_usage_id => p_usage_id, p_interface_type => l_intf_type, p_terr_rec => l_terr_rec, x_retcode => x_retcode, x_errbuf => x_errbuf);
1496 END IF;
1497
1498 ELSE
1499 NULL;
1500 END
1501 CASE;
1502 -- l_mode
1503
1504 END IF;
1505
1506 COMMIT;
1507
1508 x_seq := l_seq;
1509
1510 END populate_webadi_interface;
1511
1512 END jty_webadi_oth_terr_dwnl_pkg;
1513
1514