[Home] [Help]
PACKAGE BODY: APPS.CN_WKSHT_GET_PUB
Source
1 PACKAGE BODY CN_WKSHT_GET_PUB as
2 -- $Header: cnpwkgtb.pls 120.3 2006/02/13 15:21:31 fmburu noship $
3
4 G_CREDIT_TYPE_ID CONSTANT NUMBER := -1000;
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Cn_wksht_get_pub';
6
7 --============================================================================
8 -- Procedure : Get_srp_wksht
9 -- Description: To get salespeople assigned to payrun
10 --============================================================================
11 PROCEDURE Get_srp_wksht
12 (p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2,
14 p_commit IN VARCHAR2,
15 p_validation_level IN NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 p_start_record IN NUMBER,
20 p_increment_count IN NUMBER,
21 p_payrun_id IN NUMBER,
22 p_salesrep_name IN VARCHAR2,
23 p_employee_number IN VARCHAR2,
24 p_analyst_name IN VARCHAR2,
25 p_my_analyst IN VARCHAR2,
26 p_unassigned IN VARCHAR2,
27 p_worksheet_status IN VARCHAR2,
28 p_currency_code IN VARCHAR2,
29 p_order_by IN VARCHAR2,
30 x_wksht_tbl OUT NOCOPY wksht_tbl,
31 x_tot_amount_earnings OUT NOCOPY NUMBER,
32 x_tot_amount_adj OUT NOCOPY NUMBER,
33 x_tot_amount_adj_rec OUT NOCOPY NUMBER,
34 x_tot_amount_total OUT NOCOPY NUMBER,
35 x_tot_held_amount OUT NOCOPY NUMBER,
36 x_tot_ced OUT NOCOPY NUMBER,
37 x_tot_earn_diff OUT NOCOPY NUMBER,
38 x_total_records OUT NOCOPY NUMBER
39 ) IS
40
41 l_bb_prior_period_adj NUMBER;
42 l_bb_pmt_recovery_plans NUMBER;
43 l_curr_earnings NUMBER;
44 l_bb_total NUMBER;
45
46 l_api_name CONSTANT VARCHAR2(30) := 'Get_srp_wksht';
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_counter NUMBER;
49
50 TYPE wkshtcurtype IS ref CURSOR;
51 wksht_cur wkshtcurtype;
52
53 l_select VARCHAR2(9000);
54 -- worksheet earnings = pmt_amount_calc + pmt_amount_recovery
55 l_select1 VARCHAR2(9000) :=
56 ' Select /*+ first_rows */ w.payment_worksheet_id,
57 s.name salesrep_name,
58 s.employee_number employee_number,
59 s.salesrep_id,
60 s.resource_id,
61 s.cost_center cost_center,
62 s.charge_to_cost_center,
63 0 pmt_amount_diff,
64 nvl(w.pmt_amount_calc,0) + nvl(w.pmt_amount_recovery,0) pmt_amount_earnings,
65 nvl(w.pmt_amount_adj,0) pmt_amount_adj ,
66 nvl(w.pmt_amount_adj_rec,0) + nvl(w.pmt_amount_adj_nrec,0) Pmt_amount_adj_rec ,
67 nvl(w.pmt_amount_recovery,0) pmt_amount_recovery ,
68 nvl(w.pmt_amount_calc,0) + nvl(w.pmt_amount_adj,0) +
69 nvl(w.pmt_amount_adj_rec,0) + nvl(w.pmt_amount_adj_nrec,0)
70 + nvl(w.pmt_amount_recovery,0) Pmt_amount_total,
71 nvl(w.held_amount,0) held_amount,
72 lk.meaning status_meaning,
73 u.user_name status_by,
74 s.assigned_to_user_name analyst_name,
75 w.worksheet_status,
76 w.object_version_number,
77 p.pay_date,
78 p.org_id
79 from cn_payment_worksheets w,
80 cn_salesreps s,
81 cn_payruns p,
82 cn_lookups lk,
83 fnd_user u
84 where s.salesrep_id = w.salesrep_id
85 and w.org_id = s.org_id
86 and w.worksheet_status = lk.lookup_code
87 and w.payrun_id = p.payrun_id
88 and lk.lookup_type = ''WORKSHEET_STATUS'' and w.quota_id is NULL
89 and u.user_id (+) = nvl(w.last_updated_by, w.created_by)
90 and w.payrun_id = :B1 ';
91
92 l_where VARCHAR2(5000) := null;
93 l_where1 VARCHAR2(5000) := ' upper(s.name) LIKE :B2 ';
94 l_where2 VARCHAR2(5000) := ' upper(s.employee_number) LIKE :B3 ';
95 l_where3 varchar2(5000) := ' worksheet_status LIKE :B4 ';
96 l_where4 VARCHAR2(5000) := ' 1 = 1 ';
97 l_where5 VARCHAR2(5000) := ' s.assigned_to_user_name LIKE :B5 ';
98 l_where7 VARCHAR2(5000) := ' s.assigned_to_user_id IN (
99 SELECT
100 DISTINCT re2.user_id
101 FROM jtf_rs_group_usages u2,
102 jtf_rs_rep_managers m2,
103 jtf_rs_resource_extns_vl re2,
104 (SELECT DISTINCT m1.resource_id,
105 greatest(pr.start_date,m1.start_date_active) start_date,
106 least(pr.end_date,Nvl(m1.end_date_active,pr.end_date)) end_date
107 FROM jtf_rs_resource_extns re1,
108 cn_period_statuses pr, jtf_rs_group_usages u1,
109 jtf_rs_rep_managers m1
110 WHERE re1.user_id = :B7
111 AND pr.period_id
112 = ( select pay_period_id from cn_payruns where payrun_id = :B8)
113 AND u1.usage = ''COMP_PAYMENT''
114 AND ((m1.start_date_active <= pr.end_date) AND
115 (pr.start_date <= Nvl(m1.end_date_active,pr.start_date)))
116 AND u1.group_id = m1.group_id
117 AND m1.resource_id = re1.resource_id
118 AND m1.parent_resource_id = m1.resource_id
119 AND m1.hierarchy_type IN (''MGR_TO_MGR'',''REP_TO_REP'')
120 AND m1.category <> ''TBH''
121 ) v3
122 WHERE
123 u2.usage = ''COMP_PAYMENT''
124 AND u2.group_id = m2.group_id
125 AND m2.parent_resource_id = v3.resource_id
126 AND ((m2.start_date_active <= v3.end_date)
127 AND (v3.start_date <= Nvl(m2.end_date_active,v3.start_date)))
128 AND m2.category <> ''TBH''
129 AND m2.hierarchy_type IN (''MGR_TO_MGR'',''MGR_TO_REP'',''REP_TO_REP'')
130 AND m2.resource_id = re2.resource_id ) ' ;
131
132
133 l_where9 Varchar2(5000) := ') or ( s.assigned_to_user_id IS NULL ) ';
134 l_where11 Varchar2(5000) := ') ';
135 l_wksht_rec wksht_rec;
136 l_payment_worksheet_id cn_payment_worksheets.payment_worksheet_id%TYPE;
137 l_salesrep_name cn_salesreps.name%TYPE;
138 l_employee_number cn_salesreps.employee_number%TYPE;
139 l_cost_center cn_salesreps.cost_center%TYPE;
140 l_charge_to_cost_center cn_salesreps.charge_to_cost_center%TYPE;
141 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
142 l_resource_id cn_salesreps.resource_id%TYPE;
143 l_status cn_payruns.status%TYPE;
144 l_worksheet_status cn_lookups.meaning%TYPE;
145 l_user_name fnd_user.user_name%TYPE;
146 l_pay_date DATE;
147 l_diff1 NUMBER := 0;
148 l_diff2 NUMBER := 0;
149 l_current_earnings NUMBER;
150 l_earnings_diff NUMBER;
151 l_pmt_amount_calc NUMBER;
152 l_pmt_amount_adj NUMBER;
153 l_pmt_amount_adj_rec NUMBER;
154 l_pmt_amount_recovery NUMBER;
155 l_pmt_amount_total NUMBER;
156 l_held_amount NUMBER;
157
158 l_analyst_name cn_salesreps.assigned_to_user_name%TYPE;
159 l_object_version_number NUMBER;
160 l_worksheet_status_code cn_lookups.lookup_code%TYPE;
161 c_salesrep_name cn_salesreps.name%TYPE;
162 c_employee_number cn_salesreps.employee_number%TYPE;
163 c_worksheet_status cn_payment_worksheets.worksheet_status%TYPE;
164 c_analyst_id NUMBER;
165 c_analyst_name cn_salesreps.assigned_to_user_name%TYPE;
166 l_analyst_flag VARCHAR2(01) := 'N';
167 l_view_ced VARCHAR2(1);
168 l_view_notes VARCHAR2(1);
169 l_b6 Varchar2(100);
170 l_b7 Varchar2(100);
171 l_b8 Varchar2(100);
172 l_b9 Varchar2(100);
173 l_tmp NUMBER;
174 l_org_id NUMBER ;
175
176 CURSOR get_payrun_curs IS
177 SELECT status
178 FROM cn_payruns WHERE payrun_id = p_payrun_id;
179
180 -- cursor to check if the worksheet is pre-1158 release.
181 CURSOR view_ced_cur (l_payment_worksheet_id cn_worksheet_qg_dtls.payment_worksheet_id%TYPE) IS
182 SELECT 'Y' FROM dual WHERE exists
183 (SELECT 1 FROM cn_worksheet_qg_dtls
184 WHERE payment_worksheet_id = l_payment_worksheet_id);
185
186 -- cursor to check if thenote exist
187 CURSOR view_notes_cur (l_payment_worksheet_id cn_worksheet_qg_dtls.payment_worksheet_id%TYPE) IS
188 SELECT 'Y' FROM dual WHERE exists
189 (SELECT 1
190 FROM JTF_NOTES_B WHERE SOURCE_OBJECT_CODE = 'CN_PAYMENT_WORKSHEETS'
191 AND SOURCE_OBJECT_ID = l_payment_worksheet_id
192 );
193
194 FUNCTION Convert_Amount(l_from_amount NUMBER, l_date Date, c_org_id NUMBER)
195 RETURN NUMBER IS
196 l_to_amount NUMBER;
197 BEGIN
198 l_to_amount :=
199 GL_CURRENCY_API.Convert_Amount
200 (x_from_currency => CN_GLOBAL_VAR.GET_CURRENCY_CODE(c_org_id),
201 x_to_currency => p_currency_code,
202 x_conversion_date => l_date,
203 x_conversion_type => nvl(CN_SYSTEM_PARAMETERS.value('CN_CONVERSION_TYPE',c_org_id), 'Corporate'),
204 x_amount => l_from_amount
205 );
206 RETURN l_to_amount;
207 EXCEPTION
208 WHEN OTHERS THEN
209 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
210 THEN
211 FND_MESSAGE.SET_NAME ('CN' , 'CN1158_CURR_CONV_ERR');
212 FND_MSG_PUB.Add;
213 END IF;
214 RAISE FND_API.G_EXC_ERROR ;
215 END convert_Amount;
216
217 BEGIN
218
219 -- Standard Start of API savepoint
220 SAVEPOINT Get_srp_wksht;
221 -- Standard call to check for call compatibility.
222 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
223 l_api_name, G_PKG_NAME) THEN
224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
225 END IF;
226 -- Initialize message list if p_init_msg_list is set to TRUE.
227 IF FND_API.to_Boolean( p_init_msg_list ) THEN
228 FND_MSG_PUB.initialize;
229 END IF;
230
231 -- Initialize API return status to success
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233 -- API body
234 l_counter := 0;
235 x_total_records := 0;
236
237 -- Check if this payrun is new payrun created after 11.5.8 upgrade
238 BEGIN
239 l_tmp := 0 ;
240 SELECT 1 INTO l_tmp
241 FROM cn_payruns pay
242 WHERE pay.payrun_id = p_payrun_id
243 AND (pay.status <> 'PAID' OR
244 (pay.status = 'PAID'
245 AND exists
246 (SELECT 1
247 FROM cn_worksheet_qg_dtls dtls, cn_payment_worksheets wrk
248 WHERE dtls.payment_worksheet_id = wrk.payment_worksheet_id
249 AND wrk.payrun_id = pay.payrun_id
250 AND wrk.salesrep_id = dtls.salesrep_id)
251 ));
252 EXCEPTION
253 WHEN no_data_found THEN
254 -- this payrun is old payrun from before 11.5.8, no need to check
255 -- from payment analyst hierarchy
256 l_tmp := 0 ;
257 END;
258
259 IF l_tmp = 1 THEN
260 -- Check if login user in payment analyst hierarchy for this
261 -- pay period
262 BEGIN
263 SELECT 1 INTO l_tmp FROM dual WHERE exists
264 (SELECT 1
265 FROM jtf_rs_resource_extns re1,
266 cn_period_statuses pr,
267 jtf_rs_group_usages u1,
268 jtf_rs_rep_managers m1
269 WHERE re1.user_id = fnd_global.user_id
270 AND (pr.period_id, pr.org_id) = (
271 SELECT pay_period_id, org_id
272 FROM cn_payruns
273 WHERE payrun_id = p_payrun_id)
274 AND u1.usage = 'COMP_PAYMENT'
275 AND ((m1.start_date_active <= pr.end_date) AND
276 (pr.start_date <= Nvl(m1.end_date_active,pr.start_date)))
277 AND u1.group_id = m1.group_id
278 AND m1.resource_id = re1.resource_id
279 AND m1.parent_resource_id = m1.resource_id
280 AND m1.hierarchy_type IN ('MGR_TO_MGR','REP_TO_REP')
281 AND m1.category <> 'TBH');
282 EXCEPTION
283 WHEN no_data_found THEN
284 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
285 FND_MESSAGE.SET_NAME ('CN','CN_NO_SRP_ACCESS');
286 FND_MSG_PUB.Add;
287 END IF;
288 RAISE FND_API.G_EXC_ERROR ;
289 END;
290 END IF;
291
292 c_salesrep_name := upper(p_salesrep_name);
293 c_employee_number := upper(p_employee_number);
294 c_worksheet_status := p_worksheet_status;
295 -- c_analyst_id := p_analyst_id;
296 c_analyst_name := p_analyst_name;
297
298 l_select := l_select1 ;
299 -- Add salesrep Name is passed
300 IF p_salesrep_name IS NOT NULL AND p_salesrep_name <> '%' THEN
301 l_select := l_select || ' and ' || l_where1 ;
302 ELSE
303 l_select := l_select || ' and ' || ' 1 = :B2 ';
304 c_salesrep_name := 1;
305 END IF;
306
307 -- Add Employee Number is passed
308 IF p_employee_number IS NOT NULL AND p_employee_number <> '%' THEN
309 l_select := l_select || ' and ' || l_where2 ;
310 ELSE
311 l_select := l_select || ' and 1 = :B3 ';
312 c_employee_number := 1;
313 END IF;
314
315 -- Add worksheet status
316 IF p_worksheet_status IS NOT NULL AND p_worksheet_status <> '%'
317 AND p_worksheet_status <> 'ALL' THEN
318 l_select := l_select || ' and ' || l_where3 ;
319 ELSE
320 l_select := l_select || ' and 1 = :B4 ';
321 c_worksheet_status := 1;
322 END IF;
323
324 l_select := l_select || ' and (( ';
325
326 IF p_analyst_name IS NOT NULL AND p_analyst_name <> '%' THEN
327 l_where := l_where || ' ( ' || l_where5 || ') ';
328 l_analyst_flag := 'Y';
329 ELSE
330 l_where := l_where || ' ( 1 = :B5 ) ';
331 c_analyst_name := 1;
332 END IF;
333
334 IF p_my_analyst = 'Y' THEN
335 l_where := l_where || ' and ( ' || l_where7 || ') ';
336 l_b7 := fnd_global.user_id;
337 l_b8 := p_payrun_id;
338 ELSE
339 l_where := l_where || ' and ( 1 = :B7 ) and 1 = :B8 ';
340 l_b7 := 1;
341 l_b8 := 1;
342 END IF;
343
344 -- Bug 3597600: p_my_analyst can be NULL
345 -- Old code: p_my_analyst = 'N' THEN
346 IF (p_analyst_name IS NULL OR p_analyst_name = '%') AND
347 (p_my_analyst = 'N' OR p_my_analyst IS NULL) THEN
348 l_b9 := fnd_global.user_id;
349 l_where := l_where || ' and assigned_to_user_id = :B11 ' ;
350 ELSE
351 l_where := l_where || ' and 1 = :B11 ' ;
352 l_b9 := 1;
353 END IF;
354
355 l_select := l_select || l_where ;
356 IF p_unassigned = 'Y' THEN
357 l_select := l_select || l_where9 ;
358 ELSE
359 l_select := l_select || l_where11 ;
360 END IF;
361
362 l_select := l_select || ' ) ' || ' ' || p_order_by ;
363
364 --
365 -- Debugging
366 --
367 -- CREATE TABLE my_temp
368 -- (select_clause1 varchar2(2000),
369 -- select_clause2 varchar2(2000),
370 -- select_clause3 varchar2(2000))
371 /*
372 INSERT INTO my_temp (select_clause1,select_clause2,select_clause3)
373 SELECT substrb(l_select,1,2000),substrb(l_select,2001,2000),
374 p_payrun_id || '*' || c_salesrep_name ||'*'|| c_employee_number ||'*'||
375 c_worksheet_status ||'*'|| c_analyst_name ||'*'|| l_b7 ||'*'|| l_b8
376 ||'*'|| l_b9
377 FROM dual;
378 */
379
380 OPEN wksht_cur FOR l_select using p_payrun_id,
381 c_salesrep_name, c_employee_number, c_worksheet_status,
382 c_analyst_name, l_b7, l_b8, l_b9, l_org_id;
383
384 LOOP
385 FETCH wksht_cur INTO
386 l_payment_worksheet_id
387 ,l_salesrep_name
388 ,l_employee_number
389 ,l_salesrep_id
390 ,l_resource_id
391 ,l_cost_center
392 ,l_charge_to_cost_center
393 ,l_earnings_diff
394 ,l_pmt_amount_calc
395 ,l_pmt_amount_adj
396 ,l_pmt_amount_adj_rec
397 ,l_pmt_amount_recovery
398 ,l_pmt_amount_total
399 ,l_held_amount
400 ,l_worksheet_status
401 ,l_user_name
402 ,l_analyst_name
403 ,l_worksheet_status_code
404 ,l_object_version_number
405 ,l_pay_date;
406
407 EXIT WHEN wksht_cur%notfound;
408 x_total_records := x_total_records + 1;
409
410 IF (l_counter + 1 BETWEEN p_start_record AND (p_start_record + p_increment_count - 1)) THEN
411 -- Get current earnings due
412 cn_payment_worksheet_pvt.get_ced_and_bb
413 ( p_api_version => 1.0,
414 x_return_status => x_return_status,
415 x_msg_count => x_msg_count,
416 x_msg_data => x_msg_data,
417 p_worksheet_id => l_payment_worksheet_id,
418 x_bb_prior_period_adj => l_bb_prior_period_adj,
419 x_bb_pmt_recovery_plans => l_bb_pmt_recovery_plans,
420 x_curr_earnings => l_curr_earnings,
421 x_curr_earnings_due => l_current_earnings,
422 x_bb_total => l_bb_total);
423
424 IF x_return_status <> FND_API.g_ret_sts_success THEN
425 RAISE FND_API.G_EXC_ERROR;
426 END IF;
427
428 l_view_ced := 'N';
429
430 OPEN view_ced_cur (l_payment_worksheet_id);
431 FETCH view_ced_cur INTO l_view_ced;
432 CLOSE view_ced_cur;
433
434 l_view_notes := 'N';
435
436 OPEN view_notes_cur (l_payment_worksheet_id);
437 FETCH view_notes_cur INTO l_view_notes;
438 CLOSE view_notes_cur;
439
440 x_wksht_tbl(l_counter).payment_worksheet_id := l_payment_worksheet_id;
441 x_wksht_tbl(l_counter).view_ced := l_view_ced;
442 x_wksht_tbl(l_counter).view_notes := l_view_notes;
443 x_wksht_tbl(l_counter).salesrep_name := l_salesrep_name;
444 x_wksht_tbl(l_counter).employee_number := l_employee_number;
445 x_wksht_tbl(l_counter).salesrep_id := l_salesrep_id;
446 x_wksht_tbl(l_counter).resource_id := l_resource_id;
447 x_wksht_tbl(l_counter).worksheet_status := l_worksheet_status;
448 x_wksht_tbl(l_counter).status_by := l_user_name;
449 x_wksht_tbl(l_counter).cost_center := l_cost_center;
450 x_wksht_tbl(l_counter).charge_to_cost_center:= l_charge_to_cost_center;
451
452 -- Calc earning difference
453 l_earnings_diff := nvl(l_current_earnings,0) - nvl(l_pmt_amount_calc,0) - Nvl(l_held_amount,0);
454
455 -- user currency
456 IF nvl(p_currency_code,'FUNC_CURR') <> 'FUNC_CURR' THEN
457 x_wksht_tbl(l_counter).current_earnings := convert_amount(l_current_earnings, l_pay_date, l_org_id);
458 x_wksht_tbl(l_counter).pmt_amount_diff := convert_amount(l_earnings_diff, l_pay_date, l_org_id);
459 x_wksht_tbl(l_counter).pmt_amount_earnings:= convert_amount(l_pmt_amount_calc, l_pay_date, l_org_id);
460 x_wksht_tbl(l_counter).pmt_amount_adj := convert_amount(l_pmt_amount_adj, l_pay_date, l_org_id);
461 x_wksht_tbl(l_counter).pmt_amount_adj_rec := convert_amount(l_pmt_amount_adj_rec, l_pay_date, l_org_id);
462 x_wksht_tbl(l_counter).pmt_amount_total := convert_amount(l_pmt_amount_total,l_pay_date, l_org_id);
463 x_wksht_tbl(l_counter).held_amount := convert_amount(l_held_amount,l_pay_date,l_org_id);
464 ELSE
465 -- Functional Currency
466 x_wksht_tbl(l_counter).current_earnings := l_current_earnings;
467 x_wksht_tbl(l_counter).pmt_amount_diff := l_earnings_diff ;
468 x_wksht_tbl(l_counter).pmt_amount_earnings := l_pmt_amount_calc ;
469 x_wksht_tbl(l_counter).pmt_amount_adj := l_pmt_amount_adj;
470 x_wksht_tbl(l_counter).pmt_amount_adj_rec := l_pmt_amount_adj_rec ;
471 x_wksht_tbl(l_counter).pmt_amount_total := l_pmt_amount_total;
472 x_wksht_tbl(l_counter).held_amount := l_held_amount;
473 END IF;
474
475 x_wksht_tbl(l_counter).worksheet_status_code := l_worksheet_status_code;
476 x_wksht_tbl(l_counter).Analyst_name := l_analyst_name;
477 x_wksht_tbl(l_counter).object_version_number := l_object_version_number;
478
479 -- Cumulative total
480 x_tot_amount_earnings := nvl(x_tot_amount_earnings,0) + l_pmt_amount_calc;
481 x_tot_amount_adj := nvl(x_tot_amount_adj,0) + l_pmt_amount_adj;
482 x_tot_amount_adj_rec := nvl(x_tot_amount_adj_rec,0) + l_pmt_amount_adj_rec;
483 x_tot_amount_total := nvl(x_tot_amount_total,0) + l_pmt_amount_total;
484 x_tot_held_amount := nvl(x_tot_held_amount,0) + l_held_amount;
485 x_tot_ced := Nvl(x_tot_ced,0) + l_current_earnings;
486 x_tot_earn_diff := Nvl(x_tot_earn_diff,0) + l_earnings_diff;
487
488 END IF;
489 l_counter := l_counter + 1;
490 END LOOP;
491
492 -- Convert the total into user currency
493 IF nvl(p_currency_code,'FUNC_CURR') <> 'FUNC_CURR' THEN
494 x_tot_amount_earnings := convert_amount(x_tot_amount_earnings, l_pay_date, l_org_id);
495 x_tot_amount_adj := convert_amount(x_tot_amount_adj, l_pay_date, l_org_id);
496 x_tot_amount_adj_rec := convert_amount(x_tot_amount_adj_rec, l_pay_date, l_org_id);
497 x_tot_amount_total := convert_amount(x_tot_amount_total, l_pay_date, l_org_id);
498 x_tot_held_amount := convert_amount(x_tot_held_amount, l_pay_date, l_org_id);
499 x_tot_ced := convert_amount(x_tot_ced, l_pay_date, l_org_id);
500 x_tot_earn_diff := convert_amount(x_tot_earn_diff, l_pay_date, l_org_id);
501
502 END IF;
503 CLOSE wksht_cur;
504
505 -- End of API body.
506 -- Standard check of p_commit.
507 IF FND_API.To_Boolean( p_commit ) THEN
508 COMMIT WORK;
509 END IF;
510
511 --
512 -- Standard call to get message count and if count is 1, get message info.
513 --
514 FND_MSG_PUB.Count_And_Get
515 (p_count => x_msg_count,
516 p_data => x_msg_data,
517 p_encoded => FND_API.G_FALSE);
518 EXCEPTION
519 WHEN FND_API.G_EXC_ERROR THEN
520 ROLLBACK TO Get_srp_wksht;
521 x_return_status := FND_API.G_RET_STS_ERROR ;
522 FND_MSG_PUB.Count_And_Get
523 (p_count => x_msg_count,
524 p_data => x_msg_data,
525 p_encoded => FND_API.G_FALSE);
526 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
527 ROLLBACK TO Get_srp_wksht;
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
529 FND_MSG_PUB.Count_And_Get
530 (p_count => x_msg_count,
531 p_data => x_msg_data,
532 p_encoded => FND_API.G_FALSE);
533 WHEN OTHERS THEN
534 ROLLBACK TO Get_srp_wksht;
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
536 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
537 THEN
538 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
539 END IF;
540 FND_MSG_PUB.Count_And_Get
541 (p_count => x_msg_count,
542 p_data => x_msg_data,
543 p_encoded => FND_API.G_FALSE);
544 END Get_srp_wksht;
545
546 END Cn_wksht_get_pub ;