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