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