1 PACKAGE BODY AR_RAAPI_UTIL AS
2 /*$Header: ARXRAAUB.pls 120.28.12010000.8 2008/10/30 10:30:21 nproddut ship $*/
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 /* 5011151 - global for use_inv_acctg */
6 g_use_inv_acctg VARCHAR2(1);
7
8 FUNCTION use_inv_acctg
9 RETURN VARCHAR2
10 IS
11 BEGIN
12 IF g_use_inv_acctg IS NULL
13 THEN
14 fnd_profile.get( 'AR_USE_INV_ACCT_FOR_CM_FLAG',
15 g_use_inv_acctg);
16 IF g_use_inv_acctg IS NULL
17 THEN
18 g_use_inv_acctg := 'N';
19 END IF;
20 END IF;
21 RETURN g_use_inv_acctg;
22 END use_inv_acctg;
23
24 PROCEDURE Constant_System_Values IS
25
26 l_segment_num NUMBER;
27 l_enabled_flag VARCHAR2(1);
28
29 /* Bug 4675438 - removed all ar_system_parameter related fetches */
30 CURSOR c_ar_app_id IS
31 SELECT application_id
32 FROM fnd_application
33 WHERE application_short_name = 'AR';
34
35 CURSOR c_get_category_set IS
36 SELECT dcs.category_set_id,
37 cs.structure_id
38 FROM mtl_default_category_sets dcs,
39 mtl_category_sets cs,
40 mfg_lookups ml
41 WHERE ml.lookup_type = 'MTL_FUNCTIONAL_AREAS'
42 AND ml.lookup_code = dcs.functional_area_id
43 AND dcs.category_set_id = cs.category_set_id
44 AND ml.lookup_code = '1';
45 -- bug2117242 "meaning" is translatable column
46 -- AND ml.meaning = 'Inventory';
47
48 BEGIN
49 arp_util.debug('AR_RAAPI_UTIL.constant_system_values()+');
50
51 OPEN c_ar_app_id;
52 FETCH c_ar_app_id INTO g_ar_app_id;
53 CLOSE c_ar_app_id;
54
55 /* 5126974 - this was raising an error if MOAC not init'd
56 so I moved it to inv_org_id function where it initializes
57 on the first call
58 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
59 */
60
61 OPEN c_get_category_set;
62 FETCH c_get_category_set INTO g_category_set_id, g_category_structure_id;
63 CLOSE c_get_category_set;
64
65 g_un_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning
66 (p_lookup_type => 'REV_ADJ_TYPE'
67 ,p_lookup_code => 'UN');
68 g_ea_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning
69 (p_lookup_type => 'REV_ADJ_TYPE'
70 ,p_lookup_code => 'EA');
71 g_sa_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning
72 (p_lookup_type => 'REV_ADJ_TYPE'
73 ,p_lookup_code => 'SA');
74 g_nr_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning
75 (p_lookup_type => 'REV_ADJ_TYPE'
76 ,p_lookup_code => 'NR');
77 g_ll_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning
78 (p_lookup_type => 'REV_ADJ_TYPE'
79 ,p_lookup_code => 'LL');
80
81 g_system_cache_flag := 'Y';
82
83 EXCEPTION
84 WHEN OTHERS THEN
85 arp_util.debug('Unexpected error '||sqlerrm||
86 ' at AR_RAAPI_UTIL.constant_system_values()+');
87 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
88
89 END Constant_System_Values;
90
91 PROCEDURE Initialize_Globals
92 IS
93 BEGIN
94 g_customer_trx_id := NULL;
95 g_last_customer_trx_id := NULL;
96 g_cust_trx_type_id := NULL;
97 g_trx_date := NULL;
98 g_invoicing_rule_id := NULL;
99 g_trx_currency := NULL;
100 g_trx_curr_format := NULL;
101 g_exchange_rate := NULL;
102 g_trx_precision := NULL;
103 g_from_salesrep_id := NULL;
104 g_to_salesrep_id := NULL;
105 /* BEGIN bug 3067675 */
106 g_from_salesgroup_id := NULL;
107 g_to_salesgroup_id := NULL;
108 /* END bug 3067675 */
109 g_from_category_id := NULL;
110 g_to_category_id := NULL;
111 g_from_inventory_item_id := NULL;
112 g_to_inventory_item_id := NULL;
113 g_from_cust_trx_line_id := NULL;
114 g_to_cust_trx_line_id := NULL;
115 g_gl_date := NULL;
116
117 /* Bug 3022420 - initialize arp_global and arp_standard globals to ensure
118 the correct set of books is accessed */
119 arp_global.init_global;
120 /* Bug 5547989 - Pass org id as a parameter to arp_standard.init_standard to set the correct org id */
121 arp_standard.init_standard(arp_global.sysparam.org_id);
122 /* Change for Bug 5547989 ends */
123
124 END Initialize_Globals;
125
126
127 PROCEDURE Constant_Trx_Values
128 (p_customer_trx_id IN NUMBER)
129 IS
130
131 CURSOR c_trx IS
132 SELECT t.cust_trx_type_id
133 ,t.invoice_currency_code
134 ,t.exchange_rate
135 ,NVL(c.precision,0) -- Bug 3480443
136 ,t.trx_date
137 ,t.invoicing_rule_id
138 FROM ra_customer_trx t
139 ,fnd_currencies c
140 WHERE t.invoice_currency_code = c.currency_code
141 AND t.customer_trx_id = p_customer_trx_id;
142
143 BEGIN
144 IF PG_DEBUG in ('Y', 'C') THEN
145 arp_util.debug('AR_RAAPI_UTIL.Constant_Trx_Values()+');
146 END IF;
147
148 OPEN c_trx;
149 FETCH c_trx INTO g_cust_trx_type_id
150 ,g_trx_currency
151 ,g_exchange_rate
152 ,g_trx_precision
153 ,g_trx_date
154 ,g_invoicing_rule_id;
155 CLOSE c_trx;
156
157 g_trx_curr_format := fnd_currency.get_format_mask
158 (currency_code => g_trx_currency, field_length => 18);
159 g_trx_curr_format := REPLACE(g_trx_curr_format,'FM');
160 g_trx_curr_format := REPLACE(g_trx_curr_format,'PR');
161
162 g_last_customer_trx_id := p_customer_trx_id;
163
164 EXCEPTION
165
166 WHEN OTHERS then
167 IF PG_DEBUG in ('Y', 'C') THEN
168 arp_util.debug('Constant_Trx_Values: ' || 'Unexpected error '||sqlerrm||
169 ' at AR_RAAPI_UTIL.constant_system_values()+');
170 END IF;
171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
172
173 END Constant_Trx_Values;
174
175 PROCEDURE Validate_Parameters
176 (p_init_msg_list IN VARCHAR2
177 ,p_rev_adj_rec IN OUT NOCOPY AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type
178 ,p_validation_level IN NUMBER
179 ,x_return_status IN OUT NOCOPY VARCHAR2
180 ,x_msg_count OUT NOCOPY NUMBER
181 ,x_msg_data OUT NOCOPY VARCHAR2)
182 IS
183 l_gl_date_valid DATE; -- Bug 2146970
184 BEGIN
185 IF PG_DEBUG in ('Y', 'C') THEN
186 arp_util.debug('AR_RAAPI_UTIL.Validate_Parameters()+');
187 END IF;
188 -- Initialize message list if p_init_msg_list is set to TRUE.
189 IF FND_API.to_Boolean( p_init_msg_list )
190 THEN
191 FND_MSG_PUB.initialize;
192 END IF;
193 x_return_status := FND_API.G_RET_STS_SUCCESS;
194 Validate_Transaction (p_init_msg_list => FND_API.G_FALSE
195 ,p_rev_adj_rec => p_rev_adj_rec
196 ,p_validation_level => p_validation_level
197 ,x_return_status => x_return_status
198 ,x_msg_count => x_msg_count
199 ,x_msg_data => x_msg_data);
200 IF x_return_status = FND_API.G_RET_STS_SUCCESS
201 THEN
202 IF NVL(AR_RAAPI_UTIL.g_last_customer_trx_id,
203 AR_RAAPI_UTIL.g_customer_trx_id - 1) <> AR_RAAPI_UTIL.g_customer_trx_id
204 THEN
205 Constant_Trx_Values(AR_RAAPI_UTIL.g_customer_trx_id);
206 END IF;
207 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL
208 THEN
209 Validate_Salesreps (p_init_msg_list => FND_API.G_FALSE
210 ,p_rev_adj_rec => p_rev_adj_rec
211 ,x_return_status => x_return_status
212 ,x_msg_count => x_msg_count
213 ,x_msg_data => x_msg_data);
214 Validate_Category (p_init_msg_list => FND_API.G_FALSE
215 ,p_rev_adj_rec => p_rev_adj_rec
216 ,x_return_status => x_return_status
217 ,x_msg_count => x_msg_count
218 ,x_msg_data => x_msg_data);
219 Validate_Item (p_init_msg_list => FND_API.G_FALSE
220 ,p_rev_adj_rec => p_rev_adj_rec
221 ,x_return_status => x_return_status
222 ,x_msg_count => x_msg_count
223 ,x_msg_data => x_msg_data);
224 Validate_Line (p_init_msg_list => FND_API.G_FALSE
225 ,p_rev_adj_rec => p_rev_adj_rec
226 ,x_return_status => x_return_status
227 ,x_msg_count => x_msg_count
228 ,x_msg_data => x_msg_data);
229
230 /* Bug 2146970 - replaced call to procedure with function call */
231
232 /* Bug # 2804660- validate_gl_date should only be called here if
233 no gl date is provided, so that a gl date is
234 defaulted. */
235
236 IF (p_rev_adj_rec.gl_date IS NULL) THEN
237 l_gl_date_valid := validate_gl_date(
238 p_gl_date => p_rev_adj_rec.gl_date);
239 p_rev_adj_rec.gl_date := l_gl_date_valid;
240 END IF;
241
242 Validate_Other (p_init_msg_list => FND_API.G_FALSE
243 ,p_rev_adj_rec => p_rev_adj_rec
244 ,x_return_status => x_return_status
245 ,x_msg_count => x_msg_count
246 ,x_msg_data => x_msg_data);
247 ELSE
248 g_from_salesrep_id := p_rev_adj_rec.from_salesrep_id;
249 g_to_salesrep_id := p_rev_adj_rec.to_salesrep_id;
250 /* BEGIN bug 3067675 */
251 g_from_salesgroup_id := p_rev_adj_rec.from_salesgroup_id;
252 g_to_salesgroup_id := p_rev_adj_rec.to_salesgroup_id;
253 /* END bug 3067675 */
254 g_from_category_id := p_rev_adj_rec.from_category_id;
255 g_to_category_id := p_rev_adj_rec.to_category_id;
256 g_from_inventory_item_id := p_rev_adj_rec.from_inventory_item_id;
257 g_to_inventory_item_id := p_rev_adj_rec.to_inventory_item_id;
258 g_from_cust_trx_line_id := p_rev_adj_rec.from_cust_trx_line_id;
259 g_to_cust_trx_line_id := p_rev_adj_rec.to_cust_trx_line_id;
260 g_gl_date := p_rev_adj_rec.gl_date;
261 END IF;
262 ELSE
263 RAISE FND_API.G_EXC_ERROR;
264
265 END IF;
266 EXCEPTION
267 WHEN OTHERS THEN
268 IF PG_DEBUG in ('Y', 'C') THEN
269 arp_util.debug('Unexpected error '||sqlerrm||
270 ' at AR_RAAPI_UTIL.Validate_Parameters()+');
271 END IF;
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273
274 END Validate_Parameters;
275
276 PROCEDURE Validate_Transaction
277 (p_init_msg_list IN VARCHAR2
278 ,p_rev_adj_rec IN AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type
279 ,p_validation_level IN NUMBER
280 ,x_return_status IN OUT NOCOPY VARCHAR2
281 ,x_msg_count OUT NOCOPY NUMBER
282 ,x_msg_data OUT NOCOPY VARCHAR2)
283 IS
284 l_customer_trx_id NUMBER;
285 l_trx_type ra_cust_trx_types.type%TYPE;
286 l_invoice_total NUMBER;
287 l_cm_total NUMBER;
288 l_inv_and_cm_total NUMBER;
289 l_prev_trx_id NUMBER;
290
291 CURSOR c_trx_num IS
292 SELECT t.customer_trx_id
293 FROM ra_customer_trx t
294 ,ra_batch_sources bs
295 WHERE t.batch_source_id = bs.batch_source_id
296 AND t.trx_number = p_rev_adj_rec.trx_number
297 AND bs.name = NVL(p_rev_adj_rec.batch_source_name,bs.name)
298 AND NVL(t.invoicing_rule_id,0) <> -3
299 AND NOT EXISTS (SELECT 'X'
300 FROM ra_customer_trx_lines l
301 WHERE l.customer_trx_id = t.customer_trx_id
302 AND l.line_type = 'LINE'
303 AND autorule_complete_flag IS NOT NULL);
304
305 CURSOR c_trx_id IS
306 SELECT t.customer_trx_id
307 FROM ra_customer_trx t
308 WHERE t.customer_trx_id = p_rev_adj_rec.customer_trx_id
309 AND NVL(t.invoicing_rule_id,0) <> -3
310 AND NOT EXISTS (SELECT 'X'
311 FROM ra_customer_trx_lines l
312 WHERE l.customer_trx_id = t.customer_trx_id
313 AND l.line_type = 'LINE'
314 AND autorule_complete_flag IS NOT NULL);
315
316 CURSOR c_trx_type IS
317 SELECT tt.type,
318 t.previous_customer_trx_id
319 FROM ra_cust_trx_types tt,
320 ra_customer_trx t
321 WHERE tt.cust_trx_type_id = t.cust_trx_type_id
322 AND t.customer_trx_id = g_customer_trx_id;
323
324 CURSOR c_invoice_total IS
325 SELECT SUM(l.extended_amount)
326 FROM ra_customer_trx_lines l
327 WHERE l.customer_trx_id = g_customer_trx_id
328 AND l.line_type = 'LINE';
329
330 CURSOR c_cm_total IS
331 SELECT sum(l.extended_amount)
332 FROM ra_customer_trx_lines l,
333 ra_cust_trx_types tt,
334 ra_customer_trx cm
335 WHERE l.customer_trx_id = cm.customer_trx_id
336 AND cm.cust_trx_type_id = tt.cust_trx_type_id
337 AND l.line_type = 'LINE'
338 AND tt.type = 'CM'
339 AND cm.previous_customer_trx_id = g_customer_trx_id;
340
341 BEGIN
342 IF PG_DEBUG in ('Y', 'C') THEN
343 arp_util.debug('AR_RAAPI_UTIL.Validate_Transaction()+');
344 END IF;
345 -- Initialize message list if p_init_msg_list is set to TRUE.
346 IF FND_API.to_Boolean( p_init_msg_list )
347 THEN
348 FND_MSG_PUB.initialize;
349 END IF;
350 x_return_status := FND_API.G_RET_STS_SUCCESS;
351 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL
352 THEN
353 --
354 -- Verify the transaction ID
355 --
356 IF p_rev_adj_rec.customer_trx_id IS NULL
357 THEN
358 IF p_rev_adj_rec.trx_number IS NOT NULL
359 THEN
360 OPEN c_trx_num;
361 FETCH c_trx_num INTO g_customer_trx_id;
362 IF c_trx_num%NOTFOUND
366 FND_MESSAGE.set_token('TRX_NUMBER',p_rev_adj_rec.trx_number);
363 THEN
364 FND_MESSAGE.set_name (application => 'AR',
365 name => 'AR_RA_TRX_NOTFOUND');
367 FND_MSG_PUB.Add;
368 x_return_status := FND_API.G_RET_STS_ERROR ;
369 ELSE
370 FETCH c_trx_num INTO l_customer_trx_id;
371 IF c_trx_num%FOUND
372 THEN
373 g_customer_trx_id := NULL;
374 FND_MESSAGE.set_name (application => 'AR',
375 name => 'AR_RA_TRX_TOO_MANY_ROWS');
376 FND_MESSAGE.set_token('TRX_NUMBER',p_rev_adj_rec.trx_number);
377 FND_MSG_PUB.Add;
378 x_return_status := FND_API.G_RET_STS_ERROR ;
379 END IF;
380 END IF;
381 CLOSE c_trx_num;
382 ELSE
383 FND_MESSAGE.set_name (application => 'AR',
384 name => 'AR_RA_NO_TRX_NUMBER');
385 FND_MSG_PUB.Add;
386 x_return_status := FND_API.G_RET_STS_ERROR ;
387 END IF;
388 ELSE
389 OPEN c_trx_id;
390 FETCH c_trx_id INTO g_customer_trx_id;
391 IF c_trx_id%NOTFOUND
392 THEN
393 FND_MESSAGE.set_name (application => 'AR',
394 name => 'AR_TAPI_TRANS_NOT_EXIST');
395 FND_MESSAGE.set_token('CUSTOMER_TRX_ID',p_rev_adj_rec.customer_trx_id);
396 FND_MSG_PUB.Add;
397 x_return_status := FND_API.G_RET_STS_ERROR ;
398 END IF;
399 CLOSE c_trx_id;
400 END IF;
401 ELSE
402 g_customer_trx_id := p_rev_adj_rec.customer_trx_id;
403 END IF;
404 OPEN c_trx_type;
405 FETCH c_trx_type INTO l_trx_type, l_prev_trx_id;
406 CLOSE c_trx_type;
407 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL AND
408 g_customer_trx_id IS NOT NULL)
409 THEN
410 IF l_trx_type = 'CB'
411 THEN
412 FND_MESSAGE.set_name('AR','AR_RA_CB_DISALLOWED');
413 FND_MSG_PUB.Add;
414 x_return_status := FND_API.G_RET_STS_ERROR ;
415 END IF;
416 -- Bug # 4096889
417 -- ORASHID
418 -- IF l_trx_type = 'DM'
419 -- THEN
420 -- FND_MESSAGE.set_name('AR','AR_RA_DM_DISALLOWED');
421 -- FND_MSG_PUB.Add;
422 -- x_return_status := FND_API.G_RET_STS_ERROR ;
423 -- END IF;
424 IF l_trx_type = 'BR'
425 THEN
426 FND_MESSAGE.set_name('AR','AR_RA_BR_DISALLOWED');
427 FND_MSG_PUB.Add;
428 x_return_status := FND_API.G_RET_STS_ERROR ;
429 END IF;
430 -- Bug # 4096889
431 -- ORASHID
432 -- IF l_trx_type = 'DEP'
433 -- THEN
434 -- FND_MESSAGE.set_name('AR','AR_RA_DEP_DISALLOWED');
435 -- FND_MSG_PUB.Add;
436 -- x_return_status := FND_API.G_RET_STS_ERROR ;
437 -- END IF;
438 IF l_trx_type = 'GUAR'
439 THEN
440 FND_MESSAGE.set_name('AR','AR_RA_GUAR_DISALLOWED');
441 FND_MSG_PUB.Add;
442 x_return_status := FND_API.G_RET_STS_ERROR ;
443 END IF;
444 /* 5011151 - Only allow revenue adjustments on
445 credit memos if they are on-account or use_inv_acct=N */
446 IF l_trx_type = 'CM' AND
447 l_prev_trx_id IS NOT NULL
448 THEN
449 /* Check invoice accounting profile and
450 raise error if it is Y */
451 IF use_inv_acctg = 'Y'
452 THEN
453 /* raise error */
454 FND_MESSAGE.set_name('AR','AR_RA_CM_DISALLOWED');
455 FND_MSG_PUB.Add;
456 x_return_status := FND_API.G_RET_STS_ERROR;
457 END IF;
458 END IF;
459 END IF;
460 -- Bug # 4096889
461 -- ORASHID
462 IF l_trx_type IN ('INV', 'DEP', 'DM')
463 THEN
464 OPEN c_invoice_total;
465 FETCH c_invoice_total INTO l_invoice_total;
466 CLOSE c_invoice_total;
467 OPEN c_cm_total;
468 FETCH c_cm_total INTO l_cm_total;
469 CLOSE c_cm_total;
470
471 l_inv_and_cm_total := l_invoice_total + l_cm_total;
472 IF l_invoice_total <> l_inv_and_cm_total
473 THEN
474 IF l_inv_and_cm_total = 0
475 THEN
476 /* 5011151 - Remove this error, we now handle the
477 credit amounts inside the adj code so there is no
478 reason to overtly prevent adjustments */
479 --
480 -- Fully credit memo'd so raise an error
481 --
482 FND_MESSAGE.set_name ('AR','AR_RA_FULL_CREDIT');
483 FND_MSG_PUB.Add;
484 ELSE
485 --
486 -- Partially credit memo'd so raise a warning only
487 --
488 FND_MESSAGE.set_name ('AR','AR_RA_PARTIAL_CREDIT');
489 FND_MSG_PUB.Add;
490 END IF;
491 END IF;
492 END IF;
493 FND_MSG_PUB.Count_And_Get
494 (p_encoded => FND_API.G_FALSE,
495 p_count => x_msg_count,
496 p_data => x_msg_data);
497 EXCEPTION
501 ' at AR_RAAPI_UTIL.Validate_Transaction()+');
498 WHEN OTHERS THEN
499 IF PG_DEBUG in ('Y', 'C') THEN
500 arp_util.debug('Validate_Transaction: ' || 'Unexpected error '||sqlerrm||
502 END IF;
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504 END Validate_Transaction;
505
506
507 PROCEDURE Validate_Salesreps
508 (p_init_msg_list IN VARCHAR2
509 ,p_rev_adj_rec IN AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type
510 ,x_return_status IN OUT NOCOPY VARCHAR2
511 ,x_msg_count OUT NOCOPY NUMBER
512 ,x_msg_data OUT NOCOPY VARCHAR2)
513 IS
514 l_sales_credit_total NUMBER;
515 --begin anuj
516 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
517 l_org_id NUMBER;
518 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
519 --end anuj
520
521 /* BEGIN bug 3067675 */
522 l_rep_group_changed BOOLEAN := FALSE;
523 l_group_start_date DATE;
524 l_group_end_date DATE;
525 /* END bug 3067675 */
526
527 CURSOR c_salesrep_num (p_salesrep_number VARCHAR2) IS
528 SELECT salesrep_id
529 FROM ra_salesreps
530 WHERE salesrep_number = p_salesrep_number
531 AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE)
532 AND NVL(end_date_active,SYSDATE)
533 AND g_trx_date BETWEEN NVL(start_date_active,g_trx_date)
534 AND NVL(end_date_active,g_trx_date) ;
535
536 CURSOR c_salesrep_id (p_salesrep_id NUMBER) IS
537 SELECT salesrep_id
538 FROM ra_salesreps
539 WHERE salesrep_id = p_salesrep_id
540 AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE)
541 AND NVL(end_date_active,SYSDATE)
542 AND g_trx_date BETWEEN NVL(start_date_active,g_trx_date)
543 AND NVL(end_date_active,g_trx_date) ;
544
545 /* BEGIN bug 3067675 */
546 CURSOR c_salesgroup_id (p_salesgroup_id NUMBER) IS
547 SELECT grp.group_id group_id
548 FROM jtf_rs_group_members mem, jtf_rs_groups_b grp,
549 jtf_rs_salesreps srp, jtf_rs_group_usages usg,
550 jtf_rs_role_relations rrl
551 WHERE srp.resource_id = mem.resource_id
552 AND mem.group_id = grp.group_id
553 AND mem.group_id = usg.group_id
554 AND usg.usage = 'SALES'
555 AND mem.delete_flag = 'N'
556 AND mem.group_member_id = rrl.role_resource_id
557 AND rrl.role_resource_type = 'RS_GROUP_MEMBER'
558 AND rrl.delete_flag = 'N'
559 AND nvl(rrl.end_date_active, to_date('01/01/4713','MM/DD/RRRR')) >= l_group_start_date
560 AND rrl.start_date_active <= l_group_end_date
561 AND srp.salesrep_id = g_to_salesrep_id
562 AND nvl(srp.org_id, -99) = nvl(arp_standard.sysparm.org_id, -99)
563 AND l_group_end_date BETWEEN grp.start_date_active AND nvl(grp.end_date_active, to_date('01/01/4713','MM/DD/RRRR'))
564 AND grp.group_id = p_salesgroup_id
565 UNION ALL
566 SELECT group_id
567 FROM jtf_rs_groups_b
568 WHERE group_id = -1
569 AND group_id = p_salesgroup_id;
570 /* END bug 3067675 */
571
572 CURSOR c_check_sales_credits IS
573 SELECT DECODE(p_rev_adj_rec.sales_credit_type,'N',
574 SUM(non_revenue_percent_split), SUM(revenue_percent_split))
575 --begin anuj
576 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
577 ,org_id
578 /* Multi-Org Access Control Changes for SSA;end;anukumar;11/01/2002*/
579 --end anuj
580 FROM ra_cust_trx_line_salesreps
581 WHERE customer_trx_id = g_customer_trx_id
582 AND customer_trx_line_id IS NOT NULL
583 /* BEGIN bug 3067675 */
584 --AND salesrep_id = g_from_salesrep_id
585 --GROUP BY salesrep_id;
586 AND salesrep_id = NVL(g_from_salesrep_id, salesrep_id)
587 AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(non_revenue_salesgroup_id, -9999), NVL(revenue_salesgroup_id, -9999)) =
588 NVL(g_from_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(non_revenue_salesgroup_id, -9999), NVL(revenue_salesgroup_id, -9999)))
589 GROUP BY salesrep_id,
590 DECODE(p_rev_adj_rec.sales_credit_type,'N', non_revenue_salesgroup_id, revenue_salesgroup_id),
591 org_id;
592 /* END bug 3067675 */
593
594 CURSOR c_line_num (p_line_number NUMBER) IS
595 SELECT customer_trx_line_id
596 FROM ra_customer_trx_lines
597 WHERE line_number = p_line_number
598 AND customer_trx_id = g_customer_trx_id
599 AND line_type = 'LINE';
600
601 CURSOR c_line_id (p_line_id NUMBER) IS
602 SELECT customer_trx_line_id
603 FROM ra_customer_trx_lines
604 WHERE customer_trx_line_id = p_line_id
605 AND line_type = 'LINE';
606
607 BEGIN
608 IF PG_DEBUG in ('Y', 'C') THEN
609 arp_util.debug('AR_RAAPI_UTIL.Validate_Salesreps()+');
610 END IF;
611 -- Initialize message list if p_init_msg_list is set to TRUE.
612 IF FND_API.to_Boolean( p_init_msg_list )
613 THEN
614 FND_MSG_PUB.initialize;
615 END IF;
616 --
617 -- Validate from salesrep
618 --
619 IF g_from_salesrep_id IS NOT NULL AND
620 NVL(p_rev_adj_rec.from_salesrep_id,g_from_salesrep_id - 1)
621 = g_from_salesrep_id
625 --
622 THEN
623 --
624 -- Don't revalidate if validated previously in this session
626 NULL;
627 ElSE
628 l_rep_group_changed := TRUE; -- bug 3067675
629 IF p_rev_adj_rec.adjustment_type <> 'NR'
630 THEN
631 IF p_rev_adj_rec.from_salesrep_id IS NULL
632 THEN
633 IF p_rev_adj_rec.from_salesrep_number IS NOT NULL
634 THEN
635 OPEN c_salesrep_num (p_rev_adj_rec.from_salesrep_number);
636 FETCH c_salesrep_num INTO g_from_salesrep_id;
637 IF c_salesrep_num%NOTFOUND
638 THEN
639 /* Bug 2157246 - shortened message */
640 /* Bug 2191739 - call to message API for degovtized message */
641 FND_MESSAGE.set_name
642 (application => 'AR',
643 name => gl_public_sector.get_message_name
644 (p_message_name => 'AR_RA_INVALID_SALESREP_NUM',
645 p_app_short_name => 'AR'));
646 FND_MESSAGE.set_token('SALESREP_NUMBER',
647 p_rev_adj_rec.from_salesrep_number);
648 FND_MSG_PUB.Add;
649 x_return_status := FND_API.G_RET_STS_ERROR ;
650 END IF;
651 CLOSE c_salesrep_num;
652 END IF;
653 ELSE
654 OPEN c_salesrep_id(p_rev_adj_rec.from_salesrep_id);
655 FETCH c_salesrep_id INTO g_from_salesrep_id;
656 IF c_salesrep_id%NOTFOUND
657 THEN
658 FND_MESSAGE.set_name (application => 'AR',
659 name => 'AR_TAPI_INVALID_SALESREP_ID');
660 FND_MESSAGE.set_token('SALESREP_ID',
661 p_rev_adj_rec.from_salesrep_id);
662 FND_MSG_PUB.Add;
663 x_return_status := FND_API.G_RET_STS_ERROR ;
664 END IF;
665 CLOSE c_salesrep_id;
666 END IF;
667 END IF;
668
669 /* BEGIN bug 3067675 */
670 END IF;
671
672 --
673 -- Validate from salesgroup
674 --
675 IF g_from_salesgroup_id IS NOT NULL AND
676 NVL(p_rev_adj_rec.from_salesgroup_id,g_from_salesgroup_id - 1)
677 = g_from_salesgroup_id
678 THEN
679 --
680 -- Don't revalidate if validated previously in this session
681 --
682 NULL;
683 ElSE
684 l_rep_group_changed := TRUE;
685 IF p_rev_adj_rec.adjustment_type <> 'NR'
686 THEN
687 IF p_rev_adj_rec.from_salesgroup_id IS NOT NULL
688 THEN
689 g_from_salesgroup_id := p_rev_adj_rec.from_salesgroup_id;
690 END IF;
691 END IF;
692 END IF;
693
694 IF ((l_rep_group_changed) AND ((g_from_salesrep_id IS NOT NULL) OR (g_from_salesgroup_id IS NOT NULL)))
695 --IF g_from_salesrep_id IS NOT NULL
696 /* END bug 3067675 */
697
698 THEN
699 --
700 -- Check from salesrep,salesgroup has existing sales credits on the transaction
701 --
702 OPEN c_check_sales_credits;
703 FETCH c_check_sales_credits INTO l_sales_credit_total
704 --begin anuj
705 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
706 ,l_org_id;
707 /* Multi-Org Access Control Changes for SSA;end;anukumar;11/01/2002*/
708 --end anuj
709
710
711 CLOSE c_check_sales_credits;
712 IF NVL(l_sales_credit_total,0) = 0
713 THEN
714 /* Bug 2191739 - call to message API for degovtized message */
715 FND_MESSAGE.set_name
716 (application => 'AR',
717 name => gl_public_sector.get_message_name
718 (p_message_name => 'AR_RA_SALESREP_NOT_ON_TRX',
719 p_app_short_name => 'AR'));
720 FND_MESSAGE.set_token('SALESREP_NAME',
721 --begin anuj
722 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
723 -- ARPT_SQL_FUNC_UTIL.get_salesrep_name_number(g_from_salesrep_id,'NAME'));
724 ARPT_SQL_FUNC_UTIL.get_salesrep_name_number(g_from_salesrep_id,'NAME',l_org_id));
725 /* Multi-Org Access Control Changes for SSA;end;anukumar;11/01/2002*/
726 --end anuj
727 FND_MSG_PUB.Add;
728 x_return_status := FND_API.G_RET_STS_ERROR ;
729 END IF;
730 END IF;
731 --END IF; -- commented for bug 3067675
732 --
733 -- Validate To salesrep
734 --
735 IF g_to_salesrep_id IS NOT NULL AND
736 NVL(p_rev_adj_rec.to_salesrep_id,g_to_salesrep_id - 1) = g_to_salesrep_id
737 THEN
738 --
739 -- Don't revalidate if validated previously in this session
740 --
741 NULL;
742 ELSE
743 IF p_rev_adj_rec.adjustment_type IN ('NR','SA')
744 THEN
745 IF p_rev_adj_rec.to_salesrep_id IS NULL
746 THEN
747 IF p_rev_adj_rec.to_salesrep_number IS NULL
748 THEN
749 /* Bug 2191739 - call to message API for degovtized message */
750 FND_MESSAGE.set_name
751 (application => 'AR',
752 name => gl_public_sector.get_message_name
753 (p_message_name => 'AR_RA_NO_TO_SALESREP',
754 p_app_short_name => 'AR'));
758 OPEN c_salesrep_num (p_rev_adj_rec.to_salesrep_number);
755 FND_MSG_PUB.Add;
756 x_return_status := FND_API.G_RET_STS_ERROR ;
757 ELSE
759 FETCH c_salesrep_num INTO g_to_salesrep_id;
760 IF c_salesrep_num%NOTFOUND
761 THEN
762 /* Bug 2157246 - shortened message */
763 /* Bug 2191739 - call to message API for degovtized message */
764 FND_MESSAGE.set_name
765 (application => 'AR',
766 name => gl_public_sector.get_message_name
767 (p_message_name => 'AR_RA_INVALID_SALESREP_NUM',
768 p_app_short_name => 'AR'));
769 FND_MESSAGE.set_token('SALESREP_NUMBER',
770 p_rev_adj_rec.to_salesrep_number);
771 FND_MSG_PUB.Add;
772 x_return_status := FND_API.G_RET_STS_ERROR ;
773 END IF;
774 CLOSE c_salesrep_num;
775 END IF;
776 ELSE
777 OPEN c_salesrep_id(p_rev_adj_rec.to_salesrep_id);
778 FETCH c_salesrep_id INTO g_to_salesrep_id;
779 IF c_salesrep_id%NOTFOUND
780 THEN
781 FND_MESSAGE.set_name (application => 'AR',
782 name => 'AR_TAPI_INVALID_SALESREP_ID');
783 FND_MESSAGE.set_token('SALESREP_ID',
784 p_rev_adj_rec.to_salesrep_id);
785 FND_MSG_PUB.Add;
786 x_return_status := FND_API.G_RET_STS_ERROR ;
787 END IF;
788 CLOSE c_salesrep_id;
789 END IF;
790 END IF;
791 END IF;
792
793 /* BEGIN bug 3067675 */
794 --
795 -- Validate To salesgroup
796 --
797 IF g_to_salesgroup_id IS NOT NULL AND
798 NVL(p_rev_adj_rec.to_salesgroup_id,g_to_salesgroup_id - 1) = g_to_salesgroup_id
799 THEN
800 --
801 -- Don't revalidate if validated previously in this session
802 --
803 NULL;
804 ELSE
805 IF p_rev_adj_rec.adjustment_type IN ('NR','SA')
806 THEN
807 IF p_rev_adj_rec.to_salesgroup_id IS NOT NULL
808 THEN
809 arp_util.Get_Txn_Start_End_Dates(p_rev_adj_rec.customer_trx_id, l_group_start_date, l_group_end_date);
810 OPEN c_salesgroup_id(p_rev_adj_rec.to_salesgroup_id);
811 FETCH c_salesgroup_id INTO g_to_salesgroup_id;
812 IF c_salesgroup_id%NOTFOUND
813 THEN
814 FND_MESSAGE.set_name (application => 'AR',
815 name => 'AR_INVALID_SALESGROUP_ID');
816 FND_MSG_PUB.Add;
817 x_return_status := FND_API.G_RET_STS_ERROR ;
818 END IF;
819 CLOSE c_salesgroup_id;
820 END IF;
821 END IF;
822 END IF;
823 /* END bug 3067675 */
824
825 FND_MSG_PUB.Count_And_Get
826 (p_encoded => FND_API.G_FALSE,
827 p_count => x_msg_count,
828 p_data => x_msg_data);
829 EXCEPTION
830 WHEN OTHERS THEN
831 IF PG_DEBUG in ('Y', 'C') THEN
832 arp_util.debug('Validate_Salesreps: ' || 'Unexpected error '||sqlerrm||
833 ' at AR_RAAPI_UTIL.Validate_Salesreps()+');
834 END IF;
835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836 END Validate_Salesreps;
837
838 PROCEDURE Validate_Category
839 (p_init_msg_list IN VARCHAR2
840 ,p_rev_adj_rec IN AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type
841 ,x_return_status IN OUT NOCOPY VARCHAR2
842 ,x_msg_count OUT NOCOPY NUMBER
843 ,x_msg_data OUT NOCOPY VARCHAR2)
844 IS
845
846 l_segment_rec Segment_Rec_Type;
847 l_cat_count NUMBER;
848
849 /* Bug 2157246 - replaced CHR(0) with FND_API.G_MISS_CHAR */
850 CURSOR c_category_segs (p_segment_rec Segment_Rec_Type) IS
851 SELECT category_id
852 FROM mtl_categories_vl
853 WHERE NVL(segment1,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment1,FND_API.G_MISS_CHAR)
854 AND NVL(segment2,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment2,FND_API.G_MISS_CHAR)
855 AND NVL(segment3,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment3,FND_API.G_MISS_CHAR)
856 AND NVL(segment4,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment4,FND_API.G_MISS_CHAR)
857 AND NVL(segment5,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment5,FND_API.G_MISS_CHAR)
858 AND NVL(segment6,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment6,FND_API.G_MISS_CHAR)
859 AND NVL(segment7,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment7,FND_API.G_MISS_CHAR)
860 AND NVL(segment8,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment8,FND_API.G_MISS_CHAR)
861 AND NVL(segment9,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment9,FND_API.G_MISS_CHAR)
862 AND NVL(segment10,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment10,FND_API.G_MISS_CHAR)
863 AND NVL(segment11,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment11,FND_API.G_MISS_CHAR)
864 AND NVL(segment12,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment12,FND_API.G_MISS_CHAR)
865 AND NVL(segment13,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment13,FND_API.G_MISS_CHAR)
866 AND NVL(segment14,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment14,FND_API.G_MISS_CHAR)
870 AND NVL(segment18,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment18,FND_API.G_MISS_CHAR)
867 AND NVL(segment15,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment15,FND_API.G_MISS_CHAR)
868 AND NVL(segment16,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment16,FND_API.G_MISS_CHAR)
869 AND NVL(segment17,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment17,FND_API.G_MISS_CHAR)
871 AND NVL(segment19,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment19,FND_API.G_MISS_CHAR)
872 AND NVL(segment20,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment20,FND_API.G_MISS_CHAR)
873 AND structure_id = g_category_structure_id;
874
875 CURSOR c_category_id(p_category_id NUMBER) IS
876 SELECT category_id
877 FROM mtl_categories_vl
878 WHERE category_id = p_category_id
879 AND structure_id = g_category_structure_id;
880
881 CURSOR c_cat_exists_on_trx(p_category_id NUMBER) IS
882 SELECT COUNT(*)
883 FROM mtl_item_categories c,
884 ra_customer_trx_lines l
885 WHERE c.inventory_item_id = l.inventory_item_id
886 AND l.customer_trx_id = g_customer_trx_id
887 AND c.category_id = p_category_id
888 AND l.line_type = 'LINE'
889 AND c.category_set_id = g_category_set_id
890 AND c.organization_id = g_inv_org_id;
891
892 BEGIN
893 IF PG_DEBUG in ('Y', 'C') THEN
894 arp_util.debug('AR_RAAPI_UTIL.Validate_Category()+');
895 END IF;
896
897 /* 5126974 - move initialization to this function
898 to avoid org-specific failure in constant_system_values */
899 IF g_inv_org_id IS NULL
900 THEN
901 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
902 END IF;
903
904 -- Initialize message list if p_init_msg_list is set to TRUE.
905 IF FND_API.to_Boolean( p_init_msg_list )
906 THEN
907 FND_MSG_PUB.initialize;
908 END IF;
909 --
910 -- Validate category
911 --
912 IF g_from_category_id IS NOT NULL AND
913 NVL(p_rev_adj_rec.from_category_id,g_from_category_id - 1)
914 = g_from_category_id
915 THEN
916 --
917 -- Don't revalidate if validated previously in this session
918 --
919 NULL;
920 ELSE
921 IF p_rev_adj_rec.from_category_id IS NULL
922 THEN
923 IF (p_rev_adj_rec.from_category_segment1 IS NOT NULL OR
924 p_rev_adj_rec.from_category_segment2 IS NOT NULL OR
925 p_rev_adj_rec.from_category_segment3 IS NOT NULL OR
926 p_rev_adj_rec.from_category_segment4 IS NOT NULL OR
927 p_rev_adj_rec.from_category_segment5 IS NOT NULL OR
928 p_rev_adj_rec.from_category_segment6 IS NOT NULL OR
929 p_rev_adj_rec.from_category_segment7 IS NOT NULL OR
930 p_rev_adj_rec.from_category_segment8 IS NOT NULL OR
931 p_rev_adj_rec.from_category_segment9 IS NOT NULL OR
932 p_rev_adj_rec.from_category_segment10 IS NOT NULL OR
933 p_rev_adj_rec.from_category_segment11 IS NOT NULL OR
934 p_rev_adj_rec.from_category_segment12 IS NOT NULL OR
935 p_rev_adj_rec.from_category_segment13 IS NOT NULL OR
936 p_rev_adj_rec.from_category_segment14 IS NOT NULL OR
937 p_rev_adj_rec.from_category_segment15 IS NOT NULL OR
938 p_rev_adj_rec.from_category_segment16 IS NOT NULL OR
939 p_rev_adj_rec.from_category_segment17 IS NOT NULL OR
940 p_rev_adj_rec.from_category_segment18 IS NOT NULL OR
941 p_rev_adj_rec.from_category_segment19 IS NOT NULL OR
942 p_rev_adj_rec.from_category_segment20 IS NOT NULL)
943 THEN
944 l_segment_rec.segment1 := p_rev_adj_rec.from_category_segment1;
945 l_segment_rec.segment2 := p_rev_adj_rec.from_category_segment2;
946 l_segment_rec.segment3 := p_rev_adj_rec.from_category_segment3;
947 l_segment_rec.segment4 := p_rev_adj_rec.from_category_segment4;
948 l_segment_rec.segment5 := p_rev_adj_rec.from_category_segment5;
949 l_segment_rec.segment6 := p_rev_adj_rec.from_category_segment6;
950 l_segment_rec.segment7 := p_rev_adj_rec.from_category_segment7;
951 l_segment_rec.segment8 := p_rev_adj_rec.from_category_segment8;
952 l_segment_rec.segment9 := p_rev_adj_rec.from_category_segment9;
953 l_segment_rec.segment10 := p_rev_adj_rec.from_category_segment10;
954 l_segment_rec.segment11 := p_rev_adj_rec.from_category_segment11;
955 l_segment_rec.segment12 := p_rev_adj_rec.from_category_segment12;
956 l_segment_rec.segment13 := p_rev_adj_rec.from_category_segment13;
957 l_segment_rec.segment14 := p_rev_adj_rec.from_category_segment14;
958 l_segment_rec.segment15 := p_rev_adj_rec.from_category_segment15;
959 l_segment_rec.segment16 := p_rev_adj_rec.from_category_segment16;
960 l_segment_rec.segment17 := p_rev_adj_rec.from_category_segment17;
961 l_segment_rec.segment18 := p_rev_adj_rec.from_category_segment18;
962 l_segment_rec.segment19 := p_rev_adj_rec.from_category_segment19;
963 l_segment_rec.segment20 := p_rev_adj_rec.from_category_segment20;
964 OPEN c_category_segs(l_segment_rec);
965 FETCH c_category_segs INTO g_from_category_id;
966 IF c_category_segs%NOTFOUND
967 THEN
968 FND_MESSAGE.set_name (application => 'AR',
972 l_segment_rec.segment4||l_segment_rec.segment5||
969 name => 'AR_RA_INVALID_CAT_SEGMENTS');
970 FND_MESSAGE.set_token('CONCAT_SEGS', l_segment_rec.segment1||
971 l_segment_rec.segment2||l_segment_rec.segment3||
973 l_segment_rec.segment6||l_segment_rec.segment7||
974 l_segment_rec.segment8||l_segment_rec.segment9||
975 l_segment_rec.segment10||l_segment_rec.segment11||
976 l_segment_rec.segment12||l_segment_rec.segment13||
977 l_segment_rec.segment14||l_segment_rec.segment15||
978 l_segment_rec.segment16||l_segment_rec.segment17||
979 l_segment_rec.segment18||l_segment_rec.segment19||
980 l_segment_rec.segment20);
981 FND_MSG_PUB.Add;
982 x_return_status := FND_API.G_RET_STS_ERROR ;
983 END IF;
984 CLOSE c_category_segs;
985 ELSIF p_rev_adj_rec.line_selection_mode = 'C'
986 THEN
987 FND_MESSAGE.set_name (application => 'AR',
988 name => 'AR_RA_NO_FROM_CATEGORY');
989 FND_MSG_PUB.Add;
990 x_return_status := FND_API.G_RET_STS_ERROR ;
991 END IF;
992 ELSE
993 OPEN c_category_id(p_rev_adj_rec.from_category_id);
994 FETCH c_category_id INTO g_from_category_id;
995 IF c_category_id%NOTFOUND
996 THEN
997 FND_MESSAGE.set_name (application => 'AR',
998 name => 'AR_RA_INVALID_CATEGORY_ID');
999 FND_MESSAGE.set_token('CATEGORY_ID', p_rev_adj_rec.from_category_id);
1000 FND_MSG_PUB.Add;
1001 x_return_status := FND_API.G_RET_STS_ERROR ;
1002 END IF;
1003 CLOSE c_category_id;
1004 END IF;
1005 IF g_from_category_id IS NOT NULL
1006 THEN
1007 OPEN c_cat_exists_on_trx(g_from_category_id);
1008 FETCH c_cat_exists_on_trx INTO l_cat_count;
1009 CLOSE c_cat_exists_on_trx;
1010 IF l_cat_count = 0
1011 THEN
1012 FND_MESSAGE.set_name (application => 'AR',
1013 name => 'AR_RA_CATEGORY_NOT_ON_TRX');
1014 FND_MESSAGE.set_token('CATEGORY_ID', p_rev_adj_rec.from_category_id);
1015 FND_MSG_PUB.Add;
1016 x_return_status := FND_API.G_RET_STS_ERROR ;
1017 END IF;
1018 END IF;
1019 END IF;
1020 --
1021 -- Validate to category if line transfer
1022 --
1023 IF g_to_category_id IS NOT NULL AND
1024 NVL(p_rev_adj_rec.to_category_id,g_to_category_id - 1) = g_to_category_id
1025 THEN
1026 --
1027 -- Don't revalidate if validated previously in this session
1028 --
1029 NULL;
1030 ELSE
1031 IF p_rev_adj_rec.adjustment_type = 'LL' AND
1032 p_rev_adj_rec.line_selection_mode = 'C'
1033 THEN
1034 IF p_rev_adj_rec.to_category_id IS NULL
1035 THEN
1036 IF (p_rev_adj_rec.to_category_segment1 IS NOT NULL OR
1037 p_rev_adj_rec.to_category_segment2 IS NOT NULL OR
1038 p_rev_adj_rec.to_category_segment3 IS NOT NULL OR
1039 p_rev_adj_rec.to_category_segment4 IS NOT NULL OR
1040 p_rev_adj_rec.to_category_segment5 IS NOT NULL OR
1041 p_rev_adj_rec.to_category_segment6 IS NOT NULL OR
1042 p_rev_adj_rec.to_category_segment7 IS NOT NULL OR
1043 p_rev_adj_rec.to_category_segment8 IS NOT NULL OR
1044 p_rev_adj_rec.to_category_segment9 IS NOT NULL OR
1045 p_rev_adj_rec.to_category_segment10 IS NOT NULL OR
1046 p_rev_adj_rec.to_category_segment11 IS NOT NULL OR
1047 p_rev_adj_rec.to_category_segment12 IS NOT NULL OR
1048 p_rev_adj_rec.to_category_segment13 IS NOT NULL OR
1049 p_rev_adj_rec.to_category_segment14 IS NOT NULL OR
1050 p_rev_adj_rec.to_category_segment15 IS NOT NULL OR
1051 p_rev_adj_rec.to_category_segment16 IS NOT NULL OR
1052 p_rev_adj_rec.to_category_segment17 IS NOT NULL OR
1053 p_rev_adj_rec.to_category_segment18 IS NOT NULL OR
1054 p_rev_adj_rec.to_category_segment19 IS NOT NULL OR
1055 p_rev_adj_rec.to_category_segment20 IS NOT NULL)
1056 THEN
1057 l_segment_rec.segment1 := p_rev_adj_rec.to_category_segment1;
1058 l_segment_rec.segment2 := p_rev_adj_rec.to_category_segment2;
1059 l_segment_rec.segment3 := p_rev_adj_rec.to_category_segment3;
1060 l_segment_rec.segment4 := p_rev_adj_rec.to_category_segment4;
1061 l_segment_rec.segment5 := p_rev_adj_rec.to_category_segment5;
1062 l_segment_rec.segment6 := p_rev_adj_rec.to_category_segment6;
1063 l_segment_rec.segment7 := p_rev_adj_rec.to_category_segment7;
1064 l_segment_rec.segment8 := p_rev_adj_rec.to_category_segment8;
1065 l_segment_rec.segment9 := p_rev_adj_rec.to_category_segment9;
1066 l_segment_rec.segment10 := p_rev_adj_rec.to_category_segment10;
1067 l_segment_rec.segment11 := p_rev_adj_rec.to_category_segment11;
1068 l_segment_rec.segment12 := p_rev_adj_rec.to_category_segment12;
1069 l_segment_rec.segment13 := p_rev_adj_rec.to_category_segment13;
1070 l_segment_rec.segment14 := p_rev_adj_rec.to_category_segment14;
1071 l_segment_rec.segment15 := p_rev_adj_rec.to_category_segment15;
1075 l_segment_rec.segment19 := p_rev_adj_rec.to_category_segment19;
1072 l_segment_rec.segment16 := p_rev_adj_rec.to_category_segment16;
1073 l_segment_rec.segment17 := p_rev_adj_rec.to_category_segment17;
1074 l_segment_rec.segment18 := p_rev_adj_rec.to_category_segment18;
1076 l_segment_rec.segment20 := p_rev_adj_rec.to_category_segment20;
1077 OPEN c_category_segs(l_segment_rec);
1078 FETCH c_category_segs INTO g_to_category_id;
1079 IF c_category_segs%NOTFOUND
1080 THEN
1081 FND_MESSAGE.set_name (application => 'AR',
1082 name => 'AR_RA_INVALID_CAT_SEGMENTS');
1083 FND_MESSAGE.set_token('CONCAT_SEGS', l_segment_rec.segment1||
1084 l_segment_rec.segment2||l_segment_rec.segment3||
1085 l_segment_rec.segment4||l_segment_rec.segment5||
1086 l_segment_rec.segment6||l_segment_rec.segment7||
1087 l_segment_rec.segment8||l_segment_rec.segment9||
1088 l_segment_rec.segment10||l_segment_rec.segment11||
1089 l_segment_rec.segment12||l_segment_rec.segment13||
1090 l_segment_rec.segment14||l_segment_rec.segment15||
1091 l_segment_rec.segment16||l_segment_rec.segment17||
1092 l_segment_rec.segment18||l_segment_rec.segment19||
1093 l_segment_rec.segment20);
1094 FND_MSG_PUB.Add;
1095 x_return_status := FND_API.G_RET_STS_ERROR ;
1096 END IF;
1097 CLOSE c_category_segs;
1098 ELSE
1099 FND_MESSAGE.set_name (application => 'AR',
1100 name => 'AR_RA_NO_TO_CATEGORY');
1101 FND_MSG_PUB.Add;
1102 x_return_status := FND_API.G_RET_STS_ERROR ;
1103 END IF;
1104 ELSE
1105 OPEN c_category_id(p_rev_adj_rec.to_category_id);
1106 FETCH c_category_id INTO g_to_category_id;
1107 IF c_category_id%NOTFOUND
1108 THEN
1109 FND_MESSAGE.set_name (application => 'AR',
1110 name => 'AR_RA_INVALID_CATEGORY_ID');
1111 FND_MESSAGE.set_token('CATEGORY_ID', p_rev_adj_rec.to_category_id);
1112 FND_MSG_PUB.Add;
1113 x_return_status := FND_API.G_RET_STS_ERROR ;
1114 END IF;
1115 CLOSE c_category_id;
1116 END IF;
1117 IF g_to_category_id IS NOT NULL
1118 THEN
1119 OPEN c_cat_exists_on_trx(g_to_category_id);
1120 FETCH c_cat_exists_on_trx INTO l_cat_count;
1121 CLOSE c_cat_exists_on_trx;
1122 IF l_cat_count = 0
1123 THEN
1124 FND_MESSAGE.set_name (application => 'AR',
1125 name => 'AR_RA_CATEGORY_NOT_ON_TRX');
1126 FND_MESSAGE.set_token('CATEGORY_ID', p_rev_adj_rec.to_category_id);
1127 FND_MSG_PUB.Add;
1128 x_return_status := FND_API.G_RET_STS_ERROR ;
1129 END IF;
1130 END IF;
1131 END IF;
1132 END IF;
1133 FND_MSG_PUB.Count_And_Get
1134 (p_encoded => FND_API.G_FALSE,
1135 p_count => x_msg_count,
1136 p_data => x_msg_data);
1137 EXCEPTION
1138 WHEN TOO_MANY_ROWS THEN
1139 FND_MESSAGE.set_name (application => 'AR',
1140 name => 'AR_RA_INVALID_CAT_SEGMENTS');
1141 FND_MESSAGE.set_token('CONCAT_SEGS', l_segment_rec.segment1||
1142 l_segment_rec.segment2||l_segment_rec.segment3||
1143 l_segment_rec.segment4||l_segment_rec.segment5||
1144 l_segment_rec.segment6||l_segment_rec.segment7||
1145 l_segment_rec.segment8||l_segment_rec.segment9||
1146 l_segment_rec.segment10||l_segment_rec.segment11||
1147 l_segment_rec.segment12||l_segment_rec.segment13||
1148 l_segment_rec.segment14||l_segment_rec.segment15||
1149 l_segment_rec.segment16||l_segment_rec.segment17||
1150 l_segment_rec.segment18||l_segment_rec.segment19||
1151 l_segment_rec.segment20);
1152 FND_MSG_PUB.Add;
1153 x_return_status := FND_API.G_RET_STS_ERROR ;
1154 WHEN OTHERS THEN
1155 IF PG_DEBUG in ('Y', 'C') THEN
1156 arp_util.debug('Validate_Category: ' || 'Unexpected error '||sqlerrm||
1157 ' at AR_RAAPI_UTIL.Validate_Category()+');
1158 END IF;
1159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1160 END Validate_Category;
1161
1162 PROCEDURE Validate_Item
1163 (p_init_msg_list IN VARCHAR2
1164 ,p_rev_adj_rec IN AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type
1165 ,x_return_status IN OUT NOCOPY VARCHAR2
1166 ,x_msg_count OUT NOCOPY NUMBER
1167 ,x_msg_data OUT NOCOPY VARCHAR2)
1168 IS
1169
1170 l_segment_rec Segment_Rec_Type;
1171 l_item_count NUMBER;
1172
1173 /* Bug 2157246 - replaced CHR(0) with FND_API.G_MISS_CHAR */
1174 CURSOR c_item_segs (p_segment_rec Segment_Rec_Type) IS
1175 SELECT inventory_item_id
1176 FROM mtl_system_items
1177 WHERE NVL(segment1,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment1,FND_API.G_MISS_CHAR)
1178 AND NVL(segment2,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment2,FND_API.G_MISS_CHAR)
1179 AND NVL(segment3,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment3,FND_API.G_MISS_CHAR)
1183 AND NVL(segment7,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment7,FND_API.G_MISS_CHAR)
1180 AND NVL(segment4,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment4,FND_API.G_MISS_CHAR)
1181 AND NVL(segment5,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment5,FND_API.G_MISS_CHAR)
1182 AND NVL(segment6,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment6,FND_API.G_MISS_CHAR)
1184 AND NVL(segment8,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment8,FND_API.G_MISS_CHAR)
1185 AND NVL(segment9,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment9,FND_API.G_MISS_CHAR)
1186 AND NVL(segment10,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment10,FND_API.G_MISS_CHAR)
1187 AND NVL(segment11,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment11,FND_API.G_MISS_CHAR)
1188 AND NVL(segment12,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment12,FND_API.G_MISS_CHAR)
1189 AND NVL(segment13,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment13,FND_API.G_MISS_CHAR)
1190 AND NVL(segment14,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment14,FND_API.G_MISS_CHAR)
1191 AND NVL(segment15,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment15,FND_API.G_MISS_CHAR)
1192 AND NVL(segment16,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment16,FND_API.G_MISS_CHAR)
1193 AND NVL(segment17,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment17,FND_API.G_MISS_CHAR)
1194 AND NVL(segment18,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment18,FND_API.G_MISS_CHAR)
1195 AND NVL(segment19,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment19,FND_API.G_MISS_CHAR)
1196 AND NVL(segment20,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment20,FND_API.G_MISS_CHAR)
1197 AND organization_id = g_inv_org_id;
1198
1199 CURSOR c_item_id(p_item_id NUMBER) IS
1200 SELECT inventory_item_id
1201 FROM mtl_system_items
1202 WHERE inventory_item_id = p_item_id
1203 AND organization_id = g_inv_org_id;
1204
1205 CURSOR c_item_exists_on_trx(p_item_id NUMBER) IS
1206 SELECT COUNT(*)
1207 FROM ra_customer_trx_lines
1208 WHERE customer_trx_id = g_customer_trx_id
1209 AND inventory_item_id = p_item_id
1210 AND line_type = 'LINE';
1211
1212 BEGIN
1213 IF PG_DEBUG in ('Y', 'C') THEN
1214 arp_util.debug('AR_RAAPI_UTIL.Validate_Item()+');
1215 END IF;
1216 /* 5126974 - move initialization to this function
1217 to avoid org-specific failure in constant_system_values */
1218 IF g_inv_org_id IS NULL
1219 THEN
1220 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
1221 END IF;
1222 -- Initialize message list if p_init_msg_list is set to TRUE.
1223 IF FND_API.to_Boolean( p_init_msg_list )
1224 THEN
1225 FND_MSG_PUB.initialize;
1226 END IF;
1227 --
1228 -- Validate from item
1229 --
1230 IF g_from_inventory_item_id IS NOT NULL AND
1231 NVL(p_rev_adj_rec.from_inventory_item_id,g_from_inventory_item_id - 1)
1232 = g_from_inventory_item_id
1233 THEN
1234 --
1235 -- Don't revalidate if validated previously in this session
1236 --
1237 NULL;
1238 ELSE
1239 IF p_rev_adj_rec.from_inventory_item_id IS NULL
1240 THEN
1241 IF (p_rev_adj_rec.from_item_segment1 IS NOT NULL OR
1242 p_rev_adj_rec.from_item_segment2 IS NOT NULL OR
1243 p_rev_adj_rec.from_item_segment3 IS NOT NULL OR
1244 p_rev_adj_rec.from_item_segment4 IS NOT NULL OR
1245 p_rev_adj_rec.from_item_segment5 IS NOT NULL OR
1246 p_rev_adj_rec.from_item_segment6 IS NOT NULL OR
1247 p_rev_adj_rec.from_item_segment7 IS NOT NULL OR
1248 p_rev_adj_rec.from_item_segment8 IS NOT NULL OR
1249 p_rev_adj_rec.from_item_segment9 IS NOT NULL OR
1250 p_rev_adj_rec.from_item_segment10 IS NOT NULL OR
1251 p_rev_adj_rec.from_item_segment11 IS NOT NULL OR
1252 p_rev_adj_rec.from_item_segment12 IS NOT NULL OR
1253 p_rev_adj_rec.from_item_segment13 IS NOT NULL OR
1254 p_rev_adj_rec.from_item_segment14 IS NOT NULL OR
1255 p_rev_adj_rec.from_item_segment15 IS NOT NULL OR
1256 p_rev_adj_rec.from_item_segment16 IS NOT NULL OR
1257 p_rev_adj_rec.from_item_segment17 IS NOT NULL OR
1258 p_rev_adj_rec.from_item_segment18 IS NOT NULL OR
1259 p_rev_adj_rec.from_item_segment19 IS NOT NULL OR
1260 p_rev_adj_rec.from_item_segment20 IS NOT NULL)
1261 THEN
1262 l_segment_rec.segment1 := p_rev_adj_rec.from_item_segment1;
1263 l_segment_rec.segment2 := p_rev_adj_rec.from_item_segment2;
1264 l_segment_rec.segment3 := p_rev_adj_rec.from_item_segment3;
1265 l_segment_rec.segment4 := p_rev_adj_rec.from_item_segment4;
1266 l_segment_rec.segment5 := p_rev_adj_rec.from_item_segment5;
1267 l_segment_rec.segment6 := p_rev_adj_rec.from_item_segment6;
1268 l_segment_rec.segment7 := p_rev_adj_rec.from_item_segment7;
1269 l_segment_rec.segment8 := p_rev_adj_rec.from_item_segment8;
1270 l_segment_rec.segment9 := p_rev_adj_rec.from_item_segment9;
1271 l_segment_rec.segment10 := p_rev_adj_rec.from_item_segment10;
1272 l_segment_rec.segment11 := p_rev_adj_rec.from_item_segment11;
1273 l_segment_rec.segment12 := p_rev_adj_rec.from_item_segment12;
1274 l_segment_rec.segment13 := p_rev_adj_rec.from_item_segment13;
1275 l_segment_rec.segment14 := p_rev_adj_rec.from_item_segment14;
1276 l_segment_rec.segment15 := p_rev_adj_rec.from_item_segment15;
1277 l_segment_rec.segment16 := p_rev_adj_rec.from_item_segment16;
1281 l_segment_rec.segment20 := p_rev_adj_rec.from_item_segment20;
1278 l_segment_rec.segment17 := p_rev_adj_rec.from_item_segment17;
1279 l_segment_rec.segment18 := p_rev_adj_rec.from_item_segment18;
1280 l_segment_rec.segment19 := p_rev_adj_rec.from_item_segment19;
1282 OPEN c_item_segs(l_segment_rec);
1283 FETCH c_item_segs INTO g_from_inventory_item_id;
1284 IF c_item_segs%NOTFOUND
1285 THEN
1286 FND_MESSAGE.set_name (application => 'AR',
1287 name => 'AR_RA_INVALID_ITEM_SEGMENTS');
1288 FND_MESSAGE.set_token('CONCAT_SEGS', l_segment_rec.segment1||
1289 l_segment_rec.segment2||l_segment_rec.segment3||
1290 l_segment_rec.segment4||l_segment_rec.segment5||
1291 l_segment_rec.segment6||l_segment_rec.segment7||
1292 l_segment_rec.segment8||l_segment_rec.segment9||
1293 l_segment_rec.segment10||l_segment_rec.segment11||
1294 l_segment_rec.segment12||l_segment_rec.segment13||
1295 l_segment_rec.segment14||l_segment_rec.segment15||
1296 l_segment_rec.segment16||l_segment_rec.segment17||
1297 l_segment_rec.segment18||l_segment_rec.segment19||
1298 l_segment_rec.segment20);
1299 FND_MSG_PUB.Add;
1300 x_return_status := FND_API.G_RET_STS_ERROR ;
1301 END IF;
1302 CLOSE c_item_segs;
1303 ELSIF p_rev_adj_rec.line_selection_mode = 'I'
1304 THEN
1305 FND_MESSAGE.set_name (application => 'AR',
1306 name => 'AR_RA_NO_FROM_ITEM');
1307 FND_MSG_PUB.Add;
1308 x_return_status := FND_API.G_RET_STS_ERROR ;
1309 END IF;
1310 ELSE
1311 OPEN c_item_id(p_rev_adj_rec.from_inventory_item_id);
1312 FETCH c_item_id INTO g_from_inventory_item_id;
1313 IF c_item_id%NOTFOUND
1314 THEN
1315 FND_MESSAGE.set_name (application => 'AR',
1316 name => 'AR_RA_INVALID_ITEM_ID');
1317 FND_MESSAGE.set_token('ITEM_ID',p_rev_adj_rec.from_inventory_item_id);
1318 FND_MSG_PUB.Add;
1319 x_return_status := FND_API.G_RET_STS_ERROR ;
1320 END IF;
1321 close c_item_id;
1322 END IF;
1323 IF g_from_inventory_item_id IS NOT NULL
1324 THEN
1325 OPEN c_item_exists_on_trx(g_from_inventory_item_id);
1326 FETCH c_item_exists_on_trx INTO l_item_count;
1327 CLOSE c_item_exists_on_trx;
1328 IF l_item_count = 0
1329 THEN
1330 FND_MESSAGE.set_name (application => 'AR',
1331 name => 'AR_RA_ITEM_NOT_ON_TRX');
1332 FND_MESSAGE.set_token('ITEM_ID',p_rev_adj_rec.from_inventory_item_id);
1333 FND_MSG_PUB.Add;
1334 x_return_status := FND_API.G_RET_STS_ERROR ;
1335 END IF;
1336 END IF;
1337 END IF;
1338 --
1339 -- Validate to item if line transfer
1340 --
1341 IF g_to_inventory_item_id IS NOT NULL AND
1342 NVL(p_rev_adj_rec.to_inventory_item_id,g_to_inventory_item_id - 1)
1343 = g_to_inventory_item_id
1344 THEN
1345 --
1346 -- Don't revalidate if validated previously in this session
1347 --
1348 NULL;
1349 ELSE
1350 IF p_rev_adj_rec.adjustment_type = 'LL' AND
1351 p_rev_adj_rec.line_selection_mode = 'I'
1352 THEN
1353 IF p_rev_adj_rec.to_inventory_item_id IS NULL
1354 THEN
1355 IF (p_rev_adj_rec.to_item_segment1 IS NOT NULL OR
1356 p_rev_adj_rec.to_item_segment2 IS NOT NULL OR
1357 p_rev_adj_rec.to_item_segment3 IS NOT NULL OR
1358 p_rev_adj_rec.to_item_segment4 IS NOT NULL OR
1359 p_rev_adj_rec.to_item_segment5 IS NOT NULL OR
1360 p_rev_adj_rec.to_item_segment6 IS NOT NULL OR
1361 p_rev_adj_rec.to_item_segment7 IS NOT NULL OR
1362 p_rev_adj_rec.to_item_segment8 IS NOT NULL OR
1363 p_rev_adj_rec.to_item_segment9 IS NOT NULL OR
1364 p_rev_adj_rec.to_item_segment10 IS NOT NULL OR
1365 p_rev_adj_rec.to_item_segment11 IS NOT NULL OR
1366 p_rev_adj_rec.to_item_segment12 IS NOT NULL OR
1367 p_rev_adj_rec.to_item_segment13 IS NOT NULL OR
1368 p_rev_adj_rec.to_item_segment14 IS NOT NULL OR
1369 p_rev_adj_rec.to_item_segment15 IS NOT NULL OR
1370 p_rev_adj_rec.to_item_segment16 IS NOT NULL OR
1371 p_rev_adj_rec.to_item_segment17 IS NOT NULL OR
1372 p_rev_adj_rec.to_item_segment18 IS NOT NULL OR
1373 p_rev_adj_rec.to_item_segment19 IS NOT NULL OR
1374 p_rev_adj_rec.to_item_segment20 IS NOT NULL)
1375 THEN
1376 l_segment_rec.segment1 := p_rev_adj_rec.to_item_segment1;
1377 l_segment_rec.segment2 := p_rev_adj_rec.to_item_segment2;
1378 l_segment_rec.segment3 := p_rev_adj_rec.to_item_segment3;
1379 l_segment_rec.segment4 := p_rev_adj_rec.to_item_segment4;
1380 l_segment_rec.segment5 := p_rev_adj_rec.to_item_segment5;
1381 l_segment_rec.segment6 := p_rev_adj_rec.to_item_segment6;
1382 l_segment_rec.segment7 := p_rev_adj_rec.to_item_segment7;
1383 l_segment_rec.segment8 := p_rev_adj_rec.to_item_segment8;
1384 l_segment_rec.segment9 := p_rev_adj_rec.to_item_segment9;
1385 l_segment_rec.segment10 := p_rev_adj_rec.to_item_segment10;
1389 l_segment_rec.segment14 := p_rev_adj_rec.to_item_segment14;
1386 l_segment_rec.segment11 := p_rev_adj_rec.to_item_segment11;
1387 l_segment_rec.segment12 := p_rev_adj_rec.to_item_segment12;
1388 l_segment_rec.segment13 := p_rev_adj_rec.to_item_segment13;
1390 l_segment_rec.segment15 := p_rev_adj_rec.to_item_segment15;
1391 l_segment_rec.segment16 := p_rev_adj_rec.to_item_segment16;
1392 l_segment_rec.segment17 := p_rev_adj_rec.to_item_segment17;
1393 l_segment_rec.segment18 := p_rev_adj_rec.to_item_segment18;
1394 l_segment_rec.segment19 := p_rev_adj_rec.to_item_segment19;
1395 l_segment_rec.segment20 := p_rev_adj_rec.to_item_segment20;
1396 OPEN c_item_segs(l_segment_rec);
1397 FETCH c_item_segs INTO g_to_inventory_item_id;
1398 IF c_item_segs%NOTFOUND
1399 THEN
1400 FND_MESSAGE.set_name (application => 'AR',
1401 name => 'AR_RA_INVALID_ITEM_SEGMENTS');
1402 FND_MESSAGE.set_token('CONCAT_SEGS', l_segment_rec.segment1||
1403 l_segment_rec.segment2||l_segment_rec.segment3||
1404 l_segment_rec.segment4||l_segment_rec.segment5||
1405 l_segment_rec.segment6||l_segment_rec.segment7||
1406 l_segment_rec.segment8||l_segment_rec.segment9||
1407 l_segment_rec.segment10||l_segment_rec.segment11||
1408 l_segment_rec.segment12||l_segment_rec.segment13||
1409 l_segment_rec.segment14||l_segment_rec.segment15||
1410 l_segment_rec.segment16||l_segment_rec.segment17||
1411 l_segment_rec.segment18||l_segment_rec.segment19||
1412 l_segment_rec.segment20);
1413 FND_MSG_PUB.Add;
1414 x_return_status := FND_API.G_RET_STS_ERROR ;
1415 END IF;
1416 CLOSE c_item_segs;
1417 ELSE
1418 FND_MESSAGE.set_name (application => 'AR',
1419 name => 'AR_RA_NO_TO_ITEM');
1420 FND_MSG_PUB.Add;
1421 x_return_status := FND_API.G_RET_STS_ERROR ;
1422 END IF;
1423 ELSE
1424 OPEN c_item_id(p_rev_adj_rec.to_inventory_item_id);
1425 FETCH c_item_id INTO g_to_inventory_item_id;
1426 IF c_item_id%NOTFOUND
1427 THEN
1428 FND_MESSAGE.set_name (application => 'AR',
1429 name => 'AR_RA_INVALID_ITEM_ID');
1430 FND_MESSAGE.set_token('ITEM_ID',p_rev_adj_rec.to_inventory_item_id);
1431 FND_MSG_PUB.Add;
1432 x_return_status := FND_API.G_RET_STS_ERROR ;
1433 END IF;
1434 CLOSE c_item_id;
1435 END IF;
1436 IF g_to_inventory_item_id IS NOT NULL
1437 THEN
1438 OPEN c_item_exists_on_trx(g_to_inventory_item_id);
1439 FETCH c_item_exists_on_trx INTO l_item_count;
1440 CLOSE c_item_exists_on_trx;
1441 IF l_item_count = 0
1442 THEN
1443 FND_MESSAGE.set_name (application => 'AR',
1444 name => 'AR_RA_ITEM_NOT_ON_TRX');
1445 FND_MESSAGE.set_token('ITEM_ID',p_rev_adj_rec.to_inventory_item_id);
1446 FND_MSG_PUB.Add;
1447 x_return_status := FND_API.G_RET_STS_ERROR ;
1448 END IF;
1449 END IF;
1450 END IF;
1451 END IF;
1452 FND_MSG_PUB.Count_And_Get
1453 (p_encoded => FND_API.G_FALSE,
1454 p_count => x_msg_count,
1455 p_data => x_msg_data);
1456 EXCEPTION
1457 WHEN TOO_MANY_ROWS THEN
1458 FND_MESSAGE.set_name (application => 'AR',
1459 name => 'AR_RA_INVALID_ITEM_SEGMENTS');
1460 FND_MESSAGE.set_token('CONCAT_SEGS', l_segment_rec.segment1||
1461 l_segment_rec.segment2||l_segment_rec.segment3||
1462 l_segment_rec.segment4||l_segment_rec.segment5||
1463 l_segment_rec.segment6||l_segment_rec.segment7||
1464 l_segment_rec.segment8||l_segment_rec.segment9||
1465 l_segment_rec.segment10||l_segment_rec.segment11||
1466 l_segment_rec.segment12||l_segment_rec.segment13||
1467 l_segment_rec.segment14||l_segment_rec.segment15||
1468 l_segment_rec.segment16||l_segment_rec.segment17||
1469 l_segment_rec.segment18||l_segment_rec.segment19||
1470 l_segment_rec.segment20);
1471 FND_MSG_PUB.Add;
1472 x_return_status := FND_API.G_RET_STS_ERROR ;
1473 WHEN OTHERS THEN
1474 IF PG_DEBUG in ('Y', 'C') THEN
1475 arp_util.debug('Validate_Item: ' || 'Unexpected error '||sqlerrm||
1476 ' at AR_RAAPI_UTIL.Validate_Item()+');
1477 END IF;
1478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1479 END Validate_Item;
1480
1481 PROCEDURE Validate_Line
1482 (p_init_msg_list IN VARCHAR2
1483 ,p_rev_adj_rec IN AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type
1484 ,x_return_status IN OUT NOCOPY VARCHAR2
1485 ,x_msg_count OUT NOCOPY NUMBER
1486 ,x_msg_data OUT NOCOPY VARCHAR2)
1487 IS
1488
1489 CURSOR c_line_num (p_line_number NUMBER) IS
1490 SELECT customer_trx_line_id
1491 FROM ra_customer_trx_lines
1492 WHERE line_number = p_line_number
1493 AND customer_trx_id = g_customer_trx_id
1494 AND line_type = 'LINE';
1495
1496 CURSOR c_line_id (p_line_id NUMBER) IS
1497 SELECT customer_trx_line_id
1501 AND line_type = 'LINE';
1498 FROM ra_customer_trx_lines
1499 WHERE customer_trx_line_id = p_line_id
1500 AND customer_trx_id = g_customer_trx_id
1502
1503 BEGIN
1504 IF PG_DEBUG in ('Y', 'C') THEN
1505 arp_util.debug('AR_RAAPI_UTIL.Validate_Line()+');
1506 END IF;
1507 -- Initialize message list if p_init_msg_list is set to TRUE.
1508 IF FND_API.to_Boolean( p_init_msg_list )
1509 THEN
1510 FND_MSG_PUB.initialize;
1511 END IF;
1512 --
1513 -- Validate from line
1514 --
1515 IF p_rev_adj_rec.from_cust_trx_line_id IS NULL
1516 THEN
1517 IF p_rev_adj_rec.from_line_number IS NOT NULL
1518 THEN
1519 OPEN c_line_num(p_rev_adj_rec.from_line_number);
1520 FETCH c_line_num INTO g_from_cust_trx_line_id;
1521 IF c_line_num%NOTFOUND
1522 THEN
1523 FND_MESSAGE.set_name (application => 'AR',
1524 name => 'AR_RA_LINE_NOT_ON_TRX');
1525 FND_MESSAGE.set_token('LINE_NUMBER', p_rev_adj_rec.from_line_number);
1526 FND_MSG_PUB.Add;
1527 x_return_status := FND_API.G_RET_STS_ERROR ;
1528 END IF;
1529 CLOSE c_line_num;
1530 ELSIF p_rev_adj_rec.line_selection_mode = 'L'
1531 THEN
1532 FND_MESSAGE.set_name (application => 'AR',
1533 name => 'AR_RA_NO_FROM_LINE');
1534 FND_MSG_PUB.Add;
1535 x_return_status := FND_API.G_RET_STS_ERROR ;
1536 END IF;
1537 ELSE
1538 OPEN c_line_id(p_rev_adj_rec.from_cust_trx_line_id);
1539 FETCH c_line_id INTO g_from_cust_trx_line_id;
1540 IF c_line_id%NOTFOUND
1541 THEN
1542 FND_MESSAGE.set_name (application => 'AR',
1543 name => 'AR_RA_INVALID_LINE_ID');
1544 FND_MESSAGE.set_token('CUST_TRX_LINE_ID',
1545 p_rev_adj_rec.from_cust_trx_line_id);
1546 FND_MSG_PUB.Add;
1547 x_return_status := FND_API.G_RET_STS_ERROR ;
1548 END IF;
1549 CLOSE c_line_id;
1550 END IF;
1551
1552 FND_MSG_PUB.Count_And_Get
1553 (p_encoded => FND_API.G_FALSE,
1554 p_count => x_msg_count,
1555 p_data => x_msg_data);
1556 EXCEPTION
1557 WHEN OTHERS THEN
1558 IF PG_DEBUG in ('Y', 'C') THEN
1559 arp_util.debug('Validate_Line: ' || 'Unexpected error '||sqlerrm||
1560 ' at AR_RAAPI_UTIL.Validate_Line()+');
1561 END IF;
1562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1563 END Validate_Line;
1564
1565 /* Bug 2146970 - changed main in parameter from p_rev_adj_rec to p_gl_date
1566 and converted from procedure to function */
1567 FUNCTION Validate_GL_Date
1568 (p_gl_date IN DATE)
1569 RETURN DATE
1570 IS
1571
1572 l_gl_date DATE;
1573 l_valid_gl_date DATE;
1574 l_default_rule VARCHAR2(80);
1575 l_err_mesg VARCHAR2(2000);
1576
1577 BEGIN
1578 IF PG_DEBUG in ('Y', 'C') THEN
1579 arp_util.debug('AR_RAAPI_UTIL.Validate_GL_Date()+');
1580 END IF;
1581 --
1582 -- Bug 2030914: need to allow NOT OPENNED periods
1583 -- changed p_allow_not_open_flag from 'N' to 'Y'
1584 --
1585 l_gl_date := NVL(p_gl_date,SYSDATE);
1586 l_valid_gl_date := NULL;
1587 IF ARP_STANDARD.validate_and_default_gl_date
1588 (gl_date => p_gl_date,
1589 trx_date => g_trx_date,
1590 validation_date1 => NULL,
1591 validation_date2 => NULL,
1592 validation_date3 => NULL,
1593 default_date1 => NULL,
1594 default_date2 => NULL,
1595 default_date3 => NULL,
1596 p_allow_not_open_flag => 'Y',
1597 p_invoicing_rule_id => g_invoicing_rule_id,
1598 p_set_of_books_id => arp_global.sysparam.set_of_books_id,
1599 p_application_id => AR_RAAPI_UTIL.application_id,
1600 default_gl_date => l_valid_gl_date,
1601 defaulting_rule_used => l_default_rule,
1602 error_message => l_err_mesg)
1603 THEN
1604 IF p_gl_date <> l_valid_gl_date
1605 THEN
1606 FND_MESSAGE.set_name('AR','AR_RA_GL_DATE_CHANGED');
1607 FND_MESSAGE.set_token('GL_DATE',p_gl_date);
1608 FND_MESSAGE.set_token('NEW_GL_DATE',l_valid_gl_date);
1609 FND_MSG_PUB.Add;
1610 END IF;
1611 END IF;
1612 RETURN l_valid_gl_date;
1613 EXCEPTION
1614 WHEN OTHERS THEN
1615 IF PG_DEBUG in ('Y', 'C') THEN
1616 arp_util.debug('Validate_GL_Date: ' || 'Unexpected error '||sqlerrm||
1617 ' at AR_RAAPI_UTIL.Validate_GL_Date()+');
1618 END IF;
1619 RETURN NULL;
1620 END Validate_GL_Date;
1621
1622 FUNCTION bump_gl_date_if_closed
1623 (p_gl_date IN DATE)
1624 RETURN DATE
1625 IS
1626
1627 BEGIN
1628 IF PG_DEBUG in ('Y', 'C') THEN
1629 arp_util.debug('AR_RAAPI_UTIL.bump_gl_date_if_closed()+');
1630 END IF;
1631
1632 /* Bug 3879222 - replaced proprietary logic with a call to
1633 arp_auto_rule.assign_gl_date. That routine caches
1634 dates and calendar to make for faster returns */
1635 RETURN arp_auto_rule.assign_gl_date(p_gl_date);
1636
1640 EXCEPTION
1637 IF PG_DEBUG in ('Y', 'C') THEN
1638 arp_util.debug('AR_RAAPI_UTIL.bump_gl_date_if_closed()-');
1639 END IF;
1641 WHEN OTHERS THEN
1642 IF PG_DEBUG in ('Y', 'C') THEN
1643 arp_util.debug('bump_gl_date_if_closed: ' || 'Unexpected error '||sqlerrm||
1644 ' at AR_RAAPI_UTIL.bump_gl_date_if_closed()+');
1645 END IF;
1646 RETURN NULL;
1647 END bump_gl_date_if_closed;
1648
1649 PROCEDURE Validate_Other
1650 (p_init_msg_list IN VARCHAR2
1651 ,p_rev_adj_rec IN AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type
1652 ,x_return_status IN OUT NOCOPY VARCHAR2
1653 ,x_msg_count OUT NOCOPY NUMBER
1654 ,x_msg_data OUT NOCOPY VARCHAR2)
1655 IS
1656 l_meaning ar_lookups.meaning%TYPE;
1657 l_attribute_rec ar_receipt_api_pub.attribute_rec_type;
1658 l_df_return_status VARCHAR2(1);
1659
1660 BEGIN
1661 IF PG_DEBUG in ('Y', 'C') THEN
1662 arp_util.debug('AR_RAAPI_UTIL.Validate_Other()+');
1663 END IF;
1664 -- Initialize message list if p_init_msg_list is set to TRUE.
1665 IF FND_API.to_Boolean( p_init_msg_list )
1666 THEN
1667 FND_MSG_PUB.initialize;
1668 END IF;
1669 IF p_rev_adj_rec.adjustment_type NOT IN ('UN','EA','SA','NR')
1670 -- 'LL' temporarily disabled
1671 THEN
1672 FND_MESSAGE.set_name (application => 'AR',
1673 name => 'AR_RA_INVALID_ADJUST_TYPE');
1674 FND_MESSAGE.set_token('ADJUST_TYPE', p_rev_adj_rec.adjustment_type);
1675 FND_MSG_PUB.Add;
1676 x_return_status := FND_API.G_RET_STS_ERROR ;
1677 END IF;
1678 IF p_rev_adj_rec.sales_credit_type NOT IN ('R','N','B')
1679 THEN
1680 FND_MESSAGE.set_name (application => 'AR',
1681 name => 'AR_RA_INVALID_SALESCRED_TYPE');
1682 FND_MESSAGE.set_token('SALESCRED_TYPE', p_rev_adj_rec.sales_credit_type);
1683 FND_MSG_PUB.Add;
1684 x_return_status := FND_API.G_RET_STS_ERROR ;
1685 END IF;
1686 IF p_rev_adj_rec.amount_mode NOT IN ('T','A','P')
1687 THEN
1688 FND_MESSAGE.set_name (application => 'AR',
1689 name => 'AR_RA_INVALID_AMOUNT_MODE');
1690 FND_MESSAGE.set_token('AMOUNT_MODE', p_rev_adj_rec.amount_mode);
1691 FND_MSG_PUB.Add;
1692 x_return_status := FND_API.G_RET_STS_ERROR ;
1693 END IF;
1694 IF p_rev_adj_rec.line_selection_mode NOT IN ('A','C','I','S')
1695 THEN
1696 FND_MESSAGE.set_name (application => 'AR',
1697 name => 'AR_RA_INVALID_LINE_MODE');
1698 FND_MESSAGE.set_token('LINE_MODE', p_rev_adj_rec.line_selection_mode);
1699 FND_MSG_PUB.Add;
1700 x_return_status := FND_API.G_RET_STS_ERROR ;
1701 END IF;
1702 IF AR_Revenue_Adjustment_PVT.g_update_db_flag = 'Y'
1703 THEN
1704 /* Bug 4304865 - separate lookup for sales credit adjustments */
1705 IF p_rev_adj_rec.adjustment_type IN ('SA','NR') THEN
1706 l_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning
1707 (p_lookup_type => 'SALESCRED_ADJ_REASON'
1708 ,p_lookup_code => p_rev_adj_rec.reason_code);
1709 ELSE
1710 l_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning
1711 (p_lookup_type => 'REV_ADJ_REASON'
1712 ,p_lookup_code => p_rev_adj_rec.reason_code);
1713 END IF;
1714 IF l_meaning IS NULL
1715 THEN
1716 /* Bug 2312077 - incorrect message replaced */
1717 FND_MESSAGE.set_name (application => 'AR',
1718 name => 'AR_RA_INVALID_REASON');
1719 FND_MESSAGE.set_token('REASON_CODE', p_rev_adj_rec.reason_code);
1720 FND_MSG_PUB.Add;
1721 x_return_status := FND_API.G_RET_STS_ERROR ;
1722 END IF;
1723 END IF;
1724
1725 --
1726 -- Validate and default the dff attributes
1727 --
1728 l_attribute_rec.attribute1 := p_rev_adj_rec.attribute1;
1729 l_attribute_rec.attribute2 := p_rev_adj_rec.attribute2;
1730 l_attribute_rec.attribute3 := p_rev_adj_rec.attribute3;
1731 l_attribute_rec.attribute4 := p_rev_adj_rec.attribute4;
1732 l_attribute_rec.attribute5 := p_rev_adj_rec.attribute5;
1733 l_attribute_rec.attribute6 := p_rev_adj_rec.attribute6;
1734 l_attribute_rec.attribute7 := p_rev_adj_rec.attribute7;
1735 l_attribute_rec.attribute8 := p_rev_adj_rec.attribute8;
1736 l_attribute_rec.attribute9 := p_rev_adj_rec.attribute9;
1737 l_attribute_rec.attribute10 := p_rev_adj_rec.attribute10;
1738 l_attribute_rec.attribute11 := p_rev_adj_rec.attribute11;
1739 l_attribute_rec.attribute12 := p_rev_adj_rec.attribute12;
1740 l_attribute_rec.attribute13 := p_rev_adj_rec.attribute13;
1741 l_attribute_rec.attribute14 := p_rev_adj_rec.attribute14;
1742 l_attribute_rec.attribute15 := p_rev_adj_rec.attribute15;
1743 ar_receipt_lib_pvt.Validate_Desc_Flexfield(
1744 l_attribute_rec,
1745 'AR_REVENUE_ADJUSTMENTS',
1746 l_df_return_status
1747 );
1748 IF NVL(l_df_return_status,FND_API.G_RET_STS_SUCCESS) <>
1749 FND_API.G_RET_STS_SUCCESS
1750 THEN
1751 x_return_status := l_df_return_status;
1752 END IF;
1756 p_data => x_msg_data);
1753 FND_MSG_PUB.Count_And_Get
1754 (p_encoded => FND_API.G_FALSE,
1755 p_count => x_msg_count,
1757 EXCEPTION
1758 WHEN OTHERS THEN
1759 IF PG_DEBUG in ('Y', 'C') THEN
1760 arp_util.debug('Validate_Other: ' || 'Unexpected error '||sqlerrm||
1761 ' at AR_RAAPI_UTIL.Validate_Other()+');
1762 END IF;
1763 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1764 END Validate_Other;
1765
1766 --
1767 -- Public function to return the cost center for a given salesrep
1768 --
1769 FUNCTION Get_Salesrep_Cost_Ctr
1770 (p_salesrep_id IN NUMBER)
1771 RETURN VARCHAR2
1772 IS
1773 l_cost_ctr VARCHAR2(30);
1774 CURSOR c_cost_ctr IS
1775 SELECT get_cost_ctr(gl_id_rev)
1776 FROM ra_salesreps
1777 WHERE salesrep_id = p_salesrep_id;
1778 BEGIN
1779 OPEN c_cost_ctr;
1780 FETCH c_cost_ctr INTO l_cost_ctr;
1781 CLOSE c_cost_ctr;
1782 RETURN l_cost_ctr;
1783 EXCEPTION
1784 WHEN OTHERS THEN
1785 RETURN NULL;
1786 END Get_Salesrep_Cost_Ctr;
1787
1788 --
1789 -- Public function to return the cost center segment value for a given ccid
1790 --
1791 FUNCTION Get_Cost_Ctr
1792 (p_code_combination_id IN NUMBER)
1793 RETURN VARCHAR2
1794 IS
1795 /* Bug 4675438: moved from constant_system_values as is dependent on
1796 MOAC initialization */
1797 CURSOR c_cost_ctr_segmt IS
1798 SELECT b.segment_num
1799 FROM fnd_segment_attribute_values a ,
1800 fnd_id_flex_segments b ,
1801 gl_sets_of_books c
1802 WHERE a.id_flex_num = c.chart_of_accounts_id
1803 AND c.set_of_books_id = arp_global.sysparam.set_of_books_id
1804 AND a.application_id = 101
1805 AND a.id_flex_code = 'GL#'
1806 AND a.attribute_value = 'Y'
1807 AND a.segment_attribute_type = 'FA_COST_CTR'
1808 AND a.application_id = b.application_id
1809 AND a.id_flex_code = b.id_flex_code
1810 AND a.id_flex_num = b.id_flex_num
1811 AND a.application_column_name = b.application_column_name
1812 AND a.id_flex_num = b.id_flex_num
1813 AND b.enabled_flag = 'Y';
1814 l_segnum NUMBER;
1815 l_number_of_segs NUMBER;
1816 l_segment_array fnd_flex_ext.segmentarray;
1817 l_segment_value VARCHAR2(30);
1818
1819 BEGIN
1820 IF NOT fnd_flex_ext.get_segments ('SQLGL'
1821 ,'GL#'
1822 ,arp_global.chart_of_accounts_id
1823 ,p_code_combination_id
1824 ,l_number_of_segs
1825 ,l_segment_array)
1826 THEN
1827 RETURN NULL;
1828 END IF;
1829 OPEN c_cost_ctr_segmt;
1830 FETCH c_cost_ctr_segmt INTO l_segnum;
1831 CLOSE c_cost_ctr_segmt;
1832
1833 l_segment_value := l_segment_array(l_segnum);
1834 RETURN l_segment_value;
1835 EXCEPTION
1836 WHEN OTHERS THEN
1837 RETURN NULL;
1838 END Get_Cost_Ctr;
1839
1840
1841 PROCEDURE Validate_Sales_Credits
1842 (p_init_msg_list IN VARCHAR2
1843 ,p_customer_trx_id IN NUMBER
1844 ,p_sales_credit_type IN VARCHAR2
1845 ,p_salesrep_id IN NUMBER
1846 ,p_salesgroup_id IN NUMBER DEFAULT NULL -- bug 3067675
1847 ,p_customer_trx_line_id IN NUMBER
1848 ,p_item_id IN NUMBER
1849 ,p_category_id IN NUMBER
1850 ,x_return_status IN OUT NOCOPY VARCHAR2
1851 ,x_msg_count OUT NOCOPY NUMBER
1852 ,x_msg_data OUT NOCOPY VARCHAR2)
1853 IS
1854 l_revenue_percent_total NUMBER;
1855 l_non_revenue_percent_total NUMBER;
1856
1857 CURSOR c_salesrep_totals IS
1858 SELECT NVL(SUM(s.revenue_percent_split),0),
1859 NVL(SUM(s.non_revenue_percent_split),0)
1860 FROM ra_cust_trx_line_salesreps s,
1861 mtl_item_categories mic,
1862 ra_customer_trx_lines l
1863 WHERE s.customer_trx_line_id = l.customer_trx_line_id
1864 AND l.customer_trx_id = p_customer_trx_id
1865 AND l.line_type = 'LINE'
1866 AND s.salesrep_id = NVL(p_salesrep_id,s.salesrep_id)
1867 /* BEGIN bug 3067675 */
1868 AND DECODE(p_sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
1869 NVL(p_salesgroup_id, DECODE(p_sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
1870 /* END bug 3067675 */
1871 AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,
1872 l.customer_trx_line_id)
1873 AND NVL(l.inventory_item_id,0) =
1874 NVL(p_item_id,NVL(l.inventory_item_id,0))
1875 AND mic.organization_id(+) = g_inv_org_id
1876 AND l.inventory_item_id = mic.inventory_item_id(+)
1877 AND NVL(p_category_id,0) =
1878 DECODE(p_category_id,NULL,0,mic.category_id)
1879 AND mic.category_set_id(+) = g_category_set_id;
1880
1881 BEGIN
1882 IF PG_DEBUG in ('Y', 'C') THEN
1883 arp_util.debug('AR_RAAPI_UTIL.Validate_Sales_Credits()+');
1887 IF g_inv_org_id IS NULL
1884 END IF;
1885 /* 5126974 - move initialization to this function
1886 to avoid org-specific failure in constant_system_values */
1888 THEN
1889 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
1890 END IF;
1891 -- Initialize message list if p_init_msg_list is set to TRUE.
1892 IF FND_API.to_Boolean( p_init_msg_list )
1893 THEN
1894 FND_MSG_PUB.initialize;
1895 END IF;
1896 x_return_status := FND_API.G_RET_STS_SUCCESS;
1897 OPEN c_salesrep_totals;
1898 FETCH c_salesrep_totals INTO l_revenue_percent_total,
1899 l_non_revenue_percent_total;
1900 CLOSE c_salesrep_totals;
1901 IF (p_sales_credit_type = 'R' AND l_revenue_percent_total = 0) OR
1902 (p_sales_credit_type = 'N' AND l_non_revenue_percent_total = 0) OR
1903 (p_sales_credit_type = 'B' AND l_revenue_percent_total = 0
1904 AND l_non_revenue_percent_total = 0)
1905 THEN
1906 FND_MESSAGE.set_name('AR','AR_RA_NO_SELECTED_SALESCRED');
1907 FND_MSG_PUB.Add;
1908 x_return_status := FND_API.G_RET_STS_ERROR ;
1909 END IF;
1910 FND_MSG_PUB.Count_And_Get
1911 (p_encoded => FND_API.G_FALSE,
1912 p_count => x_msg_count,
1913 p_data => x_msg_data);
1914 EXCEPTION
1915 WHEN OTHERS THEN
1916 IF PG_DEBUG in ('Y', 'C') THEN
1917 arp_util.debug('Validate_Sales_Credits: ' || 'Unexpected error '||sqlerrm||
1918 ' at AR_RAAPI_UTIL.Validate_Sales_Credits()+');
1919 END IF;
1920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1921 END Validate_Sales_Credits;
1922
1923 FUNCTION Total_Selected_Line_Value
1924 (p_customer_trx_line_id IN NUMBER
1925 ,p_customer_trx_id IN NUMBER
1926 ,p_item_id IN NUMBER
1927 ,p_category_id IN NUMBER
1928 ,p_salesrep_id IN NUMBER
1929 ,p_salesgroup_id IN NUMBER DEFAULT NULL -- bug 3067675
1930 ,p_sales_credit_type IN VARCHAR2)
1931 RETURN NUMBER
1932 IS
1933 l_all_line_total NUMBER;
1934
1935 CURSOR c_all_line_total IS
1936 SELECT NVL(SUM(d.amount),0) amount
1937 FROM ra_cust_trx_line_gl_dist d
1938 ,mtl_item_categories mic
1939 ,ra_customer_trx_lines l
1940 WHERE d.customer_trx_line_id = l.customer_trx_line_id
1941 AND l.line_type = 'LINE'
1942 AND l.customer_trx_id = p_customer_trx_id
1943 AND d.account_class IN ('REV','UNEARN')
1944 AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,
1945 l.customer_trx_line_id)
1946 AND NVL(l.inventory_item_id,0) =
1947 NVL(p_item_id,NVL(l.inventory_item_id,0))
1948 AND mic.organization_id(+) = g_inv_org_id
1949 AND l.inventory_item_id = mic.inventory_item_id(+)
1950 AND NVL(p_category_id,0) =
1951 DECODE(p_category_id,NULL,0,mic.category_id)
1952 AND mic.category_set_id(+) = g_category_set_id
1953 AND ((p_salesrep_id IS NULL AND p_salesgroup_id IS NULL AND
1954 p_sales_credit_type IS NULL)
1955 OR EXISTS
1956 (SELECT 'X'
1957 FROM ra_cust_trx_line_salesreps ls
1958 WHERE ls.customer_trx_line_id = l.customer_trx_line_id
1959 AND ls.salesrep_id = NVL(p_salesrep_id,ls.salesrep_id)
1960 AND DECODE(p_sales_credit_type,'N',NVL(ls.non_revenue_salesgroup_id, -9999), NVL(ls.revenue_salesgroup_id, -9999)) =
1961 NVL(p_salesgroup_id, DECODE(p_sales_credit_type,'N',NVL(ls.non_revenue_salesgroup_id, -9999), NVL(ls.revenue_salesgroup_id, -9999)))
1962 GROUP BY ls.salesrep_id
1963 HAVING SUM(NVL(DECODE(p_sales_credit_type,'N',
1964 ls.non_revenue_percent_split,ls.revenue_percent_split),0)) <> 0));
1965
1966 BEGIN
1967 IF PG_DEBUG in ('Y', 'C') THEN
1968 arp_util.debug('AR_RAAPI_UTIL.Total_Selected_Line_Value()+');
1969 END IF;
1970 /* 5126974 - move initialization to this function
1971 to avoid org-specific failure in constant_system_values */
1972 IF g_inv_org_id IS NULL
1973 THEN
1974 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
1975 END IF;
1976 OPEN c_all_line_total;
1977 FETCH c_all_line_total INTO l_all_line_total;
1978 CLOSE c_all_line_total;
1979 RETURN l_all_line_total;
1980 EXCEPTION
1981 WHEN OTHERS THEN
1982 IF PG_DEBUG in ('Y', 'C') THEN
1983 arp_util.debug('Total_Selected_Line_Value: ' || 'Unexpected error '||sqlerrm||
1984 ' at AR_RAAPI_UTIL.Total_Selected_Line_Value()+');
1985 END IF;
1986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1987 END Total_Selected_Line_Value ;
1988
1989 /* Bug 2560048 RAM-C: new out parameter p_acctd_amount_out provided for use
1990 by collectibility - it is assumed that a salesrep_id will never be passed
1991 in to this routine otherwise this amount will be wrong. To be rectified
1992 when sales credit dependency removed from RAM */
1993 FUNCTION Adjustable_Revenue
1994 (p_customer_trx_line_id IN NUMBER
1995 ,p_adjustment_type IN VARCHAR2
1996 ,p_customer_trx_id IN NUMBER
1997 ,p_salesrep_id IN NUMBER
1998 ,p_salesgroup_id IN NUMBER DEFAULT NULL -- bug 3067675
2002 ,p_revenue_adjustment_id IN NUMBER
1999 ,p_sales_credit_type IN VARCHAR2
2000 ,p_item_id IN NUMBER
2001 ,p_category_id IN NUMBER
2003 ,p_line_count_out OUT NOCOPY NUMBER
2004 ,p_acctd_amount_out OUT NOCOPY NUMBER)
2005 RETURN NUMBER
2006 IS
2007 l_line_id NUMBER;
2008 l_line_amount NUMBER;
2009 l_line_acctd_amount NUMBER;
2010 l_cm_line_amount NUMBER;
2011 l_cm_line_acctd_amount NUMBER;
2012 l_net_line_amount NUMBER;
2013 l_net_line_acctd_amount NUMBER;
2014 l_line_adjustable NUMBER;
2015 l_line_count NUMBER;
2016 l_line_salesrep_total NUMBER;
2017 l_adjustable_revenue NUMBER;
2018 l_dist_count NUMBER;
2019 l_cm_flag VARCHAR2(1);
2020
2021 /* Bug 2560048 - credit memo amounts included in adjustable revenue
2022 calculation */
2023 /* Bug 3431815 - removed unnecessary extra join to ra_customer_trx
2024 to get credit memos */
2025 /* Bug 3536944: c_line broken up into 3 separate queries to improve
2026 performance */
2027
2028 CURSOR c_line IS
2029 SELECT l.customer_trx_line_id,
2030 lr.deferred_revenue_flag
2031 FROM mtl_item_categories mic
2032 ,ra_customer_trx_lines l
2033 ,ra_rules lr
2034 WHERE l.customer_trx_id = p_customer_trx_id
2035 AND l.line_type = 'LINE'
2036 AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,l.customer_trx_line_id)
2037 AND l.autorule_complete_flag IS NULL
2038 AND NVL(l.inventory_item_id,0) =
2039 NVL(p_item_id,NVL(l.inventory_item_id,0))
2040 AND DECODE(p_adjustment_type,'LL',
2041 DECODE(p_category_id,NULL,
2042 DECODE(p_item_id,NULL,
2043 DECODE(p_customer_trx_line_id,NULL,
2044 NVL(l.accounting_rule_duration,0),0),0),0),0) <= 1
2045 AND mic.organization_id(+) = g_inv_org_id
2046 AND l.inventory_item_id = mic.inventory_item_id(+)
2047 AND NVL(p_category_id,0) =
2048 DECODE(p_category_id,NULL,0,mic.category_id)
2049 AND mic.category_set_id(+) = g_category_set_id
2050 AND l.accounting_rule_id = lr.rule_id (+)
2051 AND ((p_salesrep_id IS NULL AND p_salesgroup_id IS NULL AND
2052 p_sales_credit_type IS NULL)
2053 OR EXISTS
2054 (SELECT 'X'
2055 FROM ra_cust_trx_line_salesreps ls
2056 WHERE ls.customer_trx_line_id = l.customer_trx_line_id
2057 AND ls.salesrep_id = NVL(p_salesrep_id,ls.salesrep_id)
2058 AND DECODE(p_sales_credit_type,'N',NVL(ls.non_revenue_salesgroup_id, -9999), NVL(ls.revenue_salesgroup_id, -9999)) =
2059 NVL(p_salesgroup_id, DECODE(p_sales_credit_type,'N',NVL(ls.non_revenue_salesgroup_id, -9999), NVL(ls.revenue_salesgroup_id, -9999)))
2060 AND NVL(ls.revenue_adjustment_id,0) <>
2061 NVL(p_revenue_adjustment_id,
2062 NVL(ls.revenue_adjustment_id,0) + 1)
2063 GROUP BY ls.salesrep_id
2064 HAVING SUM(NVL(DECODE(p_sales_credit_type,'N',
2065 ls.non_revenue_percent_split,ls.revenue_percent_split),0)) <> 0));
2066 /* Bug 7130380 : Added hint to improve performance */
2067 CURSOR c_line_amount (p_cust_trx_line_id NUMBER) IS
2068 SELECT /*+ index(d ra_cust_trx_line_gl_dist_n1) push_pred(s)*/
2069 NVL(SUM(d.amount),0) amount
2070 ,NVL(SUM(d.acctd_amount),0) acctd_amount
2071 FROM ra_cust_trx_line_gl_dist d,
2072 ra_cust_trx_line_salesreps s
2073 WHERE d.customer_trx_line_id = p_cust_trx_line_id
2074 AND d.customer_trx_id = p_customer_trx_id
2075 AND d.account_class = DECODE(p_adjustment_type,'EA','UNEARN','REV')
2076 AND NVL(d.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
2077 NVL(d.revenue_adjustment_id,0) + 1)
2078 AND d.customer_trx_line_id = s.customer_trx_line_id (+)
2079 AND d.cust_trx_line_salesrep_id = s.cust_trx_line_salesrep_id (+)
2080 AND NVL(s.salesrep_id,-9999) =
2081 NVL(p_salesrep_id,
2082 NVL(s.salesrep_id,-9999))
2083 AND NVL(s.revenue_salesgroup_id, -9999) =
2084 NVL(p_salesgroup_id /*group*/,
2085 NVL(s.revenue_salesgroup_id, -9999));
2086
2087 /* Bug 7130380 : Added hint to improve performanc */
2088 CURSOR c_cm_line_amount (p_cust_trx_line_id NUMBER) IS
2089 SELECT /*+ index(d ra_cust_trx_line_gl_dist_n1) push_pred(s)*/
2090 NVL(SUM(NVL(d.amount,0)),0) amount
2091 ,NVL(SUM(NVL(d.acctd_amount,0)),0) acctd_amount
2092 FROM ra_cust_trx_line_gl_dist d
2093 ,ra_customer_trx_lines l
2094 ,ra_cust_trx_line_salesreps s
2095 WHERE l.previous_customer_trx_line_id = p_cust_trx_line_id
2096 AND d.customer_trx_id = l.customer_trx_id
2097 AND d.customer_trx_line_id = l.customer_trx_line_id
2098 AND d.account_class = DECODE(p_adjustment_type,'EA','UNEARN','REV')
2099 AND NVL(d.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
2100 NVL(d.revenue_adjustment_id,0) + 1)
2101 AND d.customer_trx_line_id = s.customer_trx_line_id (+)
2102 AND d.cust_trx_line_salesrep_id = s.cust_trx_line_salesrep_id (+)
2106 AND NVL(s.revenue_salesgroup_id, -9999) =
2103 AND NVL(s.salesrep_id,-9999) =
2104 NVL(p_salesrep_id /* sr_id */,
2105 NVL(s.salesrep_id,-9999))
2107 NVL(p_salesgroup_id /*group*/,
2108 NVL(s.revenue_salesgroup_id, -9999));
2109
2110 CURSOR c_line_nr_amount (p_cust_trx_line_id NUMBER) IS
2111 SELECT SUM(NVL(s.non_revenue_amount_split,0)) amount
2112 FROM ra_cust_trx_line_salesreps s
2113 WHERE s.customer_trx_line_id = p_cust_trx_line_id
2114 AND s.salesrep_id = NVL(p_salesrep_id,s.salesrep_id)
2115 AND NVL(s.non_revenue_salesgroup_id, -9999) =
2116 NVL(p_salesgroup_id,
2117 NVL(s.non_revenue_salesgroup_id, -9999))
2118 AND NVL(s.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
2119 NVL(s.revenue_adjustment_id,0) + 1);
2120
2121 CURSOR c_cm_line_nr_amount (p_cust_trx_line_id NUMBER) IS
2122 SELECT NVL(SUM(NVL(s.non_revenue_amount_split,0)),0) amount
2123 FROM ra_customer_trx_lines l
2124 ,ra_cust_trx_line_salesreps s
2125 WHERE l.previous_customer_trx_line_id = p_cust_trx_line_id
2126 AND l.customer_trx_line_id = s.customer_trx_line_id
2127 AND s.salesrep_id = NVL(p_salesrep_id /* sr_id */,s.salesrep_id)
2128 AND NVL(s.non_revenue_salesgroup_id, -9999) =
2129 NVL(p_salesgroup_id /*group*/,
2130 NVL(s.non_revenue_salesgroup_id, -9999));
2131
2132 CURSOR c_unrec_cm(p_target_trx NUMBER) IS
2133 SELECT cmt.customer_trx_id
2134 FROM ra_customer_trx cmt
2135 WHERE cmt.previous_customer_trx_id = p_target_trx
2136 AND EXISTS ( SELECT 'Unrecognized CM'
2137 FROM ra_customer_trx_lines cmtl
2138 WHERE cmtl.customer_trx_id = cmt.customer_trx_id
2139 AND cmtl.line_type = 'LINE'
2140 AND cmtl.autorule_complete_flag = 'N');
2141
2142 BEGIN
2143 IF PG_DEBUG in ('Y', 'C') THEN
2144 arp_util.debug('AR_RAAPI_UTIL.Adjustable_Revenue()+');
2145 arp_util.debug(' p_customer_trx_line_id = ' || p_customer_trx_line_id);
2146 arp_util.debug(' p_adjustment_type = ' || p_adjustment_type);
2147 arp_util.debug(' p_customer_trx_id = ' || p_customer_trx_id);
2148 arp_util.debug(' p_salesrep_id = ' || p_salesrep_id);
2149 arp_util.debug(' p_salesgroup_id = ' || p_salesgroup_id);
2150 arp_util.debug(' p_sales_credit_type = ' || p_sales_credit_type);
2151 arp_util.debug(' p_item_id = ' || p_item_id);
2152 arp_util.debug(' p_category_id = ' || p_category_id);
2153 arp_util.debug(' p_revenue_adjustment_id = ' ||
2154 p_revenue_adjustment_id);
2155 END IF;
2156
2157 /* 5126974 - move initialization to this function
2158 to avoid org-specific failure in constant_system_values */
2159 IF g_inv_org_id IS NULL
2160 THEN
2161 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
2162 END IF;
2163 /* 5011151 - If a user attempts to RAM or API an invoice that has
2164 credits which have not (yet) been through Rev Rec, the UNEARN
2165 will total incorrectly for the target transaction and allow
2166 more REV to be earned than it should. We are going to look
2167 for CMs that have not been through RR and process them before
2168 continuing */
2169
2170 FOR cm IN c_unrec_cm(p_customer_trx_id) LOOP
2171 l_dist_count := ARP_AUTO_RULE.create_distributions
2172 ( p_commit => 'N',
2173 p_debug => 'N',
2174 p_trx_id => cm.customer_trx_id);
2175
2176 IF PG_DEBUG in ('Y','C')
2177 THEN
2178 arp_util.debug('trx_id= ' || cm.customer_trx_id || ' dists=' ||
2179 l_dist_count);
2180 END IF;
2181 END LOOP;
2182
2183 /* 5555356/5759659 - Another corner case.. if the trx being processed
2184 is a credit, and use_inv_acctg=Y, then return zero for adjustable
2185 amounts */
2186 IF use_inv_acctg = 'Y' AND
2187 p_adjustment_type in ('EA','UN')
2188 THEN
2189 select decode(previous_customer_trx_id, NULL,'N','Y')
2190 into l_cm_flag
2191 from ra_customer_trx
2192 where customer_trx_id = p_customer_trx_id;
2193
2194 IF l_cm_flag = 'Y'
2195 THEN
2196 /* User is not allowed to adjust credits */
2197 RETURN 0;
2198 END IF;
2199 END IF;
2200
2201 l_adjustable_revenue := 0;
2202 l_line_count := 0;
2203 FOR c1 IN c_line LOOP
2204 l_line_id := c1.customer_trx_line_id;
2205
2206 /* 6223281 - Modified method for salescredit type specific
2207 queries */
2208 IF NVL(p_sales_credit_type,'X') = 'N'
2209 THEN
2210 /* These cursors select only non-revenue salescredits
2211 (which have no corresponding dist rows) for
2212 non-revenue SC transfers */
2213 OPEN c_line_nr_amount(l_line_id);
2214 FETCH c_line_nr_amount INTO l_line_amount;
2215 CLOSE c_line_nr_amount;
2216
2217 OPEN c_cm_line_nr_amount(l_line_id);
2218 FETCH c_cm_line_nr_amount INTO l_cm_line_amount;
2219 CLOSE c_cm_line_nr_amount;
2223 ELSE
2220
2221 l_line_acctd_amount := 0;
2222 l_cm_line_acctd_amount := 0;
2224 /* These cursors use the dists table to insure that
2225 we only adjust what truly exists in gl_dist */
2226
2227 -- Get amount from corresponding invoice lines
2228 OPEN c_line_amount(l_line_id);
2229 FETCH c_line_amount INTO l_line_amount, l_line_acctd_amount;
2230 CLOSE c_line_amount;
2231
2232 -- ..then for any associated credit memo lines..
2233 OPEN c_cm_line_amount(l_line_id);
2234 FETCH c_cm_line_amount INTO l_cm_line_amount, l_cm_line_acctd_amount;
2235 CLOSE c_cm_line_amount;
2236 END IF;
2237
2238 -- The two are added to give net line amount
2239 l_net_line_amount := l_line_amount + l_cm_line_amount;
2240 l_net_line_acctd_amount := l_line_acctd_amount + l_cm_line_acctd_amount;
2241
2242 IF l_net_line_amount <> 0
2243 THEN
2244 l_line_adjustable := l_net_line_amount;
2245 ELSE
2246 l_line_adjustable := 0;
2247 p_acctd_amount_out := 0;
2248 END IF;
2249
2250 IF l_line_adjustable <> 0
2251 THEN
2252 p_acctd_amount_out := l_net_line_acctd_amount;
2253 l_line_count := l_line_count + 1;
2254 l_adjustable_revenue := l_adjustable_revenue + l_line_adjustable;
2255 END IF;
2256 p_line_count_out := l_line_count;
2257
2258 END LOOP;
2259
2260 IF PG_DEBUG in ('Y', 'C') THEN
2261 arp_util.debug(' l_adjustable_revenue = ' || l_adjustable_revenue);
2262 arp_util.debug('AR_RAAPI_UTIL.Adjustable_Revenue()-');
2263 END IF;
2264
2265 RETURN l_adjustable_revenue;
2266
2267 EXCEPTION
2268 WHEN OTHERS THEN
2269 IF PG_DEBUG in ('Y', 'C') THEN
2270 arp_util.debug('Adjustable_Revenue: ' || 'Unexpected error '||sqlerrm||
2271 ' at AR_RAAPI_UTIL.Adjustable_Revenue()+');
2272 END IF;
2273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2274
2275 END Adjustable_Revenue;
2276
2277 PROCEDURE Validate_Amount
2278 (p_init_msg_list IN VARCHAR2
2279 ,p_customer_trx_line_id IN NUMBER
2280 ,p_adjustment_type IN VARCHAR2
2281 ,p_amount_mode IN VARCHAR2
2282 ,p_customer_trx_id IN NUMBER
2283 ,p_salesrep_id IN NUMBER
2284 ,p_salesgroup_id IN NUMBER DEFAULT NULL -- bug 3067675
2285 ,p_sales_credit_type IN VARCHAR2
2286 ,p_item_id IN NUMBER
2287 ,p_category_id IN NUMBER
2288 ,p_revenue_amount_in IN NUMBER
2289 ,p_revenue_percent IN NUMBER
2290 ,p_revenue_adjustment_id IN NUMBER
2291 ,p_revenue_amount_out OUT NOCOPY NUMBER
2292 ,p_adjustable_amount_out OUT NOCOPY NUMBER
2293 ,p_line_count_out OUT NOCOPY NUMBER
2294 ,x_return_status OUT NOCOPY VARCHAR2
2295 ,x_msg_count OUT NOCOPY NUMBER
2296 ,x_msg_data OUT NOCOPY VARCHAR2)
2297 IS
2298 l_adjustable_revenue NUMBER;
2299 l_revenue_total NUMBER;
2300 l_max_percent NUMBER;
2301 l_acctd_amount_out NUMBER;
2302 l_sales_credit_type VARCHAR2(15); -- bug 5644810
2303
2304 invalid_amount EXCEPTION;
2305 invalid_zero EXCEPTION;
2306 adjusted_by_other_user EXCEPTION;
2307
2308 CURSOR c_revenue_total IS
2309 SELECT NVL(SUM(d.amount),0) amount
2310 FROM ra_cust_trx_line_gl_dist d
2311 ,mtl_item_categories mic
2312 ,ra_customer_trx_lines l
2313 WHERE d.customer_trx_line_id = l.customer_trx_line_id
2314 AND d.account_class IN ('REV','UNEARN')
2315 AND NVL(d.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
2316 NVL(d.revenue_adjustment_id,0) + 1)
2317 AND l.line_type = 'LINE'
2318 AND l.customer_trx_id = p_customer_trx_id
2319 AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,
2320 l.customer_trx_line_id)
2321 AND NVL(l.inventory_item_id,0) =
2322 NVL(p_item_id,NVL(l.inventory_item_id,0))
2323 AND mic.organization_id(+) = g_inv_org_id
2324 AND l.inventory_item_id = mic.inventory_item_id(+)
2325 AND NVL(p_category_id,0) =
2326 DECODE(p_category_id,NULL,0,mic.category_id)
2327 AND mic.category_set_id(+) = g_category_set_id
2328 AND DECODE(p_category_id,NULL,
2329 DECODE(p_item_id,NULL,
2330 DECODE(p_customer_trx_line_id,NULL,
2331 DECODE(p_adjustment_type,'LL',
2332 NVL(l.accounting_rule_duration,0),0),0),0),0) <= 1
2333 AND ((p_salesrep_id IS NULL AND p_salesgroup_id IS NULL AND
2334 p_sales_credit_type IS NULL)
2335 OR EXISTS
2336 (SELECT 'X'
2337 FROM ra_cust_trx_line_salesreps ls
2338 WHERE ls.customer_trx_line_id = l.customer_trx_line_id
2339 AND ls.salesrep_id = NVL(p_salesrep_id,ls.salesrep_id)
2340 AND DECODE(p_sales_credit_type,'N',
2341 NVL(ls.non_revenue_salesgroup_id, -9999),
2342 NVL(ls.revenue_salesgroup_id, -9999)) =
2346 GROUP BY ls.salesrep_id
2343 NVL(p_salesgroup_id, DECODE(p_sales_credit_type,'N',
2344 NVL(ls.non_revenue_salesgroup_id, -9999),
2345 NVL(ls.revenue_salesgroup_id, -9999)))
2347 HAVING SUM(NVL(DECODE(p_sales_credit_type,'N',
2348 ls.non_revenue_percent_split,ls.revenue_percent_split),0)) <> 0));
2349
2350
2351 BEGIN
2352 IF PG_DEBUG in ('Y', 'C') THEN
2353 arp_util.debug('AR_RAAPI_UTIL.Validate_Amount()+');
2354 arp_util.debug(' p_customer_trx_line_id = ' || p_customer_trx_line_id);
2355 arp_util.debug(' p_amount_mode = ' || p_amount_mode);
2356 arp_util.debug(' p_salesrep_id = ' || p_salesrep_id);
2357 arp_util.debug(' p_salesgroup_id = ' || p_salesgroup_id);
2358 arp_util.debug(' p_sales_credit_type = ' || p_sales_credit_type);
2359 arp_util.debug(' p_revenue_amount_in = ' || p_revenue_amount_in);
2360 arp_util.debug(' p_revenue_percent = ' || p_revenue_percent);
2361 END IF;
2362
2363 /* 5126974 - move initialization to this function
2364 to avoid org-specific failure in constant_system_values */
2365 IF g_inv_org_id IS NULL
2366 THEN
2367 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
2368 END IF;
2369
2370 -- Initialize message list if p_init_msg_list is set to TRUE.
2371 IF FND_API.to_Boolean( p_init_msg_list )
2372 THEN
2373 FND_MSG_PUB.initialize;
2374 END IF;
2375 x_return_status := FND_API.G_RET_STS_SUCCESS;
2376 IF NVL(g_last_customer_trx_id,p_customer_trx_id - 1) <> p_customer_trx_id
2377 THEN
2378 constant_trx_values(p_customer_trx_id);
2379 END IF;
2380 /* Added IF condition for bug 5644810 */
2381 IF ((p_salesrep_id IS NULL) and (p_salesgroup_id IS NULL )) THEN
2382 l_sales_credit_type := NULL;
2383 ELSE
2384 l_sales_credit_type := p_sales_credit_type;
2385 END IF;
2386
2387 l_adjustable_revenue := Adjustable_Revenue
2388 (p_customer_trx_line_id => p_customer_trx_line_id
2389 ,p_adjustment_type => p_adjustment_type
2390 ,p_customer_trx_id => p_customer_trx_id
2391 ,p_salesrep_id => p_salesrep_id
2392 ,p_salesgroup_id => p_salesgroup_id -- bug 3067675
2393 ,p_sales_credit_type => l_sales_credit_type -- bug 5644810
2394 ,p_item_id => p_item_id
2395 ,p_category_id => p_category_id
2396 ,p_revenue_adjustment_id => p_revenue_adjustment_id
2397 ,p_line_count_out => p_line_count_out
2398 ,p_acctd_amount_out => l_acctd_amount_out);
2399
2400 p_adjustable_amount_out := l_adjustable_revenue;
2401 IF p_amount_mode = 'A'
2402 THEN
2403 p_revenue_amount_out := NVL(p_revenue_amount_in,0);
2404 ELSIF p_amount_mode = 'P'
2405 THEN
2406 OPEN c_revenue_total;
2407 FETCH c_revenue_total INTO l_revenue_total;
2408 close c_revenue_total;
2409 p_revenue_amount_out := ROUND(l_revenue_total * p_revenue_percent / 100,
2410 g_trx_precision);
2411 ELSE
2412 p_revenue_amount_out := l_adjustable_revenue;
2413 END IF;
2414
2415 IF PG_DEBUG = 'Y'
2416 THEN
2417 arp_util.debug(' --- after internal validation/calcs ---');
2418 arp_util.debug(' l_adjustable_revenue = ' || l_adjustable_revenue);
2419 arp_util.debug(' l_revenue_total = ' || l_revenue_total);
2420 arp_util.debug(' p_revenue_amount_out = ' || p_revenue_amount_out);
2421 END IF;
2422
2423 IF p_revenue_amount_out = 0
2424 THEN
2425 IF p_revenue_adjustment_id IS NULL
2426 THEN
2427 RAISE invalid_zero;
2428 ELSE
2429 RAISE adjusted_by_other_user;
2430 END IF;
2431 END IF;
2432
2433 IF p_revenue_amount_out > 0
2434 THEN
2435 IF p_revenue_amount_out > l_adjustable_revenue
2436 THEN
2437 IF p_revenue_adjustment_id IS NULL
2438 THEN
2439 RAISE invalid_amount;
2440 ELSE
2441 RAISE adjusted_by_other_user;
2442 END IF;
2443 END IF;
2444 ELSIF p_revenue_amount_out < 0
2445 THEN
2446 IF p_revenue_amount_out < l_adjustable_revenue
2447 THEN
2448 IF p_revenue_adjustment_id IS NULL
2449 THEN
2450 RAISE invalid_amount;
2451 ELSE
2452 RAISE adjusted_by_other_user;
2453 END IF;
2454 END IF;
2455 END IF;
2456
2457 EXCEPTION
2458
2459 WHEN invalid_amount THEN
2460 IF p_amount_mode = 'P'
2461 THEN
2462 l_max_percent := ROUND(l_adjustable_revenue / l_revenue_total * 100,4);
2463 FND_MESSAGE.set_name
2464 (application => 'AR', name => 'AR_RA_PCT_EXCEEDS_AVAIL_PCT');
2465 FND_MESSAGE.set_token('TOT_AVAIL_PCT',l_max_percent);
2466 ELSE
2467 FND_MESSAGE.set_name
2468 (application => 'AR', name => 'AR_RA_AMT_EXCEEDS_AVAIL_REV');
2469 FND_MESSAGE.set_token('TOT_AVAIL_REV',
2470 g_trx_currency||' '||
2471 TO_CHAR(l_adjustable_revenue,g_trx_curr_format));
2472 END IF;
2473 FND_MSG_PUB.Add;
2477 p_count => x_msg_count,
2474 x_return_status := FND_API.G_RET_STS_ERROR ;
2475 FND_MSG_PUB.Count_And_Get
2476 (p_encoded => FND_API.G_FALSE,
2478 p_data => x_msg_data);
2479 WHEN invalid_zero THEN
2480 FND_MESSAGE.set_name
2481 (application => 'AR', name => 'AR_RA_ZERO_AMOUNT');
2482 FND_MSG_PUB.Add;
2483 x_return_status := FND_API.G_RET_STS_ERROR ;
2484 FND_MSG_PUB.Count_And_Get
2485 (p_encoded => FND_API.G_FALSE,
2486 p_count => x_msg_count,
2487 p_data => x_msg_data);
2488 WHEN adjusted_by_other_user THEN
2489 FND_MESSAGE.set_name
2490 (application => 'AR', name => 'AR_RA_ADJUSTED_BY_OTHER_USER');
2491 FND_MSG_PUB.Add;
2492 x_return_status := FND_API.G_RET_STS_ERROR ;
2493 FND_MSG_PUB.Count_And_Get
2494 (p_encoded => FND_API.G_FALSE,
2495 p_count => x_msg_count,
2496 p_data => x_msg_data);
2497 WHEN OTHERS THEN
2498 IF PG_DEBUG in ('Y', 'C') THEN
2499 arp_util.debug('Validate_Amount: ' || 'Unexpected error '||sqlerrm||
2500 ' at AR_RAAPI_UTIL.Validate_Amount()+');
2501 END IF;
2502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2503
2504 END Validate_Amount;
2505
2506 FUNCTION Revalidate_GL_Dates
2507 (p_customer_trx_id IN NUMBER
2508 ,p_revenue_adjustment_id IN NUMBER
2509 ,x_msg_count OUT NOCOPY NUMBER
2510 ,x_msg_data OUT NOCOPY VARCHAR2)
2511 RETURN VARCHAR2 IS
2512
2513 l_change_count NUMBER;
2514 l_gl_date DATE;
2515 l_default_rule VARCHAR2(80);
2516 l_err_mesg VARCHAR2(2000);
2517
2518 CURSOR c_gl_date IS
2519 SELECT DISTINCT gl_date
2520 FROM ra_cust_trx_line_gl_dist
2521 WHERE revenue_adjustment_id = p_revenue_adjustment_id;
2522
2523 BEGIN
2524 IF PG_DEBUG in ('Y', 'C') THEN
2525 arp_util.debug('AR_RAAPI_UTIL.Revalidate_GL_Dates()+');
2526 END IF;
2527 l_change_count := 0;
2528 FOR c1 IN c_gl_date LOOP
2529 IF ARP_STANDARD.validate_and_default_gl_date
2530 (gl_date => c1.gl_date,
2531 trx_date => g_trx_date,
2532 validation_date1 => NULL,
2533 validation_date2 => NULL,
2534 validation_date3 => NULL,
2535 default_date1 => c1.gl_date,
2536 default_date2 => NULL,
2537 default_date3 => NULL,
2538 p_allow_not_open_flag => 'Y',
2539 p_invoicing_rule_id => g_invoicing_rule_id,
2540 p_set_of_books_id => arp_global.sysparam.set_of_books_id,
2541 p_application_id => AR_RAAPI_UTIL.application_id,
2542 default_gl_date => l_gl_date,
2543 defaulting_rule_used => l_default_rule,
2544 error_message => l_err_mesg)
2545 THEN
2546 IF c1.gl_date <> l_gl_date
2547 THEN
2548 UPDATE ra_cust_trx_line_gl_dist
2549 SET gl_date = l_gl_date
2550 WHERE revenue_adjustment_id = p_revenue_adjustment_id
2551 AND gl_date = c1.gl_date;
2552 FND_MESSAGE.set_name('AR','AR_RA_GL_DATE_CHANGED');
2553 FND_MESSAGE.set_token('GL_DATE',c1.gl_date);
2554 FND_MESSAGE.set_token('NEW_GL_DATE',l_gl_date);
2555 FND_MSG_PUB.Add;
2556 l_change_count := l_change_count + 1;
2557 END IF;
2558 ELSE
2559 FND_MESSAGE.set_name('AR','AR_RA_NO_OPEN_PERIODS');
2560 RETURN FND_API.G_FALSE;
2561 END IF;
2562 END LOOP;
2563 IF l_change_count > 0
2564 THEN
2565 FND_MSG_PUB.Count_And_Get
2566 (p_encoded => FND_API.G_FALSE,
2567 p_count => x_msg_count,
2568 p_data => x_msg_data);
2569 RETURN FND_API.G_FALSE;
2570 ELSE
2571 RETURN FND_API.G_TRUE;
2572 END IF;
2573 EXCEPTION
2574 WHEN OTHERS THEN
2575 IF PG_DEBUG in ('Y', 'C') THEN
2576 arp_util.debug('Revalidate_GL_Dates: ' || 'Unexpected error '||sqlerrm||
2577 ' at AR_RAAPI_UTIL.Revalidate_GL_Dates()+');
2578 END IF;
2579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2580 END Revalidate_GL_Dates;
2581
2582 FUNCTION Deferred_GL_Date (p_start_date IN DATE,
2583 p_period_seq_no IN NUMBER)
2584 RETURN DATE
2585 IS
2586 l_init_start_date DATE;
2587 l_init_new_period_num NUMBER;
2588 l_current_new_period_num NUMBER;
2589 l_current_start_date DATE;
2590 l_current_end_date DATE;
2591 l_current_gl_date DATE;
2592
2593 /* Bug 1940911: added period_type to 'where' clause to ensure the correct
2594 period type is being selected when more than 1 type exists
2595 in a calendar. */
2596
2597 CURSOR c_start_period IS
2598 SELECT p.start_date, p.new_period_num
2599 FROM ar_periods p,
2600 gl_sets_of_books sob,
2601 ar_period_types tp
2605 AND sob.accounted_period_type = tp.period_type
2602 WHERE sob.period_set_name = p.period_set_name
2603 AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
2604 AND sob.accounted_period_type = p.period_type
2606 AND p_start_date BETWEEN p.start_date AND p.end_date;
2607
2608 CURSOR c_current_period (p_new_period_num NUMBER) IS
2609 SELECT p.start_date, p.end_date
2610 FROM ar_periods p,
2611 gl_sets_of_books sob,
2612 ar_period_types tp
2613 WHERE sob.period_set_name = p.period_set_name
2614 AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
2615 AND sob.accounted_period_type = p.period_type
2616 AND sob.accounted_period_type = tp.period_type
2617 AND p.new_period_num = p_new_period_num;
2618
2619 BEGIN
2620 IF PG_DEBUG in ('Y', 'C') THEN
2621 arp_util.debug('AR_RAAPI_UTIL.Deferred_GL_Date()+');
2622 END IF;
2623 IF p_period_seq_no = 1
2624 THEN
2625 IF PG_DEBUG in ('Y', 'C') THEN
2626 arp_util.debug('AR_RAAPI_UTIL.Deferred_GL_Date()-');
2627 END IF;
2628 RETURN p_start_date;
2629 ELSE
2630 -- Find the period relating to the start date of revenue recognition
2631 OPEN c_start_period;
2632 FETCH c_start_period INTO l_init_start_date, l_init_new_period_num;
2633 CLOSE c_start_period;
2634 -- Find the period number of the current period
2635 l_current_new_period_num := (l_init_new_period_num + p_period_seq_no -1);
2636 OPEN c_current_period(l_current_new_period_num);
2637 FETCH c_current_period INTO l_current_start_date, l_current_end_date;
2638 CLOSE c_current_period;
2639 -- Calculate the current gl_date
2640 l_current_gl_date := LEAST((p_start_date - l_init_start_date
2641 + l_current_start_date),
2642 l_current_end_date);
2643 IF PG_DEBUG in ('Y', 'C') THEN
2644 arp_util.debug('AR_RAAPI_UTIL.Deferred_GL_Date()-');
2645 END IF;
2646 RETURN l_current_gl_date;
2647 END IF;
2648 EXCEPTION
2649 WHEN OTHERS THEN
2650 IF PG_DEBUG in ('Y', 'C') THEN
2651 arp_util.debug('Deferred_GL_Date: ' || 'Unexpected error '||sqlerrm||
2652 ' at AR_RAAPI_UTIL.Deferred_GL_Date()+');
2653 END IF;
2654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2655 END Deferred_GL_Date;
2656
2657 --
2658 -- Read only functions to allow client access to globals
2659 --
2660 FUNCTION G_RET_STS_SUCCESS
2661 RETURN VARCHAR2 IS
2662 BEGIN
2663 RETURN FND_API.G_RET_STS_SUCCESS;
2664 END G_RET_STS_SUCCESS;
2665
2666 FUNCTION G_RET_STS_ERROR
2667 RETURN VARCHAR2 IS
2668 BEGIN
2669 RETURN FND_API.G_RET_STS_ERROR;
2670 END G_RET_STS_ERROR;
2671
2672 FUNCTION G_TRUE
2673 RETURN VARCHAR2 IS
2674 BEGIN
2675 RETURN FND_API.G_TRUE;
2676 END G_TRUE;
2677
2678 FUNCTION G_VALID_LEVEL_NONE
2679 RETURN VARCHAR2 IS
2680 BEGIN
2681 RETURN FND_API.G_VALID_LEVEL_NONE;
2682 END G_VALID_LEVEL_NONE;
2683
2684 FUNCTION G_VALID_LEVEL_FULL
2685 RETURN VARCHAR2 IS
2686 BEGIN
2687 RETURN FND_API.G_VALID_LEVEL_FULL;
2688 END G_VALID_LEVEL_FULL;
2689
2690 FUNCTION G_FALSE
2691 RETURN VARCHAR2 IS
2692 BEGIN
2693 RETURN FND_API.G_FALSE;
2694 END G_FALSE;
2695
2696 FUNCTION chart_of_accounts_id
2697 RETURN NUMBER IS
2698 BEGIN
2699 RETURN g_chart_of_accounts_id;
2700 END chart_of_accounts_id;
2701
2702 FUNCTION set_of_books_id
2703 RETURN NUMBER IS
2704 BEGIN
2705 RETURN g_set_of_books_id;
2706 END set_of_books_id;
2707
2708 FUNCTION application_id
2709 RETURN NUMBER IS
2710 BEGIN
2711 RETURN g_ar_app_id;
2712 END application_id;
2713
2714 FUNCTION un_meaning
2715 RETURN VARCHAR2 IS
2716 BEGIN
2717 RETURN g_un_meaning;
2718 END un_meaning;
2719
2720 FUNCTION ea_meaning
2721 RETURN VARCHAR2 IS
2722 BEGIN
2723 RETURN g_ea_meaning;
2724 END ea_meaning;
2725
2726 FUNCTION sa_meaning
2727 RETURN VARCHAR2 IS
2728 BEGIN
2729 RETURN g_sa_meaning;
2730 END sa_meaning;
2731
2732 FUNCTION nr_meaning
2733 RETURN VARCHAR2 IS
2734 BEGIN
2735 RETURN g_nr_meaning;
2736 END nr_meaning;
2737
2738 FUNCTION ll_meaning
2739 RETURN VARCHAR2 IS
2740 BEGIN
2741 RETURN g_ll_meaning;
2742 END ll_meaning;
2743
2744 FUNCTION cost_ctr_number
2745 RETURN VARCHAR2 IS
2746 BEGIN
2747 RETURN g_cost_ctr_number;
2748 END cost_ctr_number;
2749
2750 FUNCTION category_set_id
2751 RETURN VARCHAR2 IS
2752 BEGIN
2753 RETURN g_category_set_id;
2754 END category_set_id;
2755
2756 FUNCTION category_structure_id
2757 RETURN VARCHAR2 IS
2758 BEGIN
2759 RETURN g_category_structure_id;
2760 END category_structure_id;
2761
2762 FUNCTION inv_org_id
2763 RETURN VARCHAR2 IS
2764 BEGIN
2765 /* NOTE: This is returned as a varchar.. not sure why */
2766 /* 5861728 - Initialize the value if null */
2767 IF g_inv_org_id IS NULL
2768 THEN
2769 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
2770 END IF;
2771 RETURN g_inv_org_id;
2772 END inv_org_id;
2773
2774 END AR_RAAPI_UTIL;
[Home] [Help]