[Home] [Help]
PACKAGE BODY: APPS.CN_TSR_PVT
Source
1 PACKAGE BODY CN_TSR_PVT AS
2 /* $Header: cnvtsrb.pls 115.16 2002/11/21 21:19:57 hlchen ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_TSR_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvtsrb.pls';
6 G_LAST_UPDATE_DATE DATE := sysdate;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
8 G_CREATION_DATE DATE := sysdate;
9 G_CREATED_BY NUMBER := fnd_global.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
11
12
13 -- Start of comments
14 -- API name : Get_Tsr_Data
15 -- Type : Private.
16 -- Pre-reqs : None.
17 -- Usage :
18 --
19 -- Desc :
20 --
21 --
22 --
23 -- Parameters :
24 -- IN : p_api_version NUMBER Require
25 -- p_init_msg_list VARCHAR2 Optional
26 -- Default = FND_API.G_FALSE
27 -- p_commit VARCHAR2 Optional
28 -- Default = FND_API.G_FALSE
29 -- p_validation_level NUMBER Optional
30 -- Default = FND_API.G_VALID_LEVEL_FULL
31 -- OUT : x_return_status VARCHAR2(1)
32 -- x_msg_count NUMBER
33 -- x_msg_data VARCHAR2(2000)
34 -- IN : p_mgr_id NUMBER
35 -- p_comp_group_id NUMBER
36 -- p_org_code VARCHAR2
37 -- p_period_id DATE
38 -- p_start_row NUMBER
39 -- p_rows NUMBER
40 -- OUT : x_tsr_data tsr_tbl_type
41 -- x_total_rows NUMBER
42 -- Version : Current version 1.0
43 -- Initial version 1.0
44 --
45 -- Notes : Note text
46 --
47 -- End of comments
48
49
50 PROCEDURE Get_Tsr_Data
51 (
52 p_api_version IN NUMBER,
53 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
54 p_commit IN VARCHAR2 := FND_API.G_FALSE,
55 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
56 x_return_status OUT NOCOPY VARCHAR2,
57 x_msg_count OUT NOCOPY NUMBER,
58 x_msg_data OUT NOCOPY VARCHAR2,
59 p_mgr_id IN NUMBER,
60 p_comp_group_id IN NUMBER,
61 p_org_code IN VARCHAR2,
62 p_period_id IN DATE,
63 p_start_row IN NUMBER,
64 p_rows IN NUMBER,
65 x_tsr_data OUT NOCOPY tsr_tbl_type,
66 x_total_rows OUT NOCOPY NUMBER,
67 download IN VARCHAR2 := 'N'
68 ) IS
69
70 l_api_name CONSTANT VARCHAR2(30) := 'Get_Tsr_Data';
71 l_api_version CONSTANT NUMBER := 1.0;
72 l_ctr NUMBER;
73 l_inner_ctr NUMBER;
74 /* Nikhil: Completely restructed the cursor */
75 CURSOR l_tsr_cr
76 (P_SRP_ID NUMBER, P_GROUP_ID NUMBER) IS
77 SELECT
78 hr1.emp_num tsr_emp_no,
79 hr1.name tsr_name,
80 hr1.srp_id tsr_srp_id
81 FROM
82 cn_srp_hr_data hr1
83 WHERE
84 NOT EXISTS (
85 SELECT 1
86 from jtf_rs_groups_vl jg,
87 jtf_rs_role_relations jrr,
88 jtf_rs_salesreps jrs,
89 jtf_rs_roles_b jr,
90 jtf_rs_group_mbr_role_vl jgm,
91 jtf_rs_group_usages u
92 WHERE jg.group_id = jgm.group_id
93 and (jgm.manager_flag = 'Y' or jgm.member_flag = 'Y')
94 and jrs.resource_id = jgm.resource_id
95 and u.group_id = jgm.group_id
96 and u.usage = 'SF_PLANNING'
97 and jrr.role_resource_type = 'RS_INDIVIDUAL'
98 and jrr.role_resource_id = jrs.resource_id
99 and jrr.role_id = jgm.role_id and jrr.role_id = jr.role_id
100 and jr.role_type_code = 'SALES_COMP'
101 and TRUNC(p_period_id) between trunc(jrr.start_date_active)
102 and NVL(TRUNC(jrr.end_date_active), TRUNC(p_period_id))
103 and jrr.delete_flag <> 'Y' and
104 jrr.start_date_active <= jgm.start_date_active
105 and (jrr.end_date_active is null
106 or jrr.end_date_active >= jgm.end_date_active) AND jrs.SALESREP_ID > 0
107 AND NVL(jrs.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
108 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
109 = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
110 ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
111 AND jrs.salesrep_id = P_SRP_ID
112 AND jg.group_id = P_GROUP_ID
113 )
114 and hr1.srp_id = P_SRP_ID
115 UNION ALL
116 SELECT
117 hr1.emp_num tsr_emp_no,
118 hr1.name tsr_name,
119 hr1.srp_id tsr_srp_id
120 FROM
121 cn_srp_hr_data hr1
122 WHERE
123 EXISTS (
124 SELECT 1
125 from jtf_rs_groups_vl jg,
126 jtf_rs_role_relations jrr,
127 jtf_rs_salesreps jrs,
128 jtf_rs_roles_b jr,
129 jtf_rs_group_mbr_role_vl jgm,
130 cn_srp_role_dtls srd,
131 jtf_rs_group_usages u
132 WHERE jg.group_id = jgm.group_id
133 and (jgm.manager_flag = 'Y' or jgm.member_flag = 'Y')
134 and jrs.resource_id = jgm.resource_id
135 and u.group_id = jgm.group_id
136 and u.usage = 'SF_PLANNING'
137 and jrr.role_resource_type = 'RS_INDIVIDUAL'
138 and jrr.role_resource_id = jrs.resource_id
139 and jrr.role_id = jgm.role_id and jrr.role_id = jr.role_id
140 and jr.role_type_code = 'SALES_COMP'
141 AND TRUNC(p_period_id) between trunc(jrr.start_date_active)
142 AND NVL(TRUNC(jrr.end_date_active), TRUNC(p_period_id))
143 and jrr.delete_flag <> 'Y' and
144 jrr.start_date_active <= jgm.start_date_active
145 and (jrr.end_date_active is null
146 or jrr.end_date_active >= jgm.end_date_active) AND jrs.SALESREP_ID > 0
147 AND NVL(jrs.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
148 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
149 = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
150 ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
151 AND jrs.salesrep_id = P_SRP_ID
152 AND jg.group_id = P_GROUP_ID
153 AND srd.srp_role_id = jrr.role_relate_id
154 AND srd.role_model_id is null -- "CHANGED FOR MODELING IMPACT"
155 AND srd.job_title_id = -99
156 )
157 /*
158 AND NOT EXISTS --- Check this
159 (
160 SELECT s.salesrep_id
161 from jtf_rs_role_relations rr,
162 jtf_rs_salesreps s,
163 cn_srp_role_dtls srd,
164 jtf_rs_roles_b r
165 WHERE rr.role_resource_id = s.resource_id
166 and rr.role_relate_id = srd.srp_role_id
167 and rr.role_resource_type = 'RS_INDIVIDUAL'
168 and rr.delete_flag = 'N'
169 AND NVL(S.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
170 ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
171 = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
172 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
173 AND s.salesrep_id = P_SRP_ID
174 AND TRUNC(p_period_id) between trunc(rr.start_date_active)
175 AND NVL(TRUNC(rr.end_date_active), TRUNC(p_period_id))
176 AND srd.job_title_id <> -99
177 AND r.role_id = rr.role_id
178 AND r.role_type_code = 'SALES_COMP'
179 )
180 */
181 AND hr1.SRP_ID = P_SRP_ID
182 ORDER BY
183 tsr_name, tsr_emp_no;
184
185 CURSOR l_mgr_cr
186 (P_MGR_SRP_ID NUMBER) IS
187 SELECT
188 emp_num mgr_emp_no,
189 name mgr_name,
190 srp_id tsr_mgr_id
191 FROM
192 cn_srp_hr_data
193 WHERE srp_id = P_MGR_SRP_ID;
194
195
196
197 l_srp_tbl cn_srp_hier_proc_pvt.group_mbr_tbl_type;
198 l_srp_rec cn_srp_hier_proc_pvt.srp_group_rec_type;
199 l_returned_rows number;
200 l_return_status VARCHAR2(1);
201
202
203 BEGIN
204 -- Standard Start of API savepoint
205 SAVEPOINT Get_Tsr_Data_SP;
206 -- Standard call to check for call compatibility.
207 IF NOT FND_API.compatible_api_call
208 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
209 THEN
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212 -- Initialize message list if p_init_msg_list is set to TRUE.
213 IF FND_API.to_Boolean( p_init_msg_list ) THEN
214 FND_MSG_PUB.initialize;
215 END IF;
216 -- Initialize API return status to success
217 x_return_status := FND_API.G_RET_STS_SUCCESS;
218
219
220 -- API body
221 l_ctr := 1;
222 l_inner_ctr := 1;
223 --start by getting all people below this person
224 l_srp_rec.salesrep_id := p_mgr_id;
225 l_srp_rec.group_id := p_comp_group_id;
226 l_srp_rec.effective_date := p_period_id;
227
228
229 cn_srp_hier_proc_pvt.Get_Descendant_group_mbrs
230 (p_api_version => 1.0,
231 p_init_msg_list => FND_API.G_FALSE,
232 p_commit => FND_API.G_FALSE,
233 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
234 p_srp => l_srp_rec,
235 x_return_status => l_return_status,
236 x_msg_count => x_msg_count,
237 x_msg_data => x_msg_data,
238 x_srp => l_srp_tbl,
239 x_returned_rows => l_returned_rows);
240
241 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
242 RAISE FND_API.G_EXC_ERROR;
243 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
245 END IF;
246
247
248 -- dbms_output.put_line(to_char(l_srp_tbl.count));
249
250 IF (l_srp_tbl.count > 0) THEN
251 FOR i in l_srp_tbl.first .. l_srp_tbl.last LOOP
252
253 FOR eachrow in l_tsr_cr(l_srp_tbl(i).salesrep_id, l_srp_tbl(i).group_id) LOOP
254
255 -- dbms_output.put_line(eachrow.tsr_emp_no || ' ' || eachrow.tsr_name);
256 IF ( (l_ctr BETWEEN p_start_row AND (p_start_row + p_rows - 1)) AND (download = 'N')) THEN
257 x_tsr_data(l_inner_ctr).tsr_emp_no := eachrow.tsr_emp_no;
258 x_tsr_data(l_inner_ctr).tsr_name := eachrow.tsr_name;
259 x_tsr_data(l_inner_ctr).tsr_srp_id := eachrow.tsr_srp_id;
260
261 IF l_srp_tbl(i).mgr_srp_id <> 0 THEN
262 FOR mgrrow in l_mgr_cr(l_srp_tbl(i).mgr_srp_id) LOOP
263 x_tsr_data(l_inner_ctr).mgr_emp_no := mgrrow.mgr_emp_no;
264 x_tsr_data(l_inner_ctr).mgr_name := mgrrow.mgr_name;
265 x_tsr_data(l_inner_ctr).tsr_mgr_id := mgrrow.tsr_mgr_id;
266 END LOOP;
267 ELSE
268 x_tsr_data(l_inner_ctr).mgr_emp_no := ' - ';
269 x_tsr_data(l_inner_ctr).mgr_name := ' - ';
270 x_tsr_data(l_inner_ctr).tsr_mgr_id := 0;
271 END IF;
272 l_inner_ctr := l_inner_ctr + 1;
273 END IF;
274
275 IF (download = 'Y') THEN
276 x_tsr_data(l_inner_ctr).tsr_emp_no := eachrow.tsr_emp_no;
277 x_tsr_data(l_inner_ctr).tsr_name := eachrow.tsr_name;
278 IF l_srp_tbl(i).mgr_srp_id <> 0 THEN
279 FOR mgrrow in l_mgr_cr(l_srp_tbl(i).mgr_srp_id) LOOP
280 x_tsr_data(l_inner_ctr).mgr_emp_no := mgrrow.mgr_emp_no;
281 x_tsr_data(l_inner_ctr).mgr_name := mgrrow.mgr_name;
282 END LOOP;
283 ELSE
284 x_tsr_data(l_inner_ctr).mgr_emp_no := ' - ';
285 x_tsr_data(l_inner_ctr).mgr_name := ' - ';
286 END IF;
287 l_inner_ctr := l_inner_ctr + 1;
288 END IF;
289
290 l_ctr := l_ctr + 1;
291 END LOOP;
292
293 END LOOP;
294 END IF;
295
296 -- x_total_rows := l_tsr_cr%ROWCOUNT;
297 x_total_rows := l_ctr;
298
299 -- IF l_tsr_cr%ROWCOUNT = 0 THEN
300 -- x_tsr_data := G_MISS_TSR_TBL ;
301 -- END IF;
302
303 -- CLOSE l_tsr_cr;
304 -- End of API body.
305 << end_api >>
306 NULL;
307 -- Standard check of p_commit.
308 IF FND_API.To_Boolean( p_commit ) THEN
309 COMMIT WORK;
310 END IF;
311 -- Standard call to get message count and if count is 1, get message info.
312 FND_MSG_PUB.Count_And_Get
313 (
314 p_count => x_msg_count ,
315 p_data => x_msg_data ,
316 p_encoded => FND_API.G_FALSE
317 );
318
319 EXCEPTION
320 WHEN FND_API.G_EXC_ERROR THEN
321 ROLLBACK TO Get_Tsr_Data_SP ;
322 x_return_status := FND_API.G_RET_STS_ERROR ;
323 FND_MSG_PUB.Count_And_Get
324 (
325 p_count => x_msg_count ,
326 p_data => x_msg_data ,
327 p_encoded => FND_API.G_FALSE
328 );
329
330 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
331 ROLLBACK TO Get_Tsr_Data_SP ;
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
333 FND_MSG_PUB.Count_And_Get
334 (
335 p_count => x_msg_count ,
336 p_data => x_msg_data ,
337 p_encoded => FND_API.G_FALSE
338 );
339 WHEN OTHERS THEN
340 ROLLBACK TO Get_Tsr_Data_SP ;
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
342 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
343 THEN
344 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
345 END IF;
346 FND_MSG_PUB.Count_And_Get
347 (
348 p_count => x_msg_count ,
349 p_data => x_msg_data ,
350 p_encoded => FND_API.G_FALSE
351 );
352 END Get_Tsr_Data;
353
354
355 END CN_TSR_PVT;
356