[Home] [Help]
PACKAGE BODY: APPS.AP_ISP_UTILITIES_PKG
Source
1 PACKAGE BODY AP_ISP_UTILITIES_PKG AS
2 /* $Header: apisputb.pls 120.27.12010000.3 2009/01/23 10:03:35 manjayar 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 IF (l_invoice_type_lookup_code= 'STANDARD') THEN
130 l_doc_category_code := 'STD INV';
131 ELSIF (l_invoice_type_lookup_code= 'PAYMENT REQUEST') THEN
132 l_doc_category_code := 'PAY REQ INV';
133 ELSIF (l_invoice_type_lookup_code= 'CREDIT') THEN
134 l_doc_category_code := 'CRM INV';
135 ELSIF (l_invoice_type_lookup_code= 'PREPAYMENT') THEN
136 l_doc_category_code := 'PREPAY INV';
137 END IF;
138
139 debug_info := '-----> l_doc_category_code = ' || l_doc_category_code ;
140 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
141 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, debug_info);
142 END IF;
143
144 ---------------------------------------------------------------------------
145 -- Step 4
146 -- Get Doc Sequence Number
147 ---------------------------------------------------------------------------
148
149 IF ((l_doc_category_code IS NOT NULL) )THEN
150
151 debug_info := 'Valid Category ->Check if valid Sequence assigned';
152 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
153 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, debug_info);
154 END IF;
155
156 BEGIN
157 SELECT SEQ.DB_SEQUENCE_NAME,
158 SEQ.DOC_SEQUENCE_ID,
159 SA.doc_sequence_assignment_id
160 INTO p_db_seq_name,
161 p_db_sequence_id ,
162 l_doc_seq_ass_id
163 FROM FND_DOCUMENT_SEQUENCES SEQ,
164 FND_DOC_SEQUENCE_ASSIGNMENTS SA
165 WHERE SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
166 AND SA.APPLICATION_ID = 200
167 AND SA.CATEGORY_CODE = l_doc_category_code
168 AND (NVL(SA.METHOD_CODE,'A') = 'A')
169 AND (SA.SET_OF_BOOKS_ID = l_set_of_books_id)
170 AND NVL(l_gl_date, g_inv_sysdate) between
171 SA.START_DATE and
172 NVL(SA.END_DATE, TO_DATE('31/12/4712','DD/MM/YYYY'));
173 EXCEPTION
174 WHEN NO_DATA_FOUND Then
175 RAISE get_doc_seq_failure;
176 END; -- end of the above BEGION
177
178 ----------------------------------------------------------------------
179 -- Step 5
180 -- Get Doc Sequence Val
181 ----------------------------------------------------------------------
182 debug_info := 'Get Next Val';
183 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
184 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, debug_info);
185 END IF;
186
187 l_return_code := FND_SEQNUM.GET_SEQ_VAL(
188 200,
189 l_doc_category_code,
190 l_set_of_books_id,
191 'A',
192 NVL(l_gl_date, sysdate),
193 p_db_sequence_value,
194 p_db_sequence_id ,
195 'N',
196 'N');
197 debug_info := '-----------> l_doc_category_code = '|| l_doc_category_code
198 || ' p_set_of_books_id = '||to_char(l_set_of_books_id)
199 || ' p_db_sequence_id = '||to_char(p_db_sequence_id )
200 ||' p_db_seq_name = '||p_db_seq_name
201 ||' p_db_sequence_value = '||to_char(p_db_sequence_value);
202 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
203 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
204 END IF;
205
206 END IF; -- end of check l_current_invoice_status/doc_category_code
207 END IF; -- p_sequence_numbering = 'N'
208
209 p_doc_category_code := l_doc_category_code;
210
211 EXCEPTION
212 WHEN OTHERS THEN
213
214 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
215 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, debug_info);
216 END IF;
217
218 IF (SQLCODE < 0) THEN
219 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
220 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, SQLERRM);
221 END IF;
222 END IF;
223
224 END get_doc_sequence;
225
226 ------------------------------------------------------------------
227 -- This function is used to get payment terms information.
228 --
229 ------------------------------------------------------------------
230 PROCEDURE get_payment_terms (
231 p_invoice_id IN NUMBER,
232 p_terms_id OUT NOCOPY NUMBER,
233 p_terms_date OUT NOCOPY DATE,
234 p_calling_sequence IN VARCHAR2)
235 IS
236
237 l_po_header_id ap_invoices_all.po_header_id%TYPE;
238 l_vendor_site_id ap_invoices_all.vendor_site_id%TYPE;
239 l_org_id ap_invoices_all.org_id%TYPE;
240 l_invoice_type_lookup_code ap_invoices_all.invoice_type_lookup_code%TYPE;
241 l_invoice_date ap_invoices_all.invoice_date%TYPE;
242 l_invoice_received_date ap_invoices_all.invoice_received_date%TYPE;
243 l_goods_received_date ap_invoices_all.goods_received_date%TYPE;
244 l_terms_date_basis ap_system_parameters_all.terms_date_basis%TYPE;
245 l_term_id_per_name NUMBER := Null;
246 l_start_date_active DATE;
247 l_end_date_active DATE;
248 l_start_date_active_per_name DATE;
249 l_end_date_active_per_name DATE;
250 current_calling_sequence VARCHAR2(2000);
251 debug_info VARCHAR2(500);
252 l_term_name VARCHAR2(50);--Bug 4115712
253 l_no_calendar_exists VARCHAR2(1); --Bug 4115712
254 l_api_name VARCHAR2(50);
255
256
257 BEGIN
258 -- Update the calling sequence
259 --
260 current_calling_sequence :=
261 'AP_IMPORT_VALIDATION_PKG.get_payment_terms<-'
262 ||P_calling_sequence;
263 l_api_name := 'get_payment_terms';
264 --------------------------------------------------------------------------
265 -- terms defaulting: if PO exists for the invoice,
266 -- use PO terms, otherwise use terms from Supplier Site.
267 --------------------------------------------------------------------------
268 BEGIN
269 select po_header_id, vendor_site_id, org_id,
270 invoice_type_lookup_code, invoice_date,
271 invoice_received_date, goods_received_date
272 into l_po_header_id, l_vendor_site_id, l_org_id,
273 l_invoice_type_lookup_code, l_invoice_date,
274 l_invoice_received_date, l_goods_received_date
275 from ap_invoices_all
276 where invoice_id = p_invoice_id;
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 debug_info := 'no data found for the invoice id = '|| p_invoice_id;
280 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
281 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, debug_info);
282 END IF;
283
284 return;
285 END;
286 debug_info := l_api_name || ': po_header_id = ' || l_po_header_id ||
287 ', vendor_site_id = ' || l_vendor_site_id || ', org_id = ' ||
288 l_org_id || ', invoice_type = '|| l_invoice_type_lookup_code ||
289 ', invoice_date = ' || l_invoice_date;
290 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
291 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
292 END IF;
293
294 BEGIN
295 select terms_date_basis
296 into l_terms_date_basis
297 from ap_system_parameters_all
298 where org_id = l_org_id;
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 debug_info := 'no ap options found for the org id = '|| l_org_id;
302 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
303 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, debug_info);
304 END IF;
305
306 return;
307 END;
308 debug_info := l_api_name || ': terms_date_basis = ' || l_terms_date_basis;
309 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
310 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
311 END IF;
312
313 --------------------------------------------------------------
314 -- Step 1
315 -- get payment terms from PO or Supplier Site.
316 --------------------------------------------------------------
317 IF (l_po_header_id is NOT NULL) Then
318 debug_info := 'Get term_id from header po_number';
319 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
320 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
321 END IF;
322
323 SELECT terms_id
324 INTO p_terms_id
325 FROM po_headers_all
326 WHERE po_header_id = l_po_header_id
327 AND type_lookup_code in ('BLANKET', 'PLANNED', 'STANDARD');
328
329 debug_info := l_api_name || ': p_terms_id = ' || p_terms_id;
330 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
331 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
332 END IF;
333 END IF;
334
335 -- no term from header level po_number, try lines level po_number
336 IF (p_terms_id is null ) THEN
337 debug_info := 'Get term_id from lines po_numbers';
338 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
339 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
340 END IF;
341 BEGIN
342 SELECT p.terms_id
343 INTO p_terms_id
344 FROM po_headers_all p, ap_invoice_lines_all l
345 WHERE p.type_lookup_code in ('BLANKET', 'PLANNED', 'STANDARD')
346 AND l.po_header_id = p.po_header_id
347 AND l.invoice_id = p_invoice_id
348 AND p.terms_id IS NOT NULL
349 GROUP BY p.terms_id;
350 EXCEPTION
351 WHEN NO_DATA_FOUND THEN
352 NULL;
353 WHEN TOO_MANY_ROWS THEN
354 p_terms_id := null;
355 END;
356
357 -- no term from line level PO, try line level receipt
358 IF (p_terms_id is null) THEN
359 debug_info := 'Get term_id from lines receipt';
360 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
361 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
362 END IF;
363 BEGIN
364 SELECT p.terms_id
365 INTO p_terms_id
366 FROM rcv_shipment_lines r,
367 po_headers_all p,
368 ap_invoice_lines_all l
369 WHERE p.po_header_id = r.po_header_id
370 AND r.shipment_line_id = l.rcv_shipment_line_id
371 AND l.invoice_id = p_invoice_id
372 AND p.terms_id IS NOT NULL
373 GROUP BY p.terms_id;
374 EXCEPTION
375 WHEN NO_DATA_FOUND THEN
376 NULL;
377 WHEN TOO_MANY_ROWS THEN
378 debug_info := 'too many rows';
379 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
380 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
381 END IF;
382 p_terms_id := null;
383 END;
384
385 END IF; -- end get term from line level receipt
386
387 END IF; -- end get term from line level
388
389 -- no term from header or line level
390 IF ( (p_terms_id is null) AND
391 (l_invoice_type_lookup_code <> 'PAYMENT REQUEST') ) Then
392
393 debug_info := 'Get term_id from supplier site';
394 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
395 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
396 END IF;
397
398 SELECT terms_id
399 INTO p_terms_id
400 FROM po_vendor_sites_all
401 WHERE vendor_site_id = l_vendor_site_id;
402
403 ELSIF ( (p_terms_id is null) AND
404 (l_invoice_type_lookup_code = 'PAYMENT REQUEST') ) Then
405
406 debug_info := 'Get term_id from financials options';
407 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
408 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
409 END IF;
410
411 SELECT terms_id
412 INTO p_terms_id
413 FROM financials_system_params_all
414 WHERE org_id = l_org_id;
415
416 END IF;
417
418 debug_info := 'getting term active date';
419 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
420 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
421 END IF;
422
423 SELECT start_date_active, end_date_active
424 INTO l_start_date_active, l_end_date_active
425 FROM ap_terms
426 WHERE term_id = p_terms_id;
427
428 debug_info := 'terms id derived: '|| p_terms_id;
429 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
430 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
431 END IF;
432
433 --------------------------------------------------------------------------
434 -- Step 2
435 -- Derive terms date if possible
436 --
437 --------------------------------------------------------------------------
438 IF ( p_terms_id is not null ) THEN
439 IF (l_terms_date_basis = 'Invoice Received') THEN
440 p_terms_date := l_invoice_received_date;
441 ELSIF (l_terms_date_basis = 'Goods Received') THEN
442 p_terms_date := l_goods_received_date;
443 ELSIF (l_terms_date_basis = 'Invoice') THEN
444 p_terms_date := l_invoice_date;
445 ELSIF (l_terms_date_basis = 'Current') THEN
446 p_terms_date := g_inv_sysdate;
447 ELSE
448 p_terms_date := g_inv_sysdate;
449 END IF;
450 END IF;
451
452 -- Bug 4115712
453 ------------------------------------------------------------------------------
454 -- Step 4
455 -- For calendar based payment terms :
456 -- Check if special calendar exists for the period
457 -- in which the terms date falls, else fail insert.
458 -----------------------------------------------------------------------------
459 debug_info := 'Check calendar based payment terms';
460
461 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
462 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
463 END IF;
464
465 --Bug:4115712
466 IF (p_terms_id IS NOT NULL) THEN
467
468 select name
469 into l_term_name
470 from ap_terms
471 where term_id = p_terms_id;
472
473 END IF;
474
475 AP_TERMS_CAL_EXISTS_PKG.Check_For_Calendar(
476 P_Terms_Name => l_term_name,
477 P_Terms_Date => p_terms_date,
478 P_No_Cal => l_no_calendar_exists,
479 P_Calling_Sequence => 'v_check_invalidate_terms');
480
481 EXCEPTION
482 WHEN OTHERS THEN
483 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
484 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, debug_info);
485 END IF;
486
487 IF (SQLCODE < 0) THEN
488 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
489 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, SQLERRM);
490 END IF;
491 END IF;
492
493 END get_payment_terms;
494
495
496 /*=============================================================================
497 | Function Cancel_Single_Invoice
498 |
499 | Cancels one invoice by executing the following sequence of steps.
500 | This is the wrapper procedure based on
501 | ap_cancel_pkg.ap_cancel_single_invoice
502 |
503 | PROGRAM FLOW
504 |
505 | 1. check if invoice cancellable, if yes, proceed otherwise return false
506 | 3.(If invoice has had tax withheld, undo withholding) - commented
507 | 4. Clear out payment schedules
508 | 5. Cancel all the non-discard lines
509 | a. reverse matching
510 | b. fetch the maximum distribution line number
511 | c. Set encumbered flags to 'N'
512 | d. Accounting event generation
513 | e. reverse the distributions
514 | f. update Line level Cancelled information
515 | 6. Zero out the Invoice
516 | 7. Run AutoApproval for this invoice
517 | 8. check posting holds remain on this canncelled invoice
518 | a. if NOT exist - complete the cancellation by updating header
519 | level information set return value to TRUE
520 | b. if exist - no update, set the return valuse to FALSE, NO
521 | DATA rollback.
522 | 9. Commit Data
523 | 10. Populate the out parameters.
524 |
525 | NOTES
526 | 1. bug2328225 case of Matching a special charge only invoice to
527 | receipt so we check if the quantity invoiced is not null too
528 | 2. Events Project
529 | We no longer need to prevent the cancellation of an invoice
530 | just because the accounting of related payments has not been
531 | created. Therefore, bug fixes 902110 and 2237152 are removed.
532 |
533 | MODIFICATION HISTORY
534 | Date Author Description of Change
535 |
536 *============================================================================*/
537
538 PROCEDURE Cancel_Single_Invoice(
539 P_invoice_id IN NUMBER,
540 P_last_updated_by IN NUMBER,
541 P_last_update_login IN NUMBER,
542 P_accounting_date IN DATE,
543 P_message_name OUT NOCOPY VARCHAR2,
544 P_Token OUT NOCOPY VARCHAR2,
545 P_calling_sequence IN VARCHAR2)
546 IS
547
548 l_invoice_amount NUMBER;
549 l_base_amount NUMBER;
550 l_temp_cancelled_amount NUMBER;
551 l_cancelled_by NUMBER;
552 l_cancelled_amount NUMBER;
553 l_pay_curr_invoice_amount NUMBER;
554 l_cancelled_date DATE;
555 l_last_update_date DATE;
556 l_debug_info VARCHAR2(240);
557 l_original_prepayment_amount NUMBER;
558 l_curr_calling_sequence VARCHAR2(2000);
559 l_result BOOLEAN;
560 l_api_name VARCHAR2(50);
561 l_org_id NUMBER;
562
563 BEGIN
564 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.CANCEL_SINGLE_INVOICE<-' ||
565 P_calling_sequence;
566
567 l_api_name := 'cancel_single_invoice';
568 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
569 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.CANCEL_SINGLE_INVOICE(+)');
570 END IF;
571
572 l_debug_info := 'calling ap_cancel_pkg.ap_cancel_single_invoice()...';
573 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
574 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
575 END IF;
576
577
578 --5126689, since a supplier can now cancel an invoice we need to set
579 --the org and initialize, otherwise the code may not see the invoice on
580 --the moac synonyms and po will fail when they try to close the po.
581 select org_id
582 into l_org_id
583 from ap_invoices_all
584 where invoice_id = p_invoice_id;
585
586 mo_global.set_policy_context('S',l_org_id);
587 fnd_global.apps_initialize (
588 user_id =>P_last_updated_by,
589 resp_id =>-1,
590 resp_appl_id => 200); --ap
591
592
593
594 l_result := AP_CANCEL_PKG.AP_Cancel_Single_Invoice(
595 p_invoice_id,
596 p_last_updated_by,
597 p_last_update_login,
598 p_accounting_date,
599 p_message_name,
600 l_invoice_amount,
601 l_base_amount,
602 l_temp_cancelled_amount,
603 l_cancelled_by,
604 l_cancelled_amount,
605 l_cancelled_date,
606 l_last_update_date,
607 l_original_prepayment_amount,
608 l_pay_curr_invoice_amount,
609 p_token,
610 l_curr_calling_sequence);
611 l_debug_info := 'ap_cancel_single_invoice() called ';
612 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
613 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
614 END IF;
615 -- commit
616 -- ISP:CodeCleanup Bug 5256954
617 -- commit;
618
619 EXCEPTION
620 WHEN OTHERS THEN
621 IF (SQLCODE <> -20001) THEN
622 rollback;
623 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
624 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
625 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
626 FND_MESSAGE.SET_TOKEN('PARAMETERS',
627 ' P_invoice_id = ' || P_invoice_id
628 ||' P_last_updated_by = ' || P_last_updated_by
629 ||' P_last_update_login = ' || P_last_update_login
630 ||' P_accounting_date = ' || P_accounting_date);
631 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
632 END IF;
633
634 APP_EXCEPTION.RAISE_EXCEPTION;
635
636
637 END Cancel_Single_Invoice;
638
639
640 /*=============================================================================
641 | public FUNCTION Discard_Inv_Line
642 |
643 | This is a wrapper function based on
644 | ap_invoice_lines_pkg.discard_inv_line().
645 |
646 | Discard or cancel the invoice line depending on calling mode. If error
647 | occurs, it return 1 and error code will be populated. Otherwise,
648 | It return 0.
649 |
650 | Parameters
651 | P_line_rec - Invoice line record
652 | P_calling_mode - either from DISCARD, CANCEL or UNAPPLY_PREPAY
653 | p_inv_cancellable - 'Y' if invoice is canellable.
654 | P_last_updated_by
655 | P_last_update_login
656 | P_error_code - Error code indicates why it is not discardable
657 | P_calling_sequence - For debugging purpose
658 |
659 *===========================================================================*/
660 /* Bug 5470344 XBuild11 Code cleanup
661 This code is not being used
662 PROCEDURE Discard_Inv_Line(
663 p_invoice_id IN ap_invoice_lines.invoice_id%TYPE,
664 p_line_number IN ap_invoice_lines.line_number%TYPE,
665 p_calling_mode IN VARCHAR2,
666 p_inv_cancellable IN VARCHAR2 DEFAULT NULL,
667 P_last_updated_by IN NUMBER,
668 P_last_update_login IN NUMBER,
669 P_error_code OUT NOCOPY VARCHAR2,
670 P_token OUT NOCOPY VARCHAR2,
671 P_calling_sequence IN VARCHAR2)
672 IS
673
674 l_line_rec ap_invoice_lines%ROWTYPE;
675 l_curr_calling_sequence VARCHAR2(2000);
676 l_debug_info VARCHAR2(2000);
677 l_api_name VARCHAR2(50);
678 l_result NUMBER;
679
680 BEGIN
681
682 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.discard_inv_line <- ' ||
683 p_calling_sequence;
684
685 l_api_name := 'discard_inv_line';
686 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
687 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.discard_inv_line(+)');
688 END IF;
689
690 l_debug_info := 'get invoice line info...';
691 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
692 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
693 END IF;
694
695 select invoice_id, line_number,
696 po_line_location_id,
697 rcv_transaction_id, accounting_date,
698 amount, unit_price, unit_meas_lookup_code,
699 quantity_invoiced, discarded_flag, cancelled_flag,
700 period_name,
701 line_type_lookup_code, match_type
702 into l_line_rec.invoice_id, l_line_rec.line_number,
703 l_line_rec.po_line_location_id,
704 l_line_rec.rcv_transaction_id,
705 l_line_rec.accounting_date,
706 l_line_rec.amount, l_line_rec.unit_price,
707 l_line_rec.unit_meas_lookup_code,
708 l_line_rec.quantity_invoiced,
709 l_line_rec.discarded_flag, l_line_rec.cancelled_flag,
710 l_line_rec.period_name,
711 l_line_rec.line_type_lookup_code,
712 l_line_rec.match_type
713 from ap_invoice_lines_all
714 where invoice_id = p_invoice_id
715 and line_number = p_line_number;
716
717 l_debug_info := 'invoice_id = ' || l_line_rec.invoice_id ||
718 ', line_number = '|| l_line_rec.line_number;
719 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
720 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
721 END IF;
722
723 if ( ap_invoice_lines_pkg.discard_inv_line(p_line_rec => l_line_rec,
724 p_calling_mode => p_calling_mode,
725 p_inv_cancellable => p_inv_cancellable,
726 p_last_updated_by => p_last_updated_by,
727 p_last_update_login => p_last_update_login,
728 p_error_code => p_error_code,
729 p_token => p_token,
730 p_calling_sequence => p_calling_sequence) ) then
731 l_result := 0;
732 else
733 l_result := 1;
734 end if;
735
736 l_debug_info := 'discard_inv_line called ';
737 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
738 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
739 END IF;
740
741 -- return l_result;
742 -- commit;
743
744 EXCEPTION
745 WHEN OTHERS THEN
746 IF (SQLCODE <> -20001) THEN
747 rollback;
748 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
749 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
750 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
751 FND_MESSAGE.SET_TOKEN('PARAMETERS',
752 ' P_invoice_id = ' || p_invoice_id
753 ||' P_line_number = ' || p_line_number
754 ||' P_last_updated_by = ' || P_last_updated_by
755 ||' P_last_update_login = ' || P_last_update_login
756 ||' P_calling_mode = ' || p_calling_mode);
757 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
758 END IF;
759
760 APP_EXCEPTION.RAISE_EXCEPTION;
761
762 END Discard_Inv_line;
763 */
764
765 /* Bug 5407726 ISP Code cleanup XBuild9
766 This code is not being used
767 =============================================================================
768 | public procedure invoke_ap_workflow
769 | starts up a workflow process for AP invoice
770 |
771 | Parameters
772 | P_invoice_id - invoice id
773 | P_calling_sequence - For debugging purpose
774 |
775 *===========================================================================
776 PROCEDURE invoke_ap_workflow(
777 p_item_key IN VARCHAR2,
778 p_invoice_id IN ap_invoices.invoice_id%TYPE,
779 p_org_id IN ap_invoices.org_id%TYPE,
780 P_calling_sequence IN VARCHAR2)
781 IS
782
783 l_curr_calling_sequence VARCHAR2(2000);
784 l_debug_info VARCHAR2(2000);
785 l_api_name VARCHAR2(50);
786 l_result NUMBER;
787 l_item_key VARCHAR2(100);
788 l_iteration NUMBER;
789 l_invoice_supplier_name VARCHAR2(80);
790 l_invoice_number VARCHAR2(50);
791 l_invoice_date DATE;
792 l_invoice_description VARCHAR2(240);
793 l_supplier_role VARCHAR2(320);
794 BEGIN
795
796 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.invoke_ap_workflow <- ' ||
797 p_calling_sequence;
798
799 l_api_name := 'invoke_ap_workflow';
800 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
801 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.invoke_ap_workflow(+)');
802 END IF;
803
804 --
805 -- Creating a workflow process
806 --
807 l_debug_info := 'creating a workflow process...';
808 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
809 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
810 END IF;
811
812 WF_ENGINE.createProcess('APINVLDP',p_item_key, 'DISPUTE_MAIN');
813
814 l_debug_info := 'workflow process created. ';
815 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
816 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
817 END IF;
818
819
820 --
821 -- Initializing attributes
822 --
823 l_debug_info := 'setting workflow process attributes... ';
824 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
825 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
826 END IF;
827
828 WF_ENGINE.setItemAttrText('APINVLDP',p_item_key, 'INVOICE_ID', p_invoice_id);
829 WF_ENGINE.setItemAttrText('APINVLDP',p_item_key, 'ORG_ID', p_org_id);
830 l_debug_info := 'invoke_ap_workflow: invoice_id = ' || p_invoice_id ||
831 ', org_id = ' || p_org_id;
832 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
833 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
834 END IF;
835
836
837 SELECT
838 PV.vendor_name,
839 AI.invoice_num,
840 AI.invoice_date,
841 AI.description,
842 decode(AI.source, 'ISP', u.user_name, null)
843 INTO
844 l_invoice_supplier_name,
845 l_invoice_number,
846 l_invoice_date,
847 l_invoice_description,
848 l_supplier_role
849 FROM
850 ap_invoices_all AI,
851 po_vendors PV,
852 po_vendor_sites_all PVS,
853 fnd_user u
854 WHERE
855 AI.invoice_id = p_invoice_id AND
856 AI.vendor_id = PV.vendor_id AND
857 AI.vendor_site_id = PVS.vendor_site_id(+) and
858 u.user_id = ai.created_by;
859
860 l_debug_info := 'invoke_ap_workflow: iteration = ' || l_iteration ||
861 ', itemkey = '|| p_item_key ||
862 ', supplier_name = '|| l_invoice_supplier_name ||
863 ', invoice_number = '|| l_invoice_number ||
864 ', invoice_date = '|| l_invoice_date ||
865 ', supplier_role = '|| l_supplier_role;
866 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
867 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
868 END IF;
869
870 -- l_iteration := substr(p_item_key, instr(p_item_key,'_')+1, length(p_item_key));
871 l_iteration := to_number(substr(p_item_key, instr(p_item_key,'_')+1));
872 WF_ENGINE.setItemAttrNumber('APINVLDP',p_item_key, 'ITERATION', l_iteration);
873 WF_ENGINE.setItemAttrText('APINVLDP',p_item_key, 'INVOICE_SUPPLIER_NAME', l_invoice_supplier_name);
874 WF_ENGINE.setItemAttrText('APINVLDP',p_item_key, 'INVOICE_NUMBER', l_invoice_number);
875 WF_ENGINE.setItemAttrText('APINVLDP',p_item_key, 'INVOICE_DESCRIPTION', l_invoice_description);
876 WF_ENGINE.setItemAttrDate('APINVLDP',p_item_key, 'INVOICE_DATE', l_invoice_date);
877 WF_ENGINE.setItemAttrText('APINVLDP',p_item_key, 'SUPPLIER_ROLE', l_supplier_role);
878
879 l_debug_info := 'workflow process attributes set. ';
880 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
881 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
882 END IF;
883
884
885 --
886 -- Starting the process
887 --
888 l_debug_info := 'workflow process starting... ';
889 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
890 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
891 END IF;
892
893 WF_ENGINE.startProcess('APINVLDP', p_item_key);
894 l_debug_info := 'workflow process started. ';
895 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
896 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
897 END IF;
898
899
900
901 WF_ENGINE.launchProcess('APINVLDP',p_item_key, 'DISPUTE_MAIN');
902 l_debug_info := 'workflow process launched. ';
903 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
904 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
905 END IF;
906
907 commit;
908
909 EXCEPTION
910 WHEN OTHERS THEN
911 IF (SQLCODE <> -20001) THEN
912 rollback;
913 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
914 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
915 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
916 FND_MESSAGE.SET_TOKEN('PARAMETERS',
917 ' P_invoice_id = ' || p_invoice_id
918 ||' P_org_id = ' || p_org_id);
919 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
920 END IF;
921
922 APP_EXCEPTION.RAISE_EXCEPTION;
923
924 END invoke_ap_workflow;
925 */
926
927 /*=============================================================================
928 | public procedure override_tax
929 | this is a wrapper procedure for overriding tax
930 |
931 | Parameters
932 | P_invoice_id - invoice id
933 | P_calling_sequence - For debugging purpose
934 |
935 *===========================================================================*/
936 PROCEDURE override_tax(
937 P_Invoice_id IN NUMBER,
938 P_Calling_Mode IN VARCHAR2,
939 P_Override_Status IN VARCHAR2,
940 P_Event_Id IN NUMBER,
941 P_All_Error_Messages IN VARCHAR2,
942 P_Error_Code OUT NOCOPY VARCHAR2,
943 P_Calling_Sequence IN VARCHAR2)
944 IS
945
946 l_curr_calling_sequence VARCHAR2(2000);
947 l_debug_info VARCHAR2(2000);
948 l_api_name VARCHAR2(50);
949 l_result BOOLEAN;
950
951 BEGIN
952
953 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.override_tax <- ' ||
954 p_calling_sequence;
955
956 l_api_name := 'override_tax';
957 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
958 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.override_tax(+)');
959 END IF;
960
961 l_debug_info := 'calling AP_ETAX_SERVICES_PKG.Override_Tax: '
962 ||' P_invoice_id = ' || p_invoice_id
963 ||', P_calling_mode = ' || p_calling_mode
964 ||', P_override_status = ' || p_override_status
965 ||', P_event_id = ' || P_event_id;
966 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
967 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
968 END IF;
969
970 l_result := AP_ETAX_SERVICES_PKG.Override_Tax(
971 P_Invoice_id => p_invoice_id,
972 P_Calling_Mode => p_calling_mode,
973 P_Override_Status => p_override_status,
974 P_Event_Id => p_event_id,
975 P_All_Error_Messages => p_all_error_messages,
976 P_Error_Code => p_error_code,
977 P_Calling_Sequence => l_curr_calling_sequence);
978
979 l_debug_info := 'AP_ETAX_SERVICES_PKG.override_tax called: '||
980 ' error_code = ' || p_error_code;
981 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
982 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
983 END IF;
984
985 -- return l_result;
986 -- ISP:CodeCleanup Bug 5256954
987 -- commit;
988
989 EXCEPTION
990 WHEN OTHERS THEN
991 IF (SQLCODE <> -20001) THEN
992 rollback;
993 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
994 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
995 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
996 FND_MESSAGE.SET_TOKEN('PARAMETERS',
997 ' P_invoice_id = ' || p_invoice_id
998 ||', P_calling_mode = ' || p_calling_mode
999 ||', P_override_status = ' || p_override_status
1000 ||', P_event_id = ' || P_event_id
1001 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1002 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1003 END IF;
1004
1005 APP_EXCEPTION.RAISE_EXCEPTION;
1006
1007 END override_tax;
1008
1009 /*=============================================================================
1010 | public procedure populate_payment_terms
1011 | this procedure populates payment terms id and date to invoice header
1012 |
1013 | Parameters
1014 | P_invoice_id - invoice id
1015 | P_calling_sequence - For debugging purpose
1016 |
1017 *===========================================================================*/
1018 PROCEDURE populate_payment_terms(
1019 P_Invoice_id IN NUMBER,
1020 P_Calling_Sequence IN VARCHAR2)
1021 IS
1022
1023 l_curr_calling_sequence VARCHAR2(2000);
1024 l_debug_info VARCHAR2(2000);
1025 l_api_name VARCHAR2(50);
1026 l_terms_Id NUMBER;
1027 l_terms_date DATE;
1028
1029 BEGIN
1030
1031 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.populate_payment_terms <- ' ||
1032 p_calling_sequence;
1033
1034 l_api_name := 'populate_payment_terms';
1035 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1036 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.populate_payment_terms(+)');
1037 END IF;
1038
1039 get_payment_terms (p_invoice_id => p_invoice_id,
1040 p_terms_id => l_terms_id,
1041 p_terms_date => l_terms_date,
1042 p_calling_sequence => l_curr_calling_sequence);
1043
1044 update ap_invoices_all
1045 set terms_id = l_terms_id,
1046 terms_date = l_terms_date
1047 where invoice_id = p_invoice_id;
1048
1049 l_debug_info := 'invoice header record updated with terms id: '||
1050 l_terms_id ||', terms_date = '|| l_terms_date;
1051 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1052 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1053 END IF;
1054
1055 -- commit;
1056
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 IF (SQLCODE <> -20001) THEN
1060 rollback;
1061 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1062 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1063 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1064 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1065 ' P_invoice_id = ' || p_invoice_id
1066 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1067 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1068 END IF;
1069
1070 APP_EXCEPTION.RAISE_EXCEPTION;
1071
1072 END populate_payment_terms;
1073
1074 /*=============================================================================
1075 | public procedure populate_doc_sequence
1076 | this procedure populates document sequence to invoice header
1077 |
1078 | Parameters
1079 | P_invoice_id - invoice id
1080 | P_calling_sequence - For debugging purpose
1081 |
1082 *===========================================================================*/
1083 PROCEDURE populate_doc_sequence(
1084 P_Invoice_id IN NUMBER,
1085 p_sequence_numbering IN VARCHAR2,
1086 p_calling_sequence IN VARCHAR2)
1087 IS
1088
1089 l_doc_category_code ap_invoices.doc_category_code%TYPE;
1090 l_db_sequence_value ap_invoices.doc_sequence_value%TYPE;
1091 l_db_sequence_id ap_invoices.doc_sequence_id%TYPE;
1092 l_db_sequence_name VARCHAR2(1000);
1093 l_curr_calling_sequence VARCHAR2(2000);
1094 l_debug_info VARCHAR2(2000);
1095 l_api_name VARCHAR2(50);
1096
1097 BEGIN
1098
1099 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.populate_doc_sequence<- ' ||
1100 p_calling_sequence;
1101
1102 l_api_name := 'populate_doc_sequence';
1103 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1104 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.populate_doc_sequence(+)');
1105 END IF;
1106
1107 get_doc_sequence(
1108 p_invoice_id => p_invoice_id,
1109 p_sequence_numbering => p_sequence_numbering,
1110 p_doc_category_code => l_doc_category_code,
1111 p_db_sequence_value => l_db_sequence_value,
1112 p_db_seq_name => l_db_sequence_name,
1113 p_db_sequence_id => l_db_sequence_id,
1114 p_calling_sequence => l_curr_calling_sequence);
1115
1116 l_debug_info := 'got the doc category code and sequence: '||
1117 l_doc_category_code ||', doc_seq_value = '|| l_db_sequence_value
1118 || ', doc_seq_id = ' || l_db_sequence_id;
1119 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1120 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1121 END IF;
1122
1123 update ap_invoices_all
1124 set doc_category_code = l_doc_category_code,
1125 doc_sequence_value = l_db_sequence_value,
1126 doc_sequence_id = l_db_sequence_id
1127 where invoice_id = p_invoice_id;
1128
1129 l_debug_info := 'invoice header record updated with doc category code: '||
1130 l_doc_category_code ||', doc_seq_value = '|| l_db_sequence_value
1131 || ', doc_seq_id = ' || l_db_sequence_id;
1132 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1133 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1134 END IF;
1135
1136 EXCEPTION
1137 WHEN OTHERS THEN
1138 IF (SQLCODE <> -20001) THEN
1139 rollback;
1140 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1141 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1142 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1143 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1144 ' P_invoice_id = ' || p_invoice_id
1145 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1146 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1147 END IF;
1148
1149 APP_EXCEPTION.RAISE_EXCEPTION;
1150
1151 END populate_doc_sequence;
1152
1153 PROCEDURE update_invoice_header(
1154 p_invoice_id IN NUMBER,
1155 p_sequence_numbering IN VARCHAR2,
1156 p_calling_sequence IN VARCHAR2)
1157
1158 IS
1159 l_item_sum ap_invoices_all.invoice_amount%TYPE;
1160 l_tax_sum ap_invoices_all.invoice_amount%TYPE;
1161 l_misc_sum ap_invoices_all.invoice_amount%TYPE;
1162 l_frt_sum ap_invoices_all.invoice_amount%TYPE;
1163 l_retained_sum ap_invoices_all.invoice_amount%TYPE;
1164 l_curr_calling_sequence VARCHAR2(2000);
1165 l_debug_info VARCHAR2(500);
1166 l_api_name VARCHAR2(50);
1167 l_hold_count NUMBER;
1168 l_line_count NUMBER;
1169 l_line_total NUMBER;
1170 l_Sched_Hold_count NUMBER;
1171 l_inv_currency_code ap_invoices_all.invoice_currency_code%TYPE;
1172 l_invoice_date ap_invoices_all.invoice_date%TYPE;
1173 l_base_currency_code ap_invoices_all.invoice_currency_code%TYPE;
1174 l_default_exchange_Rate_type ap_invoices_all.exchange_rate_type%TYPE;
1175 l_exchange_rate ap_invoices_all.exchange_rate%TYPE;
1176 l_exchange_date ap_invoices_all.exchange_date%TYPE;
1177 l_requester_id ap_invoices_all.requester_id%TYPE;
1178
1179 BEGIN
1180 -- Update the calling sequence
1181
1182 l_curr_calling_sequence := 'update_invoice_header <-'||P_calling_sequence;
1183
1184 l_api_name := 'update_invoice_header';
1185
1186 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1187 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.update_invoice_header(+)');
1188 END IF;
1189
1190 l_debug_info := 'Step 1. update invoice amount: invoice_id = ';
1191 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1192 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1193 END IF;
1194
1195 BEGIN
1196 -- Bug 5407726 ISP Code cleanup XBuild9
1197 SELECT SUM(DECODE(line_type_lookup_code,'ITEM',NVL(amount, 0) - NVL(included_tax_amount, 0) ,0)) ITEM_SUM,
1198 --Bug 5345946 XBuild7 Code Cleanup
1199 SUM(DECODE(line_type_lookup_code,'TAX',amount,0)) + SUM(NVL(included_tax_amount, 0)) TAX_SUM,
1200 SUM(DECODE(line_type_lookup_code,'MISCELLANEOUS',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) MISC_SUM, --Bug
1201 SUM(DECODE(line_type_lookup_code,'FREIGHT',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) FREIGHT_SUM,
1202 sum(decode(line_type_lookup_code, 'ITEM', NVL(retained_amount, 0), 0)) RETAINAGE_SUM
1203 INTO l_item_sum, l_tax_sum, l_misc_sum, l_frt_sum, l_retained_sum
1204 FROM ap_invoice_lines_all
1205 WHERE invoice_id = p_invoice_id;
1206
1207 EXCEPTION
1208 WHEN NO_DATA_FOUND THEN
1209 l_debug_info := 'no lines found for the invoice id = '|| p_invoice_id;
1210 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
1211 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, l_debug_info);
1212 END IF;
1213 END;
1214
1215 -- don't do anything if lines don't exist
1216 if ( l_item_sum <> 0 ) then
1217 update ap_invoices_all
1218 set invoice_amount = l_item_sum + l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,
1219 amount_applicable_to_discount = l_item_sum + l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,
1220 net_of_retainage_flag = DECODE(l_retained_sum, 0, 'N', 'Y')
1221 where invoice_id = p_invoice_id;
1222 end if;
1223
1224
1225
1226 l_debug_info := 'Step 2. populate document sequence: invoice_id = '||
1227 p_invoice_id || ', sequence_numbering = ' || p_sequence_numbering;
1228 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1229 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1230 END IF;
1231
1232 populate_doc_sequence(
1233 p_invoice_id => p_invoice_id,
1234 p_sequence_numbering => p_sequence_numbering,
1235 p_calling_sequence => l_curr_calling_sequence);
1236
1237
1238 l_debug_info := 'Step 3. populate payment terms';
1239 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1240 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1241 END IF;
1242
1243 populate_payment_terms(
1244 p_invoice_id => p_invoice_id,
1245 p_calling_sequence => l_curr_calling_sequence);
1246
1247 l_debug_info := 'invoice header updated. ';
1248 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1249 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1250 END IF;
1251
1252
1253 -- Bug 5470344 XBuild11 Code cleanup
1254 -- For Foriegn currency invoices, exchange rate is derieved from the
1255 -- OU settings. However if OU has exchange rate of NULL or 'User'
1256 -- ISP feature defaults the exchange rate to be of type 'Corporate'
1257 SELECT ai.invoice_currency_code,
1258 ai.invoice_date,
1259 asp.base_currency_code,
1260 DECODE(asp.default_exchange_rate_type,
1261 NULL, 'Corporate',
1262 'User', 'Corporate' ,
1263 asp.default_exchange_rate_type),
1264 ap_utilities_pkg.get_exchange_rate(
1265 ai.invoice_currency_code,
1266 asp.base_currency_code,
1267 DECODE(asp.default_exchange_rate_type,
1268 NULL, 'Corporate',
1269 'User', 'Corporate' ,
1270 asp.default_exchange_rate_type),
1271 ai.invoice_date,
1272 'ISP'),
1273 ai.invoice_date,
1274 requester_id
1275 INTO l_inv_currency_code,
1276 l_invoice_date,
1277 l_base_currency_code,
1278 l_default_exchange_Rate_type,
1279 l_exchange_rate,
1280 l_exchange_date,
1281 l_requester_id
1282 FROM ap_invoices_all ai,
1283 ap_system_parameters_all asp
1284 WHERE ai.org_id = asp.org_id
1285 and ai.invoice_id = p_invoice_id;
1286
1287 IF l_base_currency_code <> l_inv_currency_code THEN
1288
1289 UPDATE ap_invoices_all
1290 SET exchange_rate_type = l_default_exchange_rate_type,
1291 exchange_rate = l_exchange_rate,
1292 exchange_date = l_exchange_date
1293 WHERE invoice_id = p_invoice_id;
1294 END IF;
1295
1296 --Bug 5500186
1297 UPDATE ap_invoice_lines_all
1298 SET requester_id = l_requester_id
1299 WHERE line_type_lookup_code = 'ITEM'
1300 AND requester_id is NULL
1301 AND invoice_id = p_invoice_id;
1302
1303
1304 -- ISP:CodeCleanup Bug 5256954
1305 -- commit;
1306
1307 EXCEPTION
1308 WHEN OTHERS THEN
1309 IF (SQLCODE <> -20001) THEN
1310 rollback;
1311 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1312 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1313 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1314 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1315 ' P_invoice_id = ' || p_invoice_id
1316 || ', sequence_numbering = ' || p_sequence_numbering
1317 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1318 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1319 END IF;
1320
1321 APP_EXCEPTION.RAISE_EXCEPTION;
1322
1323 END update_invoice_header;
1324
1325 procedure create_distributions(p_invoice_id IN NUMBER) IS
1326 l_invoice_rec ap_approval_pkg.Invoice_Rec;
1327 l_base_currency_code ap_system_parameters_all.base_currency_code%TYPE;
1328
1329 l_invoice_id ap_invoices_all.invoice_id%type;
1330 l_invoice_num ap_invoices_all.invoice_num%type;
1331 l_org_id ap_invoices_all.org_id%type;
1332 l_invoice_amount ap_invoices_all.invoice_amount%type;
1333 l_base_amount ap_invoices_all.base_amount%type;
1334 l_exchange_rate ap_invoices_all.exchange_rate%type;
1335 l_invoice_currency_code ap_invoices_all.invoice_currency_code%type;
1336 l_invoice_amount_limit ap_supplier_sites_all.invoice_amount_limit%type;
1337 l_hold_future_payments_flag ap_supplier_sites_all.hold_future_payments_flag%type;
1338 l_invoice_type_lookup_code ap_invoices_all.invoice_type_lookup_code%type;
1339 l_exchange_date ap_invoices_all.exchange_date%type;
1340 l_exchange_rate_type ap_invoices_all.exchange_rate_type%type;
1341 l_vendor_id ap_invoices_all.vendor_id%type;
1342 l_invoice_date ap_invoices_all.invoice_date%type;
1343 l_disc_is_inv_less_tax_flag ap_invoices_all.disc_is_inv_less_tax_flag%type;
1344 l_exclude_freight_from_disc ap_invoices_all.exclude_freight_from_discount%type;
1345 l_tolerance_id ap_supplier_sites_all.tolerance_id%type;
1346 l_services_tolerance_id ap_supplier_sites_all.services_tolerance_id%type;
1347 l_error_code VARCHAR2(4000);
1348 l_curr_calling_sequence VARCHAR2(2000);
1349 l_debug_info VARCHAR2(500);
1350 CURSOR approve_invoice_cur IS
1351 SELECT AI.invoice_id,
1352 AI.invoice_num,
1353 AI.invoice_amount,
1354 AI.base_amount,
1355 AI.exchange_rate,
1356 AI.invoice_currency_code,
1357 PVS.invoice_amount_limit,
1358 nvl(PVS.hold_future_payments_flag,'N'),
1359 AI.invoice_type_lookup_code,
1360 AI.exchange_date,
1361 AI.exchange_rate_type,
1362 AI.vendor_id,
1363 AI.invoice_date,
1364 AI.org_id,
1365 nvl(AI.disc_is_inv_less_tax_flag,'N'),
1366 nvl(AI.exclude_freight_from_discount,'N'),
1367 pvs.tolerance_id,
1368 pvs.services_tolerance_id
1369 FROM ap_invoices_all AI,
1370 ap_suppliers PV,
1371 ap_supplier_sites_all PVS
1372 WHERE AI.invoice_id = p_invoice_id
1373 AND AI.vendor_id = PV.vendor_id
1374 AND AI.vendor_site_id = PVS.vendor_site_id;
1375 BEGIN
1376 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.create_distributions';
1377
1378 SELECT base_currency_code
1379 INTO l_base_currency_code
1380 FROM ap_system_parameters_all asp, ap_invoices_all ai
1381 WHERE ai.invoice_id = p_invoice_id
1382 AND asp.org_id = ai.org_id;
1383
1384 l_debug_info := 'Before OPEN Approve_Invoice_Cur';
1385 OPEN Approve_Invoice_Cur;
1386 l_debug_info := 'Before Fetch Approve_Invoice_Cur';
1387 FETCH Approve_Invoice_Cur
1388 INTO l_invoice_id,
1389 l_invoice_num,
1390 l_invoice_amount,
1391 l_base_amount,
1392 l_exchange_rate,
1393 l_invoice_currency_code,
1394 l_invoice_amount_limit,
1395 l_hold_future_payments_flag,
1396 l_invoice_type_lookup_code,
1397 l_exchange_date,
1398 l_exchange_rate_type,
1399 l_vendor_id,
1400 l_invoice_date,
1401 l_org_id,
1402 l_disc_is_inv_less_tax_flag,
1403 l_exclude_freight_from_disc,
1404 l_tolerance_id,
1405 l_services_tolerance_id;
1406 CLOSE Approve_Invoice_Cur;
1407
1408 l_invoice_rec.invoice_id := l_invoice_id;
1409 l_invoice_rec.invoice_num := l_invoice_num;
1410 l_invoice_rec.invoice_amount := l_invoice_amount;
1411 l_invoice_rec.base_amount := l_base_amount;
1412 l_invoice_rec.exchange_rate := l_exchange_rate;
1413 l_invoice_rec.invoice_currency_code := l_invoice_currency_code;
1414 l_invoice_rec.invoice_amount_limit := l_invoice_amount_limit;
1415 l_invoice_rec.hold_future_payments_flag := l_hold_future_payments_flag;
1416 l_invoice_rec.invoice_type_lookup_code := l_invoice_type_lookup_code;
1417 l_invoice_rec.exchange_date := l_exchange_date;
1418 l_invoice_rec.exchange_rate_type := l_exchange_rate_type;
1419 l_invoice_rec.vendor_id := l_vendor_id;
1420 l_invoice_rec.invoice_date := l_invoice_date;
1421 l_invoice_rec.org_id := l_org_id;
1422 l_invoice_rec.disc_is_inv_less_tax_flag := l_disc_is_inv_less_tax_flag;
1423 l_invoice_rec.exclude_freight_from_discount := l_exclude_freight_from_disc;
1424 l_invoice_rec.tolerance_id := l_tolerance_id;
1425 l_invoice_rec.services_tolerance_id := l_services_tolerance_id;
1426
1427 l_debug_info := 'Before AP_APPROVAL_PKG.Generate_Distributions';
1428 AP_APPROVAL_PKG.Generate_Distributions
1429 (p_invoice_rec => l_invoice_rec ,
1430 p_base_currency_code => l_base_currency_code,
1431 p_inv_batch_id => NULL,
1432 p_run_option => NULL,
1433 p_calling_sequence => l_curr_calling_sequence,
1434 x_error_code => l_error_code)
1435 ;
1436
1437
1438 EXCEPTION
1439 WHEN OTHERS THEN
1440 IF (SQLCODE <> -20001) THEN
1441 rollback;
1442 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1443 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1444 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1445 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1446 ' P_invoice_id = ' || p_invoice_id
1447 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1448 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1449 END IF;
1450
1451 APP_EXCEPTION.RAISE_EXCEPTION;
1452
1453 END;
1454
1455 -- Bug 5605359 (Prior to this bug fix Step 5 included Step 7. Once Tax is
1456 -- overridden the Pay Schedules need to be adjusted.
1457 -- ISP Flow is as follows --
1458 -- 1. Creation of Invoice Header
1459 -- 2. Creation of Invoice Lines
1460 -- 3. Updation of Invoice Lines with Retainage amt.
1461 -- 4. Calculate Tax(Call eTax) for Invoice Lines(Invoice Line Amt is Net of Retainage)
1462 -- 5. Updation of the Invoice(update_invoice_header)
1463 -- 6. Override Tax(Update Summary Tax Lines)
1464 -- 7. Updation of the Invoice with the correct Invoice Amt after Tax Override
1465 -- and creation of the Pay Schedules(update_invoice_header2)
1466 -- 8. For PO/RCV Matched Lines (Call PO Shipment/RCV Shipment Line match)
1467 -- Recoupment adjusts already created Pay Schedules.
1468 -- 9. Commit.
1469 PROCEDURE update_invoice_header2(
1470 p_invoice_id IN NUMBER,
1471 p_calling_sequence IN VARCHAR2)
1472
1473 IS
1474 l_item_sum ap_invoices_all.invoice_amount%TYPE;
1475 l_tax_sum ap_invoices_all.invoice_amount%TYPE;
1476 l_misc_sum ap_invoices_all.invoice_amount%TYPE;
1477 l_frt_sum ap_invoices_all.invoice_amount%TYPE;
1478 l_retained_sum ap_invoices_all.invoice_amount%TYPE;
1479 l_curr_calling_sequence VARCHAR2(2000);
1480 l_debug_info VARCHAR2(500);
1481 l_api_name VARCHAR2(50);
1482 l_hold_count NUMBER;
1483 l_line_count NUMBER;
1484 l_line_total NUMBER;
1485 l_Sched_Hold_count NUMBER;
1486 l_inv_currency_code ap_invoices_all.invoice_currency_code%TYPE;
1487 l_invoice_date ap_invoices_all.invoice_date%TYPE;
1488 l_base_currency_code ap_invoices_all.invoice_currency_code%TYPE;
1489 l_default_exchange_Rate_type ap_invoices_all.exchange_rate_type%TYPE;
1490 l_exchange_rate ap_invoices_all.exchange_rate%TYPE;
1491 l_exchange_date ap_invoices_all.exchange_date%TYPE;
1492 l_requester_id ap_invoices_all.requester_id%TYPE;
1493
1494 l_wfitemkey VARCHAR2(50);
1495 l_dist_set_id ap_supplier_sites_all.distribution_set_id%TYPE;
1496 -- Bug 6859035
1497 l_period_name ap_invoice_lines_all.period_name%TYPE;
1498 l_gl_date ap_invoice_lines_all.accounting_date%TYPE;
1499 l_org_id ap_invoices_all.org_id%TYPE;
1500
1501 -- Bug 7706967 : Start
1502 l_vendor_name ap_suppliers.vendor_name%TYPE;
1503 l_vendor_id ap_suppliers.vendor_id%TYPE;
1504 l_vendor_site_code ap_supplier_sites_all.vendor_site_code%TYPE;
1505 l_vendor_site_id ap_supplier_sites_all.vendor_site_id%TYPE;
1506 -- Bug 7706967 : End
1507
1508 BEGIN
1509 -- Update the calling sequence
1510
1511 l_curr_calling_sequence := 'update_invoice_header2 <-'||P_calling_sequence;
1512
1513 l_api_name := 'update_invoice_header2';
1514
1515 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1516 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.update_invoice_header2(+)');
1517 END IF;
1518
1519 -- Bug 6859035. Accounting date and period name are not getting
1520 -- stamped properly in invoice header and lines. In all the cases
1521 -- sysdate is being used to calculate the gl date and period name
1522 -- instead of invoice date and Payables Options setup GL date basis
1523 -- is not taken into consideration while setting the date and period.
1524 -- Following updates will stamp the proper gl date and period on invoice
1525 -- header and lines with regards to the payables options GL date basis
1526 -- setup. For distributions, date the period will be defaulted
1527 -- from the line. Following are setting the accounting date: -
1528 -- Header - InvDetailSvrCmd.defaultHeaderAttributes() using sysdate to
1529 -- to calculate the accounting date instead of invoice date.
1530 -- Line - ApInvoiceLinesAllEOImpl.create() setting accounting date
1531 -- to sysdate.
1532 -- Below update will over write the the accounting date and gl date
1533 -- whatever is set in the java code with the proper values.
1534
1535 BEGIN
1536 l_debug_info := 'Updating gl date and period name at header and line level.';
1537 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1538 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1539 END IF;
1540
1541 SELECT ai.invoice_date, org_id
1542 INTO l_invoice_date, l_org_id
1543 FROM ap_invoices_all ai
1544 WHERE ai.invoice_id = p_invoice_id ;
1545
1546 AP_INVOICES_PKG.Get_gl_date_and_period(
1547 P_Date => l_invoice_date,
1548 P_Period_Name => l_period_name,
1549 P_GL_Date => l_gl_date,
1550 P_Org_Id => l_org_id) ;
1551
1552 UPDATE ap_invoices_all
1553 SET gl_date = l_gl_date
1554 WHERE invoice_id = p_invoice_id ;
1555
1556 -- Bug 7706967 - Start
1557 SELECT nvl(aps.vendor_name, hzp.party_name)
1558 INTO l_vendor_name
1559 FROM ap_suppliers aps, hz_parties hzp, ap_invoices_all ai
1560 WHERE ai.invoice_id = p_invoice_id
1561 AND aps.vendor_id = ai.vendor_id
1562 AND hzp.party_id = aps.party_id
1563 AND ROWNUM = 1;
1564
1565 SELECT apss.vendor_site_code, ai.vendor_id, ai.vendor_site_id
1566 INTO l_vendor_site_code, l_vendor_id, l_vendor_site_id
1567 FROM ap_supplier_sites_all apss, ap_invoices_all ai
1568 WHERE ai.invoice_id = p_invoice_id
1569 AND apss.vendor_site_id = ai.vendor_site_id;
1570
1571 UPDATE ap_invoices_all
1572 SET remit_to_supplier_id = l_vendor_id,
1573 remit_to_supplier_name = l_vendor_name,
1574 remit_to_supplier_site_id = l_vendor_site_id,
1575 remit_to_supplier_site = l_vendor_site_code,
1576 relationship_id = -1
1577 WHERE invoice_id = p_invoice_id;
1578 -- Bug 7706967 - End
1579
1580 UPDATE ap_invoice_lines_all
1581 SET accounting_date = l_gl_date,
1582 period_name = l_period_name
1583 WHERE invoice_id = p_invoice_id ;
1584
1585 EXCEPTION
1586 WHEN NO_DATA_FOUND THEN
1587 l_debug_info := 'No invoice found to update gl date and period.';
1588 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1589 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1590 END IF;
1591 END ;
1592 -- End bug 6859035
1593
1594 l_debug_info := 'Step 1. update invoice amount: invoice_id = ';
1595 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1596 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1597 END IF;
1598
1599 BEGIN
1600
1601 SELECT SUM(DECODE(line_type_lookup_code,'ITEM',NVL(amount, 0) - NVL(included_tax_amount, 0) ,0)) ITEM_SUM,
1602 SUM(DECODE(line_type_lookup_code,'TAX',amount,0)) + SUM(NVL(included_tax_amount, 0)) TAX_SUM,
1603 SUM(DECODE(line_type_lookup_code,'MISCELLANEOUS',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) MISC_SUM, --Bug
1604 SUM(DECODE(line_type_lookup_code,'FREIGHT',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) FREIGHT_SUM,
1605 sum(decode(line_type_lookup_code, 'ITEM', NVL(retained_amount, 0), 0)) RETAINAGE_SUM
1606 INTO l_item_sum, l_tax_sum, l_misc_sum, l_frt_sum, l_retained_sum
1607 FROM ap_invoice_lines_all
1608 WHERE invoice_id = p_invoice_id;
1609
1610 EXCEPTION
1611 WHEN NO_DATA_FOUND THEN
1612 l_debug_info := 'no lines found for the invoice id = '|| p_invoice_id;
1613 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
1614 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name, l_debug_info);
1615 END IF;
1616 END;
1617
1618 -- don't do anything if lines don't exist
1619 if ( l_item_sum <> 0 ) then
1620 update ap_invoices_all
1621 set invoice_amount = l_item_sum + l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,
1622 amount_applicable_to_discount = l_item_sum + l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,
1623 net_of_retainage_flag = DECODE(l_retained_sum, 0, 'N', 'Y'),
1624 APPROVAL_ITERATION = 1 --Needed for workflow process.
1625 where invoice_id = p_invoice_id;
1626 end if;
1627
1628
1629
1630 l_debug_info := 'Creating Pay Schedules ';
1631 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1632 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1633 END IF;
1634
1635 AP_INVOICES_POST_PROCESS_PKG.insert_children (
1636 X_invoice_id => p_invoice_id,
1637 X_Payment_Priority => 99,
1638 X_Hold_count => l_hold_count,
1639 X_Line_count => l_line_count,
1640 X_Line_Total => l_line_total,
1641 X_calling_sequence => l_curr_calling_sequence,
1642 X_Sched_Hold_count => l_Sched_Hold_count);
1643
1644 l_debug_info := 'Call Workflow for Unmatched Invoices ';
1645 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1646 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1647 END IF;
1648
1649 IF (AP_ISP_UTILITIES_PKG.get_po_number_switcher(p_invoice_id) = 'UNMATCHED') THEN
1650 SELECT asu.distribution_set_id
1651 INTO l_dist_set_id
1652 FROM ap_supplier_sites_all asu,
1653 ap_invoices_all ai
1654 WHERE ai.vendor_site_id = asu.vendor_site_id
1655 AND ai.invoice_id = p_invoice_id;
1656 --Create Distributions
1657 IF l_dist_set_id is NOT NULL THEN
1658 create_distributions(p_invoice_id);
1659 END IF;
1660
1661 --Call Workflow API
1662 AP_WORKFLOW_PKG.create_invapp_process(p_invoice_id,
1663 NULL,
1664 l_wfitemkey ) ;
1665
1666 END IF;
1667
1668
1669 EXCEPTION
1670 WHEN OTHERS THEN
1671 IF (SQLCODE <> -20001) THEN
1672 rollback;
1673 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1674 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1675 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1676 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1677 ' P_invoice_id = ' || p_invoice_id
1678 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1679 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1680 END IF;
1681
1682 APP_EXCEPTION.RAISE_EXCEPTION;
1683
1684 END update_invoice_header2;
1685
1686
1687 /*=============================================================================
1688 | public procedure match_invoice_lines
1689 | This procedure recursively matches all po/rcv matched item line
1690 | to the corresponding po/rcv shipment, given invoice id
1691 |
1692 | Description
1693 | When provided with a Invoice Line, based on the
1694 | information provided on the line will match the invoice line
1695 | appropriately to either PO or Receipt or perform Price/Quantity/Line
1696 | correction.
1697 |
1698 | Parameters
1699 | P_invoice_id - invoice id
1700 | P_calling_sequence - For debugging purpose
1701 |
1702 *===========================================================================*/
1703 Procedure Match_Invoice_Lines(
1704 P_Invoice_Id IN NUMBER,
1705 P_Calling_Sequence IN VARCHAR2) IS
1706
1707 CURSOR Invoice_Lines_cur IS
1708 SELECT line_number, quantity_invoiced, amount, po_line_location_id
1709 FROM ap_invoice_lines_all
1710 WHERE invoice_id = P_invoice_id
1711 AND NVL(discarded_flag, 'N' ) <> 'Y'
1712 AND nvl(generate_dists,'Y') <> 'D' --5090119
1713 AND line_type_lookup_code = 'ITEM';
1714
1715 l_invoice_line_number ap_invoice_lines_all.line_number%TYPE;
1716 l_po_line_location_id ap_invoice_lines_all.po_line_location_id%TYPE;
1717 l_quantity ap_invoice_lines_all.quantity_invoiced%TYPE;
1718 l_amount ap_invoice_lines_all.amount%TYPE;
1719 l_billed ap_invoice_lines_all.amount%TYPE;
1720 l_quantity_ordered ap_invoice_lines_all.amount%TYPE;
1721 l_amount_ordered ap_invoice_lines_all.amount%TYPE;
1722 l_matching_basis po_line_locations_all.matching_basis%TYPE;
1723 l_overbill_flag VARCHAR2(2);
1724 l_curr_calling_sequence VARCHAR2(2000);
1725 l_debug_info VARCHAR2(2000);
1726 l_api_name VARCHAR2(50);
1727
1728 BEGIN
1729
1730 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.match_invoice_lines<- ' ||
1731 p_calling_sequence;
1732
1733
1734
1735 l_api_name := 'match_invoice_lines';
1736 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1737 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.match_invoice_lines(+)');
1738 END IF;
1739
1740
1741 OPEN invoice_lines_cur;
1742
1743 LOOP
1744
1745 FETCH invoice_lines_cur INTO
1746 l_invoice_line_number, l_quantity, l_amount, l_po_line_location_id;
1747 EXIT WHEN ( invoice_lines_cur%NOTFOUND );
1748
1749 -- check if it's overbilled
1750 select decode(shipment_type, 'PREPAYMENT', amount_financed,
1751 decode(matching_basis, 'AMOUNT', amount_billed, quantity_billed)),
1752 matching_basis, quantity, amount
1753 into l_billed, l_matching_basis,
1754 l_quantity_ordered, l_amount_ordered
1755 from po_line_locations_all
1756 where line_location_id = l_po_line_location_id;
1757
1758 if ( l_matching_basis = 'AMOUNT' ) then
1759 if ( l_amount + l_billed > l_amount_ordered ) then
1760 l_overbill_flag := 'Y';
1761 else
1762 l_overbill_flag := 'N';
1763 end if;
1764 else -- quantity based
1765 if ( l_quantity + l_billed > l_quantity_ordered ) then
1766 l_overbill_flag := 'Y';
1767 else
1768 l_overbill_flag := 'N';
1769 end if;
1770 end if;
1771
1772 AP_MATCHING_UTILS_PKG.match_invoice_line(
1773 P_Invoice_Id => p_invoice_id,
1774 P_Invoice_Line_Number => l_invoice_line_number,
1775 P_Overbill_Flag => l_overbill_flag,
1776 P_Calling_Sequence => l_curr_calling_sequence);
1777
1778 END LOOP;
1779 CLOSE invoice_lines_cur;
1780
1781 l_debug_info := 'invoice matched. ';
1782 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1783 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1784 END IF;
1785 -- ISP:CodeCleanup Bug 5256954
1786 -- commit;
1787
1788 EXCEPTION
1789 WHEN OTHERS THEN
1790 IF (SQLCODE <> -20001) THEN
1791 rollback;
1792 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1793 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1794 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1795 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1796 ' P_invoice_id = ' || p_invoice_id
1797 || ', invoice_line_number = ' || l_invoice_line_number
1798 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1799 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1800 END IF;
1801
1802 If (Invoice_Lines_Cur%ISOPEN) Then
1803 CLOSE invoice_lines_cur;
1804 End if;
1805
1806 APP_EXCEPTION.RAISE_EXCEPTION;
1807
1808 END match_invoice_lines;
1809
1810
1811
1812
1813
1814 /*=============================================================================
1815 | public procedure get_sec_attr_value
1816 | This procedure retrieves the securing attribute value if there is ONLY
1817 | securing attribute set
1818 |
1819 | Description
1820 |
1821 | Parameters
1822 | P_user_id - user id
1823 | P_attr_code - ICX_SUPPLIER_ORG_ID, etc.
1824 | P_calling_sequence - For debugging purpose
1825 |
1826 *===========================================================================*/
1827 Procedure get_sec_attr_value (P_user_id IN NUMBER,
1828 P_attr_code IN VARCHAR2,
1829 P_attr_value OUT NOCOPY NUMBER,
1830 P_attr_value1 OUT NOCOPY VARCHAR2,
1831 P_party_id OUT NOCOPY NUMBER,
1832 P_Calling_Sequence IN VARCHAR2) IS
1833
1834 l_sec_attr_cnt NUMBER;
1835 l_curr_calling_sequence VARCHAR2(2000);
1836 l_debug_info VARCHAR2(2000);
1837 l_api_name VARCHAR2(50);
1838
1839 BEGIN
1840
1841 l_curr_calling_sequence := 'AP_ISP_UTILITIES_PKG.get_sec_attr_value <- ' ||
1842 p_calling_sequence;
1843
1844 l_api_name := 'get_sec_attr_value';
1845 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1846 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
1847 l_api_name,'AP_ISP_UTILITIES_PKG.get_sec_attr_value(+)');
1848 END IF;
1849
1850 -- we are assuming the attr_code is ICX_SUPPLIER_ORG_ID
1851 -- still pass it to allow reusability
1852 SELECT count(1) attr_value_num
1853 INTO l_sec_attr_cnt
1854 FROM ak_web_user_sec_attr_values awusav
1855 WHERE awusav.web_user_id = p_user_id
1856 AND awusav.attribute_code = p_attr_code
1857 AND awusav.attribute_application_id = 177;
1858
1859 l_debug_info := 'securing attribute count = ' || l_sec_attr_cnt;
1860 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1861 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1862 END IF;
1863
1864 IF ( l_sec_attr_cnt = 1 ) THEN
1865 SELECT nvl(to_char(asav.number_value), nvl(asav.varchar2_value, to_char(asav.date_value)))
1866 INTO p_attr_value
1867 FROM ak_web_user_sec_attr_values asav
1868 WHERE asav.attribute_application_id = 177
1869 AND asav.web_user_id = p_user_id
1870 AND asav.attribute_code = p_attr_code;
1871 END IF;
1872
1873 l_debug_info := 'securing attribute value = '|| p_attr_value;
1874 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1875 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1876 END IF;
1877
1878 IF ( p_attr_value is not null ) THEN
1879 IF ( p_attr_code = 'ICX_SUPPLIER_ORG_ID' ) THEN
1880
1881 --5077334, added party_id
1882 SELECT vendor_name, party_id
1883 INTO p_attr_value1, p_party_id
1884 FROM ap_suppliers
1885 WHERE vendor_id = p_attr_value;
1886 END IF;
1887 END IF;
1888 l_debug_info := 'securing attribute value1 = '|| p_attr_value1;
1889 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1890 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1891 END IF;
1892
1893 EXCEPTION
1894 WHEN OTHERS THEN
1895 IF (SQLCODE <> -20001) THEN
1896 rollback;
1897 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1898 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1899 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1900 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1901 ' P_user_id = ' || p_user_id
1902 || ', p_attr_code = ' || p_attr_code
1903 ||', P_calling_sequence = ' || l_curr_calling_sequence);
1904 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1905 END IF;
1906
1907 APP_EXCEPTION.RAISE_EXCEPTION;
1908
1909 END get_sec_attr_value;
1910
1911 -- Used by Negotiation
1912 PROCEDURE Release_Hold(p_hold_id IN NUMBER) IS
1913 l_debug_info varchar2(100);
1914 BEGIN
1915
1916 l_debug_info := 'update ap_holds_all to release hold';
1917
1918 UPDATE ap_holds_all h
1919 SET release_lookup_code = 'SUP/MGR Release',
1920 release_reason = 'Release of Hold By Supervisor/Manager',
1921 last_updated_by = FND_GLOBAL.user_id,
1922 last_update_date = SYSDATE,
1923 last_update_login = FND_GLOBAL.login_id
1924 WHERE hold_id = p_hold_id
1925 AND release_lookup_code IS NULL
1926 AND EXISTS(SELECT 'It is a releasable hold'
1927 FROM ap_hold_codes ahc
1928 WHERE ahc.hold_lookup_code = h.hold_lookup_code
1929 AND ahc.user_releaseable_flag = 'Y');
1930
1931
1932 EXCEPTION when others then
1933
1934 IF (SQLCODE <> -20001) THEN
1935 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1936 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1937 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'P_hold_id = '|| p_hold_id);
1938 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1939 END IF;
1940
1941 APP_EXCEPTION.RAISE_EXCEPTION;
1942 END Release_Hold;
1943
1944 -- Used by Negotiation
1945 --Bug 5595121 redesigned the api as per mswamina
1946 PROCEDURE update_po_matching_columns (p_line_location_id in number,
1947 p_po_distribution_id in number,
1948 p_quantity_change in number,
1949 p_amount_change in number,
1950 p_ap_uom in varchar2,
1951 p_invoice_id in number,
1952 p_line_number in number,
1953 p_error_code out nocopy varchar2,
1954 p_return_status out nocopy varchar2,
1955 p_calling_sequence in varchar2) is
1956
1957 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
1958 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
1959
1960 TYPE r_dist_info IS RECORD
1961 (po_distribution_id PO_DISTRIBUTIONS.po_distribution_id%TYPE, --Index Column
1962 invoice_distribution_id AP_INVOICE_DISTRIBUTIONS.invoice_distribution_id%TYPE,
1963 rcv_transaction_id RCV_TRANSACTIONS.transaction_id%TYPE,
1964 match_amount AP_INVOICE_DISTRIBUTIONS.amount%TYPE,
1965 match_quantity AP_INVOICE_DISTRIBUTIONS.quantity_invoiced%TYPE,
1966 pa_quantity AP_INVOICE_DISTRIBUTIONS.pa_quantity%TYPE,
1967 update_amount AP_INVOICE_DISTRIBUTIONS.amount%TYPE,
1968 update_quantity AP_INVOICE_DISTRIBUTIONS.quantity_invoiced%TYPE,
1969 update_pa_quantity AP_INVOICE_DISTRIBUTIONS.pa_quantity%TYPE
1970 );
1971 TYPE Dist_Tab_Type IS TABLE OF r_dist_info INDEX BY BINARY_INTEGER;
1972 x_dist_tab DIST_TAB_TYPE;
1973
1974 CURSOR po_dists IS
1975 SELECT po_distribution_id,
1976 invoice_distribution_id,
1977 rcv_transaction_id,
1978 amount,
1979 quantity_invoiced,
1980 pa_quantity
1981 FROM ap_invoice_distributions_all
1982 WHERE invoice_id = p_invoice_id
1983 AND invoice_line_number = p_line_number;
1984
1985 l_po_distribution_id PO_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
1986 l_invoice_distribution_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTION_ID%TYPE;
1987 l_rcv_transaction_id RCV_TRANSACTIONS.TRANSACTION_ID%TYPE;
1988 l_match_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
1989 l_match_quantity AP_INVOICE_LINES.QUANTITY_INVOICED%TYPE;
1990 l_total_quantity_billed AP_INVOICE_LINES.QUANTITY_INVOICED%TYPE;
1991 l_total_amount_billed AP_INVOICE_LINES.AMOUNT%TYPE;
1992 l_rounding_index PO_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
1993 l_sum_prorated_amount AP_INVOICE_LINES.AMOUNT%TYPE;
1994 l_sum_prorated_quantity AP_INVOICE_LINES.QUANTITY_INVOICED%TYPE;
1995 l_max_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
1996 l_unit_meas_lookup_code AP_INVOICE_LINES.UNIT_MEAS_LOOKUP_CODE%TYPE;
1997 l_api_name VARCHAR2(32);
1998 l_msg_data VARCHAR2(4000);
1999 l_return_status VARCHAR2(100);
2000 l_debug_info VARCHAR2(1000);
2001 l_matching_basis VARCHAR2(30);
2002 l_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE;
2003 current_calling_sequence VARCHAR2(1000);
2004 api_call_failed EXCEPTION;
2005
2006 begin
2007
2008 l_api_name := 'update_po_matching_columns';
2009 current_calling_sequence := 'Update_Po_Matching_Columns<-'||p_calling_sequence;
2010
2011 l_sum_prorated_amount := 0;
2012 l_sum_prorated_quantity := 0;
2013 l_max_dist_amount := 0;
2014
2015 l_debug_info := 'Get PO Matched info from the invoice distributions';
2016 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2017 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2018 END IF;
2019
2020
2021 SELECT sum(quantity_invoiced),sum(amount)
2022 INTO l_total_quantity_billed,l_total_amount_billed
2023 FROM ap_invoice_distributions_all
2024 WHERE invoice_id = p_invoice_id
2025 AND invoice_line_number = p_line_number;
2026
2027 SELECT matching_basis
2028 INTO l_matching_basis
2029 FROM po_line_locations_all
2030 WHERE line_location_id = p_line_location_id;
2031
2032 l_debug_info := 'Populate the pl/sql table with proration data';
2033 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2034 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2035 END IF;
2036
2037 OPEN po_dists;
2038
2039 LOOP
2040
2041 FETCH po_dists INTO l_po_distribution_id,
2042 l_invoice_distribution_id,
2043 l_rcv_transaction_id,
2044 l_match_amount,
2045 l_match_quantity,
2046 l_pa_quantity;
2047
2048 EXIT WHEN po_dists%NOTFOUND;
2049
2050 x_dist_tab(l_po_distribution_id).po_distribution_id := l_po_distribution_id;
2051 x_dist_tab(l_po_distribution_id).invoice_distribution_id := l_invoice_distribution_id;
2052 x_dist_tab(l_po_distribution_id).rcv_transaction_id := l_rcv_transaction_id;
2053 x_dist_tab(l_po_distribution_id).match_amount := l_match_amount;
2054 x_dist_tab(l_po_distribution_id).match_quantity := l_match_quantity;
2055 x_dist_tab(l_po_distribution_id).pa_quantity := l_pa_quantity;
2056
2057 x_dist_tab(l_po_distribution_id).update_amount := nvl(x_dist_tab(l_po_distribution_id).match_amount,0) *
2058 p_amount_change/l_total_amount_billed;
2059 l_sum_prorated_amount := l_sum_prorated_amount + x_dist_tab(l_po_distribution_id).update_amount;
2060 x_dist_tab(l_po_distribution_id).update_quantity := nvl(x_dist_tab(l_po_distribution_id).match_quantity ,0) *
2061 p_quantity_change/l_total_quantity_billed;
2062 IF (x_dist_tab(l_po_distribution_id).pa_quantity is not null) THEN
2063 x_dist_tab(l_po_distribution_id).update_pa_quantity := x_dist_tab(l_po_distribution_id).update_quantity;
2064 ELSE
2065 x_dist_tab(l_po_distribution_id).update_pa_quantity := null;
2066 END IF;
2067
2068 l_sum_prorated_quantity := l_sum_prorated_quantity + x_dist_tab(l_po_distribution_id).update_quantity;
2069
2070 IF (l_max_dist_amount < x_dist_tab(l_po_distribution_id).match_amount ) THEN
2071 l_max_dist_amount := x_dist_tab(l_po_distribution_id).match_amount;
2072 l_rounding_index := l_po_distribution_id;
2073 END IF;
2074
2075 END LOOP;
2076
2077 CLOSE po_dists;
2078
2079 l_debug_info := 'Correct proration rounding error';
2080 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2081 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2082 END IF;
2083
2084 IF ((l_sum_prorated_quantity <> p_quantity_change OR l_sum_prorated_amount <> p_amount_change)
2085 and l_rounding_index is not null) THEN
2086
2087 x_dist_tab(l_rounding_index).update_quantity := x_dist_tab(l_rounding_index).update_quantity +
2088 (p_quantity_change - l_sum_prorated_quantity);
2089 IF(x_dist_tab(l_rounding_index).update_pa_quantity IS NOT NULL) THEN
2090 x_dist_tab(l_rounding_index).update_pa_quantity := x_dist_tab(l_rounding_index).update_quantity;
2091 END IF;
2092
2093 x_dist_tab(l_rounding_index).update_amount := x_dist_tab(l_rounding_index).update_amount +
2094 (p_amount_change - l_sum_prorated_amount);
2095
2096 END IF;
2097
2098 l_debug_info := 'Create l_po_ap_line_loc_rec object and populate the data';
2099 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2100 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2101 END IF;
2102 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
2103 p_po_line_location_id => p_line_location_id,
2104 p_uom_code => p_ap_uom,
2105 p_quantity_billed => (-1) * p_quantity_change,
2106 p_amount_billed => (-1) * p_amount_change,
2107 p_quantity_financed => NULL,
2108 p_amount_financed => NULL,
2109 p_quantity_recouped => NULL,
2110 p_amount_recouped => NULL,
2111 p_retainage_withheld_amt => NULL,
2112 p_retainage_released_amt => NULL);
2113
2114
2115 l_debug_info := 'Create l_po_ap_dist_rec object';
2116 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2117 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2118 END IF;
2119
2120 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
2121
2122 FOR i in nvl(x_dist_tab.first,0)..nvl(x_dist_tab.last,0) LOOP
2123
2124 IF (x_dist_tab.exists(i)) THEN
2125
2126 l_po_ap_dist_rec.add_change(p_po_distribution_id => x_dist_tab(i).po_distribution_id,
2127 p_uom_code => p_ap_uom,
2128 p_quantity_billed => (-1) * x_dist_tab(i).update_quantity,
2129 p_amount_billed => (-1) * x_dist_tab(i).update_amount,
2130 p_quantity_financed => NULL,
2131 p_amount_financed => NULL,
2132 p_quantity_recouped => NULL,
2133 p_amount_recouped => NULL,
2134 p_retainage_withheld_amt => NULL,
2135 p_retainage_released_amt => NULL);
2136
2137 UPDATE ap_invoice_distributions_all
2138 SET amount = amount - nvl(x_dist_tab(i).update_amount,0),
2139 quantity_invoiced = quantity_invoiced - nvl(x_dist_tab(i).update_quantity,0),
2140 pa_quantity = pa_quantity - nvl(x_dist_tab(i).update_pa_quantity,0)
2141 WHERE invoice_distribution_id = x_dist_tab(i).invoice_distribution_id;
2142
2143
2144 IF (x_dist_tab(i).rcv_transaction_id IS NOT NULL) THEN
2145
2146 IF (l_matching_basis = 'QUANTITY') THEN
2147
2148 RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
2149 X_rcv_transaction_id => x_dist_tab(i).rcv_transaction_id,
2150 X_quantity_billed => (-1)*x_dist_tab(i).update_quantity,
2151 X_uom_lookup_code => p_ap_uom,
2152 X_amount_billed => (-1)*x_dist_tab(i).update_amount,
2153 X_matching_basis => 'QUANTITY');
2154
2155 ELSE
2156
2157 RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
2158 X_rcv_transaction_id => x_dist_tab(i).rcv_transaction_id,
2159 X_quantity_billed => NULL,
2160 X_uom_lookup_code => p_ap_uom,
2161 X_amount_billed => (-1)*x_dist_tab(i).update_amount,
2162 X_matching_basis => 'AMOUNT');
2163
2164 END IF;
2165
2166 END IF;
2167
2168 END IF;
2169
2170 END LOOP;
2171
2172
2173 l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
2174 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2175 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2176 END IF;
2177
2178 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
2179 P_Api_Version => 1.0,
2180 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
2181 P_Dist_Changes_Rec => l_po_ap_dist_rec,
2182 X_Return_Status => p_return_status,
2183 X_Msg_Data => l_msg_data);
2184
2185 IF (p_return_status <> 'S') THEN
2186 l_debug_info := 'PO API returned unsuccessfully, raise the exception';
2187 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2188 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2189 END IF;
2190 l_debug_info := l_msg_data;
2191 RAISE api_call_failed;
2192 END IF;
2193
2194 EXCEPTION
2195 WHEN OTHERS THEN
2196 IF (SQLCODE <> -20001) THEN
2197 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2198 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2199 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2200 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2201 END IF;
2202
2203 APP_EXCEPTION.RAISE_EXCEPTION;
2204
2205 end update_po_matching_columns;
2206
2207
2208 /* Bug 5407726 ISP Code cleanup XBuild9
2209 This code is not being used
2210 PROCEDURE discard_and_rematch (p_invoice_id in number,
2211 p_line_number in number,
2212 p_error_code out nocopy varchar2,
2213 p_token out nocopy varchar2) is
2214 l_line_rec ap_invoice_lines%rowtype;
2215 l_error_code varchar2(100);
2216 l_token varchar2(100);
2217
2218 begin
2219
2220 select *
2221 into l_line_rec
2222 from ap_invoice_lines_all
2223 where invoice_id = p_invoice_id
2224 and line_number = p_line_number;
2225
2226
2227 if ap_invoice_lines_pkg.Discard_Inv_Line(
2228 P_line_rec => l_line_rec,
2229 P_calling_mode => 'DISCARD',
2230 P_last_updated_by => l_line_rec.last_updated_by,
2231 P_last_update_login => l_line_rec.last_update_login,
2232 P_error_code => l_error_code,
2233 P_token => l_token,
2234 P_calling_sequence => 'NEGOTIATION')<> true then
2235 p_error_code := l_error_code;
2236 p_token := l_token;
2237 return;
2238 end if;
2239
2240
2241 select max(line_number)+1
2242 into l_line_rec.line_number
2243 from ap_invoice_lines_all
2244 where invoice_id = p_invoice_id;
2245
2246 INSERT INTO AP_INVOICE_LINES (
2247 INVOICE_ID,
2248 LINE_NUMBER,
2249 LINE_TYPE_LOOKUP_CODE,
2250 REQUESTER_ID,
2251 DESCRIPTION,
2252 LINE_SOURCE,
2253 ORG_ID,
2254 INVENTORY_ITEM_ID,
2255 ITEM_DESCRIPTION,
2256 SERIAL_NUMBER,
2257 MANUFACTURER,
2258 MODEL_NUMBER,
2259 GENERATE_DISTS,
2260 MATCH_TYPE,
2261 DISTRIBUTION_SET_ID,
2262 ACCOUNT_SEGMENT,
2263 BALANCING_SEGMENT,
2264 COST_CENTER_SEGMENT,
2265 OVERLAY_DIST_CODE_CONCAT,
2266 DEFAULT_DIST_CCID,
2267 PRORATE_ACROSS_ALL_ITEMS,
2268 LINE_GROUP_NUMBER,
2269 ACCOUNTING_DATE,
2270 PERIOD_NAME,
2271 DEFERRED_ACCTG_FLAG,
2272 DEF_ACCTG_START_DATE,
2273 DEF_ACCTG_END_DATE,
2274 DEF_ACCTG_NUMBER_OF_PERIODS,
2275 DEF_ACCTG_PERIOD_TYPE,
2276 SET_OF_BOOKS_ID,
2277 AMOUNT,
2278 BASE_AMOUNT,
2279 ROUNDING_AMT,
2280 QUANTITY_INVOICED,
2281 UNIT_MEAS_LOOKUP_CODE,
2282 UNIT_PRICE,
2283 WFAPPROVAL_STATUS,
2284 DISCARDED_FLAG,
2285 ORIGINAL_AMOUNT,
2286 ORIGINAL_BASE_AMOUNT,
2287 ORIGINAL_ROUNDING_AMT,
2288 CANCELLED_FLAG,
2289 INCOME_TAX_REGION,
2290 TYPE_1099,
2291 STAT_AMOUNT,
2292 PREPAY_INVOICE_ID,
2293 PREPAY_LINE_NUMBER,
2294 INVOICE_INCLUDES_PREPAY_FLAG,
2295 CORRECTED_INV_ID,
2296 CORRECTED_LINE_NUMBER,
2297 PO_HEADER_ID,
2298 PO_LINE_ID,
2299 PO_RELEASE_ID,
2300 PO_LINE_LOCATION_ID,
2301 PO_DISTRIBUTION_ID,
2302 RCV_TRANSACTION_ID,
2303 FINAL_MATCH_FLAG,
2304 ASSETS_TRACKING_FLAG,
2305 ASSET_BOOK_TYPE_CODE,
2306 ASSET_CATEGORY_ID,
2307 PROJECT_ID,
2308 TASK_ID,
2309 EXPENDITURE_TYPE,
2310 EXPENDITURE_ITEM_DATE,
2311 EXPENDITURE_ORGANIZATION_ID,
2312 PA_QUANTITY,
2313 PA_CC_AR_INVOICE_ID,
2314 PA_CC_AR_INVOICE_LINE_NUM,
2315 PA_CC_PROCESSED_CODE,
2316 AWARD_ID,
2317 AWT_GROUP_ID,
2318 REFERENCE_1,
2319 REFERENCE_2,
2320 RECEIPT_VERIFIED_FLAG,
2321 RECEIPT_REQUIRED_FLAG,
2322 RECEIPT_MISSING_FLAG,
2323 JUSTIFICATION,
2324 EXPENSE_GROUP,
2325 START_EXPENSE_DATE,
2326 END_EXPENSE_DATE,
2327 RECEIPT_CURRENCY_CODE,
2328 RECEIPT_CONVERSION_RATE,
2329 RECEIPT_CURRENCY_AMOUNT,
2330 DAILY_AMOUNT,
2331 WEB_PARAMETER_ID,
2332 ADJUSTMENT_REASON,
2333 MERCHANT_DOCUMENT_NUMBER,
2334 MERCHANT_NAME,
2335 MERCHANT_REFERENCE,
2336 MERCHANT_TAX_REG_NUMBER,
2337 MERCHANT_TAXPAYER_ID,
2338 COUNTRY_OF_SUPPLY,
2339 CREDIT_CARD_TRX_ID,
2340 COMPANY_PREPAID_INVOICE_ID,
2341 CC_REVERSAL_FLAG,
2342 ATTRIBUTE_CATEGORY,
2343 ATTRIBUTE1,
2344 ATTRIBUTE2,
2345 ATTRIBUTE3,
2346 ATTRIBUTE4,
2347 ATTRIBUTE5,
2348 ATTRIBUTE6,
2349 ATTRIBUTE7,
2350 ATTRIBUTE8,
2351 ATTRIBUTE9,
2352 ATTRIBUTE10,
2353 ATTRIBUTE11,
2354 ATTRIBUTE12,
2355 ATTRIBUTE13,
2356 ATTRIBUTE14,
2357 ATTRIBUTE15,
2358 CREATION_DATE,
2359 CREATED_BY,
2360 LAST_UPDATED_BY,
2361 LAST_UPDATE_DATE,
2362 LAST_UPDATE_LOGIN,
2363 PROGRAM_APPLICATION_ID,
2364 PROGRAM_ID,
2365 PROGRAM_UPDATE_DATE,
2366 REQUEST_ID,
2367 SHIP_TO_LOCATION_ID,
2368 PRIMARY_INTENDED_USE,
2369 PRODUCT_FISC_CLASSIFICATION,
2370 TRX_BUSINESS_CATEGORY,
2371 PRODUCT_TYPE,
2372 PRODUCT_CATEGORY,
2373 USER_DEFINED_FISC_CLASS,
2374 PURCHASING_CATEGORY_ID)
2375 values( l_line_rec.INVOICE_ID,
2376 l_line_rec.LINE_NUMBER,
2377 l_line_rec.LINE_TYPE_LOOKUP_CODE,
2378 l_line_rec.REQUESTER_ID,
2379 l_line_rec.DESCRIPTION,
2380 l_line_rec.LINE_SOURCE,
2381 l_line_rec.ORG_ID,
2382 l_line_rec.INVENTORY_ITEM_ID,
2383 l_line_rec.ITEM_DESCRIPTION,
2384 l_line_rec.SERIAL_NUMBER,
2385 l_line_rec.MANUFACTURER,
2386 l_line_rec.MODEL_NUMBER,
2387 l_line_rec.GENERATE_DISTS,
2388 l_line_rec.MATCH_TYPE,
2389 l_line_rec.DISTRIBUTION_SET_ID,
2390 l_line_rec.ACCOUNT_SEGMENT,
2391 l_line_rec.BALANCING_SEGMENT,
2392 l_line_rec.COST_CENTER_SEGMENT,
2393 l_line_rec.OVERLAY_DIST_CODE_CONCAT,
2394 l_line_rec.DEFAULT_DIST_CCID,
2395 l_line_rec.PRORATE_ACROSS_ALL_ITEMS,
2396 l_line_rec.LINE_GROUP_NUMBER,
2397 l_line_rec.ACCOUNTING_DATE,
2398 l_line_rec.PERIOD_NAME,
2399 l_line_rec.DEFERRED_ACCTG_FLAG,
2400 l_line_rec.DEF_ACCTG_START_DATE,
2401 l_line_rec.DEF_ACCTG_END_DATE,
2402 l_line_rec.DEF_ACCTG_NUMBER_OF_PERIODS,
2403 l_line_rec.DEF_ACCTG_PERIOD_TYPE,
2404 l_line_rec.SET_OF_BOOKS_ID,
2405 l_line_rec.AMOUNT,
2406 l_line_rec.BASE_AMOUNT,
2407 l_line_rec.ROUNDING_AMT,
2408 l_line_rec.QUANTITY_INVOICED,
2409 l_line_rec.UNIT_MEAS_LOOKUP_CODE,
2410 l_line_rec.UNIT_PRICE,
2411 l_line_rec.WFAPPROVAL_STATUS,
2412 l_line_rec.DISCARDED_FLAG,
2413 l_line_rec.ORIGINAL_AMOUNT,
2414 l_line_rec.ORIGINAL_BASE_AMOUNT,
2415 l_line_rec.ORIGINAL_ROUNDING_AMT,
2416 l_line_rec.CANCELLED_FLAG,
2417 l_line_rec.INCOME_TAX_REGION,
2418 l_line_rec.TYPE_1099,
2419 l_line_rec.STAT_AMOUNT,
2420 l_line_rec.PREPAY_INVOICE_ID,
2421 l_line_rec.PREPAY_LINE_NUMBER,
2422 l_line_rec.INVOICE_INCLUDES_PREPAY_FLAG,
2423 l_line_rec.CORRECTED_INV_ID,
2424 l_line_rec.CORRECTED_LINE_NUMBER,
2425 l_line_rec.PO_HEADER_ID,
2426 l_line_rec.PO_LINE_ID,
2427 l_line_rec.PO_RELEASE_ID,
2428 l_line_rec.PO_LINE_LOCATION_ID,
2429 l_line_rec.PO_DISTRIBUTION_ID,
2430 l_line_rec.RCV_TRANSACTION_ID,
2431 l_line_rec.FINAL_MATCH_FLAG,
2432 l_line_rec.ASSETS_TRACKING_FLAG,
2433 l_line_rec.ASSET_BOOK_TYPE_CODE,
2434 l_line_rec.ASSET_CATEGORY_ID,
2435 l_line_rec.PROJECT_ID,
2436 l_line_rec.TASK_ID,
2437 l_line_rec.EXPENDITURE_TYPE,
2438 l_line_rec.EXPENDITURE_ITEM_DATE,
2439 l_line_rec.EXPENDITURE_ORGANIZATION_ID,
2440 l_line_rec.PA_QUANTITY,
2441 l_line_rec.PA_CC_AR_INVOICE_ID,
2442 l_line_rec.PA_CC_AR_INVOICE_LINE_NUM,
2443 l_line_rec.PA_CC_PROCESSED_CODE,
2444 l_line_rec.AWARD_ID,
2445 l_line_rec.AWT_GROUP_ID,
2446 l_line_rec.REFERENCE_1,
2447 l_line_rec.REFERENCE_2,
2448 l_line_rec.RECEIPT_VERIFIED_FLAG,
2449 l_line_rec.RECEIPT_REQUIRED_FLAG,
2450 l_line_rec.RECEIPT_MISSING_FLAG,
2451 l_line_rec.JUSTIFICATION,
2452 l_line_rec.EXPENSE_GROUP,
2453 l_line_rec.START_EXPENSE_DATE,
2454 l_line_rec.END_EXPENSE_DATE,
2455 l_line_rec.RECEIPT_CURRENCY_CODE,
2456 l_line_rec.RECEIPT_CONVERSION_RATE,
2457 l_line_rec.RECEIPT_CURRENCY_AMOUNT,
2458 l_line_rec.DAILY_AMOUNT,
2459 l_line_rec.WEB_PARAMETER_ID,
2460 l_line_rec.ADJUSTMENT_REASON,
2461 l_line_rec.MERCHANT_DOCUMENT_NUMBER,
2462 l_line_rec.MERCHANT_NAME,
2463 l_line_rec.MERCHANT_REFERENCE,
2464 l_line_rec.MERCHANT_TAX_REG_NUMBER,
2465 l_line_rec.MERCHANT_TAXPAYER_ID,
2466 l_line_rec.COUNTRY_OF_SUPPLY,
2467 l_line_rec.CREDIT_CARD_TRX_ID,
2468 l_line_rec.COMPANY_PREPAID_INVOICE_ID,
2469 l_line_rec.CC_REVERSAL_FLAG,
2470 l_line_rec.ATTRIBUTE_CATEGORY,
2471 l_line_rec.ATTRIBUTE1,
2472 l_line_rec.ATTRIBUTE2,
2473 l_line_rec.ATTRIBUTE3,
2474 l_line_rec.ATTRIBUTE4,
2475 l_line_rec.ATTRIBUTE5,
2476 l_line_rec.ATTRIBUTE6,
2477 l_line_rec.ATTRIBUTE7,
2478 l_line_rec.ATTRIBUTE8,
2479 l_line_rec.ATTRIBUTE9,
2480 l_line_rec.ATTRIBUTE10,
2481 l_line_rec.ATTRIBUTE11,
2482 l_line_rec.ATTRIBUTE12,
2483 l_line_rec.ATTRIBUTE13,
2484 l_line_rec.ATTRIBUTE14,
2485 l_line_rec.ATTRIBUTE15,
2486 l_line_rec.CREATION_DATE,
2487 l_line_rec.CREATED_BY,
2488 l_line_rec.LAST_UPDATED_BY,
2489 l_line_rec.LAST_UPDATE_DATE,
2490 l_line_rec.LAST_UPDATE_LOGIN,
2491 l_line_rec.PROGRAM_APPLICATION_ID,
2492 l_line_rec.PROGRAM_ID,
2493 l_line_rec.PROGRAM_UPDATE_DATE,
2494 l_line_rec.REQUEST_ID,
2495 l_line_rec.SHIP_TO_LOCATION_ID,
2496 l_line_rec.PRIMARY_INTENDED_USE,
2497 l_line_rec.PRODUCT_FISC_CLASSIFICATION,
2498 l_line_rec.TRX_BUSINESS_CATEGORY,
2499 l_line_rec.PRODUCT_TYPE,
2500 l_line_rec.PRODUCT_CATEGORY,
2501 l_line_rec.USER_DEFINED_FISC_CLASS,
2502 l_line_rec.PURCHASING_CATEGORY_ID);
2503
2504
2505
2506 ap_matching_utils_pkg.Match_Invoice_Line(
2507 P_Invoice_Id => p_invoice_id,
2508 P_Invoice_Line_Number => l_line_rec.line_number,
2509 P_Overbill_Flag => 'N',
2510 P_Calling_Sequence => 'AP_ISP_UTILITIES_PKG.DISCARD_AND_REMATCH');
2511
2512
2513 end discard_and_rematch;
2514
2515 */
2516
2517 --Bug 5500186 --For Non-Po invoices, user should provide the Customer
2518 -- Contact Info.
2519 PROCEDURE populate_requester(p_first_name IN VARCHAR2,
2520 p_last_name IN VARCHAR2,
2521 p_email_address IN VARCHAR2,
2522 p_requester_id IN OUT NOCOPY NUMBER) IS
2523
2524 requester_id NUMBER;
2525 Type requestercur is REF CURSOR;
2526 requester_cur requestercur;
2527 TYPE req_id_list_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
2528
2529 req_id_list req_id_list_type;
2530 sql_stmt varchar2(4000);
2531
2532 BEGIN
2533
2534 sql_stmt := 'SELECT person_id '||
2535 'FROM per_all_people_f '||
2536 'WHERE NVL(effective_end_date, SYSDATE) >= SYSDATE ';
2537
2538 IF ( p_first_name is NOT NULL) then
2539 sql_stmt := sql_stmt ||' AND first_name =' || '''' || p_first_name || '''' ;
2540 END IF;
2541 IF ( p_last_name is NOT NULL) then
2542 sql_stmt := sql_stmt ||' AND last_name = ' || '''' || p_last_name || '''';
2543 END IF;
2544 IF ( p_email_address is NOT NULL) then
2545 sql_stmt := sql_stmt ||' AND email_address = ' || '''' || p_email_address || '''' ;
2546 END IF;
2547
2548 OPEN requester_cur for sql_stmt;
2549
2550 FETCH requester_cur BULK COLLECT INTO req_id_list;
2551
2552 CLOSE requester_cur;
2553
2554 IF req_id_list.COUNT = 0 THEN
2555 requester_id := NULL;
2556 ELSIF req_id_list.COUNT = 1 THEN
2557 requester_id := req_id_list(1);
2558 ELSIF req_id_list.COUNT > 1 THEN
2559 requester_id := NULL;
2560 END IF;
2561
2562 p_requester_id := requester_id;
2563
2564 END;
2565
2566 -- Bug 5659917 PO Number shows incorrect info on the Invoice
2567 -- Search Page
2568 FUNCTION get_po_number_switcher(p_invoice_id IN NUMBER)
2569 RETURN VARCHAR2 IS
2570
2571 l_po_number VARCHAR2(20);
2572 l_count NUMBER;
2573 l_po_count NUMBER;
2574 l_release_count NUMBER;
2575
2576 BEGIN
2577 --
2578 SELECT COUNT(*)
2579 INTO l_count
2580 FROM ap_invoice_lines_all
2581 WHERE po_header_id IS NOT NULL
2582 AND invoice_id = p_invoice_id;
2583
2584 IF l_count = 0 THEN
2585 RETURN 'UNMATCHED';
2586 ELSE
2587 SELECT count(*)
2588 INTO l_po_count
2589 FROM po_headers_all
2590 WHERE po_header_id IN (SELECT po_header_id
2591 FROM ap_invoice_lines_All
2592 WHERE invoice_id = p_invoice_id);
2593
2594 IF l_po_count = 1 THEN
2595 --
2596 SELECT COUNT(*)
2597 INTO l_release_count
2598 FROM po_releases_all pr
2599 WHERE po_header_id IN (SELECT po_header_id
2600 FROM ap_invoice_lines_All
2601 WHERE invoice_id = p_invoice_id);
2602 --
2603 IF (l_release_count = 0 OR l_release_count = 1) THEN
2604 RETURN 'SINGLE';
2605 ELSE
2606 RETURN 'MULTIPLE';
2607 END IF;
2608 --
2609 ELSE
2610 RETURN 'MULTIPLE';
2611 END IF;
2612 END IF;
2613
2614 END;
2615
2616 FUNCTION get_po_number(p_invoice_id IN NUMBER)
2617 RETURN VARCHAR2 IS
2618
2619 l_po_number VARCHAR2(40);
2620 l_po_header_id NUMBER;
2621 l_po_count NUMBER;
2622 l_release_count NUMBER;
2623 l_po_switcher VARCHAR2(10);
2624 l_release_num NUMBER;
2625
2626 BEGIN
2627 --
2628 l_po_switcher := get_po_number_switcher(p_invoice_id);
2629
2630 IF l_po_switcher = 'SINGLE' THEN
2631 --
2632 SELECT SEGMENT1,
2633 po_header_id
2634 INTO l_po_number,
2635 l_po_header_id
2636 FROM po_headers_all POH
2637 WHERE po_header_id IN ( SELECT po_header_id
2638 FROM ap_invoice_lines_All
2639 WHERE invoice_id = p_invoice_id);
2640
2641 SELECT COUNT(*)
2642 INTO l_release_count
2643 FROM po_releases_all pr
2644 WHERE po_header_id = l_po_header_id;
2645 --
2646 IF l_release_count = 1 THEN
2647 --
2648 SELECT release_num
2649 INTO l_release_num
2650 FROM po_releases_all
2651 WHERE po_header_id = l_po_header_id;
2652
2653 l_po_number := l_po_number||'-'|| l_release_num;
2654 END IF;
2655 --
2656 END IF;
2657
2658
2659 RETURN l_po_number;
2660 --
2661 END;
2662
2663 --Bug 5704381
2664 FUNCTION get_po_header_id(p_invoice_id IN NUMBER)
2665 RETURN NUMBER IS
2666
2667 l_po_header_id NUMBER;
2668 l_count NUMBER;
2669 l_po_count NUMBER;
2670
2671 BEGIN
2672 --
2673 SELECT COUNT(*)
2674 INTO l_count
2675 FROM ap_invoice_lines_all
2676 WHERE po_header_id IS NOT NULL
2677 AND invoice_id = p_invoice_id;
2678
2679 IF l_count <> 0 THEN
2680
2681 SELECT count(*)
2682 INTO l_po_count
2683 FROM po_headers_all
2684 WHERE po_header_id IN (SELECT po_header_id
2685 FROM ap_invoice_lines_All
2686 WHERE invoice_id = p_invoice_id);
2687
2688 IF l_po_count = 1 THEN
2689 SELECT po_header_id
2690 INTO l_po_header_id
2691 FROM po_headers_all POH
2692 WHERE po_header_id IN ( SELECT po_header_id
2693 FROM ap_invoice_lines_All
2694 WHERE invoice_id = p_invoice_id);
2695 END IF;
2696 END IF;
2697 --
2698 RETURN l_po_header_id;
2699 --
2700 END;
2701
2702 FUNCTION get_po_release(p_invoice_id IN NUMBER,
2703 p_ret_value IN VARCHAR2)
2704 RETURN NUMBER IS
2705
2706 l_po_header_id NUMBER;
2707 l_release_count NUMBER;
2708 l_po_release_id NUMBER;
2709 l_release_num NUMBER;
2710
2711
2712
2713 BEGIN
2714 --
2715 l_po_header_id := get_po_header_id(p_invoice_id);
2716 --
2717 IF l_po_header_id is NOT NULL THEN
2718 SELECT COUNT(*)
2719 INTO l_release_count
2720 FROM po_releases_all pr
2721 WHERE po_header_id IN (SELECT po_header_id
2722 FROM ap_invoice_lines_All
2723 WHERE invoice_id = p_invoice_id);
2724 --
2725 IF l_release_count = 1 THEN
2726 --
2727 SELECT po_release_id,
2728 release_num
2729 INTO l_po_release_id,
2730 l_release_num
2731 FROM po_releases_all
2732 WHERE po_header_id = l_po_header_id;
2733 END IF;
2734 --
2735 END IF;
2736 --
2737 IF p_ret_value ='NUM' THEN
2738 RETURN l_release_num;
2739 ELSE
2740 RETURN l_po_release_id;
2741 END IF;
2742 --
2743 END;
2744
2745 END AP_ISP_UTILITIES_PKG;