[Home] [Help]
PACKAGE BODY: APPS.AR_CREDIT_MEMO_API_PUB
Source
1 PACKAGE BODY AR_CREDIT_MEMO_API_PUB AS
2 /* $Header: ARWCMAPB.pls 120.12.12010000.2 2008/09/01 09:58:30 naneja ship $ */
3
4 /* =======================================================================
5 Global Data Types
6 ======================================================================*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AR_CREDIT_MEMO_API_PUB';
9 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
10
11 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
12
13 /*Bg 7367350 Added new parameter for internal comment handling*/
14 PROCEDURE create_request (
15 -- standard API parameters
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
18 p_commit IN VARCHAR2 := FND_API.G_FALSE,
19 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_msg_count OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2,
23 -- credit memo request parameters
24 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
25 p_line_credit_flag IN ra_cm_requests.line_credits_flag%type,
26 p_line_amount IN NUMBER := 0 ,
27 p_tax_amount IN NUMBER := 0 ,
28 p_freight_amount IN NUMBER := 0,
29 p_cm_reason_code IN VARCHAR2,
30 p_comments IN VARCHAR2 DEFAULT NULL,
31 p_orig_trx_number IN VARCHAR2 DEFAULT NULL,
32 p_tax_ex_cert_num IN VARCHAR2 DEFAULT NULL,
33 p_request_url IN VARCHAR2 := 'AR_CREDIT_MEMO_API_PUB.print_default_page',
34 p_transaction_url IN VARCHAR2 := 'AR_CREDIT_MEMO_API_PUB.print_default_page',
35 p_trans_act_url IN VARCHAR2 := 'AR_CREDIT_MEMO_API_PUB.print_default_page',
36 p_cm_line_tbl IN Cm_Line_Tbl_Type_Cover%type := cm_line_tbl_type_cover ,
37 p_skip_workflow_flag IN VARCHAR2 DEFAULT 'N',
38 p_credit_method_installments IN VARCHAR2 DEFAULT NULL,
39 p_credit_method_rules IN VARCHAR2 DEFAULT NULL,
40 p_batch_source_name IN VARCHAR2 DEFAULT NULL,
41 p_org_id IN NUMBER DEFAULT NULL,
42 x_request_id OUT NOCOPY VARCHAR2,
43 /*4606558*/
44 p_attribute_rec IN arw_cmreq_cover.pq_attribute_rec_type DEFAULT attribute_rec_const,
45 p_interface_attribute_rec IN arw_cmreq_cover.pq_interface_rec_type DEFAULT
46 interface_rec_const,
47 p_global_attribute_rec IN arw_cmreq_cover.pq_global_attribute_rec_type DEFAULT
48 global_attribute_rec_const,
49 p_dispute_date IN DATE DEFAULT NULL ,-- Bug 6358930
50 p_internal_comment IN VARCHAR2 DEFAULT NULL
51 )
52 IS
53
54 -- Local Variables
55 /*4606558*/
56 l_attribute_rec arw_cmreq_cover.pq_attribute_rec_type ;
57 l_interface_attribute_rec arw_cmreq_cover.pq_interface_rec_type;
58 l_global_attribute_rec arw_cmreq_cover.pq_global_attribute_rec_type;
59 l_cm_line_tbl arw_cmreq_cover.Cm_Line_Tbl_Type_Cover;
60
61
62 l_api_name CONSTANT VARCHAR2(20) := 'Create Request';
63 l_api_version CONSTANT NUMBER := 1.0;
64 l_val_return_status VARCHAR2(1);
65 validation_failed EXCEPTION;
66 creation_failed EXCEPTION;
67 l_status VARCHAR2(100) := null;
68 l_org_return_status VARCHAR2(1);
69 l_org_id NUMBER;
70
71 BEGIN
72 IF PG_DEBUG in ('Y', 'C') THEN
73 arp_util.debug ('AR_CREDIT_MEMO_API_PUB.Create_request()+');
74 arp_util.debug ('create_request: ' || 'p_init_msg_list :'||p_init_msg_list);
75 arp_util.debug ('create_request: ' || 'p_commit :'||p_commit);
76 arp_util.debug ('create_request: ' || 'p_validation_level :'||p_validation_level);
77 arp_util.debug ('create_request: ' || 'p_customer_trx_id :'||p_customer_trx_id);
78 arp_util.debug ('create_request: ' || 'p_line_credit_flag :'||p_line_credit_flag);
79 arp_util.debug ('create_request: ' || 'p_line_amount :'||p_line_amount);
80 arp_util.debug ('create_request: ' || 'p_tax_amount :'||p_tax_amount);
81 arp_util.debug ('create_request: ' || 'p_freight_amount :'||p_freight_amount);
82 arp_util.debug ('create_request: ' || 'p_cm_reason_code :'||p_cm_reason_code);
83 /* Bug 3206020 arp_util.debug ('create_request: ' || 'p_comments :'||p_comments);*/
84 arp_util.debug ('create_request: ' || 'p_request_url :'||p_request_url);
85 arp_util.debug ('create_request: ' || 'p_transaction_url :'||p_transaction_url);
86 arp_util.debug ('create_request: ' || 'p_trans_act_url :'||p_trans_act_url);
87 arp_util.debug ('create_request: ' || 'p_dispute_date :'||p_dispute_date); -- Bug 6358930
88 END IF;
89
90 /*------------------------------------+
91 | Standard start of API savepoint |
92 +------------------------------------*/
93
94 SAVEPOINT Create_request_pvt;
95
96 /*--------------------------------------------------+
97 | Standard call to check for call compatibility |
98 +--------------------------------------------------*/
99 IF PG_DEBUG in ('Y', 'C') THEN
100 arp_util.debug('create_request: ' || 'Checking call compatibility');
101 END IF;
102 IF NOT FND_API.Compatible_API_Call( l_api_version,
103 p_api_version,
104 l_api_name,
105 G_PKG_NAME) THEN
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 END IF;
108
109 /*--------------------------------------------------------------+
110 | Initialize message list if p_init_msg_list is set to TRUE |
111 +--------------------------------------------------------------*/
112
113 IF PG_DEBUG in ('Y', 'C') THEN
114 arp_util.debug('create_request: ' || 'Initializing message list');
115 END IF;
116 IF FND_API.to_Boolean( p_init_msg_list ) THEN
117 FND_MSG_PUB.initialize;
118 END IF;
119
120 /*-----------------------------------------+
121 | Initialize return status to SUCCESS |
122 +-----------------------------------------*/
123
124 IF PG_DEBUG in ('Y', 'C') THEN
125 arp_util.debug ('create_request: ' || 'Initializing return status to success');
126 END IF;
127 x_return_status := FND_API.G_RET_STS_SUCCESS;
128
129 /*---------------------------------------------+
130 | ========== Start of API Body ========== |
131 +---------------------------------------------*/
132
133 -- Begin SSA change
134 l_org_id := p_org_id;
135 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
136 ar_mo_cache_utils.set_org_context_in_api
137 (
138 p_org_id =>l_org_id,
139 p_return_status =>l_org_return_status
140 );
141 -- End SSA change
142 -- ORASHID 26-FEB-2004
143
144 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
145 x_return_status := FND_API.G_RET_STS_ERROR;
146 ELSE
147
148
149 /*------------------------------------------+
150 | Validate the receipt information. |
151 | Do not continue if there are errors. |
152 +------------------------------------------*/
153
154 IF PG_DEBUG in ('Y', 'C') THEN
155 arp_util.debug ('create_request: ' || 'Validating request parameters');
156 END IF;
157 /*4606558*/
158 l_attribute_rec:=p_attribute_rec;
159 l_interface_attribute_rec:=p_interface_attribute_rec;
160 l_global_attribute_rec:=p_global_attribute_rec;
161 l_cm_line_tbl:=p_cm_line_tbl;
162
163 validate_request_parameters (
164 p_customer_trx_id => p_customer_trx_id,
165 p_line_credit_flag => p_line_credit_flag,
166 p_line_amount => p_line_amount,
167 p_tax_amount => p_tax_amount,
168 p_freight_amount => p_freight_amount,
169 p_cm_reason_code => p_cm_reason_code,
170 p_comments => p_comments,
171 p_request_url => p_request_url,
172 p_transaction_url => p_transaction_url,
173 p_trans_act_url => p_trans_act_url,
174 p_cm_line_tbl => l_cm_line_tbl,
175 p_org_id => l_org_id,
176 l_val_return_status => l_val_return_status,
177 /*4606558*/
178 p_skip_workflow_flag => p_skip_workflow_flag,
179 p_batch_source_name => p_batch_source_name,
180 p_attribute_rec => l_attribute_rec,
181 p_interface_attribute_rec => l_interface_attribute_rec,
182 p_global_attribute_rec => l_global_attribute_rec,
183 p_dispute_date => p_dispute_date -- Bug 6358930
184 );
185
186 IF PG_DEBUG in ('Y', 'C') THEN
187 arp_util.debug ('create_request: ' || 'Done with validate_request_parameters');
188 END IF;
189
190 IF l_val_return_status <> FND_API.G_RET_STS_SUCCESS THEN
191 IF PG_DEBUG in ('Y', 'C') THEN
192 arp_util.debug ('create_request: ' || 'Failed validation routine. Raising exception');
193 END IF;
194 raise validation_failed;
195 ELSE
196 -- call the entity handler
197 IF PG_DEBUG in ('Y', 'C') THEN
198 arp_util.debug ('create_request: ' || 'will call arw_cmreq_cover.ar_request_cm');
199 END IF;
200
201 x_request_id := arw_cmreq_cover.ar_request_cm(
202 p_customer_trx_id => p_customer_trx_id,
203 p_line_credits_flag => p_line_credit_flag,
204 p_line_amount => p_line_amount,
205 p_tax_amount => p_tax_amount,
206 p_freight_amount => p_freight_amount,
207 p_cm_lines_tbl => p_cm_line_tbl,
208 p_cm_reason_code => p_cm_reason_code,
209 p_comments => p_comments,
210 p_url => p_request_url,
211 p_transaction_url => p_transaction_url,
212 p_trans_act_url => p_trans_act_url,
213 p_orig_trx_number => p_orig_trx_number,
214 p_tax_ex_cert_num => p_tax_ex_cert_num ,
215 p_skip_workflow_flag => p_skip_workflow_flag ,
216 p_credit_method_installments => p_credit_method_installments ,
217 p_credit_method_rules => p_credit_method_rules ,
218 p_batch_source_name => p_batch_source_name,
219 /*4606558*/
220 pq_attribute_rec => l_attribute_rec,
221 pq_interface_attribute_rec => l_interface_attribute_rec,
222 pq_global_attribute_rec => l_global_attribute_rec,
223 p_dispute_date => p_dispute_date, -- Bug 6358930
224 p_internal_comment => p_internal_comment /*7367350*/
225 );
226
227 -- bug 2290738 : arw_cmreq_cover.ar_request_cm will pass -1 if any error is encountered
228 if x_request_id = '-1' then
229 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
230 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Failure encountered in AR_CREDIT_MEMO_API_PUB.Create_request' ||
231 ' call to arw_cmreq_cover.ar_request_cm');
232 FND_MSG_PUB.Add;
233 raise creation_failed;
234 end if;
235
236 IF PG_DEBUG in ('Y', 'C') THEN
237 arp_util.debug ('create_request: ' || 'Done with arw_cmreq_cover.ar_request_cm');
238 END IF;
239
240 END IF;
241 END IF;
242
243 /*--------------------------------+
244 | Standard check of p_commit |
245 +--------------------------------*/
246
247 IF FND_API.To_Boolean( p_commit ) THEN
248 IF PG_DEBUG in ('Y', 'C') THEN
249 arp_util.debug('create_request: ' || 'committing');
250 END IF;
251 Commit;
252 END IF;
253
254 IF PG_DEBUG in ('Y', 'C') THEN
255 arp_util.debug ('AR_CREDIT_MEMO_API_PUB.Create_request()-');
256 END IF;
257
258 EXCEPTION
259 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
260 IF PG_DEBUG in ('Y', 'C') THEN
261 arp_util.debug ('create_request: ' || 'Exception FND_API.G_EXC_UNEXPECTED_ERROR');
262 arp_util.debug('create_request: ' || SQLERRM, G_MSG_ERROR);
263 END IF;
264 ROLLBACK TO Create_request_PVT;
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
266
267 -- Display_Parameters;
268 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
269 p_count => x_msg_count,
270 p_data => x_msg_data);
271
272 WHEN creation_failed THEN
273 IF PG_DEBUG in ('Y', 'C') THEN
274 arp_util.debug('create_request: ' || 'Exception creation_failed');
275 arp_util.debug('create_request: ' || SQLERRM, G_MSG_ERROR);
276 END IF;
277 -- ROLLBACK TO Create_request_cm;
278 x_return_status := FND_API.G_RET_STS_ERROR;
279
280 -- Display_Parameters;
281 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
282 p_count => x_msg_count,
283 p_data => x_msg_data);
284
285 WHEN validation_failed THEN
286 IF PG_DEBUG in ('Y', 'C') THEN
287 arp_util.debug('create_request: ' || 'Exception validation_failed');
288 arp_util.debug('create_request: ' || SQLERRM, G_MSG_ERROR);
289 END IF;
290 ROLLBACK TO Create_request_PVT;
291 /* bug 2290738 - returns the validation status, rather than the 'unexpected' status */
292 x_return_status := l_val_return_status;
293
294 -- Display_Parameters;
295 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
296 p_count => x_msg_count,
297 p_data => x_msg_data);
298
299 WHEN OTHERS THEN
300
301 /*-------------------------------------------------------+
302 | Handle application errors that result from trapable |
303 | error conditions. The error messages have already |
304 | been put on the error stack. |
305 +-------------------------------------------------------*/
306 IF PG_DEBUG in ('Y', 'C') THEN
307 arp_util.debug ('create_request: ' || 'Exception OTHERS');
308 END IF;
309 ROLLBACK TO Create_request_pvt;
310
311 --If only one error message on the stack, retrieve it
312
313 x_return_status := FND_API.G_RET_STS_ERROR ;
314
315 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
316 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','AR_CREDIT_MEMO_API_PUB.Create_request');
317 FND_MSG_PUB.Add;
318
319 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
320 p_count => x_msg_count,
321 p_data => x_msg_data);
322
323 END create_request;
324
325 /*old one*/
326 PROCEDURE validate_request_parameters (
327 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
328 p_line_credit_flag IN VARCHAR2,
329 p_line_amount IN NUMBER,
330 p_tax_amount IN NUMBER,
331 p_freight_amount IN NUMBER,
332 p_cm_reason_code IN VARCHAR2,
333 p_comments IN VARCHAR2,
334 p_request_url IN VARCHAR2,
335 p_transaction_url IN VARCHAR2,
336 p_trans_act_url IN VARCHAR2,
337 p_cm_line_tbl IN Cm_Line_Tbl_Type_Cover%type,
338 p_org_id IN NUMBER DEFAULT NULL,
339 l_val_return_status OUT NOCOPY VARCHAR2,
340 p_dispute_date IN DATE DEFAULT NULL) -- Bug 6358930
341 IS
342
343 -- Local Variables
344
345 l_allow_overapplication VARCHAR2(1);
346 l_lines_remaining NUMBER;
347 l_tax_remaining NUMBER;
348 l_freight_remaining NUMBER;
349 l_lines_original NUMBER;
350 l_tax_original NUMBER;
354 l_extended_amount NUMBER;
351 l_line_percent NUMBER;
352 l_tax_percent NUMBER;
353 l_count_reason_code NUMBER;
355 l_credited_amount NUMBER :=0 ;
356 l_count_trx NUMBER;
357 l_credit_memo_type_id NUMBER;
358 l_org_return_status VARCHAR2(1);
359 l_org_id NUMBER;
360 l_trx_date DATE;
361
362 BEGIN
363
364 IF PG_DEBUG in ('Y', 'C') THEN
365 arp_util.debug ('AR_CREDIT_MEMO_API_PUB.validate_request_parameters()+');
366 END IF;
367 /*-----------------------------------------+
368 | Validating customer_trx_id |
369 +-----------------------------------------*/
370
371 IF PG_DEBUG in ('Y', 'C') THEN
372 arp_util.debug('validate_request_parameters: ' || 'Validating customer_trx_id');
373 END IF;
374
375 -- Begin SSA change
376 l_org_id := p_org_id;
377 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
378 ar_mo_cache_utils.set_org_context_in_api
379 (
380 p_org_id =>l_org_id,
381 p_return_status =>l_org_return_status
382 );
383 -- End SSA change
384 -- ORASHID 26-FEB-2004
385
386 select count(*) into l_count_trx from
387 ra_customer_trx
388 where customer_trx_id = p_customer_trx_id;
389
390 -- If customer trx is invalid
391 IF l_count_trx = 0 THEN
392 IF PG_DEBUG in ('Y', 'C') THEN
393 arp_util.debug('validate_request_parameters: ' || 'Did not find customer_trx_id');
394 END IF;
395 -- Put error message on stack
396 FND_MESSAGE.SET_NAME('AR','AR_TAPI_TRANS_NOT_EXIST');
397 FND_MESSAGE.SET_TOKEN('CUSTOMER_TRX_ID',p_customer_trx_id);
398
399 FND_MSG_PUB.Add;
400 l_val_return_status := FND_API.G_RET_STS_ERROR ;
401 return;
402 END IF;
403
404 /*-----------------------------------------+
405 | Validating line_credit_flag |
406 +-----------------------------------------*/
407 IF PG_DEBUG in ('Y', 'C') THEN
408 arp_util.debug('validate_request_parameters: ' || 'Validating line_credit_flag');
409 END IF;
410 IF p_line_credit_flag not in ('Y','N') THEN
411 IF PG_DEBUG in ('Y', 'C') THEN
412 arp_util.debug ('validate_request_parameters: ' || 'Invalid line_credit_flag');
413 END IF;
414 FND_MESSAGE.SET_NAME('AR','AR_CMWF_API_INVALID_VALUE');
415 FND_MSG_PUB.Add;
416 l_val_return_status := FND_API.G_RET_STS_ERROR ;
417 return;
418 ELSE
419 /* If dispute is at line level, there has to be atleast one line */
420 IF p_line_credit_flag = 'Y' AND p_cm_line_tbl.count = 0 THEN
421 IF PG_DEBUG in ('Y', 'C') THEN
422 arp_util.debug ('validate_request_parameters: ' || 'line_credit_flag is Y and there are no lines');
423 END IF;
424 l_val_return_status := FND_API.G_RET_STS_ERROR ;
425 FND_MESSAGE.SET_NAME('AR','AR_CMWF_API_NO_LINES_INFO ');
426 FND_MSG_PUB.Add;
427 return;
428 END IF;
429 END IF;
430
431 /*--------------------------------------------------------------------+
432 | Validating tax, freight and line amounts |
433 | Bug 2290738 : also check if this trx type has a default CM trx type |
434 +---------------------------------------------------------------------*/
435 -- Checking to see if over application is allowed for transaction type
436 SELECT ctt.allow_overapplication_flag, ctt.credit_memo_type_id
437 INTO l_allow_overapplication, l_credit_memo_type_id
438 FROM ra_cust_trx_types ctt,
439 ra_customer_trx ct
440 WHERE ct.cust_trx_type_id = ctt.cust_trx_type_id
441 AND ct.customer_trx_id = p_customer_trx_id;
442
443 IF PG_DEBUG in ('Y', 'C') THEN
444 arp_util.debug('validate_request_parameters: ' || 'CM trx type id = ' || to_char(l_credit_memo_type_id));
445 END IF;
446 IF l_credit_memo_type_id IS NULL THEN
447 l_val_return_status := FND_API.G_RET_STS_ERROR ;
448 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
449 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Error : no CM trx type id defined.');
450 FND_MSG_PUB.Add;
451 return;
452 END IF;
453
454 IF PG_DEBUG in ('Y', 'C') THEN
455 arp_util.debug('validate_request_parameters: ' || 'Overapplication allowed ? ' || l_allow_overapplication);
456 END IF;
457
458 -- Selecting the amount remaining for the transaction
459 /* r12 eTax - get amounts original for pct calculation */
460 IF p_line_credit_flag = 'N' THEN
461 SELECT SUM(NVL(amount_line_items_remaining,0)),
462 SUM(NVL(tax_remaining,0)),
463 SUM(NVL(freight_remaining,0)),
464 SUM(NVL(amount_line_items_original,0)),
465 SUM(NVL(tax_original,0))
466 INTO l_lines_remaining,
467 l_tax_remaining,
468 l_freight_remaining,
469 l_lines_original,
470 l_tax_original
471 FROM ar_payment_schedules ct
472 WHERE ct.customer_trx_id = p_customer_trx_id ;
473
474 IF PG_DEBUG in ('Y', 'C') THEN
475 arp_util.debug('validate_request_parameters: ' || 'l_lines_remaining = ' || to_char(l_lines_remaining));
476 arp_util.debug('validate_request_parameters: ' || 'l_tax_remaining = ' || to_char(l_tax_remaining));
477 arp_util.debug('validate_request_parameters: ' || 'l_freight_remaining = ' || to_char(l_freight_remaining));
478 END IF;
482 -- Bug 2290738
479
480 -- If over application flag is 'N' and amount remaining is more than amount
481 -- requested, then raise error
483 -- This should be the other way round i.e. error if amount being credited is
484 -- greater than amount due remaining, so signs must be flipped on the former
485
486 /*-------------------------------------------------------------------------+
487 | Bug # 2768573: The following piece of code will throw an error to
488 | the iReceivable user if user requests a credit memo for an amount
489 | that is more than the balance remaining and the over application is
490 | turned not allowed.
491 |
492 | Although, this is a correct validation to happen, it shuld not
493 | happen here. When the actual credit memo is being created, this
494 | validation happens again (arw_cm_cover.create_header_cm), and that
495 | is the correct place for it to happen. So, commenting out the
496 | validation from here.
497 |
498 | ORASHID 17-FEB-2003
499 +-------------------------------------------------------------------------*/
500
501 /*
502 IF l_allow_overapplication = 'N' THEN
503
504 IF (((p_line_amount * -1) > l_lines_remaining) OR
505 ((p_tax_amount * -1) > l_tax_remaining) OR
506 ((p_freight_amount * -1) > l_freight_remaining)) THEN
507 IF PG_DEBUG in ('Y', 'C') THEN
508 arp_util.debug('validate_request_parameters: ' || 'Over application flag is N and amount remaining is more than amount requested');
509 END IF;
510
511 l_val_return_status := FND_API.G_RET_STS_ERROR ;
512 FND_MESSAGE.SET_NAME('AR','AR_CKAP_OVERAPP');
513 FND_MSG_PUB.Add;
514 return;
515 END IF;
516 END IF;
517 */
518
519 END IF;
520
521 /* R12 eTax line and tax percents must be the same if not zero */
522 l_line_percent := ROUND( ((p_line_amount * -1 / l_lines_original) * 100), 4);
523 l_tax_percent := ROUND( ((p_tax_amount * -1 / l_tax_original) * 100), 4);
524
525 IF (NVL(l_tax_percent,0) <> 0 AND
526 NVL(l_line_percent,0) <> 0 AND
527 NVL(l_line_percent,0) <> NVL(l_tax_percent,0)) THEN
528 FND_MESSAGE.set_name( 'AR', 'AR_ETX_BAD_CM_LINE_TAX_RATIO');
529 FND_MSG_PUB.Add;
530 RETURN;
531 END IF;
532
533 /*-----------------------------------------+
534 | Validating the reason_code |
535 +-----------------------------------------*/
536 IF PG_DEBUG in ('Y', 'C') THEN
537 arp_util.debug ('validate_request_parameters: ' || 'Validating the reason code');
538 END IF;
539 SELECT
540 count(*)
541 INTO
542 l_count_reason_code
543 FROM
544 ar_lookups
545 WHERE lookup_type ='CREDIT_MEMO_REASON'
546 AND lookup_code = p_cm_reason_code ;
547
548 IF l_count_reason_code = 0 THEN
549 l_val_return_status := FND_API.G_RET_STS_ERROR ;
550 FND_MESSAGE.SET_NAME('AR','AR_RAXTRX-1719');
551 FND_MSG_PUB.Add;
552 return;
553 END IF;
554
555 /*-------------------------------------------------+
556 | Validating the line when dispute is at line level|
557 +--------------------------------------------------*/
558 IF PG_DEBUG in ('Y', 'C') THEN
559 arp_util.debug ('validate_request_parameters: ' || 'Validating the line when dispute is at line level');
560 END IF;
561
562 IF p_line_credit_flag = 'Y' THEN
563
564 FOR i in 1..p_cm_line_tbl.count
565 LOOP
566 SELECT count(*)
567 INTO l_count_trx
568 FROM ra_customer_trx_lines
569 WHERE customer_trx_id = p_customer_trx_id
570 AND customer_trx_line_id = p_cm_line_tbl(i).customer_trx_line_id;
571
572 IF l_count_trx = 0 THEN
573 IF PG_DEBUG in ('Y', 'C') THEN
574 arp_util.debug ('validate_request_parameters: ' || 'Transaction does not exist');
575 END IF;
576 FND_MESSAGE.SET_NAME('AR','AR_TAPI_LINE_NOT_EXIST');
577 FND_MESSAGE.SET_TOKEN('CUSTOMER_TRX_LINE_ID',p_cm_line_tbl(i).customer_trx_line_id);
578 FND_MSG_PUB.Add;
579 l_val_return_status := FND_API.G_RET_STS_ERROR ;
580 return;
581 END IF;
582
583 /*-------------------------------------------------------------------------+
584 | Bug # 2768573: The following piece of code will throw an error to
585 | the iReceivable user if user requests a credit memo for an amount
586 | that is more than the balance remaining and the over application is
587 | turned not allowed.
588 |
589 | Although, this is a correct validation to happen, it shuld not
590 | happen here. When the actual credit memo is being created, this
591 | validation happens again (arw_cm_cover.create_header_cm), and that
592 | is the correct place for it to happen. So, commenting out the
593 | validation from here.
594 |
595 | ORASHID 17-FEB-2003
596 +-------------------------------------------------------------------------*/
597
598 /*
599 IF l_allow_overapplication = 'N' THEN
600 IF PG_DEBUG in ('Y', 'C') THEN
601 arp_util.debug ('validate_request_parameters: ' || 'This transaction type does not allow over application');
602 END IF;
603 -- Get the extended amount for the customer_trx_line_id
604 SELECT NVL(extended_amount,0)
608 AND customer_trx_line_id = p_cm_line_tbl(i).customer_trx_line_id;
605 INTO l_extended_amount
606 FROM ra_customer_trx_lines
607 WHERE customer_trx_id = p_customer_trx_id
609
610 -- Get the credited amount, if any, for this customer_trx_line
611 SELECT NVL(extended_amount,0)
612 INTO l_credited_amount
613 FROM ra_customer_trx_lines
614 WHERE previous_customer_trx_id = p_customer_trx_id
615 AND previous_customer_trx_line_id = p_cm_line_tbl(i).customer_trx_line_id;
616
617 IF p_cm_line_tbl(i).extended_amount > (l_extended_amount - l_credited_amount) THEN
618 IF PG_DEBUG in ('Y', 'C') THEN
619 arp_util.debug('validate_request_parameters: ' || 'Over application is not allowed. raising exception');
620 END IF;
621 FND_MESSAGE.SET_NAME('AR','AR_CKAP_OVERAPP');
622 FND_MSG_PUB.Add;
623 l_val_return_status := FND_API.G_RET_STS_ERROR ;
624 return;
625 END IF;
626 END IF;
627 */
628 END LOOP;
629 END IF;
630
631 -- START Bug 6358930
632 /*--------------------------------------------------+
633 | Validating the dispute date |
634 +--------------------------------------------------*/
635 IF PG_DEBUG in ('Y', 'C') THEN
636 arp_util.debug ('validate_request_parameters: ' || 'Validating the dispute date passed');
637 END IF;
638 IF p_dispute_date IS NOT NULL THEN
639 SELECT trx_date INTO l_trx_date
640 FROM ra_customer_trx
641 WHERE customer_trx_id = p_customer_trx_id;
642
643 -- If dispute date is less than trxn date
644 IF trunc(l_trx_date) > trunc(p_dispute_date) THEN
645 IF PG_DEBUG in ('Y', 'C') THEN
646 arp_util.debug('validate_request_parameters: ' || 'Dispute date is less than transaction date');
647 END IF;
648 -- Put error message on stack
649 FND_MESSAGE.SET_NAME ('AR','AR_DISPUTE_BEFORE_TRX_DATE');
650 FND_MSG_PUB.Add;
651 l_val_return_status := FND_API.G_RET_STS_ERROR ;
652 return;
653 END IF;
654
655 END IF;
656 -- END Bug 6358930
657 l_val_return_status := FND_API.G_RET_STS_SUCCESS;
658 IF PG_DEBUG in ('Y', 'C') THEN
659 arp_util.debug ('AR_CREDIT_MEMO_API_PUB.Validate_request_parameters()-');
660 END IF;
661
662 EXCEPTION
663 WHEN OTHERS THEN
664 IF PG_DEBUG in ('Y', 'C') THEN
665 arp_util.debug('Exception : Others in validate_request_parameters');
666 END IF;
667 END validate_request_parameters;
668
669 PROCEDURE get_request_status (
670 -- standard API parameters
671 p_api_version IN NUMBER,
672 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
673 x_msg_count OUT NOCOPY NUMBER,
674 x_msg_data OUT NOCOPY VARCHAR2,
675 x_return_status OUT NOCOPY VARCHAR2,
676 -- CREDIT MEMO REQUEST PARAMETERS
677 p_request_id IN varchar2,
678 x_status_meaning OUT NOCOPY varchar2,
679 x_reason_meaning OUT NOCOPY varchar2,
680 x_customer_trx_id OUT NOCOPY ra_customer_trx.customer_trx_id%type,
681 x_cm_customer_trx_id OUT NOCOPY ra_customer_trx.customer_trx_id%type,
682 x_line_amount OUT NOCOPY ra_cm_requests.line_amount%type,
683 x_tax_amount OUT NOCOPY ra_cm_requests.tax_amount%type,
684 x_freight_amount OUT NOCOPY ra_cm_requests.freight_amount%type,
685 x_line_credits_flag OUT NOCOPY varchar2,
686 x_created_by OUT NOCOPY wf_users.display_name%type,
687 x_creation_date OUT NOCOPY DATE,
688 x_approval_date OUT NOCOPY DATE,
689 x_comments OUT NOCOPY ra_cm_requests.comments%type,
690 x_cm_line_tbl OUT NOCOPY Cm_Line_Tbl_Type_Cover%type,
691 x_cm_activity_tbl OUT NOCOPY x_cm_activity_tbl%type,
692 x_cm_notes_tbl OUT NOCOPY x_cm_notes_tbl%type)
693
694 IS
695
696 -- Local variables
697 l_api_version CONSTANT NUMBER := 1.0;
698 l_api_name VARCHAR2(100) := 'Get Request Status';
699 l_reason_code VARCHAR2(240);
700 l_created_by VARCHAR2(240);
701 l_line_count NUMBER;
702 l_count_activities NUMBER;
703 l_count_notes NUMBER;
704
705 CURSOR per_user_cur is
706 SELECT display_name
707 FROM wf_users
708 WHERE orig_system = 'PER'
709 AND orig_system_id = l_created_by;
710
711 CURSOR fnd_user_cur is
712 SELECT display_name
713 FROM wf_users
714 WHERE orig_system = 'FND_USR'
715 AND orig_system_id = l_created_by;
716
717 CURSOR line_det_cur is
718 SELECT customer_trx_line_id,
719 extended_amount,
720 quantity,
721 price
722 FROM ra_cm_request_lines_all
723 WHERE request_id = p_request_id;
724
725 line_det_rec line_det_cur%rowtype;
726
727 CURSOR activities_cur is
728 SELECT to_char(ias.begin_date,'DD-MON-RR HH24:MI:SS') begin_date ,
729 ap.display_name||'/'||ac.display_name activity_name ,
730 ias.activity_status status,
731 ias.activity_result_code result_code,
732 ias.assigned_user -- user
733 FROM wf_item_activity_statuses ias,
734 wf_process_activities pa,
735 wf_activities_vl ac,
736 wf_activities_vl ap,
737 wf_items i
738 WHERE ias.item_type = 'ARCMREQ'
742 AND pa.activity_item_type = ac.item_type
739 AND ias.item_key = p_request_id
740 AND ias.process_activity = pa.instance_id
741 AND pa.activity_name = ac.name
743 AND pa.process_name = ap.name
744 AND pa.process_item_type = ap.item_type
745 AND i.item_key = ias.item_key
746 AND i.begin_date >=ac.begin_date
747 AND i.begin_date < nvl(ac.end_date, i.begin_date+1)
748 ORDER BY ias.begin_date,ias.execution_time;
749
750 activities_rec activities_cur%rowtype;
751
752 CURSOR notes_cur is
753 SELECT text
754 FROM ar_notes
755 WHERE customer_trx_id = x_customer_trx_id;
756
757 notes_rec notes_cur%rowtype;
758
759 p_line_credits_flag VARCHAR2(3);
760 l_request_id NUMBER;
761
762 BEGIN
763
764 IF PG_DEBUG in ('Y', 'C') THEN
765 arp_util.debug ('AR_CREDIT_MEMO_API_PUB.Get_request_status()+');
766 arp_util.debug ('get_request_status: ' || 'p_init_msg_list :'||p_init_msg_list);
767 arp_util.debug ('get_request_status: ' || 'p_api_version :'||p_api_version);
768 arp_util.debug ('get_request_status: ' || 'p_request_id :'||p_request_id);
769 END IF;
770
771 /*--------------------------------------------------+
772 | Standard call to check for call compatibility |
773 +--------------------------------------------------*/
774 IF PG_DEBUG in ('Y', 'C') THEN
775 arp_util.debug ('get_request_status: ' || 'Standard call to check for call compatibility');
776 END IF;
777 IF NOT FND_API.Compatible_API_Call( l_api_version,
778 p_api_version,
779 l_api_name,
780 G_PKG_NAME)
781 THEN
782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
783 END IF;
784
785 /*--------------------------------------------------------------+
786 | Initialize message list if p_init_msg_list is set to TRUE |
787 +--------------------------------------------------------------*/
788 IF PG_DEBUG in ('Y', 'C') THEN
789 arp_util.debug ('get_request_status: ' || 'Initializing message list');
790 END IF;
791 IF FND_API.to_Boolean( p_init_msg_list ) THEN
792 FND_MSG_PUB.initialize;
793 END IF;
794
795 /*-----------------------------------------+
796 | Initialize return status to SUCCESS |
797 +-----------------------------------------*/
798
799 x_return_status := FND_API.G_RET_STS_SUCCESS;
800
801 /*---------------------------------------------+
802 | ========== Start of API Body ========== |
803 +---------------------------------------------*/
804
805 /*---------------------------------------------+
806 | ===Validate the request_id=== |
807 +---------------------------------------------*/
808 IF PG_DEBUG in ('Y', 'C') THEN
809 arp_util.debug ('get_request_status: ' || 'Validating the request_id');
810 END IF;
811 BEGIN
812 SELECT customer_trx_id,
813 cm_customer_trx_id,
814 cm_reason_code,
815 nvl(line_amount,0),
816 nvl(tax_amount,0),
817 nvl(freight_amount,0),
818 line_credits_flag,
819 created_by,
820 creation_date,
821 approval_date,
822 comments
823 INTO x_customer_trx_id,
824 x_cm_customer_trx_id,
825 l_reason_code,
826 x_line_amount,
827 x_tax_amount,
828 x_freight_amount,
829 x_line_credits_flag,
830 l_created_by,
831 x_creation_date,
832 x_approval_date,
833 x_comments
834 FROM ra_cm_requests
835 WHERE request_id = p_request_id;
836
837 p_line_credits_flag := x_line_credits_flag;
838
839 /* bug 2290738 : check if x_cm_customer_trx_id is null that means the CM was not created
840 raise an error */
841
842 if x_cm_customer_trx_id is null then
843 x_return_status := FND_API.G_RET_STS_ERROR;
844 FND_MESSAGE.SET_NAME( 'AR','GENERIC_MESSAGE');
845 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Error : Could not find credit memo, unknown failure.');
846 FND_MSG_PUB.ADD;
847 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
848 p_count => x_msg_count,
849 p_data => x_msg_data);
850 return;
851 end if;
852
853 EXCEPTION
854 WHEN no_data_found THEN
855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
856 FND_MESSAGE.SET_NAME( 'AR','AR_CMWF_API_INVALID_REQUEST_ID');
857 FND_MESSAGE.SET_TOKEN('REQUEST_ID',p_request_id);
858 FND_MSG_PUB.ADD;
859
860 -- Display_Parameters;
861
862 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
863 p_count => x_msg_count,
864 p_data => x_msg_data);
865 END;
866
867 IF PG_DEBUG in ('Y', 'C') THEN
868 arp_util.debug ('get_request_status: ' || 'Selecting the meaning for the credit memo dispute from ar_lookups');
869 END IF;
870 SELECT meaning
871 INTO x_reason_meaning
875
872 FROM ar_lookups
873 WHERE lookup_type='CREDIT_MEMO_REASON'
874 AND lookup_code = l_reason_code;
876 IF PG_DEBUG in ('Y', 'C') THEN
877 arp_util.debug ('get_request_status: ' || 'Selecting user information');
878 END IF;
879 IF ( l_created_by <> -1) THEN
880 OPEN per_user_cur;
881 FETCH per_user_cur INTO x_created_by;
882 IF per_user_cur%NOTFOUND THEN
883 x_created_by := null;
884 CLOSE per_user_cur;
885 OPEN fnd_user_cur;
886 FETCH fnd_user_cur INTO x_created_by;
887 IF fnd_user_cur%notfound THEN
888 CLOSE fnd_user_cur;
889 END IF;
890 END IF;
891 END IF;
892
893 -- Getting the line details
894 IF PG_DEBUG in ('Y', 'C') THEN
895 arp_util.debug ('get_request_status: ' || 'Selecting line details');
896 END IF;
897
898 IF p_line_credits_flag = 'Y' THEN
899 SELECT count(*)
900 INTO l_line_count
901 FROM ra_cm_request_lines
902 WHERE request_id = p_request_id;
903
904 OPEN line_det_cur;
905 FOR i in 1..(l_line_count)
906 LOOP
907 FETCH line_det_cur INTO line_det_rec;
908 x_cm_line_tbl(i).customer_trx_line_id := line_det_rec.customer_trx_line_id;
909 x_cm_line_tbl(i).extended_amount := line_det_rec.extended_amount;
910 x_cm_line_tbl(i).quantity_credited := line_det_rec.quantity;
911 x_cm_line_tbl(i).price := line_det_rec.price;
912 END LOOP;
913 CLOSE line_det_cur;
914
915 END IF;
916
917 -- Getting the activities details
918
919 IF PG_DEBUG in ('Y', 'C') THEN
920 arp_util.debug ('get_request_status: ' || 'Getting the activities details');
921 END IF;
922 SELECT count(*)
923 INTO l_count_activities
924 FROM wf_item_activity_statuses ias,
925 wf_process_activities pa,
926 wf_activities_vl ac,
927 wf_activities_vl ap,
928 wf_items i
929 WHERE ias.item_type = 'ARCMREQ'
930 AND ias.item_key = p_request_id
931 AND ias.process_activity = pa.instance_id
932 AND pa.activity_name = ac.name
933 AND pa.activity_item_type = ac.item_type
934 AND pa.process_name = ap.name
935 AND pa.process_item_type = ap.item_type
936 AND i.item_key = ias.item_key
937 AND i.begin_date >=ac.begin_date
938 AND i.begin_date < nvl(ac.end_date, i.begin_date+1);
939
940 OPEN activities_cur;
941 FOR j in 1..l_count_activities
942 LOOP
943 FETCH activities_cur into activities_rec;
944 x_cm_activity_tbl(j).activity_name := activities_rec.activity_name;
945 x_cm_activity_tbl(j).status := activities_rec.status;
946 x_cm_activity_tbl(j).result_code := activities_rec.result_code;
947 x_cm_activity_tbl(j).user := activities_rec.assigned_user;
948 END LOOP;
949 CLOSE activities_cur;
950
951 -- Getting the notes text
952 IF PG_DEBUG in ('Y', 'C') THEN
953 arp_util.debug ('get_request_status: ' || 'Getting the notes text');
954 END IF;
955 SELECT count(*)
956 INTO l_count_notes
957 FROM ar_notes
958 WHERE customer_trx_id = x_customer_trx_id;
959
960 OPEN notes_cur;
961 FOR k in 1..l_count_notes
962 LOOP
963 FETCH notes_cur into notes_rec;
964 x_cm_notes_tbl(k).notes := notes_rec.text;
965 EXIT WHEN notes_cur%notfound;
966 END LOOP;
967 CLOSE notes_cur;
968
969 x_return_status := FND_API.G_RET_STS_SUCCESS;
970 IF PG_DEBUG in ('Y', 'C') THEN
971 arp_util.debug ('AR_CREDIT_MEMO_API_PUB.get_request_status()-');
972 END IF;
973
974 EXCEPTION
975 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
976 IF PG_DEBUG in ('Y', 'C') THEN
977 arp_util.debug('get_request_status : unexpected error');
978 arp_util.debug('get_request_status: ' || SQLERRM, G_MSG_ERROR);
979 END IF;
980 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
981
982 -- Display_Parameters;
983 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
984 p_count => x_msg_count,
985 p_data => x_msg_data);
986
987 WHEN OTHERS THEN
988 /*-------------------------------------------------------+
989 | Handle application errors that result from trapable |
990 | error conditions. The error messages have already |
991 | been put on the error stack. |
992 +-------------------------------------------------------*/
993 IF PG_DEBUG in ('Y', 'C') THEN
994 arp_util.debug ('get_request_status: ' || 'Exception OTHERS');
995 END IF;
996 x_return_status := FND_API.G_RET_STS_ERROR ;
997 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
998 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','AR_CREDIT_MEMO_API_PUB.Get_request_status');
999
1000 -- Display_Parameters;
1001 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1002 p_count => x_msg_count,
1003 p_data => x_msg_data);
1004
1005 END get_request_status;
1006
1007 -- This procedure will print a default message if the calling program does not
1011 PROCEDURE print_default_page IS
1008 -- pass the urls to display the request, transaction and activities information
1009 -- in the notifications.
1010
1012
1013 default_message varchar2(240);
1014
1015 BEGIN
1016
1017 FND_MESSAGE.SET_NAME ('AR','AR_REPORTS_UNAVAILABLE');
1018 default_message := FND_MESSAGE.GET;
1019 HTP.P (default_message);
1020
1021 END print_default_page;
1022
1023 /*NEW ONE OVER LOADED FOR 4606558*/
1024 PROCEDURE validate_request_parameters (
1025 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
1026 p_line_credit_flag IN VARCHAR2,
1027 p_line_amount IN NUMBER,
1028 p_tax_amount IN NUMBER,
1029 p_freight_amount IN NUMBER,
1030 p_cm_reason_code IN VARCHAR2,
1031 p_comments IN VARCHAR2,
1032 p_request_url IN VARCHAR2,
1033 p_transaction_url IN VARCHAR2,
1034 p_trans_act_url IN VARCHAR2,
1035 p_cm_line_tbl IN OUT NOCOPY Cm_Line_Tbl_Type_Cover%type,
1036 p_org_id IN NUMBER DEFAULT NULL,
1037 l_val_return_status OUT NOCOPY VARCHAR2,
1038 /*4556000*/
1039 p_skip_workflow_flag IN VARCHAR2,
1040 p_batch_source_name IN VARCHAR2,
1041 p_attribute_rec IN OUT NOCOPY arw_cmreq_cover.pq_attribute_rec_type,
1042 p_interface_attribute_rec IN OUT NOCOPY arw_cmreq_cover.pq_interface_rec_type,
1043 p_global_attribute_rec IN OUT NOCOPY arw_cmreq_cover.pq_global_attribute_rec_type,
1044 p_dispute_date IN DATE DEFAULT NULL) -- Bug 6358930
1045 IS
1046
1047 -- Local Variables
1048 /*4556000*/
1049 l_ct_trx ra_customer_trx%rowtype;
1050 x_return_status VARCHAR2(1);
1051 p_desc_flex_rec arp_util.attribute_rec_type;
1052 l_copy_inv_tidff_flag VARCHAR2(1);
1053 interface_line_rec interface_line_rec_type;
1054 BEGIN
1055
1056 IF PG_DEBUG in ('Y', 'C') THEN
1057 arp_util.debug ('AR_CREDIT_MEMO_API_PUB.validate_request_parameters(OVERLOADED)+');
1058 END IF;
1059 /*Still call old procedure*/
1060 validate_request_parameters (
1061 p_customer_trx_id => p_customer_trx_id,
1062 p_line_credit_flag => p_line_credit_flag,
1063 p_line_amount => p_line_amount,
1064 p_tax_amount => p_tax_amount,
1065 p_freight_amount => p_freight_amount,
1066 p_cm_reason_code => p_cm_reason_code,
1067 p_comments => p_comments,
1068 p_request_url => p_request_url,
1069 p_transaction_url => p_transaction_url,
1070 p_trans_act_url => p_trans_act_url,
1071 p_cm_line_tbl => p_cm_line_tbl,
1072 p_org_id => p_org_id,
1073 l_val_return_status => l_val_return_status,
1074 p_dispute_date => p_dispute_date);
1075
1076 /*4556000*/
1077 IF l_val_return_status <> FND_API.G_RET_STS_SUCCESS then
1078 l_val_return_status:=FND_API.G_RET_STS_ERROR;
1079 IF PG_DEBUG in ('Y', 'C') THEN
1080 arp_util.debug ('AR_CREDIT_MEMO_API_PUB.Validate_request_parameters returned error');
1081 END IF;
1082 return;
1083 END IF;
1084
1085 select * into l_ct_trx from
1086 ra_customer_trx
1087 where customer_trx_id = p_customer_trx_id;
1088
1089 IF p_line_credit_flag = 'Y' AND NVL(p_skip_workflow_flag,'N')='Y' THEN
1090
1091 FOR i in 1..p_cm_line_tbl.count
1092 LOOP
1093 /*4556000 Flex field validation for line level*/
1094 interface_line_rec.interface_line_context:=p_cm_line_tbl(i).interface_line_context;
1095 interface_line_rec.interface_line_attribute1:=p_cm_line_tbl(i).interface_line_attribute1;
1096 interface_line_rec.interface_line_attribute2:=p_cm_line_tbl(i).interface_line_attribute2;
1097 interface_line_rec.interface_line_attribute3:=p_cm_line_tbl(i).interface_line_attribute3;
1098 interface_line_rec.interface_line_attribute4:=p_cm_line_tbl(i).interface_line_attribute4;
1099 interface_line_rec.interface_line_attribute5:=p_cm_line_tbl(i).interface_line_attribute5;
1100 interface_line_rec.interface_line_attribute6:=p_cm_line_tbl(i).interface_line_attribute6;
1101 interface_line_rec.interface_line_attribute7:=p_cm_line_tbl(i).interface_line_attribute7;
1102 interface_line_rec.interface_line_attribute8:=p_cm_line_tbl(i).interface_line_attribute8;
1103 interface_line_rec.interface_line_attribute9:=p_cm_line_tbl(i).interface_line_attribute9;
1104 interface_line_rec.interface_line_attribute10:=p_cm_line_tbl(i).interface_line_attribute10;
1105 interface_line_rec.interface_line_attribute11:=p_cm_line_tbl(i).interface_line_attribute11;
1106 interface_line_rec.interface_line_attribute12:=p_cm_line_tbl(i).interface_line_attribute12;
1107 interface_line_rec.interface_line_attribute13:=p_cm_line_tbl(i).interface_line_attribute13;
1108 interface_line_rec.interface_line_attribute14:=p_cm_line_tbl(i).interface_line_attribute14;
1109 interface_line_rec.interface_line_attribute15:=p_cm_line_tbl(i).interface_line_attribute15;
1110
1111 Validate_Line_Int_Flex(
1112 p_desc_flex_rec => interface_line_rec,
1113 p_desc_flex_name => 'RA_INTERFACE_LINES',
1114 p_return_status => x_return_status );
1115 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1116 THEN
1117 IF PG_DEBUG in ('Y', 'C') THEN
1121 FND_MESSAGE.SET_TOKEN('CUSTOMER_TRX_LINE_ID',p_cm_line_tbl(i).customer_trx_line_id);
1118 arp_util.debug ('validate_request_parameters:'||'Err. Line Transaction FF Validation');
1119 END IF;
1120 FND_MESSAGE.SET_NAME('AR','AR_INAPI_INVALID_DESC_FLEX');
1122 FND_MSG_PUB.Add;
1123 l_val_return_status := FND_API.G_RET_STS_ERROR ;
1124 return;
1125 END IF;
1126 p_cm_line_tbl(i).interface_line_context:=interface_line_rec.interface_line_context;
1127 p_cm_line_tbl(i).interface_line_attribute1:=interface_line_rec.interface_line_attribute1;
1128 p_cm_line_tbl(i).interface_line_attribute2:=interface_line_rec.interface_line_attribute2;
1129 p_cm_line_tbl(i).interface_line_attribute3:=interface_line_rec.interface_line_attribute3;
1130 p_cm_line_tbl(i).interface_line_attribute4:=interface_line_rec.interface_line_attribute4;
1131 p_cm_line_tbl(i).interface_line_attribute5:=interface_line_rec.interface_line_attribute5;
1132 p_cm_line_tbl(i).interface_line_attribute6:=interface_line_rec.interface_line_attribute6;
1133 p_cm_line_tbl(i).interface_line_attribute7:=interface_line_rec.interface_line_attribute7;
1134 p_cm_line_tbl(i).interface_line_attribute8:=interface_line_rec.interface_line_attribute8;
1135 p_cm_line_tbl(i).interface_line_attribute9:=interface_line_rec.interface_line_attribute9;
1136 p_cm_line_tbl(i).interface_line_attribute10:=interface_line_rec.interface_line_attribute10;
1137 p_cm_line_tbl(i).interface_line_attribute11:=interface_line_rec.interface_line_attribute11;
1138 p_cm_line_tbl(i).interface_line_attribute12:=interface_line_rec.interface_line_attribute12;
1139 p_cm_line_tbl(i).interface_line_attribute13:=interface_line_rec.interface_line_attribute13;
1140 p_cm_line_tbl(i).interface_line_attribute14:=interface_line_rec.interface_line_attribute14;
1141 p_cm_line_tbl(i).interface_line_attribute15:=interface_line_rec.interface_line_attribute15;
1142
1143
1144 p_desc_flex_rec.attribute_category:=p_cm_line_tbl(i).attribute_category;
1145 p_desc_flex_rec.attribute1:=p_cm_line_tbl(i).attribute1;
1146 p_desc_flex_rec.attribute2:=p_cm_line_tbl(i).attribute2;
1147 p_desc_flex_rec.attribute3:=p_cm_line_tbl(i).attribute3;
1148 p_desc_flex_rec.attribute4:=p_cm_line_tbl(i).attribute4;
1149 p_desc_flex_rec.attribute5:=p_cm_line_tbl(i).attribute5;
1150 p_desc_flex_rec.attribute6:=p_cm_line_tbl(i).attribute6;
1151 p_desc_flex_rec.attribute7:=p_cm_line_tbl(i).attribute7;
1152 p_desc_flex_rec.attribute8:=p_cm_line_tbl(i).attribute8;
1153 p_desc_flex_rec.attribute9:=p_cm_line_tbl(i).attribute9;
1154 p_desc_flex_rec.attribute10:=p_cm_line_tbl(i).attribute10;
1155 p_desc_flex_rec.attribute11:=p_cm_line_tbl(i).attribute11;
1156 p_desc_flex_rec.attribute12:=p_cm_line_tbl(i).attribute12;
1157 p_desc_flex_rec.attribute13:=p_cm_line_tbl(i).attribute13;
1158 p_desc_flex_rec.attribute14:=p_cm_line_tbl(i).attribute14;
1159 p_desc_flex_rec.attribute15:=p_cm_line_tbl(i).attribute15;
1160
1161 arp_util.Validate_Desc_Flexfield(
1162 p_desc_flex_rec => p_desc_flex_rec,
1163 p_desc_flex_name => 'RA_CUSTOMER_TRX_LINES',
1164 p_return_status => x_return_status );
1165
1166 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1167 THEN
1168 IF PG_DEBUG in ('Y', 'C') THEN
1169 arp_util.debug ('validate_request_parameters:'||'Err. Line Information FF Validation');
1170 END IF;
1171 FND_MESSAGE.SET_NAME('AR','AR_INAPI_INVALID_DESC_FLEX');
1172 FND_MESSAGE.SET_TOKEN('CUSTOMER_TRX_LINE_ID',p_cm_line_tbl(i).customer_trx_line_id);
1173 FND_MSG_PUB.Add;
1174 l_val_return_status := FND_API.G_RET_STS_ERROR ;
1175 return;
1176 END IF;
1177 p_cm_line_tbl(i).attribute_category:=p_desc_flex_rec.attribute_category;
1178 p_cm_line_tbl(i).attribute1:=p_desc_flex_rec.attribute1;
1179 p_cm_line_tbl(i).attribute2:=p_desc_flex_rec.attribute2;
1180 p_cm_line_tbl(i).attribute3:=p_desc_flex_rec.attribute3;
1181 p_cm_line_tbl(i).attribute4:=p_desc_flex_rec.attribute4;
1182 p_cm_line_tbl(i).attribute5:=p_desc_flex_rec.attribute5;
1183 p_cm_line_tbl(i).attribute6:=p_desc_flex_rec.attribute6;
1184 p_cm_line_tbl(i).attribute7:=p_desc_flex_rec.attribute7;
1185 p_cm_line_tbl(i).attribute8:=p_desc_flex_rec.attribute8;
1186 p_cm_line_tbl(i).attribute9:=p_desc_flex_rec.attribute9;
1187 p_cm_line_tbl(i).attribute10:=p_desc_flex_rec.attribute10;
1188 p_cm_line_tbl(i).attribute11:=p_desc_flex_rec.attribute11;
1189 p_cm_line_tbl(i).attribute12:=p_desc_flex_rec.attribute12;
1190 p_cm_line_tbl(i).attribute13:=p_desc_flex_rec.attribute13;
1191 p_cm_line_tbl(i).attribute14:=p_desc_flex_rec.attribute14;
1192 p_cm_line_tbl(i).attribute15:=p_desc_flex_rec.attribute15;
1193
1194 END LOOP;
1195 END IF;
1196 /*4556000*/
1197 /*Flex field validation incorporated here*/
1198 /* bug fix 5583733, Validate RA_INTERFACE_HEADER only when the user has passed values for the flexfield*/
1199 if p_interface_attribute_rec.interface_header_context is NULL then
1200 p_interface_attribute_rec.interface_header_context:=l_ct_trx.interface_header_context;
1201 p_interface_attribute_rec.interface_header_attribute1:=l_ct_trx.interface_header_attribute1;
1202 p_interface_attribute_rec.interface_header_attribute2:=l_ct_trx.interface_header_attribute2;
1203 p_interface_attribute_rec.interface_header_attribute3:=l_ct_trx.interface_header_attribute3;
1207 p_interface_attribute_rec.interface_header_attribute7:=l_ct_trx.interface_header_attribute7;
1204 p_interface_attribute_rec.interface_header_attribute4:=l_ct_trx.interface_header_attribute4;
1205 p_interface_attribute_rec.interface_header_attribute5:=l_ct_trx.interface_header_attribute5;
1206 p_interface_attribute_rec.interface_header_attribute6:=l_ct_trx.interface_header_attribute6;
1208 p_interface_attribute_rec.interface_header_attribute8:=l_ct_trx.interface_header_attribute8;
1209 p_interface_attribute_rec.interface_header_attribute9:=l_ct_trx.interface_header_attribute9;
1210 p_interface_attribute_rec.interface_header_attribute10:=l_ct_trx.interface_header_attribute10;
1211 p_interface_attribute_rec.interface_header_attribute11:=l_ct_trx.interface_header_attribute11;
1212 p_interface_attribute_rec.interface_header_attribute12:=l_ct_trx.interface_header_attribute12;
1213 p_interface_attribute_rec.interface_header_attribute13:=l_ct_trx.interface_header_attribute13;
1214 p_interface_attribute_rec.interface_header_attribute14:=l_ct_trx.interface_header_attribute14;
1215 p_interface_attribute_rec.interface_header_attribute15:=l_ct_trx.interface_header_attribute15;
1216
1217 ELSE
1218
1219 IF NVL(p_skip_workflow_flag,'N')='Y' THEN
1220 Validate_int_Desc_Flex(
1221 p_desc_flex_rec => p_interface_attribute_rec,
1222 p_desc_flex_name => 'RA_INTERFACE_HEADER',
1223 p_return_status => x_return_status );
1224
1225 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1226 THEN
1227 IF PG_DEBUG in ('Y', 'C') THEN
1228 arp_util.debug ('validate_request_parameters:'||'Err. Invoice Transaction FF Validation');
1229 END IF;
1230 FND_MESSAGE.SET_NAME('AR','AR_INAPI_INVALID_DESC_FLEX');
1231 FND_MESSAGE.SET_TOKEN('CUSTOMER_TRX_ID',p_customer_trx_id);
1232 FND_MSG_PUB.Add;
1233 l_val_return_status := FND_API.G_RET_STS_ERROR ;
1234 return;
1235 END IF;
1236 END IF;
1237 END IF;
1238
1239 BEGIN
1240 SELECT NVL(COPY_INV_TIDFF_TO_CM_FLAG,'N') into l_copy_inv_tidff_flag
1241 FROM ra_batch_sources where name=p_batch_source_name;
1242 EXCEPTION
1243 WHEN NO_DATA_FOUND THEN
1244 BEGIN
1245 SELECT NVL(COPY_INV_TIDFF_TO_CM_FLAG,'N') into l_copy_inv_tidff_flag
1246 FROM ra_batch_sources where batch_source_id=l_ct_trx.batch_source_id;
1247 EXCEPTION
1248 WHEN NO_DATA_FOUND THEN
1249 FND_MESSAGE.SET_NAME('AR','AR_INAPI_INVALID_BATCH_SOURCE');
1250 FND_MESSAGE.SET_TOKEN('CUSTOMER_TRX_ID',p_customer_trx_id);
1251 FND_MSG_PUB.Add;
1252 l_val_return_status := FND_API.G_RET_STS_ERROR ;
1253 return;
1254 END;
1255 END;
1256
1257 p_desc_flex_rec.attribute_category:=p_attribute_rec.attribute_category;
1258 p_desc_flex_rec.attribute1:=p_attribute_rec.attribute1;
1259 p_desc_flex_rec.attribute2:=p_attribute_rec.attribute2;
1260 p_desc_flex_rec.attribute3:=p_attribute_rec.attribute3;
1261 p_desc_flex_rec.attribute4:=p_attribute_rec.attribute4;
1262 p_desc_flex_rec.attribute5:=p_attribute_rec.attribute5;
1263 p_desc_flex_rec.attribute6:=p_attribute_rec.attribute6;
1264 p_desc_flex_rec.attribute7:=p_attribute_rec.attribute7;
1265 p_desc_flex_rec.attribute8:=p_attribute_rec.attribute8;
1266 p_desc_flex_rec.attribute9:=p_attribute_rec.attribute9;
1267 p_desc_flex_rec.attribute10:=p_attribute_rec.attribute10;
1268 p_desc_flex_rec.attribute11:=p_attribute_rec.attribute11;
1269 p_desc_flex_rec.attribute12:=p_attribute_rec.attribute12;
1270 p_desc_flex_rec.attribute13:=p_attribute_rec.attribute13;
1271 p_desc_flex_rec.attribute14:=p_attribute_rec.attribute14;
1272 p_desc_flex_rec.attribute15:=p_attribute_rec.attribute15;
1273
1274 IF p_desc_flex_rec.attribute_category is NULL AND l_copy_inv_tidff_flag='Y' then
1275 p_desc_flex_rec.attribute_category:=l_ct_trx.attribute_category;
1276 p_desc_flex_rec.attribute1:=l_ct_trx.attribute1;
1277 p_desc_flex_rec.attribute2:=l_ct_trx.attribute2;
1278 p_desc_flex_rec.attribute3:=l_ct_trx.attribute3;
1279 p_desc_flex_rec.attribute4:=l_ct_trx.attribute4;
1280 p_desc_flex_rec.attribute5:=l_ct_trx.attribute5;
1281 p_desc_flex_rec.attribute6:=l_ct_trx.attribute6;
1282 p_desc_flex_rec.attribute7:=l_ct_trx.attribute7;
1283 p_desc_flex_rec.attribute8:=l_ct_trx.attribute8;
1284 p_desc_flex_rec.attribute9:=l_ct_trx.attribute9;
1285 p_desc_flex_rec.attribute10:=l_ct_trx.attribute10;
1286 p_desc_flex_rec.attribute11:=l_ct_trx.attribute11;
1287 p_desc_flex_rec.attribute12:=l_ct_trx.attribute12;
1288 p_desc_flex_rec.attribute13:=l_ct_trx.attribute13;
1289 p_desc_flex_rec.attribute14:=l_ct_trx.attribute14;
1290 p_desc_flex_rec.attribute15:=l_ct_trx.attribute15;
1291 END IF;
1292 IF NVL(p_skip_workflow_flag,'N')='Y' THEN
1293 arp_util.Validate_Desc_Flexfield(
1294 p_desc_flex_rec => p_desc_flex_rec,
1295 p_desc_flex_name => 'RA_CUSTOMER_TRX',
1296 p_return_status => x_return_status );
1297
1298 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1299 THEN
1300 IF PG_DEBUG in ('Y', 'C') THEN
1304 FND_MESSAGE.SET_TOKEN('CUSTOMER_TRX_ID',p_customer_trx_id);
1301 arp_util.debug ('validate_request_parameters:'||'Err. Transaction Information FF Validation');
1302 END IF;
1303 FND_MESSAGE.SET_NAME('AR','AR_INAPI_INVALID_DESC_FLEX');
1305 FND_MSG_PUB.Add;
1306 l_val_return_status := FND_API.G_RET_STS_ERROR ;
1307 return;
1308 END IF;
1309 END IF;
1310 p_attribute_rec.attribute_category:=p_desc_flex_rec.attribute_category;
1311 p_attribute_rec.attribute1:=p_desc_flex_rec.attribute1;
1312 p_attribute_rec.attribute2:=p_desc_flex_rec.attribute2;
1313 p_attribute_rec.attribute3:=p_desc_flex_rec.attribute3;
1314 p_attribute_rec.attribute4:=p_desc_flex_rec.attribute4;
1315 p_attribute_rec.attribute5:=p_desc_flex_rec.attribute5;
1316 p_attribute_rec.attribute6:=p_desc_flex_rec.attribute6;
1317 p_attribute_rec.attribute7:=p_desc_flex_rec.attribute7;
1318 p_attribute_rec.attribute8:=p_desc_flex_rec.attribute8;
1319 p_attribute_rec.attribute9:=p_desc_flex_rec.attribute9;
1320 p_attribute_rec.attribute10:=p_desc_flex_rec.attribute10;
1321 p_attribute_rec.attribute11:=p_desc_flex_rec.attribute11;
1322 p_attribute_rec.attribute12:=p_desc_flex_rec.attribute12;
1323 p_attribute_rec.attribute13:=p_desc_flex_rec.attribute13;
1324 p_attribute_rec.attribute14:=p_desc_flex_rec.attribute14;
1325 p_attribute_rec.attribute15:=p_desc_flex_rec.attribute15;
1326
1327 l_val_return_status := FND_API.G_RET_STS_SUCCESS;
1328 IF PG_DEBUG in ('Y', 'C') THEN
1329 arp_util.debug ('AR_CREDIT_MEMO_API_PUB.Validate_request_parameters(OVERLOADED)-');
1330 END IF;
1331
1332 EXCEPTION
1333 WHEN OTHERS THEN
1334 IF PG_DEBUG in ('Y', 'C') THEN
1335 arp_util.debug('Exception : Others in validate_request_parameters(OVERLOADED)');
1336 END IF;
1337 END validate_request_parameters;
1338
1339 /*4556000*/
1340 PROCEDURE Validate_Int_Desc_Flex(
1341 p_desc_flex_rec IN OUT NOCOPY arw_cmreq_cover.pq_interface_rec_type,
1342 p_desc_flex_name IN VARCHAR2,
1343 p_return_status IN OUT NOCOPY varchar2
1344 ) IS
1345
1346 l_flex_name fnd_descriptive_flexs.descriptive_flexfield_name%type;
1347 l_count NUMBER;
1348 l_col_name VARCHAR2(50);
1349 l_flex_exists VARCHAR2(1);
1350 CURSOR desc_flex_exists IS
1351 SELECT 'Y'
1352 FROM fnd_descriptive_flexs
1353 WHERE application_id = 222
1354 and descriptive_flexfield_name = p_desc_flex_name;
1355 BEGIN
1356 IF PG_DEBUG = 'Y' THEN
1357 ar_invoice_utils.debug('' || 'AR_CREDIT_MEMO_API_PUB.Validate_Int_Desc_Flex ()+');
1358 END IF;
1359 p_return_status := FND_API.G_RET_STS_SUCCESS;
1360
1361 OPEN desc_flex_exists;
1362 FETCH desc_flex_exists INTO l_flex_exists;
1363 IF desc_flex_exists%NOTFOUND THEN
1364 CLOSE desc_flex_exists;
1365 p_return_status := FND_API.G_RET_STS_ERROR;
1366 return;
1367 END IF;
1368 CLOSE desc_flex_exists;
1369
1370 IF p_desc_flex_name = 'RA_INTERFACE_HEADER'
1371 THEN
1372 fnd_flex_descval.set_context_value(p_desc_flex_rec.interface_header_context);
1373
1374 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE1',
1375 p_desc_flex_rec.interface_header_attribute1);
1376 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE2',
1377 p_desc_flex_rec.interface_header_attribute2);
1378 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE3',
1379 p_desc_flex_rec.interface_header_attribute3);
1380 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE4',
1381 p_desc_flex_rec.interface_header_attribute4);
1382 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE5',
1383 p_desc_flex_rec.interface_header_attribute5);
1384 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE6',
1385 p_desc_flex_rec.interface_header_attribute6);
1386 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE7',
1387 p_desc_flex_rec.interface_header_attribute7);
1388 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE8',
1389 p_desc_flex_rec.interface_header_attribute8);
1390 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE9',
1391 p_desc_flex_rec.interface_header_attribute9);
1392 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE10',
1393 p_desc_flex_rec.interface_header_attribute10);
1394 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE11',
1395 p_desc_flex_rec.interface_header_attribute11);
1396 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE12',
1397 p_desc_flex_rec.interface_header_attribute12);
1398 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE13',
1399 p_desc_flex_rec.interface_header_attribute13);
1400 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE14',
1401 p_desc_flex_rec.interface_header_attribute14);
1402 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE15',
1403 p_desc_flex_rec.interface_header_attribute15);
1407 THEN
1404
1405
1406 IF ( NOT fnd_flex_descval.validate_desccols('AR',p_desc_flex_name,'I') )
1408 p_return_status := FND_API.G_RET_STS_ERROR;
1409 END IF;
1410
1411 l_count := fnd_flex_descval.segment_count;
1412
1413
1414 FOR i in 1..l_count LOOP
1415 l_col_name := fnd_flex_descval.segment_column_name(i);
1416
1417 IF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE1' THEN
1418 p_desc_flex_rec.INTERFACE_HEADER_attribute1 := fnd_flex_descval.segment_id(i);
1419 ELSIF l_col_name = 'INTERFACE_HEADER_CONTEXT' THEN
1420 p_desc_flex_rec.interface_header_context := fnd_flex_descval.segment_id(i);
1421 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE2' THEN
1422 p_desc_flex_rec.INTERFACE_HEADER_attribute2 := fnd_flex_descval.segment_id(i);
1423 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE3' THEN
1424 p_desc_flex_rec.INTERFACE_HEADER_attribute3 := fnd_flex_descval.segment_id(i);
1425 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE4' THEN
1426 p_desc_flex_rec.INTERFACE_HEADER_attribute4 := fnd_flex_descval.segment_id(i);
1427 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE5' THEN
1428 p_desc_flex_rec.INTERFACE_HEADER_attribute5 := fnd_flex_descval.segment_id(i);
1429 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE6' THEN
1430 p_desc_flex_rec.INTERFACE_HEADER_attribute6 := fnd_flex_descval.segment_id(i);
1431 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE7' THEN
1432 p_desc_flex_rec.INTERFACE_HEADER_attribute7 := fnd_flex_descval.segment_id(i);
1433 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE8' THEN
1434 p_desc_flex_rec.INTERFACE_HEADER_attribute8 := fnd_flex_descval.segment_id(i);
1435 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE9' THEN
1436 p_desc_flex_rec.INTERFACE_HEADER_attribute9 := fnd_flex_descval.segment_id(i);
1437 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE10' THEN
1438 p_desc_flex_rec.INTERFACE_HEADER_attribute10 := fnd_flex_descval.segment_id(i);
1439 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE11' THEN
1440 p_desc_flex_rec.INTERFACE_HEADER_attribute11 := fnd_flex_descval.segment_id(i);
1441 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE12' THEN
1442 p_desc_flex_rec.INTERFACE_HEADER_attribute12 := fnd_flex_descval.segment_id(i);
1443 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE13' THEN
1444 p_desc_flex_rec.INTERFACE_HEADER_attribute13 := fnd_flex_descval.segment_id(i);
1445 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE14' THEN
1446 p_desc_flex_rec.INTERFACE_HEADER_attribute14 := fnd_flex_descval.segment_id(i);
1447 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE15' THEN
1448 p_desc_flex_rec.INTERFACE_HEADER_attribute15 := fnd_flex_descval.segment_id(i);
1449 END IF;
1450
1451 IF i > l_count THEN
1452 EXIT;
1453 END IF;
1454 END LOOP;
1455 END IF;
1456
1457 END Validate_Int_Desc_Flex;
1458
1459 /*4556000*/
1460 PROCEDURE Validate_Line_Int_Flex(
1461 p_desc_flex_rec IN OUT NOCOPY interface_line_rec_type,
1462 p_desc_flex_name IN VARCHAR2,
1463 p_return_status IN OUT NOCOPY varchar2
1464 ) IS
1465
1466 l_flex_name fnd_descriptive_flexs.descriptive_flexfield_name%type;
1467 l_count NUMBER;
1468 l_col_name VARCHAR2(50);
1469 l_flex_exists VARCHAR2(1);
1470 CURSOR desc_flex_exists IS
1471 SELECT 'Y'
1472 FROM fnd_descriptive_flexs
1473 WHERE application_id = 222
1474 and descriptive_flexfield_name = p_desc_flex_name;
1475 BEGIN
1476 IF PG_DEBUG = 'Y' THEN
1477 ar_invoice_utils.debug('' || 'AR_CREDIT_MEMO_API_PUB.Validate_LINE_Int_Flex ()+');
1478 END IF;
1479 p_return_status := FND_API.G_RET_STS_SUCCESS;
1480
1481 OPEN desc_flex_exists;
1482 FETCH desc_flex_exists INTO l_flex_exists;
1483 IF desc_flex_exists%NOTFOUND THEN
1484 CLOSE desc_flex_exists;
1485 p_return_status := FND_API.G_RET_STS_ERROR;
1486 return;
1487 END IF;
1488 CLOSE desc_flex_exists;
1489
1490 IF p_desc_flex_name = 'RA_INTERFACE_LINES'
1491 THEN
1492 fnd_flex_descval.set_context_value(p_desc_flex_rec.interface_line_context);
1493
1494 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE1',
1495 p_desc_flex_rec.interface_line_attribute1);
1496 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE2',
1497 p_desc_flex_rec.interface_line_attribute2);
1498 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE3',
1499 p_desc_flex_rec.interface_line_attribute3);
1500 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE4',
1501 p_desc_flex_rec.interface_line_attribute4);
1502 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE5',
1503 p_desc_flex_rec.interface_line_attribute5);
1504 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE6',
1505 p_desc_flex_rec.interface_line_attribute6);
1506 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE7',
1507 p_desc_flex_rec.interface_line_attribute7);
1511 p_desc_flex_rec.interface_line_attribute9);
1508 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE8',
1509 p_desc_flex_rec.interface_line_attribute8);
1510 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE9',
1512 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE10',
1513 p_desc_flex_rec.interface_line_attribute10);
1514 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE11',
1515 p_desc_flex_rec.interface_line_attribute11);
1516 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE12',
1517 p_desc_flex_rec.interface_line_attribute12);
1518 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE13',
1519 p_desc_flex_rec.interface_line_attribute13);
1520 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE14',
1521 p_desc_flex_rec.interface_line_attribute14);
1522 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE15',
1523 p_desc_flex_rec.interface_line_attribute15);
1524
1525
1526 IF ( NOT fnd_flex_descval.validate_desccols('AR',p_desc_flex_name,'I') )
1527 THEN
1528 p_return_status := FND_API.G_RET_STS_ERROR;
1529 END IF;
1530
1531 l_count := fnd_flex_descval.segment_count;
1532
1533
1534 FOR i in 1..l_count LOOP
1535 l_col_name := fnd_flex_descval.segment_column_name(i);
1536
1537 IF l_col_name = 'INTERFACE_LINE_ATTRIBUTE1' THEN
1538 p_desc_flex_rec.INTERFACE_LINE_attribute1 := fnd_flex_descval.segment_id(i);
1539 ELSIF l_col_name = 'INTERFACE_LINE_CONTEXT' THEN
1540 p_desc_flex_rec.interface_LINE_context := fnd_flex_descval.segment_id(i);
1541 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE2' THEN
1542 p_desc_flex_rec.INTERFACE_LINE_attribute2 := fnd_flex_descval.segment_id(i);
1543 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE3' THEN
1544 p_desc_flex_rec.INTERFACE_LINE_attribute3 := fnd_flex_descval.segment_id(i);
1545 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE4' THEN
1546 p_desc_flex_rec.INTERFACE_LINE_attribute4 := fnd_flex_descval.segment_id(i);
1547 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE5' THEN
1548 p_desc_flex_rec.INTERFACE_LINE_attribute5 := fnd_flex_descval.segment_id(i);
1549 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE6' THEN
1550 p_desc_flex_rec.INTERFACE_LINE_attribute6 := fnd_flex_descval.segment_id(i);
1551 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE7' THEN
1552 p_desc_flex_rec.INTERFACE_LINE_attribute7 := fnd_flex_descval.segment_id(i);
1553 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE8' THEN
1554 p_desc_flex_rec.INTERFACE_LINE_attribute8 := fnd_flex_descval.segment_id(i);
1555 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE9' THEN
1556 p_desc_flex_rec.INTERFACE_LINE_attribute9 := fnd_flex_descval.segment_id(i);
1557 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE10' THEN
1558 p_desc_flex_rec.INTERFACE_LINE_attribute10 := fnd_flex_descval.segment_id(i);
1559 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE11' THEN
1560 p_desc_flex_rec.INTERFACE_LINE_attribute11 := fnd_flex_descval.segment_id(i);
1561 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE12' THEN
1562 p_desc_flex_rec.INTERFACE_LINE_attribute12 := fnd_flex_descval.segment_id(i);
1563 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE13' THEN
1564 p_desc_flex_rec.INTERFACE_LINE_attribute13 := fnd_flex_descval.segment_id(i);
1565 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE14' THEN
1566 p_desc_flex_rec.INTERFACE_LINE_attribute14 := fnd_flex_descval.segment_id(i);
1567 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE15' THEN
1568 p_desc_flex_rec.INTERFACE_LINE_attribute15 := fnd_flex_descval.segment_id(i);
1569 END IF;
1570
1571 IF i > l_count THEN
1572 EXIT;
1573 END IF;
1574 END LOOP;
1575 END IF;
1576
1577 END Validate_Line_Int_Flex;
1578
1579 END AR_CREDIT_MEMO_API_PUB;