[Home] [Help]
PACKAGE BODY: APPS.CN_PAY_GROUP_DTLS_PVT
Source
1 PACKAGE BODY CN_PAY_GROUP_DTLS_PVT AS
2 /*$Header: cnvpgdtb.pls 115.7 2003/09/15 12:01:06 bpradhan ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_PAY_GROUP_DTLS_PVT';
5
6
7
8 PROCEDURE Get_Pay_Group_Dtls
9 (p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
11 p_commit IN VARCHAR2 := FND_API.G_FALSE,
12 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
13 p_start_record IN NUMBER := -1,
14 p_fetch_size IN NUMBER := -1,
15 p_pay_group_id IN NUMBER,
16 x_pay_group_dtls OUT NOCOPY pay_group_dtls_tbl_type,
17 x_total_record OUT NOCOPY NUMBER,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2
21 ) IS
22 l_api_name CONSTANT VARCHAR2(30) := 'Get_Pay_Group_Dtls';
23 l_api_version CONSTANT NUMBER := 1.0;
24
25 l_counter NUMBER;
26
27 CURSOR l_pay_group_dtls_cr IS
28 select cpg.pay_group_id, cpg.name, cs.period_set_name, cpg.period_type, cps.period_name, cps.period_year, cps.quarter_num, cps.start_date, cps.end_date
29 from cn_pay_groups cpg, cn_period_statuses cps, cn_period_sets cs
30 where (cpg.period_set_id = cps.period_set_id) and
31 (cpg.period_type_id = cps.period_type_id) and
32 (cpg.start_date <= cps.start_date) and
33 ( nvl(cpg.end_date, cps.end_date) >= cps.end_date) and
34 (cpg.period_set_id = cs.period_set_id) and
35 (cpg.pay_group_id = p_pay_group_id) order by cps.period_id;
36
37
38 BEGIN
39 -- Standard Start of API savepoint
40 SAVEPOINT Get_Pay_Group_Dtls;
41 -- Standard call to check for call compatibility.
42 IF NOT FND_API.Compatible_API_Call
43 (l_api_version ,
44 p_api_version ,
45 l_api_name ,
46 G_PKG_NAME )
47 THEN
48 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
49 END IF;
50 -- Initialize message list if p_init_msg_list is set to TRUE.
51 IF FND_API.to_Boolean( p_init_msg_list ) THEN
52 FND_MSG_PUB.initialize;
53 END IF;
54 -- Initialize API return status to success
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56 -- API body
57
58 -- x_pay_group_dtls := G_MISS_PAY_GROUP_DTLS_REC_TB ;
59
60 l_counter := 0;
61 x_total_record := 0;
62
63 FOR l_pay_group_dtls IN l_pay_group_dtls_cr LOOP
64
65 x_total_record := x_total_record +1;
66 IF (p_fetch_size = -1) OR (x_total_record >= p_start_record
67 AND x_total_record <= (p_start_record + p_fetch_size - 1)) THEN
68 -- assign values of the row to x_srp_list
69
70
71
72
73
74
75 x_pay_group_dtls(l_counter).pay_group_id := l_pay_group_dtls.pay_group_id;
76
77
78 x_pay_group_dtls(l_counter).name := l_pay_group_dtls.name;
79
80 x_pay_group_dtls(l_counter).period_set_name := l_pay_group_dtls.period_set_name;
81
82 x_pay_group_dtls(l_counter).period_type := l_pay_group_dtls.period_type;
83
84 x_pay_group_dtls(l_counter).period_name := l_pay_group_dtls.period_name;
85
86 x_pay_group_dtls(l_counter).period_year := l_pay_group_dtls.period_year;
87
88 x_pay_group_dtls(l_counter).quarter_num := l_pay_group_dtls.quarter_num;
89
90 x_pay_group_dtls(l_counter).start_date := l_pay_group_dtls.start_date;
91
92 x_pay_group_dtls(l_counter).end_date := l_pay_group_dtls.end_date;
93
94 l_counter := l_counter + 1;
95
96
97 END IF;
98 END LOOP;
99
100 -- End of API body.
101 -- Standard check of p_commit.
102 IF FND_API.To_Boolean( p_commit ) THEN
103 COMMIT WORK;
104 END IF;
105 -- Standard call to get message count and if count is 1, get message info.
106 FND_MSG_PUB.Count_And_Get
107 (p_count => x_msg_count ,
108 p_data => x_msg_data ,
109 p_encoded => FND_API.G_FALSE );
110 EXCEPTION
111 WHEN FND_API.G_EXC_ERROR THEN
112 ROLLBACK TO Get_Pay_Group_Dtls;
113 x_return_status := FND_API.G_RET_STS_ERROR ;
114 FND_MSG_PUB.Count_And_Get
115 (p_count => x_msg_count ,
116 p_data => x_msg_data ,
117 p_encoded => FND_API.G_FALSE );
118 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
119 ROLLBACK TO Get_Pay_Group_Dtls;
120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
121 FND_MSG_PUB.Count_And_Get
122 (p_count => x_msg_count ,
123 p_data => x_msg_data ,
124 p_encoded => FND_API.G_FALSE );
125 WHEN OTHERS THEN
126 ROLLBACK TO Get_Pay_Group_Dtls;
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
128 IF FND_MSG_PUB.Check_Msg_Level
129 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
130 THEN
131 FND_MSG_PUB.Add_Exc_Msg
132 (G_PKG_NAME ,
133 l_api_name );
134 END IF;
135 FND_MSG_PUB.Count_And_Get
136 (p_count => x_msg_count ,
137 p_data => x_msg_data ,
138 p_encoded => FND_API.G_FALSE );
139 END Get_Pay_Group_Dtls;
140
141
142 PROCEDURE Get_Pay_Group_Sales
143 (p_api_version IN NUMBER,
144 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
145 p_commit IN VARCHAR2 := FND_API.G_FALSE,
146 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
147 p_start_record IN NUMBER := -1,
148 p_fetch_size IN NUMBER := -1,
149 p_pay_group_id IN NUMBER,
150 x_pay_group_sales OUT NOCOPY pay_group_sales_tbl_type,
151 x_total_record OUT NOCOPY NUMBER,
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2
155 ) IS
156 l_api_name CONSTANT VARCHAR2(30) := 'Get_Pay_Group_Sales';
157 l_api_version CONSTANT NUMBER := 1.0;
158
159 l_counter NUMBER;
160
161 CURSOR l_pay_group_sales_cr IS
162 select cpg.pay_group_id a,
163 cpg.name b,
164 cs.period_set_name c,
165 cpg.period_type d,
166 csr.name e,
167 csr.employee_number f,
168 cspg.start_date g,
169 nvl(cspg.end_date, cpg.end_date) h
170 from cn_pay_groups cpg,
171 cn_salesreps csr,
172 cn_srp_pay_groups cspg,
173 cn_period_sets cs
174 where (cpg.pay_group_id = cspg.pay_group_id) and
175 (csr.salesrep_id = cspg.salesrep_id) and
176 (cpg.period_set_id = cs.period_set_id) and
177 (cpg.pay_group_id = p_pay_group_id);
178
179
180 BEGIN
181 -- Standard Start of API savepoint
182 SAVEPOINT Get_Pay_Group_Sales;
183 -- Standard call to check for call compatibility.
184 IF NOT FND_API.Compatible_API_Call
185 (l_api_version ,
186 p_api_version ,
187 l_api_name ,
188 G_PKG_NAME )
189 THEN
190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191 END IF;
192 -- Initialize message list if p_init_msg_list is set to TRUE.
193 IF FND_API.to_Boolean( p_init_msg_list ) THEN
194 FND_MSG_PUB.initialize;
195 END IF;
196 -- Initialize API return status to success
197 x_return_status := FND_API.G_RET_STS_SUCCESS;
198 -- API body
199
200 -- x_pay_group_sales := G_MISS_PAY_GROUP_DTLS_REC_TB ;
201
202 l_counter := 0;
203 x_total_record := 0;
204
205 FOR l_pay_group_sales IN l_pay_group_sales_cr LOOP
206
207 x_total_record := x_total_record +1;
208 IF (p_fetch_size = -1) OR (x_total_record >= p_start_record
209 AND x_total_record <= (p_start_record + p_fetch_size - 1)) THEN
210 -- assign values of the row to x_srp_list
211
212
213
214
215
216
217 x_pay_group_sales(l_counter).pay_group_id := l_pay_group_sales.a;
218
219
220 x_pay_group_sales(l_counter).name := l_pay_group_sales.b;
221
222 x_pay_group_sales(l_counter).period_set_name := l_pay_group_sales.c;
223
224 x_pay_group_sales(l_counter).period_type := l_pay_group_sales.d;
225
226 x_pay_group_sales(l_counter).salesrep_name := l_pay_group_sales.e;
227 x_pay_group_sales(l_counter).employee_number := l_pay_group_sales.f;
228
229 x_pay_group_sales(l_counter).start_date := l_pay_group_sales.g;
230
231 x_pay_group_sales(l_counter).end_date := l_pay_group_sales.h;
232
233 l_counter := l_counter + 1;
234
235
236 END IF;
237 END LOOP;
238
239 -- End of API body.
240 -- Standard check of p_commit.
241 IF FND_API.To_Boolean( p_commit ) THEN
242 COMMIT WORK;
243 END IF;
244 -- Standard call to get message count and if count is 1, get message info.
245 FND_MSG_PUB.Count_And_Get
246 (p_count => x_msg_count ,
247 p_data => x_msg_data ,
248 p_encoded => FND_API.G_FALSE );
249 EXCEPTION
250 WHEN FND_API.G_EXC_ERROR THEN
251 ROLLBACK TO Get_Pay_Group_Sales;
252 x_return_status := FND_API.G_RET_STS_ERROR ;
253 FND_MSG_PUB.Count_And_Get
254 (p_count => x_msg_count ,
255 p_data => x_msg_data ,
256 p_encoded => FND_API.G_FALSE );
257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
258 ROLLBACK TO Get_Pay_Group_Sales;
259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
260 FND_MSG_PUB.Count_And_Get
261 (p_count => x_msg_count ,
262 p_data => x_msg_data ,
263 p_encoded => FND_API.G_FALSE );
264 WHEN OTHERS THEN
265 ROLLBACK TO Get_Pay_Group_Sales;
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
267 IF FND_MSG_PUB.Check_Msg_Level
268 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
269 THEN
270 FND_MSG_PUB.Add_Exc_Msg
271 (G_PKG_NAME ,
272 l_api_name );
273 END IF;
274 FND_MSG_PUB.Count_And_Get
275 (p_count => x_msg_count ,
276 p_data => x_msg_data ,
277 p_encoded => FND_API.G_FALSE );
278 END Get_Pay_Group_Sales;
279
280
281
282 PROCEDURE Get_Pay_Group_Roles
283 (p_api_version IN NUMBER,
284 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
285 p_commit IN VARCHAR2 := FND_API.G_FALSE,
286 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
287 p_start_record IN NUMBER := -1,
288 p_fetch_size IN NUMBER := -1,
289 p_pay_group_id IN NUMBER,
290 x_pay_group_roles OUT NOCOPY pay_group_roles_tbl_type,
291 x_total_record OUT NOCOPY NUMBER,
292 x_return_status OUT NOCOPY VARCHAR2,
293 x_msg_count OUT NOCOPY NUMBER,
294 x_msg_data OUT NOCOPY VARCHAR2
295 ) IS
296 l_api_name CONSTANT VARCHAR2(30) := 'Get_Pay_Group_Roles';
297 l_api_version CONSTANT NUMBER := 1.0;
298
299 l_counter NUMBER;
300
301 -- removed the NVL condition for the end date for the bug fix 3138828 and 3138774
302
303 CURSOR l_pay_group_roles_cr IS
304 select cpg.pay_group_id a,
305 cpg.name b,
306 cs.period_set_name c,
307 cpg.period_type d,
308 csr.name e,
309 csr.role_id f,
310 cspg.start_date g,
311 cspg.end_date h
312 from cn_pay_groups cpg,
313 cn_roles csr,
314 cn_role_pay_groups cspg,
315 cn_period_sets cs
316 where (cpg.pay_group_id = cspg.pay_group_id) and
317 (csr.role_id = cspg.role_id) and
318 (cpg.pay_group_id = p_pay_group_id);
319
320
321
322
323
324
325 BEGIN
326 -- Standard Start of API savepoint
327 SAVEPOINT Get_Pay_Group_Roles;
328 -- Standard call to check for call compatibility.
329 IF NOT FND_API.Compatible_API_Call
330 (l_api_version ,
331 p_api_version ,
332 l_api_name ,
333 G_PKG_NAME )
334 THEN
335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
336 END IF;
337 -- Initialize message list if p_init_msg_list is set to TRUE.
338 IF FND_API.to_Boolean( p_init_msg_list ) THEN
339 FND_MSG_PUB.initialize;
340 END IF;
341 -- Initialize API return status to success
342 x_return_status := FND_API.G_RET_STS_SUCCESS;
343 -- API body
344
345 -- x_pay_group_sales := G_MISS_PAY_GROUP_DTLS_REC_TB ;
346
347 l_counter := 0;
348 x_total_record := 0;
349
350 FOR l_pay_group_roles IN l_pay_group_roles_cr LOOP
351
352 x_total_record := x_total_record +1;
353
354
355
356
357
358
359
360 x_pay_group_roles(l_counter).pay_group_id := l_pay_group_roles.a;
361
362
363 x_pay_group_roles(l_counter).name := l_pay_group_roles.b;
364
365 x_pay_group_roles(l_counter).period_set_name := l_pay_group_roles.c;
366
367 x_pay_group_roles(l_counter).period_type := l_pay_group_roles.d;
368
369 x_pay_group_roles(l_counter).role_name := l_pay_group_roles.e;
370 x_pay_group_roles(l_counter).role_id := l_pay_group_roles.f;
371
372 x_pay_group_roles(l_counter).start_date := l_pay_group_roles.g;
373
374 x_pay_group_roles(l_counter).end_date := l_pay_group_roles.h;
375
376 l_counter := l_counter + 1;
377
378
379
380
381 END LOOP;
382
383 --IF l_counter = 0 THEN
384
385 -- SELECT name INTO x_pay_group_roles(l_counter).name FROM CN_PAY_GROUPS WHERE PAY_GROUP_ID= p_pay_group_id;
386
387 -- END IF;
388
389 -- End of API body.
390 -- Standard check of p_commit.
391 IF FND_API.To_Boolean( p_commit ) THEN
392 COMMIT WORK;
393 END IF;
394 -- Standard call to get message count and if count is 1, get message info.
395 FND_MSG_PUB.Count_And_Get
396 (p_count => x_msg_count ,
397 p_data => x_msg_data ,
398 p_encoded => FND_API.G_FALSE );
399 EXCEPTION
400 WHEN FND_API.G_EXC_ERROR THEN
401 ROLLBACK TO Get_Pay_Group_Roles;
402 x_return_status := FND_API.G_RET_STS_ERROR ;
403 FND_MSG_PUB.Count_And_Get
404 (p_count => x_msg_count ,
405 p_data => x_msg_data ,
406 p_encoded => FND_API.G_FALSE );
407 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
408 ROLLBACK TO Get_Pay_Group_Roles;
409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
410 FND_MSG_PUB.Count_And_Get
411 (p_count => x_msg_count ,
412 p_data => x_msg_data ,
413 p_encoded => FND_API.G_FALSE );
414 WHEN OTHERS THEN
415 ROLLBACK TO Get_Pay_Group_Roles;
416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
417 IF FND_MSG_PUB.Check_Msg_Level
418 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
419 THEN
420 FND_MSG_PUB.Add_Exc_Msg
421 (G_PKG_NAME ,
422 l_api_name );
423 END IF;
424 FND_MSG_PUB.Count_And_Get
425 (p_count => x_msg_count ,
426 p_data => x_msg_data ,
427 p_encoded => FND_API.G_FALSE );
428 END Get_Pay_Group_Roles;
429
430
431
432
433
434 END CN_PAY_GROUP_DTLS_PVT;