[Home] [Help]
PACKAGE BODY: APPS.AP_ISP_UTILITIES_PKG
Source
1 PACKAGE BODY AP_ISP_UTILITIES_PKG AS
2 /* $Header: apisputb.pls 120.50.12020000.6 2013/05/20 07:19:20 rseeta ship $ */
3
4 --added the below FND_LOG related variables, in order
5 --to enable LOGGING for this package.
6 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
8 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
9 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
11 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
12 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
13 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_ISP_UTILITIES_PKG.';
14
15 g_inv_sysdate CONSTANT DATE := TRUNC(SYSDATE);
16
17
18 /*==========================================================================
19 PROCEDURE: Get_Doc_Sequence
20 Note: Method has to be automatic!
21 Mode 1: Simple Manual Entry without Audit
22 (Use Voucher Num, Seq Num "Not Used")
23 Mode 3: Auto voucher numbering with Audit
24 (Use doc_sequence_value, Seq Num 'P','A'))
25 Mode 3 will override Mode 1
26 Mode 2 Audited Manual Entry is not supported
27
28 The following is a brief description of the implementation of Document
29 Sequential Numbering in Invoice Open Interface (R11 only)
30
31 The two modes for numbering can be:
32 - Simple Manual Entry without Audit: Any value entered in the column
33 AP_INVOICES_INTERFACE.VOUCHER_NUM will be inserted in AP_INVOICES.
34 VOUCHER_NUM without validation.
35
36 - Auto Voucher Numbering with Audit: A value will be obtained
37 automatically for the record being imported and will be populated in
38 AP_INVOICES. DOC_SEQUENCE_VALUE. Also audit information would be inserted
39 into the audit table.
40
41 If the profile value for the "Sequential Numbering" option is "Not Used"
42 there will be no document sequencing generated.
43
44 If the profile value is "Partial" or "Always" then
45 document sequencing will be generated???
46
47 If the profile value is "Always" and no document category is specified
48 by the user, then "Standard Invoices" category will be used for
49 standard invoices and "Credit Memo Invoices" category will be used
50 for credits.
51 We assume that a valid automatic sequence exists for such categories.
52
53 ============================================================================*/
54
55 PROCEDURE get_doc_sequence(
56 p_invoice_id IN NUMBER,
57 p_sequence_numbering IN VARCHAR2,
58 p_doc_category_code OUT NOCOPY VARCHAR,
59 p_db_sequence_value OUT NOCOPY NUMBER,
60 p_db_seq_name OUT NOCOPY VARCHAR2,
61 p_db_sequence_id OUT NOCOPY NUMBER,
62 p_calling_sequence IN VARCHAR2)
63
64 IS
65 get_doc_seq_failure EXCEPTION;
66 l_name VARCHAR2(80);
67 l_doc_category_code ap_invoices.doc_category_code%TYPE;
68 l_application_id NUMBER;
69 l_doc_seq_ass_id NUMBER;
70 l_invoice_type_lookup_code ap_invoices.invoice_type_lookup_code%TYPE;
71 l_set_of_books_id ap_invoices.set_of_books_id%TYPE;
72 l_gl_date ap_invoices.gl_date%TYPE;
73 current_calling_sequence VARCHAR2(2000);
74 debug_info VARCHAR2(500);
75 l_return_code NUMBER;
76 l_api_name VARCHAR2(50);
77
78 BEGIN
79 -- Update the calling sequence
80
81 current_calling_sequence := 'get_doc_sequence<-'||P_calling_sequence;
82
83 l_api_name := 'get_doc_sequence';
84
85 BEGIN
86 select invoice_type_lookup_code, set_of_books_id, gl_date
87 into l_invoice_type_lookup_code, l_set_of_books_id, l_gl_date
88 from ap_invoices_all
89 where invoice_id = p_invoice_id;
90 EXCEPTION
91 WHEN NO_DATA_FOUND THEN
92 debug_info := 'no data found for the invoice id = '|| p_invoice_id;
93 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
94 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, debug_info);
95 END IF;
96
97 -- don't do anything if invoice doesn't exist
98 return;
99 END;
100 debug_info := l_api_name || ': invoice_type = '|| l_invoice_type_lookup_code ||
101 ', set_of_books_id = ' || l_set_of_books_id ||
102 ', gl_date = ' || l_gl_date;
103 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
104 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
105 END IF;
106
107
108 --------------------------------------------------------------------------
109 -- Step 1
110 -- p_sequence_numbering should be in ('A','P')
111 -- Do not use seq num if N (Not Used)
112 --------------------------------------------------------------------------
113
114 IF (p_sequence_numbering IN ('A','P')) THEN
115
116 ---------------------------------------------------------------------
117 -- Step 3
118 -- Use Default Doc Category
119 ---------------------------------------------------------------------
120 debug_info := 'Use Default Category, Seq:Always';
121 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
122 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, debug_info);
123 END IF;
124
125 --Contract Payments: Modified the IF condition to look at the invoice_type
126 --rather than the sign of the invoice_amount in deciding which category to
127 --apply, and also added the logic for 'PREPAYMENT' invoices.
128
129 -- Bug 10206983. Added 'INVOICE REQUEST'.
130
131 IF (l_invoice_type_lookup_code IN ('STANDARD', 'INVOICE REQUEST')) THEN
132 l_doc_category_code := 'STD INV';
133 ELSIF (l_invoice_type_lookup_code= 'PAYMENT REQUEST') THEN
134 l_doc_category_code := 'PAY REQ INV';
135 ELSIF (l_invoice_type_lookup_code= 'CREDIT') THEN
136 l_doc_category_code := 'CRM INV';
137 ELSIF (l_invoice_type_lookup_code= 'PREPAYMENT') THEN
138 l_doc_category_code := 'PREPAY INV';
139 END IF;
140
141 debug_info := '-----> l_doc_category_code = ' || l_doc_category_code ;
142 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
143 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, debug_info);
144 END IF;
145
146 ---------------------------------------------------------------------------
147 -- Step 4
148 -- Get Doc Sequence Number
149 ---------------------------------------------------------------------------
150
151 IF ((l_doc_category_code IS NOT NULL) )THEN
152
153 debug_info := 'Valid Category ->Check if valid Sequence assigned';
154 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
155 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, debug_info);
156 END IF;
157
158 BEGIN
159 SELECT SEQ.DB_SEQUENCE_NAME,
160 SEQ.DOC_SEQUENCE_ID,
161 SA.doc_sequence_assignment_id
162 INTO p_db_seq_name,
163 p_db_sequence_id ,
164 l_doc_seq_ass_id
165 FROM FND_DOCUMENT_SEQUENCES SEQ,
166 FND_DOC_SEQUENCE_ASSIGNMENTS SA
167 WHERE SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
168 AND SA.APPLICATION_ID = 200
169 AND SA.CATEGORY_CODE = l_doc_category_code
170 AND (NVL(SA.METHOD_CODE,'A') = 'A')
171 AND (SA.SET_OF_BOOKS_ID = l_set_of_books_id)
172 AND NVL(l_gl_date, g_inv_sysdate) between
173 SA.START_DATE and
174 NVL(SA.END_DATE, TO_DATE('31/12/4712','DD/MM/YYYY'));
175 EXCEPTION
176 WHEN NO_DATA_FOUND Then
177 RAISE get_doc_seq_failure;
178 END; -- end of the above BEGION
179
180 ----------------------------------------------------------------------
181 -- Step 5
182 -- Get Doc Sequence Val
183 ----------------------------------------------------------------------
184 debug_info := 'Get Next Val';
185 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
186 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, debug_info);
187 END IF;
188
189 l_return_code := FND_SEQNUM.GET_SEQ_VAL(
190 200,
191 l_doc_category_code,
192 l_set_of_books_id,
193 'A',
194 NVL(l_gl_date, sysdate),
195 p_db_sequence_value,
196 p_db_sequence_id ,
197 'N',
198 'N');
199 debug_info := '-----------> l_doc_category_code = '|| l_doc_category_code
200 || ' p_set_of_books_id = '||to_char(l_set_of_books_id)
201 || ' p_db_sequence_id = '||to_char(p_db_sequence_id )
202 ||' p_db_seq_name = '||p_db_seq_name
203 ||' p_db_sequence_value = '||to_char(p_db_sequence_value);
204 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
205 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
206 END IF;
207
208 END IF; -- end of check l_current_invoice_status/doc_category_code
209 END IF; -- p_sequence_numbering = 'N'
210
211 p_doc_category_code := l_doc_category_code;
212
213 EXCEPTION
214 WHEN OTHERS THEN
215
216 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
217 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, debug_info);
218 END IF;
219
220 IF (SQLCODE < 0) THEN
221 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
222 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, SQLERRM);
223 END IF;
224 END IF;
225
226 END get_doc_sequence;
227
228 ------------------------------------------------------------------
229 -- This function is used to get payment terms information.
230 --
231 ------------------------------------------------------------------
232 PROCEDURE get_payment_terms (
233 p_invoice_id IN NUMBER,
234 p_terms_id OUT NOCOPY NUMBER,
235 p_terms_date OUT NOCOPY DATE,
236 p_calling_sequence IN VARCHAR2)
237 IS
238
239 l_po_header_id ap_invoices_all.po_header_id%TYPE;
240 l_vendor_site_id ap_invoices_all.vendor_site_id%TYPE;
241 l_org_id ap_invoices_all.org_id%TYPE;
242 l_invoice_type_lookup_code ap_invoices_all.invoice_type_lookup_code%TYPE;
243 l_invoice_date ap_invoices_all.invoice_date%TYPE;
244 l_invoice_received_date ap_invoices_all.invoice_received_date%TYPE;
245 l_goods_received_date ap_invoices_all.goods_received_date%TYPE;
246 l_terms_date_basis ap_system_parameters_all.terms_date_basis%TYPE;
247 l_term_id_per_name NUMBER := Null;
248 l_start_date_active DATE;
249 l_end_date_active DATE;
250 l_start_date_active_per_name DATE;
251 l_end_date_active_per_name DATE;
252 current_calling_sequence VARCHAR2(2000);
253 debug_info VARCHAR2(500);
254 l_term_name VARCHAR2(50);--Bug 4115712
255 l_no_calendar_exists VARCHAR2(1); --Bug 4115712
256 l_api_name VARCHAR2(50);
257
258
259 BEGIN
260 -- Update the calling sequence
261 --
262 current_calling_sequence :=
263 'AP_IMPORT_VALIDATION_PKG.get_payment_terms<-'
264 ||P_calling_sequence;
265 l_api_name := 'get_payment_terms';
266 --------------------------------------------------------------------------
267 -- terms defaulting: if PO exists for the invoice,
268 -- use PO terms, otherwise use terms from Supplier Site.
269 --------------------------------------------------------------------------
270 BEGIN
271 select po_header_id, vendor_site_id, org_id,
272 invoice_type_lookup_code, invoice_date,
273 invoice_received_date, goods_received_date
274 into l_po_header_id, l_vendor_site_id, l_org_id,
275 l_invoice_type_lookup_code, l_invoice_date,
276 l_invoice_received_date, l_goods_received_date
277 from ap_invoices_all
278 where invoice_id = p_invoice_id;
279 EXCEPTION
280 WHEN NO_DATA_FOUND THEN
281 debug_info := 'no data found for the invoice id = '|| p_invoice_id;
282 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
283 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, debug_info);
284 END IF;
285
286 return;
287 END;
288 debug_info := l_api_name || ': po_header_id = ' || l_po_header_id ||
289 ', vendor_site_id = ' || l_vendor_site_id || ', org_id = ' ||
290 l_org_id || ', invoice_type = '|| l_invoice_type_lookup_code ||
291 ', invoice_date = ' || l_invoice_date;
292 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
293 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
294 END IF;
295
296 BEGIN
297 -- bug8711412
298 select nvl(assi.terms_date_basis, aps.terms_date_basis)
299 into l_terms_date_basis
300 from ap_supplier_sites_all assi,
301 ap_product_setup aps
302 where assi.vendor_site_id = l_vendor_site_id;
303 EXCEPTION
304 WHEN NO_DATA_FOUND THEN
305 debug_info := 'no ap options found for the vendor_site_id = '|| l_vendor_site_id;
306 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
307 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, debug_info);
308 END IF;
309
310 return;
311 END;
312 debug_info := l_api_name || ': terms_date_basis = ' || l_terms_date_basis;
313 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
314 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
315 END IF;
316
317 --------------------------------------------------------------
318 -- Step 1
319 -- get payment terms from PO or Supplier Site.
320 --------------------------------------------------------------
321 IF (l_po_header_id is NOT NULL) Then
322 debug_info := 'Get term_id from header po_number';
323 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
324 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
325 END IF;
326
327 SELECT terms_id
328 INTO p_terms_id
329 FROM po_headers_all
330 WHERE po_header_id = l_po_header_id
331 AND type_lookup_code in ('BLANKET', 'PLANNED', 'STANDARD');
332
333 debug_info := l_api_name || ': p_terms_id = ' || p_terms_id;
334 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
335 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
336 END IF;
337 END IF;
338
339 -- no term from header level po_number, try lines level po_number
340 IF (p_terms_id is null ) THEN
341 debug_info := 'Get term_id from lines po_numbers';
342 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
343 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
344 END IF;
345 BEGIN
346 SELECT p.terms_id
347 INTO p_terms_id
348 FROM po_headers_all p, ap_invoice_lines_all l
349 WHERE p.type_lookup_code in ('BLANKET', 'PLANNED', 'STANDARD')
350 AND l.po_header_id = p.po_header_id
351 AND l.invoice_id = p_invoice_id
352 AND p.terms_id IS NOT NULL
353 GROUP BY p.terms_id;
354 EXCEPTION
355 WHEN NO_DATA_FOUND THEN
356 NULL;
357 WHEN TOO_MANY_ROWS THEN
358 p_terms_id := null;
359 END;
360
361 -- no term from line level PO, try line level receipt
362 IF (p_terms_id is null) THEN
363 debug_info := 'Get term_id from lines receipt';
364 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
365 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
366 END IF;
367 BEGIN
368 SELECT p.terms_id
369 INTO p_terms_id
370 FROM rcv_shipment_lines r,
371 po_headers_all p,
372 ap_invoice_lines_all l
373 WHERE p.po_header_id = r.po_header_id
374 AND r.shipment_line_id = l.rcv_shipment_line_id
375 AND l.invoice_id = p_invoice_id
376 AND p.terms_id IS NOT NULL
377 GROUP BY p.terms_id;
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 NULL;
381 WHEN TOO_MANY_ROWS THEN
382 debug_info := 'too many rows';
383 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
384 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
385 END IF;
386 p_terms_id := null;
387 END;
388
389 END IF; -- end get term from line level receipt
390
391 END IF; -- end get term from line level
392
393 -- no term from header or line level
394 IF ( (p_terms_id is null) AND
395 (l_invoice_type_lookup_code <> 'PAYMENT REQUEST') ) Then
396
397 debug_info := 'Get term_id from supplier site';
398 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
399 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
400 END IF;
401
402 SELECT terms_id
403 INTO p_terms_id
404 FROM po_vendor_sites_all
405 WHERE vendor_site_id = l_vendor_site_id;
406
407 ELSIF ( (p_terms_id is null) AND
408 (l_invoice_type_lookup_code = 'PAYMENT REQUEST') ) Then
409
410 debug_info := 'Get term_id from financials options';
411 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
412 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
413 END IF;
414
415 SELECT terms_id
416 INTO p_terms_id
417 FROM financials_system_params_all
418 WHERE org_id = l_org_id;
419
420 END IF;
421
422 debug_info := 'getting term active date';
423 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
424 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
425 END IF;
426
427 SELECT start_date_active, end_date_active
428 INTO l_start_date_active, l_end_date_active
429 FROM ap_terms
430 WHERE term_id = p_terms_id;
431
432 debug_info := 'terms id derived: '|| p_terms_id;
433 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
434 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
435 END IF;
436
437 --------------------------------------------------------------------------
438 -- Step 2
439 -- Derive terms date if possible
440 --
441 --------------------------------------------------------------------------
442 IF ( p_terms_id is not null ) THEN
443 IF (l_terms_date_basis = 'Invoice Received') THEN
444 p_terms_date := l_invoice_received_date;
445 ELSIF (l_terms_date_basis = 'Goods Received') THEN
446 p_terms_date := l_goods_received_date;
447 ELSIF (l_terms_date_basis = 'Invoice') THEN
448 p_terms_date := l_invoice_date;
449 ELSIF (l_terms_date_basis = 'Current') THEN
450 p_terms_date := g_inv_sysdate;
451 ELSE
452 p_terms_date := g_inv_sysdate;
453 END IF;
454 END IF;
455
456 -- Bug 4115712
457 ------------------------------------------------------------------------------
458 -- Step 4
459 -- For calendar based payment terms :
460 -- Check if special calendar exists for the period
461 -- in which the terms date falls, else fail insert.
462 -----------------------------------------------------------------------------
463 debug_info := 'Check calendar based payment terms';
464
465 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
466 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
467 END IF;
468
469 --Bug:4115712
470 IF (p_terms_id IS NOT NULL) THEN
471
472 select name
473 into l_term_name
474 from ap_terms
475 where term_id = p_terms_id;
476
477 END IF;
478
479 AP_TERMS_CAL_EXISTS_PKG.Check_For_Calendar(
480 P_Terms_Name => l_term_name,
481 P_Terms_Date => p_terms_date,
482 P_No_Cal => l_no_calendar_exists,
483 P_Calling_Sequence => 'v_check_invalidate_terms');
484
485 EXCEPTION
486 WHEN OTHERS THEN
487 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
488 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, debug_info);
489 END IF;
490
491 IF (SQLCODE < 0) THEN
492 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
493 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, SQLERRM);
494 END IF;
495 END IF;
496
497 END get_payment_terms;
498
499
500 /*=============================================================================
501 | Function Cancel_Single_Invoice
502 |
503 | Cancels one invoice by executing the following sequence of steps.
504 | This is the wrapper procedure based on
505 | ap_cancel_pkg.ap_cancel_single_invoice
506 |
507 | PROGRAM FLOW
508 |
509 | 1. check if invoice cancellable, if yes, proceed otherwise return false
510 | 3.(If invoice has had tax withheld, undo withholding) - commented
511 | 4. Clear out payment schedules
512 | 5. Cancel all the non-discard lines
513 | a. reverse matching
514 | b. fetch the maximum distribution line number
515 | c. Set encumbered flags to 'N'
516 | d. Accounting event generation
517 | e. reverse the distributions
518 | f. update Line level Cancelled information
519 | 6. Zero out the Invoice
520 | 7. Run AutoApproval for this invoice
521 | 8. check posting holds remain on this canncelled invoice
522 | a. if NOT exist - complete the cancellation by updating header
523 | level information set return value to TRUE
524 | b. if exist - no update, set the return valuse to FALSE, NO
525 | DATA rollback.
526 | 9. Commit Data
527 | 10. Populate the out parameters.
528 |
529 | NOTES
530 | 1. bug2328225 case of Matching a special charge only invoice to
531 | receipt so we check if the quantity invoiced is not null too
532 | 2. Events Project
533 | We no longer need to prevent the cancellation of an invoice
534 | just because the accounting of related payments has not been
535 | created. Therefore, bug fixes 902110 and 2237152 are removed.
536 |
537 | MODIFICATION HISTORY
538 | Date Author Description of Change
539 |
540 *============================================================================*/
541
542 PROCEDURE Cancel_Single_Invoice(
543 P_invoice_id IN NUMBER,
544 P_last_updated_by IN NUMBER,
545 P_last_update_login IN NUMBER,
546 P_accounting_date IN DATE,
547 P_message_name OUT NOCOPY VARCHAR2,
548 P_Token OUT NOCOPY VARCHAR2,
549 P_calling_sequence IN VARCHAR2)
550 IS
551
552 l_invoice_amount NUMBER;
553 l_base_amount NUMBER;
554 l_temp_cancelled_amount NUMBER;
555 l_cancelled_by NUMBER;
556 l_cancelled_amount NUMBER;
557 l_pay_curr_invoice_amount NUMBER;
558 l_cancelled_date DATE;
559 l_last_update_date DATE;
560 l_debug_info VARCHAR2(240);
561 l_original_prepayment_amount NUMBER;
562 l_curr_calling_sequence VARCHAR2(2000);
563 l_result BOOLEAN;
564 l_api_name VARCHAR2(50);
565 l_org_id NUMBER;
566
567 BEGIN
568 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.CANCEL_SINGLE_INVOICE<-' ||
569 P_calling_sequence;
570
571 l_api_name := 'cancel_single_invoice';
572 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
573 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.CANCEL_SINGLE_INVOICE(+)');
574 END IF;
575
576 l_debug_info := 'calling ap_cancel_pkg.ap_cancel_single_invoice()...';
577 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
578 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
579 END IF;
580
581
582 --5126689, since a supplier can now cancel an invoice we need to set
583 --the org and initialize, otherwise the code may not see the invoice on
584 --the moac synonyms and po will fail when they try to close the po.
585 select org_id
586 into l_org_id
587 from ap_invoices_all
588 where invoice_id = p_invoice_id;
589
590 mo_global.set_policy_context('S',l_org_id);
591 fnd_global.apps_initialize (
592 user_id =>P_last_updated_by,
593 resp_id =>-1,
594 resp_appl_id => 200); --ap
595
596
597
598 l_result := AP_CANCEL_PKG.AP_Cancel_Single_Invoice(
599 p_invoice_id,
600 p_last_updated_by,
601 p_last_update_login,
602 p_accounting_date,
603 p_message_name,
604 l_invoice_amount,
605 l_base_amount,
606 l_temp_cancelled_amount,
607 l_cancelled_by,
608 l_cancelled_amount,
609 l_cancelled_date,
610 l_last_update_date,
611 l_original_prepayment_amount,
612 l_pay_curr_invoice_amount,
613 p_token,
614 l_curr_calling_sequence);
615 l_debug_info := 'ap_cancel_single_invoice() called ';
616 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
617 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
618 END IF;
619 -- commit
620 -- ISP:CodeCleanup Bug 5256954
621 -- commit;
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 IF (SQLCODE <> -20001) THEN
626 rollback;
627 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
628 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
629 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
630 FND_MESSAGE.SET_TOKEN('PARAMETERS',
631 ' P_invoice_id = ' || P_invoice_id
632 ||' P_last_updated_by = ' || P_last_updated_by
633 ||' P_last_update_login = ' || P_last_update_login
634 ||' P_accounting_date = ' || P_accounting_date);
635 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
636 END IF;
637
638 APP_EXCEPTION.RAISE_EXCEPTION;
639
640
641 END Cancel_Single_Invoice;
642
643
644 /*=============================================================================
645 | public FUNCTION Discard_Inv_Line
646 |
647 | This is a wrapper function based on
648 | ap_invoice_lines_pkg.discard_inv_line().
649 |
650 | Discard or cancel the invoice line depending on calling mode. If error
651 | occurs, it return 1 and error code will be populated. Otherwise,
652 | It return 0.
653 |
654 | Parameters
655 | P_line_rec - Invoice line record
656 | P_calling_mode - either from DISCARD, CANCEL or UNAPPLY_PREPAY
657 | p_inv_cancellable - 'Y' if invoice is canellable.
658 | P_last_updated_by
659 | P_last_update_login
660 | P_error_code - Error code indicates why it is not discardable
661 | P_calling_sequence - For debugging purpose
662 |
663 *===========================================================================*/
664 /* Bug 5470344 XBuild11 Code cleanup
665 This code is not being used
666 PROCEDURE Discard_Inv_Line(
667 p_invoice_id IN ap_invoice_lines.invoice_id%TYPE,
668 p_line_number IN ap_invoice_lines.line_number%TYPE,
669 p_calling_mode IN VARCHAR2,
670 p_inv_cancellable IN VARCHAR2 DEFAULT NULL,
671 P_last_updated_by IN NUMBER,
672 P_last_update_login IN NUMBER,
673 P_error_code OUT NOCOPY VARCHAR2,
674 P_token OUT NOCOPY VARCHAR2,
675 P_calling_sequence IN VARCHAR2)
676 IS
677
678 l_line_rec ap_invoice_lines%ROWTYPE;
679 l_curr_calling_sequence VARCHAR2(2000);
680 l_debug_info VARCHAR2(2000);
681 l_api_name VARCHAR2(50);
682 l_result NUMBER;
683
684 BEGIN
685
686 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.discard_inv_line <- ' ||
687 p_calling_sequence;
688
689 l_api_name := 'discard_inv_line';
690 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
691 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.discard_inv_line(+)');
692 END IF;
693
694 l_debug_info := 'get invoice line info...';
695 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
696 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
697 END IF;
698
699 select invoice_id, line_number,
700 po_line_location_id,
701 rcv_transaction_id, accounting_date,
702 amount, unit_price, unit_meas_lookup_code,
703 quantity_invoiced, discarded_flag, cancelled_flag,
704 period_name,
705 line_type_lookup_code, match_type
706 into l_line_rec.invoice_id, l_line_rec.line_number,
707 l_line_rec.po_line_location_id,
708 l_line_rec.rcv_transaction_id,
709 l_line_rec.accounting_date,
710 l_line_rec.amount, l_line_rec.unit_price,
711 l_line_rec.unit_meas_lookup_code,
712 l_line_rec.quantity_invoiced,
713 l_line_rec.discarded_flag, l_line_rec.cancelled_flag,
714 l_line_rec.period_name,
715 l_line_rec.line_type_lookup_code,
716 l_line_rec.match_type
717 from ap_invoice_lines_all
718 where invoice_id = p_invoice_id
719 and line_number = p_line_number;
720
721 l_debug_info := 'invoice_id = ' || l_line_rec.invoice_id ||
722 ', line_number = '|| l_line_rec.line_number;
723 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
724 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
725 END IF;
726
727 if ( ap_invoice_lines_pkg.discard_inv_line(p_line_rec => l_line_rec,
728 p_calling_mode => p_calling_mode,
729 p_inv_cancellable => p_inv_cancellable,
730 p_last_updated_by => p_last_updated_by,
731 p_last_update_login => p_last_update_login,
732 p_error_code => p_error_code,
733 p_token => p_token,
734 p_calling_sequence => p_calling_sequence) ) then
735 l_result := 0;
736 else
737 l_result := 1;
738 end if;
739
740 l_debug_info := 'discard_inv_line called ';
741 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
742 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
743 END IF;
744
745 -- return l_result;
746 -- commit;
747
748 EXCEPTION
749 WHEN OTHERS THEN
750 IF (SQLCODE <> -20001) THEN
751 rollback;
752 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
753 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
754 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
755 FND_MESSAGE.SET_TOKEN('PARAMETERS',
756 ' P_invoice_id = ' || p_invoice_id
757 ||' P_line_number = ' || p_line_number
758 ||' P_last_updated_by = ' || P_last_updated_by
759 ||' P_last_update_login = ' || P_last_update_login
760 ||' P_calling_mode = ' || p_calling_mode);
761 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
762 END IF;
763
764 APP_EXCEPTION.RAISE_EXCEPTION;
765
766 END Discard_Inv_line;
767 */
768
769 /* Bug 5407726 ISP Code cleanup XBuild9
770 This code is not being used
771 =============================================================================
772 | public procedure invoke_ap_workflow
773 | starts up a workflow process for AP invoice
774 |
775 | Parameters
776 | P_invoice_id - invoice id
777 | P_calling_sequence - For debugging purpose
778 |
779 *===========================================================================
780 PROCEDURE invoke_ap_workflow(
781 p_item_key IN VARCHAR2,
782 p_invoice_id IN ap_invoices.invoice_id%TYPE,
783 p_org_id IN ap_invoices.org_id%TYPE,
784 P_calling_sequence IN VARCHAR2)
785 IS
786
787 l_curr_calling_sequence VARCHAR2(2000);
788 l_debug_info VARCHAR2(2000);
789 l_api_name VARCHAR2(50);
790 l_result NUMBER;
791 l_item_key VARCHAR2(100);
792 l_iteration NUMBER;
793 l_invoice_supplier_name VARCHAR2(80);
794 l_invoice_number VARCHAR2(50);
795 l_invoice_date DATE;
796 l_invoice_description VARCHAR2(240);
797 l_supplier_role VARCHAR2(320);
798 BEGIN
799
800 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.invoke_ap_workflow <- ' ||
801 p_calling_sequence;
802
803 l_api_name := 'invoke_ap_workflow';
804 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
805 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.invoke_ap_workflow(+)');
806 END IF;
807
808 --
809 -- Creating a workflow process
810 --
811 l_debug_info := 'creating a workflow process...';
812 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
813 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
814 END IF;
815
816 WF_ENGINE.createProcess('APINVLDP',p_item_key, 'DISPUTE_MAIN');
817
818 l_debug_info := 'workflow process created. ';
819 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
820 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
821 END IF;
822
823
824 --
825 -- Initializing attributes
826 --
827 l_debug_info := 'setting workflow process attributes... ';
828 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
829 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
830 END IF;
831
832 WF_ENGINE.setItemAttrText('APINVLDP',p_item_key, 'INVOICE_ID', p_invoice_id);
833 WF_ENGINE.setItemAttrText('APINVLDP',p_item_key, 'ORG_ID', p_org_id);
834 l_debug_info := 'invoke_ap_workflow: invoice_id = ' || p_invoice_id ||
835 ', org_id = ' || p_org_id;
836 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
837 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
838 END IF;
839
840
841 SELECT
842 PV.vendor_name,
843 AI.invoice_num,
844 AI.invoice_date,
845 AI.description,
846 decode(AI.source, 'ISP', u.user_name, null)
847 INTO
848 l_invoice_supplier_name,
849 l_invoice_number,
850 l_invoice_date,
851 l_invoice_description,
852 l_supplier_role
853 FROM
854 ap_invoices_all AI,
855 po_vendors PV,
856 po_vendor_sites_all PVS,
857 fnd_user u
858 WHERE
859 AI.invoice_id = p_invoice_id AND
860 AI.vendor_id = PV.vendor_id AND
861 AI.vendor_site_id = PVS.vendor_site_id(+) and
862 u.user_id = ai.created_by;
863
864 l_debug_info := 'invoke_ap_workflow: iteration = ' || l_iteration ||
865 ', itemkey = '|| p_item_key ||
866 ', supplier_name = '|| l_invoice_supplier_name ||
867 ', invoice_number = '|| l_invoice_number ||
868 ', invoice_date = '|| l_invoice_date ||
869 ', supplier_role = '|| l_supplier_role;
870 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
871 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
872 END IF;
873
874 -- l_iteration := substr(p_item_key, instr(p_item_key,'_')+1, length(p_item_key));
875 l_iteration := to_number(substr(p_item_key, instr(p_item_key,'_')+1));
876 WF_ENGINE.setItemAttrNumber('APINVLDP',p_item_key, 'ITERATION', l_iteration);
877 WF_ENGINE.setItemAttrText('APINVLDP',p_item_key, 'INVOICE_SUPPLIER_NAME', l_invoice_supplier_name);
878 WF_ENGINE.setItemAttrText('APINVLDP',p_item_key, 'INVOICE_NUMBER', l_invoice_number);
879 WF_ENGINE.setItemAttrText('APINVLDP',p_item_key, 'INVOICE_DESCRIPTION', l_invoice_description);
880 WF_ENGINE.setItemAttrDate('APINVLDP',p_item_key, 'INVOICE_DATE', l_invoice_date);
881 WF_ENGINE.setItemAttrText('APINVLDP',p_item_key, 'SUPPLIER_ROLE', l_supplier_role);
882
883 l_debug_info := 'workflow process attributes set. ';
884 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
885 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
886 END IF;
887
888
889 --
890 -- Starting the process
891 --
892 l_debug_info := 'workflow process starting... ';
893 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
894 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
895 END IF;
896
897 WF_ENGINE.startProcess('APINVLDP', p_item_key);
898 l_debug_info := 'workflow process started. ';
899 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
900 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
901 END IF;
902
903
904
905 WF_ENGINE.launchProcess('APINVLDP',p_item_key, 'DISPUTE_MAIN');
906 l_debug_info := 'workflow process launched. ';
907 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
908 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
909 END IF;
910
911 commit;
912
913 EXCEPTION
914 WHEN OTHERS THEN
915 IF (SQLCODE <> -20001) THEN
916 rollback;
917 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
918 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
919 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
920 FND_MESSAGE.SET_TOKEN('PARAMETERS',
921 ' P_invoice_id = ' || p_invoice_id
922 ||' P_org_id = ' || p_org_id);
923 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
924 END IF;
925
926 APP_EXCEPTION.RAISE_EXCEPTION;
927
928 END invoke_ap_workflow;
929 */
930
931 /*=============================================================================
932 | public procedure override_tax
933 | this is a wrapper procedure for overriding tax
934 |
935 | Parameters
936 | P_invoice_id - invoice id
937 | P_calling_sequence - For debugging purpose
938 |
939 *===========================================================================*/
940 PROCEDURE override_tax(
941 P_Invoice_id IN NUMBER,
942 P_Calling_Mode IN VARCHAR2,
943 P_Override_Status IN VARCHAR2,
944 P_Event_Id IN NUMBER,
945 P_All_Error_Messages IN VARCHAR2,
946 P_Error_Code OUT NOCOPY VARCHAR2,
947 P_Calling_Sequence IN VARCHAR2)
948 IS
949
950 l_curr_calling_sequence VARCHAR2(2000);
951 l_debug_info VARCHAR2(2000);
952 l_api_name VARCHAR2(50);
953 l_result BOOLEAN;
954
955 BEGIN
956
957 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.override_tax <- ' ||
958 p_calling_sequence;
959
960 l_api_name := 'override_tax';
961 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
962 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.override_tax(+)');
963 END IF;
964
965 l_debug_info := 'calling AP_ETAX_SERVICES_PKG.Override_Tax: '
966 ||' P_invoice_id = ' || p_invoice_id
967 ||', P_calling_mode = ' || p_calling_mode
968 ||', P_override_status = ' || p_override_status
969 ||', P_event_id = ' || P_event_id;
970 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
971 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
972 END IF;
973
974 l_result := AP_ETAX_SERVICES_PKG.Override_Tax(
975 P_Invoice_id => p_invoice_id,
976 P_Calling_Mode => p_calling_mode,
977 P_Override_Status => p_override_status,
978 P_Event_Id => p_event_id,
979 P_All_Error_Messages => p_all_error_messages,
980 P_Error_Code => p_error_code,
981 P_Calling_Sequence => l_curr_calling_sequence);
982
983 l_debug_info := 'AP_ETAX_SERVICES_PKG.override_tax called: '||
984 ' error_code = ' || p_error_code;
985 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
986 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
987 END IF;
988
989 -- return l_result;
990 -- ISP:CodeCleanup Bug 5256954
991 -- commit;
992
993 EXCEPTION
994 WHEN OTHERS THEN
995 IF (SQLCODE <> -20001) THEN
996 rollback;
997 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
998 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
999 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1000 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1001 ' P_invoice_id = ' || p_invoice_id
1002 ||', P_calling_mode = ' || p_calling_mode
1003 ||', P_override_status = ' || p_override_status
1004 ||', P_event_id = ' || P_event_id
1005 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1006 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1007 END IF;
1008
1009 APP_EXCEPTION.RAISE_EXCEPTION;
1010
1011 END override_tax;
1012
1013 /*=============================================================================
1014 | public procedure populate_payment_terms
1015 | this procedure populates payment terms id and date to invoice header
1016 |
1017 | Parameters
1018 | P_invoice_id - invoice id
1019 | P_calling_sequence - For debugging purpose
1020 |
1021 *===========================================================================*/
1022 PROCEDURE populate_payment_terms(
1023 P_Invoice_id IN NUMBER,
1024 P_Calling_Sequence IN VARCHAR2)
1025 IS
1026
1027 l_curr_calling_sequence VARCHAR2(2000);
1028 l_debug_info VARCHAR2(2000);
1029 l_api_name VARCHAR2(50);
1030 l_terms_Id NUMBER;
1031 l_terms_date DATE;
1032
1033 BEGIN
1034
1035 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.populate_payment_terms <- ' ||
1036 p_calling_sequence;
1037
1038 l_api_name := 'populate_payment_terms';
1039 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1040 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.populate_payment_terms(+)');
1041 END IF;
1042
1043 get_payment_terms (p_invoice_id => p_invoice_id,
1044 p_terms_id => l_terms_id,
1045 p_terms_date => l_terms_date,
1046 p_calling_sequence => l_curr_calling_sequence);
1047
1048 --bug 9138008, Updating date columns after truncating time-stamp.
1049 --Also added updates for invoice_date and invoice_received_date as
1050 --terms_date is derived based on these two fields among others
1051
1052 update ap_invoices_all
1053 set terms_id = l_terms_id,
1054 terms_date = trunc(l_terms_date),
1055 invoice_date = trunc(invoice_date),
1056 invoice_received_date = trunc(invoice_received_date)
1057 where invoice_id = p_invoice_id;
1058
1059 l_debug_info := 'invoice header record updated with terms id: '||
1060 l_terms_id ||', terms_date = '|| l_terms_date;
1061 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1062 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1063 END IF;
1064
1065 -- commit;
1066
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 IF (SQLCODE <> -20001) THEN
1070 rollback;
1071 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1072 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1073 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1074 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1075 ' P_invoice_id = ' || p_invoice_id
1076 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1077 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1078 END IF;
1079
1080 APP_EXCEPTION.RAISE_EXCEPTION;
1081
1082 END populate_payment_terms;
1083
1084 /*=============================================================================
1085 | public procedure populate_doc_sequence
1086 | this procedure populates document sequence to invoice header
1087 |
1088 | Parameters
1089 | P_invoice_id - invoice id
1090 | P_calling_sequence - For debugging purpose
1091 |
1092 *===========================================================================*/
1093 PROCEDURE populate_doc_sequence(
1094 P_Invoice_id IN NUMBER,
1095 p_sequence_numbering IN VARCHAR2,
1096 p_calling_sequence IN VARCHAR2)
1097 IS
1098
1099 l_doc_category_code ap_invoices.doc_category_code%TYPE;
1100 l_db_sequence_value ap_invoices.doc_sequence_value%TYPE;
1101 l_db_sequence_id ap_invoices.doc_sequence_id%TYPE;
1102 l_db_sequence_name VARCHAR2(1000);
1103 l_curr_calling_sequence VARCHAR2(2000);
1104 l_debug_info VARCHAR2(2000);
1105 l_api_name VARCHAR2(50);
1106
1107 BEGIN
1108
1109 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.populate_doc_sequence<- ' ||
1110 p_calling_sequence;
1111
1112 l_api_name := 'populate_doc_sequence';
1113 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1114 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.populate_doc_sequence(+)');
1115 END IF;
1116
1117 get_doc_sequence(
1118 p_invoice_id => p_invoice_id,
1119 p_sequence_numbering => p_sequence_numbering,
1120 p_doc_category_code => l_doc_category_code,
1121 p_db_sequence_value => l_db_sequence_value,
1122 p_db_seq_name => l_db_sequence_name,
1123 p_db_sequence_id => l_db_sequence_id,
1124 p_calling_sequence => l_curr_calling_sequence);
1125
1126 l_debug_info := 'got the doc category code and sequence: '||
1127 l_doc_category_code ||', doc_seq_value = '|| l_db_sequence_value
1128 || ', doc_seq_id = ' || l_db_sequence_id;
1129 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1130 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1131 END IF;
1132
1133 update ap_invoices_all
1134 set doc_category_code = l_doc_category_code,
1135 doc_sequence_value = l_db_sequence_value,
1136 doc_sequence_id = l_db_sequence_id
1137 where invoice_id = p_invoice_id;
1138
1139 l_debug_info := 'invoice header record updated with doc category code: '||
1140 l_doc_category_code ||', doc_seq_value = '|| l_db_sequence_value
1141 || ', doc_seq_id = ' || l_db_sequence_id;
1142 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1143 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1144 END IF;
1145
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 IF (SQLCODE <> -20001) THEN
1149 rollback;
1150 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1151 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1152 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1153 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1154 ' P_invoice_id = ' || p_invoice_id
1155 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1156 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1157 END IF;
1158
1159 APP_EXCEPTION.RAISE_EXCEPTION;
1160
1161 END populate_doc_sequence;
1162
1163 PROCEDURE update_invoice_header(
1164 p_invoice_id IN NUMBER,
1165 p_sequence_numbering IN VARCHAR2,
1166 p_calling_sequence IN VARCHAR2)
1167
1168 IS
1169 l_item_sum ap_invoices_all.invoice_amount%TYPE;
1170 l_tax_sum ap_invoices_all.invoice_amount%TYPE;
1171 l_misc_sum ap_invoices_all.invoice_amount%TYPE;
1172 l_frt_sum ap_invoices_all.invoice_amount%TYPE;
1173 l_retained_sum ap_invoices_all.invoice_amount%TYPE;
1174 l_curr_calling_sequence VARCHAR2(2000);
1175 l_debug_info VARCHAR2(500);
1176 l_api_name VARCHAR2(50);
1177 l_hold_count NUMBER;
1178 l_line_count NUMBER;
1179 l_line_total NUMBER;
1180 l_Sched_Hold_count NUMBER;
1181 l_inv_currency_code ap_invoices_all.invoice_currency_code%TYPE;
1182 l_invoice_date ap_invoices_all.invoice_date%TYPE;
1183 l_base_currency_code ap_invoices_all.invoice_currency_code%TYPE;
1184 l_default_exchange_Rate_type ap_invoices_all.exchange_rate_type%TYPE;
1185 l_exchange_rate ap_invoices_all.exchange_rate%TYPE;
1186 l_exchange_date ap_invoices_all.exchange_date%TYPE;
1187 l_requester_id ap_invoices_all.requester_id%TYPE;
1188
1189 --Bug 9239655
1190 l_exclude_freight_from_disc VARCHAR2(1) :='N';
1191 l_exclude_tax_from_disc VARCHAR2(1) :='N';
1192 NULL_EXCHG_RATE EXCEPTION;
1193 x_msg_count NUMBER;
1194 x_msg_data VARCHAR2(1000);
1195
1196 l_lines_cnt NUMBER; /*Bug 14386893*/
1197 BEGIN
1198 -- Update the calling sequence
1199
1200 l_curr_calling_sequence := 'update_invoice_header <-'||P_calling_sequence;
1201
1202 l_api_name := 'update_invoice_header';
1203
1204
1205 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1206 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.update_invoice_header(+)');
1207 END IF;
1208
1209 l_debug_info := 'Step 1. update invoice amount: invoice_id = ';
1210 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1211 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1212 END IF;
1213
1214 BEGIN
1215 -- Bug 5407726 ISP Code cleanup XBuild9
1216 SELECT SUM(DECODE(line_type_lookup_code,'ITEM',NVL(amount, 0) - NVL(included_tax_amount, 0) ,0)) ITEM_SUM,
1217 --Bug 5345946 XBuild7 Code Cleanup
1218 SUM(DECODE(line_type_lookup_code,'TAX',amount,0)) + SUM(NVL(included_tax_amount, 0)) TAX_SUM,
1219 SUM(DECODE(line_type_lookup_code,'MISCELLANEOUS',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) MISC_SUM, --Bug
1220 SUM(DECODE(line_type_lookup_code,'FREIGHT',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) FREIGHT_SUM,
1221 sum(decode(line_type_lookup_code, 'ITEM', NVL(retained_amount, 0), 0)) RETAINAGE_SUM,
1222 count(*) /*Bug 14386893*/
1223 INTO l_item_sum, l_tax_sum, l_misc_sum, l_frt_sum, l_retained_sum, l_lines_cnt
1224 FROM ap_invoice_lines_all
1225 WHERE invoice_id = p_invoice_id;
1226
1227
1228 EXCEPTION
1229 WHEN NO_DATA_FOUND THEN
1230 l_debug_info := 'no lines found for the invoice id = '|| p_invoice_id;
1231 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
1232 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, l_debug_info);
1233 END IF;
1234 END;
1235
1236 --Bug 9239655
1237 BEGIN
1238 SELECT decode(assa.exclude_freight_from_discount,NULL,nvl(aps.exclude_freight_from_discount,'N'),assa.exclude_freight_from_discount)
1239 INTO l_exclude_freight_from_disc
1240 FROM ap_suppliers aps,
1241 ap_supplier_sites_all assa
1242 WHERE aps.vendor_id = (select vendor_id from ap_invoices_all where invoice_id = p_invoice_id)
1243 AND assa.vendor_id = aps.vendor_id
1244 AND assa.vendor_site_id = (select vendor_site_id from ap_invoices_all where invoice_id = p_invoice_id);
1245
1246 SELECT nvl(asp.disc_is_inv_less_tax_flag,'N')
1247 INTO l_exclude_tax_from_disc
1248 FROM ap_system_parameters asp
1249 WHERE asp.org_id = (select org_id from ap_invoices_all where invoice_id = p_invoice_id);
1250
1251 EXCEPTION
1252 WHEN OTHERS THEN NULL;
1253 END;
1254
1255 -- don't do anything if lines don't exist
1256 /*Bug 14386893*/
1257 if (l_lines_cnt > 0) then
1258 update ap_invoices_all ai
1259 set invoice_amount = l_item_sum + l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,
1260 amount_applicable_to_discount = l_item_sum + l_misc_sum + l_retained_sum
1261 /* Bug 9239655: Added conditions for adding Freight and Tax lines sum to
1262 amount_applicable_to_discount */
1263 /*+ l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,*/
1264 + decode(l_exclude_tax_from_disc,'Y',0,l_tax_sum)
1265 + decode(l_exclude_freight_from_disc,'Y',0,l_frt_sum),
1266 net_of_retainage_flag = DECODE(l_retained_sum, 0, 'N', 'Y')
1267 where ai.invoice_id = p_invoice_id;
1268 end if;
1269
1270
1271
1272 l_debug_info := 'Step 2. populate document sequence: invoice_id = '||
1273 p_invoice_id || ', sequence_numbering = ' || p_sequence_numbering;
1274 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1275 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1276 END IF;
1277
1278 populate_doc_sequence(
1279 p_invoice_id => p_invoice_id,
1280 p_sequence_numbering => p_sequence_numbering,
1281 p_calling_sequence => l_curr_calling_sequence);
1282
1283
1284 l_debug_info := 'Step 3. populate payment terms';
1285 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1286 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1287 END IF;
1288
1289 populate_payment_terms(
1290 p_invoice_id => p_invoice_id,
1291 p_calling_sequence => l_curr_calling_sequence);
1292
1293 l_debug_info := 'invoice header updated. ';
1294 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1295 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1296 END IF;
1297
1298
1299 -- Bug 5470344 XBuild11 Code cleanup
1300 -- For Foriegn currency invoices, exchange rate is derieved from the
1301 -- OU settings. However if OU has exchange rate of NULL or 'User'
1302 -- ISP feature defaults the exchange rate to be of type 'Corporate'
1303 SELECT ai.invoice_currency_code,
1304 ai.invoice_date,
1305 asp.base_currency_code,
1306 DECODE(asp.default_exchange_rate_type,
1307 NULL, 'Corporate',
1308 'User', 'Corporate' ,
1309 asp.default_exchange_rate_type),
1310 ap_utilities_pkg.get_exchange_rate(
1311 ai.invoice_currency_code,
1312 asp.base_currency_code,
1313 DECODE(asp.default_exchange_rate_type,
1314 NULL, 'Corporate',
1315 'User', 'Corporate' ,
1316 asp.default_exchange_rate_type),
1317 ai.invoice_date,
1318 'ISP'),
1319 ai.gl_date, /* Bug 16314016 ai.invoice_date*/
1320 requester_id
1321 INTO l_inv_currency_code,
1322 l_invoice_date,
1323 l_base_currency_code,
1324 l_default_exchange_Rate_type,
1325 l_exchange_rate,
1326 l_exchange_date,
1327 l_requester_id
1328 FROM ap_invoices_all ai,
1329 ap_system_parameters_all asp
1330 WHERE ai.org_id = asp.org_id
1331 and ai.invoice_id = p_invoice_id;
1332
1333
1334 IF l_base_currency_code <> l_inv_currency_code THEN
1335 /* BUG 11867431 start */
1336 IF l_exchange_rate is null THEN
1337 fnd_message.set_name ('SQLAP', 'AP_EXCHANGE_RATE');
1338 fnd_message.set_token ('P_EXCHANGE_RATE_TYPE',
1339 l_default_exchange_Rate_type
1340 );
1341 fnd_message.set_token ('CURRENCY', l_inv_currency_code);
1342 fnd_message.set_token ('P_AS_OF_DATE', l_exchange_date);
1343 fnd_msg_pub.add;
1344 RAISE NULL_EXCHG_RATE;
1345 END IF;
1346 /* BUG 11867431 end */
1347 UPDATE ap_invoices_all
1348 SET exchange_rate_type = l_default_exchange_rate_type,
1349 exchange_rate = l_exchange_rate,
1350 exchange_date = l_exchange_date
1351 WHERE invoice_id = p_invoice_id;
1352
1353 /* Bug 9768308 begin */
1354 IF (l_default_exchange_rate_type = 'User') THEN
1355
1356 UPDATE ap_invoices_all
1357 SET base_amount = ap_utilities_pkg.ap_round_currency(
1358 invoice_amount * l_exchange_rate,
1359 l_base_currency_code)
1360 WHERE invoice_id = p_invoice_id ;
1361
1362 ELSE
1363
1364 -- euro triangulation
1365 UPDATE ap_invoices_all
1366 SET base_amount = gl_currency_api.convert_amount(
1367 l_inv_currency_code,
1368 l_base_currency_code,
1369 l_exchange_date,
1370 l_default_exchange_rate_type,
1371 invoice_amount)
1372 WHERE invoice_id = p_invoice_id ;
1373
1374 END IF;
1375 /* Bug 9768308 end */
1376 END IF;
1377
1378 --Bug 5500186
1379 UPDATE ap_invoice_lines_all
1380 SET requester_id = l_requester_id
1381 WHERE line_type_lookup_code = 'ITEM'
1382 AND requester_id is NULL
1383 AND invoice_id = p_invoice_id;
1384
1385
1386 -- ISP:CodeCleanup Bug 5256954
1387 -- commit;
1388
1389 EXCEPTION
1390 WHEN NULL_EXCHG_RATE THEN
1391 FND_MSG_PUB.Count_And_Get
1392 ( p_count => x_msg_count,
1393 p_data => x_msg_data
1394 );
1395 APP_EXCEPTION.RAISE_EXCEPTION;
1396 WHEN OTHERS THEN
1397 IF (SQLCODE <> -20001) THEN
1398 rollback;
1399 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1400 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1401 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1402 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1403 ' P_invoice_id = ' || p_invoice_id
1404 || ', sequence_numbering = ' || p_sequence_numbering
1405 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1406 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1407 END IF;
1408
1409 APP_EXCEPTION.RAISE_EXCEPTION;
1410
1411 END update_invoice_header;
1412
1413 procedure create_distributions(p_invoice_id IN NUMBER) IS
1414 l_invoice_rec ap_approval_pkg.Invoice_Rec;
1415 l_base_currency_code ap_system_parameters_all.base_currency_code%TYPE;
1416
1417 l_invoice_id ap_invoices_all.invoice_id%type;
1418 l_invoice_num ap_invoices_all.invoice_num%type;
1419 l_org_id ap_invoices_all.org_id%type;
1420 l_invoice_amount ap_invoices_all.invoice_amount%type;
1421 l_base_amount ap_invoices_all.base_amount%type;
1422 l_exchange_rate ap_invoices_all.exchange_rate%type;
1423 l_invoice_currency_code ap_invoices_all.invoice_currency_code%type;
1424 l_invoice_amount_limit ap_supplier_sites_all.invoice_amount_limit%type;
1425 l_hold_future_payments_flag ap_supplier_sites_all.hold_future_payments_flag%type;
1426 l_invoice_type_lookup_code ap_invoices_all.invoice_type_lookup_code%type;
1427 l_exchange_date ap_invoices_all.exchange_date%type;
1428 l_exchange_rate_type ap_invoices_all.exchange_rate_type%type;
1429 l_vendor_id ap_invoices_all.vendor_id%type;
1430 l_invoice_date ap_invoices_all.invoice_date%type;
1431 l_disc_is_inv_less_tax_flag ap_invoices_all.disc_is_inv_less_tax_flag%type;
1432 l_exclude_freight_from_disc ap_invoices_all.exclude_freight_from_discount%type;
1433 l_tolerance_id ap_supplier_sites_all.tolerance_id%type;
1434 l_services_tolerance_id ap_supplier_sites_all.services_tolerance_id%type;
1435 l_error_code VARCHAR2(4000);
1436 l_curr_calling_sequence VARCHAR2(2000);
1437 l_debug_info VARCHAR2(500);
1438 CURSOR approve_invoice_cur IS
1439 SELECT AI.invoice_id,
1440 AI.invoice_num,
1441 AI.invoice_amount,
1442 AI.base_amount,
1443 AI.exchange_rate,
1444 AI.invoice_currency_code,
1445 PVS.invoice_amount_limit,
1446 nvl(PVS.hold_future_payments_flag,'N'),
1447 AI.invoice_type_lookup_code,
1448 AI.exchange_date,
1449 AI.exchange_rate_type,
1450 AI.vendor_id,
1451 AI.invoice_date,
1452 AI.org_id,
1453 nvl(AI.disc_is_inv_less_tax_flag,'N'),
1454 nvl(AI.exclude_freight_from_discount,'N'),
1455 pvs.tolerance_id,
1456 pvs.services_tolerance_id
1457 FROM ap_invoices_all AI,
1458 ap_suppliers PV,
1459 ap_supplier_sites_all PVS
1460 WHERE AI.invoice_id = p_invoice_id
1461 AND AI.vendor_id = PV.vendor_id
1462 AND AI.vendor_site_id = PVS.vendor_site_id;
1463 BEGIN
1464 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.create_distributions';
1465
1466 SELECT base_currency_code
1467 INTO l_base_currency_code
1468 FROM ap_system_parameters_all asp, ap_invoices_all ai
1469 WHERE ai.invoice_id = p_invoice_id
1470 AND asp.org_id = ai.org_id;
1471
1472 l_debug_info := 'Before OPEN Approve_Invoice_Cur';
1473 OPEN Approve_Invoice_Cur;
1474 l_debug_info := 'Before Fetch Approve_Invoice_Cur';
1475 FETCH Approve_Invoice_Cur
1476 INTO l_invoice_id,
1477 l_invoice_num,
1478 l_invoice_amount,
1479 l_base_amount,
1480 l_exchange_rate,
1481 l_invoice_currency_code,
1482 l_invoice_amount_limit,
1483 l_hold_future_payments_flag,
1484 l_invoice_type_lookup_code,
1485 l_exchange_date,
1486 l_exchange_rate_type,
1487 l_vendor_id,
1488 l_invoice_date,
1489 l_org_id,
1490 l_disc_is_inv_less_tax_flag,
1491 l_exclude_freight_from_disc,
1492 l_tolerance_id,
1493 l_services_tolerance_id;
1494 CLOSE Approve_Invoice_Cur;
1495
1496 l_invoice_rec.invoice_id := l_invoice_id;
1497 l_invoice_rec.invoice_num := l_invoice_num;
1498 l_invoice_rec.invoice_amount := l_invoice_amount;
1499 l_invoice_rec.base_amount := l_base_amount;
1500 l_invoice_rec.exchange_rate := l_exchange_rate;
1501 l_invoice_rec.invoice_currency_code := l_invoice_currency_code;
1502 l_invoice_rec.invoice_amount_limit := l_invoice_amount_limit;
1503 l_invoice_rec.hold_future_payments_flag := l_hold_future_payments_flag;
1504 l_invoice_rec.invoice_type_lookup_code := l_invoice_type_lookup_code;
1505 l_invoice_rec.exchange_date := l_exchange_date;
1506 l_invoice_rec.exchange_rate_type := l_exchange_rate_type;
1507 l_invoice_rec.vendor_id := l_vendor_id;
1508 l_invoice_rec.invoice_date := l_invoice_date;
1509 l_invoice_rec.org_id := l_org_id;
1510 l_invoice_rec.disc_is_inv_less_tax_flag := l_disc_is_inv_less_tax_flag;
1511 l_invoice_rec.exclude_freight_from_discount := l_exclude_freight_from_disc;
1512 l_invoice_rec.tolerance_id := l_tolerance_id;
1513 l_invoice_rec.services_tolerance_id := l_services_tolerance_id;
1514
1515 l_debug_info := 'Before AP_APPROVAL_PKG.Generate_Distributions';
1516 AP_APPROVAL_PKG.Generate_Distributions
1517 (p_invoice_rec => l_invoice_rec ,
1518 p_base_currency_code => l_base_currency_code,
1519 p_inv_batch_id => NULL,
1520 p_run_option => NULL,
1521 p_calling_sequence => l_curr_calling_sequence,
1522 x_error_code => l_error_code)
1523 ;
1524
1525
1526 EXCEPTION
1527 WHEN OTHERS THEN
1528 IF (SQLCODE <> -20001) THEN
1529 rollback;
1530 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1531 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1532 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1533 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1534 ' P_invoice_id = ' || p_invoice_id
1535 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1536 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1537 END IF;
1538
1539 APP_EXCEPTION.RAISE_EXCEPTION;
1540
1541 END;
1542
1543 -- Bug 5605359 (Prior to this bug fix Step 5 included Step 7. Once Tax is
1544 -- overridden the Pay Schedules need to be adjusted.
1545 -- ISP Flow is as follows --
1546 -- 1. Creation of Invoice Header
1547 -- 2. Creation of Invoice Lines
1548 -- 3. Updation of Invoice Lines with Retainage amt.
1549 -- 4. Calculate Tax(Call eTax) for Invoice Lines(Invoice Line Amt is Net of Retainage)
1550 -- 5. Updation of the Invoice(update_invoice_header)
1551 -- 6. Override Tax(Update Summary Tax Lines)
1552 -- 7. Updation of the Invoice with the correct Invoice Amt after Tax Override
1553 -- and creation of the Pay Schedules(update_invoice_header2)
1554 -- 8. For PO/RCV Matched Lines (Call PO Shipment/RCV Shipment Line match)
1555 -- Recoupment adjusts already created Pay Schedules.
1556 -- 9. Commit.
1557 PROCEDURE update_invoice_header2(
1558 p_invoice_id IN NUMBER,
1559 p_calling_sequence IN VARCHAR2)
1560
1561 IS
1562 l_item_sum ap_invoices_all.invoice_amount%TYPE;
1563 l_tax_sum ap_invoices_all.invoice_amount%TYPE;
1564 l_misc_sum ap_invoices_all.invoice_amount%TYPE;
1565 l_frt_sum ap_invoices_all.invoice_amount%TYPE;
1566 l_retained_sum ap_invoices_all.invoice_amount%TYPE;
1567 l_curr_calling_sequence VARCHAR2(2000);
1568 l_debug_info VARCHAR2(500);
1569 l_api_name VARCHAR2(50);
1570 l_hold_count NUMBER;
1571 l_line_count NUMBER;
1572 l_line_total NUMBER;
1573 l_Sched_Hold_count NUMBER;
1574 l_inv_currency_code ap_invoices_all.invoice_currency_code%TYPE;
1575 l_pay_currency_code ap_invoices_all.invoice_currency_code%TYPE;
1576
1577 l_invoice_date ap_invoices_all.invoice_date%TYPE;
1578 l_base_currency_code ap_invoices_all.invoice_currency_code%TYPE;
1579 l_default_exchange_Rate_type ap_invoices_all.exchange_rate_type%TYPE;
1580 l_exchange_rate ap_invoices_all.exchange_rate%TYPE;
1581 l_exchange_rate_type ap_invoices_all.exchange_rate_type%TYPE;
1582 l_exchange_date ap_invoices_all.exchange_date%TYPE;
1583 l_requester_id ap_invoices_all.requester_id%TYPE;
1584
1585 l_wfitemkey VARCHAR2(50);
1586 l_dist_set_id ap_supplier_sites_all.distribution_set_id%TYPE;
1587 -- Bug 6859035
1588 l_period_name ap_invoice_lines_all.period_name%TYPE;
1589 l_gl_date ap_invoice_lines_all.accounting_date%TYPE;
1590 l_org_id ap_invoices_all.org_id%TYPE;
1591
1592 -- Bug 7706967 : Start
1593 l_vendor_name ap_suppliers.vendor_name%TYPE;
1594 l_vendor_id ap_suppliers.vendor_id%TYPE;
1595 l_vendor_site_code ap_supplier_sites_all.vendor_site_code%TYPE;
1596 l_vendor_site_id ap_supplier_sites_all.vendor_site_id%TYPE;
1597 -- Bug 7706967 : End
1598
1599 l_payment_priority ap_supplier_sites_all.payment_priority%TYPE ; -- B# 8649741
1600
1601 --CARS Project. Bug 8865603.
1602 l_iter number := 0;
1603
1604 --Bug 9239655
1605 l_exclude_freight_from_disc VARCHAR2(1) :='N';
1606 l_exclude_tax_from_disc VARCHAR2(1) :='N';
1607
1608 -- Bug 9531531
1609 l_type_1099 ap_invoice_lines_all.type_1099%TYPE;
1610 -- Bug 10040759
1611 l_type_1099_po ap_invoice_lines_all.type_1099%TYPE;
1612 l_log_msg VARCHAR2(1000);
1613 l_lines_cnt NUMBER; /*Bug 14386893*/
1614 NULL_EXCHG_RATE EXCEPTION;
1615
1616
1617 BEGIN
1618 -- Update the calling sequence
1619
1620 l_curr_calling_sequence := 'update_invoice_header2 <-'||P_calling_sequence;
1621
1622 l_api_name := 'update_invoice_header2';
1623
1624 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1625 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.update_invoice_header2(+)');
1626 END IF;
1627
1628 -- Bug 6859035. Accounting date and period name are not getting
1629 -- stamped properly in invoice header and lines. In all the cases
1630 -- sysdate is being used to calculate the gl date and period name
1631 -- instead of invoice date and Payables Options setup GL date basis
1632 -- is not taken into consideration while setting the date and period.
1633 -- Following updates will stamp the proper gl date and period on invoice
1634 -- header and lines with regards to the payables options GL date basis
1635 -- setup. For distributions, date the period will be defaulted
1636 -- from the line. Following are setting the accounting date: -
1637 -- Header - InvDetailSvrCmd.defaultHeaderAttributes() using sysdate to
1638 -- to calculate the accounting date instead of invoice date.
1639 -- Line - ApInvoiceLinesAllEOImpl.create() setting accounting date
1640 -- to sysdate.
1641 -- Below update will over write the the accounting date and gl date
1642 -- whatever is set in the java code with the proper values.
1643
1644 BEGIN
1645 l_debug_info := 'Updating gl date and period name at header and line level.';
1646 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1647 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1648 END IF;
1649
1650 SELECT ai.invoice_date, org_id
1651 INTO l_invoice_date, l_org_id
1652 FROM ap_invoices_all ai
1653 WHERE ai.invoice_id = p_invoice_id ;
1654
1655 -- Bug 8405782
1656 AP_UTILITIES_PKG.Get_gl_date_and_period_1(
1657 P_Date => l_invoice_date,
1658 P_Period_Name => l_period_name,
1659 P_GL_Date => l_gl_date,
1660 P_Org_Id => l_org_id) ;
1661
1662 UPDATE ap_invoices_all
1663 SET gl_date = l_gl_date
1664 WHERE invoice_id = p_invoice_id ;
1665
1666 -- Bug 8345877 commenting out the below fix as
1667 -- remit_to columsn are no more mandatory.
1668
1669 -- Bug 7706967 - Start
1670 /*SELECT nvl(aps.vendor_name, hzp.party_name)
1671 INTO l_vendor_name
1672 FROM ap_suppliers aps, hz_parties hzp, ap_invoices_all ai
1673 WHERE ai.invoice_id = p_invoice_id
1674 AND aps.vendor_id = ai.vendor_id
1675 AND hzp.party_id = aps.party_id
1676 AND ROWNUM = 1;
1677
1678 SELECT apss.vendor_site_code, ai.vendor_id, ai.vendor_site_id
1679 INTO l_vendor_site_code, l_vendor_id, l_vendor_site_id
1680 FROM ap_supplier_sites_all apss, ap_invoices_all ai
1681 WHERE ai.invoice_id = p_invoice_id
1682 AND apss.vendor_site_id = ai.vendor_site_id;
1683
1684 UPDATE ap_invoices_all
1685 SET remit_to_supplier_id = l_vendor_id,
1686 remit_to_supplier_name = l_vendor_name,
1687 remit_to_supplier_site_id = l_vendor_site_id,
1688 remit_to_supplier_site = l_vendor_site_code,
1689 relationship_id = -1
1690 WHERE invoice_id = p_invoice_id;*/ --bug 8345877
1691 -- Bug 7706967 - End
1692
1693 -- Bug 9531531 Begin
1694 SELECT po.type_1099
1695 INTO l_type_1099
1696 FROM ap_suppliers po,
1697 ap_invoices_all ai
1698 WHERE po.vendor_id = ai.vendor_id
1699 AND ai.invoice_id = p_invoice_id;
1700
1701 l_debug_info := 'Derived type_1099 from Supplier: ' || l_type_1099;
1702 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1703 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1704 END IF;
1705 -- Bug 9531531 End
1706
1707 -- Bug 10040759 Begin
1708 FOR line in (SELECT * from ap_invoice_lines_all where invoice_id = p_invoice_id)
1709 LOOP
1710 BEGIN
1711 SELECT type_1099
1712 INTO l_type_1099_po
1713 FROM po_lines_all
1714 WHERE po_header_id = line.po_header_id
1715 AND rownum = 1;
1716 EXCEPTION
1717 WHEN NO_DATA_FOUND THEN NULL;
1718 END;
1719 UPDATE ap_invoice_lines_all
1720 SET type_1099 = NVL(l_type_1099_po, l_type_1099)
1721 WHERE invoice_id = line.invoice_id
1722 AND line_number = line.line_number;
1723 END LOOP;
1724 -- Bug 10040759 End
1725
1726 UPDATE ap_invoice_lines_all
1727 SET accounting_date = l_gl_date,
1728 period_name = l_period_name
1729 --type_1099 = l_type_1099 -- Bug 9531531
1730 WHERE invoice_id = p_invoice_id ;
1731
1732 EXCEPTION
1733 WHEN NO_DATA_FOUND THEN
1734 l_debug_info := 'No invoice found to update gl date and period. type_1099 = ' || l_type_1099; -- Bug 9531531 Added type_1099
1735 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1736 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1737 END IF;
1738 END ;
1739 -- End bug 6859035
1740
1741 l_debug_info := 'Step 1. update invoice amount: invoice_id = ';
1742 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1743 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1744 END IF;
1745
1746 BEGIN
1747
1748 SELECT SUM(DECODE(line_type_lookup_code,'ITEM',NVL(amount, 0) - NVL(included_tax_amount, 0) ,0)) ITEM_SUM,
1749 SUM(DECODE(line_type_lookup_code,'TAX',amount,0)) + SUM(NVL(included_tax_amount, 0)) TAX_SUM,
1750 SUM(DECODE(line_type_lookup_code,'MISCELLANEOUS',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) MISC_SUM, --Bug
1751 SUM(DECODE(line_type_lookup_code,'FREIGHT',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) FREIGHT_SUM,
1752 sum(decode(line_type_lookup_code, 'ITEM', NVL(retained_amount, 0), 0)) RETAINAGE_SUM,
1753 count(*) /*Bug 14386893*/
1754 INTO l_item_sum, l_tax_sum, l_misc_sum, l_frt_sum, l_retained_sum, l_lines_cnt
1755 FROM ap_invoice_lines_all
1756 WHERE invoice_id = p_invoice_id;
1757 EXCEPTION
1758 WHEN NO_DATA_FOUND THEN
1759 l_debug_info := 'no lines found for the invoice id = '|| p_invoice_id;
1760 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
1761 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, l_debug_info);
1762 END IF;
1763 END;
1764
1765 --Bug 9239655
1766 BEGIN
1767 SELECT decode(assa.exclude_freight_from_discount,NULL,nvl(aps.exclude_freight_from_discount,'N'),assa.exclude_freight_from_discount)
1768 INTO l_exclude_freight_from_disc
1769 FROM ap_suppliers aps,
1770 ap_supplier_sites_all assa
1771 WHERE aps.vendor_id = (select vendor_id from ap_invoices_all where invoice_id = p_invoice_id)
1772 AND assa.vendor_id = aps.vendor_id
1773 AND assa.vendor_site_id = (select vendor_site_id from ap_invoices_all where invoice_id = p_invoice_id);
1774
1775 SELECT nvl(asp.disc_is_inv_less_tax_flag,'N')
1776 INTO l_exclude_tax_from_disc
1777 FROM ap_system_parameters asp
1778 WHERE asp.org_id = (select org_id from ap_invoices_all where invoice_id = p_invoice_id);
1779
1780 EXCEPTION
1781 WHEN OTHERS THEN NULL;
1782 END;
1783
1784
1785 -- don't do anything if lines don't exist
1786 /*Bug 14386893*/
1787 if (l_lines_cnt > 0) then
1788 update ap_invoices_all ai
1789 set invoice_amount = l_item_sum + l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,
1790 amount_applicable_to_discount = l_item_sum + l_misc_sum + l_retained_sum
1791 /* Bug 9239655: Added conditions for adding Freight and Tax lines sum to
1792 amount_applicable_to_discount */
1793 /*+ l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,*/
1794 + decode(l_exclude_tax_from_disc,'Y',0,l_tax_sum)
1795 + decode(l_exclude_freight_from_disc,'Y',0,l_frt_sum),
1796 net_of_retainage_flag = DECODE(l_retained_sum, 0, 'N', 'Y'),
1797 APPROVAL_ITERATION = (nvl(approval_iteration, 0) + 1 ) --Needed for workflow process. Modified for CARS Project. Bug 8865603.
1798 where ai.invoice_id = p_invoice_id;
1799
1800 end if;
1801
1802 -- Bug 16361038, added the below code to update
1803 -- the base amount on Invoice header in case the tax amount
1804 -- overridden before Submitting the Invoice
1805 --
1806
1807 SELECT ai.invoice_currency_code,
1808 ai.invoice_date,
1809 asp.base_currency_code,
1810 DECODE(asp.default_exchange_rate_type,
1811 NULL, 'Corporate',
1812 'User', 'Corporate' ,
1813 asp.default_exchange_rate_type),
1814 ap_utilities_pkg.get_exchange_rate(
1815 ai.invoice_currency_code,
1816 asp.base_currency_code,
1817 DECODE(asp.default_exchange_rate_type,
1818 NULL, 'Corporate',
1819 'User', 'Corporate' ,
1820 asp.default_exchange_rate_type),
1821 ai.invoice_date,
1822 'ISP'),
1823 ai.invoice_date,
1824 requester_id
1825 INTO l_inv_currency_code,
1826 l_invoice_date,
1827 l_base_currency_code,
1828 l_default_exchange_Rate_type,
1829 l_exchange_rate,
1830 l_exchange_date,
1831 l_requester_id
1832 FROM ap_invoices_all ai,
1833 ap_system_parameters_all asp
1834 WHERE ai.org_id = asp.org_id
1835 and ai.invoice_id = p_invoice_id;
1836
1837
1838 IF l_base_currency_code <> l_inv_currency_code THEN
1839 /* BUG 11867431 start */
1840 IF l_exchange_rate is null THEN
1841 fnd_message.set_name ('SQLAP', 'AP_EXCHANGE_RATE');
1842 fnd_message.set_token ('P_EXCHANGE_RATE_TYPE',
1843 l_default_exchange_Rate_type
1844 );
1845 fnd_message.set_token ('CURRENCY', l_inv_currency_code);
1846 fnd_message.set_token ('P_AS_OF_DATE', l_exchange_date);
1847 fnd_msg_pub.add;
1848 RAISE NULL_EXCHG_RATE;
1849 END IF;
1850 /* BUG 11867431 end */
1851 UPDATE ap_invoices_all
1852 SET exchange_rate_type = l_default_exchange_rate_type,
1853 exchange_rate = l_exchange_rate,
1854 exchange_date = l_exchange_date
1855 WHERE invoice_id = p_invoice_id;
1856
1857 /* Bug 9768308 begin */
1858 IF (l_default_exchange_rate_type = 'User') THEN
1859
1860 UPDATE ap_invoices_all
1861 SET base_amount = ap_utilities_pkg.ap_round_currency(
1862 invoice_amount * l_exchange_rate,
1863 l_base_currency_code)
1864 WHERE invoice_id = p_invoice_id ;
1865
1866 ELSE
1867
1868 -- euro triangulation
1869 UPDATE ap_invoices_all
1870 SET base_amount = gl_currency_api.convert_amount(
1871 l_inv_currency_code,
1872 l_base_currency_code,
1873 l_exchange_date,
1874 l_default_exchange_rate_type,
1875 invoice_amount)
1876 WHERE invoice_id = p_invoice_id ;
1877
1878 END IF;
1879 /* Bug 9768308 end */
1880 END IF;
1881
1882
1883
1884 -- end Bug 16361038
1885 l_debug_info := 'Creating Pay Schedules ';
1886 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1887 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1888 END IF;
1889
1890
1891 -- B# 8649741 : get payment_priority from Sites table, do not use 99 as default
1892 BEGIN
1893
1894 SELECT nvl(payment_priority,99)
1895 INTO l_payment_priority
1896 FROM ap_supplier_sites_all s,
1897 ap_invoices_all i
1898 WHERE s.vendor_id = i.vendor_id
1899 AND s.vendor_site_id = i.vendor_site_id
1900 AND i.invoice_id = p_invoice_id ;
1901
1902 EXCEPTION
1903 WHEN NO_DATA_FOUND THEN
1904 l_payment_priority := 99 ;
1905 l_debug_info := 'no Site row found for the Invoice_id = '|| p_invoice_id ;
1906 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
1907 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, l_debug_info);
1908 END IF;
1909 END;
1910 -- B# 8649741 : end
1911
1912
1913 AP_INVOICES_POST_PROCESS_PKG.insert_children (
1914 X_invoice_id => p_invoice_id,
1915 --X_Payment_Priority => 99, .. B# 8649741
1916 X_Payment_Priority => l_payment_priority, -- B# 8649741
1917 X_Hold_count => l_hold_count,
1918 X_Line_count => l_line_count,
1919 X_Line_Total => l_line_total,
1920 X_calling_sequence => l_curr_calling_sequence,
1921 X_Sched_Hold_count => l_Sched_Hold_count);
1922
1923 l_debug_info := 'Call Workflow for Unmatched Invoices ';
1924 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1925 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1926 END IF;
1927
1928 IF (AP_ISP_UTILITIES_PKG.get_po_number_switcher(p_invoice_id) = 'UNMATCHED') THEN
1929 SELECT asu.distribution_set_id
1930 INTO l_dist_set_id
1931 FROM ap_supplier_sites_all asu,
1932 ap_invoices_all ai
1933 WHERE ai.vendor_site_id = asu.vendor_site_id
1934 AND ai.invoice_id = p_invoice_id;
1935 --Create Distributions
1936 IF l_dist_set_id is NOT NULL THEN
1937 create_distributions(p_invoice_id);
1938 END IF;
1939
1940 --CARS Project. Bug 8865603.
1941 BEGIN
1942
1943 SELECT nvl(approval_iteration, 0)
1944 INTO l_iter
1945 FROM ap_invoices_all
1946 WHERE invoice_id = p_invoice_id;
1947
1948 EXCEPTION
1949 WHEN OTHERS THEN
1950 NULL;
1951 END;
1952
1953 --Call Workflow API
1954 AP_WORKFLOW_PKG.create_invapp_process(p_invoice_id,
1955 l_iter,
1956 l_wfitemkey ) ;
1957
1958 END IF;
1959
1960
1961 EXCEPTION
1962 WHEN OTHERS THEN
1963 IF (SQLCODE <> -20001) THEN
1964 rollback;
1965 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1966 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1967 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1968 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1969 ' P_invoice_id = ' || p_invoice_id
1970 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1971 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1972 END IF;
1973
1974 APP_EXCEPTION.RAISE_EXCEPTION;
1975
1976 END update_invoice_header2;
1977
1978
1979 /*=============================================================================
1980 | public procedure match_invoice_lines
1981 | This procedure recursively matches all po/rcv matched item line
1982 | to the corresponding po/rcv shipment, given invoice id
1983 |
1984 | Description
1985 | When provided with a Invoice Line, based on the
1986 | information provided on the line will match the invoice line
1987 | appropriately to either PO or Receipt or perform Price/Quantity/Line
1988 | correction.
1989 |
1990 | Parameters
1991 | P_invoice_id - invoice id
1992 | P_calling_sequence - For debugging purpose
1993 |
1994 *===========================================================================*/
1995 Procedure Match_Invoice_Lines(
1996 P_Invoice_Id IN NUMBER,
1997 P_Calling_Sequence IN VARCHAR2) IS
1998
1999 CURSOR Invoice_Lines_cur IS
2000 SELECT line_number, quantity_invoiced, amount, po_line_location_id
2001 FROM ap_invoice_lines_all
2002 WHERE invoice_id = P_invoice_id
2003 AND NVL(discarded_flag, 'N' ) <> 'Y'
2004 AND nvl(generate_dists,'Y') <> 'D' --5090119
2005 AND line_type_lookup_code = 'ITEM';
2006
2007 l_invoice_line_number ap_invoice_lines_all.line_number%TYPE;
2008 l_po_line_location_id ap_invoice_lines_all.po_line_location_id%TYPE;
2009 l_quantity ap_invoice_lines_all.quantity_invoiced%TYPE;
2010 l_amount ap_invoice_lines_all.amount%TYPE;
2011 l_billed ap_invoice_lines_all.amount%TYPE;
2012 l_quantity_ordered ap_invoice_lines_all.amount%TYPE;
2013 l_amount_ordered ap_invoice_lines_all.amount%TYPE;
2014 l_matching_basis po_line_locations_all.matching_basis%TYPE;
2015 l_overbill_flag VARCHAR2(2);
2016 l_curr_calling_sequence VARCHAR2(2000);
2017 l_debug_info VARCHAR2(2000);
2018 l_api_name VARCHAR2(50);
2019
2020 BEGIN
2021
2022 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.match_invoice_lines<- ' ||
2023 p_calling_sequence;
2024
2025
2026
2027 l_api_name := 'match_invoice_lines';
2028 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2029 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.match_invoice_lines(+)');
2030 END IF;
2031
2032
2033 OPEN invoice_lines_cur;
2034
2035 LOOP
2036
2037 FETCH invoice_lines_cur INTO
2038 l_invoice_line_number, l_quantity, l_amount, l_po_line_location_id;
2039 EXIT WHEN ( invoice_lines_cur%NOTFOUND );
2040
2041 -- check if it's overbilled
2042 select decode(shipment_type, 'PREPAYMENT', amount_financed,
2043 decode(matching_basis, 'AMOUNT', amount_billed, quantity_billed)),
2044 matching_basis, quantity, amount
2045 into l_billed, l_matching_basis,
2046 l_quantity_ordered, l_amount_ordered
2047 from po_line_locations_all
2048 where line_location_id = l_po_line_location_id;
2049
2050 if ( l_matching_basis = 'AMOUNT' ) then
2051 if ( l_amount + l_billed > l_amount_ordered ) then
2052 l_overbill_flag := 'Y';
2053 else
2054 l_overbill_flag := 'N';
2055 end if;
2056 else -- quantity based
2057 if ( l_quantity + l_billed > l_quantity_ordered ) then
2058 l_overbill_flag := 'Y';
2059 else
2060 l_overbill_flag := 'N';
2061 end if;
2062 end if;
2063
2064 AP_MATCHING_UTILS_PKG.match_invoice_line(
2065 P_Invoice_Id => p_invoice_id,
2066 P_Invoice_Line_Number => l_invoice_line_number,
2067 P_Overbill_Flag => l_overbill_flag,
2068 P_Calling_Sequence => l_curr_calling_sequence);
2069
2070 END LOOP;
2071 CLOSE invoice_lines_cur;
2072
2073 l_debug_info := 'invoice matched. ';
2074 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2075 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2076 END IF;
2077 -- ISP:CodeCleanup Bug 5256954
2078 -- commit;
2079
2080 EXCEPTION
2081 WHEN OTHERS THEN
2082 IF (SQLCODE <> -20001) THEN
2083 rollback;
2084 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2085 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2086 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
2087 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2088 ' P_invoice_id = ' || p_invoice_id
2089 || ', invoice_line_number = ' || l_invoice_line_number
2090 ||', P_calling_sequence = ' || l_curr_calling_sequence);
2091 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2092 END IF;
2093
2094 If (Invoice_Lines_Cur%ISOPEN) Then
2095 CLOSE invoice_lines_cur;
2096 End if;
2097
2098 APP_EXCEPTION.RAISE_EXCEPTION;
2099
2100 END match_invoice_lines;
2101
2102
2103
2104
2105
2106 /*=============================================================================
2107 | public procedure get_sec_attr_value
2108 | This procedure retrieves the securing attribute value if there is ONLY
2109 | securing attribute set
2110 |
2111 | Description
2112 |
2113 | Parameters
2114 | P_user_id - user id
2115 | P_attr_code - ICX_SUPPLIER_ORG_ID, etc.
2116 | P_calling_sequence - For debugging purpose
2117 |
2118 *===========================================================================*/
2119 Procedure get_sec_attr_value (P_user_id IN NUMBER,
2120 P_attr_code IN VARCHAR2,
2121 P_attr_value OUT NOCOPY NUMBER,
2122 P_attr_value1 OUT NOCOPY VARCHAR2,
2123 P_party_id OUT NOCOPY NUMBER,
2124 P_Calling_Sequence IN VARCHAR2) IS
2125
2126 l_sec_attr_cnt NUMBER;
2127 l_curr_calling_sequence VARCHAR2(2000);
2128 l_debug_info VARCHAR2(2000);
2129 l_api_name VARCHAR2(50);
2130
2131 BEGIN
2132
2133 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.get_sec_attr_value <- ' ||
2134 p_calling_sequence;
2135
2136 l_api_name := 'get_sec_attr_value';
2137 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2138 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
2139 l_api_name,'AP_ISP_UTILITIES_PKG.get_sec_attr_value(+)');
2140 END IF;
2141
2142 -- we are assuming the attr_code is ICX_SUPPLIER_ORG_ID
2143 -- still pass it to allow reusability
2144 SELECT count(1) attr_value_num
2145 INTO l_sec_attr_cnt
2146 FROM ak_web_user_sec_attr_values awusav
2147 WHERE awusav.web_user_id = p_user_id
2148 AND awusav.attribute_code = p_attr_code
2149 AND awusav.attribute_application_id = 177;
2150
2151 l_debug_info := 'securing attribute count = ' || l_sec_attr_cnt;
2152 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2153 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2154 END IF;
2155
2156 IF ( l_sec_attr_cnt = 1 ) THEN
2157 SELECT nvl(to_char(asav.number_value), nvl(asav.varchar2_value, to_char(asav.date_value)))
2158 INTO p_attr_value
2159 FROM ak_web_user_sec_attr_values asav
2160 WHERE asav.attribute_application_id = 177
2161 AND asav.web_user_id = p_user_id
2162 AND asav.attribute_code = p_attr_code;
2163 END IF;
2164
2165 l_debug_info := 'securing attribute value = '|| p_attr_value;
2166 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2167 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2168 END IF;
2169
2170 IF ( p_attr_value is not null ) THEN
2171 IF ( p_attr_code = 'ICX_SUPPLIER_ORG_ID' ) THEN
2172
2173 --5077334, added party_id
2174 SELECT vendor_name, party_id
2175 INTO p_attr_value1, p_party_id
2176 FROM ap_suppliers
2177 WHERE vendor_id = p_attr_value;
2178 END IF;
2179 END IF;
2180 l_debug_info := 'securing attribute value1 = '|| p_attr_value1;
2181 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2182 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2183 END IF;
2184
2185 EXCEPTION
2186 WHEN OTHERS THEN
2187 IF (SQLCODE <> -20001) THEN
2188 rollback;
2189 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2190 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2191 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
2192 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2193 ' P_user_id = ' || p_user_id
2194 || ', p_attr_code = ' || p_attr_code
2195 ||', P_calling_sequence = ' || l_curr_calling_sequence);
2196 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2197 END IF;
2198
2199 APP_EXCEPTION.RAISE_EXCEPTION;
2200
2201 END get_sec_attr_value;
2202
2203 -- Used by Negotiation
2204 PROCEDURE Release_Hold(p_hold_id IN NUMBER) IS
2205 l_debug_info varchar2(100);
2206 l_release_reason ap_lookup_codes.description%type ; -- Bug 10176292
2207 BEGIN
2208
2209 l_debug_info := 'update ap_holds_all to release hold';
2210
2211 -- Bug 10176292.
2212 -- Now deriving release reason from ap_lookup_codes.
2213
2214 SELECT DESCRIPTION
2215 INTO l_release_reason
2216 FROM ap_lookup_codes
2217 WHERE LOOKUP_TYPE = 'HOLD CODE'
2218 AND LOOKUP_CODE = 'SUP/MGR RELEASE' ;
2219
2220 UPDATE ap_holds_all h
2221 SET release_lookup_code = 'SUP/MGR RELEASE',
2222 release_reason = l_release_reason, -- Bug 10176292.
2223 last_updated_by = FND_GLOBAL.user_id,
2224 last_update_date = SYSDATE,
2225 last_update_login = FND_GLOBAL.login_id
2226 WHERE hold_id = p_hold_id
2227 AND release_lookup_code IS NULL
2228 AND EXISTS(SELECT 'It is a releasable hold'
2229 FROM ap_hold_codes ahc
2230 WHERE ahc.hold_lookup_code = h.hold_lookup_code
2231 AND ahc.user_releaseable_flag = 'Y');
2232
2233
2234 EXCEPTION when others then
2235
2236 IF (SQLCODE <> -20001) THEN
2237 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2238 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2239 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'P_hold_id = '|| p_hold_id);
2240 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2241 END IF;
2242
2243 APP_EXCEPTION.RAISE_EXCEPTION;
2244 END Release_Hold;
2245
2246 -- Used by Negotiation
2247 --Bug 5595121 redesigned the api as per mswamina
2248 PROCEDURE update_po_matching_columns (p_line_location_id in number,
2249 p_po_distribution_id in number,
2250 p_quantity_change in number,
2251 p_amount_change in number,
2252 p_ap_uom in varchar2,
2253 p_invoice_id in number,
2254 p_line_number in number,
2255 p_error_code out nocopy varchar2,
2256 p_return_status out nocopy varchar2,
2257 p_calling_sequence in varchar2) is
2258
2259 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
2260 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
2261
2262 TYPE r_dist_info IS RECORD
2263 (po_distribution_id PO_DISTRIBUTIONS.po_distribution_id%TYPE, --Index Column
2264 invoice_distribution_id AP_INVOICE_DISTRIBUTIONS.invoice_distribution_id%TYPE,
2265 rcv_transaction_id RCV_TRANSACTIONS.transaction_id%TYPE,
2266 match_amount AP_INVOICE_DISTRIBUTIONS.amount%TYPE,
2267 match_quantity AP_INVOICE_DISTRIBUTIONS.quantity_invoiced%TYPE,
2268 pa_quantity AP_INVOICE_DISTRIBUTIONS.pa_quantity%TYPE,
2269 update_amount AP_INVOICE_DISTRIBUTIONS.amount%TYPE,
2270 update_quantity AP_INVOICE_DISTRIBUTIONS.quantity_invoiced%TYPE,
2271 update_pa_quantity AP_INVOICE_DISTRIBUTIONS.pa_quantity%TYPE
2272 );
2273 TYPE Dist_Tab_Type IS TABLE OF r_dist_info INDEX BY BINARY_INTEGER;
2274 x_dist_tab DIST_TAB_TYPE;
2275
2276 CURSOR po_dists IS
2277 SELECT po_distribution_id,
2278 invoice_distribution_id,
2279 rcv_transaction_id,
2280 amount,
2281 quantity_invoiced,
2282 pa_quantity
2283 FROM ap_invoice_distributions_all
2284 WHERE invoice_id = p_invoice_id
2285 AND invoice_line_number = p_line_number;
2286
2287 l_po_distribution_id PO_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
2288 l_invoice_distribution_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
2289 l_rcv_transaction_id RCV_TRANSACTIONS.TRANSACTION_ID%TYPE;
2290 l_match_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
2291 l_match_quantity AP_INVOICE_LINES.QUANTITY_INVOICED%TYPE;
2292 l_total_quantity_billed AP_INVOICE_LINES.QUANTITY_INVOICED%TYPE;
2293 l_total_amount_billed AP_INVOICE_LINES.AMOUNT%TYPE;
2294 l_rounding_index PO_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
2295 l_sum_prorated_amount AP_INVOICE_LINES.AMOUNT%TYPE;
2296 l_sum_prorated_quantity AP_INVOICE_LINES.QUANTITY_INVOICED%TYPE;
2297 l_max_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
2298 l_unit_meas_lookup_code AP_INVOICE_LINES.UNIT_MEAS_LOOKUP_CODE%TYPE;
2299 l_api_name VARCHAR2(32);
2300 l_msg_data VARCHAR2(4000);
2301 l_return_status VARCHAR2(100);
2302 l_debug_info VARCHAR2(1000);
2303 l_matching_basis VARCHAR2(30);
2304 l_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE;
2305 current_calling_sequence VARCHAR2(1000);
2306 api_call_failed EXCEPTION;
2307
2308 begin
2309
2310 l_api_name := 'update_po_matching_columns';
2311 current_calling_sequence := 'Update_Po_Matching_Columns<-'||p_calling_sequence;
2312
2313 l_sum_prorated_amount := 0;
2314 l_sum_prorated_quantity := 0;
2315 l_max_dist_amount := 0;
2316
2317 l_debug_info := 'Get PO Matched info from the invoice distributions';
2318 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2319 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2320 END IF;
2321
2322
2323 SELECT sum(quantity_invoiced),sum(amount)
2324 INTO l_total_quantity_billed,l_total_amount_billed
2325 FROM ap_invoice_distributions_all
2326 WHERE invoice_id = p_invoice_id
2327 AND invoice_line_number = p_line_number;
2328
2329 SELECT matching_basis
2330 INTO l_matching_basis
2331 FROM po_line_locations_all
2332 WHERE line_location_id = p_line_location_id;
2333
2334 l_debug_info := 'Populate the pl/sql table with proration data';
2335 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2336 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2337 END IF;
2338
2339 OPEN po_dists;
2340
2341 LOOP
2342
2343 FETCH po_dists INTO l_po_distribution_id,
2344 l_invoice_distribution_id,
2345 l_rcv_transaction_id,
2346 l_match_amount,
2347 l_match_quantity,
2348 l_pa_quantity;
2349
2350 EXIT WHEN po_dists%NOTFOUND;
2351
2352 x_dist_tab(l_po_distribution_id).po_distribution_id := l_po_distribution_id;
2353 x_dist_tab(l_po_distribution_id).invoice_distribution_id := l_invoice_distribution_id;
2354 x_dist_tab(l_po_distribution_id).rcv_transaction_id := l_rcv_transaction_id;
2355 x_dist_tab(l_po_distribution_id).match_amount := l_match_amount;
2356 x_dist_tab(l_po_distribution_id).match_quantity := l_match_quantity;
2357 x_dist_tab(l_po_distribution_id).pa_quantity := l_pa_quantity;
2358
2359 x_dist_tab(l_po_distribution_id).update_amount := nvl(x_dist_tab(l_po_distribution_id).match_amount,0) *
2360 p_amount_change/l_total_amount_billed;
2361 l_sum_prorated_amount := l_sum_prorated_amount + x_dist_tab(l_po_distribution_id).update_amount;
2362 x_dist_tab(l_po_distribution_id).update_quantity := nvl(x_dist_tab(l_po_distribution_id).match_quantity ,0) *
2363 p_quantity_change/l_total_quantity_billed;
2364 IF (x_dist_tab(l_po_distribution_id).pa_quantity is not null) THEN
2365 x_dist_tab(l_po_distribution_id).update_pa_quantity := x_dist_tab(l_po_distribution_id).update_quantity;
2366 ELSE
2367 x_dist_tab(l_po_distribution_id).update_pa_quantity := null;
2368 END IF;
2369
2370 l_sum_prorated_quantity := l_sum_prorated_quantity + x_dist_tab(l_po_distribution_id).update_quantity;
2371
2372 IF (l_max_dist_amount < x_dist_tab(l_po_distribution_id).match_amount ) THEN
2373 l_max_dist_amount := x_dist_tab(l_po_distribution_id).match_amount;
2374 l_rounding_index := l_po_distribution_id;
2375 END IF;
2376
2377 END LOOP;
2378
2379 CLOSE po_dists;
2380
2381 l_debug_info := 'Correct proration rounding error';
2382 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2383 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2384 END IF;
2385
2386 IF ((l_sum_prorated_quantity <> p_quantity_change OR l_sum_prorated_amount <> p_amount_change)
2387 and l_rounding_index is not null) THEN
2388
2389 x_dist_tab(l_rounding_index).update_quantity := x_dist_tab(l_rounding_index).update_quantity +
2390 (p_quantity_change - l_sum_prorated_quantity);
2391 IF(x_dist_tab(l_rounding_index).update_pa_quantity IS NOT NULL) THEN
2392 x_dist_tab(l_rounding_index).update_pa_quantity := x_dist_tab(l_rounding_index).update_quantity;
2393 END IF;
2394
2395 x_dist_tab(l_rounding_index).update_amount := x_dist_tab(l_rounding_index).update_amount +
2396 (p_amount_change - l_sum_prorated_amount);
2397
2398 END IF;
2399
2400 l_debug_info := 'Create l_po_ap_line_loc_rec object and populate the data';
2401 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2402 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2403 END IF;
2404 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
2405 p_po_line_location_id => p_line_location_id,
2406 p_uom_code => p_ap_uom,
2407 p_quantity_billed => (-1) * p_quantity_change,
2408 p_amount_billed => (-1) * p_amount_change,
2409 p_quantity_financed => NULL,
2410 p_amount_financed => NULL,
2411 p_quantity_recouped => NULL,
2412 p_amount_recouped => NULL,
2413 p_retainage_withheld_amt => NULL,
2414 p_retainage_released_amt => NULL);
2415
2416
2417 l_debug_info := 'Create l_po_ap_dist_rec object';
2418 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2419 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2420 END IF;
2421
2422 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
2423
2424 FOR i in nvl(x_dist_tab.first,0)..nvl(x_dist_tab.last,0) LOOP
2425
2426 IF (x_dist_tab.exists(i)) THEN
2427
2428 l_po_ap_dist_rec.add_change(p_po_distribution_id => x_dist_tab(i).po_distribution_id,
2429 p_uom_code => p_ap_uom,
2430 p_quantity_billed => (-1) * x_dist_tab(i).update_quantity,
2431 p_amount_billed => (-1) * x_dist_tab(i).update_amount,
2432 p_quantity_financed => NULL,
2433 p_amount_financed => NULL,
2434 p_quantity_recouped => NULL,
2435 p_amount_recouped => NULL,
2436 p_retainage_withheld_amt => NULL,
2437 p_retainage_released_amt => NULL);
2438
2439 UPDATE ap_invoice_distributions_all
2440 SET amount = amount - nvl(x_dist_tab(i).update_amount,0),
2441 quantity_invoiced = quantity_invoiced - nvl(x_dist_tab(i).update_quantity,0),
2442 pa_quantity = pa_quantity - nvl(x_dist_tab(i).update_pa_quantity,0)
2443 WHERE invoice_distribution_id = x_dist_tab(i).invoice_distribution_id;
2444
2445
2446 IF (x_dist_tab(i).rcv_transaction_id IS NOT NULL) THEN
2447
2448 IF (l_matching_basis = 'QUANTITY') THEN
2449
2450 RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
2451 X_rcv_transaction_id => x_dist_tab(i).rcv_transaction_id,
2452 X_quantity_billed => (-1)*x_dist_tab(i).update_quantity,
2453 X_uom_lookup_code => p_ap_uom,
2454 X_amount_billed => (-1)*x_dist_tab(i).update_amount,
2455 X_matching_basis => 'QUANTITY');
2456
2457 ELSE
2458
2459 RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
2460 X_rcv_transaction_id => x_dist_tab(i).rcv_transaction_id,
2461 X_quantity_billed => NULL,
2462 X_uom_lookup_code => p_ap_uom,
2463 X_amount_billed => (-1)*x_dist_tab(i).update_amount,
2464 X_matching_basis => 'AMOUNT');
2465
2466 END IF;
2467
2468 END IF;
2469
2470 END IF;
2471
2472 END LOOP;
2473
2474
2475 l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
2476 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2477 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2478 END IF;
2479
2480 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
2481 P_Api_Version => 1.0,
2482 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
2483 P_Dist_Changes_Rec => l_po_ap_dist_rec,
2484 X_Return_Status => p_return_status,
2485 X_Msg_Data => l_msg_data);
2486
2487 IF (p_return_status <> 'S') THEN
2488 l_debug_info := 'PO API returned unsuccessfully, raise the exception';
2489 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2490 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2491 END IF;
2492 l_debug_info := l_msg_data;
2493 RAISE api_call_failed;
2494 END IF;
2495
2496 EXCEPTION
2497 WHEN OTHERS THEN
2498 IF (SQLCODE <> -20001) THEN
2499 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2500 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2501 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2502 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2503 END IF;
2504
2505 APP_EXCEPTION.RAISE_EXCEPTION;
2506
2507 end update_po_matching_columns;
2508
2509
2510 /* Bug 5407726 ISP Code cleanup XBuild9
2511 This code is not being used
2512 PROCEDURE discard_and_rematch (p_invoice_id in number,
2513 p_line_number in number,
2514 p_error_code out nocopy varchar2,
2515 p_token out nocopy varchar2) is
2516 l_line_rec ap_invoice_lines%rowtype;
2517 l_error_code varchar2(100);
2518 l_token varchar2(100);
2519
2520 begin
2521
2522 select *
2523 into l_line_rec
2524 from ap_invoice_lines_all
2525 where invoice_id = p_invoice_id
2526 and line_number = p_line_number;
2527
2528
2529 if ap_invoice_lines_pkg.Discard_Inv_Line(
2530 P_line_rec => l_line_rec,
2531 P_calling_mode => 'DISCARD',
2532 P_last_updated_by => l_line_rec.last_updated_by,
2533 P_last_update_login => l_line_rec.last_update_login,
2534 P_error_code => l_error_code,
2535 P_token => l_token,
2536 P_calling_sequence => 'NEGOTIATION')<> true then
2537 p_error_code := l_error_code;
2538 p_token := l_token;
2539 return;
2540 end if;
2541
2542
2543 select max(line_number)+1
2544 into l_line_rec.line_number
2545 from ap_invoice_lines_all
2546 where invoice_id = p_invoice_id;
2547
2548 INSERT INTO AP_INVOICE_LINES (
2549 INVOICE_ID,
2550 LINE_NUMBER,
2551 LINE_TYPE_LOOKUP_CODE,
2552 REQUESTER_ID,
2553 DESCRIPTION,
2554 LINE_SOURCE,
2555 ORG_ID,
2556 INVENTORY_ITEM_ID,
2557 ITEM_DESCRIPTION,
2558 SERIAL_NUMBER,
2559 MANUFACTURER,
2560 MODEL_NUMBER,
2561 GENERATE_DISTS,
2562 MATCH_TYPE,
2563 DISTRIBUTION_SET_ID,
2564 ACCOUNT_SEGMENT,
2565 BALANCING_SEGMENT,
2566 COST_CENTER_SEGMENT,
2567 OVERLAY_DIST_CODE_CONCAT,
2568 DEFAULT_DIST_CCID,
2569 PRORATE_ACROSS_ALL_ITEMS,
2570 LINE_GROUP_NUMBER,
2571 ACCOUNTING_DATE,
2572 PERIOD_NAME,
2573 DEFERRED_ACCTG_FLAG,
2574 DEF_ACCTG_START_DATE,
2575 DEF_ACCTG_END_DATE,
2576 DEF_ACCTG_NUMBER_OF_PERIODS,
2577 DEF_ACCTG_PERIOD_TYPE,
2578 SET_OF_BOOKS_ID,
2579 AMOUNT,
2580 BASE_AMOUNT,
2581 ROUNDING_AMT,
2582 QUANTITY_INVOICED,
2583 UNIT_MEAS_LOOKUP_CODE,
2584 UNIT_PRICE,
2585 WFAPPROVAL_STATUS,
2586 DISCARDED_FLAG,
2587 ORIGINAL_AMOUNT,
2588 ORIGINAL_BASE_AMOUNT,
2589 ORIGINAL_ROUNDING_AMT,
2590 CANCELLED_FLAG,
2591 INCOME_TAX_REGION,
2592 TYPE_1099,
2593 STAT_AMOUNT,
2594 PREPAY_INVOICE_ID,
2595 PREPAY_LINE_NUMBER,
2596 INVOICE_INCLUDES_PREPAY_FLAG,
2597 CORRECTED_INV_ID,
2598 CORRECTED_LINE_NUMBER,
2599 PO_HEADER_ID,
2600 PO_LINE_ID,
2601 PO_RELEASE_ID,
2602 PO_LINE_LOCATION_ID,
2603 PO_DISTRIBUTION_ID,
2604 RCV_TRANSACTION_ID,
2605 FINAL_MATCH_FLAG,
2606 ASSETS_TRACKING_FLAG,
2607 ASSET_BOOK_TYPE_CODE,
2608 ASSET_CATEGORY_ID,
2609 PROJECT_ID,
2610 TASK_ID,
2611 EXPENDITURE_TYPE,
2612 EXPENDITURE_ITEM_DATE,
2613 EXPENDITURE_ORGANIZATION_ID,
2614 PA_QUANTITY,
2615 PA_CC_AR_INVOICE_ID,
2616 PA_CC_AR_INVOICE_LINE_NUM,
2617 PA_CC_PROCESSED_CODE,
2618 AWARD_ID,
2619 AWT_GROUP_ID,
2620 REFERENCE_1,
2621 REFERENCE_2,
2622 RECEIPT_VERIFIED_FLAG,
2623 RECEIPT_REQUIRED_FLAG,
2624 RECEIPT_MISSING_FLAG,
2625 JUSTIFICATION,
2626 EXPENSE_GROUP,
2627 START_EXPENSE_DATE,
2628 END_EXPENSE_DATE,
2629 RECEIPT_CURRENCY_CODE,
2630 RECEIPT_CONVERSION_RATE,
2631 RECEIPT_CURRENCY_AMOUNT,
2632 DAILY_AMOUNT,
2633 WEB_PARAMETER_ID,
2634 ADJUSTMENT_REASON,
2635 MERCHANT_DOCUMENT_NUMBER,
2636 MERCHANT_NAME,
2637 MERCHANT_REFERENCE,
2638 MERCHANT_TAX_REG_NUMBER,
2639 MERCHANT_TAXPAYER_ID,
2640 COUNTRY_OF_SUPPLY,
2641 CREDIT_CARD_TRX_ID,
2642 COMPANY_PREPAID_INVOICE_ID,
2643 CC_REVERSAL_FLAG,
2644 ATTRIBUTE_CATEGORY,
2645 ATTRIBUTE1,
2646 ATTRIBUTE2,
2647 ATTRIBUTE3,
2648 ATTRIBUTE4,
2649 ATTRIBUTE5,
2650 ATTRIBUTE6,
2651 ATTRIBUTE7,
2652 ATTRIBUTE8,
2653 ATTRIBUTE9,
2654 ATTRIBUTE10,
2655 ATTRIBUTE11,
2656 ATTRIBUTE12,
2657 ATTRIBUTE13,
2658 ATTRIBUTE14,
2659 ATTRIBUTE15,
2660 CREATION_DATE,
2661 CREATED_BY,
2662 LAST_UPDATED_BY,
2663 LAST_UPDATE_DATE,
2664 LAST_UPDATE_LOGIN,
2665 PROGRAM_APPLICATION_ID,
2666 PROGRAM_ID,
2667 PROGRAM_UPDATE_DATE,
2668 REQUEST_ID,
2669 SHIP_TO_LOCATION_ID,
2670 PRIMARY_INTENDED_USE,
2671 PRODUCT_FISC_CLASSIFICATION,
2672 TRX_BUSINESS_CATEGORY,
2673 PRODUCT_TYPE,
2674 PRODUCT_CATEGORY,
2675 USER_DEFINED_FISC_CLASS,
2676 PURCHASING_CATEGORY_ID)
2677 values( l_line_rec.INVOICE_ID,
2678 l_line_rec.LINE_NUMBER,
2679 l_line_rec.LINE_TYPE_LOOKUP_CODE,
2680 l_line_rec.REQUESTER_ID,
2681 l_line_rec.DESCRIPTION,
2682 l_line_rec.LINE_SOURCE,
2683 l_line_rec.ORG_ID,
2684 l_line_rec.INVENTORY_ITEM_ID,
2685 l_line_rec.ITEM_DESCRIPTION,
2686 l_line_rec.SERIAL_NUMBER,
2687 l_line_rec.MANUFACTURER,
2688 l_line_rec.MODEL_NUMBER,
2689 l_line_rec.GENERATE_DISTS,
2690 l_line_rec.MATCH_TYPE,
2691 l_line_rec.DISTRIBUTION_SET_ID,
2692 l_line_rec.ACCOUNT_SEGMENT,
2693 l_line_rec.BALANCING_SEGMENT,
2694 l_line_rec.COST_CENTER_SEGMENT,
2695 l_line_rec.OVERLAY_DIST_CODE_CONCAT,
2696 l_line_rec.DEFAULT_DIST_CCID,
2697 l_line_rec.PRORATE_ACROSS_ALL_ITEMS,
2698 l_line_rec.LINE_GROUP_NUMBER,
2699 l_line_rec.ACCOUNTING_DATE,
2700 l_line_rec.PERIOD_NAME,
2701 l_line_rec.DEFERRED_ACCTG_FLAG,
2702 l_line_rec.DEF_ACCTG_START_DATE,
2703 l_line_rec.DEF_ACCTG_END_DATE,
2704 l_line_rec.DEF_ACCTG_NUMBER_OF_PERIODS,
2705 l_line_rec.DEF_ACCTG_PERIOD_TYPE,
2706 l_line_rec.SET_OF_BOOKS_ID,
2707 l_line_rec.AMOUNT,
2708 l_line_rec.BASE_AMOUNT,
2709 l_line_rec.ROUNDING_AMT,
2710 l_line_rec.QUANTITY_INVOICED,
2711 l_line_rec.UNIT_MEAS_LOOKUP_CODE,
2712 l_line_rec.UNIT_PRICE,
2713 l_line_rec.WFAPPROVAL_STATUS,
2714 l_line_rec.DISCARDED_FLAG,
2715 l_line_rec.ORIGINAL_AMOUNT,
2716 l_line_rec.ORIGINAL_BASE_AMOUNT,
2717 l_line_rec.ORIGINAL_ROUNDING_AMT,
2718 l_line_rec.CANCELLED_FLAG,
2719 l_line_rec.INCOME_TAX_REGION,
2720 l_line_rec.TYPE_1099,
2721 l_line_rec.STAT_AMOUNT,
2722 l_line_rec.PREPAY_INVOICE_ID,
2723 l_line_rec.PREPAY_LINE_NUMBER,
2724 l_line_rec.INVOICE_INCLUDES_PREPAY_FLAG,
2725 l_line_rec.CORRECTED_INV_ID,
2726 l_line_rec.CORRECTED_LINE_NUMBER,
2727 l_line_rec.PO_HEADER_ID,
2728 l_line_rec.PO_LINE_ID,
2729 l_line_rec.PO_RELEASE_ID,
2730 l_line_rec.PO_LINE_LOCATION_ID,
2731 l_line_rec.PO_DISTRIBUTION_ID,
2732 l_line_rec.RCV_TRANSACTION_ID,
2733 l_line_rec.FINAL_MATCH_FLAG,
2734 l_line_rec.ASSETS_TRACKING_FLAG,
2735 l_line_rec.ASSET_BOOK_TYPE_CODE,
2736 l_line_rec.ASSET_CATEGORY_ID,
2737 l_line_rec.PROJECT_ID,
2738 l_line_rec.TASK_ID,
2739 l_line_rec.EXPENDITURE_TYPE,
2740 l_line_rec.EXPENDITURE_ITEM_DATE,
2741 l_line_rec.EXPENDITURE_ORGANIZATION_ID,
2742 l_line_rec.PA_QUANTITY,
2743 l_line_rec.PA_CC_AR_INVOICE_ID,
2744 l_line_rec.PA_CC_AR_INVOICE_LINE_NUM,
2745 l_line_rec.PA_CC_PROCESSED_CODE,
2746 l_line_rec.AWARD_ID,
2747 l_line_rec.AWT_GROUP_ID,
2748 l_line_rec.REFERENCE_1,
2749 l_line_rec.REFERENCE_2,
2750 l_line_rec.RECEIPT_VERIFIED_FLAG,
2751 l_line_rec.RECEIPT_REQUIRED_FLAG,
2752 l_line_rec.RECEIPT_MISSING_FLAG,
2753 l_line_rec.JUSTIFICATION,
2754 l_line_rec.EXPENSE_GROUP,
2755 l_line_rec.START_EXPENSE_DATE,
2756 l_line_rec.END_EXPENSE_DATE,
2757 l_line_rec.RECEIPT_CURRENCY_CODE,
2758 l_line_rec.RECEIPT_CONVERSION_RATE,
2759 l_line_rec.RECEIPT_CURRENCY_AMOUNT,
2760 l_line_rec.DAILY_AMOUNT,
2761 l_line_rec.WEB_PARAMETER_ID,
2762 l_line_rec.ADJUSTMENT_REASON,
2763 l_line_rec.MERCHANT_DOCUMENT_NUMBER,
2764 l_line_rec.MERCHANT_NAME,
2765 l_line_rec.MERCHANT_REFERENCE,
2766 l_line_rec.MERCHANT_TAX_REG_NUMBER,
2767 l_line_rec.MERCHANT_TAXPAYER_ID,
2768 l_line_rec.COUNTRY_OF_SUPPLY,
2769 l_line_rec.CREDIT_CARD_TRX_ID,
2770 l_line_rec.COMPANY_PREPAID_INVOICE_ID,
2771 l_line_rec.CC_REVERSAL_FLAG,
2772 l_line_rec.ATTRIBUTE_CATEGORY,
2773 l_line_rec.ATTRIBUTE1,
2774 l_line_rec.ATTRIBUTE2,
2775 l_line_rec.ATTRIBUTE3,
2776 l_line_rec.ATTRIBUTE4,
2777 l_line_rec.ATTRIBUTE5,
2778 l_line_rec.ATTRIBUTE6,
2779 l_line_rec.ATTRIBUTE7,
2780 l_line_rec.ATTRIBUTE8,
2781 l_line_rec.ATTRIBUTE9,
2782 l_line_rec.ATTRIBUTE10,
2783 l_line_rec.ATTRIBUTE11,
2784 l_line_rec.ATTRIBUTE12,
2785 l_line_rec.ATTRIBUTE13,
2786 l_line_rec.ATTRIBUTE14,
2787 l_line_rec.ATTRIBUTE15,
2788 l_line_rec.CREATION_DATE,
2789 l_line_rec.CREATED_BY,
2790 l_line_rec.LAST_UPDATED_BY,
2791 l_line_rec.LAST_UPDATE_DATE,
2792 l_line_rec.LAST_UPDATE_LOGIN,
2793 l_line_rec.PROGRAM_APPLICATION_ID,
2794 l_line_rec.PROGRAM_ID,
2795 l_line_rec.PROGRAM_UPDATE_DATE,
2796 l_line_rec.REQUEST_ID,
2797 l_line_rec.SHIP_TO_LOCATION_ID,
2798 l_line_rec.PRIMARY_INTENDED_USE,
2799 l_line_rec.PRODUCT_FISC_CLASSIFICATION,
2800 l_line_rec.TRX_BUSINESS_CATEGORY,
2801 l_line_rec.PRODUCT_TYPE,
2802 l_line_rec.PRODUCT_CATEGORY,
2803 l_line_rec.USER_DEFINED_FISC_CLASS,
2804 l_line_rec.PURCHASING_CATEGORY_ID);
2805
2806
2807
2808 ap_matching_utils_pkg.Match_Invoice_Line(
2809 P_Invoice_Id => p_invoice_id,
2810 P_Invoice_Line_Number => l_line_rec.line_number,
2811 P_Overbill_Flag => 'N',
2812 P_Calling_Sequence => 'AP_ISP_UTILITIES_PKG.DISCARD_AND_REMATCH');
2813
2814
2815 end discard_and_rematch;
2816
2817 */
2818
2819 --Bug 5500186 --For Non-Po invoices, user should provide the Customer
2820 -- Contact Info.
2821 PROCEDURE populate_requester(p_first_name IN VARCHAR2,
2822 p_last_name IN VARCHAR2,
2823 p_email_address IN VARCHAR2,
2824 p_requester_id IN OUT NOCOPY NUMBER) IS
2825
2826 requester_id NUMBER;
2827
2828 /*Bug 16772090*/
2829 v_SQL varchar2(2000);
2830 v_select INTEGER; /* "Pointer" to a DBMS_SQL select statement */
2831 v_execute INTEGER;
2832 v_column NUMBER;
2833 row_count INTEGER;
2834 l_curr_calling_sequence VARCHAR2(100);
2835 l_debug_info VARCHAR2(100);
2836 BEGIN
2837 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.populate_requester';
2838 -- bug16484805, added conditions on first_name, last_name and email
2839 -- using bind variables directly to the SQL
2840 --
2841 /* Removed code changes of bug 16484805, 9074429 for bug Bug 16772090 */
2842
2843 /*Bug 16772090*/
2844 v_SQL := 'select person_id from per_all_people_f where NVL(effective_end_date, SYSDATE) >= SYSDATE';
2845
2846 IF p_first_name IS NOT NULL THEN
2847 v_SQL := v_SQL || ' AND upper(first_name) = :p_first_name';
2848 END IF;
2849
2850 IF p_last_name IS NOT NULL THEN
2851 v_SQL := v_SQL || ' AND upper(last_name) = :p_last_name';
2852 END IF;
2853
2854 IF p_email_address IS NOT NULL THEN
2855 v_SQL := v_SQL || ' AND upper(email_address) = :p_email_address';
2856 END IF;
2857
2858 -- Setup Cursor
2859 v_select := dbms_sql.open_cursor;
2860 dbms_sql.parse( v_select, v_SQL, DBMS_SQL.native);
2861
2862 IF p_first_name IS NOT NULL THEN
2863 dbms_sql.bind_variable( v_select, ':p_first_name', upper(p_first_name) );
2864 END IF;
2865
2866 IF p_last_name IS NOT NULL THEN
2867 dbms_sql.bind_variable( v_select, ':p_last_name', upper(p_last_name) );
2868 END IF;
2869
2870 IF p_email_address IS NOT NULL THEN
2871 dbms_sql.bind_variable( v_select, ':p_email_address', upper(p_email_address) );
2872 END IF;
2873
2874 DBMS_SQL.DEFINE_COLUMN(v_select, 1, v_column);
2875
2876 v_execute := DBMS_SQL.EXECUTE( v_select );
2877
2878 row_count := 0;
2879 l_debug_info := 'query to fetch person id by passing first, last name or email address';
2880 LOOP
2881 IF DBMS_SQL.FETCH_ROWS(v_select) = 0 THEN
2882 EXIT;
2883 END IF;
2884 DBMS_SQL.COLUMN_VALUE( v_select, 1, v_column);
2885 row_count := DBMS_SQL.LAST_ROW_COUNT;
2886
2887 IF row_count > 1 THEN
2888 EXIT;
2889 END IF;
2890 END LOOP;
2891
2892 IF row_count = 0 THEN
2893 -- No row was found
2894 p_requester_id := NULL;
2895 ELSIF row_count = 1 THEN
2896 -- Single row was found
2897 p_requester_id := v_column;
2898 ELSIF row_count > 1 THEN
2899 -- Multiple rows were found
2900 p_requester_id := NULL;
2901 END IF;
2902
2903 --Tidy Up
2904 DBMS_SQL.CLOSE_CURSOR(v_select);
2905
2906 EXCEPTION
2907 WHEN OTHERS THEN
2908 IF DBMS_SQL.IS_open(v_select) THEN
2909 p_requester_id := NULL;
2910 DBMS_SQL.CLOSE_CURSOR(v_select);
2911 END IF;
2912 IF (SQLCODE <> -20001) THEN
2913 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2914 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2915 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
2916 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2917 ', p_first_name = ' || p_first_name ||
2918 ', p_last_name = ' || p_last_name ||
2919 ', p_email_address = ' || p_email_address ||
2920 ', P_calling_sequence = ' || l_curr_calling_sequence);
2921 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2922 END IF;
2923
2924 APP_EXCEPTION.RAISE_EXCEPTION;
2925 END;
2926
2927 -- Bug 5659917 PO Number shows incorrect info on the Invoice
2928 -- Search Page
2929 FUNCTION get_po_number_switcher(p_invoice_id IN NUMBER)
2930 RETURN VARCHAR2 IS
2931
2932 l_po_number VARCHAR2(50); -- for CLM Bug 9503239
2933 l_count NUMBER;
2934 l_po_count NUMBER;
2935 l_release_count NUMBER;
2936
2937 BEGIN
2938 --
2939 SELECT COUNT(*)
2940 INTO l_count
2941 FROM ap_invoice_lines_all
2942 WHERE po_header_id IS NOT NULL
2943 AND invoice_id = p_invoice_id;
2944
2945 IF l_count = 0 THEN
2946 RETURN 'UNMATCHED';
2947 ELSE
2948 SELECT count(*)
2949 INTO l_po_count
2950 FROM po_headers_all
2951 WHERE po_header_id IN (SELECT po_header_id
2952 FROM ap_invoice_lines_All
2953 WHERE invoice_id = p_invoice_id);
2954
2955 IF l_po_count = 1 THEN
2956 --
2957 SELECT COUNT(*)
2958 INTO l_release_count
2959 FROM po_releases_all pr
2960 WHERE po_header_id IN (SELECT po_header_id
2961 FROM ap_invoice_lines_All
2962 WHERE invoice_id = p_invoice_id);
2963 --
2964 IF (l_release_count = 0 OR l_release_count = 1) THEN
2965 RETURN 'SINGLE';
2966 ELSE
2967 RETURN 'MULTIPLE';
2968 END IF;
2969 --
2970 ELSE
2971 RETURN 'MULTIPLE';
2972 END IF;
2973 END IF;
2974
2975 END;
2976
2977 FUNCTION get_po_number(p_invoice_id IN NUMBER)
2978 RETURN VARCHAR2 IS
2979
2980 l_po_number VARCHAR2(50); -- for CLM Bug 9503239
2981 l_po_header_id NUMBER;
2982 l_po_count NUMBER;
2983 l_release_count NUMBER;
2984 l_po_switcher VARCHAR2(10);
2985 l_release_num NUMBER;
2986
2987 BEGIN
2988 --
2989 l_po_switcher := get_po_number_switcher(p_invoice_id);
2990
2991 IF l_po_switcher = 'SINGLE' THEN
2992 --
2993 SELECT NVL(CLM_DOCUMENT_NUMBER, SEGMENT1), -- for CLM Bug 9503239
2994 po_header_id
2995 INTO l_po_number,
2996 l_po_header_id
2997 FROM po_headers_all POH
2998 WHERE po_header_id IN ( SELECT po_header_id
2999 FROM ap_invoice_lines_All
3000 WHERE invoice_id = p_invoice_id);
3001
3002 SELECT COUNT(*)
3003 INTO l_release_count
3004 FROM po_releases_all pr
3005 WHERE po_header_id = l_po_header_id;
3006 --
3007 IF l_release_count = 1 THEN
3008 --
3009 SELECT release_num
3010 INTO l_release_num
3011 FROM po_releases_all
3012 WHERE po_header_id = l_po_header_id;
3013
3014 l_po_number := l_po_number||'-'|| l_release_num;
3015 END IF;
3016 --
3017 END IF;
3018
3019
3020 RETURN l_po_number;
3021 --
3022 END;
3023
3024 --Bug 5704381
3025 FUNCTION get_po_header_id(p_invoice_id IN NUMBER)
3026 RETURN NUMBER IS
3027
3028 l_po_header_id NUMBER;
3029 l_count NUMBER;
3030 l_po_count NUMBER;
3031
3032 BEGIN
3033 --
3034 SELECT COUNT(*)
3035 INTO l_count
3036 FROM ap_invoice_lines_all
3037 WHERE po_header_id IS NOT NULL
3038 AND invoice_id = p_invoice_id;
3039
3040 IF l_count <> 0 THEN
3041
3042 SELECT count(*)
3043 INTO l_po_count
3044 FROM po_headers_all
3045 WHERE po_header_id IN (SELECT po_header_id
3046 FROM ap_invoice_lines_All
3047 WHERE invoice_id = p_invoice_id);
3048
3049 IF l_po_count = 1 THEN
3050 SELECT po_header_id
3051 INTO l_po_header_id
3052 FROM po_headers_all POH
3053 WHERE po_header_id IN ( SELECT po_header_id
3054 FROM ap_invoice_lines_All
3055 WHERE invoice_id = p_invoice_id);
3056 END IF;
3057 END IF;
3058 --
3059 RETURN l_po_header_id;
3060 --
3061 END;
3062
3063 FUNCTION get_po_release(p_invoice_id IN NUMBER,
3064 p_ret_value IN VARCHAR2)
3065 RETURN NUMBER IS
3066
3067 l_po_header_id NUMBER;
3068 l_release_count NUMBER;
3069 l_po_release_id NUMBER;
3070 l_release_num NUMBER;
3071
3072
3073
3074 BEGIN
3075 --
3076 l_po_header_id := get_po_header_id(p_invoice_id);
3077 --
3078 IF l_po_header_id is NOT NULL THEN
3079 SELECT COUNT(*)
3080 INTO l_release_count
3081 FROM po_releases_all pr
3082 WHERE po_header_id IN (SELECT po_header_id
3083 FROM ap_invoice_lines_All
3084 WHERE invoice_id = p_invoice_id);
3085 --
3086 IF l_release_count = 1 THEN
3087 --
3088 SELECT po_release_id,
3089 release_num
3090 INTO l_po_release_id,
3091 l_release_num
3092 FROM po_releases_all
3093 WHERE po_header_id = l_po_header_id;
3094 END IF;
3095 --
3096 END IF;
3097 --
3098 IF p_ret_value ='NUM' THEN
3099 RETURN l_release_num;
3100 ELSE
3101 RETURN l_po_release_id;
3102 END IF;
3103 --
3104 END;
3105
3106 --Bug 8865603
3107
3108 PROCEDURE stop_approval(p_invoice_id NUMBER) IS
3109
3110 dummy BOOLEAN;
3111 l_hist_rec AP_INV_APRVL_HIST%ROWTYPE;
3112 l_approval_iteration AP_INVOICES_ALL.approval_iteration%TYPE;
3113
3114 BEGIN
3115
3116 BEGIN
3117
3118 SELECT nvl(approval_iteration, 1)
3119 INTO l_approval_iteration
3120 FROM ap_invoices_all ai
3121 WHERE invoice_id = p_invoice_id;
3122
3123 SELECT history_type,
3124 invoice_id,
3125 iteration,
3126 'WITHDRAWN',
3127 FND_PROFILE.VALUE('USER_ID'),
3128 FND_PROFILE.VALUE('USERNAME'),
3129 FND_PROFILE.VALUE('USER_ID'),
3130 sysdate,
3131 sysdate,
3132 FND_PROFILE.VALUE('USER_ID'),
3133 FND_PROFILE.VALUE('LOGIN_ID'),
3134 org_id,
3135 null,
3136 null,
3137 null,
3138 null,
3139 notification_order
3140 INTO l_hist_rec.history_type,
3141 l_hist_rec.invoice_id,
3142 l_hist_rec.iteration,
3143 l_hist_rec.response,
3144 l_hist_rec.approver_id,
3145 l_hist_rec.approver_name,
3146 l_hist_rec.created_by,
3147 l_hist_rec.creation_date,
3148 l_hist_rec.last_update_date,
3149 l_hist_rec.last_updated_by,
3150 l_hist_rec.last_update_login,
3151 l_hist_rec.org_id,
3152 l_hist_rec.amount_approved,
3153 l_hist_rec.hold_id,
3154 l_hist_rec.line_number,
3155 l_hist_rec.approver_comments,
3156 l_hist_rec.notification_order
3157 FROM ap_inv_aprvl_hist_all
3158 WHERE invoice_id = p_invoice_id
3159 AND iteration = l_approval_iteration
3160 AND response = 'SENT'
3161 AND rownum = 1;
3162
3163 AP_WORKFLOW_PKG.insert_history_table(l_hist_rec);
3164
3165 EXCEPTION
3166 WHEN OTHERS THEN
3167 NULL;
3168 END;
3169
3170 DUMMY := ap_workflow_pkg.stop_approval(
3171 p_invoice_id, NULL, 'AP_ISP_UTILITIES_PKG.stop_approval.stop_approval');
3172
3173 END;
3174
3175 FUNCTION unsubmit_switcher(
3176 p_wfapproval_status VARCHAR2,
3177 p_approval_ready_flag VARCHAR2,
3178 p_cancel_date DATE,
3179 p_invoice_type VARCHAR2) RETURN VARCHAR2 IS
3180
3181 BEGIN
3182 IF p_approval_ready_flag <> 'S' AND
3183 p_invoice_type = 'INVOICE REQUEST' AND
3184 p_cancel_date IS NULL AND
3185 p_wfapproval_status NOT IN
3186 ('REJECTED', 'MANUALLY APPROVED', 'WFAPPROVED', 'NOT REQUIRED') THEN
3187 RETURN 'UnsubmitEnabled';
3188 ELSE
3189 RETURN 'UnsubmitDisabled';
3190 END IF;
3191 END;
3192
3193 --Bug 8865603
3194
3195 -- Bug 9095733.
3196 -- Wrapper API created to invoke pa_acc_gen_wf_pkg.ap_inv_generate_account
3197 -- from java layer. pa_acc_gen_wf_pkg.ap_inv_generate_account can not be
3198 -- invoked directly as the PAI returns boolean which is not supported
3199 -- from java.
3200
3201 FUNCTION ap_inv_generate_account_wrap
3202 (
3203 p_project_id IN pa_projects_all.project_id%TYPE,
3204 p_task_id IN pa_tasks.task_id%TYPE,
3205 p_expenditure_type IN pa_expenditure_types.expenditure_type%TYPE,
3206 p_vendor_id IN po_vendors.vendor_id%type,
3207 p_expenditure_organization_id IN hr_organization_units.organization_id%TYPE,
3208 p_expenditure_item_date IN pa_expenditure_items_all.expenditure_item_date%TYPE,
3209 p_billable_flag IN pa_tasks.billable_flag%TYPE,
3210 p_chart_of_accounts_id IN NUMBER,
3211 p_attribute_category IN ap_invoices_all.attribute_category%TYPE,
3212 p_attribute1 IN ap_invoices_all.attribute1%TYPE,
3213 p_attribute2 IN ap_invoices_all.attribute2%TYPE,
3214 p_attribute3 IN ap_invoices_all.attribute3%TYPE,
3215 p_attribute4 IN ap_invoices_all.attribute4%TYPE,
3216 p_attribute5 IN ap_invoices_all.attribute5%TYPE,
3217 p_attribute6 IN ap_invoices_all.attribute6%TYPE,
3218 p_attribute7 IN ap_invoices_all.attribute7%TYPE,
3219 p_attribute8 IN ap_invoices_all.attribute8%TYPE,
3220 p_attribute9 IN ap_invoices_all.attribute9%TYPE,
3221 p_attribute10 IN ap_invoices_all.attribute10%TYPE,
3222 p_attribute11 IN ap_invoices_all.attribute11%TYPE,
3223 p_attribute12 IN ap_invoices_all.attribute12%TYPE,
3224 p_attribute13 IN ap_invoices_all.attribute13%TYPE,
3225 p_attribute14 IN ap_invoices_all.attribute14%TYPE,
3226 p_attribute15 IN ap_invoices_all.attribute15%TYPE,
3227 p_dist_attribute_category IN ap_invoice_distributions_all.attribute_category%TYPE,
3228 p_dist_attribute1 IN ap_invoice_distributions_all.attribute1%TYPE,
3229 p_dist_attribute2 IN ap_invoice_distributions_all.attribute2%TYPE,
3230 p_dist_attribute3 IN ap_invoice_distributions_all.attribute3%TYPE,
3231 p_dist_attribute4 IN ap_invoice_distributions_all.attribute4%TYPE,
3232 p_dist_attribute5 IN ap_invoice_distributions_all.attribute5%TYPE,
3233 p_dist_attribute6 IN ap_invoice_distributions_all.attribute6%TYPE,
3234 p_dist_attribute7 IN ap_invoice_distributions_all.attribute7%TYPE,
3235 p_dist_attribute8 IN ap_invoice_distributions_all.attribute8%TYPE,
3236 p_dist_attribute9 IN ap_invoice_distributions_all.attribute9%TYPE,
3237 p_dist_attribute10 IN ap_invoice_distributions_all.attribute10%TYPE,
3238 p_dist_attribute11 IN ap_invoice_distributions_all.attribute11%TYPE,
3239 p_dist_attribute12 IN ap_invoice_distributions_all.attribute12%TYPE,
3240 p_dist_attribute13 IN ap_invoice_distributions_all.attribute13%TYPE,
3241 p_dist_attribute14 IN ap_invoice_distributions_all.attribute14%TYPE,
3242 p_dist_attribute15 IN ap_invoice_distributions_all.attribute15%TYPE,
3243 p_input_ccid IN gl_code_combinations.code_combination_id%TYPE default null,
3244 x_return_ccid OUT NOCOPY gl_code_combinations.code_combination_id%TYPE,
3245 x_concat_segs IN OUT NOCOPY VARCHAR2, -- Bug 5935019
3246 x_concat_ids IN OUT NOCOPY VARCHAR2, -- Bug 5935019
3247 x_concat_descrs IN OUT NOCOPY VARCHAR2, -- Bug 5935019
3248 x_error_message OUT NOCOPY VARCHAR2,
3249 X_award_set_id IN NUMBER DEFAULT NULL,
3250 p_accounting_date IN ap_invoice_distributions_all.accounting_date%TYPE default NULL,
3251 p_award_id IN NUMBER DEFAULT NULL,
3252 p_expenditure_item_id IN NUMBER DEFAULT NULL ) RETURN NUMBER IS
3253
3254 l_result BOOLEAN;
3255
3256 BEGIN
3257
3258 l_result := pa_acc_gen_wf_pkg.ap_inv_generate_account (
3259 p_project_id => p_project_id,
3260 p_task_id => p_task_id,
3261 p_expenditure_type => p_expenditure_type,
3262 p_vendor_id => p_vendor_id,
3263 p_expenditure_organization_id => p_expenditure_organization_id,
3264 p_expenditure_item_date => p_expenditure_item_date,
3265 p_billable_flag => p_billable_flag,
3266 p_chart_of_accounts_id => p_chart_of_accounts_id,
3267 p_accounting_date => p_accounting_date,
3268 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
3269 P_ATTRIBUTE1 => P_ATTRIBUTE1,
3270 P_ATTRIBUTE2 => P_ATTRIBUTE2,
3271 P_ATTRIBUTE3 => P_ATTRIBUTE3,
3272 P_ATTRIBUTE4 => P_ATTRIBUTE4,
3273 P_ATTRIBUTE5 => P_ATTRIBUTE5,
3274 P_ATTRIBUTE6 => P_ATTRIBUTE6,
3275 P_ATTRIBUTE7 => P_ATTRIBUTE7,
3276 P_ATTRIBUTE8 => P_ATTRIBUTE8,
3277 P_ATTRIBUTE9 => P_ATTRIBUTE9,
3278 P_ATTRIBUTE10 => P_ATTRIBUTE10,
3279 P_ATTRIBUTE11 => P_ATTRIBUTE11,
3280 P_ATTRIBUTE12 => P_ATTRIBUTE12,
3281 P_ATTRIBUTE13 => P_ATTRIBUTE13,
3282 P_ATTRIBUTE14 => P_ATTRIBUTE14,
3283 P_ATTRIBUTE15 => P_ATTRIBUTE15,
3284 P_DIST_ATTRIBUTE_CATEGORY => P_DIST_ATTRIBUTE_CATEGORY,
3285 P_DIST_ATTRIBUTE1 => P_DIST_ATTRIBUTE1,
3286 P_DIST_ATTRIBUTE2 => P_DIST_ATTRIBUTE2,
3287 P_DIST_ATTRIBUTE3 => P_DIST_ATTRIBUTE3,
3288 P_DIST_ATTRIBUTE4 => P_DIST_ATTRIBUTE4,
3289 P_DIST_ATTRIBUTE5 => P_DIST_ATTRIBUTE5,
3290 P_DIST_ATTRIBUTE6 => P_DIST_ATTRIBUTE6,
3291 P_DIST_ATTRIBUTE7 => P_DIST_ATTRIBUTE7,
3292 P_DIST_ATTRIBUTE8 => P_DIST_ATTRIBUTE8,
3293 P_DIST_ATTRIBUTE9 => P_DIST_ATTRIBUTE9,
3294 P_DIST_ATTRIBUTE10 => P_DIST_ATTRIBUTE10,
3295 P_DIST_ATTRIBUTE11 => P_DIST_ATTRIBUTE11,
3296 P_DIST_ATTRIBUTE12 => P_DIST_ATTRIBUTE12,
3297 P_DIST_ATTRIBUTE13 => P_DIST_ATTRIBUTE13,
3298 P_DIST_ATTRIBUTE14 => P_DIST_ATTRIBUTE14,
3299 P_DIST_ATTRIBUTE15 => P_DIST_ATTRIBUTE15,
3300 p_input_ccid => p_input_ccid,
3301 x_return_ccid => x_return_ccid,
3302 x_concat_segs => x_concat_segs,
3303 x_concat_ids => x_concat_ids,
3304 x_concat_descrs => x_concat_descrs,
3305 x_error_message => x_error_message,
3306 X_award_set_id => X_award_set_id,
3307 p_award_id => p_award_id,
3308 p_expenditure_item_id => p_expenditure_item_id) ;
3309
3310 IF (l_result = true) THEN
3311 RETURN 1 ;
3312 ELSE
3313 RETURN 0 ;
3314 END IF;
3315
3316 END ap_inv_generate_account_wrap;
3317
3318 FUNCTION validatevendor
3319 (p_vendor_id IN NUMBER
3320 )
3321 RETURN VARCHAR2 IS
3322 l_active_sup VARCHAR2(1);
3323 BEGIN
3324
3325 SELECT 'Y'
3326 INTO l_active_sup
3327 FROM ap_suppliers
3328 WHERE vendor_id = p_vendor_id
3329 AND sysdate <= nvl(end_date_active, sysdate + 1);
3330
3331 RETURN 'Y';
3332
3333 EXCEPTION
3334 WHEN OTHERS THEN
3335 RETURN 'N';
3336 END;
3337
3338 END AP_ISP_UTILITIES_PKG;