1 PACKAGE BODY AR_RAAPI_UTIL AS
2 /*$Header: ARXRAAUB.pls 120.43 2011/07/19 02:39:06 dgaurab 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
363 THEN
364 FND_MESSAGE.set_name (application => 'AR',
365 name => 'AR_RA_TRX_NOTFOUND');
366 FND_MESSAGE.set_token('TRX_NUMBER',p_rev_adj_rec.trx_number);
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
498 WHEN OTHERS THEN
499 IF PG_DEBUG in ('Y', 'C') THEN
500 arp_util.debug('Validate_Transaction: ' || 'Unexpected error '||sqlerrm||
501 ' at AR_RAAPI_UTIL.Validate_Transaction()+');
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
622 THEN
623 --
624 -- Don't revalidate if validated previously in this session
625 --
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'));
755 FND_MSG_PUB.Add;
756 x_return_status := FND_API.G_RET_STS_ERROR ;
757 ELSE
758 OPEN c_salesrep_num (p_rev_adj_rec.to_salesrep_number);
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)
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)
870 AND NVL(segment18,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment18,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',
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||
972 l_segment_rec.segment4||l_segment_rec.segment5||
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;
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;
1075 l_segment_rec.segment19 := p_rev_adj_rec.to_category_segment19;
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)
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)
1183 AND NVL(segment7,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment7,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;
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;
1281 l_segment_rec.segment20 := p_rev_adj_rec.from_item_segment20;
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;
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;
1389 l_segment_rec.segment14 := p_rev_adj_rec.to_item_segment14;
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
1498 FROM ra_customer_trx_lines
1499 WHERE customer_trx_line_id = p_line_id
1500 AND customer_trx_id = g_customer_trx_id
1501 AND line_type = 'LINE';
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 --Int'l Calendar Project
1608 FND_MESSAGE.set_token('GL_DATE',fnd_date.date_to_chardate(p_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
1609 FND_MESSAGE.set_token('NEW_GL_DATE',fnd_date.date_to_chardate(l_valid_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
1610 FND_MSG_PUB.Add;
1611 END IF;
1612 END IF;
1613 RETURN l_valid_gl_date;
1614 EXCEPTION
1615 WHEN OTHERS THEN
1616 IF PG_DEBUG in ('Y', 'C') THEN
1617 arp_util.debug('Validate_GL_Date: ' || 'Unexpected error '||sqlerrm||
1618 ' at AR_RAAPI_UTIL.Validate_GL_Date()+');
1619 END IF;
1620 RETURN NULL;
1621 END Validate_GL_Date;
1622
1623 FUNCTION bump_gl_date_if_closed
1624 (p_gl_date IN DATE)
1625 RETURN DATE
1626 IS
1627
1628 BEGIN
1629 IF PG_DEBUG in ('Y', 'C') THEN
1630 arp_util.debug('AR_RAAPI_UTIL.bump_gl_date_if_closed()+');
1631 END IF;
1632
1633 /* Bug 3879222 - replaced proprietary logic with a call to
1634 arp_auto_rule.assign_gl_date. That routine caches
1635 dates and calendar to make for faster returns */
1636 RETURN arp_auto_rule.assign_gl_date(p_gl_date);
1637
1638 IF PG_DEBUG in ('Y', 'C') THEN
1639 arp_util.debug('AR_RAAPI_UTIL.bump_gl_date_if_closed()-');
1640 END IF;
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643 IF PG_DEBUG in ('Y', 'C') THEN
1644 arp_util.debug('bump_gl_date_if_closed: ' || 'Unexpected error '||sqlerrm||
1645 ' at AR_RAAPI_UTIL.bump_gl_date_if_closed()+');
1646 END IF;
1647 RETURN NULL;
1648 END bump_gl_date_if_closed;
1649
1650 PROCEDURE Validate_Other
1651 (p_init_msg_list IN VARCHAR2
1652 ,p_rev_adj_rec IN AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type
1653 ,x_return_status IN OUT NOCOPY VARCHAR2
1654 ,x_msg_count OUT NOCOPY NUMBER
1655 ,x_msg_data OUT NOCOPY VARCHAR2)
1656 IS
1657 l_meaning ar_lookups.meaning%TYPE;
1658 l_attribute_rec ar_receipt_api_pub.attribute_rec_type;
1659 l_df_return_status VARCHAR2(1);
1660
1661 BEGIN
1662 IF PG_DEBUG in ('Y', 'C') THEN
1663 arp_util.debug('AR_RAAPI_UTIL.Validate_Other()+');
1664 END IF;
1665 -- Initialize message list if p_init_msg_list is set to TRUE.
1666 IF FND_API.to_Boolean( p_init_msg_list )
1667 THEN
1668 FND_MSG_PUB.initialize;
1669 END IF;
1670 IF p_rev_adj_rec.adjustment_type NOT IN ('UN','EA','SA','NR')
1671 -- 'LL' temporarily disabled
1672 THEN
1673 FND_MESSAGE.set_name (application => 'AR',
1674 name => 'AR_RA_INVALID_ADJUST_TYPE');
1675 FND_MESSAGE.set_token('ADJUST_TYPE', p_rev_adj_rec.adjustment_type);
1676 FND_MSG_PUB.Add;
1677 x_return_status := FND_API.G_RET_STS_ERROR ;
1678 END IF;
1679 IF p_rev_adj_rec.sales_credit_type NOT IN ('R','N','B')
1680 THEN
1681 FND_MESSAGE.set_name (application => 'AR',
1682 name => 'AR_RA_INVALID_SALESCRED_TYPE');
1683 FND_MESSAGE.set_token('SALESCRED_TYPE', p_rev_adj_rec.sales_credit_type);
1684 FND_MSG_PUB.Add;
1685 x_return_status := FND_API.G_RET_STS_ERROR ;
1686 END IF;
1687 IF p_rev_adj_rec.amount_mode NOT IN ('T','A','P')
1688 THEN
1689 FND_MESSAGE.set_name (application => 'AR',
1690 name => 'AR_RA_INVALID_AMOUNT_MODE');
1691 FND_MESSAGE.set_token('AMOUNT_MODE', p_rev_adj_rec.amount_mode);
1692 FND_MSG_PUB.Add;
1693 x_return_status := FND_API.G_RET_STS_ERROR ;
1694 END IF;
1695 IF p_rev_adj_rec.line_selection_mode NOT IN ('A','C','I','S')
1696 THEN
1697 FND_MESSAGE.set_name (application => 'AR',
1698 name => 'AR_RA_INVALID_LINE_MODE');
1699 FND_MESSAGE.set_token('LINE_MODE', p_rev_adj_rec.line_selection_mode);
1700 FND_MSG_PUB.Add;
1701 x_return_status := FND_API.G_RET_STS_ERROR ;
1702 END IF;
1703 IF AR_Revenue_Adjustment_PVT.g_update_db_flag = 'Y'
1704 THEN
1705 /* Bug 4304865 - separate lookup for sales credit adjustments */
1706 IF p_rev_adj_rec.adjustment_type IN ('SA','NR') THEN
1707 l_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning
1708 (p_lookup_type => 'SALESCRED_ADJ_REASON'
1709 ,p_lookup_code => p_rev_adj_rec.reason_code);
1710 ELSE
1711 l_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning
1712 (p_lookup_type => 'REV_ADJ_REASON'
1713 ,p_lookup_code => p_rev_adj_rec.reason_code);
1714 END IF;
1715 IF l_meaning IS NULL
1716 THEN
1717 /* Bug 2312077 - incorrect message replaced */
1718 FND_MESSAGE.set_name (application => 'AR',
1719 name => 'AR_RA_INVALID_REASON');
1720 FND_MESSAGE.set_token('REASON_CODE', p_rev_adj_rec.reason_code);
1721 FND_MSG_PUB.Add;
1722 x_return_status := FND_API.G_RET_STS_ERROR ;
1723 END IF;
1724 END IF;
1725
1726 --
1727 -- Validate and default the dff attributes
1728 --
1729 l_attribute_rec.attribute1 := p_rev_adj_rec.attribute1;
1730 l_attribute_rec.attribute2 := p_rev_adj_rec.attribute2;
1731 l_attribute_rec.attribute3 := p_rev_adj_rec.attribute3;
1732 l_attribute_rec.attribute4 := p_rev_adj_rec.attribute4;
1733 l_attribute_rec.attribute5 := p_rev_adj_rec.attribute5;
1734 l_attribute_rec.attribute6 := p_rev_adj_rec.attribute6;
1735 l_attribute_rec.attribute7 := p_rev_adj_rec.attribute7;
1736 l_attribute_rec.attribute8 := p_rev_adj_rec.attribute8;
1737 l_attribute_rec.attribute9 := p_rev_adj_rec.attribute9;
1738 l_attribute_rec.attribute10 := p_rev_adj_rec.attribute10;
1739 l_attribute_rec.attribute11 := p_rev_adj_rec.attribute11;
1740 l_attribute_rec.attribute12 := p_rev_adj_rec.attribute12;
1741 l_attribute_rec.attribute13 := p_rev_adj_rec.attribute13;
1742 l_attribute_rec.attribute14 := p_rev_adj_rec.attribute14;
1743 l_attribute_rec.attribute15 := p_rev_adj_rec.attribute15;
1744 ar_receipt_lib_pvt.Validate_Desc_Flexfield(
1745 l_attribute_rec,
1746 'AR_REVENUE_ADJUSTMENTS',
1747 l_df_return_status
1748 );
1749 IF NVL(l_df_return_status,FND_API.G_RET_STS_SUCCESS) <>
1750 FND_API.G_RET_STS_SUCCESS
1751 THEN
1752 x_return_status := l_df_return_status;
1753 END IF;
1754 FND_MSG_PUB.Count_And_Get
1755 (p_encoded => FND_API.G_FALSE,
1756 p_count => x_msg_count,
1757 p_data => x_msg_data);
1758 EXCEPTION
1759 WHEN OTHERS THEN
1760 IF PG_DEBUG in ('Y', 'C') THEN
1761 arp_util.debug('Validate_Other: ' || 'Unexpected error '||sqlerrm||
1762 ' at AR_RAAPI_UTIL.Validate_Other()+');
1763 END IF;
1764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1765 END Validate_Other;
1766
1767 --
1768 -- Public function to return the cost center for a given salesrep
1769 --
1770 FUNCTION Get_Salesrep_Cost_Ctr
1771 (p_salesrep_id IN NUMBER)
1772 RETURN VARCHAR2
1773 IS
1774 l_cost_ctr VARCHAR2(30);
1775 CURSOR c_cost_ctr IS
1776 SELECT get_cost_ctr(gl_id_rev)
1777 FROM ra_salesreps
1778 WHERE salesrep_id = p_salesrep_id;
1779 BEGIN
1780 OPEN c_cost_ctr;
1781 FETCH c_cost_ctr INTO l_cost_ctr;
1782 CLOSE c_cost_ctr;
1783 RETURN l_cost_ctr;
1784 EXCEPTION
1785 WHEN OTHERS THEN
1786 RETURN NULL;
1787 END Get_Salesrep_Cost_Ctr;
1788
1789 --
1790 -- Public function to return the cost center segment value for a given ccid
1791 --
1792 FUNCTION Get_Cost_Ctr
1793 (p_code_combination_id IN NUMBER)
1794 RETURN VARCHAR2
1795 IS
1796 /* Bug 4675438: moved from constant_system_values as is dependent on
1797 MOAC initialization */
1798 CURSOR c_cost_ctr_segmt IS
1799 SELECT b.segment_num
1800 FROM fnd_segment_attribute_values a ,
1801 fnd_id_flex_segments b ,
1802 gl_sets_of_books c
1803 WHERE a.id_flex_num = c.chart_of_accounts_id
1804 AND c.set_of_books_id = arp_global.sysparam.set_of_books_id
1805 AND a.application_id = 101
1806 AND a.id_flex_code = 'GL#'
1807 AND a.attribute_value = 'Y'
1808 AND a.segment_attribute_type = 'FA_COST_CTR'
1809 AND a.application_id = b.application_id
1810 AND a.id_flex_code = b.id_flex_code
1811 AND a.id_flex_num = b.id_flex_num
1812 AND a.application_column_name = b.application_column_name
1813 AND a.id_flex_num = b.id_flex_num
1814 AND b.enabled_flag = 'Y';
1815 l_segnum NUMBER;
1816 l_number_of_segs NUMBER;
1817 l_segment_array fnd_flex_ext.segmentarray;
1818 l_segment_value VARCHAR2(30);
1819
1820 BEGIN
1821 IF NOT fnd_flex_ext.get_segments ('SQLGL'
1822 ,'GL#'
1823 ,arp_global.chart_of_accounts_id
1824 ,p_code_combination_id
1825 ,l_number_of_segs
1826 ,l_segment_array)
1827 THEN
1828 RETURN NULL;
1829 END IF;
1830 OPEN c_cost_ctr_segmt;
1831 FETCH c_cost_ctr_segmt INTO l_segnum;
1832 CLOSE c_cost_ctr_segmt;
1833
1834 l_segment_value := l_segment_array(l_segnum);
1835 RETURN l_segment_value;
1836 EXCEPTION
1837 WHEN OTHERS THEN
1838 RETURN NULL;
1839 END Get_Cost_Ctr;
1840
1841
1842 PROCEDURE Validate_Sales_Credits
1843 (p_init_msg_list IN VARCHAR2
1844 ,p_customer_trx_id IN NUMBER
1845 ,p_sales_credit_type IN VARCHAR2
1846 ,p_salesrep_id IN NUMBER
1847 ,p_salesgroup_id IN NUMBER DEFAULT NULL -- bug 3067675
1848 ,p_customer_trx_line_id IN NUMBER
1849 ,p_item_id IN NUMBER
1850 ,p_category_id IN NUMBER
1851 ,x_return_status IN OUT NOCOPY VARCHAR2
1852 ,x_msg_count OUT NOCOPY NUMBER
1853 ,x_msg_data OUT NOCOPY VARCHAR2)
1854 IS
1855 l_revenue_percent_total NUMBER;
1856 l_non_revenue_percent_total NUMBER;
1857
1858 CURSOR c_salesrep_totals IS
1859 SELECT NVL(SUM(s.revenue_percent_split),0),
1860 NVL(SUM(s.non_revenue_percent_split),0)
1861 FROM ra_cust_trx_line_salesreps s,
1862 mtl_item_categories mic,
1863 ra_customer_trx_lines l
1864 WHERE s.customer_trx_line_id = l.customer_trx_line_id
1865 AND l.customer_trx_id = p_customer_trx_id
1866 AND l.line_type = 'LINE'
1867 AND s.salesrep_id = NVL(p_salesrep_id,s.salesrep_id)
1868 /* BEGIN bug 3067675 */
1869 AND DECODE(p_sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
1870 NVL(p_salesgroup_id, DECODE(p_sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
1871 /* END bug 3067675 */
1872 AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,
1873 l.customer_trx_line_id)
1874 AND NVL(l.inventory_item_id,0) =
1875 NVL(p_item_id,NVL(l.inventory_item_id,0))
1876 AND mic.organization_id(+) = g_inv_org_id
1877 AND l.inventory_item_id = mic.inventory_item_id(+)
1878 AND NVL(p_category_id,0) =
1879 DECODE(p_category_id,NULL,0,mic.category_id)
1880 AND mic.category_set_id(+) = g_category_set_id;
1881
1882 BEGIN
1883 IF PG_DEBUG in ('Y', 'C') THEN
1884 arp_util.debug('AR_RAAPI_UTIL.Validate_Sales_Credits()+');
1885 END IF;
1886 /* 5126974 - move initialization to this function
1887 to avoid org-specific failure in constant_system_values */
1888 IF g_inv_org_id IS NULL
1889 THEN
1890 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
1891 END IF;
1892 -- Initialize message list if p_init_msg_list is set to TRUE.
1893 IF FND_API.to_Boolean( p_init_msg_list )
1894 THEN
1895 FND_MSG_PUB.initialize;
1896 END IF;
1897 x_return_status := FND_API.G_RET_STS_SUCCESS;
1898 OPEN c_salesrep_totals;
1899 FETCH c_salesrep_totals INTO l_revenue_percent_total,
1900 l_non_revenue_percent_total;
1901 CLOSE c_salesrep_totals;
1902 IF (p_sales_credit_type = 'R' AND l_revenue_percent_total = 0) OR
1903 (p_sales_credit_type = 'N' AND l_non_revenue_percent_total = 0) OR
1904 (p_sales_credit_type = 'B' AND l_revenue_percent_total = 0
1905 AND l_non_revenue_percent_total = 0)
1906 THEN
1907 FND_MESSAGE.set_name('AR','AR_RA_NO_SELECTED_SALESCRED');
1908 FND_MSG_PUB.Add;
1909 x_return_status := FND_API.G_RET_STS_ERROR ;
1910 END IF;
1911 FND_MSG_PUB.Count_And_Get
1912 (p_encoded => FND_API.G_FALSE,
1913 p_count => x_msg_count,
1914 p_data => x_msg_data);
1915 EXCEPTION
1916 WHEN OTHERS THEN
1917 IF PG_DEBUG in ('Y', 'C') THEN
1918 arp_util.debug('Validate_Sales_Credits: ' || 'Unexpected error '||sqlerrm||
1919 ' at AR_RAAPI_UTIL.Validate_Sales_Credits()+');
1920 END IF;
1921 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1922 END Validate_Sales_Credits;
1923
1924 FUNCTION Total_Selected_Line_Value
1925 (p_customer_trx_line_id IN NUMBER
1926 ,p_customer_trx_id IN NUMBER
1927 ,p_item_id IN NUMBER
1928 ,p_category_id IN NUMBER
1929 ,p_salesrep_id IN NUMBER
1930 ,p_salesgroup_id IN NUMBER DEFAULT NULL -- bug 3067675
1931 ,p_sales_credit_type IN VARCHAR2)
1932 RETURN NUMBER
1933 IS
1934 l_all_line_total NUMBER;
1935
1936 CURSOR c_all_line_total IS
1937 SELECT NVL(SUM(d.amount),0) amount
1938 FROM ra_cust_trx_line_gl_dist d
1939 ,mtl_item_categories mic
1940 ,ra_customer_trx_lines l
1941 WHERE d.customer_trx_line_id = l.customer_trx_line_id
1942 AND l.line_type = 'LINE'
1943 AND l.customer_trx_id = p_customer_trx_id
1944 AND d.account_class IN ('REV','UNEARN')
1945 AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,
1946 l.customer_trx_line_id)
1947 AND NVL(l.inventory_item_id,0) =
1948 NVL(p_item_id,NVL(l.inventory_item_id,0))
1949 AND mic.organization_id(+) = g_inv_org_id
1950 AND l.inventory_item_id = mic.inventory_item_id(+)
1951 AND NVL(p_category_id,0) =
1952 DECODE(p_category_id,NULL,0,mic.category_id)
1953 AND mic.category_set_id(+) = g_category_set_id
1954 AND ((p_salesrep_id IS NULL AND p_salesgroup_id IS NULL AND
1955 p_sales_credit_type IS NULL)
1956 OR EXISTS
1957 (SELECT 'X'
1958 FROM ra_cust_trx_line_salesreps ls
1959 WHERE ls.customer_trx_line_id = l.customer_trx_line_id
1960 AND ls.salesrep_id = NVL(p_salesrep_id,ls.salesrep_id)
1961 AND DECODE(p_sales_credit_type,'N',NVL(ls.non_revenue_salesgroup_id, -9999), NVL(ls.revenue_salesgroup_id, -9999)) =
1962 NVL(p_salesgroup_id, DECODE(p_sales_credit_type,'N',NVL(ls.non_revenue_salesgroup_id, -9999), NVL(ls.revenue_salesgroup_id, -9999)))
1963 GROUP BY ls.salesrep_id
1964 HAVING SUM(NVL(DECODE(p_sales_credit_type,'N',
1965 ls.non_revenue_percent_split,ls.revenue_percent_split),0)) <> 0));
1966
1967 BEGIN
1968 IF PG_DEBUG in ('Y', 'C') THEN
1969 arp_util.debug('AR_RAAPI_UTIL.Total_Selected_Line_Value()+');
1970 END IF;
1971 /* 5126974 - move initialization to this function
1972 to avoid org-specific failure in constant_system_values */
1973 IF g_inv_org_id IS NULL
1974 THEN
1975 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
1976 END IF;
1977 OPEN c_all_line_total;
1978 FETCH c_all_line_total INTO l_all_line_total;
1979 CLOSE c_all_line_total;
1980 RETURN l_all_line_total;
1981 EXCEPTION
1982 WHEN OTHERS THEN
1983 IF PG_DEBUG in ('Y', 'C') THEN
1984 arp_util.debug('Total_Selected_Line_Value: ' || 'Unexpected error '||sqlerrm||
1985 ' at AR_RAAPI_UTIL.Total_Selected_Line_Value()+');
1986 END IF;
1987 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1988 END Total_Selected_Line_Value ;
1989
1990 /* 7365097 - Centralized some credit memo processing logic
1991 so that it can be neatly included in both adjustable_revenue
1992 and adjustable_revenue_total
1993
1994 RETURNS FALSE if this is a regular credit memo and accounting
1995 is based on invoice
1996 */
1997 FUNCTION check_credit_memos
1998 (p_customer_trx_id IN NUMBER,
1999 p_adjustment_type IN VARCHAR2)
2000 RETURN BOOLEAN
2001 IS
2002 CURSOR c_unrec_cm(p_target_trx NUMBER) IS
2003 SELECT cmt.customer_trx_id
2004 FROM ra_customer_trx cmt
2005 WHERE cmt.previous_customer_trx_id = p_target_trx
2006 AND EXISTS ( SELECT 'Unrecognized CM'
2007 FROM ra_customer_trx_lines cmtl
2008 WHERE cmtl.customer_trx_id = cmt.customer_trx_id
2009 AND cmtl.line_type = 'LINE'
2010 AND cmtl.autorule_complete_flag = 'N');
2011
2012 l_dist_count NUMBER;
2013 l_cm_flag VARCHAR2(1);
2014 BEGIN
2015 /* 5011151 - If a user attempts to RAM or API an invoice that has
2016 credits which have not (yet) been through Rev Rec, the UNEARN
2017 will total incorrectly for the target transaction and allow
2018 more REV to be earned than it should. We are going to look
2019 for CMs that have not been through RR and process them before
2020 continuing */
2021
2022 FOR cm IN c_unrec_cm(p_customer_trx_id) LOOP
2023 l_dist_count := ARP_AUTO_RULE.create_distributions
2024 ( p_commit => 'N',
2025 p_debug => 'N',
2026 p_trx_id => cm.customer_trx_id);
2027
2028 IF PG_DEBUG in ('Y','C')
2029 THEN
2030 arp_util.debug('trx_id= ' || cm.customer_trx_id || ' dists=' ||
2031 l_dist_count);
2032 END IF;
2033 END LOOP;
2034
2035 /* 5555356/5759659 - Another corner case.. if the trx being processed
2036 is a credit, and use_inv_acctg=Y, then return zero for adjustable
2037 amounts */
2038 IF use_inv_acctg = 'Y' AND
2039 p_adjustment_type in ('EA','UN')
2040 THEN
2041 select decode(previous_customer_trx_id, NULL,'N','Y')
2042 into l_cm_flag
2043 from ra_customer_trx
2044 where customer_trx_id = p_customer_trx_id;
2045
2046 IF l_cm_flag = 'Y'
2047 THEN
2048 /* User is not allowed to adjust credits */
2049 RETURN FALSE; -- trap in callee, and return 0
2050 END IF;
2051 END IF;
2052
2053 RETURN TRUE; -- successfull, allow to continue
2054 END check_credit_memos;
2055
2056 /* Bug 2560048 RAM-C: new out parameter p_acctd_amount_out provided for use
2057 by collectibility - it is assumed that a salesrep_id will never be passed
2058 in to this routine otherwise this amount will be wrong. To be rectified
2059 when sales credit dependency removed from RAM */
2060 FUNCTION Adjustable_Revenue
2061 (p_customer_trx_line_id IN NUMBER
2062 ,p_adjustment_type IN VARCHAR2
2063 ,p_customer_trx_id IN NUMBER
2064 ,p_salesrep_id IN NUMBER
2065 ,p_salesgroup_id IN NUMBER DEFAULT NULL -- bug 3067675
2066 ,p_sales_credit_type IN VARCHAR2
2067 ,p_item_id IN NUMBER
2068 ,p_category_id IN NUMBER
2069 ,p_revenue_adjustment_id IN NUMBER
2070 ,p_line_count_out OUT NOCOPY NUMBER
2071 ,p_acctd_amount_out OUT NOCOPY NUMBER)
2072 RETURN NUMBER
2073 IS
2074 l_line_id NUMBER;
2075 l_line_amount NUMBER;
2076 l_line_acctd_amount NUMBER;
2077 l_cm_line_amount NUMBER;
2078 l_cm_line_acctd_amount NUMBER;
2079 l_net_line_amount NUMBER;
2080 l_net_line_acctd_amount NUMBER;
2081 l_line_adjustable NUMBER;
2082 l_line_count NUMBER;
2083 l_line_salesrep_total NUMBER;
2084 l_adjustable_revenue NUMBER;
2085 l_dist_count NUMBER;
2086 l_cm_flag VARCHAR2(1);
2087
2088 /* Bug 2560048 - credit memo amounts included in adjustable revenue
2089 calculation */
2090 /* Bug 3431815 - removed unnecessary extra join to ra_customer_trx
2091 to get credit memos */
2092 /* Bug 3536944: c_line broken up into 3 separate queries to improve
2093 performance */
2094
2095 CURSOR c_line IS
2096 SELECT l.customer_trx_line_id,
2097 lr.deferred_revenue_flag
2098 FROM mtl_item_categories mic
2099 ,ra_customer_trx_lines l
2100 ,ra_rules lr
2101 WHERE l.customer_trx_id = p_customer_trx_id
2102 AND l.line_type = 'LINE'
2103 AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,l.customer_trx_line_id)
2104 AND l.autorule_complete_flag IS NULL
2105 AND NVL(l.inventory_item_id,0) =
2106 NVL(p_item_id,NVL(l.inventory_item_id,0))
2107 AND DECODE(p_adjustment_type,'LL',
2108 DECODE(p_category_id,NULL,
2109 DECODE(p_item_id,NULL,
2110 DECODE(p_customer_trx_line_id,NULL,
2111 NVL(l.accounting_rule_duration,0),0),0),0),0) <= 1
2112 AND mic.organization_id(+) = g_inv_org_id
2113 AND l.inventory_item_id = mic.inventory_item_id(+)
2114 AND NVL(p_category_id,0) =
2115 DECODE(p_category_id,NULL,0,mic.category_id)
2116 AND mic.category_set_id(+) = g_category_set_id
2117 AND l.accounting_rule_id = lr.rule_id (+)
2118 AND ((p_salesrep_id IS NULL AND p_salesgroup_id IS NULL)
2119 /* AND p_sales_credit_type IS NULL) */
2120 OR EXISTS
2121 (SELECT 'X'
2122 FROM ra_cust_trx_line_salesreps ls
2123 WHERE ls.customer_trx_line_id = l.customer_trx_line_id
2124 AND ls.salesrep_id = NVL(p_salesrep_id,ls.salesrep_id)
2125 AND DECODE(p_sales_credit_type,'N',NVL(ls.non_revenue_salesgroup_id, -9999), NVL(ls.revenue_salesgroup_id, -9999)) =
2126 NVL(p_salesgroup_id, DECODE(p_sales_credit_type,'N',NVL(ls.non_revenue_salesgroup_id, -9999), NVL(ls.revenue_salesgroup_id, -9999)))
2127 AND NVL(ls.revenue_adjustment_id,0) <>
2128 NVL(p_revenue_adjustment_id,
2129 NVL(ls.revenue_adjustment_id,0) + 1)
2130 GROUP BY ls.salesrep_id
2131 HAVING SUM(NVL(DECODE(p_sales_credit_type,'N',
2132 ls.non_revenue_percent_split,ls.revenue_percent_split),0)) <> 0));
2133
2134 /* Bug 7130380 : Added hint to improve performance */
2135 CURSOR c_line_amount (p_cust_trx_line_id NUMBER) IS
2136 SELECT /*+ index(d ra_cust_trx_line_gl_dist_n1) push_pred(s)*/
2137 NVL(SUM(d.amount),0) amount
2138 ,NVL(SUM(d.acctd_amount),0) acctd_amount
2139 FROM ra_cust_trx_line_gl_dist d,
2140 ra_cust_trx_line_salesreps s
2141 WHERE d.customer_trx_line_id = p_cust_trx_line_id
2142 AND d.customer_trx_id = p_customer_trx_id
2143 AND d.account_class = DECODE(p_adjustment_type,'EA','UNEARN','REV')
2144 AND NVL(d.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
2145 NVL(d.revenue_adjustment_id,0) + 1)
2146 AND d.customer_trx_line_id = s.customer_trx_line_id (+)
2147 AND d.cust_trx_line_salesrep_id = s.cust_trx_line_salesrep_id (+)
2148 AND NVL(s.salesrep_id,-9999) =
2149 NVL(p_salesrep_id,
2150 NVL(s.salesrep_id,-9999))
2151 AND NVL(s.revenue_salesgroup_id, -9999) =
2152 NVL(p_salesgroup_id /*group*/,
2153 NVL(s.revenue_salesgroup_id, -9999));
2154
2155 /* Bug 7130380 : Added hint to improve performanc */
2156 CURSOR c_cm_line_amount (p_cust_trx_line_id NUMBER) IS
2157 SELECT /*+ index(d ra_cust_trx_line_gl_dist_n1) push_pred(s)*/
2158 NVL(SUM(NVL(d.amount,0)),0) amount
2159 ,NVL(SUM(NVL(d.acctd_amount,0)),0) acctd_amount
2160 FROM ra_cust_trx_line_gl_dist d
2161 ,ra_customer_trx_lines l
2162 ,ra_cust_trx_line_salesreps s
2163 WHERE l.previous_customer_trx_line_id = p_cust_trx_line_id
2164 AND d.customer_trx_id = l.customer_trx_id
2165 AND d.customer_trx_line_id = l.customer_trx_line_id
2166 AND d.account_class = DECODE(p_adjustment_type,'EA','UNEARN','REV')
2167 AND NVL(d.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
2168 NVL(d.revenue_adjustment_id,0) + 1)
2169 AND d.customer_trx_line_id = s.customer_trx_line_id (+)
2170 AND d.cust_trx_line_salesrep_id = s.cust_trx_line_salesrep_id (+)
2171 AND NVL(s.salesrep_id,-9999) =
2172 NVL(p_salesrep_id /* sr_id */,
2173 NVL(s.salesrep_id,-9999))
2174 AND NVL(s.revenue_salesgroup_id, -9999) =
2175 NVL(p_salesgroup_id /*group*/,
2176 NVL(s.revenue_salesgroup_id, -9999));
2177
2178 CURSOR c_line_nr_amount (p_cust_trx_line_id NUMBER) IS
2179 SELECT SUM(NVL(s.non_revenue_amount_split,0)) amount
2180 FROM ra_cust_trx_line_salesreps s
2181 WHERE s.customer_trx_line_id = p_cust_trx_line_id
2182 AND s.salesrep_id = NVL(p_salesrep_id,s.salesrep_id)
2183 AND NVL(s.non_revenue_salesgroup_id, -9999) =
2184 NVL(p_salesgroup_id,
2185 NVL(s.non_revenue_salesgroup_id, -9999))
2186 AND NVL(s.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
2187 NVL(s.revenue_adjustment_id,0) + 1);
2188
2189 CURSOR c_cm_line_nr_amount (p_cust_trx_line_id NUMBER) IS
2190 SELECT NVL(SUM(NVL(s.non_revenue_amount_split,0)),0) amount
2191 FROM ra_customer_trx_lines l
2192 ,ra_cust_trx_line_salesreps s
2193 WHERE l.previous_customer_trx_line_id = p_cust_trx_line_id
2194 AND l.customer_trx_line_id = s.customer_trx_line_id
2195 AND s.salesrep_id = NVL(p_salesrep_id /* sr_id */,s.salesrep_id)
2196 AND NVL(s.non_revenue_salesgroup_id, -9999) =
2197 NVL(p_salesgroup_id /*group*/,
2198 NVL(s.non_revenue_salesgroup_id, -9999));
2199
2200 /* 7365097 - if autoaccounting not based on SR, then
2201 we'll need to get salescredit revenue from salescredits
2202 table instead of gl_dist */
2203 CURSOR c_line_rnsr_amount (p_cust_trx_line_id NUMBER) IS
2204 SELECT SUM(NVL(s.revenue_amount_split,0)) amount
2205 FROM ra_cust_trx_line_salesreps s
2206 WHERE s.customer_trx_line_id = p_cust_trx_line_id
2207 AND s.salesrep_id = NVL(p_salesrep_id,s.salesrep_id)
2208 AND NVL(s.revenue_salesgroup_id, -9999) =
2209 NVL(p_salesgroup_id,
2210 NVL(s.revenue_salesgroup_id, -9999))
2211 AND NVL(s.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
2212 NVL(s.revenue_adjustment_id,0) + 1);
2213
2214 CURSOR c_cm_line_rnsr_amount (p_cust_trx_line_id NUMBER) IS
2215 SELECT NVL(SUM(NVL(s.revenue_amount_split,0)),0) amount
2216 FROM ra_customer_trx_lines l
2217 ,ra_cust_trx_line_salesreps s
2218 WHERE l.previous_customer_trx_line_id = p_cust_trx_line_id
2219 AND l.customer_trx_line_id = s.customer_trx_line_id
2220 AND s.salesrep_id = NVL(p_salesrep_id /* sr_id */,s.salesrep_id)
2221 AND NVL(s.revenue_salesgroup_id, -9999) =
2222 NVL(p_salesgroup_id /*group*/,
2223 NVL(s.revenue_salesgroup_id, -9999));
2224
2225 BEGIN
2226 IF PG_DEBUG in ('Y', 'C') THEN
2227 arp_util.debug('AR_RAAPI_UTIL.Adjustable_Revenue()+');
2228 arp_util.debug(' p_customer_trx_line_id = ' || p_customer_trx_line_id);
2229 arp_util.debug(' p_adjustment_type = ' || p_adjustment_type);
2230 arp_util.debug(' p_customer_trx_id = ' || p_customer_trx_id);
2231 arp_util.debug(' p_salesrep_id = ' || p_salesrep_id);
2232 arp_util.debug(' p_salesgroup_id = ' || p_salesgroup_id);
2233 arp_util.debug(' p_sales_credit_type = ' || p_sales_credit_type);
2234 arp_util.debug(' p_item_id = ' || p_item_id);
2235 arp_util.debug(' p_category_id = ' || p_category_id);
2236 arp_util.debug(' p_revenue_adjustment_id = ' ||
2237 p_revenue_adjustment_id);
2238 END IF;
2239
2240 /* 5126974 - move initialization to this function
2241 to avoid org-specific failure in constant_system_values */
2242 IF g_inv_org_id IS NULL
2243 THEN
2244 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
2245 END IF;
2246
2247 /* 7365097 - centralized CM test */
2248 IF NOT check_credit_memos(p_customer_trx_id, p_adjustment_type)
2249 THEN
2250 RETURN 0;
2251 END IF;
2252
2253 l_adjustable_revenue := 0;
2254 l_line_count := 0;
2255 FOR c1 IN c_line LOOP
2256 l_line_id := c1.customer_trx_line_id;
2257
2258 /* 6223281 - Modified method for salescredit type specific
2259 queries */
2260 IF NVL(p_sales_credit_type,'X') = 'N'
2261 THEN
2262 /* These cursors select only non-revenue salescredits
2263 (which have no corresponding dist rows) for
2264 non-revenue SC transfers */
2265 OPEN c_line_nr_amount(l_line_id);
2266 FETCH c_line_nr_amount INTO l_line_amount;
2267 CLOSE c_line_nr_amount;
2268
2269 OPEN c_cm_line_nr_amount(l_line_id);
2270 FETCH c_cm_line_nr_amount INTO l_cm_line_amount;
2271 CLOSE c_cm_line_nr_amount;
2272
2273 l_line_acctd_amount := 0;
2274 l_cm_line_acctd_amount := 0;
2275 ELSE
2276 IF NOT arp_auto_accounting.query_autoacc_def('REV','RA_SALESREPS')
2277 THEN
2278 IF p_adjustment_type = 'SA'
2279 THEN
2280 /* can't use gl_dist data since it won't have
2281 salescredit_ids populated, have to use
2282 salescredits directly (almost like non-rev SRs) */
2283 OPEN c_line_rnsr_amount(l_line_id);
2284 FETCH c_line_rnsr_amount INTO l_line_amount;
2285 CLOSE c_line_rnsr_amount;
2286
2287 OPEN c_cm_line_rnsr_amount(l_line_id);
2288 FETCH c_cm_line_rnsr_amount INTO l_cm_line_amount;
2289 CLOSE c_cm_line_rnsr_amount;
2290 ELSE
2291 /* Use raw gl_dist amounts */
2292 l_line_amount := adjustable_revenue_total(l_line_id,
2293 p_customer_trx_id,
2294 p_adjustment_type,
2295 p_revenue_adjustment_id);
2296 l_cm_line_amount := 0; -- total above includes CMs already
2297 END IF;
2298 ELSE
2299 /* These cursors use the dists table to insure that
2300 we only adjust what truly exists in gl_dist */
2301
2302 -- Get amount from corresponding invoice lines
2303 OPEN c_line_amount(l_line_id);
2304 FETCH c_line_amount INTO l_line_amount, l_line_acctd_amount;
2305 CLOSE c_line_amount;
2306
2307 -- ..then for any associated credit memo lines..
2308 OPEN c_cm_line_amount(l_line_id);
2309 FETCH c_cm_line_amount INTO l_cm_line_amount,
2310 l_cm_line_acctd_amount;
2311 CLOSE c_cm_line_amount;
2312 END IF;
2313 END IF;
2314
2315 -- The two are added to give net line amount
2316 l_net_line_amount := l_line_amount + l_cm_line_amount;
2317 l_net_line_acctd_amount := l_line_acctd_amount + l_cm_line_acctd_amount;
2318
2319 IF l_net_line_amount <> 0
2320 THEN
2321 l_line_adjustable := l_net_line_amount;
2322 ELSE
2323 l_line_adjustable := 0;
2324 p_acctd_amount_out := 0;
2325 END IF;
2326
2327 IF l_line_adjustable <> 0
2328 THEN
2329 p_acctd_amount_out := l_net_line_acctd_amount;
2330 l_line_count := l_line_count + 1;
2331 l_adjustable_revenue := l_adjustable_revenue + l_line_adjustable;
2332 END IF;
2333 p_line_count_out := l_line_count;
2334
2335 END LOOP;
2336
2337 IF PG_DEBUG in ('Y', 'C') THEN
2338 arp_util.debug(' l_adjustable_revenue = ' || l_adjustable_revenue);
2339 arp_util.debug('AR_RAAPI_UTIL.Adjustable_Revenue()-');
2340 END IF;
2341
2342 RETURN l_adjustable_revenue;
2343
2344 EXCEPTION
2345 WHEN OTHERS THEN
2346 IF PG_DEBUG in ('Y', 'C') THEN
2347 arp_util.debug('Adjustable_Revenue: ' || 'Unexpected error '||sqlerrm||
2348 ' at AR_RAAPI_UTIL.Adjustable_Revenue()+');
2349 END IF;
2350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2351
2352 END Adjustable_Revenue;
2353
2354 /* 7365097 - New function for calculating adjustable_revenue
2355 for top of revenue adjustment form */
2356 FUNCTION Adjustable_Revenue_Total
2357 (p_customer_trx_line_id IN NUMBER
2358 ,p_customer_trx_id IN NUMBER
2359 ,p_adjustment_type IN VARCHAR2
2360 ,p_revenue_adjustment_id IN NUMBER DEFAULT NULL)
2361 RETURN NUMBER
2362 IS
2363
2364 CURSOR c_line_amount (p_trx_id NUMBER, p_line_id NUMBER,
2365 p_adj_type VARCHAR2, p_rev_adj_id NUMBER) IS
2366 SELECT SUM(NVL(d.amount,0)) amount
2367 FROM ra_cust_trx_line_gl_dist_all d
2368 WHERE d.customer_trx_line_id = NVL(p_line_id,
2369 d.customer_trx_line_id)
2370 AND d.customer_trx_id = p_trx_id
2371 AND d.account_class = DECODE(p_adj_type,'EA','UNEARN','REV')
2372 AND d.account_set_flag = 'N'
2373 AND NVL(d.revenue_adjustment_id,0) <>
2374 NVL(p_rev_adj_id, NVL(d.revenue_adjustment_id,0) + 1);
2375
2376 CURSOR c_cm_line_amount (p_trx_id NUMBER, p_line_id NUMBER,
2377 p_adj_type VARCHAR2, p_rev_adj_id NUMBER) IS
2378 SELECT NVL(SUM(NVL(d.amount,0)),0) amount
2379 FROM ra_cust_trx_line_gl_dist_all d,
2380 ra_customer_trx_lines_all l
2381 WHERE l.previous_customer_trx_line_id =
2382 NVL(p_line_id,
2383 l.previous_customer_trx_line_id)
2384 AND l.previous_customer_trx_id = p_trx_id
2385 AND d.customer_trx_id = l.customer_trx_id
2386 AND d.customer_trx_line_id = l.customer_trx_line_id
2387 AND d.account_class = DECODE(p_adj_type,'EA','UNEARN','REV')
2388 AND d.account_set_flag = 'N'
2389 AND NVL(d.revenue_adjustment_id,0) <>
2390 NVL(p_rev_adj_id, NVL(d.revenue_adjustment_id,0) + 1);
2391
2392 l_inv_amt NUMBER;
2393 l_cm_amt NUMBER;
2394 l_total_amt NUMBER;
2395
2396 BEGIN
2397 IF PG_DEBUG in ('Y', 'C') THEN
2398 arp_util.debug('AR_RAAPI_UTIL.revenue_amount_total()+');
2399 arp_util.debug(' p_customer_trx_id = ' || p_customer_trx_id);
2400 arp_util.debug(' p_customer_trx_line_id = ' || p_customer_trx_line_id);
2401 arp_util.debug(' p_adjustment_type = ' || p_adjustment_type);
2402 arp_util.debug(' p_revenue_adjustment_id= ' || p_revenue_adjustment_id);
2403 END IF;
2404
2405 /* DO not allow adjustments against certain regular credit memos */
2406 IF NOT check_credit_memos(p_customer_trx_id, p_adjustment_type)
2407 THEN
2408 RETURN 0;
2409 END IF;
2410
2411 OPEN c_line_amount(p_customer_trx_id,p_customer_trx_line_id,
2412 p_adjustment_type,p_revenue_adjustment_id);
2413 FETCH c_line_amount INTO l_inv_amt;
2414 CLOSE c_line_amount;
2415
2416 OPEN c_cm_line_amount(p_customer_trx_id,p_customer_trx_line_id,
2417 p_adjustment_type,p_revenue_adjustment_id);
2418 FETCH c_cm_line_amount INTO l_cm_amt;
2419 CLOSE c_cm_line_amount;
2420
2421 l_total_amt := l_inv_amt + l_cm_amt;
2422
2423 IF PG_DEBUG in ('Y', 'C') THEN
2424
2425 arp_util.debug(' l_inv_amt = ' || l_inv_amt);
2426 arp_util.debug(' l_cm_amt = ' || l_cm_amt);
2427 arp_util.debug(' l_total_amt = ' || l_total_amt);
2428 arp_util.debug('AR_RAAPI_UTIL.revenue_amount_total()-');
2429 END IF;
2430
2431 RETURN l_total_amt;
2432
2433 END Adjustable_Revenue_Total;
2434
2435 PROCEDURE Validate_Amount
2436 (p_init_msg_list IN VARCHAR2
2437 ,p_customer_trx_line_id IN NUMBER
2438 ,p_adjustment_type IN VARCHAR2
2439 ,p_amount_mode IN VARCHAR2
2440 ,p_customer_trx_id IN NUMBER
2441 ,p_salesrep_id IN NUMBER
2442 ,p_salesgroup_id IN NUMBER DEFAULT NULL -- bug 3067675
2443 ,p_sales_credit_type IN VARCHAR2
2444 ,p_item_id IN NUMBER
2445 ,p_category_id IN NUMBER
2446 ,p_revenue_amount_in IN NUMBER
2447 ,p_revenue_percent IN NUMBER
2448 ,p_revenue_adjustment_id IN NUMBER
2449 ,p_revenue_amount_out OUT NOCOPY NUMBER
2450 ,p_adjustable_amount_out OUT NOCOPY NUMBER
2451 ,p_line_count_out OUT NOCOPY NUMBER
2452 ,x_return_status OUT NOCOPY VARCHAR2
2453 ,x_msg_count OUT NOCOPY NUMBER
2454 ,x_msg_data OUT NOCOPY VARCHAR2)
2455 IS
2456 l_adjustable_revenue NUMBER;
2457 l_revenue_total NUMBER;
2458 l_max_percent NUMBER;
2459 l_acctd_amount_out NUMBER;
2460 l_sales_credit_type VARCHAR2(15); -- bug 5644810
2461
2462 invalid_amount EXCEPTION;
2463 invalid_zero EXCEPTION;
2464 adjusted_by_other_user EXCEPTION;
2465
2466 CURSOR c_revenue_total IS
2467 SELECT NVL(SUM(d.amount),0) amount
2468 FROM ra_cust_trx_line_gl_dist d
2469 ,mtl_item_categories mic
2470 ,ra_customer_trx_lines l
2471 WHERE d.customer_trx_line_id = l.customer_trx_line_id
2472 AND d.account_class IN ('REV','UNEARN')
2473 AND NVL(d.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
2474 NVL(d.revenue_adjustment_id,0) + 1)
2475 AND l.line_type = 'LINE'
2476 AND l.customer_trx_id = p_customer_trx_id
2477 AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,
2478 l.customer_trx_line_id)
2479 AND NVL(l.inventory_item_id,0) =
2480 NVL(p_item_id,NVL(l.inventory_item_id,0))
2481 AND mic.organization_id(+) = g_inv_org_id
2482 AND l.inventory_item_id = mic.inventory_item_id(+)
2483 AND NVL(p_category_id,0) =
2484 DECODE(p_category_id,NULL,0,mic.category_id)
2485 AND mic.category_set_id(+) = g_category_set_id
2486 AND DECODE(p_category_id,NULL,
2487 DECODE(p_item_id,NULL,
2488 DECODE(p_customer_trx_line_id,NULL,
2489 DECODE(p_adjustment_type,'LL',
2490 NVL(l.accounting_rule_duration,0),0),0),0),0) <= 1
2491 AND ((p_salesrep_id IS NULL AND p_salesgroup_id IS NULL AND
2492 p_sales_credit_type IS NULL)
2493 OR EXISTS
2494 (SELECT 'X'
2495 FROM ra_cust_trx_line_salesreps ls
2496 WHERE ls.customer_trx_line_id = l.customer_trx_line_id
2497 AND ls.salesrep_id = NVL(p_salesrep_id,ls.salesrep_id)
2498 AND DECODE(p_sales_credit_type,'N',
2499 NVL(ls.non_revenue_salesgroup_id, -9999),
2500 NVL(ls.revenue_salesgroup_id, -9999)) =
2501 NVL(p_salesgroup_id, DECODE(p_sales_credit_type,'N',
2502 NVL(ls.non_revenue_salesgroup_id, -9999),
2503 NVL(ls.revenue_salesgroup_id, -9999)))
2504 GROUP BY ls.salesrep_id
2505 HAVING SUM(NVL(DECODE(p_sales_credit_type,'N',
2506 ls.non_revenue_percent_split,ls.revenue_percent_split),0)) <> 0));
2507
2508
2509 BEGIN
2510 IF PG_DEBUG in ('Y', 'C') THEN
2511 arp_util.debug('AR_RAAPI_UTIL.Validate_Amount()+');
2512 arp_util.debug(' p_customer_trx_line_id = ' || p_customer_trx_line_id);
2513 arp_util.debug(' p_amount_mode = ' || p_amount_mode);
2514 arp_util.debug(' p_salesrep_id = ' || p_salesrep_id);
2515 arp_util.debug(' p_salesgroup_id = ' || p_salesgroup_id);
2516 arp_util.debug(' p_sales_credit_type = ' || p_sales_credit_type);
2517 arp_util.debug(' p_revenue_amount_in = ' || p_revenue_amount_in);
2518 arp_util.debug(' p_revenue_percent = ' || p_revenue_percent);
2519 END IF;
2520
2521 /* 5126974 - move initialization to this function
2522 to avoid org-specific failure in constant_system_values */
2523 IF g_inv_org_id IS NULL
2524 THEN
2525 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
2526 END IF;
2527
2528 -- Initialize message list if p_init_msg_list is set to TRUE.
2529 IF FND_API.to_Boolean( p_init_msg_list )
2530 THEN
2531 FND_MSG_PUB.initialize;
2532 END IF;
2533 x_return_status := FND_API.G_RET_STS_SUCCESS;
2534 IF NVL(g_last_customer_trx_id,p_customer_trx_id - 1) <> p_customer_trx_id
2535 THEN
2536 constant_trx_values(p_customer_trx_id);
2537 END IF;
2538 /* Added IF condition for bug 5644810 */
2539 IF ((p_salesrep_id IS NULL) and (p_salesgroup_id IS NULL )) THEN
2540 l_sales_credit_type := NULL;
2541 ELSE
2542 l_sales_credit_type := p_sales_credit_type;
2543 END IF;
2544
2545 l_adjustable_revenue := Adjustable_Revenue
2546 (p_customer_trx_line_id => p_customer_trx_line_id
2547 ,p_adjustment_type => p_adjustment_type
2548 ,p_customer_trx_id => p_customer_trx_id
2549 ,p_salesrep_id => p_salesrep_id
2550 ,p_salesgroup_id => p_salesgroup_id -- bug 3067675
2551 ,p_sales_credit_type => l_sales_credit_type -- bug 5644810
2552 ,p_item_id => p_item_id
2553 ,p_category_id => p_category_id
2554 ,p_revenue_adjustment_id => p_revenue_adjustment_id
2555 ,p_line_count_out => p_line_count_out
2556 ,p_acctd_amount_out => l_acctd_amount_out);
2557
2558 p_adjustable_amount_out := l_adjustable_revenue;
2559 IF p_amount_mode = 'A'
2560 THEN
2561 p_revenue_amount_out := NVL(p_revenue_amount_in,0);
2562 ELSIF p_amount_mode = 'P'
2563 THEN
2564 OPEN c_revenue_total;
2565 FETCH c_revenue_total INTO l_revenue_total;
2566 close c_revenue_total;
2567 p_revenue_amount_out := ROUND(l_revenue_total * p_revenue_percent / 100,
2568 g_trx_precision);
2569 ELSE
2570 p_revenue_amount_out := l_adjustable_revenue;
2571 END IF;
2572
2573 IF PG_DEBUG = 'Y'
2574 THEN
2575 arp_util.debug(' --- after internal validation/calcs ---');
2576 arp_util.debug(' l_adjustable_revenue = ' || l_adjustable_revenue);
2577 arp_util.debug(' l_revenue_total = ' || l_revenue_total);
2578 arp_util.debug(' p_revenue_amount_out = ' || p_revenue_amount_out);
2579 END IF;
2580
2581 /* 7454302 - Allow adjustments of zero amounts
2582 IF p_revenue_amount_out = 0
2583 THEN
2584 IF p_revenue_adjustment_id IS NULL
2585 THEN
2586 RAISE invalid_zero;
2587 ELSE
2588 RAISE adjusted_by_other_user;
2589 END IF;
2590 END IF;
2591 */
2592 IF p_revenue_amount_out > 0
2593 THEN
2594 IF p_revenue_amount_out > l_adjustable_revenue
2595 THEN
2596 IF p_revenue_adjustment_id IS NULL
2597 THEN
2598 RAISE invalid_amount;
2599 ELSE
2600 RAISE adjusted_by_other_user;
2601 END IF;
2602 END IF;
2603 ELSIF p_revenue_amount_out < 0
2604 THEN
2605 IF p_revenue_amount_out < l_adjustable_revenue
2606 THEN
2607 IF p_revenue_adjustment_id IS NULL
2608 THEN
2609 RAISE invalid_amount;
2610 ELSE
2611 RAISE adjusted_by_other_user;
2612 END IF;
2613 END IF;
2614 END IF;
2615
2616 EXCEPTION
2617
2618 WHEN invalid_amount THEN
2619 IF p_amount_mode = 'P'
2620 THEN
2621 l_max_percent := ROUND(l_adjustable_revenue / l_revenue_total * 100,4);
2622 FND_MESSAGE.set_name
2623 (application => 'AR', name => 'AR_RA_PCT_EXCEEDS_AVAIL_PCT');
2624 FND_MESSAGE.set_token('TOT_AVAIL_PCT',l_max_percent);
2625 ELSE
2626 FND_MESSAGE.set_name
2627 (application => 'AR', name => 'AR_RA_AMT_EXCEEDS_AVAIL_REV');
2628 FND_MESSAGE.set_token('TOT_AVAIL_REV',
2629 g_trx_currency||' '||
2630 TO_CHAR(l_adjustable_revenue,g_trx_curr_format));
2631 END IF;
2632 FND_MSG_PUB.Add;
2633 x_return_status := FND_API.G_RET_STS_ERROR ;
2634 FND_MSG_PUB.Count_And_Get
2635 (p_encoded => FND_API.G_FALSE,
2636 p_count => x_msg_count,
2637 p_data => x_msg_data);
2638 WHEN invalid_zero THEN
2639 FND_MESSAGE.set_name
2640 (application => 'AR', name => 'AR_RA_ZERO_AMOUNT');
2641 FND_MSG_PUB.Add;
2642 x_return_status := FND_API.G_RET_STS_ERROR ;
2643 FND_MSG_PUB.Count_And_Get
2644 (p_encoded => FND_API.G_FALSE,
2645 p_count => x_msg_count,
2646 p_data => x_msg_data);
2647 WHEN adjusted_by_other_user THEN
2648 FND_MESSAGE.set_name
2649 (application => 'AR', name => 'AR_RA_ADJUSTED_BY_OTHER_USER');
2650 FND_MSG_PUB.Add;
2651 x_return_status := FND_API.G_RET_STS_ERROR ;
2652 FND_MSG_PUB.Count_And_Get
2653 (p_encoded => FND_API.G_FALSE,
2654 p_count => x_msg_count,
2655 p_data => x_msg_data);
2656 WHEN OTHERS THEN
2657 IF PG_DEBUG in ('Y', 'C') THEN
2658 arp_util.debug('Validate_Amount: ' || 'Unexpected error '||sqlerrm||
2659 ' at AR_RAAPI_UTIL.Validate_Amount()+');
2660 END IF;
2661 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2662
2663 END Validate_Amount;
2664
2665 FUNCTION Revalidate_GL_Dates
2666 (p_customer_trx_id IN NUMBER
2667 ,p_revenue_adjustment_id IN NUMBER
2668 ,x_msg_count OUT NOCOPY NUMBER
2669 ,x_msg_data OUT NOCOPY VARCHAR2)
2670 RETURN VARCHAR2 IS
2671
2672 l_change_count NUMBER;
2673 l_gl_date DATE;
2674 l_default_rule VARCHAR2(80);
2675 l_err_mesg VARCHAR2(2000);
2676
2677 CURSOR c_gl_date IS
2678 SELECT DISTINCT gl_date
2679 FROM ra_cust_trx_line_gl_dist
2680 WHERE revenue_adjustment_id = p_revenue_adjustment_id;
2681
2682 BEGIN
2683 IF PG_DEBUG in ('Y', 'C') THEN
2684 arp_util.debug('AR_RAAPI_UTIL.Revalidate_GL_Dates()+');
2685 END IF;
2686 l_change_count := 0;
2687 FOR c1 IN c_gl_date LOOP
2688 IF ARP_STANDARD.validate_and_default_gl_date
2689 (gl_date => c1.gl_date,
2690 trx_date => g_trx_date,
2691 validation_date1 => NULL,
2692 validation_date2 => NULL,
2693 validation_date3 => NULL,
2694 default_date1 => c1.gl_date,
2695 default_date2 => NULL,
2696 default_date3 => NULL,
2697 p_allow_not_open_flag => 'Y',
2698 p_invoicing_rule_id => g_invoicing_rule_id,
2699 p_set_of_books_id => arp_global.sysparam.set_of_books_id,
2700 p_application_id => AR_RAAPI_UTIL.application_id,
2701 default_gl_date => l_gl_date,
2702 defaulting_rule_used => l_default_rule,
2703 error_message => l_err_mesg)
2704 THEN
2705 IF c1.gl_date <> l_gl_date
2706 THEN
2707 UPDATE ra_cust_trx_line_gl_dist
2708 SET gl_date = l_gl_date
2709 WHERE revenue_adjustment_id = p_revenue_adjustment_id
2710 AND gl_date = c1.gl_date;
2711 FND_MESSAGE.set_name('AR','AR_RA_GL_DATE_CHANGED');
2712 --Int'l Calendar Project
2713 FND_MESSAGE.set_token('GL_DATE',fnd_date.date_to_chardate(c1.gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
2714 FND_MESSAGE.set_token('NEW_GL_DATE',fnd_date.date_to_chardate(l_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
2715 FND_MSG_PUB.Add;
2716 l_change_count := l_change_count + 1;
2717 END IF;
2718 ELSE
2719 FND_MESSAGE.set_name('AR','AR_RA_NO_OPEN_PERIODS');
2720 RETURN FND_API.G_FALSE;
2721 END IF;
2722 END LOOP;
2723 IF l_change_count > 0
2724 THEN
2725 FND_MSG_PUB.Count_And_Get
2726 (p_encoded => FND_API.G_FALSE,
2727 p_count => x_msg_count,
2728 p_data => x_msg_data);
2729 RETURN FND_API.G_FALSE;
2730 ELSE
2731 RETURN FND_API.G_TRUE;
2732 END IF;
2733 EXCEPTION
2734 WHEN OTHERS THEN
2735 IF PG_DEBUG in ('Y', 'C') THEN
2736 arp_util.debug('Revalidate_GL_Dates: ' || 'Unexpected error '||sqlerrm||
2737 ' at AR_RAAPI_UTIL.Revalidate_GL_Dates()+');
2738 END IF;
2739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2740 END Revalidate_GL_Dates;
2741
2742 FUNCTION Deferred_GL_Date (p_start_date IN DATE,
2743 p_period_seq_no IN NUMBER)
2744 RETURN DATE
2745 IS
2746 l_init_start_date DATE;
2747 l_init_new_period_num NUMBER;
2748 l_current_new_period_num NUMBER;
2749 l_current_start_date DATE;
2750 l_current_end_date DATE;
2751 l_current_gl_date DATE;
2752
2753 /* Bug 1940911: added period_type to 'where' clause to ensure the correct
2754 period type is being selected when more than 1 type exists
2755 in a calendar. */
2756
2757 CURSOR c_start_period IS
2758 SELECT p.start_date, p.new_period_num
2759 FROM ar_periods p,
2760 gl_sets_of_books sob,
2761 ar_period_types tp
2762 WHERE sob.period_set_name = p.period_set_name
2763 AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
2764 AND sob.accounted_period_type = p.period_type
2765 AND sob.accounted_period_type = tp.period_type
2766 AND p_start_date BETWEEN p.start_date AND p.end_date;
2767
2768 CURSOR c_current_period (p_new_period_num NUMBER) IS
2769 SELECT p.start_date, p.end_date
2770 FROM ar_periods p,
2771 gl_sets_of_books sob,
2772 ar_period_types tp
2773 WHERE sob.period_set_name = p.period_set_name
2774 AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
2775 AND sob.accounted_period_type = p.period_type
2776 AND sob.accounted_period_type = tp.period_type
2777 AND p.new_period_num = p_new_period_num;
2778
2779 BEGIN
2780 IF PG_DEBUG in ('Y', 'C') THEN
2781 arp_util.debug('AR_RAAPI_UTIL.Deferred_GL_Date()+');
2782 END IF;
2783 IF p_period_seq_no = 1
2784 THEN
2785 IF PG_DEBUG in ('Y', 'C') THEN
2786 arp_util.debug('AR_RAAPI_UTIL.Deferred_GL_Date()-');
2787 END IF;
2788 RETURN p_start_date;
2789 ELSE
2790 -- Find the period relating to the start date of revenue recognition
2791 OPEN c_start_period;
2792 FETCH c_start_period INTO l_init_start_date, l_init_new_period_num;
2793 CLOSE c_start_period;
2794 -- Find the period number of the current period
2795 l_current_new_period_num := (l_init_new_period_num + p_period_seq_no -1);
2796 OPEN c_current_period(l_current_new_period_num);
2797 FETCH c_current_period INTO l_current_start_date, l_current_end_date;
2798 CLOSE c_current_period;
2799 -- Calculate the current gl_date
2800 l_current_gl_date := LEAST((p_start_date - l_init_start_date
2801 + l_current_start_date),
2802 l_current_end_date);
2803 IF PG_DEBUG in ('Y', 'C') THEN
2804 arp_util.debug('AR_RAAPI_UTIL.Deferred_GL_Date()-');
2805 END IF;
2806 RETURN l_current_gl_date;
2807 END IF;
2808 EXCEPTION
2809 WHEN OTHERS THEN
2810 IF PG_DEBUG in ('Y', 'C') THEN
2811 arp_util.debug('Deferred_GL_Date: ' || 'Unexpected error '||sqlerrm||
2812 ' at AR_RAAPI_UTIL.Deferred_GL_Date()+');
2813 END IF;
2814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2815 END Deferred_GL_Date;
2816
2817 --
2818 -- Read only functions to allow client access to globals
2819 --
2820 FUNCTION G_RET_STS_SUCCESS
2821 RETURN VARCHAR2 IS
2822 BEGIN
2823 RETURN FND_API.G_RET_STS_SUCCESS;
2824 END G_RET_STS_SUCCESS;
2825
2826 FUNCTION G_RET_STS_ERROR
2827 RETURN VARCHAR2 IS
2828 BEGIN
2829 RETURN FND_API.G_RET_STS_ERROR;
2830 END G_RET_STS_ERROR;
2831
2832 FUNCTION G_TRUE
2833 RETURN VARCHAR2 IS
2834 BEGIN
2835 RETURN FND_API.G_TRUE;
2836 END G_TRUE;
2837
2838 FUNCTION G_VALID_LEVEL_NONE
2839 RETURN VARCHAR2 IS
2840 BEGIN
2841 RETURN FND_API.G_VALID_LEVEL_NONE;
2842 END G_VALID_LEVEL_NONE;
2843
2844 FUNCTION G_VALID_LEVEL_FULL
2845 RETURN VARCHAR2 IS
2846 BEGIN
2847 RETURN FND_API.G_VALID_LEVEL_FULL;
2848 END G_VALID_LEVEL_FULL;
2849
2850 FUNCTION G_FALSE
2851 RETURN VARCHAR2 IS
2852 BEGIN
2853 RETURN FND_API.G_FALSE;
2854 END G_FALSE;
2855
2856 FUNCTION chart_of_accounts_id
2857 RETURN NUMBER IS
2858 BEGIN
2859 RETURN g_chart_of_accounts_id;
2860 END chart_of_accounts_id;
2861
2862 FUNCTION set_of_books_id
2863 RETURN NUMBER IS
2864 BEGIN
2865 RETURN g_set_of_books_id;
2866 END set_of_books_id;
2867
2868 FUNCTION application_id
2869 RETURN NUMBER IS
2870 BEGIN
2871 RETURN g_ar_app_id;
2872 END application_id;
2873
2874 FUNCTION un_meaning
2875 RETURN VARCHAR2 IS
2876 BEGIN
2877 RETURN g_un_meaning;
2878 END un_meaning;
2879
2880 FUNCTION ea_meaning
2881 RETURN VARCHAR2 IS
2882 BEGIN
2883 RETURN g_ea_meaning;
2884 END ea_meaning;
2885
2886 FUNCTION sa_meaning
2887 RETURN VARCHAR2 IS
2888 BEGIN
2889 RETURN g_sa_meaning;
2890 END sa_meaning;
2891
2892 FUNCTION nr_meaning
2893 RETURN VARCHAR2 IS
2894 BEGIN
2895 RETURN g_nr_meaning;
2896 END nr_meaning;
2897
2898 FUNCTION ll_meaning
2899 RETURN VARCHAR2 IS
2900 BEGIN
2901 RETURN g_ll_meaning;
2902 END ll_meaning;
2903
2904 FUNCTION cost_ctr_number
2905 RETURN VARCHAR2 IS
2906 BEGIN
2907 RETURN g_cost_ctr_number;
2908 END cost_ctr_number;
2909
2910 FUNCTION category_set_id
2911 RETURN VARCHAR2 IS
2912 BEGIN
2913 RETURN g_category_set_id;
2914 END category_set_id;
2915
2916 FUNCTION category_structure_id
2917 RETURN VARCHAR2 IS
2918 BEGIN
2919 RETURN g_category_structure_id;
2920 END category_structure_id;
2921
2922 FUNCTION inv_org_id
2923 RETURN VARCHAR2 IS
2924 BEGIN
2925 /* NOTE: This is returned as a varchar.. not sure why */
2926 /* 5861728 - Initialize the value if null */
2927 IF g_inv_org_id IS NULL
2928 THEN
2929 oe_profile.get('SO_ORGANIZATION_ID',g_inv_org_id);
2930 END IF;
2931 RETURN g_inv_org_id;
2932 END inv_org_id;
2933
2934 /* 7454302 - Determines if a revenue adjustment is allowed
2935 on a zero line. Only allowed first time in.
2936
2937 returns TRUE if there are no REV lines for current zero line
2938 returns FALSE if line is not zero or is zero and has REV lines
2939
2940 p_check_line_amt skips the test of ra_customer_trx_lines when
2941 that information has already been tested */
2942 FUNCTION unearned_zero_lines(p_customer_trx_id IN NUMBER,
2943 p_customer_trx_line_id IN NUMBER DEFAULT NULL,
2944 p_check_line_amt IN VARCHAR DEFAULT 'Y',
2945 p_adjustment_type IN VARCHAR DEFAULT 'EA')
2946 RETURN BOOLEAN IS
2947 l_zero_lines NUMBER := 99;
2948 l_unearned_zero_lines NUMBER := 0;
2949 BEGIN
2950 IF p_check_line_amt = 'Y'
2951 THEN
2952 /* Are there any zero lines? */
2953 SELECT count(*)
2954 INTO l_zero_lines
2955 FROM ra_customer_trx_lines l
2956 WHERE l.customer_trx_id = p_customer_trx_id
2957 AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,
2958 l.customer_trx_line_id)
2959 AND l.line_type = 'LINE'
2960 AND l.extended_amount = 0;
2961 END IF;
2962
2963 IF l_zero_lines = 0
2964 THEN
2965 RETURN FALSE;
2966 ELSE
2967
2968 /* Do the zero lines have distributions? */
2969
2970 /* 9349263 - return TRUE only if the line is zero and
2971 no dists exist, return FALSE if line is not zero or if
2972 it is zero and distributions exist */
2973 SELECT sum(percent)
2974 INTO l_unearned_zero_lines
2975 FROM ra_cust_trx_line_gl_dist
2976 WHERE customer_trx_id = p_customer_trx_id
2977 AND customer_trx_line_id = NVL(p_customer_trx_line_id,
2978 customer_trx_line_id)
2979 AND account_class = DECODE(p_adjustment_type,'EA','REV',
2980 'UNEARN')
2981 AND account_set_flag = 'N';
2982
2983 IF l_unearned_zero_lines IS NULL
2984 THEN
2985 /* No dists exist */
2986 RETURN TRUE;
2987 ELSIF l_unearned_zero_lines <= 0
2988 THEN
2989 /* dists exist, but trx is unearned */
2990 RETURN TRUE;
2991 ELSE
2992 /* dists exist, but it is earned so no action allowed or reqd */
2993 RETURN FALSE;
2994 END IF;
2995 END IF;
2996
2997 RETURN FALSE;
2998
2999 EXCEPTION
3000 WHEN OTHERS THEN
3001 RETURN FALSE;
3002
3003 END unearned_zero_lines;
3004
3005 END AR_RAAPI_UTIL;
[Home] [Help]