[Home] [Help]
PACKAGE BODY: APPS.ARW_CMREQ_COVER
Source
1 PACKAGE BODY arw_cmreq_cover AS
2 /* $Header: ARWCMRQB.pls 120.39.12010000.3 2008/09/03 06:42:03 naneja ship $ */
3
4
5 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
6
7 FUNCTION ar_request_cm(
8 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
9 p_line_credits_flag IN ra_cm_requests.line_credits_flag%type,
10 p_line_amount IN number,
11 p_tax_amount IN number,
12 p_freight_amount IN number,
13 p_cm_lines_tbl IN Cm_Line_Tbl_Type_Cover,
14 p_cm_reason_code IN varchar2,
15 p_comments IN varchar2,
16 p_url IN ra_cm_requests.url%TYPE,
17 p_transaction_url IN ra_cm_requests.transaction_url%TYPE,
18 p_trans_act_url IN ra_cm_requests.activities_url%TYPE,
19 p_orig_trx_number IN varchar2,
20 p_tax_ex_cert_num IN varchar2,
21 p_skip_workflow_flag IN VARCHAR2,
22 p_credit_method_installments IN VARCHAR2,
23 p_credit_method_rules IN VARCHAR2,
24 p_batch_source_name IN VARCHAR2,
25 /*4556000-4606558*/
26 pq_attribute_rec IN pq_attribute_rec_type DEFAULT pq_attribute_rec_const,
27 pq_interface_attribute_rec IN pq_interface_rec_type DEFAULT pq_interface_rec_const,
28 pq_global_attribute_rec IN pq_global_attribute_rec_type DEFAULT
29 pq_global_attribute_const,
30 p_dispute_date IN DATE DEFAULT NULL, -- Bug 6358930
31 p_internal_comment IN VARCHAR2 DEFAULT NULL /*7367350*/
32 ) RETURN varchar2
33 IS
34 /* org_id is not needed
35 l_org_id number := 204;*/
36 l_customer_trx_id number(15);
37 l_line_amount number;
38 l_line_credits_flag varchar2(1);
39 l_request_id number;
40 /* Bug 3206020 Changed comments width from 240 to 1760. */
41 l_comments varchar2(1760);
42 l_error_code number;
43 l_error_msg varchar2(2000);
44 l_error_tab arp_trx_validate.Message_Tbl_Type;
45 l_num_lines number;
46 l_total_amount number;
47 l_total_line_amount number;
48 l_dispute_amount number;
49 l_url ra_cm_requests.url%TYPE;
50 l_status varchar2(255);
51 l_profile_value fnd_profile_option_values.profile_option_value%TYPE;
52 /*Bug 5481525 variables to hold status of CM creation*/
53 l_credit_memo_id number;
54 l_result VARCHAR2(10);
55 l_threshold NUMBER;
56 /*4220382 */
57
58 CURSOR ps_cur IS
59 SELECT payment_schedule_id , due_date , dispute_date , amount_in_dispute
60 FROM ar_payment_schedules ps
61 WHERE ps.customer_trx_id = p_customer_trx_id;
62
63 BEGIN
64 IF PG_DEBUG in ('Y', 'C') THEN
65 arp_util.debug('arw_cmreq_cover.ar_request_cm(+)' );
66 END IF;
67
68 /*---------------------+
69 | Set the Org Context |
70 +---------------------*/
71
72 /*fnd_client_info.set_org_context(to_char(l_org_id));*/
73
74 /*---------------------+
75 | Check Exceptions |
76 +---------------------*/
77
78
79 -- Total Credit Memo Header Amount
80 l_total_amount := nvl(p_line_amount,0)+nvl(p_tax_amount,0)
81 + nvl(p_freight_amount,0);
82
83 /* Insert Request information into Candidate table */
84
85 /*-----------------------------------------+
86 | Get the unique identifier for request_id|
87 +-----------------------------------------*/
88
89 select ra_cm_requests_s.nextval
90 into l_request_id
91 from sys.dual;
92
93
94 -- The users of the CMWF API have to pass in the url for the request
95 -- confirmation page, which will be used to display the page from the
96 -- notifications screen in worflow. The request_id has to be a parameter for
97 -- the confirmation page. However, the request_id will not be available to the
98 -- calling program at the time the CMWF API is called and cannot be passed along
99 -- with the url. We need to append the req_id before the url is stored in
100 -- the db
101 l_url := replace (p_url,'req_id=', 'req_id='||l_request_id);
102
103 -- Bug 2105483 : rather then calling arp_global at the start
104 -- of the package, where it can error out NOCOPY since org_id is not yet set,
105 -- do the call right before it is needed
106 arp_global.init_global;
107
108 /*-----------------------------------------+
109 | Insert Record into Request Table |
110 +-----------------------------------------*/
111
112 /*4556000-4606558 added additional columns*/
113 INSERT INTO ra_cm_requests
114 (request_id,
115 customer_trx_id,
116 cm_customer_trx_id,
117 url,
118 line_credits_flag,
119 line_amount,
120 tax_amount,
121 freight_amount,
122 cm_reason_code,
123 comments,
124 status,
125 approval_date,
126 last_update_date,
127 last_updated_by,
128 creation_date,
129 created_by,
130 last_update_login,
131 transaction_url,
132 activities_url,
133 orig_trx_number,
134 tax_ex_cert_num,
135 org_id,
136 INTERFACE_HEADER_CONTEXT,
137 INTERFACE_HEADER_ATTRIBUTE1,
138 INTERFACE_HEADER_ATTRIBUTE2,
139 INTERFACE_HEADER_ATTRIBUTE3,
140 INTERFACE_HEADER_ATTRIBUTE4,
141 INTERFACE_HEADER_ATTRIBUTE5,
142 INTERFACE_HEADER_ATTRIBUTE6,
143 INTERFACE_HEADER_ATTRIBUTE7,
144 INTERFACE_HEADER_ATTRIBUTE8,
145 INTERFACE_HEADER_ATTRIBUTE9,
146 INTERFACE_HEADER_ATTRIBUTE10,
147 INTERFACE_HEADER_ATTRIBUTE11,
148 INTERFACE_HEADER_ATTRIBUTE12,
149 INTERFACE_HEADER_ATTRIBUTE13,
150 INTERFACE_HEADER_ATTRIBUTE14,
151 INTERFACE_HEADER_ATTRIBUTE15,
152 ATTRIBUTE_CATEGORY,
153 ATTRIBUTE1,
154 ATTRIBUTE2,
155 ATTRIBUTE3,
156 ATTRIBUTE4,
157 ATTRIBUTE5,
158 ATTRIBUTE6,
159 ATTRIBUTE7,
160 ATTRIBUTE8,
161 ATTRIBUTE9,
162 ATTRIBUTE10,
163 ATTRIBUTE11,
164 ATTRIBUTE12,
165 ATTRIBUTE13,
166 ATTRIBUTE14,
167 ATTRIBUTE15,
168 GLOBAL_ATTRIBUTE_CATEGORY,
169 GLOBAL_ATTRIBUTE1,
170 GLOBAL_ATTRIBUTE2,
171 GLOBAL_ATTRIBUTE3,
172 GLOBAL_ATTRIBUTE4,
173 GLOBAL_ATTRIBUTE5,
174 GLOBAL_ATTRIBUTE6,
175 GLOBAL_ATTRIBUTE7,
176 GLOBAL_ATTRIBUTE8,
177 GLOBAL_ATTRIBUTE9,
178 GLOBAL_ATTRIBUTE10,
179 GLOBAL_ATTRIBUTE11,
180 GLOBAL_ATTRIBUTE12,
181 GLOBAL_ATTRIBUTE13,
182 GLOBAL_ATTRIBUTE14,
183 GLOBAL_ATTRIBUTE15,
184 GLOBAL_ATTRIBUTE16,
185 GLOBAL_ATTRIBUTE17,
186 GLOBAL_ATTRIBUTE18,
187 GLOBAL_ATTRIBUTE19,
188 GLOBAL_ATTRIBUTE20,
189 GLOBAL_ATTRIBUTE21,
190 GLOBAL_ATTRIBUTE22,
191 GLOBAL_ATTRIBUTE23,
192 GLOBAL_ATTRIBUTE24,
193 GLOBAL_ATTRIBUTE25,
194 GLOBAL_ATTRIBUTE26,
195 GLOBAL_ATTRIBUTE27,
196 GLOBAL_ATTRIBUTE28,
197 GLOBAL_ATTRIBUTE29,
198 GLOBAL_ATTRIBUTE30,
199 DISPUTE_DATE,
200 INTERNAL_COMMENT
201 )
202 VALUES
203 (
204 l_request_id,
205 p_customer_trx_id,
206 NULL,
207 l_url,
208 p_line_credits_flag,
209 p_line_amount,
210 p_tax_amount,
211 p_freight_amount,
212 p_cm_reason_code,
213 p_comments,
214 'PENDING_APPROVAL',
215 NULL,
216 arp_global.last_update_date,
217 arp_global.last_updated_by,
218 arp_global.creation_date,
219 arp_global.created_by,
220 arp_global.last_update_login,
221 p_transaction_url,
222 p_trans_act_url,
223 p_orig_trx_number,
224 p_tax_ex_cert_num,
225 arp_standard.sysparm.org_id,
226 pq_interface_attribute_rec.interface_header_context,
227 pq_interface_attribute_rec.interface_header_attribute1,
228 pq_interface_attribute_rec.interface_header_attribute2,
229 pq_interface_attribute_rec.interface_header_attribute3,
230 pq_interface_attribute_rec.interface_header_attribute4,
231 pq_interface_attribute_rec.interface_header_attribute5,
232 pq_interface_attribute_rec.interface_header_attribute6,
233 pq_interface_attribute_rec.interface_header_attribute7,
234 pq_interface_attribute_rec.interface_header_attribute8,
235 pq_interface_attribute_rec.interface_header_attribute9,
236 pq_interface_attribute_rec.interface_header_attribute10,
237 pq_interface_attribute_rec.interface_header_attribute11,
238 pq_interface_attribute_rec.interface_header_attribute12,
239 pq_interface_attribute_rec.interface_header_attribute13,
240 pq_interface_attribute_rec.interface_header_attribute14,
241 pq_interface_attribute_rec.interface_header_attribute15,
242 pq_attribute_rec.attribute_category,
243 pq_attribute_rec.attribute1,
244 pq_attribute_rec.attribute2,
245 pq_attribute_rec.attribute3,
246 pq_attribute_rec.attribute4,
247 pq_attribute_rec.attribute5,
248 pq_attribute_rec.attribute6,
249 pq_attribute_rec.attribute7,
250 pq_attribute_rec.attribute8,
251 pq_attribute_rec.attribute9,
252 pq_attribute_rec.attribute10,
253 pq_attribute_rec.attribute11,
254 pq_attribute_rec.attribute12,
255 pq_attribute_rec.attribute13,
256 pq_attribute_rec.attribute14,
257 pq_attribute_rec.attribute15,
258 pq_global_attribute_rec.global_attribute_category,
259 pq_global_attribute_rec.global_attribute1,
260 pq_global_attribute_rec.global_attribute2,
261 pq_global_attribute_rec.global_attribute3,
262 pq_global_attribute_rec.global_attribute4,
263 pq_global_attribute_rec.global_attribute5,
264 pq_global_attribute_rec.global_attribute6,
265 pq_global_attribute_rec.global_attribute7,
266 pq_global_attribute_rec.global_attribute8,
267 pq_global_attribute_rec.global_attribute9,
268 pq_global_attribute_rec.global_attribute10,
269 pq_global_attribute_rec.global_attribute11,
270 pq_global_attribute_rec.global_attribute12,
271 pq_global_attribute_rec.global_attribute13,
272 pq_global_attribute_rec.global_attribute14,
273 pq_global_attribute_rec.global_attribute15,
274 pq_global_attribute_rec.global_attribute16,
275 pq_global_attribute_rec.global_attribute17,
276 pq_global_attribute_rec.global_attribute18,
277 pq_global_attribute_rec.global_attribute19,
278 pq_global_attribute_rec.global_attribute20,
279 pq_global_attribute_rec.global_attribute21,
280 pq_global_attribute_rec.global_attribute22,
281 pq_global_attribute_rec.global_attribute23,
282 pq_global_attribute_rec.global_attribute24,
283 pq_global_attribute_rec.global_attribute25,
284 pq_global_attribute_rec.global_attribute26,
285 pq_global_attribute_rec.global_attribute27,
286 pq_global_attribute_rec.global_attribute28,
287 pq_global_attribute_rec.global_attribute29,
288 pq_global_attribute_rec.global_attribute30,
289 p_dispute_date,
290 p_internal_comment
291 );
292
293 IF p_line_credits_flag ='Y' THEN
294 l_num_lines := p_cm_lines_tbl.count;
295
296 For i in 1..l_num_lines
297 LOOP
298 l_total_line_amount := nvl(l_total_line_amount,0)+
299 nvl(p_cm_lines_tbl(i).extended_amount,0);
300 /*4556000-4606558 additional columns added here*/
301 INSERT into ra_cm_request_lines
302 ( request_id,
303 customer_trx_line_id,
304 extended_amount,
305 last_update_date,
306 last_updated_by,
307 creation_date,
308 created_by,
309 last_update_login,
310 quantity,
311 price,
312 org_id,
313 INTERFACE_LINE_CONTEXT,
314 INTERFACE_LINE_ATTRIBUTE1,
315 INTERFACE_LINE_ATTRIBUTE2,
316 INTERFACE_LINE_ATTRIBUTE3,
317 INTERFACE_LINE_ATTRIBUTE4,
318 INTERFACE_LINE_ATTRIBUTE5,
319 INTERFACE_LINE_ATTRIBUTE6,
320 INTERFACE_LINE_ATTRIBUTE7,
321 INTERFACE_LINE_ATTRIBUTE8,
322 INTERFACE_LINE_ATTRIBUTE9,
323 INTERFACE_LINE_ATTRIBUTE10,
324 INTERFACE_LINE_ATTRIBUTE11,
325 INTERFACE_LINE_ATTRIBUTE12,
326 INTERFACE_LINE_ATTRIBUTE13,
327 INTERFACE_LINE_ATTRIBUTE14,
328 INTERFACE_LINE_ATTRIBUTE15,
329 ATTRIBUTE_CATEGORY ,
330 ATTRIBUTE1 ,
331 ATTRIBUTE2 ,
332 ATTRIBUTE3 ,
333 ATTRIBUTE4 ,
334 ATTRIBUTE5 ,
335 ATTRIBUTE6 ,
336 ATTRIBUTE7 ,
337 ATTRIBUTE8 ,
338 ATTRIBUTE9 ,
339 ATTRIBUTE10 ,
340 ATTRIBUTE11 ,
341 ATTRIBUTE12 ,
342 ATTRIBUTE13 ,
343 ATTRIBUTE14 ,
344 ATTRIBUTE15 ,
345 GLOBAL_ATTRIBUTE_CATEGORY ,
346 GLOBAL_ATTRIBUTE1 ,
347 GLOBAL_ATTRIBUTE2 ,
348 GLOBAL_ATTRIBUTE3 ,
349 GLOBAL_ATTRIBUTE4 ,
350 GLOBAL_ATTRIBUTE5 ,
351 GLOBAL_ATTRIBUTE6 ,
352 GLOBAL_ATTRIBUTE7 ,
353 GLOBAL_ATTRIBUTE8 ,
354 GLOBAL_ATTRIBUTE9 ,
355 GLOBAL_ATTRIBUTE10 ,
356 GLOBAL_ATTRIBUTE11 ,
357 GLOBAL_ATTRIBUTE12 ,
358 GLOBAL_ATTRIBUTE13 ,
359 GLOBAL_ATTRIBUTE14 ,
360 GLOBAL_ATTRIBUTE15 ,
361 GLOBAL_ATTRIBUTE16 ,
362 GLOBAL_ATTRIBUTE17 ,
363 GLOBAL_ATTRIBUTE18 ,
364 GLOBAL_ATTRIBUTE19 ,
365 GLOBAL_ATTRIBUTE20
366 )
367 VALUES
368 (l_request_id,
369 p_cm_lines_tbl(i).customer_trx_line_id,
370 p_cm_lines_tbl(i).extended_amount,
371 arp_global.last_update_date,
372 arp_global.last_updated_by,
373 arp_global.creation_date,
374 arp_global.created_by,
375 arp_global.last_update_login,
376 p_cm_lines_tbl(i).quantity_credited,
377 p_cm_lines_tbl(i).price,
378 arp_standard.sysparm.org_id,
379 p_cm_lines_tbl(i).INTERFACE_LINE_CONTEXT,
380 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE1,
381 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE2,
382 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE3,
383 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE4,
384 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE5,
385 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE6,
386 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE7,
387 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE8,
388 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE9,
389 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE10,
390 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE11,
391 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE12,
392 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE13,
393 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE14,
394 p_cm_lines_tbl(i).INTERFACE_LINE_ATTRIBUTE15,
395 p_cm_lines_tbl(i).ATTRIBUTE_CATEGORY ,
396 p_cm_lines_tbl(i).ATTRIBUTE1 ,
397 p_cm_lines_tbl(i).ATTRIBUTE2 ,
398 p_cm_lines_tbl(i).ATTRIBUTE3 ,
399 p_cm_lines_tbl(i).ATTRIBUTE4 ,
400 p_cm_lines_tbl(i).ATTRIBUTE5 ,
401 p_cm_lines_tbl(i).ATTRIBUTE6 ,
402 p_cm_lines_tbl(i).ATTRIBUTE7 ,
403 p_cm_lines_tbl(i).ATTRIBUTE8 ,
404 p_cm_lines_tbl(i).ATTRIBUTE9 ,
405 p_cm_lines_tbl(i).ATTRIBUTE10 ,
406 p_cm_lines_tbl(i).ATTRIBUTE11 ,
407 p_cm_lines_tbl(i).ATTRIBUTE12 ,
408 p_cm_lines_tbl(i).ATTRIBUTE13 ,
409 p_cm_lines_tbl(i).ATTRIBUTE14 ,
410 p_cm_lines_tbl(i).ATTRIBUTE15 ,
411 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE_CATEGORY ,
412 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE1 ,
413 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE2 ,
414 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE3 ,
415 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE4 ,
416 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE5 ,
417 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE6 ,
418 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE7 ,
419 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE8 ,
420 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE9 ,
421 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE10 ,
422 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE11 ,
423 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE12 ,
424 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE13 ,
425 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE14 ,
426 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE15 ,
427 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE16 ,
428 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE17 ,
429 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE18 ,
430 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE19 ,
431 p_cm_lines_tbl(i).GLOBAL_ATTRIBUTE20
432 );
433 END LOOP;
434 END IF;
435
436
437 /* Calculate total Dispute Amount */
438
439 IF p_line_credits_flag ='Y'
440
441 THEN
442 l_dispute_amount := l_total_line_amount;
443 ELSE
444 l_dispute_amount := l_total_amount;
445 END IF;
446
447 UPDATE ra_cm_requests
448 SET total_amount = l_dispute_amount
449 WHERE request_id = l_request_id;
450
451
452 -- Put Invoice in Dispute before instantiation of Workflow Approval
453 /*4220382 */
454
455 IF NVL(p_skip_workflow_flag,'N') = 'N' THEN
456 BEGIN
457 l_dispute_amount := l_dispute_amount * -1; /*4469239 */
458
459 FOR ps_rec IN ps_cur
460 LOOP
461
462 ps_rec.amount_in_dispute := NVL(ps_rec.amount_in_dispute,0) + NVL(l_dispute_amount,0);
463 IF p_dispute_date IS NOT NULL AND
464 p_dispute_date >= NVL(ps_rec.dispute_date, p_dispute_date)
465 THEN
466 ps_rec.dispute_date := p_dispute_date;
467 ELSIF p_dispute_date IS NULL AND
468 (ps_rec.dispute_date IS NULL OR
469 NVL(ps_rec.dispute_date,trunc(sysdate)) <= trunc(sysdate))
470 THEN
471 ps_rec.dispute_date := arp_global.last_update_date;
472 END IF;
473 arp_process_cutil.update_ps
474 (p_ps_id=> ps_rec.payment_schedule_id,
475 p_due_date=> ps_rec.due_date,
476 p_amount_in_dispute=> ps_rec.amount_in_dispute,
477 p_dispute_date=> ps_rec.dispute_date,
478 p_update_dff => 'N',
479 p_attribute_category=>NULL,
480 p_attribute1=>NULL,
481 p_attribute2=>NULL,
482 p_attribute3=>NULL,
483 p_attribute4=>NULL,
484 p_attribute5=>NULL,
485 p_attribute6=>NULL,
486 p_attribute7=>NULL,
487 p_attribute8=>NULL,
488 p_attribute9=>NULL,
489 p_attribute10=>NULL,
490 p_attribute11=>NULL,
491 p_attribute12=>NULL,
492 p_attribute13=>NULL,
493 p_attribute14=>NULL,
494 p_attribute15=>NULL );
495
496 END LOOP;
497 END;
498 END IF;
499
500 IF p_skip_workflow_flag = 'Y' THEN
501 -- CALL CM API DIRECTLY
502 BEGIN
503
504 -- bug 2290738, add p_status to capture status of CM creation
505
506 arw_cmreq_cover.ar_autocreate_cm(
507 p_request_id => l_request_id,
508 p_batch_source_name => p_batch_source_name,
509 p_credit_method_rules => p_credit_method_rules,
510 p_credit_method_installments => p_credit_method_installments,
511 p_error_tab => l_error_tab,
512 p_status => l_status);
513 END;
514
515 /*Bug 5481525 get if CM is created and update status accordingly*/
516 l_credit_memo_id :=-99;
517 begin
518 select cm_customer_trx_id
519 into l_credit_memo_id
520 from ra_cm_requests
521 where request_id = l_request_id;
522 exception
523 when others then
524 l_result := 'FALSE';
525 end;
526
527 update ra_cm_requests
528 set status= DECODE(nvl(l_credit_memo_id,-99),-99,'APPROVED_PEND_COMP','COMPLETE'),
529 approval_date = SYSDATE,
530 last_updated_by = arp_global.last_updated_by,
531 last_update_date = arp_global.last_update_date,
532 last_update_login = arp_global.last_update_login
533 where request_id = l_request_id;
534
535 if l_status is not null then
536 IF PG_DEBUG in ('Y', 'C') THEN
537 arp_util.debug('error encountered in arw_cmreq_cover.ar_autocreate_cm, return -1');
538 arp_util.debug('arw_cmreq_cover.ar_request_cm(-)');
539 END IF;
540 return('-1');
541 end if;
542 ELSE
543 fnd_profile.get('AR_USE_OAM_IN_CMWF', l_profile_value);
544 IF PG_DEBUG in ('Y', 'C') THEN
545 arp_util.debug('arw_cmreq_cover.ar_request_cm(-)' || l_profile_value);
546 END IF;
547 IF (l_profile_value = 'Y') Then
548 BEGIN
549 l_threshold := WF_ENGINE.threshold;
550 WF_ENGINE.threshold :=50;
551 wf_engine.createprocess('ARAMECM', l_request_id, 'CMREQ_APPROVAL');
552 wf_engine.startprocess ('ARAMECM', l_request_id);
553 WF_ENGINE.threshold :=l_threshold;
554 END;
555 ELSE
556 BEGIN
557 l_threshold := WF_ENGINE.threshold;
558 WF_ENGINE.threshold :=50;
559 wf_engine.createprocess('ARCMREQ', l_request_id, 'CMREQ_APPROVAL');
560 wf_engine.startprocess ('ARCMREQ', l_request_id);
561 WF_ENGINE.threshold :=l_threshold;
562 END;
563 END IF;
564
565 END IF;
566
567 IF PG_DEBUG in ('Y', 'C') THEN
568 arp_util.debug('arw_cmreq_cover.ar_request_cm(-)');
569 END IF;
570 RETURN l_request_id;
571
572 EXCEPTION
573 WHEN OTHERS THEN
574 return('-1'); /*Bug3041195*/
575 END ar_request_cm;
576
577 -- bug 2290738 : added p_status to capture error status
578
579 PROCEDURE ar_autocreate_cm(
580 p_request_id IN ra_cm_requests.request_id%type,
581 p_batch_source_name IN ra_batch_sources.name%type,
582 p_credit_method_rules IN varchar2,
583 p_credit_method_installments IN varchar2,
584 p_error_tab OUT NOCOPY arp_trx_validate.Message_Tbl_Type,
585 p_status OUT NOCOPY varchar2)
586 IS
587
588 -- declare Local Variables
589
590 l_customer_trx_id number;
591 l_cm_customer_trx_id number;
592 l_line_credits_flag varchar2(1);
593 l_line_amount number;
594 l_tax_amount number;
595 l_freight_amount number;
596 l_reason_code varchar2(30);
597 /* Bug 3206020 Changed comments width from 240 to 1760. */
598 l_comments varchar2(1760);
599 l_batch_source_id number;
600 l_cm_cust_trx_type_id number;
601 l_batch_source_name varchar2(30);
602 l_trx_batch_rec ra_batches%rowtype;
603 l_trx_header_rec ra_customer_trx%rowtype;
604 l_cm_header_rec ra_customer_trx%rowtype;
605 p_cm_header_rec ra_customer_trx%rowtype;
606 l_trx_rec_gl_date DATE;
607 l_status varchar2(255);
608 l_trx_number ra_customer_trx.trx_number%type;
609 l_computed_tax_percent number;
610 l_computed_tax_amount ra_customer_trx_lines.extended_amount%type;
611 -- TDEY : bug 1272415 changed type of l_credit_line_table
612 -- to remove trx api dependency
613 l_credit_line_table arw_cm_cover.credit_lines_table_type;
614 i number :=0;
615
616 /*4606558*/
617 pqa_attribute_rec pq_attribute_rec_type;
618 pqa_interface_attribute_rec pq_interface_rec_type;
619 pqa_global_attribute_rec pq_global_attribute_rec_type;
620
621 CURSOR line_cur IS
622 SELECT customer_trx_line_id, extended_amount, quantity, price,
623 INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE2,
624 INTERFACE_LINE_ATTRIBUTE3, INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5,
625 INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7, INTERFACE_LINE_ATTRIBUTE8,
626 INTERFACE_LINE_ATTRIBUTE9, INTERFACE_LINE_ATTRIBUTE10, INTERFACE_LINE_ATTRIBUTE11,
627 INTERFACE_LINE_ATTRIBUTE12, INTERFACE_LINE_ATTRIBUTE13, INTERFACE_LINE_ATTRIBUTE14,
628 INTERFACE_LINE_ATTRIBUTE15, ATTRIBUTE_CATEGORY , ATTRIBUTE1 ,
629 ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 ,
630 ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 ,
631 ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10 ,
632 ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13 ,
633 ATTRIBUTE14 , ATTRIBUTE15 , GLOBAL_ATTRIBUTE_CATEGORY ,
634 GLOBAL_ATTRIBUTE1 , GLOBAL_ATTRIBUTE2 , GLOBAL_ATTRIBUTE3 ,
635 GLOBAL_ATTRIBUTE4 , GLOBAL_ATTRIBUTE5 , GLOBAL_ATTRIBUTE6 ,
636 GLOBAL_ATTRIBUTE7 , GLOBAL_ATTRIBUTE8 , GLOBAL_ATTRIBUTE9 ,
637 GLOBAL_ATTRIBUTE10 , GLOBAL_ATTRIBUTE11 , GLOBAL_ATTRIBUTE12 ,
638 GLOBAL_ATTRIBUTE13 , GLOBAL_ATTRIBUTE14 , GLOBAL_ATTRIBUTE15 ,
639 GLOBAL_ATTRIBUTE16 , GLOBAL_ATTRIBUTE17 , GLOBAL_ATTRIBUTE18 ,
640 GLOBAL_ATTRIBUTE19 , GLOBAL_ATTRIBUTE20
641 FROM ra_cm_request_lines
642 WHERE request_id=p_request_id;
643
644 BEGIN
645
646 IF PG_DEBUG in ('Y', 'C') THEN
647 arp_util.debug('arw_cmreq_cover.ar_autocreate_cm(+)');
648 arp_util.debug( 'in parameters are : ');
649 arp_util.debug( 'p_request_id = ' || to_char(p_request_id));
650 arp_util.debug( 'p_batch_source_name = ' || p_batch_source_name);
651 arp_util.debug( 'p_credit_method_rules = ' || p_credit_method_rules);
652 arp_util.debug( 'p_credit_method_installments = ' || p_credit_method_installments);
653 END IF;
654
655 /* 5885313 - Initialize cached values in pls packages */
656 arp_global.init_global(mo_global.GET_CURRENT_ORG_ID);
657 ARP_STANDARD.INIT_STANDARD(mo_global.GET_CURRENT_ORG_ID);
658 arp_cache_util.refresh_cache;
659 /*----------------------------------------------+
660 |Get request information from CM request table|
661 +----------------------------------------------*/
662
663 -- Bug # 983278
664
665 select customer_trx_id,
666 line_credits_flag,
667 line_amount,
668 tax_amount,
669 freight_amount,
670 cm_reason_code,
671 comments ,
672 /*4556000-4606558 added few columns in our select statement*/
673 INTERFACE_HEADER_CONTEXT, INTERFACE_HEADER_ATTRIBUTE1, INTERFACE_HEADER_ATTRIBUTE2,
674 INTERFACE_HEADER_ATTRIBUTE3,INTERFACE_HEADER_ATTRIBUTE4,INTERFACE_HEADER_ATTRIBUTE5,
675 INTERFACE_HEADER_ATTRIBUTE6,INTERFACE_HEADER_ATTRIBUTE7,INTERFACE_HEADER_ATTRIBUTE8,
676 INTERFACE_HEADER_ATTRIBUTE9,INTERFACE_HEADER_ATTRIBUTE10,INTERFACE_HEADER_ATTRIBUTE11,
677 INTERFACE_HEADER_ATTRIBUTE12,INTERFACE_HEADER_ATTRIBUTE13,INTERFACE_HEADER_ATTRIBUTE14,
678 INTERFACE_HEADER_ATTRIBUTE15, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
679 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
680 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, GLOBAL_ATTRIBUTE_CATEGORY,
681 GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE3, GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5,
682 GLOBAL_ATTRIBUTE6, GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9,
683 GLOBAL_ATTRIBUTE10, GLOBAL_ATTRIBUTE11, GLOBAL_ATTRIBUTE12, GLOBAL_ATTRIBUTE13,
684 GLOBAL_ATTRIBUTE14, GLOBAL_ATTRIBUTE15, GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE17,
685 GLOBAL_ATTRIBUTE18, GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20, GLOBAL_ATTRIBUTE21,
686 GLOBAL_ATTRIBUTE22, GLOBAL_ATTRIBUTE23, GLOBAL_ATTRIBUTE24, GLOBAL_ATTRIBUTE25,
687 GLOBAL_ATTRIBUTE26, GLOBAL_ATTRIBUTE27, GLOBAL_ATTRIBUTE28, GLOBAL_ATTRIBUTE29,
688 GLOBAL_ATTRIBUTE30
689 into l_customer_trx_id,l_line_credits_flag, l_line_amount,l_tax_amount,l_freight_amount,l_reason_code,
690 l_comments,
691 /*4556000-4606558 added few columns*/
692 pqa_interface_attribute_rec.interface_header_context,
693 pqa_interface_attribute_rec.interface_header_attribute1,
694 pqa_interface_attribute_rec.interface_header_attribute2,
695 pqa_interface_attribute_rec.interface_header_attribute3,
696 pqa_interface_attribute_rec.interface_header_attribute4,
697 pqa_interface_attribute_rec.interface_header_attribute5,
698 pqa_interface_attribute_rec.interface_header_attribute6,
699 pqa_interface_attribute_rec.interface_header_attribute7,
700 pqa_interface_attribute_rec.interface_header_attribute8,
701 pqa_interface_attribute_rec.interface_header_attribute9,
702 pqa_interface_attribute_rec.interface_header_attribute10,
703 pqa_interface_attribute_rec.interface_header_attribute11,
704 pqa_interface_attribute_rec.interface_header_attribute12,
705 pqa_interface_attribute_rec.interface_header_attribute13,
706 pqa_interface_attribute_rec.interface_header_attribute14,
707 pqa_interface_attribute_rec.interface_header_attribute15,
708 pqa_attribute_rec.attribute_category, pqa_attribute_rec.attribute1,
709 pqa_attribute_rec.attribute2, pqa_attribute_rec.attribute3, pqa_attribute_rec.attribute4,
710 pqa_attribute_rec.attribute5,pqa_attribute_rec.attribute6, pqa_attribute_rec.attribute7,
711 pqa_attribute_rec.attribute8, pqa_attribute_rec.attribute9,pqa_attribute_rec.attribute10,
712 pqa_attribute_rec.attribute11,pqa_attribute_rec.attribute12,pqa_attribute_rec.attribute13,
713 pqa_attribute_rec.attribute14, pqa_attribute_rec.attribute15,
714 pqa_global_attribute_rec.global_attribute_category, pqa_global_attribute_rec.global_attribute1,
715 pqa_global_attribute_rec.global_attribute2, pqa_global_attribute_rec.global_attribute3,
716 pqa_global_attribute_rec.global_attribute4, pqa_global_attribute_rec.global_attribute5,
717 pqa_global_attribute_rec.global_attribute6, pqa_global_attribute_rec.global_attribute7,
718 pqa_global_attribute_rec.global_attribute8, pqa_global_attribute_rec.global_attribute9,
719 pqa_global_attribute_rec.global_attribute10, pqa_global_attribute_rec.global_attribute11,
720 pqa_global_attribute_rec.global_attribute12, pqa_global_attribute_rec.global_attribute13,
721 pqa_global_attribute_rec.global_attribute14, pqa_global_attribute_rec.global_attribute15,
722 pqa_global_attribute_rec.global_attribute16, pqa_global_attribute_rec.global_attribute17,
723 pqa_global_attribute_rec.global_attribute18, pqa_global_attribute_rec.global_attribute19,
724 pqa_global_attribute_rec.global_attribute20, pqa_global_attribute_rec.global_attribute21,
725 pqa_global_attribute_rec.global_attribute22, pqa_global_attribute_rec.global_attribute23,
726 pqa_global_attribute_rec.global_attribute24, pqa_global_attribute_rec.global_attribute25,
727 pqa_global_attribute_rec.global_attribute26, pqa_global_attribute_rec.global_attribute27,
728 pqa_global_attribute_rec.global_attribute28, pqa_global_attribute_rec.global_attribute29,
729 pqa_global_attribute_rec.global_attribute30
730 from ra_cm_requests
731 where request_id = p_request_id;
732 --
733 -- TDEY 09/14/99 : defaulting l_computed_tax_amount to l_tax_amount
734 -- l_tax_amount is the amount of tax credit being
735 -- requested by the user
736 --
737 l_computed_tax_amount := l_tax_amount ;
738
739 /* Bug 2637404 : retrieve fields that should default from trx being
740 credited */
741
742 select *
743 into l_trx_header_rec
744 from ra_customer_trx
745 where customer_trx_id = l_customer_trx_id;
746
747 /* 5041175 - obsoleting ar_transaction_pub and associated
748 routines. Replaced with inline sql logic to fetch
749 batch source */
750 /*6010707 As per doc when using AME the batch source name can be null*/
751 IF NOT (nvl(fnd_profile.value('AR_USE_OAM_IN_CMWF'),'N') = 'Y' and p_batch_source_name is NULL ) THEN
752 select batch_source_id
753 into l_batch_source_id
754 from ra_batch_sources
755 where name = p_batch_source_name
756 and org_id = l_trx_header_rec.org_id;
757 END IF;
758
759 IF l_line_credits_flag = 'N' THEN
760
761 /*------------------------------------------------------------------------
762 Bug # 983278
763
764 TDEY 09/14/99 : Need to pass in p_line_amount => l_line_amount,
765 p_freight_amount => l_freight_amount,
766 Currently it is passing NULL which means we are always seeking a 100%
767 credit. This is obviuosly not true.
768
769
770 TDEY 09/09/29 : For both header and line level credit_transaction,
771 need to add p_errors => p_error_tab,
772 Without this, all error messages are basically evaporating into thin air.
773
774
775 TDEY 02/23/00 : Bug 1272415 : replaced calls to ar_transaction_pub.
776 credit_transaction
777 with arw_cm_cover.create_header_cm
778
779 VCRISOST 09/18/02 : Bug 2507329/2580574 : instead of NULL, pass actual
780 values to p_reason_code and p_internal_notes
781
782 VCRISOST 10/08/02 : Bug 2609465 : l_comments should populate p_comments
783 rather than p_internal_notes
784
785 Bug 2637404 : pass attribute* / interface_header_attribute* from transaction
786 being credited to the credit memo
787 -------------------------------------------------------------------------- */
788 arw_cm_cover.create_header_cm(
789 p_prev_customer_trx_id => l_customer_trx_id ,
790 p_batch_id => NULL,
791 p_trx_date => NULL,
792 p_gl_date => NULL,
793 p_complete_flag => 'Y',
794 p_batch_source_id => l_batch_source_id,
795 p_cust_trx_type_id => NULL,
796 p_currency_code => NULL,
797 p_exchange_date => NULL,
798 p_exchange_rate_type => NULL,
799 p_exchange_rate => NULL,
800 p_invoicing_rule_id => NULL,
801 p_method_for_rules => p_credit_method_rules,
802 p_split_term_method => p_credit_method_installments,
803 p_initial_customer_trx_id => l_trx_header_rec.initial_customer_trx_id,
804 p_primary_salesrep_id => NULL,
805 p_bill_to_customer_id => NULL,
806 p_bill_to_address_id => NULL,
807 p_bill_to_site_use_id => NULL,
808 p_bill_to_contact_id => NULL,
809 p_ship_to_customer_id => NULL,
810 p_ship_to_address_id => NULL,
811 p_ship_to_site_use_id => NULL,
812 p_ship_to_contact_id => NULL,
813 p_receipt_method_id => NULL,
814 p_paying_customer_id => NULL,
815 p_paying_site_use_id => NULL,
816 p_customer_bank_account_id => NULL,
817 p_printing_option => NULL,
818 p_printing_last_printed => NULL,
819 p_printing_pending => NULL,
820 p_doc_sequence_value => NULL,
821 p_doc_sequence_id => NULL,
822 p_reason_code => l_reason_code,
823 p_customer_reference => NULL,
824 p_customer_reference_date => NULL,
825 p_internal_notes => NULL,
826 p_set_of_books_id => NULL,
827 p_created_from => NULL,
828 p_old_trx_number => NULL,
829 /*4606558*/
830 p_attribute_category => pqa_attribute_rec.attribute_category,
831 p_attribute1 => pqa_attribute_rec.attribute1,
832 p_attribute2 => pqa_attribute_rec.attribute2,
833 p_attribute3 => pqa_attribute_rec.attribute3,
834 p_attribute4 => pqa_attribute_rec.attribute4,
835 p_attribute5 => pqa_attribute_rec.attribute5,
836 p_attribute6 => pqa_attribute_rec.attribute6,
837 p_attribute7 => pqa_attribute_rec.attribute7,
838 p_attribute8 => pqa_attribute_rec.attribute8,
839 p_attribute9 => pqa_attribute_rec.attribute9,
840 p_attribute10 => pqa_attribute_rec.attribute10,
841 p_attribute11 => pqa_attribute_rec.attribute11,
842 p_attribute12 => pqa_attribute_rec.attribute12,
843 p_attribute13 => pqa_attribute_rec.attribute13,
844 p_attribute14 => pqa_attribute_rec.attribute14,
845 p_attribute15 => pqa_attribute_rec.attribute15,
846 p_interface_header_context => pqa_interface_attribute_rec.interface_header_context,
847 p_interface_header_attribute1 => pqa_interface_attribute_rec.interface_header_attribute1,
848 p_interface_header_attribute2 => pqa_interface_attribute_rec.interface_header_attribute2,
849 p_interface_header_attribute3 => pqa_interface_attribute_rec.interface_header_attribute3,
850 p_interface_header_attribute4 => pqa_interface_attribute_rec.interface_header_attribute4,
851 p_interface_header_attribute5 => pqa_interface_attribute_rec.interface_header_attribute5,
852 p_interface_header_attribute6 => pqa_interface_attribute_rec.interface_header_attribute6,
853 p_interface_header_attribute7 => pqa_interface_attribute_rec.interface_header_attribute7,
854 p_interface_header_attribute8 => pqa_interface_attribute_rec.interface_header_attribute8,
855 p_interface_header_attribute9 => pqa_interface_attribute_rec.interface_header_attribute9,
856 p_interface_header_attribute10 => pqa_interface_attribute_rec.interface_header_attribute10,
857 p_interface_header_attribute11 => pqa_interface_attribute_rec.interface_header_attribute11,
858 p_interface_header_attribute12 => pqa_interface_attribute_rec.interface_header_attribute12,
859 p_interface_header_attribute13 => pqa_interface_attribute_rec.interface_header_attribute13,
860 p_interface_header_attribute14 => pqa_interface_attribute_rec.interface_header_attribute14,
861 p_interface_header_attribute15 => pqa_interface_attribute_rec.interface_header_attribute15,
862 p_default_ussgl_trx_code => NULL,
863 p_line_percent => NULL,
864 p_freight_percent => NULL,
865 p_line_amount => l_line_amount,
866 p_freight_amount => l_freight_amount,
867 p_compute_tax => 'N', -- Bug 3152685.
868 p_comments => l_comments,
869 p_customer_trx_id => l_cm_customer_trx_id,
870 p_trx_number => l_trx_number,
871 p_computed_tax_percent => l_computed_tax_percent,
872 p_computed_tax_amount => l_computed_tax_amount,
873 p_errors => p_error_tab,
874 p_status => p_status,
875 p_purchase_order => l_trx_header_rec.purchase_order,
876 p_purchase_order_revision => l_trx_header_rec.purchase_order_revision,
877 p_purchase_order_date => l_trx_header_rec.purchase_order_date,
878 p_legal_entity_id => l_trx_header_rec.legal_entity_id,
879 /*4556000-4606558*/
880 p_global_attribute_category => pqa_global_attribute_rec.global_attribute_category,
881 p_global_attribute1 => pqa_global_attribute_rec.global_attribute1,
882 p_global_attribute2 => pqa_global_attribute_rec.global_attribute2,
883 p_global_attribute3 => pqa_global_attribute_rec.global_attribute3,
884 p_global_attribute4 => pqa_global_attribute_rec.global_attribute4,
885 p_global_attribute5 => pqa_global_attribute_rec.global_attribute5,
886 p_global_attribute6 => pqa_global_attribute_rec.global_attribute6,
887 p_global_attribute7 => pqa_global_attribute_rec.global_attribute7,
888 p_global_attribute8 => pqa_global_attribute_rec.global_attribute8,
889 p_global_attribute9 => pqa_global_attribute_rec.global_attribute9,
890 p_global_attribute10 => pqa_global_attribute_rec.global_attribute10,
891 p_global_attribute11 => pqa_global_attribute_rec.global_attribute11,
892 p_global_attribute12 => pqa_global_attribute_rec.global_attribute12,
893 p_global_attribute13 => pqa_global_attribute_rec.global_attribute13,
894 p_global_attribute14 => pqa_global_attribute_rec.global_attribute14,
895 p_global_attribute15 => pqa_global_attribute_rec.global_attribute15,
896 p_global_attribute16 => pqa_global_attribute_rec.global_attribute16,
897 p_global_attribute17 => pqa_global_attribute_rec.global_attribute17,
898 p_global_attribute18 => pqa_global_attribute_rec.global_attribute18,
899 p_global_attribute19 => pqa_global_attribute_rec.global_attribute19,
900 p_global_attribute20 => pqa_global_attribute_rec.global_attribute20,
901 p_global_attribute21 => pqa_global_attribute_rec.global_attribute21,
902 p_global_attribute22 => pqa_global_attribute_rec.global_attribute22,
903 p_global_attribute23 => pqa_global_attribute_rec.global_attribute23,
904 p_global_attribute24 => pqa_global_attribute_rec.global_attribute24,
905 p_global_attribute25 => pqa_global_attribute_rec.global_attribute25,
906 p_global_attribute26 => pqa_global_attribute_rec.global_attribute26,
907 p_global_attribute27 => pqa_global_attribute_rec.global_attribute27,
908 p_global_attribute28 => pqa_global_attribute_rec.global_attribute28,
909 p_global_attribute29 => pqa_global_attribute_rec.global_attribute29,
910 p_global_attribute30 => pqa_global_attribute_rec.global_attribute30
911 );
912
913 ELSIF l_line_credits_flag = 'Y' THEN
914 OPEN line_cur;
915 LOOP
916 EXIT WHEN line_cur%NOTFOUND;
917 i := i+1;
918 /*4606558*/
919 FETCH line_cur INTO l_credit_line_table(i).previous_customer_trx_line_id,
920 l_credit_line_table(i).extended_amount,
921 l_credit_line_table(i).quantity_credited,
922 l_credit_line_table(i).unit_selling_price,
923 l_credit_line_table(i).INTERFACE_LINE_CONTEXT, l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE1,
924 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE2,
925 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE3,
926 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE4,
927 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE5,
928 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE6,
929 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE7,
930 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE8,
931 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE9,
932 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE10,
933 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE11,
934 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE12,
935 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE13,
936 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE14,
937 l_credit_line_table(i).INTERFACE_LINE_ATTRIBUTE15,
938 l_credit_line_table(i).ATTRIBUTE_CATEGORY ,
939 l_credit_line_table(i).ATTRIBUTE1 ,
940 l_credit_line_table(i).ATTRIBUTE2 ,
941 l_credit_line_table(i).ATTRIBUTE3 ,
942 l_credit_line_table(i).ATTRIBUTE4 ,
943 l_credit_line_table(i).ATTRIBUTE5 ,
944 l_credit_line_table(i).ATTRIBUTE6 ,
945 l_credit_line_table(i).ATTRIBUTE7 ,
946 l_credit_line_table(i).ATTRIBUTE8 ,
947 l_credit_line_table(i).ATTRIBUTE9 ,
948 l_credit_line_table(i).ATTRIBUTE10 ,
949 l_credit_line_table(i).ATTRIBUTE11 ,
950 l_credit_line_table(i).ATTRIBUTE12 ,
951 l_credit_line_table(i).ATTRIBUTE13 ,
952 l_credit_line_table(i).ATTRIBUTE14 ,
953 l_credit_line_table(i).ATTRIBUTE15 ,
954 l_credit_line_table(i).GLOBAL_ATTRIBUTE_CATEGORY ,
955 l_credit_line_table(i).GLOBAL_ATTRIBUTE1 ,
956 l_credit_line_table(i).GLOBAL_ATTRIBUTE2 ,
957 l_credit_line_table(i).GLOBAL_ATTRIBUTE3 ,
958 l_credit_line_table(i).GLOBAL_ATTRIBUTE4 ,
959 l_credit_line_table(i).GLOBAL_ATTRIBUTE5 ,
960 l_credit_line_table(i).GLOBAL_ATTRIBUTE6 ,
961 l_credit_line_table(i).GLOBAL_ATTRIBUTE7 ,
962 l_credit_line_table(i).GLOBAL_ATTRIBUTE8 ,
963 l_credit_line_table(i).GLOBAL_ATTRIBUTE9 ,
964 l_credit_line_table(i).GLOBAL_ATTRIBUTE10 ,
965 l_credit_line_table(i).GLOBAL_ATTRIBUTE11 ,
966 l_credit_line_table(i).GLOBAL_ATTRIBUTE12 ,
967 l_credit_line_table(i).GLOBAL_ATTRIBUTE13 ,
968 l_credit_line_table(i).GLOBAL_ATTRIBUTE14 ,
969 l_credit_line_table(i).GLOBAL_ATTRIBUTE15 ,
970 l_credit_line_table(i).GLOBAL_ATTRIBUTE16 ,
971 l_credit_line_table(i).GLOBAL_ATTRIBUTE17 ,
972 l_credit_line_table(i).GLOBAL_ATTRIBUTE18 ,
973 l_credit_line_table(i).GLOBAL_ATTRIBUTE19 ,
974 l_credit_line_table(i).GLOBAL_ATTRIBUTE20 ;
975 END LOOP;
976 CLOSE line_cur;
977
978 /*
979 TDEY 02/23/00 : Bug 1199202 : replaced calls to ar_transaction_pub.
980 credit_transaction
981 with arw_cm_cover.create_line_cm
982
983 VCRISOST 09/18/02 : Bug 2507329/2580574 : instead of NULL, pass actual
984 values to p_reason_code and p_internal_notes
985 VCRISOST 10/08/02 : Bug 2609465 : l_comments should populate p_comments
986 rather than p_internal_notes
987
988 Bug 2637404 : pass attribute* / interface_header_attribute* from transaction
989 being credited to the credit memo
990 */
991
992 arw_cm_cover.create_line_cm(
993 p_prev_customer_trx_id => l_customer_trx_id ,
994 p_batch_id => NULL,
995 p_trx_date => NULL,
996 p_gl_date => NULL,
997 p_complete_flag => 'Y',
998 p_batch_source_id => l_batch_source_id,
999 p_cust_trx_type_id => NULL,
1000 p_currency_code => NULL,
1001 p_exchange_date => NULL,
1002 p_exchange_rate_type => NULL,
1003 p_exchange_rate => NULL,
1004 p_invoicing_rule_id => NULL,
1005 p_method_for_rules => p_credit_method_rules,
1006 p_split_term_method => p_credit_method_installments,
1007 p_initial_customer_trx_id => l_trx_header_rec.initial_customer_trx_id,
1008 p_primary_salesrep_id => NULL,
1009 p_bill_to_customer_id => NULL,
1010 p_bill_to_address_id => NULL,
1011 p_bill_to_site_use_id => NULL,
1012 p_bill_to_contact_id => NULL,
1013 p_ship_to_customer_id => NULL,
1014 p_ship_to_address_id => NULL,
1015 p_ship_to_site_use_id => NULL,
1016 p_ship_to_contact_id => NULL,
1017 p_receipt_method_id => NULL,
1018 p_paying_customer_id => NULL,
1019 p_paying_site_use_id => NULL,
1020 p_customer_bank_account_id => NULL,
1021 p_printing_option => NULL,
1022 p_printing_last_printed => NULL,
1023 p_printing_pending => NULL,
1024 p_doc_sequence_value => NULL,
1025 p_doc_sequence_id => NULL,
1026 p_reason_code => l_reason_code,
1027 p_customer_reference => NULL,
1028 p_customer_reference_date => NULL,
1029 p_internal_notes => NULL,
1030 p_set_of_books_id => NULL,
1031 p_created_from => NULL,
1032 p_old_trx_number => NULL,
1033 /*4606558*/
1034 p_attribute_category => pqa_attribute_rec.attribute_category,
1035 p_attribute1 => pqa_attribute_rec.attribute1,
1036 p_attribute2 => pqa_attribute_rec.attribute2,
1037 p_attribute3 => pqa_attribute_rec.attribute3,
1038 p_attribute4 => pqa_attribute_rec.attribute4,
1039 p_attribute5 => pqa_attribute_rec.attribute5,
1040 p_attribute6 => pqa_attribute_rec.attribute6,
1041 p_attribute7 => pqa_attribute_rec.attribute7,
1042 p_attribute8 => pqa_attribute_rec.attribute8,
1043 p_attribute9 => pqa_attribute_rec.attribute9,
1044 p_attribute10 => pqa_attribute_rec.attribute10,
1045 p_attribute11 => pqa_attribute_rec.attribute11,
1046 p_attribute12 => pqa_attribute_rec.attribute12,
1047 p_attribute13 => pqa_attribute_rec.attribute13,
1048 p_attribute14 => pqa_attribute_rec.attribute14,
1049 p_attribute15 => pqa_attribute_rec.attribute15,
1050 p_interface_header_context => pqa_interface_attribute_rec.interface_header_context,
1051 p_interface_header_attribute1 => pqa_interface_attribute_rec.interface_header_attribute1,
1052 p_interface_header_attribute2 => pqa_interface_attribute_rec.interface_header_attribute2,
1053 p_interface_header_attribute3 => pqa_interface_attribute_rec.interface_header_attribute3,
1054 p_interface_header_attribute4 => pqa_interface_attribute_rec.interface_header_attribute4,
1055 p_interface_header_attribute5 => pqa_interface_attribute_rec.interface_header_attribute5,
1056 p_interface_header_attribute6 => pqa_interface_attribute_rec.interface_header_attribute6,
1057 p_interface_header_attribute7 => pqa_interface_attribute_rec.interface_header_attribute7,
1058 p_interface_header_attribute8 => pqa_interface_attribute_rec.interface_header_attribute8,
1059 p_interface_header_attribute9 => pqa_interface_attribute_rec.interface_header_attribute9,
1060 p_interface_header_attribute10 => pqa_interface_attribute_rec.interface_header_attribute10,
1061 p_interface_header_attribute11 => pqa_interface_attribute_rec.interface_header_attribute11,
1062 p_interface_header_attribute12 => pqa_interface_attribute_rec.interface_header_attribute12,
1063 p_interface_header_attribute13 => pqa_interface_attribute_rec.interface_header_attribute13,
1064 p_interface_header_attribute14 => pqa_interface_attribute_rec.interface_header_attribute14,
1065 p_interface_header_attribute15 => pqa_interface_attribute_rec.interface_header_attribute15,
1066 p_default_ussgl_trx_code => NULL,
1067 p_line_percent => NULL,
1068 p_freight_percent => NULL,
1069 p_line_amount => NULL,
1070 p_freight_amount => NULL,
1071 p_compute_tax => NULL,
1072 p_comments => l_comments,
1073 p_customer_trx_id => l_cm_customer_trx_id,
1074 p_trx_number => l_trx_number,
1075 p_computed_tax_percent => l_computed_tax_percent,
1076 p_computed_tax_amount => l_computed_tax_amount,
1077 p_errors => p_error_tab,
1078 p_status => p_status,
1079 p_credit_line_table => l_credit_line_table,
1080 p_purchase_order => l_trx_header_rec.purchase_order,
1081 p_purchase_order_revision => l_trx_header_rec.purchase_order_revision,
1082 p_purchase_order_date => l_trx_header_rec.purchase_order_date,
1083 p_legal_entity_id => l_trx_header_rec.legal_entity_id,
1084 /*4556000-4606558*/
1085 p_global_attribute_category => pqa_global_attribute_rec.global_attribute_category,
1086 p_global_attribute1 => pqa_global_attribute_rec.global_attribute1,
1087 p_global_attribute2 => pqa_global_attribute_rec.global_attribute2,
1088 p_global_attribute3 => pqa_global_attribute_rec.global_attribute3,
1089 p_global_attribute4 => pqa_global_attribute_rec.global_attribute4,
1090 p_global_attribute5 => pqa_global_attribute_rec.global_attribute5,
1091 p_global_attribute6 => pqa_global_attribute_rec.global_attribute6,
1092 p_global_attribute7 => pqa_global_attribute_rec.global_attribute7,
1093 p_global_attribute8 => pqa_global_attribute_rec.global_attribute8,
1094 p_global_attribute9 => pqa_global_attribute_rec.global_attribute9,
1095 p_global_attribute10 => pqa_global_attribute_rec.global_attribute10,
1096 p_global_attribute11 => pqa_global_attribute_rec.global_attribute11,
1097 p_global_attribute12 => pqa_global_attribute_rec.global_attribute12,
1098 p_global_attribute13 => pqa_global_attribute_rec.global_attribute13,
1099 p_global_attribute14 => pqa_global_attribute_rec.global_attribute14,
1100 p_global_attribute15 => pqa_global_attribute_rec.global_attribute15,
1101 p_global_attribute16 => pqa_global_attribute_rec.global_attribute16,
1102 p_global_attribute17 => pqa_global_attribute_rec.global_attribute17,
1103 p_global_attribute18 => pqa_global_attribute_rec.global_attribute18,
1104 p_global_attribute19 => pqa_global_attribute_rec.global_attribute19,
1105 p_global_attribute20 => pqa_global_attribute_rec.global_attribute20,
1106 p_global_attribute21 => pqa_global_attribute_rec.global_attribute21,
1107 p_global_attribute22 => pqa_global_attribute_rec.global_attribute22,
1108 p_global_attribute23 => pqa_global_attribute_rec.global_attribute23,
1109 p_global_attribute24 => pqa_global_attribute_rec.global_attribute24,
1110 p_global_attribute25 => pqa_global_attribute_rec.global_attribute25,
1111 p_global_attribute26 => pqa_global_attribute_rec.global_attribute26,
1112 p_global_attribute27 => pqa_global_attribute_rec.global_attribute27,
1113 p_global_attribute28 => pqa_global_attribute_rec.global_attribute28,
1114 p_global_attribute29 => pqa_global_attribute_rec.global_attribute29,
1115 p_global_attribute30 => pqa_global_attribute_rec.global_attribute30);
1116
1117 END IF;
1118
1119 IF l_cm_customer_trx_id IS NOT NULL THEN
1120 UPDATE ra_cm_requests
1121 SET cm_customer_trx_id = l_cm_customer_trx_id
1122 WHERE request_id = p_request_id;
1123 END IF;
1124
1125
1126 IF PG_DEBUG in ('Y', 'C') THEN
1127 arp_util.debug( 'arw_cmreq_cover.get_cm_defaults(-)');
1128 END IF;
1129
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 /*Bug3041195*/
1133 p_status := 'E';
1134
1135
1136 END ar_autocreate_cm;
1137
1138 -- Bug 3751162/3471955 : define logic for abort process event
1139 /*4220382 : GSCC warning */
1140 FUNCTION cancel_cm_request (p_subscription_guid in raw,
1141 p_event in out NOCOPY WF_EVENT_T) return varchar2 is
1142
1143 -- note ITMETYPE is misspelled on purpose as that is how it is saved in
1144 -- wfengb.pls AbortProcess definition
1145 l_itemtype VARCHAR2(8) := p_event.GetValueForParameter('ITMETYPE');
1146 l_itemkey VARCHAR2(240) := p_event.GetValueForParameter('ITEMKEY');
1147
1148 begin
1149
1150 if l_itemtype = 'ARCMREQ' then
1151
1152 begin
1153 update ra_cm_requests_all
1154 set status = 'CANCELLED'
1155 where request_id = l_itemkey;
1156 exception
1157 when no_data_found then
1158 null;
1159 end;
1160
1161 end if;
1162
1163 return 'SUCCESS';
1164
1165 exception
1166 when others then
1167
1168 WF_CORE.CONTEXT('ARW_CMREQ_COVER', 'CANCEL_CM_REQUEST',
1169 p_event.getEventName(), p_subscription_guid);
1170 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1171
1172 RETURN 'ERROR';
1173
1174 end;
1175
1176 END ARW_CMREQ_COVER;