[Home] [Help]
PACKAGE BODY: APPS.AP_IMPORT_UTILITIES_PKG
Source
1 PACKAGE BODY AP_IMPORT_UTILITIES_PKG AS
2 /* $Header: apiimutb.pls 120.67.12010000.12 2008/11/20 14:15:33 dcshanmu ship $ */
3
4 -- Bug 3929697
5 -- Declared two global variables for getting the value of
6 -- distribution line number
7
8 lg_invoice_id NUMBER :=0;
9 lg_dist_line_num NUMBER ;
10
11 --==============================================================
12 -- copy attachment association
13 --
14 --==============================================================
15 FUNCTION copy_attachments(p_from_invoice_id IN NUMBER,
16 p_to_invoice_id IN NUMBER)
17 RETURN NUMBER IS
18 l_attachments_count NUMBER := 0;
19 debug_info VARCHAR2(500);
20 BEGIN
21 select count(1)
22 into l_attachments_count
23 from fnd_attached_documents
24 where entity_name = 'AP_INVOICES_INTERFACE'
25 and pk1_value = p_from_invoice_id;
26
27 -- we only need to copy attachments if there is one
28 if ( l_attachments_count > 0 )
29 then
30 fnd_attached_documents2_pkg.copy_attachments(
31 x_from_entity_name => 'AP_INVOICES_INTERFACE',
32 x_from_pk1_value => p_from_invoice_id,
33 x_to_entity_name => 'AP_INVOICES',
34 x_to_pk1_value => p_to_invoice_id);
35 end if;
36
37 return l_attachments_count;
38 EXCEPTION
39
40 WHEN OTHERS then
41
42 IF (SQLCODE < 0) then
43 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
44 AP_IMPORT_UTILITIES_PKG.Print(
45 AP_IMPORT_INVOICES_PKG.g_debug_switch, SQLERRM);
46 END IF;
47 END IF;
48 RETURN 0;
49 END copy_attachments;
50
51
52 /*======================================================================
53 Function: Check_Control_Table
54 This function is called at the beginning of the Import Program to handle
55 concurrency control. It prevents the program from running if another
56 process is running for the same set of parameters.
57 ========================================================================*/
58 FUNCTION Check_control_table(
59 p_source IN VARCHAR2,
60 p_group_id IN VARCHAR2,
61 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
62
63 -- Bug 4145391. Modified the select for the cursor to improve performance.
64 -- Removed the p_group_id where clause and added it to the cursor
65 -- import_requests_group
66 CURSOR import_requests IS
67 SELECT request_id,
68 group_id
69 FROM ap_interface_controls
70 WHERE source = p_source
71 ORDER BY request_id DESC;
72
73 CURSOR import_requests_group IS
74 SELECT request_id,
75 group_id
76 FROM ap_interface_controls
77 WHERE source = p_source
78 AND group_id = p_group_id
79 ORDER BY request_id DESC;
80
81 check_control_failure EXCEPTION;
82 current_calling_sequence VARCHAR2(2000);
83 debug_info VARCHAR2(500);
84 l_phase VARCHAR2(30);
85 l_status VARCHAR2(30);
86 l_dev_phase VARCHAR2(30);
87 l_dev_status VARCHAR2(30);
88 l_message VARCHAR2(240);
89 l_new_record VARCHAR2(1) := 'Y';
90 l_previous_request_id NUMBER;
91 l_group_id VARCHAR2(80);
92
93 BEGIN
94
95 -- Update the calling sequence
96
97 current_calling_sequence :=
98 'AP_Import_Utilities_Pkg.Check_control_table<-'||P_calling_sequence;
99
100 -----------------------------------------------------------------------
101 -- Step 1,
102 -- Lock the control table, in case some other concurrent process try to
103 -- insert a idential record
104 -----------------------------------------------------------------------
105
106 debug_info := '(Check_control_table 1) Lock the control table ';
107 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
108 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
109 END IF;
110
111 LOCK TABLE AP_INTERFACE_CONTROLS IN EXCLUSIVE MODE;
112
113 debug_info := '(Check_control_table) Open import_requests cursor';
114
115 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
116 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
117 END IF;
118
119 -- Bug 4145391. To improve the performance of the import program coding two
120 -- different cursors based on the parameter p_group_id
121 IF (p_group_id IS NULL) THEN
122 OPEN import_requests;
123 ELSE
124 OPEN import_requests_group;
125 END IF;
126
127 LOOP
128 -------------------------------------------------------------------------
129 -- Step 2, Fetch l_previous_request_id from ap_interface_controls with
130 -- the same source and group_id (optional). If group_id is null,
131 -- all requests from the source will be fetched
132 -------------------------------------------------------------------------
133
134 debug_info := '(Check_control_table 2) Fetch import_requests';
135 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
136 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
137 END IF;
138
139 -- Bug 4145391
140 IF (p_group_id IS NULL) THEN
141 FETCH import_requests INTO l_previous_request_id,
142 l_group_id;
143 EXIT WHEN import_requests%NOTFOUND OR
144 import_requests%NOTFOUND IS NULL;
145 ELSE
146 FETCH import_requests_group INTO l_previous_request_id,
147 l_group_id;
148 EXIT WHEN import_requests_group%NOTFOUND OR
149 import_requests_group%NOTFOUND IS NULL;
150 END IF;
151
152
153 -- It won't be new record if program is up to this point
154 l_new_record := 'N';
155
156 -----------------------------------------------------------------------
157 -- Step 3,
158 -- Check status for the concurrent program from the request_id
159 -----------------------------------------------------------------------
160
161 debug_info := '(Check_control_table 3) Check concurrent program status';
162 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
163 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
164 debug_info||' previous req id ='||l_previous_request_id);
165 END IF;
166
167 IF (FND_CONCURRENT.GET_REQUEST_STATUS(
168 request_id =>l_previous_request_id,
169 appl_shortname =>'',
170 program =>'',
171 phase =>l_phase,
172 status =>l_status,
173 dev_phase =>l_dev_phase,
174 dev_status =>l_dev_status,
175 message =>l_message) <> TRUE) THEN
176 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
177 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
178 'FUNCTION GET_REQUEST_STATUS ERROR, Reason: '||l_message);
179 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
180 'FND_CONCURRENT.GET_REQUEST_STATUS<-'||current_calling_sequence);
181 END IF;
182 RAISE Check_control_failure;
183
184 END IF;
185
186 -- show output values (only if debug_switch = 'Y')
187
188 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
189 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
190 '------------------> l_dev_phase = '||l_dev_phase
191 ||' l_dev_status = '||l_dev_status
192 ||' l_previous_request_id = '||to_char(l_previous_request_id));
193 END IF;
194
195 -------------------------------------------------------------------------
196 -- Step 4.1
197 -- Reject if any process for the source and group_id (optional) is
198 -- currentlt running
199 -------------------------------------------------------------------------
200 IF (l_dev_phase in ('PENDING','RUNNING','INACTIVE')) then
201
202 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
203 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
204 'ERROR: There are existing import processes currently in the status '
205 ||l_dev_phase||' for this source ('||p_source||') and group_id ('
206 ||p_group_id
207 ||') , please check your concurrent process requests');
208 END IF;
209 RAISE Check_control_failure;
210
211 ELSIF (l_dev_phase = 'COMPLETE') THEN
212
213 ---------------------------------------------------------------------
214 -- Step 4.2
215 -- Delete the previous record in ap_interface_controls if the status
216 -- is 'COMPLETE'
217 ---------------------------------------------------------------------
218 debug_info := '(Check_control_table 4.2) Delete the previous record '||
219 'in ap_interface_controls';
220 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
221 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
222 END IF;
223
224 -- Bug 4145391
225 IF (p_group_id IS NULL) THEN
226 DELETE FROM AP_INTERFACE_CONTROLS
227 WHERE source = p_source
228 AND request_id = l_previous_request_id;
229 ELSE
230 DELETE FROM AP_INTERFACE_CONTROLS
231 WHERE source = p_source
232 AND group_id = p_group_id
233 AND request_id = l_previous_request_id;
234 END IF;
235
236 END IF; -- for step 4
237
238 END LOOP;
239
240 debug_info := '(Check_control_table) CLOSE import_requests cursor';
241 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
242 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
243 END IF;
244
245 -- Bug 4145391
246 IF (p_group_id IS NULL) THEN
247 CLOSE import_requests;
248 ELSE
249 CLOSE import_requests_group;
250 END IF;
251
252 -----------------------------------------
253 -- Step 5
254 -- Insert record into control table
255 -----------------------------------------
256
257 debug_info := '(Check_control_table 5) Insert record into control table';
258 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
259 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
260 END IF;
261
262 INSERT INTO AP_INTERFACE_CONTROLS(
263 source,
264 group_id,
265 request_id)
266 VALUES (p_source,
267 p_group_id,
268 AP_IMPORT_INVOICES_PKG.g_conc_request_id);
269
270 ----------------------------------------------------------------------------
271 -- Step 6
272 -- Commit the change to database, it will also release the lock for the table
273 ----------------------------------------------------------------------------
274
275 debug_info := '(Check_control_table 6) Commit';
276 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
277 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
278 END IF;
279
280 COMMIT;
281
282 RETURN(TRUE);
283
284 EXCEPTION
285
286 WHEN OTHERS then
287 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
288 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
289 END IF;
290
291 IF (SQLCODE < 0) then
292 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
293 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
294 END IF;
295 END IF;
296
297 -- Bug 4145391
298 IF (p_group_id IS NULL) THEN
299 CLOSE import_requests;
300 ELSE
301 CLOSE import_requests_group;
302 END IF;
303
304 RETURN (FALSE);
305
306 END Check_control_table;
307
308
309 /*======================================================================
310 Procedure: Print
311 Procedure to output debug messages in strings no longer than 80 chars.
312 ========================================================================*/
313 PROCEDURE Print (
314 P_debug IN VARCHAR2,
315 P_string IN VARCHAR2)
316 IS
317 stemp VARCHAR2(80);
318 nlength NUMBER := 1;
319 BEGIN
320
321 IF (P_Debug = 'Y') THEN
322 WHILE(length(P_string) >= nlength)
323 LOOP
324
325 stemp := substrb(P_string, nlength, 80);
326 fnd_file.put_line(FND_FILE.LOG, stemp);
327 nlength := (nlength + 80);
328
329 END LOOP;
330 END IF;
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 IF (SQLCODE <> -20001) THEN
335 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
336 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
337 END IF;
338 APP_EXCEPTION.RAISE_EXCEPTION;
339
340 END Print;
341
342 /*======================================================================
343 Function: Insert_Rejections
344 This function is called whenever the process needs to insert a
345 rejection. If the process is called in the context of the 'XML
346 Gateway' as source, the supplier must be notifies and the rejection
347 code is one of a fixed list of rejection codes, then it inserts all
348 tokens into the interface rejections table, else it ignores token
349 parameters.
350 ========================================================================*/
351 FUNCTION insert_rejections (
352 p_parent_table IN VARCHAR2,
353 p_parent_id IN NUMBER,
354 p_reject_code IN VARCHAR2,
355 p_last_updated_by IN NUMBER,
356 p_last_update_login IN NUMBER,
357 p_calling_sequence IN VARCHAR2,
358 p_notify_vendor_flag IN VARCHAR2 DEFAULT NULL,
359 p_token_name1 IN VARCHAR2 DEFAULT NULL,
360 p_token_value1 IN VARCHAR2 DEFAULT NULL,
361 p_token_name2 IN VARCHAR2 DEFAULT NULL,
362 p_token_value2 IN VARCHAR2 DEFAULT NULL,
363 p_token_name3 IN VARCHAR2 DEFAULT NULL,
364 p_token_value3 IN VARCHAR2 DEFAULT NULL,
365 p_token_name4 IN VARCHAR2 DEFAULT NULL,
366 p_token_value4 IN VARCHAR2 DEFAULT NULL,
367 p_token_name5 IN VARCHAR2 DEFAULT NULL,
368 p_token_value5 IN VARCHAR2 DEFAULT NULL,
369 p_token_name6 IN VARCHAR2 DEFAULT NULL,
370 p_token_value6 IN VARCHAR2 DEFAULT NULL,
371 p_token_name7 IN VARCHAR2 DEFAULT NULL,
372 p_token_value7 IN VARCHAR2 DEFAULT NULL,
373 p_token_name8 IN VARCHAR2 DEFAULT NULL,
374 p_token_value8 IN VARCHAR2 DEFAULT NULL,
375 p_token_name9 IN VARCHAR2 DEFAULT NULL,
376 p_token_value9 IN VARCHAR2 DEFAULT NULL,
377 p_token_name10 IN VARCHAR2 DEFAULT NULL,
378 p_token_value10 IN VARCHAR2 DEFAULT NULL)
379 RETURN BOOLEAN IS
380
381 current_calling_sequence VARCHAR2(2000);
382 debug_info VARCHAR2(500);
383
384 BEGIN
385 -- Update the calling sequence
386
387 current_calling_sequence := 'AP_Import_Utilities_Pkg.Insert_rejections<-'
388 ||P_calling_sequence;
389
390 --------------------------------------------------------------------------
391 -- Step1
392 -- Insert into AP_INTERFACE_REJECTIONS
393 --------------------------------------------------------------------------
394
395 debug_info := '(Insert Rejections 1) Insert into AP_INTERFACE_REJECTIONS, '||
396 'REJECT CODE:'||p_reject_code;
397
398 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
399 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
400 END IF;
401
402 IF(AP_IMPORT_INVOICES_PKG.g_source = 'XML GATEWAY'
403 AND NVL(p_notify_vendor_flag,'Y') = 'Y'
404 AND p_reject_code in ('CAN MATCH TO ONLY 1 LINE',
405 'DUPLICATE INVOICE NUMBER',
406 'DUPLICATE LINE NUMBER',
407 'INCONSISTENT CURR',
408 'INCONSISTENT PO LINE INFO',
409 'INCONSISTENT PO SUPPLIER',
410 'INVALID INVOICE AMOUNT',
411 'INVALID ITEM',
412 'INVALID PO INFO',
413 'INVALID PO NUM',
414 'INVALID PO RELEASE INFO',
415 'INVALID PO RELEASE NUM',
416 'INVALID PO SHIPMENT NUM',
417 'NEGATIVE QUANTITY BILLED', --Bug 5134622
418 'INVALID PRICE/QUANTITY',
419 'INVALID QUANTITY',
420 'INVALID UNIT PRICE',
421 'NO PO LINE NUM',
422 'RELEASE MISSING') ) THEN
423 -------------------------------------------------
424 -- Step 2
425 -- Set notify_vendor_flag for XML GATEWAY source
426 -------------------------------------------------
427
428 debug_info := '(Insert Rejections 2) '||
429 'Set notify_vendor_flag for XML GATEWAY';
430 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
431 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
432 END IF;
433
434 INSERT INTO AP_INTERFACE_REJECTIONS(
435 parent_table,
436 parent_id,
437 reject_lookup_code,
438 last_updated_by,
439 last_update_date,
440 last_update_login,
441 created_by,
442 creation_date,
443 notify_vendor_flag,
444 token_name1,
445 token_value1,
446 token_name2,
447 token_value2,
448 token_name3,
449 token_value3,
450 token_name4,
451 token_value4,
452 token_name5,
453 token_value5,
454 token_name6,
455 token_value6,
456 token_name7,
457 token_value7,
458 token_name8,
459 token_value8,
460 token_name9,
461 token_value9,
462 token_name10,
463 token_value10)
464 VALUES (
465 p_parent_table,
466 p_parent_id,
467 p_reject_code,
468 p_last_updated_by,
469 SYSDATE,
470 p_last_update_login,
471 p_last_updated_by,
472 SYSDATE,
473 'Y', -- p_notify_vendor_flag,
474 p_token_name1,
475 p_token_value1,
476 p_token_name2,
477 p_token_value2,
478 p_token_name3,
479 p_token_value3,
480 p_token_name4,
481 p_token_value4,
482 p_token_name5,
483 p_token_value5,
484 p_token_name6,
485 p_token_value6,
486 p_token_name7,
487 p_token_value7,
488 p_token_name8,
489 p_token_value8,
490 p_token_name9,
491 p_token_value9,
492 p_token_name10,
493 p_token_value10);
494 ELSE
495 INSERT INTO AP_INTERFACE_REJECTIONS(
496 parent_table,
497 parent_id,
498 reject_lookup_code,
499 last_updated_by,
500 last_update_date,
501 last_update_login,
502 created_by,
503 creation_date)
504 VALUES (
505 p_parent_table,
506 p_parent_id,
507 p_reject_code,
508 p_last_updated_by,
509 SYSDATE,
510 p_last_update_login,
511 p_last_updated_by,
512 SYSDATE);
513
514 END IF; -- if XML GATEWAY supplier rejection
515
516 RETURN(TRUE);
517
518 EXCEPTION
519 WHEN OTHERS then
520 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
521 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
522 END IF;
523
524 IF (SQLCODE < 0) then
525 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
526 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
527 END IF;
528 END IF;
529
530 RETURN (FALSE);
531
532 END insert_rejections;
533
534
535 FUNCTION get_overbill_for_shipment (
536 p_po_shipment_id IN NUMBER,
537 p_quantity_invoiced IN NUMBER,
538 p_amount_invoiced IN NUMBER,
539 p_overbilled OUT NOCOPY VARCHAR2,
540 p_quantity_outstanding OUT NOCOPY NUMBER,
541 p_quantity_ordered OUT NOCOPY NUMBER,
542 p_qty_already_billed OUT NOCOPY NUMBER,
543 p_amount_outstanding OUT NOCOPY NUMBER,
544 p_amount_ordered OUT NOCOPY NUMBER,
545 p_amt_already_billed OUT NOCOPY NUMBER,
546 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
547
548 current_calling_sequence VARCHAR2(2000);
549 debug_info VARCHAR2(500);
550 l_matching_basis PO_LINE_LOCATIONS_ALL.MATCHING_BASIS%TYPE;
551
552 BEGIN
553 -- Update the calling sequence
554
555 current_calling_sequence :=
556 'AP_Import_Utilities_Pkg.get_overbill_for_shipment<-'
557 ||P_calling_sequence;
558
559 --------------------------------------------------------------------------
560 -- Step 1
561 -- Get quantity_outstanding
562 --------------------------------------------------------------------------
563
564 debug_info := '(Get Overbill for Shipment 1) Get quantity_outstanding';
565 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
566 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
567 END IF;
568
569 --Contract Payments: modified the SELECT clause
570 SELECT decode(pod.distribution_type,'PREPAYMENT',
571 sum(NVL(pod.quantity_ordered,0) - NVL(pod.quantity_financed,0)
572 - NVL(pod.quantity_cancelled,0)),
573 sum(NVL(pod.quantity_ordered,0) - NVL(pod.quantity_billed,0)
574 - NVL(pod.quantity_cancelled,0))
575 ),
576 sum(NVL(pod.quantity_ordered,0) - NVL(pod.quantity_cancelled,0)),
577 decode(pod.distribution_type,'PREPAYMENT',
578 sum(NVL(pod.quantity_financed,0)),
579 sum(NVL(pod.quantity_billed,0))
580 ),
581 decode(pod.distribution_type,'PREPAYMENT',
582 sum(NVL(pod.amount_ordered,0) - NVL(pod.amount_financed,0)
583 - NVL(pod.amount_cancelled,0)),
584 sum(NVL(pod.amount_ordered,0) - NVL(pod.amount_billed,0)
585 - NVL(pod.amount_cancelled,0))
586 ),
587 sum(NVL(pod.amount_ordered,0) - NVL(pod.amount_cancelled,0)),
588 decode(pod.distribution_type,'PREPAYMENT',
589 sum(NVL(pod.amount_financed,0)),
590 sum(NVL(pod.amount_billed,0))
591 ),
592 pll.matching_basis
593 INTO p_quantity_outstanding,
594 p_quantity_ordered,
595 p_qty_already_billed,
596 p_amount_outstanding,
597 p_amount_ordered,
598 p_amt_already_billed,
599 l_matching_basis
600 FROM po_distributions_ap_v pod,
601 po_line_locations pll
602 WHERE pod.line_location_id = p_po_shipment_id
603 AND pll.line_location_id = pod.line_location_id
604 GROUP BY pod.distribution_type,pll.matching_basis ;
605
606
607 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
608 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,'------------------>
609 p_quantity_outstanding = '||to_char(p_quantity_outstanding)
610 ||' p_quantity_ordered = '||to_char(p_quantity_ordered)
611 ||' p_qty_already_billed = '||to_char(p_qty_already_billed)
612 ||' p_amount_outstanding = '||to_char(p_amount_outstanding)
613 ||' p_amount_ordered = '||to_char(p_amount_ordered)
614 ||' p_amt_already_billed = '||to_char(p_amt_already_billed)
615 );
616 END IF;
617
618 ---------------------------------------------------------------------------
619 -- Decide if overbilled
620 -- Bug 562898
621 -- Overbill flag should be Y is l_quantity_outstanding =0
622 ---------------------------------------------------------------------------
623
624 IF (l_matching_basis = 'QUANTITY') THEN
625 IF ((p_quantity_outstanding - p_quantity_invoiced) <= 0) THEN
626 P_overbilled := 'Y';
627 ELSE
628 P_overbilled := 'N';
629 END IF;
630 ELSIF (l_matching_basis = 'AMOUNT') THEN
631 IF ((p_amount_outstanding - p_amount_invoiced) <= 0) THEN
632 P_overbilled := 'Y';
633 ELSE
634 P_overbilled := 'N';
635 END IF;
636 END IF;
637
638 RETURN(TRUE);
639
640 EXCEPTION
641
642 WHEN OTHERS THEN
643 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
644 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
645 END IF;
646 IF (SQLCODE < 0) THEN
647 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
648 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
649 END IF;
650 END IF;
651
652 RETURN (FALSE);
653
654 END get_overbill_for_shipment;
655
656
657 /*======================================================================
658 Function: Get_Batch_ID
659 This function returns a batch_id and batch_type given a batch_name.
660 If the batch already exists the batch_type returned is 'OLD BATCH',
661 else the batch_type returned is 'NEW BATCH'. If this is a NEW
662 BATCH the batch_id is obtained from the appropriate sequence, else
663 it is read off the AP_BATCHES table.
664 ========================================================================*/
665 FUNCTION get_batch_id (
666 p_batch_name IN VARCHAR2,
667 P_batch_id OUT NOCOPY NUMBER,
668 p_batch_type OUT NOCOPY VARCHAR2,
669 P_calling_sequence IN VARCHAR2)
670 RETURN BOOLEAN
671 IS
672 l_batch_id NUMBER;
673 current_calling_sequence VARCHAR2(2000);
674 debug_info VARCHAR2(500);
675
676 BEGIN
677 -- Update the calling sequence
678
679 current_calling_sequence :=
680 'AP_Import_Utilities_Pkg.get_batch_id<-'||P_calling_sequence;
681
682 ------------------------------------------------------------------
683 -- Find the old batch_id if it's existing batch, or use sequence
684 -- find the next available batch_id
685 ------------------------------------------------------------------
686 debug_info := 'Check batch_name existance';
687
688 BEGIN
689 debug_info := '(Get_batch_id 1) Get old batch id';
690 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
691 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
692 END IF;
693
694 SELECT 'OLD BATCH',
695 batch_id
696 INTO p_batch_type,
697 l_batch_id
698 FROM ap_batches_all
699 WHERE batch_name = P_batch_name;
700
701 EXCEPTION
702 WHEN NO_DATA_FOUND THEN
703 p_batch_type := 'NEW BATCH';
704 END;
705
706 IF (p_batch_type = 'NEW BATCH') THEN
707
708 ---------------------------------------------
709 -- Get New batch_id and Batch_date
710 ---------------------------------------------
711
712 debug_info := '(Get_batch_id 2) Get New batch_id';
713 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
714 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
715 END IF;
716
717 SELECT ap_batches_s.nextval
718 INTO l_batch_id
719 FROM sys.dual;
720
721 END IF;
722
723 p_batch_id := l_batch_id;
724
725 RETURN(TRUE);
726
727 EXCEPTION
728 WHEN OTHERS then
729 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
730 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
731 END IF;
732
733 IF (SQLCODE < 0) THEN
734 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
735 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
736 END IF;
737 END IF;
738
739 RETURN (FALSE);
740
741 END get_batch_id;
742
743 FUNCTION get_auto_batch_name(
744 p_source IN VARCHAR2,
745 p_batch_name OUT NOCOPY VARCHAR2,
746 p_calling_sequence IN VARCHAR2)
747 RETURN BOOLEAN
748 IS
749 l_batch_num NUMBER;
750 current_calling_sequence VARCHAR2(2000);
751 debug_info VARCHAR2(500);
752
753 BEGIN
754 -- Update the calling sequence
755 --
756 current_calling_sequence :=
757 'AP_Import_Utilities_Pkg.get_auto_batch_name<-' ||p_calling_sequence;
758
759 debug_info := '(Get_auto_batch_name 1) automatically create batch name';
760
761 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
762 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
763 END IF;
764
765 SELECT ap_batches_s2.nextval
766 INTO l_batch_num
767 FROM sys.dual;
768
769 p_batch_name := p_source || ':' || to_char(l_batch_num);
770 RETURN(TRUE);
771
772 EXCEPTION
773
774 WHEN OTHERS THEN
775 RETURN(FALSE);
776
777 END get_auto_batch_name;
778
779
780 /*======================================================================
781 Function: Get_Info
782 This function returns values of system options, profile options and
783 financials options once an OU has been detected.
784 ========================================================================*/
785 FUNCTION get_info (
786 p_org_id IN NUMBER,
787 p_set_of_books_id OUT NOCOPY NUMBER,
788 p_multi_currency_flag OUT NOCOPY VARCHAR2,
789 p_make_rate_mandatory_flag OUT NOCOPY VARCHAR2,
790 p_default_exchange_rate_type OUT NOCOPY VARCHAR2,
791 p_base_currency_code OUT NOCOPY VARCHAR2,
792 p_batch_control_flag OUT NOCOPY VARCHAR2,
793 p_invoice_currency_code OUT NOCOPY VARCHAR2,
794 p_base_min_acct_unit OUT NOCOPY NUMBER,
795 p_base_precision OUT NOCOPY NUMBER,
796 p_sequence_numbering OUT NOCOPY VARCHAR2,
797 p_awt_include_tax_amt OUT NOCOPY VARCHAR2,
798 p_gl_date IN OUT NOCOPY DATE,
799 -- p_ussgl_transcation_code OUT NOCOPY VARCHAR2, - Bug 4277744
800 p_trnasfer_desc_flex_flag OUT NOCOPY VARCHAR2,
801 p_gl_date_from_receipt_flag OUT NOCOPY VARCHAR2,
802 p_purch_encumbrance_flag OUT NOCOPY VARCHAR2,
803 p_retainage_ccid OUT NOCOPY NUMBER,
804 P_pa_installed OUT NOCOPY VARCHAR2,
805 p_chart_of_accounts_id OUT NOCOPY NUMBER,
806 p_inv_doc_cat_override OUT NOCOPY VARCHAR2,
807 p_calc_user_xrate OUT NOCOPY VARCHAR2,
808 p_calling_sequence IN VARCHAR2,
809 p_approval_workflow_flag OUT NOCOPY VARCHAR2,
810 p_freight_code_combination_id OUT NOCOPY NUMBER,
811 p_allow_interest_invoices OUT NOCOPY VARCHAR2, --bug 4113223
812 p_add_days_settlement_date OUT NOCOPY NUMBER, --bug 4930111
813 p_disc_is_inv_less_tax_flag OUT NOCOPY VARCHAR2, --bug 4931755
814 p_source IN VARCHAR2, --bug 5382889. LE TimeZone
815 p_invoice_date IN DATE, -- bug 5382889. LE TimeZone
816 p_goods_received_date IN DATE, -- bug 5382889. LE TimeZone
817 p_asset_book_type OUT NOCOPY VARCHAR2 -- Bug 5448579
818 )
819 RETURN BOOLEAN
820 IS
821
822 l_status VARCHAR2(10);
823 l_industry VARCHAR2(10);
824 get_info_failure EXCEPTION;
825 current_calling_sequence VARCHAR2(2000);
826 debug_info VARCHAR2(500);
827 l_ext_precision NUMBER(2);
828
829
830
831 l_inv_gl_date DATE; --Bug 5382889. LE Timezone
832 l_rts_txn_le_date DATE; --Bug 5382889. LE Timezone
833 l_inv_le_date DATE; --Bug 5382889. LE Timezone
834 l_sys_le_date DATE; --Bug 5382889. LE Timezone
835
836 l_asset_book_count NUMBER;
837
838 BEGIN
839 -- Update the calling sequence
840
841 current_calling_sequence :=
842 'AP_Import_Utilities_Pkg.Get_info<-'||P_calling_sequence;
843
844 debug_info := '(Get_info 1) Read from ap_system_parameters';
845 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
846 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
847 END IF;
848
849 SELECT
850 set_of_books_id,
851 multi_currency_flag,
852 make_rate_mandatory_flag,
853 default_exchange_rate_type,
854 base_currency_code,
855 aps.invoice_currency_code,
856 awt_include_tax_amt,
857 -- ussgl_transaction_code, - Bug 4277744
858 transfer_desc_flex_flag,
859 gl_date_from_receipt_flag,
860 inv_doc_category_override,
861 NVL(calc_user_xrate, 'N'),
862 NVL(approval_workflow_flag,'N'),
863 freight_code_combination_id ,
864 /*we need to get the value of allow_interest_invoices
865 from system_parameters versus product setup, since the value
866 in the product setup is only for defaulting into suppliers,
867 whereas the value in asp decides whether we create INT invoices
868 or not*/
869 asp.auto_calculate_interest_flag,
870 --bugfix:4930111
871 asp.add_days_settlement_date,
872 NVL(asp.disc_is_inv_less_tax_flag, 'N') /* bug 4931755 */
873 INTO p_set_of_books_id,
874 p_multi_currency_flag,
875 p_make_rate_mandatory_flag,
876 p_default_exchange_rate_type,
877 p_base_currency_code,
878 p_invoice_currency_code,
879 p_awt_include_tax_amt,
880 -- p_ussgl_transcation_code, - Bug 4277744
881 p_trnasfer_desc_flex_flag,
882 p_gl_date_from_receipt_flag,
883 p_inv_doc_cat_override,
884 p_calc_user_xrate,
885 p_approval_workflow_flag,
886 p_freight_code_combination_id,
887 p_allow_interest_invoices,
888 p_add_days_settlement_date,
889 p_disc_is_inv_less_tax_flag
890 FROM ap_system_parameters_all asp,
891 ap_product_setup aps
892 WHERE asp.org_id = p_org_id;
893
894 debug_info := '(Get_info 2) Get Batch Control Profile Option';
895 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
896 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
897 END IF;
898
899 BEGIN
900 FND_PROFILE.GET('AP_USE_INV_BATCH_CONTROLS',p_batch_control_flag);
901
902 EXCEPTION
903 WHEN OTHERS THEN
904 p_batch_control_flag := 'N';
905 END ;
906
907 debug_info := '(Get_info 3) Get encumbrance option';
908 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
909 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
910 END IF;
911
912 SELECT purch_encumbrance_flag, retainage_code_combination_id
913 INTO p_purch_encumbrance_flag, p_retainage_ccid
914 FROM financials_system_params_all
915 WHERE org_id = p_org_id;
916
917 debug_info := '(Get_info 4) Get minimum_accountable_unit';
918 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
919 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
920 END IF;
921
922 FND_CURRENCY.GET_INFO(
923 p_base_currency_code ,
924 p_base_precision ,
925 l_ext_precision ,
926 p_base_min_acct_unit);
927
928 debug_info := '(Get_info 5) Get p_sequence_numbering';
929 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
930 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
931 END IF;
932
933 p_sequence_numbering := FND_PROFILE.VALUE('UNIQUE:SEQ_NUMBERS');
934
935
936 debug_info := '(Get_info 6) Get gl_date based on report parameters';
937 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
938 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
939 END IF;
940
941 -- Bug 5645581. Gl_date will calculated at the Import_Invoices
942 -- Procedure in the Main Package
943 /*IF p_source = 'ERS' THEN -- bug 5382889, LE TimeZone
944
945 debug_info := 'Determine gl_date from ERS invoice';
946
947 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
948 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
949 END IF;
950
951 l_rts_txn_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
952 p_trxn_date => nvl(p_goods_received_date, p_invoice_date)
953 ,p_ou_id => p_org_id);
954
955 l_inv_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
956 p_trxn_date => p_invoice_date
957 ,p_ou_id => p_org_id);
958
959 l_sys_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
960 p_trxn_date => sysdate
961 ,p_ou_id => p_org_id);
962
963
964 -- The gl_date id determined from the flag gl_date_from_receipt_flag
965 -- If the flag = 'I' -- take Invoice_date
966 -- = 'S' -- take System date
967 -- = 'N' -- take nvl(receipt_date, invoice_date)
968 -- = 'Y' -- take nvl(receipt_date, sysdate)
969 -- Note here that the Invoice date is no longer the same as the receipt_date,
970 -- i.e. the RETURN tranasaction_date , so case I and N are no longer the same
971
972 debug_info := 'Determine invoice gl_date from LE Timezone API ';
973 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
974 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
975 END IF;
976
977 If (p_gl_date_from_receipt_flag = 'I') Then
978 l_inv_gl_date := l_inv_le_date;
979 Elsif (p_gl_date_from_receipt_flag = 'N') Then
980 l_inv_gl_date := nvl(l_rts_txn_le_date, l_inv_le_date);
981 Elsif (p_gl_date_from_receipt_flag = 'S') Then
982 l_inv_gl_date := l_sys_le_date;
983 Elsif (p_gl_date_from_receipt_flag = 'Y') then
984 l_inv_gl_date := nvl(l_rts_txn_le_date, l_sys_le_date);
985 End if;
986
987 p_gl_date := l_inv_gl_date;
988
989 ELSE
990 IF (p_gl_date IS NULL) THEN
991 IF (p_gl_date_from_receipt_flag IN ('S','Y')) THEN
992 debug_info := '(Get_info 6a) GL Date is Sysdate';
993 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
994 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
995 END IF;
996
997 p_gl_date := sysdate;
998
999 ELSE
1000 debug_info := '(Get_info 6b) GL Date should be Invoice Date';
1001 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1002 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1003 END IF;
1004 END IF;
1005 END IF;
1006 END IF;
1007
1008 p_gl_date := trunc(p_gl_date); */
1009 debug_info := '(Get_info 7) Check if PA is installed';
1010 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1011 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1012 END IF;
1013
1014 IF (FND_INSTALLATION.GET(275, 275, l_status, l_industry)) THEN
1015 IF (l_status <> 'I') THEN
1016 P_PA_INSTALLED := 'N';
1017 ELSE
1018 P_PA_INSTALLED := 'Y';
1019 AP_IMPORT_INVOICES_PKG.g_pa_allows_overrides :=
1020 NVL(FND_PROFILE.VALUE('PA_ALLOW_FLEXBUILDER_OVERRIDES'), 'N');
1021 END IF;
1022 ELSE
1023 RAISE get_info_failure;
1024 END IF;
1025
1026 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1027 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1028 '------------------> l_status = '|| l_status
1029 ||' l_industry = ' ||l_industry
1030 ||' p_pa_installed = '||p_pa_installed);
1031 END IF;
1032
1033 debug_info := '(Get_info 8) Get chart_of_accounts_id from p_set_of_books_id';
1034 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1035 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1036 END IF;
1037
1038 SELECT chart_of_accounts_id
1039 INTO p_chart_of_accounts_id
1040 FROM gl_sets_of_books
1041 WHERE set_of_books_id = p_set_of_books_id;
1042
1043 -- Bug 5448579
1044 debug_info := '(Get_info 9) Get Asset Book Type Code';
1045 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1046 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1047 END IF;
1048 BEGIN
1049 SELECT count(*)
1050 INTO l_asset_book_count
1051 FROM fa_book_controls bc
1052 WHERE bc.book_class = 'CORPORATE'
1053 AND bc.set_of_books_id = p_set_of_books_id
1054 AND bc.date_ineffective IS NULL;
1055
1056 IF (l_asset_book_count = 1) THEN
1057 SELECT bc.book_type_code
1058 INTO p_asset_book_type
1059 FROM fa_book_controls bc
1060 WHERE bc.book_class = 'CORPORATE' --bug7040148
1061 AND bc.set_of_books_id = p_set_of_books_id
1062 AND bc.date_ineffective IS NULL;
1063
1064 ELSE
1065 p_asset_book_type := NULL;
1066 END IF;
1067
1068 EXCEPTION
1069 -- No need to error handle if FA information not available.
1070 WHEN no_data_found THEN
1071 NULL;
1072 WHEN OTHERS THEN
1073 NULL;
1074 END;
1075
1076 debug_info := '(Get_info 9) Get system tolerances';
1077 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1078 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1079 END IF;
1080
1081 -- For EDI transactions, if the price and qty tolerance is set to null
1082 -- we assume this to be zero. This is implemented as per discussion with
1083 -- Subir.
1084
1085 --Bug 4051803 commented out the below code and moved it to
1086 --function get_tolerance_info, which will be called to for
1087 --get tolerance info specific to site as oppose to org.
1088 /*
1089 SELECT
1090 DECODE(price_tolerance, NULL,1,(1 + (price_tolerance/100))),
1091 DECODE(price_tolerance, NULL,1,(1 - (price_tolerance/100))),
1092 DECODE(quantity_tolerance, NULL,1, (1 + (quantity_tolerance/100))),
1093 DECODE(qty_received_tolerance, NULL,NULL, (1 +(qty_received_tolerance/100))),
1094 max_qty_ord_tolerance,
1095 max_qty_rec_tolerance,
1096 ship_amt_tolerance,
1097 rate_amt_tolerance,
1098 total_amt_tolerance
1099 INTO
1100 p_positive_price_tolerance,
1101 p_negative_price_tolerance,
1102 p_qty_tolerance,
1103 p_qty_rec_tolerance,
1104 p_max_qty_ord_tolerance,
1105 p_max_qty_rec_tolerance,
1106 p_ship_amt_tolerance,
1107 p_rate_amt_tolerance,
1108 p_total_amt_tolerance
1109 FROM ap_tolerances_all
1110 where org_id = p_org_id; */
1111
1112 RETURN (TRUE);
1113
1114
1115 EXCEPTION
1116 WHEN OTHERS then
1117 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1118 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1119 END IF;
1120
1121 IF (SQLCODE < 0) then
1122 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1123 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
1124 END IF;
1125 END IF;
1126
1127 RETURN (FALSE);
1128
1129 END get_info;
1130
1131
1132 -- Bug 4051803
1133 --===================================================================
1134 -- Function: get_tolerance_info
1135 -- Get tolerance info. from po_vendor_sites_all
1136 -- based on vendor_site_id
1137 --===================================================================
1138 FUNCTION get_tolerance_info(
1139 p_vendor_site_id IN NUMBER,
1140 p_positive_price_tolerance OUT NOCOPY NUMBER,
1141 p_negative_price_tolerance OUT NOCOPY NUMBER,
1142 p_qty_tolerance OUT NOCOPY NUMBER,
1143 p_qty_rec_tolerance OUT NOCOPY NUMBER,
1144 p_max_qty_ord_tolerance OUT NOCOPY NUMBER,
1145 p_max_qty_rec_tolerance OUT NOCOPY NUMBER,
1146 p_amt_tolerance OUT NOCOPY NUMBER,
1147 p_amt_rec_tolerance OUT NOCOPY NUMBER,
1148 p_max_amt_ord_tolerance OUT NOCOPY NUMBER,
1149 p_max_amt_rec_tolerance OUT NOCOPY NUMBER,
1150 p_goods_ship_amt_tolerance OUT NOCOPY NUMBER,
1151 p_goods_rate_amt_tolerance OUT NOCOPY NUMBER,
1152 p_goods_total_amt_tolerance OUT NOCOPY NUMBER,
1153 p_services_ship_amt_tolerance OUT NOCOPY NUMBER,
1154 p_services_rate_amt_tolerance OUT NOCOPY NUMBER,
1155 p_services_total_amt_tolerance OUT NOCOPY NUMBER,
1156 p_calling_sequence IN VARCHAR2)
1157 RETURN BOOLEAN IS
1158 debug_info VARCHAR2(500);
1159 l_price_tolerance ap_tolerance_templates.price_tolerance%TYPE;
1160 BEGIN
1161
1162 debug_info := '(Get_tolerance_info 1) Get tolerance info...';
1163 If AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' then
1164 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1165 End if;
1166
1167 -- For EDI transactions, if the price and qty tolerance is set to null
1168 -- we assume this to be zero. This is implemented as per discussion with
1169 -- Subir.
1170
1171 BEGIN
1172
1173 select price_tolerance,
1174 decode(price_tolerance, NULL,1,(1 + (price_tolerance/100))),
1175 decode(price_tolerance, NULL,1,(1 - (price_tolerance/100))),
1176 decode(quantity_tolerance, NULL,1, (1 + (quantity_tolerance/100))),
1177 decode(qty_received_tolerance, NULL,NULL, (1 +(qty_received_tolerance/100))),
1178 max_qty_ord_tolerance,
1179 max_qty_rec_tolerance,
1180 ship_amt_tolerance,
1181 rate_amt_tolerance,
1182 total_amt_tolerance
1183 into
1184 l_price_tolerance,
1185 p_positive_price_tolerance,
1186 p_negative_price_tolerance,
1187 p_qty_tolerance,
1188 p_qty_rec_tolerance,
1189 p_max_qty_ord_tolerance,
1190 p_max_qty_rec_tolerance,
1191 p_goods_ship_amt_tolerance,
1192 p_goods_rate_amt_tolerance,
1193 p_goods_total_amt_tolerance
1194 from ap_tolerance_templates att,
1195 po_vendor_sites_all pvs
1196 where pvs.vendor_site_id = p_vendor_site_id
1197 and pvs.tolerance_id = att.tolerance_id;
1198
1199 EXCEPTION
1200 when no_data_found then
1201 debug_info := '(get_tolerance_info 1) NO_DATA_FOUND exception';
1202 If AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' then
1203 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1204 End if;
1205 END;
1206
1207
1208 BEGIN
1209 select decode(quantity_tolerance, NULL,1, (1 + (quantity_tolerance/100))),
1210 decode(qty_received_tolerance, NULL,NULL, (1 +(qty_received_tolerance/100))),
1211 max_qty_ord_tolerance,
1212 max_qty_rec_tolerance,
1213 ship_amt_tolerance,
1214 rate_amt_tolerance,
1215 total_amt_tolerance
1216 into
1217 p_amt_tolerance,
1218 p_amt_rec_tolerance,
1219 p_max_amt_ord_tolerance,
1220 p_max_amt_rec_tolerance,
1221 p_services_ship_amt_tolerance,
1222 p_services_rate_amt_tolerance,
1223 p_services_total_amt_tolerance
1224 from ap_tolerance_templates att,
1225 po_vendor_sites_all pvs
1226 where pvs.vendor_site_id = p_vendor_site_id
1227 and pvs.services_tolerance_id = att.tolerance_id;
1228
1229
1230 EXCEPTION WHEN NO_DATA_FOUND THEN
1231
1232 debug_info := '(get_tolerance_info 2) NO_DATA_FOUND exception';
1233 If AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' then
1234 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1235 End if;
1236
1237 END;
1238
1239 If AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' then
1240 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,'------------------> p_vendor_site_id = '||
1241 to_char(p_vendor_site_id)
1242 ||' l_price_tolerance = '||to_char(l_price_tolerance)
1243 ||' l_positive_price_tolerance = '||to_char(p_positive_price_tolerance)
1244 ||' l_negative_price_tolerance = '||to_char(p_negative_price_tolerance)
1245 ||' l_qty_tolerance = '||to_char(p_qty_tolerance)
1246 ||' l_qty_received_tolerance = '||to_char(p_qty_rec_tolerance)
1247 ||' l_max_qty_ord_tolerance = '||to_char(p_max_qty_ord_tolerance)
1248 ||' l_max_qty_rec_tolerance = '||to_char(p_max_qty_rec_tolerance)
1249 ||' l_amt_tolerance = '||to_char(p_amt_tolerance)
1250 ||' l_amt_received_tolerance = '||to_char(p_amt_rec_tolerance)
1251 ||' l_max_amt_ord_tolerance = '||to_char(p_max_amt_ord_tolerance)
1252 ||' l_max_amt_rec_tolerance = '||to_char(p_max_amt_rec_tolerance)
1253 ||' l_goods_ship_amt_tolerance = '||to_char(p_goods_ship_amt_tolerance)
1254 ||' l_goods_rate_amttolerance = '||to_char(p_goods_rate_amt_tolerance)
1255 ||' l_goods_total_amt_tolerance = '||to_char(p_goods_total_amt_tolerance)
1256 ||' l_services_ship_amt_tolerance = '||to_char(p_services_ship_amt_tolerance)
1257 ||' l_services_rate_amttolerance = '||to_char(p_services_rate_amt_tolerance)
1258 ||' l_services_total_amt_tolerance = '||to_char(p_services_total_amt_tolerance));
1259 end if;
1260
1261 RETURN (TRUE);
1262
1263 EXCEPTION
1264
1265 WHEN OTHERS then
1266 If AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' then
1267 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info); End if;
1268
1269 IF (SQLCODE < 0) then
1270 If AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y'
1271 then Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM); End if;
1272 END IF;
1273
1274 RETURN (FALSE);
1275
1276 END get_tolerance_info;
1277
1278
1279 /*======================================================================
1280 Function: find_vendor_primary_paysite
1281 This function is called when import program is trying to default a
1282 vendor site in case user did not give input of vendor site information.
1283 1. Return primary site id if there is one
1284 2. Return the only paysite if there is no primary paysite
1285 3. Return null if there are multiple paysite but no primary paysite
1286 4. Return null if there is no paysite
1287 ========================================================================*/
1288 FUNCTION find_vendor_primary_paysite(
1289 p_vendor_id IN NUMBER,
1290 p_vendor_primary_paysite_id OUT NOCOPY NUMBER,
1291 p_calling_sequence IN VARCHAR2)
1292 RETURN BOOLEAN
1293 IS
1294
1295 CURSOR primary_pay_site_cur IS
1296 SELECT vendor_site_id
1297 FROM po_vendor_sites PVS
1298 WHERE vendor_id = p_vendor_id
1299 AND pay_site_flag = 'Y'
1300 AND primary_pay_site_flag = 'Y'
1301 AND NVL(trunc(PVS.INACTIVE_DATE),AP_IMPORT_INVOICES_PKG.g_inv_sysdate+1)
1302 > AP_IMPORT_INVOICES_PKG.g_inv_sysdate ;
1303
1304 CURSOR pay_site_cur IS
1305 SELECT vendor_site_id
1306 FROM po_vendor_sites PVS
1307 WHERE vendor_id = p_vendor_id
1308 AND pay_site_flag = 'Y'
1309 AND NVL(trunc(PVS.INACTIVE_DATE),AP_IMPORT_INVOICES_PKG.g_inv_sysdate+1)
1310 > AP_IMPORT_INVOICES_PKG.g_inv_sysdate ;
1311
1312 l_vendor_site_id PO_VENDOR_SITES.VENDOR_SITE_ID%TYPE;
1313 l_paysite_count NUMBER;
1314 current_calling_sequence VARCHAR2(2000);
1315 debug_info VARCHAR2(500);
1316
1317 BEGIN
1318 -- Update the calling sequence
1319
1320 current_calling_sequence :=
1321 'AP_IMPORT_UTILITIES_PKG.find_vendor_primary_paysite<-'
1322 ||P_calling_sequence;
1323
1324 debug_info := '(Find vendor primary paysite 1) Get the primary paysite';
1325 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1326 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1327 END IF;
1328
1329 OPEN primary_pay_site_cur;
1330 FETCH primary_pay_site_cur INTO l_vendor_site_id;
1331 CLOSE primary_pay_site_cur;
1332
1333 IF ( l_vendor_site_id is null ) THEN
1334
1335 SELECT count(*)
1336 INTO l_paysite_count
1337 FROM po_vendor_sites PVS
1338 WHERE vendor_id = p_vendor_id
1339 AND pay_site_flag = 'Y'
1340 AND NVL(trunc(PVS.INACTIVE_DATE),AP_IMPORT_INVOICES_PKG.g_inv_sysdate+1)
1341 > AP_IMPORT_INVOICES_PKG.g_inv_sysdate ;
1342
1343 IF ( l_paysite_count = 1 ) THEN
1344 OPEN pay_site_cur;
1345 FETCH pay_site_cur INTO l_vendor_site_id;
1346 CLOSE pay_site_cur;
1347 p_vendor_primary_paysite_id := l_vendor_site_id;
1348 ELSE
1349 p_vendor_primary_paysite_id := null;
1350 END IF;
1351 ELSE
1352 p_vendor_primary_paysite_id := l_vendor_site_id;
1353 END IF;
1354
1355 RETURN(TRUE);
1356
1357 EXCEPTION
1358
1359 WHEN OTHERS THEN
1360 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1361 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1362 END IF;
1363 IF (SQLCODE < 0) then
1364 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1365 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
1366 END IF;
1367 END IF;
1368
1369 RETURN (FALSE);
1370
1371 END find_vendor_primary_paysite;
1372
1373
1374 FUNCTION get_employee_id(
1375 p_invoice_id IN NUMBER,
1376 p_vendor_id IN NUMBER,
1377 p_employee_id OUT NOCOPY NUMBER,
1378 p_default_last_updated_by IN NUMBER,
1379 p_default_last_update_login IN NUMBER,
1380 p_current_invoice_status OUT NOCOPY VARCHAR2,
1381 p_calling_sequence IN VARCHAR2)
1382 RETURN BOOLEAN
1383 IS
1384 get_employee_failure EXCEPTION;
1385 l_current_invoice_status VARCHAR2(1) := 'Y';
1386 l_employee_id NUMBER;
1387 current_calling_sequence VARCHAR2(2000);
1388 debug_info VARCHAR2(500);
1389
1390 BEGIN
1391 -- Update the calling sequence
1392
1393 current_calling_sequence := 'get_employee_id<-'||P_calling_sequence;
1394
1395 BEGIN
1396 debug_info := '(Get_employee_id 1) Get employee id from po_vendors';
1397 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1398 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1399 END IF;
1400
1401 SELECT employee_id
1402 INTO l_employee_id
1403 FROM po_vendors
1404 WHERE vendor_id = p_vendor_id;
1405
1406 EXCEPTION
1407 WHEN NO_DATA_FOUND THEN
1408
1409 -- Potentially this should never happen
1410 -- as vendor is already validated at the invoice level
1411
1412 debug_info := '(Get_employee_id 2) Vendor Id is invalid';
1413 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1414 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1415 END IF;
1416
1417 -- Reject Here for Invalid Vendor
1418
1419 debug_info := '(Get emloyee_id 3) Check for invalid Supplier.';
1420 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1421 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1422 END IF;
1423
1424 IF (insert_rejections(AP_IMPORT_INVOICES_PKG.g_invoices_table,
1425 p_invoice_id,
1426 'INVALID SUPPLIER',
1427 p_default_last_updated_by,
1428 p_default_last_update_login,
1429 current_calling_sequence) <> TRUE) THEN
1430 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1431 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1432 'insert_rejections<-'||current_calling_sequence);
1433 END IF;
1434 RAISE get_employee_failure;
1435 END IF;
1436 l_current_invoice_status := 'N';
1437 END;
1438
1439 IF (l_employee_id IS NULL) THEN
1440
1441 -- We shall not reject if employee id is Null
1442
1443 debug_info := '(Get_employee_id 3) Employee_id id Null';
1444 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1445 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1446 END IF;
1447
1448 END IF;
1449 --
1450 p_employee_id :=l_employee_id;
1451 p_current_invoice_status := l_current_invoice_status;
1452
1453 RETURN(TRUE);
1454
1455 EXCEPTION
1456 WHEN OTHERS THEN
1457 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1458 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1459 END IF;
1460
1461 IF (SQLCODE < 0) THEN
1462 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1463 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
1464 END IF;
1465 END IF;
1466
1467 RETURN (FALSE);
1468
1469 END get_employee_id;
1470
1471
1472 FUNCTION get_next_dist_line_num(
1473 p_invoice_id IN NUMBER,
1474 p_line_num IN NUMBER,
1475 p_next_dist_num OUT NOCOPY NUMBER,
1476 P_calling_sequence IN VARCHAR2)
1477 RETURN BOOLEAN
1478 IS
1479 current_calling_sequence VARCHAR2(2000);
1480 debug_info VARCHAR2(500);
1481
1482 BEGIN
1483
1484 -- Update the calling sequence
1485
1486 current_calling_sequence := 'get_next_dist_line_num<-'||P_calling_sequence;
1487
1488 --------------------------------------------------------------------------
1489 -- Step 1
1490 -- Get the next available distribution line number given the invoice
1491 -- and line number
1492 --------------------------------------------------------------------------
1493
1494 debug_info := '(Get Next Dist Line Num 1) Get the next available '||
1495 'distribution line number';
1496 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1497 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1498 END IF;
1499
1500 /* For bug 3929697
1501 * Before executing the select for getting the value
1502 * of distribution line number check whether it is already
1503 * fetched. If so, increment distribution line number
1504 * else execute the select to get the corresponding value
1505 * */
1506
1507 If (lg_invoice_id = p_invoice_id and lg_dist_line_num is not null) Then
1508 p_next_dist_num := lg_dist_line_num + 1;
1509 Else
1510 SELECT max(distribution_line_number)
1511 INTO p_next_dist_num
1512 FROM ap_invoice_distributions
1513 WHERE invoice_id = p_invoice_id
1514 AND invoice_line_number = p_line_num;
1515 p_next_dist_num := nvl(p_next_dist_num,0) + 1;
1516 End if;
1517 lg_invoice_id := p_invoice_id;
1518 lg_dist_line_num := p_next_dist_num;
1519
1520 RETURN(TRUE);
1521
1522 RETURN NULL; EXCEPTION
1523
1524 WHEN NO_DATA_FOUND THEN
1525 p_next_dist_num := 1;
1526 /* For bug 3929697
1527 Initialized the global variables */
1528 lg_invoice_id := p_invoice_id;
1529 lg_dist_line_num := p_next_dist_num;
1530 RETURN(TRUE);
1531
1532 WHEN OTHERS THEN
1533 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1534 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1535 END IF;
1536
1537 IF (SQLCODE < 0) THEN
1538 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1539 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
1540 END IF;
1541 END IF;
1542
1543 RETURN (FALSE);
1544
1545 END get_next_dist_line_num;
1546
1547
1548 FUNCTION get_overbill_for_po_line(
1549 p_po_line_id IN NUMBER,
1550 p_quantity_invoiced IN NUMBER,
1551 p_amount_invoiced IN NUMBER,
1552 p_overbilled OUT NOCOPY VARCHAR2,
1553 p_outstanding OUT NOCOPY NUMBER,
1554 p_ordered OUT NOCOPY NUMBER,
1555 p_already_billed OUT NOCOPY NUMBER,
1556 p_po_line_matching_basis OUT NOCOPY VARCHAR2,
1557 P_calling_sequence IN VARCHAR2)
1558 RETURN BOOLEAN
1559
1560 IS
1561 current_calling_sequence VARCHAR2(2000);
1562 debug_info VARCHAR2(500);
1563
1564 BEGIN
1565 -- Update the calling sequence
1566
1567 current_calling_sequence := 'get_overbill_for_po_line<-'||P_calling_sequence;
1568
1569 ----------------------------------------------------------------------------
1570 -- Step 1
1571 -- Get quantity_outstanding
1572 ----------------------------------------------------------------------------
1573 debug_info := '(Get Overbill for PO Line 1) Get quantity_outstanding';
1574 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1575 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1576 END IF;
1577
1578 --Contract Payments: Modified the SELECT clause
1579 /*-----------------------------------------------------------------------------------------+
1580 --For the case of complex works, following scenarios are possible:
1581 1.Matching Basis at PO Line = 'AMOUNT' and
1582 shipments could have matching_basis of 'QUANTITY'/'AMOUNT'. And within that
1583 shipment_type could be 'PREPAYMENT' or 'STANDARD'. For 'PREPAYMENT'
1584 we need to go off of financed columns and
1585 for 'STANDARD' we need to go off of Billed columns.
1586 2.Matching Basis at PO Line = 'QUANTITY'
1587 and then shipments can have the matching basis of only 'QUANTITY'. And within that
1588 shipment_type could be 'PREPAYMENT' or 'STANDARD'. For 'PREPAYMENT'
1589 we need to go off of financed columns and
1590 for 'STANDARD' we need to go off of Billed columns.
1591 +------------------------------------------------------------------------------------------*/
1592
1593 SELECT
1594 DECODE(pl.matching_basis, 'QUANTITY',
1595 DECODE(pll.shipment_type,'PREPAYMENT',
1596 sum(NVL(pll.quantity,0) - NVL(pll.quantity_financed,0) -
1597 NVL(pll.quantity_cancelled,0)),
1598 sum(NVL(pll.quantity,0) - NVL(pll.quantity_billed,0) -
1599 NVL(pll.quantity_cancelled,0))
1600 ),
1601 'AMOUNT',
1602 SUM(DECODE(pll.matching_basis,'QUANTITY',
1603 (DECODE(pll.shipment_type,'PREPAYMENT',
1604 NVL(pll.quantity,0) - NVL(pll.quantity_financed,0) -
1605 NVL(pll.quantity_cancelled,0),
1606 NVL(pll.quantity,0) - NVL(pll.quantity_billed,0) -
1607 NVL(pll.quantity_cancelled,0)
1608 )
1609 )*pll.price_override,
1610 'AMOUNT',
1611 DECODE(pll.shipment_type,'PREPAYMENT',
1612 NVL(pll.amount,0) - NVL(pll.amount_financed,0) -
1613 NVL(pll.amount_cancelled,0),
1614 NVL(pll.amount,0) - NVL(pll.amount_billed,0) -
1615 NVL(pll.amount_cancelled,0)
1616 )
1617 )
1618 )
1619 ),
1620 DECODE(pl.matching_basis,
1621 'QUANTITY',
1622 SUM(NVL(pll.quantity,0) - NVL(pll.quantity_cancelled,0)),
1623 'AMOUNT',
1624 SUM(DECODE(pll.matching_basis,
1625 'QUANTITY',
1626 (NVL(pll.quantity,0) - NVL(pll.quantity_cancelled,0))*pll.price_override,
1627 'AMOUNT',
1628 NVL(pll.amount,0) - NVL(pll.amount_cancelled,0)
1629 )
1630 )
1631 ),
1632 DECODE(pl.matching_basis,
1633 'QUANTITY',
1634 DECODE(shipment_type,'PREPAYMENT',
1635 sum(NVL(quantity_financed,0)),sum(NVL(quantity_billed,0))
1636 ),
1637 'AMOUNT',
1638 SUM(DECODE(pll.matching_basis,
1639 'QUANTITY',
1640 DECODE(shipment_type,'PREPAYMENT',
1641 NVL(quantity_financed,0),NVL(quantity_billed,0)
1642 )*pll.price_override,
1643 'AMOUNT',
1644 DECODE(pll.shipment_type,'PREPAYMENT',
1645 NVL(pll.amount_financed,0),NVL(pll.amount_billed,0)
1646 )
1647 )
1648 )
1649 ),
1650 pl.matching_basis
1651 INTO p_outstanding,
1652 p_ordered,
1653 p_already_billed,
1654 p_po_line_matching_basis
1655 FROM po_line_locations pll,
1656 po_lines pl
1657 WHERE pll.po_line_id = p_po_line_id
1658 AND pl.po_line_id = pll.po_line_id
1659 -- bug fix 6959362 starts
1660 group by pl.matching_basis, pll.shipment_type;
1661 -- bug fix 6959362 ends
1662
1663 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1664 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1665 '------------------> p_outstanding = '
1666 ||to_char(p_outstanding)
1667 ||' p_ordered = '||to_char(p_ordered)
1668 ||' p_already_billed = '||to_char(p_already_billed));
1669 END IF;
1670
1671 ----------------------------------------------------
1672 -- Decide if overbilled
1673 ----------------------------------------------------
1674 -- Bug 562898
1675 -- Overbill flag should be Y is l_quantity_outstanding =0
1676 IF (p_po_line_matching_basis = 'QUANTITY') THEN
1677 IF ((p_outstanding - p_quantity_invoiced) <= 0) THEN
1678 P_overbilled := 'Y';
1679 ELSE
1680 P_overbilled := 'N';
1681 END IF;
1682 ELSIF (p_po_line_matching_basis = 'AMOUNT') THEN
1683 IF ((p_outstanding - p_amount_invoiced) <= 0) THEN
1684 P_overbilled := 'Y';
1685 ELSE
1686 P_overbilled := 'N';
1687 END IF;
1688 END IF;
1689
1690 RETURN(TRUE);
1691
1692 EXCEPTION
1693 WHEN OTHERS THEN
1694 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1695 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1696 END IF;
1697
1698 IF (SQLCODE < 0) THEN
1699 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1700 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
1701 END IF;
1702 END IF;
1703
1704 RETURN (FALSE);
1705
1706 END get_overbill_for_po_line;
1707
1708
1709 FUNCTION pa_flexbuild (
1710 p_invoice_rec IN
1711 AP_IMPORT_INVOICES_PKG.r_invoice_info_rec,
1712 p_invoice_lines_rec IN OUT NOCOPY
1713 AP_IMPORT_INVOICES_PKG.r_line_info_rec,
1714 p_accounting_date IN DATE,
1715 p_pa_installed IN VARCHAR2,
1716 p_employee_id IN NUMBER,
1717 p_base_currency_code IN VARCHAR2,
1718 p_chart_of_accounts_id IN NUMBER,
1719 p_default_last_updated_by IN NUMBER,
1720 p_default_last_update_login IN NUMBER,
1721 p_pa_default_dist_ccid OUT NOCOPY NUMBER,
1722 p_pa_concatenated_segments OUT NOCOPY VARCHAR2,
1723 p_current_invoice_status OUT NOCOPY VARCHAR2,
1724 p_calling_sequence IN VARCHAR2)
1725 RETURN BOOLEAN
1726 IS
1727 pa_flexbuild_failure EXCEPTION;
1728 l_current_invoice_status VARCHAR2(1) := 'Y';
1729 user_id NUMBER;
1730 procedure_billable_flag VARCHAR2(60) := '';
1731 l_msg_application VARCHAR2(25);
1732 l_msg_type VARCHAR2(25);
1733 l_msg_token1 VARCHAR2(30);
1734 l_msg_token2 VARCHAR2(30);
1735 l_msg_token3 VARCHAR2(30);
1736 l_msg_count NUMBER;
1737 l_msg_data VARCHAR2(500);
1738 l_concat_ids VARCHAR2(200);
1739 -- CHANGES FOR BUG - 3657665 ** STARTS **
1740 --l_errmsg VARCHAR2(200);
1741 l_errmsg VARCHAR2(2000);
1742 -- CHANGES FOR BUG - 3657665 ** ENDS **
1743 l_concat_descrs VARCHAR2(500);
1744 l_concat_segs VARCHAR2(2000);
1745 current_calling_sequence VARCHAR2(2000);
1746 debug_info VARCHAR2(500);
1747 l_sys_link_function VARCHAR2(2); --Bugfix:5725904
1748
1749 BEGIN
1750
1751 -- Update the calling sequence
1752
1753 current_calling_sequence :=
1754 'AP_IMPORT_UTILITIES_PKG.pa_flexbuild<-'||P_calling_sequence;
1755
1756 ----------------------------------------------------------------------------
1757 -- Step 1
1758 ----------------------------------------------------------------------------
1759
1760 debug_info := '(PA Flexbuild 1) Check for PA installation and Project Info';
1761 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1762 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
1763 END IF;
1764
1765 IF (p_pa_installed = 'Y' AND
1766 p_invoice_lines_rec.project_id is not null) THEN
1767
1768 -- We only care to VAlidate Transactions and flexbuild if PA is
1769 -- installed and there is a project_id; that is, the invoice is
1770 -- project-related.
1771
1772 debug_info := '(PA Flexbuild 1) Get User Id';
1773 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1774 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1775 END IF;
1776
1777 user_id := to_number(FND_GLOBAL.USER_ID);
1778
1779 debug_info := '(PA Flexbuild 1) PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION';
1780 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1781 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1782 END IF;
1783
1784 --bugfix:5725904
1785 If (p_invoice_rec.invoice_type_lookup_code ='EXPENSE REPORT') Then
1786 l_sys_link_function :='ER' ;
1787 Else
1788 l_sys_link_function :='VI' ;
1789 End if;
1790
1791 PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION(
1792 X_PROJECT_ID => p_invoice_lines_rec.project_id,
1793 X_TASK_ID => p_invoice_lines_rec.task_id,
1794 X_EI_DATE => p_invoice_lines_rec.expenditure_item_date,
1795 X_EXPENDITURE_TYPE => p_invoice_lines_rec.expenditure_type,
1796 X_NON_LABOR_RESOURCE => NULL,
1797 X_PERSON_ID => p_employee_id,
1798 X_QUANTITY => '1',
1799 X_DENOM_CURRENCY_CODE=> p_invoice_rec.invoice_currency_code,
1800 X_ACCT_CURRENCY_CODE => p_base_currency_code,
1801 X_DENOM_RAW_COST => p_invoice_lines_rec.amount,
1802 X_ACCT_RAW_COST => p_invoice_lines_rec.base_amount,
1803 X_ACCT_RATE_TYPE => p_invoice_rec.exchange_rate_type,
1804 X_ACCT_RATE_DATE => p_invoice_rec.exchange_date,
1805 X_ACCT_EXCHANGE_RATE => p_invoice_rec.exchange_rate,
1806 X_TRANSFER_EI => null,
1807 X_INCURRED_BY_ORG_ID => p_invoice_lines_rec.expenditure_organization_id,
1808 X_NL_RESOURCE_ORG_ID => null,
1809 X_TRANSACTION_SOURCE => l_sys_link_function,--bug2853287 --bug:5725904
1810 X_CALLING_MODULE => 'APXIIMPT',
1811 X_VENDOR_ID => p_invoice_rec.vendor_id,
1812 X_ENTERED_BY_USER_ID => user_id,
1813 X_ATTRIBUTE_CATEGORY => p_invoice_lines_rec.attribute_category,
1814 X_ATTRIBUTE1 => p_invoice_lines_rec.attribute1,
1815 X_ATTRIBUTE2 => p_invoice_lines_rec.attribute2,
1816 X_ATTRIBUTE3 => p_invoice_lines_rec.attribute3,
1817 X_ATTRIBUTE4 => p_invoice_lines_rec.attribute4,
1818 X_ATTRIBUTE5 => p_invoice_lines_rec.attribute5,
1819 X_ATTRIBUTE6 => p_invoice_lines_rec.attribute6,
1820 X_ATTRIBUTE7 => p_invoice_lines_rec.attribute7,
1821 X_ATTRIBUTE8 => p_invoice_lines_rec.attribute8,
1822 X_ATTRIBUTE9 => p_invoice_lines_rec.attribute9,
1823 X_ATTRIBUTE10 => p_invoice_lines_rec.attribute10,
1824 X_ATTRIBUTE11 => p_invoice_lines_rec.attribute11,
1825 X_ATTRIBUTE12 => p_invoice_lines_rec.attribute12,
1826 X_ATTRIBUTE13 => p_invoice_lines_rec.attribute13,
1827 X_ATTRIBUTE14 => p_invoice_lines_rec.attribute14,
1828 X_ATTRIBUTE15 => p_invoice_lines_rec.attribute15,
1829 X_MSG_APPLICATION => l_msg_application, -- IN OUT
1830 X_MSG_TYPE => l_msg_type, -- OUT NOCOPY
1831 X_MSG_TOKEN1 => l_msg_token1, -- OUT NOCOPY
1832 X_MSG_TOKEN2 => l_msg_token2, -- OUT NOCOPY
1833 X_MSG_TOKEN3 => l_msg_token3, -- OUT NOCOPY
1834 X_MSG_COUNT => l_msg_count, -- OUT NOCOPY
1835 X_MSG_DATA => l_msg_data, -- OUT NOCOPY
1836 X_BILLABLE_FLAG => procedure_billable_flag , -- OUT NOCOPY
1837 P_Document_Type => p_invoice_rec.invoice_type_lookup_code,
1838 P_Document_Line_Type => p_invoice_lines_rec.line_type_lookup_code,
1839 P_SYS_LINK_FUNCTION => 'VI'); -- Added for bug2714409
1840
1841 IF (l_msg_data IS NOT NULL) THEN
1842 debug_info :=
1843 '(PA Flexbuild 1) PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION '||
1844 'Failed :Insert Rejection';
1845 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1846 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1847 END IF;
1848
1849 -- Bug 5214592 . Added the debug message.
1850 debug_info := SUBSTR(l_msg_data,1,80);
1851 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1852 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1853 END IF;
1854
1855
1856 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
1857 AP_IMPORT_INVOICES_PKG.g_invoice_lines_table,
1858 p_invoice_lines_rec.invoice_line_id,
1859 'PA FLEXBUILD FAILED',
1860 p_default_last_updated_by,
1861 p_default_last_update_login,
1862 current_calling_sequence) <> TRUE) THEN
1863 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1864 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1865 'insert_rejections<- '||current_calling_sequence);
1866 END IF;
1867 RAISE pa_flexbuild_failure;
1868 END IF;
1869
1870 l_current_invoice_status := 'N';
1871 p_current_invoice_status := l_current_invoice_status;
1872 RETURN (TRUE);
1873
1874 END IF; -- l_msg_data is not null
1875
1876 --------------------------------------------------------------------------
1877 -- Step 2 - Flexbuild
1878 --------------------------------------------------------------------------
1879
1880 debug_info := '(PA Flexbuild 2) Call for flexbuilding';
1881 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1882 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1883 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1884 '------------> '
1885 ||' P_CHART_OF_ACCOUNTS_ID = '||to_char(P_CHART_OF_ACCOUNTS_ID)
1886 ||' PROJECT_ID = '||to_char(P_invoice_lines_rec.PROJECT_ID)
1887 ||' TASK_ID = '||to_char(P_invoice_lines_rec.TASK_ID)
1888 ||' award_ID = '||to_char(P_invoice_lines_rec.AWARD_ID)
1889 ||' EXPENDITURE_TYPE = '||P_invoice_lines_rec.EXPENDITURE_TYPE
1890 ||' EXPENDITURE_ORGANIZATION_ID = '
1891 ||to_char(P_invoice_lines_rec.EXPENDITURE_ORGANIZATION_ID)
1892 ||' VENDOR_ID = '||to_char(P_invoice_rec.VENDOR_ID)
1893 ||' procedure_billable_flag= '||procedure_billable_flag);
1894 END IF;
1895
1896 -- Flexbuild using Workflow.
1897
1898 debug_info :=
1899 '(PA Flexbuild 2) Call pa_acc_gen_wf_pkg.ap_inv_generate_account '||
1900 'for flexbuilding';
1901 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1902 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1903 END IF;
1904
1905 IF ( NOT pa_acc_gen_wf_pkg.ap_inv_generate_account (
1906 p_project_id => p_invoice_lines_rec.project_id,
1907 p_task_id => p_invoice_lines_rec.task_id,
1908 X_AWARD_SET_ID => p_invoice_lines_rec.award_id,
1909 p_expenditure_type => p_invoice_lines_rec.expenditure_type,
1910 p_vendor_id => P_invoice_rec.VENDOR_ID,
1911 p_expenditure_organization_id =>
1912 P_invoice_lines_rec.EXPENDITURE_ORGANIZATION_ID,
1913 p_expenditure_item_date =>
1914 P_invoice_lines_rec.EXPENDITURE_ITEM_DATE,
1915 p_billable_flag => procedure_billable_flag,
1916 p_chart_of_accounts_id => P_CHART_OF_ACCOUNTS_ID,
1917 p_accounting_date => P_ACCOUNTING_DATE,
1918 P_ATTRIBUTE_CATEGORY => P_invoice_rec.ATTRIBUTE_CATEGORY,
1919 P_ATTRIBUTE1 => P_invoice_rec.ATTRIBUTE1,
1920 P_ATTRIBUTE2 => P_invoice_rec.ATTRIBUTE2,
1921 P_ATTRIBUTE3 => P_invoice_rec.ATTRIBUTE3,
1922 P_ATTRIBUTE4 => P_invoice_rec.ATTRIBUTE4,
1923 P_ATTRIBUTE5 => P_invoice_rec.ATTRIBUTE5,
1924 P_ATTRIBUTE6 => P_invoice_rec.ATTRIBUTE6,
1925 P_ATTRIBUTE7 => P_invoice_rec.ATTRIBUTE7,
1926 P_ATTRIBUTE8 => P_invoice_rec.ATTRIBUTE8,
1927 P_ATTRIBUTE9 => P_invoice_rec.ATTRIBUTE9,
1928 P_ATTRIBUTE10 => P_invoice_rec.ATTRIBUTE10,
1929 P_ATTRIBUTE11 => P_invoice_rec.ATTRIBUTE11,
1930 P_ATTRIBUTE12 => P_invoice_rec.ATTRIBUTE12,
1931 P_ATTRIBUTE13 => P_invoice_rec.ATTRIBUTE13,
1932 P_ATTRIBUTE14 => P_invoice_rec.ATTRIBUTE14,
1933 P_ATTRIBUTE15 => P_invoice_rec.ATTRIBUTE15,
1934 P_DIST_ATTRIBUTE_CATEGORY => p_invoice_lines_rec.attribute_category,
1935 P_DIST_ATTRIBUTE1 => p_invoice_lines_rec.attribute1,
1936 P_DIST_ATTRIBUTE2 => p_invoice_lines_rec.attribute2,
1937 P_DIST_ATTRIBUTE3 => p_invoice_lines_rec.attribute3,
1938 P_DIST_ATTRIBUTE4 => p_invoice_lines_rec.attribute4,
1939 P_DIST_ATTRIBUTE5 => p_invoice_lines_rec.attribute5,
1940 P_DIST_ATTRIBUTE6 => p_invoice_lines_rec.attribute6,
1941 P_DIST_ATTRIBUTE7 => p_invoice_lines_rec.attribute7,
1942 P_DIST_ATTRIBUTE8 => p_invoice_lines_rec.attribute8,
1943 P_DIST_ATTRIBUTE9 => p_invoice_lines_rec.attribute9,
1944 P_DIST_ATTRIBUTE10 => p_invoice_lines_rec.attribute10,
1945 P_DIST_ATTRIBUTE11 => p_invoice_lines_rec.attribute11,
1946 P_DIST_ATTRIBUTE12 => p_invoice_lines_rec.attribute12,
1947 P_DIST_ATTRIBUTE13 => p_invoice_lines_rec.attribute13,
1948 P_DIST_ATTRIBUTE14 => p_invoice_lines_rec.attribute14,
1949 P_DIST_ATTRIBUTE15 => p_invoice_lines_rec.attribute15,
1950 x_return_ccid => P_PA_DEFAULT_DIST_CCID, --OUT
1951 x_concat_segs => l_concat_segs, -- OUT NOCOPY
1952 x_concat_ids => l_concat_ids, -- OUT NOCOPY
1953 x_concat_descrs => l_concat_descrs, -- OUT NOCOPY
1954 x_error_message => l_errmsg)) THEN -- OUT NOCOPY
1955
1956 -- Show error message
1957
1958 -- CHANGES FOR BUG - 3657665 ** STARTS **
1959 -- Need to encode the message and then print the value for the same returned by PA.
1960 fnd_message.set_encoded(l_errmsg);
1961 l_errmsg := fnd_message.get;
1962 -- CHANGES FOR BUG - 3657665 ** ENDS **
1963
1964 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1965 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1966 '------------> l_errmsg '|| l_errmsg);
1967 END IF;
1968
1969 -- REJECT here
1970
1971 debug_info :=
1972 '(PA Flexbuild 2) pa_acc_gen_wf_pkg.ap_inv_generate_account '||
1973 'Failed :Insert Rejection';
1974 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1975 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1976 END IF;
1977
1978 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
1979 AP_IMPORT_INVOICES_PKG.g_invoice_lines_table,
1980 p_invoice_lines_rec.invoice_line_id,
1981 'PA FLEXBUILD FAILED',
1982 p_default_last_updated_by,
1983 p_default_last_update_login,
1984 current_calling_sequence) <> TRUE) THEN
1985
1986 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1987 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1988 'insert_rejections<- '||current_calling_sequence);
1989 END IF;
1990 RAISE pa_flexbuild_failure;
1991 END IF;
1992
1993 l_current_invoice_status := 'N';
1994 P_PA_CONCATENATED_SEGMENTS := l_concat_segs;
1995 p_current_invoice_status := l_current_invoice_status;
1996
1997 RETURN (TRUE);
1998
1999 END IF; -- If not pa generate account
2000
2001 debug_info := '(PA Flexbuild 2) Return Concatenated Segments';
2002 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2003 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2004 END IF;
2005
2006 P_PA_CONCATENATED_SEGMENTS := l_concat_segs;
2007 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2008 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
2009 '------------> p_pa_default_dist_ccid = '
2010 || to_char(p_pa_default_dist_ccid)
2011 ||' p_pa_concatenated_segments = '||p_pa_concatenated_segments
2012 ||' l_concat_segs = '||l_concat_segs
2013 ||' l_concat_ids = '||l_concat_ids
2014 ||' procedure_billable_flag = '||procedure_billable_flag
2015 ||' l_concat_descrs = '||l_concat_descrs
2016 ||' l_errmsg = '||l_errmsg);
2017 END IF;
2018 END IF; -- pa installed and project id is not null
2019
2020 debug_info := '(PA Flexbuild 3) Return Invoice Status';
2021 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2022 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2023 END IF;
2024
2025 p_current_invoice_status := l_current_invoice_status;
2026 RETURN(TRUE);
2027
2028 EXCEPTION
2029 WHEN OTHERS THEN
2030 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2031 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2032 END IF;
2033 IF (SQLCODE < 0) THEN
2034 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2035 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, SQLERRM);
2036 END IF;
2037 END IF;
2038
2039 RETURN (FALSE);
2040 END pa_flexbuild;
2041
2042 /*==========================================================================
2043 Private Function: Get Document Sequence
2044 Note: Method has to be automatic!
2045 Mode 1: Simple Manual Entry without Audit
2046 (Use Voucher Num, Seq Num "Not Used")
2047 Mode 3: Auto voucher numbering with Audit
2048 (Use doc_sequence_value, Seq Num 'P','A'))
2049 Mode 3 will override Mode 1
2050 Mode 2 Audited Manual Entry is not supported
2051
2052 The following is a brief description of the implementation of Document
2053 Sequential Numbering in Invoice Open Interface (R11 only)
2054
2055 The two modes for numbering can be:
2056 - Simple Manual Entry without Audit: Any value entered in the column
2057 AP_INVOICES_INTERFACE.VOUCHER_NUM will be inserted in AP_INVOICES.
2058 VOUCHER_NUM without validation.
2059
2060 - Auto Voucher Numbering with Audit: A value will be obtained
2061 automatically for the record being imported and will be populated in
2062 AP_INVOICES. DOC_SEQUENCE_VALUE. Also audit information would be inserted
2063 into the audit table.
2064
2065 The latter mode will always override the first one.
2066
2067 The logic for the five new rejections is as follows:
2068 - 'Category not needed' - 'Document sequential numbering is not used'.
2069 - 'Invalid Category' - 'Document category specified is not valid'.
2070 - 'Override Disabled' - 'Document Category Override Payables option
2071 is disabled'
2072 - 'Invalid Assignment' - 'Invalid sequence assigned to specified document
2073 category'
2074 - 'Invalid Sequence' - 'Could not retrieve document sequence value from
2075 the given sequence'
2076
2077 If the profile value for the "Sequential Numbering" option is "Not Used"
2078 and the user specifies a document category then the invoice would be
2079 rejected for 'Category not needed'.
2080
2081 If the profile value is "Partial" or "Always" and
2082 the payables option of Invoice Document Category override is
2083 "Yes" then the user can specify the document category, else the
2084 invoice will be rejected for 'Override Disabled', if the user populates
2085 AP_INVOICES_INTERFACE.DOC_CATEGORY_CODE (and override is "No").
2086
2087 If the profile value is "Always" and no document category is specified
2088 by the user, then "Standard Invoices" category will be used for
2089 standard invoices and "Credit Memo Invoices" category will be used
2090 for credits.
2091 We assume that a valid automatic sequence exists for such categories.
2092
2093 If the payables option of Invoice Document Category override is
2094 "Yes" and the user specifies any of the following categories then
2095 the invoice is rejected for 'Invalid Category'.
2096
2097 ('INT INV',
2098 'MIX INV',
2099 'DBM INV',
2100 'CHECK PAY',
2101 'CLEAR PAY',
2102 'EFT PAY',
2103 'FUTURE PAY',
2104 'MAN FUTURE PAY',
2105 'PREPAY INV',
2106 'REC INV',
2107 'WIRE PAY',
2108 'EXP REP INV')
2109
2110 If the document category is "Standard Invoices" and the invoice amount
2111 is less than zero, or, the document category is "Credit Memo Invoices"
2112 and the invoice amount is greated than zero then the invoice will be
2113 rejected for 'Invalid Category'.
2114
2115 The document category specified should be valid in
2116 FND_DOC_SEQUENCE_CATEGORIES for AP_INVOICES or AP_INVOICES_ALL
2117 table. If not then the invoice will be rejected for 'Invalid Category'.
2118
2119 If the document category is valid then Check the status of the
2120 sequence assigned to this category.The sequence should be automatic
2121 and active. If not then reject for 'Invalid Assignment'.
2122
2123 If the sequence is valid then get the next value for the assigned
2124 sequence. If there is an error in retrieving the nextval then reject
2125 for 'Invalid Sequence'. This should not happen in the ideal scenario.
2126 ============================================================================*/
2127
2128 FUNCTION get_doc_sequence(
2129 p_invoice_rec IN OUT
2130 AP_IMPORT_INVOICES_PKG.r_invoice_info_rec,
2131 p_inv_doc_cat_override IN VARCHAR2,
2132 p_set_of_books_id IN NUMBER,
2133 p_sequence_numbering IN VARCHAR2,
2134 p_default_last_updated_by IN NUMBER,
2135 p_default_last_update_login IN NUMBER,
2136 p_db_sequence_value OUT NOCOPY NUMBER,
2137 p_db_seq_name OUT NOCOPY VARCHAR2,
2138 p_db_sequence_id OUT NOCOPY NUMBER,
2139 p_current_invoice_status OUT NOCOPY VARCHAR2,
2140 p_calling_sequence IN VARCHAR2)
2141 RETURN BOOLEAN
2142 IS
2143 get_doc_seq_failure EXCEPTION;
2144 l_name VARCHAR2(80);
2145 l_doc_category_code
2146 ap_invoices.doc_category_code%TYPE := p_invoice_rec.doc_category_code;
2147 l_application_id NUMBER;
2148 l_doc_seq_ass_id NUMBER;
2149 l_current_invoice_status VARCHAR2(1) := 'Y';
2150 current_calling_sequence VARCHAR2(2000);
2151 debug_info VARCHAR2(500);
2152 l_return_code NUMBER;
2153
2154 BEGIN
2155 -- Update the calling sequence
2156
2157 current_calling_sequence := 'get_doc_sequence<-'||P_calling_sequence;
2158
2159 IF ((p_sequence_numbering = 'N') AND
2160 (p_invoice_rec.doc_category_code IS NOT NULL)) THEN
2161 --------------------------------------------------------------------------
2162 -- Step 1
2163 -- p_sequence_numbering should be in ('A','P')
2164 -- Do not use seq num if N (Not Used)
2165 -- Reject if Doc category provided is provided by user in this case.
2166 --------------------------------------------------------------------------
2167
2168 debug_info := '(Get Doc Sequence 1) Reject Seq Num is not enabled ';
2169 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2170 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2171 END IF;
2172
2173 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
2174 'AP_INVOICE_INTERFACE',
2175 p_invoice_rec.invoice_id,
2176 'DOC CAT NOT REQD',
2177 p_default_last_updated_by,
2178 p_default_last_update_login,
2179 current_calling_sequence) <> TRUE) THEN
2180 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2181 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch,
2182 'insert_rejections<- '||current_calling_sequence);
2183 END IF;
2184 RAISE get_doc_seq_failure;
2185 l_current_invoice_status := 'N';
2186 p_current_invoice_status := l_current_invoice_status;
2187 END IF;
2188 RETURN (TRUE);
2189
2190 ELSIF (p_sequence_numbering IN ('A','P')) THEN
2191
2192 -------------------------------------------------------------------------
2193 -- Step 2
2194 -- Seq Numbering is enabled process doc category
2195 -------------------------------------------------------------------------
2196 IF (p_invoice_rec.doc_category_code IS NOT NULL) THEN
2197 debug_info := '(Get Doc Sequence 2) Seq Numbering is enabled AND doc_cat'
2198 || ' is not null process doc category ';
2199 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2200 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2201 END IF;
2202
2203 IF (p_inv_doc_cat_override = 'Y') THEN
2204 ---------------------------------------------------------------------
2205 -- Step 2.1
2206 -- Doc Category Override is allowed
2207 ---------------------------------------------------------------------
2208 debug_info := '(Get Doc Sequence 2.1) Doc Category Override allowed';
2209 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2210 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2211 END IF;
2212
2213 -- Reject if category is a seeded one and not allowed in this case
2214
2215 --Bug: 4410499, Added the support for EXP REP INV doc category type
2216
2217 -- Contract Payments: Modified the below IF condition to add logic for
2218 -- 'Prepayment' type invoices.
2219
2220 IF ( ( p_invoice_rec.doc_category_code = 'STD INV' and
2221 p_invoice_rec.invoice_type_lookup_code <> 'STANDARD')
2222 OR
2223 ( p_invoice_rec.doc_category_code = 'PAY REQ INV' and
2224 p_invoice_rec.invoice_type_lookup_code <> 'PAYMENT REQUEST')
2225 OR
2226 ( p_invoice_rec.doc_category_code = 'CRM INV' and
2227 p_invoice_rec.invoice_type_lookup_code <> 'CREDIT')
2228 -- Bug 7299826: Added support for Debit Memos
2229 OR
2230 ( p_invoice_rec.doc_category_code = 'DBM INV' and
2231 p_invoice_rec.invoice_type_lookup_code <> 'DEBIT')
2232 OR
2233 ( p_invoice_rec.doc_category_code = 'PREPAY INV' and
2234 p_invoice_rec.invoice_type_lookup_code <> 'PREPAYMENT')
2235 OR
2236 ( p_invoice_rec.doc_category_code = 'EXP REP INV' and
2237 p_invoice_rec.invoice_type_lookup_code <> 'EXPENSE REPORT')
2238
2239 OR
2240 ( p_invoice_rec.doc_category_code IN (
2241 'INT INV',
2242 'MIX INV',
2243 --'DBM INV', -- bug 7299826
2244 'CHECK PAY',
2245 'CLEAR PAY',
2246 'EFT PAY',
2247 'FUTURE PAY',
2248 'MAN FUTURE PAY',
2249 'PREPAY INV',
2250 'REC INV',
2251 'WIRE PAY'))) THEN
2252
2253 debug_info := '(Get Doc Sequence 2.1) Reject->category seeded one';
2254 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2255 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2256 END IF;
2257
2258 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
2259 AP_IMPORT_INVOICES_PKG.g_invoices_table,
2260 p_invoice_rec.invoice_id,
2261 'INVALID DOC CATEGORY',
2262 p_default_last_updated_by,
2263 p_default_last_update_login,
2264 current_calling_sequence) <> TRUE) THEN
2265
2266 debug_info := 'insert_rejections<- '||current_calling_sequence;
2267 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2268 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2269 END IF;
2270 RAISE get_doc_seq_failure;
2271 END IF;
2272 l_current_invoice_status := 'N';
2273 END IF; -- end of seeded category check
2274
2275 -----------------------------------------------------------------------
2276 -- Step 2.2
2277 -- Validate Doc Category
2278 -----------------------------------------------------------------------
2279 debug_info := '(Get Doc Sequence 2.2) Check Doc Category ' ||
2280 'exists and valid';
2281 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2282 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2283 END IF;
2284
2285 BEGIN
2286 SELECT name, application_id
2287 INTO l_name, l_application_id
2288 FROM fnd_doc_sequence_categories
2289 WHERE code = p_invoice_rec.doc_category_code
2290 AND table_name IN ('AP_INVOICES','AP_INVOICES_ALL');
2291 EXCEPTION
2292 WHEN NO_DATA_FOUND THEN
2293 debug_info := debug_info || 'Reject->Doc cat does not exist';
2294 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2295 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2296 END IF;
2297 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
2298 AP_IMPORT_INVOICES_PKG.g_invoices_table,
2299 p_invoice_rec.invoice_id,
2300 'INVALID DOC CATEGORY',
2301 p_default_last_updated_by,
2302 p_default_last_update_login,
2303 current_calling_sequence) <> TRUE) THEN
2304
2305 debug_info := 'insert_rejections<- '||current_calling_sequence;
2306 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2307 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2308 END IF;
2309 RAISE get_doc_seq_failure;
2310 END IF;
2311 l_current_invoice_status := 'N';
2312 END;
2313 ELSE -- override is no
2314 -----------------------------------------------------------------------
2315 -- Step 3
2316 -- override <> 'Y'
2317 -- Reject Override not allowed
2318 -----------------------------------------------------------------------
2319
2320 debug_info := '(Get Doc Sequence 3) Reject->cat override not allowed';
2321 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2322 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2323 END IF;
2324
2325 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
2326 AP_IMPORT_INVOICES_PKG.g_invoices_table,
2327 p_invoice_rec.invoice_id,
2328 'OVERRIDE DISALLOWED',
2329 p_default_last_updated_by,
2330 p_default_last_update_login,
2331 current_calling_sequence) <> TRUE) THEN
2332 debug_info := 'insert_rejections<- '||current_calling_sequence;
2333 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2334 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2335 END IF;
2336 RAISE get_doc_seq_failure;
2337 END IF;
2338 l_current_invoice_status := 'N';
2339 END IF; -- end of check l_doc_cat_override = 'Y'
2340 ELSIF ( (p_invoice_rec.doc_category_code IS NULL) AND
2341 (p_sequence_numbering = 'A')) THEN
2342 ---------------------------------------------------------------------
2343 -- Step 4
2344 -- Use Default Doc Category
2345 ---------------------------------------------------------------------
2346 debug_info := '(Get Doc Sequence 4) Use Default Category, Seq:Always';
2347 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2348 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2349 END IF;
2350
2351 --Contract Payments: Modified the IF condition to look at the invoice_type
2352 --rather than the sign of the invoice_amount in deciding which category to
2353 --apply, and also added the logic for 'PREPAYMENT' invoices.
2354
2355 IF (p_invoice_rec.invoice_type_lookup_code = 'STANDARD') THEN
2356 l_doc_category_code := 'STD INV';
2357 ELSIF (p_invoice_rec.invoice_type_lookup_code = 'PAYMENT REQUEST') THEN
2358 l_doc_category_code := 'PAY REQ INV';
2359 ELSIF (p_invoice_rec.invoice_type_lookup_code = 'CREDIT') THEN
2360 l_doc_category_code := 'CRM INV';
2361 -- Bug 7299826
2362 ELSIF (p_invoice_rec.invoice_type_lookup_code = 'DEBIT') THEN
2363 l_doc_category_code := 'DBM INV';
2364 -- Bug 7299826 End
2365 ELSIF (p_invoice_rec.invoice_type_lookup_code = 'PREPAYMENT') THEN
2366 l_doc_category_code := 'PREPAY INV';
2367 END IF;
2368
2369 debug_info := '-----> l_doc_category_code = ' || l_doc_category_code ;
2370 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2371 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2372 END IF;
2373 END IF; -- end of check Doc_category_code is not null
2374
2375 ---------------------------------------------------------------------------
2376 -- Step 5
2377 -- Get Doc Sequence Number
2378 ---------------------------------------------------------------------------
2379
2380 IF ((l_doc_category_code IS NOT NULL) AND
2381 (l_current_invoice_status = 'Y')) THEN
2382
2383 debug_info := '(Get Doc Sequence 5) Valid Category ->Check if valid ' ||
2384 ' Sequence assigned';
2385 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2386 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2387 END IF;
2388
2389 BEGIN
2390 SELECT SEQ.DB_SEQUENCE_NAME,
2391 SEQ.DOC_SEQUENCE_ID,
2392 SA.doc_sequence_assignment_id
2393 INTO p_db_seq_name,
2394 p_db_sequence_id ,
2395 l_doc_seq_ass_id
2396 FROM FND_DOCUMENT_SEQUENCES SEQ,
2397 FND_DOC_SEQUENCE_ASSIGNMENTS SA
2398 WHERE SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
2399 AND SA.APPLICATION_ID = 200
2400 AND SA.CATEGORY_CODE = l_doc_category_code
2401 AND NVL(SA.METHOD_CODE,'A') = 'A'
2402 AND NVL(SA.SET_OF_BOOKS_ID,
2403 p_set_of_books_id) = p_set_of_books_id -- 3817492
2404 AND NVL(p_invoice_rec.gl_date,
2405 AP_IMPORT_INVOICES_PKG.g_inv_sysdate) between
2406 SA.START_DATE and
2407 NVL(SA.END_DATE, TO_DATE('31/12/4712','DD/MM/YYYY'));
2408
2409 -- Bug 5064959 starts. Check for inconsistent Voucher info. When a valid sequence exists ,
2410 -- user should not manually enter the voucher number.
2411
2412 If (p_invoice_rec.voucher_num IS NOT NULL) Then
2413
2414 debug_info := '(Get Doc Sequence 5) Reject: Inconsistent Voucher Info';
2415 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2416 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2417 END IF;
2418
2419 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections( AP_IMPORT_INVOICES_PKG.g_invoices_table,
2420 p_invoice_rec.invoice_id,
2421 'INCONSISTENT VOUCHER INFO',
2422 p_default_last_updated_by,
2423 p_default_last_update_login,
2424 current_calling_sequence) <> TRUE) THEN
2425 debug_info := 'insert_rejections<- '||current_calling_sequence;
2426 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2427 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2428 END IF;
2429 RAISE get_doc_seq_failure;
2430
2431 END IF;
2432
2433 l_current_invoice_status := 'N';
2434
2435 End If;
2436
2437 -- Bug 5064959 ends.
2438
2439 EXCEPTION
2440 WHEN NO_DATA_FOUND Then
2441
2442 --bug5854731 starts.Added the below If clause
2443 --Only if the Sequenctial numbering option is 'Always Used',we raise the error.
2444 IF(p_sequence_numbering='A') THEN --bug5854731.Only if the Sequenctial numbering op
2445 debug_info := '(Get Doc Sequence 5) Reject:Invalid Sequence' ||
2446 'assignment';
2447 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2448 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2449 END IF;
2450
2451 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
2452 AP_IMPORT_INVOICES_PKG.g_invoices_table,
2453 p_invoice_rec.invoice_id,
2454 'INVALID ASSIGNMENT',
2455 p_default_last_updated_by,
2456 p_default_last_update_login,
2457 current_calling_sequence) <> TRUE) THEN
2458 debug_info := 'insert_rejections<- '||current_calling_sequence;
2459 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2460 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2461 END IF;
2462 RAISE get_doc_seq_failure;
2463 END IF;
2464 l_current_invoice_status := 'N';
2465 END IF; --end of p_sequence_numbering='A' bug5854731 ends
2466 END; -- end of the above BEGION
2467
2468
2469 IF (l_current_invoice_status = 'Y'
2470 and p_db_sequence_id is NOT NULL) THEN --bug5854731.Added the AND clause.
2471 --Only if the sequence_id fetched from the step5 is not null,
2472 --we proceed forward to get the sequence value.
2473
2474 ----------------------------------------------------------------------
2475 -- Step 6
2476 -- Get Doc Sequence Val
2477 ----------------------------------------------------------------------
2478 debug_info := '(Get Doc Sequence 6) Get Next Val';
2479 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2480 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2481 END IF;
2482
2483 l_return_code := FND_SEQNUM.GET_SEQ_VAL(
2484 200,
2485 l_doc_category_code,
2486 p_set_of_books_id,
2487 'A',
2488 NVL(p_invoice_rec.gl_date,
2489 AP_IMPORT_INVOICES_PKG.g_inv_sysdate),
2490 p_db_sequence_value,
2491 p_db_sequence_id ,
2492 'N',
2493 'N');
2494 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2495 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
2496 '-----------> l_doc_category_code = '|| l_doc_category_code
2497 || ' p_set_of_books_id = '||to_char(p_set_of_books_id)
2498 || ' p_db_sequence_id = '||to_char(p_db_sequence_id )
2499 ||' p_db_seq_name = '||p_db_seq_name
2500 ||' p_db_sequence_value = '||to_char(p_db_sequence_value));
2501 END IF;
2502
2503 IF ((p_db_sequence_value IS NULL) or (l_return_code <> 0)) THEN
2504 debug_info := '(Get Doc Sequence 7) Reject:Invalid Sequence';
2505 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2506 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2507 END IF;
2508
2509 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
2510 AP_IMPORT_INVOICES_PKG.g_invoices_table,
2511 p_invoice_rec.invoice_id,
2512 'INVALID SEQUENCE',
2513 p_default_last_updated_by,
2514 p_default_last_update_login,
2515 current_calling_sequence) <> TRUE) THEN
2516 debug_info := 'insert_rejections<- '||current_calling_sequence;
2517 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2518 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2519 END IF;
2520 RAISE get_doc_seq_failure;
2521 END IF;
2522 l_current_invoice_status := 'N';
2523 END IF; -- end of check l_return_code and seqval
2524 END IF; -- end of check l_current_invoice_status = 'Y' for step 6
2525 END IF; -- end of check l_current_invoice_status/doc_category_code
2526
2527 -- Bug 5064959 starts. The validation for seq value should be done if the profile value is 'A' or 'P'.
2528
2529 --Bug 7214515/7261280 Uncommented the code changes done in 6492341 and only commented
2530 -- length check condition
2531 -- Bug 6492431 The code is commented to remove the 9 digit restriction on doc_sequnce_number.
2532 -- if ( ( LENGTH( nvl(p_db_sequence_value,0)) > 9 ) or --Condition value changed from 8 to 9 for BUG 5950643
2533 If ( TRANSLATE( p_db_sequence_value ,'x1234567890','x') IS NOT NULL) then
2534
2535 debug_info := '(Get Doc Sequence 8) Reject: Invalid Voucher Number';
2536 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2537 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2538 END IF;
2539
2540 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections( AP_IMPORT_INVOICES_PKG.g_invoices_table,
2541 p_invoice_rec.invoice_id,
2542 'INCONSISTENT VOUCHER INFO',
2543 p_default_last_updated_by,
2544 p_default_last_update_login,
2545 current_calling_sequence) <> TRUE) THEN
2546 debug_info := 'insert_rejections<- '||current_calling_sequence;
2547 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2548 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2549 END IF;
2550 RAISE get_doc_seq_failure;
2551
2552 END IF;
2553 l_current_invoice_status := 'N';
2554 END IF;
2555
2556 -- Bug 5064959 ends.
2557
2558 END IF; -- p_sequence_numbering = 'N'
2559
2560 p_invoice_rec.doc_category_code := l_doc_category_code;
2561 p_current_invoice_status := l_current_invoice_status;
2562
2563 RETURN(TRUE);
2564
2565 EXCEPTION
2566 WHEN OTHERS THEN
2567
2568 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2569 Print(
2570 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2571 END IF;
2572
2573 IF (SQLCODE < 0) THEN
2574 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2575 Print(
2576 AP_IMPORT_INVOICES_PKG.g_debug_switch, SQLERRM);
2577 END IF;
2578 END IF;
2579
2580 RETURN (FALSE);
2581 END get_doc_sequence;
2582
2583 /*===================================================================
2584 Private function: get_invoice_info
2585 Get some values for creating invoices from po_vendors,
2586 po_headers
2587 =================================================================== */
2588
2589 FUNCTION get_invoice_info(
2590 p_invoice_rec IN OUT NOCOPY
2591 AP_IMPORT_INVOICES_PKG.r_invoice_info_rec,
2592 p_default_last_updated_by IN NUMBER,
2593 p_default_last_update_login IN NUMBER,
2594 p_pay_curr_invoice_amount OUT NOCOPY NUMBER,
2595 p_payment_priority OUT NOCOPY NUMBER,
2596 p_invoice_amount_limit OUT NOCOPY NUMBER,
2597 p_hold_future_payments_flag OUT NOCOPY VARCHAR2,
2598 p_supplier_hold_reason OUT NOCOPY VARCHAR2,
2599 p_exclude_freight_from_disc OUT NOCOPY VARCHAR2, /* bug 4931755 */
2600 p_calling_sequence IN VARCHAR2)
2601 RETURN BOOLEAN
2602 IS
2603 get_invoice_info_failure EXCEPTION;
2604 debug_info VARCHAR2(500);
2605 current_calling_sequence VARCHAR2(2000);
2606 BEGIN
2607 -- Update the calling sequence
2608
2609 current_calling_sequence := 'get_invoice_info->'||P_calling_sequence;
2610
2611 ----------------------------------------------------------------------------
2612 -- Step 1
2613 -- Calculate the invoice amount in payment currency
2614 ----------------------------------------------------------------------------
2615
2616 debug_info := '(Get Invoice Info step 1) Calculate invoice amount in ' ||
2617 'payment currency ';
2618 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2619 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2620 END IF;
2621
2622 IF ( p_invoice_rec.payment_cross_rate is NOT NULL) THEN
2623 p_pay_curr_invoice_amount := gl_currency_api.convert_amount(
2624 p_invoice_rec.invoice_currency_code,
2625 p_invoice_rec.payment_currency_code,
2626 p_invoice_rec.payment_cross_rate_date,
2627 p_invoice_rec.payment_cross_rate_type,
2628 p_invoice_rec.invoice_amount);
2629
2630 END IF;
2631
2632 -----------------------------------------------------------------------------
2633 -- Step 2
2634 -- Get amount_applicable_to_discount
2635 -----------------------------------------------------------------------------
2636
2637 debug_info := '(Get Invoice Info step 2) Get amt_applicable_to_discount ' ||
2638 ' value if not given';
2639 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2640 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2641 END IF;
2642
2643 p_invoice_rec.amount_applicable_to_discount :=
2644 NVL(p_invoice_rec.amount_applicable_to_discount,
2645 p_invoice_rec.invoice_amount);
2646
2647 -----------------------------------------------------------------------------
2648 -- Step 3
2649 -- Get information from supplier site if null in invoice record or never
2650 -- read:
2651 -- payment_method_lookup_code into invoice rec
2652 -- pay_group_lookup_code into invoice rec
2653 -- accts_pay_code_combination_id into invoice rec
2654 -- payment_priority into OUT parameter
2655 -- invoice_amount_limit into OUT parameter
2656 -- hold_future_payments_flag into OUT parameter
2657 -- hold_reason into OUT parameter
2658 -----------------------------------------------------------------------------
2659
2660 -- Payment Requests: Added the if condition for payment requests type invoices
2661 IF (p_invoice_rec.invoice_type_lookup_code = 'PAYMENT REQUEST') THEN
2662
2663 debug_info := '(Get Invoice Info step 3) Get payment default info ';
2664 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2665 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2666 END IF;
2667 BEGIN
2668
2669 SELECT DECODE(p_invoice_rec.pay_group_lookup_code,
2670 NULL,asp.vendor_pay_group_lookup_code,
2671 p_invoice_rec.pay_group_lookup_code),
2672 DECODE(p_invoice_rec.accts_pay_code_combination_id, Null,
2673 fsp.accts_pay_code_combination_id,
2674 p_invoice_rec.accts_pay_code_combination_id),
2675 p_invoice_rec.payment_priority,
2676 NULL, --invoice_amount_limit,
2677 'N', --hold_future_payments_flag,
2678 NULL, --hold_reason
2679 'N' -- exclude_freight_from_discount.bug 4931755
2680 INTO p_invoice_rec.pay_group_lookup_code,
2681 p_invoice_rec.accts_pay_code_combination_id,
2682 p_payment_priority,
2683 p_invoice_amount_limit,
2684 p_hold_future_payments_flag,
2685 p_supplier_hold_reason,
2686 p_exclude_freight_from_disc
2687 FROM ap_system_parameters asp,
2688 financials_system_parameters fsp
2689 WHERE asp.org_id = p_invoice_rec.org_id
2690 AND asp.org_id = fsp.org_id;
2691 EXCEPTION
2692 WHEN no_data_found THEN
2693 debug_info := debug_info || '->no data found in query';
2694 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2695 Print(
2696 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2697 debug_info);
2698 END IF;
2699 RAISE get_invoice_info_failure;
2700 END;
2701
2702 ELSE
2703
2704 debug_info := '(Get Invoice Info step 3) Get supplier site default info ';
2705 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2706 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2707 END IF;
2708 BEGIN
2709
2710 SELECT DECODE(p_invoice_rec.pay_group_lookup_code,
2711 NULL,pay_group_lookup_code,
2712 p_invoice_rec.pay_group_lookup_code),
2713 DECODE(p_invoice_rec.accts_pay_code_combination_id, Null,
2714 accts_pay_code_combination_id,
2715 p_invoice_rec.accts_pay_code_combination_id),
2716 payment_priority,
2717 invoice_amount_limit,
2718 hold_future_payments_flag,
2719 hold_reason,
2720 NVL(exclude_freight_from_discount, 'N') /*bug 4931755 */
2721 INTO p_invoice_rec.pay_group_lookup_code,
2722 p_invoice_rec.accts_pay_code_combination_id,
2723 p_payment_priority,
2724 p_invoice_amount_limit,
2725 p_hold_future_payments_flag,
2726 p_supplier_hold_reason,
2727 p_exclude_freight_from_disc
2728 FROM ap_supplier_sites_all
2729 WHERE vendor_id = p_invoice_rec.vendor_id
2730 AND vendor_site_id = p_invoice_rec.vendor_site_id;
2731 EXCEPTION
2732 WHEN no_data_found THEN
2733 debug_info := debug_info || '->no data found in query';
2734 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2735 Print(
2736 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2737 debug_info);
2738 END IF;
2739 RAISE get_invoice_info_failure;
2740 END;
2741 END IF;
2742
2743 -----------------------------------------------------------------------------
2744 -- Step 4
2745 -- Populate who columns if null
2746 -----------------------------------------------------------------------------
2747 debug_info := '(Get Invoice Info step 4) Get WHO columns ';
2748 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2749 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2750 END IF;
2751 p_invoice_rec.last_updated_by :=
2752 NVL(p_invoice_rec.last_updated_by,p_default_last_updated_by);
2753 p_invoice_rec.last_update_login :=
2754 NVL(p_invoice_rec.last_update_login,NVL(p_default_last_update_login,
2755 p_default_last_updated_by));
2756 p_invoice_rec.created_by :=
2757 NVL(p_invoice_rec.created_by,p_default_last_updated_by);
2758 p_invoice_rec.creation_date :=
2759 NVL(p_invoice_rec.creation_date, AP_IMPORT_INVOICES_PKG.g_inv_sysdate);
2760 p_invoice_rec.last_update_date :=
2761 NVL(p_invoice_rec.last_update_date, AP_IMPORT_INVOICES_PKG.g_inv_sysdate);
2762
2763 RETURN(TRUE);
2764 EXCEPTION
2765 WHEN OTHERS then
2766 debug_info := debug_info || '->exception';
2767 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2768 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2769 END IF;
2770
2771 IF (SQLCODE < 0) then
2772 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2773 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, SQLERRM);
2774 END IF;
2775 END IF;
2776
2777 RETURN (FALSE);
2778
2779 END get_invoice_info;
2780
2781 /*=========================================================================
2782
2783 Function Insert_Ap_Invoices
2784 Program Flow:
2785
2786 =========================================================================*/
2787 -- Payment Request: Added p_needs_invoice_approval for payment request invoices
2788 FUNCTION insert_ap_invoices(
2789 p_invoice_rec IN OUT
2790 AP_IMPORT_INVOICES_PKG.r_invoice_info_rec,
2791 p_base_invoice_id OUT NOCOPY NUMBER,
2792 p_set_of_books_id IN NUMBER,
2793 p_doc_sequence_id IN
2794 AP_INVOICES.doc_sequence_id%TYPE,
2795 p_doc_sequence_value IN
2796 AP_INVOICES.doc_sequence_value%TYPE,
2797 p_batch_id IN AP_INVOICES.batch_id%TYPE,
2798 p_pay_curr_invoice_amount IN NUMBER,
2799 p_approval_workflow_flag IN VARCHAR2,
2800 p_needs_invoice_approval IN VARCHAR2,
2801 p_add_days_settlement_date IN NUMBER, --bug 493011
2802 p_disc_is_inv_less_tax_flag IN VARCHAR2, --bug 4931755
2803 p_exclude_freight_from_disc IN VARCHAR2, --bug 4931755
2804 p_calling_sequence IN VARCHAR2)
2805 RETURN BOOLEAN
2806 IS
2807 l_invoice_id NUMBER;
2808 debug_info VARCHAR2(500);
2809 current_calling_sequence VARCHAR2(2000);
2810 l_approval_ready_flag VARCHAR2(1) := 'Y';
2811 l_wfapproval_status VARCHAR2(30);
2812 --bugfix:4930111
2813 l_earliest_settlement_date DATE;
2814 l_attachments_count NUMBER;
2815
2816 BEGIN
2817 -- Update the calling sequence
2818
2819 current_calling_sequence := 'insert_ap_invoices<-'||P_calling_sequence;
2820
2821 -----------------------------------------------------------------------------
2822 -- Step 1
2823 -- get new invoice_id for base table ap_invoices
2824 -----------------------------------------------------------------------------
2825
2826 debug_info := '(Insert ap invoices step 1) Get new invoice_id for base ' ||
2827 'table ap_invoices';
2828 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2829 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2830 END IF;
2831 -- Bug 5448579
2832 /*
2833 SELECT ap_invoices_s.nextval
2834 INTO l_invoice_id
2835 FROM sys.dual;
2836 */
2837 -----------------------------------------------------------------------------
2838 -- Step 2
2839 -- get wfapproval_status from profile value - ASP.approval_workflow_flag
2840 -----------------------------------------------------------------------------
2841
2842 debug_info := '(Insert ap invoices step 2)-Get wfapproval_status ' ||
2843 'depends on profile value';
2844 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2845 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2846 END IF;
2847
2848 IF p_approval_workflow_flag = 'N' THEN
2849 l_wfapproval_status := 'NOT REQUIRED';
2850 ELSE
2851
2852 -- Payment Request: Added IF condition
2853 -- We need to set the approval status to approved if the
2854 -- invoice does not need approval
2855 IF p_needs_invoice_approval = 'N' AND
2856 p_invoice_rec.invoice_type_lookup_code = 'PAYMENT REQUEST' THEN
2857 l_wfapproval_status := 'WFAPPROVED';
2858 ELSE
2859 l_wfapproval_status := 'REQUIRED';
2860 END IF;
2861
2862 END IF;
2863 -- BUG 6785691. Aded to make approval not required in case of expense reports.
2864
2865 IF p_invoice_rec.INVOICE_TYPE_LOOKUP_CODE = 'EXPENSE REPORT' THEN
2866 l_wfapproval_status := 'NOT REQUIRED';
2867 END IF;
2868 -- BUG 6785691. END
2869
2870 /* Bug 4014019: Commenting the call to jg_globe_flex_val due to build issues.
2871
2872 -----------------------------------------------------------------------------
2873 -- Step 3
2874 -- Insert jg_zz_invoice_info
2875 -----------------------------------------------------------------------------
2876 debug_info := '(Insert ap invoices step 3) - Call ' ||
2877 'jg_globe_flex_val.insert_jg_zz_invoice_info';
2878 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2879 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2880 END IF;
2881
2882 jg_globe_flex_val.insert_jg_zz_invoice_info(
2883 l_invoice_id,
2884 p_invoice_rec.global_attribute_category,
2885 p_invoice_rec.global_attribute1,
2886 p_invoice_rec.global_attribute2,
2887 p_invoice_rec.global_attribute3,
2888 p_invoice_rec.global_attribute4,
2889 p_invoice_rec.global_attribute5,
2890 p_invoice_rec.global_attribute6,
2891 p_invoice_rec.global_attribute7,
2892 p_invoice_rec.global_attribute8,
2893 p_invoice_rec.global_attribute9,
2894 p_invoice_rec.global_attribute10,
2895 p_invoice_rec.global_attribute11,
2896 p_invoice_rec.global_attribute12,
2897 p_invoice_rec.global_attribute13,
2898 p_invoice_rec.global_attribute14,
2899 p_invoice_rec.global_attribute15,
2900 p_invoice_rec.global_attribute16,
2901 p_invoice_rec.global_attribute17,
2902 p_invoice_rec.global_attribute18,
2903 p_invoice_rec.global_attribute19,
2904 p_invoice_rec.global_attribute20,
2905 p_invoice_rec.last_updated_by,
2906 p_invoice_rec.last_update_date,
2907 p_invoice_rec.last_update_login,
2908 p_invoice_rec.created_by,
2909 p_invoice_rec.creation_date,
2910 current_calling_sequence);
2911
2912 */
2913
2914
2915 debug_info := '(Insert ap invoices step 3) Calculate earliest settlement date for Prepayment type invoices';
2916 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2917 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2918 END IF;
2919
2920 IF (p_invoice_rec.invoice_type_lookup_code = 'PREPAYMENT') THEN
2921 l_earliest_settlement_date := sysdate + nvl(p_add_days_settlement_date,0);
2922 END IF;
2923
2924
2925 -----------------------------------------------------------------------------
2926 -- Step 4
2927 -- Insert into ap_invoices table
2928 -----------------------------------------------------------------------------
2929
2930 debug_info := '(Insert ap invoices step 4) - Insert into ap_invoices';
2931 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2932 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2933 END IF;
2934
2935 -- Payment Requests: Added party_id, party_site_id,
2936 -- pay_proc_trxn_type_code, payment_function to the insert stmt
2937 INSERT INTO ap_invoices_all(
2938 invoice_id,
2939 org_id,
2940 last_update_date,
2941 last_updated_by,
2942 last_update_login,
2943 vendor_id,
2944 invoice_num,
2945 invoice_amount,
2946 vendor_site_id,
2947 amount_paid,
2948 discount_amount_taken,
2949 invoice_date,
2950 invoice_type_lookup_code,
2951 description,
2952 batch_id,
2953 amount_applicable_to_discount,
2954 terms_id,
2955 approved_amount,
2956 approval_status,
2957 approval_description,
2958 pay_group_lookup_code,
2959 set_of_books_id,
2960 accts_pay_code_combination_id,
2961 invoice_currency_code,
2962 payment_currency_code,
2963 payment_cross_rate,
2964 exchange_date,
2965 exchange_rate_type,
2966 exchange_rate,
2967 base_amount,
2968 payment_status_flag,
2969 posting_status,
2970 attribute_category,
2971 attribute1,
2972 attribute2,
2973 attribute3,
2974 attribute4,
2975 attribute5,
2976 attribute6,
2977 attribute7,
2978 attribute8,
2979 attribute9,
2980 attribute10,
2981 attribute11,
2982 attribute12,
2983 attribute13,
2984 attribute14,
2985 attribute15,
2986 global_attribute_category,
2987 global_attribute1,
2988 global_attribute2,
2989 global_attribute3,
2990 global_attribute4,
2991 global_attribute5,
2992 global_attribute6,
2993 global_attribute7,
2994 global_attribute8,
2995 global_attribute9,
2996 global_attribute10,
2997 global_attribute11,
2998 global_attribute12,
2999 global_attribute13,
3000 global_attribute14,
3001 global_attribute15,
3002 global_attribute16,
3003 global_attribute17,
3004 global_attribute18,
3005 global_attribute19,
3006 global_attribute20,
3007 creation_date,
3008 created_by,
3009 vendor_prepay_amount,
3010 prepay_flag,
3011 recurring_payment_id,
3012 terms_date,
3013 source,
3014 payment_method_code,
3015 doc_sequence_id,
3016 doc_sequence_value,
3017 doc_category_code,
3018 voucher_num,
3019 exclusive_payment_flag,
3020 awt_group_id,
3021 pay_awt_group_id,--bug6639866
3022 payment_cross_rate_type,
3023 payment_cross_rate_date,
3024 pay_curr_invoice_amount,
3025 goods_received_date,
3026 invoice_received_date,
3027 -- ussgl_transaction_code, - Bug 4277744
3028 gl_date,
3029 approval_ready_flag,
3030 wfapproval_status,
3031 requester_id,
3032 control_amount,
3033 tax_related_invoice_id,
3034 taxation_country,
3035 document_sub_type,
3036 supplier_tax_invoice_number,
3037 supplier_tax_invoice_date,
3038 supplier_tax_exchange_rate,
3039 tax_invoice_recording_date,
3040 tax_invoice_internal_seq,
3041 legal_entity_id,
3042 application_id,
3043 product_table,
3044 reference_key1,
3045 reference_key2,
3046 reference_key3,
3047 reference_key4,
3048 reference_key5,
3049 reference_1,
3050 reference_2,
3051 net_of_retainage_flag,
3052 cust_registration_code,
3053 cust_registration_number,
3054 paid_on_behalf_employee_id,
3055 party_id,
3056 party_site_id,
3057 pay_proc_trxn_type_code,
3058 payment_function,
3059 BANK_CHARGE_BEARER,
3060 REMITTANCE_MESSAGE1,
3061 REMITTANCE_MESSAGE2,
3062 REMITTANCE_MESSAGE3,
3063 UNIQUE_REMITTANCE_IDENTIFIER,
3064 URI_CHECK_DIGIT,
3065 SETTLEMENT_PRIORITY,
3066 PAYMENT_REASON_CODE,
3067 PAYMENT_REASON_COMMENTS,
3068 DELIVERY_CHANNEL_CODE,
3069 EXTERNAL_BANK_ACCOUNT_ID,
3070 --bugfix:4930111
3071 EARLIEST_SETTLEMENT_DATE,
3072 --bug 4931755
3073 DISC_IS_INV_LESS_TAX_FLAG,
3074 EXCLUDE_FREIGHT_FROM_DISCOUNT,
3075 --Bug 7357218 Quick Pay and Dispute Resolution Project
3076 ORIGINAL_INVOICE_AMOUNT,
3077 DISPUTE_REASON,
3078 --Third Party Payments
3079 REMIT_TO_SUPPLIER_NAME,
3080 REMIT_TO_SUPPLIER_ID,
3081 REMIT_TO_SUPPLIER_SITE,
3082 REMIT_TO_SUPPLIER_SITE_ID,
3083 RELATIONSHIP_ID
3084 )
3085 VALUES (ap_invoices_s.nextval, -- l_invoice_id, Bug 5448579
3086 p_invoice_rec.org_id,
3087 p_invoice_rec.last_update_date,
3088 --bug 6951863 fix -start
3089 --p_invoice_rec.last_update_login,
3090 p_invoice_rec.last_updated_by,
3091 --p_invoice_rec.last_updated_by,
3092 p_invoice_rec.last_update_login,
3093 --bug 6951863 fix -end
3094 p_invoice_rec.vendor_id,
3095 p_invoice_rec.invoice_num,
3096 p_invoice_rec.invoice_amount,
3097 p_invoice_rec.vendor_site_id,
3098 0, -- amount_paid
3099 0, -- discount_amount_taken,
3100 p_invoice_rec.invoice_date,
3101 p_invoice_rec.invoice_type_lookup_code,
3102 p_invoice_rec.description, -- description
3103 p_batch_id, -- batch_id
3104 p_invoice_rec.amount_applicable_to_discount,
3105 p_invoice_rec.terms_id, -- terms_id
3106 NULL, -- approved_amount
3107 NULL, -- approval_status
3108 NULL, -- approval_description
3109 p_invoice_rec.pay_group_lookup_code,
3110 p_set_of_books_id,
3111 p_invoice_rec.accts_pay_code_combination_id,
3112 p_invoice_rec.invoice_currency_code,
3113 p_invoice_rec.payment_currency_code,
3114 p_invoice_rec.payment_cross_rate,
3115 p_invoice_rec.exchange_date,
3116 p_invoice_rec.exchange_rate_type,
3117 p_invoice_rec.exchange_rate,
3118 p_invoice_rec.no_xrate_base_amount, -- base_amount
3119 'N', -- payment_status_flag
3120 NULL, -- posting_status
3121 p_invoice_rec.attribute_category,
3122 p_invoice_rec.attribute1,
3123 p_invoice_rec.attribute2,
3124 p_invoice_rec.attribute3,
3125 p_invoice_rec.attribute4,
3126 p_invoice_rec.attribute5,
3127 p_invoice_rec.attribute6,
3128 p_invoice_rec.attribute7,
3129 p_invoice_rec.attribute8,
3130 p_invoice_rec.attribute9,
3131 p_invoice_rec.attribute10,
3132 p_invoice_rec.attribute11,
3133 p_invoice_rec.attribute12,
3134 p_invoice_rec.attribute13,
3135 p_invoice_rec.attribute14,
3136 p_invoice_rec.attribute15,
3137 p_invoice_rec.global_attribute_category,
3138 p_invoice_rec.global_attribute1,
3139 p_invoice_rec.global_attribute2,
3140 p_invoice_rec.global_attribute3,
3141 p_invoice_rec.global_attribute4,
3142 p_invoice_rec.global_attribute5,
3143 p_invoice_rec.global_attribute6,
3144 p_invoice_rec.global_attribute7,
3145 p_invoice_rec.global_attribute8,
3146 p_invoice_rec.global_attribute9,
3147 p_invoice_rec.global_attribute10,
3148 p_invoice_rec.global_attribute11,
3149 p_invoice_rec.global_attribute12,
3150 p_invoice_rec.global_attribute13,
3151 p_invoice_rec.global_attribute14,
3152 p_invoice_rec.global_attribute15,
3153 p_invoice_rec.global_attribute16,
3154 p_invoice_rec.global_attribute17,
3155 p_invoice_rec.global_attribute18,
3156 p_invoice_rec.global_attribute19,
3157 p_invoice_rec.global_attribute20,
3158 p_invoice_rec.creation_date,
3159 p_invoice_rec.created_by,
3160 0, -- vendor_prepay_amount,
3161 'N', -- prepay_flag,
3162 NULL, -- recurring_payment_id,
3163 p_invoice_rec.terms_date,
3164 p_invoice_rec.source,
3165 p_invoice_rec.payment_method_code,
3166 p_doc_sequence_id,
3167 p_doc_sequence_value, -- doc_sequence_value
3168 p_invoice_rec.doc_category_code, -- doc_category_code
3169 DECODE(p_invoice_rec.doc_category_code, NULL,
3170 p_invoice_rec.voucher_num, ''), -- voucher_num
3171 --p_invoice_rec.exclusive_payment_flag, -- **exclusive_payment_flag
3172 DECODE(p_invoice_rec.invoice_type_lookup_code, 'CREDIT', 'N', p_invoice_rec.exclusive_payment_flag), -- BUG 7195865
3173 p_invoice_rec.awt_group_id, -- awt_group_id
3174 p_invoice_rec.pay_awt_group_id, -- pay_awt_group_id--bug6639866
3175 p_invoice_rec.payment_cross_rate_type, -- payment_cross_rate_type
3176 p_invoice_rec.payment_cross_rate_date, -- payment_crosss_rate_date
3177 p_pay_curr_invoice_amount, -- pay_curr_invoice_amount
3178 p_invoice_rec.goods_received_date, -- goods_received_date
3179 p_invoice_rec.invoice_received_date, -- invoice_received_date
3180 -- Removed for bug 4277744
3181 -- p_invoice_rec.ussgl_transaction_code, -- ussgl_transaction_code
3182 TRUNC(p_invoice_rec.gl_date), -- gl_date
3183 l_approval_ready_flag, -- approval_ready_flag
3184 l_wfapproval_status, -- wfapproval_status
3185 p_invoice_rec.requester_id, -- request_id
3186 p_invoice_rec.control_amount, -- control_amount
3187 p_invoice_rec.tax_related_invoice_id, -- tax_related_invoice_id
3188 p_invoice_rec.taxation_country, -- taxation_country
3189 p_invoice_rec.document_sub_type, -- document_sub_type
3190 p_invoice_rec.supplier_tax_invoice_number,
3191 -- supplier_tax_invoice_number
3192 p_invoice_rec.supplier_tax_invoice_date,
3193 -- supplier_tax_invoice_date
3194 p_invoice_rec.supplier_tax_exchange_rate,
3195 -- supplier_tax_exchange_rate
3196 p_invoice_rec.tax_invoice_recording_date,
3197 -- tax_invoice_recording_date
3198 p_invoice_rec.tax_invoice_internal_seq, -- tax_invoice_internal_seq
3199 p_invoice_rec.legal_entity_id, -- legal_entity_id
3200 p_invoice_rec.application_id, --application identifier
3201 p_invoice_rec.product_table, --product_table
3202 p_invoice_rec.reference_key1, --reference_key1
3203 p_invoice_rec.reference_key2, --reference_key2
3204 p_invoice_rec.reference_key3, --reference_key3
3205 p_invoice_rec.reference_key4, --reference_key4
3206 p_invoice_rec.reference_key5, --reference_key5
3207 p_invoice_rec.reference_1, --reference_1
3208 p_invoice_rec.reference_2, --reference_2
3209 p_invoice_rec.net_of_retainage_flag, --net_of_retainage_flag
3210 P_invoice_rec.cust_registration_code,
3211 P_invoice_rec.cust_registration_number,
3212 P_invoice_rec.paid_on_behalf_employee_id,
3213 p_invoice_rec.party_id,
3214 p_invoice_rec.party_site_id,
3215 p_invoice_rec.pay_proc_trxn_type_code,
3216 p_invoice_rec.payment_function,
3217 p_invoice_rec.BANK_CHARGE_BEARER,
3218 p_invoice_rec.REMITTANCE_MESSAGE1,
3219 p_invoice_rec.REMITTANCE_MESSAGE2,
3220 p_invoice_rec.REMITTANCE_MESSAGE3,
3221 p_invoice_rec.UNIQUE_REMITTANCE_IDENTIFIER,
3222 p_invoice_rec.URI_CHECK_DIGIT,
3223 p_invoice_rec.SETTLEMENT_PRIORITY,
3224 p_invoice_rec.PAYMENT_REASON_CODE,
3225 p_invoice_rec.PAYMENT_REASON_COMMENTS,
3226 p_invoice_rec.DELIVERY_CHANNEL_CODE,
3227 p_invoice_rec.EXTERNAL_BANK_ACCOUNT_ID,
3228 --bugfix:4930111
3229 l_earliest_settlement_date,
3230 --bug4931755
3231 p_disc_is_inv_less_tax_flag,
3232 p_exclude_freight_from_disc,
3233 --Bug 7357218 Quick Pay and Dispute Resolution Project
3234 p_invoice_rec.ORIGINAL_INVOICE_AMOUNT,
3235 p_invoice_rec.DISPUTE_REASON,
3236 --Third Party Payments
3237 p_invoice_rec.REMIT_TO_SUPPLIER_NAME,
3238 p_invoice_rec.REMIT_TO_SUPPLIER_ID,
3239 p_invoice_rec.REMIT_TO_SUPPLIER_SITE,
3240 p_invoice_rec.REMIT_TO_SUPPLIER_SITE_ID,
3241 p_invoice_rec.RELATIONSHIP_ID
3242 ) RETURNING invoice_id INTO l_invoice_id;
3243
3244 -----------------------------------------------------------------------------
3245 -- Step 5
3246 -- copy attachment for the invoice
3247 -----------------------------------------------------------------------------
3248 debug_info := '(Insert ap invoices step 5) before copy attachments: '||
3249 'source = ' || p_invoice_rec.source || ', from_invoice_id = ' ||
3250 p_invoice_rec.invoice_id || ', to_invoice_id = ' || l_invoice_id;
3251 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3252 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3253 END IF;
3254
3255 l_attachments_count :=
3256 copy_attachments(p_invoice_rec.invoice_id, l_invoice_id);
3257 debug_info := '(Insert ap invoices step 5) copy attachments done: ' ||
3258 l_attachments_count;
3259 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3260 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3261 END IF;
3262
3263 -----------------------------------------------------------------------------
3264 -- Step 6
3265 -- Assign the out parameter for new invoice_id
3266 -----------------------------------------------------------------------------
3267 debug_info := '(Insert ap invoices step 6) - Return the new invoice_id-> ' ||
3268 to_char(l_invoice_id);
3269 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3270 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3271 END IF;
3272
3273
3274 p_base_invoice_id := l_invoice_id;
3275
3276 RETURN( TRUE );
3277 EXCEPTION
3278 WHEN OTHERS THEN
3279 debug_info := debug_info || '->exception';
3280 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3281 Print(
3282 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3283 debug_info);
3284 END IF;
3285 IF (SQLCODE < 0) THEN
3286 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3287 Print(
3288 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3289 SQLERRM);
3290 END IF;
3291 END IF;
3292 RETURN (FALSE);
3293
3294 END insert_ap_invoices;
3295
3296 /*======================================================================
3297 Function: Change_invoice_status
3298
3299 The available statuses are:
3300 'PROCESSING' - Temporary status to prevent the invoice cursor pick it up
3301 again. It means invoice is ok during this run and will be
3302 changed to 'PROCESSED' after the batch finished.
3303 'REJECTING' - Temporary status to prevent the invoice cursor pick it up
3304 again. It means invoice is rejected during this run and
3305 will be changed to 'REJECTED' after the batch finished.
3306 'PROCESSED' - It means invoice has been successfully imported
3307 'REJECTED' - It means there are some rejections or error for this invoice.
3308 Interface invoice cannot be purged if the flag is other than 'PRECESSED'
3309 ========================================================================*/
3310
3311 FUNCTION change_invoice_status(
3312 p_status IN VARCHAR2,
3313 p_import_invoice_id IN NUMBER,
3314 P_calling_sequence IN VARCHAR2)
3315 RETURN BOOLEAN
3316 IS
3317 current_calling_sequence VARCHAR2(2000);
3318 debug_info VARCHAR2(500);
3319
3320 BEGIN
3321 -- Update the calling sequence
3322
3323 current_calling_sequence := 'Change_invoice_status<-'||P_calling_sequence;
3324
3325 ---------------------------------------------
3326 -- Step 1
3327 -- Update status to p_invoices_interface
3328 ---------------------------------------------
3329
3330 debug_info := '(Change_invoice_status 1) Change invoice status to '||
3331 p_status;
3332 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3333 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3334 END IF;
3335
3336 UPDATE AP_INVOICES_INTERFACE
3337 SET status = p_status
3338 WHERE invoice_id = p_import_invoice_id;
3339
3340 RETURN(TRUE);
3341
3342 EXCEPTION
3343 WHEN OTHERS THEN
3344 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3345 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3346 END IF;
3347
3348 IF (SQLCODE < 0) THEN
3349 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3350 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
3351 END IF;
3352 END IF;
3353
3354 RETURN (FALSE);
3355
3356 END change_invoice_status;
3357
3358 /*======================================================================
3359 Private Funtion: Update_temp_invoice_status
3360
3361 Change temporary invoice status from
3362 'PROCESSING' to 'PROCESSED'
3363 'REJECTING' to 'REJECTED'
3364 ======================================================================*/
3365
3366 FUNCTION Update_temp_invoice_status(
3367 p_source IN VARCHAR2,
3368 p_group_id IN VARCHAR2,
3369 p_calling_sequence IN VARCHAR2)
3370 RETURN BOOLEAN
3371 IS
3372 current_calling_sequence VARCHAR2(2000);
3373 debug_info VARCHAR2(500);
3374 --4019310, use binds for literals
3375 l_processed varchar2(10);
3376 l_rejected varchar2(10);
3377 l_processing varchar2(10);
3378 l_rejecting varchar2(10);
3379 BEGIN
3380
3381 l_processed := 'PROCESSED';
3382 l_rejected := 'REJECTED';
3383 l_rejecting := 'REJECTING';
3384 l_processing:= 'PROCESSING';
3385 -- Update the calling sequence
3386 --
3387 current_calling_sequence := 'Update_temp_invoice_status<-'||
3388 P_calling_sequence;
3389
3390 ---------------------------------------------
3391 -- 1. Change PROCESSING to PROCESSED
3392 ---------------------------------------------
3393 debug_info := '(Update_temp_invoice_status 1) Change '||
3394 'PROCESSING to PROCESSED ';
3395
3396 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3397 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3398 END IF;
3399
3400 ---------------------------------------------
3401 -- 2. Change REJECTING to REJECTED
3402 ---------------------------------------------
3403 debug_info := '(Update_temp_invoice_status 2) Change REJECTING to REJECTED';
3404
3405 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3406 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3407 END IF;
3408 -- Bug fix: 1952122
3409 -- Rewrite with two statements avoiding AND ((p_group_id is NULL) OR (group_id = --p_group_id))
3410 --3910020, used binds in the sql below
3411
3412 --Bug 6801046
3413 --Update statement should only update the status of Invoices
3414 --pertaining to the current request. Modified the below 4 update stmts.
3415
3416 IF p_group_id IS NULL THEN
3417
3418 UPDATE AP_INVOICES_INTERFACE
3419 SET status = l_processed
3420 WHERE source = p_source
3421 AND p_group_id is NULL
3422 AND status = l_processing
3423 AND request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id;
3424
3425 UPDATE AP_INVOICES_INTERFACE
3426 SET status = l_rejected
3427 WHERE source = p_source
3428 AND p_group_id is NULL
3429 AND status = l_rejecting
3430 AND request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id;
3431
3432 ELSE
3433
3434 UPDATE AP_INVOICES_INTERFACE
3435 SET status = l_processed
3436 WHERE source = p_source
3437 AND group_id = p_group_id
3438 AND status = l_processing
3439 ANd request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id;
3440
3441 UPDATE AP_INVOICES_INTERFACE
3442 SET status = l_rejected
3443 WHERE source = p_source
3444 AND group_id = p_group_id
3445 AND status = l_rejecting
3446 AND request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id;
3447
3448 END IF;
3449
3450 RETURN(TRUE);
3451
3452 EXCEPTION
3453 WHEN OTHERS THEN
3454 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3455 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3456 END IF;
3457
3458 IF (SQLCODE < 0) THEN
3459 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3460 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
3461 END IF;
3462 END IF;
3463
3464 RETURN (FALSE);
3465
3466 END Update_temp_invoice_status;
3467
3468 /*======================================================================
3469 Private Procedure: Insert new AP_BATCHES lines
3470
3471 Insert New Batch line if the batch name is new
3472 ======================================================================*/
3473
3474 FUNCTION Insert_ap_batches(
3475 p_batch_id IN NUMBER,
3476 p_batch_name IN VARCHAR2,
3477 p_invoice_currency_code IN VARCHAR2,
3478 p_payment_currency_code IN VARCHAR2,
3479 p_actual_invoice_count IN NUMBER,
3480 p_actual_invoice_total IN NUMBER,
3481 p_last_updated_by IN NUMBER,
3482 p_calling_sequence IN VARCHAR2)
3483 RETURN BOOLEAN
3484 IS
3485 current_calling_sequence VARCHAR2(2000);
3486 debug_info VARCHAR2(500);
3487 BEGIN
3488 -- Update the calling sequence
3489
3490 current_calling_sequence := 'Insert_ap_batches<-'||p_calling_sequence;
3491
3492 ---------------------------------------------
3493 -- Insert ap_batches
3494 ---------------------------------------------
3495 debug_info := 'Insert ap_batches';
3496 -- bug 5441261. Insert should be into AP_BATCHES_ALL
3497 INSERT INTO ap_batches_all(
3498 batch_id,
3499 batch_name,
3500 batch_date,
3501 last_update_date,
3502 last_updated_by,
3503 control_invoice_count,
3504 control_invoice_total,
3505 actual_invoice_count,
3506 actual_invoice_total,
3507 invoice_currency_code,
3508 payment_currency_code,
3509 creation_date,
3510 created_by)
3511 VALUES(
3512 p_batch_id,
3513 p_batch_name,
3514 TRUNC(SYSDATE),
3515 SYSDATE,
3516 p_last_updated_by,
3517 p_actual_invoice_count ,
3518 p_actual_invoice_total ,
3519 p_actual_invoice_count ,
3520 p_actual_invoice_total ,
3521 p_invoice_currency_code,
3522 p_payment_currency_code,
3523 SYSDATE,
3524 p_last_updated_by);
3525
3526 RETURN(TRUE);
3527
3528 EXCEPTION
3529
3530 WHEN OTHERS then
3531 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3532 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3533 END IF;
3534
3535 IF (SQLCODE < 0) THEN
3536 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3537 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
3538 END IF;
3539 END IF;
3540
3541 RETURN (FALSE);
3542
3543 END Insert_ap_batches;
3544
3545 /*======================================================================
3546 Function: Update_Ap_Batches
3547 This function updates the value of control invoice count and
3548 control invoice total in ap_batches
3549 ======================================================================*/
3550
3551 FUNCTION Update_Ap_Batches(
3552 p_batch_id IN NUMBER,
3553 p_batch_name IN VARCHAR2,
3554 p_actual_invoice_count IN NUMBER,
3555 p_actual_invoice_total IN NUMBER,
3556 p_last_updated_by IN NUMBER,
3557 p_calling_sequence IN VARCHAR2)
3558 RETURN BOOLEAN
3559 IS
3560 current_calling_sequence varchar2(2000);
3561 debug_info varchar2(500);
3562
3563 BEGIN
3564
3565 -- Update the calling sequence
3566
3567 current_calling_sequence :='Update_Ap_Batches<-'||p_calling_sequence;
3568
3569 -- Update ap_batches
3570
3571 debug_info :='Update ap_batches';
3572
3573 UPDATE ap_batches
3574 SET control_invoice_count =
3575 NVL(control_invoice_count,0)+
3576 p_actual_invoice_count,
3577 control_invoice_total =
3578 NVL(control_invoice_total,0)+
3579 p_actual_invoice_total,
3580 actual_invoice_count =
3581 actual_invoice_count+
3582 p_actual_invoice_count,
3583 actual_invoice_total =
3584 actual_invoice_total+
3585 p_actual_invoice_total
3586 WHERE batch_id = p_batch_id; -- Added for bug2003024
3587
3588 RETURN(TRUE);
3589
3590 EXCEPTION
3591 WHEN OTHERS THEN
3592 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3593 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3594 END IF;
3595
3596 IF (SQLCODE<0) THEN
3597 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3598 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
3599 END IF;
3600 END IF;
3601
3602 RETURN(FALSE);
3603
3604 END Update_ap_Batches;
3605
3606 /*=========================================================================*/
3607 /* */
3608 /* Function Insert_Ap_Invoices_lines */
3609 /* Program Flow: */
3610 /* 1. Insert into ap_invoice_lines with the validated interface lines */
3611 /* data */
3612 /* 2. Bulk select primary key of lines */
3613 /* Parameters */
3614 /* p_base_invoice_id */
3615 /* p_invoice_lines_tab - validated interface lines data */
3616 /* p_set_of_books_id - set_of_books_id populated in get_info() */
3617 /* p_default_last_updated_by */
3618 /* p_default_last_update_login */
3619 /* p_calling_sequence - for debug purpose */
3620 /* */
3621 /*=========================================================================*/
3622
3623 FUNCTION insert_ap_invoice_lines(
3624 p_base_invoice_id IN NUMBER,
3625 p_invoice_lines_tab IN
3626 AP_IMPORT_INVOICES_PKG.t_lines_table,
3627 p_set_of_books_id IN NUMBER,
3628 p_approval_workflow_flag IN VARCHAR2,
3629 p_tax_only_flag IN VARCHAR2,
3630 p_tax_only_rcv_matched_flag IN VARCHAR2,
3631 p_default_last_updated_by IN NUMBER,
3632 p_default_last_update_login IN NUMBER,
3633 p_calling_sequence IN VARCHAR2)
3634 RETURN BOOLEAN
3635 IS
3636 debug_info VARCHAR2(500);
3637 current_calling_sequence VARCHAR2(2000);
3638 i BINARY_INTEGER := 0;
3639 l_generate_dists AP_INVOICE_LINES.generate_dists%TYPE := 'Y';
3640 l_wfapproval_status AP_INVOICE_LINES.wfapproval_status%TYPE := NULL;
3641 l_key_value_list gl_ca_utility_pkg.r_key_value_arr;
3642
3643 l_inv_code VARCHAR2(50); -- BUG 6785691
3644
3645 -- bug# 6989166 starts
3646 Cursor c_ship_to_location (p_ship_to_loc_code HR_LOCATIONS.LOCATION_CODE%TYPE) Is
3647 Select ship_to_location_id
3648 From hr_locations
3649 Where location_code = p_ship_to_loc_code
3650 and nvl(ship_to_site_flag, 'N') = 'Y';
3651 -- bug# 6989166 ends
3652
3653 Cursor c_ship_to (c_invoice_id NUMBER) Is
3654 Select aps.ship_to_location_id
3655 From ap_invoices_all ai,
3656 ap_supplier_sites_all aps
3657 Where ai.invoice_id = c_invoice_id
3658 And ai.vendor_site_id = aps.vendor_site_id;
3659
3660 l_ship_to_location_id ap_supplier_sites_all.ship_to_location_id%type;
3661 -- bug# 6989166 starts
3662 p_ship_to_location_id ap_supplier_sites_all.ship_to_location_id%type;
3663 -- bug# 6989166 ends
3664
3665 BEGIN
3666 -- Update the calling sequence
3667
3668 current_calling_sequence := 'insert_ap_invoice_lines<-'||P_calling_sequence;
3669
3670 -----------------------------------------------------------------------------
3671 -- Step 1
3672 -- Initialize the work flow approval flag
3673 -----------------------------------------------------------------------------
3674
3675 debug_info := '(Insert ap invoice lines step 1) - populate the '||
3676 'wfapproval_status_flag';
3677
3678 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3679 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3680 END IF;
3681
3682 IF ( NVL(p_approval_workflow_flag, 'N') = 'N' ) THEN
3683 l_wfapproval_status := 'NOT REQUIRED';
3684 ELSE
3685 l_wfapproval_status := 'REQUIRED';
3686 END IF;
3687
3688 -- BUG 6785691. START
3689 select INVOICE_TYPE_LOOKUP_CODE
3690 into l_inv_code
3691 from ap_invoices_all
3692 where invoice_id = p_base_invoice_id;
3693
3694 IF l_inv_code = 'EXPENSE REPORT' THEN
3695 l_wfapproval_status := 'NOT REQUIRED';
3696 END IF;
3697 -- BUG 6785691. END
3698
3699 -----------------------------------------------------------------------------
3700 -- Step 2
3701 -- Insert into the ap_invoice_lines table
3702 -----------------------------------------------------------------------------
3703
3704 debug_info := '(Insert ap invoice lines step 2) - Loop the Pl/sql table';
3705
3706 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3707 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3708 END IF;
3709
3710 Open c_ship_to (p_base_invoice_id);
3711 Fetch c_ship_to
3712 Into p_ship_to_location_id; --l_ship_to_location_id; -- bug 6989166
3713 Close c_ship_to;
3714
3715 BEGIN
3716 FOR i IN p_invoice_lines_tab.FIRST..p_invoice_lines_tab.LAST LOOP
3717 -- bUg 7537802
3718 l_generate_dists := 'Y';
3719 -- bug# 6989166 starts
3720 IF (p_invoice_lines_tab(i).ship_to_location_code IS NOT NULL AND
3721 p_invoice_lines_tab(i).ship_to_location_id IS NULL) THEN
3722
3723 Open c_ship_to_location (p_invoice_lines_tab(i).ship_to_location_code);
3724 Fetch c_ship_to_location
3725 Into l_ship_to_location_id;
3726 Close c_ship_to_location;
3727 ELSE
3728 l_ship_to_location_id := p_ship_to_location_id;
3729 END IF;
3730 -- bug# 6989166 ends
3731
3732 /* IF (p_invoice_lines_tab(i).line_type_lookup_code <> 'TAX'
3733 OR (p_invoice_lines_tab(i).line_type_lookup_code = 'TAX'
3734 and nvl(p_tax_only_flag, 'N') <> 'Y'
3735 and nvl(p_tax_only_rcv_matched_flag, 'N') <> 'Y')) THEN */
3736 /* commented for 6010950 as all the lines we first need to insert into
3737 ap_invoice_lines as user is importing DFF's also while importing invoice.
3738 So if we are copying the tax line from zx_lines_summary instead of inserting
3739 the Tax line first in ap_invoice_lines then we are loosing DFF information. */
3740
3741 --Bug 7427463/7379883 project_id, code_combination information is not populate
3742 --at ap_exp_report_lines_all table. We are allowing expense reports, payment requests
3743 --to generate distribution without checking any values.
3744
3745 IF (l_inv_code IN ('EXPENSE REPORT','PAYMENT REQUEST')) THEN
3746 l_generate_dists := 'Y';
3747
3748 -- End of Bug 7427463/7379883
3749
3750 -- BUG 7291580 checking for po_number, project_id, distribution_set_id, dist_code_combination_id
3751 -- rcv_transaction_id. If all are null then set generate_dist to 'N', else to 'Y'
3752
3753 ELSIF ( p_invoice_lines_tab(i).project_id IS NULL
3754 AND p_invoice_lines_tab(i).distribution_set_id IS NULL
3755 AND p_invoice_lines_tab(i).po_header_id IS NULL
3756 AND p_invoice_lines_tab(i).dist_code_combination_id IS NULL
3757 AND p_invoice_lines_tab(i).rcv_transaction_id IS NULL
3758 ) THEN
3759 l_generate_dists := 'N';
3760 END IF;
3761 -- End of BUG 7291580
3762
3763 -- Insert only non-tax lines
3764 -- tax lines will be created after calling import_document_with_tax
3765 -- or calculate tax in the case of tax only lines matched to receipts
3766
3767 INSERT INTO ap_invoice_lines_all(
3768 INVOICE_ID,
3769 LINE_NUMBER,
3770 LINE_TYPE_LOOKUP_CODE,
3771 REQUESTER_ID,
3772 DESCRIPTION,
3773 LINE_SOURCE,
3774 ORG_ID,
3775 LINE_GROUP_NUMBER,
3776 INVENTORY_ITEM_ID,
3777 ITEM_DESCRIPTION,
3778 SERIAL_NUMBER,
3779 MANUFACTURER,
3780 MODEL_NUMBER,
3781 WARRANTY_NUMBER,
3782 GENERATE_DISTS,
3783 MATCH_TYPE,
3784 DISTRIBUTION_SET_ID,
3785 ACCOUNT_SEGMENT,
3786 BALANCING_SEGMENT,
3787 COST_CENTER_SEGMENT,
3788 OVERLAY_DIST_CODE_CONCAT,
3789 DEFAULT_DIST_CCID,
3790 PRORATE_ACROSS_ALL_ITEMS,
3791 ACCOUNTING_DATE,
3792 PERIOD_NAME ,
3793 DEFERRED_ACCTG_FLAG ,
3794 DEF_ACCTG_START_DATE ,
3795 DEF_ACCTG_END_DATE,
3796 DEF_ACCTG_NUMBER_OF_PERIODS,
3797 DEF_ACCTG_PERIOD_TYPE ,
3798 SET_OF_BOOKS_ID,
3799 AMOUNT,
3800 BASE_AMOUNT,
3801 ROUNDING_AMT,
3802 QUANTITY_INVOICED,
3803 UNIT_MEAS_LOOKUP_CODE ,
3804 UNIT_PRICE,
3805 WFAPPROVAL_STATUS,
3806 -- USSGL_TRANSACTION_CODE, - Bug 4277744
3807 DISCARDED_FLAG,
3808 ORIGINAL_AMOUNT,
3809 ORIGINAL_BASE_AMOUNT ,
3810 ORIGINAL_ROUNDING_AMT ,
3811 CANCELLED_FLAG ,
3812 INCOME_TAX_REGION,
3813 TYPE_1099 ,
3814 STAT_AMOUNT ,
3815 PREPAY_INVOICE_ID ,
3816 PREPAY_LINE_NUMBER ,
3817 INVOICE_INCLUDES_PREPAY_FLAG ,
3818 CORRECTED_INV_ID ,
3819 CORRECTED_LINE_NUMBER ,
3820 PO_HEADER_ID,
3821 PO_LINE_ID ,
3822 PO_RELEASE_ID ,
3823 PO_LINE_LOCATION_ID ,
3824 PO_DISTRIBUTION_ID,
3825 RCV_TRANSACTION_ID,
3826 --Bug 7344899
3827 RCV_SHIPMENT_LINE_ID,
3828 FINAL_MATCH_FLAG,
3829 ASSETS_TRACKING_FLAG ,
3830 ASSET_BOOK_TYPE_CODE ,
3831 ASSET_CATEGORY_ID ,
3832 PROJECT_ID ,
3833 TASK_ID ,
3834 EXPENDITURE_TYPE ,
3835 EXPENDITURE_ITEM_DATE ,
3836 EXPENDITURE_ORGANIZATION_ID ,
3837 PA_QUANTITY,
3838 PA_CC_AR_INVOICE_ID ,
3839 PA_CC_AR_INVOICE_LINE_NUM ,
3840 PA_CC_PROCESSED_CODE ,
3841 AWARD_ID,
3842 AWT_GROUP_ID ,
3843 PAY_AWT_GROUP_ID ,--bug6639866
3844 REFERENCE_1 ,
3845 REFERENCE_2 ,
3846 RECEIPT_VERIFIED_FLAG ,
3847 RECEIPT_REQUIRED_FLAG ,
3848 RECEIPT_MISSING_FLAG ,
3849 JUSTIFICATION ,
3850 EXPENSE_GROUP ,
3851 START_EXPENSE_DATE ,
3852 END_EXPENSE_DATE ,
3853 RECEIPT_CURRENCY_CODE ,
3854 RECEIPT_CONVERSION_RATE,
3855 RECEIPT_CURRENCY_AMOUNT ,
3856 DAILY_AMOUNT ,
3857 WEB_PARAMETER_ID ,
3858 ADJUSTMENT_REASON ,
3859 MERCHANT_DOCUMENT_NUMBER ,
3860 MERCHANT_NAME ,
3861 MERCHANT_REFERENCE ,
3862 MERCHANT_TAX_REG_NUMBER,
3863 MERCHANT_TAXPAYER_ID ,
3864 COUNTRY_OF_SUPPLY,
3865 CREDIT_CARD_TRX_ID ,
3866 COMPANY_PREPAID_INVOICE_ID,
3867 CC_REVERSAL_FLAG ,
3868 CREATION_DATE ,
3869 CREATED_BY,
3870 LAST_UPDATED_BY ,
3871 LAST_UPDATE_DATE ,
3872 LAST_UPDATE_LOGIN ,
3873 PROGRAM_APPLICATION_ID ,
3874 PROGRAM_ID ,
3875 PROGRAM_UPDATE_DATE,
3876 REQUEST_ID ,
3877 ATTRIBUTE_CATEGORY,
3878 ATTRIBUTE1,
3879 ATTRIBUTE2 ,
3880 ATTRIBUTE3 ,
3881 ATTRIBUTE4 ,
3882 ATTRIBUTE5 ,
3883 ATTRIBUTE6 ,
3884 ATTRIBUTE7 ,
3885 ATTRIBUTE8,
3886 ATTRIBUTE9 ,
3887 ATTRIBUTE10,
3888 ATTRIBUTE11,
3889 ATTRIBUTE12,
3890 ATTRIBUTE13 ,
3891 ATTRIBUTE14,
3892 ATTRIBUTE15,
3893 GLOBAL_ATTRIBUTE_CATEGORY,
3894 GLOBAL_ATTRIBUTE1,
3895 GLOBAL_ATTRIBUTE2,
3896 GLOBAL_ATTRIBUTE3,
3897 GLOBAL_ATTRIBUTE4 ,
3898 GLOBAL_ATTRIBUTE5 ,
3899 GLOBAL_ATTRIBUTE6 ,
3900 GLOBAL_ATTRIBUTE7 ,
3901 GLOBAL_ATTRIBUTE8 ,
3902 GLOBAL_ATTRIBUTE9 ,
3903 GLOBAL_ATTRIBUTE10,
3904 GLOBAL_ATTRIBUTE11,
3905 GLOBAL_ATTRIBUTE12 ,
3906 GLOBAL_ATTRIBUTE13,
3907 GLOBAL_ATTRIBUTE14,
3908 GLOBAL_ATTRIBUTE15,
3909 GLOBAL_ATTRIBUTE16,
3910 GLOBAL_ATTRIBUTE17 ,
3911 GLOBAL_ATTRIBUTE18 ,
3912 GLOBAL_ATTRIBUTE19 ,
3913 GLOBAL_ATTRIBUTE20 ,
3914 CONTROL_AMOUNT,
3915 ASSESSABLE_VALUE,
3916 PRIMARY_INTENDED_USE,
3917 SHIP_TO_LOCATION_ID,
3918 PRODUCT_TYPE,
3919 PRODUCT_CATEGORY,
3920 PRODUCT_FISC_CLASSIFICATION,
3921 USER_DEFINED_FISC_CLASS,
3922 TRX_BUSINESS_CATEGORY,
3923 APPLICATION_ID,
3924 PRODUCT_TABLE,
3925 REFERENCE_KEY1,
3926 REFERENCE_KEY2,
3927 REFERENCE_KEY3,
3928 REFERENCE_KEY4,
3929 REFERENCE_KEY5,
3930 PURCHASING_CATEGORY_ID,
3931 COST_FACTOR_ID,
3932 SOURCE_APPLICATION_ID,
3933 SOURCE_ENTITY_CODE,
3934 SOURCE_EVENT_CLASS_CODE,
3935 SOURCE_TRX_ID,
3936 SOURCE_LINE_ID,
3937 SOURCE_TRX_LEVEL_TYPE,
3938 TAX_CLASSIFICATION_CODE,
3939 RETAINED_AMOUNT,
3940 RETAINED_AMOUNT_REMAINING,
3941 TAX_REGIME_CODE,
3942 TAX,
3943 TAX_JURISDICTION_CODE,
3944 TAX_STATUS_CODE,
3945 TAX_RATE_ID,
3946 TAX_RATE_CODE,
3947 TAX_RATE
3948 )VALUES (
3949 p_Base_invoice_id,
3950 -- invoice_id
3951 p_invoice_lines_tab(i).line_number,
3952 -- line_number
3953 p_invoice_lines_tab(i).line_type_lookup_code,
3954 -- line_type_lookup_code
3955 p_invoice_lines_tab(i).requester_id,
3956 -- requester_id
3957 p_invoice_lines_tab(i).description,
3958 -- description
3959 'IMPORTED',
3960 -- line_source
3961 p_invoice_lines_tab(i).org_id,
3962 -- org_id
3963 p_invoice_lines_tab(i).line_group_number,
3964 -- line_group_number
3965 p_invoice_lines_tab(i).inventory_item_id,
3966 -- inventory_item_id
3967 p_invoice_lines_tab(i).item_description,
3968 -- item_description
3969 p_invoice_lines_tab(i).serial_number,
3970 -- serial_number
3971 p_invoice_lines_tab(i).manufacturer,
3972 -- manufacturer
3973 p_invoice_lines_tab(i).model_number,
3974 -- model_number
3975 p_invoice_lines_tab(i).warranty_number,
3976 -- warranty_number
3977 l_generate_dists,
3978 -- generate_dists
3979 /* Bug 5400087 */
3980 decode(p_invoice_lines_tab(i).line_type_lookup_code, 'ITEM',
3981 decode(p_invoice_lines_tab(i).match_type, NULL, 'NOT_MATCHED',
3982 p_invoice_lines_tab(i).match_type),
3983 decode(p_invoice_lines_tab(i).rcv_transaction_id, NULL, 'NOT_MATCHED',
3984 'OTHER_TO_RECEIPT')),
3985 /*decode(p_invoice_lines_tab(i).rcv_transaction_id, NULL,
3986 decode(p_invoice_lines_tab(i).po_header_id, NULL,
3987 decode(p_invoice_lines_tab(i).corrected_inv_id,
3988 NULL,'NOT_MATCHED','PRICE_CORRECTION'),
3989 'ITEM_TO_PO'),
3990 decode(p_invoice_lines_tab(i).line_type_lookup_code, 'ITEM',
3991 'ITEM_TO_RECEIPT', 'OTHER_TO_RECEIPT')), */
3992 -- match_type
3993 p_invoice_lines_tab(i).distribution_set_id,
3994 -- distribution_set_id
3995 p_invoice_lines_tab(i).account_segment,
3996 -- account_segment
3997 p_invoice_lines_tab(i).balancing_segment,
3998 -- balancing_segment
3999 p_invoice_lines_tab(i).cost_center_segment,
4000 -- cost_center_segment
4001 p_invoice_lines_tab(i).dist_code_concatenated,
4002 -- overlay_dist_code_concat
4003 p_invoice_lines_tab(i).dist_code_combination_id,
4004 -- default_dist_ccid
4005 p_invoice_lines_tab(i).prorate_across_flag,
4006 -- prorate_across_all_items
4007 p_invoice_lines_tab(i).accounting_date,
4008 -- accounting_date
4009 p_invoice_lines_tab(i).period_name,
4010 -- period_name
4011 p_invoice_lines_tab(i).deferred_acctg_flag,
4012 -- deferred_acctg_flag
4013 p_invoice_lines_tab(i).def_acctg_start_date,
4014 -- def_acctg_start_date
4015 p_invoice_lines_tab(i).def_acctg_end_date,
4016 -- def_acctg_end_date
4017 p_invoice_lines_tab(i).def_acctg_number_of_periods,
4018 -- def_acctg_number_of_periods
4019 p_invoice_lines_tab(i).def_acctg_period_type,
4020 -- def_acctg_period_type
4021 p_set_of_books_id,
4022 -- set_of_books_id
4023 p_invoice_lines_tab(i).amount,
4024 -- amount
4025 p_invoice_lines_tab(i).base_amount,
4026 -- base_amount
4027 NULL, -- rounding_amt
4028 p_invoice_lines_tab(i).quantity_invoiced,
4029 -- quantity_invoiced
4030 p_invoice_lines_tab(i).unit_of_meas_lookup_code,
4031 -- unit_meas_lookup_code
4032 p_invoice_lines_tab(i).unit_price,
4033 -- unit_price
4034 l_wfapproval_status,
4035 -- wfapproval_status
4036 -- p_invoice_lines_tab(i).ussgl_transaction_code,
4037 -- ussgl_transaction_code - Bug 4277744
4038 'N', -- discarded_flag
4039 NULL, -- original_amount
4040 NULL, -- original_base_amount
4041 NULL, -- original_rounding_amt
4042 'N', -- cancelled_flag
4043 p_invoice_lines_tab(i).income_tax_region,
4044 -- income_tax_region
4045 p_invoice_lines_tab(i).type_1099,
4046 -- type_1099
4047 p_invoice_lines_tab(i).stat_amount,
4048 -- stat_amount
4049 NULL,
4050 -- prepay_invoice_id
4051 NULL,
4052 -- prepay_line_number
4053 NULL,
4054 -- invoice_includes_prepay_flag
4055 p_invoice_lines_tab(i).corrected_inv_id, -- corrected_inv_id
4056 p_invoice_lines_tab(i).price_correct_inv_line_num, -- corrected_line_number
4057 p_invoice_lines_tab(i).po_header_id,
4058 -- po_header_id
4059 p_invoice_lines_tab(i).po_line_id,
4060 -- po_line_id
4061 p_invoice_lines_tab(i).po_release_id,
4062 -- po_release_id
4063 p_invoice_lines_tab(i).po_line_location_id,
4064 -- po_line_location_id
4065 p_invoice_lines_tab(i).po_distribution_id,
4066 -- po_distribution_id
4067 p_invoice_lines_tab(i).rcv_transaction_id,
4068 -- rcv_transaction_id
4069 --bug 7344899
4070 p_invoice_lines_tab(i).rcv_shipment_line_id,
4071 --rcv_shipment_line_id
4072 p_invoice_lines_tab(i).final_match_flag,
4073 -- final_match_flag
4074 nvl(p_invoice_lines_tab(i).assets_tracking_flag, 'N'),
4075 -- assets_tracking_flag
4076 p_invoice_lines_tab(i).asset_book_type_code,
4077 -- asset_book_type_code,
4078 p_invoice_lines_tab(i).asset_category_id,
4079 -- asset_category_id
4080 p_invoice_lines_tab(i).project_id,
4081 -- project_id
4082 p_invoice_lines_tab(i).task_id,
4083 -- task_id
4084 p_invoice_lines_tab(i).expenditure_type,
4085 -- expenditure_type
4086 p_invoice_lines_tab(i).expenditure_item_date,
4087 -- expenditure_item_date
4088 p_invoice_lines_tab(i).expenditure_organization_id,
4089 -- expenditure_organization_id
4090 p_invoice_lines_tab(i).pa_quantity,
4091 -- pa_quantity
4092 p_invoice_lines_tab(i).pa_cc_ar_invoice_id,
4093 -- pa_cc_ar_invoice_id
4094 p_invoice_lines_tab(i).pa_cc_ar_invoice_line_num,
4095 -- pa_cc_ar_invoice_line_num
4096 p_invoice_lines_tab(i).pa_cc_processed_code,
4097 -- pa_cc_processed_code
4098 p_invoice_lines_tab(i).award_id,
4099 -- award_id
4100 p_invoice_lines_tab(i).awt_group_id,
4101 -- awt_group_id
4102 p_invoice_lines_tab(i).pay_awt_group_id,
4103 -- pay_awt_group_id --bug6639866
4104 p_invoice_lines_tab(i).reference_1,
4105 -- reference_1
4106 p_invoice_lines_tab(i).reference_2,
4107 -- reference_2
4108 NULL, -- receipt_verified_flag
4109 NULL, -- receipt_required_flag
4110 NULL, -- receipt_missing_flag
4111 p_invoice_lines_tab(i).justification, -- justification --Bug6167068 Populated the colum values from AP_lines_interface rather than putting only NULL vaules
4112 p_invoice_lines_tab(i).expense_group, -- expense_group --Bug6167068
4113 NULL, -- start_expense_date
4114 NULL, -- end_expense_date
4115 p_invoice_lines_tab(i).receipt_currency_code, -- receipt_currency_code --Bug6167068
4116 p_invoice_lines_tab(i).receipt_conversion_rate, -- receipt_conversion_rate --Bug6167068
4117 p_invoice_lines_tab(i).receipt_currency_amount, -- receipt_currency_amount --Bug6167068
4118 NULL, -- daily_amount
4119 NULL, -- web_parameter_id
4120 NULL, -- adjustment_reason
4121 p_invoice_lines_tab(i).merchant_document_number, -- merchant_document_number --Bug6167068
4122 p_invoice_lines_tab(i).merchant_name, -- merchant_name --Bug6167068
4123 p_invoice_lines_tab(i).merchant_reference, -- merchant_reference --Bug6167068
4124 p_invoice_lines_tab(i).merchant_tax_reg_number, -- merchant_tax_reg_number --Bug6167068
4125 p_invoice_lines_tab(i).merchant_taxpayer_id, -- merchant_taxpayer_id --Bug6167068
4126 p_invoice_lines_tab(i).country_of_supply, -- country_of_supply --Bug6167068
4127 p_invoice_lines_tab(i).credit_card_trx_id,
4128 -- credit_card_trx_id
4129 p_invoice_lines_tab(i).company_prepaid_invoice_id, -- company_prepaid_invoice_id --Bug6167068
4130 p_invoice_lines_tab(i).cc_reversal_flag, -- cc_reversal_flag --Bug6167068
4131 AP_IMPORT_INVOICES_PKG.g_inv_sysdate,
4132 -- creation_date
4133 p_default_last_updated_by,
4134 -- created_by
4135 p_default_last_updated_by,
4136 -- last_updated_by
4137 AP_IMPORT_INVOICES_PKG.g_inv_sysdate,
4138 -- last_update_date
4139 p_default_last_update_login,
4140 -- last_update_login
4141 AP_IMPORT_INVOICES_PKG.g_program_application_id,
4142 -- program_application_id
4143 AP_IMPORT_INVOICES_PKG.g_program_id,
4144 -- program_id
4145 AP_IMPORT_INVOICES_PKG.g_inv_sysdate,
4146 -- program_update_date
4147 AP_IMPORT_INVOICES_PKG.g_conc_request_id,
4148 -- request_id
4149 p_invoice_lines_tab(i).attribute_category,
4150 -- attribute_category
4151 p_invoice_lines_tab(i).attribute1,
4152 -- attribute1
4153 p_invoice_lines_tab(i).attribute2,
4154 -- attribute2
4155 p_invoice_lines_tab(i).attribute3,
4156 -- attribute3
4157 p_invoice_lines_tab(i).attribute4,
4158 -- attribute4
4159 p_invoice_lines_tab(i).attribute5,
4160 -- attribute5
4161 p_invoice_lines_tab(i).attribute6,
4162 -- attribute6
4163 p_invoice_lines_tab(i).attribute7,
4164 -- attribute7
4165 p_invoice_lines_tab(i).attribute8,
4166 -- attribute8
4167 p_invoice_lines_tab(i).attribute9,
4168 -- attribute9
4169 p_invoice_lines_tab(i).attribute10,
4170 -- attribute10
4171 p_invoice_lines_tab(i).attribute11,
4172 -- attribute11
4173 p_invoice_lines_tab(i).attribute12,
4174 -- attribute12
4175 p_invoice_lines_tab(i).attribute13,
4176 -- attribute13
4177 p_invoice_lines_tab(i).attribute14,
4178 -- attribute14
4179 p_invoice_lines_tab(i).attribute15,
4180 -- attribute15
4181 p_invoice_lines_tab(i).global_attribute_category,
4182 -- global_attribute_category
4183 p_invoice_lines_tab(i).global_attribute1,
4184 -- global_attribute1
4185 p_invoice_lines_tab(i).global_attribute2,
4186 -- global_attribute2
4187 p_invoice_lines_tab(i).global_attribute3,
4188 -- global_attribute3
4189 p_invoice_lines_tab(i).global_attribute4,
4190 -- global_attribute4
4191 p_invoice_lines_tab(i).global_attribute5,
4192 -- global_attribute5
4193 p_invoice_lines_tab(i).global_attribute6,
4194 -- global_attribute6
4195 p_invoice_lines_tab(i).global_attribute7,
4196 -- global_attribute7
4197 p_invoice_lines_tab(i).global_attribute8,
4198 -- global_attribute8
4199 p_invoice_lines_tab(i).global_attribute9,
4200 -- global_attribute9
4201 p_invoice_lines_tab(i).global_attribute10,
4202 -- global_attribute10
4203 p_invoice_lines_tab(i).global_attribute11,
4204 -- global_attribute11
4205 p_invoice_lines_tab(i).global_attribute12,
4206 -- global_attribute12
4207 p_invoice_lines_tab(i).global_attribute13,
4208 -- global_attribute13
4209 p_invoice_lines_tab(i).global_attribute14,
4210 -- global_attribute14
4211 p_invoice_lines_tab(i).global_attribute15,
4212 -- global_attribute15
4213 p_invoice_lines_tab(i).global_attribute16,
4214 -- global_attribute16
4215 p_invoice_lines_tab(i).global_attribute17,
4216 -- global_attribute17
4217 p_invoice_lines_tab(i).global_attribute18,
4218 -- global_attribute18
4219 p_invoice_lines_tab(i).global_attribute19,
4220 -- global_attribute19
4221 p_invoice_lines_tab(i).global_attribute20,
4222 -- global_attribute20
4223 p_invoice_lines_tab(i).control_amount,
4224 -- control_amount
4225 p_invoice_lines_tab(i).assessable_value,
4226 -- assessable_value
4227 p_invoice_lines_tab(i).primary_intended_use,
4228 -- primary_intended_use
4229 nvl(p_invoice_lines_tab(i).ship_to_location_id, l_ship_to_location_id),
4230 -- ship_to_location_id
4231 p_invoice_lines_tab(i).product_type,
4232 -- product_type
4233 p_invoice_lines_tab(i).product_category,
4234 -- product_category
4235 p_invoice_lines_tab(i).product_fisc_classification,
4236 -- product_fisc_classification
4237 p_invoice_lines_tab(i).user_defined_fisc_class,
4238 -- user_defined_fisc_class
4239 p_invoice_lines_tab(i).trx_business_category,
4240 -- application_id
4241 p_invoice_lines_tab(i).application_id,
4242 -- product_table
4243 p_invoice_lines_tab(i).product_table,
4244 -- reference_key1
4245 p_invoice_lines_tab(i).reference_key1,
4246 -- reference_key2
4247 p_invoice_lines_tab(i).reference_key2,
4248 -- reference_key3
4249 p_invoice_lines_tab(i).reference_key3,
4250 -- reference_key4
4251 p_invoice_lines_tab(i).reference_key4,
4252 -- reference_key5
4253 p_invoice_lines_tab(i).reference_key5,
4254 -- purchasing_category_id
4255 p_invoice_lines_tab(i).purchasing_category_id,
4256 -- cost_factor_id
4257 p_invoice_lines_tab(i).cost_factor_id,
4258 -- source_application_id
4259 p_invoice_lines_tab(i).source_application_id,
4260 -- source_entity_code
4261 p_invoice_lines_tab(i).source_entity_code,
4262 --source_event_class_code
4263 p_invoice_lines_tab(i).source_event_class_code,
4264 --source_trx_id
4265 p_invoice_lines_tab(i).source_trx_id,
4266 --source_line_id
4267 p_invoice_lines_tab(i).source_line_id,
4268 --source_trx_level_type
4269 p_invoice_lines_tab(i).source_trx_level_type,
4270 --tax_classification_code
4271 p_invoice_lines_tab(i).tax_classification_code,
4272 --retained_amount
4273 p_invoice_lines_tab(i).retained_amount,
4274 --retained_amount_remaining
4275 (-p_invoice_lines_tab(i).retained_amount),
4276 --tax_regime_code
4277 p_invoice_lines_tab(i).tax_regime_code,
4278 --tax
4279 p_invoice_lines_tab(i).tax,
4280 --tax_jurisdiction_code
4281 p_invoice_lines_tab(i).tax_jurisdiction_code,
4282 --tax_status_code
4283 p_invoice_lines_tab(i).tax_status_code,
4284 --tax_rate_id
4285 p_invoice_lines_tab(i).tax_rate_id,
4286 --tax_rate_code
4287 p_invoice_lines_tab(i).tax_rate_code,
4288 --tax_rate
4289 p_invoice_lines_tab(i).tax_rate);
4290
4291 -- END IF; Commented for bug 6010950
4292
4293 END LOOP;
4294 End; -- end of insert
4295
4296 RETURN( TRUE );
4297 EXCEPTION
4298 WHEN OTHERS THEN
4299 debug_info := debug_info || '->exception';
4300 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4301 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
4302 END IF;
4303
4304 IF (SQLCODE < 0) THEN
4305 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4306 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch, SQLERRM);
4307 END IF;
4308 END IF;
4309
4310 RETURN (FALSE);
4311
4312 END insert_ap_invoice_lines;
4313
4314 /*=========================================================================*/
4315 /* */
4316 /* Private Function Create_Lines */
4317 /* Program Flow: */
4318 /* 1. Insert interface lines data into transaction lines table */
4319 /* 2. Allocate base amount rounding for lines inserted into transaction */
4320 /* table */
4321 /* 3. Loop through lines and either match to PO/RCV, produce price */
4322 /* correction or create allocation rules. */
4323 /* Parameters: */
4324 /* */
4325 /* p_batch_id */
4326 /* p_base_invoice_id */
4327 /* p_invoice_lines_tab */
4328 /* p_base_currency_code */
4329 /* p_set_of_books_id */
4330 /* p_chart_of_accounts_id */
4331 /* p_default_last_updated_by */
4332 /* p_default_last_update_login */
4333 /* p_calling_sequence */
4334 /* */
4335 /*=========================================================================*/
4336
4337 FUNCTION Create_Lines(
4338 p_batch_id IN NUMBER,
4339 p_base_invoice_id IN NUMBER,
4340 p_invoice_lines_tab IN
4341 AP_IMPORT_INVOICES_PKG.t_lines_table,
4342 p_base_currency_code IN VARCHAR2,
4343 p_set_of_books_id IN NUMBER,
4344 p_approval_workflow_flag IN VARCHAR2,
4345 p_tax_only_flag IN VARCHAR2,
4346 p_tax_only_rcv_matched_flag IN VARCHAR2,
4347 p_default_last_updated_by IN NUMBER,
4348 p_default_last_update_login IN NUMBER,
4349 p_calling_sequence IN VARCHAR2)
4350 RETURN BOOLEAN
4351 IS
4352 create_lines_failure EXCEPTION;
4353 current_calling_sequence VARCHAR2(2000);
4354 debug_info VARCHAR2(500);
4355 l_debug_context VARCHAR2(1000);
4356 l_round_amt_exist BOOLEAN := FALSE;
4357 l_rounded_line_num NUMBER;
4358 l_rounded_amt NUMBER := 0;
4359 l_error_code VARCHAR2(30);
4360 i BINARY_INTEGER := 0;
4361 l_overbill_flag VARCHAR2(1) := 'N';
4362 l_quantity_outstanding NUMBER;
4363 l_quantity_ordered NUMBER;
4364 l_qty_already_billed NUMBER;
4365 l_amount_outstanding NUMBER;
4366 l_amount_ordered NUMBER;
4367 l_amt_already_billed NUMBER;
4368
4369 l_modified_line_rounding_amt NUMBER; --6892789
4370 l_base_amt NUMBER; --6892789
4371 l_round_inv_line_numbers AP_INVOICES_UTILITY_PKG.inv_line_num_tab_type; --6892789
4372
4373 BEGIN
4374 -- Update the calling sequence
4375
4376 current_calling_sequence := 'Create_lines<-'||P_calling_sequence;
4377
4378 --------------------------------------------------------------------------
4379 -- Step 1
4380 -- Call API that Bulk insert invoice lines regardless of line type.
4381 --------------------------------------------------------------------------
4382
4383 debug_info := '(Create lines 1) Call API to Insert all the lines ';
4384 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4385 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
4386 END IF;
4387
4388 IF ( insert_ap_invoice_lines(
4389 p_base_invoice_id => p_base_invoice_id,
4390 p_invoice_lines_tab => p_invoice_lines_tab,
4391 p_set_of_books_id => p_set_of_books_id,
4392 p_approval_workflow_flag => p_approval_workflow_flag,
4393 p_tax_only_flag => p_tax_only_flag,
4394 p_tax_only_rcv_matched_flag => p_tax_only_rcv_matched_flag,
4395 p_default_last_updated_by => p_default_last_updated_by,
4396 p_default_last_update_login => p_default_last_update_login,
4397 p_calling_sequence => current_calling_sequence )<>TRUE) THEN
4398
4399 debug_info := debug_info || 'exceptions';
4400 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4401 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
4402 RAISE create_lines_failure;
4403 END IF;
4404 END IF;
4405
4406 --------------------------------------------------------------------------
4407 -- Step 2
4408 -- Call API to do base amount rounding for x_base_invoice_id in
4409 -- ap_invoice_lines core transaction table
4410 --------------------------------------------------------------------------
4411
4412 debug_info := '(Create lines 2) Call Utility function to round the line '||
4413 ' before create distributions';
4414 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4415 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
4416 END IF;
4417
4418 /* modifying following code as per the bug 6892789 as there is a chance
4419 that line base amt goes to -ve value (line amount being +ve) so in such
4420 case, adjust line base amount upto zero and adjust the remaing amount in
4421 another line having next max amount */
4422
4423 -- get the lines which can be adjusted
4424 l_round_amt_exist := AP_INVOICES_UTILITY_PKG.round_base_amts(
4425 X_Invoice_Id => p_base_invoice_id,
4426 X_Reporting_Ledger_Id => NULL,
4427 X_Rounded_Line_Numbers => l_round_inv_line_numbers,
4428 X_Rounded_Amt => l_rounded_amt,
4429 X_Debug_Info => debug_info,
4430 X_Debug_Context => l_debug_context,
4431 X_Calling_sequence => current_calling_sequence);
4432
4433 -- adjustment required and there exist line numbers that can be adjusted
4434 IF ( l_round_amt_exist AND l_round_inv_line_numbers.count > 0 ) THEN
4435 -- iterate throgh lines until there is no need to adjust
4436 for i in 1 .. l_round_inv_line_numbers.count
4437 loop
4438 IF l_rounded_amt <> 0 THEN
4439 -- get the existing base amount for the selected line
4440 select base_amount
4441 INTO l_base_amt
4442 FROM AP_INVOICE_LINES
4443 WHERE invoice_id = p_base_invoice_id
4444 AND line_number = l_round_inv_line_numbers(i);
4445
4446 -- get the calculated adjusted base amount and rounding amount
4447 -- get rounding amount for the next line if required
4448 l_base_amt := AP_APPROVAL_PKG.get_adjusted_base_amount(
4449 p_base_amount => l_base_amt,
4450 p_rounding_amt => l_modified_line_rounding_amt,
4451 p_next_line_rounding_amt => l_rounded_amt);
4452
4453 -- update the calculatd base amount, rounding amount
4454 UPDATE AP_INVOICE_LINES
4455 SET base_amount = l_base_amt,
4456 rounding_amt = ABS( NVL(l_modified_line_rounding_amt, 0) ),
4457 last_update_date = SYSDATE,
4458 last_updated_by = FND_GLOBAL.user_id,
4459 last_update_login = FND_GLOBAL.login_id
4460 WHERE invoice_id = p_base_invoice_id
4461 AND line_number = l_round_inv_line_numbers(i);
4462 ELSE
4463 -- adjustment not required or there are no lines that can be adjusted
4464 EXIT;
4465 END IF;
4466 end loop;
4467
4468 END IF;
4469
4470 --------------------------------------------------------------------------
4471 -- Step 3
4472 -- Loop through lines and call matching package if line is to be matched
4473 -- or call allocations package if allocation rule/lines need to be created
4474 --------------------------------------------------------------------------
4475 debug_info := '(Create lines 3) Call Matching or Allocations';
4476
4477 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4478 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
4479 END IF;
4480
4481 BEGIN
4482
4483 FOR i IN p_invoice_lines_tab.FIRST..p_invoice_lines_tab.LAST LOOP
4484
4485 IF (p_invoice_lines_tab(i).line_type_lookup_code = 'ITEM') THEN
4486
4487 IF (p_invoice_lines_tab(i).po_line_location_id IS NOT NULL) THEN
4488 debug_info := '(Create Lines 3.1) Check for quantity overbill '
4489 ||'for PO Shipment';
4490
4491 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
4492 AP_IMPORT_UTILITIES_PKG.Print(
4493 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
4494 END IF;
4495
4496 IF (AP_IMPORT_UTILITIES_PKG.get_overbill_for_shipment(
4497 p_invoice_lines_tab(i).po_line_location_id, -- IN
4498 p_invoice_lines_tab(i).quantity_invoiced, -- IN
4499 p_invoice_lines_tab(i).amount, -- IN
4500 l_overbill_flag, -- OUT NOCOPY
4501 l_quantity_outstanding, -- OUT NOCOPY
4502 l_quantity_ordered, -- OUT NOCOPY
4503 l_qty_already_billed, -- OUT NOCOPY
4504 l_amount_outstanding, -- OUT NOCOPY
4505 l_amount_ordered, -- OUT NOCOPY
4506 l_amt_already_billed, -- OUT NOCOPY
4507 current_calling_sequence) <> TRUE) THEN
4508
4509 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4510 AP_IMPORT_UTILITIES_PKG.Print(
4511 AP_IMPORT_INVOICES_PKG.g_debug_switch,
4512 'get_overbill_for_shipment<-'||current_calling_sequence);
4513 END IF;
4514 RAISE create_lines_failure;
4515 END IF;
4516
4517 END IF;
4518
4519 debug_info := '(Create lines 4) Calling Matching API';
4520
4521 ap_matching_utils_pkg.match_invoice_line(
4522 P_Invoice_Id => p_base_invoice_id,
4523 P_Invoice_Line_Number => p_invoice_lines_tab(i).line_number,
4524 P_Overbill_Flag => l_overbill_flag,
4525 P_Calling_Sequence => current_calling_sequence);
4526
4527 ELSIF (p_invoice_lines_tab(i).line_type_lookup_code <> 'ITEM' AND
4528 NVL(p_invoice_lines_tab(i).prorate_across_flag, 'N') = 'Y' AND
4529 p_invoice_lines_tab(i).line_group_number IS NULL) THEN
4530
4531 IF (NOT (ap_allocation_rules_pkg.insert_fully_prorated_rule(
4532 p_base_invoice_id,
4533 p_invoice_lines_tab(i).line_number,
4534 l_error_code))) THEN
4535
4536 debug_info := '(Create lines 5) Error encountered: '||l_error_code;
4537 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4538 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
4539 END IF;
4540 RETURN(FALSE);
4541
4542 END IF;
4543
4544 ELSIF (p_invoice_lines_tab(i).line_type_lookup_code <> 'ITEM' AND
4545 NVL(p_invoice_lines_tab(i).prorate_across_flag, 'N') = 'Y' AND
4546 p_invoice_lines_tab(i).line_group_number IS NOT NULL) THEN
4547
4548 IF (NOT (ap_allocation_rules_pkg.insert_from_line_group_number(
4549 p_base_invoice_id,
4550 p_invoice_lines_tab(i).line_number,
4551 l_error_code))) THEN
4552
4553 debug_info := '(Create lines 6) Error encountered: '||l_error_code;
4554
4555 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4556 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
4557 END IF;
4558
4559 RETURN(FALSE);
4560 END IF;
4561
4562 END IF;
4563
4564 END LOOP;
4565
4566 END;
4567
4568 RETURN( TRUE );
4569 EXCEPTION
4570 WHEN OTHERS THEN
4571
4572 debug_info := debug_info || '->exception';
4573 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4574 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
4575 END IF;
4576
4577 IF (SQLCODE < 0) THEN
4578 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4579 Print( AP_IMPORT_INVOICES_PKG.g_debug_switch, SQLERRM);
4580 END IF;
4581 END IF;
4582 RETURN (FALSE);
4583 END Create_lines;
4584
4585 FUNCTION insert_holds(
4586 p_base_invoice_id IN NUMBER,
4587 p_hold_code IN VARCHAR2,
4588 p_hold_reason IN VARCHAR2,
4589 p_hold_future_payments_flag IN VARCHAR2,
4590 p_supplier_hold_reason IN VARCHAR2,
4591 p_invoice_amount_limit IN NUMBER,
4592 p_invoice_base_amount IN NUMBER,
4593 p_last_updated_by IN NUMBER,
4594 P_calling_sequence IN VARCHAR2)
4595 RETURN BOOLEAN
4596 IS
4597 current_calling_sequence VARCHAR2(2000);
4598 debug_info VARCHAR2(500);
4599
4600 BEGIN
4601 -- Update the calling sequence
4602
4603 current_calling_sequence := 'insert_holds<-'||P_calling_sequence;
4604
4605 --------------------------------------------------------------------------
4606 -- Step 1
4607 -- Insert invoice holds FROM the import batch
4608 --------------------------------------------------------------------------
4609
4610 debug_info := '(Insert Holds 1) Insert invoice holds FROM the import batch';
4611 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4612 AP_IMPORT_UTILITIES_PKG.Print(
4613 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4614 END IF;
4615
4616 IF (p_hold_code is NOT NULL) THEN
4617 ap_holds_pkg.insert_single_hold(
4618 X_invoice_id =>p_base_invoice_id,
4619 X_hold_lookup_code =>p_hold_code,
4620 X_hold_type =>'INVOICE HOLD REASON',
4621 X_hold_reason =>p_hold_reason,
4622 X_held_by =>p_last_updated_by,
4623 X_calling_sequence =>current_calling_sequence);
4624 END IF;
4625
4626 ---------------------------------------------------------------------------
4627 -- Step 2
4628 -- Insert Suppler's holds
4629 ---------------------------------------------------------------------------
4630
4631 debug_info := '(Insert Holds 2) Insert Suppler holds';
4632 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4633 AP_IMPORT_UTILITIES_PKG.Print(
4634 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4635 END IF;
4636
4637 iF (NVL(p_hold_future_payments_flag,'N') = 'Y') THEN
4638 ap_holds_pkg.insert_single_hold(
4639 X_invoice_id =>p_base_invoice_id,
4640 --Bug 7448784 Changed 'Vendor' to 'VENDOR'
4641 X_hold_lookup_code =>'VENDOR',
4642 X_hold_type =>'INVOICE HOLD REASON',
4643 X_hold_reason =>p_supplier_hold_reason,
4644 X_held_by =>5,
4645 X_calling_sequence =>current_calling_sequence);
4646 END IF;
4647
4648 IF (p_invoice_base_amount > p_invoice_amount_limit) THEN
4649
4650 --------------------------------------------------------------------------
4651 -- Step 3
4652 -- Insert Hold IF invoice_base_amount > invoice_amount_limit
4653 --------------------------------------------------------------------------
4654 debug_info := '(Insert Holds 3) Insert Hold IF invoice_base_amount > '||
4655 'invoice_amount_limit';
4656 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4657 AP_IMPORT_UTILITIES_PKG.Print(
4658 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4659 END IF;
4660
4661 ap_holds_pkg.insert_single_hold(
4662 X_invoice_id =>p_base_invoice_id,
4663 X_hold_lookup_code =>'AMOUNT',
4664 X_hold_type =>'INVOICE HOLD REASON',
4665 X_hold_reason =>p_supplier_hold_reason,
4666 X_held_by =>5,
4667 X_calling_sequence =>current_calling_sequence);
4668 END IF;
4669
4670 RETURN(TRUE);
4671
4672 EXCEPTION
4673 WHEN OTHERS THEN
4674 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4675 AP_IMPORT_UTILITIES_PKG.Print(
4676 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4677 END IF;
4678
4679 IF (SQLCODE < 0) THEN
4680 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4681 AP_IMPORT_UTILITIES_PKG.Print(
4682 AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
4683 END IF;
4684 END IF;
4685
4686 RETURN (FALSE);
4687
4688 END insert_holds;
4689
4690 /*=========================================================================*/
4691 /* */
4692 /* Function Get_tax_only_rcv_matched_flag */
4693 /* This function is used to determine if the invoice is a tax only and if */
4694 /* the existing tax lines are rcv matched and no tax information is */
4695 /* populated for the lines. */
4696 /* */
4697 /* Parameters */
4698 /* p_invoice_id */
4699 /* */
4700 /*=========================================================================*/
4701
4702 FUNCTION get_tax_only_rcv_matched_flag(
4703 P_invoice_id IN NUMBER) RETURN VARCHAR2
4704
4705 IS
4706
4707 l_tax_only_rcv_matched_flag VARCHAR2(1);
4708
4709 BEGIN
4710
4711 --------------------------------------------------------------------------
4712 -- Select Y if invoice is tax only and tax lines are RCV matched and no
4713 -- tax line has tax info populated
4714 --------------------------------------------------------------------------
4715
4716 IF (p_invoice_id IS NOT NULL) THEN
4717
4718 BEGIN
4719 SELECT 'N'
4720 INTO l_tax_only_rcv_matched_flag
4721 FROM ap_invoice_lines_interface
4722 WHERE invoice_id = p_invoice_id
4723 AND (line_type_lookup_code <> 'TAX' OR
4724 (line_type_lookup_code = 'TAX' AND
4725 rcv_transaction_id IS NULL AND
4726 (tax_regime_code IS NOT NULL OR
4727 tax IS NOT NULL OR
4728 tax_jurisdiction_code IS NOT NULL OR
4729 tax_status_code IS NOT NULL OR
4730 tax_rate_id IS NOT NULL OR
4731 tax_rate_code IS NOT NULL OR
4732 tax_rate IS NOT NULL OR
4733 incl_in_taxable_line_flag IS NOT NULL OR
4734 tax_classification_code is not null))) --bug6255826
4735 AND ROWNUM = 1;
4736
4737 EXCEPTION
4738 WHEN NO_DATA_FOUND THEN
4739 l_tax_only_rcv_matched_flag := 'Y';
4740 END;
4741
4742 END IF;
4743
4744 RETURN l_tax_only_rcv_matched_flag;
4745
4746 END get_tax_only_rcv_matched_flag;
4747
4748 /*=========================================================================*/
4749 /* */
4750 /* Function Get_tax_only_flag */
4751 /* This function is used to determine if the invoice is a tax only one */
4752 /* This flag will be used by the eTax validate_default_import API to */
4753 /* determine how the global temporary tables for the tax lines should be */
4754 /* populated. */
4755 /* */
4756 /* Parameters */
4757 /* p_invoice_id */
4758 /* */
4759 /*=========================================================================*/
4760
4761 FUNCTION get_tax_only_flag(
4762 P_invoice_id IN NUMBER) RETURN VARCHAR2
4763
4764 IS
4765
4766 l_tax_only_flag VARCHAR2(1);
4767
4768 BEGIN
4769
4770 --------------------------------------------------------------------------
4771 -- Select Y if invoice is tax only
4772 --------------------------------------------------------------------------
4773 IF (p_invoice_id IS NOT NULL) THEN
4774
4775 BEGIN
4776 SELECT 'N'
4777 INTO l_tax_only_flag
4778 FROM ap_invoice_lines_interface
4779 WHERE invoice_id = p_invoice_id
4780 AND line_type_lookup_code <> 'TAX'
4781 AND ROWNUM = 1;
4782
4783 EXCEPTION
4784 WHEN NO_DATA_FOUND THEN
4785 l_tax_only_flag := 'Y';
4786 END;
4787 END IF;
4788
4789 RETURN l_tax_only_flag;
4790
4791 END get_tax_only_flag;
4792
4793 /* 5039042. Function for Checking if Distribution Generation Event is rgeistered for the
4794 source application */
4795 FUNCTION Is_Product_Registered(P_Application_Id IN NUMBER,
4796 X_Registration_Api OUT NOCOPY VARCHAR2,
4797 X_Registration_View OUT NOCOPY VARCHAR2,
4798 P_Calling_Sequence IN VARCHAR2)
4799 RETURN BOOLEAN IS
4800
4801 l_debug_info VARCHAR2(1000);
4802 l_curr_calling_sequence VARCHAR2(2000);
4803
4804 BEGIN
4805
4806 l_curr_calling_sequence := 'Is_Product_Registered <-'||p_calling_sequence;
4807 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4808 AP_IMPORT_UTILITIES_PKG.Print(
4809 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_curr_calling_sequence);
4810 END IF;
4811
4812 l_debug_info := 'Check if the other application is registered for Distribution Generation';
4813 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4814 AP_IMPORT_UTILITIES_PKG.Print(
4815 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_debug_info);
4816 END IF;
4817
4818
4819 BEGIN
4820
4821 SELECT registration_api,
4822 registration_view
4823 INTO x_registration_api,
4824 x_registration_view
4825 FROM ap_product_registrations
4826 WHERE application_id = 200
4827 AND reg_application_id = p_application_id
4828 AND registration_event_type = 'DISTRIBUTION_GENERATION';
4829
4830 EXCEPTION WHEN NO_DATA_FOUND THEN
4831 x_registration_view := NULL;
4832 x_registration_api := NULL;
4833 RETURN(FALSE);
4834 END;
4835
4836 RETURN(TRUE);
4837
4838 EXCEPTION
4839 WHEN OTHERS then
4840 IF (SQLCODE <> -20001) THEN
4841 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4842 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4843 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
4844 FND_MESSAGE.SET_TOKEN('PARAMETERS',
4845 ' Application Id = ' || to_char(P_Application_Id) );
4846 END IF;
4847 APP_EXCEPTION.RAISE_EXCEPTION;
4848
4849 END Is_Product_Registered;
4850
4851 -- Bug 5448579. This function will be used for caching org_id, name
4852 FUNCTION Cache_Org_Id_Name (
4853 P_Moac_Org_Table OUT NOCOPY AP_IMPORT_INVOICES_PKG.moac_ou_tab_type,
4854 P_Fsp_Org_Table OUT NOCOPY AP_IMPORT_INVOICES_PKG.fsp_org_tab_type,
4855 P_Calling_Sequence IN VARCHAR2 )
4856
4857 RETURN BOOLEAN IS
4858
4859 CURSOR moac_org IS
4860 SELECT organization_id,
4861 mo_global.get_ou_name(organization_id)
4862 FROM Mo_Glob_Org_Access_Tmp;
4863
4864 CURSOR fsp_org IS
4865 SELECT org_id
4866 FROM Financials_System_Parameters;
4867
4868 l_debug_info VARCHAR2(1000);
4869 l_curr_calling_sequence VARCHAR2(2000);
4870
4871
4872 BEGIN
4873
4874 l_curr_calling_sequence := 'Cache_Org_Id_Name <- '||P_calling_sequence;
4875 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4876 AP_IMPORT_UTILITIES_PKG.Print(
4877 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_curr_calling_sequence);
4878 END IF;
4879
4880 l_debug_info := 'Caching Org_id , Name from MO: Security Profile';
4881 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4882 AP_IMPORT_UTILITIES_PKG.Print(
4883 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_debug_info);
4884 END IF;
4885
4886 OPEN moac_org;
4887 FETCH moac_org
4888 BULK COLLECT INTO P_Moac_Org_Table;
4889 CLOSE moac_org;
4890
4891 l_debug_info := 'Caching Org_id from Financials Systems';
4892 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4893 AP_IMPORT_UTILITIES_PKG.Print(
4894 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_debug_info);
4895 END IF;
4896
4897 OPEN fsp_org;
4898 FETCH fsp_org
4899 BULK COLLECT INTO P_Fsp_Org_Table;
4900 CLOSE fsp_org;
4901
4902
4903 RETURN(TRUE);
4904
4905 EXCEPTION
4906 WHEN OTHERS then
4907 IF (SQLCODE <> -20001) THEN
4908 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4909 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4910 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
4911 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
4912 END IF;
4913 APP_EXCEPTION.RAISE_EXCEPTION;
4914
4915 END Cache_Org_Id_Name;
4916
4917 -- Bug 5448579. This function will be used for checking term claendar based on terms_id
4918 PROCEDURE Check_For_Calendar_Term
4919 (p_terms_id IN number,
4920 p_terms_date IN date,
4921 p_no_cal IN OUT NOCOPY varchar2,
4922 p_calling_sequence IN varchar2) IS
4923
4924 CURSOR c IS
4925 SELECT calendar
4926 FROM ap_terms,
4927 ap_terms_lines
4928 WHERE ap_terms.term_id = ap_terms_lines.term_id
4929 AND ap_terms.term_id = p_terms_id
4930 AND ap_terms_lines.calendar is not null;
4931
4932 l_calendar VARCHAR2(30);
4933 l_cal_exists VARCHAR2(1);
4934 l_debug_info VARCHAR2(100);
4935 l_curr_calling_sequence VARCHAR2(2000);
4936
4937 BEGIN
4938 -- Update the calling sequence
4939 --
4940 l_curr_calling_sequence :=
4941 'AP_IMPORT_UTILITIES_PKG.Check_For_Calendar_Term<-'||p_calling_sequence;
4942 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4943 AP_IMPORT_UTILITIES_PKG.Print(
4944 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_curr_calling_sequence);
4945 END IF;
4946
4947 --------------------------------------------------------
4948 l_debug_info := 'OPEN cursor c';
4949 --------------------------------------------------------
4950 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4951 AP_IMPORT_UTILITIES_PKG.Print(
4952 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_debug_info);
4953 END IF;
4954
4955 l_cal_exists := '';
4956 OPEN c;
4957
4958 LOOP
4959 --------------------------------------------------------
4960 l_debug_info := 'Fetch cursor C';
4961 --------------------------------------------------------
4962 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4963 AP_IMPORT_UTILITIES_PKG.Print(
4964 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_debug_info);
4965 END IF;
4966
4967 FETCH c INTO l_calendar;
4968 EXIT WHEN c%NOTFOUND;
4969
4970 --------------------------------------------------------
4971 l_debug_info := 'Check for calendar';
4972 --------------------------------------------------------
4973 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4974 AP_IMPORT_UTILITIES_PKG.Print(
4975 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_debug_info);
4976 END IF;
4977
4978 BEGIN
4979
4980 -- Bug1769230 Added truncate function to eliminate time part
4981 -- from p_terms_date variable.
4982 SELECT 'Y'
4983 INTO l_cal_exists
4984 FROM ap_other_periods aop,
4985 ap_other_period_types aopt
4986 WHERE aopt.period_type = l_calendar
4987 AND aopt.module = 'PAYMENT TERMS'
4988 AND aopt.module = aop.module -- bug 2902681
4989 AND aopt.period_type = aop.period_type
4990 AND aop.start_date <= trunc(p_terms_date)
4991 AND aop.end_date >= trunc(p_terms_date);
4992 EXCEPTION
4993 WHEN NO_DATA_FOUND then
4994 null;
4995 END;
4996
4997 if (l_cal_exists <> 'Y') or (l_cal_exists is null) then
4998 p_no_cal := 'Y';
4999 return;
5000 end if;
5001
5002 END LOOP;
5003 --------------------------------------------------------
5004 l_debug_info := 'CLOSE cursor c';
5005 --------------------------------------------------------
5006 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
5007 AP_IMPORT_UTILITIES_PKG.Print(
5008 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_debug_info);
5009 END IF;
5010
5011 CLOSE c;
5012
5013 EXCEPTION
5014 WHEN OTHERS THEN
5015 IF (SQLCODE <> -20001) THEN
5016 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
5017 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
5018 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
5019 FND_MESSAGE.SET_TOKEN('PARAMETERS',
5020 'Payment Terms = '|| p_terms_id
5021 ||' Terms date = '||to_char(p_terms_date));
5022 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
5023 END IF;
5024 APP_EXCEPTION.RAISE_EXCEPTION;
5025 End Check_For_Calendar_Term;
5026
5027 -- Bug 5448579. This function will be used for caching Pay Group
5028 FUNCTION Cache_Pay_Group (
5029 P_Pay_Group_Table OUT NOCOPY AP_IMPORT_INVOICES_PKG.pay_group_tab_type,
5030 P_Calling_Sequence IN VARCHAR2)
5031 RETURN BOOLEAN IS
5032
5033 CURSOR pay_group IS
5034 SELECT lookup_code
5035 FROM po_lookup_codes
5036 WHERE lookup_type = 'PAY GROUP'
5037 AND DECODE(SIGN(NVL(inactive_date,
5038 AP_IMPORT_INVOICES_PKG.g_inv_sysdate) -
5039 AP_IMPORT_INVOICES_PKG.g_inv_sysdate),
5040 -1,'','*') = '*';
5041
5042 l_debug_info VARCHAR2(1000);
5043 l_curr_calling_sequence VARCHAR2(2000);
5044
5045
5046 BEGIN
5047
5048 l_curr_calling_sequence := 'Cache_Pay_group <- '||P_calling_sequence;
5049 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
5050 AP_IMPORT_UTILITIES_PKG.Print(
5051 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_curr_calling_sequence);
5052 END IF;
5053
5054 l_debug_info := 'Caching Pay Group from PO Lookup Codes';
5055 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
5056 AP_IMPORT_UTILITIES_PKG.Print(
5057 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_debug_info);
5058 END IF;
5059
5060 OPEN pay_group;
5061 FETCH pay_group
5062 BULK COLLECT INTO P_Pay_Group_Table;
5063 CLOSE pay_group;
5064
5065 RETURN(TRUE);
5066
5067 EXCEPTION
5068 WHEN OTHERS then
5069 IF (SQLCODE <> -20001) THEN
5070 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
5071 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
5072 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
5073 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
5074 END IF;
5075 APP_EXCEPTION.RAISE_EXCEPTION;
5076
5077 END Cache_Pay_Group;
5078
5079 -- Bug 5448579. This function will be used for caching Payment Method from IBY
5080 FUNCTION Cache_Payment_Method (
5081 P_Payment_Method_Table OUT NOCOPY AP_IMPORT_INVOICES_PKG.payment_method_tab_type,
5082 P_Calling_Sequence IN VARCHAR2)
5083 RETURN BOOLEAN IS
5084
5085 CURSOR payment_method IS
5086 SELECT payment_method_code
5087 FROM IBY_PAYMENT_METHODS_VL;
5088
5089 l_debug_info VARCHAR2(1000);
5090 l_curr_calling_sequence VARCHAR2(2000);
5091
5092
5093 BEGIN
5094
5095 l_curr_calling_sequence := 'Cache_Payment_Method <- '||P_calling_sequence;
5096 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
5097 AP_IMPORT_UTILITIES_PKG.Print(
5098 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_curr_calling_sequence);
5099 END IF;
5100
5101 l_debug_info := 'Caching Payment Method from IBY';
5102 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
5103 AP_IMPORT_UTILITIES_PKG.Print(
5104 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_debug_info);
5105 END IF;
5106
5107 OPEN payment_method;
5108 FETCH payment_method
5109 BULK COLLECT INTO P_Payment_Method_Table;
5110 CLOSE payment_method;
5111
5112 RETURN(TRUE);
5113
5114 EXCEPTION
5115 WHEN OTHERS then
5116 IF (SQLCODE <> -20001) THEN
5117 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
5118 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
5119 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
5120 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
5121 END IF;
5122 APP_EXCEPTION.RAISE_EXCEPTION;
5123
5124 END Cache_Payment_Method;
5125
5126 FUNCTION Cache_Fnd_Currency (
5127 P_Fnd_Currency_Table OUT NOCOPY AP_IMPORT_INVOICES_PKG.fnd_currency_tab_type,
5128 P_Calling_Sequence IN VARCHAR2)
5129 RETURN BOOLEAN IS
5130
5131 CURSOR currency_code_cur IS
5132 SELECT currency_code,
5133 start_date_active,
5134 end_date_active,
5135 minimum_accountable_unit,
5136 precision,
5137 enabled_flag
5138 FROM fnd_currencies;
5139
5140 l_debug_info VARCHAR2(1000);
5141 l_curr_calling_sequence VARCHAR2(2000);
5142
5143
5144 BEGIN
5145
5146 l_curr_calling_sequence := 'Cache_Fnd_Currency <- '||P_calling_sequence;
5147 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
5148 AP_IMPORT_UTILITIES_PKG.Print(
5149 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_curr_calling_sequence);
5150 END IF;
5151
5152 l_debug_info := 'Caching Currency from Fnd Currency';
5153 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
5154 AP_IMPORT_UTILITIES_PKG.Print(
5155 AP_IMPORT_INVOICES_PKG.g_debug_switch,l_debug_info);
5156 END IF;
5157
5158 OPEN currency_code_cur;
5159 FETCH currency_code_cur
5160 BULK COLLECT INTO P_Fnd_Currency_Table;
5161 CLOSE currency_code_cur;
5162
5163 RETURN(TRUE);
5164
5165 EXCEPTION
5166 WHEN OTHERS then
5167 IF (SQLCODE <> -20001) THEN
5168 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
5169 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
5170 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
5171 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
5172 END IF;
5173 APP_EXCEPTION.RAISE_EXCEPTION;
5174
5175 END Cache_Fnd_Currency;
5176
5177 END AP_IMPORT_UTILITIES_PKG;