[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_EXCEL_NAORG_PVT
Source
1 PACKAGE BODY JTF_TTY_EXCEL_NAORG_PVT AS
2 /* $Header: jtfamifb.pls 120.14 2006/07/31 18:57:48 mhtran noship $ */
3 -- ===========================================================================+
4 -- | Copyright (c) 2003 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +===========================================================================
8 -- Start of Comments
9 -- ---------------------------------------------------
10 -- PACKAGE NAME: JTF_TTY_EXCEL_NAORG_PVT
11 -- ---------------------------------------------------
12 -- PURPOSE
13 --
14 -- This package is used to populate the interface table jtf_tty_webadi_interface
15 -- for the admin export download
16 --
17 --
18 -- Procedures:
19 -- (see below for specification)
20 --
21 -- NOTES
22 -- This package is publicly available for use
23 --
24 -- HISTORY
25 -- 02/24/2004 ACHANDA Created
26 -- 04/14/2004 SGKUMAR Bug 3570818 fixed. Performance fix for
27 -- Named Accounts Search by City, Role or
28 -- Salesperson and Group
29 -- 10/07/2004 sgkumar Bug 3907932 fixed. Performance fix
30 -- for na search by salesperson
31 -- 10/14/2005 vbghosh Modified to suport export button functionality.
32 --
33 -- End of Comments
34 --
35
36 g_seq NUMBER;
37 --g_seq NUMBER := -9999;
38 g_rows_limit CONSTANT NUMBER := 25000;
39 g_no_lookup VARCHAR2(80);
40
41 G_SEQUENCE_ERROR EXCEPTION;
42 G_DELETE_ERROR EXCEPTION;
43 G_TERRGRP_MISSING EXCEPTION;
44 G_NO_LOOKUP_MISSING EXCEPTION;
45
46 /* this function returns true is p_str contains any character othet than % or space */
47 FUNCTION CONTAINS_ONLY_PCTG(P_STR IN VARCHAR2) RETURN BOOLEAN
48 AS
49 l_length NUMBER;
50 l_found BOOLEAN;
51 BEGIN
52 l_found := FALSE;
53
54 IF (TRIM(p_str) IS NOT NULL) THEN
55 l_length := LENGTH(p_str);
56 FOR i IN 1..l_length LOOP
57 IF (SUBSTR(p_str, i, 1) <> '%') THEN
58 l_found := TRUE;
59 EXIT;
60 END IF;
61 END LOOP;
62 END IF;
63
64 RETURN l_found;
65 EXCEPTION
66 WHEN OTHERS THEN
67 RAISE;
68 END;
69
70 PROCEDURE POPULATE_INTERFACE_FOR_ORG( P_USERID IN INTEGER
71 ,P_SICCODE IN VARCHAR2
72 ,P_SICCODE_TYPE IN VARCHAR2
73 ,P_SITE_DUNS IN VARCHAR2
74 ,P_PARTY_NAME IN VARCHAR2
75 ,P_WEB_SITE IN VARCHAR2
76 ,P_EMAIL_ADDR IN VARCHAR2
77 ,P_CITY IN VARCHAR2
78 ,P_STATE IN VARCHAR2
79 ,P_COUNTY IN VARCHAR2
80 ,P_PROVINCE IN VARCHAR2
81 ,P_POSTAL_CODE_FROM IN VARCHAR2
82 ,P_POSTAL_CODE_TO IN VARCHAR2
83 ,P_COUNTRY IN VARCHAR2
84 ,P_PARTY_NUMBER IN VARCHAR2
85 ,P_CERT_LEVEL IN VARCHAR2
86 ,P_PARTY_TYPE IN VARCHAR2
87 ,P_HIERARCHY_TYPE IN VARCHAR2
88 ,P_RELATIONSHIP_ROLE IN VARCHAR2
89 ,P_CLASS_TYPE IN VARCHAR2
90 ,P_CLASS_CODE IN VARCHAR2
91 ,P_ANNUAL_REV_FROM IN NUMBER
92 ,P_ANNUAL_REV_TO IN NUMBER
93 ,P_NUM_EMP_FROM IN VARCHAR2
94 ,P_NUM_EMP_TO IN VARCHAR2
95 ,P_CUST_CATEGORY IN VARCHAR2
96 ,P_IDADDR_FLAG IN VARCHAR2
97 ,X_ROWS_INSERTED OUT NOCOPY NUMBER
98 ,X_RETCODE OUT NOCOPY VARCHAR2
99 ,X_ERRBUF OUT NOCOPY VARCHAR2) IS
100
101 l_na_query VARCHAR2(6000);
102 l_curr_date VARCHAR2(100);
103
104 L_PARTY_NAME_STR VARCHAR2(361);
105 L_WEBSITE_STR VARCHAR2(2001);
106 L_EMAIL_STR VARCHAR2(2001);
107 l_useExistsClause VARCHAR2(1);
108
109 BEGIN
110
111 X_RETCODE := 0;
112 X_ERRBUF := NULL;
113 X_ROWS_INSERTED := 0;
114 l_curr_date := TO_CHAR(SYSDATE);
115 l_useExistsClause := 'N';
116
117 L_PARTY_NAME_STR := NULL;
118 L_WEBSITE_STR := NULL;
119 L_EMAIL_STR := NULL;
120
121
122 /* form the pl/sql block to insert all the ORGs in the interface table jtf_tty_webadi_interface */
123 l_na_query :=
124 'DECLARE ' ||
125 ' P_USERID INTEGER := :P_USERID; '||
126 ' P_SICCODE VARCHAR2(100) := :P_SICCODE; '||
127 ' P_SICCODE_TYPE VARCHAR2(100) := :P_SICCODE_TYPE; '||
128 ' P_SITE_DUNS VARCHAR2(100) := :P_SITE_DUNS; '||
129 ' P_PARTY_NAME VARCHAR2(360) := :P_PARTY_NAME; '||
130 ' P_WEB_SITE VARCHAR2(2000) := :P_WEB_SITE; '||
131 ' P_EMAIL_ADDR VARCHAR2(2000) := :P_EMAIL_ADDR; '||
132 ' P_CITY VARCHAR2(100) := :P_CITY; '||
133 ' P_STATE VARCHAR2(100) := :P_STATE; '||
134 ' P_COUNTY VARCHAR2(100) := :P_COUNTY; '||
135 ' P_PROVINCE VARCHAR2(100) := :P_PROVINCE; '||
136 ' P_POSTAL_CODE_FROM VARCHAR2(100) := :P_POSTAL_CODE_FROM; '||
137 ' P_POSTAL_CODE_TO VARCHAR2(100) := :P_POSTAL_CODE_TO; '||
138 ' P_COUNTRY VARCHAR2(100) := :P_COUNTRY; '||
139 ' P_PARTY_NUMBER VARCHAR2(30) := :P_PARTY_NUMBER; '||
140 ' P_CERT_LEVEL VARCHAR2(60) := :P_CERT_LEVEL; ' ||
141 ' P_PARTY_TYPE VARCHAR2(60) := :P_PARTY_TYPE; ' ||
142 ' P_HIERARCHY_TYPE VARCHAR2(60) := :P_HIERARCHY_TYPE; ' ||
143 ' P_RELATIONSHIP_ROLE VARCHAR2(60) := :P_RELATIONSHIP_ROLE; ' ||
144 ' P_CLASS_TYPE VARCHAR2(60) := :P_CLASS_TYPE; ' ||
145 ' P_CLASS_CODE VARCHAR2(60) := :P_CLASS_CODE; ' ||
146 ' P_ANNUAL_REV_FROM NUMBER := :P_ANNUAL_REV_FROM; ' ||
147 ' P_ANNUAL_REV_TO NUMBER := :P_ANNUAL_REV_TO; ' ||
148 ' P_NUM_EMP_FROM VARCHAR2(10) := :P_NUM_EMP_FROM; ' ||
149 ' P_NUM_EMP_TO VARCHAR2(10) := :P_NUM_EMP_TO; ' ||
150 ' P_CUST_CATEGORY VARCHAR2(60) := :P_CUST_CATEGORY; ' ||
151 ' P_IDENT_ADDR_FLAG VARCHAR2(1) := :P_IDADDR_FLAG; ' ||
152 ' BEGIN '||
153 ' INSERT into JTF_TTY_WEBADI_INTERFACE ( ' ||
154 ' USER_SEQUENCE' ||
155 ' ,USER_ID' ||
156 ' ,TERR_GRP_ACCT_ID' ||
157 ' ,JTF_TTY_WEBADI_INT_ID' ||
158 ' ,NAMED_ACCOUNT' ||
159 ' ,SITE_TYPE' ||
160 ' ,DUNS'||
161 ' ,TRADE_NAME' ||
162 ' ,GU_DUNS' ||
163 ' ,GU_NAME' ||
164 ' ,DU_DUNS' ||
165 ' ,DU_NAME' ||
166 ' ,CITY' ||
167 ' ,STATE' ||
168 ' ,POSTAL_CODE' ||
169 ' ,TERRITORY_GROUP' ||
170 ' ,PARTY_NUMBER' ||
171 ' ,TO_TERRITORY_GROUP' ||
172 ' ,DELETE_FLAG' ||
173 ' ,CREATED_BY' ||
174 ' ,CREATION_DATE' ||
175 ' ,LAST_UPDATED_BY' ||
176 ' ,LAST_UPDATE_DATE' ||
180 ' ,IDENTIFYING_ADDRESS ) '||
177 ' ,PARTY_SITE_NUMBER'||
178 ' ,SALES_MANAGER' ||
179 ' ,PHONETIC_NAME' ||
181 ' SELECT ' || g_seq || ' USER_SEQUENCE '||
182 ' ,' || P_USERID || ' USER_ID '||
183 ' ,TO_NUMBER(null) TERR_GRP_ACCT_ID '||
184 ' ,TO_NUMBER(null) JTF_TTY_WEBADI_INT_ID '||
185 ' ,NAMED_ACCOUNT NAMED_ACCOUNT '||
186 ' ,null SITE_TYPE '||
187 ' ,SITE_DUNS DUNS '||
188 ' ,TRADE_NAME TRADE_NAME '||
189 ' ,GU_DUNS GU_DUNS '||
190 ' ,GU_NAME GU_NAME '||
191 ' ,DU_DUNS DU_DUNS '||
192 ' ,DU_NAME DU_NAME '||
193 ' ,CITY CITY '||
194 ' ,STATE STATE '||
195 ' ,POSTAL_CODE POSTAL_CODE ' ||
196 ' ,null TERRITORY_GROUP ' ||
197 ' ,PARTY_NUMBER PARTY_NUMBER ' ||
198 ' ,NULL TO_TERRITORY_GROUP ' ||
199 ' ,''' || g_no_lookup || ''' DELETE_FLAG ' ||
200 ' ,' || P_USERID || ' CREATED_BY '||
201 ' ,''' || l_curr_date|| '''' || ' CREATION_DATE ' ||
202 ' ,' || P_USERID || ' LAST_UPDATED_BY '||
203 ' ,''' || l_curr_date|| '''' || ' LAST_UPDATE_DATE ' ||
204 ' ,PARTY_SITE_NUMBER PARTY_SITE_NUMBER'||
205 ' ,null SALES_MANAGER '||
206 ' ,PHONETIC_NAME PHONETIC_NAME' ||
207 ' ,IDENTIFYING_ADDRESS_FLAG IDENTIFYING_ADDRESS' ||
208 ' FROM ( ' ||
209 ' SELECT '||
210 ' hzp.party_name named_account ' ||
211 ' ,hzp.duns_number_c site_duns ' ||
212 ' ,hzp.known_as trade_name ' ||
213 ' ,GU.GU_DUNS gu_duns ' ||
214 ' ,GU.GU_NAME gu_name ' ||
215 ' ,null du_duns ' ||
216 ' ,null du_name ' ||
217 ' ,hzp.party_number party_number ' ||
218 ' ,hzl.city city ' ||
219 ' ,hzl.state state ' ||
220 ' ,hzl.postal_code postal_code ' ||
221 ' ,hzps.party_site_number party_site_number '||
222 ' ,identifying_address_flag identifying_address_flag ' ||
223 ' ,decode(hzp.party_type, ''ORGANIZATION'',hzp.ORGANIZATION_NAME_PHONETIC, ' ||
224 ' ''PERSON'',hzp.PERSON_LAST_NAME_PHONETIC || '', '' || hzp.PERSON_FIRST_NAME_PHONETIC,null) PHONETIC_NAME' ||
225 ' FROM hz_parties hzp, hz_party_sites hzps, hz_locations hzl';
226
227 IF ( (P_ANNUAL_REV_FROM IS NOT NULL) OR
228 (P_ANNUAL_REV_TO IS NOT NULL) OR
229 (trim(P_NUM_EMP_FROM) IS NOT NULL) OR
230 (trim(P_NUM_EMP_TO) IS NOT NULL) ) THEN
231 l_na_query := l_na_query || ' ,hz_organization_profiles hzop ';
232 END IF;
233
234 l_na_query := l_na_query ||
235 ' ,( /* Global Ultimate : min is used to make sre that there is 1 GU for each party */ ' ||
236 ' SELECT min(gup.party_name) GU_NAME ' ||
237 ' , min(gup.duns_number_c) GU_DUNS ' ||
238 ' , hzr.object_id GU_OBJECT_ID ' ||
239 ' FROM hz_parties gup ' ||
240 ' , hz_relationships hzr ' ||
241 ' WHERE hzr.subject_table_name = ''HZ_PARTIES'' ' ||
242 ' AND hzr.object_table_name = ''HZ_PARTIES'' ' ||
243 ' AND hzr.relationship_type = ''GLOBAL_ULTIMATE'' ' ||
244 ' AND hzr.relationship_code = ''GLOBAL_ULTIMATE_OF'' ' ||
245 ' AND hzr.status = ''A'' ' ||
246 ' AND hzr.subject_id = gup.party_id ' ||
247 ' AND gup.status = ''A'' ' ||
248 ' GROUP BY hzr.object_id ) GU ' ||
249 ' WHERE hzp.status = ''A'' ' ||
250 ' AND GU.GU_OBJECT_ID (+) = hzp.party_id ' ||
251 ' AND hzp.party_id = hzps.party_id ' ||
252 ' AND hzps.status = ''A'' ' ||
253 ' AND hzps.location_id = hzl.location_id ' ;
254
255 IF ( (P_ANNUAL_REV_FROM IS NOT NULL) OR
256 (P_ANNUAL_REV_TO IS NOT NULL) OR
257 (trim(P_NUM_EMP_FROM) IS NOT NULL) OR
258 (trim(P_NUM_EMP_TO) IS NOT NULL) ) THEN
259 l_na_query := l_na_query || ' AND hzp.party_id = hzop.party_id ' ||
260 ' AND hzop.status = ''A'' ' ||
261 ' AND ( (sysdate >= hzop.effective_start_date) AND ' ||
262 ' ( ( hzop.effective_end_date IS NULL ) OR ' ||
263 ' ( sysdate <= hzop.effective_end_date) ) ' ||
264 ' ) ';
265
266 END IF;
267
268 IF (trim(P_IDADDR_FLAG) IS NOT NULL) THEN
269 l_na_query := l_na_query || 'AND hzps.identifying_address_flag = :P_IDADDR_FLAG ' ;
270 l_useExistsClause := 'Y';
271 END IF;
272
273 IF (trim(P_SICCODE) IS NOT NULL) THEN
274 l_na_query := l_na_query || 'AND hzp.sic_code = :P_SICCODE ' ;
275 l_useExistsClause := 'Y';
276 END IF;
277
278 IF (trim(P_SICCODE_TYPE) IS NOT NULL) THEN
279 l_na_query := l_na_query || 'AND hzp.sic_code_type = :P_SICCODE_TYPE ' ;
280 END IF;
281
282 IF ((trim(P_SITE_DUNS) IS NOT NULL) AND
283 (trim(P_RELATIONSHIP_ROLE) IS NULL) ) THEN
284 l_na_query := l_na_query || 'AND hzp.duns_number_c = :P_SITE_DUNS ' ;
285 l_useExistsClause := 'Y';
286 END IF;
287
288 IF ( (CONTAINS_ONLY_PCTG(P_PARTY_NAME)) AND
289 (trim(P_RELATIONSHIP_ROLE) IS NULL) ) THEN
290 l_na_query := l_na_query || 'AND upper(hzp.party_name) like :P_PARTY_NAME ' ;
291 l_useExistsClause := 'Y';
292 END IF;
293
294 IF (CONTAINS_ONLY_PCTG(P_WEB_SITE)) THEN
295 l_na_query := l_na_query || 'AND upper(hzp.url) like :P_WEB_SITE ' ;
296 END IF;
297
298 IF (CONTAINS_ONLY_PCTG(P_EMAIL_ADDR)) THEN
299 l_na_query := l_na_query || 'AND upper(hzp.email_address) like :P_EMAIL_ADDR ' ;
300 END IF;
301
302 IF (trim(P_PARTY_TYPE) IS NOT NULL) THEN
303 l_na_query := l_na_query || 'AND hzp.party_type = :P_PARTY_TYPE ' ;
307 IF (trim(P_CITY) IS NOT NULL) THEN
304 END IF;
305
306
308 l_na_query := l_na_query || 'AND hzl.city = :P_CITY ';
309 END IF;
310
311 IF (trim(P_STATE) IS NOT NULL) THEN
312 l_na_query := l_na_query || 'AND hzl.state = :P_STATE ';
313 END IF;
314
315 IF (trim(P_PROVINCE) IS NOT NULL) THEN
316 l_na_query := l_na_query || 'AND hzl.province = :P_PROVINCE ' ;
317 END IF;
318
319 IF (trim(P_COUNTY) IS NOT NULL) THEN
320 l_na_query := l_na_query || 'AND hzl.COUNTY = :P_COUNTY ' ;
321 END IF;
322
323 IF ((trim(P_POSTAL_CODE_FROM) IS NOT NULL) AND (trim(P_POSTAL_CODE_TO) IS NOT NULL)
324 AND (P_POSTAL_CODE_FROM = P_POSTAL_CODE_TO)) THEN
325 l_na_query := l_na_query || 'AND hzl.postal_code = :P_POSTAL_CODE_FROM ';
326
327 ELSIF ((trim(P_POSTAL_CODE_FROM) IS NOT NULL) AND (trim(P_POSTAL_CODE_TO) IS NOT NULL)
328 AND (P_POSTAL_CODE_FROM <> P_POSTAL_CODE_TO)) THEN
329 l_na_query := l_na_query || 'AND hzl.postal_code between :P_POSTAL_CODE_FROM and :P_POSTAL_CODE_TO ';
330
331 ELSIF ((trim(P_POSTAL_CODE_FROM) IS NOT NULL) AND (trim(P_POSTAL_CODE_TO) IS NULL)) THEN
332 l_na_query := l_na_query || 'AND hzl.postal_code = :P_POSTAL_CODE_FROM ';
333
334 ELSIF ((trim(P_POSTAL_CODE_FROM) IS NULL) AND (trim(P_POSTAL_CODE_TO) IS NOT NULL)) THEN
335 l_na_query := l_na_query || 'AND hzl.postal_code = :P_POSTAL_CODE_TO ' ;
336 END IF;
337
338 IF (trim(P_COUNTRY) IS NOT NULL) THEN
339 l_na_query := l_na_query || 'AND hzl.country = :P_COUNTRY ';
340 END IF;
341
342 IF ((trim(P_PARTY_NUMBER) IS NOT NULL) AND
343 (trim(P_RELATIONSHIP_ROLE) IS NULL) ) THEN
344 l_na_query := l_na_query || 'AND hzp.party_number = :P_PARTY_NUMBER ';
345 l_useExistsClause := 'Y';
346 END IF;
347
348
349 IF ((trim(P_CERT_LEVEL) IS NOT NULL) AND (P_CERT_LEVEL <> 'ALL')) THEN
350 l_na_query := l_na_query || ' and hzp.certification_level = :P_CERT_LEVEL ';
351 END IF;
352
353 -- Annual Revenue
354 IF ((P_ANNUAL_REV_FROM IS NOT NULL) AND (P_ANNUAL_REV_TO IS NOT NULL)
355 AND (P_ANNUAL_REV_FROM = P_ANNUAL_REV_TO)) THEN
356 l_na_query := l_na_query || 'AND hzop.curr_fy_potential_revenue = :P_ANNUAL_REV_FROM ';
357
358 ELSIF ((P_ANNUAL_REV_FROM IS NOT NULL) AND (P_ANNUAL_REV_TO IS NOT NULL)
359 AND (P_ANNUAL_REV_FROM <> P_ANNUAL_REV_TO)) THEN
360 l_na_query := l_na_query || 'AND hzop.curr_fy_potential_revenue between :P_ANNUAL_REV_FROM and :P_ANNUAL_REV_TO ';
361
362 ELSIF ((P_ANNUAL_REV_FROM IS NOT NULL) AND (P_ANNUAL_REV_TO IS NULL)) THEN
363 l_na_query := l_na_query || 'AND hzop.curr_fy_potential_revenue >= :P_ANNUAL_REV_FROM ';
364
365 ELSIF ((P_ANNUAL_REV_FROM IS NULL) AND (P_ANNUAL_REV_TO IS NOT NULL)) THEN
366 l_na_query := l_na_query || 'AND hzop.curr_fy_potential_revenue <= :P_ANNUAL_REV_TO ' ;
367 END IF;
368
369 -- Number of Employees
370
371 IF ((trim(P_NUM_EMP_FROM) IS NOT NULL) AND (trim(P_NUM_EMP_TO) IS NOT NULL)
372 AND (P_NUM_EMP_FROM = P_NUM_EMP_TO)) THEN
373 l_na_query := l_na_query || 'AND hzop.emp_at_primary_adr = :P_NUM_EMP_FROM ';
374
375 ELSIF ((trim(P_NUM_EMP_FROM) IS NOT NULL) AND (trim(P_NUM_EMP_TO) IS NOT NULL)
376 AND (P_NUM_EMP_FROM <> P_NUM_EMP_TO)) THEN
377 l_na_query := l_na_query || 'AND hzop.emp_at_primary_adr between :P_NUM_EMP_FROM and :P_NUM_EMP_TO ';
378
379 ELSIF ((trim(P_NUM_EMP_FROM) IS NOT NULL) AND (trim(P_NUM_EMP_TO) IS NULL)) THEN
380 l_na_query := l_na_query || 'AND hzop.emp_at_primary_adr = :P_NUM_EMP_FROM ';
381
382 ELSIF ((trim(P_NUM_EMP_FROM) IS NULL) AND (trim(P_NUM_EMP_TO) IS NOT NULL)) THEN
383 l_na_query := l_na_query || 'hzop.emp_at_primary_adr = :P_NUM_EMP_TO ' ;
384 END IF;
385
386
387 IF (trim(P_CUST_CATEGORY) IS NOT NULL) THEN
388 l_na_query := l_na_query || 'AND hzp.category_code = :P_CUST_CATEGORY' ;
389 END IF;
390
391
392 IF (trim(P_HIERARCHY_TYPE) IS NOT NULL) AND
393 (trim(P_RELATIONSHIP_ROLE) IS NOT NULL) THEN
394
395 IF l_useExistsClause = 'Y' THEN
396 l_na_query := l_na_query || ' AND EXISTS ( SELECT null ';
397 ELSE
398 l_na_query := l_na_query || ' AND hzp.party_id IN ( SELECT b.subject_id ';
399 END IF;
400
401 l_na_query := l_na_query || 'FROM hz_relationships b, hz_parties c ';
402 l_na_query := l_na_query || 'WHERE b.object_id = c.party_id ';
403
404 IF l_useExistsClause = 'Y' THEN
405 l_na_query := l_na_query || 'AND b.subject_id = hzp.party_id ' ;
406 END IF;
407
408 l_na_query := l_na_query || 'AND b.subject_table_name = ''HZ_PARTIES'' ';
409 l_na_query := l_na_query || 'AND b.object_table_name = ''HZ_PARTIES'' ';
410 l_na_query := l_na_query || 'AND b.status = ''A'' ';
411 l_na_query := l_na_query || 'AND b.relationship_type = :P_HIERARCHY_TYPE ';
412 l_na_query := l_na_query || 'AND b.relationship_code = :P_RELATIONSHIP_ROLE ';
413
414 IF (CONTAINS_ONLY_PCTG(P_PARTY_NAME)) THEN
415 l_na_query := l_na_query || 'AND upper(c.party_name) like :P_PARTY_NAME ' ;
416 END IF;
417
418
419 IF (trim(P_SITE_DUNS) IS NOT NULL) THEN
420 l_na_query := l_na_query || 'AND c.duns_number_c = :P_SITE_DUNS ' ;
421 END IF;
422
423 IF (trim(P_PARTY_NUMBER) IS NOT NULL) THEN
424 l_na_query := l_na_query || 'AND c.party_number = :P_PARTY_NUMBER ';
425 END IF;
426
427 l_na_query := l_na_query || ')';
428 END IF;
429
430 IF (trim(P_CLASS_TYPE) IS NOT NULL) THEN
431
432 IF l_useExistsClause = 'Y' THEN
433 l_na_query := l_na_query || ' AND EXISTS ( SELECT null ';
434 ELSE
438 l_na_query := l_na_query || 'FROM hz_code_assignments hca ' ;
435 l_na_query := l_na_query || ' AND hzp.party_id IN ( SELECT hca.owner_table_id ' ;
436 END IF;
437
439 l_na_query := l_na_query || 'WHERE hca.status = ''A'' ';
440 l_na_query := l_na_query || 'AND sysdate >= hca.start_date_active ';
441 l_na_query := l_na_query || 'AND ( hca.end_date_active IS NULL OR ';
442 l_na_query := l_na_query || ' sysdate <= hca.end_date_active ) ';
443 l_na_query := l_na_query || 'AND hca.class_category = :P_CLASS_TYPE ';
444
445
446 IF (trim(P_CLASS_CODE) IS NOT NULL) THEN
447 l_na_query := l_na_query || ' AND hca.class_code = :P_CLASS_CODE ';
448 END IF;
449
450 IF l_useExistsClause = 'Y' THEN
451 l_na_query := l_na_query || ' AND hca.owner_table_id = hzp.party_id ';
452 END IF;
453
454 l_na_query := l_na_query || ')';
455 END IF;
456
457 l_na_query := l_na_query || ' AND ROWNUM <= ' || TO_CHAR(g_rows_limit + 1);
458 l_na_query := l_na_query || ' ); ';
459 l_na_query := l_na_query || ':X_ROWS_INSERTED := SQL%ROWCOUNT; ';
460 l_na_query := l_na_query || ' end; ';
461 /* end of the pl/sql block */
462
463 IF (CONTAINS_ONLY_PCTG(P_PARTY_NAME)) THEN
464 L_PARTY_NAME_STR := UPPER(P_PARTY_NAME) || '%';
465 END IF;
466 IF (CONTAINS_ONLY_PCTG(P_WEB_SITE)) THEN
467 L_WEBSITE_STR := UPPER(P_WEB_SITE) || '%';
468 END IF;
469 IF (CONTAINS_ONLY_PCTG(P_EMAIL_ADDR)) THEN
470 L_EMAIL_STR := UPPER(P_EMAIL_ADDR) || '%';
471 END IF;
472
473 /* execute the pl/sql block and commit */
474 EXECUTE IMMEDIATE l_na_query USING
475 P_USERID,P_SICCODE,P_SICCODE_TYPE, P_SITE_DUNS, L_PARTY_NAME_STR, L_WEBSITE_STR, L_EMAIL_STR,
476 P_CITY, P_STATE, P_COUNTY, P_PROVINCE, P_POSTAL_CODE_FROM, P_POSTAL_CODE_TO,P_COUNTRY, P_PARTY_NUMBER,
477 P_CERT_LEVEL, P_PARTY_TYPE, P_HIERARCHY_TYPE, P_RELATIONSHIP_ROLE, P_CLASS_TYPE, P_CLASS_CODE,
478 P_ANNUAL_REV_FROM, P_ANNUAL_REV_TO, P_NUM_EMP_FROM, P_NUM_EMP_TO, P_CUST_CATEGORY,
479 P_IDADDR_FLAG, OUT X_ROWS_INSERTED;
480
481 COMMIT;
482
483 EXCEPTION
484 WHEN OTHERS THEN
485 RAISE;
486 END POPULATE_INTERFACE_FOR_ORG;
487
488 PROCEDURE POPULATE_INTERFACE_FOR_NA( P_USERID IN INTEGER
489 ,P_GRPID IN NUMBER
490 ,P_SITE_TYPE IN VARCHAR2
491 ,P_SICCODE IN VARCHAR2
492 ,P_SITE_DUNS IN VARCHAR2
493 ,P_NAMED_ACCOUNT IN VARCHAR2
494 ,P_CITY IN VARCHAR2
495 ,P_STATE IN VARCHAR2
496 ,P_PROVINCE IN VARCHAR2
497 ,P_POSTAL_CODE_FROM IN VARCHAR2
498 ,P_POSTAL_CODE_TO IN VARCHAR2
499 ,P_COUNTRY IN VARCHAR2
500 ,P_DU_DUNS IN VARCHAR2
501 ,P_DU_NAME IN VARCHAR2
502 ,P_PARTY_NUMBER IN VARCHAR2
503 ,P_GU_DUNS IN VARCHAR2
504 ,P_GU_NAME IN VARCHAR2
505 ,P_CERT_LEVEL IN VARCHAR2
506 ,P_SALESPERSON IN NUMBER
507 ,P_SALES_GROUP IN NUMBER
508 ,P_SALES_ROLE IN VARCHAR2
509 ,P_PARTY_TYPE IN VARCHAR2
510 ,P_HIERARCHY_TYPE IN VARCHAR2 --added
511 ,P_RELATIONSHIP_ROLE IN VARCHAR2
512 ,P_CLASS_TYPE IN VARCHAR2
513 ,P_CLASS_CODE IN VARCHAR2
514 ,P_ANNUAL_REV_FROM IN NUMBER
515 ,P_ANNUAL_REV_TO IN NUMBER
516 ,P_NUM_EMP_FROM IN VARCHAR2
517 ,P_NUM_EMP_TO IN VARCHAR2
518 ,P_CUST_CATEGORY IN VARCHAR2
519 ,P_IDADDR_FLAG IN VARCHAR2
520 ,P_SICCODE_TYPE IN VARCHAR2
521 ,P_GRPNAME IN VARCHAR2 -- added 06/15/2006
522 ,P_VIEW_DATE IN DATE -- added 07/20/2006
523 ,P_ORG_ID IN NUMBER -- added 07/20/2006
524 ,X_ROWS_INSERTED OUT NOCOPY NUMBER
525 ,X_RETCODE OUT NOCOPY VARCHAR2
526 ,X_ERRBUF OUT NOCOPY VARCHAR2) IS
527
528
529 RESOURCE_NAME VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
530 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
531 GROUP_NAME VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
532 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
533 ROLE_NAME VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
534 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
535 COL_ROLE VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
539 RESOURCE_ATTRIBUTE2 VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
536 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
537 RESOURCE_ATTRIBUTE1 VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
538 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
540 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
541 RESOURCE_ATTRIBUTE3 VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
542 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
543 RESOURCE_ATTRIBUTE4 VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
544 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
545 RESOURCE_ATTRIBUTE5 VARRAY_TYPE:=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
546 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
547 RESOURCE_START_DATE DARRAY_TYPE:=DARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
548 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
549 RESOURCE_END_DATE DARRAY_TYPE:=DARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
550 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
551
552
553 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);
554
555 l_na_query VARCHAR2(31000);
556 l_curr_date VARCHAR2(100);
557
558 P_NAMED_ACCOUNT_STR VARCHAR2(361);
559 P_CITY_STR VARCHAR2(61);
560 P_DU_NAME_STR VARCHAR2(361);
561 P_GU_NAME_STR VARCHAR2(361);
562
563 i NUMBER;
564 j NUMBER;
565 k NUMBER;
566
567 /*vbghosh added */
568 l_useExistsClause VARCHAR2(1);
569 l_terr_group_id NUMBER;
570
571 /* JRADHAKR fixed the following cursor to consider the
572 roles only for a specific TG bug 3576571 */
573
574 CURSOR getStatistic (userid IN NUMBER) IS
575 SELECT role_code, MAX(num) num
576 FROM (
577 SELECT rol.role_code role_code, COUNT(rol.role_code) num
578 FROM
579 jtf_rs_roles_vl rol,
580 jtf_tty_named_acct_rsc narsc,
581 jtf_tty_terr_grp_accts ga
582 WHERE rol.role_code = narsc.rsc_role_code
583 AND narsc.terr_group_account_id = ga.terr_group_account_id
584 AND ga.terr_group_account_id IN
585 ( SELECT terr_grp_acct_id
586 FROM jtf_tty_webadi_interface
587 WHERE user_id = userid
588 )
589 GROUP BY ga.terr_group_account_id, rol.role_code
590 ORDER BY MAX(rol.role_name)
591 )
592 GROUP BY role_code;
593
594
595 CURSOR getNAFromInterface(userid IN NUMBER) IS
596 SELECT jtf_tty_webadi_int_id, terr_grp_acct_id
597 FROM jtf_tty_webadi_interface
598 WHERE user_id = userid;
599
600 CURSOR getSalesperson( P_TG_ACCT_ID IN NUMBER) IS
601 SELECT rsc.resource_name resource_name
602 , rol.role_name role_name
603 , grp.group_name group_name
604 , rsc.resource_id resource_id
605 , grp.group_id group_id
606 , rol.role_code role_code
607 , narsc.ATTRIBUTE1
608 , narsc.ATTRIBUTE2
609 , narsc.ATTRIBUTE3
610 , narsc.ATTRIBUTE4
611 , narsc.ATTRIBUTE5
612 , narsc.START_DATE
613 , narsc.END_DATE
614 FROM jtf_rs_resource_extns_vl rsc
615 , jtf_rs_groups_vl grp
616 , jtf_rs_roles_vl rol
617 , jtf_tty_named_acct_rsc narsc
618 , jtf_tty_terr_grp_accts ga
619 -- , jtf_tty_terr_groups tga
620 WHERE rsc.resource_id = narsc.resource_id
621 AND grp.group_id = narsc.rsc_group_id
622 AND rol.role_code = narsc.rsc_role_code
623 AND narsc.terr_group_account_id = ga.terr_group_account_id
624 -- AND tga.terr_group_id = ga.terr_group_id
625 -- AND sysdate >= tga.active_from_date
626 --AND (tga.active_to_date is null OR
627 -- sysdate <= tga.active_to_date)
628 AND narsc.rsc_resource_type = 'RS_EMPLOYEE'
629 AND ga.terr_group_account_id = P_TG_ACCT_ID;
630
631 BEGIN
632
633 X_RETCODE := 0;
634 X_ERRBUF := NULL;
635 X_ROWS_INSERTED := 0;
636 l_curr_date := TO_CHAR(SYSDATE);
637
638 P_NAMED_ACCOUNT_STR := NULL;
639 P_CITY_STR := NULL;
640 P_DU_NAME_STR := NULL;
641 P_GU_NAME_STR := NULL;
642
643 -- dbms_output.put_line('Start POPULATE_INTERFACE_FOR_NA');
644
645 /* form the pl/sql block to insert all the NAs in the interface table jtf_tty_webadi_interface */
646 l_na_query :=
647 'DECLARE ' ||
648 ' P_USERID INTEGER := :P_USERID; '||
649 ' P_GRPID NUMBER := :P_GRPID ; '|| --vbghosh Added
650 ' P_VIEW_DATE DATE := :P_VIEW_DATE; ' || -- added 07/20/2006
651 ' P_ORG_ID NUMBER := :P_ORG_ID; ' || -- added 07/20/2006
652 ' P_GRPNAME VARCHAR2(360) := TRIM(UPPER(:P_GRPNAME)); ' || -- added 06/15/2006
653 ' P_SITE_TYPE VARCHAR2(100) := :P_SITE_TYPE; '||
654 ' P_SICCODE VARCHAR2(100) := :P_SICCODE; '||
658 ' P_STATE VARCHAR2(100) := :P_STATE; '||
655 ' P_SITE_DUNS VARCHAR2(100) := :P_SITE_DUNS; '||
656 ' P_NAMED_ACCOUNT VARCHAR2(360) := :P_NAMED_ACCOUNT; '||
657 ' P_CITY VARCHAR2(100) := :P_CITY; '||
659 ' P_PROVINCE VARCHAR2(100) := :P_PROVINCE; '||
660 ' P_POSTAL_CODE_FROM VARCHAR2(100) := :P_POSTAL_CODE_FROM; '||
661 ' P_POSTAL_CODE_TO VARCHAR2(100) := :P_POSTAL_CODE_TO; '||
662 ' P_COUNTRY VARCHAR2(100) := :P_COUNTRY; '||
663 ' P_DU_DUNS VARCHAR2(100) := :P_DU_DUNS; '||
664 ' P_DU_NAME VARCHAR2(360) := :P_DU_NAME; '||
665 ' P_PARTY_NUMBER VARCHAR2(30) := :P_PARTY_NUMBER; '||
666 ' P_GU_DUNS VARCHAR2(100) := :P_GU_DUNS; '||
667 ' P_GU_NAME VARCHAR2(360) := :P_GU_NAME; '||
668 ' P_CERT_LEVEL VARCHAR2(30) := :P_CERT_LEVEL; ' ||
669 ' P_SALESPERSON NUMBER := :P_SALESPERSON; '||
670 ' P_SALES_GROUP NUMBER := :P_SALES_GROUP; '||
671 ' P_SALES_ROLE VARCHAR2(100) := :P_SALES_ROLE; '||
672 ' P_PARTY_TYPE VARCHAR2(60) := :P_PARTY_TYPE; ' || --vbghosh Added
673 ' P_HIERARCHY_TYPE VARCHAR2(60) := :P_HIERARCHY_TYPE; ' ||
674 ' P_RELATIONSHIP_ROLE VARCHAR2(60) := :P_RELATIONSHIP_ROLE; ' ||
675 ' P_CLASS_TYPE VARCHAR2(60) := :P_CLASS_TYPE; ' ||
676 ' P_CLASS_CODE VARCHAR2(60) := :P_CLASS_CODE; ' ||
677 ' P_ANNUAL_REV_FROM NUMBER := :P_ANNUAL_REV_FROM; ' ||
678 ' P_ANNUAL_REV_TO NUMBER := :P_ANNUAL_REV_TO; ' ||
679 ' P_NUM_EMP_FROM VARCHAR2(10) := :P_NUM_EMP_FROM; ' ||
680 ' P_NUM_EMP_TO VARCHAR2(10) := :P_NUM_EMP_TO; ' ||
681 ' P_CUST_CATEGORY VARCHAR2(60) := :P_CUST_CATEGORY; ' ||
682 ' P_IDENT_ADDR_FLAG VARCHAR2(1) := :P_IDADDR_FLAG; ' || -- end added
683 ' BEGIN '||
684
685 ' INSERT into JTF_TTY_WEBADI_INTERFACE ( ' ||
686 ' USER_SEQUENCE ' ||
687 ' ,USER_ID ' ||
688 ' ,TERR_GRP_ACCT_ID ' ||
689 ' ,JTF_TTY_WEBADI_INT_ID ' ||
690 ' ,NAMED_ACCOUNT ' ||
691 ' ,SITE_TYPE ' ||
692 ' ,DUNS '||
693 ' ,TRADE_NAME ' ||
694 ' ,GU_DUNS ' ||
695 ' ,GU_NAME ' ||
696 ' ,DU_DUNS ' ||
697 ' ,DU_NAME ' ||
698 ' ,CITY ' ||
699 ' ,STATE ' ||
700 ' ,POSTAL_CODE ' ||
701 ' ,TERRITORY_GROUP ' ||
702 ' ,PARTY_NUMBER ' ||
703 ' ,TO_TERRITORY_GROUP ' ||
704 ' ,DELETE_FLAG ' ||
705 ' ,CREATED_BY ' ||
706 ' ,CREATION_DATE ' ||
707 ' ,LAST_UPDATED_BY ' ||
708 ' ,LAST_UPDATE_DATE ' ||
709 ' ,PARTY_SITE_NUMBER ' ||
710 ' ,SALES_MANAGER '||
711 ' ,PHONETIC_NAME' ||
712 ' ,IDENTIFYING_ADDRESS '||
713 ' , start_date '||
714 ' , end_date '||
715 ' , attribute1 '||
716 ' , attribute2 '||
717 ' , attribute3 '||
718 ' , attribute4 '||
719 ' , attribute5 '||
720 ' , attribute6 '||
721 ' , attribute7 '||
722 ' , attribute8 '||
723 ' , attribute9 '||
724 ' , attribute10 '||
725 ' , attribute11 '||
726 ' , attribute12 '||
727 ' , attribute13 '||
728 ' , attribute14 '||
729 ' , attribute15 ) '||
730 ' SELECT ' ||
731 g_seq || ' USER_SEQUENCE '||
732 ' ,' || P_USERID || ' USER_ID '||
733 ' ,TGAID TERR_GRP_ACCT_ID '||
734 ' ,NAID JTF_TTY_WEBADI_INT_ID '||
735 ' ,NAMED_ACCOUNT NAMED_ACCOUNT '||
736 ' ,SITE_TYPE SITE_TYPE '||
737 ' ,SITE_DUNS DUNS '||
738 ' ,TRADE_NAME TRADE_NAME '||
739 ' ,GU_DUNS GU_DUNS '||
740 ' ,GU_NAME GU_NAME '||
741 ' ,DU_DUNS DU_DUNS '||
742 ' ,DU_NAME DU_NAME '||
743 ' ,CITY CITY '||
744 ' ,STATE STATE '||
745 ' ,POSTAL_CODE POSTAL_CODE ' ||
746 ' ,TERRGRPNAME TERRITORY_GROUP ' ||
747 ' ,PARTY_NUMBER PARTY_NUMBER ' ||
748 ' ,NULL TO_TERRITORY_GROUP ' ||
749 ' ,''' || g_no_lookup || ''' DELETE_FLAG ' ||
750 ' ,' || P_USERID || ' CREATED_BY '||
751 ' ,''' || l_curr_date|| '''' || ' CREATION_DATE ' ||
752 ' ,' || P_USERID || ' LAST_UPDATED_BY '||
753 ' ,''' || l_curr_date|| '''' || ' LAST_UPDATE_DATE ' ||
754 ' ,PARTY_SITE_NUMBER PARTY_SITE_NUMBER' ||
755 ' ,null SALES_MANAGER ' ||
756 ' ,PHONETIC_NAME PHONETIC_NAME' ||
757 ' ,IDENTIFYING_ADDRESS_FLAG IDENTIFYING_ADDRESS' ||
758 ' , start_date '||
759 ' , end_date '||
760 ' , attribute1 '||
761 ' , attribute2 '||
762 ' , attribute3 '||
763 ' , attribute4 '||
764 ' , attribute5 '||
765 ' , attribute6 '||
766 ' , attribute7 '||
767 ' , attribute8 '||
768 ' , attribute9 '||
769 ' , attribute10 '||
770 ' , attribute11 '||
771 ' , attribute12 '||
772 ' , attribute13 '||
773 ' , attribute14 '||
774 ' , attribute15 '||
775 ' FROM ( ' ||
776 ' SELECT '||
777 ' ga.terr_group_account_id tgaid ' ||
778 ' ,na.named_account_id naid ' ||
779 ' ,hzp.party_name named_account ' ||
780 ' ,lkp.meaning site_type ' ||
781 ' ,hzp.duns_number_c site_duns ' ||
782 ' ,hzp.known_as trade_name ' ||
783 ' ,GU.GU_DUNS gu_duns ' ||
784 ' ,GU.GU_NAME gu_name ' ||
785 ' ,null du_duns ' ||
786 ' ,null du_name ' ||
787 ' ,hzl.city city ' ||
788 ' ,hzl.state state ' ||
789 ' ,hzl.postal_code postal_code ' ||
793 ' ,identifying_address_flag identifying_address_flag ' ||
790 ' ,ttygrp.terr_group_name terrgrpname ' ||
791 ' ,hzp.party_number party_number '||
792 ' ,hzps.party_site_number party_site_number '||
794 ' ,decode(hzp.party_type, ''ORGANIZATION'',hzp.ORGANIZATION_NAME_PHONETIC, ' ||
795 ' ''PERSON'',hzp.PERSON_LAST_NAME_PHONETIC || '', '' || hzp.PERSON_FIRST_NAME_PHONETIC,null) PHONETIC_NAME' ||
796 ' , ga.start_date '||
797 ' , ga.end_date '||
798 ' ,ga.attribute1 '||
799 ' ,ga.attribute2 '||
800 ' ,ga.attribute3 '||
801 ' ,ga.attribute4 '||
802 ' ,ga.attribute5 '||
803 ' ,ga.attribute6 '||
804 ' ,ga.attribute7 '||
805 ' ,ga.attribute8 '||
806 ' ,ga.attribute9 '||
807 ' ,ga.attribute10 '||
808 ' ,ga.attribute11 '||
809 ' ,ga.attribute12 '||
810 ' ,ga.attribute13 '||
811 ' ,ga.attribute14 '||
812 ' ,ga.attribute15 '||
813 ' FROM ' ||
814 ' hz_parties hzp ' ||
815 ' ,hz_locations hzl '||
816 ' ,hz_party_sites hzps '||
817 ' ,jtf_tty_named_accts na ' ||
818 ' ,jtf_tty_terr_grp_accts ga ' ||
819 ' ,fnd_lookups lkp ' ||
820 ' ,jtf_tty_terr_groups ttygrp ' ;
821
822 /*vbghosh Added start */
823 IF ( (P_ANNUAL_REV_FROM IS NOT NULL) OR
824 (P_ANNUAL_REV_TO IS NOT NULL) OR
825 (trim(P_NUM_EMP_FROM) IS NOT NULL) OR
826 (trim(P_NUM_EMP_TO) IS NOT NULL) ) THEN
827 l_na_query := l_na_query || ' ,hz_organization_profiles hzop ';
828 END IF;
829
830 l_na_query := l_na_query ||
831 ' ,( /* Global Ultimate : min is used to make sre that there is 1 GU for each party */ ' ||
832 ' SELECT min(gup.party_name) GU_NAME ' ||
833 ' , min(gup.duns_number_c) GU_DUNS ' ||
834 ' , hzr.object_id GU_OBJECT_ID ' ||
835 ' FROM hz_parties gup ' ||
836 ' , hz_relationships hzr ' ||
837 ' WHERE hzr.subject_table_name = ''HZ_PARTIES'' ' ||
838 ' AND hzr.object_table_name = ''HZ_PARTIES'' ' ||
839 ' AND hzr.relationship_type = ''GLOBAL_ULTIMATE'' ' ||
840 ' AND hzr.relationship_code = ''GLOBAL_ULTIMATE_OF'' ' ||
841 ' AND hzr.status = ''A'' ' ||
842 ' AND hzr.subject_id = gup.party_id ' ||
843 ' AND gup.status = ''A'' ' ||
844 ' GROUP BY hzr.object_id ) GU ' ||
845 ' WHERE hzp.party_id = na.party_id ' ||
846 ' AND hzps.party_site_id = na.party_site_id ' ||
847 ' AND hzps.location_id = hzl.location_id ' ||
848 ' AND hzp.party_id = hzps.party_id ' ||
849 ' AND na.site_type_code = lkp.lookup_code ' ||
850 ' AND lkp.lookup_type = ''JTF_TTY_SITE_TYPE_CODE'' ' ||
851 ' AND na.named_account_id = ga.named_account_id ' ||
852 ' AND ttygrp.terr_group_id = ga.terr_group_id ' ||
853 -- ' AND ttygrp.terr_group_id = :P_GRPID ' ||
854 ' AND GU.GU_OBJECT_ID (+) = hzp.party_id ';
855
856
857 if ( P_GRPID is not null ) then
858 l_na_query := l_na_query || ' AND ttygrp.terr_group_id = :P_GRPID ' ;
859 end if;
860
861
862 IF ( p_org_id is not null OR P_ORG_ID <> -999 OR
863 P_GRPNAME is not null OR P_VIEW_DATE is not null) THEN
864
865 l_na_query := l_na_query || ' AND EXISTS ( SELECT 1 ' ||
866 ' FROM jtf_terr_all jt ' ||
867 ' WHERE jt.territory_type_id = -1 ';
868
869 if ( P_ORG_ID is not null AND P_ORG_ID <> -999 ) THEN
870 l_na_query := l_na_query || ' AND jt.org_id = :P_ORG_ID ';
871 end if;
872
873 if ( P_GRPNAME is not null ) then
874 l_na_query := l_na_query || ' AND UPPER (jt.NAME) LIKE :P_GRPNAME ';
875 end if;
876
877 if ( P_VIEW_DATE is not null ) then
878 l_na_query := l_na_query || ' AND :P_VIEW_DATE BETWEEN jt.start_date_active AND jt.end_date_active ' ;
879 end if;
880
881 l_na_query := l_na_query || ' AND jt.terr_group_account_id = ga.TERR_GROUP_ACCOUNT_ID ) ' ;
882 END IF; -- add exist query for jtf_terr_all table
883
884
885 IF ( (P_ANNUAL_REV_FROM IS NOT NULL) OR
886 (P_ANNUAL_REV_TO IS NOT NULL) OR
887 (trim(P_NUM_EMP_FROM) IS NOT NULL) OR
888 (trim(P_NUM_EMP_TO) IS NOT NULL) ) THEN
889 l_na_query := l_na_query || ' AND hzp.party_id = hzop.party_id ' ||
890 ' AND hzop.status = ''A'' ' ||
891 ' AND ( (sysdate >= hzop.effective_start_date) AND ' ||
892 ' ( ( hzop.effective_end_date IS NULL ) OR ' ||
893 ' ( sysdate <= hzop.effective_end_date) ) ' ||
894 ' ) ';
895
896 END IF;
897
898
899 IF (trim(P_IDADDR_FLAG) IS NOT NULL) THEN
900 l_na_query := l_na_query || 'AND hzps.identifying_address_flag = :P_IDADDR_FLAG ' ;
901 l_useExistsClause := 'Y';
902 END IF;
903
904
905 /* vbghosh Added end */
906
907 IF ((trim(P_SITE_TYPE) IS NOT NULL) AND (P_SITE_TYPE <> 'ALL')) THEN
908 l_na_query := l_na_query || 'AND lkp.lookup_code = :P_SITE_TYPE ';
909 END IF;
910
911 IF (trim(P_SICCODE) IS NOT NULL) THEN
912 l_na_query := l_na_query || 'AND hzp.sic_code = :P_SICCODE ' ;
913 l_useExistsClause := 'Y';
914 END IF;
915
916 --vbghosh added
920
917 IF (trim(P_SICCODE_TYPE) IS NOT NULL) THEN
918 l_na_query := l_na_query || 'AND hzp.sic_code_type = :P_SICCODE_TYPE ' ;
919 END IF;
921 --vbghosh Added
922 IF ( (CONTAINS_ONLY_PCTG(P_NAMED_ACCOUNT)) AND
923 (trim(P_RELATIONSHIP_ROLE) IS NULL) ) THEN
924 l_na_query := l_na_query || 'AND upper(hzp.party_name) like :P_NAMED_ACCOUNT ' ;
925 l_useExistsClause := 'Y';
926 END IF;
927
928
929 IF ((trim(P_SITE_DUNS) IS NOT NULL) AND
930 (trim(P_RELATIONSHIP_ROLE) IS NULL) ) THEN
931 l_na_query := l_na_query || 'AND hzp.duns_number_c = :P_SITE_DUNS ' ;
932 l_useExistsClause := 'Y';
933 END IF;
934
935
936
937 IF (trim(P_CITY) IS NOT NULL) THEN
938 l_na_query := l_na_query || 'AND upper(hzl.city) like :P_CITY ';
939 END IF;
940
941 IF (trim(P_STATE) IS NOT NULL) THEN
942 l_na_query := l_na_query || 'AND hzl.state = :P_STATE ';
943 END IF;
944
945 IF (trim(P_PROVINCE) IS NOT NULL) THEN
946 l_na_query := l_na_query || 'AND hzl.province = :P_PROVINCE ' ;
947 END IF;
948
949 IF ((trim(P_POSTAL_CODE_FROM) IS NOT NULL) AND (trim(P_POSTAL_CODE_TO) IS NOT NULL)
950 AND (P_POSTAL_CODE_FROM = P_POSTAL_CODE_TO)) THEN
951 l_na_query := l_na_query || 'AND hzl.postal_code = :P_POSTAL_CODE_FROM ';
952
953 ELSIF ((trim(P_POSTAL_CODE_FROM) IS NOT NULL) AND (trim(P_POSTAL_CODE_TO) IS NOT NULL)
954 AND (P_POSTAL_CODE_FROM <> P_POSTAL_CODE_TO)) THEN
955 l_na_query := l_na_query || 'AND hzl.postal_code between :P_POSTAL_CODE_FROM and :P_POSTAL_CODE_TO ';
956
957 ELSIF ((trim(P_POSTAL_CODE_FROM) IS NOT NULL) AND (trim(P_POSTAL_CODE_TO) IS NULL)) THEN
958 l_na_query := l_na_query || 'AND hzl.postal_code = :P_POSTAL_CODE_FROM ';
959
960 ELSIF ((trim(P_POSTAL_CODE_FROM) IS NULL) AND (trim(P_POSTAL_CODE_TO) IS NOT NULL)) THEN
961 l_na_query := l_na_query || 'AND hzl.postal_code = :P_POSTAL_CODE_TO ' ;
962 END IF;
963
964 IF (trim(P_COUNTRY) IS NOT NULL) THEN
965 l_na_query := l_na_query || 'AND hzl.country = :P_COUNTRY ';
966 END IF;
967
968
969 IF ((trim(P_DU_DUNS) IS NOT NULL) OR (trim(P_DU_NAME) IS NOT NULL)) THEN
970 l_na_query := l_na_query ||
971 ' and hzp.party_id in ' ||
972 ' ( select hzr.object_id '||
973 ' from hz_parties hzp1, ' ||
974 ' hz_relationships hzr '||
975 ' where hzp1.party_id = hzr.subject_id '||
976 ' and hzr.subject_table_name = ''HZ_PARTIES'' '||
977 ' and hzr.object_table_name = ''HZ_PARTIES'' '||
978 ' and hzr.relationship_type = ''DOMESTIC_ULTIMATE'' '||
979 ' and hzr.relationship_code = ''DOMESTIC_ULTIMATE_OF'' '||
980 ' and hzr.status = ''A'' ';
981
982 IF (trim(P_DU_DUNS) IS NOT NULL) THEN
983 l_na_query := l_na_query || ' and hzp1.duns_number_c = :P_DU_DUNS ';
984 END IF;
985 IF (CONTAINS_ONLY_PCTG(P_DU_NAME)) THEN
986 l_na_query := l_na_query || ' and upper(hzp1.party_name) like :P_DU_NAME ';
987 END IF;
988 l_na_query := l_na_query || ')';
989 END IF;
990
991 --vbghosh added
992 IF ((trim(P_PARTY_NUMBER) IS NOT NULL) AND
993 (trim(P_RELATIONSHIP_ROLE) IS NULL) ) THEN
994 l_na_query := l_na_query || 'AND hzp.party_number = :P_PARTY_NUMBER ';
995 l_useExistsClause := 'Y';
996 END IF;
997
998
999 --vbghosh added
1000 -- Annual Revenue
1001 IF ((P_ANNUAL_REV_FROM IS NOT NULL) AND (P_ANNUAL_REV_TO IS NOT NULL)
1002 AND (P_ANNUAL_REV_FROM = P_ANNUAL_REV_TO)) THEN
1003 l_na_query := l_na_query || 'AND hzop.curr_fy_potential_revenue = :P_ANNUAL_REV_FROM ';
1004
1005 ELSIF ((P_ANNUAL_REV_FROM IS NOT NULL) AND (P_ANNUAL_REV_TO IS NOT NULL)
1006 AND (P_ANNUAL_REV_FROM <> P_ANNUAL_REV_TO)) THEN
1007 l_na_query := l_na_query || 'AND hzop.curr_fy_potential_revenue between :P_ANNUAL_REV_FROM and :P_ANNUAL_REV_TO ';
1008
1009 ELSIF ((P_ANNUAL_REV_FROM IS NOT NULL) AND (P_ANNUAL_REV_TO IS NULL)) THEN
1010 l_na_query := l_na_query || 'AND hzop.curr_fy_potential_revenue >= :P_ANNUAL_REV_FROM ';
1011
1012 ELSIF ((P_ANNUAL_REV_FROM IS NULL) AND (P_ANNUAL_REV_TO IS NOT NULL)) THEN
1013 l_na_query := l_na_query || 'AND hzop.curr_fy_potential_revenue <= :P_ANNUAL_REV_TO ' ;
1014 END IF;
1015
1016 --vbghosh added
1017 -- Number of Employees
1018
1019 IF ((trim(P_NUM_EMP_FROM) IS NOT NULL) AND (trim(P_NUM_EMP_TO) IS NOT NULL)
1020 AND (P_NUM_EMP_FROM = P_NUM_EMP_TO)) THEN
1021 l_na_query := l_na_query || 'AND hzop.emp_at_primary_adr = :P_NUM_EMP_FROM ';
1022
1023 ELSIF ((trim(P_NUM_EMP_FROM) IS NOT NULL) AND (trim(P_NUM_EMP_TO) IS NOT NULL)
1024 AND (P_NUM_EMP_FROM <> P_NUM_EMP_TO)) THEN
1025 l_na_query := l_na_query || 'AND hzop.emp_at_primary_adr between :P_NUM_EMP_FROM and :P_NUM_EMP_TO ';
1026
1027 ELSIF ((trim(P_NUM_EMP_FROM) IS NOT NULL) AND (trim(P_NUM_EMP_TO) IS NULL)) THEN
1028 l_na_query := l_na_query || 'AND hzop.emp_at_primary_adr = :P_NUM_EMP_FROM ';
1029
1030 ELSIF ((trim(P_NUM_EMP_FROM) IS NULL) AND (trim(P_NUM_EMP_TO) IS NOT NULL)) THEN
1031 l_na_query := l_na_query || 'hzop.emp_at_primary_adr = :P_NUM_EMP_TO ' ;
1032 END IF;
1033
1034 --vbghosh added
1035 IF (trim(P_HIERARCHY_TYPE) IS NOT NULL) AND
1036 (trim(P_RELATIONSHIP_ROLE) IS NOT NULL) THEN
1037
1038 IF l_useExistsClause = 'Y' THEN
1039 l_na_query := l_na_query || ' AND EXISTS ( SELECT null ';
1040 ELSE
1041 l_na_query := l_na_query || ' AND hzp.party_id IN ( SELECT b.subject_id ';
1042 END IF;
1043
1047 IF l_useExistsClause = 'Y' THEN
1044 l_na_query := l_na_query || 'FROM hz_relationships b, hz_parties c ';
1045 l_na_query := l_na_query || 'WHERE b.object_id = c.party_id ';
1046
1048 l_na_query := l_na_query || 'AND b.subject_id = hzp.party_id ' ;
1049 END IF;
1050
1051 l_na_query := l_na_query || 'AND b.subject_table_name = ''HZ_PARTIES'' ';
1052 l_na_query := l_na_query || 'AND b.object_table_name = ''HZ_PARTIES'' ';
1053 l_na_query := l_na_query || 'AND b.status = ''A'' ';
1054 l_na_query := l_na_query || 'AND b.relationship_type = :P_HIERARCHY_TYPE ';
1055 l_na_query := l_na_query || 'AND b.relationship_code = :P_RELATIONSHIP_ROLE ';
1056
1057 IF (CONTAINS_ONLY_PCTG(P_NAMED_ACCOUNT)) THEN
1058 l_na_query := l_na_query || 'AND upper(c.party_name) like :P_NAMED_ACCOUNT ' ;
1059 END IF;
1060
1061
1062 IF (trim(P_SITE_DUNS) IS NOT NULL) THEN
1063 l_na_query := l_na_query || 'AND c.duns_number_c = :P_SITE_DUNS ' ;
1064 END IF;
1065
1066 IF (trim(P_PARTY_NUMBER) IS NOT NULL) THEN
1067 l_na_query := l_na_query || 'AND c.party_number = :P_PARTY_NUMBER ';
1068 END IF;
1069
1070 l_na_query := l_na_query || ')';
1071 END IF;
1072
1073 --vbghosh added
1074 IF (trim(P_CLASS_TYPE) IS NOT NULL) THEN
1075
1076 IF l_useExistsClause = 'Y' THEN
1077 l_na_query := l_na_query || ' AND EXISTS ( SELECT null ';
1078 ELSE
1079 l_na_query := l_na_query || ' AND hzp.party_id IN ( SELECT hca.owner_table_id ' ;
1080 END IF;
1081
1082 l_na_query := l_na_query || 'FROM hz_code_assignments hca ' ;
1083 l_na_query := l_na_query || 'WHERE hca.status = ''A'' ';
1084 l_na_query := l_na_query || 'AND sysdate >= hca.start_date_active ';
1085 l_na_query := l_na_query || 'AND ( hca.end_date_active IS NULL OR ';
1086 l_na_query := l_na_query || ' sysdate <= hca.end_date_active ) ';
1087 l_na_query := l_na_query || 'AND hca.class_category = :P_CLASS_TYPE ';
1088
1089
1090 IF (trim(P_CLASS_CODE) IS NOT NULL) THEN
1091 l_na_query := l_na_query || ' AND hca.class_code = :P_CLASS_CODE ';
1092 END IF;
1093
1094 IF l_useExistsClause = 'Y' THEN
1095 l_na_query := l_na_query || ' AND hca.owner_table_id = hzp.party_id ';
1096 END IF;
1097
1098 l_na_query := l_na_query || ')';
1099 END IF;
1100
1101
1102
1103
1104 IF (trim(P_GU_DUNS) IS NOT NULL) THEN
1105 l_na_query := l_na_query || ' and gu.gu_duns = :P_GU_DUNS ';
1106 END IF;
1107
1108 IF (CONTAINS_ONLY_PCTG(P_GU_NAME)) THEN
1109 l_na_query := l_na_query || ' and upper(gu.gu_name) LIKE :P_GU_NAME ';
1110 END IF;
1111
1112 IF ((trim(P_CERT_LEVEL) IS NOT NULL) AND (P_CERT_LEVEL <> 'ALL')) THEN
1113 l_na_query := l_na_query || ' and hzp.certification_level = :P_CERT_LEVEL ';
1114 END IF;
1115
1116 IF ((trim(P_SALESPERSON) IS NOT NULL) OR
1117 (trim(P_SALES_GROUP) IS NOT NULL) OR
1118 (trim(P_SALES_ROLE) IS NOT NULL)) THEN
1119
1120
1121
1122 IF ((trim(P_SALESPERSON) IS NOT NULL) AND
1123 (trim(P_SALES_GROUP) IS NULL) AND
1124 (trim(P_SALES_ROLE) IS NULL)) THEN
1125 l_na_query := l_na_query || ' and ga.terr_group_account_id in ( ';
1126 l_na_query := l_na_query ||
1127 ' select /*+ NO_MERGE */ narsc1.terr_group_account_id ' ||
1128 ' from jtf_tty_named_acct_rsc narsc1, ' ||
1129 ' ( SELECT dir.resource_id, ' ||
1130 ' MY_GRPS.group_id , ' ||
1131 ' MY_GRPS.CURRENT_USER_ID ' ||
1132 ' FROM jtf_rs_group_members grpmemo , ' ||
1133 ' jtf_rs_resource_extns dir , ' ||
1134 ' ( SELECT /*+ NO_MERGE */ dv.group_id , ' ||
1135 ' mrsc.user_id CURRENT_USER_ID ' ||
1136 ' FROM jtf_rs_group_usages usg , ' ||
1137 ' jtf_rs_groups_denorm dv , ' ||
1138 ' jtf_rs_rep_managers sgh , ' ||
1139 ' jtf_rs_resource_extns mrsc , ' ||
1140 ' jtf_rs_roles_b rol , ' ||
1141 ' jtf_rs_role_relations rlt ' ||
1142 ' WHERE usg.usage = ''SALES'' ' ||
1143 ' AND usg.group_id = dv.group_id ' ||
1144 ' AND rlt.role_id = rol.role_id ' ||
1145 ' AND rlt.role_relate_id = sgh.par_role_relate_id ' ||
1146 ' AND dv.parent_group_id = sgh.group_id ' ||
1147 ' AND sgh.resource_id = sgh.parent_resource_id ' ||
1148 ' AND (sgh.hierarchy_type IN (''MGR_TO_MGR'') ' ||
1149 ' OR rol.role_code = FND_PROFILE.VALUE(''JTF_TTY_NA_PROXY_USER_ROLE'')) ' ||
1150 ' AND mrsc.resource_id = sgh.resource_id ' ||
1151 ' ) MY_GRPS ' ||
1152 ' WHERE grpmemo.resource_id = dir.resource_id ' ||
1153 ' AND grpmemo.group_id = MY_GRPS.group_id ' ||
1154 ' UNION ALL ' ||
1155 ' SELECT dir.resource_id , ' ||
1156 ' grpmemo.group_id , ' ||
1157 ' dir.user_id CURRENT_USER_ID ' ||
1158 ' FROM jtf_rs_group_members grpmemo , ' ||
1159 ' jtf_rs_resource_extns dir , ' ||
1160 ' jtf_rs_group_usages usg ' ||
1161 ' WHERE usg.usage = ''SALES'' ' ||
1162 ' AND grpmemo.resource_id = dir.resource_id ' ||
1163 ' AND grpmemo.group_id = usg.group_id ' ||
1164 ' ) repdn1 ' ||
1165 ' where narsc1.resource_id = repdn1.resource_id ' ||
1166 ' and narsc1.rsc_group_id = repdn1.group_id ' ||
1167 ' and repdn1.current_user_id = :P_SALESPERSON ';
1168
1169 END IF;
1170
1171 IF ((trim(P_SALES_GROUP) IS NOT NULL) AND
1172 (trim(P_SALESPERSON) IS NULL) AND
1173 (trim(P_SALES_ROLE) IS NULL)) THEN
1174 l_na_query := l_na_query || ' and ga.terr_group_account_id in ( ';
1175 l_na_query := l_na_query ||
1176 ' select narsc1.terr_group_account_id '||
1177 ' from jtf_tty_named_acct_rsc narsc1, '||
1178 ' jtf_rs_group_members mem1, '||
1179 ' jtf_rs_groups_denorm grpdn1 '||
1180 ' where narsc1.resource_id = mem1.resource_id '||
1181 ' and narsc1.rsc_group_id = mem1.group_id '||
1182 ' and mem1.delete_flag = ''N'' '||
1183 ' and mem1.group_id = grpdn1.group_id '||
1184 ' and SYSDATE BETWEEN NVL(grpdn1.start_date_active, SYSDATE-1) '||
1185 ' AND NVL(grpdn1.end_date_active, SYSDATE+1) '||
1186 ' and grpdn1.parent_group_id = :P_SALES_GROUP ';
1187 END IF;
1188
1189 IF ((trim(P_SALES_ROLE) IS NOT NULL) AND
1190 (trim(P_SALESPERSON) IS NULL) AND
1191 (trim(P_SALES_GROUP) IS NULL)) THEN
1192 l_na_query := l_na_query || ' and EXISTS ( ';
1193 l_na_query := l_na_query ||
1194 ' select narsc1.terr_group_account_id '||
1195 ' from jtf_tty_named_acct_rsc narsc1, '||
1196 --' jtf_rs_rep_managers mgr, '||
1197 ' jtf_rs_rep_managers mgr1, '||
1198 ' jtf_rs_role_relations rlt, '||
1199 ' jtf_rs_roles_b rol '||
1200 ' where mgr1.resource_id = narsc1.resource_id '||
1201 ' and mgr1.group_id = narsc1.rsc_group_id '||
1202 ' and ga.terr_group_account_id = narsc1.terr_group_account_id ' ||
1203 -- ' and mgr1.parent_resource_id = mgr.resource_id '||
1204 ' and mgr1.par_role_relate_id = rlt.role_relate_id '||
1205 ' and rlt.role_id = rol.role_id '||
1206 ' and rlt.role_resource_type = ''RS_GROUP_MEMBER'' '||
1207 ' and rlt.delete_flag = ''N'' '||
1208 ' and SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE+1) '||
1209 ' and rol.role_code = :P_SALES_ROLE ';
1210 END IF;
1211
1212 IF ((trim(P_SALESPERSON) IS NOT NULL) AND
1213 (trim(P_SALES_GROUP) IS NOT NULL) AND
1214 (trim(P_SALES_ROLE) IS NULL)) THEN
1215 l_na_query := l_na_query || ' and ga.terr_group_account_id in ( ';
1216 l_na_query := l_na_query ||
1217 ' select narsc1.terr_group_account_id '||
1218 ' from jtf_tty_named_acct_rsc narsc1, '||
1219 ' jtf_tty_my_resources_v repdn1 '||
1220 ' where narsc1.resource_id = repdn1.resource_id '||
1221 ' and narsc1.rsc_group_id = repdn1.group_id '||
1222 ' and repdn1.current_user_id = :P_SALESPERSON '||
1223 ' and repdn1.parent_group_id = :P_SALES_GROUP ';
1224 END IF;
1225
1226 IF ((trim(P_SALESPERSON) IS NOT NULL) AND
1227 (trim(P_SALES_GROUP) IS NULL) AND
1231 ' select narsc1.terr_group_account_id '||
1228 (trim(P_SALES_ROLE) IS NOT NULL)) THEN
1229 l_na_query := l_na_query || ' and ga.terr_group_account_id in ( ';
1230 l_na_query := l_na_query ||
1232 ' from jtf_tty_named_acct_rsc narsc1, '||
1233 ' jtf_tty_my_resources_v repdn1 '||
1234 ' where narsc1.resource_id = repdn1.resource_id '||
1235 ' and narsc1.rsc_group_id = repdn1.group_id '||
1236 ' and repdn1.current_user_id = :P_SALESPERSON '||
1237 ' and repdn1.current_user_role_code = :P_SALES_ROLE ';
1238 END IF;
1239
1240 IF ((trim(P_SALESPERSON) IS NULL) AND
1241 (trim(P_SALES_GROUP) IS NOT NULL) AND
1242 (trim(P_SALES_ROLE) IS NOT NULL)) THEN
1243 l_na_query := l_na_query || ' and EXISTS ( ';
1244 l_na_query := l_na_query ||
1245 ' select narsc1.terr_group_account_id '||
1246 ' from jtf_tty_named_acct_rsc narsc1, '||
1247 -- ' jtf_rs_rep_managers mgr, '||
1248 ' jtf_rs_rep_managers mgr1, '||
1249 ' jtf_rs_role_relations rlt, '||
1250 ' jtf_rs_roles_b rol, '||
1251 ' jtf_rs_groups_denorm grpdn '||
1252 ' where mgr1.resource_id = narsc1.resource_id '||
1253 ' and mgr1.group_id = narsc1.rsc_group_id '||
1254 --' and mgr1.parent_resource_id = mgr.resource_id '||
1255 ' and ga.terr_group_account_id = narsc1.terr_group_account_id ' ||
1256 ' and mgr1.par_role_relate_id = rlt.role_relate_id '||
1257 ' and rlt.role_id = rol.role_id '||
1258 ' and rlt.role_resource_type = ''RS_GROUP_MEMBER'' '||
1259 ' and rlt.delete_flag = ''N'' '||
1260 ' and SYSDATE BETWEEN rlt.start_date_active AND NVL(rlt.end_date_active, SYSDATE+1) '||
1261 ' and rol.role_code = :P_SALES_ROLE '||
1262 ' and mgr.group_id = grpdn.group_id '||
1263 ' and grpdn.parent_group_id = :P_SALES_GROUP ';
1264 END IF;
1265
1266 IF ((trim(P_SALESPERSON) IS NOT NULL) AND
1267 (trim(P_SALES_GROUP) IS NOT NULL) AND
1268 (trim(P_SALES_ROLE) IS NOT NULL)) THEN
1269 l_na_query := l_na_query || ' and ga.terr_group_account_id in ( ';
1270 l_na_query := l_na_query ||
1271 ' select narsc1.terr_group_account_id '||
1272 ' from jtf_tty_named_acct_rsc narsc1, '||
1273 ' jtf_tty_my_resources_v repdn1 '||
1274 ' where narsc1.resource_id = repdn1.resource_id '||
1275 ' and narsc1.rsc_group_id = repdn1.group_id '||
1276 ' and repdn1.current_user_id = :P_SALESPERSON ' ||
1277 ' and repdn1.parent_group_id = :P_SALES_GROUP '||
1278 ' and repdn1.current_user_role_code = :P_SALES_ROLE ';
1279 END IF;
1280
1281 l_na_query := l_na_query || ') ';
1282 END IF; -- end of p_salessperson or p_selasgrp or p_selaesrole is not null
1283
1284 IF ((trim(P_SALESPERSON) IS NOT NULL) OR
1285 (trim(P_SALES_GROUP) IS NOT NULL) OR
1286 (trim(P_SALES_ROLE) IS NOT NULL)) THEN
1287
1288 l_na_query := l_na_query || ' ) ';
1289 l_na_query := l_na_query || ' WHERE ROWNUM <= ' || TO_CHAR(g_rows_limit + 1) || ';';
1290 l_na_query := l_na_query || ':X_ROWS_INSERTED := SQL%ROWCOUNT; ';
1291 l_na_query := l_na_query || ' end; ';
1292 ELSE
1293 l_na_query := l_na_query || ' AND ROWNUM <= ' || TO_CHAR(g_rows_limit + 1);
1294 l_na_query := l_na_query || ' ); ';
1295 l_na_query := l_na_query || ':X_ROWS_INSERTED := SQL%ROWCOUNT; ';
1296 l_na_query := l_na_query || ' end; ';
1297 END IF;
1298 /* end of the pl/sql block */
1299
1300 IF (CONTAINS_ONLY_PCTG(P_NAMED_ACCOUNT)) THEN
1301 P_NAMED_ACCOUNT_STR := UPPER(P_NAMED_ACCOUNT) || '%';
1302 END IF;
1303
1304 IF (CONTAINS_ONLY_PCTG(P_DU_NAME)) THEN
1305 P_DU_NAME_STR := UPPER(P_DU_NAME)||'%';
1306 END IF;
1307 IF (CONTAINS_ONLY_PCTG(P_GU_NAME)) THEN
1308 P_GU_NAME_STR := UPPER(P_GU_NAME)||'%';
1309 END IF;
1310
1311 commit;
1312
1313 --P(l_na_query);
1314
1315 /* execute the pl/sql block and commit */
1316 /* add all the param in order of declare */
1317 EXECUTE IMMEDIATE l_na_query USING P_USERID,P_GRPID,P_VIEW_DATE,P_ORG_ID,
1318 P_GRPNAME,P_SITE_TYPE,P_SICCODE,P_SITE_DUNS,
1319 P_NAMED_ACCOUNT_STR,P_CITY,P_STATE,P_PROVINCE,P_POSTAL_CODE_FROM,
1320 P_POSTAL_CODE_TO,P_COUNTRY,P_DU_DUNS,P_DU_NAME_STR,P_PARTY_NUMBER,P_GU_DUNS,
1321 P_GU_NAME_STR,P_CERT_LEVEL,P_SALESPERSON,P_SALES_GROUP,P_SALES_ROLE, P_PARTY_TYPE,
1322 P_HIERARCHY_TYPE, P_RELATIONSHIP_ROLE,P_CLASS_TYPE, P_CLASS_CODE,
1323 P_ANNUAL_REV_FROM, P_ANNUAL_REV_TO, P_NUM_EMP_FROM,P_NUM_EMP_TO,
1324 P_CUST_CATEGORY, P_IDADDR_FLAG , OUT X_ROWS_INSERTED;
1325
1326 COMMIT;
1327
1328
1329 --dbms_output.put_line('Rows inserted: '|| X_ROWS_INSERTED);
1330 /* NAs are populated, now start collect sales persons */
1331
1332 /* populate slots */
1333 i:=1;
1334
1335 FOR stat IN getStatistic(p_userid) LOOP
1336 IF i+stat.num-1 <=30 THEN
1337 FOR k IN i..i+stat.num-1 LOOP
1338 COL_ROLE(k) := stat.role_code;
1339 END LOOP;
1340 ELSE
1341 g_seq := 0;
1342 x_retcode := '-1';
1343 x_errbuf := 'More than 30 sales persons.';
1344 RETURN;
1345 END IF;
1346
1347 i:=i+stat.num;
1348 END LOOP;
1349
1350
1351 /* for each NA_ID */
1352 FOR m IN getNAFromInterface(p_userid) LOOP
1353
1357 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1354 /* clear col_used flags */
1355 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);
1356 RESOURCE_NAME :=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1358 GROUP_NAME :=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1359 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1360 ROLE_NAME :=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1361 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1362 RESOURCE_ATTRIBUTE1 :=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1363 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1364 RESOURCE_ATTRIBUTE2 :=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1365 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1366 RESOURCE_ATTRIBUTE3 :=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1367 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1368 RESOURCE_ATTRIBUTE4 :=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1369 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1370 RESOURCE_ATTRIBUTE5 :=VARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1371 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1372 RESOURCE_START_DATE :=DARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1373 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1374 RESOURCE_END_DATE :=DARRAY_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1375 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1376
1377 /* get all sales for this NA */
1378 FOR SALES IN getSalesperson(m.terr_grp_acct_id ) LOOP
1379
1380 FOR j IN 1..30 LOOP -- look into 30 slots
1381 IF SALES.role_code = COL_ROLE(j) AND COL_USED(j)=0 THEN
1382 COL_USED(j) :=1;
1383 RESOURCE_NAME(j):=SALES.resource_name;
1384 GROUP_NAME(j) :=SALES.group_name;
1385 ROLE_NAME(j) :=SALES.role_name;
1386 RESOURCE_ATTRIBUTE1(j) := SALES.ATTRIBUTE1;
1387 RESOURCE_ATTRIBUTE2(j) := SALES.ATTRIBUTE2;
1388 RESOURCE_ATTRIBUTE3(j) := SALES.ATTRIBUTE3;
1389 RESOURCE_ATTRIBUTE4(j) := SALES.ATTRIBUTE4;
1390 RESOURCE_ATTRIBUTE5(j) := SALES.ATTRIBUTE5;
1391 RESOURCE_START_DATE(j) := SALES.START_DATE;
1392 RESOURCE_END_DATE(j) := SALES.END_DATE;
1393
1394 EXIT;
1395 END IF;
1396 END LOOP; -- of slotting
1397 END LOOP; -- of SALES
1398
1399
1400 UPDATE JTF_TTY_WEBADI_INTERFACE -- /*+ INDEX JTF_TTY_WEBADI_INTF_N2 */
1401 SET RESOURCE1_NAME=RESOURCE_NAME(1),GROUP1_NAME=GROUP_NAME(1),ROLE1_NAME=ROLE_NAME(1),
1402 RESOURCE1_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(1), RESOURCE1_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(1),
1403 RESOURCE1_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(1), RESOURCE1_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(1),
1404 RESOURCE1_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(1),RESOURCE1_START_DATE=RESOURCE_START_DATE(1),RESOURCE1_END_DATE=RESOURCE_END_DATE(1),
1405 RESOURCE2_NAME=RESOURCE_NAME(2),GROUP2_NAME=GROUP_NAME(2),ROLE2_NAME=ROLE_NAME(2),
1406 RESOURCE2_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(2), RESOURCE2_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(2),
1407 RESOURCE2_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(2), RESOURCE2_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(2),
1408 RESOURCE2_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(2),RESOURCE2_START_DATE=RESOURCE_START_DATE(2),RESOURCE2_END_DATE=RESOURCE_END_DATE(2),
1409 RESOURCE3_NAME=RESOURCE_NAME(3),GROUP3_NAME=GROUP_NAME(3),ROLE3_NAME=ROLE_NAME(3),
1410 RESOURCE3_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(3), RESOURCE3_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(3),
1411 RESOURCE3_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(3), RESOURCE3_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(3),
1412 RESOURCE3_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(3),RESOURCE3_START_DATE=RESOURCE_START_DATE(3),RESOURCE3_END_DATE=RESOURCE_END_DATE(3),
1413 RESOURCE4_NAME=RESOURCE_NAME(4),GROUP4_NAME=GROUP_NAME(4),ROLE4_NAME=ROLE_NAME(4),
1414 RESOURCE4_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(4), RESOURCE4_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(4),
1415 RESOURCE4_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(4), RESOURCE4_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(4),
1416 RESOURCE4_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(4),RESOURCE4_START_DATE=RESOURCE_START_DATE(4),RESOURCE4_END_DATE=RESOURCE_END_DATE(4),
1417 RESOURCE5_NAME=RESOURCE_NAME(5),GROUP5_NAME=GROUP_NAME(5),ROLE5_NAME=ROLE_NAME(5),
1418 RESOURCE5_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(5), RESOURCE5_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(5),
1419 RESOURCE5_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(5), RESOURCE5_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(5),
1420 RESOURCE5_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(5),RESOURCE5_START_DATE=RESOURCE_START_DATE(5),RESOURCE5_END_DATE=RESOURCE_END_DATE(5),
1421 RESOURCE6_NAME=RESOURCE_NAME(6),GROUP6_NAME=GROUP_NAME(6),ROLE6_NAME=ROLE_NAME(6),
1422 RESOURCE6_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(6), RESOURCE6_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(6),
1423 RESOURCE6_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(6), RESOURCE6_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(6),
1424 RESOURCE6_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(6),RESOURCE6_START_DATE=RESOURCE_START_DATE(6),RESOURCE6_END_DATE=RESOURCE_END_DATE(6),
1425 RESOURCE7_NAME=RESOURCE_NAME(7),GROUP7_NAME=GROUP_NAME(7),ROLE7_NAME=ROLE_NAME(7),
1426 RESOURCE7_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(7), RESOURCE7_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(7),
1430 RESOURCE8_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(8), RESOURCE8_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(8),
1427 RESOURCE7_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(7), RESOURCE7_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(7),
1428 RESOURCE7_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(7),RESOURCE7_START_DATE=RESOURCE_START_DATE(7),RESOURCE7_END_DATE=RESOURCE_END_DATE(7),
1429 RESOURCE8_NAME=RESOURCE_NAME(8),GROUP8_NAME=GROUP_NAME(8),ROLE8_NAME=ROLE_NAME(8),
1431 RESOURCE8_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(8), RESOURCE8_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(8),
1432 RESOURCE8_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(8),RESOURCE8_START_DATE=RESOURCE_START_DATE(8),RESOURCE8_END_DATE=RESOURCE_END_DATE(8),
1433 RESOURCE9_NAME=RESOURCE_NAME(9),GROUP9_NAME=GROUP_NAME(9),ROLE9_NAME=ROLE_NAME(9),
1434 RESOURCE9_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(9), RESOURCE9_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(9),
1435 RESOURCE9_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(9), RESOURCE9_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(9),
1436 RESOURCE9_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(9),RESOURCE9_START_DATE=RESOURCE_START_DATE(9),RESOURCE9_END_DATE=RESOURCE_END_DATE(9),
1437 RESOURCE10_NAME=RESOURCE_NAME(10),GROUP10_NAME=GROUP_NAME(10),ROLE10_NAME=ROLE_NAME(10),
1438 RESOURCE10_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(10), RESOURCE10_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(10),
1439 RESOURCE10_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(10), RESOURCE10_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(10),
1440 RESOURCE10_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(10),RESOURCE10_START_DATE=RESOURCE_START_DATE(10),
1441 RESOURCE10_END_DATE=RESOURCE_END_DATE(10),
1442 RESOURCE11_NAME=RESOURCE_NAME(11),GROUP11_NAME=GROUP_NAME(11),ROLE11_NAME=ROLE_NAME(11),
1443 RESOURCE11_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(11), RESOURCE11_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(11),
1444 RESOURCE11_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(11), RESOURCE11_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(11),
1445 RESOURCE11_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(11),RESOURCE11_START_DATE=RESOURCE_START_DATE(11),
1446 RESOURCE11_END_DATE=RESOURCE_END_DATE(11),
1447 RESOURCE12_NAME=RESOURCE_NAME(12),GROUP12_NAME=GROUP_NAME(12),ROLE12_NAME=ROLE_NAME(12),
1448 RESOURCE12_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(12), RESOURCE12_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(12),
1449 RESOURCE12_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(12), RESOURCE12_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(12),
1450 RESOURCE12_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(12),RESOURCE12_START_DATE=RESOURCE_START_DATE(12),
1451 RESOURCE12_END_DATE=RESOURCE_END_DATE(12),
1452 RESOURCE13_NAME=RESOURCE_NAME(13),GROUP13_NAME=GROUP_NAME(13),ROLE13_NAME=ROLE_NAME(13),
1453 RESOURCE13_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(13), RESOURCE13_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(13),
1454 RESOURCE13_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(13), RESOURCE13_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(13),
1455 RESOURCE13_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(13),RESOURCE13_START_DATE=RESOURCE_START_DATE(13),
1456 RESOURCE13_END_DATE=RESOURCE_END_DATE(13),
1457 RESOURCE14_NAME=RESOURCE_NAME(14),GROUP14_NAME=GROUP_NAME(14),ROLE14_NAME=ROLE_NAME(14),
1458 RESOURCE14_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(14), RESOURCE14_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(14),
1459 RESOURCE14_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(14), RESOURCE14_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(14),
1460 RESOURCE14_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(14),RESOURCE14_START_DATE=RESOURCE_START_DATE(14),
1461 RESOURCE14_END_DATE=RESOURCE_END_DATE(14),
1462 RESOURCE15_NAME=RESOURCE_NAME(15),GROUP15_NAME=GROUP_NAME(15),ROLE15_NAME=ROLE_NAME(15),
1463 RESOURCE15_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(15), RESOURCE15_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(15),
1464 RESOURCE15_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(15), RESOURCE15_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(15),
1465 RESOURCE15_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(15),RESOURCE15_START_DATE=RESOURCE_START_DATE(15),
1466 RESOURCE15_END_DATE=RESOURCE_END_DATE(15),
1467 RESOURCE16_NAME=RESOURCE_NAME(16),GROUP16_NAME=GROUP_NAME(16),ROLE16_NAME=ROLE_NAME(16),
1468 RESOURCE16_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(16), RESOURCE16_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(16),
1469 RESOURCE16_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(16), RESOURCE16_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(16),
1470 RESOURCE16_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(16),RESOURCE16_START_DATE=RESOURCE_START_DATE(16),
1471 RESOURCE16_END_DATE=RESOURCE_END_DATE(16),
1472 RESOURCE17_NAME=RESOURCE_NAME(17),GROUP17_NAME=GROUP_NAME(17),ROLE17_NAME=ROLE_NAME(17),
1473 RESOURCE17_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(17), RESOURCE17_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(17),
1474 RESOURCE17_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(17), RESOURCE17_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(17),
1475 RESOURCE17_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(17),RESOURCE17_START_DATE=RESOURCE_START_DATE(17),
1476 RESOURCE17_END_DATE=RESOURCE_END_DATE(17),
1477 RESOURCE18_NAME=RESOURCE_NAME(18),GROUP18_NAME=GROUP_NAME(18),ROLE18_NAME=ROLE_NAME(18),
1478 RESOURCE18_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(18), RESOURCE18_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(18),
1479 RESOURCE18_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(18), RESOURCE18_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(18),
1480 RESOURCE18_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(18),RESOURCE18_START_DATE=RESOURCE_START_DATE(18),
1481 RESOURCE18_END_DATE=RESOURCE_END_DATE(18),
1482 RESOURCE19_NAME=RESOURCE_NAME(19),GROUP19_NAME=GROUP_NAME(19),ROLE19_NAME=ROLE_NAME(19),
1483 RESOURCE19_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(19), RESOURCE19_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(19),
1484 RESOURCE19_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(19), RESOURCE19_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(19),
1485 RESOURCE19_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(19),RESOURCE19_START_DATE=RESOURCE_START_DATE(19),
1486 RESOURCE19_END_DATE=RESOURCE_END_DATE(19),
1487 RESOURCE20_NAME=RESOURCE_NAME(20),GROUP20_NAME=GROUP_NAME(20),ROLE20_NAME=ROLE_NAME(20),
1488 RESOURCE20_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(20), RESOURCE20_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(20),
1489 RESOURCE20_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(20), RESOURCE20_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(20),
1490 RESOURCE20_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(20),RESOURCE20_START_DATE=RESOURCE_START_DATE(20),
1491 RESOURCE20_END_DATE=RESOURCE_END_DATE(20),
1495 RESOURCE21_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(21),RESOURCE21_START_DATE=RESOURCE_START_DATE(21),
1492 RESOURCE21_NAME=RESOURCE_NAME(21),GROUP21_NAME=GROUP_NAME(21),ROLE21_NAME=ROLE_NAME(21),
1493 RESOURCE21_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(21), RESOURCE21_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(21),
1494 RESOURCE21_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(21), RESOURCE21_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(21),
1496 RESOURCE21_END_DATE=RESOURCE_END_DATE(21),
1497 RESOURCE22_NAME=RESOURCE_NAME(22),GROUP22_NAME=GROUP_NAME(22),ROLE22_NAME=ROLE_NAME(22),
1498 RESOURCE22_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(22), RESOURCE22_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(22),
1499 RESOURCE22_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(22), RESOURCE22_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(22),
1500 RESOURCE22_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(22),RESOURCE22_START_DATE=RESOURCE_START_DATE(22),
1501 RESOURCE22_END_DATE=RESOURCE_END_DATE(22),
1502 RESOURCE23_NAME=RESOURCE_NAME(23),GROUP23_NAME=GROUP_NAME(23),ROLE23_NAME=ROLE_NAME(23),
1503 RESOURCE23_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(23), RESOURCE23_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(23),
1504 RESOURCE23_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(23), RESOURCE23_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(23),
1505 RESOURCE23_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(23),RESOURCE23_START_DATE=RESOURCE_START_DATE(23),
1506 RESOURCE23_END_DATE=RESOURCE_END_DATE(23),
1507 RESOURCE24_NAME=RESOURCE_NAME(24),GROUP24_NAME=GROUP_NAME(24),ROLE24_NAME=ROLE_NAME(24),
1508 RESOURCE24_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(24), RESOURCE24_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(24),
1509 RESOURCE24_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(24), RESOURCE24_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(24),
1510 RESOURCE24_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(24),RESOURCE24_START_DATE=RESOURCE_START_DATE(24),
1511 RESOURCE24_END_DATE=RESOURCE_END_DATE(24),
1512 RESOURCE25_NAME=RESOURCE_NAME(25),GROUP25_NAME=GROUP_NAME(25),ROLE25_NAME=ROLE_NAME(25),
1513 RESOURCE25_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(25), RESOURCE25_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(25),
1514 RESOURCE25_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(25), RESOURCE25_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(25),
1515 RESOURCE25_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(25),RESOURCE25_START_DATE=RESOURCE_START_DATE(25),
1516 RESOURCE25_END_DATE=RESOURCE_END_DATE(25),
1517 RESOURCE26_NAME=RESOURCE_NAME(26),GROUP26_NAME=GROUP_NAME(26),ROLE26_NAME=ROLE_NAME(26),
1518 RESOURCE26_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(26), RESOURCE26_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(26),
1519 RESOURCE26_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(26), RESOURCE26_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(26),
1520 RESOURCE26_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(26),RESOURCE26_START_DATE=RESOURCE_START_DATE(26),
1521 RESOURCE26_END_DATE=RESOURCE_END_DATE(26),
1522 RESOURCE27_NAME=RESOURCE_NAME(27),GROUP27_NAME=GROUP_NAME(27),ROLE27_NAME=ROLE_NAME(27),
1523 RESOURCE27_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(27), RESOURCE27_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(27),
1524 RESOURCE27_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(27), RESOURCE27_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(27),
1525 RESOURCE27_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(27),RESOURCE27_START_DATE=RESOURCE_START_DATE(27),
1526 RESOURCE27_END_DATE=RESOURCE_END_DATE(27),
1527 RESOURCE28_NAME=RESOURCE_NAME(28),GROUP28_NAME=GROUP_NAME(28),ROLE28_NAME=ROLE_NAME(28),
1528 RESOURCE28_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(28), RESOURCE28_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(28),
1529 RESOURCE28_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(28), RESOURCE28_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(28),
1530 RESOURCE28_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(28),RESOURCE28_START_DATE=RESOURCE_START_DATE(28),
1531 RESOURCE28_END_DATE=RESOURCE_END_DATE(28),
1532 RESOURCE29_NAME=RESOURCE_NAME(29),GROUP29_NAME=GROUP_NAME(29),ROLE29_NAME=ROLE_NAME(29),
1533 RESOURCE29_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(29), RESOURCE29_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(29),
1534 RESOURCE29_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(29), RESOURCE29_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(29),
1535 RESOURCE29_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(29),RESOURCE29_START_DATE=RESOURCE_START_DATE(29),
1536 RESOURCE29_END_DATE=RESOURCE_END_DATE(29),
1537 RESOURCE30_NAME=RESOURCE_NAME(30),GROUP30_NAME=GROUP_NAME(30),ROLE30_NAME=ROLE_NAME(30),
1538 RESOURCE30_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(30), RESOURCE30_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(30),
1539 RESOURCE30_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(30), RESOURCE30_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(30),
1540 RESOURCE30_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(30),RESOURCE30_START_DATE=RESOURCE_START_DATE(30),
1541 RESOURCE30_END_DATE=RESOURCE_END_DATE(30)
1542 WHERE user_id = p_userid
1543 AND TERR_GRP_ACCT_ID =m.TERR_GRP_ACCT_ID;
1544
1545 END LOOP; -- of NA
1546
1547 COMMIT;
1548
1549 EXCEPTION
1550 WHEN OTHERS THEN
1551 RAISE;
1552 END POPULATE_INTERFACE_FOR_NA;
1553
1554
1555 PROCEDURE POPULATE_WEBADI_INTERFACE( P_CALLFROM IN VARCHAR2
1556 ,P_SEARCHTYPE IN VARCHAR2
1557 ,P_SEARCHVALUE IN VARCHAR2
1558 ,P_USERID IN INTEGER
1559 ,P_GRPNAME IN VARCHAR2
1560 ,P_GRPID IN NUMBER
1561 ,P_SITE_TYPE IN VARCHAR2
1562 ,P_SICCODE IN VARCHAR2
1563 ,P_SICCODE_TYPE IN VARCHAR2 DEFAULT NULL
1564 ,P_SITE_DUNS IN VARCHAR2
1565 ,P_NAMED_ACCOUNT IN VARCHAR2
1566 ,P_WEB_SITE IN VARCHAR2 DEFAULT NULL
1567 ,P_EMAIL_ADDR IN VARCHAR2 DEFAULT NULL
1568 ,P_CITY IN VARCHAR2
1569 ,P_STATE IN VARCHAR2
1570 ,P_COUNTY IN VARCHAR2 DEFAULT NULL
1571 ,P_PROVINCE IN VARCHAR2
1572 ,P_POSTAL_CODE_FROM IN VARCHAR2
1573 ,P_POSTAL_CODE_TO IN VARCHAR2
1574 ,P_COUNTRY IN VARCHAR2
1575 ,P_DU_DUNS IN VARCHAR2
1576 ,P_DU_NAME IN VARCHAR2
1577 ,P_PARTY_NUMBER IN VARCHAR2
1578 ,P_GU_DUNS IN VARCHAR2
1579 ,P_GU_NAME IN VARCHAR2
1580 ,P_CERT_LEVEL IN VARCHAR2
1581 ,P_SALESPERSON IN NUMBER
1582 ,P_SALES_GROUP IN NUMBER
1583 ,P_SALES_ROLE IN VARCHAR2
1584 ,P_ASSIGNED_STATUS IN VARCHAR2
1585 ,P_ISADMINFLAG IN VARCHAR2
1586 ,P_PARTY_TYPE IN VARCHAR2 DEFAULT NULL
1587 ,P_HIERARCHY_TYPE IN VARCHAR2 DEFAULT NULL
1588 ,P_RELATIONSHIP_ROLE IN VARCHAR2 DEFAULT NULL
1589 ,P_CLASS_TYPE IN VARCHAR2 DEFAULT NULL
1590 ,P_CLASS_CODE IN VARCHAR2 DEFAULT NULL
1591 ,P_ANN_REV_FROM IN NUMBER DEFAULT NULL
1592 ,P_ANN_REV_TO IN NUMBER DEFAULT NULL
1593 ,P_NUM_EMP_FROM IN VARCHAR2 DEFAULT NULL
1594 ,P_NUM_EMP_TO IN VARCHAR2 DEFAULT NULL
1595 ,P_CUST_CATEGORY IN VARCHAR2 DEFAULT NULL
1596 ,P_IDENT_ADDR_FLAG IN VARCHAR2 DEFAULT NULL
1597 ,P_MAPPED_STATUS IN VARCHAR2 DEFAULT NULL
1598 ,P_VIEW_DATE IN DATE DEFAULT NULL -- added 07/20/2006
1599 ,P_ORG_ID IN NUMBER DEFAULT NULL -- added 07/20/2006
1600 ,X_SEQ OUT NOCOPY VARCHAR2
1601 ,X_RETCODE OUT NOCOPY VARCHAR2
1602 ,X_ERRBUF OUT NOCOPY VARCHAR2) IS
1603
1604 l_rows_inserted NUMBER;
1605
1606 BEGIN
1607
1608 /* Initialize the out and loclal variables */
1609 X_SEQ := 0;
1610 X_RETCODE := 0;
1611 X_ERRBUF := NULL;
1612 l_rows_inserted := 0;
1613
1614 /* Get the next sequence number of the table jtf_tty_webadi_interface */
1615 BEGIN
1616 SELECT jtf_tty_interface_s.NEXTVAL
1617 INTO g_seq
1618 FROM DUAL;
1619
1620 EXCEPTION
1621 WHEN OTHERS THEN
1622 RAISE G_SEQUENCE_ERROR;
1623 END;
1624
1625 /* Get the meaning for no lookup code */
1626 BEGIN
1627 SELECT MEANING
1628 INTO g_no_lookup
1629 FROM FND_LOOKUPS
1630 WHERE lookup_type = 'JTF_TERR_FLAGS'
1631 AND lookup_code = 'N';
1632
1633 EXCEPTION
1634 WHEN OTHERS THEN
1635 RAISE G_NO_LOOKUP_MISSING;
1636 END;
1637
1638 /* Delete the existing data for the user from the interface table */
1639 BEGIN
1640 DELETE /*+ index(tty jtf_tty_webadi_intf_n1) */
1641 FROM JTF_TTY_WEBADI_INTERFACE tty
1642 WHERE tty.user_id = TO_NUMBER(p_userid);
1643
1644 COMMIT;
1645
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 RAISE G_DELETE_ERROR;
1649 END;
1650
1651 /* check to see if admin is initiating the download */
1652 IF (p_callfrom = 'ADMIN') THEN
1653 /* check to see if the export object type is named account (NA) or organization (ORG) */
1654 IF (p_searchtype = 'NA') THEN
1655
1656 -- IF (trim(P_GRPID) IS NOT NULL) THEN
1657 POPULATE_INTERFACE_FOR_NA( P_USERID
1658 ,P_GRPID
1659 ,P_SITE_TYPE
1660 ,P_SICCODE
1661 ,P_SITE_DUNS
1662 ,P_NAMED_ACCOUNT
1663 ,P_CITY
1664 ,P_STATE
1665 ,P_PROVINCE
1666 ,P_POSTAL_CODE_FROM
1667 ,P_POSTAL_CODE_TO
1668 ,P_COUNTRY
1669 ,P_DU_DUNS
1670 ,P_DU_NAME
1671 ,P_PARTY_NUMBER
1672 ,P_GU_DUNS
1673 ,P_GU_NAME
1674 ,P_CERT_LEVEL
1675 ,P_SALESPERSON
1676 ,P_SALES_GROUP
1677 ,P_SALES_ROLE
1678 ,P_PARTY_TYPE
1679 ,P_HIERARCHY_TYPE
1680 ,P_RELATIONSHIP_ROLE
1681 ,P_CLASS_TYPE
1682 ,P_CLASS_CODE
1683 ,P_ANN_REV_FROM
1684 ,P_ANN_REV_TO
1685 ,P_NUM_EMP_FROM
1686 ,P_NUM_EMP_TO
1687 ,P_CUST_CATEGORY
1688 ,P_IDENT_ADDR_FLAG
1689 ,P_SICCODE_TYPE
1690 ,P_GRPNAME
1691 ,P_VIEW_DATE
1692 ,P_ORG_ID
1693 ,L_ROWS_INSERTED
1694 ,X_RETCODE
1695 ,X_ERRBUF);
1696 -- ELSE
1697 -- RAISE G_TERRGRP_MISSING;
1698 -- END IF;
1699 ELSIF (p_searchtype = 'ORG') THEN
1700 POPULATE_INTERFACE_FOR_ORG( P_USERID
1701 ,P_SICCODE
1702 ,P_SICCODE_TYPE
1703 ,P_SITE_DUNS
1704 ,P_NAMED_ACCOUNT
1705 ,P_WEB_SITE
1706 ,P_EMAIL_ADDR
1707 ,P_CITY
1708 ,P_STATE
1709 ,P_COUNTY
1710 ,P_PROVINCE
1711 ,P_POSTAL_CODE_FROM
1712 ,P_POSTAL_CODE_TO
1713 ,P_COUNTRY
1714 ,P_PARTY_NUMBER
1715 ,P_CERT_LEVEL
1716 ,P_PARTY_TYPE
1717 ,P_HIERARCHY_TYPE
1718 ,P_RELATIONSHIP_ROLE
1719 ,P_CLASS_TYPE
1720 ,P_CLASS_CODE
1721 ,P_ANN_REV_FROM
1722 ,P_ANN_REV_TO
1723 ,P_NUM_EMP_FROM
1724 ,P_NUM_EMP_TO
1725 ,P_CUST_CATEGORY
1726 ,P_IDENT_ADDR_FLAG
1727 ,L_ROWS_INSERTED
1728 ,X_RETCODE
1729 ,X_ERRBUF);
1730 END IF;
1731 END IF;
1732
1733 COMMIT;
1734
1735 /* check to see if more than 25000 records have been inserted in the interface table */
1736 /* if yes , set retcode to 2 (warning) and errbuf to appropiate error message */
1737 IF (l_rows_inserted > g_rows_limit) THEN
1738 x_retcode := -2;
1739 x_errbuf := 'More than ' || g_rows_limit || ' records have been retrieved';
1740 END IF;
1741
1742 x_seq := TO_CHAR(g_seq);
1743
1744 EXCEPTION
1745 WHEN G_SEQUENCE_ERROR THEN
1746 x_retcode := -1;
1747 x_errbuf := 'Error in generating the sequence number jtf_tty_interface_s. SQLCODE : ' || SQLCODE ||
1748 ' SQLERRM : ' || SQLERRM;
1749 x_seq := 0;
1750 RETURN;
1751
1752 WHEN G_NO_LOOKUP_MISSING THEN
1753 x_retcode := -1;
1754 x_errbuf := 'Error in getting the meaning for the lookup code = N and lookup type = JTF_TERR_FLAGS : ' || SQLCODE ||
1755 ' SQLERRM : ' || SQLERRM;
1756 x_seq := 0;
1757 RETURN;
1758
1759 WHEN G_DELETE_ERROR THEN
1760 x_retcode := -1;
1761 x_errbuf := 'Error in deleting the existing data for the user from the interface table. SQLCODE : ' || SQLCODE ||
1762 ' SQLERRM : ' || SQLERRM;
1763 x_seq := 0;
1764 RETURN;
1765 /*
1766 WHEN G_TERRGRP_MISSING THEN
1767 x_retcode := -1;
1768 x_errbuf := 'Territory Group must be specified while downloading named accounts.';
1769 x_seq := 0;
1770 RETURN;
1771 */
1772 WHEN OTHERS THEN
1773 x_retcode := -1;
1774 x_errbuf := 'SQLCODE : ' || SQLCODE || ' SQLERRM : ' || SQLERRM;
1775 x_seq := 0;
1776 RETURN;
1777 END POPULATE_WEBADI_INTERFACE;
1778
1779 END JTF_TTY_EXCEL_NAORG_PVT;