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