[Home] [Help]
PACKAGE BODY: APPS.AR_REVENUE_ADJUSTMENT_PVT
Source
1 PACKAGE BODY AR_Revenue_Adjustment_PVT AS
2 /*$Header: ARXVRADB.pls 120.38.12010000.8 2008/12/01 06:35:28 ankuagar ship $*/
3
4 /*=======================================================================+
5 | Global Constants
6 +=======================================================================*/
7 g_ra_empty_dist_tbl RA_Dist_Tbl_Type;
8 g_ra_dist_tbl RA_Dist_Tbl_Type;
9 g_dist_count NUMBER;
10 g_rev_mgt_installed VARCHAR2(1); -- Bug 2650708
11 g_period_set_name VARCHAR(15);
12 g_base_precision NUMBER;
13 g_bmau NUMBER;
14 g_org_id NUMBER;
15 g_sob_id NUMBER;
16
17 g_warehouse_id NUMBER;
18 g_memo_line_id NUMBER;
19 g_inventory_item_id NUMBER;
20 g_line_id NUMBER;
21
22 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AR_Revenue_Adjustment_PVT';
23
24 -----------------------------------------------------------------------
25 -- API name : Unearn_Revenue
26 -- Type : Private
27 -- Function : Transfers a specified amount of revenue from
28 -- earned to unearned revenue account
29 -- Pre-reqs : Sufficient earned revenue must exist.
30 -- Parameters :
31 -- IN : p_api_version NUMBER Required
32 -- p_init_msg_list VARCHAR2 Optional
33 -- Default = FND_API.G_FALSE
34 -- p_commit VARCHAR2 Optional
35 -- Default = FND_API.G_FALSE
36 -- p_validation_level NUMBER Optional
37 -- Default = FND_API.G_VALID_LEVEL_FULL
38 -- p_rev_adj_rec Rev_Adj_Rec_Type Required
39 -- OUT NOCOPY : x_return_status VARCHAR2(1)
40 -- x_msg_count NUMBER
41 -- x_msg_data VARCHAR2(2000)
42 -- x_adjustment_id NUMBER
43 -- x_adjustment_number VARCHAR2
44 --
45 -- Version : Current version 2.0
46 -- IN parameters consolidated into new record type
47 -- Initial version 1.0
48 --
49 -- Notes : AutoAccounting used for both debits and credits
50 --
51 -----------------------------------------------------------------------
52 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
53
54 PROCEDURE Unearn_Revenue
55 ( p_api_version IN NUMBER
56 ,p_init_msg_list IN VARCHAR2
57 ,p_commit IN VARCHAR2
58 ,p_validation_level IN NUMBER
59 ,x_return_status OUT NOCOPY VARCHAR2
60 ,x_msg_count OUT NOCOPY NUMBER
61 ,x_msg_data OUT NOCOPY VARCHAR2
62 ,p_rev_adj_rec IN Rev_Adj_Rec_Type
63 ,x_adjustment_id OUT NOCOPY NUMBER
64 ,x_adjustment_number OUT NOCOPY VARCHAR2)
65 IS
66 l_api_name CONSTANT VARCHAR2(30) := 'Unearn_Revenue';
67 l_api_version CONSTANT NUMBER := 2.0;
68 l_rev_adj_rec AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type;
69
70 BEGIN
71 IF PG_DEBUG in ('Y', 'C') THEN
72 arp_util.debug('AR_Revenue_Adjustment_PVT.Unearn_Revenue()+');
73 END IF;
74 -- Standard Start of API savepoint
75 SAVEPOINT Unearn_Revenue_PVT;
76 -- Standard call to check for call compatibility.
77 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
78 p_api_version ,
79 l_api_name ,
80 G_PKG_NAME )
81 THEN
82 IF PG_DEBUG in ('Y', 'C') THEN
83 arp_util.debug('Unearn_Revenue: ' || '.Unexpected error '||sqlerrm||
84 ' at AR_Revenue_Adjustment_PVT.Unearn_Revenue()+');
85 END IF;
86 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
87 END IF;
88 -- Initialize message list if p_init_msg_list is set to TRUE.
89 IF FND_API.to_Boolean( p_init_msg_list )
90 THEN
91 FND_MSG_PUB.initialize;
92 END IF;
93 -- Initialize API return status to success
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95
96 l_rev_adj_rec := p_rev_adj_rec;
97
98 /*Bug 6731185 JVARKEY Making sure GL date has no timestamp*/
99 l_rev_adj_rec.gl_date := trunc(p_rev_adj_rec.gl_date);
100
101 l_rev_adj_rec.adjustment_type := 'UN';
102 AR_Revenue_Adjustment_PVT.earn_or_unearn
103 (p_rev_adj_rec => l_rev_adj_rec
104 ,p_validation_level => p_validation_level
105 ,x_return_status => x_return_status
106 ,x_msg_count => x_msg_count
107 ,x_msg_data => x_msg_data
108 ,x_adjustment_id => x_adjustment_id
109 ,x_adjustment_number => x_adjustment_number);
110 IF x_return_status = FND_API.G_RET_STS_ERROR
111 THEN
112 RAISE FND_API.G_EXC_ERROR;
113 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
114 THEN
115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116 END IF;
117
118 -- Standard check of p_commit.
119 IF FND_API.To_Boolean( p_commit )
120 THEN
121 COMMIT WORK;
122 END IF;
123 -- Standard call to get message count and if count is 1, get message info.
124 FND_MSG_PUB.Count_And_Get
125 (p_encoded => FND_API.G_FALSE,
126 p_count => x_msg_count,
127 p_data => x_msg_data);
128 IF PG_DEBUG in ('Y', 'C') THEN
129 arp_util.debug('AR_Revenue_Adjustment_PVT.Unearn_Revenue()-');
130 END IF;
131 EXCEPTION
132 WHEN FND_API.G_EXC_ERROR THEN
133 ROLLBACK TO Unearn_Revenue_PVT;
134 x_return_status := FND_API.G_RET_STS_ERROR ;
135 FND_MSG_PUB.Count_And_Get
136 (p_encoded => FND_API.G_FALSE,
137 p_count => x_msg_count,
138 p_data => x_msg_data);
139 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
140 IF PG_DEBUG in ('Y', 'C') THEN
141 arp_util.debug('Unearn_Revenue: ' || 'Unexpected error '||sqlerrm||
142 ' at AR_Revenue_Adjustment_PVT.Unearn_Revenue()+');
143 END IF;
144 ROLLBACK TO Unearn_Revenue_PVT;
145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
146 FND_MSG_PUB.Count_And_Get
147 (p_encoded => FND_API.G_FALSE,
148 p_count => x_msg_count,
149 p_data => x_msg_data);
150 WHEN OTHERS THEN
151 IF (SQLCODE = -20001)
152 THEN
153 IF PG_DEBUG in ('Y', 'C') THEN
154 arp_util.debug('Unearn_Revenue: ' || '20001 error '||
155 ' at AR_Revenue_Adjustment_PVT.Unearn_Revenue()+');
156 END IF;
157 x_return_status := FND_API.G_RET_STS_ERROR ;
158 ELSE
159 IF PG_DEBUG in ('Y', 'C') THEN
160 arp_util.debug('Unearn_Revenue: ' || 'Unexpected error '||sqlerrm||
161 ' at AR_Revenue_Adjustment_PVT.Unearn_Revenue()+');
162 END IF;
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
164 IF FND_MSG_PUB.Check_Msg_Level
165 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
166 THEN
167 FND_MSG_PUB.Add_Exc_Msg
168 ( G_PKG_NAME ,
169 l_api_name
170 );
171 END IF;
172 END IF;
173 ROLLBACK TO Unearn_Revenue_PVT;
174 FND_MSG_PUB.Count_And_Get
175 (p_encoded => FND_API.G_FALSE,
176 p_count => x_msg_count,
177 p_data => x_msg_data);
178 END Unearn_Revenue;
179
180 -----------------------------------------------------------------------
181 -- API name : Earn_Revenue
182 -- Type : Private
183 -- Function : Transfers a specified amount of revenue from
184 -- unearned to earned revenue account.
185 -- Pre-reqs : Sufficient unearned revenue must exist.
186 -- Parameters :
187 -- IN : p_api_version NUMBER Required
188 -- p_init_msg_list VARCHAR2 Optional
189 -- Default = FND_API.G_FALSE
190 -- p_commit VARCHAR2 Optional
191 -- Default = FND_API.G_FALSE
192 -- p_validation_level NUMBER Optional
193 -- Default = FND_API.G_VALID_LEVEL_FULL
194 -- p_rev_adj_rec Rev_Adj_Rec_Type Required
195 -- OUT NOCOPY : x_return_status VARCHAR2(1)
196 -- x_msg_count NUMBER
197 -- x_msg_data VARCHAR2(2000)
198 -- x_adjustment_id NUMBER
199 -- x_adjustment_number VARCHAR2
200 --
201 -- Version : Current version 2.0
202 -- IN parameters consolidated into new record type
203 -- Initial version 1.0
204 --
205 -- Notes : AutoAccounting used for both debits and credits
206 --
207 -----------------------------------------------------------------------
208 PROCEDURE Earn_Revenue
209 ( p_api_version IN NUMBER
210 ,p_init_msg_list IN VARCHAR2
211 ,p_commit IN VARCHAR2
212 ,p_validation_level IN NUMBER
213 ,x_return_status OUT NOCOPY VARCHAR2
214 ,x_msg_count OUT NOCOPY NUMBER
215 ,x_msg_data OUT NOCOPY VARCHAR2
216 ,p_rev_adj_rec IN Rev_Adj_Rec_Type
217 ,x_adjustment_id OUT NOCOPY NUMBER
218 ,x_adjustment_number OUT NOCOPY VARCHAR2)
219 IS
220 l_api_name CONSTANT VARCHAR2(30) := 'Earn_Revenue';
221 l_api_version CONSTANT NUMBER := 2.0;
222 l_rev_adj_rec AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type;
223
224 BEGIN
225 IF PG_DEBUG in ('Y', 'C') THEN
226 arp_util.debug('AR_Revenue_Adjustment_PVT.Earn_Revenue()+');
227 END IF;
228 -- Standard Start of API savepoint
229 SAVEPOINT Earn_Revenue_PVT;
230 -- Standard call to check for call compatibility.
231 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
232 p_api_version ,
233 l_api_name ,
234 G_PKG_NAME )
235 THEN
236 IF PG_DEBUG in ('Y', 'C') THEN
237 arp_util.debug('Earn_Revenue: ' || 'Unexpected error '||sqlerrm||
238 ' at AR_Revenue_Adjustment_PVT.Earn_Revenue()+');
239 END IF;
240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
241 END IF;
242 -- Initialize message list if p_init_msg_list is set to TRUE.
243 IF FND_API.to_Boolean( p_init_msg_list )
244 THEN
245 FND_MSG_PUB.initialize;
246 END IF;
247 -- Initialize API return status to success
248 x_return_status := FND_API.G_RET_STS_SUCCESS;
249
250 l_rev_adj_rec := p_rev_adj_rec;
251
252 /*Bug 6731185 JVARKEY Making sure GL date has no timestamp*/
253 l_rev_adj_rec.gl_date := trunc(p_rev_adj_rec.gl_date);
254
255 l_rev_adj_rec.adjustment_type := 'EA';
256 AR_Revenue_Adjustment_PVT.earn_or_unearn
257 (p_rev_adj_rec => l_rev_adj_rec
258 ,p_validation_level => p_validation_level
259 ,x_return_status => x_return_status
260 ,x_msg_count => x_msg_count
261 ,x_msg_data => x_msg_data
262 ,x_adjustment_id => x_adjustment_id
263 ,x_adjustment_number => x_adjustment_number);
264 IF x_return_status = FND_API.G_RET_STS_ERROR
265 THEN
266 RAISE FND_API.G_EXC_ERROR;
267 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
268 THEN
269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270 END IF;
271 -- Standard check of p_commit.
272 IF FND_API.To_Boolean( p_commit )
273 THEN
274 COMMIT WORK;
275 END IF;
276 -- Standard call to get message count and if count is 1, get message info.
277 FND_MSG_PUB.Count_And_Get
278 (p_encoded => FND_API.G_FALSE,
279 p_count => x_msg_count,
280 p_data => x_msg_data);
281 IF PG_DEBUG in ('Y', 'C') THEN
282 arp_util.debug('AR_Revenue_Adjustment_PVT.Earn_Revenue()-');
283 END IF;
284 EXCEPTION
285 WHEN FND_API.G_EXC_ERROR THEN
286 ROLLBACK TO Earn_Revenue_PVT;
287 x_return_status := FND_API.G_RET_STS_ERROR ;
288 FND_MSG_PUB.Count_And_Get
289 (p_encoded => FND_API.G_FALSE,
290 p_count => x_msg_count,
291 p_data => x_msg_data);
292 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
293 IF PG_DEBUG in ('Y', 'C') THEN
294 arp_util.debug('Earn_Revenue: ' || 'Unexpected error '||sqlerrm||
295 ' at AR_Revenue_Adjustment_PVT.Earn_Revenue()+');
296 END IF;
297 ROLLBACK TO Earn_Revenue_PVT;
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
299 FND_MSG_PUB.Count_And_Get
300 (p_encoded => FND_API.G_FALSE,
301 p_count => x_msg_count,
302 p_data => x_msg_data);
303 WHEN OTHERS THEN
304 IF (SQLCODE = -20001)
305 THEN
306 IF PG_DEBUG in ('Y', 'C') THEN
307 arp_util.debug('Earn_Revenue: ' || '20001 error '||
308 ' at AR_Revenue_Adjustment_PVT.Earn_Revenue()+');
309 END IF;
310 x_return_status := FND_API.G_RET_STS_ERROR ;
311 ELSE
312 IF PG_DEBUG in ('Y', 'C') THEN
313 arp_util.debug('Earn_Revenue: ' || 'Unexpected error '||sqlerrm||
314 ' at AR_Revenue_Adjustment_PVT.Earn_Revenue()+');
315 END IF;
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
317 IF FND_MSG_PUB.Check_Msg_Level
318 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
319 THEN
320 FND_MSG_PUB.Add_Exc_Msg
321 ( G_PKG_NAME ,
322 l_api_name
323 );
324 END IF;
325 END IF;
326 ROLLBACK TO Earn_Revenue_PVT;
327 FND_MSG_PUB.Count_And_Get
328 (p_encoded => FND_API.G_FALSE,
329 p_count => x_msg_count,
330 p_data => x_msg_data);
331 END Earn_Revenue;
332
333 -----------------------------------------------------------------------
334 -- API name : Earn_Revenue (OVERLOADED)
335 -- Type : Private
336 -- Function : Transfers a specified amount of revenue from
337 -- unearned to earned revenue account. This is an
338 -- overlaid version of the previous procedure that
339 -- returns the new distributions in a pl/sql table
340 -- rather than writing them to ra_cust_trx_line_gl_dist
341 -- Pre-reqs : Sufficient unearned revenue must exist.
342 -- Parameters :
343 -- IN : p_api_version NUMBER Required
344 -- p_init_msg_list VARCHAR2 Optional
345 -- Default = FND_API.G_FALSE
346 -- p_commit VARCHAR2 Optional
347 -- Default = FND_API.G_FALSE
348 -- p_validation_level NUMBER Optional
349 -- Default = FND_API.G_VALID_LEVEL_FULL
350 -- p_rev_adj_rec Rev_Adj_Rec_Type Required
351 -- OUT NOCOPY : x_return_status VARCHAR2(1)
352 -- x_msg_count NUMBER
353 -- x_msg_data VARCHAR2(2000)
354 -- x_adjustment_id NUMBER
355 -- x_adjustment_number VARCHAR2
356 -- x_dist_count NUMBER
357 -- x_ra_dist_tbl RA_Dist_Tbl_Type
358 --
359 -- Version : Current version 2.0
363 -- This procedure is an overlay of the standard Unearn_Revenue
360 -- Initial version 1.0
361 --
362 -- Notes : AutoAccounting used for both debits and credits
364 -- API which does not write out NOCOPY a revenue adjustment record or
365 -- entries into ra_cust_trx_line_gl_dist. Instead it outputs
366 -- the distributions to a pl/sql table which is passed out NOCOPY to
367 -- the calling routine
368 -----------------------------------------------------------------------
369 PROCEDURE Earn_Revenue
370 ( p_api_version IN NUMBER
371 ,p_init_msg_list IN VARCHAR2
372 ,p_commit IN VARCHAR2
373 ,p_validation_level IN NUMBER
374 ,x_return_status OUT NOCOPY VARCHAR2
375 ,x_msg_count OUT NOCOPY NUMBER
376 ,x_msg_data OUT NOCOPY VARCHAR2
377 ,p_rev_adj_rec IN Rev_Adj_Rec_Type
378 ,x_adjustment_id OUT NOCOPY NUMBER
379 ,x_adjustment_number OUT NOCOPY VARCHAR2
380 ,x_dist_count OUT NOCOPY NUMBER
381 ,x_ra_dist_tbl OUT NOCOPY RA_Dist_Tbl_Type)
382 IS
383
384 BEGIN
385 IF PG_DEBUG in ('Y', 'C') THEN
386 arp_util.debug('AR_Revenue_Adjustment_PVT.(OVERLOADED)Earn_Revenue()+');
387 END IF;
388
389 --initialize the global gl distributions table
390 g_ra_dist_tbl := g_ra_empty_dist_tbl ;
391 g_dist_count := 0;
392
393 --This flag tells the API not to create any distributions or revenue
394 --adjustment records but to insert distributions into a pl/sql table
395 g_update_db_flag := 'N';
396
397 Earn_Revenue
398 ( p_api_version => 2.0
399 ,p_init_msg_list => FND_API.G_TRUE
400 ,p_commit => FND_API.G_FALSE
401 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
402 ,x_return_status => x_return_status
403 ,x_msg_count => x_msg_count
404 ,x_msg_data => x_msg_data
405 ,p_rev_adj_rec => p_rev_adj_rec
406 ,x_adjustment_id => x_adjustment_id
407 ,x_adjustment_number => x_adjustment_number);
408
409 --Now set the table output parameter and the count of rows in it
410 x_ra_dist_tbl := g_ra_dist_tbl;
411 x_dist_count := g_dist_count;
412
413 --Set the g_update_db_flag variable to 'Y' which is the default option
414 --for all other calls to the API
415 g_update_db_flag := 'Y';
416
417 IF PG_DEBUG in ('Y', 'C') THEN
418 arp_util.debug('AR_Revenue_Adjustment_PVT.(OVERLOADED)Earn_Revenue()-');
419 END IF;
420 END Earn_Revenue;
421
422 PROCEDURE earn_or_unearn
423 (p_rev_adj_rec IN Rev_Adj_Rec_Type
424 ,p_validation_level IN NUMBER
425 ,x_return_status OUT NOCOPY VARCHAR2
426 ,x_msg_count OUT NOCOPY NUMBER
427 ,x_msg_data OUT NOCOPY VARCHAR2
428 ,x_adjustment_id OUT NOCOPY NUMBER
429 ,x_adjustment_number OUT NOCOPY VARCHAR2)
430 IS
431 l_rev_adj_rec AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type;
432 l_line_id NUMBER;
433 l_line_amount NUMBER;
434 l_line_acctd_amount NUMBER;
435 l_cm_line_amount NUMBER;
436 l_net_line_amount NUMBER;
437 l_line_total NUMBER;
438 l_line_count NUMBER;
439 l_no_of_lines NUMBER;
440 l_total_adjusted NUMBER;
441 l_line_rev_total NUMBER;
442 l_line_rsc_total NUMBER;
443 l_line_rsc_amount NUMBER;
444 l_adj_inv_total NUMBER;
445 l_line_adjustable NUMBER;
446 l_adjustable_amount NUMBER;
447 l_revenue_amount NUMBER;
448 l_revenue_amount_prorata NUMBER;
449 l_type_code VARCHAR2(2);
450 l_gl_date_valid DATE; -- Bug 2146970
451 l_scenario NUMBER; -- Bug 2560048
452 l_first_adjustment_number NUMBER; -- Bug 2560048
453 l_last_adjustment_number NUMBER; -- Bug 2560048
454 l_override_flag VARCHAR2(1);
455 l_user_generated_flag VARCHAR2(1);
456 l_sr_count NUMBER; -- 5021530
457 x_line_count_out NUMBER; -- 6223281
458 x_acctd_amount_out NUMBER; -- 6223281
459
460 l_xla_call_req_flag boolean := FALSE;--BUG 7130380
461 l_xla_event arp_xla_events.xla_events_type;
462
463 -- Bug 3431815: credit memos included
464 -- Bug 3536944: c_line cursor broken up into 3 different queries to
465 -- improve performance: c_line, c_line_amount and c_cm_line_amount.
466
467 CURSOR c_line IS
468 SELECT l.customer_trx_line_id
469 ,l.memo_line_id
470 ,l.inventory_item_id
471 ,l.accounting_rule_id
472 ,l.accounting_rule_duration -- Bug 2168875
473 ,NVL(l.override_auto_accounting_flag,'N')
474 override_auto_accounting_flag-- Bug 3879222
475 FROM mtl_item_categories mic
476 ,ra_customer_trx_lines l
477 WHERE l.line_type = 'LINE'
478 AND l.autorule_complete_flag IS NULL
482 AND NVL(l.inventory_item_id,0) =
479 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
480 AND l.customer_trx_line_id =
481 NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,l.customer_trx_line_id)
483 NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
484 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
485 AND l.inventory_item_id = mic.inventory_item_id(+)
486 AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
487 DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
488 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
489 AND ((AR_RAAPI_UTIL.g_from_salesrep_id IS NULL AND
490 AR_RAAPI_UTIL.g_from_salesgroup_id IS NULL)
491 OR EXISTS
492 (SELECT 'X'
493 FROM ra_cust_trx_line_salesreps ls
494 WHERE ls.customer_trx_line_id = l.customer_trx_line_id
495 AND ls.salesrep_id =
496 NVL(AR_RAAPI_UTIL.g_from_salesrep_id,ls.salesrep_id)
497 AND NVL(ls.revenue_salesgroup_id, -9999) =
498 NVL(AR_RAAPI_UTIL.g_from_salesgroup_id,
499 NVL(ls.revenue_salesgroup_id, -9999))
500 GROUP BY ls.salesrep_id
501 HAVING SUM(NVL(ls.revenue_percent_split,0)) <> 0));
502
503
504 BEGIN
505 IF PG_DEBUG in ('Y', 'C') THEN
506 arp_util.debug('AR_Revenue_Adjustment_PVT.earn_or_unearn()+');
507 END IF;
508 l_rev_adj_rec := p_rev_adj_rec;
509
510 /*Bug 6731185 JVARKEY Making sure GL date has no timestamp*/
511 l_rev_adj_rec.gl_date := trunc(p_rev_adj_rec.gl_date);
512
513 AR_RAAPI_UTIL.Constant_System_Values;
514 AR_RAAPI_UTIL.Initialize_Globals;
515 AR_RAAPI_UTIL.Validate_Parameters (p_init_msg_list => FND_API.G_FALSE
516 ,p_rev_adj_rec => l_rev_adj_rec
517 ,p_validation_level => p_validation_level
518 ,x_return_status => x_return_status
519 ,x_msg_count => x_msg_count
520 ,x_msg_data => x_msg_data);
521 IF x_return_status = FND_API.G_RET_STS_SUCCESS
522 THEN
523 l_total_adjusted := 0;
524
525 AR_RAAPI_UTIL.Validate_Amount
526 (p_init_msg_list => FND_API.G_FALSE
527 ,p_customer_trx_line_id => AR_RAAPI_UTIL.g_from_cust_trx_line_id
528 ,p_adjustment_type => p_rev_adj_rec.adjustment_type
529 ,p_amount_mode => p_rev_adj_rec.amount_mode
530 ,p_customer_trx_id => AR_RAAPI_UTIL.g_customer_trx_id
531 ,p_salesrep_id => AR_RAAPI_UTIL.g_from_salesrep_id
532 ,p_salesgroup_id => AR_RAAPI_UTIL.g_from_salesgroup_id
533 ,p_sales_credit_type => p_rev_adj_rec.sales_credit_type
534 ,p_item_id => AR_RAAPI_UTIL.g_from_inventory_item_id
535 ,p_category_id => AR_RAAPI_UTIL.g_from_category_id
536 ,p_revenue_amount_in => p_rev_adj_rec.amount
537 ,p_revenue_percent => p_rev_adj_rec.percent
538 ,p_revenue_amount_out => l_revenue_amount
539 ,p_adjustable_amount_out => l_adj_inv_total
540 ,p_line_count_out => l_no_of_lines
541 ,x_return_status => x_return_status
542 ,x_msg_count => x_msg_count
543 ,x_msg_data => x_msg_data);
544 END IF;
545
546 IF x_return_status = FND_API.G_RET_STS_SUCCESS
547 THEN
548 l_rev_adj_rec.amount := l_revenue_amount;
549 l_line_count := 0;
550
551 /* Bug 2146970 - validate the GL date passed in */
552 l_gl_date_valid := AR_RAAPI_UTIL.bump_gl_date_if_closed
553 (p_gl_date => l_rev_adj_rec.gl_date);
554
555 IF PG_DEBUG in ('Y','C')
556 THEN
557 arp_util.debug('original gl_date = ' || l_rev_adj_rec.gl_date);
558 arp_util.debug('new gl_date = ' || l_gl_date_valid);
559 END IF;
560
561 IF l_gl_date_valid IS NULL
562 THEN
563 FND_MESSAGE.set_name('AR','AR_VAL_GL_DATE');
564 FND_MSG_PUB.Add;
565 RAISE FND_API.G_EXC_ERROR;
566 ELSE
567 /* date was returned, use it */
568 l_rev_adj_rec.gl_date := l_gl_date_valid;
569
570 IF PG_DEBUG in ('Y','C')
571 THEN
572 arp_util.debug('gl_date set to = ' || l_rev_adj_rec.gl_date);
573 END IF;
574 END IF;
575
576 IF g_update_db_flag = 'Y'
577 THEN
578 create_adjustment
579 (p_rev_adj_rec => l_rev_adj_rec
580 ,x_adjustment_id => x_adjustment_id
581 ,x_adjustment_number => x_adjustment_number);
582 IF PG_DEBUG in ('Y', 'C') THEN
583 arp_util.debug('revenue_adjustment_id : '||x_adjustment_id);
584 arp_util.debug('revenue_adjustment_number : '||x_adjustment_number);
585 arp_util.debug('g_rev_mgt_installed : '||g_rev_mgt_installed);
586 arp_util.debug('p_rev_adj_rec.source : '||p_rev_adj_rec.source);
587 END IF;
588 /* Bug 2560048 RAM-C - if api call not from collectibility engine
589 then the transaction must be recorded as manually overridden */
593 NVL(p_rev_adj_rec.source,-99) <>
590 /* Bug 2650708 - cater for null source value, and only call if
591 rev mgt installed and transaction is monitored by rev mgt */
592 IF (g_rev_mgt_installed = 'Y' AND
594 ar_revenue_management_pvt.c_revenue_management_source AND
595 ar_revenue_management_pvt.acceptance_allowed
596 (p_rev_adj_rec.customer_trx_id,
597 p_rev_adj_rec.from_cust_trx_line_id) <>
598 ar_revenue_management_pvt.c_transaction_not_monitored)
599 THEN
600 ar_revenue_management_pvt.revenue_synchronizer(
601 p_mode =>
602 ar_revenue_management_pvt.c_manual_override_mode
603 , p_customer_trx_id => p_rev_adj_rec.customer_trx_id
604 , p_customer_trx_line_id => p_rev_adj_rec.from_cust_trx_line_id
605 , p_gl_date => NULL
606 , p_comments => NULL
607 , p_ram_desc_flexfield => NULL
608 , x_scenario => l_scenario
609 , x_first_adjustment_number => l_first_adjustment_number
610 , x_last_adjustment_number => l_last_adjustment_number
611 , x_return_status => x_return_status
612 , x_msg_count => x_msg_count
613 , x_msg_data => x_msg_data);
614 END IF;
615 ELSE
616 x_adjustment_id := NULL;
617 x_adjustment_number := NULL;
618 END IF;
619 IF PG_DEBUG in ('Y', 'C') THEN
620 arp_util.debug('x_return_status : '||x_return_status);
621 END IF;
622 /* Bug 2651996 - a null return status is being passed back from revenue
623 synchronizer - assume successful */
624 IF x_return_status IS NULL
625 THEN
626 x_return_status := FND_API.G_RET_STS_SUCCESS;
627 END IF;
628 IF PG_DEBUG in ('Y', 'C') THEN
629 arp_util.debug('x_return_status : '||x_return_status);
630 END IF;
631
632 IF p_rev_adj_rec.adjustment_type = 'EA'
633 THEN
634 l_revenue_amount := l_revenue_amount * -1;
635 END IF;
636
637 /* Bug 4304865: set flag for later storage on ra_cust_trx_line_gl_dist
638 if earn/unearn not called from revenue management package */
639 IF NVL(p_rev_adj_rec.source, -99999) <>
640 ar_revenue_management_pvt.c_revenue_management_source
641 THEN
642 l_user_generated_flag := 'Y';
643 ELSE
644 l_user_generated_flag := NULL;
645 END IF;
646
647 FOR c1 IN c_line LOOP
648 l_line_id := c1.customer_trx_line_id;
649
650 /* 6223281 - fetch the maximum amount allowable for the adjustment
651 based on the given parameters */
652 l_line_adjustable := AR_RAAPI_UTIL.Adjustable_Revenue
653 (p_customer_trx_line_id => l_line_id
654 ,p_adjustment_type => p_rev_adj_rec.adjustment_type
655 ,p_customer_trx_id => AR_RAAPI_UTIL.g_customer_trx_id
656 ,p_salesrep_id => AR_RAAPI_UTIL.g_from_salesrep_id
657 ,p_salesgroup_id => AR_RAAPI_UTIL.g_from_salesgroup_id
658 ,p_sales_credit_type => p_rev_adj_rec.sales_credit_type
659 ,p_item_id => AR_RAAPI_UTIL.g_from_inventory_item_id
660 ,p_category_id => AR_RAAPI_UTIL.g_from_category_id
661 ,p_revenue_adjustment_id => NULL -- intentionally null
662 ,p_line_count_out => x_line_count_out
663 ,p_acctd_amount_out => x_acctd_amount_out);
664
665 IF l_line_adjustable <> 0
666 THEN
667 l_revenue_amount_prorata := ROUND(l_revenue_amount *
668 l_line_adjustable / l_adj_inv_total,AR_RAAPI_UTIL.g_trx_precision);
669
670 /* Check for salesreps for use later */
671 SELECT count(*)
672 INTO l_sr_count
673 FROM ra_cust_trx_line_salesreps
674 WHERE customer_trx_line_id = l_line_id
675 AND NVL(revenue_percent_split,0) <> 0
676 AND customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id;
677
678 IF c1.accounting_rule_id IS NOT NULL
679 THEN
680 /* 6325023 - previous logic differentiated between
681 override_auto_accounting_flag, sr_count, etc.
682 Due to problems with rounding of acctd_amount,
683 we decided to remove reference/use of debit_credit
684 entirely and just consolidate on use of
685 dists_by_model and the centralized rounding logic. */
686
687 /* Set the override flag so we round this trx later */
688 l_override_flag := 'Y';
689
690 /* The API/wizard takes all amounts as positive values
691 and code above changes some of them (EA) to negative.
692 This code assumes REV to be positive and UE as negative
693 so we have to switch the signs.*/
694 l_revenue_amount_prorata := l_revenue_amount_prorata * -1;
695
696 /* New Logic for proration without salesreps */
697 dists_by_model(c1.customer_trx_line_id
701 ,l_rev_adj_rec.gl_date);
698 ,l_revenue_amount_prorata
699 ,x_adjustment_id
700 ,l_user_generated_flag
702
703 IF l_xla_call_req_flag <> TRUE THEN
704 l_xla_call_req_flag := true;
705 END IF;
706
707 /* Store adjustment info in AR_LINE_REV_ADJ_GT
708 so we can round the adjustment dists later */
709 INSERT INTO AR_LINE_REV_ADJ_GT(
710 CUSTOMER_TRX_ID,
711 CUSTOMER_TRX_LINE_ID,
712 REVENUE_ADJUSTMENT_ID,
713 AMOUNT,
714 PERCENT)
715 VALUES
716 (p_rev_adj_rec.customer_trx_id,
717 c1.customer_trx_line_id,
718 x_adjustment_id,
719 l_revenue_amount_prorata,
720 p_rev_adj_rec.percent);
721
722 ELSE
723 /* 5021530 call new logic (no_sr) conditionally */
724 IF c1.override_auto_accounting_flag = 'Y' OR
725 l_sr_count = 0
726 THEN
727 /* new non-SR logic */
728 /* Set the override flag so we round this trx later */
729 l_override_flag := 'Y';
730
731 /* The API/wizard takes all amounts as positive values
732 and code above changes some of them (EA) to negative.
733 This code assumes REV to be positive and UE as negative
734 so we have to switch the signs.*/
735 l_revenue_amount_prorata := l_revenue_amount_prorata * -1;
736
737 IF (PG_DEBUG IN ('Y','C'))
738 THEN
739 arp_debug.debug('l_revenue_amount_prorata = ' ||
740 l_revenue_amount_prorata);
741 END IF;
742
743 no_rule_debit_credit_no_sr(c1.customer_trx_line_id
744 ,AR_RAAPI_UTIL.g_customer_trx_id
745 ,AR_RAAPI_UTIL.g_from_salesrep_id
746 ,l_revenue_amount_prorata
747 ,l_gl_date_valid -- bug 2146970
748 ,NULL
749 ,c1.inventory_item_id
750 ,c1.memo_line_id
751 ,x_adjustment_id
752 ,l_user_generated_flag);
753
754 /* Store adjustment info in AR_LINE_REV_ADJ_GT
755 so we can round the adjustment dists later */
756 INSERT INTO AR_LINE_REV_ADJ_GT(
757 CUSTOMER_TRX_ID,
758 CUSTOMER_TRX_LINE_ID,
759 REVENUE_ADJUSTMENT_ID,
760 AMOUNT,
761 PERCENT)
762 VALUES
763 (p_rev_adj_rec.customer_trx_id,
764 c1.customer_trx_line_id,
765 x_adjustment_id,
766 l_revenue_amount_prorata,
767 p_rev_adj_rec.percent);
768
769 ELSE
770 /* Original SR-based logic */
771 no_rule_debit_credit (c1.customer_trx_line_id
772 ,AR_RAAPI_UTIL.g_customer_trx_id
773 ,AR_RAAPI_UTIL.g_from_salesrep_id
774 ,l_revenue_amount_prorata
775 ,l_gl_date_valid -- bug 2146970
776 ,NULL
777 ,c1.inventory_item_id
778 ,c1.memo_line_id
779 ,x_adjustment_id
780 ,l_user_generated_flag);
781 END IF;
782 END IF;
783 END IF;
784
785 END LOOP; -- c_line loop
786
787 IF l_xla_call_req_flag THEN
788 IF AR_RAAPI_UTIL.g_customer_trx_id is NOT NULL THEN
789 l_xla_event.xla_from_doc_id := AR_RAAPI_UTIL.g_customer_trx_id;
790 l_xla_event.xla_to_doc_id := AR_RAAPI_UTIL.g_customer_trx_id;
791 l_xla_event.xla_req_id := NULL;
792 l_xla_event.xla_dist_id := NULL;
793 l_xla_event.xla_doc_table := 'CT';
794 l_xla_event.xla_doc_event := NULL;
795 l_xla_event.xla_mode := 'O';
796 l_xla_event.xla_call := 'B';
797
798 ARP_XLA_EVENTS.CREATE_EVENTS(p_xla_ev_rec => l_xla_event );
799 END IF;
800 END IF;
801
802 /* Bug 3879222 */
803 /* Call the rounding logic for overridden adjustments
804 if the flag is set. Note that the adjustments to
805 be rounded are stored in a global temporary table
806 and that table is used by the rounding code to
807 determine the lines targeted for rounding. */
808 IF (l_override_flag = 'Y')
809 THEN
810 /* This call will round each adjustment that has be
811 previously recorded in ar_rev_line_adj_gt */
812 IF (arp_rounding.correct_rev_adj_by_line = 0)
813 THEN
814 arp_util.debug('ERROR: arp_rounding.correct_rev_adj_by_line');
818
815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
816 END IF;
817 END IF;
819 END IF;
820 IF PG_DEBUG in ('Y', 'C') THEN
821 arp_util.debug('AR_Revenue_Adjustment_PVT.earn_or_unearn()-');
822 END IF;
823
824 EXCEPTION
825
826 WHEN OTHERS then
827 IF (SQLCODE = -20001)
828 THEN
829 IF PG_DEBUG in ('Y', 'C') THEN
830 arp_util.debug
831 ('20001 error at AR_Revenue_Adjustment_PVT.earn_or_unearn');
832 END IF;
833 RAISE FND_API.G_EXC_ERROR;
834 ELSE
835 IF PG_DEBUG in ('Y', 'C') THEN
836 arp_util.debug('earn_or_unearn: ' || 'Unexpected error '||sqlerrm||
837 ' at AR_Revenue_Adjustment_PVT.earn_or_unearn()+');
838 END IF;
839 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
840 END IF;
841 END earn_or_unearn;
842
843 -----------------------------------------------------------------------
844 -- API name : Transfer_Sales_Credits
845 -- Type : Private
846 -- Function : Transfers revenue and/or non revenue sales credits
847 -- between the specified salesreps. The associated
848 -- earned revenue is transferred with revenue sales
849 -- credits
850 -- Pre-reqs : Sufficient earned revenue must exist for the salesrep
851 -- from whom sales credits are being transferred.
852 -- Parameters :
853 -- IN : p_api_version NUMBER Required
854 -- p_init_msg_list VARCHAR2 Optional
855 -- Default = FND_API.G_FALSE
856 -- p_commit VARCHAR2 Optional
857 -- Default = FND_API.G_FALSE
858 -- p_validation_level NUMBER Optional
859 -- Default = FND_API.G_VALID_LEVEL_FULL
860 -- p_rev_adj_rec Rev_Adj_Rec_Type Required
861 -- OUT NOCOPY : x_return_status VARCHAR2(1)
862 -- x_msg_count NUMBER
863 -- x_msg_data VARCHAR2(2000)
864 -- x_adjustment_id NUMBER
865 -- x_adjustment_number VARCHAR2
866 --
867 -- Version : Current version 2.0
868 -- IN parameters consolidated into new record type
869 -- Initial version 1.0
870 --
871 -- Notes : AutoAccounting used for both debits and credits
872 --
873 -----------------------------------------------------------------------
874 PROCEDURE Transfer_Sales_Credits
875 ( p_api_version IN NUMBER
876 ,p_init_msg_list IN VARCHAR2
877 ,p_commit IN VARCHAR2
878 ,p_validation_level IN NUMBER
879 ,x_return_status OUT NOCOPY VARCHAR2
880 ,x_msg_count OUT NOCOPY NUMBER
881 ,x_msg_data OUT NOCOPY VARCHAR2
882 ,p_rev_adj_rec IN Rev_Adj_Rec_Type
883 ,x_adjustment_id OUT NOCOPY NUMBER
884 ,x_adjustment_number OUT NOCOPY VARCHAR2)
885 IS
886 l_api_name CONSTANT VARCHAR2(30) := 'Transfer_Sales_Credits';
887 l_api_version CONSTANT NUMBER := 2.0;
888 l_rev_adj_rec AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type;
889
890 l_trx_total NUMBER := 0;
891 l_line_count NUMBER := 0;
892 l_salesrep_count NUMBER := 0;
893 l_nr_salesrep_count NUMBER := 0;
894 l_no_of_lines NUMBER := 0;
895 l_no_of_salesreps NUMBER := 0;
896 l_nr_no_of_salesreps NUMBER := 0;
897 l_total_adjusted NUMBER := 0;
898 l_line_adjusted NUMBER := 0;
899 l_line_adjusted_acctd NUMBER := 0;
900 l_line_percent_adjusted NUMBER := 0;
901 l_nr_line_adjusted NUMBER := 0;
902 l_nr_line_pct_adjusted NUMBER := 0;
903 l_nonrev_trx_total NUMBER := 0;
904 l_nonrev_line_count NUMBER := 0;
905 l_no_of_nonrev_lines NUMBER := 0;
906 l_total_nonrev_adjusted NUMBER := 0;
907 l_adj_trx_total NUMBER := 0;
908 l_line_rsc_total NUMBER := 0;
909 l_line_rsc_amount NUMBER := 0;
910 l_line_revenue_total NUMBER := 0;
911 l_cm_line_revenue_total NUMBER := 0;
912 l_net_line_revenue_total NUMBER := 0;
913 l_line_nonrev_total NUMBER := 0;
914 l_line_adjustable NUMBER := 0;
915 l_adjustable_amount NUMBER := 0;
916 l_trx_line_salesrep_id NUMBER;
917 l_revenue_amount_split NUMBER := 0;
918 l_revenue_percent_split NUMBER := 0;
919 l_revenue_amount NUMBER := 0;
920 l_line_amount NUMBER := 0;
921 l_line_amount_acctd NUMBER := 0;
922 l_line_percent NUMBER := 0;
923 l_line_nonrev_amount NUMBER := 0;
924 l_line_nonrev_percent NUMBER := 0;
925 l_nonrev_amount_split NUMBER := 0;
926 l_nonrev_percent_split NUMBER := 0;
927 l_nonrev_amount NUMBER := 0;
928 l_amount_prorata NUMBER := 0;
929 l_amount_prorata_acctd NUMBER := 0;
930 l_percent_prorata NUMBER := 0;
931 l_nonrev_amount_prorata NUMBER := 0;
932 l_nonrev_percent_prorata NUMBER := 0;
933
937 l_nonrev_percent_split_new NUMBER := 0;
934 l_revenue_amount_split_new NUMBER := 0;
935 l_revenue_percent_split_new NUMBER := 0;
936 l_nonrev_amount_split_new NUMBER := 0;
938 l_transferable_amount NUMBER := 0;
939 l_cust_trx_line_id NUMBER;
940 l_line_number NUMBER;
941 l_last_revenue_dist_id NUMBER := 0;
942 l_next_rev_dist_id NUMBER := 0;
943 l_credit_ccid NUMBER := 0;
944 l_debit_ccid NUMBER := 0;
945 l_concat_segments VARCHAR2(2000);
946 l_fail_count NUMBER := 0;
947 l_dist_acctd_amount NUMBER := 0;
948 l_debit_sales_credit_id NUMBER;
949 l_credit_sales_credit_id NUMBER;
950 l_total_sc_percent NUMBER;
951 l_neg_sc_limit NUMBER;
952 l_new_salesrep_name ra_salesreps.name%TYPE;
953 l_max_percent NUMBER;
954
955 l_warehouse_id NUMBER; -- Bug 1930302.
956 l_gl_date_valid DATE; -- Bug 2146970
957 l_last_salesrep_flag VARCHAR2(1); -- Bug 2477881
958
959 invalid_sc_total EXCEPTION;
960 -- invalid_percent EXCEPTION;
961 invalid_amount EXCEPTION;
962 invalid_zero EXCEPTION;
963 invalid_ccid EXCEPTION;
964
965 CURSOR c_trx_total IS
966 SELECT SUM(NVL(s.revenue_amount_split,0))
967 FROM ra_cust_trx_line_salesreps s
968 ,mtl_item_categories mic
969 ,ra_customer_trx_lines l
970 WHERE s.customer_trx_line_id = l.customer_trx_line_id
971 AND l.line_type = 'LINE'
972 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
973 AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
974 l.customer_trx_line_id)
975 AND NVL(l.inventory_item_id,0) =
976 NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
977 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
978 AND l.inventory_item_id = mic.inventory_item_id(+)
979 AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
980 DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
981 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
982 AND EXISTS
983 (SELECT s1.salesrep_id
984 FROM ra_cust_trx_line_salesreps s1
985 WHERE s1.customer_trx_line_id = l.customer_trx_line_id
986 AND s1.salesrep_id =
987 NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s1.salesrep_id)
988 AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s1.non_revenue_salesgroup_id, -9999), NVL(s1.revenue_salesgroup_id, -9999)) =
989 NVL(AR_RAAPI_UTIL.g_from_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s1.non_revenue_salesgroup_id, -9999), NVL(s1.revenue_salesgroup_id, -9999)))
990 GROUP BY s1.salesrep_id
991 HAVING DECODE(p_rev_adj_rec.sales_credit_type,'N',
992 SUM(NVL(s1.non_revenue_percent_split,0)),
993 SUM(NVL(s1.revenue_percent_split,0))) <> 0);
994
995 CURSOR c_rev_total IS
996 SELECT NVL(SUM(d.amount),0)
997 FROM ra_cust_trx_line_gl_dist d
998 ,mtl_item_categories mic
999 ,ra_customer_trx_lines l
1000 WHERE d.customer_trx_line_id = l.customer_trx_line_id
1001 AND d.account_class = 'REV'
1002 AND l.line_type = 'LINE'
1003 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
1004 AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
1005 l.customer_trx_line_id)
1006 AND NVL(l.inventory_item_id,0) =
1007 NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
1008 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
1009 AND l.inventory_item_id = mic.inventory_item_id(+)
1010 AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
1011 DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
1012 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
1013 AND EXISTS
1014 (SELECT s.salesrep_id
1015 FROM ra_cust_trx_line_salesreps s
1016 WHERE s.customer_trx_line_id = l.customer_trx_line_id
1017 AND s.salesrep_id =
1018 NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s.salesrep_id)
1019 AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
1020 NVL(AR_RAAPI_UTIL.g_from_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
1021 GROUP BY s.salesrep_id
1022 HAVING DECODE(p_rev_adj_rec.sales_credit_type,'N',
1023 SUM(NVL(s.non_revenue_percent_split,0)),
1024 SUM(NVL(s.revenue_percent_split,0))) <> 0);
1025
1026 CURSOR c_nonrev_trx_total IS
1027 SELECT SUM(NVL(s.non_revenue_amount_split,0))
1028 FROM ra_customer_trx_lines l
1029 ,mtl_item_categories mic
1030 ,ra_cust_trx_line_salesreps s
1031 WHERE l.customer_trx_line_id = s.customer_trx_line_id
1032 AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
1033 l.customer_trx_line_id)
1034 AND NVL(l.inventory_item_id,0) =
1038 AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
1035 NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
1036 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
1037 AND l.inventory_item_id = mic.inventory_item_id(+)
1039 DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
1040 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
1041 AND NVL(s.non_revenue_percent_split,0) <> 0
1042 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
1043 AND s.salesrep_id = NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s.salesrep_id)
1044 AND NVL(s.non_revenue_salesgroup_id,-9999) = NVL(AR_RAAPI_UTIL.g_from_salesgroup_id,NVL(s.non_revenue_salesgroup_id,-9999));
1045
1046 CURSOR c_nonrev_line_count IS
1047 SELECT COUNT(*)
1048 FROM mtl_item_categories mic
1049 ,ra_customer_trx_lines l
1050 WHERE l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
1051 l.customer_trx_line_id)
1052 AND NVL(l.inventory_item_id,0) =
1053 NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
1054 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
1055 AND l.inventory_item_id = mic.inventory_item_id(+)
1056 AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
1057 DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
1058 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
1059 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
1060 AND EXISTS
1061 (SELECT s.salesrep_id
1062 FROM ra_cust_trx_line_salesreps s
1063 WHERE s.customer_trx_line_id = l.customer_trx_line_id
1064 AND NVL(s.non_revenue_percent_split,0) <> 0
1065 AND s.salesrep_id =
1066 NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s.salesrep_id)
1067 AND NVL(s.non_revenue_salesgroup_id,-9999) =
1068 NVL(AR_RAAPI_UTIL.g_from_salesgroup_id,NVL(s.non_revenue_salesgroup_id,-9999))
1069 GROUP BY s.customer_trx_line_id
1070 HAVING SUM(NVL(s.non_revenue_percent_split,0)) <> 0);
1071
1072 CURSOR c_line IS
1073 SELECT SUM (s.revenue_amount_split) amount
1074 ,l.customer_trx_line_id
1075 ,l.accounting_rule_id
1076 ,l.accounting_rule_duration
1077 ,l.inventory_item_id
1078 ,l.memo_line_id
1079 ,l.warehouse_id
1080 ,l.line_number
1081 FROM ra_cust_trx_line_salesreps s
1082 ,mtl_item_categories mic
1083 ,ra_customer_trx_lines l
1084 WHERE l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
1085 AND l.customer_trx_line_id = s.customer_trx_line_id
1086 AND l.customer_trx_line_id =
1087 NVL (AR_RAAPI_UTIL.g_from_cust_trx_line_id, l.customer_trx_line_id)
1088 AND NVL(l.inventory_item_id,0) =
1089 NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
1090 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
1091 AND l.inventory_item_id = mic.inventory_item_id(+)
1092 AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
1093 DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
1094 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
1095 AND l.line_type = 'LINE'
1096 AND EXISTS
1097 (SELECT s1.salesrep_id
1098 FROM ra_cust_trx_line_salesreps s1
1099 WHERE s1.customer_trx_line_id = l.customer_trx_line_id
1100 AND s1.salesrep_id =
1101 NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s1.salesrep_id)
1102 AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s1.non_revenue_salesgroup_id, -9999), NVL(s1.revenue_salesgroup_id, -9999)) =
1103 NVL(AR_RAAPI_UTIL.g_from_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s1.non_revenue_salesgroup_id, -9999), NVL(s1.revenue_salesgroup_id, -9999)))
1104 GROUP BY s1.salesrep_id
1105 HAVING DECODE(p_rev_adj_rec.sales_credit_type,'N',
1106 SUM(NVL(s1.non_revenue_percent_split,0)),
1107 SUM(NVL(s1.revenue_percent_split,0))) <> 0)
1108 GROUP BY l.customer_trx_line_id
1109 ,l.accounting_rule_id
1110 ,l.accounting_rule_duration
1111 ,l.inventory_item_id
1112 ,l.memo_line_id
1113 ,l.warehouse_id
1114 /* Bug 2130207 - changed from l_warehouse_id */
1115 ,l.line_number
1116 HAVING SUM(s.revenue_amount_split) <> 0;
1117
1118 CURSOR c_line_rsc_amount IS
1119 SELECT NVL(SUM(revenue_amount_split),0)
1120 FROM ra_cust_trx_line_salesreps
1121 WHERE customer_trx_line_id = l_cust_trx_line_id
1122 and NVL(revenue_percent_split,0) <> 0
1123 AND customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
1124 and salesrep_id = NVL(AR_RAAPI_UTIL.g_from_salesrep_id,salesrep_id)
1125 and NVL(revenue_salesgroup_id, -9999) = NVL(AR_RAAPI_UTIL.g_from_salesgroup_id,NVL(revenue_salesgroup_id, -9999));
1126
1127 -- Bug 3431815: Credit Memos included
1128 -- Bug 3536944: cursor split into separate queries for invoice and CMs
1129 -- Bug 3676923: cater for null amounts and removed check on autorule
1130 -- complete flag for credit memos
1131
1132 CURSOR c_line_revenue_total IS
1133 SELECT NVL(SUM(d.amount),0)
1134 FROM ra_cust_trx_line_gl_dist d
1138 AND d.account_class = 'REV';
1135 WHERE d.customer_trx_line_id = l_cust_trx_line_id
1136 AND d.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
1137 AND d.account_set_flag = 'N'
1139
1140 CURSOR c_cm_line_revenue_total IS
1141 SELECT NVL(SUM(d.amount),0)
1142 FROM ra_cust_trx_line_gl_dist d,
1143 ra_customer_trx_lines l
1144 WHERE d.customer_trx_line_id = l.customer_trx_line_id
1145 AND d.customer_trx_id = l.customer_trx_id
1146 AND l.previous_customer_trx_line_id = l_cust_trx_line_id
1147 AND l.previous_customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
1148 AND d.account_set_flag = 'N'
1149 AND d.account_class = 'REV';
1150
1151 CURSOR c_line_nonrev_total IS
1152 SELECT SUM(NVL(s.non_revenue_amount_split,0))
1153 FROM ra_cust_trx_line_salesreps s
1154 WHERE s.customer_trx_line_id = l_cust_trx_line_id
1155 AND s.salesrep_id = NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s.salesrep_id)
1156 and NVL(s.non_revenue_salesgroup_id, -9999) = NVL(AR_RAAPI_UTIL.g_from_salesgroup_id,NVL(s.non_revenue_salesgroup_id, -9999));
1157
1158 CURSOR get_salesrep_lines_old is
1159 SELECT SUM(NVL(s.revenue_amount_split,0)) revenue_amount_split,
1160 SUM(NVL(s.revenue_percent_split,0)) revenue_percent_split,
1161 SUM(NVL(s.non_revenue_amount_split,0)) nonrev_amount_split,
1162 SUM(NVL(s.non_revenue_percent_split,0)) nonrev_percent_split
1163 FROM ra_cust_trx_line_salesreps s,
1164 ra_customer_trx_lines l
1165 WHERE s.customer_trx_line_id = l_cust_trx_line_id
1166 AND s.salesrep_id = NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s.salesrep_id)
1167 AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
1168 NVL(AR_RAAPI_UTIL.g_from_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
1169 AND l.line_type = 'LINE'
1170 AND l.customer_trx_line_id = s.customer_trx_line_id
1171 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id;
1172
1173 CURSOR get_salesrep_lines_new is
1174 SELECT NVL(SUM(s.revenue_amount_split),0),
1175 NVL(SUM(s.revenue_percent_split),0),
1176 NVL(SUM(s.non_revenue_amount_split),0),
1177 NVL(SUM(s.non_revenue_percent_split),0)
1178 FROM ra_cust_trx_line_salesreps s,
1179 ra_customer_trx_lines l
1180 WHERE s.customer_trx_line_id = l_cust_trx_line_id
1181 AND s.salesrep_id = AR_RAAPI_UTIL.g_to_salesrep_id
1182 AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
1183 NVL(AR_RAAPI_UTIL.g_to_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
1184 AND l.line_type = 'LINE'
1185 AND l.customer_trx_line_id = s.customer_trx_line_id
1186 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id;
1187
1188 CURSOR c_get_from_salesreps IS
1189 SELECT s.salesrep_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', s.non_revenue_salesgroup_id, s.revenue_salesgroup_id) salesgroup_id,
1190 SUM(s.revenue_amount_split) revenue_amount_split,
1191 SUM(s.revenue_percent_split) revenue_percent_split,
1192 SUM(s.non_revenue_amount_split) nonrev_amount_split,
1193 SUM(s.non_revenue_percent_split) nonrev_percent_split
1194 FROM ra_cust_trx_line_salesreps s
1195 WHERE s.customer_trx_line_id = l_cust_trx_line_id
1196 AND s.salesrep_id = NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s.salesrep_id)
1197 AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
1198 NVL(AR_RAAPI_UTIL.g_from_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
1199 AND NVL(s.revenue_adjustment_id,-99) <> x_adjustment_id -- bug 2543675
1200 GROUP BY s.salesrep_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', s.non_revenue_salesgroup_id,s.revenue_salesgroup_id);
1201
1202 CURSOR c_new_salesrep_name IS
1203 SELECT name
1204 FROM ra_salesreps
1205 WHERE salesrep_id = AR_RAAPI_UTIL.g_to_salesrep_id;
1206
1207 BEGIN
1208 IF PG_DEBUG in ('Y', 'C') THEN
1209 arp_util.debug('AR_Revenue_Adjustment_PVT.Transfer_Sales_Credits()+');
1210 END IF;
1211 -- Standard Start of API savepoint
1212 SAVEPOINT Transfer_Sales_Credits_PVT;
1213 -- Standard call to check for call compatibility.
1214 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1215 p_api_version ,
1216 l_api_name ,
1217 G_PKG_NAME )
1218 THEN
1219 IF PG_DEBUG in ('Y', 'C') THEN
1220 arp_util.debug('Transfer_Sales_Credits: ' || 'Unexpected error '||sqlerrm||
1221 ' at AR_Revenue_Adjustment_PVT.Transfer_Sales_Credits()+');
1222 END IF;
1223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1224 END IF;
1225 -- Initialize message list if p_init_msg_list is set to TRUE.
1226 IF FND_API.to_Boolean( p_init_msg_list )
1227 THEN
1228 FND_MSG_PUB.initialize;
1229 END IF;
1230 -- Initialize API return status to success
1231 x_return_status := FND_API.G_RET_STS_SUCCESS;
1232
1233 l_rev_adj_rec := p_rev_adj_rec;
1234
1235 /*Bug 6731185 JVARKEY Making sure GL date has no timestamp*/
1236 l_rev_adj_rec.gl_date := trunc(p_rev_adj_rec.gl_date);
1237
1241 AR_RAAPI_UTIL.Initialize_Globals;
1238 l_rev_adj_rec.adjustment_type := 'SA';
1239
1240 AR_RAAPI_UTIL.Constant_System_Values;
1242 AR_RAAPI_UTIL.Validate_Parameters (p_init_msg_list => FND_API.G_FALSE
1243 ,p_rev_adj_rec => l_rev_adj_rec
1244 ,p_validation_level => p_validation_level
1245 ,x_return_status => x_return_status
1246 ,x_msg_count => x_msg_count
1247 ,x_msg_data => x_msg_data);
1248 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1249 THEN
1250 RAISE FND_API.G_EXC_ERROR;
1251 END IF;
1252
1253 --
1254 -- Inner PL/SQL Block to optimize error handling
1255 --
1256 BEGIN
1257 IF PG_DEBUG in ('Y', 'C') THEN
1258 arp_util.debug('AR_Revenue_Adjustment_PVT.Transfer_Sales_Credits(2)+');
1259 END IF;
1260
1261 l_revenue_amount := 0;
1262 l_nonrev_amount := 0;
1263 l_adj_trx_total := 0;
1264 l_line_count := 0;
1265 l_nonrev_line_count := 0;
1266 l_salesrep_count := 0;
1267 l_nr_salesrep_count := 0;
1268 l_total_adjusted := 0;
1269 l_total_nonrev_adjusted := 0;
1270 l_line_adjusted := 0;
1271 l_line_adjusted_acctd := 0; /* Bug 2143925 */
1272 l_line_percent_adjusted := 0;
1273 l_nr_line_adjusted :=0;
1274 l_nr_line_pct_adjusted :=0;
1275
1276 OPEN c_trx_total;
1277 FETCH c_trx_total INTO l_trx_total;
1278 CLOSE c_trx_total;
1279
1280 OPEN c_new_salesrep_name;
1281 FETCH c_new_salesrep_name INTO l_new_salesrep_name;
1282 CLOSE c_new_salesrep_name;
1283
1284 --
1285 -- Determine revenue amount to be transferred
1286 --
1287 IF p_rev_adj_rec.sales_credit_type IN ('R','B')
1288 THEN
1289 AR_RAAPI_UTIL.Validate_Amount
1290 (p_init_msg_list => FND_API.G_FALSE
1291 ,p_customer_trx_line_id => AR_RAAPI_UTIL.g_from_cust_trx_line_id
1292 ,p_adjustment_type => 'SA'
1293 ,p_amount_mode => p_rev_adj_rec.amount_mode
1294 ,p_customer_trx_id => AR_RAAPI_UTIL.g_customer_trx_id
1295 ,p_salesrep_id => AR_RAAPI_UTIL.g_from_salesrep_id
1296 ,p_salesgroup_id => AR_RAAPI_UTIL.g_from_salesgroup_id
1297 ,p_sales_credit_type => p_rev_adj_rec.sales_credit_type
1298 ,p_item_id => AR_RAAPI_UTIL.g_from_inventory_item_id
1299 ,p_category_id => AR_RAAPI_UTIL.g_from_category_id
1300 ,p_revenue_amount_in => p_rev_adj_rec.amount
1301 ,p_revenue_percent => p_rev_adj_rec.percent
1302 ,p_revenue_amount_out => l_revenue_amount
1303 ,p_adjustable_amount_out => l_adj_trx_total
1304 ,p_line_count_out => l_no_of_lines
1305 ,x_return_status => x_return_status
1306 ,x_msg_count => x_msg_count
1307 ,x_msg_data => x_msg_data);
1308 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1309 THEN
1310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1311 ELSIF x_return_status = FND_API.G_RET_STS_ERROR
1312 THEN
1313 RAISE FND_API.G_EXC_ERROR;
1314 END IF;
1315 l_rev_adj_rec.amount := l_revenue_amount;
1316 /* Bug 2146970 - validate the GL date passed in */
1317 l_gl_date_valid := AR_RAAPI_UTIL.bump_gl_date_if_closed
1318 (p_gl_date => l_rev_adj_rec.gl_date);
1319 IF l_gl_date_valid IS NULL
1320 THEN
1321 FND_MESSAGE.set_name('AR','AR_VAL_GL_DATE');
1322 FND_MSG_PUB.Add;
1323 RAISE FND_API.G_EXC_ERROR;
1324 END IF;
1325 END IF;
1326 --
1327 -- Determine non revenue amount to be transferred
1328 --
1329 IF p_rev_adj_rec.sales_credit_type in ('N','B')
1330 THEN
1331 OPEN c_nonrev_trx_total;
1332 FETCH c_nonrev_trx_total INTO l_nonrev_trx_total;
1333 CLOSE c_nonrev_trx_total;
1334 OPEN c_nonrev_line_count;
1335 FETCH c_nonrev_line_count INTO l_no_of_nonrev_lines;
1336 CLOSE c_nonrev_line_count;
1337 IF p_rev_adj_rec.amount_mode = 'A'
1338 THEN
1339 IF (p_rev_adj_rec.amount > 0 AND
1340 p_rev_adj_rec.amount > l_nonrev_trx_total) OR
1341 (p_rev_adj_rec.amount < 0 AND
1342 p_rev_adj_rec.amount < l_nonrev_trx_total)
1343 THEN
1344 RAISE invalid_amount;
1345 ELSE
1346 l_nonrev_amount := p_rev_adj_rec.amount;
1347 END IF;
1348 ELSIF p_rev_adj_rec.amount_mode = 'P'
1349 THEN
1350 l_nonrev_amount := ROUND(l_trx_total * p_rev_adj_rec.percent
1351 / 100, AR_RAAPI_UTIL.g_trx_precision);
1352 ELSE
1353 l_nonrev_amount := l_nonrev_trx_total;
1354 END IF;
1355
1356 IF l_nonrev_amount = 0
1357 THEN
1358 RAISE invalid_zero;
1359 END IF;
1360
1361 IF (l_nonrev_amount > 0 AND
1362 l_nonrev_amount > l_nonrev_trx_total) OR
1363 (l_nonrev_amount < 0 AND
1364 l_nonrev_amount < l_nonrev_trx_total)
1365 THEN
1366 RAISE invalid_amount;
1367 END IF;
1368 IF p_rev_adj_rec.sales_credit_type = 'N'
1369 THEN
1373
1370 l_rev_adj_rec.amount := l_nonrev_amount;
1371 END IF;
1372 END IF;
1374 --
1375 -- Create revenue adjustment record
1376 --
1377 create_adjustment
1378 (p_rev_adj_rec => l_rev_adj_rec
1379 ,x_adjustment_id => x_adjustment_id
1380 ,x_adjustment_number => x_adjustment_number);
1381
1382 --
1383 -- Now transfer the amount on each line pro rata
1384 --
1385 FOR c1 IN c_line LOOP
1386
1387 l_cust_trx_line_id := c1.customer_trx_line_id;
1388 l_line_number := c1.line_number;
1389 l_line_amount := 0;
1390 l_line_amount_acctd := 0; /* Bug 2143925 */
1391 l_line_percent := 0;
1392 l_salesrep_count := 0;
1393 l_nr_salesrep_count := 0;
1394 l_line_nonrev_amount := 0;
1395 l_line_nonrev_percent := 0;
1396
1397 -- Bug 3536944: first the revenue on the invoice line is retrieved,
1398 -- then the revenue for any credit memo lines is added to get net amount
1399 OPEN c_line_revenue_total;
1400 FETCH c_line_revenue_total INTO l_line_revenue_total;
1401 CLOSE c_line_revenue_total;
1402
1403 OPEN c_cm_line_revenue_total;
1404 FETCH c_cm_line_revenue_total INTO l_cm_line_revenue_total;
1405 CLOSE c_cm_line_revenue_total;
1406
1407 l_net_line_revenue_total := l_line_revenue_total + l_cm_line_revenue_total;
1408
1409 OPEN get_salesrep_lines_old;
1410 FETCH get_salesrep_lines_old INTO l_revenue_amount_split,
1411 l_revenue_percent_split,
1412 l_nonrev_amount_split,
1413 l_nonrev_percent_split;
1414 CLOSE get_salesrep_lines_old;
1415
1416 OPEN get_salesrep_lines_new;
1417 FETCH get_salesrep_lines_new INTO l_revenue_amount_split_new,
1418 l_revenue_percent_split_new,
1419 l_nonrev_amount_split_new,
1420 l_nonrev_percent_split_new;
1421 CLOSE get_salesrep_lines_new;
1422
1423 IF p_rev_adj_rec.sales_credit_type IN ('R','B') AND
1424 l_revenue_percent_split <> 0
1425 THEN
1426 IF l_net_line_revenue_total > 0
1427 THEN
1428 l_line_adjustable :=
1429 LEAST(l_net_line_revenue_total,l_revenue_amount_split);
1430 ELSIF l_net_line_revenue_total < 0
1431 THEN
1432 l_line_adjustable :=
1433 GREATEST(l_net_line_revenue_total,l_revenue_amount_split);
1434 ELSE
1435 l_line_adjustable := 0;
1436 END IF;
1437 l_line_amount := ROUND(l_revenue_amount * l_line_adjustable
1438 / l_adj_trx_total , AR_RAAPI_UTIL.g_trx_precision);
1439 IF l_line_amount <> 0
1440 THEN
1441 l_line_count := l_line_count + 1;
1442 l_total_adjusted := l_total_adjusted + l_line_amount;
1443 IF l_line_count = l_no_of_lines AND
1444 l_total_adjusted <> l_revenue_amount
1445 THEN
1446 l_line_amount := l_line_amount + l_revenue_amount
1447 - l_total_adjusted;
1448 END IF;
1449 END IF;
1450 /* Bug 2143925 - get the line amount in SOB currency */
1451 l_line_amount_acctd :=
1452 /* Bug 4675438: MOAC/SSA */
1453 ARPCURR.functional_amount(
1454 amount => l_line_amount
1455 , currency_code => arp_global.functional_currency
1456 , exchange_rate => AR_RAAPI_UTIL.g_exchange_rate
1457 , precision => NULL
1458 , min_acc_unit => NULL );
1459 l_line_percent := ROUND(l_line_amount / c1.amount * 100, 4);
1460 END IF;
1461 IF p_rev_adj_rec.sales_credit_type IN ('N','B') AND
1462 l_nonrev_percent_split <> 0
1463 THEN
1464 OPEN c_line_nonrev_total;
1465 FETCH c_line_nonrev_total INTO l_line_nonrev_total;
1466 CLOSE c_line_nonrev_total;
1467 l_line_nonrev_amount := ROUND(l_nonrev_amount * l_line_nonrev_total
1468 / l_nonrev_trx_total, AR_RAAPI_UTIL.g_trx_precision);
1469 IF l_line_nonrev_amount <> 0
1470 THEN
1471 l_nonrev_line_count := l_nonrev_line_count + 1;
1472 l_total_nonrev_adjusted := l_total_nonrev_adjusted +
1473 l_line_nonrev_amount;
1474 IF l_nonrev_line_count = l_no_of_nonrev_lines AND
1475 l_total_nonrev_adjusted <> l_nonrev_amount
1476 THEN
1477 l_line_nonrev_amount := l_line_nonrev_amount +
1478 l_nonrev_amount - l_total_nonrev_adjusted;
1479 END IF;
1480 IF p_rev_adj_rec.amount_mode = 'P'
1481 THEN
1482 l_line_nonrev_percent := p_rev_adj_rec.percent;
1483 ELSIF p_rev_adj_rec.amount_mode = 'A'
1484 THEN
1485 l_line_nonrev_percent := ROUND(l_line_nonrev_amount
1486 / c1.amount * 100, 4);
1487 ELSE -- amount mode = 'T'
1488 l_line_nonrev_percent := l_nonrev_percent_split;
1489 END IF;
1490 END IF;
1491 END IF;
1492
1496 However, percent is not always going to equal 100 due
1493 /* 7365097 - This validation was raising an improperly
1494 handled exception in cases where the percent did not
1495 exactly equal 100. In internal case, it was 100.0013.
1497 to obscure rounding corrections, so such minor differences
1498 should be allowed to process successfully. */
1499 IF p_rev_adj_rec.sales_credit_type in ('R','B') AND l_line_amount <> 0
1500 AND l_revenue_percent_split <> 0 AND
1501 (l_line_percent + l_revenue_percent_split_new > 100 OR
1502 l_line_percent + l_revenue_percent_split_new < -100)
1503 THEN
1504 IF PG_DEBUG in ('Y', 'C') THEN
1505 arp_util.debug('pseudo-EXCEPTION: Percent <> 100');
1506 arp_util.debug(' l_line_amount = ' || l_line_amount);
1507 arp_util.debug(' l_revenue_percent_split = ' ||
1508 l_revenue_percent_split);
1509 arp_util.debug(' l_line_percent = ' || l_line_percent);
1510 arp_util.debug(' l_revenue_percent_split_new = ' ||
1511 l_revenue_percent_split_new);
1512 END IF;
1513 -- RAISE invalid_percent;
1514 END IF;
1515
1516 --
1517 -- Check if total rev/non rev percent on new salesrep exceeds limit
1518 --
1519 l_total_sc_percent := l_line_percent + l_revenue_percent_split_new +
1520 l_line_nonrev_percent + l_nonrev_percent_split_new;
1521 IF l_total_sc_percent < 0
1522 THEN
1523 IF arp_global.sysparam.sales_credit_pct_limit IS NULL
1524 THEN
1525 l_neg_sc_limit := NULL;
1526 ELSE
1527 l_neg_sc_limit := arp_global.sysparam.sales_credit_pct_limit * -1;
1528 END IF;
1529 END IF;
1530 IF (l_total_sc_percent > 0 AND l_total_sc_percent >
1531 NVL(arp_global.sysparam.sales_credit_pct_limit,l_total_sc_percent)) OR
1532 (l_total_sc_percent < 0 and l_total_sc_percent <
1533 NVL(l_neg_sc_limit,l_total_sc_percent))
1534 THEN
1535 RAISE invalid_sc_total;
1536 END IF;
1537
1538 l_no_of_salesreps := 0;
1539 l_nr_no_of_salesreps := 0;
1540
1541 --
1542 -- We loop thru the from salesreps twice, firstly to find the number of
1543 -- valid salesreps, and secondly to process the actual transfer
1544 --
1545 FOR srrec in c_get_from_salesreps LOOP
1546 IF l_line_amount <> 0 AND
1547 p_rev_adj_rec.sales_credit_type IN ('R','B') AND
1548 l_revenue_percent_split <> 0
1549 THEN
1550 l_amount_prorata := ROUND(l_line_amount * srrec.revenue_percent_split
1551 / l_revenue_percent_split, AR_RAAPI_UTIL.g_trx_precision);
1552 IF l_amount_prorata <> 0
1553 THEN
1554 l_no_of_salesreps := l_no_of_salesreps + 1;
1555 END IF;
1556 END IF;
1557 IF p_rev_adj_rec.sales_credit_type in ('N','B') AND
1558 l_line_nonrev_amount <> 0 AND l_nonrev_percent_split <> 0
1559 THEN
1560 l_nonrev_amount_prorata := ROUND(l_line_nonrev_amount *
1561 srrec.nonrev_percent_split / l_nonrev_percent_split,
1562 AR_RAAPI_UTIL.g_trx_precision);
1563 IF l_nonrev_amount_prorata <> 0
1564 THEN
1565 l_nr_no_of_salesreps := l_nr_no_of_salesreps + 1;
1566 END IF;
1567 END IF;
1568 END LOOP; -- c_get_from_salesreps loop(1)
1569
1570 /* Bug 2543675 - insert target sales credit and get the credit ccid
1571 before debiting the source sales reps */
1572 IF l_line_amount <> 0 AND
1573 p_rev_adj_rec.sales_credit_type IN ('R','B') AND
1574 l_revenue_percent_split <> 0
1575 THEN
1576 insert_sales_credit( AR_RAAPI_UTIL.g_customer_trx_id,
1577 AR_RAAPI_UTIL.g_to_salesrep_id,
1578 AR_RAAPI_UTIL.g_to_salesgroup_id,
1579 l_cust_trx_line_id,
1580 l_line_amount,
1581 l_line_percent,
1582 'R',
1583 l_credit_sales_credit_id,
1584 x_adjustment_id,
1585 l_rev_adj_rec.gl_date);
1586
1587 --
1588 -- Initiate auto accounting procedure to find ccid to credit
1589 --
1590 -- Bug 1930302 : Added warehouse_id as 16th parameter.
1591
1592 ARP_AUTO_ACCOUNTING.do_autoaccounting('G'
1593 ,'REV'
1594 ,AR_RAAPI_UTIL.g_customer_trx_id
1595 ,l_cust_trx_line_id
1596 ,NULL
1597 ,NULL
1598 ,l_rev_adj_rec.gl_date
1599 ,NULL
1600 ,l_line_amount
1601 ,NULL
1602 ,NULL
1603 ,AR_RAAPI_UTIL.g_cust_trx_type_id
1604 ,AR_RAAPI_UTIL.g_to_salesrep_id
1608 ,l_credit_ccid
1605 ,c1.inventory_item_id
1606 ,c1.memo_line_id
1607 ,c1.warehouse_id
1609 ,l_concat_segments
1610 ,l_fail_count);
1611 IF l_credit_ccid IS NULL
1612 THEN
1613 l_credit_ccid := FND_FLEX_EXT.GET_CCID
1614 ('SQLGL'
1615 ,'GL#'
1616 ,arp_global.chart_of_accounts_id
1617 ,TO_CHAR(l_rev_adj_rec.gl_date,'DD-MON-YYYY')
1618 ,l_concat_segments);
1619 END IF;
1620
1621 IF l_credit_ccid = -1 OR
1622 l_credit_ccid = 0 OR
1623 l_fail_count > 0
1624 THEN
1625 RAISE invalid_ccid;
1626 END IF;
1627
1628 END IF;
1629
1630 FOR c2 IN c_get_from_salesreps LOOP
1631
1632 IF l_line_amount <> 0 AND
1633 p_rev_adj_rec.sales_credit_type IN ('R','B') AND
1634 l_revenue_percent_split <> 0
1635 THEN
1636 /* Bug 6932079 - Added the NVL for c2.revenue_percent_split */
1637 l_amount_prorata := ROUND(l_line_amount * NVL(c2.revenue_percent_split, 0)
1638 / l_revenue_percent_split, AR_RAAPI_UTIL.g_trx_precision);
1639 /* Bug 2143925 - get the pro rata amount in SOB currency */
1640 /* Bug 4675438: MOAC/SSA */
1641 l_amount_prorata_acctd :=
1642 ARPCURR.functional_amount(
1643 amount => l_amount_prorata
1644 , currency_code => arp_global.functional_currency
1645 , exchange_rate => AR_RAAPI_UTIL.g_exchange_rate
1646 , precision => NULL
1647 , min_acc_unit => NULL );
1648 l_percent_prorata :=
1649 ROUND((l_amount_prorata / c1.amount) * 100,4);
1650 /* Added these debug messages as part of bug 6932079 */
1651 IF PG_DEBUG in ('Y', 'C') THEN
1652 arp_util.debug('l_amount_prorata = ' || l_amount_prorata);
1653 arp_util.debug('l_percent_prorata = ' || l_percent_prorata);
1654 END IF;
1655
1656 IF l_amount_prorata <> 0
1657 THEN
1658 l_salesrep_count := l_salesrep_count + 1;
1659 l_line_adjusted := l_line_adjusted + l_amount_prorata;
1660 /* Bug 2143925 */
1661 l_line_adjusted_acctd := l_line_adjusted_acctd +
1662 l_amount_prorata_acctd;
1663 l_line_percent_adjusted := l_line_percent_adjusted +
1664 l_percent_prorata;
1665 IF l_salesrep_count = l_no_of_salesreps
1666 THEN
1667 l_last_salesrep_flag := 'Y'; -- Bug 2477881
1668 IF l_line_adjusted <> l_line_amount
1669 THEN
1670 l_amount_prorata := l_amount_prorata + l_line_amount
1671 - l_line_adjusted;
1672 END IF;
1673 /* Bug 2143925 */
1674 IF l_line_adjusted_acctd <> l_line_amount_acctd
1675 THEN
1676 l_amount_prorata_acctd := l_amount_prorata_acctd
1677 + l_line_amount_acctd - l_line_adjusted_acctd;
1678 END IF;
1679 IF l_line_percent_adjusted <> l_line_percent
1680 THEN
1681 l_percent_prorata := l_percent_prorata + l_line_percent
1682 - l_line_percent_adjusted;
1683 END IF;
1684 ELSE
1685 l_last_salesrep_flag := 'N'; -- Bug 2477881
1686 END IF;
1687 insert_sales_credit(AR_RAAPI_UTIL.g_customer_trx_id,
1688 c2.salesrep_id,
1689 c2.salesgroup_id,
1690 l_cust_trx_line_id,
1691 l_amount_prorata * -1,
1692 l_percent_prorata * -1,
1693 'R',
1694 l_debit_sales_credit_id,
1695 x_adjustment_id,
1696 l_rev_adj_rec.gl_date);
1697 --
1698 -- Initiate auto accounting procedure to find ccid to debit
1699 --
1700 -- Bug 1930302 : Added warehouse_id as 16th parameter.
1701
1702 ARP_AUTO_ACCOUNTING.do_autoaccounting
1703 ('G'
1704 ,'REV'
1705 ,AR_RAAPI_UTIL.g_customer_trx_id
1706 ,l_cust_trx_line_id
1707 ,NULL
1708 ,NULL
1709 ,l_rev_adj_rec.gl_date
1710 ,NULL
1711 ,l_amount_prorata
1712 ,NULL
1713 ,NULL
1714 ,AR_RAAPI_UTIL.g_cust_trx_type_id
1718 ,c1.warehouse_id
1715 ,c2.salesrep_id
1716 ,c1.inventory_item_id
1717 ,c1.memo_line_id
1719 ,l_debit_ccid
1720 ,l_concat_segments
1721 ,l_fail_count);
1722 IF l_debit_ccid IS NULL
1723 THEN
1724 l_debit_ccid :=
1725 FND_FLEX_EXT.GET_CCID
1726 ('SQLGL'
1727 ,'GL#'
1728 ,arp_global.chart_of_accounts_id
1729 ,TO_CHAR(l_rev_adj_rec.gl_date,'DD-MON-YYYY')
1730 ,l_concat_segments);
1731 END IF;
1732 IF l_debit_ccid = -1 OR
1733 l_debit_ccid = 0 OR
1734 l_fail_count > 0
1735 THEN
1736 RAISE invalid_ccid;
1737 END IF;
1738
1739 IF c1.accounting_rule_id IS NOT NULL
1740 THEN
1741 transfer_salesrep_revenue
1742 (c1.customer_trx_line_id
1743 ,AR_RAAPI_UTIL.g_customer_trx_id
1744 ,l_debit_sales_credit_id
1745 ,l_amount_prorata * -1
1746 ,l_amount_prorata_acctd * -1 -- Bug 2143925
1747 ,l_rev_adj_rec.gl_date
1748 ,l_debit_ccid
1749 ,l_last_salesrep_flag -- Bug 2477881
1750 ,l_line_amount * -1 -- Bug 2477881
1751 ,l_line_amount_acctd * -1 -- Bug 2477881
1752 ,x_adjustment_id);
1753 /* Bug 2543675 - insert 1 credit for every debit */
1754 transfer_salesrep_revenue
1755 (c1.customer_trx_line_id
1756 ,AR_RAAPI_UTIL.g_customer_trx_id
1757 ,l_credit_sales_credit_id
1758 ,l_amount_prorata
1759 ,l_amount_prorata_acctd
1760 ,l_rev_adj_rec.gl_date
1761 ,l_credit_ccid
1762 ,l_last_salesrep_flag -- Bug 2477881
1763 ,l_line_amount -- Bug 2477881
1764 ,l_line_amount_acctd -- Bug 2477881
1765 ,x_adjustment_id);
1766 ELSE
1767 insert_distribution(l_cust_trx_line_id,
1768 l_debit_ccid,
1769 l_percent_prorata * -1,
1770 l_amount_prorata_acctd * -1, -- Bug 2143925
1771 l_gl_date_valid, -- Bug 2146970
1772 l_rev_adj_rec.gl_date,
1773 'REV',
1774 l_amount_prorata * -1,
1775 l_debit_sales_credit_id,
1776 AR_RAAPI_UTIL.g_customer_trx_id,
1777 x_adjustment_id);
1778 /* Bug 2543675 - insert 1 credit for every debit */
1779 insert_distribution(l_cust_trx_line_id,
1780 l_credit_ccid,
1781 l_percent_prorata ,
1782 l_amount_prorata_acctd,
1783 l_gl_date_valid,
1784 l_rev_adj_rec.gl_date,
1785 'REV',
1786 l_amount_prorata,
1787 l_credit_sales_credit_id,
1788 AR_RAAPI_UTIL.g_customer_trx_id,
1789 x_adjustment_id);
1790
1791 END IF;
1792 END IF;
1793 END IF;
1794
1795 IF p_rev_adj_rec.sales_credit_type in ('N','B') AND
1796 l_line_nonrev_amount <> 0 AND l_nonrev_percent_split <> 0
1797 THEN
1798 l_nonrev_amount_prorata := ROUND(l_line_nonrev_amount *
1799 c2.nonrev_percent_split / l_nonrev_percent_split,
1800 AR_RAAPI_UTIL.g_trx_precision);
1801 l_nonrev_percent_prorata :=
1802 ROUND((l_nonrev_amount_prorata / c1.amount) * 100,4);
1803
1804 IF l_nonrev_percent_prorata <> 0
1805 THEN
1806 l_nr_salesrep_count := l_nr_salesrep_count + 1;
1807 l_nr_line_adjusted := l_nr_line_adjusted + l_nonrev_amount_prorata;
1808 l_nr_line_pct_adjusted := l_nr_line_pct_adjusted +
1809 l_nonrev_percent_prorata;
1810 IF l_nr_salesrep_count = l_nr_no_of_salesreps
1811 THEN
1812 IF l_nr_line_adjusted <> l_line_nonrev_amount
1813 THEN
1814 l_nonrev_amount_prorata := l_nonrev_amount_prorata
1815 + l_line_nonrev_amount - l_nr_line_adjusted;
1816 END IF;
1817 IF l_nr_line_pct_adjusted <> l_line_nonrev_percent
1818 THEN
1819 l_nonrev_percent_prorata := l_nonrev_percent_prorata +
1820 l_line_nonrev_percent - l_nr_line_pct_adjusted;
1821 END IF;
1822 END IF;
1823 insert_sales_credit(AR_RAAPI_UTIL.g_customer_trx_id,
1827 l_nonrev_amount_prorata * -1,
1824 c2.salesrep_id,
1825 c2.salesgroup_id,
1826 l_cust_trx_line_id,
1828 l_nonrev_percent_prorata * -1,
1829 'N',
1830 l_debit_sales_credit_id,
1831 x_adjustment_id,
1832 NULL);
1833 END IF;
1834 END IF;
1835
1836 END LOOP; -- c_get_from_salesreps loop (2)
1837
1838 l_line_adjusted := 0;
1839 l_line_adjusted_acctd := 0; -- Bug 2143925
1840 l_line_percent_adjusted := 0;
1841 l_nr_line_adjusted := 0;
1842 l_nr_line_pct_adjusted := 0;
1843
1844 IF p_rev_adj_rec.sales_credit_type in ('N','B') AND
1845 l_line_nonrev_amount <> 0 AND l_nonrev_percent_split <> 0
1846 THEN
1847 insert_sales_credit( AR_RAAPI_UTIL.g_customer_trx_id,
1848 AR_RAAPI_UTIL.g_to_salesrep_id,
1849 AR_RAAPI_UTIL.g_to_salesgroup_id,
1850 l_cust_trx_line_id,
1851 l_line_nonrev_amount,
1852 l_line_nonrev_percent,
1853 'N',
1854 l_credit_sales_credit_id,
1855 x_adjustment_id,
1856 NULL);
1857 END IF;
1858
1859 END LOOP; -- c_line loop
1860
1861 EXCEPTION
1862 WHEN invalid_sc_total THEN
1863 /* Bug 2191739 - call to message API for degovtized message */
1864 FND_MESSAGE.set_name (
1865 application => 'AR',
1866 name => gl_public_sector.get_message_name
1867 (p_message_name => 'AR_RA_SALES_CREDIT_LIMIT',
1868 p_app_short_name => 'AR'));
1869 FND_MESSAGE.set_token('SALES_CREDIT_LIMIT',
1870 arp_global.sysparam.sales_credit_pct_limit);
1871 FND_MESSAGE.set_token('SALESREP_NAME',l_new_salesrep_name);
1872 FND_MESSAGE.set_token('LINE_NUMBER',l_line_number);
1873 FND_MSG_PUB.Add;
1874 RAISE FND_API.G_EXC_ERROR;
1875
1876 WHEN invalid_amount THEN
1877 IF p_rev_adj_rec.amount_mode = 'P'
1878 THEN
1879 l_max_percent := ROUND(l_nonrev_trx_total / l_trx_total * 100,2);
1880 FND_MESSAGE.set_name
1881 (application => 'AR', name => 'AR_RA_PCT_EXCEEDS_AVAIL_PCT');
1882 FND_MESSAGE.set_token('TOT_AVAIL_PCT',l_max_percent);
1883 ELSE
1884 FND_MESSAGE.SET_NAME
1885 (application => 'AR', name => 'AR_RA_AMT_EXCEEDS_AVAIL_REV');
1886 FND_MESSAGE.set_token('TOT_AVAIL_REV',
1887 AR_RAAPI_UTIL.g_trx_currency||' '||l_adj_trx_total);
1888 END IF;
1889 FND_MSG_PUB.Add;
1890 RAISE FND_API.G_EXC_ERROR;
1891 WHEN invalid_zero THEN
1892 FND_MESSAGE.set_name
1893 (application => 'AR', name => 'AR_RA_ZERO_AMOUNT');
1894 FND_MSG_PUB.Add;
1895 RAISE FND_API.G_EXC_ERROR;
1896 WHEN invalid_ccid THEN
1897 FND_MSG_PUB.Add;
1898 FND_MESSAGE.SET_NAME(application => 'AR',
1899 name => 'AR_RA_INVALID_CODE_COMB');
1900 FND_MESSAGE.SET_TOKEN('CODE_COMBINATION',l_concat_segments);
1901 FND_MSG_PUB.Add;
1902 RAISE FND_API.G_EXC_ERROR;
1903 WHEN OTHERS then
1904 IF (SQLCODE = -20001)
1905 THEN
1906 IF PG_DEBUG in ('Y', 'C') THEN
1907 arp_util.debug
1908 ('20001 error at AR_Revenue_Adjustment_PVT.Transfer_Sales_Credits()');
1909 END IF;
1910 RAISE FND_API.G_EXC_ERROR;
1911 ELSE
1912 IF PG_DEBUG in ('Y', 'C') THEN
1913 arp_util.debug('Transfer_Sales_Credits: ' || 'Unexpected error '||sqlerrm||
1914 ' at AR_Revenue_Adjustment_PVT.Transfer_Sales_Credits()+');
1915 END IF;
1916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1917 END IF;
1918 END;
1919 --
1920 -- End of Inner Block
1921 --
1922
1923 -- Standard check of p_commit.
1924 IF FND_API.To_Boolean( p_commit )
1925 THEN
1926 COMMIT WORK;
1927 END IF;
1928 -- Standard call to get message count and if count is 1, get message info.
1929 FND_MSG_PUB.Count_And_Get
1930 (p_encoded => FND_API.G_FALSE,
1931 p_count => x_msg_count,
1932 p_data => x_msg_data);
1933 IF PG_DEBUG in ('Y', 'C') THEN
1934 arp_util.debug('AR_Revenue_Adjustment_PVT.Transfer_Sales_Credits()-');
1935 END IF;
1936 EXCEPTION
1937 WHEN FND_API.G_EXC_ERROR THEN
1938 ROLLBACK TO Transfer_Sales_Credits_PVT;
1939 x_return_status := FND_API.G_RET_STS_ERROR ;
1940 FND_MSG_PUB.Count_And_Get
1941 (p_encoded => FND_API.G_FALSE,
1942 p_count => x_msg_count,
1943 p_data => x_msg_data);
1944 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1945 IF PG_DEBUG in ('Y', 'C') THEN
1946 arp_util.debug('Transfer_Sales_Credits: ' || 'Unexpected error '||sqlerrm||
1950 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1947 ' at AR_Revenue_Adjustment_PVT.Transfer_Sales_Credits()+');
1948 END IF;
1949 ROLLBACK TO Transfer_Sales_Credits_PVT;
1951 FND_MSG_PUB.Count_And_Get
1952 (p_encoded => FND_API.G_FALSE,
1953 p_count => x_msg_count,
1954 p_data => x_msg_data);
1955 WHEN OTHERS THEN
1956 IF (SQLCODE = -20001)
1957 THEN
1958 IF PG_DEBUG in ('Y', 'C') THEN
1959 arp_util.debug('Transfer_Sales_Credits: ' || '20001 error '||
1960 ' at AR_Revenue_Adjustment_PVT.Transfer_Sales_Credits()+');
1961 END IF;
1962 x_return_status := FND_API.G_RET_STS_ERROR ;
1963 ELSE
1964 IF PG_DEBUG in ('Y', 'C') THEN
1965 arp_util.debug('Transfer_Sales_Credits: ' || 'Unexpected error '||sqlerrm||
1966 ' at AR_Revenue_Adjustment_PVT.Transfer_Sales_Credits()+');
1967 END IF;
1968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1969 IF FND_MSG_PUB.Check_Msg_Level
1970 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1971 THEN
1972 FND_MSG_PUB.Add_Exc_Msg
1973 ( G_PKG_NAME ,
1974 l_api_name
1975 );
1976 END IF;
1977 END IF;
1978 ROLLBACK TO Transfer_Sales_Credits_PVT;
1979 FND_MSG_PUB.Count_And_Get
1980 (p_encoded => FND_API.G_FALSE,
1981 p_count => x_msg_count,
1982 p_data => x_msg_data);
1983
1984 END Transfer_Sales_Credits;
1985
1986 -----------------------------------------------------------------------
1987 -- API name : Add_Non_Revenue_Sales_Credits
1988 -- Type : Private
1989 -- Function : Adds non revenue sales credits to the specified
1990 -- salesrep subject to any maximum limit of revenue
1991 -- and non revenue salsescredit per salesrep per line
1992 -- as defined in the sales credit percent limit in
1993 -- system options.
1994 -- Pre-reqs : None
1995 --
1996 -- Parameters :
1997 -- IN : p_api_version NUMBER Required
1998 -- p_init_msg_list VARCHAR2 Optional
1999 -- Default = FND_API.G_FALSE
2000 -- p_commit VARCHAR2 Optional
2001 -- Default = FND_API.G_FALSE
2002 -- p_validation_level NUMBER Optional
2003 -- Default = FND_API.G_VALID_LEVEL_FULL
2004 -- p_rev_adj_rec Rev_Adj_Rec_Type Required
2005 -- OUT NOCOPY : x_return_status VARCHAR2(1)
2006 -- x_msg_count NUMBER
2007 -- x_msg_data VARCHAR2(2000)
2008 -- x_adjustment_id NUMBER
2009 -- x_adjustment_number VARCHAR2
2010 --
2011 -- Version : Current version 2.0
2012 -- IN parameters consolidated into new record type
2013 -- Initial version 1.0
2014 --
2015 -- Notes :
2016 --
2017 PROCEDURE Add_Non_Revenue_Sales_Credits
2018 ( p_api_version IN NUMBER
2019 ,p_init_msg_list IN VARCHAR2
2020 ,p_commit IN VARCHAR2
2021 ,p_validation_level IN NUMBER
2022 ,x_return_status OUT NOCOPY VARCHAR2
2023 ,x_msg_count OUT NOCOPY NUMBER
2024 ,x_msg_data OUT NOCOPY VARCHAR2
2025 ,p_rev_adj_rec IN Rev_Adj_Rec_Type
2026 ,x_adjustment_id OUT NOCOPY NUMBER
2027 ,x_adjustment_number OUT NOCOPY VARCHAR2)
2028 IS
2029 l_api_name CONSTANT VARCHAR2(30) :=
2030 'Add_Non_Revenue_Sales_Credits';
2031 l_api_version CONSTANT NUMBER := 2.0;
2032 l_rev_adj_rec AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type;
2033
2034 l_trx_total NUMBER;
2035 l_line_count NUMBER;
2036 l_no_of_lines NUMBER;
2037 l_total_adjusted NUMBER;
2038 l_line_adjusted NUMBER;
2039 l_amount NUMBER;
2040 l_line_amount NUMBER;
2041 l_line_percent NUMBER;
2042 l_sales_credit_id NUMBER;
2043
2044 l_revenue_amount_split_new NUMBER;
2045 l_revenue_percent_split_new NUMBER;
2046 l_nonrev_amount_split_new NUMBER;
2047 l_nonrev_percent_split_new NUMBER;
2048 l_transferable_amount NUMBER;
2049 l_cust_trx_line_id NUMBER;
2050 l_line_number NUMBER;
2051 l_total_sc_percent NUMBER;
2052 l_neg_sc_limit NUMBER;
2053 l_new_salesrep_name ra_salesreps.name%TYPE;
2054
2055 invalid_sc_total EXCEPTION;
2056 invalid_amount EXCEPTION;
2057
2058 CURSOR c_trx_total IS
2059 SELECT SUM(NVL(s.revenue_amount_split,0))
2060 FROM ra_cust_trx_line_salesreps s
2061 ,mtl_item_categories mic
2062 ,ra_customer_trx_lines l
2063 WHERE s.customer_trx_line_id = l.customer_trx_line_id
2064 AND l.line_type = 'LINE'
2065 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
2069 NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
2066 AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
2067 l.customer_trx_line_id)
2068 AND NVL(l.inventory_item_id,0) =
2070 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
2071 AND l.inventory_item_id = mic.inventory_item_id(+)
2072 AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
2073 DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
2074 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id;
2075
2076 CURSOR c_line_count IS
2077 SELECT COUNT(*)
2078 FROM mtl_item_categories mic
2079 ,ra_customer_trx_lines l
2080 WHERE l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
2081 AND l.line_type = 'LINE'
2082 AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
2083 l.customer_trx_line_id)
2084 AND NVL(l.inventory_item_id,0) =
2085 NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
2086 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
2087 AND l.inventory_item_id = mic.inventory_item_id(+)
2088 AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
2089 DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
2090 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id;
2091
2092 CURSOR c_line IS
2093 SELECT l.customer_trx_line_id
2094 ,l.line_number
2095 ,SUM(NVL(s.revenue_amount_split,0)) amount
2096 FROM mtl_item_categories mic
2097 ,ra_cust_trx_line_salesreps s
2098 ,ra_customer_trx_lines l
2099 WHERE l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
2100 AND l.customer_trx_line_id = s.customer_trx_line_id
2101 AND l.customer_trx_line_id =
2102 NVL (AR_RAAPI_UTIL.g_from_cust_trx_line_id, l.customer_trx_line_id)
2103 AND NVL(l.inventory_item_id,0) =
2104 NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
2105 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
2106 AND l.inventory_item_id = mic.inventory_item_id(+)
2107 AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
2108 DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
2109 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
2110 AND l.line_type = 'LINE'
2111 GROUP BY l.customer_trx_line_id
2112 ,l.line_number;
2113
2114 CURSOR get_salesrep_lines_new is
2115 SELECT NVL(SUM(s.revenue_amount_split),0),
2116 NVL(SUM(s.revenue_percent_split),0),
2117 NVL(SUM(s.non_revenue_amount_split),0),
2118 NVL(SUM(s.non_revenue_percent_split),0)
2119 FROM ra_cust_trx_line_salesreps s,
2120 ra_customer_trx_lines l
2121 WHERE s.customer_trx_line_id = l_cust_trx_line_id
2122 AND s.salesrep_id = AR_RAAPI_UTIL.g_to_salesrep_id
2123 AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
2124 NVL(AR_RAAPI_UTIL.g_to_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
2125 AND l.line_type = 'LINE'
2126 AND l.customer_trx_line_id = s.customer_trx_line_id
2127 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id;
2128
2129 CURSOR c_new_salesrep_name IS
2130 SELECT name
2131 FROM ra_salesreps
2132 WHERE salesrep_id = AR_RAAPI_UTIL.g_to_salesrep_id;
2133
2134 BEGIN
2135
2136 IF PG_DEBUG in ('Y', 'C') THEN
2137 arp_util.debug('AR_Revenue_Adjustment_PVT.Add_Non_Revenue_Sales_Credits()+');
2138 END IF;
2139 -- Standard Start of API savepoint
2140 SAVEPOINT Add_Non_Rev_Sales_Credits_PVT;
2141 -- Standard call to check for call compatibility.
2142 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2143 p_api_version ,
2144 l_api_name ,
2145 G_PKG_NAME )
2146 THEN
2147 IF PG_DEBUG in ('Y', 'C') THEN
2148 arp_util.debug('Add_Non_Revenue_Sales_Credits: ' || 'Unexpected error '||sqlerrm||
2149 ' at AR_Revenue_Adjustment_PVT.Add_Non_Revenue_Sales_Credits()+');
2150 END IF;
2151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2152 END IF;
2153 -- Initialize message list if p_init_msg_list is set to TRUE.
2154 IF FND_API.to_Boolean( p_init_msg_list )
2155 THEN
2156 FND_MSG_PUB.initialize;
2157 END IF;
2158 -- Initialize API return status to success
2159 x_return_status := FND_API.G_RET_STS_SUCCESS;
2160
2161 l_rev_adj_rec := p_rev_adj_rec;
2162
2163 /*Bug 6731185 JVARKEY Making sure GL date has no timestamp*/
2164 l_rev_adj_rec.gl_date := trunc(p_rev_adj_rec.gl_date);
2165
2166 l_rev_adj_rec.adjustment_type := 'NR';
2167
2168 AR_RAAPI_UTIL.Constant_System_Values;
2169 AR_RAAPI_UTIL.Initialize_Globals;
2170 AR_RAAPI_UTIL.Validate_Parameters (p_init_msg_list => FND_API.G_FALSE
2171 ,p_rev_adj_rec => l_rev_adj_rec
2172 ,p_validation_level => p_validation_level
2173 ,x_return_status => x_return_status
2177 THEN
2174 ,x_msg_count => x_msg_count
2175 ,x_msg_data => x_msg_data);
2176 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2178 RAISE FND_API.G_EXC_ERROR;
2179 END IF;
2180
2181 --
2182 -- Inner PL/SQL block to ensure consistent error handling
2183 --
2184 BEGIN
2185 IF PG_DEBUG in ('Y', 'C') THEN
2186 arp_util.debug('AR_Revenue_Adjustment_PVT.Add_Non_Revenue_Sales_Credits(2)+');
2187 END IF;
2188 l_amount := 0;
2189 l_line_count := 0;
2190 l_total_adjusted := 0;
2191 l_line_adjusted :=0;
2192
2193 OPEN c_trx_total;
2194 FETCH c_trx_total INTO l_trx_total;
2195 CLOSE c_trx_total;
2196
2197 OPEN c_new_salesrep_name;
2198 FETCH c_new_salesrep_name INTO l_new_salesrep_name;
2199 CLOSE c_new_salesrep_name;
2200 --
2201 -- Determine amount to be added
2202 --
2203 OPEN c_line_count;
2204 FETCH c_line_count INTO l_no_of_lines;
2205 CLOSE c_line_count;
2206
2207 IF p_rev_adj_rec.amount_mode = 'A'
2208 THEN
2209 l_amount := p_rev_adj_rec.amount;
2210 ELSIF p_rev_adj_rec.amount_mode = 'P'
2211 THEN
2212 l_amount := ROUND(l_trx_total * p_rev_adj_rec.percent / 100,
2213 AR_RAAPI_UTIL.g_trx_precision);
2214 END IF;
2215 IF l_amount = 0
2216 THEN
2217 RAISE invalid_amount;
2218 END IF;
2219
2220 create_adjustment
2221 (p_rev_adj_rec => l_rev_adj_rec
2222 ,x_adjustment_id => x_adjustment_id
2223 ,x_adjustment_number => x_adjustment_number);
2224
2225 --
2226 -- Now add the amount to each line pro rata
2227 --
2228 FOR c1 IN c_line LOOP
2229
2230 l_cust_trx_line_id := c1.customer_trx_line_id;
2231 l_line_number := c1.line_number;
2232 l_line_amount := 0;
2233 l_line_percent := 0;
2234
2235 l_line_amount := ROUND(l_amount * c1.amount
2236 / l_trx_total , AR_RAAPI_UTIL.g_trx_precision);
2237
2238 IF l_line_amount <> 0
2239 THEN
2240 l_line_count := l_line_count + 1;
2241 l_total_adjusted := l_total_adjusted + l_line_amount;
2242 IF l_line_count = l_no_of_lines AND l_total_adjusted <> l_amount
2243 THEN
2244 l_line_amount := l_line_amount + l_amount - l_total_adjusted;
2245 END IF;
2246 IF p_rev_adj_rec.amount_mode = 'P'
2247 THEN
2248 l_line_percent := p_rev_adj_rec.percent;
2249 ELSE
2250 l_line_percent := ROUND(l_line_amount / c1.amount * 100, 4);
2251 END IF;
2252 END IF;
2253
2254 OPEN get_salesrep_lines_new;
2255 FETCH get_salesrep_lines_new INTO l_revenue_amount_split_new,
2256 l_revenue_percent_split_new,
2257 l_nonrev_amount_split_new,
2258 l_nonrev_percent_split_new;
2259 CLOSE get_salesrep_lines_new;
2260
2261 --
2262 -- Check if total rev/non rev percent on new salesrep exceeds limit
2263 --
2264 l_total_sc_percent := l_line_percent + l_revenue_percent_split_new +
2265 l_nonrev_percent_split_new;
2266 IF l_total_sc_percent < 0
2267 THEN
2268 IF arp_global.sysparam.sales_credit_pct_limit IS NULL
2269 THEN
2270 l_neg_sc_limit := NULL;
2271 ELSE
2272 l_neg_sc_limit := arp_global.sysparam.sales_credit_pct_limit * -1;
2273 END IF;
2274 END IF;
2275 IF (l_total_sc_percent > 0 AND l_total_sc_percent >
2276 NVL(arp_global.sysparam.sales_credit_pct_limit,l_total_sc_percent)) OR
2277 (l_total_sc_percent < 0 and l_total_sc_percent <
2278 NVL(l_neg_sc_limit,l_total_sc_percent))
2279 THEN
2280 RAISE invalid_sc_total;
2281 END IF;
2282
2283 insert_sales_credit( AR_RAAPI_UTIL.g_customer_trx_id,
2284 AR_RAAPI_UTIL.g_to_salesrep_id,
2285 AR_RAAPI_UTIL.g_to_salesgroup_id,
2286 l_cust_trx_line_id,
2287 l_line_amount,
2288 l_line_percent,
2289 'N',
2290 l_sales_credit_id,
2291 x_adjustment_id,
2292 NULL);
2293
2294 END LOOP; -- c_line LOOP
2295
2296 EXCEPTION
2297 WHEN invalid_sc_total THEN
2298 /* Bug 2191739 - call to message API for degovtized message */
2299 FND_MESSAGE.set_name (
2300 application => 'AR',
2301 name => gl_public_sector.get_message_name
2302 (p_message_name => 'AR_RA_SALES_CREDIT_LIMIT',
2303 p_app_short_name => 'AR'));
2304 FND_MESSAGE.set_token('SALES_CREDIT_LIMIT',
2305 arp_global.sysparam.sales_credit_pct_limit);
2306 FND_MESSAGE.set_token('SALESREP_NAME',l_new_salesrep_name);
2310 WHEN invalid_amount THEN
2307 FND_MESSAGE.set_token('LINE_NUMBER',l_line_number);
2308 FND_MSG_PUB.Add;
2309 RAISE FND_API.G_EXC_ERROR;
2311 FND_MESSAGE.SET_NAME
2312 (application => 'AR', name => 'AR_RA_AMT_EXCEEDS_AVAIL_REV');
2313 FND_MESSAGE.set_token('TOT_AVAIL_REV',
2314 AR_RAAPI_UTIL.g_trx_currency||' '||'0');
2315 FND_MSG_PUB.Add;
2316 RAISE FND_API.G_EXC_ERROR;
2317 WHEN OTHERS then
2318 IF (SQLCODE = -20001)
2319 THEN
2320 IF PG_DEBUG in ('Y', 'C') THEN
2321 arp_util.debug
2322 ('20001 error at AR_Revenue_Adjustment_PVT.Add_Non_Revenue_Sales_Credits()');
2323 END IF;
2324 RAISE FND_API.G_EXC_ERROR;
2325 ELSE
2326 IF PG_DEBUG in ('Y', 'C') THEN
2327 arp_util.debug('Add_Non_Revenue_Sales_Credits: ' || 'Unexpected error '||sqlerrm||
2328 ' at AR_Revenue_Adjustment_PVT.Add_Non_Revenue_Sales_Credits()+');
2329 END IF;
2330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2331 END IF;
2332
2333 END;
2334 --
2335 -- End of Inner Block
2336 --
2337
2338 -- Standard check of p_commit.
2339 IF FND_API.To_Boolean( p_commit )
2340 THEN
2341 COMMIT WORK;
2342 END IF;
2343 -- Standard call to get message count and if count is 1, get message info.
2344 FND_MSG_PUB.Count_And_Get
2345 (p_encoded => FND_API.G_FALSE,
2346 p_count => x_msg_count,
2347 p_data => x_msg_data);
2348 IF PG_DEBUG in ('Y', 'C') THEN
2349 arp_util.debug('AR_Revenue_Adjustment_PVT.Add_Non_Revenue_Sales_Credits()-');
2350 END IF;
2351 EXCEPTION
2352 WHEN FND_API.G_EXC_ERROR THEN
2353 ROLLBACK TO Add_Non_Rev_Sales_Credits_PVT;
2354 x_return_status := FND_API.G_RET_STS_ERROR ;
2355 FND_MSG_PUB.Count_And_Get
2356 (p_encoded => FND_API.G_FALSE,
2357 p_count => x_msg_count,
2358 p_data => x_msg_data);
2359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2360 IF PG_DEBUG in ('Y', 'C') THEN
2361 arp_util.debug('Add_Non_Revenue_Sales_Credits: ' || 'Unexpected error '||sqlerrm||
2362 ' at AR_Revenue_Adjustment_PVT.Add_Non_Revenue_Sales_Credits()+');
2363 END IF;
2364 ROLLBACK TO Add_Non_Rev_Sales_Credits_PVT;
2365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2366 FND_MSG_PUB.Count_And_Get
2367 (p_encoded => FND_API.G_FALSE,
2368 p_count => x_msg_count,
2369 p_data => x_msg_data);
2370 WHEN OTHERS THEN
2371 IF (SQLCODE = -20001)
2372 THEN
2373 IF PG_DEBUG in ('Y', 'C') THEN
2374 arp_util.debug('Add_Non_Revenue_Sales_Credits: ' || '20001 error '||
2375 ' at AR_Revenue_Adjustment_PVT.Add_Non_Revenue_Sales_Credits()+');
2376 END IF;
2377 x_return_status := FND_API.G_RET_STS_ERROR ;
2378 ELSE
2379 IF PG_DEBUG in ('Y', 'C') THEN
2380 arp_util.debug('Add_Non_Revenue_Sales_Credits: ' || 'Unexpected error '||sqlerrm||
2381 ' at AR_Revenue_Adjustment_PVT.Add_Non_Revenue_Sales_Credits()+');
2382 END IF;
2383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2384 IF FND_MSG_PUB.Check_Msg_Level
2385 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2386 THEN
2387 FND_MSG_PUB.Add_Exc_Msg
2388 ( G_PKG_NAME ,
2389 l_api_name
2390 );
2391 END IF;
2392 END IF;
2393 ROLLBACK TO Add_Non_Rev_Sales_Credits_PVT;
2394 FND_MSG_PUB.Count_And_Get
2395 (p_encoded => FND_API.G_FALSE,
2396 p_count => x_msg_count,
2397 p_data => x_msg_data);
2398 END Add_Non_Revenue_Sales_Credits;
2399
2400 PROCEDURE transfer_salesrep_revenue
2401 (p_customer_trx_line_id IN NUMBER
2402 ,p_customer_trx_id IN NUMBER
2403 ,p_sales_credit_id IN NUMBER
2404 ,p_revenue_amount IN NUMBER
2405 ,p_revenue_acctd_amount IN NUMBER
2406 ,p_gl_date IN DATE
2407 ,p_ccid IN NUMBER
2408 ,p_last_salesrep_flag IN VARCHAR2 -- Bug 2477881
2409 ,p_line_amount IN NUMBER -- Bug 2477881
2410 ,p_line_amount_acctd IN NUMBER -- Bug 2477881
2411 ,p_adjustment_id IN NUMBER)
2412 IS
2413 l_user_id NUMBER := 0;
2414 l_line_id NUMBER := 0;
2415 l_dist_amount NUMBER := 0;
2416 l_dist_acctd_amount NUMBER := 0;
2417 l_dist_percent NUMBER := 0;
2418 l_no_of_assignments NUMBER := 0;
2419 l_assignment_count NUMBER := 0;
2420 l_assignment_total NUMBER := 0;
2421 l_dist_tot NUMBER := 0;
2422 l_dist_acctd_tot NUMBER := 0;
2423 l_dist_pct_tot NUMBER := 0; -- Bug 2477881
2424 l_revenue_percent NUMBER := 0; -- Bug 2477881
2425 l_ext_amount NUMBER := 0;
2426 l_acc_rule_duration NUMBER := 0;
2427 l_rule_start_date DATE;
2428 l_deferred_revenue_flag VARCHAR2(1);
2432 l_cr_account_class ra_cust_trx_line_gl_dist.account_class%TYPE;
2429 l_deferred_days NUMBER := 0;
2430 l_gl_date DATE;
2431 l_gl_date_valid DATE; -- Bug 2146970
2433 l_revenue_type VARCHAR2(10);
2434 l_gl_date_total NUMBER; -- Bug 2477881
2435 l_gl_date_acctd_total NUMBER; -- Bug 2477881
2436 l_gl_date_pct_total NUMBER; -- Bug 2477881
2437 l_correct_gl_date_amt NUMBER; -- Bug 2477881
2438 l_correct_gl_date_acctd_amt NUMBER; -- Bug 2477881
2439 l_correct_gl_date_pct NUMBER; -- Bug 2477881
2440
2441 l_round_acctd_amount NUMBER;
2442 l_acctd_round_flag boolean := FALSE;
2443
2444 CURSOR c_assignment_count IS
2445 SELECT SUM(ar.amount), count(*)
2446 FROM ar_revenue_assignments ar,
2447 gl_sets_of_books sob
2448 WHERE customer_trx_line_id = p_customer_trx_line_id
2449 AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
2450 AND ar.period_set_name = sob.period_set_name
2451 AND ar.account_class = 'REV';
2452
2453 CURSOR c_revenue_assignment IS
2454 SELECT (ar.gl_date + l_deferred_days) gl_date,
2455 SUM(ar.amount) amount
2456 FROM ar_revenue_assignments ar,
2457 gl_sets_of_books sob
2458 WHERE ar.customer_trx_line_id = p_customer_trx_line_id
2459 AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
2460 AND ar.period_set_name = sob.period_set_name
2461 AND ar.account_class = 'REV'
2462 GROUP BY ar.gl_date
2463 ORDER BY (ar.gl_date + l_deferred_days);
2464 -- Bug 6640822 , added order by clause in the above query
2465
2466 CURSOR c_line IS
2467 SELECT l.customer_trx_line_id,
2468 l.extended_amount,
2469 l.accounting_rule_duration,
2470 r.deferred_revenue_flag,
2471 l.rule_start_date
2472 FROM ra_customer_trx_lines l,
2473 ra_rules r
2474 WHERE l.accounting_rule_id = r.rule_id
2475 AND l.customer_trx_line_id = p_customer_trx_line_id
2476 AND l.customer_trx_id = p_customer_trx_id
2477 AND l.line_type = 'LINE';
2478
2479 BEGIN
2480 IF PG_DEBUG in ('Y', 'C') THEN
2481 arp_util.debug('AR_Revenue_Adjustment_PVT.transfer_salesrep_revenue()+');
2482 arp_util.debug(' p_customer_trx_line_id = ' || p_customer_trx_line_id);
2483 arp_util.debug(' p_customer_trx_id = ' || p_customer_trx_id);
2484 arp_util.debug(' p_sales_credit_id = ' || p_sales_credit_id);
2485 arp_util.debug(' p_revenue_amount = ' || p_revenue_amount);
2486 arp_util.debug(' p_revenue_acctd_amount = ' || p_revenue_acctd_amount);
2487 arp_util.debug(' p_line_amount = ' || p_line_amount);
2488 arp_util.debug(' p_line_amount_acctd = ' || p_line_amount_acctd);
2489 END IF;
2490
2491 FND_PROFILE.get('USER_ID',l_user_id);
2492 IF l_user_id IS NULL
2493 THEN
2494 l_user_id := 0;
2495 ELSE
2496 l_user_id := FND_GLOBAL.USER_ID;
2497 END IF;
2498
2499 l_dist_tot := 0;
2500 l_dist_pct_tot := 0; -- Bug 2477881
2501 l_dist_acctd_tot := 0; -- Bug 2143925
2502
2503 OPEN c_line;
2504 FETCH c_line INTO l_line_id,
2505 l_ext_amount,
2506 l_acc_rule_duration,
2507 l_deferred_revenue_flag,
2508 l_rule_start_date;
2509 CLOSE c_line;
2510
2511 /* Bug 2477881 - find overall percent to check pct rounding errors */
2512 IF (p_revenue_amount = l_ext_amount OR l_ext_amount = 0)
2513 THEN
2514 l_revenue_percent := 100;
2515 ELSE
2516 l_revenue_percent := ROUND(((p_revenue_amount / l_ext_amount) * 100),4);
2517 END IF;
2518
2519 IF l_deferred_revenue_flag = 'Y'
2520 THEN
2521 l_deferred_days := TRUNC(p_gl_date) - TRUNC(l_rule_start_date);
2522 ELSE
2523 l_deferred_days := 0;
2524 END IF;
2525
2526 OPEN c_assignment_count;
2527 FETCH c_assignment_count INTO l_assignment_total,
2528 l_no_of_assignments;
2529 CLOSE c_assignment_count;
2530
2531 FOR c1 in c_revenue_assignment LOOP
2532
2533 l_assignment_count := l_assignment_count + 1;
2534
2535 /* Bug 2143925 - calculate acctd distribution amount */
2536 /* Bug 2477881 - To correct rounding errors for multi salesrep transfers,
2537 calculate the correct overall revenue amount to be debited */
2538 /* Bug 4675438: MOAC/SSA */
2539 IF l_assignment_total = 0
2540 THEN
2541 l_dist_amount := arpcurr.currround(p_revenue_amount / l_no_of_assignments , AR_RAAPI_UTIL.g_trx_currency);
2542 l_dist_acctd_amount := arpcurr.currround(p_revenue_acctd_amount / l_no_of_assignments ,AR_RAAPI_UTIL.g_trx_currency);
2543 l_correct_gl_date_amt := arpcurr.currround(p_line_amount / l_no_of_assignments ,AR_RAAPI_UTIL.g_trx_currency);
2544 l_correct_gl_date_acctd_amt := arpcurr.currround(p_line_amount_acctd / l_no_of_assignments ,AR_RAAPI_UTIL.g_trx_currency);
2545 ELSE
2546 l_dist_amount := arpcurr.currround(p_revenue_amount * c1.amount / l_assignment_total , AR_RAAPI_UTIL.g_trx_currency);
2550 END IF;
2547 l_dist_acctd_amount := arpcurr.currround(p_revenue_acctd_amount * c1.amount / l_assignment_total , AR_RAAPI_UTIL.g_trx_currency);
2548 l_correct_gl_date_amt := arpcurr.currround(p_line_amount * c1.amount / l_assignment_total ,AR_RAAPI_UTIL.g_trx_currency);
2549 l_correct_gl_date_acctd_amt := arpcurr.currround(p_line_amount_acctd * c1.amount / l_assignment_total ,AR_RAAPI_UTIL.g_trx_currency);
2551
2552 IF l_ext_amount = 0
2553 THEN
2554 l_dist_percent := ROUND (100 / l_no_of_assignments , 4);
2555 ELSE
2556 l_dist_percent := ROUND (l_dist_amount / l_ext_amount * 100, 4);
2557 END IF;
2558
2559 l_dist_tot := l_dist_tot + l_dist_amount;
2560 /* Bug 2143925 */
2561 l_dist_acctd_tot := l_dist_acctd_tot + l_dist_acctd_amount;
2562 l_dist_pct_tot := l_dist_pct_tot + l_dist_percent;
2563 IF l_assignment_count = l_no_of_assignments AND
2564 l_dist_tot <> p_revenue_amount
2565 THEN
2566 l_dist_amount := l_dist_amount + (p_revenue_amount - l_dist_tot);
2567 END IF;
2568 /* Bug 2143925 - load any rounding difference onto last distribution */
2569 IF l_assignment_count = l_no_of_assignments AND
2570 l_dist_acctd_tot <> p_revenue_acctd_amount
2571 THEN
2572 l_dist_acctd_amount := l_dist_acctd_amount +
2573 (p_revenue_acctd_amount - l_dist_acctd_tot);
2574 /* 6325023 - check resulting sign of acctd_amount and prepare to
2575 split the distributions */
2576 IF SIGN(l_dist_amount) <> SIGN(l_dist_acctd_amount)
2577 AND SIGN(l_dist_amount) <> 0
2578 THEN
2579 l_acctd_round_flag := TRUE;
2580 /* back out the rounding correction */
2581 l_dist_acctd_amount := l_dist_acctd_amount -
2582 (p_revenue_acctd_amount - l_dist_acctd_tot);
2583 /* set local variable for second insert */
2584 l_round_acctd_amount := p_revenue_acctd_amount - l_dist_acctd_tot;
2585 ELSE
2586 l_acctd_round_flag := FALSE;
2587 l_round_acctd_amount := 0;
2588 END IF;
2589 END IF;
2590
2591 /* Bug 2477881 - load pct rounding difference onto last distribution */
2592 IF l_assignment_count = l_no_of_assignments AND
2593 l_dist_pct_tot <> l_revenue_percent
2594 THEN
2595 l_dist_percent := l_dist_percent +
2596 (l_revenue_percent - l_dist_pct_tot);
2597 END IF;
2598
2599 IF ((l_dist_percent > -0.01 AND l_dist_percent < 0.01) OR
2600 l_dist_percent > 999 OR
2601 l_dist_percent < -999)
2602 THEN
2603 l_dist_percent := ROUND (100 / l_no_of_assignments, 4)
2604 * SIGN(l_dist_percent);
2605 END IF;
2606 IF NVL(l_acc_rule_duration,0) > 1
2607 THEN
2608 l_gl_date := GREATEST(p_gl_date, c1.gl_date);
2609 ELSE
2610 l_gl_date := p_gl_date;
2611 END IF;
2612
2613 /* Bug 2146970 - validate the GL date passed in */
2614 l_gl_date_valid := AR_RAAPI_UTIL.bump_gl_date_if_closed (p_gl_date => l_gl_date);
2615 IF l_gl_date_valid IS NULL
2616 THEN
2617 FND_MESSAGE.set_name('AR','AR_RA_NO_OPEN_PERIODS');
2618 FND_MSG_PUB.Add;
2619 RAISE FND_API.G_EXC_ERROR;
2620 ELSE
2621 l_gl_date := l_gl_date_valid;
2622 END IF;
2623
2624 insert_distribution ( p_customer_trx_line_id,
2625 p_ccid,
2626 l_dist_percent,
2627 l_dist_acctd_amount,
2628 l_gl_date,
2629 c1.gl_date,
2630 'REV',
2631 l_dist_amount,
2632 p_sales_credit_id,
2633 p_customer_trx_id,
2634 p_adjustment_id);
2635
2636 IF l_acctd_round_flag
2637 THEN
2638 /* 6325023 - Need to insert a second dist for the acctd correction */
2639 insert_distribution ( p_customer_trx_line_id,
2640 p_ccid,
2641 0,
2642 l_round_acctd_amount,
2643 l_gl_date,
2644 c1.gl_date,
2645 'REV',
2646 0,
2647 p_sales_credit_id,
2648 p_customer_trx_id,
2649 p_adjustment_id,
2650 'Y');
2651
2652
2653 END IF;
2654
2655 END LOOP; -- assignments loop
2656 IF PG_DEBUG in ('Y', 'C') THEN
2657 arp_util.debug('AR_Revenue_Adjustment_PVT.transfer_salesrep_revenue()-');
2658 END IF;
2659
2660 EXCEPTION
2661
2662 WHEN OTHERS then
2663 IF (SQLCODE = -20001)
2664 THEN
2665 IF PG_DEBUG in ('Y', 'C') THEN
2666 arp_util.debug
2667 ('20001 error at AR_Revenue_Adjustment_PVT.transfer_salesrep_revenue()');
2668 END IF;
2672 arp_util.debug('transfer_salesrep_revenue: ' || 'Unexpected error '||sqlerrm||
2669 RAISE FND_API.G_EXC_ERROR;
2670 ELSE
2671 IF PG_DEBUG in ('Y', 'C') THEN
2673 ' at AR_Revenue_Adjustment_PVT.transfer_salesrep_revenue()+');
2674 END IF;
2675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2676 END IF;
2677
2678 END transfer_salesrep_revenue;
2679
2680 /* Bug 3879222 - new procedure for prorations that are not
2681 based on salesrep. This code is triggered when the line has
2682 override_auto_accounting_flag = Y */
2683
2684 /* 6862351 - added p_gl_date. This will be used to override
2685 the gl_date of the distributions if the rule has
2686 only one period. For multiperiod schedules, the dates
2687 will be based on ar_revenue_assignments returns */
2688 PROCEDURE dists_by_model
2689 (p_customer_trx_line_id IN NUMBER
2690 ,p_revenue_amount IN NUMBER
2691 ,p_adjustment_id IN NUMBER
2692 ,p_user_generated_flag IN VARCHAR2
2693 ,p_gl_date IN DATE)
2694 IS
2695 /* Locals */
2696
2697 CURSOR c_psn IS
2698 SELECT
2699 period_set_name,
2700 precision,
2701 minimum_accountable_unit,
2702 asp.org_id,
2703 asp.set_of_books_id
2704 FROM
2705 fnd_currencies fc,
2706 gl_sets_of_books gsb,
2707 ar_system_parameters asp
2708 WHERE
2709 gsb.set_of_books_id = asp.set_of_books_id
2710 AND fc.currency_code = gsb.currency_code;
2711
2712 rows NUMBER;
2713 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
2714
2715 --{BUG#5064609
2716 CURSOR cu_trx IS
2717 SELECT customer_trx_id
2718 FROM ra_customer_trx_lines
2719 WHERE customer_trx_line_id = p_customer_trx_line_id;
2720 l_trx_id NUMBER;
2721 l_xla_event arp_xla_events.xla_events_type;
2722 --}
2723
2724 BEGIN
2725 IF PG_DEBUG in ('Y', 'C')
2726 THEN
2727 arp_util.debug('AR_Revenue_Adjustment_PVT.dists_by_model()+');
2728 arp_util.debug(' p_adjustment_id = ' || p_adjustment_id);
2729 END IF;
2730
2731 /* Initialize globals for this package */
2732 IF g_period_set_name IS NULL
2733 THEN
2734 OPEN c_psn;
2735 FETCH c_psn INTO
2736 g_period_set_name,
2737 g_base_precision,
2738 g_bmau,
2739 g_org_id,
2740 g_sob_id;
2741 CLOSE c_psn;
2742 END IF;
2743
2744 /* Insert using statement similar to one in
2745 arp_auto_rule.create_assignments. Main diff is
2746 that this one is by line rather than trx. */
2747
2748 INSERT INTO ra_cust_trx_line_gl_dist
2749 (
2750 customer_trx_line_id,
2751 customer_trx_id,
2752 code_combination_id,
2753 set_of_books_id,
2754 account_class,
2755 account_set_flag,
2756 percent,
2757 amount,
2758 acctd_amount,
2759 gl_date,
2760 cust_trx_line_salesrep_id,
2761 request_id,
2762 program_application_id,
2763 program_id,
2764 program_update_date,
2765 creation_date,
2766 created_by,
2767 last_update_date,
2768 last_updated_by,
2769 posting_control_id,
2770 original_gl_date,
2771 cust_trx_line_gl_dist_id,
2772 revenue_adjustment_id,
2773 user_generated_flag,
2774 org_id
2775 )
2776 SELECT /*+ ORDERED push_pred(ra.lgd) index(dist ra_cust_trx_line_gl_dist_n1)*/
2777 ra.customer_trx_line_id, /* customer_trx_line_id */
2778 l.customer_trx_id, /* customer_trx_id */
2779 dist.code_combination_id, /* code_combination_id */
2780 arp_standard.sysparm.set_of_books_id, /* set_of_books_id */
2781 ra.account_class, /* account_class */
2782 'N', /* account_set_flag */
2783 ROUND((((ra.amount * dist.percent
2784 * p_revenue_amount)
2785 / l.extended_amount)
2786 / l.extended_amount),4), /* percent */
2787 DECODE(fc.minimum_accountable_unit,
2788 NULL, ROUND(((ra.amount * (dist.percent/100)
2789 * p_revenue_amount)
2790 / l.extended_amount),
2791 fc.precision),
2792 ROUND((((ra.amount * (dist.percent/100)
2793 * p_revenue_amount)
2794 / l.extended_amount)
2795 / fc.minimum_accountable_unit)
2796 * fc.minimum_accountable_unit)), /* Amount */
2797 DECODE(g_bmau,
2798 NULL, ROUND((((ra.amount * (dist.percent/100)
2799 * p_revenue_amount)
2800 / l.extended_amount)
2804 * p_revenue_amount)
2801 * NVL(h.exchange_rate,1)),
2802 g_base_precision),
2803 ROUND(((((ra.amount * (dist.percent/100)
2805 / l.extended_amount)
2806 * NVL(h.exchange_rate,1))
2807 / g_bmau)
2808 * g_bmau)), /* Acctd_amount */
2809 DECODE(l.accounting_rule_duration, 1, p_gl_date,
2810 arp_auto_rule.assign_gl_date(ra.gl_date)),/* Derived gl_date */
2811 dist.cust_trx_line_salesrep_id, /* Srep ID */
2812 arp_standard.profile.request_id,
2813 arp_standard.application_id,
2814 arp_standard.profile.program_id,
2815 sysdate,
2816 sysdate,
2817 arp_standard.profile.user_id,
2818 sysdate,
2819 arp_standard.profile.user_id,
2820 -3,
2821 ra.gl_date, /* original_gl_date */
2822 ra_cust_trx_line_gl_dist_s.NEXTVAL, /* cust_trx_line_gl_dist_id */
2823 p_adjustment_id,
2824 p_user_generated_flag,
2825 arp_standard.sysparm.org_id
2826 FROM
2827 ra_customer_trx_lines l,
2828 ra_customer_trx h,
2829 fnd_currencies fc,
2830 ra_cust_trx_line_gl_dist dist,
2831 ar_revenue_assignments ra
2832 WHERE
2833 l.customer_trx_line_id = p_customer_trx_line_id
2834 AND fc.currency_code = h.invoice_currency_code
2835 AND l.customer_trx_id = h.customer_trx_id
2836 AND ra.customer_trx_line_id = l.customer_trx_line_id
2837 AND ra.period_set_name = g_period_set_name
2838 AND dist.customer_trx_line_id= ra.customer_trx_line_id
2839 AND dist.account_class = ra.account_class
2840 AND dist.account_set_flag = 'Y'; /* model accounts */
2841
2842 /* Call MRC for inserts/updates if we have rows to
2843 process and if MRC is enabled */
2844 rows := sql%rowcount;
2845
2846
2847 IF PG_DEBUG in ('Y', 'C') THEN
2848 arp_standard.debug('Total lines inserted: ' || rows);
2849 END IF;
2850
2851 /* Note: rounding will be handled at the transaction level
2852 in a call to arp_rounding.correct_rev_adj_by_line */
2853
2854 END dists_by_model;
2855
2856 /* end */
2857
2858 PROCEDURE debit_credit
2859 (p_customer_trx_line_id IN NUMBER
2860 ,p_customer_trx_id IN NUMBER
2861 ,p_salesrep_id IN NUMBER
2862 ,p_revenue_amount IN NUMBER
2863 ,p_gl_date IN DATE
2864 ,p_credit_ccid IN NUMBER
2865 ,p_inventory_item_id IN NUMBER
2866 ,p_memo_line_id IN NUMBER
2867 ,p_adjustment_id IN NUMBER
2868 ,p_user_generated_flag IN VARCHAR2)
2869 IS
2870 l_user_id NUMBER := 0;
2871 l_last_salescredit_id NUMBER := 0;
2872 l_cust_trx_line_salesrep_id NUMBER := 0;
2873 l_latest_percent_split NUMBER := 0;
2874 l_salesrep_percent NUMBER := 0; -- Bug 2555736
2875 l_debit_ccid NUMBER := 0;
2876 l_credit_ccid NUMBER := 0;
2877 l_line_id NUMBER := 0;
2878 l_line_number NUMBER := 0;
2879 l_dist_amount NUMBER := 0;
2880 l_dist_acctd_amount NUMBER := 0;
2881 l_dist_percent NUMBER := 0;
2882 l_no_of_assignments NUMBER := 0;
2883 l_assignment_count NUMBER := 0;
2884 l_assignment_total NUMBER := 0;
2885 l_salesrep_count NUMBER := 0;
2886 l_no_of_salesreps NUMBER := 0;
2887 l_dist_tot NUMBER := 0;
2888 l_dist_pct_tot NUMBER := 0; -- Bug 2487901
2889 l_revenue_percent NUMBER := 0; -- Bug 2487901
2890 l_concat_segments VARCHAR2(2000);
2891 l_debit_concat_segments VARCHAR2(2000);
2892 l_credit_concat_segments VARCHAR2(2000);
2893 l_fail_count NUMBER := 0;
2894 l_ext_amount NUMBER := 0;
2895 l_acc_rule_duration NUMBER := 0;
2896 l_rule_start_date DATE;
2897 l_deferred_revenue_flag VARCHAR2(1);
2898 l_gl_date DATE;
2899 l_gl_date_valid DATE;
2900 l_cr_account_class ra_cust_trx_line_gl_dist.account_class%TYPE;
2901 l_revenue_type VARCHAR2(10);
2902 l_default_rule VARCHAR2(80);
2903 l_err_mesg VARCHAR2(2000);
2904
2905 l_warehouse_id NUMBER; -- Bug 1930302.
2906
2907 invalid_salesrep EXCEPTION;
2908 invalid_ccid EXCEPTION;
2909
2910 CURSOR c_assignment_count IS
2911 SELECT SUM(ar.amount), count(*)
2912 FROM ar_revenue_assignments ar,
2913 gl_sets_of_books sob
2914 WHERE customer_trx_line_id = p_customer_trx_line_id
2915 AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
2916 AND ar.period_set_name = sob.period_set_name
2917 AND ar.account_class = 'REV';
2918
2919 CURSOR c_revenue_assignment IS
2920 SELECT ar.gl_date
2921 , SUM(ar.amount) amount
2922 FROM ar_revenue_assignments ar,
2923 gl_sets_of_books sob
2927 AND ar.account_class = 'REV'
2924 WHERE ar.customer_trx_line_id = p_customer_trx_line_id
2925 AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
2926 AND ar.period_set_name = sob.period_set_name
2928 GROUP BY ar.gl_date
2929 ORDER BY gl_date ASC;
2930
2931 CURSOR c_salesrep_count IS
2932 SELECT COUNT(*)
2933 FROM ra_salesreps
2934 WHERE salesrep_id IN
2935 (SELECT salesrep_id
2936 FROM ra_cust_trx_line_salesreps
2937 WHERE customer_trx_line_id = p_customer_trx_line_id
2938 AND NVL(revenue_percent_split,0) <> 0
2939 GROUP by salesrep_id
2940 HAVING SUM(NVL(revenue_percent_split,0)) <> 0)
2941 AND salesrep_id = NVL(p_salesrep_id,salesrep_id);
2942
2943 CURSOR c_salesrep IS
2944 SELECT salesrep_id,
2945 SUM(NVL(revenue_percent_split,0)) revenue_percent_split,
2946 MAX(cust_trx_line_salesrep_id) max_id
2947 FROM ra_cust_trx_line_salesreps
2948 WHERE customer_trx_line_id = p_customer_trx_line_id
2949 AND salesrep_id = NVL(p_salesrep_id,salesrep_id)
2950 AND NVL(revenue_percent_split,0) <> 0
2951 GROUP by salesrep_id
2952 HAVING SUM(NVL(revenue_percent_split,0)) <> 0;
2953
2954 CURSOR c_last_salescredit IS
2955 SELECT NVL(revenue_percent_split,0)
2956 FROM ra_cust_trx_line_salesreps
2957 WHERE customer_trx_line_id = p_customer_trx_line_id
2958 AND cust_trx_line_salesrep_id = l_last_salescredit_id;
2959
2960 CURSOR c_line IS
2961 SELECT l.customer_trx_line_id,
2962 l.line_number,
2963 l.extended_amount,
2964 l.accounting_rule_duration,
2965 r.deferred_revenue_flag,
2966 l.warehouse_id,
2967 l.rule_start_date
2968 FROM ra_customer_trx_lines l,
2969 ra_rules r
2970 WHERE l.accounting_rule_id = r.rule_id
2971 AND l.customer_trx_line_id = p_customer_trx_line_id
2972 AND l.customer_trx_id = p_customer_trx_id
2973 AND l.line_type = 'LINE';
2974
2975 BEGIN
2976
2977 IF PG_DEBUG in ('Y', 'C') THEN
2978 arp_util.debug('AR_Revenue_Adjustment_PVT.debit_credit()+');
2979 arp_util.debug(' p_customer_trx_id = ' || p_customer_trx_id);
2980 arp_util.debug(' p_customer_trx_line_id = ' || p_customer_trx_line_id);
2981 arp_util.debug(' p_salesrep_id = ' || p_salesrep_id);
2982 arp_util.debug(' p_revenue_amount = ' || p_revenue_amount);
2983 arp_util.debug(' p_adjustment_id = ' || p_adjustment_id);
2984 END IF;
2985 FND_PROFILE.get('USER_ID',l_user_id);
2986 IF l_user_id IS NULL
2987 THEN
2988 l_user_id := 0;
2989 ELSE
2990 l_user_id := FND_GLOBAL.USER_ID;
2991 END IF;
2992
2993 l_debit_ccid := -1;
2994 l_credit_ccid := -1;
2995 l_dist_tot := 0;
2996 l_dist_pct_tot := 0; -- Bug 2487901
2997
2998 OPEN c_line;
2999
3000 -- Bug 1930302
3001
3002 FETCH c_line INTO l_line_id,
3003 l_line_number,
3004 l_ext_amount,
3005 l_acc_rule_duration,
3006 l_deferred_revenue_flag,
3007 l_warehouse_id,
3008 l_rule_start_date;
3009 CLOSE c_line;
3010
3011 /* Bug 2487901 - find overall percent to check pct rounding errors */
3012 IF (p_revenue_amount = l_ext_amount OR l_ext_amount = 0)
3013 THEN
3014 l_revenue_percent := 100;
3015 ELSE
3016 l_revenue_percent := ROUND(((p_revenue_amount / l_ext_amount) * 100),4);
3017 END IF;
3018
3019 OPEN c_assignment_count;
3020 FETCH c_assignment_count INTO l_assignment_total,
3021 l_no_of_assignments;
3022 CLOSE c_assignment_count;
3023
3024 OPEN c_salesrep_count;
3025 FETCH c_salesrep_count INTO l_no_of_salesreps;
3026 CLOSE c_salesrep_count;
3027
3028 IF l_no_of_salesreps = 0
3029 THEN
3030 RAISE invalid_salesrep;
3031 END IF;
3032 l_salesrep_count := 0;
3033
3034 FOR c2 in c_salesrep LOOP
3035
3036 l_last_salescredit_id := c2.max_id;
3037 OPEN c_last_salescredit;
3038 FETCH c_last_salescredit INTO l_latest_percent_split;
3039 CLOSE c_last_salescredit;
3040
3041 /* 6223281 - removed revenue_percent_split comparison
3042 as it was mismatching and resulting in null salesreps
3043 on UNEARN actions after SC xfer ones had occurred. */
3044 l_cust_trx_line_salesrep_id := c2.max_id;
3045
3046 l_salesrep_count := l_salesrep_count + 1;
3047
3048 /* Bug 2555736 - if salesrep specified salesrep percent is always 100
3049 otherwise use current salesrep in cursor percent */
3050 IF p_salesrep_id IS NOT NULL
3051 THEN
3052 l_salesrep_percent := 100;
3053 ELSE
3054 l_salesrep_percent := c2.revenue_percent_split;
3055 END IF;
3056
3057 --
3058 -- Initiate auto accounting procedure to find ccid to debit
3059 --
3060 -- Bug 1930302 : Added warehouse_id as 16th parameter.
3061
3065 ,p_customer_trx_id
3062 ARP_AUTO_ACCOUNTING.do_autoaccounting
3063 ('G'
3064 ,'REV'
3066 ,p_customer_trx_line_id
3067 ,NULL
3068 ,NULL
3069 ,NULL
3070 ,NULL
3071 ,NULL --l_dist_amount
3072 ,NULL
3073 ,null
3074 ,AR_RAAPI_UTIL.g_cust_trx_type_id
3075 ,c2.salesrep_id
3076 ,p_inventory_item_id
3077 ,p_memo_line_id
3078 ,l_warehouse_id
3079 ,l_debit_ccid
3080 ,l_debit_concat_segments
3081 ,l_fail_count);
3082 IF l_debit_ccid IS NULL
3083 THEN
3084 l_debit_ccid := FND_FLEX_EXT.GET_CCID
3085 ('SQLGL',
3086 'GL#',
3087 arp_global.chart_of_accounts_id,
3088 to_char(TRUNC(SYSDATE),'DD-MON-YYYY'),
3089 l_debit_concat_segments);
3090 END IF;
3091 IF (l_debit_ccid < 1 OR l_fail_count > 0)
3092 THEN
3093 l_concat_segments := l_debit_concat_segments;
3094 RAISE invalid_ccid;
3095 END IF;
3096
3097 IF p_credit_ccid IS NULL
3098 THEN
3099 --
3100 -- Initiate auto accounting procedure
3101 --
3102 --Bug 1930302 : Added warehouse_id as 16th parameter.
3103
3104 ARP_AUTO_ACCOUNTING.do_autoaccounting
3105 ('G'
3106 ,'UNEARN'
3107 ,p_customer_trx_id
3108 ,p_customer_trx_line_id
3109 ,NULL
3110 ,NULL
3111 ,NULL
3112 ,NULL
3113 ,NULL --l_dist_tot
3114 ,NULL
3115 ,NULL
3116 ,AR_RAAPI_UTIL.g_cust_trx_type_id
3117 ,c2.salesrep_id
3118 ,p_inventory_item_id
3119 ,p_memo_line_id
3120 ,l_warehouse_id
3121 ,l_credit_ccid
3122 ,l_credit_concat_segments
3123 ,l_fail_count);
3124 l_revenue_type := 'UNEARN';
3125 IF l_credit_ccid IS NULL
3126 THEN
3127 l_credit_ccid :=
3128 FND_FLEX_EXT.GET_CCID('SQLGL',
3129 'GL#',
3130 arp_global.chart_of_accounts_id,
3131 TO_CHAR(TRUNC(SYSDATE),'DD-MON-YYYY') ,
3132 l_credit_concat_segments);
3133 END IF;
3134 IF (l_credit_ccid < 1 OR l_fail_count > 0)
3135 THEN
3136 l_concat_segments := l_credit_concat_segments;
3137 RAISE invalid_ccid;
3138 END IF;
3139
3140 ELSE -- clearing account provided
3141 l_credit_ccid := p_credit_ccid;
3142 l_revenue_type := 'SUSPENSE';
3143
3144 END IF;
3145
3146 /* Bug 2487901 - Since assignment loop was moved inside salescredit loop
3147 the assignment count must be initialized here */
3148 l_assignment_count := 0;
3149
3150 FOR c1 in c_revenue_assignment LOOP
3151
3152 l_assignment_count := l_assignment_count + 1;
3153 -- If revenue is deferred, get the deferred GL date
3154 IF NVL(l_deferred_revenue_flag,'N') = 'Y'
3155 THEN
3156 l_gl_date := AR_RAAPI_UTIL.Deferred_GL_Date
3157 (p_start_date => p_gl_date,
3158 p_period_seq_no => l_assignment_count);
3159
3160
3161 /*--------------------------------------------------------
3162 | Bug # 2817503
3163 |
3164 | User passsed GL Date should override rule start date
3165 | if the line has an immediate rule.
3166 |
3167 | ORASHID 25-FEB-2003
3168 +-------------------------------------------------------*/
3169
3170 ELSIF (l_acc_rule_duration = 1) THEN
3171 l_gl_date := NVL(p_gl_date, c1.gl_date);
3172 ELSE
3173 l_gl_date := c1.gl_date;
3174 END IF;
3175
3176 /* Bug 2555736 - use derived salesrep percent */
3177 /* Bug 4675438: MOAC/SSA */
3178 IF l_assignment_total = 0
3179 THEN
3180 l_dist_amount := arpcurr.currround(
3184 l_dist_amount := arpcurr.currround(p_revenue_amount * l_salesrep_percent / 100 * c1.amount / l_assignment_total , AR_RAAPI_UTIL.g_trx_currency);
3181 p_revenue_amount * l_salesrep_percent
3182 / 100 / l_no_of_assignments , AR_RAAPI_UTIL.g_trx_currency);
3183 ELSE
3185 END IF;
3186 l_dist_tot := l_dist_tot + l_dist_amount;
3187 IF l_salesrep_count = l_no_of_salesreps AND
3188 l_assignment_count = l_no_of_assignments AND
3189 l_dist_tot <> p_revenue_amount
3190 THEN
3191 l_dist_amount := l_dist_amount + (p_revenue_amount - l_dist_tot);
3192 END IF;
3193 IF l_ext_amount = 0
3194 THEN
3195 l_dist_percent := ROUND ((100 / l_no_of_assignments /
3196 l_no_of_salesreps), 4);
3197 ELSE
3198 l_dist_percent := ROUND (((l_dist_amount / l_ext_amount) * 100), 4);
3199 END IF;
3200 IF ((l_dist_percent > -0.01 AND l_dist_percent < 0.01) OR
3201 l_dist_percent > 999 OR
3202 l_dist_percent < -999)
3203 THEN
3204 l_dist_percent := ROUND ((100 / l_no_of_assignments /
3205 l_no_of_salesreps), 4) * SIGN(l_dist_percent);
3206 END IF;
3207
3208 /* Bug 2487901 - keep running pct total and load rounding difference
3209 onto last distribution */
3210 l_dist_pct_tot := l_dist_pct_tot + l_dist_percent;
3211 IF l_salesrep_count = l_no_of_salesreps AND
3212 l_assignment_count = l_no_of_assignments AND
3213 l_dist_pct_tot <> l_revenue_percent
3214 THEN
3215 l_dist_percent := l_dist_percent +
3216 (l_revenue_percent - l_dist_pct_tot);
3217 END IF;
3218
3219 /* Bug 4675438: MOAC/SSA */
3220 l_dist_acctd_amount :=
3221 ARPCURR.functional_amount(
3222 amount => l_dist_amount
3223 , currency_code => arp_global.functional_currency
3224 , exchange_rate => AR_RAAPI_UTIL.g_exchange_rate
3225 , precision => NULL
3226 , min_acc_unit => NULL );
3227 /* Bug 2146970 - validate the GL date for all lines regardless
3228 of duration */
3229 l_gl_date_valid := AR_RAAPI_UTIL.bump_gl_date_if_closed
3230 (p_gl_date => l_gl_date);
3231 IF l_gl_date_valid IS NULL
3232 THEN
3233 FND_MESSAGE.set_name('AR','AR_RA_NO_OPEN_PERIODS');
3234 FND_MSG_PUB.Add;
3235 RAISE FND_API.G_EXC_ERROR;
3236 END IF;
3237
3238 --
3239 -- Step 1: Debit earned revenue account
3240 --
3241 insert_distribution ( p_customer_trx_line_id,
3242 l_debit_ccid,
3243 l_dist_percent * -1,
3244 l_dist_acctd_amount * -1,
3245 l_gl_date_valid,
3246 c1.gl_date,
3247 'REV',
3248 l_dist_amount * -1,
3249 l_cust_trx_line_salesrep_id,
3250 p_customer_trx_id,
3251 p_adjustment_id,
3252 p_user_generated_flag);
3253
3254 --
3255 -- Step 2: Credit unearned or line revenue transfer clearing account
3256 --
3257
3258 insert_distribution ( p_customer_trx_line_id,
3259 l_credit_ccid,
3260 l_dist_percent,
3261 l_dist_acctd_amount,
3262 l_gl_date_valid,
3263 c1.gl_date,
3264 l_revenue_type,
3265 l_dist_amount,
3266 l_cust_trx_line_salesrep_id,
3267 p_customer_trx_id,
3268 p_adjustment_id,
3269 p_user_generated_flag);
3270
3271 END LOOP; -- assignments loop
3272
3273 END LOOP; -- sales credit loop
3274 IF PG_DEBUG in ('Y', 'C') THEN
3275 arp_util.debug('AR_Revenue_Adjustment_PVT.debit_credit()-');
3276 END IF;
3277
3278 EXCEPTION
3279
3280 WHEN invalid_salesrep THEN
3281 FND_MESSAGE.SET_NAME(application => 'AR',
3282 name => 'AR_RA_NO_REV_SALES_CREDIT');
3283 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
3284 FND_MSG_PUB.Add;
3285 RAISE FND_API.G_EXC_ERROR;
3286 WHEN invalid_ccid THEN
3287 FND_MSG_PUB.Add;
3288 FND_MESSAGE.SET_NAME(application => 'AR',
3289 name => 'AR_RA_INVALID_CODE_COMB');
3290 FND_MESSAGE.SET_TOKEN('CODE_COMBINATION',l_concat_segments);
3291 FND_MSG_PUB.Add;
3292 RAISE FND_API.G_EXC_ERROR;
3293 WHEN OTHERS then
3294 IF (SQLCODE = -20001)
3295 THEN
3296 IF PG_DEBUG in ('Y', 'C') THEN
3297 arp_util.debug
3298 ('20001 error at AR_Revenue_Adjustment_PVT.debit_credit()');
3299 END IF;
3300 RAISE FND_API.G_EXC_ERROR;
3301 ELSE
3302 IF PG_DEBUG in ('Y', 'C') THEN
3303 arp_util.debug('debit_credit: ' || 'Unexpected error '||sqlerrm||
3307 END IF;
3304 ' at AR_Revenue_Adjustment_PVT.debit_credit()+');
3305 END IF;
3306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3308
3309 END debit_credit;
3310
3311 /* 5021530 - This logic handles no-rules cases
3312 where sreps exist. (old logic that is dependent on SRs) */
3313 PROCEDURE no_rule_debit_credit
3314 (p_customer_trx_line_id IN NUMBER
3315 ,p_customer_trx_id IN NUMBER
3316 ,p_salesrep_id IN NUMBER
3317 ,p_revenue_amount IN NUMBER
3318 ,p_gl_date IN DATE
3319 ,p_credit_ccid IN NUMBER
3320 ,p_inventory_item_id IN NUMBER
3321 ,p_memo_line_id IN NUMBER
3322 ,p_adjustment_id IN NUMBER
3323 ,p_user_generated_flag IN VARCHAR2)
3324 IS
3325 l_user_id NUMBER := 0;
3326 l_last_salescredit_id NUMBER := 0;
3327 l_cust_trx_line_salesrep_id NUMBER := 0;
3328 l_latest_percent_split NUMBER := 0;
3329 l_debit_ccid NUMBER := 0;
3330 l_credit_ccid NUMBER := 0;
3331 l_line_id NUMBER := 0;
3332 l_line_number NUMBER := 0;
3333 l_dist_amount NUMBER := 0;
3334 l_dist_acctd_amount NUMBER := 0;
3335 l_dist_percent NUMBER := 0;
3336 l_salesrep_count NUMBER := 0;
3337 l_no_of_salesreps NUMBER := 0;
3338 l_salesrep_percent NUMBER := 0; -- Bug 2555736
3339 l_dist_tot NUMBER := 0;
3340 l_concat_segments VARCHAR2(2000);
3341 l_new_concat_segments VARCHAR2(2000);
3342 l_fail_count NUMBER := 0;
3343 l_ext_amount NUMBER := 0;
3344 l_acc_rule_duration NUMBER := 0;
3345 l_gl_date DATE;
3346 l_cr_account_class ra_cust_trx_line_gl_dist.account_class%TYPE;
3347 l_revenue_type VARCHAR2(10);
3348
3349 l_warehouse_id NUMBER; -- Bug 1930302.
3350
3351 invalid_salesrep EXCEPTION;
3352 invalid_ccid EXCEPTION;
3353 invalid_other EXCEPTION;
3354
3355 CURSOR c_salesrep_count IS
3356 SELECT COUNT(*)
3357 FROM ra_salesreps
3358 WHERE salesrep_id IN
3359 (SELECT salesrep_id
3360 FROM ra_cust_trx_line_salesreps
3361 WHERE customer_trx_line_id = p_customer_trx_line_id
3362 AND NVL(revenue_percent_split,0) <> 0
3363 GROUP by salesrep_id
3364 HAVING SUM(NVL(revenue_percent_split,0)) <> 0)
3365 AND salesrep_id = NVL(p_salesrep_id,salesrep_id);
3366
3367 CURSOR c_salesrep IS
3368 SELECT salesrep_id,
3369 SUM(NVL(revenue_percent_split,0)) revenue_percent_split,
3370 MAX(cust_trx_line_salesrep_id) max_id
3371 FROM ra_cust_trx_line_salesreps
3372 WHERE customer_trx_line_id = p_customer_trx_line_id
3373 AND salesrep_id = NVL(p_salesrep_id,salesrep_id)
3374 AND NVL(revenue_percent_split,0) <> 0
3375 GROUP by salesrep_id
3376 HAVING SUM(NVL(revenue_percent_split,0)) <> 0;
3377
3378 CURSOR c_last_salescredit IS
3379 SELECT NVL(revenue_percent_split,0)
3380 FROM ra_cust_trx_line_salesreps
3381 WHERE customer_trx_line_id = p_customer_trx_line_id
3382 AND cust_trx_line_salesrep_id = l_last_salescredit_id;
3383
3384 -- Bug 1930302
3385
3386 CURSOR c_line IS
3387 SELECT customer_trx_line_id,
3388 line_number,
3389 extended_amount,
3390 warehouse_id,
3391 accounting_rule_duration
3392 FROM ra_customer_trx_lines
3393 WHERE customer_trx_line_id = p_customer_trx_line_id
3394 AND customer_trx_id = p_customer_trx_id
3395 AND line_type = 'LINE';
3396
3397 BEGIN
3398 IF PG_DEBUG in ('Y', 'C') THEN
3399 arp_util.debug('AR_Revenue_Adjustment_PVT.no_rule_debit_credit()+');
3400 arp_util.debug(' p_customer_trx_id = ' || p_customer_trx_id);
3401 arp_util.debug(' p_customer_trx_line_id = ' || p_customer_trx_line_id);
3402 arp_util.debug(' p_salesrep_id = ' || p_salesrep_id);
3403 arp_util.debug(' p_revenue_amount = ' || p_revenue_amount);
3404 arp_util.debug(' p_adjustment_id = ' || p_adjustment_id);
3405 END IF;
3406 FND_PROFILE.get('USER_ID',l_user_id);
3407 IF l_user_id IS NULL
3408 THEN
3409 l_user_id := 0;
3410 ELSE
3411 l_user_id := FND_GLOBAL.USER_ID;
3412 END IF;
3413
3414 l_debit_ccid := -1;
3415 l_credit_ccid := -1;
3416 l_dist_tot := 0;
3417
3418 -- Bug 1930302
3419
3420 OPEN c_line;
3421 FETCH c_line INTO l_line_id,
3422 l_line_number,
3423 l_ext_amount,
3424 l_warehouse_id,
3425 l_acc_rule_duration;
3426 CLOSE c_line;
3427
3428 OPEN c_salesrep_count;
3429 FETCH c_salesrep_count INTO l_no_of_salesreps;
3430 CLOSE c_salesrep_count;
3431
3432 IF l_no_of_salesreps = 0
3433 THEN
3434 RAISE invalid_salesrep;
3435 END IF;
3436
3437 l_salesrep_count := 0;
3438
3439 FOR c1 in c_salesrep LOOP
3440
3441 l_last_salescredit_id := c1.max_id;
3445
3442 OPEN c_last_salescredit;
3443 FETCH c_last_salescredit INTO l_latest_percent_split;
3444 CLOSE c_last_salescredit;
3446 /* 6223281 - Removed revenue_percent_split comparison
3447 as it does not match when SC xfers and adjustments
3448 occurr on the same invoice line. */
3449 l_cust_trx_line_salesrep_id := c1.max_id;
3450
3451 /* Bug 2555736 - if salesrep specified salesrep percent is always 100
3452 otherwise use current salesrep in cursor percent */
3453 IF p_salesrep_id IS NOT NULL
3454 THEN
3455 l_salesrep_percent := 100;
3456 ELSE
3457 l_salesrep_percent := c1.revenue_percent_split;
3458 END IF;
3459
3460 /* Bug 4675438: MOAC/SSA */
3461 l_dist_amount := arpcurr.currround(p_revenue_amount * l_salesrep_percent / 100 , AR_RAAPI_UTIL.g_trx_currency);
3462 l_dist_tot := l_dist_tot + l_dist_amount;
3463 l_salesrep_count := l_salesrep_count + 1;
3464 IF l_salesrep_count = l_no_of_salesreps AND
3465 l_dist_tot <> p_revenue_amount
3466 THEN
3467 l_dist_amount := l_dist_amount + (p_revenue_amount - l_dist_tot);
3468 END IF;
3469 IF l_ext_amount = 0
3470 THEN
3471 l_dist_percent := ROUND ((100 / l_no_of_salesreps) ,4);
3472 ELSE
3473 l_dist_percent := ROUND (((l_dist_amount / l_ext_amount) * 100), 4);
3474 END IF;
3475 IF ((l_dist_percent > -0.01 AND l_dist_percent < 0.01) OR
3476 l_dist_percent > 999 OR
3477 l_dist_percent < -999)
3478 THEN
3479 l_dist_percent := ROUND ((100 / l_no_of_salesreps), 4)
3480 * SIGN(l_dist_percent);
3481 END IF;
3482 /* Bug 4675438: MOAC/SSA */
3483 l_dist_acctd_amount :=
3484 ARPCURR.functional_amount(
3485 amount => l_dist_amount
3486 , currency_code => arp_global.functional_currency
3487 , exchange_rate => AR_RAAPI_UTIL.g_exchange_rate
3488 , precision => NULL
3489 , min_acc_unit => NULL );
3490 --
3491 -- Step 1: Debit earned revenue
3492 --
3493
3494 --
3495 -- Initiate auto accounting procedure to find ccid to debit
3496 --
3497 -- Bug 1930302 : Added warehouse_id as 16th parameter.
3498
3499 ARP_AUTO_ACCOUNTING.do_autoaccounting('G'
3500 ,'REV'
3501 ,p_customer_trx_id
3502 ,p_customer_trx_line_id
3503 ,NULL
3504 ,NULL
3505 ,NULL
3506 ,NULL
3507 ,l_dist_amount
3508 ,NULL
3509 ,NULL
3510 ,AR_RAAPI_UTIL.g_cust_trx_type_id
3511 ,c1.salesrep_id
3512 ,p_inventory_item_id
3513 ,p_memo_line_id
3514 ,l_warehouse_id
3515 ,l_debit_ccid
3516 ,l_concat_segments
3517 ,l_fail_count);
3518 IF l_debit_ccid IS NULL
3519 THEN
3520 l_debit_ccid := FND_FLEX_EXT.GET_CCID
3521 ('SQLGL',
3522 'GL#',
3523 arp_global.chart_of_accounts_id,
3524 to_char(p_gl_date,'DD-MON-YYYY') ,
3525 l_concat_segments);
3526 END IF;
3527
3528 IF l_debit_ccid = -1 OR
3529 l_debit_ccid = 0 OR
3530 l_fail_count > 0
3531 THEN
3532 RAISE invalid_ccid;
3533 END IF;
3534
3535 insert_distribution ( p_customer_trx_line_id,
3536 l_debit_ccid,
3537 l_dist_percent * -1,
3538 l_dist_acctd_amount * -1,
3539 p_gl_date,
3540 p_gl_date,
3541 'REV',
3542 l_dist_amount * -1,
3543 l_cust_trx_line_salesrep_id,
3544 p_customer_trx_id,
3545 p_adjustment_id,
3546 p_user_generated_flag);
3547
3548 --
3549 -- Step 2: Credit unearned revenue or line transfer clearing account
3550 --
3551 IF p_credit_ccid IS NULL
3552 THEN
3553 --
3554 -- Initiate auto accounting procedure
3555 --
3556 -- Bug 1930302 : Added warehouse_id as 16th parameter.
3557
3558 ARP_AUTO_ACCOUNTING.do_autoaccounting('G'
3559 ,'UNEARN'
3560 ,p_customer_trx_id
3561 ,p_customer_trx_line_id
3562 ,NULL
3563 ,NULL
3567 ,NULL
3564 ,NULL
3565 ,NULL
3566 ,l_dist_tot
3568 ,NULL
3569 ,AR_RAAPI_UTIL.g_cust_trx_type_id
3570 ,c1.salesrep_id
3571 ,p_inventory_item_id
3572 ,p_memo_line_id
3573 ,l_warehouse_id
3574 ,l_credit_ccid
3575 ,l_concat_segments
3576 ,l_fail_count);
3577
3578 IF l_credit_ccid IS NULL
3579 THEN
3580 l_credit_ccid :=
3581 FND_FLEX_EXT.GET_CCID ('SQLGL',
3582 'GL#',
3583 arp_global.chart_of_accounts_id,
3584 TO_CHAR(p_gl_date,'DD-MON-YYYY') ,
3585 l_concat_segments);
3586 END IF;
3587
3588 IF l_credit_ccid = -1 OR
3589 l_credit_ccid = 0 OR
3590 l_fail_count > 0
3591 THEN
3592 RAISE invalid_ccid;
3593 END IF;
3594 l_revenue_type := 'UNEARN';
3595
3596 ELSE -- i.e. transferring revenue between lines
3597 l_credit_ccid := p_credit_ccid;
3598 l_revenue_type := 'SUSPENSE';
3599 END IF;
3600
3601 insert_distribution ( p_customer_trx_line_id,
3602 l_credit_ccid,
3603 l_dist_percent,
3604 l_dist_acctd_amount,
3605 p_gl_date,
3606 p_gl_date,
3607 l_revenue_type,
3608 l_dist_amount,
3609 l_cust_trx_line_salesrep_id,
3610 p_customer_trx_id,
3611 p_adjustment_id,
3612 p_user_generated_flag);
3613
3614 END LOOP; -- sales credit loop
3615
3616 IF PG_DEBUG in ('Y', 'C') THEN
3617 arp_util.debug('AR_Revenue_Adjustment_PVT.no_rule_debit_credit()-');
3618 END IF;
3619
3620 EXCEPTION
3621
3622 WHEN invalid_salesrep THEN
3623 FND_MESSAGE.SET_NAME(application => 'AR',
3624 name => 'AR_RA_NO_REV_SALES_CREDIT');
3625 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
3626 FND_MSG_PUB.Add;
3627 RAISE FND_API.G_EXC_ERROR;
3628 WHEN invalid_ccid THEN
3629 FND_MSG_PUB.Add;
3630 FND_MESSAGE.SET_NAME(application => 'AR',
3631 name => 'AR_RA_INVALID_CODE_COMB');
3632 FND_MESSAGE.SET_TOKEN('CODE_COMBINATION',l_concat_segments);
3633 FND_MSG_PUB.Add;
3634 RAISE FND_API.G_EXC_ERROR;
3635 WHEN OTHERS then
3636 IF (SQLCODE = -20001)
3637 THEN
3638 IF PG_DEBUG in ('Y', 'C') THEN
3639 arp_util.debug
3640 ('20001 error at AR_Revenue_Adjustment_PVT.no_rule_debit_credit()');
3641 END IF;
3642 RAISE FND_API.G_EXC_ERROR;
3643 ELSE
3644 IF PG_DEBUG in ('Y', 'C') THEN
3645 arp_util.debug('no_rule_debit_credit: ' || 'Unexpected error '||sqlerrm||
3646 ' at AR_Revenue_Adjustment_PVT.no_rule_debit_credit()+');
3647 END IF;
3648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3649 END IF;
3650
3651 END no_rule_debit_credit;
3652
3653 /* 5021530 - this routine handles no-rules cases where no salesreps
3654 exist (new logic - does not require salesreps)
3655
3656 */
3657 PROCEDURE no_rule_debit_credit_no_sr
3658 (p_customer_trx_line_id IN NUMBER
3659 ,p_customer_trx_id IN NUMBER
3660 ,p_salesrep_id IN NUMBER
3661 ,p_revenue_amount IN NUMBER
3662 ,p_gl_date IN DATE
3663 ,p_credit_ccid IN NUMBER
3664 ,p_inventory_item_id IN NUMBER
3665 ,p_memo_line_id IN NUMBER
3666 ,p_adjustment_id IN NUMBER
3667 ,p_user_generated_flag IN VARCHAR2)
3668 IS
3669 l_user_id NUMBER := 0;
3670 l_last_salescredit_id NUMBER := 0;
3671 l_cust_trx_line_salesrep_id NUMBER := 0;
3672 l_latest_percent_split NUMBER := 0;
3673 l_debit_ccid NUMBER := 0;
3674 l_credit_ccid NUMBER := 0;
3675 l_dist_amount NUMBER := 0;
3676 l_dist_acctd_amount NUMBER := 0;
3677 l_dist_percent NUMBER := 0;
3678 l_concat_segments VARCHAR2(2000);
3679 l_new_concat_segments VARCHAR2(2000);
3680 l_fail_count NUMBER := 0;
3681 l_revenue_type VARCHAR2(10);
3682
3683 invalid_ccid EXCEPTION;
3684 invalid_other EXCEPTION;
3685
3686 /* 5021530 - cursor based off non-rev_adj dists
3687 in REV account class
3688
3689 Just check for ccid in returned row(s) and if null,
3690 call autoaccounting
3691 */
3692 /* 5644810 - Added NVL to sum(gl.amount), sum(gl.acctd_amount) and sum(gl.percent) in SELECT ststement */
3696 l.extended_amount,
3693 CURSOR c_dist IS
3694 SELECT l.customer_trx_line_id,
3695 l.line_number,
3697 l.warehouse_id,
3698 l.accounting_rule_duration,
3699 gl.code_combination_id,
3700 NVL(sum(gl.amount),0) amount,
3701 NVL(sum(gl.acctd_amount),0) acctd_amount,
3702 NVL(sum(gl.percent),0) percent
3703 FROM ra_customer_trx_lines l,
3704 ra_cust_trx_line_gl_dist gl
3705 WHERE l.customer_trx_line_id = p_customer_trx_line_id
3706 AND l.customer_trx_id = p_customer_trx_id
3707 AND l.line_type = 'LINE'
3708 AND l.customer_trx_line_id = gl.customer_trx_line_id (+)
3709 AND gl.account_class (+) = 'REV'
3710 AND gl.revenue_adjustment_id (+) IS NULL
3711 GROUP BY l.customer_trx_line_id, l.line_number, l.extended_amount,
3712 l.warehouse_id, l.accounting_rule_duration,
3713 gl.code_combination_id;
3714
3715 BEGIN
3716 IF PG_DEBUG in ('Y', 'C') THEN
3717 arp_util.debug('AR_Revenue_Adjustment_PVT.no_rule_debit_credit_no_sr()+');
3718 END IF;
3719 FND_PROFILE.get('USER_ID',l_user_id);
3720 IF l_user_id IS NULL
3721 THEN
3722 l_user_id := 0;
3723 ELSE
3724 l_user_id := FND_GLOBAL.USER_ID;
3725 END IF;
3726
3727 l_debit_ccid := -1;
3728 l_credit_ccid := -1;
3729
3730 FOR gld in c_dist LOOP
3731
3732 /* set amounts for adjustment */ /* bug 5644810 added IF condition */
3733 IF gld.code_combination_id IS NULL THEN
3734 l_dist_amount := arpcurr.currround(p_revenue_amount ,AR_RAAPI_UTIL.g_trx_currency);
3735 ELSE
3736 l_dist_amount := arpcurr.currround(p_revenue_amount * gld.percent / 100,
3737 AR_RAAPI_UTIL.g_trx_currency);
3738 END IF;
3739 l_dist_percent := ROUND (((l_dist_amount / gld.extended_amount) * 100), 4);
3740
3741 l_dist_acctd_amount :=
3742 ARPCURR.functional_amount(
3743 amount => l_dist_amount
3744 , currency_code => arp_global.functional_currency
3745 , exchange_rate => AR_RAAPI_UTIL.g_exchange_rate
3746 , precision => NULL
3747 , min_acc_unit => NULL );
3748 --
3749 -- Step 1: Debit earned revenue
3750 --
3751
3752 IF gld.code_combination_id IS NULL
3753 THEN
3754 /* No REV distributions to work from,
3755 call autoaccounting to get one */
3756 ARP_AUTO_ACCOUNTING.do_autoaccounting('G'
3757 ,'REV'
3758 ,p_customer_trx_id
3759 ,p_customer_trx_line_id
3760 ,NULL
3761 ,NULL
3762 ,NULL
3763 ,NULL
3764 ,l_dist_amount
3765 ,NULL
3766 ,NULL
3767 ,AR_RAAPI_UTIL.g_cust_trx_type_id
3768 ,NULL -- srep id
3769 ,p_inventory_item_id
3770 ,p_memo_line_id
3771 ,gld.warehouse_id
3772 ,l_debit_ccid
3773 ,l_concat_segments
3774 ,l_fail_count);
3775 IF l_debit_ccid IS NULL
3776 THEN
3777 l_debit_ccid := FND_FLEX_EXT.GET_CCID
3778 ('SQLGL',
3779 'GL#',
3780 arp_global.chart_of_accounts_id,
3781 to_char(p_gl_date,'DD-MON-YYYY') ,
3782 l_concat_segments);
3783 END IF;
3784
3785 IF l_debit_ccid = -1 OR
3786 l_debit_ccid = 0 OR
3787 l_fail_count > 0
3788 THEN
3789 RAISE invalid_ccid;
3790 END IF;
3791
3792 ELSE
3793 /* We retrieved at least one REV account, use it */
3794 l_debit_ccid := gld.code_combination_id;
3795 END IF;
3796
3797 insert_distribution ( p_customer_trx_line_id,
3798 l_debit_ccid,
3799 l_dist_percent,
3800 l_dist_acctd_amount,
3801 p_gl_date,
3802 p_gl_date,
3803 'REV',
3804 l_dist_amount,
3805 NULL, -- srep dist id
3806 p_customer_trx_id,
3807 p_adjustment_id,
3808 p_user_generated_flag);
3809
3810 --
3811 -- Step 2: Credit unearned revenue or line transfer clearing account
3812 --
3813 /* 5021530 - always call autoaccounting for UNEARN as there
3814 is no guarantee that there will be a balance or that
3815 the percent will be useful.
3816
3820 THEN
3817 It is also very unusual to actually override UNEARN
3818 accounts manually. */
3819 IF p_credit_ccid IS NULL
3821 --
3822 -- Initiate auto accounting procedure
3823 --
3824 -- Bug 1930302 : Added warehouse_id as 16th parameter.
3825
3826 ARP_AUTO_ACCOUNTING.do_autoaccounting('G'
3827 ,'UNEARN'
3828 ,p_customer_trx_id
3829 ,p_customer_trx_line_id
3830 ,NULL
3831 ,NULL
3832 ,NULL
3833 ,NULL
3834 ,l_dist_amount
3835 ,NULL
3836 ,NULL
3837 ,AR_RAAPI_UTIL.g_cust_trx_type_id
3838 ,NULL -- srep_Id
3839 ,p_inventory_item_id
3840 ,p_memo_line_id
3841 ,gld.warehouse_id
3842 ,l_credit_ccid
3843 ,l_concat_segments
3844 ,l_fail_count);
3845
3846 IF l_credit_ccid IS NULL
3847 THEN
3848 l_credit_ccid :=
3849 FND_FLEX_EXT.GET_CCID ('SQLGL',
3850 'GL#',
3851 arp_global.chart_of_accounts_id,
3852 TO_CHAR(p_gl_date,'DD-MON-YYYY') ,
3853 l_concat_segments);
3854 END IF;
3855
3856 IF l_credit_ccid = -1 OR
3857 l_credit_ccid = 0 OR
3858 l_fail_count > 0
3859 THEN
3860 RAISE invalid_ccid;
3861 END IF;
3862 l_revenue_type := 'UNEARN';
3863
3864 ELSE -- i.e. transferring revenue between lines
3865 l_credit_ccid := p_credit_ccid;
3866 l_revenue_type := 'SUSPENSE';
3867 END IF;
3868
3869 insert_distribution ( p_customer_trx_line_id,
3870 l_credit_ccid,
3871 l_dist_percent * -1,
3872 l_dist_acctd_amount * -1,
3873 p_gl_date,
3874 p_gl_date,
3875 l_revenue_type,
3876 l_dist_amount * -1,
3877 NULL, -- srep_dist_id
3878 p_customer_trx_id,
3879 p_adjustment_id,
3880 p_user_generated_flag);
3881
3882
3883 END LOOP;
3884
3885 IF PG_DEBUG in ('Y', 'C') THEN
3886 arp_util.debug('AR_Revenue_Adjustment_PVT.no_rule_debit_credit_no_sr()-');
3887 END IF;
3888
3889 EXCEPTION
3890 WHEN invalid_ccid THEN
3891 FND_MSG_PUB.Add;
3892 FND_MESSAGE.SET_NAME(application => 'AR',
3893 name => 'AR_RA_INVALID_CODE_COMB');
3894 FND_MESSAGE.SET_TOKEN('CODE_COMBINATION',l_concat_segments);
3895 FND_MSG_PUB.Add;
3896 RAISE FND_API.G_EXC_ERROR;
3897 WHEN OTHERS then
3898 IF (SQLCODE = -20001)
3899 THEN
3900 IF PG_DEBUG in ('Y', 'C') THEN
3901 arp_util.debug
3902 ('20001 error at AR_Revenue_Adjustment_PVT.no_rule_debit_credit_no_sr()');
3903 END IF;
3904 RAISE FND_API.G_EXC_ERROR;
3905 ELSE
3906 IF PG_DEBUG in ('Y', 'C') THEN
3907 arp_util.debug('no_rule_debit_credit_no_sr: ' || 'Unexpected error '||sqlerrm||
3908 ' at AR_Revenue_Adjustment_PVT.no_rule_debit_credit()+');
3909 END IF;
3910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3911 END IF;
3912
3913 END no_rule_debit_credit_no_sr;
3914
3915
3916 -----------------------------------------------------------------------
3917 -- API name : Transfer_Revenue_Between_Lines
3918 -- Type : Private
3919 -- Function : Transfers a specified amount of revenue between
3920 -- specified transaction lines via a clearing account
3921 -- Pre-reqs : Sufficient earned revenue must exist.
3922 -- Parameters :
3923 -- IN : p_api_version NUMBER Required
3924 -- p_init_msg_list VARCHAR2 Optional
3925 -- Default = FND_API.G_FALSE
3926 -- p_commit VARCHAR2 Optional
3927 -- Default = FND_API.G_FALSE
3928 -- p_validation_level NUMBER Optional
3929 -- Default = FND_API.G_VALID_LEVEL_FULL
3930 -- p_rev_adj_rec Rev_Adj_Rec_Type Required
3931 -- OUT NOCOPY : x_return_status VARCHAR2(1)
3932 -- x_msg_count NUMBER
3933 -- x_msg_data VARCHAR2(2000)
3934 -- x_adjustment_id NUMBER
3935 -- x_adjustment_number VARCHAR2
3936 -- .
3937 -- .
3938 -- Version : Current version 2.0
3939 -- IN parameters consolidated into new record type
3943 --
3940 -- Initial version 1.0
3941 --
3942 -- Notes : AutoAccounting used for revenue debits and credits
3944
3945 -----------------------------------------------------------------------
3946 PROCEDURE Transfer_Revenue_Between_Lines
3947 ( p_api_version IN NUMBER
3948 ,p_init_msg_list IN VARCHAR2
3949 ,p_commit IN VARCHAR2
3950 ,p_validation_level IN NUMBER
3951 ,x_return_status OUT NOCOPY VARCHAR2
3952 ,x_msg_count OUT NOCOPY NUMBER
3953 ,x_msg_data OUT NOCOPY VARCHAR2
3954 ,p_rev_adj_rec IN Rev_Adj_Rec_Type
3955 ,x_adjustment_id OUT NOCOPY NUMBER
3956 ,x_adjustment_number OUT NOCOPY VARCHAR2)
3957 IS
3958 l_api_name CONSTANT VARCHAR2(30)
3959 := 'Transfer_Revenue_Between_Lines';
3960 l_api_version CONSTANT NUMBER := 2.0;
3961 l_rev_adj_rec AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type;
3962 l_tax_rate_count NUMBER := 0;
3963 l_lines_from_total NUMBER := 0;
3964 l_line_from_count NUMBER := 0;
3965 l_no_of_lines_from NUMBER := 0;
3966 l_line_adjustable NUMBER;
3967 l_total_adjusted NUMBER := 0;
3968 l_line_id NUMBER;
3969 l_line_number NUMBER;
3970 l_line_salesrep_amount NUMBER;
3971 l_line_rev_total NUMBER;
3972 l_adj_inv_total NUMBER;
3973 l_lines_to_total NUMBER := 0;
3974 l_line_to_count NUMBER := 0;
3975 l_no_of_lines_to NUMBER := 0;
3976 l_revenue_amount NUMBER := 0;
3977 l_revenue_amount_prorata NUMBER := 0;
3978 l_credit_ccid NUMBER;
3979
3980 l_warehouse_id NUMBER; -- Bug 1930302.
3981
3982 invalid_tax EXCEPTION;
3983 invalid_same_lines EXCEPTION;
3984 invalid_lines EXCEPTION;
3985
3986 CURSOR c_lines_from_total IS
3987 SELECT NVL(SUM(d.amount),0)
3988 FROM ra_cust_trx_line_gl_dist d
3989 ,mtl_item_categories mic
3990 ,ra_customer_trx_lines l
3991 WHERE d.customer_trx_line_id = l.customer_trx_line_id
3992 AND d.account_class = 'REV'
3993 AND l.line_type = 'LINE'
3994 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
3995 AND d.account_class IN ('REV','UNEARN','UNBILL')
3996 AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
3997 l.customer_trx_line_id)
3998 AND NVL(l.inventory_item_id,0) =
3999 NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
4000 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
4001 AND l.inventory_item_id = mic.inventory_item_id(+)
4002 AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
4003 DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
4004 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
4005 AND DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,
4006 DECODE(AR_RAAPI_UTIL.g_from_inventory_item_id,NULL,
4007 DECODE(AR_RAAPI_UTIL.g_from_cust_trx_line_id,NULL,
4008 NVL(l.accounting_rule_duration,0),0),0),0) <= 1;
4009
4010 CURSOR c_lines_from IS
4011 SELECT l.line_number
4012 ,l.customer_trx_line_id
4013 ,l.memo_line_id
4014 ,l.inventory_item_id
4015 ,l.accounting_rule_id
4016 ,NVL(SUM(d.amount),0) amount
4017 FROM ra_cust_trx_line_gl_dist d
4018 ,mtl_item_categories mic
4019 ,ra_customer_trx_lines l
4020 WHERE d.customer_trx_line_id = l.customer_trx_line_id
4021 AND d.account_class = 'REV'
4022 AND l.line_type = 'LINE'
4023 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
4024 AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
4025 l.customer_trx_line_id)
4026 AND NVL(l.inventory_item_id,0) =
4027 NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
4028 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
4029 AND l.inventory_item_id = mic.inventory_item_id(+)
4030 AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
4031 DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
4032 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
4033 AND DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,
4034 DECODE(AR_RAAPI_UTIL.g_from_inventory_item_id,NULL,
4035 DECODE(AR_RAAPI_UTIL.g_from_cust_trx_line_id,NULL,
4036 NVL(l.accounting_rule_duration,0),0),0),0) <= 1
4037 GROUP BY l.line_number
4038 ,l.customer_trx_line_id
4039 ,l.memo_line_id
4040 ,l.inventory_item_id
4041 ,l.accounting_rule_id;
4042
4043 CURSOR c_line_salesrep_amount IS
4044 SELECT SUM(NVL(revenue_amount_split,0))
4045 FROM ra_cust_trx_line_salesreps
4046 WHERE customer_trx_line_id = l_line_id
4047 AND customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
4051 CURSOR c_tax_rate_count IS
4048 AND salesrep_id = NVL(AR_RAAPI_UTIL.g_from_salesrep_id,salesrep_id)
4049 AND NVL(revenue_salesgroup_id, -9999) = NVL(AR_RAAPI_UTIL.g_from_salesgroup_id,NVL(revenue_salesgroup_id, -9999));
4050
4052 SELECT NVL(COUNT(DISTINCT tax.item_exception_rate_id||
4053 tax.tax_exemption_id|| tax.vat_tax_id||
4054 tax.sales_tax_id|| tax.tax_rate|| tax.tax_precedence),0)
4055 FROM ra_customer_trx_lines line
4056 ,mtl_item_categories mic
4057 ,ra_customer_trx_lines tax
4058 WHERE line.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
4059 AND tax.line_type = 'TAX'
4060 AND line.customer_trx_line_id = tax.link_to_cust_trx_line_id
4061 AND line.customer_trx_line_id IN
4062 (NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id, line.customer_trx_line_id),
4063 NVL(AR_RAAPI_UTIL.g_to_cust_trx_line_id, line.customer_trx_line_id))
4064 AND NVL(line.inventory_item_id,0) IN
4065 (NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(line.inventory_item_id,0)),
4066 NVL(AR_RAAPI_UTIL.g_to_inventory_item_id,NVL(line.inventory_item_id,0)))
4067 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
4068 AND line.inventory_item_id = mic.inventory_item_id(+)
4069 AND (NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
4070 DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id) OR
4071 NVL(AR_RAAPI_UTIL.g_to_category_id,0) =
4072 DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,0,mic.category_id))
4073 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id;
4074
4075 CURSOR c_lines_to_total IS
4076 SELECT NVL(SUM(d.amount),0) amount
4077 FROM ra_cust_trx_line_gl_dist d
4078 ,mtl_item_categories mic
4079 ,ra_customer_trx_lines l
4080 WHERE d.customer_trx_line_id = l.customer_trx_line_id
4081 AND d.account_class = 'REV'
4082 AND l.line_type = 'LINE'
4083 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
4084 AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_to_cust_trx_line_id,
4085 l.customer_trx_line_id)
4086 AND NVL(l.inventory_item_id,0) =
4087 NVL(AR_RAAPI_UTIL.g_to_inventory_item_id,NVL(l.inventory_item_id,0))
4088 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
4089 AND l.inventory_item_id = mic.inventory_item_id(+)
4090 AND NVL(AR_RAAPI_UTIL.g_to_category_id,0) =
4091 DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,0,mic.category_id)
4092 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
4093 AND DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,
4094 DECODE(AR_RAAPI_UTIL.g_to_inventory_item_id,NULL,
4095 DECODE(AR_RAAPI_UTIL.g_to_cust_trx_line_id,NULL,
4096 NVL(l.accounting_rule_duration,0),2),2),2) > 1;
4097
4098 CURSOR c_line_to_count IS
4099 SELECT COUNT(*)
4100 FROM mtl_item_categories mic
4101 ,ra_customer_trx_lines l
4102 WHERE l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
4103 AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_to_cust_trx_line_id,
4104 l.customer_trx_line_id)
4105 AND NVL(l.inventory_item_id,0) =
4106 NVL(AR_RAAPI_UTIL.g_to_inventory_item_id,NVL(l.inventory_item_id,0))
4107 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
4108 AND l.inventory_item_id = mic.inventory_item_id(+)
4109 AND NVL(AR_RAAPI_UTIL.g_to_category_id,0) =
4110 DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,0,mic.category_id)
4111 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
4112 AND l.line_type = 'LINE'
4113 AND DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,
4114 DECODE(AR_RAAPI_UTIL.g_to_inventory_item_id,NULL,
4115 DECODE(AR_RAAPI_UTIL.g_to_cust_trx_line_id,NULL,
4116 NVL(l.accounting_rule_duration,0),2),2),2) > 1;
4117
4118 CURSOR c_lines_to IS
4119 SELECT l.line_number
4120 ,l.customer_trx_line_id
4121 ,l.memo_line_id
4122 ,l.inventory_item_id
4123 ,l.accounting_rule_id
4124 ,l.accounting_rule_duration
4125 ,SUM(d.amount) amount
4126 FROM ra_cust_trx_line_gl_dist d
4127 ,mtl_item_categories mic
4128 ,ra_customer_trx_lines l
4129 WHERE d.customer_trx_line_id = l.customer_trx_line_id
4130 AND d.account_class = 'REV'
4131 AND l.line_type = 'LINE'
4132 AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
4133 AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_to_cust_trx_line_id,
4134 l.customer_trx_line_id)
4135 AND NVL(l.inventory_item_id,0) =
4136 NVL(AR_RAAPI_UTIL.g_to_inventory_item_id,NVL(l.inventory_item_id,0))
4137 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
4138 AND l.inventory_item_id = mic.inventory_item_id(+)
4139 AND NVL(AR_RAAPI_UTIL.g_to_category_id,0) =
4140 DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,0,mic.category_id)
4141 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
4142 AND DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,
4143 DECODE(AR_RAAPI_UTIL.g_to_inventory_item_id,NULL,
4144 DECODE(AR_RAAPI_UTIL.g_to_cust_trx_line_id,NULL,
4145 NVL(l.accounting_rule_duration,0),2),2),2) > 1
4146 GROUP BY l.line_number
4147 ,l.customer_trx_line_id
4148 ,l.memo_line_id
4152
4149 ,l.inventory_item_id
4150 ,l.accounting_rule_id
4151 ,l.accounting_rule_duration;
4153 BEGIN
4154 IF PG_DEBUG in ('Y', 'C') THEN
4155 arp_util.debug('AR_Revenue_Adjustment_PVT.Transfer_Revenue_Between_Lines()+');
4156 END IF;
4157 -- Standard Start of API savepoint
4158 SAVEPOINT Transfer_Rev_Between_Lines_PVT;
4159 -- Standard call to check for call compatibility.
4160 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
4161 p_api_version ,
4162 l_api_name ,
4163 G_PKG_NAME )
4164 THEN
4165 IF PG_DEBUG in ('Y', 'C') THEN
4166 arp_util.debug('Transfer_Revenue_Between_Lines: ' || 'Unexpected error '||sqlerrm||
4167 ' at AR_Revenue_Adjustment_PVT.Transfer_Revenue_Between_Lines()+');
4168 END IF;
4169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4170 END IF;
4171 -- Initialize message list if p_init_msg_list is set to TRUE.
4172 IF FND_API.to_Boolean( p_init_msg_list )
4173 THEN
4174 FND_MSG_PUB.initialize;
4175 END IF;
4176 -- Initialize API return status to success
4177 x_return_status := FND_API.G_RET_STS_SUCCESS;
4178
4179 BEGIN
4180 IF PG_DEBUG in ('Y', 'C') THEN
4181 arp_util.debug('AR_Revenue_Adjustment_PVT.Transfer_Revenue_Between_Lines(2)+');
4182 END IF;
4183
4184 l_rev_adj_rec := p_rev_adj_rec;
4185
4186 /*Bug 6731185 JVARKEY Making sure GL date has no timestamp*/
4187 l_rev_adj_rec.gl_date := trunc(p_rev_adj_rec.gl_date);
4188
4189 l_rev_adj_rec.adjustment_type := 'LL';
4190
4191 AR_RAAPI_UTIL.Constant_System_Values;
4192 AR_RAAPI_UTIL.Initialize_Globals;
4193 AR_RAAPI_UTIL.Validate_Parameters (p_init_msg_list => FND_API.G_FALSE
4194 ,p_rev_adj_rec => l_rev_adj_rec
4195 ,p_validation_level => p_validation_level
4196 ,x_return_status => x_return_status
4197 ,x_msg_count => x_msg_count
4198 ,x_msg_data => x_msg_data);
4199 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4200 THEN
4201 RAISE FND_API.G_EXC_ERROR;
4202 END IF;
4203
4204 IF NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,-100) =
4205 NVL(AR_RAAPI_UTIL.g_to_cust_trx_line_id,-200)
4206 THEN
4207 FND_MESSAGE.SET_NAME (application => 'AR',
4208 name => 'AR_RA_SAME_FROM_AND_TO_LINES');
4209 RAISE invalid_same_lines;
4210 END IF;
4211 IF NVL(AR_RAAPI_UTIL.g_from_category_id,-100) =
4212 NVL(AR_RAAPI_UTIL.g_to_category_id,-200)
4213 THEN
4214 FND_MESSAGE.SET_NAME (application => 'AR',
4215 name => 'AR_RA_SAME_FROM_AND_TO_CATS');
4216 RAISE invalid_same_lines;
4217 END IF;
4218 IF NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,-100) =
4219 NVL(AR_RAAPI_UTIL.g_to_inventory_item_id,-200)
4220 THEN
4221 FND_MESSAGE.SET_NAME (application => 'AR',
4222 name => 'AR_RA_SAME_FROM_AND_TO_ITEMS');
4223 RAISE invalid_same_lines;
4224 END IF;
4225
4226 OPEN c_tax_rate_count;
4227 FETCH c_tax_rate_count INTO l_tax_rate_count;
4228 CLOSE c_tax_rate_count;
4229
4230 IF l_tax_rate_count > 1
4231 THEN
4232 RAISE invalid_tax;
4233 END IF;
4234
4235 OPEN c_lines_from_total;
4236 FETCH c_lines_from_total INTO l_lines_from_total;
4237 CLOSE c_lines_from_total;
4238
4239 AR_RAAPI_UTIL.Validate_Amount
4240 (p_init_msg_list => FND_API.G_FALSE
4241 ,p_customer_trx_line_id => AR_RAAPI_UTIL.g_from_cust_trx_line_id
4242 ,p_adjustment_type => 'LL'
4243 ,p_amount_mode => p_rev_adj_rec.amount_mode
4244 ,p_customer_trx_id => AR_RAAPI_UTIL.g_customer_trx_id
4245 ,p_salesrep_id => AR_RAAPI_UTIL.g_from_salesrep_id
4246 ,p_salesgroup_id => AR_RAAPI_UTIL.g_from_salesgroup_id
4247 ,p_sales_credit_type => p_rev_adj_rec.sales_credit_type
4248 ,p_item_id => AR_RAAPI_UTIL.g_from_inventory_item_id
4249 ,p_category_id => AR_RAAPI_UTIL.g_from_category_id
4250 ,p_revenue_amount_in => p_rev_adj_rec.amount
4251 ,p_revenue_percent => p_rev_adj_rec.percent
4252 ,p_revenue_amount_out => l_revenue_amount
4253 ,p_adjustable_amount_out => l_adj_inv_total
4254 ,p_line_count_out => l_no_of_lines_from
4255 ,x_return_status => x_return_status
4256 ,x_msg_count => x_msg_count
4257 ,x_msg_data => x_msg_data);
4258 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4259 THEN
4260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4261 ELSIF x_return_status = FND_API.G_RET_STS_ERROR
4262 THEN
4263 RAISE FND_API.G_EXC_ERROR;
4264 END IF;
4265 l_rev_adj_rec.amount := l_revenue_amount;
4266
4267 OPEN c_lines_to_total;
4268 FETCH c_lines_to_total INTO l_lines_to_total;
4269 CLOSE c_lines_to_total;
4270
4271 OPEN c_line_to_count;
4272 FETCH c_line_to_count INTO l_no_of_lines_to;
4273 CLOSE c_line_to_count;
4274
4275 IF (l_no_of_lines_from = 0 OR l_no_of_lines_to = 0) AND
4279 RAISE invalid_lines;
4276 AR_RAAPI_UTIL.g_from_cust_trx_line_id IS NULL AND
4277 AR_RAAPI_UTIL.g_to_cust_trx_line_id IS NULL
4278 THEN
4280 END IF;
4281
4282 --
4283 -- Create revenue adjustment record
4284 --
4285 create_adjustment
4286 (p_rev_adj_rec => l_rev_adj_rec
4287 ,x_adjustment_id => x_adjustment_id
4288 ,x_adjustment_number => x_adjustment_number);
4289
4290 FOR c1 IN c_lines_from LOOP
4291 l_line_id := c1.customer_trx_line_id;
4292 OPEN c_line_salesrep_amount;
4293 FETCH c_line_salesrep_amount INTO l_line_salesrep_amount;
4294 CLOSE c_line_salesrep_amount;
4295
4296 IF c1.amount > 0
4297 THEN
4298 l_line_adjustable := LEAST(c1.amount,l_line_salesrep_amount);
4299 ELSIF c1.amount < 0
4300 THEN
4301 l_line_adjustable := GREATEST(c1.amount,l_line_salesrep_amount);
4302 ELSE
4303 l_line_adjustable := 0;
4304 END IF;
4305 IF l_line_adjustable <> 0
4306 THEN
4307 l_revenue_amount_prorata := ROUND(l_revenue_amount *
4308 l_line_adjustable / l_adj_inv_total,AR_RAAPI_UTIL.g_trx_precision);
4309 l_line_from_count := l_line_from_count + 1;
4310 l_total_adjusted := l_total_adjusted + l_revenue_amount_prorata;
4311
4312 IF l_line_from_count = l_no_of_lines_from AND
4313 l_total_adjusted <> l_revenue_amount
4314 THEN
4315 l_revenue_amount_prorata := l_revenue_amount_prorata +
4316 l_revenue_amount - l_total_adjusted;
4317 END IF;
4318 IF c1.accounting_rule_id IS NOT NULL
4319 THEN
4320 debit_credit (c1.customer_trx_line_id
4321 ,AR_RAAPI_UTIL.g_customer_trx_id
4322 ,AR_RAAPI_UTIL.g_from_salesrep_id
4323 ,l_revenue_amount_prorata
4324 ,l_rev_adj_rec.gl_date
4325 ,arp_global.sysparam.rev_transfer_clear_ccid
4326 ,c1.inventory_item_id
4327 ,c1.memo_line_id
4328 ,x_adjustment_id);
4329 ELSE
4330 no_rule_debit_credit (c1.customer_trx_line_id
4331 ,AR_RAAPI_UTIL.g_customer_trx_id
4332 ,AR_RAAPI_UTIL.g_from_salesrep_id
4333 ,l_revenue_amount_prorata
4334 ,l_rev_adj_rec.gl_date
4335 ,arp_global.sysparam.rev_transfer_clear_ccid
4336 ,c1.inventory_item_id
4337 ,c1.memo_line_id
4338 ,x_adjustment_id);
4339 END IF;
4340 END IF;
4341
4342 END LOOP; -- c_lines_from loop
4343
4344 l_total_adjusted := 0;
4345
4346 FOR c1 IN c_lines_to LOOP
4347 IF l_lines_to_total = 0
4348 THEN
4349 l_revenue_amount_prorata :=
4350 ROUND(l_revenue_amount / l_no_of_lines_to,
4351 AR_RAAPI_UTIL.g_trx_precision);
4352 ELSE
4353 l_revenue_amount_prorata := ROUND(l_revenue_amount * c1.amount /
4354 l_lines_to_total, AR_RAAPI_UTIL.g_trx_precision);
4355 END IF;
4356 l_line_to_count := l_line_to_count + 1;
4357 l_total_adjusted := l_total_adjusted + l_revenue_amount_prorata;
4358 IF l_line_to_count = l_no_of_lines_to AND
4359 l_total_adjusted <> l_revenue_amount
4360 THEN
4361 l_revenue_amount_prorata := l_revenue_amount_prorata
4362 + l_revenue_amount - l_total_adjusted;
4363 END IF;
4364
4365 IF c1.accounting_rule_id IS NOT NULL
4366 THEN
4367 IF NVL(c1.accounting_rule_duration,0) > 1
4368 THEN
4369 cr_target_line_unearned(c1.customer_trx_line_id
4370 ,AR_RAAPI_UTIL.g_customer_trx_id
4371 ,l_revenue_amount_prorata
4372 ,l_rev_adj_rec.gl_date
4373 ,c1.inventory_item_id
4374 ,c1.memo_line_id
4375 ,x_adjustment_id);
4376 l_credit_ccid := NULL;
4377 ELSE
4378 l_credit_ccid := arp_global.sysparam.rev_transfer_clear_ccid;
4379 END IF;
4380
4381 debit_credit (c1.customer_trx_line_id
4382 ,AR_RAAPI_UTIL.g_customer_trx_id
4383 ,NULL
4384 ,l_revenue_amount_prorata * -1
4385 ,l_rev_adj_rec.gl_date
4386 ,l_credit_ccid
4387 ,c1.inventory_item_id
4388 ,c1.memo_line_id
4389 ,x_adjustment_id);
4390 ELSE
4391 no_rule_debit_credit (c1.customer_trx_line_id
4392 ,AR_RAAPI_UTIL.g_customer_trx_id
4393 ,NULL
4394 ,l_revenue_amount_prorata * -1
4398 ,c1.memo_line_id
4395 ,l_rev_adj_rec.gl_date
4396 ,arp_global.sysparam.rev_transfer_clear_ccid
4397 ,c1.inventory_item_id
4399 ,x_adjustment_id);
4400 END IF;
4401 reset_dist_percent( c1.customer_trx_line_id);
4402
4403 END LOOP; -- c_lines_to loop
4404
4405 EXCEPTION
4406 WHEN invalid_same_lines THEN
4407 FND_MSG_PUB.Add;
4408 RAISE FND_API.G_EXC_ERROR;
4409 WHEN invalid_lines THEN
4410 FND_MESSAGE.SET_NAME (application => 'AR',
4411 name => 'AR_RA_NO_TSFR_LINES_AVAIL');
4412 FND_MSG_PUB.Add;
4413 RAISE FND_API.G_EXC_ERROR;
4414 WHEN invalid_tax THEN
4415 FND_MESSAGE.SET_NAME (application => 'AR',
4416 name => 'AR_RA_TAX_TREATMENTS_VARY');
4417 FND_MSG_PUB.Add;
4418 RAISE FND_API.G_EXC_ERROR;
4419 WHEN OTHERS then
4420 IF (SQLCODE = -20001)
4421 THEN
4422 IF PG_DEBUG in ('Y', 'C') THEN
4423 arp_util.debug
4424 ('20001 error at AR_Revenue_Adjustment_PVT.Transfer_Revenue_Between_Lines()');
4425 END IF;
4426 RAISE FND_API.G_EXC_ERROR;
4427 ELSE
4428 IF PG_DEBUG in ('Y', 'C') THEN
4429 arp_util.debug('Transfer_Revenue_Between_Lines: ' || 'Unexpected error '||sqlerrm||
4430 ' at AR_Revenue_Adjustment_PVT.Transfer_Revenue_Between_Lines()+');
4431 END IF;
4432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4433 END IF;
4434 END;
4435 --
4436 -- End of Inner Block
4437 --
4438
4439 -- Standard check of p_commit.
4440 IF FND_API.To_Boolean( p_commit )
4441 THEN
4442 COMMIT WORK;
4443 END IF;
4444 -- Standard call to get message count and if count is 1, get message info.
4445 FND_MSG_PUB.Count_And_Get
4446 (p_encoded => FND_API.G_FALSE,
4447 p_count => x_msg_count,
4448 p_data => x_msg_data);
4449 IF PG_DEBUG in ('Y', 'C') THEN
4450 arp_util.debug('AR_Revenue_Adjustment_PVT.Transfer_Revenue_Between_Lines()-');
4451 END IF;
4452 EXCEPTION
4453 WHEN FND_API.G_EXC_ERROR THEN
4454 ROLLBACK TO Transfer_Rev_Between_Lines_PVT;
4455 x_return_status := FND_API.G_RET_STS_ERROR ;
4456 FND_MSG_PUB.Count_And_Get
4457 (p_encoded => FND_API.G_FALSE,
4458 p_count => x_msg_count,
4459 p_data => x_msg_data);
4460 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4461 IF PG_DEBUG in ('Y', 'C') THEN
4462 arp_util.debug('Transfer_Revenue_Between_Lines: ' || 'Unexpected error '||sqlerrm||
4463 ' at AR_Revenue_Adjustment_PVT.Transfer_Revenue_Between_Lines()+');
4464 END IF;
4465 ROLLBACK TO Transfer_Rev_Between_Lines_PVT;
4466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4467 FND_MSG_PUB.Count_And_Get
4468 (p_encoded => FND_API.G_FALSE,
4469 p_count => x_msg_count,
4470 p_data => x_msg_data);
4471 WHEN OTHERS THEN
4472 IF (SQLCODE = -20001)
4473 THEN
4474 IF PG_DEBUG in ('Y', 'C') THEN
4475 arp_util.debug('Transfer_Revenue_Between_Lines: ' || '20001 error '||
4476 ' at AR_Revenue_Adjustment_PVT.Transfer_Revenue_Between_Lines()+');
4477 END IF;
4478 x_return_status := FND_API.G_RET_STS_ERROR ;
4479 ELSE
4480 IF PG_DEBUG in ('Y', 'C') THEN
4481 arp_util.debug('Transfer_Revenue_Between_Lines: ' || 'Unexpected error '||sqlerrm||
4482 ' at AR_Revenue_Adjustment_PVT.Transfer_Revenue_Between_Lines()+');
4483 END IF;
4484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4485 IF FND_MSG_PUB.Check_Msg_Level
4486 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4487 THEN
4488 FND_MSG_PUB.Add_Exc_Msg
4489 ( G_PKG_NAME ,
4490 l_api_name
4491 );
4492 END IF;
4493 END IF;
4494 ROLLBACK TO Transfer_Rev_Between_Lines_PVT;
4495 FND_MSG_PUB.Count_And_Get
4496 (p_encoded => FND_API.G_FALSE,
4497 p_count => x_msg_count,
4498 p_data => x_msg_data);
4499
4500 END Transfer_Revenue_Between_Lines;
4501
4502 PROCEDURE reset_dist_percent
4503 (p_customer_trx_line_id IN NUMBER)
4504 IS
4505 l_no_of_lines NUMBER;
4506 l_total NUMBER;
4507 l_counter NUMBER;
4508 l_percent NUMBER;
4509 l_percent_total NUMBER;
4510
4511 CURSOR c_total IS
4512 SELECT NVL(SUM(amount),0)
4513 FROM ra_cust_trx_line_gl_dist
4514 WHERE customer_trx_line_id = p_customer_trx_line_id
4515 AND account_set_flag = 'N'
4516 AND account_class IN ('REV','UNEARN')
4517 AND NVL(amount,0) <> 0;
4518
4519 CURSOR c_count IS
4520 SELECT COUNT(*)
4521 FROM ra_cust_trx_line_gl_dist
4522 WHERE customer_trx_line_id = p_customer_trx_line_id
4526 CURSOR c_dist_lines IS
4523 AND account_set_flag = 'N'
4524 AND NVL(amount,0) <> 0;
4525
4527 SELECT rowid, amount
4528 FROM ra_cust_trx_line_gl_dist
4529 WHERE customer_trx_line_id = p_customer_trx_line_id
4530 AND account_set_flag = 'N'
4531 AND NVL(amount,0) <> 0;
4532
4533 BEGIN
4534 IF PG_DEBUG in ('Y', 'C') THEN
4535 arp_util.debug('AR_Revenue_Adjustment_PVT.reset_dist_percent()+');
4536 END IF;
4537 OPEN c_total;
4538 FETCH c_total into l_total;
4539 CLOSE c_total;
4540 OPEN c_count;
4541 FETCH c_count into l_no_of_lines;
4542 CLOSE c_count;
4543 l_counter := 0;
4544 l_percent_total := 0;
4545 FOR c1 IN c_dist_lines LOOP
4546 l_counter := l_counter + 1;
4547 IF l_counter = l_no_of_lines
4548 THEN
4549 l_percent := 100 - l_percent_total;
4550 ELSE
4551 l_percent := ROUND(c1.amount / l_total * 100,4);
4552 l_percent_total := l_percent_total + l_percent;
4553 END IF;
4554 UPDATE ra_cust_trx_line_gl_dist
4555 SET percent = l_percent
4556 WHERE rowid = c1.rowid;
4557 END LOOP;
4558 IF PG_DEBUG in ('Y', 'C') THEN
4559 arp_util.debug('AR_Revenue_Adjustment_PVT.reset_dist_percent()-');
4560 END IF;
4561 EXCEPTION
4562 WHEN OTHERS THEN
4563 IF (SQLCODE = -20001)
4564 THEN
4565 IF PG_DEBUG in ('Y', 'C') THEN
4566 arp_util.debug
4567 ('20001 error at AR_Revenue_Adjustment_PVT.reset_dist_percent()');
4568 END IF;
4569 RAISE FND_API.G_EXC_ERROR;
4570 ELSE
4571 IF PG_DEBUG in ('Y', 'C') THEN
4572 arp_util.debug('reset_dist_percent: ' || 'Unexpected error '||sqlerrm||
4573 ' at AR_Revenue_Adjustment_PVT.reset_dist_percent()+');
4574 END IF;
4575 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4576 END IF;
4577 END reset_dist_percent;
4578
4579 PROCEDURE create_adjustment
4580 (p_rev_adj_rec IN Rev_Adj_Rec_Type
4581 ,x_adjustment_id OUT NOCOPY NUMBER
4582 ,x_adjustment_number OUT NOCOPY VARCHAR2)
4583 IS
4584 l_adjustment_number VARCHAR2(20);
4585
4586 CURSOR c_adjustment_id IS
4587 SELECT ar_revenue_adjustments_s1.NEXTVAL
4588 FROM dual;
4589
4590 CURSOR c_adjustment_number IS
4591 SELECT ar_revenue_adjustments_s2.NEXTVAL
4592 FROM dual;
4593
4594 BEGIN
4595 IF PG_DEBUG in ('Y', 'C') THEN
4596 arp_util.debug('AR_Revenue_Adjustment_PVT.create_adjustment()+');
4597 END IF;
4598
4599 OPEN c_adjustment_id;
4600 FETCH c_adjustment_id INTO x_adjustment_id;
4601 CLOSE c_adjustment_id;
4602
4603 OPEN c_adjustment_number;
4604 FETCH c_adjustment_number INTO x_adjustment_number;
4605 CLOSE c_adjustment_number;
4606
4607 INSERT INTO ar_revenue_adjustments
4608 (revenue_adjustment_id
4609 ,revenue_adjustment_number
4610 ,customer_trx_id
4611 ,application_date
4612 ,from_salesrep_id
4613 ,to_salesrep_id
4614 ,from_salesgroup_id
4615 ,to_salesgroup_id
4616 ,type
4617 ,sales_credit_type
4618 ,amount_mode
4619 ,amount
4620 ,percent
4621 ,line_selection_mode
4622 ,from_category_id
4623 ,to_category_id
4624 ,from_inventory_item_id
4625 ,to_inventory_item_id
4626 ,from_cust_trx_line_id
4627 ,to_cust_trx_line_id
4628 ,gl_date
4629 ,reason_code
4630 ,comments
4631 ,attribute_category
4632 ,attribute1
4633 ,attribute2
4634 ,attribute3
4635 ,attribute4
4636 ,attribute5
4637 ,attribute6
4638 ,attribute7
4639 ,attribute8
4640 ,attribute9
4641 ,attribute10
4642 ,attribute11
4643 ,attribute12
4644 ,attribute13
4645 ,attribute14
4646 ,attribute15
4647 ,status
4648 ,creation_date
4649 ,created_by
4650 ,last_update_date
4651 ,last_updated_by
4652 ,org_id ) -- Bug 4607673
4653 VALUES
4654 (x_adjustment_id
4655 ,x_adjustment_number
4656 ,AR_RAAPI_UTIL.g_customer_trx_id
4657 ,SYSDATE
4658 ,AR_RAAPI_UTIL.g_from_salesrep_id
4659 ,AR_RAAPI_UTIL.g_to_salesrep_id
4660 ,AR_RAAPI_UTIL.g_from_salesgroup_id
4661 ,AR_RAAPI_UTIL.g_to_salesgroup_id
4662 ,p_rev_adj_rec.adjustment_type
4663 ,p_rev_adj_rec.sales_credit_type
4664 ,p_rev_adj_rec.amount_mode
4665 ,p_rev_adj_rec.amount
4666 ,p_rev_adj_rec.percent
4667 ,p_rev_adj_rec.line_selection_mode
4668 ,AR_RAAPI_UTIL.g_from_category_id
4672 ,AR_RAAPI_UTIL.g_from_cust_trx_line_id
4669 ,AR_RAAPI_UTIL.g_to_category_id
4670 ,AR_RAAPI_UTIL.g_from_inventory_item_id
4671 ,AR_RAAPI_UTIL.g_to_inventory_item_id
4673 ,AR_RAAPI_UTIL.g_to_cust_trx_line_id
4674 /*Bug 6731185 JVARKEY Making sure GL date has no timestamp*/
4675 ,trunc(p_rev_adj_rec.gl_date)
4676 /*,p_rev_adj_rec.gl_date*/
4677 ,p_rev_adj_rec.reason_code
4678 ,p_rev_adj_rec.comments
4679 ,p_rev_adj_rec.attribute_category
4680 ,p_rev_adj_rec.attribute1
4681 ,p_rev_adj_rec.attribute2
4682 ,p_rev_adj_rec.attribute3
4683 ,p_rev_adj_rec.attribute4
4684 ,p_rev_adj_rec.attribute5
4685 ,p_rev_adj_rec.attribute6
4686 ,p_rev_adj_rec.attribute7
4687 ,p_rev_adj_rec.attribute8
4688 ,p_rev_adj_rec.attribute9
4689 ,p_rev_adj_rec.attribute10
4690 ,p_rev_adj_rec.attribute11
4691 ,p_rev_adj_rec.attribute12
4692 ,p_rev_adj_rec.attribute13
4693 ,p_rev_adj_rec.attribute14
4694 ,p_rev_adj_rec.attribute15
4695 ,'A'
4696 ,SYSDATE
4697 ,FND_GLOBAL.user_id
4698 ,SYSDATE
4699 ,FND_GLOBAL.user_id
4700 ,arp_standard.sysparm.org_id ); -- Bug 4607673
4701 IF PG_DEBUG in ('Y', 'C') THEN
4702 arp_util.debug('AR_Revenue_Adjustment_PVT.create_adjustment()-');
4703 END IF;
4704
4705 EXCEPTION
4706 WHEN OTHERS THEN
4707 IF (SQLCODE = -20001)
4708 THEN
4709 IF PG_DEBUG in ('Y', 'C') THEN
4710 arp_util.debug
4711 ('20001 error at AR_Revenue_Adjustment_PVT.create_adjustment()');
4712 END IF;
4713 RAISE FND_API.G_EXC_ERROR;
4714 ELSE
4715 IF PG_DEBUG in ('Y', 'C') THEN
4716 arp_util.debug('create_adjustment: ' || 'Unexpected error '||sqlerrm||
4717 ' at AR_Revenue_Adjustment_PVT.create_adjustment()+');
4718 END IF;
4719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4720 END IF;
4721 END create_adjustment;
4722
4723 PROCEDURE cr_target_line_unearned
4724 (p_customer_trx_line_id IN NUMBER
4725 ,p_customer_trx_id IN NUMBER
4726 ,p_revenue_amount IN NUMBER
4727 ,p_gl_date IN DATE
4728 ,p_inventory_item_id IN NUMBER
4729 ,p_memo_line_id IN NUMBER
4730 ,p_adjustment_id IN NUMBER)
4731 IS
4732 l_user_id NUMBER := 0;
4733 l_debit_ccid NUMBER := 0;
4734 l_credit_ccid NUMBER := 0;
4735 l_dist_amount NUMBER := 0;
4736 l_dist_acctd_amount NUMBER := 0;
4737 l_dist_percent NUMBER := 0;
4738 l_salesrep_count NUMBER := 0;
4739 l_no_of_salesreps NUMBER := 0;
4740 l_dist_tot NUMBER := 0;
4741 l_concat_segments VARCHAR2(2000);
4742 l_fail_count NUMBER := 0;
4743 l_ext_amount NUMBER := 0;
4744 l_acc_rule_duration NUMBER := 0;
4745 l_revenue_type VARCHAR2(10);
4746
4747 l_warehouse_id NUMBER;
4748
4749 invalid_ccid EXCEPTION;
4750
4751 CURSOR c_salesrep_count IS
4752 SELECT COUNT(*)
4753 FROM ra_salesreps
4754 WHERE salesrep_id IN
4755 (SELECT salesrep_id
4756 FROM ra_cust_trx_line_salesreps
4757 WHERE customer_trx_line_id = p_customer_trx_line_id
4758 AND NVL(revenue_percent_split,0) <> 0
4759 GROUP by salesrep_id
4760 HAVING SUM(NVL(revenue_percent_split,0)) <> 0);
4761
4762 CURSOR c_line IS
4763 SELECT extended_amount, warehouse_id
4764 FROM ra_customer_trx_lines
4765 WHERE customer_trx_line_id = p_customer_trx_line_id;
4766
4767 CURSOR c_salesrep IS
4768 SELECT salesrep_id,
4769 SUM(NVL(revenue_percent_split,0)) revenue_percent_split,
4770 MAX(cust_trx_line_salesrep_id) max_id
4771 FROM ra_cust_trx_line_salesreps
4772 WHERE customer_trx_line_id = p_customer_trx_line_id
4773 AND NVL(revenue_percent_split,0) <> 0
4774 GROUP by salesrep_id
4775 HAVING SUM(NVL(revenue_percent_split,0)) <> 0;
4776
4777 BEGIN
4778 IF PG_DEBUG in ('Y', 'C') THEN
4779 arp_util.debug('AR_Revenue_Adjustment_PVT.cr_target_line_unearned()+');
4780 END IF;
4781 FND_PROFILE.get('USER_ID',l_user_id);
4782 IF l_user_id IS NULL
4783 THEN
4784 l_user_id := 0;
4785 ELSE
4786 l_user_id := FND_GLOBAL.USER_ID;
4787 END IF;
4788
4789 l_debit_ccid := -1;
4790 l_credit_ccid := -1;
4791 l_dist_tot := 0;
4792 l_salesrep_count := 0;
4793
4794 OPEN c_salesrep_count;
4795 FETCH c_salesrep_count INTO l_no_of_salesreps;
4796 CLOSE c_salesrep_count;
4797
4798 OPEN c_line;
4799 FETCH c_line INTO l_ext_amount, l_warehouse_id;
4800 CLOSE c_line;
4801
4802 FOR c1 in c_salesrep LOOP
4803
4804 l_dist_amount := arpcurr.currround(p_revenue_amount * c1.revenue_percent_split / 100 , AR_RAAPI_UTIL.g_trx_currency);
4808 l_dist_tot <> p_revenue_amount
4805 l_dist_tot := l_dist_tot + l_dist_amount;
4806 l_salesrep_count := l_salesrep_count + 1;
4807 IF l_salesrep_count = l_no_of_salesreps AND
4809 THEN
4810 l_dist_amount := l_dist_amount + (p_revenue_amount - l_dist_tot);
4811 END IF;
4812 IF l_ext_amount = 0
4813 THEN
4814 l_dist_percent := ROUND ((100 / l_no_of_salesreps), 4);
4815 ELSE
4816 l_dist_percent := ROUND (((l_dist_amount / l_ext_amount) * 100), 4);
4817 END IF;
4818 IF ((l_dist_percent > -0.01 AND l_dist_percent < 0.01) OR
4819 l_dist_percent > 999 OR
4820 l_dist_percent < -999)
4821 THEN
4822 l_dist_percent := ROUND ((100 / l_no_of_salesreps), 4)
4823 * SIGN(l_dist_percent);
4824 END IF;
4825 l_dist_acctd_amount :=
4826 ARPCURR.functional_amount(
4827 amount => l_dist_amount
4828 , currency_code => arp_global.functional_currency
4829 , exchange_rate => AR_RAAPI_UTIL.g_exchange_rate
4830 , precision => NULL
4831 , min_acc_unit => NULL );
4832 --
4833 -- Initiate auto accounting procedure
4834 --
4835 -- Bug 1930302 : Added warehouse_id as 16th parameter.
4836
4837 ARP_AUTO_ACCOUNTING.do_autoaccounting('G'
4838 ,'UNEARN'
4839 ,p_customer_trx_id
4840 ,p_customer_trx_line_id
4841 ,NULL
4842 ,NULL
4843 ,NULL
4844 ,NULL
4845 ,l_dist_tot
4846 ,NULL
4847 ,NULL
4848 ,AR_RAAPI_UTIL.g_cust_trx_type_id
4849 ,c1.salesrep_id
4850 ,p_inventory_item_id
4851 ,p_memo_line_id
4852 ,l_warehouse_id
4853 ,l_credit_ccid
4854 ,l_concat_segments
4855 ,l_fail_count);
4856
4857 IF l_credit_ccid IS NULL
4858 THEN
4859 l_credit_ccid := FND_FLEX_EXT.GET_CCID
4860 ('SQLGL',
4861 'GL#',
4862 arp_global.chart_of_accounts_id,
4863 TO_CHAR(p_gl_date,'DD-MON-YYYY'),
4864 l_concat_segments);
4865 END IF;
4866
4867 IF l_credit_ccid = -1 OR
4868 l_credit_ccid = 0 OR
4869 l_fail_count > 0
4870 THEN
4871 RAISE invalid_ccid;
4872 END IF;
4873
4874 insert_distribution ( p_customer_trx_line_id,
4875 l_credit_ccid,
4876 l_dist_percent,
4877 l_dist_acctd_amount,
4878 p_gl_date,
4879 p_gl_date,
4880 'UNEARN',
4881 l_dist_amount,
4882 NULL,
4883 p_customer_trx_id,
4884 p_adjustment_id);
4885
4886 l_debit_ccid := arp_global.sysparam.rev_transfer_clear_ccid;
4887
4888 insert_distribution ( p_customer_trx_line_id,
4889 l_debit_ccid,
4890 l_dist_percent * -1,
4891 l_dist_acctd_amount * -1,
4892 p_gl_date,
4893 p_gl_date,
4894 'SUSPENSE',
4895 l_dist_amount * -1,
4896 NULL,
4897 p_customer_trx_id,
4898 p_adjustment_id);
4899
4900 END LOOP; -- sales credit loop
4901 IF PG_DEBUG in ('Y', 'C') THEN
4902 arp_util.debug('AR_Revenue_Adjustment_PVT.cr_target_line_unearned()-');
4903 END IF;
4904
4905 EXCEPTION
4906
4907 WHEN invalid_ccid THEN
4908 FND_MSG_PUB.Add;
4909 FND_MESSAGE.SET_NAME(application => 'AR',
4910 name => 'AR_RA_INVALID_CODE_COMB');
4911 FND_MESSAGE.SET_TOKEN('CODE_COMBINATION',l_concat_segments);
4912 FND_MSG_PUB.Add;
4913 RAISE FND_API.G_EXC_ERROR;
4914 WHEN OTHERS THEN
4915 IF (SQLCODE = -20001)
4916 THEN
4917 IF PG_DEBUG in ('Y', 'C') THEN
4918 arp_util.debug
4919 ('20001 error at AR_Revenue_Adjustment_PVT.cr_target_line_unearned()');
4920 END IF;
4921 RAISE FND_API.G_EXC_ERROR;
4922 ELSE
4923 IF PG_DEBUG in ('Y', 'C') THEN
4924 arp_util.debug('cr_target_line_unearned: ' || 'Unexpected error '||sqlerrm||
4925 ' at AR_Revenue_Adjustment_PVT.cr_target_line_unearned()+');
4926 END IF;
4927 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4931
4928 END IF;
4929
4930 END cr_target_line_unearned;
4932 PROCEDURE insert_distribution(p_customer_trx_line_id IN NUMBER,
4933 p_ccid IN NUMBER,
4934 p_percent IN NUMBER,
4935 p_acctd_amount IN NUMBER,
4936 p_gl_date IN DATE,
4937 p_orig_gl_date IN DATE,
4938 p_account_class IN VARCHAR2,
4939 p_amount IN NUMBER,
4940 p_cust_trx_line_salesrep_id IN NUMBER,
4941 p_customer_trx_id IN NUMBER,
4942 p_adjustment_id IN NUMBER,
4943 p_user_generated_flag IN VARCHAR2,
4944 p_rounding_flag IN VARCHAR2
4945 DEFAULT NULL)
4946 IS
4947
4948 l_dist_id NUMBER;
4949 l_user_id NUMBER;
4950
4951 CURSOR cu_trx IS
4952 SELECT customer_trx_id
4953 FROM ra_customer_trx_lines
4954 WHERE customer_trx_line_id = p_customer_trx_line_id;
4955
4956 l_trx_id NUMBER;
4957 l_xla_event arp_xla_events.xla_events_type;
4958
4959 BEGIN
4960 IF PG_DEBUG in ('Y', 'C') THEN
4961 arp_util.debug('AR_Revenue_Adjustment_PVT.insert_distribution()+');
4962 arp_util.debug(' p_customer_trx_line_id = ' || p_customer_trx_line_id);
4963 arp_util.debug(' p_amount = ' || p_amount);
4964 arp_util.debug(' p_acctd_amount = ' || p_acctd_amount);
4965 arp_util.debug(' p_cust_trx_line_salesrep_id = ' || p_cust_trx_line_salesrep_id);
4966 arp_util.debug(' p_adjustment_id = ' || p_adjustment_id);
4967 arp_util.debug(' g_update_db_flag = ' || g_update_db_flag);
4968 arp_util.debug(' p_rounding_flag = ' || p_rounding_flag);
4969 END IF;
4970 IF p_amount <> 0 OR
4971 p_acctd_amount <> 0 /* 6325023 - rounding */
4972 THEN
4973
4974 FND_PROFILE.get('USER_ID',l_user_id);
4975 IF l_user_id IS NULL
4976 THEN
4977 l_user_id := 0;
4978 ELSE
4979 l_user_id := FND_GLOBAL.USER_ID;
4980 END IF;
4981
4982 IF g_update_db_flag = 'Y'
4983 THEN
4984 SELECT ra_cust_trx_line_gl_dist_s.NEXTVAL
4985 INTO l_dist_id
4986 FROM dual;
4987
4988 INSERT INTO ra_cust_trx_line_gl_dist
4989 (cust_trx_line_gl_dist_id
4990 ,customer_trx_line_id
4991 ,code_combination_id
4992 ,set_of_books_id
4993 ,last_update_date
4994 ,last_updated_by
4995 ,creation_date
4996 ,created_by
4997 ,percent
4998 ,amount
4999 ,gl_date
5000 ,original_gl_date
5001 ,cust_trx_line_salesrep_id
5002 ,account_class
5003 ,customer_trx_id
5004 ,account_set_flag
5005 ,acctd_amount
5006 ,posting_control_id
5007 ,revenue_adjustment_id
5008 ,user_generated_flag
5009 ,org_id -- Bug 4607673
5010 ,rounding_correction_flag
5011 ) VALUES
5012 (l_dist_id
5013 ,p_customer_trx_line_id
5014 ,p_ccid
5015 ,arp_global.sysparam.set_of_books_id
5016 ,SYSDATE
5017 ,l_user_id
5018 ,SYSDATE
5019 ,l_user_id
5020 ,p_percent
5021 ,p_amount
5022 /*Bug 6731185 JVARKEY Making sure GL date has no timestamp*/
5023 ,trunc(p_gl_date)
5024 /*,p_gl_date*/
5025 ,p_orig_gl_date
5026 ,p_cust_trx_line_salesrep_id
5027 ,p_account_class
5028 ,p_customer_trx_id
5029 ,'N'
5030 ,p_acctd_amount
5031 ,-3
5032 ,p_adjustment_id
5033 ,p_user_generated_flag
5034 ,arp_standard.sysparm.org_id
5035 ,p_rounding_flag); -- Bug 4607673
5036
5037 --{BUG#5064609 call XLA event
5038 OPEN cu_trx;
5039 FETCH cu_trx INTO l_trx_id;
5040 IF cu_trx%FOUND THEN
5041 l_xla_event.xla_from_doc_id := l_trx_id;
5042 l_xla_event.xla_to_doc_id := l_trx_id;
5043 l_xla_event.xla_req_id := NULL;
5044 l_xla_event.xla_dist_id := NULL;
5045 l_xla_event.xla_doc_table := 'CT';
5046 l_xla_event.xla_doc_event := NULL;
5047 l_xla_event.xla_mode := 'O';
5048 l_xla_event.xla_call := 'B';
5049
5050 ARP_XLA_EVENTS.CREATE_EVENTS(p_xla_ev_rec => l_xla_event );
5051 END IF;
5052 CLOSE cu_trx;
5053 --}
5054
5055 ELSE
5056 g_dist_count := g_dist_count + 1;
5057 g_ra_dist_tbl(g_dist_count).customer_trx_line_id := p_customer_trx_line_id;
5058 g_ra_dist_tbl(g_dist_count).code_combination_id := p_ccid;
5062 g_ra_dist_tbl(g_dist_count).creation_date := SYSDATE;
5059 g_ra_dist_tbl(g_dist_count).set_of_books_id := arp_global.sysparam.set_of_books_id;
5060 g_ra_dist_tbl(g_dist_count).last_update_date := SYSDATE;
5061 g_ra_dist_tbl(g_dist_count).last_updated_by := l_user_id;
5063 g_ra_dist_tbl(g_dist_count).created_by := l_user_id;
5064 g_ra_dist_tbl(g_dist_count).percent := p_percent;
5065 g_ra_dist_tbl(g_dist_count).amount := p_amount;
5066 /*Bug 6731185 JVARKEY Making sure GL date has no timestamp*/
5067 g_ra_dist_tbl(g_dist_count).gl_date := trunc(p_gl_date);
5068 /*g_ra_dist_tbl(g_dist_count).gl_date := p_gl_date;*/
5069 g_ra_dist_tbl(g_dist_count).cust_trx_line_salesrep_id := p_cust_trx_line_salesrep_id;
5070 g_ra_dist_tbl(g_dist_count).account_class := p_account_class;
5071 g_ra_dist_tbl(g_dist_count).customer_trx_id := p_customer_trx_id;
5072 g_ra_dist_tbl(g_dist_count).account_set_flag := 'N';
5073 g_ra_dist_tbl(g_dist_count).acctd_amount := p_acctd_amount;
5074 g_ra_dist_tbl(g_dist_count).posting_control_id := -3;
5075 END IF;
5076
5077 END IF;
5078 IF PG_DEBUG in ('Y', 'C') THEN
5079 arp_util.debug('AR_Revenue_Adjustment_PVT.insert_distribution()-');
5080 END IF;
5081 EXCEPTION
5082 WHEN OTHERS THEN
5083 IF (SQLCODE = -20001)
5084 THEN
5085 IF PG_DEBUG in ('Y', 'C') THEN
5086 arp_util.debug
5087 ('20001 error at AR_Revenue_Adjustment_PVT.insert_distribution()');
5088 END IF;
5089 RAISE;
5090 ELSE
5091 IF PG_DEBUG in ('Y', 'C') THEN
5092 arp_util.debug('insert_distribution: ' || 'Unexpected error '||sqlerrm||
5093 ' at AR_Revenue_Adjustment_PVT.insert_distribution()+');
5094 END IF;
5095 RAISE;
5096 END IF;
5097 END insert_distribution;
5098
5099 /* 6615118 - insert model distributions if the srep is revenue
5100 and the transaction has rules */
5101
5102 PROCEDURE insert_sales_credit (p_customer_trx_id IN NUMBER,
5103 p_salesrep_id IN NUMBER,
5104 p_salesgroup_id IN NUMBER,
5105 p_cust_trx_line_id IN NUMBER,
5106 p_amount IN NUMBER,
5107 p_percent IN NUMBER,
5108 p_type IN VARCHAR2,
5109 p_sales_credit_id IN OUT NOCOPY NUMBER,
5110 p_adjustment_id IN NUMBER,
5111 p_gl_date IN DATE)
5112 IS
5113
5114 CURSOR get_salesrep_line_id IS
5115 SELECT ra_cust_trx_line_salesreps_s.NEXTVAL
5116 FROM dual;
5117
5118 CURSOR get_gldist_line_id IS
5119 SELECT ra_cust_trx_line_gl_dist_s.NEXTVAL
5120 FROM dual;
5121
5122 l_user_id NUMBER;
5123 l_account_class ra_cust_trx_line_gl_dist_all.account_class%TYPE;
5124 l_gl_dist_id ra_cust_trx_line_gl_dist_all.cust_trx_line_gl_dist_id%TYPE;
5125 l_ccid ra_cust_trx_line_gl_dist_all.code_combination_id%TYPE;
5126 l_concat_segments VARCHAR2(2000);
5127 l_fail_count NUMBER := 0;
5128
5129 invalid_ccid EXCEPTION;
5130
5131 BEGIN
5132 IF PG_DEBUG in ('Y', 'C') THEN
5133 arp_util.debug('AR_Revenue_Adjustment_PVT.insert_sales_credit()+');
5134 arp_util.debug(' p_customer_trx_id = ' || p_customer_trx_id);
5135 arp_util.debug(' p_cust_trx_line_id = ' || p_cust_trx_line_id);
5136 arp_util.debug(' p_salesrep_id = ' || p_salesrep_id);
5137 arp_util.debug(' p_salesgroup_id = ' || p_salesgroup_id);
5138 arp_util.debug(' p_type = ' || p_type);
5139 arp_util.debug(' p_amount = ' || p_amount);
5140 arp_util.debug(' p_percent = ' || p_percent);
5141 END IF;
5142 OPEN get_salesrep_line_id;
5143 FETCH get_salesrep_line_id INTO p_sales_credit_id;
5144 CLOSE get_salesrep_line_id;
5145
5146 FND_PROFILE.get('USER_ID',l_user_id);
5147 IF l_user_id IS NULL
5148 THEN
5149 l_user_id := 0;
5150 ELSE
5151 l_user_id := FND_GLOBAL.USER_ID;
5152 END IF;
5153
5154 INSERT INTO ra_cust_trx_line_salesreps
5155 (cust_trx_line_salesrep_id
5156 ,last_update_date
5157 ,last_updated_by
5158 ,creation_date
5159 ,created_by
5160 ,customer_trx_id
5161 ,salesrep_id
5162 ,revenue_salesgroup_id
5163 ,non_revenue_salesgroup_id
5164 ,customer_trx_line_id
5165 ,revenue_amount_split
5166 ,non_revenue_amount_split
5167 ,revenue_percent_split
5168 ,non_revenue_percent_split
5169 ,revenue_adjustment_id
5170 ,org_id)
5171 VALUES (p_sales_credit_id
5172 ,SYSDATE
5173 ,l_user_id
5174 ,SYSDATE
5175 ,l_user_id
5176 ,p_customer_trx_id
5177 ,p_salesrep_id
5178 ,DECODE(p_type,'R',p_salesgroup_id,NULL)
5179 ,DECODE(p_type,'N',p_salesgroup_id,NULL)
5180 ,p_cust_trx_line_id
5181 ,DECODE(p_type,'R',p_amount,NULL)
5182 ,DECODE(p_type,'N',p_amount,NULL)
5183 ,DECODE(p_type,'R',p_percent,NULL)
5187
5184 ,DECODE(p_type,'N',p_percent,NULL)
5185 ,p_adjustment_id
5186 ,arp_standard.sysparm.org_id);
5188 IF PG_DEBUG in ('Y', 'C') THEN
5189 arp_util.debug(' Inserted salescredit row w/ ID = ' ||
5190 p_sales_credit_id);
5191 END IF;
5192
5193 /* 6615118 - create model distribution if srep is revenue type
5194 and transaction has rules */
5195 IF p_type = 'R' AND
5196 AR_RAAPI_UTIL.g_invoicing_rule_id IS NOT NULL
5197 THEN
5198 /* fetch these values for autoaccounting */
5199
5200 IF NVL(g_line_id, -99) = p_cust_trx_line_id
5201 THEN
5202 /* already fetched these values, no nothing */
5203 IF PG_DEBUG in ('Y', 'C') THEN
5204 arp_util.debug(' cached values = ' || g_inventory_item_id ||
5205 ':' || g_memo_line_id || ':' || g_warehouse_id);
5206 END IF;
5207 ELSE
5208 /* fetch warehouse, etc for this line */
5209 SELECT inventory_item_id, memo_line_id, warehouse_id
5210 INTO g_inventory_item_id, g_memo_line_id, g_warehouse_id
5211 FROM RA_CUSTOMER_TRX_LINES
5212 WHERE customer_trx_line_id = p_cust_trx_line_id;
5213
5214 g_line_id := p_cust_trx_line_id;
5215
5216 IF PG_DEBUG in ('Y', 'C') THEN
5217 arp_util.debug(' retrieved values = ' || g_inventory_item_id ||
5218 ':' || g_memo_line_id || ':' || g_warehouse_id);
5219 END IF;
5220 END IF;
5221
5222 /* 6615118 - using a loop to run through this
5223 process twice.. once for REV and second for UNEARN */
5224 FOR i IN 1..2 LOOP
5225 IF i = 1
5226 THEN
5227 l_account_class := 'REV';
5228 ELSE
5229 l_account_class := 'UNEARN';
5230 END IF;
5231
5232 ARP_AUTO_ACCOUNTING.do_autoaccounting('G'
5233 ,l_account_class
5234 ,p_customer_trx_id
5235 ,p_cust_trx_line_id
5236 ,NULL
5237 ,NULL
5238 ,NULL
5239 ,NULL
5240 ,p_amount
5241 ,NULL
5242 ,NULL
5243 ,AR_RAAPI_UTIL.g_cust_trx_type_id
5244 ,p_salesrep_id
5245 ,g_inventory_item_id
5246 ,g_memo_line_id
5247 ,g_warehouse_id
5248 ,l_ccid
5249 ,l_concat_segments
5250 ,l_fail_count);
5251 IF l_ccid IS NULL
5252 THEN
5253 l_ccid := FND_FLEX_EXT.GET_CCID
5254 ('SQLGL'
5255 ,'GL#'
5256 ,arp_global.chart_of_accounts_id
5257 ,TO_CHAR(p_gl_date,'DD-MON-YYYY')
5258 ,l_concat_segments);
5259 END IF;
5260
5261 IF l_ccid = -1 OR
5262 l_ccid = 0 OR
5263 l_fail_count > 0
5264 THEN
5265 RAISE invalid_ccid;
5266 END IF;
5267
5268 OPEN get_gldist_line_id;
5269 FETCH get_gldist_line_id INTO l_gl_dist_id;
5270 CLOSE get_gldist_line_id;
5271
5272 INSERT INTO ra_cust_trx_line_gl_dist
5273 (
5274 customer_trx_line_id,
5275 customer_trx_id,
5276 code_combination_id,
5277 set_of_books_id,
5278 account_class,
5279 account_set_flag,
5280 percent,
5281 amount,
5282 acctd_amount,
5283 gl_date,
5284 cust_trx_line_salesrep_id,
5285 request_id,
5286 program_application_id,
5287 program_id,
5288 program_update_date,
5289 creation_date,
5290 created_by,
5291 last_update_date,
5292 last_updated_by,
5293 posting_control_id,
5294 original_gl_date,
5295 cust_trx_line_gl_dist_id,
5296 revenue_adjustment_id,
5297 user_generated_flag,
5298 org_id
5299 )
5300 VALUES
5301 ( p_cust_trx_line_id,
5302 p_customer_trx_id,
5303 l_ccid,
5304 arp_standard.sysparm.set_of_books_id,
5305 l_account_class,
5306 'Y',
5307 p_percent,
5308 NULL,
5309 NULL,
5310 NULL,
5311 p_sales_credit_id,
5312 arp_standard.profile.request_id,
5313 arp_standard.application_id,
5314 arp_standard.profile.program_id,
5315 sysdate,
5316 sysdate,
5317 l_user_id,
5318 sysdate,
5319 l_user_id,
5323 p_adjustment_id,
5320 -3,
5321 NULL,
5322 l_gl_dist_id,
5324 'Y',
5325 arp_standard.sysparm.org_id
5326 );
5327
5328 IF PG_DEBUG in ('Y', 'C') THEN
5329 arp_util.debug(' Inserted ' || l_account_class ||
5330 ' model dist with gl_dist_id = ' || l_gl_dist_id ||
5331 ' ccid = ' || l_ccid);
5332 END IF;
5333 END LOOP;
5334 END IF;
5335
5336 IF PG_DEBUG in ('Y', 'C') THEN
5337 arp_util.debug('AR_Revenue_Adjustment_PVT.insert_sales_credit()-');
5338 END IF;
5339
5340 EXCEPTION
5341 WHEN invalid_ccid THEN
5342 FND_MSG_PUB.Add;
5343 FND_MESSAGE.SET_NAME(application => 'AR',
5344 name => 'AR_RA_INVALID_CODE_COMB');
5345 FND_MESSAGE.SET_TOKEN('CODE_COMBINATION',l_concat_segments);
5346 FND_MSG_PUB.Add;
5347 RAISE FND_API.G_EXC_ERROR;
5348 WHEN OTHERS THEN
5349 IF (SQLCODE = -20001)
5350 THEN
5351 IF PG_DEBUG in ('Y', 'C') THEN
5352 arp_util.debug
5353 ('20001 error at AR_Revenue_Adjustment_PVT.insert_sales_credit()');
5354 END IF;
5355 RAISE FND_API.G_EXC_ERROR;
5356 ELSE
5357 IF PG_DEBUG in ('Y', 'C') THEN
5358 arp_util.debug('insert_sales_credit: ' || 'Unexpected error '||sqlerrm||
5359 ' at AR_Revenue_Adjustment_PVT.insert_sales_credit()+');
5360 END IF;
5361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5362 END IF;
5363
5364 END insert_sales_credit;
5365
5366 FUNCTION category_set_id
5367 RETURN VARCHAR2 IS
5368 BEGIN
5369 RETURN AR_RAAPI_UTIL.g_category_set_id;
5370 END category_set_id;
5371
5372 FUNCTION inv_org_id
5373 RETURN VARCHAR2 IS
5374 BEGIN
5375 RETURN AR_RAAPI_UTIL.g_inv_org_id;
5376 END inv_org_id;
5377
5378 -----------------------------------------------------------------------
5379 -- API name : Record_Acceptance
5380 -- Type : Private
5381 -- Function : Calls collectibility engine to determine if revenue
5382 -- is to be scheduled/unscheduled for each line.
5383 -- Pre-reqs :
5384 -- Parameters :
5385 -- IN :
5386 -- OUT NOCOPY :
5387 --
5388 --
5389 -- Notes :
5390 PROCEDURE Record_Acceptance
5391 (p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
5392 p_category_id IN mtl_categories.category_id%TYPE,
5393 p_inventory_item_id IN mtl_system_items.inventory_item_id%TYPE,
5394 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE,
5395 p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%TYPE,
5396 p_comments IN ar_revenue_adjustments.comments%TYPE,
5397 p_ram_desc_flexfield IN ar_revenue_management_pvt.desc_flexfield,
5398 x_scenario OUT NOCOPY NUMBER,
5399 x_first_rev_adj_id OUT NOCOPY ar_revenue_adjustments.revenue_adjustment_id%TYPE,
5400 x_last_rev_adj_id OUT NOCOPY ar_revenue_adjustments.revenue_adjustment_id%TYPE,
5401 x_return_status OUT NOCOPY VARCHAR2,
5402 x_msg_count OUT NOCOPY NUMBER,
5403 x_msg_data OUT NOCOPY VARCHAR2)
5404 IS
5405 l_scenario NUMBER;
5406 l_first_adj_num NUMBER;
5407 l_last_adj_num NUMBER;
5408 l_real_last_adj_num NUMBER;
5409 l_not_recognized_flag VARCHAR2(1) := 'N';
5410 l_partially_recognized_flag VARCHAR2(1) := 'N';
5414 SELECT l.customer_trx_line_id
5411 l_fully_recognized_flag VARCHAR2(1) := 'N';
5412
5413 CURSOR c_line IS
5415 FROM mtl_item_categories mic
5416 ,ra_rules r
5417 ,ra_customer_trx_lines l
5418 WHERE l.customer_trx_id = p_customer_trx_id
5419 AND l.accounting_rule_id = r.rule_id(+)
5420 AND NVL(l.inventory_item_id,0) =
5421 NVL(p_inventory_item_id,NVL(l.inventory_item_id,0))
5422 AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
5423 AND l.inventory_item_id = mic.inventory_item_id(+)
5424 AND NVL(p_category_id,0) =
5425 DECODE(p_category_id,NULL,0,mic.category_id)
5426 AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
5427 AND l.line_type = 'LINE'
5428 AND NVL(r.deferred_revenue_flag,'N') <> 'Y';
5429
5430 BEGIN
5431 IF PG_DEBUG in ('Y', 'C') THEN
5432 arp_util.debug('AR_Revenue_Adjustment_PVT.record_acceptance()+');
5433 END IF;
5434 AR_RAAPI_UTIL.Constant_System_Values;
5435 AR_RAAPI_UTIL.Initialize_Globals;
5436 x_first_rev_adj_id := NULL;
5437 x_last_rev_adj_id := NULL;
5438 l_first_adj_num := NULL;
5439 l_last_adj_num := NULL;
5440 l_real_last_adj_num := NULL;
5441 IF (p_category_id IS NOT NULL or p_inventory_item_id IS NOT NULL)
5442 THEN
5443 FOR c1 IN c_line LOOP
5444 ar_revenue_management_pvt.revenue_synchronizer(
5445 p_mode => ar_revenue_management_pvt.c_acceptance_obtained_mode,
5446 p_customer_trx_id => p_customer_trx_id,
5447 p_customer_trx_line_id => c1.customer_trx_line_id,
5448 p_gl_date => p_gl_date,
5449 p_comments => p_comments,
5450 p_ram_desc_flexfield => p_ram_desc_flexfield,
5451 x_scenario => l_scenario,
5452 x_first_adjustment_number => l_first_adj_num,
5453 x_last_adjustment_number => l_last_adj_num,
5454 x_return_status => x_return_status,
5455 x_msg_count => x_msg_count,
5456 x_msg_data => x_msg_data);
5457 IF l_scenario = ar_revenue_management_pvt.c_not_recognized
5458 THEN
5459 l_not_recognized_flag := 'Y';
5460 ELSIF l_scenario = ar_revenue_management_pvt.c_partially_recognized
5461 THEN
5465 l_fully_recognized_flag := 'Y';
5462 l_partially_recognized_flag := 'Y';
5463 ELSIF l_scenario = ar_revenue_management_pvt.c_fully_recognized
5464 THEN
5466 END IF;
5467 IF (x_first_rev_adj_id IS NULL AND l_first_adj_num IS NOT NULL)
5468 THEN
5469 select revenue_adjustment_id into x_first_rev_adj_id
5470 FROM ar_revenue_adjustments
5471 WHERE revenue_adjustment_number = l_first_adj_num;
5472 END IF;
5473 IF l_last_adj_num IS NOT NULL
5474 THEN
5475 l_real_last_adj_num := l_last_adj_num;
5476 END IF;
5477 END LOOP;
5478 IF (l_not_recognized_flag = 'Y' AND
5479 l_partially_recognized_flag = 'N' AND
5480 l_fully_recognized_flag = 'N')
5481 THEN
5482 x_scenario := 0;
5483 ELSIF (l_not_recognized_flag = 'N' AND
5484 l_partially_recognized_flag = 'N' AND
5485 l_fully_recognized_flag = 'Y')
5486 THEN
5487 x_scenario := 2;
5488 ELSE
5489 x_scenario := 1;
5490 END IF;
5491 IF l_real_last_adj_num IS NOT NULL
5492 THEN
5493 select revenue_adjustment_id into x_last_rev_adj_id
5494 FROM ar_revenue_adjustments
5495 WHERE revenue_adjustment_number = l_real_last_adj_num;
5496 ELSE
5497 x_last_rev_adj_id := x_first_rev_adj_id;
5498 END IF;
5499 ELSE
5500 ar_revenue_management_pvt.revenue_synchronizer(
5501 p_mode => ar_revenue_management_pvt.c_acceptance_obtained_mode,
5502 p_customer_trx_id => p_customer_trx_id,
5503 p_customer_trx_line_id => p_customer_trx_line_id,
5504 p_gl_date => p_gl_date,
5505 p_comments => p_comments,
5506 p_ram_desc_flexfield => p_ram_desc_flexfield,
5507 x_scenario => l_scenario,
5508 x_first_adjustment_number => l_first_adj_num,
5509 x_last_adjustment_number => l_last_adj_num,
5510 x_return_status => x_return_status,
5511 x_msg_count => x_msg_count,
5512 x_msg_data => x_msg_data);
5513
5514 IF l_scenario = ar_revenue_management_pvt.c_not_recognized
5515 THEN
5516 x_scenario := 0;
5517 ELSIF l_scenario = ar_revenue_management_pvt.c_partially_recognized
5518 THEN
5519 x_scenario := 1;
5520 ELSIF l_scenario = ar_revenue_management_pvt.c_fully_recognized
5521 THEN
5522 x_scenario := 2;
5523 END IF;
5524
5525 IF l_first_adj_num IS NOT NULL
5526 THEN
5527 select revenue_adjustment_id into x_first_rev_adj_id
5528 FROM ar_revenue_adjustments
5529 WHERE revenue_adjustment_number = l_first_adj_num;
5530 END IF;
5531 END IF;
5532 IF l_last_adj_num IS NOT NULL
5533 THEN
5534 select revenue_adjustment_id into x_last_rev_adj_id
5535 FROM ar_revenue_adjustments
5536 WHERE revenue_adjustment_number = l_last_adj_num;
5537 END IF;
5538 IF PG_DEBUG in ('Y', 'C') THEN
5539 arp_util.debug('Record_Acceptance: ' || 'l_first_adj_num = '||l_first_adj_num);
5540 arp_util.debug('Record_Acceptance: ' || 'l_last_adj_num = '||l_last_adj_num);
5541 arp_util.debug('Record_Acceptance: ' || 'x_first_rev_adj_id = '||x_first_rev_adj_id);
5542 arp_util.debug('Record_Acceptance: ' || 'x_last_rev_adj_id = '||x_last_rev_adj_id);
5543 arp_util.debug('AR_Revenue_Adjustment_PVT.record_acceptance()-');
5544 END IF;
5545 EXCEPTION
5546 WHEN OTHERS THEN
5547 IF (SQLCODE = -20001)
5548 THEN
5549 IF PG_DEBUG in ('Y', 'C') THEN
5550 arp_util.debug
5551 ('20001 error at AR_Revenue_Adjustment_PVT.record_acceptance()');
5552 END IF;
5553 RAISE FND_API.G_EXC_ERROR;
5554 ELSE
5555 IF PG_DEBUG in ('Y', 'C') THEN
5556 arp_util.debug('Record_Acceptance: ' || 'Unexpected error '||sqlerrm||
5557 ' at AR_Revenue_Adjustment_PVT.record_acceptance()+');
5558 END IF;
5559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5560 END IF;
5561 END Record_Acceptance;
5562
5563 /* Initialization section */
5564 BEGIN
5565 /* Bug 2650708: check if revenue management is installed */
5566 IF ar_revenue_management_pvt.revenue_management_enabled
5567 THEN
5568 g_rev_mgt_installed := 'Y';
5569 ELSE
5570 g_rev_mgt_installed := 'N';
5571 END IF;
5572
5573 END AR_Revenue_Adjustment_PVT;