[Home] [Help]
PACKAGE BODY: APPS.AP_ALLOCATION_RULES_PKG
Source
1 PACKAGE BODY AP_ALLOCATION_RULES_PKG as
2 /* $Header: apalocrb.pls 120.13 2007/12/05 18:43:29 gagrawal ship $ */
3
4 -----------------------------------------------------------------------
5 -- FUNCTION insert_fully_prorated creates an PRORATION type allocation
6 -- rule given an invoice line of type freight or misc.
7 -----------------------------------------------------------------------
8
9 FUNCTION Insert_Fully_Prorated_Rule(
10 X_invoice_id IN NUMBER,
11 X_line_number IN NUMBER,
12 X_error_code OUT NOCOPY VARCHAR2) RETURN BOOLEAN
13 IS
14
15 l_generate_dists AP_INVOICE_LINES.GENERATE_DISTS%TYPE;
16 l_other_alloc_rules NUMBER;
17 current_calling_sequence VARCHAR2(2000);
18 debug_info VARCHAR2(200);
19 BEGIN
20
21 -- Update the calling sequence
22
23 current_calling_sequence := 'AP_ALLOCATION_RULES_PKG.'||
24 'insert_fully_prorated_rule';
25 --------------------------------------------------------------
26 -- Step 1 - Verify line exists, has generate_dists flag
27 -- set to Y
28 --------------------------------------------------------------
29 BEGIN
30 SELECT generate_dists
31 INTO l_generate_dists
32 FROM ap_invoice_lines
33 WHERE invoice_id = X_invoice_id
34 AND line_number = X_line_number;
35
36 /* Bug 5131721 */
37 IF (nvl(l_generate_dists, 'N') = 'D') THEN
38 X_error_code := 'AP_GENERATE_DISTS_IS_NO';
39 RETURN(FALSE);
40 END IF;
41 EXCEPTION
42 WHEN NO_DATA_FOUND THEN
43 IF (SQLCODE <> -20001) THEN
44 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
45 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
46 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
47 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id)
48
49 ||', Invoice Line Number = '||TO_CHAR(X_line_number));
50 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
51 END IF;
52 X_error_code := 'AP_DEBUG';
53 RETURN (FALSE);
54 END;
55
56 --------------------------------------------------------------
57 -- Step 2 - Verify no other allocation rules exist for
58 -- this line
59 --------------------------------------------------------------
60
61 BEGIN
62 l_other_alloc_rules := 0;
63 SELECT COUNT(*)
64 INTO l_other_alloc_rules
65 FROM ap_allocation_rules
66 WHERE invoice_id = X_invoice_id
67 AND chrg_invoice_line_number = X_line_number;
68
69 IF (l_other_alloc_rules <> 0) THEN
70 X_error_code := 'AP_ALLOCATIONS_EXIST';
71 RETURN(FALSE);
72 END IF;
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 NULL;
77 END;
78
79 BEGIN
80 INSERT INTO ap_allocation_rules(
81 invoice_id,
82 chrg_invoice_line_number,
83 rule_type,
84 rule_generation_type,
85 status,
86 creation_date,
87 created_by,
88 last_updated_by,
89 last_update_date,
90 last_update_login,
91 program_application_id,
92 program_id,
93 program_update_date,
94 request_id)
95 VALUES(
96 X_invoice_id, -- invoice_id
97 X_line_number, -- chrg_invoice_line_number
98 'PRORATION', -- rule_type
99 'SYSTEM', -- rule_generation_type
100 'PENDING', -- status
101 SYSDATE, -- creation_date
102 FND_GLOBAL.USER_ID, -- created_by
103 0, -- last_updated_by
104 SYSDATE, -- last_update_date
105 FND_GLOBAL.LOGIN_ID, -- last_update_login
106 FND_GLOBAL.PROG_APPL_ID, -- program_application_id
107 FND_GLOBAL.CONC_PROGRAM_ID,-- program_id
108 SYSDATE, -- program_update_date
109 FND_GLOBAL.CONC_REQUEST_ID -- request_id
110 );
111 EXCEPTION
112 WHEN OTHERS THEN
113 IF (SQLCODE <> -20001) THEN
114 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
115 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
116 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
117 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id)
118 ||', Invoice Line Number = '||TO_CHAR(X_line_number));
119 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
120 END IF;
121 X_error_code := 'AP_DEBUG';
122 RETURN (FALSE);
123 END;
124
125 RETURN(TRUE);
126 END insert_fully_prorated_rule;
127
128
129 -----------------------------------------------------------------------
130 -- FUNCTION insert_from_line_group_number creates an AMOUNT type
131 -- allocation rule and rule lines given an invoice line of type
132 -- freight or misc populated with a line_group_number (Open Interface
133 -- Import)
134 -----------------------------------------------------------------------
135
136 FUNCTION insert_from_line_group_number (
137 X_invoice_id IN NUMBER,
138 X_line_number IN NUMBER,
139 X_error_code OUT NOCOPY VARCHAR2) RETURN BOOLEAN
140 IS
141
142 l_generate_dists AP_INVOICE_LINES.GENERATE_DISTS%TYPE;
143 l_line_group_number AP_INVOICE_LINES.LINE_GROUP_NUMBER%TYPE;
144 l_amount_to_prorate AP_INVOICE_LINES.AMOUNT%TYPE;
145 l_total_prorated AP_INVOICE_LINES.AMOUNT%TYPE;
146 l_inv_curr_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
147 l_count_non_item_lines NUMBER := 0;
148 l_prorating_total NUMBER := 0;
149 l_other_alloc_rules NUMBER;
150 current_calling_sequence VARCHAR2(2000);
151 debug_info VARCHAR2(200);
152 BEGIN
153
154 -- Update the calling sequence
155 current_calling_sequence := 'AP_ALLOCATION_RULES_PKG.'||
156 'insert_from_prorated_rule';
157 --------------------------------------------------------------
158 -- Step 1 - Verify line exists, has generate_dists flag
159 -- set to Y
160 --------------------------------------------------------------
161 BEGIN
162 SELECT ail.generate_dists,
163 ail.line_group_number,
164 ail.amount , --bug6653070
165 ai.invoice_currency_code
166 INTO l_generate_dists,
167 l_line_group_number,
168 l_amount_to_prorate,
169 l_inv_curr_code
170 FROM ap_invoice_lines ail,
171 ap_invoices ai
172 WHERE ail.invoice_id = X_invoice_id
173 AND ail.line_number = x_line_number
174 AND ai.invoice_id = X_invoice_id;
175
176 /* Bug 5131721 */
177 IF (nvl(l_generate_dists, 'N') = 'D') THEN
178 X_error_code := 'AP_GENERATE_DISTS_IS_NO';
179 RETURN(FALSE);
180 END IF;
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 IF (SQLCODE <> -20001) THEN
184 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
185 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
186 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
187 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id =
188 '||TO_CHAR(X_invoice_id)
189 ||', Invoice Line Number = '||TO_CHAR(X_line_number));
190 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
191 END IF;
192 X_error_code := 'AP_DEBUG';
193 RETURN (FALSE);
194 END;
195
196 --------------------------------------------------------------
197 -- Step 2 - Verify lines it is to be allocated across are of
198 -- type ITEM
199 --------------------------------------------------------------
200 BEGIN
201 SELECT COUNT(*)
202 INTO l_count_non_item_lines
203 FROM ap_invoice_lines
204 WHERE invoice_id = X_invoice_id
205 AND line_number <> X_line_number
206 AND line_group_number = l_line_group_number
207 AND line_type_lookup_code <> 'ITEM';
208
209 IF (l_count_non_item_lines <> 0) THEN
210 X_error_code := 'AP_NO_ITEMS_LINES_AVAIL';
211 END IF;
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 NULL;
216 END;
217
218 --------------------------------------------------------------
219 -- Step 3 - Verify sum to prorate across is non zero
220 --------------------------------------------------------------
221 BEGIN
222 SELECT SUM(amount)
223 INTO l_prorating_total
224 FROM ap_invoice_lines
225 WHERE invoice_id = X_invoice_id
226 AND line_number <> X_line_number
227 AND line_group_number = l_line_group_number
228 AND line_type_lookup_code = 'ITEM'
229 AND nvl(match_type,'NOT_MATCHED') NOT IN
230 ('PRICE_CORRECTION', 'QTY_CORRECTION','LINE_CORRECTION');
231
232 IF (l_prorating_total = 0) THEN
233 X_error_code := 'AP_NO_ITEMS_LINES_AVAIL';
234 END IF;
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 NULL;
239 END;
240
241
242 --------------------------------------------------------------
243 -- Step 4 - Verify no other allocation rules exist for
244 -- this line
245 --------------------------------------------------------------
246
247 BEGIN
248 l_other_alloc_rules := 0;
249 SELECT COUNT(*)
250 INTO l_other_alloc_rules
251 FROM ap_allocation_rules
252 WHERE invoice_id = X_invoice_id
253 AND chrg_invoice_line_number = X_line_number;
254
255 IF (l_other_alloc_rules <> 0) THEN
256 X_error_code := 'AP_ALLOCATIONS_EXIST';
257 RETURN(FALSE);
258 END IF;
259
260 EXCEPTION
261 WHEN OTHERS THEN
262 NULL;
263 END;
264
265
266 --------------------------------------------------------------
267 -- Step 5 - Insert Allocation Rule
268 --------------------------------------------------------------
269 BEGIN
270 INSERT INTO ap_allocation_rules(
271 invoice_id,
272 chrg_invoice_line_number,
273 rule_type,
274 rule_generation_type,
275 status,
276 creation_date,
277 created_by,
278 last_updated_by,
279 last_update_date,
280 last_update_login,
281 program_application_id,
282 program_id,
283 program_update_date,
284 request_id)
285 VALUES(
286 X_invoice_id, -- invoice_id
287 X_line_number, -- chrg_invoice_line_number
288 'AMOUNT', -- rule_type
289 'SYSTEM', -- rule_generation_type
290 'PENDING', -- status
291 SYSDATE, -- creation_date
292 FND_GLOBAL.USER_ID, -- created_by
293 0, -- last_updated_by
294 SYSDATE, -- last_update_date
295 FND_GLOBAL.LOGIN_ID, -- last_update_login
296 FND_GLOBAL.PROG_APPL_ID, -- program_application_id
297 FND_GLOBAL.CONC_PROGRAM_ID,-- program_id
298 SYSDATE, -- program_update_date
299 FND_GLOBAL.CONC_REQUEST_ID -- request_id
300 );
301 EXCEPTION
302 WHEN OTHERS THEN
303 IF (SQLCODE <> -20001) THEN
304 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
305 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
306 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
307 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id =
308 '||TO_CHAR(X_invoice_id)
309 ||', Invoice Line Number = '||TO_CHAR(X_line_number));
310 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
311 END IF;
312 X_error_code := 'AP_DEBUG';
313 RETURN (FALSE);
314 END;
315
316
317 --------------------------------------------------------------
318 -- Step 6 - Insert Allocation Rule Lines
319 --------------------------------------------------------------
320 BEGIN
321 INSERT INTO ap_allocation_rule_lines (
322 invoice_id,
323 chrg_invoice_line_number,
324 to_invoice_line_number,
325 amount,
326 creation_date,
327 created_by,
328 last_updated_by,
329 last_update_date,
330 last_update_login,
331 program_application_id,
332 program_id,
333 program_update_date,
334 request_id)
335 SELECT
336 X_invoice_id, -- invoice_id
337 X_line_number, -- chrg_invoice_line_number
338 line_number, -- to_invoice_line_number
339 ap_utilities_pkg.ap_round_currency(l_amount_to_prorate * amount /
340 l_prorating_total, l_inv_curr_code),
341 -- amount
342 SYSDATE, -- creation_date
343 FND_GLOBAL.USER_ID, -- created_by
344 0, -- last_updated_by
345 SYSDATE, -- last_update_date
346 FND_GLOBAL.LOGIN_ID, -- last_update_login
347 FND_GLOBAL.PROG_APPL_ID, -- program_application_id
348 FND_GLOBAL.CONC_PROGRAM_ID,-- program_id
349 SYSDATE, -- program_update_date
350 FND_GLOBAL.CONC_REQUEST_ID -- request_id
351 FROM ap_invoice_lines
352 WHERE invoice_id = X_invoice_id
353 AND line_number <> X_line_number
354 AND line_group_number = l_line_group_number
355 AND line_type_lookup_code = 'ITEM'
356 AND nvl(match_type,'NOT_MATCHED') NOT IN
357 ('PRICE_CORRECTION', 'QTY_CORRECTION','LINE_CORRECTION');
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 IF (SQLCODE <> -20001) THEN
362 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
363 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
364 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
365 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id =
366 '||TO_CHAR(X_invoice_id)
367 ||', Invoice Line Number = '||TO_CHAR(X_line_number));
368 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
369 END IF;
370 X_error_code := 'AP_DEBUG';
371 RETURN (FALSE);
372 END;
373
374 --------------------------------------------------------------
375 -- Step 6 - Verify if there is any rounding and apply it to
376 -- max of largest.
377 --------------------------------------------------------------
378 BEGIN
379 SELECT SUM(amount)
380 INTO l_total_prorated
381 FROM ap_allocation_rule_lines
382 WHERE invoice_id = X_invoice_id
383 AND chrg_invoice_line_number = X_line_number;
384
385 IF (l_amount_to_prorate <> l_total_prorated) THEN
386 UPDATE ap_allocation_rule_lines
387 SET amount = amount + (l_amount_to_prorate - l_total_prorated)
388 WHERE invoice_id = X_invoice_id
389 AND chrg_invoice_line_number = X_line_number
390 AND to_invoice_line_number =
391 (SELECT (MAX(ail1.line_number))
392 FROM ap_invoice_lines ail1
393 WHERE ail1.invoice_id = X_invoice_id
394 AND ail1.line_number <> X_line_number
395 AND ail1.amount <> 0
396 AND ail1.line_group_number = l_line_group_number
397 AND ABS(ail1.amount) >=
398 ( SELECT MAX(ABS(ail2.amount))
399 FROM ap_invoice_lines ail2
400 WHERE ail2.invoice_id = X_invoice_id
404 l_line_group_number));
401 AND ail2.line_number <> X_line_number
402 AND ail2.line_number <> ail1.line_number
403 AND ail2.line_group_number =
405 END IF;
406 EXCEPTION
407 WHEN OTHERS THEN
408 IF (SQLCODE <> -20001) THEN
409 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
410 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
411 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
412 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id =
413 '||TO_CHAR(X_invoice_id)
414 ||', Invoice Line Number = '||TO_CHAR(X_line_number));
415 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
416 END IF;
417 X_error_code := 'AP_DEBUG';
418 RETURN (FALSE);
419 END;
420
421 RETURN(TRUE);
422 END insert_from_line_group_number;
423
424 Procedure Insert_Percentage_Alloc_Rule(
425 X_Invoice_id IN NUMBER,
426 X_Chrg_Line_Number IN NUMBER,
427 X_To_Line_Number IN NUMBER,
428 X_Rule_Generation_Type IN VARCHAR2 DEFAULT 'SYSTEM',
429 X_Status IN VARCHAR2 DEFAULT 'PENDING',
430 X_Percentage IN NUMBER,
431 X_Calling_Sequence IN VARCHAR2) IS
432
433 l_debug_info VARCHAR2(100);
434 current_calling_sequence VARCHAR2(2000);
435 BEGIN
436
437 current_calling_sequence := 'Insert_Percentage_Alloc_Rule<-'||X_Calling_Sequence;
438
439 l_debug_info := 'Insert record into AP_ALLOCATION_RULES';
440
441 Insert into AP_ALLOCATION_RULES
442 (Invoice_id,
443 Chrg_Invoice_Line_Number,
444 Rule_Type,
445 Rule_Generation_Type,
446 Status,
447 Creation_Date,
448 Created_By,
449 Last_Updated_By,
450 Last_Update_Date,
451 Last_Update_Login,
452 Program_Application_Id,
453 Program_Id,
454 Program_Update_Date,
455 Request_Id)
456 values(x_invoice_id,
457 x_chrg_line_number,
458 'PERCENTAGE',
459 x_rule_generation_type,
460 x_status,
461 sysdate,
462 fnd_global.user_id,
463 fnd_global.user_id,
464 sysdate,
465 fnd_global.login_id,
466 NULL,
467 NULL,
468 NULL,
469 NULL);
470
471
472 l_debug_info := 'Inserting record into AP_ALLOCATION_RULE_LINES';
473
474 Insert Into ap_allocation_rule_lines (
475 Invoice_id,
476 chrg_invoice_line_number,
477 to_invoice_line_number,
478 percentage,
479 amount,
480 creation_date,
481 created_by,
482 last_updated_by,
483 last_update_date,
484 last_update_login,
485 program_application_id,
486 program_id,
487 program_update_date,
488 request_id)
489 values(x_invoice_id,
490 x_chrg_line_number,
491 x_to_line_number,
492 x_percentage,
493 NULL,
494 sysdate,
495 fnd_global.user_id,
496 fnd_global.user_id,
497 sysdate,
498 fnd_global.login_id,
499 NULL,
500 NULL,
501 NULL,
502 NULL);
503
504 EXCEPTION
505 WHEN OTHERS THEN
506 IF (SQLCODE <> -20001) THEN
507 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
508 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
509 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
510 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_Invoice_Id)
511 ||', Chrg Invoice Line Number = '||TO_CHAR(X_Chrg_Line_Number)
512 ||', To Invoice Line Number = '||TO_CHAR(X_To_Line_Number)
513 ||', Rule Generation Type = '||x_rule_generation_type
514 ||', Percentage = '||TO_CHAR(x_percentage)
515 ||', Status = '||x_status);
516 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
517 END IF;
518 APP_EXCEPTION.RAISE_EXCEPTION;
519
520 END Insert_Percentage_Alloc_Rule;
521
522
523
524 ---------------------------------------------------------------------------
525 -- This function is called if the user wants to view the Allocations from
526 -- the standpoint of ITEM Type of line.
527 -- This function calls : Create_Proration_Rule for every charge line
528 -- with Auto Generate Dists = 'Y' and Match Type <> OTHER_TO_RECEIPT
529 -- Create Proration Rule when called in this context
530 -- (Window Context: ALLOCATIONS) creates temporary allocation lines for
531 -- charge line which do not have a Rule (or have a pending PRORATION rule)
532 -- associated with them.
533 -- It returns FALSE if an error is encountered, TRUE otherwise.
534 -- The following error codes may be returned via the X_error_code OUT
535 -- parameter:
536 -- 'AP_NO_CHARGES_EXIST' - No charge lines exist for this invoice.
537 -- 'AP_GENERATE_DISTS_IS_NO' - Generate Dists Flag is N for this chrg line
538 -- 'AP_NO_ITEM_LINES_AVAIL' - No Item Lines exist or sum total of Item
539 -- lines is zero for this Invoice
540 ----------------------------------------------------------------------------
544 X_Error_Code OUT NOCOPY VARCHAR2,
541 FUNCTION Create_Allocations(
542 X_Invoice_id IN NUMBER,
543 X_Window_context IN VARCHAR2,
545 X_Debug_Info OUT NOCOPY VARCHAR2,
546 X_Debug_Context OUT NOCOPY VARCHAR2,
547 X_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
548
549 IS
550
551 CURSOR chrg_lines_cur IS
552 SELECT invoice_id,
553 line_number
554 FROM ap_invoice_lines
555 WHERE line_type_lookup_code in ('FREIGHT', 'MISCELLANEOUS')
556 AND generate_dists <> 'D' -- Bug 5131721
557 AND NVL(match_type, 'NOT_MATCHED') <> 'OTHER_TO_RECEIPT'
558 AND invoice_id = X_invoice_id
559 ORDER BY line_number;
560
561 l_chrg_lines_count BINARY_INTEGER := 0;
562 l_invoice_id AP_INVOICE_LINES_ALL.Invoice_Id%TYPE;
563 l_chrg_line_number AP_INVOICE_LINES_ALL.line_number%TYPE;
564 l_error_code VARCHAR2(30);
565 debug_info VARCHAR2(200);
566 debug_context VARCHAR2(2000);
567 current_calling_sequence VARCHAR2(2000);
568
569 BEGIN
570
571 -- Update the calling sequence
572 current_calling_sequence := 'Ap_Allocation_Rules_Pkg.'||
573 'Create_Allocations';
574
575 --------------------------------------------------------------
576 debug_info := ' Step 1 - Verify IF any chrg line exists for '
577 ||'this Invoice';
578 --------------------------------------------------------------
579 BEGIN
580
581 SELECT COUNT(*)
582 INTO l_chrg_lines_count
583 FROM ap_invoice_lines
584 WHERE line_type_lookup_code in ('FREIGHT', 'MISCELLANEOUS', 'TAX')
585 AND generate_dists <> 'D' -- Bug 5131721
586 AND NVL(match_type, 'NOT_MATCHED') <> 'OTHER_TO_RECEIPT'
587 AND invoice_id = X_invoice_id;
588
589 IF (l_chrg_lines_count = 0) THEN
590 X_error_code := 'AP_NO_CHARGES_EXIST';
591 RETURN(FALSE);
592 END IF;
593 EXCEPTION
594 WHEN OTHERS THEN
595 NULL;
596 END;
597
598 --------------------------------------------------------------
599 debug_info := ' Step 2 - For all Freight/Misc lines '
600 ||' create allocation rule.';
601 -- of TYPE Proration if one does
602 -- not exist. Prorate the chrg line accross all item lines
603 --------------------------------------------------------------
604
605 OPEN chrg_lines_cur;
606 LOOP
607 FETCH chrg_lines_cur
608 INTO l_invoice_id,
609 l_chrg_line_number;
610
611 EXIT WHEN chrg_lines_cur%NOTFOUND;
612
613 IF (NOT (Ap_Allocation_Rules_Pkg.Create_Proration_Rule(
614 l_invoice_id,
615 l_chrg_line_number,
616 NULL, -- rule_type
617 X_Window_context, --
618 l_error_code, -- OUT
619 Debug_Info, -- OUT
620 Debug_Context, -- OUT
621 current_calling_sequence -- IN
622 ))) THEN
623
624 IF (l_error_code IS NOT NULL) THEN
625 X_error_code := l_error_code;
626 CLOSE chrg_lines_cur;
627 RETURN (FALSE);
628 ELSE
629 CLOSE chrg_lines_cur;
630 X_debug_context := current_calling_sequence;
631 X_debug_info := debug_info;
632 RETURN (FALSE);
633 END IF;
634 END IF;
635
636 END LOOP;
637 CLOSE chrg_lines_cur;
638 RETURN(TRUE);
639 END Create_Allocations;
640
641
642 ------------------------------------------------------------------------------
643 -- This function may be called from the following:
644 -- 1. Create_Allocations Function to generate temporary Allocations so
645 -- that the user can view Allocations from the standpoint of ITEM Line.
646 -- 2. In the WNFI for Allocations Rule Window, to create a default
647 -- rule of type PRORATION.(If a one doesn't exist)
648 -- 3. When-List-Changed of Rule Type in the Allocation Rules window. This
649 -- function re-creates the Proration lines for the Rule Type PRORATION
650 -- It returns FALSE if an error is encountered, TRUE otherwise.
651 -- The following error codes may be returned via the X_error_code OUT
652 -- parameter:
653 -- 'AP_NO_CHARGES_EXIST' - No charge lines exist for this invoice.
654 -- 'AP_GENERATE_DISTS_IS_NO' - Generate Dists Flag is N for this chrg line
655 -- 'AP_NO_ITEM_LINES_AVAIL' - No Item Lines exist or sum total of Item
656 -- lines is zero for this Invoice
657 ------------------------------------------------------------------------------
658 FUNCTION Create_Proration_Rule(
659 X_invoice_id IN NUMBER,
660 X_chrg_line_number IN NUMBER,
661 X_rule_type IN VARCHAR2,
662 X_window_context IN VARCHAR2,
663 X_Error_Code OUT NOCOPY VARCHAR2,
664 X_Debug_Info OUT NOCOPY VARCHAR2,
665 X_Debug_Context OUT NOCOPY VARCHAR2,
666 X_calling_sequence IN VARCHAR2)
667 RETURN BOOLEAN IS
668
672 l_amount_to_prorate AP_INVOICE_LINES.AMOUNT%TYPE;
669 l_generate_dists AP_INVOICE_LINES.GENERATE_DISTS%TYPE;
670 l_alloc_rule NUMBER;
671 l_alloc_rule_lines NUMBER;
673 l_total_prorated AP_INVOICE_LINES.AMOUNT%TYPE;
674 l_inv_curr_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
675 l_rule_type AP_ALLOCATION_RULES.RULE_TYPE%TYPE;
676 l_rule_status AP_ALLOCATION_RULES.STATUS%TYPE;
677 l_prorating_total NUMBER := 0;
678 l_create_alloc_rule VARCHAR2(1) := 'Y';
679 l_create_alloc_rule_lines VARCHAR2(1) := 'Y';
680
681 debug_info VARCHAR2(200);
682 debug_context VARCHAR2(2000);
683 current_calling_sequence VARCHAR2(2000);
684
685 BEGIN
686
687 -- Update the calling sequence
688
689 current_calling_sequence := 'Ap_Allocation_Rules_Pkg.'||
690 'Create_Proration_Rule';
691 --------------------------------------------------------------
692 debug_info := ' Step 1 - Verify IF any chrg line exists for '
693 ||'this Invoice';
694 --------------------------------------------------------------
695 BEGIN
696 SELECT ail.generate_dists,
697 ail.amount, --bug6653070
698 ai.invoice_currency_code
699 INTO l_generate_dists,
700 l_amount_to_prorate,
701 l_inv_curr_code
702 FROM ap_invoice_lines ail,
703 ap_invoices ai
704 WHERE ail.invoice_id = X_invoice_id
705 AND ail.line_number = x_chrg_line_number
706 AND ai.invoice_id = X_invoice_id;
707
708 /* Bug 5131721 */
709 IF (nvl(l_generate_dists, 'N') = 'D') THEN
710 X_error_code := 'AP_GENERATE_DISTS_IS_NO';
711 RETURN(FALSE);
712 END IF;
713 EXCEPTION
714 WHEN NO_DATA_FOUND THEN
715 X_error_code := 'AP_NO_CHARGES_EXIST';
716 RETURN(FALSE);
717 WHEN OTHERS THEN
718 X_debug_context := current_calling_sequence;
719 X_debug_info := debug_info;
720 IF (SQLCODE <> -20001) THEN
721 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
722 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
723 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
724 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id =
725 '||TO_CHAR(X_invoice_id)
726 ||', Invoice Line Number = '||TO_CHAR(X_chrg_line_number));
727 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
728 END IF;
729 X_error_code := 'AP_DEBUG';
730 RETURN(FALSE);
731 END;
732
733 --------------------------------------------------------------
734 debug_info := ' Step 2 - Verify IF the allocation rule exist '
735 ||'this chrg line.';
736 --------------------------------------------------------------
737 BEGIN
738
739 l_create_alloc_rule := 'Y';
740 l_alloc_rule := 0;
741 SELECT COUNT(*)
742 INTO l_alloc_rule
743 FROM ap_allocation_rules
744 WHERE invoice_id = X_invoice_id
745 AND chrg_invoice_line_number = X_chrg_line_number;
746
747 IF (l_alloc_rule <> 0) THEN
748 l_create_alloc_rule := 'N';
749 END IF;
750 EXCEPTION
751 WHEN OTHERS THEN
752 NULL;
753 END;
754
755
756 --------------------------------------------------------------
757 debug_info := ' Step 3 - IF the allocation rules exist then '
758 ||'determine the Status and Rule type.';
759 -- associated with the charge line
760 --------------------------------------------------------------
761 IF l_create_alloc_rule = 'N' then
762
763 BEGIN
764 SELECT rule_type,
765 status
766 INTO l_rule_type,
767 l_rule_status
768 FROM ap_allocation_rules
769 WHERE invoice_id = X_invoice_id
770 AND chrg_invoice_line_number = X_chrg_line_number;
771
772 EXCEPTION
773 WHEN OTHERS THEN
774 NULL;
775 END;
776
777 IF l_rule_status = 'EXECUTED' THEN
778 RETURN(TRUE);
779 END IF;
780
781 -- X_Rule_type is not NULL when create_proration_rule is
782 -- called from WHEN-LIST-CHANGED in Allocation Rules Window
783 IF (X_rule_type is NOT NULL ) then
784 l_rule_type := X_rule_type;
785 END IF;
786
787 IF l_rule_type <> 'PRORATION' then
788 RETURN(TRUE);
789 END IF;
790
791 ELSE --Allocation Rule does not exists.
792
793 l_rule_type := 'PRORATION';
794 l_rule_status := 'PENDING';
795
796 END IF;
797
798 --------------------------------------------------------------
799 -- IF allocation rule lines exist for this chrg line and the
800 -- Rule_type is PRORATION
801 debug_info := 'Step 4 - Recreate the Allocation Rule Lines '
802 ||'after deleting the existing ones.';
803 --------------------------------------------------------------
804 IF l_create_alloc_rule = 'N' AND l_rule_status = 'PENDING' THEN
805
806 BEGIN
807 l_alloc_rule_lines := 0;
808 SELECT COUNT(*)
809 INTO l_alloc_rule_lines
810 FROM ap_allocation_rule_lines
814 IF (l_rule_type ='PRORATION') THEN
811 WHERE invoice_id = X_invoice_id
812 AND chrg_invoice_line_number = X_chrg_line_number;
813
815 l_create_alloc_rule_lines := 'Y';
816 IF l_alloc_rule_lines <> 0 then
817 Ap_Allocation_Rules_Pkg.delete_allocation_lines(
818 X_invoice_id,
819 X_chrg_Line_number,
820 'Insert_fully_prorated_rule');
821 END IF;
822 ELSE
823 l_create_alloc_rule_lines := 'N';
824 END IF;
825 EXCEPTION
826 WHEN OTHERS THEN
827 NULL;
828 END;
829 END IF;
830
831
832 -----------------------------------------------------------------
833 debug_info := ' Step 5 - Verify sum to prorate across is non zero';
834 -----------------------------------------------------------------
835 IF l_create_alloc_rule_lines = 'Y' AND
836 l_rule_status = 'PENDING' then
837
838 BEGIN
839 SELECT SUM(amount)
840 INTO l_prorating_total
841 FROM ap_invoice_lines
842 WHERE invoice_id = X_invoice_id
843 AND line_number <> X_chrg_line_number
844 AND line_type_lookup_code = 'ITEM'
845 AND nvl(match_type,'NOT_MATCHED') NOT IN
846 ('PRICE_CORRECTION', 'QTY_CORRECTION','LINE_CORRECTION');
847
848 IF (l_prorating_total = 0) THEN
849 X_error_code := 'AP_NO_ITEM_LINES_AVAIL';
850 RETURN(FALSE);
851 END IF;
852
853 EXCEPTION
854 WHEN OTHERS THEN
855 NULL;
856 END;
857
858 END IF;
859
860 --------------------------------------------------------------
861 debug_info := ' Step 6 - Create Allocation Rule.';
862 --------------------------------------------------------------
863 IF l_create_alloc_rule = 'Y' AND
864 X_window_context <> 'ALLOCATIONS' THEN
865 BEGIN
866 INSERT INTO ap_allocation_rules(
867 invoice_id,
868 chrg_invoice_line_number,
869 rule_type,
870 rule_generation_type,
871 status,
872 creation_date,
873 created_by,
874 last_updated_by,
875 last_update_date,
876 last_update_login,
877 program_application_id,
878 program_id,
879 program_update_date,
880 request_id)
881 VALUES(
882 X_invoice_id, -- invoice_id
883 X_chrg_line_number, -- chrg_invoice_line_number
884 'PRORATION', -- rule_type
885 'USER', -- rule_generation_type
886 'PENDING', -- status
887 SYSDATE, -- creation_date
888 FND_GLOBAL.USER_ID, -- created_by
889 0, -- last_updated_by
890 SYSDATE, -- last_update_date
891 FND_GLOBAL.LOGIN_ID, -- last_update_login
892 NULL, -- program_application_id
893 NULL, -- program_id
894 SYSDATE, -- program_update_date
895 NULL -- request_id
896 );
897 EXCEPTION
898 WHEN OTHERS THEN
899 IF (SQLCODE <> -20001) THEN
900 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
901 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
902 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
903 current_calling_sequence);
904 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '
905 ||to_char(X_Invoice_ID)
906 ||', Invoice Line Number = '||to_char(X_chrg_Line_Number));
907 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
908 END IF;
909 APP_EXCEPTION.RAISE_EXCEPTION;
910 END;
911 END IF;
912
913 -------------------------------------------------------------
914 debug_info := ' Step 7 - Create Allocation Rule Lines for '
915 ||'PRORATION.';
916 -- Allocation Rule Lines for Rule Type are not saved unless
917 -- the Allocation Rule has been EXECUTED.
918 -- A latest snapshot of PRORATION Rules is generated evertime
919 -- for the current state of the Invoice.
920 --------------------------------------------------------------
921 IF l_create_alloc_rule_lines = 'Y' AND
922 l_rule_status = 'PENDING' THEN
923
924 IF X_window_context = 'ALLOCATIONS' then
925
926 BEGIN
927 INSERT INTO ap_allocation_rule_lines_gt(
928 invoice_id,
929 chrg_invoice_line_number,
930 to_invoice_line_number,
931 amount,
932 creation_date,
933 created_by,
934 last_updated_by,
935 last_update_date,
936 last_update_login,
937 program_application_id,
938 program_id,
939 program_update_date,
940 request_id)
941 SELECT
942 X_invoice_id, -- invoice_id
943 X_chrg_line_number, -- chrg_invoice_line_number
944 line_number, -- to_invoice_line_number
945 ap_utilities_pkg.ap_round_currency(l_amount_to_prorate * amount /
946 l_prorating_total, l_inv_curr_code),
950 0, -- last_updated_by
947 -- amount
948 SYSDATE, -- creation_date
949 FND_GLOBAL.USER_ID, -- created_by
951 SYSDATE, -- last_update_date
952 FND_GLOBAL.LOGIN_ID, -- last_update_login
953 NULL, -- program_application_id
954 NULL, -- program_id
955 SYSDATE, -- program_update_date
956 NULL -- request_id
957 FROM ap_invoice_lines
958 WHERE invoice_id = X_invoice_id
959 AND line_number <> X_chrg_line_number
960 AND line_type_lookup_code = 'ITEM'
961 AND nvl(match_type,'NOT_MATCHED') NOT IN
962 ('PRICE_CORRECTION', 'QTY_CORRECTION','LINE_CORRECTION');
963 EXCEPTION
964 WHEN OTHERS THEN
965 IF (SQLCODE <> -20001) THEN
966 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
967 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
968 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
969 current_calling_sequence);
970 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '
971 ||to_char(X_Invoice_ID)
972 ||', Invoice Line Number = '
973 ||to_char(X_chrg_Line_Number));
974 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
975 END IF;
976 APP_EXCEPTION.RAISE_EXCEPTION;
977 END;
978
979 ELSE
980
981 BEGIN
982 INSERT INTO ap_allocation_rule_lines(
983 invoice_id,
984 chrg_invoice_line_number,
985 to_invoice_line_number,
986 amount,
987 creation_date,
988 created_by,
989 last_updated_by,
990 last_update_date,
991 last_update_login,
992 program_application_id,
993 program_id,
994 program_update_date,
995 request_id)
996 SELECT
997 X_invoice_id, -- invoice_id
998 X_chrg_line_number, -- chrg_invoice_line_number
999 line_number, -- to_invoice_line_number
1000 ap_utilities_pkg.ap_round_currency(l_amount_to_prorate * amount /
1001 l_prorating_total, l_inv_curr_code),
1002 -- amount
1003 SYSDATE, -- creation_date
1004 FND_GLOBAL.USER_ID, -- created_by
1005 0, -- last_updated_by
1006 SYSDATE, -- last_update_date
1007 FND_GLOBAL.LOGIN_ID, -- last_update_login
1008 NULL, -- program_application_id
1009 NULL, -- program_id
1010 SYSDATE, -- program_update_date
1011 NULL -- request_id
1012 FROM ap_invoice_lines
1013 WHERE invoice_id = X_invoice_id
1014 AND line_number <> X_chrg_line_number
1015 AND line_type_lookup_code = 'ITEM'
1016 AND nvl(match_type,'NOT_MATCHED') NOT IN
1017 ('PRICE_CORRECTION', 'QTY_CORRECTION','LINE_CORRECTION');
1018
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021 IF (SQLCODE <> -20001) THEN
1022 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1023 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1024 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1025 current_calling_sequence);
1026 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '
1027 ||to_char(X_Invoice_ID)
1028 ||', Invoice Line Number = '
1029 ||to_char(X_chrg_Line_Number));
1030 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1031 END IF;
1032 APP_EXCEPTION.RAISE_EXCEPTION;
1033 END;
1034 END IF;
1035 END IF;
1036
1037 --------------------------------------------------------------
1038 debug_info := 'Step 8 - Verify if there is any rounding and '
1039 ||' apply it to max of largest.';
1040 --------------------------------------------------------------
1041 IF l_create_alloc_rule_lines = 'Y' then
1042 IF X_window_context = 'ALLOCATIONS' then
1043 BEGIN
1044 SELECT SUM(amount)
1045 INTO l_total_prorated
1046 FROM ap_allocation_rule_lines_gt
1047 WHERE invoice_id = X_invoice_id
1048 AND chrg_invoice_line_number = X_chrg_line_number;
1049
1050 IF (l_amount_to_prorate <> l_total_prorated) THEN
1051 UPDATE ap_allocation_rule_lines_gt
1052 SET amount = amount + (l_amount_to_prorate - l_total_prorated)
1053 WHERE invoice_id = X_invoice_id
1054 AND chrg_invoice_line_number = X_chrg_line_number
1055 AND to_invoice_line_number =
1056 (SELECT (MAX(ail1.line_number))
1057 FROM ap_invoice_lines ail1
1058 WHERE ail1.invoice_id = X_invoice_id
1059 AND ail1.line_number <> X_chrg_line_number
1060 AND ail1.amount <> 0
1061 AND ABS(ail1.amount) >=
1062 ( SELECT MAX(ABS(ail2.amount))
1063 FROM ap_invoice_lines ail2
1064 WHERE ail2.invoice_id = X_invoice_id
1065 AND ail2.line_number <> X_chrg_line_number
1069 END IF;
1066 AND ail2.line_number <> ail1.line_number
1067 )
1068 );
1070 EXCEPTION
1071 WHEN OTHERS THEN
1072 IF (SQLCODE <> -20001) THEN
1073 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1074 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1075 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1076 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id ='
1077 ||to_char(X_Invoice_ID)
1078 ||', Invoice Line Number = '||to_char(X_chrg_Line_Number));
1079 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1080 END IF;
1081 APP_EXCEPTION.RAISE_EXCEPTION;
1082 END;
1083 ELSE
1084 BEGIN
1085 SELECT SUM(amount)
1086 INTO l_total_prorated
1087 FROM ap_allocation_rule_lines
1088 WHERE invoice_id = X_invoice_id
1089 AND chrg_invoice_line_number = X_chrg_line_number;
1090
1091 IF (l_amount_to_prorate <> l_total_prorated) THEN
1092 UPDATE ap_allocation_rule_lines
1093 SET amount = amount + (l_amount_to_prorate - l_total_prorated)
1094 WHERE invoice_id = X_invoice_id
1095 AND chrg_invoice_line_number = X_chrg_line_number
1096 AND to_invoice_line_number =
1097 (SELECT (MAX(ail1.line_number))
1098 FROM ap_invoice_lines ail1
1099 WHERE ail1.invoice_id = X_invoice_id
1100 AND ail1.line_number <> X_chrg_line_number
1101 AND ail1.amount <> 0
1102 AND ABS(ail1.amount) >=
1103 ( SELECT MAX(ABS(ail2.amount))
1104 FROM ap_invoice_lines ail2
1105 WHERE ail2.invoice_id = X_invoice_id
1106 AND ail2.line_number <> X_chrg_line_number
1107 AND ail2.line_number <> ail1.line_number
1108 ));
1109
1110 END IF;
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 IF (SQLCODE <> -20001) THEN
1114 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1115 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1116 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1117 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '
1118 ||to_char(X_Invoice_ID)
1119 ||', Invoice Line Number = '||to_char(X_chrg_Line_Number));
1120 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1121 END IF;
1122 APP_EXCEPTION.RAISE_EXCEPTION;
1123 END;
1124 END IF;
1125 END IF;
1126
1127
1128 RETURN(TRUE);
1129
1130 END Create_Proration_rule;
1131
1132
1133
1134
1135 -----------------------------------------------------------------------
1136 -- This procedure sums up the Allocations lines from the Standpoint of a
1137 -- Item Line. This procedures sums up the Allocations lines from the
1138 -- Allocation_rule_lines table(AMOUNT, PERCENTAGE and Executed PRORATION)
1139 -- UNIONED with the PENDING Proration allocation rule lines from the
1140 -- Global Temporary Table(AP_ALLOCATION_RULE_LINES_GT).
1141 -----------------------------------------------------------------------
1142 PROCEDURE Select_Item_Summary(
1143 X_Invoice_id IN NUMBER,
1144 X_to_invoice_line_number IN NUMBER,
1145 X_allocated_total IN OUT NOCOPY NUMBER,
1146 X_allocated_total_rtot_db IN OUT NOCOPY NUMBER,
1147 X_calling_sequence IN VARCHAR2) IS
1148
1149 current_calling_sequence VARCHAR2(2000);
1150 debug_info VARCHAR2(100);
1151 l_allocated_total NUMBER := 0;
1152 l_allocated_total_rtot_db NUMBER := 0;
1153 l_allocated_total_gt NUMBER := 0;
1154 l_allocated_total_gt_rtot_db NUMBER := 0;
1155 --
1156
1157 BEGIN
1158 -- Update the calling sequence
1159 --
1160 current_calling_sequence :=
1161 'Ap_Allocation_Rules_Pkg.select_Item_summary<-'||X_Calling_Sequence;
1162 ----------------------------------------------------------
1163 debug_info := 'Get allocated total';
1164 ----------------------------------------------------------
1165
1166 SELECT nvl(sum(amount), 0), nvl(sum(amount), 0)
1167 INTO l_allocated_total, l_allocated_total_rtot_db
1168 FROM ap_allocation_rule_lines
1169 WHERE invoice_id = X_invoice_id
1170 AND to_invoice_line_number = X_to_invoice_line_number;
1171
1172
1173 ----------------------------------------------------------
1174 debug_info := 'Get allocated total from the GT Table';
1175 ----------------------------------------------------------
1176 SELECT nvl(sum(amount), 0), nvl(sum(amount), 0)
1177 INTO l_allocated_total_gt, l_allocated_total_gt_rtot_db
1178 FROM ap_allocation_rule_lines_gt
1179 WHERE invoice_id = X_invoice_id
1180 AND to_invoice_line_number = X_to_invoice_line_number;
1181
1182 X_allocated_total := l_allocated_total + l_allocated_total_gt;
1183 X_allocated_total_rtot_db := l_allocated_total_rtot_db +
1184 l_allocated_total_gt_rtot_db;
1185
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 IF (SQLCODE <> -20001) then
1189 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1190 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1194 || ' X_allocated_total (OUT) ='||to_char(X_allocated_total)
1191 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1192 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1193 'X_invoice_id ='||to_char(X_invoice_id)
1195 || ' X_allocated_total_rtot_db (OUT) ='
1196 ||to_char(X_allocated_total_rtot_db));
1197 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1198 END IF;
1199 APP_EXCEPTION.RAISE_EXCEPTION;
1200
1201 END Select_Item_Summary;
1202
1203
1204 -----------------------------------------------------------------------
1205 -- This function Inserts/Updates/Deletes records
1206 -- in the Ap_allocation_Rule_lines based on the allocation_flag
1207 -- passed to this function via manual allocation for AMOUNT and
1208 -- PERCENTAGE type Rules in the Allocation Rule Window.
1209 ------------------------------------------------------------------------
1210 FUNCTION Allocation_Rule_Lines(
1211 X_Invoice_id IN NUMBER,
1212 X_chrg_invoice_line_number IN NUMBER,
1213 X_to_invoice_line_number IN NUMBER,
1214 X_allocated_percentage IN NUMBER,
1215 X_allocated_amount IN NUMBER,
1216 X_allocation_flag IN VARCHAR2,
1217 X_Error_code OUT NOCOPY VARCHAR2) RETURN BOOLEAN
1218 IS
1219
1220 l_generate_dists AP_INVOICE_LINES.GENERATE_DISTS%TYPE;
1221 l_alloc_rule_line NUMBER;
1222
1223 debug_info VARCHAR2(100);
1224 current_calling_sequence VARCHAR2(2000);
1225
1226 BEGIN
1227
1228 -- Update the calling sequence
1229 current_calling_sequence := 'Ap_Allocation_Rules_Pkg.'||
1230 'Allocation_Rule_Lines';
1231
1232 --------------------------------------------------------------
1233 debug_info := ' Step 1 - Verify IF allocation rule line exists';
1234 -- i.e. chrg line is allocated to the Item Line.'
1235 --------------------------------------------------------------
1236
1237 BEGIN
1238 l_alloc_rule_line := 0;
1239 SELECT COUNT(*)
1240 INTO l_alloc_rule_line
1241 FROM ap_allocation_rule_lines
1242 WHERE invoice_id = X_invoice_id
1243 AND chrg_invoice_line_number = X_chrg_invoice_line_number
1244 AND to_invoice_line_number = X_to_invoice_line_number;
1245
1246 EXCEPTION
1247 WHEN OTHERS THEN
1248 NULL;
1249 END;
1250
1251 --------------------------------------------------------------
1252 -- IF the allocation line does not exists
1253 -- and IF the X_allocation_flag is Y
1254 debug_info := ' Step 2 - Insert the allocation rule line.';
1255 --------------------------------------------------------------
1256 IF l_alloc_rule_line = 0 then
1257
1258 IF X_allocation_flag = 'Y' then
1259 BEGIN
1260 INSERT INTO ap_allocation_rule_Lines(
1261 invoice_id,
1262 chrg_invoice_line_number,
1263 to_invoice_line_number,
1264 percentage,
1265 amount,
1266 creation_date,
1267 created_by,
1268 last_update_date,
1269 last_updated_by,
1270 last_update_login,
1271 program_application_id,
1272 program_id,
1273 program_update_date,
1274 request_id)
1275 VALUES(
1276 X_invoice_id, -- invoice_id
1277 X_chrg_invoice_line_number, -- chrg_invoice_line_number
1278 X_to_invoice_line_number, -- to_invoice_line_number
1279 X_allocated_percentage, -- percentage
1280 X_allocated_Amount, -- amount
1281 SYSDATE, -- creation_date
1282 FND_GLOBAL.USER_ID, -- created_by
1283 SYSDATE, -- last_update_date
1284 FND_GLOBAL.USER_ID, -- last_updated_by
1285 FND_GLOBAL.LOGIN_ID, -- last_update_login
1286 NULL, -- program_application_id
1287 NULL, -- program_id
1288 NULL, -- program_update_date
1289 NULL -- request_id
1290 );
1291 EXCEPTION
1292 WHEN OTHERS THEN
1293 IF (SQLCODE <> -20001) THEN
1294 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1295 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1296 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1297 current_calling_sequence);
1298 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '
1299 ||to_char(X_Invoice_ID) ||', Invoice Line Number = '
1300 ||to_char(X_chrg_Invoice_Line_Number));
1301 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1302 END IF;
1303 APP_EXCEPTION.RAISE_EXCEPTION;
1304 END;
1305
1306 END IF; --Allocation_flag
1307
1308 END IF;
1309
1310 --------------------------------------------------------------
1311 -- IF the allocation line exists
1312 -- and IF the Allocation_flag is N
1313 debug_info := ' Step 3 -Delete the allocation rule line. ';
1314 --------------------------------------------------------------
1315 IF l_alloc_rule_line <> 0 THEN
1319 DELETE FROM ap_allocation_rule_lines
1316
1317 IF X_allocation_flag = 'N' then
1318 BEGIN
1320 WHERE invoice_id = X_invoice_id
1321 AND chrg_invoice_line_number = X_chrg_invoice_line_number
1322 AND to_invoice_line_number = X_to_invoice_line_number;
1323
1324 RETURN(TRUE);
1325 EXCEPTION
1326 WHEN OTHERS THEN
1327 IF (SQLCODE <> -20001) THEN
1328 X_error_code := 'COULD_NOT_INSERT_ALLOC_RULE';
1329 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1330 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1331 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1332 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||to_char(X_Invoice_ID)
1333 ||', Invoice Line Number = '||to_char(X_chrg_Invoice_Line_Number));
1334 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1335 END IF;
1336 APP_EXCEPTION.RAISE_EXCEPTION;
1337 END;
1338
1339 END IF;
1340 END IF;
1341
1342 --------------------------------------------------------------
1343 -- IF the allocation line exists
1344 -- and IF the Allocation_flag is Y
1345 debug_info := ' Step 4 - Update the allocation rule line ';
1346 --------------------------------------------------------------
1347 IF l_alloc_rule_line <> 0 THEN
1348
1349 IF X_allocation_flag = 'Y' then
1350 BEGIN
1351 UPDATE ap_allocation_rule_lines
1352 SET Amount = X_allocated_AMount,
1353 percentage = X_allocated_percentage,
1354 last_update_date = SYSDATE,
1355 last_updated_by = FND_GLOBAL.user_id,
1356 last_update_login = FND_GLOBAL.login_id
1357 WHERE invoice_id = X_invoice_id
1358 AND chrg_invoice_line_number = X_chrg_invoice_line_number
1359 AND to_invoice_line_number = X_to_invoice_line_number;
1360
1361 RETURN(TRUE);
1362
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 IF (SQLCODE <> -20001) THEN
1366 X_error_code := 'COULD_NOT_INSERT_ALLOC_RULE';
1367 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1368 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1369 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1370 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||to_char(X_Invoice_ID)
1371 ||', Invoice Line Number = '||to_char(X_chrg_Invoice_Line_Number));
1372 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','Inserting Allocation Rule Lines');
1373 END IF;
1374 APP_EXCEPTION.RAISE_EXCEPTION;
1375 END;
1376 END IF; -- allocaton_flag
1377
1378 END IF; -- If other rule line exists
1379
1380 RETURN(TRUE);
1381
1382 END Allocation_Rule_Lines;
1383
1384
1385 ------------------------------------------------------------------------
1386 -- This procedure is used to get the setup and invoice information needed
1387 -- by the both the sub-components(Allocation Rules Window and Allocations)
1388 -- of the Allocations Form.
1389 ------------------------------------------------------------------------
1390 PROCEDURE form_startup(
1391 X_invoice_id IN NUMBER,
1392 X_chart_of_accounts_id OUT NOCOPY NUMBER,
1393 X_invoice_date OUT NOCOPY DATE,
1394 X_vendor_type_lookup_code OUT NOCOPY VARCHAR2,
1395 X_vendor_name OUT NOCOPY VARCHAR2,
1396 X_invoice_num OUT NOCOPY VARCHAR2,
1397 X_invoice_currency_code OUT NOCOPY VARCHAR2,
1398 X_calling_sequence IN VARCHAR2) IS
1399
1400 l_org_id NUMBER;
1401 current_calling_sequence VARCHAR2(2000);
1402 debug_info VARCHAR2(100);
1403
1404 BEGIN
1405 -- Update the calling sequence
1406 --
1407 current_calling_sequence :=
1408 'Ap_Allocation_Rules_Pkg.form_startup<-'||X_Calling_Sequence;
1409
1410 --------------------------------------------------------------
1411 debug_info := 'Get invoice information';
1412 --------------------------------------------------------------
1413 SELECT ai.invoice_date,
1414 pv.vendor_type_lookup_code,
1415 pv.vendor_name,
1416 ai.invoice_num,
1417 ai.invoice_currency_code,
1418 ai.org_id
1419 INTO X_invoice_date,
1420 X_vendor_type_lookup_code,
1421 X_vendor_name,
1422 X_invoice_num,
1423 X_invoice_currency_code,
1424 l_org_id
1425 FROM ap_invoices ai, po_vendors pv
1426 WHERE ai.invoice_id = X_invoice_id
1427 AND ai.vendor_id = pv.vendor_id;
1428
1429
1430 --------------------------------------------------------------
1431 debug_info := 'Get chart of accounts';
1432 --------------------------------------------------------------
1433 SELECT gl.chart_of_accounts_id
1434 INTO X_chart_of_accounts_id
1435 FROM ap_system_parameters ap,
1436 gl_sets_of_books gl
1437 WHERE gl.set_of_books_id = ap.set_of_books_id
1438 AND ap.org_id = l_org_id;
1439
1440 EXCEPTION
1444 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1441 WHEN OTHERS THEN
1442 if (SQLCODE <> -20001) then
1443 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1445 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1446 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1447 'X_invoiceid ='||to_char(X_invoice_id));
1448 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1449 end if;
1450 APP_EXCEPTION.RAISE_EXCEPTION;
1451
1452 END form_startup;
1453
1454
1455 ------------------------------------------------------------------------
1456 -- Table Handler for the AP_ALLOCATION RULES Table for the update of
1457 -- Rule Type.
1458 ------------------------------------------------------------------------
1459 Procedure Update_row(
1460 X_rowid IN OUT NOCOPY VARCHAR2,
1461 X_Invoice_Id NUMBER,
1462 X_chrg_invoice_line_number NUMBER,
1463 X_Rule_Type VARCHAR2,
1464 X_Rule_Generation_Type VARCHAR2,
1465 X_Status VARCHAR2,
1466 X_last_updated_by NUMBER,
1467 X_last_update_date DATE,
1468 X_last_update_login NUMBER,
1469 X_calling_Sequence VARCHAR2) IS
1470
1471 current_calling_sequence VARCHAR2(2000);
1472 debug_info VARCHAR2(100);
1473
1474 BEGIN
1475
1476 current_calling_sequence := 'Ap_Allocation_Rules_Pkg.Update_Row<-'
1477 ||X_Calling_Sequence;
1478
1479 -- Check for uniqueness of the lineine number
1480
1481
1482 --------------------------------------------------------------
1483 debug_info := 'Update ap_allocation_rules';
1484 --------------------------------------------------------------
1485
1486 UPDATE ap_allocation_rules
1487 SET invoice_id = X_invoice_id,
1488 chrg_invoice_line_number = X_chrg_invoice_line_number,
1489 rule_type = X_rule_type,
1490 rule_generation_type = X_rule_generation_type,
1491 status = X_status,
1492 last_updated_by = X_last_updated_by,
1493 last_update_date = X_last_update_Date,
1494 last_update_login = X_last_update_login
1495 WHERE rowid = X_rowid;
1496
1497 IF (SQL%NOTFOUND) THEN
1498 Raise NO_DATA_FOUND;
1499 END IF;
1500
1501
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 IF (SQLCODE <> -20001) THEN
1505 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1506 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1507 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1508 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1509 END IF;
1510 APP_EXCEPTION.RAISE_EXCEPTION;
1511 END;
1512
1513
1514
1515 ------------------------------------------------------------------------
1516 -- Table Handler for the AP_ALLOCATION RULES Table for the Locking of
1517 -- Allocation Rule associated with the Charge Line.
1518 ------------------------------------------------------------------------
1519 Procedure Lock_row(
1520 X_rowid IN OUT NOCOPY VARCHAR2,
1521 X_Invoice_Id NUMBER,
1522 X_chrg_invoice_line_number NUMBER,
1523 X_Rule_Type VARCHAR2,
1524 X_Rule_Generation_Type VARCHAR2,
1525 X_Status VARCHAR2,
1526 X_calling_Sequence VARCHAR2)
1527 IS
1528
1529 CURSOR C IS
1530 SELECT *
1531 FROM AP_ALLOCATION_RULES
1532 WHERE rowid = X_Rowid
1533 FOR UPDATE of Invoice_Id NOWAIT;
1534 Recinfo C%ROWTYPE;
1535 current_calling_sequence VARCHAR2(2000);
1536 debug_info VARCHAR2(100);
1537
1538
1539 BEGIN
1540 -- Update the calling sequence
1541 --
1542 current_calling_sequence := 'Ap_Allocation_Rules_Pkg.Lock_Row<-'
1543 ||X_Calling_Sequence;
1544
1545 --------------------------------------------------------------
1546 debug_info := 'Select from ap_allocation_rules';
1547 --------------------------------------------------------------
1548
1549 OPEN C;
1550
1551 --------------------------------------------------------------
1552 debug_info := 'Fetch cursor C';
1553 --------------------------------------------------------------
1554 FETCH C INTO Recinfo;
1555 IF (C%NOTFOUND) THEN
1556 debug_info := 'Close cursor C - ROW NOTFOUND';
1557 CLOSE C;
1558 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
1559 APP_EXCEPTION.Raise_Exception;
1560 END IF;
1561 --------------------------------------------------------------
1562 debug_info := 'Close cursor C';
1563 --------------------------------------------------------------
1564 CLOSE C;
1565
1566 IF (
1567 (Recinfo.invoice_id = X_Invoice_Id)
1568 AND ( (Recinfo.chrg_invoice_line_number =
1569 X_chrg_invoice_line_number)
1570 OR ( (Recinfo.chrg_invoice_line_number IS NULL)
1571 AND (X_chrg_invoice_line_number IS NULL)))
1572 AND ( (Recinfo.rule_type =
1576 AND ( (Recinfo.rule_generation_type =
1573 X_rule_type)
1574 OR ( (Recinfo.rule_type IS NULL)
1575 AND (X_rule_type IS NULL)))
1577 X_Rule_generation_type)
1578 OR ( (Recinfo.Rule_generation_type IS NULL)
1579 AND (X_rule_generation_type IS NULL)))
1580 AND ( (Recinfo.status =
1581 X_status)
1582 OR ( (Recinfo.status IS NULL)
1583 AND (X_status IS NULL)))
1584
1585 ) then
1586 return;
1587 else
1588 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1589 APP_EXCEPTION.Raise_Exception;
1590 end if;
1591
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 IF (SQLCODE <> -20001) THEN
1595 IF (SQLCODE = -54) THEN
1596 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
1597 ELSE
1598 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1599 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1600 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1601 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
1602 ', INVOICE_ID == ' ||TO_CHAR(X_Invoice_Id));
1603 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1604 END IF;
1605 END IF;
1606 APP_EXCEPTION.RAISE_EXCEPTION;
1607
1608 END Lock_Row;
1609
1610 ------------------------------------------------------------------------------
1611 -- This function is called while updating the Amount for a Charge Line
1612 -- in the Invoice Window of the Invoice Workbench.
1613 -- The function prorates the already allocated allocation lines for the
1614 -- Pending Allocation rule(Amount and Percenatge) with respect to the new
1615 -- Charge Line Amount(Amount <> 0).
1616 -- The function returns TRUE and performs NO Action if the Charge Line
1617 -- does not has an Allocation Rule or the Charge Line has a Pending Rule
1618 -- of type Proration.
1619 -- It returns FALSE if an error is encountered, TRUE otherwise.
1620 -- The following error codes may be returned via the X_error_code OUT
1621 -- parameter:
1622 -- 'AP_NO_CHARGES_EXIST' - No charge lines exist for this invoice.
1623 -- 'AP_GENERATE_DISTS_IS_NO' - Generate Dists Flag is N for this chrg line
1624 -- 'AP_NO_ITEM_LINES_AVAIL' - No Item Lines exist or sum total of Item
1625 -- lines is zero for this Invoice
1626 -- 'AP_ALLOC_EXECUTED' -- You cannot make this change because this line's
1627 -- allocation rule has been executed.
1628 ------------------------------------------------------------------------------
1629
1630 FUNCTION Prorate_allocated_lines(
1631 X_invoice_id IN NUMBER,
1632 X_chrg_line_number IN NUMBER,
1633 X_new_chrg_line_amt IN NUMBER,
1634 X_Error_Code OUT NOCOPY VARCHAR2,
1635 X_Debug_Info OUT NOCOPY VARCHAR2,
1636 X_Debug_Context OUT NOCOPY VARCHAR2,
1637 X_calling_sequence IN VARCHAR2)
1638 RETURN BOOLEAN IS
1639
1640 l_generate_dists AP_INVOICE_LINES.GENERATE_DISTS%TYPE;
1641 l_alloc_rule NUMBER;
1642 l_amount_to_prorate AP_INVOICE_LINES.AMOUNT%TYPE;
1643 l_total_prorated AP_INVOICE_LINES.AMOUNT%TYPE;
1644 l_inv_curr_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
1645 l_rule_type AP_ALLOCATION_RULES.RULE_TYPE%TYPE;
1646 l_rule_status AP_ALLOCATION_RULES.STATUS%TYPE;
1647 l_prorating_total NUMBER := 0;
1648 l_prorated_total NUMBER := 0;
1649
1650 debug_info VARCHAR2(100);
1651 debug_context VARCHAR2(2000);
1652 current_calling_sequence VARCHAR2(2000);
1653
1654 BEGIN
1655
1656 -- Update the calling sequence
1657 current_calling_sequence := 'Ap_Allocation_Rules_Pkg.'||
1658 'Prorate_allocated_lines';
1659 --------------------------------------------------------------
1660 debug_info := 'Step 1 - Verify chrg line exists, has '
1661 ||' generate_dists flag set to Y';
1662 --------------------------------------------------------------
1663 BEGIN
1664 SELECT ail.generate_dists,
1665 X_new_chrg_line_amt, --bug6653070
1666 ai.invoice_currency_code
1667 INTO l_generate_dists,
1668 l_amount_to_prorate,
1669 l_inv_curr_code
1670 FROM ap_invoice_lines ail,
1671 ap_invoices ai
1672 WHERE ail.invoice_id = X_invoice_id
1673 AND ail.line_number = x_chrg_line_number
1674 AND ai.invoice_id = X_invoice_id;
1675
1676 /* Bug 5131721 */
1677 IF (nvl(l_generate_dists, 'N') = 'D') THEN
1678 X_error_code := 'AP_GENERATE_DISTS_IS_NO';
1679 RETURN(FALSE);
1680 END IF;
1681 EXCEPTION
1682 WHEN NO_DATA_FOUND THEN
1683 X_error_code := 'AP_NO_CHARGES_EXIST';
1684 RETURN(FALSE);
1685 WHEN OTHERS THEN
1686
1687 X_debug_context := current_calling_sequence;
1688 X_debug_info := debug_info;
1689 IF (SQLCODE <> -20001) THEN
1690 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1691 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1695 ||', Invoice Line Number = '||TO_CHAR(X_chrg_line_number));
1692 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1693 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id =
1694 '||TO_CHAR(X_invoice_id)
1696 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1697 END IF;
1698 X_error_code := 'AP_DEBUG';
1699 RETURN(FALSE);
1700 END;
1701
1702 --------------------------------------------------------------
1703 debug_info := 'Step 2 - Verify IF the allocation rule exist '
1704 ||'for this chrg line.';
1705 --------------------------------------------------------------
1706 BEGIN
1707
1708 l_alloc_rule := 0;
1709 SELECT COUNT(*)
1710 INTO l_alloc_rule
1711 FROM ap_allocation_rules
1712 WHERE invoice_id = X_invoice_id
1713 AND chrg_invoice_line_number = X_chrg_line_number;
1714
1715 IF (l_alloc_rule = 0) THEN
1716 RETURN(TRUE);
1717 END IF;
1718 EXCEPTION
1719 WHEN OTHERS THEN
1720 NULL;
1721 END;
1722
1723 --------------------------------------------------------------
1724 -- IF the allocation rules exist then
1725 debug_info := ' Step 3 - determine the Status and Rule Type';
1726 -- associated with the charge line.'
1727 --------------------------------------------------------------
1728 IF l_alloc_rule <> 0 then
1729
1730 BEGIN
1731 SELECT rule_type,
1732 status
1733 INTO l_rule_type,
1734 l_rule_status
1735 FROM ap_allocation_rules
1736 WHERE invoice_id = X_invoice_id
1737 AND chrg_invoice_line_number = X_chrg_line_number;
1738
1739 EXCEPTION
1740 WHEN OTHERS THEN
1741 NULL;
1742 END;
1743
1744 IF (l_rule_status = 'EXECUTED') THEN
1745 X_error_code := 'AP_ALLOC_EXECUTED';
1746 RETURN(FALSE);
1747 END IF;
1748
1749
1750 END IF;
1751
1752 --------------------------------------------------------------
1753 -- For AMOUNT and PERCENTAGE Based Rule Types verify that'
1754 debug_info := 'Step 4 - The Sum to prorate across is non zero';
1755 ---------------------------------------------------------------
1756 IF l_rule_type <> 'PRORATION' AND
1757 X_new_chrg_line_amt <> 0 THEN
1758
1759 BEGIN
1760 SELECT NVL(SUM(amount) ,0)
1761 INTO l_prorating_total
1762 FROM ap_allocation_rule_lines
1763 WHERE invoice_id = X_invoice_id
1764 AND chrg_invoice_line_number = X_chrg_line_number;
1765
1766 IF (l_prorating_total = 0) THEN
1767 X_error_code := 'AP_NO_ITEMS_LINES_AVAIL';
1768 RETURN(FALSE);
1769 END IF;
1770
1771 IF (l_prorating_total = X_new_chrg_line_amt ) THEN
1772 RETURN(TRUE);
1773 END IF;
1774
1775
1776 EXCEPTION
1777 WHEN OTHERS THEN
1778 NULL;
1779 END;
1780 END IF;
1781
1782 ---------------------------------------------------------
1783 debug_info := 'Step 5 - Update ap_allocation_rule_lines ';
1784 ---------------------------------------------------------
1785 IF (l_rule_type <> 'PRORATION' AND
1786 X_new_chrg_line_amt <> 0 ) THEN
1787
1788 BEGIN
1789
1790 UPDATE ap_allocation_rule_lines
1791 SET amount = ap_utilities_pkg.ap_round_currency(
1792 l_amount_to_prorate*amount/
1793 l_prorating_total, l_inv_curr_code),
1794 last_updated_by = FND_GLOBAL.USER_ID,
1795 last_update_date = SYSDATE,
1796 last_update_login = FND_GLOBAL.LOGIN_ID
1797 WHERE invoice_id = X_invoice_id
1798 AND chrg_invoice_line_number = X_chrg_line_number;
1799
1800 EXCEPTION
1801 WHEN OTHERS THEN
1802 IF (SQLCODE <> -20001) THEN
1803 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1804 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1805 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1806 current_calling_sequence);
1807 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '
1808 ||to_char(X_Invoice_ID)
1809 ||', Invoice Line Number = '
1810 ||to_char(X_chrg_Line_Number));
1811 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1812 END IF;
1813 APP_EXCEPTION.RAISE_EXCEPTION;
1814 END;
1815
1816 END IF;
1817
1818 --------------------------------------------------------------
1819 debug_info := 'Step 6 - Verify if there is any rounding and '
1820 ||'apply it to max of largest.';
1821 --------------------------------------------------------------
1822 IF (l_rule_type <> 'PRORATION' AND
1823 X_new_chrg_line_amt <> 0 ) THEN
1824 BEGIN
1825 SELECT SUM(amount)
1826 INTO l_total_prorated
1827 FROM ap_allocation_rule_lines
1828 WHERE invoice_id = X_invoice_id
1829 AND chrg_invoice_line_number = X_chrg_line_number;
1830
1831 IF (l_amount_to_prorate <> l_total_prorated) THEN
1832 UPDATE ap_allocation_rule_lines
1836 AND to_invoice_line_number =
1833 SET amount = amount + (l_amount_to_prorate - l_total_prorated)
1834 WHERE invoice_id = X_invoice_id
1835 AND chrg_invoice_line_number = X_chrg_line_number
1837 (SELECT (MAX(arl1.to_invoice_line_number))
1838 FROM ap_allocation_rule_lines arl1
1839 WHERE arl1.invoice_id = X_invoice_id
1840 AND arl1.chrg_invoice_line_number = X_chrg_line_number
1841 AND arl1.amount <> 0
1842 AND ABS(arl1.amount) >=
1843 ( SELECT MAX(ABS(arl2.amount))
1844 FROM ap_allocation_rule_lines arl2
1845 WHERE arl2.invoice_id = X_invoice_id
1846 AND arl2.chrg_invoice_line_number = X_chrg_line_number
1847 AND arl2.to_invoice_line_number <>
1848 arl2.to_invoice_line_number
1849 ));
1850 END IF;
1851 EXCEPTION
1852 WHEN OTHERS THEN
1853 IF (SQLCODE <> -20001) THEN
1854 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1855 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1856 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1857 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id ='
1858 ||to_char(X_Invoice_ID)
1859 ||', Invoice Line Number ='
1860 ||to_char(X_chrg_Line_Number));
1861 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1862 END IF;
1863 APP_EXCEPTION.RAISE_EXCEPTION;
1864 END;
1865 END IF;
1866
1867
1868 RETURN(TRUE);
1869 END Prorate_allocated_lines;
1870
1871 ------------------------------------------------------------------------------
1872 -- This function is called while updating the Amount for a Charge Line to 0
1873 -- in the Invoice Window of the Invoice Workbench.
1874 -- This function
1875 -- The function returns TRUE and performs NO Action if the Charge Line
1876 -- does not has an Allocation Rule or the Charge Line has a Pending Rule
1877 -- of type Proration.
1878 -- It returns FALSE if an error is encountered, TRUE otherwise.
1879 -- The following error codes may be returned via the X_error_code OUT
1880 -- parameter:
1881 -- 'AP_NO_CHARGES_EXIST' - No charge lines exist for this invoice.
1882 -- 'AP_GENERATE_DISTS_IS_NO' - Generate Dists Flag is N for this chrg line
1883 -- 'AP_ALLOC_EXECUTED' -- You cannot make this change because this line's
1884 -- allocation rule has been executed.
1885 ------------------------------------------------------------------------------
1886
1887 FUNCTION Delete_Allocations(
1888 X_invoice_id IN NUMBER,
1889 X_chrg_line_number IN NUMBER,
1890 X_new_chrg_line_amt IN NUMBER,
1891 X_Error_Code OUT NOCOPY VARCHAR2,
1892 X_Debug_Info OUT NOCOPY VARCHAR2,
1893 X_Debug_Context OUT NOCOPY VARCHAR2,
1894 X_calling_sequence IN VARCHAR2)
1895 RETURN BOOLEAN IS
1896
1897 l_generate_dists AP_INVOICE_LINES.GENERATE_DISTS%TYPE;
1898 l_alloc_rule NUMBER;
1899 l_amount_to_prorate AP_INVOICE_LINES.AMOUNT%TYPE;
1900 l_total_prorated AP_INVOICE_LINES.AMOUNT%TYPE;
1901 l_inv_curr_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
1902 l_rule_type AP_ALLOCATION_RULES.RULE_TYPE%TYPE;
1903 l_rule_status AP_ALLOCATION_RULES.STATUS%TYPE;
1904 l_prorating_total NUMBER := 0;
1905 l_prorated_total NUMBER := 0;
1906
1907 debug_info VARCHAR2(100);
1908 debug_context VARCHAR2(2000);
1909 current_calling_sequence VARCHAR2(2000);
1910
1911 BEGIN
1912
1913 -- Update the calling sequence
1914 current_calling_sequence := 'Ap_Allocation_Rules_Pkg.'||
1915 'Delete_Allocations';
1916 --------------------------------------------------------------
1917 debug_info := 'Step 1 - Verify chrg line exists, has '
1918 ||' generate_dists flag set to Y';
1919 --------------------------------------------------------------
1920 BEGIN
1921 SELECT ail.generate_dists,
1922 X_new_chrg_line_amt,
1923 ai.invoice_currency_code
1924 INTO l_generate_dists,
1925 l_amount_to_prorate,
1926 l_inv_curr_code
1927 FROM ap_invoice_lines ail,
1928 ap_invoices ai
1929 WHERE ail.invoice_id = X_invoice_id
1930 AND ail.line_number = x_chrg_line_number
1931 AND ai.invoice_id = X_invoice_id;
1932
1933 /* Bug 5131721 */
1934 IF (nvl(l_generate_dists, 'N') = 'D') THEN
1935 X_error_code := 'AP_GENERATE_DISTS_IS_NO';
1936 RETURN(FALSE);
1937 END IF;
1938 EXCEPTION
1939 WHEN NO_DATA_FOUND THEN
1940 X_error_code := 'AP_NO_CHARGES_EXIST';
1941 RETURN(FALSE);
1942 WHEN OTHERS THEN
1943 X_debug_context := current_calling_sequence;
1944 X_debug_info := debug_info;
1945 IF (SQLCODE <> -20001) THEN
1946 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1950 '||TO_CHAR(X_invoice_id)
1947 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1948 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1949 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id =
1951 ||', Invoice Line Number = '||TO_CHAR(X_chrg_line_number));
1952 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1953 END IF;
1954 X_error_code := 'AP_DEBUG';
1955 RETURN (FALSE);
1956 END;
1957
1958 --------------------------------------------------------------
1959 debug_info := 'Step 2 - Verify IF the allocation rule exist '
1960 ||'for this chrg line.';
1961 --------------------------------------------------------------
1962 BEGIN
1963
1964 l_alloc_rule := 0;
1965 SELECT COUNT(*)
1966 INTO l_alloc_rule
1967 FROM ap_allocation_rules
1968 WHERE invoice_id = X_invoice_id
1969 AND chrg_invoice_line_number = X_chrg_line_number;
1970
1971 IF (l_alloc_rule = 0) THEN
1972 RETURN(TRUE);
1973 END IF;
1974 EXCEPTION
1975 WHEN OTHERS THEN
1976 NULL;
1977 END;
1978
1979 --------------------------------------------------------------
1980 --- IF the allocation rules exist then '
1981 debug_info := ' Step 3 - Determine the Status and Rule type ';
1982 -- associated with the charge line.
1983 --------------------------------------------------------------
1984 IF l_alloc_rule <> 0 then
1985
1986 BEGIN
1987 SELECT rule_type,
1988 status
1989 INTO l_rule_type,
1990 l_rule_status
1991 FROM ap_allocation_rules
1992 WHERE invoice_id = X_invoice_id
1993 AND chrg_invoice_line_number = X_chrg_line_number;
1994
1995 EXCEPTION
1996 WHEN OTHERS THEN
1997 NULL;
1998 END;
1999
2000 IF (l_rule_status = 'EXECUTED') THEN
2001 X_error_code := 'AP_ALLOC_EXECUTED';
2002 RETURN(FALSE);
2003 END IF;
2004
2005
2006 END IF;
2007
2008
2009 --------------------------------------------------------
2010 debug_info := 'Step 4 - If the New Amount is Zero, all '
2011 ||'pending allocation rules will be deleted.';
2012 -------------------------------------------------------
2013 IF X_new_chrg_line_amt = 0 THEN
2014
2015 DELETE FROM ap_allocation_rules
2016 WHERE invoice_id = X_Invoice_Id
2017 AND chrg_invoice_line_number = X_chrg_line_number;
2018
2019 IF l_rule_type <> 'PRORATION' THEN
2020 Ap_Allocation_Rules_Pkg.Delete_Allocation_Lines(
2021 X_invoice_id => X_Invoice_Id,
2022 X_chrg_line_number => X_chrg_line_number,
2023 X_calling_sequence => current_calling_sequence);
2024 END IF;
2025
2026 RETURN(TRUE);
2027 END IF;
2028
2029 RETURN(TRUE);
2030 END Delete_Allocations;
2031
2032 -------------------------------------------------------------------------
2033 -- This Procedure deletes the pending Allocation Rule Lines so
2034 -- that the Rule Lines can be recreated as of the latest snapshot
2035 -- This function is called from the following
2036 -- 1. Whenever the user changes the Rule Type of the Allocation Rule
2037 -- 2. Delete Allocations(Whenever a user updates the charge line amount
2038 -- to 0 in the Invoice Window).
2039 ------------------------------------------------------------------------
2040 PROCEDURE delete_allocation_lines(
2041 X_invoice_id IN NUMBER,
2042 X_chrg_line_number IN NUMBER,
2043 X_calling_sequence IN VARCHAR2) IS
2044
2045 current_calling_sequence VARCHAR2(2000);
2046 debug_info VARCHAR2(100);
2047
2048 BEGIN
2049 current_calling_sequence :=
2050 'Ap_Allocation_Rules_Pkg.Delete_allocations_lines<-'||X_Calling_Sequence;
2051 -----------------------------------------------------------
2052 debug_info := 'Deleting any existing allocation rows ';
2053 ----------------------------------------------------------
2054 DELETE FROM ap_allocation_rule_lines
2055 WHERE invoice_id = X_invoice_id
2056 AND chrg_invoice_line_number = X_chrg_line_number;
2057
2058 EXCEPTION
2059 WHEN OTHERS THEN
2060 if (SQLCODE <> -20001) then
2061 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
2062 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
2063 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
2064 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2065 'X_Invoice_Id = '||to_char(X_invoice_id) );
2066 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
2067 end if;
2068 APP_EXCEPTION.RAISE_EXCEPTION;
2069
2070 END Delete_Allocation_Lines;
2071
2072 -------------------------------------------------------------------------
2073 -- This Procedure creates Allocation Rule and Rule Lines for tax lines.
2074 -- It is invoked after tax lines are inserted from the AP eTax utilities
2075 -- package.
2076 -------------------------------------------------------------------------
2077 FUNCTION insert_tax_allocations (
2078 X_invoice_id IN NUMBER,
2079 X_chrg_line_number IN NUMBER,
2080 X_error_code OUT NOCOPY VARCHAR2) RETURN BOOLEAN
2081 IS
2082
2086 l_total_prorated AP_INVOICE_LINES.AMOUNT%TYPE;
2083 l_generate_dists AP_INVOICE_LINES.GENERATE_DISTS%TYPE;
2084 l_line_group_number AP_INVOICE_LINES.LINE_GROUP_NUMBER%TYPE;
2085 l_amount_to_prorate AP_INVOICE_LINES.AMOUNT%TYPE;
2087 l_inv_curr_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
2088 l_count_non_item_lines NUMBER := 0;
2089 l_prorating_total NUMBER := 0;
2090 l_other_alloc_rules NUMBER;
2091 l_other_alloc_rule_line NUMBER;
2092 current_calling_sequence VARCHAR2(2000);
2093 debug_info VARCHAR2(200);
2094
2095 BEGIN
2096
2097 -- Update the calling sequence
2098 current_calling_sequence := 'AP_ALLOCATION_RULES_PKG.'||
2099 'insert_tax_allocations';
2100
2101 --------------------------------------------------------------
2102 -- Step 1 - Verify line exists, has generate_dists flag
2103 -- set to Y
2104 --------------------------------------------------------------
2105 BEGIN
2106 SELECT ail.generate_dists,
2107 ail.line_group_number,
2108 ail.amount,
2109 ai.invoice_currency_code
2110 INTO l_generate_dists,
2111 l_line_group_number,
2112 l_amount_to_prorate,
2113 l_inv_curr_code
2114 FROM ap_invoice_lines ail,
2115 ap_invoices ai
2116 WHERE ail.invoice_id = x_invoice_id
2117 AND ail.line_number = x_chrg_line_number
2118 AND ai.invoice_id = ail.invoice_id;
2119
2120 /* Bug 5131721 */
2121 IF (nvl(l_generate_dists, 'N') = 'D' ) THEN
2122 X_error_code := 'AP_GENERATE_DISTS_IS_NO';
2123 RETURN(FALSE);
2124 END IF;
2125
2126 EXCEPTION
2127 WHEN NO_DATA_FOUND THEN
2128 IF (SQLCODE <> -20001) THEN
2129 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2130 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2131 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2132 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id) ||
2133 ', Invoice Line Number = '||TO_CHAR(X_chrg_line_number));
2134 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
2135 END IF;
2136 X_error_code := 'AP_DEBUG';
2137 RETURN (FALSE);
2138 END;
2139
2140 --------------------------------------------------------------
2141 -- Step 2 - Delete any allocation rules/rule lines that exists.
2142 --------------------------------------------------------------
2143 BEGIN
2144 l_other_alloc_rules := 0;
2145 SELECT COUNT(*)
2146 INTO l_other_alloc_rules
2147 FROM ap_allocation_rules
2148 WHERE invoice_id = X_invoice_id
2149 AND chrg_invoice_line_number = X_chrg_line_number;
2150
2151 SELECT COUNT(*)
2152 INTO l_other_alloc_rule_line
2153 FROM ap_allocation_rule_lines
2154 WHERE invoice_id = X_invoice_id
2155 AND chrg_invoice_line_number = X_chrg_line_number;
2156
2157
2158 IF (l_other_alloc_rules <> 0) THEN
2159
2160 DELETE FROM ap_allocation_rules
2161 WHERE invoice_id = X_invoice_id
2162 AND chrg_invoice_line_number = X_chrg_line_number;
2163
2164 END IF;
2165
2166 IF (l_other_alloc_rule_line <> 0) THEN
2167
2168 DELETE FROM ap_allocation_rule_lines
2169 WHERE invoice_id = X_invoice_id
2170 AND chrg_invoice_line_number = X_chrg_line_number;
2171
2172 END IF;
2173
2174 EXCEPTION
2175 WHEN OTHERS THEN
2176 NULL;
2177 END;
2178
2179 --------------------------------------------------------------
2180 -- Step 5 - Insert Allocation Rule
2181 --------------------------------------------------------------
2182 BEGIN
2183 INSERT INTO ap_allocation_rules(
2184 invoice_id,
2185 chrg_invoice_line_number,
2186 rule_type,
2187 rule_generation_type,
2188 status,
2189 creation_date,
2190 created_by,
2191 last_updated_by,
2192 last_update_date,
2193 last_update_login,
2194 program_application_id,
2195 program_id,
2196 program_update_date,
2197 request_id)
2198 VALUES(
2199 X_invoice_id, -- invoice_id
2200 X_chrg_line_number, -- chrg_invoice_line_number
2201 'AMOUNT', -- rule_type
2202 'SYSTEM', -- rule_generation_type
2203 'EXECUTED', -- status
2204 SYSDATE, -- creation_date
2205 FND_GLOBAL.USER_ID, -- created_by
2206 FND_GLOBAL.USER_ID, -- last_updated_by
2207 SYSDATE, -- last_update_date
2208 FND_GLOBAL.LOGIN_ID, -- last_update_login
2209 FND_GLOBAL.PROG_APPL_ID, -- program_application_id
2210 FND_GLOBAL.CONC_PROGRAM_ID,-- program_id
2211 SYSDATE, -- program_update_date
2212 FND_GLOBAL.CONC_REQUEST_ID -- request_id
2213 );
2214 EXCEPTION
2215 WHEN OTHERS THEN
2216 IF (SQLCODE <> -20001) THEN
2217 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2218 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2219 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2220 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id ='||TO_CHAR(X_invoice_id) ||
2224 X_error_code := 'AP_DEBUG';
2221 ', Invoice Line Number = '||TO_CHAR(X_chrg_line_number));
2222 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
2223 END IF;
2225 RETURN (FALSE);
2226 END;
2227
2228 --------------------------------------------------------------
2229 -- Step 6 - Insert Allocation Rule Lines
2230 --------------------------------------------------------------
2231 BEGIN
2232 INSERT INTO ap_allocation_rule_lines (
2233 invoice_id,
2234 chrg_invoice_line_number,
2235 to_invoice_line_number,
2236 amount,
2237 creation_date,
2238 created_by,
2239 last_updated_by,
2240 last_update_date,
2241 last_update_login,
2242 program_application_id,
2243 program_id,
2244 program_update_date,
2245 request_id)
2246 SELECT x_invoice_id, -- invoice_id
2247 x_chrg_line_number, -- chrg_invoice_line_number
2248 zxl.trx_line_number, -- to_invoice_line_number
2249 sum(zxl.tax_amt), -- amount
2250 SYSDATE, -- creation_date
2251 FND_GLOBAL.USER_ID, -- created_by
2252 FND_GLOBAL.USER_ID, -- last_updated_by
2253 SYSDATE, -- last_update_date
2254 FND_GLOBAL.LOGIN_ID, -- last_update_login
2255 FND_GLOBAL.PROG_APPL_ID, -- program_application_id
2256 FND_GLOBAL.CONC_PROGRAM_ID, -- program_id
2257 SYSDATE, -- program_update_date
2258 FND_GLOBAL.CONC_REQUEST_ID -- request_id
2259 FROM zx_lines zxl,
2260 ap_invoice_lines apl
2261 WHERE apl.invoice_id = x_invoice_id
2262 AND apl.line_number = x_chrg_line_number
2263 AND apl.summary_tax_line_id = zxl.summary_tax_line_id
2264 AND zxl.application_id = AP_ETAX_PKG.AP_APPLICATION_ID
2265 AND zxl.entity_code = AP_ETAX_PKG.AP_ENTITY_CODE
2266 AND zxl.event_class_code IN (AP_ETAX_PKG.AP_INV_EVENT_CLASS_CODE,
2267 AP_ETAX_PKG.AP_PP_EVENT_CLASS_CODE,
2268 AP_ETAX_PKG.AP_ER_EVENT_CLASS_CODE)
2269 AND zxl.trx_id = apl.invoice_id
2270 AND NVL(zxl.reporting_only_flag, 'N') = 'N'
2271 GROUP BY zxl.trx_line_number;
2272
2273 EXCEPTION
2274 WHEN OTHERS THEN
2275 IF (SQLCODE <> -20001) THEN
2276 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2277 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2278 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2279 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_invoice_id) ||
2280 ', Invoice Line Number = '||TO_CHAR(X_chrg_line_number));
2281 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
2282 END IF;
2283 X_error_code := 'AP_DEBUG';
2284 RETURN (FALSE);
2285 END;
2286
2287 RETURN(TRUE);
2288
2289 END insert_tax_allocations;
2290
2291 END AP_ALLOCATION_RULES_PKG;