[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_WEBADI_NADOC_PKG
Source
1 PACKAGE BODY Jtf_Tty_Webadi_Nadoc_Pkg AS
2 /* $Header: jtfintfb.pls 120.6.12000000.2 2007/06/27 08:32:56 sseshaiy ship $ */
3 -- ===========================================================================+
4 -- | Copyright (c) 2003 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +===========================================================================
8 -- Start of Comments
9 -- ---------------------------------------------------
10 -- PURPOSE
11 --
12 -- This package is used to return a list of column in order of selectivity.
13 -- And create indices on columns in order of input
14 --
15 --
16 -- Procedures:
17 -- (see below for specification)
18 --
19 -- This package is publicly available for use
20 --
21 -- HISTORY
22 -- 05/02/2002 SHLI Created
23 -- 14/03/2004 SP Modified for bug 3334142.
24 --
25 -- End of Comments
26 --
27 -- *******************************************************
28 -- Start of Comments
29 -- *******************************************************
30
31 PROCEDURE POPULATE_INTERFACE( P_CALLFROM IN VARCHAR2,
32 P_SEARCHTYPE IN VARCHAR2,
33 P_SEARCHVALUE IN VARCHAR2,
34 P_USERID IN INTEGER,
35 P_GRPID IN NUMBER,
36 P_GRPNAME IN VARCHAR2,
37 P_SITE_TYPE IN VARCHAR2,
38 P_SICCODE IN VARCHAR2,
39 P_SITE_DUNS IN VARCHAR2,
40 P_NAMED_ACCOUNT IN VARCHAR2,
44 P_POSTAL_CODE_FROM IN VARCHAR2,
41 P_CITY IN VARCHAR2,
42 P_STATE IN VARCHAR2,
43 P_PROVINCE IN VARCHAR2,
45 P_POSTAL_CODE_TO IN VARCHAR2,
46 P_COUNTRY IN VARCHAR2,
47 P_DU_DUNS IN VARCHAR2,
48 P_DU_NAME IN VARCHAR2,
49 P_GU_DUNS IN VARCHAR2,
50 P_GU_NAME IN VARCHAR2,
51 P_SALESPERSON IN NUMBER,
52 P_SALES_GROUP IN NUMBER,
53 P_SALES_ROLE IN VARCHAR2,
54 P_ASSIGNED_STATUS IN VARCHAR2,
55 P_IDENTADDRFLAG IN VARCHAR2,
56 P_ISADMINFLAG IN VARCHAR2,
57 X_SEQ OUT NOCOPY VARCHAR2) IS
58
59 PARAM_ARRAY VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
60
61 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
62
63 /*
64 RESOURCE_NAME VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
65
66 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
67 GROUP_NAME VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
68
69 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
70 ROLE_NAME VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
71
72 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
73 COL_ROLE VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
74
75 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
76 RESOURCE_ID NARRAY_TYPE:=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
77 L_GROUP_ID NARRAY_TYPE:=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
78 ROLE_CODE VARRAY_TYPE:=VARRAY_TYPE(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
79
80 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
81 COL_RSC NARRAY_TYPE:=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
82 COL_USED NARRAY_TYPE:=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
83 */
84
85 RESOURCE_NAME VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
86 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
87 GROUP_NAME VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
88 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
89 ROLE_NAME VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
90 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
91 COL_ROLE VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
92 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
93 COL_USED NARRAY_TYPE:=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
94
95 salesMgr NUMBER;
96 SEQ NUMBER;
97 ID NUMBER;
98 NAMED_ACCOUNT VARCHAR2(360);
99 L_PARTY_ID VARCHAR2(30);
100 SITE_TYPE VARCHAR2(80);
101 DUNS VARCHAR2(30);
102 TRADE_NAME VARCHAR2(240);
103 GU_DUNS VARCHAR2(360);
104 GU_NAME VARCHAR2(360);
105 --DOME_ULTIMATE_DUNS VARCHAR2(360);
106 --DOME_ULTIMATE_NAME VARCHAR2(360);
107 CITY VARCHAR2(60);
108 STATE VARCHAR2(60);
109 POSTAL_CODE VARCHAR2(60);
110 TERRITORY_GROUP VARCHAR2(150);
111 NA_ID NUMBER;
112 TERR_GRP_ACCT_ID NUMBER;
113 CREATED_BY NUMBER(15);
114 CREATION_DATE DATE;
115 LAST_UPDATED_BY NUMBER(15);
116 LAST_UPDATE_DATE DATE;
117 LAST_UPDATE_LOGIN NUMBER(15);
118 l_na_rsc_stat VARCHAR2(6000);
119 l_na_query VARCHAR2(6000);
120 l_var1 VARCHAR2(200);
121 l_var2 VARCHAR2(200);
122 curr_date VARCHAR2(100);
123 i NUMBER;
124 j NUMBER;
125 k NUMBER;
126 numParam NUMBER;
127 m NUMBER;
128 num NUMBER;
129 nastat INTEGER;
130 na INTEGER;
131 ignore INTEGER;
132 namedacct INTEGER;
133 useExistsClause VARCHAR2(10);
134 l_identAddrFlag VARCHAR2(5);
135
136 P_NAMED_ACCOUNT_STR VARCHAR2(361);
137 P_CITY_STR VARCHAR2(61);
138 P_POSTAL_CODE_FROM_STR VARCHAR2(61);
139 P_POSTAL_CODE_TO_STR VARCHAR2(61);
140 P_DU_NAME_STR VARCHAR2(361);
141 P_GU_NAME_STR VARCHAR2(361);
142 l_sql VARCHAR2(5000);
143
144 TYPE csr_type IS REF CURSOR;
148 num number);
145 l_get_statistic_csr csr_type;
146 TYPE get_stat_rec_type IS RECORD
147 (role_code varchar(30),
149 TYPE get_stat_tbl_type is table of get_stat_rec_type index by binary_integer;
150
151 l_get_stat_tbl get_stat_tbl_type;
152
153 CURSOR getSalesperson( P_NAID IN NUMBER ) IS
154 SELECT rsc.resource_name resource_name
155 , rol.role_name role_name
156 , grp.group_name group_name
157 , rsc.resource_id resource_id
158 , grp.group_id group_id
159 , rol.role_code role_code
160 FROM jtf_rs_resource_extns_vl rsc
161 , jtf_rs_groups_vl grp
162 , jtf_rs_roles_vl rol
163 , jtf_tty_named_acct_rsc narsc
164 , jtf_tty_terr_grp_accts ga
165 -- , jtf_tty_named_accts na
166 WHERE rsc.resource_id = narsc.resource_id
167 AND grp.group_id = narsc.rsc_group_id
168 AND rol.role_code = narsc.rsc_role_code
169 AND narsc.terr_group_account_id = ga.terr_group_account_id
170 AND narsc.rsc_resource_type = 'RS_EMPLOYEE'
171 -- AND ga.named_account_id = na.named_account_id
172 AND ga.named_account_id = P_NAID;
173 -- ??? ORDER BY UPPER(rol.role_name), rsc.resource_name;
174
175
176 CURSOR getNAFromInterface IS
177 SELECT jtf_tty_webadi_int_id, terr_grp_acct_id
178 FROM jtf_tty_webadi_interface--JTF_TTY_WEBADI_INT_GT --
179 WHERE user_id=p_userid;
180
181
182 BEGIN
183 ---delete from tmp;
184 ---insert into tmp values ( to_char(sysdate,'HH,MI:SS'), '1. start'); commit;
185
186 P_NAMED_ACCOUNT_STR := NULL;
187 P_CITY_STR := NULL;
188 P_DU_NAME_STR := NULL;
189 P_GU_NAME_STR := NULL;
190
191 BEGIN
192
193
194
195
196 --l_t1 := TO_CHAR(SYSDATE, 'mm/dd/yyyy hh24:mi:ss');
197 --dbms_output.put_line('PREVIOUS DOWNLOAD CLEANUP = '|| l_t1 );
198
199 SELECT jtf_tty_interface_s.NEXTVAL INTO SEQ
200 FROM dual;
201
202 --delete from JTF_TTY_WEBADI_INT_GT ;
203 -- remove existing old data for this userid
204 DELETE /*+ INDEX(jtf_tty_webadi_intf_n1) */
205 FROM JTF_TTY_WEBADI_INTERFACE tty
206 WHERE tty.user_id = TO_NUMBER(p_userid);
207 -- AND tty.user_sequence <> seq;
208
209 --dbms_output.put_line('CLEANUP ROW COUNT = '|| TO_CHAR(SQL%ROWCOUNT) );
210
211 -- JDOCHERT: 11/01/03
212 -- Very important commit as it will
213 -- prevent locking of JTF_TTY_WEBADI_INTERFACE
214 -- table should the download fail
215 --
216 COMMIT;
217
218 EXCEPTION
219 WHEN OTHERS THEN
220 NULL;
221
222 END;
223
224 -- and sysdate - creation_date >2;
225 --select count(*) into id from JTF_TTY_WEBADI_INTERFACE;
226 --if id=0 then id:=1;
227 --else select max(id)+1 into id from JTF_TTY_WEBADI_INTERFACE;
228 --end if;
229
230 BEGIN
231
232 SELECT resource_id INTO salesMgr FROM jtf_rs_resource_extns
233 WHERE user_id = TO_NUMBER(p_userid);
234
235 EXCEPTION
236 WHEN NO_DATA_FOUND THEN
237 x_seq := '-100';
238 RETURN;
239 END;
240
241 /* search by
242
243 2 Unmapped Named Account
244 3 Named Account
245 4 Site Type
246 5 Salesperson
247 6 City
248 7 State
249 8 Postal Code
250 9 Country
251 91 Territory Group
252 */
253 --l_t1 := TO_CHAR(SYSDATE, 'mm/dd/yyyy hh24:mi:ss');
254 --dbms_output.put_line('QUERY BUILD = '|| l_t1 );
255 curr_date := TO_CHAR(SYSDATE);
256
257 IF p_callfrom = 'S' /* simple search */ THEN
258
259 --DBMS_OUTPUT.PUT_LINE ('p_userid: '||P_USERID ||', SEQ: ' ||SEQ||', CURR_DATE: '||CURR_DATE);
260 l_na_query :=
261 'INSERT into JTF_TTY_WEBADI_INTERFACE ' || --JTF_TTY_WEBADI_INT_GT
262 ' ( USER_SEQUENCE,USER_ID,TERR_GRP_ACCT_ID,JTF_TTY_WEBADI_INT_ID,NAMED_ACCOUNT,SITE_TYPE,TRADE_NAME,DUNS, '||
263 ' GU_DUNS,GU_NAME,CITY,STATE,POSTAL_CODE,TERRITORY_GROUP, ' ||
264 ' CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE ' ||
265 ' ) ' ||
266 ' SELECT ' ||
267 ' :SEQ USER_SEQUENCE,'||
268 ' :P_USERID USER_ID,'|| ' GAID, NAID, NAMED_ACCOUNT,SITE_TYPE,TRADE_NAME, SITE_DUNS, '||
269 ' GU_DUNS,GU_NAME,CITY,STATE,POSTAL_CODE, GRPNAME, ' ||
270 ' :P_USERID CREATED_BY, :curr_date CREATION_DATE,' ||
271 ' :P_USERID LAST_UPDATED_BY, :curr_date LAST_UPDATE_DATE '||
272 ' FROM (';
273
274 /* main select */
275 IF P_SEARCHTYPE = '91' --Territory Group
276 AND P_SEARCHVALUE IS NOT NULL AND trim(P_SEARCHVALUE) IS NOT NULL AND SUBSTR(trim(P_SEARCHVALUE),1,1)<> '%' THEN
277
278 l_var1 := UPPER(trim(P_SEARCHVALUE)) || '%';
279 l_na_query :=l_na_query ||
280 ' select /*+ ORDERED */ ' ||
281 ' hzp.party_name named_account, ' ||
282 ' lkp.meaning site_type, ' ||
283 ' hzp.known_as trade_name, ' ||
284 ' hzp.duns_number_c site_duns, ' ||
285 ' GU.GU_NAME gu_name, ' ||
289 ' hzl.postal_code postal_code, ' ||
286 ' GU.GU_DUNS gu_duns, ' ||
287 ' hzl.city city, ' ||
288 ' hzl.state state, ' ||
290 ' ttygrp.terr_group_name grpname, ' ||
291 ' ga.terr_group_account_id gaid, '||
292 ' ga.terr_group_id tgid, ' ||
293 ' na.named_account_id naid ' ||
294 ' from jtf_tty_terr_grp_accts ga ' ||
295 ' ,jtf_tty_terr_groups ttygrp ' ||
296 ' ,jtf_tty_named_accts na ' ||
297 ' ,fnd_lookups lkp ' ||
298 ' ,hz_parties hzp ' ||
299 ' ,hz_party_sites hzps ' ||
300 ' ,hz_locations hzl ' ||
301 ' , ( /* Global Ultimate */ ' ||
302 ' SELECT min(gup.party_name) GU_NAME ' ||
303 ' , min(gup.duns_number_c) GU_DUNS ' ||
304 ' , hzr.object_id GU_OBJECT_ID ' ||
305 ' FROM hz_parties gup ' ||
306 ' , hz_relationships hzr ' ||
307 ' WHERE hzr.subject_table_name = ''HZ_PARTIES'' ' ||
308 ' AND hzr.object_table_name = ''HZ_PARTIES'' ' ||
309 ' AND hzr.relationship_type = ''GLOBAL_ULTIMATE'' ' ||
310 ' AND hzr.relationship_code = ''GLOBAL_ULTIMATE_OF'' ' ||
311 ' AND hzr.status = ''A'' ' ||
312 ' AND hzr.subject_id = gup.party_id ' ||
313 ' AND gup.status = ''A'' ' ||
314 ' group by hzr.object_id ) GU ' ||
315 ' where ' ||
316 ' ga.terr_group_account_id IN ' ||
317 ' ( ' ||
318 ' select /*+ NO_MERGE */ narsc.terr_group_account_id ' ||
319 ' from jtf_tty_named_acct_rsc narsc, ' ||
320 ' jtf_tty_srch_my_resources_v repdn ' ||
321 ' where current_user_id = :P_USERID ' ||
322 ' and narsc.rsc_group_id = repdn.group_id ' ||
323 ' and narsc.resource_id = repdn.resource_id ' ||
324 ' ) ' ||
325 ' and ttygrp.terr_group_id = ga.terr_group_id ' ||
326 ' and ttygrp.active_from_date <= sysdate ' ||
327 ' and ( ttygrp.active_to_date is null or ' ||
328 ' ttygrp.active_to_date >= sysdate) ' ||
329 ' and upper(ttygrp.terr_group_name) like :P_SEARCHSTR ' ||
330 ' and na.named_account_id = ga.named_account_id ' ||
331 ' AND na.site_type_code = lkp.lookup_code ' ||
332 ' and lkp.lookup_type = ''JTF_TTY_SITE_TYPE_CODE'' ' ||
333 ' and hzp.party_id = na.party_id ' ||
334 ' and hzps.party_id = na.party_id ' ||
335 ' and hzps.party_site_id = na.party_site_id ' ||
336 ' and hzps.location_id = hzl.location_id ' ||
337 ' AND GU.GU_OBJECT_ID (+) = hzp.party_id ' ||
338 ' ) '; -- done
339
340 m :=2; -- 2 bind variable
341
342 ELSE /* search by null or search by other(than TG) */
343 l_na_query := l_na_query ||
344 ' select * from ( ' ||
345 ' select hzp.party_name named_account, ' ||
346 ' lkp.meaning site_type, ' ||
347 ' hzp.known_as trade_name, ' ||
348 ' hzp.duns_number_c site_duns, ' ||
349 ' GU.GU_NAME gu_name, ' ||
350 ' GU.GU_DUNS gu_duns, ' ||
351 ' hzl.city city, ' ||
352 ' hzl.state state, ' ||
353 ' hzl.postal_code postal_code, ' ||
354 ' hzl.country country, ' ||
355 ' ttygrp.terr_group_name grpname, ' ||
356 ' ga.terr_group_account_id gaid, ' ||
357 ' ga.terr_group_id tgid, ' ||
358 ' lkp.lookup_code sitetypecode, ' ||
359 ' hzp.province, ' ||
360 ' na.named_account_id naid ' ||
361 ' from hz_parties hzp, ' ||
362 ' hz_party_sites hzps, ' ||
363 ' hz_locations hzl, ' ||
364 ' jtf_tty_named_accts na, ' ||
365 ' jtf_tty_terr_grp_accts ga, ' ||
366 ' fnd_lookups lkp, ' ||
367 ' jtf_tty_terr_groups ttygrp ' ||
368 ' , ( /* Global Ultimate */ ' ||
369 ' SELECT min(gup.party_name) GU_NAME ' ||
370 ' , min(gup.duns_number_c) GU_DUNS ' ||
371 ' , hzr.object_id GU_OBJECT_ID ' ||
372 ' FROM hz_parties gup ' ||
373 ' , hz_relationships hzr ' ||
374 ' WHERE hzr.subject_table_name = ''HZ_PARTIES'' ' ||
375 ' AND hzr.object_table_name = ''HZ_PARTIES'' ' ||
376 ' AND hzr.relationship_type = ''GLOBAL_ULTIMATE'' ' ||
377 ' AND hzr.relationship_code = ''GLOBAL_ULTIMATE_OF'' ' ||
378 ' AND hzr.status = ''A'' ' ||
379 ' AND hzr.subject_id = gup.party_id ' ||
380 ' AND gup.status = ''A'' ' ||
381 ' group by hzr.object_id ) GU ' ||
382 ' where hzp.party_id = na.party_id ' ||
383 ' and hzps.party_id = na.party_id ' ||
384 ' and hzps.party_site_id = na.party_site_id ' ||
385 ' and hzps.location_id = hzl.location_id ' ||
386 ' and na.site_type_code = lkp.lookup_code ' ||
387 ' and lkp.lookup_type = ''JTF_TTY_SITE_TYPE_CODE'' ' ||
388 ' and na.named_account_id = ga.named_account_id ' ||
389 ' and ttygrp.terr_group_id = ga.terr_group_id ' ||
390 ' and ttygrp.active_from_date <= sysdate ' ||
391 ' and ( ttygrp.active_to_date is null ' ||
392 ' or ' ||
393 ' ttygrp.active_to_date >= sysdate ' ||
394 ' ) ' ||
395 ' AND GU.GU_OBJECT_ID (+) = hzp.party_id ' ||
396 ' ) '; -- not done
397
398
399 IF P_SEARCHVALUE IS NULL OR trim(P_SEARCHVALUE) IS NULL OR SUBSTR(trim(P_SEARCHVALUE),1,1) = '%' THEN
400
401 l_na_query := l_na_query ||
402 ' where gaid IN ( select /*+ NO_MERGE */ narsc.terr_group_account_id ' ||
403 ' from jtf_tty_named_acct_rsc narsc, '||
404 ' jtf_tty_srch_my_resources_v repdn '||
405 ' where narsc.resource_id = repdn.resource_id '||
406 ' and narsc.rsc_group_id = repdn.group_id '||
407 ' and repdn.current_user_id = :P_USERID ) ' ||
408 ' )';
409 m:=1; -- one bind variable so far
410
411 ELSE
412 l_na_query := l_na_query ||
413 ' where EXISTS ( select narsc.terr_group_account_id '||
414 ' from jtf_tty_named_acct_rsc narsc, '||
415 ' jtf_tty_srch_my_resources_v repdn '||
416 ' where narsc.resource_id = repdn.resource_id '||
417 ' and narsc.rsc_group_id = repdn.group_id '||
418 ' and narsc.terr_group_account_id = gaid '||
419 ' and repdn.current_user_id = :P_USERID ) ' ||
420 ' and ';
421
422 IF P_SEARCHTYPE = '8' THEN l_var1 := trim(P_SEARCHVALUE) || '%'; -- no index
423 ELSE l_var1 := UPPER(trim(P_SEARCHVALUE)) || '%';
424 END IF;
425
426 IF P_SEARCHTYPE = '1' THEN NULL;
427 ELSIF P_SEARCHTYPE = '3' --named account
428 THEN l_na_query := l_na_query || ' upper(named_account) like :P_SEARCHSTR';
429 ELSIF P_SEARCHTYPE = '4' --site type
430 THEN l_na_query := l_na_query || ' upper(site_type) like :P_SEARCHSTR';
431 ELSIF P_SEARCHTYPE = '6' --city
432 THEN l_na_query := l_na_query || ' upper(city) like :P_SEARCHSTR';
433 ELSIF P_SEARCHTYPE = '7' --state
434 THEN l_na_query := l_na_query || ' upper(state) like :P_SEARCHSTR';
435 ELSIF P_SEARCHTYPE = '8' --postal_code
436 -- then l_na_query := l_na_query || ' upper(postal_code) like :P_SEARCHSTR';
437 -- no index
438 THEN l_na_query := l_na_query || ' postal_code like :P_SEARCHSTR';
439 END IF;
440 l_na_query := l_na_query || ')'; -- close
441 m:=2; -- 2 bind variable
442 END IF; -- P_SEARCHVALUE is null or ...
443
444 END IF; -- for P_SEARCHTYPE = '91'
445
446
447 ELSE /* advanced search */
448
449 l_na_query:='DECLARE ' ||
450 ' P_USERID INTEGER := :P_USERID; '||
451 ' P_GRPID NUMBER := :P_GRPID ; '||
452 ' P_SITE_TYPE VARCHAR2(100) := :P_SITE_TYPE; '||
453 ' P_SICCODE VARCHAR2(100) := :P_SICCODE; '||
454 ' P_SITE_DUNS VARCHAR2(100) := :P_SITE_DUNS; '||
455 ' P_NAMED_ACCOUNT VARCHAR2(360) := :P_NAMED_ACCOUNT; '||
456 ' P_CITY VARCHAR2(100) := :P_CITY; '||
457 ' P_STATE VARCHAR2(100) := :P_STATE; '||
458 ' P_PROVINCE VARCHAR2(100) := :P_PROVINCE; '||
459 ' P_POSTAL_CODE_FROM VARCHAR2(100) := :P_POSTAL_CODE_FROM; '||
460 ' P_POSTAL_CODE_TO VARCHAR2(100) := :P_POSTAL_CODE_TO; '||
461 ' P_COUNTRY VARCHAR2(100) := :P_COUNTRY; '||
462 ' P_DU_DUNS VARCHAR2(100) := :P_DU_DUNS; '||
463 ' P_DU_NAME VARCHAR2(360) := :P_DU_NAME; '||
464 ' P_GU_DUNS VARCHAR2(100) := :P_GU_DUNS; '||
465 ' P_GU_NAME VARCHAR2(360) := :P_GU_NAME; '||
466 ' P_SALESPERSON NUMBER := :P_SALESPERSON; '||
467 ' P_SALES_GROUP NUMBER := :P_SALES_GROUP; '||
468 ' P_SALES_ROLE VARCHAR2(100) := :P_SALES_ROLE; '||
469 ' P_ASSIGNED_STATUS VARCHAR2(100) := :P_ASSIGNED_STATUS; ' ||
470 ' L_SEQ NUMBER := :SEQ; ' ||
471 ' L_CURR_DATE DATE := :CURR_DATE; '||
472
473 ' BEGIN '||
474
475 ' INSERT into JTF_TTY_WEBADI_INTERFACE ' || --JTF_TTY_WEBADI_INTERFACE '||
476 ' ( USER_SEQUENCE,USER_ID,TERR_GRP_ACCT_ID,JTF_TTY_WEBADI_INT_ID,NAMED_ACCOUNT,SITE_TYPE,TRADE_NAME,DUNS, '||
477 ' GU_DUNS,GU_NAME,CITY,STATE,POSTAL_CODE,TERRITORY_GROUP, ' ||
478 ' CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE ' ||
479 ' ) ' ||
480 ' SELECT ' ||
481 ' L_SEQ USER_SEQUENCE,'||
482 ' P_USERID USER_ID,'|| ' GAID, NAID, NAMED_ACCOUNT,SITE_TYPE,TRADE_NAME, SITE_DUNS, '||
483 ' GU_DUNS,GU_NAME,CITY,STATE,POSTAL_CODE, GRPNAME, ' ||
484 ' P_USERID CREATED_BY, L_CURR_DATE CREATION_DATE,' ||
485 ' P_USERID LAST_UPDATED_BY, L_CURR_DATE LAST_UPDATE_DATE '||
486 ' FROM ( ' ||
487 ' select '||
488 ' ga.terr_group_id tgid, ' ||
489 ' na.named_account_id naid, ' ||
490 ' hzp.party_name named_account, ' ||
491 ' hzp.party_id party_id,'||
492 ' lkp.meaning site_type, ' ||
493 ' hzp.known_as trade_name, ' ||
494 ' hzp.duns_number_c site_duns, ' ||
495 ' GU.GU_DUNS gu_duns, ' ||
496 ' GU.GU_NAME gu_name, ' ||
497 ' hzl.city city, ' ||
498 ' hzl.state state, ' ||
499 ' hzl.postal_code postal_code, ' ||
500 ' hzl.country country, ' ||
501 ' ttygrp.terr_group_name grpname, ' ||
502 ' ga.terr_group_account_id gaid,' ||
503 ' lkp.lookup_code sitetypecode, '||
504 ' hzp.sic_code siccode, ' ||
505 ' hzp.province privince, '||
506 ' decode(hzps.identifying_address_flag, ''Y'', ''Y'', ''N'' ) identifying_addr_flag ' ||
507 ' from hz_parties hzp, ' ||
508 ' hz_locations hzl, ' ||
509 ' hz_party_sites hzps, ' ||
510 ' jtf_tty_named_accts na, ' ||
511 ' jtf_tty_terr_grp_accts ga, ' ||
512 ' fnd_lookups lkp, ' ||
513 ' jtf_tty_terr_groups ttygrp ' ||
514 ' , ( /* Global Ultimate */ ' ||
515 ' SELECT min(gup.party_name) GU_NAME ' ||
516 ' , min(gup.duns_number_c) GU_DUNS ' ||
517 ' , hzr.object_id GU_OBJECT_ID ' ||
518 ' FROM hz_parties gup ' ||
519 ' , hz_relationships hzr ' ||
520 ' WHERE hzr.subject_table_name = ''HZ_PARTIES'' ' ||
521 ' AND hzr.object_table_name = ''HZ_PARTIES'' ' ||
522 ' AND hzr.relationship_type = ''GLOBAL_ULTIMATE'' ' ||
523 ' AND hzr.relationship_code = ''GLOBAL_ULTIMATE_OF'' ' ||
524 ' AND hzr.status = ''A'' ' ||
525 ' AND hzr.subject_id = gup.party_id ' ||
526 ' AND gup.status = ''A'' ' ||
527 ' group by hzr.object_id ) GU ' ||
528 ' where hzp.party_id = na.party_id ' ||
529 ' AND hzps.party_site_id = na.party_site_id ' ||
530 ' AND hzp.party_id = hzps.party_id ' ||
531 ' AND hzps.party_id = na.party_id ' ||
532 ' AND hzps.location_id = hzl.location_id ' ||
533 ' and na.site_type_code = lkp.lookup_code ' ||
534 ' and lkp.lookup_type = ''JTF_TTY_SITE_TYPE_CODE'' ' ||
535 ' and na.named_account_id = ga.named_account_id ' ||
536 ' AND ttygrp.terr_group_id = ga.terr_group_id ' ||
537 ' AND GU.GU_OBJECT_ID (+) = hzp.party_id ' ;
538 /*******************************/
539
540
541 useExistsClause := 'N' ;
542
543 IF P_GRPID IS NOT NULL AND trim(P_GRPID) IS NOT NULL THEN
544 -- if (terrGrpID!=null !"".equals(terrGrpID.trim()))
545 l_na_query := l_na_query || ' and ga.terr_group_id = :P_GRPID ';
546 END IF;
547
548 IF P_CITY IS NOT NULL AND trim(P_CITY) IS NOT NULL THEN
549 -- if (city!=null !"".equals(city.trim()))
550 l_na_query := l_na_query || 'AND upper(hzl.city) like :P_CITY ';
551 useExistsClause := 'Y';
552 P_CITY_STR := UPPER(P_CITY) || '%';
553 END IF;
554
555 IF P_STATE IS NOT NULL AND trim(P_STATE) IS NOT NULL THEN
556 -- if (state!=null !"".equals(state.trim()))
557 l_na_query := l_na_query || 'AND hzl.state = :P_STATE ';
558 END IF;
559
560 IF P_COUNTRY IS NOT NULL AND trim(P_COUNTRY) IS NOT NULL THEN
561 -- if (country!=null !"".equals(country.trim()))
562 l_na_query := l_na_query || 'AND hzl.country = :P_COUNTRY ';
563 END IF;
564
565 IF P_POSTAL_CODE_FROM IS NOT NULL AND trim(P_POSTAL_CODE_FROM) IS NOT NULL
566 AND P_POSTAL_CODE_TO IS NOT NULL AND trim(P_POSTAL_CODE_TO) IS NOT NULL
567 AND P_POSTAL_CODE_FROM = P_POSTAL_CODE_TO THEN
568 --if (postCodeFrom!=null !"".equals(postCodeFrom.trim())
569 -- postCodeTo!=null !"".equals(postCodeTo.trim())
570 -- postCodeFrom.equals(postCodeTo) )
571 l_na_query := l_na_query || 'AND hzl.postal_code = :P_POSTAL_CODE_FROM ';
572 useExistsClause := 'Y' ;
573 ELSIF P_POSTAL_CODE_FROM IS NOT NULL AND trim(P_POSTAL_CODE_FROM) IS NOT NULL
574 AND P_POSTAL_CODE_TO IS NOT NULL AND trim(P_POSTAL_CODE_TO) IS NOT NULL
575 AND P_POSTAL_CODE_FROM <> P_POSTAL_CODE_TO THEN
576 -- (postCodeFrom!=null !"".equals(postCodeFrom.trim())
577 -- postCodeTo!=null !"".equals(postCodeTo.trim())
578 -- !postCodeFrom.equals(postCodeTo))
579 l_na_query := l_na_query || 'AND hzl.postal_code between :P_POSTAL_CODE_FROM and :P_POSTAL_CODE_TO ';
580 useExistsClause := 'N' ;
581 END IF;
582
583 IF P_POSTAL_CODE_FROM IS NOT NULL AND trim(P_POSTAL_CODE_FROM) IS NOT NULL
584 AND (P_POSTAL_CODE_TO IS NULL OR trim(P_POSTAL_CODE_TO) IS NULL) THEN
585 --if (postCodeFrom!=null !"".equals(postCodeFrom.trim())
586 -- (postCodeTo==null || "".equals(postCodeTo.trim())) )
587 l_na_query := l_na_query || 'AND hzl.postal_code = :P_POSTAL_CODE_FROM ';
588 useExistsClause := 'Y';
589 END IF;
590
591 IF (P_POSTAL_CODE_FROM IS NULL OR trim(P_POSTAL_CODE_FROM) IS NULL)
592 AND P_POSTAL_CODE_TO IS NOT NULL AND trim(P_POSTAL_CODE_TO) IS NOT NULL THEN
593 --if ( (postCodeFrom==null || "".equals(postCodeFrom.trim()))
594 -- postCodeTo!=null !"".equals(postCodeTo.trim()) )
598
595 l_na_query := l_na_query || 'AND hzl.postal_code = :P_POSTAL_CODE_TO ' ;
596 useExistsClause := 'Y' ;
597 END IF;
599 IF P_SITE_TYPE IS NOT NULL AND trim(P_SITE_TYPE) IS NOT NULL AND P_SITE_TYPE <> 'ALL' THEN
600 --if (siteType!=null !"".equals(siteType.trim()) !"ALL".equals(siteType))
601 l_na_query := l_na_query || 'AND lkp.lookup_code = :P_SITE_TYPE ';
602 END IF;
603
604 IF P_SICCODE IS NOT NULL AND trim(P_SICCODE) IS NOT NULL THEN
605 -- if (SICCode!=null !"".equals(SICCode.trim()) )
606 l_na_query := l_na_query || 'AND hzp.sic_code = :P_SICCODE ' ;
607 useExistsClause := 'Y' ;
608 END IF;
609
610 IF P_SITE_DUNS IS NOT NULL AND trim(P_SITE_DUNS) IS NOT NULL THEN
611 -- if (siteDUNS!=null !"".equals(siteDUNS.trim()) )
612 l_na_query := l_na_query || 'AND hzp.duns_number_c = :P_SITE_DUNS ' ;
613 useExistsClause := 'Y';
614 END IF;
615
616 IF P_NAMED_ACCOUNT IS NOT NULL AND trim(P_NAMED_ACCOUNT) IS NOT NULL THEN
617 --if (siteBN!=null !"".equals(siteBN.trim()) )
618 l_na_query := l_na_query || 'AND upper(hzp.party_name) like :P_NAMED_ACCOUNT ' ;
619 useExistsClause := 'Y';
620 P_NAMED_ACCOUNT_STR := UPPER(P_NAMED_ACCOUNT) || '%';
621 END IF;
622
623 IF P_PROVINCE IS NOT NULL AND trim(P_PROVINCE) IS NOT NULL THEN
624 -- if (province!=null !"".equals(province.trim()) )
625 l_na_query := l_na_query || 'AND hzp.province = :P_PROVINCE ' ;
626 useExistsClause := 'Y' ;
627 END IF;
628
629 IF (P_DU_DUNS IS NOT NULL AND trim(P_DU_DUNS) IS NOT NULL) OR
630 (P_DU_NAME IS NOT NULL AND trim(P_DU_NAME) IS NOT NULL) THEN
631 -- if (DUBN!=null !"".equals(DUBN.trim()) )
632 -- if (DUDUNS!=null !"".equals(DUDUNS.trim()) )
633 l_na_query := l_na_query ||
634 ' AND hzp.PARTY_ID in ( select hzr.object_id '||
635 ' from hz_parties hzp1, ' ||
636 ' hz_relationships hzr '||
637 ' where hzp1.party_id = hzr.subject_id '||
638 ' and hzr.subject_table_name = ''HZ_PARTIES'' '||
639 ' and hzr.object_table_name = ''HZ_PARTIES'' '||
640 ' and hzr.relationship_type = ''DOMESTIC_ULTIMATE'' '||
641 ' and hzr.relationship_code = ''DOMESTIC_ULTIMATE_OF'' '||
642 ' and hzr.status = ''A'' ';
643 IF P_DU_DUNS IS NOT NULL AND trim(P_DU_DUNS) IS NOT NULL THEN
644 l_na_query := l_na_query || ' and hzp1.duns_number_c = :P_DU_DUNS ';
645 END IF;
646 IF P_DU_NAME IS NOT NULL AND trim(P_DU_NAME) IS NOT NULL THEN
647 l_na_query := l_na_query || ' and upper(hzp1.party_name) like :P_DU_NAME ';
648 P_DU_NAME_STR := UPPER(P_DU_NAME)||'%';
649 END IF;
650 l_na_query := l_na_query || ')';
651 useExistsClause := 'Y';
652 END IF;
653
654 IF P_GU_DUNS IS NOT NULL AND trim(P_GU_DUNS) IS NOT NULL THEN
655 --if (GUDUNS!=null !"".equals(GUDUNS.trim()) )
656 l_na_query := l_na_query || ' and GU.gu_duns = :P_GU_DUNS ';
657 useExistsClause := 'Y' ;
658 END IF;
659
660 IF P_GU_NAME IS NOT NULL AND trim(P_GU_NAME) IS NOT NULL THEN
661 --if (GUBN!=null !"".equals(GUBN.trim()) )
662 l_na_query := l_na_query || ' and upper(GU.gu_name) like :P_GU_NAME ';
663 useExistsClause := 'Y' ;
664 P_GU_NAME_STR := UPPER(P_GU_NAME)||'%';
665 END IF;
666
667 ----BBB
668 IF (P_SALESPERSON IS NOT NULL AND trim(P_SALESPERSON) IS NOT NULL) OR
669 (P_SALES_GROUP IS NOT NULL AND trim(P_SALES_GROUP) IS NOT NULL) OR
670 (P_SALES_ROLE IS NOT NULL AND trim(P_SALES_ROLE ) IS NOT NULL) THEN
671 --if ( (salesperson!=null !"".equals(salesperson.trim())) ||
672 -- (salesGrp !=null !"".equals(salesGrp.trim()) ) ||
673 -- (salesRole !=null !"".equals(salesRole.trim()) ) )
674
675 l_na_query := l_na_query || ' and ';
676 IF trim(P_ASSIGNED_STATUS)='2' THEN
677 l_na_query := l_na_query || ' ga.terr_group_account_id = -9999555 '; --???
678 ELSE ---AAA
679 l_na_query := l_na_query || ' ga.terr_group_account_id in ( ';
680
681 IF P_SALESPERSON IS NOT NULL AND trim(P_SALESPERSON) IS NOT NULL AND
682 P_SALES_GROUP IS NULL AND trim(P_SALES_GROUP) IS NULL AND
683 P_SALES_ROLE IS NULL AND trim(P_SALES_ROLE ) IS NULL THEN
684
685 l_na_query := l_na_query ||
686 ' select /*+ NO_MERGE */ narsc1.terr_group_account_id '||
687 ' from jtf_tty_named_acct_rsc narsc1, '||
688 '( SELECT dir.resource_id, ' ||
689 ' MY_GRPS.group_id , ' ||
690 ' MY_GRPS.CURRENT_USER_ID ' ||
691 ' FROM jtf_rs_group_members grpmemo , ' ||
692 ' jtf_rs_resource_extns dir , ' ||
693 ' ( SELECT /*+ NO_MERGE */ dv.group_id , ' ||
694 ' mrsc.user_id CURRENT_USER_ID ' ||
695 ' FROM jtf_rs_group_usages usg , ' ||
696 'jtf_rs_groups_denorm dv , ' ||
697 'jtf_rs_rep_managers sgh , '||
698 'jtf_rs_resource_extns mrsc , ' ||
699 'jtf_rs_roles_b rol , ' ||
700 'jtf_rs_role_relations rlt ' ||
701 ' WHERE usg.usage = ''SALES'' ' ||
702 ' AND usg.group_id = dv.group_id ' ||
706 ' AND sgh.resource_id = sgh.parent_resource_id ' ||
703 ' AND rlt.role_id = rol.role_id ' ||
704 ' AND rlt.role_relate_id = sgh.par_role_relate_id '||
705 ' AND dv.parent_group_id = sgh.group_id ' ||
707 ' AND (sgh.hierarchy_type IN (''MGR_TO_MGR'') ' ||
708 ' OR rol.role_code = FND_PROFILE.VALUE(''JTF_TTY_NA_PROXY_USER_ROLE'')) ' ||
709 ' AND mrsc.resource_id = sgh.resource_id ' ||
710 ' ) MY_GRPS ' ||
711 ' WHERE grpmemo.resource_id = dir.resource_id ' ||
712 ' AND grpmemo.group_id = MY_GRPS.group_id ' ||
713 ' UNION ALL ' ||
714 ' SELECT dir.resource_id , '||
715 ' grpmemo.group_id , ' ||
716 ' dir.user_id CURRENT_USER_ID ' ||
717 ' FROM jtf_rs_group_members grpmemo , ' ||
718 ' jtf_rs_resource_extns dir , ' ||
719 ' jtf_rs_group_usages usg ' ||
720 ' WHERE usg.usage = ''SALES'' ' ||
721 ' AND grpmemo.resource_id = dir.resource_id ' ||
722 ' AND grpmemo.group_id = usg.group_id ' ||
723 ' ) repdn1 ' ||
724 ' where narsc1.resource_id = repdn1.resource_id '||
725 ' and narsc1.rsc_group_id = repdn1.group_id '||
726 ' and repdn1.current_user_id = :P_SALESPERSON ';
727 END IF;
728
729 IF P_SALES_GROUP IS NOT NULL AND trim(P_SALES_GROUP) IS NOT NULL AND
730 (P_SALESPERSON IS NULL OR trim(P_SALESPERSON) IS NULL) AND
731 (P_SALES_ROLE IS NULL OR trim(P_SALES_ROLE ) IS NULL) THEN
732
733 l_na_query := l_na_query ||
734 ' select narsc1.terr_group_account_id '||
735 ' from jtf_tty_named_acct_rsc narsc1, '||
736 ' jtf_rs_group_members mem1, '||
737 ' jtf_rs_groups_denorm grpdn1 '||
738 ' where narsc1.resource_id = mem1.resource_id '||
739 ' and narsc1.rsc_group_id = mem1.group_id '||
740 ' and mem1.delete_flag = ''N'' '||
741 ' and mem1.group_id = grpdn1.group_id '||
742 ' and SYSDATE BETWEEN NVL(grpdn1.start_date_active, SYSDATE-1) '||
743 ' AND NVL(grpdn1.end_date_active, SYSDATE+1) '||
744 ' and grpdn1.parent_group_id = :P_SALES_GROUP ';
745 END IF;
746
747 IF P_SALES_ROLE IS NOT NULL AND trim(P_SALES_ROLE) IS NOT NULL AND
748 (P_SALESPERSON IS NULL OR trim(P_SALESPERSON) IS NULL) AND
749 (P_SALES_GROUP IS NULL OR trim(P_SALES_GROUP) IS NULL) THEN
750 l_na_query := l_na_query ||
751 ' select narsc1.terr_group_account_id'||
752 ' from jtf_tty_named_acct_rsc narsc1,'||
753 ' jtf_tty_my_resources_v repdn1,'||
754 ' jtf_rs_rep_managers repmgr1,'||
755 ' jtf_rs_groups_denorm grpdn1 '||
756 ' where narsc1.resource_id = repmgr1.resource_id'||
757 ' and narsc1.rsc_group_id = repmgr1.group_id'||
758 ' and repmgr1.group_id = grpdn1.group_id'||
759 ' and repdn1.resource_id = repmgr1.parent_resource_id'||
760 ' and repdn1.parent_group_id = grpdn1.parent_group_id'||
761 ' and repdn1.current_user_id = :P_USERID '||
762 ' and repdn1.role_code = :P_SALES_ROLE ';
763 END IF;
764
765 IF (P_SALESPERSON IS NOT NULL AND trim(P_SALESPERSON) IS NOT NULL) AND
766 (P_SALES_GROUP IS NOT NULL AND trim(P_SALES_GROUP) IS NOT NULL) AND
767 (P_SALES_ROLE IS NULL OR trim(P_SALES_ROLE) IS NULL) THEN
768 l_na_query := l_na_query ||
769 ' select narsc1.terr_group_account_id '||
770 ' from jtf_tty_named_acct_rsc narsc1, '||
771 ' jtf_tty_my_resources_v repdn1 '||
772 ' where narsc1.resource_id = repdn1.resource_id '||
773 ' and narsc1.rsc_group_id = repdn1.group_id '||
774 ' and repdn1.current_user_id = :P_SALESPERSON '||
775 ' and repdn1.parent_group_id = :P_SALES_GROUP ';
776 END IF;
777
778 IF (P_SALESPERSON IS NOT NULL AND trim(P_SALESPERSON) IS NOT NULL) AND
779 (P_SALES_GROUP IS NULL OR trim(P_SALES_GROUP) IS NULL) AND
780 (P_SALES_ROLE IS NOT NULL AND trim(P_SALES_ROLE) IS NOT NULL) THEN
781 l_na_query := l_na_query ||
782 ' select narsc1.terr_group_account_id '||
783 ' from jtf_tty_named_acct_rsc narsc1, '||
784 ' jtf_tty_my_resources_v repdn1 '||
785 ' where narsc1.resource_id = repdn1.resource_id '||
786 ' and narsc1.rsc_group_id = repdn1.group_id '||
787 ' and repdn1.current_user_id = :P_SALESPERSON '||
788 ' and repdn1.current_user_role_code = :P_SALES_ROLE ';
789 END IF;
790
791 IF (P_SALESPERSON IS NULL OR trim(P_SALESPERSON) IS NULL) AND
792 (P_SALES_GROUP IS NOT NULL AND trim(P_SALES_GROUP) IS NOT NULL) AND
793 (P_SALES_ROLE IS NOT NULL AND trim(P_SALES_ROLE) IS NOT NULL) THEN
794 l_na_query := l_na_query ||
795 ' select narsc1.terr_group_account_id '||
796 ' from jtf_tty_named_acct_rsc narsc1, '||
797 ' jtf_tty_my_resources_v repdn1, '||
801 ' and narsc1.rsc_group_id = repmgr1.group_id '||
798 ' jtf_rs_rep_managers repmgr1, '||
799 ' jtf_rs_groups_denorm grpdn1 '||
800 ' where narsc1.resource_id = repmgr1.resource_id '||
802 ' and repmgr1.group_id = grpdn1.group_id '||
803 ' and repdn1.resource_id = repmgr1.parent_resource_id '||
804 ' and repdn1.current_user_id = :P_USERID '||
805 ' and repdn1.role_code = :P_SALES_ROLE '||
806 ' and grpdn1.parent_group_id = :P_SALES_GROUP ';
807 END IF;
808
809 IF (P_SALESPERSON IS NOT NULL AND trim(P_SALESPERSON) IS NOT NULL) AND
810 (P_SALES_GROUP IS NOT NULL AND trim(P_SALES_GROUP) IS NOT NULL) AND
811 (P_SALES_ROLE IS NOT NULL AND trim(P_SALES_ROLE) IS NOT NULL) THEN
812 l_na_query := l_na_query ||
813 ' select narsc1.terr_group_account_id '||
814 ' from jtf_tty_named_acct_rsc narsc1, '||
815 ' jtf_tty_my_resources_v repdn1 '||
816 ' where narsc1.resource_id = repdn1.resource_id '||
817 ' and narsc1.rsc_group_id = repdn1.group_id '||
818 ' and repdn1.current_user_id = :P_SALESPERSON ' ||
819 ' and repdn1.parent_group_id = :P_SALES_GROUP '||
820 ' and repdn1.current_user_role_code = :P_SALES_ROLE ';
821 END IF;
822 l_na_query := l_na_query || ')';
823 END IF; -- end of AAA : salesperson/grp/role and not unassigned
824 ---- end if salesperson/grp/role
825 -- elseif of BBB
826 ELSIF P_ASSIGNED_STATUS IS NOT NULL AND trim(P_ASSIGNED_STATUS) IS NOT NULL
827 AND P_ASSIGNED_STATUS<>'1' THEN
828
829 l_na_query := l_na_query || ' AND ';
830
831 IF useExistsClause = 'Y' THEN
832 l_na_query := l_na_query || ' EXISTS ' ;
833 ELSE
834 l_na_query := l_na_query || ' ga.terr_group_account_id IN ' ;
835 END IF;
836
837 IF P_ASSIGNED_STATUS='2' THEN
838 IF P_ISADMINFLAG = 'Y' THEN
839 l_na_query := l_na_query ||
840 ' ( select narsc1.terr_group_account_id '||
841 ' from jtf_tty_named_acct_rsc narsc1, '||
842 ' jtf_tty_my_directs_v dir '||
843 ' where narsc1.resource_id = dir.resource_id '||
844 ' and narsc1.rsc_group_id = dir.group_id '||
845 ' and dir.group_id = dir.parent_group_id '||
846 ' and narsc1.assigned_flag = ''N'' '||
847 ' and dir.current_user_id = :P_USERID ';
848 ELSE
849 l_na_query := l_na_query ||
850 '( select narsc1.terr_group_account_id '||
851 ' from jtf_tty_named_acct_rsc narsc1, '||
852 ' jtf_rs_resource_extns rsc1 '||
853 ' where narsc1.resource_id = rsc1.resource_id '||
854 ' and narsc1.assigned_flag = ''N'' '||
855 ' and rsc1.user_id = :P_USERID ';
856 END IF; -- end of isAdmin
857
858 IF useExistsClause = 'Y' THEN
859 l_na_query := l_na_query || ' and narsc1.terr_group_account_id = ga.terr_group_account_id ';
860 END IF;
861 ---- end of AssignedStatus equals 2
862
863 ELSE -- P_ASSIGNED_STATUS<>'2'
864 l_na_query := l_na_query ||
865 ' ( select narsc.terr_group_account_id '||
866 ' from jtf_tty_named_acct_rsc narsc, '||
867 ' jtf_tty_srch_my_resources_v repdn '||
868 ' where narsc.resource_id = repdn.resource_id '||
869 ' and narsc.rsc_group_id = repdn.group_id '||
870 ' and repdn.current_user_id = :P_USERID ';
871
872 IF useExistsClause='Y' THEN
873 l_na_query := l_na_query || ' and narsc.terr_group_account_id = ga.terr_group_account_id ';
874 END IF;
875
876 IF P_ISADMINFLAG = 'Y' THEN
877 l_na_query := l_na_query ||
878 ' and not exists ( select ''Y'' '||
879 ' from jtf_tty_named_acct_rsc narsc1, '||
880 ' jtf_tty_my_directs_v dir '||
881 ' where narsc1.resource_id = dir.resource_id '||
882 ' and narsc1.rsc_group_id = dir.group_id '||
883 ' and dir.group_id = dir.parent_group_id '||
884 ' and narsc1.terr_group_account_id = narsc.terr_group_account_id '||
885 ' and narsc1.assigned_flag = ''N'' '||
886 ' and dir.current_user_id = :P_USERID ';
887 ELSE
888 l_na_query := l_na_query ||
889 ' and not exists ( select ''Y'' '||
890 ' from jtf_tty_named_acct_rsc narsc1, '||
891 ' jtf_rs_resource_extns rsc1 '||
892 ' where narsc1.resource_id = rsc1.resource_id '||
893 ' and narsc1.terr_group_account_id = narsc.terr_group_account_id '||
894 ' and narsc1.assigned_flag = ''N'' '||
895 ' and rsc1.user_id = :P_USERID ';
896 END IF;
897
898 l_na_query := l_na_query || ')';
899 END IF; -- of P_ASSIGNED_STATUS<>'2'
900
901 l_na_query := l_na_query || ')';
902
903 --- end of assigned status
904 ELSE -- of BBB: i.e no rep/group/role/assigned parameters
905 l_na_query := l_na_query || ' AND ';
906
907 IF useExistsClause='Y' THEN
911 ' ga.terr_group_account_id IN (select /*+ NO_MERGE */ narsc1.terr_group_account_id ';
908 l_na_query := l_na_query || ' EXISTS (select narsc1.terr_group_account_id ';
909 ELSE
910 l_na_query := l_na_query ||
912 END IF;
913
914 l_na_query := l_na_query ||
915 ' from jtf_tty_named_acct_rsc narsc1, '||
916 ' jtf_tty_srch_my_resources_v repdn '||
917 ' where narsc1.resource_id = repdn.resource_id '||
918 ' and narsc1.rsc_group_id = repdn.group_id '||
919 ' and repdn.current_user_id = :P_USERID ';
920
921
922 IF useExistsClause='Y' THEN
923 l_na_query := l_na_query || ' and narsc1.terr_group_account_id = ga.terr_group_account_id ';
924 END IF;
925
926 l_na_query := l_na_query || ')';
927
928 END IF; -- of BBB;
929
930 /* identifying_addr **/
931
932 IF P_IDENTADDRFLAG IS NOT NULL THEN
933 l_na_query := l_na_query || ' and hzps.identifying_address_flag = ''Y'' ';
934 END IF;
935
936 l_na_query := l_na_query || ');';
937 l_na_query := l_na_query || ' end; ';
938
939 END IF; -- advance search
940
941 /*
942 insert into tmp values(p_ExtraWhereClause, 'p_ExtraWhereClause'); commit;
943 insert into tmp values(p_paranum,'p_paranum');
944 insert into tmp values(p_1, 'p_1');
945 insert into tmp values(p_2, 'p_2');
946 insert into tmp values(p_3, 'p_3');
947 insert into tmp values(p_4, 'p_4');
948 insert into tmp values(p_5, 'p_5'); commit;
949 */
950
951 IF p_callfrom = 'S' /* simple search */ THEN
952 /* simple search param = 1 or 2, first one is user_id */
953
954 IF m=1 THEN
955
956 EXECUTE IMMEDIATE l_na_query USING SEQ,P_USERID,P_USERID,CURR_DATE,P_USERID,CURR_DATE,P_USERID;
957 ELSE
958 EXECUTE IMMEDIATE l_na_query USING SEQ,P_USERID,P_USERID,CURR_DATE,P_USERID,CURR_DATE,P_USERID,l_var1;
959 END IF;
960
961 ELSE /* adv search */
962 EXECUTE IMMEDIATE l_na_query USING P_USERID,P_GRPID,P_SITE_TYPE,P_SICCODE,P_SITE_DUNS,
963 P_NAMED_ACCOUNT_STR,P_CITY_STR,P_STATE,P_PROVINCE,P_POSTAL_CODE_FROM,
964 P_POSTAL_CODE_TO,P_COUNTRY,P_DU_DUNS,P_DU_NAME_STR,P_GU_DUNS,
965 P_GU_NAME_STR,P_SALESPERSON,P_SALES_GROUP,P_SALES_ROLE,P_ASSIGNED_STATUS,
966 SEQ, CURR_DATE;
967 END IF;
968 COMMIT;
969
970
971 ---insert into tmp values ( to_char(sysdate,'HH,MI:SS'), '3. finish na, before stat, userid='||p_userid);commit;
972
973
974 /* Nas are populated, now start collect sales*/
975
976 /* populate slots */
977 i:=1;
978 l_sql := 'SELECT role_code, MAX(num) num '
979 || 'FROM ( '
980 || 'SELECT rol.role_code role_code, COUNT(rol.role_code) num '
981 || 'FROM '
982 || ' ( SELECT /*+ DYNAMIC_SAMPLING(jtw,5) */ '
983 || ' jtw.jtf_tty_webadi_int_id '
984 || ' FROM jtf_tty_webadi_interface jtw ' --JTF_TTY_WEBADI_INT_GT
985 || ' WHERE jtw.user_id = ' ||p_userid
986 || ' ) sub, '
987 || ' jtf_rs_roles_vl rol, '
988 || ' jtf_tty_named_acct_rsc narsc, '
989 || ' jtf_tty_terr_grp_accts ga '
990 || ' WHERE rol.role_code = narsc.rsc_role_code '
991 || ' AND narsc.terr_group_account_id = ga.terr_group_account_id '
992 || ' AND ga.named_account_id = sub.jtf_tty_webadi_int_id '
993 || ' GROUP BY ga.named_account_id, rol.role_code '
994 || ' ORDER BY MAX(rol.role_name) '
995 || ' ) '
996 || ' GROUP BY role_code ';
997
998 open l_get_statistic_csr for l_sql;
999 fetch l_get_statistic_csr bulk collect into l_get_stat_tbl;
1000 close l_get_statistic_csr;
1001
1002 if l_get_stat_tbl.count > 0 then
1003 FOR stat IN l_get_stat_tbl.first..l_get_stat_tbl.last
1004 LOOP
1005 IF i+l_get_stat_tbl(stat).num-1 <=30 THEN
1006 FOR k IN i..i+l_get_stat_tbl(stat).num-1
1007 LOOP
1008 COL_ROLE(k) := l_get_stat_tbl(stat).role_code;
1009 END LOOP;
1010 ELSE x_seq := '-1';
1011 RETURN;
1012 END IF;
1013
1014 i:=i+l_get_stat_tbl(stat).num;
1015 END LOOP;
1016 end if;
1017
1018 /* for each NA_ID */
1019 --- insert into tmp values ( to_char(sysdate,'HH,MI:SS'), '4. finish analysis '); commit;
1020
1021 FOR m IN getNAFromInterface
1022 -- FOR m IN 1 .. NAST_tbl.TERR_GRP_ACCT_ID.count
1023 LOOP
1024
1025
1026 /* clear col_used flags */
1027 COL_USED :=NARRAY_TYPE(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
1028 RESOURCE_NAME :=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1029 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1030 GROUP_NAME :=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1031 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1032 ROLE_NAME :=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1033 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1034
1035 /*FOR k in 1..30 loop COL_USED(k):=0;
1036 RESOURCE_NAME(k):=null;
1037 GROUP_NAME(k):=null;
1038 ROLE_NAME(k):=null;
1039 end loop;
1040 */
1041 /* get all sales for this NA */
1042 --FOR SALES IN getSalesperson(NAST_tbl.JTF_TTY_WEBADI_INT_ID(m)/*NA_ID*/, NAST_tbl.TERR_GRP_ACCT_ID(m) )
1043 FOR SALES IN getSalesperson(m.JTF_TTY_WEBADI_INT_ID )
1044 LOOP
1045
1046 --k:=0; -- not yet sloted
1047 FOR j IN 1..30
1048 LOOP -- look into 30 slots
1049 IF /*k=0 and*/ SALES.role_code = COL_ROLE(j) AND COL_USED(j)=0 THEN
1050 COL_USED(j) :=1;
1051 --k:=1;
1052 -- write into pl/sql table row
1053 RESOURCE_NAME(j):=SALES.resource_name;
1054 GROUP_NAME(j) :=SALES.group_name;
1055 ROLE_NAME(j) :=SALES.role_name;
1056 EXIT;
1057 END IF;
1058 END LOOP; -- of slotting
1059 END LOOP; -- of SALES
1060
1061
1062 UPDATE JTF_TTY_WEBADI_INTERFACE -- /*+ INDEX JTF_TTY_WEBADI_INTF_N2 */
1063 SET RESOURCE1_NAME=RESOURCE_NAME(1),GROUP1_NAME=GROUP_NAME(1),ROLE1_NAME=ROLE_NAME(1),
1064 RESOURCE2_NAME=RESOURCE_NAME(2),GROUP2_NAME=GROUP_NAME(2),ROLE2_NAME=ROLE_NAME(2),
1065 RESOURCE3_NAME=RESOURCE_NAME(3),GROUP3_NAME=GROUP_NAME(3),ROLE3_NAME=ROLE_NAME(3),
1066 RESOURCE4_NAME=RESOURCE_NAME(4),GROUP4_NAME=GROUP_NAME(4),ROLE4_NAME=ROLE_NAME(4),
1067 RESOURCE5_NAME=RESOURCE_NAME(5),GROUP5_NAME=GROUP_NAME(5),ROLE5_NAME=ROLE_NAME(5),
1068 RESOURCE6_NAME=RESOURCE_NAME(6),GROUP6_NAME=GROUP_NAME(6),ROLE6_NAME=ROLE_NAME(6),
1069 RESOURCE7_NAME=RESOURCE_NAME(7),GROUP7_NAME=GROUP_NAME(7),ROLE7_NAME=ROLE_NAME(7),
1070 RESOURCE8_NAME=RESOURCE_NAME(8),GROUP8_NAME=GROUP_NAME(8),ROLE8_NAME=ROLE_NAME(8),
1071 RESOURCE9_NAME=RESOURCE_NAME(9),GROUP9_NAME=GROUP_NAME(9),ROLE9_NAME=ROLE_NAME(9),
1072 RESOURCE10_NAME=RESOURCE_NAME(10),GROUP10_NAME=GROUP_NAME(10),ROLE10_NAME=ROLE_NAME(10),
1073 RESOURCE11_NAME=RESOURCE_NAME(11),GROUP11_NAME=GROUP_NAME(11),ROLE11_NAME=ROLE_NAME(11),
1074 RESOURCE12_NAME=RESOURCE_NAME(12),GROUP12_NAME=GROUP_NAME(12),ROLE12_NAME=ROLE_NAME(12),
1075 RESOURCE13_NAME=RESOURCE_NAME(13),GROUP13_NAME=GROUP_NAME(13),ROLE13_NAME=ROLE_NAME(13),
1076 RESOURCE14_NAME=RESOURCE_NAME(14),GROUP14_NAME=GROUP_NAME(14),ROLE14_NAME=ROLE_NAME(14),
1077 RESOURCE15_NAME=RESOURCE_NAME(15),GROUP15_NAME=GROUP_NAME(15),ROLE15_NAME=ROLE_NAME(15),
1078 RESOURCE16_NAME=RESOURCE_NAME(16),GROUP16_NAME=GROUP_NAME(16),ROLE16_NAME=ROLE_NAME(16),
1079 RESOURCE17_NAME=RESOURCE_NAME(17),GROUP17_NAME=GROUP_NAME(17),ROLE17_NAME=ROLE_NAME(17),
1080 RESOURCE18_NAME=RESOURCE_NAME(18),GROUP18_NAME=GROUP_NAME(18),ROLE18_NAME=ROLE_NAME(18),
1081 RESOURCE19_NAME=RESOURCE_NAME(19),GROUP19_NAME=GROUP_NAME(19),ROLE19_NAME=ROLE_NAME(19),
1082 RESOURCE20_NAME=RESOURCE_NAME(20),GROUP20_NAME=GROUP_NAME(20),ROLE20_NAME=ROLE_NAME(20),
1083 RESOURCE21_NAME=RESOURCE_NAME(21),GROUP21_NAME=GROUP_NAME(21),ROLE21_NAME=ROLE_NAME(21),
1084 RESOURCE22_NAME=RESOURCE_NAME(22),GROUP22_NAME=GROUP_NAME(22),ROLE22_NAME=ROLE_NAME(22),
1085 RESOURCE23_NAME=RESOURCE_NAME(23),GROUP23_NAME=GROUP_NAME(23),ROLE23_NAME=ROLE_NAME(23),
1086 RESOURCE24_NAME=RESOURCE_NAME(24),GROUP24_NAME=GROUP_NAME(24),ROLE24_NAME=ROLE_NAME(24),
1087 RESOURCE25_NAME=RESOURCE_NAME(25),GROUP25_NAME=GROUP_NAME(25),ROLE25_NAME=ROLE_NAME(25),
1088 RESOURCE26_NAME=RESOURCE_NAME(26),GROUP26_NAME=GROUP_NAME(26),ROLE26_NAME=ROLE_NAME(26),
1089 RESOURCE27_NAME=RESOURCE_NAME(27),GROUP27_NAME=GROUP_NAME(27),ROLE27_NAME=ROLE_NAME(27),
1090 RESOURCE28_NAME=RESOURCE_NAME(28),GROUP28_NAME=GROUP_NAME(28),ROLE28_NAME=ROLE_NAME(28),
1091 RESOURCE29_NAME=RESOURCE_NAME(29),GROUP29_NAME=GROUP_NAME(29),ROLE29_NAME=ROLE_NAME(29),
1092 RESOURCE30_NAME=RESOURCE_NAME(30),GROUP30_NAME=GROUP_NAME(30),ROLE30_NAME=ROLE_NAME(30)
1093 WHERE user_id = p_userid
1094 --and TERR_GRP_ACCT_ID = NAST_tbl.TERR_GRP_ACCT_ID(m);
1095 AND TERR_GRP_ACCT_ID =m.TERR_GRP_ACCT_ID;
1096
1097
1098 -- if mod(i, 300)=0 then insert into tmp values ( to_char(sysdate,'HH,MI:SS'), 'finish update with ' || i); commit; end if;
1099
1100 END LOOP; -- of NA
1101 x_seq := TO_CHAR(seq);
1102 ---insert into tmp values ( to_char(sysdate,'HH,MI:SS'), '5. done with update, seq=' || x_seq); commit;
1103 COMMIT;
1104 -- clear all fields
1105
1106 END;
1107
1108
1109 END Jtf_Tty_Webadi_Nadoc_Pkg;