[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_SALES_PUB
Source
1 Package Body JTF_TERR_SALES_PUB AS
2 /* $Header: jtfptsab.pls 120.5 2005/11/11 15:00:50 achanda ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTF_SALES_TERRITORY_PUB
6 -- ---------------------------------------------------
7 -- PURPOSE
8 -- Joint task force core Sales territory manager public api's.
9 -- This package is a public API for getting winning territories
10 -- or territory resources.
11 --
12 -- Procedures:
13 -- (see below for specification)
14 --
15 -- NOTES
16 -- This package is publicly available for use
17 --
18 -- HISTORY
19 -- 09/14/99 VNEDUNGA Created
20 -- 12/02/99 VNEDUNGA Chaging the Dynamic SQL
21 -- Added a call to reset global variable
22 -- in the Gte_WinningTerrMember procedure
23 -- 12/22/99 vnedunga Making chnages to filter
24 -- resource by resource type
25 -- 01/05/00 vnedunga Making chnages to API to confirm
26 -- with new qualifier chnages
27 -- 01/12/00 vnedunga Adding Currency code get_WinnigTerr API
28 -- 02/01/00 vnedunga Chnaging the get resource SQL
29 -- 02/08/00 vnedunga Chnaging the get resource SQL, = NULL to IS NULL
30 -- 02/08/00 vnedunga Type in third_party_flag in oppor/lead api
31 -- 02/24/00 vnedunga Making chnages to call the newly designed
32 -- Generated Engine packages
33 -- 02/24/00 vnedunga Adding the code to rerturn Catch all
34 -- if there was no qualifying Territory
35 -- 03/23/00 vnedunga Making changes to return full_access_flag
36 -- 05/04/00 vnedunga Adding pricing_date for sales
37 -- 06/14/00 vnedunga Changeing the get winning Terr memeber api
38 -- to return group_id
39 -- 06/19/00 vnedunga Adding partner_id inplace of third_party_flag
40 -- and category_code in place of line_of_business
41 -- 07/26/01 EIHSU changed all char_list references to 360
42 -- 07/31/01 EIHSU 9i compatibility changes: no more tbl of records
43 -- casted to table for cursor definition
44 -- 05/25/05 achanda Modified to 12.0 architecture
45 --
46 -- End of Comments
47 --
48 -- ***************************************************
49 -- GLOBAL VARIABLES
50 -- ***************************************************
51 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TERR_SALES_PUB';
52 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfptsab.pls';
53
54 G_NEW_LINE VARCHAR2(02) := FND_GLOBAL.Local_Chr(10);
55 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
56 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
57 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
58 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
59 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
60 G_APP_SHORT_NAME VARCHAR2(15) := FND_GLOBAL.Application_Short_Name;
61
62
63 /*========================================================================================*/
64 /*========================= ACCOUNT ======================================================*/
65 /*========================================================================================*/
66
67 -- ***************************************************
68 -- start of comments
69 -- ***************************************************
70 -- api name : Get_WinningTerrMembers - ### BULK ###
71 -- type : public.
72 -- function : Get winning territories members for an ACCOUNT
73 -- pre-reqs : Territories needs to be setup first
74 -- requirements :
75 -- business rules :
76
77 -- version : current version 1.0
78 -- initial version: initial version 1.0
79 --
80 -- notes: Public API for retreving a set of winning
81 -- territories resources. This is an overloaded
82 -- procedure for accounts,lead, oppor, service
83 -- requests, and collections.
84 --
85 -- end of comments
86 procedure Get_WinningTerrMembers
87 ( p_api_version_number IN number,
88 p_init_msg_list IN varchar2 := fnd_api.g_false,
89 p_TerrAccount_Rec IN JTF_TERRITORY_PUB.JTF_Account_BULK_rec_type,
90 p_Resource_Type IN varchar2,
91 p_Role IN varchar2,
92 x_return_status OUT NOCOPY varchar2,
93 x_msg_count OUT NOCOPY number,
94 X_msg_data OUT NOCOPY varchar2,
95 x_winners_rec OUT NOCOPY JTF_TERRITORY_PUB.WINNING_BULK_REC_TYPE,
96 p_top_level_terr_id IN NUMBER := FND_API.G_MISS_NUM,
97 p_num_winners IN NUMBER := FND_API.G_MISS_NUM
98 )
99 AS
100 l_api_name CONSTANT VARCHAR2(30) := 'Get_WinningTerrMembers_Acct';
101 l_api_version_number CONSTANT NUMBER := 1.0;
102
103 l_Counter NUMBER;
104
105 lx_winners_rec JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
106 l_sysdate DATE;
107
108 BEGIN
109 null;
110 EXCEPTION
111 WHEN FND_API.G_EXC_ERROR THEN
112 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
113 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
114 'jtf.plsql.jtf_terr_sales_pub.get_winningterrmembers.g_exc_error',
115 substr(x_msg_data, 1, 4000));
116 END IF;
117
118 WHEN OTHERS THEN
119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
120 x_msg_data := SQLCODE || ' : ' || SQLERRM;
121 x_msg_count := 1;
122 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
123 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
124 'jtf.plsql.jtf_terr_sales_pub.get_winningterrmembers.other',
125 substr(x_msg_data, 1, 4000));
126 END IF;
127
128 End Get_WinningTerrMembers;
129
130
131
132 /*========================================================================================*/
133 /*========================= OPPORTUNITY ==================================================*/
134 /*========================================================================================*/
135
136 -- ***************************************************
137 -- start of comments
138 -- ***************************************************
139 -- api name : Get_WinningTerrMembers - ### BULK ###
140 -- type : public.
141 -- function : Get winning territories members for an OPPORTUNITY
142 -- pre-reqs : Territories needs to be setup first
143 -- requirements :
144 -- business rules :
145
146 -- version : current version 1.0
147 -- initial version: initial version 1.0
148 --
149 -- notes: Public API for retreving a set of winning
150 -- territories resources. This is an overloaded
151 -- procedure for accounts,lead, oppor, service
152 -- requests, and collections.
153 --
154 -- end of comments
155 procedure Get_WinningTerrMembers
156 ( p_api_version_number IN number,
157 p_init_msg_list IN varchar2 := fnd_api.g_false,
158 p_TerrOppor_Rec IN JTF_TERRITORY_PUB.JTF_Oppor_BULK_rec_type,
159 p_Resource_Type IN varchar2,
160 p_Role IN varchar2,
161 x_return_status OUT NOCOPY varchar2,
162 x_msg_count OUT NOCOPY number,
163 X_msg_data OUT NOCOPY varchar2,
164 x_winners_rec OUT NOCOPY JTF_TERRITORY_PUB.WINNING_BULK_REC_TYPE
165 )
166 AS
167 l_api_name CONSTANT VARCHAR2(30) := 'Get_WinningTerrMembers_Oppor';
168 l_api_version_number CONSTANT NUMBER := 1.0;
169
170 l_Counter NUMBER;
171 l_sysdate DATE;
172
173 lx_winners_rec JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
174
175 BEGIN
176
177 null;
178
179 EXCEPTION
180 WHEN FND_API.G_EXC_ERROR THEN
181 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
182 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
183 'jtf.plsql.jtf_terr_sales_pub.get_winningterrmembers.g_exc_error',
184 substr(x_msg_data, 1, 4000));
185 END IF;
186
187 WHEN OTHERS THEN
188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
189 x_msg_data := SQLCODE || ' : ' || SQLERRM;
190 x_msg_count := 1;
191 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
192 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
193 'jtf.plsql.jtf_terr_sales_pub.get_winningterrmembers.other',
194 substr(x_msg_data, 1, 4000));
195 END IF;
196
197 End Get_WinningTerrMembers;
198
199
200
201
202 /*========================================================================================*/
203 /*========================= LEAD =========================================================*/
204 /*========================================================================================*/
205
206 -- ***************************************************
207 -- start of comments
208 -- ***************************************************
209 -- api name : Get_WinningTerrMembers - ### BULK ###
210 -- type : public.
211 -- function : Get winning territories members for a LEAD
212 -- pre-reqs : Territories needs to be setup first
213 -- requirements :
214 -- business rules :
215
216 -- version : current version 1.0
217 -- initial version: initial version 1.0
218 --
219 -- notes: Public API for retreving a set of winning
220 -- territories resources. This is an overloaded
221 -- procedure for accounts,lead, oppor, service
222 -- requests, and collections.
223 --
224 -- end of comments
225 procedure Get_WinningTerrMembers
226 ( p_api_version_number IN number,
227 p_init_msg_list IN varchar2 := fnd_api.g_false,
228 p_TerrLead_Rec IN JTF_TERRITORY_PUB.JTF_Lead_BULK_rec_type,
229 p_Resource_Type IN varchar2,
230 p_Role IN varchar2,
231 x_return_status OUT NOCOPY varchar2,
232 x_msg_count OUT NOCOPY number,
233 X_msg_data OUT NOCOPY varchar2,
234 x_winners_rec OUT NOCOPY JTF_TERRITORY_PUB.WINNING_BULK_REC_TYPE
235 )
236 AS
237 l_api_name CONSTANT VARCHAR2(30) := 'Get_WinningTerrMembers_Lead';
238 l_api_version_number CONSTANT NUMBER := 1.0;
239
240 l_Counter NUMBER;
241 l_sysdate DATE;
242
243 lx_winners_rec JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
244 l_trans_rec JTY_ASSIGN_REALTIME_PUB.bulk_trans_id_type;
245
246 BEGIN
247 -- debug message
248 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
249 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
250 'jtf.plsql.jtf_terr_sales_pub.get_winningterrmembers.begin',
251 'Start of the procedure jtf_terr_sales_pub.get_winningterrmembers');
252 END IF;
253
254 -- Standard call to check for call compatibility.
255 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
256 p_api_version_number,
257 l_api_name,
258 G_PKG_NAME)
259 THEN
260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
261 END IF;
262
263 -- Initialize message list if p_init_msg_list is set to TRUE.
264 IF FND_API.to_Boolean( p_init_msg_list )
265 THEN
266 FND_MSG_PUB.initialize;
267 END IF;
268
269 --
270 -- API body
271 --
272 x_return_status := FND_API.G_RET_STS_SUCCESS;
273 l_sysdate := SYSDATE;
274
275 FOR i IN p_TerrLead_Rec.sales_lead_id.FIRST .. p_TerrLead_Rec.sales_lead_id.LAST LOOP
276 l_trans_rec.trans_object_id1 := jtf_terr_number_list(p_TerrLead_Rec.sales_lead_id(i));
277 l_trans_rec.trans_object_id2 := jtf_terr_number_list(null);
278 l_trans_rec.trans_object_id3 := jtf_terr_number_list(null);
279 l_trans_rec.trans_object_id4 := jtf_terr_number_list(null);
280 l_trans_rec.trans_object_id5 := jtf_terr_number_list(null);
281 l_trans_rec.txn_date := jtf_terr_date_list(null);
282
283 JTY_ASSIGN_REALTIME_PUB.get_winners(
284 p_api_version_number => 1.0,
285 p_init_msg_list => FND_API.G_FALSE,
286 p_source_id => -1001,
287 p_trans_id => -1003,
288 p_mode => 'REAL TIME:RESOURCE',
289 p_param_passing_mechanism => 'PBR',
290 p_program_name => 'SALES/LEAD PROGRAM',
291 p_trans_rec => l_trans_rec,
292 p_name_value_pair => null,
293 p_role => null,
294 p_resource_type => null,
295 x_return_status => x_return_status,
296 x_msg_count => x_msg_count,
297 x_msg_data => x_msg_data,
298 x_winners_rec => lx_winners_rec
299 );
300
301 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
302 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
303 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
304 'jtf.plsql.jtf_terr_sales_pub.get_winningterrmembers.get_winners',
305 'API JTY_ASSIGN_REALTIME_PUB.get_winners has failed');
306 END IF;
307 RAISE FND_API.G_EXC_ERROR;
308 END IF;
309 END LOOP;
310
311 -- debug message
312 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
313 FND_LOG.string(FND_LOG.LEVEL_EVENT,
314 'jtf.plsql.jtf_terr_sales_pub.get_winningterrmembers.get_winners',
315 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.get_winners');
316 END IF;
317
318 /*
319 lp_Lead_rec := p_terrlead_rec;
320 lp_Lead_rec.trans_object_id := p_terrlead_rec.sales_lead_id;
321
322 jtf_terr_1001_lead_dyn.search_terr_rules(
323 p_rec => lp_Lead_rec
324 , x_rec => x_winners_rec
325 , p_role => p_role
326 , p_resource_type => p_resource_type );
327 */
328
329 l_counter := lx_winners_rec.terr_id.FIRST;
330 WHILE (l_counter <= lx_winners_rec.terr_id.LAST) LOOP
331
332 x_winners_rec.PARTY_ID.EXTEND;
333 x_winners_rec.PARTY_SITE_ID.EXTEND;
334 x_winners_rec.TRANS_OBJECT_ID.EXTEND;
335 x_winners_rec.TRANS_DETAIL_OBJECT_ID.EXTEND;
336 x_winners_rec.TERR_ID.EXTEND;
337 x_winners_rec.ABSOLUTE_RANK.EXTEND;
338 x_winners_rec.TERR_RSC_ID.EXTEND;
339 x_winners_rec.RESOURCE_ID.EXTEND;
340 x_winners_rec.RESOURCE_TYPE.EXTEND;
341 x_winners_rec.GROUP_ID.EXTEND;
342 x_winners_rec.ROLE.EXTEND;
343 x_winners_rec.PRIMARY_CONTACT_FLAG.EXTEND;
344 x_winners_rec.FULL_ACCESS_FLAG.EXTEND;
345
346 x_winners_rec.PARTY_ID(l_counter) := lx_winners_rec.trans_object_id(l_counter);
347 x_winners_rec.PARTY_SITE_ID(l_counter) := lx_winners_rec.trans_detail_object_id(l_counter);
348 x_winners_rec.TRANS_OBJECT_ID(l_counter) := lx_winners_rec.trans_object_id(l_counter);
349 x_winners_rec.TRANS_DETAIL_OBJECT_ID(l_counter) := lx_winners_rec.trans_detail_object_id(l_counter);
350 x_winners_rec.TERR_ID(l_counter) := lx_winners_rec.terr_id(l_counter);
351 x_winners_rec.ABSOLUTE_RANK(l_counter) := lx_winners_rec.absolute_rank(l_counter);
352 x_winners_rec.TERR_RSC_ID(l_counter) := lx_winners_rec.terr_rsc_id(l_counter);
353 x_winners_rec.RESOURCE_ID(l_counter) := lx_winners_rec.resource_id(l_counter);
354 x_winners_rec.RESOURCE_TYPE(l_counter) := lx_winners_rec.resource_type(l_counter);
355 x_winners_rec.GROUP_ID(l_counter) := lx_winners_rec.group_id(l_counter);
356 x_winners_rec.ROLE(l_counter) := lx_winners_rec.role(l_counter);
357 x_winners_rec.PRIMARY_CONTACT_FLAG(l_counter) := lx_winners_rec.PRIMARY_CONTACT_FLAG(l_counter);
358 x_winners_rec.FULL_ACCESS_FLAG(l_counter) := lx_winners_rec.FULL_ACCESS_FLAG(l_counter);
359
360 l_counter := l_counter + 1;
361
362 END LOOP;
363
364 -- debug message
365 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
366 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
367 'jtf.plsql.jtf_terr_sales_pub.get_winningterrmembers.end',
368 'End of the procedure jtf_terr_sales_pub.get_winningterrmembers');
369 END IF;
370
371 EXCEPTION
372 WHEN FND_API.G_EXC_ERROR THEN
373 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
374 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
375 'jtf.plsql.jtf_terr_sales_pub.get_winningterrmembers.g_exc_error',
376 substr(x_msg_data, 1, 4000));
377 END IF;
378
379 WHEN OTHERS THEN
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
381 x_msg_data := SQLCODE || ' : ' || SQLERRM;
382 x_msg_count := 1;
383 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
384 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
385 'jtf.plsql.jtf_terr_sales_pub.get_winningterrmembers.other',
386 substr(x_msg_data, 1, 4000));
387 END IF;
388
389 End Get_WinningTerrMembers;
390
391
392
393
394 --####################################################
395 -- For APPLICATIONS RELEASE 11.5.6 AND BEYOND, THE
396 -- FOLLOWING APIs SHOULD NO LONGER BE USED
397 --####################################################
398
399
400 /*========================================================================================*/
401 /*========================= ACCOUNT ======================================================*/
402 /*========================================================================================*/
403 -- ***************************************************
404 -- start of comments
405 -- ***************************************************
406 -- api name : Get_WinningTerrMembers - ### SINGLE ###
407 -- type : public.
408
409
410 --
411 -- For APPLICATIONS RELEASE 11.5.6 AND BEYOND, THIS API SHOULD NOT BE USED
412 --
413
414
415 -- function : Get winning territories members for an ACCOUNT
416 -- pre-reqs : Territories needs to be setup first
417 -- requirements :
418 -- business rules :
419
420 -- version : current version 1.0
421 -- initial version: initial version 1.0
422 --
423 -- notes: Public API for retreving a set of winning
424 -- territories resources. This is an overloaded
425 -- procedure for accounts,lead, oppor, service
426 -- requests, and collections.
427 --
428 -- end of comments
429 procedure Get_WinningTerrMembers
430 ( p_api_version_number IN number,
431 p_init_msg_list IN varchar2 := fnd_api.g_false,
432 p_TerrAccount_Rec IN JTF_TERRITORY_PUB.JTF_Account_rec_type,
433 p_Resource_Type IN varchar2,
434 p_Role IN varchar2,
435 x_return_status OUT NOCOPY varchar2,
436 x_msg_count OUT NOCOPY number,
437 X_msg_data OUT NOCOPY varchar2,
438 x_TerrResource_tbl OUT NOCOPY JTF_TERRITORY_PUB.WinningTerrMember_tbl_type
439 ) AS
440
441 p_Rec JTF_TERRITORY_PUB.JTF_Account_bulk_rec_type;
442 x_rec JTF_TERRITORY_PUB.Winning_bulk_rec_type;
443 l_RscCounter NUMBER := 0;
444 l_counter NUMBER := 0;
445
446 BEGIN
447 /* initialise BULK tables with SINGLE record values */
448 p_Rec.party_id := jtf_terr_number_list(p_terraccount_rec.party_id);
449 p_Rec.party_site_id := jtf_terr_number_list(p_terraccount_rec.party_site_id);
450 p_rec.city := jtf_terr_char_360list(p_terraccount_rec.city);
451 p_Rec.postal_code := jtf_terr_char_360list(p_terraccount_rec.postal_code);
452 p_rec.state := jtf_terr_char_360list(p_terraccount_rec.state);
453 p_rec.province := jtf_terr_char_360list(p_terraccount_rec.province);
454 p_rec.county := jtf_terr_char_360list(p_terraccount_rec.county);
455 p_rec.country := jtf_terr_char_360list(p_terraccount_rec.country);
456 p_rec.interest_type_id := jtf_terr_number_list(p_terraccount_rec.interest_type_id);
457 p_rec.primary_interest_id := jtf_terr_number_list(p_terraccount_rec.primary_interest_id);
458 p_rec.secondary_interest_id := jtf_terr_number_list(p_terraccount_rec.secondary_interest_id);
459 p_rec.area_code := jtf_terr_char_360list(p_terraccount_rec.area_code);
460 p_rec.comp_name_range := jtf_terr_char_360list(p_terraccount_rec.comp_name_range);
461 p_rec.partner_id := jtf_terr_number_list(p_terraccount_rec.partner_id);
462 p_rec.num_of_employees := jtf_terr_number_list(p_terraccount_rec.num_of_employees);
463 p_rec.category_code := jtf_terr_char_360list(p_terraccount_rec.category_code);
464 p_rec.party_relationship_id := jtf_terr_number_list(p_terraccount_rec.party_relationship_id);
465 p_rec.sic_code := jtf_terr_char_360list(p_terraccount_rec.sic_code);
466 p_rec.attribute1 := jtf_terr_char_360list(p_terraccount_rec.attribute1);
467 p_rec.attribute2 := jtf_terr_char_360list(p_terraccount_rec.attribute2);
468 p_rec.attribute3 := jtf_terr_char_360list(p_terraccount_rec.attribute3);
469 p_rec.attribute4 := jtf_terr_char_360list(p_terraccount_rec.attribute4);
470 p_rec.attribute5 := jtf_terr_char_360list(p_terraccount_rec.attribute5);
471 p_rec.attribute6 := jtf_terr_char_360list(p_terraccount_rec.attribute6);
472 p_rec.attribute7 := jtf_terr_char_360list(p_terraccount_rec.attribute7);
473 p_rec.attribute8 := jtf_terr_char_360list(p_terraccount_rec.attribute8);
474 p_rec.attribute9 := jtf_terr_char_360list(p_terraccount_rec.attribute9);
475 p_rec.attribute10 := jtf_terr_char_360list(p_terraccount_rec.attribute10);
476 p_rec.attribute11 := jtf_terr_char_360list(p_terraccount_rec.attribute11);
477 p_rec.attribute12 := jtf_terr_char_360list(p_terraccount_rec.attribute12);
478 p_rec.attribute13 := jtf_terr_char_360list(p_terraccount_rec.attribute13);
479 p_rec.attribute14 := jtf_terr_char_360list(p_terraccount_rec.attribute14);
480 p_rec.attribute15 := jtf_terr_char_360list(p_terraccount_rec.attribute15);
481
482
483 JTF_TERR_SALES_PUB.Get_WinningTerrMembers(
484 P_Api_Version_Number => P_Api_Version_Number,
485 P_Init_Msg_List => p_init_msg_list,
486 p_TerrAccount_Rec => p_Rec,
487 p_resource_type => p_resource_type,
488 p_Role => p_role,
489 X_Return_Status => x_return_status,
490 X_Msg_Count => x_Msg_Count,
491 X_Msg_Data => x_Msg_Data,
492 x_winners_rec => x_rec);
493
494 l_counter := x_rec.terr_id.FIRST;
495
496 WHILE (l_counter <= x_rec.terr_id.LAST) LOOP
497
498 x_TerrResource_tbl(l_counter).TERR_RSC_ID := x_rec.terr_rsc_id(l_counter);
499 x_TerrResource_tbl(l_counter).RESOURCE_ID := x_rec.resource_id(l_counter);
500 x_TerrResource_tbl(l_counter).RESOURCE_TYPE := x_rec.resource_type(l_counter);
501 x_TerrResource_tbl(l_counter).GROUP_ID := x_rec.group_id(l_counter);
502 x_TerrResource_tbl(l_counter).ROLE := x_rec.role(l_counter);
503 x_TerrResource_tbl(l_counter).PRIMARY_CONTACT_FLAG := x_rec.primary_contact_flag(l_counter);
504 x_TerrResource_tbl(l_counter).FULL_ACCESS_FLAG := x_rec.full_access_flag(l_counter);
505 x_TerrResource_tbl(l_counter).TERR_ID := x_rec.terr_id(l_counter);
506 x_TerrResource_tbl(l_counter).ABSOLUTE_RANK := x_rec.absolute_rank(l_counter);
507
508 l_counter := l_counter + 1;
509
510 END LOOP;
511
512 end Get_WinningTerrMembers;
513
514
515 -- ***************************************************
516 -- start of comments
517 -- ***************************************************
518 -- api name : Get_WinningTerritories - ### SINGLE ###
519 -- type : public.
520
521 --
522 -- For APPLICATIONS RELEASE 11.5.6 AND BEYOND, THIS API SHOULD NOT BE USED
523 --
524
525 -- function : Get the WINNING territories for an ACCOUNT
526 -- pre-reqs : Territories needs to be setup first
527 -- requirements :
528 -- business rules :
529 -- version : current version 1.0
530 -- initial version: initial version 1.0
531 --
532 -- notes: Public API for retrieving the winning territories
533 -- This is an overloaded procedure for accounts,lead,
534 -- opportunity, service requests, and collections.
535 --
536 --
537 -- end of comments
538 procedure Get_WinningTerritories
539 ( p_api_version_number IN number,
540 p_init_msg_list IN varchar2 := fnd_api.g_false,
541 x_return_status OUT NOCOPY varchar2,
542 x_msg_count OUT NOCOPY number,
543 X_msg_data OUT NOCOPY varchar2,
544 p_TerrAcct_Rec IN JTF_TERRITORY_PUB.JTF_Account_rec_type
545 )AS
546
547 p_Rec JTF_TERRITORY_PUB.JTF_Account_bulk_rec_type;
548 x_rec JTF_TERRITORY_PUB.Winning_bulk_rec_type;
549
550 l_RscCounter NUMBER := 0;
551
552 BEGIN
553
554 /* initialise BULK tables with SINGLE record values */
555 p_Rec.POSTAL_CODE := jtf_terr_char_360list(p_terracct_rec.postal_code);
556 p_Rec.PARTY_ID := jtf_terr_number_list(p_terracct_rec.party_id);
557 p_Rec.PARTY_SITE_ID := jtf_terr_number_list(p_terracct_rec.party_site_id);
558 p_rec.city := jtf_terr_char_360list(p_terracct_rec.city);
559 p_Rec.postal_code := jtf_terr_char_360list(p_terracct_rec.postal_code);
560 p_rec.state := jtf_terr_char_360list(p_terracct_rec.state);
561 p_rec.province := jtf_terr_char_360list(p_terracct_rec.province);
562 p_rec.county := jtf_terr_char_360list(p_terracct_rec.county);
563 p_rec.country := jtf_terr_char_360list(p_terracct_rec.country);
564 p_rec.interest_type_id := jtf_terr_number_list(p_terracct_rec.interest_type_id);
565 p_rec.primary_interest_id := jtf_terr_number_list(p_terracct_rec.primary_interest_id);
566 p_rec.secondary_interest_id := jtf_terr_number_list(p_terracct_rec.secondary_interest_id);
567 p_rec.area_code := jtf_terr_char_360list(p_terracct_rec.area_code);
568 p_rec.comp_name_range := jtf_terr_char_360list(p_terracct_rec.comp_name_range);
569 p_rec.partner_id := jtf_terr_number_list(p_terracct_rec.partner_id);
570 p_rec.num_of_employees := jtf_terr_number_list(p_terracct_rec.num_of_employees);
571 p_rec.category_code := jtf_terr_char_360list(p_terracct_rec.category_code);
572 p_rec.party_relationship_id := jtf_terr_number_list(p_terracct_rec.party_relationship_id);
573 p_rec.sic_code := jtf_terr_char_360list(p_terracct_rec.sic_code);
574 p_rec.attribute1 := jtf_terr_char_360list(p_terracct_rec.attribute1);
575 p_rec.attribute2 := jtf_terr_char_360list(p_terracct_rec.attribute2);
576 p_rec.attribute3 := jtf_terr_char_360list(p_terracct_rec.attribute3);
577 p_rec.attribute4 := jtf_terr_char_360list(p_terracct_rec.attribute4);
578 p_rec.attribute5 := jtf_terr_char_360list(p_terracct_rec.attribute5);
579 p_rec.attribute6 := jtf_terr_char_360list(p_terracct_rec.attribute6);
580 p_rec.attribute7 := jtf_terr_char_360list(p_terracct_rec.attribute7);
581 p_rec.attribute8 := jtf_terr_char_360list(p_terracct_rec.attribute8);
582 p_rec.attribute9 := jtf_terr_char_360list(p_terracct_rec.attribute9);
583 p_rec.attribute10 := jtf_terr_char_360list(p_terracct_rec.attribute10);
584 p_rec.attribute11 := jtf_terr_char_360list(p_terracct_rec.attribute11);
585 p_rec.attribute12 := jtf_terr_char_360list(p_terracct_rec.attribute12);
586 p_rec.attribute13 := jtf_terr_char_360list(p_terracct_rec.attribute13);
587 p_rec.attribute14 := jtf_terr_char_360list(p_terracct_rec.attribute14);
588 p_rec.attribute15 := jtf_terr_char_360list(p_terracct_rec.attribute15);
589
590 JTF_TERR_SALES_PUB.Get_WinningTerritories( p_api_version_number => 1.0,
591 p_init_msg_list => p_init_msg_list,
592 x_return_status => x_return_status,
593 x_msg_count => x_msg_count,
594 X_msg_data => X_msg_data,
595 p_TerrAcct_Rec => p_Rec,
596 x_winners_rec => x_rec);
597 end Get_WinningTerritories;
598
599
600 --
601 -- Start of Comments
602 -- ***************************************************
603 -- start of comments
604 -- ***************************************************
605 -- api name : Get_WinningTerritories - ### BULK ###
606 -- type : public.
607
608 --
609 -- For APPLICATIONS RELEASE 11.5.6 AND BEYOND, THIS API SHOULD NOT BE USED
610 --
611
612 -- function : Get the WINNING territories for an ACCOUNT
613 -- pre-reqs : Territories needs to be setup first
614 -- requirements :
615 -- business rules :
616 -- version : current version 1.0
617 -- initial version: initial version 1.0
618 --
619 -- notes: Public API for retrieving the winning territories
620 -- This is an overloaded procedure for accounts,lead,
621 -- opportunity, service requests, and collections.
622 --
623 --
624 -- end of comments
625 procedure Get_WinningTerritories
626 ( p_api_version_number IN number,
627 p_init_msg_list IN varchar2 := fnd_api.g_false,
628 x_return_status OUT NOCOPY varchar2,
629 x_msg_count OUT NOCOPY number,
630 X_msg_data OUT NOCOPY varchar2,
631 p_TerrAcct_Rec IN JTF_TERRITORY_PUB.JTF_Account_BULK_rec_type,
632 x_winners_rec OUT NOCOPY JTF_TERRITORY_PUB.Winning_BULK_rec_type,
633 p_top_level_terr_id IN NUMBER := FND_API.G_MISS_NUM,
634 p_num_winners IN NUMBER := FND_API.G_MISS_NUM,
635 p_role IN VARCHAR2 := FND_API.G_MISS_CHAR
636 )
637 AS
638 l_api_name CONSTANT VARCHAR2(30) := 'Get_WinningTerritories_Acct';
639 l_api_version_number CONSTANT NUMBER := 1.0;
640 l_return_status VARCHAR2(1);
641 l_counter NUMBER;
642 l_Org_Id NUMBER ;
643 l_cursor NUMBER ;
644 l_sql VARCHAR2(32767) ;
645 l_num_rows NUMBER ;
646 l_RetCode BOOLEAN;
647 l_Num_Of_Winners NUMBER;
648 l_Profile_Value VARCHAR2(25);
649 l_NoOfWinTerr NUMBER;
650 l_Terr_Record JTF_TERRITORY_PUB.WinningTerr_Rec_type;
651
652 l_two_levels VARCHAR2(10) := 'TRUE';
653 l_count NUMBER := 1;
654 l_count1 NUMBER := 0;
655 l_count2 NUMBER := 0;
656
657 /* This cursor is not allowed in 9i
658 CURSOR c_get_win_terr IS
659 SELECT DISTINCT wt.column_value
660 FROM TABLE ( CAST( x_winners_rec.terr_id AS JTF_TERR_NUMBER_LIST) ) AS wt;
661 */
662
663
664 /* 2167091 bug fix: JDOCHERT: 01/17/02 */
665 lp_Acc_Rec JTF_TERRITORY_PUB.JTF_Account_BULK_rec_type;
666
667 BEGIN
668 -- Standard call to check for call compatibility.
669 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
670 p_api_version_number,
671 l_api_name,
672 G_PKG_NAME)
673 THEN
674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
675 END IF;
676
677 -- Initialize message list if p_init_msg_list is set to TRUE.
678 IF FND_API.to_Boolean( p_init_msg_list )
679 THEN
680 FND_MSG_PUB.initialize;
681 END IF;
682
683 -- Debug Message
684 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
685 THEN
686 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_WIN_ACCT_START');
687 FND_MSG_PUB.Add;
688 END IF;
689
690 -- Initialize API return status to success
691 x_return_status := FND_API.G_RET_STS_SUCCESS;
692
693 --Reset the global variables
694 l_RetCode := JTF_TERRITORY_GLOBAL_PUB.Reset;
695
696 /* 2167091 bug fix: JDOCHERT: 01/17/02 */
697 lp_Acc_Rec := p_TerrAcct_Rec;
698 lp_Acc_Rec.trans_object_id := p_TerrAcct_Rec.party_id;
699
700 jtf_terr_1001_account_dyn.search_terr_rules(
701 p_rec => lp_Acc_Rec
702 , x_rec => x_winners_rec
703 , p_top_level_terr_id => p_top_level_terr_id
704 , p_num_winners => p_num_winners
705 , p_role => p_role);
706
707 /*=======================================================================================
708 START OF CODE USED BY AMS (Oracle Marketing)
709 =======================================================================================*/
710 IF (x_winners_rec.terr_id.LAST >= 1) THEN
711
712 -- put first terr_id into global table record
713 JTF_TERRITORY_GLOBAL_PUB.G_WinningTerr_Tbl(1).terr_id
714 := x_winners_rec.terr_id(x_winners_rec.terr_id.first);
715
716 -- insert additional territories if they do not exist in the global table record
717 l_count1 := x_winners_rec.terr_id.FIRST;
718 WHILE (l_count1 <= x_winners_rec.terr_id.LAST) LOOP
719 l_count2 := JTF_TERRITORY_GLOBAL_PUB.G_WinningTerr_Tbl.FIRST;
720
721 WHILE (l_count2 <= JTF_TERRITORY_GLOBAL_PUB.G_WinningTerr_Tbl.LAST) LOOP
722 EXIT WHEN JTF_TERRITORY_GLOBAL_PUB.G_WinningTerr_Tbl(l_count2).terr_id = x_winners_rec.terr_id(l_count1);
723
724 IF (l_count2 = JTF_TERRITORY_GLOBAL_PUB.G_WinningTerr_Tbl.LAST) THEN
725 JTF_TERRITORY_GLOBAL_PUB.G_WinningTerr_Tbl(l_count2 + 1).terr_id := x_winners_rec.terr_id(l_count1);
726 END IF;
727 l_count2 := l_count2 + 1;
728
729 END LOOP;
730 l_count1 := l_count1 + 1;
731
732 END LOOP;
733
734 END IF;
735
736 -- Get the number of Territories in the Global Table
737 l_NoOfWinTerr := JTF_TERRITORY_GLOBAL_PUB.get_RecordCount;
738
739 /* If the program did not find qualifying Territory then
740 ** add Catch all
741 */
742 If l_NoOfWinTerr = 0 Then
743
744 l_Terr_Record.TERR_ID := 1;
745 l_Terr_Record.RANK := 0;
746 JTF_TERRITORY_GLOBAL_PUB.Add_Record( p_WinningTerr_Rec => l_Terr_Record,
747 p_Number_Of_Winners => l_Num_Of_Winners,
748 X_Return_Status => l_Return_Status);
749 End If;
750
751 /*=======================================================================================
752 END OF CODE USED BY AMS (Oracle Marketing)
753 =======================================================================================*/
754
755 -- Debug Message
756 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
757 THEN
758 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_WIN_ACCT_END');
759 FND_MSG_PUB.Add;
760 END IF;
761
762 -- Standard call to get message count and if count is 1, get message info.
763 FND_MSG_PUB.Count_And_Get
764 ( p_count => x_msg_count,
765 p_data => x_msg_data
766 );
767
768
769 EXCEPTION
770
771 WHEN FND_API.G_EXC_ERROR THEN
772 x_return_status := FND_API.G_RET_STS_ERROR ;
773
774 FND_MSG_PUB.Count_And_Get
775 ( p_count => x_msg_count,
776 p_data => x_msg_data
777 );
778
779
780 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
782
783 FND_MSG_PUB.Count_And_Get
784 ( p_count => x_msg_count,
785 p_data => x_msg_data
786 );
787
788
789 WHEN OTHERS THEN
790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
791
792 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
793 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_GEN_TERR_PACKAGE');
794 FND_MESSAGE.Set_Token('JTF', 'JTF_TERR_1001_ACCOUNT_DYN');
795 FND_MSG_PUB.Add;
796 END IF;
797
798 FND_MSG_PUB.Count_And_Get
799 ( p_count => x_msg_count,
800 p_data => x_msg_data
801 );
802 --
803 End Get_WinningTerritories;
804
805
806 /*========================================================================================*/
807 /*========================= OPPORTUNITY ==================================================*/
808 /*========================================================================================*/
809
810 -- ***************************************************
811 -- start of comments
812 -- ***************************************************
813 -- api name : Get_WinningTerrMembers - ### SINGLE ###
814 -- type : public.
815
816
817 --
818 -- For APPLICATIONS RELEASE 11.5.6 AND BEYOND, THIS API SHOULD NOT BE USED
819 --
820
821
822 -- function : Get winning territories members for an OPPORTUNITY
823 -- pre-reqs : Territories needs to be setup first
824 -- requirements :
825 -- business rules :
826
827 -- version : current version 1.0
828 -- initial version: initial version 1.0
829 --
830 -- notes: Public API for retreving a set of winning
831 -- territories resources. This is an overloaded
832 -- procedure for accounts,lead, oppor, service
833 -- requests, and collections.
834 --
835 -- end of comments
836 procedure Get_WinningTerrMembers
837 ( p_api_version_number IN number,
838 p_init_msg_list IN varchar2 := fnd_api.g_false,
839 p_TerrOppor_Rec IN JTF_TERRITORY_PUB.JTF_Oppor_rec_type,
840 p_Resource_Type IN varchar2,
841 p_Role IN varchar2,
842 x_return_status OUT NOCOPY varchar2,
843 x_msg_count OUT NOCOPY number,
844 X_msg_data OUT NOCOPY varchar2,
845 x_TerrResource_tbl OUT NOCOPY JTF_TERRITORY_PUB.WinningTerrMember_tbl_type
846 ) AS
847
848 p_Rec JTF_TERRITORY_PUB.JTF_Oppor_bulk_rec_type;
849 x_rec JTF_TERRITORY_PUB.Winning_bulk_rec_type;
850 l_RscCounter NUMBER := 0;
851 l_counter NUMBER := 0;
852
853 BEGIN
854
855
856 /* initialise BULK tables with SINGLE record values */
857 p_Rec.lead_id := jtf_terr_number_list(p_terroppor_rec.lead_id);
858 p_Rec.lead_line_id := jtf_terr_number_list(p_terroppor_rec.lead_line_id);
859 p_rec.city := jtf_terr_char_360list(p_terroppor_rec.city);
860 p_Rec.postal_code := jtf_terr_char_360list(p_terroppor_rec.postal_code);
861 p_rec.state := jtf_terr_char_360list(p_terroppor_rec.state);
862 p_rec.province := jtf_terr_char_360list(p_terroppor_rec.province);
863 p_rec.county := jtf_terr_char_360list(p_terroppor_rec.county);
864 p_rec.country := jtf_terr_char_360list(p_terroppor_rec.country);
865 p_rec.interest_type_id := jtf_terr_number_list(p_terroppor_rec.interest_type_id);
866 p_rec.primary_interest_id := jtf_terr_number_list(p_terroppor_rec.primary_interest_id);
867 p_rec.secondary_interest_id := jtf_terr_number_list(p_terroppor_rec.secondary_interest_id);
868 p_Rec.party_id := jtf_terr_number_list(p_terroppor_rec.party_id);
869 p_Rec.party_site_id := jtf_terr_number_list(p_terroppor_rec.party_site_id);
870 p_rec.area_code := jtf_terr_char_360list(p_terroppor_rec.area_code);
871 p_rec.comp_name_range := jtf_terr_char_360list(p_terroppor_rec.comp_name_range);
872 p_rec.partner_id := jtf_terr_number_list(p_terroppor_rec.partner_id);
873 p_rec.num_of_employees := jtf_terr_number_list(p_terroppor_rec.num_of_employees);
874 p_rec.category_code := jtf_terr_char_360list(p_terroppor_rec.category_code);
875 p_rec.party_relationship_id := jtf_terr_number_list(p_terroppor_rec.party_relationship_id);
876 p_rec.sic_code := jtf_terr_char_360list(p_terroppor_rec.sic_code);
877 p_rec.target_segment_current := jtf_terr_char_360list(p_terroppor_rec.target_segment_current);
878 p_rec.total_amount := jtf_terr_number_list(p_terroppor_rec.total_amount);
879 p_rec.currency_code := jtf_terr_char_360list(p_terroppor_rec.currency_code);
880 p_rec.pricing_date := jtf_terr_date_list(p_terroppor_rec.pricing_date);
881 p_rec.channel_code := jtf_terr_char_360list(p_terroppor_rec.channel_code);
882 p_rec.inventory_item_id := jtf_terr_number_list(p_terroppor_rec.inventory_item_id);
883 p_rec.opp_interest_type_id := jtf_terr_number_list(p_terroppor_rec.opp_interest_type_id);
884 p_rec.opp_primary_interest_id := jtf_terr_number_list(p_terroppor_rec.opp_primary_interest_id);
885 p_rec.opp_secondary_interest_id := jtf_terr_number_list(p_terroppor_rec.opp_secondary_interest_id);
886 p_rec.opclss_interest_type_id := jtf_terr_number_list(p_terroppor_rec.opclss_interest_type_id);
887 p_rec.opclss_primary_interest_id := jtf_terr_number_list(p_terroppor_rec.opclss_primary_interest_id);
888 p_rec.opclss_secondary_interest_id := jtf_terr_number_list(p_terroppor_rec.opclss_secondary_interest_id);
889 p_rec.attribute1 := jtf_terr_char_360list(p_terroppor_rec.attribute1);
890 p_rec.attribute2 := jtf_terr_char_360list(p_terroppor_rec.attribute2);
891 p_rec.attribute3 := jtf_terr_char_360list(p_terroppor_rec.attribute3);
892 p_rec.attribute4 := jtf_terr_char_360list(p_terroppor_rec.attribute4);
893 p_rec.attribute5 := jtf_terr_char_360list(p_terroppor_rec.attribute5);
894 p_rec.attribute6 := jtf_terr_char_360list(p_terroppor_rec.attribute6);
895 p_rec.attribute7 := jtf_terr_char_360list(p_terroppor_rec.attribute7);
896 p_rec.attribute8 := jtf_terr_char_360list(p_terroppor_rec.attribute8);
897 p_rec.attribute9 := jtf_terr_char_360list(p_terroppor_rec.attribute9);
898 p_rec.attribute10 := jtf_terr_char_360list(p_terroppor_rec.attribute10);
899 p_rec.attribute11 := jtf_terr_char_360list(p_terroppor_rec.attribute11);
900 p_rec.attribute12 := jtf_terr_char_360list(p_terroppor_rec.attribute12);
901 p_rec.attribute13 := jtf_terr_char_360list(p_terroppor_rec.attribute13);
902 p_rec.attribute14 := jtf_terr_char_360list(p_terroppor_rec.attribute14);
903 p_rec.attribute15 := jtf_terr_char_360list(p_terroppor_rec.attribute15);
904
905
906 JTF_TERR_SALES_PUB.Get_WinningTerrMembers(
907 P_Api_Version_Number => P_Api_Version_Number,
908 P_Init_Msg_List => p_init_msg_list,
909 p_TerrOppor_Rec => p_Rec,
910 p_resource_type => p_resource_type,
911 p_Role => p_role,
912 X_Return_Status => x_return_status,
913 X_Msg_Count => x_Msg_Count,
914 X_Msg_Data => x_Msg_Data,
915 x_winners_rec => x_rec);
916
917
918 l_counter := x_rec.terr_id.FIRST;
919
920 WHILE (l_counter <= x_rec.terr_id.LAST) LOOP
921
922 x_TerrResource_tbl(l_counter).TERR_RSC_ID := x_rec.terr_rsc_id(l_counter);
923 x_TerrResource_tbl(l_counter).RESOURCE_ID := x_rec.resource_id(l_counter);
924 x_TerrResource_tbl(l_counter).RESOURCE_TYPE := x_rec.resource_type(l_counter);
925 x_TerrResource_tbl(l_counter).GROUP_ID := x_rec.group_id(l_counter);
926 x_TerrResource_tbl(l_counter).ROLE := x_rec.role(l_counter);
927 x_TerrResource_tbl(l_counter).PRIMARY_CONTACT_FLAG := x_rec.primary_contact_flag(l_counter);
928 x_TerrResource_tbl(l_counter).FULL_ACCESS_FLAG := x_rec.full_access_flag(l_counter);
929 x_TerrResource_tbl(l_counter).TERR_ID := x_rec.terr_id(l_counter);
930 x_TerrResource_tbl(l_counter).ABSOLUTE_RANK := x_rec.absolute_rank(l_counter);
931
932 l_counter := l_counter + 1;
933
934 END LOOP;
935
936 end Get_WinningTerrMembers;
937
938
939 /*========================================================================================*/
940 /*========================= LEAD =========================================================*/
941 /*========================================================================================*/
942
943 -- ***************************************************
944 -- start of comments
945 -- ***************************************************
946 -- api name : Get_WinningTerrMembers - ### SINGLE ###
947 -- type : public.
948
949 --
950 -- For APPLICATIONS RELEASE 11.5.6 AND BEYOND, THIS API SHOULD NOT BE USED
951 --
952
953 -- function : Get winning territories members for a LEAD
954 -- pre-reqs : Territories needs to be setup first
955 -- requirements :
956 -- business rules :
957
958 -- version : current version 1.0
959 -- initial version: initial version 1.0
960 --
961 -- notes: Public API for retreving a set of winning
962 -- territories resources. This is an overloaded
963 -- procedure for accounts,lead, oppor, service
964 -- requests, and collections.
965 --
966 -- end of comments
967 procedure Get_WinningTerrMembers
968 ( p_api_version_number IN number,
969 p_init_msg_list IN varchar2 := fnd_api.g_false,
970 p_TerrLead_Rec IN JTF_TERRITORY_PUB.JTF_Lead_rec_type,
971 p_Resource_Type IN varchar2,
972 p_Role IN varchar2,
973 x_return_status OUT NOCOPY varchar2,
974 x_msg_count OUT NOCOPY number,
975 X_msg_data OUT NOCOPY varchar2,
976 x_TerrResource_tbl OUT NOCOPY JTF_TERRITORY_PUB.WinningTerrMember_tbl_type
977 ) AS
978
979 p_Rec JTF_TERRITORY_PUB.JTF_Lead_bulk_rec_type;
980 x_rec JTF_TERRITORY_PUB.Winning_bulk_rec_type;
981 l_RscCounter NUMBER := 0;
982 l_counter NUMBER := 0;
983
984 BEGIN
985
986
987 /* initialise BULK tables with SINGLE record values */
988 p_Rec.sales_lead_id := jtf_terr_number_list(p_terrlead_rec.sales_lead_id);
989 p_Rec.sales_lead_line_id := jtf_terr_number_list(p_terrlead_rec.sales_lead_line_id);
990 p_rec.city := jtf_terr_char_360list(p_terrlead_rec.city);
991 p_Rec.postal_code := jtf_terr_char_360list(p_terrlead_rec.postal_code);
992 p_rec.state := jtf_terr_char_360list(p_terrlead_rec.state);
993 p_rec.province := jtf_terr_char_360list(p_terrlead_rec.province);
994 p_rec.county := jtf_terr_char_360list(p_terrlead_rec.county);
995 p_rec.country := jtf_terr_char_360list(p_terrlead_rec.country);
996 p_rec.interest_type_id := jtf_terr_number_list(p_terrlead_rec.interest_type_id);
997 p_rec.primary_interest_id := jtf_terr_number_list(p_terrlead_rec.primary_interest_id);
998 p_rec.secondary_interest_id := jtf_terr_number_list(p_terrlead_rec.secondary_interest_id);
999 p_Rec.party_id := jtf_terr_number_list(p_terrlead_rec.party_id);
1000 p_Rec.party_site_id := jtf_terr_number_list(p_terrlead_rec.party_site_id);
1001 p_rec.area_code := jtf_terr_char_360list(p_terrlead_rec.area_code);
1002 p_rec.comp_name_range := jtf_terr_char_360list(p_terrlead_rec.comp_name_range);
1003 p_rec.partner_id := jtf_terr_number_list(p_terrlead_rec.partner_id);
1004 p_rec.num_of_employees := jtf_terr_number_list(p_terrlead_rec.num_of_employees);
1005 p_rec.category_code := jtf_terr_char_360list(p_terrlead_rec.category_code);
1006 p_rec.party_relationship_id := jtf_terr_number_list(p_terrlead_rec.party_relationship_id);
1007 p_rec.sic_code := jtf_terr_char_360list(p_terrlead_rec.sic_code);
1008 p_rec.budget_amount := jtf_terr_number_list(p_terrlead_rec.budget_amount);
1009 p_rec.currency_code := jtf_terr_char_360list(p_terrlead_rec.currency_code);
1010 p_rec.pricing_date := jtf_terr_date_list(p_terrlead_rec.pricing_date);
1011 p_rec.source_promotion_id := jtf_terr_number_list(p_terrlead_rec.source_promotion_id);
1012 p_rec.inventory_item_id := jtf_terr_number_list(p_terrlead_rec.inventory_item_id);
1013 p_rec.lead_interest_type_id := jtf_terr_number_list(p_terrlead_rec.lead_interest_type_id);
1014 p_rec.lead_primary_interest_id := jtf_terr_number_list(p_terrlead_rec.lead_primary_interest_id);
1015 p_rec.lead_secondary_interest_id := jtf_terr_number_list(p_terrlead_rec.lead_secondary_interest_id);
1016 p_rec.purchase_amount := jtf_terr_number_list(p_terrlead_rec.purchase_amount);
1017 p_rec.attribute1 := jtf_terr_char_360list(p_terrlead_rec.attribute1);
1018 p_rec.attribute2 := jtf_terr_char_360list(p_terrlead_rec.attribute2);
1019 p_rec.attribute3 := jtf_terr_char_360list(p_terrlead_rec.attribute3);
1020 p_rec.attribute4 := jtf_terr_char_360list(p_terrlead_rec.attribute4);
1021 p_rec.attribute5 := jtf_terr_char_360list(p_terrlead_rec.attribute5);
1022 p_rec.attribute6 := jtf_terr_char_360list(p_terrlead_rec.attribute6);
1023 p_rec.attribute7 := jtf_terr_char_360list(p_terrlead_rec.attribute7);
1024 p_rec.attribute8 := jtf_terr_char_360list(p_terrlead_rec.attribute8);
1025 p_rec.attribute9 := jtf_terr_char_360list(p_terrlead_rec.attribute9);
1026 p_rec.attribute10 := jtf_terr_char_360list(p_terrlead_rec.attribute10);
1027 p_rec.attribute11 := jtf_terr_char_360list(p_terrlead_rec.attribute11);
1028 p_rec.attribute12 := jtf_terr_char_360list(p_terrlead_rec.attribute12);
1029 p_rec.attribute13 := jtf_terr_char_360list(p_terrlead_rec.attribute13);
1030 p_rec.attribute14 := jtf_terr_char_360list(p_terrlead_rec.attribute14);
1031 p_rec.attribute15 := jtf_terr_char_360list(p_terrlead_rec.attribute15);
1032
1033 JTF_TERR_SALES_PUB.Get_WinningTerrMembers(
1034 P_Api_Version_Number => P_Api_Version_Number,
1035 P_Init_Msg_List => p_init_msg_list,
1036 p_TerrLead_Rec => p_Rec,
1037 p_resource_type => p_resource_type,
1038 p_Role => p_role,
1039 X_Return_Status => x_return_status,
1040 X_Msg_Count => x_Msg_Count,
1041 X_Msg_Data => x_Msg_Data,
1042 x_winners_rec => x_rec);
1043
1044 l_counter := x_rec.terr_id.FIRST;
1045
1046 WHILE (l_counter <= x_rec.terr_id.LAST) LOOP
1047
1048 x_TerrResource_tbl(l_counter).TERR_RSC_ID := x_rec.terr_rsc_id(l_counter);
1049 x_TerrResource_tbl(l_counter).RESOURCE_ID := x_rec.resource_id(l_counter);
1050 x_TerrResource_tbl(l_counter).RESOURCE_TYPE := x_rec.resource_type(l_counter);
1051 x_TerrResource_tbl(l_counter).GROUP_ID := x_rec.group_id(l_counter);
1052 x_TerrResource_tbl(l_counter).ROLE := x_rec.role(l_counter);
1053 x_TerrResource_tbl(l_counter).PRIMARY_CONTACT_FLAG := x_rec.primary_contact_flag(l_counter);
1054 x_TerrResource_tbl(l_counter).FULL_ACCESS_FLAG := x_rec.full_access_flag(l_counter);
1055 x_TerrResource_tbl(l_counter).TERR_ID := x_rec.terr_id(l_counter);
1056 x_TerrResource_tbl(l_counter).ABSOLUTE_RANK := x_rec.absolute_rank(l_counter);
1057
1058 l_counter := l_counter + 1;
1059
1060 END LOOP;
1061
1062 end Get_WinningTerrMembers;
1063
1064 END JTF_TERR_SALES_PUB;