[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_MAINTAIN_NA_PVT
Source
1 PACKAGE BODY JTF_TTY_MAINTAIN_NA_PVT AS
2 /* $Header: jtftmnab.pls 120.19 2011/07/19 05:12:07 nbombili ship $ */
3 -- Start of Comments
4 -- PURPOSE
5 -- For handling Admin Excel Export functionalities like Add Org To
6 -- a territory group, update sales team, transfer
7 -- NOTES
8 -- ORACLE INTERNAL USE ONLY: NOT for customer use
9 --
10 -- HISTORY
11 -- 02/27/04 VXSRINIV Created
12 -- 03/09/04 SGKUMAR Modified get_terr_grp_details to set the x_return
13 -- _status appropriately.
14 -- Modified assign_ua_acct_to_tgowners to use correct
15 -- data type for role code.
16 -- 03/12/04 SGKUMAR if party number is missing, give error message
17 -- 03/25/04 SGKUMAR Bug 3532370 fixed in update sales team API
18 -- 02/28/05 SHLI GSST decom
19 -- End of Comments
20 /* procedure to log error messages
21 *
22 */
23 PROCEDURE put_jty_log(p_text VARCHAR2, p_module_name VARCHAR2, p_severity NUMBER) IS
24 l_len number;
25 l_start number := 1;
26 l_end number := 1;
27 last_reached boolean := false;
28 BEGIN
29 if (p_text is null or p_text='' or FND_LOG.G_CURRENT_RUNTIME_LEVEL < FND_LOG.LEVEL_STATEMENT)
30 then
31 return;
32 end if;
33
34 l_len:=nvl(length(p_text),0);
35
36 if l_len <= 0 then
37 return;
38 end if;
39
40 while true loop
41 l_end:=l_start+250;
42
43 if l_end >= l_len then
44 l_end:=l_len;
45 last_reached:=true;
46 end if;
47 -- hard coding as it is giving GSCC warning
48 -- for now it is ok as only unexpected errors are generated
49 if( p_severity >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
50 FND_LOG.STRING(p_severity, p_module_name, substr(p_text, l_start, 250));
51 end if;
52 l_start:=l_start+250;
53 if last_reached then
54 exit;
55 end if;
56 end loop;
57
58 END put_jty_log;
59
60 --This procedure retrieves the Territory_Group_Account_Id, Territory_Group_Id
61 --and Named_Account_Id after validating the party and the territory_group.
62 PROCEDURE GET_TERR_GRP_ACCT_DETAILS(
63 P_API_VERSION_NUMBER IN NUMBER,
64 P_INIT_MSG_LIST IN VARCHAR2,
65 P_COMMIT IN VARCHAR2,
66 P_VALIDATION_LEVEL IN NUMBER,
67 P_PARTY_NUMBER IN VARCHAR2,
68 P_PARTY_SITE_ID IN NUMBER,
69 P_TERR_GRP_NAME IN VARCHAR2,
70 P_ATTRIBUTE1 IN VARCHAR2,
71 P_ATTRIBUTE2 IN VARCHAR2,
72 P_ATTRIBUTE3 IN VARCHAR2,
73 P_ATTRIBUTE4 IN VARCHAR2,
74 P_ATTRIBUTE5 IN VARCHAR2,
75 P_ATTRIBUTE6 IN VARCHAR2,
76 P_ATTRIBUTE7 IN VARCHAR2,
77 P_ATTRIBUTE8 IN VARCHAR2,
78 P_ATTRIBUTE9 IN VARCHAR2,
79 P_ATTRIBUTE10 IN VARCHAR2,
80 P_ATTRIBUTE11 IN VARCHAR2,
81 P_ATTRIBUTE12 IN VARCHAR2,
82 P_ATTRIBUTE13 IN VARCHAR2,
83 P_ATTRIBUTE14 IN VARCHAR2,
84 P_ATTRIBUTE15 IN VARCHAR2,
85 P_START_DATE IN OUT NOCOPY DATE,
86 P_END_DATE IN OUT NOCOPY DATE,
87 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
88 X_MSG_COUNT OUT NOCOPY NUMBER,
89 X_MSG_DATA OUT NOCOPY VARCHAR2,
90 X_TERR_GRP_ID OUT NOCOPY NUMBER,
91 X_TERR_GRP_ACCT_ID OUT NOCOPY NUMBER,
92 X_NAMED_ACCT_ID OUT NOCOPY NUMBER) IS
93
94 l_party_number VARCHAR2(30);
95 l_tg_name VARCHAR2(150);
96 l_party_id NUMBER;
97 l_tg_id NUMBER;
98 l_na_id NUMBER;
99 l_tga_id NUMBER;
100 l_error_msg VARCHAR2(250);
101 l_tg_start_date date;
102 l_tg_end_date date;
103
104 --Cursor to validate party based on party number : Single Row
105 CURSOR c_validate_party(l_party_number VARCHAR2, l_party_site_id NUMBER) IS
106 SELECT hzp.party_id
107 FROM hz_parties hzp
108 , hz_party_sites hzps
109 WHERE hzp.party_number = l_party_number
110 AND hzp.party_id = hzps.party_id
111 and hzps.party_site_id = l_party_site_id
112 AND hzp.status = 'A';
113 -- AND hzp.party_type = 'ORGANIZATION';
114
115
116 --Cursor to validate territory group based on TG name : Single Row
117 --Note: Joining with jtf_terr to stripe data by org
118 --Note: Possible to have multiple TGs with same name, hence using ROWNUM
119 CURSOR c_validate_tg(l_tg_name VARCHAR2) IS
120 SELECT a.terr_group_id,
121 a.active_from_date,
122 NVL( a.active_to_date, ADD_MONTHS(a.active_from_date,120) ) active_to_date
123 FROM jtf_tty_terr_groups a, jtf_terr_all b
124 WHERE a.parent_terr_id = b.terr_id
125 AND upper(a.terr_group_name) = upper(l_tg_name)
126 -- and b.org_id = FND_PROFILE.VALUE('ORG_ID')
127 AND a.self_service_type = 'NAMED_ACCOUNT'
128 AND a.active_from_date <= sysdate
129 AND (a.active_to_date is null or a.active_to_date >= sysdate)
130 AND rownum < 2;
131
132 --Cursor to get NA id and TGA id for specified party and terr group: Single Row
133 CURSOR c_get_all_ids(l_party_id NUMBER, l_party_site_id NUMBER, l_tg_id NUMBER) IS
134 SELECT na.named_account_id, tga.terr_group_account_id
135 FROM jtf_tty_named_accts na, jtf_tty_terr_grp_accts tga
136 WHERE na.party_id = l_party_id
137 AND na.party_site_id = l_party_site_id
138 AND tga.named_account_id = na.named_account_id
139 AND tga.terr_group_id = l_tg_id;
140
141 BEGIN
142
143 l_party_number := nvl(P_PARTY_NUMBER, -999);
144 OPEN c_validate_party(l_party_number, p_party_site_id);
145 FETCH c_validate_party INTO l_party_id;
146
147 --If party is not valid then set message and return
148 IF c_validate_party%NOTFOUND THEN
149 CLOSE c_validate_party;
150 x_terr_grp_id := -999;
151 x_terr_grp_acct_id := -999;
152 x_named_acct_id := -999;
153 x_return_status := fnd_api.g_ret_sts_error;
154 fnd_message.clear;
155 fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_PARTY');
156 return;
157 END IF;
158 CLOSE c_validate_party;
159
160 l_tg_name := nvl(P_TERR_GRP_NAME, -999);
161 OPEN c_validate_tg(l_tg_name);
162 FETCH c_validate_tg INTO l_tg_id, l_tg_start_date, l_tg_end_date;
163
164 --If TG is not valid then set message and return
165 IF c_validate_tg%NOTFOUND THEN
166 CLOSE c_validate_tg;
167 x_terr_grp_id := -999;
168 x_terr_grp_acct_id := -999;
169 x_named_acct_id := -999;
170 x_return_status := fnd_api.g_ret_sts_error;
171 fnd_message.clear;
172 fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_FROM_TG');
173 return;
174 END IF;
175 CLOSE c_validate_tg;
176
177 -- added 06/05/2006 bug 5246668, to validate and set date
178 IF p_start_date is null THEN
179 IF (TRUNC(SYSDATE) > l_tg_start_date) THEN
180 p_start_date := TRUNC(SYSDATE);
181 ELSE
182 p_start_date := l_tg_start_date;
183 END IF;
184 ELSE
185 IF (p_start_date < l_tg_start_date) THEN
186 x_return_status := fnd_api.g_ret_sts_error;
187 fnd_message.clear;
188 fnd_message.set_name ('JTF', 'JTF_TERR_STARTDATE_NOT_VALID');
189 return;
190 END IF;
191 END IF;
192
193 IF p_end_date is null THEN
194 IF (ADD_MONTHS(NVL(P_START_DATE,TRUNC(SYSDATE)), 12) > l_tg_end_date) THEN
195 p_end_date := l_tg_end_date;
196 ELSE
197 p_end_date := ADD_MONTHS(NVL(P_START_DATE,TRUNC(SYSDATE)), 12);
198 END IF;
199 ELSE
200 IF (p_end_date < l_tg_start_date OR p_end_date > l_tg_end_date) THEN
201 x_return_status := fnd_api.g_ret_sts_error;
202 fnd_message.clear;
203 fnd_message.set_name ('JTF', 'JTF_TERR_ENDDATE_NOT_VALID');
204 return;
205 END IF;
206 END IF;
207
208 -- end for bug 5246668, by mhtran
209
210 --If TG and Party are valid then get TGA and NA ids
211 OPEN c_get_all_ids(l_party_id, p_party_site_id, l_tg_id);
212 FETCH c_get_all_ids into l_na_id, l_tga_id;
213
214 --If matching NA and TGA ids do not exist then set message and return
215 IF c_get_all_ids%NOTFOUND THEN
216 CLOSE c_get_all_ids;
217 x_terr_grp_id := -999;
218 x_terr_grp_acct_id := -999;
219 x_named_acct_id := -999;
220 x_return_status := fnd_api.g_ret_sts_error;
221 fnd_message.clear;
222 fnd_message.set_name ('JTF', 'JTF_TTY_NO_TGA_MATCH');
223 return;
224 END IF;
225 CLOSE c_get_all_ids;
226
227 -- added 06/05/2006 bug 5246668, update attributes and date
228
229 update jtf_tty_terr_grp_accts
230 set ATTRIBUTE1 = P_ATTRIBUTE1,
231 ATTRIBUTE2 = P_ATTRIBUTE2,
232 ATTRIBUTE3 = P_ATTRIBUTE3,
233 ATTRIBUTE4 = P_ATTRIBUTE4,
234 ATTRIBUTE5 = P_ATTRIBUTE5,
235 ATTRIBUTE6 = P_ATTRIBUTE6,
236 ATTRIBUTE7 = P_ATTRIBUTE7,
237 ATTRIBUTE8 = P_ATTRIBUTE8,
238 ATTRIBUTE9 = P_ATTRIBUTE9,
239 ATTRIBUTE10 = P_ATTRIBUTE10,
240 ATTRIBUTE11 = P_ATTRIBUTE11,
241 ATTRIBUTE12 = P_ATTRIBUTE12,
242 ATTRIBUTE13 = P_ATTRIBUTE13,
243 ATTRIBUTE14 = P_ATTRIBUTE14,
244 ATTRIBUTE15 = P_ATTRIBUTE15,
245 START_DATE = p_start_date,
246 END_DATE = p_end_date
247 where terr_group_account_id = l_tga_id
248 and terr_group_id = l_tg_id;
249
250 x_terr_grp_id := l_tg_id;
251 x_terr_grp_acct_id := l_tga_id;
252 x_named_acct_id := l_na_id;
253 x_return_status := fnd_api.g_ret_sts_success;
254
255 EXCEPTION
256 WHEN OTHERS THEN
257 l_error_msg := substr(sqlerrm,1,200);
258 fnd_message.clear;
259 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
260 fnd_message.set_token('ERRMSG', l_error_msg );
261 put_jty_log('Error in GET_TERR_GRP_ACCT_DETAILS: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
262 return;
263 END GET_TERR_GRP_ACCT_DETAILS;
264
265 --This procedure deletes a named account from a territory group
266 PROCEDURE DELETE_ACCT_FROM_TG(
267 P_API_VERSION_NUMBER IN NUMBER,
268 P_INIT_MSG_LIST IN VARCHAR2,
269 P_COMMIT IN VARCHAR2,
270 P_VALIDATION_LEVEL IN NUMBER,
271 P_TERR_GRP_ACCT_ID IN NUMBER,
272 P_TERR_GRP_ID IN NUMBER,
273 P_NAMED_ACCT_ID IN NUMBER,
274 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
275 X_MSG_COUNT OUT NOCOPY NUMBER,
276 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
277
278 l_user_id NUMBER;
279 l_login_id NUMBER;
280 l_error_msg VARCHAR2(250);
281
282 BEGIN
283
284 --Delete resource assignment for the TG account
285 DELETE from jtf_tty_named_acct_rsc
286 WHERE terr_group_account_id = p_terr_grp_acct_id;
287
288 --Delete TG account
289 DELETE from JTF_TTY_TERR_GRP_ACCTS
290 WHERE terr_group_account_id = p_terr_grp_acct_id;
291
292 --Delete this named account if it has no other TG references
293
294 DELETE from JTF_TTY_NAMED_ACCTS
295 WHERE named_account_id = P_NAMED_ACCT_ID
296 AND NOT EXISTS (SELECT named_account_id
297 from JTF_TTY_TERR_GRP_ACCTS a
298 where a.named_account_id = P_NAMED_ACCT_ID);
299
300 --Delete the NA mappings if an NA is deleted or no reference to it exists
301
302 DELETE from JTF_TTY_ACCT_QUAL_MAPS
303 WHERE named_account_id = P_NAMED_ACCT_ID
304 AND NOT EXISTS (SELECT named_account_id
305 from JTF_TTY_NAMED_ACCTS a
306 where a.named_account_id = P_NAMED_ACCT_ID); --Delete named account if it has no other TG references
307
308 l_user_id := fnd_global.user_id;
309 l_login_id := fnd_global.login_id;
310
311 /* shli GSST Decom */
312 /*--Insert row to track changes for GTP
313 INSERT INTO jtf_tty_named_acct_changes
314 (NAMED_ACCT_CHANGE_ID,
315 OBJECT_VERSION_NUMBER,
316 OBJECT_TYPE,
317 OBJECT_ID,
318 CHANGE_TYPE,
319 FROM_WHERE,
320 CREATED_BY,
321 CREATION_DATE,
322 LAST_UPDATED_BY,
323 LAST_UPDATE_DATE,
324 LAST_UPDATE_LOGIN)
325 VALUES (JTF_TTY_NAMED_ACCT_CHANGES_S.NEXTVAL,
326 1,
327 'TGA',
328 p_terr_grp_acct_id,
329 'DELETE',
330 'DELETE NA',
331 l_user_id,
332 sysdate,
333 l_user_id,
334 sysdate,
335 l_login_id);
336 */
337 x_return_status := fnd_api.g_ret_sts_success;
338
339 JTF_TTY_GEN_TERR_PVT.delete_TGA(
340 p_terr_grp_acct_id =>p_terr_grp_acct_id,
341 p_terr_group_id =>p_terr_grp_id,
342 p_catchall_terr_id =>-1,
343 p_change_type =>'SALES_TEAM_UPDATE'
344 );
345
346 EXCEPTION
347 WHEN OTHERS THEN
348 x_return_status := fnd_api.g_ret_sts_unexp_error;
349 l_error_msg := substr(sqlerrm,1,200);
350 fnd_message.clear;
351 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
352 fnd_message.set_token('ERRMSG', l_error_msg );
353 put_jty_log('Error in DELETE_ACCT_FROM_TG: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
354 return;
355 END DELETE_ACCT_FROM_TG;
356
357
358
359
360 --This function returns the site type code for a party
361 FUNCTION GET_SITE_TYPE_CODE( P_PARTY_ID NUMBER ) RETURN VARCHAR2 IS
362
363 l_site_type_code VARCHAR2(30);
364 l_chk_done VARCHAR2(1) := 'N' ;
365
366 BEGIN
367
368 hz_common_pub.disable_cont_source_security;
369
370 --Check for global ultimate
371 BEGIN
372 SELECT 'Y' INTO l_chk_done FROM DUAL
373 WHERE EXISTS (SELECT 'Y'
374 FROM hz_relationships hzr
375 WHERE hzr.subject_table_name = 'HZ_PARTIES'
376 AND hzr.object_table_name = 'HZ_PARTIES'
377 AND hzr.relationship_type = 'GLOBAL_ULTIMATE'
378 AND hzr.relationship_code = 'GLOBAL_ULTIMATE_OF'
379 AND hzr.status = 'A'
380 AND sysdate between hzr.start_date and nvl(hzr.end_date, sysdate)
381 AND hzr.subject_id = p_party_id );
382 EXCEPTION
383 WHEN NO_DATA_FOUND THEN
384 null;
385 END;
386
387 IF l_chk_done = 'Y' THEN
388 l_site_type_code := 'GU' ;
389 RETURN l_site_type_code;
390 END IF;
391
392 --Check for domestic ultimate
393 BEGIN
394 SELECT 'Y' INTO l_chk_done FROM DUAL
395 WHERE EXISTS (SELECT 'Y'
396 FROM hz_relationships hzr
397 WHERE hzr.subject_table_name = 'HZ_PARTIES'
398 AND hzr.object_table_name = 'HZ_PARTIES'
399 AND hzr.relationship_type = 'DOMESTIC_ULTIMATE'
400 AND hzr.relationship_code = 'DOMESTIC_ULTIMATE_OF'
401 AND hzr.status = 'A'
402 AND sysdate between hzr.start_date and nvl(hzr.end_date, sysdate)
403 AND hzr.subject_id = p_party_id );
404 EXCEPTION
405 WHEN NO_DATA_FOUND THEN
406 null;
407 END;
408
409 IF l_chk_done = 'Y' THEN
410 l_site_type_code := 'DU' ;
411 RETURN l_site_type_code;
412 END IF;
413
414 BEGIN
415 SELECT lkp.lookup_code INTO l_site_type_code
416 FROM fnd_lookups lkp, hz_parties hzp
417 WHERE lkp.lookup_type = 'JTF_TTY_SITE_TYPE_CODE'
418 AND hzp.hq_branch_ind = lkp.lookup_code
419 AND hzp.party_id = p_party_id;
420
421 EXCEPTION
422 WHEN NO_DATA_FOUND THEN
423 l_site_type_code := 'UN';
424 END;
425
426 RETURN(l_site_type_code);
427
428 EXCEPTION
429 WHEN OTHERS THEN
430 put_jty_log('Error in GET_SITE_TYPE_CODE: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
431
432 END GET_SITE_TYPE_CODE;
433
434 --This procedure creates qualifier mappings for a named account
435 PROCEDURE CREATE_ACCT_MAPPINGS(
436 P_API_VERSION_NUMBER IN NUMBER,
437 P_INIT_MSG_LIST IN VARCHAR2,
438 P_COMMIT IN VARCHAR2,
439 P_VALIDATION_LEVEL IN NUMBER,
440 P_ACCT_ID IN NUMBER,
441 P_PARTY_ID IN NUMBER,
442 P_PARTY_SITE_ID IN NUMBER,
443 P_USER_ID IN NUMBER,
444 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
445 X_MSG_COUNT OUT NOCOPY NUMBER,
446 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
447
448 l_business_name VARCHAR2(360) DEFAULT NULL;
449 l_trade_name VARCHAR2(240) DEFAULT NULL;
450 l_postal_code VARCHAR2(60) DEFAULT NULL;
451
452 BEGIN
453
454 BEGIN
455 SELECT H3.party_name,
456 H3.known_as,
457 H1.postal_code
458 INTO l_business_name,
459 l_trade_name,
460 l_postal_code
461 FROM HZ_PARTIES H3,
462 HZ_LOCATIONS H1,
463 HZ_PARTY_SITES H2
464 WHERE h3.party_id = h2.party_id
465 AND h2.location_id = h1.location_id
466 AND h3.party_id = p_party_id
467 AND h2.party_site_id = p_party_site_id;
468
469 EXCEPTION
470 WHEN NO_DATA_FOUND THEN
471 null;
472 END;
473
474 --Key name for business name
475 IF (l_business_name is not null) THEN
476
477 INSERT INTO jtf_tty_acct_qual_maps
478 (ACCOUNT_QUAL_MAP_ID,
479 OBJECT_VERSION_NUMBER,
480 NAMED_ACCOUNT_ID,
481 QUAL_USG_ID,
482 COMPARISON_OPERATOR,
483 VALUE1_CHAR,
484 VALUE2_CHAR,
485 CREATED_BY,
486 CREATION_DATE,
487 LAST_UPDATED_BY,
488 LAST_UPDATE_DATE)
489 (SELECT jtf_tty_acct_qual_maps_s.nextval,
490 1,
491 p_acct_id,
492 -1012,
493 '=',
494 UPPER(l_business_name),
495 null,
496 p_user_id,
497 sysdate,
498 p_user_id,
499 sysdate
500 FROM DUAl);
501 END IF;
502
503 --Key name for trade name
504 IF (l_trade_name is not null) THEN
505
506 INSERT INTO jtf_tty_acct_qual_maps
507 (ACCOUNT_QUAL_MAP_ID,
508 OBJECT_VERSION_NUMBER,
509 NAMED_ACCOUNT_ID,
510 QUAL_USG_ID,
511 COMPARISON_OPERATOR,
512 VALUE1_CHAR,
513 VALUE2_CHAR,
514 CREATED_BY,
515 CREATION_DATE,
516 LAST_UPDATED_BY,
517 LAST_UPDATE_DATE)
518 (SELECT jtf_tty_acct_qual_maps_s.nextval,
519 1,
520 p_acct_id,
521 -1012,
522 '=',
523 UPPER(l_trade_name),
524 null,
525 p_user_id,
526 sysdate,
527 p_user_id,
528 sysdate
529 FROM DUAL);
530 END IF;
531
532 --Key name for postal code
533 IF (l_postal_code is not null) THEN
534
535 INSERT INTO jtf_tty_acct_qual_maps
536 (ACCOUNT_QUAL_MAP_ID,
537 OBJECT_VERSION_NUMBER,
538 NAMED_ACCOUNT_ID,
539 QUAL_USG_ID,
540 COMPARISON_OPERATOR,
541 VALUE1_CHAR,
542 VALUE2_CHAR,
543 CREATED_BY,
544 CREATION_DATE,
545 LAST_UPDATED_BY,
546 LAST_UPDATE_DATE)
547 (SELECT jtf_tty_acct_qual_maps_s.nextval,
548 1,
549 p_acct_id,
550 -1007,
551 '=',
552 l_postal_code,
553 null,
554 p_user_id,
555 sysdate,
556 p_user_id,
557 sysdate
558 FROM DUAL);
559 END IF;
560
561 END CREATE_ACCT_MAPPINGS;
562
563
564 --This procedure adds an organization to a TG
565 PROCEDURE ADD_ORG_TO_TG(
566 P_API_VERSION_NUMBER IN NUMBER,
567 P_INIT_MSG_LIST IN VARCHAR2,
568 P_COMMIT IN VARCHAR2,
569 P_VALIDATION_LEVEL IN NUMBER,
570 P_PARTY_NUMBER IN VARCHAR2,
571 P_PARTY_SITE_ID IN NUMBER,
572 P_TERR_GRP_NAME IN VARCHAR2,
573 P_ATTRIBUTE1 IN VARCHAR2,
574 P_ATTRIBUTE2 IN VARCHAR2,
575 P_ATTRIBUTE3 IN VARCHAR2,
576 P_ATTRIBUTE4 IN VARCHAR2,
577 P_ATTRIBUTE5 IN VARCHAR2,
578 P_ATTRIBUTE6 IN VARCHAR2,
579 P_ATTRIBUTE7 IN VARCHAR2,
580 P_ATTRIBUTE8 IN VARCHAR2,
581 P_ATTRIBUTE9 IN VARCHAR2,
582 P_ATTRIBUTE10 IN VARCHAR2,
583 P_ATTRIBUTE11 IN VARCHAR2,
584 P_ATTRIBUTE12 IN VARCHAR2,
585 P_ATTRIBUTE13 IN VARCHAR2,
586 P_ATTRIBUTE14 IN VARCHAR2,
587 P_ATTRIBUTE15 IN VARCHAR2,
588 P_START_DATE IN OUT NOCOPY DATE,
589 P_END_DATE IN OUT NOCOPY DATE,
590 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
591 X_MSG_COUNT OUT NOCOPY NUMBER,
592 X_MSG_DATA OUT NOCOPY VARCHAR2,
593 X_TERR_GRP_ID OUT NOCOPY NUMBER,
594 X_TERR_GRP_ACCT_ID OUT NOCOPY NUMBER,
595 X_NAMED_ACCT_ID OUT NOCOPY NUMBER) IS
596
597 l_party_number VARCHAR2(30);
598 l_tg_name VARCHAR2(150);
599 l_party_id NUMBER;
600 l_tg_id NUMBER;
601 l_na_id NUMBER;
602 l_tga_id NUMBER;
603 l_site_type_code VARCHAR2(30);
604 l_duns_number VARCHAR2(30);
605 l_match_rule_code VARCHAR2(30);
606 l_mapping_flag VARCHAR2(1) := 'N';
607 l_user_id NUMBER;
608 l_login_id NUMBER;
609 l_error_msg VARCHAR2(250);
610 l_tg_start_date date;
611 l_tg_end_date date;
612
613 --Cursor to validate party based on party number : Single Row
614 CURSOR c_validate_party(l_party_number VARCHAR2, l_party_site_id NUMBER) IS
615
616 SELECT hzp.party_id, hzp.duns_number_c
617 FROM hz_parties hzp
618 , hz_party_sites hzps
619 WHERE hzp.party_number = l_party_number
620 AND hzp.party_id = hzps.party_id
621 and hzps.party_site_id = l_party_site_id
622 AND hzp.status = 'A';
623 -- AND hzp.party_type = 'ORGANIZATION';
624
625
626 --Cursor to validate territory group based on TG name : Single Row
627 --Note: Joining with jtf_terr to stripe data by org
628 --Note: Possible to have multiple TGs with same name, hence using ROWNUM
629 CURSOR c_validate_tg(l_tg_name VARCHAR2) IS
630 SELECT a.terr_group_id, a.matching_rule_code,
631 a.active_from_date,
632 NVL( a.active_to_date, ADD_MONTHS(a.active_from_date,120) ) active_to_date
633 FROM jtf_tty_terr_groups a, jtf_terr_all b
634 WHERE a.parent_terr_id = b.terr_id
635 AND upper(a.terr_group_name) like upper(l_tg_name)
636 AND a.self_service_type = 'NAMED_ACCOUNT'
637 AND a.active_from_date <= sysdate
638 AND (a.active_to_date is null or a.active_to_date >= sysdate)
639 AND rownum < 2;
640
641 --Cursor to check if TGA already exists for specified party and terr group: Single Row
642 CURSOR c_check_tga_exists(l_party_id NUMBER, l_party_site_id NUMBER, l_tg_id NUMBER) IS
643 SELECT na.named_account_id, tga.terr_group_account_id
644 FROM jtf_tty_named_accts na, jtf_tty_terr_grp_accts tga
645 WHERE na.party_id = l_party_id
646 AND na.party_site_id = l_party_site_id
647 AND tga.named_account_id = na.named_account_id
648 AND tga.terr_group_id = l_tg_id;
649
650 --Cursor to check if given party is already a named account : Single Row
651 CURSOR c_check_na_exists(l_party_id NUMBER, l_party_site_id NUMBER) IS
652 SELECT named_account_id, mapping_complete_flag
653 FROM jtf_tty_named_accts
654 WHERE party_id = l_party_id
655 and party_site_id = l_party_site_id ;
656
657
658 BEGIN
659
660 l_duns_number := null;
661 -- dbms_output.put_line('Sandeep - In AADD_ORGTO_TG :1' );
662 l_party_number := nvl(P_PARTY_NUMBER, -999);
663 OPEN c_validate_party(l_party_number, p_party_site_id);
664 FETCH c_validate_party INTO l_party_id, l_duns_number;
665
666 --If party is not valid then set message and return
667 IF c_validate_party%NOTFOUND THEN
668 CLOSE c_validate_party;
669 x_terr_grp_id := -999;
670 x_terr_grp_acct_id := -999;
671 x_named_acct_id := -999;
672 x_return_status := fnd_api.g_ret_sts_error;
673 fnd_message.clear;
674 fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_PARTY');
675 return;
676 END IF;
677 CLOSE c_validate_party;
678
679 l_tg_name := nvl(P_TERR_GRP_NAME, -999);
680 -- dbms_output.put_line('Sandeep - In AADD_ORGTO_TG :1.5' || l_tg_name);
681 OPEN c_validate_tg(l_tg_name);
682 FETCH c_validate_tg INTO l_tg_id, l_match_rule_code, l_tg_start_date, l_tg_end_date;
683 -- dbms_output.put_line('Sandeep - In AADD_ORGTO_TG :2' || l_tg_id);
684
685 --If TG is not valid then set message and return
686 IF c_validate_tg%NOTFOUND THEN
687 CLOSE c_validate_tg;
688 x_terr_grp_id := -999;
689 x_terr_grp_acct_id := -999;
690 x_named_acct_id := -999;
691 x_return_status := fnd_api.g_ret_sts_error;
692 fnd_message.clear;
693 fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_TO_TG');
694 return;
695 END IF;
696 CLOSE c_validate_tg;
697
698 IF (( l_match_rule_code = '3' ) AND ( l_duns_number is null )) THEN
699 x_terr_grp_id := -999;
700 x_terr_grp_acct_id := -999;
701 x_named_acct_id := -999;
702 x_return_status := fnd_api.g_ret_sts_error;
703 fnd_message.clear;
704 fnd_message.set_name ('JTF', 'JTF_TTY_NO_DUNS_NUMBER');
705 return;
706 END IF;
707
708 -- added 06/05/2006 bug 5246668, to validate and set date
709 IF p_start_date is null THEN
710 IF (TRUNC(SYSDATE) > l_tg_start_date) THEN
711 p_start_date := TRUNC(SYSDATE);
712 ELSE
713 p_start_date := l_tg_start_date;
714 END IF;
715 ELSE
716 IF (p_start_date < l_tg_start_date) THEN
717 x_return_status := fnd_api.g_ret_sts_error;
718 fnd_message.clear;
719 fnd_message.set_name ('JTF', 'JTF_TERR_STARTDATE_NOT_VALID');
720 return;
721 END IF;
722 END IF;
723
724 IF p_end_date is null THEN
725 IF (ADD_MONTHS(NVL(P_START_DATE,TRUNC(SYSDATE)), 12) > l_tg_end_date) THEN
726 p_end_date := l_tg_end_date;
727 ELSE
728 p_end_date := ADD_MONTHS(NVL(P_START_DATE,TRUNC(SYSDATE)), 12);
729 END IF;
730 ELSE
731 IF (p_end_date < l_tg_start_date OR p_end_date > l_tg_end_date) THEN
732 x_return_status := fnd_api.g_ret_sts_error;
733 fnd_message.clear;
734 fnd_message.set_name ('JTF', 'JTF_TERR_ENDDATE_NOT_VALID');
735 return;
736 END IF;
737 END IF;
738
739 -- end for bug 5246668, by mhtran
740
741
742 -- dbms_output.put_line('Sandeep - In AADD_ORGTO_TG :3' || l_tg_id);
743 OPEN c_check_tga_exists(l_party_id, p_party_site_id, l_tg_id);
744 FETCH c_check_tga_exists INTO l_na_id, l_tga_id;
745
746 --If TGA already exists then set message and return
747 IF c_check_tga_exists%FOUND THEN
748 CLOSE c_check_tga_exists;
749 x_terr_grp_id := -999;
750 x_terr_grp_acct_id := -999;
751 x_named_acct_id := -999;
752 x_return_status := fnd_api.g_ret_sts_error;
753 fnd_message.clear;
754 fnd_message.set_name ('JTF', 'JTF_TTY_TGA_EXISTS');
755 return;
756 END IF;
757 CLOSE c_check_tga_exists;
758
759 --At this point it is clear that this is a valid party/NA which needs to be added to the TG
760 OPEN c_check_na_exists(l_party_id, p_party_site_id);
761 FETCH c_check_na_exists into l_na_id, l_mapping_flag;
762
763 --Get site_type_code and user_id needed while creating NA and TGA
764 l_site_type_code := JTF_TTY_MAINTAIN_NA_PVT.get_site_type_code(l_party_id);
765 l_user_id := fnd_global.user_id;
766 l_login_id := fnd_global.login_id;
767
768 --If named account does not exist, then create a named account for the party
769 IF c_check_na_exists%NOTFOUND THEN
770
771 SELECT jtf_tty_named_accts_s.nextval INTO l_na_id FROM dual;
772 -- dbms_output.put_line('Sandeep - In AADD_ORGTO_TG :3' );
773
774 INSERT INTO jtf_tty_named_accts
775 (NAMED_ACCOUNT_ID,
776 OBJECT_VERSION_NUMBER,
777 PARTY_ID,
778 PARTY_SITE_ID,
779 MAPPING_COMPLETE_FLAG,
780 SITE_TYPE_CODE,
781 CREATED_BY,
782 CREATION_DATE,
783 LAST_UPDATED_BY,
784 LAST_UPDATE_DATE,
785 LAST_UPDATE_LOGIN)
786 VALUES
787 (l_na_id,
788 2,
789 l_party_id,
790 p_party_site_id,
791 l_mapping_flag,
792 l_site_type_code,
793 l_user_id,
794 sysdate,
795 l_user_id,
796 sysdate,
797 l_login_id);
798
799 IF l_match_rule_code = '1' THEN
800 JTF_TTY_MAINTAIN_NA_PVT.create_acct_mappings
801 (P_API_VERSION_NUMBER => 1.0,
802 P_INIT_MSG_LIST => fnd_api.g_false,
803 P_COMMIT => fnd_api.g_false,
804 P_VALIDATION_LEVEL => fnd_api.g_valid_level_full,
805 P_ACCT_ID => l_na_id,
806 P_PARTY_ID => l_party_id,
807 P_PARTY_SITE_ID => p_party_site_id,
808 P_USER_ID => l_user_id,
809 X_RETURN_STATUS => x_return_status,
810 X_MSG_COUNT => x_msg_count,
811 X_MSG_DATA => x_msg_data);
812 END IF;
813 END IF;
814
815 --At this point either named account exists or it has been created
816 --Add this named account to the TG
817 -- dbms_output.put_line('Sandeep - In AADD_ORGTO_TG :34' );
818 CLOSE c_check_na_exists;
819 SELECT jtf_tty_terr_grp_accts_s.nextval INTO l_tga_id FROM dual;
820
821 INSERT INTO jtf_tty_terr_grp_accts
822 (TERR_GROUP_ACCOUNT_ID,
823 OBJECT_VERSION_NUMBER,
824 TERR_GROUP_ID,
825 NAMED_ACCOUNT_ID,
826 DN_JNA_MAPPING_COMPLETE_FLAG,
827 DN_JNA_SITE_TYPE_CODE,
828 DN_JNR_ASSIGNED_FLAG,
829 ATTRIBUTE1,
830 ATTRIBUTE2,
831 ATTRIBUTE3,
832 ATTRIBUTE4,
833 ATTRIBUTE5,
834 ATTRIBUTE6,
835 ATTRIBUTE7,
836 ATTRIBUTE8,
837 ATTRIBUTE9,
838 ATTRIBUTE10,
839 ATTRIBUTE11,
840 ATTRIBUTE12,
841 ATTRIBUTE13,
842 ATTRIBUTE14,
843 ATTRIBUTE15,
844 START_DATE,
845 END_DATE,
846 CREATED_BY,
847 CREATION_DATE,
848 LAST_UPDATED_BY,
849 LAST_UPDATE_DATE,
850 LAST_UPDATE_LOGIN)
851 VALUES (l_tga_id,
852 2,
853 l_tg_id,
854 l_na_id,
855 l_mapping_flag,
856 l_site_type_code,
857 'N',
858 P_ATTRIBUTE1,
859 P_ATTRIBUTE2,
860 P_ATTRIBUTE3,
861 P_ATTRIBUTE4,
862 P_ATTRIBUTE5,
863 P_ATTRIBUTE6,
864 P_ATTRIBUTE7,
865 P_ATTRIBUTE8,
866 P_ATTRIBUTE9,
867 P_ATTRIBUTE10,
868 P_ATTRIBUTE11,
869 P_ATTRIBUTE12,
870 P_ATTRIBUTE13,
871 P_ATTRIBUTE14,
872 P_ATTRIBUTE15,
873 P_START_DATE,
874 P_END_DATE,
875 l_user_id,
876 sysdate,
877 l_user_id,
878 sysdate,
879 l_login_id);
880
881 /* by shli, GSST Decom */
882 /*--Insert row to track changes for GTP
883 INSERT INTO jtf_tty_named_acct_changes
884 (NAMED_ACCT_CHANGE_ID,
885 OBJECT_VERSION_NUMBER,
886 OBJECT_TYPE,
887 OBJECT_ID,
888 CHANGE_TYPE,
889 FROM_WHERE,
890 CREATED_BY,
891 CREATION_DATE,
892 LAST_UPDATED_BY,
893 LAST_UPDATE_DATE,
894 LAST_UPDATE_LOGIN)
895 VALUES (JTF_TTY_NAMED_ACCT_CHANGES_S.NEXTVAL,
896 1,
897 'TG',
898 l_tg_id,
899 'UPDATE',
900 'UPDATE TERRITORY GROUP',
901 l_user_id,
902 sysdate,
903 l_user_id,
904 sysdate,
905 l_login_id);
906 */
907 x_terr_grp_id := l_tg_id;
908 x_terr_grp_acct_id := l_tga_id;
909 x_named_acct_id := l_na_id;
910 x_return_status := fnd_api.g_ret_sts_success;
911
912 EXCEPTION
913 WHEN OTHERS THEN
914 l_error_msg := substr(sqlerrm,1,200);
915 fnd_message.clear;
916 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
917 fnd_message.set_token('ERRMSG', l_error_msg );
918 put_jty_log('Error in ADD_ORG_TO_TG: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
919 return;
920 END ADD_ORG_TO_TG;
921
922 --This procedure assigns a named account to respective TG owner(s)
923 PROCEDURE ASSIGN_ACCT_TO_TG_OWNERS(
924 P_API_VERSION_NUMBER IN NUMBER,
925 P_INIT_MSG_LIST IN VARCHAR2,
926 P_COMMIT IN VARCHAR2,
927 P_VALIDATION_LEVEL IN NUMBER,
928 P_TERR_GRP_ACCT_ID IN NUMBER,
929 P_TERR_GRP_ID IN NUMBER,
930 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
931 X_MSG_COUNT OUT NOCOPY NUMBER,
932 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
933
934 l_rsc_group_id NUMBER;
935 l_resource_id NUMBER;
936 l_rsc_role_code VARCHAR2(60);
937 l_rsc_resource_type VARCHAR2(30);
938 l_user_id NUMBER;
939 l_error_msg VARCHAR2(250);
940
941 -- Cursor to get resource details for TG owner(s) : Multiple Rows
942 CURSOR c_get_rsc_details(l_tg_id NUMBER) IS
943 SELECT rsc_group_id, resource_id, rsc_role_code, rsc_resource_type
944 FROM jtf_tty_terr_grp_owners
945 WHERE terr_group_id = l_tg_id;
946
947 BEGIN
948
949 OPEN c_get_rsc_details(p_terr_grp_id);
950 LOOP
951 FETCH c_get_rsc_details INTO l_rsc_group_id, l_resource_id, l_rsc_role_code, l_rsc_resource_type;
952 EXIT WHEN c_get_rsc_details%NOTFOUND;
953 l_user_id := fnd_global.user_id;
954 INSERT INTO jtf_tty_named_acct_rsc
955 (ACCOUNT_RESOURCE_ID,
956 OBJECT_VERSION_NUMBER,
957 TERR_GROUP_ACCOUNT_ID,
958 RESOURCE_ID,
959 RSC_GROUP_ID,
960 RSC_ROLE_CODE,
961 ASSIGNED_FLAG,
962 RSC_RESOURCE_TYPE,
963 CREATED_BY,
964 CREATION_DATE,
965 LAST_UPDATED_BY,
966 LAST_UPDATE_DATE)
967 (SELECT jtf_tty_named_acct_rsc_s.nextval,
968 1,
969 p_terr_grp_acct_id,
970 l_resource_id,
971 l_rsc_group_id,
972 l_rsc_role_code,
973 'N',
974 l_rsc_resource_type,
975 l_user_id,
976 sysdate,
977 l_user_id,
978 sysdate
979 FROM dual
980 WHERE NOT EXISTS (SELECT null FROM jtf_tty_named_acct_rsc rsc
981 WHERE rsc.terr_group_account_id = p_terr_grp_acct_id
982 AND rsc.RESOURCE_ID = l_resource_id
983 AND rsc.RSC_ROLE_CODE = l_rsc_role_code
984 AND rsc.RSC_GROUP_ID = l_rsc_group_id
985 AND rsc.RSC_RESOURCE_TYPE = l_rsc_resource_type));
986
987
988 END LOOP;
989 CLOSE c_get_rsc_details;
990
991 EXCEPTION
992 WHEN OTHERS THEN
993 l_error_msg := substr(sqlerrm,1,200);
994 fnd_message.clear;
995 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
996 fnd_message.set_token('ERRMSG', l_error_msg );
997 put_jty_log('Error in ASSIGN_ACCT_TO_TG_OWNERS: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
998 return;
999 END ASSIGN_ACCT_TO_TG_OWNERS;
1000
1001 --Procedure to validate resource, called by populate sales team
1002 PROCEDURE VALIDATE_RESOURCE (
1003 P_API_VERSION_NUMBER IN NUMBER,
1004 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1005 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
1006 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1007 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1008 X_MSG_COUNT OUT NOCOPY NUMBER,
1009 X_MSG_DATA OUT NOCOPY VARCHAR2,
1010 P_RESOURCE_NAME IN VARCHAR2,
1011 P_GROUP_NAME IN VARCHAR2,
1012 P_ROLE_NAME IN VARCHAR2,
1013 P_TERR_GROUP_ID IN NUMBER,
1014 X_RESOURCE_ID OUT NOCOPY NUMBER,
1015 X_GROUP_ID OUT NOCOPY NUMBER,
1016 X_ROLE_CODE OUT NOCOPY VARCHAR2,
1017 X_ERROR_CODE OUT NOCOPY NUMBER,
1018 X_STATUS OUT NOCOPY VARCHAR2) IS
1019
1020 counter NUMBER:=0;
1021 comb NUMBER:=0;
1022 l_select varchar2(10);
1023 l_user_id NUMBER;
1024 found NUMBER;
1025 l_num_valid_rsc_id NUMBER := 0;
1026 l_error_msg VARCHAR2(250);
1027
1028 TYPE NUMBER_TABLE_TYPE IS TABLE OF NUMBER;
1029 l_res_tbl NUMBER_TABLE_TYPE := NUMBER_TABLE_TYPE();
1030
1031 CURSOR c_get_resource_id ( c_resource_name VARCHAR2 ) IS
1032 SELECT RESOURCE_id
1033 FROM jtf_rs_resource_extns_vl
1034 WHERE upper(resource_name) = upper(c_resource_name)
1035 AND category = 'EMPLOYEE'
1036 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE+1);
1037
1038 CURSOR c_get_group_id ( c_group_name VARCHAR2 ) IS
1039 SELECT group_id
1040 FROM jtf_rs_groups_vl
1041 WHERE upper(group_name) = upper(c_group_name)
1042 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE+1);
1043
1044 CURSOR c_get_role_code ( c_role_name VARCHAR2 ) IS
1045 SELECT rol.role_code
1046 FROM jtf_rs_roles_vl rol
1047 WHERE upper(rol.role_name) = upper(c_role_name)
1048 AND (rol.role_type_code = 'SALES' OR
1049 rol.role_type_code = 'TELESALES' OR
1050 rol.role_type_code = 'FIELDSALES')
1051 AND active_flag ='Y';
1052
1053 BEGIN
1054
1055 x_status := 'S';
1056 x_return_status := FND_API.G_RET_STS_SUCCESS;
1057 l_user_id := fnd_global.user_id;
1058
1059 IF (P_RESOURCE_NAME is not null AND P_GROUP_NAME is not null AND P_ROLE_NAME is not null) THEN
1060
1061 --Validation against LOVs by terr group's owner resource_id allows a resource not owned by the logged in
1062 --user valid. The validation also blocks any resource outside the terr group.
1063
1064 --Check group name AVP name
1065 BEGIN
1066 -- check group name
1067 counter :=0;
1068 FOR group_rec IN c_get_group_id( c_group_name => p_group_name ) LOOP
1069 counter := counter +1;
1070 x_group_id := group_rec.group_id; -- group_id assigned
1071 IF counter=2 THEN
1072 x_status := 'E';
1073 x_return_status := FND_API.G_RET_STS_ERROR ;
1074 fnd_message.clear;
1075 fnd_message.set_name ('JTF', 'JTF_TTY_NON_UNIQUE_GROUP_NAME');
1076 x_error_code := 1;
1077 RETURN;
1078 END IF;
1079 END LOOP;
1080 IF counter=0 THEN
1081 RAISE NO_DATA_FOUND;
1082 END IF;
1083
1084 -- check role name
1085 counter :=0;
1086 FOR role_rec IN c_get_role_code( c_role_name => p_role_name ) LOOP
1087 counter := counter +1;
1088 x_role_code := role_rec.role_code; -- role_code assigned
1089 IF counter=2 THEN
1090 x_status := 'E';
1091 x_return_status := FND_API.G_RET_STS_ERROR ;
1092 fnd_message.clear;
1093 fnd_message.set_name ('JTF', 'JTF_TTY_NON_UNIQUE_ROLE_NAME');
1094 x_error_code := 1;
1095 RETURN;
1096 END IF;
1097 END LOOP;
1098 IF counter=0 THEN
1099 RAISE NO_DATA_FOUND;
1100 END IF;
1101
1102 EXCEPTION
1103 WHEN NO_DATA_FOUND THEN
1104 x_status := 'E';
1105 x_return_status := FND_API.G_RET_STS_ERROR ;
1106 fnd_message.clear;
1107 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
1108 x_error_code := 1;
1109 RETURN;
1110 END; -- of check group name AVP name
1111
1112 --Check resource, group and role combination
1113 BEGIN
1114 counter :=0;
1115 FOR res_rec IN c_get_resource_id( c_resource_name => p_resource_name ) LOOP
1116 l_res_tbl.EXTEND;
1117 counter := counter + 1;
1118 l_res_tbl(counter) := res_rec.resource_id;
1119 END LOOP;
1120
1121 --No resource by this name
1122 IF counter = 0 THEN
1123 RAISE NO_DATA_FOUND;
1124 ELSE
1125 IF (l_res_tbl IS NOT NULL) AND ( l_res_tbl.COUNT > 0 ) THEN
1126 l_num_valid_rsc_id := 0;
1127 FOR i IN 1 .. l_res_tbl.COUNT LOOP
1128 /* commenting out due to 3576571 bug */
1129 /*
1130 BEGIN
1131 SELECT 'VALID' INTO l_select
1132 FROM jtf_tty_terr_grp_accts tga,
1133 jtf_tty_named_acct_rsc nar,
1134 jtf_tty_terr_groups tg
1135 WHERE nar.terr_group_account_id = tga.terr_group_account_id
1136 AND nar.rsc_role_code = X_ROLE_CODE
1137 AND tga.terr_group_id = tg.terr_group_id
1138 AND sysdate >= tg.active_from_date
1139 AND (tg.active_to_date is null OR
1140 sysdate <= tg.active_to_date)
1141 AND nar.resource_id = l_res_tbl(i)
1142 AND nar.rsc_group_id = X_GROUP_ID
1143 AND tga.named_account_id = P_NAMED_ACCOUNT_ID
1144 AND tga.terr_group_id <>P_terr_group_id
1145 AND rownum < 2;
1146
1147 x_status := 'I'; -- it is in other TG, return with Ignore
1148 RETURN;
1149
1150 EXCEPTION -- go on
1151 WHEN NO_DATA_FOUND THEN
1152 NULL;
1153 END;
1154 */
1155 BEGIN
1156 SELECT 'VALID'
1157 INTO l_select
1158 FROM jtf_rs_group_members mem, jtf_rs_roles_b rol, jtf_rs_role_relations rlt
1159 WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
1160 AND rlt.delete_flag = 'N'
1161 AND sysdate >= rlt.start_date_active
1162 AND (rlt.end_date_active is null OR
1163 sysdate <= rlt.end_date_active)
1164 AND rlt.role_id = rol.role_id
1165 AND rol.role_code = x_role_code
1166 AND rlt.role_resource_id = mem.group_member_id
1167 AND mem.delete_flag = 'N'
1168 AND mem.group_id = x_group_id
1169 AND mem.resource_id = l_res_tbl(i);
1170
1171 x_resource_id := l_res_tbl(i);
1172 l_num_valid_rsc_id := l_num_valid_rsc_id + 1;
1173
1174 EXCEPTION
1175 WHEN NO_DATA_FOUND THEN
1176 NULL;
1177 WHEN TOO_MANY_ROWS THEN
1178 RAISE TOO_MANY_ROWS; -- not common error.
1179 END;
1180 END LOOP;
1181
1182 IF l_num_valid_rsc_id > 1 THEN
1183 RAISE TOO_MANY_ROWS; -- duplicate combination, like two Lisa in the same resource group, same role
1184 ELSIF l_num_valid_rsc_id =0 THEN
1185 RAISE NO_DATA_FOUND; -- the reps(by that name) are valid but not in the resource group with the role
1186 END IF;
1187
1188 END IF; -- l_res_tbl > 0
1189 END IF; -- count
1190
1191 EXCEPTION
1192 WHEN NO_DATA_FOUND THEN
1193 x_status := 'E';
1194 x_return_status := FND_API.G_RET_STS_ERROR ;
1195 fnd_message.clear;
1196 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
1197 x_error_code := 1;
1198 RETURN;
1199 WHEN TOO_MANY_ROWS THEN
1200 x_status := 'E';
1201 x_return_status := FND_API.G_RET_STS_ERROR ;
1202 fnd_message.clear;
1203 fnd_message.set_name ('JTF', 'JTF_TTY_NON_UNIQUE_SALES_DATA');
1204 x_error_code := 1;
1205 RETURN;
1206 WHEN OTHERS THEN
1207 x_status := 'E';
1208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1209 x_error_code := 4;
1210 l_error_msg := substr(sqlerrm,1,200);
1211 fnd_message.clear;
1212 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
1213 fnd_message.set_token('ERRMSG', l_error_msg );
1214 put_jty_log('Error in VALIDATE_RESOURCE: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
1215 RETURN;
1216 END;
1217
1218 -- check the role code
1219 BEGIN
1220 SELECT 'Y'
1221 INTO l_select
1222 FROM jtf_tty_terr_grp_roles
1223 WHERE terr_group_id=P_terr_group_id
1224 AND role_code = X_ROLE_CODE;
1225
1226 EXCEPTION
1227 WHEN NO_DATA_FOUND THEN
1228 x_status := 'E';
1229 x_error_code := 2;
1230 x_return_status := FND_API.G_RET_STS_ERROR;
1231 fnd_message.clear;
1232 fnd_message.set_name ('JTF', 'JTF_TTY_ROLE_NOT_IN_TG');
1233 RETURN;
1234 END;
1235
1236 ELSE
1237 x_status := 'E';
1238 x_error_code := 3;
1239 x_return_status := FND_API.G_RET_STS_ERROR;
1240 fnd_message.clear;
1241 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_MANDATORY');
1242 RETURN;
1243 END IF;
1244
1245 EXCEPTION
1246 WHEN OTHERS THEN
1247 x_status := 'E';
1248 x_error_code := 4;
1249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1250 l_error_msg := substr(sqlerrm,1,200);
1251 fnd_message.clear;
1252 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
1253 fnd_message.set_token('ERRMSG', l_error_msg );
1254 put_jty_log('Error in VALIDATE_RESOURCE: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
1255 RETURN;
1256 END VALIDATE_RESOURCE;
1257
1258 /* Procedure which checks whether the user can add the given
1259 sales person.*/
1260
1261 PROCEDURE CHECK_VALID_RESOURCE_ADD (
1262 p_Api_Version_Number IN NUMBER,
1263 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1264 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1265 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1266 X_Return_Status OUT NOCOPY VARCHAR2,
1267 X_Msg_Count OUT NOCOPY NUMBER,
1268 X_Msg_Data OUT NOCOPY VARCHAR2,
1269 P_RESOURCE_id in number,
1270 P_GROUP_ID IN NUMBER,
1271 P_ROLE_CODE in varchar2,
1272 P_user_id in number,
1273 P_TG_ID in number,
1274 x_owner_resource_id out NOCOPY NUMBER,
1275 x_owner_group_id out NOCOPY NUMBER,
1276 x_owner_role_code OUT NOCOPY VARCHAR2,
1277 x_error_code out NOCOPY number,
1278 x_status out NOCOPY varchar2) is
1279
1280 l_select varchar2(100);
1281 l_error_msg VARCHAR2(250);
1282
1283 BEGIN
1284 -- x_status := 'S';
1285 x_return_status := FND_API.G_RET_STS_SUCCESS;
1286 /* check salesperson for the current TG */
1287 BEGIN
1288 /* find if the resource belongs to the saleshierarchy of the territory group owner(s) since when an
1289 admin is doing upload, he/she is doing as the owner of the TG */
1290 SELECT 'VALID'
1291 INTO l_select
1292 FROM jtf_tty_srch_my_resources_v /*jtf_tty_my_resources_v*/ grv,
1293 jtf_tty_terr_grp_owners jto,
1294 jtf_rs_resource_extns res
1295 WHERE EXISTS
1296 ( SELECT NULL
1297 FROM jtf_rs_groups_denorm grpd
1298 WHERE /* part of Salesgroup hierarchy of Territory Group owner */
1299 grpd.parent_group_id = JTO.rsc_group_id
1300 /* groups I (logged-in user) am 'member' of */
1301 AND grpd.group_id = GRV.group_id
1302 )
1303 AND jto.terr_group_id = P_TG_ID
1304 AND grv.ROLE_CODE = P_ROLE_CODE
1305 AND grv.GROUP_ID = P_GROUP_ID
1306 AND grv.resource_id = P_RESOURCE_ID
1307 AND grv.CURRENT_USER_ID = res.USER_ID
1308 AND jto.resource_id = res.resource_id
1309 AND ROWNUM < 2;
1310
1311
1312 EXCEPTION
1313 WHEN NO_DATA_FOUND THEN
1314 -- x_status := 'E';
1315 x_error_code := 1;
1316 x_return_status := FND_API.G_RET_STS_ERROR;
1317 fnd_message.clear;
1318 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
1319 RETURN;
1320
1321 WHEN OTHERS THEN
1322 -- x_status := 'E';
1323 x_error_code := 4;
1324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1325 put_jty_log('Error in CHECK_VALID_RESOURCE_ADD: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
1326 l_error_msg := substr(sqlerrm,1,200);
1327 fnd_message.clear;
1328 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
1329 fnd_message.set_token('ERRMSG', l_error_msg );
1330 RETURN;
1331 END;
1332
1333
1334 END CHECK_VALID_RESOURCE_ADD;
1335
1336 /* Procedure which checks whether the user can add the given
1337 sales person.*/
1338
1339 PROCEDURE GET_RESOURCE_OWNERS (
1340 p_Api_Version_Number IN NUMBER,
1341 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1342 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1343 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1344 X_Return_Status OUT NOCOPY VARCHAR2,
1345 X_Msg_Count OUT NOCOPY NUMBER,
1346 X_Msg_Data OUT NOCOPY VARCHAR2,
1347 P_TERR_GP_ID in number,
1348 P_RESOURCES_TBL IN SALESREP_RSC_TBL_TYPE,
1349 x_rscs_owners_tbl OUT NOCOPY SALESREP_RSC_OWNERS_TBL_TYPE,
1350 x_error_code out NOCOPY number,
1351 x_status out NOCOPY varchar2) is
1352
1353 l_select varchar2(100);
1354 j integer := 0;
1355 i integer := 0;
1356 p_group_id1 NUMBER DEFAULT NULL ;
1357 p_group_id2 NUMBER DEFAULT NULL ;
1358 p_group_id3 NUMBER DEFAULT NULL ;
1359 p_group_id4 NUMBER DEFAULT NULL ;
1360 p_group_id5 NUMBER DEFAULT NULL ;
1361 p_group_id6 NUMBER DEFAULT NULL ;
1362 p_group_id7 NUMBER DEFAULT NULL ;
1363 p_group_id8 NUMBER DEFAULT NULL ;
1364 p_group_id9 NUMBER DEFAULT NULL ;
1365 p_group_id10 NUMBER DEFAULT NULL ;
1366 p_group_id11 NUMBER DEFAULT NULL ;
1367 p_group_id12 NUMBER DEFAULT NULL ;
1368 p_group_id13 NUMBER DEFAULT NULL ;
1369 p_group_id14 NUMBER DEFAULT NULL ;
1370 p_group_id15 NUMBER DEFAULT NULL ;
1371 p_group_id16 NUMBER DEFAULT NULL ;
1372 p_group_id17 NUMBER DEFAULT NULL ;
1373 p_group_id18 NUMBER DEFAULT NULL ;
1374 p_group_id19 NUMBER DEFAULT NULL ;
1375 p_group_id20 NUMBER DEFAULT NULL ;
1376 p_group_id21 NUMBER DEFAULT NULL ;
1377 p_group_id22 NUMBER DEFAULT NULL ;
1378 p_group_id23 NUMBER DEFAULT NULL ;
1379 p_group_id24 NUMBER DEFAULT NULL ;
1380 p_group_id25 NUMBER DEFAULT NULL ;
1381 p_group_id26 NUMBER DEFAULT NULL ;
1382 p_group_id27 NUMBER DEFAULT NULL ;
1383 p_group_id28 NUMBER DEFAULT NULL ;
1384 p_group_id29 NUMBER DEFAULT NULL ;
1385 p_group_id30 NUMBER DEFAULT NULL ;
1386 l_get_owners varchar2(2000);
1387 l_rsc_groups varchar2(2000);
1388 l_cursor NUMBER;
1389 fdbk INTEGER;
1390 l_error_msg VARCHAR2(250);
1391
1392 cursor get_owners_c
1393 IS
1394 SELECT jto.resource_id, jto.rsc_group_id, jto.rsc_role_code, grpd.group_id
1395 FROM jtf_rs_grp_denorm_vl grpd, jtf_tty_terr_grp_owners jto
1396 WHERE grpd.parent_group_id = JTO.rsc_group_id
1397 AND jto.terr_group_id = P_TERR_GP_ID
1398 AND grpd.group_id IN (p_group_id1, p_group_id2, p_group_id3, p_group_id4, p_group_id5,
1399 p_group_id6, p_group_id7, p_group_id8, p_group_id9, p_group_id10,
1400 p_group_id11, p_group_id12, p_group_id13, p_group_id14, p_group_id15,
1401 p_group_id16, p_group_id17, p_group_id18, p_group_id19, p_group_id20,
1402 p_group_id21, p_group_id22, p_group_id23, p_group_id24, p_group_id25,
1403 p_group_id26, p_group_id27, p_group_id28, p_group_id29, p_group_id30);
1404
1405 BEGIN
1406 -- x_status := 'S';
1407 x_rscs_owners_tbl := SALESREP_RSC_OWNERS_TBL_TYPE();
1408 x_return_status := FND_API.G_RET_STS_SUCCESS;
1409 /* check salesperson for the current TG */
1410 j := 0;
1411 FOR i in P_RESOURCES_TBL.first.. P_RESOURCES_TBL.last LOOP
1412 if (i = 1) then
1413 p_group_id1:= P_RESOURCES_TBL(i).group_id;
1414 elsif (i = 2) then
1415 p_group_id2:= P_RESOURCES_TBL(i).group_id;
1416 elsif (i = 3) then
1417 p_group_id3:= P_RESOURCES_TBL(i).group_id;
1418 elsif (i = 4) then
1419 p_group_id4:= P_RESOURCES_TBL(i).group_id;
1420 elsif (i = 5) then
1421 p_group_id5:= P_RESOURCES_TBL(i).group_id;
1422 elsif (i = 6) then
1423 p_group_id6:= P_RESOURCES_TBL(i).group_id;
1424 elsif (i = 7) then
1425 p_group_id7:= P_RESOURCES_TBL(i).group_id;
1426 elsif (i = 8) then
1427 p_group_id8:= P_RESOURCES_TBL(i).group_id;
1428 elsif (i = 9) then
1429 p_group_id9:= P_RESOURCES_TBL(i).group_id;
1430 elsif (i = 10) then
1431 p_group_id10:= P_RESOURCES_TBL(i).group_id;
1432 elsif (i = 11) then
1433 p_group_id11:= P_RESOURCES_TBL(i).group_id;
1434 elsif (i = 12) then
1435 p_group_id12:= P_RESOURCES_TBL(i).group_id;
1436 elsif (i = 13) then
1437 p_group_id13:= P_RESOURCES_TBL(i).group_id;
1438 elsif (i = 14) then
1439 p_group_id14:= P_RESOURCES_TBL(i).group_id;
1440 elsif (i = 15) then
1441 p_group_id15:= P_RESOURCES_TBL(i).group_id;
1442 elsif (i = 16) then
1443 p_group_id16:= P_RESOURCES_TBL(i).group_id;
1444 elsif (i = 17) then
1445 p_group_id17:= P_RESOURCES_TBL(i).group_id;
1446 elsif (i = 18) then
1447 p_group_id18:= P_RESOURCES_TBL(i).group_id;
1448 elsif (i = 19) then
1449 p_group_id19:= P_RESOURCES_TBL(i).group_id;
1450 elsif (i = 20) then
1451 p_group_id20:= P_RESOURCES_TBL(i).group_id;
1452 elsif (i = 21) then
1453 p_group_id21:= P_RESOURCES_TBL(i).group_id;
1454 elsif (i = 22) then
1455 p_group_id22:= P_RESOURCES_TBL(i).group_id;
1456 elsif (i = 23) then
1457 p_group_id23:= P_RESOURCES_TBL(i).group_id;
1458 elsif (i = 24) then
1459 p_group_id24:= P_RESOURCES_TBL(i).group_id;
1460 elsif (i = 25) then
1461 p_group_id25:= P_RESOURCES_TBL(i).group_id;
1462 elsif (i = 26) then
1463 p_group_id26:= P_RESOURCES_TBL(i).group_id;
1464 elsif (i = 27) then
1465 p_group_id27:= P_RESOURCES_TBL(i).group_id;
1466 elsif (i = 28) then
1467 p_group_id28:= P_RESOURCES_TBL(i).group_id;
1468 elsif (i = 29) then
1469 p_group_id29:= P_RESOURCES_TBL(i).group_id;
1470 elsif (i = 30) then
1471 p_group_id30:= P_RESOURCES_TBL(i).group_id;
1472 end if;
1473 END LOOP;
1474
1475 /* dynamic sql
1476 FOR i in P_RESOURCES_TBL.first.. P_RESOURCES_TBL.last LOOP
1477 if (i = 1) then
1478 l_rsc_groups := P_RESOURCES_TBL(i).group_id;
1479 else
1480 l_rsc_groups := l_rsc_groups || ',' || P_RESOURCES_TBL(i).group_id;
1481 end if;
1482 END LOOP;
1483 l_get_owners :=
1484 'SELECT jto.resource_id, jto.rsc_group_id, jto.rsc_role_code, grpd.group_id' ||
1485 ' FROM jtf_rs_grp_denorm_vl grpd, jtf_tty_terr_grp_owners jto ' ||
1486 ' WHERE grpd.parent_group_id = JTO.rsc_group_id' ||
1487 ' AND jto.terr_group_id = :P_TERR_GP_ID ' ||
1488 ' AND grpd.group_id IN (' || l_rsc_groups || ')';
1489 l_cursor := -- dbms_SQL.OPEN_CURSOR;
1490 -- dbms_SQL.PARSE ( l_Cursor, l_get_owners, -- dbms_SQL.NATIVE );
1491 -- dbms_SQL.BIND_VARIABLE (l_cursor, ':P_TERR_GP_ID', P_TERR_GP_ID );
1492 fdbk := -- dbms_SQL.EXECUTE(l_cursor);
1493 LOOP
1494 /* Fetch next row. Exit when done. */
1495 /*
1496 EXIT WHEN -- dbms_SQL.FETCH_ROWS (cur) = 0;
1497 -- dbms_SQL.COLUMN_VALUE (l_Cursor, 1, rec.employee_id);
1498 -- dbms_SQL.COLUMN_VALUE (l_Cursor, 2, rec.last_name);
1499 -- dbms_SQL.COLUMN_VALUE (l_Cursor, 3, rec.last_name);
1500 -- dbms_SQL.COLUMN_VALUE (l_Cursor, 4, rec.last_name);
1501 -- dbms_output.put_line (
1502 TO_CHAR (rec.employee_id) || '=' ||
1503 rec.last_name);
1504 END LOOP;
1505
1506 -- dbms_SQL.CLOSE_CURSOR (cur);
1507 * end of dynamic sql */
1508 FOR OWNERS IN get_owners_c LOOP
1509 j := j+1;
1510 x_rscs_owners_tbl.extend();
1511 x_rscs_owners_tbl(j).owner_resource_id := owners.resource_id;
1512 x_rscs_owners_tbl(j).owner_group_id := owners.rsc_group_id;
1513 x_rscs_owners_tbl(j).owner_role_code := owners.rsc_role_code;
1514 x_rscs_owners_tbl(j).group_id := owners.group_id;
1515 END LOOP;
1516
1517 EXCEPTION
1518 WHEN NO_DATA_FOUND THEN
1519 -- x_status := 'E';
1520 x_error_code := 1;
1521 x_return_status := FND_API.G_RET_STS_ERROR;
1522 fnd_message.clear;
1523 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
1524 RETURN;
1525
1526 WHEN OTHERS THEN
1527 -- x_status := 'E';
1528 x_error_code := 4;
1529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1530 l_error_msg := substr(sqlerrm,1,200);
1531 fnd_message.clear;
1532 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
1533 fnd_message.set_token('ERRMSG', l_error_msg );
1534 put_jty_log('Error in GET_RESOURCE_OWNERS: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
1535 RETURN;
1536
1537
1538 END GET_RESOURCE_OWNERS;
1539
1540 /* If the account is not assigned to anyone in the tg owners hierarchy
1541 assign to the owner(s) */
1542 PROCEDURE ASSIGN_UA_ACCT_TO_TGOWNERS(
1543 p_Api_Version_Number IN NUMBER,
1544 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1545 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1546 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1547 X_Return_Status OUT NOCOPY VARCHAR2,
1548 X_Msg_Count OUT NOCOPY NUMBER,
1549 X_Msg_Data OUT NOCOPY VARCHAR2,
1550 P_TERR_GP_ACCT_ID IN NUMBER,
1551 P_TERR_GP_ID IN NUMBER)
1552 AS
1553 cursor getTerrgpOwners(id IN NUMBER) is
1554 select resource_id,
1555 rsc_group_id,
1556 rsc_role_code
1557 from jtf_tty_terr_grp_owners
1558 where terr_group_id = id;
1559
1560 p_owner_user_rsc_id NUMBER;
1561 p_owner_group_id NUMBER;
1562 p_owner_role_code VARCHAR2(60);
1563 p_exists_flag VARCHAR2(1);
1564 l_error_msg VARCHAR2(250);
1565
1566 BEGIN
1567 -- dbms_output.put_line('Sandeep - In ASSIGN_UA_ACCT_TO_TGOWNERS ' || P_TERR_GP_ACCT_ID );
1568 x_return_status := FND_API.G_RET_STS_SUCCESS;
1569 FOR owners_c IN getTerrgpOwners(p_terr_gp_id) LOOP
1570 p_owner_user_rsc_id := owners_c.resource_id;
1571 p_owner_group_id := owners_c.rsc_group_id;
1572 p_owner_role_code :=owners_c.rsc_role_code;
1573 BEGIN
1574 SELECT 'X'
1575 INTO p_exists_flag
1576 FROM jtf_tty_terr_grp_accts ga,
1577 jtf_tty_my_resources_v repdn,
1578 jtf_tty_named_acct_rsc narsc
1579 WHERE ga.terr_group_account_id = narsc.terr_group_account_id
1580 AND narsc.resource_id = repdn.resource_id
1581 AND narsc.rsc_group_id = repdn.group_id
1582 AND narsc.rsc_role_code = repdn.role_code
1583 AND repdn.parent_group_id = p_owner_group_id
1584 AND repdn.current_user_rsc_id = p_owner_user_rsc_id
1585 AND repdn.current_user_role_code = p_owner_role_code
1586 AND ga.terr_group_id = p_terr_gp_id
1587 AND ga.terr_group_account_id = p_terr_gp_acct_id
1588 AND ROWNUM < 2;
1589 EXCEPTION
1590 WHEN NO_DATA_FOUND THEN
1591 -- dbms_output.put_line('Sandeep - ASSIGN_UA_ACCT_TO_TGOWNERS: Doing insert ' || P_TERR_GP_ACCT_ID );
1592 /* Assign tga to the owner p_owner_user_rsc_id, p_owner_group_id, p_owner_role_code */
1593 INSERT INTO jtf_tty_named_acct_rsc(
1594 ACCOUNT_RESOURCE_ID,
1595 OBJECT_VERSION_NUMBER,
1596 TERR_GROUP_ACCOUNT_ID,
1597 RESOURCE_ID,
1598 RSC_GROUP_ID ,
1599 RSC_ROLE_CODE ,
1600 RSC_RESOURCE_TYPE ,
1601 ASSIGNED_FLAG ,
1602 CREATED_BY,
1603 CREATION_DATE ,
1604 LAST_UPDATED_BY ,
1605 LAST_UPDATE_DATE ,
1606 LAST_UPDATE_LOGIN )
1607 VALUES(jtf_tty_named_acct_rsc_s.nextval,
1608 1,
1609 p_terr_gp_acct_id,
1610 p_owner_user_rsc_id,
1611 p_owner_group_id,
1612 p_owner_role_code,
1613 'RS_EMPLOYEE',
1614 'N',
1615 fnd_global.user_id,
1616 sysdate,
1617 fnd_global.user_id,
1618 sysdate,
1619 null);
1620 END;
1621 END LOOP;
1622 EXCEPTION
1623 WHEN OTHERS THEN
1624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1625 -- dbms_output.put_line('Sandeep - Error 2 In ASSIGN_UA_ACCT_TO_TGOWNERS ' || P_TERR_GP_ACCT_ID );
1626 l_error_msg := substr(sqlerrm,1,200);
1627 fnd_message.clear;
1628 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
1629 fnd_message.set_token('ERRMSG', l_error_msg );
1630 put_jty_log('Error in ASSIGN_UA_ACCT_TO_TGOWNERS: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
1631 RETURN;
1632
1633 END ASSIGN_UA_ACCT_TO_TGOWNERS;
1634
1635 --**************************************
1636 -- PROCEDURE POPULATE_SALES_TEAM
1637 --**************************************
1638
1639 -- input:
1640 -- [list of] lp_resource_id, lp_group_id, lp_role_code
1641 -- [list of] lp_party_id
1642 -- FROM CALLING PAGE
1643 -- lp_current_user_resource_id NOTE THIS PARAMETER NO LONGER USED
1644 -- p_user_attribute1 IS NOW USED INSTEAD, value is USER_ID
1645 -- lp_territory_group_id
1646
1647 PROCEDURE POPULATE_SALES_TEAM(
1648 p_api_version_number IN NUMBER,
1649 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1650 p_SQL_Trace IN VARCHAR2,
1651 p_Debug_Flag IN VARCHAR2,
1652 x_return_status OUT NOCOPY VARCHAR2,
1653 x_msg_count OUT NOCOPY NUMBER,
1654 x_msg_data OUT NOCOPY VARCHAR2,
1655 p_from_where in VARCHAR2,
1656 p_user_resource_id IN NUMBER, -- NOTE THIS IS NOT USED, user_attr1 used for user_id instead.
1657 p_terr_group_id IN NUMBER,
1658 p_user_attribute1 IN VARCHAR2,
1659 p_user_attribute2 IN VARCHAR2,
1660 p_added_rscs_tbl IN SALESREP_RSC_TBL_TYPE,
1661 p_removed_rscs_tbl IN SALESREP_RSC_TBL_TYPE,
1662 p_affected_parties_tbl IN AFFECTED_PARTY_TBL_TYPE,
1663 ERRBUF OUT NOCOPY VARCHAR2,
1664 RETCODE OUT NOCOPY VARCHAR2
1665 )
1666 IS
1667
1668 l_user_id NUMBER := p_user_attribute1;
1669 /*lp_user_resource_id NUMBER := p_user_resource_id; */
1670 lp_user_resource_type VARCHAR2(30) := 'RS_EMPLOYEE';
1671 lp_terr_group_id NUMBER := p_terr_group_id;
1672
1673 lp_resource_id NUMBER;
1674 lp_group_id NUMBER;
1675 lp_role_code VARCHAR2(300);
1676 lp_mgr_resource_id NUMBER;
1677 lp_mgr_group_id NUMBER;
1678 lp_mgr_role_code VARCHAR2(300);
1679 lp_resource_type VARCHAR2(300);
1680 t_resource_id NUMBER;
1681 t_resource_type VARCHAR2(19);
1682
1683 lp_named_account_id NUMBER;
1684
1685 l_role_code VARCHAR2(300);
1686 l_terr_group_account_id NUMBER;
1687 l_directs_on_account NUMBER := 0;
1688 l_assign_flag VARCHAR2(1);
1689 l_resource_id_is_leaf VARCHAR2(1);
1690 l_assigned_rsc_exists NUMBER := 0; -- 0 if no assigned rsc exists, 1 otherwise
1691
1692 l_find_subs VARCHAR2(1); -- are we processing subsidiaries?
1693 l_master_pty_last NUMBER; -- last index of the master parties
1694 l_sub_pty_index NUMBER; -- index where the subsidiaries will be added
1695 l_acct_rsc_exist_count NUMBER; -- verify if existing rsc/group/role exists
1696
1697 l_change_id NUMBER;
1698 l_user number;
1699 l_login_id number;
1700 l_error_msg VARCHAR2(250);
1701
1702
1703 new_seq_acct_rsc_id NUMBER;
1704 new_seq_acct_rsc_dn_id NUMBER;
1705 new_seq_RESOURCE_ACCT_SUMM_ID NUMBER;
1706
1707
1708 -- LIST OF ALL GROUPS A GIVEN RESOURCE OWNS IN THE CONTEXT OF A PARENT GRUOP
1709 cursor c_rsc_owned_grps(cl_parent_resource_id number, cl_group_id number) is
1710 SELECT mgr.resource_id, mgr.group_id
1711 FROM jtf_rs_rep_managers mgr,
1712 jtf_rs_groups_denorm gd
1713 WHERE mgr.hierarchy_type = 'MGR_TO_MGR'
1714 AND mgr.resource_id = mgr.parent_resource_id
1715 AND trunc(sysdate) BETWEEN mgr.start_date_active
1716 AND NVL(mgr.end_date_active,trunc(sysdate))
1717 AND mgr.group_id = gd.group_id
1718 AND gd.parent_group_id = cl_group_id
1719 AND mgr.resource_id = cl_parent_resource_id
1720 AND rownum < 2;
1721
1722
1723 -- LIST OF ALL DIRECTS TO REMOVE WHEN REMOVING A MANAGING RESOURCE
1724 -- FROM A NAMED ACCOUNT IN THE CONTEXT OF A GROUP
1725 cursor c_rsc_directs(cl_parent_resource_id number, cl_group_id number) is
1726 SELECT DISTINCT RESOURCE_ID
1727 FROM JTF_RS_REP_MANAGERS
1728 WHERE group_id = cl_group_id
1729 and resource_id <> cl_parent_resource_id
1730 and parent_resource_id = cl_parent_resource_id;
1731
1732
1733 -- ALL SUBSIDIARIES OF cl_party_id that is owned by lp_user_resource_id, p_terr_group_id
1734 -- QUERY MODIFIED FOR TERR_GROUP_ACCOUNT_ID IN/OUT
1735 cursor c_subsidiaries(cl_terr_group_account_id number) is
1736 select distinct gao.terr_group_account_id
1737 from hz_relationships hzr,
1738 jtf_tty_named_accts nai,
1739 jtf_tty_terr_grp_accts gai,
1740 jtf_tty_named_accts nao,
1741 jtf_tty_terr_grp_accts gao
1742 where gao.named_account_id = nao.named_account_id
1743 and nao.party_id = hzr.object_id -- these are the subsidiary parties
1744 and hzr.subject_table_name = 'HZ_PARTIES'
1745 and hzr.object_table_name = 'HZ_PARTIES'
1746 and hzr.relationship_code IN ( 'GLOBAL_ULTIMATE_OF', 'HEADQUARTERS_OF', 'DOMESTIC_ULTIMATE_OF', 'PARENT_OF' )
1747 and hzr.status = 'A'
1748 and sysdate between hzr.start_date and nvl( hzr.end_date, sysdate)
1749 and hzr.subject_id = nai.party_id -- this is the parent party
1750 and nai.named_account_id = gai.named_account_id
1751 and gai.terr_group_account_id = cl_terr_group_account_id
1752 -- subsidiaries that are owned by user
1753 and exists( select 'Y'
1754 from jtf_tty_named_acct_rsc narsc ,
1755 jtf_tty_my_resources_v repdn
1756 -- jtf_tty_named_accts na,
1757 -- jtf_tty_terr_grp_accts ga
1758 where narsc.terr_group_account_id = gao.terr_group_account_id
1759 -- and ga.named_account_id = na.named_account_id
1760 and narsc.resource_id = repdn.resource_id
1761 and narsc.rsc_group_id = repdn.group_id
1762 and repdn.current_user_id = l_user_id );
1763
1764
1765
1766 /* this cursor return the managers details for the logged in person group with respect to the
1767 effected resource */
1768
1769 cursor c_groups_manager(cl_current_user_id number, cl_eff_resource_id number ) is
1770 select grv.resource_id, grv.group_id, grv.role_code
1771 from
1772 jtf_tty_my_resources_v grv
1773 , JTF_RS_GROUPS_DENORM grpd
1774 , jtf_rs_roles_b rol
1775 WHERE grpd.parent_group_id = grv.parent_group_id
1776 and grpd.group_id IN ( select grv1.group_id
1777 from jtf_rs_group_members grv1
1778 where grv1.resource_id = cl_eff_resource_id )
1779 and grv.CURRENT_USER_ID = cl_current_user_id
1780 and grv.group_id = grv.parent_group_id
1781 and grv.role_code = rol.role_code
1782 and rol.manager_flag = 'Y';
1783
1784
1785
1786 BEGIN
1787
1788 /***********************************************************
1789 **** PHASE 0: API INTERNAL OPTIMIZATIONS
1790 **** Populate p_affected_parties_tbl with subsidiaries
1791 **** if ASSIGN_SUBSIDIARIES has been selected for any resource
1792 ************************************************************/
1793
1794
1795 l_user := fnd_global.USER_ID;
1796 l_login_id := fnd_global.LOGIN_ID;
1797 x_return_status := fnd_api.g_ret_sts_success;
1798
1799
1800
1801 -- tag all incoming accounts as non-subsidiary record
1802 IF (p_affected_parties_tbl is not null) THEN
1803 IF (p_affected_parties_tbl.last > 0) THEN
1804 -- TAG the original inputs for affected parties
1805 FOR n in p_affected_parties_tbl.first.. p_affected_parties_tbl.last LOOP
1806 -- p_affected_parties_tbl(n).attribute1 := 'N';
1807 null;
1808 END LOOP;
1809 END IF;
1810 END IF;
1811
1812
1813 ---------------------------------------------
1814 -- REMOVING RESOURCES IN SALES TEAM
1815 ---------------------------------------------
1816 -- Delete resource being removed from account (ALONG WITH ALL HIS DIRECTS)
1817 IF ((p_affected_parties_tbl is not null) and (p_removed_rscs_tbl is not null)) THEN
1818 IF ((p_affected_parties_tbl.last > 0) and (p_removed_rscs_tbl.last > 0)) THEN
1819
1820 FOR j in p_affected_parties_tbl.first.. p_affected_parties_tbl.last LOOP
1821 -- dbms_output.put_line('p_affected_parties_tbl ' || j || p_affected_parties_tbl(j).party_id);
1822
1823 -- each named account exists in context of a territory group for resource
1824 l_terr_group_account_id := p_affected_parties_tbl(j).terr_group_account_id;
1825
1826 /* JRADHAKR: Inserting values to jtf_tty_named_acct_changes table for GTP
1827 to do an incremental and Total Mode */
1828 /* by shli, GSST Decom */
1829 /*
1830 select jtf_tty_named_acct_changes_s.nextval
1831 into l_change_id
1832 from sys.dual;
1833
1834 insert into jtf_tty_named_acct_changes
1835 ( NAMED_ACCT_CHANGE_ID
1836 , OBJECT_VERSION_NUMBER
1837 , OBJECT_TYPE
1838 , OBJECT_ID
1839 , CHANGE_TYPE
1840 , FROM_WHERE
1841 , CREATED_BY
1842 , CREATION_DATE
1843 , LAST_UPDATED_BY
1844 , LAST_UPDATE_DATE
1845 , LAST_UPDATE_LOGIN
1846 )
1847 VALUES (
1848 l_change_id
1849 , 1
1850 , 'TGA'
1851 , l_terr_group_account_id
1852 , 'UPDATE'
1853 , 'UPDATE SALES TEAM'
1854 , l_user
1855 , sysdate
1856 , l_user
1857 , sysdate
1858 , l_login_id
1859 );
1860 */
1861
1862 FOR i in p_removed_rscs_tbl.first.. p_removed_rscs_tbl.last LOOP
1863 -- dbms_output.put_line('p_removed_rscs_tbl ' || i || p_removed_rscs_tbl(i).resource_id);
1864
1865 IF ((p_removed_rscs_tbl(i).attribute1 = 'Y') OR
1866 (p_removed_rscs_tbl(i).attribute1 = 'N' and p_affected_parties_tbl(j).attribute1 = 'N')
1867 )
1868 THEN
1869 lp_resource_id := p_removed_rscs_tbl(i).resource_id;
1870 lp_group_id := p_removed_rscs_tbl(i).group_id;
1871 lp_role_code := p_removed_rscs_tbl(i).role_code;
1872 lp_resource_type := p_removed_rscs_tbl(i).resource_type;
1873 lp_mgr_resource_id := p_removed_rscs_tbl(i).mgr_resource_id;
1874
1875 -- delete resource to be removed from sales team
1876 -- dbms_output.put_line('DELETING FROM jtf_tty_named_acct_rsc ');
1877 -- dbms_output.put_line(' ' ||
1878 -- ' //l_terr_group_account_id=' || l_terr_group_account_id ||
1879 -- ' //lp_resource_id=' || lp_resource_id ||
1880 -- ' //lp_group_id=' || lp_group_id ||
1881 -- ' //lp_role_code=' || lp_role_code || '//');
1882
1883 delete from jtf_tty_named_acct_rsc
1884 where rsc_group_id = lp_group_id
1885 and rsc_role_code = lp_role_code
1886 and terr_group_account_id = l_terr_group_account_id
1887 and resource_id = lp_resource_id;
1888
1889
1890
1891 -- if no one in user's hierarhy is assigned to this account
1892 -- after this delete, add user to this account
1893
1894 -- if no one in user's hierarhy is assigned to this account
1895 -- after this delete, set assigned_to_direct_flag to 'N' for user's NA
1896
1897 -- ACHANDA : bug 3265188 : change the IN clause to EXISTS to improve performance
1898
1899 if (p_from_where <> 'ADMIN') THEN
1900 select count(*) INTO l_directs_on_account
1901 from jtf_tty_named_acct_rsc ar
1902 where ar.terr_group_account_id = l_terr_group_account_id
1903 and exists (
1904 select 1
1905 from jtf_tty_my_resources_v grv
1906 , jtf_rs_groups_denorm grpd
1907 WHERE ar.resource_id = grv.resource_id
1908 and grpd.parent_group_id = grv.parent_group_id
1909 and exists (
1910 select 1
1911 from jtf_rs_group_members grv1
1912 where grpd.group_id = grv1.group_id
1913 and grv1.resource_id = lp_resource_id )
1914 and grv.CURRENT_USER_ID = l_user_id )
1915 and rownum < 2;
1916
1917 -- dbms_output.put_line('l_directs_on_account = ' || l_directs_on_account);
1918
1919 IF l_directs_on_account = 0 THEN
1920 select jtf_tty_named_acct_rsc_s.nextval into new_seq_acct_rsc_id
1921 from dual;
1922
1923 lp_mgr_group_id := p_removed_rscs_tbl(i).mgr_group_id;
1924 lp_mgr_role_code := p_removed_rscs_tbl(i).mgr_role_code;
1925
1926 -- assigned flag N because user did not assign himself.
1927 -- It is an auto assign to user when none of his directs are assigned.
1928 insert into jtf_tty_named_acct_rsc (
1929 account_resource_id,
1930 object_version_number,
1931 terr_group_account_id,
1932 resource_id,
1933 rsc_group_id,
1934 rsc_role_code,
1935 assigned_flag,
1936 rsc_resource_type,
1937 created_by,
1938 creation_date,
1939 last_updated_by,
1940 last_update_date
1941 )
1942 VALUES (
1943 new_seq_acct_rsc_id, --account_resource_id,
1944 2, --object_version_number
1945 l_terr_group_account_id, --terr_group_account_id
1946 lp_mgr_resource_id, --resource_id,
1947 lp_mgr_group_id, --rsc_group_id,
1948 lp_mgr_role_code, --rsc_role_code,
1949 'N', --assigned_flag,
1950 lp_user_resource_type, --rsc_resource_type
1951 1, --created_by
1952 sysdate, --creation_date
1953 1, --last_updated_by
1954 sysdate --last_update_date
1955 );
1956
1957
1958
1959
1960 END IF; --l_directs_on_account = 0?
1961 END IF; -- not for admin
1962 -- LOOP THROUGH ALL SUBORDINATES OF THIS RESOURCE_ID
1963 -- remove all directs of this rem_resource_id from account
1964 -- this cursor does not include lp_resource_id itself.
1965
1966 --bug 2828011: do not remove directs if user removing self.
1967 IF lp_mgr_resource_id <> lp_resource_id THEN
1968 /* not required as we do not have denorm table */
1969 /*
1970 FOR crd IN c_rsc_directs(lp_resource_id, lp_group_id) LOOP
1971 -- delete subordinates from JTF_TTY_NAMED_ACCT_RSC
1972 DELETE FROM JTF_TTY_NAMED_ACCT_RSC
1973 WHERE rsc_role_code = lp_role_code
1974 AND terr_group_account_id = l_terr_group_account_id
1975 AND resource_id = crd.resource_id;
1976
1977
1978 END LOOP; -- c_rsc_directs
1979 */
1980 null;
1981 END IF; -- lp_user_resource_id <> lp_resource_id ?
1982
1983 END IF; -- process this? (subsidiary logic)
1984
1985 END LOOP; -- p_removed_rscs_tbl
1986
1987 END LOOP; -- p_affected_parties_tbl
1988
1989 END IF; -- ((p_affected_parties_tbl.last > 0) and (p_removed_rscs_tbl.last > 0))
1990 END IF; -- ((p_affected_parties_tbl is not null) and (p_removed_rscs_tbl is not null))
1991 /***********************************************************
1992 **** PHASE I: DATAMODEL MODIFICATIONS
1993 **** Changes made only to JTF_TTY_NAMED_ACCT_RSC
1994 **** JTF_TTY_ACCT_RSC_DN
1995 ************************************************************/
1996 -- dbms_output.put_line('PHASE I ');
1997
1998 ---------------------------------------------
1999 -- ADDING RESOURCES TO SALES TEAM
2000 ---------------------------------------------
2001 IF ((p_affected_parties_tbl is not null) and (p_added_rscs_tbl is not null)) THEN
2002 IF ((p_affected_parties_tbl.last > 0) and (p_added_rscs_tbl.last > 0)) THEN
2003
2004 FOR j in p_affected_parties_tbl.first.. p_affected_parties_tbl.last LOOP
2005 -- dbms_output.put_line('Adding Resources to: p_affected_parties_tbl =' || j || p_affected_parties_tbl(j).party_id);
2006
2007 -- each named account exists in context of a territory group for resource
2008 l_terr_group_account_id := p_affected_parties_tbl(j).terr_group_account_id;
2009
2010 FOR i in p_added_rscs_tbl.first.. p_added_rscs_tbl.last LOOP
2011 -- dbms_output.put_line('Resource being Added: p_added_rscs_tbl =' || i || p_added_rscs_tbl(i).resource_id);
2012
2013 IF ((p_added_rscs_tbl(i).attribute1 = 'Y') OR
2014 (p_added_rscs_tbl(i).attribute1 = 'N' and p_affected_parties_tbl(j).attribute1 = 'N')
2015 )
2016 THEN
2017
2018 lp_resource_id := p_added_rscs_tbl(i).resource_id;
2019 lp_group_id := p_added_rscs_tbl(i).group_id;
2020 lp_role_code := p_added_rscs_tbl(i).role_code;
2021 lp_resource_type := p_added_rscs_tbl(i).resource_type;
2022 lp_mgr_resource_id := p_added_rscs_tbl(i).mgr_resource_id;
2023
2024 -- method of processing depends on whether resource is the user. Bug: 2816957
2025 if lp_mgr_resource_id = lp_resource_id then
2026 -- DOES RECORD PROCESSED EXIST? Bug: 2729383
2027 select count(*) into l_acct_rsc_exist_count
2028 from (
2029 select account_resource_id
2030 from jtf_tty_named_acct_rsc
2031 where resource_id = lp_resource_id
2032 and rsc_group_id = lp_group_id
2033 and rsc_role_code = lp_role_code
2034 and terr_group_account_id = l_terr_group_account_id
2035 and assigned_flag = 'Y' -- still need a Y assign flag on NA/RSC to abort addition.
2036 and rownum < 2
2037 );
2038 else
2039 -- DOES RECORD PROCESSED EXIST? Bug: 2729383
2040 select count(*) into l_acct_rsc_exist_count
2041 from (
2042 select account_resource_id
2043 from jtf_tty_named_acct_rsc
2044 where resource_id = lp_resource_id
2045 and rsc_group_id = lp_group_id
2046 and rsc_role_code = lp_role_code
2047 and terr_group_account_id = l_terr_group_account_id
2048 -- and assigned_flag = 'Y' bug 2803830
2049 and rownum < 2
2050 );
2051
2052 end if;
2053
2054
2055 -- DOES RECORD TO BE PROCESSED EXIST?
2056 IF l_acct_rsc_exist_count = 0 THEN
2057
2058 --is user resource_id a leaf node in hierarchy?
2059 l_resource_id_is_leaf := 'Y';
2060 FOR crd IN c_rsc_owned_grps(lp_resource_id, lp_group_id) LOOP
2061 l_resource_id_is_leaf := 'N';
2062 EXIT;
2063 END LOOP; -- c_rsc_directs
2064
2065 -- set l_assign_flag for account
2066 IF ( (lp_resource_id = lp_mgr_resource_id)
2067 OR (l_resource_id_is_leaf = 'Y'))
2068 THEN l_assign_flag := 'Y';
2069 ELSE l_assign_flag := 'N';
2070 END IF;
2071
2072 -- test if record already exists for this rsc/grp/role with assign Y
2073
2074
2075 -- insert into jtf_tty_named_acct_rsc
2076 select jtf_tty_named_acct_rsc_s.nextval into new_seq_acct_rsc_id
2077 from dual;
2078
2079 -- dbms_output.put_line('inserting to jtf_tty_named_acct_rsc ');
2080 -- dbms_output.put_line(' ' || ' //new_seq_acct_rsc_id=' || new_seq_acct_rsc_id ||
2081 --' //l_terr_group_account_id=' || l_terr_group_account_id ||' //lp_resource_id=' ||
2082 -- lp_resource_id ||' //lp_group_id=' || lp_group_id
2083 --|| l_assign_flag ||' //lp_resource_type=' || lp_resource_type );
2084
2085 -- assigned flag Y because user is assigning this individual, may be himself.
2086 insert into jtf_tty_named_acct_rsc (
2087 account_resource_id,
2088 object_version_number,
2089 terr_group_account_id,
2090 resource_id,
2091 rsc_group_id,
2092 rsc_role_code,
2093 assigned_flag,
2094 rsc_resource_type,
2095 created_by,
2096 creation_date,
2097 last_updated_by,
2098 last_update_date,
2099 attribute1,
2100 attribute2,
2101 attribute3,
2102 attribute4,
2103 attribute5,
2104 START_DATE,
2105 END_DATE
2106 )
2107 VALUES (
2108 new_seq_acct_rsc_id, --account_resource_id,
2109 2, --object_version_number
2110 l_terr_group_account_id, --terr_group_account_id
2111 lp_resource_id, --resource_id,
2112 lp_group_id, --rsc_group_id,
2113 lp_role_code, --rsc_role_code,
2114 l_assign_flag, --assigned_flag,
2115 lp_resource_type, --rsc_resource_type
2116 1, --created_by
2117 sysdate, --creation_date
2118 1, --last_updated_by
2119 sysdate --last_update_date
2120 ,p_added_rscs_tbl(i).RESOURCE_ATT1
2121 ,p_added_rscs_tbl(i).RESOURCE_ATT2
2122 ,p_added_rscs_tbl(i).RESOURCE_ATT3
2123 ,p_added_rscs_tbl(i).RESOURCE_ATT4
2124 ,p_added_rscs_tbl(i).RESOURCE_ATT5
2125 ,p_added_rscs_tbl(i).RESOURCE_START_DATE
2126 ,p_added_rscs_tbl(i).RESOURCE_END_DATE
2127 );
2128
2129 -- if user exists as an account resource w/assign_flag = N then
2130 -- delete user for this account from JTF_TTY_NAMED_ACCT_RSC
2131 -- dbms_output.put_line('deleting from JTF_TTY_NAMED_ACCT_RSC:' || '//lp_group_id:'
2132 --|| lp_group_id ||'//p_role_code:' ||lp_role_code ||'//l_terr_group_account_id:'
2133 --||l_terr_group_account_id || '//lp_user_resource_id:' || lp_user_resource_id);
2134 -- Bug: 2726632
2135 -- Bug: 2732533
2136
2137 /* JRADHAKR: Inserting values to jtf_tty_named_acct_changes table for GTP
2138 to do an incremental and Total Mode */
2139 /* by shli, GSST Decom */
2140 /* select jtf_tty_named_acct_changes_s.nextval
2141 into l_change_id
2142 from sys.dual;
2143
2144 insert into jtf_tty_named_acct_changes
2145 ( NAMED_ACCT_CHANGE_ID
2146 , OBJECT_VERSION_NUMBER
2147 , OBJECT_TYPE
2148 , OBJECT_ID
2149 , CHANGE_TYPE
2150 , FROM_WHERE
2151 , CREATED_BY
2152 , CREATION_DATE
2153 , LAST_UPDATED_BY
2154 , LAST_UPDATE_DATE
2155 , LAST_UPDATE_LOGIN
2156 )
2157 VALUES (
2158 l_change_id
2159 , 1
2160 , 'TGA'
2161 , l_terr_group_account_id
2162 , 'UPDATE'
2163 , 'UPDATE SALES TEAM'
2164 , l_user
2165 , sysdate
2166 , l_user
2167 , sysdate
2168 , l_login_id
2169 );
2170 */
2171
2172 delete from jtf_tty_named_acct_rsc
2173 where 1=1
2174 --and rsc_group_id = lp_group_id
2175 --and rsc_role_code = lp_role_code
2176 and terr_group_account_id = l_terr_group_account_id
2177 and resource_id = lp_mgr_resource_id
2178 and assigned_flag = 'N';
2179
2180 ELSE
2181 -- add 06/05/2006 bug 5246668, update resource attributes
2182 update jtf_tty_named_acct_rsc
2183 set attribute1 = p_added_rscs_tbl(i).RESOURCE_ATT1,
2184 attribute2 = p_added_rscs_tbl(i).RESOURCE_ATT2,
2185 attribute3 = p_added_rscs_tbl(i).RESOURCE_ATT3,
2186 attribute4 = p_added_rscs_tbl(i).RESOURCE_ATT4,
2187 attribute5 = p_added_rscs_tbl(i).RESOURCE_ATT5,
2188 start_date = p_added_rscs_tbl(i).RESOURCE_START_DATE,
2189 end_date = p_added_rscs_tbl(i).RESOURCE_END_DATE
2190 where resource_id = lp_resource_id
2191 and rsc_group_id = lp_group_id
2192 and rsc_role_code = lp_role_code
2193 and terr_group_account_id = l_terr_group_account_id;
2194
2195 END IF; -- DOES RECORD TO BE PROCESSED EXIST?
2196
2197 END IF; -- process this? (subsidiary logic)
2198
2199 END LOOP; -- p_added_rscs_tbl
2200
2201 END LOOP; -- LOOP p_affected_parties_tbl
2202
2203 END IF; --((p_affected_parties_tbl.last > 0) and (p_added_rscs_tbl.last > 0))
2204 END IF; --((p_affected_parties_tbl is not null) and (p_added_rscs_tbl is not null))
2205
2206 exception
2207
2208 when others then
2209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2210 l_error_msg := substr(sqlerrm,1,200);
2211 fnd_message.clear;
2212 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2213 fnd_message.set_token('ERRMSG', l_error_msg );
2214 put_jty_log('Error in POPULATE_SALES_TEAM: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
2215 return;
2216
2217 END POPULATE_SALES_TEAM;
2218
2219 /* Procedure to check if all the salespersons provided in the excel document
2220 * are valid. It internally calls validate_resource to see if the salespersons
2221 * are valid from Resource Manager's data and are in TG's role access.
2222 */
2223
2224 PROCEDURE VALIDATE_SALES_TEAM(
2225 p_Api_Version_Number IN NUMBER,
2226 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2227 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2228 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2229 X_Return_Status OUT NOCOPY VARCHAR2,
2230 X_Msg_Count OUT NOCOPY NUMBER,
2231 X_Msg_Data OUT NOCOPY VARCHAR2,
2232 P_TERR_GP_ID IN NUMBER,
2233 P_START_DATE IN DATE,
2234 P_END_DATE IN DATE,
2235 l_excel_rscs_tbl IN EXCEL_SALESREP_RSC_TBLTYP,
2236 x_added_rscs_tbl OUT NOCOPY SALESREP_RSC_TBL_TYPE)
2237 AS
2238 j integer := 0;
2239 p_resource_name varchar2(300);
2240 p_group_name varchar2(300);
2241 p_role_name varchar2(300);
2242 errbuf varchar2(2000);
2243 retcode number;
2244 X_RESOURCE_id number;
2245 x_group_id number;
2246 x_role_code varchar2(30);
2247 x_error_code varchar2(2);
2248 x_status varchar2(3);
2249 l_error_msg VARCHAR2(250);
2250 l_rsc_start_date date;
2251 l_rsc_end_date date;
2252 BEGIN
2253 -- dbms_output.put_line('Sandeep - Begin update sales team: after validate resource');
2254 x_return_status := FND_API.G_RET_STS_SUCCESS;
2255 x_added_rscs_tbl := SALESREP_RSC_TBL_TYPE();
2256 if l_excel_rscs_tbl.FIRST is not null THEN
2257 FOR i in l_excel_rscs_tbl.first.. l_excel_rscs_tbl.last LOOP
2258 if l_excel_rscs_tbl(i).resource_name is not null
2259 or
2260 l_excel_rscs_tbl(i).group_name is not null
2261 or
2262 l_excel_rscs_tbl(i).role_name is not null
2263 THEN
2264 -- dbms_output.put_line('Sandeep - before validate resource: before validate resource');
2265 validate_resource (
2266 P_Api_Version_Number => P_Api_Version_Number,
2267 p_Init_Msg_List => p_Init_Msg_List,
2268 p_Commit => p_Commit,
2269 p_validation_level => p_validation_level,
2270 X_Return_Status => X_Return_Status,
2271 X_Msg_Count => X_Msg_Count,
2272 X_Msg_Data => X_Msg_Data,
2273 P_RESOURCE_NAME =>l_excel_rscs_tbl(i).resource_name ,
2274 P_GROUP_NAME =>l_excel_rscs_tbl(i).group_name ,
2275 P_ROLE_NAME =>l_excel_rscs_tbl(i).role_name ,
2276 P_terr_group_id =>P_TERR_GP_ID ,
2277 X_RESOURCE_id =>X_RESOURCE_id ,
2278 x_group_id =>x_group_id ,
2279 x_role_code =>x_role_code ,
2280 x_error_code =>x_error_code,
2281 x_status =>x_status );
2282 -- dbms_output.put_line('Sandeep - after validate resource: before validate resource ' || i || ' status ' ||x_status);
2283 if x_status = 'S' then
2284 -- -- dbms_output.put_line('Sandeep - Before inserting to x_added_rscs_tbl');
2285 x_added_rscs_tbl.extend;
2286 -- -- dbms_output.put_line('Sandeep - After extending to x_added_rscs_tbl');
2287 j:=j+1;
2288 x_added_rscs_tbl(j).resource_id := X_RESOURCE_id;
2289 x_added_rscs_tbl(j).group_id := x_group_id;
2290 x_added_rscs_tbl(j).role_code := x_role_code;
2291 x_added_rscs_tbl(j).resource_att1 := l_excel_rscs_tbl(i).resource_att1;
2292 x_added_rscs_tbl(j).resource_att2 := l_excel_rscs_tbl(i).resource_att2;
2293 x_added_rscs_tbl(j).resource_att3 := l_excel_rscs_tbl(i).resource_att3;
2294 x_added_rscs_tbl(j).resource_att4 := l_excel_rscs_tbl(i).resource_att4;
2295 x_added_rscs_tbl(j).resource_att5 := l_excel_rscs_tbl(i).resource_att5;
2296 x_added_rscs_tbl(j).attribute1 := 'N';
2297 x_added_rscs_tbl(j).attribute2 := i;
2298
2299 -- added 06/05/2006 bug 5246668, to validate and set date
2300 IF l_excel_rscs_tbl(i).resource_start_date is null THEN
2301 x_added_rscs_tbl(j).resource_start_date := P_START_DATE;
2302 ELSE
2303 IF (l_excel_rscs_tbl(i).resource_start_date < P_START_DATE) THEN
2304 x_return_status := fnd_api.g_ret_sts_error;
2305 fnd_message.clear;
2306 fnd_message.set_name ('JTF', 'JTY_RSC_STARTDATE_NOT_VALID');
2307 FND_MESSAGE.Set_Token ('RES_NAME', i ||', '||l_excel_rscs_tbl(i).resource_name||',');
2308 return;
2309 ELSE
2310 x_added_rscs_tbl(j).resource_start_date := l_excel_rscs_tbl(i).resource_start_date;
2311 END IF;
2312 END IF;
2313
2314 IF l_excel_rscs_tbl(i).resource_end_date is null THEN
2315 x_added_rscs_tbl(j).resource_end_date := P_END_DATE;
2316 ELSE
2317 IF (l_excel_rscs_tbl(i).resource_end_date BETWEEN p_start_date and P_end_date) THEN
2318 x_added_rscs_tbl(j).resource_end_date := l_excel_rscs_tbl(i).resource_end_date;
2319 ELSE
2320 x_return_status := fnd_api.g_ret_sts_error;
2321 fnd_message.clear;
2322 fnd_message.set_name ('JTF', 'JTY_RSC_ENDDATE_NOT_VALID');
2323 FND_MESSAGE.Set_Token ('RES_NAME', i ||', '||l_excel_rscs_tbl(i).resource_name||',');
2324 return;
2325 END IF;
2326 END IF;
2327
2328 -- end for bug 5246668, by mhtran
2329
2330 -- dbms_output.put_line('Sandeep - after inserting to x_added_rscs_tbl');
2331 elsif x_status = 'I' then NULL;
2332 else
2333 x_return_status := fnd_api.g_ret_sts_error;
2334 -- dbms_output.put_line('Sandeep - error in update sales team: after validate resource' );
2335 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', i); end if;
2336 return;
2337 end if;
2338 END IF;
2339 END LOOP;
2340 END IF;
2341 -- -- dbms_output.put_line('Sandeep - ');
2342 EXCEPTION
2343 WHEN OTHERS THEN
2344 x_return_status := fnd_api.g_ret_sts_unexp_error;
2345 -- dbms_output.put_line('Sandeep - ' || sqlerrm);
2346 l_error_msg := substr(sqlerrm,1,200);
2347 fnd_message.clear;
2348 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2349 fnd_message.set_token('ERRMSG', l_error_msg );
2350 put_jty_log('Error in VALIDATE_SALES_TEAM: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
2351 return;
2352 END VALIDATE_SALES_TEAM;
2353
2354 /* Procedure to update the sales team assignments for an account
2355 * It gets invoked by populate_admin_excel_data for update sales team
2356 * it validates the salespersons, checks if they can be added then
2357 * delete and addd salespersons, if needed and assign to owners of tg if
2358 * needed.
2359 */
2360 PROCEDURE UPDATE_SALES_TEAM (
2361 p_Api_Version_Number IN NUMBER,
2362 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2363 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2364 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2365 X_Return_Status OUT NOCOPY VARCHAR2,
2366 X_Msg_Count OUT NOCOPY NUMBER,
2367 X_Msg_Data OUT NOCOPY VARCHAR2,
2368 p_added_rscs_tbl IN SALESREP_RSC_TBL_TYPE,
2369 P_TERR_GP_ID IN NUMBER ,
2370 P_TERR_GP_ACCT_ID IN NUMBER ,
2371 P_NAMED_ACCT_ID IN NUMBER,
2372 P_SALES_GROUP IN NUMBER,
2373 P_SALES_ROLE IN VARCHAR2)
2374 AS
2375 i integer:=0;
2376 j integer :=0;
2377 k integer :=0;
2378 m integer :=0;
2379 n integer :=0;
2380 CURSOR c_res_list(l_terr_grp_acct_id IN number,
2381 l_sales_group IN number,
2382 l_sales_role IN varchar2)
2383 IS select RESOURCE_ID, RSC_GROUP_ID , RSC_ROLE_CODE
2384 from jtf_tty_named_acct_rsc
2385 where TERR_GROUP_ACCOUNT_ID = l_terr_grp_acct_id
2386 and (l_sales_group is null
2387 or RSC_GROUP_ID in
2388 (select group_id
2389 from jtf_rs_groups_denorm
2390 where parent_group_id = l_sales_group))
2391 and (l_sales_role is null
2392 or RSC_ROLE_CODE = l_sales_role);
2393
2394 errbuf varchar2(2000);
2395 retcode number;
2396 X_RESOURCE_id number;
2397 x_group_id number;
2398 x_role_code varchar2(30);
2399 x_error_code varchar2(2);
2400 x_status varchar2(3);
2401
2402 l_added_rscs_tbl SALESREP_RSC_TBL_TYPE;
2403 l_add_rscs_tbl SALESREP_RSC_TBL_TYPE;
2404 l_directs_tbl SALESREP_RSC_TBL_TYPE;
2405 l_removed_rscs_tbl SALESREP_RSC_TBL_TYPE;
2406 l_rscs_owners_tbl SALESREP_RSC_OWNERS_TBL_TYPE;
2407 l_owners_tbl OWNER_RSC_TBL_TYPE;
2408
2409 l_affected_parties_tbl AFFECTED_PARTY_TBL_TYPE;
2410 l_user_id NUMBER;
2411 l_assign_flag varchar2(1);
2412 l_whether_exist varchar2(1);
2413 l_atleast_one_rep boolean := FALSE;
2414 l_valid_person_flag boolean := FALSE;
2415 l_add_count NUMBER := 0;
2416 l_delete_count NUMBER := 0;
2417 i integer:=0;
2418 l_error_msg VARCHAR2(250);
2419
2420 l_res_found BOOLEAN := FALSE;
2421 cursor get_owners_c(l_tg_id NUMBER)
2422 IS
2423 select resource_id,
2424 rsc_group_id,
2425 rsc_role_code,
2426 'N' delete_flag
2427 from jtf_tty_terr_grp_owners
2428 where terr_group_id = l_tg_id;
2429
2430
2431 BEGIN
2432
2433 -- dbms_output.put_line('Sandeep - before update sales team');
2434 l_user_id := fnd_global.user_id;
2435 l_added_rscs_tbl := SALESREP_RSC_TBL_TYPE();
2436 l_added_rscs_tbl := p_added_rscs_tbl;
2437 l_affected_parties_tbl := AFFECTED_PARTY_TBL_TYPE();
2438 l_owners_tbl := OWNER_RSC_TBL_TYPE();
2439 -- l_owners_tbl.extend;
2440 l_affected_parties_tbl.extend;
2441 l_affected_parties_tbl(1).terr_group_account_id := P_TERR_GP_ACCT_ID;
2442 l_affected_parties_tbl(1).attribute1 := 'N';
2443 /* get all the owners for this TG */
2444 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
2445 for owners in get_owners_c(P_TERR_GP_ID) LOOP
2446 j := j+1;
2447 l_owners_tbl.extend();
2448 l_owners_tbl(j).resource_id := owners.resource_id;
2449 l_owners_tbl(j).group_id := owners.rsc_group_id;
2450 l_owners_tbl(j).role_code := owners.rsc_role_code;
2451 l_owners_tbl(j).delete_flag := 'N';
2452 end loop;
2453
2454 l_add_rscs_tbl := SALESREP_RSC_TBL_TYPE();
2455 l_removed_rscs_tbl := SALESREP_RSC_TBL_TYPE();
2456 /* get owners for all the salespersons coming from excel document */
2457 -- dbms_output.put_line('Sandeep - In update sales team: before gt resource owners' || l_added_rscs_tbl.count());
2458 if ( l_added_rscs_tbl.FIRST is not null) THEN
2459 GET_RESOURCE_OWNERS(
2460 P_Api_Version_Number => P_Api_Version_Number,
2461 p_Init_Msg_List => p_Init_Msg_List,
2462 p_Commit => p_Commit,
2463 p_validation_level => p_validation_level,
2464 X_Return_Status => X_Return_Status,
2465 X_Msg_Count => X_Msg_Count,
2466 X_Msg_Data => X_Msg_Data,
2467 p_terr_gp_id => P_TERR_GP_ID,
2468 P_RESOURCES_TBL => l_added_rscs_tbl,
2469 x_rscs_owners_tbl => l_rscs_owners_tbl
2470 , x_error_code => x_error_code
2471 , x_status => x_status );
2472 end if;
2473 -- dbms_output.put_line('Sandeep - In update sales team: after gt resource owners ' || X_Return_Status);
2474 IF ( l_added_rscs_tbl.FIRST is not null) THEN
2475 IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
2476 if l_added_rscs_tbl.FIRST is not null
2477 then
2478 -- dbms_output.put_line('Sandeep - In update sales team: data in added rscs table ');
2479 for j in l_added_rscs_tbl.FIRST..l_added_rscs_tbl.LAST
2480 loop
2481 if l_rscs_owners_tbl.FIRST is not null THEN
2482 -- dbms_output.put_line('Sandeep - In update sales team: data in added rscs table ');
2483 for k in l_rscs_owners_tbl.FIRST..l_rscs_owners_tbl.LAST
2484 loop
2485 if (l_added_rscs_tbl(j).group_id = l_rscs_owners_tbl(k).group_id) THEN
2486 l_valid_person_flag := TRUE;
2487 -- dbms_output.put_line('In update sales team, valid person flag: true');
2488 for m in l_owners_tbl.FIRST..l_owners_tbl.LAST loop
2489 if (l_owners_tbl(m).resource_id = l_rscs_owners_tbl(k).owner_resource_id
2490 and
2491 l_owners_tbl(m).group_id = l_rscs_owners_tbl(k).owner_group_id
2492 and
2493 l_owners_tbl(m).role_code = l_rscs_owners_tbl(k).owner_role_code) THEN
2494 l_owners_tbl(m).delete_flag := 'Y';
2495 end if;
2496 end loop;
2497 end if;
2498 end loop;
2499 else
2500 x_return_status := FND_API.G_RET_STS_ERROR;
2501 fnd_message.clear;
2502 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
2503 FND_MESSAGE.Set_Token ('POSITION', l_added_rscs_tbl(j).attribute2);
2504 RETURN;
2505 end if;
2506 if (l_valid_person_flag = TRUE) THEN
2507 begin
2508 l_valid_person_flag := FALSE;
2509 select ASSIGNED_FLAG
2510 into l_assign_flag
2511 from jtf_tty_named_acct_rsc
2512 where TERR_GROUP_ACCOUNT_ID = P_TERR_GP_ACCT_ID
2513 and RESOURCE_ID = l_added_rscs_tbl(j).Resource_id
2514 and RSC_GROUP_ID = l_added_rscs_tbl(j).group_id
2515 and RSC_ROLE_CODE = l_added_rscs_tbl(j).role_code
2516 and RSC_RESOURCE_TYPE = 'RS_EMPLOYEE';
2517 -- dbms_output.put_line('Sandeep - In populate sales team: existing salesperson ' || l_added_rscs_tbl(j).Resource_id );
2518 IF l_assign_flag = 'N' THEN
2519 l_add_rscs_tbl.extend;
2520 n := n + 1;
2521 l_add_rscs_tbl(n).resource_id := l_added_rscs_tbl(j).Resource_id;
2522 l_add_rscs_tbl(n).group_id := l_added_rscs_tbl(j).group_id;
2523 l_add_rscs_tbl(n).role_code := l_added_rscs_tbl(j).role_code;
2524 l_add_rscs_tbl(n).resource_att1 := l_added_rscs_tbl(j).resource_att1;
2525 l_add_rscs_tbl(n).resource_att2 := l_added_rscs_tbl(j).resource_att2;
2526 l_add_rscs_tbl(n).resource_att3 := l_added_rscs_tbl(j).resource_att3;
2527 l_add_rscs_tbl(n).resource_att4 := l_added_rscs_tbl(j).resource_att4;
2528 l_add_rscs_tbl(n).resource_att5 := l_added_rscs_tbl(j).resource_att5;
2529 l_add_rscs_tbl(n).resource_start_date := l_added_rscs_tbl(j).resource_start_date;
2530 l_add_rscs_tbl(n).resource_end_date := l_added_rscs_tbl(j).resource_end_date;
2531 l_add_rscs_tbl(n).attribute1 := 'N';
2532 -- dbms_output.put_line('In update resource, attribute1: ' ||l_add_rscs_tbl(n).resource_att1);
2533 /* for admin upload, the manager does not make sense as the administrator
2534 * is acting as territory group owners. This code should not be there for a RM Upload */
2535 l_add_rscs_tbl(n).mgr_resource_id := -999;
2536 l_add_rscs_tbl(n).mgr_group_id := -999;
2537 l_add_rscs_tbl(n).mgr_role_code := '-999';
2538 l_add_rscs_tbl(n).resource_type := 'RS_EMPLOYEE';
2539 ELSE --l_assign_flag = 'Y',ignore
2540 NULL;
2541 END IF;
2542
2543
2544 exception
2545 when no_data_found then
2546 l_add_rscs_tbl.extend;
2547 n := n + 1;
2548 l_add_rscs_tbl(n).resource_id := l_added_rscs_tbl(j).Resource_id;
2549 l_add_rscs_tbl(n).group_id := l_added_rscs_tbl(j).group_id;
2550 l_add_rscs_tbl(n).role_code := l_added_rscs_tbl(j).role_code;
2551 l_add_rscs_tbl(n).resource_att1 := l_added_rscs_tbl(j).resource_att1;
2552 l_add_rscs_tbl(n).resource_att2 := l_added_rscs_tbl(j).resource_att2;
2553 l_add_rscs_tbl(n).resource_att3 := l_added_rscs_tbl(j).resource_att3;
2554 l_add_rscs_tbl(n).resource_att4 := l_added_rscs_tbl(j).resource_att4;
2555 l_add_rscs_tbl(n).resource_att5 := l_added_rscs_tbl(j).resource_att5;
2556 l_add_rscs_tbl(n).resource_start_date := l_added_rscs_tbl(j).resource_start_date;
2557 l_add_rscs_tbl(n).resource_end_date := l_added_rscs_tbl(j).resource_end_date;
2558
2559 -- dbms_output.put_line('update resource, attribute 1: '||l_add_rscs_tbl(n).resource_att1);
2560 l_add_rscs_tbl(n).attribute1 := 'N';
2561 /* for admin upload, the manager does not make sense as the administrator
2562 * is acting as territory group owners. This code should not be there for a RM Upload */
2563 l_add_rscs_tbl(n).mgr_resource_id := -999;
2564 l_add_rscs_tbl(n).mgr_group_id := -999;
2565 l_add_rscs_tbl(n).mgr_role_code := '-999';
2566 l_add_rscs_tbl(n).resource_type := 'RS_EMPLOYEE';
2567 end;
2568 else
2569 x_return_status := FND_API.G_RET_STS_ERROR;
2570 fnd_message.clear;
2571 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
2572 FND_MESSAGE.Set_Token ('POSITION', l_added_rscs_tbl(j).attribute2);
2573 RETURN;
2574 end if;
2575 end loop;
2576 end if;
2577 ELSE
2578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2579 -- dbms_output.put_line('Sandeep - ERror 2 In populate sales team');
2580 l_error_msg := substr(sqlerrm,1,200);
2581 fnd_message.clear;
2582 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2583 fnd_message.set_token('ERRMSG', l_error_msg );
2584 put_jty_log('Error in UPDATE_SALES_TEAM: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
2585 return;
2586 END IF;
2587 END IF;
2588 IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
2589 for i in l_owners_tbl.FIRST..l_owners_tbl.LAST loop
2590 if (l_owners_tbl(i).delete_flag = 'N') THEN
2591 -- dbms_output.put_line ('In owner tbl');
2592 l_add_rscs_tbl.extend;
2593 n := n + 1;
2594 l_add_rscs_tbl(n).resource_id := l_owners_tbl(i).Resource_id;
2595 l_add_rscs_tbl(n).group_id := l_owners_tbl(i).group_id;
2596 l_add_rscs_tbl(n).role_code := l_owners_tbl(i).role_code;
2597 l_add_rscs_tbl(n).attribute1 := 'N';
2598 /* for admin upload, the manager does not make sense as the administrator
2599 * is acting as territory group owners. This code should not be there for a RM Upload */
2600 l_add_rscs_tbl(n).mgr_resource_id := -999;
2601 l_add_rscs_tbl(n).mgr_group_id := -999;
2602 l_add_rscs_tbl(n).mgr_role_code := '-999';
2603 l_add_rscs_tbl(n).resource_type := 'RS_EMPLOYEE';
2604 end if;
2605 end loop;
2606 END IF;
2607
2608 for c_res in c_res_list(P_TERR_GP_ACCT_ID, P_SALES_GROUP, P_SALES_ROLE)
2609 loop
2610 l_res_found := FALSE;
2611 if l_added_rscs_tbl.FIRST is not null
2612 then
2613 for j in l_added_rscs_tbl.FIRST..l_added_rscs_tbl.LAST
2614 loop
2615 if l_added_rscs_tbl(j).Resource_id = c_res.Resource_id
2616 and l_added_rscs_tbl(j).group_id = c_res.RSC_GROUP_ID
2617 and l_added_rscs_tbl(j).role_code = c_res.RSC_ROLE_CODE
2618 then
2619 l_res_found := TRUE;
2620 exit;
2621 END IF;
2622 end loop;
2623 end if;
2624
2625 if l_res_found = FALSE THEN
2626 Begin
2627 l_removed_rscs_tbl.extend;
2628 l_delete_count := l_delete_count +1;
2629 l_removed_rscs_tbl(l_delete_count).resource_id := c_res.Resource_id;
2630 l_removed_rscs_tbl(l_delete_count).group_id := c_res.RSC_GROUP_ID;
2631 l_removed_rscs_tbl(l_delete_count).role_code := c_res.RSC_ROLE_CODE;
2632 l_removed_rscs_tbl(l_delete_count).attribute1 := 'N';
2633
2634 l_removed_rscs_tbl(l_delete_count).mgr_resource_id := -999;
2635 l_removed_rscs_tbl(l_delete_count).mgr_group_id := -999;
2636 l_removed_rscs_tbl(l_delete_count).mgr_role_code := '-999';
2637 l_removed_rscs_tbl(l_delete_count).resource_type := 'RS_EMPLOYEE';
2638 Exception
2639 when others then
2640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2641 -- dbms_output.put_line('Sandeep - Error In populate sales team: removed sales persons processing');
2642 l_error_msg := substr(sqlerrm,1,200);
2643 fnd_message.clear;
2644 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2645 fnd_message.set_token('ERRMSG', l_error_msg );
2646 put_jty_log('Error in UPDATE_SALES_TEAM: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
2647 return;
2648 end;
2649 end if; -- if l_res_found = FALSE
2650 end loop; -- end of c_res loop
2651
2652 -- dbms_output.put_line('Sandeep - In populate sales team: Before doing update sales team ');
2653 /* for admin upload, the manager does not make sense as the administrator
2654 * is acting as territory group owners. This code should not be there for a RM Upload */
2655 -- now remove and add salespersons for this account
2656 POPULATE_SALES_TEAM(
2657 p_api_version_number => 1,
2658 p_init_msg_list => 'N',
2659 p_SQL_Trace => 'N',
2660 p_Debug_Flag => 'N',
2661 x_return_status => x_return_status,
2662 x_msg_count => x_msg_count,
2663 x_msg_data => x_msg_data,
2664 p_from_where => 'ADMIN',
2665 p_user_resource_id => null,
2666 p_terr_group_id => p_terr_gp_id,
2667 p_user_attribute1 => fnd_global.user_id,
2668 --p_user_attribute1 => 1069,
2669 p_user_attribute2 => null,
2670 p_added_rscs_tbl => l_add_rscs_tbl,
2671 p_removed_rscs_tbl => l_removed_rscs_tbl,
2672 p_affected_parties_tbl => l_affected_parties_tbl,
2673 ERRBUF => errbuf,
2674 RETCODE => retcode
2675 );
2676
2677
2678
2679 exception
2680 when no_data_found then
2681 fnd_message.clear;
2682 fnd_message.set_name ('JTF', 'JTF_TTY_NA_NOT_ASSIGED');
2683 -- dbms_output.put_line('Sandeep - ERror 1 In populate sales team');
2684 x_return_status := FND_API.G_RET_STS_ERROR;
2685 return;
2686
2687 when others then
2688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2689 -- dbms_output.put_line('Sandeep - ERror 2 In populate sales team');
2690 l_error_msg := substr(sqlerrm,1,200);
2691 fnd_message.clear;
2692 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2693 fnd_message.set_token('ERRMSG', l_error_msg );
2694 put_jty_log('Error in UPDATE_SALES_TEAM: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
2695 return;
2696
2697 END UPDATE_SALES_TEAM;
2698
2699 /* Procedure called during Add to Org with Update Sales Team and Transfer to TG with Update Sales Team
2700 * It makes the sales team assignments for the accounts
2701 * APIs called: Populate Sales Team
2702 */
2703 PROCEDURE ADD_SALES_TEAM (
2704 p_Api_Version_Number IN NUMBER,
2705 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2706 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2707 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2708 X_Return_Status OUT NOCOPY VARCHAR2,
2709 X_Msg_Count OUT NOCOPY NUMBER,
2710 X_Msg_Data OUT NOCOPY VARCHAR2,
2711 p_added_rscs_tbl IN SALESREP_RSC_TBL_TYPE,
2712 P_TERR_GP_ID IN NUMBER ,
2713 P_TERR_GP_ACCT_ID IN NUMBER ,
2714 P_NAMED_ACCT_ID IN NUMBER)
2715 AS
2716
2717 errbuf varchar2(2000);
2718 retcode number;
2719 X_OWNER_RESOURCE_id number;
2720 x_owner_group_id number;
2721 x_owner_role_code varchar2(60);
2722 x_error_code varchar2(2);
2723 x_status varchar2(3);
2724
2725 l_added_rscs_tbl SALESREP_RSC_TBL_TYPE;
2726 l_rscs_owners_tbl SALESREP_RSC_OWNERS_TBL_TYPE;
2727 l_add_rscs_tbl SALESREP_RSC_TBL_TYPE;
2728 l_directs_tbl SALESREP_RSC_TBL_TYPE;
2729 l_removed_rscs_tbl SALESREP_RSC_TBL_TYPE;
2730 l_owners_tbl OWNER_RSC_TBL_TYPE;
2731 l_affected_parties_tbl AFFECTED_PARTY_TBL_TYPE;
2732
2733 l_user_id NUMBER;
2734 l_assign_flag varchar2(1);
2735 l_whether_exist varchar2(1);
2736 l_atleast_one_rep boolean := FALSE;
2737 l_add_count NUMBER := 0;
2738 l_delete_count NUMBER := 0;
2739 l_valid_person_flag boolean := FALSE;
2740 i integer:=0;
2741 j integer :=0;
2742 k integer :=0;
2743 m integer :=0;
2744 n integer :=0;
2745 l_error_msg VARCHAR2(250);
2746
2747
2748
2749 l_res_found BOOLEAN := FALSE;
2750 cursor get_owners_c(l_tg_id NUMBER)
2751 IS
2752 select resource_id,
2753 rsc_group_id,
2754 rsc_role_code,
2755 'N' delete_flag
2756 from jtf_tty_terr_grp_owners
2757 where terr_group_id = l_tg_id;
2758
2759
2760 BEGIN
2761
2762 -- dbms_output.put_line('Sandeep - Start of Add sales team');
2763 l_user_id := fnd_global.user_id;
2764 l_added_rscs_tbl := SALESREP_RSC_TBL_TYPE();
2765 l_added_rscs_tbl := p_added_rscs_tbl;
2766 l_affected_parties_tbl := AFFECTED_PARTY_TBL_TYPE();
2767 l_owners_tbl := OWNER_RSC_TBL_TYPE();
2768 -- l_owners_tbl.extend;
2769 l_affected_parties_tbl.extend;
2770 l_affected_parties_tbl(1).terr_group_account_id := P_TERR_GP_ACCT_ID;
2771 l_affected_parties_tbl(1).attribute1 := 'N';
2772 l_rscs_owners_tbl := SALESREP_RSC_OWNERS_TBL_TYPE();
2773 /* get all the owners for this TG into a PL/SQL table*/
2774 -- dbms_output.put_line('Sandeep - Start of Add sales team 1');
2775 for owners in get_owners_c(P_TERR_GP_ID) LOOP
2776 j := j+1;
2777 l_owners_tbl.extend();
2778 l_owners_tbl(j).resource_id := owners.resource_id;
2779 l_owners_tbl(j).group_id := owners.rsc_group_id;
2780 l_owners_tbl(j).role_code := owners.rsc_role_code;
2781 l_owners_tbl(j).delete_flag := 'N';
2782 end loop;
2783 -- dbms_output.put_line('Sandeep - Start of Add sales team 2');
2784 l_add_rscs_tbl := SALESREP_RSC_TBL_TYPE();
2785 l_removed_rscs_tbl := SALESREP_RSC_TBL_TYPE();
2786 /* get owners for all the salespersons coming from excel document */
2787 GET_RESOURCE_OWNERS(
2788 P_Api_Version_Number => P_Api_Version_Number,
2789 p_Init_Msg_List => p_Init_Msg_List,
2790 p_Commit => p_Commit,
2791 p_validation_level => p_validation_level,
2792 X_Return_Status => X_Return_Status,
2793 X_Msg_Count => X_Msg_Count,
2794 X_Msg_Data => X_Msg_Data,
2795 p_terr_gp_id => P_TERR_GP_ID,
2796 P_RESOURCES_TBL => l_added_rscs_tbl,
2797 x_rscs_owners_tbl => l_rscs_owners_tbl
2798 , x_error_code => x_error_code
2799 , x_status => x_status );
2800 -- dbms_output.put_line('Sandeep - Start of Add sales team status ' || X_Return_Status);
2801 IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
2802 if l_added_rscs_tbl.FIRST is not null
2803 then
2804 -- dbms_output.put_line('Sandeep - looping added resources table ');
2805 for j in l_added_rscs_tbl.FIRST..l_added_rscs_tbl.LAST
2806 loop
2807 -- dbms_output.put_line('Sandeep - before looping added resources owners table ');
2808 if l_rscs_owners_tbl.FIRST is not null THEN
2809 -- dbms_output.put_line('Sandeep - looping added resources owners table ');
2810 for k in l_rscs_owners_tbl.FIRST..l_rscs_owners_tbl.LAST
2811
2812 loop
2813 -- dbms_output.put_line('Sandeep - looping added resources owners table ');
2814 if (l_added_rscs_tbl(j).group_id = l_rscs_owners_tbl(k).group_id) THEN
2815 l_valid_person_flag := TRUE;
2816 for m in l_owners_tbl.FIRST..l_owners_tbl.LAST loop
2817 if (l_owners_tbl(m).resource_id = l_rscs_owners_tbl(k).owner_resource_id
2818 and
2819 l_owners_tbl(m).group_id = l_rscs_owners_tbl(k).owner_group_id
2820 and
2821 l_owners_tbl(m).role_code = l_rscs_owners_tbl(k).owner_role_code) THEN
2822 l_owners_tbl(m).delete_flag := 'Y';
2823 end if;
2824 end loop;
2825 end if;
2826 end loop; /* end of looping resource's owners table */
2827 else
2828 x_return_status := FND_API.G_RET_STS_ERROR;
2829 -- dbms_output.put_line('Sandeep - No Owners found');
2830 fnd_message.clear;
2831 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
2832 FND_MESSAGE.Set_Token ('POSITION', l_added_rscs_tbl(j).attribute2);
2833 RETURN;
2834 end if;
2835 -- dbms_output.put_line('Sandeep - 111');
2836 if (l_valid_person_flag = TRUE) /* add the salesperson to l_add_rsc_tbl */ THEN
2837 l_valid_person_flag := FALSE;
2838 l_add_rscs_tbl.extend;
2839 n := n + 1;
2840 l_add_rscs_tbl(n).resource_id := l_added_rscs_tbl(j).Resource_id;
2841 l_add_rscs_tbl(n).group_id := l_added_rscs_tbl(j).group_id;
2842 l_add_rscs_tbl(n).role_code := l_added_rscs_tbl(j).role_code;
2843 l_add_rscs_tbl(n).resource_att1 := l_added_rscs_tbl(j).resource_att1;
2844 l_add_rscs_tbl(n).resource_att2 := l_added_rscs_tbl(j).resource_att2;
2845 l_add_rscs_tbl(n).resource_att3 := l_added_rscs_tbl(j).resource_att3;
2846 l_add_rscs_tbl(n).resource_att4 := l_added_rscs_tbl(j).resource_att4;
2847 l_add_rscs_tbl(n).resource_att5 := l_added_rscs_tbl(j).resource_att5;
2848 l_add_rscs_tbl(n).resource_start_date := l_added_rscs_tbl(j).resource_start_date;
2849 l_add_rscs_tbl(n).resource_end_date := l_added_rscs_tbl(j).resource_end_date;
2850
2851 -- dbms_output.put_line('add resource, attribute 1: '||l_add_rscs_tbl(n).resource_att1);
2852 l_add_rscs_tbl(n).attribute1 := 'N';
2853 /* for admin upload, the manager does not make sense as the administrator
2854 * is acting as territory group owners. This code should not be there for a RM Upload */
2855 l_add_rscs_tbl(n).mgr_resource_id := -999;
2856 l_add_rscs_tbl(n).mgr_group_id := -999;
2857 l_add_rscs_tbl(n).mgr_role_code := '-999';
2858 l_add_rscs_tbl(n).resource_type := 'RS_EMPLOYEE';
2859 else
2860 x_return_status := FND_API.G_RET_STS_ERROR;
2861 fnd_message.clear;
2862 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
2863 FND_MESSAGE.Set_Token ('POSITION', l_added_rscs_tbl(j).attribute2);
2864 RETURN;
2865 end if;
2866 end loop; /* end of looping l_added_rscs_tbl */
2867 end if; /* end of if l_added_rscs_tbl.first is not null */
2868 ELSE /* if GET_RESOURCE_OWNERS returned error */
2869 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2870 -- dbms_output.put_line('Sandeep - ERror 2 In populate sales team');
2871 l_error_msg := substr(sqlerrm,1,200);
2872 fnd_message.clear;
2873 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2874 fnd_message.set_token('ERRMSG', l_error_msg );
2875 return;
2876 END IF;
2877 -- dbms_output.put_line('Sandeep - 222');
2878 IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
2879 for i in l_owners_tbl.FIRST..l_owners_tbl.LAST loop
2880 if (l_owners_tbl(i).delete_flag = 'N') THEN
2881 l_add_rscs_tbl.extend;
2882 n := n + 1;
2883 l_add_rscs_tbl(n).resource_id := l_owners_tbl(i).Resource_id;
2884 l_add_rscs_tbl(n).group_id := l_owners_tbl(i).group_id;
2885 l_add_rscs_tbl(n).role_code := l_owners_tbl(i).role_code;
2886 l_add_rscs_tbl(n).attribute1 := 'N';
2887 /* for admin upload, the manager does not make sense as the administrator
2888 * is acting as territory group owners. This code should not be there for a RM Upload */
2889 l_add_rscs_tbl(n).mgr_resource_id := -999;
2890 l_add_rscs_tbl(n).mgr_group_id := -999;
2891 l_add_rscs_tbl(n).mgr_role_code := '-999';
2892 l_add_rscs_tbl(n).resource_type := 'RS_EMPLOYEE';
2893 end if;
2894 end loop;
2895 END IF;
2896
2897
2898 -- dbms_output.put_line('Sandeep - In populate sales team: Before doing update sales team ');
2899 /* for admin upload, the manager does not make sense as the administrator
2900 * is acting as territory group owners. This code should not be there for a RM Upload */
2901 -- now remove and add salespersons for this account
2902 POPULATE_SALES_TEAM(
2903 p_api_version_number => 1,
2904 p_init_msg_list => 'N',
2905 p_SQL_Trace => 'N',
2906 p_Debug_Flag => 'N',
2907 x_return_status => x_return_status,
2908 x_msg_count => x_msg_count,
2909 x_msg_data => x_msg_data,
2910 p_from_where => 'ADMIN',
2911 p_user_resource_id => null,
2912 p_terr_group_id => p_terr_gp_id,
2913 p_user_attribute1 => fnd_global.user_id,
2914 --p_user_attribute1 => 1069,
2915 p_user_attribute2 => null,
2916 p_added_rscs_tbl => l_add_rscs_tbl,
2917 p_removed_rscs_tbl => l_removed_rscs_tbl,
2918 p_affected_parties_tbl => l_affected_parties_tbl,
2919 ERRBUF => errbuf,
2920 RETCODE => retcode
2921 );
2922
2923
2924
2925 exception
2926 when no_data_found then
2927 fnd_message.clear;
2928 fnd_message.set_name ('JTF', 'JTF_TTY_NA_NOT_ASSIGED');
2929 -- dbms_output.put_line('Sandeep - ERror 1 In populate sales team');
2930 x_return_status := FND_API.G_RET_STS_ERROR;
2931 return;
2932
2933 when others then
2934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2935 -- dbms_output.put_line('Sandeep ' || SQLERRM);
2936 -- dbms_output.put_line('Sandeep - ERror 2 In populate sales team');
2937 l_error_msg := substr(sqlerrm,1,200);
2938 fnd_message.clear;
2939 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2940 fnd_message.set_token('ERRMSG', l_error_msg );
2941 put_jty_log('Error in ADD_SALES_TEAM: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
2942 return;
2943
2944 END ADD_SALES_TEAM;
2945
2946
2947 --Procedure that is invoked for each updated row in Excel (WEB ADI)
2948 --This procedure uploads the data from Excel to the database by calling various APIs.
2949
2950 PROCEDURE POPULATE_ADMIN_EXCEL_DATA(
2951 P_PARTY_NUMBER IN VARCHAR2,
2952 P_NAMED_ACCOUNT IN VARCHAR2,
2953 P_SITE_TYPE IN VARCHAR2,
2954 P_TRADE_NAME IN VARCHAR2,
2955 P_DUNS IN VARCHAR2,
2956 P_GU_DUNS IN VARCHAR2,
2957 P_GU_NAME IN VARCHAR2,
2958 P_DU_DUNS IN VARCHAR2,
2959 P_DU_NAME IN VARCHAR2,
2960 P_CITY IN VARCHAR2,
2961 P_STATE IN VARCHAR2,
2962 P_POSTAL_CODE IN VARCHAR2,
2963 P_TERRITORY_GROUP IN VARCHAR2,
2964 P_TO_TERRITORY_GROUP IN VARCHAR2,
2965 P_DELETE_FLAG IN VARCHAR2,
2966 P_RESOURCE1_NAME IN VARCHAR2,
2967 P_GROUP1_NAME IN VARCHAR2,
2968 P_ROLE1_NAME IN VARCHAR2,
2969 P_RESOURCE2_NAME IN VARCHAR2,
2970 P_GROUP2_NAME IN VARCHAR2,
2971 P_ROLE2_NAME IN VARCHAR2,
2972 P_RESOURCE3_NAME IN VARCHAR2,
2973 P_GROUP3_NAME IN VARCHAR2,
2974 P_ROLE3_NAME IN VARCHAR2,
2975 P_RESOURCE4_NAME IN VARCHAR2,
2976 P_GROUP4_NAME IN VARCHAR2,
2977 P_ROLE4_NAME IN VARCHAR2,
2978 P_RESOURCE5_NAME IN VARCHAR2,
2979 P_GROUP5_NAME IN VARCHAR2,
2980 P_ROLE5_NAME IN VARCHAR2,
2981 P_RESOURCE6_NAME IN VARCHAR2,
2982 P_GROUP6_NAME IN VARCHAR2,
2983 P_ROLE6_NAME IN VARCHAR2,
2984 P_RESOURCE7_NAME IN VARCHAR2,
2985 P_GROUP7_NAME IN VARCHAR2,
2986 P_ROLE7_NAME IN VARCHAR2,
2987 P_RESOURCE8_NAME IN VARCHAR2,
2988 P_GROUP8_NAME IN VARCHAR2,
2989 P_ROLE8_NAME IN VARCHAR2,
2990 P_RESOURCE9_NAME IN VARCHAR2,
2991 P_GROUP9_NAME IN VARCHAR2,
2992 P_ROLE9_NAME IN VARCHAR2,
2993 P_RESOURCE10_NAME IN VARCHAR2,
2994 P_GROUP10_NAME IN VARCHAR2,
2995 P_ROLE10_NAME IN VARCHAR2,
2996 P_RESOURCE11_NAME IN VARCHAR2,
2997 P_GROUP11_NAME IN VARCHAR2,
2998 P_ROLE11_NAME IN VARCHAR2,
2999 P_RESOURCE12_NAME IN VARCHAR2,
3000 P_GROUP12_NAME IN VARCHAR2,
3001 P_ROLE12_NAME IN VARCHAR2,
3002 P_RESOURCE13_NAME IN VARCHAR2,
3003 P_GROUP13_NAME IN VARCHAR2,
3004 P_ROLE13_NAME IN VARCHAR2,
3005 P_RESOURCE14_NAME IN VARCHAR2,
3006 P_GROUP14_NAME IN VARCHAR2,
3007 P_ROLE14_NAME IN VARCHAR2,
3008 P_RESOURCE15_NAME IN VARCHAR2,
3009 P_GROUP15_NAME IN VARCHAR2,
3010 P_ROLE15_NAME IN VARCHAR2,
3011 P_RESOURCE16_NAME IN VARCHAR2,
3012 P_GROUP16_NAME IN VARCHAR2,
3013 P_ROLE16_NAME IN VARCHAR2,
3014 P_RESOURCE17_NAME IN VARCHAR2,
3015 P_GROUP17_NAME IN VARCHAR2,
3016 P_ROLE17_NAME IN VARCHAR2,
3017 P_RESOURCE18_NAME IN VARCHAR2,
3018 P_GROUP18_NAME IN VARCHAR2,
3019 P_ROLE18_NAME IN VARCHAR2,
3020 P_RESOURCE19_NAME IN VARCHAR2,
3021 P_GROUP19_NAME IN VARCHAR2,
3022 P_ROLE19_NAME IN VARCHAR2,
3023 P_RESOURCE20_NAME IN VARCHAR2,
3024 P_GROUP20_NAME IN VARCHAR2,
3025 P_ROLE20_NAME IN VARCHAR2,
3026 P_RESOURCE21_NAME IN VARCHAR2,
3027 P_GROUP21_NAME IN VARCHAR2,
3028 P_ROLE21_NAME IN VARCHAR2,
3029 P_RESOURCE22_NAME IN VARCHAR2,
3030 P_GROUP22_NAME IN VARCHAR2,
3031 P_ROLE22_NAME IN VARCHAR2,
3032 P_RESOURCE23_NAME IN VARCHAR2,
3033 P_GROUP23_NAME IN VARCHAR2,
3034 P_ROLE23_NAME IN VARCHAR2,
3035 P_RESOURCE24_NAME IN VARCHAR2,
3036 P_GROUP24_NAME IN VARCHAR2,
3037 P_ROLE24_NAME IN VARCHAR2,
3038 P_RESOURCE25_NAME IN VARCHAR2,
3039 P_GROUP25_NAME IN VARCHAR2,
3040 P_ROLE25_NAME IN VARCHAR2,
3041 P_RESOURCE26_NAME IN VARCHAR2,
3042 P_GROUP26_NAME IN VARCHAR2,
3043 P_ROLE26_NAME IN VARCHAR2,
3044 P_RESOURCE27_NAME IN VARCHAR2,
3045 P_GROUP27_NAME IN VARCHAR2,
3046 P_ROLE27_NAME IN VARCHAR2,
3047 P_RESOURCE28_NAME IN VARCHAR2,
3048 P_GROUP28_NAME IN VARCHAR2,
3049 P_ROLE28_NAME IN VARCHAR2,
3050 P_RESOURCE29_NAME IN VARCHAR2,
3051 P_GROUP29_NAME IN VARCHAR2,
3052 P_ROLE29_NAME IN VARCHAR2,
3053 P_RESOURCE30_NAME IN VARCHAR2,
3054 P_GROUP30_NAME IN VARCHAR2,
3055 P_ROLE30_NAME IN VARCHAR2,
3056 P_PARTY_SITE_ID IN VARCHAR2,
3057 P_SALES_GROUP IN VARCHAR2,
3058 P_SALES_ROLE IN VARCHAR2,
3059 P_PHONETIC_NAME IN VARCHAR2,
3060 P_IDENTIFYING_ADDRESS IN VARCHAR2,
3061 P_RES1_ATT1 IN VARCHAR2,
3062 P_RES2_ATT1 IN VARCHAR2,
3063 P_RES3_ATT1 IN VARCHAR2,
3064 P_RES4_ATT1 IN VARCHAR2,
3065 P_RES5_ATT1 IN VARCHAR2,
3066 P_RES6_ATT1 IN VARCHAR2,
3067 P_RES7_ATT1 IN VARCHAR2,
3068 P_RES8_ATT1 IN VARCHAR2,
3069 P_RES9_ATT1 IN VARCHAR2,
3070 P_RES10_ATT1 IN VARCHAR2,
3071 P_RES11_ATT1 IN VARCHAR2,
3072 P_RES12_ATT1 IN VARCHAR2,
3073 P_RES13_ATT1 IN VARCHAR2,
3074 P_RES14_ATT1 IN VARCHAR2,
3075 P_RES15_ATT1 IN VARCHAR2,
3076 P_RES16_ATT1 IN VARCHAR2,
3077 P_RES17_ATT1 IN VARCHAR2,
3078 P_RES18_ATT1 IN VARCHAR2,
3079 P_RES19_ATT1 IN VARCHAR2,
3080 P_RES20_ATT1 IN VARCHAR2,
3081 P_RES21_ATT1 IN VARCHAR2,
3082 P_RES22_ATT1 IN VARCHAR2,
3083 P_RES23_ATT1 IN VARCHAR2,
3084 P_RES24_ATT1 IN VARCHAR2,
3085 P_RES25_ATT1 IN VARCHAR2,
3086 P_RES26_ATT1 IN VARCHAR2,
3087 P_RES27_ATT1 IN VARCHAR2,
3088 P_RES28_ATT1 IN VARCHAR2,
3089 P_RES29_ATT1 IN VARCHAR2,
3090 P_RES30_ATT1 IN VARCHAR2,
3091 P_RES1_ATT2 IN VARCHAR2,
3092 P_RES2_ATT2 IN VARCHAR2,
3093 P_RES3_ATT2 IN VARCHAR2,
3094 P_RES4_ATT2 IN VARCHAR2,
3095 P_RES5_ATT2 IN VARCHAR2,
3096 P_RES6_ATT2 IN VARCHAR2,
3097 P_RES7_ATT2 IN VARCHAR2,
3098 P_RES8_ATT2 IN VARCHAR2,
3099 P_RES9_ATT2 IN VARCHAR2,
3100 P_RES10_ATT2 IN VARCHAR2,
3101 P_RES11_ATT2 IN VARCHAR2,
3102 P_RES12_ATT2 IN VARCHAR2,
3103 P_RES13_ATT2 IN VARCHAR2,
3104 P_RES14_ATT2 IN VARCHAR2,
3105 P_RES15_ATT2 IN VARCHAR2,
3106 P_RES16_ATT2 IN VARCHAR2,
3107 P_RES17_ATT2 IN VARCHAR2,
3108 P_RES18_ATT2 IN VARCHAR2,
3109 P_RES19_ATT2 IN VARCHAR2,
3110 P_RES20_ATT2 IN VARCHAR2,
3111 P_RES21_ATT2 IN VARCHAR2,
3112 P_RES22_ATT2 IN VARCHAR2,
3113 P_RES23_ATT2 IN VARCHAR2,
3114 P_RES24_ATT2 IN VARCHAR2,
3115 P_RES25_ATT2 IN VARCHAR2,
3116 P_RES26_ATT2 IN VARCHAR2,
3117 P_RES27_ATT2 IN VARCHAR2,
3118 P_RES28_ATT2 IN VARCHAR2,
3119 P_RES29_ATT2 IN VARCHAR2,
3120 P_RES30_ATT2 IN VARCHAR2,
3121 P_RES1_ATT3 IN VARCHAR2,
3122 P_RES2_ATT3 IN VARCHAR2,
3123 P_RES3_ATT3 IN VARCHAR2,
3124 P_RES4_ATT3 IN VARCHAR2,
3125 P_RES5_ATT3 IN VARCHAR2,
3126 P_RES6_ATT3 IN VARCHAR2,
3127 P_RES7_ATT3 IN VARCHAR2,
3128 P_RES8_ATT3 IN VARCHAR2,
3129 P_RES9_ATT3 IN VARCHAR2,
3130 P_RES10_ATT3 IN VARCHAR2,
3131 P_RES11_ATT3 IN VARCHAR2,
3132 P_RES12_ATT3 IN VARCHAR2,
3133 P_RES13_ATT3 IN VARCHAR2,
3134 P_RES14_ATT3 IN VARCHAR2,
3135 P_RES15_ATT3 IN VARCHAR2,
3136 P_RES16_ATT3 IN VARCHAR2,
3137 P_RES17_ATT3 IN VARCHAR2,
3138 P_RES18_ATT3 IN VARCHAR2,
3139 P_RES19_ATT3 IN VARCHAR2,
3140 P_RES20_ATT3 IN VARCHAR2,
3141 P_RES21_ATT3 IN VARCHAR2,
3142 P_RES22_ATT3 IN VARCHAR2,
3143 P_RES23_ATT3 IN VARCHAR2,
3144 P_RES24_ATT3 IN VARCHAR2,
3145 P_RES25_ATT3 IN VARCHAR2,
3146 P_RES26_ATT3 IN VARCHAR2,
3147 P_RES27_ATT3 IN VARCHAR2,
3148 P_RES28_ATT3 IN VARCHAR2,
3149 P_RES29_ATT3 IN VARCHAR2,
3150 P_RES30_ATT3 IN VARCHAR2,
3151 P_RES1_ATT4 IN VARCHAR2,
3152 P_RES2_ATT4 IN VARCHAR2,
3153 P_RES3_ATT4 IN VARCHAR2,
3154 P_RES4_ATT4 IN VARCHAR2,
3155 P_RES5_ATT4 IN VARCHAR2,
3156 P_RES6_ATT4 IN VARCHAR2,
3157 P_RES7_ATT4 IN VARCHAR2,
3158 P_RES8_ATT4 IN VARCHAR2,
3159 P_RES9_ATT4 IN VARCHAR2,
3160 P_RES10_ATT4 IN VARCHAR2,
3161 P_RES11_ATT4 IN VARCHAR2,
3162 P_RES12_ATT4 IN VARCHAR2,
3163 P_RES13_ATT4 IN VARCHAR2,
3164 P_RES14_ATT4 IN VARCHAR2,
3165 P_RES15_ATT4 IN VARCHAR2,
3166 P_RES16_ATT4 IN VARCHAR2,
3167 P_RES17_ATT4 IN VARCHAR2,
3168 P_RES18_ATT4 IN VARCHAR2,
3169 P_RES19_ATT4 IN VARCHAR2,
3170 P_RES20_ATT4 IN VARCHAR2,
3171 P_RES21_ATT4 IN VARCHAR2,
3172 P_RES22_ATT4 IN VARCHAR2,
3173 P_RES23_ATT4 IN VARCHAR2,
3174 P_RES24_ATT4 IN VARCHAR2,
3175 P_RES25_ATT4 IN VARCHAR2,
3176 P_RES26_ATT4 IN VARCHAR2,
3177 P_RES27_ATT4 IN VARCHAR2,
3178 P_RES28_ATT4 IN VARCHAR2,
3179 P_RES29_ATT4 IN VARCHAR2,
3180 P_RES30_ATT4 IN VARCHAR2,
3181 P_RES1_ATT5 IN VARCHAR2,
3182 P_RES2_ATT5 IN VARCHAR2,
3183 P_RES3_ATT5 IN VARCHAR2,
3184 P_RES4_ATT5 IN VARCHAR2,
3185 P_RES5_ATT5 IN VARCHAR2,
3186 P_RES6_ATT5 IN VARCHAR2,
3187 P_RES7_ATT5 IN VARCHAR2,
3188 P_RES8_ATT5 IN VARCHAR2,
3189 P_RES9_ATT5 IN VARCHAR2,
3190 P_RES10_ATT5 IN VARCHAR2,
3191 P_RES11_ATT5 IN VARCHAR2,
3192 P_RES12_ATT5 IN VARCHAR2,
3193 P_RES13_ATT5 IN VARCHAR2,
3194 P_RES14_ATT5 IN VARCHAR2,
3195 P_RES15_ATT5 IN VARCHAR2,
3196 P_RES16_ATT5 IN VARCHAR2,
3197 P_RES17_ATT5 IN VARCHAR2,
3198 P_RES18_ATT5 IN VARCHAR2,
3199 P_RES19_ATT5 IN VARCHAR2,
3200 P_RES20_ATT5 IN VARCHAR2,
3201 P_RES21_ATT5 IN VARCHAR2,
3202 P_RES22_ATT5 IN VARCHAR2,
3203 P_RES23_ATT5 IN VARCHAR2,
3204 P_RES24_ATT5 IN VARCHAR2,
3205 P_RES25_ATT5 IN VARCHAR2,
3206 P_RES26_ATT5 IN VARCHAR2,
3207 P_RES27_ATT5 IN VARCHAR2,
3208 P_RES28_ATT5 IN VARCHAR2,
3209 P_RES29_ATT5 IN VARCHAR2,
3210 P_RES30_ATT5 IN VARCHAR2,
3211 P_RES1_START_DATE IN DATE,
3212 P_RES2_START_DATE IN DATE,
3213 P_RES3_START_DATE IN DATE,
3214 P_RES4_START_DATE IN DATE,
3215 P_RES5_START_DATE IN DATE,
3216 P_RES6_START_DATE IN DATE,
3217 P_RES7_START_DATE IN DATE,
3218 P_RES8_START_DATE IN DATE,
3219 P_RES9_START_DATE IN DATE,
3220 P_RES10_START_DATE IN DATE,
3221 P_RES11_START_DATE IN DATE,
3222 P_RES12_START_DATE IN DATE,
3223 P_RES13_START_DATE IN DATE,
3224 P_RES14_START_DATE IN DATE,
3225 P_RES15_START_DATE IN DATE,
3226 P_RES16_START_DATE IN DATE,
3227 P_RES17_START_DATE IN DATE,
3228 P_RES18_START_DATE IN DATE,
3229 P_RES19_START_DATE IN DATE,
3230 P_RES20_START_DATE IN DATE,
3231 P_RES21_START_DATE IN DATE,
3232 P_RES22_START_DATE IN DATE,
3233 P_RES23_START_DATE IN DATE,
3234 P_RES24_START_DATE IN DATE,
3235 P_RES25_START_DATE IN DATE,
3236 P_RES26_START_DATE IN DATE,
3237 P_RES27_START_DATE IN DATE,
3238 P_RES28_START_DATE IN DATE,
3239 P_RES29_START_DATE IN DATE,
3240 P_RES30_START_DATE IN DATE,
3241 P_RES1_END_DATE IN DATE,
3242 P_RES2_END_DATE IN DATE,
3243 P_RES3_END_DATE IN DATE,
3244 P_RES4_END_DATE IN DATE,
3245 P_RES5_END_DATE IN DATE,
3246 P_RES6_END_DATE IN DATE,
3247 P_RES7_END_DATE IN DATE,
3248 P_RES8_END_DATE IN DATE,
3249 P_RES9_END_DATE IN DATE,
3250 P_RES10_END_DATE IN DATE,
3251 P_RES11_END_DATE IN DATE,
3252 P_RES12_END_DATE IN DATE,
3253 P_RES13_END_DATE IN DATE,
3254 P_RES14_END_DATE IN DATE,
3255 P_RES15_END_DATE IN DATE,
3256 P_RES16_END_DATE IN DATE,
3257 P_RES17_END_DATE IN DATE,
3258 P_RES18_END_DATE IN DATE,
3259 P_RES19_END_DATE IN DATE,
3260 P_RES20_END_DATE IN DATE,
3261 P_RES21_END_DATE IN DATE,
3262 P_RES22_END_DATE IN DATE,
3263 P_RES23_END_DATE IN DATE,
3264 P_RES24_END_DATE IN DATE,
3265 P_RES25_END_DATE IN DATE,
3266 P_RES26_END_DATE IN DATE,
3267 P_RES27_END_DATE IN DATE,
3268 P_RES28_END_DATE IN DATE,
3269 P_RES29_END_DATE IN DATE,
3270 P_RES30_END_DATE IN DATE,
3271 P_ATTRIBUTE1 IN VARCHAR2,
3272 P_ATTRIBUTE2 IN VARCHAR2,
3273 P_ATTRIBUTE3 IN VARCHAR2,
3274 P_ATTRIBUTE4 IN VARCHAR2,
3275 P_ATTRIBUTE5 IN VARCHAR2,
3276 P_ATTRIBUTE6 IN VARCHAR2,
3277 P_ATTRIBUTE7 IN VARCHAR2,
3278 P_ATTRIBUTE8 IN VARCHAR2,
3279 P_ATTRIBUTE9 IN VARCHAR2,
3280 P_ATTRIBUTE10 IN VARCHAR2,
3281 P_ATTRIBUTE11 IN VARCHAR2,
3282 P_ATTRIBUTE12 IN VARCHAR2,
3283 P_ATTRIBUTE13 IN VARCHAR2,
3284 P_ATTRIBUTE14 IN VARCHAR2,
3285 P_ATTRIBUTE15 IN VARCHAR2,
3286 P_START_DATE IN DATE,
3287 P_END_DATE IN DATE
3288 )
3289 AS
3290 p_salesperson_flag VARCHAR2(1):= 'Y'; -- flag for salespersons, empty or not
3291 x_status NUMBER;
3292 x_from_tg_id NUMBER;
3293 x_from_tg_acct_id NUMBER;
3294 x_named_acct_id NUMBER;
3295 x_new_named_acct_id NUMBER;
3296 x_to_tg_id NUMBER;
3297 x_to_tg_acct_id NUMBER;
3298 p_Api_Version_Number NUMBER := 1.0;
3299 p_Init_Msg_List VARCHAR2(1) := FND_API.G_FALSE;
3300 p_Commit VARCHAR2(1) := FND_API.G_FALSE;
3301 p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
3302 X_Return_Status VARCHAR2(1);
3303 X_Msg_Count NUMBER;
3304 l_party_site_id NUMBER;
3305 X_Msg_Data VARCHAR2(2000);
3306 l_matching_rule_code VARCHAR2(30);
3307
3308
3309 p_from_tg JTF_TTY_TERR_GROUPS.TERR_GROUP_NAME%TYPE;
3310 p_to_tg JTF_TTY_TERR_GROUPS.TERR_GROUP_NAME%TYPE;
3311 p_delete_flag_code VARCHAR2(1);
3312 l_excel_rscs_tbl EXCEL_SALESREP_RSC_TBLTYP;
3313 l_added_rscs_tbl SALESREP_RSC_TBL_TYPE;
3314 l_error_msg VARCHAR2(250);
3315 i integer :=0;
3316 l_start_date date;
3317 l_end_date date;
3318
3319 BEGIN
3320
3321 p_from_tg := P_TERRITORY_GROUP;
3322 p_to_tg := P_TO_TERRITORY_GROUP;
3323 l_excel_rscs_tbl := EXCEL_SALESREP_RSC_TBLTYP();
3324 l_added_rscs_tbl := SALESREP_RSC_TBL_TYPE();
3325 x_new_named_acct_id :=-999;
3326
3327 -- set start and end date
3328 l_start_date := P_START_DATE;
3329 l_end_date := P_END_DATE;
3330
3331
3332 /* check if party number is missing */
3333 IF (p_party_number is null OR trim(p_party_number) is null) THEN
3334 fnd_message.clear;
3335 FND_MESSAGE.SET_NAME ('JTF', 'JTF_TTY_PARTYNUM_MISSING');
3336 RETURN;
3337 END IF;
3338
3339 /* check for invalid scenario */
3340
3341 IF (p_from_tg is null AND p_to_tg is null) THEN
3342 fnd_message.clear;
3343 FND_MESSAGE.SET_NAME ('JTF', 'JTF_TTY_INVALID_ACTION');
3344 RETURN;
3345 END IF;
3346
3347 IF p_to_tg IS NOT NULL THEN
3348
3349 BEGIN
3350
3351 Select matching_rule_code
3352 into l_matching_rule_code
3353 from jtf_tty_terr_groups
3354 where terr_group_name = p_to_tg;
3355
3356 if((p_party_site_id is null or trim(p_party_site_id) is null ) and (trim(l_matching_rule_code) = '1' or trim(l_matching_rule_code) = '2' or trim(l_matching_rule_code) = '5'))
3357 then
3358 raise no_data_found;
3359
3360 elsif ((p_party_site_id is null or trim(p_party_site_id) is null ) and (trim(l_matching_rule_code) <> '1' or trim(l_matching_rule_code) <> '2' or trim(l_matching_rule_code) <> '5'))
3361 then
3362
3363 select party_site_id into l_party_site_id
3364 from hz_party_sites party_site, hz_parties party
3365 where party.party_number = p_party_number
3366 and party.party_id = party_site.party_id
3367 and party_site.status = 'A'
3368 and party_site.identifying_address_flag = 'Y';
3369
3370 else
3371 SELECT PARTY_SITE_ID
3372 INTO l_party_site_id
3373 FROM hz_party_sites
3374 WHERE party_site_number = P_PARTY_SITE_ID;
3375 end if;
3376
3377
3378 EXCEPTION
3379 WHEN NO_DATA_FOUND THEN
3380 fnd_message.clear;
3381 FND_MESSAGE.SET_NAME ('JTF', 'JTF_TTY_INV_PARTY_SITE_NUM');
3382 RETURN;
3383 WHEN OTHERS THEN
3384 l_error_msg := substr(sqlerrm,1,200);
3385 fnd_message.clear;
3386 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
3387 fnd_message.set_token('ERRMSG', l_error_msg );
3388 return;
3389 END;
3390 END IF;
3391
3392
3393
3394 /* check for invalid delete flag */
3395 BEGIN
3396 SELECT lookup_code
3397 INTO p_delete_flag_code
3398 FROM fnd_lookups
3399 WHERE lookup_type = 'JTF_TERR_FLAGS'
3400 AND upper(meaning) = upper(p_delete_flag)
3401 AND rownum < 2;
3402 EXCEPTION
3403 WHEN NO_DATA_FOUND THEN
3404 fnd_message.clear;
3405 FND_MESSAGE.SET_NAME ('JTF', 'JTF_TTY_INV_DELETE_FLAG');
3406 RETURN;
3407 WHEN OTHERS THEN
3408 l_error_msg := substr(sqlerrm,1,200);
3409 fnd_message.clear;
3410 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
3411 fnd_message.set_token('ERRMSG', l_error_msg );
3412 return;
3413 END;
3414
3415
3416 /* check for invalid scenario, white spaces */
3417 IF (trim(p_from_tg) is null AND trim(p_to_tg) is null) THEN
3418 fnd_message.clear;
3419 FND_MESSAGE.SET_NAME ('JTF', 'JTF_TTY_INVALID_ACTION');
3420 RETURN;
3421 END IF;
3422
3423 /* Retrieve the terr group account details if trying to delete a
3424 named account, transfer a named account or update sales team */
3425 IF (p_from_tg is not null and trim(p_from_tg) is not null) THEN
3426 -- dbms_output.put_line('Sandeep - before GET_TERR_GRP_ACCT_DETAILS');
3427
3428 /*Added to fix BUG8209345*/
3429
3430 IF (p_party_site_id is null or trim(p_party_site_id) is null) THEN
3431
3432 raise no_data_found;
3433
3434 ELSE
3435
3436 SELECT PARTY_SITE_ID
3437 INTO l_party_site_id
3438 FROM hz_party_sites
3439 WHERE party_site_number = P_PARTY_SITE_ID;
3440
3441
3442 END IF;
3443
3444 /*BUG8209345*/
3445
3446 GET_TERR_GRP_ACCT_DETAILS(
3447 p_Api_Version_Number => 1.0 ,
3448 p_Init_Msg_List => FND_API.G_FALSE ,
3449 p_Commit => FND_API.G_FALSE ,
3450 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3451 X_Return_Status => X_Return_Status ,
3452 X_Msg_Count => X_Msg_Count ,
3453 X_Msg_Data => X_Msg_Data,
3454 P_PARTY_NUMBER => P_PARTY_NUMBER,
3455 P_PARTY_SITE_ID => l_party_site_id,
3456 P_ATTRIBUTE1 => P_ATTRIBUTE1,
3457 P_ATTRIBUTE2 => P_ATTRIBUTE2,
3458 P_ATTRIBUTE3 => P_ATTRIBUTE3,
3459 P_ATTRIBUTE4 => P_ATTRIBUTE4,
3460 P_ATTRIBUTE5 => P_ATTRIBUTE5,
3461 P_ATTRIBUTE6 => P_ATTRIBUTE6,
3462 P_ATTRIBUTE7 => P_ATTRIBUTE7,
3463 P_ATTRIBUTE8 => P_ATTRIBUTE8,
3464 P_ATTRIBUTE9 => P_ATTRIBUTE9,
3465 P_ATTRIBUTE10 => P_ATTRIBUTE10,
3466 P_ATTRIBUTE11 => P_ATTRIBUTE11,
3467 P_ATTRIBUTE12 => P_ATTRIBUTE12,
3468 P_ATTRIBUTE13 => P_ATTRIBUTE13,
3469 P_ATTRIBUTE14 => P_ATTRIBUTE14,
3470 P_ATTRIBUTE15 => P_ATTRIBUTE15,
3471 P_START_DATE => l_start_date,
3472 P_END_DATE => l_end_date,
3473 P_TERR_GRP_NAME => P_FROM_TG,
3474 X_TERR_GRP_ID => X_FROM_TG_ID,
3475 X_TERR_GRP_ACCT_ID => X_FROM_TG_ACCT_ID,
3476 X_NAMED_ACCT_ID => X_NAMED_ACCT_ID);
3477
3478 IF (X_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
3479 RETURN; /* unable to get terr gp acct details due to wrong party number
3480 --* wrong tg name or party not a na in the tg */
3481 END IF;
3482
3483 JTF_TTY_GEN_TERR_PVT.update_terr_for_na(x_from_tg_acct_id, x_from_tg_id );
3484
3485 END IF;
3486
3487 IF (p_resource1_name is null and p_resource2_name is null
3488 and p_resource3_name is null and p_resource4_name is null
3489 and p_resource5_name is null and p_resource6_name is null
3490 and p_resource7_name is null and p_resource8_name is null
3491 and p_resource9_name is null and p_resource10_name is null
3492 and p_resource11_name is null and p_resource12_name is null
3493 and p_resource13_name is null and p_resource14_name is null
3494 and p_resource15_name is null and p_resource16_name is null
3495 and p_resource17_name is null and p_resource18_name is null
3496 and p_resource19_name is null and p_resource20_name is null
3497 and p_resource21_name is null and p_resource22_name is null
3498 and p_resource23_name is null and p_resource24_name is null
3499 and p_resource25_name is null and p_resource26_name is null
3500 and p_resource27_name is null and p_resource28_name is null
3501 and p_resource29_name is null and p_resource30_name is null) THEN
3502
3503 p_salesperson_flag := 'N'; /* no sales persons entered*/
3504 ELSE /* populate pl/sql table with salespersons from excel document */
3505 -- dbms_output.put_line('Sandeep - before populating l_excel_rscs_tbl');
3506 WHILE (i < 30) LOOP
3507 i:= i + 1;
3508 l_excel_rscs_tbl.extend;
3509 if (i = 1) then
3510 l_excel_rscs_tbl(i).resource_name := p_resource1_name;
3511 l_excel_rscs_tbl(i).group_name := p_group1_name;
3512 l_excel_rscs_tbl(i).role_name := p_role1_name;
3513 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES1_ATT1;
3514 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES1_ATT2;
3515 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES1_ATT3;
3516 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES1_ATT4;
3517 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES1_ATT5;
3518 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES1_START_DATE;
3519 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES1_END_DATE;
3520
3521 -- dbms_output.put_line('Attribute 1: '|| l_excel_rscs_tbl(i).RESOURCE_ATT1);
3522 end if;
3523 if (i = 2) then
3524 l_excel_rscs_tbl(i).resource_name := p_resource2_name;
3525 l_excel_rscs_tbl(i).group_name := p_group2_name;
3526 l_excel_rscs_tbl(i).role_name := p_role2_name;
3527 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES2_ATT1;
3528 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES2_ATT2;
3529 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES2_ATT3;
3530 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES2_ATT4;
3531 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES2_ATT5;
3532 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES2_START_DATE;
3533 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES2_END_DATE;
3534
3535 end if;
3536 if (i = 3) then
3537 l_excel_rscs_tbl(i).resource_name := p_resource3_name;
3538 l_excel_rscs_tbl(i).group_name := p_group3_name;
3539 l_excel_rscs_tbl(i).role_name := p_role3_name;
3540 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES3_ATT1;
3541 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES3_ATT2;
3542 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES3_ATT3;
3543 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES3_ATT4;
3544 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES3_ATT5;
3545 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES3_START_DATE;
3546 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES3_END_DATE;
3547
3548 end if;
3549 if (i = 4) then
3550 l_excel_rscs_tbl(i).resource_name := p_resource4_name;
3551 l_excel_rscs_tbl(i).group_name := p_group4_name;
3552 l_excel_rscs_tbl(i).role_name := p_role4_name;
3553 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES4_ATT1;
3554 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES4_ATT2;
3555 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES4_ATT3;
3556 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES4_ATT4;
3557 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES4_ATT5;
3558 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES4_START_DATE;
3559 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES4_END_DATE;
3560
3561 end if;
3562 if (i = 5) then
3563 l_excel_rscs_tbl(i).resource_name := p_resource5_name;
3564 l_excel_rscs_tbl(i).group_name := p_group5_name;
3565 l_excel_rscs_tbl(i).role_name := p_role5_name;
3566 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES5_ATT1;
3567 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES5_ATT2;
3568 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES5_ATT3;
3569 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES5_ATT4;
3570 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES5_ATT5;
3571 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES5_START_DATE;
3572 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES5_END_DATE;
3573
3574 end if;
3575 if (i = 6) then
3576 l_excel_rscs_tbl(i).resource_name := p_resource6_name;
3577 l_excel_rscs_tbl(i).group_name := p_group6_name;
3578 l_excel_rscs_tbl(i).role_name := p_role6_name;
3579 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES6_ATT1;
3580 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES6_ATT2;
3581 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES6_ATT3;
3582 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES6_ATT4;
3583 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES6_ATT5;
3584 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES6_START_DATE;
3585 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES6_END_DATE;
3586
3587 end if;
3588 if (i = 7) then
3589 l_excel_rscs_tbl(i).resource_name := p_resource7_name;
3590 l_excel_rscs_tbl(i).group_name := p_group7_name;
3591 l_excel_rscs_tbl(i).role_name := p_role7_name;
3592 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES7_ATT1;
3593 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES7_ATT2;
3594 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES7_ATT3;
3595 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES7_ATT4;
3596 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES7_ATT5;
3597 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES7_START_DATE;
3598 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES7_END_DATE;
3599
3600 end if;
3601 if (i = 8) then
3602 l_excel_rscs_tbl(i).resource_name := p_resource8_name;
3603 l_excel_rscs_tbl(i).group_name := p_group8_name;
3604 l_excel_rscs_tbl(i).role_name := p_role8_name;
3605 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES8_ATT1;
3606 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES8_ATT2;
3607 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES8_ATT3;
3608 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES8_ATT4;
3609 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES8_ATT5;
3610 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES8_START_DATE;
3611 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES8_END_DATE;
3612
3613 end if;
3614 if (i = 9) then
3615 l_excel_rscs_tbl(i).resource_name := p_resource9_name;
3616 l_excel_rscs_tbl(i).group_name := p_group9_name;
3617 l_excel_rscs_tbl(i).role_name := p_role9_name;
3618 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES9_ATT1;
3619 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES9_ATT2;
3620 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES9_ATT3;
3621 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES9_ATT4;
3622 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES9_ATT5;
3623 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES9_START_DATE;
3624 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES9_END_DATE;
3625
3626 end if;
3627 if (i = 10) then
3628 l_excel_rscs_tbl(i).resource_name := p_resource10_name;
3629 l_excel_rscs_tbl(i).group_name := p_group10_name;
3630 l_excel_rscs_tbl(i).role_name := p_role10_name;
3631 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES10_ATT1;
3632 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES10_ATT2;
3633 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES10_ATT3;
3634 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES10_ATT4;
3635 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES10_ATT5;
3636 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES10_START_DATE;
3637 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES10_END_DATE;
3638
3639 end if;
3640 if (i = 11) then
3641 l_excel_rscs_tbl(i).resource_name := p_resource11_name;
3642 l_excel_rscs_tbl(i).group_name := p_group11_name;
3643 l_excel_rscs_tbl(i).role_name := p_role11_name;
3644 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES11_ATT1;
3645 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES11_ATT2;
3646 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES11_ATT3;
3647 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES11_ATT4;
3648 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES11_ATT5;
3649 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES11_START_DATE;
3650 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES11_END_DATE;
3651
3652 end if;
3653 if (i = 12) then
3654 l_excel_rscs_tbl(i).resource_name := p_resource12_name;
3655 l_excel_rscs_tbl(i).group_name := p_group12_name;
3656 l_excel_rscs_tbl(i).role_name := p_role12_name;
3657 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES12_ATT1;
3658 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES12_ATT2;
3659 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES12_ATT3;
3660 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES12_ATT4;
3661 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES12_ATT5;
3662 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES12_START_DATE;
3663 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES12_END_DATE;
3664
3665 end if;
3666 if (i = 13) then
3667 l_excel_rscs_tbl(i).resource_name := p_resource13_name;
3668 l_excel_rscs_tbl(i).group_name := p_group13_name;
3669 l_excel_rscs_tbl(i).role_name := p_role13_name;
3670 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES13_ATT1;
3671 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES13_ATT2;
3672 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES13_ATT3;
3673 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES13_ATT4;
3674 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES13_ATT5;
3675 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES13_START_DATE;
3676 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES13_END_DATE;
3677
3678 end if;
3679 if (i = 14) then
3680 l_excel_rscs_tbl(i).resource_name := p_resource14_name;
3681 l_excel_rscs_tbl(i).group_name := p_group14_name;
3682 l_excel_rscs_tbl(i).role_name := p_role14_name;
3683 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES14_ATT1;
3684 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES14_ATT2;
3685 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES14_ATT3;
3686 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES14_ATT4;
3687 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES14_ATT5;
3688 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES14_START_DATE;
3689 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES14_END_DATE;
3690
3691 end if;
3692 if (i = 15) then
3693 l_excel_rscs_tbl(i).resource_name := p_resource15_name;
3694 l_excel_rscs_tbl(i).group_name := p_group15_name;
3695 l_excel_rscs_tbl(i).role_name := p_role15_name;
3696 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES15_ATT1;
3697 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES15_ATT2;
3698 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES15_ATT3;
3699 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES15_ATT4;
3700 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES15_ATT5;
3701 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES15_START_DATE;
3702 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES15_END_DATE;
3703
3704 end if;
3705 if (i = 16) then
3706 l_excel_rscs_tbl(i).resource_name := p_resource16_name;
3707 l_excel_rscs_tbl(i).group_name := p_group16_name;
3708 l_excel_rscs_tbl(i).role_name := p_role16_name;
3709 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES16_ATT1;
3710 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES16_ATT2;
3711 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES16_ATT3;
3712 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES16_ATT4;
3713 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES16_ATT5;
3714 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES16_START_DATE;
3715 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES16_END_DATE;
3716
3717 end if;
3718 if (i = 17) then
3719 l_excel_rscs_tbl(i).resource_name := p_resource17_name;
3720 l_excel_rscs_tbl(i).group_name := p_group17_name;
3721 l_excel_rscs_tbl(i).role_name := p_role17_name;
3722 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES17_ATT1;
3723 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES17_ATT2;
3724 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES17_ATT3;
3725 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES17_ATT4;
3726 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES17_ATT5;
3727 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES17_START_DATE;
3728 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES17_END_DATE;
3729
3730 end if;
3731 if (i = 18) then
3732 l_excel_rscs_tbl(i).resource_name := p_resource18_name;
3733 l_excel_rscs_tbl(i).group_name := p_group18_name;
3734 l_excel_rscs_tbl(i).role_name := p_role18_name;
3735 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES18_ATT1;
3736 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES18_ATT2;
3737 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES18_ATT3;
3738 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES18_ATT4;
3739 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES18_ATT5;
3740 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES18_START_DATE;
3741 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES18_END_DATE;
3742
3743 end if;
3744 if (i = 19) then
3745 l_excel_rscs_tbl(i).resource_name := p_resource19_name;
3746 l_excel_rscs_tbl(i).group_name := p_group19_name;
3747 l_excel_rscs_tbl(i).role_name := p_role19_name;
3748 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES19_ATT1;
3749 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES19_ATT2;
3750 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES19_ATT3;
3751 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES19_ATT4;
3752 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES19_ATT5;
3753 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES19_START_DATE;
3754 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES19_END_DATE;
3755
3756 end if;
3757 if (i = 20) then
3758 l_excel_rscs_tbl(i).resource_name := p_resource20_name;
3759 l_excel_rscs_tbl(i).group_name := p_group20_name;
3760 l_excel_rscs_tbl(i).role_name := p_role20_name;
3761 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES20_ATT1;
3762 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES20_ATT2;
3763 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES20_ATT3;
3764 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES20_ATT4;
3765 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES20_ATT5;
3766 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES20_START_DATE;
3767 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES20_END_DATE;
3768
3769 end if;
3770 if (i = 21) then
3771 l_excel_rscs_tbl(i).resource_name := p_resource21_name;
3772 l_excel_rscs_tbl(i).group_name := p_group21_name;
3773 l_excel_rscs_tbl(i).role_name := p_role21_name;
3774 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES21_ATT1;
3775 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES21_ATT2;
3776 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES21_ATT3;
3777 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES21_ATT4;
3778 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES21_ATT5;
3779 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES21_START_DATE;
3780 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES21_END_DATE;
3781
3782 end if;
3783 if (i = 22) then
3784 l_excel_rscs_tbl(i).resource_name := p_resource22_name;
3785 l_excel_rscs_tbl(i).group_name := p_group22_name;
3786 l_excel_rscs_tbl(i).role_name := p_role22_name;
3787 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES22_ATT1;
3788 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES22_ATT2;
3789 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES22_ATT3;
3790 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES22_ATT4;
3791 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES22_ATT5;
3792 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES22_START_DATE;
3793 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES22_END_DATE;
3794
3795 end if;
3796 if (i = 23) then
3797 l_excel_rscs_tbl(i).resource_name := p_resource23_name;
3798 l_excel_rscs_tbl(i).group_name := p_group23_name;
3799 l_excel_rscs_tbl(i).role_name := p_role23_name;
3800 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES23_ATT1;
3801 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES23_ATT2;
3802 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES23_ATT3;
3803 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES23_ATT4;
3804 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES23_ATT5;
3805 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES23_START_DATE;
3806 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES23_END_DATE;
3807
3808 end if;
3809 if (i = 24) then
3810 l_excel_rscs_tbl(i).resource_name := p_resource24_name;
3811 l_excel_rscs_tbl(i).group_name := p_group24_name;
3812 l_excel_rscs_tbl(i).role_name := p_role24_name;
3813 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES24_ATT1;
3814 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES24_ATT2;
3815 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES24_ATT3;
3816 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES24_ATT4;
3817 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES24_ATT5;
3818 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES24_START_DATE;
3819 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES24_END_DATE;
3820
3821 end if;
3822 if (i = 25) then
3823 l_excel_rscs_tbl(i).resource_name := p_resource25_name;
3824 l_excel_rscs_tbl(i).group_name := p_group25_name;
3825 l_excel_rscs_tbl(i).role_name := p_role25_name;
3826 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES25_ATT1;
3827 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES25_ATT2;
3828 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES25_ATT3;
3829 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES25_ATT4;
3830 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES25_ATT5;
3831 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES25_START_DATE;
3832 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES25_END_DATE;
3833
3834 end if;
3835 if (i = 26) then
3836 l_excel_rscs_tbl(i).resource_name := p_resource26_name;
3837 l_excel_rscs_tbl(i).group_name := p_group26_name;
3838 l_excel_rscs_tbl(i).role_name := p_role26_name;
3839 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES26_ATT1;
3840 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES26_ATT2;
3841 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES26_ATT3;
3842 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES26_ATT4;
3843 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES26_ATT5;
3844 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES26_START_DATE;
3845 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES26_END_DATE;
3846
3847 end if;
3848 if (i = 27) then
3849 l_excel_rscs_tbl(i).resource_name := p_resource27_name;
3850 l_excel_rscs_tbl(i).group_name := p_group27_name;
3851 l_excel_rscs_tbl(i).role_name := p_role27_name;
3852 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES27_ATT1;
3853 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES27_ATT2;
3854 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES27_ATT3;
3855 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES27_ATT4;
3856 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES27_ATT5;
3857 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES27_START_DATE;
3858 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES27_END_DATE;
3859
3860 end if;
3861 if (i = 28) then
3862 l_excel_rscs_tbl(i).resource_name := p_resource28_name;
3863 l_excel_rscs_tbl(i).group_name := p_group28_name;
3864 l_excel_rscs_tbl(i).role_name := p_role28_name;
3865 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES28_ATT1;
3866 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES28_ATT2;
3867 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES28_ATT3;
3868 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES28_ATT4;
3869 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES28_ATT5;
3870 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES28_START_DATE;
3871 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES28_END_DATE;
3872
3873 end if;
3874 if (i = 29) then
3875 l_excel_rscs_tbl(i).resource_name := p_resource29_name;
3876 l_excel_rscs_tbl(i).group_name := p_group29_name;
3877 l_excel_rscs_tbl(i).role_name := p_role29_name;
3878 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES29_ATT1;
3879 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES29_ATT2;
3880 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES29_ATT3;
3881 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES29_ATT4;
3882 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES29_ATT5;
3883 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES29_START_DATE;
3884 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES29_END_DATE;
3885
3886 end if;
3887 if (i = 30) then
3888 l_excel_rscs_tbl(i).resource_name := p_resource30_name;
3889 l_excel_rscs_tbl(i).group_name := p_group30_name;
3890 l_excel_rscs_tbl(i).role_name := p_role30_name;
3891 l_excel_rscs_tbl(i).RESOURCE_ATT1 := P_RES30_ATT1;
3892 l_excel_rscs_tbl(i).RESOURCE_ATT2 := P_RES30_ATT2;
3893 l_excel_rscs_tbl(i).RESOURCE_ATT3 := P_RES30_ATT3;
3894 l_excel_rscs_tbl(i).RESOURCE_ATT4 := P_RES30_ATT4;
3895 l_excel_rscs_tbl(i).RESOURCE_ATT5 := P_RES30_ATT5;
3896 l_excel_rscs_tbl(i).RESOURCE_START_DATE := P_RES30_START_DATE;
3897 l_excel_rscs_tbl(i).RESOURCE_END_DATE := P_RES30_END_DATE;
3898
3899 end if;
3900 END LOOP;
3901 END IF;
3902 -- dbms_output.put_line('Sandeep - Done with populating l_excel_rscs_tbl');
3903 IF (P_DELETE_FLAG_CODE = 'Y') THEN /* trying to delete from TG */
3904 /* check for invalid scenarios */
3905 IF (p_from_tg is null AND p_to_tg is not null) THEN
3906 fnd_message.clear;
3907 FND_MESSAGE.SET_NAME ('JTF', 'JTF_TTY_INV_PROMOTE_DELETE');
3908 RETURN;
3909 ELSIF (p_from_tg is not null AND p_to_tg is not null) THEN
3910 fnd_message.clear;
3911 FND_MESSAGE.SET_NAME ('JTF', 'JTF_TTY_INV_TRANSFER_DELETE');
3912 RETURN;
3913 ELSE /* it is a valid delete from TG case */
3914 DELETE_ACCT_FROM_TG(P_TERR_GRP_ACCT_ID => X_FROM_TG_ACCT_ID,
3915 P_NAMED_ACCT_ID => X_NAMED_ACCT_ID,
3916 P_TERR_GRP_ID => X_FROM_TG_ID,
3917 P_Api_Version_Number => P_Api_Version_Number,
3918 p_Init_Msg_List => p_Init_Msg_List,
3919 p_Commit => p_Commit,
3920 p_validation_level => p_validation_level,
3921 X_Return_Status => X_Return_Status,
3922 X_Msg_Count => X_Msg_Count,
3923 X_Msg_Data => X_Msg_Data) ;
3924 END IF;
3925 ELSE /* Not a delete from the territory group, can be a
3926 update sales team, Add to TG, Add to tg with update
3927 salesteam, Transfer, or transfer with update sales team */
3928 BEGIN
3929 IF (p_from_tg is not null AND p_to_tg is null) THEN
3930 /* i.e. update sales team */
3931 -- dbms_output.put_line('Sandeep - Before doing validate Sales Team');
3932 -- Check if all the salespersons are valid ones
3933 -- if valid x_return_status <>
3934 validate_sales_team(
3935 P_Api_Version_Number ,
3936 p_Init_Msg_List ,
3937 p_Commit ,
3938 p_validation_level ,
3939 X_Return_Status ,
3940 X_Msg_Count ,
3941 X_Msg_Data ,
3942 X_FROM_TG_ID ,
3943 L_START_DATE,
3944 L_END_DATE,
3945 l_excel_rscs_tbl ,
3946 l_added_rscs_tbl);
3947 -- dbms_output.put_line('Sandeep - After doing validate Sales Team' || X_Return_Status);
3948 IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
3949 UPDATE_SALES_TEAM(P_Api_Version_Number ,
3950 p_Init_Msg_List ,
3951 p_Commit ,
3952 p_validation_level ,
3953 X_Return_Status ,
3954 X_Msg_Count ,
3955 X_Msg_Data ,
3956 l_added_rscs_tbl ,
3957 X_FROM_TG_ID ,
3958 X_FROM_TG_ACCT_ID ,
3959 X_NAMED_ACCT_ID,
3960 P_SALES_GROUP,
3961 P_SALES_ROLE);
3962 ELSE
3963 RETURN;
3964 END IF;
3965 -- dbms_output.put_line('Sandeep - After doing Update Sales Team');
3966
3967 JTF_TTY_GEN_TERR_PVT.update_terr_rscs_for_na(x_from_tg_acct_id, x_from_tg_id );
3968
3969 ELSIF (p_from_tg is null AND p_to_tg is not null) THEN
3970
3971 /* i.e. Add to Org or Add to Org with Update Sales Team */
3972 -- dbms_output.put_line('Sandeep - Add to Org/Add to Org with update sales Team -- Before Add Org to TG');
3973 ADD_ORG_TO_TG(P_Api_Version_Number => P_Api_Version_Number,
3974 p_Init_Msg_List => p_Init_Msg_List,
3975 p_Commit => p_Commit,
3976 p_validation_level => p_validation_level,
3977 X_Return_Status => X_Return_Status,
3978 X_Msg_Count => X_Msg_Count,
3979 X_Msg_Data => X_Msg_Data,
3980 p_party_number => p_party_number,
3981 p_party_site_id => l_party_site_id,
3982 p_terr_grp_name => p_to_tg,
3983 P_ATTRIBUTE1 => P_ATTRIBUTE1,
3984 P_ATTRIBUTE2 => P_ATTRIBUTE2,
3985 P_ATTRIBUTE3 => P_ATTRIBUTE3,
3986 P_ATTRIBUTE4 => P_ATTRIBUTE4,
3987 P_ATTRIBUTE5 => P_ATTRIBUTE5,
3988 P_ATTRIBUTE6 => P_ATTRIBUTE6,
3989 P_ATTRIBUTE7 => P_ATTRIBUTE7,
3990 P_ATTRIBUTE8 => P_ATTRIBUTE8,
3991 P_ATTRIBUTE9 => P_ATTRIBUTE9,
3992 P_ATTRIBUTE10 => P_ATTRIBUTE10,
3993 P_ATTRIBUTE11 => P_ATTRIBUTE11,
3994 P_ATTRIBUTE12 => P_ATTRIBUTE12,
3995 P_ATTRIBUTE13 => P_ATTRIBUTE13,
3996 P_ATTRIBUTE14 => P_ATTRIBUTE14,
3997 P_ATTRIBUTE15 => P_ATTRIBUTE15,
3998 P_START_DATE => L_START_DATE,
3999 P_END_DATE => L_END_DATE,
4000 x_terr_grp_acct_id => x_to_tg_acct_id,
4001 x_terr_grp_id => x_to_tg_id,
4002 x_named_acct_id => x_named_acct_id);
4003
4004 -- dbms_output.put_line('Sandeep - Add to Org/Add to Org with update sales Team -- After Add Org to TG: Status ' || X_Return_Status);
4005 IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN /* add of org is successful */
4006 if (p_salesperson_flag = 'N') THEN
4007 /* Add Org to TG, assign the account to territory group owners*/
4008
4009 ASSIGN_ACCT_TO_TG_OWNERS(
4010 P_Api_Version_Number => P_Api_Version_Number,
4011 p_Init_Msg_List => p_Init_Msg_List,
4012 p_Commit => p_Commit,
4013 p_validation_level => p_validation_level,
4014 X_Return_Status => X_Return_Status,
4015 X_Msg_Count => X_Msg_Count,
4016 X_Msg_Data => X_Msg_Data,
4017 p_terr_grp_acct_id => x_to_tg_acct_id,
4018 p_terr_grp_id => x_to_tg_id);
4019
4020
4021 else /* Add Org to TG with update sales team */
4022 -- Check if all the salespersons are valid ones
4023 -- if valid x_return_status <>
4024 -- dbms_output.put_line('Sandeep - Add Org to TG with update Sales Team Before doing validate Sales Team');
4025 validate_sales_team(
4026 P_Api_Version_Number ,
4027 p_Init_Msg_List ,
4028 p_Commit ,
4029 p_validation_level ,
4030 X_Return_Status ,
4031 X_Msg_Count ,
4032 X_Msg_Data ,
4033 X_TO_TG_ID ,
4034 L_START_DATE,
4035 L_END_DATE,
4036 l_excel_rscs_tbl ,
4037 l_added_rscs_tbl);
4038 -- dbms_output.put_line('Sandeep - Add Org to TG with update Sales Team After doing validate Sales Team: Status ' || X_Return_Status);
4039 IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
4040 BEGIN
4041 -- dbms_output.put_line('Sandeep - Add Org to TG with update Sales Team Before ADD_SALES_TEAM');
4042 ADD_SALES_TEAM(P_Api_Version_Number ,
4043 p_Init_Msg_List ,
4044 p_Commit ,
4045 p_validation_level ,
4046 X_Return_Status ,
4047 X_Msg_Count ,
4048 X_Msg_Data ,
4049 l_added_rscs_tbl ,
4050 X_TO_TG_ID ,
4051 X_TO_TG_ACCT_ID ,
4052 X_NAMED_ACCT_ID);
4053 -- dbms_output.put_line('Sandeep - Add Org to TG with update Sales Team After ADD_SALES_TEAM' || X_Return_Status);
4054
4055 END;
4056 ELSE
4057 RETURN;
4058 END IF;
4059 END IF; /* end of update sales team */
4060
4061 JTF_TTY_GEN_TERR_PVT.create_terr_for_na(x_to_tg_acct_id, x_to_tg_id );
4062 END IF; /* add of org successful */
4063 ELSE /* Transfer or Transfer with update Sales Team
4064 delete the account from the From TG */
4065 DELETE_ACCT_FROM_TG(P_TERR_GRP_ACCT_ID => X_FROM_TG_ACCT_ID,
4066 P_NAMED_ACCT_ID => X_NAMED_ACCT_ID,
4067 P_TERR_GRP_ID => X_FROM_TG_ID,
4068 P_Api_Version_Number => P_Api_Version_Number,
4069 p_Init_Msg_List => p_Init_Msg_List,
4070 p_Commit => p_Commit,
4071 p_validation_level => p_validation_level,
4072 X_Return_Status => X_Return_Status,
4073 X_Msg_Count => X_Msg_Count,
4074 X_Msg_Data => X_Msg_Data) ;
4075
4076
4077
4078 IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
4079 /* i.e. delete was successful, so move to add org to To Tg */
4080 ADD_ORG_TO_TG(P_Api_Version_Number => P_Api_Version_Number,
4081 p_Init_Msg_List => p_Init_Msg_List,
4082 p_Commit => p_Commit,
4083 p_validation_level => p_validation_level,
4084 X_Return_Status => X_Return_Status,
4085 X_Msg_Count => X_Msg_Count,
4086 X_Msg_Data => X_Msg_Data,
4087 p_party_number => p_party_number,
4088 p_party_site_id => l_party_site_id,
4089 p_terr_grp_name => p_to_tg,
4090 P_ATTRIBUTE1 => P_ATTRIBUTE1,
4091 P_ATTRIBUTE2 => P_ATTRIBUTE2,
4092 P_ATTRIBUTE3 => P_ATTRIBUTE3,
4093 P_ATTRIBUTE4 => P_ATTRIBUTE4,
4094 P_ATTRIBUTE5 => P_ATTRIBUTE5,
4095 P_ATTRIBUTE6 => P_ATTRIBUTE6,
4096 P_ATTRIBUTE7 => P_ATTRIBUTE7,
4097 P_ATTRIBUTE8 => P_ATTRIBUTE8,
4098 P_ATTRIBUTE9 => P_ATTRIBUTE9,
4099 P_ATTRIBUTE10 => P_ATTRIBUTE10,
4100 P_ATTRIBUTE11 => P_ATTRIBUTE11,
4101 P_ATTRIBUTE12 => P_ATTRIBUTE12,
4102 P_ATTRIBUTE13 => P_ATTRIBUTE13,
4103 P_ATTRIBUTE14 => P_ATTRIBUTE14,
4104 P_ATTRIBUTE15 => P_ATTRIBUTE15,
4105 P_START_DATE => L_START_DATE,
4106 P_END_DATE => L_END_DATE,
4107 x_terr_grp_acct_id => x_to_tg_acct_id,
4108 x_terr_grp_id => x_to_tg_id,
4109 x_named_acct_id => x_new_named_acct_id);
4110
4111 IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN /* add of org is successful */
4112 if (p_salesperson_flag = 'N') THEN
4113 /* Transfer with no update sales team,
4114 * assign the account to territory group owners*/
4115 ASSIGN_ACCT_TO_TG_OWNERS(
4116 P_Api_Version_Number => P_Api_Version_Number,
4117 p_Init_Msg_List => p_Init_Msg_List,
4118 p_Commit => p_Commit,
4119 p_validation_level => p_validation_level,
4120 X_Return_Status => X_Return_Status,
4121 X_Msg_Count => X_Msg_Count,
4122 X_Msg_Data => X_Msg_Data,
4123 p_terr_grp_acct_id => x_to_tg_acct_id,
4124 p_terr_grp_id => x_to_tg_id);
4125 else /* Transfer to TG with update sales team */
4126 validate_sales_team(
4127 P_Api_Version_Number ,
4128 p_Init_Msg_List ,
4129 p_Commit ,
4130 p_validation_level ,
4131 X_Return_Status ,
4132 X_Msg_Count ,
4133 X_Msg_Data ,
4134 X_TO_TG_ID ,
4135 L_START_DATE,
4136 L_END_DATE,
4137 l_excel_rscs_tbl ,
4138 l_added_rscs_tbl);
4139 IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
4140 ADD_SALES_TEAM(P_Api_Version_Number ,
4141 p_Init_Msg_List ,
4142 p_Commit ,
4143 p_validation_level ,
4144 X_Return_Status ,
4145 X_Msg_Count ,
4146 X_Msg_Data ,
4147 l_added_rscs_tbl ,
4148 X_TO_TG_ID ,
4149 X_TO_TG_ACCT_ID ,
4150 X_new_NAMED_ACCT_ID);
4151
4152
4153 ELSE
4154 RETURN;
4155 END IF;
4156 END IF; /* end of update sales team */
4157 END IF; /* add of org successful */
4158
4159 JTF_TTY_GEN_TERR_PVT.create_terr_for_na(x_to_tg_acct_id, x_to_tg_id );
4160 END IF; /* end of delete of org from the from TG was successful */
4161 END IF;/* end of transfer or transfer with update sales team */
4162
4163 END; /* of begin for not a delete form TG */
4164 END IF; /* not a delete form TG */
4165
4166 EXCEPTION
4167 WHEN OTHERS THEN
4168 l_error_msg := substr(sqlerrm,1,200);
4169 fnd_message.clear;
4170 fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
4171 fnd_message.set_token('ERRMSG', l_error_msg );
4172
4173 -- dbms_output.put_line ('error : '|| l_error_msg);
4174 END POPULATE_ADMIN_EXCEL_DATA;
4175 END JTF_TTY_MAINTAIN_NA_PVT;