1 PACKAGE cn_srp_hier_proc_pvt AUTHID CURRENT_USER AS
2 /*$Header: cnvsrhrs.pls 115.9 2002/11/21 21:19:14 hlchen ship $*/
3
4 type srp_tbl_type IS table OF number
5 INDEX BY binary_integer;
6
7 type srp_role_group_rec_type IS RECORD
8 (salesrep_id number,
9 role_id number,
10 group_id number,
11 mgr_srp_id number,
12 start_date date,
13 end_date date,
14 hier_level number);
15
16 type srp_role_group_tbl_type IS table OF srp_role_group_rec_type INDEX BY binary_integer;
17
18
19 type srp_role_info_rec_type IS RECORD
20 (
21 srp_role_id NUMBER,
22 srp_id number,
23 overlay_flag varchar2(1),
24 non_std_flag varchar2(1),
25 role_id NUMBER,
26 role_name VARCHAR2(60),
27 job_title_id NUMBER,
28 job_discretion VARCHAR2(80),
29 status VARCHAR2(30),
30 plan_activate_status VARCHAR2(30),
31 club_eligible_flag VARCHAR2(1),
32 org_code VARCHAR2(30),
33 start_date date,
34 end_date date,
35 group_id number
36 );
37
38 type srp_role_info_tbl_type IS table OF srp_role_info_rec_type INDEX BY binary_integer;
39
40
41
42 type srp_group_rec_type IS record
43 (salesrep_id number,
44 group_id number,
45 effective_date date);
46
47 type input_group_type IS record
48 (group_id number(15),
49 effective_date date);
50
51 type group_rec_type IS record
52 (group_id number(15),
53 start_date date,
54 end_date date,
55 hier_level number);
56
57 type group_tbl_type IS table OF group_rec_type INDEX BY binary_integer;
58
59 type group_mbr_rec_type IS record
60 (
61 group_id number,
62 salesrep_id number,
63 mgr_srp_id number,
64 hier_level number
65 );
66
67 type group_mbr_tbl_type IS table OF group_mbr_rec_type INDEX BY binary_integer;
68
69
70 -- Start of comments
71 -- API name : Get_Managers
72 -- Type : Private.
73 -- Function :
74 -- Pre-reqs : None.
75 -- Parameters :
76 -- IN : p_api_version IN NUMBER Required
77 -- p_init_msg_list IN VARCHAR2 Optional
78 -- Default = FND_API.G_FALSE
79 -- p_commit IN VARCHAR2 Optional
80 -- Default = FND_API.G_FALSE
81 -- p_validation_level IN NUMBER Optional
82 -- Default = FND_API.G_VALID_LEVEL_FULL
83 -- p_salesrep_id IN NUMBER Required
84 -- p_comp_group_id IN NUMBER Required
85 -- p_effective_date IN DATE Required
86 -- OUT : x_return_status OUT VARCHAR2(1)
87 -- x_msg_count OUT NUMBER
88 -- x_msg_data OUT VARCHAR2(2000)
89 -- x_salesrep_tbl OUT srp_tbl_type
90 -- x_returned_rows OUT INTEGER
91 -- Version : Current version 1.0
92 -- Initial version 1.0
93 --
94 -- Notes : Note text
95 --
96 -- End of comments
97 PROCEDURE Get_Managers
98 (p_api_version IN NUMBER ,
99 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
100 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
101 p_validation_level IN NUMBER :=
102 FND_API.G_VALID_LEVEL_FULL ,
103 p_salesrep_id IN number ,
104 p_comp_group_id IN number ,
105 p_effective_date IN date ,
106 x_return_status OUT NOCOPY VARCHAR2 ,
107 x_msg_count OUT NOCOPY NUMBER ,
108 x_msg_data OUT NOCOPY VARCHAR2 ,
109 x_salesrep_tbl OUT NOCOPY srp_role_group_tbl_type ,
110 x_returned_rows OUT NOCOPY integer );
111
112 -- Start of comments
113 -- API name : Get_Salesreps
114 -- Type : Private.
115 -- Function :
116 -- Pre-reqs : None.
117 -- Parameters :
118 -- IN : p_api_version IN NUMBER Required
119 -- p_init_msg_list IN VARCHAR2 Optional
120 -- Default = FND_API.G_FALSE
121 -- p_commit IN VARCHAR2 Optional
122 -- Default = FND_API.G_FALSE
123 -- p_validation_level IN NUMBER Optional
124 -- Default = FND_API.G_VALID_LEVEL_FULL
125 -- p_salesrep_id IN NUMBER Required
126 -- p_comp_group_id IN NUMBER Required
127 -- p_effective_date IN DATE Required
128 -- OUT : x_return_status OUT VARCHAR2(1)
129 -- x_msg_count OUT NUMBER
130 -- x_msg_data OUT VARCHAR2(2000)
131 -- x_salesrep_tbl OUT srp_tbl_type
132 -- x_returned_rows OUT INTEGER
133 -- Version : Current version 1.0
134 -- Initial version 1.0
135 --
136 -- Notes : Note text
137 --
138 -- End of comments
139 PROCEDURE Get_Salesreps
140 (p_api_version IN NUMBER ,
141 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
142 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
143 p_validation_level IN NUMBER :=
144 FND_API.G_VALID_LEVEL_FULL ,
145 p_salesrep_id IN number ,
146 p_comp_group_id IN number ,
147 p_effective_date IN date ,
148 p_return_current IN varchar2 := 'N' ,
149 x_return_status OUT NOCOPY VARCHAR2 ,
150 x_msg_count OUT NOCOPY NUMBER ,
151 x_msg_data OUT NOCOPY VARCHAR2 ,
152 x_salesrep_tbl OUT NOCOPY srp_role_group_tbl_type ,
153 x_returned_rows OUT NOCOPY integer );
154
155 -- Start of comments
156 -- API name : Get_Ancestor_Salesreps
157 -- Type : Private.
158 -- Function :
159 -- Pre-reqs : None.
160 -- Parameters :
161 -- IN : p_api_version IN NUMBER Required
162 -- p_init_msg_list IN VARCHAR2 Optional
163 -- Default = FND_API.G_FALSE
164 -- p_commit IN VARCHAR2 Optional
165 -- Default = FND_API.G_FALSE
166 -- p_validation_level IN NUMBER Optional
167 -- Default = FND_API.G_VALID_LEVEL_FULL
168 -- p_srp IN srp_group_rec_type Required
169 -- p_effective_date IN DATE Required
170 -- OUT : x_return_status OUT VARCHAR2(1)
171 -- x_msg_count OUT NUMBER
172 -- x_msg_data OUT VARCHAR2(2000)
173 -- x_srp OUT srp_role_group_tbl_type
174 -- x_returned_rows OUT INTEGER
175 -- Version : Current version 1.0
176 -- Initial version 1.0
177 --
178 -- Notes : Note text
179 --
180 -- End of comments
181 PROCEDURE Get_Ancestor_Salesreps
182 (p_api_version IN NUMBER,
183 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
184 p_commit IN VARCHAR2 := FND_API.G_FALSE,
185 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
186 p_srp IN srp_group_rec_type,
187 x_return_status OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER,
189 x_msg_data OUT NOCOPY VARCHAR2,
190 x_srp OUT NOCOPY srp_role_group_tbl_type,
191 x_returned_rows OUT NOCOPY number);
192
193 -- Start of comments
194 -- API name : Get_Descendant_Salesreps
195 -- Type : Private.
196 -- Function :
197 -- Pre-reqs : None.
198 -- Parameters :
199 -- IN : p_api_version IN NUMBER Required
200 -- p_init_msg_list IN VARCHAR2 Optional
201 -- Default = FND_API.G_FALSE
202 -- p_commit IN VARCHAR2 Optional
203 -- Default = FND_API.G_FALSE
204 -- p_validation_level IN NUMBER Optional
205 -- Default = FND_API.G_VALID_LEVEL_FULL
206 -- p_srp IN srp_group_rec_type Required
207 -- p_effective_date IN DATE Required
208 -- OUT : x_return_status OUT VARCHAR2(1)
209 -- x_msg_count OUT NUMBER
210 -- x_msg_data OUT VARCHAR2(2000)
211 -- x_srp OUT srp_role_group_tbl_type
212 -- x_returned_rows OUT INTEGER
213 -- Version : Current version 1.0
214 -- Initial version 1.0
215 --
216 -- Notes : Note text
217 --
218 -- End of comments
219 PROCEDURE Get_Descendant_Salesreps
220 (p_api_version IN NUMBER,
221 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
222 p_commit IN VARCHAR2 := FND_API.G_FALSE,
223 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
224 p_srp IN srp_group_rec_type,
225 p_return_current IN varchar2 := 'Y',
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2,
229 x_srp OUT NOCOPY srp_role_group_tbl_type,
230 x_returned_rows OUT NOCOPY number);
231
232
233 -- Start of comments
234 -- API name : Get_desc_role_info
235 -- Type : Private.
236 -- Function :
237 -- Pre-reqs : None.
238 -- Parameters :
239 -- IN : p_api_version IN NUMBER Required
240 -- p_init_msg_list IN VARCHAR2 Optional
241 -- Default = FND_API.G_FALSE
242 -- p_commit IN VARCHAR2 Optional
243 -- Default = FND_API.G_FALSE
244 -- p_validation_level IN NUMBER Optional
245 -- Default = FND_API.G_VALID_LEVEL_FULL
246 -- p_srp IN srp_group_rec_type Required
247 -- p_effective_date IN DATE Required
248 -- OUT : x_return_status OUT VARCHAR2(1)
249 -- x_msg_count OUT NUMBER
250 -- x_msg_data OUT VARCHAR2(2000)
251 -- x_srp OUT srp_role_info_tbl_type
252 -- x_returned_rows OUT INTEGER
253 -- Version : Current version 1.0
254 -- Initial version 1.0
255 --
256 -- Notes : Note text
257 --
258 -- End of comments
259 PROCEDURE Get_desc_role_info
260 (p_api_version IN NUMBER,
261 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
262 p_commit IN VARCHAR2 := FND_API.G_FALSE,
263 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
264 p_srp IN srp_group_rec_type,
265 p_return_current IN varchar2 := 'Y',
266 x_return_status OUT NOCOPY VARCHAR2,
267 x_msg_count OUT NOCOPY NUMBER,
268 x_msg_data OUT NOCOPY VARCHAR2,
269 x_srp OUT NOCOPY srp_role_info_tbl_type,
270 x_returned_rows OUT NOCOPY number);
271
272 -- ***********************************
273 -- TBD : MO
274 -- ***********************************
275 PROCEDURE Get_MO_desc_role_info
276 (p_api_version IN NUMBER,
277 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
278 p_commit IN VARCHAR2 := FND_API.G_FALSE,
279 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
280 p_srp IN srp_group_rec_type,
281 p_return_current IN varchar2 := 'Y',
282 p_is_multiorg IN VARCHAR2 := 'N',
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_msg_count OUT NOCOPY NUMBER,
285 x_msg_data OUT NOCOPY VARCHAR2,
286 x_srp OUT NOCOPY srp_role_info_tbl_type,
287 x_returned_rows OUT NOCOPY number);
288
289 -- Start of comments
290 -- API name : Get_desc_role_info
291 -- Type : Private.
292 -- Function :
293 -- Pre-reqs : None.
294 -- Parameters :
295 -- IN : p_api_version IN NUMBER Required
296 -- p_init_msg_list IN VARCHAR2 Optional
297 -- Default = FND_API.G_FALSE
298 -- p_commit IN VARCHAR2 Optional
299 -- Default = FND_API.G_FALSE
300 -- p_validation_level IN NUMBER Optional
301 -- Default = FND_API.G_VALID_LEVEL_FULL
302 -- p_srp IN srp_group_rec_type Required
303 -- p_effective_date IN DATE Required
304 -- OUT : x_return_status OUT VARCHAR2(1)
305 -- x_msg_count OUT NUMBER
306 -- x_msg_data OUT VARCHAR2(2000)
307 -- x_srp OUT group_mbr_tbl_type
308 -- x_returned_rows OUT INTEGER
309 -- Version : Current version 1.0
310 -- Initial version 1.0
311 --
312 -- Notes : Note text
313 --
314 -- End of comments
315 PROCEDURE Get_descendant_group_mbrs
316 (p_api_version IN NUMBER,
317 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
318 p_commit IN VARCHAR2 := FND_API.G_FALSE,
319 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
320 p_srp IN srp_group_rec_type,
321 p_return_current IN varchar2 := 'Y',
322 p_level IN number := 0,
323 p_first_level_only IN varchar2 := 'N',
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2,
327 x_srp IN OUT NOCOPY group_mbr_tbl_type,
328 x_returned_rows OUT NOCOPY number);
329
330 -- ***********************************
331 -- TBD : MO
332 -- ***********************************
333
334 PROCEDURE Get_MO_descendant_group_mbrs
335 (p_api_version IN NUMBER,
336 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
340 p_return_current IN varchar2 := 'Y',
337 p_commit IN VARCHAR2 := FND_API.G_FALSE,
338 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
339 p_srp IN srp_group_rec_type,
341 p_level IN number := 0,
342 p_first_level_only IN varchar2 := 'N',
343 p_is_multiorg IN VARCHAR2 := 'N',
344 x_return_status OUT NOCOPY VARCHAR2,
345 x_msg_count OUT NOCOPY NUMBER,
346 x_msg_data OUT NOCOPY VARCHAR2,
347 x_srp IN OUT NOCOPY group_mbr_tbl_type,
348 x_returned_rows OUT NOCOPY number);
349
350 -- API name : get_ancestor_group
351 -- Type : Private.
352 -- Pre-reqs : None
353 -- Usage :
354 --
355 -- Desc :
356 --
357 --
358 --
359 -- Parameters :
360 -- IN : p_api_version IN NUMBER Required
361 -- p_init_msg_list IN VARCHAR2 Optional
362 -- Default = FND_API.G_FALSE
363 -- p_commit IN VARCHAR2 Optional
364 -- Default = FND_API.G_FALSE
365 -- p_validation_level IN NUMBER Optional
366 -- Default = FND_API.G_VALID_LEVEL_FULL
367 -- p_group IN input_group_type Required
368 -- OUT : x_return_status OUT VARCHAR2(1)
369 -- x_msg_count OUT NUMBER
370 -- x_msg_data OUT VARCHAR2(2000)
371 -- x_group OUT group_tbl_type
372 -- Version : Current version 1.0
373 -- Initial version 1.0
374 --
375 -- Notes :
376 --
377 -- End of comments
378
379
380
381 PROCEDURE get_ancestor_group
382 (p_api_version IN NUMBER,
383 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
384 p_commit IN VARCHAR2 := FND_API.G_FALSE,
385 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
386 x_return_status OUT NOCOPY VARCHAR2,
387 x_msg_count OUT NOCOPY NUMBER,
388 x_msg_data OUT NOCOPY VARCHAR2,
389 p_group IN input_group_type,
390 x_group IN OUT NOCOPY group_tbl_type,
391 p_level IN number := 0);
392
393 -- API name : get_descendant_group
394 -- Type : Private.
395 -- Pre-reqs : None
396 -- Usage :
397 --
398 -- Desc :
399 --
400 --
401 --
402 -- Parameters :
403 -- IN : p_api_version IN NUMBER Required
404 -- p_init_msg_list IN VARCHAR2 Optional
405 -- Default = FND_API.G_FALSE
406 -- p_commit IN VARCHAR2 Optional
407 -- Default = FND_API.G_FALSE
408 -- p_validation_level IN NUMBER Optional
409 -- Default = FND_API.G_VALID_LEVEL_FULL
410 -- p_group IN input_group_type Required
411 -- OUT : x_return_status OUT VARCHAR2(1)
412 -- x_msg_count OUT NUMBER
413 -- x_msg_data OUT VARCHAR2(2000)
414 -- x_group OUT group_tbl_type
415 -- Version : Current version 1.0
416 -- Initial version 1.0
417 --
418 -- Notes :
419 --
420 -- End of comments
421
422 PROCEDURE get_descendant_group
423 (p_api_version IN NUMBER,
424 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
425 p_commit IN VARCHAR2 := FND_API.G_FALSE,
426 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
427 x_return_status OUT NOCOPY VARCHAR2,
428 x_msg_count OUT NOCOPY NUMBER,
429 x_msg_data OUT NOCOPY VARCHAR2,
430 p_group IN input_group_type,
431 x_group IN OUT NOCOPY group_tbl_type,
432 p_level IN number);
433
434 -- Start of comments
435 -- API name : Get_All_Managers
436 -- Type : Private.
437 -- Function :
438 -- Pre-reqs : None.
439 -- Parameters :
440 -- IN : p_api_version IN NUMBER Required
441 -- p_init_msg_list IN VARCHAR2 Optional
442 -- Default = FND_API.G_FALSE
443 -- p_commit IN VARCHAR2 Optional
444 -- Default = FND_API.G_FALSE
445 -- p_validation_level IN NUMBER Optional
446 -- Default = FND_API.G_VALID_LEVEL_FULL
447 -- p_srp IN srp_group_rec_type Required
448 -- p_effective_date IN DATE Required
449 -- OUT : x_return_status OUT VARCHAR2(1)
450 -- x_msg_count OUT NUMBER
451 -- x_msg_data OUT VARCHAR2(2000)
452 -- x_srp OUT srp_role_group_tbl_type
453 -- x_returned_rows OUT INTEGER
454 -- Version : Current version 1.0
455 -- Initial version 1.0
456 --
457 -- Notes : Note text
458 --
459 -- End of comments
460 PROCEDURE Get_All_Managers
464 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
461 (p_api_version IN NUMBER,
462 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
463 p_commit IN VARCHAR2 := FND_API.G_FALSE,
465 p_srp IN srp_group_rec_type,
466 x_return_status OUT NOCOPY VARCHAR2,
467 x_msg_count OUT NOCOPY NUMBER,
468 x_msg_data OUT NOCOPY VARCHAR2,
469 x_srp OUT NOCOPY srp_role_group_tbl_type,
470 x_returned_rows OUT NOCOPY number);
471
472 END cn_srp_hier_proc_pvt;
[Home] [Help]