[Home] [Help]
PACKAGE BODY: APPS.JTY_WEBADI_OTH_TERR_DWNL_PKG
Source
4 -- | Copyright (c) 1999 Oracle Corporation |
1 PACKAGE BODY jty_webadi_oth_terr_dwnl_pkg AS
2 /* $Header: jtfowdpb.pls 120.44.12010000.16 2010/03/02 08:57:32 ppillai ship $ */
3 -- +===========================================================================+
8
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +===========================================================================+
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 DELETE FROM jty_webadi_qual_header jwh
40 WHERE NOT EXISTS
41 (SELECT 1
42 FROM jty_webadi_oth_terr_intf jwot
43 WHERE jwot.user_sequence = jwh.user_sequence) ;
44
45
46 INSERT
47 INTO jty_webadi_qual_header(qualifier_num, user_sequence, qual_usg_id, qualifier_name, display_type,
48 operator_type, qual_cond_col_name, qual_val1_col_name, qual_val2_col_name, qual_val3_col_name,
49 html_lov_sql1, html_lov_sql2, html_lov_sql3, display_sql1, display_sql2, display_sql3,
50 convert_to_id_flag, comparison_operator)
51 SELECT rownum,
52 sub.*
53 FROM
54 (SELECT p_user_sequence,
55 qual.qual_usg_id,
56 --rownum,
57 qual.seeded_qual_name,
58 qual.display_type,
59 qual.hierarchy_type operator_type,
60 qual.seeded_qual_name || '.Condition',
61 qual.seeded_qual_name || '.Value1',
62 qual.seeded_qual_name || '.Value2',
63 qual.seeded_qual_name || '.Value3',
64 qual.html_lov_sql1,
65 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,
66 qual.html_lov_sql3,
67 qual.display_sql1,
68 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,
69 qual.display_sql3,
70 convert_to_id_flag,
71 (
72 CASE
73 WHEN qual.equal_flag = 'Y' THEN
74 CASE
75 WHEN qual.like_flag = 'Y' THEN
76 CASE
77 WHEN qual.between_flag = 'Y' THEN '=,LIKE,BETWEEN'
78 ELSE '=,LIKE'
79 END
80 ELSE
81 CASE
82 WHEN qual.between_flag = 'Y' THEN '=,BETWEEN'
83 ELSE '='
84 END
85 END
86 ELSE
87 CASE
88 WHEN qual.like_flag = 'Y' THEN
89 CASE
90 WHEN qual.between_flag = 'Y' THEN 'LIKE,BETWEEN'
91 ELSE 'LIKE'
92 END
93 ELSE
94 CASE
95 WHEN qual.between_flag = 'Y' THEN ',BETWEEN'
96 ELSE ''
97 END
98 END
99 END) comparison_operator
100 FROM jtf_seeded_qual_usgs_v qual
101 WHERE qual.org_id = p_org_id
102 AND qual.source_id = p_usage_id
103 AND qual.enabled_flag = 'Y'
104 ORDER BY qual.html_lov_sql3,
105 qual.html_lov_sql2,
106 operator_type DESC)
107 sub;
108
109 x_row_count := SQL % rowcount;
110 COMMIT;
111
112 END get_qual_header;
113
114 PROCEDURE get_qual_type_header(p_usage_id IN INTEGER, p_user_sequence IN INTEGER) IS
115
116 BEGIN
117
118 DELETE FROM jty_webadi_qual_type_header
119 WHERE user_sequence = p_user_sequence;
120
121 INSERT
122 INTO jty_webadi_qual_type_header(qual_type_id, qual_type_num, qual_type_name, qual_type_descr, user_sequence)
123 (SELECT jqtu.qual_type_usg_id qual_type_id,
124 rownum,
125 jqt.name qual_type_name,
126 jqt.description qual_type_descr,
127 p_user_sequence
128 FROM jtf_qual_type_usgs_all jqtu,
129 jtf_qual_types_all jqt
130 WHERE jqtu.qual_type_id = jqt.qual_type_id
131 AND jqtu.source_id = p_usage_id)
132 ;
133
134 COMMIT;
135 END get_qual_type_header;
136
137 PROCEDURE dl_unassign_geography(p_org_id IN NUMBER, p_usage_id IN NUMBER, p_user_id IN NUMBER,
138 p_user_sequence IN NUMBER, p_interface_type IN VARCHAR2, p_terr_id IN NUMBER, p_geo_type IN NUMBER,
139 x_retcode OUT nocopy VARCHAR2, x_errbuf OUT nocopy VARCHAR2) IS
140
141 l_query VARCHAR2(30000);
142 l_qual_name VARCHAR2(150);
143 l_comp_oper VARCHAR2(30);
144 l_qual_val1 VARCHAR2(150);
145 l_qual_val2 VARCHAR2(150);
146 l_qual_num NUMBER;
147 l_loc_seg_id NUMBER;
148
149 BEGIN
150
151 -- get the start geo location value
152 SELECT qgt.qualifier_num,
153 REPLACE(LTRIM(LTRIM(UPPER(qualifier_name))), ' ', '_') qual_name
154 INTO l_qual_num,
155 l_qual_name
156 FROM jty_webadi_qual_header qgt
160
157 WHERE qgt.operator_type = 'GEOGRAPHY'
158 AND qgt.user_sequence = p_user_sequence
159 AND qgt.qual_usg_id = p_geo_type;
161 --dbms_output.put_line('l_qual_num, l_qual_name: '|| l_qual_num ||', '|| l_qual_name);
162 CASE l_qual_name
163 WHEN 'COUNTRY' THEN
164 x_retcode := fnd_api.g_ret_sts_error;
165 fnd_message.clear;
166 fnd_message.set_name('JTF', 'JTY_OTH_TERR_GEO_TYPE');
167 x_errbuf := fnd_message.GET();
168 ELSE
169 INSERT
170 INTO jty_webadi_oth_terr_intf(interface_type, org_id, usage_id, user_id, user_sequence,
171 qual1_value1, qual2_value1, qual3_value1, qual4_value1, qual5_value1, qual6_value1,
172 qual7_value1, qual8_value1, qual9_value1, qual10_value1, qual11_value1, qual12_value1,
173 qual13_value1, qual14_value1, qual15_value1, qual16_value1, qual17_value1, qual18_value1,
174 qual19_value1, qual20_value1, qual21_value1, qual22_value1, qual23_value1, qual24_value1,
175 qual25_value1)
176 SELECT p_interface_type,
177 p_org_id,
178 p_usage_id,
179 p_user_id,
180 p_user_sequence,
181 decode(l_qual_num, 1, geography_name, NULL) qual1_value1,
182 decode(l_qual_num, 2, geography_name, NULL) qual2_value1,
183 decode(l_qual_num, 3, geography_name, NULL) qual3_value1,
184 decode(l_qual_num, 4, geography_name, NULL) qual4_value1,
185 decode(l_qual_num, 5, geography_name, NULL) qual5_value1,
186 decode(l_qual_num, 6, geography_name, NULL) qual6_value1,
187 decode(l_qual_num, 7, geography_name, NULL) qual7_value1,
188 decode(l_qual_num, 8, geography_name, NULL) qual8_value1,
189 decode(l_qual_num, 9, geography_name, NULL) qual9_value1,
190 decode(l_qual_num, 10, geography_name, NULL) qual10_value1,
191 decode(l_qual_num, 11, geography_name, NULL) qual11_value1,
192 decode(l_qual_num, 12, geography_name, NULL) qual12_value1,
193 decode(l_qual_num, 13, geography_name, NULL) qual13_value1,
194 decode(l_qual_num, 14, geography_name, NULL) qual14_value1,
195 decode(l_qual_num, 15, geography_name, NULL) qual15_value1,
196 decode(l_qual_num, 16, geography_name, NULL) qual16_value1,
197 decode(l_qual_num, 17, geography_name, NULL) qual17_value1,
198 decode(l_qual_num, 18, geography_name, NULL) qual18_value1,
199 decode(l_qual_num, 19, geography_name, NULL) qual19_value1,
200 decode(l_qual_num, 20, geography_name, NULL) qual20_value1,
201 decode(l_qual_num, 21, geography_name, NULL) qual21_value1,
202 decode(l_qual_num, 22, geography_name, NULL) qual22_value1,
203 decode(l_qual_num, 23, geography_name, NULL) qual23_value1,
204 decode(l_qual_num, 24, geography_name, NULL) qual24_value1,
205 decode(l_qual_num, 25, geography_name, NULL) qual25_value1
206 FROM hz_geographies hzg,
207 jtf_terr_values_all qv,
208 jtf_terr_qual_all jtq
209 WHERE hzg.geography_type = l_qual_name
210 AND jtq.terr_qual_id = qv.terr_qual_id
211 AND qv.low_value_char IN(geography_element1_code, geography_element2_code, geography_element3_code, geography_element4_code, geography_element5_code)
212 AND jtq.org_id = p_org_id
213 AND jtq.terr_id = p_terr_id
214 AND NOT EXISTS
215 (SELECT 1
216 FROM jtf_terr_values_all qv,
217 jtf_terr_qual_all jtq,
218 jtf_terr_all terr
219 WHERE hzg.geography_name = qv.low_value_char
220 AND jtq.terr_qual_id = qv.terr_qual_id
221 AND jtq.org_id = p_org_id
222 AND jtq.terr_id = terr.terr_id
223 AND nvl(terr.terr_group_flag, 'N') = 'N'
224 AND terr.enabled_flag = 'Y'
225 AND nvl(terr.enable_self_service, 'N') = 'N'
226 AND terr.parent_territory_id = p_terr_id
227 AND terr.org_id = p_org_id)
228 ;
229 END
230 CASE;
231
232 x_retcode := fnd_api.g_ret_sts_success;
233 x_errbuf := 'Success';
234
235 COMMIT;
236
237 EXCEPTION
238 WHEN no_data_found THEN
239 x_retcode := fnd_api.g_ret_sts_error;
240 fnd_message.clear;
241 fnd_message.set_name('JTF', 'JTY_OTH_TERR_GEO_TYPE');
242 x_errbuf := fnd_message.GET();
243 --raise_application_error(-20000, 'No geography defined for this territory');
244 WHEN others THEN
245 x_retcode := fnd_api.g_ret_sts_error;
246 fnd_message.clear;
247 fnd_message.set_name('JTF', 'JTY_OTH_TERR_GEO_TYPE');
248 fnd_message.set_token('POSITION', sqlerrm);
249 x_errbuf := fnd_message.GET();
250
251 END dl_unassign_geography;
252
253 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
254 CURSOR c_grp_name IS
255 SELECT group_name
256 FROM jtf_rs_groups_tl
257 WHERE group_id = p_group_id
258 AND LANGUAGE = userenv('LANG');
259
260 CURSOR c_team_name IS
261 SELECT team_name
262 FROM jtf_rs_teams_tl
263 WHERE team_id = p_resource_id
264 AND LANGUAGE = userenv('LANG');
265
266 CURSOR c_role_name IS
267 SELECT jrrt.role_name
268 FROM jtf_rs_roles_tl jrrt,
269 jtf_rs_roles_b jrrb
270 WHERE(p_role_id IS NULL OR jrrt.role_id = p_role_id)
271 AND jrrb.role_code(+) = p_role
272 AND jrrb.role_id = jrrt.role_id(+)
273 AND jrrt.LANGUAGE = userenv('LANG');
274
275 CURSOR c_resource_name IS
276 SELECT resource_name
277 FROM jtf_rs_resource_extns_tl
278 WHERE resource_id = p_resource_id
279 AND LANGUAGE = userenv('LANG');
280 l_name VARCHAR2(250) := NULL;
281 BEGIN
282
283 IF p_resource_type = 'RS_GROUP' THEN
284
285 OPEN c_grp_name;
286 FETCH c_grp_name
290
287 INTO l_name;
288 CLOSE c_grp_name;
289 ELSIF p_resource_type = 'RS_TEAM' THEN
291 OPEN c_team_name;
292 FETCH c_team_name
293 INTO l_name;
294 CLOSE c_team_name;
295 ELSIF p_resource_type = 'RS_ROLE' THEN
296
297 OPEN c_role_name;
298 FETCH c_role_name
299 INTO l_name;
300 CLOSE c_role_name;
301 ELSE
302
303 OPEN c_resource_name;
304 FETCH c_resource_name
305 INTO l_name;
306 CLOSE c_resource_name;
307 END IF;
308
309 RETURN l_name;
310 END get_resource_name;
311
312 FUNCTION get_group_name(p_resource_type VARCHAR, p_group_id NUMBER, p_resource_id NUMBER) RETURN VARCHAR IS
313 CURSOR c_ind_grp_name IS
314 SELECT group_name
315 FROM jtf_rs_groups_tl
316 WHERE group_id = p_group_id
317 AND LANGUAGE = userenv('LANG');
318
319 CURSOR c_grp_grp_name IS
320 select JRGV.GROUP_NAME from
321 JTF_RS_GROUPS_VL JRGV, JTF_RS_GROUP_MEMBERS_VL jrgmv
322 where jrgv.group_ID = jrgmv.group_ID
323 AND jrgmv.RESOURCE_ID = p_resource_id;
324
325 l_group_name VARCHAR2(250) := NULL;
326 BEGIN
327
328 IF p_resource_type = 'RS_GROUP' THEN
329 OPEN c_grp_grp_name;
330 FETCH c_grp_grp_name
331 INTO l_group_name;
332 CLOSE c_grp_grp_name;
333 RETURN l_group_name;
334 ELSIF p_resource_type = 'RS_TEAM' THEN
335 RETURN NULL;
336 ELSIF p_resource_type = 'RS_ROLE' THEN
337 RETURN NULL;
338 ELSE
339 OPEN c_ind_grp_name;
340 FETCH c_ind_grp_name
341 INTO l_group_name;
342 CLOSE c_ind_grp_name;
343 RETURN l_group_name;
344 END IF;
345
346 END get_group_name;
347
348 FUNCTION get_role_name(p_resource_type VARCHAR, p_role VARCHAR2) RETURN VARCHAR IS CURSOR c_role_name IS
349 SELECT jrrt.role_name
350 FROM jtf_rs_roles_tl jrrt,
351 jtf_rs_roles_b jrrb
352 WHERE jrrt.role_id = jrrb.role_id
353 AND language = 'US'
354 AND jrrb.role_code = p_role;
355
356 l_role_name VARCHAR2(250) := NULL;
357 BEGIN
358 IF p_resource_type = 'RS_TEAM' THEN
359 RETURN NULL;
360 ELSIF p_resource_type = 'RS_ROLE' THEN
361 RETURN NULL;
362 ELSE
363 OPEN c_role_name;
364 FETCH c_role_name
365 INTO l_role_name;
366 CLOSE c_role_name;
367 RETURN l_role_name;
368 END IF;
369
370 END get_role_name;
371
372 FUNCTION get_email(p_resource_type VARCHAR, p_resource_id NUMBER) RETURN VARCHAR IS
373 CURSOR c_email IS
374 SELECT source_email
375 FROM jtf_rs_resource_extns
376 WHERE resource_id = p_resource_id;
377 CURSOR c_grp_email IS
378 SELECT EMAIL FROM jtf_rs_resources_vl
379 WHERE RESOURCE_ID = p_resource_id and resource_type = p_resource_type;
380
381 l_email VARCHAR2(2000);
382 BEGIN
383 IF p_resource_type = 'RS_GROUP' THEN
384 OPEN c_grp_email;
385 FETCH c_grp_email
386 INTO l_email;
387 CLOSE c_grp_email;
388 RETURN l_email;
389 ELSE
390 OPEN c_email;
391 FETCH c_email
392 INTO l_email;
393 CLOSE c_email;
394 RETURN l_email;
395 END IF;
396 END get_email;
397
398 PROCEDURE dl_all_territories(p_user_sequence IN NUMBER, p_user_id IN NUMBER, p_org_id IN NUMBER,
399 p_usage_id IN NUMBER, p_interface_type IN VARCHAR2, p_terr_rec IN terr_rec_type,
400 x_retcode OUT nocopy VARCHAR2, x_errbuf OUT nocopy VARCHAR2) IS
401
402 l_header VARCHAR2(15);
403 l_today_date DATE := sysdate;
404 l_sql VARCHAR2(31000);
408 BEGIN
405 l_action_flag VARCHAR2(1) := 'U';
406 l_row_count NUMBER;
407
409
410 -- get access details information
411
412 IF p_terr_rec.terr_id.COUNT > 0 THEN
413 l_header := 'TERR';
414 forall i IN p_terr_rec.terr_id.FIRST .. p_terr_rec.terr_id.LAST INSERT
415 INTO jty_webadi_oth_terr_intf(interface_type, action_flag, header, user_sequence, user_id, org_id,
416 org_name, usage_id, terr_id, terr_name, terr_type_name, terr_type_id, rank, num_winners,
417 terr_start_date, terr_end_date, parent_terr_id, hierarchy, attribute_category, attribute1,
418 attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8,
419 attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15,
420 creation_date, created_by, last_update_date, last_updated_by, last_update_login)
421 VALUES(p_interface_type, l_action_flag, l_header, p_user_sequence, p_user_id, p_org_id,
422 p_terr_rec.org_name(i), p_usage_id, p_terr_rec.terr_id(i), p_terr_rec.terr_name(i),
423 p_terr_rec.terr_type_name(i), p_terr_rec.terr_type_id(i), p_terr_rec.rank(i),
424 p_terr_rec.num_winners(i), p_terr_rec.start_date(i), p_terr_rec.end_date(i),
425 p_terr_rec.parent_terr_id(i), p_terr_rec.hierarchy(i), p_terr_rec.attribute_category(i),
426 p_terr_rec.attribute1(i), p_terr_rec.attribute2(i), p_terr_rec.attribute3(i),
427 p_terr_rec.attribute4(i), p_terr_rec.attribute5(i), p_terr_rec.attribute6(i),
428 p_terr_rec.attribute7(i), p_terr_rec.attribute8(i), p_terr_rec.attribute9(i),
429 p_terr_rec.attribute10(i), p_terr_rec.attribute11(i), p_terr_rec.attribute12(i),
430 p_terr_rec.attribute13(i), p_terr_rec.attribute14(i), p_terr_rec.attribute15(i),
431 l_today_date, p_user_id, l_today_date, p_user_id, p_user_id);
432
433 l_header := 'QUAL';
434
435 forall i IN p_terr_rec.terr_id.FIRST .. p_terr_rec.terr_id.LAST INSERT
436 INTO jty_webadi_oth_terr_intf(interface_type, action_flag, header, user_sequence, user_id,
437 org_id, org_name, usage_id, terr_id, terr_name, terr_type_name, terr_type_id, rank,
438 num_winners, terr_start_date, terr_end_date, parent_terr_id, hierarchy, creation_date,
439 created_by, last_update_date, last_updated_by, last_update_login, row_id, qual1_value_id,
440 qual1_value1, qual1_value2, qual1_value3, qual2_value_id, qual2_value1, qual2_value2,
441 qual2_value3, qual3_value_id, qual3_value1, qual3_value2, qual3_value3, qual4_value_id,
442 qual4_value1, qual4_value2, qual4_value3, qual5_value_id, qual5_value1, qual5_value2,
443 qual5_value3, qual6_value_id, qual6_value1, qual6_value2, qual6_value3, qual7_value_id,
444 qual7_value1, qual7_value2, qual7_value3, qual8_value_id, qual8_value1, qual8_value2,
445 qual8_value3, qual9_value_id, qual9_value1, qual9_value2, qual9_value3, qual10_value_id,
446 qual10_value1, qual10_value2, qual10_value3, qual11_value_id, qual11_value1, qual11_value2,
447 qual11_value3, qual12_value_id, qual12_value1, qual12_value2, qual12_value3, qual13_value_id,
448 qual13_value1, qual13_value2, qual13_value3, qual14_value_id, qual14_value1, qual14_value2,
449 qual14_value3, qual15_value_id, qual15_value1, qual15_value2, qual15_value3, qual16_value_id,
450 qual16_value1, qual16_value2, qual16_value3, qual17_value_id, qual17_value1, qual17_value2,
451 qual17_value3, qual18_value_id, qual18_value1, qual18_value2, qual18_value3, qual19_value_id,
452 qual19_value1, qual19_value2, qual19_value3, qual20_value_id, qual20_value1, qual20_value2,
453 qual20_value3, qual21_value_id, qual21_value1, qual21_value2, qual21_value3, qual22_value_id,
454 qual22_value1, qual22_value2, qual22_value3, qual23_value_id, qual23_value1, qual23_value2,
455 qual23_value3, qual24_value_id, qual24_value1, qual24_value2, qual24_value3, qual25_value_id,
456 qual25_value1, qual25_value2, qual25_value3,
457
458 qual26_value_id, qual26_value1, qual26_value2, qual26_value3, qual27_value_id,
459 qual27_value1, qual27_value2, qual27_value3, qual28_value_id, qual28_value1, qual28_value2,
460 qual28_value3, qual29_value_id, qual29_value1, qual29_value2, qual29_value3, qual30_value_id,
461 qual30_value1, qual30_value2, qual30_value3, qual31_value_id, qual31_value1, qual31_value2,
462 qual31_value3, qual32_value_id, qual32_value1, qual32_value2, qual32_value3, qual33_value_id,
463 qual33_value1, qual33_value2, qual33_value3, qual34_value_id, qual34_value1, qual34_value2,
464 qual34_value3, qual35_value_id, qual35_value1, qual35_value2, qual35_value3, qual36_value_id,
465 qual36_value1, qual36_value2, qual36_value3, qual37_value_id, qual37_value1, qual37_value2,
466 qual37_value3, qual38_value_id, qual38_value1, qual38_value2, qual38_value3, qual39_value_id,
467 qual39_value1, qual39_value2, qual39_value3, qual40_value_id, qual40_value1, qual40_value2,
468 qual40_value3, qual41_value_id, qual41_value1, qual41_value2, qual41_value3, qual42_value_id,
469 qual42_value1, qual42_value2, qual42_value3, qual43_value_id, qual43_value1, qual43_value2,
470 qual43_value3, qual44_value_id, qual44_value1, qual44_value2, qual44_value3, qual45_value_id,
471 qual45_value1, qual45_value2, qual45_value3,
472
473 qual46_value_id, qual46_value1, qual46_value2, qual46_value3, qual47_value_id,
474 qual47_value1, qual47_value2, qual47_value3, qual48_value_id, qual48_value1, qual48_value2,
475 qual48_value3, qual49_value_id, qual49_value1, qual49_value2, qual49_value3, qual50_value_id,
476 qual50_value1, qual50_value2, qual50_value3, qual51_value_id, qual51_value1, qual51_value2,
480 qual56_value1, qual56_value2, qual56_value3, qual57_value_id, qual57_value1, qual57_value2,
477 qual51_value3, qual52_value_id, qual52_value1, qual52_value2, qual52_value3, qual53_value_id,
478 qual53_value1, qual53_value2, qual53_value3, qual54_value_id, qual54_value1, qual54_value2,
479 qual54_value3, qual55_value_id, qual55_value1, qual55_value2, qual55_value3, qual56_value_id,
481 qual57_value3, qual58_value_id, qual58_value1, qual58_value2, qual58_value3, qual59_value_id,
482 qual59_value1, qual59_value2, qual59_value3, qual60_value_id, qual60_value1, qual60_value2,
483 qual60_value3, qual61_value_id, qual61_value1, qual61_value2, qual61_value3, qual62_value_id,
484 qual62_value1, qual62_value2, qual62_value3, qual63_value_id, qual63_value1, qual63_value2,
485 qual63_value3, qual64_value_id, qual64_value1, qual64_value2, qual64_value3, qual65_value_id,
486 qual65_value1, qual65_value2, qual65_value3,
487
488 qual66_value_id, qual66_value1, qual66_value2, qual66_value3, qual67_value_id,
489 qual67_value1, qual67_value2, qual67_value3, qual68_value_id, qual68_value1, qual68_value2,
490 qual68_value3, qual69_value_id, qual69_value1, qual69_value2, qual69_value3, qual70_value_id,
491 qual70_value1, qual70_value2, qual70_value3, qual71_value_id, qual71_value1, qual71_value2,
492 qual71_value3, qual72_value_id, qual72_value1, qual72_value2, qual72_value3, qual73_value_id,
493 qual73_value1, qual73_value2, qual73_value3, qual74_value_id, qual74_value1, qual74_value2,
494 qual74_value3, qual75_value_id, qual75_value1, qual75_value2, qual75_value3
495
496
497
498 )
499 SELECT p_interface_type,
500 l_action_flag,
501 l_header,
502 p_user_sequence,
503 p_user_id,
504 p_org_id,
505 p_terr_rec.org_name(i),
506 p_usage_id,
507 p_terr_rec.terr_id(i),
508 p_terr_rec.terr_name(i),
509 p_terr_rec.terr_type_name(i),
510 p_terr_rec.terr_type_id(i),
511 p_terr_rec.rank(i),
512 p_terr_rec.num_winners(i),
513 p_terr_rec.start_date(i),
514 p_terr_rec.end_date(i),
515 p_terr_rec.parent_terr_id(i),
516 p_terr_rec.hierarchy(i),
517 l_today_date,
518 p_user_id,
519 l_today_date,
520 p_user_id,
521 p_user_id,
522 row_id,
523 qual1_value_id,
524 qual1_value1,
525 qual1_value2,
526 qual1_value3,
527 qual2_value_id,
528 qual2_value1,
529 qual2_value2,
530 qual2_value3,
531 qual3_value_id,
532 qual3_value1,
533 qual3_value2,
534 qual3_value3,
535 qual4_value_id,
536 qual4_value1,
537 qual4_value2,
538 qual4_value3,
539 qual5_value_id,
540 qual5_value1,
541 qual5_value2,
542 qual5_value3,
543 qual6_value_id,
544 qual6_value1,
545 qual6_value2,
546 qual6_value3,
547 qual7_value_id,
548 qual7_value1,
549 qual7_value2,
550 qual7_value3,
551 qual8_value_id,
552 qual8_value1,
553 qual8_value2,
554 qual8_value3,
555 qual9_value_id,
556 qual9_value1,
557 qual9_value2,
558 qual9_value3,
559 qual10_value_id,
560 qual10_value1,
561 qual10_value2,
562 qual10_value3,
563 qual11_value_id,
564 qual11_value1,
565 qual11_value2,
566 qual11_value3,
567 qual12_value_id,
568 qual12_value1,
569 qual12_value2,
570 qual12_value3,
571 qual13_value_id,
572 qual13_value1,
573 qual13_value2,
574 qual13_value3,
575 qual14_value_id,
576 qual14_value1,
577 qual14_value2,
578 qual14_value3,
579 qual15_value_id,
580 qual15_value1,
581 qual15_value2,
582 qual15_value3,
583 qual16_value_id,
584 qual16_value1,
585 qual16_value2,
586 qual16_value3,
587 qual17_value_id,
588 qual17_value1,
589 qual17_value2,
590 qual17_value3,
591 qual18_value_id,
592 qual18_value1,
593 qual18_value2,
594 qual18_value3,
595 qual19_value_id,
596 qual19_value1,
597 qual19_value2,
598 qual19_value3,
599 qual20_value_id,
600 qual20_value1,
601 qual20_value2,
602 qual20_value3,
603 qual21_value_id,
604 qual21_value1,
605 qual21_value2,
606 qual21_value3,
607 qual22_value_id,
608 qual22_value1,
609 qual22_value2,
610 qual22_value3,
611 qual23_value_id,
612 qual23_value1,
613 qual23_value2,
614 qual23_value3,
615 qual24_value_id,
616 qual24_value1,
617 qual24_value2,
618 qual24_value3,
619 qual25_value_id,
620 qual25_value1,
621 qual25_value2,
622 qual25_value3,
623 qual26_value_id,
624 qual26_value1,
625 qual26_value2,
626 qual26_value3,
627 qual27_value_id,
628 qual27_value1,
629 qual27_value2,
630 qual27_value3,
631 qual28_value_id,
632 qual28_value1,
633 qual28_value2,
634 qual28_value3,
638 qual29_value3,
635 qual29_value_id,
636 qual29_value1,
637 qual29_value2,
639 qual30_value_id,
640 qual30_value1,
641 qual30_value2,
642 qual30_value3,
643 qual31_value_id,
644 qual31_value1,
645 qual31_value2,
646 qual31_value3,
647 qual32_value_id,
648 qual32_value1,
649 qual32_value2,
650 qual32_value3,
651 qual33_value_id,
652 qual33_value1,
653 qual33_value2,
654 qual33_value3,
655 qual34_value_id,
656 qual34_value1,
657 qual34_value2,
658 qual34_value3,
659 qual35_value_id,
660 qual35_value1,
661 qual35_value2,
662 qual35_value3,
663 qual36_value_id,
664 qual36_value1,
665 qual36_value2,
666 qual36_value3,
667 qual37_value_id,
668 qual37_value1,
669 qual37_value2,
670 qual37_value3,
671 qual38_value_id,
672 qual38_value1,
673 qual38_value2,
674 qual38_value3,
675 qual39_value_id,
676 qual39_value1,
677 qual39_value2,
678 qual39_value3,
679 qual40_value_id,
680 qual40_value1,
681 qual40_value2,
682 qual40_value3,
683 qual41_value_id,
684 qual41_value1,
685 qual41_value2,
686 qual41_value3,
687 qual42_value_id,
688 qual42_value1,
689 qual42_value2,
690 qual42_value3,
691 qual43_value_id,
692 qual43_value1,
693 qual43_value2,
694 qual43_value3,
695 qual44_value_id,
696 qual44_value1,
697 qual44_value2,
698 qual44_value3,
699 qual45_value_id,
700 qual45_value1,
701 qual45_value2,
702 qual45_value3,
703 qual46_value_id,
704 qual46_value1,
705 qual46_value2,
706 qual46_value3,
707 qual47_value_id,
708 qual47_value1,
709 qual47_value2,
710 qual47_value3,
711 qual48_value_id,
712 qual48_value1,
713 qual48_value2,
714 qual48_value3,
715 qual49_value_id,
716 qual49_value1,
717 qual49_value2,
718 qual49_value3,
719 qual50_value_id,
720 qual50_value1,
721 qual50_value2,
722 qual50_value3,
723 qual51_value_id,
724 qual51_value1,
725 qual51_value2,
726 qual51_value3,
727 qual52_value_id,
728 qual52_value1,
729 qual52_value2,
730 qual52_value3,
731 qual53_value_id,
732 qual53_value1,
733 qual53_value2,
734 qual53_value3,
735 qual54_value_id,
736 qual54_value1,
737 qual54_value2,
738 qual54_value3,
739 qual55_value_id,
740 qual55_value1,
741 qual55_value2,
742 qual55_value3,
743 qual56_value_id,
744 qual56_value1,
745 qual56_value2,
746 qual56_value3,
747 qual57_value_id,
748 qual57_value1,
749 qual57_value2,
750 qual57_value3,
751 qual58_value_id,
752 qual58_value1,
753 qual58_value2,
754 qual58_value3,
755 qual59_value_id,
756 qual59_value1,
757 qual59_value2,
758 qual59_value3,
759 qual60_value_id,
760 qual60_value1,
761 qual60_value2,
762 qual60_value3,
763 qual61_value_id,
764 qual61_value1,
765 qual61_value2,
766 qual61_value3,
767 qual62_value_id,
768 qual62_value1,
769 qual62_value2,
770 qual62_value3,
771 qual63_value_id,
772 qual63_value1,
773 qual63_value2,
774 qual63_value3,
775 qual64_value_id,
776 qual64_value1,
777 qual64_value2,
778 qual64_value3,
779 qual65_value_id,
780 qual65_value1,
781 qual65_value2,
782 qual65_value3,
783 qual66_value_id,
784 qual66_value1,
785 qual66_value2,
786 qual66_value3,
787 qual67_value_id,
788 qual67_value1,
789 qual67_value2,
790 qual67_value3,
791 qual68_value_id,
792 qual68_value1,
793 qual68_value2,
794 qual68_value3,
795 qual69_value_id,
796 qual69_value1,
797 qual69_value2,
801 qual70_value2,
798 qual69_value3,
799 qual70_value_id,
800 qual70_value1,
802 qual70_value3,
803 qual71_value_id,
804 qual71_value1,
805 qual71_value2,
806 qual71_value3,
807 qual72_value_id,
808 qual72_value1,
809 qual72_value2,
810 qual72_value3,
811 qual73_value_id,
812 qual73_value1,
813 qual73_value2,
814 qual73_value3,
815 qual74_value_id,
816 qual74_value1,
817 qual74_value2,
818 qual74_value3,
819 qual75_value_id,
820 qual75_value1,
821 qual75_value2,
822 qual75_value3
823 FROM
824 (SELECT row_id,
825 MAX(decode(qualifier_num, 1, terr_value_id)) qual1_value_id,
826 MAX(decode(qualifier_num, 1, qual_value1)) qual1_value1,
827 MAX(decode(qualifier_num, 1, qual_value2)) qual1_value2,
828 MAX(decode(qualifier_num, 1, qual_value3)) qual1_value3,
829 MAX(decode(qualifier_num, 2, terr_value_id)) qual2_value_id,
830 MAX(decode(qualifier_num, 2, qual_value1)) qual2_value1,
831 MAX(decode(qualifier_num, 2, qual_value2)) qual2_value2,
832 MAX(decode(qualifier_num, 2, qual_value3)) qual2_value3,
833 MAX(decode(qualifier_num, 3, terr_value_id)) qual3_value_id,
834 MAX(decode(qualifier_num, 3, qual_value1)) qual3_value1,
835 MAX(decode(qualifier_num, 3, qual_value2)) qual3_value2,
836 MAX(decode(qualifier_num, 3, qual_value3)) qual3_value3,
837 MAX(decode(qualifier_num, 4, terr_value_id)) qual4_value_id,
838 MAX(decode(qualifier_num, 4, qual_value1)) qual4_value1,
839 MAX(decode(qualifier_num, 4, qual_value2)) qual4_value2,
840 MAX(decode(qualifier_num, 4, qual_value3)) qual4_value3,
841 MAX(decode(qualifier_num, 5, terr_value_id)) qual5_value_id,
842 MAX(decode(qualifier_num, 5, qual_value1)) qual5_value1,
843 MAX(decode(qualifier_num, 5, qual_value2)) qual5_value2,
844 MAX(decode(qualifier_num, 5, qual_value3)) qual5_value3,
845 MAX(decode(qualifier_num, 6, terr_value_id)) qual6_value_id,
846 MAX(decode(qualifier_num, 6, qual_value1)) qual6_value1,
847 MAX(decode(qualifier_num, 6, qual_value2)) qual6_value2,
848 MAX(decode(qualifier_num, 6, qual_value3)) qual6_value3,
849 MAX(decode(qualifier_num, 7, terr_value_id)) qual7_value_id,
850 MAX(decode(qualifier_num, 7, qual_value1)) qual7_value1,
851 MAX(decode(qualifier_num, 7, qual_value2)) qual7_value2,
852 MAX(decode(qualifier_num, 7, qual_value3)) qual7_value3,
853 MAX(decode(qualifier_num, 8, terr_value_id)) qual8_value_id,
854 MAX(decode(qualifier_num, 8, qual_value1)) qual8_value1,
855 MAX(decode(qualifier_num, 8, qual_value2)) qual8_value2,
856 MAX(decode(qualifier_num, 8, qual_value3)) qual8_value3,
857 MAX(decode(qualifier_num, 9, terr_value_id)) qual9_value_id,
858 MAX(decode(qualifier_num, 9, qual_value1)) qual9_value1,
859 MAX(decode(qualifier_num, 9, qual_value2)) qual9_value2,
860 MAX(decode(qualifier_num, 9, qual_value3)) qual9_value3,
861 MAX(decode(qualifier_num, 10, terr_value_id)) qual10_value_id,
862 MAX(decode(qualifier_num, 10, qual_value1)) qual10_value1,
863 MAX(decode(qualifier_num, 10, qual_value2)) qual10_value2,
864 MAX(decode(qualifier_num, 10, qual_value3)) qual10_value3,
865 MAX(decode(qualifier_num, 11, terr_value_id)) qual11_value_id,
866 MAX(decode(qualifier_num, 11, qual_value1)) qual11_value1,
867 MAX(decode(qualifier_num, 11, qual_value2)) qual11_value2,
868 MAX(decode(qualifier_num, 11, qual_value3)) qual11_value3,
869 MAX(decode(qualifier_num, 12, terr_value_id)) qual12_value_id,
870 MAX(decode(qualifier_num, 12, qual_value1)) qual12_value1,
871 MAX(decode(qualifier_num, 12, qual_value2)) qual12_value2,
872 MAX(decode(qualifier_num, 12, qual_value3)) qual12_value3,
873 MAX(decode(qualifier_num, 13, terr_value_id)) qual13_value_id,
874 MAX(decode(qualifier_num, 13, qual_value1)) qual13_value1,
875 MAX(decode(qualifier_num, 13, qual_value2)) qual13_value2,
876 MAX(decode(qualifier_num, 13, qual_value3)) qual13_value3,
877 MAX(decode(qualifier_num, 14, terr_value_id)) qual14_value_id,
878 MAX(decode(qualifier_num, 14, qual_value1)) qual14_value1,
879 MAX(decode(qualifier_num, 14, qual_value2)) qual14_value2,
880 MAX(decode(qualifier_num, 14, qual_value3)) qual14_value3,
881 MAX(decode(qualifier_num, 15, terr_value_id)) qual15_value_id,
882 MAX(decode(qualifier_num, 15, qual_value1)) qual15_value1,
883 MAX(decode(qualifier_num, 15, qual_value2)) qual15_value2,
884 MAX(decode(qualifier_num, 15, qual_value3)) qual15_value3,
885 MAX(decode(qualifier_num, 16, terr_value_id)) qual16_value_id,
886 MAX(decode(qualifier_num, 16, qual_value1)) qual16_value1,
887 MAX(decode(qualifier_num, 16, qual_value2)) qual16_value2,
888 MAX(decode(qualifier_num, 16, qual_value3)) qual16_value3,
889 MAX(decode(qualifier_num, 17, terr_value_id)) qual17_value_id,
890 MAX(decode(qualifier_num, 17, qual_value1)) qual17_value1,
891 MAX(decode(qualifier_num, 17, qual_value2)) qual17_value2,
892 MAX(decode(qualifier_num, 17, qual_value3)) qual17_value3,
893 MAX(decode(qualifier_num, 18, terr_value_id)) qual18_value_id,
894 MAX(decode(qualifier_num, 18, qual_value1)) qual18_value1,
895 MAX(decode(qualifier_num, 18, qual_value2)) qual18_value2,
896 MAX(decode(qualifier_num, 18, qual_value3)) qual18_value3,
900 MAX(decode(qualifier_num, 19, qual_value3)) qual19_value3,
897 MAX(decode(qualifier_num, 19, terr_value_id)) qual19_value_id,
898 MAX(decode(qualifier_num, 19, qual_value1)) qual19_value1,
899 MAX(decode(qualifier_num, 19, qual_value2)) qual19_value2,
901 MAX(decode(qualifier_num, 20, terr_value_id)) qual20_value_id,
902 MAX(decode(qualifier_num, 20, qual_value1)) qual20_value1,
903 MAX(decode(qualifier_num, 20, qual_value2)) qual20_value2,
904 MAX(decode(qualifier_num, 20, qual_value3)) qual20_value3,
905 MAX(decode(qualifier_num, 21, terr_value_id)) qual21_value_id,
906 MAX(decode(qualifier_num, 21, qual_value1)) qual21_value1,
907 MAX(decode(qualifier_num, 21, qual_value2)) qual21_value2,
908 MAX(decode(qualifier_num, 21, qual_value3)) qual21_value3,
909 MAX(decode(qualifier_num, 22, terr_value_id)) qual22_value_id,
910 MAX(decode(qualifier_num, 22, qual_value1)) qual22_value1,
911 MAX(decode(qualifier_num, 22, qual_value2)) qual22_value2,
912 MAX(decode(qualifier_num, 22, qual_value3)) qual22_value3,
913 MAX(decode(qualifier_num, 23, terr_value_id)) qual23_value_id,
914 MAX(decode(qualifier_num, 23, qual_value1)) qual23_value1,
915 MAX(decode(qualifier_num, 23, qual_value2)) qual23_value2,
916 MAX(decode(qualifier_num, 23, qual_value3)) qual23_value3,
917 MAX(decode(qualifier_num, 24, terr_value_id)) qual24_value_id,
918 MAX(decode(qualifier_num, 24, qual_value1)) qual24_value1,
919 MAX(decode(qualifier_num, 24, qual_value2)) qual24_value2,
920 MAX(decode(qualifier_num, 24, qual_value3)) qual24_value3,
921 MAX(decode(qualifier_num, 25, terr_value_id)) qual25_value_id,
922 MAX(decode(qualifier_num, 25, qual_value1)) qual25_value1,
923 MAX(decode(qualifier_num, 25, qual_value2)) qual25_value2,
924 MAX(decode(qualifier_num, 25, qual_value3)) qual25_value3,
925
926 MAX(decode(qualifier_num, 26, terr_value_id)) qual26_value_id,
927 MAX(decode(qualifier_num, 26, qual_value1)) qual26_value1,
928 MAX(decode(qualifier_num, 26, qual_value2)) qual26_value2,
929 MAX(decode(qualifier_num, 26, qual_value3)) qual26_value3,
930 MAX(decode(qualifier_num, 27, terr_value_id)) qual27_value_id,
931 MAX(decode(qualifier_num, 27, qual_value1)) qual27_value1,
932 MAX(decode(qualifier_num, 27, qual_value2)) qual27_value2,
933 MAX(decode(qualifier_num, 27, qual_value3)) qual27_value3,
934 MAX(decode(qualifier_num, 28, terr_value_id)) qual28_value_id,
935 MAX(decode(qualifier_num, 28, qual_value1)) qual28_value1,
936 MAX(decode(qualifier_num, 28, qual_value2)) qual28_value2,
937 MAX(decode(qualifier_num, 28, qual_value3)) qual28_value3,
938 MAX(decode(qualifier_num, 29, terr_value_id)) qual29_value_id,
939 MAX(decode(qualifier_num, 29, qual_value1)) qual29_value1,
940 MAX(decode(qualifier_num, 29, qual_value2)) qual29_value2,
941 MAX(decode(qualifier_num, 29, qual_value3)) qual29_value3,
942 MAX(decode(qualifier_num, 30, terr_value_id)) qual30_value_id,
943 MAX(decode(qualifier_num, 30, qual_value1)) qual30_value1,
944 MAX(decode(qualifier_num, 30, qual_value2)) qual30_value2,
945 MAX(decode(qualifier_num, 30, qual_value3)) qual30_value3,
946 MAX(decode(qualifier_num, 31, terr_value_id)) qual31_value_id,
947 MAX(decode(qualifier_num, 31, qual_value1)) qual31_value1,
948 MAX(decode(qualifier_num, 31, qual_value2)) qual31_value2,
949 MAX(decode(qualifier_num, 31, qual_value3)) qual31_value3,
950 MAX(decode(qualifier_num, 32, terr_value_id)) qual32_value_id,
951 MAX(decode(qualifier_num, 32, qual_value1)) qual32_value1,
952 MAX(decode(qualifier_num, 32, qual_value2)) qual32_value2,
953 MAX(decode(qualifier_num, 32, qual_value3)) qual32_value3,
954 MAX(decode(qualifier_num, 33, terr_value_id)) qual33_value_id,
955 MAX(decode(qualifier_num, 33, qual_value1)) qual33_value1,
956 MAX(decode(qualifier_num, 33, qual_value2)) qual33_value2,
960 MAX(decode(qualifier_num, 34, qual_value2)) qual34_value2,
957 MAX(decode(qualifier_num, 33, qual_value3)) qual33_value3,
958 MAX(decode(qualifier_num, 34, terr_value_id)) qual34_value_id,
959 MAX(decode(qualifier_num, 34, qual_value1)) qual34_value1,
961 MAX(decode(qualifier_num, 34, qual_value3)) qual34_value3,
962 MAX(decode(qualifier_num, 35, terr_value_id)) qual35_value_id,
963 MAX(decode(qualifier_num, 35, qual_value1)) qual35_value1,
964 MAX(decode(qualifier_num, 35, qual_value2)) qual35_value2,
965 MAX(decode(qualifier_num, 35, qual_value3)) qual35_value3,
966
967 MAX(decode(qualifier_num, 36, terr_value_id)) qual36_value_id,
968 MAX(decode(qualifier_num, 36, qual_value1)) qual36_value1,
969 MAX(decode(qualifier_num, 36, qual_value2)) qual36_value2,
970 MAX(decode(qualifier_num, 36, qual_value3)) qual36_value3,
971 MAX(decode(qualifier_num, 37, terr_value_id)) qual37_value_id,
972 MAX(decode(qualifier_num, 37, qual_value1)) qual37_value1,
973 MAX(decode(qualifier_num, 37, qual_value2)) qual37_value2,
974 MAX(decode(qualifier_num, 37, qual_value3)) qual37_value3,
975 MAX(decode(qualifier_num, 38, terr_value_id)) qual38_value_id,
976 MAX(decode(qualifier_num, 38, qual_value1)) qual38_value1,
977 MAX(decode(qualifier_num, 38, qual_value2)) qual38_value2,
978 MAX(decode(qualifier_num, 38, qual_value3)) qual38_value3,
979 MAX(decode(qualifier_num, 39, terr_value_id)) qual39_value_id,
980 MAX(decode(qualifier_num, 39, qual_value1)) qual39_value1,
981 MAX(decode(qualifier_num, 39, qual_value2)) qual39_value2,
982 MAX(decode(qualifier_num, 39, qual_value3)) qual39_value3,
983 MAX(decode(qualifier_num, 40, terr_value_id)) qual40_value_id,
984 MAX(decode(qualifier_num, 40, qual_value1)) qual40_value1,
985 MAX(decode(qualifier_num, 40, qual_value2)) qual40_value2,
986 MAX(decode(qualifier_num, 40, qual_value3)) qual40_value3,
987 MAX(decode(qualifier_num, 41, terr_value_id)) qual41_value_id,
988 MAX(decode(qualifier_num, 41, qual_value1)) qual41_value1,
989 MAX(decode(qualifier_num, 41, qual_value2)) qual41_value2,
990 MAX(decode(qualifier_num, 41, qual_value3)) qual41_value3,
991 MAX(decode(qualifier_num, 42, terr_value_id)) qual42_value_id,
992 MAX(decode(qualifier_num, 42, qual_value1)) qual42_value1,
993 MAX(decode(qualifier_num, 42, qual_value2)) qual42_value2,
994 MAX(decode(qualifier_num, 42, qual_value3)) qual42_value3,
995 MAX(decode(qualifier_num, 43, terr_value_id)) qual43_value_id,
996 MAX(decode(qualifier_num, 43, qual_value1)) qual43_value1,
997 MAX(decode(qualifier_num, 43, qual_value2)) qual43_value2,
998 MAX(decode(qualifier_num, 43, qual_value3)) qual43_value3,
999 MAX(decode(qualifier_num, 44, terr_value_id)) qual44_value_id,
1000 MAX(decode(qualifier_num, 44, qual_value1)) qual44_value1,
1001 MAX(decode(qualifier_num, 44, qual_value2)) qual44_value2,
1002 MAX(decode(qualifier_num, 44, qual_value3)) qual44_value3,
1003 MAX(decode(qualifier_num, 45, terr_value_id)) qual45_value_id,
1004 MAX(decode(qualifier_num, 45, qual_value1)) qual45_value1,
1005 MAX(decode(qualifier_num, 45, qual_value2)) qual45_value2,
1006 MAX(decode(qualifier_num, 45, qual_value3)) qual45_value3,
1007
1008 MAX(decode(qualifier_num, 46, terr_value_id)) qual46_value_id,
1009 MAX(decode(qualifier_num, 46, qual_value1)) qual46_value1,
1010 MAX(decode(qualifier_num, 46, qual_value2)) qual46_value2,
1011 MAX(decode(qualifier_num, 46, qual_value3)) qual46_value3,
1012 MAX(decode(qualifier_num, 47, terr_value_id)) qual47_value_id,
1013 MAX(decode(qualifier_num, 47, qual_value1)) qual47_value1,
1014 MAX(decode(qualifier_num, 47, qual_value2)) qual47_value2,
1015 MAX(decode(qualifier_num, 47, qual_value3)) qual47_value3,
1016 MAX(decode(qualifier_num, 48, terr_value_id)) qual48_value_id,
1017 MAX(decode(qualifier_num, 48, qual_value1)) qual48_value1,
1018 MAX(decode(qualifier_num, 48, qual_value2)) qual48_value2,
1019 MAX(decode(qualifier_num, 48, qual_value3)) qual48_value3,
1020 MAX(decode(qualifier_num, 49, terr_value_id)) qual49_value_id,
1021 MAX(decode(qualifier_num, 49, qual_value1)) qual49_value1,
1022 MAX(decode(qualifier_num, 49, qual_value2)) qual49_value2,
1023 MAX(decode(qualifier_num, 49, qual_value3)) qual49_value3,
1024 MAX(decode(qualifier_num, 50, terr_value_id)) qual50_value_id,
1025 MAX(decode(qualifier_num, 50, qual_value1)) qual50_value1,
1026 MAX(decode(qualifier_num, 50, qual_value2)) qual50_value2,
1027 MAX(decode(qualifier_num, 50, qual_value3)) qual50_value3,
1028 MAX(decode(qualifier_num, 51, terr_value_id)) qual51_value_id,
1029 MAX(decode(qualifier_num, 51, qual_value1)) qual51_value1,
1030 MAX(decode(qualifier_num, 51, qual_value2)) qual51_value2,
1031 MAX(decode(qualifier_num, 51, qual_value3)) qual51_value3,
1032 MAX(decode(qualifier_num, 52, terr_value_id)) qual52_value_id,
1033 MAX(decode(qualifier_num, 52, qual_value1)) qual52_value1,
1034 MAX(decode(qualifier_num, 52, qual_value2)) qual52_value2,
1038 MAX(decode(qualifier_num, 53, qual_value2)) qual53_value2,
1035 MAX(decode(qualifier_num, 52, qual_value3)) qual52_value3,
1036 MAX(decode(qualifier_num, 53, terr_value_id)) qual53_value_id,
1037 MAX(decode(qualifier_num, 53, qual_value1)) qual53_value1,
1039 MAX(decode(qualifier_num, 53, qual_value3)) qual53_value3,
1040 MAX(decode(qualifier_num, 54, terr_value_id)) qual54_value_id,
1041 MAX(decode(qualifier_num, 54, qual_value1)) qual54_value1,
1042 MAX(decode(qualifier_num, 54, qual_value2)) qual54_value2,
1043 MAX(decode(qualifier_num, 54, qual_value3)) qual54_value3,
1044 MAX(decode(qualifier_num, 55, terr_value_id)) qual55_value_id,
1045 MAX(decode(qualifier_num, 55, qual_value1)) qual55_value1,
1046 MAX(decode(qualifier_num, 55, qual_value2)) qual55_value2,
1047 MAX(decode(qualifier_num, 55, qual_value3)) qual55_value3,
1048
1049 MAX(decode(qualifier_num, 56, terr_value_id)) qual56_value_id,
1050 MAX(decode(qualifier_num, 56, qual_value1)) qual56_value1,
1051 MAX(decode(qualifier_num, 56, qual_value2)) qual56_value2,
1052 MAX(decode(qualifier_num, 56, qual_value3)) qual56_value3,
1053 MAX(decode(qualifier_num, 57, terr_value_id)) qual57_value_id,
1054 MAX(decode(qualifier_num, 57, qual_value1)) qual57_value1,
1055 MAX(decode(qualifier_num, 57, qual_value2)) qual57_value2,
1056 MAX(decode(qualifier_num, 57, qual_value3)) qual57_value3,
1057 MAX(decode(qualifier_num, 58, terr_value_id)) qual58_value_id,
1058 MAX(decode(qualifier_num, 58, qual_value1)) qual58_value1,
1059 MAX(decode(qualifier_num, 58, qual_value2)) qual58_value2,
1060 MAX(decode(qualifier_num, 58, qual_value3)) qual58_value3,
1061 MAX(decode(qualifier_num, 59, terr_value_id)) qual59_value_id,
1062 MAX(decode(qualifier_num, 59, qual_value1)) qual59_value1,
1063 MAX(decode(qualifier_num, 59, qual_value2)) qual59_value2,
1064 MAX(decode(qualifier_num, 59, qual_value3)) qual59_value3,
1065 MAX(decode(qualifier_num, 60, terr_value_id)) qual60_value_id,
1066 MAX(decode(qualifier_num, 60, qual_value1)) qual60_value1,
1067 MAX(decode(qualifier_num, 60, qual_value2)) qual60_value2,
1068 MAX(decode(qualifier_num, 60, qual_value3)) qual60_value3,
1069 MAX(decode(qualifier_num, 61, terr_value_id)) qual61_value_id,
1070 MAX(decode(qualifier_num, 61, qual_value1)) qual61_value1,
1071 MAX(decode(qualifier_num, 61, qual_value2)) qual61_value2,
1072 MAX(decode(qualifier_num, 61, qual_value3)) qual61_value3,
1073 MAX(decode(qualifier_num, 62, terr_value_id)) qual62_value_id,
1074 MAX(decode(qualifier_num, 62, qual_value1)) qual62_value1,
1075 MAX(decode(qualifier_num, 62, qual_value2)) qual62_value2,
1076 MAX(decode(qualifier_num, 62, qual_value3)) qual62_value3,
1077 MAX(decode(qualifier_num, 63, terr_value_id)) qual63_value_id,
1078 MAX(decode(qualifier_num, 63, qual_value1)) qual63_value1,
1079 MAX(decode(qualifier_num, 63, qual_value2)) qual63_value2,
1080 MAX(decode(qualifier_num, 63, qual_value3)) qual63_value3,
1081 MAX(decode(qualifier_num, 64, terr_value_id)) qual64_value_id,
1082 MAX(decode(qualifier_num, 64, qual_value1)) qual64_value1,
1083 MAX(decode(qualifier_num, 64, qual_value2)) qual64_value2,
1084 MAX(decode(qualifier_num, 64, qual_value3)) qual64_value3,
1085 MAX(decode(qualifier_num, 65, terr_value_id)) qual65_value_id,
1086 MAX(decode(qualifier_num, 65, qual_value1)) qual65_value1,
1087 MAX(decode(qualifier_num, 65, qual_value2)) qual65_value2,
1088 MAX(decode(qualifier_num, 65, qual_value3)) qual65_value3,
1089
1090 MAX(decode(qualifier_num, 66, terr_value_id)) qual66_value_id,
1091 MAX(decode(qualifier_num, 66, qual_value1)) qual66_value1,
1092 MAX(decode(qualifier_num, 66, qual_value2)) qual66_value2,
1093 MAX(decode(qualifier_num, 66, qual_value3)) qual66_value3,
1094 MAX(decode(qualifier_num, 67, terr_value_id)) qual67_value_id,
1095 MAX(decode(qualifier_num, 67, qual_value1)) qual67_value1,
1096 MAX(decode(qualifier_num, 67, qual_value2)) qual67_value2,
1097 MAX(decode(qualifier_num, 67, qual_value3)) qual67_value3,
1098 MAX(decode(qualifier_num, 68, terr_value_id)) qual68_value_id,
1099 MAX(decode(qualifier_num, 68, qual_value1)) qual68_value1,
1100 MAX(decode(qualifier_num, 68, qual_value2)) qual68_value2,
1101 MAX(decode(qualifier_num, 68, qual_value3)) qual68_value3,
1102 MAX(decode(qualifier_num, 69, terr_value_id)) qual69_value_id,
1103 MAX(decode(qualifier_num, 69, qual_value1)) qual69_value1,
1104 MAX(decode(qualifier_num, 69, qual_value2)) qual69_value2,
1105 MAX(decode(qualifier_num, 69, qual_value3)) qual69_value3,
1106 MAX(decode(qualifier_num, 70, terr_value_id)) qual70_value_id,
1107 MAX(decode(qualifier_num, 70, qual_value1)) qual70_value1,
1108 MAX(decode(qualifier_num, 70, qual_value2)) qual70_value2,
1109 MAX(decode(qualifier_num, 70, qual_value3)) qual70_value3,
1110 MAX(decode(qualifier_num, 71, terr_value_id)) qual71_value_id,
1111 MAX(decode(qualifier_num, 71, qual_value1)) qual71_value1,
1112 MAX(decode(qualifier_num, 71, qual_value2)) qual71_value2,
1113 MAX(decode(qualifier_num, 71, qual_value3)) qual71_value3,
1114 MAX(decode(qualifier_num, 72, terr_value_id)) qual72_value_id,
1115 MAX(decode(qualifier_num, 72, qual_value1)) qual72_value1,
1116 MAX(decode(qualifier_num, 72, qual_value2)) qual72_value2,
1120 MAX(decode(qualifier_num, 73, qual_value2)) qual73_value2,
1117 MAX(decode(qualifier_num, 72, qual_value3)) qual72_value3,
1118 MAX(decode(qualifier_num, 73, terr_value_id)) qual73_value_id,
1119 MAX(decode(qualifier_num, 73, qual_value1)) qual73_value1,
1121 MAX(decode(qualifier_num, 73, qual_value3)) qual73_value3,
1122 MAX(decode(qualifier_num, 74, terr_value_id)) qual74_value_id,
1123 MAX(decode(qualifier_num, 74, qual_value1)) qual74_value1,
1124 MAX(decode(qualifier_num, 74, qual_value2)) qual74_value2,
1125 MAX(decode(qualifier_num, 74, qual_value3)) qual74_value3,
1126 MAX(decode(qualifier_num, 75, terr_value_id)) qual75_value_id,
1127 MAX(decode(qualifier_num, 75, qual_value1)) qual75_value1,
1128 MAX(decode(qualifier_num, 75, qual_value2)) qual75_value2,
1129 MAX(decode(qualifier_num, 75, qual_value3)) qual75_value3
1130
1131 FROM
1132 (SELECT rank() over(PARTITION BY jtq.terr_id, qgt.qual_usg_id
1133 ORDER BY rownum) row_id,
1134 jtva.terr_value_id,
1135 qgt.qualifier_num,
1136 decode(qgt.display_type, 'CHAR', decode(qgt.convert_to_id_flag, 'Y',
1137 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1138 qgt.display_sql1, jtva.low_value_char_id, NULL), 'N',
1139 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1140 qgt.display_sql1, jtva.low_value_char, NULL), NULL), 'CHAR_2IDS',
1141 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1142 qgt.display_sql1, jtva.value1_id, jtva.value2_id), 'DEP_2FIELDS',
1143 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1144 qgt.display_sql1, jtva.value1_id, -9999), 'DEP_2FIELDS_1CHAR_1ID',
1145 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1146 qgt.display_sql1, jtva.low_value_char, NULL), 'DEP_2FIELDS_CHAR_2IDS',
1147 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1148 qgt.display_sql1, jtva.value1_id, -9999), 'DEP_3FIELDS',
1149 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1150 qgt.display_sql1, jtva.value1_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',
1151 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1152 qgt.display_sql1, jtva.value1_id, -9999), 'INTEREST_TYPE',
1153 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1154 qgt.display_sql1, jtva.interest_type_id, NULL), 'NUMERIC', jtva.low_value_number, 'CURRENCY',
1155 jtva.low_value_number, NULL) qual_value1,
1156 decode(qgt.display_type, 'CHAR', jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag,
1157 qgt.display_type, NULL, qgt.display_sql2, jtva.high_value_char, NULL), 'CHAR_2IDS', NULL,
1158 'DEP_2FIELDS', jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1159 qgt.display_sql2, jtva.value2_id, -9999), 'DEP_2FIELDS_1CHAR_1ID',
1160 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1161 qgt.display_sql2, jtva.low_value_char_id, NULL), 'DEP_2FIELDS_CHAR_2IDS',
1162 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1163 qgt.display_sql2, jtva.value2_id, -9999), 'DEP_3FIELDS',
1164 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1165 qgt.display_sql2, jtva.value2_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',
1166 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1167 qgt.display_sql2, jtva.value2_id, -9999), 'INTEREST_TYPE',
1168 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1169 qgt.display_sql2, jtva.primary_interest_code_id, NULL), 'NUMERIC', jtva.high_value_number,
1170 'CURRENCY', jtva.high_value_number, NULL) qual_value2,
1171 decode(qgt.display_type, 'DEP_3FIELDS', jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag,
1172 qgt.display_type, NULL, qgt.display_sql3, jtva.value3_id, NULL), 'DEP_3FIELDS_CHAR_3IDS',
1173 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1174 qgt.display_sql3, jtva.value3_id, jtva.value4_id), 'INTEREST_TYPE',
1175 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1176 qgt.display_sql3, jtva.secondary_interest_code_id, NULL), 'CURRENCY',
1177 jtf_territory_pvt.get_terr_value_desc(qgt.convert_to_id_flag, qgt.display_type, NULL,
1178 qgt.display_sql2, jtva.currency_code, NULL), NULL) qual_value3,
1179 NULL qual_value4
1180 FROM jty_webadi_qual_header qgt,
1181 jtf_terr_values_all jtva,
1182 jtf_terr_qual_all jtq
1183 WHERE qgt.qual_usg_id = jtq.qual_usg_id
1184 AND jtq.terr_qual_id = jtva.terr_qual_id --AND qgt.qualifier_num = 1
1185 AND jtq.org_id = p_org_id
1186 AND qgt.user_sequence = p_user_sequence
1187 AND jtq.terr_id = p_terr_rec.terr_id(i))
1188 GROUP BY row_id)
1189 ;
1190
1191 l_header := 'RSC';
1192
1193 forall i IN p_terr_rec.terr_id.FIRST .. p_terr_rec.terr_id.LAST
1194 INSERT INTO jty_webadi_resources(user_sequence, interface_type, header, terr_id, terr_rsc_id,
1195 resource_name, resource_group, resource_role, resource_id, group_id, role_code,
1199 trans_access_code1, terr_rsc_access_id1, trans_access_code2, terr_rsc_access_id2,
1196 resource_type, res_start_date, res_end_date, email, attribute_category, attribute1,
1197 attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8,
1198 attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15,
1200 trans_access_code3, terr_rsc_access_id3, trans_access_code4, terr_rsc_access_id4,
1201 trans_access_code5, terr_rsc_access_id5, trans_access_code6, terr_rsc_access_id6,
1202 trans_access_code7, terr_rsc_access_id7, trans_access_code8, terr_rsc_access_id8,
1203 trans_access_code9, terr_rsc_access_id9, trans_access_code10, terr_rsc_access_id10)
1204 SELECT terr_rsc.user_sequence,
1205 p_interface_type,
1206 l_header,
1207 terr_rsc.terr_id,
1208 terr_rsc.terr_rsc_id,
1209 get_resource_name(terr_rsc.resource_type, terr_rsc.resource_id, terr_rsc.group_id, terr_rsc.role_id, terr_rsc.role) resource_name,
1210 get_group_name(terr_rsc.resource_type, terr_rsc.group_id, terr_rsc.resource_id) resource_group,
1211 get_role_name(terr_rsc.resource_type, terr_rsc.role) resource_role,
1212 terr_rsc.resource_id resource_id,
1213 terr_rsc.group_id group_id,
1214 terr_rsc.role role_code,
1215 decode(terr_rsc.resource_type, 'RS_GROUP', 1, 'RS_TEAM', 2, 'RS_ROLE', 3, 0) resource_type,
1216 terr_rsc.start_date,
1217 terr_rsc.end_date,
1218 get_email(terr_rsc.resource_type, terr_rsc.resource_id),
1219 terr_rsc.attribute_category,
1220 terr_rsc.attribute1,
1221 terr_rsc.attribute2,
1222 terr_rsc.attribute3,
1223 terr_rsc.attribute4,
1224 terr_rsc.attribute5,
1225 terr_rsc.attribute6,
1226 terr_rsc.attribute7,
1227 terr_rsc.attribute8,
1228 terr_rsc.attribute9,
1229 terr_rsc.attribute10,
1230 terr_rsc.attribute11,
1231 terr_rsc.attribute12,
1232 terr_rsc.attribute13,
1233 terr_rsc.attribute14,
1234 terr_rsc.attribute15,
1235 terr_rsc.qual_type1_val,
1236 terr_rsc.terr_rsc_access_id1,
1237 terr_rsc.qual_type2_val,
1238 terr_rsc.terr_rsc_access_id2,
1239 terr_rsc.qual_type3_val,
1240 terr_rsc.terr_rsc_access_id3,
1241 terr_rsc.qual_type4_val,
1242 terr_rsc.terr_rsc_access_id4,
1243 terr_rsc.qual_type5_val,
1244 terr_rsc.terr_rsc_access_id5,
1245 terr_rsc.qual_type6_val,
1246 terr_rsc.terr_rsc_access_id6,
1247 terr_rsc.qual_type7_val,
1248 terr_rsc.terr_rsc_access_id7,
1249 terr_rsc.qual_type8_val,
1250 terr_rsc.terr_rsc_access_id8,
1251 terr_rsc.qual_type9_val,
1252 terr_rsc.terr_rsc_access_id9,
1253 terr_rsc.qual_type10_val,
1254 terr_rsc.terr_rsc_access_id10
1255 FROM
1256 (SELECT jqth.user_sequence,
1257 jtr.terr_id,
1258 jtr.resource_type,
1259 jtr.terr_rsc_id,
1260 jtr.resource_id,
1261 decode(jtr.resource_type, 'RS_GROUP', jtr.resource_id, jtr.group_id) group_id,
1262 decode(jtr.resource_type, 'RS_ROLE', jtr.resource_id, NULL) role_id,
1263 jtr.role,
1264 jtr.start_date_active start_date,
1265 jtr.end_date_active end_date,
1266 jtr.attribute_category,
1267 jtr.attribute1,
1268 jtr.attribute2,
1269 jtr.attribute3,
1270 jtr.attribute4,
1271 jtr.attribute5,
1272 jtr.attribute6,
1273 jtr.attribute7,
1274 jtr.attribute8,
1275 jtr.attribute9,
1276 jtr.attribute10,
1277 jtr.attribute11,
1278 jtr.attribute12,
1279 jtr.attribute13,
1280 jtr.attribute14,
1281 jtr.attribute15,
1282 MAX(decode(jqth.qual_type_num, 1, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id1,
1283 MAX(decode(jqth.qual_type_num, 1, fnd.description, NULL)) qual_type1_val,
1284 MAX(decode(jqth.qual_type_num, 2, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id2,
1285 MAX(decode(jqth.qual_type_num, 2, fnd.description, NULL)) qual_type2_val,
1286 MAX(decode(jqth.qual_type_num, 3, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id3,
1287 MAX(decode(jqth.qual_type_num, 3, fnd.description, NULL)) qual_type3_val,
1288 MAX(decode(jqth.qual_type_num, 4, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id4,
1289 MAX(decode(jqth.qual_type_num, 4, fnd.description, NULL)) qual_type4_val,
1290 MAX(decode(jqth.qual_type_num, 5, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id5,
1291 MAX(decode(jqth.qual_type_num, 5, fnd.description, NULL)) qual_type5_val,
1292 MAX(decode(jqth.qual_type_num, 6, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id6,
1293 MAX(decode(jqth.qual_type_num, 6, fnd.description, NULL)) qual_type6_val,
1294 MAX(decode(jqth.qual_type_num, 7, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id7,
1295 MAX(decode(jqth.qual_type_num, 7, fnd.description, NULL)) qual_type7_val,
1296 MAX(decode(jqth.qual_type_num, 8, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id8,
1297 MAX(decode(jqth.qual_type_num, 8, fnd.description, NULL)) qual_type8_val,
1298 MAX(decode(jqth.qual_type_num, 9, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id9,
1299 MAX(decode(jqth.qual_type_num, 9, fnd.description, NULL)) qual_type9_val,
1300 MAX(decode(jqth.qual_type_num, 10, jtra.terr_rsc_access_id, NULL)) terr_rsc_access_id10,
1301 MAX(decode(jqth.qual_type_num, 10, fnd.description, NULL)) qual_type10_val
1302 FROM jtf_sources_all jsa,
1303 jtf_terr_rsc_access_all jtra,
1304 jty_webadi_qual_type_header jqth,
1308 AND jsa.source_id = p_usage_id
1305 jtf_terr_rsc_all jtr,
1306 fnd_lookups fnd
1307 WHERE jsa.rsc_access_lkup = fnd.lookup_type
1309 AND jtra.trans_access_code = fnd.lookup_code
1310 AND jtra.access_type(+) = jqth.qual_type_name
1311 AND jqth.user_sequence = p_user_sequence
1312 AND jtr.terr_id = p_terr_rec.terr_id(i)
1313 AND jtra.terr_rsc_id = jtr.terr_rsc_id
1314 GROUP BY jtr.terr_id,
1315 jqth.user_sequence,
1316 jtr.terr_rsc_id,
1317 jtr.resource_id,
1318 jtr.group_id,
1319 jtr.role,
1320 jtr.resource_type,
1321 jtr.start_date_active,
1322 jtr.end_date_active,
1323 jtr.attribute_category,
1324 jtr.attribute1,
1325 jtr.attribute2,
1326 jtr.attribute3,
1327 jtr.attribute4,
1328 jtr.attribute5,
1329 jtr.attribute6,
1330 jtr.attribute7,
1331 jtr.attribute8,
1332 jtr.attribute9,
1333 jtr.attribute10,
1334 jtr.attribute11,
1335 jtr.attribute12,
1336 jtr.attribute13,
1337 jtr.attribute14,
1338 jtr.attribute15)
1339 terr_rsc;
1340
1341 forall i IN p_terr_rec.terr_id.FIRST .. p_terr_rec.terr_id.LAST
1342 INSERT INTO jty_webadi_oth_terr_intf(interface_type, user_sequence, terr_rsc_id, action_flag,
1343 header, user_id, org_id, org_name, usage_id, parent_terr_id, terr_id, terr_name,
1344 hierarchy, creation_date, created_by, last_update_date, last_updated_by, last_update_login)
1345 SELECT jwr.interface_type,
1346 jwr.user_sequence,
1347 jwr.terr_rsc_id,
1348 l_action_flag,
1349 l_header,
1350 p_user_id,
1351 p_org_id,
1352 p_terr_rec.org_name(i),
1353 p_usage_id,
1354 p_terr_rec.parent_terr_id(i),
1355 p_terr_rec.terr_id(i),
1356 p_terr_rec.terr_name(i),
1357 p_terr_rec.hierarchy(i),
1358 l_today_date,
1359 p_user_id,
1360 l_today_date,
1361 p_user_id,
1362 p_user_id
1363 FROM jty_webadi_resources jwr
1364 WHERE jwr.user_sequence = p_user_sequence
1365 AND jwr.interface_type = p_interface_type
1366 AND jwr.header = l_header
1367 AND jwr.terr_id = p_terr_rec.terr_id(i);
1368
1369 END IF;
1370
1371 x_retcode := fnd_api.g_ret_sts_success;
1372 x_errbuf := 'Success';
1373
1374 COMMIT;
1375
1376 EXCEPTION
1377 WHEN others THEN
1378 x_retcode := fnd_api.g_ret_sts_error;
1379 x_errbuf := 'Other errors in download territory definition: ' || SQLCODE || ': ' || sqlerrm;
1380
1381 END dl_all_territories;
1382
1383 PROCEDURE populate_webadi_interface(p_usage_id IN NUMBER, p_user_id IN NUMBER, p_terr_id IN NUMBER,
1384 p_org_id IN NUMBER, p_type_id IN NUMBER, p_mode IN VARCHAR2 DEFAULT 'NODE',
1385 p_view IN VARCHAR2 DEFAULT 'TERR', p_geo_type IN NUMBER, p_active IN DATE,
1386 p_terr_id_array IN VARCHAR2 DEFAULT NULL, x_seq OUT nocopy VARCHAR2, x_retcode OUT nocopy VARCHAR2,
1387 x_errbuf OUT nocopy VARCHAR2) IS
1388
1389 l_seq NUMBER;
1390 l_intf_type VARCHAR2(1) := 'D';
1391 l_mode VARCHAR2(15);
1392 l_view VARCHAR2(15);
1393 l_active VARCHAR2(1);
1394 l_string VARCHAR2(5000);
1395 l_cnt NUMBER;
1396 l_value NUMBER;
1397 l_no_of_qualifiers NUMBER;
1398
1399 CURSOR get_single_terr(v_terr_id NUMBER, v_org_id NUMBER) IS
1400 SELECT terr.terr_id,
1401 decode(terr.terr_id, 1, hr.name, terr.name) terr_name,
1402 terr.rank,
1403 terr.num_winners,
1404 terr.start_date_active start_date,
1405 terr.end_date_active end_date,
1406 terr_type.name terr_type_name,
1407 terr_type.terr_type_id,
1408 terr.parent_territory_id parent_terr_id,
1409 hr.name org_name,
1410 --decode(reverse(substr(sub.hierarchy,2)),terr.name,null,
1411 -- replace(reverse(substr(sub.hierarchy,2)),'/','->')) hierachy,
1412 LTRIM(RTRIM(RTRIM(REPLACE(REVERSE(SUBSTR(sub.hierarchy, 2)), '/', '->'), terr.name), '->'), '->') hierachy,
1413 terr.attribute_category,
1414 terr.attribute1,
1415 terr.attribute2,
1416 terr.attribute3,
1417 terr.attribute4,
1418 terr.attribute5,
1419 terr.attribute6,
1420 terr.attribute7,
1421 terr.attribute8,
1422 terr.attribute9,
1423 terr.attribute10,
1424 terr.attribute11,
1425 terr.attribute12,
1426 terr.attribute13,
1427 terr.attribute14,
1428 terr.attribute15
1429 FROM hr_operating_units hr,
1430 jtf_terr_types_all terr_type,
1431 jtf_terr_all terr,
1432 (SELECT MAX(sys_connect_by_path(REVERSE(terr.name), '/')) hierarchy
1433 FROM jtf_terr_all terr
1434 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
1435 AND terr.terr_id <> 1)
1436 sub
1437 WHERE terr.org_id = hr.organization_id
1438 AND terr.territory_type_id = terr_type.terr_type_id
1439 AND terr.terr_id = v_terr_id
1440 AND terr.org_id = v_org_id;
1441
1442 CURSOR get_all_terr(v_terr_id NUMBER, v_org_id NUMBER, v_active DATE, v_parent_terr_hierarchy VARCHAR2) IS
1443 SELECT sub.terr_id,
1444 decode(sub.terr_id, 1, hr.name, sub.terr_name) terr_name,
1445 sub.rank,
1446 sub.num_winners,
1447 sub.start_date,
1448 sub.end_date,
1449 terr_type.name terr_type_name,
1450 terr_type.terr_type_id,
1451 sub.parent_terr_id,
1452 hr.name org_name,
1453 RTRIM(v_parent_terr_hierarchy||LTRIM(RTRIM(REPLACE(sub.hierarchy, sub.terr_name, ''), '->'), '->'), '->') hierarchy,
1454 sub.attribute_category,
1455 sub.attribute1,
1456 sub.attribute2,
1457 sub.attribute3,
1458 sub.attribute4,
1459 sub.attribute5,
1463 sub.attribute9,
1460 sub.attribute6,
1461 sub.attribute7,
1462 sub.attribute8,
1464 sub.attribute10,
1465 sub.attribute11,
1466 sub.attribute12,
1467 sub.attribute13,
1468 sub.attribute14,
1469 sub.attribute15
1470 FROM hr_operating_units hr,
1471 jtf_terr_types_all terr_type,
1472 (SELECT terr.terr_id terr_id,
1473 terr.name terr_name,
1474 sys_connect_by_path(terr.name, '->') hierarchy,
1475 terr.parent_territory_id parent_terr_id,
1476 terr.rank rank,
1477 terr.num_winners num_winners,
1478 terr.start_date_active start_date,
1479 terr.end_date_active end_date,
1480 terr.territory_type_id terr_type_id,
1481 terr.org_id,
1482 terr.attribute_category,
1483 terr.attribute1,
1484 terr.attribute2,
1485 terr.attribute3,
1486 terr.attribute4,
1487 terr.attribute5,
1488 terr.attribute6,
1489 terr.attribute7,
1490 terr.attribute8,
1491 terr.attribute9,
1492 terr.attribute10,
1493 terr.attribute11,
1494 terr.attribute12,
1495 terr.attribute13,
1496 terr.attribute14,
1497 terr.attribute15
1498 FROM jtf_terr_all terr
1499 WHERE terr.org_id = v_org_id
1500 AND nvl(terr.terr_group_flag, 'N') = 'N'
1501 AND nvl(terr.enable_self_service, 'N') = 'N'
1502 AND(v_active BETWEEN terr.start_date_active
1503 AND terr.end_date_active OR v_active IS NULL) CONNECT BY terr.parent_territory_id = PRIOR terr.terr_id
1504 AND terr.terr_id <> 1 START WITH terr.terr_id = v_terr_id
1505 ORDER siblings BY terr.terr_id)
1506 sub
1507 WHERE sub.org_id = hr.organization_id
1508 AND sub.terr_type_id = terr_type.terr_type_id;
1509
1510 CURSOR get_imm_children(v_terr_id NUMBER, v_org_id NUMBER, v_active DATE, v_parent_terr_hierarchy VARCHAR2) IS
1511 SELECT sub.terr_id,
1512 decode(sub.terr_id, 1, hr.name, sub.terr_name) terr_name,
1513 sub.rank,
1514 sub.num_winners,
1515 sub.start_date,
1516 sub.end_date,
1517 terr_type.name terr_type_name,
1518 terr_type.terr_type_id,
1519 sub.parent_terr_id,
1520 hr.name org_name,
1521 RTRIM(v_parent_terr_hierarchy||LTRIM(RTRIM(REPLACE(sub.hierarchy, sub.terr_name, ''), '->'), '->'), '->') hierarchy,
1522 sub.attribute_category,
1523 sub.attribute1,
1524 sub.attribute2,
1525 sub.attribute3,
1526 sub.attribute4,
1527 sub.attribute5,
1528 sub.attribute6,
1529 sub.attribute7,
1530 sub.attribute8,
1531 sub.attribute9,
1532 sub.attribute10,
1533 sub.attribute11,
1534 sub.attribute12,
1535 sub.attribute13,
1536 sub.attribute14,
1537 sub.attribute15
1538 FROM hr_operating_units hr,
1539 jtf_terr_types_all terr_type,
1540 (SELECT terr.terr_id terr_id,
1541 terr.name terr_name,
1542 sys_connect_by_path(terr.name, '->') hierarchy,
1543 terr.parent_territory_id parent_terr_id,
1544 terr.rank rank,
1545 terr.num_winners num_winners,
1546 terr.start_date_active start_date,
1547 terr.end_date_active end_date,
1548 terr.territory_type_id terr_type_id,
1549 terr.org_id,
1550 terr.attribute_category,
1551 terr.attribute1,
1552 terr.attribute2,
1553 terr.attribute3,
1554 terr.attribute4,
1555 terr.attribute5,
1556 terr.attribute6,
1557 terr.attribute7,
1558 terr.attribute8,
1559 terr.attribute9,
1560 terr.attribute10,
1561 terr.attribute11,
1562 terr.attribute12,
1563 terr.attribute13,
1564 terr.attribute14,
1565 terr.attribute15
1566 FROM jtf_terr_all terr
1567 WHERE terr.org_id = v_org_id
1568 AND LEVEL < 3
1569 AND nvl(terr.terr_group_flag, 'N') = 'N'
1570 AND nvl(terr.enable_self_service, 'N') = 'N'
1571 AND(v_active BETWEEN terr.start_date_active
1572 AND terr.end_date_active OR v_active IS NULL) CONNECT BY terr.parent_territory_id = PRIOR terr.terr_id
1573 AND terr.terr_id <> 1 START WITH terr.terr_id = v_terr_id
1574 ORDER siblings BY terr.terr_id)
1575 sub
1576 WHERE sub.org_id = hr.organization_id
1577 AND sub.terr_type_id = terr_type.terr_type_id;
1578
1579 CURSOR get_search_csr(v_org_id NUMBER, v_active DATE) IS
1580 SELECT sub.terr_id,
1581 decode(sub.terr_id, 1, hr.name, sub.terr_name) terr_name,
1582 sub.rank,
1583 sub.num_winners,
1584 sub.start_date,
1585 sub.end_date,
1586 terr_type.name terr_type_name,
1587 terr_type.terr_type_id,
1588 sub.parent_terr_id,
1589 hr.name org_name,
1590 LTRIM(RTRIM(REPLACE(sub.hierarchy, sub.terr_name, ''), '->'), '->') hierarchy,
1591 sub.attribute_category,
1592 sub.attribute1,
1593 sub.attribute2,
1594 sub.attribute3,
1595 sub.attribute4,
1596 sub.attribute5,
1597 sub.attribute6,
1598 sub.attribute7,
1599 sub.attribute8,
1600 sub.attribute9,
1601 sub.attribute10,
1602 sub.attribute11,
1603 sub.attribute12,
1604 sub.attribute13,
1605 sub.attribute14,
1606 sub.attribute15
1607 FROM hr_operating_units hr,
1608 jtf_terr_types_all terr_type,
1609 (SELECT terr.terr_id terr_id,
1610 terr.name terr_name,
1611 sys_connect_by_path(terr.name, '->') hierarchy,
1612 terr.parent_territory_id parent_terr_id,
1613 terr.rank rank,
1614 terr.num_winners num_winners,
1615 terr.start_date_active start_date,
1616 terr.end_date_active end_date,
1617 terr.territory_type_id terr_type_id,
1618 terr.org_id,
1619 terr.attribute_category,
1620 terr.attribute1,
1621 terr.attribute2,
1622 terr.attribute3,
1623 terr.attribute4,
1627 terr.attribute8,
1624 terr.attribute5,
1625 terr.attribute6,
1626 terr.attribute7,
1628 terr.attribute9,
1629 terr.attribute10,
1630 terr.attribute11,
1631 terr.attribute12,
1632 terr.attribute13,
1633 terr.attribute14,
1634 terr.attribute15
1635 FROM jtf_terr_all terr
1636 WHERE terr.org_id = v_org_id
1637 AND nvl(terr.terr_group_flag, 'N') = 'N'
1638 AND nvl(terr.enable_self_service, 'N') = 'N'
1639 AND(v_active BETWEEN terr.start_date_active
1640 AND terr.end_date_active OR v_active IS NULL) CONNECT BY terr.parent_territory_id = PRIOR terr.terr_id
1641 AND terr.terr_id <> 1 START WITH terr.terr_id IN
1642 (SELECT num_col
1643 FROM jty_str_to_table_gt)
1644 ORDER siblings BY terr.terr_id)
1645 sub
1646 WHERE sub.org_id = hr.organization_id
1647 AND sub.terr_type_id = terr_type.terr_type_id;
1648
1649 l_terr_rec terr_rec_type;
1650 --l_all_terr_rec_tbl terr_rec_tbl_type;
1651 l_parent_terr_hierarchy VARCHAR2(2000);
1652 BEGIN
1653 mo_global.set_org_context(p_org_id, NULL, 'JTF');
1654
1655 x_retcode := 'S';
1656 x_errbuf := 'Success';
1657 l_mode := nvl(p_mode, 'NODE');
1658 l_view := nvl(p_view, 'TERR');
1659
1660 BEGIN
1661 SELECT jty_webadi_oth_terr_intf_s.nextval
1662 INTO l_seq
1663 FROM dual;
1664 -- remove existing old data which is older than 3 days
1665 --Following query changed for bug 8734322 to stop webadi territory tables to grow in size because earlier where condition
1666 -- checks for user id and if that user never comes for second time then that data remains in table
1667
1668 DELETE FROM jty_webadi_oth_terr_intf
1669 WHERE
1670 -- user_id = p_user_id AND
1671 creation_date <= sysdate -3;
1672
1673 DELETE FROM jty_webadi_resources jwr
1674 WHERE NOT EXISTS
1675 (SELECT 1
1676 FROM jty_webadi_oth_terr_intf jwot
1677 WHERE jwot.user_sequence = jwr.user_sequence)
1678 ;
1679
1680 COMMIT;
1681
1682 EXCEPTION
1683 WHEN others THEN
1684 NULL;
1685 END;
1686
1687 --Added for bug 8200357
1688 IF l_mode <> 'SEARCH'
1689 THEN
1690 --Added for bug 7639213
1691 SELECT RTRIM(REPLACE(sub.hierarchy,'/','->'),terr.name) hierarchy
1692 INTO l_parent_terr_hierarchy
1693 FROM jtf_terr_all terr,
1694 (SELECT REVERSE(SUBSTR(MAX(sys_connect_by_path(REVERSE(terr.name),'/')),2)) hierarchy
1695 FROM jtf_terr_all terr
1696 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
1697 AND terr.terr_id <> 1
1698 ) sub
1699 WHERE terr.org_id = p_org_id
1700 AND terr.terr_id = p_terr_id ;
1701
1702 END IF;
1703
1704
1705 -- Populate a global table with qualfiers details that
1706 -- needs to be displayed
1707 --get qualifier header information
1708 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);
1709
1710 -- get qual type header information
1711 get_qual_type_header(p_usage_id => p_usage_id, p_user_sequence => l_seq);
1712
1713 --dbms_output.put_line('get_qual_details: Returns l_no_of_qualifiers ' || l_no_of_qualifiers);
1714
1715 IF(l_no_of_qualifiers > 75) THEN
1716 fnd_message.clear;
1717 fnd_message.set_name('JTF', 'JTY_OTH_TERR_TOO_MANY_QUAL');
1718 fnd_message.set_token('POSITION', l_no_of_qualifiers);
1719 x_retcode := fnd_api.g_ret_sts_error;
1720 x_errbuf := fnd_message.GET();
1721
1722 --APP_EXCEPTION.RAISE_EXCEPTION;
1723 ELSIF(l_no_of_qualifiers = 0) THEN
1724 fnd_message.clear;
1725 fnd_message.set_name('JTF', 'JTY_OTH_TERR_NO_QUAL_ENABLED');
1726 x_retcode := fnd_api.g_ret_sts_error;
1727 x_errbuf := fnd_message.GET();
1728
1729 --APP_EXCEPTION.RAISE_EXCEPTION;
1730 ELSE
1731 -- start populating terr detail
1732 CASE l_mode
1733 WHEN 'NODE' THEN
1734
1735 OPEN get_single_terr(p_terr_id, p_org_id);
1736 FETCH get_single_terr bulk collect
1737 INTO l_terr_rec.terr_id,
1738 l_terr_rec.terr_name,
1739 l_terr_rec.rank,
1740 l_terr_rec.num_winners,
1741 l_terr_rec.start_date,
1742 l_terr_rec.end_date,
1743 l_terr_rec.terr_type_name,
1744 l_terr_rec.terr_type_id,
1745 l_terr_rec.parent_terr_id,
1746 l_terr_rec.org_name,
1747 l_terr_rec.hierarchy,
1748 l_terr_rec.attribute_category,
1749 l_terr_rec.attribute1,
1750 l_terr_rec.attribute2,
1751 l_terr_rec.attribute3,
1752 l_terr_rec.attribute4,
1753 l_terr_rec.attribute5,
1754 l_terr_rec.attribute6,
1755 l_terr_rec.attribute7,
1756 l_terr_rec.attribute8,
1757 l_terr_rec.attribute9,
1758 l_terr_rec.attribute10,
1759 l_terr_rec.attribute11,
1760 l_terr_rec.attribute12,
1761 l_terr_rec.attribute13,
1762 l_terr_rec.attribute14,
1763 l_terr_rec.attribute15;
1764 CLOSE get_single_terr;
1765
1766 -- call download single terr procedure
1767 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);
1768
1769 COMMIT;
1770 -- process immediate children
1771 WHEN 'IMM' THEN
1772 CASE l_view
1773 WHEN 'TERR' THEN
1774
1778 l_terr_rec.terr_name,
1775 OPEN get_imm_children(p_terr_id, p_org_id, p_active, l_parent_terr_hierarchy);
1776 FETCH get_imm_children bulk collect
1777 INTO l_terr_rec.terr_id,
1779 l_terr_rec.rank,
1780 l_terr_rec.num_winners,
1781 l_terr_rec.start_date,
1782 l_terr_rec.end_date,
1783 l_terr_rec.terr_type_name,
1784 l_terr_rec.terr_type_id,
1785 l_terr_rec.parent_terr_id,
1786 l_terr_rec.org_name,
1787 l_terr_rec.hierarchy,
1788 l_terr_rec.attribute_category,
1789 l_terr_rec.attribute1,
1790 l_terr_rec.attribute2,
1791 l_terr_rec.attribute3,
1792 l_terr_rec.attribute4,
1793 l_terr_rec.attribute5,
1794 l_terr_rec.attribute6,
1795 l_terr_rec.attribute7,
1796 l_terr_rec.attribute8,
1797 l_terr_rec.attribute9,
1798 l_terr_rec.attribute10,
1799 l_terr_rec.attribute11,
1800 l_terr_rec.attribute12,
1801 l_terr_rec.attribute13,
1802 l_terr_rec.attribute14,
1803 l_terr_rec.attribute15;
1804 CLOSE get_imm_children;
1805
1806 --dbms_output.put_line('before passing to process' || l_terr_rec_tbl.count);
1807 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);
1808 COMMIT;
1809 WHEN 'UNASS' THEN
1810 -- process unassign geography
1811 dl_unassign_geography(p_org_id => p_org_id, p_usage_id => p_usage_id, p_user_id => p_user_id,
1812 p_user_sequence => l_seq, p_interface_type => l_intf_type, p_terr_id => p_terr_id,
1813 p_geo_type => p_geo_type, x_retcode => x_retcode, x_errbuf => x_errbuf);
1814 COMMIT;
1815 WHEN 'BOTH' THEN
1816
1817 OPEN get_imm_children(p_terr_id, p_org_id, p_active, l_parent_terr_hierarchy);
1818 FETCH get_imm_children bulk collect
1819 INTO l_terr_rec.terr_id,
1820 l_terr_rec.terr_name,
1821 l_terr_rec.rank,
1822 l_terr_rec.num_winners,
1823 l_terr_rec.start_date,
1824 l_terr_rec.end_date,
1825 l_terr_rec.terr_type_name,
1826 l_terr_rec.terr_type_id,
1827 l_terr_rec.parent_terr_id,
1828 l_terr_rec.org_name,
1829 l_terr_rec.hierarchy,
1830 l_terr_rec.attribute_category,
1831 l_terr_rec.attribute1,
1832 l_terr_rec.attribute2,
1833 l_terr_rec.attribute3,
1834 l_terr_rec.attribute4,
1835 l_terr_rec.attribute5,
1836 l_terr_rec.attribute6,
1837 l_terr_rec.attribute7,
1838 l_terr_rec.attribute8,
1839 l_terr_rec.attribute9,
1840 l_terr_rec.attribute10,
1841 l_terr_rec.attribute11,
1842 l_terr_rec.attribute12,
1843 l_terr_rec.attribute13,
1844 l_terr_rec.attribute14,
1845 l_terr_rec.attribute15;
1846 CLOSE get_imm_children;
1847
1848 -- process assigned territories
1849 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);
1850
1851 -- process unassign geography
1852 dl_unassign_geography(p_org_id => p_org_id, p_usage_id => p_usage_id, p_user_id => p_user_id,
1853 p_user_sequence => l_seq, p_interface_type => l_intf_type, p_terr_id => p_terr_id,
1854 p_geo_type => p_geo_type, x_retcode => x_retcode, x_errbuf => x_errbuf);
1855 COMMIT;
1856 ELSE
1857 NULL;
1858 END
1859 CASE;
1860 --l_view
1861 WHEN 'ALL' THEN
1862
1863 OPEN get_all_terr(p_terr_id, p_org_id, p_active, l_parent_terr_hierarchy);
1864 FETCH get_all_terr bulk collect
1865 INTO l_terr_rec.terr_id,
1866 l_terr_rec.terr_name,
1867 l_terr_rec.rank,
1868 l_terr_rec.num_winners,
1869 l_terr_rec.start_date,
1870 l_terr_rec.end_date,
1871 l_terr_rec.terr_type_name,
1872 l_terr_rec.terr_type_id,
1873 l_terr_rec.parent_terr_id,
1874 l_terr_rec.org_name,
1875 l_terr_rec.hierarchy,
1876 l_terr_rec.attribute_category,
1877 l_terr_rec.attribute1,
1878 l_terr_rec.attribute2,
1879 l_terr_rec.attribute3,
1880 l_terr_rec.attribute4,
1881 l_terr_rec.attribute5,
1882 l_terr_rec.attribute6,
1883 l_terr_rec.attribute7,
1884 l_terr_rec.attribute8,
1885 l_terr_rec.attribute9,
1886 l_terr_rec.attribute10,
1887 l_terr_rec.attribute11,
1888 l_terr_rec.attribute12,
1889 l_terr_rec.attribute13,
1890 l_terr_rec.attribute14,
1891 l_terr_rec.attribute15;
1892 CLOSE get_all_terr;
1893
1894 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);
1895 COMMIT;
1896 WHEN 'SEARCH' THEN
1897 --p( l_cur_query );
1898
1899 IF p_terr_id_array IS NOT NULL THEN
1900 BEGIN
1901 --EXECUTE IMMEDIATE ('TRUNCATE TABLE JTY_STR_TO_TABLE_GT');
1902
1903 DELETE FROM jty_str_to_table_gt;
1904 l_string := p_terr_id_array || ',';
1905 LOOP
1906 EXIT
1907 WHEN l_string IS NULL;
1908 l_cnt := instr(l_string, ',');
1909 l_value := SUBSTR(l_string, 1, l_cnt -1);
1910
1911 IF l_value IS NOT NULL THEN
1912 INSERT
1913 INTO jty_str_to_table_gt(num_col)
1914 VALUES(l_value);
1915 END IF;
1916
1917 l_string := SUBSTR(l_string, l_cnt + 1);
1918
1919 END LOOP;
1920 END;
1921
1922 OPEN get_search_csr(p_org_id, p_active);
1923 FETCH get_search_csr bulk collect
1924 INTO l_terr_rec.terr_id,
1925 l_terr_rec.terr_name,
1926 l_terr_rec.rank,
1927 l_terr_rec.num_winners,
1928 l_terr_rec.start_date,
1929 l_terr_rec.end_date,
1930 l_terr_rec.terr_type_name,
1931 l_terr_rec.terr_type_id,
1932 l_terr_rec.parent_terr_id,
1933 l_terr_rec.org_name,
1934 l_terr_rec.hierarchy,
1935 l_terr_rec.attribute_category,
1936 l_terr_rec.attribute1,
1937 l_terr_rec.attribute2,
1938 l_terr_rec.attribute3,
1939 l_terr_rec.attribute4,
1940 l_terr_rec.attribute5,
1941 l_terr_rec.attribute6,
1942 l_terr_rec.attribute7,
1943 l_terr_rec.attribute8,
1944 l_terr_rec.attribute9,
1945 l_terr_rec.attribute10,
1946 l_terr_rec.attribute11,
1947 l_terr_rec.attribute12,
1948 l_terr_rec.attribute13,
1949 l_terr_rec.attribute14,
1950 l_terr_rec.attribute15;
1951 CLOSE get_search_csr;
1952
1953 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);
1954 END IF;
1955
1956 ELSE
1957 NULL;
1958 END
1959 CASE;
1960 -- l_mode
1961
1962 END IF;
1963
1964 COMMIT;
1965
1966 x_seq := l_seq;
1967
1968 END populate_webadi_interface;
1969
1970 END jty_webadi_oth_terr_dwnl_pkg;
1971