[Home] [Help]
PACKAGE BODY: APPS.FUN_AP_TRANSFER
Source
1 PACKAGE BODY FUN_AP_TRANSFER AS
2 /* $Header: FUN_AP_XFER_B.pls 120.35.12020000.4 2012/12/11 09:22:50 srampure ship $ */
3
4 /* $Header: FUN_AP_XFER_B.pls 120.35.12020000.4 2012/12/11 09:22:50 srampure ship $ */
5
6 ap_acct_invalid EXCEPTION;
7
8
9 FUNCTION has_valid_conversion_rate (
10 p_from_currency IN varchar2,
11 p_to_currency IN varchar2,
12 p_exchange_type IN varchar2,
13 p_exchange_date IN date) RETURN number
14 IS
15 l_has_rate number;
16 BEGIN
17 IF (p_from_currency = p_to_currency) THEN
18 RETURN 1;
19 END IF;
20 SELECT COUNT(conversion_rate) INTO l_has_rate
21 FROM gl_daily_rates
22 WHERE from_currency = p_from_currency AND
23 to_currency = p_to_currency AND
24 conversion_type = p_exchange_type AND
25 conversion_date = p_exchange_date;
26
27 IF (l_has_rate = 0) THEN
28 RETURN 0;
29 END IF;
30 RETURN 1;
31 END has_valid_conversion_rate;
32
33
34 /*-----------------------------------------------------
35 * FUNCTION lock_and_transfer
36 * ----------------------------------------------------
37 * Acquires lock and transfer one trx.
38 *
39 * Returns TRUE iff it can obtain lock, see a valid
40 * status, and transfer the trx.
41 * ---------------------------------------------------*/
42
43 FUNCTION lock_and_transfer (
44 p_trx_id IN number,
45 p_batch_date IN date,
46 p_vendor_id IN number,
47 p_site_id IN number,
48 p_gl_date IN date,
49 p_currency IN varchar2,
50 p_exchg_rate IN varchar2,
51 p_source IN varchar2,
52 p_approval_date IN date,
53 p_to_org_id IN number,
54 p_invoice_num IN varchar2,
55 p_from_org_id IN NUMBER) RETURN boolean
56 IS
57 l_status varchar2(15);
58 l_payable_ccid number;
59 BEGIN
60 IF (NOT lock_transaction(p_trx_id)) THEN
61 RETURN FALSE;
62 ELSE
63 transfer_single(p_trx_id, p_batch_date, p_vendor_id,
64 p_site_id, p_currency, p_exchg_rate,
65 p_source, p_gl_date, p_approval_date,
66 p_to_org_id, p_invoice_num,p_from_org_id,
67 l_payable_ccid);
68
69 update_status(p_trx_id);
70 END IF;
71
72 RETURN TRUE;
73 END lock_and_transfer;
74
75
76
77 /*-----------------------------------------------------
78 * FUNCTION lock_transaction
79 * ----------------------------------------------------
80 * Lock the transaction, test if it's valid still.
81 * ---------------------------------------------------*/
82
83 FUNCTION lock_transaction (
84 p_trx_id IN number) RETURN boolean
85 IS
86 l_status varchar2(15);
87 BEGIN
88 SELECT status INTO l_status
89 FROM fun_trx_headers
90 WHERE trx_id = p_trx_id
91 FOR UPDATE;
92
93 IF (l_status = 'XFER_AR') THEN
94 RETURN TRUE;
95 ELSE
96 RETURN FALSE;
97 END IF;
98 END lock_transaction;
99
100
101
102
103 /*-----------------------------------------------------
104 * PROCEDURE update_status
105 * ----------------------------------------------------
106 * Returns the new status.
107 * ---------------------------------------------------*/
108
109 PROCEDURE update_status (
110 p_trx_id IN number)
111 IS
112 l_result varchar2(1);
113 l_msg_count number;
114 l_msg_data varchar2(1000);
115 BEGIN
116 fun_trx_pvt.update_trx_status
117 (p_api_version => 1.0,
118 x_return_status => l_result,
119 x_msg_count => l_msg_count,
120 x_msg_data => l_msg_data,
121 p_trx_id => p_trx_id,
122 p_update_status_to => 'COMPLETE');
123
124 -- Handle the API call return
125
126 IF l_result = FND_API.G_RET_STS_ERROR THEN
127
128 raise FND_API.G_EXC_ERROR;
129 END IF;
130
131
132 IF l_result = FND_API.G_RET_STS_UNEXP_ERROR THEN
133
134 raise FND_API.G_EXC_UNEXPECTED_ERROR;
135 END IF;
136
137
138 END update_status;
139
140
141
142
143
144 /*-----------------------------------------------------
145 * PROCEDURE transfer_batch
146 * ----------------------------------------------------
147 * Transfer to AP interface in batch.
148 * ---------------------------------------------------*/
149
150 PROCEDURE transfer_batch (
151 errbuf OUT NOCOPY varchar2,
152 retcode OUT NOCOPY number,
153 p_org_id IN number default null,
154 p_le_id IN number default null,
155 p_period_low IN varchar2 default null,
156 p_period_high IN varchar2 default null,
157 p_run_payables_import IN varchar2 default 'N' )
158 IS
159 l_trx_id number;
160 l_batch_date date;
161 l_currency varchar2(15);
162 l_ledger_currency varchar2(15);
163 l_period_status varchar2(1);
164 l_gl_date date;
165 l_approval_date date;
166 l_invoice_num varchar2(50);
167 l_from_le_id number;
168 l_to_le_id number;
169 l_from_org_id number;
170 l_to_org_id number;
171 l_recipient_id number;
172 l_initiator_id number;
173
174 l_vendor_id number;
175 l_site_id number;
176 l_payable_ccid number;
177 l_rowcount number := 0;
178 l_error number := 0;
179 x_msg_data varchar2(1000);
180 l_parameter_list WF_PARAMETER_LIST_T :=wf_parameter_list_t();
181 l_event_key varchar2(240);
182 l_init_sysdate date;
183 l_source varchar2(100) := 'GLOBAL_INTERCOMPANY';
184 l_request_id number;
185 l_conv_type fun_trx_batches.exchange_rate_type%TYPE;
186 l_counter number;
187 l_trx_num varchar2(15);
188 l_batch_num varchar2(20);
189 l_batch_id number;
190 l_org_name varchar2(240);
191 l_le_name varchar2(240);
192 l_date_low date;
193 l_date_high date;
194 Request_Submission_Failure EXCEPTION;
195 is_data_transferred varchar2(1);
196 l_run_payables_import varchar2(3);
197
198
199 TYPE ORG_ID_TAB_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
200 org_id_table ORG_ID_TAB_TYPE;
201 l_previous_org_id number;
202 --Bug: 9094846.
203 CURSOR c_trx IS
204 SELECT h.trx_id,
205 h.trx_number,
206 b.batch_number,
207 b.batch_id,
208 b.batch_date,
209 b.currency_code,
210 b.exchange_rate_type,
211 ledgers.currency_code,
212 ps.closing_status,
213 b.gl_date,
214 h.approval_date,
215 h.ar_invoice_number,
216 b.from_le_id,
217 h.to_le_id ,
218 fun_tca_pkg.get_ou_id(h.initiator_id) from_org_id,
219 NVL(p_org_id, fun_tca_pkg.get_ou_id(h.recipient_id)) to_org_id,
220 b.initiator_id,
221 h.recipient_id
222 FROM fun_trx_batches b,
223 fun_trx_headers h,
224 gl_period_statuses ps,
225 gl_ledgers ledgers
226 WHERE b.batch_id = h.batch_id AND
227 ps.ledger_id = h.to_ledger_id AND
228 ps.application_id = 200 AND
229 ledgers.ledger_id = h.to_ledger_id AND
230 b.gl_date BETWEEN ps.start_date AND ps.end_date AND
231 b.gl_date BETWEEN nvl(l_date_low, b.gl_date)
232 AND nvl(l_date_high, b.gl_date) AND
233 ps.adjustment_period_flag <> 'Y' AND
234 h.to_le_id = NVL(p_le_id, h.to_le_id) AND
235 NVL(p_org_id, 1) = NVL2(p_org_id,
236 fun_tca_pkg.get_ou_id(h.recipient_id),1) AND
237 h.status = 'XFER_AR' AND
238 h.ar_invoice_number IS NOT NULL
239 ORDER BY to_org_id; -- Order By added to be able to set
240 -- Org Context when submitting AP Import
241
242 BEGIN
243 l_error:=1;
244 retcode := 0;
245 l_counter :=0;
246 l_date_low := TRUNC(fnd_date.canonical_to_date(p_period_low));
247 l_date_high:= TRUNC(fnd_date.canonical_to_date(p_period_high));
248 is_data_transferred:= 'N';
249 IF p_run_payables_import = 'Y' THEN
250 l_run_payables_import := 'Yes';
251 ELSE
252 l_run_payables_import :='No';
253 END IF;
254
255 select sysdate into l_init_sysdate from dual;
256
257 l_request_id := fnd_global.conc_request_id;
258
259 IF (p_org_id is not null) THEN
260 select hr.name into l_org_name from hr_operating_units hr
261 where hr.organization_id = p_org_id;
262 END IF;
263
264 IF (p_le_id is not null) THEN
265 select xle.name into l_le_name from xle_entity_profiles xle
266 where xle.legal_entity_id = p_le_id;
267 END IF;
268
269 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Transfer Intercompany Transactions to Payables Report Date:'||to_char(sysdate,'DD-MON-YYYY HH:MM'));
270 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2 );
271 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Operating Unit: ' || l_org_name);
272 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Legal Entity: ' || l_le_name );
273 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' GL Date From: ' || to_char(l_date_low, 'DD-MON-YYYY'));
274 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' GL Date To: ' || to_char(l_date_high,'DD-MON-YYYY'));
275 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Submit Payables Import: ' || l_run_payables_import);
276 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2 );
277 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Batch Number Transaction Number Transfer Status' );
278 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'------------ ------------------ ----------------');
279
280
281 l_previous_org_id := 0;
282 OPEN c_trx;
283 LOOP
284 FETCH c_trx INTO l_trx_id,
285 l_trx_num,
286 l_batch_num,
287 l_batch_id,
288 l_batch_date,
289 l_currency,
290 l_conv_type,
291 l_ledger_currency,
292 l_period_status,
293 l_gl_date,
294 l_approval_date,
295 l_invoice_num,
296 l_from_le_id,
297 l_to_le_id,
298 l_from_org_id,
299 l_to_org_id,
300 l_initiator_id,
301 l_recipient_id;
302
303 exit when c_trx%NOTFOUND;
304 l_counter := l_counter+1;
305
306 IF (NOT fun_trading_relation.get_supplier(
307 'INTERCOMPANY',
308 l_from_le_id,
309 l_to_le_id,
310 l_from_org_id,
311 l_to_org_id,
312 l_initiator_id,
313 l_recipient_id,
314 l_batch_date,
315 x_msg_data,
316 l_vendor_id,
317 l_site_id)) THEN
318
319 l_error := 2;
320 fnd_message.set_name('FUN','FUN_XFER_AP_INVALID_RELATION');
321
322 END IF;
323
324 IF (l_period_status NOT IN ('O', 'F')) THEN
325 l_error := 2;
326 fnd_message.set_name('FUN', 'FUN_XFER_AP_PERIOD');
327 END IF;
328
329 IF (l_to_org_id IS NULL) THEN
330 l_error := 2;
331 fnd_message.set_name('FUN', 'FUN_XFER_AP_TO_ORG_ID_NULL');
332 END IF;
333
334 IF (has_valid_conversion_rate(l_currency,l_ledger_currency,l_conv_type,l_gl_date)=0) THEN
335 l_error := 2;
336 fnd_message.set_name('FUN', 'FUN_CONV_RATE_NOT_FOUND');
337 END IF;
338
339 IF (l_error = 1) THEN
340 -- Lock record
341 SELECT trx_id INTO l_trx_id
342 FROM fun_trx_headers
343 WHERE trx_id = l_trx_id
344 FOR UPDATE;
345
346 BEGIN
347 transfer_single(l_trx_id, l_batch_date, l_vendor_id,
348 l_site_id, l_currency, l_conv_type,
349 l_source, l_gl_date, l_approval_date,
350 l_to_org_id, l_invoice_num, l_from_org_id,
351 l_payable_ccid);
352
353
354 update_status(l_trx_id);
355
356 IF l_previous_org_id <> l_to_org_id THEN
357 ORG_ID_TABLE(org_id_table.count+1) := l_to_org_id;
358 l_previous_org_id := l_to_org_id;
359 END IF;
360
361 fnd_message.set_name('FUN','FUN_XFER_SUCCESS');
362 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(l_batch_num, 1,20),20)||rpad(substr(l_trx_num,1,15),20)||fnd_message.get);
363 is_data_transferred:='Y';
364
365 --Bug:12703241. Cancel open workflw activities and the notifications
366
367 FUN_WF_COMMON.wf_abort(l_batch_id, l_trx_id, 'FUNAPINT');
368
369 EXCEPTION
370 WHEN OTHERS THEN
371 fnd_file.put_line(fnd_file.log,'Error :'||SQLERRM);
372
373
374 END;
375 ELSIF (l_error = 2) THEN
376 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(l_batch_num,1,20),20)||rpad(substr(l_trx_num,1,15),20)||fnd_message.get);
377 END IF;
378
379 l_error := 1;
380
381 END LOOP;
382 CLOSE c_trx;
383 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2 );
384 IF (l_counter = 0) THEN
385 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' *****No Data Found*****');
386 ELSE
387 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' *****End Of Report*****');
388 END IF;
389
390 COMMIT;
391
392 -- RAISE oracle.apps.fun.batch.ap.transfer;
393 WF_EVENT.AddParameterToList(p_name=>'INIT_SYS_DATE',
394 p_value=>TO_CHAR(l_init_sysdate),
395 p_parameterlist =>l_parameter_list
396 );
397 WF_EVENT.AddParameterToList(p_name=>'SOURCE',
398 p_value=>l_source,
399 p_parameterlist =>l_parameter_list
400 );
401 l_event_key:=FUN_INITIATOR_WF_PKG.GENERATE_KEY(p_batch_id=>l_request_id,
402 p_trx_id => 0
403 );
404
405 WF_EVENT.RAISE(p_event_name =>'oracle.apps.fun.batch.ap.transfer',
406 p_event_key =>l_event_key,
407 p_parameters=>l_parameter_list);
408
409 IF p_run_payables_import='Y' and is_data_transferred='Y' AND
410 org_id_table.count > 0
411 THEN
412 FOR I in org_id_table.First .. org_id_table.last
413 LOOP
414 FND_REQUEST.set_org_id(org_id_table(I));
415 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
416 application => 'SQLAP',
417 program =>'APXIIMPT',
418 description => null,
419 start_time => null,
420 sub_request=> FALSE,
421 argument1 => org_id_table(I),
422 argument2 =>'GLOBAL_INTERCOMPANY',
423 argument3 =>null,
424 argument4 =>null);
425
426 IF l_request_id <> 0 THEN
427 fnd_file.put_line(fnd_file.log,'Submitted the Payables Import Program. Request id: ' || l_request_id || ' for Org Id ' || org_id_table(I));
428 commit;
429 ELSE
430 RAISE Request_Submission_Failure;
431 END IF;
432 END LOOP;
433 END IF;
434
435
436 l_parameter_list.delete();
437
438 EXCEPTION
439
440 WHEN Request_Submission_Failure THEN
441 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in submitting Payables Import Process');
442 retcode :=2;
443 WHEN OTHERS THEN
444 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error:' || sqlcode || sqlerrm);
445 retcode := 2;
446
447 END transfer_batch;
448
449 /*-----------------------------------------------------
450 * PROCEDURE transfer_single
451 * ----------------------------------------------------
452 * Transfer a single transaction to AP interface.
453 * It assumes that the caller has a lock on the
454 * transaction, and the caller will do the commit.
455 * ---------------------------------------------------*/
456
457 PROCEDURE transfer_single (
458 p_trx_id IN number,
459 p_batch_date IN date,
460 p_vendor_id IN number,
461 p_vendor_site_id IN number,
462 p_currency IN varchar2,
463 p_conv_type IN varchar2,
464 p_source IN varchar2,
465 p_gl_date IN date,
466 p_approval_date IN date,
467 p_org_id IN number,
468 p_invoice_num IN varchar2,
469 p_from_org_id IN NUMBER,
470 p_payables_ccid OUT NOCOPY number)
471 IS
472 l_acct_valid varchar2(1);
473 l_desc varchar2(250);
474 l_desc_header_level varchar2(250); -- Bug No : 7652608
475 l_invoice_id number;
476 l_inv_line_id number;
477 l_l_amount_cr number;
478 l_l_amount_dr number;
479 l_d_amount_cr number;
480 l_d_amount_dr number;
481 l_amount number;
482 l_ccid number;
483 l_dist_number number;
484 l_line_type varchar2(15);
485 l_dist_type varchar2(15);
486
487 l_parameter_list WF_PARAMETER_LIST_T :=wf_parameter_list_t();
488 l_event_key VARCHAR2(240);
489
490 CURSOR c_dist IS
491 SELECT NVL(d.amount_cr,0), NVL(d.amount_dr,0), d.ccid,
492 d.dist_type_flag, NVL(t.reci_amount_cr,0), NVL(t.reci_amount_dr,0),
493 d.dist_number, t.line_type_flag, d.description -- Bug No : 7122846
494 FROM fun_dist_lines d,
495 fun_trx_lines t
496 WHERE t.trx_id = p_trx_id AND
497 d.line_id = t.line_id AND
498 d.party_type_flag = 'R';
499
500 -- For intercompany transactions, as per the current datamodel
501 -- each AR Intercompany invoice will have 1 line
502 -- ie 1 row in ra_customer_trx and ra_customer_trx_lines
503 -- Bug 9463299: Added clause AND artrx.INTERFACE_HEADER_CONTEXT = 'INTERNAL_ALLOCATIONS'
504 CURSOR c_get_etax_info (p_trx_id NUMBER,
505 p_invoice_num VARCHAR2,
506 p_from_org_id NUMBER)
507 IS
508 SELECT zx.application_id,
509 zx.entity_code,
510 zx.event_class_code,
511 zx.trx_id,
512 zx.trx_line_id,
513 zx.trx_level_type
514 FROM zx_lines_det_factors zx,
515 ra_customer_trx_all artrx
516 WHERE zx.trx_id = artrx.customer_trx_id
517 AND zx.application_id = 222
518 AND zx.entity_code = 'TRANSACTIONS'
519 AND artrx.INTERFACE_HEADER_CONTEXT = 'INTERNAL_ALLOCATIONS'
520 AND artrx.trx_number = p_invoice_num
521 AND artrx.org_id = p_from_org_id
522 AND artrx.INTERFACE_HEADER_ATTRIBUTE2 = TO_CHAR(p_trx_id); -- bug no : 7718598
523 ---Bug: 9094846
524
525
526 l_payment_method_lookup_code VARCHAR2(30);
527 l_application_id zx_lines_det_factors.application_id%TYPE;
528 l_entity_code zx_lines_det_factors.entity_code%TYPE;
529 l_event_class_code zx_lines_det_factors.event_class_code%TYPE;
530 l_trx_id zx_lines_det_factors.trx_id%TYPE;
531 l_trx_line_id zx_lines_det_factors.trx_line_id%TYPE;
532 l_trx_level_type zx_lines_det_factors.trx_level_type%TYPE;
533
534 --Bug: 13622727
535
536 --------------------------------------------------------
537 l_currency_code varchar2(10);
538 l_le_id number;
539 l_party_id number;
540 l_party_site_id number;
541 l_IBY_PAYMENT_METHOD varchar2(80);
542 l_PAYMENT_REASON varchar2(80);
543 l_BANK_CHARGE_BEARER_DSP varchar2(80);
544 l_DELIVERY_CHANNEL varchar2(80);
545 l_SETTLEMENT_PRIORITY_DSP varchar2(80);
546 l_bank_account_num varchar2(100);
547 l_bank_account_name varchar2(80);
548 l_bank_branch_name varchar2(360);
549 l_bank_branch_num varchar2(30);
550 l_bank_name varchar2(360);
551 l_bank_number varchar2(30);
552 l_PAYMENT_METHOD_CODE varchar2(30);
553 l_PAYMENT_REASON_CODE varchar2(30);
554 l_BANK_CHARGE_BEARER varchar2(30);
555 l_DELIVERY_CHANNEL_CODE varchar2(30);
556 l_SETTLEMENT_PRIORITY varchar2(30);
557 l_external_bank_account_id number;
558 l_exclusive_payment_flag varchar2(1);
559 l_payment_reason_comments varchar2(240);
560
561 --------------------------------------------------------
562
563 BEGIN
564
565 -- Get etax info for AP to be able to calculate tax
566 OPEN c_get_etax_info( p_trx_id, p_invoice_num, p_from_org_id);
567 FETCH c_get_etax_info INTO l_application_id,
568 l_entity_code,
569 l_event_class_code,
570 l_trx_id,
571 l_trx_line_id,
572 l_trx_level_type;
573 CLOSE c_get_etax_info;
574
575 SELECT supp.party_id, site.party_site_id
576 INTO l_party_id, l_party_site_id
577 FROM
578 ap_suppliers supp,
579 ap_supplier_sites_all site
580 WHERE supp.vendor_id = site.vendor_id
581 AND site.vendor_site_id = p_vendor_site_id
582 AND site.vendor_id = p_vendor_id
583 AND site.org_id = p_org_id;
584 OPEN c_dist;
585
586 SELECT ap_invoices_interface_s.nextval INTO l_invoice_id
587 FROM dual;
588
589 LOOP
590 FETCH c_dist INTO l_d_amount_cr, l_d_amount_dr, l_ccid,
591 l_dist_type, l_l_amount_cr, l_l_amount_dr,
592 l_dist_number, l_line_type, l_desc;
593 EXIT WHEN c_dist%NOTFOUND;
594
595 IF (l_dist_type = 'P') THEN
596 /* to do
597 fun_trx_pvt.is_payable_acct_valid(l_acct_valid, l_ccid);
598 IF (l_acct_valid <> fnd_api.g_ret_sts_success) THEN
599 RAISE ap_acct_invalid;
600 END IF;
601 */
602
603 -- Amounts Transferred to AP should be
604 -- Reci Trx Amount: 1000 Cr, AP Amount: 1000
605 -- Reci Trx Amount: -1000 Cr, AP Amount: -1000
606 -- Reci Trx Amount: 1000 Dr, AP Amount: -1000
607 -- Reci Trx Amount: -1000 Dr, AP Amount: 1000
608
609 IF l_l_amount_cr <> 0
610 THEN
611 l_amount := l_l_amount_cr;
612
613 ELSIF l_l_amount_dr <> 0
614 THEN
615 l_amount := l_l_amount_dr * (-1);
616 END IF;
617 -- Bug: 7652608
618 select fth.description, fth.to_le_id, ftb.currency_code
619 INTO l_desc_header_level, l_le_id, l_currency_code
620 from fun_trx_headers fth, fun_trx_batches ftb
621 where fth.trx_id = p_trx_id
622 and fth.batch_id = ftb.batch_id;
623
624 --Bug: 13622727
625 ap_invoices_pkg.get_payment_attributes(
626 p_le_id =>l_le_id,
627 p_org_id =>p_org_id,
628 p_payee_party_id =>l_party_id,
629 p_payee_party_site_id =>l_party_site_id,
630 p_supplier_site_id =>p_vendor_site_id,
631 p_payment_currency =>l_currency_code,
632 p_payment_amount =>l_amount,
633 p_payment_function =>'PAYABLES_DISB',
634 p_pay_proc_trxn_type_code =>'PAYABLES_DOC',
635 p_PAYMENT_METHOD_CODE => l_payment_method_code,
636 p_PAYMENT_REASON_CODE => l_payment_reason_code,
637 p_BANK_CHARGE_BEARER => l_bank_charge_bearer,
638 p_DELIVERY_CHANNEL_CODE => l_delivery_channel_code,
639 p_SETTLEMENT_PRIORITY => l_settlement_priority,
640 p_PAY_ALONE => l_exclusive_payment_flag,
641 p_external_bank_account_id => l_external_bank_account_id,
642 p_IBY_PAYMENT_METHOD => l_IBY_PAYMENT_METHOD,
643 p_PAYMENT_REASON => l_PAYMENT_REASON,
644 p_BANK_CHARGE_BEARER_DSP => l_BANK_CHARGE_BEARER_DSP,
645 p_DELIVERY_CHANNEL => l_DELIVERY_CHANNEL,
646 p_SETTLEMENT_PRIORITY_DSP => l_SETTLEMENT_PRIORITY_DSP,
647 p_bank_account_num => l_bank_account_num,
648 p_bank_account_name => l_bank_account_name,
649 p_bank_branch_name => l_bank_branch_name,
650 p_bank_branch_num => l_bank_branch_num,
651 p_bank_name => l_bank_name,
652 p_bank_number => l_bank_number,
653 p_payment_reason_comments => l_payment_reason_comments,
654 p_application_id => 200);
655
656 l_payment_method_lookup_code := Nvl(l_payment_method_code,
657 'CHECK');
658
659 INSERT INTO ap_invoices_interface (
660 invoice_id, invoice_num, invoice_date,
661 vendor_id, vendor_site_id, invoice_amount,
662 invoice_currency_code, exchange_rate_type, exchange_date,
663 description,
664 source, group_id,
665 goods_received_date, invoice_received_date,
666 gl_date, accts_pay_code_combination_id, org_id,
667 payment_method_code,
668 calc_tax_during_import_flag ,
669 add_tax_to_inv_amt_flag)
670 VALUES (
671 l_invoice_id, p_invoice_num, p_batch_date,
672 p_vendor_id, p_vendor_site_id, l_amount,
673 p_currency, p_conv_type, p_batch_date,
674 l_desc_header_level, -- Bug No : 7652608
675 p_source, p_trx_id,
676 p_batch_date, Nvl(p_approval_date, TRUNC(SYSDATE)),
677 p_gl_date, l_ccid, p_org_id,
678 l_payment_method_lookup_code,
679 'Y',
680 'Y');
681
682 ELSIF (l_dist_type = 'L') THEN
683 -- Amounts Transferred to AP should be
684 -- Reci Dst Amount: 1000 Dr, AP Amount: 1000
685 -- Reci Dst Amount: -1000 Dr, AP Amount: -1000
686 -- Reci Dst Amount: 1000 Cr, AP Amount: -1000
687 -- Reci Dst Amount: -1000 Cr, AP Amount: 1000
688
689 IF l_d_amount_dr <> 0
690 THEN
691 l_amount := l_d_amount_dr;
692
693 ELSIF l_d_amount_cr <> 0
694 THEN
695 l_amount := l_d_amount_cr * (-1);
696 END IF;
697
698 SELECT ap_invoice_lines_interface_s.nextval into l_inv_line_id
699 FROM dual;
700
701 -- Bug 9734156 Mapped Dist Number to line number
702
703 INSERT INTO ap_invoice_lines_interface (
704 invoice_id, invoice_line_id, line_number,
705 line_type_lookup_code, amount, accounting_date,
706 description, dist_code_combination_id, org_id,
707 source_application_id,
708 source_entity_code,
709 source_event_class_code,
710 source_trx_id,
711 source_trx_level_type,
712 source_line_id )
713 VALUES (
714 l_invoice_id, l_inv_line_id, l_dist_number,
715 'ITEM', l_amount, p_gl_date,
716 l_desc, l_ccid, p_org_id,
717 l_application_id, -- added for etax changes
718 l_entity_code, -- added for etax changes
719 'INTERCOMPANY_TRX', -- added for etax changes
720 l_trx_id, -- added for etax changes
721 'LINE' , -- added for etax changes
722 l_trx_line_id);
723 END IF;
724
725 END LOOP;
726
727 WF_EVENT.AddParameterToList(p_name=>'INVOICE_ID',
728 p_value=>TO_CHAR(l_invoice_id),
729 p_parameterlist =>l_parameter_list
730 );
731
732 WF_EVENT.AddParameterToList(p_name=>'TRX_ID',
733 p_value=>TO_CHAR(p_trx_id),
734 p_parameterlist =>l_parameter_list
735 );
736
737 l_event_key:=FUN_INITIATOR_WF_PKG.GENERATE_KEY(p_batch_id=>l_invoice_id,
738 p_trx_id => 0
739 );
740
741 WF_EVENT.RAISE(p_event_name =>'oracle.apps.fun.single.ap.transfer',
742 p_event_key =>l_event_key,
743 p_parameters=>l_parameter_list);
744
745 l_parameter_list.delete();
746
747 EXCEPTION
748 When others then
749 raise;
750
751 END transfer_single;
752
753
754
755 END;
756