[Home] [Help]
PACKAGE BODY: APPS.PO_INVOICES_SV1
Source
1 PACKAGE BODY PO_INVOICES_SV1 AS
2 /* $Header: POXIVCRB.pls 120.9 2006/06/13 12:24:04 szaveri noship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6 g_asn_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_RVCTP_ENABLE_TRACE'),'N');
7
8 /* <CANCEL ASBN FPI START>, bug # 2569530 */
9 g_log_head CONSTANT VARCHAR2(30) := 'po.plsql.PO_INVOICES_SV1.';
10 /* <CANCEL ASBN FPI END> */
11
12 /* <PAY ON USE FPI START> */
13 g_pkg_name CONSTANT VARCHAR2(50) := 'PO_INVOICES_SV1';
14 /* <PAY ON USE FPI END> */
15
16 /*================================================================
17
18 PROCEDURE NAME: create_ap_invoices()
19
20 ==================================================================*/
21 PROCEDURE create_ap_invoices(X_transaction_source IN VARCHAR2,
22 X_commit_interval IN NUMBER,
23 X_shipment_header_id IN NUMBER,
24 X_aging_period IN NUMBER)
25
26 IS
27
28 X_progress VARCHAR2(4) := null;
29 X_completion_code BOOLEAN;
30 X_receipt_completion_status BOOLEAN := TRUE;
31 X_bill_notice_compl_status BOOLEAN := TRUE;
32 X_receipt_event VARCHAR2(25) := 'RECEIVE';
33 /*** this makes the API more generic, in future releases, we can use
34 the same procedure to handle transactions for 'ACCEPT', 'DELIVER', etc.
35 ***/
36
37 X_doc_sequence_value NUMBER;
38 X_doc_sequence_id NUMBER;
39 X_db_sequence_name VARCHAR2(50);
40 X_sequential_numbering VARCHAR2(2);
41 X_invoice_id NUMBER;
42 X_set_of_books_id NUMBER;
43 X_invoice_date DATE;
44
45
46 /* <PAY ON USE FPI> */
47 l_consumption_comp_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
48
49
50 BEGIN
51
52 FND_MSG_PUB.initialize;
53
54 /* Bug WDK - Removed profile PO_ASBN_INVOICE_INTERFACE */
55
56 If (X_transaction_source IS NULL) THEN
57 X_progress := '010';
58 X_receipt_completion_status :=
59 po_invoices_sv2.create_receipt_invoices(X_commit_interval,
60 X_shipment_header_id,
61 X_receipt_event, X_aging_period);
62
63 X_progress := '020';
64
65 X_bill_notice_compl_status :=
66 PO_CREATE_ASBN_INVOICE.create_asbn_invoice (1,
67 X_shipment_header_id);
68
69
70 /* <PAY ON USE FPI START> */
71 PO_INVOICES_SV2.create_use_invoices(
72 1.0,
73 l_consumption_comp_status,
74 x_commit_interval,
75 x_aging_period);
76 /* <PAY ON USE FPI END> */
77
78 ELSIF (X_transaction_source = 'ERS') THEN
79 X_progress := '030';
80 X_receipt_completion_status :=
81 po_invoices_sv2.create_receipt_invoices(X_commit_interval,
82 X_shipment_header_id,
83 X_receipt_event,X_aging_period);
84
85 ELSIF (X_transaction_source = 'ASBN') THEN
86 X_progress := '040';
87
88 X_bill_notice_compl_status :=
89 PO_CREATE_ASBN_INVOICE.create_asbn_invoice (1,
90 X_shipment_header_id);
91
92 /* <PAY ON USE FPI START> */
93 ELSIF (X_transaction_source = 'ERS_AND_USE') THEN
94 X_receipt_completion_status :=
95 PO_INVOICES_SV2.create_receipt_invoices(
96 X_commit_interval,
97 X_shipment_header_id,
98 X_receipt_event,
99 X_aging_period);
100
101 PO_INVOICES_SV2.create_use_invoices(
102 1.0,
103 l_consumption_comp_status,
104 x_commit_interval,
105 x_aging_period);
106
107 ELSIF (X_transaction_source = 'USE') THEN
108
109 PO_INVOICES_SV2.create_use_invoices(
110 1.0,
111 l_consumption_comp_status,
112 x_commit_interval,
113 x_aging_period);
114
115 /* <PAY ON USE FPI END> */
116
117 END IF;
118
119
120 X_progress := '050';
121 X_completion_code := X_receipt_completion_status AND
122 X_bill_notice_compl_status;
123
124 /** This will return TRUE if create_ap_invoices did not encounter any
125 application errors. However, if one or more application errors is
126 found during the execution of this program, X_completion_code will
127 be FALSE. **/
128
129 /* <PAY ON USE FPI START> */
130 IF (l_consumption_comp_status <> FND_API.G_RET_STS_SUCCESS) THEN
131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
132 END IF;
133 /* <PAY ON USE FPI END> */
134
135 EXCEPTION
136 /* <PAY ON USE FPI START> */
137 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
138 RAISE; /* Error has been printed. No need to print error here */
139 /* <PAY ON USE FPI END> */
140 WHEN others THEN
141 po_message_s.sql_error('create_ap_invoices', x_progress,sqlcode);
142 RAISE;
143 END create_ap_invoices;
144
145
146 /*================================================================
147
148 FUNCTION NAME: get_vendor_related_info()
149
150 ==================================================================*/
151
152 PROCEDURE get_vendor_related_info (X_vendor_id IN NUMBER,
153 X_default_pay_site_id IN NUMBER,
154 X_pay_group_lookup_code OUT NOCOPY VARCHAR2,
155 X_payment_method_lookup_code OUT NOCOPY VARCHAR2,
156 X_payment_priority OUT NOCOPY VARCHAR2,
157 X_terms_date_basis OUT NOCOPY VARCHAR2,
158 X_vendor_income_tax_region OUT NOCOPY VARCHAR2,
159 X_type_1099 OUT NOCOPY VARCHAR2,
160 X_awt_flag OUT NOCOPY VARCHAR2,
161 X_awt_group_id OUT NOCOPY NUMBER,
162 X_exclude_freight_from_disc OUT NOCOPY VARCHAR2,
163 X_payment_currency_code OUT NOCOPY VARCHAR2 -- BUG 612979
164 )
165
166 IS
167 X_progress VARCHAR2(4) := null;
168 x_txn_attributes_rec IBY_DISBURSEMENT_COMP_PUB.Trxn_Attributes_Rec_Type;
169 x_return_status VARCHAR2(3);
170 x_msg_count NUMBER;
171 x_msg_data VARCHAR2(2000);
172 x_default_pmt_attrs_rec IBY_DISBURSEMENT_COMP_PUB.Default_Pmt_Attrs_Rec_Type;
173 Begin
174 X_progress := '010';
175
176 /*** Obtain the following vendor, vendor_site related info. We would
177 first lookup the values at the vendor-site first. If not found, then
178 we would use the values specified at the vendor site. */
179
180 SELECT 200 Application_ID, --this APs application ID
181 PO_MOAC_UTILS_PVT.Get_Current_Org_Id Payer_Org_Id,
182 pvds.party_id Payer_Party_Id,
183 pvss.party_site_id Payee_Party_Site_Id,
184 'PAYABLES_DOC' Pay_Proc_Trxn_Type_Code,
185 NVL(pvss.payment_currency_code, pvss.invoice_currency_code) Payment_Currency,
186 'PAYABLES_DISB' Payment_Function,
187 NVL(pvss.pay_group_lookup_code, pvds.pay_group_lookup_code),
188 pvss.payment_priority,
189 pvss.terms_date_basis,
190 pvss.state vendor_income_tax_region,
191 pvds.type_1099,
192 pvss.allow_awt_flag,
193 pvss.awt_group_id,
194 pvss.exclude_freight_from_discount,
195 NVL(pvss.payment_currency_code, pvss.invoice_currency_code)
196 INTO X_txn_attributes_rec.Application_Id,
197 X_txn_attributes_rec.Payer_Org_Id,
198 X_txn_attributes_rec.Payee_Party_Id,
199 X_txn_attributes_rec.Payee_Party_Site_Id,
200 x_txn_attributes_rec.Pay_Proc_Trxn_Type_Code,
201 x_txn_attributes_rec.Payment_Currency,
202 x_txn_attributes_rec.Payment_Function,
203 X_pay_group_lookup_code,
204 X_payment_priority,
205 X_terms_date_basis,
206 X_vendor_income_tax_region,
207 X_type_1099,
208 X_awt_flag,
209 X_awt_group_id,
210 X_exclude_freight_from_disc,
211 X_payment_currency_code
212 FROM po_vendors pvds,
213 po_vendor_sites pvss
214 WHERE pvss.vendor_site_id = X_default_pay_site_id
215 AND pvss.vendor_id = pvds.vendor_id;
216
217 /* Bug 4656555 - get legal_entity_id from base table
218 SELECT legal_entity_id
219 INTO x_txn_attributes_rec.Payer_Legal_Entity_Id
220 FROM hr_operating_units hou
221 WHERE hou.organization_id = X_txn_attributes_rec.Payer_Org_Id;
222 */
223 SELECT to_number(org_information2)
224 INTO x_txn_attributes_rec.Payer_Legal_Entity_Id
225 FROM hr_organization_information hoi,
226 hr_all_organization_units_tl otl
227 WHERE hoi.organization_id = X_txn_attributes_rec.Payer_Org_Id
228 AND hoi.organization_id = otl.organization_id
229 AND hoi.org_information_context = 'Operating Unit Information'
230 AND otl.language = userenv('LANG');
231
232 IBY_DISBURSEMENT_COMP_PUB.Get_Default_Payment_Attributes(
233 p_api_version => 1.0
234 , p_init_msg_list => FND_API.G_FALSE
235 , p_ignore_payee_pref => 'N'
236 , p_trxn_attributes_rec => x_txn_attributes_rec
237 , x_return_status => x_return_status
238 , x_msg_count => x_msg_count
239 , x_msg_data => x_msg_data
240 , x_default_pmt_attrs_rec => x_default_pmt_attrs_rec);
241
242 X_payment_method_lookup_code := x_default_pmt_attrs_rec.Payment_Method.Payment_Method_Code;
243 EXCEPTION
244 WHEN others THEN
245 po_message_s.sql_error('get_vendor_related_info', x_progress,sqlcode);
246 RAISE;
247 END get_vendor_related_info;
248
249
250 /*================================================================
251
252 FUNCTION NAME: get_ap_parameters()
253
254 ==================================================================*/
255
256
257 PROCEDURE get_ap_parameters( X_def_sets_of_books_id OUT NOCOPY NUMBER,
258 X_def_base_currency_code OUT NOCOPY VARCHAR2,
259 X_def_batch_control_flag OUT NOCOPY VARCHAR2,
260 X_def_exchange_rate_type OUT NOCOPY VARCHAR2,
261 X_def_multi_currency_flag OUT NOCOPY VARCHAR2,
262 X_def_gl_dat_fr_rec_flag OUT NOCOPY VARCHAR2,
263 X_def_dis_inv_less_tax_flag OUT NOCOPY VARCHAR2,
264 X_def_income_tax_region OUT NOCOPY VARCHAR2,
265 X_def_income_tax_region_flag OUT NOCOPY VARCHAR2,
266 X_def_vat_country_code OUT NOCOPY VARCHAR2,
267 X_def_transfer_desc_flex_flag OUT NOCOPY VARCHAR2,
268 X_def_org_id OUT NOCOPY NUMBER,
269 /* bug# 908129 added the following parameter*/
270 X_def_awt_include_tax_amt OUT NOCOPY VARCHAR2 )
271 IS
272
273 X_progress VARCHAR2(4) := NULL;
274
275
276 BEGIN
277
278 IF (g_asn_debug = 'Y') THEN
279 asn_debug.put_line('Obtain AP System Options ... ');
280 END IF;
281 X_progress := '010';
282
283 /* This select statement is used to obtain the AP_SYSTEM_PARAMETERS */
284
285 SELECT set_of_books_id,
286 base_currency_code,
287 NVL(batch_control_flag, 'N') batch_control_flag,
288 default_exchange_rate_type,
289 multi_currency_flag,
290 gl_date_from_receipt_flag,
291 disc_is_inv_less_tax_flag, income_tax_region,
292 income_tax_region_flag,
293 transfer_desc_flex_flag,
294 org_id,
295 awt_include_tax_amt
296 INTO X_def_sets_of_books_id,
297 X_def_base_currency_code,
298 X_def_batch_control_flag,
299 X_def_exchange_rate_type,
300 X_def_multi_currency_flag,
301 X_def_gl_dat_fr_rec_flag,
302 X_def_dis_inv_less_tax_flag,
303 X_def_income_tax_region,
304 X_def_income_tax_region_flag,
305 X_def_transfer_desc_flex_flag,
306 X_def_org_id,
307 X_def_awt_include_tax_amt
308 FROM ap_system_parameters;
309
310 X_progress := '020';
311
312 SELECT vat_country_code
313 INTO X_def_vat_country_code
314 FROM financials_system_parameters;
315
316
317 EXCEPTION
318 WHEN others THEN
319 IF (g_asn_debug = 'Y') THEN
320 asn_debug.put_line('Error in getting AP System Options ... ');
321 END IF;
322 po_message_s.sql_error('get_ap_parameters', x_progress,sqlcode);
323 RAISE;
324 END get_ap_parameters;
325
326
327 /*================================================================
328
329 PROCEDURE NAME: create_ap_batches()
330
331 ==================================================================*/
332
333 -- Bug 4723269 : Added parameter p_org_id
334
335 PROCEDURE create_ap_batches( X_batch_source IN VARCHAR2,
336 X_currency_code IN VARCHAR2,
337 p_org_id IN NUMBER,
338 X_batch_id OUT NOCOPY NUMBER
339 )
340
341 IS
342
343 X_progress VARCHAR2(3) := NULL;
344 X_tmp_batch_id NUMBER;
345 X_batch_name ap_batches.batch_name%TYPE;
346
347 BEGIN
348 IF (g_asn_debug = 'Y') THEN
349 asn_debug.put_line('Creating AP Invoice Batch ... ');
350 END IF;
351
352 X_progress := '010';
353 /*** obtain the translated batch name ***/
354 IF (X_batch_source = 'ERS') THEN
355 fnd_message.set_name('PO', 'PO_INV_CR_ERS_BATCH_DESC');
356 ELSIF (X_batch_source = 'ASBN') THEN
357 fnd_message.set_name('PO', 'PO_INV_CR_ASBN_BATCH_DESC');
358 END IF;
359
360 X_progress := '020';
361 IF (X_batch_source IN ('ERS', 'ASBN')) THEN
362 X_batch_name := fnd_message.get;
363 ELSE
364 X_batch_name := X_batch_source;
365 END IF;
366
367 X_progress := '030';
368 SELECT ap_batches_s.nextval
369 INTO X_tmp_batch_id
370 FROM dual;
371
372 -- Bug 4723269 : Populate org_id in ap_batches_all
373
374 X_progress := '040';
375 INSERT INTO ap_batches_all
376 ( batch_id,
377 batch_name,
378 batch_date,
379 invoice_currency_code,
380 payment_currency_code,
381 last_update_date,
382 last_updated_by,
383 last_update_login,
384 creation_date,
385 created_by,
386 org_id
387 )
388 VALUES
389 ( X_tmp_batch_id,
390 X_batch_name || '/' || TO_CHAR(sysdate)
391 || '/' || TO_CHAR(X_tmp_batch_id),
392 sysdate,
393 X_currency_code,
394 X_currency_code,
395 sysdate,
396 FND_GLOBAL.user_id,
397 FND_GLOBAL.login_id,
398 sysdate,
399 FND_GLOBAL.user_id,
400 p_org_id
401 );
402
403 X_batch_id := X_tmp_batch_id;
404
405 EXCEPTION
406 WHEN others THEN
407 IF (g_asn_debug = 'Y') THEN
408 asn_debug.put_line('Error in creating AP Invoice Batch ... ');
409 END IF;
410 po_message_s.sql_error('create_ap_batches', x_progress,sqlcode);
411 RAISE;
412 END create_ap_batches;
413
414
415 /*================================================================
416
417 PROCEDURE NAME: update_ap_batches()
418
419 ==================================================================*/
420 PROCEDURE update_ap_batches( X_batch_id IN NUMBER,
421 X_invoice_count IN NUMBER,
422 X_invoice_total IN NUMBER)
423
424 IS
425 X_progress VARCHAR2(3) := null;
426 BEGIN
427 IF (g_asn_debug = 'Y') THEN
428 asn_debug.put_line('Updating current invoice batch ... ');
429 END IF;
430
431 X_progress := '010';
432
433 /* Bug402317. gtummala. 2/13/97
434 * We were not populating the control count or the control
435 * control total originally. These need to be popluated
436 * to be the same as the actual count and actual total.
437 */
438
439 -- Bug 4723269 : Changed ap_batches to ap_batches_all
440
441 UPDATE ap_batches_all
442 SET actual_invoice_count = X_invoice_count,
443 actual_invoice_total = X_invoice_total,
444 control_invoice_count = X_invoice_count,
445 control_invoice_total = X_invoice_total,
446 last_updated_by = FND_GLOBAL.user_id,
447 last_update_date = sysdate,
448 last_update_login = FND_GLOBAL.login_id
449 WHERE batch_id = X_batch_id;
450
451 EXCEPTION
452 WHEN others THEN
453 IF (g_asn_debug = 'Y') THEN
454 asn_debug.put_line('Error in Updating current invoice batch ... ');
455 END IF;
456 po_message_s.sql_error('update_ap_batches', x_progress,sqlcode);
457 RAISE;
458 END update_ap_batches;
459
460
461
462 /*================================================================
463
464 PROCEDURE NAME: create_invoice_header()
465
466 ==================================================================*/
467
468
469 /* =====================================================================
470
471 PROCEDURE get_accounting_date_and_period
472
473 ======================================================================== */
474
475 PROCEDURE get_accounting_date_and_period(
476 X_def_gl_dat_fr_rec_flag IN VARCHAR2,
477 X_def_sets_of_books_id IN NUMBER,
478 X_invoice_date IN DATE,
479 X_receipt_date IN DATE,
480 X_batch_id IN NUMBER,
481 X_transaction_type IN VARCHAR2,
482 X_unique_id IN NUMBER,
483 X_accounting_date OUT NOCOPY DATE,
484 X_period_name OUT NOCOPY VARCHAR2,
485 X_curr_inv_process_flag IN OUT NOCOPY VARCHAR2 )
486
487 IS
488
489 X_progress VARCHAR2(3) := null;
490 X_temp_accounting_date DATE;
491 X_temp_period_name gl_period_statuses.period_name%TYPE;
492
493 CURSOR c_period IS
494 SELECT period_name
495 FROM gl_period_statuses gps
496 WHERE gps.application_id = 200 /*** Payables ***/
497 AND gps.set_of_books_id = X_def_sets_of_books_id
498 AND gps.adjustment_period_flag = 'N'
499 AND X_temp_accounting_date
500 BETWEEN gps.start_date AND gps.end_date
501 AND gps.closing_status IN ('O', 'F');
502 /*** period would be an OPEN or FUTURE one ***/
503
504 BEGIN
505 x_progress := '010';
506
507 IF (X_def_gl_dat_fr_rec_flag = 'I') THEN
508 /*** GL Date = 'Invoice' ***/
509 X_temp_accounting_date := X_invoice_date;
510 ELSIF (X_def_gl_dat_fr_rec_flag = 'S') THEN
511 /*** GL Date = 'System' ***/
512 X_temp_accounting_date := sysdate;
513 ELSIF (X_def_gl_dat_fr_rec_flag = 'N') THEN
514 /*** GL Date = 'Receipt-Invoice' ***/
515 X_temp_accounting_date := NVL(X_receipt_date, X_invoice_date);
516 ELSIF (X_def_gl_dat_fr_rec_flag = 'Y') THEN
517 /*** GL Date = 'Receipt-System ***/
518 X_temp_accounting_date := NVL(X_receipt_date, sysdate);
519 END IF;
520
521 /* bug 657365, need to truncate the accounting date before we pass into the cursor
522 to determine if period is open or not */
523
524 x_temp_accounting_date := trunc(x_temp_accounting_date);
525
526 /*** need some way to signal an error if accounting date is NULL ***/
527
528 X_progress := '020';
529 /*** Next find out the period name for the accounting date: ***/
530 X_temp_period_name := NULL;
531 OPEN c_period;
532 FETCH c_period INTO X_temp_period_name;
533 CLOSE c_period;
534
535 X_progress := '030';
536 If (X_temp_period_name IS NULL) THEN
537 /*** accounting date used does not fall into an open
538 or future accounting period. ***/
539
540 X_progress := '040';
541 IF (g_asn_debug = 'Y') THEN
542 asn_debug.put_line('->Error: Invalid acctg date.');
543 END IF;
544
545 po_interface_errors_sv1.handle_interface_errors(
546 X_transaction_type,
547 'FATAL',
548 X_batch_id,
549 X_unique_id, -- header_id
550 null, -- line_id
551 'PO_INV_CR_INVALID_GL_PERIOD',
552 'GL_PERIOD_STATUSES',
553 'PERIOD_NAME',
554 'GL_DATE',
555 null, null, null, null, null,
556 fnd_date.date_to_chardate(X_temp_accounting_date),
557 null,null,null,null,null,
558 X_curr_inv_process_flag);
559 ELSE
560 X_progress := '050';
561 IF (g_asn_debug = 'Y') THEN
562 asn_debug.put_line('Acctg Date = ' ||TO_CHAR(X_temp_accounting_date));
563 END IF;
564 X_accounting_date := X_temp_accounting_date;
565 X_period_name := X_temp_period_name;
566 END IF;
567
568
569 EXCEPTION
570 WHEN others THEN
571 po_message_s.sql_error('get_accounting_date_and_period',
572 x_progress, sqlcode);
573 RAISE;
574 END get_accounting_date_and_period;
575
576
577 --
578 -- Cancel invoice line
579 --
580 /*==================================================================
581 PROCEDURE NAME: cancel_asbn_invoices_line
582
583 DESCRIPTION: Decide call which version of AP's API Ap_Cancel_Single_Invoice
584 to cancel invoices in ASBN cancellation
585
586 PARAMETERS: p_invoice_id IN NUMBER,
587
588 DESIGN
589 REFERENCES:
590
591 CHANGE Created 21-AUGUST-02 DXIE
592 HISTORY:
593
594 13/06/2006 Modified the procedure and removed calls to
595 cancel_asbn_invoices_line_new and
596 cancel_asbn_invoices_line_old (Bug: 5257152)
597 =======================================================================*/
598
599 PROCEDURE cancel_asbn_invoices_line (
600 p_invoice_id IN NUMBER)
601 IS
602
603 l_gl_date DATE;
604 l_api_name CONSTANT VARCHAR2(30) := 'cancel_asbn_invoices_line';
605 l_token VARCHAR2(1000);
606 l_return_status BOOLEAN;
607 l_message_name FND_NEW_MESSAGES.message_name%TYPE;
608 l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
609 l_base_amount AP_INVOICES.base_amount%TYPE;
610 l_temp_cancelled_amount AP_INVOICES.temp_cancelled_amount%TYPE;
611 l_cancelled_by AP_INVOICES.cancelled_by%TYPE;
612 l_cancelled_amount AP_INVOICES.cancelled_amount%TYPE;
613 l_cancelled_date AP_INVOICES.cancelled_date%TYPE;
614 l_last_update_date AP_INVOICES.last_update_date%TYPE;
615 l_original_prepayment_amount NUMBER;
616 l_pay_curr_invoice_amount AP_INVOICES.pay_curr_invoice_amount%TYPE;
617
618 BEGIN
619
620 IF (g_fnd_debug = 'Y') THEN
621 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
622 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name || '.begin','Inside CANCEL_ASBN_INVOICES_LINE procedure');
623 END IF;
624 END IF;
625
626
627 SELECT gl_date
628 INTO l_gl_date
629 FROM ap_invoices_all
630 WHERE invoice_id = p_invoice_id;
631
632 IF (NOT AP_CANCEL_PKG.Ap_Cancel_Single_Invoice( --AP Call
633 P_invoice_id => p_invoice_id,
634 P_last_updated_by => 1,
635 P_last_update_login => 1,
636 P_accounting_date => l_gl_date,
637 P_message_name => l_message_name,
638 P_invoice_amount => l_invoice_amount,
639 P_base_amount => l_base_amount,
640 P_temp_cancelled_amount => l_temp_cancelled_amount,
641 P_cancelled_by => l_cancelled_by,
642 P_cancelled_amount => l_cancelled_amount,
643 P_cancelled_date => l_cancelled_date,
644 P_last_update_date => l_last_update_date,
645 P_original_prepayment_amount=> l_original_prepayment_amount,
646 P_pay_curr_invoice_amount => l_pay_curr_invoice_amount,
647 P_Token => l_token,
648 P_calling_sequence => 'rvtth.lpc'
649 )
650 ) THEN
651 IF (g_asn_debug = 'Y') THEN
652 asn_debug.put_line('AP_CANCEL_PKG.Ap_Cancel_Single_Invoice returned FALSE');
653 asn_debug.put_line('Token Value from AP API = ' || l_token);
654 END IF;
655 ELSE
656 IF (g_asn_debug = 'Y') THEN
657 asn_debug.put_line('AP_CANCEL_PKG.Ap_Cancel_Single_Invoice returned TRUE');
658 asn_debug.put_line('Token Value from AP API = ' || l_token);
659 END IF;
660 END IF; --AP Call ends
661
662 IF (g_fnd_debug = 'Y') THEN
663 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
664 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name || '.begin', 'CANCEL_ASBN_INVOICES_LINE procedure Ends');
665 END IF;
666 END IF;
667
668 EXCEPTION
669 WHEN OTHERS THEN
670 IF (g_fnd_debug = 'Y') THEN
671 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
672 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, g_log_head || l_api_name ||'.EXCEPTION', 'CANCEL_ASBN_INVOICES_LINE: Inside exception :'|| sqlcode);
673 END IF;
674 END IF;
675 RAISE; --Raise the Exception
676 END cancel_asbn_invoices_line;
677
678
679 --
680 -- Cancel invoice in case of ASBN
681 -- Call the AP package to cancel the single invoice.
682 --
683 /*==================================================================
684 PROCEDURE NAME: cancel_asbn_invoices
685
686 DESCRIPTION: Calls AP's API Ap_Cancel_Single_Invoice to cancel invoices
687 in ASBN cancellation
688
689 PARAMETERS: p_invoice_num IN VARCHAR2,
690 p_vendor_id IN NUMBER
691
692 DESIGN
693 REFERENCES:
694
695 CHANGE Created 21-AUGUST-02 DXIE
696 HISTORY:
697 =======================================================================*/
698
699 PROCEDURE cancel_asbn_invoices (
700 p_invoice_num IN VARCHAR2,
701 p_vendor_id IN NUMBER)
702
703 IS
704 l_invoice_id NUMBER := NULL;
705
706 CURSOR l_invoice_id_csr IS
707 select invoice_id
708 from AP_INVOICES_ALL
709 where invoice_num = p_invoice_num
710 and vendor_id = p_vendor_id;
711
712 l_api_name CONSTANT VARCHAR2(30) := 'cancel_asbn_invoices';
713
714 BEGIN
715
716 IF (g_fnd_debug = 'Y') THEN
717 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
718 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name || '.begin','Inside CANCEL_ASBN_INVOICES procedure');
719 END IF;
720 END IF;
721 -- Get invoice_id and call AP package to cancel invoice
722 if (p_invoice_num is not null) then
723 OPEN l_invoice_id_csr;
724 LOOP
725 FETCH l_invoice_id_csr INTO l_invoice_id;
726 EXIT WHEN l_invoice_id_csr%NOTFOUND;
727
728 if (l_invoice_id is not null) then
729 IF (g_fnd_debug = 'Y') THEN
730 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
731 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name || '.begin', 'Call CANCEL_ASBN_INVOICES_LINE procedure');
732 END IF;
733 END IF;
734 cancel_asbn_invoices_line(l_invoice_id);
735 end if;
736
737 END LOOP;
738 CLOSE l_invoice_id_csr;
739 end if;
740
741 IF (g_fnd_debug = 'Y') THEN
742 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
743 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name || '.begin', 'CANCEL_ASBN_INVOICES procedure Ends');
744 END IF;
745 END IF;
746
747 EXCEPTION
748 WHEN OTHERS THEN
749 IF (g_fnd_debug = 'Y') THEN
750 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
751 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, g_log_head || l_api_name ||'.EXCEPTION', 'CANCEL_ASBN_INVOICES: Inside exception :'|| sqlcode);
752 END IF;
753 END IF;
754 raise;
755
756 END cancel_asbn_invoices;
757 /* <CANCEL ASBN FPI END> */
758
759 /* <PAY ON USE FPI START> */
760 PROCEDURE submit_invoice_import (
761 x_return_status OUT NOCOPY VARCHAR2,
762 p_source IN VARCHAR2,
763 p_group_id IN VARCHAR2,
764 p_batch_name IN VARCHAR2,
765 p_user_id IN NUMBER,
766 p_login_id IN NUMBER,
767 x_request_id OUT NOCOPY NUMBER)
768 IS
769 l_api_name VARCHAR2(50) := 'submit_invoice_import';
770 BEGIN
771 x_return_status := FND_API.G_RET_STS_SUCCESS;
772
773 x_request_id := fnd_request.submit_request(
774 'SQLAP',
775 'APXIIMPT',
776 NULL,
777 NULL,
778 FALSE,
779 NULL, -- Bug 4911166 : Passing Operating unit parameter
780 p_source,
781 p_group_id,
782 p_batch_name,
783 null, -- hold name
784 null, -- hold reason
785 null, -- gl date
786 'N', -- purge flag
787 'N', -- trace switch
788 'N', -- debug switch
789 'N', -- summary flag
790 TO_CHAR(1000), -- commit batch size
791 TO_CHAR(p_user_id), -- user_id
792 TO_CHAR(p_login_id), -- login_id
793 fnd_global.local_chr(0),NULL,
794 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
795 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
796 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
797 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
798 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
799
800 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
801 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
802 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
803 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
804
805 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
806 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
807 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
808
809 EXCEPTION
810 WHEN OTHERS THEN
811 IF (g_asn_debug = 'Y') THEN
812 ASN_DEBUG.put_line('Error in submit invoice import.');
813 END IF;
814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
815 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
816 END submit_invoice_import;
817
818 PROCEDURE delete_interface_records(
819 x_return_status OUT NOCOPY VARCHAR2,
820 p_group_id IN VARCHAR2)
821 IS
822 l_api_name VARCHAR2(50) := 'delete_interface_records';
823 BEGIN
824 x_return_status := FND_API.G_RET_STS_SUCCESS;
825
826 DELETE FROM ap_invoice_lines_interface aili
827 WHERE EXISTS (SELECT 1
828 FROM ap_invoices_interface aii
829 WHERE aii.invoice_id = aili.invoice_id
830 AND aii.group_id = p_group_id);
831
832 DELETE FROM ap_invoices_interface aii
833 WHERE aii.group_id = p_group_id;
834 EXCEPTION
835 WHEN OTHERS THEN
836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
837 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
838 END delete_interface_records;
839
840 /* <PAY ON USE FPI END> */
841
842 END PO_INVOICES_SV1;