[Home] [Help]
PACKAGE BODY: APPS.CN_GET_COMM_PMT_PAID_GRP
Source
1 PACKAGE BODY CN_GET_COMM_PMT_PAID_GRP AS
2 -- $Header: cnggcppb.pls 120.0 2005/08/08 00:14:23 appldev noship $
3 g_api_version CONSTANT NUMBER := 1.0;
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CN_GET_COMM_PMT_PAID_GRP';
5 g_credit_type_id CONSTANT NUMBER := -1000;
6
7 PROCEDURE get_from_currency
8 (
9 p_org_id cn_salesreps.org_id%TYPE,
10 x_from_currency OUT NOCOPY gl_sets_of_books.currency_code%TYPE
11 )
12 IS
13 CURSOR get_source_currency_code(p_org_id cn_salesreps.org_id%TYPE)
14 IS
15 SELECT gsob.currency_code from_currency
16 FROM cn_repositories_all repo,
17 gl_sets_of_books gsob
18 WHERE repo.org_id = p_org_id
19 AND repo.application_id = 283
20 AND repo.set_of_books_id = gsob.set_of_books_id;
21 BEGIN
22 FOR i IN get_source_currency_code(p_org_id)
23 LOOP
24 x_from_currency := i.from_currency;
25 END LOOP;
26 END get_from_currency;
27
28 PROCEDURE get_conversion_rate
29 (
30 p_from_currency gl_sets_of_books.currency_code%TYPE,
31 p_to_currency gl_sets_of_books.currency_code%TYPE,
32 p_conversion_date DATE,
33 p_conversion_type gl_daily_conversion_types.conversion_type%TYPE,
34 x_rate OUT NOCOPY NUMBER
35 )
36 IS
37 l_numerator NUMBER;
38 l_denominator NUMBER;
39 l_rate NUMBER;
40 BEGIN
41 gl_currency_api.get_closest_triangulation_rate (
42 x_from_currency => p_from_currency,
43 x_to_currency => p_to_currency,
44 x_conversion_date => p_conversion_date,
45 x_conversion_type => p_conversion_type,
46 x_max_roll_days => 0,
47 x_denominator => l_denominator,
48 x_numerator => l_numerator,
49 x_rate => l_rate);
50
51 x_rate := l_rate;
52 EXCEPTION
53 WHEN OTHERS
54 THEN
55 x_rate := NULL;
56 END get_conversion_rate;
57
58 PROCEDURE get_prorated_days
59 (
60 p_start_date DATE,
61 p_end_date DATE,
62 x_prorated_days OUT NOCOPY NUMBER
63 )
64 IS
65 CURSOR get_prorated_days(p_start_date DATE, p_end_date DATE)
66 IS
67 SELECT (p_end_date - p_start_date + 1) prorated_days
68 FROM dual;
69 BEGIN
70 FOR i IN get_prorated_days(p_start_date, p_end_date)
71 LOOP
72 x_prorated_days := i.prorated_days;
73 END LOOP;
74 END get_prorated_days;
75
76 PROCEDURE debug_msg(msg IN VARCHAR2) IS
77 BEGIN
78 --dbms_output.put_line(msg);
79 NULL;
80 END;
81
82 -- ===========================================================================
83 -- Procedure : get_comm_and_paid_pmt.
84 -- Description : This public procedure is used to get the commission earned and payment
85 -- paid amount from OIC.
86 -- The following is added for compensation earned as p_proration_flag
87 -- is for compensation earned.
88 -- For example, p_start_date : 15-Jan-04
89 -- p_end_date : 15-Feb-04
90 -- i)In Jan, comp is $1000 and in Feb, comp is $2000.
91 -- If the p_proration_flag : FND_API.G_TRUE ('T')
92 -- Then we need to sum up all amount for the Jan and Feb and then prorate for
93 -- 15-Jan-04 to 15-Feb-04 to (1000+2000)* (17+15)/(31+15)and return new date ranges as:
94 -- x_new_start_date : 01-Jan-04
95 -- x_new_end_date : 28-Feb-04
96 -- ii)If the p_proration_flag : FND_API.G_FALSE ('F')
97 -- Then we need to sum up all amount for the Jan and Feb (1000+ 2000) and return new date ranges as:
98 -- x_new_start_date : 01-Jan-04
99 -- x_new_end_date : 28-Feb-04
100 -- Both x_new_start_date and x_new_end_date will not be NULL if they are the same across the orgs.
101 -- Both x_new_start_date and x_new_end_date will be NULL if they are different across the orgs.
102 -- ===========================================================================
103 PROCEDURE get_comm_and_paid_pmt
104 (
105 p_api_version IN NUMBER,
106 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
107 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
108 p_person_id IN NUMBER,
109 p_start_date IN DATE,
110 p_end_date IN DATE,
111 p_target_currency_code IN VARCHAR2,
112 p_proration_flag IN VARCHAR2,
113 x_return_status OUT NOCOPY VARCHAR2,
114 x_msg_count OUT NOCOPY NUMBER,
115 x_msg_data OUT NOCOPY VARCHAR2,
116 x_comp_earned OUT NOCOPY NUMBER,
117 x_comp_paid OUT NOCOPY NUMBER,
118 x_new_start_date OUT NOCOPY Date,
119 x_new_end_date OUT NOCOPY Date
120 )
121 IS
122 CURSOR get_org_and_srp_id(p_person_id jtf_rs_salesreps.person_id%TYPE)
123 IS
124 SELECT rs.org_id, rs.salesrep_id, hou.name operating_unit
125 FROM jtf_rs_salesreps rs, jtf_rs_resource_extns rre,
126 hr_operating_units hou
127 WHERE rre.source_id = p_person_id
128 AND rre.resource_id = rs.resource_id
129 AND rre.category = 'EMPLOYEE'
130 AND rs.org_id = hou.organization_id;
131
132 CURSOR get_period_ids(p_start_date cn_period_statuses_all.start_date%TYPE,
133 p_end_date cn_period_statuses_all.end_date%TYPE,
134 p_org_id cn_period_statuses_all.org_id%TYPE)
135 IS
136 SELECT min(period_id) start_period_id, max(period_id) end_period_id,
137 min(start_date) new_start_date, max(end_date) new_end_date,
138 (max(end_date) - min(start_date) + 1) total_days
139 FROM cn_period_statuses_all
140 WHERE start_date <= p_end_date
141 AND p_start_date <= end_date
142 AND org_id = p_org_id
143 ORDER BY period_id;
144
145 CURSOR get_comm_and_pmt(p_salesrep_id jtf_rs_salesreps.salesrep_id%TYPE,
146 p_start_period_id cn_period_statuses_all.period_id%TYPE,
147 p_end_period_id cn_period_statuses_all.period_id%TYPE,
148 p_org_id cn_srp_periods_all.org_id%TYPE)
149 IS
150 SELECT NVL(SUM(NVL(balance2_dtd,0)),0) commission,
151 NVL(SUM(NVL(balance1_dtd,0) - NVL(balance1_ctd,0)),0) paid_pmt
152 FROM cn_srp_periods_all
153 WHERE salesrep_id = p_salesrep_id
154 AND period_id BETWEEN p_start_period_id AND p_end_period_id
155 AND credit_type_id = g_credit_type_id
156 AND quota_id IS NULL
157 AND org_id = p_org_id;
158
159 l_comp_earned cn_srp_periods.balance2_dtd%TYPE;
160 l_converted_comp_earned cn_srp_periods.balance2_dtd%TYPE;
161 l_comp_paid cn_srp_periods.balance1_dtd%TYPE;
162 l_converted_comp_paid cn_srp_periods.balance1_dtd%TYPE;
163
164 l_sum_converted_comp_earned cn_srp_periods.balance2_dtd%TYPE := 0;
165 l_sum_converted_comp_paid cn_srp_periods.balance1_dtd%TYPE := 0;
166 l_org_cnt NUMBER := 0;
167 l_new_date_range_exists BOOLEAN := TRUE;
168
169 l_proration_flag VARCHAR2(1);
170 l_prorated_days NUMBER;
171 l_total_days NUMBER;
172
173 l_rate NUMBER; --conversion rate
174 l_factor NUMBER; --proration factor
175
176 l_from_currency gl_sets_of_books.currency_code%TYPE;
177 l_conversion_date DATE;
178 l_conversion_type gl_daily_conversion_types.conversion_type%TYPE;
179
180 l_temp_org_name hr_operating_units.name%TYPE;
181 l_temp_start_date DATE;
182 l_temp_end_date DATE;
183
184 l_new_start_date DATE;
185 l_new_end_date DATE;
186
187 l_api_name CONSTANT VARCHAR2(30) := 'get_comm_and_paid_pmt';
188 l_init_msg_list VARCHAR2(1);
189 l_validation_level NUMBER;
190
191 BEGIN
192 -- Standard call to check for call compatibility.
193 IF NOT FND_API.Compatible_API_Call ( g_api_version ,
194 p_api_version ,
195 l_api_name ,
196 G_PKG_NAME )
197 THEN
201 -- Initialize API return status to success
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199 END IF;
200
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203
204 --Defaults:
205 l_init_msg_list := NVL(p_init_msg_list, FND_API.G_FALSE);
206 l_validation_level := NVL(p_validation_level, FND_API.G_VALID_LEVEL_FULL);
207 l_proration_flag := NVL(p_proration_flag, FND_API.G_FALSE);
208
209 -- Initialize message list if p_init_msg_list is set to TRUE.
210 IF FND_API.to_Boolean( l_init_msg_list )
211 THEN
212 FND_MSG_PUB.initialize;
213 END IF;
214
215 x_comp_earned := NULL;
216 x_comp_paid := NULL;
217 x_new_start_date := NULL;
218 x_new_end_date := NULL;
219
220 l_conversion_date := p_end_date;
221 l_conversion_type := 'Corporate';
222
223 get_prorated_days
224 (
225 p_start_date => p_start_date,
226 p_end_date => p_end_date,
227 x_prorated_days => l_prorated_days
228 );
229
230 FOR rec IN get_org_and_srp_id(p_person_id)
231 LOOP
232 l_org_cnt := l_org_cnt + 1;
233 debug_msg('org_id = ' || to_char(rec.org_id) );
234
235 get_from_currency
236 (
237 p_org_id => rec.org_id,
238 x_from_currency => l_from_currency
239 );
240
241 IF (l_from_currency <> p_target_currency_code)
242 THEN
243 get_conversion_rate
244 (
245 p_from_currency => l_from_currency,
246 p_to_currency => p_target_currency_code,
247 p_conversion_date => l_conversion_date,
248 p_conversion_type => l_conversion_type,
249 x_rate => l_rate
250 );
251
252 --Need to raise exception if conversion rate is not available from gl.
253 IF l_rate IS NULL
254 THEN
255 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
256 THEN
257 --CN_NO_MATCH_CORP_CONV:
258 FND_MESSAGE.SET_NAME('CN', 'CN_NO_MATCH_CORP_CONV');
259 FND_MESSAGE.SET_TOKEN('FROM_CURR',l_from_currency );
260 FND_MESSAGE.SET_TOKEN('TO_CURR',p_target_currency_code );
261 FND_MSG_PUB.ADD;
262 END IF;
263 RAISE FND_API.G_EXC_ERROR;
264 END IF;
265 ELSIF (l_from_currency = p_target_currency_code)
266 THEN
267 l_rate := 1;
268 END IF;
269
270 l_comp_earned := 0;
271 l_comp_paid := 0;
272 --
273 --Compute commission and paid payment here.
274 --
275 FOR pid IN get_period_ids(p_start_date, p_end_date, rec.org_id)
276 LOOP
277 FOR comm IN get_comm_and_pmt(rec.salesrep_id, pid.start_period_id, pid.end_period_id,rec.org_id)
278 LOOP
279 l_comp_earned := comm.commission;
280 debug_msg('l_comp_earned = ' || to_char(l_comp_earned) );
281 l_comp_paid := comm.paid_pmt;
282 debug_msg('l_comp_paid = ' || to_char(l_comp_paid) );
283 END LOOP;
284
285 l_new_start_date := pid.new_start_date;
286 l_new_end_date := pid.new_end_date;
287 l_total_days := pid.total_days;
288 END LOOP;
289
290 --
291 --Do currency conversion here.
292 --
293 debug_msg('l_rate = ' || to_char(l_rate) );
294 l_converted_comp_earned := l_comp_earned * l_rate;
295 l_converted_comp_paid := l_comp_paid * l_rate;
296
297 --
298 --Do proration.
299 --
300 IF l_proration_flag = FND_API.G_TRUE
301 THEN
302 l_factor := l_prorated_days / l_total_days;
303 ELSIF l_proration_flag = FND_API.G_FALSE
304 THEN
305 l_factor := 1;
306 END IF;
307 debug_msg('l_factor = ' || to_char(l_factor) );
308
309 l_sum_converted_comp_earned := l_sum_converted_comp_earned + l_converted_comp_earned * l_factor;
310 l_sum_converted_comp_paid := l_sum_converted_comp_paid + l_converted_comp_paid * l_factor;
311
312 --To be used in finding new date range.
313 IF l_org_cnt = 1
314 THEN
315 l_temp_start_date := l_new_start_date;
316 l_temp_end_date := l_new_end_date;
317 l_temp_org_name := rec.operating_unit;
318 ELSIF l_org_cnt > 1
319 THEN
320 --No need to raise exception if calendar setup is different
321 --in different orgs. Only push the error message to the message stack.
322 IF ( (l_temp_start_date <> l_new_start_date) OR (l_temp_end_date <> l_new_end_date) )
323 THEN
324 l_new_date_range_exists := FALSE;
325
326 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
327 THEN
328 --'CN_NO_MATCH_DATE_RANGE'
329 FND_MESSAGE.SET_NAME('CN', 'CN_NO_MATCH_DATE_RANGE');
330 FND_MESSAGE.SET_TOKEN('OPERATING_UNIT1',l_temp_org_name );
331 FND_MESSAGE.SET_TOKEN('OPERATING_UNIT2',rec.operating_unit );
332 FND_MSG_PUB.ADD;
333 END IF;
334 END IF;
335 END IF;
336
337 END LOOP; --end of loop get_org_and_srp_id
338
339 IF l_org_cnt = 0
340 THEN
341 --Need to raise exception if no salesreps are found in any orgs.
342 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
343 THEN
344 --'CN_NO_MATCH_SALESREP'
345 FND_MESSAGE.SET_NAME('CN', 'CN_NO_MATCH_SALESREP');
346 FND_MESSAGE.SET_TOKEN('PERSON_ID',to_char(p_person_id) );
347 FND_MSG_PUB.ADD;
348 END IF;
349
350 RAISE FND_API.G_EXC_ERROR;
351 END IF;
352
353 --
354 --Get the compensation earned and paid.
355 --
356 x_comp_earned := l_sum_converted_comp_earned;
357 x_comp_paid := l_sum_converted_comp_paid;
358
359 --
360 --Get the new start date and new end date
361 --
362 IF l_new_date_range_exists = TRUE
363 THEN
364 x_new_start_date := l_temp_start_date;
365 x_new_end_date := l_temp_end_date;
366 ELSIF l_new_date_range_exists = FALSE
367 THEN
368 x_new_start_date := NULL;
369 x_new_end_date := NULL;
370 END IF;
371
372 -- End of API body.
373
374 --
375 -- Standard call to get message count and if count is 1, get message info.
376 --
377 FND_MSG_PUB.Count_And_Get
378 (
379 p_count => x_msg_count ,
380 p_data => x_msg_data ,
381 p_encoded => FND_API.G_FALSE
382 );
383
384 EXCEPTION
385 WHEN FND_API.G_EXC_ERROR THEN
386 x_return_status := FND_API.G_RET_STS_ERROR ;
387 FND_MSG_PUB.Count_And_Get
388 (
389 p_count => x_msg_count ,
390 p_data => x_msg_data ,
391 p_encoded => FND_API.G_FALSE
392 );
393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
395 FND_MSG_PUB.Count_And_Get
396 (
397 p_count => x_msg_count ,
398 p_data => x_msg_data ,
399 p_encoded => FND_API.G_FALSE
400 );
401 WHEN OTHERS THEN
402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
403 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
404 THEN
405 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
406 END IF;
407 FND_MSG_PUB.Count_And_Get
408 (
409 p_count => x_msg_count ,
410 p_data => x_msg_data ,
411 p_encoded => FND_API.G_FALSE
412 );
413 END get_comm_and_paid_pmt;
414
415
416 END CN_GET_COMM_PMT_PAID_GRP ;