1 PACKAGE PV_TERR_ASSIGN_PUB AUTHID CURRENT_USER as
2 /* $Header: pvxpptas.pls 120.2 2005/08/11 00:18:42 appldev ship $ */
3
4 g_partner_name CONSTANT NUMBER := -1702 ;
5 g_city CONSTANT NUMBER := -1066 ;
6 g_county CONSTANT NUMBER := -1069 ;
7 g_state CONSTANT NUMBER := -1068 ;
8 g_province CONSTANT NUMBER := -1071 ;
9 g_postal_code CONSTANT NUMBER := -1067 ;
10 g_country CONSTANT NUMBER := -1065 ;
11 g_area_code CONSTANT NUMBER := -1009 ;
12 g_cust_catgy_code CONSTANT NUMBER := -1081 ;
13 g_partner_type CONSTANT NUMBER := -1703 ;
14 g_partner_level CONSTANT NUMBER := -1704 ;
15 g_number_of_employee CONSTANT NUMBER := -1016 ;
16 g_Annual_Revenue CONSTANT NUMBER := -1101 ;
17
18 --===================================================================
19 -- Start of Comments
20 -- -------------------------------------------------------
21 -- Record name
22 -- partner_qualifiers_rec_type
23 -- -------------------------------------------------------
24 -- Parameters:
25 -- partner_name
26 -- area_code
27 -- city
28 -- country
29 -- county
30 -- postal_code
31 -- province
32 -- state
33 -- Annual_Revenue
34 -- number_of_employee
35 -- customer_category_code
36 -- partner_type
37 -- partner_level
38 --
39 -- Required
40 --
41 -- Defaults
42 --
43 -- End of Comments
44
45 --===================================================================
46 TYPE partner_qualifiers_rec_type IS RECORD
47 (
48 partner_name VARCHAR2(360),
49 party_site_id NUMBER,
50 party_id NUMBER,
51 area_code VARCHAR2(10),
52 city VARCHAR2(60),
53 country VARCHAR2(60),
54 county VARCHAR2(60),
55 postal_code VARCHAR2(60),
56 province VARCHAR2(60),
57 state VARCHAR2(60),
58 Annual_Revenue NUMBER,
59 number_of_employee NUMBER,
60 customer_category_code VARCHAR2(30),
61 partner_type VARCHAR2(500),
62 partner_level VARCHAR2(30)
63 );
64
65 g_miss_partner_qualifiers_rec partner_qualifiers_rec_type := NULL;
66 TYPE partner_qualifiers_tbl_type IS TABLE OF partner_qualifiers_rec_type INDEX BY BINARY_INTEGER;
67 g_miss_partner_qualifiers_tbl partner_qualifiers_tbl_type;
68
69 --===================================================================
70 -- Start of Comments
71 -- -------------------------------------------------------
72 -- Record name
73 -- prtnr_qflr_flg_rec_type
74 -- -------------------------------------------------------
75 -- Parameters:
76 -- partner_name_flg
77 -- party_site_id_flg
78 -- area_code_flg
79 -- city_flg
80 -- country_flg
81 -- county_flg
82 -- postal_code_flg
83 -- province_flg
84 -- state_flg
85 -- Annual_Revenue_flg
86 -- number_of_employee_flg
87 -- cust_catgy_code_flg
88 -- partner_type_flg
89 -- partner_level_flg
90 --
91 -- Required
92 --
93 -- Defaults
94 --
95 -- End of Comments
96
97 --===================================================================
98 TYPE prtnr_qflr_flg_rec_type IS RECORD
99 (
100 partner_name_flg VARCHAR2(1),
101 party_site_id_flg VARCHAR2(1),
102 area_code_flg VARCHAR2(1),
103 city_flg VARCHAR2(1),
104 country_flg VARCHAR2(1),
105 county_flg VARCHAR2(1),
106 postal_code_flg VARCHAR2(1),
107 province_flg VARCHAR2(1),
108 state_flg VARCHAR2(1),
109 Annual_Revenue_flg VARCHAR2(1),
110 number_of_employee_flg VARCHAR2(1),
111 cust_catgy_code_flg VARCHAR2(1),
112 partner_type_flg VARCHAR2(1),
113 partner_level_flg VARCHAR2(1)
114 );
115
116 g_miss_prtnr_qflr_flg_rec prtnr_qflr_flg_rec_type := NULL;
117 TYPE prtnr_qflr_flg_tbl_type IS TABLE OF prtnr_qflr_flg_rec_type INDEX BY BINARY_INTEGER;
118 g_miss_prtnr_qflr_flg_tbl prtnr_qflr_flg_tbl_type;
119
120 -- Start of Comments
121 --
122 -- Funtion name : chk_prtnr_qflr_enabled
123 -- Type : Public
124 -- Function : The purpose of this function is to find out, whether
125 -- the supplied partner qualifiers is enabled or not.
126 --
127 -- Pre-reqs :
128 --
129 -- Paramaeters :
130 -- IN :
131 -- p_prtnr_qualifier IN NUMBER
132 -- OUT :
133 --
134 -- Version :
135 -- Initial version 1.0
136 --
137 -- Notes: Procedure to find out, whther the supplied partner qualifier
138 -- is enabled in the Qualifier Setup.
139 --
140 --
141 -- End of Comments
142 FUNCTION chk_prtnr_qflr_enabled (p_prtnr_qualifier IN NUMBER )
143 RETURN VARCHAR2 ;
144
145 TYPE ResourceList is TABLE OF NUMBER(15);
146 TYPE PersonList is TABLE OF NUMBER(15);
147 TYPE ResourceCategoryList is TABLE OF VARCHAR2(30);
148 TYPE GroupList is TABLE OF NUMBER(15);
149
150 TYPE ResourceRec is RECORD (
151 resource_id ResourceList ,
152 person_id PersonList ,
153 resource_category ResourceCategoryList ,
154 group_id GroupList );
155
156 PROCEDURE GET_RES_FROM_TEAM_GROUP(
157 P_RESOURCE_ID IN NUMBER,
158 P_RESOURCE_TYPE IN VARCHAR2,
159 X_RESOURCE_REC OUT NOCOPY PV_TERR_ASSIGN_PUB.ResourceRec
160 );
161
162 TYPE prtnr_aces_rec_type IS RECORD(
163 partner_access_id NUMBER );
164
165 g_miss_prtnr_aces_rec_type prtnr_aces_rec_type := NULL;
166 TYPE prtnr_aces_tbl_type IS TABLE OF prtnr_aces_rec_type INDEX BY BINARY_INTEGER;
167 g_miss_prtnr_aces_tbl prtnr_aces_tbl_type;
168
169
170
171 -- Start of Comments
172 --
173 -- API name : Get_Partner_Details
174 -- Type : Public
175 -- Function : The purpose of this procedure is to build a partner qualifiers
176 -- table for a given party_id
177 --
178 -- Pre-reqs :
179 --
180 -- Paramaeters :
181 -- IN :
182 -- p_party_id IN NUMBER
183 -- OUT :
184 -- x_return_status OUT VARCHAR2(1)
185 -- x_msg_count OUT NUMBER
186 -- x_msg_data OUT VARCHAR2(2000)
187 -- x_partner_qualifiers_tbl OUT partner_qualifiers_tbl_type
188 --
189 -- Version :
190 -- Initial version 1.0
191 --
192 -- Notes: API for creating a Channel Team for a Partner Organization.
193 --
194 --
195 -- End of Comments
196
197 PROCEDURE get_partner_details (
198 p_party_id IN NUMBER,
199 x_return_status OUT NOCOPY VARCHAR2,
200 x_msg_count OUT NOCOPY NUMBER,
201 x_msg_data OUT NOCOPY VARCHAR2,
202 x_partner_qualifiers_tbl OUT NOCOPY partner_qualifiers_tbl_type );
203
204 -- Start of Comments
205 --
206 -- NAME
207 -- PV_TERR_ASSIGN_PUB
208 --
209 -- PURPOSE
210 -- This package is a public API to create the channel team based on the user as well
211 -- as the pre defined qualifiers for the partner. This API will call the Do_Create_channel_team
212 -- which does all the required processing.This is more of an overloaded method for Do_Create_channel_team
213 --
214 -- Procedures:
215 -- Do_Create_Channel_Team
216 --
217 -- NOTES
218 -- This package is for private use only
219 --
220 -- Pre-reqs : Existing resource should have a "Channel Manager" or
221 -- "Channel Rep" role at group level.
222 --
223 -- Paramaeters :
224 -- IN :
225 -- p_api_version_number IN NUMBER,
226 -- p_init_msg_list IN VARCHAR2
227 -- p_commit IN VARCHAR2
228 -- p_validation_level IN NUMBER
229 -- p_partner_id IN NUMBER
230 -- p_vad_partner_id IN NUMBER
231 -- p_mode IN VARCHAR2
232 -- p_login_user IN NUMBER ,
233 --
234 --
235 -- OUT :
236 -- x_return_status OUT VARCHAR2(1)
237 -- x_msg_count OUT NUMBER
238 -- x_msg_data OUT VARCHAR2(2000)
239 -- x_prtnr_access_id_tbl OUT PV_TERR_ASSIGN_PUB.PartnerAccessRec
240 --
241 -- Version :
242 -- Initial version 1.0
243 --
244 -- HISTORY
245 -- 07/27/05 pinagara Created
246 --
247 -- End of Comments
248
249 PROCEDURE Create_Channel_Team
250 ( p_api_version_number IN NUMBER ,
251 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
252 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
253 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
254 p_partner_id IN NUMBER ,
255 p_vad_partner_id IN NUMBER ,
256 p_mode IN VARCHAR2 ,
257 p_login_user IN NUMBER ,
258 x_return_status OUT NOCOPY VARCHAR2,
259 x_msg_count OUT NOCOPY NUMBER,
260 x_msg_data OUT NOCOPY VARCHAR2,
261 x_prtnr_access_id_tbl OUT NOCOPY prtnr_aces_tbl_type
262 );
263
264 -- Start of Comments
265 --
266 -- NAME
267 -- PV_TERR_ASSIGN_PUB
268 --
269 -- PURPOSE
270 -- This package is a public API to create the channel team based on the user as well
271 -- as the pre defined qualifiers for the partner. This API inturn calls apis to create
272 -- the channel team based on territory as well as the logged in user.
273 --
274 -- Procedures:
275 -- Do_Create_Channel_Team
276 --
277 -- NOTES
278 -- This package is for private use only
279 --
280 -- Pre-reqs : Existing resource should have a "Channel Manager" or
281 -- "Channel Rep" role at group level.
282 --
283 -- Paramaeters :
284 -- IN :
285 -- p_api_version_number IN NUMBER,
286 -- p_init_msg_list IN VARCHAR2
287 -- p_commit IN VARCHAR2
288 -- p_validation_level IN NUMBER
289 -- p_partner_id IN NUMBER
290 -- p_vad_partner_id IN NUMBER
291 -- p_mode IN VARCHAR2
292 -- p_login_user IN NUMBER ,
293 -- p_partner_qualifiers_tbl IN partner_qualifiers_tbl_type,
294 --
295 --
296 -- OUT :
297 -- x_return_status OUT VARCHAR2(1)
298 -- x_msg_count OUT NUMBER
299 -- x_msg_data OUT VARCHAR2(2000)
300 -- x_prtnr_access_id_tbl OUT PV_TERR_ASSIGN_PUB.PartnerAccessRec
301 --
302 -- Version :
303 -- Initial version 1.0
304 --
305 -- HISTORY
306 -- 07/27/05 pinagara Created
307 --
308 -- End of Comments
309
310 PROCEDURE Do_Create_Channel_Team
311 ( p_api_version_number IN NUMBER ,
312 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
313 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
314 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
315 p_partner_id IN NUMBER ,
316 p_vad_partner_id IN NUMBER ,
317 p_mode IN VARCHAR2 ,
318 p_login_user IN NUMBER ,
319 p_partner_qualifiers_tbl IN partner_qualifiers_tbl_type,
320 x_return_status OUT NOCOPY VARCHAR2,
321 x_msg_count OUT NOCOPY NUMBER,
322 x_msg_data OUT NOCOPY VARCHAR2,
323 x_prtnr_access_id_tbl OUT NOCOPY prtnr_aces_tbl_type
324 );
325
326
327 -- Start of Comments
328 --
329 -- API name : Create_Online_Channel_Team
330 -- Type : Public
331 -- Function : The purpose of this procedure is to create a Channel
332 -- team for a given Partner_id in the PV_PARTNER_ACCESSES
333 -- table.
334 --
335 -- Pre-reqs : Existing resource should be a "Channel Manager" or
336 -- "Channel Rep" role at group level.
337 --
338 -- Paramaeters :
339 -- IN :
340 -- p_api_version_number IN NUMBER,
341 -- p_init_msg_list IN VARCHAR2
342 -- p_commit IN VARCHAR2
343 -- p_validation_level IN NUMBER
344 --
345 -- p_partner_id IN NUMBER
346 -- p_vad_partner_id IN NUMBER
347 -- p_login_user IN NUMBER
348 -- p_mode IN VARCHAR2
349 -- OUT :
350 -- x_return_status OUT VARCHAR2(1)
351 -- x_msg_count OUT NUMBER
352 -- x_msg_data OUT VARCHAR2(2000)
353 -- x_prtnr_access_id_tbl OUT JTF_NUMBER_TABLE
354 --
355 -- Version :
356 -- Initial version 1.0
357 --
358 -- Notes: API for creating a Channel Team for a Partner Organization.
359 --
360 --
361 -- End of Comments
362
363 PROCEDURE Create_Online_Channel_Team
364 ( p_api_version_number IN NUMBER ,
365 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
366 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
367 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
368 x_return_status OUT NOCOPY VARCHAR2,
369 x_msg_count OUT NOCOPY NUMBER,
370 x_msg_data OUT NOCOPY VARCHAR2,
371 p_partner_id IN NUMBER ,
372 p_vad_partner_id IN NUMBER ,
373 p_mode IN VARCHAR2 ,
374 p_login_user IN NUMBER ,
375 x_prtnr_access_id_tbl OUT NOCOPY prtnr_aces_tbl_type
376 );
377
378 --Obsoleted
379 PROCEDURE Do_Cr_Online_Chnl_Team
380 ( p_api_version_number IN NUMBER ,
381 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
382 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
383 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
384 x_return_status OUT NOCOPY VARCHAR2,
385 x_msg_count OUT NOCOPY NUMBER,
386 x_msg_data OUT NOCOPY VARCHAR2,
387 p_partner_id IN NUMBER ,
388 p_vad_partner_id IN NUMBER ,
389 p_mode IN VARCHAR2 ,
390 p_login_user IN NUMBER ,
391 p_partner_qualifiers_tbl IN partner_qualifiers_tbl_type,
392 x_prtnr_access_id_tbl OUT NOCOPY prtnr_aces_tbl_type
393 );
394
395 -- Start of Comments
396 --
397 -- API name : Create_VAD_Channel_Team
398 -- Type : Public
399 -- Function : The purpose of this procedure is to create a Channel
400 -- team of all VAD employees for a given VAD_Partner_id in
401 -- the PV_PARTNER_ACCESSES table.
402 --
403 -- Pre-reqs : Existing resource should be a "Channel Manager" or
404 -- "Channel Rep" role at group level.
405 --
406 -- Paramaeters :
407 -- IN :
408 -- p_api_version_number IN NUMBER,
409 -- p_init_msg_list IN VARCHAR2
410 -- p_commit IN VARCHAR2
411 -- p_validation_level IN NUMBER
412 --
413 -- p_partner_id IN NUMBER
414 -- p_vad_partner_id IN NUMBER
415 -- OUT :
416 -- x_return_status OUT VARCHAR2(1)
417 -- x_msg_count OUT NUMBER
418 -- x_msg_data OUT VARCHAR2(2000)
419 --
420 -- x_prtnr_access_id_tbl OUT JTF_NUMBER_TABLE
421 --
422 -- Version :
423 -- Initial version 1.0
424 --
425 -- Notes: API for creating a Channel Team of VAD employees for a
426 -- Partner Organization.
427 --
428 --
429 -- End of Comments
430
431 PROCEDURE Create_VAD_Channel_Team
432 ( p_api_version_number IN NUMBER ,
433 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
434 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
435 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
436 x_return_status OUT NOCOPY VARCHAR2,
437 x_msg_count OUT NOCOPY NUMBER,
438 x_msg_data OUT NOCOPY VARCHAR2,
439 p_partner_id IN NUMBER,
440 p_vad_partner_id IN NUMBER,
441 x_prtnr_access_id_tbl OUT NOCOPY prtnr_aces_tbl_type
442 );
443
444 -- Start of Comments
445 --
446 -- API name : Update_Channel_Team
447 -- Type : Public
448 -- Function : The purpose of this procedure is to Update a Channel
449 -- team of a given partner_id, whenever there is an update
450 -- in any of the partner qualifiers.
451 --
452 -- Pre-reqs :
453 --
454 -- Paramaeters :
455 -- IN :
456 -- p_api_version_number IN NUMBER,
457 -- p_init_msg_list IN VARCHAR2
458 -- p_commit IN VARCHAR2
459 -- p_validation_level IN NUMBER
460 --
461 -- p_partner_id IN NUMBER
462 -- p_vad_partner_id IN NUMBER
463 -- p_mode IN VARCHAR2
464 -- p_prtnr_qualifier_rec IN partner_qualifiers_rec_type
465 -- OUT :
466 -- x_return_status OUT VARCHAR2(1)
467 -- x_msg_count OUT NUMBER
468 -- x_msg_data OUT VARCHAR2(2000)
469 -- x_prtnr_access_id_tbl OUT JTF_NUMBER_TABLE
470 --
471 -- Version :
472 -- Initial version 1.0
473 --
474 -- Notes: API for creating a Channel Team for a Partner Organization.
475 --
476 --
477 -- End of Comments
478
479 PROCEDURE Update_Channel_Team
480 ( p_api_version_number IN NUMBER ,
481 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
482 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
483 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
484 p_partner_id IN NUMBER ,
485 p_vad_partner_id IN NUMBER ,
486 p_mode IN VARCHAR2 := 'UPDATE',
487 p_login_user IN NUMBER ,
488 p_upd_prtnr_qflr_flg_rec IN prtnr_qflr_flg_rec_type,
489 x_return_status OUT NOCOPY VARCHAR2,
490 x_msg_count OUT NOCOPY NUMBER,
491 x_msg_data OUT NOCOPY VARCHAR2,
492 x_prtnr_access_id_tbl OUT NOCOPY prtnr_aces_tbl_type
493 );
497 -- API name : Assign_Channel_Team
494
495 -- Start of Comments
496 --
498 -- Type : Public
499 -- Function : The purpose of this procedure is to Update the Channel
500 -- team for all partner_id by running in TOTAL_MODE. This procedure attached to
501 -- 'Territory assignment for partners in TOTAL mode' concurrent request program.
502 -- It reads all the partner_id from PV_PARTNER_PROFILES table and re-assign the
503 -- channel team.
504 --
505 -- Pre-reqs :
506 --
507 -- Paramaeters :
508 -- IN :
509 --
510 -- OUT :
511 -- ERRBUF OUT NOCOPY VARCHAR2,
512 -- RETCODE OUT NOCOPY VARCHAR2
513 --
514 -- Version :
515 -- Initial version 1.0
516 --
517 -- Notes: Concurrent request program for re-assignment of Channel Team
518 -- for all the Partner Organizations stored in PV_PARTNER_PROFILES
519 -- table.
520 --
521 --
522 -- End of Comments
523
524 PROCEDURE Assign_Channel_Team(
525 ERRBUF OUT NOCOPY VARCHAR2,
526 RETCODE OUT NOCOPY VARCHAR2,
527 p_mode IN VARCHAR2,
528 p_first_partner_id IN NUMBER,
529 p_last_partner_id IN NUMBER
530 ) ;
531
532 --
533 -- API name : Process_Sub_Territories
534 -- Type : Public
535 -- Function : The purpose of this procedure is to Update the Channel team for
536 -- all those Partner's, who get affected by the change in territory
537 -- definition. This procedure attached to 'Re-define Channel team for
538 -- specific territories' concurrent request program. It reads all the
539 -- partner_id from PV_PARTNER_ACCESSES table and re-assign the channel
540 -- team.
541 --
542 -- Pre-reqs :
543 --
544 -- Paramaeters :
545 -- IN :
546 --
547 -- OUT :
548 -- ERRBUF OUT NOCOPY VARCHAR2,
549 -- RETCODE OUT NOCOPY VARCHAR2
550 --
551 -- Version :
552 -- Initial version 1.0
553 --
554 -- Notes: Concurrent request program for re-assignment of Channel Team
555 -- for all the Partner Organizations stored in PV_PARTNER_PROFILES
556 -- table.
557 --
558 --
559 -- End of Comments
560
561 PROCEDURE Process_Sub_Territories(
562 ERRBUF OUT NOCOPY VARCHAR2,
563 RETCODE OUT NOCOPY VARCHAR2,
564 p_terr_id1 IN NUMBER,
565 p_terr_id2 IN NUMBER,
566 p_terr_id3 IN NUMBER,
567 p_terr_id4 IN NUMBER,
568 p_terr_id5 IN NUMBER,
569 p_terr_id6 IN NUMBER,
570 p_terr_id7 IN NUMBER,
571 p_terr_id8 IN NUMBER,
572 p_terr_id9 IN NUMBER,
573 p_terr_id10 IN NUMBER,
574 p_terr_id11 IN NUMBER,
575 p_terr_id12 IN NUMBER,
576 p_terr_id13 IN NUMBER,
577 p_terr_id14 IN NUMBER,
578 p_terr_id15 IN NUMBER,
579 p_terr_id16 IN NUMBER,
580 p_terr_id17 IN NUMBER,
581 p_terr_id18 IN NUMBER,
582 p_terr_id19 IN NUMBER,
583 p_terr_id20 IN NUMBER ) ;
584
585
586 END PV_TERR_ASSIGN_PUB;
587
[Home] [Help]